Formuły programu Excel & Funkcje: Ucz się z podstawowymi PRZYKŁADAMI

Spisie treści:

Anonim

Formuły i funkcje są elementami składowymi pracy z danymi liczbowymi w programie Excel. W tym artykule przedstawiono formuły i funkcje.

W tym artykule omówimy następujące tematy.

  • Co to są formuły w programie Excel?
  • Błędy, których należy unikać podczas pracy z formułami w programie Excel
  • Co to jest funkcja w programie Excel?
  • Znaczenie funkcji
  • Wspólne funkcje
  • Funkcje numeryczne
  • Funkcje łańcuchowe
  • Funkcje daty i godziny
  • Funkcja wyszukiwania V.

Dane samouczków

W tym samouczku będziemy pracować z następującymi zestawami danych.

Budżet na artykuły domowe

S / N POZYCJA ILOŚĆ CENA £ SUMA CZĘŚCIOWA Czy to niedrogie?
1 Mango 9 600
2 Pomarańcze 3 1200
3 Pomidory 1 2500
4 Olej do gotowania 5 6500
5 Tonik 13 3900

Harmonogram projektu budowy domu

S / N POZYCJA DATA ROZPOCZĘCIA DATA KOŃCOWA CZAS TRWANIA (DNI)
1 Geodezyjna ziemia 04.02.2015 07.02.2015
2 Fundacja Lay 10.02.2015 15.02.2015
3 Zadaszenie 27.02.2015 03.03.2015
4 Obraz 09.03.2015 21.03.2015

Co to są formuły w programie Excel?

FORMUŁY W EXCEL to wyrażenie działające na wartościach w zakresie adresów komórek i operatorów. Na przykład = A1 + A2 + A3, co oznacza sumę zakresu wartości od komórki A1 do komórki A3. Przykład formuły składającej się z dyskretnych wartości, takich jak = 6 * 3.

=A2 * D2 / 2

TUTAJ,

  • informuje program Excel, że jest to formuła i powinien ją ocenić.
  • "A2" * D2" odwołuje się do adresów komórek A2 i D2, a następnie mnoży wartości znalezione w tych adresach komórek.
  • "/" jest operatorem arytmetycznym dzielenia
  • "2" jest wartością dyskretną

Formuły ćwiczenia praktyczne

Będziemy pracować z przykładowymi danymi dotyczącymi budżetu domowego, aby obliczyć sumę częściową.

  • Utwórz nowy skoroszyt w programie Excel
  • Wprowadź dane pokazane w powyższym budżecie na materiały eksploatacyjne do domu.
  • Twój arkusz powinien wyglądać następująco.

Napiszemy teraz formułę obliczającą sumę częściową

Ustaw fokus na komórkę E4

Wprowadź następującą formułę.

=C4*D4

TUTAJ,

  • "C4*D4" używa mnożenia operatora arytmetycznego (*) do pomnożenia wartości adresu komórki C4 i D4.

Naciśnij klawisz Enter

Otrzymasz następujący wynik

Poniższy animowany obraz pokazuje, jak automatycznie wybrać adres komórki i zastosować tę samą formułę do innych wierszy.

Błędy, których należy unikać podczas pracy z formułami w programie Excel

  1. Pamiętaj o zasadach nawiasów dzielenia, mnożenia, dodawania i odejmowania (BODMAS). Oznacza to, że wyrażenia w nawiasach są oceniane jako pierwsze. W przypadku operatorów arytmetycznych, dzielenie jest obliczane najpierw, a następnie mnożenie, a następnie dodawanie i odejmowanie jest ostatnią oceną. Korzystając z tej reguły, możemy przepisać powyższą formułę na = (A2 * D2) / 2. Zapewni to, że A2 i D2 zostaną najpierw obliczone, a następnie podzielone przez dwa.
  2. Formuły arkusza kalkulacyjnego programu Excel zwykle działają z danymi liczbowymi; możesz skorzystać z walidacji danych, aby określić typ danych, które powinny być akceptowane przez komórkę, tj. tylko liczby.
  3. Aby upewnić się, że pracujesz z poprawnymi adresami komórek, do których odwołują się formuły, możesz nacisnąć klawisz F2 na klawiaturze. Podświetli to adresy komórek użyte w formule i możesz sprawdzić krzyżowo, czy są to żądane adresy komórek.
  4. Kiedy pracujesz z wieloma wierszami, możesz użyć numerów seryjnych dla wszystkich wierszy i mieć liczbę rekordów na dole arkusza. Należy porównać liczbę numerów seryjnych z sumą rekordów, aby upewnić się, że formuły obejmowały wszystkie wiersze.

Sprawdź 10 najlepszych formuł arkusza kalkulacyjnego Excel

Co to jest funkcja w programie Excel?

FUNKCJA W EXCEL to predefiniowana formuła używana dla określonych wartości w określonej kolejności. Funkcja służy do szybkich zadań, takich jak znajdowanie sumy, liczby, średniej, wartości maksymalnej i wartości minimalnej dla zakresu komórek. Na przykład komórka A3 poniżej zawiera funkcję SUMA, która oblicza sumę zakresu A1: A2.

  • SUMA do sumowania zakresu liczb
  • ŚREDNIA do obliczania średniej z danego zakresu liczb
  • LICZBA do zliczania liczby elementów w podanym zakresie

Znaczenie funkcji

Funkcje zwiększają produktywność użytkowników podczas pracy z programem Excel . Powiedzmy, że chcesz uzyskać całkowitą sumę dla powyższego budżetu na artykuły domowe. Aby to uprościć, możesz użyć wzoru, aby uzyskać całkowitą sumę. Używając formuły, musiałbyś kolejno odnosić się do komórek od E4 do E8. Musiałbyś użyć następującego wzoru.

= E4 + E5 + E6 + E7 + E8

W przypadku funkcji powyższy wzór zapisałbyś jako

=SUM (E4:E8)

Jak widać z powyższej funkcji używanej do uzyskania sumy zakresu komórek, znacznie wydajniejsze jest użycie funkcji do uzyskania sumy niż użycie formuły, która będzie musiała odwoływać się do wielu komórek.

Wspólne funkcje

Przyjrzyjmy się niektórym z najczęściej używanych funkcji w formułach programu MS Excel. Zaczniemy od funkcji statystycznych.

S / N FUNKCJONOWAĆ KATEGORIA OPIS STOSOWANIE
01 SUMA Math & Trig Dodaje wszystkie wartości w zakresie komórek = SUMA (E4: E8)
02 MIN Statystyczny Znajduje minimalną wartość w zakresie komórek = MIN (E4: E8)
03 MAX Statystyczny Znajduje maksymalną wartość w zakresie komórek = MAX (E4: E8)
04 ŚREDNI Statystyczny Oblicza średnią wartość w zakresie komórek = ŚREDNIA (E4: E8)
05 LICZYĆ Statystyczny Zlicza liczbę komórek w zakresie komórek = LICZBA (E4: E8)
06 LEN Tekst Zwraca liczbę znaków w tekście ciągu = DŁ (B7)
07 SUMIF Math & Trig Dodaje wszystkie wartości w zakresie komórek, które spełniają określone kryteria. = SUMA.JEŻELI (zakres, kryteria; [zakres_suma]) = SUMA.JEŻELI (D4: D8; "> = 1000"; C4: C8)
08 AVERAGEIF Statystyczny Oblicza średnią wartość w zakresie komórek, które spełniają określone kryteria. = ŚREDNIA.JEŻELI (zakres, kryteria, [średni_zakres]) = ŚREDNIA.JEŻELI (F4: F8, "Tak", E4: E8)
09 DNI Data i godzina Zwraca liczbę dni między dwiema datami = DNI (D4; C4)
10 TERAZ Data i godzina Zwraca bieżącą datę i godzinę systemową = TERAZ ()

Funkcje numeryczne

Jak sama nazwa wskazuje, funkcje te działają na danych liczbowych. W poniższej tabeli przedstawiono niektóre typowe funkcje numeryczne.

S / N FUNKCJONOWAĆ KATEGORIA OPIS STOSOWANIE
1 ISNUMBER Informacja Zwraca True, jeśli podana wartość jest liczbowa, lub False, jeśli nie jest liczbą = CZY.LICZBA (A3)
2 SKRAJ Math & Trig Generuje liczbę losową z przedziału od 0 do 1 = LOS ()
3 OKRĄGŁY Math & Trig Zaokrągla wartość dziesiętną do określonej liczby miejsc dziesiętnych = ZAOKR (3,14455; 2)
4 MEDIANA Statystyczny Zwraca liczbę ze środka zbioru podanych liczb = MEDIANA (3,4,5,2,5)
5 Liczba Pi Math & Trig Zwraca wartość funkcji matematycznej PI (π) = PI ()
6 MOC Math & Trig Zwraca wynik liczby podniesionej do potęgi. POTĘGA (liczba, potęga) = MOC (2,4)
7 MOD Math & Trig Zwraca resztę po podzieleniu dwóch liczb = MOD (10,3)
8 RZYMSKI Math & Trig Konwertuje liczbę na cyfry rzymskie = RZYMSKI (1984)

Funkcje łańcuchowe

Te podstawowe funkcje programu Excel służą do manipulowania danymi tekstowymi. W poniższej tabeli przedstawiono niektóre typowe funkcje ciągów.

S / N FUNKCJONOWAĆ KATEGORIA OPIS STOSOWANIE KOMENTARZ
1 LEWO Tekst Zwraca liczbę określonych znaków od początku (po lewej stronie) ciągu = LEWO ("GURU99"; 4) Zostały 4 znaki „GURU99”
2 DOBRZE Tekst Zwraca liczbę określonych znaków od końca (prawej strony) ciągu = PRAWO („GURU99”; 2) Prawe 2 znaki „GURU99”
3 ŚRODEK Tekst Pobiera liczbę znaków ze środka ciągu z określonej pozycji początkowej i określonej długości. = MID (tekst, nr_początkowy, liczba_znaków) = MID („GURU99”; 2, 3) Pobieranie postaci od 2 do 5
4 ISTEXT Informacja Zwraca wartość True, jeśli podany parametr to Text = ISTEXT (wartość) wartość - wartość do sprawdzenia.
5 ODNALEŹĆ Tekst Zwraca pozycję początkową ciągu tekstowego w innym ciągu tekstowym. W tej funkcji rozróżniana jest wielkość liter. = ZNAJDŹ (znajdź_tekst, w_tekście, [numer_początkowy]) = ZNAJDŹ ("oo"; "Pokrycie dachowe"; 1) Znajdź oo w "Pokrycia dachowe", Wynik to 2
6 ZASTĄPIĆ Tekst Zastępuje część ciągu innym określonym ciągiem. = REPLACE (stary_tekst, numer_początkowy, liczba_znaków, nowy_tekst) = ZAMIEŃ ("Pokrycie dachu"; 2,2; "xx") Zamień „oo” na „xx”

Funkcje daty i czasu

Te funkcje służą do manipulowania wartościami dat. W poniższej tabeli przedstawiono niektóre typowe funkcje daty

S / N FUNKCJONOWAĆ KATEGORIA OPIS STOSOWANIE
1 DATA Data i godzina Zwraca liczbę reprezentującą datę w kodzie Excel = DATA (2015; 2; 4)
2 DNI Data i godzina Znajdź liczbę dni między dwiema datami = DNI (D6; C6)
3 MIESIĄC Data i godzina Zwraca miesiąc na podstawie wartości daty = MIESIĄC („02.04.2015”)
4 MINUTA Data i godzina Zwraca minuty z wartości czasu = MINUTA („12:31”)
5 ROK Data i godzina Zwraca rok z wartości daty = ROK („02.04.2015”)

Funkcja WYSZUKAJ.PIONOWO

Funkcja WYSZUKAJ.PIONOWO służy do wyszukiwania pionowego w skrajnej lewej kolumnie i zwracania wartości w tym samym wierszu z określonej kolumny. Wyjaśnijmy to w języku laika. Budżet wyposażenia domu zawiera kolumnę z numerem seryjnym, która jednoznacznie identyfikuje każdą pozycję w budżecie. Załóżmy, że masz numer seryjny towaru i chcesz poznać opis przedmiotu, możesz skorzystać z funkcji WYSZUKAJ.PIONOWO. Oto jak działałaby funkcja WYSZUKAJ.PIONOWO.

=VLOOKUP (C12, A4:B8, 2, FALSE)

TUTAJ,

  • "=VLOOKUP" wywołuje funkcję wyszukiwania pionowego
  • "C12" określa wartość do wyszukania w skrajnej lewej kolumnie
  • "A4:B8" określa tablicę tabeli z danymi
  • "2" określa numer kolumny z wartością wiersza, która ma zostać zwrócona przez funkcję WYSZUKAJ.PIONOWO
  • "FALSE," informuje funkcję WYSZUKAJ.PIONOWO, że szukamy dokładnego dopasowania podanej wartości wyszukiwania

Animowany obraz poniżej pokazuje to w akcji

Pobierz powyższy kod programu Excel

Podsumowanie

Excel umożliwia manipulowanie danymi za pomocą formuł i / lub funkcji. Funkcje są generalnie bardziej produktywne w porównaniu do pisania formuł. Funkcje są również dokładniejsze w porównaniu do formuł, ponieważ margines popełniania błędów jest bardzo minimalny.

Oto lista ważnych formuł i funkcji programu Excel

  • SUMA, funkcja = =SUM(E4:E8)
  • Funkcja MIN = =MIN(E4:E8)
  • Funkcja MAX = =MAX(E4:E8)
  • ŚREDNIA, funkcja = =AVERAGE(E4:E8)
  • LICZBA, funkcja = =COUNT(E4:E8)
  • DNI, funkcja = =DAYS(D4,C4)
  • Funkcja WYSZUKAJ.PIONOWO = =VLOOKUP (C12, A4:B8, 2, FALSE)
  • DATA, funkcja = =DATE(2020,2,4)