Откройте файл Лабораторные работы. xlsx.
Задание 1. Построить линейчатую диаграмму.
Ваши действия:
1. Перейдите на лист Лаб. 7.
2. Постройте линейчатую диаграмму для данных Задания 1 (см. рис.7.18), которая должна содержать заголовок и подписи данных (рис.9.13).
3. Диаграмму расположите рядом с данными.
Рис.9.13. Линейчатая диаграмма
Задание 2. Построить графики для 3-х функций.
1. На листе Лаб.7 постройте диаграммы для данных Задания 2 (см. рис.7.19) в виде графиков для трех функций Y=SIN(X), Y=1,25*SIN(2*X), Y=1,5*SIN(4*X):
- выделите ячейки В17: D38;
- выберите тип диаграммы – График с маркерами (рис.9.14).
Рис. 9.14. Графики функций
Y=SIN(X), Y=1,25*SIN(2*X), Y=1,5*SIN(4*X)
2. Выделите диаграмму и перейдите на вкладку Конструктор:
- добавьте название диаграммы;
- расположите легенду над диаграммой;
- установите максимальное и минимальное значения на вертикальной оси – 1,500;
- установите цену деления на горизонтальной оси – 1,000.
3. Диаграмму расположите рядом с данными.
Задание 3. Построить график функции Y=SIN(X)+1,25*SIN(2*X)+1,5*SIN(4*X).
1. На листе Лаб.7 выделите ячейки Е17:Е38 (см. рис.7.19).
2. Выберите тип диаграммы – График с маркерами (рис.9.15).
3. Установите максимальное значение на горизонтальной оси – 6,500.
4. Измените полученную диаграмму, добавив заголовок и удалив легенду.
Рис.9.15. График функцииY=SIN(X)+1,25*SIN(2*X)+1,5*SIN(4*X)
Вопросы для самоконтроля
1. Какие виды диаграмм существуют в Excel?
2. Какие новые вкладки появляются на Ленте, если выделить диаграмму?
3. Сколько существует типов диаграмм?
Тесты
1. Сколько существует типов диаграмм?
A. 10
B. 11
C. 12
D. 13
2. Можно ли изменить параметры диаграммы после ее построения?
A. Можно изменить только размер диаграммы.
B. Можно изменить тип диаграммы, ряд данных, расположение диаграммы, ее размеры и т.д.
C. Можно изменить все, кроме типа диаграммы.
D. Ничего изменить нельзя: диаграмму надо построить заново.
3. Для построения диаграммы в таблице необходимо выделить …
A. Заголовки строк таблицы.
B. Заголовки столбцов таблицы.
C. Итоговую строку.
D. Числовые данные.
4. Для круговой диаграммы необходимо выделить …
A. Заголовки строк таблицы.
B. Заголовки столбцов таблицы.
C. Один столбец чисел.
D. Несколько столбцов с числами.
5. Диаграмму можно разместить …
A. На отдельном листе.
B. На имеющемся листе.
C. На специальном листе.
6. Что следует сделать для выделения диаграммы?
A. Сделать щелчок левой кнопкой мыши по имени любой строки, на которой находится диаграмма.
B. Сделать щелчок левой кнопкой мыши по имени любого столбца, на котором находится диаграмма.
C. Сделать щелчок левой кнопкой мыши по диаграмме.
D. Выделить область листа, на которой находится диаграмма.
7. Что произойдет с диаграммой при удалении таблицы, для которой она была создана?
A. Ничего.
B. Диаграмма будет удалена.
C. Останется пустая диаграмма.
D. Таблицу нельзя удалить, пока не удалена диаграмма.
8. Какой командой следует воспользоваться для добавления подписей значений?
A. Конструктор, Добавить элемент диаграммы, Оси.
B. Конструктор, Добавить элемент диаграммы, Подписи данных.
C. Конструктор, Добавить элемент диаграммы, Таблица данных.
D. Конструктор, Добавить элемент диаграммы, Легенда.
9. Какую область листа необходимо выделить при создании диаграммы, отображающей данные за 1, 2, 3 и 4 кварталы?
A. Никакую. Область листа будет выделена автоматически
B. А35:Е38
C. А35:Е39
D. А35:F39
10. Какую область листа необходимо выделить при создании круговой диаграммы, отображающей данные за 4 квартал?
A. А35:А38;Е35:Е38
B. А35:А39;Е35:Е39
C. А35:Е38
D. A35:F39
11. Какой вкладкой следует воспользоваться для создания диаграммы?
A. Вставка.
B. Разметка страницы.
C. Данные.
D. Вид.
12. Какие новые вкладки появляются на Ленте, если выделить диаграмму?
A. Разметка страницы.
B. Конструктор.
C. Макет.
D. Формат.
13. На какой вкладке находятся элементы для изменения типа диаграммы (например, с круговой на гистограмму)?
A. Разметка страницы.
B. Конструктор.
C. Макет.
D. Формат.
14. Что следует сделать для обновления диаграммы при изменении значений в таблице, для которой построена диаграмма?
A. Заново создать диаграмму.
B. Перед изменением данных необходимо выделить диаграмму.
C. Выделить диаграмму и нажать клавишу клавиатуры F9.
D. Ничего делать не надо, диаграмма изменится автоматически.
15. Что произойдет со значениями в таблице при удалении диаграммы?
A. Значения в ячейках, для которых создавалась диаграмма, будут удалены.
B. Значения в ячейках, для которых создавалась диаграмма, будут удалены, а также будут удалены значения во всех влияющих ячейках.
C. Значения в ячейках, для которых создавалась диаграмма, будут удалены, а также будут удалены значения во всех зависимых ячейках.
D. Ничего не произойдет.
16. Можно ли изменить цену делений на вертикальной оси диаграммы?
A. Да, можно установить любую цену делений.
B. Да, но цена деления должна быть кратна 10.
C. Да, но только при изменении значений ячеек в таблице.
D. Нельзя изменить.
17. Как можно изменить подписи (метки делений) на горизонтальной оси диаграммы?
A. Выделить ось и ввести с клавиатуры другие подписи.
B. Выделить ось и воспользоваться окном "Формат оси".
C. Выделить диаграмму и воспользоваться окном "Формат области диаграммы".
D. Нельзя изменить без изменения значений ячеек в таблице.
18. Как изменить подписи на легенде диаграммы?
A. Выделить легенду и ввести с клавиатуры другие подписи.
B. Выделить легенду и воспользоваться окном "Формат легенды".
C. Выделить последовательно каждый элемент легенды и воспользоваться окном "Формат элемента легенды".
D. Нельзя изменить без изменения значений ячеек в таблице.
19. Как изменить положение диаграммы на листе?
A. Никак нельзя.
B. Перетащить за любую границу диаграммы.
C. Перетащить за любую часть выделенной диаграммы.
20. Перетащить за любой маркер диаграммы.
21. Для какой цели можно использовать команду Переместить диаграмму? (выберите все правильные ответы)
A. Для перемещения диаграммы на отдельный лист данной книги.
B. Для перемещения диаграммы на другой лист данной книги.
C. Для перемещения диаграммы в другую книгу.
D. Для перемещения листа с диаграммой в другую книгу.
22. В какой вкладке находятся элементы для оформления диаграммы?
A. Разметка страницы.
B. Конструктор.
C. Макет.
D. Формат.
23. В какой вкладке находятся элементы для установки точного размера диаграммы?
A. Разметка страницы.
B. Конструктор.
C. Макет.
D. Формат.
ГЛАВА 10. АНАЛИЗ ДАННЫХ
Одно из наиболее важных достоинств MS Excel состоит в том, что программа позволяет легко и быстро выполнять анализ «что-если». Вы можете изменять основные переменные и сразу же видеть результаты этих изменений.
Автоматическое обновление вычислений обеспечивает интерактивную обратную связь с вашими экспериментами «что-если». Если для модели установлен автоматический пересчет, то можно изменить значение в ячейке и тут же увидеть результаты пересчета во всех ячейках, которые зависят от измененного значения.
Команда Анализ «что-если» находится на вкладке Данные в разделе команд Работа с данными и содержит три команды, показанные на рис. 10.1.
Рис. 10.1. Перечень команд анализа данных
Подбор параметра
С помощью команды Подбор параметра можно определить неизвестное значение, которое будет давать желаемый результат.
Задание 1. Поиск заданного значения с помощью механизма подбора параметра.
Необходимо узнать количество сдаваемых в прокат видеокассет, чтобы прокатный бизнес не был убыточен. Другими словами, нужно узнать, сколько должно быть в прокате кассет, чтобы оплатить суммарные расходы и получить заданный чистый доход. Для этого можно использовать анализ «что-если» путем подбора параметра.
Ваши действия:
1. Откройте файл Упражнения.xlsx.
2. Вставьте в рабочую книгу новый лист с именем Упр.10.
3. Введите исходные данные, показанные на рис. 10.2.
4. Для расчетных полей используйте формулы:
- Суммарный доход =В2*В3
- Чистый доход =В4–В5
Рис. 10.2. Исходные данные
5. Выделите ячейку с формулой, которую нужно проанализировать (в данном примере это В6).
6. Перейдите на вкладку Данные и в разделе команд Работа с данными выберите команду Анализ «что-если».
7. Выберите команду Подбор параметра (см. рис. 10.1).
8. В появившемся диалоговом окне введите 0 в поле Значение (т.е. задайте то значение, которое хотите получить), а в поле Изменяя значение ячейки – $В$3 (т.е. укажите ссылку на ячейку, которую хотите изменить) (рис. 10.3).
9. Нажмите на клавишу ОК.
Рис. 10.3. Диалоговое окно Подбор параметра
10. Excel подсчитает значение с помощью анализа «что-если» и покажет вам окно результата подбора параметра (рис. 10.4).
Рис. 10.4. Диалоговое окно Результат подбора параметра
11. Если хотите обновить данные на листе, нажмите на клавишу ОК.
Обратите внимание, что при открытом диалоговом окне Результат подбора параметра результат вычислений виден в ячейке В3 – это значение 100.
12. Нажмите на клавишу Отмена, чтобы оставитьна рабочем листе исходные данные.
13. Проверьте, что активной является, по-прежнему, ячейка В6, т.е. та, в которой есть формула для расчета чистого дохода.
14. Выполните команду Подбор параметра
15. Установите в поле Значение новое число – 50000р.
16. Установите в поле Изменяя значение ячейки – $В$2. В результате Excel рассчитает новое значение Платы за прокат – это 1000р.
17. Нажмите на клавишу Отмена, чтобы снова оставитьна рабочем листе исходные данные.
Обратите внимание, что возможность отказаться от результатов подбора параметров делает этот способ удобным при анализе альтернативных вариантов.
Таблица данных с одной переменной
Необходимо узнать, как будет меняться Чистый доход при изменении Платы за прокат. Это можно сделать, используя первый способ, используя механизм подбора параметра. Однако в этом случае пришлось бы повторять всю процедуру несколько раз для каждого значения Платы за прокат. Гораздо более быстрым и простым способом является использование таблицы данных с одной переменной, которая проверяет воздействие выбранной переменной на несколько формул.
Задание 2. Создать таблицу данных для одной переменной с расположением исходных данных по строкам.
Ваши действия:
1. В столбец D (Плата за прокат) введите значения, для которых необходимо найти альтернативные варианты подсчета Чистого дохода: 1000р., 1250р. и т.д. (для ввода исходных используйте режим автозаполнения) рис. 10.5.
Рис. 10.5. Таблица данных с расположением переменных по строкам
2. В ячейку E 2 введите формулу, которая возвращает значение Чистого дохода (т.е. просто нужно сослаться на соответствующую ячейку, где эта формула уже имеется - это ячейка В6).
Обратите внимание, что таблица данных с одной переменной, при условии, что сами значения переменной расположены в каждой новой строке, будет работать только в том случае, если будут соблюдены следующие два условия расположения формулы: формула должна находиться в строке над первым расчетным значением, т.е. на одну ячейку выше и правее столбца с исходными данными.
3. Выделите диапазон ячеек D 2: E 11 (таблица должна выделяться без заголовков).
4. Откройте вкладку Данные, в группе команд Работа с данными выполните команду Анализ «что-если», затем команду Таблица данных.
5. В появившемся диалоговом окне Таблица данных в поле Подставлять значения по строкам в введите ссылку на входную ячейку, для которой должны быть изменены исходные значения в таблице, т.е. $В$2 (рис. 10.6) и нажмите на клавишу ОК.
Рис. 10.6. Диалоговое око Таблица данных для построения таблицы подстановки с одной переменной
6. Результат расчета Чистого дохода появится в столбце E (см. рис. 10.5).
Задание 3. Создать таблицу данных для одной переменной с расположением исходных данных по столбцам.
Ваши действия:
1. В ячейки В14:J14 введите значения Количества кассет от 100 до 300 с помощью режима автозаполнения с шагом 25 (рис. 10.7).
Рис. 10.7. Таблица данных с расположением
значений переменных по столбцам
Обратите внимание, что при создании таблицы данных для одной переменной, при условии, что сами значения переменной расположены в каждом новом столбце, необходимо выполнять следующее два правила: формула для расчета должна находиться ниже на одну ячейку исходных данных и левее столбца c исходными данными.
2. В ячейку А15 введите формулу для расчета Чистого дохода или ссылку на готовую формулу, например, на ячейку В6, как показано на рис. 10.7.
3. Выделите диапазон ячеек А14:I15.
4. Откройте диалоговое окно Таблица данных.
5. В поле Подставлять значения по столбцам в укажите ссылку на ячейку В3, чтобы подсчитать Чистый доход в зависимости от переменной Количество кассет (рис. 10.8).
Рис. 10.8. Диалоговое око Таблица данных для построения таблицы подстановки с одной переменной
6. Нажмите на клавишу ОК. В результате произойдет заполнение таблицы расчетными данными Чистого дохода (см. рис. 10.7).
Таблица данных с двумя переменными
Необходимо узнать, как будет изменяться Чистый доход при изменении двух переменных: Платы за прокат и Количества кассет, находящихся в прокате.
Для решения этой задачи необходимо использовать таблицу данных с двумя переменными, которая позволит проверить влияние двух переменных на одну формулу.
Задание 4. Создать таблицу данных для двух переменных.
Ваши действия:
1. В ячейки H 3:P3 введите количество кассет от 100 до 300 с шагом 25, а в ячейки G 4:G12 введите значения Платы за прокат от 1000р. до 3000р. с шагом 250 (рис. 10.9).
2. На пересечении исходных данных в ячейку F3 введите ссылку на формулу, расположенную в ячейке В6.
Рис. 10.9. Готовая таблица данных для двух переменных
3. Правильно выделите диапазон ячеек для построения таблицы данных – G3: P12. Это очень важный этап, т.е. необходимо захватить формулу, оба диапазона переменных и пустые ячейки, куда будут внесены расчетные значения Чистого дохода.
4. Перейдите на вкладку Данные, в разделе Работа с данными выполните команду Анализ «Что, если…».
5. Введите в поле Подставлять значения по столбцам в адрес ячейки, в которой храниться значение, определяющее количество кассет – это ячейка В3 (рис. 10.10).
Рис. 10.10. Диалоговое око Таблица данных для построения таблицы подстановки с двумя переменными
6. Введите в поле Подставлять значения по строкам в адрес ячейки, в которой хранится значение, определяющее Плату за прокат – это ячейка В2.
7. Нажмите на клавишу ОК и проанализируйте заполненную таблицу.
