Krok 5: Wybierz ogranicznik, który oddziela dane w komórce. Krok 6: Wybierz format komórek docelowych. Podążając za tymi krokami i używając funkcji tekstu do kolumn w programie Excel, możesz łatwo podzielić komórkę na dwie lub więcej komórek na podstawie określonego ogranicznika. Metoda 2: Za pomocą wzorów i funkcji Jednym z nich jest użycie kombinacji klawiszy Alt + Enter. Po wpisaniu tekstu w komórce, naciśnięcie tych klawiszy spowoduje przeniesienie do nowej linii w tej samej komórce. Można to wykorzystać, na przykład, do rozdzielenia danych w jednej komórce na kilka linii. Innym sposobem jest użycie funkcji CHAR(10). Po wpisaniu formuły Nie byłoby w tym nic trudnego gdyby w tej komorce znadowal sie tekst tylko dla jednego rekordu a wynik wyszukaj pionowo mialby trafic do komorki obok. Zakładamy ze C1 wygląda tak: xxx-yyy-zzz-aaa-bbb-ccc. W drugim skoroszycie mam listę gdzie w kolumnie A jest w oddzielnej komórce po kolei (aaa, bbb, ccc, xxx, yyy, zzz) a w kolumnie B 1. Zastosowanie tej funkcji spowoduje zmianę oryginalnych danych, lepiej najpierw je skopiować. 2. Tylko wszystkie ciągi w każdej komórce mają kilka znaków, dzięki czemu ta funkcja może działać. Kliknij tutaj, aby uzyskać więcej informacji na temat usuwania według pozycji. Funkcja JEŻELI jest jedną z najważniejszych funkcji Excela. Jest to tzw. funkcja warunkowa. Za jej pomocą możemy skonstruować warunek. Na podstawie tego warunku możemy przypisywać wartości do danych. Dla przykładu w komórce A1 mamy dowolną liczbę. W komórce B1 chcemy sprawdzić, czy ta liczba jest większa od 100. Zacznijmy od podstawowych wiadomości. JEŻELI należy do grupy funkcji logicznych, czyli takich, które testują pewne warunki i na tej podstawie wyświetlają odpowiedni wynik. Funkcja ta może generować tylko dwa wyniki: pierwszy otrzymamy, jeśli wynik jest prawdziwy, a drugi — jeśli wynik jest fałszywy. Jej składnia wygląda Używając SUMIF + SUMIF +…formuła. Jeśli chcesz zsumować liczby spełniające którekolwiek z kryteriów (logika OR) z wielu kryteriów, możesz dodać kilka funkcji SUMA.JEŻELI w jednej formule, ogólna składnia to: =SUMIF (criteria_range, criteria1, sum_range)+SUMIF (criteria_range, criteria2, sum_range)+…. criteria_range: zakres W formatowaniu warunkowym zastosuj taki mniej więcej ciąg myślowy: jeżeli dziś >= data_wpisana +80 to koloruj na czerwono. Osobno w jakiejś tam kolumnie (przyjmijmy, że jest to komórka G4) wpisujesz normalny warunek: jeżeli dziś >= data wpisana to G4="ŹLE" w przeciwnym razie G4="OK". Wystarczy to podstawić do funkcji, celowo Iść do kartę i kliknij Formatowanie warunkowe. Nowa reguła formatowania dialog, wybierz Użyj formuły, aby określić, które komórki należy sformatować opcję i wprowadź tę formułę = Hasformula (A1) zobacz zrzut ekranu: utworzony i wybierz jeden kolor, który Ci się podoba Formatowanie komórek Okno dialogowe. . Przy połączeniu funkcji Jeżeli i Oraz możemy sprawdzić czy wszystkie argumenty są prawdziwe. Jak działa funkcja Oraz ? zwraca prawdę gdy wszystkie argumenty są prawdziwe. Łączne zastosowanie funkcji Jeżeli i Lub pozwala na zwrócenie wartości logicznej Prawda lub Fałsz wtedy gdy co najmniej jeden z argumentów będzie prawdziwy. EQrJl. Do czego służy funkcja WARUNKI w Excelu?WARUNKI to funkcja logiczna sprawdzająca podane warunki, która w działaniu przypomina funkcję JEŻELI, jednak przy wiekszej ilości warunków jest bardziej czytelna. Jaka jest budowa składni funkcji WARUNKI?Składnia funkcji wygląda następująco: WARUNKI(test_logiczny;wartość_jeśli_prawda;test_logiczny2;wartość_jeśli_prawda2;[…]test_logicznyN;wartość_jeśli_prawdaN) Dlaczego nie mam funkcji WARUNKI?Możliwe, że masz starszą wersje programu Excel. Funkcja WARUNKI dostępna jest od wersji Excel 2019 oraz Excel dla Microsoft 365. Funkcja WARUNKI – film instruktażowy Praca z przykładowym plikiem Plik do przykładu można pobrać poniżej. UWAGA! Plik wyświetla błędy, jeśli wpisana funkcja w komórkę jest inna niż zaplanowałem dla danego przykładu. Co nie oznacza, że koniecznie jest to błędem, ponieważ często pewne rzeczy w Excelu można zrobić na kilka / kilkanaście sposobów. Funkcja WARUNKI – budowa? Funkcja WARUNKI do działania potrzebuje tylko jednego argumentu, a w zasadzie pary warunków: testu logicznego i wartości, jeśli test logiczny jest prawdą. O wiele lepiej skorzytać z niej, gdy chcemy sprawdzić kilka wariantow. Jeśli będziemy chcieli skorzystac z funkcji WARUNKI to musimy pamiętać, że powinniśmy sprawdzić wszystkie możliwości, gdyż funkcja ta nie zwraca wartości FAŁSZ, a od razu naszym oczom ukazuję się błąd #N/D. Jeśli więc mam tylko jeden warunek do sprawdzenia proponuję skorzystać z funkcji JEŻELI. Składnia funkcji WARUNKI: =WARUNKI(test_logiczny;wartość_jeśli_prawda;test_logiczny2;wartość_jeśli_prawda2;[…]test_logicznyN;wartość_jeśli_prawdaN) =WARUNKI(M19=7;"Jest 7") Wpisując powyższą formułę do komórki N19,gdy w komórce M19 jest liczba 7, wynik to Jest 7,jeśli do komórki M19 wpiszemy 6, wynik to błąd #N/D,żeby pozbyć sie błędu, trzeba tak napisać warunki, by błąd nie występował, np. =WARUNKI(M19=7;"Jest 7";M197;"Nie jest 7") wtedy, gdy w M19 jest 7 to komórka obok wyświetli Jest 7,a jeśli wartośc w komórce M19 nie jest równa 7 to zobaczymy komunikat Nie jest 7. Jak wyliczyć wysokość premię dzięki funkcji WARUNKI? Myślę, że mając powyższą wiedzę możemy przejść do właściwego zadania. Naszym zadaniem jest policzyć procentową wartość premii dla pracowników. Wykorzystamy do tego zadania oczywiście funkcję WARUNKI. W komórkę J9 wpisujemy: =WARUNKI(H9>=$M$10;$N$10) Po nacisnięciu klawisza ENTER pojawi nam się błąd #N/D – dodatkowo w komórce P9 pojawiła się informacja, że formuła nie jest identyczna jak w zadaniu. Dopisujemy kolejne warunki do funkcji Przechodzimy raz jeszcze do komórki J9 i tym razem dopisujemy już wszystkie kolejne warunki, pamietając, że adresy z Tabeli Premiowej powinny byc adresowane bezwzględnie. Całość powinna wyglądać tak: =WARUNKI(H9>=$M$10;$N$10;H9>=$M$11;$N$11;H9>=$M$12;$N$12;H9>=$M$13;$N$13;H9>=$M$14;$N$14) Jak zobaczymy po naciśnięciu klawisza ENTER w naszym przykładzie nadal mamy błąd. Błąd jest, gdyż nie obsłużylismy ostatniego warunku, czyli co bedzie, jeśli % wykonania planu bedzie niższy niż 80%. Pozostaje więc nam poprawić formułę o wypisanie zera, jeśli wartość % wykonania planu jest nizsza od 80%. =WARUNKI(H9>=$M$10;$N$10;H9>=$M$11;$N$11;H9>=$M$12;$N$12;H9>=$M$13;$N$13;H9>=$M$14;$N$14;H9<$M$14;0) Pozostanie pzresunąć kursor na prawy dolny róg komórki J9 i gdy zmieni się w mały czarny krzyżyk to dwa razy kliknąć lewy przycisk myszy. Dzięki temu zabiegowi mamy skopiowaną całą formułę do poniższych komórek. Dodatkowo jak widać mamy wszędzie dobry wynik. Zobacz inne przykłady Jeśli spodobał Ci się wpis pozostaw po sobie znak w formie komentarza, a także zapraszam do zapisania się na moją listę mailingową. Przy okazji może zainteresują Cię inne moje artykuły. 13 grudnia 2020 W drugiej części przedstawiam jak sprawdzić, czy początkowe dwie litery to Ch, Rz, Cz, Sz i jeśli tak to wyświetlić dwie, a nie jedną literę Zobacz więcej 7 grudnia 2020 Praca z tekstem w Excelu potrafi frustrować. Przynajmniej do momentu, w którym poznamy kilka funkcji tekstowych i sposobów pracy z nimi. Wierzcie mi, tu jedna funkcja nie wystarczy. Zobacz więcej 29 listopada 2020 Jedna z bardziej popularnych funkcji w Excelu, która służy do wyszukiwania danych. Z artykułu dowiesz sie jak z niej korzystać w podstawowym zakresie. Zobacz więcej 24 listopada 2020 Przedstawiam funkcję przewyższającą i Funkcja ta potrafi to wszystko z czym nie dawały sobie rady wcześniejsze. Zobacz więcej 15 listopada 2020 Używanie zbyt wielu warunków w funkcji JEŻELI jest męczące i nieczytelne. Jeśli jesteście szczęsliwymi posiadaczami Exela 2019 lub Excela 365 to skorzystajcie z funkcji WARUNKI. Zobacz więcej 8 listopada 2020 Tabele przestawne są zmorą początkujących użytkowników Excela. Wiem to z autopsji, lata temu także tak miałem. Jednak nauka korzystania z nich nie jest wcale taka trudna. Zobacz więcej 1 listopada 2020 Gdy szukamy uśrednionych danych, czasem średnia nie wystarcza. Wtedy na pewno pomoże mediana, przeczytaj jak łatwo można z niej korzystać w Excelu. Zobacz więcej Nawigacja wpisu Uwaga: przeglądasz tę stronę na urządzeniu o niewielkim ekranie (szerokość 6;6; JEŻELI(D2=7;0; JEŻELI(D2=1; JEŻELI(E29,5;9,5))))))) Rys. 6: Schemat algorytmu. Jak widać, trzy relacje występujące w tej formule nie mają przypisanej akcji alternatywnej, czyli nie wystąpił dla nich trzeci argument funkcji JEŻELI a to oznacza, że wyniki mogą być niepoprawne. Program Calc reaguje na brak argumentu w zależności od tego, czy jest to wersja LibreOffice, czy Apache OpenOffice. LibreOffice Calc pozwala na pominięcie każdego z argumentów i przyjmuje wówczas, że ten pominięty argument odpowiada wartości 0. Teoretycznie można więc wpisać funkcję JEŻELI w postaci =JEŻELI(;;) co spowoduje wstawienie liczby 0 do komórki z taką formułą. 0 dlatego, że pominięcie pierwszego argumentu jest traktowane jak 0, a zero odpowiada wartości FAŁSZ, co dla tej funkcji oznacza wybór trzeciego argumentu, który jeśli go brakuje, zostanie zastąpiony zerem. Inaczej jest w Apache OpenOffice. Ten dopuszcza pominięcie tylko ostatniego argumentu, wstawiając jako jego wynik stałą FAŁSZ, czyli w arytmetycznym zrozumieniu tej wartości – 0. Jeśli zostanie pominięty drugi argument, wyświetlany jest komunikat „Błąd:518”, a jeśli zostanie pominięty pierwszy argument, Calc zaproponuje modyfikację formuły poprzez pominięcie pierwszego średnika i w tej sytuacji drugi argument staje się pierwszym, trzeci drugim, a trzeci jest pominięty. Jeżeli pominięto i drugi argument, kolejny komunikat zaproponuje usunięcie także tego średnika. Gdy zaproponowana modyfikacja zostanie odrzucona, formuła wyświetli „Błąd:511”, jeśli przyjęta – formuła przyjmie postać =JEŻELI(arg) i wyświetli wartość PRAWDA, gdy argument ma wartość różną od zera albo FAŁSZ, gdy ten argument ma wartość zerową. Jeśli pierwszy argument nie będzie liczbą, w obu wersjach programu Calc wynikiem jest błąd #ARG!. W przypadku tego schematu brakują akcje związane z wartością logiczną FAŁSZ. Jest to ostatni argument funkcji JEŻELI więc jego pominięcie oznacza przyjęcie wartości 0. Schemat unaocznia także fakt, że jakiekolwiek działania dotyczą tylko takiego przypadku gdy wartość D2 wynosi 6. Badanie w ścieżce odpowiadającej za spełniony warunek D2=6 warunku D2=7 lub D2=1 jest bez sensu, bo D2 w tej ścieżce to przecież 6. Zwróćmy także uwagę na fragment schematu zawierający wyrażenia logiczne E26. Już pierwsza relacja oznacza, że pójście ścieżką FAŁSZ to ścieżka, w której E2>6 więc to badanie nie jest potrzebne. Podobnie jest z dwoma ostatnimi warunkami decyzyjnymi. Jeśli E2 nie spełnia warunku, że E26;SUMA(E2-F2);0)) Odpowiada ona takiemu schematowi: Rys. 8: Schemat algorytmu. W tej formule oczywiste jest, że zastosowanie funkcji SUMA do wyrażenia E2-F2 jest bezsensowne, gdyż już wyrażenie E2-F2 zwraca wartość będącą oczekiwanym wynikiem. W powyższej formule komórka przyjmuje, jak widać albo wartość odejmowania E2-F2 (i dzieje się to tylko wtedy gdy D2=6 i jednocześnie E2>6) albo zero w pozostałych przypadkach. W tym wyrażeniu także pominięty został ostatni argument pierwszej funkcji JEŻELI więc wartość 0 jest przypisywana dla każdego warunku, gdy D26. Skorygowany schemat może więc wyglądać następująco: Rys. 9: Schemat algorytmu. Pierwszym wyrażeniem funkcji możne zatem być wyrażenie logiczne wykorzystujące funkcję I, które będzie generowało wynik PRAWDA tylko wtedy gdy oba warunki będą spełnione. =JEŻELI(I(D2=6;E2>6);E2-F2;0) Tylko czy na pewno trzeba w tej sytuacji zastosować funkcję JEŻELI? Pamiętajmy, że wartości logicznej PRAWDA otrzymywanej jako wynik wyrażenia logicznego odpowiada arytmetyczna wartość 1 natomiast wartości FAŁSZ – arytmetyczna wartość 0. Tę formulę można więc zastąpić następująco: =(E2-F2)*I(D2=6;E2>6) albo wręcz tak: =(E2-F2)*(D2=2)*(E2>6) W pierwsze postaci tej formuły funkcja I zwróci wartość 1 albo 0. Wiadomo, że każda wartość liczbowa pomnożona przez 1 daje tę samą liczbę a przez 0 – zero. W drugiej postaci obliczenie różnicy E2-F2 nastąpi tylko wtedy gdy oba wyrażenie logiczne będą prawdziwe, czyli arytmetycznie wyniosą 1. Kończąc temat schematów blokowych, poruszę jeszcze jeden jego aspekt, wynikający z wcześniejszego stwierdzenia, że każdy argument funkcji może być dowolnym złożonym wyrażeniem. Gdy takim wyrażeniem ma być pierwszy argument funkcji JEŻELI jego schemat formalnie należałoby umieścić wewnątrz bloku decyzyjnego, albowiem w nim wpisany jest pierwszy argument. Taki sposób prezentacji schematu jest trudny do analizowania ponadto niełatwy do wykonania graficznego. Dlatego warto takie złożone wyrażenie rozrysować jako oddzielny schemat blokowy, nadając temu schematowi jakąś nazwę i nazwę tę wpisać do podstawowego schematu wewnątrz bloku decyzyjnego w postaci np. „Procedura nazwa”. Informacje pomocnicze dla użytkowników Apache OpenOffice i LibreOffice w systemie MacOs System operacyjny Windows/Linux System operacyjny MacOs Kliknięcie prawym przyciskiem myszki Control + kliknięcie Klawisz CTRL (w skrótach klawiaturowych) Klawisz ⌘ F5 Nawigator Shift + ⌘ + F5 F11 Style i formatowanie ⌘ + T W tym samouczku wyjaśniono różnicę między funkcjami i pod względem ich składni i użycia i zawiera szereg przykładów formuł do sumowania wartości z wieloma kryteriami AND / OR w programach Excel 2019, 2016, 2013, 2010, 2007, 2003 i wcześniejszych. Jak każdy wie, Microsoft Excel udostępnia tablicę funkcji do wykonywania różnych obliczeń na danych. Kilka artykułów temu omówiliśmy i które są przeznaczone do liczenia komórek odpowiednio na podstawie jednego warunku i kilku warunków. W zeszłym tygodniu omówiliśmy Excel SUMIF, który dodaje wartości spełniające określone kryteria. Teraz nadszedł czas, aby przejść do liczby mnogiej wersji – Excel która umożliwia sumowanie wartości według wielu kryteriów. Osoby zaznajomione z funkcją mogą pomyśleć, że przekształcenie jej na wymaga tylko dodatkowego wysiłku „S” i kilka dodatkowych kryteriów. Wydawałoby się to całkiem logiczne… ale „logiczne” nie zawsze tak jest w przypadku firmy Microsoft 🙂 Funkcja – składnia & użycie funkcja – składnia & użycie Jak używać funkcji SUMIFS w programie Excel (logika AND) Excel SUMIF z wieloma kryteriami (logika OR) Excel SUMIFS z wieloma warunkami LUB Używanie Excel SUM w formułach tablicowych Funkcja programu Excel – składnia & użycie Funkcja służy do warunkowego sumowania wartości na podstawie pojedynczych kryteriów. Szczegółowo omówiliśmy składnię SUMIF w poprzednim artykule, więc pozwólcie, że przedstawię teraz krótkie podsumowanie. SUMIF (zakres, kryteria,) range – zakres komórek do oceny według kryteriów, wymagany. criteria – warunek, który musi zostać spełniony, wymagane. sum_range – komórki do zsumowania, jeśli warunek jest spełniony, opcjonalne. Jak widać, składnia funkcji Excel dopuszcza tylko jeden warunek. Mimo to mówimy, że Excel może służyć do sumowania wartości z wieloma kryteriami. Jak to możliwe? Dodając wyniki kilku funkcji i używając formuł z kryteriami tablicowymi, jak pokazano w poniższych przykładach. Funkcja programu Excel – składnia & użycie Używasz funkcji w programie Excel, aby znaleźć warunkową sumę wartości na podstawie wielu kryteriów. Funkcja została wprowadzona w programie Excel 2007, dzięki czemu można jej używać we wszystkich nowoczesnych wersjach programu Excel 2019, 2016, 2013, 2010 i 2007. W porównaniu z funkcją składnia jest nieco bardziej złożona : (zakres_suma, zakres_kryteriów1, kryteria1,,…) Pierwsze 3 argumenty są obowiązkowe, dodatkowe zakresy i powiązane z nimi kryteria są opcjonalne. sum_range – wymagana jest jedna lub więcej komórek do zsumowania. Może to być pojedyncza komórka, zakres komórek lub nazwany zakres. Sumowane są tylko komórki z liczbami; puste i wartości tekstowe są ignorowane. criteria_range1 – wymagany jest pierwszy zakres do oceny według powiązanych kryteriów. criteria1 – pierwszy warunek, który należy spełnić, wymagany. Możesz podać kryteria w postaci liczby, wyrażenia logicznego, odwołania do komórki, tekstu lub innej funkcji programu Excel. Na przykład możesz użyć takich kryteriów, jak 10, „> = 10″, A1, „wiśnie” lub DZIŚ (). criteria_range2, criteria2, … – są to dodatkowe zakresy i związane z nimi kryteria, opcjonalne. W formułach SUMIFS można użyć maksymalnie 127 par zakres / kryteria. Ważne! Funkcja działa z logiką ORAZ, co oznacza, że komórka w zakresie sumy jest sumowana tylko wtedy, gdy spełnia wszystkie określone kryteria (wszystkie kryteria są prawdziwe dla tej komórki). A teraz niech ” s spójrz na formułę programu Excel z dwoma warunkami. Załóżmy, że masz tabelę zawierającą partie owoców od różnych dostawców. Nazwy owoców znajdują się w kolumnie A, nazwy dostawców w kolumnie B i ilość w kolumnie C. Chcesz poznać sumę kwot odnoszących się do danego owocu i dostawcy np wszystkie jabłka dostarczone przez Petea. Kiedy „uczysz się czegoś nowego”, zawsze dobrze jest zacząć od prostych rzeczy. Zatem na początek zdefiniujmy wszystkie argumenty dla naszej formuły zakres_sumowania – C2: C9 zakres_kryteriów1 – A2: A9 kryteria1 – „jabłka” kryteria_zakres2 – B2: B9 kryteria2 – „Pete” Teraz zbierz powyższe parametry i otrzyma następującą formułę SUMIFS: =SUMIFS(C2:C9, A2:A9, "apples", B2:B9, "Pete") Aby doprecyzować formułę dalej, możesz zastąpić kryteria tekstowe „jabłka” i „Pete” odwołaniami do tym przypadku nie będziesz musiał zmieniać wzoru w celu obliczenia ilości innych owoców od innego dostawcy: =SUMIFS(C2:C9, A2:A9, F1, B2:B9, F2) Używanie funkcji i w programie Excel – rzeczy do zapamiętania Ponieważ celem tego samouczka jest omówienie wszystkich możliwych sposobów sumowania wartości według kilku warunków, omówimy przykłady formuł z obiema funkcjami – Excel i z wieloma kryteriami. Aby używać ich poprawnie, musisz jasno zrozumieć, co łączy te dwie funkcje i czym się różnią. Chociaż część wspólna jest jasna – podobne przeznaczenie i parametry – różnice nie są tak oczywiste, ale bardzo istotne. 1. Kolejność argumentów W funkcjach i kolejność argumentów jest różna. W szczególności, zakres sumy jest pierwszym parametr w SUMIFS, ale jest trzeci w SUMIF. Na pierwszy rzut oka może się wydawać, że Microsoft celowo komplikuje krzywą uczenia się swoich użytkowników. eee, po bliższym przyjrzeniu się, zobaczysz uzasadnienie tego. Chodzi o to, że suma_zakres jest opcjonalna w Jeśli go pominiesz, nie ma problemu, twoja formuła zsumuje wartości z zakresu (pierwszy parametr). W suma_zakres jest bardzo ważna i obowiązkowa, dlatego jest na pierwszym miejscu. Zapewne chłopaki z Microsoftu myśleli, że po dodaniu dziesiątej lub setnej pary zakres / kryteria ktoś mógłby zapomnieć o określeniu zakresu do zsumowania 🙂 2. Zakres sumy i zakres kryteriów powinny mieć taką samą wielkość W funkcji argument sum_zakres niekoniecznie musi mieć taki sam rozmiar jak argument zakresu, o ile górna lewa komórka znajduje się po prawej stronie. W programie Excel SUMIFS każdy zakres kryteriów musi zawierać taką samą liczbę wierszy i kolumn, jak parametr sum_range. Na przykład =SUMIF(A2:A9,F1,C2:C18) zwróci prawidłowy wynik, ponieważ program Excel uwzględnia tylko górna lewa komórka w argumencie sum_range (C2 w tym przykładzie, która jest poprawna), a następnie zawiera tyle kolumn i wierszy, ile zawiera argument zakresu. W porządku, wystarczająca strategia (tj. teoria) przejdźmy do taktyki (np. przykłady formuł:) Jak używać w Excelu – przykłady formuł Przed chwilą omawialiśmy prostą formułę z dwoma tekstami Kryteria. W ten sam sposób można używać funkcji programu Excel z wieloma kryteriami wyrażonymi przez liczby, daty, wyrażenia logiczne i inne funkcje programu Excel. Przykład 1. Excel SUMIFS z operatorami porównania Załóżmy, że w naszej tabeli dostawców owoców chcesz zsumować wszystkie dostawy dokonane przez Mikea w ilości 200 lub więcej. Aby to zrobić, użyj operatora porównania „większe niż o r równe „(> =) w kryteriach2 i uzyskaj następującą formułę =SUMIFS(C2:C9,B2:B9,"Mike",C2:C9,">=200") Uwaga. Zwróć uwagę, że w formułach Excel SUMIFS wyrażenia logiczne z operatorami porównania powinny być zawsze ujęte w podwójne cudzysłowy („”). Omawiając funkcję Excel SUMIF, omówiliśmy szczegółowo wszystkie możliwe operatory porównania, te same operatory może być używany w kryteriach Na przykład poniższa formuła zwraca sumę wszystkich wartości w komórkach C2: C9, które są większe lub równe 200 i mniejsze lub równe 300. =SUMIFS(C2:C9, C2:C9,">=200", C2:C9,"="&TODAY()-7, C2:C10,"=10/1/2014", B2:B9, "", C2:C10,"=") Używanie Excela z wieloma kryteriami LUB Jak wspomniano na początku tego samouczka, funkcja została zaprojektowana z logiką AND. Ale co, jeśli musisz zsumować wartości z wieloma kryteriami OR, tj. Gdy przynajmniej jeden z warunków jest spełniony? Przykład 1. + SUMA Najprostszym rozwiązaniem jest sumowanie wyniki zwrócone przez kilka funkcji Na przykład poniższy wzór pokazuje, jak znaleźć całkowitą liczbę produktów dostarczonych przez Michała i Jana: =SUMIF(C2:C9,"Mike",D2:D9) + SUMIF(C2:C9,"John",D2:D9) Jak widać, pierwsza funkcja dodaje ilości odpowiadające „Michałowi”, druga funkcja zwraca kwoty związane z „Janem”, a następnie dodajesz te dwie liczby. Przykład 2. SUMA & z argumentem tablicowym Powyższe rozwiązanie jest bardzo proste i może wykonać zadanie szybko, gdy są tylko kilka kryteriów. Jednak formuła + może ogromnie wzrosnąć, jeśli chcesz zsumować wartości z wieloma warunkami LUB. W takim przypadku lepszym podejściem jest użycie argumentu kryteriów tablicowych w funkcji Przeanalizujmy teraz to podejście. Możesz zacząć od wypisania wszystkich warunków oddzielonych przecinkami, a następnie zawrzeć wynikową listę rozdzieloną przecinkami w {nawiasach klamrowych}, co technicznie nazywa się tablicą. Argument tablicowy składający się z 3 wartości zmusza twoją formułę do zwrócenia trzech oddzielnych wyników, ale ponieważ piszemy formułę w jednej komórce, zwróci ona tylko pierwszy wynik – tj. sumę produktów dostarczonych przez Jana . Aby to podejście oparte na kryteriach tablic zadziałało, musisz użyć jeszcze jednej małej sztuczki – zawrzeć formułę w funkcji SUMA, na przykład: =SUM(SUMIF(C2:C9, {"John","Mike","Pete"} , D2:D9)) Jak widać, kryteria tablicowe sprawiają, że formuła jest znacznie bardziej zwarta w porównaniu z + i pozwala dodać tyle wartości, ile chcesz w tablicy. To podejście działa zarówno z liczbami, jak i wartościami tekstowymi. Na przykład, jeśli zamiast nazw dostawców w kolumnie C, masz identyfikatory dostawców, takie jak 1, 2, 3 itd. ., Twoja formuła SUMIF wyglądałaby podobnie do tego: =SUM(SUMIF(C2:C9, {1,2,3} , D2:D9)) W przeciwieństwie do wartości tekstowych, liczby nie muszą być ujęte w podwójne cudzysłowy w tablica argumentów. Przykład 3. SUMPRODUCT & SUMIF W przypadku, gdy preferowanym sposobem jest umieszczenie kryteriów w niektórych komórkach, a nie określ je bezpośrednio w formule, możesz użyć w połączeniu z funkcją która mnoży składniki w podanych tablicach i zwraca sumę tych iloczynów. =SUMPRODUCT(SUMIF(C2:C9, G2:G4, D2:D9)) Gdzie G2: G4 to komórki zawierające kryteria, w naszym przypadku nazwy dostawców, jak pokazano na poniższym zrzucie ekranu. Ale oczywiście nic nie stoi na przeszkodzie wypisując wartości w kryteriach tablicowych funkcji jeśli chcesz: =SUMPRODUCT(SUMIF(C2:C9, {"Mike","John","Pete"}, D2:D9)) Wynik zwracany przez obie formuły będzie identyczny z tym, co widać na zrzucie ekranu: Excel SUMIFS z wiele kryteriów LUB Jeśli chcesz warunkowo sumować wartości w programie Excel nie tylko z wieloma warunkami LUB, ale z kilkoma zestawami warunków, będziesz musiał użyć zamiast Formuły będą bardzo podobne do tego, co przed chwilą omówiliśmy. Jak zwykle, przykład może pomóc lepiej zilustrować ten punkt. W naszej tabeli dostawców owoców dodajmy datę dostawy (kolumna E) i znajdź całkowitą ilość dostarczoną przez Mikea, Johna i Petea w październiku. Przykład 1. SUMY + Wzór utworzony przez to podejście zawiera wiele powtórzeń i wyglądów uciążliwe, ale łatwe do zrozumienia i, co najważniejsze, działa 🙂 Przykład 2. SUMA & SUMIFS z argumentem tablicowym Próbowałem wyjaśnić istotę tego podejścia w przykładzie więc teraz możemy po prostu skopiować tę formułę, zmienić kolejność argumentów (jak pamiętacie, jest inna w i i dodać dodatkowe kryteria. wynikowa formuła jest bardziej zwarta niż + =SUM(SUMIFS(D2:D9,C2:C9, {"Mike", "John", "Pete"}, E2:E9,">=10/1/2014", E2:E9, "=H4), --(E2:E9=10) * (C1:C10>B1:B10)) Nie widzę żadnych praktycznych aplikacja do powyższego wzoru? Pomyśl o tym w inny sposób:) Załóżmy, że masz listę zamówień pokazaną na poniższym zrzucie ekranu i chcesz wiedzieć, ile produktów nie zostało w całości dostarczonych w podanym terminie. Po przetłumaczeniu na język Excela mamy następujące warunki: Warunek 1: Wartość w kolumnie B (zamówione elementy) jest większa niż 0 Warunek 2: Wartość w kolumnie C (Dostarczono) w mniej niż w kolumnie B Warunek 3: Data w kolumnie D (Termin) jest mniejsza niż 11/1/2014. Wstawienie trzech zakresów / par warunków razem, otrzymujemy następującą formułę: =SUM((B2:B10>=0)*(B2:B10>C2:C10)*(D2:D10