Prawie każdy, kto korzysta z Excela, ma tę jedną funkcję, na której polegał od czasów studiów, ponieważ działa ona na tyle dobrze, że nie trzeba się jej uczyć. Dlatego funkcje WYSZUKAJ.PIONOWO i ZŁĄCZ.TEKSTY znajdziesz w zupełnie nowych plikach, nawet w 2026 roku. Rozumiem to, bo wciąż waham się… Porzucenie SUMY CZĘŚCIOWEJ na rzecz AGREGATÓW Po tym jak mnie wspierałeś przez długi czas.
Jednak Excel ewoluował i wiele formuł, których uczyliśmy się lata temu, jest teraz bardziej złożonych, mniej niezawodnych lub trudniejszych w utrzymaniu niż powinny. Jeśli Twoje formuły wyglądają tak samo jak kiedyś, a arkusze kalkulacyjne wydają się cięższe niż powinny, może to oznaczać, że potrzebujesz aktualizacji.
Szybkie linki
XLOOKUP całkowicie zastępuje funkcję WYSZUKAJ.PIONOWO i WYSZUKAJ.POZIOMO
Pojedyncza funkcja działająca w dowolnym kierunku
VLOOKUP i HLOOKUP to w zasadzie pierwszy zestaw funkcji wyszukiwania w Excelu. Działają, są znane i wszechobecne. Problem polega na tym, że są sztywne, co staje się coraz bardziej frustrujące wraz z rozrastaniem się arkuszy kalkulacyjnych. Coś tak prostego, jak wstawienie kolumny w środku tabeli, może zaburzyć wyniki, ponieważ obie funkcje opierają się na zakodowanych numerach indeksów kolumn lub wierszy.
=WYSZUKAJ.PIONOWO(wartość_szukana, tablica_tablicowa, nr_indeksu_kolumny, [zakres_wyszukiwania]) =WYSZUKAJ.POZIOMO(wartość_szukana, tablica_tablicowa, nr_indeksu_wiersza, [zakres_wyszukiwania])
Co więcej, funkcja WYSZUKAJ.PIONOWO nie może przeszukiwać lewej strony kolumny klucza, co zmusza do zmiany kolejności danych lub tworzenia kolumn pomocniczych, aby podstawowe wyszukiwanie działało. Funkcja WYSZUKAJ.PIONOWO rozwiązuje ten problem od razu. Zamiast wskazywać dużą tabelę i liczyć kolumny lub wiersze, wskazujesz programowi Excel, gdzie ma szukać i co ma zwrócić:
=XWYSZUKAJ(wyszukaj_wartość, wyszukana_tablica, zwrócona_tablica, [jeśli nie znaleziono], [tryb_dopasowania], [tryb_wyszukiwania])
Dzięki temu podejściu Twoje formuły nie przestaną działać, gdy ktoś doda kolumnę lub zmieni kolejność pól w tabeli. Funkcja XLOOKUP działa również od lewej do prawej, od prawej do lewej i od góry do dołu, co zapewnia większą elastyczność, gdy dane nie są umieszczone w tradycyjnej, przejrzystej tabeli wyszukiwania.
Kolejną istotną aktualizacją jest to, że funkcja XLOOKUP zawiera teraz argument if_not_found, dzięki czemu nie potrzebujesz już funkcji IFERROR, aby uniknąć nieestetycznych komunikatów #N/A. Te dwie składnie podkreślają różnicę:
=JEŻELI.BŁĄD(WYSZUKAJ.PIONOWO(B2; C2:E7; 4; PRAWDA)" ") =XWYSZUKAJ.PIONOWO(B2; C2:E7; D2:D7; "Nie znaleziono")
Pierwsza formuła wyszukuje komórkę B2 w pierwszej kolumnie zakresu C2:E7 i zwraca czwartą kolumnę, natomiast druga formuła wyszukuje komórkę B2 w zakresie C2:E7 i zwraca pasującą wartość z kolumny D2:D7. Funkcja XLOOKUP domyślnie używa dopasowań dokładnych, co zmniejsza ryzyko przypadkowego pobrania przybliżonych wyników z nieposortowanej listy, a także obsługuje brakujące wartości bez konieczności uwzględniania każdego wyszukiwania w funkcji JEŻELI.BŁĄD.
XMATCH to wydajniejsza wersja MATCH
Precyzyjna kontrola typów dopasowań i trendów wyszukiwania
Bardzo prawdopodobne, że nadal używasz MATCH dla pamięci mięśniowej. Zawsze świetnie sprawdzał się w znajdowaniu wartościowej pozycji w zakresie, zwłaszcza gdy MATCH jest połączony z INDEXEMProblem polega na tym, że funkcja MATCH jest łatwa do nadużycia. Jeśli zapomnisz określić dokładny typ dopasowania, Excel może wyświetlić najbliższy wynik na podstawie założeń dotyczących sortowania, które w rzeczywistości nie mają zastosowania do Twoich danych.
=DOPASUJ(wartość_wyszukiwania, tablica_wyszukiwania, [typ_dopasowania])
Ponieważ tego typu błąd często daje pozornie sensowny wynik, jego wykrycie i skorygowanie może być trudne. Dzięki funkcji XMATCH formuły domyślnie mają dokładne dopasowanie, co i tak jest pożądane w zdecydowanej większości przypadków.
=XMATCH(wartość_wyszukiwania, tablica_wyszukiwania, [tryb_dopasowania], [tryb_wyszukiwania])
Jeśli piszesz =PODAJ(25; A1:A3; 0) Z przyzwyczajenia pomocne jest przejście na =XMATCH(25, A1:A3, 1) Skorzystaj z tego ulepszenia. Oprócz bezpieczniejszych ustawień domyślnych, XMATCH pozwala również kontrolować kierunek wyszukiwania. Może przeszukiwać listę od końca, a nie tylko od początku, co jest szczególnie przydatne, gdy chcesz znaleźć ostatni wpis lub ostatnie wystąpienie wartości bez dodawania kolumn pomocniczych.
=XMATCH(E3, C3:C100, 0, -1)
Oczywiście, funkcja MATCH nadal nadaje się do użytku, ale na tym etapie byłoby to jak wybranie czegoś, co po prostu działa, gdy obok istnieje bardziej efektywna opcja.
Dynamiczne formuły FILTER, UNIQUE i SUM zastępują przełomowe rozwiązania SUMPRODUCT.
Łatwiejsze do odczytania, poprawienia i utrzymania w czasie
Korzystając z funkcji SUMA.ILOCZYNÓW, można obsługiwać obliczenia wielokryterialne, sumy ważone i logikę macierzową w ramach jednej formuły. Ta elastyczność sprawia, że od lat jest to popularne rozwiązanie alternatywne. Wadą jest to, że formuły SUMA.ILOCZYNÓW często wyglądają jak kod, co utrudnia ich odczytanie, debugowanie i utrzymanie w dłuższej perspektywie. Teraz, gdy… Natywna obsługa tablic dynamicznych w programie ExcelWiele rzeczy, do których używaliśmy funkcji SUMPRODUCT, nie wymaga już tak skomplikowanych i niejasnych wzorów.
Dzięki dynamicznym tablicom w nowoczesnym Excelu, standardowe funkcje, takie jak SUMA, mogą teraz bezpośrednio obsługiwać obliczenia tablicowe. Po połączeniu tych dwóch metod, nowsze podejście jest zazwyczaj bardziej przejrzyste i łatwiejsze do zrozumienia:
| Obliczenia | SUMPRODUCT | SUMA |
|---|---|---|
| proste dodawanie | =SUMA.ILOCZYNÓW($A$20:$A$50) | =SUMA($A$20:$A$50) |
| Klasyczna kombinacja ważona | =SUMA.ILOCZYNÓW(A2A5; C2:C5) | =SUMA(A2:A5 * C2:C5) |
| Agregacja wielokryterialna | =SUMPRODUCT((A2:A9=”East”)*(B2:B9=”Cherries”)*C2:C9) | =SUM((A2:A9=”East”)*(B2:B9=”Cherries”)*C2:C9) |
Co więcej, dzięki nowszym funkcjom, takim jak FILTER i UNIQUE, zamiast tworzyć jedną długą, trudną do odczytania formułę, można rozbić problem na mniejsze, bardziej czytelne kroki. Można filtrować interesujące dane, w razie potrzeby wyodrębnić unikalne wartości, a następnie podsumować wynik. Logika pozostaje ta sama, ale cel formuły staje się znacznie bardziej zrozumiały dla każdego, kto będzie musiał ją później odczytać lub zmodyfikować.
Na przykład, jeśli masz listę sprzedaży w kolumnie B i nazwy produktów w kolumnie A, a chcesz zebrać dane o sprzedaży konkretnego produktu, takiego jak „Jabłka”, możesz zapisać jedną z dwóch poniższych formuł:
=SUMPRODUCT((A2:A10="Apples")*(B2:B10)) =SUM(FILTER(B2:B10, A2:A10="Apples", 0))
W wersji SUMPRODUCT, Excel tworzy tablicę wartości PRAWDA i FAŁSZ, aby sprawdzić, czy produkt jest „Jabłkami”, konwertuje je na 1 i 0, a następnie mnoży przez dane sprzedaży. Wszystko, co nie jest „Jabłkiem”, jest konwertowane na zero, które jest pomijane w końcowej sumie. W drugiej formule, FILTER po prostu zwraca dane sprzedaży, gdzie produktem są „Jabłka” i dodaje SUMA do tej już mniejszej i bardziej przejrzystej listy. Wynik jest taki sam, ale drugie podejście znacznie ułatwia zrozumienie celu formuły na pierwszy rzut oka.
Funkcja SUMPRODUCT może okazać się wygodna, ponieważ jej struktury array1 i array2 można łatwo modyfikować podczas eksperymentów. Jednak w przypadku dużych zbiorów danych, zawierających setki tysięcy lub miliony wierszy, może być znacznie wolniejsza niż bardziej dostępne, nowoczesne alternatywy. Oprócz lepszej wydajności, nowsze funkcje oferują bardziej przejrzyste formuły, które są łatwiejsze do korekty i mniej onieśmielające dla kolejnej osoby otwierającej arkusz kalkulacyjny.
IFS i SWITCH eliminują zagnieżdżone formaty IF
Czystsza logika bez niekończących się nawiasów
Technicznie rzecz biorąc, zagnieżdżone instrukcje JEŻELI działają, ale szybko stają się koszmarem – trudne do odczytania i utrzymania, zwłaszcza jeśli nie zostały napisane samodzielnie. W przypadku metody IFS, zamiast zagnieżdżać instrukcje JEŻELI w instrukcjach JEŻELI w instrukcjach JEŻELI, warunki i wyniki są wymieniane w kolejności, dzięki czemu formuła wygląda bardziej logicznie: jeśli to, to tamto; jeśli to jest coś innego, to coś innego. Funkcja SWITCH idzie o krok dalej, porównując pojedynczą wartość z wieloma stałymi prawdopodobieństwami; jest to bardziej przejrzyste, czytelne i zawiera opcję domyślną, dzięki czemu nie trzeba dodawać dodatkowej obsługi błędów na końcu.
=JEŻELI(test1, wynik1, JEŻELI(test2, wynik2, JEŻELI(test3, wynik3, ...))) =JEŻELI(test_logiczny1, wartość_jeśli_prawda1, test_logiczny2, wartość_jeśli_prawda2, ...) =PRZEŁĄCZ(wyrażenie, wartość1, wynik1, wartość2, wynik2, ... [domyślne])
Na przykład, jeśli chcesz przypisać wyniki do wyników, możesz użyć zagnieżdżonych instrukcji IF, ale IFS wyraża tę samą logikę w sposób bardziej przejrzysty:
=JEŻELI(A1>90, „A”, JEŻELI(A1>80, „B”, JEŻELI(A1>70, „C”, „F”))) =JEŻELI(A1>90, „A”, A1>80, „B”, A1>70, „C”, PRAWDA, „F”)
Wartość TRUE na końcu formuły IFS działa jak warunek globalny, wskazując programowi Excel, co ma zwrócić, jeśli żaden z poprzednich warunków nie zostanie spełniony. W tym przypadku każdy wynik poniżej 70 spada do wartości „F” bez konieczności ponownego zagnieżdżania IF. Funkcja SWITCH obsługuje nieco inny wzorzec, taki jak przypisywanie kodów numerycznych do sekcji, i zawiera argument domyślny na końcu, więc nie wymaga rozwiązania TRUE.
=PRZEŁĄCZ(A1, 101, „Sprzedaż”, 102, „Marketing”, „Inne”)
Jeśli komórka A1 zawiera 101, Excel zwróci wartość „Sprzedaż”. Jeśli zawiera 102, Excel zwróci wartość „Marketing”. Jeśli zawiera cokolwiek innego, Excel zwróci wartość „Inne”. Logika pozostanie jasna, nawet gdy lista przypadków się wydłuży.
TEXTJOIN i CONCAT całkowicie zastępują CONCATENATE
Scalaj zakresy, a nie tylko pojedyncze komórki.
Funkcja CONCATENATE jest obecnie praktycznie eksponatem muzealnym. Nadal można ją spotkać w wielu arkuszach kalkulacyjnych, ale została oficjalnie zastąpiona, i to nie bez powodu. Nie działa ona dobrze z pełnymi zakresami, ponieważ trzeba ręcznie odwoływać się do każdej komórki i brakuje jej wbudowanego elementu rozdzielającego.
Jego współczesna alternatywa, CONCAT, działa w bardzo podobny sposób, ale dodaje obsługę odwołań do pełnej domeny:
=CONCAT(tekst1, ...)
Zamiast ręcznie wybierać każdą komórkę, możesz wpisać taką formułę i pozwolić programowi Excel zająć się resztą:
=ZŁĄCZ.T(B2:C8)
Jeśli chcesz określić separator, taki jak przecinek lub spacja, i nakazać programowi Excel ignorowanie pustych komórek, najlepszym narzędziem do tego zadania jest TEXTJOIN:
=TEXTJOIN(rozdzielacz, ignoruj_puste, tekst1, ...) =TEXTJOIN(", ", PRAWDA, B2:C8)Pierwszy argument to separator, którym może być dowolny inny separator, a drugi określa, czy Excel ma pomijać puste komórki. Jest to szczególnie przydatne podczas tworzenia czytelnych list, tworzenia zapytań SQL lub kompilowania tekstu sformatowanego z kolumny lub zakresu danych.
Czas porzucić stare nawyki związane z programem Excel
Dobra wiadomość jest taka, że żadna z tych aktualizacji nie wymaga od Ciebie nauki Excela od podstaw. W większości przypadków nowsze funkcje są w rzeczywistości prostsze w obsłudze niż te, które zastępują; po prostu przyzwyczajenie się do nich zajmuje trochę czasu.
Jeśli w tym tygodniu aktualizujesz tylko jeden nawyk, zacznij od funkcji, której używasz najczęściej i zastąp ją jej współczesnym odpowiednikiem. Następnie spróbuj przepisać mały fragment skoroszytu, używając tych pięciu podstawień, i przekonaj się, jak proste są Twoje formuły.
