Zaawansowane formuły programu Excel (spis treści)

  • Wprowadzenie do zaawansowanych formuł w programie Excel
  • Przykłady zaawansowanej formuły w programie Excel

Wprowadzenie do zaawansowanych formuł w programie Excel

Po osiągnięciu pośredniego poziomu w programie Excel musisz ciężko pracować, aby przejść do poziomu zaawansowanego. Aby przejść do poziomu zaawansowanego, musisz znać niektóre z często używanych formuł zaawansowanych. W tym artykule omówię 10 najlepszych formuł zaawansowanych, o których muszą wiedzieć wszyscy uczniowie programu Excel. Postępuj zgodnie z tym artykułem, aby uczyć się i odkrywać.

Przykłady zaawansowanej formuły w programie Excel

Rozumiemy, jak używać formuł zaawansowanych w programie Excel z kilkoma przykładami.

Możesz pobrać ten zaawansowany szablon programu Excel tutaj - Zaawansowane formuły szablon programu Excel

Przykład # 1 - Funkcja częściowego WYSZUKAJ.PIONOWO

Musiałeś napotkać sytuację, w której WYSZUKAJ.PIONOWO powoduje błąd, nawet jeśli w wartości wyszukiwania było niewielkie niedopasowanie. Na przykład, jeśli poszukujesz pensji imienia Abhishek Sinha i jeśli masz tylko Abhishek, funkcja WYSZUKAJ.PIONOWO nie może pobrać danych.

Jednak używając znaku gwiazdki na każdym końcu wartości wyszukiwania, możemy pobrać dane dla częściowej wartości wyszukiwania.

W tabeli 1 mam pełne imię i nazwisko, ale w tabeli 2 mam tylko częściowe nazwiska i muszę znaleźć wynagrodzenie każdego pracownika.

Krok 1: Otwórz formułę WYSZUKAJ.PIONOWO w komórce E3. Przed wybraniem wartości wyszukiwania umieść gwiazdkę (*) po obu stronach wartości wyszukiwania.

Krok 2: Jak zwykle możesz teraz wypełnić formułę WYSZUKAJ.PIONOWO, a my uzyskamy wyniki.

Po zastosowaniu powyższej formuły dane wyjściowe pokazano poniżej.

Ta sama formuła jest używana w innych komórkach.

UWAGA: Jednym z ograniczeń jest częściowe WYSZUKAJ.PIONOWO zwraca tę samą wartość, jeśli istnieją Abhishek Sinha i Abhishek Naidu. Ponieważ tutaj wspólną częściową wartością jest Abhishek.

Przykład # 2 - LICZBY z symbolami operatora

Musisz użyć funkcji LICZ.JEŻELI i IFS, aby policzyć rzeczy na liście. Możemy również liczyć na podstawie symboli operatora, takich jak większe niż (>) mniejsze niż (<) i znak równości (=).

Teraz spójrz na przykład na poniższe dane. Jeśli chcesz policzyć całkowitą liczbę faktur dla regionu POŁUDNIE po dacie 10 stycznia 2018 r., Jak liczyć?

Przykład 3 - JEŻELI warunek z warunkami AND i OR

Funkcje logiczne są częścią naszych codziennych czynności. Musisz je opanować, aby przejść do następnego poziomu. Jeśli obliczasz premię na podstawie wielu warunków, musisz zagnieździć warunek AND lub OR z warunkiem IF, aby wykonać zadanie.

Załóżmy, że musisz obliczyć kwotę premii dla każdego działu na podstawie działu i lat pracy, w których potrzebujesz tych funkcji. W oparciu o poniższe kryteria musimy obliczyć premię.

Jeśli usługa jest starsza niż 4 lata, a działem jest albo premia sprzedaży lub wsparcia wynosi 50000, albo premia wynosi 25000.

Zastosuj poniższy wzór, aby uzyskać kwotę premii.

Po zastosowaniu powyższej formuły dane wyjściowe pokazano poniżej.

Ta sama formuła stosowana w komórkach E3 do E9.

Przykład # 4 - Funkcja TEKSTU, dzięki której kierujesz się dynamicznie

Załóżmy, że utrzymujesz codzienną tabelę sprzedaży i musisz ją aktualizować codziennie. Na początku tabeli znajduje się jeden nagłówek z napisem „Skonsolidowane dane sprzedaży od DD-MM-RRRR do DD-MM-RRRR”. W miarę aktualizowania tabeli musisz zmienić datę nagłówka. Czy nie jest frustrującym zadaniem powtarzanie tego samego? Możemy uczynić ten nagłówek dynamicznym za pomocą funkcji TETX, MIN i Max wraz z łączonymi symbolami operatora ampersand (&).

Przykład 5 - INDEKS + MECZ + MAKS., Aby znaleźć sprzedawcę o najwyższej sprzedaży

Masz listę sprzedawców i sprzedaży, których dokonali pod ich nazwiskiem. Jak powiedzieć, kto jest najlepszym lub najwyższym sprzedawcą na liście? Oczywiście mamy kilka innych technik, aby powiedzieć wynik, ale ta funkcja może zwrócić najwyższego sprzedawcę z partii.

Zastosuj poniższą funkcję, aby uzyskać najwyższą nazwę sprzedawcy.

Po zastosowaniu powyższej formuły dane wyjściowe pokazano poniżej.

Przykład # 6 - Uzyskaj liczbę unikalnych wartości z listy

Jeśli masz wiele zduplikowanych wartości i musisz podać, ile unikalnych wartości jest dostępnych w jaki sposób? Możesz to stwierdzić, usuwając zduplikowaną wartość, ale nie jest to dynamiczny sposób określania liczby unikatowych wartości.

Za pomocą tej funkcji tablicowej możemy odróżnić unikalne wartości z partii.

Zastosuj poniższą formułę, aby uzyskać listę unikalnych wartości.

Uwaga: jest to formuła tablicowa, musisz zamknąć formułę, przytrzymując Shift + Ctrl i naciśnij klawisz Enter.

Przykład # 7 - Użyj nazwanego zakresu, aby dynamicznie rozwinąć menu rozwijane

Jeśli często pracujesz z listą rozwijaną i aktualizujesz ją, musisz wrócić do zakresu listy źródeł, aby usunąć lub dodać wartości. Następnie musisz wrócić do rozwijanych komórek i ponownie zaktualizować zakres listy rozwijanej.

Jeśli jednak możesz utworzyć nazwany zakres dla swojej listy rozwijanej, możesz uczynić ją rozwijaną dynamiczną i aktualną.

Utwórz zakres nazw, jak pokazano na poniższym obrazku.

Teraz przejdź do rozwijanej komórki i otwórz rozwijane okno dialogowe.

W źródle naciśnij klawisz F3, wyświetli wszystkie zdefiniowane nazwy, wybierz nazwę z rozwijanej listy.

Ok, lista rozwijana jest gotowa i automatycznie zaktualizuje wartości, gdy nastąpi zmiana zakresu listy rozwijanej.

Przykład # 8 - Pozbądź się wartości błędów za pomocą funkcji IFERROR

Jestem pewien, że wystąpiły wartości błędów podczas pracy z WYSZUKAJ.PIONOWO, obliczenia podziału. Obsługa tych wartości błędów jest żmudnym zadaniem. Ale możemy pozbyć się tych wartości błędów, używając funkcji IFERROR we wzorze.

Po zastosowaniu powyższej formuły dane wyjściowe pokazano poniżej.

Ta sama formuła stosowana w innych komórkach.

Przykład # 9 - Użyj funkcji PMT, aby stworzyć własną tabelę EMI

W dzisiejszych czasach opcja EMI nie jest dla nas niczym dziwnym. W programie Excel możemy oszacować własny wykres EMI za pomocą funkcji PMT. Wykonaj poniższe kroki, aby utworzyć własny wykres.

Zastosuj poniższą formułę w komórce B4, aby uzyskać kwotę EMI.

Przykład # 10 - INDEKS + PODAJNIK jako alternatywa dla funkcji WYSZUKAJ.PIONOWO

Mam nadzieję, że zdajesz sobie sprawę z ograniczenia funkcji WYSZUKAJ.PIONOWO. Jednym z głównych ograniczeń jest to, że funkcja WYSZUKAJ.PIONOWO może pobierać dane od lewej do prawej, a nie od prawej do lewej. Nie cały czas dane są dobrze zorganizowane i gotowe do użycia. Często szukana kolumna wartości znajduje się po lewej stronie wartości wyszukiwania, więc funkcja WYSZUKAJ.PIONOWO nie pomaga w takich przypadkach.

Kombinacja funkcji INDEX + MATCH służy jako alternatywa dla funkcji WYSZUKAJ.PIONOWO.

Po zastosowaniu powyższej formuły dane wyjściowe pokazano poniżej.

Ta sama formuła jest używana w innych komórkach.

Na podstawie identyfikatora produktu musimy wyodrębnić wartości sprzedaży. W głównej tabeli Identyfikator produktu znajduje się po prawej stronie kolumny sprzedaży. Dlatego funkcja WYSZUKAJ.PIONOWO nie może pobrać danych. Użyj poniższej formuły, aby pobrać dane.

Polecane artykuły

Jest to przewodnik po zaawansowanych formułach w programie Excel. Tutaj omawiamy sposób korzystania z zaawansowanych formuł w programie Excel wraz z praktycznymi przykładami i szablonem programu Excel do pobrania. Możesz także przejrzeć nasze inne sugerowane artykuły -

  1. Jak korzystać z funkcji Excel INDEX?
  2. Zaawansowany Excel | Funkcja bazy danych
  3. Przykłady formuły TRIM w programie Excel
  4. Przewodnik po formule Excel OFFSET

Kategoria:

Porady Excel