Excel, как извлечь данные из Excel

Обновлено: 21.11.2024

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

Что такое извлечение данных?

Что такое извлечение данных? Извлечение данных — это именно то, на что это похоже — отбраковка вашей электронной таблицы для извлечения определенных данных для нового столбца или электронной таблицы, следуя стандартным процессам ввода и очистки данных. Например, если у вас есть набор данных о плотности населения, вы можете выделить города с населением более 10 000 человек на квадратную милю и только те, которые находятся в Калифорнии, чтобы можно было проанализировать демографический состав этих городов. Конечно, если вы работаете с небольшим набором данных, вы можете обойтись копированием/вставкой, но извлечение данных вручную может быстро стать непосильным. Извлечение данных наиболее эффективно, когда вы можете определить, какие данные вам нужны, но использовать для их извлечения эффективное программное обеспечение машинного обучения.

Неудивительно, что многие компании используют Excel в качестве инструмента для извлечения данных. Чтобы извлечь данные из столбцов Excel, вы можете использовать комбинацию функций ВПР, ПОИСКПОЗ и ИНДЕКС. Функция ВПР, возможно, лучше всего подходит для извлечения данных, позволяя вам искать и извлекать данные из определенного столбца. Функции ПОИСКПОЗ и ИНДЕКС работают несколько наоборот; ПОИСКПОЗ находит позицию заданного значения, а ИНДЕКС извлекает значение из известной позиции. Все три полезно знать (и относительно легко освоить), чтобы извлекать данные из Excel. Но является ли Excel лучшей системой для извлечения данных в современных условиях?

Проблемы с извлечением данных

Для простых структурированных данных извлечение данных в Excel довольно просто. Однако по мере роста масштабов и сложности современных данных извлечение данных в Excel становится все более сложной задачей для пользователей. Обширные, сложные и неструктурированные данные могут сделать извлечение данных трудоемким и сложным. Скажем, например, вы хотите извлечь и проанализировать определенные слова из серии твитов. Это означает, что вы работаете с данными JSON, и для того, чтобы извлечь данные из Excel, вам сначала нужно разложить их, а затем выполнить шаги, связанные с извлечением данных. Excel не был создан для данных JSON или большинства сложных типов данных и систем, а это означает, что извлечение данных становится не только более сложным процессом, но и значительно более длительным. Компании обращаются к инструментам извлечения данных для решения многих из этих проблем. Когда бизнесу требуется несколько инструментов для каждой части процесса подготовки данных, большинству управленческих команд требуется другое решение. Но есть ли другие решения?

Новые платформы для извлечения данных

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

Извлечение данных в Trifacta

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

Помимо инструментов извлечения данных, Trifacta может многое предложить, в том числе бесчисленные возможности подготовки данных и уникальный управляемый опыт, который сокращает общее время подготовки данных до 90%. Наш инструмент выполняет больше, чем просто извлечение данных, и может сэкономить драгоценное время и деньги. Мы хотели бы поговорить с вами о ваших потребностях в подготовке и интеграции данных, а также о том, как вы можете выполнять извлечение данных в Trifacta. Свяжитесь с нами для получения дополнительной информации. Или попробуйте сами, подписавшись на бесплатную пробную версию Trifacta здесь.

Если вам нужно найти и извлечь столбец данных из одной таблицы и поместить его в другую, используйте функцию ВПР.Эта функция работает в любой версии Excel в Windows и Mac, а также в Google Sheets. Он позволяет находить данные в одной таблице по некоторому идентификатору, который является общим с другой таблицей. Две таблицы могут находиться на разных листах или даже в разных книгах. Существует также функция HLOOKUP, которая делает то же самое, но с данными, расположенными горизонтально по строкам.

Функции ПОИСКПОЗ и ИНДЕКС удобно использовать, когда вас интересует расположение конкретных данных, например столбца или строки, содержащих имя человека.

Премиум-варианты

Прежде чем мы перейдем к функциям Excel, знаете ли вы, что на Envato Market есть ряд скриптов и плагинов Excel, которые позволяют выполнять расширенные функции?

Скрипты и плагины Excel на Envato Market

Скринкаст

Если вы хотите следовать этому руководству, используя свой собственный файл Excel, вы можете это сделать. Или, если хотите, загрузите ZIP-файл, прилагаемый к этому руководству, который содержит образец рабочей книги с именем vlookup example.xlsx.

Использование функции ВПР

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

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

Синтаксис

Синтаксис функции ВПР:

=ВПР(искомое значение, диапазон таблицы, номер столбца, [истина/ложь])

Вот что означают эти аргументы:

  • Искомое значение. Ячейка с уникальным идентификатором.
  • Диапазон таблицы. Диапазон ячеек с идентификатором в первом столбце, за которым следуют остальные данные в других столбцах.
  • Номер столбца. Номер столбца, содержащего искомые данные. Не путайте это с буквой столбца. На приведенном выше рисунке состояния находятся в столбце 4.
  • Верно/неверно. Этот аргумент является необязательным. True означает, что приемлемо приблизительное соответствие, а False означает, что приемлемо только точное соответствие.

Мы хотим найти суммы продаж из таблицы на иллюстрации выше, поэтому мы используем следующие аргументы:

Синтаксис функции ВПР

Определить имя диапазона для создания абсолютной ссылки

В файле Vlookup example.xlsx просмотрите таблицу "Объемы продаж". Мы введем формулу в ячейке B5, а затем воспользуемся функцией автозаполнения, чтобы скопировать формулу вниз по листу. Это означает, что диапазон таблицы в формуле должен быть абсолютной ссылкой. Хороший способ сделать это — определить имя для диапазона таблицы.

Определение имени диапазона в Excel

Определение имени диапазона в Google Таблицах

В Google Таблицах определение имени немного отличается.

Ввод формулы

Чтобы ввести формулу, перейдите на лист "Объемы продаж" и нажмите кнопку B5.

Введите формулу:

Нажмите Enter.

Вход в функцию ВПР

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

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

Дважды щелкните, чтобы заполнить значения в столбце.

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

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

Использование ПОИСКПОЗ

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

Создать расширенный фильтр Excel

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

Создать расширенный фильтр Excel

Вот шаги по настройке данных и созданию расширенного фильтра.

1. Настройте базу данных

  1. Первая строка (A1:D1) содержит заголовки. Каждый столбец должен иметь уникальный заголовок. Повторяющиеся заголовки вызовут проблемы при использовании расширенного фильтра.
  2. Последующие строки содержат данные.
  3. В базе данных нет пустых строк.
  4. В конце базы данных есть пустая строка, а справа пустой столбец.

2. Настройте диапазон критериев (необязательно)

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

ВНИМАНИЕ! Когда вы используете текстовые критерии, Excel интерпретирует их как "начинается с". Если вам нужно точное совпадение, см. пример точного совпадения ниже.

  1. В этом примере ячейки F1:F2 являются диапазоном критериев.
  2. Заголовок в F1 точно соответствует заголовку (D1) в базе данных.
  3. Ячейка F2 содержит критерий. Используется оператор > (больше) с числом 500 (без знака $).

После применения расширенного фильтра Excel заказы на общую сумму более 500 долларов США останутся видимыми.

Другие операторы включают:

3. Настройте диапазон извлечения (необязательно)

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

ПРИМЕЧАНИЕ. Если вы хотите извлечь ВСЕ столбцы, вы можете оставить диапазон извлечения пустым для расширенного фильтра Excel.

  1. Выберите ячейку в левом верхнем углу диапазона для извлеченных данных.
  2. Введите заголовки для столбцов, которые вы хотите извлечь.
    1. Они должны точно совпадать с заголовками столбцов в данных по орфографии и пунктуации.
    2. Порядок столбцов может быть другим
    3. Можно включить любой или все столбцы.

    4. Применить расширенный фильтр Excel

    Выполните следующие действия, чтобы применить расширенный фильтр

    1. Выберите ячейку в базе данных.
    2. На вкладке "Данные" ленты Excel нажмите "Дополнительно", чтобы открыть диалоговое окно "Расширенный фильтр"

    1. Вы можете отфильтровать список на месте или скопировать результаты в другое место.
    2. Excel должен автоматически определить диапазон списка. Если нет, вы можете выбрать ячейки на листе.
    3. Выберите диапазон критериев на листе.
    4. Если вы копируете в новое место, выберите начальную ячейку для копии
      • Примечание. При копировании в другое место все ячейки ниже диапазона извлечения будут очищены при применении расширенного фильтра.
    5. Нажмите "ОК", чтобы закрыть диалоговое окно "Расширенный фильтр Excel".

    Фильтровать уникальные записи

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

    Письменные инструкции по этому методу находятся под видео.

    Фильтровать уникальные записи

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

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

    1. Выберите ячейку в базе данных.
    2. На вкладке "Данные" ленты Excel нажмите "Дополнительно".
    3. В диалоговом окне "Расширенный фильтр" выберите "Копировать в другое место".
    4. Для диапазона списка выберите столбцы, из которых вы хотите извлечь уникальные значения.
    5. Оставьте диапазон критериев пустым.
    6. Выберите начальную ячейку для копирования в местоположение.
    7. Добавьте галочку в поле Только уникальные записи.
    8. Нажмите "ОК".

    Дубликаты в уникальных записях

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

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

    Чтобы узнать больше об этой проблеме, перейдите на страницу Проблема удаления дубликатов. Вы увидите, почему перечислены дубликаты (точность с плавающей запятой) и как решить проблему с помощью функции ОКРУГЛ.

    Видео: удаление дубликатов

    В Excel 2007 и более поздних версиях есть инструмент «Удалить дубликаты».В отличие от параметра «Только уникальные записи» расширенного фильтра, при этом исходный список не остается без изменений, а полностью удаляются все повторяющиеся элементы из списка. Остается только первый экземпляр каждого элемента.

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

    Извлечение данных на другой лист

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

    Извлечение данных на другой лист

    Если ваши данные Excel находятся на одном листе, вы можете отправить отфильтрованные данные на другой лист, используя расширенный фильтр. В этом примере данные находятся на Листе1 и будут скопированы на Лист2.

    1. Перейдите к Sheet2 (см. шаги на видео выше)
    2. Выберите ячейку в неиспользуемой части листа (в данном примере ячейка C4).
    3. На вкладке "Данные" ленты Excel нажмите "Дополнительно".
    4. Выберите «Копировать в другое место».
    5. Нажмите в поле "Диапазон списка".
    6. Выберите Sheet1 и выберите базу данных.
    7. (необязательно) Щелкните поле диапазона критериев.
    8. Выберите диапазон критериев.
    9. Нажмите в поле Копировать в.
    10. Выберите ячейку на Листе 2, с которой должны начинаться результаты, или выберите заголовки, введенные на Листе 2.
    11. (необязательно) Установите флажок "Только уникальные значения".
    12. Нажмите "ОК".

    Настройка диапазона критериев

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

    ВНИМАНИЕ! При вводе текстового критерия Excel интерпретирует его как "начинается с". Если вам нужно точное совпадение, см. пример точного совпадения ниже.

    Расположение диапазона критериев

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

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

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

    Это не повлияет на расширенный фильтр — вы просто не сможете увидеть некоторые критерии.

    И против ИЛИ

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

    Правило И

    В этом примере есть одна строка правил критериев:

    • имя клиента должно начинаться с "МегаМарт"
    • И название продукта должно начинаться с «Cookies».
    • И общее количество должно быть больше 500

    ИЛИ правила

    В этом примере есть 3 строки с правилами критериев.

    Критерии в разных строках соединяются оператором ИЛИ. Во втором примере --

    • имя клиента должно начинаться с "МегаМарт"
    • ИЛИ название продукта должно начинаться с «Cookies»
    • ИЛИ общее количество должно быть больше 500.

    Правила И/ИЛИ

    Используя несколько строк, вы можете комбинировать операторы И и ИЛИ, как в этом примере:

    • имя клиента должно начинаться с "MegaMart" И название продукта должно начинаться с "Cookies"
      • ИЛИ

      Использование подстановочных знаков в критериях

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

      Подстановочный знак *

      Подстановочный знак звездочка (*) представляет собой любое количество символов в этой позиции, включая ноль символов.

      ПРИМЕЧАНИЕ. Поскольку Excel интерпретирует текстовые критерии как "начинается с", вам не нужно добавлять подстановочный знак * в конце строки

      В этом примере критерий клиента: *o*r

      Два имени клиента, FoodMart и Mart-o-rama, содержат букву "о", за которой следует буква "р" в любом месте после "о". Записи для этих двух клиентов отображаются в отфильтрованном списке.

      ? подстановочный знак

      Вопросительный знак (?) подстановочный знак представляет один символ в этой позиции.

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

      Подстановочный знак ~

      Подстановочный знак тильда (~) позволяет искать символы, которые используются в качестве подстановочных знаков.

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

      Чтобы найти только имя клиента, начинающееся с Good*Eats, используйте тильду (~) перед звездочкой в ​​ячейке критерия: Good~*Eats

      Примеры критериев

      Вот несколько других примеров критериев расширенного фильтра:

      ПРИМЕЧАНИЕ. На этой странице есть дополнительные примеры критериев: Расширенные фильтры -- Комплексные критерии

      Извлечение элементов в диапазоне

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

      Если вы вводите два критерия в одну и ту же строку в диапазоне критериев, вы создаете оператор AND. В этом примере все извлекаемые записи должны быть:

      Извлечение элементов с определенным текстом

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

      Например, если в качестве критерия ввести "Лед", Excel найдет "Лед", "Мороженое" и "Молоко-мороженое", поскольку все они начинаются с "Лед".

      Чтобы извлечь только те записи, которые точно соответствуют тексту критерия, используйте следующий формат в диапазоне критериев (верхний или нижний регистр не повлияют на результаты):
      ="=ice"

      Где должен быть диапазон критериев?

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

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

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

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

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

      Получить образец рабочей тетради

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

      Если есть диапазон данных с некоторыми оценками, именами и регионами, и вы просто хотите узнать значения, которые больше или равны 1 и меньше или равны 3 в столбце Оценка, а затем извлеките соответствующие строки в следующую таблицу, как показано на снимке экрана ниже. Как можно быстро извлечь эти строки, соответствующие критериям, в другое место в Excel, кроме как найти и скопировать их одну за другой?
      Извлечь строки, соответствующие критериям, с помощью функции фильтра
      Извлекайте строки, соответствующие критериям, с помощью Kutools for Excel

      Извлечение строк, соответствующих критериям, с помощью функции фильтра

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

      <р>1. Выберите диапазон, включающий заголовки, из которых вы хотите извлечь строки, нажмите «Данные» > «Фильтр», чтобы добавить значки «Фильтр» рядом с заголовками. Смотрите скриншот:

      <р>2. Затем щелкните значок «Фильтр» рядом со столбцом «Оценка» (столбец, на основе которого вы можете извлекать строки) и выберите «Числовые фильтры» > «Пользовательский фильтр» в контекстном меню. Смотрите скриншот:

      <р>3. В диалоговом окне «Пользовательский автофильтр» выберите критерии из раскрывающихся списков, введите диапазон чисел в текстовые поля и установите флажок И. Смотрите скриншот:

      <р>4. Затем нажмите OK, и диапазон данных будет отфильтрован по заданным вами критериям.

      <р>5. Затем выберите отфильтрованные данные и нажмите Ctrl + G, чтобы открыть диалоговое окно «Перейти к», и нажмите «Специальный», чтобы перейти к диалоговому окну «Перейти к специальному», и установите флажок «Только видимые ячейки». Смотрите скриншот；

      <р>6. Нажмите «ОК», и теперь в диапазоне выделены только видимые ячейки, и нажмите Ctrl + C, чтобы скопировать его, а затем перейдите к выбору ячейки, чтобы нажать Ctrl + V, чтобы вставить видимые ячейки. Смотрите скриншот:

      Извлечение строк, соответствующих критериям, с помощью Kutools for Excel

      После установки Kutools for Excel сделайте следующее: (Бесплатно загрузите Kutools for Excel сейчас!)

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

      <р>1. Выберите столбец, на основе которого вы извлекаете строки, и нажмите Kutools > Выбрать > Выбрать определенные ячейки. Смотрите скриншот:

      <р>2. Затем в диалоговом окне «Выбрать определенные ячейки» установите флажок «Вся строка» в разделе «Тип выбора» и укажите критерии в разделе «Указать тип». Смотрите скриншот:

      <р>3. Нажмите «ОК», чтобы закрыть диалоговое окно, и появится всплывающее диалоговое окно, напоминающее о выборе количества ячеек, просто нажмите «ОК», чтобы закрыть его.

      <р>4. Теперь нажмите Ctrl + C, чтобы скопировать строки, и выберите ячейку, в которую нужно поместить извлеченные строки, и нажмите Ctrl + V, чтобы вставить их.

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