Списък с Топ 10 Разширени формули и функции на Excel
# 1 - Формула VLOOKUP в Excel
Тази усъвършенствана функция на Excel е една от най-използваните формули в Excel. Това се дължи главно на простотата на тази формула и нейното приложение при търсене на определена стойност от други таблици, която има една обща променлива в тези таблици. Да предположим, че имате две таблици с подробности за заплатата и името на служителя на компанията, като идентификаторът на служителя е основна колона. Искате да получавате заплата от таблица Б в таблица А.
Можете да използвате VLOOKUP както по-долу.
Това ще доведе до таблицата по-долу, когато приложим тази усъвършенствана формула на Excel в други клетки на колоната Работна заплата.
Плъзнете формулата до останалите клетки.
Има три основни разграничения на VLOOKUP:
- Не можете да имате първична колона вдясно на колоната, за която искате да попълните стойността от друга таблица. В този случай колоната Заплата на служителя не може да бъде преди идентификатор на служител.
- В дублираните стойности в първичната колона в таблица B първата стойност ще се попълни в клетката.
- Ако вмъкнете нова колона в базата данни (напр. Вмъкнете нова колона преди заплата на служител в таблица Б), изходът на формулата може да бъде различен в зависимост от позиция, която сте споменали във формулата (В горния случай резултатът ще бъде празно)
# 2 - Формула INDEX в Excel
Тази усъвършенствана формула на Excel се използва за получаване на стойността на клетка в дадена таблица чрез посочване на броя редове, колони или и двете. Напр. За да получите името на служител при 5 -то наблюдение, по-долу са данните.
Можем да използваме усъвършенстваната формула на Excel, както по-долу:
Същата формула INDEX може да се използва за получаване на стойност по реда. Когато се използват номера на редове и колони, синтаксисът ще изглежда по следния начин:
Горната формула ще върне „Rajesh Ved.“
Забележка: Ако вмъкнете друг ред в 5 -ия ред, формулата ще върне „Chandan Kale“. Следователно изходът ще зависи от всички промени, които се случват в таблицата с данни с течение на времето.
# 3 - Формула за съвпадение в Excel
Тази разширена формула на Excel връща номера на реда или колоната, когато в дадения диапазон има определен низ или номер. В примера по-долу се опитваме да намерим „Rajesh Ved“ в колоната Име на служител.
Формулата ще бъде дадена по-долу:
Функцията MATCH ще върне 5 като стойност.
На 3 -ти аргумент се използва за точно съвпадение. Можете също да използвате +1 и -1 според вашите изисквания.
Забележка: Може да се комбинират INDEX и MATCH, за да се преодолее ограничението на VLOOKUP.
# 4 - АКО И формула в Excel
Има много случаи, когато човек трябва да създаде флагове въз основа на някои ограничения. Всички сме запознати с основния синтаксис на IF. Използваме тази усъвършенствана функция IF IF, за да създадем ново поле въз основа на някакво ограничение върху съществуващо поле. Но какво, ако трябва да използваме множество колони, докато създаваме флаг. Напр. В долния случай искаме да маркираме всички служители, чиято заплата е по-голяма от 50 000, но идентификационният номер на служителя е по-голям от 3.
В такива случаи бихме използвали IF И. Моля, намерете по-долу скрийншота за същото.
Ще върне резултата като 0.
Можем да имаме много условия или ограничения, за да създадем флаг, базиран на множество колони, използвайки И.
# 5 - АКО ИЛИ Формула в Excel
По същия начин можем да използваме функцията OR в Excel вместо И, ако трябва да удовлетворим едно от условията на много.
В горните случаи, ако някое от едно условие бъде изпълнено, ще имаме клетка, попълнена като 1 друго 0. Можем също да заместим 1 или 0 с някои поднизове с двойни кавички („”).
# 6 - Формула SUMIF в Excel
При някои анализи може да се наложи да филтрирате някои наблюдения, когато прилагате функцията за сумиране или преброяване. В такива случаи тази усъвършенствана функция на SUMIF на excel е в наше спасение. Той филтрира всички наблюдения въз основа на определени условия, дадени в тази усъвършенствана формула на Excel, и ги обобщава. Напр. Ами ако искаме да знаем сумата на заплатите само на тези служители, които имат идентификационен номер на служител по-голям от 3.
Чрез прилагане на формулата SUMIFS:
Формулата връща резултатите като 322000.
Също така можем да преброим броя на служителите в организацията с идентификатор на служител по-голям от 3, когато използваме COUNTIF вместо SUMIF.
# 7 - CONCATENATE Formula в Excel
Тази разширена функция на Excel е една от формулите, която може да се използва с множество варианти. Тази усъвършенствана формула на Excel ни помага да обединим няколко текстови низа в един текстов низ. Например, ако искаме да покажем идентификатор на служител и име на служител в една колона.
Можем да използваме тази формула CONCATENATE, за да направим това.
Горната формула ще доведе до „1Aman Gupta“.
Можем да имаме още един вариант, като поставим един тире между ID и NAME. Напр. CONCATENATE (B3, ”-“, C3) ще доведе до “1-Aman Gupta”. Можем също да използваме това във VLOOKUP, когато LOOKUP в стойност на Excel е смес от повече от една променлива.
# 8 - ЛЯВА, СРЕДНА и ДЯСНА формула в Excel
Можем да използваме тази усъвършенствана формула на Excel, за да извлечем конкретен подниз от даден низ. Може да се използва въз основа на нашите изисквания. Напр. Ако искаме да извлечем първите 5 знака от име на служител, можем да използваме формулата НАЛЯВО в Excel с името на колоната и втория параметър като 5.
Резултатът е даден по-долу:
Прилагането на формулата НАДЯСНО в Excel също е същото. Просто бихме разглеждали героя отдясно на низа. Въпреки това, в случай на MID функция в Excel, трябва да дадем началната позиция на необходимия текстов низ и дължината на низа.
# 9 - Формула OFFSET в Excel
Тази усъвършенствана функция на excel с комбинация от други функции като SUM или AVERAGE може да даде динамичен щрих на изчисленията. Най-добре се използва в случаите, когато вмъкваме непрекъснати редове в съществуваща база данни. OFFSET Excel ни предоставя диапазон, в който трябва да споменем референтна клетка, брой редове и колони. Напр. Ако искаме да изчислим средната стойност на първите 5 служители във фирмата, където имаме заплата на служителите, сортирана по идентификационен номер на служител, можем да направим следното. Изчислението по-долу винаги ще ни дава заплата.
- Ще ни даде сумата на заплатите на първите 5 служители.
# 10 - Формула TRIM в Excel
Тази усъвършенствана формула на Excel се използва за изчистване на маловажните интервали от текста. Напр. Ако искаме да премахнем интервали в началото на някакво име, можем да го използваме, като използваме функцията TRIM в Excel, както е показано по-долу:
Резултатният изход ще бъде “Chandan Kale” без никакво пространство преди Chandan.
