W tej lekcji zapoznajemy Cię z podstawowymi zasadami tworzenia formuł i korzystania z funkcji. Uważamy, że jednym z najlepszych sposobów nauki jest praktyka, dlatego przedstawiamy kilka przykładów i szczegółowo je objaśniamy. Tematy, które omówimy, to:
- wiersze i kolumny
- przykładowa funkcja matematyczna: SUM()
- operatory
- pierwszeństwo operatorów
- przykładowa funkcja finansowa: PMT(), spłata pożyczki
- użycie funkcji „string” („string” to skrót od „ciąg tekstu”) wewnątrz formuły i zagnieżdżanie funkcji
Formuły są mieszanką „funkcji”, „operatorów” i „operandów”. Zanim napiszemy kilka formuł, musimy stworzyć funkcję, ale zanim będziemy mogli stworzyć funkcję, musimy najpierw zrozumieć notację wierszy i kolumn.
Wiersze i kolumny
Aby zrozumieć, jak pisać formuły i funkcje, musisz wiedzieć o wierszach i kolumnach.
Wiersze biegną poziomo, a kolumny pionowo. Aby zapamiętać, która z nich jest która, pomyśl o kolumnie podtrzymującej dach – kolumny idą z góry na dół, a wiersze z lewej strony na prawą.
Kolumny są oznaczane literami, a wiersze liczbami. Pierwsza komórka w arkuszu to A1, czyli kolumna A, wiersz 1. Kolumny są oznaczone literami A-Z. Gdy w alfabecie zabraknie liter, Excel umieszcza przed nimi kolejną literę: AA, AB, AC… AZ, BA, BC, BC itd.
Przykład: Funkcja Suma()
Zademonstrujmy teraz, jak używać funkcji.
Funkcji używamy, wpisując je bezpośrednio lub korzystając z kreatora funkcji. Kreator funkcji otwiera się, gdy wybierzesz funkcję z menu „Formuły” z „Biblioteki funkcji”. W przeciwnym razie możesz wpisać = w komórce, a poręczne rozwijane menu pozwoli Ci wybrać funkcję.
Kreator podpowiada, jakie argumenty musisz podać dla każdej funkcji. Podaje również link do instrukcji online, jeśli potrzebujesz pomocy w zrozumieniu, co dana funkcja robi i jak jej używać. Na przykład, jeśli wpiszesz w komórce =suma, kreator w linii pokaże Ci, jakie argumenty są wymagane dla funkcji SUMA.
Gdy wpisujesz funkcję, kreator jest w linii, czyli tuż przy Twoich palcach. Gdy wybierasz funkcję z menu „Formuły”, kreator jest wyskakującym okienkiem. Oto wyskakujący kreator dla funkcji SUM().
Dla naszej pierwszej funkcji użyjmy SUM(), która dodaje listę liczb.
Załóżmy, że mamy taki arkusz kalkulacyjny zawierający plany budżetowe na wakacje Twojej rodziny:
Aby obliczyć całkowite koszty, mógłbyś napisać =b2+b3+b4+b5, ale łatwiej jest użyć funkcji SUM().
W programie Excel należy poszukać symbolu Σ w lewym górnym rogu ekranu programu Excel, aby znaleźć przycisk AutoSum (matematycy używają greckiej litery Σ do dodawania serii liczb).
Jeśli kursor znajduje się poniżej liczb budżetu rodzinnego, Excel jest na tyle inteligentny, że wie, iż chcesz zsumować listę liczb znajdujących się powyżej miejsca, w którym umieściłeś kursor, więc podświetla te liczby.
Naciśnij „enter”, aby zaakceptować zakres wybrany przez Excela, lub użyj kursora, aby zmienić, które komórki są wybrane.
Jeśli spojrzysz na to, co Excel umieścił w arkuszu kalkulacyjnym, zobaczysz, że napisał taką funkcję:
W tej formule Excel sumuje liczby od B2 do B9. Zauważ, że zostawiliśmy trochę miejsca pod wierszem 5, abyś mógł dołożyć do budżetu rodzinnego na wakacje – koszt z pewnością wzrośnie, gdy lista dzieci, co chcą robić i gdzie chcą pojechać, będzie się wydłużać!
Funkcje matematyczne nie działają z literami, więc jeśli wstawisz litery do kolumny, wynik zostanie wyświetlony jako „#NAME?”, jak pokazano poniżej.
#NAME? wskazuje, że wystąpił jakiś błąd. Może to być dowolna liczba rzeczy, w tym:
- złe odwołanie do komórki
- użycie liter w funkcjach matematycznych
- pominięcie wymaganych argumentów
- zły zapis nazwy funkcji
- nielegalne operacje matematyczne, takie jak dzielenie przez 0
Najprostszym sposobem na wybranie argumentów w obliczeniach jest użycie myszy. Możesz dodać do listy argumentów funkcji lub usunąć je z niej, powiększając lub pomniejszając pole, które Excel rysuje po przesunięciu myszy lub kliknięciu w inną komórkę.
Kliknęliśmy w górnej części kwadratu narysowanego przez Excela, aby wyjąć z budżetu „bilety lotnicze”. Widać symbol krzyżyka, który można narysować, aby powiększyć lub zmniejszyć wybrany zakres.
Naciśnij „enter”, aby potwierdzić wyniki.
Operatory obliczeniowe
Istnieją dwa rodzaje operatorów: matematyczny i porównawczy.
Istnieją też inne operatory nie związane z matematyką jak „&”, który oznacza konkatenację (połączenie koniec do końca) dwóch ciągów znaków. Na przykład, =”Excel” & ” is Fun” równa się „Excel is Fun”.
Teraz przyjrzymy się operatorom porównania.
Jak widać powyżej, operatory porównania działają z liczbami i tekstem.
Uwaga, jeśli wpiszesz =”a”>”b” do komórki, to powie „FAŁSZ”, ponieważ „a” nie jest większe niż „b”. „b” pojawia się po „a” w alfabecie, więc „a” > „b” lub „b” > „a.”
Operator Order Precedence
Order precedence to pomysł z matematyki. Excel musi przestrzegać tych samych zasad, co matematyka. Ten temat jest bardziej skomplikowany, więc weź oddech i zagłębmy się w niego.
Pierwszeństwo rzędu oznacza kolejność, w jakiej komputer oblicza odpowiedź. Jak wyjaśniliśmy w Lekcji 1, pole koła wynosi πr2, czyli π * r * r. Nie jest to (πr)2.
Więc musisz zrozumieć pierwszeństwo kolejności, gdy piszesz formułę.
Ogólnie można powiedzieć, że:
- Excel najpierw oblicza elementy w nawiasach, pracując od wewnątrz do zewnątrz.
- Następnie wykorzystuje matematyczne reguły pierwszeństwa kolejności.
- Gdy dwa elementy mają takie samo pierwszeństwo, Excel działa od lewej do prawej.
Pierwszeństwo operatorów matematycznych jest pokazane poniżej, w kolejności malejącej.
Istnieją inne reguły pierwszeństwa związane z ciągami znaków i operatorami odniesienia. Na chwilę obecną, będziemy trzymać się tego, co właśnie omówiliśmy. Teraz, spójrzmy na kilka przykładów.
Przykład: Obliczanie powierzchni okręgu
Powierzchnia okręgu to =PI() * promień ^ 2.
Patrząc na powyższą tabelę widzimy, że wykładniki pojawiają się przed mnożeniem. Zatem komputer najpierw oblicza promień ^ 2, a następnie mnoży ten wynik przez Pi.
Przykład: Obliczanie podwyżki pensji
Powiedzmy, że Twój szef zdecydował, że wykonujesz świetną pracę i ma zamiar dać Ci 10% podwyżki! Jak obliczyłbyś swoją nową pensję?
Po pierwsze, pamiętaj, że mnożenie jest przed dodawaniem.
Czy jest to = pensja + pensja * 10%, czy = pensja + (pensja * 10%)?
Załóżmy, że Twoja pensja wynosi 100$. Z 10% podwyżką, twoja nowa pensja będzie:
= 100 + 100 * 10% = 100 + 10 = 110
Możesz również napisać to w ten sposób:
=100 + (100 * 10%) = 100 + 10 = 110
W drugim przypadku, mamy wyjaśnioną kolejność pierwszeństwa poprzez użycie nawiasów. Pamiętaj, że nawiasy są oceniane przed każdą inną operacją.
Przy okazji, łatwiejszy sposób zapisu to = pensja * 110%
Kilka innych przykładów
Jest jeszcze jeden przykład: = 4 * 3 / 2. Jaka jest odpowiedź?
Z reguł w powyższej tabeli wynika, że * i / mają równe pierwszeństwo. Tak więc Excel działa od lewej do prawej, 4 * 3 = 12 najpierw, a następnie dzieli to przez 2, aby uzyskać 6.
Znowu można to wyjaśnić, pisząc = (4 * 3) / 2
A co z = 4 + 3 * 2?
Komputer widzi oba operatory * i +. Więc zgodnie z regułami pierwszeństwa (mnożenie jest przed dodawaniem) najpierw oblicza 3 * 2 = 6, a następnie dodaje 4, aby otrzymać 10.
Jeśli chciałbyś zmienić kolejność pierwszeństwa, napisałbyś = (4 + 3) * 2 = 14.
A co z tym = -1 ^ 3?
Wtedy odpowiedź brzmi -3, ponieważ komputer obliczył = (-1) ^ 3 = -1 * -1 * -1 = -1.
Więc mamy kilka przykładów matematycznej kolejności i pierwszeństwa, mamy nadzieję, że pomoże to wyjaśnić kilka rzeczy o tym, jak Excel wykonuje obliczenia (i to prawdopodobnie wystarczająco dużo matematyki, aby przetrwać całe życie dla niektórych z was).
Przykład: Funkcja Spłata kredytu (PMT)
Przyjrzyjrzyjmy się przykładowi obliczania spłaty kredytu.
Zacznij od utworzenia nowego arkusza.
Formatuj liczby za pomocą znaków dolara i użyj zerowych miejsc po przecinku, ponieważ nie interesują nas teraz centy, ponieważ nie mają one większego znaczenia, gdy mówimy o dolarach (w następnym rozdziale szczegółowo zbadamy, jak formatować liczby). Na przykład, aby sformatować stopę procentową, kliknij prawym przyciskiem myszy na komórkę i kliknij „formatuj komórki”. Wybierz procent i użyj dwóch miejsc po przecinku.
Podobnie sformatuj pozostałe komórki na „walutę” zamiast procentu i wybierz „liczbę” dla okresu kredytowania.
Teraz mamy:
Dodaj funkcję SUM(), aby „zsumować” miesięczne wydatki.
Uwaga, komórka z hipoteką nie jest uwzględniana w sumie. Excel nie wie, że chcesz uwzględnić tę liczbę, ponieważ nie ma tam żadnej wartości. Należy więc uważać, aby funkcję SUM() wysunąć do góry albo kursorem, albo wpisując E2 w miejscu, gdzie jest napisane E3, aby hipoteka została uwzględniona w sumie.
Postaw kursor w komórce płatności (B4).
W menu Formuły wybierz rozwijaną opcję „Finansowe”, a następnie wybierz funkcję PMT. Pojawi się kreator:
Wybierz kursorem „rate.”, „nper” (okres kredytowania), „Pv” („present value” lub kwota kredytu). Zauważ, że musisz podzielić stopę procentową przez 12, ponieważ odsetki są naliczane co miesiąc. Ponadto musisz pomnożyć okres kredytowania w latach przez 12, aby otrzymać okres kredytowania w miesiącach. Naciśnij „OK”, aby zapisać wynik w arkuszu kalkulacyjnym.
Zauważ, że płatność jest pokazana jako liczba ujemna: -1013.37062. Aby nadać jej wartość dodatnią i dodać ją do miesięcznych wydatków, wskaż komórkę z kredytem hipotecznym (E2). Wpisz znak „=-„, a następnie kursorem wskaż pole płatności. Otrzymana formuła to =-B4.
Teraz arkusz wygląda tak:
Twoje miesięczne wydatki wynoszą $1,863 – Auć!
Przykład: Funkcja tekstowa
Tutaj zademonstrujemy, jak używać funkcji wewnątrz formuły oraz funkcji tekstowych.
Załóżmy, że masz listę studentów, jak pokazano poniżej. Imię i nazwisko znajduje się w jednym polu oddzielone przecinkiem. Musimy umieścić nazwisko i firmę w osobnych komórkach. Jak to zrobić?
Aby poradzić sobie z tym problemem, musisz użyć algorytmu – czyli procedury krok po kroku, jak to zrobić.
Na przykład spójrz na „Washington, George.” Procedura dzielenia tego na dwa słowa byłaby następująca:
- Oblicz długość ciągu.
- Znajdź pozycję przecinka (to pokazuje, gdzie kończy się jedno słowo, a zaczyna drugie).
- Kopiuj lewą stronę ciągu aż do przecinka.
- Kopiuj prawą stronę ciągu od przecinka do końca.
Przedyskutujmy, jak to zrobić z „George Washington” krok po kroku w programie Excel.
- Oblicz długość ciągu za pomocą funkcji =LEN(A3) – wynik to 18.
- Teraz znajdź położenie przecinka wpisując funkcję =FIND(„,”,A3″) – wynik to 11.
- Teraz weź lewą stronę ciągu aż do przecinka i utwórz tę zagnieżdżoną formułę używając wyniku z kroku 1: =LEFT(A3,FIND(„,”,A3)-1). Zauważ, że musimy odjąć 1 od długości, ponieważ funkcja ZNAJDŹ podaje pozycję przecinka.
A oto jak to wszystko wygląda, gdy wszystkie funkcje są umieszczone razem w formule. W komórce B3 widać, że formuła ta pobiera wszystkie informacje z komórki A3 i wprowadza do niej „Washington”.
Mamy więc „Washington”, teraz musimy uzyskać „George”. Jak to zrobić?
Zauważ, że mogliśmy zapisać wynik z kroku 1 w komórce, powiedzmy B6, a następnie napisać prostszą formułę =LEFT(A3,B6-1). Ale to zużywa jedną komórkę na krok przerywany.
- Zapamiętaj położenie przecinka lub oblicz go ponownie.
- Oblicz długość ciągu.
- Oblicz znaki od końca ciągu do przecinka.
Wyciągnij liczbę znaków z kroku 3 i odejmij jeden, aby pominąć przecinek i spację.
Zróbmy to krok po kroku.
Twój arkusz kalkulacyjny powinien teraz wyglądać podobnie do poniższego zrzutu ekranu. Skopiowaliśmy formuły jako tekst do dolnej części arkusza kalkulacyjnego, aby ułatwić ich odczytanie i wyświetlenie.
Ta lekcja była trochę trudna, ale formuły wystarczy napisać tylko raz.
Następna lekcja …
To kończy naszą dzisiejszą lekcję. Powinieneś mieć teraz dość mocne zrozumienie formuł i funkcji, wierszy i kolumn, oraz sposobu w jaki to wszystko może być użyte poprzez kilka konkretnych przykładów.