Ugrás a tartalomra

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ő:

= ha logikai_teszt, akkor érték_ha_igaz else érték_ha_hamis
  • 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.
Megjegyzések: Power Query ha az utasítás megkülönbözteti a kis- és nagybetűket, akkor az if, then és else-nek kisbetűnek kell lennie.

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%;
Tipp:
  • 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

Beágyazott if utasítások

Á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.


Ha utasítás VAGY logikával

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ő:

= if logikai_teszt1 vagy logikai_teszt2 vagy … akkor érték_ha_igaz else érték_ha_hamis

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.


If utasítás ÉS logikával

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ő:

= if logikai_teszt1 és logikai_teszt2 és … akkor érték_ha_igaz else érték_ha_hamis

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.

Tipp:
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

Népszerű szolgáltatások: Ismétlődések keresése, kiemelése vagy azonosítása   |  Üres sorok törlése   |  Oszlopok vagy cellák kombinálása adatvesztés nélkül   |   Kerek Formula nélkül ...
Szuper keresés: Több kritérium VLookup    Többértékű VLookup  |   VLookup több munkalapon   |   Fuzzy Lookup ....
Speciális legördülő lista: Gyors legördülő lista létrehozása   |  Függő legördülő lista   |  Többszörösen válassza ki a legördülő listát ....
Oszlopkezelő: Adjon meg egy adott számú oszlopot  |  Oszlopok mozgatása  |  Kapcsolja be a Rejtett oszlopok láthatósági állapotát  |  Tartományok és oszlopok összehasonlítása ...
Kiemelt funkciók: Rács fókusz   |  Design nézet   |   Nagy Formula bár    Munkafüzet és lapkezelő   |  Erőforrás-könyvtár (Auto szöveg)   |  Dátumválasztó   |  Kombinálja a munkalapokat   |  Cellák titkosítása/dekódolása    E-mailek küldése listánként   |  Szuper szűrő   |   Speciális szűrő (félkövér/dőlt/áthúzott szűrés...) ...
A 15 legjobb eszközkészlet12 szöveg Eszközök (Szöveg hozzáadása, Karakterek eltávolítása,...)   |   50 + Táblázatos Típusai (Gantt-diagram,...)   |   40+ Praktikus képletek (Számolja ki az életkort a születésnap alapján,...)   |   19 beszúrás Eszközök (Helyezze be a QR-kódot, Kép beszúrása az útvonalból,...)   |   12 Átalakítás Eszközök (Számok szavakig, Valuta átváltás,...)   |   7 Egyesítés és felosztás Eszközök (Haladó kombinált sorok, Hasított sejtek,...)   |   ... és több

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...

kte lap 201905


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!
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations