Ако сте запалени по Excel и искате да издигнете уменията си на следващото ниво, вие сте на правилното място.
Excel предлага зашеметяващ набор от над 450 функции, от популярни като VLOOKUP и SUMIF до по-малко известни скъпоценни камъни, които правят чудеса с вашите данни. Ще проучим 20 усъвършенствани функции на Excel, които ще повишат вашия опит в работата с електронни таблици.
Съдържание
- Функция XLOOKUP
- Функция VLOOKUP
- Функция INDEX / MATCH
- Функция SUMPRODUCT
- Функции SUMIF/COUNTIF & SUMIFS/COUNTIFS
- Функция ФИЛТЪР
- Често задавани въпроси
- Последни мисли
Функция XLOOKUP
XLOOKUP е мощна и усъвършенствана функция, въведена в Excel 2021 и Excel за Microsoft 365. Това е подобрена алтернатива на популярната функция VLOOKUP, адресираща някои ограничения.
Важно е обаче да се отбележи, че XLOOKUP не е наличен в по-стари версии на Excel.
Основната цел на XLOOKUP е да търси конкретна стойност (наричана справочна стойност) в списък от стойности, подредени в редове или колони. Тя ви позволява да определите дали стойността съществува в списъка и да извлечете всяка съответстваща стойност от същия ред или колона.
Струва си да се спомене, че ако използвате XLOOKUP в работната си книга и я споделите с някой, използващ по-стара версия на Excel, формулите XLOOKUP няма да работят в тяхната версия. Следователно избягването на използването на XLOOKUP в такива случаи е препоръчително, за да се осигури съвместимост.
Функция VLOOKUP
VLOOKUP отдавна е призната за една от най-широко използваните функции на Excel, въпреки че по-усъвършенстваната функция XLOOKUP вече я надмина. Въпреки това, тъй като не всеки има достъп до Excel 2021 или Excel за Microsoft 365, разбирането на VLOOKUP остава от решаващо значение за много потребители.
За тези, които са добре запознати с усъвършенстваните формули, задълбоченото разбиране на VLOOKUP може значително да подобри работата им.
Подобно на XLOOKUP, VLOOKUP сканира определена колона в списък и може да извлече или съответстващата стойност за търсене, или която и да е стойност от друга колона в същия ред. Тя позволява на потребителите да търсят конкретна стойност в най-лявата колона на таблица и да връщат съответстваща стойност от определена колона в тази таблица.
Функцията VLOOKUP се състои от четири основни компонента:
- Търсена стойност (стойността, която се търси)
- Масив от таблица (диапазон от клетки, съдържащи данните)
- Индексен номер на колона (колоната, от която да извлечете резултата)
- Търсене на диапазон (незадължителен параметър, определящ дали е необходимо точно съвпадение)
Функция INDEX / MATCH
Комбинацията INDEX/MATCH е изключително ефективен и многофункционален инструмент в Excel, способен да извършва впечатляващи постижения в напреднали формули. Преди XLOOKUP да бъде представен, много опитни потребители на Excel предпочитаха INDEX/MATCH пред VLOOKUP, тъй като се справяше с някои от ограниченията на VLOOKUP.
Дори с вече наличен XLOOKUP, научаването да се използва INDEX/MATCH остава от съществено значение за тези, които искат да овладеят разширените функции на Excel.
Функцията INDEX в Excel връща стойността на клетка в определен диапазон въз основа на номерата на нейните редове и колони. Той действа като мощен инструмент за извличане на данни от дадено място в таблица.
От друга страна, функцията MATCH се използва за намиране на позицията на търсена стойност в диапазон от клетки. Той връща относителната позиция на стойността в диапазона, което ви позволява да намерите конкретни данни.
Когато комбинирате INDEX и MATCH, вие създавате формула за динамично търсене, за да търсите стойност в една колона и да върнете съответната стойност от друга. Тази комбинация преодолява ограниченията на VLOOKUP, като например изискването колоната за справка да бъде най-лявата колона в таблицата.
Функция SUMPRODUCT
Функцията SUMPRODUCT в Excel е любима сред напредналите потребители, като предлага повече от просто основни аритметични изчисления. Въпреки че името му подсказва, че се използва за умножение и сумиране на числа, неговите възможности се простират отвъд това.
Първоначално SUMPRODUCT имаше за цел да изчисли сумата от продуктите в две колони. Въпреки че това е валиден случай на употреба, потребителите на Excel откриха неговата гъвкавост и започнаха да го използват за по-сложни цели.
Напредналите потребители на Excel често използват формулата SUMPRODUCT за условни суми или изчисления.
Например:
Ако имате набор от данни, съдържащ търговци, съответните им региони и техните номера на продажби, можете да използвате SUMPRODUCT за:
- Добавете стойности на продажбите за определени региони или множество региони.
- Сумирайте само стойностите на продажбите, които надхвърлят определен праг.
- Извършвайте условни изчисления въз основа на стойности на продажбите над или под конкретен праг, като използвате условието ИЛИ.
Чрез включването на логически условия във функцията SUMPRODUCT, потребителите могат да приспособят своите изчисления, за да извлекат специфични подгрупи от данни въз основа на желани критерии. Това го прави мощен инструмент за извършване на сложни анализи и извличане на информация от големи набори от данни.
Функции SUMIF/COUNTIF & SUMIFS/COUNTIFS
Функциите SUMIF, COUNTIF, SUMIFS и COUNTIFS са групирани заедно, защото работят по подобен начин, като позволяват условно сумиране или преброяване в рамките на набор от данни.
Например, ако имате списък с имена и искате да преброите колко пъти определено име се появява бързо, можете да използвате функцията COUNTIF.
По същия начин, ако имате набор от данни с имената на търговските представители и съответните стойности на продажбите и искате да изчислите сумата от продажби за конкретен търговски представител, функцията SUMIF е инструментът, който трябва да използвате.
В случаите, когато трябва да се вземат предвид множество условия, като например получаване на сумата от продажби за конкретен търговски представител през определен месец, функцията SUMIFS влиза в действие. Тази функция позволява прилагането на множество критерии, като името на търговския представител и датата, за извършване на операцията за сумиране.
По същия начин функцията COUNTIFS се използва при броене въз основа на множество условия в набор от данни.
Функция ФИЛТЪР
Функцията FILTER е ценно допълнение към Excel 2021 и Excel за Microsoft 365, предоставяйки ефективен начин за филтриране и извличане на данни от набор от данни.
Както подсказва името, функцията FILTER позволява на потребителите да прилагат филтри към набор от данни в Excel, позволявайки извличането на конкретни резултати. Преди въвеждането на тази функция, постигането на подобни резултати изискваше сложни и продължителни решения. Изображение 7
За да илюстрирате полезността на функцията FILTER, помислете за набор от данни, съдържащ имена на търговски представители, съответните им региони или държави и техните стойности на продажбите. С функцията FILTER филтрирането на всички записи, свързани с определена държава, става лесно.
Възможността за използване на препратка към клетка вместо твърдо кодиране на името на държавата във формулата я прави още по-мощна. Като поставят името на държавата в клетка и го позовават във формулата, потребителите могат лесно да променят името на държавата в клетката и формулата автоматично ще предостави съответните записи за тази държава.
Често задавани въпроси
Какво представлява функцията за зони в Excel?
Функцията "AREAS" в Excel не е вградена функция. Не съществува в библиотеката със стандартни функции на Excel.
Кои са 5-те мощни функции на Excel, които улесняват работата?
Пет мощни функции на Excel, които могат да улеснят работата, включват VLOOKUP, SUMIF, INDEX, MATCH и IFERROR.
Кои са 5-те основни умения за работа с Excel?
Пет основни умения за Excel включват разбиране на реферирането на клетки, използване на формули и функции, форматиране на данни и клетки, създаване на диаграми и графики и филтриране и сортиране на данни.
Каква е разликата между Excel и разширен Excel?
Excel се отнася до стандартния софтуер за електронни таблици, който включва основни функции и характеристики, докато разширеният Excel обикновено се отнася до използването на сложни формули, разширени функции, инструменти за анализ на данни и техники за автоматизация в рамките на Excel.
Какви са нивата на умения за Excel?
Уменията за Excel често се категоризират на начинаещи, средно напреднали и напреднали. Уменията на ниво за начинаещи обхващат основни задачи и формули, уменията на средно ниво включват по-сложни формули и манипулиране на данни, а уменията на ниво за напреднали обхващат разширени функции, макроси и техники за анализ на данни.
Последни мисли
Някои ключови области и функции в разширения Excel могат значително да повишат вашите умения и ефективност със софтуера. Функции като XLOOKUP, INDEX/MATCH, SUMPRODUCT, SUMIF/COUNTIF и функцията FILTER предлагат мощни инструменти за анализ на данни, търсене, условни изчисления и филтриране.
Овладяването на тези функции ви позволява да манипулирате и извличате ценни прозрения от сложни набори от данни. Разбирането на разширени области като условно форматиране, обобщени таблици, валидиране на данни и формули за масиви може допълнително да подобри вашите умения за Excel.
Още нещо
Ако имате секунда, моля, споделете тази статия в социалните мрежи; някой друг също може да се възползва.
Абонирайте се за нашия бюлетин и бъдете първите, които ще четат нашите бъдещи статии, рецензии и публикации в блогове направо във входящата си електронна поща. Ние също така предлагаме сделки, промоции и актуализации за нашите продукти и ги споделяме по имейл. Няма да пропуснете нито един.
Свързани статии
» Овладяване на моделирането на данни в Excel: усъвършенствани техники и най-добри практики
» Овладяване на разширено филтриране в Excel: съвети и техники
» Изчисляване на корен квадратен в Excel: лесни стъпки за точни резултати