Definiowanie i tworzenie formuły

W tej lekcji przedstawimy podstawowe zasady tworzenia formuł i używania funkcji. Uważamy, że jednym z najlepszych sposobów uczenia się jest praktyka, dlatego podajemy kilka przykładów i szczegółowo je wyjaśniamy. Tematy, które omówimy, obejmują:

NAWIGACJA SZKOLNA

Dlaczego potrzebujesz formuł i funkcji??
Definiowanie i tworzenie formuły
Względne i bezwzględne odwołanie do komórki i formatowanie
Przydatne funkcje, które powinieneś poznać
Odnośniki, wykresy, statystyki i tabele przestawne

wiersze i kolumny
przykładowa funkcja matematyczna: SUMA ()
operatorów
pierwszeństwo operatora
przykładowa funkcja finansowa: PMT (), wypłata pożyczki
za pomocą funkcji "string" ("ciąg" jest skrótem dla "ciągu tekstu") wewnątrz formuły i funkcji zagnieżdżania

Formuły są mieszaniną "funkcji", "operatorów" i "operandów". Zanim napiszemy kilka formuł, musimy utworzyć funkcję, ale zanim będziemy mogli utworzyć funkcję, najpierw musimy zrozumieć notację wiersza i kolumny.

Wiersze i kolumny

Aby zrozumieć, jak pisać formuły i funkcje, musisz wiedzieć o wierszach i kolumnach.

Wiersze działają poziomo, a kolumny działają pionowo. Aby zapamiętać, co jest czym, pomyśl o kolumnie trzymającej dach - kolumny idą w górę, a zatem rzędy idą w lewo-prawo.

Kolumny są oznaczone literami; wiersze według liczb. Pierwsza komórka w arkuszu kalkulacyjnym to A1 oznaczająca kolumnę A, wiersz 1. Kolumny są oznaczone jako A-Z. Gdy kończy się alfabet, Excel umieszcza inną literę z przodu: AA, AB, AC ... AZ, BA, BC, BC, itp.

Przykład: Funkcja Sum ()

Teraz pokażmy, jak korzystać z funkcji.

Korzystasz z funkcji, wpisując je bezpośrednio lub korzystając z kreatora funkcji. Kreator funkcji otworzy się, gdy wybierzesz funkcję z menu "Formuły" z "Biblioteki funkcji". W przeciwnym razie możesz wpisać = w komórce, a wygodne menu rozwijane pozwoli ci wybrać funkcję.

Kreator powie Ci, jakie argumenty musisz podać dla każdej funkcji. Zawiera również link do instrukcji online, jeśli potrzebujesz pomocy w zrozumieniu, co robi ta funkcja i jak z niej korzystać. Na przykład, jeśli wpiszesz = suma w komórkę, kreator w linii pokazuje, jakie argumenty są wymagane dla funkcji SUMA.

Po wpisaniu funkcji kreator jest w linii lub w prawo na twoich palcach. Po wybraniu funkcji z menu "Formuły" kreator jest wyskakującym okienkiem. Oto wyskakujący kreator dla funkcji SUMA ().

Dla naszej pierwszej funkcji użyjmy SUM (), która dodaje listę liczb.

Załóżmy, że mamy ten arkusz kalkulacyjny, by zawierać plany budżetowania wakacji rodzinnych:

Aby obliczyć całkowite koszty, możesz napisać = b2 + b3 + b4 + b5, ale łatwiej użyć funkcji SUMA ().

W programie Excel poszukaj symbolu Σ w lewym górnym rogu ekranu Excel, aby znaleźć przycisk AutoSum (matematycy używają greckiej litery Σ do dodania serii liczb).

Jeśli kursor znajduje się poniżej numerów budżetów rodzinnych, program Excel jest wystarczająco inteligentny, aby wiedzieć, że chcesz zsumować listę numerów powyżej miejsca, w którym umieściłeś kursor, aby podświetlić liczby.

Naciśnij "enter", aby zaakceptować zakres wybrany przez Excel lub użyj kursora, aby zmienić, które komórki są zaznaczone.

Jeśli spojrzysz na to, co Excel umieścił w arkuszu kalkulacyjnym, zobaczysz, że napisał on tę funkcję:

W tej formule program Excel sumuje liczby od B2 do B9. Zauważmy, że zostawiliśmy trochę miejsca pod rządem 5, abyś mógł dodać do rodzinnego budżetu na wakacje - koszty z pewnością wzrosną, ponieważ lista dzieci, co chcą robić i gdzie chcą iść dłużej!

Funkcje matematyczne nie działają z literami, więc jeśli umieścisz litery w kolumnie, wynik zostanie wyświetlony jako "#NAME?", Jak pokazano poniżej.

#IMIĘ? wskazuje, że jest jakiś błąd. Może to być dowolna liczba rzeczy, w tym:

złe odwołanie do komórki
używanie liter w funkcjach matematycznych
pominięcie wymaganych argumentów
nazwa funkcji pisowni jest nieprawidłowa
nielegalne operacje matematyczne, takie jak dzielenie przez 0

Najprostszym sposobem wyboru argumentów w obliczeniach jest użycie myszy. Możesz dodać lub usunąć z listy argumenty do funkcji, powiększając lub zmniejszając pole, które Excel rysuje po przesunięciu myszy lub kliknięciu w inną komórkę.

Kliknęliśmy na wierzchołek kwadratu wykreślonego przez program Excel, aby usunąć "bilety lotnicze" z budżetu. Możesz zobaczyć symbol krzyża, który możesz narysować, aby wybrany zakres był większy lub mniejszy.

Naciśnij "enter", aby potwierdzić wyniki.

Operatory obliczania

Istnieją dwa rodzaje operatorów: matematyka i porównanie.

Operator matematyki	Definicja
+	dodanie
-	odejmowanie lub negacja, np. 6 * -1 = -6
*	mnożenie
/	podział
%	procent
^	wykładnik, np. 2⁴= 2 ^ 4 = 2 * 2 * 2 * 2 = 16

Istnieją inne operatory niezwiązane z matematyką, takie jak "&", co oznacza łączenie (łączenie od końca do końca) dwóch ciągów. Na przykład = "Excel" i "jest zabawny" to "Excel jest zabawny".

Teraz patrzymy na operatorów porównania.

Operator porównania	Definicja
=	wynosi np. 2 = 4 lub "b" = "b"
>	większa niż np. 4> 2 lub "b"> "a"
<	mniej niż np. 2 < 4 or “a” < “b”
> =	większy lub równy - inny sposób myślenia o tym jest> = oznacza zarówno > lub =.
<=	mniejszy lub równy.
	nie równe, na przykład, 46

Jak widać powyżej, operatory porównania działają z liczbami i tekstem.

Uwaga: jeśli wpiszesz = "a"> "b" do komórki, powie "FALSE", ponieważ "a" nie jest większe niż "b." "B" pojawia się po "a" w alfabecie, więc "a" > "B" lub "B"> "a."

Pierwszeństwo zlecenia operatora

Pierwszeństwo kolejności jest pomysłem z matematyki. Excel musi przestrzegać tych samych zasad co matematyka. Ten temat jest bardziej skomplikowany, więc weź głęboki oddech i zanurzmy się.

Pierwszeństwo kolejności oznacza kolejność, w jakiej komputer oblicza odpowiedź. Jak wyjaśniliśmy w Lekcji 1, obszar koła to πr², który jest taki sam jak π * r * r. To jest nie (πr)².

Podczas pisania formuły musisz więc zrozumieć pierwszeństwo kolejności.

Ogólnie możesz to powiedzieć:

Program Excel najpierw ocenia elementy w nawiasach, które działają na odwrót.
Następnie stosuje zasady kolejności pierwszeństwa w matematyce.
Gdy dwa elementy mają taki sam priorytet, program Excel działa od lewej do prawej.

Pierwszeństwo operatorów matematycznych pokazano poniżej w malejącej kolejności.

( i )	Gdy używane są nawiasy, zastępują one normalne reguły pierwszeństwa. Oznacza to, że Excel wykona te obliczenia w pierwszej kolejności. Wyjaśnimy to dalej poniżej.
-	Negacja, np. -1. Jest to to samo, co pomnożenie liczby przez -1. -4 = 4 * (-1)
%	Procent, oznacza pomnożenie przez 100. Przykładowo, 0,003 = 0,3%.
^	Wykładnik, np. 10 ^ 2 = 100
* i /	Mnożenie i dzielenie. W jaki sposób dwóch operatorów może mieć takie samo pierwszeństwo? Oznacza to tylko, że jeśli formuła ma dwa kolejne operatory o tym samym priorytecie, to obliczenia są wykonywane od lewej do prawej.
+ i -	Dodawanie i odejmowanie.

Istnieją inne reguły pierwszeństwa dotyczące łańcuchów i operatorów referencyjnych. W tej chwili będziemy trzymać się tego, co właśnie omówiliśmy. A teraz spójrzmy na kilka przykładów.

Przykład: Obliczanie powierzchni koła

Obszar koła jest= PI () * promień ^ 2.

Patrząc na powyższą tabelę widzimy, że wykładniki pojawiają się przed mnożeniem. Tak więc komputer najpierw oblicza promień ^ 2, a następnie mnoży wynik przez Pi.

Przykład: obliczanie podwyżki wynagrodzenia

Powiedzmy, że twój szef zdecyduje, że wykonujesz świetną robotę, a on lub ona da ci 10% podwyżki! Jak obliczysz swoje nowe wynagrodzenie?

Najpierw pamiętaj, że mnożenie następuje przed dodaniem.

Czy to = wynagrodzenie + wynagrodzenie * 10% lub jest to = wynagrodzenie + (wynagrodzenie * 10%)?

Załóżmy, że twoja pensja wynosi 100 $. Po podwyższeniu o 10% twoja nowa pensja będzie:

= 100 + 100 * 10% = 100 + 10 = 110

Możesz również napisać tak:

= 100 + (100 * 10%) = 100 + 10 = 110

W drugim przypadku jawnie ustalamy kolejność pierwszeństwa za pomocą nawiasów. Pamiętaj, że nawiasy są oceniane przed każdą inną operacją.

Przy okazji, prostszym sposobem na zapisanie tego jest = wynagrodzenie * 110%

Nawiasy mogą być zagnieżdżane w sobie nawzajem. Tak więc, kiedy piszemy (3 + (4 * 2)), pracując od wewnątrz do zewnątrz, najpierw obliczamy 4 * 2 = 8, następnie dodajemy 3 + 8, aby uzyskać 11.

Kilka innych przykładów

Oto inny przykład: = 4 * 3 / 2. Jaka jest odpowiedź?

Z powyższej tabeli wynika, że * i / mają równy priorytet. Tak więc program Excel działa od lewej do prawej, najpierw 4 * 3 = 12, a następnie dzieli go na 2, aby uzyskać 6.

Ponownie możesz to wyrazić, pisząc (4 * 3) / 2

Co około = 4 + 3 * 2?

Komputer widzi operatory * i +. Więc postępując zgodnie z regułami pierwszeństwa (mnożenie przychodzi przed dodaniem), najpierw oblicza 3 * 2 = 6, następnie dodaje 4, aby uzyskać 10.

Jeśli chcesz zmienić kolejność pierwszeństwa, napiszesz = (4 + 3) * 2 = 14.

Co z tym jednym = -1 ^ 3?

Wtedy odpowiedź wynosi -3, ponieważ komputer obliczony = (-1) ^ 3 = -1 * -1 * -1 = -1.

Pamiętaj, że ujemny czas negatywny jest dodatni, a ujemny czas pozytywny jest ujemny. Możesz to zobaczyć w ten sposób (-1 * -1) * -1 = 1 * -1 = -1.

Jest kilka przykładów porządku matematycznego i pierwszeństwa, mamy nadzieję, że pomoże to wyjaśnić kilka rzeczy o tym, jak Excel wykonuje obliczenia (a to prawdopodobnie wystarczająca matematyka, aby przetrwać całe życie dla niektórych z was).

Przykład: funkcja płatności pożyczki (PMT)

Spójrzmy na przykład, aby obliczyć spłatę pożyczki.

Rozpocznij od utworzenia nowego arkusza roboczego.

Sformatuj cyfry za pomocą znaków dolara i użyj miejsc po przecinku, ponieważ nie jesteśmy zainteresowani centami teraz, ponieważ nie mają większego znaczenia, kiedy mówisz o dolarach (w następnym rozdziale zajmiemy się formatowaniem liczb w szczegółach). Na przykład, aby sformatować stopę procentową, kliknij komórkę prawym przyciskiem myszy i kliknij "formatuj komórki". Wybierz procent i użyj 2 miejsc po przecinku..

Podobnie sformatuj pozostałe komórki dla "waluty" zamiast procentu i wybierz "numer" dla terminu pożyczki.

Teraz mamy:

Dodaj funkcję SUMA () do "łącznych" miesięcznych wydatków.

Zanotuj hipoteka komórka nie jest zawarta w sumie. Excel nie wie, że chcesz uwzględnić ten numer, ponieważ nie ma tam żadnej wartości. Tak więc należy uważać, aby rozszerzyć funkcję SUMA () do góry albo za pomocą kursora lub wpisując E2, gdzie mówi się, że E3 zawiera hipotekę w sumie.

Umieść kursor w komórce płatniczej (B4).

W menu Formuły wybierz menu rozwijane "Finansowe", a następnie wybierz funkcję PMT. Kreator wyskoczy:

Użyj kursora, aby wybrać "stawkę", "nper" (termin spłaty), "Pv" ("aktualna wartość" lub kwota pożyczki). Zauważ, że musisz podzielić stopę procentową przez 12, ponieważ odsetki są naliczane miesięcznie. Musisz także pomnożyć okres kredytu w latach przez 12, aby uzyskać okres kredytu w miesiącach. Naciśnij "OK", aby zapisać wynik w arkuszu kalkulacyjnym.

Zauważ, że płatność jest pokazana jako liczba ujemna: -1013,37062. Aby było dodatnie i dodać je do miesięcznych wydatków, należy wskazać komórkę hipoteczną (E2). Wpisz "= -", a następnie użyj kursora, aby wskazać pole płatności. Wynikowa formuła to = -B4.

Teraz arkusz kalkulacyjny wygląda następująco:

Twoje miesięczne wydatki wynoszą 1.863 $ - Ouy!

Przykład: funkcja tekstu

Tutaj pokazujemy, jak korzystać z funkcji wewnątrz formuły i funkcji tekstowych.

Załóżmy, że masz listę studentów, jak pokazano poniżej. Imię i nazwisko znajduje się w jednym polu oddzielonym przecinkiem. Musimy umieścić ostatnie i firmowe nazwy w oddzielnych komórkach. Jak to robimy?

Aby rozwiązać ten problem, musisz użyć algorytmu - tj. Procedury krok po kroku, aby to zrobić.

Na przykład spójrz na "Washington, George". Procedura podzielenia tego na dwa słowa będzie następująca:

Oblicz długość łańcucha.
Znajdź pozycję przecinka (pokazuje, gdzie kończy się jedno słowo, a zaczyna drugie).
Skopiuj lewą stronę łańcucha aż do przecinka.
Skopiuj prawą stronę ciągu od przecinka do końca.

Porozmawiajmy o tym, jak to zrobić z "George Washington" krok po kroku w Excelu.

Obliczyć długość struny za pomocą funkcji = LEN (A3) - wynikiem jest 18.
Teraz znajdź pozycję przecinka, wchodząc w tę funkcję = ZNAJDŹ (",", A3 ") - wynikiem jest 11.
Teraz bierz lewą stronę łańcucha aż do przecinka i utwórz tę zagnieżdżoną formułę, używając wyniku z kroku 1: = LEWO (A3, FIND (",", A3) -1). Zauważ, że musimy odjąć 1 od długości, ponieważ funkcja FIND podaje pozycję przecinka.

Oto, jak to wszystko wygląda, gdy wszystkie funkcje są umieszczone razem w formule. W komórce B3 widać, że ta formuła pobiera wszystkie informacje z komórki A3 i dodaje do niej "Washington".

Więc mamy "Waszyngton", teraz musimy uzyskać "George". Jak to zrobimy?

Zauważ, że moglibyśmy zapisać wynik z kroku 1 w komórce sam, powiedzmy, B6, a następnie napisać prostszą formułę = LEWO (A3, B6-1). Ale to zużywa jedną komórkę na przerywany krok.

Zapamiętaj położenie przecinka lub oblicz go ponownie.
Oblicz długość łańcucha.
Policz znaki od końca łańcucha do przecinka.

Pobierz liczbę znaków z kroku 3 i odejmij jeden, aby pominąć przecinek i spację.

Zróbmy to krok po kroku.

Z góry jest to = ZNAJDŹ (",", A3 ")
Długość struny wynosi = LEN (A3)
Będziesz musiał użyć trochę matematyki, aby znaleźć liczbę znaków do wykonania: = LEN (A3) - FIND (",", A3) - 1
Prawa strona łańcucha, który chcemy, to = PRAWY (A3, LEN (A3) - FIND (",", A3) - 1)

Twój arkusz kalkulacyjny powinien teraz wyglądać podobnie do zrzutu ekranu poniżej. Skróciliśmy formuły jako tekst na dole arkusza kalkulacyjnego, aby ułatwić czytanie i wyświetlanie.

Ta była trochę trudna, ale musisz tylko raz zapisać te formuły.

Już idę dalej ...

To kończy naszą lekcję na dzisiaj. Powinieneś teraz dość dobrze zrozumieć formuły i funkcje, wiersze i kolumny oraz sposób, w jaki to wszystko można wykorzystać poprzez kilka konkretnych przykładów.

W następnej lekcji 3 omówimy odwołanie do komórki i formatowanie, a także przeniesienie i skopiowanie formuł, aby nie trzeba było przepisywać każdej formuły w kółko!