Ściągawka formuł programu Excel: przewodnik dla średniozaawansowanych

Więc udało Ci się opanować podstawy Excela – brawa dla Ciebie! Teraz zdecydowałeś się podnieść poprzeczkę i zagłębić się w bardziej zaawansowane rzeczy, prawda? Cóż, masz szczęście, ponieważ przygotowaliśmy dla Ciebie poręczną ściągawkę Excel dla średniozaawansowanych!

Ta zaawansowana ściągawka programu Microsoft Excel obejmuje szeroki zakres tematów, które zwiększą Twoje umiejętności w zakresie analizy numerycznej, przetwarzania tekstu oraz złożonej logiki daty i czasu.

Ściągawka programu Excel dla średniozaawansowanych jako źródło informacji na komputerze pozwala zagłębić się w bardziej zaawansowane formuły i funkcje programu Microsoft Excel.

Teraz czytaj dalej, ale także wydrukuj i zapisz ściągawkę poniżej!

Zacznijmy!

Excel-formuły-ściągawka-średniozaawansowany-1 Pobierz

Spis treści

• Pośrednie formuły matematyczne
  • Logika warunkowa
• Pośrednie wzory statystyczne
  • 1. MINA i MAXA
  • 2. LICZ. i LICZ.JEŻELI
• Formuły programu Excel do analizy finansowej
  • 1. PRODUKT Funkcja
  • 2. Funkcje ILORAZ i LOG
  • Funkcje dla scenariuszy inwestycyjnych
• Pośrednie formuły daty i godziny w programie Excel
  • 1. Funkcje miesiąca pośredniego
  • 2. Funkcje tygodnia pośredniego
  • 3. Funkcje czasu pośredniego
• Funkcje programu Excel dla algebry warunkowej i logicznej
  • 1. Funkcja JEŻELI
  • 2. Funkcja AND
  • 3. Funkcja LUB
  • 4. Funkcja NIE
• Odwoływanie się do komórek
  • 1. POŚREDNIA Funkcja
  • 2. Funkcja INDEKS
  • 3. Funkcja PRZESUNIĘCIA
• Pośrednie formuły tekstowe
  • 1. TEKST POŁĄCZ Funkcja
  • 2. Funkcja WYMIANA
  • 3. ZAMIENNIK Funkcja
• Pośrednie formuły wyszukiwania
• Formuły obsługi błędów programu Microsoft Excel
  • 1. JEŻELI.BŁĄD Funkcja
  • 2. Funkcja JEŻELI
• Skróty do formuł i funkcji
• Formatowanie komórek pośrednich
• Końcowe przemyślenia

Pośrednie formuły matematyczne

Nasza ściągawka dla początkujących obejmowała najpopularniejsze funkcje matematyczne dla początkujących, takie jak funkcja SUMA. Użytkownicy średniozaawansowani powinni zapoznać się z tymi bardziej złożonymi funkcjami:

• ABS() zwraca wartość bezwzględną liczby.

• SQRT() zwraca pierwiastek kwadratowy z liczby

• RAND() zapewnia losową liczbę z przedziału od 0 do 1

Logika warunkowa

Użytkownicy średniozaawansowani powinni również wiedzieć, jak wykonywać obliczenia matematyczne w oparciu o wiele warunków przy użyciu następujących funkcji:

• SUMA.WARUNKÓW(suma_zakres; zakres_kryteriów1; kryteria1; [….])

• LICZ.WARUNKI(kryteria_zakres1; kryteria1; [zakres_kryteriów2; kryteria2]; …)

• ŚREDNIA.WARUNKÓW(kryteria_zakres1, kryteria1, [zakres_kryteriów2, kryteria2], …)

Funkcje oceniają podane kryteria i generują wartość logiczną PRAWDA lub FAŁSZ.

Załóżmy, że masz następujące dane sprzedaży w kolumnach A, B i C:

Aby obliczyć sumę sprzedaży czerwonych koszulek, użyj formuły SUMA.WARUNKÓW:

=SUMA.WARUNKÓW(C2:C6; A2:A6; „Koszulka”; B2:B6; „Czerwony”)

Aby policzyć liczbę rzędów z czerwonymi koszulkami, użyj wzoru LICZ.WARUNKI:

=LICZ.WARUNKI(C2:C6; A2:A6; „Koszulka”; B2:B6; „Czerwony”)

Aby obliczyć średnią sprzedaż czerwonych t-shirtów, użyj wzoru ŚREDNIA.WARUNKÓW:

=ŚREDNIA.WARUNKÓW(C2:C6; A2:A6; „Koszulka”, B2:B6; „Czerwony”)

Pośrednie wzory statystyczne

Oprócz podstawowych funkcji, takich jak MIN i MAX, Excel oferuje szerszy zakres formuł statystycznych dla średniozaawansowanych użytkowników. Oto niektóre z najbardziej przydatnych:

• MINA

• MAKS

• LICZBA

• LICZ.JEŻELI

1. MINA i MAXA

Częściej używane funkcje MIN i MAX ignorują wartości tekstowe.

Alternatywne funkcje MINA i MAXA uwzględniają zarówno tekst, jak i liczby podczas wyszukiwania najwyższych lub najniższych wartości. Wartości tekstowe są oceniane tak, jakby wynosiły zero.

Różnice widać na poniższym obrazku. Pierwszy wiersz ocenia minimalną wartość 10, podczas gdy drugi wiersz ocenia jako 0 ze względu na obecność wartości tekstowej.

2. LICZ. i LICZ.JEŻELI

Funkcja ILE.LICZ. służy do zliczania niepustych komórek w zakresie.

Funkcja LICZ.JEŻELI jest mniej specyficzna, ponieważ służy do zliczania komórek w zakresie, które spełniają określony warunek lub kryteria.

Tych funkcji można również użyć do zliczenia różnych wartości w kolumnie. Ten film pokazuje, jak to zrobić.

Formuły programu Excel do analizy finansowej

Istnieje kilka formuł, których będziesz chciał użyć podczas przeprowadzania analizy finansowej, takiej jak prognozowanie inwestycji:

• ILOCZYN(liczba1; [liczba2…])

• ILORAZ(licznik; mianownik)

• LOG(liczba; [podstawa])

1. PRODUKT Funkcja

Załóżmy, że chcesz obliczyć przyszłą wartość inwestycji początkowej w wysokości 1000 USD (komórka B1) przy rocznej stopie procentowej 4% (komórka B2) po pięciu latach (komórka B3).

Użyj funkcji ILOCZYN, aby obliczyć przyszłą wartość za pomocą następującego wzoru:

=B1 * ILOCZYN (1 + B2)^B3

Ta formuła zwróci przyszłą wartość Twojej inwestycji.

2. Funkcje ILORAZ i LOG

Kolejnym krokiem jest obliczenie, po ilu latach inwestycja podwoi swoją wartość przy danej stopie procentowej.

Użyj funkcji QUOTIENT w połączeniu z funkcją LOG, aby obliczyć to w następujący sposób:

=ILOROZ(LOG(2) / LOG(1 + B2), 1)

Ta formuła zwróci 17 z przykładowymi danymi, wskazując, że podwojenie wartości inwestycji zajmie 17 lat przy rocznej stopie procentowej wynoszącej 4%.

Ten obraz przedstawia formuły w arkuszu programu Excel :

Funkcje dla scenariuszy inwestycyjnych

Oto niektóre funkcje, które są odpowiednie dla określonych scenariuszy finansowych. Jeden z nich może być dokładnie tym, czego potrzebujesz:

• NPV – Funkcja NPV (Net Present Value) oblicza wartość bieżącą netto inwestycji na podstawie serii przyszłych przepływów pieniężnych w oparciu o stopę dyskontową.

• ACCRINT – Funkcja ACCRINT oblicza narosłe odsetki papieru wartościowego, który płaci odsetki okresowe. Jest to przydatne do określania odsetek uzyskanych z papieru wartościowego od daty ostatniej płatności do określonej daty rozliczenia.

• INTRATE – Funkcja INTRATE oblicza stopę procentową dla w pełni zainwestowanego papieru wartościowego.

• PMT – Funkcja PMT oblicza całkowitą płatność za dłużny papier wartościowy.

• IRR – Funkcja IRR zapewnia wewnętrzną stopę zwrotu.

• YIELD – Funkcja YIELD zapewnia rentowność papieru wartościowego na podstawie stopy procentowej, wartości nominalnej i terminu zapadalności.

Pośrednie formuły daty i godziny w programie Excel

Podstawowe funkcje daty i godziny w programie Excel obejmują funkcje TERAZ i DZIŚ dla bieżącej daty. Użytkownicy średniozaawansowani powinni również wiedzieć, jak wyodrębnić komponenty z podanej daty za pomocą:

• Data dnia)

• MIESIĄC(data)

• ROK (data)

Formuła =MIESIĄC(„23 kwietnia 2023″) zwróci wynik 4 dla czwartego miesiąca. Podobnie funkcje DZIEŃ i ROK zwrócą odpowiednio 23 i 2023.

1. Funkcje miesiąca pośredniego

Średnio zaawansowani użytkownicy czasami zajmują się dodawaniem lub odejmowaniem miesięcy do daty i znajdowaniem końca miesiąca.

• EDATE(data_początkowa; liczba_miesięcy)

• EOMIESIĄC(data_początkowa; liczba_miesięcy)

Na przykład formuła =EDATE(„23 kwietnia 2023”, 2) obliczy datę dwa miesiące później.

Formuła =EOMIESIĄC(23 kwietnia 2023”, 2) oblicza koniec miesiąca dwa miesiące później.

Wynik to „30 czerwca 2023”, który uwzględnia fakt, że w czerwcu jest tylko trzydzieści dni. Jeśli określisz 3 miesiące, wynikiem będzie „31 lipca 2023”.

2. Funkcje tygodnia pośredniego

Możesz użyć kombinacji funkcji SUMA i DZIEŃ.TYGODNIA, aby policzyć liczbę pełnych dni roboczych w zakresie dat.

Funkcja WEEKDAY zwraca dzień tygodnia dla podanej daty. Drugi parametr określa system numeracji z domyślnym ustawieniem od niedzieli do soboty.

Jeśli masz serię dat w zakresie komórek B1:B40, użyj tej formuły do ​​obliczenia dni roboczych:

=SUMA(–(DZIEŃ.TYGODNIA(B1:B40;2)>5))

3. Funkcje czasu pośredniego

Funkcja YEARFRAC oblicza ułamek roku między dwiema datami.

Funkcja TIMEVALUE służy do konwersji czasu reprezentowanego jako tekst na liczbę dziesiętną reprezentującą część 24-godzinnego dnia.

Wynikiem jest numer seryjny programu Excel, gdzie 1 reprezentuje pełny 24-godzinny dzień, 0,5 reprezentuje 12 godzin, 0,25 reprezentuje 6 godzin i tak dalej.

Załóżmy, że masz godzinę w formacie tekstowym „17:45” w komórce A1 i chcesz przekonwertować ją na liczbę dziesiętną . Oto formuła:

= WARTOŚĆ CZASU (A1)

Wartość 0,74 reprezentuje część 24-godzinnej doby, która minęła w czasie piętnaście minut przed godziną szóstą.

Wykonaj, aby zmienić komórkę czasu z formatu czasu na format tekstowy. Możesz sformatować to jako wartość procentową, aby było wyraźniejsze.

Funkcje programu Excel dla algebry warunkowej i logicznej

Funkcje warunkowe i logiczne są niezbędne do podejmowania decyzji w Excelu. Następujące funkcje można łączyć z bardziej podstawowymi formułami programu Excel w celu uzyskania zaawansowanej logiki:

1. JEŻELI(warunek; wartość_jeśli_prawda; wartość_jeśli_fałsz):

2. AND(warunek1, warunek2, …)

3. LUB(warunek1, warunek2, …)

4. NIE(warunek1, warunek2, …)

Funkcja JEŻELI ocenia warunek i zwraca różne wartości w zależności od tego, czy warunek jest prawdziwy, czy fałszywy.

Funkcje AND, OR i NOT sprawdzają, które warunki są prawdziwe i pozwalają podejmować odpowiednie decyzje.

Oto kilka przykładów opartych na danych dotyczących sprzedaży, z których korzystaliśmy wcześniej.

1. Funkcja JEŻELI

Załóżmy, że chcesz wyświetlić tekst „Niska sprzedaż”, jeśli sprzedaż przedmiotów jest mniejsza niż 15. Jeśli sprzedaż jest wyższa, chcesz wyświetlić „Wysoka sprzedaż” w nowej kolumnie.

Użyj tej formuły i skopiuj ją do innych wierszy:

=JEŻELI(C2<15; „Niska sprzedaż”, „Wysoka sprzedaż”)

2. Funkcja AND

Załóżmy, że chcesz wyświetlić PRAWDA lub FAŁSZ dla koszulek z co najmniej 15 sprzedażami. Użyj tej formuły:

=ORAZ(A3=”Koszulka”, C3>=15)

3. Funkcja LUB

Załóżmy, że chcesz przetestować wiele warunków i zwrócić PRAWDA, jeśli którykolwiek z warunków jest spełniony, a w przeciwnym razie FAŁSZ, użyjesz funkcji LUB.

Na przykład, jeśli mamy wyniki testu w komórkach A1 i B1 i chcemy wiedzieć, czy któryś z nich jest wyższy niż 80, możemy użyć: =OR( A1>80, B1>80) . Zwróci to wartość PRAWDA, jeśli którykolwiek wynik (lub oba) przekroczy 80, i FAŁSZ, jeśli oba będą miały 80 lub mniej.

4. Funkcja NIE

Załóżmy, że chcesz sprawdzić, czy przedmiot nie jest bluzą z kapturem. Użyj tej formuły:

=NIE(A2=”Bluza”)

Odwoływanie się do komórek

Początkujący szybko oswoi się z używaniem odwołań bezwzględnych i względnych, takich jak $A$1 lub B2. Użytkownicy średniozaawansowani powinni zapoznać się z używaniem pośrednich odniesień, indeksów i przesunięć:

1. POŚREDNI(tekst_ref)

2. INDEKS(zakres; numer_wiersza; numer_kolumny)

3. PRZESUNIĘCIE(komórka_odniesienia; wiersze; kolumny):

1. POŚREDNIA Funkcja

Funkcja ADR.POŚR zwraca wartość określonego odwołania do komórki wprowadzonego jako tekst. Aby odwołać się do komórki B3, użyj tej formuły:

=POŚREDNI("B3")

Przewaga nad prostymi odwołaniami do komórek polega na tym, że ta funkcja zapewnia dynamiczne odwołanie. Oznacza to, że Excel automatycznie aktualizuje odwołanie, gdy zmienia się struktura arkusza kalkulacyjnego, np. usuwany jest wiersz.

2. Funkcja INDEKS

Funkcja INDEKS służy do odwoływania się do komórek w określonym zakresie na podstawie numerów wierszy i kolumn.

Aby odwołać się do komórki B3 w tabeli składającej się z sześciu wierszy, użyj następującej formuły:

=INDEKS(B1:B6; 3)

3. Funkcja PRZESUNIĘCIA

Funkcja PRZESUNIĘCIE zwraca komórkę lub zakres oddalony o określoną liczbę wierszy i kolumn od komórki odniesienia.

Jeśli chcesz wyświetlić wartość komórki, która jest o dwie komórki w poprzek i jedną komórkę w dół od komórki A1, użyj tej formuły:

=ODSUNIĘCIE(A1; 2; 1)

Oto formuły w akcji:

Pośrednie formuły tekstowe

Początkujący powinni znać funkcje tekstowe, takie jak LEWY, który wyodrębnia jeden lub więcej znaków z lewej strony łańcucha. Zaawansowany użytkownik powinien być zaznajomiony z takimi funkcjami:

1. TEXTJOIN(separator, ignoruj_pusty, tekst1, [tekst2, …])

2. ZAMIEŃ(stary_tekst, numer_początkowy, liczba_znaków, nowy_tekst)

3. SUBSTITUTE(tekst; stary_tekst; nowy_tekst; [numer_instancji])

1. TEKST POŁĄCZ Funkcja

Funkcja TEXTJOIN łączy komórki z podanym ogranicznikiem. Możesz także określić, gdzie ignorować puste komórki.

Aby utworzyć rozdzielaną przecinkami listę niektórych elementów w kolumnie A, użyj następującej formuły:

=POŁĄCZ.TEKST(“; “; PRAWDA; A2:A4)

2. Funkcja WYMIANA

Funkcja zamiany pozwala określić pozycję początkową i długość łańcucha, który chcesz zamienić na cel.

Aby zamienić pierwsze dwa znaki ciągu znaków na „XX”, użyj następującej formuły:

=POŁĄCZ.TEKST(A2; 1; 2; „XX”)

3. ZAMIENNIK Funkcja

Funkcja zastępcza zastępuje określone wystąpienia ciągu tekstowego w innym ciągu tekstowym nowym ciągiem tekstowym.

Aby zastąpić słowa „Dżinsy” słowem „Legginsy”, użyj następującej formuły:

=SUBSTITUTE(A3; „Dżinsy”; „Legginsy”)

Oto formuły w akcji:

Pośrednie formuły wyszukiwania

Typowe funkcje wyszukiwania, takie jak WYSZUKAJ.PIONOWO i PODAJ.POZYCJĘ, są omówione w ściągawce dla początkujących.

Użytkownicy średniozaawansowani powinni zapoznać się z funkcją wyszukiwania WYBIERZ:

• WYBIERZ(numer_indeksu, wartość1, [wartość2, …])

Funkcja WYBIERZ zwraca wartość z listy wartości na podstawie określonego numeru indeksu.

Załóżmy, że masz kolumnę z rozmiarami koszulek oznaczoną jako 1, 2 lub 3. Chcesz wyświetlić odpowiednio kategorię małych, średnich i dużych w tym samym wierszu. Użyj tej formuły:

=WYBIERZ(A1; „Mały”, „Średni”, „Duży”)

Formuły obsługi błędów programu Microsoft Excel

Ściągawka dla początkujących zawiera typowe komunikaty o błędach, które zobaczysz podczas pracy z programem Excel. Użytkownicy średniozaawansowani powinni mieć możliwość korzystania z formuł w celu płynnej obsługi błędów.

1. JEŻELI.BŁĄD(wartość; jeśli_błąd)

2. JEŻELI.NA(wartość; wartość_jeśli_dotyczy)

1. JEŻELI.BŁĄD Funkcja

Błędy dzielenia przez zero są powszechne, ale domyślnym sposobem wyświetlania programu Excel jest #DIV/0! nie będzie jasne dla każdego użytkownika.

Użyj funkcji JEŻELI.BŁĄD, aby zastąpić błąd standardowy przez „Nie można podzielić przez zero” za pomocą tej formuły:

=JEŻELI.BŁĄD(A1/B1; „Nie można podzielić przez zero”)

Ten rysunek pokazuje nieprzetworzony błąd w trzecim wierszu i funkcję JEŻELI.BŁĄD obsługującą czwarty wiersz.

2. Funkcja JEŻELI

Funkcja JEŻELI.NA w programie Excel służy do przechwytywania i obsługi błędów #N/D.

Inna sekcja tej ściągawki przedstawiała funkcję WYBIERZ. Ta funkcja zwróci błąd N/D, jeśli numer indeksu nie jest liczbą całkowitą.

Załóżmy, że rozmiar koszulki został wprowadzony jako 1,5, a użytkownik błędnie pomyślał, że pokaże to kategorię pomiędzy małym a średnim. Zamiast tego wyświetli błąd #N/D .

Aby podać bardziej pomocny błąd, użyj tej formuły:

=JEŻELI.(WYBIERZ(A2; „Niski”, „Średni”, „Wysoki”); „Nieprawidłowy indeks”)

Skróty do formuł i funkcji

Istnieje kilka skrótów klawiaturowych, które mogą usprawnić pracę z arkuszem.

• F2 : Edytuj aktywną komórkę i umieść punkt wstawiania na końcu zawartości komórki.

• F9 : Oblicz i wyświetl wynik wybranej części formuły.

• Ctrl + Shift + Enter : Wprowadź formułę tablicową.

• Shift + F3 : Otwórz okno dialogowe Wstaw funkcję.

• Esc : Anuluj wprowadzanie formuły i przywróć pierwotną zawartość komórki.

Niektóre z tych skrótów programu Excel mogą nie być dostępne we wszystkich językach lub układach klawiatury.

Formatowanie komórek pośrednich

W analizie finansowej kluczowe znaczenie ma odpowiednie formatowanie komórek zawierających liczby, daty i waluty. Aby sformatować komórki, wykonaj następujące kroki:

1. Zaznacz komórki, które chcesz sformatować.

2. Kliknij prawym przyciskiem myszy zaznaczone komórki i wybierz „Formatuj komórki”.

3. Wybierz odpowiednią kategorię (np. Liczba, Waluta, Data) i zastosuj żądany format.

Niektóre typowe typy formatowania, które należy wziąć pod uwagę w finansach, obejmują:

• Waluta : 1234,56 USD

• Procent : 12,34%

• Księgowość : (1 234,00 USD)

• Data : 10 maja 2023 r

Końcowe przemyślenia

Gdy zapoznasz się z różnymi formułami i funkcjami opisanymi w tej ściągawce , będziesz lepiej przygotowany do obsługi coraz bardziej złożonych zadań i projektów.

Praktyka jest kluczem do opanowania każdej umiejętności, więc nie bój się eksperymentować z tymi formułami w codziennej pracy. Przeniosą Twoją wiedzę od poziomu początkującego do średniozaawansowanego.

To tylko przedsmak tego, co mogą zrobić pośrednie funkcje programu Excel, aby uprościć pracę, usprawnić analizy i pomóc w odkrywaniu spostrzeżeń z danych.

Prawdziwa magia dzieje się, gdy zaczynasz łączyć funkcje w naszej ściągawce i dostosowywać je do swoich unikalnych potrzeb. Więc nie zatrzymuj się tutaj. Kontynuuj odkrywanie, eksperymentuj, a przekonasz się, że Excel to nie tylko narzędzie, to rewolucja!

Czujesz, że chcesz przenieść sprawy na wyższy poziom? Sprawdź naszą ściągawkę dotyczącą zaawansowanych formuł programu Excel.