Описание оборудования и используемых программных комплексов

При выполнении лабораторной работы необходим специализированный компьютерный класс с минимальными системными требованиями компьютеров:

– Процессор – Intel Pentium III;

– ОЗУ – 256 Mb;

– видеокарта – 32 Mb.

Требуемое программное обеспечение:

– Операционная система Microsoft Windows, пакет прикладных программ Microsoft Office 2010.

 

Краткое руководство по эксплуатации оборудования

При использовании оборудования необходимо:

– соблюдать общие правила нахождения в учебных лабораториях, работы с компьютером и использования программных средств;

– привести в порядок одежду;

– осмотреть рабочее место, убрать все мешающие работе предметы;

– визуально проверить правильность подключения ПЭВМ к электросети.

 

Задания

В качестве примера рассмотрим таблицу с итогами сессии.

№	Фамилия	Группа	Оценка по информатике	Оценка по физике	Оценка по математике
	Кушнарев 0.
	Богатырев С
	Докукина Л.
	Морозова К.
	Немчинов А.
	Джемисюк Н.
	Непошеваленко И
	Васильев 0.
	Гондарева Н.
	Карпачова Л.
	Грибовский А.
	Дедикова Т.
	Дронова И.
	Кпемешов А.
	Кпынина Е.
	Сибилева 0.

Каждый пункт задания делать на отдельном листе одной рабочей книги!

 

Задание 1. Сортировка списков

1.1 Отсортируйте таблицу по фамилиям студентов.

Для простой сортировки строк следует активизировать любую ячейку внутри списка, выбрать кнопку «Сортировка и фильтр» на панели Главная и щелкнуть по одному из значков (по возрастанию или по убыванию).

1.2 Отсортируйте таблицу по двум признакам: первичный – группа (по возрастанию), вторичный – фамилия (по алфавиту).

Если в столбце, являющемся признаком сортировки, много повторяющейся информации, то возможна дополнительная сортировка по вторичному признаку. В этом случае нужно добавить уровень сортировки. Необходимо выделить сортируемый диапазон и выбрать на панели Главная команду Настраиваемая сортировка под меню «Сортировка и фильтр». В окне "Сортировка" следует задать признак сортировки (заголовок столбца), а также как сортировать - по возрастанию или по убыванию (рис. 8).

Рисунок 8

Задание 2. Использование промежуточных итогов

2.1 Рассчитайте количество человек в каждой группе

Для расчета количества человек в каждой группе следует скопировать отсортированную! таблицу на новый лист и выбрать команду Данные ► Промежуточные итоги и установить следующие критерии для промежуточных итогов (рис. 9):

Рисунок 9

2.2 Подсчитайте средние оценки по физике в каждой группе.

Задание 3. Работа с фильтром.

3.1 С применением автофильтра выбрать записи:

· об отличниках;

· о студентах 156 группы, обучающихся без троек;

· с фамилиями, начинающимися с буквы К;

· 4 наихудшие оценки по математике.

В качестве примера выбора данных рассмотрим применение автофильтра для таблицы с итогами сессии. На рисунке 10 показаны результаты фильтрации по условию "Оценка по информатике"=5.

Рисунок 10

Для задания более сложного условия фильтрации необходимо в соответствующем раскрывающемся списке выбрать "Числовые фильтры – Настраиваемый фильтр" и сформулировать его в открывшемся окне "Пользовательский автофильтр". Окно содержит поля для ввода знаков логических отношений и метки логических операций И и ИЛИ. Например, для отбора записей, соответствующих студентам, получившим по информатике 4 или 5, следует выполнить настройки, как показано на рисунке 11.

Рисунок 11

Отменить результаты фильтрации можно через пункт меню фильтра «Выделить все».

3.2 Вычислить значение среднего балла по информатике в группе 155 с помощью функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ.

После получения результатов работы автофильтра они могут быть обработаны с помощью функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ.

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ имеет следующий синтаксис:

ПРОМЕЖУТОЧНЫЕ.ИТОГИ(N; диапазон), где N может принимать следующие значения:

1 - вычисление среднего значения;

2 - счет чисел;

3 - счет значений;

4 - вычисление максимального значения;

5 - вычисление минимального значения;

6 – вычисление произведения;

7, 8 - вычисление стандартных отклонений;

9 - вычисление суммы;

10, 11 - вычисление дисперсии.

Диапазон задает область применения функции. Например, для вычисления значения среднего балла по информатике в группе 155 необходимо вначале отфильтровать студентов со значением поля Группа=155, а затем использовать функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ для полученного диапазона значений поля Оценки по информатике и N=1.

3.3 С помощью расширенного фильтра выберите студентов, у которых оценка по информатике равна 4 или оценка по физике равна 5.

В качестве примера рассмотрим условие фильтрации ("Группа"=154 И "Оценка по информатике">3) ИЛИ ("Группа"=155 И "Оценка по информатике">3).

Исходная таблица, блок критериев и новая таблица с результатами фильтрации показаны на рисунке 12.

Рисунок 12

В рассмотренном примере блок критериев расположен в диапазоне G1:H3. Запуск расширенного фильтра выполняется через меню Данные/Фильтр/Дополнительно. В окне "Расширенный фильтр" следует задать настройки, как показано на рисунке 13.

Рисунок 13

Обратите внимание, что место для размещения результата указано диапазоном из одной строки. Excel автоматически увеличивает этот диапазон, если число записей, удовлетворяющих условию больше заданного в окне "Расширенный фильтр". Этот диапазон должен быть отделен от диапазона критериев по крайней мере одной пустой строкой.

Задание 3. Подбор параметра с использованием MS EXCEL

3.1 Найти значение параметра х, при котором У=82,135х³+1,58х²+45х принимает значения 4, 5, 12, 14.

Для поиска значения параметра х подготовим таблицу следующим образом: в ячейку В1 введем искомую функцию в виде: =82,175*СТЕПЕНЬ(B2;3)+1,58*B2*B2+45*B2.

Аналогичные формулы введем в ячейки С1, D1, E1. Для решения используем команду Данные – Анализ «что-если» – Подбор параметра и установим следующие ссылки на ячейки (рис 14). Нажав на кнопку ОК, получим результат.

Рисунок 14

3.2 Используя инструмент «Подбор параметра» определить:

· При какой цене сумма со скидкой, полученная за мониторы будет равна 150000р.?

· При каком количестве проданных флеш-накопителей сумма без скидки будет равна 47000р.?

· Какую максимальную цену можно заплатить за наушники, если сумма без скидки, которую допустимо заплатить, равна 50000р.?

Подготовить следующую таблицу:

Наименование товара	Цена 1 ед. товара	Продано единиц	Сумма (в руб.)	Сумма со скидкой (в руб.)
Монитор
Флеш-накопитель	526,00
Наушники

В ячейки Сумма и Сумма со скидкой ввести соответствующие формулы, считая, что сумме более 50000 руб. предоставляется скидка, равная 5%, в противном случае 2%.

Задание 4. Создание таблицы подстановки

4.1 Выполнить с помощью таблицы подстановки с одной переменной анализ размещения вклада, т.е. определить влияние изменения процентной ставки на сумму возврата вклада.

Для решения этой задачи подготовим данные таким образом, как указано на рисунке 15.

Рисунок 15

Коэффициент увеличения вклада при начислении сложных процентов вычисляется по формуле: =(1+В5)^В4, где В5 - процентная ставка, В4 - срок возврата вклада, а символ ^ - оператор "возведение в степень". Сумма возврата вклада вычисляется в ячейке В7 по формуле: =В3*В6.

В качестве переменной будем использовать процентную ставку (ячейку ввода В5), которая может принимать значения от 3 до 10%. Эти значения введем в столбец D согласно приведенному выше образцу. В ячейку, находящуюся правее и выше первого введенного в столбец D значения, введем формулу для вычисления суммы возврата вклада: = ВЗ*В6.

Чтобы создать таблицу подстановки, выделим диапазон ячеек D2:E10, включающий следующие элементы:

· списки значений;

· ячейки, содержащие формулы подстановки;

· диапазон, в который будет помещен результат.

После этого воспользуемся командой Анализ «что-если» -Таблица данных панели Данные. В появившемся диалоговом окне в поле «Подставлять значения по строкам в» введем абсолютный адрес ячейки ввода (с процентной ставкой) - $В$5. Получим результат, представленный на рисунке 16.

Рисунок 16

4.2 Выполнить с помощью таблицы подстановки с двумя переменными анализ влияние изменения величины процентной ставки и изменения срока вклада на сумму возврата вклада.

При создании таблицы подстановки с помощью двух переменных формула для определения значений должна находиться в месте пересечения столбца и строки с подставляемыми значениями. Используемая формула должна ссылаться на две различные ячейки ввода.

Скопируем из первой таблицы во второй экземпляр таблицы диапазон ячеек D3:D10. Введем формулу расчета суммы возврата вклада (=B3*B6) в ячейку, находящуюся выше первого подставляемого значения процентной ставки (в В13). В строку правее формулы (С13:Н13) введем значения подстановки для второй переменной - срока вклада - от 5 до 10 лет. Выделим диапазон ячеек, содержащий формулу и оба набора значений для подстановки (В13:Н21). Выполним команду Анализ «что-если» -Таблица данных. В диалоговом окне «Таблица подстановки» введем абсолютные адреса двух ячеек ввода. Ссылку на ячейку ввода для значений подстановки, расположенных в столбце ($B$5), укажем в поле «Подставлять значения по строкам». Ссылку на ячейку ввода для значений подстановки, расположенных в строке ($B$4), укажем в поле «Подставлять значения по столбцам». Получим результат, представленный на рисунке 17.

Рисунок 17

Задание 5 Создание сценариев

Создать сценарии развития ситуации по продаже продукции предприятия, представить отчет по структуре сценария:

Варианты ситуаций
Объем реализации	5,68	6,58	12,59
Цена продукции
Выручка от реализации

Для создания сценария подготовим нужные данные следующим способом: в ячейку В1 введем первый объем реализации, в ячейку В2 – цену продукции, а в ячейке В6 подсчитаем выручку по формуле =В2*В3. Выделим диапазон клеток В2:В3 и воспользуемся Диспетчером сценариев в команде Анализ «что-если» на панели «Данные»:

С помощью кнопки Добавить в диалоговом окне «Добавление сценария» будем вводить по очереди имена сценариев (сценарий1, сценарий2, сценарий3) и нажимать ОК (рисунок 18).

Рисунок 18

Каждый раз после ввода имени сценария открывается окно «Значения ячеек» сценария, в котором будем изменять объем реализации и цену продукции (рисунок 19).

Рисунок 19

В результате у нас создалось 3 сценария для каждой ситуации. Для создания отчета по структуре сценария нажмем на кнопку Отчет и выберем отчет в виде структуры. Добавится лист «Структура сценария» с отчетом (рисунок 20).

Рисунок 20

Задание 6 Консолидации данных

Провести консолидацию данных по зарплатам сотрудников за январь и февраль:

· по расположению;

· по категориям;

· с сохранением связей с исходными данными.

Исходные данные по зарплатам за 2 месяца показаны на рис. 21, 22, консолидированные данные на рис. 23. Для расчета столбца «Подоходный налог» используйте формулу =B2*0,12. Значения полей «сумма к выдаче» и «Итого» вычисляются по соответствующим формулам.

Рисунок 21 Лист 1. Зарплата за январь

 

Рисунок 22 Лист 2. Зарплата за февраль

 

Рисунок 23 Консолидированные данные

Для выполнения данного задания:

1. Перенесите данные каждой таблицы на отдельный лист (значения в последней графе определите по соответствующей формуле).

2. Получите консолидированную таблицу, как показано на рисунке 3, выполняя следующие операции:

· Перейдите на новый лист, установите курсор в первую ячейку области, где будет располагаться консолидированная таблица (А1);

· выполните команду «Консолидация» на панели «Данные»;

· установите курсор в поле Ссылка, и введите в него диапазон ячеек первого листа (см. рис. 24). Это можно сделать вручную или с помощью мыши, выделив нужный блок ячеек;

Рис. 24

· нажмите кнопку <Добавить>, при этом в окне Список диапазонов появится ссылка на выделенный диапазон;

· вновь установите курсор в поле Ссылка, и введите в него диапазон ячеек второй таблицы (заработная плата за февраль);

· нажмите кнопку <Добавить>, в окне Список диапазонов появится ссылка на выделенный диапазон;

· в диалоговом окне Консолидация выберите из списка функцию согласно заданию (например, Сумма), включите флажки Подписи верхней строки, Значения левого столбца и Создавать связи с исходными данными (рис. 25.);

 

Рис. 25

· нажмите кнопку <ОК>, на листе 3 появится консолидированная по категориям таблица (рис. 26).

Рис 26. Пример консолидации данных по категориям

Если для выполнения задания в конечной таблице некоторые столбцы не нужны, их можно удалить.

Задание 7 Формирование сводной таблицы и сводной диаграммы

Переименуйте следующий лист рабочей книги, присвоив ему имя Деревья, и скопируйте в него из методических указаний таблицу «Учет количества деревьев», показанную на рисунке 27.

N п.п. делянки	Порода	Возраст, лет	Высота, м	Площадь учета, м2	Количество деревьев, шт
	дуб		0,25
	дуб		0,35
	клен		1,1
	липа		2,5
	ель		0,3
	дуб		0,35
	дуб		0,6
	дуб		0,78
	дуб		1,1
	липа		0,41
	дуб		1,2
	ель		0,85
	клен
	липа		2,5
	липа		3,1

Рисунок 27 Таблица «Учет количества деревьев»

Создайте сводную таблицу

Для построения и модификации СТ используется инструмент «Сводные таблицы», вызываемый с помощью пункта «Сводная таблица» на панели «Вставка».

После этого появится диалоговое окно (см. рис. 28), в котором нужно выбрать:

· Источник данных

· Место для размещения сводной таблицы.

Источником данных может быть таблица или диапазон существующей книги EXCEL либо внешний источник данных.

В первом случае полное имя интервала задается в виде:

[имя_ книги]имя_листа!интервал;

Если СТ строится в той же книге, где находится исходная таблица, то имя книги указывать не обязательно.

 

Рисунок 28 Диалоговой окно при создании сводной таблицы

В зависимости от выбранного вида источника изменяются последующие этапы работы по созданию СТ.

Ниже указывается, куда размещать сводную таблицу: на существующий лист (в этом случае нужно выбрать диапазон или первую ячейку диапазона) либо на новый лист.

На следующем шаге создается структура СТ и определяются ее функции. Список полей представлен на рис. 6 и состоит из областей: названия строк, названия столбцов, фильтр отчета и значения. Слева отображаются все имена полей (заголовки столбцов) в заданном интервале исходной таблицы. Размещение поля в определенную область, как показано на рис. 29, выполняется путем его “перетаскивания” при нажатой левой кнопки мыши. Чтобы удалить поле из области, его перетаскивают за пределы. Удаление поля приведет к скрытию в СТ всех зависимых от него величин, но не повлияет на исходные данные.

Каждая область, куда размещаются поля исходной таблицы, имеет свое назначение, определяющее внешний вид СТ и ее функции.

Названия строк - поля этой области формируют заголовки строк СТ; если таких полей несколько, то они размещаются в макете сверху вниз, обеспечивая группирование данных СТ по иерархии полей, где для каждого элемента внешнего поля, элементы внутреннего поля повторяются.

Названия столбцов - поля в этой области формируют заголовки столбцов СТ; если таких полей несколько, то они в макете размещаются слева направо, обеспечивая группирование данных СТ по иерархии полей.

Фильтр отчета - поля в этой области выступают в качестве фильтров и позволяют просматривать независимо друг от друга данные СТ, соответствующие разным значениям поля, помещенного в эту область; в области страница может быть размещено несколько полей, между которыми устанавливается иерархическая связь - сверху вниз.

Рисунок 29 Список полей сводной таблицы

Значения - обязательно определяемая область для размещения полей, по которым подводятся итоги, согласно выбранной итоговой функции; размещаемые здесь поля могут быть произвольных типов. В сводных таблицах можно вычислить и представить различные итоговые функции: Сумма, Количество, Максимум, Среднее и другие. Кроме того, для каждого поля этой области можно задать одно из 9 дополнительных вычислений, которые задают способ представления чисел (например, вместо абсолютных значений вывод процентной величины этих значений по отношению к

некоторому итогу).

По умолчанию в качестве итоговой функции используется сумма, но можно использовать и другую функцию (количество, максимум, среднее). Для этого нужно в области Значения, щелкнуть по перевернутому треугольнику одного из выбранного поля и в контекстном меню выбрать «Параметры полей значений» (см. рис. 30)

Рисунок 30 Выбор параметров полей значений сводной таблицы

Далее выбрать соответствующую итоговую функцию (см. рис 31).

Рис. 31 Настройка параметров полей значений сводной таблицы: Выбор операции

 

Результат настройки списка полей представлен на рис. 32.

 

Рисунок 32 Пример простой сводной таблицы

 

Можно использовать несколько названий строк, например: порода и площадь учёта. В этом случае сведения по каждой породе будут подразделяться на варианты площадей учета (рис. 33).

Рисунок 33 Пример сводной таблицы с 2 полями в названии строк