Formuły i funkcje programu Excel: ucz się na podstawowych PRZYKŁADACH
Charlotte Miller
Formuły i funkcje to elementy składowe pracy z danymi numerycznymi w programie Excel. W tym artykule przedstawiono formuły i funkcje.
Dane tutoriali
W tym samouczku będziemy pracować z następującymi zbiorami danych.
Budżet artykułów domowych
| S / N | ITEM | QTY | CENA | SUBTOTAL | Czy to jest 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 | ITEM | DATA ROZPOCZĘCIA | DATA ZAKONCZENIA | CZAS TRWANIA (DNI) |
|---|---|---|---|---|
| 1 | Ziemia badawcza | 04/02/2015 | 07/02/2015 | |
| 2 | Kłaść Foundation | 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 EXCELU jest wyrażeniem, które działa na wartościach w zakresie adresów komórek i operatorów. Na przykład =A1+A2+A3, które znajduje sumę zakresu wartości od komórki A1 do komórki A3. Przykład formuły składającej się z wartości dyskretnych, 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 ćwiczeń praktycznych
Będziemy pracować na przykładowych danych dla budżetu domowego, aby obliczyć sumę częściową.
- Utwórz nowy skoroszyt w programie Excel
- Wprowadź dane wskazane w budżecie artykułów gospodarstwa domowego powyżej.
- Twój arkusz powinien wyglądać następująco.
Napiszemy teraz wzór obliczający sumę częściową
Ustaw fokus na komórkę E4
Wprowadź następującą formułę.
=C4*D4
TUTAJ,
"C4*D4"używa arytmetycznego operatora mnożenia (*) w celu pomnożenia wartości adresu komórki C4 i D4.
Naciśnij klawisz Enter
Otrzymasz następujący wynik
Poniższa animacja 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 Excelu
- Pamiętaj o zasadach Brackets 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 oceniane jako pierwsze, po nim następuje mnożenie, a następnie dodawanie i odejmowanie. Na końcu oceniane jest odejmowanie. Stosując tę regułę, możemy zapisać powyższy wzór jako =(A2 * D2) / 2. Dzięki temu A2 i D2 są oceniane jako pierwsze, a następnie dzielone przez dwa.
- Formuły arkuszy kalkulacyjnych programu Excel zazwyczaj działają na danych liczbowych. Możesz skorzystać z funkcji sprawdzania poprawności danych, aby określić typ danych, które mają zostać zaakceptowane przez komórkę, np. tylko liczby.
- Aby mieć pewność, że pracujesz z właściwymi adresami komórek wymienionymi w formułach, możesz nacisnąć klawisz F2 na klawiaturze. Spowoduje to wyróżnienie adresów komórek użytych w formule i umożliwi sprawdzenie krzyżowe, aby upewnić się, że są to żądane adresy komórek.
- Gdy pracujesz z wieloma wierszami, możesz użyć numerów seryjnych dla wszystkich wierszy i mieć liczbę rekordów na dole arkusza. Powinieneś porównać liczbę numerów seryjnych z całkowitą liczbą rekordów, aby upewnić się, że Twoje formuły obejmują wszystkie wiersze.
Do
10 najlepszych formuł arkusza kalkulacyjnego Excel
Co to jest funkcja w Excelu?
FUNKCJA W EXCELU 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
- ŚREDNI do obliczania średniej z danego zakresu liczb
- COUNT do zliczania liczby elementów w danym zakresie
Znaczenie funkcji
Funkcje zwiększają produktywność użytkownika podczas pracy z Excelem. Powiedzmy, że chcesz uzyskać całkowitą sumę dla powyższego budżetu na artykuły domowe. Aby to uprościć, możesz użyć formuły, aby uzyskać całkowitą sumę. Używając formuły, musiałbyś odwołać się do komórek od E4 do E8, jedna po drugiej. Musiałbyś użyć następującej formuły.
= E4 + E5 + E6 + E7 + E8
Za pomocą funkcji można zapisać powyższą formułę jako
=SUM (E4:E8)
Jak widać z powyższej funkcji służącej do uzyskania sumy zakresu komórek, znacznie efektywniejsze 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 | FUNKCJA | KATEGORIA | OPIS | ZASTOSOWANIE |
|---|---|---|---|---|
| 01 | SUMA | Math & Trig | Dodaje wszystkie wartości z zakresu 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 | =MAKS(E4:E8) |
| 04 | ŚREDNI | Statystyczny | Oblicza średnią wartość w zakresie komórek | = ŚREDNIA (E4: E8) |
| 05 | COUNT | 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 | SUMA | Math & Trig |
Dodaje wszystkie wartości z zakresu komórek, które spełniają określone kryteria. =SUMA.JEŻELI(zakres;kryteria;[zakres_sumy]) |
=SUMIF(D4:D8,”>=1000″,C4:C8) |
| 08 | ŚREDNIA.IF | Statystyczny |
Oblicza średnią wartość w zakresie komórek spełniających określone kryteria. = ŚREDNIA.JEŻELI (zakres, kryteria, [średnia_zakres]) |
=ŚREDNIA JEŻELI(F4:F8,”Tak”,E4:E8) |
| 09 | DNI | Data i czas | Zwraca liczbę dni pomiędzy dwiema datami | =DNI(D4,C4) |
| 10 | TERAZ | Data i czas | Zwraca bieżącą datę i godzinę systemową | = TERAZ () |
Funkcje numeryczne
Jak sama nazwa wskazuje, funkcje te działają na danych liczbowych. Poniższa tabela pokazuje niektóre typowe funkcje liczbowe.
| S / N | FUNKCJA | KATEGORIA | OPIS | ZASTOSOWANIE |
|---|---|---|---|---|
| 1 | CZY.NUMER | Informacje | Zwraca True, jeśli podana wartość jest liczbowa i False, jeśli nie jest liczbowa | =CZY.LICZBA(A3) |
| 2 | SKRAJ | Math & Trig | Generuje losową liczbę z zakresu od 0 do 1 | = LOS () |
| 3 | ROUND | Math & Trig | Zaokrągla wartość dziesiętną do określonej liczby miejsc dziesiętnych | = ZAOKR (3.14455,2) |
| 4 | MEDIAN | Statystyczny | Zwraca liczbę znajdującą się w środku zbioru podanych liczb | =MEDIANA(3,4,5,2,5) |
| 5 | PI | Math & Trig | Zwraca wartość funkcji matematycznej PI(π) | =PI() |
| 6 | MOC | Math & Trig |
Zwraca wynik liczby podniesionej do potęgi. MOC(liczba, moc) |
=MOC(2,4) |
| 7 | MOD | Math & Trig | Zwraca resztę z dzielenia dwóch liczb | =MOD(10,3) |
| 8 | ROMAN | Math & Trig | Konwertuje liczbę na cyfry rzymskie | =RZYMSKI(1984) |
Funkcje ciągów
Te podstawowe funkcje programu Excel służą do manipulowania danymi tekstowymi. Poniższa tabela przedstawia niektóre typowe funkcje stringów.
| S / N | FUNKCJA | KATEGORIA | OPIS | ZASTOSOWANIE | JAK |
|---|---|---|---|---|---|
| 1 | LEWA | Tekst | Zwraca liczbę określonych znaków od początku (po lewej stronie) ciągu znaków | =LEWA(“GURU99”,4) | Pozostało 4 znaki „GURU99” |
| 2 | PRAWO | Tekst | Zwraca liczbę określonych znaków z końca (prawej strony) ciągu | =PRAWY(“GURU99”,2) | W prawo 2 znaki „GURU99” |
| 3 | MID | Tekst |
Pobiera liczbę znaków ze środka ciągu o określonej pozycji początkowej i długości. =MID (tekst, numer_początkowy, liczba_znaków) |
=ŚREDNIA(“GURU99”,2,3) | Odzyskiwanie znaków od 2 do 5 |
| 4 | CZYTEKST | Informacje | Zwraca True, jeśli podany parametr to Text | =ISTEKST(wartość) | wartość — wartość do sprawdzenia. |
| 5 | Użytkownicy | 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, [liczba_początkowa]) |
=ZNAJDŹ(„oo”, „Pokrycia dachowe”,1) | Znajdź oo w „Pokrycia dachowe”, wynik to 2 |
| 6 | WYMIANA | Tekst |
Zastępuje część ciągu innym określonym ciągiem. =ZAMIEŃ (stary_tekst, numer_początkowy, liczba_znaków, nowy_tekst) |
=ZAMIEŃ(„Pokrycie dachowe”,2,2”,xx”) | Zamień „oo” na „xx” |
Funkcje daty i czasu
Te funkcje służą do manipulowania wartościami daty. Poniższa tabela przedstawia niektóre typowe funkcje daty
| S / N | FUNKCJA | KATEGORIA | OPIS | ZASTOSOWANIE |
|---|---|---|---|---|
| 1 | DATA | Data i czas | Zwraca liczbę reprezentującą datę w kodzie programu Excel | = DATA (2015,2,4) |
| 2 | DNI | Data i czas | Znajdź liczbę dni pomiędzy dwiema datami | =DNI(D6,C6) |
| 3 | MIESIĄC | Data i czas | Zwraca miesiąc na podstawie wartości daty | =MIESIĄC(„4”) |
| 4 | MINUTE | Data i czas | Zwraca minuty z wartości czasu | =MINUTA(„12:31”) |
| 5 | ROK | Data i czas | Zwraca rok na podstawie wartości daty | =ROK(“04”) |
Funkcja WYSZUKAJ.PIONOWO
Kurs Funkcja WYSZUKAJ.PIONOWO służy do wykonywania pionowego wyszukiwania w górę w lewej kolumnie i zwracania wartości w tym samym wierszu z określonej kolumny. Wyjaśnijmy to językiem laika. Budżet artykułów gospodarstwa domowego zawiera kolumnę z numerem seryjnym, która jednoznacznie identyfikuje każdą pozycję w budżecie. Załóżmy, że masz numer seryjny przedmiotu i chcesz poznać opis przedmiotu, możesz skorzystać z funkcji WYSZUKAJ.PIONOWO. Oto jak będzie działać funkcja WYSZUKAJ.PIONOWO.
=VLOOKUP (C12, A4:B8, 2, FALSE)
TUTAJ,
"=VLOOKUP"wywołuje funkcję wyszukiwania pionowego"C12"określa wartość, która ma być wyszukiwana w kolumnie znajdującej się najbardziej po lewej stronie"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
Podsumowanie
Excel umożliwia manipulowanie danymi za pomocą formuł i/lub funkcji. Funkcje są na ogół bardziej produktywne w porównaniu do pisania formuł. Funkcje są również dokładniejsze w porównaniu do formuł, ponieważ margines popełnienia błędów jest bardzo minimalny.
Oto lista ważnych formuł i funkcji programu Excel
- Funkcja SUMA =
=SUM(E4:E8)
- Funkcja MIN =
=MIN(E4:E8)
- Funkcja MAX =
=MAX(E4:E8)
- Funkcja ŚREDNIA =
=AVERAGE(E4:E8)
- Funkcja LICZ. =
=COUNT(E4:E8)
- Funkcja DNI =
=DAYS(D4,C4)
- Funkcja WYSZUKAJ.PIONOWO =
=VLOOKUP (C12, A4:B8, 2, FALSE)
- Funkcja DATA =
=DATE(2020,2,4)
