Оплата командировочных расходов

 

 

Суточные	Москва
иной город

 

Проживание	н/док
б/док

 

 

К-во

Наличие

Наличие

Стоим

Оплата

проездн

квитанци

ость

ФИО

Город

дней

Стоимость

Оплата

прожи

проживан

Суточные

Сумма к

команд

ых

проезда

проезда

и за

вания

ия в

оплате

документ

прожива

ировки

ов

ние

в

сутки

сутки

Крылов

С-Петербург

да

да

Почкин

Москва

нет

да

Осинина

Новосибирск

нет

да

Ульянова

Москва

да

да

Демина

С-Петербург

да

нет

Еремин

С-Петербург

нет

да

Попов

Москва

да

нет

Колесов

Новосибирск

да

нет

Маслова

Новосибирск

нет

да

Лаптев

С-Петербург

да

да

Локтева

Москва

нет

да

Мохов

С-Петербург

да

нет

 

Рис. 22. Исходные данные для задачи о командировках

 

2. Выполните расчет оплаты проезда в столбце «Оплата проезда»,

 

используя функцию ЕСЛИ и учитывая, что проезд не оплачивается в

 

случае отсутствия документов.

 

3. Выполните расчет проживания в сутки, учитывая, что при наличии документов за проживание расчет производится по предоставленным документам, но не более 270 рублей в сутки. При отсутствии документов начисляется 7 рублей за сутки. Используйте для расчета функцию ЕСЛИ и другие логические функции.

 

4. Рассчитайте суточные, исходя из приведенных тарифов для различных городов, используя функцию ЕСЛИ.

 

5. Рассчитайте сумму к оплате для каждого командированного сотрудника, учитывая, что она равна сумме стоимости проезда, суточных и стоимости проживания. С помощью соответствующих формул вычислите

 

и занесите в отдельные ячейки минимальные, максимальные и средние

 

 

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

 

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

 

просмотра)

 

Задача 1. Задача о складе

 

Постановка задачи. Предположим,что нам необходимо рассчитать

 

сумму скидки на товар, купленный на мелкооптовом складе. Причем скидка предоставляется в зависимости от количества купленного товара:

 

от 100 до 150 штук – 5%, от 150 до 200 – 10%, от 200 до 250 – 15%, от 250

 

до300 – 20%, от 300 до 350 – 25%, более 350 – 30%. Ассортимент и цены приведены в соответствующей таблице (рис. 23).

 

 

Наименование товара	Цена за ед.	К-во, шт.	Стоимость	Скидка
	тов.		товара
Болт металлический	18,55р.
Гайка обычная	19,20р.
Гайка ОП	21,85р.
Шуруп	14,50р.
Винт	12,80р.
Гвоздь средний	4,15р.
Гвоздь малый	3,95р.
Скоба обычная	13,65р.
Скоба большая	17,60р.

Стоимость по прайсу

 

Скидка

 

В кассу

 

Рис. 23. Исходные данные для задачи

 

Методические указания

 

Данная задача может быть решена с помощью применения функции ЕСЛИ. Однако это будет иметь определенные неудобства в силу громоздкости организации формулы. Кроме того, функция ЕСЛИ допускает не более 7 вложений, поэтому не всегда может быть применена.

 

Данную задачу решим с помощью функции ВПР – функции вертикального просмотра данных,которая находится в категории«Ссылки и массивы».

 

 

Функция ВПР ищет заданное значение в первом столбце указанной таблицы и возвращает значение, расположенное в той же строке, что и найденное, в указанном столбце. Использование данной функции требует некоторой подготовительной работы. Предварительно необходимо создать таблицу подстановки, содержащую сведения относительно возможных скидок (рис. 24). Обратите внимание, что данная таблица организована таким образом, что в первом столбце, напротив соответствующей величины скидки, располагается нижняя граница заданных интервалов по сумме заказа. Такая организация таблицы подстановки позволяет выводить значение, расположенное во втором столбце и в том случае, когда заданная величина находится в интервале между любыми двумя значениями,

 

находящимися в первом столбце.

 

Количество	Процент
скидки
	0%
	5%
	10%
	15%
	20%
	25%
	30%

 

Рис. 24. Таблица подстановки

 

Аргументы функции ВПР:

 

Искомое_значение –это значение,которое должно быть найдено впервом столбце массива. Искомое_значение может быть значением,

 

ссылкой или текстовой строкой.

 

Табл_массив –это таблица с информацией,в которой ищутсяданные. Можно использовать ссылку на диапазон ячеек или его имя (в

 

случае, если диапазон предварительно был поименован).

 

Номер_индекса_столбца –это номер столбца в массиве

 

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

 

номер_столбца равен1,то возвращается значение из первого столбца

 

 

аргумента инфо_таблица; если номер_столбца равен 2, то возвращается значение из второго столбца аргумента инфо_таблица и так далее.

 

Диапазон_просмотра –это логическое значение,которое определяет,

 

нужно ли, чтобы ВПР искала точное или приближенное соответствие. Если этот аргумент имеет значение ИСТИНА или опущен, то возвращается приблизительно соответствующее значение; другими словами, если точное соответствие не найдено, то возвращается наибольшее значение, которое меньше чем искомое_значение. Если этот аргумент имеет значение ЛОЖЬ,

 

то функция ВПР ищет точное соответствие. Если таковое не найдено, то возвращается значение ошибки #Н/Д.

 

Итак, заполняя столбец «Скидка», воспользуемся функцией ВПР. В

 

качестве ее аргументов в рассматриваемой задаче необходимо ввести:

 

Искомое значение –указать адрес ячейки,в которой будетнаходиться значение количества купленного товара (Количество);

 

Табл_массив –указать координаты созданной таблицы скидок; Номер_индекса_столбца –набрать на клавиатуре цифру2

 

(столбец, содержащий данные относительно скидок);

 

Диапазон просмотра –ИСТИНА(или опущен).

 

Замечание. Функция ГПР применяется аналогично.Отличиесостоит в том, что таблица подстановки для ее применения располагается горизонтально и основными аргументами являются номера строк.

 

Задача 2. Задача о тестировании.

 

Имеются сведения о результатах тестирования студентов одной из групп некоторого ВУЗа и таблица, по которой комиссия выводит оценку для каждого студента по итогам трех тестов. Необходимо автоматизировать эту работу.

 

 

ФИО	Тест 1	Тест 2	Тест 3
Михайлов А.А.
Муравьев А.Н.
Палкин Н.А.
Щеглов А.П.
Андреев Л.П.
Солодов А.С.
Кошкин П.Е.

 

Рис. 25. Исходные данные для задачи о тестировании

 

По р яд о к раб о ты:

 

1. Создать таблицу по образцу (рис.25).

 

2. Добавить столбец «Результат» и вычислить итоги по трем

 

тестам.

 

3. Ниже таблицы тестирования создать вертикальную Справочную таблицу по образцу (рис. 26).

 

4. Добавить столбец «Оценка» и заполнить его, ссылаясь на справочную таблицу и используя функцию ВПР.

 

5. Ниже Справочной таблицы создать горизонтальную Справочную таблицу по образцу (рис. 26).

 

6. Добавить столбец «Оценка2» и заполнить его оценками,

 

ссылаясь на горизонтальную Справочную таблицу и используя функцию

 

ГПР.

 

Справочная таблица

Балл Оценка

 

0 2

 

18 3

 

33 4

 

49 5

 

 

Балл
Оценка

 

 

Рис. 26. Справочные таблицы

 

Задача 3 (задача для самостоятельной работы). Расчет окладов ипремий.

Используя возможности функции ВПР, рассчитать оклады сотрудникам кафедры экономики и начислить премию в размере 19%. Для расчета оклада использовать таблицу разрядов (рис. 27).

 

 

Мин.оклад
Премия	19%
Разряд	Коэффициент
	3,12
	3,53
	3,99
	4,51
	5,1
	5,76
	6,51
	7,36
	8,17
	9,07
	10,07

 

ФИО	Должность	Разряд
Муравьев Н.С.	проф.
Морозов А.В.	проф.
Гусев И.А.	проф.
Антонов В.А.	доц.
Пирожкова В.А.	доц.
Кабанова М.А.	доц.
Краснов Н.А.	доц.
Белова И.О.	доц.
Кирсанов Ф.Ю.	доц.
Соколов Б.А.	ст.преп.
Мешков Р.Д.	ст.преп.
Маслов Ю.Д.	ст.преп.
Агапова Н.Н.	асс.
Воронов М.Н.	асс.
Реброва Г.Ф.	методист
Козлова А.И.	лаборант

 

Рис. 27. Исходные данные и таблица подстановки для задачи об окладах

 

Задача 4. Использование функций ПОИСКПОЗ и ГПРВ магазине имеется лист цен в виде таблицы (рис. 28). В строке

«Товар» проставлены граничные значения количества товара, а ниже – цены за единицу товара. Для оптовых покупателей цены снижаются.

 

Например, если покупатель приобретает партию из 6 аккумуляторов, он платит по 300 руб. за шт., если партия составит 30 шт., то – по 270 руб.,

 

если 58, то – по 250 руб. Покупатель заказывает товары, представленные в накладной (рис. 28). Необходимо автоматизировать расчет цены за единицу товара, вычислить стоимость товаров каждого наименования и общую сумму заказа.

 

Товар						Накладная
Аккумулятор						Товар	Кол-во
Труба						Труба
Ключ						Задвижка
Задвижка						Ключ

 

Рис. 28. Исходные данные для задачи 4

 

По р яд о к раб о ты:

 

1. Создать «Лист цен» по образцу (рис. 28).

 

2. Ниже создать накладную по образцу (рис. 28). Дополнить ее столбцами «Цена за 1» и «Стоимость».

 

 

3. Изучить справочную информацию о функции ПОИСКПОЗ,

 

которая возвращает позицию искомого значения в массиве, а не само значение. Синтаксис функции приводится ниже.

 

ПОИСКПОЗ (искомое_значение; просматриваемый_массив;тип_

 

сопоставления)

 

Искомое_значение может быть значением(числом,текстом илилогическим значением) или ссылкой на ячейку, содержащую такое значение

 

Просматриваемый_массив –блок,состоящий из одного столбцаили одной строки.

 

Тип_сопоставления –число–1,0,1 (в нашем случае,целесообразновыбрать 0).

4. Заполнить столбец «Цена за 1», используя функцию ГПР (в качестве аргумента номер строки использовать функцию ПОИСКПОЗ).

 

5. Вычислить стоимость закупок каждого наименования и общую стоимость заказа.

 

Лабораторная работа №7. Работа с массивами Методические указания

 

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

 

Массив –прямоугольный диапазон формул или значений,которые

 

MS Excel обрабатывает как единую группу. Прямоугольный числовой массив, состоящий из m строк и n столбцов, принято называть матрицей размерности m n.

 

При работе с массивами необходимо помнить следующие два основных правила:

1. Результатом операции над массивами (матрицами) также является массив (матрица) определенного размера. Поэтому при вводе

 

 

формулы необходимо заранее выделить весь диапазон рабочего листа, где будет находиться будущий результат.

 

2. По окончании ввода формулы нажимать комбинацию клавиш

 

CTRL+SHIFT+ENTER.

 

Две матрицы одинаковой размерности можно сложить или вычесть путем простого сложения или вычитания (получится массив такой же размерности). Функции для работы с матрицами приведены в таблице 5.

 

Таблица 5

 

Название функции	Результат
МОБР(массив)	Возвращает обратную матрицу
МОПРЕД(массив)	Считает определитель матрицы
МУМНОЖ(массив 1;массив 2)	Возвращает матричное произведение двух
	матриц. При этом должно выполняться
	правило: число столбцов массива 1 равно
	числу строк массива 2.
ТРАНСП(массив)	Возвращает транспонированную матрицу

 

 

Функция ТРАНСП находится в категории «Ссылки и массивы», а

 

все остальные – в категории «Математические».

 

Задача 1

 

Выполните следующие действия:

 

		1)		Найдите сумму и разность двух матриц:	A			и
				9 10 1112
		16 15 14 13
		12 11 10
B
							.

 

 

				11 22 33
		2 4 6 8
	44 55 66
2) Найти произведение матриц:				и B
A	1 3 5 7		77 88 99
					12 34 56

 

3) Вычислить определители матриц из п. 1)

 

4) Найти матрицы, обратные к матрицам из п.1)

 

		5)	Решите	систему	уравнений методом обратной матрицы
	=	∙	3 x 1	x 2
(		x 23 x 3
): 2 x 1
	x 12 x 2 x 3

Задача 2 (по А.Ю.Гарнаеву, [2])

 

Решите следующие системы уравнений методом обратной матрицы,

 

предварительно найдя матрицу коэффициентов при неизвестных: 1. A^TAA^TX B, где

 

			9 5
A			B
		1 5 6
2. A3ATX B, где
		4 1 6
A			B
3. AATAX B, где
			6 7
A			B

 

4. A²A^TAX B, где

 

 

		3 6 5 7
		4 6 3 5
A			B
	2 3 2 6
		2 4 3 6
5. AATA2X B, где
		2 1 5 2
		5 2 2 6
A			B
	2 2 1 2
		1 3 3 1

 

Задача 3 (задача для самостоятельной работы)

 

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

 

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

 

						Таблица 6
Брюки, юбки, жилеты	Ткань	Цена за 1 м (руб.)
1,2	0,9	0,75	шерстяная
A	0,6		подкладочная	C
0,7	0,5

 

 

Определить:

 

a) количество метров тканей (D), необходимое для выпуска

 

следующих изделий (таблица 7).

 

Таблица 7

 

		Брюки
B		Юбки
		Жилеты

 

b) общую стоимость тканей (S), если известна их цена (С)

 

 

Лабораторная работа №8. Условное форматирование Методические указания Условное форматирование –это один из способов визуального

 

выделения данных, отвечающих определенным требованиям (критериям).

 

Для его применения необходимо выделить массив данных (без заголовков)

 

и выполнить команду: