Korzystanie z kryteriów w programie Microsoft Excel

Microsoft Excel to nie tylko edytor arkuszy kalkulacyjnych, ale także potężna aplikacja do różnych obliczeń. Ostatnia, ale nie najmniej ważna, funkcja ta wynika z wbudowanych funkcji. Przy pomocy pewnych funkcji (operatorów) możliwe jest określenie nawet warunków obliczeniowych, które nazywane są kryteriami. Nauczmy się bardziej szczegółowo, jak ich używać podczas pracy w Excelu.

Stosowanie kryteriów

Kryteria to warunki, w których program wykonuje określone działania. Są używane w wielu wbudowanych funkcjach. W ich nazwie najczęściej występuje wyrażenie "JEŻELI" . Do tej grupy operatorów należy przede wszystkim uwzględnić KRAJE , KONTA , SUMA , SUMMES . Oprócz wbudowanych operatorów kryteria w programie Excel są również używane do formatowania warunkowego. Zastanówmy się nad ich zastosowaniem przy pracy z różnymi narzędziami danego procesora stołowego.

RACHUNKI

Głównym zadaniem operatora COUNTIF , który odnosi się do grupy statystycznej, jest zliczanie komórek zajmowanych przez różne wartości, które spełniają określony z góry określony warunek. Jego składnia jest następująca:

=СЧЁТЕСЛИ(диапазон;критерий)

Jak widać, ten operator ma dwa argumenty. "Zakres" to adres tablicy elementów na arkuszu, na którym ma się liczyć.

"Kryterium" jest argumentem, który określa warunek, że musi zawierać komórki określonego obszaru, które mają być uwzględnione w liczeniu. Jako parametr możesz użyć wyrażenia liczbowego, tekstu lub odwołania do komórki, w której znajduje się kryterium. W takim przypadku można użyć następujących symboli, aby wskazać kryterium: "<" ( "less" ), ">" ( "more" ), "=" ( "równy" ), "<>" ( "nie równy" ). Na przykład, jeśli określisz wyrażenie "<50" , w obliczeniach uwzględnione zostaną tylko elementy określone przez argument "Zakres" , w którym wartości liczbowe są mniejsze niż 50. Wykorzystanie tych znaków do określenia parametrów będzie miało znaczenie dla wszystkich pozostałych opcji, które zostaną omówione w tej lekcji poniżej.

A teraz spójrzmy na konkretny przykład działania tego operatora w praktyce.

Jest więc tabela, która pokazuje dochód pięciu sklepów tygodniowo. Musimy znać liczbę dni w tym okresie, w których w sklepie 2 przychody ze sprzedaży przekroczyły 15 000 rubli.

1. Wybierz element arkusza, w którym operator wydrukuje wynik obliczeń. Następnie kliknij ikonę "Wstaw funkcję" .
2. Uruchomi się Kreator funkcji . Przechodzimy do bloku "Statystycznego" . Tam znajdujemy i rozróżniamy nazwę "KRAJE" . Następnie kliknij przycisk "OK" .
3. Okno argumentów powyższego operatora jest aktywne. W polu "Zakres" określ obszar komórki, w którym będą wykonywane obliczenia. W naszym przypadku powinieneś wybrać zawartość linii "Sklep 2" , w której znajduje się przychód dziennie. Ustaw kursor w określonym polu i trzymając lewy przycisk myszy wybierz odpowiednią tablicę w tabeli. Adres wybranej tablicy zostanie wyświetlony w oknie.

   W kolejnym polu "Kryterium" wystarczy podać bezpośredni parametr wyboru. W naszym przypadku musimy liczyć tylko te elementy tabeli, w których wartość przekracza 15000. Dlatego używamy klawiatury, aby wjechać do określonego pola wyrażeniem "> 15000" .

   Po wykonaniu wszystkich powyższych czynności, kliknij przycisk "OK" .

4. Program oblicza i wyświetla wynik w elemencie arkusza, który został podświetlony przed aktywacją kreatora funkcji . Jak widać, w tym przypadku wynik jest równy liczbie 5. Oznacza to, że w wybranej tablicy w pięciu komórkach są wartości przekraczające 15000. Oznacza to, że możemy wywnioskować, że w sklepie 2 w ciągu pięciu dni analizowanej siódemki dochód przekroczył 15 000 rubli.

Lekcja: Kreator funkcji w programie Excel

WĘGIEL

Kolejną funkcją, która obsługuje kryteria, jest COUNTRY . Odnosi się również do grupy statystycznej operatorów. Zadaniem programu COUNTERMAN jest liczenie komórek w określonej macierzy, które spełniają określony zestaw warunków. Jest to fakt, że można określić nie jeden, ale kilka parametrów i odróżnić ten operator od poprzedniego. Składnia jest następująca:

=СЧЁТЕСЛИМН(диапазон_условия1;условие1;диапазон_условия2;условие2;…)

"Zakres warunków" jest identyczny z pierwszym argumentem poprzedniego zdania. Oznacza to, że jest to odniesienie do obszaru, w którym zostaną policzone komórki spełniające określone warunki. Ten operator pozwala określić kilka takich obszarów jednocześnie.

"Warunek" to kryterium określające, które elementy z odpowiedniego zestawu danych zostaną uwzględnione w obliczeniach, a które nie. Każdy dany obszar danych musi być określony osobno, nawet jeśli jest zgodny. Wymagane jest, aby wszystkie tablice używane jako obszary warunkowe miały taką samą liczbę wierszy i kolumn.

Aby określić kilka parametrów tego samego obszaru danych, na przykład, aby policzyć liczbę komórek, w których wartości są większe niż pewna liczba, ale mniejsza niż inna liczba, należy określić tę samą tablicę kilka razy, co argument "Warunek" . Jednak jako odpowiednie argumenty "Warunek" należy stosować różne kryteria.

Na przykładzie tego samego stołu z cotygodniowymi dochodami ze sklepów spojrzymy, jak to działa. Musimy znać liczbę dni w tygodniu, kiedy dochód we wszystkich wskazanych punktach sprzedaży osiągnął ustaloną dla nich stawkę. Stawki przychodu są następujące:

• Kup 1 - 14000 rubli;
• Kup 2 - 15 000 rubli;
• Kup 3 - 24000 rubli;
• Kup 4 - 11 000 rubli;
• Kup 5 - 32000 rubli.

1. Aby wykonać powyższe zadanie, podświetl kursor z elementem arkusza roboczego, na który będzie wynik wyniku przetwarzania danych KRAJU . Klikamy na ikonę "Wstaw funkcję" .
2. Przełączając się do Kreatora funkcji , wracamy do bloku "Statystycznego" . Na liście należy znaleźć nazwę FORMULARZA KRAJU i dokonać jego wyboru. Po wykonaniu tej czynności należy kliknąć przycisk "OK" .
3. Po wykonaniu powyższego algorytmu akcji otwiera się okno z argumentami COUNTLINE .

   W polu "Warunek zasięgu1" wprowadź adres linii, w której znajdują się dane dotyczące przychodu tygodniowego w Sklepie 1. Aby to zrobić, umieść kursor w polu i wybierz odpowiedni wiersz w tabeli. Współrzędne są wyświetlane w oknie.

   Biorąc pod uwagę, że dzienna stopa przychodu w Sklepie 1 wynosi 14 000 rubli, to w polu "Warunek 1" wpisujemy wyrażenie "> 14000" .

   W polu "Zakres warunków2 (3,4,5)" należy wprowadzić współrzędne linii z cotygodniowymi dochodami zgodnie ze Store 2, Store 3, Store 4 i Store 5. Akcja jest wykonywana według tego samego algorytmu, co dla pierwszego argumentu z tej grupy.

   W polach "Warunek2 ", "Warunek3" , "Warunek4" i "Warunek5" wpisujemy odpowiednio wartości "> 15000 ", "> 24000" , "> 11000" i "> 32000" . Jak można się domyślić, wartości te odpowiadają przedziałom dochodów przekraczającym normę dla odpowiedniego sklepu.

   Po wprowadzeniu wszystkich niezbędnych danych (łącznie 10 pól), kliknij przycisk "OK" .

4. Program oblicza i wyświetla wynik na ekranie. Jak widać, jest równa liczbie 3. Oznacza to, że w ciągu trzech dni od analizowanego tygodnia dochody we wszystkich punktach sprzedaży przekroczyły ustaloną dla nich stawkę.

Teraz nieco zmieńmy ten problem. Powinniśmy obliczyć liczbę dni, w których Sklep 1 otrzymał dochód przekraczający 14 000 rubli, ale mniej niż 17 000 rubli.

1. Umieszczamy kursor w elemencie, w którym będzie wyprowadzane wyjście wyników obliczeń. Klikamy ikonę "Wstaw funkcję" nad obszarem roboczym arkusza.
2. Ponieważ ostatnio zastosowaliśmy formułę COUNCILS , teraz nie trzeba już iść do grupy kreatorów funkcji "Statystycznych" . Nazwę tego operatora można znaleźć w kategorii "10 ostatnio używanych" . Wybierz go i kliknij przycisk "OK" .
3. Otwiera okno argumentów operatora СЧЁТЕСЛИМН, który jest już nam znany. Ustawiamy kursor w polu "Zakres warunków1" i, po zaciśnięciu lewego przycisku myszy, wybieramy wszystkie komórki, które zawierają przychód w dniach przechowywania 1. Znajdują się one w rzędzie, który nazywa się "Sklep 1" . Następnie współrzędne danego obszaru zostaną odzwierciedlone w oknie.

   Następnie ustawiamy kursor w polu "Warunek1" . Tutaj musimy określić dolną granicę wartości w komórkach, które będą brały udział w obliczeniach. Określ wyrażenie "> 14000" .

   W polu "Zakres warunków2" wpisujemy ten sam adres w taki sam sposób, jak wprowadzono w polu "Zakres warunków1" , tzn. Ponownie wprowadzamy współrzędne komórki z wartościami przychodów w pierwszym punkcie sprzedaży.

   W polu "Warunek2" wskazujemy górny limit wyboru: "<17000" .

   Po wykonaniu wszystkich powyższych czynności kliknij przycisk "OK" .

4. Program wyprowadza wynik obliczeń. Jak widać, ostateczna wartość jest równa 5. Oznacza to, że w ciągu 5 dni analizowanej siódemki przychody w pierwszym sklepie mieściły się w przedziale od 14 000 do 17 000 rubli.

LATO

Inny operator, który używa kryteriów, to LATO . W przeciwieństwie do poprzednich funkcji, odnosi się do matematycznego bloku operatorów. Jego zadaniem jest podsumowanie danych w komórkach, które odpowiadają pewnym warunkom. Składnia jest następująca:

=СУММЕСЛИ(диапазон;критерий;[диапазон_суммирования])

Argument "Zakres" wskazuje obszar komórek, które będą sprawdzane pod kątem zgodności z warunkiem. W rzeczywistości opiera się na tej samej zasadzie, co ten sam argument funkcji RADY .

"Kryterium" jest obowiązkowym argumentem, który określa parametr wyboru komórki z określonego obszaru danych, który ma zostać zsumowany. Zasady wskazania są takie same jak analogicznych argumentów poprzednich operatorów, które rozważaliśmy powyżej.

"Zakres sumowania" jest opcjonalnym argumentem. Wskazuje konkretny obszar tablicy, w którym zostanie wykonane sumowanie. Jeśli go pominiesz i nie określisz, domyślnie przyjmuje się, że jest on równy wartości wymaganego argumentu "Zakres" .

Teraz, jak zawsze, rozważ zastosowanie tego operatora w praktyce. W oparciu o tę samą tabelę mamy do czynienia z zadaniem obliczenia kwoty przychodu w Sklepie 1 za okres od 11.03.2017.

1. Wybierz komórkę, w której wynik będzie wyprowadzany. Klikamy na ikonę "Wstaw funkcję" .
2. Przejdź do kreatora funkcji w bloku "Mathematical" i znajdź nazwę "SUMMER" . Kliknij przycisk "OK" .
3. Zostanie uruchomione okno argumentów funkcji SUMIFER . Istnieją trzy pola odpowiadające argumentom określonego operatora.

   W polu "Zakres" wprowadź obszar tabeli, w którym będą znajdować się wartości do sprawdzenia pod kątem zgodności z warunkami. W naszym przypadku będzie to ciąg dat. Ustawiamy kursor w tym polu i zaznaczamy wszystkie komórki zawierające daty.

   Ponieważ musimy dodać tylko wpływy z 11 marca, wpisujemy wartość "> 10.03.2017" w polu "Kryterium" .

   W polu "Zakres sumowania" należy podać obszar, którego wartości, odpowiadające określonym kryteriom, zostaną zsumowane. W naszym przypadku są to wartości przychodów z linii "Shop1" . Wybierz odpowiednią tablicę elementów arkusza.

   Po wprowadzeniu wszystkich określonych danych kliknij przycisk "OK" .

4. Następnie wynik przetwarzania danych przez funkcję LATO zostanie wyświetlony we wcześniej określonym elemencie arkusza. W naszym przypadku jest to 47921.53. Oznacza to, że począwszy od 11.03.2017 do końca analizowanego okresu, łączne przychody dla Sklepu 1 wyniosły 47 921,53 rubli.

PODSUMOWANIE

Kończymy badanie operatorów, którzy używają kryteriów, zatrzymując się przy funkcji PODSUMOWANIE . Zadaniem tej funkcji matematycznej jest sumowanie wartości wskazanych obszarów tabeli, wybranych kilkoma parametrami. Składnia określonego operatora to:

=СУММЕСЛИМН(диапазон_суммирования;диапазон_условия1;условие1;диапазон_условия2;условие2;…)

"Zakres sumowania" jest argumentem, który jest adresem tej macierzy, do której dodane zostaną komórki odpowiadające określonemu kryterium.

"Zakres warunków" jest argumentem reprezentującym tablicę danych, która jest sprawdzana pod kątem zgodności ze stanem;

"Warunek" jest argumentem, który jest kryterium wyboru dla dodawania.

Ta funkcja oznacza operacje z kilkoma zestawami podobnych operatorów.

Zobaczmy, jak ten operator ma zastosowanie do rozwiązywania problemów w kontekście naszej tabeli przychodów ze sprzedaży w punktach sprzedaży. Będziemy musieli obliczyć dochód, jaki Sklep 1 przyniósł w okresie od 9 do 13 marca 2017 r. Jednocześnie przy obliczaniu dochodu należy uwzględnić tylko te dni, w których przychód przekracza 14 000 rubli.

1. Ponownie wybierz komórkę, aby wyświetlić sumę i kliknij ikonę "Wstaw funkcję" .
2. W kreatorze funkcji najpierw przechodzimy do bloku "Matematycznego" i tam wybieramy element o nazwie "SUMMESLIMN" . Kliknij przycisk "OK" .
3. Wyświetlane jest okno argumentów operatora, którego nazwa została wymieniona powyżej.

   Ustaw kursor w polu "Zakres sumowania" . W przeciwieństwie do kolejnych argumentów, ten jedyny w swoim rodzaju i wskazuje na tablicę wartości, w której zostanie wykonane sumowanie danych pasujących do określonych kryteriów. Następnie wybierz obszar linii "Sklep1" , który opublikował wartości przychodów dla odpowiedniego gniazdka.

   Po wyświetleniu adresu w oknie, przejdź do pola "Zakres warunków1" . Tutaj będziemy musieli wyświetlić współrzędne wiersza z datami. Zaciskamy lewy przycisk myszy i wybieramy wszystkie daty w tabeli.

   Ustawiamy kursor w polu "Warunek1" . Pierwszy warunek polega na tym, że podsumujemy dane nie wcześniej niż 9 marca. Dlatego wpisujemy wartość "> 08.03.2017" .

   Przechodzimy do argumentu "Zakres warunków2" . Tutaj musisz wprowadzić te same współrzędne, które zostały zapisane w polu "Warunek zasięgu1" . Robimy to w ten sam sposób, czyli podświetlając wiersz z datami.

   Ustaw kursor w polu "Warunek2" . Drugi warunek jest taki, że dni, w których dochody zostaną dodane, nie mogą być późniejsze niż 13 marca. Dlatego zapisujemy następujące wyrażenie: «<14.03.2017» .

   Przejdź do pola "Zakres warunków2" . W takim przypadku musimy wybrać tę samą tablicę, której adres został wprowadzony jako tablica sumy.

   Po wyświetleniu adresu określonej tablicy w oknie, przejdź do pola "Warunek3" . Biorąc pod uwagę, że w podsumowaniu uwzględnione zostaną tylko wartości przekraczające 14 000 rubli, odnotujemy następujący znak: "> 14000" .

   Po ostatniej akcji kliknij przycisk "OK" .

4. Program wyprowadza wynik do arkusza. Jest równa 62491.38. Oznacza to, że w okresie od 9 do 13 marca 2017 r. Kwota przychodu, gdy została dodana w dniach, gdy przekracza 14 000 rubli, wynosiła 62491.38 rubli.

Formatowanie warunkowe

Ostatnim, opisanym przez nas narzędziem, przy pracy z określonymi kryteriami, jest formatowanie warunkowe. Wykonuje określone formatowanie komórek, które spełniają określone warunki. Przyjrzyjmy się przykładowi pracy z formatowaniem warunkowym.

Wybierz komórki w tabeli na niebiesko, gdzie wartości na dzień przekraczają 14 000 rubli.

1. Wybierz całą tablicę pozycji w tabeli, która wskazuje dochody placówek według dnia.
2. Przechodzimy do zakładki "Strona główna" . Klikamy ikonę "Formatowanie warunkowe" , znajdującą się w bloku "Style" na taśmie. Wyświetlana jest lista akcji. Klikamy w niego na pozycji "Utwórz regułę ..." .
3. Okno generowania reguł formatowania zostanie aktywowane. W obszarze wyboru typu reguły wybierz nazwę "Formatuj tylko komórki, które zawierają" . W pierwszym polu bloku warunku, z listy możliwych opcji, wybierz "Wartość komórki" . W następnym polu wybierz pozycję "Więcej" . W tym ostatnim określamy samą wartość, z czego więcej musimy sformatować elementy tabeli. Mamy 14000. Aby wybrać rodzaj formatowania, klikamy przycisk "Formatuj ..." .
4. Okno formatu jest aktywne. Przejście do zakładki "Wypełnij" . Из предложенных вариантов цветов заливки выбираем синий, щелкая по нему левой кнопкой мыши. После того, как выбранный цвет отобразился в области «Образец» , клацаем по кнопке «OK» .
5. Автоматически происходит возврат к окну генерации правила форматирования. В нём также в области «Образец» отображается синий цвет. Тут нам нужно произвести одно единственное действие: клацнуть по кнопке «OK» .
6. После выполнения последнего действия, все ячейки выделенного массива, где содержится число большее, чем 14000, будут залиты синим цветом.

Более подробно о возможностях условного форматирования рассказывается в отдельной статье.

Lekcja: Условное форматирование в программе Эксель

Как видим, с помощью инструментов, использующих при своей работе критерии, в Экселе можно решать довольно разноплановые задачи. Это может быть, как подсчет сумм и значений, так и форматирование, а также выполнение многих других задач. Основными инструментами, работающими в данной программе с критериями, то есть, с определенными условиями, при выполнении которых активируется указанное действие, является набор встроенных функций, а также условное форматирование.

• ← Budowanie wykresu zależności w programie Microsoft Excel
• Tworzenie harmonogramu sieciowego w programie Microsoft Excel →