Podstawowe funkcje programu Excel sprawdzają się w przypadku prostych obliczeń, ale szybko stają się skomplikowane w przypadku złożonej analizy danych. W efekcie powstają trudne do odczytania zagnieżdżone formuły, wiele kolumn pomocniczych, które zaśmiecają arkusz kalkulacyjny, oraz formuły, które mogą się nie powieść po zmianie danych. Właśnie tutaj z pomocą przychodzą formuły macierzowe w programie Excel.
Formuły tablicowe umożliwiają wykonywanie obliczeń na całych zakresach danych za pomocą jednej formuły. Dlatego możesz Wykonuj błyskawiczne wyszukiwania, filtruj i sortuj za pomocą jednego, wydajnego wyrażenia, zamiast pisać oddzielne formuły dla każdego wiersza lub kolumny. Nie jest to nowość w programie Excel, ale niektórzy ludzie trzymają się starych metod wykonywania zadań, podczas gdy nowe funkcje mogą uczynić ich pracę prostszą i bardziej efektywną.
Szybkie linki
5. XWYSZUKAJ
Za każdym razem działa lepiej niż funkcja WYSZUKAJ.PIONOWO.
XLOOKUP to funkcja wyszukiwania, która powinna istnieć od samego początku. W przeciwieństwie do funkcji WYSZUKAJ.PIONOWO, która wymusza liczenie kolumn i przeszukuje tylko w prawo, XLOOKUP działa w dowolnym kierunku i korzysta z rzeczywistych odwołań do kolumn. Jej składnia jest następująca:
=XWYSZUKAJ(wyszukaj_wartość, wyszukana_tablica, zwrócona_tablica, [jeśli nie znaleziono], [tryb_dopasowania], [tryb_wyszukiwania])
Oto co oznacza każdy parametr:
- szukana_wartość: Konkretna wartość, której szukasz. Może to być numer części, kod produktu lub dowolny identyfikator w Twoim zestawie danych.
- szukana_tablica: Zakres, w którym przeszukuje program Excel lookup_value Twoje. Zazwyczaj jest to pojedyncza kolumna lub wiersz zawierający Twoje kryteria wyszukiwania.
- tablica_zwrotna: Zakres zawierający wartości, które chcesz pobrać. Może to być pojedyncza kolumna, wiele kolumn, a nawet cała sekcja tabeli.
- if_not_found (opcjonalne): Niestandardowy tekst lub wartość wyświetlana w przypadku braku dopasowania. Eliminuje to irytujące błędy #N/A i pozwala na wyświetlanie komunikatów „Nie znaleziono” lub „Sprawdź numer części”.
- match_mode (opcjonalny): Steruje typem dopasowania. Użyj 0 dla dokładnego dopasowania (domyślnie), -1 dla następnego dokładnego lub mniejszego dopasowania, 1 dla następnego dokładnego lub większego dopasowania i 2 dla dopasowania wieloznacznego.
- tryb_wyszukiwania (opcjonalny): Określa kierunek wyszukiwania. Użyj wartości 1 dla wyszukiwania od pierwszego do ostatniego (domyślnie), -1 dla wyszukiwania od ostatniego do pierwszego i 2 dla wyszukiwania binarnego w posortowanych danych.
Weźmy na przykład arkusz kalkulacyjny do inwentaryzacji mechanicznej. Poniższa formuła wyszukuje numer części „BRG-002” w zakresie identyfikatorów części i zwraca odpowiednie dane. Jeśli część nie jest obecna, zamiast błędu wyświetla komunikat „Nie znaleziono części”.
=XLOOKUP("BRG-002", A:A, A:H, "Część nie została znaleziona")
Funkcja XLOOKUP umożliwia wyodrębnianie danych z różnych kolumn bez uciążliwych obliczeń kolumnowych, które występują w funkcji VLOOKUP, co czyni ją jedną z najważniejszych funkcji Funkcje programu Excel umożliwiające szybkie wyszukiwanie danych.
4. SUMPRODUCT
Elektrownia do obliczeń warunkowych
Funkcja SUMA.ILOCZYNÓW nie tylko dodaje liczby, ale także mnoży macierze i sumuje wyniki. Dzięki temu jest przydatna do złożonych obliczeń warunkowych, które wymagają wielu kolumn pomocniczych.
Ma on następującą formułę:
=SUMA.ILOCZYNÓW(tablica1, [tablica2], [tablica3], ...)
Tutaj, array1 Jest to pierwszy zakres wartości, który należy pomnożyć – zazwyczaj jest to główna kolumna danych, np. ilości lub koszty. array2 Jest to opcjonalny drugi zakres do mnożenia, który często zawiera kryteria lub logikę warunkową wykorzystującą operatory porównania.
Stają się one bardziej użyteczne, gdy używamy operatorów logicznych w tablicach. Na przykład, gdy wpisujemy warunki takie jak (dostawca="Siemens"), Excel konwertuje wyniki PRAWDA/FAŁSZ na 1/0, umożliwiając obliczenia.
Na przykład poniższy wzór oblicza całkowitą wartość zapasów części dostarczanych wyłącznie przez firmę Siemens. Wzór mnoży ilości przez koszty jednostkowe, ale tylko w wierszach, w których dostawca spełnia kryteria.
=SUMPRODUCT(D2:D100*H2:H100*(G2:G100="Siemens"))
Podobnie poniższy wzór pozwala obliczyć całkowity koszt zapasu łożysk w dobrej kondycji:
=SUMPRODUCT((C2:C100="Bearings")*(D2:D100>=15)*H2:H100)
Jednocześnie muszą obowiązywać dwa warunki – kategoria musi być „Łożyska”, a poziom zapasów musi wynosić 15 lub więcej sztuk, co pomaga nam identyfikować kategorie łożysk, dla których pokrycie zapasów jest wystarczające.
W przeciwieństwie do tradycyjnych funkcji SUMA z wieloma kryteriami funkcja SUMA.ILOCZYNÓW nie wymaga skomplikowanych struktur zagnieżdżonych, ponieważ obsługuje wiele warunków w jednej, czytelnej formule. Funkcje SUMA w Excelu, Podobnie jak funkcje SUMA.JEŻELI i SUMA.WARUNKÓW, doskonale nadają się do prostego sumowania warunkowego, ale funkcja SUMA.ILOCZYNÓW sprawdza się znakomicie, gdy trzeba pomnożyć wartości przed sumowaniem lub gdy trzeba obsługiwać bardziej złożone operacje logiczne.
3. FILTR
Ułatwia dynamiczną ekstrakcję danych
Funkcja FILTER wyodrębnia wiersze z zestawu danych na podstawie określonych przez Ciebie warunków. W przeciwieństwie do filtrowania ręcznego, ta funkcja generuje dynamiczne wyniki, które automatycznie aktualizują się po zmianie danych źródłowych. Składnia funkcji FILTER jest następująca:
=FILTR(tablica;włącz;[jeśli_pusty])
Oto, co kontroluje każde wejście:
- tablica (zakres): Pełny zakres danych, które chcesz filtrować. Obejmuje to wszystkie kolumny, które chcesz uwzględnić w wynikach, a nie tylko kolumnę kryteriów.
- włączać: Warunek logiczny określający, które wiersze mają zostać zwrócone – używa operatorów porównania, aby utworzyć tablice PRAWDA/FAŁSZ dla każdego wiersza.
- jeśli_puste (opcjonalne): Wyświetla niestandardowy komunikat, gdy żaden wiersz nie spełnia kryteriów. Zapobiega błędom #CALC! i wyświetla zrozumiały tekst, na przykład „Nie znaleziono pasujących wyników”.
Funkcja działa poprzez ocenę warunku dla każdego wiersza w zakresie. Gdy warunek zwróci wartość PRAWDA, cały wiersz pojawi się w przefiltrowanych wynikach. Oto przykład z arkusza kalkulacyjnego dotyczącego inwentaryzacji mechanicznej:
=FILTER(A2:H101, (C2:C101="Bearings")*(G2:G101="Timken"))
Ta formuła wyodrębnia wszystkie wiersze, w których zasób to „Timken”, a kategoria to „Łożyska”. Gwiazdka (*) tworzy warunek AND poprzez pomnożenie tablic logicznych przez siebie.
Gdy dodajesz nowe dane do zakresu źródłowego, Korzystanie z funkcji FILTR w programie Excel Jest to bardziej sensowne niż ręczne sortowanie i tabele tymczasowe, ponieważ wyniki filtrowania są automatycznie aktualizowane. Dzięki temu jest przydatne do tworzenia dynamicznych pulpitów nawigacyjnych i raportów.
2. NIECODZIENNCH
Wyodrębnij unikalne wartości bez duplikatów
Funkcja UNIQUE pobiera unikalne wartości z zakresu danych i automatycznie unika duplikatów. Ta funkcja jest ważna, jeśli chcesz tworzyć listy rozwijane, analizować kategorie danych i tworzyć raporty podsumowujące. Wzór wygląda następująco:
=UNIQUE(tablica, [wg_kolumny], [dokładnie_raz])
Oto jak działa każde wejście:
- tablica (zakres): Zakres zawierający dane, z których chcesz usunąć duplikaty — może to być pojedyncza kolumna, wiele kolumn lub cała sekcja tabeli.
- by_col (opcjonalnie): Wartość FALSE porównuje wiersze w celu ustalenia unikalności (domyślnie), a TRUE porównuje kolumny. Jednak w większości scenariuszy używane jest domyślne porównanie wierszy.
- dokładnie_raz (opcjonalnie): FALSE zwraca wszystkie unikatowe wartości, w tym te, które pojawiają się wiele razy (domyślnie), a TRUE zwraca tylko wartości, które pojawiają się dokładnie raz w zestawie danych.
Funkcja UNIQUE ocenia każdy wiersz lub wartość w tablicy i zwraca tylko pierwsze wystąpienie każdego unikatowego elementu. Kolejność jest zgodna z oryginalną sekwencją danych. Oto przykład:
=UNIQUE(G2:G22)
Ta formuła wyodrębnia wszystkie unikalne nazwy dostawców z kolumny „Dostawca G” i tworzy czytelną, zduplikowaną listę. Używam jej do tworzenia list rozwijanych dostawców lub raportów podsumowujących.
Można go również stosować w całej tabeli, jak pokazano poniżej:
=UNIKALNE(A2:F100)
Zwraca unikalne kombinacje we wszystkich kolumnach (od A do F), wyświetlając odrębne rekordy inwentarza. Jeśli dwie części mają identyczne wartości w każdej kolumnie, w wynikach pojawi się tylko jedna.
Podczas pracy z dużymi zbiorami danych, UNIQUE eliminuje żmudny proces ręcznego usuwania duplikatów. Dynamiczne wyniki są aktualizowane w miarę napływania nowych danych, a ponieważ UNIQUE tworzy macierze rozproszenia, takie podejście eliminuje problem zmiany rozmiaru tabel poprzez automatyczne skalowanie w celu uwzględnienia wszystkich unikatowych wartości. Używam go do utrzymywania przejrzystych list referencyjnych i budowania wiarygodnych zakresów walidacji danych.
1. SORTUJ i SORTUJ WEDŁUG
Zorganizuj swoje dane bez naruszania oryginału
Funkcje SORT i SORTBY dynamicznie porządkują dane, zachowując jednocześnie integralność źródła. SORT obsługuje podstawowe sortowanie według pozycji w kolumnie, natomiast SORTBY sortuje według wartości w różnych kolumnach, co zapewnia większą elastyczność w przypadku złożonego sortowania.
SORT używa następującej struktury:
=SORTUJ(tablica; [indeks_sortowania]; [kolejność_sortowania]; [według_kolumny])
Oto, co kontroluje każdy parametr:
- szyk: Zakres danych, który chcesz sortować — obejmuje wszystkie kolumny, które powinny pojawić się w sortowanych wynikach.
- sort_index (opcjonalny): Numer kolumny w tablicy, według której ma być sortowane. Użyj 1 dla pierwszej kolumny, 2 dla drugiej kolumny itd. (domyślnie 1).
- sort_order (opcjonalnie): Użyj wartości 1, aby uporządkować je w kolejności rosnącej (domyślnie) lub -1, aby uporządkować je w kolejności malejącej.
- by_col (opcjonalnie): FALSE, aby sortować według wierszy (domyślnie), TRUE, aby sortować według kolumn — w większości scenariuszy używane jest sortowanie wierszy.
Funkcja SORTBY ma następującą postać:
=SORTBY(tablica, według_tablicy1, [porządek_sortowania1], [według_tablicy2], [porządek_sortowania2], ...)
Transakcje spółki obejmują:
- szyk: Zakres danych do sortowania — podobnie jak funkcja SORT, zawiera wszystkie kolumny, które chcesz uwzględnić w wynikach.
- przez_tablicę1: Zakres zawierający wartości decydujące o kolejności sortowania — może to być dowolna kolumna, nawet spoza zakresu tablicy głównej.
- sort_order1 (opcjonalnie): 1 dla kolejności rosnącej (domyślnie), -1 dla kolejności malejącej.
- by_array2, sort_order2 (opcjonalne): Dodatkowe kryteria sortowania dla sortowania wielopoziomowego.
Przyjrzyjmy się przykładowi arkusza kalkulacyjnego dotyczącego inwentaryzacji mechanicznej. Funkcje te obsługują rzeczywiste scenariusze sortowania:
=SORTUJ(A2:H22, 4, -1)
Sortuje cały stan magazynowy według stanów magazynowych w kolejności malejącej, przy czym towary o najwyższym stanie magazynowym są wyświetlane jako pierwsze. Formuła sortuje według kolumny 4 (stany magazynowe), zachowując jednocześnie wszystkie relacje między wierszami.
Używam funkcji SORTUJ. Zamiast funkcji SORT możesz jej użyć, aby uzyskać lepszą kontrolę nad kryteriami sortowania i wieloma poziomami sortowania. Na przykład poniższa formuła sortuje najpierw alfabetycznie według kategorii, a następnie według poziomów zapasów od najwyższego do najniższego w każdej kategorii.
=SORTOWANIE WG(A2:H22, C2:C22, 1, D2:D22, -1)
Zorganizowane arkusze kalkulacyjne, lepsze wyniki
Formuły tablicowe eliminują bałagan kolumn pomocniczych i zagnieżdżonych funkcji, który utrudnia utrzymanie arkuszy kalkulacyjnych. Otrzymujesz pojedyncze formuły obsługujące wiele operacji, dzięki czemu skoroszyty stają się bardziej przejrzyste i profesjonalne.
Jedną z istotnych zalet są funkcje dynamiczne, w których wyniki są automatycznie aktualizowane po zmianie danych źródłowych. Eliminuje to konieczność ręcznej aktualizacji lub wprowadzania niepoprawnych formuł, zwiększając niezawodność arkuszy kalkulacyjnych w bieżącej analizie.
Biblioteka funkcji tablicowych Excela stale się rozwija, wykraczając poza te podstawowe narzędzia. Kiedy potrzebuję połączyć dane z wielu źródeł, używam funkcji VSTACK i HSTACK do łączenia zakresów. Razem funkcje te tworzą wydajne przepływy pracy przetwarzania danych, które byłyby niemożliwe do zrealizowania przy użyciu tradycyjnych formuł.
