Excel Функции

Cheatsheet Content

### Основни Функции Тези функции са сред най-често използваните за базови изчисления. - **`SUM(число1, [число2], ...)`** - **Описание:** Изчислява сумата на всички числа в даден диапазон от клетки или списък от аргументи. - **Пример:** `=SUM(A1:A5)` или `=SUM(10, 20, 30)` - Ако A1=10, A2=20, A3=30, A4=40, A5=50, то `=SUM(A1:A5)` ще върне `150`. - **`AVERAGE(число1, [число2], ...)`** - **Описание:** Изчислява средноаритметичната стойност на числата в даден диапазон. - **Пример:** `=AVERAGE(B1:B5)` - Ако B1=10, B2=20, B3=30, B4=40, B5=50, то `=AVERAGE(B1:B5)` ще върне `30`. - **`MIN(число1, [число2], ...)`** - **Описание:** Връща най-малката числова стойност в списък от аргументи. - **Пример:** `=MIN(C1:C5)` - Ако C1=5, C2=15, C3=2, C4=20, C5=8, то `=MIN(C1:C5)` ще върне `2`. - **`MAX(число1, [число2], ...)`** - **Описание:** Връща най-голямата числова стойност в списък от аргументи. - **Пример:** `=MAX(D1:D5)` - Ако D1=5, D2=15, D3=2, D4=20, D5=8, то `=MAX(D1:D5)` ще върне `20`. - **`COUNT(стойност1, [стойност2], ...)`** - **Описание:** Преброява броя на клетките в даден диапазон, които съдържат числа. - **Пример:** `=COUNT(E1:E5)` - Ако E1=10, E2="текст", E3=20, E4="", E5=30, то `=COUNT(E1:E5)` ще върне `3`. - **`COUNTA(стойност1, [стойност2], ...)`** - **Описание:** Преброява броя на клетките в даден диапазон, които не са празни (съдържат данни от всякакъв тип). - **Пример:** `=COUNTA(F1:F5)` - Ако F1=10, F2="текст", F3=TRUE, F4="", F5=30, то `=COUNTA(F1:F5)` ще върне `4`. ### Текстови Функции Използват се за манипулиране и анализ на текстови данни. - **`CONCATENATE(текст1, [текст2], ...)` или `CONCAT(текст1, [текст2], ...)` (по-нова версия)** - **Описание:** Обединява няколко текстови низа в един. - **Пример:** `=CONCATENATE("Здравейте", " ", "свят")` или `=CONCAT("Здравейте", " ", "свят")` - Връща `"Здравейте свят"`. - **`LEFT(текст, [брой_знаци])`** - **Описание:** Връща определен брой знаци от началото (лявата страна) на текстов низ. - **Пример:** `=LEFT("Excel", 2)` - Връща `"Ex"`. - **`RIGHT(текст, [брой_знаци])`** - **Описание:** Връща определен брой знаци от края (дясната страна) на текстов низ. - **Пример:** `=RIGHT("Excel", 3)` - Връща `"cel"`. - **`MID(текст, начална_позиция, брой_знаци)`** - **Описание:** Връща определен брой знаци от средата на текстов низ, като започва от посочена позиция. - **Пример:** `=MID("Функции", 3, 4)` - Връща `"нкци"`. - **`LEN(текст)`** - **Описание:** Връща броя на знаците в текстов низ. - **Пример:** `=LEN("Excel")` - Връща `5`. - **`FIND(търсен_текст, текст, [начална_позиция])`** - **Описание:** Връща началната позиция на един текстов низ в друг текстов низ (чувствителна към регистъра). - **Пример:** `=FIND("це", "функции")` - Връща `4`. - **`REPLACE(стар_текст, начална_позиция, брой_знаци, нов_текст)`** - **Описание:** Замества част от текстов низ с друг текстов низ. - **Пример:** `=REPLACE("Excel", 1, 2, "XX")` - Връща `"XXcel"`. - **`TRIM(текст)`** - **Описание:** Премахва всички интервали от текст, освен единични интервали между думи. - **Пример:** `=TRIM(" Здравейте свят ")` - Връща `"Здравейте свят"`. - **`UPPER(текст)`** - **Описание:** Преобразува всички букви в текстов низ в главни. - **Пример:** `=UPPER("excel")` - Връща `"EXCEL"`. - **`LOWER(текст)`** - **Описание:** Преобразува всички букви в текстов низ в малки. - **Пример:** `=LOWER("EXCEL")` - Връща `"excel"`. - **`PROPER(текст)`** - **Описание:** Преобразува първата буква на всяка дума в текстов низ в главна, а останалите в малки. - **Пример:** `=PROPER("excel функции")` - Връща `"Excel Функции"`. ### Логически Функции Използват се за вземане на решения въз основа на условия. - **`IF(логически_тест, стойност_ако_вярно, [стойност_ако_грешно])`** - **Описание:** Връща една стойност, ако условието е вярно, и друга стойност, ако условието е грешно. - **Пример:** `=IF(A1>10, "Голям", "Малък")` - Ако A1=15, ще върне `"Голям"`. Ако A1=5, ще върне `"Малък"`. - **`AND(логическо1, [логическо2], ...)`** - **Описание:** Връща TRUE, ако всички аргументи са TRUE; връща FALSE, ако поне един аргумент е FALSE. - **Пример:** `=AND(A1>10, B1 10, B1 ### Математически и Тригонометрични Функции Извършват различни математически операции. - **`ROUND(число, брой_цифри)`** - **Описание:** Закръгля число до определен брой цифри. - **Пример:** `=ROUND(3.14159, 2)` - Връща `3.14`. - **`ROUNDUP(число, брой_цифри)`** - **Описание:** Закръгля число нагоре (към по-голяма абсолютна стойност) до определен брой цифри. - **Пример:** `=ROUNDUP(3.14159, 2)` - Връща `3.15`. - **`ROUNDDOWN(число, брой_цифри)`** - **Описание:** Закръгля число надолу (към по-малка абсолютна стойност) до определен брой цифри. - **Пример:** `=ROUNDDOWN(3.14159, 2)` - Връща `3.14`. - **`INT(число)`** - **Описание:** Закръгля число до най-близкото по-малко цяло число. - **Пример:** `=INT(3.7)` ще върне `3`. `=INT(-3.2)` ще върне `-4`. - **`ABS(число)`** - **Описание:** Връща абсолютната стойност на число. - **Пример:** `=ABS(-5)` - Връща `5`. - **`SQRT(число)`** - **Описание:** Връща положителния квадратен корен на число. - **Пример:** `=SQRT(25)` - Връща `5`. - **`POWER(число, степен)`** - **Описание:** Връща резултата от повдигане на число на степен. - **Пример:** `=POWER(2, 3)` - Връща `8` (2 на степен 3). - **`SUMIF(диапазон, критерии, [диапазон_за_сумиране])`** - **Описание:** Сумира клетки, които отговарят на един зададен критерий. - **Пример:** `=SUMIF(A1:A5, ">100", B1:B5)` - Ако A1=150, B1=10; A2=80, B2=5; A3=200, B3=12, то ще сумира B1 и B3, връщайки `22`. - **`SUMIFS(диапазон_за_сумиране, диапазон_критерии1, критерии1, [диапазон_критерии2, критерии2], ...)`** - **Описание:** Сумира клетки, които отговарят на множество зададени критерии. - **Пример:** `=SUMIFS(C1:C5, A1:A5, ">100", B1:B5, " ### Функции за Дата и Час Използват се за работа с дати и часове. - **`TODAY()`** - **Описание:** Връща текущата дата. - **Пример:** `=TODAY()` - Връща `2023-10-27` (или текущата дата). - **`NOW()`** - **Описание:** Връща текущата дата и час. - **Пример:** `=NOW()` - Връща `2023-10-27 14:30` (или текущата дата и час). - **`DAY(дата)`** - **Описание:** Връща деня от месеца (число от 1 до 31) за дадена дата. - **Пример:** `=DAY("2023-10-27")` - Връща `27`. - **`MONTH(дата)`** - **Описание:** Връща месеца (число от 1 до 12) за дадена дата. - **Пример:** `=MONTH("2023-10-27")` - Връща `10`. - **`YEAR(дата)`** - **Описание:** Връща годината за дадена дата. - **Пример:** `=YEAR("2023-10-27")` - Връща `2023`. - **`DATE(година, месец, ден)`** - **Описание:** Връща сериен номер, представляващ определена дата. - **Пример:** `=DATE(2023, 10, 27)` - Връща `2023-10-27`. - **`DATEDIF(начална_дата, крайна_дата, единица)`** - **Описание:** Изчислява броя на дните, месеците или годините между две дати. - **Единици:** `"Y"` (години), `"M"` (месеци), `"D"` (дни), `"YD"` (дни без години), `"MD"` (дни без месеци и години). - **Пример:** `=DATEDIF("2022-01-01", "2023-01-01", "Y")` - Връща `1`. - **`NETWORKDAYS(начална_дата, крайна_дата, [празници])`** - **Описание:** Връща броя на работните дни между две дати (без почивни дни и опционални празници). - **Пример:** `=NETWORKDAYS("2023-10-23", "2023-10-27")` (понеделник до петък) - Връща `5`. ### Функции за Търсене и Препратки Използват се за намиране на данни и създаване на препратки. - **`VLOOKUP(стойност_за_търсене, табличен_масив, номер_на_колона_за_индекс, [приблизително_съвпадение])`** - **Описание:** Търси стойност в първата колона на таблица и връща стойност от същия ред в посочената колона. - **Пример:** `=VLOOKUP("Ябълка", A1:C10, 2, FALSE)` - Търси "Ябълка" в A1:A10 и връща съответната стойност от втората колона на същия ред. `FALSE` за точно съвпадение. - **`HLOOKUP(стойност_за_търсене, табличен_масив, номер_на_ред_за_индекс, [приблизително_съвпадение])`** - **Описание:** Аналогична на VLOOKUP, но търси стойност в първия ред на таблица и връща стойност от същата колона в посочения ред. - **Пример:** `=HLOOKUP("Месец", A1:E5, 3, FALSE)` - Търси "Месец" в A1:E1 и връща съответната стойност от третия ред на същата колона. - **`XLOOKUP(търсена_стойност, диапазон_за_търсене, диапазон_за_връщане, [ако_не_намерено], [режим_на_съвпадение], [режим_на_търсене])`** - **Описание:** (По-нова и по-мощна алтернатива на VLOOKUP/HLOOKUP) Търси елемент в диапазон и връща съответния елемент от друг диапазон. - **Пример:** `=XLOOKUP("ID001", A:A, B:B, "Не е намерено", 0)` - Търси "ID001" в колона A и връща съответната стойност от колона B. `0` за точно съвпадение. - **`INDEX(масив, номер_на_ред, [номер_на_колона])`** - **Описание:** Връща стойността на елемент в таблица или диапазон, определен от индексите на ред и колона. - **Пример:** `=INDEX(A1:C10, 3, 2)` - Връща стойността от клетка B3 (3-ти ред, 2-ра колона в диапазона A1:C10). - **`MATCH(търсена_стойност, диапазон_за_търсене, [тип_на_съвпадение])`** - **Описание:** Връща относителната позиция на елемент в масив, който съвпада с посочена стойност в посочен ред. - **Тип на съвпадение:** `0` (точно), `1` (по-малко от, сортирано възходящо), `-1` (по-голямо от, сортирано низходящо). - **Пример:** `=MATCH("Ябълка", A1:A10, 0)` - Ако "Ябълка" е в A5, ще върне `5`. - **`INDIRECT(текст_препратка, [A1])`** - **Описание:** Връща препратка, посочена от текстов низ. Позволява създаване на динамични препратки. - **Пример:** Ако A1 съдържа текста "B5", то `=INDIRECT(A1)` ще върне стойността в клетка B5. ### Статистически Функции Използват се за статистически анализи на данни. - **`COUNTIF(диапазон, критерии)`** - **Описание:** Преброява броя на клетките в диапазон, които отговарят на даден критерий. - **Пример:** `=COUNTIF(A1:A10, ">50")` - Преброява колко клетки в A1:A10 съдържат число, по-голямо от 50. - **`COUNTIFS(диапазон_критерии1, критерии1, [диапазон_критерии2, критерии2], ...)`** - **Описание:** Преброява броя на клетките в диапазон, които отговарят на множество критерии. - **Пример:** `=COUNTIFS(A1:A10, ">50", B1:B10, " 10")` - Изчислява средната стойност на C1:C10, където A1:A10 е "Продукт A" *и* B1:B10 е по-голямо от 10. - **`RANK.EQ(число, препратка, [ред])`** - **Описание:** Връща ранга на число в списък от числа. Ако има дубликати, те получават един и същ ранг, а следващият ранг се пропуска. - **Ред:** `0` (низходящ, по подразбиране) или `1` (възходящ). - **Пример:** `=RANK.EQ(A1, B1:B10, 0)` - Връща ранга на стойността в A1 в диапазона B1:B10. - **`MODE.SNGL(число1, [число2], ...)`** - **Описание:** Връща най-често срещаната стойност (модата) в набор от данни. - **Пример:** `=MODE.SNGL(A1:A10)` - Ако A1:A10 съдържа {1, 2, 2, 3, 4}, ще върне `2`. - **`MEDIAN(число1, [число2], ...)`** - **Описание:** Връща медианата (средната стойност) на даден набор от числа. - **Пример:** `=MEDIAN(A1:A10)` - Ако A1:A10 съдържа {1, 2, 3, 4, 5}, ще върне `3`. - Ако A1:A10 съдържа {1, 2, 3, 4}, ще върне `2.5`. ### Комбинации от Функции Често силата на Excel идва от комбинирането на няколко функции за постигане на по-сложни резултати. #### 1. `IF` с `AND`/`OR` за множество условия - **Сценарий:** Проверка дали даден ученик е преминал, ако има над 60 точки по математика *и* над 70 по физика. - **Формула:** `=IF(AND(B2>=60, C2>=70), "Преминал", "Не е преминал")` - **Обяснение:** Функцията `AND` проверява две условия едновременно. Ако и двете са TRUE, `IF` връща "Преминал". В противен случай връща "Не е преминал". - **Пример:** | Ученик | Математика (B) | Физика (C) | Резултат | |--------|----------------|------------|----------| | Иван | 65 | 75 | Преминал | | Мария | 55 | 80 | Не е преминал | | Петър | 70 | 60 | Не е преминал | #### 2. `INDEX` и `MATCH` за гъвкаво търсене (алтернатива на VLOOKUP) - **Сценарий:** Намиране на цена на продукт по име, когато колоната с имената не е първа. - **Формула:** `=INDEX(C:C, MATCH("Ябълка", B:B, 0))` - **Обяснение:** `MATCH("Ябълка", B:B, 0)` намира номера на реда, на който се намира "Ябълка" в колона B. `INDEX(C:C, ...)` използва този номер на ред, за да извлече стойността от колона C. - **Пример:** | ID | Продукт (B) | Цена (C) | |---------|-------------|----------| | 101 | Портокал | 1.50 | | 102 | Ябълка | 1.20 | | 103 | Банан | 0.80 | - Резултат: `1.20` #### 3. `SUMPRODUCT` за сумиране на произведения - **Сценарий:** Изчисляване на обща стойност на поръчка (количество * единична цена). - **Формула:** `=SUMPRODUCT(B2:B5, C2:C5)` - **Обяснение:** Умножава съответните елементи в дадените диапазони и след това сумира тези произведения. - **Пример:** | Продукт | Количество (B) | Ед. цена (C) | |---------|----------------|--------------| | А | 2 | 10 | | Б | 3 | 5 | | В | 1 | 20 | | Г | 4 | 8 | - Резултат: `(2*10) + (3*5) + (1*20) + (4*8) = 20 + 15 + 20 + 32 = 87` #### 4. `TEXT` с `TODAY` или `NOW` за форматиране на дата/час - **Сценарий:** Показване на текущата дата във формат "Ден, DD.MM.YYYY". - **Формула:** `=TEXT(TODAY(), "Ден, dd.mm.yyyy")` - **Обяснение:** `TODAY()` връща текущата дата. `TEXT()` форматира тази дата според зададения формат. - **Пример:** Ако днес е 27 октомври 2023 г., ще върне `"Петък, 27.10.2023"`. #### 5. `IFERROR` с `VLOOKUP` за обработка на грешки - **Сценарий:** Търсене на продукт, като се показва съобщение, ако продуктът не е намерен, вместо грешка. - **Формула:** `=IFERROR(VLOOKUP("Несъществуващ", A1:B10, 2, FALSE), "Продуктът не е намерен")` - **Обяснение:** Ако `VLOOKUP` върне грешка (напр. `#N/A`), `IFERROR` ще покаже "Продуктът не е намерен". В противен случай ще покаже резултата от `VLOOKUP`. - **Пример:** | Продукт (A) | Цена (B) | |-------------|----------| | Ябълка | 1.20 | | Банан | 0.80 | - Резултат за "Несъществуващ": `"Продуктът не е намерен"` #### 6. Динамично именуване на листове с `INDIRECT` - **Сценарий:** Препратка към клетка от лист, чието име е записано в друга клетка. - **Формула:** `=SUM(INDIRECT(A1&"!B2:B5"))` - **Обяснение:** Ако клетка A1 съдържа текста "Продажби", то формулата ще сумира диапазона B2:B5 от лист "Продажби". - **Пример:** | A1 | |-----------| | Продажби | - Резултат: Сумата от B2:B5 на лист "Продажби". #### 7. Извличане на име и фамилия от пълно име - **Сценарий:** Разделяне на пълно име, записано в една клетка, на име и фамилия. - **Формула за Име (в B2, ако пълното име е в A2):** `=LEFT(A2, FIND(" ", A2)-1)` - **Обяснение:** `FIND(" ", A2)` намира позицията на първия интервал. `LEFT` извлича знаците до тази позиция минус един (за да не включва интервала). - **Формула за Фамилия (в C2):** `=RIGHT(A2, LEN(A2)-FIND(" ", A2))` - **Обяснение:** `LEN(A2)` дава общата дължина на името. `FIND(" ", A2)` дава позицията на интервала. Изваждайки двете, получаваме броя на знаците във фамилията. `RIGHT` извлича тези знаци отдясно. - **Пример:** | Пълно име (A) | Име (B) | Фамилия (C) | |---------------|---------|-------------| | Иван Петров | Иван | Петров | - Резултат: B2 ще е "Иван", C2 ще е "Петров".