Инструменти за анализ на данни в Excel за визуализация, моделиране и др

Електронните таблици са издържали изпитанието на времето, оставайки популярен инструмент за анализ на данни от въвеждането им през 1979 г. Дори в ерата на усъвършенстваните езици за програмиране и бази данни, Microsoft Excel продължава да доминира, като се гордее с над 750 милиона потребители в световен мащаб.

Популярността на Excel произтича от обширните му характеристики и функции, които дават възможност на анализаторите да почистват, обобщават, насочват и визуализират безпроблемно данни. В тази статия ще разгледаме топ 10 на основните характеристики и функции на Excel за анализ на данни.

Пивот таблици и пивот диаграми

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

Изберете диапазона от данни и въведете Insert
Обобщена диаграма > Обобщена диаграма и обобщена таблица.

След като се появи редакторът за създаване на обобщена таблица, избраният диапазон автоматично ще попълни полето Таблица/диапазон. Щракването върху OK генерира осевата таблица. В полетата на обобщената диаграма плъзнете желаното поле (напр. Семейно_положение) в полето Ос (Категории) и полето Стойности. Ако типът данни е низ, агрегацията по подразбиране е Count; ако е числово, по подразбиране е Sum.

Обобщената таблица и диаграмата се попълват само с няколко кликвания, визуално представяйки данните. Допълнителни измерения или филтри могат да се добавят чрез плъзгане на нови полета в съответните полета. Тази простота и гъвкавост правят обобщените таблици и диаграми широко предпочитани за агрегиране на данни и визуализация в Excel.

Предимства на обобщените таблици и диаграми:

Опростете анализа на данни : Трансформирането на необработени данни в обобщен формат става лесно.
Бързи прозрения : Визуализирането на данни чрез диаграми позволява лесно идентифициране на модели, тенденции и отклонения.
Гъвкаво персонализиране : Полетата могат лесно да се добавят, премахват или пренареждат, за да персонализирате анализа.
Интерактивно изследване : осевите диаграми позволяват на потребителите да филтрират и разбиват конкретни подмножества от данни за по-задълбочен анализ.
Автоматични актуализации : Когато основните данни се променят, обобщените таблици и диаграми могат да бъдат обновени, за да отразят актуализираната информация.

Условно форматиране

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

За да илюстрираме, искаме да подчертаем всички стойности в колоната "Year_Birth", които са по-големи от 1987.

Изберете колоната.
Отидете на Условно форматиране.
Маркирайте Правила за клетки > По-голямо от и ще се появи редакторът на правила.
Въведете стойността 1987 и щракнете върху OK. Клетките в колоната със стойности над 1987 ще бъдат маркирани в светло червено.

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

Премахване на дубликати

Данните често съдържат дубликати, което може да попречи на точния анализ. Excel предоставя удобна функция за премахване на дубликати и рационализиране на вашите данни. Преди да изтриете дубликати, можете да използвате условно форматиране, за да ги маркирате за преглед. Отидете на Данни > Инструменти за данни > Премахване на дубликати за достъп до функцията за премахване на дубликати.

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

За да премахнете дубликати, трябва да следвате стъпките, споменати по-рано. След като изберете набора от данни, ще отидете на Данни > Инструменти за данни > Премахване на дубликати.

Ще се появи редакторът за премахване на дубликати, който ви позволява да изберете колоните, които да имате предвид при идентифицирането на дубликати. В този случай можете да изберете и трите колони: име, възраст и град. Трябва също така да се уверите, че е поставена отметка в квадратчето „Моите данни имат заглавки“.

След като конфигурирате настройките, щракнете върху OK. Excel ще премахне дублиращите се записи и съобщение ще посочи броя на уникалните стойности в набора от данни. В този пример, след премахване на дубликати, таблицата ще изглежда така:

Както можете да видите, дублиращите се записи за Джон и Сара са премахнати, което води до набор от данни с четири уникални стойности.

XLOOKUP

XLOOKUP е многофункционална функция в Excel, която съчетава функционалността на VLOOKUP и HLOOKUP. Тя ви позволява да търсите стойност в диапазон, независимо дали вертикално или хоризонтално, и да извличате съответния резултат. Синтаксисът за функцията XLOOKUP е както следва:

=XLOOKUP(търсена_стойност, търсен_масив, върнат_масив, [ако_не_намерено], [режим_на_съвпадение], [режим_на_търсене])

За да илюстрираме, нека разгледаме пример, в който искаме да намерим Year_Birth въз основа на въведена стойност на ID. В клетка AD2 въведете стойността на ID (напр. 8755), а в клетка AE2 въведете формулата XLOOKUP:

=XLOOKUP(

Lookup_value е стойността, която искаме да търсим, така че препращаме към AD2.

Lookup_array е колоната или редът, който съдържа търсените стойности, така че избираме A2:A2241, за да получим масив от идентификатори.

return_array е колоната или редът, който съдържа стойностите, които искаме да извлечем, така че избираме B2:B2241, за да получим стойности Year_Birth.

Завършената формула ще изглежда така: =XLOOKUP(AD2, A2:A2241, B2:B2241)

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

Функция IFERROR

Функцията IFERROR в Excel ви позволява да обработвате грешки във формула, като предоставяте персонализирано съобщение за грешка или алтернативна стойност. Синтаксисът му е ясен:

=АКОГРЕШКА(стойност, стойност_при_грешка)

В контекста на функцията XLOOKUP, ако ИД, въведен в клетка AD2, не бъде намерен в масива за търсене, клетка AE2 ще покаже грешката #N/A. Можем да използваме функцията IFERROR, за да обвием функцията XLOOKUP, за да предоставим по-смислено съобщение. Формулата ще изглежда така:

=IFERROR(XLOOKUP(AD2, A2:A2241, B2:B2241), "ИД не е намерен")

С тази формула, ако функцията XLOOKUP срещне грешка (напр. ID не е намерен), тя ще покаже указаното потребителско съобщение „ID не е намерен“ в клетка AE2.

Вместо текстово съобщение можете също да използвате друга клетка като value_if_error. Ако посочите празна клетка като стойност, тя ще покаже 0 в клетката, където възниква грешката.

Заредете и активирайте Analysis Toolpak

Analysis ToolPak е мощна добавка за Excel, която ви позволява лесно да извършвате сложни статистически или инженерни анализи. С помощта на този инструмент можете да спестите време и да опростите процеса на анализ на данни. ToolPak използва набор от статистически и инженерни макро функции за изчисляване и показване на резултатите в изходни таблици и дори може да генерира диаграми.

За достъп до Analysis ToolPak:

Отидете в раздела Данни.
Щракнете върху бутона Анализ на данни в групата Анализ.

Ако не виждате командата Data Analysis, трябва да заредите програмата за добавка Analysis ToolPak.

За да заредите и активирате Analysis ToolPak, изпълнете следните стъпки:

Щракнете върху раздела Файл, изберете Опции и отидете в категорията Добавки.
В полето Управление изберете Добавки на Excel и щракнете върху бутона Старт.
За Excel на Mac отидете в менюто Инструменти и изберете Добавки на Excel.
Поставете отметка в квадратчето Analysis ToolPak в полето Add-Ins и щракнете върху OK.
Ако не виждате Analysis ToolPak в списъка, щракнете върху Преглед, за да го намерите.
Ако бъдете подканени, щракнете върху Да, за да инсталирате Analysis ToolPak, ако в момента не е инсталиран на вашия компютър.

Забележка : Ако искате да включите функции на Visual Basic for Application (VBA) за Analysis ToolPak, можете също да заредите Analysis ToolPak - VBA Add-in като Analysis ToolPak.

Анова

Инструментите за анализ на Anova в Excel предоставят различни видове анализ на дисперсията за сравняване на множество проби или фактори. Конкретният инструмент, който трябва да използвате, зависи от броя на факторите и пробите, които имате.

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

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

Корелация

Можете да използвате функциите CORREL или PEARSON в Excel, за да изчислите коефициента на корелация между две променливи на измерване. Тези функции са полезни при наблюдение на всяка променлива от множество субекти. Коефициентът на корелация измерва степента, в която две променливи варират заедно. Инструментът за корелационен анализ в Excel е особено удобен, когато имате повече от две променливи за измерване за всеки предмет.

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

Коефициентът на корелация може да приема стойности между -1 и +1 включително. Положителната корелация показва, че големите стойности на една променлива са свързани с големите стойности на другата, докато отрицателната корелация показва, че малките стойности на една променлива са свързани с големите стойности на другата. Корелация близо до 0 предполага, че променливите не са свързани.

Ковариация

В Excel инструментите за корелация и ковариация могат да се използват за анализиране на набори от N измервателни променливи, наблюдавани в група от индивиди. Тези инструменти предоставят изходни таблици или матрици, които показват съответно коефициента на корелация или ковариацията между всяка двойка измервателни променливи.

Основната разлика между корелацията и ковариацията е в тяхното мащабиране. Коефициентите на корелация се мащабират в диапазона между -1 и +1, докато ковариациите не се мащабират. Както корелацията, така и ковариацията измерват степента, в която две променливи „варират заедно“ или са свързани.

Инструментът за ковариация в Excel изчислява стойността на COVARIANCE.P за всяка двойка променливи за измерване. Ако съществуват само две променливи, можете да използвате директно функцията COVARIANCE.P. Диагоналните записи в изходната таблица на инструмента за ковариация представляват ковариацията на всяка измервана променлива със самата себе си, което е еквивалентно на дисперсията на популацията, изчислена с помощта на функцията VAR.P.

Описателна статистика

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

Ето какво трябва да знаете за инструмента за описателна статистика:

Централна тенденция : Докладът включва мерки, които описват центъра на разпределението на данните, като средна (средна), медиана (средна стойност) и режим (най-честа стойност).
Променливост : Инструментът изчислява различни мерки, които показват разпространението или дисперсията на данните, като диапазон (разлика между максималните и минималните стойности), дисперсия (средно квадратно отклонение от средната стойност) и стандартно отклонение (корен квадратен от дисперсията ).
Форма на разпределение : Инструментът също така предоставя информация за формата на разпределението на данните, включително неравномерност (асиметрия на разпределението) и ексцес (изпъкналост или плоскост на разпределението).
Квартили и процентили : Отчетът включва квартили (разделящи данните на четири равни части) и процентили (разделящи данните на стотни), които дават представа за разпределението на данните на различни нива.
Брой и липсващи стойности : Инструментът брои броя на точките от данни и идентифицира всички липсващи стойности, като гарантира, че разбирате напълно набора от данни.

Често задавани въпроси

Кой инструмент се използва за анализ на данни в Excel?

Excel предоставя различни инструменти за анализ на данни, включително функции, диаграми, обобщени таблици и добавките Power Query и Power Pivot.

Какви са функциите за анализ на данни на Excel?

Excel предлага широка гама от функции за анализ на данни като SUM, AVERAGE, COUNT, MIN, MAX, IF, VLOOKUP и много други, които ви позволяват да извършвате изчисления и да манипулирате данни ефективно.

Microsoft Excel анализира ли данни?

Microsoft Excel се използва широко за анализ на данни поради своя изчерпателен набор от функции, функции и инструменти, предназначени специално за анализиране и интерпретиране на данни.

Как извършвате анализ на данни?

За да извършите анализ на данни в Excel, можете да започнете с организиране на вашите данни в табличен формат, прилагане на подходящи функции и формули, създаване на диаграми и графики, използване на обобщени таблици и използване на разширени инструменти като Power Query и Power Pivot за по-сложен анализ.

Обяснете подробно какво представлява анализът на данни?

Анализът на данни е процесът на проверка, почистване, трансформиране и моделиране на данни, за да се открие полезна информация, да се направят заключения и да се подпомогне вземането на решения. Включва различни техники като статистически анализ, визуализация на данни, извличане на данни и разпознаване на модели за разкриване на модели, тенденции и прозрения от данните.

Последни мисли

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

Възможностите на Excel за моделиране на данни ви позволяват да организирате и структурирате вашите данни за разширени изчисления и анализ на сценарии. Инструментите за регресионен анализ в Excel ви позволяват да идентифицирате връзки и да правите прогнози въз основа на вашите данни.

Освен тези специфични функции, Excel предлага широк набор от допълнителни инструменти и функции за анализ, за да изследвате и анализирате вашите данни ефективно. Като използвате възможностите на Excel за анализ на данни, можете да придобиете ценна информация, да вземете информирани решения и да съобщите констатациите си с яснота и въздействие.

Още нещо

Ако имате секунда, моля, споделете тази статия в социалните мрежи; някой друг също може да се възползва.

Абонирайте се за нашия бюлетин и бъдете първите, които ще четат нашите бъдещи статии, рецензии и публикации в блогове направо във входящата си електронна поща. Ние също така предлагаме сделки, промоции и актуализации за нашите продукти и ги споделяме по имейл. Няма да пропуснете нито един.

Свързани статии

» Овладяване на моделирането на данни в Excel: усъвършенствани техники и най-добри практики
» Ръководство за начинаещи в Microsoft Excel Online: Управление на данни и създаване на електронни таблици
» Манипулиране на основни данни с Excel SUBSTITUTE функция: Ръководство стъпка по стъпка

Намерете всичко необходимо

Анализ на данни в Excel: инструменти за визуализация, моделиране, регресия и др