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

2024 Автор: Geoffrey Carr | [email protected]. Последно модифициран: 2024-01-31 12:53

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

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

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

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

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

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

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

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

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

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

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

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

Помощникът ви разяснява какви аргументи трябва да предоставите за всяка функция. Той също така осигурява връзка към онлайн инструкции, ако имате нужда от помощ, за да разберете какво прави функцията и как да я използвате. Например, ако въведете = сумата в клетка, помощникът в реда ви показва какви аргументи се изискват за функцията 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"> "а"
<	малко от, например, 2 <4 или "a" <b"
>=	по-голяма или равна на - друг начин да се мисли за това е> = означава един > или =.
<=	по-малко или равно на.
	не е равно на например 4 <6

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

Забележете, че ако въведете "a"> "b" в клетка, то ще каже "FALSE", тъй като "a" не е по-голямо от "b". "B" > "B" или "B"> "a."

Предпоставка за поръчка на оператор

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

Приоритет на поръчката означава реда, в който компютърът изчислява отговора. Както обяснихме в Урок 1, областта на кръга е πр², което е същото като π * 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. Съветникът се появява:

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

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

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

Вашите месечни разходи са $ 1,863 - Ouch!

Пример: Текстова функция

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

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

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

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

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

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

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

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

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

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

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

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

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

Отгоре, това е = FIND (",", A3 ")
Дължината на низа е = LEN (A3)
Ще трябва да използвате някои математически знаци, за да намерите броя знаци, които трябва да направите: = LEN (A3) - FIND (",", A3) - 1
Дясната страна на низа, която искаме, е = НАДЯСНО (A3, LEN (A3)) - FIND ("," A3)

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

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

Следва …

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

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