Табличный процессор Excel в качестве приемника связей.

2.3.2.1. Откройте окно для нового документа Word. Создайте и оформите в нем три таблицы, приведенные на рис.13.8. В книге Имя_13_1 откройте новый рабочий лист. Внедрите на этот лист (в область с ячейки А1...), установив связь с источником данных, первую (верхнюю) таблицу из документа Word как "Текст". Для выполнения связывания выполните традиционную последовательность действий:

· скопируйте таблицу-источник в документе Word в буфер редактирования;

· перейдите в приложение-приемник (Excel);

· выполните команду Главная – Буфер обмена - Вставить – Специальная вставка, выбрав тип данных "Текст";

· включите режим установления связи (переключатель "Связать").

2.3.2.2. Внедрите на рабочий лист (диапазон с А6...), установив связь с источником данных, вторую (среднюю) таблицу из документа Word как "Объект Документ Microsoft Word".

2.3.2.3. Внедрите на рабочий лист (диапазон с А10...), установив связь с источником данных, третью таблицу из документа Word как "Объект Документ Microsoft Word".

2.3.2.4. Поочередно изменяя значения данных в ячейках трех исходных таблиц, пронаблюдайте, как меняются данные в таблицах приложения-приемника.

Управление установленными связями.

2.3.3.1. Активизируйте команду Office - Параметры Excel – Дополнительно – При пересчете этой книги и проверьте установку флажка Сохранять значения внешних связей.

Рис.13.9

Динамический обмен данными.

Excel в качестве источника и приемника данных.

2.4.1.1. Откройте окно нового документа Word. Создайте в этом окне таблицу, приведенную на рис.13.9. В этой таблице столбец "К выдаче" и значение суммы не заполнять.

2.4.1.2. Откройте новый лист в окне Excel. Пользуясь механизмом связывания, создайте в ячейках А1:D4 таблицу на основе исходной таблицы из Word. (Главная – Буфер обмена - Вставить – Специальная вставка – Текст и переключатель "Связать").

2.4.1.3. Дополните таблицу необходимым столбцом Е, записав в него соответствующие формулы. В ячейках Е2:Е4 запишите разности данных в столбцах С и D ("Начислено" - "Удержано"), а в ячейке Е5 - формулу для вычисления общей суммы, назначенной к выдаче по всем лицам.

2.4.1.4. Установите индивидуальные связи для ячеек столбца "К выдаче" и строки "Сумма" с соответствующими ячейками таблицы Word. Структура описанных связей представлена на рис.13.9. Просмотрите установленные связи, пользуясь командой Данные – Изменить связь.

2.4.1.5. Изменяя исходные данные в таблице Word, пронаблюдайте, как работают каналы динамической связи приложений, и как, соответственно, меняются данные в таблице Excel, и как они вновь передаются в Word.

Обмен графическими объектами.

2.4.2.1. Дополните таблицу, созданную ранее в рабочем листе Excel, графиком (3-х мерная столбиковая диаграмма), созданным на основе данных таблицы, приведенной на рис.13.10.

Рис.13.10

2.4.2.2. Пользуясь механизмом связывания объектов, представьте данный график в документе Word. Обратите внимание, как меняется диалог, в котором от пользователя запрашивается тип связываемых данных.

2.4.2.3. Изменяя числовые данные в исходной таблице Word, пронаблюдайте, как меняются данные и графики в листе Excel, и как они передаются обратно в документ Word.

Задание № 14 (Часть 1). Работа с макросами.

Цель: Знакомство со способами создания и использования макросов в MS Excel.

Темы: Создание и выполнение макросов. Макросы с относительными ссылками. Вложенные процедуры. Создание и применение пользовательских функций. Работа с модулем.

1. Создание и выполнение макросов.

1.1. Активизируйте вкладку «Разработчик», если она не представлена среди других ленточных вкладок. Для этого используйте команду Office – Параметры Excel – Основные - Показывать вкладку «Разработчик» на ленте.

1.2. Пользуясь командой Разработчик – Код – Запись макроса, задайте для создаваемого макроса имя "СтарыйАдрес", сочетание клавиш, сохранение в «этой книге» и сведения об авторе макроса в разделе описания (рис.14.1).

1.3. Начните запись макроса, позволяющего выполнить ввод в ячейки А5, А6, А7 следующий текст: А5: ПТС

А6: Ул. Большая Морская, д.3

А7: г. Санкт - Петербург.

(В процессе записи макроса старайтесь избегать ошибок и исправлений!)

1.4. Завершите запись макроса, воспользовавшись командой Разработчик – Код – Остановить запись или нажав соответствующую кнопку в строке состояния.

Рис.14.1

1.5. Очистите рабочий лист и перейдите в ячейку A5. Выполните созданный макрос, воспользовавшись командой Разработчик – Код – Макросы - Выполнить.

1.6. Снова очистите рабочий лист и повторите выполнение макроса, предварительно перейдя в любую другую ячейку.

1.7. Сравните результаты выполнения макроса в п.п. 1.5 и 1.6.

1.8. Очистите рабочий лист и запустите макрос, пользуясь сочетанием клавиш, которые были назначены при записи макроса. Если они не были назначены, то сделайте это с помощью команды Разработчик – Код – Макросы – Параметры.

1.9. Просмотрите содержимое макроса в редакторе Visual Basic, воспользовавшись командой Разработчик – Код – Visual Basic или командой Разработчик – Код – Макросы – Изменить или сочетанием клавиш Alt+F11 (Рис.14.2)

Рис.14.2

1.10. Измените текст в Module1, заменив номер дома в адресе ("3" замените на "3/5", вручную записав новый текст).

1.11. Вернитесь в окно Excel с помощью команды редактора Visual Basic File – Close and Return to Microsoft Excel или сочетания клавиш Alt+F11, выполните макрос "СтарыйАдрес" и убедитесь в его работоспособности.

2. Создание и выполнение макросов с относительными ссылками.

2.1. Создайте новый макрос под именем "НовыйАдрес" в режиме создания макроса с относительными ссылками.

2.1.1. Очистите рабочий лист ("Лист1").

2.1.2. Пользуясь командой Разработчик – Код – Запись макроса, активизируйте диалоговое окно Запись макроса, задайте для создаваемого макроса имя "НовыйАдрес" и заполните остальные поля.

2.1.3. Включите режим использования относительных ссылок, воспользовавшись командой Разработчик – Код – Относительные ссылки.

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

ГУТ им. проф М А Бонч-Бруевича

г. Санкт-Петербург,

наб. реки Мойки, д.61

2.1.5. Закончите запись макроса нажатием соответствующей кнопки.

2.2. Перейдите в редактор Visual Basic и просмотрите содержимое макроса "Новый Адрес" (рис.14.3). Сравните тексты макросов "СтарыйАдрес" и "НовыйАдрес".

2.3. Вернитесь в окно Excel, поочередно выполните макросы "СтарыйАдрес" и "Новый Адрес". Отметьте различия в выполнении макросов.

2.4. Выполните оба макроса, пользуясь сочетаниями клавиш, назначенными при записи макросов. Если они не были назначены, то сделайте это с помощью команды Разработчик – Код – Макросы – Параметры.

 

Рис.14.3

3. Создание и использование вложенных процедур.

3.1. Откройте редактор Visual Basic (лист "Модуль1"). Внесите в программный код VBA изменения, показанные на рис.14.4.

Рис.14.4

В результате должна быть создана новая процедура, названная "Formatting". Макрос "СтарыйАдрес" вызывает процедуру "Formatting" в третьей строке программного кода. В процедуре "Formatting" использована функция MsgBox (вывод окна сообщения), которая позволяет приостановить выполнение процедуры "Formatting" до нажатия кнопки ОК и выдать подходящее случаю сообщение.

3.2. Вернитесь на лист Excel и выполните макрос "СтарыйАдрес".

3.3. Измените текст процедуры "Formatting" следующим образом:

· установите размер шрифта 16 пт.;

· установите зеленый цвет шрифта (индекс зеленого цвета - 50);

· уберите подчеркивание текста.

3.4. Выполните макрос "СтарыйАдрес", внимательно проследив за правильностью его выполнения с учетом сделанных изменений.

3.5. Внесите изменения в текст макроса "СтарыйАдрес" таким образом, чтобы процедура "Formatting" действовала на ячейку A7.

3.6. Внесите изменения в текст макроса "СтарыйАдрес" таким образом, чтобы процедура "Formatting" действовала на ячейки A5, A6, A7.

3.7. Внесите изменения в текст макроса "Новый Адрес" таким образом, чтобы выполнение макроса "НовыйАдрес" начиналось с процедуры "Formatting".

3.8. Проследите за правильностью выполнения макросов "СтарыйАдрес" и "НовыйАдрес" с учетом сделанных в п.п. 3.5 - 3.7 изменений.

4. Создание и применение пользовательских функций.

4.1. Создайте таблицу, приведенную на рис.14.5, не заполняя ячейки С2:С4.

Рис.14.5

4.2. Откройте редактор Visual Basic (Alt+F11).

4.3. Вставьте новый лист модуля (Insert – Module).

4.4. Введите в лист модуля текст функции, позволяющей вычислить размер надбавки в зависимости от количества продаваемого товара. Текст такой функции приведен на рис.14.6.

Рис.14.6

4.5. Перейдите на лист Excel, содержащий таблицу (рис.14.5). Введите вручную в ячейку С2 функцию "Надбавка(количество, цена)", используя в качестве аргументов функции ссылки на ячейки А2 и В2.

4.6. В ячейку С3 введите формулу для вычисления надбавки, пользуясь Мастером функций (категория Определенные пользователем).

4.7. В ячейку С4 введите формулу для вычисления надбавки, пользуясь копированием формул.

4.8. Изменяя данные в ячейках А2:А4, убедитесь в правильности вычислений, производимых созданной функцией.

Задание № 14 (Часть 2). Настройка интерфейса.

Цель: Знакомство со способами создания пользовательского интерфейса
в MS Excel.

Темы: Вызов и выполнение макросов. Кнопки – команды на панели быстрого доступа. Назначение макросов объектам в листе. «Автоматически» выполняемые макросы (Auto_open).

1. Создание кнопки – команды на панели быстрого доступа.

1.1. Воспользуйтесь командой Office – Параметры Excel – Настройка и выберите Макросы в списке Выбрать команды из, укажите последовательно на макросы НовыйАдрес и СтарыйАдрес, нажмите кнопку Добавить. Затем измените изображения кнопок с помощью кнопки Изменить и введите имя в поле Отображаемое имя, которое в виде подсказки будет появляться при наведении мыши на кнопку - команду. (рис.14.7).

Рис.14.7

1.2. Поочередно выполните макросы "СтарыйАдрес" и "НовыйАдрес", используя созданные кнопки - команды.

2. Назначение макросов объектам в листе.

2.1. Назначение макросов командным кнопкам в листе.

2.1.1. Выберите команду Разработчик – Элементы управления – Вставить - Элементы управления формы. В палитре элементов выберите кнопку и поместите ее на листе. В диалоговом окне Назначить макрос объекту свяжите кнопку с одним из двух созданных макросов. Повторите эти действия для создания второй кнопки и свяжите ее со вторым макросом.

2.1.2. Отформатируйте объекты (кнопки) таким образом, чтобы их размеры не превышали 2 ячеек в ширину и 2 ячеек в высоту. На каждой кнопке должна быть надпись, поясняющая назначение кнопки и оформленная жирным шрифтом размером 10 пунктов.

2.1.3. Проверьте правильность выполнения макросов с помощью созданных кнопок.

2.2. Назначение макросов объектам.

2.2.1. С помощью команды Вставка – Иллюстрации – Фигуры создайте на рабочем листе два объекта из категории Фигуры, выбранные произвольно.

2.2.2. Используя контекстное меню, для каждого объекта назначьте макрос, который будет выполняться при активизации объекта.

2.2.3. Проверьте правильность выполнения макросов с помощью созданных графических объектов.

2.2.4. Назначьте макросы двум другим объектам (SmartArt, WordArt, Клипы, Иллюстрации) по собственному выбору и проверьте их работоспособность.

3. Предъявите полученные результаты преподавателю, а затем тщательно удалите объекты, созданные на панели быстрого доступа. Для этого используйте контекстное меню и команду Удалить с панели быстрого доступа.

4. Задание для самостоятельной работы.

4.1. Перейдите в режим ручного пересчета.

Рис.14.8

4.2. Создайте таблицу, приведенную на рис.14.8. Для этой таблицы создайте макрос с именем "Расчеты", который будет вычислять средние значения роста и веса, записывая их в соответствующие ячейки. Для вызова этого макроса создайте элемент управления типа "командная кнопка", при выборе (нажатии) которой будет выполняться расчет средних значений.

4.3. Восстановите режим автоматических вычислений.