Hogyan fésztsünk több IF függvényt Excel-ben?

01/06

A beágyazott IF függvények működése

Az IF funkció hasznosságát úgy lehet meghosszabbítani, hogy több IF-függvény beillesztésével vagy fészkelésével egymásba ágyazódik .

A beágyazott IF-funkciók növelik az esetlegesen vizsgált feltételek számát, és növelik az ezeknek az eredményeknek a kezeléséhez szükséges lépések számát.

Az Excel legfrissebb verziói lehetővé teszik, hogy a 64 IF függvény egymásba ágyazódjon, míg az Excel 2003 és a korábbi csak héten engedélyezett.

Az IF funkciók bemutatása

Amint a fenti képen látható, ez a bemutató csak két IF-funkciót használ, hogy létrehozza a következő képletet , amely éves bérösszegét számolja az alkalmazottaknak az éves fizetésük alapján.

A példában alkalmazott képlet az alábbi. A beágyazott IF függvény az első IF ​​függvény érték_if_false argumentuma .

= HA (D7 = 50000, $ D $ 5 * D7, $ D $ 4 * D7))

A képlet különböző részeit vesszővel elválasztjuk, és a következő feladatokat végezzük:

1. Az első rész, a D7, ellenőrzi, hogy egy alkalmazott fizetése kevesebb, mint 30 000 dollár
2. Ha ez így van, akkor a középső rész, $ D $ 3 * D7 , megszorozza a fizetést a levonási arány 6%
3. Ha nem, akkor a második IF-függvény: további két feltétel vizsgálata: IF (D7> = 50000, $ D $ 5 * D7, $ D $ 4 * D7)
   • D7> = 50000 , ellenőrzi, hogy egy alkalmazott fizetése nagyobb-e, vagy egyenlő-e 50 000 dollárral
   • Ha ez így van, $ D $ 5 * D7 szorozza meg a fizetést a levonási arány 10%
   • Ha nem, $ D $ 4 * D7 szorozza meg a fizetést a levonási arány 8%

A bemutatóadatok beírása

Adja meg az adatokat az Excel munkalap C1-E6 celláiba a fenti képen látható módon.

Az egyetlen olyan adat, amelyet ezen a ponton nem adtak meg, maga az IF funkció található az E7 cellában.

Azok számára, akik nem szeretnek gépelni, az Excel-be másolásra vonatkozó adatok és utasítások ezen a linken érhetők el.

Megjegyzés: Az adatok másolására vonatkozó utasítások nem tartalmazzák a munkalapon lévő formázási lépéseket.

Ez nem akadályozza a tutorial kitöltését. A munkalap eltérhet a bemutatott példától, de az IF funkció ugyanazokat az eredményeket adja.

02. 06. sz

A beágyazott IF függvény elindítása

Bár csak a teljes képlet beírása lehetséges

= HA (D7 = 50000, $ D $ 5 * D7, $ D $ 4 * D7))

a munkalap E7 cellájába, és működik, akkor gyakran könnyebb használni a függvény párbeszédablakát a szükséges érvek megadásához.

A párbeszédpanel használata kicsit trükköbb, amikor beágyazott függvényeket ad meg, mert beágyazott funkciót kell beírni. Egy második párbeszédpanelt nem lehet megnyitni a második argumentumkészlet beírásához.

Ebben a példában a beágyazott IF funkciót a párbeszédablak harmadik sorába írjuk Value_if_false argumentumként.

• Mivel a munkalap kiszámítja a több alkalmazottra vonatkozó éves levonást, a képletet először egy E7 cellába kell bevinni abszolút sejt-referenciák alkalmazásával a levonási arányok után, majd másolni az E8: E11 cellákra.

Bemutató lépések

1. Kattintson az E7 cellára, hogy az aktív cella legyen. - a beágyazott IF képlet helyét.
2. Kattintson a szalag Formulák lapjára
3. Kattintson a Logikai ikonra, és nyissa meg a funkció legördülő listáját.
4. Kattintson a listában szereplő IF -re, hogy megjelenjen a funkció párbeszédablaka.

A párbeszédablak üres soraira beírt adatok az IF függvény argumentumát alkotják.

Ezek az érvek jelzik a függvényt, hogy a feltétel tesztelésre és milyen lépésekre van szükség, ha a feltétel igaz vagy hamis.

Tutorial Shortcut lehetőség

Ha folytatni szeretné ezt a példát, megteheti

• írja be az argumentumokat a fenti képen látható párbeszédablakba, majd ugorjon az utolsó lépéshez, amely kiterjeszti a képletet a 7-10. sorokra;
• vagy kövesse a következő öt oldalt, amelyek részletes utasításokat és magyarázatokat adnak a három érv beírásához.

03/06

Belépés a Logical_test argumentumba

A Logical_test argumentum mindig két adatelem összehasonlítása. Ezek az adatok lehetnek számok, cella hivatkozások , képletek eredményei, vagy akár szöveges adatok.

Két érték összehasonlításához a Logical_test összehasonlító operátort használ az értékek között.

Ebben a példában három fizetési szint van, amely meghatározza a munkavállaló éves levonását.

• kevesebb, mint 30 000 dollár
• $ 30,000 és $ 49,999 között
• 50 000 vagy annál több

Egy IF funkció két szintet hasonlíthat össze, de a harmadik fizetési szint a második beágyazott IF funkció használatát igényli.

Az első összehasonlítás a D-ban található munkavállalói éves fizetés között lesz, a küszöbérték 30 000 dollár.

Mivel a cél annak megállapítása, hogy a D7 értéke kevesebb, mint 30 000 dollár, a kevésbé "<" értékű operátort alkalmazzák az értékek között.

Bemutató lépések

1. Kattintson a Logical_test sorra a párbeszédpanelen
2. A C7 cellára kattintva hozzáadhatja a Cella hivatkozást a Logical_test vonalhoz
3. Nyomja meg a billentyűt a "<" billentyű lenyomásával
4. A 30000-as típus a kisebb mint a szimbólum után
5. A befejezett logikai tesztnek a következőnek kell lennie: D7 <30000

Megjegyzés: Ne írja be a dollárjelet ($) vagy vesszővel elválasztó (,) a 30000-hoz.

Érvénytelen hibaüzenet jelenik meg a Logical_test sor végén, ha az egyik szimbólum be van adva az adatokkal együtt.

04/06

A Value_if_true argumentum megadása

A Value_if_true argumentum megmondja az IF funkciónak, hogy mit kell tennie, ha a Logical_test igaz.

A Value_if_true argumentum lehet egy képlet, egy szövegblokk, egy érték , egy cellahivatkozás vagy a cella üres lehet.

Ebben a példában, ha a D7 cellában lévő adatok kevesebb, mint 30 000 dollár. Az Excel többszörözi a munkavállaló éves fizetését a D7 cellában a D3 cellában levő 6% -os levonási arány mellett.

Relatív vs. abszolút sejt referenciák

Általában ha egy képletet átmásolunk más cellákra, a képletben a relatív sejt-referenciák megváltoznak, hogy azok tükrözzék a képlet új helyét. Ez megkönnyíti ugyanazt a képletet több helyen is.

Időnként azonban, ha egy funkció másolásakor megváltozik a cellahivatkozás, hiba lép fel.

A hibák megelőzése érdekében a cellaszámlálások abszolút értékűek lehetnek, ami megakadályozza, hogy megváltoztassák őket a másoláskor.

Az abszolút sejtes hivatkozásokat úgy hozza létre, hogy dollárjeleket adnak hozzá a rendszeres cellahivatkozásokhoz, például $ D $ 3-hoz .

A dollárjelek hozzáadásával könnyen elvégezhető a billentyűzet F4 gombjainak megnyomása, miután a cellahivatkozást beírta a párbeszédpanelbe.

A példában a D3 cellában levezetett levonási arány abszolút cellahivatkozásként kerül a dialógusdoboz Value_if_true sorába.

Bemutató lépések

1. Kattintson a párbeszédablak Value_if_true sorára
2. Kattintson a D3 cellára a munkalapon, hogy hozzáadja ezt a cellahivatkozást a Value_if_true vonalhoz
3. Nyomja meg az F4 billentyűt a billentyűzeten, hogy a D3 abszolút cellahivatkozás legyen ($ D $ 3)
4. Nyomja meg a csillagot ( * ) a billentyűzeten - a csillag az Excel szimbóluma
5. Kattintson a D7 cellára, hogy hozzáadja ezt a cellahivatkozást a Value_if_true vonalhoz
6. A befejezett Value_if_true sor a következő: $ D $ 3 * D7

Megjegyzés : A D7 nem szerepel abszolút cella-referenciaként, mert megváltoztatnia kell, ha a képlet átmásolódik az E8: E11 cellákra, hogy a megfelelő levonási összeget minden munkavállalóhoz megkaphassa.

05/06

A beágyazott IF függvény megadása Value_if_false argumentumként

Normális esetben az Value_if_false argumentum megmondja az IF függvénynek, hogy mit kell tennie, ha a Logical_test hamis, de ebben az esetben a beágyazott IF függvény ezt az argumentumot adja meg.

Ezzel a következő eredmények merülnek fel:

• A beágyazott IF-funkcióban ( D7> = 50000 ) a Logical_test argumentum minden olyan fizetést tesztel, amely nem kevesebb, mint 30 000 dollár.
• Az 50 000 dollárnál nagyobb vagy azzal egyenlő fizetéseknél az Value_if_true argumentum a D5 cellában levő 10% -os levonási arányt megszorozza.
• A fennmaradó fizetések esetében - amelyek meghaladják a 30 000 dollárt, de kevesebb mint 50 000 dollárt - a Value_if_false argumentum megszorozza őket a D4 cellában levő 8% -os levonási arány mellett.

Bemutató lépések

Amint azt a bemutató elején említettük, egy második párbeszédpanelt nem lehet megnyitni a beágyazott függvény beírásához, így be kell írni a Value_if_false sorba.

Megjegyzés: a beágyazott funkciók nem egyenlő jelekkel kezdődnek, hanem inkább a függvény nevével.

1. Kattintson a Value_if_false sorra a párbeszédpanelen
2. Adja meg a következő IF funkciót
   IF (D7> = 50000, $ D $ 5 * D7, $ D $ 4 * D7)
3. Kattintson az OK gombra az IF funkció befejezéséhez és zárja be a párbeszédpanelt
4. A $ 3,678.96 értéknek meg kell jelennie az E7 cellában *
5. Ha az E7 cellára kattint, akkor a teljes funkció
   = HA (D7 = 50000, $ D $ 5 * D7, $ D $ 4 * D7))
   megjelenik a munkalap feletti képletsávban

* Mivel R. Holt több mint 30 000 dollárt, de kevesebb, mint 50 000 dollárt keres évente, a képlet 45 987 * 8% -ot használ az éves levonás kiszámításához.

Ha minden lépést követett, a példának meg kell egyeznie a jelen cikk elsõ képével.

Az utolsó lépés az IF-képletnek az E8-tól E11-ig terjedő cellákra történő másolása, a kitöltő fogantyúval a munkalap kitöltéséhez .

06, 06

A beágyazott IF funkciók másolása a kitöltő fogantyúval

A munkalap kitöltéséhez a beágyazott IF funkciót tartalmazó képletet át kell másolni az E8-tól E11-ig terjedő cellákra.

A funkció másolásakor az Excel frissíti a relatív sejt-referenciákat, hogy tükrözze a függvény új helyét, miközben megtartja az abszolút cellahivatkozást.

Az Excel formátumok másolásának egyik egyszerű módja a Fill Handle.

Bemutató lépések

1. Kattintson az E7 cellára, hogy az aktív cella legyen .
2. Helyezze az egérmutatót az aktív cella jobb alsó sarkában található fekete négyzet fölé. A mutató "+" pluszjelre változik.
3. Kattintson a bal egérgombbal, és húzza le a kitöltő fogantyút az E11 cellára.
4. Engedje fel az egérgombot. Az E8-tól E11-ig terjedő sejteket a fenti képen látható képlet eredményével töltjük meg.