Power Query: If utasítás – beágyazott ifs és több feltétel
Excelben Power Query, az IF utasítás az egyik legnépszerűbb függvény a feltétel ellenőrzésére és egy adott érték visszaadására attól függően, hogy az eredmény IGAZ vagy HAMIS. Van némi különbség az if utasítás és az Excel IF függvénye között. Ebben az oktatóanyagban bemutatom ennek az if utasításnak a szintaxisát, valamint néhány egyszerű és összetett példát.
Az alapvető if utasítás szintaxisa Power Query
Power Query if utasítás feltételes oszlopot használva
Power Query if utasítást az M kód írásával
Az alapvető if utasítás szintaxisa Power Query
In Power Query, a szintaxis a következő:
- logikai_teszt: A tesztelni kívánt állapot.
- érték_ha_igaz: A visszaadandó érték, ha az eredmény IGAZ.
- érték_ha_hamis: A visszaadandó érték, ha az eredmény HAMIS.
Excelben Power Query, kétféleképpen hozhat létre ilyen típusú feltételes logikát:
- A Feltételes oszlop funkció használata néhány alapvető forgatókönyvhöz;
- M-kód írása a fejlettebb forgatókönyvekhez.
A következő részben bemutatok néhány példát ennek az if utasításnak a használatára.
Power Query if utasítás feltételes oszlopot használva
1. példa: Basic if utasítás
Itt bemutatom, hogyan kell használni ezt az if utasítást Power Query. Például van egy következő termékjelentésem, ha a termék állapota Régi, és 50% kedvezményt mutat; ha a termék állapota Új, 20% kedvezményt jelenít meg az alábbi képernyőképek szerint.
1. Válassza ki az adattáblázatot a munkalapon, majd Excel 2019-ben és Excel 365-ben kattintson a gombra dátum > Táblázatból/Tartományból, lásd a képernyőképet:
Megjegyzések: Az Excel 2016-ban és az Excel 2021-ben kattintson a gombra dátum > A táblázatból, lásd a képernyőképet:
2. Aztán a nyitottban Power Query szerkesztő ablakban kattintson Oszlop hozzáadása > Feltételes oszlop, lásd a képernyőképet:
3. A kiugrott Feltételes oszlop hozzáadása párbeszédpanelen tegye a következőket:
- Új oszlopnév: Adja meg az új oszlop nevét;
- Ezután adja meg a szükséges feltételeket. Például pontosítok Ha az állapot megegyezik a régi, akkor 50% különben 20%;
- Oszlop neve: Az az oszlop, amelyhez viszonyítva értékeli az if-feltételt. Itt az Állapot választom.
- Operátor: Feltételes logika használható. Az opciók a kiválasztott oszlopnév adattípusától függően eltérőek lehetnek.
- szöveg: azzal kezdődik, nem ezzel kezdődik, egyenlő, tartalmaz stb.
- Számok: egyenlő, nem egyenlő, nagyobb vagy egyenlő, stb.
- találka: előtte van, utána van, egyenlő, nem egyenlő stb.
- Érték: Az a konkrét érték, amellyel összehasonlíthatja értékelését. Az oszlopnévvel és az operátorral együtt feltételt alkot.
- teljesítmény: A visszaadandó érték, ha a feltétel teljesül.
- Más: Egy másik érték, amelyet vissza kell adni, ha a feltétel hamis.
4. Ezután kattints a gombra OK gombot, hogy visszatérjen a Power Query szerkesztő ablak. Most egy új Kedvezmény oszlop hozzáadásra került, lásd a képernyőképet:
5. Ha százalékosra szeretné formázni a számokat, kattintson rá ABC123 ikont a Kedvezmény oszlopfejlécet, és válassza ki Százalék amire szüksége van, lásd a képernyőképet:
6. Végül kattintson Kezdőlap > Bezárás és betöltés > Bezárás és betöltés hogy ezeket az adatokat egy új munkalapra töltse be.
2. példa: Complex if utasítás
Ezzel a Feltételes oszlop opcióval két vagy több feltételt is beszúrhat a Feltételes oszlop hozzáadása párbeszéd. Kérjük, tegye a következőket:
1. Válassza ki az adattáblázatot, és lépjen a Power Query szerkesztő ablakra kattintva dátum > Táblázatból/Tartományból. Az új ablakban kattintson a gombra Oszlop hozzáadása > Feltételes oszlop.
2. A kiugrott Feltételes oszlop hozzáadása párbeszédpanelen tegye a következőket:
- Adja meg az új oszlop nevét a Új oszlopnév szövegdoboz;
- Adja meg az első feltételt az első feltétel mezőben, majd kattintson a gombra Záradék hozzáadása gombot, hogy szükség szerint további feltételmezőket adjon hozzá.
3. A feltételek kitöltése után kattintson a gombra OK gombot, hogy visszatérjen a Power Query szerkesztő ablak. Most kap egy új oszlopot a megfelelő eredménnyel. Lásd a képernyőképet:
4. Végül kérem kattintson Kezdőlap > Bezárás és betöltés > Bezárás és betöltés hogy ezeket az adatokat egy új munkalapra töltse be.
Power Query if utasítást az M kód írásával
Általában a Feltételes oszlop néhány alapvető forgatókönyv esetén hasznos. Néha előfordulhat, hogy több feltételt kell használnia ÉS vagy VAGY logikával. Ebben az esetben összetettebb forgatókönyvek esetén M kódot kell írnia egy egyéni oszlopba.
1. példa: Basic if utasítás
Vegyük például az első adatot, ha a termék állapota Régi, 50% kedvezményt jelenít meg; ha a termék állapota Új, 20% kedvezményt jelenít meg. Az M kód beírásához tegye a következőket:
1. Válassza ki a táblázatot, majd kattintson a gombra dátum > Táblázatból/Tartományból menni Power Query szerkesztő ablak.
2. A megnyíló ablakban kattintson a gombra Oszlop hozzáadása > Egyéni oszlop, lásd a képernyőképet:
3. A kiugrott Egyéni oszlop párbeszédpanelen tegye a következőket:
- Adja meg az új oszlop nevét a Új oszlopnév szövegdoboz;
- Ezután írja be ezt a képletet: if [Status] = "Régi ", akkor "50% " egyébként "20% " be a Egyéni oszlop képlet doboz.
4. Ezután kattints a gombra OK a párbeszédpanel bezárásához. Most a kívánt eredményt kapja:
5. Végül kattintson Kezdőlap > Bezárás és betöltés > Bezárás és betöltés hogy ezeket az adatokat egy új munkalapra töltse be.
2. példa: Complex if utasítás
Általában az alfeltételek teszteléséhez több if-utasítást is beágyazhat. Például megvan az alábbi adattáblázat. Ha a termék „Ruha”, adjon 50% kedvezményt az eredeti árból; ha a termék „pulóver” vagy „pulóver”, adjon 20% kedvezményt az eredeti árból; és más termékek megtartják az eredeti árat.
1. Válassza ki az adattáblázatot, és kattintson a gombra dátum > Táblázatból/Tartományból menni Power Query szerkesztő ablak.
2. A megnyíló ablakban kattintson a gombra Oszlop hozzáadása > Egyéni oszlop. A nyitottban Egyéni oszlop párbeszédpanelen tegye a következőket:
- Adja meg az új oszlop nevét a Új oszlopnév szövegdoboz;
- Ezután írja be az alábbi képletet a Egyéni oszlop képlet doboz.
- = ha [Termék] = "Ruha", akkor [Ár] * 0.5 egyéb
ha [Termék] = "Puóver", akkor [Ár] * 0.8 egyéb
ha [Termék] = "Pucsi" akkor [Ár] * 0.8
különben [ár]
3. Ezután kattintson OK gombbal térhet vissza a Power Query szerkesztő ablakot, és kap egy új oszlopot a szükséges adatokkal, lásd a képernyőképet:
4. Végül kattintson Kezdőlap > Bezárás és betöltés > Bezárás és betöltés hogy ezeket az adatokat egy új munkalapra töltse be.
A VAGY logika több logikai tesztet hajt végre, és a valódi eredményt akkor kapja vissza, ha bármelyik logikai teszt teljesül. A szintaxis a következő:
Tételezzük fel, hogy megvan az alábbi táblázat, most új oszlopos megjelenítést szeretnék: ha a termék „Ruha” vagy „T-shirt”, akkor a márka „AAA”, a többi termék márkája „BBB”.
1. Válassza ki az adattáblázatot, és kattintson a gombra dátum > Táblázatból/Tartományból menni Power Query szerkesztő ablak.
2. A megnyíló ablakban kattintson a gombra Oszlop hozzáadása > Egyéni oszlop, a nyitottban Egyéni oszlop párbeszédpanelen tegye a következőket:
- Adja meg az új oszlop nevét a Új oszlopnév szövegdoboz;
- Ezután írja be az alábbi képletet a Egyéni oszlopképlet doboz.
- = ha [Termék] = "Ruha" vagy [Termék] = "póló", akkor "AAA"
különben "BBB"
3. Ezután kattintson OK gombbal térhet vissza a Power Query szerkesztő ablakot, és kap egy új oszlopot a szükséges adatokkal, lásd a képernyőképet:
4. Végül kattintson a gombra Kezdőlap > Bezárás és betöltés > Bezárás és betöltés hogy ezeket az adatokat egy új munkalapra töltse be.
Az ÉS logika több logikai tesztet hajt végre egyetlen if utasításon belül. Valamennyi tesztnek igaznak kell lennie ahhoz, hogy a valódi eredményt adják vissza. Ha bármelyik teszt hamis, a hamis eredményt adják vissza. A szintaxis a következő:
Vegyük például a fenti adatokat, egy új oszlopot szeretnék a következőképpen megjeleníteni: ha a termék „Ruha” és 300-nál nagyobb rendelés, akkor adjon 50% kedvezményt az eredeti árból; ellenkező esetben tartsa meg az eredeti árat.
1. Válassza ki az adattáblázatot, és kattintson a gombra dátum > Táblázatból/Tartományból menni Power Query szerkesztő ablak.
2. A megnyíló ablakban kattintson a gombra Oszlop hozzáadása > Egyéni oszlop. A nyitottban Egyéni oszlop párbeszédpanelen tegye a következőket:
- Adja meg az új oszlop nevét a Új oszlopnév szövegdoboz;
- Ezután írja be az alábbi képletet a Egyéni oszlopképlet doboz.
- = ha [Termék] ="Ruha" és [Rendelés] > 300, akkor [Ár]*0.5
különben [ár]
3. Ezután kattints a gombra OK gombbal térhet vissza a Power Query szerkesztő ablakot, és kap egy új oszlopot a szükséges adatokkal, lásd a képernyőképet:
4. Végül a gombra kattintva töltse be ezeket az adatokat egy új munkalapra Kezdőlap > Bezárás és betöltés > Bezárás és betöltés.
If utasítás VAGY és ÉS logikával
Rendben, az előző példák könnyen érthetők. Most nehezítsük meg. Az ÉS és VAGY kombinációjával bármilyen elképzelhető feltételt alkothat. Ebben a típusban zárójeleket használhat a képletben összetett szabályok meghatározásához.
Vegyük példaként a fenti adatokat is, tegyük fel, hogy egy új oszlopot a következőképpen akarok megjeleníteni: ha a termék „Ruha” és a rendelése nagyobb, mint 300, vagy a termék „Nadrág” és a rendelése nagyobb, mint 300, akkor mutassa meg „A+”, különben az „Egyéb” megjelenítése.
1. Válassza ki az adattáblázatot, és kattintson a gombra dátum > Táblázatból/Tartományból menni Power Query szerkesztő ablak.
2. A megnyíló ablakban kattintson a gombra Oszlop hozzáadása > Egyéni oszlop. A nyitottban Egyéni oszlop párbeszédpanelen tegye a következőket:
- Adja meg az új oszlop nevét a Új oszlopnév szövegdoboz;
- Ezután írja be az alábbi képletet a Egyéni oszlopképlet doboz.
- =if ([Termék] = "Ruha" és [Rendelés] > 300 ) vagy
([Termék] = "Nadrág" és [Rendelés] > 300)
majd "A+"
más "egyéb"
3. Ezután kattints a gombra OK gombbal térhet vissza a Power Query szerkesztő ablakot, és kap egy új oszlopot a szükséges adatokkal, lásd a képernyőképet:
4. Végül a gombra kattintva töltse be ezeket az adatokat egy új munkalapra Kezdőlap > Bezárás és betöltés > Bezárás és betöltés.
Az Egyéni oszlopképlet mezőben a következő logikai operátorokat használhatja:
- = : Egyenlő
- <> : Nem egyenlő
- > : Nagyobb mint
- >= : nagyobb vagy egyenlő
- < : Kevesebb mint
- <= : Kisebb vagy egyenlő
A legjobb irodai hatékonyságnövelő eszközök
Töltsd fel Excel-készségeidet a Kutools for Excel segítségével, és tapasztald meg a még soha nem látott hatékonyságot. A Kutools for Excel több mint 300 speciális funkciót kínál a termelékenység fokozásához és az időmegtakarításhoz. Kattintson ide, hogy megszerezze a leginkább szükséges funkciót...
Az Office lap füles felületet hoz az Office-ba, és sokkal könnyebbé teszi a munkáját
- Füles szerkesztés és olvasás engedélyezése Wordben, Excelben és PowerPointban, Publisher, Access, Visio és Project.
- Több dokumentum megnyitása és létrehozása ugyanazon ablak új lapjain, mint új ablakokban.
- 50% -kal növeli a termelékenységet, és naponta több száz kattintással csökkenti az egér kattintását!