Ссылка, которая ссылается на одну и ту же ячейку или диапазон на нескольких листах, называется трехмерной ссылкой. Трехмерные ссылки — это удобный способ создания ссылок на несколько листов, которые следуют одному шаблону и содержат один и тот же тип данных, например, при консолидации бюджетных данных из разных отделов организации.
Сведения о трехмерных ссылках
Трехмерные ссылки можно использовать для суммирования бюджетных ассигнований между тремя отделами (отделом продаж, отделом кадров и маркетингом) на отдельном листе с помощью следующей трехмерной ссылки:
=SUM(Sales:Marketing!B3)
Можно даже добавить еще один лист, а затем переместить его в диапазон, на который ссылается формула. Например, чтобы добавить ссылку на ячейку B3 листа "Объекты ", переместите лист "Объекты " между листами "Продажи " и " Отдел кадров ", перетащив соответствующую вкладку, как показано на рисунке:
Так как формула содержит трехмерную ссылку на диапазон имен листов, Sales:Marketing! B3, все листы диапазона являются частью нового вычисления.
Сведения о том, как трехмерные ссылки изменяются при перемещении, копировании, вставке и удалении листов
Ниже приведены примеры того, что происходит при вставке, копировании, удалении или перемещении листов, включенных в трехмерные ссылки. В каждом из этих примеров используется формула =СУММ(Лист2:Лист6! A2:A5), чтобы добавить ячейки с A2 по A5 на листах со второго по шестой:
- Вставка или копирование: Если вставить или скопировать листы между листами 2 и 6, Microsoft Excel привлечет к вычислениям все значения в ячейках с A2 по A5 на составляющих листах.
- Удалить — Если удалить листы между листами 2 и 6, Microsoft Excel не будет использовать их значения в вычислениях.
- Переместить — Если листы, находящиеся между листом 2 и листом 6, переместить таким образом, чтобы они оказались перед листом 2 или после листа 6, Microsoft Excel вычтет из суммы содержимое ячеек с перемещенных листов.
- Перемещение конечной точки — Если переместить лист 2 или 6 в другое место книги, Microsoft Excel скорректирует сумму и включит новые листы между ними, если только вы не измените порядок конечных точек в книге на противоположный. Если конечные точки поворачиваются на противоположные, объемные ссылки изменяют лист конечных точек. Например, у вас может быть ссылка на Лист2:Лист6. Если переместить лист 2 так, чтобы он находился в книге после листа 6, формула будет изменена так, чтобы указывать на лист 3:лист 6. Если переместить лист 6 перед листом 2, формула изменяется так, чтобы указывать на лист2:лист 5.
- Удаление конечной точки: Если удалить лист 2 или 6, Microsoft Excel удалит из вычислений значения с этого листа.
Создание трехмерной ссылки
Сделайте следующее:
- Щелкните ячейку, в которой будет введена функция.
- Введите "= " (знак равенства), затем название функции (см. таблицу ниже) и открывающую круглую скобку.
- Щелкните ярлычок первого листа, на который требуется сослаться.
- Удерживая нажатой клавишу 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 Tech Community или получить поддержку в сообществах.