С помощью трехмерной привязки вы создаете ссылку, которая ссылается на диапазон рабочих листов. Это элегантный способ анализировать большое количество идентичных листов с помощью простых формул.
Иногда в Excel вам может понадобиться сослаться на большое количество листов с одинаковой структурой. В этом случае вы можете использовать специальный трюк под названием «3D эталон».
Вот результаты тестов, которые мы рассмотрели ранее. В сводном листе представлены результаты с 1 по 5 неделю.
Предположим, мы хотим усреднить эти результаты тестов? В этом случае, поскольку у нас уже есть все результаты тестов на одном листе, мы можем просто использовать функцию СРЕДНЕЕ и ссылаться на ячейки в столбцах с B по H, и мы получаем среднее значение.
Но что, если мы хотим получить среднее значение по всем тестам, а у нас нет их всех на одном листе? В этом случае, если все листы, на которые вы хотите ссылаться, имеют одинаковую точную структуру и отображаются в книге рядом друг с другом, вы можете использовать так называемое трехмерное сопоставление.
Трехмерная привязка работает как диапазон ячеек - вы используете двоеточие между начальным и конечным листами. Итак, чтобы получить средние результаты тестов с 1 по 5 неделю, я могу запустить функцию AVERAGE с открытыми скобками, а затем ввести:
Неделя 1: Неделя 5
Затем восклицательный знак, а затем адрес первого результата теста на каждом листе, D6.
Когда я ввожу формулу и копирую ее, мы получаем точно такие же средние значения, которые рассчитывали ранее.
Но в этом случае формула не зависит от значений в сводной таблице. Вместо этого он использует значения с первого листа (неделя 1) по последний лист (неделя 5). Фактически, мы могли бы просто удалить значения из сводной таблицы, и среднее значение продолжало бы работать.
Я это исправлю.
Затем удалите трехмерную формулу и посмотрите, как ввести формулу, указав и щелкнув.
Запустите формулу как обычно. Затем щелкните первый лист, Неделя 1. Теперь, удерживая нажатой клавишу Shift, щелкните последний лист, Неделя 5. Excel построит трехмерную ссылку. Чтобы завершить формулу, щелкните D6 и нажмите клавишу ВВОД.
Обратите внимание, что Excel не заботится об именах листов при создании 3D-ссылки. Он просто включает все листы между первой и последней ссылкой. Если переименовать неделю 1, все по-прежнему работает нормально, но ссылка обновляется.
Однако, если я перенесу неделю на 1 неделю, мы фактически удаляем недели 2, 3 и 4 из расчета, поэтому мы получаем среднее значение только для недель 1 и 5.
ЕСЛИ мы переместим пятую неделю обратно в исходное место, все будет работать так же, как и раньше.
Курс
Основная формулаСвязанные ярлыки
Расширить выделение до последней ячейки вниз Ctrl + Shift + ↓ ⌃ + ⇧ + ↓ Отменить последнее действие Ctrl + Z ⌘ + Z Принять и применить Enter Return
