Лабораторная работа №5 Использование логических и статистических функций

Цель работы: знакомство с различными встроенными функциями Microsoft Excel и проведение анализа данных.

Задание 1

1. Для студентов планируется поездка во Францию, но поедут только студенты, у которых средний балл более или равен «4», нет неудовлетворительных оценок и по иностранному языку «5». Создать функцию автоматического определения претендентов на поездку.

2. Подсчитать в каждой группе количество «5», «4», «3» и «2».

3. Определить «Худшую группу» по максимальному количеству «2» и «Лучшую группу» по максимальному количеству «5». При выполнении вычислений применять операцию «Присвоение имени блоку ячеек».

Методика выполнения работы

1. На новом листе рабочей книги создайте таблицу по образцу таблицы 5.6.

Таблица 5.6

2. В столбец «Ср. балл» введите формулу, для этого воспользуйтесь кнопкой Вставить функцию в строке ввода и редактирования формул или командой Формулы Þ Библиотека функций .

Выберите категорию функций Статистическая и функцию СРЗНАЧ.

В открывшемся диалоговом окне введите диапазон ячеек С5: Е5. Щелкните ОК и скопируйте формулу вниз до ячейки F24.

3. С помощью функций из категории Логические создадим в ячейке G5 логическое выражение

=ЕСЛИ(И(F5> =4; E5=5; C5< > 2; D5< > 2; E5< > 2); " Едет"; " Не подходит" )

Скопируйте формулу до ячейки G24.

4. Для подсчета количества человек, которые едут во Францию, а также количества различных оценок создайте на листе таблицу в соответствии с таблицей 5.7.

Таблица 5.7

5. Введите формулу подсчета количества студентов подходящих для поездки в ячейку В26

=СЧЁТЕСЛИ(G5: G24; " Едет" )

Функция СЧЕТЕСЛИ находится в категории Статистические.

6. В ячейку В27 введите формулу =СЧЁТЕСЛИ(G6: G25; " Не подходит" )

7. Количество полученных оценок определенного вида будем подсчитывать в ячейках С30: F33, используя уже знакомую нам функцию СЧЕТЕСЛИ. Введем в С30 формулу =СЧЁТЕСЛИ($C$5: $E$8; B30). Абсолютные ссылки (знак $) здесь применяются для удобного последующего копирования. Блок ячеек охватывает все оценки группы Б124, а ячейка В30 указывает на то, что подсчитываются оценки «отлично». Аналогично введите формулы подсчета «5» для других групп в ячейках D30: F30. Затем скопируйте ячейки С30: F30 вниз до 33 строки. Формула должна автоматически настроиться под другие диапазоны ячеек.

8. Присвойте имена блокам ячеек С33: F33 и С30: F30, содержащим количество двоек и пятерок по группам соответственно, как «Двойки» и «Пятерки». Для этого блок ячеек предварительно выделяется затем выполняется команда Формулы Þ Определенные имена Þ Присвоить имя .

9. В ячейку D35 введите формулу

=ПРОСМОТР(МАКС(Двойки); Двойки; C29: F29)

Функция МАКС находится в категории Статистические, а функция ПРОСМОТР в категории Ссылки и массивы.

С помощью справки изучите принцип работы функции

ПРОСМОТР! Ответь на вопрос: для чего в функции используется блок ячеек С29: F29?

10. В ячейку D36 аналогично пункту 9 введите формулу для определения лучшей группы по количеству пятерок.

Лабораторная работа № 6 Работа с функциями ссылки и массива

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

Задание 1

Определить стоимость объектов недвижимости (табл. 5.10) на основе их первоначальной стоимости и таблицы скидок за продолжительность эксплуатации (табл. 5.11), т.е. начиная с 1 года – 5%-ая скидка, с 5 лет – 10%-ая скидка и т.д.

Таблица 5.10

Наименование	Первоначальная стоимость	Год создания	Год эксплуатации

Зернохранилище
Складские помещения
Комбайн
Трактор
Грузовой автомобиль

Таблица 5.11

Количество лет эксплуатации
Скидки	5%	10%	30%	40%	45%

Методика выполнения работы

1. Создать представленные таблицы на одном рабочем листе Excel.

2. Присвоить диапазонам ячеек имена:

- выделить блок ячеек, содержащий значения начальной стоимости и выполнить команду Формулы Þ Присвоить имя. Задать имя Первоначальная_стоимость (обратите внимание, что в именах не должно быть пробелов);

- выделите блок ячеек, содержащих значения года создания и выполните команду Формулы Þ Присвоить имя. Задать имя Год_создания;

- таким же образом присвоить имя диапазону Год эксплуатации

3. Выделите блок ячеек, содержащий таблицу 5.11, и присвойте ему имя Справочная_таблица.

Далее воспользуемся функцией ГПР. Функция ГПР берет значение из первого диапазона, сравнивает со значениями первой строки второго диапазона, находит равное ему или ближайшее наибольшее, а затем в качестве результата берет значение из указанной строки второго диапазона, в нашем примере - строка 2. Подобным образом работает функция ВПР, но она работает с диапазонами ячеек, расположенными вертикально.

4. В первую ячейку, находящуюся на пересечении строки с годом 2000 и столбца – год 2005 введите формулу =Первоначальная_стоимость*(1-ГПР(Год_эксплуатации-Год_создания; Справочная_таблица; 2)). скопируйте формулу с помощью автозаполнения в другие вычисляемые ячейки

Задание 2

Необходимо создать две связанные таблицы для учета отдыхающих в Доме отдыха. В зависимости от категории комнаты и количества дней проживания рассчитать сумму оплаты. Выполнить поиск, и подстановку значений в таблицу используя функцию ПРОСМОТР. При этом количество дней проживания определяется как разница между днем отъезда и днем заезда.

Методика выполнения работы

1. Создайте новую рабочую книгу под именем «Дом отдыха».

2. Переименуйте первый лист в «Сведения» и заполните лист (рис.5.71).

3. Присвойте имена диапазонам командой Формулы Þ Присвоить имя .

a) А2: А31 - комнаты

б) В2: В31 - категория

в) D2: D7 – цена_категория

г) Е2: Е7 – цена

д) G2 – курс

е) G5 – сегодня

Рисунок 5.71 – Таблица для заполнения

4. Перейдите на лист 2, переименуйте его в «Заезды».

5. Подготовить таблицу следующего вида (рис.5.72):

Рисунок 5.72 – Таблица для заполнения

6. Заполните столбец D (номер комнаты) данными по своему усмотрению, на основании данных листа «Сведения».

7. В ячейку Е2 введите формулу =ПРОСМОТР(D2; комнаты; категория) и скопировать ее вниз до 14 строки.

8. В ячейку F2 для определения этажа введите формулу =ЛЕВСИМВ(D2; 1) и скопируйте ее вниз.

9. В ячейку G2 для определения количества дней проживания вводим функцию, =ЕСЛИ(C2=0; сегодня-B2; C2-B2)

10. В ячейку H2 для определения суммы оплаты в $ введем формулу =ПРОСМОТР(E2; цен_категория; цена)

11. В ячейку I2 для определения суммы оплаты в рублях введем формулу =H2*Курс

12. Создать сводную таблицу, позволяющую определить общую сумму оплаты по месяцам заезда. Для этого установите курсор внутри таблицы «Заезды» и выполните команду Вставка Þ Таблица Þ Сводная таблица. Откроется макет для размещения полей. На ось строк перетащите поле Дата прибытия, а в область данных поля: Сумма в рублях и Сумма в $ (рис. 5.73).

Рисунок 5.73 – Макет сводной таблицы

13. Установите активную ячейку в поле даты на начальное значение и выполните команду Данные Þ Структура Þ Группировать. Заполните диалоговое окно (рис. 5.74) так, чтобы выполнялась группировка по месяцам и нажать ОК.

Рисунок 5.74

Сохраните файл.

Лабораторная работа № 7 Работа со сводными таблицами

Цель работы: освоить навыки создания, редактирования и анализа данных на основе сводных таблиц.

Задание.

Построить сводную таблицу для расчета месячной заработной платы рабочих при повременной форме оплаты труда, начисления премии и учета удержаний. Премия дифференцирована по разрядам: 2 разряд 25%, 3 разряд 32%, 4 разряд 50% к тарифу, 5 разряд – 50%. Удержания берутся со всех видов начислений (зарплата, премия) и составляют 13% от суммы начислений.

Методика выполнения работы

1. Открыть новую книгу.

2. Переименовать лист в Картотека.

3. Подготовить исходные данные (табл. 5.12).

Таблица 5.12

ФИО	Табельный №	Профессия	Разряд работающего	Тариф	Льготы
Иванов А.П.		Кладовщик		5, 76р.
Колесов В.И.		Грузчик		5, 76р.
Крылов А.Р.		Грузчик		6, 79р.
Михайлов П.Р.		Грузчик		5, 76р.
Смирнов И.А.		Кладовщик		6, 79р.
Соколов Р.В.		Уборщик		4, 95р.
Котов А.А.		Весовщик		7, 78р.
Павлов Ф.Ф.		Весовщик		6, 79р.
Соловьев А.П.		Упаковщик		5, 76р.
Крылов А.Н.		Грузчик		7, 78р.
Ильин А.Е.		Упаковщик		7, 78р.

4. Установить курсор в список, выполнить команду меню ВставкаÞ Сводная таблица для вызова Мастера сводных таблиц и диаграмм.

5. Указать тип источника – Создать таблицу на основе данных, находящихся в списке или базе данных Microsoft Excel. Выбрать вид создаваемого отчета – Сводная таблица.

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

7. Разместить поля в макете сводной таблицы:

Фильтр отчета – Профессия, Названия строк – ФИО, Названия столбцов – Разряд работающего, Значения – Тариф, Операция – Сумма.

Макет сводной таблицы представлен на рисунке 5.75.

Рисунок 5.75 – Макет сводной таблицы

8. На ленте Конструктор выполнить команды: Общие итогиÞ Включить по столбцам; Выбрать стиль сводной таблицы. На ленте Параметры: Сводная таблицаÞ Параметры. В открывшемся окне задать Для пустых ячеек отображать – пробел; Сохранять форматирование ячеек. Нажать кнопку ОК .

В сводной таблице (рис. 5.76) представлен список всех работающих. Для каждого работающего указан только один тариф, соответствующий его разряду.

Для преобразования сводной таблицы следует:

1. Установить курсор в область сводной таблицы.

2. Выполнить команду Параметры Þ Формулы Þ Вычисляемое поле для создания вычисляемого поля.

Рисунок 5.76 – Сводная таблица

3. На рис. 5.77 представлено диалоговое окно для формирования вычисляемого поля. Имя поля – Зарплата, Формула вычисления: =Тариф*168. (Коэффициент 168 зависит от количества рабочих часов в текущем учетном периоде.). Для добавления поля в формулу можно воспользоваться кнопкой Добавить.

Рисунок 5.77 – Создание вычисляемого поля

4. Установить курсор в область сводной таблицы.

5. С помощью кнопки Список полей на ленте Параметры откройте макет сводной таблицы для корректировки.

6. Удалить поле Сумма по полю Тариф (простым перетаскиванием мышкой за поле окна).

7. Установить курсор в области сводной таблицы на поле Сумма по полю Зарплата.

8. На ленте Параметры выполнить команду Активное поле Þ Параметры поля (рис. 5.78):

- Изменить имя поля в сводной таблице – Месячная зарплата. Нажать кнопку Числовой формат и указать формат поля – Денежный.

- Нажать кнопку ОК.

Рисунок 5.78 – Задание параметров вычисляемого поля

9. Установить курсор в область сводной таблицы на поле Разряд работающего.

10. Создать вычисляемый объект Премия. Премия выплачивается как процент к начисленной заработной плате, дифференцируется по разрядам: 2 разряд – 25%, 3 разряд – 32%, 4 разряд – 50%, 5 разряд – 55%.

- На ленте Параметры выполнить команду Формулы Þ Вычисляемый объект (рис. 5.79). Указать имя объекта – Премия.

- Для построения формулы в окне Поля выбрать поле Разряд работающего, в окне Элементы выбрать элементы

- Формула: = ‘2’*0, 25+’3’*0, 32+’4’*0, 5+’5’*0, 55

- Нажать кнопку Добавить.

- Закрыть окно – кнопка ОК.

Рисунок 5.79 – Создание вычисляемого объекта

11. Установить курсор в область сводной таблицы на поле Разряд работающего. Создать вычисляемый объект Вычеты, сумма вычетов это 13% от суммы заработка и премии.

- Выполнить команду Формулы Þ Вычисляемый объект . Указать имя объекта – Вычеты (рис. 5.80).

Рисунок 5.80 – Создание вычисляемого объекта

- В окне Поля выбрать поле Разряд работающего, в окне Элементы выбрать элементы для построения формулы вида:
= –0, 13*(’2’+’3’+’4’+’5’+Премия)

- Нажать кнопку Добавить.

- Закрыть окно – кнопка ОК.

12. Выполнить команду ПараметрыÞ ФормулыÞ Вывести формулы для просмотра выражений вычисляемых полей и объектов (рис. 5.81).

Если потребуется изменить нормативы (количество отработанных часов, % премии, % вычетов), следует отредактировать вычисляемые поля и объекты – команда меню ФормулыÞ Вывести формулы , вызывать поле/объект, внести изменения

Вычисляемое поле
Порядок решения	Поле	Формула
	Зарплата	=Тариф *168

Вычисляемый объект
Порядок решения	Объект	Формула
	Премия	='2' 0, 25+'3'0, 32+'4'0, 4+'5'0, 55
	Вычеты	= -0, 13*('2'+'3'+'4'+'5'+Премия)

Рисунок 5.81 – Вывод формул

13. Переименовать лист, содержащий сводную таблицу, присвоив имя, Сводная таблица 1.

14. Поставить курсор внутрь сводной таблицы и на ленте Конструктор выполнить команду Макет отчета . Выбрать тип отчета.

15. Поставить курсор внутрь сводной таблицы и щелкнуть на ленте Параметры кнопку Сводная диаграмма .

16. В готовой диаграмме перетащить Разряд работающего в область Поле ряда. Выбирая вид профессии просмотреть данные по различным профессиям.

17. Сохранить рабочую книгу.

⇐ Предыдущая 1 2 345 6 Следующая ⇒