Ексел е може би най-успешната компютърна програма, създавана някога. Значителни дялове на икономиката се крепят благодарение на огромната функционалност на този софтуер. В днешно време базови знания по Ексел са необходимо условие да започнете работа. Същевременно, задълбоченото познаване на Ексел, отваря пред вас нови хоризонти и е една сериозна заявка за успешно кариерно развитие. В този урок представяме на вашето внимание следните формули за напреднали, с които не само ще впечатлите шефовете си, но и ще придобиете сериозно предимство пред вашите конкуренти.

Index-Match

Една от основните функции в Ексел е vlookup. Vlookup е изключително мощна функция, с която можете да съпоставяте данни от различни таблици. С vlookup можете да намирате данни от различни екселски спредшийтове и дори различни файлове. В резултат на това, могат да бъдат спестени часове и дори дни наред (при таблици от няколко хиляди символа) ръчно съпоставяне на данните. Като добавим към огромната функционалност на vlookup и това, че формулата е сравнително лесна за научаване, vlookup изглежда на пръв поглед непоклатима, царицата на формулите.

 На практика това не е така. Истинските майстори на Ексел използват Index-Match вместо Vlookup. Всъщност единствения плюс на vlookup пред index-match е, че vlookup е по-опростена и лесна за ползване. Поради опростения си характер, vlookup има редица ограничения в търсенето на данни и в много случаи не върши никаква работа. Тогава на помощ се явява функцията index-match. Все още не сте убедени? Нека да разгледаме следния пример.

Пример 1

Имаме таблица с данни за служители и фирми. Искаме да разберем:

1. В коя фирма работи Димка Тодорова

2 Кой е служителят на фирма Проспект в таблицата.

За да намерим отговора на първия въпрос можем да използваме и обикновен vlookup. Интересуват ни само колони A и C. Функцията =VLOOKUP(A5,A1:D10,3,0)  изкарва данните срещу нейното име(A5) от третата колона в таблица A1:D10 срещу нейното име  и отговорът е логично фирма „Проспект”.

Можем да стигнем до същия отговор и с Index-match. В случая формулата е: =INDEX(C1:C10,MATCH(A5,A1:A10,0)). С тази формула достигаме до същия резултат, но логиката е различна. Формулата не започва с детерминантата А5, а с колоната която търсим (C1:C10) и мачва данните от нея с детерминантата в колона А(А1:А10). За разлика от vlookup, тази формула не използва рейнджове, а търси само в конкретни колони. Това всъщност е едно от най-големите и предимства. Ще разберем защо, когато се опитаме да отговорим на втория въпрос.

Не става с vlookup, нали? Няма как да разберем чрез vlookup кой е служителят на фирма Проспект, тъй като vlookup има едно голямо ограничение: може да търси само от ляво надясно. Тук на помощ идва вълшебството index-match:  =INDEX(A1:A10,MATCH(C5,C1:C10,0)).

Index-match е любимата формула на специалистите, не само в този случай, но и в редица други ситуации, поради сериозното превъзходство на тази формула пред vlookup:

Значително предимство на index-match е, че таблицата може да се разширява, като се добавят нови колони, без това да разваля формулата. Може да се добавя нова колона в средата на таблицата, както и нова колона в края. Формулата продължава да фунционира правилно.

• Счита се, че рискът да се допусне грешка с index-match е много-по малък, поради това, че се използват само две колони и не е необходимо да се броят ръчно колоните. Това е от особено значение, когато се ползват огромни таблици с много колони.
• Index-match може да прави хоризонтален lookup (hlookup), за разлика от vlookup, която може да търси само вертикално.
• Index-match заема значително по-малко изчислителна мощ на компютъра, затова е задължителния начин за търсене в по-големи таблици с повече от няколко хиляди редове, тъй като работи няколко пъти по-бързо от vlookup.

В заключение: index-match може да е по-сложна функция от vlookup, но след като я запомните и използвате няколко пъти, тя ще стане ваш незаменим помощник при работа със софтуера, поради много по-голямата и функционалност от vlookup.

3-D Sum

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

Имаме информация за разходите, които сме направили всяка седмица за алкохол, цигари и храна. Искаме да изчислим колко общо разходи за алкохол сме направили през съботите. За целата сумираме стойностите в клетки B2 на всички шийтове: =SUM('Седмица 1:Седмица 4'!B2). В случая в периода за един месец през съботните дни са направени общо 26 лв разходи за алкохол.

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

Goal Seek

Цели индустрии не биха били това, което са, без goal seek помощника на Ексел. Изключително полезна функция за хората, които работят в сферата на продажбите и консултантските услуги. Това е един изключително удобен инструмент, който ви позволява да изчислите с колко трябва да увеличите „А”, за да достигнете определено ниво на „Б”

Например, нека да разгледаме примера на Уолтър Хики, автор в Бизнес Инсайдър. Имаме таблица с продажби за определен период от време и искаме да видим колко продажби трябва да направим през следващия месец, за да достигнем определено ниво на приходите за тримесичието. 

Да кажем, че искаме да променим броя приходи в клетка F3 на 100 000 000$ и искаме да видим колко продажби трябва да бъдат направени за 3-тия месец. За целта отиваме на data таба -> data tools групата -> what if analysis ->go seak и задаваме параметрите:

Отговорът е 44 476 единици и това е броят продажби, който трябва да се направят, за да достигнат приходите 100 млн $.

Формули за масиви

Функциите с масиви представляват по-сложни формули, тъй като се налага да се използват данни от повече от една клетка, за да се върне резултат. Те не се въвеждат като стандартни формули, а с клавишната комбинация control + shift + enter. Например, масивите могат да послужат при умножение на матрици или при събиране на числа през един ред. Нека разгледаме следния пример

Искаме да умножим числата от клетки C и D, след което да изчислим техния сбор. За целта можем да ползваме две формули или да ползваме функция с масиви: {=SUM(C2:C11*D2:D11) }. Огромното предимство на тази формула е, че ако имаме хиляди редове можем да изчислим всичко това много по-лесно и бързо, само в една клетка. Формулите за масиви са предпочитани от експертите на Ексел, тъй като се смята, че риска да се допусне грешка при тях е значително по-малък и обикновено са много по-удобни, когато се използват вложени функции.

Sumifs

Sumif е една отлична функция, за сумиране на данни по определен критерии. Често обаче, както е  в живота, нещата не са толкова очевидни и може да има повече от един фактор, който трябва да се вземе под внимание. На помощ идват функцията Sumifs, чрез която може да се сумират данни, които отговарят на повече от един критерий. Например имаме следната таблица:

Искаме да видим не само какво количесто от определе продукт е доставено, но и в зависимост от доставчиците. За да видим колко ябълки е доставил точно определен човек, например Иван, използваме следната формула:

=SUMIFS(C2:C10, A2:A10, "ябълки", B2:B10, "Иван")

Отговорът  е 3 кг.

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