Excel Advanced Formulas (Съдържание)
- Въведение в разширените формули в Excel
- Примери за разширена формула в Excel
Въведение в разширените формули в Excel
След като достигнете междинното ниво в excel, трябва да работите усилено, за да преминете към напредналото ниво. За да преминете към напредналото ниво, трябва да сте запознати с някои от често използваните разширени формули. В тази статия ще разгледам топ 10 усъвършенствани формули, които всички отлични учащи трябва да знаят. Следвайте тази статия, за да научите и изследвате.
Примери за разширена формула в Excel
Нека разберем как да използваме разширените формули в Excel с някои примери.
Можете да изтеглите този шаблон за разширени формули Excel тук - Шаблон за разширени формули ExcelПример №1 - Частична функция VLOOKUP
Сигурно сте се сблъскали със ситуация, при която VLOOKUP издава грешка, дори ако е имало леко пропускане в стойността на търсенето. Например, ако намирате за заплатата на името Abhishek Sinha и ако имате само Abhishek, тогава VLOOKUP не може да ви донесе данните.
Въпреки това, използвайки знак звездичка в двата края на търсената стойност, можем да извлечем данните за частична стойност на търсене.
В таблица 1 имам пълно име и заплата, но в таблица 2 имам само частични имена и трябва да намеря заплатата на всеки служител.
Стъпка 1: Отворете формулата VLOOKUP в клетка E3. Преди да изберете стойността на търсене, поставете звездичка (*) от двете страни на търсената стойност.
Стъпка 2: Както обикновено можете да попълните формулата VLOOKUP сега и ние ще имаме резултатите.
След използване на горната формула, изходът е показан по-долу.
Същата формула се използва и в други клетки.
Пример №2 - COUNTIFS със символи на оператора
Трябва да сте използвали функцията COUNTIF & IFS, за да преброите нещата в списъка. Можем също да броим въз основа на символи на оператора като по-голям от (>) по-малък от (<) и знак равен (=).
Сега вижте например по-долу данните. Ако искате да преброите общия брой фактури за региона ЮЖНО след датата 10 януари 2018 г., как да броите?
Пример № 3 - АКО Състояние с И ИЛИ Условия
Логическите функции са част от ежедневните ни дейности. Трябва да ги овладеете, за да преминете към следващото ниво. Ако изчислявате бонус въз основа на няколко условия, тогава трябва да вложите ИЛИ или ИЛИ условие с условие IF, за да свършите работата.
Да приемем, че трябва да изчислите размера на бонуса за всеки отдел въз основа на отдела и години на обслужване, от които се нуждаете от тези функции. Въз основа на критериите по-долу трябва да изчислим бонуса.
Ако услугата е над 4 години и отделът е или бонус за продажби или поддръжка е 50000 или иначе бонусът е 25000.
Приложете по-долу формула, за да получите сумата на бонуса.
След използване на горната формула, изходът е показан по-долу.
Същата формула, която се прилага в клетка E3 до E9.
Пример # 4 - ТЕКСТНА функция, за да ви направи заглавие динамично
Да приемем, че поддържате дневна таблица с продажби и трябва да актуализирате таблицата всеки ден. В началото на таблицата имате едно заглавие, което гласи „Консолидирани данни за продажбите от DD-MM-YYYY до DD-MM-YYYY“. Както и кога таблицата се актуализира, трябва да промените датата на заглавието. Не е ли разочароваща задача да правиш едно и също нещо отново и отново? Можем да направим това заглавие динамично, като използваме TETX, MIN и Max функция заедно с конкатенатен символ оператор ampersand (&).
Пример №5 - INDEX + MATCH + MAX за намиране на най-високото лице по продажбите
Имате списък с лицата по продажби и продажбите, които са направили срещу името им. Как да разберете кой е най-добрият или най-високият продавач в списъка? Разбира се, имаме няколко други техники, за да кажем резултата, но тази функция може да върне най-високия продавач от партидата.
Прилагайте по-долу функция, за да получите най-високото име на продавача.
След използване на горната формула, изходът е показан по-долу.
Пример №6 - Получете брой уникални стойности от списъка
Ако имате много дублиращи се стойности и се изисква да кажете колко уникални стойности има в как да кажете? Можете да разберете, като премахнете дублираната стойност, но това не е динамичният начин да се каже на броя на уникалните стойности.
С помощта на тази функция на масива можем да кажем уникалните стойности от партидата.
Прилагайте по-долу формула, за да получите уникален списък със стойности.
Пример №7 - Използвайте именуван диапазон, за да направите падащото меню динамично
Ако често работите с падащ списък и актуализирате падащия списък, трябва да се върнете към обхвата на списъка с източници, за да изтриете или добавите стойности. След това трябва да се върнете към падащите клетки и отново да актуализирате обхвата на падащия списък.
Ако обаче можете да създадете име на диапазон за падащия си списък, можете да направите падащия списък динамичен и актуален.
Създайте диапазон от имена, както е показано на изображението по-долу.
Сега отидете на падащата клетка и отворете диалоговия прозорец на падащото меню.
В източника натиснете клавиша F3, той ще покаже всички определени имена, изберете името на вашия падащ списък.
Ок, падащ списък е готов и той ще актуализира стойностите автоматично, когато има промяна в обхвата на падащия списък.
Пример № 8 - Отървете се от стойностите на грешките, използвайки функция IFERROR
Сигурен съм, че сте изпитали стойности на грешки по време на работа с изчисления VLOOKUP, Division. Работата с тези стойности на грешка е досадна задача. Но можем да се отървем от тези стойности на грешки, като използваме IFERROR функцията във формулата.
След използване на горната формула, изходът е показан по-долу.
Същата формула, използвана и в други клетки.
Пример # 9 - Използвайте функцията PMT, за да създадете своя собствена EMI диаграма
В днешно време опцията EMI не е странно нещо за всички нас. В excel можем да оценим нашата собствена EMI диаграма, като използваме функцията PMT. Следвайте стъпките по-долу, за да създадете своя собствена диаграма.
Прилагайте по-долу формула в клетка В4, за да получите EMI количеството.
Пример # 10 - INDEX + MATCH като алтернатива на VLOOKUP функция
Надявам се да сте наясно с ограничението на функцията VLOOKUP. Едно от основните ограничения VLOOKUP може да извлече данните от ляво на дясно, а не от дясно на ляво. Не винаги данните са добре организирани и готови за използване. Често колоната със стойност, която търсим, е от лявата страна на търсената стойност, така че VLOOKUP не успява да помогне в тези случаи.
Комбинация от функция INDEX + MATCH служи като алтернатива на функцията VLOOKUP.
След използване на горната формула, изходът е показан по-долу.
Същата формула се използва и в други клетки.
Въз основа на идентификационния номер на продукта, ние трябва да извлечем стойности на продажбите. В основната таблица Идент. № на продукта е от дясната страна на графата за продажби. Така VLOOKUP не може да извлече данните. Използвайте формулата по-долу, за да получите данните.
Препоръчителни статии
Това е ръководство за разширени формули в Excel. Тук обсъждаме как да използвате Разширени формули в Excel, заедно с практически примери и шаблон за Excel, който можете да изтеглите. Можете да разгледате и другите ни предложени статии -
- Как да използвате Excel INDEX функция?
- Разширено Excel | Функция на база данни
- Примери за TRIM формула в Excel
- Ръководство за формулата на Excel OFFSET