ссылаться на одну и ту же ячейку или диапазон на нескольких листах
В этом кратком руководстве объясняется, что такое трехмерная ссылка Excel и как ее можно использовать для ссылки на одну и ту же ячейку или диапазон ячеек на всех выбранных листах. Вы также узнаете, как создать трехмерную формулу для агрегирования данных на разных листах, например, суммировать одну и ту же ячейку из нескольких листов с помощью одной формулы.
Одной из лучших функций ссылок на ячейки в Excel является трехмерная ссылка или размерная ссылка, как ее еще называют.
Трехмерная ссылка в Excel относится к одной и той же ячейке или диапазону ячеек на нескольких листах. Это очень удобный и быстрый способ расчета данных на нескольких листах с одинаковой структурой, и он может быть хорошей альтернативой функции консолидации Excel. Это может показаться немного расплывчатым, но не волнуйтесь, следующие примеры прояснят ситуацию.
Что такое 3D-справка в Excel?
Как отмечалось выше, ссылка Excel 3D позволяет ссылаться на одну и ту же ячейку или диапазон ячеек на нескольких листах. Другими словами, он ссылается не только на диапазон ячеек, но и на диапазон имен рабочих листов. Ключевым моментом является то, что все листы, на которые ссылаются, должны иметь один и тот же шаблон и один и тот же тип данных. Пожалуйста, рассмотрите следующий пример.
Предположим, у вас есть ежемесячные отчеты о продажах на 4 разных листах:
То, что вы ищете, это узнать общий итог, т.е. сложить промежуточные итоги в четырех месячных листах. Самое очевидное решение, которое приходит на ум, — сложить ячейки промежуточных итогов со всех рабочих листов обычным способом:
=Янв!B6+Фев!B6+Мар!B6+Апр!B6
А если у вас 12 листов на весь год, а то и больше листов на несколько лет? Это было бы очень много работы. Вместо этого вы можете использовать функцию СУММ с 3D-привязкой для суммирования по листам:
=СУММ(Янв:Апр!B6)
Эта формула СУММ выполняет те же вычисления, что и более длинная формула выше, то есть суммирует значения в ячейке B6 на всех листах между двумя указанными вами граничными рабочими листами, Ян а также апр в этом примере:
Кончик. Если вы собираетесь скопировать трехмерную формулу в несколько ячеек и не хотите, чтобы ссылки на ячейки менялись, вы можете заблокировать их, добавив знак $, т. е. используя абсолютные ссылки на ячейки, такие как =СУММ(Янв:Апр!$ 6 долларов США).
Вам даже не нужно вычислять промежуточный итог в каждом ежемесячном листе — включите диапазон ячеек, которые будут вычисляться, непосредственно в вашей 3D-формуле:
=СУММ(Янв:Апр!B2:B5)
Если вы хотите узнать общий объем продаж по каждому отдельному продукту, создайте сводную таблицу, в которой элементы отображаются точно в том же порядке, что и в ежемесячных листах, и введите следующую трехмерную формулу в самую верхнюю ячейку, B2 в этом примере:
=СУММ(Янв:Апр!B2)
Не забудьте использовать относительную ссылку на ячейку без знака $, чтобы формула корректировалась для других ячеек при копировании вниз по столбцу:
Основываясь на приведенных выше примерах, давайте создадим общую трехмерную ссылку Excel и трехмерную формулу.
Справочник по Excel 3D
Первый_лист:Последний_лист!клетка или же
Первый_лист:Последний_лист!диапазон
Трехмерная формула Excel
знак равноФункция(Первый_лист:Последний_лист!клетка) или же
знак равноФункция(Первый_лист:Последний_лист!диапазон)
При использовании таких трехмерных формул в Excel все рабочие листы между Первый_лист а также Последний_лист включаются в расчеты.
Примечание. Не все функции Excel поддерживают 3D-ссылки, вот полный список функций что делать.
Как создать трехмерную ссылку в Excel
Чтобы создать формулу с трехмерной ссылкой, выполните следующие действия:
- Щелкните ячейку, в которую вы хотите ввести 3D-формулу.
- Введите знак равенства (=), введите имя функции и введите открывающую скобку, например =СУММ(
- Щелкните вкладку первого рабочего листа, который вы хотите включить в 3D-ссылку.
- Удерживая нажатой клавишу Shift, щелкните вкладку последнего рабочего листа, который необходимо включить в 3D-образец.
- Выберите ячейку или диапазон ячеек, которые вы хотите вычислить.
- Введите остальную часть формулы, как обычно.
- Нажмите клавишу Enter, чтобы завершить формулу Excel 3-D.
Как включить новый лист в формулу Excel 3D
Трехмерные ссылки в Excel расширяемы. Это означает, что вы можете в какой-то момент создать трехмерную ссылку, затем вставить новый рабочий лист и переместить его в диапазон, на который ссылается ваша трехмерная формула. В следующем примере приведены подробные сведения.
Предположим, сейчас только начало года и у вас есть данные только за первые несколько месяцев. Тем не менее, новый лист, скорее всего, будет добавляться каждый месяц, и вы хотели бы, чтобы эти новые листы включались в ваши расчеты по мере их создания.
Для этого создайте пустой лист, скажем декабрьи сделайте его последним листом в 3D-референсе:
=СУММ(Янв:Декабрь!B2:B5)
Когда новый лист вставлен в книгу, просто переместите его в любое место между январем и декабрем:
Вот и все! Поскольку ваша формула СУММ содержит трехмерную ссылку, она суммирует указанный диапазон ячеек (B2:B5) на всех листах в пределах указанного диапазона имен рабочих листов (янв:декабрь!). Просто помните, что все листы, включенные в ссылку Excel 3D, должны иметь одинаковую компоновку данных и один и тот же тип данных.
Как создать имя для трехмерной ссылки Excel
Чтобы вам было еще проще использовать 3D-формулы в Excel, вы можете создать определенное имя для своего 3D-образца.
- На Формулы вкладку, перейдите в Определенные имена группу и нажмите «Определить имя».
- в Новое имя введите осмысленное и легко запоминающееся имя в поле «Имя» длиной до 255 символов. В этом примере пусть это будет что-то очень простое, скажем моя_ссылка.
- Удалить содержимое файла Относится к поле, а затем введите 3D-привязку следующим образом:
- Введите = (знак равенства).
- Удерживая нажатой клавишу Shift, щелкните вкладку первого листа, на который вы хотите сослаться, а затем щелкните последний лист.
- Выберите ячейку или диапазон ячеек, на которые нужно сослаться. Вы также можете сослаться на весь столбец, щелкнув букву столбца на листе.
В этом примере давайте создадим ссылку Excel 3D для всего столбца B на листах. Ян через апр. В результате вы получите что-то вроде этого:
- Нажмите кнопку OK, чтобы сохранить только что созданное имя 3D-эталона и закрыть диалоговое окно. Сделанный!
А теперь, чтобы просуммировать числа в столбце B во всех рабочих листах из Ян через апрвы просто используете эту простую формулу:
=СУММ(моя_ссылка)
Функции Excel, поддерживающие трехмерные ссылки
Вот список функций Excel, которые позволяют использовать трехмерные ссылки:
SUM – суммирует числовые значения.
СРЗНАЧ – вычисляет среднее арифметическое чисел.
СРЗНАЧ – вычисляет среднее арифметическое значений, включая числа, текст и логику.
COUNT – подсчитывает ячейки с числами.
COUNTA — подсчитывает непустые ячейки.
MAX — возвращает наибольшее значение.
MAXA — возвращает наибольшее значение, включая текст и логику.
MIN – Находит наименьшее значение.
MINA — находит наименьшее значение, включая текст и логику.
ПРОИЗВЕД – умножает числа.
СТАНДОТКЛОН, СТАНДОТКЛОН, СТАНДОТКЛОН, СТАНДОТКЛОНПА — расчет выборочного отклонения заданного набора значений.
VAR, VARP, VARP, VARPA — возвращает выборочную дисперсию указанного набора значений.
Как меняются ссылки Excel 3-D при вставке, перемещении или удалении листов
Поскольку каждая трехмерная ссылка в Excel определяется начальным и конечным листами, давайте назовем их конечными точками трехмерной ссылки, изменение конечных точек изменяет ссылку и, следовательно, изменяет вашу трехмерную формулу. А теперь давайте посмотрим, что именно происходит, когда вы удаляете или перемещаете конечные точки трехмерных ссылок или вставляете, удаляете или перемещаете листы внутри них.
Поскольку почти все легче понять на примере, дальнейшие объяснения будут основываться на следующей трехмерной формуле, которую мы создали ранее:
Вставляйте, перемещайте или копируйте листы в конечных точках. Если вы вставите, скопируете или переместите рабочие листы между конечными точками 3D-привязки (Ян а также апр листов в этом примере), указанный диапазон (ячейки с B2 по B5) во всех вновь добавленных листах будет включен в расчеты.
Удаляйте листы или перемещайте листы за пределы конечных точек. Когда вы удаляете какие-либо рабочие листы между конечными точками или перемещаете листы за пределы конечных точек, такие листы исключаются из вашей 3D-формулы.
Переместить конечную точку. Если вы переместите любую конечную точку (Ян или же апр лист или оба) в новое место в той же книге, Excel изменит вашу трехмерную формулу, чтобы включить новые листы, которые попадают между конечными точками, и исключить те, которые выпали из конечных точек.
Переверните конечные точки. Обращение опорных конечных точек Excel 3D приводит к изменению одного из листов конечных точек. Например, если вы переместите начальный лист (Ян) после конечного листа (апр), Ян лист будет удален из трехмерной ссылки, которая изменится на Feb:Apr!B2:B5.
Перемещение конечного листа (апр) перед стартовым листом (Ян) будет иметь аналогичный эффект. В этом случае апр лист будет исключен из 3D-ссылки, которая изменится на Jan:Mar!B2:B5.
Обратите внимание, что восстановление исходного порядка конечных точек не приведет к восстановлению исходной трехмерной ссылки. В приведенном выше примере, даже если мы переместим Ян лист обратно в первую позицию, 3D-ссылка останется Feb:Apr!B2:B5, и вам придется отредактировать ее вручную, чтобы включить Ян в ваших расчетах.
Удалить конечную точку. Когда вы удаляете один из листов конечных точек, он удаляется из 3D-привязки, а удаленная конечная точка изменяется следующим образом:
- Если первый лист удаляется, конечная точка изменяется на следующий за ним лист. В этом примере, если Ян лист удаляется, ссылка 3D изменяется на Feb:Apr!B2:B5.
- Если последний лист удаляется, конечная точка изменяется на предыдущий лист. В этом примере, если апр лист удаляется, 3D-привязка изменяется на Jan:Mar!B2:B5.
Вот как вы создаете и используете трехмерные ссылки в Excel. Как видите, это очень удобный и быстрый способ вычисления одних и тех же диапазонов более чем на одном листе. Хотя обновление длинных формул, ссылающихся на разные листы, может быть утомительным, трехмерная формула Excel требует обновления всего пары ссылок, или вы можете просто вставить новые листы между конечными точками трехмерных ссылок, не изменяя формулу.
Это все на сегодня. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
