Как анализировать данные в Microsoft Excel
3 августа 2023 г.Если вы работаете с импортированными или чужими данными в Microsoft Excel, скорее всего, вам придется немного очистить данные, прежде чем вы сможете их использовать. Возможно, вам придется удалить пробелы в начале или конце значений или переформатировать столбец значений из текста в числа или наоборот. Также часто анализируются значения, когда в столбце хранится более одного значения.
ПОСМОТРЕТЬ: Изучите эти советы по Excel, которые должен знать каждый.
Хорошим примером этого являются имена. Вы часто найдете полное имя в одном столбце, что затрудняет работу с этими значениями. В этом руководстве я покажу вам два быстрых способа анализа данных с помощью функций Excel Flash Fill и Power Query.
Перейти к:
- Что вам понадобится
Как анализировать с помощью строковых функций Microsoft Excel
Как использовать Flash Fill для анализа данных в Microsoft Excel
Как использовать Power Query для анализа данных в Microsoft Excel
Что вам понадобится
Я использую Microsoft 365 в 64-разрядной системе Windows 11. Вы можете использовать строковые функции в более старых версиях, включая формат меню .xls. Flash Fill доступен в Excel 2013 и более поздних версиях, а Power Query доступен в Excel 2010 и более поздних версиях. Кроме того, Excel для Интернета поддерживает мгновенное заполнение. Вы также можете открывать существующие запросы Power Query в Excel для Интернета, но не можете создавать их в версии для браузера.
Если вы хотите взглянуть на окончательную версию, загрузите демо-файлы.
Как анализировать с помощью строковых функций Microsoft Excel
До Flash Fill в большинстве случаев синтаксический анализ требовал объединения строковых функций, таких как RIGHT(), LEFT(), MID(), FIND() и LEN(). Например, выражение в столбце D на рисунке A.
=ЕСЛИОШИБКА(ПРАВО(B3,ДЛСТР(B3)-ЕСЛИОШИБКА(НАЙТИ(" ",B3,НАЙТИ(" ",B3)+1),НАЙТИ(" ",B3))),B3)
Рисунок А
Приведенное выше выражение возвращает фамилию из значений в столбце B. ЕСЛИОШИБКА() обрабатывает ошибку при отсутствии второго пробела. К счастью, наши значения имен согласуются по формату, но так будет не всегда. Даже если ЕСЛИОШИБКА() удалена, это все равно сложное и утомительное выражение.
Flash Fill может выполнять многие задачи синтаксического анализа в более поздних версиях Microsoft Excel.
Как использовать Flash Fill для анализа данных в Microsoft Excel
Функция Flash Fill в Excel существует с Excel 2013, поэтому вы, возможно, знакомы с ней. При использовании его для синтаксического анализа введите первое значение в целевой столбец, чтобы Excel мог оценить шаблон. Иногда для быстрой заливки требуется только одно значение. Поскольку наши данные непротиворечивы, эта задача синтаксического анализа будет легкой.
Давайте используем Flash Fill для анализа фамилий из значений имен в столбце B:
1. Введите Harkins в C3.
2. Нажмите «Ввод». Скорее всего, Flash Fill еще не заполнит столбец.
3. Если Flash Fill не определяет шаблон, введите первые несколько символов в Smyth, как показано на рисунке B. Как видите, Flash Fill теперь имеет шаблон и предлагает заполнить весь столбец.
Рисунок Б
4. Нажмите Enter, чтобы заполнить оставшиеся ячейки, как показано на рисунке C. Если вы используете более старую версию Excel, вам может потребоваться нажать Ctrl + Enter.
Рисунок С
Мгновенное заполнение Excel намного быстрее и проще, чем объединение строковых функций. Даже когда данные не согласуются, эта функция хорошо находит закономерность после нескольких попыток. Excel для Интернета поддерживает мгновенное заполнение. Найдите его на вкладке «Данные». Вместо нажатия Enter нажмите «Быстрая заливка» в группе «Инструменты данных».
ПОСМОТРЕТЬ: узнайте о 6 способах сэкономить время с помощью Flash Fill в Microsoft Excel.
Как использовать Power Query для анализа данных в Microsoft Excel
Power Query доступен для пользователей Microsoft Excel 2010 и более поздних версий. Его цель — извлекать и очищать данные, но он полон функций, которые могут делать гораздо больше. Используя Power Query, мы добавим столбец и проанализируем фамилии в этом столбце.
Во-первых, нам нужно загрузить данные в Power Query следующим образом:
1. Щелкните в любом месте таблицы Excel, которую вы хотите загрузить в Power Query.
2. Щелкните вкладку Данные.
3. В группе «Получить и преобразовать данные» щелкните «Из таблицы/диапазона». Если вы не отформатировали данные в виде таблицы, Power Query предложит вам сделать это. Демонстрационная таблица называется TablePersonnel (рис. D). Вам не нужно давать имя таблице, но с именованной таблицей немного проще работать.
Рисунок D
Как только данные появятся в Power Query, добавьте столбец на основе столбца «Персонал» следующим образом:
1. Щелкните заголовок «Персонал», чтобы выбрать столбец, затем щелкните вкладку «Добавить столбец».
2. В группе «Общие» выберите «Из выбора» в раскрывающемся списке «Столбец из примеров» (рис. E). Power Query добавляет новый пустой столбец. Чтобы ввести новые значения фамилии для этого столбца, мы будем использовать собственную версию Power Query Flash Fill.
Рисунок Е
3. В первую ячейку нового столбца введите Harkins (рис. F). В ответ Power Query предлагает набор значений фамилий для всего столбца. Просмотрите их немного: если они неверны, введите следующую фамилию. Однако Power Query возвращает правильный список только с одним именем, чтобы задать шаблон для наших данных.
Рисунок F
4. Нажмите OK, чтобы создать и заполнить новый столбец фамилиями, как показано на рисунке G.
Рисунок G
Все, что осталось сделать, это сохранить измененные данные в Microsoft Excel следующим образом:
5. Щелкните вкладку Главная.
6. В группе «Закрыть» нажмите «Закрыть и загрузить» в раскрывающемся списке «Закрыть и загрузить».
На рисунке H показаны проанализированные фамилии на листе Excel, отформатированном как объект Table. Power Query использует имя исходной таблицы TablePersonnel для присвоения имени новому листу. На этом этапе вы можете переименовать новый столбец. Вы также можете переименовать столбец в Power Query перед сохранением данных.
Рисунок Н
Flash Fill был определенно быстрее, но знание метода Power Query поможет, когда вы уже работаете с данными в Power Query.
Читать дальше: узнайте, как применять аналитические данные в Excel.
Оригинал