Ссылка, ссылающаяся на одну ячейку или диапазон на нескольких листах, называется трехмерной ссылкой. Трехмерная ссылка — это удобный и удобный способ ссылаться на несколько листов, которые следуют одному шаблону и содержат данные одного типа, например при консолидации бюджетных данных из разных отделов организации.
Сведения о трехмерном справочнике
Вы можете использовать трехмерную ссылку, чтобы сложить бюджетные ассигнования между тремя отделами, отделами продаж, отдела кадров и маркетинга на разных листах, используя следующую трехмерную ссылку:
=SUM(Sales:Marketing!B3)
Можно даже добавить другой лист, а затем переместить его в диапазон, на который ссылается формула. Например, чтобы добавить ссылку на ячейку B3 на листе "Объекты ", переместите лист "Объекты " между листами "Продажи " и "Отдел кадров ", перетаскивая вкладку, как показано на рисунке:
Поскольку формула содержит трехмерную ссылку на диапазон имен листов, Sales:Marketing! B3. Все листы в диапазоне являются частью нового вычисления.
Узнайте, как изменяются трехмерные ссылки при перемещении, копировании, вставке или удалении листов
В приведенных ниже примерах объясняется, что происходит при вставке, копировании, удалении или перемещении листов, включенных в трехмерную ссылку. В каждом из этих примеров используется формула =СУММ(Лист2:Лист6! A2:A5), чтобы добавить ячейки A2–A5 на листы 2–6:
- Вставка или копирование— Если вы вставляете или копируете листы между Листом 2 и Листом 6 (конечные точки в этом примере), Excel включает все значения в ячейках от A2 до A5 в составные листы в вычислениях.
- Удалить — При удалении листов между Листами 2 и Лист6 Excel удаляет их значения из вычисления.
- Переместить — При перемещении листов между листами Sheet2 и Sheet6 в расположение за пределами указанного диапазона листа Excel удаляет их значения из вычисления.
- Перемещение конечной точки — Если переместить Лист 2 или Лист6 в другое место в той же книге, Excel изменит вычисление, чтобы включить новые листы между ними, если вы не измените порядок конечных точек в книге. При обратном изменении конечных точек трехмерная ссылка изменяет лист конечной точки. Например, у вас может быть ссылка на Лист2:Лист6. При перемещении лист 2 так, чтобы он был после листа 6 в книге, формула изменится так, чтобы она указывала на Лист3:Лист6. Если переместить лист 6 перед листом Sheet2, формула изменится так, чтобы она указывала на Лист2:Лист5.
- Удаление конечной точки — Если удалить Лист 2 или Лист6, Excel удаляет значения на этом листе из вычисления.
Создание трехмерной ссылки
Сделайте следующее:
- Щелкните ячейку, в которой вы введете функцию.
- Введите a = (знак равенства), за которым следует имя функции (см. таблицу ниже), а затем открываемую скобку.
- Щелкните вкладку для первого листа, на который вы хотите ссылаться.
- Удерживая нажатой клавишу SHIFT, щелкните вкладку последнего листа, на который вы хотите ссылаться.
- Выберите ячейку или диапазон ячеек, на которые требуется ссылаться.
- Заполните формулу и нажмите клавишу ВВОД.
В трехмерной ссылке можно использовать следующие функции:
| Функция | Описание |
|---|---|
| Функция СРЗНАЧ | Вычисляет среднее (среднее арифметическое) чисел. |
| СРЗНАЧА | Вычисляет среднее (среднее арифметическое) чисел; включает текст и логику. |
| Функция СЧЁТ | Подсчитывает ячейки, содержащие числа. |
| СЧЁТЗ | Подсчитывает не пустые ячейки. |
| СРГАРМ | Возвращает гармоничное среднее набора данных положительных чисел: взаимное арифметическое среднее для взаимных значений. |
| ЭКСЦЕСС | Возвращает эксцесс множества данных. |
| НАИБОЛЬШИЙ | Возвращает k-ое по величине значение из множества данных. Например, пятое по величине число. |
| Функция МАКС | Находит наибольшее значение в наборе значений. |
| МАКСА | Находит наибольшее значение в наборе значений; включает текст и логику. |
| МЕДИАНА | Возвращает медиану или число в середине набора заданных чисел. |
| Функция МИН | Находит наименьшее значение в наборе значений. |
| МИНА | Находит наименьшее значение в наборе значений; включает текст и логику. |
| ПЕРСЕНТИЛЬ | Возвращает k-й процентиль значений в диапазоне, где k находится в диапазоне 0..1. Предоставляется для совместимости с более ранними версиями Excel. |
| ПРОЦЕНТИЛЬ.ИСКЛ | Возвращает k-ю процентиль для значений диапазона, где k — число от 0 и 1 (не включая эти числа). |
| ПРОЦЕНТИЛЬ.ВКЛ | Возвращает k-ю процентиль для значений диапазона, где k — число от 0 и 1 (включая эти числа). |
| ПРОЦЕНТРАНГ | Возвращает ранг значения в наборе данных в виде процента (0,.1) набора данных. Предоставляется для совместимости с более ранними версиями Excel. |
| ПРОЦЕНТРАНГ.ИСКЛ | Возвращает ранг значения в наборе данных как процентное содержание в наборе данных (от 0 до 1, не включая эти числа). |
| ПРОЦЕНТРАНГ.ВКЛ | Возвращает ранг значения в наборе данных как процентное содержание в наборе данных (от 0 до 1, включая эти числа). |
| КВАРТИЛЬ | Возвращает квартил набора данных на основе значений процентиля от 0..1. Предоставляется для совместимости с более ранними версиями Excel. |
| КВАРТИЛЬ.ИСКЛ | Возвращает квартил набора данных, основанный на значениях процентилей от 0..1, исключающих. |
| КВАРТИЛЬ.ВКЛ | Возвращает квартиль набора данных на основе значений процентили от 0 до 1 (включительно). |
| ПРОИЗВЕД | Умножает числа. |
| РАНГ | Возвращает ранг числа в списке чисел: его размер относительно других значений в списке. Предоставляется для совместимости с более ранними версиями Excel. |
| РАНГ.РВ | Возвращает ранг числа в списке чисел: его размер относительно других значений в списке. |
| РАНГ.СР | Возвращает ранг числа в списке чисел: его размер относительно других значений в списке. |
| СКОС | Возвращает асимметрию распределения. |
| НАИМЕНЬШИЙ | Возвращает k-ое наименьшее значение в множестве данных. |
| СТАНДОТКЛОН.В | Вычисляет стандартное отклонение на основе выборки. |
| СТАНДОТКЛОН.Г | Вычисляет стандартное отклонение всей совокупности. |
| СТАНДОТКЛОНА | Вычисляет стандартное отклонение на основе выборки; включает текст и логику. |
| СТАНДОТКЛОНПА | Вычисляет стандартное отклонение всей совокупности; включает текст и логику. |
| УРЕЗСРЕДНЕЕ | Возвращает среднее внутренности множества данных. |
| Функция СУММ | Добавляет числа. |
| ДИСП.В | Оценивает дисперсию по выборке. |
| ДИСП.Г | Вычисляет дисперсию для всей совокупности. |
| ДИСПА | Оценивает дисперсию на основе выборки; включает текст и логику. |
| ДИСПРА | Вычисляет дисперсию для всей совокупности; включает текст и логику |
Создание имени для трехмерной ссылки
Сделайте следующее:
- На вкладке Формулы щелкните Определить имя (в группе Определенные имена ).
- Во всплывающем окне Новое имя введите имя для ссылки. Длина имени не может превышать 255 знаков.
- В списке Ссылки выберите знак равенства (=) и ссылку, а затем нажмите клавишу Backspace.
- Щелкните вкладку для первого листа, на который вы хотите ссылаться.
- Удерживая нажатой клавишу SHIFT, щелкните вкладку листа ссылки.
- Выберите ячейку или диапазон ячеек, на которые требуется ссылаться.
Подробнее
Дополнительные сведения о перемещении листа в книге см. в статье Перемещение или копирование листа. Дополнительные сведения о создании и использовании определенных имен см. в статье Определение и использование имен в формулах.
Дополнительные сведения
Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществах.