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

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

Aby zostać zaawansowanym użytkownikiem programu Excel, musisz dowiedzieć się, jak używać formuł tablicowych, które są w stanie wykonywać obliczenia niedostępne dla formuł nietablicowych. Następujący artykuł jest oparty na serii artykułów Excel Power User napisanych przez Colina Wilcoxa i zaadaptowanych z rozdziałów 14 i 15 książki Excel 2002 Formulas (Formuły programu Excel 2002) autorstwa Johna Walkenbacha, specjalisty Excel MVP.

Informacje o formułach tablicowych

Formuły tablicowe są często nazywane formułami CSE (Ctrl+Shift+Enter), ponieważ do ich wpisania konieczne jest naciśnięcie klawiszy Ctrl+Shift+Enter, a nie samego klawisza Enter.

Dlaczego warto używać formuł tablicowych?

Użytkownicy mający doświadczenie w korzystaniu z formuł w programie Excel wiedzą, że za ich pomocą można wykonywać całkiem skomplikowane operacje. Można na przykład obliczyć całkowity koszt pożyczki dla dowolnie określonej liczby lat spłaty. Za pomocą formuł tablicowych można wykonywać złożone zadania, takie jak:

  • Zliczanie znaków znajdujących się w zakresie komórek.

  • Sumowanie tylko liczb spełniających określone kryteria, takich jak najmniejsze wartości w zakresach lub liczby należące do przedziału określonego przez górną i dolną granicę.

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

Krótkie wprowadzenie do tablic i formuł tablicowych

Formuła tablicowa to formuła, która może wykonywać wiele obliczeń na jednym lub większej liczbie elementów w tablicy. Tablica to wiersz wartości, kolumna wartości lub połączenie wierszy i kolumn wartości. Formuły tablicowe mogą zwracać wiele wyników lub jeden wynik. Można na przykład umieścić formułę tablicową w zakresie komórek i używać jej do obliczania sum częściowych kolumn lub wierszy. Można także utworzyć formułę tablicową w pojedynczej komórce i obliczać pojedynczą wartość. Formuła tablicowa obejmująca wiele komórek nosi nazwę formuły wielokomórkowej, a formuła tablicowa znajdująca się w jednej komórce — formuły jednokomórkowej.

W przykładach w następnej sekcji pokazano, w jaki sposób tworzy się wielo- i jednokomórkowe formuły tablicowe.

Przykład zastosowania formuł tablicowych

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

Tutaj znajduje się skoroszyt osadzony w przeglądarce. Zawiera on przykładowe dane, ale w arkuszach osadzonych nie można tworzyć i edytować formuł tablicowych — do tego jest potrzebny program Excel. Można tutaj przejrzeć wyniki działania formuł tablicowych w arkuszu osadzonym, dodać tekst opisujący działanie tych formuł, jednak pełną ich funkcjonalność można sprawdzić otwierając ten skoroszyt w programie Excel.

Tworzenie wielokomórkowej formuły tablicowej

  1. Skopiuj całą poniższą tabelę i wklej ją w komórce A1 w pustym arkuszu programu Excel.

    Sprzedawca

    Typ
    samochodu

    Liczba
    sprzedaży

    Cena
    jednostkowa

    Sprzedaż
    razem

    Bochenek

    Sedan

    5

    33000

    Coupe

    4

    37000

    Rybka

    Sedan

    6

    24000

    Coupe

    10

    21000

    Karwat

    Sedan

    3

    29000

    Coupe

    1

    31000

    Chmiela

    Sedan

    9

    24000

    Coupe

    5

    37000

    Czupta

    Sedan

    6

    33000

    Coupe

    10

    31000

    Formuła (suma końcowa)

    Suma końcowa

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

    =SUMA(C2:C11*D2:D11)

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

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

Skoroszyt ten można pobrać klikając zielony przycisk programu Excel na czarnym pasku znajdującym się na dole skoroszytu. Następnie można otworzyć ten plik w programie Excel, zaznaczyć komórki zawierające formuły tablicowe i nacisnąć klawisze Ctrl+Shift+Enter, aby je uruchomić.

Jeżeli równolegle pracujesz w programie Excel, upewnij się, że aktywny jest Arkusz1, i wybierz komórki E2:E11. Naciśnij klawisz F2, a następnie wprowadź formułę =C2:C11*D2:D11 w bieżącej komórce (E2). Naciśnięcie klawisza Enter spowoduje wprowadzenie tej formuły tylko do komórki E2 i wyświetlenie liczby 165000. Zamiast naciskania samego klawisza Enter naciśnij klawisze Ctrl+Shift+Enter. Teraz wyniki pojawią się w komórkach E2:E11. Warto zauważyć, że na pasku formuły pojawi się ona w postaci {=C2:C11*D2:D11}. Oznacza to, że jest to formuła tablicowa, co widać w tabeli poniżej.

Po naciśnięciu klawiszy Ctrl+Shift+Enter program Excel otoczy formułę nawiasami klamrowymi ({ }) i umieści formułę w każdej komórce wybranego 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 F10 skoroszytu wprowadź następującą formułę i naciśnij klawisze Ctrl+Shift+Enter:

=SUMA(C2:C11*D2:D11)

W tym przypadku program Excel mnoży wartości w tablicy (zakres komórek od C2 do D11), a następnie używa funkcji SUMA, aby dodać sumy do siebie. 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 też zauważyć, że formuła jednokomórkowa (w komórce G11) 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żna zmienić formułę w kolumnie E lub całkowicie usunąć tę kolumnę, bez wpływu na formułę w komórce G11.

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

  • Spójność    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. Aby się o tym przekonać, można kliknąć komórkę E3 i nacisnąć 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ą. Dodatkowym zabezpieczeniem jest konieczność naciśnięcia klawiszy Ctrl+Shift+Enter w celu potwierdzenia zmiany formuły.

  • Mniejszy rozmiar plików    Często można użyć jednej formuły tablicowej zamiast kilku formuł pośrednich. Na przykład w tym skoroszycie do obliczania wyników w kolumnie E używana jest jedna formuła tablicowa. Gdyby użyto standardowych formuł (takich jak =C2*D2, C3*D3, C4*D4), to aby obliczyć te same wyniki, należałoby użyć 11 różnych formuł.

Składnia formuły tablicowej

Ogólnie rzecz biorąc, w formułach tablicowych jest używana standardowa składnia formuł. Wszystkie formuły rozpoczynają się od znaku równości. W formułach tablicowych można używać większości wbudowanych funkcji programu Excel. Podstawową różnicą podczas używania formuły tablicowej jest konieczność naciśnięcia klawiszy Ctrl+Shift+Enter w celu wprowadzenia formuły. Po wykonaniu tej czynności program Excel otacza formułę tablicową nawiasami klamrowymi. Ręczne wpisanie nawiasów klamrowych spowoduje przekonwertowanie formuły na ciąg tekstowy, przez co formuła nie będzie działać.

Formuły tablicowe to bardzo skuteczna metoda budowana 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).

Wprowadzanie i zmienianie formuł tablicowych

Ważne    Podczas wprowadzania lub edytowania formuły tablicowej należy zawsze nacisnąć 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ą, należy zaznaczyć formułę (na przykład =C2:C11*D2:D11), nacisnąć klawisz Delete, a następnie nacisnąć klawisze Ctrl+Shift+Enter.

  • Do wielokomórkowej formuły tablicowej nie można dodawać pustych komórek; nie można także usuwać z niej komórek.

Rozszerzanie formuły tablicowej

Czasami może być konieczne rozszerzenie formuły tablicowej. Ten proces nie jest skomplikowany, ale należy postępować zgodnie z powyższymi wskazówkami.

W tym arkuszu dodaliśmy kilka nowych wierszy z informacjami o sprzedaży, w wierszach od 12 do 17. Teraz chcemy zaktualizować formuły tablicowe, aby obejmowały te dodatkowe wiersze.

Należy wykonać to w programie komputerowym Excel (po pobraniu skoroszytu na komputer).

Rozszerzanie formuły tablicowej

  1. Skopiuj tę całą tabelę do komórki A1 w arkuszu programu Excel.

    Sprzedawca

    Typ
    samochodu

    Liczba
    sprzedaży

    Cena
    jednostkowa

    Sprzedaż
    razem

    Bochenek

    Sedan

    5

    33000

    165000

    Coupe

    4

    37000

    148000

    Rybka

    Sedan

    6

    24000

    144000

    Coupe

    10

    21000

    168000

    Karwat

    Sedan

    3

    29000

    87000

    Coupe

    1

    31000

    31000

    Chmiela

    Sedan

    9

    24000

    216000

    Coupe

    5

    37000

    185000

    Czupta

    Sedan

    6

    33000

    198000

    Coupe

    10

    31000

    248000

    Szypuła

    Sedan

    2

    27000

    Coupe

    3

    30000

    Wiśniewska

    Sedan

    4

    22000

    Coupe

    1

    41000

    Bochenek

    Sedan

    5

    32000

    Coupe

    3

    36000

    Suma końcowa

  2. Zaznacz komórkę E18, wprowadź formułę na sumę końcową = SUMA(C2:C17*D2*D17) w komórce A20 i naciśnij klawisze Ctrl+Shift+Enter.
    Odpowiedź powinna wynieść 2 131 000.

  3. Zaznacz zakres komórek zawierający bieżącą formułę tablicową (E2:E11) oraz puste komórki (E12:E17), które znajdują się obok nowych danych. Innymi słowy zaznacz komórki E2:E17.

  4. Naciśnij klawisz F2, aby przełączyć się do trybu edycji.

  5. Na pasku formuły zmień wartość C11 na C17, a wartość D11 na D17 i naciśnij klawisze Ctrl+Shift+Enter.
    Program Excel zaktualizuje formułę w komórkach od E2 do E11 i umieści wystąpienie formuły w nowych komórkach (od E12 do E17).

  6. Wpisz formułę tablicową = SUMA(C2:C17*D2*D17) w komórce F17, aby odnosiła się do komórek od wiersza 2 do wiersza 17, i naciśnij klawisze Ctrl+Shift+Enter, aby wprowadzić formułę tablicową.
    Nowa suma końcowa powinna wynosić 2 131 000.

Wady korzystania z formuł tablicowych

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

  • Czasami można zapomnieć o konieczności naciśnięcia 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 rozumieć utworzonych formuł. W praktyce formuły tablicowe nie są zazwyczaj wyjaśnione w arkuszu, więc jeśli inne osoby będą musiały modyfikować skoroszyty użytkownika, należy unikać stosowania formuł tablicowych lub upewnić się, że te osoby znają formuły tablicowe i wiedzą, jak zmieniać takie formuły w razie potrzeby.

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

Początek strony

Informacje o stałych tablicowych

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ć kombinacji 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 przykład tablicy składającej się z jednego wiersza: {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 zawierającej dwa wiersze, pierwszy zawiera liczby 1, 2, 3 i 4, a drugi liczby 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.

Początek strony

Tworzenie stałych jednowymiarowych i dwuwymiarowych

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

Tworzenie stałej poziomej

  1. Użyj skoroszytu z poprzednich przykładów lub utwórz nowy skoroszyt.

  2. Zaznacz komórki od A1 do E1.

  3. Na pasku formuły wprowadź następującą formułę i naciśnij klawisze Ctrl+Shift+Enter:

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

    W tym przypadku należy wpisać nawias klamrowy otwierający i zamykający ({ }).

    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łę i 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łę i 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łę i 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.

Składnia stałej tablicowej

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 także 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ć, należy skopiować funkcję, zaznaczyć pustą komórkę w arkuszu, wkleić formułę na pasku formuły, a następnie nacisnąć 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})

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

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.

Nadawanie nazw stałym tablicowym

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 poniższą 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.

Rozwiązywanie problemów ze stałymi tablicowymi

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. Pominięcie średnika lub ukośnika albo umieszczenie go w nieodpowiednim miejscu może spowodować, że stała tablicowa nie zostanie poprawnie utworzona lub zostanie 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.

Stałe tablicowe w działaniu

W poniższych przykładach pokazano kilka sposobów użycia stałych tablicowych w formułach tablicowych. W niektórych przykładach użyto funkcji TRANSPONUJ w celu przekonwertowania 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 poniższą 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 poniższą 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 poniższą 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. Wpisz poniższą 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.

Początek strony

Korzystanie z podstawowych formuł tablicowych

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. Spowoduje to wprowadzenie tej tablicy liczb w zakresie komórek C8:E10 przy użyciu formuły tablicowej.
    W arkuszu komórki od C8 do E10 powinny wyglądać następująco:

    10

    20

    30

    40

    50

    60

    70

    80

    90

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

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

    =C8:E10

    Zostanie wyświetlona tablica komórek o wielkości 3x3: od C1 do E3 zawierająca te same wartości, które były widoczne 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.
    Formuła tablicowa nadal powinna mieć postać =C8:E10.

  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ć postać ={10,20,30;40,50,60;70,80,90}, tak jak w komórkach C8:E10.

  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.

  2. Zaznacz komórkę A9, 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ę A12, a następnie naciśnij klawisze Ctrl+Shift+Enter, aby wyświetlić zawartość najdłuższej komórki z zakresu A2:A6 (komórka A3).

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)

Następująca formuła została użyta w komórce A9 do zliczenia całkowitej liczby 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) w komórce zawierającej formułę (A9).

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. Zaznacz komórki od A16 do A18.
    W tym zestawie komórek będą przechowywane wyniki zwracane przez formułę tablicową.

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

    =MIN.K(A5:A14;{1\2\3})

W komórkach od A16 do A18 będą wyświetlane wartości 400, 475 i 500.

Ta formuła używa stałej tablicowej w celu trzykrotnego wykonania funkcji MIN.K 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 oraz odpowiednią liczbę komórek wyników do zakresu A12:A14. W tej formule można także używać dodatkowych funkcji, takich jak SUMA lub ŚREDNIA. Na przykład:

=SUMA(MIN.K(A 5 :A1 4 ;{1\2\3}))

=ŚREDNIA(MIN.K(A 5 :A1 4 ;{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 A1 do A3.

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

    =MAX.K(A5:A14;WIERSZ(ADR.POŚR("1:3")))

W komórkach od A1 do A3 będą wyświetlane wartości 3200, 2700 i 2000.

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 kolumnie w skoroszycie ćwiczeniowym, wprowadź tę formułę tablicową w komórkach A5:A14, 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.

Warto też przeanalizować działanie formuły używanej wcześniej  — =MAX.K(A5:A14;WIERSZ(ADR.POŚR("1:3"))) — rozpoczynając od elementów w nawiasach wewnętrznych i przesuwając się na zewnątrz. Funkcja ADR.POŚR zwraca zestaw wartości tekstowych, w tym przypadku wartości od 1 do 3. Z kolei funkcja WIERSZ generuje zawierającą trzy komórki tablicę kolumnową. Funkcja MAX.K używa wartości w zakresie komórek A5:A14, a następnie trzykrotnie je ocenia, po jednym razie dla każdego odwołania zwróconego przez funkcję WIERSZ. Wartości 3200, 2700 i 2000 są zwracane do zawierającej trzy komórki tablicy kolumnowej. Aby znaleźć więcej wartości, należy do funkcji ADR.POŚR dodać większy zakres komórek.

Tej formuły można też użyć z innymi funkcjami, takimi jak SUMA i ŚREDNIA.

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

Ta formuła działa tylko wtedy, gdy zakres danych zawiera jedną kolumnę komórek. W arkuszu Arkusz3 wprowadź następującą formułę w komórce A16 i naciśnij klawisze Ctrl+Shift+Enter:

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

W komórce A16 pojawi się napis „szereg 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 elementu z zakresu komórek A6:A9. Funkcja MAX określa, która z tych wartości jest największa, a więc odpowiada najdłuższemu ciągowi tekstowemu, który znajduje się w komórce A7.

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Ł( A6 : A9 ))

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

DŁ( A6:A9 )

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 A6:A9 dostarcza tablicę, funkcja PODAJ.POZYCJĘ dostarcza adres komórki, a ostatni argument (1) określa, że wartość pochodzi z pierwszej kolumny w tablicy.

Początek strony

Korzystanie z zaawansowanych formuł tablicowych

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))

Ta formuła tworzy nową tablicę, która zawiera oryginalne wartości minus wartości błędu. Działanie formuły, analizując je od funkcji wewnętrznych w kierunku funkcji zewnętrznych, jest następujące. Funkcja CZY.BŁĄD wyszukuje błędy w zakresie komórek (Dane). Funkcja JEŻELI zwraca określoną wartość, jeśli określony warunek jest spełniony (PRAWDA) i inną wartość, jeśli nie jest on spełniony (FAŁSZ). W tym przypadku ta funkcja zwraca pusty ciąg ("") dla każdej wartości błędu, ponieważ wynikiem oceny warunku jest wartość PRAWDA. Ta funkcja zwraca także wszystkie pozostałe wartości z zakresu (Dane), ponieważ dla nich wynikiem oceny warunku jest wartość FAŁSZ, co oznacza, że nie zawierają one wartości błędu. Następnie funkcja SUMA oblicza sumę przefiltrowanej 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))

Początek strony

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ż

Omówienie formuł

Rozwijaj swoje umiejętności
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.

×