Автоматически выполняемые макросы.

5.1. Создайте в новой книге макрос под именем «Auto_open», записывающий на лист в диапазон ячеек (В2:В4) три имени (Лена, Зина, Нина) и обрамляющий эти имена красной рамкой. Очистите лист книги от введенных данных и сохраните книгу под именем «Книга_МакОткр» в формате «Книга Excel с поддержкой макросов». Закройте книгу.

5.2. Откройте книгу «Книга_МакОткр» и убедитесь, что созданный макрос выполняется «автоматически» при открытии книги.

Задание № 15 (Часть 1). Создание форм «UserForm».

Цель: Создание и использование экранных форм для пользовательских диалоговых окон в приложениях MS Excel.

Темы: Создание пользовательских форм в редакторе Visual Basic. Свойства форм. Элементы управления форм и панель инструментов.

Задачи: Отобразить в табличной области наименование, цену и количество изделий первого или второго сорта, пользуясь табличными данными (рис.15.1) и экранной формой, представленной на рис.15.2.

1. Создайте таблицу, представленную на рис.15.1. Дайте листу имя "Склад" и сохраните в файле «Формы».

Рис.15.1

2. Создайте макросы, необходимые для выполнения работы.

2.1. Создайте в автоматическом режиме два макроса СОРТ1 и СОРТ2, которые, используя расширенный фильтр, извлекут из списка (рис.15.1) товары 1-го и 2-го сортов. Критерии для извлечения данных следует записать до начала записи макросов, расположив первый (для сорта 1) в диапазоне G1:G2, а второй (для сорта 2) в диапазоне H1:H2. Извлеченные данные разместите в диапазонах, начинающихся с ячеек А15 и А25 соответственно. Запись макроса следует начать с команды активизации листа (щелчком по ярлыку листа). Проверьте правильность работы макросов, пользуясь командой Разработчик – Код – Макросы - Выполнить. Повторно проверьте работу макросов, сделав предварительно активным Лист2 книги.

2.2. Создайте макрос под именем ОЧИСТКА, который очищает содержимое ячеек диапазона результатов выборки (А15:D35) и проверьте его работоспособность.

3. Создайте первую экранную (пользовательскую) форму.

3.1. С помощью команды Разработчик – Код – Visual Basic или сочетания клавиш Alt+F11 активизируйте окно редактора Visual Basic Editor (VBE) и ознакомьтесь с его составляющими:

В верхней левой части редактора расположено окно проекта Project Explorer, а в нижней - окно свойств Properties Window, отображающее свойства активного объекта. Каждый проект содержит совокупность кодов и объектов VBA, обеспечивающих работу данного проекта и принадлежащих книге Excel. Компоненты, содержащие коды представлены в виде иерархии папок – Microsoft Excel Objects. К ним относятся листы книги Excel, модули (Modules) и формы (Forms), создаваемые пользователем.

3.2. Просмотрите созданные в автоматическом режиме макросы в Модуле1, находящемся в папке Modules. Для этого раскройте папку Modules и дважды щелкните на Модуле1.

3.3. Создайте пользовательскую форму, пользуясь командой меню редактора Visual Basic Insert - UserForm. Новая форма по умолчанию имеет имя UserForm1 (свойство Name) и такой же заголовок (свойство Caption), который отображается в строке заголовка окна формы.

3.3.1. Познакомьтесь с содержанием окна свойств формы, предварительно выделив объект – форму (View – Properties Window). Свойства могут быть сгруппированы по категориям или по алфавиту. Замените значение свойства формы Caption, которое отображается в заголовке на "Выбор сорта". Обратите внимание на имя формы – свойство объекта Name, которое используется для обращения к объекту и пока остается прежним, но может быть изменено по желанию пользователя.

3.3.2. Используйте по собственному усмотрению свойства формы, устанавливающие ее фон (BackColor)и расположение на экране (StartUpPosition).

3.3.3. Сохраните файл и активизируйте форму, нажав клавишу F5 или выбрав команду Run - Run Sub/UserForm, предварительно выделив форму. Закройте окно формы и вернитесь в редактор VBE.

3.3.4. Активизируйте панель инструментов (Toolbox), содержащую элементы управления, которые могут быть размещены в окне формы, выбором команды View – Toolbox, если она не отображена на экране.

3.3.5. Выберите на панели инструментов кнопки (CommandButton) и расположите их на форме как показано на рис.15.2. Используйте команду Format для изменения размеров кнопок и упорядочивания их расположения.

3.3.6. Активизируйте поочередно каждую кнопку и измените ее свойство Caption, которое обеспечивает вывод надписи на кнопке, в соответствии с рис.15.2

3.3.7. Выполните двойной щелчок мыши на первой кнопке. При этом в правой части экрана появится область кодов данной формы и шаблон процедуры обработки события, которая выполняется при щелчке мыши на выбранной кнопке. Название процедуры стандартно и состоит из имени объекта - CommandButton1 и события, которое ее вызывает – Click, записанные через подчеркивание.

Private Sub CommandButton1_Click()

Код процедуры

End Sub

Рис.15.2

3.3.8. Скопируйте код макроса Сорт1 из Модуля1 и вставьте его в тело процедуры CommandButton1_Click (на место, обозначенное "Код процедуры").

3.3.9. Повторите п.3.3.7.-3.3.8 для кнопок Сорт2 и Очистка.

Переключиться между окнами объектов и кодов можно с помощью кнопок View Code и View Object панели инструментов окна проектов.

3.3.10. Проверьте работоспособность кнопок в режиме отладки (F5).

3.3.11. Для кнопки Закрыть введите код Me. Hide. Проверьте работоспособность кнопки.

3.3.12. Активизируйте форму с помощью элемента управления – кнопки с листа Лист1 (склад). Для этого вернитесь на лист "склад" и с помощью команды Разработчик – Элементы управления – Вставить – Элементы ActiveX поместите кнопку на листе. Активизируйте окно ее свойств с помощью команды Разработчик – Элементы управления – Свойства и свойству Caption присвойте значение "Выбор сорта". После двойного щелчка на кнопке впишите в шаблон предложенной процедуры обработки события код UserForm1.Show. Обратите внимание на то, что код при этом сохраняется в объекте Лист1 (склад). Вернитесь на лист “склад”, выйдите из режима Конструктора и запустите форму с листа "склад" с помощью кнопки "Выбор сорта". Проверьте ее работу.

3.3.13. Замените код в процедурах событий кнопок Сорт1, Сорт2 и Очистка на вызов соответствующих процедур, записанных в виде макросов в Модуле1. Для этого в теле процедур событий запишите названия вызываемых процедур. Например, код кнопки Сорт1 будет выглядеть следующим образом:

Private Sub CommandButton1_Click()

Сорт1

End Sub

3.3.14. Проверьте работоспособность формы.

Задание № 15 (Часть 2). Обработка событий.

Цель: Знакомство с методами управления формами (обработка событий) средствами VBA.

Темы: Использование полей форм для ввода и обработки данных. Обработка событий.

Задачи: Отобразить в текстовых полях экранной формы (рис.15.3) характеристики (цену, количество и сорт) товара, наименование которого выбрано пользователем в раскрывающемся списке. Установка флажка указывает на те характеристики товара, которые пользователь желает увидеть в соответствующих текстовых полях экранной формы. Форма основана на данных таблицы, представленной на рис.15.1.

1. Создайте вторую экранную форму со свойством Name, имеющим значение UserForm2 и свойством Caption со значением СКЛАД

1.1. Расположите элементы управления в форме так, как показано на рис.15.3.

1.2. Поместите результат выбора элемента раскрывающегося списка ComboBox1 в ячейку A10 листа «склад».

Для этого сформируйте процедуру обработки события для объекта ComboBox1. Двойной щелчок на объекте активизирует окно его кодов. Выберите в левом раскрывающемся списке окна кодов объект ComboBox1, а в правом – событие Change, при котором элемент, выбранный из списка, будет помещен в ячейку А10 листа «склад». Эта процедура имеет вид:

 

Private Sub ComboBox1_Change()

Sheets("склад").Range("A10").Value = UserForm2.ComboBox1.Value

End Sub

 

Рис.15.3

1.3. Сформируйте процедуры обработки событий для трех флажков CheckBox1, CheckBox2 и CheckBox3 и события Click. В этих процедурах будет установлена связь между флажками и соответствующими ячейками листа «склад» (А11 – А13). В этих ячейках будет помещен результат установки флажка – логическое значение ИСТИНА, если флажок установлен, и логическое значение ЛОЖЬ - если не установлен. (Начальное значение False в этих ячейках устанавливается в процедуре инициализации формы).

Процедуры имеют вид, показанный для первого флажка:

 

Private Sub CheckBox1_Click()

Sheets("склад").Range("A11").Value = CheckBox1.Value

End Sub

 

1.4. Перейдите в режим отображения кодов формы и для объекта UserForm выберите из правого раскрывающегося списка событий событие Initialize, которое происходит при отображении формы на экране.

При инициализации формы сформируйте раскрывающийся список ComboBox1 с помощью метода AddItem, а также установите исходные значения в ячейках листа «склад», связанных с флажками так как показано далее:

 

Private Sub UserForm_Initialize()

' формирование раскрывающегося списка

ComboBox1.AddItem "бетон"

ComboBox1.AddItem "двери"

ComboBox1.AddItem "доски"

ComboBox1.AddItem "кирпич"

ComboBox1.AddItem "плитка"

ComboBox1.AddItem "рамы"

ComboBox1.AddItem "трубы"

' первоначально в списке отображается первый элемент с индексом (ListIndex) =0

ComboBox1.ListIndex = 0

' установка исходных значений False в ячейки листа, связанные с флажками

Sheets("склад").Range("A11").Value = False

Sheets("склад"). Range("A12").Value = False

Sheets("склад"). Range("A13").Value = False

End Sub

1.5. Сформируйте в рабочем листе «склад»в ячейках С11:С13 формулы, которые позволят по известному названию товара из списка и известному значению флажка вывести значения цены, количества и сорта выбранного товара. Формулы должны содержать функции ЕСЛИ и ПРОСМОТР. Например, формула, записанная в клетку С11 для определения цены товара, будет выглядеть следующим образом:

ЕСЛИ(А11;ПРОСМОТР(А10;А2:А8;В2:В8);" ")

1.6. Сформируйте процедуру обработки события для кнопки «результат». В этой процедуре необходимо отобразить значения цены, количества и сорта из диапазона ячеек С11:С13 в текстовых полях TextBox1, TextBox2 и TextBox3. Процедура имеет вид:

 

Private Sub CommandButton1_Click()

TextBox1.Value = Sheets("склад"). Range("C11").Value

TextBox2.Value = Sheets("склад"). Range("C12").Value

TextBox3.Value = Sheets("склад"). Range("C13").Value

End Sub

1.7. Сформируйте процедуру обработки события для кнопки «закрыть». Процедура имеет вид:

 

Private Sub CommandButton2_Click()

Me.Hide

End Sub

1.8. Проверьте работоспособность экранной формы в отладочном режиме с помощью клавиши F5 или команды Run Sub/UserForm.

1.9. Создайте в листе модуля процедуру Show_form. Тело процедуры должно содержать код

UserForm2.Show.

 

Sub Show_form()

UserForm2.Show

End Sub

1.10. Создайте в рабочем листе произвольный графический объект и назначьте ему процедуру, активизирующую созданную экранную форму.

1.11. Проверьте работоспособность процедуры.

Задание № 15 (Часть 3). Диалоговые окна.

Цель: Знакомство со стандартными диалоговыми окнами.

Темы: Использование функции MsgBox для сообщений и управления приложением.

1. Создайте окно сообщений, приведенное на рис.15.4.

Рис.15.4

1.1. На листе модуля создайте процедуру "Выход", один из вариантов текста которой приведен далее.

Sub Выход()

msg= MsgBox ("Завершить работу? ", vbYesNo+vbQuestion+vbDefaultButton2, "Выход")

If msg = vbYes Then

ActiveWorkbook.Save

ActiveWindow.Close

Else

Exit Sub

End If

End Sub

 

1.2. Создайте в листе «склад» произвольный графический объект и назначьте ему процедуру "Выход". Проверьте работоспособность процедуры.

Задание № 16. Работа с базами данных.

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

Темы: Формирование критериев выборки. Импорт данных в MS Excel. Утилита MS Query. SQL-запрос.

1. Работа с данными Excel как с "базой данных".

1.1. Создайте таблицу, приведенную в левой части рис.16.1. Большую часть этой таблицы можно заимствовать из задания № 8. Обратите внимание на то, как в соответствии с заданием определяются и записываются в ячейках листа формулы для вычисления надбавки, налога и выплат.

1.2. Сформулируйте критерии для выборки данных и запишите их справа от исходной таблицы в несмежных диапазонах, как представлено на рис.16.1 (справа).

Критерии для выборки следующих данных:

· лица женского пола, (критерий №1);

· лица мужского пола с суммой выплат больше 500 руб. (критерий №2);

· лица мужского пола с суммой выплат меньше 400 руб. (критерий №3);

· мужчины - референты и водители с выплатой больше 250 и меньше 500, а также женщины - менеджеры с выплатой больше 500 и меньше 700 (критерий №4).

1.3. Пользуясь командой Данные – Сортировка и фильтр – Дополнительно - Расширенный фильтр выполните выборку данных в соответствии с указанными критериями и поместите результаты выборки в диапазоне ячеек справа от критериев (рис.16.1).

Рис.16.1

2. Импорт текстовых файлов.

2.1. Путем копирования данных создайте на отдельном листе рабочей книги часть представленной выше таблицы, содержащей только исходные данные (диапазон А1:Н12).

2.2. Сохраните эти данные в формате текстового файла с табуляционными отступами в качестве разделителей.

2.3. Запустите текстовый процессор Word и загрузите в него сохраненный файл. Просмотрите его структуру.

2.4. Закройте текстовый файл с данными.

2.5. Загрузите в табличный процессор Excel созданный в предыдущем пункте текстовый файл. Обратите внимание на все диалоговые окна "Мастера текстов", которые открываются в процессе загрузки файла и на возможности работы со структурой импортируемого текстового документа, особенно на определение разделителей и задание форматов данных.

2.6. Просмотрите загруженную таблицу, сравните её с исходной таблицей. Обратите внимание на количество листов в созданной книге.

3. Использование MS Query для создания запросов к внешним базам данных.

3.1. Проверьте наличие в вашей папке файла, содержащего базу данных (название файла уточните у преподавателя (DBAccess.mdb)).

3.2. Перейдите на новый лист Excel. Запустите программу взаимодействия с базами данных MS Query, используя команду Данные – Получить внешние данные – Из других источников – Из Microsoft Query.

3.2.1. Выберите в качестве источника данных файлы, создаваемые MS Access Database и откажитесь от использования мастера запросов (рис.16.2).

Рис.16.2

3.2.2. В открывшемся диалоговом окне "Выбор базы данных" (рис.16.3) найдите и выберите файл DBAccess.mdb, содержащий базу данных.

Рис.16.3

3.2.3. В окне "Добавление таблицы" выберите один файл с именем "Таблица1" для дальнейшего использования.

3.2.4. Научитесь добавлять, удалять и перемещать столбцы (поля базы данных) в области данных. Для размещения столбцов можно использовать непосредственно таблицу, область данных или команды меню. Разместите в области данных столбцы-поля в следующем порядке: "№", "Фамилия", "Пол", "Должность", "Оклад", "Телефон", "Год рожд".

3.2.5. Используя команду Записи – Изменить столбец, создайте заголовки столбцов, отличающиеся от наименований полей в базе данных, как показано на рис.16.4.

3.2.6. Используя команду Вид - Условия, измените внешний вид запроса так, чтобы в нем присутствовали три области: область таблиц (исходные данные), область критериев (формирование условий запроса) и область результатов (рис.16.4.).

3.2.7. Уберите (скройте) столбец, соответствующий полю "№" (Формат – Скрыть столбец).

3.2.8. Извлеките все записи из таблицы базы данных, выполнив команду Файл – Вернуть данные в Microsoft Office Excel, разместив их, начиная с ячейки А1 текущего листа Excel.

3.2.9. Отсортируйте извлеченные данные в соответствии со следующими правилами:

· по фамилии в алфавитном порядке;

· в порядке убывания по году рождения;

3.2.10. Находясь в области полученных данных, вернитесь в MS Query, используя команду Данные – Подключения – Обновить все – Свойства подключения – вкладка Определение – кнопка Изменить запрос.

3.2.11. Научитесь выполнять переходы к записям (строкам) с произвольным номером (Записи - Перейти). Просмотрите первую, последнюю и 8-ю записи. Внесите исправления в номера телефонов первой, последней и 2-й записей, предварительно указав возможность правки записей (Записи – Разрешить правку).

Рис.16.4

3.2.12. Верните данные в рабочий лист Excel.

3.2.13. Измените свойства диапазона данных, указав включение номеров строк (рис.16.5) (Данные – Подключения – Свойства и Данные – Подключения – Обновить все - Обновить).

Рис.16.5

4. Формирование критериев для выбора данных из внешней базы данных.

4.1. Формирование простых критериев.

4.1.1. На новом листе Excel выполните запрос, используя область критериев (Вид - Условия), в которой укажите в качестве поля для определения критерия - "Должность", а в качестве значения поля - менеджер.

4.1.2. Выполните выборку данных и просмотрите результат в области результатов (Записи – Выполнить запрос).

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

4.1.4. Создайте новый критерий для выбора данных о лицах мужского пола. Для формирования критерия воспользуйтесь режимом диалогового задания критерия. Последовательно открывая диалоговые окна "Добавление условия" и "Выбор значений", запишите название поля (Пол) и значение поля (м). Пример записи такого критерия представлен на рис.16.6.

4.1.5. Выполните запрос и проверьте полученный результат.

4.1.6. Сохраните созданный запрос под именем "Запрос1".

4.2. Критерии, содержащие несколько логических условий (связанные критерии).

4.2.1. Сформируйте критерий для выборки данных о лицах, фамилии которых начинаются на "И" или "С".

4.2.2. Сформируйте критерий для выборки данных о менеджерах - мужчинах и секретарях.

4.2.3. Сохраните созданный запрос под именем "Запрос2".

4.3.Специальные критерии.

4.3.1. Внесите изменения непосредственно в записи базы данных, удалив содержимое поля "Оклад" для директора и референта.

4.3.2. Извлеките данные о лицах, для которых отсутствует значение в поле "Оклад".

4.3.3. Очистите область критериев (удалите все критерии).

4.3.4. Загрузите из файла сохраненный ранее запрос ("Запрос1") и отредактируйте его так, чтобы он в добавление ко всему, что было ранее, выбирал только записи с "пустым" значением поля "Оклад".

5. SQL-Запрос.

5.1. Сформируйте критерий и выполните запрос, содержащий сведения о фамилии, должности и телефоне для лиц, достигших возраста 50 лет.

5.2. Вызовите на экран окно для просмотра SQL-инструкции для созданного запроса, как показано на рис.16.7 (Вид – Запрос SQL). Просмотрите текст SQL-инструкции, выясните назначение каждой из ее частей и установите соответствие между ними и объектами "Запроса по образцу", создаваемому ранее.

Рис.16.6

5.3. Просмотрите SQL-инструкцию для "Запроса1".

5.4. Отредактируйте SQL-инструкцию так, чтобы она соответствовала запросу, рассмотренному в п.4.3.2.

5.5. Вызовите запрос, сохраненный в файле под именем "Запрос2" и просмотрите его, определив назначение каждого из его элементов.

Рис.16.7

Задание № 17. MS Query. Многотабличные БД.

Цель: Знакомство с использованием MS Query для работы с многотабличными базами данных.

Темы: Связанные таблицы. Объединение данных в запросе.

1. Откройте новый документ Excel и запустите MS Query, выбрав в качестве источника данных - файлы, создаваемые MS Access 2007, и откажитесь от использования мастера запросов.

1.1. Проверьте наличие в Вашей папке файла, содержащего базу данных (название файла уточните у преподавателя (DB2Access.mdb)).

1.2. В окне "Добавление таблицы" выберите файл с именем "Таблица1" для дальнейшего использования.

1.3. Поместите в область результатов все поля данной таблицы.

1.4. Переместите указатель на последнюю запись и определите количество записей в Таблице1. Запомните или запишите полученное значение.

1.5. Удалите из области таблиц Таблицу1. Добавьте в эту область Таблицу2 из базы данных DB2Access.mdb.

1.6. Поместите в область результатов все поля второй таблицы. Определите количество записей в таблице. Запишите или запомните результат для дальнейшего сравнения.

2. Поместите в область таблиц две таблицы: "Таблица1" и "Таблица2".

2.1. Поместите в область результатов поля "ЛичнКод", "Фамилия", "Должность" из таблицы "Таблица1" и поля "ЛичнКод", "ПочтИндекс", "Город", "Дом", "Квартира" из "Таблица2".

2.2. Пользуясь командой Таблица - Объединения и диалоговым окном "Объединения", установите такой режим объединения данных в запросе, который обеспечит точное соответствие (=) между значениями полей "ЛичнКод" обеих таблиц так, как показано на рис.17.1 (объединение включает только записи с точным совпадением значений полей связи из двух таблиц).

Рис.17.1

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

2.4. Удалите исходный вариант объединения из списка "Объединения в запросе" в нижней части окна "Объединения".

2.5. Установите 2-й вариант объединения, включающий все значения из "Таблицы1", и только те записи из "Таблицы2", где значения полей "ЛичнКод" обеих таблиц точно совпадают.

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

2.7. Установите 3-й вариант, когда объединение включает все значения из таблицы "Таблица2" и только записи из "Таблицы1", где значения полей "ЛичнКод" для обеих таблиц точно совпадают. Определите количество записей в области результатов.

3. Установите вариант объединения, описанный в п.2.5.

3.1. Сформируйте критерий выборки по полю "ПочтИндекс" для получения фамилий лиц, адреса которых не известны.

3.2. Установите объединение, представленное в п.2.7, и сформируйте критерий выборки по полю "Фамилия" для получения данных о лицах, чьи фамилии не известны. Исходя из обозначений личного кода, сделайте выводы о том, какие должности могут занимать эти лица.

3.3. Удалите в области результатов одно из полей "ЛичнКод" и установите первый вид объединения (точное совпадение значений). Сформируйте критерий выборки по полю "Должность" для получения сведений о месте жительства директора.

3.4. Сохраните последний запрос под именем "Запрос3" и верните полученные результаты в Excel на первый лист в свободный диапазон ячеек.

Рис.17.2

3.5. Перейдите из режима автоматического выполнения запросов в режим "Выполнить запрос". Для этого воспользуйтесь соответствующими пунктами меню "Записи" или кнопками на панели инструментов. На основе двух имеющихся таблиц сформулируйте запросы и получите из таблиц следующие данные:

· обо всех лицах, личный код которых начинается на "М". Верните полученные результаты в Excel на рабочий лист в свободный диапазон ячеек;

· обо всех лицах, личный код которых начинается символом "М" или "Д", проживающих в Москве;

· обо всех лицах, личный код которых начинается символом "И", проживающих в Санкт-Петербурге, а также лицах, коды которых начинаются с символа "С", проживающих где угодно (рис.17.2).

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

Задание № 18 (Часть 1). Таблицы данных.

Цель: Использование инструментов "Таблицы данных" для решения типовых задач. Знакомство с применением инструмента "Поиск решения".

Темы: «Таблицы данных» с одной и двумя переменными. Организация вычислений.

1. Создание таблицы данных с одной переменной.

1.1. В соответствии с таблицей, приведенной на рис.18.1, создайте "таблицу данных", которая позволит вычислить значения функции Y=SIN(X) для X, меняющегося в диапазоне от 0 до 6,280 с шагом 0,628.

1.1.1. Пользуясь автозаполнением, задайте численные значения входного диапазона данных (ячейки B3:B13).

1.1.2. В ячейку С2 введите формулу для вычисления Y=SIN(X), в нее будут подставляться изменяемые данные (значения Х). Значения Х будут передаваться в формулу через ячейку В2.

1.1.3. Выделите диапазон ячеек В2:С13, вызовите диалог Таблица данных, пользуясь командой Данные – Работа с данными – Анализ «что-если» - Таблица данных и определите, что входные данные диапазона В3:В13 будут передаваться в формулу через ячейку В2.

1.2. Выполните подстановку, проверьте правильность результата, сопоставив полученные данные с приведенными на рис.18.1.

1.3. Постройте график рассчитанной функции, разместив его как на рис.18.1.

Рис.18.1

2. Добавление формул в существующую таблицу подстановки с одной переменной.

2.1. Пользуясь инструментом "таблицы данных", создайте аналогично упражнению п.п.1.1 - 1.3 таблицу, позволяющую рассчитать 11 значений функции Y=SIN(X) при Х, меняющемся от 0 до 3,1415926. Эта таблица представлена на рис.18.2 в ячейках В7:С18. Формула для вычислений записана в ячейке С7. Подстановка входных данных (Х) в формулу выполняется через ячейку В7.

2.2. Очистите таблицу данных от результатов вычислений.

2.3. Дополните таблицу тремя новыми формулами: Y=1,25*SIN(2*X), Y=1,5*SIN(4*X), Y=SIN(X)+1,25*SIN(2*X)+1,5*SIN(4*X), зависящими от одного и того же аргумента Х, меняющегося в том же диапазоне значений. Три новые формулы запишите в ячейках D7, E7 и F7 соответственно.

2.4. Пользуясь "таблицей данных", выполните расчет по всем четырем формулам для заданного диапазона изменения входных значений Х.

2.5. Проверьте правильность вычислений. Постройте два графика, на первом из которых будут представлены три первые функции: Y=SIN(X), Y=1,25*SIN(2*X), Y=1,5*SIN(4*X), а на втором – четвертая функция Y=SIN(X)+1,25*SIN(2*X)+1,5*SIN(4*X). Таблица и графики должны быть оформлены так, как показано на рис.18.2.

Рис.18.2

3. Создание таблицы данных с двумя переменными.

3.1. Пользуясь "таблицей данных" с двумя переменными, создайте таблицу умножения целых чисел. Множимое – целые числа в диапазоне от 1 до 7. Множитель – нечетные целые числа в диапазоне от 3 до 9. Образец таблицы представлен на рис.18.3. Формула записывается в ячейке К4 как K4=K2*L2, где ячейки К2 и L2 используются для передачи наборов входных данных из двух диапазонов (множимого и множителя). При заполнении полей диалогового окна «Таблица данных » обратите внимание на правильность передачи диапазонов множимого и множителя через соответствующие ячейки.

Рис.18.3

3.2. Пользуясь "таблицей данных" с двумя переменными А и Х, создайте таблицу для вычисления функции Y=2*A*SIN(X+A). Диапазоны изменения А и Х, а также результаты вычислений представлены на рис.18.4.

3.3. Выполните вычисления, проверьте правильность результатов и постройте графики для Y=2*A*SIN(X+A) при трех значениях А как показано на рис.18.4.

Рис.18.4

4. Преобразование рассчитанных значений таблицы данных в константы и перенос данных из таблиц данных.

4.1. Пользуясь командами Копировать и Вставить, создайте, начиная с ячейки В16, таблицу, которая будет содержать числовые значения диапазона С4:Е14, взятые из последней таблицы подстановки.

4.2. Скопируйте диапазон С4:Е14 в буфер и, пользуясь "Специальной вставкой", замените формулы таблицы в диапазоне С4:Е14 числовыми значениями.

4.3. Очистите диапазон ячеек С4:Е14.

5. Удаление всей таблицы данных.

5.1. Восстановите вид таблицы, повторив действия, описанные в п.п. 3.2 - 3.3.

5.2. Попытайтесь удалить данные столбца D созданной таблицы. Попытайтесь удалить диапазон ячеек из созданной таблицы.

5.3. Очистите область всей таблицы данных, включая формулы, значения подстановки, рассчитанные значения, форматы и комментарии, воспользовавшись командой Главная - Редактирование – Очистить – Очистить все.

Задание № 18 (Часть 2). Подбор параметра.

Цель: Использование инструментов "Подбор параметра" для решения
типовых задач.

Темы: Нахождение и анализ данных с помощью «Подбора параметров».

1. Создайте таблицу, приведенную справа на рис.18.5, воспользовавшись расчетными формулами из левой таблицы. Эта таблица представляет гипотетическую смету расходов на выполнение некоторых работ, где отдельные статьи сметы связаны между собой определенными зависимостями. При создании таблицы (сметы) исходными данными являются величины Мк и Зп, а Q - задаваемое значение. Анализ сметы сводится к сопоставлению отдельных статей расходов, общих расходов и прибыли с общей стоимостью работ.

2. Пользуясь командой Данные – Работа с данными – Анализ «что-если» - Подбор параметра, определите:

· при какой общей стоимости работ прибыль достигнет величины 500 т.р.;

· при какой общей стоимости работ прибыль достигнет величины 1000 т.р.;

· при какой общей стоимости работ отношение прибыли к общей стоимости достигнет величины 50%.

Рис.18.5

3. Изменяя значения общей стоимости работ (Q) и копируя результаты на новый лист (A1:G3), постройте таблицу, отражающую зависимость величин (П/Q)% и прибыли (П) от общей стоимости работ (Q). Пример такой таблицы - на рис.18.6.

4. Постройте графики зависимостей (П/Q)% и прибыли (П) от общей стоимости работ (Q) аналогично графикам приведенным на рис.18.6.

Рис.18.6

5. Создайте таблицу, приведенную на рис.18.7. В этой таблице представлены гипотетические поквартальные сведения о сбыте некоторых товаров, себестоимости продукции, доходах от реализации и величине прибыли. Объем сбыта зависит от некоторого сезонного коэффициента (Кi), а также не линейно зависит от затрат на рекламу продукции при прочих фиксированных факторах. При создании таблицы исходными данными являются величины Кi, Qi, C и R. Остальные величины вычисляются по формулам, приведенным в комментариях к таблице (диапазон A14:D17). Создав таблицу, убедитесь, что полученные результаты расчетов по формулам совпадают с приведенными в таблице рис.18.7. Точность представления данных в таблице – два десятичных знака после запятой, для процентных величин – младший разряд целой части числа.

6. Определите характер зависимостей Vi=f(Qi), Di=f(Qi), Pi=f(Qi), введя несколько значений Qi (в диапазоне от 1000 до 100000). Постройте вручную или используя средства Excel примерные графики названных зависимостей.

Рис.18.7

7. Пользуясь "подбором параметра", определите:

· можно ли получить в IV квартале прибыль P₄=100000 т.р., изменяя расходы на рекламу Q₄;

· величину расходов на рекламу в IV квартале (Q₄), необходимую для получения прибыли P₄=150000 т.р.;

· пользуясь последовательным подбором параметра, максимальное значение прибыли P₄ с точностью до единиц целой части числа.

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

8.1. Задайте величину затрат на рекламу Q₄=2000 т.р

8.2. Подбирая параметр Q₄, определите, при каком значении Q4 будет достигнута величина прибыли P₄=30000 т.р.

8.3. Задайте величину затрат на рекламу Q₄=60000 т.р

8.2. Подбирая параметр Q4, определите, при каком значении Q4 будет достигнута величина прибыли P₄=32000 т.р.

9. Найдите близкие к максимальным значения:

- прибыли для четырех кварталов (Pi);	- квартальные значения Qi;
- суммарную (годовую) прибыль (P);	- суммарные годовые расходы на рекламу (Q);
- долю расходов на рекламу в общем доходе от реализации продукции (Q/D).

Результат разместите в ячейках F11:G11. Для определения названных величин рекомендуется последовательно воспользоваться несколько раз подбором параметра.

10. Пользуясь расчетными формулами (A14:D17), постройте на отдельном листе таблицу, отражающую зависимости величин V₄, D₄, P₄ от величины Q₄ (для значений Q₄ меняющихся от 10000 до 100000 с шагом 10000).

11. Постройте на отдельном листе два графика (подобных представленным на рис.18.6), на которых будут отображены зависимости V₄=f(Q₄) для первого графика и D₄=f(Q₄) и P₄=f(Q₄) для второго графика.

12. Точность подбора параметра.

12.1. Запишите в ячейках С2, С3 и С4 (рис.18.8) исходные данные и формулу для вычисления произведения двух чисел Y=A*X. Исходные значения сомножителей: А=0,5 и Х=2,35.

12.2. Выполните следующие действия:

· скопируйте исходные данные и формулу (С2:С4) в диапазоны Е2:Е4, G2:G4 и I2:I4;

· для столбцов E, G и I установите разрядность отображаемых значений равную соответственно 4, 6 и 16 разрядов после десятичной запятой, как показано на рис.18.8.

12.3. Выполните "подбор параметра" для нахождения первого сомножителя (А), расположенного в ячейке С2 при С3=2,35, искомом значении целевой ячейки С4=4,3758 и начальном значении С2=0,5.

12.4. Повторите "подбор параметра" для той же формулы, записанной в диапазонах Е2:Е4, G2:G4 и I2:I4.

Рис.18.8

12.5. Выполните вручную умножение для данных в ячейках Е2:Е3, G2:G3 и I2:I3 с заданной разрядностью и запишите результаты в ячейки Е6, G6 и I6 соответственно.

12.6. Сравните данные, полученные в результате ручных вычислений и "подбора параметров". Оцените величину и знак погрешности вычислений.

13. Создайте таблицу, в которой выполняется возведение числа Х в степень Y по образцу, приведенному на рис 18.9. Формула Z=X^Y, обеспечивающая вычисления, записана в ячейке D13, а исходные данные Х=2 и У=2 - в ячейках D11 и D12 соответственно.

13.1. Пользуясь "подбором параметра", выполните поиск такого значения Х (при неизменном Y=2), которое обеспечит Z=4. Результат поиска должен быть представлен в ячейках В11:В13.