Как ввести формулу если данные расположены на нескольких рабочих листах

Опубликовано: 29.04.2024

Формулы могут работать с ячейками из других листов. Вам просто нужно указать перед ссылкой на ячейку имя листа и восклицательный знак. Например, следующая формула добавляет 12 к значению в ячейке С1, которая находится на листе Лист2: =Лист2!С1+12.

Что делать, если вам нужно вычислить сумму всех значений в С1, начиная с листа Лист2 и заканчивая листом Лист6? Следующая формула решает эту задачу: =СУММ(Лист2:Лист6!C1). В данном случае двоеточие разделяет название первого и последнего листов.

Для создания такой формулы выполните приведенные ниже действия.

Активизируйте ячейку, которая будет содержать формулу, и наберите =СУММ(.

Щелкните на вкладке первого листа (в нашем случае это Лист2) и выберите ячейку С1.

Нажмите Shift и щелкните на вкладке последнего листа (в нашем случае это Лист6).

Нажмите Enter, и формула будет введена в ячейку.

В шаге 2 вы можете выбрать диапазон, а не одну-единствениую ячейку. Например, эта формула возвращает сумму C1:F12 для всех листов от Лист2 до Лист6: =СУММ(Лист2:Лист6!С1:Р12).

Теперь я покажу вам интересный прием, который узнал при прочтении новостных конференций Excel и на обучение в Минске. Если вы хотите просуммировать одну и ту же ячейку со всех листов, кроме текущего, просто введите формулу наподобие этой: =СУММ(‘*’!C1). Звездочка служит подстановочным символом, который интерпретируется как «все листы, кроме этого одного». Когда вы нажмете клавишу Enter после ввода этой формулы, Excel преобразует формулу, чтобы она использовала фактические имена листов. Это работает, даже если активный лист находится где-нибудь в середине, между другими листами. Например, если книга состоит из шести листов и вы введете приведенную выше формулу в ячейку листа Лист3, Excel создаст следующую формулу: =СУММ(Лист1:Лист2!С1;Лист4:Лист6!C1).

Но это еще не все. Введите следующую формулу для нахождения суммы ячеек С1 во всех листах, которые начинаются со слова Регион: =СУММ(‘Регион*»!C1). Excel может преобразовать данную формулу во что-то наподобие этого: =СУММ(Регион1:Регион4!C1).

Вы также можете использовать подстановочный знак ? — он указывает на любой отдельный символ. Например, при вводе следующей формулы Excel создаст формулу, которая просуммирует значения, начиная с листа Лист1 и заканчивая листом Лист9 (названия, которые содержат одну цифру): =СУММ(‘Лист?’!C1). Этот прием не ограничивается функцией СУММ. Он работает и с другими функциями, такими как СРЗНАЧ, МИН, МАКС.

Функция ЛИСТ предназначена для возвращения номера конкретного листа с промежутком, который открывает доступ ко всей рабочей книге в MS Excel. Функция ЛИСТЫ предоставляет пользователю информацию о количестве листов, содержащихся в рабочей книге.

Формулы с использованием ссылок на другие листы Excel

Предположим у нас имеется фирма DecArt в которой работают сотрудники и им ежемесячно начисляется зарплата. У данной фирмы имеются сведения о среднемесячной зарплате в Excel, а данные по ней размещены на разных листах: на листе 1 размещены данные о зарплате, на листе 2 премия в процентах. Нам необходимо вычислить размер премии в рублях, при том чтобы данные эти были размещены на втором листе.

Для начала рассмотрим пример работы с листами в формулах Excel. Пример 1:

Создадим на листе 1 рабочей книги табличного процессора Excel таблицу, как показано на рисунке. Сведения о среднемесячной заработной плате:
Далее на листе 2 рабочей книги подготовим область для размещения нашего результата — размера нашей премии в рублях, как показано на рисунке:
Далее нам необходимо будет ввести в ячейку В2 формулу, изображенную на рисунке ниже:

Ввод данной формулы происходил следующим образом: для начала в ячейке В2 мы установили знак «=», затем кликнули на «Лист1» в нижнем левом углу рабочей книги и перешли на ячейку C3 на листе 1, далее ввели операцию умножения и перешли вновь на «Лист2» для того чтобы добавить процент.

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

Функция ЛИСТЫ для подсчета количества листов в рабочей книге

Рассмотрим теперь пример работы функции ЛИСТЫ. Довольно часто бывает так, что в рабочей книге Excel расположено слишком много листов. Выяснить их точное количество визуально не представляется возможным, именно с этой целью и создана функция ЛИСТЫ.

В данной функции всего 1 аргумент – «Ссылка» да и то необязательный для заполнения. Если его не заполнять тогда функция возвращает общее количество листов, созданных в текущей рабочей книге файла Excel. При необходимости можно заполнить аргумент. Для этого в нем необходимо указать ссылку на рабочую книгу, в которой необходимо подсчитать общее количество листов, созданных в ней.

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

На рисунке ниже показано примерное количество листов:

Чтобы организовать подсчет всех листов, необходимо воспользоваться функцией ЛИСТЫ. Просто ставим знак равенства «=» и вписываем функцию, не заполняя ее аргументов в скобках. Вызов данной функции показан ниже на рисунке:

В результате получим следующее значение: 12 листов.

Таким образом мы узнали, что в нашей фирме имеется 12 документов, содержащихся в рабочей книге Excel. Этот простой пример наглядно иллюстрирует работу функции ЛИСТЫ. Данная функция может стать полезной для руководителей, офисных сотрудников, менеджеров по продажам.

Ссылки на другие листы в шаблонах документов

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

Создадим таблицу «Банкетное меню», общий вид которой представлен на рисунке ниже:
Аналогичным образом создадим таблицы на разных листах «Общая стоимость» и «Общий выход»:
При помощи формулы со ссылками на другие листы произведем расчет общей стоимости банкетного меню:
Перейдем на лист «Общий выход» и путем умножения ячеек веса одной порции, находящегося на листе 2 и общего количества, находящегося на листе 1 произведем расчет общего выхода:

В результате у нас получился простейший шаблон для подсчета расходов на 1 банкет.

Один из читателей нашего сайта SirExcel задал вопрос, который может встречаться очень часто. Вопрос заключался в следующем «Как сделать так, чтобы на одном листе показывалась информация из определенной ячейки к примеру $A$1 но с каждого листа из книги? Листов очень много надо сделать сводный список содержимого определенной ячейки со всех листов.»

Я как раз собирался написать о том, как работает функция ДВССЫЛ, почему бы не рассмотреть ее именно на этом примере.

Итак, мы имеем файл Excel, в котором много листов. Давайте рассмотрим простой пример, где названия листов у нас не менялись. То есть у нас есть Лист1, Лист2, Лист3 и так далее.

Допустим, что на каждом листе (Лист1-Лист5) у нас есть данные по выручке 5 различных магазинов в виде одинаковых таблиц данных.

На листе 6 есть результирующая таблица, где нам необходимо заполнить ее данными, взяв их со всех листов.

Таким образом, чтобы заполнить данные за январь нам необходимо взять данные по выручке магазинов, находящихся в ячейке B2, но на 5 различных листов.

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

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

ДВССЫЛ – функция, которая возвращает ссылку заданную текстовой строкой.

Чтобы понять принцип действия данной функцию, давайте рассмотрим такой пример.

Посмотрите на рисунок ниже. В ячейке А1 написан текст D4, а в самой ячейке D4 указано число 9999. Если мы напишем формулу ДВССЫЛ(A1), то в результате получим число 9999. То же самое мы получим если напишем формулу так:
=ДВССЫЛ("D4")
То есть мы написали текстом адрес ячейки D4 и функция ДВССЫЛ вернула нам то значение, которое находится по данному адресу (D4).

Надеюсь, все стало более понятным. Теперь давайте рассмотрим на нашем примере. Чтобы заполнить данные за январь с разных листов нам необходимо прописать следующие ссылки на ячейки.

=Лист1!B2 (Выручка за январь магазина 1)

=Лист2! В2 (Выручка за январь магазина 2) и т.д.

Но сложность заключается в том, что если ячейку просто протянуть вниз, то Лист1 не будет меняться на Лист2 и так далее.

Если мы пропишем формулу

ДВCСЫЛ("Лист1!B2"), ДВCСЫЛ("Лист2!B2") и так далее, то функция будет возвращать нам то же самое, но это так же не решает нашу проблему, ведь «Лист1!B2» прописан обычным текстом и так же не будет изменяться при протягивании.

Для решения нашей задачи мы разделим текст «Лист1!B2» на две части (отдельно «Лист1» и отдельно «!B2» — обратите внимание на восклицательный знак во второй части текста) и потом их склеим. Текст Лист1, Лист2 и так далее мы пропишем напротив соответствующих магазинов, при этом написав Лист1, мы сможем просто протянуть ячейку вниз и автоматически получить список Лист1, Лист2 и так далее.

Теперь сцепим этот текст внутри функции ДВССЫЛ с помощью знака & и получим формулу, которая будет автоматически протягиваться и при этом будут подтягиваться данные из одной и той же ячейки, но разных листов.

Итоговая формула выглядит именно так, но я специально расписал с самого начала по шагам, чтобы было понятно из чего состоит данная формула. В принципе, этого уже достаточно, чтобы за короткий срок решить задачу, указанную в начале статьи. Чтобы заполнить данные за февраль, нам необходимо в ячейке D2 написать такую же формулу, но поменять текст «!B2» на «!B3»

=ДВССЫЛ(A2&"!B3") и протянуть вниз, аналогично за март.

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

Итак, мы решили задачу, как быстро заполнить данные за определенный месяц, но нам необходимо заполнить данные за остальные месяца. Мы знаем, что данные за Январь соответствуют ячейке B2, февраль — B3, Март — B4. То есть буква «B» остается неизменной, меняется лишь цифра. Зная это мы просто разобьем текст внутри ссылки ДВССЫЛ не на 2 части, а на три. Добавим дополнительно строку над таблицей и напишем сверху цифры соответствующие ячейки месяца. Пропишем формулу.

=ДВССЫЛ(A3& «!B»&C1), где A3 — это текст «Лист1», «!B» — это неизменный текст и С1 — это цифра 2. Все это объединено с помощью знака & в общий текст «Лист1!B2». Нам также потребуется закрепить столбец A (с помощью знака $), чтобы он не менялся при протягивании формулы вправо и строку 1, чтобы она не менялась при протягивании формулы вниз. Мы получаем следующую итоговую формулу.

=ДВССЫЛ($A3&"!B"&C$1)
которую можно протянуть вправо и вниз.

Заметки по теме:

Если бы листы назывались у нас Магазин 1, Магазин 2 и так далее, то дополнительный столбец со словами Лист1, Лист2 и так далее нам бы не потребовался.
Функцию ДВССЫЛ используют часто тогда, когда требуется изменить ссылку на ячейку в формуле, не изменяя саму формулу.

Если вам понравилась статья, пожалуйста, нажмите +1 и «Мне нравится». Так же подписывайтесь на нашу рассылку или вступайте в нашу группу ВКонтакте, чтобы не пропустить наши следующие уроки по Excel

Иногда одни и те же данные одновременно содержатся в нескольких рабочих листах. Чтобы выполнить ввод данных в несколько рабочих листов, можно применить инструмент группировки Excel. Эту группировку можно выполнить стандартными методами Excel или с использованием кода VBA. [1] Встроенный механизм Excel для одновременного размещения данных в нескольких листах — это средство под названием Группа. Оно позволяет группировать листы, связывая их внутри рабочей книги. Чтобы применить средство Группа, щелкните лист, на котором будете вводить данные, а затем, удерживая клавишу Ctrl, щелкните ярлычки с именами рабочих листов, куда должны одновременно вводиться те же данные (рис. 1).

Рис. 1. Три первых листа объединены в группу

Если нужно выбрать несколько листов, расположенных по порядку, можно кликнуть на первый из них, а затем удерживая Shift кликнуть на последний. Когда вы будете вводить данные в любую ячейку на вашем рабочем листе, они автоматически появятся и на остальных сгруппированных листах. Вы можете переходить между листами в группе, при этом группировка не пропадет. Чтобы отменить группировку листов, либо выберите любой лист, не включенный в группировку, либо щелкните правой кнопкой мыши любой ярлычок листа и выберите команду Разгруппировать листы.

Если у вас сгруппировано несколько рабочих листов, то, взглянув на строку заголовка, вы увидите слово Группа в квадратных скобках. Это позволяет понять, что ваши листы объединены в группу. Однако эта надпись не бросается в глаза, поэтому мы советуем разгруппировывать листы сразу же, как только закончите делать то, для чего была необходима группировка.

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

Эти трудности можно преодолеть при помощи кода VBA. Чтобы этот код заработал, он должен находиться в частном модуле для объекта Лист (а не в модуле рабочей книги). Ранее я однажды уже использовал такой код (см. Создание указателя листов в книге). Чтобы быстро перейти в частный модуль, щелкните правой кнопкой мыши ярлычок листа и в контекстном меню выберите команду Исходный текст. Для запуска кода, расположенного в частном модуле для объекта Лист, вы сможете использовать одно из событий листа Excel, то есть событие, которое происходит в вашем листе, например, изменение ячейки, выбор диапазона, активация, деактивация и так далее. В указанном выше примере макрос запускался каждый раз при переходе на соответствующий лист.

Первое, что необходимо сделать для обработки группировки, — это выбрать имя для диапазона ячеек, который вы хотите сгруппировать, чтобы данные автоматически отображались на других листах. Я, например, выбрал на Лист3 область С3:F8 и ввел в поле имени MyRange (рис. 2). Чтобы убедитьбся, что диапазону присвоено имя, можно перейти на вкладку Формулы и кликнуть на кнопке Диспетчер имен. В открывшемся окне вы увидите строку с параметрами только что определенного имени диапазона.

Рис. 2. Присвоение диапазону ячеек имени MyRange

Убедиться, что вы находить в частном модуле для объекта Лист можно, посмотрев на заголовок окна модуля, в котором отражаются названия файла и листа (см. выделение на рис. 3). В частный модуль введите код:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Range( " MyRange " ), Target) Is Nothing Then

Sheets(Array( " Лист3 " , " Лист1 " , " Лист2 " )).Select

Рис. 3. Код VBA для автоматической группировки листов

В этом коде мы использовали имя диапазона MyRange. Измените MyRange на имя диапазона, которое вы используете в своем листе. Кроме того, измените в коде три имени листов на имена листов, которые хотите сгруппировать. Закончив ввод, закройте окно модуля или нажмите сочетание клавиш Alt+Q, чтобы вернуться в Excel. Сохраните книгу Excel с расширением .xlsm (с поддержкой макросов).

Важно помнить, что первым в этом массиве должно стоять имя листа, содержащего этот код, то есть листа, на котором вы будете вводить данные. После того как код создан, каждый раз, когда вы будете выбирать ячейку на Лист3, код будет проверять, принадлежит ли выбранная ячейка диапазону с именем MyRange (С3:F8). Если да, то код будет автоматически группировать нужные рабочие листы. Если нет, он будет разгруппировывать листы, активируя тот лист, на котором вы в данный момент находитесь. Прелесть этого трюка в том, что вручную группировать листы не нужно, и нет риска забыть разгруппировать их.

Вы также можете сделать так, чтобы данные появлялись на других листах, но не в ячейках с теми же адресами. Для этого я определил новый диапазон на Лист6 – Пример2 (В2:Н11). Далее записал следующий код в частный модуль объекта Лист6:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Range( " Пример2 " ), Target) Is Nothing Then

With Range( " Пример2 " )

.Copy Destination: =Sheets ( " Лист4 " ).Range ( " A1 " )

.Copy Destination:=Sheets( " Лист5 " ).Range( " D10 " )

Этот код левому верхнему углу диапазона на Лист 6 – В2 поставит в соответствие диапазон на Лист4 с вершиной в А1 и на Лист5 – с вершиной D10. Вводя на Лист6 значения в ячейки диапазона Пример2 (В2:Н11), вы получите аналогичные значения в диапазонах на Лист4 (А1:G10) и Лист5 (D10:J19).

Часто бывает так, что одни и те же данные одновременно содержатся в нескольких рабочих листах. Можно применить инструмент группировки Excel, чтобы данные в одной рабочей книге можно было одновременно вводить в несколько рабочих листов. Кроме того, мы предлагаем более быстрый и гибкий подход, в котором используется пара строчек кода VBA (Visual Basic for Applications).

Встроенный механизм Excel для одновременного размещения данных в нескольких листах — это средство под названием Группа (Group). Оно позволяет группировать листы, связывая их внутри рабочей книги.

Группировка рабочих листов вручную

Чтобы применить средство Группа (Group) вручную, щелкните лист, на котором будете вводить данные, а затем, удерживая клавишу Ctrl (Shift на Macintosh), щелкните ярлычки с именами рабочих листов, куда должны одновременно вводиться те же данные. Когда вы будете вводить данные в любую ячейку на вашем рабочем листе, они автоматически появятся и на остальных сгруппированных листах. Миссия окончена.

Чтобы отменить группировку листов, либо выберите любой лист, не включенный в группировку, либо щелкните правой кнопкой мыши любой ярлычок листа и выберите команду Разгруппировать листы (Ungroup Sheets).

Автоматическая группировка рабочих листов

Эти трудности можно преодолеть при помощи очень простого кода VBA. Чтобы этот код заработал, он должен находиться в частном модуле (private module) для объекта Sheet (Лист). Чтобы быстро перейти в частный модуль, правой кнопкой мыши щелкните ярлычок листа и в контекстном меню выберите команду Исходный текст (View Code) . Затем для помещения кода в частный модуль для объекта Лист (Sheet) вы сможете использовать одно из событий листа Excel, то есть событий, которые происходят в вашем листе, например, изменение ячейки, выбор диапазона, активация, деактивация и так далее.

// Листинг 1.1 Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Range("MyRange"), Target) Is Nothing Then Sheets(Array("Sheet5", "Sheet3", "Sheet1")).Select Else Me.Select End If End Sub

В этом коде мы использовали имя диапазона MyRange. (Если вы не знакомы с именованными диапазонами, обратитесь к разделу «Адресация данных по имени» [Трюк № 39].) Измените MyRange на имя диапазона, которое вы используете в своем листе. Кроме того, измените в коде три имени листов на имена листов, которые хотите сгруппировать (рис. 1.5). Закончив, закройте окно модуля или нажмите сочетание клавиш Alt/Apple+Q, чтобы вернуться в Excel.

Рис. 1.5. Код автоматической группировки рабочих листов

Важно помнить, что первым в этом массиве должно стоять имя листа, содержащего этот код, то есть листа, на котором вы будете вводить данные. После того как код создан, каждый раз, когда вы будете выбирать ячейку на рабочем листе, код будет проверять, принадлежит ли выбранная ячейка диапазону с именем MyRange. Если да, то код будет автоматически группировать нужные рабочие листы. Если нет, он будет разгруппировывать листы, активируя тот лист, на котором вы в данный момент находитесь. Прелесть этого трюка в том, что вручную группировать листы не нужно, и нет риска забыть разгруппировать их. Этот подход может сэкономить много времени и усилий.

Если вы хотите, чтобы те же данные появлялись на других листах, но не в ячейках с теми же адресами, используйте код из листинга 1.2.

// Листинг 1.2 Private Sub worksheet_Change(ByVal Target As Range) If Not Intersect(Range("MyRange"), Target) Is Nothing Then With Range("MyRange") .Copy Destination:=Sheets("Sheet3").Range("A1") .Copy Destination:=Sheets("Sheet1").Range("D10") End With End If End Sub

Этот код также необходимо записать в частный модуль объекта Лист (Sheet). Чтобы раскрыть его, выполните действия, описанные ранее в этом трюке.

Формулы с использованием ссылок на другие листы Excel

Для начала рассмотрим пример работы с листами в формулах Excel. Пример 1:

Создадим на листе 1 рабочей книги табличного процессора Excel таблицу, как показано на рисунке. Сведения о среднемесячной заработной плате:
Далее на листе 2 рабочей книги подготовим область для размещения нашего результата - размера нашей премии в рублях, как показано на рисунке:
Далее нам необходимо будет ввести в ячейку В2 формулу, изображенную на рисунке ниже:

Ввод данной формулы происходил следующим образом: для начала в ячейке В2 мы установили знак "=", затем кликнули на "Лист1" в нижнем левом углу рабочей книги и перешли на ячейку C3 на листе 1, далее ввели операцию умножения и перешли вновь на "Лист2" для того чтобы добавить процент.

Функция ЛИСТЫ для подсчета количества листов в рабочей книге

На рисунке ниже показано примерное количество листов:

В результате получим следующее значение: 12 листов.

Ссылки на другие листы в шаблонах документов

Создадим таблицу "Банкетное меню", общий вид которой представлен на рисунке ниже:
Аналогичным образом создадим таблицы на разных листах "Общая стоимость" и "Общий выход":
При помощи формулы со ссылками на другие листы произведем расчет общей стоимости банкетного меню:
Перейдем на лист "Общий выход" и путем умножения ячеек веса одной порции, находящегося на листе 2 и общего количества, находящегося на листе 1 произведем расчет общего выхода:

В результате у нас получился простейший шаблон для подсчета расходов на 1 банкет.

Изучим возможности для суммирования данных из ячеек с разных листов книги с помощью использования трехмерных ссылок в Excel.

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

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

Как сложить листы в Excel?

Вариант 1. Ручной ввод.

Начнем с одной стороны с медленного варианта, но с другой стороны с более понятного и наглядного способа.

Это совсем простой вариант. Еще один вариант записи можно аналогично реализовать с помощью функции СУММ:

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

Как это часто бывает если нужно посчитать сумму для двух или трех листов, то вышеописанный способ отлично подойдет. Но если усложнить задачу и представить подобный пример не для 3, а, например, для 30 листов, то процесс уже несколько затянется.

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

Вариант 2. Полуавтоматический ввод.

Повторно воспользуемся функцией СУММ, но в этот раз запишем ссылку на листы через двоеточие:

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

Поэтому запись для сложения 10 или 20 листов будет примерно такой же — ИмяПервогоЛиста:ИмяПоследнегоЛиста!Диапазон.

Чуть более подробно остановимся на логике формирования формулы. Напомню, что оператор двоеточие в Excel служит для объединения диапазона, который образует ссылку на все ячейки находящиеся между первой и последней (включая сами ячейки). Например, диапазон A1:A10 обозначает двумерную ссылку, в которую попадают все ячейки между первой (A1) и последней (A10).

При добавлении еще одной размерности в виде листов мы получаем так называемую трехмерную ссылку. К примеру, диапазон Лист1:Лист3!A1:A10 содержит все ячейки от Лист1!A1:A10 до Лист3!A1:A10.

Таким образом в нашем примере суммируются все ячейки A1 от Лист1 до Лист3. Но при применении такого способа суммирования есть некоторые тонкости, которые желательно нужно помнить, чтобы случайно не допустить ошибку, давайте подробнее про них поговорим.

Особенности трехмерных ссылок

Как мы уже говорили ранее, при применении трехмерных ссылок нужен только первый и последний лист, промежуточные листы между ними в записи никак не участвуют. Отсюда и возникают следующие тонкости:

Добавление/копирование листа. Если добавить новый лист или скопировать уже существующий и вставить его между первым и последним (используемых в формуле), то он автоматически будет участвовать в расчете.
Т.е. сама формула не поменяется, а значение вполне может измениться, если на новом листе в тех же ячейках содержатся данные;
Удаление листа. Аналогичный случай примеру выше. При удалении листа формула не изменится, а расчет поменяется в зависимости от удаленных данных (если удалить начальный или конечный листы, то формула изменится, промежуточный — формула останется неизменной);
Перемещение листа. При перемещении листа в другое место книги формула не изменится, но поменяется набор листов между ними (либо добавятся новые, либо удалятся текущие).

Если описание покажется несколько сложным, в этом случае попробуйте поэкспериментировать с формулой и перестановкой листов на конкретном примере и поймите по какому принципу она работает.

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

Спасибо за внимание!
Если у вас есть мысли или вопросы по теме статьи — пишите и спрашивайте в комментариях.

Читайте также: