ЗАДАНИЕ №1
Цель работы:
Приобрести навыки работы с таблицами, изучить приемы ввода констант и формул в ячейки рабочего листа освоить технологию создания и форматирования диаграмм.
Постановка задачи:
1. Выбрать вариант I= (N mod 25) +1, где N - последние две цифры зачетной книжки, а I - остаток от деления N на 25
2. Вычислить значения функции на заданном интервале с заданным шагом изменения аргумента в соответствии с вариантом из таблицы 1.1. Результаты оформить в виде таблицы. Первый столбец таблицы должен содержать значения аргумента x, второй столбец – значения функции y. Для ввода значений x использовать метод автозаполнения, для вычисления значений y – встроенную логическую функцию ЕСЛИ.
3. Построить график функции. На графике:
разными маркерами выделить максимальное и минимальное значения функции;
дать названия диаграмме и осям;
метки на оси X представить в формате с фиксированной точкой, а на оси Y – с плавающей точкой.
4. Вычислить значения функции на заданном интервале с заданным шагом изменения аргумента в соответствии с вариантом из таблицы 2.2, результаты оформить в виде таблицы. Построить поверхность
Вариант индивидуального задания № 6.
Таблица 1.1 – Вариант задания для построения графика
| № вар | Функция | Интервал изменения аргумента х | Шаг изменения аргумента х |
y = 
|
| 0.2 |
Таблица 1.2 – Варианты заданий для построения поверхности
| № вар | Функция | Интервал изменения аргументов х,y | Шаг изменения аргументов х,у |
| 
| 0,1 |
Решение
Создадим книгу Excel с именем Вариант 6 и присвоим первому листу название Табл 1.1. Откроем данный лист и введем данные которые представлены в таблице 1.1.
Называем ячейки А1 – х и В1 – y. В ячейку А1 вводим значение аргумента х которое равно -1, а в ячейку А2 вводим -0,8. Затем выделяем эти ячейки и с помощью маркера автозаполнения перетаскиваем до значения 1. Итог автозаполнения представлен на (рис.1)
Рис. 1 Автозаполнение аргумента х
В ячейку В2 вводим формулу; =ЕСЛИ(A2<0;(A2/4)-2*A2^2;EXP(A2)) нажимаем Enter и перетаскиваем маркером. Таблица примет вид как на (рис.2)
Рис.2 Нахождения значений Y
Выделяем диапазон ячеек А1:В12 и на вкладке Вставка в окне диаграммы выбираем Точечная с гладкими кривыми. Результат показан на (рис. 3)
Рис.3 График функции y(x)
Для того, чтобы выделить минимальное и максимальное значения функции в ячейке В14 вычислим максимальное значение =МАКС(B2:B12) и в ячейке В15 минимальное значение =МИН(B2:B12).
Рис. 4 Максимальное и минимальное значения функции
Рис. 5 Окончательный вид графика функции
Переименуем лист 2 в Табл 1.2. В ячейку А2 вводим значение аргумента х которое равно -1, а в ячейку А2 вводим -0,9. Затем выделяем эти ячейки и с помощью маркера автозаполнения перетаскиваем до значения 1. В ячейку В1 вводим значение аргумента y которое равно -1, а в ячейку С1 вводим -0,9. Затем выделяем эти ячейки и с помощью маркера автозаполнения перетаскиваем до значения 1. В ячейку В2 вводим формулу: =5*$A2^2*(COS(C$1))^2-2*C$1^2*EXP(C$1). Итог автозаполнения представлен на (рис.6)
Рис. 6
Затем на вкладке Вставка выбираем построение поверхности (рис.7)
Рис.7
Рис. 8 Построенная поверхность
ЗАДАНИЕ №2
ОБРАБОТКА СПИСКОВ
Цель:
Получение практических навыков при работе с данными в Excel- таблицах, организованных в виде списков, изучение принципов выполнения операций сортировки данных (строк / столбцов) и поиска записей с использованием фильтров.
Постановка задачи:
1. На рабочем листе Excel создать список Работники магазинов (рис.1).
Рис.9. Задание для выполнения
2. Скопировать таблицу три раза. Отсортировать полученные списки:
первый по полю Заработная плата по убыванию;
второй по должности (первый ключ сортировки) и фамилии (второй ключ сортировки) в алфавитном порядке;
третий – по столбцам по возрастанию.
3 С помощью автофильтра в одной из таблиц отобрать:
- сведения о кассирах;
- записи по работникам с зарплатой от 11000 до 12000;
- сведения о работниках, фамилии которых начинаются на буквы А или В
- записи по работникам магазинов с номерами от 40 до 49.
- сведения о работнике с наибольшей зарплатой
- сведения о работниках с зарплатой ниже среднего значения
4 Выполнить задания из п.2.3, используя расширенный фильтр.
5 Подвести итоги:
- вывести среднюю зарплату по каждому магазину;
- определить количество работников, занимающих определенную должность, и суммарную зарплату для каждой должности..
6. Построить сводные таблицы:
- для определения средней зарплаты работников, занимающих определенную должность, по всем магазинам.
- для определения суммарной заработной платы в каждом магазине по всем должностям и для каждой должности в отдельности. Для этого выбрать значения поля Фамилия в качестве заголовков строк, поле № магазина в качестве заголовков столбцов, Должность – фильтр, Заработная плата – в качестве значений.
Построить сводную диаграмму, отражающую зависимость максимальной заработной платы в каждом магазине от номера магазина
Решение.
Создадим таблицу согласно задания и скопируем ее на три листа книги.
Сортировка
Таблицу 1 отсортируем по полю Заработная плата по убыванию (рис.10)
Таблицу 2 отсортируем по должности (первый ключ сортировки) и фамилии (второй ключ сортировки) в алфавитном порядке (Рис.11)
Таблицу 3 отсортируем по столбцам по возрастанию (Рис.12)
Рис. 10. Сортировка Заработной платы по убыванию
Рис. 11. Сортировка по должности и фамилии
Рис. 12. Сортировка по всем столбцам по возрастанию
Автофильтр
Рис. 13. Результат применения автофильтра
Расширенный фильтр
Для использования расширенного фильтра необходимо иметь три таблицы: исходную, с условием и результирующую.
Для таблицы с условием в диапазоне А19:А20 пишем таблицу условием «Кассир».
| Должность |
| Кассир |
Рис. 14 Таблица с условием
Ставим курсор в исходную таблицу, на вкладке Данные в группе Фильтр нажимаем кнопку Дополнительно и выбираем диапазоны исходной таблицы, таблицы с условием и шапку итоговой таблицы, как показано на (рис. 15):
Рис. 15. Окно расширенного фильтра
Результат выполнения расширенного фильтра:
Рис.16 Таблица сведения о кассирах
Ниже представлены остальные таблицы полученные с помощью расширенного фильтра.
Рис.17. Таблицы построенные с помощью расширенного фильра
Итоги
Для вывода средней зарплаты по каждому магазину, копируем созданную таблицу на лист Итоги.
Сортируем данные по полю № магазина. На вкладке Главная в группе Редактирование нажимаем кнопку Сортировка и фильтр – Настраиваемая сортировка – устанавливаем сортировку по полю № магазина:
Рис.18 Сортировка по полю № магазина
Проведем итоги. Выделяем всю заполненную таблицу, на вкладке Данные в группе Работа с данными нажимаем кнопку промежуточные итоги. В окне промежуточные итоги выбираем При каждом изменении – № магазина, операция Сумма, Добавить итоги по –Заработная плата.
Рис. 19 Окно Промежуточные итоги
Проведенные итоги по заданию показаны на рис.20.
Рис.20 Результат выполнения промежуточных итогов
Далее определим количество работников, занимающих определенную должность, и суммарную зарплату для каждой должности. Сортируем данные по полю Должность. На вкладке Главная в группе Редактирование нажимаем кнопку Сортировка и фильтр – Настраиваемая сортировка – устанавливаем сортировку по полю Должность:
Рис.21 Результат выполнения промежуточных итогов
Сводные таблицы
Для определения средней зарплаты работников, занимающих определенную должность, по всем магазинам
Решение.
Ставим курсор в таблицу1 и на вкладке Вставка нажимаем кнопку Сводная таблица.
На новом листе формируем сводную таблицу. Перетягиваем поля в указанные области:
Поле Должность в названия строк;
Поле № магазина в названия столбцов
Поле Фамилия в Фильтр отчета и поле Заработная плата в значения.
Раскрываем список в поле Заработная плата, выбираем команду Параметры и нажимаем на функцию Среднее.
Сводная таблица сформирована.
Рис.22 Сводная таблица 1
Сводные таблицы
- для определения суммарной заработной платы в каждом магазине по всем должностям и для каждой должности в отдельности. Для этого выбрать значения поля Фамилия в качестве заголовков строк, поле № магазина в качестве заголовков столбцов, Должность – фильтр, Заработная плата – в качестве значений.
Рис.22 Сводная таблица 2
Сводная диаграмма
Ставим курсор в таблицу1 и на вкладке Вставка нажимаем кнопку Сводная диаграмма.
На новом листе формируем сводную диаграмму. Перетягиваем поля в указанные области:
Поле № магазина в названия строк;
Поле Заработная плата в значения.
Раскрываем список в поле Заработная плата, выбираем команду Параметры и нажимаем на функцию Максимум.
Сводная диаграмма сформирована.
Рис.24 Сводная диаграмма
Вывод: после выполнения работ приобретены навыки работы с таблицами, изучены приемы ввода констант и формул в ячейки рабочего листа освоены технологии создания и форматирования диаграмм.