Czym są formuły i funkcje w programie Excel i jak ich używać?

Dane są używane tylko wtedy, gdy faktycznie możesz nad nimi pracować i Przewyższać to jedno narzędzie, które zapewnia dużą wygodę zarówno wtedy, gdy trzeba formułować własne równania, jak i korzystać z wbudowanych. W tym artykule dowiesz się, jak faktycznie możesz pracować z tymi funkcjami reklamowymi Formuły programu Excel.

Oto krótkie spojrzenie na omówione tutaj tematy:

• Co to jest formuła?
• Pisanie formuł programu Excel
• Edycja formuły
• Skopiuj / wklej formułę
• Ukryj formuły w programie Excel
• Formuły programu Excel dotyczące pierwszeństwa operatorów
• Co to są funkcje w programie Excel?
• Najważniejsze funkcje

Co to jest formuła?

Ogólnie formuła to skondensowany sposób przedstawiania niektórych informacji za pomocą symboli. W programie Excel formuły to wyrażenia, które można wprowadzać do komórek arkusza programu Excel, a ich wyniki są wyświetlane jako wynik.

Formuły programu Excel mogą mieć następujące typy:

Pisanie formuł Excela:

Aby napisać formułę do komórki arkusza Excela, możesz wykonać następujące czynności:

• Wybierz komórkę, w której chcesz wyświetlić wynik
• Najpierw wpisz znak „=”, aby poinformować program Excel, że zamierzasz wprowadzić formułę w tej komórce
• Następnie możesz wpisać adresy komórek lub określić wartości, które zamierzasz obliczyć
• Na przykład, jeśli chcesz dodać wartości dwóch komórek, możesz wpisać adres komórki w następujący sposób:

• Możesz także zaznaczyć komórki, których sumę chcesz obliczyć, zaznaczając wszystkie wymagane komórki, naciskając klawisz Ctrl:

Edycja formuły:

Jeśli chcesz edytować wcześniej wprowadzoną formułę, po prostu wybierz komórkę zawierającą formułę docelową i na pasku formuły możesz wprowadzić żądane zmiany. W poprzednim przykładzie obliczyłem sumę A1 i A2. Teraz edytuję to samo i zmienię formułę, aby obliczyć iloczyn wartości obecnych w tych dwóch komórkach:

Gdy to zrobisz, naciśnij Enter, aby zobaczyć żądane wyjście.

Skopiuj lub wklej formułę:

Excel jest naprawdę przydatny, gdy musisz kopiować / wklejać formuły. Za każdym razem, gdy kopiujesz formułę, program Excel automatycznie zajmuje się odwołaniami do komórek, które są wymagane w tym miejscu. Odbywa się to za pośrednictwem systemu o nazwie as Względne adresy komórek .

Aby skopiować formułę, wybierz komórkę, która zawiera oryginalną formułę, a następnie przeciągnij ją do tej komórki, która wymaga kopii tej formuły w następujący sposób:

jak korzystać z metody tostingu

Jak widać na obrazku, formuła została pierwotnie zapisana w formacie A3, a następnie przeciągnąłem ją wzdłuż B3 i C3, aby obliczyć sumę B1, B2 i C1, C2 bez wyłącznie zapisywania adresów komórek.

W przypadku zmiany wartości którejkolwiek z komórek wynik zostanie automatycznie zaktualizowany przez program Excel zgodnie z Względne adresy komórek , Bezwzględne adresy komórek lub Mieszane adresy komórek .

Ukryj formuły w programie Excel:

Jeśli chcesz ukryć jakąś formułę z arkusza Excela, możesz to zrobić w następujący sposób:

• Wybierz komórki, których formułę chcesz ukryć
• Otwórz okno Czcionka i wybierz okienko Ochrona
• Zaznacz opcję Ukryty i kliknij OK
• Następnie na karcie wstążki wybierz opcję Recenzja
• Kliknij Chroń arkusz (formuły nie będą działać, jeśli tego nie zrobisz)
• Excel poprosi Cię o wprowadzenie hasła, aby odkryć formuły do ​​wykorzystania w przyszłości

Pierwszeństwo operatorów w formułach programu Excel:

Formuły programu Excel są zgodne z zasadami BODMAS (Brackets Order Division Mnożenie Dodawanie Odejmowanie). Jeśli masz formułę zawierającą nawiasy, wyrażenie w nawiasach zostanie rozwiązane przed jakąkolwiek inną częścią pełnej formuły. Spójrz na obrazek poniżej:

Jak widać w powyższym przykładzie, mam wzór składający się z nawiasu. Tak więc zgodnie z regułami BODMAS Excel najpierw znajdzie różnicę między A2 i B1, a następnie doda wynik do A1.

Co to są „funkcje” w programie Excel?

Ogólnie rzecz biorąc, funkcja definiuje formułę, która jest wykonywana w określonej kolejności. Excel udostępnia ogromną liczbę plików wbudowane funkcje które można wykorzystać do obliczenia wyniku różnych formuł.

Formuły w Excelu są podzielone na następujące kategorie:

Teraz sprawdźmy, jak wykorzystać niektóre z najważniejszych i najczęściej używanych formuł programu Excel.

Najważniejsze funkcje programu Excel:

Oto niektóre z najważniejszych funkcji programu Excel wraz z ich opisami i przykładami.

DATA:

Jedną z najważniejszych i najczęściej używanych funkcji daty w programie Excel jest funkcja DATA. Składnia jest następująca:

DATE (rok, miesiąc, dzień)

Ta funkcja zwraca liczbę reprezentującą daną datę w formacie daty i godziny MS Excel. Funkcji DATA można używać w następujący sposób:

PRZYKŁAD:

1. = DATA (2019,11,7)
2. = DATA (2019,11,7) -7 (zwraca bieżącą datę - siedem dni)

DZIEŃ:

Ta funkcja zwraca wartość dnia miesiąca (1-31). Składnia jest następująca:

DZIEŃ (numer_seryjny)

Tutaj numer_seryjny to data, której dzień chcesz pobrać. Można go podać w dowolny sposób, na przykład jako wynik innej funkcji dostarczonej przez funkcję DATE lub odwołanie do komórki.

PRZYKŁAD:

1. = DZIEŃ (A7)
2. = DZIEŃ (DZIŚ ())
3. = DZIEŃ (DATA (2019; 11,8))

MIESIĄC:

Podobnie jak funkcja DZIEŃ, program Excel udostępnia inną funkcję, tj. Funkcję MIESIĄC, służącą do pobierania miesiąca z określonej daty. Składnia jest następująca:

MIESIĄC (numer_seryjny)

PRZYKŁAD:

1. = MIESIĄC (DZISIAJ ())
2. = MIESIĄC (DATA (2019; 11,8))

Odsetek:

Jak wszyscy wiemy, Procent to stosunek obliczany jako ułamek 100. Można go zapisać w następujący sposób:

Procent = (część / całość) x 100

W programie Excel możesz obliczyć procent dowolnych żądanych wartości. Na przykład, jeśli masz część i całe wartości obecne w A1 i A2 i chcesz obliczyć procent, możesz to zrobić w następujący sposób:

• Wybierz komórkę, w której chcesz wyświetlić wynik
• Wpisz znak „=”
• Następnie wpisz formułę jako A1 / A2 i naciśnij Enter
• Z grupy numerów kart głównych wybierz symbol „%”

JEŚLI:

Instrukcja IF jest instrukcją warunkową, która zwraca True, gdy określony warunek jest spełniony, i Flase, gdy warunek nie jest spełniony. Excel udostępnia wbudowaną funkcję „JEŻELI”, która służy do tego celu. Jego składnia jest następująca:

JEŻELI (test_logiczny; wartość_jeśli_prawda; wartość_jeśli_fałsz)

Tutaj, test logiczny to warunek, który ma zostać sprawdzony

PRZYKŁAD:

• Wprowadź wartości do porównania
• Wybierz komórkę, która będzie wyświetlać dane wyjściowe
• Wpisz na pasku formuły „= JEŻELI (A1 = A2,„ Tak ”,„ Nie ”)” i naciśnij Enter

WYSZUKAJ.PIONOWO:

Ta funkcja służy do wyszukiwania i pobierania określonych danych z kolumny z arkusza Excel. „V” w WYSZUKAJ.PIONOWO oznacza wyszukiwanie pionowe. Jest to jedna z najważniejszych i najczęściej używanych formuł w programie Excel i aby skorzystać z tej funkcji, tabelę należy posortować w kolejności rosnącej. Składnia tej funkcji jest następująca:

WYSZUKAJ.PIONOWO (wartość_wyszukiwania, tablica_tabeli, indeks_kolumny, zakres_numeru, wyszukiwanie)

gdzie,

lookup_value to wartość do wyszukania

tablica_tabeli to tabela, która ma zostać przeszukana

col_index to kolumna, z której ma zostać pobrana wartość

Zakres wyszukiwania (opcjonalnie) zwraca TRUE przez ok. dopasowanie i FALSE dla dokładnego dopasowania

PRZYKŁAD:

Jak widać na obrazku, podana przeze mnie wartość to 2, a zakres tabeli wynosi od A1 do D4. Chcę pobrać nazwisko pracownika, dlatego podałem wartość kolumny jako 2, a ponieważ chcę, aby było to dokładne dopasowanie, użyłem Fałsz do wyszukiwania zakresu.

Podatek dochodowy:

Załóżmy, że chcesz obliczyć podatek dochodowy od osoby, której łączne wynagrodzenie wynosi 300 USD. Będziesz musiał obliczyć podatek dochodowy w następujący sposób:

• Wypisz łączne wynagrodzenie, potrącenia z wynagrodzenia, dochód podlegający opodatkowaniu i procent podatku dochodowego
• Określ wartości całkowitego wynagrodzenia, potrąceń od wynagrodzeń
• Następnie oblicz dochód podlegający opodatkowaniu, znajdując różnicę między całkowitym wynagrodzeniem a potrąceniami z wynagrodzenia
• Na koniec oblicz kwotę podatku

SUMA:

Funkcja SUMA w programie Excel oblicza wynik, dodając wszystkie określone wartości w programie Excel. Składnia tej funkcji jest następująca:

SUMA (liczba1; liczba2;…)

Dodaje do niego wszystkie liczby określone jako parametr.

PRZYKŁAD:

Jeśli chcesz obliczyć sumę wydatków na zakup warzyw, wypisz wszystkie ceny, a następnie użyj wzoru SUMA w następujący sposób:

Odsetki złożone:

Aby obliczyć odsetki składane, możesz skorzystać z jednego z formuł programu Excel o nazwie FV. Ta funkcja zwróci przyszłą wartość inwestycji na podstawie okresowych, stałych stóp procentowych i płatności. Składnia tej funkcji jest następująca:

FV (stopa, liczba_okresów, rata, wartość pv, typ)

Aby obliczyć stawkę, musisz podzielić roczną stopę przez liczbę okresów, tj. Roczną stopę / okresy. Liczba okresów lub liczba_okresów jest obliczana poprzez pomnożenie okresu (liczby lat) przez okresy, tj. Okres * okres. pmt oznacza okresową płatność i może mieć dowolną wartość, w tym zero.

Rozważmy następujący przykład:

W powyższym przykładzie obliczyłem odsetki złożone na 500 $ w wysokości 10% przez 5 lat i zakładając, że wartość płatności okresowych wynosi 0. Proszę zauważyć, że użyłem -B1, co oznacza, że ​​odebrano mi 500 $.

Średni:

Jak wszyscy wiemy, średnia przedstawia medianę wielu wartości. W programie Excel średnią można łatwo obliczyć za pomocą wbudowanej funkcji o nazwie „ŚREDNIA”. Składnia tej funkcji jest następująca:

ŚREDNIA (liczba1; liczba2;…)

PRZYKŁAD:

Jeśli chcesz obliczyć średnią ocen uzyskanych przez uczniów ze wszystkich egzaminów, możesz po prostu utworzyć tabelę, a następnie użyć wzoru ŚREDNIA, aby obliczyć średnią ocen uzyskanych przez każdego ucznia.

W powyższym przykładzie obliczyłem średnią ocen dla dwóch uczniów z dwóch egzaminów. Jeśli masz więcej niż dwie wartości, których średnia musi zostać określona, ​​wystarczy określić zakres komórek, w których te wartości są obecne. Na przykład:

LICZYĆ:

Funkcja zliczania w programie Excel policzy liczbę komórek zawierających liczby w podanym zakresie. Składnia tej funkcji jest następująca:

COUNT (wartość1; wartość2;…)

PRZYKŁAD:

W przypadku, gdy chcę obliczyć liczbę komórek zawierających liczby z tabeli, którą utworzyłem w poprzednim przykładzie, będę musiał po prostu wybrać komórkę, w której chcę wyświetlić wynik, a następnie skorzystać z funkcji COUNT w następujący sposób:

OKRĄGŁY:

Aby zaokrąglić wartości do określonych miejsc dziesiętnych, możesz skorzystać z funkcji ZAOKR. Ta funkcja zwróci liczbę, zaokrąglając ją do określonej liczby miejsc dziesiętnych. Składnia tej funkcji jest następująca:

ROUND (liczba; liczba_cyfr)

PRZYKŁAD:

Znajdowanie oceny:

Aby znaleźć oceny, będziesz musiał skorzystać z zagnieżdżonych instrukcji IF w programie Excel. Na przykład w przykładzie Średnia obliczyłem średnią ocen zdobytych przez uczniów w testach. Teraz, aby znaleźć stopnie uzyskane przez tych uczniów, będę musiał utworzyć zagnieżdżoną funkcję JEŻELI w następujący sposób:

Jak widać, średnie oceny znajdują się w kolumnie G. Do obliczenia oceny użyłem zagnieżdżonej formuły JEŻELI. Kod wygląda następująco:

= JEŻELI (G19> 90, „A”, (JEŻELI (G19> 75, „B”, JEŻELI (G19> 60, „C”, JEŻELI (G19> 40, „D”, „F”)))))

Po wykonaniu tej czynności wystarczy skopiować formułę do wszystkich komórek, w których chcesz wyświetlić oceny.

RANGA:

W przypadku, gdy chcesz określić stopień osiągnięty przez uczniów klasy, możesz skorzystać z jednego z wbudowanych formuł programu Excel, tj. RANK. Ta funkcja zwróci Rank dla określonego zakresu, porównując podany zakres w porządku rosnącym lub malejącym. Składnia tej funkcji jest następująca:

RANK (ref, number, order)

PRZYKŁAD:

Jak widać, w powyższym przykładzie obliczyłem Rank uczniów za pomocą funkcji Rank. W tym przypadku pierwszy parametr to średnie oceny osiągnięte przez każdego ucznia, a tablica to średnia uzyskana przez wszystkich innych uczniów w klasie. Nie podałem żadnej kolejności, dlatego wynik zostanie określony w kolejności malejącej. W przypadku rang w porządku rosnącym będziesz musiał określić dowolną wartość niezerową.

LICZ.JEŻELI:

Aby policzyć komórki na podstawie określonego warunku, możesz użyć jednego z wbudowanych formuł programu Excel o nazwie „LICZ.JEŻELI”. Ta funkcja zwróci liczbę komórek, które spełniają jakiś warunek w podanym zakresie. Składnia tej funkcji jest następująca:

COUNTIF (zakres, kryteria)

PRZYKŁAD:

Jak widać, w powyższym przykładzie znalazłem liczbę komórek o wartościach większych niż 80. Możesz również podać wartość tekstową parametrowi kryterium.

INDEKS:

Funkcja INDEKS zwraca wartość lub odwołanie do komórki w określonej pozycji w określonym zakresie. Składnia tej funkcji jest następująca:

INDEKS (tablica, nr_wiersza, nr_kolumny) lub

INDEKS (odniesienie,nr_wiersza, nr_kolumny, nr_obszaru)

Funkcja indeksu działa w następujący sposób dla Szyk Formularz:

• Jeśli podano numer wiersza i numer kolumny, zwraca wartość obecną w komórce przecięcia
• Jeśli wartość wiersza jest ustawiona na zero, zwróci wartości obecne w całej kolumnie w określonym zakresie
• Jeśli wartość kolumny jest ustawiona na zero, zwróci wartości obecne w całym wierszu w określonym zakresie

Funkcja indeksu działa w następujący sposób dla Odniesienie Formularz:

• Zwraca odwołanie do komórki, w której przecinają się wartości wiersza i kolumny
• Pole area_num wskazuje, który zakres ma być używany w przypadku podania wielu zakresów

PRZYKŁAD:

Jak widać, w powyższym przykładzie użyłem funkcji INDEKS, aby określić wartość obecną w drugim wierszu i czwartej kolumnie dla zakresu komórek od A18 do G20.

Podobnie możesz również użyć funkcji INDEKS, określając wiele odwołań w następujący sposób:

utwórz parametr w tabeli

To prowadzi nas do końca tego artykułu na temat formuł i funkcji programu Excel. Mam nadzieję, że wszystko, co zostało Ci udostępnione, jest dla Ciebie jasne. Upewnij się, że ćwiczysz jak najwięcej i cofnij swoje doświadczenie.

Masz do nas pytanie? Wspomnij o tym w sekcji komentarzy na blogu „Formuły i funkcje programu Excel”, a my skontaktujemy się z Tobą tak szybko, jak to możliwe.

Aby uzyskać dogłębną wiedzę na temat popularnych technologii i ich różnych zastosowań, możesz zapisać się na żywo z całodobowym wsparciem i dożywotnim dostępem.