Порядок выполнения задания
1. Выбрать вариант I= (N mod 25 ) +1, где N - последние две цифры зачетной книжки, а I- остаток от деления N на 25
2. Вычислить значения функции на заданном интервале с заданным шагом изменения аргумента в соответствии с вариантом из таблицы 1.1. Результаты оформить в виде таблицы. Первый столбец таблицы должен содержать значения аргумента x, второй столбец – значения функции y. Для ввода значений x использовать метод автозаполнения, для вычисления значений y – встроенную логическую функцию ЕСЛИ.
3. Построить график функции. На графике:
- разными маркерами выделить максимальное и минимальное значения функции;
- дать названия диаграмме и осям;
- метки на оси X представить в формате с фиксированной точкой, а на оси Y – с плавающей точкой.
4. Вычислить значения функции на заданном интервале с заданным шагом изменения аргумента в соответствии с вариантом из таблицы 2.2, результаты оформить в виде таблицы. Построить поверхность
Варианты индивидуальных заданий
Таблица 1.1 – Варианты заданий для построения графика
| № вар | Функция | Интервал изменения аргумента х | Шаг изменения аргумента х | |
 y = 
|
| 0.2 | ||
y = 
|
| 0.4 | ||
y = 
|
| 0.2 | ||
y = 
|
| 0.2 | ||
y = 
|
| 0.2 | ||
y = 
|
| 0.2 | ||
y = 
|
| 0.1 | ||
y = 
|
| 0.2 | ||
y = 
|
| 0.2 | ||
y = 
|
| 0.2 | ||
y = 
|
| 0.2 | ||
y = 
|
| 0.2 | ||
y = 
|
| 0.2 | ||
y = 
|
| 0.2 |
Таблица 1.2 – Варианты заданий для построения поверхности
| № вар | Функция | Интервал изменения аргументов х,y | Шаг изменения аргументов х,у |
| 
| 0.1 | |
| 
| 0.2 | |
|
| 0.2 | |
|
| 0.2 | |
|
| 0.1 | |
|
| 0,1 | |
|
| 0,2 | |
|
| 0,1 | |
|
| 0,2 | |
|
| 0,2 | |
|
| 0,1 | |
|
| 0,1 | |
|
| 0.1 | |
|
| 0.1 |
Содержание отчета
Цель работы, постановка задачи, основные шаги формирования таблиц и построения диаграмм, результаты моделирования, выводы.
ЗАДАНИЕ №2
ОБРАБОТКА СПИСКОВ
Цель – получение практических навыков при работе с данными в Excel- таблицах, организованных в виде списков, изучение принципов выполнения операций сортировки данных (строк / столбцов) и поиска записей с использованием фильтров.
Краткие теоретические сведения
Создание списков
Основным способом представления данных в Microsoft Excel является список.
Список в Excel – снабженная метками последовательность строк рабочего листа, содержащих в одинаковых столбцах данные одного типа. Списки можно рассматривать как внутренние базы данных, записями в которых являются строки, а полями – столбцы.
Первая строка списка список отводится для указания имен полей. При создании списка необходимо выполнять следующие правила:
1. На одном листе не следует помещать более одного списка;
2. Следует отделять список от других данных на листе, хотя бы одной пустой строкой и пустым столбцом;
3. Для имен столбцов следует использовать шрифт, тип данных, выравнивание, формат, рамку отличные от тех, которые используются для данных списка.
4. Чтобы отделить имена столбцов от данных, следует поместить рамку по нижнему краю ячеек первой строки.
Сортировка списков
Список можно отсортировать по возрастанию, по убыванию или в заданном пользователем порядке. Чтобы отсортировать весь список необходимо выделить список либо одну ячейку в списке и выбрать в на ленте команду Данные – Сортировка.
Рис.2.1 – Окно сортировки
Сортировка строк. В полях Сортироватьпо и Затем понеобходимо указать (выбрать из раскрывающегося меню) имена сортируемых столбцов (ключи сортировки), порядок сортировки (возрастание или убывание) и нажать кнопку OK. Поле Затем по(второй ключ сортировки)используют, если в поле первого ключа есть одинаковые значения. Поле Затем поподключается нажатием кнопки Добавить уровень.
Сортировка столбцов. В окне команды Сортировка нажать кнопку Параметры. Установить переключатель Сортировать в положение Столбцы диапазона и нажать кнопку OK. Выбор строк, по которым требуется отсортировать столбцы производится в полях Сортировать по и Затем по.
Сортировка в задаваемом пользователем порядке. Требуемый порядок сортировки задается в окне Сортировка - Параметры.Для этогонеобходимовызвать раскрывающееся меню в поле Сортировка по первому ключу.Пользовательский (настраиваемый) список порядка сортировки необходимо создать заранее с помощью команды Сервис-Параметры-Списки либо из существующих на листе элементов (их необходимо выделить и нажать кнопку Импорт), либо с помощью непосредственного ввода элементов списка.
Для быстрой сортировки на ленте на вкладке Данные находятся кнопки со стрелками вверх и вниз. Ключевым полем для сортировки в этом случае является столбец с выделенной ячейкой.
Применение фильтров
С помощью фильтров можно выводить и просматривать только те данные, которые удовлетворяют определенным условиям.
Для этих целей есть две команды Автофильтр и Расширенный фильтр. Для применения фильтров необходимо выделить список или установить курсор на любую ячейку списка.
Автофильтр активизируется путем выбора команды Данные - Фильтр. В первой строке таблицы появляются кнопки с изображением стрелки. Щелкнув по такой кнопке, можно выбрать поле, для которого осуществляется поиск записей. Если в открывшемся окне задать любую константу, соответствующую значению в поле, в результате на выходе фильтра оказывается только одна запись. ПараметрВсе восстанавливает в таблице все записи.
С помощью автофильтра можно устанавливать сложные пользовательские критерии. Для этого в раскрывающемся списке необходимо выбрать строку Числовые (текстовые) фильтры, Настраиваемый фильтр, в результате чего появится диалоговое окно.
Левые поля диалогового окна задают операцию отношения, которая выбирается из меню (больше, меньше, равно, … для чисел, «содержит», «не содержит»… для текстов). Правые поля должны содержать сравниваемые значения. Можно использовать символы «?» и «*» вместо любого символа или группы символов соответственно. Критерии типа “И”,“ИЛИ” могут быть заданы для отдельного поля. Если задать условия для нескольких полей, то будут выбраны записи, для которых одновременно выполняются заданные условия (критерий “И”). Пример использования автофильтра приведен на рис.2.2.
Рис.2.2. Выбор строк, для которых значения в поле «Доход» находятся в диапазоне 2000…3000.
При необходимости использовать более сложные критерии отбора записей используется Расширенный фильтр (команда Данные – Дополнительно). В отличие от автофильтра результаты фильтрации можно вынести в другое место рабочего листа.
Чтобы воспользоваться Расширенным фильтром, необходимо предварительно создать таблицу критериев, расположив ее на том же листе. Обычно для этого копируют имена полей списка на свободное пространство. В ячейку, расположенную под именем поля, для которого осуществляется поиск записей, записывается условие отбора. Пример использования расширенного фильтра приведен на рис 2.3.
Рис.2.3. Выбор строк, для которых значения в поле «Доход» превышают 2000 и название фирмы начинается на «Ю».
При использовании расширенного фильтра необходимо выделить область, содержащую данные (A2:B4). Активизировать фильтр путем выбора пункта Данные - Дополнительно. Подтвердить область исходной таблицы и указать диапазон критерияD2:E3.На выходе фильтра появятся записи, соответствующие заданному критерию.
Результирующую таблицу можно помещать, как на место исходного списка, так и вне его, задав в диалоговом окне левую верхнюю ячейку будущей таблицы результата.
Аналогично в ячейку под соответствующей меткой столбца можно вставить другой критерий. При этом критерию “И” соответствует несколько условий, задаваемых в одной строке (для нескольких полей). Несколько условий в различных строках интерпретируются как критерий “ИЛИ”.
Расширенный фильтр используется и в том случае, если требуется вычисляемый критерий. Для записи такого критерия нужна формула. К формулам в таблице критериев расширенного фильтра предъявляются следующие требования:
1) формула должна выводить логическое значение ЛОЖЬ или ИСТИНА. После выполнения поиска на экран выводятся только те строки, для которых результатом будет ИСТИНА;
2) формула должна ссылаться хотя бы на один столбец (первое значение в столбце) в списке
3) в таблице критериев заголовок над формулой не выполняется, но пустая ячейка над формулой включается в диапазон критериев.
Примерный вид вычисляемого критерия: =Е4>СРЗНАЧ($E$4:$E$18)
где Е4:Е18 данные для вычисления среднего, используется абсолютная адресация ячеек (знак $).
Подсчет итогов в списках
Команда Промежуточные итоги вкладки Данные позволяет добавить в таблицу итоговые строки для каждой группы данных в списке, а также выполнить другие расчеты на групповом уровне. Можно подвести общие итоги, т.е. применить выбранную функцию (сумма, среднее и т.д.) ко всему списку, задать место размещения общих итогов в начале или в конце списка. При подведении итогов список структурируется, и с помощью символов структуры можно просматривать данные на нужном уровне детализации.
Чтобы подвести итоги, необходимо:
Выделить ячейку в столбце, содержащем данные, для которых необходимо подвести итоги. Столбец должен содержать одинаковые значения. Отсортировать список по значениям этого столбца. Например, если Вы работаете с таблицей, содержащей информацию о продавцах и проданных ими товарах, то, чтобы найти общее количество товаров, проданных каждым продавцом, требуется отсортировать список по столбцу продавцов.
Выбрать команду Промежуточный итог в меню Данные, раздел Структура.
3 Заполнить диалоговое окно Промежуточные итоги: в поле При каждом изменении в указать имя поля, содержащего отсортированные значения. В поле Операциявыбрать функцию (Сумма, Среднее, Количество). Заполнить поле Добавить итоги по (имя числового поля).
- Флажки Заменить текущие итоги и Итоги под данными должны быть установлены. Затем щелкнуть кнопку ОК.
- С помощью структуры списка можно вывести: общие итоги, щелкнув по символу уровня 1; общие и промежуточные итоги, щелкнув по символу уровня 2; полный список, щелкнув по символу уровня 3;
- Для удаления итогов и структуры необходимо щелкнуть по кнопке Убрать все в окне Промежуточные итоги.
- Для получения нескольких итоговых формул в одном столбце необходимо несколько раз выполнить командуПромежуточные итоги. При повторном выполнении этой команды сбросить флажок Заменить текущие итоги.
Сводная таблица
Отчет сводной таблицы представляет собой интерактивную таблицу, с помощью которой можно быстро обобщать большие объемы данных. Можно менять местами строки и столбцы для получения различных сводок по исходным данным, фильтровать данные, отображая различные страницы, либо отображать подробности по нужным областям.
Для создания сводной таблицы необходимо выделить диапазон ячеек, на основании которого будет создаваться сводная таблица (в этот диапазон должны войти заголовки столбцов).На вкладкеВставка в разделе Таблицынажать кнопку Сводная таблица.
В результате открывается панель Список полей сводной таблицы для создания ее макета. Необходимо перетянуть из верхней области панели Список полей сводной таблицыв область Названия строкназвание того столбца (поля), значения в котором будут в сводной таблице названиями строк. Перетянуть из верхней области панели Список полей сводной таблицыв область Названия столбцовназвание того столбца (поля), значения в котором будут в сводной таблице названиями столбцов. Перетянуть из верхней области панели Список полей сводной таблицыв область Значенияназвание того столбца (поля), значения в котором будут подытоживаться в сводной таблице.
После этого на листе создается сводная таблица, а на Лентепоявляется временный раздел Работа со сводными таблицамис двумя вкладками Параметрыи Конструктордля работы со сводной таблицей
Для изменения итоговой функции на вкладке Параметры нажать кнопку Вычисления.