Лабораторная работа № 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

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

 

Самостоятельное задание

 

1. В таблице приведена выработка рабочих за 3 дня. Создать на рабочем листе таблицу. На новом листе определить выработку каждого работника по дням с помощью функции СУММЕСЛИ и оформив в виде таблицы.

 

Ф.И.О.	№ уч-ка	23.05.2009	24.05.2009	25.05.2009
Иванов
Петров
Сидоров
Петров
Петров
Иванов
Иванов
Петров
Иванов
Петров
Сидоров
Иванов
Петров
Иванов
Сидоров
Сидоров
Иванов
Сидоров
Сидоров
Иванов
Сидоров
Петров
Иванов
Петров
Петров
Сидоров
Сидоров

 

2. Найти в Excel решение уравнения АХ=В, если все его аргументы – массивы.

Массив А		Массив В

 

 

Лабораторная работа № 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. На основании данных из лабораторной работы №3 (рис. 5.50) построить сводную таблицу следующего вида:

Сумма по полю Продажа
Категория	Магазин	Всего
Боевик	Восток	146,5
	Запад	64,5
	Север	479,5
	Юг
Боевик Всего	830,5
Драма	Восток
	Запад
	Север
	Юг
Драма Всего

 

2. В построенной сводной таблице добавить среднее значение суммы продаж по магазинам для каждой категории фильма. Для этого воспользуйтесь созданием вычисляемого поля.