Формули на Excel & Функции: Научете с основни ПРИМЕРИ

Съдържание:

Anonim

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

В тази статия ще разгледаме следните теми.

  • Какво е формули в Excel?
  • Грешки, които трябва да се избягват при работа с формули в Excel
  • Какво представлява функцията в Excel?
  • Значението на функциите
  • Общи функции
  • Числови функции
  • Струнни функции
  • Функции за дата и час
  • V Функция за търсене

Данни за уроци

За този урок ще работим със следните набори от данни.

Бюджет за домашни консумативи

S / N ВЕЩ КОЛКО ЦЕНА ОБЩО Достъпно ли е?
1 Манго 9 600
2 Портокали 3 1200
3 Домати 1 2500
4 Олио 5 6500
5 Тонизираща вода 13 3900

График на проекта за строителство на къщи

S / N ВЕЩ НАЧАЛНА ДАТА КРАЙНА ДАТА ПРОДЪЛЖИТЕЛНОСТ (ДНИ)
1 Проучване на земя 02.02.2015 02.07.2015
2 Lay Foundation 02.10.2015 15.02.2015г
3 Покривни покриви 27.02.2015г 03.03.2015
4 Живопис 03.09.2015 21.03.2015г

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

ФОРМУЛИ В EXCEL е израз, който действа върху стойности в диапазон от адреси на клетки и оператори. Например = A1 + A2 + A3, което намира сумата от диапазона от стойности от клетка A1 до клетка A3. Пример за формула, съставена от дискретни стойности като = 6 * 3.

=A2 * D2 / 2

ТУК,

  • казва на Excel, че това е формула и трябва да я оцени.
  • "A2" * D2" прави препратка към адреси на клетки A2 и D2, след което умножава стойностите, намерени в тези адреси на клетки.
  • "/" е делителен аритметичен оператор
  • "2" е дискретна стойност

Формули практическо упражнение

Ще работим с примерните данни за домашния бюджет, за да изчислим междинната сума.

  • Създайте нова работна книга в Excel
  • Въведете данните, показани в бюджета за домашни консумативи по-горе.
  • Вашият работен лист трябва да изглежда по следния начин.

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

Задайте фокуса на клетка E4

Въведете следната формула.

=C4*D4

ТУК,

  • "C4*D4" използва умножение на аритметичен оператор (*), за да умножи стойността на клетъчния адрес C4 и D4.

Натиснете клавиша за въвеждане

Ще получите следния резултат

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

Грешки, които трябва да се избягват при работа с формули в Excel

  1. Запомнете правилата на скоби за деление, умножение, събиране и изваждане (BODMAS). Това означава, че изразите в скоби се оценяват първо. За аритметичните оператори делението се оценява първо, последвано от умножение, след което добавянето и изваждането е последното, което се оценява. Използвайки това правило, можем да пренапишем горната формула като = (A2 * D2) / 2. Това ще гарантира, че A2 и D2 първо се оценяват, след което се разделят на две.
  2. Формулите за електронни таблици на Excel обикновено работят с числови данни; можете да се възползвате от проверката на данните, за да укажете типа данни, които трябва да бъдат приети от клетка, т.е. само числа.
  3. За да сте сигурни, че работите с правилните адреси на клетки, посочени във формулите, можете да натиснете F2 на клавиатурата. Това ще подчертае адресите на клетките, използвани във формулата, и можете да извършите кръстосана проверка, за да се уверите, че те са желаните адреси на клетки.
  4. Когато работите с много редове, можете да използвате серийни номера за всички редове и да имате брой записи в долната част на листа. Трябва да сравните броя на серийните номера с общия запис, за да сте сигурни, че формулите ви включват всички редове.

Вижте Топ 10 формули за електронни таблици на Excel

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

FUNCTION IN EXCEL е предварително дефинирана формула, която се използва за конкретни стойности в определен ред. Функцията се използва за бързи задачи като намиране на сумата, броя, средната, максималната стойност и минималните стойности за диапазон от клетки. Например клетка A3 по-долу съдържа функцията SUM, която изчислява сумата от диапазона A1: A2.

  • SUM за сумиране на диапазон от числа
  • СРЕДНО за изчисляване на средната стойност за даден диапазон от числа
  • COUNT за преброяване на броя на елементите в даден диапазон

Значението на функциите

Функциите увеличават производителността на потребителите при работа с Excel . Да предположим, че искате да получите общата сума за горепосочения бюджет за домашни консумативи. За да улесните, можете да използвате формула, за да получите общия сбор. Използвайки формула, ще трябва да препращате клетките Е4 до Е8 една по една. Ще трябва да използвате следната формула.

= E4 + E5 + E6 + E7 + E8

С функция бихте написали горната формула като

=SUM (E4:E8)

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

Общи функции

Нека разгледаме някои от най-често използваните функции във формулите на ms excel. Ще започнем със статистическите функции.

S / N ФУНКЦИЯ КАТЕГОРИЯ ОПИСАНИЕ УПОТРЕБА
01 СУММА Математика и триг Добавя всички стойности в диапазон от клетки = СУМА (E4: E8)
02 МИН Статистически Намира минималната стойност в диапазон от клетки = МИН (E4: E8)
03 МАКС Статистически Намира максималната стойност в диапазон от клетки = МАКС. (E4: E8)
04 СРЕДНО АРИТМЕТИЧНО Статистически Изчислява средната стойност в диапазон от клетки = СРЕДНА (E4: E8)
05 БРОЯ Статистически Преброява броя на клетките в диапазон от клетки = БРОЙ (E4: E8)
06 LEN Текст Връща броя на символите в текстов низ = LEN (B7)
07 SUMIF Математика и триг Добавя всички стойности в диапазон от клетки, които отговарят на зададени критерии. = SUMIF (диапазон, критерии, [обхват_сума]) = SUMIF (D4: D8, "> = 1000", C4: C8)
08 AVERAGEIF Статистически Изчислява средната стойност в диапазон от клетки, които отговарят на посочените критерии. = AVERAGEIF (диапазон, критерии, [среден_диапазон]) = AVERAGEIF (F4: F8, "Да", E4: E8)
09 ДНИ Време за среща Връща броя на дните между две дати = ДНИ (D4, C4)
10 СЕГА Време за среща Връща текущата системна дата и час = СЕГА ()

Числови функции

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

S / N ФУНКЦИЯ КАТЕГОРИЯ ОПИСАНИЕ УПОТРЕБА
1 БРОЙ Информация Връща True, ако предоставената стойност е числова и False, ако не е числова = ISNUMBER (A3)
2 РАНД Математика и триг Генерира произволно число между 0 и 1 = РАНД ()
3 КРЪГЪЛ Математика и триг Закръглява десетична стойност до посочения брой десетични точки = КРЪГЛО (3.14455,2)
4 МЕДИАНА Статистически Връща числото в средата на набора от дадени числа = СРЕДНО (3,4,5,2,5)
5 PI Математика и триг Връща стойността на математическата функция PI (π) = PI ()
6 МОЩНОСТ Математика и триг Връща резултата от число, повишено в степен. POWER (брой, мощност) = МОЩНОСТ (2,4)
7 MOD Математика и триг Връща остатъка, когато разделите две числа = MOD (10,3)
8 РИМСКИ Математика и триг Преобразува число в римски цифри = РИМАН (1984)

Струнни функции

Тези основни функции на Excel се използват за манипулиране на текстови данни. Следващата таблица показва някои от често срещаните низови функции.

S / N ФУНКЦИЯ КАТЕГОРИЯ ОПИСАНИЕ УПОТРЕБА КОМЕНТАР
1 НАЛЯВО Текст Връща брой посочени символи от началото (отляво) на низ = НАЛЯВО ("GURU99", 4) Вляво 4 символа на "GURU99"
2 НАДЯСНО Текст Връща брой посочени знаци от края (дясната страна) на низ = НАДЯСНО ("GURU99", 2) Вдясно 2 знака на "GURU99"
3 СРЕДЕН Текст Извлича редица символи от средата на низ от определена начална позиция и дължина. = MID (текст, начален_номер, брой_символи) = MID ("GURU99", 2,3) Извличане на символи от 2 до 5
4 ИСТЕКСТ Информация Връща True, ако предоставеният параметър е Text = ISTEXT (стойност) стойност - Стойността за проверка.
5 НАМИРАМ Текст Връща началната позиция на текстов низ в друг текстов низ. Тази функция чувствителна към малки и големи букви. = НАМЕРЕТЕ (намери_текст, в_текст, [начален_номер]) = НАМЕРЕТЕ ("oo", "Покриви", 1) Намерете oo в "Покриви", резултатът е 2
6 ЗАМЕНЕТЕ Текст Заменя част от низ с друг определен низ. = ЗАМЕНЕТЕ (стар_текст, начален_номер, брой_символи, нов_текст) = ЗАМЕНЕТЕ ("Покривни", 2,2, "хх") Заменете "oo" с "xx"

Функции за дата и час

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

S / N ФУНКЦИЯ КАТЕГОРИЯ ОПИСАНИЕ УПОТРЕБА
1 ДАТА Време за среща Връща числото, което представлява датата в кода на Excel = ДАТА (2015,2,4)
2 ДНИ Време за среща Намерете броя на дните между две дати = ДНИ (D6, C6)
3 МЕСЕЦ Време за среща Връща месеца от стойност на датата = МЕСЕЦ ("4/2/2015")
4 МИНУТА Време за среща Връща минутите от стойност на времето = МИНУТА ("12:31")
5 ГОДИНА Време за среща Връща годината от стойност на датата = ГОДИНА ("02.02.2015")

Функция VLOOKUP

Функцията VLOOKUP се използва за извършване на вертикално търсене в най-лявата колона и връщане на стойност в същия ред от колона, която сте посочили. Нека обясним това на непрофесионален език. Бюджетът за домашни консумативи има колона със сериен номер, която уникално идентифицира всяка позиция в бюджета. Да предположим, че имате сериен номер на артикула и искате да знаете описанието на артикула, можете да използвате функцията VLOOKUP. Ето как би работила функцията VLOOKUP.

=VLOOKUP (C12, A4:B8, 2, FALSE)

ТУК,

  • "=VLOOKUP" извиква функцията за вертикално търсене
  • "C12" указва стойността, която да се търси в най-лявата колона
  • "A4:B8" указва масива на таблицата с данните
  • "2" указва номера на колоната със стойността на реда, която се връща от функцията VLOOKUP
  • "FALSE," казва на функцията VLOOKUP, че търсим точно съвпадение на предоставената стойност за търсене

Анимираното изображение по-долу показва това в действие

Изтеглете горния код на Excel

Обобщение

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

Ето списък на важните формула и функция на Excel

  • SUM функция = =SUM(E4:E8)
  • MIN функция = =MIN(E4:E8)
  • MAX функция = =MAX(E4:E8)
  • СРЕДНА функция = =AVERAGE(E4:E8)
  • БРОЙ функция = =COUNT(E4:E8)
  • Функция ДНИ = =DAYS(D4,C4)
  • Функция VLOOKUP = =VLOOKUP (C12, A4:B8, 2, FALSE)
  • DATE функция = =DATE(2020,2,4)