17 zaawansowanych formuł Excela – musi wiedzieć każdy profesjonalista

Jeśli często marnujesz ręce i marnujesz godziny na ręczne wykonywanie zadań w programie Excel, tracisz wiedzę, jak potężny może być program Excel, jeśli wiesz, jak używać go we właściwy sposób. Microsoft Excel jest znany ze swoich formuł, które mogą wydajnie pracować na dużej liczbie danych w komórce, bez większego zaangażowania ręcznego w ten proces.

Opracowaliśmy 17 zaawansowanych formuł Excela, które mają niesamowitą zdolność do przekształcania długich, przyziemnych, manualnych zadań w kilka sekund pracy. To formuły, które każdy profesjonalista powinien mieć na wyciągnięcie ręki, aby zaoszczędzić swój cenny czas lub zaimponować szefowi w krytycznym momencie.

Przejrzyj listę i powiedz nam swój ulubiony!

Uczący się otrzymują średnią podwyżkę wynagrodzenia o 58%, przy czym najwyższa wynosi do 400%.

1. DOPASOWANIE DO INDEKSU

Formuła = INDEKS(C3:E9;POZYCJA(B13;C3:C9,0);POZYCJA(B14;C3:E3,0))

Doskonała alternatywa dla formuły WYSZUKAJ.PIONOWO lub WYSZUKAJ.POZIOMO w programie Excel, która ma pewne wady przy wykonywaniu zadań wyszukiwania. INDEX MATCH to kombinacja dwóch oddzielnych funkcji:

INDEKS: Ta formuła zwraca wartość komórki w tabeli na podstawie kolumny i numeru wiersza.

DOPASUJ: Ta formuła zwraca pozycję komórki w wierszu lub kolumnie.

Przykładem połączenia formuł INDEKS i PODAJ .

Źródło

Podzielmy to krok po kroku:

Jak połączyć INDEKS i DOPASUJ

• Wpisz INDEKS
• Wybierz obszar, który chcesz zindeksować
• Zablokuj obszar za pomocą F4
• Znajdź wiersz, z którego chcesz wyszukać dane
• Wpisz MATCH z serią informacji i zablokuj obszar za pomocą F4
• Wpisz 0 dla dokładnego dopasowania, zamknij nawiasy
• Wciśnij Enter

Ciekawe pomysły na projekty Data Science

Teraz masz w pełni dynamiczny i funkcjonalny zestaw kolumn i wierszy w arkuszu, w którym wszystkie przeczesywanie komórek i wierszy w poszukiwaniu właściwych danych odbywa się automatycznie.

2. ŚREDNIA

Aby wykonać wzór na średnią, aby znaleźć średnią dowolnego zakresu liczb, należy wykonać następujące czynności:

Wprowadź wartości, komórki lub skalę komórek, które obliczasz w formacie.

Formuła zaczyna się od =ŚREDNIA(liczba1, liczba 2 itd.)

Jeśli chcesz wykonać średnią z zakresu komórek w arkuszu, oto kroki, które musisz wykonać:

• Wprowadź wartości, komórki lub skalę komórek, które obliczasz w formacie, tak jak to zrobiłeś powyżej
• Formuła powinna wyglądać jak =ŚREDNIA (wartość początkowa: wartość końcowa).

Jeśli zastanawiasz się, jak wprowadzić wartości dla regionu komórek, możesz zaznaczyć obszar na arkuszu za pomocą myszy i zablokować go za pomocą F4. Spowoduje to, że Excel wykona za Ciebie resztę pracy bez sumowania i dzielenia liczby z liczbą pozycji w grupie.

3. SUMA

Ta formuła w programie Excel jest oznaczona jako SUMA.JEŻELI(zakres, kryteria, [zakres sumy]). Spowoduje to sumę wartości w żądanym zakresie komórek, które spełnią określone przez Ciebie wymagania. Na przykład =SUMA.JEŻELI(C3:C12, „>70 000) zwróci sumę wartości między komórkami C3 i C12 tylko z komórek, które mają wartość większą niż 70 000.

W przypadku finansów, wynagrodzeń, a nawet kalkulacji kosztów, potrzebujesz wartości leadów, które są powiązane z konkretnymi pracownikami, określonej przez postawiony przez Ciebie warunek. Robienie tego ręcznie jest bardzo czasochłonne i spowalnia.

Formuła dla powyższego warunku może mieć postać =SUMA.JEŻELI(zakres, kryteria, [zakres_sumy]), gdzie Zakres można zdefiniować jako Zakres arkusza, z którego należy wybrać wartości.

[sum_range] jest zdefiniowany jako dodatkowy lub opcjonalny zakres, który zamierzasz dodać oprócz pierwszego wprowadzonego zakresu.

Oto przykład:

Źródło

4. PRZESUNIĘCIE W POŁĄCZENIU Z SUMA

Sama funkcja PRZESUNIĘCIE może nie być przydatna, ale w połączeniu z innymi usługami możesz uzyskać złożoną formułę z szybszymi wynikami, jeśli chcesz stworzyć dynamiczną rolę, która może sumować dowolną zmienną liczbę komórek, zwykłą formułę SUMA, która jest statyczne należy połączyć z funkcją OFFSET.

Tak więc, aby znaleźć sumę wartości w komórkach zmiennych, wzór powinien wyglądać następująco:

=SUMA(B4:PRZESUNIĘCIE(B4,0;E2-1))

Źródło

Tutaj końcowe odniesienie funkcji SUMA zostaje zastąpione funkcją PRZESUNIĘCIE. Formuła SUMA rozpoczynająca się w B4 kończy się zmienną i jest formułą PRZESUNIĘCIA, zaczynając od B4, kontynuując przez wartość w E2 („3”) minus jeden. Pomaga to metodzie przejść przez dwie komórki i zsumować dane z trzech lat. W powyższym odwołaniu widać, że komórka F7 zawiera sumę komórek B4: D4 = 15.

5. JEŚLI I

Ta formuła przydaje się w sytuacjach, gdy musisz stworzyć określone warunki, aby przeczesać stos danych. Instrukcja IF pomaga w korzystaniu z zaawansowanej funkcji programu Excel IF do tworzenia nowego pola na podstawie tych warunków na już istniejącej ścieżce.

Przykładowo, jeśli chcesz oznaczyć pracowników z zarobkami powyżej 50 tys. i identyfikatorem pracownika większym niż 3, to formuła będzie wyglądać tak:

JEŻELI(ORAZ(E4>3;F4>50000)1,0)

Źródło

Ponieważ w powyższych danych nie ma prawdziwych przypadków, formuła zwróci wartość 0.

6. ŁĄCZENIE

Jeśli masz dużo ciągów tekstowych w arkuszu danych i chcesz, aby dane były wyświetlane w jednym wierszu, ta formuła jest Twoim celem. Na przykład, jeśli chcesz przedstawić identyfikator pracownika i nazwisko pracownika w jednej kolumnie, metoda powinna wyglądać następująco:

=ZŁĄCZ.TEKSTY(B3;C3)

Źródło

7. PMT

Ta funkcja pomoże Ci określić przyszłe płatności należne za pożyczkę, biorąc pod uwagę określoną stopę procentową, kwotę główną i czas trwania pożyczki. Oto, co musisz zacząć:

• Okres kredytowania
• Kapitał początkowy (pieniądze) pożyczki
• Przyszła wartość
• Rodzaj pożyczki

Teraz, jeśli chcesz znaleźć miesięczną spłatę kwoty głównej, wzór na to samo będzie następujący:

= PMT (stawka, na, PV, [fv], [typ])

Zrozummy to na przykładzie:

Źródło

=PMT(C2/12.B2.A2)

A najlepsze jest to, że możesz przeciągnąć prawy dolny róg komórek PMT przez pola, aby automatycznie obliczyć miesięczną kwotę płatności dla wszystkich pól!

Popularne rodzaje pracy w zakresie analizy danych

8. PRZYCINANIE

Jest to jedna z najczęściej używanych formuł w programie Excel, która czyści wszelkie niepożądane miejsca na polach. Na przykład: Jeśli chcesz usunąć spacje na początku jakiejś nazwy, możesz to zrobić za pomocą funkcji TRIM:

=PRZYC(C6)

Źródło

To zwróci ci wartość Chandan Kale bez żadnych dodatkowych spacji dołączonych do przodu lub na końcu.

9. LEN

Jest to funkcja, która informuje o liczbie znaków w ciągu tekstu. Jednym z najbardziej ekscytujących sposobów wykorzystania tego jest, na przykład, jeśli chcesz wyróżnić darczyńców, którzy przekazali ponad 1000 USD, licząc cyfry w kolumnie darowizny, wzór będzie następujący:

=DŁ(B2)

Źródło

A jeśli chcesz podświetlić wszystkie komórki w kolumnie Darowizna, musisz:

• Wybierz w menu kartę Strona główna
• Kliknij Formatowanie warunkowe (na pasku narzędzi)
• Wybierz Użyj formuły, aby określić, które komórki do sformatowania

Źródło

Tutaj, jeśli ustawisz warunek „>3”, wszystko powyżej 1000 USD otrzyma unikalne formatowanie, które możesz wybrać, klikając opcję Formatuj.

10. WYBIERZ

Jest to świetna funkcja do analizy scenariuszy w modelowaniu finansowym. Pozwala wybrać między określoną liczbą opcji i zwrócić „wybór”, który wybrałeś. Na przykład, jeśli masz trzy różne wartości wzrostu przychodów w przyszłym roku na podstawie założeń, korzystając z funkcji WYBIERZ, możesz zwrócić kwotę zgodnie z wymaganiami.

Formuła to:

=WYBIERZ(C7;D3;D4;D5)

Źródło

11. KOMÓRKA, LEWA, ŚRODEK i PRAWA

Wszystkie powyższe funkcje można łączyć na wiele sposobów, aby uzyskać zaawansowane formuły.

• Funkcja KOMÓRKA służy do zwracania różnego rodzaju informacji o zawartości komórki
• Usługa LEFT służy do zastępowania tekstu z początku komórki.
• Funkcja MID może zwrócić tekst z dowolnego punktu początkowego komórki.
• Funkcja PRAWY zwraca tylko tekst z końca komórki.

Źródło

12. XNPV i XIRR

Wszystkim analitykom, którzy zetkną się z bankowością inwestycyjną, badaniami akcji lub jakimkolwiek innym obszarem finansów korporacyjnych, który wymaga dyskontowania przepływów pieniężnych, te formuły z pewnością zaoszczędzą Ci wiele czasu i narzekania!

Pytania i odpowiedzi dotyczące wywiadu dotyczącego nauki o danych

Na przykład, jeśli chcesz obliczyć bieżącą wartość netto (NPV) dla dowolnej inwestycji przy użyciu określonej stopy dyskontowania i przepływów pieniężnych występujących w nieregularnych odstępach czasu, użyj następującej funkcji.

=XNPV(stopa dyskontowa, przepływy pieniężne, daty)

Źródło

Z drugiej strony funkcja XIRR informuje o wewnętrznej stopie zwrotu (gdzie wypływ = napływ) dla serii przepływów pieniężnych występujących w nieregularnych odstępach czasu, takich jak:

13. LICZBA ( )

Analitycy często próbują znaleźć liczbę komórek z wartościami (liczby, błędy, tekst, wartości logiczne) oraz tych, które są puste. Funkcja ILE.NIEPUSTYCH( ) może pomóc Ci znaleźć nazwy niepustych komórek w wybranym zakresie. Na przykład formuła zliczania wartości dla arkusza danych zawierającego kolumny A1-A10 to:

=ILE.LICZB(A1: A10)

Źródło

14. FV

Ta formuła przydaje się, jeśli chcesz zainwestować w coś pieniądze i poznać jego wartość. Wymagania formuły FV to:

• Oprocentowanie pożyczki
• Liczba płatności
• Płatność za każdy okres
• Aktualne saldo początkowe
• Rodzaj pożyczki

Na przykład, jeśli chcesz porównać kilka czystych płyt CD i masz spadek o wartości 20 000 USD do zainwestowania w płytę CD. Stopy procentowe są przedstawione w formacie dziesiętnym; płatności są zerowe. Wzór scenariusza będzie następujący:

=FV(A2/12;B2,C2,D2)

Źródło

Wyniki będą następujące:

Źródło

15. RANDBWEEN

Ta funkcja pomaga losowo wybrać liczbę z predefiniowanego zakresu liczb. Po umieszczeniu w formule najniższej i najwyższej liczby, Excel może wybrać odpowiednie dane z pól, do których przypisane są nazwy w Zakresie i losowo wybrać z nich. Metoda scenariusza to:

=RANDBETWEEN(punkt początkowy;punkt końcowy)

Źródło

16. MAŁY

Funkcja MAŁY w programie Excel zwraca wartości liczbowe na podstawie pozycji wartości na liście, która jest uszeregowana według ważności. Ta funkcja pomaga pobrać „n-tą najmniejszą wartość” z tablicy lub zakresu komórek, takich jak najmniejsza wartość, druga najniższa wartość, trzecia najniższa wartość itp.

Składnia formuły to

=MAŁY (czasy,zakres)

Na przykład,

Źródło

Ponieważ funkcja MAŁA jest automatyczna, musisz podać zakres i liczbę całkowitą dla „n-tej”, aby określić wartość rankingową. Oficjalne nazwy tych argumentów to „tablica” i „k”.

17. KWARTYL

Ta funkcja zwraca kwartyl (każda z czterech równych grup) w danym zestawie danych i może zwrócić wartość minimalną, wartość maksymalną pierwszego kwartyla i wartość maksymalną drugiego kwartyla. Ta funkcja przenosi ilość pól w kwartylu w tablicy. Funkcja zwraca wartość liczbową zgodnie z żądanym percentylem.

Składnia: =KWARTYL (tablica; kwarta)

Źródło

WNIOSEK:

Microsoft Excel jest dość nieunikniony, jeśli chodzi o miejsce pracy w XXI wieku, ale sztuczka polega na tym, że nie musi być tak zniechęcająca. Uczyń go swoim przyjacielem i obserwuj, jak rośnie Twoja produktywność!