Jak korzystać z formuły tablicowej w programie Excel

Formuły tablicowe to kluczowa część zestawu narzędzi, dzięki którym program Excel jest wszechstronny. Jednak te wyrażenia mogą być zniechęcające dla początkujących.

Choć na pierwszy rzut oka mogą wydawać się skomplikowane, ich podstawy są łatwe do zrozumienia. Nauka tablic jest przydatna, ponieważ te formuły upraszczają zarządzanie złożonymi danymi programu Excel.

Co to jest formuła tablicowa w programie Excel?

Tablica to dowolna kolekcja komórek w kolumnie, wierszu lub kombinacji zebranych razem w grupę.

W programie Microsoft Excel termin formuła tablicowa odnosi się do rodziny formuł, które wykonują jedną lub więcej operacji na takim zakresie komórek jednocześnie, a nie pojedynczo.

Wcześniejsze wersje programu Excel wymagały od użytkowników naciśnięcia klawiszy Ctrl + Shift + Enter w celu utworzenia funkcji tablicowej, co skutkowało nazwami funkcji CSE (Ctrl, Shift, Escape), chociaż nie dotyczy to już programu Excel 365.

Przykładem może być funkcja, która pobiera sumę wszystkich sprzedaży powyżej 100 USD w danym dniu. Lub określić długość (w cyfrach) pięciu różnych liczb zapisanych w pięciu komórkach.

Formuły tablicowe mogą być również doskonałym sposobem pobierania przykładowych danych z dużych zestawów danych. Mogą używać argumentów obejmujących zakres komórek z pojedynczej kolumny, zakres komórek z jednego wiersza lub komórki obejmujące wiele wierszy i kolumn.

Instrukcje warunkowe można również stosować podczas wyciągania danych do formuły tablicowej, na przykład przeciągania tylko liczb powyżej lub poniżej określonej wartości lub przeciągania co n-tej wartości.

Ta szczegółowa kontrola pozwala upewnić się, że pobierasz dokładny podzbiór danych, odfiltrowujesz niechciane wartości komórek lub pobierasz losową próbkę elementów do celów testowych.

Jak zdefiniować i utworzyć tablicę w programie Excel

Formuły tablicowe są na najbardziej podstawowym poziomie łatwe do utworzenia. Jako prosty przykład możemy rozważyć fakturę dotyczącą zamówienia części klienta z następującymi informacjami o pozycji: produkt WeU (jednostka magazynowa), zakupiona ilość, łączna waga zakupionych produktów oraz cena za zakupioną jednostkę.

Aby uzupełnić fakturę, na końcu dolnej tabeli należy dodać trzy kolumny, jedną na sumę częściową części, jedną na koszt wysyłki, a trzecią na łączną cenę każdej pozycji.

Możesz użyć prostej formuły, aby niezależnie obliczyć każdą pozycję. Ale jeśli faktura dotyczyła wielu pozycji, może szybko stać się znacznie bardziej skomplikowana. Zamiast tego „Suma częściowa” może zostać obliczona za pomocą jednej formuły umieszczonej w komórce E4:

=B4:B8 * D4:D8

Obie liczby, które mnożymy, są zakresami komórek, a nie pojedynczymi komórkami. W każdym wierszu formuła pobiera odpowiednie wartości komórek z tablic i umieszcza wynik we właściwej komórce bez dalszych działań ze strony użytkownika.

Jeśli przyjmiemy zryczałtowaną stawkę wysyłki w wysokości 1,50 USD za funt, możemy obliczyć naszą kolumnę wysyłki za pomocą podobnej formuły umieszczonej w komórce F4:

=C4:C8 * B11

Tym razem użyliśmy tylko tablicy po lewej stronie operatora mnożenia. Wyniki są nadal wprowadzane automatycznie, ale tym razem każdy jest mnożony przez wartość statyczną.

Na koniec możemy użyć tego samego typu formuły, co w przypadku sumy częściowej, aby uzyskać sumę, dodając tablice sum częściowych i kosztów wysyłki w komórce G4:

=E4:E8 + F4:F8

Po raz kolejny prosta formuła tablicowa zamieniła pięć formuł w jedną.

W przyszłości dodanie obliczeń podatkowych do tego arkusza kalkulacyjnego wymagałoby zmiany tylko jednej formuły zamiast zmiany każdej pozycji.

Zarządzanie wyrażeniami warunkowymi w tablicach programu Excel

W poprzednim przykładzie zastosowano podstawowe formuły arytmetyczne w tablicach, aby uzyskać wyniki, ale prosta matematyka nie będzie już działać w bardziej złożonych sytuacjach.

Załóżmy na przykład, że firma tworząca fakturę w poprzednim przykładzie zmieniła przewoźnika. W takim przypadku standardowa wysyłka może obniżyć cenę, ale może zostać naliczona opłata za przedmioty powyżej określonej wagi.

W przypadku nowych stawek wysyłki standardowa wysyłka będzie kosztować 1,00 USD za funt , a wysyłka o większej wadze będzie kosztować 1,75 USD za funt . Wysyłka o dużej wadze dotyczy rzeczy powyżej siedmiu funtów .

Zamiast powracać do obliczania każdego wiersza stawki wysyłki, formuła tablicowa może zawierać instrukcję warunkową JEŻELI w celu określenia odpowiedniego kosztu wysyłki dla każdej pozycji:

=IF(C4:C8 < 7, C4:C8*B11, C4:C8*B12)

Dzięki jednej szybkiej zmianie funkcji w jednej komórce i dodaniu nowej stawki wysyłki w naszej tabeli stawek, możemy teraz zobaczyć, że nie tylko cała kolumna wysyłki jest obliczana na podstawie wagi, ale sumy automatycznie uwzględniają nową ceny.

W ten sposób faktura jest zabezpieczona na przyszłość, zapewniając, że wszelkie przyszłe zmiany w kalkulacji kosztów będą wymagały zmiany tylko jednej funkcji.

Łączenie wielu lub różnych okresów warunkowych może pozwolić nam na wykonanie różnych czynności. Ta logika może być używana na kilka sposobów na tej samej tablicy danych. Na przykład,

• Można dodać dodatkową funkcję JEŻELI, aby zignorować wysyłkę, jeśli suma częściowa przekroczy określoną kwotę.
• Można dodać tabelę podatkową, aby opodatkować różne kategorie produktów według różnych stawek.

Bez względu na to, jak złożona jest faktura, edycja pojedynczej komórki wystarczy, aby przeliczyć dowolną część rachunku.

Przykład: Uśrednianie N-tych liczb

Formuły tablicowe nie tylko upraszczają obliczanie i aktualizowanie dużych zbiorów danych, ale także umożliwiają pobieranie wycinka zestawu danych do celów testowych.

Jak pokazuje poniższy przykład, wyciągnięcie wycinka danych z dużego zbioru danych do celów sprawdzania poprawności jest łatwe przy użyciu formuł tablicowych:

W powyższym przykładzie komórka D20 wykorzystuje prostą funkcję do uśrednienia co n-tego wyniku z czujnika 1. W tym przypadku możemy zdefiniować n-ty używając wartości w komórce D19, dając nam kontrolę nad wielkością próby, która określa średnią:

=AVERAGE(IF(MOD(ROW(B2:B16)-2,D19)=0,B2:B16,""))

Pozwala to szybko i łatwo podzielić duży zbiór danych na podzbiory.

Opanowanie formuł tablicowych w programie Excel jest niezbędne

Te proste przykłady są dobrym punktem wyjścia do zrozumienia logiki stojącej za formułami tablicowymi. Przemyśl je, aby móc szybko wykonywać wiele operacji dzisiaj iw przyszłości, dostosowując tylko kilka odwołań do komórek i funkcji. Formuły tablicowe to lekki i wydajny sposób dzielenia i dzielenia danych. Opanuj je na początku swojej przygody z Excelem, abyś mógł pewnie wykonywać zaawansowane obliczenia statystyczne.