10.10.2012 26899

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

[stextbox id=»info»]Этот способ недоступен на Mac. Вам нужно будет открыть VBE, нажав сочетание клавиш Option+F11 или выбрав команду Tools → Macro → Visual Basic Editor. Затем в окне Projects, удерживая клавишу Ctrl, щелкните This Workbook.[/stextbox]
Чтобы вернуться в Excel, закройте окно Script или нажмите сочетание клавиш Alt/Apple+Q.

Предыдущий код учитывает только 1000 строк данных. Это число должно быть всегда больше количества строк, которое, по вашему мнению, вам понадобится. Другими словами, если таблица в другой рабочей книге содержит 500 строк, добавьте еще несколько сотен, чтобы учесть возможный рост исходной таблицы.
[stextbox id=»warning»]Не указывайте слишком большие числа (например, 10 000, если только у вас в действительности нет такого количества данных), так как это сильно повлияет на скорость выполнения кода и обновления данных.[/stextbox]
Сохраните рабочую книгу, закройте ее и откройте заново, удостоверившись, что включили макросы. Добавленный код выполнится автоматически и скопирует формулы в строке 1 на лист Data, а затем автоматически преобразует все строки, кроме первой, в значения. Вы получите копию источника данных, которая будет обновляться при каждом открытии рабочей книги.

Теперь, если необходимо, можно скрыть этот лист, выбрав команду Формат → Лист → Скрыть (Format → Sheet → Hide) или воспользовавшись способом, описанным в разделе «Трюк №5. Как скрыть лист, чтобы его невозможно было отобразить».

Теперь, чтобы использовать в качестве основы сводной таблицы этот динамический именованный диапазон, выделите любую ячейку сводной таблицы и на панели инструментов Сводные таблицы (PivotTable) выберите команду Сводная таблица → Мастер сводных таблиц (PivotTable → Wizard). Щелкайте кнопку Назад (Back), пока не достигнете шага 1. Установите первый переключатель В списке или базе данных Microsoft Excel (Microsoft Excel List or Database), щелкните на кнопке Далее (Next) и на шаге 2 введите =PivotData (имя динамического именованного диапазона). Щелкните на кнопке Готово (Finish).

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

Данные•Сводные таблицы