kilka funkcji jeżeli w jednej komórce

Najprostsza metoda: użyj znaku minus (-) Najprostszym sposobem odejmowania w Excelu jest użycie znaku minus (-) pomiędzy wartościami, które mają zostać odjęte. Na przykład, jeśli chcemy odjąć 5 od 10, możemy napisać w komórce: =10-5. I naciśnij Enter. Wynik zostanie następnie wyświetlony w komórce: =10-5 5. Zamiast wartości Wyróżnione słowa w akapicie powyżej to 4 argumenty funkcji WYSZUKAJ.PIONOWO (w wersji angielskiej: VLOOKUP), którą użyjemy do rozwiązania tego zadania. Da się to zrobić na wiele innych sposobów, jednak to jest zdecydowanie najpopularniejsze. Then Wymagane w składni jednowierszowej; opcjonalnie w składni wielowierszowej. statements Opcjonalny. Co najmniej jedna instrukcja, IfThen która jest wykonywana, jeśli condition zostanie obliczona na Truewartość . elseifcondition Wymagane, jeśli ElseIf jest obecne. Wyrażenie. Składnia funkcji wygląda tak: =PODAJ.POZYCJĘ ( szukana_wartość; przeszukiwana_tab; [typ_porównania]) czyli: =PODAJ.POZYCJĘ ( tu podajesz czego szukasz; tu podajesz gdzie szukasz; tu podajemy parametr: -1,0 albo 1, które mówi o sposobie porównania szukanej wartości z wartościami na liście, gdzie szukasz) Po wyjaśnienie jak W przypadku SUMA.JEŻELI jest ich 3: Argument 1 – podaj zakres komórek, w których chcesz szukać określonych wartości. Dla tych wartości będą sumowane liczby z argumentu nr 3. Zazwyczaj jest to kolumna tabeli. Argument 2 – podaj warunek, określający jakich komórek szukasz (np. zawierające konkretny tekst, liczby, daty). Wprowadzenie. Microsoft Excel ma kilka funkcji sklasyfikowanych jako funkcje wyszukiwania. Wiele z nich można zastąpić wszechstronną funkcją XLOOKUP. Funkcja WYSZUKAJ.PIONOWO może zastąpić funkcje WYSZUKAJ.PIONOWO, WYSZUKAJ.POZIOMO i INDEKS + PODAJ.POZYCJĘ. Jednak funkcja WYSZUKAJ.X jest dostępna tylko w programie Excel dla platformy rangsangan atau gerak hati yang timbul dengan tiba tiba. Funkcje Podstawy Przykłady opisane w tej lekcji dostępne są w arkuszu Excela: Funkcje tylko ich samodzielne przerobienie daje gwarancję zapamiętania tej lekcji Przykłady dla wszystkich lekcji szkolenia Excel 2007: Cwiczenia Excel Funkcje w Excelu uruchamiamy klikając na ikonę . Istnieje kilka sposobów, aby znaleźć interesującą nas funkcje, w oknie ‘Wyszukaj funkcję’ możemy wpisać nazwę funkcji (jeśli ją znamy) lub jednym słowem, to co funkcja powinna robić. Możemy też wybrać kategorię, w której może znajdować się interesująca nas funkcja, np. Finansowe lub Matematyczne. Klikając na nazwę funkcji w oknie ‘Wybierz funkcję:’ pod listą nazw widzimy opis funkcji. W wielu przypadkach opis ten pozwala zrozumieć, do czego służy dana funkcja. Po wybraniu funkcji, (w poniższym przykładzie SUMA), wyświetlane jest okno ‘Argumenty funkcji’, po wprowadzeniu adresów komórek do pierwszego z okienek, poniżej zostanie wyświetlony aktualny wynik funkcji (o ile jest możliwe jego obliczenie), pod nim znany już nam opis funkcji, a jeszcze poniżej opis argumentu, który można wprowadzić do pola ‘Liczba1’ i tego co funkcja z nimi zrobi. Opisy najczęściej używanych funkcji Poniżej przedstawiam opisy często używanych funkcji z przykładami użycia zaczerpniętymi z doświadczeń w korporacjach. Lista opisanych funkcji oczywiście nie wyczerpuje wszystkich możliwości Excela, ale tak jak wspominałem wcześniej, dla większości funkcji sposób ich działania można zrozumieć z opisów zawartych w Excelu. Funkcje bardziej skomplikowane zostały omówione w lekcjach: ‘Funkcje dla Zaawansowanych’ i ‘Funkcje Tablicowe’. SUMA (Arkusz: ‘SUMA’) Prawdopodobnie najczęściej używana funkcja Excela, oraz najprostszą w użyciu. Można jej używać na kilka sposobów, najbezpieczniejszym będzie zaznaczenie całego obszaru, który chcemy zsumować wraz z komórką poniżej lub po prawej, gdzie ma znaleźć się suma i kliknięcie symbolu sumy na pasku ikon lub wciśnięcie kombinacji klawiczy Alt+=. Drugim sposobem jest ustawienie jako aktywnej komórki, tej w której ma znaleźć się suma i pozwolenie Excelowi domyślić się jaki obszar ma zsumować, zawsze należy sprawdzić czy program zaproponował właściwy zakres i wcisnąć enter. UWAGA: Nawet w przypadku tak prostej funkcji bardzo wielu użytkownikom Excela zdarzają się błędy, najpopularniejsze z nich polegają na ustawieniu aktywnej komórki na końcu sumowanych liczb i wciśnięciu ikony ‘Autosumowania’, po zasugerowaniu przez Excela zakresu, użytkownicy, bez spojrzenia co zostanie zsumowane, wciskają enter i zakres sumy jest niewłaściwy. Dzieje się tak najczęściej w 2 przypadkach: - kiedy brak jest jednej z danych Excel zatrzyma się przy pierwszej pustej komórce. - gdy w nagłówku kolumny znajduje się liczba np. rok także on zostanie zsumowany. Znacznie więcej możliwości daje funkcja opisane w dalszej części tej lekcji. ŚREDNIA (Arkusz: ‘ŚREDNIA’) Funkcja oblicza średnią dla wprowadzonych liczb, bądź zakresów. UWAGA: Funkcja ta podobnie jak wszystkie inne funkcje Excela inaczej traktuje pustą komórkę lub tekst a inaczej liczbę zero, co dobrze ilustruje poniższy przykład. W pierwszej z tabel średnia liczona jest tylko dla 6 elementów tabeli. Gdybyśmy chcieli obliczyć ile wynosi średnia sprzedaż na produkt dla poniższych 7 produktów, w pustą komórkę należy wprowadzić zero. Tekst tak samo jak puste komórki jest pomijany w kalkulacjach. Zaletą tej funkcji jest to, że można do niej wprowadzać adresy pojedynczych komórek i jednocześnie całe zakresy. Średnia dostępna jest też w pasku stanu. Więcej informacji na ten temat w lekcji: Pasek Stanu. Do obliczania średniej ważonej najprościej wykorzystywać funkcję a do obliczania średniej geometrycznej funkcję Funkcja JEŻELI (Arkusz: ‘JEŻELI’) W poniższym przykładzie w kolumnie E chcielibyśmy uzyskać wzrost sprzedaży dla długiej listy produktów. Ponieważ część produktów nie była sprzedawana w 2009 roku zwykła formuła dzielenia dałaby w ich przypadku wynik ‘#DZIEL/0!’, który nie wygląda zbyt profesjonalnie, dlatego wolelibyśmy mieć w takich przypadkach wprowadzony znak „-----„. Proponuje wykasować tą formułę i ustawić jako aktywną komórkę E4, po czym wybrać ikonę funkcji. W okienku ‘Wyszukaj funkcję’ wpisujemy ‘jeżeli’ i zatwierdzamy enterem. Po wybraniu funkcji JEŻELI i kliknięciu OK na powyżej przedstawionym oknie ‘Wstawianie funkcji’, zostanie wyświetlone poniżej pokazane okno ‘Argumenty funkcji’. W okienku ‘Test_logiczny’ wprowadzamy C4>0, czyli jeżeli sprzedaż w 2009 była większa od zera to… W okienku ‘Wartość_jeżeli_prawda’ wprowadzamy formułę na wzrost procentowy, który można wyliczyć gdy sprzedaż w 2009 była większa od zera. W okienku ‘Wartość_jeżeli_fałsz’ wprowadzamy -----, symbol ten ma być wyświetlany gdy nie jest możliwe obliczenie procentowego wzrostu. Formułę zatwierdzamy kliknięciem w przycisk OK, po czym kopiujemy ją z komórki E4 do poniższych komórek tabeli. W efekcie powinniśmy uzyskać poniższy wygląd tabeli. ZAOKR (Arkusz: ‘ZAOKR’) W Excelu jest wiele funkcji służących do zaokrąglania, funkcja ZAOKR() jest najczęściej używaną z nich. Ma 2 argumenty: - liczbę, którą będziemy zaokrąglać (lub adres komórki w której ta liczba się znajduje) - ilość cyfr która ma być pokazana po przecinku (lub adres komórki w której ilość ta jest wpisana) W poniższym przykładzie w komórce C5 liczba 1,23456789 została zaokrąglona do 2 miejsca po przecinku czyli do liczby 1,23. Cyfry od 1 do 4 są zaokrąglane w dół, od 5 do 9 w górę, co łatwo zauważyć przeglądając wyniki powyższego przykładu. MAX, MIN (Arkusz: ‘MAX MIN’) Funkcje wybierają najmniejszą (MIN) lub największą liczbę (MAX) z podanego zakresu bądź zakresów. Tekst i puste komórki nie są brane pod uwagę. W poniższym przykładzie komórka C9 jest pusta, czyli jej wartość to 0, jednak jako najmniejsza wartość wybrana została komórka C13. Poniżej prezentuje zaczerpnięty z praktyki biznesowej przykład użycia funkcji = W wierszu ‘15’ znajdują się formuły wykorzystujące tą funkcję i zliczające ile było klientów którym przyznano rabat w każdym z miesięcy, a w kolumnie ‘J’ w ilu miesiącach przyznano rabat danemu klientowi. DZIŚ() Funkcja zwraca dzisiejszą dane, jest używana głównie w różnego rodzaju formularzach, oraz w logistyce np. do obliczania ile dni zostało od daty dostawy/transportu do dziś. Funkcja ta, jak mało która, nie posiada argumentów. Jej poprawny wynik zależy od prawidłowo ustawionej daty (i godziny) w systemie Windows. Funkcje Logiczne: LUB, ORAZ (Arkusz: ‘ORAZ LUB’) Funkcja logiczne rzadko są używane samodzielnie, znacznie częściej w połączeniu z innymi funkcjami, o łączeniu funkcji możesz przeczytać w dalszej części tej lekcji. ORAZ Funkcja zwraca wartości logiczne ‘PRAWDA’/‘FAŁSZ’, wartość ‘PRAWDA’, jeżeli wszystkie warunki zostały spełnione, w przeciwnym razie ‘FAŁSZ’. W poniższym przykładzie w kolumnie F uzyskaliśmy wartość prawda dla miesięcy, w których sprzedaż we wszystkich oddziałach była poniżej 50. LUB W poniższym przykładzie w kolumnie F przy użyciu funkcji LUB sprawdzamy czy w danym miesiącu sprzedaż w którymkolwiek z oddziałów przekroczyła wartość 100. Funkcja zwraca wartości logiczne: ‘PRAWDA’/’FAŁSZ’, ‘PRAWDĘ’ jeśli choć jeden z warunków jest spełniony, gdy żaden z nich nie jest spełniony ‘FAŁSZ’. ZAGNIEŻDŻANIE FUNKCJI (Arkusz: ‘Zagnieżdżanie funkcji’) Dość często występuje potrzeba użycia kilku funkcji na raz, co jest nazywane także zagnieżdżaniem funkcji. W poniższym przykładzie chcielibyśmy na podstawie dużej ilości imion ustalić płeć osób noszących te imiona. Zdecydowana większość imion kobiet używanych w Polsce kończy się na literę „a”, litera ta nie występuje natomiast na końcu imion męskich. Poniżej rozwiązanie tego problemu przy użyciu funkcji ‘JEŻELI’ i funkcji tekstowej ‘PRAWY’. Funkcję JEŻELI, poznaliśmy już w tej lekcji, zacznijmy więc od funkcji PRAWY, wycina ona określoną ilość liter od prawej strony tekstu. W poniższym przykładzie wycina 1 literę z prawej strony tekstu znajdującego się w komórce B3. (Ostatnia litera z tekstu ‘Agata’ to „a”.) W komórce D3 zgodnie z poniższym rysunkiem wprowadzamy funkcję JEŻELI, która gdy w komórce C3 znajduje się litera „a” daje rezultat: „Kobieta”, a przeciwnym wypadku „Mężczyzna”. Rozwiązaliśmy już dane nam zadanie, ale postarajmy się to zrobić przy użyciu jednej a nie dwóch komórek. Wchodzimy w komórkę C3 i zaznaczamy znajdującą się w niej formułę pomijając znak równości. Kopiujemy zaznaczoną formułę (wciskając Ctrl+C), po czym wychodzimy z komórki wciskając klawisz Esc. Wchodzimy do komórki D3, gdzie zaznaczamy adres C3 i wklejamy skopiowaną wcześniej formułę w jego miejsce (adres zostanie automatycznie usunięty). W ten sposób uzyskaliśmy funkcję zagnieżdżoną. Kolumna C nie jest już dłużej nam potrzebna. Po skopiowaniu formuły do poniższych wierszy i skasowaniu kolumny C z ostatnimi literami powinniśmy uzyskać widok taki jak poniżej. Zaawansowani użytkownicy Excela od razu wprowadzają wiele zagnieżdżonych funkcji, zaprezentowany powyżej sposób jest najbardziej użyteczny dla początkujących i pozwala zrozumieć logikę łączenia funkcji. (Arkusz ‘ W poniższym przykładzie mamy dane wartości sprzedaży i marży procentowej dla 4 krajów, obliczyliśmy już sumę sprzedaży, teraz chcemy obliczyć marżę procentową dla sumy tych krajów. Oczywiście użycie funkcji ŚREDNIA nie dałoby właściwego rozwiązania ze względu na to że sprzedaż w krajach ma różne wielkości i kraje te w rożny sposób wpływają na marżę całościową. W tym przykładzie moglibyśmy mozolnie wprowadzić formułę przemnażającą sprzedaż z każdego kraju przez marżę w kraju tym uzyskaną i podzielić to przez sumę sprzedaży, formuła taka jest pokazana w komórce I4. Dzięki sumie iloczynów formuła ta zostanie wprowadzona szybciej, ograniczymy ryzyko błędu, a w przypadku dodania kolejnego kraju nie będzie konieczności dokonywania zmian w formule. Stajemy w komórce G4, wybieramy funkcję i wprowadzamy adresy tak jak na poniższym rysunku. Aby uzyskać średnią ważoną wystarczy na końcu formuły dopisać dzielenie przez sumę sprzedaży znajdującą się w komórce G5. Właściwy wynik to 32%, taki sam jak uzyskaliśmy korzystając z formuły. Na koniec dodajmy jeszcze kolumnę pomiędzy Polską a Rosją wprowadzając do niej nowe dane. Średnia ważona marża w H4 aktualizowana jest na bieżąco. (Arkusz ‘Funkcje Tekstowe’) W poniższym przykładzie przedstawiam fragment Price Survey (lista z cenami konkurencji), jaki pewna firma otrzymała od agencji badawczej. Niestety pojemności produktów były częścią nazwy, co znacznie utrudniło porównywanie cen produktów. Ponieważ produktów objętych badaniem było kilka tysięcy zdecydowano się użyć funkcji tekstowych, aby wyodrębnić pojemność. Użyto dwóch funkcji tekstowych najpierw użyto funkcję - wyszukiwany jest tekst ml, który następuje po pojemności danego produktu, wynikiem działania na funkcję nałożona jest funkcja która wycina 3 znaki z nazwy produktu znajdujące się o 3 w lewo od miejsca w którym został znaleziony tekst ‘ml’. Wynikiem działania powyżej opisanych funkcji jest nie liczba lecz tekst, który w przypadku produktów o pojemności poniżej 100ml zaczyna się od spacji. Aby móc dokonywać operacji matematycznych na pojemności, należy całą kolumnę C zamienić na wartości (skopiować i wkleić w to samo miejsce wartości), po czym korzystając z operacji zamień (Ctrl+H) zamienić znak spacja na nic (niczego nie wpisując w okienko „Zamień na:”). (Arkusz ‘ Funkcja ta jest z reguły używana jako funkcja ‘wewnętrzna’ innych funkcji tak, aby gdy jeden z argumentów funkcji głównej będzie miał niewłaściwy format lub okaże się błędem, funkcja ta pomijała go zamiast wskazywać błąd. Najprostszym przykładem będzie użycie ten funkcji w dodawaniu komórek i w połączeniu z funkcją JEŻELI. (Arkusz: ‘ W poniższym przykładzie prezentuje porównanie wykorzystania funkcji i funkcji SUMA oraz ich połączenia. W kolumnie C używam funkcji SUMA dla regionów, a także dla sumy końcowej opisanej jako TOTAL. W tym przypadku obliczany jest błędny wynik ponieważ oprócz sprzedaży dla klientów sumowane są także sumy dla regionów, właściwym rozwiązaniem dla tego typu problemu byłoby zsumowanie komórek sum dla regionów (odpowiednia formuła znajduje się w komórce C27). W kolumnie D prezentuję użycie funkcji zarówno w sumach dla regionów, jak i Total. Wykorzystanie tej funkcji da nam pewność że sumy dla regionów (sumy wewnętrzne) będą pomijane. Wynik jest poprawny. W kolumnie E używam funkcji w wariancie, który pomija ukryte wiersze. Ponieważ wiersz 4 jest ukryty, wynik nie będzie poprawny, chyba że zależało nam właśnie na pominięciu wartości w ukrytych komórkach. W ostatnim przykładzie pokazuję, że funkcja pomija jedynie inne sumuje natomiast zwykłą funkcję SUMA. Wynik jest błędny. = W składni funkcji pierwsza cyfra jest oznaczeniem funkcji jaka ma być wykorzystana, dla cyfry ‘9’ lub ‘109’ będzie to suma, ale są także inne możliwości, przedstawiam je w tabeli poniżej. Drugim argumentem jest obszar który ma być sumowany. Funkcja nr (z artościami ukrytymi) Funkcja_nr (bez wartości ukrytych) Funkcja 1 101 ŚREDNIA 2 102 3 103 4 104 MAKSIMUM 5 105 MINIMUM 6 106 ILCZYN 7 107 8 108 9 109 SUMA 10 110 WARIANCJA 11 111 Funkcje Finansowe NPV, IRR, CAGR (Arkusz: ‘Funkcje Finansowe’) NPV Funkcja NPV służy do obliczania Wartości Bieżącej Netto inwestycji czyli mówiąc wprost: ile da się na tym zarobić. W poniższym przykładzie na przestrzeni lat 2010-2012 dokonujemy inwestycji, która będzie nam przynosić przychody od 2012 do 2030. Stopa dyskontowa w całym okresie wynosi 5%. Zakładamy że wszystkie wydatki i przypływy zachodzą na koniec okresu. Gdybyśmy chcieli wyliczyć wartość na dzień dzisiejszy (zakładamy 1 stycznia 2010) wydatków moglibyśmy skorzystać z poniższego wzoru: =F7/(1+C5)+G7/(1+C5)^2+H7/(1+C5)^3 (Symbol ^ oznacza podniesienie do potęgi i można go wpisać z klawiatury przytrzymując Shift i wciskając cyfrę 6.) Każdy z wydatków dzielimy przez 1+stopa dyskontowa aby wyliczyć jego wartość na dziś (wartość pieniądza w czasie jest zmienna). Wydatek z 2011 roku dzielimy dwukrotnie stąd potęga ^2, a wartość z 2012 trzykrotnie (trzecia potęga czyli ^3). Powyżej opisany wzór został użyty w komórce C7. W komórce C8 wykorzystano funkcję NPV, dzięki której uzyskano dokładnie ten sam wynik. W przypadku obliczania bieżącej wartości wydatków możliwe było wprowadzenie wzoru na NPV ponieważ wydatki są ponoszone tylko w 3 okresach i wzór nie był zbyt długi. Gdybyśmy chcieli tak samo wyliczyć bieżącą wartość przypływów musielibyśmy wykazać się dużą cierpliwością, a ryzyko błędu byłoby bardzo duże. W komórce C10 użyto funkcji NPV do obliczenia bieżącej wartości przypływów. Warto podkreślić, że w komórkach dla których przypływy nie występują (F8 i G8) muszą być wpisane zera, w przeciwnym razie funkcja pominie te komórki i wynik będzie niepoprawny. Aby obliczyć bieżącą wartość przewidywanego zysk na tym projekcie wystarczy odjąć od bieżącej wartości przypływów (C10) bieżącą wartość wydatków (C8). Taka formuła zapisana jest w komórce C12. Zysk możemy także wyliczyć bezpośrednio korzystając z danych znajdujących się w tabeli w wierszu 9, gdzie od przypływów odjęto wydatki. Funkcja ta została zapisana w komórce C13. IRR Funkcja IRR oblicza wewnętrzną stopę zwrotu, która może być rozumiana jako graniczna wielkość oprocentowania przy której zysk na danym projekcie wynosi 0. Jeżeli stopa dyskontowa jest niższa niż IRR zysk jest dodatni, jeżeli wyższa projekt przyniesie stratę. Można ją także rozumieć jako najwyższe oprocentowanie kredytu jakie możemy zaakceptować aby projekt nie przyniósł strat. Funkcja IRR dla tego przykładu wykorzystana jest w komórce C18. Dla sprawdzenia poprawności obliczeń możemy wynik funkcji IRR wpisać w komórce C5 (wprowadzamy z klawiatury 8,886%), zysk dla tego projektu będzie wynosił zero (komórki C12 i C13). CAGR CAGR to średni roczny wzrost/spadek obliczany dla okresu określonej liczby lat. W Excelu nie ma funkcji która by go obliczała, warto więc zapamiętać formułę dzięki której możemy to zrobić. W poniższym przykładzie sprzedaż w 2010 roku wynosiła 10,0 a w 2019 przewidywana jest sprzedaż wynosząca 23,6, chcielibyśmy policzyć ile wynosi średni roczny wzrost który pozwoliłby uzyskać taką sprzedaż. Skorzystamy z następującego wzoru: Średni wzrost = (Wartość Końcowa / Wartość Początkowa) ^ (1/(ilość okresów -1))-1 W poniższym przykładzie: Wartość Końcowa =23,6 (wartość w roku 2019) Wartość Początkowa = 10 (wartość w roku 2010) ilość okresów = 10 (w tabeli pokazane są dane dla 10 lat ) W poniższym przykładzie formuła będzie wyglądać następująco: Średni wzrost =(O27/F27)^(1/9)-1 WYBIERZ (Arkusz: ‘WYBIERZ) Działanie funkcji WYBIERZ jest bardzo proste, spośród listy wartości, które należy wprowadzić oddzielnie, wybiera tą, której numer pojawi się w pozycji Nr_arg. W poniższym przykładzie wprowadzono 5 wysokości rabatów, które zależą od ‘klasy klienta’ przyjmującej wartości od 1 do 5. Po wprowadzeniu cyfry do komórki B4 rabat zostaje wyszukany i podany w komórce C4. Funkcja ta jest podobna w swym działaniu do funkcji choć oferuje mniej możliwości. (Arkusz ‘ Moduł liczby to funkcja używana w matematyce, nazywana jest także wartością bezwzględną. Funkcja ta ma jeden argument, który może być tylko liczbą (lub adresem, lub inną funkcją dającą w wyniku liczbę). Funkcja ta dla liczb dodatnich nie zmienia ich wartości a dla liczb ujemnych zmienia znak na dodatni. Przykład: Jest to jedna z wielu funkcji w Excelu, które można łatwo zastąpić używając prostych symboli matematycznych. W tym przypadku potęgowania. Najpierw należy podnieść liczbę do potęgi 2 (lub innej parzystej) a potem wynik pierwiastkować pierwiastkiem 2 stopnia (lub innego takiego samego jak potęga). (-5)^2^(1/2)=5 LOS() Funkcja losuje wartość z przedziału od 0 do 1. Ponieważ losowanie odbywa się od nowa przy każdym przeliczeniu arkusza, najlepiej jest od razu zamienić ją na wartości, w przeciwnym razie losowanie nowych wartości przy każdej zmianie danych w pliku, może znacznie spowolnić działanie komputera. Często wynik tej funkcji przemnaża się je lub dzieli aby uzyskać rząd wielkości liczb który nas interesuje np. =los()*1000 lub =0,5+los()/10 Funkcja bywa używana najczęściej do tworzenia różnorodnych danych, które wyglądają realistycznie, większość danych w tym szkoleniu powstała przy użyciu tej funkcji. Często też w dyskusjach w wielkich korporacjach niektóre osoby sugerują tą funkcję jako możliwe źródło danych, które otrzymały od innych działów, krajów lub partnerów biznesowych. Więcej o funkcjach Excela w lekcji ‘Funkcje dla Zaawansowanych’. 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

kilka funkcji jeżeli w jednej komórce