Какие инструменты в Excel следует использовать для обработки больших объемов данных

Обновлено: 20.11.2024

Одним из преимуществ работы в команде Excel является возможность встретиться с широким кругом клиентов. При общении с пользователями Excel очевидно, что существует значительная путаница в отношении того, что же такое «большие данные». Многим клиентам приходится самостоятельно разбираться в какофонии аббревиатур, технологий, архитектур, бизнес-моделей и вертикальных сценариев.

Поэтому неудивительно, что некоторые люди придумали совершенно разные способы определения понятия «большие данные». Мы слышали от некоторых людей, которые думали, что большие данные работают с двумя тысячами строк данных. И мы слышали от поставщиков, которые утверждают, что работают с большими данными десятилетиями и не считают это чем-то новым. Широкий диапазон интерпретаций иногда напоминает нам старую притчу о слепых и слоне, где группа мужчин прикасается к слону, чтобы узнать, что это такое. Каждый человек чувствует свою часть, но только одну часть, например, хвост или бивень. Затем они сравнивают записи и узнают, что полностью расходятся во мнениях.

Определение больших данных

В команде Excel мы воспользовались советами аналитиков, чтобы определить большие данные как данные, которые включают любое из следующего:

Большой объем — как с точки зрения элементов данных, так и размерности.
Высокая скорость — поступление с очень высокой скоростью, обычно с предположением о малой задержке между поступлением данных и получением значения.
Большое разнообразие – возможность изменения формы и значения данных с течением времени.

И что требует:

Экономичная обработка. Как мы уже упоминали, многие поставщики заявляют, что работают с большими данными десятилетиями. Технически это правильно, однако многие из этих решений полагаются на дорогостоящие масштабируемые машины с нестандартным оборудованием и хранилищами SAN, чтобы получить достаточную мощность. Наиболее многообещающим аспектом больших данных является инновация, позволяющая поступиться некоторыми аспектами решения, чтобы получить беспрецедентно низкие затраты на создание и развертывание решений.
Инновационные типы анализа. Выполнение того же старого анализа большего количества данных, как правило, является хорошим признаком того, что вы занимаетесь масштабированием, а не большими данными.
Новая ценность для бизнеса. В сравнении с этим принципом и предыдущим, если набор данных на самом деле не меняет того, как вы проводите анализ или что вы делаете с аналитическим результатом, то, скорее всего, это не большие данные.

В то же время сообразительные технологи также понимают, что иногда их потребности лучше всего удовлетворяются с помощью проверенных и проверенных технологий. Когда им нужно построить критически важную систему, требующую транзакций ACID, надежного языка запросов и безопасности корпоративного уровня, реляционные базы данных обычно вполне отвечают всем требованиям, особенно по мере того, как реляционные поставщики продвигают свои предложения, чтобы привнести некоторые преимущества новых технологий в своих существующих клиентов. Это требует более глубокого понимания потребностей и технологий для создания наилучшего соответствия.

Роль Excel в больших данных

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

Лучшее место для Excel в категориях сценариев больших данных – исследовательский/специальный анализ. Здесь бизнес-аналитики хотят использовать свой любимый инструмент анализа для новых хранилищ данных, чтобы получить беспрецедентное богатство информации. Они ожидают, что инструменты не ограничиваются аспектами «объема, скорости и разнообразия» больших данных, а также позволяют им задавать новые типы вопросов, которые они не могли задавать ранее: больше неструктурированных данных (например, социальных сетей) в качестве первоклассного входа в их аналитический рабочий процесс.

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

При работе с большими данными существует ряд технологий и методов, которые можно применить, чтобы эти три шаблона были успешными.

Импорт данных в Excel

Многие клиенты используют подключение для переноса внешних данных в Excel в виде обновляемого снимка. Преимущество здесь в том, что он создает автономный документ, который можно использовать для работы в автономном режиме, но который обновляется новыми данными в онлайн-режиме. Поскольку данные хранятся в Excel, клиенты также могут преобразовывать их в соответствии со своим личным контекстом или аналитическими потребностями.

При импорте больших данных в Excel необходимо учитывать несколько ключевых проблем:

Запросы к большим данным. Для источников данных, предназначенных для больших данных, таких как SaaS, HDFS и большие реляционные источники, иногда могут потребоваться специальные инструменты. К счастью, у Excel есть решение: Power Query, встроенное в Excel 2016 и доступное отдельно для загрузки для более ранних версий. Power Query предоставляет несколько современных наборов соединителей для пользователей Excel, включая соединители для реляционных систем, HDFS, SaaS (Dynamics CRM, SalesForce) и т. д. Мы постоянно пополняем этот список и будем рады вашим отзывам о новых соединителях, которые мы должны предоставить поле на нашем UserVoice.
Преобразование данных. Большие данные, как и все данные, редко бывают идеально чистыми. Power Query позволяет создать согласованный, повторяемый и проверяемый набор шагов преобразования данных. Объединив простые действия в серию прикладных шагов, вы можете создать надежный и преобразованный набор данных для работы.

Обработка больших источников данных. Power Query предназначен только для извлечения «головы» набора данных, чтобы предоставить вам быстрый и гибкий предварительный просмотр данных в реальном времени, не требуя загрузки всего набора в память. Затем вы можете работать с запросами, фильтровать только подмножество данных, с которыми вы хотите работать, и импортировать их.
Обработка частично структурированных данных. Мы часто сталкиваемся с необходимостью, особенно в случае больших данных, считывать данные, которые не так четко структурированы, как данные традиционных реляционных баз данных. Он может быть разбросан по нескольким файлам в папке или иметь очень иерархический характер. Power Query предлагает элегантные способы обработки обоих этих случаев. Все файлы в папке можно обрабатывать как единое целое в Power Query, поэтому вы можете создавать мощные преобразования, которые работают с группами (даже с фильтрованными группами!) файлов в папке. Кроме того, несколько хранилищ данных, а также предложения SaaS используют формат данных JSON как способ работы со сложными, вложенными и иерархическими данными. Power Query имеет встроенную поддержку извлечения структуры из данных в формате JSON, что значительно упрощает использование этих сложных данных в Excel.
Обработка больших объемов данных в Excel. Начиная с Excel 2013, функция «Модель данных» в Excel обеспечивает поддержку больших объемов данных, чем ограничение в 1 млн строк на листе. Модель данных также включает представление таблиц, столбцов и взаимосвязей в качестве первоклассных объектов, а также предоставляет предварительно созданные часто используемые бизнес-сценарии, такие как годовой рост или работа с организационными иерархиями. Некоторым клиентам модели данных запаса достаточно для работы с их собственными большими объемами данных. Помимо документации по продукту, несколько наших MVP предоставили отличный контент по Power Pivot и модели данных. Вот несколько статей от Роба Колли и Чанду.

Текущий запрос внешнего источника

Иногда из-за огромного объема данных или модели анализа импорт всех исходных данных в Excel либо запрещен, либо проблематичен (например, из-за проблем с раскрытием данных).

Клиенты, использующие сводные таблицы OLAP, уже хорошо знакомы с преимуществами сочетания упрощенных клиентских функций в сводных таблицах и сводных диаграммах с масштабируемыми внешними механизмами. Интерактивный запрос внешних источников с удобным для бизнеса уровнем метаданных в сводных таблицах позволяет пользователям легко и быстро исследовать и находить полезные агрегаты и фрагменты данных.

Один очень простой способ создать такой внешний источник интерактивных таблиц запросов с большим объемом данных — это «увеличение» модели данных в автономную базу данных служб SQL Server Analysis Services. После того, как пользователь создал модель данных, процесс превращения ее в куб служб SQL Server Analysis Services является относительно простым для специалиста по бизнес-аналитике, что, в свою очередь, позволяет получить централизованно управляемый и контролируемый ресурс, который может обеспечить сложную защиту и поддержку разделения данных.< /p>

По мере появления новых технологий ищите дополнительные коннекторы, которые обеспечивают такой уровень интерактивности с этими внешними источниками.

Экспорт из приложения в Excel

Из-за того, что пользователи знакомы с Excel, функция «Экспорт в Excel» часто запрашивается в различных приложениях. Обычно это создает статический экспорт подмножества данных в исходном приложении, обычно экспортируемого для целей отчетности, свободного от базовых бизнес-правил. Поскольку в браузере размещается все больше приложений, мы добавляем новые API, которые расширяют возможности интеграции с Excel Online.

Обзор

Мы надеемся, что смогли дать вам набор шаблонов, которые помогут сделать обсуждение больших данных более продуктивным в ваших командах.Мы постоянно ищем лучшие способы помочь нашим клиентам разобраться в технологическом ландшафте и будем рады вашим отзывам!

Контекст для Power Pivot… Если вы часто пользуетесь Excel, то, вероятно, знакомы со сводными таблицами. Они используются для быстрого получения информации из небольших объемов данных, а также могут быть преобразованы в простые для понимания графики. Но даже у Excel есть свои ограничения. При объединении таблиц, обработке больших наборов данных, содержащих более миллиона строк, или выборе данных из нескольких источников Excel будет сталкиваться с трудностями. Неожиданное завершение работы Excel, очень медленная работа или истечение времени ожидания, когда требуется принудительное завершение работы, могут вызвать разочарование!

Что произойдет, если у вас будет более миллиона строк (точнее, 1 048 576) данных? Вы используете Power Pivots.

В 2010 году Microsoft добавила Power Pivots в Excel, чтобы упростить анализ больших объемов данных. Power Pivot может обрабатывать сотни миллионов строк данных, что делает его лучшей альтернативой Microsoft Access, который до Excel был единственным способом сделать это. Думайте о Power Pivot как о способе использования сводных таблиц в очень больших наборах данных.

Это также полезно, когда данные поступают из нескольких источников. С помощью Power Pivot вы можете импортировать эти данные только в одну книгу, не используя несколько исходных листов, что может привести к путанице и разочарованию.

Power Pivot был создан для импорта и анализа данных из нескольких источников. В качестве источников данных в Power Pivot можно использовать что угодно, от баз данных Microsoft SQL, Oracle или Access до данных списка SharePoint и текстовых документов.

Доступ к Power Pivot

Power Pivot — это бесплатная надстройка в Excel и постоянная встроенная функция в Excel 2016 и 365. Первым шагом в использовании Power Pivot является добавление его на ленту Excel. В последних версиях Microsoft Excel (13 – 17 дюймов) Power Pivot встроен, но вам может потребоваться его активировать.

Включите Power Pivot, нажав Файл -> Параметры -> Надстройки -> Microsoft Power Pivot для Excel:

Теперь Power Pivot включен, но не совсем готов к использованию. Остался еще один шаг.

Вам нужно будет указать Power Pivot, куда нужно перейти для импорта данных. Для этого щелкните вкладку Power Pivot на ленте -> Управление данными -> Получить внешние данные. В списке источников данных много вариантов. В этом примере будут использоваться данные из другого файла Excel, поэтому выберите опцию Microsoft Excel внизу списка. Импорт больших объемов данных займет некоторое время.

После завершения импорта вы увидите данные в главном окне Power Pivot. Одновременно откроются два окна — обычное окно Excel и окно Power Pivot. Однако вам не обязательно иметь данные на открытой странице Excel.

Создание базовой сводной таблицы Power

Допустим, у вас есть таблица со списком продаж товаров. Каждая строка — это идентификатор клиента, а столбцы — это имя, номер счета, дата, количество и цена.

Поскольку клиенты имеют более одного счета, но только один идентификатор клиента, информацию о клиенте необходимо повторять несколько раз. Это не кажется проблемой, когда есть только несколько счетов-фактур. Но когда сумма счетов начнет составлять один миллион, будет менее эффективно использовать этот формат в базовом Excel и более эффективно создавать Power Pivot.

Для этого откройте новую книгу Excel. Выберите Power Pivot на ленте, затем нажмите «Управление» -> «Из других источников» и прокрутите вниз до Excel. На экране выберите файл с помощью кнопки «Обзор».

Выбрав файл, нажмите кнопку Далее. Хорошо переименовать заголовок «Дружественное имя» в название, описывающее набор данных. В данном случае заголовок был изменен на «Счета-фактуры». Нажмите Готово.

Успех! Список счетов был импортирован в Power Pivot Table. Отсюда вы можете создавать диаграммы сводных таблиц так же, как и с меньшими наборами данных (объяснено в следующем разделе). Опять же, причина, по которой Power Pivot будет использоваться здесь, заключается в том, что данные были в другом формате (SQL, Access, Oracle) или если в файле Excel было более миллиона строк. В противном случае использование базовой функции сводной таблицы в Excel будет работать без ошибок.

Создание сводной диаграммы из сводной таблицы Power

Чтобы создать диаграмму из этой Power Pivot, щелкните значок сводной таблицы на ленте Excel и выберите "Сводная диаграмма" (выберите "Сводная таблица", если вы хотите создать обычную сводную таблицу в Excel перед созданием диаграммы).

Откроется новая книга. Используйте панель инструментов полей справа, чтобы выбрать поля для таблицы. В этом примере заказ каждой компании сравнивается по месяцам. Итак, имя клиента, дата и количество выбраны и включены в сводную диаграмму.

Совет: формулы Power Pivot

Power Pivot имеет множество полезных функций и преимуществ. В дополнение к обычным функциям Excel в нем представлено более 75 новых формул. Вот два хороших, которые нужно знать:

=COUNTROWS: подсчитывает количество строк в источнике данных. Если у вас есть несколько источников данных, связанных друг с другом идентификатором, например названием продукта, вы также можете подсчитать количество строк по отношению к этим идентификаторам.

=SWITCH: переключатель невероятно полезен для данных, которые необходимо редактировать. Например, у вас могут быть строки с числом, обозначающим определенный месяц, вместо фактического названия месяца — 1 для января, 2 для февраля, 3 для марта и т. д. Используйте =SWITCH, чтобы изменить (переключить) все числа на фактический месяц.

Power Pivot автоматически использует вычисление =SUM для суммирования числовых данных, что является отличной функцией. Чтобы изменить тип используемого расчета, щелкните правой кнопкой мыши внутри сводной таблицы и выберите «Настройки поля значений» -> «Суммировать значения по вкладке». Как вы можете видеть на изображении, =COUNT, =AVERAGE, =MIN, =MAX и многие другие варианты.

Совет: Power Pivot и SharePoint

Многие организации используют Microsoft Sharepoint. Панели мониторинга, графики и сводные диаграммы Power Pivot можно публиковать прямо в Sharepoint для быстрого просмотра любым сотрудником вашей организации. Чтобы использовать это, установите подключаемый модуль «Power Pivot для SharePoint» на корпоративном сайте Sharepoint.

Power Pivots может показаться расширенной функцией Excel, но их легко использовать, если вы понимаете, как получить доступ к этой функции и импортировать набор данных. Как только данные будут введены, вы можете запустить сводную таблицу или сводную диаграмму из набора данных, как если бы вы использовали обычную таблицу данных. Power Pivot — это один из самых быстрых способов предоставить вам легкое представление о больших объемах данных, которые в противном случае могут привести к сбою Excel или, как минимум, свести вас с ума. Поэтому, если вы обнаружите, что в электронной таблице миллионы строк, найдите и используйте Power Pivot.

Если вы еще не освоили сводные таблицы, рекомендуем начать с них, прежде чем переходить на Power Pivot. Познакомьтесь со сводными таблицами ЗДЕСЬ, посмотрите часовой записанный веб-семинар по сводным таблицам ЗДЕСЬ.

Чтобы получить более подробное руководство по Power Pivot, ознакомьтесь с этим:

Наконец, чтобы загрузить наши 100 самых полезных советов по Excel, нажмите ЗДЕСЬ

Задайте вопрос или присоединитесь к обсуждению всего, что связано с Excel, на нашем канале Slack.

В Excel более 475 функций. Это может затруднить начало анализа данных.

С таким большим разнообразием функций может быть трудно понять, какую из них использовать для конкретных задач Excel.

Самые полезные функции Excel — это те, которые облегчают задачу. И хорошая новость заключается в том, что у большинства пользователей Excel есть набор инструментов, состоящий всего из нескольких функций, которые удовлетворяют большинство их потребностей.

Этот ресурс охватывает 12 наиболее полезных функций Excel для анализа данных. Эти функции предоставляют инструменты для решения большинства задач анализа данных Excel.

Загрузите бесплатную рабочую тетрадь

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

Изучите самые полезные функции Excel

Загрузите файл с упражнениями БЕСПЛАТНО, чтобы следовать ему

Функция ЕСЛИ чрезвычайно полезна. Эта функция означает, что мы можем автоматизировать принятие решений в наших электронных таблицах.

С помощью ЕСЛИ мы могли заставить Excel выполнять другие вычисления или отображать другое значение в зависимости от результата логического теста (решения).

Функция ЕСЛИ запрашивает выполнение логического теста, какое действие следует предпринять, если тест верен, и альтернативное действие, если результат теста неверен.

=IF(логический тест, значение, если оно истинно, значение, если оно ложно)

В этом примере мы отображаем слово «Да», если дата доставки в столбце C более чем на 7 дней позже даты заказа в столбце B. В противном случае отображается слово «Нет».

2. СУММЕСЛИМН

СУММЕСЛИМН — одна из самых полезных функций Excel. Он суммирует значения, соответствующие заданным критериям.

Excel также имеет функцию СУММЕСЛИМН, которая выполняет ту же задачу, за исключением того, что она может проверять только одно условие, а СУММЕСЛИМН может проверять многие.

Таким образом, вы можете игнорировать СУММЕСЛИ, поскольку СУММЕСЛИМН — более совершенная функция.

Функция запрашивает диапазон значений для суммирования, а затем каждый диапазон для проверки и критерии для проверки.

=СУММЕСЛИМН(диапазон суммы, диапазон критериев 1, критерий 1, …)

В этом примере мы суммируем значения в столбце C для региона, введенного в ячейку E3.

Определенно стоит более подробно изучить функцию СУММЕСЛИМН. Это чрезвычайно полезная функция Excel.

3. СЧЁТЕСЛИМН — укажите средние и максимальные значения

Функция СЧЁТЕСЛИМН — ещё одна мегафункция для анализа данных Excel.

Она очень похожа на функцию СУММЕСЛИМН. И хотя они не упоминаются в числе 12 наиболее полезных функций Excel для анализа данных, существуют также функции СРЗНАЧЕСЛИ, МАКСИМС и МИНИМСЛИМН.

Функция СЧЁТЕСЛИМН подсчитает количество значений, соответствующих заданным критериям. Поэтому для него не требуется диапазон суммы, такой как СУММЕСЛИМН.

=COUNTIFS(диапазон критериев 1, критерии 1, …)

В этом примере мы подсчитываем количество продаж из региона, введенное в ячейку E3, со значением 200 или более.

При использовании функций СУММЕСЛИМН и СЧЁТЕСЛИМН критерии необходимо вводить в виде текста или ссылки на ячейку. В этом примере оба метода используются в одной формуле.

4. ОБРЕЗАТЬ

Эта замечательная функция удалит из ячейки все пробелы, кроме одиночных пробелов между словами.

Самое распространенное использование этой функции — удаление пробелов в конце. Это обычно происходит, когда содержимое вставляется откуда-то еще или когда пользователи случайно вводят пробелы в конце текста.

В этом примере функция СЧЁТЕСЛИМН не работает, так как в конце ячейки B6 был случайно использован пробел.

Пользователи не могут видеть это пространство, что означает, что оно не идентифицируется до тех пор, пока что-то не перестанет работать.

Функция TRIM предложит вам удалить пробелы из текста.

В этом примере функция TRIM используется в отдельном столбце для очистки данных в столбце региона, готовых к анализу.

Тогда функция СЧЁТЕСЛИМН имеет чистые данные и работает правильно.

5. СЦЕПИТЬ

Функция СЦЕПИТЬ объединяет значения из нескольких ячеек в одно.

Это удобно для объединения различных частей текста, таких как чье-либо имя, адрес, номер ссылки, путь к файлу или URL-адрес.

Он предложит вам использовать различные значения.

=СЦЕПИТЬ(текст1, текст2, текст3, …)

В этом примере СЦЕПИТЬ используется для объединения имени и фамилии в полное имя. В качестве аргумента text2 вводится пробел.

6. ВЛЕВО/ВПРАВО

Функции ВЛЕВО и ВПРАВО будут выполнять противоположное действие СЦЕПИТЬ. Они извлекут указанное количество символов из начала и конца текста.

Это можно использовать для извлечения частей адреса, URL или ссылки для дальнейшего анализа.

Функции ВЛЕВО и ВПРАВО запрашивают одну и ту же информацию. Они хотят знать, где находится текст и сколько символов вы хотите извлечь.

=LEFT(текст, число символов)

=RIGHT(текст, количество символов)

В этом примере столбец A содержит ссылку, состоящую из идентификатора клиента (первые два символа), идентификатора транзакции и кода региона (последний символ).

Следующая ЛЕВАЯ функция используется для извлечения идентификатора клиента.

Функция RIGHT может использоваться для извлечения последнего символа из ячеек в столбце A. В этом примере показано, находится ли клиент на юге или на севере.

7. ВПР

Функция ВПР — одна из наиболее часто используемых и узнаваемых функций в Excel.

Он будет искать значение в таблице и возвращать информацию из другого столбца, относящуюся к этому значению.

Он отлично подходит для объединения данных из разных списков в один или для сравнения двух списков на наличие совпадающих или отсутствующих элементов. Это важный инструмент анализа данных Excel.

Он запрашивает четыре элемента информации:

Значение, которое вы хотите найти
В какой таблице искать
В каком столбце содержится информация, которую вы хотите вернуть
Какой тип поиска вы хотите выполнить.

=VLOOKUP(искомое значение, массив таблиц, порядковый номер столбца, поиск в диапазоне)

В этом примере у нас есть таблица, содержащая данные о продажах наших сотрудников. Существует еще одна таблица с дополнительной информацией об этих сотрудниках (для примера таблицы сделаны небольшими).

Мы хотели бы внести данные, показывающие, в каком регионе находится сотрудник, в таблицу продаж для анализа.

В столбце D используется следующая формула:

Это может быть одна из самых сложных функций для новичков в формулах Excel. Подробнее узнать о функции ВПР можно в этой статье или в нашем всеобъемлющем курсе Excel.

8. ЕСЛИ ОШИБКА

Иногда случаются ошибки, которые могут быть невинными, а иногда эти ошибки можно предсказать. Функция VLOOKUP, описанная ранее, является типичным примером этого.

У нас возникла ошибка из-за опечатки в имени в таблице продаж. Это означает, что ВПР не может найти это имя и выдает ошибку.

Используя ЕСЛИОШИБКА, мы могли бы отобразить более значимую ошибку, чем та, которую предоставляет Excel, или даже выполнить другой расчет.

Функция ЕСЛИОШИБКА требует двух вещей. Значение, которое необходимо проверить на наличие ошибки и какое действие следует выполнить вместо этого.

В этом примере мы используем функцию ЕСЛИОШИБКА для функции ВПР, чтобы отобразить более осмысленное сообщение.

=IFERROR(VLOOKUP(B2,$G$2:$H$12,2,FALSE),"Имя не найдено. Проверьте оба списка")

9. ЗНАЧЕНИЕ

Часто набор данных, которые вам нужно проанализировать, был импортирован из другой системы или откуда-то скопирован и вставлен.

Это часто может привести к тому, что данные будут в неправильном формате, например, число будет сохранено как текст. Вы не можете выполнять задачи анализа данных, такие как суммирование, если Excel не распознает их как числа.

К счастью, функция VALUE здесь, чтобы помочь. Его работа заключается в преобразовании чисел, хранящихся в виде текста, в числа.

Функция предлагает преобразовать текст.

В этом примере следующая формула преобразует значения продаж, хранящиеся в виде текста в столбце B, в число.

10. УНИКАЛЬНЫЙ

УНИКАЛЬНАЯ функция — это новая функция, доступная только тем, кто использует версию Microsoft 365.

Функция хочет знать три вещи:

Диапазон, из которого возвращается уникальный список
Вы хотите проверить уникальные значения по столбцу или по строке.
Хотите ли вы уникальный список или отдельный список (элементы, которые встречаются только один раз).

=UNIQUE(массив, по столбцу, ровно один раз)

В этом примере у нас есть список продаж продуктов, и мы хотим извлечь уникальный список названий продуктов. Для этого нам нужно только указать диапазон.

Это функция динамического массива, поэтому результаты выбрасываются. Синяя рамка указывает на разбросанный диапазон.

Затем мы можем использовать функцию СУММЕСЛИМН, упомянутую ранее в этой статье, для суммирования продаж каждого из этих продуктов.

11. СОРТИРОВАТЬ

Это еще одна функция, доступная только подписчикам Microsoft 365. Как следует из названия, он будет сортировать список.

Функция SORT запрашивает четыре аргумента:

Диапазон для сортировки
По какому столбцу сортировать диапазон
В каком порядке сортировать диапазон (по возрастанию или по убыванию)
Сортировать строки или столбцы.

=SORT(массив, индекс сортировки, порядок сортировки, по столбцу)

Это фантастика. И его можно использовать с предыдущим примером UNIQUE для сортировки названий продуктов по порядку.

Для этого нам нужно только указать диапазон для сортировки.

12. ФИЛЬТР

После функции СОРТИРОВКИ есть функция фильтрации списка.Еще одна функция, доступная только пользователям Microsoft 365.

Эта функция отфильтрует диапазон. Это чрезвычайно мощная функция, которая идеально подходит для анализа данных и создания отчетов.

Функция ФИЛЬТР принимает три аргумента:

Диапазон для фильтрации
Критерии, определяющие, какие результаты следует возвращать
Что делать, если результаты не возвращаются.

=FILTER(массив, включить, если пусто)

В этом примере возвращаются только результаты для темы, введенной в ячейку F2.

Подведение итогов

Изучение наиболее полезных функций Excel для анализа данных, упомянутых в этой статье, значительно упростит анализ данных Excel.

Но есть еще много других функций, а также функций Excel, которые помогут вам стать настоящим мастером анализа данных.

Двумя другими важными инструментами Excel, которые необходимо освоить, являются Power Query и Power Pivot.

Power Query упрощает импорт и преобразование данных для анализа. А Power Pivot — идеальный инструмент, если вы анализируете большие объемы данных. Он может хранить огромные объемы данных вне Excel и имеет собственный язык формул DAX.

Пройдите курсы Power Query и Power Pivot на сайте GoSkills, чтобы быстро отточить свои навыки анализа данных в Excel уже сегодня.

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

Это процесс сбора, анализа и интерпретации информации с использованием различных инструментов анализа данных для извлечения ценности и создания привлекательных визуализаций для поддержки принятия решений.

Excel исключительно хорошо справляется с анализом структурированных данных (чисел, оценок, групп и т. д.).

В этой статье мы сосредоточимся на том, как анализировать данные в Excel с помощью различных функций и надстроек Excel, а затем познакомим вас с простым способом анализа неструктурированных данных в Excel.

Как выполнять анализ данных в Excel

Компании собирают как количественные, так и качественные данные и часто экспортируют эти данные в Excel, готовые для анализа. Excel – один из самых популярных и надежных инструментов для управления бизнес-данными и их анализа, а также для выявления тенденций в больших наборах данных.

Это также самое простое место для начала анализа данных, поэтому давайте рассмотрим некоторые из основных функций Excel ниже:

Отфильтруйте данные

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

Например, в этом наборе отзывов о товарах данные в столбце "Размер компании" отфильтрованы, чтобы отображались только отзывы от малых предприятий.

Ваш браузер не поддерживает тег видео.

Функции

Excel имеет более 400 функций, которые представляют собой предопределенные формулы, помогающие автоматизировать вычисления. Некоторые из них особенно полезны для анализа данных, например:

СОЕДИНИТЬ: объединяет две или более ячеек.
СЧЕТЕСЛИ: подсчитывает количество ячеек, соответствующих определенным критериям.
СРЕДНЕСЛИМН: вычисляет среднее значение ячеек на основе нескольких критериев.

Ниже формула СЧЁТЕСЛИ(I2:I190,"Положительно") используется для автоматического подсчета того, сколько мнений из набора отзывов о продуктах были классифицированы как положительные:

Ваш браузер не поддерживает тег видео.

Сводные таблицы

Сводные таблицы — это одна из самых мощных функций Excel для анализа данных. Сводные таблицы, также известные как кросс-таблицы, используются для суммирования (или разделения) данных, чтобы вы могли сосредоточиться на конкретных аспектах, которые хотите изучить более подробно.

Вы можете быстро создать сводную таблицу, перетаскивая поля и изменяя их порядок без необходимости писать какие-либо формулы. Гибкость в объединении информации также позволяет легко выявлять тенденции, которые в противном случае могли бы остаться незамеченными.

Прежде чем создавать сводную таблицу, определитесь с конечным результатом: что вы пытаетесь понять? Может быть, вы хотите узнать, какой продукт был более популярен в летний сезон, или определить сегменты клиентов на основе покупательского поведения. Имея в голове четкий вопрос, вы лучше поймете, какие данные нужно включить.

В приведенном ниже примере обзоры продуктов, ранее классифицированные по темам и настроениям с помощью MonkeyLearn (мы объясним это позже), сведены в сводную таблицу, показывающую количество положительных, отрицательных и нейтральных мнений по каждому аспекту бизнеса ( Простота использования, производительность, функции и многое другое):

Ваш браузер не поддерживает тег видео.

В приведенной выше итоговой сводной таблице данные о тональности были объединены в столбцы, а данные по темам — в строки, и Excel автоматически подсчитал итоги.

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

Условное форматирование в Excel автоматически выделяет ячейки, используя цвета на основе предопределенных правил, что упрощает идентификацию различных данных. Например, вы можете использовать это правило, чтобы добавить цветовой градиент к показателям NPS, чтобы легко идентифицировать сторонников, пассивных и недоброжелателей.

В приведенном ниже примере показано, как создать правило условного форматирования в наборе данных показателей NPS: "Если значение больше 8, закрасить ячейку зеленым цветом".

Ваш браузер не поддерживает тег видео.

Для более подробных результатов вы можете комбинировать это с другими функциями, такими как спарклайны, которые представляют собой крошечные линейные диаграммы, которые отображают ваши данные из ряда значений. Они полезны для анализа и выявления тенденций в данных, связанных с эффективностью, таких как поддержка клиентов и сезонные колебания, или для выделения высоких или низких значений.

Анализ «что, если»

Анализ "что, если" позволяет создавать различные сценарии и легко сравнивать результаты. Изменяя значения в ячейках, вы можете увидеть, каким будет результат.

Еще одной полезной функцией этого инструмента является средство поиска целей Excel, которое помогает понять, как достичь желаемой цели (например, цели по доходу).

Короче говоря, эти инструменты экономят ваше время и усилия, когда у вас нет всех доступных данных для ответа на определенный вопрос.

Диаграммы

Визуализация данных с помощью диаграмм в Excel помогает эффективно доносить идеи. Доступны различные типы диаграмм, например гистограммы (идеально подходят для сравнительного анализа данных), круговые диаграммы (идеальны для отображения пропорциональных данных) и линейные диаграммы (идеально подходят для анализа тенденций за определенный период времени).

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

Выполнение комплексного анализа данных с помощью пакета инструментов анализа

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

С помощью пакета инструментов анализа вам будет проще обнаруживать выбросы в данных (путем расчета дисперсии), а также вычислять корреляцию и регрессию.

Чтобы начать использовать надстройку для анализа данных Excel, сначала необходимо настроить пакет инструментов анализа:

В Excel нажмите Параметры на вкладке Файл.
Перейдите в раздел Надстройки, затем выберите Пакет инструментов анализа и нажмите Перейти.
Во всплывающем окне установите флажок Пакет инструментов анализа и нажмите ОК.
На вкладке Данные на панели инструментов листа Excel теперь вы увидите параметр Анализ данных
Нажмите на нее и выберите анализ, который хотите выполнить (корреляция, описательная статистика, t-критерий и т. д.), затем нажмите ОК

Чтобы узнать больше о каждом из инструментов, доступных в пакете инструментов анализа, перейдите на сайт службы поддержки Майкрософт, где объясняется, когда и зачем использовать каждый тип анализа данных.

Поднимите анализ данных в Excel на новый уровень с помощью искусственного интеллекта без кода

Несомненно, Excel — отличная отправная точка для анализа данных, особенно при обработке чисел. Тем не менее, все больше и больше компаний начинают видеть преимущества неструктурированных данных, таких как открытые ответы, комментарии в социальных сетях, обзоры и т. д., поскольку они содержат много ценных сведений, которые помогут вам понять «почему» за цифрами.< /p>

Традиционное программное обеспечение для анализа данных не способно обрабатывать строки текста для извлечения смысла. Хотя вы можете использовать Excel для сбора открытых ответов из вашего последнего опроса NPS, вы не можете использовать его для автоматического понимания того, о чем они или что думают клиенты.

К счастью, теперь вы можете использовать инструменты машинного обучения для анализа данных Excel, что позволяет извлекать пользу из огромных объемов текстовых данных в режиме реального времени.

Инструменты машинного обучения, такие как MonkeyLearn, могут автоматически классифицировать текст или извлекать определенную информацию из текстовых данных. Для начала вы можете использовать экстрактор ключевых слов, чтобы быстро получить информацию, например, самые частые и релевантные слова, которые клиенты используют в наборе обзоров продуктов.

Читайте также: