10 основни функции на Microsoft Excel за анализ на данни

1. КОНКЕТИРАНЕ

=CONCATENATE е една от най-важните функции за анализ на данни, тъй като ви позволява да комбинирате текст, числа, дати и т.н. от множество клетки в една. Функцията е особено полезна за комбиниране на данни от различни клетки в една клетка. Например, той е удобен за създаване на параметри за проследяване за маркетингови кампании, изграждане на заявки за API, добавяне на текст към числов формат и няколко други неща.

В примера по-горе исках месеца и продажбите заедно в една колона. За това използвах формулата =CONCATENATE(A2, B2) в клетка C2, за да получа Jan$700 като резултат.

формула: =КОНКАТИРАНЕ(клетки, които искате да комбинирате)

2. LEN

=LEN е друга удобна функция за анализ на данни, която по същество извежда броя на знаците във всяка дадена клетка. Функцията е предимно използваема при създаване на заглавни тагове или описания, които имат ограничение за знаци. Също така може да бъде полезно, когато се опитвате да откриете разликите между различните уникални идентификатори, които често са доста дълги и не са в правилния ред.

В примера по-горе исках да преброя цифрите за броя гледания, които получавах всеки месец. За това използвах формулата =LEN(C2) в клетката D2, за да получа 5 като резултат.

формула: =LEN(клетка)

3. VLOOKUP

=VLOOKUP е може би една от най-разпознаваемите функции за всеки, запознат с анализа на данни. Можете да го използвате, за да съпоставите данни от таблица с входна стойност. Функцията предлага два режима на съвпадение — точно и приблизително, което се контролира от обхвата на търсене. Ако зададете диапазона на FALSE, той ще търси точно съвпадение, но ако го зададете на TRUE, ще търси приблизително съвпадение.

В примера по-горе исках да търся броя на гледанията за конкретен месец. За това използвах формулата =VLOOKUP("Jun", A2:C13, 3) в клетката G4 и получих 74992 като резултат. Тук "Jun" е стойността за търсене, A2:C13 е масивът на таблицата, в който търся "Jun", а 3 е номерът на колоната, в която формулата ще намери съответните изгледи за юни.

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

формула: =VLOOKUP(търсеща_стойност, таблица_масив, номер_индекс_столче, [диапазон_търсене])

4. ИНДЕКС/СЪВпадение

Подобно на функцията VLOOKUP, функциите INDEX и MATCH са полезни за търсене на конкретни данни въз основа на входна стойност. INDEX и MATCH, когато се използват заедно, могат да преодолеят ограниченията на VLOOKUP за предоставяне на грешни резултати (ако не сте внимателни). Така че, когато комбинирате тези две функции, те могат да определят препратката към данните и да търсят стойност в масив с едно измерение. Това връща координатите на данните като число.

В примера по-горе исках да видя броя на гледанията през януари. За това използвах формулата =ИНДЕКС (A2:C13, MATCH("Jan", A2:A13,0), 3). Тук A2:C13 е колоната с данни, която искам формулата да върне, "Jan" е стойността, която искам да съвпада, A2:A13 е колона, в която формулата ще намери "Jan", а 0 означава, че искам формулата да намери точно съвпадение за стойност.

Ако искате да намерите приблизително съвпадение, ще трябва да замените 0 с 1 или -1. Така че 1 ще намери най-голямата стойност, по-малка или равна на стойността за търсене, а -1 ще намери най-малката стойност, по-малка или равна на стойността за търсене. Имайте предвид, че ако не използвате 0, 1 или -1, формулата ще използва 1, by.

Сега, ако не искате да кодирате името на месеца, можете да го замените с номера на клетката. Така че можем да заменим "Jan" във формулата, спомената по-горе с F3 или A2, за да получим същия резултат.

формула: =ИНДЕКС(колона на данните, които искате да върнете, MATCH(обща точка от данни, която се опитвате да съпоставите, колона на другия източник на данни, който има обща точка от данни, 0))

5. MINIFS/MAXIFS

=MINIFS и =MAXIFS са много подобни на функциите =MIN и =MAX, с изключение на факта, че ви позволяват да вземете минималния/максималния набор от стойности и да ги съпоставите по определени критерии. Така че по същество функцията търси минималните/максималните стойности и я съпоставя с критериите за въвеждане.

В примера по-горе исках да намеря минималните резултати въз основа на пола на ученика. За това използвах формулата =MINIFS (C2:C10, B2:B10, "M") и получих резултата 27. Тук C2:C10 е колоната, в която формулата ще търси резултатите, B2:B10 е колона, в която формулата ще търси критериите (пола), а "M" е критериите.

По същия начин, за максимални резултати използвах формулата =MAXIFS(C2:C10, B2:B10, "M") и получих резултата 100.

Формула за MINIFS: =MINIFS(минимален_диапазон, критерии_диапазон1, критерии1,...)

Формула за MAXIFS: =MAXIFS(максимален_диапазон, критерии_диапазон1, критерии1,...)

6. СРЕДНИ КОФИ

Функцията =AVERAGEIFS ви позволява да намерите средна стойност за конкретен набор от данни въз основа на един или повече критерии. Докато използвате тази функция, трябва да имате предвид, че всеки критерий и среден диапазон могат да бъдат различни. Въпреки това, във функцията =AVERAGEIF и диапазонът на критериите, и диапазонът на сумата трябва да имат еднакъв диапазон на размерите. Забележете разликата в единствено и множествено число между тези функции? Е, там трябва да внимавате.

В този пример исках да намеря средния резултат въз основа на пола на учениците. За това използвах формулата =AVERAGEIFS(C2:C10, B2:B10, "M") и получих 56,8 като резултат. Тук C2:C10 е диапазонът, в който формулата ще търси средната стойност, B2:B10 е диапазонът на критериите, а "M" е критериите.

формула: =СРЕДНИ (среден_диапазон, критерии_диапазон1, критерии1,...)

7. COUNTIFS

Сега, ако искате да преброите броя на случаите, когато набор от данни отговаря на определени критерии, ще трябва да използвате функцията =COUNTIFS. Тази функция ви позволява да добавяте неограничени критерии към вашата заявка и по този начин я прави най-лесният начин за намиране на броя въз основа на критериите за въвеждане.

В този пример исках да намеря броя на учениците от мъжки или женски пол, които са получили положителни оценки (т.е. >=40). За това използвах формулата =COUNTIFS(B2:B10, "M", C2:C10, ">=40"). Тук B2:B10 е диапазонът, в който формулата ще търси първите критерии (пол), "M" е първият критерий, C2:C10 е диапазонът, в който формулата ще търси втория критерий (маркировки), а „>=40“ е вторият критерии.

формула: =БРОЙКИ(критерии_диапазон1, критерии1,...)

8. СУМПРОИЗВОД

Функцията =SUMPRODUCT ви помага да умножите диапазони или масиви заедно и след това връща сумата от продуктите. Това е доста гъвкава функция и може да се използва за броене и сумиране на масиви като COUNTIFS или SUMIFS, но с допълнителна гъвкавост. Можете също да използвате други функции в SUMPRODUCT, за да разширите функционалността му още повече.

В този пример исках да намеря общата сума на всички продадени продукти. За това използвах формулата =SUMPRODUCT(B2:B8, C2:C8). Тук B2:B8 е първият масив (количеството продадени продукти), а C2:C8 е вторият масив (цената на всеки продукт). След това формулата умножава количеството на всеки продаден продукт с неговата цена и след това събира всичко, за да осигури общите продажби.

формула: =SUMPRODUCT(масив1, [масив2], [масив3],...)

9. ОТРЕЖИВАНЕ

Функцията =TRIM е особено полезна, когато работите с набор от данни, който има няколко интервала или нежелани символи. Функцията ви позволява да премахвате тези интервали или знаци от вашите данни с лекота, което ви позволява да получите точни резултати, докато използвате други функции.

В този пример исках да премахна всички допълнителни интервали между думите Mouse и pad в A7. За това използвах формулата =TRIM(A7).

Формулата просто премахна допълнителните интервали и предостави резултата Подложка за мишка с едно интервал.

формула: =ОТРИМ(текст)

10. НАМИРАНЕ/ТЪРСЕНЕ

Закръгляването на нещата са функциите НАМИРАНЕ/ТЪРСЕНЕ, които ще ви помогнат да изолирате конкретен текст в набор от данни. И двете функции са доста сходни в това, което правят, с изключение на една основна разлика — функцията =FIND връща само съвпадения, чувствителни към малки и големи букви. Междувременно функцията =SEARCH няма такива ограничения. Тези функции са особено полезни при търсене на аномалии или уникални идентификатори.

В този пример исках да намеря колко пъти „Gui“ се появи в Guiding Tech, за което използвах формулата =FIND(A2, B2), която даде резултат 1. Сега, ако исках да намеря колко пъти 'gui' се е появил в Guiding Tech вместо това, бих трябвало да използвам формулата =SEARCH, защото не е чувствителна към главните букви.

Формула за намиране: =НАМЕРИ(намерен_текст, в_текст, [начален_номер])

Формула за търсене: =ТЪРСЕНЕ(намерен_текст, в_текст, [начален_номер])