4.8.1. Ссылка на другие листы
Посмотрим, как ввести в формулу адрес ячейки, хранящейся на другом листе. Для этого в книгу Мои таблицы.xls добавьте лист Налоги. На нем в ячейку А1 введите слово НДС, а в ячейку В1: 20%. В ячейку А2 введите НсП, а в ячейку В2: 2%. Вернитесь на лист Услуги и очистите содержимое ячеек В8:Н8.
Затем:
Активизируйте ячейку В8.
Напечатайте знак равенства, щелкните по ячейке В6 и напечатайте знак умножения.
Щелкните по ярлычку листа Налоги, а затем по ячейке В1. Обратите внимание: часть ссылки, указывающая на лист, отделена восклицательным знаком от ссылки на ячейку, и ссылка на ячейку является относительной (рис.4.2).
Рис.4.2. Так записывается адрес ячейки, расположенной на другом листе
Нажмите клавишу [Enter] (или щелкните мышкой по зеленой галочке). Ввод формулы завершен, вы автоматически возвращаетесь на лист Услуги.
С помощью маркера заполнения скопируйте исходную формулу в ячейки С8:H8.
Если получились нули, исправьте ошибку в исходной формуле и проделайте предыдущую операцию еще раз. Объясните причины возникновения ошибки.
Таким же образом, самостоятельно рассчитайте налог с продаж.
Введите новое значение НДС, равное 15%, и посмотрите, как изменятся расчетные данные.
ЗАМЕЧАНИЕ: при переименовании листа автоматически изменяется и ссылка на него.
4.8.2. Ссылка на другие книги
Теперь познакомимся со ссылками на ячейки, расположенными в другой книге. Для этого откройте новую книгу. Скопируйте в неё лист Налоги и сохраните под именем Нормативы.xls.
Вернитесь в книгу Мои таблицы.xls на лист Услуги и очистите содержимое ячеек В8:H8. Далее выполните пункты первые два пункта предыдущей инструкции, а затем, после того как вы напечатали знак умножения, с помощью меню Окно перейдите в книгу Нормативы.xls на лист Налоги и щелкните по ячейке В1. В строке формул появится следующая формула (рис.4.3): =B6*[Нормативы.xls]Налоги!$B$1.
Ссылка [Нормативы.xls]Налоги!$B$1 называется внешней ссылкой. ОБРАТИТЕ ВНИМАНИЕ: часть ссылки, указывающая на книгу, заключена в квадратные скобки, и ссылка на ячейку в этом случае является абсолютной.
Рис.4.3. Адресация ячейки, расположенной в другой книге
Нажав клавишу [Enter], завершите ввод формулы. Excel автоматически возвращает нас в книгу Мои таблицы.xls на лист Услуги. Воспользовавшись маркером заполнения, скопируйте исходную формулу в остальные ячейки. Сохраните книгу Мои таблицы.xls.
С помощью формул, содержащих внешние ссылки, между книгами Нормативы.xls (исходная книга) и Мои таблицы.xls (зависимая книга) теперь установлена динамическая связь. Это значит, что при изменении данных в исходной книге возможен автоматический пересчет формул в зависимых книгах.
4.8.3. Открытие зависимых книг
При сохранении зависимых книг в них сохраняются самые последние результаты расчетов. При открытии зависимой книги Excel уведомит вас о том, что данная книга содержит связи с другой книгой и спросит вас, не следует ли их обновить (рис.4.4).
Рис.4.4. Открытие зависимой книги
Если вы нажмете кнопку Нет, Excel откроет зависимую книгу без обновления ссылок на исходные книги. Если вы нажмете кнопку Да, Excel будет искать исходные книги и, если найдет их, то заново прочитает исходные значения и обновит зависимые формулы. Исходная книга при этом не открывается!
Задание: закройте книгу Мои таблицы.xls28; в книге Нормативы.xls введите новое значение НДС = 22% и закройте ее, сохранив внесенные изменения. Откройте книгу Мои таблицы.xls, ответив Нет на запрос об обновлении связей, – в таблице останутся прежние числа. Закройте ее и откройте заново, ответив теперь Да, – суммы налогов и числа Всего: изменятся.
Обратите внимание на синтаксис внешних ссылок в формулах при закрытом исходном файле (=B6*'C:\ST\[Нормативы.xls]Налоги'!$B$1): часть ссылки, указывающая на книгу и лист, заключена в одинарные кавычки. Кроме того, в ссылке появился путь каталога (ST), в котором хранится исходная книга.
В случае если исходный файл не будет найден (например, вы его переименовали, удалили или переместили в другую папку), на экране появится диалоговое окно с заголовком Файл не найден (рис 4.5), с помощью которого вы можете указать новый файл, из которого следует взять исходные значения. Однако имейте в виду, что внешняя ссылка в зависимой книге не изменяется!
Рис.4.5. С помощью этого окна можно сказать Excel, откуда ему следует взять новые значения внешних ссылок
Рассмотрим конкретный пример. В файле Нормативы.xls введите новое значение НДС = 25%, сохраните и закройте его. С помощью программы Проводник или Мой компьютер переименуйте его в Новые нормативы.xls. При открытии книги Мои таблицы.xls ответьте Да на запрос об обновлении связей. Excel, не найдя файл Нормативы.xls, выведет на экран окно (рис.4.5), в котором попросит указать файл, из которого следует взять значения налогов. Если мы укажем файл Новые нормативы.xls, то произойдет пересчет таблицы, однако ссылки в формулах не изменятся, – как в них были внешние ссылки на книгу Нормативы.xls, так они и остались. О том, как изменить внешние ссылки, вы узнаете из следующего параграфа.