Дефиниране и създаване на формула

В този урок ви запознаваме с основните правила за създаване на формули и използване на функции. Смятаме, че един от най-добрите начини за учене е чрез практика, така че ние предлагаме няколко примера и ги обясняваме подробно. Темите, които ще разгледаме, включват:

НАВИГАЦИЯ НА УЧИЛИЩАТА

Защо ви трябват формули и функции?
Дефиниране и създаване на формула
Относителна и абсолютна референтна клетка и форматиране
Полезни функции, които трябва да знаете
Показвания, графики, статистически данни и обобщени таблици

редове и колони
примерна математическа функция: SUM ()
оператори
предимство на оператора
пример финансова функция: PMT (), плащане на заем
използвайки "string" функция ("string" е съкращение за "string of text") в рамките на формулата и функциите за гнездене

Формулите са смесица от „функции“, „оператори“ и „операнди“. Преди да напишем няколко формули, трябва да създадем функция, но преди да можем да създадем функция, първо трябва да разберем нотация на редове и колони..

Редове и колони

За да разберете как да пишете формули и функции, трябва да знаете за редове и колони.

Редовете се изпълняват хоризонтално, а колоните - вертикално. За да запомните коя е тази, мислете за колона, която държи покрив - колоните вървят нагоре-надолу и по този начин редовете вървят наляво-надясно.

Колоните са обозначени с букви; редове по номера. Първата клетка в електронната таблица е А1, означава колона А, ред 1. Колоните са обозначени с А-Z. Когато азбуката свърши, Excel поставя още една буква отпред: AA, AB, AC… AZ, BA, BC, BC и т.н..

Пример: Сума на функцията ()

Сега нека покажем как да използваме функция.

Използвате функции, като ги напишете директно в или с помощта на помощника за функции. Съветникът за функциите се отваря, когато или изберете функция от менюто „Формули“ от „Библиотека с функции“. В противен случай можете да въведете = в клетката и удобното падащо меню ще ви позволи да изберете функция.

Съветникът ви казва какви аргументи трябва да предоставите за всяка функция. Той също така предоставя връзка към онлайн инструкции, ако имате нужда от помощ, за да разберете какво прави функцията и как да я използвате. Например, ако въведете = sum в клетката, помощният помощник показва, какви аргументи са необходими за функцията SUM.

Когато въвеждате функция, съветникът е вграден или на пръсти. Когато изберете функция от менюто „Формули“, съветникът е изскачащ прозорец. Тук е изскачащ съветник за функцията SUM ().

За нашата първа функция, нека използваме SUM (), който добавя списък с числа.

Да предположим, че имаме тази електронна таблица, която съдържа планове за бюджетиране на ваканцията на семейството ви:

За изчисляване на общите разходи можете да напишете = b2 + b3 + b4 + b5, но е по-лесно да използвате функцията SUM ().

В Excel, потърсете символа Σ в горния ляв ъгъл на екрана на Excel, за да намерите бутона AutoSum (математиците използват гръцката буква Σ за добавяне на серия от числа).

Ако курсорът е под номерата на семейните бюджети, Excel е достатъчно умен, за да знае, че искате да сумирате списъка с цифри над мястото, където сте поставили курсора, за да подчертае числата.

Натиснете “enter”, за да приемете диапазона, избран от Excel, или използвайте курсора, за да промените кои клетки да бъдат избрани.

Ако се вгледате в това, което Excel поставя в електронната таблица, можете да видите, че той е написал тази функция:

В тази формула Excel сумира числата от B2 до B9. Имайте предвид, че оставихме една стая под ред 5, така че да можете да добавите към бюджета на семейната ваканция - цената със сигурност ще се повиши, тъй като детският списък на това, което те искат да правят и къде искат да отидат, расте!

Математическите функции не работят с букви, така че ако поставите букви в колоната, резултатът се показва като „#NAME?“, Както е показано по-долу.

#NAME? показва, че има някаква грешка. Може да са различни неща, включително:

препратка към лоша клетка
използване на букви в математически функции
пропускане на необходимите аргументи
неправилно име на функцията за правопис
незаконни математически операции като разделяне с 0

Най-лесният начин да изберете аргументите в изчислението е да използвате мишката. Можете да добавите или премахнете от списъка с аргументи функцията, като увеличите или намалите полето, което Excel изтегля, когато преместите мишката или кликнете в друга клетка.

Кликнахме върху горната част на площада, изтеглена от Excel, за да вземем „самолетни билети“ от бюджета. Можете да видите символа на кръста, който можете да нарисувате, за да направите избрания обхват по-голям или по-малък.

Натиснете “enter”, за да потвърдите резултатите.

Оператори на изчисления

Има два вида оператори: математика и сравнение.

Математически оператор	дефиниция
+	допълнение
-	изваждане, или отрицание, например 6 * -1 = -6
*	умножение
/	делене
%	на сто
^	показател, напр. 2⁴= 2 ^ 4 = 2 * 2 * 2 * 2 = 16

Има и други оператори, които не са свързани с математиката като "&", което означава свързване (свързване от две до края) на две низове. Например, "Excel" и "е забавно" е равно на "Excel е забавно".

Сега разглеждаме операторите за сравнение.

Оператор за сравнение	дефиниция
=	е равен, например, 2 = 4 или „b“ = „b“
>	по-голям от, например 4> 2 или „b“> „a“
<	по-малко от, например, 2 < 4 or “a” < “b”
> =	по-голям или равен - друг начин да се мисли за това е> = означава един > или =.
<=	по-малко или равно на.
	не е равна на, например 46

Както можете да видите по-горе, операторите за сравнение работят с числа и текст.

Забележете, че ако влезете = ”a”> ”b” в клетката ще каже “FALSE”, тъй като “a” не е по-голямо от “b”. “B” идва след “a” в азбуката, така че “a” > „Б“ или "Б"> "а."

Предпочитание на поръчката на оператора

Приоритет на поръчка е идея от математиката. Excel трябва да следва същите правила като математиката. Тази тема е по-сложна, така че поемете дъх и да се потопим.

Предпочитание на поръчката означава реда, в който компютърът изчислява отговора. Както обяснихме в Урок 1, площта на окръжността е πr², което е същото като π * r * r. то е не (Πr)².

Така че трябва да разберете предимството на поръчката, когато пишете формула.

Обикновено можете да кажете следното:

Excel първо оценява елементите в скоби, които работят отвътре.
След това се използват правилата за приоритет на математиката.
Когато две позиции имат едно и също предимство, Excel работи от ляво на дясно.

Предимството на математическите оператори е показано по-долу, в низходящ ред.

( и )	Когато се използват скоби, те отменят нормалните правила за предимство. Това означава, че Excel първо ще направи това изчисление. По-долу ще обясним това.
-	Отрицание, например -1. Това е същото като умножаване на число с -1. -4 = 4 * (-1)
%	Процент, означава умножение по 100. Например, 0.003 = 0.3%.
^	Експонент, например 10 ^ 2 = 100
* и /	Умножете и разделете. Как два оператора могат да имат едно и също предимство? Това просто означава, че ако формулата има още два оператора със същия приоритет, тогава изчислението се прави отляво надясно.
+ и -	Добавяне и изваждане.

Има и други правила за предимство, свързани с низове и референтни оператори. За момента просто ще се придържаме към това, което току-що покрихме. Сега нека разгледаме някои примери.

Пример: Изчисляване на площта на кръга

Областта на кръга е= PI () * радиус ^ 2.

Гледайки таблицата по-горе, виждаме, че експонентите идват преди умножение. Така компютърът първо изчислява радиус ^ 2 и след това мултиплицира този резултат от Pi.

Пример: Изчисляване на увеличение на заплатата

Да речем, че шефът ти решава, че вършиш страхотна работа и той или тя ще ти даде 10% увеличение! Как ще изчислиш новата си заплата?

Първо, не забравяйте, че умножението идва преди добавянето.

= Заплата + заплата * 10% или = заплата + (заплата * 10%)?

Да предположим, че вашата заплата е $ 100. С 10% увеличение, новата Ви заплата ще бъде:

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

Можете също да го напишете по следния начин:

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

Във втория случай правим изричен ред на предимство с помощта на скоби. Не забравяйте, че скобите се оценяват преди всяка друга операция.

Между другото, по-лесният начин да напишете това е = заплата * 110%

Скобите могат да бъдат вмъкнати един в друг. Така че, когато пишем (3 + (4 * 2)), работим отвътре навън, първо изчислява 4 * 2 = 8, след това добавяме 3 + 8, за да получим 11.

Още няколко примера

Ето още един пример: = 4 * 3 / 2. Какъв е отговорът?

От правилата в таблицата по-горе виждаме, че * и / имат равен приоритет. Така Excel работи от ляво на дясно, първо 4 * 3 = 12, след което разделя това на 2, за да получи 6.

Отново можете да направите това изрично, като напишете = (4 * 3) / 2

Какво за = 4 + 3 * 2?

Компютърът вижда операторите * и +. Така следвайки правилата за предимство (умножението идва преди добавянето) той изчислява 3 * 2 = 6 първо, след което добавя 4, за да получи 10.

Ако искате да промените реда на приоритет, ще напишете = (4 + 3) * 2 = 14.

Какво ще кажете за този = -1 ^ 3?

Тогава отговорът е -3, защото компютърът изчислява = (-1) ^ 3 = -1 * -1 * -1 = -1.

Не забравяйте, че отрицателните отрицателни времена са положителни, а отрицателните - положителни. Можете да видите това по следния начин (-1 * -1) * -1 = 1 * -1 = -1.

Така че има няколко примера за математически ред и предимство, надяваме се, че това ще ви помогне да изчистите няколко неща за това как Excel изпълнява изчисления (и това вероятно е достатъчно, за да издържи цял живот за някои от вас).

Пример: Плащане по функционален заем (PMT)

Нека да разгледаме един пример за изчисляване на плащането на заем.

Започнете, като създадете нов работен лист.

Форматирайте номерата със знаци за долар и използвайте нула след десетичната запетая, тъй като ние не се интересуваме от цента в момента, защото те нямат голямо значение, когато говорите за долари (в следващата глава ще разгледаме как да се форматират числата в детайли). Например, за да форматирате лихвения процент, щракнете с десния бутон върху клетката и щракнете върху "формат клетки". Изберете процент и използвайте 2 знака след десетичната запетая.

По същия начин форматирайте останалите клетки за „валута“ вместо процент и изберете „номер“ за срока на заема.

Сега имаме:

Добавете функцията SUM () към „общи“ месечни разходи.

Забележка: ипотека клетката не е включена в общата сума. Excel не знае, че искате да включите този номер, тъй като там няма стойност. Така че внимавайте да разширите функцията SUM () до върха, като използвате курсора или въведете E2, където се казва, че E3 включва ипотеката в сумата.

Поставете курсора в клетката за плащане (B4).

В менюто Формули изберете падащото меню „Финансово” и след това изберете функцията PMT. Появява се съветникът:

Използвайте курсора, за да изберете “rate.”, “Nper” (срок на заема), “Pv” (“настояща стойност” или сума на заема). Забележете, че трябва да разделите лихвения процент на 12, тъй като лихвата се изчислява месечно. Също така трябва да умножите срока на кредита в години с 12, за да получите срока на кредита в месеци. Натиснете “OK”, за да запишете резултата в електронната таблица.

Обърнете внимание, че плащането се показва като отрицателно число: -1013.37062. За да го направите положителна и да я добавите към месечните разходи, посочете ипотечната клетка (E2). Въведете „= -“, след което използвайте курсора, за да посочите полето за плащане. Получената формула е = -B4.

Сега електронната таблица изглежда така:

Вашите месечни разходи са 1863 долара - Ouch!

Пример: Функция за текст

Тук ще покажем как да използваме функции във функциите на формула и текст.

Да предположим, че имате списък с ученици, както е показано по-долу. Първото и последното име са в едно поле, разделени със запетая. Трябва да поставим последните и твърди имена в отделни клетки. Как да направим това?

За да се справите с този проблем, трябва да използвате алгоритъм, т.е. стъпка по стъпка, за да направите това.

Например, вижте „Вашингтон, Джордж“. Процедурата за разделяне на две думи ще бъде:

Изчислете дължината на низа.
Намерете позицията на запетая (това показва къде завършва една дума, а другата започва).
Копирайте лявата страна на нишката до запетая.
Копирайте дясната страна на низ от запетая до края.

Нека обсъдим как да направим това с “Джордж Вашингтон” стъпка по стъпка в Excel.

Изчислете дължината на низа с функцията = LEN (A3) - резултатът е 18.
Сега намерете позицията на запетая, като въведете тази функция = FIND (“,”, A3 ”) - резултатът е 11.
Сега вземете лявата страна на низа до запетаята и създайте тази вложена формула като използвате резултата от стъпка 1: = НАЛЯВО (A3, FIND (“,”, A3) -1). Забележете, че трябва да извадим 1 от дължината, защото FIND дава позицията на запетая.

Ето как изглежда всичко това, когато всички функции са поставени заедно във формула. В клетка B3 можете да видите тази формула да вземе цялата информация от клетка A3 и да въведе „Вашингтон“ в нея.

Така че ние имаме „Вашингтон“, сега трябва да получим „Джордж“?

Забележете, че бихме могли да запазим резултата от Стъпка 1 в клетка сама по себе си, да речем, B6, след това да напишем по-проста формула = LEFT (A3, B6-1). Но това използва една клетка за интермитентната стъпка.

Запомнете позицията на запетая или го изчислете отново.
Изчислете дължината на низа.
Пребройте знаците от края на низа до запетая.

Вземете броя на знаците от Стъпка 3 и извадете един, за да пропуснете запетаята и интервала.

Нека направим това стъпка по стъпка.

От горе, това е = FIND (“,”, A3 ”)
Дължината на низа е = LEN (A3)
Ще трябва да използвате някаква математика, за да намерите броя на символите: = LEN (A3) - FIND (“,”, A3) - 1
Дясната страна на низа, която искаме, е = RIGHT (A3, LEN (A3) - FIND („,“, A3) - 1)

Вашата електронна таблица сега трябва да изглежда подобна на екранната снимка по-долу. Копирахме формулите като текст в долната част на електронната таблица, за да го направим по-лесен за четене и разглеждане.

Това беше малко трудно, но само трябва да напишете тези формули веднъж.

Следва…

Това завършва нашия урок за днес. Сега трябва да имате доста ясно разбиране за формули и функции, редове и колони, както и начина, по който всичко това може да се използва чрез няколко конкретни примера..

Следвайки следващия урок 3, ще обсъдим позоваването на клетките и форматирането, както и преместването и копирането на формули, така че да не се налага да презаписвате всяка формула отново и отново!