Перенос данных из одной таблицы в другую в excel: Как в excel перенести данные с одной таблицы в другую

Главная > Фильтры > Расширенный

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

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

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

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

Вот шаги для настройки ваших данных и создания расширенного Фильтр в Microsoft Excel.

— 1) Настроить базу данных

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

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

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

1. Настроить базу данных

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

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

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

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

ПРЕДУПРЕЖДЕНИЕ . Когда вы используете текстовые критерии для расширенного фильтра, Excel интерпретирует его как «начинается с». Если вам нужно точное совпадение текстовых значений, см. приведенный ниже пример точного совпадения.

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

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

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

• <    меньше
• <= меньше или равно
• >= больше или равно
• <> не равно

3.

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

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

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

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

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

ПРИМЕЧАНИЕ . Если вы хотите отправить данные на другой лист , выполните действия, описанные в разделе «Отправка данных на другой лист» ниже

Фильтр уникальных записей

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

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

ПРИМЕЧАНИЕ . В Excel также есть инструмент «Удалить дубликаты», и видео в следующем разделе показывает, как работает этот инструмент.

Фильтр уникальных записей

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

• получить список клиентов из списка заказов
• составить список реализуемой продукции.

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

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

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

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

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

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

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

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

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

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

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

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

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

Временная шкала видео

• 0:00 Введение
• 0:11 Заказы на продажу
• 0:35 Лучшие заказы
• 1:00 Извлечение и критерии
• 1:44 Расширенный фильтр
• 3:44 Проверьте данные
• 4:16 Предупреждения
• 4:52 Получить файл Excel

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

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

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

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

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

— 1) Местоположение диапазона критериев

— 2) И против ИЛИ

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

1) Местоположение диапазона критериев

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

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

ПРИМЕЧАНИЯ:

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

2) И против ИЛИ

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

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

И Правило

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

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

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

ИЛИ Правила

В этом примере есть 3 строки с правилами критериев, по одному критерию в каждой строке.

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

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

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

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

Расширенный фильтр применяет следующую логику И/ИЛИ:

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

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

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

— а) Подстановочный знак * (звездочка)

— б) ? подстановочный знак (вопросительный знак)

— c) Подстановочный знак ~ (тильда)

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

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

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

В этом примере подстановочный знак Заказчика: *o*r

Два имени клиента, Fo o dMa r t и Mart- o — r ama содержат «o», за которым следует «r» в любом месте после «o». Записи для этих двух клиентов отображаются в отфильтрованном списке.

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

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

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

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

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

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

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

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

Ниже показаны еще два примера критериев расширенного фильтра:

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

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

Другие примеры см. на следующей странице

— Дополнительные примеры критериев фильтрации

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

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

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

• больше или равно 500
  • И
• меньше или равно до 750

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

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

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

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

• =»=лед»

Первый знак равенства запускает формулу ячейки, а второй знак равенства устанавливает критерий «равно» текстовому значению.

Дополнительные примеры критериев фильтра

На странице «Расширенные фильтры — сложные критерии» есть еще много примеров критериев.

Например, на этой странице вы можете увидеть эти примеры критериев и многое другое:

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

Сумма счета не соответствует сумме платежа

В этом коротком видеоролике показан пример критерия «несопоставленные данные». Письменные инструкции на странице Advanced Filters — Complex Criteria вместе со многими другими примерами критериев Advanced Filter и файлами для загрузки.

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

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

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

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

Примечание

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

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

Использование макросов расширенного фильтра

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

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

— 1) Кнопки макросов расширенного фильтра

— 2) Расширенный формат фильтра и формулы

После того, как вы быстро просмотрите эти примеры и будете готовы попробовать макросы, перейдите к примерам файлов на странице «Макросы расширенного фильтра» и узнайте, как приступить к работе. Эта страница показывает вам:

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

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

1) Кнопки макросов расширенного фильтра

Например, вот снимок экрана из одного из примеров файлов макросов расширенного фильтра на странице «Макросы расширенного фильтра».

Образец файла с именем Отправить список на лист или в файл (конкретные настройки) , имеет кнопки на рабочем листе, поэтому вам легко тестировать макросы.

Просто нажмите одну из кнопок рабочего листа, чтобы запустить макрос, который:

• Создать отдельный лист с записями для каждого торгового представителя
• Или создайте отдельную книгу Excel с записями для каждого торгового представителя

Использование макросов расширенного фильтра

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

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

— 1) Кнопки макросов расширенного фильтра

— 2) Расширенный формат фильтра и формулы

После того, как вы быстро просмотрите эти примеры и будете готовы попробовать макросы, перейдите к примерам файлов на странице «Макросы расширенного фильтра» и узнайте, как приступить к работе. Эта страница показывает вам:

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

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

1) Кнопки макросов расширенного фильтра

Например, вот снимок экрана из одного из примеров файлов макросов расширенного фильтра на странице «Макросы расширенного фильтра».

Образец файла с именем Отправить список на лист или в файл (конкретные настройки) , имеет кнопки на рабочем листе, поэтому вам легко тестировать макросы.

Просто нажмите одну из кнопок рабочего листа, чтобы запустить макрос, который:

• Создать отдельный лист с записями для каждого торгового представителя
• Или создайте отдельную книгу Excel с записями для каждого торгового представителя

2) Расширенный формат фильтра и формулы

В другом примере файлов для расширенных макросов фильтров с именем Filter & Copy Formatting есть код VBA, который копирует форматирование и формулы из исходных данных.