Excel to świetne narzędzie do raportowania, analizowania, organizowania i automatyzacji danych. Funkcje programu Excel bardzo pomagają w pracy z danymi. Funkcje takie jak COUNTIFS, SUMIFS, VLOOKUP itp. są najpotężniejszymi i najczęściej używanymi funkcjami od momentu powstania w świecie Excela.
Chociaż funkcje dostępne w Excelu 2016 i starszych wystarczą do opracowania wszelkiego rodzaju obliczeń i automatyzacji, czasami formuły stają się trudne. Na przykład, jeśli nie znajdziesz maksymalnej wartości w niektórych warunkach, musisz użyć kilku sztuczek w starszej wersji programu Excel 2016. Tego rodzaju drobne, ale ważne rzeczy są rozwiązywane w Excelu 2019 i 365.
W Excel 2019 i 365 pojawiło się ponad 10 nowych funkcji, które zmniejszają ludzki wysiłek i złożoność formuł.
1. Funkcja MAXIFS
W programie Excel 2016 i starszym, jeśli chcesz uzyskać maksymalną wartość w zakresie, gdy jeden lub więcej warunków spełnia jeden lub więcej warunków, musisz użyć MAX z IF z kilkoma sztuczkami. Nie jest to trudne, ale dla niektórych czasochłonne i mylące.
Excel 2019 wprowadza nową funkcję o nazwie funkcja MAXIFS. Ta funkcja zwraca maksymalną wartość z tablicy, gdy wszystkie podane warunki są spełnione.
Składnia funkcji to:
| =MAXIFS(maksymalny_zakres;kryteria_zakres1,kryteria1,kryteria_zakres2,kryteria2… ) |
Max_zakres1: Jest to zakres liczbowy zawierający wartość maksymalną.
Kryteria_zakres1: Jest to zakres kryteriów, który chcesz filtrować przed uzyskaniem maksymalnej wartości.
Kryteria1: Są to kryteria lub filtr, które chcesz umieścić w zakresie kryteria_zakres przed uzyskaniem wartości Max.
Załóżmy, że musisz uzyskać maksymalne oceny z klasy 3, wtedy wzór będzie
| =MAKS.WARUNKI(znaczniki;klasa;3) |
Tutaj znaki to nazwany zakres, który zawiera znaki, a klasa to nazwany zakres, który zawiera klasę.
Przeczytaj szczegółowo o funkcji MAXIFS tutaj.
2. Funkcja minifów
Podobnie jak funkcja MAXIFS, funkcja MINIFS służy do uzyskania minimalnej wartości z podanego zakresu, gdy wszystkie podane warunki są spełnione.
Składnia funkcji to:
| =MINIFS(min_zakres;kryteria_zakres1,kryteria1,kryteria_zakres2,kryteria2… |
Min_zakres1: Jest to zakres liczbowy zawierający wartość minimalną.
Kryteria_zakres1: Jest to zakres kryteriów, który chcesz filtrować przed uzyskaniem minimalnej wartości.
Kryteria1: Są to kryteria lub filtr, które chcesz umieścić w zakresie kryteria_zakres przed uzyskaniem wartości minimalnej.
Załóżmy, że musisz uzyskać minimalne oceny z klasy 3, wtedy wzór będzie
| =MIN.WARUNKI(znaczniki;klasa;3) |
Tutaj „oznaczenia” to nazwany zakres zawierający znaki, a „klasa” to nazwany zakres, który zawiera klasę.
Przeczytaj szczegółowo o funkcji MAXIFS tutaj.
Aby znaleźć minimalną wartość z zakresu z warunkami w programie Excel 2016 i starszych, przeczytaj to.
3. Funkcja IFS
Ponieważ zagnieżdżony Ifs zajmuje szczególne miejsce w naszym codziennym życiu zawodowym, bardzo go lubimy. Ale dla niektórych nowych uczniów jest to skomplikowane. Zagnieżdżone if pozwalają nam sprawdzić wiele warunków i zwrócić inną wartość, gdy którykolwiek z warunków zostanie spełniony. Formuły stają się coraz bardziej złożone z coraz większą funkcją IF.
Excel 2019 i Excel 365 używają teraz funkcji IFS. Może sprawdzać wiele warunków i zwracać różne wartości dla każdego warunku.
Składnia funkcji IFS:
| =IFS (warunek1, Wartość1_Jeżeli_Prawda, [Warunek2, Wartość2_Jeżeli_Prawda],…) |
Warunek 1:Pierwszy warunek.
Value1_If_True: Wartość, jeśli pierwszy warunek jest prawdziwy.
[Warunek2]: Jest to opcjonalne. Drugi warunek, jeśli masz.
[Value1_If_True]: Wartość, jeśli drugi warunek jest prawdziwy.
Możesz mieć dowolną liczbę kombinacji warunków i wartości. Jest limit, ale nigdy nie będziesz musiał go osiągnąć.
Powiedzmy, że musisz wystawić uczniom oceny za pomocą ocen. W przypadku ocen powyżej 80, ocena A, B dla więcej niż 60, C dla więcej niż 40 i F dla mniej lub równej 40.
| =IFS (A1>80, "A", A1>60, "B",A1>40, "C",A1<=40, "F") |
Szczegółowe wyjaśnienie funkcji IFS można znaleźć tutaj.
4. Funkcja PRZEŁĄCZNIK
Funkcja switch zwraca różne wartości w zależności od wyników jednego wyrażenia. Brzmi jak IFS? To jest rodzaj. W rzeczywistości ta funkcja służy do zastępowania innego rodzaju zagnieżdżonych formuł IF.
W przeciwieństwie do funkcji IFS, która zwraca wartości na podstawie TRUE, FALSE; funkcja SWITCH zwraca wartości na podstawie WARTOŚCI zwracanych przez wyrażenie.
| =PRZEŁĄCZ (wyrażenie, wartość1,wynik1, [domyślna lub wartość2,wynik2],…) |
Wyrażenie: Może to być dowolne prawidłowe wyrażenie, które zwraca pewne wartości. Odwołanie do komórki, formuła lub wartość statyczna.
Wartość1,wynik1: Wartość i wynik są sparowane. Jeśli wartość zwrócona przezwyrażenie to wartość1 to zostanie zwrócony wynik1.
[Domyślna lub wartość2,wynik2]: Jeśli chcesz zwrócić jakąś domyślną wartość, zdefiniuj ją tutaj. W przeciwnym razie zdefiniuj wartość2 i wynik2. Jest to opcjonalne.
Na przykład, jeśli masz formułę, która zwraca imiona zwierząt. Teraz, w zależności od zwróconej nazwy zwierzęcia, chcesz zwrócić charakterystyczny dźwięk tego zwierzęcia.
| =PRZEŁĄCZ (A1, „Pies”, „Bow Wow”, „Kot”, „Miau”, „Mówi”) |
Opisałem tutaj szczegółowo funkcję SWITCH.
5. Funkcja FILTRA
Funkcja FILTER służy do filtrowania danych na podstawie pewnych kryteriów. Wykorzystaliśmy opcję filtrowania z zakładki głównej w programie Excel. Funkcja FILTER działa tak samo jak opcja filtrowania. Po prostu zwraca przefiltrowane dane za pomocą funkcji. Te przefiltrowane dane mogą służyć jako źródło danych dla innych formuł.
Składnia funkcji FILTER to:
| =FILTR(tablica;uwzględnij;[jeśli_pusty]) |
Szyk: To jest tablica, którą chcesz filtrować. Może być jednowymiarowy lub dwuwymiarowy.
Włączać:Jest to filtr, który chcesz umieścić na tablicy. Podobnie, kolory="czerwony".
[jeśli_pusty]:Jest to opcjonalne. Zdefiniuj dowolny tekst lub wyrażenie, jeśli filtr nic nie zwraca.
Poniższy wzór zwraca wszystkie owoce, których kolor jest czerwony.
| =FILTR(owoce;kolor="czerwony";"nie znaleziono owoców") |
Tutaj owoce i kolor są nazwanymi zakresami, które zawierają odpowiednio nazwy owoców i ich kolorów.
Możesz przeczytać szczegółowo o funkcji FILTER tutaj.
6. Funkcja sortowania
W programie Excel 2016 i starszym bardzo trudno było uzyskać posortowaną tablicę za pomocą formuły. Ten proces jest uproszczony w Excel 2019 i 365.
Excel 2019 wprowadza funkcję SORTUJ. Funkcja SORTUJ sortuje daną tablicę w porządku rosnącym lub malejącym według podanej kolumny/wiersza.
Składnia funkcji SORTOWANIE to:
| =SORTUJ(tablica;[indeks_sortowania];[kolejność_sortowania];[wg_kolumny]) |
Szyk:Jest to odwołanie do tablicy lub zakresu, który chcesz posortować.
[indeks_sortowania]:Numer kolumny w dwuwymiarowej tablicy, według której chcesz posortować zakres. Domyślnie jest to 1.
[sortowanie]:Kolejność, według której chcesz posortować tablicę. W górę wynosi 1, a w dół -1. Domyślnie jest to 1.
[by_col]:Ustaw True(1), jeśli chcesz posortować tablicę poziomą. Domyślnie jest to False(0) dla danych pionowych.
Powiedzmy, że chcesz posortować wartości w zakresie A2:A11 rosnąco. wtedy formuła będzie.
| =SORTUJ(A2:A11) |
Szczegółowo wyjaśniłem tutaj funkcję SORT.
7. Funkcja SORTUJ
Funkcja SORTBY jest podobna do funkcji SORT. Jedyna różnica polega na tym, że tablica sortująca nie musi być częścią tablicy sortowanej w funkcji SORTBY.
| =SORTUJ(tablica;sortowanie_tablica1;[kolejność];…) |
Szyk:To jest tablica, którą chcesz posortować.
Sorting_array1:To jest tablica, według której chcesz posortować tablicę. Wymiar tej tablicy powinien być zgodny z szyk.
[zamówienie]:Opcjonalny. Ustaw ją na -1, jeśli chcesz, aby kolejność była malejąca. Domyślnie jest rosnąco(1).
Powiedzmy, że chcesz posortować zakres A2:A11 według zakresu B2:B11, w kolejności malejącej. Wtedy formuła w Excelu 2019 lub 365 będzie wyglądać tak:
| =SORTUJ.BY(A2:A11;B2:B11;-1) |
Szczegółowo wyjaśniłem tutaj funkcję SORTBY.
8. UNIKALNA funkcja
W programie Excel 2016 i starszym użyliśmy wielu funkcji w połączeniu, aby uzyskać wszystkie unikalne wartości z podanej listy. Zastosowana formuła jest dość złożona i trudna do zrozumienia.
Excel 2019 i 365 wprowadzają jedną prostą funkcję UNIQUE, która zwraca wszystkie unikalne wartości z danej tablicy.
Składnia funkcji UNIQUE to:
| =UNIKALNE(tablica;[wg_kolumny];[dokładnie_raz]) |
Szyk: Tablica, z której chcesz wyodrębnić unikalne wartości:
[by_col]: Ustaw TRUE(1), jeśli tablica jest pozioma. Domyślnie jest to FALSE dla danych pionowych.
[dokładnie_raz]: ustaw ją na TRUE(1), jeśli chcesz wyodrębnić wartości, które występują w tablicy tylko raz. Domyślnie jest to FALSE(0), aby wyodrębnić wszystkie unikalne wartości.
Powiedzmy, że chcę uzyskać tylko jedno wystąpienie każdej wartości z zakresu A2:A11, wtedy formuła będzie wyglądać tak:
| =UNIKALNE(A2:A11) |
Aby przeczytać szczegółowo o funkcji UNIQUE, kliknij tutaj.
9. Funkcja SEKWENCJI
Aby uzyskać ciąg liczb w programie Excel 2016 i starszym, używamy kombinacji funkcji. Rozwiązanie działa, ale jest złożone.
Excel 2019 i 365 udostępnia rozwiązanie w postaci funkcji SEKWENCJA. Funkcja sekwencji po prostu zwraca serię liczby.
Składnia funkcji SEKWENCJA to:
| =SEKWENCJA(wiersze;[kolumny];[początek];[krok]) |
Wydziwianie:Liczba wierszy, do których chcesz rozlać sekwencję.
[kolumna]:Liczba kolumn, do których chcesz rozlać sekwencję. Liczby wypełnią najpierw kolumny, a następnie wiersze. Kolumna jest opcjonalna. Domyślnie jest to 1.
[początek]:Opcjonalny. Numer początkowy sekwencji. Domyślnie jest to 1.
[krok]:To numer przyrostu dla następnej liczby. Domyślnie jest to 1.
Prostym przykładem jest uzyskanie serii od 1 do 10. Wzór będzie następujący:
| =SEKWENCJA(10) |
Aby szczegółowo zrozumieć funkcję SEKWENCJA w programie Excel 365, przeczytaj to.
10. Funkcja RANDARRAY
Jest to kolejna dynamiczna formuła tablicowa, która zwraca tablicę liczb losowych. Jest to połączenie funkcji RAND i RANDBETWEEN. Możesz uzyskać ułamkowe liczby losowe lub liczby całkowite. Możesz określić liczbę liczb losowych, które chcesz. Nawet wiersze i kolumny, w których chcesz rozmieścić te liczby.
Składnia funkcji RANDARRAY to:
| =KOLUMNA TABLICA([wiersze];[kolumny];[min];[maks];[liczba całkowita]) |
Wszystkie argumenty w tej funkcji są opcjonalne. Domyślnie działa jako funkcja RAND.
[wydziwianie]:Liczba liczb, które chcesz w pionie (liczba wierszy, które chcesz wypełnić).
[kolumny]:Liczba liczb, które chcesz w poziomie (liczba kolumn, które chcesz wypełnić).
[min]:Numer początkowy lub minimalna wartość liczb losowych.
[maks.]:Maksymalny zakres liczby.
[liczba całkowita]:Ustaw to prawda, jeśli chcesz, aby liczby losowe były liczbami całkowitymi. Domyślnie jest fałszywy i zwraca ułamkowe liczby losowe.
Poniższa funkcja zwróci pięć losowych liczb ułamkowych w wierszu:
| =TABLICA LOSOWA(5) |
Przeczytaj szczegółowo o funkcji RANDARRAY tutaj.
11. Funkcja CONCAT
W programie Excel 2016 i starszym nie jest łatwo połączyć więcej niż jedną komórkę lub zakres przy użyciu jednej formuły.
Excel 2019 i 365 problem rozwiązuje funkcja CONCAT. Funkcja może przyjmować wiele komórek, zakresów jako argumenty.
Składnia funkcji CONCAT to:
| =CONCAT(tekst1,[tekst2];…) |
Tekst 1 : Tekst1 może być dowolnym tekstem lub zakresem, który chcesz połączyć.
[tekst2]: Jest to opcjonalne. Może to być również dowolny tekst lub zakres.
Załóżmy, że jeśli chcesz połączyć każdą komórkę z zakresu A2:A11, formuła będzie wyglądać tak:
| =ZŁĄCZ(A2:A11) |
Aby poznać szczegółowo funkcję CONCAT, kliknij tutaj.
12. Funkcja TEXTJOIN
Powyższa funkcja łączy wszystkie komórki w zakresie, ale nie łączy komórek z określonym ogranicznikiem. Powiedzmy, że jeśli przygotowujesz plik w formacie CSV, będziesz musiał połączyć komórki przecinkiem. W takim przypadku funkcja CONCATENATE i CONCAT zakończą się niepowodzeniem.
Tutaj funkcja TEXTJOIN działa cudownie i łączy podane teksty z podanym ogranicznikiem.
| =TEKSTJOIN(ogranicznik, ignoruj_puste_komórki;tekst1,[tekst2];…) |
Ogranicznik:Jest to separator, którego chcesz użyć jako separatora pomiędzy poszczególnymi tekstami. Może to być przecinek (,), średnik (;) lub cokolwiek, nawet nic.
Ignoruj_puste_komórki:To jest zmienna binarna. Ustaw ją na TRUE, jeśli chcesz ignorować puste komórki w zakresach, w przeciwnym razie ustaw na FALSE, aby uwzględnić puste komórki.
Tekst 1:To jest tekst, do którego chcesz dołączyć. Mogą to być pojedyncze teksty, komórki lub całe zakresy.
Powiedzmy, że chcę połączyć zakres A2:A11 z przecinkiem, ignorując puste komórki.
| =POŁĄCZTEKST(",",1,1,A2:A11) |
Aby szczegółowo zrozumieć tę funkcję, kliknij tutaj.
Ten artykuł był tylko wstępem do nowej funkcji Excel 365 i 2019. Funkcje te szczegółowo wyjaśniłem w osobnych artykułach. Możesz kliknąć łącza dostępne w każdej funkcji w artykule, aby w pełni zrozumieć tę funkcję. Istnieją inne funkcje, takie jak XLOOKUP, które nie zostały jeszcze wydane.
Jeśli masz jakiekolwiek wątpliwości dotyczące tematów Excela lub VBA, zapytaj w sekcji komentarzy poniżej. Powiedz nam, jak możemy się poprawić. Doceniamy Twoją sugestię i pragniemy usłyszeć od Ciebie.
Utwórz funkcję VBA, aby zwrócić tablicę | Aby zwrócić tablicę z funkcji zdefiniowanej przez użytkownika, musimy ją zadeklarować, gdy nazywamy UDF.
Tablice w programie Excel Formuła|Dowiedz się, jakie tablice znajdują się w programie Excel.
Jak utworzyć funkcję zdefiniowaną przez użytkownika za pomocą VBA | Dowiedz się, jak tworzyć funkcje zdefiniowane przez użytkownika w programie Excel
Korzystanie z funkcji zdefiniowanej przez użytkownika (UDF) z innego skoroszytu przy użyciu VBA w programie Microsoft Excel | Użyj funkcji zdefiniowanej przez użytkownika w innym skoroszycie programu Excel
Zwróć wartości błędów z funkcji zdefiniowanych przez użytkownika za pomocą VBA w programie Microsoft Excel | Dowiedz się, jak zwrócić wartości błędów z funkcji zdefiniowanej przez użytkownika
Popularne artykuły:
Podziel arkusz Excela na wiele plików na podstawie kolumny za pomocą VBA | Ten kod VBA dzieli arkusz Excela na podstawie unikalnych wartości w określonej kolumnie. Pobierz plik roboczy.
Wyłącz komunikaty ostrzegawcze za pomocą VBA w programie Microsoft Excel 2016 | Aby wyłączyć komunikaty ostrzegawcze, które przerywają działający kod VBA, używamy klasy Application.
Dodaj i zapisz nowy skoroszyt za pomocą VBA w programie Microsoft Excel 2016 | Do dodawania i zapisywania skoroszytów za pomocą VBA używamy klasy Workbooks. Workbooks.Add łatwo dodaje nowy skoroszyt, jednak…
