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

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

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

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

Нередко размеры таких массивов данных достигают десятков (а то и сотен) тысяч строк.

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

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

Совершенно очевидно, что обрабатывать большой массив данных для составления небольшого отчёта — занятие долгое и утомительное.

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

1. Пользователь при помощи автофильтра выбирает в хранилище нужные ему данные;

2. Затем он вручную копирует эти данные и вставляет их в другой файл;

3. А уже в другом файле составляет нужный ему отчёт.

Естественно, такой процесс составления отчётов отнимает очень много времени, сил и требует постоянной высокой концентрации внимания.

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

Чтобы справиться с этой задачей — мы с вами будем использовать запросы.

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

Более того, запросы позволяют «вытаскивать» данные из других файлов Excel, руководствуясь определёнными параметрами — то есть могут вытаскивать не все данные, а только те, которые нужны в текущем файле.

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

Запросы — это очень мощный инструмент, который способен существенно снизить трудоёмкость работы и сильно облегчить жизнь.

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

Да. Ещё одна важная деталь, про которую я забыл упомянуть в своём видео.

При формировании запроса у вас должна появиться ссылка на лист исходных данных вида «Исходные данные$» (1). Если у вас эта ссылка не появится — нажмите на кнопку «Параметры» (2) и в появившемся окне поставьте галочку напротив пункта «Системные таблицы» (3).

После этого ссылка вида «Исходные данные$» должна появиться.

Хорошо. Со всеми предварительными запросами разобрались. А теперь давайте смотреть видео.

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

В моей практике был случай, когда в одной компании каждый месяц информационная система выгружала данные в Excel в таблицу размером примерно 200 столбцов на 25 000 строк.

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

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

Только представьте себе – практически 20 дней в месяц трое экономистов терзали и истязали одну несчастную таблицу.

Составление запросов заняло у нас 2 дня, и после этого весь объём работ по составлению всех нужных отчётов стал проделывать 1 экономист за 3-4 дня.

Что было: 3 экономиста х 20 дней х 8 часов = 480 человеко-часов.

Что стало: 1 экономист х 4 дня х 8 часов = 32 человеко-часа.

Сокращение трудозатрат в 15 раз. Пример, конечно, очень показательный, но далеко не единственный в моей практике.

Если вы хотите добиться у себя на работе таких же результатов – предлагаю вам пройти мой видеокурс « Бюджетирование и управленческий учёт в Excel ».

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

На нашем тренинге уже успели обучиться более 2 600 человек .

Мы уже провели более 140 тренингов 15 городах России – от Питера до Владивостока и везде получали самые высокие оценки нашей работы от участников.

Обычно после тренинга многие участники говорят: «Мы даже не думали, что в Excel можно делать такие удивительные вещи!». Впрочем, лучше сами посмотрите, что говорят участники:

А если у вас остались вопросы или нужна дополнительная информация — звоните по телефону 8 800 100-93-44 (звонок из любого города России с любого телефона бесплатный).

Вы занимаетесь организацией тренингов и хотели бы провести этот трениг у себя?
Скачать файл для организации тренинга здесь >>

Телефон: 8 800 201-49-35 с 9:00 до 16:00 по Московскому времени

Политика конфиденциальности

Настоящая политика конфиденциальности распространяются на всех посетителей и пользователей сайта. Пользователю сайта необходимо внимательно ознакомиться с настоящими Условиями.

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

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

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

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

Выпадающий список в EXCEL. Примеры и описание

Избавиться от пустых строк и учесть новые элементы перечня позволяет Динамический диапазон . Для этого при создании Имени Список_элементов в поле Диапазон необходимо записать формулу = СМЕЩ(Список!$A200;;;СЧЁТЗ(Список!$A:$A))

Используем именованный диапазон Создадим Именованный диапазон Список_элементов, содержащий перечень элементов выпадающего списка (ячейки A 1: A 4 на листе Список ) . Для этого:

Ячейки связи между листами и книгами в Excel — zanz

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

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

Зависимый выпадающий список в Excel и Google таблицах · BIRDYX

• mxl — специально созданный для возможности сохранять сразу в формате таблицы, даже сбрасывать в базы на других компьютерах;
• xls — стандартный тип;
• txt — текстовый, вся информация в блокноте;
• pdf — подойдет, если планируется работать с разновидностями Adobe Reader;
• html — веб-формат, используется для размещения на странице в интернете.

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

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

Как вставить файл в книгу Excel?.

• В исходной вкладке с данными (Лист1), выделите ячейки, на которые вы хотите сослаться.
• Скопируйте ячейки (Ctrl/команда + С, или щелкните правой кнопкой мыши и выберите копия).
• Перейти на другую вкладку (Sheet2) и нажмите на ячейку (или ячейки), где вы хотите разместить ссылки.

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

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

Как выгружать таблицы из 1С в Excel: выгрузка с 1C в Эксель, скопировать и перенести данные, документы, отчеты, файлы

Альтернативным способом ссылки на перечень элементов, расположенных на другом листе, является использование функции ДВССЫЛ() . На листе Пример , выделяем диапазон ячеек, которые будут содержать выпадающий список, вызываем Проверку данных , в Источнике указываем =ДВССЫЛ(«список!A1:A4») .

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

Почему в excel не работает выпадающий список

• в книге Источник.xlsx создайте необходимый перечень элементов;
• в книге Источник.xlsx диапазону ячеек содержащему перечень элементов присвойте Имя , например СписокВнеш;
• откройте книгу, в которой предполагается разместить ячейки с выпадающим списком;
• выделите нужный диапазон ячеек, вызовите инструмент Проверка данных, в поле Источник укажите = ДВССЫЛ(«[Источник.xlsx]лист1!СписокВнеш») ;

Стоить отметить, что инструментом внедрения пользуются достаточно редко, поэтому нужную вкладку не так-то просто найти.
Переходим во вкладку Вставка -> Текст -> Объект:

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

На одном листе располагается несколько таблиц

1. Создаем стандартный список с помощью инструмента «Проверка данных». Добавляем в исходный код листа готовый макрос. Как это делать, описано выше. С его помощью справа от выпадающего списка будут добавляться выбранные значения.
2. Чтобы выбранные значения показывались снизу, вставляем другой код обработчика.
3. Чтобы выбираемые значения отображались в одной ячейке, разделенные любым знаком препинания, применим такой модуль.

Дело в том, что данный формат содержит 2 измерения: чтобы найти что-то в таблице, вы должны определиться со строкой, перебирая филиал, группу и агента. Когда вы найдёте нужную стоку, то потом придётся искать уже нужный столбец, так как их тут много. И эта «двухмерность» сильно усложняет работу с такой таблицей и для стандартных инструментов Excel — формул и сводных таблиц.