Lista 10 najlepszych zaawansowanych formuł i funkcji 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 tego wzoru i jego zastosowania do wyszukiwania określonej wartości z innych tabel, które mają jedną wspólną zmienną w tych tabelach. Załóżmy, że masz dwie tabele ze szczegółami wynagrodzenia pracowników firmy i nazwiskiem, a kolumna główna 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 zduplikowanych wartościach 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 przez określenie liczby wierszy, kolumn lub obu. Dawny. Aby poznać 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 kolejny wiersz do piątego wiersza, formuła zwróci „Chandan Kale”. Stąd wynik zależałby od wszelkich zmian zachodzących w tabeli danych w czasie.
# 3 - Formuła dopasowywania w programie Excel
Ta zaawansowana formuła programu Excel zwraca numer wiersza lub kolumny, gdy w podanym zakresie znajduje się określony ciąg lub liczba. W poniższym przykładzie próbujemy znaleźć „Rajesh Ved” w kolumnie Employee Name.
Formuła byłaby taka, jak podano poniżej:
Funkcja PODAJ.POZYCJĘ zwróciłaby 5 jako wartość.
3 rd argumentem 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 ograniczenie 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 LUB w programie Excel zamiast AND, jeśli musimy spełnić jeden z wielu warunków.
W powyższych przypadkach, jeśli którykolwiek z warunków zostanie spełniony, będziemy mieć komórkę wypełnioną jako 1, w przeciwnym razie 0. Możemy również zastąpić 1 lub 0 niektórymi podciągami z podwójnymi cudzysłowami („”).
# 6 - Formuła SUMIF w Excelu
W przypadku niektórych analiz 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, używając 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 wyświetlić 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, aby wyodrębnić określony podciąg z danego ciągu. Można go użyć w zależności od naszych wymagań. 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:
Stosowanie formuły PRAWY 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ą żądanego ciągu tekstowego i długość ciągu.
# 9 - Formuła OFFSET w Excelu
Ta zaawansowana funkcja programu Excel z połączeniem innych funkcji, takich jak SUMA lub ŚREDNIA, może nadać obliczeniom dynamiczny charakter. Najlepiej sprawdza się w przypadkach, gdy wstawiamy ciągłe wiersze do istniejącej bazy danych. OFFSET Excel zapewnia nam 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:
Wynikowy wynik to „Chandan Kale” bez spacji przed Chandan.
