Obsługa wielu warunków w formule JEŻELI. W tym artykule porównano trzy różne metody.
Kiedy musisz wykonać obliczenia warunkowe, odpowiedzią jest funkcja JEŻELI. Jeśli to inaczej. Na poniższym rysunku proste IF oblicza premię, jeśli sprzedaż wyniosła 20 000 USD lub więcej.
Ale co się dzieje, gdy trzeba spełnić dwa warunki? Większość ludzi zagnieździ jedną instrukcję IF w drugiej, na przykład:
Ale to wymyka się spod kontroli, jeśli masz wiele warunków, które muszą być spełnione. Funkcja AND skróci i uprości formułę. = AND (Test, Test, Test, Test) będzie True tylko wtedy, gdy wszystkie testy logiczne mają wartość True. Poniższy przykład przedstawia krótszą formułę z tymi samymi wynikami.
Jeśli podoba Ci się AND, możesz znaleźć zastosowanie dla LUB i NIE. = LUB (Test, Test, Test, Test) będzie True, jeśli którykolwiek z testów logicznych ma wartość True. NIE odwróci odpowiedzi. =NOT(True)to fałsz. =NOT(False)jest prawdziwy. Jeśli kiedykolwiek będziesz musiał zrobić coś wymyślnego, takiego jak NAND, możesz tego NIE (AND (Test, Test, Test, Test)).
Uwaga
Chociaż program Excel 2013 wprowadził XOR jako wyłączny lub, nie działa tak, jak oczekiwaliby księgowi. =XOR(True,False,True,True)jest prawdą z powodów, których wyjaśnienie tutaj jest zbyt skomplikowane. XOR naprawdę liczy, czy masz nieparzystą liczbę wartości True. Dziwny. Naprawdę dziwne.
Wskazówka bonusowa
Korzystanie z logiki boolowskiej
Na moich seminariach zawsze omawiam IF. I zawsze pytam, jak ludzie rozwiązaliby problem dwóch warunków. Wyniki są często takie same; 70-80% ludzi używa zagnieżdżonego IF, a 20-30% używa AND. Tylko raz w Wirginii kobieta z Price Waterhouse zaproponowała taką formułę:
To działa. Daje tę samą odpowiedź, co inne formuły. Oblicz premię .02 * B4. Ale potem pomnóż tę premię przez testy logiczne w nawiasach. Kiedy zmuszasz Excela do mnożenia liczby przez Prawdę lub Fałsz, Prawda staje się 1, a Fałsz staje się 0. Dowolna liczba razy 1 jest sobą. Dowolna liczba pomnożona przez 0 daje 0. Pomnożenie premii przez warunki zapewnia, że wypłacane są tylko te wiersze, które spełniają oba warunki.
To jest super. To działa. Ale wydaje się to zagmatwane, kiedy pierwszy raz to widzisz. Mój żart na seminarium zawsze brzmi: „Jeśli odchodzisz z pracy w przyszłym miesiącu i nienawidzisz swoich współpracowników, zacznij stosować tę formułę”.
Obejrzyj wideo
- Najprostszą funkcją JEŻELI jest
=IF(Logical Test,Formula if True, Formula if False) - Ale co zrobić, jeśli musisz przetestować dwa warunki?
- Wiele osób to zrobi
=IF(Test 1, IF(Test 2, Formula if True, False), False) - To staje się nieporęczne, jeśli 3, 5, 17 warunków!
- Zamiast tego użyj
=IF(AND(t1,t2,t3,t4),Formula if True, Formula if False) - Jeśli lubisz AND, rozważ OR, NOT w innych sytuacjach
- NAND można zrobić za pomocą NOT (AND ())
- NOR można zrobić za pomocą NOT (OR ())
- Zachowaj ostrożność podczas korzystania z XOR, ponieważ wyniki nie są takie, jakich oczekujesz
Transkrypcja wideo
Naucz się programu Excel z podcastu, odcinek 2025 - Wiele warunków w IF!
Będę podcastował całą książkę, „i” w prawym górnym rogu przeniesie Cię do listy odtwarzania dla wszystkich tych podcastów!
W porządku, zaczniemy od najprostszego na świecie przypadku IF, fikcyjnego wiceprezesa ds. Sprzedaży przychodzi do nas i mówi „Hej, w tym miesiącu każdy, kto ma sprzedaż powyżej 20000 USD, otrzyma 2% premii”. W porządku, więc funkcja JEŻELI składa się z trzech części: Test logiczny, który mówi „Czy B4> 20000 USD?” Przecinek, więc co zrobić, jeśli to prawda? Jeśli jest PRAWDA, 0,02 * B4, przecinek, co zrobić, jeśli jest FAŁSZ? Cóż, jeśli nie zarobiłeś 20000 $, bez premii dla ciebie, jest zero, w porządku. Ctrl + Enter, aby to skopiować, i widzisz, że mamy bonus tylko w wierszach powyżej 20000 $, ten jest zamknięty, ale nadal nie ma bonusu, w porządku. Otóż, nigdy nie było planu bonusowego tak prostego, prawda, zawsze istnieje wiele zasad, więc tutaj musimy sprawdzić, czy przychód> 20000 $ ORAZ procent zysku brutto> 50%.
W porządku, a jeśli wiesz, jak to rozwiązać, zastanów się, jak to rozwiążesz, w porządku. Przewiduję, że wielu z was mówi „Cóż, zaczniemy od jednego Instrukcja JEŻELI, a później inna instrukcja JEŻELI podobna do tej, zagnieżdżone IF ”. A potem jest kilku z was, którzy mówią: „Cóż, zróbmy instrukcję JEŻELI, a następnie natychmiast, w nawiasach, przechodzimy do innej funkcji o nazwie AND”. A potem jest sposób, aby to zrobić bez żadnych IF. W porządku, więc przyjrzę się temu przez te, więc oto pierwszy, najczęstszy sposób, zagnieżdżony IF, OK, oto jak większość ludzi by to zrobiła.
= JEŚLI, zrób pierwszy test, sprawdź, czy przychód w B4> 20000 USD, jeśli tak, zrób inny JEŻELI, sprawdź, czy procent zysku brutto> 0,05? Jeśli to PRAWDA, to oba warunki są PRAWDZIWE, możemy zrobić .02 * B4, w przeciwnym razie nie ma premii. OK, ale nie skończyliśmy, zamknij nawiasy wewnętrzne, przecinek, a następnie, jeśli pierwszy test nie był prawdziwy, bez premii, Ctrl + Enter, aby skopiować. Widzisz, że tylko te wiersze, które są powyżej 20000 USD i powyżej 50% procent zysku brutto, otrzymują premię. W porządku, dawno temu był taki okropny limit, w którym nie można było zagnieździć więcej niż 7 stwierdzeń IF. To był bolesny dzień, szczególnie jeśli powoli dodawałeś warunki przez miesiące, a w końcu miałeś jeden, który miał 7 i musiałeś dodać 8. W porządku, cóż, dzisiaj możesz iść do 32, nie sądzę, że powinieneś kiedykolwiek iść do 32,ale jeśli po prostu desperacko potrzebujesz przejść z 7 do 8, to dobrze, dobrze. Więc to jest podejście do zagnieżdżonych instrukcji IF, kiedy prowadzę seminaria na żywo, robi to mniej więcej połowa sali, ale jest o wiele, dużo lepsza droga.
= JEŻELI, a następnie natychmiast przejdź do funkcji o nazwie AND, więc wewnątrz AND umieszczamy wszystkie testy: Czy przychód> 20000, przecinek, to procent zysku brutto> 0,5. Jeśli było więcej testów, wstawiaj przecinki z dodatkowymi testami, a następnie zamknij koniec, wszystko na końcu musi być PRAWDZIWE, aby koniec był PRAWDZIWY. Więc jeśli dojdziemy do tego punktu, jeśli koniec jest PRAWDA, 0,02 * przychodu, w przeciwnym razie 0, to jest krótsza formuła, łatwiej jest wprowadzić, otrzymujesz te same wyniki, życie jest świetne.
W porządku, ze wszystkich seminariów, które odbyłem w ciągu ostatnich 15 lat, tylko raz ktoś wszedł i uderzył mnie tą szaloną formułą. Powiedziała: „Słuchaj, po prostu zrobimy = 0,02 * przychody, w ten sposób”. W porządku, oblicz premię, myślę „Zaraz, poczekaj, to będzie drogie, dasz premię każdemu”. odpowiada: „Czekaj, jeszcze nie skończyłem, razy, a potem w nawiasach umieścimy każdy warunek, czyli przychód> 20000 razy, w nawiasach procent zysku brutto> 0,5”. W porządku, a oto co się dzieje, gdy obliczamy premię, a następnie obliczamy ją na PRAWDA lub FAŁSZ. A kiedy zmuszamy Excela do mnożenia PRAWDA lub FAŁSZ przez liczbę, PRAWDA staje się 1, wszystko * 1 jest sobą, FAŁSZ staje się 0! Dobrze, mamy tutaj 2% * przychodu * 1 * 0, cokolwiek * 0 = 0, prawda,więc to usuwa bonus. To jest logika boolowska, PRAWDA * PRAWDA, 1 * 1 = 1, jeśli albo są FAŁSZEM, albo wszystkie są FAŁSZEM, to da wynik 0 i otrzymamy dokładnie ten sam wynik. Czy myślę, że powinieneś przełączyć się na ten? Nie, to zagmatwane, chyba że odchodzisz z pracy w przyszłym tygodniu i nienawidzisz swoich współpracowników, więc możesz się na to przełączyć, w porządku.
Jeśli podoba Ci się funkcja AND, istnieją inne funkcje lub kontrole, które sprawdzają, czy którykolwiek z warunków jest PRAWDZIWY, więc to czy to czy to zwróci TRUE. NOT zamieni TRUE na FALSE i FALSE na TRUE, co jest przydatne, gdy próbujesz wykonać logiczne koncepcje NAND lub NOR. NAND oznacza nie - i jest PRAWDA, gdy przynajmniej jeden warunek jest FAŁSZ, w porządku. Więc jeśli żaden z nich nie jest PRAWDZIWY, to świetnie, jeśli kilka z nich jest PRAWDZIWYCH, to świetnie, ale jak tylko wszystkie są PRAWDZIWE, wtedy nie płacimy. NOR oznacza nie - lub oznacza, że żaden z warunków nie jest PRAWDZIWY - jeśli tak się stanie, lub tak się stanie, nie otrzymasz premii. A potem XOR, teraz uważaj na ten, został wprowadzony w programie Excel 2013 i nie robi tego, co my, księgowi, sądzimy, że powinien.Ekskluzywny - lub oznacza, że tylko jeden z testów jest PRAWDA i działa, gdy są dwa warunki. Ale dla inżynierów elektryków robią to w parze, więc nie daje to rezultatów, o których można by pomyśleć.
W porządku, więc oto Test 1, Test 2, Test 3, Test 4, trzy z nich są PRAWDZIWE, a XOR mówi „Czy dokładnie jeden z nich jest PRAWDZIWY?” A kiedy robimy ten XOR, mówi „Tak, dokładnie jeden z tych, które są PRAWDZIWE”. a to dlatego, że funkcja Excela powiela działanie bardzo popularnego chipa używanego w elektrotechnice, wiem, to szokujące, prawda? Myślisz, że Excel jest tylko dla księgowych, ale inżynierowie też używają Excela i najwyraźniej dodali XOR dla nich, a nie dla księgowych. Zatem sposób, w jaki to jest oceniane, gdy patrzą na pierwsze dwa: „Czy jeden z tych dwóch jest PRAWDZIWY? Tak!" W porządku, więc otrzymujemy PRAWDA, a następnie biorą odpowiedź z XOR tego i porównują ją z PRAWDĄ: „Czy jedno z tych 2 jest PRAWDZIWE? Nie, 2 z nich to PRAWDA, więc to staje się FAŁSZEM! ” Następnie przyjmują tę odpowiedź i XOR ją z ostatnią,więc robią to w parach, prawda? „Czy jeden z tych dwóch jest PRAWDZIWY? Tak!" W porządku, więc tak to otrzymujemy. Okazuje się, że to, co faktycznie robi, elektrotechnika polega na liczeniu, jeśli nieparzysta liczba wejść jest PRAWDA. Niekoniecznie przydatne dla księgowych, którzy oczekują, że zrobi to, co oznacza XOR w języku angielskim.
W porządku, wiele świetnych wskazówek w tej książce, przydatnych rzeczy, a nawet ta okropna dyskusja o NAND i XOR i tym podobnych. Kup książkę, a wszystkie te wskazówki będziesz mieć w dłoni. Podsumowanie od dzisiaj: Najprostsza funkcja JEŻELI, = test logiczny JEŻELI, co zrobić, jeśli jest PRAWDA, co zrobić, jeśli jest FAŁSZ, ale jeśli masz 2 warunki, wiele osób zagnieżdża instrukcje IF, ale wyobraź sobie, że masz 3 , 5 lub 17 warunków do zagnieżdżenia. I rozwiążę to, trochę skróć to, więc jeśli lubisz AND, jest też OR lub NIE, możesz zrobić NAND, możesz zrobić NOR, ale bądź ostrożny podczas korzystania z tego nowego Excela 2013 XOR, wyniki mogą nie być takie, jakie chcesz oczekiwać.
W porządku hej, chcę ci podziękować za zatrzymanie się, do zobaczenia następnym razem z kolejnym netcastem od!
Pobieranie pliku
Pobierz przykładowy plik tutaj: Podcast2025.xlsx
