Лабораторная работа 3

Применение логических, текстовых функций, функций даты

и времени в вычислениях

Цель работы:

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

освоение приемов работы по преобразованию и связыванию ячеек таблиц.

Постановка задачи

Дана исходная таблица, содержащая некоторую информацию: фамилии, имена, должности, оклады, результаты экзаменов и т. д.

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

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

Пример

Дана таблица. Преобразованная таблица должна содержать колонки: «№», «ФИО», «Стаж на предприятии», «Премиальный коэффициент», зависящий от стажа работы.

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

1. Новый лист книги назвать «Преобразование таблицы».

2. В первой строке ввести заголовок «Исходная таблица».

3. Ввести данные в таблицу

А	В	С	D	Е	F
	Исходная таблица
	№	Фамилия	Имя	Отчество	Дата приёма на работу
		Иванов	Иван	Андреевич	10.10.01
		Петров	Михаил	Васильевич	01.02.89
		Васильев	Николай	Николаевич	12.11.90
		Сидоров	Павел	Иванович	15.12.96
		Мамин	Петр	Кузьмич	20.06.92
		Букин	Валерий	Олегович	14.08.90

4. В двенадцатой строке ввести заголовок «Преобразованная таблица», в соответствующих ячейках ниже – названия колонок: «№», «ФИО», «Стаж на предприятии» и «Премиальный коэффициент».

5. Создание инициалов с помощью текстовых функций

В ячейку C14 запишите формулу = С3&” “&ЛЕВСИМВ(D3;1)&”.”& ЛЕВСИМВ(E3;1)&”.”

6. Определение стажа в годах с помощью функции даты и времени.

Год определяется как целое в интервале от 1900 до 9999. В ячейку D14 запишите формулу = ГОД(СЕГОДНЯ() – F3) – 1900. При необходимости измените формат данных в ячейке на числовой.

Стаж будет не совпадать с примером, так как в формуле использована функция «СЕГОДНЯ()». Значением функции «СЕГОДНЯ()» является текущая дата.

7. Величина коэффициента премии зависит от стажа.

0 – 5 лет – 0,4 оклада

6 – 10 лет – 0,7 оклада

больше 11 лет – 1 оклад.

Один из вариантов использования логической функции «если» для выполнения указанных условий приведен ниже.

В ячейку E14 запишите формулу: = ЕСЛИ(D14<6; 0,4; ЕСЛИ(D14>11; 1; 0,7)).

Таблица примет вид:

А	В	С	D	Е
	Преобразованная таблица
	№	ФИО	Стаж на предприятии	Премия
		Иванов И.А.	4,00	0,4
		Петров М.В.	17,00
		Васильев Н.Н.	15,00
		Сидоров П. И	9,00	0,7
		Мамин П.К.	13,00
		Букин В.О.	15,00

В результате выполнения лабораторной работы лист книги Excel должен содержать две таблицы исходную и преобразованную.

Варианты заданий

Преобразование таблиц

Вариант 1. Стипендия

А	В	С	D	Е
		Результаты экзаменов
Физика	математика	история
	№	Фамилия	Имя	Отчество
		Иванов	Иван	Андреевич
		Петров	Михаил	Васильевич
		Васильев	Николай	Николаевич
		Сидоров	Павел	Иванович
		Мамин	Петр	Кузьмич
		Букин	Валерий	Олегович

Преобразованная таблица должна содержать порядковый номер, фамилию и инициалы, а также вид стипендии (повышенная, обычная, нет стипендии) по итогам сдачи сессии. Повышенная стипендия назначается, если все отметки – 5, обычная – если есть хотя бы одна 4 и не назначается стипендия, если есть хотя бы одна 3.

Вариант 2. Табель

Всего рабочих дней в месяце – 24

А	В	С	D	Е	F
		Проработано дней в месяце
1 - ая половина	2 - ая половина	Всего дней
	№	Фамилия	Имя	Отчество	оклад
		Иванов	Иван	Андреевич
		Петров	Михаил	Васильевич
		Васильев	Николай	Николаевич
		Сидоров	Павел	Иванович
		Мамин	Петр	Кузьмич
		Букин	Валерий	Олегович

Преобразованная таблица должна содержать столбцы «№», «ФИО», «оклад» и «начислено». Столбец «ФИО» содержит фамилию, первые два символа имени и первую букву отчества. Символы имени и отчества заканчиваются точками. Столбец «начислено» – вычисляемый.

Вариант 3. Путешествие. График круиза

	№	Город	Дата приезда	Дата отъезда
		Москва	12.06.05	17.06.05
		Владимир	17.06.05	19.06.05
		Суздаль	19.06.05	23.06.05
		Ростов	23.06.05	26.06.05
		Кострома	26.06.05	30.06.05

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

Вариант 4. Абитуриент. Результаты вступительных экзаменов

А

В

С

D

Е

Результаты экзаменов

Сумма баллов

Физика

Математика

Русский язык

№

Фамилия

Имя

Отчество

Иванов

Иван

Андреевич

Петров

Михаил

Васильевич

Васильев

Николай

Николаевич

Сидоров

Павел

Иванович

Мамин

Петр

Кузьмич

Букин

Валерий

Олегович

Проходной балл на специальности

Мировая экономика
Управление персоналом
Прикладная информатика

Преобразованная таблица должна содержать столбцы «№», «ФИО», «специальность», на которую зачислен абитуриент. Столбец «ФИО» содержит фамилию, первую букву имени и первую букву отчества. Символы имени и отчества заканчиваются точками. Столбец «специальность» содержит название специальности или выражение «Ждём на будущий год».

Вариант 5. Трудовая книжка Иванова Ивана Андреевича

А	В	С	D	Е
	Исходная таблица
	№	Должность	Дата поступления	Дата увольнения
		Лаборант	01.10.87	20.04.90
		Техник	01.05.90	30.04.92
		Ст. техник	15.06.92	25.11.98
		Инженер	01.01.99	31.08.01
		Ведущий специалист	02.09.01	01.10.03
		Начальник отдела	02.10.03	11.03.06

Преобразованная таблица должна содержать столбцы «№», «Стаж в должности» в годах и строку с формирующейся фразой «Общий трудовой стаж Иванова И.А. составляет …. лет». При формировании этой строки должен быть использован адрес ячейки, содержащий общий стаж работника.

Вариант 6. Телефоны

А	В	С	D	Е
	Исходная таблица
	№	Фамилия	Имя	Отчество	Телефон
		Иванов	Иван	Андреевич
		Петров	Михаил	Васильевич
		Васильев	Николай	Николаевич
		Сидоров	Павел	Иванович
		Мамин	Петр	Кузьмич
		Букин	Валерий	Олегович

Преобразованная таблица должна содержать две строки в заданном формате, которые формируется автоматически

Фамилия	Иванов И.А
Телефон	31-01-00

Вариант 7. Здоровье

А	В	С	D	Е	F
	Дневной стационар
	№	Фамилия	Имя	Отчество	Температура
		Иванов	Иван	Андреевич	37,5
		Петрова	Мария	Васильевна	36,6
		Васильев	Николай	Николаевич	38,0
		Сидорова	Анна	Ивановна	36,9
		Мамин	Петр	Кузьмич	37,6
		Букин	Валерий	Олегович	37,9

Преобразованная таблица «Состояние здоровья» должна содержать столбцы «№», «ФИО», «Заключение врача» и строку «Текущая дата». Столбец «ФИО» содержит фамилию, первую букву имени и первую букву отчества. Символы имени и отчества заканчиваются точками. Столбец «Заключение врача» (болен, больна, здоров, здорова – в зависимости от рода) заполняется с условием – здоров(а), если температура меньше 37º.

Вариант 8. Оплата жилья

А	В	С	D	Е
	Коттедж №1
	№	Фамилия	Имя	Отчество	Дата последней оплаты
		Иванов	Иван	Андреевич	01.04.08
		Петров	Михаил	Васильевич	20.02.08
		Васильев	Николай	Николаевич	02.03.08
		Сидоров	Павел	Иванович	15.03.08
		Мамин	Петр	Кузьмич	20.03.08
		Букин	Валерий	Олегович	11.03.08

Преобразованная таблица «Долги по оплате жилья» должна содержать столбцы «порядковый номер», «фамилию и инициалы», «факт задолженности на 1 марта 2008 года» и «Долг». Из них формируемыми должны быть «фамилия и инициалы», «факт задолженности на 1 марта 2008 года» и величина долга в процентах с учетом пени. Пени начисляется из расчета 0,1% за каждый просроченный день.

Вариант 9. Командировки

А	В	С	D	Е
	График пребывания сотрудников в командировке
	№	Фамилия	Имя	Отчество	Дата отъезда	Длительность командировки	город
		Иванов	Иван	Андреевич	12.01.06		Москва
		Петров	Михаил	Васильевич	01.02.06		Пермь
		Васильев	Николай	Николаевич	22.01.06		Омск
		Сидоров	Павел	Иванович	15.01.06		Минск
		Мамин	Петр	Кузьмич	20.01.06		Кемерово
		Букин	Валерий	Олегович	21.01.06		Париж

Преобразованная таблица должна содержать столбцы «№», «ФИО», «Наличие на рабочем месте».

Из них формируемыми должны быть «ФИО» и «Наличие на рабочем месте». Столбец «ФИО» содержит фамилию, первую букву имени и первую букву отчества. Символы имени и отчества заканчиваются точками. Столбец «Наличие на рабочем месте» определяется фактом пребывания или не пребывания сотрудника в командировке на 01.02.06 - «вернулся из Пермь» или «не вернулся из Пермь».

Вариант 10. Пребывание сотрудников за границей

А	В	С	D	Е
	График пребывания сотрудников в командировке
	№	Фамилия	Имя	Отчество	Дата отъезда	Длительность	страна
		Иванов	Иван	Андреевич	10.01.06		Кипр
		Петров	Михаил	Васильевич	01.02.06		Китай
		Васильев	Николай	Николаевич	02.03.06		Италия
		Сидоров	Павел	Иванович	15.01.06		Турция
		Мамин	Петр	Кузьмич	20.01.06		Китай
		Букин	Валерий	Олегович	11.03.06		США

Преобразованная таблица должна содержать столбцы «№», «ФИО», «Место пребывания» и «Причина отсутствия».

Столбец «ФИО» содержит фамилию, первую букву имени и первую букву отчества. Столбец «Место пребывания» сотрудника на 21.01.06 содержит название страны пребывания или Россия. Столбец «Причина выезда за границу» заполняется с учетом условия: если длительность пребывания в другой стране более 8 дней – отпуск, в противном случае - командировка.

Вариант 11. Расчет премии за работу в издательстве

№	Фамилия	Имя	Отчество	Объем страниц	Дата сдачи работы
	Иванов	Иван	Андреевич		24.02.2012
	Петров	Михаил	Васильевич		13.01.2012
	Васильев	Николай	Николаевич		25.02.2012
	Сидоров	Павел	Иванович		09.02.2012
	Мамин	Петр	Кузьмич		10.01.2012
	Егорова	Валерия	Олеговна		01.03.2012

Размер премии зависит от даты сдачи и объёма сданного материала. Норма 10 страниц. Дата сдачи работы - 25 февраля 2012 года. Если работник сделал больше 10 страниц и сдал их вовремя или раньше, то он получает за каждую страницу сверх нормы по 1250 рублей премии. Если сдал не вовремя, то получает понижающий коэффициент к зарплате – 0,1 % за каждый день задержки.

Преобразованная таблица должна содержать столбцы «№», «ФИО», «Премия», «Коэффициент».

Столбец «ФИО» содержит фамилию, первую букву имени и первую букву отчества. Каждая ячейка столбца «Премия» содержит размер премии в рублях, который рассчитывается в зависимости от условий и проверяется изменением данных в исходной таблице. Столбец «Коэффициент» содержит коэффициент равный единице(100 %) или понижающий коэффициент, если таковой есть, в процентах от заработной платы.

Дата добавления: 2015-11-15