Лабораторная работа №8
.pdf
Лабораторный практикум по «Информатике». Разработчик: Аткина В.С.
Лабораторная работа №8
Тема: Работа с данными в MS Excel
Цель: Освоить основные приемы работы, обработки и анализа данных средствами MS Excel
Лабораторная работа направлена на освоение следующих компетенций:
ОК-9 - способность к логически-правильному мышлению, обобщению, анализу, критическому осмыслению информации, систематизации, прогнозированию, постановке исследовательских задач и выбору путей их решения на основании принципов научного познания
ПК-1 способность выявлять естественнонаучную сущность проблем, возникающих в ходе профессиональной деятельности, и применять соответствующий физико-математический аппарат для их формализации, анализа и выработки решения.
I.Теоретическая часть
1.1.Анализ данных и автоматизация расчетов
1.1.1.Использование команды «Подбор параметра»
Подбор параметра - удобное средство Excel для анализа "Что-если". При этом значения для ячеек-параметров изменяются так, чтобы число в целевой ячейке стало равно заданному. Таким образом, эту функцию удобно применять в том случае если известен результат, который требуется вычислить при помощи формулы, но неизвестны значения, которые необходимо ввести для получения этого результата, можно воспользоваться средством «Подбор параметра». Для того чтобы воспользоваться командой подбор параметра нужно выполнить следующий алгоритм действий:
1
Лабораторный практикум по «Информатике». Разработчик: Аткина В.С.
1.На вкладке «Данные» в группе «Работа с данными» выбрать команду «Анализ «Что - если», а затем выбрать в списке пункт «Подбор параметра».
2.В поле «Установить в ячейке» введите ссылку на ячейку, содержащую необходимую формулу.
3.Введите искомый результат в поле «Значение».
4.В поле «Изменяя значение ячейки» введите ссылку на ячейку, значение которой нужно подобрать.
Рисунок 1 - Функция подбор параметра
Рассмотрим пример работы с данной функцией. Для этого используем следующую модель, представленную ниже.
Пусть имеется некоторая виртуальная организация, ее Директор должен составить штатное расписание, т.е. определить, сколько сотрудников, на каких должностях и с каким окладом он должен принять на работу. Общий месячный фонд зарплаты составляет 400000 рублей.
Исходными данными для данной модели являются:
1.Число сотрудников:
Охранник - 3
Директор -1
Экономист - 1
Бухгалтер - 1
Специалист по защите информации - 1
Начальник отдела - 3
2
Лабораторный практикум по «Информатике». Разработчик: Аткина В.С.
Секретарь - 2
Инженер -5
Администратор - 1
Менеджер - 5
Кадровик - 1
Технический персонал - 4
2. За основу берется оклад технического персонала равный базовой ставке, а все остальные вычисляются через него: каждый оклад является линейной функцией от оклада технического персонала: А*С + В, где С – базовая ставка; А и В - коэффициенты, которые определены для каждой должности. Допустим, директор установил следующие значения коэффициентов:
|
Охранник должен получать в 1,5 раза больше технического |
|
|
персонала |
А=1,5 В=0 |
|
Бухгалтер - в 3 раза больше А=3 В=0 |
|
Экономист – на 3000 больше бухгалтера А=3 В=3000
Начальник отдела - на 10000 больше, чем бухгалтер А=3
В=10000
Инженер - в 3,5 раза больше технического персонала А=3,5
В=0
Кадровик – в 2,8 больше технического персонала А=2,8
В=0
|
Администратор - на 1000 больше кадровика А=2,8 |
|
В=1000 |
|
Специалист по защите информации - в 4 раза больше |
|
технического персонала А=4 В=0 |
Секретарь в 2 раза больше технического персонала А=2
В=0
Менеджер на 2500 больше секретаря А=2 В=2500
3
Лабораторный практикум по «Информатике». Разработчик: Аткина В.С.
Директор - на 8000 больше начальника отделаА=3
В=18000
3.Задав количество человек на каждой должности, можно составить уравнение:
Nl*(Al*C+Bl)+N2(A2*C+B2)+...+N11*(A11*C+B11)=210000,
где |
|
|
N1 |
- |
количество охранников; |
N2 |
- |
количество директорови т.д. |
А1...А11 и В1...В11 |
|
- коэффициентыдлякаждойдолжности. |
4.На некоторых должностях число людей может меняться. Например, зная, что найти технический персонал трудно, руководитель может принять решение сократить число технического персонала, чтобы увеличить оклад по этой должности за счет изменения базового оклада.
5.В этом уравнении нам известны А1...А11 и В1...В11, а не известны С и N1...N11. Ясно, что решить такое уравнение известными методами не удается, да и единственно верного решения нет. Остается решать уравнение путем подбора. Взяв первоначально какие-либо приемлемые значения неизвестных, подсчитаем сумму. Если эта сумма равна фонду заработной платы, то нам повезло. Если фонд заработной платы превышен, то можно снизить базовый оклад, либо отказаться от услуг какого-либо работника и т.д.
Результат расчета, описанной выше модели представлен на рисунке 2,
инаглядно представляет, что при заданных нами параметрах наблюдается значительное превышение месячного фонда заработной платы, следовательно, необходимо откорректировать исходные параметры модели. Для этого воспользуемся функцией подбор параметра (см. рисунок 3) и изменим значение в ячейки I5 (базовый оклад) таким образом, чтобы суммарная зарплата сотрудников (ячейка F17) была равна месячному фонду заработной платы и составляла 400000 рублей.
4
Лабораторный практикум по «Информатике». Разработчик: Аткина В.С.
Рисунок 2 - Модель "Штатное расписание"
Результат выполнения данной функции представлен на рисунке 4 значение в ячейки I5 было изменено в соответствии с поставленной задачей и составило 4963,0177…
Рисунок 3 - Применение функции подбор параметра
5
Лабораторный практикум по «Информатике». Разработчик: Аткина В.С.
Рисунок 4-Результат выполнения функции подбор параметра
Если нас не устраивает подобный вариант решения поставленной задачи, то с помощью данного метода можно изменить и подобрать необходимое значение изменяя данные любых других ячеек.
1.1.2. Вычисление нескольких версий результатов с помощью таблицы данных
Таблицы данных являются частью блока задач, который иногда называют инструментами анализа «Что-если». Таблица данных представляет собой диапазон ячеек, показывающий, как изменение определенных значений в формулах влияет на результаты этих формул. Таблицы предоставляют способ быстрого расчета нескольких вариантов в рамках одной операции, а также способ просмотра и сравнения результатов всех различных вариантов на одном листе.
Можно создать таблицу данных с одной или с двумя переменными, в зависимости от числа переменных, которые необходимо проверить.
Таблицы данных с одной переменной используется для того что бы при необходимости просмотреть, как различные варианты одного значения влияют на размер вычисляемого результата.
6
Лабораторный практикум по «Информатике». Разработчик: Аткина В.С.
Таблицы данных с двумя переменными. В таблице данных с двумя переменными может быть показано влияние на одно вычисляемое значение различных вариантов двух переменных.
Для создания таблицы данных с одной переменной нужно выполнить следующий алгоритм действий:
1.Следует сформировать таблицу данных с одной переменной, чтобы введенные значения были расположены либо в столбце (ориентированные по столбцу), либо в строке (ориентированные по строке). Для этого введите в отдельный столбец или в отдельную строку список значений, которые нужно подставлять в ячейку ввода. Если значения в таблице данных ориентированы по столбцу, введите формулу в ячейку, расположенную на одну строку выше и на одну ячейку правее первого значения. Справа от первой формулы введите любые другие формулы. Если значения в таблице данных ориентированы по строке, введите формулу в ячейку, расположенную на один столбец левее и на одну строку ниже первого значения. Ниже в том же столбце введите любые другие формулы.
2.Выделите диапазон ячеек, содержащих формулы и значения, которые нужно подставить.
3.На вкладке «Данные» в группе «Работа с данными» выберите команду «Анализ что - если», а затем выберите в списке пункт «Таблица» (см. рисунок 5).
4.Выполните одно из следующих действий. Если значения в таблице ориентированы по столбцу, введите в поле ввода «Подставлять значения по строкам в». Если значения в таблице ориентированы по строке, введите ссылку на ячейку ввода в поле «Подставлять значения по столбцам в».
7
Лабораторный практикум по «Информатике». Разработчик: Аткина В.С.
Рисунок 5 - Анализ данных с помощью таблиц
Для создания таблицы с двумя переменными нужно:
1.Таблицы данных с двумя переменными используют одну формулу с двумя наборами значений. Формула должна ссылаться на две различные ячейки ввода.
2.В том же столбце ниже формулы введите значения подстановки для первой переменной.
3.Введите значения подстановки для второй переменной справа от формулы в той же строке.
4.Выделите диапазон ячеек, содержащий формулу и оба набора данных подстановки.
5.На вкладке «Данные» в группе «Работа с данными» выберите команду «Анализ что-если», а затем выберите в списке пункт Таблица.
6.В поле «Подставлять значения по столбцам» в введите ссылку на ячейку ввода для значений подстановки в строке.
7.В поле «Подставлять значения по строкам» в введите ссылку на ячейку ввода для значений подстановки в столбце.
8.Нажмите кнопку ОК.
Рассмотрим пример связанный с применением данного вида анализа данных, пусть у нас имеется таблица расчета стоимости системы защиты информации (СЗИ), которая складывается как из стоимости самих средств защиты информации, так и из стоимости услуг по разработки и созданию
8
Лабораторный практикум по «Информатике». Разработчик: Аткина В.С.
проекта самой системы защиты. Мы можем с помощью таблицы данных поварьировать как значение стоимости средств защиты информации и посмотреть, как изменение этого значения окажет влияние на стоимость самой СЗИ (применяя в этом случае таблицу данных с одним входом), так и проанализировать изменение стоимости СЗИ при сочетании различных вариантов стоимости проектирования и средств защиты (применяя таблицу с двумя входами). Результаты применения таблиц данных в двух рассмотренных случаях представлены на рисунках 6 и 7 соответственно.
Рисунок 6 - Таблица данных с одним параметром
Рисунок 7 - Таблица данных с двумя входами
9
Лабораторный практикум по «Информатике». Разработчик: Аткина В.С.
1.2.Именование объектов в Excel
Имя — это осмысленное краткое обозначение, позволяющее легче
понять назначение ссылки на ячейку. Существует несколько типов имен, которые можно создавать и использовать:
Определенное имя - имя, представляющее ячейку, диапазон ячеек, формулу или значение константы. Можно создавать свои собственные определенные имена, а иногда Microsoft Office Excel создает для пользователя определенное имя, например, при установке области печати.
Имя таблицы - имя таблицы Excel, являющейся набором данных по отдельной теме, которые хранятся в записях (строках) и полях (столбцах). Excel создает имена таблиц Excel по умолчанию: Таблица1, Таблица2 и т. д., каждый раз при вставке таблицы Excel,
но эти имена можно изменить, чтобы сделать их более выразительными.
Все имена имеют область действия, это либо конкретный лист (локальный уровень листа) либо вся книга (глобальный уровень книги). Область действия имени — это область, в которой имена распознаются без уточнений. Имя должно быть уникальным в своей области определения. Excel запрещает определять имя, не являющееся уникальным в его области определения. Но можно использовать одинаковые имена в разных областях определения.
Имена создаются следующими способами:
1.Поле имени в строке формул. Это лучше всего использовать для создания имени на уровне книги для выделенного диапазона.
2.Создание имени по выделению. Можно удобно создавать имена из существующих имен строк и столбцов с помощью выделения ячеек на листе.
3.Диалоговое окно «Создание имени». Это лучше всего использовать, если нужна большая гибкость при создании имен,
10