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

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

Ето бърз поглед към темите, които са обсъдени тук:

• Какво е формула?
• Писане на формули на Excel
• Редактиране на формула
• Копирайте / поставете формула
• Скриване на формули в Excel
• Формули на Excel за предимство на оператора
• Какво представляват функциите в Excel?
• Най-важните функции

Какво е формула?

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

Формулите на Excel могат да бъдат от следните типове:

Писане на формули на Excel:

За да напишете формула в клетка на лист на Excel, можете да направите следното:

• Изберете клетката, в която искате да се покаже резултатът
• Първоначално въведете знак „=“, за да уведомите Excel, че ще въведете формула в тази клетка
• След това можете да въведете адресите на клетките или да посочите стойностите, които възнамерявате да изчислите
• Например, ако искате да добавите стойностите на две клетки, можете да въведете адреса на клетката, както следва:

• Можете също така да изберете клетките, чиято сума искате да изчислите, като изберете всички необходими клетки, докато натискате клавиша Ctrl:

Редактиране на формула:

В случай, че искате да редактирате някаква предварително въведена формула, просто изберете клетката, която съдържа целевата формула и в лентата с формули можете да направите желаните промени. В предишния пример изчислих сумата от А1 и А2. Сега ще редактирам същото и ще променя формулата, за да изчисля произведението на стойностите, присъстващи в тези две клетки:

След като направите това, натиснете Enter, за да видите желания изход.

Копирайте или поставете формула:

Excel е много полезен, когато трябва да копирате / поставите формули. Всеки път, когато копирате формула, Excel автоматично се грижи за препратките към клетки, които се изискват на тази позиция. Това става чрез система, наречена Относителни клетъчни адреси .

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

Както можете да видите на изображението, формулата е написана първоначално в A3 и след това я изтеглих надолу по B3 и C3, за да изчисля сумата от B1, B2 и C1, C2, без да записвам изключително адресите на клетките.

В случай, че променя стойностите на някоя от клетките, изходът ще се актуализира автоматично от Excel в съответствие с Относителни клетъчни адреси , Абсолютни клетъчни адреси или Смесени клетъчни адреси .

Скриване на формули в Excel:

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

• Изберете клетките, чиято формула възнамерявате да скриете
• Отворете прозореца Шрифт и изберете екрана Защита
• Проверете опцията Скрито и кликнете върху OK
• След това от раздела на лентата изберете Преглед
• Щракнете върху Защитен лист (Формулите няма да работят, ако не направите това)
• Excel ще ви помоли да въведете парола, за да покажете формулите за бъдеща употреба

Предимство на оператора на формули на Excel:

Формулите на Excel следват правилата BODMAS (Brackets Order Division Multiplication Addition Subction). Ако имате формула, която съдържа скоби, изразът в скобите ще бъде решен преди всяка друга част от пълната формула. Погледнете изображението по-долу:

Както можете да видите в горния пример, имам формула, която се състои от скоба. Така че в съответствие с правилата BODMAS, Excel първо ще намери разликата между A2 и B1 и след това добавя резултата с A1.

Какво са ‘Функции’ в Excel?

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

Формулите в Excel са разделени на следните категории:

Сега нека проверим как да използваме някои от най-важните и често използвани формули на Excel.

Най-важните функции на Excel:

Ето някои от най-важните функции на Excel заедно с техните описания и примери.

ДАТА:

Една от най-важните и широко използвани функции Date в Excel е функцията DATE. Синтаксисът му е следният:

ДАТА (година, месец, ден)

Тази функция връща число, което представлява дадената дата във формата за дата и час на MS Excel. Функцията DATE може да се използва, както следва:

ПРИМЕР:

1. = ДАТА (2019,11,7)
2. = DATE (2019,11,7) -7 (връща текущата дата - седем дни)

ДЕН:

Тази функция връща дневната стойност на месеца (1-31). Синтаксисът му е следният:

DAY (сериен_номер)

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

ПРИМЕР:

1. = ДЕН (A7)
2. = ДЕН (ДНЕС ())
3. = ДЕН (ДАТА (2019, 11,8))

възходящ ред c ++

МЕСЕЦ:

Подобно на функцията DAY, Excel предоставя друга функция, т.е. функцията MONTH за извличане на месеца от определена дата. Синтаксисът е както следва:

MONTH (сериен_номер)

ПРИМЕР:

1. = МЕСЕЦ (ДНЕС ())
2. = МЕСЕЦ (ДАТА (2019, 11,8))

Процент:

Както всички знаем, Процентът е съотношението, изчислено като част от 100. Може да се обозначи както следва:

Процент = (Част / Цялост) x 100

В Excel можете да изчислите процента на желаните стойности. Например, ако имате стойности за част и цели в А1 и А2 и искате да изчислите процента, можете да го направите, както следва:

• Изберете клетката, където искате да покажете резултата
• Напишете знак '='
• След това въведете формулата като A1 / A2 и натиснете Enter
• От групата номера на началните раздели изберете символа „%“

АКО:

Операторът IF е условен израз, който връща True, когато зададеното условие е изпълнено, и Flase, когато условието не е. Excel предоставя вградена функция „IF“, която служи за тази цел. Синтаксисът му е както следва:

АКО (логически_тест, стойност_ако_истина, стойност_ако_фалши)

Тук, логически_ тест е условието, което трябва да се провери

ПРИМЕР:

• Въведете стойностите, които ще се сравняват
• Изберете клетката, която ще показва изхода
• Въведете в лентата за формули „= IF (A1 = A2,„ Yes “,„ No “) и натиснете Enter

ПРЕГЛЕД:

Тази функция се използва, за да търси и извлича някои конкретни данни от колона от лист на Excel. „V“ във VLOOKUP означава вертикално търсене. Това е една от най-важните и широко използвани формули в Excel и за да се използва тази функция, таблицата трябва да бъде сортирана във възходящ ред. Синтаксисът на тази функция е както следва:

VLOOKUP (lookup_value, table_array, col_index, num_range, lookup)

където,

lookup_value е стойността, която трябва да се търси

масив_ масив е таблицата, която трябва да се търси

col_index е колоната, от която трябва да се извлече стойността

range_lookup (по избор) връща TRUE за прибл. съвпадение и FALSE за точно съвпадение

ПРИМЕР:

Както можете да видите на изображението, стойността, която посочих, е 2, а диапазонът на таблицата е между A1 и D4. Искам да извлека името на служителя, затова дадох стойността на колоната като 2 и тъй като искам да е точно съвпадение, използвах False за търсене на диапазон.

Данък общ доход:

Да предположим, че искате да изчислите данъка върху доходите на лице, чиято обща заплата е $ 300. Ще трябва да изчислите данъка върху дохода, както следва:

• Избройте общата заплата, удръжките на заплатите, облагаемия доход и процента на данъка върху дохода
• Посочете стойностите на Обща заплата, Удръжки на заплати
• След това изчислете облагаемия доход, като намерите разликата между общите удръжки и удръжки на заплати
• Накрая изчислете данъчната сума

СУММА:

Функцията SUM в Excel изчислява резултата чрез добавяне на всички посочени стойности в Excel. Синтаксисът на тази функция е както следва:

SUM (номер1, номер2, ...)

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

ПРИМЕР:

В случай, че искате да изчислите сумата от сумата, която сте похарчили за закупуване на зеленчуци, посочете всички цени и след това използвайте формулата SUM, както следва:

Съставна лихва:

За да изчислите сложната лихва, можете да използвате една от формулите на Excel, наречена FV. Тази функция ще върне бъдещата стойност на инвестицията въз основа на периодични, постоянни лихвени проценти и плащания. Синтаксисът на тази функция е както следва:

FV (скорост, nper, pmt, pv, тип)

За да изчислите лихвения процент, ще трябва да разделите годишния лихвен процент на броя периоди, т.е. годишен лихвен процент / периоди. Броят на периодите или nper се изчислява чрез умножаване на срока (брой години) с периодите, т.е. срок * периоди. pmt означава периодично плащане и може да бъде всякаква стойност, включително нула.

Помислете за следния пример:

В горния пример изчислих сложната лихва за $ 500 при ставка от 10% за 5 години и приемайки, че стойността на периодичното плащане е 0. Моля, имайте предвид, че съм използвал -B1 в смисъл, 500 долара са ми взети.

Средно аритметично:

Средното, както всички знаем, изобразява средната стойност на редица стойности. В Excel средната стойност може лесно да бъде изчислена с помощта на вградената функция, наречена „СРЕДНА“. Синтаксисът на тази функция е както следва:

СРЕДЕН (номер1, номер2, ...)

ПРИМЕР:

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

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

БРОЯ:

Функцията за броене в Excel ще отчита броя на клетките, съдържащи числа в даден диапазон. Синтаксисът на тази функция е както следва:

БРОЙ (стойност1, стойност2, ...)

ПРИМЕР:

В случай, че искам да изчисля броя на клетките, съдържащи числа от таблицата, която създадох в предишния пример, просто ще трябва да избера клетката, в която искам да покажа резултата, и след това да използвам функцията COUNT, както следва:

КРЪГЪЛ:

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

как да създам свързан списък в c

КРЪГЛО (число, брой_цифри)

ПРИМЕР:

Намиране на клас:

За да намерите оценки, ще трябва да използвате вложени оператори IF в Excel. Например в средния пример бях изчислил средната оценка, отбелязана от учениците в тестовете. Сега, за да намеря оценките, постигнати от тези ученици, ще трябва да създам вложена IF функция, както следва:

Както можете да видите, средните оценки присъстват в колона G. За да изчисля оценката, използвах вложена формула IF. Кодът е както следва:

= IF (G19> 90, 'A', (IF (G19> 75, 'B', IF (G19> 60, 'C', IF (G19> 40, 'D', 'F'))))))

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

РАНГ:

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

РАНГ (реф., Номер, поръчка)

ПРИМЕР:

Както можете да видите, в горния пример съм изчислил Ранга на учениците, използвайки функцията Ранг. Тук първият параметър е средната оценка, постигната от всеки ученик, а масивът е средната стойност, постигната от всички останали ученици от класа. Не съм посочил никакъв ред, следователно изходът ще бъде определен в низходящ ред. За възходящи подреждания ще трябва да посочите всякаква ненулева стойност.

COUNTIF:

За да преброите клетките въз основа на дадено условие, можете да използвате една от вградените формули на Excel, наречена „COUNTIF“. Тази функция ще върне броя на клетките, които отговарят на някакво условие в даден диапазон. Синтаксисът на тази функция е както следва:

COUNTIF (диапазон, критерии)

ПРИМЕР:

Както можете да видите, в горния пример открих броя на клетките със стойности, които са по-големи от 80. Можете също така да дадете някаква текстова стойност на параметъра на критериите.

ИНДЕКС:

Функцията INDEX връща стойност или препратка към клетка на определена позиция в определен диапазон. Синтаксисът на тази функция е както следва:

INDEX (масив, номер на ред, номер на колона) или

ИНДЕКС (справка,номер на ред, номер на колона, номер на площ)

Индексната функция работи, както следва за Масив форма:

• Ако са предоставени както номер на ред, така и номер на колона, той връща стойността, която присъства в пресечната клетка
• Ако стойността на реда е зададена на нула, тя ще върне стойностите, присъстващи в цялата колона в посочения диапазон
• Ако стойността на колоната е зададена на нула, тя ще върне стойностите, присъстващи в целия ред в посочения диапазон

Индексната функция работи, както следва за Справка форма:

• Връща препратката към клетката, където стойностите на реда и колоната се пресичат
• Area_num ще посочи кой диапазон трябва да се използва в случай, че са предоставени множество диапазони

ПРИМЕР:

Както можете да видите, в горния пример използвах функцията INDEX, за да определя стойността, присъстваща във 2-ри ред и 4-та колона за диапазона от клетки между A18 до G20.

По същия начин можете да използвате и функцията INDEX, като посочите множество препратки, както следва:

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

Имате въпрос към нас? Моля, споменете го в раздела за коментари в този блог „Формули и функции на Excel“ и ние ще се свържем с вас възможно най-скоро.

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