41894

Списки. Фильтрация данных. Связывание таблиц. Лабораторные работы в MS Excel 2007

Лабораторная работа

Информатика, кибернетика и программирование

Введите таблицу приведенную на рис. Рис. Введите таблицу представленную на рис. Активизируйте лист с исходной таблицей рис.

Русский

2013-10-26

1.43 MB

116 чел.

Лабораторные работы в MS Excel 2007

(часть 3 основная аудиторная)

Задание № 1. Списки. 2

Задание № 2. Фильтрация данных. 4

Задание № 3. Связывание таблиц. 5

Задание № 4. Консолидация данных. 8

Задание № 5. Сводные таблицы. 12

Задание № 6. Обмен данными. 16

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

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

Задание № 9. Таблицы данных. 30

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

Задание № 10 (Часть 2). Поиск решения. 35

 


Задание № 1. Списки.

ЦельЗнакомство с методами обработки данных, организованных в списки.

Темы: Сортировка, редактирование, просмотр, поиск и извлечение данных в списках MS Excel.

1 .Сортировка списка по строкам.

1.1. Введите таблицу, приведенную на рис.8.1.

Рис.8.1

1.2. Пользуясь командой Главная – Редактирование – Сортировка и фильтр - Настраиваемая сортировка, отсортируйте список:

  1.  по полу;
  2.  по полу и должности;
  3.  по полу, должности и году рождения.

Проанализируйте полученные результаты.

1.3. Пользуясь сортировкой и командой Данные – Структура – Промежуточные итоги, ответьте на вопросы а) - с).

  1.  Сколько Ивановых работают в фирме и кто из них самый молодой?
  2.  Каков средний возраст мужчин и женщин, работающих в фирме?
  3.  Сколько в фирме менеджеров, инженеров, водителей и представителей других должностей?

2. Сортировка списка по столбцам.

2.1. Введите таблицу, представленную на рис.8.2, на новый лист книги.

2.2. Отсортируйте ее так, чтобы предметы (названия полей) располагались в алфавитном порядке. Транспонируйте таблицу с помощью команд Главная – Буфер обмена - Копировать и Главная – Буфер обмена – Вставить - Транспонировать. Добавьте в конец таблицы строку "Средний балл" и заполните ее, введя соответствующую формулу.

2.3.Выполните сортировки по столбцам.

2.3.1. Переставьте столбцы так, чтобы фамилии студентов расположились в обратном алфавитном порядке.

2.3.2. Переставьте столбцы так, чтобы в первых колонках были худшие учащиеся (с минимальным средним баллом).

2.3.3. Переставьте столбцы так, чтобы в первых колонках были учащиеся, хорошо успевающие по математике, но с провалами по физкультуре.

2.3.4. Переставьте столбцы так, чтобы фамилии студентов расположились в алфавитном порядке, снова транспонируйте таблицу и поместите ее, начиная с клетки A25.

3. Обработка списков с помощью формы.

3.1. Активизируйте лист с исходной таблицей (рис.8.1).

3.2. Активизируйте окно Форма, предварительно поместив его на панель быстрого доступа (Office – Параметры Excel – Настройка – Команда не на ленте – Добавить (в панель быстрого доступа),

 просмотрите все записи в списке;

 "примите" (добавить) на работу одного инженера;

 "увольте" (удалить) одного водителя (используя кнопку "Критерии");

сотруднице Абрамовой поменяйте фамилию на Иванова (используя кнопку "Критерии").

Рис.8.2

4. Измените структуру таблицы П.8.1.

4.1. Введите дополнительное поле "Оклад" после поля "Пол" и заполните его осмысленными значениями.

4.2. После поля "Оклад" добавьте еще три поля: "Надбавки", "Налоги", "К выплате".

4.3. Установите надбавки в размере 1000 руб. женщинам старше 50 лет. Поиск соответствующих лиц выполняйте через форму.

4.4. В обычном режиме редактирования заполните поле "Налог" - 5% от суммы оклада и надбавки, если она не превышает порога в 10 000 тыс. руб., и 10%, если свыше. При заполнении поля используйте функцию ЕСЛИ.

4.5. Запишите формулу в поле "К выплате" ("Оклад" + "Надбавки" - "Налоги").

5. Предъявите результаты преподавателю.


Задание № 2. Фильтрация данных.

ЦельОсвоить возможности фильтрации табличных данных.

Темы: Фильтрация данных с помощью автофильтра. Расширенная фильтрация и сложные критерии.

1. Фильтрация записей с помощью функции автофильтра.

1.1. Для выполнения задания скопируйте итоговый список сотрудников из Задания №8 в новую книгу.

1.2. Примените автофильтр, пользуясь командой Данные – Сортировка и фильтр - Фильтр, или Главная – Редактирование – Сортировка и фильтр – Фильтр, определите:

сколько в фирме женщин и каков их средний заработок;

троих самых великовозрастных, независимо от пола;

какой максимальный оклад имеет сотрудница, не получающая надбавку;

кто из мужчин живет в центральном районе (телефон начинается от 310 до 315);

каков суммарный заработок у менеджеров и инженеров;

сколько в фирме работает Ивановых, и каков их суммарный оклад;

сколько сотрудников получают больше 3 000 руб., но меньше 10 000 тыс. руб. Сколько из них женщин;

сколько сотрудников получают больше 10 000 руб. или меньше 3000 руб., и кто из них не получает надбавки;

2. Расширенная фильтрация. Для активизации расширенной фильтрации воспользуйтесь командой Данные – Сортировка и фильтр – Дополнительно.

2.1. Для выполнения задания скопируйте "Ведомость оценок" (рис.8.2 из Задания № 8), расположите ее в начале чистого листа; определите область критериев справа от таблицы.

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

тройки по математике;  тройки по математике, но четверки по физике;

двойки по математике, но пять по физике и средний балл больше 3,5 (предварительно добавив в таблицу столбец "Ср. балл");

тройки по математике или тройки по физике;

двойку по любому предмету (хотя бы одну).

2.3. Извлеките (на месте) данные о студентах, имеющих:

средний балл меньше 4;

средний балл больше, чем 3,5 и оценку по математике больше 3;

средний балл не меньше, чем 4,5 или меньше 4, но по биологии - 5;

средний балл больше, чем 3,5, но меньше 42.

2.4. Извлеките только фамилии студентов3:

не имеющих двоек;  имеющих хотя бы одну двойку.

не имеющих двоек и имеющих средний балл не меньше 4;

3. Предъявите результаты преподавателю.


Задание № 3. Связывание таблиц.

ЦельНаучиться создавать связи между таблицами.

Темы: Создание и использование связей между данными. Использование «объемных формул».

1. Создайте три таблицы, содержащие сведения о ценах на программные продукты, по образцу, приведенному на рис.10.1. Для каждого месяца первого квартала на отдельном листе книги Имя_10_1 создается собственная таблица с названием "Прайс-лист (Месяц)", где месяц - Январь, Февраль, Март.

1.1. При создании таблиц организуйте связи между таблицами "Прайс-лист (Январь)" и таблицами "Прайс-лист (Февраль)" и "Прайс-лист (Март)", для чего скопируйте диапазон ячеек А3:В13 январской таблицы цен в буфер, перейдите в таблицу "Прайс-лист (Февраль)" и воспользуйтесь командами Главная – Буфер обмена – Вставить – Вставить связь (или Главная – Буфер обмена – Вставить – Специальная вставка – Вставить связь). Аналогично установите связь с таблицей "Прайс-лист(Март)".

Рис.10.1

1.2. Переменную часть таблиц (столбец "Цена") отредактируйте согласно данным, приведенным на рис.10.1. Переименуйте листы, дав им соответствующие имена (Январь, Февраль, Март).

1.3. Посмотрите, как выглядят ссылки в строке формул при активизации связанных ячеек в таблицах февраля и марта. Изменив содержимое ячейки А7 в январской таблице, просмотрите, как изменится соответствующая ячейка в февральской таблице. Попытайтесь изменить текст в ячейке А7 февральской таблицы и сделайте выводы о направленности установленной связи.

2. Создайте таблицы "Отгрузка (Январь)", "Отгрузка (Февраль)" и "Отгрузка (Март)"по образцу, приведенному на рис.10.2, пользуясь режимом группового заполнения, и дайте листам книги названия: Отгр_ЯНВ, Отгр_ФЕВ, Отгр_МАР.

2.1. В ячейке D4 запишите формулу, обеспечивающую ссылку на таблицу "Прайс_лист (Январь)". Эта формула приведена в строке формул, показанной на рис.10.2 в верхней части.

2.2. Скопируйте формулу в ячейки D5:D13.

2.3. Запишите в ячейку D14 формулу, выполняющую суммирование по столбцу "Итого" (ячейки D4:D13).

2.4. Активизируйте команду Формулы – Зависимости формул – Влияющие ячейки для ячейки D14.

2.5. Установите курсор в ячейку D4 и отобразите влияющие ячейки. Пронаблюдайте, как отображается зависимость от внешней таблицы "Прайс_лист (Январь)", связанной с таблицей "Отгрузка(Январь)". Обратите внимание, как в строке формул выглядит формула со ссылкой на ячейку из другой таблицы, и из каких элементов состоит эта ссылка.

2.6. Сохраните созданную книгу с шестью листами под именем Имя_10_1.

2.7. Сохраните копию книги под именем Имя_10_2.

2.8. Удалите из книги Имя_10_1 листы "Отгр_ЯНВ", "Отгр_ФЕВ" и "Отгр_МАР", сохранив в ней только прайс_листы.

Рис.10.2

3. Оставьте открытыми обе книги. Заполните таблицу "Отгрузка(Февраль)" книги Имя_10_2 , пользуясь "Прайс_листом(Февраль)" книги Имя_10_1.

3.1. В ячейке D4 запишите формулу, обеспечивающую ссылку на таблицу "Прайс_лист (Февраль)". Эта формула приведена в строке формул, показанной на рис.10.3.а, в верхней части.

3.2. Скопируйте формулу в ячейки D5:D13.

4. Заполните таблицу "Отгрузка(Март)" книги Имя_10_2, пользуясь "Прайс_листом(Март)" книги Имя_10_1 аналогично п.3.1 и 3.2

4.1. Закройте книгу Имя_10_1. Просмотрите формулу в D4.Она приведена в строке формул, показанной на рис.10.3.б, в верхней части.

4.2. Запишите в ячейки D14 отгрузочных листов формулу, выполняющую суммирование по столбцу "Итого" (ячейки D4:D13).

Рис.10.3.а     Рис.10.3.б

5. Создайте новую таблицу "Суммарный доход за три месяца", в которой будут сведены итоговые значения выручки за все кварталы за счет организации "трехмерной связи", т.е. связи между одинаковыми клетками однотипных таблиц. Принцип создания такой таблицы представлен на рис.10.4. В создаваемой таблице запишите две формулы для получения одного и того же значения, но в одной из них запишите формулу с непосредственным обращением к каждой таблице, а в другой - с обращением к блоку таблиц, так называемую "объемную" формулу. Примеры записи таких формул приведены на рис.10.4 непосредственно под ячейками В4, В7 и выделены курсивом.

Рис.10.4

6. Предъявите результаты преподавателю.


Задание № 4. Консолидация данных.

ЦельЗнакомство с механизмом консолидации данных.

Темы: Консолидация «по положению». Консолидация «по категориям». Консолидация со связью.

1. Создайте три таблицы, содержащие сведения о поставляемых товарах, по образцу, приведенному на рис.11.1. Для каждого месяца первого квартала на отдельном листе книги Имя_11_1 создается собственная таблица с названием "Поставки товаров в месяце", где месяц - январь, февраль, март. При создании таблиц пользуйтесь режимом "группового заполнения листов" или копирования данных.

1.1. Переменная часть таблиц (столбцы "Объем" и "Дата") должна соответствовать данным, приведенным на рис.11.1. Переименуйте листы, дав им соответствующие имена (Янв, Фев, Мар).

Рис.11.1

2. Вставьте новый лист, дав ему имя "Конс_данные". Скопируйте в него заголовок таблицы и откорректируйте его соответствующим образом (рис.11.3). Установите курсор в первую свободную ячейку (А3).

 

Рис.11.2

2.1. Активизируйте диалоговое окно Консолидация с помощью команд Данные – Работа с данными - Консолидация, и, последовательно указывая в поле Ссылка необходимые адреса консолидируемых областей, сформируйте их полный список, состоящий из трех записей, как представлено на рис.11.2.

2.2. В поле Функция оставьте функцию вычисления суммы и укажите, что в качестве имен (названий строк) будут выбираться данные из левого столбца (А) консолидируемой области. Укажите на необходимость создания динамической связи с исходными данными.

Рис.11.3

2.3. Выполните консолидацию. Сравните полученные результаты с приведенными на рис.11.3.

2.4. Просмотрите созданную структуру, последовательно показывая или скрывая уровни этой структуры. Откройте второй (внутренний) уровень для поставщиков из С.Петербурга и Череповца. Сравните полученный результат с представленным на рис.11.4.

Рис.11.4      Рис.11.5

2.5. Пользуясь командами Формулы – Зависимости формул – Влияющие ячейки, проследите влияющие ячейки для ячеек С7, С10, С38. Убедитесь в правильности полученных результатов.

2.6. Раскройте структуру для первых трех консолидированных данных и просмотрите формулы в столбце С (Формулы – Зависимости формул – Влияющие ячейки – Показать формулы). Верните отображение результатов вычислений.

2.7. Меняя данные в ячейках листов "Янв", "Фев", "Мар", проследите за автоматическим пересчетом общей итоговой суммы (ячейка С38) и частичных сумм в ячейках С10, С26 и т.д.

3. Сохраните созданную книгу с четырьмя листами под именем Имя_11_1.

3.1. Откройте новую книгу и создайте в ней одну таблицу, имеющую аналогичную предыдущим структуру и содержащую данные за второй квартал. Образец такой таблицы на рис.11.5. Назовите лист с таблицей "2кварт". Сохраните созданную книгу под именем Имя_11_2.

3.2. Сверните окно рабочей книги.

Рис.11.6

3.3. На новом листе книги Имя_11_1 выполните консолидацию четырех диапазонов ячеек - трех из листов "Янв", "Фев", "Мар" книги Имя_11_1, а четвертого из соответствующего диапазона книги Имя_11_2 листа "2кварт". Пользуйтесь кнопкой Обзор диалогового окна Консолидация. Обратите внимание на структуру ссылки при задании области консолидации из неактивной книги. Проверьте результат и сравните его с тем, что представлен на рис.11.6.

3.4. Закройте книгу Имя_11_2. Обратите внимание на структуру ссылки при задании области консолидации из закрытой книги.

3.5. Выполните консолидацию данных из четырех таблиц (аналогично пункту 3.3), но задав в качестве обработки данных вычисление среднего значения консолидируемых данных. Сравните полученный результат с представленным на рис.11.7.

4. Добавьте в книгу еще один лист, именовав его как "Конс_данные2", и выполните на этом листе консолидацию данных, расположенных в таблицах листов "Янв", "Фев", "Мар" и "Апр" (структура таблицы листа "Апр" приведена на рис.11.8), обратив внимание на задание консолидируемой области для листа "Апр".

Рис.11.7

4.1. Проверьте правильность структуры таблицы на листе "Конс_данные2", сравнив ее с представленной на рис.11.9.

Рис.11.8     Рис.11.9

5. Предъявите результаты преподавателю.


Задание № 5. Сводные таблицы.

ЦельНаучиться создавать сводные таблицы.

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

1. Создайте новую книгу. На листе этой книги создайте таблицу (рис.12.1), содержащую данные о продажах автомобилей тремя гипотетическими фирмами (Альфа, Бета и Гамма) за первое полугодие 2009 года. Назовите лист "Продажи" и сохраните книгу.

Рис.12.1

2. Выполните команду Вставка – Таблицы – Сводная таблица, предварительно установив курсор в исходную таблицу. В диалоговом окне «Создание сводной таблицы» выберите диапазон данных для анализа и местоположение сводной таблицы (на новом листе).

2.1. Для создания таблицы, приведенной на рис.12.2, заполните макет сводной таблицы в панели Список полей сводной таблицы, поместив поля таблицы в соответствующие области: поле Марка – в Названия строк, поле Фирма – в Названия столбцов, а поле Цена – в область Значения. Полученная в результате этих действий сводная таблица, а также панель Список полей сводной таблицы показаны на рис.12.2.

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

2.3. Познакомьтесь с вкладкой Работа со сводными таблицами. Измените функцию суммирования по полю Цена на среднее значение, установив курсор на числовые данные таблицы и выбрав из контекстного меню команду Итоги по…или команду Работа со сводными таблицами – Параметры – Активное поле – Параметры поля – Операция. Измените название поля на Средняя цена, используя текстовое поле Пользовательское имя в том же диалоговом окне.

2.4. Поменяйте ориентацию строк и столбцов таблицы, пользуясь раскрывающимися списками в областях панели «Список полей сводной таблицы».

2.5. Измените форматирование числовых значений сводной таблицы, применив денежный формат (Работа со сводными таблицами – Параметры – Активное поле – Параметры поля). Примените стили к сводной таблице (Работа со сводными таблицами – Конструктор – Параметры стилей сводной таблицы).

Рис.12.2

2.6. Рассмотрите возможности отображения и сокрытия итогов вычислений (Работа со сводными таблицами – Конструктор – Макет).

2.7. Скройте и восстановите заголовки полей (Работа со сводными таблицами – Параметры – Показать или скрыть – Заголовки полей).

2.8. Постройте диаграмму, отображающую данные сводной таблицы, используя команды Работа со сводными таблицами – Параметры – Сервис – Сводная диаграмма.

3. Сформируйте сводную таблицу, позволяющую оценить количество продаж и сумму продаж трех фирм различных марок машин. Таблица представлена на рис.12.3.

Рис.12.3

4. Сформируйте сводную таблицу, позволяющую оценить сумму продаж и даты продаж. Таблица представлена на рис.12.4. Проанализируйте данные таблицы.

4.1. Сгруппируйте данные по месяцам, затем по кварталам, затем по месяцам и кварталам (Работа со сводными таблицами – Параметры – Группировать – Группировка по полю). Таблица представлена на рис.12.5.а. Проанализируйте данные таблицы.

Рис.12.4

4.2. Примените к датам продажи группировку по выделенному, выделив сначала группу зимнее-летних месяцев, а затем группу остальных. После выделения обратитесь к команде Работа со сводными таблицами – Параметры – Группировать – Группировка по выделенному. Таблица представлена на рис.12.5.б. Проанализируйте данные таблицы.

Рис.12.5.а

Рис.12.5.б

5. Создайте новую сводную таблицу, которая будет отображать суммарную цену и количество проданных каждой фирмой автомобилей в зависимости от года выпуска автомобиля и даты продажи. Сгруппируйте данные о годах выпуска с шагом в пять лет. Таблица представлена на рис.12.6. Проанализируйте данные таблицы.

Рис.12.6

5.1. Постройте диаграммы, отображающие полученные результаты

6. Создайте сводную таблицу, представленную на рис.12.7. Используйте в ней вычисляемое поле Сумма в рублях. Для его создания примените команду Работа со сводными таблицами – Параметры – Сервис – Формулы - Вычисляемое поле. Дайте имя полю Сумма в рублях, а в поле Формула введите формулу =30*Цена.

Рис.12.7

7. Предъявите результаты преподавателю.


Задание № 6. Обмен данными.

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

Темы: Приложения – источники и приемники данных. Технология встраивания и связывания объектов (OLE). Динамический обмен данными.

1. Обмен данными через буфер обмена (вставка).

1.1. Табличный процессор Excel в качестве источника данных.

1.1.1. Запустите табличный процессор Excel и создайте в нем таблицу, приведенную на рис.13.1. Сохраните рабочую книгу под именем Имя_13_1.

1.1.2. Запустите текстовый процессор Word. Откройте окно нового документа. Установите размеры окон в Excel и Word, как показано на рис.13.1. Введите в окне документа Word две строки текста: "Начало документа" и "Продолжение документа".

Рис.13.1

1.1.3. Скопируйте в буфер обмена таблицу из диапазона A1:D5 рабочего листа книги Имя_13_1.

1.1.4. Вставьте в документ Word таблицу из ячеек А1:D5 рабочего листа книги Имя_13_1, используя буфер обмена.

1.1.5. Вставьте в документ Word таблицу из ячеек А1:D5 рабочего листа книги Имя_13_1 как неформатированный текст. Для этого используйте режим специальной вставки и укажите в качестве типа принимаемых данных "Неформатированный текст".

1.1.6. Вставьте в документ Word таблицу из ячеек А1:D5 рабочего листа книги Имя_13_1 как текст в формате RTF. Используйте режим специальной вставки и укажите в качестве типа принимаемых данных "Текст в формате RTF". Образец такой вставки представлен на рис.13.2.

1.1.7. Вставьте в документ Word таблицу из ячеек А1:D5 рабочего листа книги Имя_13_1 как рисунок. Используйте режим специальной вставки и укажите в качестве типа принимаемых данных "Рисунок".

1.1.8. Вставьте в документ Word таблицу из ячеек А1:D5 рабочего листа книги Имя_13_1 как лист Microsoft Excel (объект). Используйте режим специальной вставки и укажите в качестве типа принимаемых данных "Лист Microsoft Excel (объект)".

1.1.9. Внесите в документ комментарии, расположенные перед соответствующим вставленным фрагментом. Комментарий должен содержать текст, поясняющий тип вставленных данных, например "Ниже вставлен текст в формате RTF".

1.1.10. Внимательно познакомьтесь с поведением вставленных объектов при попытке их активизации. Для этого дважды щелкните мышью на каждой из вставленных таблиц.

1.1.11. Особое внимание уделите последней таблице. Внимательно просмотрите, как при активизации вставленного объекта запускается программа-источник данных, и как стандартная панель инструментов текстового процессора Word подменяется панелью Excel.

1.1.12. Оцените возможности и удобства (или неудобства) механизма "Редактирования на месте", который реализуется в данном случае.

Рис. 13.2

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

1.2.1. Запустите текстовый процессор Word. Откройте окно нового документа и создайте в нем таблицу, приведенную на рис.13.3, воспроизведя элементы оформления таблицы (ширина столбца (2 см), шрифт, обрамление и заполнение как показано на рис.13.3).

Рис.13.3

1.2.2. Откройте новый рабочий лист в окне табличного процессора Excel. Установите размеры окон Word и Excel, как показано на рис.13.3.

1.2.3. Вставите созданную таблицу в диапазон ячеек А1:С4, используя буфер обмена операционной системы MS Windows.

1.2.4. Вставьте созданную таблицу в диапазон ячеек А6:С9 как текст, используя режим специальной вставки и указав в качестве типа данных "Текст".

1.2.5. Обратите внимание на то, что происходит с оформлением вставляемых документов. Отформатируйте таблицу в диапазоне А6:С9 таким образом, чтобы она точно соответствовала оригиналу (таблице из текстового процессора Word).

2. Встраивание (внедрение) объектов.

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

2.1.1. Используя таблицу на рис.13.4 как источник, встроить (внедрить) ее в тот же рабочий лист книги Имя_13_1, начиная с ячейки А9 как "Объект Документ Microsoft Word".

2.1.2. Используя таблицу на рис.13.4 как источник, встроить (внедрить) ее в тот же рабочий лист книги Имя_12_1 с ячейки А14 как "Объект Документ Microsoft Word", но заменив ее реальное отображение условной пиктограммой.

Рис.13.4

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

2.1.4. Измените размеры области, занимаемой таблицей, внедренной как "Объект Документ Microsoft Word" (А14...), переместив правую границу внедренного объекта на линию, разделяющую столбцы С и D. Пронаблюдайте поведение объекта.

2.1.5. Отметьте диапазон ячеек рабочего листа, который покрывает все вставленные и внедренные таблицы. Очистите все содержимое ячеек. Убедитесь, что все внедренные объекты сохранились, и сделайте выводы об их поведении и возможностях работы с ними.

2.2. Табличный процессор Excel в качестве контейнера стандартных объектов.

2.2.1. Откройте новый лист в рабочей книге Excel. Используя механизм внедрения (встраивания) объектов, разместите на этом листе все объекты, приведенные на рис.13.5. Для создания этих стандартных объектов следует использовать дополнительные программы "надстройки", входящие в комплект MS Office. Вызов дополнительных программ осуществляется посредством меню Вставка – Текст - Объект и Вставка - Иллюстрации с дальнейшим указанием типа объекта.

Рис.13.5

2.3. Связывание объектов.

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

Рис.13.6

2.3.1.1. Создайте на новом листе книги Имя_13_1 таблицу, приведенную на рис.13.6. В окне документа Word внедрите таблицу из листа Excel, установив связи с таблицей-источником. Тип внедренной таблицы - "Неформатированный текст".

Для выполнения связывания выполните последовательность действий:

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

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

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

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

Рис.13.7

2.3.1.2. В окне документа Word внедрите таблицу из листа Excel, установив связи с таблицей-источником. Тип внедренной таблицы - "Текст в формате RTF". Образец представлен на рис.13.6 (вторая таблица).

Рис.13.8

2.3.1.3. В окне Word внедрите таблицу из Excel, установив связи с таблицей-источником. Тип внедренной таблицы - "Лист Microsoft Excel" (рис.13.7).

2.3.1.4. В окне Word внедрите таблицу из Excel, установив связи с таблицей-источником. Тип внедренной таблицы - "Рисунок" (рис.13.7).

2.3.1.5. Для созданных связанных таблиц просмотрите действие механизма связи, меняя в исходной таблице числовые данные.

2.3.2. Табличный процессор 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. Управление установленными связями.

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

Рис.13.9

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

2.4.1. 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. Обмен графическими объектами.

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

Рис.13.10

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

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

3. Предъявите результаты преподавателю.

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

ЦельЗнакомство с использованием 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

6. Предъявите результаты преподавателю.


Задание № 8. 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 на рабочий лист в свободные диапазоны ячеек.

4. Предъявите результаты преподавателю.

Задание № 9. Таблицы данных.

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

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

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

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

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

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

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 квартале прибыль P4=100000 т.р., изменяя расходы на рекламу Q4;

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

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

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

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

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

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

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

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

- прибыли для четырех кварталов (Pi);

- квартальные значения Qi;

- суммарную (годовую) прибыль (P);

- суммарные годовые расходы на рекламу (Q);

- долю расходов на рекламу в общем доходе от реализации продукции (Q/D).

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

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

11. Постройте на отдельном листе два графика (подобных представленным на рис.18.6), на которых будут отображены зависимости V4=f(Q4) для первого графика и D4=f(Q4) и P4=f(Q4) для второго графика.

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=XY, обеспечивающая вычисления, записана в ячейке D13, а исходные данные Х=2 и У=2 - в ячейках D11 и D12 соответственно.

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

Рис.18.9

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

13.3. Сравните результаты непосредственного вычисления (D11:D13) и результаты двух "подборов параметров" (B11:B13 и C11:C13). Сделайте выводы относительно возможности использования инструмента "подбор параметра".

Задание № 10 (Часть 2). Поиск решения.

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

Темы: Нахождение оптимального значения с помощью «Поиска решения».

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

1.1. Запустите инструмент "поиск решения" (Office – Параметры Excel – Надстройки – в раскрывающемся списке Управление – Надстройки Excel – Перейти – установить флажок Поиск решения), просмотрите все варьируемые параметры поиска, задаваемые с помощью диалогового окна "Поиск решения" (рис.18.10.)

Рис.18.10

1.2. Определите как целевую ячейку, содержащую величину годовой прибыли (P).

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

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

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

затраты на рекламу в каждом квартале (Qi) не могут быть отрицательными;

цена продукции не должна быть меньше себестоимости.

1.6. Выполните поиск решения. Убедитесь, что решение найдено и результаты поиска совпадают с результатами, приведенными на рис.18.11. Занесите полученные результаты в таблицу и сохраните на отдельном листе отчет по результатам поиска решения.

Рис.18.11

2. Предъявите результаты преподавателю.

2 Реализацию логической функции И для данных одного столбца осуществлять повторным размещением в области критериев заголовка данного столбца.

3 Для выполнения этого задания в ячейку, с которой будет начинаться область для извлечения данных, ввести название заголовка столбца "Фамилия".


 

А также другие работы, которые могут Вас заинтересовать

26564. ВСЭ ТУШ И ОРГАНОВ ПРИ ЭМФИЗЕМАТОЗНОМ КАРБУНКУЛЕ 8.51 KB
  Печень пятнистоглинистого цвета имеет очаги красноватожелтого цвета величиной до грецкого ореха иногда в ней содержатся газы. Почки дряблые с некротическими очагами темнокоричневого цвета или глинистые граница между корковым и мозговым слоями сглажена. Мышцы дряблые темнокрасного цвета местами сероватые мышечные волокна деструктированы миолиз при надавливании на них выжимается кровянистый липкий сок содержащий пузырьки газа неприятного прогорклого запаха. Ткани в области карбункула темнокрасного дегтеобразного цвета; рыхлая...
26565. ВСЭ И САНИТАРНАЯ ОЦЕНКА ТУШ И ОРГАНОВ ПРИ ЯЩУРЕ 25.74 KB
  К ящуру также восприимчивы северный олень и верблюд а из диких животных лось олень антилопа кабан косуля сайга бизон зубр. Человек заражается ящуром при употреблении в пищу необезвреженного молока от больных животных а также при доении больных животных или их переработке на мясо. У молодых животных при ящуре находят катар верхних дыхательных путей острый катар желудочнокишечного тракта. У отдельных видов животных эти изменения проявляются следующим образом.
26566. ВСЭ ТУШЕК И ОРГАНОВ ПТИЦЫ ПРИ ПАСТЕРЕЛЛЕЗЕ, ПУЛЛОРОЗЕ, ТУБЕРКУЛЕЗЕ, ЛЕЙКОЗЕ И ОРНИТОЗЕ 10.76 KB
  Изменения у птиц выявляют чаще в печени кишечнике селезенке яичниках и костях реже в легких почках и на серозных покровах. За местную локальную форму туберкулеза у птиц принимают наличие очагов только в кишечнике или печени без поражения кишечника. Туберкулезный процесс с одновременным поражением кишечника и печени или нескольких органов считают г'енерализованным. Патологоанатомически лейкоз у кур чаще проявляется очаговыми или диффузными разрастаниями лимфоидных элементов в печени селезенке яичнике и других органах.
26567. ГИГИЕНА ПОЛУЧЕНИЯ И ПЕРВИЧНАЯ ОБРАБОТКА МОЛОКА НА ФЕРМАХ 6.04 KB
  ГИГИЕНА ПОЛУЧЕНИЯ И ПЕРВИЧНАЯ ОБРАБОТКА МОЛОКА НА ФЕРМАХ. Комплексы по производству молока на промышленной основе это сельскохозяйственные предприятия с круглогодовым производством продукции высокой механизацией производственных процессов автоматическим управлением системами механизмов и наличием в структуре стада 90 дойных коров. ПЕРВИЧНАЯ ОБРАБОТКА И ХРАНЕНИЕ МОЛОКА. Первичную обработку молока выполняют в молочной.
26568. ОБОРУДОВАНИЕ ЛАБОРАТОРИЙ ВСЭ НА КОЛХОЗНЫХ РЫНКАХ 15.81 KB
  Департамент ветеринарии разработал и утвердил нормы затрат времени при проведении ветеринарносанитарной экспертизы пищевых продуктов на рынках а также расчет нагрузки на ветеринарного специалиста. В ней должны быть: комната для регистрации доставленных пищевых продуктов смотровой зал для ветсанэкспертизы мяса и мясопродуктов рыбы и других гидробионтов; смотровой зал для ветсанэкспертизы молока и молочных продуктов; комната для контроля растительных продуктов и меда; кабинет заведующего лабораторией или старшего ветврача; комната для...
26569. ОБЩАЯ СХЕМА ЛИМФООБРАЩЕНИЯ, СТРОЕНИЕ И ТОПОГРАФИЯ ЛИМФАТИЧЕСКИХ УЗЛОВ 6.62 KB
  Л у: нижнечелюстной околоушной поверхностный шейный заглоточный средний боковой; передние средостенные средние средостенные задний средостенный средостенные дорсальные бронхиальный бифуркационный правый бронхиальный эпартериальный л у печени желудочные брыжеечные ободочной кишки аноректальные почечные передний грудной кости межреберные глубокие шейные реберношейный подмышечный подлопаточный 1 ребра коленной складки подколенный седалищный поверхностный паховый подвздошный округлый боковой средний крестцовый...
26570. ОПРЕДЕЛЕНИЕ БАКТЕРИАЛЬНОЙ ЗАГРЯЗНЕННОСТИ МОЛОКА 3 KB
  ОПРЕДЕЛЕНИЕ БАКТЕРИАЛЬНОЙ ЗАГРЯЗНЕННОСТИ МОЛОКА. Бактериальная обсемененность это количество микроорганизмов в 1 см3 молока. Повышенная бактериальная обсемененность результат несоблюдения правил гигиены при производстве молока и его хранении. Технические условия для высшего сорта молока ориентировочное количество бактерий составляет до 300 тыс.
26571. ОПРЕДЕЛЕНИЕ КИСЛОТНОСТИ МОЛОКА 4.16 KB
  ОПРЕДЕЛЕНИЕ КИСЛОТНОСТИ МОЛОКА. Определение кислотности молока и молочных продуктов проводится по ГОСТ 362492.Сущность метода состоит в титровании кислых солей: белков углекислого газа и других компонентов молока раствором щелочи в присутствии фенолфталеина. Кислотность молока является важнейшим биохимическим показателем который учитывается при продаже молоха государству.
26572. ОПРЕДЕЛЕНИЕ КОЛИЧЕСТВА ЖИРА В МОЛОКЕ 3.66 KB
  ОПРЕДЕЛЕНИЕ КОЛИЧЕСТВА ЖИРА В МОЛОКЕ.Определение состояния жира в молоке.Определение содержания жира в молоке. Уровень воды в водяной бане должен быть выше уровня столбика жира в жиромере.