Сегодня мы поговорим о получении данных из закрытых файлов Excel. В одном из предыдущих уроков рассматривалась та же задача, но с применением макроса. Теперь посмотрим как обойтись без него.
Способ №1
Воспользуемся строкой формул, это самый простой и понятный способ действий. Представим картину: есть папка с несколькими книгами Excel; в каждой книге несколько листов, они заполнены данными; эти данные нужно собрать в один общий файл.
Итак, книга «Итог» должна собрать из файла «Магазины» наименование магазина за определённый месяц и из файла «Продажи» сумму продаж за каждый месяц по магазинам.
Взглянем в строчку формул книги «Итого» на листе «январь».
Весь фокус заключается в чём — необходимо путь до папки с файлом заключить в кавычки, имя файла заключить в квадратные скобки, далее через знак восклицания указать адрес ячейки откуда хотим забрать данные.
Точно так же и с именем магазина, который подгружается из файла «Магазины».
Итог: необходимо правильно прописать имена файлов и их пути.
Способ №2
Напомню — если у вас Excel версии 2013 придётся установить этот модуль дополнительно. Если Excel 2016 он уже будет в комплекте.
Переходим на вкладку «Power Query», нажимаем кнопку «Из файла» и выбираем вариант «Из файла Excel».
В появившемся окне выбираем файл из которого нужно выгрузить информацию.
Далее выберем информацию с каких листов нужно загрузить. Щёлкаем галку «Несколько элементов» и отмечаем листы.
Нажимаем кнопку «Загрузить в» и выбираем «Таблица», будет выгружена информация на новые листы. Если хотим всё на один лист — придётся по очереди выгружать каждый лист.
Каждый лист загрузится в таблицу «Итог» и можно будет оперировать данными как угодно.
Наведя курсор в часть запроса, можно будет увидеть подробную статистику по элементу Power Query.
Вот такие два способа получения данных из закрытых файлов Excel. Всем удачи!
Автор записи: Иван
Похожие записи ‘
Поиск даты в ячейке с текстом
Поиск даты в ячейке с текстом Добрый день, уважаемые читатели и подписчики.
Vcf в Excel
Vcf в Excel Добрый день, уважаемые читатели и подписчики блога! Давно не было.
Импорт из Excel в Power Bi
Импорт из Excel в Power Bi Добрый день, уважаемые читатели и подписчики блога.
Power Bi SQL запросы
Power Bi SQL запросы Добрый день, уважаемые читатели и подписчики блога! Сегодня.
Excel сумма по цвету
Excel сумма по цвету Добрый день, уважаемые читатели и подписчики блога.
Добавить комментарий Отменить ответ
Для отправки комментария вам необходимо авторизоваться.
Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.
Как в Excel получить данные из другого файла?
Как искать / находить значения в другой книге?
В этой статье рассказывается о том, как искать значения и возвращать данные из другой книги, а также как находить / искать значения из другой книги. Здесь я подробно расскажу о трех решениях.
Например, вы создаете таблицу «Покупка фруктов» в Excel, и теперь вам нужно просмотреть фрукты из другой книги и вернуть соответствующие цены, как показано на скриншотах ниже. Здесь я расскажу вам, как решить эту проблему с помощью функции VLOOKKUP в Excel.
1. Откройте обе книги, из которых вы будете просматривать значения и возвращать значения.
2. Выберите пустую ячейку, в которую вы вернете цену, введите формулу = ВПР (B2; [Price.xlsx] Sheet1! $ A $ 1: $ B $ 24,2; FALSE) в него, а затем перетащите его маркер заполнения, чтобы применить эту формулу к диапазону по мере необходимости.
Заметки:
(1) В приведенной выше формуле B2 — это плод, который вы будете искать в другой книге, Price.xlsx указывает имя файла книги, из которой вы будете искать, Sheet1 означает имя листа, из которого вы будете искать, и A $ 1 : $ B $ 24 — это диапазон, из которого вы будете искать. Вы можете изменить их по своему усмотрению.
(2) После закрытия книги, из которой вы искали, формула автоматически обновится до = ВПР (B2; ‘W: test [Price.xlsx] Sheet1’! $ A $ 1: $ B $ 24,2, FALSE) , W: test — это путь сохранения книги, из которой вы искали.
Пока все цены вернулись правильно, как показано на скриншоте слева. И эти цены будут обновляться автоматически, если исходная книга, которую вы искали, изменилась.
 |
Формула слишком сложна для запоминания? Сохраните формулу как запись Auto Text для повторного использования одним щелчком мыши в будущем! Подробнее . Бесплатная пробная версия |
Может возникнуть путаница при настройке пути сохранения, имени файла и рабочего листа в функции VLOOKUP. Этот метод позволит легко решить эту проблему с помощью VBA.
1. нажмите другой + F11 клавиши, чтобы открыть окно Microsoft Visual Basic для приложений.
2. Нажмите Вставить > Модуль, а затем вставьте ниже код VBA в открывающееся окно модуля.
VBA: данные Vlookup и возвращаемые значения из другой закрытой книги
Внимание: Этот VBA будет возвращать значения в столбце, который находится на 2 столбца позади выбранного столбца. Например, я выбираю столбец B при применении этого VBA, значения возвращаются в столбце D. Если вам нужно изменить столбец назначения, узнайте код xRg.Offset (0, 2) .Formula = xString & GetColumn (xFCell.Column + 1) & «$» & xFCell.Row и заменить 2 на другой номер по мере необходимости.
3. нажмите F5 ключ или щелкните Выполнить кнопку для запуска этого VBA.
4. В открывшемся диалоговом окне укажите диапазон данных, который вы будете искать, и щелкните значок OK кнопку.
5. Теперь выберите книгу, в которой вы будете искать значения в открывшемся диалоговом окне «Выбор книги», и нажмите кнопку Откройте кнопку.
Теперь все выбранные значения ищутся в указанной закрытой книге, и соответствующие значения возвращаются в указанном столбце. Смотрите скриншот:
Транспортировка данных в Ехсеl
При использовании Microsoft Excel часто возникает необходимость перенести в него данные из специализированной бухгалтерской программы. В таких случаях, конечно, можно сделать распечатку и потом ее отсканировать или просто набрать все данные заново. Но это усложняет процедуру. Гораздо удобнее использовать возможности самого Excel.
Извлекаем полезную информацию В бухгалтерских программах обычно присутствует функция экспорта данных в обычный текстовый файл. А уже оттуда, из файла экспорта, данные можно импортировать в Excel, после чего с ними можно работать в этой программе. Для того чтобы произвести экспорт данных из бухгалтерской программы в текстовый файл, нужно нажать соответствующую кнопку или выбрать нужный пункт в меню. Как эта функция работает конкретно в вашей версии программы, можно узнать из руководства пользователя, которое поставляется изготовителем, или из справочной системы самого программного продукта. В большинстве современных бухгалтерских программ схема действий при выполнении экспорта данных выглядит примерно одинаково. Первым делом нужно отобрать в бухгалтерской программе документы, которые мы хотим перенести в другой файл. После того как сформируем их список, жмем на соответствующую кнопку или выбираем подходящий пункт меню для осуществления экспорта данных. Обычно программа перед началом процедуры переноса данных требует задать имя файла, в который будут сохранены выбранные документы, и указать папку для его сохранения. Результатом экспорта обычно бывает файл с расширением *.txt, содержащий в себе данные из бухгалтерской программы. Встречаются и другие расширения, но значительно реже. Переносим данные в Excel Если у вас нет необходимости переносить данные в Excel постоянно, можно использовать способ непосредственного открытия нужных файлов в среде Excel. Для этого сперва открываем программу Excel и жмем на кнопку открытия файла. В появившемся окошке в строке для типа файла выбираем параметр «Все файлы». А после этого ищем папку с файлом, который содержит данные из бухгалтерской программы, выделяем его курсором и нажимаем кнопку «Открыть». Если вдруг вы не видите нужного документа в папке, где он хранится, то скорее всего вы забыли выбрать пункт «Все файлы», и Excel его просто «не видит». Далее появится окно мастера по импорту данных из файла на лист Excel. В «Мастере текстов» имеются необходимые настройки и параметры, которые позволяют легко добиться хорошего результата. Нужно лишь следовать подсказкам мастера. Обычно импорт файлов производится в три шага. Для удобства пользователя на каждом из них есть окно для предварительного просмотра результатов изменений. Шаг первый. Здесь мы должны задать документу первоначальное форматирование, то есть определить, каким именно образом информация будет размещена по столбцам. Сперва в окне мастера в разделе «Формат файла» нужно выбрать тип используемой кодировки, чтобы текст нормально читался. Если в окне просмотра появляются какие-то непонятные слова или символы, попробуем выбрать для формата файла другой параметр. Потом указываем, с какой строки требуется производить импорт данных. Например, это необходимо, если в первой строке содержится техническое описание столбцов, которое нам не нужно. Когда все готово, нажимаем кнопку «Далее» для перехода к следующему шагу. Шаг второй. На этом этапе нам нужно определить, правильно ли был сделан выбор типа данных. Если в окне, где отображен результат, информация неправильно разбита по столбцам, возвращаемся назад и выбираем другой тип данных. При первом шаге у нас была возможность выбрать для формата данных значение «фиксированной ширины». Если мы установили галочку напротив этого пункта, то на втором шаге потребуется задать границы каждому столбцу. Сделать это можно на линейке в окне «Образец разбора данных». Символ разделителя столбцов пользователь выбирает самостоятельно. Какой именно знак установлен в данный момент, можно увидеть в табличке «Символом-разделителем является:». Если символ выбран неправильно, разбивка по столбцам будет происходить некорректно. Тогда следует подобрать или ввести другой символ-разделитель. Когда все станет нормально, жмем на кнопку «Далее» и переходим к последнему шагу. Шаг третий. В этот момент мы можем сразу исключить из таблицы ненужные столбцы, которые содержат лишнюю информацию. Для каждого из оставшихся столбцов укажем формат данных, который будет учитываться при создании листа в Excel. Будьте внимательны: при изменении формата происходит переформатирование данных, и это может привести к их искажению. Определившись со всеми столбцами, нажимаем на кнопку «Готово». После импорта полученную информацию нужно сразу сохранить. При этом обязательно выберите тип файла «Книга Microsoft Excel». А уже после полученные данные можно отформатировать на свой вкус. Если перенос данных прошел не совсем удачно, расстраиваться не стоит – процедуру импорта можно повторить еще раз. Но в мастере текстов попробуйте подобрать другие настройки и параметры. Регулярно обновляем файл Бывают случаи, когда нужно с некоторой периодичностью импортировать новые данные из специализированной бухгалтерской программы в файл Microsoft Excel, то есть регулярно его обновлять. Процедура такого перемещения очень похожа на вышеописанную. Сперва мы также производим экспорт данных из бухгалтерской программы в файл. А потом в меню программы Excel выбираем пункт «Данные», затем подпункт «Внешние данные», и далее – «Импорт текстового файла. ». В результате на экране появится окно импорта текстового файла, где вместо кнопки «Открыть» будет «Импорт». Выбираем документ, из которого будем брать информацию, и после этого повторяем три вышеописанных шага. Завершаем перенос данных выбором листа, куда следует поместить данные. В начале таблицы, в которую мы импортировали данные из бухгалтерской программы, можно создать новую строчку с описанием столбцов. А слева добавить столбец для нумерации строк. И в последующем они останутся неизменными, а все обновляемые данные будут размещаться за ними. Этот способ имеет свои преимущества. Если данные из бухгалтерской программы нужно постоянно экспортировать в один и тот же файл, то его обновление займет значительно меньше времени. Это возможно благодаря тому, что все шаги мастера не придется каждый раз повторять вручную, программа выполнит их самостоятельно. Обновить данные из бухгалтерской программы в уже имеющемся файле Excel еще проще. Для этого открываем книгу Microsoft Excel, в которой была сохранена информация из текстового файла, и ставим курсор в любую ячейку с перенесенными данными. Потом в меню Excel выбираем пункт «Данные», а затем – «Обновить данные». После этого появится окно для открытия файла, в котором уже указано имя документа, из которого вы брали информацию в прошлый раз. Остается лишь нажать кнопку «Импорт» – и можно приступать к обработке обновленных данных. Когда вы работаете с постоянно обновляемым файлом, не меняйте местами и не добавляйте новые столбцы между уже имеющимися. Это связано с тем, что при последующем импорте данных информация будет размещена в обычном порядке, и в результате данные в таблице будут смещены.
Excel предпочитает краткость Перенося в Excel большие таблицы, можно столкнуться с определенными проблемами. Если общее количество строк в таблице превышает тысячу, то автофильтр будет работать плохо. Дело в том, что этот режим охватывает только первую тысячу строк, а остальные игнорирует. И корректно произвести фильтрацию данных такой длинной таблицы будет невозможно. Еще один «скользкий участок» при использовании функции автофильтра – полностью пустая строка внутри таблицы. В этом случае отфильтрованы будут только строки, расположенные выше пустой. А данные, размещенные после незаполненной строчки, участвовать в режиме фильтра не будут.
Трюк №49. Эффективная вставка данных из другой рабочей книги Excel
Используйте данные, находящиеся в другой рабочей книге, как источник для вашей сводной таблицы. При создании сводной таблицы в Excel можно выбирать разнообразные источники данных. Самый простой и мощный подход — использовать данные из этой же рабочей книги.
К сожалению, по определенным причинам это не всегда возможно. Например, данные, находящиеся в другой рабочей книге, вводятся ежедневно, и пользователи, вводящие данные, не должны видеть сводную таблицу. Применение динамических именованных диапазонов существенно сократит время обновления, необходимое для вашей сводной таблицы. Так как невозможно ссылаться на динамический именованный диапазон из другой рабочей книги, это также означает, что ваша сводная таблица не будет ссылаться на тысячи пустых строк, существенно увеличивая размер файла. Таким образом, можно получить данные из другой рабочей книги и после этого основывать сводную таблицу на данных в этой же рабочей книге, а не использовать внешние ссылки.
В рабочей книге, где будет находиться ваша сводная таблица, вставьте новый рабочий лист и присвойте ему имя Data. Откройте рабочую книгу и рабочий лист с нужными данными. В любой пустой ячейке на этом рабочем листе введите формулу =IF(A1=»»;»»;A1) , в русской версии Excel =ЕСЛИ(А1=»»;»»;А1) , где А1 — первый заголовок таблицы данных.
Выделите ячейку А1. Затем вырежьте ее, активируйте исходную рабочую книгу и вставьте ячейку А1 в ячейку А1 на листе Data. Вы получите ссылку на другую рабочую книгу. Скопируйте эту ячейку в такое количество столбцов, сколько заголовков содержит источник данных. Затем выберите команду Формулы → Имя → Присвоить (Formulas → Name → Define) и в поле Имя (Names in workbook) введите PivotData. В поле Формула (Refers to) введите формулу =OFFSET($А$1;0;0;COUNTA($А:$А);COUNTA($1:$1)) , в русской версии Excel =СМЕЩ($А$1;0;0;СЧЁТЗ($А:$А);СЧЁТЗ($1:$1)) .
Щелкните на кнопке Добавить (Add), затем на кнопке ОК. После этого нужно добавить код, который будет выполняться каждый раз при открытии рабочей книги. Правой кнопкой мыши щелкните значок Excel (расположенный в левом верхнем углу окна), в контекстном меню выберите команду Исходный текст (View Code) и введите код из листинга 4.2.
// Листинг 4.2 Private Sub Workbook_0pen() With Worksheets(«Data») .Range(«2:1000»).Clear .Range(«1:1»).AutoFi11 .Range(«1:1000») .Range(«2:1000») = .Range(«2:1000»).Value End With End Sub
Предыдущий код учитывает только 1000 строк данных. Это число должно быть всегда больше количества строк, которое, по вашему мнению, вам понадобится. Другими словами, если таблица в другой рабочей книге содержит 500 строк, добавьте еще несколько сотен, чтобы учесть возможный рост исходной таблицы.
Теперь, если необходимо, можно скрыть этот лист, выбрав команду Формат → Лист → Скрыть (Format → Sheet → Hide) или воспользовавшись способом, описанным в разделе «Трюк №5. Как скрыть лист, чтобы его невозможно было отобразить».
Теперь, чтобы использовать в качестве основы сводной таблицы этот динамический именованный диапазон, выделите любую ячейку сводной таблицы и на панели инструментов Сводные таблицы (PivotTable) выберите команду Сводная таблица → Мастер сводных таблиц (PivotTable → Wizard). Щелкайте кнопку Назад (Back), пока не достигнете шага 1. Установите первый переключатель В списке или базе данных Microsoft Excel (Microsoft Excel List or Database), щелкните на кнопке Далее (Next) и на шаге 2 введите =PivotData (имя динамического именованного диапазона). Щелкните на кнопке Готово (Finish).
Вы не будете страдать от задержки в работе, которая случается, когда сводная таблица ссылается на внешний источник данных, так как сами данные хранятся в той же рабочей книге. Кроме того, коль скоро вы применили динамический именованный диапазон, сводная таблица стала динамической, она не ссылается на множество пустых строк, и размер файла остается допустимым.
Глава 8. Работа с внешними данными в Таблицах Excel
Это продолжение перевода книги Зак Барресс и Кевин Джонс. Таблицы Excel: Полное руководство для создания, использования и автоматизации списков и таблиц (Excel Tables: A Complete Guide for Creating, Using and Automating Lists and Tables by Zack Barresse and Kevin Jones. Published by: Holy Macro! Books. First printing: July 2014. – 161 p.). Получение и хранение огромного количества данных стало важной частью современного бизнес-ландшафта, и анализ таких данных становится все более сложным. Чтобы справиться с этой тенденцией, Microsoft продолжает добавлять в Excel средства обработки запросов и анализа данных.
Последнее дополнение – это набор инструментов BI (business intelligence). Эти инструменты дают возможность представить данные с помощью традиционных инструментов, таких как Таблицы, сводные таблицы и диаграммы, чтобы эффективно рассказать историю. До сих пор мы изучали роль Таблиц в рабочей книге. В этой главе рассматривается, как Excel использует Таблицы для обработки внешних данных.
Рис. 8.1. Группа Получить и преобразовать данные вкладки Данные
Скачать заметку в формате Word или pdf, примеры в формате Excel
Подключение к внешним данным
Вы можете получить доступ к внешним источникам через вкладку Данные, группу Получить и преобразовать данные (рис. 8.1). Подключения к данным хранятся вместе с книгой, и вы можете просмотреть их, выбрав пункт Данные –> Запросы и подключения.
Подключение к данным может быть отключено на вашем компьютере. Для подключения данных пройдите по меню Файл –> Параметры –> Центр управления безопасностью –> Параметры центра управления безопасностью –> Внешнее содержимое. Установите переключатель на одну из опций: включить все подключения к данным (не рекомендуется) или запрос на подключение к данным.
Рис. 8.2. Настройка доступа к внешним данным; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке
Подробнее о подключении к внешним источникам данных см. Кен Пульс и Мигель Эскобар. Язык М для Power Query. При использовании таблиц, подключенных к данным можно переставлять и удалять столбцы, не изменяя запрос. Excel продолжает сопоставлять запрошенные данные с правильными столбцами. Однако ширина столбцов обычно автоматически устанавливается при обновлении. Чтобы запретить Excel автоматически устанавливать ширину столбцов Таблицы при обновлении, щелкните правой кнопкой мыши в любом месте Таблицы и пройдите по меню Конструктор –> Данные из внешней таблицы –> Свойства, а затем снимите флажок Задать ширину столбца.
Рис. 8.3. Свойства Таблицы, подключенной к внешним данным
Подключение к базе данных
Для подключения к базе данных SQL Server выберите Данные –> Получить данные –> Из базы данных –> Из базы данных SQL Server. Появится мастер подключения к данным, предлагающий элементы управления для указания имени сервера и типа входа, который будет использоваться для открытия соединения. Обратитесь к своему администратору SQL Server или ИТ-администратору, чтобы узнать, как ввести учетные данные для входа.
Рис. 8.4. Подключение к базе данных SQL Server
При импорте данных в книгу Excel их можно загрузить в модель данных, предоставив доступ к ним другим инструментам анализа, таким как Power Pivot.
Существует много различных типов доступных источников данных, и иногда шаблоны соединений по умолчанию, представленные Excel, не работают. Посетите сайт https://www.connectionstrings.com/. Там есть много примеров строк подключения, статьи и форум вопросов и ответов.
Имя Таблицы
Excel импортирует каждую выбранную таблицу базы данных в новую Таблицу Excel на новом листе в активной книге. Он создает имена Таблиц в формате Table_Name, где Name – это имя таблицы базы данных или представления в базе данных SQL Server. При импорте одной таблицы имя таблицы будет Table_ServerName_ DatabaseName_TableName, где ServerName – имя сервера, DatabaseName – имя базы данных, а TableName – имя импортируемой таблицы.
Работа с текстовыми файлами
Текстовые файлы обычно представлены в виде CSV-файла (значения, разделенные запятыми), txt-файла (с разделителями табуляции) или PRN-файла (с фиксированными полями или пробелами). Excel по-прежнему создает подключение к данным при импорте текстовых файлов, но не помещает данные в Таблицу. Это связано с отсутствием информации о данных, содержащихся в текстовом файле. Базы данных имеют определенные правила, такие как уникальные имена полей/столбцов, в то время как текстовые файлы не связаны этими правилами. Excel загружает текстовые данные в запрос; Excel не пытается принудительно поместить данные в Таблицу или другой структурированный формат.
Подключение к текстовому файлу
Чтобы выбрать текстовый файл для импорта, пройдите по меню Данные –> Получить и преобразовать данные –> Из текстового/CSV-файла. Форматы файлов по умолчанию – PRN, TXT и CSV. После выбора файла Excel открывает диалоговое окно:
Текст файл был подготовлен авторами книги в англо-американской традиции, поэтому перед размещением данных в книге Excel, их нужно доработать. Кликните кнопку Преобразовать данные, и измените формат столбцов OrderDate, Cost и Total используя локаль (подробнее см. Изменение настроек Power Query, действующих по умолчанию).
Рис. 8.6. Данные после преобразования в редакторе Power Query
Теперь данные можно поместить на лист Excel. Кликните Закрыть и загрузить. Excel по умолчанию поместит данные в Таблицу:
Рис. 8.7. Обработанные данные из текстового файла в Таблице Excel
Чтобы обновить подключение к текстовому файлу, необходимо обновить запрос.
Работа с данными из Azure Marketplace
Azure Marketplace является хранилищем данных и приложений анализа данных. Это облачная инфраструктура и платформа, которая используется для создания, развертывания и управления облачными службами. Данные, доступные в Azure Marketplace, можно экспортировать в текстовые файлы, поместить в модель данных Power Pivot или непосредственно в Таблицу. Если запрашиваемый набор записей превышает 1 млн, его придется импортировать в модель данных, которая поддерживает неограниченное число строк.
Для подключения к Azure Marketplace из Excel требуется URL-адрес данных и ключ учетной записи. На домашней странице Azure Marketplace щелкните ссылку Databases. Появится страница со списком наборов данных; некоторые из них бесплатны, а некоторые доступны за плату. Выберите один из бесплатных источников данных. Пройдите регистрацию. После подключения к набору данных Azure Marketplace это соединение становится таким же, как и любое другое, и его можно обновить, чтобы работать с самыми последними данными. Имейте в виду, что каждое соединение считается трансакцией.
Сводные таблицы Excel
Как создать в Excel выпадающий список с данными из другого файла
Создать простой выпадающий список в ячейке с данными из этого же файла – просто. Все усложняется, когда сам список и данные для него находятся в разных файлах. Для решения этой проблемы есть несколько способов.
Способ 1. Функция ДВССЫЛ
В простом случае можно использовать функцию ДВССЫЛ (INDIRECT), чтобы сформировать правильную ссылку на внешний файл. Например, если необходимо создать выпадающий список с содержимым ячеек А1:А10 с листа Список из файла Товары.xls, нужно открыть окно проверки данных через вкладку Данные – Проверка данных (Data – Validation) и в поле Источник (Source) ввести следующую конструкцию: =ДВССЫЛ(«[Товары.xls]Список!$A$1:$A$10») .
Чтобы сформировать правильную ссылку на внешний файл можно использовать функцию ДВССЫЛ
Функция ДВССЫЛ (INDIRECT) преобразует текстовую строку аргумента в реальный адрес, используемый для ссылки на данные. Обратите внимание, что имя файла заключается в квадратные скобки, а восклицательный знак служит разделителем имени листа и адреса диапазона ячеек. Если имя файла содержит пробелы, то его надо заключить в апострофы.
Если файл с исходными данными для списка лежит в другой папке, необходимо указать полный путь к файлу, например, следующим образом: =ДВССЫЛ(«‘C:Поставщики[Товары.xls]Список’!$A$1:$A$10») . В данном случае не забудьте заключить в апострофы полный путь к файлу и имя листа. Минус этого способа только один – выпадающий список будет корректно работать только в том случае, если файл Товары.xls открыт.
Способ 2. Импорт данных
Суть этого способа в том, что данные для выпадающего списка буду импортироваться из другого файла в текущий. Для этого необходимо создать обновляемую связь между двумя файлами.
Сначала откройте файл-источник, где находятся эталонные значения для выпадающего списка (назовем его, допустим, Справочник.xlsx). Выделите диапазон с данными для списка и отформатируйте его как таблицу с помощью кнопки Форматировать как таблицу на вкладке Главная (Home – Format as Table). Обратите внимание, что у такой таблицы предварительно должна быть сделана «шапка» – строка заголовка. После этого файл Справочник можно сохранить и закрыть.
Теперь откроем книгу, где мы хотим создать выпадающий список (условно назовем ее Бланк.xlsx). Вставим чистый лист (Alt+F11), выберем на вкладке Данные – Существующие подключения – Найти другие (Data – Existing Connections – Browse for more) и укажем наш файл Справочник.xlsx. Появится диалоговое окно, в котором Excel спросит нас о том, какую именно таблицу мы хотим импортировать (если их в файле было несколько).
Теперь откроем книгу, где мы хотим создать выпадающий список
После нажатия на ОК появится еще одно последнее окно, где можно указать удобную ячейку для импорта и, нажав на кнопку Свойства (Properties), задать частоту обновления информации.
После нажатия на ОК появится еще одно последнее окно
Тут можно включить флажок Обновить при открытии файла (Refresh on open), чтобы каждый раз при открытии этой книги иметь последнюю версию списка.
Можно включить флажок Обновить при открытии файла
После нажатия на кнопку ОК, Excel загрузит данные из созданной таблицы из файла Справочник в наш текущий файл Бланк и отформатирует их в виде таблицы.
Excel загрузит данные из созданной таблицы
Если выделить импортированный список (диапазон А2:А7 в нашем случае), то в строке формул можно увидеть его имя, которое он автоматически получает при вставке.
В строке формул можно увидеть имя импортированного списка
Это имя также можно увидеть в Диспетчере имен на вкладке Формулы (Formulas – Name Manager).
Осталось создать выпадающий список, который будет ссылаться на эти данные. Для этого:
- Выделяем ячейки, где хотим создать выпадающие списки.
- На вкладке Данные жмем на кнопку Проверка данных (Data – Validation).
- Выбираем в раскрывающемся списке разрешенных типов данных вариант Список (List) и вводим в поле Источник (Source) следующую формулу: =ДВССЫЛ(«Таблица_Справочник») . В англоязычной версии Excel это будет =INDIRECT(«Таблица_Справочник») .
Осталось создать выпадающий список
Логично было бы ввести просто имя нашего диапазона, но, к сожалению, Microsoft Excel почему-то не воспринимает имена таблиц в поле Источник. Поэтому мы используем тактическую хитрость – функцию ДВССЫЛ (INDIRECT), которая превращает свой аргумент (имя нашей таблицы) в рабочую ссылку.
Все. После нажатия на ОК наш список начнет работать и будет в дальнейшем автоматически обновляться, подгружая данные из другого файла.
После нажатия на ОК список начнет работать и автоматически обновляться
