Лабораторная работа 4 (1). Лабораторная работа 4 Анализ данных в ms excel 2010. Цель и задачи работы

• Название: Лабораторная работа 4 Анализ данных в ms excel 2010. Цель и задачи работы
• Дата: 09.01.2022
• Размер: 0.85 Mb.
• Имя файла: Лабораторная работа 4 (1).pdf
• Тип: Лабораторная работа; #326276

1
Лабораторная работа № 4
Анализ данных в MS Excel 2010.
1. Цель и задачи работы
Изучение возможностей обработки данных в электронных таблицах и изучения технологий объединения данных нескольких таблиц Excel
Задачами работы является изучение возможностей анализа данные в программе Excel для выполнения финансовых и экономических расчетов: подбора параметра, таблиц подстановки.
2. Теоретические сведения
2.1. Работа со списками
Списком называют таблицу, строки которой содержат однородную информацию. Наиболее частые операции со списками
– сортировка и поиск информации. Excel автоматически определяет границы списка. Признаком конца области при автоматическом определении служит первая пустая строка. Для определения верхней границы списка сравнивается содержимое первой и второй строк области списка. Если эти строки различаются по типу, то первая строка рассматривается как заголовок. Она исключается из обрабатываемой области.
2.1.1 Сортировка списков
Необходимость сортировки записей в списках возникает обычно для последующего быстрого поиска информации в нем.
Существуют два способа сортировки: по возрастанию и по убыванию признака сортировки, которым является один из столбцов списка. Для простой сортировки строк следует активизировать любую ячейку внутри списка, выбрать кнопку «Сортировка и фильтр» на панели Главная и щелкнуть по одному из значков (по возрастанию или по убыванию). Excel автоматически определяет границы списка и сортирует строки целиком. Если пользователь сомневается в правильности определения границ списка, то целесообразно выделить сортируемый диапазон и выбрать там же команду Настраиваемая сортировка. В окне "Сортировка" следует задать признак сортировки (заголовок столбца), а также как сортировать – по возрастанию или по убыванию.

2
Если в столбце, являющемся признаком сортировки, много повторяющейся информации, то возможна дополнительная сортировка по вторичному признаку. В этом случае нужно добавить уровень сортировки.
2.1.2 Использование промежуточных итогов для анализа
списка
Команда Данные – Промежуточные итоги выбирается, когда список отсортирован по заданным критериям. Эта команда добавляет строки промежуточных итогов для каждой группы элементов списка, а также создает общие итоги. При этом можно использовать различные функции для вычисления итогов (например,
СУММ или СРЗНАЧ). При выводе промежуточных итогов Excel создает структуру списка. Чтобы вывести интересующий пользователя уровень детализации данных, нужно щелкнуть мышью на соответствующем символе структуры.
Чтобы удалить промежуточные итоги и структуру, нужно использовать кнопку
«Убрать
все» диалогового окна
Промежуточные итоги. Для применения нескольких итоговых функций достаточно повторить команду Промежуточные итоги для новой итоговой функции и снять флажок «Заменить текущие
итоги» диалогового окна Промежуточные итоги.
Также анализировать отфильтрованный список можно с помощью функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ.
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ имеет следующий синтаксис:
ПРОМЕЖУТОЧНЫЕ.ИТОГИ(N; диапазон), где N может принимать следующие значения:
1 - вычисление среднего значения;
2 - счет чисел;
3 - счет значений;
4 - вычисление максимального значения;
5 - вычисление минимального значения;
6 – вычисление произведения;
7, 8 - вычисление стандартных отклонений;
9 - вычисление суммы;
10, 11 - вычисление дисперсии.
Диапазон задает область применения функции.

3
2.2 Применение фильтров
Фильтр – это средство для отбора записей в таблице по некоторому критерию. В Excel имеются два типа фильтров:
автофильтр и расширенный фильтр. Автофильтр показывает записи, совпадающие с критериями фильтрации, и скрывает не совпадающие. Расширенный фильтр способен сформировать новую таблицу из отфильтрованных записей.
2.2.1 Автофильтр
Для применения автофильтра необходимо выделить любую клетку внутри фильтруемой таблицы и обратиться на панели
Данные к кнопке Фильтр. После обращения в заголовке таблицы должны появиться кнопки для раскрытия списков. Нажатие любой кнопки приводит к раскрытию списка элементов соответствующего столбца таблицы. Выбранный элемент является критерием фильтрации. Строки таблицы, в которых элементы столбца не совпадают с критерием будут скрыты, причем за совпавшими сохраняются их прежние порядковые номера. Выбор второго критерия в другом списке приведет к дополнительной фильтрации записей и т.д.
Достоинство автофильтра в простоте его применения.
Недостаток: в отсутствии возможности формулировать сложные условия, связывающие условия фильтрации в разных столбцах операцией ИЛИ.
2.2.2 Расширенный фильтр
Для применения расширенного фильтра требуется предварительная подготовка, состоящая из двух этапов:
– подготовки вспомогательной таблицы (диапазона) критериев;
– планирования места для размещения результатов фильтрации.
Таблица критериев состоит из строки заголовков и строк с критериями. В смежных ячейках первой строки размещаются необходимые заголовки критериев, совпадающие с заголовками основной таблицы.
Лучше формировать эти заголовки копированием из основной таблицы. Под заголовками размещаются критерии, причем если несколько критериев расположены в одной строке, они считаются связанными между собой логической операцией И, если в разных – ИЛИ.
В качестве критериев можно использовать содержимое ячеек таблицы или логические выражения с использованием содержимого.

4
2.3 Подбор параметра
Средство MS Ехсе1 «Подбор параметра» позволяет получить требуемое значение в определенной ячейке, которую называют целевой, путем изменения значения (параметра) другой ячейки, которую называют влияющей. Это позволяет решать обратную задачу, когда требуется, меняя значение одного из исходных данных (параметров), получить заданное значение результата. При этом целевая ячейка должна прямо или косвенно ссылаться на ячейку с изменяемым значением. Подбор параметра выполняется с помощью команды Данные – Анализ «что-если» – Подбор
параметра. В открывшемся диалоговом окне задаются:
– в поле ввода «Установить в ячейке» – ссылка на целевую ячейку;
– в поле ввода «Значение» – требуемое значение,
– в поле ввода «Изменяя значение ячейки» – ссылка на изменяемую ячейку.
После нажатия кнопки ОК или клавиши Enter результат подбора параметра будет показан в окне «Результат подбора параметра». Значение параметра сохранится в изменяемой ячейке.
Если не нужно сохранить значение параметра, то следует нажать кнопку Отмена.
Решение может быть не найдено, если результат зависит не от одного параметра или если изменяемая и целевая ячейки логически не связаны.
В процессе подбора параметра окно «Результат подбора параметра» находится на экране. Если задача обладает плохой сходимостью, т.е. требуется много (или бесконечно много) шагов, чтобы найти решение с заданной точностью, то можно воспользоваться кнопками Шаг и Пауза, чтобы контролировать процесс и прервать его при необходимости.
2.4 Таблицы подстановки
Таблицы подстановки позволяют вычислять и анализировать данные в тех случаях, когда необходимо найти результат для нескольких значений в одной или двух исходных (влияющих) ячейках. MS Excel позволяет создавать таблицы подстановки следующих типов:
– таблицы подстановки с одной переменной и с одной или несколькими формулами (рис. 4.1);
– таблицы подстановки с двумя переменными.

5
Рис. 4.1 Таблица подстановки
Таблица подстановки представляет собой диапазон ячеек, содержащий результаты подстановки различных значений данных в формулы рабочего листа. На одном рабочем листе Excel можно использовать несколько таблиц подстановок. В Excel реализуются таблицы подстановки с одной и с двумя переменными.
Ячейка ввода – это ячейка, в которую подставляются значения из таблицы данных. Формулы, используемые в таблицах подстановки, должны ссылаться на ячейки ввода.
Порядок действий:
1) Ввести список значений, которые следует подставлять в ячейки ввода, в отдельный столбец и/или в отдельную строку
(ячейки D2:D12);
2) Ввести формулу в ячейку, расположенную выше столбца и левее строки с введенными значениями (ячейка Е1);
3) Выделить диапазон ячеек с формулой и значениями подстановки (ячейки D1:E12);
4) Активизировать диалоговое окно «Таблица подстановки» командой Анализ «что-если» - Таблица данных панели Данные.;
5) Указать ссылку на ячейку (ячейки) ввода в диалоговом окне
«Таблица подстановки» (рис. 4.2);
6) Инициировать вычисления, нажав кнопку OK.

6
Рис. 4.2 Диалоговое окно Таблица подстановки
При построении таблицы подстановки с одной переменной можно указать несколько формул, но все они должны ссылаться на одну ячейку ввода.
Таблица подстановки с двумя переменными использует всегда только одну формулу, оперирующую двумя наборами значений.
Изменяя формулы подстановки или данные в списке значений, можно увидеть, как это влияет на возвращаемый результат.
Изменение или удаление отдельного элемента из массива значений невозможно, т.к. рассчитанные значения содержатся в диапазоне ячеек, использующих одну и ту же формулу. Необходимо удалить все значения, предварительно выделив их.
3. Описание оборудования и используемых программных
комплексов
При выполнении лабораторной работы необходим специализированный компьютерный класс с минимальными системными требованиями компьютеров:
– Процессор – Intel Pentium III;
– ОЗУ – 256 Mb;
– видеокарта – 32 Mb.
Требуемое программное обеспечение:
– Операционная система Microsoft Windows, пакет прикладных программ Microsoft Office 2010.
При использовании оборудования необходимо:
– соблюдать общие правила нахождения в учебных лабораториях, работы с компьютером и использования программных средств;
– привести в порядок одежду;
– осмотреть рабочее место, убрать все мешающие работе предметы;
– визуально проверить правильность подключения ПЭВМ к электросети.

7
4. Задание.
4.1 В качестве примера рассмотрим таблицу с итогами сессии
(табл. 4.1).
Таблица 4.1
Итоги сессии
№
Фамилия
Группа
Оценка по
информатике
Оценка
по
физике
Оценка по
математике
1
Кушнарев 0.
154
5
5
3
2
Богатырев С
154
4
4
3
3
Докукина Л.
155
4
3
4
4
Морозова К.
156
5
4
3
5
Немчинов А.
156
3
3
2
6
Джемисюк Н.
154
3
3
3
7
Непошеваленко И 154
4
4
4
8
Васильев 0.
155
4
3
4
9
Гондарева Н.
155
5
5
5
10 Карпачова Л.
155
4
3
3
11 Грибовский А.
156
5
4
3
12 Дедикова Т.
156
3
3
4
13 Дронова И.
156
5
5
5
14 Кпемешов А.
156
3
3
3
15 Кпынина Е.
156
4
4
4
16 Сибилева 0.
156
5
4
3
Выполните задания на анализ данных в этой таблице согласно методике, представленной в п. 6.
Каждый пункт задания делать на отдельном листе одной рабочей книги!
4.2 Выполните задания для самостоятельной работы по указанному преподавателем варианту:
– Методом подбора параметра решить уравнение f(x)=0 с точностью четырех знаков после запятой. Вариант функции выбирать из таблицы 4.2.

8
Таблица 4.2
Варианты функций
Вариант
f(x)
1 20 3
4


x
x
2 7
5 4


x
x
3 5
2 3


x
x
4 7
2 3


x
x
5 2


x
x
e
6 1
2


x
x
e
7
x
x
cos
2

8 1
5 0
ln


x
x
– С помощью таблицы данных с двумя переменными найти суммы платежей по полученной ссуде. Вариант выбирать из таблицы 4.3.
Таблица 4.3
Варианты переменных
№ варианта
Число платежей
Величина ссуды
% -ая ставка
1 11,12,13,14 100000 3,4,5,6,7 %
2 8,9,10,11,12,13 250000 5,6,7,8 %
3 10,11,12,13 556230 6,7,8,9,10,11 %
4 5,6,7,8,9 450821 15,16,17,18,19 %
5 6,7,8,9,10,11 152064 12,13,14,15 %
6 16,17,18,19,20 101000 11,12,13,14 %
7 7,8,9,10,11,12 594053 4,5,6,7,8,9,10%
8 12,13,14,15,16,17 784206 10,11,12,13%
5. Методика выполнения задания.
5.1. Сортировка списков
5.1.1Отсортируйте таблицу по фамилиям студентов.
Для простой сортировки строк следует активизировать любую ячейку внутри списка, выбрать кнопку «Сортировка и фильтр» на панели Главная и щелкнуть по одному из значков (по возрастанию или по убыванию).
5.1.2 Отсортируйте таблицу по двум признакам: первичный –
группа (по возрастанию), вторичный – фамилия (по алфавиту).
Если в столбце, являющемся признаком сортировки, много повторяющейся информации, то возможна дополнительная

9 сортировка по вторичному признаку. В этом случае нужно добавить уровень сортировки. Необходимо выделить сортируемый диапазон и выбрать на панели Главная команду Настраиваемая сортировка под меню «Сортировка и фильтр». В окне "Сортировка" следует задать признак сортировки (заголовок столбца), а также как сортировать - по возрастанию или по убыванию (рис. 4.3).
Рис. 4.3 Многоуровневая сортировка
5.2. Использование промежуточных итогов
5.2.1 Рассчитайте количество человек в каждой группе
Для расчета количества человек в каждой группе следует скопировать отсортированную! таблицу на новый лист и выбрать команду Данные – Промежуточные итоги и установить следующие критерии для промежуточных итогов (рис. 4.4):
Рис. 4.4 Команда Промежуточные итоги
5.2.2 Подсчитайте средние оценки по физике в каждой группе.

10
5.3. Работа с фильтром.
5.3.1 С применением автофильтра выбрать записи:
– об отличниках;
– о студентах 156 группы, обучающихся без троек;
– с фамилиями, начинающимися с буквы К;
– 4 наихудшие оценки по математике.
В качестве примера выбора данных рассмотрим применение автофильтра для таблицы с итогами сессии. На рисунке 4.5 показаны результаты фильтрации по условию "Оценка по информатике"=5.
Рис. 4.5 Установка Автофильтра
Для задания более сложного условия фильтрации необходимо в соответствующем раскрывающемся списке выбрать "Числовые
фильтры – Настраиваемый фильтр" и сформулировать его в открывшемся окне "Пользовательский автофильтр". Окно содержит поля для ввода знаков логических отношений и метки логических операций И и ИЛИ. Например, для отбора записей, соответствующих студентам, получившим по информатике 4 или 5, следует выполнить настройки, как показано на рисунке 4.6.
Рис. 4.6 Пользовательский автофильтр

11
Для выбора наихудших оценок по математике воспользуйтесь пунктом «Первые 10». Отменить результаты фильтрации можно через пункт меню фильтра «Выделить все».
5.3.2 Вычислить значение среднего балла по информатике в
группе 155 с помощью функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ.
После получения результатов работы автофильтра они могут быть обработаны с помощью функции
ПРОМЕЖУТОЧНЫЕ.ИТОГИ.
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ имеет следующий синтаксис:
ПРОМЕЖУТОЧНЫЕ.ИТОГИ(N; диапазон), где N может принимать следующие значения:
1 - вычисление среднего значения;
2 - счет чисел;
3 - счет значений;
4 - вычисление максимального значения;
5 - вычисление минимального значения;
6 – вычисление произведения;
7, 8 - вычисление стандартных отклонений;
9 - вычисление суммы;
10, 11 - вычисление дисперсии.
Диапазон задает область применения функции. Например, для вычисления значения среднего балла по информатике в группе 155 необходимо вначале отфильтровать студентов со значением поля
Группа=155, а затем использовать функцию
ПРОМЕЖУТОЧНЫЕ.ИТОГИ для полученного диапазона значений поля Оценки по информатике и N=1.
5.3.3 С помощью расширенного фильтра выберите
студентов, у которых оценка по информатике равна 4 или оценка
по физике равна 5.
В качестве примера рассмотрим условие фильтрации
("Группа"=154 И "Оценка по информатике">3) ИЛИ ("Группа"=155
И "Оценка по информатике">3).
Исходная таблица, блок критериев и новая таблица с результатами фильтрации показаны на рисунке 4.7.

12
Рис. 4.7 Расширенный фильтр
В рассмотренном примере блок критериев расположен в диапазоне G1:H3. Запуск расширенного фильтра выполняется через меню Данные/Фильтр/Дополнительно. В окне "Расширенный
фильтр" следует задать настройки, как показано на рисунке 4.8.
Рис. 4.8 Установка параметров расширенного фильтра
Обратите внимание, что место для размещения результата указано диапазоном из одной строки. Excel автоматически увеличивает этот диапазон, если число записей, удовлетворяющих условию больше заданного в окне "Расширенный фильтр". Этот диапазон должен быть отделен от диапазона критериев по крайней мере одной пустой строкой.

13
5.4. Подбор параметра с использованием MS EXCEL
5.4.1 Найти значение параметра х, при котором
У=82,135х
3
+1,58х
2
+45х принимает значения 4, 5, 12, 14.
Для поиска значения параметра х подготовим таблицу следующим образом: в ячейку В1 введем искомую функцию в виде:
=82,175*СТЕПЕНЬ(B2;3)+1,58*B2*B2+45*B2.
Аналогичные формулы введем в ячейки С1, D1, E1. Для решения используем команду Данные – Анализ «что-если» –
Подбор параметра и установим следующие ссылки на ячейки (рис.
4.9). Нажав на кнопку ОК, получим результат.
Рис. 4.9 Подбор параметра
5.4.2
Используя
инструмент
«Подбор
параметра»
определить:
– При какой цене сумма со скидкой, полученная за мониторы
будет равна 150000р.?
– При каком количестве проданных флеш-накопителей сумма
без скидки будет равна 47000р.?
– Какую максимальную цену можно заплатить за наушники,
если сумма без скидки, которую допустимо заплатить, равна
50000р.?
Для выполнения задания подготовить табл. 4.4. В ячейки
Сумма и Сумма со скидкой ввести соответствующие формулы, считая, что сумме более 50000 руб. предоставляется скидка, равная
5%, в противном случае 2%.

14
Таблица 4.4
Исходные данные
Наименование товара
Цена 1 ед. товара
Продано единиц
Сумма
(в руб.)
Сумма со скидкой
(в руб.)
Монитор
20
Флеш-накопитель 526,00
Наушники
23
5.5. Создание таблицы подстановки
5.5.1 Выполнить с помощью таблицы подстановки с одной
переменной анализ размещения вклада, т.е. определить влияние
изменения процентной ставки на сумму возврата вклада.
Для решения этой задачи подготовим данные таким образом, как указано на рисунке 4.10.
Рис. 4.10 Данные для таблицы данных с одной переменной
Коэффициент увеличения вклада при начислении сложных процентов вычисляется по формуле: =(1+В5)^В4, где В5 – процентная ставка, В4 – срок возврата вклада, а символ ^ – оператор "возведение в степень". Сумма возврата вклада вычисляется в ячейке
В7 по формуле: =В3*В6.
В качестве переменной будем использовать процентную ставку
(ячейку ввода В5), которая может принимать значения от 3 до 10%.
Эти значения введем в столбец D согласно приведенному выше образцу. В ячейку, находящуюся правее и выше первого введенного в столбец D значения, введем формулу для вычисления суммы возврата вклада: = ВЗ*В6.

15
Чтобы создать таблицу подстановки, выделим диапазон ячеек
D2:E10, включающий следующие элементы:
– списки значений;
– ячейки, содержащие формулы подстановки;
– диапазон, в который будет помещен результат.
После этого воспользуемся командой Анализ «что-если» -
Таблица данных панели Данные. В появившемся диалоговом окне в поле «Подставлять значения по строкам в» введем абсолютный адрес ячейки ввода (с процентной ставкой) - $В$5. Получим результат, представленный на рисунке 4.11.
Рис. 4.11 Результаты расчета в таблице данных с одной переменной
5.5.2 Выполнить с помощью таблицы подстановки с двумя
переменными анализ влияние изменения величины процентной
ставки и изменения срока вклада на сумму возврата вклада.
При создании таблицы подстановки с помощью двух переменных формула для определения значений должна находиться в месте пересечения столбца и строки с подставляемыми значениями. Используемая формула должна ссылаться на две различные ячейки ввода.
Скопируем из первой таблицы во второй экземпляр таблицы диапазон ячеек D3:D10. Введем формулу расчета суммы возврата вклада (=B3*B6) в ячейку, находящуюся выше первого подставляемого значения процентной ставки (в В13). В строку правее формулы (С13:Н13) введем значения подстановки для второй переменной – срока вклада – от 5 до 10 лет. Выделим диапазон ячеек, содержащий формулу и оба набора значений для подстановки

16
(В13:Н21). Выполним команду Анализ «что-если» -Таблица
данных. В диалоговом окне «Таблица подстановки» введем абсолютные адреса двух ячеек ввода. Ссылку на ячейку ввода для значений подстановки, расположенных в столбце ($B$5), укажем в поле «Подставлять значения по строкам». Ссылку на ячейку ввода для значений подстановки, расположенных в строке ($B$4), укажем в поле «Подставлять значения по столбцам». Получим результат, представленный на рисунке 4.12.
Рис. 4.12 Результаты расчета в таблице данных с двумя переменными
6. Требования к содержанию и оформлению отчета
Отчет по лабораторной работе должен содержать:
– название университета, факультета и кафедры;
– название работы;
– сведения о студенте (ФИО, группа, курс) и о преподавателе
(ФИО);
– цель и задачи работы;
– постановку задачи (варианты задания);
– результаты выполнения заданий работы;
– ответы на контрольные вопросы по указанию преподавателя;
– дату выполнения лабораторной работы;
– выводы по работе.
7. Контрольные вопросы
1. Опишите возможности по анализу данных в Excel.
2. Каковы возможности по сортировке данных предоставляет
Excel?
3. Каким образом выполнить вторичную сортировку?

17 4. Какие возможности по отбору данных по заданному условию предоставляет Excel?
5. Что такое «Подбор параметра» и как его выполнить?
6. Как создать таблицу данных в Excel?
7. Каким образом и где следует задать формулу для расчета таблицы данных?
8. Каковы основные правила использования расширенного фильтра данных?
9. Опишите последовательность действий, необходимых для расчета промежуточных итогов.
10. Чем отличается использование команды «Промежуточные итоги» и функции Промежуточные.Итоги?
8. Критерии результативности лабораторного практикума
Лабораторная работа считается выполненной, если:
– задание к лабораторной работе выполнено в полном объеме;
– студент представил результаты выполнения в электронной форме;
– результаты выполнения соответствуют поставленным задачам;
– при устном ответе на 1-2 контрольных вопроса по указанию преподавателя студент демонстрирует знание основных операторов и определений изученной темы и умение применить их при решении соответствующих задач;
– представленный отчет по лабораторной работе соответствует требованиям раздела 6.
9. Литература
1. Менделсон, Эдвард. Не только Microsoft Office 2010! /
Э. Менделсон // PC Magazine, 2010, № 9. – С. 43-52.
2. Долженков В. А. Самоучитель Excel 2007 / В. А. Долженков,
А. Б. Стученков. – СПб. : БХВ-Петербург, 2008. – 544 с.
3. Курбатова, Е. А. Microsoft Office Excel 2007. Самоучитель /
Е. А. Курбатова. – М ; СПб; Киев: Диалектика, 2008. – 384 с.