Объемные ссылки в excel как сделать

Создание трехмерной ссылки на один и тот же диапазон ячеек на нескольких листах

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

В этой статье

Подробнее о трехмерных ссылках

Трехэтапную ссылку можно использовать для суммы бюджетных распределений между тремя отделами: отделами продаж, отделом кадров и отделом маркетинга, каждый из которых работает на разных таблицах. Для этого используйте 3-D ссылку:

Можно даже добавить другой таблицу, а затем переместить ее в диапазон, на который ссылается формула. Например, чтобы добавить ссылку на ячейку B3 на предприятии, перетасквинуйте этот таблицу между таблицами "Продажи" и "Отдел кадров", как показано в примере ниже.

Поскольку формула содержит трех объемную ссылку на диапазон имен электронной таблицы, Sales:Marketing! B3,все таблицы в диапазоне будут включены в новое вычисление.

Узнайте, как изменяются трех d-d references when you move, copy, insert, or delete worksheets

В следующих примерах объясняется, что происходит при вставке, копировании, удалении или удалении включенных в трех d ссылку таблиц. В примерах используется формула =СУММ(Лист2:Лист6!A2:A5) для суммирования значений в ячейках с A2 по A5 на листах со второго по шестой.

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

Удалить Если удалить листы между листами 2 и 6, Excel удалит их значения из вычислений.

Переместить Если переместить листы между листами 2 и 6 в место за пределами диапазона, на который имеется ссылка, Excel удалит их значения из вычислений.

Перемещение конечного листа Если переместить лист 2 или 6 в другое место книги, Excel скорректирует сумму таким образом, чтобы между ними включались новые листы, если не изменить порядок конечных точек в книге. Если обратить конечные точки в обратном порядке, трехэтапная ссылка изменяет конечный таблицу. Например, допустим, что имеется ссылка на лист 2:Лист6: если переместить лист 2 после листа 6 в книге, формула будет ссылаться на лист3:Лист6. Если переместить лист 6 перед листом 2, формула будет скорректироваться так, чтобы она была на листе "Лист2:Лист5".

Удаление конечного листа Если удалить лист 2 или 6, Excel удалит из вычислений значения на этом листе.

Создание трехсъемной ссылки

Щелкните ячейку, в которой вы хотите ввести функцию.

Введите = (знак равно), введите имя функции и открываю скобку.

В этом примере можно использовать следующие функции:

Объемные таблицы в Excel

Каждая ячейка в Excel имеет две координаты — номер строки и номер столбца. Поскольку рабочая книга состоит из нескольких рабочих листов (по умолчанию из трех), то ячейке можно присвоить третью координату — номер листа, которая представляет собой третье измерение, что позволяет говорить об объемных таблицах.

Использование рабочих книг с несколькими листами обеспечивает следующие преимущества:

  • гибкость в оформлении рабочих листов;
  • возможность консолидации (объединения) данных.

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

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

Трехмерные ссылки

Excel использует ярлыки листов для определения адреса листа. Имя каждого листа последовательно увеличивается на единицу, начиная с Лист! (Sheetl). Трехмерные формулы включают области, которые охватывают более одного листа, т. е. определяют диапазон листов, содержащих данные.

Лист!: Лист2!В11
Квартал!: Квартал4!А1:А10

Объединение листов

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

  1. Выделите ярлык первого листа.
  2. Нажмите Shift и щелкните ярлык последнего листа.

Ярлыки объединенных листов станут выделенными. Для объединения несмежных листов:

  1. Выделите ярлык первого листа.
  2. Нажмите Ctrl и щелкайте ярлыки тех листов, которые должны быть включены.

Ярлыки объединенных листов станут выделенными.

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

Создание общего заголовка для группы листов

  1. Создайте в новой рабочей книге четыре листа с именами Отдел1, Отдел2, Отдел3 и Общий список.
  2. Выберите ярлык листа Отдел1.
  3. Нажмите Shift и щелкните ярлык листа ОтделЗ (будут выделены листы Отдел1, Отдел2, Отдел3, которые должны быть включены в группу).
  4. Выделите ячейку А1 и введите текст: Список сотрудников по отделам на 01.07.2000 г.
  5. Примените форматирование шрифта Размер 12, Полужирный.
  6. Выделите несколько соседних ячеек (А1:Е1 по размеру таблицы).
  7. Нажмите кнопку Объединить и поместить в центре — на панели Форматирование.
  8. Для отмены объединения листов щелкните ярлык листа Общий список, не принадлежащего к группе.
  9. Просмотрите заголовки на трех листах (Отдел1, Отдел2, Отдел3) и убедитесь, что заголовка на листе Общий список нет.
  10. Введите элементы списка по отделам.
  11. Скопируйте введенные данные на лист Общий список и сохраните файл с именем Список_сотрудников.х1s.
Ссылка на основную публикацию