Пятый урок Трехмерные ссылки
Данный пример позволяет увидеть ту часть таблицы данного документа в другом документе с помощью трехмерных ссылок.
Трехмерные ссылки используются при необходимости анализа данных из одной и той же ячейки или диапазона ячеек на нескольких листах одной книги. Трехмерная ссылка включает в себя ссылку на ячейку или диапазон, перед которой ставятся имена листов. Microsoft Excel использует все листы, хранящиеся между начальным и конечным именами, указанными в ссылке.
Создадим таблицу следующего вида, и сохраним его под именем Книга23:
Выделимячейки А1:G16.
Правой кнопкой мыши выбираем Формат ячеек.
Во вкладке граница выбираем Граница внешние и внутренние.
Выделяем 11-ую строчку правой кнопкой мыши, выбираем объединить ячейки.
В ячейки B10:G10 вводим формулы =сумм(B2:B9), для каждого столбца своя буква.
В ячейку B12 вводим формулу, которая считает общее количество проданных деталей за 6 дней только в Москве
=СУММ(B2:G2)+СУММ(B4:G4)+СУММ(B6:G6)+СУММ(B8:G8).
В ячейку B13 введем формулу =(СУММ(B3:G3)).
В ячейку B14 =(СУММ(B5:G5)).
В ячейку B15 =(СУММ(B7:G7)).
В ячейку B16 =(СУММ(B9:G9))
В ячейках C12:G16 нужно убрать внутренние границы. Выделяем эти ячейки, правой кнопкой мыши выбираем Формат ячеек, во вкладке Граница убираем внутренние.
Все оставшиеся ячейки заполняем так, как указано в примере.
Создадим таблицу следующего вида, и сохраним его под именем Книга5:
Выделяем ячейки A1:B6, правой кнопкой мыши выбираем Формат ячеек, во вкладке Граница выбираем внешние и внутренние.
В ячейку B2 введем формулу, которая ссылаясь на другой документ, присваивает то значение, которое мы хотим увидеть в этом документе.
В ячейку B2 введем формулу =[книга23.xlsx]лист1!b12.
В ячейку B введем формулу =[книга23.xlsx]лист1!b13.
В ячейку B4 введем формулу =[книга23.xlsx]лист1!b14.
В ячейку B5 введем формулу =[книга23.xlsx]лист1!b15. 7. В ячейку B6 введем формулу =[книга23.xlsx]лист1!b16.
Консолидация данных.
Консолидация данных это сбор и объединение данных из разных исходных областей. Исходные области могут располагаться на любом листе или книге, на других открытых листах или книгах. Существует несколько способов консолидации. Рассмотрим консолидацию с помощью трехмерных ссылок (трехмерная ссылка включает в себя ссылку на ячейку или диапазон, перед которой ставятся имена листов), что является наиболее предпочтительным способом. При использовании трехмерных ссылок отсутствуют ограничения по расположению данных в исходных областях. То есть сделаем так, чтобы в ячейках искомой таблицы были ссылки на значение ячеек другой таблицы другого документа.
Создайте такую таблицу:
Чтобы создать такую таблицу, необходимо:
Создать новую Книгу.
Ввести данные в ячейки
Сохранить Книгу под именем таблица.xlsx. Теперь сделайте другую таблицу:
Для этого:
Введите имена строк и столбцов таблицы.
Выделите ячейки A1:E3 и выполните команду Главная/(ячейки)Формат/Формат ячеек.
Во вкладке Границы сделайте внутренние и внешние рамки и нажмите ОК.
Выделитешапку таблицы Выделите ячейки A1:E3 и выполните команду Главная/(ячейки)Формат/Формат ячеек.
Во вкладке Заливка измените цвет ячеек таблицы на черный.
Во вкладке Шрифт измените цвет текста на белый.
Теперь нужно установить во второй таблице ссылки на соответствующие ячейки первой таблицы. Для этого используем данные из файла таблица.xlsx.
Чтобы вставить ссылки в последнюю таблицу сделайте следующее:
Выделите ячейку B2 второй таблицы.
В этой ячейке поставьте знак "=".
Перейдите в файл таблица.xlsx, где находятся исходные данные.
Найдите в первой таблице строчку, в которой значения ячейки в столбцах Спорт, Год соответствует значению Гольф, 93 соответственно, и выделите её.
Аналогично проделайте действия с ячейками C2 – E2, B3 – E3.
Нажмите Enter. В ячейку вставится ссылка на ячейку исходной таблицы.
После этих действий должна получиться такая таблица:
