Wskazówki i przykłady dotyczące formuł tablicowych

Wskazówki i przykłady dotyczące formuł tablicowych

Formuła tablicowa to formuła, która może wykonywać wiele obliczeń na jednym lub większej liczby elementach w tablicy. Tablicę można sobie pomylić z wierszem lub kolumną wartości albo z połączeniem wierszy i kolumn wartości. Formuły tablicowe mogą zwracać wiele wyników lub jeden wynik.

Począwszy od aktualizacji dla programu Microsoft 365 z września 2018 r., dowolna formuła, która może zwrócić wiele wyników, automatycznie rozleje je albo w sąsiednich komórkach. Tej zmianie zachowania towarzyszy również kilka nowych funkcji tablicy dynamicznej. Dynamiczne formuły tablicowe, niezależnie od tego, czy są to istniejące funkcje, czy dynamiczne funkcje tablicowe, muszą być wprowadzane tylko w jednej komórce, a następnie potwierdzone naciśnięciem klawisza Enter. Wcześniejsze, starsze formuły tablicowe wymagają najpierw wybrania całego zakresu wyjściowego, a następnie potwierdzenia formuły za pomocą klawiszy Ctrl+Shift+Enter. Są one zwykle nazywane formułami CSE.

Za pomocą formuł tablicowych można wykonywać złożone zadania, takie jak:

  • Szybkie tworzenie przykładowych zestawów danych.

  • Zlicz liczbę znaków zawartych w zakresie komórek.

  • Sumowanie tylko liczb spełniających określone warunki, takich jak najmniejsze wartości w zakresie lub liczby, które należą do przedziału górnego i dolnego.

  • Sumowanie co N-tej wartości w zakresie wartości.

W poniższych przykładach popisano, jak tworzyć wielokomórkowe i jednokomórkowe formuły tablicowe. Tam, gdzie to możliwe, po opisie przykładów przedstawiono niektóre dynamiczne funkcje tablicowe, a także istniejące formuły tablicowe wprowadzone jako tablice dynamiczne i starsze.

Pobierz nasze przykłady

Pobierz przykładowy skoroszyt ze wszystkimi przykładami formuł tablicowych w tym artykule.

W tym ćwiczeniu pokazano, jak za pomocą wielokomórkowych i jednokomórkowych formuł tablicowych można obliczać zestaw danych dotyczących sprzedaży. W pierwszej części wielokomórkowa formuła posłuży do obliczenia zestawu sum częściowych. W drugiej części jednokomórkowa formuła zostanie użyta w celu obliczenia sumy końcowej.

  • Wielokomórkowa formuła tablicowa

    Wielokomórkowa funkcja tablicowa w komórce H10 =F10:F19*G10:G19 do obliczania liczby samochodów sprzedanych według ceny jednostkowej

  • Tutaj obliczamy łączną sprzedaż coupe i sedan dla każdego sprzedawcy, wprowadzając =F10:F19*G10:G19 w komórce H10.

    Po naciśnięciu klawisza Enterwyniki się rozlejeją w komórkach H10:H19. Zwróć uwagę, że zakres rozlanie jest wyróżniany obramowaniem po zaznaczeniu dowolnej komórki w zakresie rozlanie. Możesz również zauważyć, że formuły w komórkach H10:H19 są wyszarowane. Są one tylko do odwołania, więc jeśli chcesz dostosować formułę, musisz zaznaczyć komórkę H10, w której znajduje się formuła wzorcowa.

  • Jednokomórkowa formuła tablicowa

    Jednokomórkowa formuła tablicowa do obliczania sumy całkowitej za pomocą formuły =SUMA(F10:F19*G10:G19)

    W komórce H20 przykładowego skoroszytu wpisz lub skopiuj i wklej =SUMA(F10:F19*G10:G19),a następnie naciśnij klawisz Enter.

    W tym przypadku funkcja Excel wartości w tablicy (zakres komórek od F10 do G19), a następnie używa funkcji SUMA w celu dodania sum. Wynikiem jest suma końcowa o wartości 1 590 000 zł.

    W tym przykładzie pokazano, jak duże możliwości oferują formuły tego typu. Na przykład załóżmy, że użytkownik ma 1000 wierszy danych. Można zsumować wszystkie lub część danych, tworząc formułę tablicową w jednej komórce, zamiast przeciągania formuły przez 1000 wierszy. Należy również zauważyć, że formuła jednokomórkowa (w komórce H20) jest całkowicie niezależna od formuły wielokomórkowej (formuła w komórkach od H10 do H19). Jest to kolejna zaleta korzystania z formuł tablicowych — elastyczność. Możesz zmienić pozostałe formuły w kolumnie H bez wpływu na formułę w kolumnie H20. Dobrym rozwiązaniem może być też sporządzanie podobnych do tego sum niezależnie, ponieważ pomaga to zweryfikować dokładność wyników.

  • Ponadto dynamiczne formuły tablicowe mają następujące zalety:

    • Spójność (Consistency)    Jeśli klikniesz dowolną komórkę od komórki H10 w dół, zobaczysz tę samą formułę. Taka spójność umożliwia zapewnienie większej dokładności.

    • Bezpieczeństwo    Nie można zastąpić składnika wielokomórkowej formuły tablicowej. Na przykład kliknij komórkę H11 i naciśnij klawisz Delete. Excel nie zmieni wyniku tablicy. Aby ją zmienić, należy zaznaczyć lewą górną komórkę tablicy lub komórkę H10.

    • Mniejszy rozmiar plików    Często można użyć jednej formuły tablicowej zamiast kilku formuł pośrednich. Na przykład w przykładzie sprzedaży samochodów użyto jednej formuły tablicowej do obliczenia wyników w kolumnie E. Gdyby były używane standardowe formuły, takie jak =F10*G10, F11*G11, F12*G12 itd., do obliczenia tych samych wyników by było użytych 11 różnych formuł. To nie jest nic dużego, ale co zrobić, jeśli łącznie masz tysiące wierszy? Może to być bardzo ważne.

    • Wydajność    Funkcje tablicowe mogą być wydajnym sposobem tworzenia złożonych formuł. Formuła tablicowa =SUMA(F10:F19*G10:G19) jest taka sama jak formuła: =SUMA(F10*G10;F11*G11;F12*G12;F13*G13;F14*G14;F15*G15;F16*G16;F17*G17;F18*G18;F19*G19).

    • Rozlanie    Dynamiczne formuły tablicowe automatycznie rozleje się w zakresie wyjściowym. Jeśli dane źródłowe są w tabeli Excel, rozmiar dynamicznych formuł tablicowych zostanie automatycznie zmieniony wraz z dodawaniem lub usuwaniem danych.

    • #SPILL! błąd    Tablice dynamiczne wprowadziły błąd #SPILL!, co oznacza, że zamierzone zakresy rozlanych są z jakiegoś powodu zablokowane. Gdy rozwiążemy problem z zablokowaniem, formuła automatycznie się rozleje.

Stałe tablicowe to składniki formuł tablicowych. Aby utworzyć stałe tablicowe, należy wprowadzić listę elementów, a następnie ręcznie otoczyć listę nawiasami ({ }), tak jak pokazano poniżej:

={1;2;3;4;5} lub ={"Styczeń","Luty","Marzec"}

Rozdzielenie elementów średnikami spowoduje utworzenie tablicy poziomej (wiersza). Rozdzielenie elementów ukośnikami spowoduje utworzenie tablicy pionowej (kolumny). Aby utworzyć tablicę dwuwymiarową, należy rozgraniczać elementy w każdym wierszu średnikami, a każdy wiersz rozdzielać średnikami.

Wykonując poniższe procedury, można przećwiczyć tworzenie stałych poziomych, pionowych i dwuwymiarowych. Przedstawiono przykłady użycia funkcji SEKWENCJA do automatycznego generowania stałych tablicowych, a także samodzielnie wprowadzonych stałych tablicowych.

  • Tworzenie stałej poziomej

    Użyj skoroszytu z poprzednich przykładów lub utwórz nowy skoroszyt. Zaznacz dowolną pustą komórkę i wprowadź =SEKWENCJA(1;5). Funkcja SEKWENCJA tworzy tablicę 1 wiersz na 5 kolumn taką samą jak ={1;2;3;4;5}. Zostanie wyświetlony następujący wynik:

    Tworzenie poziomej stałej tablicowej przy użyciu funkcji =SEKWENCJA(1;5) lub ={1;2;3;4;5}

  • Tworzenie stałej pionowej

    Zaznacz dowolną pustą komórkę z pomieszczeniem i wprowadź =SEKWENCJA(5)lub ={1;2;3;4;5}. Zostanie wyświetlony następujący wynik:

    Tworzenie pionowej stałej tablicowej przy użyciu funkcji =SEKWENCJA(5) lub ={1;2;3;4;5}

  • Tworzenie stałej dwuwymiarowej

    Zaznacz dowolną pustą komórkę z pustą komórką z po prawej strony i poniżej nią, a następnie wprowadź =SEKWENCJA(3;4). Zostanie wyświetlony następujący wynik:

    Tworzenie stałej tablicowej o 3 wierszach na 4 kolumny przy użyciu funkcji =SEKWENCJA(3;4)

    Możesz również wprowadzić: lub ={1;2;3;4;5;6;7;8;9;10;11;12}, ale warto zwrócić uwagę na miejsce, w którym należy umieszczać średniki, a średniki.

    Jak widać, opcja SEKWENCJA daje istotne korzyści w związku z ręcznym wprowadzaniem wartości stałych tablicowych. Przede wszystkim pozwala zaoszczędzić czas, ale może też zmniejszyć liczbę błędów ręcznych. Jest również łatwiejsza do odczytania, szczególnie gdy średniki mogą być trudne do odróżnienia od separatorów przecinków.

Oto przykład, w ramach większej formuły są używane stałe tablicowe. W przykładowym skoroszycie przejdź do stałej w arkuszu formuł lub utwórz nowy arkusz.

W komórce D9 wprowadzono ciąg =SEKWENCJA(1;5;3;1),ale można też wprowadzić wartości 3, 4, 5, 6 i 7 w komórkach A9:H9. Nie ma nic specjalnego w tym konkretnym wyborze liczb, po prostu wybraliśmy coś innego niż 1-5 do różnicy.

W komórce E11 wprowadź =SUMA(D9:H9*SEKWENCJA(1;5))lub =SUMA(D9:H9*{1;2;3;4;5}). Formuły zwracają 85.

Używanie stałych tablicowych w formułach. W tym przykładzie u używaliśmy funkcji =SUMA(D9:H(*SEKWENCJA(1;5))

Funkcja SEKWENCJA tworzy odpowiednik stałej tablicowej {1;2;3;4;5}. Ponieważ Excel najpierw wykonuje operacje na wyrażeniach ujętych w nawiasy, następnymi dwoma elementami odtwarzaowymi są wartości komórek w komórkach D9:H9 i operator mnożenia (*). W tym punkcie formuła mnoży wartości w przechowywanej tablicy przez odpowiadające im wartości w stałej. Jest to odpowiednik formuły:

=SUMA(D9*1;E9*2;F9*3;G9*4;H9*5)lub =SUMA(3*1;4*2;5*3;6*4;7*5)

Na koniec funkcja SUMA dodaje wartości i zwraca wartość 85.

Aby uniknąć używania przechowywanej tablicy i całkowicie przechowywać operację w pamięci, możesz zamienić ją na inną stałą tablicową:

=SUMA(SEKWENCJA(1;5;3;1)*SEKWENCJA(1;5))lub =SUMA({3;4;5;6;7}*{1;2;3;4;5})

Elementy, których można używać w stałych tablicowych

  • Stałe tablicowe mogą zawierać liczby, tekst, wartości logiczne (takie jak PRAWDA i FAŁSZ) i wartości błędów, takie jak #N/D!. Można używać liczb w formatach całkowitym, dziesiętnym i naukowym. Dołączanie tekstu wymaga ujmowania go w cudzysłów ("tekst").

  • Stałe tablicowe nie mogą zawierać dodatkowych tablic, formuł ani funkcji. Innymi słowy mogą zawierać tylko tekst lub liczby oddzielone średnikami i odwrotnymi ukośnikami. Po wprowadzeniu formuły, takiej jak {1;2;A1:D4} lub {1;2;SUM(Q2:Z8)}, w programie Excel zostanie wyświetlony komunikat ostrzegawczy. Dodatkowo wartości liczbowe nie mogą zawierać znaków procenta, dolara, średników i nawiasów.

Jednym z najlepszych sposobów używania stałych tablicowych jest ich nazwanie. Stałe nazwane są znacznie prostsze w użyciu i za ich pomocą można ukryć część złożonych formuł tablicowych. Aby nazwać stałą tablicową i użyć jej w formule, wykonaj następujące czynności:

Przejdź do pola Formuły > nazwy zdefiniowane >Definiuj nazwę. W polu Nazwa wpisz Kwartał1. W polu Odwołuje się do wprowadź następującą stałą (pamiętaj, aby ręcznie wpisać nawiasy klamrowe):

={"Styczeń";"Luty";"Marzec"}

Okno dialogowe powinno teraz wyglądać tak:

Dodawanie nazwanej stałej tablicowej z > nazw zdefiniowanych > Menedżer nazw > Nowy

Kliknij przycisk OK,zaznacz dowolny wiersz z trzema pustymi komórkami i wprowadź =Kwartał1.

Zostanie wyświetlony następujący wynik:

Użyj nazwanej stałej tablicowej w formule, na przykład =Kwartał1, gdzie Kwartał1 został zdefiniowany jako ={"Styczeń","Luty","Marzec"}

Jeśli chcesz, aby wyniki rozlały się w pionie, a nie w poziomie, możesz użyć funkcji = TRANSPONUJ (kwartał1).

Jeśli chcesz wyświetlić listę 12 miesięcy, tak jak podczas tworzenia zestawienia finansowego, możesz przy użyciu funkcji SEKWENCJA utworzyć jedną dla bieżącego roku. Schludną funkcją jest to, że mimo wyświetlania tylko miesiąca, za nią znajduje się prawidłowa data, która może być wykorzystanie w innych obliczeniach. Te przykłady znajdują się w nazwanych stałych tablicowych i arkuszach szybkich przykładowych zestawów danych w przykładowym skoroszycie.

=TEKST(DATA(ROK(DZIŚ());SEKWENCJA(1;12);1);"mmm")

Użyj kombinacji funkcji TEKST, DATA, ROK, DZIŚ i SEKWENCJA, aby utworzyć dynamiczną listę 12 miesięcy

Ta funkcja używa funkcji DATA do utworzenia daty na podstawie bieżącego roku, funkcja SEKWENCJA tworzy stałą tablicową od 1 do 12 dla dat od stycznia do grudnia, a funkcja TEKST konwertuje format wyświetlania na "mmm" (sty, lut, mar itd.). Aby wyświetlić pełną nazwę miesiąca, na przykład Styczeń, należy użyć ciągu "mmmm".

Podczas używania stałej nazwanej jako formuły tablicowej należy pamiętać o wprowadzeniu znaku równości, tak jak w =Kwartał1, a nie tylko Kwartał1. Bez znaku równości program Excel zinterpretuje tablicę jako ciąg tekstowy i formuła nie zadziała w oczekiwany sposób. Należy też pamiętać, że można używać kombinacji funkcji, tekstu i liczb. Wszystko zależy od tego, jaką kreatywność chcesz uzyskać.

W poniższych przykładach pokazano kilka sposobów użycia stałych tablicowych w formułach tablicowych. W niektórych przykładach funkcji TRANSPONUJ używa się do konwertowania wierszy na kolumny i odwrotnie.

  • Wiele elementów w tablicy

    Wprowadź =SEKWENCJA(1;12)*2lub ={1;2;3;4;5;6;7;8;9;10;11;12}*2

    Możesz również dzielić przy użyciu (/), dodać za pomocą (+) i odejmować za pomocą (-).

  • Obliczanie kwadratów elementów w tablicy

    Wprowadź =SEKWENCJA(1;12)^2lub ={1;2;3;4;5;6;7;8;9;10;11;12}^2

  • Znajdowanie kwadratów elementów w tablicy

    Wprowadź =PIERWIASEK (SEKWENCJA(1;12)^2)lub =PIERWIAST({1;2;3;4;5;6;7;8;9;10;11;12}^2)

  • Transponowanie jednowymiarowego wiersza

    Wprowadź =TRANSPONUJ(SEKWENCJA(1;5))lub =TRANSPONUJ({1;2;3;4;5})

    Mimo że wprowadzono poziomą stałą tablicową, funkcja TRANSPONUJ konwertuje stałą tablicową na kolumnę.

  • Transponowanie jednowymiarowej kolumny

    Wprowadź =TRANSPONUJ(SEKWENCJA(5;1))lub =TRANSPONUJ({1;2;3;4;5})

    Mimo że wprowadzono pionową stałą tablicową, funkcja TRANSPONUJ konwertuje stałą na wiersz.

  • Transponowanie stałej dwuwymiarowej

    Wprowadź =TRANSPONUJ(SEKWENCJA(3;4))lub =TRANSPONUJ({1;2;3;4;5;6;7;8;9;10;11;12})

    Funkcja TRANSPONUJ konwertuje każdy wiersz na serię kolumn.

W tej sekcji znajdują się przykłady podstawowych formuł tablicowych.

  • Tworzenie tablicy na podstawie istniejących wartości

    W poniższym przykładzie wyjaśniono, jak używać formuł tablicowych w celu utworzenia nowej tablicy na pomocą istniejącej tablicy.

    Wprowadź =SEKWENCJA(3;6;10;10)lub ={10;20;30;40;50;60;70;80;90;100;110;120;130;140;150;160;170;180}

    Pamiętaj, aby wpisać { (otwierający nawias klamrowy) przed wpisaniem cyfry 10 i } (zamykający nawias klamrowy) po wpisaniu liczby 180, ponieważ tworzysz tablicę liczb.

    Następnie wprowadź =D9# lub=D9:I11 w pustej komórce. Zostanie wyświetlona tablica komórek 3 x 6 z wartościami widocznymi w zakresach D9:D11. Znak # nazywa się operatoremzakresu rozlanego, który Excel odwołanie do całego zakresu tablicy zamiast konieczności jego wpisywania.

    Używanie operatora zakresu rozlanego (#) do odwołania do istniejącej tablicy

  • Tworzenie stałej tablicowej na podstawie istniejących wartości

    Możesz uzyskać wyniki formuły tablicowej rozlanej i przekonwertować ją na jej części składowe. Zaznacz komórkę D9, a następnie naciśnij klawisz F2, aby przełączyć się do trybu edycji. Następnie naciśnij klawisz F9, aby przekonwertować odwołania do komórek na wartości, Excel następnie przekonwertowane na stałą tablicową. Po naciśnięciu klawisza Enterformuła =D9#powinna mieć akcję ={10;20;30;40;50;60;70;80;90}.

  • Zliczanie znaków w zakresie komórek

    W poniższym przykładzie pokazano, jak zliczyć liczbę znaków w zakresie komórek. Dotyczy to również spacji.

    Zliczaj całkowitą liczbę znaków w zakresie i innych tablic do pracy z ciągami tekstowymi.

    =SUMA(DŁ(C9:C13))

    W tym przypadku funkcja DŁ zwraca długość każdego ciągu tekstowego w każdej z komórek w zakresie. Następnie funkcja SUMA dodaje te wartości i wyświetla wynik (66). Jeśli chcesz uzyskać średnią liczbę znaków, możesz użyć:

    =ŚREDNIA(DŁ(C9:C13))

  • Zawartość najdłuższej komórki w zakresie C9:C13

    =INDEKS(C9:C13;DOPASUJ(MAX(DŁ(C9:C13));DŁ(C9:C13);0);1)

    Ta formuła działa tylko wtedy, gdy zakres danych zawiera jedną kolumnę komórek.

    Poniżej przedstawiono analizę zasady działania tej formuły, począwszy od elementów wewnętrznych i przechodząc do elementów zewnętrznych. Funkcja zwraca długość każdego z elementów w zakresie komórek D2:D6. Funkcja MAX oblicza największą spośród tych pozycji wartość, która odpowiada najdłuższemu ciągowi tekstowemu, który znajduje się w komórce D3.

    Tutaj sprawy nieco się komplikują. Funkcja MATCH oblicza przesunięcie (pozycję względną) komórki zawierającej najdłuższy ciąg tekstowy. W tym celu ta funkcja wymaga trzech argumentów: szukana wartość, przeszukiwana tablica i typ porównania. Funkcja PODAJ.POZYCJĘ wyszukuje w przeszukiwanej tablicy określoną szukaną wartość. W tym przypadku szukaną wartością jest najdłuższy ciąg tekstowy:

    MAX(DŁ(C9:C13)

    i taki ciąg znajduje się w tej tablicy:

    DŁ(C9:C13)

    W tym przypadku argument typ dopasowania to 0. Typem dopasowania może być wartość 1, 0 lub -1.

    • 1 . Zwraca największą wartość, która jest mniejsza lub równa wartości odnośnika

    • 0 — zwraca pierwszą wartość dokładnie równą wartości odnośnika.

    • -1 . Zwraca najmniejszą wartość, która jest większa lub równa określonej wartości odnośnika

    • Jeśli użytkownik pominie typ porównania, program Excel użyje wartości 1.

    Na koniec funkcja INDEKS pobiera te argumenty: tablicę oraz numer wiersza i numer kolumny w tej tablicy. Zakres komórek C9:C13 dostarcza tablicę, funkcja DOPASUJ dostarcza adres komórki, a ostatni argument (1) określa, że wartość pochodzi z pierwszej kolumny w tablicy.

    Aby uzyskać zawartość najmniejszego ciągu tekstowego, należy w powyższym przykładzie zamienić ciąg MAX na wartość MIN.

  • Znajdowanie n najmniejszych wartości w zakresie

    W tym przykładzie pokazano, jak znaleźć trzy najmniejsze wartości w zakresie komórek, gdzie tablica przykładowych danych w komórkach B9:B18 ma tworzona za pomocą: =ZAOST(LOSOWA.TABLICA(10;1)*100). Funkcja RANDARRAY jest nietrwałą funkcją, więc za każdym razem, gdy zostanie Excel obliczenia, otrzymasz nowy zestaw liczb losowych.

    Excel formuły tablicowej, aby znaleźć N-ty najmniejszą wartość: =MIN.K(B9#;SEKWENCJA(D9))

    Wprowadź =MIN.K(B9#;SEKWENCJA(D9); =MIN.K(B9:B18;{1;2;3})

    Ta formuła używa stałej tablicowej do trzykrotnego szacowania funkcji MIN.MAŁE i zwraca najmniejszych 3 elementy tablicy znajdującej się w komórkach B9:B18, gdzie 3 jest wartością zmienną w komórce D9. Aby znaleźć więcej wartości, można zwiększyć wartość w funkcji SEKWENCJA lub dodać więcej argumentów do stałej. W tej formule można także używać dodatkowych funkcji, takich jak SUMA lub ŚREDNIA. Na przykład:

    =SUMA(MIN.K(B9#;SEKWENCJA(D9))

    =ŚREDNIA(MIN.K(B9#;SEKWENCJA(D9))

  • Znajdowanie n największych wartości w zakresie

    Aby znaleźć największe wartości w zakresie, można zastąpić funkcję MAŁE funkcją DUŻE. Ponadto w poniższym przykładzie użyto funkcji WIERSZ i ADR.POŚR.

    Wprowadź =DUŻY(B9#;WIERSZ(INDIRECT("1:3")))lub =LARGE(B9:B18;WIERSZ(INDIRECT("1:3")))

    W tym miejscu przyda się nieco informacji o funkcjach WIERSZ i ADR.POŚR. Za pomocą funkcji WIERSZ można utworzyć tablicę zawierającą kolejne liczby całkowite. Na przykład wybierz puste pole i wprowadź:

    =WIERSZ(1:10)

    Ta formuła tworzy kolumnę zawierająca 10 kolejnych liczb całkowitych. Aby zobaczyć potencjalny problem, wstaw wiersz powyżej zakresu zawierającego formułę tablicową (czyli powyżej wiersza 1). Excel dostosuje odwołania do wierszy, a teraz formuła wygeneruje liczby całkowite z od 2 do 11. Aby rozwiązać ten problem, należy dodać do formuły funkcję ADR.POŚR:

    =WIERSZ(ADR.POŚR("1:10"))

    Funkcja INDIRECT używa ciągów tekstowych jako argumentów (dlatego zakres 1:10 jest otoczony cudzysłowami). Program Excel nie dostosowuje wartości tekstowych, gdy użytkownik wstawia wiersze lub w inny sposób przenosi formułę tablicową. Dzięki temu funkcja WIERSZ zawsze generuje odpowiednią tablicę liczb całkowitych. Równie łatwo możesz użyć funkcji SEKWENCJA:

    =SEKWENCJA(10)

    Sprawdźmy wcześniej używaną formułę — =MIN.K(B9#;WIERSZ(POŚR("1:3"))) — rozpoczynając od nawiasów wewnętrznych i wychodząc na zewnątrz: funkcja INDIRECT zwraca zestaw wartości tekstowych, w tym przypadku wartości od 1 do 3. Funkcja WIERSZ z kolei generuje trzykomórkową tablicę kolumnową. Funkcja LARGE używa wartości z zakresu komórek B9:B18 i jest szacowana trzy razy, raz dla każdego odwołania zwróconego przez funkcję WIERSZ. Aby znaleźć więcej wartości, należy dodać do funkcji INDIRECT większy zakres komórek. Na koniec, tak jak w przypadku przykładów SMALL, można użyć tej formuły z innymi funkcjami, takimi jak SUMA i ŚREDNIA.

  • Suma zakresu zawierającego wartości błędu

    Funkcja SUMA w programie Excel nie działa w przypadku próby zsumowanie zakresu zawierającego wartość błędu, taką jak #VALUE! lub #N/A. W tym przykładzie pokazano, jak zsumować wartości w zakresie o nazwie Dane, który zawiera błędy:

    Do radzenia sobie z błędami używaj tablic. Na przykład formuła =SUMA(JEŻELI(CZY.BŁĄD(Dane);"";Dane) zsumuje zakres o nazwie Dane, nawet jeśli zawiera błędy, takie jak #VALUE! lub #NA!.

  • =SUMA(JEŻELI(CZY.BŁĄD(Dane);"";Dane))

    Formuła tworzy nową tablicę zawierającą oryginalne wartości minus wszelkie wartości błędów. Zaczynając od funkcji wewnętrznych i wychodząc na zewnątrz, funkcja CZY.BŁĄD wyszukuje błędy w zakresie komórek (Danych). Funkcja JEŻELI zwraca określoną wartość, jeśli określony warunek ma wartość PRAWDA, lub inną wartość, jeśli ma wartość FAŁSZ. W tym przypadku zwraca puste ciągi ("") dla wszystkich wartości błędów, ponieważ mają one wartość PRAWDA, i zwraca pozostałe wartości z zakresu (danych), ponieważ dla tych wartości są zwracane wartości FAŁSZ, co oznacza, że nie zawierają one wartości błędów. Następnie funkcja SUMA oblicza sumę filtrowanej tablicy.

  • Ustalanie liczby wartości błędu w zakresie

    Ten przykład jest podobny do poprzedniej formuły, ale zwraca liczbę wartości błędów w zakresie o nazwie Dane, zamiast je odfiltrowyowywować:

    =SUMA(JEŻELI(CZY.BŁĄD(Dane);1;0))

    Ta formuła tworzy tablicę zawierającą wartość 1 dla komórek zawierających błędy i wartość 0 dla komórek niezawierających błędów. Można uprościć tę formułę i uzyskać taki sam wynik, usuwając trzeci argument funkcji JEŻELI:

    =SUMA(JEŻELI(CZY.BŁĄD(Dane);1))

    Jeśli nie zostanie określony argument, funkcja JEŻELI zwróci wartość FAŁSZ, gdy komórka nie będzie zawierać wartości błędu. Tę formułę można jeszcze bardziej uprościć:

    =SUMA(JEŻELI(CZY.BŁĄD(Dane)*1))

    Ta wersja działa, ponieważ PRAWDA*1=1, a FAŁSZ*1=0.

Może zaistnieć konieczność zsumowania wartości na podstawie warunków.

Tablice mogą być tablicowe do obliczania na podstawie określonych warunków. Formuła =SUMA(JEŻELI(>0;Sprzedaż)) zsumuje wszystkie wartości większe niż 0 w zakresie o nazwie Sprzedaż.

Na przykład ta formuła tablicowa sumuje tylko dodatnie liczby całkowite w zakresie o nazwie Sprzedaż, które reprezentują komórki E9:E24 w powyższym przykładzie:

=SUMA(JEŻELI(Sprzedaż>0;Sprzedaż))

Funkcja JEŻELI tworzy tablicę wartości dodatnich i fałszowych. Funkcja SUMA ignoruje wartości FAŁSZ, ponieważ 0+0=0. Zakres komórek używany w tej formule może składać się z dowolnej liczby wierszy i kolumn.

Można także sumować wartości, które spełniają kilka warunków. Na przykład ta formuła tablicowa oblicza wartości większe niż 0 ORAZ mniejsze niż 2500:

=SUMA((>0)*(Sprzedaż<2500)*(Sprzedaż))

Należy pamiętać, że ta formuła zwróci błąd, jeśli w zakresie będzie znajdować się co najmniej jedna komórka, której wartość nie jest wartością liczbową.

Można też tworzyć formuły tablicowe używające warunków typu LUB. Można na przykład zsumować wartości większe niż 0 LUB mniejsze niż 2500:

=SUMA(JEŻELI((>0)+(Sprzedaż<2500);Sprzedaż))

Funkcji ORAZ i LUB nie można używać w formułach tablicowych bezpośrednio, ponieważ zwracają one pojedynczy wynik (PRAWDA lub FAŁSZ), a funkcje tablicowe wymagają tablic wyników. Można obejść ten problem, korzystając z logiki pokazanej w poprzedniej formule. Innymi słowy można wykonywać operacje matematyczne, takie jak dodawanie lub mnożenie wartości spełniających warunek LUB lub ORAZ.

W tym przykładzie pokazano, jak można usunąć zera z zakresu w sytuacji, gdy trzeba obliczyć średnią z wartości w zakresie. W formule jest używany zakres danych o nazwie Sprzedaż:

=ŚREDNIA(JEŻELI(Sprzedaż<>0;Sprzedaż))

Funkcja JEŻELI tworzy tablicę wartości, które nie są równe 0, a następnie przekazuje te wartości do funkcji ŚREDNIA.

Ta formuła tablicowa porównuje wartości w dwóch zakresach komórek o nazwach MojeDane i TwojeDane i zwraca liczbę różnic między tymi zakresami. Jeśli zawartość obu zakresów jest identyczna, formuła zwraca wartość 0. Aby użyć tej formuły, zakresy komórek muszą mieć taki sam rozmiar i taki sam wymiar. Jeśli na przykład zakres MojeDane ma rozmiar 3 wiersze na 5 kolumn, zakres TwojeDane także musi mieć rozmiar 3 wiersze na 5 kolumn:

=SUMA(JEŻELI(MojeDane=TwojeDane;0;1))

Formuła tworzy nową tablicę o takim samym rozmiarze jak porównywane zakresy. Funkcja JEŻELI wypełnia tę tablicę wartościami 0 i 1 (0 dla komórek o różnych wartościach, a 1 dla komórek o identycznych wartościach). Następnie funkcja SUMA zwraca sumę wartości w tablicy.

Tę formułę można uprościć w następujący sposób:

=SUMA(1*(MojeDane<>TwojeDane))

Ta formuła działa podobnie do formuły zliczającej wartości błędu w zakresie, ponieważ PRAWDA*1=1, a FAŁSZ*1=0.

Ta formuła tablicowa zwraca numer wiersza, w którym znajduje się maksymalna wartość jednokolumnowego zakresu o nazwie Dane:

=MIN(JEŻELI(Dane=MAX(Dane);WIERSZ(Dane);""))

Funkcja JEŻELI tworzy nową tablicę odpowiadającą zakresowi Dane. Jeśli odpowiednia komórka zawiera maksymalną wartość w zakresie, tablica zawiera numer wiersza. W przeciwnym razie tablica zawiera pusty ciąg (""). Funkcja MIN używa nowej tablicy jako drugiego argumentu i zwraca najmniejszą wartość, która odpowiada numerowi wiersza zawierającego maksymalną wartość w zakresie Dane. Jeśli zakres Dane zawiera kilka identycznych maksymalnych wartości, formuła zwróci numer wiersza pierwszej z tych wartości.

Aby zwrócić rzeczywisty adres komórki zawierającej maksymalną wartość, należy użyć następującej formuły:

=ADRES(MIN(JEŻELI(Dane=MAX(Dane);WIERSZ(Dane);""));KOLUMNA(Dane))

Podobne przykłady znajdziesz w przykładowym skoroszycie w arkuszu Różnice między zestawami danych.

W tym ćwiczeniu pokazano, jak za pomocą wielokomórkowych i jednokomórkowych formuł tablicowych można obliczać zestaw danych dotyczących sprzedaży. W pierwszej części wielokomórkowa formuła posłuży do obliczenia zestawu sum częściowych. W drugiej części jednokomórkowa formuła zostanie użyta w celu obliczenia sumy końcowej.

  • Wielokomórkowa formuła tablicowa

Skopiuj całą pod tabelę i wklej ją w komórce A1 w pustym arkuszu.

Sprzedawca

Typ samochodu

Liczba sprzedanych towarów

Cena jednostkowa

Total Sales

Bochenek

Sedan

5

33000

Coupe

4

37000

Rybka

Sedan

6

24000

Coupe

8

21000

Karwat

Sedan

3

29000

Coupe

1

31000

Chmiela

Sedan

9

24000

Coupe

5

37000

Czupta

Sedan

6

33000

Coupe

8

31000

Formuła (suma końcowa)

Suma końcowa

'=SUMA(C2:C11*D2:D11)

=SUMA(C2:C11*D2:D11)

  1. Aby wyświetlić całkowitą sprzedaż coupe i sedan dla każdego sprzedawcy, zaznacz komórki E2:E11, wprowadź formułę =C2:C11*D2:D11, a następnie naciśnij klawisze Ctrl+Shift+Enter.

  2. Aby wyświetlić sumę całkowitą wszystkich sprzedaży, zaznacz komórkę F11, wprowadź formułę =SUMA(C2:C11*D2:D11), a następnie naciśnij klawisze Ctrl+Shift+Enter.

Po naciśnięciu klawiszy Ctrl+Shift+EnterExcel formułę nawiasami klamrowym ({ }) i wstawi wystąpienie formuły w każdej komórce zaznaczonego zakresu. Stanie się to bardzo szybko, a w kolumnie E będą widoczne łączne kwoty sprzedaży każdego typu samochodu dla każdego sprzedawcy. W każdej zaznaczonej komórce: E2, E3, E4 i tak dalej, będzie widać tę samą formułę: {=C2:C11*D2:D11}

Sumy w kolumnie E są wyliczane przez formułę tablicową

  • Tworzenie jednokomórkowej formuły tablicowej

W komórce D13 skoroszytu wpisz następującą formułę, a następnie naciśnij klawisze Ctrl+Shift+Enter:

=SUMA(C2:C11*D2:D11)

W tym przypadku funkcja Excel wartości w tablicy (zakres komórek od C2 do D11), a następnie używa funkcji SUMA w celu dodania sum. Wynikiem jest suma końcowa o wartości 1 590 000 zł. W tym przykładzie pokazano, jak duże możliwości oferują formuły tego typu. Na przykład załóżmy, że użytkownik ma 1000 wierszy danych. Można zsumować wszystkie lub część danych, tworząc formułę tablicową w jednej komórce, zamiast przeciągania formuły przez 1000 wierszy.

Należy również zauważyć, że formuła jednokomórkowa (w komórce D13) jest całkowicie niezależna od formuły wielokomórkowej (formuła w komórkach od E2 do E11). Jest to kolejna zaleta korzystania z formuł tablicowych — elastyczność. Możesz zmienić formułę w kolumnie E lub całkowicie usunąć tę kolumnę bez wpływu na formułę w kolumnie D13.

Formuły tablicowe mają także następujące zalety:

  • Spójność (Consistency)    Po kliknięciu dowolnej komórki od komórki E2 w dół będzie wyświetlana ta sama formuła. Taka spójność umożliwia zapewnienie większej dokładności.

  • Bezpieczeństwo    Nie można zastąpić składnika wielokomórkowej formuły tablicowej. Na przykład kliknij komórkę E3 i naciśnij klawisz Delete. Trzeba będzie albo zaznaczyć cały zakres komórek (od E2 do E11) i zmienić formułę dla całej tablicy, albo pozostawić tablicę niezmienioną. Ze względów bezpieczeństwa należy nacisnąć klawisze Ctrl+Shift+Enter, aby potwierdzić każdą zmianę w formule.

  • Mniejszy rozmiar plików    Często można użyć jednej formuły tablicowej zamiast kilku formuł pośrednich. Na przykład skoroszyt używa jednej formuły tablicowej do obliczania wyników w kolumnie E. Gdyby były używane formuły standardowe (takie jak =C2*D2, C3*D3, C4*D4...), do obliczenia tych samych wyników by było użytych 11 różnych formuł.

Na ogół w formułach tablicowych jest używana standardowa składnia formuł. Wszystkie zaczynają się od znaku równości (=) i większości wbudowanych funkcji Excel tablicowych. Kluczowa różnica polega na tym, że podczas korzystania z formuły tablicowej należy nacisnąć klawisze Ctrl+Shift+Enter, aby wprowadzić formułę. W takim przypadku formuła Excel nawiasami klamrową — jeśli ręcznie wpisze się nawiasy klamrowe, formuła zostanie przekonwertowana na ciąg tekstowy i nie będzie działać.

Funkcje tablicowe mogą być wydajnym sposobem tworzenia złożonych formuł. Formuła tablicowa =SUMA(C2:C11*D2:D11) działa tak samo, jak następujący zestaw formuł: =SUMA(C2*D2;C3*D3;C4*D4;C5*D5;C6*D6;C7*D7;C8*D8;C9*D9;C10*D10;C11*D11).

Ważne: Zawsze, gdy trzeba wprowadzić formułę tablicową, naciskaj klawisze Ctrl+Shift+Enter. Dotyczy to zarówno formuł jednokomórkowych, jak i wielokomórkowych.

Podczas pracy z formułami wielokomórkowymi należy zawsze pamiętać o następujących zasadach:

  • Przed wprowadzeniem formuły należy zaznaczyć zakres komórek, w których będą przechowywane wyniki. Zgodnie z tą regułą postępowano podczas tworzenia wielokomórkowej formuły tablicowej przy zaznaczaniu komórek od E2 do E11.

  • Nie można zmienić zawartości pojedynczej komórki w formule tablicowej. Aby to sprawdzić, zaznacz komórkę E3 w skoroszycie i naciśnij klawisz Delete. W programie Excel zostanie wyświetlony komunikat informujący o braku możliwości modyfikacji tablicy.

  • Można przenieść lub usunąć całą formułę tablicową, ale nie można przenieść ani usunąć jej części. Innymi słowy, aby zmniejszyć zakres formuły tablicowej, należy najpierw usunąć istniejącą formułę, a potem utworzyć nową.

  • Aby usunąć formułę tablicową, zaznacz cały zakres formuły (na przykład E2:E11),a następnie naciśnij klawisz Delete.

  • Do wielokomórkowej formuły tablicowej nie można wstawiać pustych komórek ani ich usuwać.

Czasami może być konieczne rozszerzenie formuły tablicowej. Zaznacz pierwszą komórkę w istniejącym zakresie tablicy i kontynuuj aż do momentu wybrania całego zakresu, do którego chcesz rozszerzyć formułę. Naciśnij klawisz F2, aby edytować formułę, a następnie naciśnij klawisze CTRL+SHIFT+ENTER, aby potwierdzić formułę po dostosowaniu zakresu formuły. Najważniejsze jest zaznaczenie całego zakresu, począwszy od lewej górnej komórki w tablicy. Lewa górna komórka to komórka, która jest edytowana.

Formuły tablicowe mogą być niezwykle przydatne, ale mogą mieć także wady:

  • Od czasu do czasu możesz zapomnieć o naciśnięciu klawiszy Ctrl+Shift+Enter. To może się zdarzyć nawet najbardziej doświadczonym użytkownikom programu Excel. Należy pamiętać, aby zawsze naciskać tę kombinację klawiszy podczas wprowadzania lub edytowania formuły tablicowej.

  • Inni użytkownicy skoroszytu mogą nie zrozumieć twoich formuł. W praktyce formuły tablicowe nie są zazwyczaj objaśnione w arkuszu. Dlatego jeśli inne osoby będą chcieć modyfikować skoroszyty użytkownika, należy unikać formuł tablicowych lub upewnić się, że te osoby znają formuły tablicowe i wiedzą, jak je modyfikować, jeśli zajmą się potrzebami.

  • W zależności od szybkości przetwarzania i ilości pamięci komputera duże formuły tablicowe mogą spowolnić wykonywanie obliczeń.

Stałe tablicowe to składniki formuł tablicowych. Aby utworzyć stałe tablicowe, należy wprowadzić listę elementów, a następnie ręcznie otoczyć listę nawiasami ({ }), tak jak pokazano poniżej:

={1;2;3;4;5}

Wiemy już, że podczas tworzenia formuł tablicowych trzeba naciskać klawisze Ctrl+Shift+Enter. Stałe tablicowe są składnikami formuł tablicowych, więc podczas wpisywania należy ręcznie otoczyć je nawiasami. Następnie należy użyć klawiszy Ctrl+Shift+Enter, aby wprowadzić całą formułę.

Rozdzielenie elementów średnikami spowoduje utworzenie tablicy poziomej (wiersza). Rozdzielenie elementów ukośnikami spowoduje utworzenie tablicy pionowej (kolumny). Aby utworzyć tablicę dwuwymiarową, należy rozdzielić elementy znajdujące się w każdym wierszu średnikami, a wiersze — ukośnikami.

Oto tablica w jednym wierszu: {1;2;3;4}. Oto przykład tablicy składającej się z jednej kolumny: {1\2\3\4}. Oto przykład tablicy składającej się z dwóch wierszy i czterech kolumn: {1;2;3;4\5;6;7;8}. W tablicy dwóch wierszy pierwszy wiersz to 1, 2, 3 i 4, a drugi to 5, 6, 7 i 8. Pojedynczy ukośnik odwrotny rozdziela wiersze między liczbami 4 i 5.

Podobnie jak w przypadku formuł tablicowych, stałych tablicowych można używać z większością wbudowanych funkcji programu Excel. W kolejnych sekcjach wyjaśniono, jak tworzyć poszczególne rodzaje stałych i jak używać tych stałych z funkcjami programu Excel.

Wykonując poniższe procedury, można przećwiczyć tworzenie stałych poziomych, pionowych i dwuwymiarowych.

Tworzenie stałej poziomej

  1. W pustym arkuszu zaznacz komórki od A1 do E1.

  2. Na pasku formuły wprowadź następującą formułę, a następnie naciśnij klawisze Ctrl+Shift+Enter:

    ={1;2;3;4;5}

    W takim przypadku należy wpisać nawias klamrowy otwierający i zamykający ({ }), a Excel dodać drugi zestaw.

    Zostanie wyświetlony poniższy wynik.

    Pozioma stała tablicowa w formule

Tworzenie stałej pionowej

  1. W skoroszycie zaznacz pięć komórek w kolumnie.

  2. Na pasku formuły wprowadź następującą formułę, a następnie naciśnij klawisze Ctrl+Shift+Enter:

    ={1\2\3\4\5}

    Zostanie wyświetlony poniższy wynik.

    Pionowa stała tablicowa w formule tablicowej

Tworzenie stałej dwuwymiarowej

  1. W skoroszycie zaznacz blok komórek o szerokości czterech kolumn i wysokości trzech wierszy.

  2. Na pasku formuły wprowadź następującą formułę, a następnie naciśnij klawisze Ctrl+Shift+Enter:

    ={1;2;3;4\5;6;7;8\9;10;11;12}

    Zostanie wyświetlony następujący wynik:

    Dwuwymiarowa stała tablicowa w formule tablicowej

Używanie stałych w formułach

Oto prosty przykład zawierający stałe:

  1. W przykładowym skoroszycie utwórz nowy arkusz.

  2. W komórce A1 wprowadź liczbę 3, a następnie 4 w B1, 5 w C1, 6 w D1 i 7 w E1.

  3. W komórce A3 wpisz następującą formułę, a następnie naciśnij klawisze Ctrl+Shift+Enter:

    =SUMA(A1:E1*{1;2;3;4;5})

    Należy zauważyć, że program Excel otoczył stałą kolejnymi nawiasami klamrowymi, ponieważ wprowadzono ją w formule tablicowej.

    Formuła tablicowa ze stałą tablicową

    W komórce A3 zobaczysz wartość 85.

W następnej sekcji wyjaśniono, jak działa ta formuła.

Użyta przed chwilą formuła składa się z kilku części.

Składnia formuły tablicowej ze stałą tablicową

1. Funkcja

2. Przechowywana tablica

3. Operator

4. Stała tablicowa

Ostatni element w nawiasach to stała tablicowa: {1;2;3;4;5}. Należy pamiętać, że program Excel nie otacza automatycznie stałych tablicowych nawiasami klamrowymi i należy je wprowadzić ręcznie. Należy również pamiętać, że po dodaniu stałej do formuły tablicowej należy ją wprowadzić, naciskając klawisze Ctrl+Shift+Enter.

Ponieważ w programie Excel najpierw są wykonywane operacje na wyrażeniach w nawiasach, następnymi dwoma elementami używanymi w obliczeniach są wartości przechowywane w skoroszycie (A1:E1) oraz operator. W tym punkcie formuła mnoży wartości w przechowywanej tablicy przez odpowiadające im wartości w stałej. Jest to odpowiednik formuły:

=SUMA(A1*1;B1*2;C1*3;D1*4;E1*5)

Na końcu funkcja SUMA dodaje wartości i suma 85 pojawia się w komórce A3.

Aby uniknąć używania przechowywanej tablicy i wykonać całą operację wyłącznie w pamięci, należy zastąpić przechowywaną tablicę inną stałą tablicową:

=SUMA({3;4;5;6;7}*{1;2;3;4;5})

Aby to wypróbować, skopiuj funkcję, zaznacz pustą komórkę w skoroszycie, wklej formułę na pasku formuły, a następnie naciśnij klawisze Ctrl+Shift+Enter. Wynik będzie taki sam jak przy wcześniejszym ćwiczeniu z formułą tablicową:

=SUMA(A1:E1*{1;2;3;4;5})

Stałe tablicowe mogą zawierać liczby, tekst, wartości logiczne (takie jak PRAWDA i FAŁSZ) i wartości błędów (na przykład #N/D). Można używać liczb w formatach całkowitym, dziesiętnym i naukowym. Dołączenie tekstu wymaga ujęcia go w cudzysłowy (").

Stałe tablicowe nie mogą zawierać dodatkowych tablic, formuł ani funkcji. Innymi słowy mogą zawierać tylko tekst lub liczby oddzielone średnikami i odwrotnymi ukośnikami. Po wprowadzeniu formuły, takiej jak {1;2;A1:D4} lub {1;2;SUM(Q2:Z8)}, w programie Excel zostanie wyświetlony komunikat ostrzegawczy. Dodatkowo wartości liczbowe nie mogą zawierać znaków procenta, dolara, średników i nawiasów.

Jednym z najlepszych sposobów używania stałych tablicowych jest ich nazwanie. Stałe nazwane są znacznie prostsze w użyciu i za ich pomocą można ukryć część złożonych formuł tablicowych. Aby nazwać stałą tablicową i użyć jej w formule, wykonaj następujące czynności:

  1. Na karcie Formuły w grupie Nazwy zdefiniowane kliknij pozycję Definiuj nazwę.
    Zostanie wyświetlone okno dialogowe Definiowanie nazwy.

  2. W polu Nazwa wpisz ciąg Kwartał1.

  3. W polu Odwołuje się do wprowadź następującą stałą (pamiętaj, aby ręcznie wpisać nawiasy klamrowe):

    ={"Styczeń";"Luty";"Marzec"}

    Zawartość okna dialogowego wygląda teraz następująco:

    Okno dialogowe Edytowanie nazwy z formułą

  4. Kliknij przycisk OK i zaznacz wiersz składający z trzech pustych komórek.

  5. Wpisz następującą formułę, a następnie naciśnij klawisze Ctrl+Shift+Enter.

    =Kwartał1

    Zostanie wyświetlony poniższy wynik.

    Nazwana tablica wprowadzona jako formuła

Używając stałej nazwanej jako formuły tablicowej, należy pamiętać o wprowadzeniu znaku równości. Bez znaku równości program Excel zinterpretuje tablicę jako ciąg tekstowy i formuła nie zadziała w oczekiwany sposób. Należy też pamiętać, że można używać kombinacji tekstu i liczb.

Gdy stałe tablicowe nie działają, należy sprawdzić, czy nie występują następujące problemy:

  • Być może niektóre elementy nie są rozdzielone odpowiednim znakiem. Jeśli pominiesz średnik lub umieścisz go w nieodpowiednim miejscu, stała tablicowa może nie zostać utworzona poprawnie lub może zostać wyświetlony komunikat ostrzegawczy.

  • Być może zaznaczono zakres komórek, który nie jest zgodny z liczbę elementów w stałej. Na przykład jeśli zaznaczono sześć komórek w kolumnie do użycia z pięciokomórkową stałą, w pustej komórce zostanie wyświetlona wartość błędu #N/D!. I odwrotnie, jeśli użytkownik zaznaczy za mało komórek, program Excel pominie wartości, które nie mają odpowiadających im komórek.

W poniższych przykładach pokazano kilka sposobów użycia stałych tablicowych w formułach tablicowych. W niektórych przykładach funkcji TRANSPONUJ używa się do konwertowania wierszy na kolumny i odwrotnie.

Mnożenie każdego elementu w tablicy

  1. Utwórz nowy arkusz, a następnie zaznacz blok pustych komórek o szerokości czterech kolumn i wysokości trzech wierszy.

  2. Wpisz następującą formułę, a następnie naciśnij klawisze Ctrl+Shift+Enter:

    ={1;2;3;4\5;6;7;8\9;10;11;12}*2

Obliczanie kwadratów elementów w tablicy

  1. Zaznacz blok pustych komórek o szerokości czterech kolumn i wysokości trzech wierszy.

  2. Wpisz następującą formułę tablicową, a następnie naciśnij klawisze Ctrl+Shift+Enter:

    ={1;2;3;4\5;6;7;8\9;10;11;12}*{1;2;3;4\5;6;7;8\9;10;11;12}

    Możesz także wprowadzić następującą formułę tablicową, w której użyto znaku daszka (^) jako operatora:

    ={1;2;3;4\5;6;7;8\9;10;11;12}^2

Transponowanie jednowymiarowego wiersza

  1. Zaznacz pięć pustych komórek w kolumnie.

  2. Wpisz następującą formułę, a następnie naciśnij klawisze Ctrl+Shift+Enter:

    =TRANSPONUJ({1;2;3;4;5})

    Mimo że wprowadzono poziomą stałą tablicową, funkcja TRANSPONUJ konwertuje stałą tablicową na kolumnę.

Transponowanie jednowymiarowej kolumny

  1. Zaznacz pięć pustych komórek w wierszu.

  2. Wprowadź następującą formułę, a następnie naciśnij klawisze Ctrl+Shift+Enter:

    =TRANSPONUJ({1\2\3\4\5})

Mimo że wprowadzono pionową stałą tablicową, funkcja TRANSPONUJ konwertuje stałą na wiersz.

Transponowanie stałej dwuwymiarowej

  1. Zaznacz blok komórek o szerokości trzech kolumn i wysokości czterech wierszy.

  2. Wprowadź następującą stałą, a następnie naciśnij klawisze Ctrl+Shift+Enter:

    =TRANSPONUJ({1;2;3;4\5;6;7;8\9;10;11;12})

    Funkcja TRANSPONUJ konwertuje każdy wiersz na serię kolumn.

W tej sekcji znajdują się przykłady podstawowych formuł tablicowych.

Tworzenie tablic i stałych tablicowych na podstawie istniejących wartości

W poniższym przykładzie wyjaśniono, w jaki sposób należy używać formuł tablicowych w celu tworzenia łączy między zakresami komórek w różnych arkuszach. Pokazano także, jak utworzyć stałą tablicową na podstawie takiego samego zestawu wartości.

Tworzenie tablicy na podstawie istniejących wartości

  1. W arkuszu programu Excel zaznacz komórki C8:E10 i wprowadź tę formułę:

    ={10;20;30\40;50;60\70;80;90}

    Pamiętaj, aby wpisać { (otwierający nawias klamrowy) przed liczbą 10 i } (zamykający nawias klamrowy) po liczbie 90, ponieważ tworzysz tablicę liczb.

  2. Naciśnij klawisze Ctrl+Shift+Enter,co wprowadzi tę tablicę liczb w zakresie komórek C8:E10 za pomocą formuły tablicowej. W arkuszu komórki od C8 do E10 powinny wyglądać tak:

    10

    20

    30

    40

    50

    60

    70

    80

    90

  3. Zaznacz zakres komórek od C1 do E3.

  4. Wprowadź następującą formułę na pasku formuły, a następnie naciśnij klawisze Ctrl+Shift+Enter:

    =C8:E10

    W komórkach od C1 do E3 pojawi się tablica komórek 3x3 z wartościami widocznymi w komórkach od C8 do E10.

Tworzenie stałej tablicowej na podstawie istniejących wartości

  1. Mając zaznaczone komórki C1:C3, naciśnij klawisz F2, aby przełączyć się do trybu edycji. 

  2. Naciśnij klawisz F9, aby przekonwertować odwołania do komórek na wartości. Program Excel przekonwertuje wartości na stałą tablicową. Teraz formuła powinna mieć formułę ={10,20,30;40,50,60;70,80,90}.

  3. Naciśnij klawisze Ctrl+Shift+Enter, aby wprowadzić stałą tablicową jako formułę tablicową.

Zliczanie znaków w zakresie komórek

W poniższym przykładzie pokazano, jak można ustalić liczbę znaków (włącznie ze spacjami) w zakresie komórek.

  1. Skopiuj tę całą tabelę i wklej ją w komórce A1 arkusza.

    Dane

    To jest

    wiele komórek, które

    zostały połączone,

    aby utworzyć

    jedno zdanie.

    Całkowita liczba znaków w zakresie komórek A2:A6

    =SUMA(DŁ(A2:A6))

    Zawartość najdłuższej komórki (A3)

    =INDEKS(A2:A6;PODAJ.POZYCJĘ(MAX(DŁ(A2:A6));DŁ(A2:A6);0);1)

  2. Zaznacz komórkę A8, a następnie naciśnij klawisze Ctrl+Shift+Enter, aby wyświetlić całkowitą liczbę znaków w komórkach A2:A6 (66).

  3. Zaznacz komórkę A10, a następnie naciśnij klawisze Ctrl+Shift+Enter, aby wyświetlić zawartość najdłuższej komórki z komórek A2:A6 (komórka A3).

Następująca formuła jest używana w komórce A8 i zlicza całkowitą liczbę znaków (66) w komórkach od A2 do A6.

=SUMA(DŁ(A2:A6))

W tym przypadku funkcja zwraca długość każdego ciągu tekstowego z każdej komórki w zakresie. Następnie funkcja SUMA dodaje te wartości i wyświetla wynik (66).

Znajdowanie n najmniejszych wartości w zakresie

W tym przykładzie pokazano, jak można znaleźć trzy najmniejsze wartości w zakresie komórek.

  1. Wprowadź niektóre liczby losowe w komórkach A1:A11.

  2. Zaznacz komórki od C1 do C3. W tym zestawie komórek będą przechowywane wyniki zwracane przez formułę tablicową.

  3. Wprowadź następującą formułę, a następnie naciśnij klawisze Ctrl+Shift+Enter:

    =MAŁA(A1:A11;{1;2;3})

Ta formuła używa stałej tablicowej do trzykrotnego obliczania funkcji MAŁA i zwraca najmniejszy (1), drugi najmniejszy (2) i trzeci najmniejszy (3) element tablicy znajdującej się w komórkach A1:A10 Aby znaleźć więcej wartości, należy dodać do stałej więcej argumentów. W tej formule można także używać dodatkowych funkcji, takich jak SUMA lub ŚREDNIA. Na przykład:

=SUMA(MAŁE(A1:A10;{1;2;3})

=ŚREDNIA(MAŁA(A1:A10;{1;2;3})

Znajdowanie n największych wartości w zakresie

Aby znaleźć największe wartości w zakresie, należy zastąpić funkcję MIN.K funkcją MAX.K. Ponadto w poniższym przykładzie użyto funkcji WIERSZ i ADR.POŚR.

  1. Zaznacz komórki od D1 do D3.

  2. Na pasku formuły wprowadź tę formułę, a następnie naciśnij klawisze Ctrl+Shift+Enter:

    =DUŻE(A1:A10;WIERSZ(POŚR("1:3")))

W tym miejscu przyda się nieco informacji o funkcjach WIERSZ i ADR.POŚR. Za pomocą funkcji WIERSZ można utworzyć tablicę zawierającą kolejne liczby całkowite. Na przykład zaznacz 10 pustych komórek w skoroszycie ćwiczeniowym, wprowadź tę formułę tablicową, a następnie naciśnij klawisze Ctrl+Shift+Enter:

=WIERSZ(1:10)

Ta formuła tworzy kolumnę zawierająca 10 kolejnych liczb całkowitych. Aby zobaczyć potencjalny problem, wstaw wiersz powyżej zakresu zawierającego formułę tablicową (czyli powyżej wiersza 1). Program Excel dostosuje odwołania do wierszy, a formuła wygeneruje liczby całkowite z zakresu od 2 do 11. Aby rozwiązać ten problem, należy dodać do formuły funkcję ADR.POŚR:

=WIERSZ(ADR.POŚR("1:10"))

Funkcja ADR.POŚR używa ciągów tekstowych jako argumentów (dlatego zakres 1:10 jest umieszczony w cudzysłowie). Program Excel nie dostosowuje wartości tekstowych, gdy użytkownik wstawia wiersze lub w inny sposób przenosi formułę tablicową. Dzięki temu funkcja WIERSZ zawsze generuje odpowiednią tablicę liczb całkowitych.

Przyjrzyjmy się użytej wcześniej formule — =DUŻE(A5:A14;WIERSZ(POŚR("1:3"))) — rozpoczynając od nawiasów wewnętrznych i wychodząc na zewnątrz: funkcja INDIRECT zwraca zestaw wartości tekstowych, w tym przypadku wartości od 1 do 3. Funkcja WIERSZ z kolei generuje trzykomórkową tablicę kolumnową. Funkcja LARGE używa wartości z zakresu komórek A5:A14 i jest szacowana trzy razy, raz dla każdego odwołania zwróconego przez funkcję WIERSZ. Wartości 3200, 2700 i 2000 są zwracane do trzykomórkowej tablicy kolumnowej. Aby znaleźć więcej wartości, należy dodać do funkcji INDIRECT większy zakres komórek.

Jak w poprzednich przykładach, tej formuły można używać z innymi funkcjami, takimi jak SUMA i ŚREDNIA.

Znajdowanie najdłuższego ciągu tekstowego w zakresie komórek

Wróć do wcześniejszego przykładu ciągu tekstowego, wprowadź następującą formułę w pustej komórce i naciśnij klawisze Ctrl+Shift+Enter:

=INDEKS(A2:A6;PODAJ.POZYCJĘ(MAX(DŁ(A2:A6));DŁ(A2:A6);0);1)

Zostanie wyświetlony tekst "wiele komórek, które".

Poniżej przedstawiono analizę zasady działania tej formuły, począwszy od elementów wewnętrznych i przechodząc do elementów zewnętrznych. Funkcja zwraca długość każdego z elementów w zakresie komórek A2:A6. Funkcja MAX oblicza największą spośród tych pozycji wartość, która odpowiada najdłuższemu ciągowi tekstowemu, który znajduje się w komórce A3.

Tutaj sprawy nieco się komplikują. Funkcja PODAJ.POZYCJĘ oblicza przesunięcie (pozycję względną) komórki zawierającej najdłuższy ciąg tekstowy. W tym celu ta funkcja wymaga trzech argumentów: szukana wartość, przeszukiwana tablica i typ porównania. Funkcja PODAJ.POZYCJĘ wyszukuje w przeszukiwanej tablicy określoną szukaną wartość. W tym przypadku szukaną wartością jest najdłuższy ciąg tekstowy:

(MAX(DŁ(A2:A6))

i taki ciąg znajduje się w tej tablicy:

DŁ(A2:A6)

Argument typ porównania to 0. Argument typ porównania może mieć wartość 1, 0 lub -1. Po określeniu wartości 1, funkcja PODAJ.POZYCJĘ zwraca największą wartość, która jest mniejsza lub równa szukanej wartości. Po określeniu wartości 0 funkcja PODAJ.POZYCJĘ zwraca pierwszą wartość równą szukanej wartości. Po określeniu wartości -1, funkcja PODAJ.POZYCJĘ znajduje najmniejszą wartość, która jest większa lub równa szukanej wartości. Jeśli użytkownik pominie typ porównania, program Excel użyje wartości 1.

I wreszcie funkcja INDEKS pobiera te argumenty: tablicę oraz numer wiersza i numer kolumny w tej tablicy. Zakres komórek A2:A6 dostarcza tablicę, funkcja DOPASUJ dostarcza adres komórki, a ostatni argument(1)określa, że wartość pochodzi z pierwszej kolumny w tablicy.

W tej sekcji znajdują się przykłady zaawansowanych formuł tablicowych.

Suma zakresu zawierającego wartości błędu

Funkcja SUMA w programie Excel nie działa, gdy użytkownik próbuje podsumować zakres zawierający wartość błędu, taką jak #N/D!. W tym przykładzie pokazano, w jaki sposób można podsumować wartości w zakresie nazwanym Dane, który zawiera błędy.

=SUMA(JEŻELI(CZY.BŁĄD(Dane);"";Dane))

Formuła tworzy nową tablicę zawierającą oryginalne wartości minus wszelkie wartości błędów. Zaczynając od funkcji wewnętrznych i wychodząc na zewnątrz, funkcja CZY.BŁĄD wyszukuje błędy w zakresie komórek (Danych). Funkcja JEŻELI zwraca określoną wartość, jeśli określony warunek ma wartość PRAWDA, lub inną wartość, jeśli ma wartość FAŁSZ. W tym przypadku zwraca puste ciągi ("") dla wszystkich wartości błędów, ponieważ są one zwracane jako PRAWDA, a pozostałe wartości z zakresu(Dane)są zwracane ze względu na wartość FAŁSZ, co oznacza, że nie zawierają one wartości błędów. Następnie funkcja SUMA oblicza sumę filtrowanej tablicy.

Ustalanie liczby wartości błędu w zakresie

Formuła w tym przykładzie jest podoba do poprzedniej formuły, ale zwraca liczbę wartości błędu w zakresie nazwanym Dane, zamiast je odfiltrowywać:

=SUMA(JEŻELI(CZY.BŁĄD(Dane);1;0))

Ta formuła tworzy tablicę zawierającą wartość 1 dla komórek zawierających błędy i wartość 0 dla komórek niezawierających błędów. Można uprościć tę formułę i uzyskać taki sam wynik, usuwając trzeci argument funkcji JEŻELI:

=SUMA(JEŻELI(CZY.BŁĄD(Dane);1))

Jeśli nie zostanie określony argument, funkcja JEŻELI zwróci wartość FAŁSZ, gdy komórka nie będzie zawierać wartości błędu. Tę formułę można jeszcze bardziej uprościć:

=SUMA(JEŻELI(CZY.BŁĄD(Dane)*1))

Ta wersja działa, ponieważ PRAWDA*1=1, a FAŁSZ*1=0.

Sumowanie wartości na podstawie warunków

Może zaistnieć konieczność zsumowania wartości na podstawie warunków. Na przykład ta formuła tablicowa sumuje tylko dodatnie liczby całkowite w zakresie nazwanym Sprzedaż:

=SUMA(JEŻELI(Sprzedaż>0;Sprzedaż))

Funkcja JEŻELI tworzy tablicę wartości dodatnich i wartości FAŁSZ. Funkcja SUMA ignoruje wartości FAŁSZ, ponieważ 0+0=0. Zakres komórek używany w tej formule może składać się z dowolnej liczby wierszy i kolumn.

Można także sumować wartości, które spełniają kilka warunków. Na przykład ta formuła używa w obliczeniach wartości większych niż 0 oraz mniejszych lub równych 5:

=SUMA((Sprzedaż>0)*(Sprzedaż<=5)*(Sprzedaż))

Należy pamiętać, że ta formuła zwróci błąd, jeśli w zakresie będzie znajdować się co najmniej jedna komórka, której wartość nie jest wartością liczbową.

Można też tworzyć formuły tablicowe używające warunków typu LUB. Na przykład można sumować wartości, które są mniejsze niż 5 i większe niż 15:

=SUMA(JEŻELI((Sprzedaż<5)+(Sprzedaż>15);Sprzedaż))

Funkcja JEŻELI znajdzie wszystkie wartości mniejsze niż 5 i większe niż 15, a następnie przekaże te wartości do funkcji SUMA.

Funkcji ORAZ i LUB nie można używać w formułach tablicowych bezpośrednio, ponieważ zwracają one pojedynczy wynik (PRAWDA lub FAŁSZ), a funkcje tablicowe wymagają tablic wyników. Można obejść ten problem, korzystając z logiki pokazanej w poprzedniej formule. Innymi słowy można wykonywać operacje matematyczne, takie jak dodawanie lub mnożenie, na wartościach spełniających warunek LUB bądź ORAZ.

Obliczanie średniej z wyłączeniem zer

W tym przykładzie pokazano, jak można usunąć zera z zakresu w sytuacji, gdy trzeba obliczyć średnią z wartości w zakresie. W formule jest używany zakres danych o nazwie Sprzedaż:

=ŚREDNIA(JEŻELI(Sprzedaż<>0;Sprzedaż))

Funkcja JEŻELI tworzy tablicę wartości, które nie są równe 0, a następnie przekazuje te wartości do funkcji ŚREDNIA.

Ustalanie liczby różnic między dwoma zakresami komórek

Ta formuła tablicowa porównuje wartości w dwóch zakresach komórek o nazwach MojeDane i TwojeDane i zwraca liczbę różnic między tymi zakresami. Jeśli zawartość obu zakresów jest identyczna, formuła zwraca wartość 0. Aby używać tej formuły, należy mieć zakresy komórek o takim samym rozmiarze i takiej samej liczbie wymiarów (na przykład jeżeli zakres MojeDane ma rozmiar 3 wiersze na 5 kolumn, zakres TwojeDane także musi mieć rozmiar 3 wiersze na 5 kolumn):

=SUMA(JEŻELI(MojeDane=TwojeDane;0;1))

Formuła tworzy nową tablicę o takim samym rozmiarze jak porównywane zakresy. Funkcja JEŻELI wypełnia tę tablicę wartościami 0 i 1 (0 dla komórek o różnych wartościach, a 1 dla komórek o identycznych wartościach). Następnie funkcja SUMA zwraca sumę wartości w tablicy.

Tę formułę można uprościć w następujący sposób:

=SUMA(1*(MojeDane<>TwojeDane))

Ta formuła działa podobnie do formuły zliczającej wartości błędu w zakresie, ponieważ PRAWDA*1=1, a FAŁSZ*1=0.

Znajdowanie lokalizacji maksymalnej wartości w zakresie

Ta formuła tablicowa zwraca numer wiersza, w którym znajduje się maksymalna wartość jednokolumnowego zakresu o nazwie Dane:

=MIN(JEŻELI(Dane=MAX(Dane);WIERSZ(Dane);""))

Funkcja JEŻELI tworzy nową tablicę odpowiadającą zakresowi Dane. Jeśli odpowiednia komórka zawiera maksymalną wartość w zakresie, tablica zawiera numer wiersza. W przeciwnym razie tablica zawiera pusty ciąg (""). Funkcja MIN używa nowej tablicy jako drugiego argumentu i zwraca najmniejszą wartość, która odpowiada numerowi wiersza zawierającego maksymalną wartość w zakresie Dane. Jeśli zakres Dane zawiera kilka identycznych maksymalnych wartości, formuła zwróci numer wiersza pierwszej z tych wartości.

Aby zwrócić rzeczywisty adres komórki zawierającej maksymalną wartość, należy użyć następującej formuły:

=ADRES(MIN(JEŻELI(Dane=MAX(Dane);WIERSZ(Dane);""));KOLUMNA(Dane))

Potwierdzenie

Część tego artykułu została oparta na serii kolumn programu Excel Power User napisanych przez Colina Wilcoxa i zaadaptowana z rozdziałów 14 i 15 książki Excel 2002 Formulas ( Formuły 2002) autorstwa Johna Walkenbacha, byłego Excel MVP.

Potrzebujesz dodatkowej pomocy?

Zawsze możesz zadać pytanie ekspertowi w społeczności technicznej programu Excel, uzyskać pomoc techniczną w społeczności witryny Answers bądź zasugerować nową funkcję lub ulepszenie w witrynie UserVoice dotyczącej programu Excel.

Zobacz też

Tablice dynamiczne i zachowanie tablicy rozlanej

Dynamiczne formuły tablicowe a starsze formuły tablicowe CSE

Funkcja FILTRUJ

Funkcja LOSOWA.TABLICA

Funkcja SEKWENCJA

Funkcja SORTUJ

Funkcja SORTUJ.WEDŁUG

Funkcja UNIKATOWE

Błędy #SPILL! w programie Excel

Operator przecięcia pośredniego: @

Omówienie formuł

Potrzebna dalsza pomoc?

Rozwijaj umiejętności związane z pakietem Office
Poznaj szkolenia
Uzyskuj nowe funkcje w pierwszej kolejności
Dołącz do niejawnych testerów pakietu Office

Czy te informacje były pomocne?

Dziękujemy za opinię!

Dziękujemy za opinię! Wygląda na to, że połączenie Cię z jednym z naszych agentów pomocy technicznej pakietu Office może być pomocne.

×