Excel_8
.pdfПрактическая работа 8
Поиск в таблицах
Цель работы: получить понятие о стандартных функциях поиска в таблицах
Указания к выполнению.
Необходимо, исходя из справочника тарифов, заполнить столбец окладов в таблице распределения рабочих по цехам и разрядов. Для каждого цеха рассчитать сумму окладов рабочих.
Данные всех трех таблиц, приведенных на следуюшей странице, должны быть взаимосвязанными. Необходимость такой взаимосвязанностидиктуетсяосновнымпринципомпостроенияэлектронных таблиц: данные надо связывать так, чтобы изменения в исходных данных автоматически приводили бы к изменению связанных данных. Следовательно, неправильно было бы заполнить столбец окладов в таблице работников вручную: при изменении тарифной сетки пришлось бы позаботиться об изменении основной таблицы.
•В ячейку D3 вставьте функцию ВПР (категория Ссылки и массивы). Для этой функции заполните следующие поля ввода:
1.Искомое_значение.Адресячейкисразрядомсотрудника(B3).
2.Таблица.Диапазонячеектаблицы-справочника(B16:C20).
3.Номер_столбца. Номер столбца в таблице-справочнике с окладами сотрудников (2).
•Для того, чтобы при распространении формулы адреса ячеек, указывающих на таблицу-справочник не изменялись, сделайте их абсолютными, добавив знак $ к адресам ячеек в строке формул. В итоге в ячейке D3 должна быть записана формула
=ВПР(B3;$B$16:$C$20;2)
•Распространите полученную формулу на ячейки D4 - D12.
•Для расчета сумм окладов, соответствующих отдельным разрядам, введите в ячейку C24 функцию СУММЕСЛИ (категория Математические), указав в качестве параметров следующие поля:
1.Диапазон - столбец с номерами цехов (C3 : C12).
63
2.Критерий - адрес ячейки с номером цеха, для которого нужно рассчитать сумму окладов (B24).
3.Диапазон_суммирования - столбец с окладами сотрудни-
ков (D3 : D12).
•Перед распространением формулы сделайте абсолютными неизменяемые адреса ячеек. Должна получиться формула вида
=СУММЕСЛИ($C$3:$C$12;B24;$D$3:$D$12).
64
•Составьте дополнительную справочную таблицу надбавок сотрудникам для каждого цеха: 1-го - 200 руб; 2-го - 350 руб; 3-го - 150 руб. На основании этой таблицы добавьте к основной таблице столбец надбавок сотрудников, используя функцию ВПР.
•Исправьте таблицу распределения окладов по цехам, добавив суммы надбавок работников. Введите дополнительный столбец Суммы надбавок и рассчитайте данные столбца с помощью функции СУММЕСЛИ.
Самостоятельная работа.
Исходя из таблицы соответствия дат знакам зодиака, стихий, планет, году по восточному календарю, определить по дате рождения,какомузнакузодиака,стихии,планете,годусоответствует данный человек.
Указания к выполнению.
•Введите в ячейку F1 формулу, определяющую год даты рождения:функцияГОД(категорияДатаивремя).ВполеДата_в_числовом_форматезадайтеадресячейкисдатойрождения(C1).
•В ячейку A19 таблицы введите функцию ДАТА (категория Дата и время), определяющую дату, год которой совпадает с годом даты ячейки C1. В поле Год задайте адрес ячейки E1; в поле Месяц введите 12; в поле День введите 22.
•Аналогичные формулы введите в ячейки A7—A18.
•В ячейку C2 занесите функцию ВПР поиска знака зодиака по дате рождения. Параметры функции: Искомое_значение: дата рождения (ячейка C1); Таблица: таблица поиска (A8 : E20); Номер_столбца: столбец со знаками зодиака (2).
•Аналогичные формулы занесите в ячейки C3 и C4.
Обратите внимание, что в этом случае функция ВПР ищет значения не по полному совпадению ключевых параметров, а по признаку «наибольшего меньшего». Очевидно, что и в этом случае значения в первом столбце должны быть расположены по возрастанию.
•Вычислите остаток от деления года рождения на 12. Для этого введите в ячейку F2 функцию ОСТАТ (категория Математические). Параметры функции: Число: год рождения (ячейка F1); Делитель: 12.
65
•В ячейку C5 занесите функцию ВПР поиска года по восточному календарю. Параметры формулы: Искомое_значение: остаток (ячейка E2); Таблица: таблица поиска (B8 : F19); Номер_столбца: столбец с годами по восточному календарю(5).
Практическая работа 9
Формулы массивов
Цель работы: получить понятие о приемах работы с массивами
Указания к выполнению.
Необходимо, исходя из результатов сдачи ЕГЭ по информатикеопределитьоценкишкольниковподаннойдисциплинеиколичество учащихся, имеющих каждую оценку.
66