Связанными называются таблицы, в которых данные из одних таблиц используются в других таблицах или участвуют в вычислениях, производимых в других таблицах. Связь устанавливается в результате задания внешней ссылки на другие листы той же или другой рабочей книги. Связь играет большую роль в тех случаях, когда значения одной таблицы зависят от значений в другой таблице. При изменении значений в одной таблице изменяются и значения в тех ячейках, которые содержат ссылки на эту таблицу, т.е. речь идет о динамической связи. Внешняя ссылка на ячейку, находящуюся на другом листе этой же рабочей книги состоит из имени листа и адреса ячейки, разделенных восклицательным знаком:
Имя_листа!Адрес_ячейки
Например, Товары!В5. Ссылка на ячейку, находящуюся в другой книге имеет вид:
[Имя_книги]Имя_листа!Адрес_ячейки
Рассмотрим порядок подготовки ведомости реализации товаров, в которой, используя данные таблиц на листах Товары и Цены, необходимо вычислить:
· сумму реализации каждого товара;
· сумму остатка каждого товара;
· общие суммы реализации и остатка материалов.
1. Откройте рабочую книгу УЧЕТ.XLS.
2. Перейдите на новый лист и дайте ему имя Реализация.
Примечание. Вставить новый лист можно командой Вставка►Лист.
3. Подготовьте таблицу, представленную на рис. 5.24:
· выделите ячейки А1:С9 и установите для них размер шрифта – 12 пт;
· объедините ячейки А1:С1 и введите заголовок таблицы Ведомость реализации;
· Оформите шапку таблицы. Для этого выделите ячейки А3:С3 и установите для них в окне Формат ячеек на вкладке Выравнивание флажок Переносить по словам. В ячейки А3, В3 и С3 введите соответствующие заголовки. Если необходимо, увеличьте ширину столбцов вручную.
4. Заполните столбец Код материала, используя значения соответствующих ячеек листа Товары,установив ссылки на ячейки B4:B8 листа Товары. Для этого выполните действия:
· на листе Реализация активизируйте ячейку А4 и введите знак "=" для определения формулы;
· перейдите на лист Товары. Ярлык листа Реализация выделится подсветкой, а в строке формул появится ссылка на лист-источник в следующем виде: =Товары!
· выполните щелчок левой клавишей мыши на ячейке В4 (в строке формул появится ссылка на лист-источник в следующем виде: =Товары!B4)
· нажмите клавишу [Enter], не переходя на лист Реализация;
· в результате на листе Реализация в ячейке А4 отобразится код первого товара – 101;
· скопируйте формулу из ячейки А4 в ячейки А5:А8.
Примечание. Если нужная информация находится в другой книге (например в книге с именем Отчет, на листе Ведомость, в ячейке В4), то необходимо перейти в нее с помощью команды меню Окно (книга Отчет должна быть заранее открыта),затем перейти на лист Ведомость и выделить ячейку В5. В строке формул появится ссылка вида: =[Отчет.xls]Ведомость!В4
5. Рассчитайте суммы реализации товаров путем перемножения значений столбца Расход (лист Товары) на значения столбца Цена розничная (лист Цены). Введите формулы для вычисления сумм реализации товаров. Для этого выполните следующую последовательность действий:
· на листе Реализация, активизируйте ячейку В4 и введите знак "=";
· перейдите на лист Товары, выполните щелчок на ячейке Е4 и введите с клавиатуры знак умножения «*»;
· перейдите на лист Цены, щелкните на ячейке D4; нажмите [Enter];
· В результате на листе Реализация в ячейке В4 появится значение, рассчитанное по формуле:
=Товары!E4*Цены!D4
· скопируйте формулу из ячейки В4 в ячейки В5:В8.
6. Рассчитайте суммы остатка товаров. Для этого выполните следующую последовательность действий:
· на листе Реализация активизируйте ячейку С4 и введите знак "=";
· перейдите на лист Товары, выполните щелчок на ячейке F4 и введите с клавиатуры знак умножения «*»;
· перейдите на лист Цены, щелкните на ячейке D4; нажмите [Enter];
· В ячейке С4 листа Реализация появится значение, рассчитанное по формуле:
=Цены!D4*Товары!F4
· скопируйте формулу из ячейки С4 в ячейки С5:С8.
7. Рассчитайте итоговые значения столбцов и поместите результаты в ячейки В9 и С9.
8. Перейдите в режим формул данной таблицы (Сервис►Параметры►вкладкаВид►установитефлажокФормулы). Сравните результат с таблицей, представленной на рис. 5.25.
Рис.5.25. таблица Ведомость реализации в режиме формул
За каждой кнопкой панелей инструментов закреплена небольшая программа – макрос. Макрос представляет собой последовательность макрокоманд и макрофункций. Excel предоставляет пользователю возможность создавать собственные макросы с использованием языка VBA или макрорекордера.
Наиболее простым способом создания макрокоманды является ее запись с помощью макрорекордера. Это средство позволяет автоматизировать выполнение часто повторяющихся последовательностей рабочих шагов и при этом не требует знаний макропрограммирования. Макрорекордер протоколирует все выполняемые пользователем действия и сам преобразует их в VBA-код.
Рассмотрим порядок создания и запуска макроса, который должен для любого выделенного блока ячеек выполнять следующую последовательность действий:
· устанавливать для шрифта размер– 16 пт, цвет– синий;
· задавать внешние границы для блока толстой синей линией;
· присваивать ячейкам денежный формат с отображением двух знаков после запятой.
1. Активизируйте новый пустой рабочий лист.
2. ВыполнитекомандуСервис► Макрос► Начать запись.
3. В открывшемся диалоговом окне Запись макроса(рис. 5.26) введите имя макроса – Пробный.
Примечание. Имя макроса должно не содержать пробелы и не превышать 255 символов. По желанию можно внести описание макроса, задать комбинацию «горячих» клавиш для быстрого вызова макроса в поле Сочетание клавиш и в поле Сохранить в указать, где будет сохранен созданный макрос.
4. Для запуска процедуры записи щелкните на кнопке [ОК]. На экран будет выведена панель инструментов Остановить запись, представленная на рис. 5.27. Панель содержит две кнопки: [Остановить запись] и [Относительная ссылка].
Примечание. Если панель Остановить запись не появилась, то ее можно отобразить командой Вид►Панели инструментов►Остановить запись.
5. Выполните щелчок на кнопке [Относительная ссылка] для того, чтобы макрос в дальнейшем выполнялся для любого выделенного блока ячеек, а не той ячейки, которая была активна при записи макроса.
· измените цвет шрифта на синий, размер шрифта – 16 пт;
· перейдите на вкладку Граница, выберите заданный тип и синий цвет линии, щелкните на кнопке [Внешние];
· перейдите на вкладкуЧисло, в списке Числовые форматы выберите Денежный, число десятичных знаков – 2.Нажмите [ОК]
7. Завершите запись щелчком по кнопке [Остановить запись] или выполнив команду меню Сервис►Макрос►Остановить запись.
8. Проверьте работу макроса. Для этого:
· активизируйте новый лист и выделите, например, ячейки В2:D8;
· введите в ячейки В2, С6 произвольные данные;
· выполните команду Сервис►Макрос►Макросы. В открывшемся диалоговом окне Макрос выберите макрос Пробный и нажмите [Выполнить].
Для более быстрого запуска макрос также можно назначить пиктограмме на панели инструментов или пункту в главном меню.
Рассмотрим алгоритм назначения макроса кнопке на панели инструментов на примере инсталляции макроса Пробный в виде пиктограммы красного цвета на панель инструментов Форматированиеи запуска макроса при помощи пиктограммы.
1. Откройте окно Настройка командой Вид►Панели инструментов► Настройка►вкладка Команды.
2. Всписке Категории выберите – Макросы, а в списке Команды выделите пункт Настраиваемая кнопка и перетащите его при помощи левой кнопки мыши на панель инструментов, разместив, например, после кнопки К [курсив]. На панели инструментов появится новая кнопка (обычно это кнопка с изображением улыбающегося смайлика).
3. Назначьте кнопке макрос, для чего в окне Настройка нажмите кнопку [Изменить выделенный объект] (новая кнопка на панели инструментов должна быть выделена) и в открывшемся меню выберите пункт Назначить макрос. В окне Назначить макрос выделите макрос с именем Пробный и нажмите [ОК].
4. Представьте кнопку без текста. Снова активизируйте кнопку [Изменить выделенный объект] и выберите пункт – Основной стиль.
5. Измените значок на кнопке. Для этого нажмите кнопку [Изменить выделенный объект] и выберите пункт Изменить значок на кнопке. В окне Редактор кнопок в группе Цвета выберите нужный цвет, а в поле Рисунок разукрасьте кнопку при помощи щелчков мыши на каждом квадратике. Нажмите [ОК]. Закройте окно Настройка.
6. Проверьте работу новой кнопки на ячейках H2:I6, введя в них произвольные данные.
Для удаления макроса используется кнопка [Удалить] в окне Макросы. Кнопку с панели инструментов можно удалить, отбуксировав ее в любое место окна Настройка.
Заполните столбец Код материала, используя значения соответствующих ячеек листа Товары,установив ссылки на ячейки B4:B8 листа Товары. Для этого выполните действия:
Рис.5.25. таблица Ведомость реализации в режиме формул
Активизируйте новый пустой рабочий лист.
Для запуска процедуры записи щелкните на кнопке [ОК]. На экран будет выведена панель инструментов Остановить запись, представленная на рис. 5.27. Панель содержит две кнопки: [Остановить запись] и [Относительная ссылка].
для того, чтобы макрос в дальнейшем выполнялся для любого выделенного блока ячеек, а не той ячейки, которая была активна при записи макроса.
или выполнив команду меню Сервис►Макрос►Остановить запись.