Zaawansowane formuły programu Excel | Lista 10 najlepszych zaawansowanych funkcji programu Excel

Lista 10 najlepszych zaawansowanych formuł i funkcji programu Excel

Możesz pobrać ten szablon zaawansowanych formuł programu Excel tutaj - szablon zaawansowanych formuł programu Excel

# 1 - Formuła WYSZUKAJ.PIONOWO w programie Excel

Ta zaawansowana funkcja programu Excel jest jedną z najczęściej używanych formuł w programie Excel. Wynika to głównie z prostoty tej formuły i jej zastosowania do wyszukiwania pewnej wartości z innych tabel, które mają jedną wspólną zmienną w tych tabelach. Załóżmy, że masz dwie tabele, które zawierają szczegółowe informacje o wynagrodzeniu pracowników firmy i nazwisku, a kolumna podstawowa to identyfikator pracownika. Chcesz otrzymać wynagrodzenie z Tabeli B w Tabeli A.

Możesz użyć funkcji WYSZUKAJ.PIONOWO, jak poniżej.

W poniższej tabeli pojawi się, gdy zastosujemy tę zaawansowaną formułę programu Excel w innych komórkach kolumny Wynagrodzenie pracowników.

Przeciągnij formułę do pozostałych komórek.

Istnieją trzy główne ograniczenia funkcji WYSZUKAJ.PIONOWO:

Nie możesz mieć kolumny podstawowej po prawej stronie kolumny, dla której chcesz wypełnić wartość z innej tabeli. W takim przypadku kolumna Wynagrodzenie pracownika nie może znajdować się przed Identyfikatorem pracownika.
W przypadku zduplikowanych wartości w kolumnie podstawowej w tabeli B pierwsza wartość zostanie umieszczona w komórce.
Jeśli wstawisz nową kolumnę do bazy danych (np. Wstaw nową kolumnę przed wynagrodzeniem pracownika w tabeli B), wynik formuły może być inny w zależności od pozycji, o której wspomniałeś w formule (w powyższym przypadku wynik byłoby puste)

# 2 - Formuła INDEKSU w Excelu

Ta zaawansowana formuła programu Excel służy do pobierania wartości komórki w danej tabeli poprzez określenie liczby wierszy, kolumn lub obu. Dawny. Aby uzyskać nazwisko pracownika przy piątej obserwacji, poniżej znajdują się dane.

Możemy skorzystać z zaawansowanej formuły Excela, jak poniżej:

Ta sama formuła INDEKS może być używana do pobierania wartości w wierszu. Używając zarówno numeru wiersza, jak i numeru kolumny, składnia wyglądałaby mniej więcej tak:

Powyższy wzór zwróciłby „Rajesh Ved”.

Uwaga: jeśli wstawisz inny wiersz do danych w wierszu 5, formuła zwróci „Chandan Kale”. W związku z tym wynik będzie zależał od wszelkich zmian zachodzących w tabeli danych w czasie.

# 3 - Formuła dopasowania w programie Excel

Ta zaawansowana formuła programu Excel zwraca numer wiersza lub kolumny, gdy występuje dopasowanie określonego ciągu lub liczby w podanym zakresie. W poniższym przykładzie próbujemy znaleźć pozycję „Rajesh Ved” w kolumnie Employee Name.

Formuła byłaby taka, jak podano poniżej:

Funkcja PODAJ.POZYCJĘ zwróciłaby 5 jako wartość.

Trzeci argument służy do dokładnego dopasowania. Możesz także użyć +1 i -1 w zależności od swoich wymagań.

Uwaga: można połączyć INDEKS i PODAJ.POZYCJĘ, aby pokonać ograniczenia funkcji WYSZUKAJ.PIONOWO.

# 4 - JEŻELI AND Formuła w programie Excel

Istnieje wiele przypadków, w których trzeba utworzyć flagi w oparciu o pewne ograniczenia. Wszyscy znamy podstawową składnię IF. Używamy tej zaawansowanej funkcji JEŻELI programu Excel do tworzenia nowego pola w oparciu o pewne ograniczenia dotyczące już istniejącego pola. Ale co, jeśli podczas tworzenia flagi musimy wziąć pod uwagę wiele kolumn. Dawny. W poniższym przypadku chcemy oznaczyć wszystkich pracowników, których wynagrodzenie jest większe niż 50 tys., Ale identyfikator pracownika jest większy niż 3.

W takich przypadkach użylibyśmy IF AND. Poniżej zrzut ekranu dla tego samego.

Zwróci wynik jako 0.

Możemy mieć wiele warunków lub ograniczeń, aby utworzyć flagę na podstawie wielu kolumn za pomocą AND.

# 5 - JEŻELI LUB Formuła w programie Excel

Podobnie, możemy również użyć funkcji OR w programie Excel zamiast AND, jeśli tylko musimy spełnić jeden z wielu warunków.

W powyższych przypadkach, jeśli którykolwiek z warunków zostanie spełniony, komórka zostanie wypełniona jako 1, w przeciwnym razie 0. Możemy również podstawić 1 lub 0 przez niektóre podciągi z podwójnymi cudzysłowami („”).

# 6 - Formuła SUMIF w Excelu

W niektórych analizach może być konieczne filtrowanie niektórych obserwacji podczas stosowania funkcji sumy lub licznika. W takich przypadkach pomocna jest ta zaawansowana funkcja SUMA.JEŻELI w programie Excel. Filtruje wszystkie obserwacje w oparciu o określone warunki podane w tej zaawansowanej formule programu Excel i podsumowuje je. Dawny. A co, jeśli chcemy poznać sumę wynagrodzeń tylko tych pracowników, którzy mają identyfikator pracownika większy niż 3.

Stosując formułę SUMA.WARUNKÓW:

Formuła zwraca wyniki jako 322000.

Możemy również policzyć liczbę pracowników w organizacji o identyfikatorze pracownika większym niż 3, gdy użyjemy LICZ.JEŻELI zamiast SUMA.JEŻELI.

# 7 - CONCATENATE Formula w Excelu

Ta zaawansowana funkcja programu Excel jest jedną z formuł, których można używać z wieloma wariantami. Ta zaawansowana formuła programu Excel pomaga nam połączyć kilka ciągów tekstowych w jeden ciąg tekstowy. Na przykład, jeśli chcemy pokazać identyfikator pracownika i nazwisko pracownika w jednej kolumnie.

W tym celu możemy użyć tej formuły CONCATENATE.

Powyższy wzór da w wyniku „1Aman Gupta”.

Możemy mieć jeszcze jeden wariant, umieszczając pojedynczy łącznik między identyfikatorem a nazwą. Dawny. CONCATENATE (B3, ”-„, C3) da w wyniku „1-Aman Gupta”. Możemy również użyć tego do WYSZUKAJ.PIONOWO, gdy WYSZUKAJ w wartości Excela jest mieszaniną więcej niż jednej zmiennej.

# 8 - LEFT, MID i RIGHT Formuła w programie Excel

Możemy użyć tej zaawansowanej formuły Excela, jeśli chcemy wyodrębnić określony podciąg z danego ciągu. Wspomniane formuły mogą być użyte w oparciu o nasze wymagania. Dawny. Jeśli chcemy wyodrębnić pierwsze 5 znaków z nazwy pracownika, możemy użyć formuły LEFT w programie Excel z nazwą kolumny i drugim parametrem jako 5.

Wynik podano poniżej:

Zastosowanie formuły PRAWEJ w programie Excel jest również takie samo, po prostu patrzymy na znak z prawej strony ciągu. Jednak w przypadku funkcji MID w programie excel musimy podać pozycję początkową wymaganego ciągu tekstowego i długość ciągu.

# 9 - Formuła OFFSET w Excelu

Ta zaawansowana funkcja programu Excel z kombinacją innych funkcji, takich jak SUMA lub ŚREDNIA, może być przydatna w nadaniu obliczeniom dynamicznego charakteru. Najlepiej sprawdza się w przypadkach, gdy wstawiamy ciągłe wiersze do istniejącej bazy danych. OFFSET Excel podaje zakres, w którym musimy wspomnieć o komórce odniesienia, liczbie wierszy i kolumn. Dawny. Jeśli chcemy obliczyć średnią z pierwszych 5 pracowników w firmie, w której mamy wynagrodzenie pracowników posortowane według identyfikatora pracownika, możemy wykonać następujące czynności. Poniższa kalkulacja zawsze da nam wynagrodzenie.

To da nam sumę wynagrodzeń pierwszych 5 pracowników.

# 10 - TRIM Formula w Excelu

Ta zaawansowana formuła programu Excel służy do usuwania nieistotnych spacji z tekstu. Dawny. Jeśli chcemy usunąć spacje na początku jakiejś nazwy, możemy tego użyć, używając funkcji TRIM w programie Excel, jak poniżej: