Przejdź do głównej zawartości

Power Query: Instrukcja if - zagnieżdżone if i wiele warunków

W Excelu Power Query, instrukcja JEŻELI jest jedną z najpopularniejszych funkcji sprawdzających warunek i zwracających określoną wartość w zależności od tego, czy wynikiem jest PRAWDA, czy FAŁSZ. Istnieją pewne różnice między instrukcją if a funkcją JEŻELI w programie Excel. W tym samouczku przedstawię składnię tej instrukcji if oraz kilka prostych i złożonych przykładów.

Podstawowa składnia instrukcji if Power Query

Power Query if instrukcja wykorzystująca kolumnę warunkową

Power Query if instrukcja poprzez wpisanie kodu M


Podstawowa składnia instrukcji if Power Query

In Power Query, składnia jest następująca:

= jeśli test_logiczny, to wartość_jeśli_prawda, w przeciwnym razie wartość_jeśli_fałsz
  • test logiczny: Warunek, który chcesz przetestować.
  • wartość_jeśli_prawda: Wartość do zwrócenia, jeśli wynikiem jest PRAWDA.
  • wartość_jeżeli_fałsz: Wartość do zwrócenia, jeśli wynikiem jest FAŁSZ.
Note: Power Query if w instrukcji rozróżniana jest wielkość liter, if, then i else muszą być pisane małymi literami.

W Excelu Power Queryistnieją dwa sposoby tworzenia tego typu logiki warunkowej:

  • Korzystanie z funkcji kolumny warunkowej w niektórych podstawowych scenariuszach;
  • Pisanie kodu M dla bardziej zaawansowanych scenariuszy.

W następnej sekcji omówię kilka przykładów użycia instrukcji if.


Power Query if instrukcja wykorzystująca kolumnę warunkową

 Przykład 1: Podstawowa instrukcja if

Tutaj przedstawię, jak używać tej instrukcji if w Power Query. Na przykład mam następujący raport o produkcie, jeśli status produktu to Stary, wyświetlający 50% rabatu; jeśli status produktu to Nowy, wyświetlając 20% zniżki, jak pokazano na poniższych zrzutach ekranu.

1. Wybierz tabelę danych z arkusza, a następnie kliknij w Excel 2019 i Excel 365 Dane > Z tabeli/zakresuzobacz zrzut ekranu:

Note: W programach Excel 2016 i Excel 2021 kliknij Dane > Z tabelizobacz zrzut ekranu:

2. Następnie w otwartym Power Query redaktor okno, kliknij Dodaj kolumnę > Kolumna warunkowazobacz zrzut ekranu:

3. W wyskoczył Dodaj kolumnę warunkową w oknie dialogowym, wykonaj następujące czynności:

  • Nowa nazwa kolumny: Wprowadź nazwę nowej kolumny;
  • Następnie określ potrzebne kryteria. Określę np Jeśli Status jest równy Stary, to 50%, w przeciwnym razie 20%;
Porady:
  • Nazwa kolumny: kolumna do oceny warunku if. Tutaj wybieram Stan.
  • Operator: Logika warunkowa do użycia. Opcje będą się różnić w zależności od typu danych wybranej nazwy kolumny.
    • Tekst: zaczyna się od, nie zaczyna się od, równa się, zawiera itp.
    • Liczby: równa się, nie równa się, jest większa lub równa itp.
    • Data: jest przed, jest po, jest równe, nie jest równe itp.
  • wartość: Konkretna wartość, z którą porównasz swoją ocenę. Wraz z Nazwą kolumny i Operatorem tworzy warunek.
  • Wydajność: Wartość do zwrócenia, jeśli warunek jest spełniony.
  • Więcej: Kolejna wartość do zwrócenia, gdy warunek jest fałszywy.

4. Następnie kliknij OK przycisk, aby wrócić do Power Query redaktor okno. Teraz nowy Zniżka dodano kolumnę, zobacz zrzut ekranu:

5. Jeśli chcesz sformatować liczby w procentach, po prostu kliknij ABC123 ikona od Zniżka nagłówek kolumny i wybierz Odsetek jak potrzebujesz, zobacz zrzut ekranu:

6. Na koniec proszę kliknąć Strona główna > Zamknij i wczytaj > Zamknij i wczytaj aby załadować te dane do nowego arkusza.


 Przykład 2: Złożona instrukcja if

Dzięki tej opcji kolumny warunkowej możesz również wstawić dwa lub więcej warunków do pliku Dodaj kolumnę warunkową dialog. Proszę zrobić tak:

1. Wybierz tabelę danych i przejdź do Power Query redaktor okno klikając Dane > Z tabeli/zakresu. W nowym oknie kliknij Dodaj kolumnę > Kolumna warunkowa.

2. W wyskoczył Dodaj kolumnę warunkową w oknie dialogowym, wykonaj następujące operacje:

  • Wprowadź nazwę nowej kolumny w pliku Nowa nazwa kolumny pole tekstowe;
  • Określ pierwsze kryteria w pierwszym polu kryteriów, a następnie kliknij Dodaj klauzulę przycisk, aby dodać inne pola kryteriów zgodnie z potrzebami.

3. Po uzupełnieniu kryteriów kliknij OK przycisk, aby wrócić do Power Query redaktor okno. Teraz otrzymasz nową kolumnę z odpowiednim wynikiem, którego potrzebujesz. Zobacz zrzut ekranu:

4. Proszę w końcu kliknąć Strona główna > Zamknij i wczytaj > Zamknij i wczytaj aby załadować te dane do nowego arkusza.


Power Query if instrukcja poprzez wpisanie kodu M

Zwykle kolumna warunkowa jest pomocna w niektórych podstawowych scenariuszach. Czasami może być konieczne użycie wielu warunków z logiką AND lub OR. W takim przypadku musisz napisać kod M w kolumnie niestandardowej dla bardziej złożonych scenariuszy.

 Przykład 1: Podstawowa instrukcja if

Weź jako przykład pierwsze dane, jeśli status produktu to Stary, wyświetlając 50% rabatu; jeśli status produktu to Nowy, wyświetla 20% rabatu. Aby napisać kod M, wykonaj następujące czynności:

1. Wybierz tabelę i kliknij Dane > Z tabeli/zakresu , aby przejść do Power Query redaktor okno.

2. W otwartym oknie kliknij Dodaj kolumnę > Kolumna niestandardowazobacz zrzut ekranu:

3. W wyskoczył Kolumna niestandardowa w oknie dialogowym, wykonaj następujące operacje:

  • Wprowadź nazwę nowej kolumny w pliku Nowa nazwa kolumny pole tekstowe;
  • Następnie wprowadź tę formułę: if [Status] = „Stary”, to „50%”, w przeciwnym razie „20%” do Kolumna niestandardowa formuła pudełko.

4. Następnie kliknij OK aby zamknąć to okno dialogowe. Teraz otrzymasz następujący wynik, zgodnie z potrzebami:

5. Nareszcie kliknij Strona główna > Zamknij i wczytaj > Zamknij i wczytaj aby załadować te dane do nowego arkusza.


 Przykład 2: Złożona instrukcja if

Zagnieżdżone instrukcje if

Zwykle, aby przetestować warunki podrzędne, można zagnieżdżać wiele instrukcji if. Na przykład mam poniższą tabelę danych. Jeśli produkt to „Sukienka”, daj 50% rabatu na pierwotną cenę; jeśli produktem jest „Sweter” lub „Bluza”, udziel rabatu 20% od ceny pierwotnej; a inne produkty zachowują pierwotną cenę.

1. Wybierz tabelę danych i kliknij Dane > Z tabeli/zakresu , aby przejść do Power Query redaktor okno.

2. W otwartym oknie kliknij Dodaj kolumnę > Kolumna niestandardowa. W otwartym Kolumna niestandardowa w oknie dialogowym, wykonaj następujące operacje:

  • Wprowadź nazwę nowej kolumny w pliku Nowa nazwa kolumny pole tekstowe;
  • Następnie wprowadź poniższą formułę do pliku Kolumna niestandardowa formuła pudełko.
  • = jeśli [Produkt] = „Sukienka”, to [Cena] * 0.5 w przeciwnym razie
    jeśli [Produkt] = „Sweter”, to [Cena] * 0.8 w przeciwnym razie
    jeśli [Produkt] = „Bluza z kapturem”, to [Cena] * 0.8
    inne [cena]

3. A następnie kliknij OK przycisk, aby wrócić do Power Query redaktor okno, a otrzymasz nową kolumnę z potrzebnymi danymi, zobacz zrzut ekranu:

4. Nareszcie kliknij Strona główna > Zamknij i wczytaj > Zamknij i wczytaj aby załadować te dane do nowego arkusza.


Instrukcja if z logiką OR

Logika OR wykonuje wiele testów logicznych, a prawdziwy wynik zostanie zwrócony, jeśli którykolwiek z testów logicznych się powiedzie. Składnia to:

= jeśli test_logiczny1 lub test_logiczny2 lub … to wartość_jeśli_prawda w przeciwnym razie wartość_jeśli_fałsz

Załóżmy, że mam poniższą tabelę, teraz chcę wyświetlić nową kolumnę w następujący sposób: jeśli produkt to „Sukienka” lub „T-shirt”, to marka to „AAA”, marka innych produktów to „BBB”.

1. Wybierz tabelę danych i kliknij Dane > Z tabeli/zakresu , aby przejść do Power Query redaktor okno.

2. W otwartym oknie kliknij Dodaj kolumnę > Kolumna niestandardowa, w otwartym Kolumna niestandardowa w oknie dialogowym, wykonaj następujące operacje:

  • Wprowadź nazwę nowej kolumny w pliku Nowa nazwa kolumny pole tekstowe;
  • Następnie wprowadź poniższą formułę do pliku Niestandardowa formuła kolumny pudełko.
  • = jeśli [Produkt] = „Sukienka” lub [Produkt] = „T-shirt”, to „AAA”
    inaczej „BBB”

3. A następnie kliknij OK przycisk, aby wrócić do Power Query redaktor okno, a otrzymasz nową kolumnę z potrzebnymi danymi, zobacz zrzut ekranu:

4. Na koniec kliknij Strona główna > Zamknij i wczytaj > Zamknij i wczytaj aby załadować te dane do nowego arkusza.


Instrukcja if z logiką AND

Logika AND wykonuje wiele testów logicznych w jednej instrukcji if. Wszystkie testy muszą być prawdziwe, aby został zwrócony prawdziwy wynik. Jeśli którykolwiek z testów jest fałszywy, zwracany jest fałszywy wynik. Składnia to:

= jeśli test_logiczny1 i test_logiczny2 i … to wartość_jeśli_prawda, w przeciwnym razie wartość_jeśli_fałsz

Weźmy na przykład powyższe dane, chcę, aby nowa kolumna wyświetlała się jako: jeśli produkt to „Sukienka” i zamówienie większe niż 300, to daj 50% zniżki na pierwotną cenę; w przeciwnym razie zachowaj pierwotną cenę.

1. Wybierz tabelę danych i kliknij Dane > Z tabeli/zakresu , aby przejść do Power Query redaktor okno.

2. W otwartym oknie kliknij Dodaj kolumnę > Kolumna niestandardowa. W otwartym Kolumna niestandardowa w oknie dialogowym, wykonaj następujące operacje:

  • Wprowadź nazwę nowej kolumny w pliku Nowa nazwa kolumny pole tekstowe;
  • Następnie wprowadź poniższą formułę do pliku Niestandardowa formuła kolumny pudełko.
  • = jeśli [Produkt] ="Sukienka" i [Zamówienie] > 300, to [Cena]*0.5
    inne [cena]

3. Następnie kliknij OK przycisk, aby wrócić do Power Query redaktor okno, a otrzymasz nową kolumnę z potrzebnymi danymi, zobacz zrzut ekranu:

4. Na koniec należy załadować te dane do nowego arkusza, klikając Strona główna > Zamknij i wczytaj > Zamknij i wczytaj.


Instrukcja if z OR i AND Logika

W porządku, poprzednie przykłady są dla nas łatwe do zrozumienia. Teraz zróbmy to trudniej. Możesz łączyć AND i OR, aby utworzyć dowolny warunek, jaki możesz sobie wyobrazić. W tym typie możesz użyć nawiasów w formule, aby zdefiniować złożone reguły.

Weź powyższe dane jako przykład, załóżmy, że chcę, aby nowa kolumna wyświetlała się w następujący sposób: jeśli produktem jest „Sukienka” i jego zamówienie jest większe niż 300 lub produkt to „Spodnie” i jego zamówienie jest większe niż 300, to pokaż „A+”, w przeciwnym razie wyświetl „Inne”.

1. Wybierz tabelę danych i kliknij Dane > Z tabeli/zakresu , aby przejść do Power Query redaktor okno.

2. W otwartym oknie kliknij Dodaj kolumnę > Kolumna niestandardowa. W otwartym Kolumna niestandardowa w oknie dialogowym, wykonaj następujące operacje:

  • Wprowadź nazwę nowej kolumny w pliku Nowa nazwa kolumny pole tekstowe;
  • Następnie wprowadź poniższą formułę do pliku Niestandardowa formuła kolumny pudełko.
  • =if ([Produkt] = "Sukienka" i [Zamówienie] > 300 ) lub
    ([Produkt] = "Spodnie" i [Zamówienie] > 300 )
    następnie „A+”
    inaczej „Inne”

3. Następnie kliknij OK przycisk, aby wrócić do Power Query redaktor okno, a otrzymasz nową kolumnę z potrzebnymi danymi, zobacz zrzut ekranu:

4. Na koniec należy załadować te dane do nowego arkusza, klikając Strona główna > Zamknij i wczytaj > Zamknij i wczytaj.

Porady:
W polu Formuła kolumny niestandardowej możesz użyć następujących operatorów logicznych:
  • = : Równe
  • <> : Nie równe
  • > : Większy niż
  • >= : Większy lub równy
  • < : Mniej niż
  • <= : Mniejsze lub równe

Najlepsze narzędzia biurowe

🤖 Pomocnik AI Kutools: Zrewolucjonizuj analizę danych w oparciu o: Inteligentne wykonanie   |  Wygeneruj kod  |  Twórz niestandardowe formuły  |  Analizuj dane i generuj wykresy  |  Wywołaj funkcje Kutools...
Popularne funkcje: Znajdź, wyróżnij lub zidentyfikuj duplikaty   |  Usuń puste wiersze   |  Łącz kolumny lub komórki bez utraty danych   |   Okrągły bez wzoru ...
Super wyszukiwanie: Wiele kryteriów VLookup    Wiele wartości VLookup  |   Przeglądanie pionowe na wielu arkuszach   |   Wyszukiwanie rozmyte ....
Zaawansowana lista rozwijana: Szybko twórz listę rozwijaną   |  Zależna lista rozwijana   |  Lista rozwijana wielokrotnego wyboru ....
Menedżer kolumn: Dodaj określoną liczbę kolumn  |  Przesuń kolumny  |  Przełącz stan widoczności ukrytych kolumn  |  Porównaj zakresy i kolumny ...
Polecane funkcje: Fokus siatki   |  Widok projektu   |   Duży pasek formuły    Menedżer skoroszytów i arkuszy   |  Biblioteka zasobów (Automatyczny tekst)   |  Selektor dat   |  Połącz arkusze   |  Szyfruj/odszyfruj komórki    Wysyłaj e-maile według listy   |  Super filtr   |   Specjalny filtr (filtruj pogrubienie/kursywa/przekreślenie...) ...
15 najlepszych zestawów narzędzi12 Tekst Tools (Dodaj tekst, Usuń znaki, ...)   |   50 + Wykres rodzaje (Wykres Gantta, ...)   |   40+ Praktyczne Wzory (Oblicz wiek na podstawie urodzin, ...)   |   19 Wprowadzenie Tools (Wstaw kod QR, Wstaw obraz ze ścieżki, ...)   |   12 Konwersja Tools (Liczby na słowa, Przeliczanie walut, ...)   |   7 Połącz i podziel Tools (Zaawansowane wiersze łączenia, Podział komórki, ...)   |   ... i więcej

Zwiększ swoje umiejętności Excela dzięki Kutools for Excel i doświadcz wydajności jak nigdy dotąd. Kutools dla programu Excel oferuje ponad 300 zaawansowanych funkcji zwiększających produktywność i oszczędzających czas.  Kliknij tutaj, aby uzyskać funkcję, której najbardziej potrzebujesz...

Opis


Karta Office wprowadza interfejs z zakładkami do pakietu Office i znacznie ułatwia pracę

  • Włącz edycję i czytanie na kartach w programach Word, Excel, PowerPoint, Publisher, Access, Visio i Project.
  • Otwieraj i twórz wiele dokumentów w nowych kartach tego samego okna, a nie w nowych oknach.
  • Zwiększa produktywność o 50% i redukuje setki kliknięć myszką każdego dnia!
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