Формули и функции в Excel с основни примери

по: Шарлот Милър Шарлот Милър
Hours Обновено

⚡ Умно обобщение

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

  • 🟰 Формула: Израз, който започва със знак за равенство и работи с адреси на клетки и оператори, например =C4*D4.
  • 🧩 Функция: Предварително дефинирана формула, която изпълнява задача в диапазон, така че =SUM(E4:E8) замества =E4+E5+E6+E7+E8.
  • 🧮 БОДМАС: Excel първо изчислява скоби, след това деление и умножение, накрая събиране и извличанеtracТА.
  • 📊 Статистически функции: SUM, MIN, MAX, AVERAGE, COUNT, SUMIF и AVERAGEIF обобщават диапазон.
  • 🔤 Функции за низове: LEFT, RIGHT, MID, FIND и REPLACE манипулират текст.
  • 📅 Функции за дата: ДАТА, ДНИ, МЕСЕЦ, ГОДИНА и СЕГА работят със стойности за дата и час.
  • ???? VLOOKUP: Търси стойност в най-лявата колона на таблица и връща стойност от посочена от вас колона.
Прочетете повече

Формули и функции в Excel

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

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

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

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

S / N ТОЧКА Количество ЦЕНА SUBTOTAL Достъпно ли е?
1 Мангото 9 600
2 Портокали 3 1200
3 домати 1 2500
4 Олио 5 6500
5 Tonic Water 13 3900

График на проекта за изграждане на къща

S / N ТОЧКА НАЧАЛНА ДАТА КРАЙНА ДАТА ПРОДЪЛЖИТЕЛНОСТ (ДНИ)
1 Проучете земя 04/02/2015 07/02/2015
2 Lay Foundation 10/02/2015 15/02/2015
3 покривен 27/02/2015 03/03/2015
4 живопис 09/03/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. Запомнете правилата на Brackets на деление, умножение, събиране и извличанеtracАЦИ (БОДМАС). Това означава, че изразите в скоби се оценяват първо. При аритметичните оператори, първо се оценява делението, последвано от умножение, след това събиране и извличане.tracцията е последната, която се оценява. Използвайки това правило, можем да пренапишем горната формула като =(A2 * D2) / 2. Това ще гарантира, че A2 и D2 първо се оценяват, а след това се делят на две.
  2. Формулите за електронни таблици на Excel обикновено работят с числови данни; можете да се възползвате от валидирането на данни, за да посочите типа данни, които трябва да се приемат от клетка, т.е. само числа.
  3. За да сте сигурни, че работите с правилните адреси на клетки, посочени във формулите, можете да натиснете F2 на клавиатурата. Това ще подчертае адресите на клетките, използвани във формулата, и можете да проверите кръстосано, за да сте сигурни, че са желаните адреси на клетки.
  4. Когато работите с много редове, можете да използвате серийни номера за всички редове и да имате брой записи в долната част на листа. Трябва да сравните броя на серийните номера с общия запис, за да сте сигурни, че вашите формули включват всички редове.

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

Не пропускайте:

Какво е функция в Excel?

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

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

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

Функциите повишават производителността на потребителите при работа с excel. Да приемем, че бихте искали да получите общата сума за горния бюджет за домашни консумативи. За да го направите по-просто, можете да използвате формула, за да получите общата сума. Използвайки формула, ще трябва да посочите клетките от E4 до E8 една по една. Ще трябва да използвате следната формула.

= E4 + E5 + E6 + E7 + E8

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

=SUM (E4:E8)

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

Общи функции

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

S / N ФУНКЦИЯ КАТЕГОРИЯ ОПИСАНИЕ ИЗПОЛЗВАНЕ
01 SUM Математика и триг Добавя всички стойности в диапазон от клетки = SUM (E4: E8)
02 MIN Статистически Намира минималната стойност в диапазон от клетки =MIN(E4:E8)
03 MAX Статистически Намира максималната стойност в диапазон от клетки =МАКС(E4:E8)
04 СРЕДНО АРИТМЕТИЧНО Статистически Изчислява средната стойност в диапазон от клетки = Средна (E4: E8)
05 COUNT Статистически Преброява броя на клетките в диапазон от клетки = COUNT (E4: E8)
06 САМО Текст Връща броя знаци в текст на низ =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 ISNUMBER Информация Връща True, ако предоставената стойност е числова, и False, ако не е числова =ISNUMBER(A3)
2 RAND Математика и триг Генерира произволно число между 0 и 1 =RAND()
3 КРЪГЛА Математика и триг Закръглява десетична стойност до посочения брой десетични точки =КРЪГЛО(3.14455,2)
4 MEDIAN Статистически Връща числото в средата на набора от дадени числа =МЕДИАН(3,4,5,2,5;XNUMX;XNUMX;XNUMX;XNUMX)
5 PI Математика и триг Връща стойността на математическата функция PI(π) =PI()
6 ЕКСПЛОЗИВНОСТ Математика и триг Връща резултата от число, повдигнато на степен.
POWER (число, мощност)		 =МОЩНОСТ(2,4;XNUMX)
7 MOD Математика и триг Връща остатъка, когато разделите две числа =MOD(10,3;XNUMX)
8 ROMAN Математика и триг Преобразува число в римски цифри =РОМАН(1984)

Стрингови функции

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

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

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

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

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

Функция VLOOKUP

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

Функция VLOOKUP

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

ТУК,

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

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

Функция VLOOKUP

Изтеглете горния Excel файл Code

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

  • Функция SUM = =SUM(E4:E8)
  • MIN функция = =MIN(E4:E8)
  • Функция MAX = =MAX(E4:E8)
  • Функция AVERAGE = =AVERAGE(E4:E8)
  • Функция COUNT = =COUNT(E4:E8)
  • Функция DAYS = =DAYS(D4,C4)
  • Функция VLOOKUP = =VLOOKUP (C12, A4:B8, 2, FALSE)
  • Функция ДАТА = =DATE(2020,2,4)

Въпроси и Отговори

Формула е всеки израз, който създавате, например =E4+E5+E6. Функция е предварително дефинирана формула с име, например =SUM(E4:E8), която изпълнява същата задача в диапазон с по-малко ty.ping и по-малко грешки.

FALSE изисква точно съвпадение, така че VLOOKUP връща стойност само когато търсената стойност е намерена точно. TRUE изисква приблизително съвпадение и изисква първата колона да бъде сортирана във възходящ ред.

SUM сумира всички стойности в диапазон. SUMIF сумира само стойностите, които отговарят на условие, например =SUMIF(D4:D8,”>=1000″,C4:C8) сумира само количествата, чиято цена е 1000 или повече.

Да. Функции на изкуствен интелект, като например Copilot в Excel, превръщат обикновена заявка като „сумиране на колона E, където колона F е „Да“ в =SUMIF(F4:F8;„Да“;E4:E8). Потребителят все още проверява диапазона и резултата.

Да. Асистентите с изкуствен интелект четат формула, обясняват всяка част на разбираем език и предлагат корекция на грешки, като например грешен диапазон или липсваща скоба. Потребителят преглежда промяната, преди да я приложи.

Обобщете тази публикация с: