Wskazówki i przykłady dotyczące 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

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

Sprzedawca	Typ  samochodu	Liczba  sprzedanych	Cena  jednostkowa	Całkowita sprzedaż
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 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.

2. 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.

Po naciśnięciu klawiszy Ctrl+Shift+Enterprogram Excel otacza formułę nawiasami klamrowymi ({ }) i wstawia 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}. 

• Tworzenie jednokomórkowej formuły tablicowej

W komórce D13 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 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żna zmienić formułę w kolumnie E lub całkowicie usunąć tę kolumnę, bez wpływu na formułę w komórce D13.

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ą. Jako dodatkowy środek bezpieczeństwa naciśnij Ctrl+Shift+Enter, aby potwierdzić wszelkie zmiany w formule.

• Mniejszy rozmiar plików    Często można użyć pojedynczej 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ł.

Ogólnie rzecz biorąc, formuły tablicowe używają standardowej składni formuły. Wszystkie zaczynają się od znaku równości (=) i można w nich używać dowolnych wbudowanych funkcji programu Excel. Kluczową różnicą jest to, że w przypadku korzystania z formuły tablicowej należy nacisnąć klawisze Ctrl+Shift+Enter, aby wprowadzić formułę. Gdy to zrobisz, program Excel otoczy formułę tablicową nawiasami klamrowymi — jeśli wprowadzisz nawiasy klamrowe ręcznie, formuła zostanie przekonwertowana na ciąg tekstowy i nie będzie działać.

Funkcje tablicowe mogą stanowić wydajny sposób na tworzenie 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).

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 Usuń.

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

Czasami może być konieczne rozszerzenie formuły tablicowej. Zaznacz pierwszą komórkę w istniejącym zakresie tablicy i kontynuuj do momentu zaznaczenia 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 jej zakresu. Kluczem 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:

• 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ć Twoich formuł. W praktyce formuły tablicowe zwykle nie są wyjaśnione w arkuszu. 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ń.

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.

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 o A1 do E1.

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

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

   W tym przypadku musisz wprowadzić otwierający i zamykający nawias klamrowy ({ }), a program Excel doda drugi zestaw.

   Zostanie wyświetlony poniższy wynik.

   

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.

   

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:

   

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.

   

   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.

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

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:

   

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.

   

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. Pominięcie przecinka lub średnika 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.

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.

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. 

2. Naciśnij klawisz F9, aby przekonwertować odwołania do komórek na wartości. Program Excel przekonwertuje wartości na stałą tablicową. Formuła powinna teraz wyglądać następująco: ={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 zakresu A2:A6 (komórka A3).

Następująca formuła została użyta w komórce A8 do zliczenia całkowitej liczby znaków (66) w komórkach od A2 do A6.

=SUMA(DŁ(A2:A6))

W tym przypadku funkcja DŁ 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ź liczby losowe w komórkach A1:A11.

2. Wybierz komórki od C1 do C3. Ten zestaw komórek będzie przechowywać wyniki zwracane przez formułę tablicową.

3. Wpisz poniższą formułę, a następnie naciśnij klawisze Ctrl+Shift+Enter:

   =MIN.K(A1:A11,{1;2;3})

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. W tej formule można także używać dodatkowych funkcji, takich jak SUMA lub ŚREDNIA. Na przykład:

=SUMA(MIN.K(A1:A10,{1\2\3})

=ŚREDNIA(MIN.K(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. Wybierz komórki od D1 do D3.

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

   =MAX.K(A1:A10;WIERSZ(ADR.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 pustą kolumnę zawierającą 10 komórek w swoim skoroszycie do ćwiczeń, 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.

Przeanalizujmy użytą wcześniej formułę — =MAX.K(A5:A14,WIERSZ(ADR.POŚR("1:3"))) — zaczynając od nawiasów wewnętrznych i kierując się na zewnątrz: funkcja ADR.POŚR zwraca zestaw wartości tekstowych, w tym przypadku wartości od 1 do 3. Funkcja WIERSZ z kolei generuje tablicę kolumnową z trzema komórkami. Funkcja MAX.K używa wartości z zakresu komórek A5:A14 i jest oceniana trzy razy dla każdego odwołania zwróconego przez funkcję WIERSZ. Wartości 3200, 2700 i 2000 są zwracane do tablicy kolumnowej z trzema komórkami. Jeśli chcesz znaleźć więcej wartości, dodaj większy zakres komórek do funkcji ADR.POŚR.

Podobnie jak we wcześniejszych przykładach, tej formuły można użyć także 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)

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 DŁ zwraca długość każdego z elementów w zakresie komórek A2:A6. Funkcja MAKS oblicza największą wartość spośród tych elementów, która odpowiada najdłuższemu ciągowi tekstowemu, znajdującemu 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(LEN(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 PODAJ.POZYCJĘ 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 poza wszelkimi wartościami błędów. Począwszy od funkcji wewnętrznych i poruszając się w kierunku zewnętrznym, funkcja CZY.BŁĄD wyszukuje błędy w zakresie komórek (Dane). Funkcja JEŻELI zwraca określoną wartość, jeśli podany warunek ma wartość PRAWDA, albo inną wartość, jeśli ten warunek ma wartość FAŁSZ. W tym przypadku zwraca puste ciągi ("") dla wszystkich wartości błędów, ponieważ są one ocenione jako wartość TRUE, i zwraca pozostałe wartości z zakresu (Dane), ponieważ są one ocenione jako FAŁSZ, czyli niezawierające wartości błędów. Następnie funkcja SUMA oblicza sumę dla 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))