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

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

Uwaga: Staramy się udostępniać najnowszą zawartość Pomocy w Twoim języku tak szybko, jak to możliwe. Ta strona została przetłumaczona automatycznie i może zawierać błędy gramatyczne lub nieścisłości. Chcemy, aby ta zawartość była dla Ciebie przydatna. Prosimy o powiadomienie nas, czy te informacje były pomocne, u dołu tej strony. Oto angielskojęzyczny artykuł do wglądu.

Formuła tablicowa jest formułą, która umożliwia wykonywanie wielu obliczeń w jednym lub kilku elementach tablicy. Tablicę można traktować jako wiersz lub kolumnę wartości albo kombinację wierszy i kolumn wartości. Formuły tablicowe mogą zwracać wiele wyników lub jeden wynik.

Począwszy od aktualizacji z września 2018 dla pakietu Office 365, każda formuła, która może zwracać wiele wyników, będzie automatycznie rozlewać je w dół lub w poprzek sąsiadujących komórek. Zmianie zachowania towarzyszy także kilka nowych funkcji dynamicznych tablic. Dynamiczne formuły tablicowe, niezależnie od tego, czy korzystają z istniejących funkcji, czy dynamicznej tablicy, muszą być wprowadzone tylko w jednej komórce, a następnie potwierdzone przez naciśnięcie klawisza Enter. Wcześniejsze, starsze formuły tablicowe wymagają najpierw zaznaczenia całego zakresu wyjściowego, a następnie potwierdzenia formuły za pomocą kombinacji klawiszy CTRL + SHIFT + ENTER. Są one często określane jako formuły klienta .

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

  • Szybkie tworzenie przykładowych zestawów danych.

  • Zliczanie znaków zawartych w zakresie komórek.

  • Sumowanie tylko liczb spełniających określone warunki, takich jak najniższe wartości w zakresie lub liczby, które mieszczą się między górną a dolną granicą.

  • Zsumowanie każdej n-tej wartości w zakresie wartości.

W poniższych przykładach pokazano, jak tworzyć wielokomórkowe i jednokomórkowe formuły tablicowe. W miarę możliwości uwzględniono przykłady dotyczące funkcji tablic dynamicznych, 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

  • W tym miejscu obliczamy łączną sprzedaż dla każdego sprzedawcy przez sedan, wprowadzając ciąg = F10: F19 * G10: G19 w komórce H10.

    Po naciśnięciu klawisza Enterwyniki są przelewane w dół do komórek H10: H19. Zauważ, że zakres rozlania jest wyróżniony obramowaniem, gdy zaznaczysz dowolną komórkę w zakresie rozlania. Możesz również zauważyć, że formuły w komórkach H10: H19 są wyszarzone. Są one dostępne tylko w celu dostosowania formuły, dlatego należy zaznaczyć komórkę H10, w której ma się znaleźć formuła główna.

  • Formuła tablicowa z jedną komórką

    Formuła tablicowa z jedną komórką do obliczania sumy końcowej = suma (F10: F19 * G10: G19)

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

    W takim przypadku program Excel mnoży wartości w tablicy (komórki z zakresu od F10 do G19), a następnie używa funkcji Suma, aby dodać sumy razem. 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. Ponadto Zwróć uwagę, że formuła z jedną komórką w komórce H20 jest całkowicie niezależna od formuły wielokomórkowej (formuła w komórkach H10 do H19). Jest to kolejna zaleta korzystania z formuł tablicowych — elastyczność. Możesz zmienić inne formuły w kolumnie H bez wpływu na formułę w programie H20. Dobrą praktyką jest również niezależna suma taka jak ta, co ułatwia sprawdzanie dokładności wyników.

  • Formuły tablicowe dynamiczne oferują też następujące korzyści:

    • Spójność    Jeśli klikniesz dowolną komórkę z 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 formuły tablicowej z wieloma komórkami. Na przykład kliknij komórkę H11, a następnie naciśnij klawisz Delete. Program Excel nie zmieni wyjścia tablicy. Aby ją zmienić, należy zaznaczyć komórkę w lewym rogu tablicy lub komórkę H10.

    • Mniejsze rozmiary plików    Często można użyć pojedynczej formuły tablicowej zamiast kilku formuł pośrednich. Na przykład w przykładowej sprzedaży samochodów użyto jednej formuły tablicowej do obliczenia wyników w kolumnie E. Jeśli użyto standardowych formuł, takich jak = F10 * G10, F11 * G11, F12 * G12 itd., można użyć 11 różnych formuł do obliczenia tych samych wyników. To nie jest duża transakcja, ale co zrobić, jeśli masz tysiące wierszy do zsumowania? Może to potrwać bardzo dużą różnicę.

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

    • Mieściły    Dynamiczne formuły tablicowe będą automatycznie rozlać do zakresu wyjściowego. Jeśli dane źródłowe są w tabeli programu Excel, to dynamiczne formuły tablicowe będą automatycznie zmieniać rozmiar podczas dodawania lub usuwania danych.

    • #SPILL! błędów    Tablice dynamiczne wprowadziły błąd #SPILL!wskazujący, że przewidziany zakres rozlania jest zablokowany z jakiegoś powodu. Po rozwiązaniu tego blockage formuła zostanie automatycznie rozlana.

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 rozgraniczyć elementy w każdym wierszu, rozdzielając je przecinkami, i rozdzielić poszczególne wiersze średnikami.

Wykonując poniższe procedury, można przećwiczyć tworzenie stałych poziomych, pionowych i dwuwymiarowych. Będziemy pokazywać przykłady użycia funkcji sekwencja w celu automatycznego generowania stałych tablicowych, a także ręcznie wprowadzanych 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ź = Sequence (1; 5). Funkcja SEQUENCE buduje 1 wiersz o 5 kolumn w taki sam sposób, jak = {1, 2, 3, 4, 5}. Zostanie wyświetlony następujący wynik:

    Tworzenie poziomej stałej tablicowej z = sekwencja (1, 5) lub = {1, 2, 3, 4, 5}

  • Tworzenie stałej pionowej

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

    Tworzenie pionowej stałej tablicowej z = sekwencja (5) lub = {1; 2; 3; 4; 5}

  • Tworzenie stałej dwuwymiarowej

    Zaznacz dowolną pustą komórkę ze spacją po prawej stronie i pod nią, a następnie wprowadź = Sequence (3; 4). Zostanie wyświetlony następujący wynik:

    Tworzenie tablicy o podstawie 3 wierszach o 4 kolumny z czterema kolumnami; = sekwencja (3; 4)

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

    Jak widać, opcja Sekwencja oferuje znaczne korzyści dotyczące ręcznego wprowadzania wartości stałych tablicowych. Wystarczy, że oszczędza to czas, ale również ułatwia zmniejszenie błędów z wpisu ręcznego. Jest to też łatwiejsze do odczytania, szczególnie w przypadku, gdy średniki mogą być trudne do odróżnienia od separatorów przecinkiem.

Oto przykład, w którym użyto stałych tablicowych jako części większej formuły. W przykładowym skoroszycie przejdź do stałej w arkuszu formuły lub Utwórz nowy arkusz.

W komórce D9 wprowadzono = sekwencja (1, 5, 3; 1), ale można także wprowadzić 3, 4, 5, 6 i 7 w komórkach A9: H9. Nie ma żadnych specjalnych informacji na temat tego konkretnego numeru, więc wybieramy coś innego niż 1-5, aby uzyskać zróżnicowanie.

W komórce E11 wpisz = Suma (D9: H9 * Sequence (1, 5))lub = sum (D9: H9 * {1, 2, 3, 4, 5}). Formuły zwracają 85.

Używanie stałych tablicowych w formułach. W tym przykładzie użyto funkcji = Suma (D9: H (* sekwencja (1; 5)).

Funkcja SEQUENCE tworzy odpowiednik tablicy stałych {1, 2, 3, 4, 5}. Ponieważ program Excel wykonuje operacje na wyrażeniach ujętych w nawiasy pierwsze, dwa następne elementy, które są odtwarzane, to wartości komórek w 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:

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

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

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

= Sum (Sequence (1; 5; 3; 1) * Sequence (1, 5))lub = sum ({3; 4; 5; 6; 7} * {1; 2; 3; 4; 5})

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

  • Stałe tablicowe mogą zawierać liczby, tekst, wartości logiczne (takie jak prawda i FAŁSZ) oraz wartości błędów, takie jak #N/A. Liczb można używać w formatach liczb całkowitych, dziesiętnych i naukowych. W przypadku dołączania tekstu należy go ująć 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 najlepszego sposobu użycia stałych tablicowych jest nadawanie ich nazw. 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 formuł _GT_ zdefiniowanych nazw > Definiuj nazwę. W polu Nazwa wpisz ciąg 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ć następująco:

Dodawanie tablicy o nazwie "stała Tablica" z formuł > zdefiniowanych nazw > Name Manager > New

Kliknij przycisk OK, a następnie zaznacz dowolny wiersz z trzema pustymi komórkami i wprowadź = ciąg Kwartał1.

Zostanie wyświetlony następujący wynik:

Użyj nazwanej stałej tablicowej w formule, na przykład = ciąg Kwartał1, gdzie ciąg Kwartał1 została zdefiniowana jako = {"Styczeń", "luty", "marzec"}

Jeśli wyniki mają być rozlewane w pionie zamiast w poziomie, można użyć funkcji =TRANSPONUJ(ciąg Kwartał1).

Jeśli chcesz wyświetlić listę 12 miesięcy, na przykład podczas tworzenia sprawozdania finansowego, możesz oprzeć się na tym roku w funkcji SEQUENCE. Za pomocą tej funkcji należy pamiętać, że nawet jeśli jest wyświetlany tylko miesiąc, jest to poprawna data, która może być używana w innych obliczeniach. Te przykłady znajdziesz w przykładowym nazwanym tablicy stałych tablic i szybkich próbkach zestawów danych w skoroszycie.

= TEKST (Data (rok (dzisiaj ()); SEQUENCE (1; 12); 1); "MMM")

Używanie kombinacji funkcji tekst, Data, rok, dziś i sekwencja w celu utworzenia dynamicznej listy 12 miesięcy

Spowoduje to utworzenie daty na podstawie bieżącego roku, a sekwencja tworzy tablicę stałą z przedziału od 1 do 12 od stycznia do grudnia, a następnie Funkcja tekst konwertuje format wyświetlania na wartość "MMM" (sty, lut, mar itd.). Jeśli chcesz wyświetlić pełną nazwę miesiąca, na przykład styczeń, użyj ciągu "MMMM".

Używając nazwanej stałej jako formuły tablicowej, pamiętaj, aby wpisać znak równości, na przykład = ciąg Kwartał1, a nie tylko ciąg 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. Na koniec pamiętaj, że możesz korzystać z kombinacji funkcji, tekstu i liczb. Wszystko to zależy od tego, jak kreatywność ma być uzyskiwana.

W poniższych przykładach pokazano kilka sposobów użycia stałych tablicowych w formułach tablicowych. W kilku przykładach użyto funkcji TRANSPONUJ w celu przekonwertowania wierszy na kolumny i odwrotnie.

  • Wiele elementów w tablicy

    Enter = sekwencja (1; 12) * 2, lub = {1; 2; 3; 4; 5; 6; 7; 8; 9

    Możesz również podzielić się na (/), dodać za pomocą (+) i odjąć za pomocą (-).

  • Obliczanie kwadratów elementów w tablicy

    Enter = sekwencja (1; 12) ^ 2lub = {1; 2; 3; 4; 5; 6; 7; 8; 9

  • Znajdowanie pierwiastka kwadratowego kwadratowego elementów w tablicy

    Enter =sqrt(Sequence (1; 12) ^ 2)lub = sqrt ({1; 2; 3; 4; 5; 6; 7; 8; 9; 10

  • Transponowanie jednowymiarowego wiersza

    Enter = TRANSPONUJ (Sequence (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

    Enter = TRANSPONUJ (Sequence (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

    Enter = TRANSPONUJ (Sequence (3; 4))lub = TRANSPONUJ ({1; 2; 3; 4; 5; 6; 7

    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 utworzyć nową tablicę z istniejącej tablicy za pomocą formuł tablicowych.

    Enter = sekwencja (3; 6; 10; 10)lub = {10; 20; 30; 40; 50; 60; 70; 90100110120; 130140150160170180}

    Pamiętaj, aby wpisać {(nawias klamrowy) przed wpisaniem 10, a} (zamykający nawias klamrowy) po wpisaniu 180, ponieważ tworzona jest tablica liczb.

    Następnie wprowadź formułę = D9 #lub = D9: i11 w pustej komórce. Zostanie wyświetlona tablica komórek o wartości 3 x 6 z tymi samymi wartościami, które są widoczne w D9: D11. Znak # jest nazywany operatorem zakresu, a Excel's sposobem odwołania się do całego zakresu tablicy zamiast go wpisywać.

    Odwołanie do istniejącej tablicy za pomocą operatora rozlania zakresu (#)

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

    Wyniki rozlania formuły tablicowej można wykonać i przekonwertować 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, które program Excel konwertuje na stałą tablicową. Po naciśnięciu klawisza Enterformuła = D9 # powinna być teraz równa = {10; 20; 30; 40; 50; 60; 70; 80

  • Zliczanie znaków w zakresie komórek

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

    Zliczanie całkowitej liczby znaków w zakresie oraz innych tablic służących 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 doda te wartości razem i wyświetli wynik (66). Jeśli chcesz uzyskać przeciętną liczbę znaków, możesz skorzystać z następujących funkcji:

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

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

    = INDEKS (C9: C13, MATCH (MAX (LEN (C9: C13)), LEN (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 dł zwraca długość każdego elementu z zakresu komórek D2: D6. Funkcja Max oblicza największą wartość spośród tych elementów, które odpowiadają najdłuższemu ciągowi tekstowemu, który znajduje się w komórce D3.

    Tutaj sprawy nieco się komplikują. Funkcja Uwzględnij oblicza przesunięcie (położenie względne) 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 (C9: C13)

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

    DŁ (C9: C13)

    Argument Uwzględnij typ w tym przypadku jest równy 0. Typ dopasowania może być wartością 1, 0 lub-1.

    • 1-zwraca największą wartość, która jest mniejsza niż lub równa wartości Val.

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

    Wreszcie Funkcja indeks przyjmuje następujące argumenty: tablicę oraz numer wiersza i kolumny w tej tablicy. Zakres komórek C9: C13 zapewnia tablicę, funkcja podaj. pole zawiera adres komórki, a ostatni argument (1) określa, że wartość pochodzi z pierwszej kolumny tablicy.

    Jeśli chcesz uzyskać zawartość najmniejszego ciągu tekstowego, w powyższym przykładzie Zastąp maks.

  • Znajdowanie n najmniejszych wartości w zakresie

    W tym przykładzie pokazano, jak znaleźć trzy najmniejsze wartości w zakresie komórek, w którym znajduje się tablica przykładowych danych w komórkach B9: B18has została utworzona przy użyciu: = int (RANDARRAY(10; 1) * 100). Zauważ, że RANDARRAY jest funkcją nietrwałą, więc po każdym obliczeniu wartości w programie Excel otrzymasz nowy zestaw liczb losowych.

    Formuła tablicowa programu Excel, która umożliwia znalezienie n-tej najB9ej najmniejszej wartości: = małe (#; sekwencja (D9))

    Enter = małe (B9 #; sekwencja (D9), = mały (B9: B18; {1; 2; 3})

    W tej formule użyto stałej tablicowej w celu oszacowania małej funkcji trzykrotności i zwrócenia najmniejszej liczby 3 członków tablicy, która znajduje się w komórkach B9: B18, gdzie 3 jest wartością zmiennej w komórce D9. Aby znaleźć więcej wartości, możesz zwiększyć wartość w funkcji SEQUENCE 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 (MAŁA (B9 #; SEKWENCJA (D9))

    = ŚREDNIA (MAŁA (B9 #; SEKWENCJA (D9))

  • Znajdowanie n największych wartości w zakresie

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

    Enter = Large (B9 #; Row ("1:3")))lub = Large (B9: B18; Row (pośredni ("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 pusty 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). Program Excel dostosowuje odwołania do wierszy, a formuła generuje teraz 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 inDIRECT używa ciągów tekstowych jako argumentów (co oznacza, że zakres 1:10 jest ujęty w cudzysłów). 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. Możesz równie łatwo używać sekwencji:

    = SEQUENCE (10)

    Zapoznaj się z formułą używaną wcześniej — = duża (B9 #, wiersz (poŚREDNIA) ("1:3"))) — Zaczynając od nawiasów wewnętrznych i pracują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 tablicę kolumn o trzech komórkach. W przypadku funkcji LARGE są używane wartości z zakresu komórek B9: B18, a wartość ta jest szacowana trzy razy, raz dla każdego odwołania zwróconego przez wartość wiersz. Jeśli chcesz znaleźć więcej wartości, Dodaj większy zakres komórek do funkcji inDIRECT. Na koniec, podobnie jak w przypadku małych przykładów, 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 podczas próby zsumowania zakresu zawierającego wartość błędu, na przykład #VALUE! lub #N/A. W tym przykładzie pokazano, jak zsumować wartości z zakresu o nazwie dane zawierające błędy:

    Używaj tablic do rozwiązywania problemów z błędami. Na przykład funkcja = suma (jeżeli (wartość. błąd (dane), "", dane "spowoduje zsumowanie zakresu o nazwie dane, nawet jeśli zawiera on błędy, takie jak #VALUE! lub #NA!.

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

    Formuła tworzy nową tablicę zawierającą wartości oryginalne minus wszystkie wartości błędów. Począwszy od funkcji wewnętrznych i pracujących na zewnątrz, funkcja isERROR przeszukuje zakres komórek (dane) dla błędów. Funkcja jeżeli Zwraca określoną wartość, jeśli określony warunek ma wartość PRAWDA, a inna wartość, jeśli ma wartość FAŁSZ. W tym przypadku zwraca on puste ciągi ("") dla wszystkich wartości błędów, ponieważ mają one wartość PRAWDA i zwraca pozostałe wartości z zakresu (dane), ponieważ będą one interpretowane jako FAŁSZ, co oznacza, że nie zawierają wartości błędów. Funkcja suma. następnie oblicza sumę dla filtrowanej tablicy.

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

    Ten przykład przypomina poprzednią formułę, ale zwraca liczbę wartości błędu w zakresie o nazwie dane zamiast je filtrować.

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

Za pomocą tablic można obliczać na podstawie określonych warunków. = SUM (jeżeli (Sales>0; Sales)) sumuje wszystkie wartości większe niż 0 w zakresie o nazwie Sales.

Na przykład ta formuła tablicowa sumuje tylko dodatnie liczby całkowite z zakresu o nazwie Sprzedaż, który reprezentuje 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łszywych. 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 i mniejsze niż 2500:

= SUM ((Sales>0) * (Sales<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. Na przykład można sumować wartości większe niż 0 lub mniejsze niż 2500:

= Suma (jeżeli ((Sales>0) + (Sales<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, wykonywanie operacji matematycznych, takich jak dodawanie lub mnożenie, na wartościach, które spełnią warunek OR lub AND.

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 można było użyć tej formuły, zakresy komórek muszą mieć taki sam rozmiar i ten sam wymiar. Jeśli na przykład dane są w zakresie 3 wierszy na 5 kolumn, TwojeDane musi także zawierać 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 * (MyData<>YourData))

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

W przykładowym skoroszycie znajdziesz podobne przykłady na temat różnic między arkuszem zestawów 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łą tabelę poniżej i wklej ją w komórce A1 w pustym arkuszu.

Sprzedaż Osoba

Samochód Wpisz tekst

Numer Sprzedaż

Unit (jednostka ) Cena

Suma 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 wyświetlić łączną wartość sprzedaży dla każdego sprzedawcy, należy zaznaczyć komórki E2: E11, wprowadzić formułę = C2: C11 * D2: D11, a następnie nacisnąć 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}

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 takim 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 razem. 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.

Ponadto Zwróć uwagę, ż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ły w kolumnie E lub całkowicie usunąć tę kolumnę bez wpływu na formułę w 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. 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ą. Jako dodatkowy środek bezpieczeństwa należy nacisnąć klawisze CTRL + SHIFT + ENTER , aby potwierdzić zmianę formuły.

  • Mniejsze rozmiary plików    Często można użyć pojedynczej formuły tablicowej zamiast kilku formuł pośrednich. Na przykład w skoroszycie użyto jednej formuły tablicowej do obliczenia wyników w kolumnie E. Jeśli użyto standardowych formuł (takich jak = C2 * D2, C3 * D3, C4 * D4...), być może użyto 11 różnych formuł do obliczania tych samych wyników.

Ogólnie, formuły tablicowe używają standardowej składni formuły. Wszystkie te funkcje zaczynają się od znaku równości (=), a w formułach tablicowych można używać większości wbudowanych funkcji programu Excel. W przypadku korzystania z formuły tablicowej należy nacisnąć klawisze CTRL + SHIFT + ENTER , aby wprowadzić formułę. Po wykonaniu tej czynności program Excel otacza formułę tablicową za pomocą nawiasów klamrowych — Jeśli ręcznie wpisano nawiasy, formuła zostanie przekonwertowana na ciąg tekstowy i nie będzie działać.

Funkcje tablicowe mogą być efektywnym 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: Naciskaj klawisze CTRL + SHIFT + ENTER , ilekroć zechcesz wprowadzić formułę tablicową. 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.

  • Nie można wstawiać pustych komórek do ani usuwać komórek z wielokomórkowej formuły tablicowej.

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 zakresu formuły. Klucz polega na zaznaczeniu całego zakresu, rozpoczynając od lewej lewej komórki tablicy. Lewa lewa komórka to ta, którą edytujesz.

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

  • Czasami nie zapomnisz nacisnąć 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ć formuł. W praktyce formuły tablicowe nie są ogólnie objaśnione w arkuszu. Dlatego jeśli inne osoby muszą modyfikować skoroszyty, należy unikać formuł tablicowych lub upewnić się, że te osoby znają wszystkie formuły tablicowe i dowiedzieć się, jak je zmienić, jeśli wymagają tego.

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

Teraz po utworzeniu formuł tablicowych należy nacisnąć 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 w celu wprowadzenia całej formuły.

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 dwóch tablicach wierszy pierwszym wierszem jest 1, 2, 3 i 4, a drugi wiersz 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ć otwierający i zamykający nawias klamrowy ({}), a program 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. Pamiętaj też, że po dodaniu stałej do formuły tablicowej należy nacisnąć klawisze CTRL + SHIFT + ENTER , aby wprowadzić formułę.

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 zrobić, skopiuj ją, 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 najważniejszych sposobów używania stałych tablicowych jest nadawanie ich nazw. 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 Definiuj nazwę .

  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.

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 pominięto przecinek lub średnik lub jeśli umieścisz je w niewłaściwym miejscu, stała tablicowa może nie zostać utworzona poprawnie lub może zostać wyświetlony komunikat z ostrzeżeniem.

  • 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 kilku 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 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 spowoduje wprowadzenie tej tablicy liczb w zakresie komórek C8: E10 przy użyciu formuły tablicowej. W arkuszu C8 przez E10 powinna wyglądać następująco:

    10

    20

    30

    40

    50

    60

    70

    80

    90

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

  4. Wpisz poniższą formułę na pasku formuły, a następnie naciśnij klawisze CTRL + SHIFT + ENTER:

    =C8:E10

    3x3 tablica komórek jest wyświetlana w komórkach od C1 do E3 o tych samych wartościach, które są widoczne w C8 przez E10.

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

  1. Gdy jest zaznaczona pozycja 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 być teraz równa = {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).

W A8 komórek jest używana następująca formuła, która 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 doda te wartości razem i wyświetli 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ŁE (A1: A11; {1; 2; 3})

W tej formule użyto stałej tablicowej w celu oszacowania małej funkcji trzykrotnej i zwrócenia najmniejszej (1), sekundy najmniejszej (2) i trzeciego najmniejszej (3) członków tablicy, która jest zawarta w komórkach a1: A10 aby znaleźć więcej wartości, należy dodać więcej argumentów do poziomie. W tej formule można także używać dodatkowych funkcji, takich jak SUMA lub ŚREDNIA. Na przykład:

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

= AVERAGE (MAŁE (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ŻY (A1: A10; WIERSZ (POŚREDNI ("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ę z 10 komórkami w skoroszycie ć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.

Zapoznaj się z formułą używaną wcześniej — = duża (A5: A14; Row (pośredni ("1:3"))) — Zaczynając od nawiasów wewnętrznych i pracujących na zewnątrz: funkcja Indirect zwraca zestaw wartości tekstowych, w tym przypadku wartości od 1 do 3. Funkcja wiersz z kolei generuje tablicę kolumnowy o trzech komórkach. W przypadku funkcji Large są używane wartości z zakresu komórek A5: A14, a wartość ta jest szacowana trzy razy, raz dla każdego odwołania zwróconego przez wartość wiersz . Wartości 3200, 2700 i 2000 są zwracane do tablicy kolumn o trzech komórkach. Jeśli chcesz znaleźć więcej wartości, Dodaj większy zakres komórek do funkcji Indirect .

Podobnie jak w przypadku wcześniejszych przykładów, można użyć tej formuły z innymi funkcjami, takimi jak Suma i średnia.

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

Wróć do poprzedniego przykładowego ciągu tekstowego Wprowadź następującą formułę w pustej komórce, a następnie naciśnij klawisze CTRL + SHIFT + ENTER:

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

Pojawi się tekst "pęczka komórki, która".

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 w zakresie komórek a2: A6. Funkcja Max oblicza największą wartość spośród tych elementów, które odpowiadają 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Ł. JEŻELI (A2: A6))

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

LEN (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 zawiera tablicę, a funkcja Podaj . tekst zapewnia adres komórki, a końcowy argument (1) określa, że wartość pochodzi z pierwszej kolumny 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ą wartości oryginalne minus wszystkie wartości błędów. Począwszy od funkcji wewnętrznych i pracujących na zewnątrz, Funkcja ISERROR przeszukuje zakres komórek (dane) dla błędów. Funkcja Jeżeli Zwraca określoną wartość, jeśli określony warunek ma wartość PRAWDA, a inna wartość, jeśli ma wartość FAŁSZ. W tym przypadku zwraca on puste ciągi ("") dla wszystkich wartości błędów, ponieważ mają one wartość PRAWDA i zwraca pozostałe wartości z zakresu (dane), ponieważ będą one interpretowane jako FAŁSZ, co oznacza, że nie zawierają wartości błędów. Funkcja Suma . następnie 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 * (MyData<>YourData))

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

Potwierdzili informacje

Część tego artykułu została utworzona na podstawie serii kolumn użytkownika dodatku programu Excel napisanych przez marek Rolecki i dostosowanych do działów 14 i 15 formuł programu Excel 2002, książki napisanej przez Jan Walkenbach, byłego 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 klienta

Funkcja FILTRUJ

Funkcja LOSOWA.TABLICA

Funkcja SEKWENCJA

Funkcja POJEDYNCZE

Funkcja SORTUJ

Funkcja SORTUJ.WEDŁUG

Funkcja UNIKATOWE

Błędy #SPILL! w programie Excel

Omówienie formuł

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.

×