В прошлой статье мы научились правильно ссылаться на другие ячейки в рамках одного листа Excel. Сегодня рассмотрим, как делать ссылки на другие листы и книги. Без лишних слов, приступим.
Ссылки на другие листы
Когда мы в формуле ссылаемся на ячейку из другого листа, то Excel делает всю работу за нас и правильно оформляет ссылку. Но, несмотря на это, мы должны владеть синтаксисом таких ссылок, если хотим быть продвинутыми пользователями Excel и свободно оперировать всеми его возможностями.
Мы продаем товары в разные города. У нас есть статистика продаж каждого товара по городам и для каждого города она находится в соответствующем листе Excel. И есть лист "Отчёт", где мы должны суммировать продажи всех товаров во все города. Скачать файл с примером можете по ссылке.
Если бы мы сослались на ячейку A1 листа с названием [Москва], то синтаксис был бы следующим:
Москва!A1
Восклицательный знак (!) всегда следует за названием лист и отделяет его от адреса ячейки.
Одинарные кавычки используются для заключения названия листа, если оно содержит пробелы. Так, если вы ссылаетесь на ячейку A1 в листе с названием [Нижний Новгород], то синтаксис будет таким:
'Нижний Новгород'!A1
Давайте теперь используем функцию СУММ, чтобы просуммировать данные из разных листов. Как вы видели на скриншоте у нас есть данные на четырёх листах: [Москва], [Нижний Новгород], [Рязань] и [Омск]. на каждом из этих листов данные располагаются в диапазоне B2:B7.
В ячейке B3 листа [Отчёт] мы должны написать формулу:
=СУММ(Москва!B2:B7;'Нижний Новгород'!B2:B7;Рязань!B2:B7;Омск!B2:B7)
Она суммирует значения диапазона B2:B7 на каждом листе. Как видим, она использует синтаксис, который мы обсуждали ранее, включая восклицательные знаки и одинарные кавычки вокруг листа [Нижний Новгород].
В этом примере точка с запятой используется для разделения каждого диапазона, используемого функцией СУММ.
На каждом листе эти диапазоны данных одинаковы, благодаря этому формулу можно было бы упростить.
Следующая формула суммирует те же значения, но использует оператор диапазона для суммирования значений в диапазонах B2:B7 начиная с листа [Москва] и заканчивая листом [Омск], потому что эти листы являются первым и последним листами в диапазоне:
=СУММ(Москва:Омск!B2:B7)
Эта формула более лаконична, чем предыдущая. Она также даёт нам то преимущество, что если мы включим новый лист в диапазон (между [Москвой] и [Омском]), то данные из него также будут включены в сумму.
В этом примере использование листов на первом и последнем месте с названием [Москва] и [Омск] довольно произвольно. Мы могли бы создать листы с названиями [Начало] и [Конец]. они были бы совершенно пустыми и не имели бы никакой функции, кроме как содержать внутри диапазона листы для суммирования. И внутри мы могли бы добавлять, удалять и перемещать листы без опаски нарушить формулу.
Ссылки на другие книги
Сразу следует сказать, что книгами в Exel называются отдельные файлы. Как и в случае с листами, Excel сам оформит ссылку за вас в формуле, когда вы выберете ячейку в другой книге. Однако, нам также очень важно знать правила оформления таких ссылок.
Если вы будете ссылаться в своей формуле на ячейку A1 на листе с названием [Отчёт] книги с названием [Продажи.xlsx], то синтаксис будет выглядеть следующим образом:
='[Продажи.xlsx]Отчет'!$A$1
Если книга [Продажи.xlsx ] будет закрыта, тогда синтаксис формулы изменится, чтобы показать полный путь к файлу:
='C:\Users\Администратор\Desktop\[Продажи.xlsx]Отчет'!$A$1
Давайте посмотрим на примере. Для этого нам понадобится второй файл (скачать по ссылке) с названием [Москва.xlsx], в которой есть лист [Прошлый год].
Нам нужно узнать насколько изменились продажи в Москве по сравнению с прошлым годом. Для этого в первой книге в ячейке D3 листа [Отчет] введём формулу
=СУММ(Москва!$B$2:$B$7)-СУММ('[Москва.xlsx]Прошлый год'!$B$2:$B$7)
Она вычисляет разницу между суммой значений на листе [Москва] текущей книги и суммой значений на листе [Прошлый год] книги [Москва.xlsx ].
У нас получилось, что в этом году в Москве мы продали на 222 товара меньше, чем в прошлом.
Стоит заметить, что многие функции в Excel способны извлекать данные из закрытой книги, например, такие как СУММ, СРЗНАЧ и ИНДЕКС, в то время как некоторые другие (СУММЕСЛИМН и СМЕЩ) этого не могут.
А теперь сделайте такую вещь: сохраните и закройте обе книги, а потом снова откройте первую, где находятся ссылки на ячейки из другой книги. Когда вы открываете файл, содержащий ссылки на другие книги, и, скорее всего, выдаст вам сообщение о безопасности.
Вы можете изменить эти настройки, нажав Файл ➤ Параметры ➤ Центр управления безопасностью ➤ Параметры центра управления безопасностью ➤ Внешнее содержимое
По умолчанию запрашивается обновление у пользователя, поэтому вы получаете сообщение о безопасности. Это можно изменить, включив или отключив эти обновления.
Также вы можете открыть окно редактирования ссылок, доступ к которому осуществляется нажатием кнопки Данные ➤ Изменить связи.
В этом окне вы можете просматривать, обновлять и редактировать внешние ссылки в книге.
В окне Изменение связей перечислены все внешние ссылки в книге. Вы можете видеть расположение внешних файлов, а справа есть несколько элементов управления для обновления, изменения и разрыва ссылок.
При разрыве ссылки на внешний источник формула преобразуется в вычисленное значение на момент разрыва ссылки.
Вот мы и научились правильно оформлять ссылки на другие листы и книги Excel. В следующей статье мы научимся всем способам вычисления процентов.
Не переключайтесь;)