Ściągawka formuł programu Excel: zaawansowany przewodnik

A więc zapoznałeś się z podstawami, poradziłeś sobie z zadaniami średniozaawansowanymi i teraz jesteś gotowy do zmagania się z wielkimi działami — zaawansowanymi formułami programu Excel! Excel ma wiele zaawansowanych funkcji i formuł do skomplikowanych obliczeń, więc warto mieć przewodnik, który wskazuje te, których potrzebujesz.

Ta ściągawka formuł programu Excel obejmuje zaawansowane formuły prognozowania, analizy statystyczne, funkcje manipulacji danymi, obsługę błędów i nie tylko.

To odniesienie wyposaży Cię w wiedzę na temat korzystania z tych zaawansowanych funkcji. Każdej formule towarzyszą jasne wyjaśnienia, składnia i praktyczne przykłady, które pomogą średniozaawansowanym użytkownikom programu Excel stać się zaawansowanymi zaawansowanymi użytkownikami.

Pobierz i wydrukuj ściągawkę i miej ją pod ręką.

Ok, zaczynajmy.

Excel-formuły-oszustwo-arkusz-zaawansowane-1 Pobierz

Najpierw przejdźmy do formuł tablicowych.

Spis treści

• Formuły tablicowe
  • 1. UNIKALNA funkcja
  • 2. Funkcja SORTUJ
  • 3. FILTR Funkcja
• Randomizacja funkcji programu Excel
  • 1. LOSOWA funkcja
  • 2. TABLICA LOSOWA
• Zaawansowane formuły prognozowania w programie Microsoft Excel
  • 1. PROGNOZA.ETS Funkcja
  • 2. Funkcja TRENDU
• Zaawansowane wzory statystyczne
  • 1. Funkcja PERCENTYL
  • 2. Funkcja KWARTYLOWA
• Zaawansowana analiza danych i formuły manipulacji
  • 1. Funkcja TRANSPOZYCJI
  • 2. Funkcja CZĘSTOTLIWOŚĆ
  • 3. POBIERZ DANE Funkcja
• Zaawansowana obsługa błędów
• Zaawansowane formuły wyszukiwania
  • 1. Funkcja XLOOKUP
  • 2. Funkcja XMATCH
• Końcowe przemyślenia

Formuły tablicowe

Nasza ściągawka dla początkujących pokazuje, jak ręcznie sortować i filtrować dane. Zaawansowani użytkownicy robią to programowo za pomocą formuł tablicowych.

Formuły tablicowe umożliwiają jednoczesne wykonywanie obliczeń na wielu komórkach. Są to trzy kluczowe funkcje:

1. UNIKALNY

2. SORTOWAĆ

3. FILTR

Niektóre z tych funkcji są dostępne tylko w najnowszych wersjach programu Microsoft Excel.

1. UNIKALNA funkcja

Funkcja UNIQUE akceptuje zakres i zwraca listę unikalnych wartości.

Załóżmy, że masz dane dotyczące sprzedaży artykułów odzieżowych. Aby znaleźć unikatowe pozycje w kolumnie A, użyj następującej formuły:

=UNIKALNE(A2:A6)

2. Funkcja SORTUJ

Funkcja SORTUJ sortuje zawartość zakresu. Składnia to:

SORT(tablica; [indeks_sortowania]; [kolejność_sortowania]; [wg_kolumny])

• tablica : zakres wartości do sortowania.

• sort_index : kolumna do sortowania (domyślnie 1)

• sort_order : 1 dla rosnącej (domyślnie) lub 2 dla malejącej).

• by_col : PRAWDA, aby posortować według kolumny (domyślnie) lub FALSE, aby posortować według wiersza.

Ostatnie trzy argumenty są opcjonalne, a wartości domyślne są zwykle takie, jakie chcesz.

Aby posortować przykładowe dane według pierwszej kolumny, użyj następującej formuły:

=SORTUJ(A2:C6)

3. FILTR Funkcja

Funkcja FILTR umożliwia filtrowanie zakresu według określonego warunku. Oto składnia:

=FILTRUJ(tablica; uwzględnij; [jeśli_puste])

• array : zakres do filtrowania.

• zawierać : warunek określający, które wartości mają być filtrowane.

• if_empty : określa, co ma zostać zwrócone, jeśli żadna wartość nie spełnia kryteriów filtrowania (wartość domyślna to „”).

Załóżmy, że chcesz przefiltrować wiersze w przykładowych danych, aby pokazać tylko tam, gdzie wartość sprzedaży jest większa niż 15 USD. Użyj tej formuły:

=FILTR(A2:C6; C2:C6>15)

Randomizacja funkcji programu Excel

Nasz pośredni kod pokazuje, jak korzystać z funkcji LOS, która generuje losową liczbę z przedziału od 0 do 1.

Zaawansowani użytkownicy programu Excel wiedzą, jak używać funkcji losowania do szybkiego generowania przykładowych danych.

1. LOSOWA funkcja

Funkcja RANDBETWEEN jest bardziej elastyczna niż LOS, ponieważ możesz określić dolną i górną liczbę jako coś innego niż 0 i 1.

Aby wygenerować dane zawierające liczby od 1 do 100, wprowadź następującą formułę do komórki A1:

=LOSOWEPOMIĘDZY(1; 100)

Następnie skopiuj komórkę do dowolnej liczby wierszy i kolumn. Tworzenie siatki losowych liczb zajmuje kilka sekund:

2. TABLICA LOSOWA

Być może myślisz, że dobrze byłoby uniknąć ręcznego kopiowania funkcji RANDBETWEEN. Aby uzyskać superzaawansowaną funkcję, możesz użyć nowej funkcji TABLICA LOSOWA w najnowszej wersji programu Microsoft Excel.

Składnia to:

LOSOWATABLICA([wiersze], [kolumny], [min], [maks.], [liczba całkowita])

• wiersze : liczba wierszy

• kolumny : liczba kolumn

• min : najniższa liczba

• maks : najwyższa liczba

• liczba całkowita : domyślnie PRAWDA, w przeciwnym razie używane są liczby dziesiętne.

Aby wygenerować losowe liczby z przedziału od 1 do 100 w sześciu wierszach i dziewięciu kolumnach, użyj następującej formuły:

=LOSOWATABLICA(6; 9; 1; 100; PRAWDA)

Zaawansowane formuły prognozowania w programie Microsoft Excel

Funkcje prognozowania programu Excel służą do prognozowania przyszłych wartości na podstawie istniejących trendów danych. Funkcje te pomagają identyfikować wzorce i trendy projektowe na podstawie Twoich danych.

1. PROGNOZA.ETS Funkcja

Starsza funkcja PROGNOZA została zastąpiona zestawem nowszych funkcji w programie Excel 2016.

Funkcję wybierasz na podstawie określonego modelu prognozowania, który chcesz. Na przykład funkcja PROGNOZA.ETS używa algorytmu Wygładzanie wykładnicze .

Składnia to:

PROGNOZA.ETS(data_docelowa; wartości; oś czasu)

• target_date : data, dla której chcesz obliczyć wartość.

• wartości : dane historyczne.

• oś czasu : zakres dat

Załóżmy, że masz daty od 1 do 5 stycznia w kolumnie A i kwoty sprzedaży w kolumnie B. Ta formuła przewiduje następną kwotę sprzedaży:

=PROGNOZA.ETS(„6 stycznia 2023”, B2:B6, A2:A6)

2. Funkcja TRENDU

Funkcja TREND wyświetla zestaw wartości na podstawie metody najmniejszych kwadratów. Zwraca tablicę. Składnia to:

TREND(znane_y; [znane_x]; [nowe_x]; [stała])

• znane_y : zakres wartości y

• znany_x : zakres wartości x

• nowy_x : zakres obliczonych wartości

Często znane_y to punkty danych, a znane_x to daty.

Korzystając z tych samych danych, co w poprzednim przykładzie, możesz wprowadzić poniższą formułę do komórki C2. Zostanie wygenerowany zestaw wartości.

=TREND(B2:B6; A2:A6)

Zaawansowane wzory statystyczne

Zaawansowane funkcje statystyczne obejmują obliczanie percentyli i kwartyli. Niektóre funkcje matematyczne są dostępne w celu zapewnienia zgodności z poprzednimi wersjami, ale zaleca się korzystanie z najbardziej aktualnych wersji.

1. Funkcja PERCENTYL

Ta funkcja oblicza procent punktów danych, które spadają poniżej określonej wartości. Składnia to:

PERCENTYL.ZAMK(tablica; k)

• tablica : zakres komórek

• k : percentyl od 0 do 1

Załóżmy, że chcesz obliczyć 70. percentyl danych w kolumnie B. Użyj tej formuły:

=PERCENTYL.PRZEKR.(B2:B6; 0,7)

2. Funkcja KWARTYLOWA

Ta funkcja jest odmianą funkcji percentyla, ale wykorzystuje ćwiartki do dzielenia danych. Oto składnia:

QUARTIL.INC(tablica; kwarta)

• tablica : zakres danych

• kwarta : 1 dla 25. percentyla, 2 dla 50., 3 dla 75. i 4 dla maksimum.

Poniższy wzór obliczy pierwszy kwartyl danych w kolumnie A.

=KWARTYL.ZAMK(A2:A5; 1)

Zaawansowana analiza danych i formuły manipulacji

Kilka zaawansowanych funkcji pozwala zmieniać format danych, analizować rozkłady częstotliwości i wyodrębniać dane z tabel przestawnych.

1. TRANSPONOWAĆ

2. CZĘSTOTLIWOŚĆ

3. POBIERZ DANE

1. Funkcja TRANSPOZYCJI

Czasami chcesz przenieść dane z wierszy do kolumn i odwrotnie. Możesz to zrobić ręcznie lub zamiast tego użyć funkcji TRANSPONUJ.

Załóżmy, że w komórkach A2, A3 i A4 masz pozycje „Koszulka”, „Bluza z kapturem” i „Dżinsy”. Chcesz przekształcić je w nagłówki kolumn. Ta funkcja zwraca wartości w jednym wierszu:

=TRANSPONUJ(A2:A4)

2. Funkcja CZĘSTOTLIWOŚĆ

Ta funkcja oblicza rozkład częstości wartości w zbiorze danych. Oto składnia:

CZĘSTOTLIWOŚĆ(tablica_danych; tablica_pojemników)

• data_array : zakres wartości.

• bins_array : interwały do ​​użycia.

Załóżmy, że masz dane sprzedaży w kolumnie B i chcesz przeanalizować rozkład częstotliwości wartości na podstawie ilości:

• poniżej 20.

• od 20 do 80.

• powyżej 80.

Reprezentuje to trzy pojemniki i można je obliczyć za pomocą następującego wzoru:

=CZĘSTOTLIWOŚĆ(A2:A6;{20;80})

Aby uzyskać więcej informacji na temat rozkładów częstotliwości w programie Excel, obejrzyj ten film:

3. POBIERZ DANE Funkcja

Ta funkcja umożliwia wyodrębnienie podsumowanych informacji z tabel przestawnych. Oto składnia:

POBIERZDANE(pole_danych, tabela_przestawna, [pole1, element1], [pole2, element2], …)

• pole_danych : pole danych lub wartość, którą chcesz pobrać z tabeli przestawnej.

• tabela_przestawna : odwołanie do tabeli przestawnej.

• field1, item1 itd. : pary pole/element do filtrowania.

Załóżmy, że masz tabelę przestawną opartą na kolorze sprzedawanych przedmiotów. Aby wyodrębnić sprzedaż dla czerwonych przedmiotów, użyj następującej formuły:

=POBIERZDANE("Sprzedaż", A1, "Kolor", "Czerwony")

Zaawansowana obsługa błędów

Nawet najbardziej podstawowe formuły programu Excel mogą powodować błędy. Użytkownicy średniozaawansowani powinni wiedzieć, jak używać ISERROR do obsługi błędów. Zaawansowani użytkownicy powinni również zapoznać się z funkcją ERROR.TYPE służącą do identyfikacji błędów.

Funkcja BŁĄD.TYP pomaga zidentyfikować konkretny typ błędu w komórce lub formule.

Zwraca wartość liczbową odpowiadającą różnym typom błędów, takim jak #N/D, #VALUE!, #REF! i więcej.

Załóżmy, że w komórce A1 wystąpił błąd i chcesz określić jego typ błędu. Poniższa formuła zwróci liczbę odpowiadającą konkretnemu błędowi:

=TYP.BŁĘDU(A1)

Możesz połączyć to z wieloma funkcjami, aby reagować w różny sposób w zależności od rodzaju błędu. Oto najczęstsze błędy i ich wartości:

1. #ZERO! (nie znaleziono wspólnej komórki w zakresie)

2. #DZIEL/0! ( dzielenie przez zero lub pusta komórka)

3. #WARTOŚĆ! (nieodpowiedni typ danych lub argument w formule)

4. #REF! (komórka, do której następuje odwołanie, została usunięta lub występuje odwołanie cykliczne )

5. #NAZWA? (Excel nie rozpoznaje funkcji lub zakresu)

6. #LICZBA! (nieprawidłowa wartość liczbowa)

7. #N/D (nie można znaleźć wartości)

Załóżmy, że chcesz obsłużyć trzy określone typy błędów. Użyj tej formuły, aby wyświetlić określony komunikat o błędzie na podstawie typu:

=JEŻELI(CZY.BŁĄD(C2), JEŻELI(TYP.BŁĘDU(C2)=2, „Błąd dzielenia przez zero”, JEŻELI(TYP.BŁĘDU(C2)=3, „Błąd nieprawidłowej wartości”, JEŻELI(TYP.BŁĘDU(C2) )=7, „Błąd braku wartości”, „Inny błąd”))), C2)

Zaawansowane formuły wyszukiwania

Nasze ściągawki dla początkujących i średniozaawansowanych obejmowały wybrane funkcje wyszukiwania . Oto kilka zaawansowanych opcji:

1. XWYSZUKAJ

2. XMATCH

1. Funkcja XLOOKUP

Ta funkcja wyszukiwania umożliwia wyszukanie wartości w zakresie i zwrócenie odpowiedniej wartości z innej kolumny lub zakresu.

Oferuje większą wszechstronność niż prostsze funkcje wyszukiwania, takie jak WYSZUKAJ.PIONOWO . Oto składnia:

XLOOKUP(przeszukiwana_wartość, szukana_tablica, zwracana_tablica, [tryb_dopasowania], [tryb_wyszukiwania], [jeżeli_nie znaleziono])

• szukana_wartość : wartość, którą chcesz wyszukać.

• lookup_array : zakres wyszukiwania.

• tablica_powrotu : zakres, w którym zostanie wyświetlona odpowiednia wartość.

• match_mode : dopasowanie dokładne (0), następne mniejsze (1), następne większe (-1) lub dopasowanie wieloznaczne (2).

• search_mode : -1 od góry do dołu, 1 od dołu do góry lub 2 dla wyszukiwania binarnego.

• if_not_found : ustawia wartość, która ma zostać zwrócona, jeśli nie zostanie znalezione dopasowanie.

Załóżmy, że chcesz przeszukać zakres danych pod kątem pierwszego wystąpienia elementu odzieży i zwrócić kwoty sprzedaży. Ta formuła wyszuka tekst „Bluza” i zwróci wartość w sąsiedniej komórce, jeśli zostanie znaleziona:

=XLOOKUP("Bluza"; A2:A6; B2:B6)

2. Funkcja XMATCH

Ta funkcja umożliwia znalezienie pozycji określonej wartości w zakresie lub tablicy. Oto składnia:

XMATCH(wyszukiwana_wartość, przeszukiwana_tablica, [typ_dopasowania], [tryb_wyszukiwania])

• szukana_wartość : wartość, którą chcesz znaleźć.

• lookup_array : Zakres, który chcesz przeszukać.

• typ_dopasowania : dopasowanie dokładne (0), następne najmniejsze (-1), następne największe (1).

• search_mode : wyszukiwanie binarne (1) lub wyszukiwanie liniowe (2).

Załóżmy, że chcesz znaleźć pierwsze wystąpienie żółtego elementu w zakresie w kolumnie B. Użyj tej formuły:

=XMATCH(„Żółty”; B2:B6; 0)

Końcowe przemyślenia

Ta ściągawka obejmuje szeroki zakres funkcji, od analizy statystycznej, formuł wyszukiwania, technik manipulacji danymi i strategii obsługi błędów.

Podane przykłady i wyjaśnienia pomagają rozwikłać tajemnice tych zaawansowanych formuł, czyniąc je dostępnymi nawet dla osób z ograniczonym doświadczeniem.

Gdy zaczniesz włączać je do swoich zadań w programie Excel, jesteś na najlepszej drodze do podniesienia swoich umiejętności obsługi programu Excel do poziomu zaawansowanego.

Pamiętaj jednak, że ta ściągawka to tylko wierzchołek góry lodowej. Naprawdę niesamowite rzeczy dzieją się, gdy stajesz się kreatywny, mieszasz i dopasowujesz te formuły oraz dostosowujesz je, aby sprostać Twoim wyjątkowym wyzwaniom. Excel jest jak płótno, a te formuły to Twoja paleta — śmiało, maluj swoje arcydzieło!