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:
- 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.
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%;
- 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
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.
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:
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.
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:
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.
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
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...
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!