Power Query: Instrucțiunea If - if-uri imbricate și condiții multiple
În Excel Power Query, instrucțiunea IF este una dintre cele mai populare funcții pentru a verifica o condiție și a returna o anumită valoare, în funcție de dacă rezultatul este TRUE sau FALSE. Există unele diferențe între această declarație if și funcția IF din Excel. În acest tutorial, voi introduce sintaxa acestei instrucțiuni if și câteva exemple simple și complexe pentru dvs.
Sintaxa de bază a instrucțiunii if of Power Query
Power Query if utilizând o coloană condiționată
Power Query declarația if prin scrierea codului M
Sintaxa de bază a instrucțiunii if of Power Query
In Power Query, sintaxa este:
- test_logic: Condiția pe care doriți să o testați.
- valoare_dacă_adevărată: Valoarea de returnat dacă rezultatul este TRUE.
- valoare_dacă_falsă: Valoarea de returnat dacă rezultatul este FALS.
În Excel Power Query, există două moduri de a crea acest tip de logică condiționată:
- Utilizarea caracteristicii Coloană condiționată pentru unele scenarii de bază;
- Scrierea codului M pentru scenarii mai avansate.
În secțiunea următoare, voi vorbi despre câteva exemple de utilizare a acestei declarații if.
Power Query if utilizând o coloană condiționată
Exemplul 1: instrucțiunea if de bază
Aici, voi prezenta cum să folosiți această declarație if în Power Query. De exemplu, am următorul raport de produs, dacă starea produsului este Vechi, afișând o reducere de 50%; dacă starea produsului este Nou, se afișează o reducere de 20%, așa cum se arată mai jos.
1. Selectați tabelul de date din foaia de lucru, apoi, în Excel 2019 și Excel 365, faceți clic Date > Din masă/gamă, vezi captura de ecran:
notițe: În Excel 2016 și Excel 2021, faceți clic Date > Din tabel, vezi captura de ecran:
2. Apoi, în deschidere Power Query Editor fereastră, faceți clic pe Adăugați o coloană > Coloana Condițională, vezi captura de ecran:
3. În pop-out Adăugați o coloană condiționată dialog, vă rugăm să efectuați următoarele operații:
- Nou nume de coloană: Introduceți un nume pentru noua coloană;
- Apoi, specificați criteriile de care aveți nevoie. De exemplu, voi preciza Dacă starea este egală cu Vechi, atunci 50% altfel 20%;
- Nume coloană: Coloana pentru a evalua condiția dumneavoastră dacă. Aici, selectez Status.
- Operator: Logica condiționată de utilizat. Opțiunile vor diferi în funcție de tipul de date al Numelui coloanei selectat.
- Text: începe cu, nu începe cu, egalează, conține etc.
- Numere: este egal, nu este egal, este mai mare sau egal cu etc.
- Data: este înainte, este după, este egal cu, nu este egal etc.
- Valoare: valoarea specifică cu care să vă comparați evaluarea. Împreună cu Numele coloanei și Operatorul formează o condiție.
- producție: Valoarea de returnat dacă condiția este îndeplinită.
- Altfel: O altă valoare de returnat atunci când condiția este falsă.
4. Apoi apasa OK butonul pentru a reveni la Power Query Editor fereastră. Acum, un nou Reducere coloana este adăugată, vezi captura de ecran:
5. Dacă doriți să formatați numerele în procente, faceți clic pe ABC123 pictogramă din Reducere antetul coloanei și alegeți Procent CBD după cum aveți nevoie, vedeți captura de ecran:
6. În cele din urmă, dați clic Home > Închideți și încărcați > Închideți și încărcați pentru a încărca aceste date într-o nouă foaie de lucru.
Exemplul 2: Instrucțiune if complex
Cu această opțiune de coloană condiționată, puteți introduce, de asemenea, două sau mai multe condiții în Adăugați o coloană condiționată dialog. Vă rugăm să faceți așa:
1. Selectați tabelul de date și accesați Power Query Editor fereastră făcând clic pe Date > Din masă/gamă. În fereastra nouă, faceți clic Adăugați o coloană > Coloana Condițională.
2. În pop-out Adăugați o coloană condiționată caseta de dialog, vă rugăm să efectuați următoarele operații:
- Introduceți un nume pentru noua coloană în Nou nume de coloană casetă de text;
- Specificați primele criterii în primul câmp de criterii, apoi faceți clic Adăugați o clauză butonul pentru a adăuga alte câmpuri de criterii după cum aveți nevoie.
3. După ce ați terminat criteriile, faceți clic OK butonul pentru a reveni la Power Query Editor fereastră. Acum, veți obține o nouă coloană cu rezultatul corespunzător de care aveți nevoie. Vedeți captura de ecran:
4. În cele din urmă, dați clic Home > Închideți și încărcați > Închideți și încărcați pentru a încărca aceste date într-o nouă foaie de lucru.
Power Query declarația if prin scrierea codului M
În mod normal, coloana condiționată este utilă pentru unele scenarii de bază. Uneori, poate fi necesar să utilizați mai multe condiții cu logica AND sau OR. În acest caz, trebuie să scrieți cod M într-o coloană personalizată pentru scenarii mai complexe.
Exemplul 1: instrucțiunea if de bază
Luați ca exemplu primele date, dacă starea produsului este Vechi, afișând o reducere de 50%; dacă starea produsului este Nou, afișând o reducere de 20%. Pentru a scrie codul M, procedați astfel:
1. Selectați tabelul și faceți clic pe Date > Din masă/gamă a merge la Power Query Editor fereastră.
2. În fereastra deschisă, faceți clic Adăugați o coloană > Coloană personalizată, vezi captura de ecran:
3. În pop-out Coloană personalizată caseta de dialog, vă rugăm să efectuați următoarele operații:
- Introduceți un nume pentru noua coloană în Nou nume de coloană casetă de text;
- Apoi, introduceți această formulă: dacă [Stare] = „Vechi”, atunci „50%”, altfel „20%” în Coloană personalizată formulă cutie.
4. Apoi apasa OK pentru a închide această casetă de dialog. Acum, veți obține următorul rezultat după cum aveți nevoie:
5. În cele din urmă, faceți clic pe Home > Închideți și încărcați > Închideți și încărcați pentru a încărca aceste date într-o nouă foaie de lucru.
Exemplul 2: Instrucțiune if complex
De obicei, pentru a testa subcondițiile, puteți imbrica mai multe instrucțiuni if. De exemplu, am tabelul de date de mai jos. Dacă produsul este „Rochie”, acordați o reducere de 50% pentru prețul inițial; dacă produsul este „pulover” sau „hanocă”, acordați o reducere de 20% pentru prețul inițial; iar alte produse păstrează prețul inițial.
1. Selectați tabelul de date și faceți clic Date > Din masă/gamă a merge la Power Query Editor fereastră.
2. În fereastra deschisă, faceți clic Adăugați o coloană > Coloană personalizată. În deschise Coloană personalizată caseta de dialog, vă rugăm să efectuați următoarele operații:
- Introduceți un nume pentru noua coloană în Nou nume de coloană casetă de text;
- Apoi, introduceți formula de mai jos în Coloană personalizată formulă cutie.
- = dacă [Produs] = „Rochie”, atunci [Preț] * 0.5 altfel
dacă [Produs] = „Puover”, atunci [Preț] * 0.8 altfel
dacă [Produs] = „Haorac”, atunci [Preț] * 0.8
altfel [Preț]
3. Apoi, faceți clic OK butonul pentru a reveni la Power Query Editor fereastră și veți obține o nouă coloană cu datele de care aveți nevoie, vedeți captura de ecran:
4. În cele din urmă, faceți clic pe Home > Închideți și încărcați > Închideți și încărcați pentru a încărca aceste date într-o nouă foaie de lucru.
Logica SAU efectuează mai multe teste logice, iar rezultatul adevărat va reveni dacă oricare dintre testele logice este tura. Sintaxa este:
Presupunând că am tabelul de mai jos, acum vreau o nouă coloană afișată ca: dacă produsul este „Rochie” sau „T-shirt”, atunci marca este „AAA”, marca altor produse este „BBB”.
1. Selectați tabelul de date și faceți clic Date > Din masă/gamă a merge la Power Query Editor fereastră.
2. În fereastra deschisă, faceți clic Adăugați o coloană > Coloană personalizată, în deschise Coloană personalizată caseta de dialog, vă rugăm să efectuați următoarele operații:
- Introduceți un nume pentru noua coloană în Nou nume de coloană casetă de text;
- Apoi, introduceți formula de mai jos în Formula personalizată pentru coloană cutie.
- = dacă [Produs] = „Rochie” sau [Produs] = „T-shirt”, apoi „AAA”
altfel "BBB"
3. Apoi, faceți clic OK butonul pentru a reveni la Power Query Editor fereastră și veți obține o nouă coloană cu datele de care aveți nevoie, vedeți captura de ecran:
4. În cele din urmă, faceți clic pe Home > Închideți și încărcați > Închideți și încărcați pentru a încărca aceste date într-o nouă foaie de lucru.
Logica AND efectuează mai multe teste logice în interiorul unei singure instrucțiuni if. Toate testele trebuie să fie adevărate pentru ca rezultatul adevărat să fie returnat. Dacă oricare dintre teste este fals, rezultatul fals este returnat. Sintaxa este:
Luați de exemplu datele de mai sus, vreau să se afișeze o nouă coloană ca: dacă produsul este „Rochie” și comanda mai mult de 300, atunci acordați o reducere de 50% pentru prețul inițial; altfel păstrați prețul inițial.
1. Selectați tabelul de date și faceți clic Date > Din masă/gamă a merge la Power Query Editor fereastră.
2. În fereastra deschisă, faceți clic Adăugați o coloană > Coloană personalizată. În deschise Coloană personalizată caseta de dialog, vă rugăm să efectuați următoarele operații:
- Introduceți un nume pentru noua coloană în Nou nume de coloană casetă de text;
- Apoi, introduceți formula de mai jos în Formula personalizată pentru coloană cutie.
- = dacă [Produs] ="Rochie" și [Comandă] > 300, atunci [Preț]*0.5
altfel [Preț]
3. Apoi apasa OK butonul pentru a reveni la Power Query Editor fereastră și veți obține o nouă coloană cu datele de care aveți nevoie, vedeți captura de ecran:
4. În cele din urmă, ar trebui să încărcați aceste date într-o nouă foaie de lucru făcând clic Home > Închideți și încărcați > Închideți și încărcați.
Instrucțiunea If cu logici SAU și AND
Bine, exemplele anterioare sunt ușor de înțeles pentru noi. Acum, să facem totul mai greu. Puteți combina ȘI și SAU pentru a forma orice condiție pe care ți-o poți imagina. În acest tip, puteți utiliza paranteze în formulă pentru a defini reguli complexe.
Luați și datele de mai sus ca exemplu, să presupunem că vreau să se afișeze o nouă coloană ca: dacă produsul este „Rochie” și comanda sa este mai mare de 300 sau produsul este „Pantaloni” și comanda sa mai mare de 300, atunci afișați „A+”, altfel, afișați „Altele”.
1. Selectați tabelul de date și faceți clic Date > Din masă/gamă a merge la Power Query Editor fereastră.
2. În fereastra deschisă, faceți clic Adăugați o coloană > Coloană personalizată. În deschise Coloană personalizată caseta de dialog, vă rugăm să efectuați următoarele operații:
- Introduceți un nume pentru noua coloană în Nou nume de coloană casetă de text;
- Apoi, introduceți formula de mai jos în Formula personalizată pentru coloană cutie.
- =dacă ([Produs] = „Rochie” și [Comandă] > 300 ) sau
([Produs] = „Pantaloni” și [Comandă] > 300)
apoi "A+"
altfel "Altul"
3. Apoi apasa OK butonul pentru a reveni la Power Query Editor fereastră și veți obține o nouă coloană cu datele de care aveți nevoie, vedeți captura de ecran:
4. În cele din urmă, ar trebui să încărcați aceste date într-o nouă foaie de lucru făcând clic Home > Închideți și încărcați > Închideți și încărcați.
În caseta Formulă de coloană personalizată, puteți utiliza următorii operatori logici:
- = : Egal
- <> : Nu este egal cu
- > : Mai mare decât
- >= : Mai mare sau egal cu
- < : Mai puțin de
- <= : Mai mic sau egal cu
Cele mai bune instrumente de productivitate de birou
Îmbunătățiți-vă abilitățile Excel cu Kutools pentru Excel și experimentați eficiența ca niciodată. Kutools pentru Excel oferă peste 300 de funcții avansate pentru a crește productivitatea și a economisi timp. Faceți clic aici pentru a obține funcția de care aveți cea mai mare nevoie...
Fila Office aduce interfața cu file în Office și vă face munca mult mai ușoară
- Activați editarea și citirea cu file în Word, Excel, PowerPoint, Publisher, Access, Visio și Project.
- Deschideți și creați mai multe documente în filele noi ale aceleiași ferestre, mai degrabă decât în ferestrele noi.
- Vă crește productivitatea cu 50% și reduce sute de clicuri de mouse pentru dvs. în fiecare zi!