Excel данные с другого excel Excelka.ru - все про Ексель

Excel данные с другого excel

Вставка данных из другого листа в Excel

Начинаем заполнять документ данными, необходимыми для вычислений. Сначала укажем стоимость всех товаров в рублях. Можно, конечно, вписывать их вручную, меняя значения почти ежедневно, в зависимости от курса. Но это неправильно. Как сделать так, чтобы программа брала необходимые значения из уже готового документа, в данном случае — из листа «Прайс-лист»? Ведь там все значения уже есть, и цены автоматически вычисляются в зависимости от курса у.е.

Перейдем на лист «Прайс-лист». Посмотрим, что мы здесь имеем. Стоимость первого товара в рублях — доски для сноуборда «Balance» — находится в ячейке «D6». Запомним это.

Переходим на наш лист. Отмечаем ячейку, где должна располагаться цена доски «Balance» в рублях. В данном случае это ячейка «Е2». В строке формул пишем следующее выражение: «=’Прайс-лист’!D6». В апострофах (‘…’) пишем название листа в документе, откуда мы собираемся брать данные. После восклицательного знака идет название ячейки. Ничего сложного.

Отлично! Именно этот результат мы и хотели получить. Теперь нам необходимо только изменять курс у.е. в прайс-листе, а цены в накладной будут изменяться автоматически.

Аналогично вносим формулы вычисления во все остальные ячейки.

Теперь начинаем вычислять сумму купленного товара в каждой строке. Для этого необходимо цену умножить на количество. Правильно?

Подключение к другой книге

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке) .

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

С помощью функции Получить и преобразовать данные» (Power Query) Excel можно подключиться к книге Excel.

На вкладке Данные щелкните Получить данные > Из файла > Из книги. Если вы не видите кнопки Получить данные, нажмите кнопку Создать запрос и выберите пункты Из файла > Из книги.

Найдите книгу в окне Импорт данных.

В окне Навигатор выберите таблицу или лист, которые вы хотите импортировать, а затем нажмите кнопку Загрузить или Изменить.

В Excel 2010 и 2013 существует два способа подключения к другой книге. Рекомендуется использовать Power Query (для этого нужно скачать надстройку Power Query). Если вам не удается скачать надстройку Power Query, вы можете использовать мастер подключения к данным.

На вкладке ленты Power Query щелкните Из файла > Из Excel.

Перейдите к книге.

В окне Навигатор выберите таблицу или лист, которые вы хотите импортировать, а затем нажмите кнопку Загрузить или Изменить.

Мастер подключения к данным

Шаг 1. Создание подключения к книге

На вкладке Данные нажмите кнопку Подключения.

В диалоговом окне Подключения к книге нажмите кнопку Добавить.

В нижней части диалогового окна Существующие подключения нажмите кнопку Найти другие.

Найдите нужную книгу и нажмите кнопку Открыть.

В диалоговом окне Выбор таблицы выберите нужную таблицу (лист) и нажмите кнопку ОК.

В диалоговом окне Выбор таблицы листы называются таблицами.

Одновременно можно добавить только одну таблицу.

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

Чтобы добавить дополнительные таблицы, повторите шаги 2–5.

Нажмите кнопку Закрыть.

Шаг 2. Добавление таблиц к листу

Нажмите кнопку Существующие подключения, выберите таблицы и нажмите кнопку Открыть.

В диалоговом окне Импорт данных выберите расположение данных в книге и как их представить: в виде таблицы, сводной диаграммы или сводной таблицы.

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

Мастер подключения к данным

Шаг 1. Создание подключения к книге

На вкладке Данные нажмите кнопку Подключения.

В диалоговом окне Подключения к книге нажмите кнопку Добавить.

В нижней части диалогового окна Существующие подключения нажмите кнопку Найти другие.

Найдите нужную книгу и нажмите кнопку Открыть.

В диалоговом окне Выбор таблицы выберите нужную таблицу (лист) и нажмите кнопку ОК.

В диалоговом окне Выбор таблицы листы называются таблицами.

Читать еще:  В excel не работает объединение ячеек в

Одновременно можно добавить только одну таблицу.

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

Чтобы добавить дополнительные таблицы, повторите шаги 2–5.

Нажмите кнопку Закрыть.

Шаг 2. Добавление таблиц к листу

Нажмите кнопку Существующие подключения, выберите таблицы и нажмите кнопку Открыть.

В диалоговом окне Импорт данных выберите расположение данных в книге и как их представить: в виде таблицы, сводной диаграммы или сводной таблицы.

Обновление данных книги

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Сводные таблицы 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).

Читать еще:  Сцепить в excel диапазон ячеек

Осталось создать выпадающий список, который будет ссылаться на эти данные. Для этого:

  1. Выделяем ячейки, где хотим создать выпадающие списки.
  2. На вкладке Данные жмем на кнопку Проверка данных (Data – Validation).
  3. Выбираем в раскрывающемся списке разрешенных типов данных вариант Список (List) и вводим в поле Источник (Source) следующую формулу: =ДВССЫЛ(«Таблица_Справочник») . В англоязычной версии Excel это будет =INDIRECT(«Таблица_Справочник») .

Осталось создать выпадающий список

Логично было бы ввести просто имя нашего диапазона, но, к сожалению, Microsoft Excel почему-то не воспринимает имена таблиц в поле Источник. Поэтому мы используем тактическую хитрость – функцию ДВССЫЛ (INDIRECT), которая превращает свой аргумент (имя нашей таблицы) в рабочую ссылку.

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

После нажатия на ОК список начнет работать и автоматически обновляться

Excel At Excel вып.3: Собираем данные с разных листов

Дано: 22 таблицы унифицированного формата с перечнем помещений от 22 дочерних предприятий.

Задача: сделать сводную таблицу с данными всех 22 таблиц

Итак, решение. Есть три варианта решения данной задачи. Первый — использовать встроенную (начиная с excel 2013) надстройку PowerPivot (об этом методе мы поговорим позднее). Второй — посредством сводной таблицы через несколько диапазонов консолидации. Не люблю этот метод, т.к. такая сводная таблица имеет существенно более ограниченный функционал по сравнению с простой сводной таблицей. Наконец, третий вариант — это создать общую таблицу с использованием функции INDIRECT (ДВССЫЛ). Этому методу и посвятим сегодня наш пост.

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

1) как заставить общую таблицу «переключаться» с одного листа данных на другой?;

2) как заставить ее делать это в нужный момент (когда закончились строки на одном листе)?

Начнем со второго вопроса, т.к. ответ на него мы уже знаем. Это циклы, о которых мы подробно говорили в выпуске 2 «Циклы в Excel без VBA». Для решения проблемы достаточно в наш файл добавить лист с перечнем всех обществ, соответствующими названиями листов и количеством подсчетом количества строк на каждом таком листе.

Обратите внимание на формулу в столбце Кол-во записей. Мы могли бы использовать просто COUNTA (СЧЁТА) со ссылкой на каждый лист. Но это потребует времени для «линковки» каждого листа. Плюс мы с вами готовим универсальные решения, которые будут работать в независимости от количества листов.

Именно для этого в функцию COUNTA (СЧЁТА) и внедряется эта чуднАя функция INDIRECT (ДВССЫЛ). Остановимся на ней подробнее.

Функция имеет, по сути, единственный параметр — ref_text (ссылка_на_текст). Что делает эта функция? Всего-навсего преобразует текст в ссылку на ячейку. То есть она преобразует текст, например, «А1» в ссылку и возвращает значение ячейки А1.

Соответственно, зная несложные правила работы с текстом в Excel, мы можем легко сделать текст внутри изменяемым, а значит можем сделать и изменяемыми ссылки, которые будет возвращать функция INDIRECT (ДВССЫЛ).

Разберем пример выше. Функция INDIRECT (ДВССЫЛ) имеет следующий вид:

Чтобы понять, что означает текст внутри нее, давайте вспомним, как выглядит в Excel ссылка на ячейку на другом листе. Вот так:

Соответственно, в нашем случае у нас неизменен столбец B:B, а вот название листа меняется. Для «автоматизации» формулы заменяем Название листа на ссылку на название листа с данными по соответствующему обществу (столбец Название листа). Получаем следующее:

где все, что подчеркнуто — текст, а выделено жирным — ссылки.

ВАЖНО! Обратите внимание на кавычки и конкатенацию («склеивание») при помощи амперсента. Не забудьте, что текст вносится в кавычках и соединяется со ссылками при помощи символа &.

Получается, что INDIRECT (ДВССЫЛ) получает название листа из ячеек в столбце А:А (Название листа), а диапазон у нас прописан фиксированно текстом (В:В). COUNTA (СЧЁТА) же просто считает количество строк в столбце В:В на соответствующем листа за вычетом заголовка.

Перейдем к формированию общей таблицы. Как вы уже наверное прикинули, у нас будет два вложенных цикла: один — количество обществ, второй — количество записей на листе данных по соответствующему обществу. Соответственно, нам потребуются два вспомогательных столбца (определим их в столбцы А и В). Ровно так же, как мы делали на примере в выпуске 2 «Циклы в Excel без VBA», пишем формулу первого цикла:

Читать еще:  Автоматическое расширение ячейки в excel при заполнении

=IF(A2=»»;»»;IF(COUNTIF($A$2:A2;A2)=OFFSET(‘Список обществ’!$E$2;A2-1;0);IF(A2+1>COUNTA(‘Список обществ’!A:A)-1;»»;A2+1);A2))

Затем пишем формулу второго цикла:

Далее при помощи уже знакомой нам функции OFFSET (СМЕЩ) заполняем столбцы из таблицы на листе Список обществ.

Осталось подтянуть данные с 22 листов по обществам. Тут нам опять поможет OFFSET (СМЕЩ), но уже в паре с INDIRECT (ДВССЫЛ).

OFFSET (СМЕЩ) передает в формулу название листа, а конструкция B2+1 — номер строки в столбце Е на том листе.

ВАЖНО! Обратите внимание, что OFFSET (СМЕЩ) вкладывается внутрь INDIRECT (ДВССЫЛ), а не наоборот.

Трюк №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).

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

Ссылка на основную публикацию