Szybki raport z wykorzystaniem formuł tablicowych Wyznaczenie wartości średniej, maksymalnej czy minimalnej nie jest trudnym zadaniem. Wystarczy, że zastosujemy odpowiednią funkcję. Niestety w przypadku, gdy obliczenie ma uwzględniać dodatkowe kryterium i ma dotyczyć każdego z elementów figurujących na długiej liście staje się to niezwykle pracochłonne. Wtedy warto zastosować formuły tablicowe.
Przyjmijmy, że w arkuszu znajduje się lista towarów sprzedanych w sklepiku szkolnym pewnym okresie. Na jej podstawie potrzebujemy szybko sporządzić raport zawierający średnią liczbę sprzedanych sztuk, a także najmniejszą i największą. Wyniki mają dotyczyć każdego towaru.
W pierwszej kolejności przygotujemy tabelę pomocniczą, w której zostaną zwrócone wyniki. W tym celu:
Wpisz nagłówki kolumn zgodnie z rysunkiem.
W kolumnie Towar
wprowadź nazwy wszystkich towarów znajdujących się na właściwej liście.
Tabela pomocnicza jest gotowa, możemy więc wprowadzić formuły. W tym celu:
Zaznacz komórkę F2
i wpisz do niej następującą formułę:
Linia 1. znak równości ŚREDNIA otwórz nawias okrągły JEŻELI otwórz nawias okrągły $B$2 dwukropek $B$35 znak równości $E2 średnik $C$2 dwukropek $C$35 średnik cudzysłów cudzysłów zamknij nawias okrągły zamknij nawias okrągły.
=ŚREDNIA(JEŻELI($B$2:$B$35=$E2;$C$2:$C$35;""))
i koniecznie zatwierdź ją kombinacją klawiszy [Ctrl]+[Shift ]+[Enter]
, ponieważ jest to formuła tablicowa.
Skopiuj ją do komórek poniżej.
Do komórki G2
wpisz formułę zwracającą zakup najmniejszej liczby sztuk danego towaru.
Linia 1. znak równości MIN otwórz nawias okrągły JEŻELI otwórz nawias okrągły $B$2 dwukropek $B$35 znak równości $E2 średnik $C$2 dwukropek $C$35 średnik cudzysłów cudzysłów zamknij nawias okrągły zamknij nawias okrągły.
=MIN(JEŻELI($B$2:$B$35=$E2;$C$2:$C$35;""))
Zatwierdź ją wspomnianą kombinacją klawiszy i także skopiuj poniżej.
W komórce H2
powinna znaleźć się formuła zwracająca największe zamówienia. Jak się pewnie domyślasz, będzie wyglądała bardzo podobnie do poprzednich:
Linia 1. znak równości MAX otwórz nawias okrągły JEŻELI otwórz nawias okrągły $B$2 dwukropek $B$35 znak równości $E2 średnik $C$2 dwukropek $C$35 średnik cudzysłów cudzysłów zamknij nawias okrągły zamknij nawias okrągły.
=MAX(JEŻELI($B$2:$B$35=$E2;$C$2:$C$35;""))
Funkcja JEŻELI
przeszukuje wszystkie komórki z zakresu B2:B35
w poszukiwaniu nazwy zgodnej ze znajdującą się w komórce E2
. Na tej podstawie tworzona jest tablica wartości zawierająca liczby sztuk, które odpowiadają Towarowi 1
. Na nich są przeprowadzane dalsze obliczenia za pomocą funkcji ŚREDNIA
, MAX
lub MIN
.
RfLPnQVI4tuwR Nagranie filmowe dotyczące szybkiego raportu z wykorzystywania formuł tablicowych
Nagranie filmowe dotyczące szybkiego raportu z wykorzystywania formuł tablicowych
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.
Film dostępny pod adresem /preview/resource/RfLPnQVI4tuwR
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.
Nagranie filmowe dotyczące szybkiego raportu z wykorzystywania formuł tablicowych
Pobierz przykładowe dane:
RV53avDRVFIjl
Plik XLSX o rozmiarze 10.41 KB w języku polskim
Polecenie 1
Twój wujek poprosił cię o pomoc w przygotowaniu raportu dla jego firmy sprzedaży 5 produktów. Oblicz średnią (zaokrąglij ją do całkowitych wartości) oraz minimalną i maksymalną ilość danego typu towaru, jaka została kupiona.
Pokaż rozwiązanie Przykładowe rozwiązanie zadania:
Formuły z poszczególnych komórek:
F2
= ZAOKR.DO.CAŁK(ŚREDNIA(JEŻELI($C$2:$C$31=$F24;$D$2:$D$31;" ")))
G2
= MIN(JEŻELI($C$2:$C$31=$F24;$D$2:$D$31;" "))
H2
= MAX(JEŻELI($C$2:$C$31=$F24;$D$2:$D$31;" "))
R1ZApdS6MCksI Na zrzucie ekranu widoczny jest fragment arkusza Excel . W komórce A1 dodano tytuł numer transakcji, W komórkach od A2 do A19 wpisano kolejne numery porządkowe. W komórce B1 wpisano tytuł towar. W komórkach od B2 do B19 wpisano oznaczenia liczbowe sprzedawanych towarów. W komórce C1 wpisano tytuł ilość sztuk. W komórkach od C2 do C19 wpisano różne liczby. Kolumna D pozostaje pusta. W komórce E1 wpisano tytuł Towar. W komórkach od E2 do E6 wpisano kolejne numery porządkowe. W komórce F1 wpisano tytuł Średnia. W komórkach od F2 do F5 znajdują się formuły różniące się odwołaniem do danych zamieszczonych w komórkach kolumny E, czyli od E2 do E6. Formuła w komórce F2 wygląda następująco =ZAOKR.DO.CAŁK(ŚREDNIA(JEŻELI($B$2:$B31=$E2;$C$2:$C$31;” ”))). W komórce G1 wpisano tytuł Min. W komórkach od G2 do G5 znajdują się formuły różniące się odwołaniem do danych zamieszczonych w komórkach kolumny E, czyli od E2 do E6. Formuła w komórce G2 napisano następująco =MIN(JEŻELI($B$2:$B31=$E2;$C$2:$C$31;” ”)). W komórce H1 wpisano tytuł Max. W komórkach od H2 do H5 znajdują się formuły różniące się odwołaniem do danych zamieszczonych w komórkach kolumny E, czyli od E2 do E6. Formułę obliczania należy wpisać w odpowiedniej komórce. W tym przypadku w komórce H2. Brzmi ona następująco =MAX(JEŻELI($B$2:$B31=$E2;$C$2:$C$31;” ”)).
3
Polecenie 2
Zmodyfikuj arkusz z poprzedniego zadania. Ustal stałą cenę dla każdego rodzaju towaru i zapisz je w oddzielnej tabeli. Uzupełnij swój arkusz o indywidualne numery kupców, którzy dokonali danej transakcji. Znajdź stałych klientów firmy wujka tzn. takich, którzy dokonali więcej niż 5 transakcji. Oblicz średnią oraz minimalną i maksymalną kwotę jaką wydali w ramach jednej transakcji.
Pokaż rozwiązanie Przykładowe rozwiązanie zadania:
Formuły z poszczególnych komórek:
I2
= JEŻELI(H2>=5;ZAOKR.DO.CAŁK(ŚREDNIA(JEŻELI($B$2:$B$31=$G2;$E$2:$E$31;" ")));" ")
J2
= JEŻELI(H2>=5;MIN(JEŻELI($B$2:$B$31=$G2;$E$2:$E$31;" "));" ")
K2
= JEŻELI(H2>=5;MAX(JEŻELI($B$2:$B$31=$G2;$E$2:$E$31;" "));" ")
Rl0BJ1qTzISyz Na zrzucie ekranu widoczny jest fragment arkusza Excel . W komórce A1 znajduje się napis numer transakcji. Od komórki A2 do A21 znajdują się numery transakcji. W komórce B1 znajduje się napis numer klienta. Od komórki B2 do B21 znajdują się numery klientów. W komórce C1 znajduje się napis towar. Od komórki C2 do C21 znajdują się numery towarów. W komórce D1 znajduje się napis ilości sztuk. Od komórki D2 do D21 znajdują się ilości sztuk. W komórce E1 wpisano tytuł KWOTA. W komórkach od E3 do E21 wpisano dane liczbowe wyliczone na podstawie formuły wpisanej w komórce E2. =WYSZUKAJ.PIONOWO(C2;$G$13:$H$18;2;FAŁSZ)*D2 W komórce G1 znajduje się napis numer klienta. W komórkach od G2 do G11 znajdują się numery klientów. W komórce H1 znajduje się napis Ilość transakcji. W komórkach od H2 do H11 znajdują się liczby od 1 do 6. W komórce I1 znajduje się napis Średnia. W komórkach I2, I5, I7 i I9 znajdują się liczby. W komórce J1 znajduje się napis Min. W komórkach J2, J5, J7 i J9 znajdują się liczby. W komórce K1 znajduje się napis Max W komórkach K2, K5, K7 i K9 znajdują się liczby. W komórce G13 znajduje się napis Towar, a w komórce H13 Cennik. W komórkach od G14 do G18 znajdują się liczby od 1 do 5 rosnąco. W komórkach od H14 do H18 znajdują się liczby od 3 do 10.
R1I7XLPBbg2H9 Na zrzucie ekranu widoczny jest fragment arkusza Excel . W komórce A1 znajduje się napis numer transakcji. Od komórki A2 do A21 znajdują się numery transakcji. W komórce B1 znajduje się napis numer klienta. Od komórki B2 do B21 znajdują się numery klientów. W komórce C1 znajduje się napis towar. Od komórki C2 do C21 znajdują się numery towarów. W komórce D1 znajduje się napis ilości sztuk. Od komórki D2 do D21 znajdują się ilości sztuk. W komórce E1 wpisano tytuł KWOTA. W komórkach od E2 do E21 wpisano liczby. W komórce G1 znajduje się napis numer klienta. W komórce G2 znajduje się formuła = UNIKATOWE(B2:B31) W komórkach od G3 do G11 znajdują się numery klientów. W komórce H1 znajduje się napis Ilość transakcji. W komórkach od H2 do H11 znajdują się liczby od 1 do 6. W komórce I1 znajduje się napis Średnia. W komórkach I2, I5, I7 i I9 znajdują się liczby. W komórce J1 znajduje się napis Min. W komórkach J2, J5, J7 i J9 znajdują się liczby. W komórce K1 znajduje się napis Max W komórkach K2, K5, K7 i K9 znajdują się liczby. W komórce G13 znajduje się napis Towar, a w komórce H13 Cennik. W komórkach od G14 do G18 znajdują się liczby od 1 do 5 rosnąco. W komórkach od H14 do H18 znajdują się liczby od 3 do 10.
R1NTePQNsMS7t Na zrzucie ekranu widoczny jest fragment arkusza Excel . W komórce A1 znajduje się napis numer transakcji. Od komórki A2 do A21 znajdują się numery transakcji. W komórce B1 znajduje się napis numer klienta. Od komórki B2 do B21 znajdują się numery klientów. W komórce C1 znajduje się napis towar. Od komórki C2 do C21 znajdują się numery towarów. W komórce D1 znajduje się napis ilości sztuk. Od komórki D2 do D21 znajdują się ilości sztuk. W komórce E1 wpisano tytuł KWOTA. W komórkach od E2 do E21 wpisano liczby. W komórce G1 znajduje się napis numer klienta. W komórkach od G2 do G11 znajdują się numery klientów. W komórce H1 znajduje się napis Ilość transakcji. W komórce H2 znajduje się formuła =LICZ.JEŻELI($B$2:$B$31;G2) W komórkach od H3 do H11 znajdują się liczby od 1 do 6. W komórce I1 znajduje się napis Średnia. W komórkach I2, I5, I7 i I9 znajdują się liczby. W komórce J1 znajduje się napis Min. W komórkach J2, J5, J7 i J9 znajdują się liczby. W komórce K1 znajduje się napis Max W komórkach K2, K5, K7 i K9 znajdują się liczby. W komórce G13 znajduje się napis Towar, a w komórce H13 Cennik. W komórkach od G14 do G18 znajdują się liczby od 1 do 5 rosnąco. W komórkach od H14 do H18 znajdują się liczby od 3 do 10.
R1Pia8aJfnt0s Na zrzucie ekranu widoczny jest fragment arkusza Excel . W komórce A1 znajduje się napis numer transakcji. Od komórki A2 do A21 znajdują się numery transakcji. W komórce B1 znajduje się napis numer klienta. Od komórki B2 do B21 znajdują się numery klientów. W komórce C1 znajduje się napis towar. Od komórki C2 do C21 znajdują się numery towarów. W komórce D1 znajduje się napis ilości sztuk. Od komórki D2 do D21 znajdują się ilości sztuk. W komórce E1 wpisano tytuł KWOTA. W komórkach od E2 do E21 wpisano liczby. W komórce G1 znajduje się napis numer klienta. W komórkach od G2 do G11 znajdują się numery klientów. W komórce H1 znajduje się napis Ilość transakcji. W komórkach od H2 do H11 znajdują się liczby od 1 do 6. W komórce I1 znajduje się napis Średnia. W komórce I2 znajduje się formuła = JEŻELI(H2>=5;ZAOKR.DO.CAŁK(ŚREDNIA(JEŻELI($B$2:$B$31=$G2;$E$2:$E$31;"")));"") W komórkach I5, I7 i I9 znajdują się liczby. W komórce J1 znajduje się napis Min. W komórkach J2, J5, J7 i J9 znajdują się liczby. W komórce K1 znajduje się napis Max W komórkach K2, K5, K7 i K9 znajdują się liczby. W komórce G13 znajduje się napis Towar, a w komórce H13 Cennik. W komórkach od G14 do G18 znajdują się liczby od 1 do 5 rosnąco. W komórkach od H14 do H18 znajdują się liczby od 3 do 10.
RsOyIylOsDxzJ Na zrzucie ekranu widoczny jest fragment arkusza Excel . W komórce A1 znajduje się napis numer transakcji. Od komórki A2 do A21 znajdują się numery transakcji. W komórce B1 znajduje się napis numer klienta. Od komórki B2 do B21 znajdują się numery klientów. W komórce C1 znajduje się napis towar. Od komórki C2 do C21 znajdują się numery towarów. W komórce D1 znajduje się napis ilości sztuk. Od komórki D2 do D21 znajdują się ilości sztuk. W komórce E1 wpisano tytuł KWOTA. W komórkach od E2 do E21 wpisano liczby. W komórce G1 znajduje się napis numer klienta. W komórkach od G2 do G11 znajdują się numery klientów. W komórce H1 znajduje się napis Ilość transakcji. W komórkach od H2 do H11 znajdują się liczby od 1 do 6. W komórce I1 znajduje się napis Średnia. W komórkach I2, I5, I7 i I9 znajdują się liczby. W komórce J1 znajduje się napis Min. W komórce J2 znajduje się formuła = JEŻELI(H2>=5;MIN(JEŻELI($B$2:$B$31=$G2;$E$2:$E$31;""));"") W komórkach J5, J7 i J9 znajdują się liczby. W komórce K1 znajduje się napis Max W komórkach K2, K5, K7 i K9 znajdują się liczby. W komórce G13 znajduje się napis Towar, a w komórce H13 Cennik. W komórkach od G14 do G18 znajdują się liczby od 1 do 5 rosnąco. W komórkach od H14 do H18 znajdują się liczby od 3 do 10.
R1VgV95h1oFfY Na zrzucie ekranu widoczny jest fragment arkusza Excel . W komórce A1 znajduje się napis numer transakcji. Od komórki A2 do A21 znajdują się numery transakcji. W komórce B1 znajduje się napis numer klienta. Od komórki B2 do B21 znajdują się numery klientów. W komórce C1 znajduje się napis towar. Od komórki C2 do C21 znajdują się numery towarów. W komórce D1 znajduje się napis ilości sztuk. Od komórki D2 do D21 znajdują się ilości sztuk. W komórce E1 wpisano tytuł KWOTA. W komórkach od E2 do E21 wpisano liczby. W komórce G1 znajduje się napis numer klienta. W komórkach od G2 do G11 znajdują się numery klientów. W komórce H1 znajduje się napis Ilość transakcji. W komórkach od H2 do H11 znajdują się liczby od 1 do 6. W komórce I1 znajduje się napis Średnia. W komórkach I2, I5, I7 i I9 znajdują się liczby. W komórce J1 znajduje się napis Min. W komórkach J2, J5, J7 i J9 znajdują się liczby. W komórce K1 znajduje się napis Max W komórce K2 znajduje się formuła = JEŻELI(H2>=5;MAX(JEŻELI($B$2:$B$31=$G2;$E$2:$E$31;""));"") W komórkach K2, K5, K7 i K9 znajdują się liczby. W komórce G13 znajduje się napis Towar, a w komórce H13 Cennik. W komórkach od G14 do G18 znajdują się liczby od 1 do 5 rosnąco. W komórkach od H14 do H18 znajdują się liczby od 3 do 10.