Пример выполнения контрольной работы В данном документе показаны способы выполнения заданий в Excel, типичных для всех вариантов контрольной работы №2

Пример выполнения контрольной работы

В данном документе показаны способы выполнения заданий в Excel, типичных для всех вариантов контрольной работы №2. В отчет по работе, который выполняется в Word, следует только копировать таблицы и результаты расчетов в двух вариантах: числовом (обычном) и формульном. Указание. Для отображения результатов расчета в Excel в формульном виде необходимо выполнить следующие действия: Кнопка Excel – Параметры Excel (внизу) – Дополнительно – Экран – установить флажок «показывать формулы, а не их значения».

 

Имеется таблица 73 с наименованиями работ. В таблице 74 приведены данные по учету выполнения этих работ бригадами рабочих в течение некоторого периода.

Заполнение таблиц

Таблица 73 Таблица заполняется произвольно неповторяющимися значениями (не менее 4… Таблица 74

Задание

Скопировать таблицу 74 на новый лист, добавить в нее столбец «Наименование работы» и выполнить задания.

1 Ввести новый столбец «Показатель 1» и заполнить его следующим образом: если число рабочих превышает 10 человек, то вывести 1, иначе – 0.

2 Ввести еще один столбец «Показатель 2» и заполнить его следующим образом: если работа имеет наименование «Погрузка» или «Сортировка», то 1, иначе – 0.

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

3 Какое количество древесины переработано малочисленными бригадами в составе не более 10 человек?

4 Сколько дней на работе с кодом 12 перерабатывалось древесины от 300 до 400 м³?

5 Найти максимальную зарплату, выплаченную за работу «Обрубка сучьев».

6 Скопировать таблицу 74 на Лист 4 и выполнить следующие задания:

– отсортировать таблицу по наименованию работы;

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

– скрыть записи исходного списка, оставив только итоговые данные;

– построить по итоговым данным гистограмму и круговую диаграмму.

Выполнение расчетов проведем в табличном процессоре Excel. Заполним, прежде всего, таблицы исходными данными. На Листе 1 создадим таблицу 75, а на Листе 2 – таблицу 76.

 

Таблица 75 – Заполнение наименованиями работ

 

	A	B
	Код работы	Наименование работы
		Погрузка
		Сортировка
		Транспортировка
		Обрубка сучьев

 

Таблица 76 – Заполнение исходными данными учета работ

 

	A	B	C	D	E
	Код работы	Дата	Количество древесины, м3	Число рабочих	Сумма заработной платы, руб.
		12.06.05	493,06
		13.06.05	374,44
		14.06.05	487,46
		15.06.05	401,98
		16.06.05	291,59
		17.06.05	341,89
		20.06.05	252,01
		21.06.05	484,15
		22.06.05	305,79
		23.06.05	342,43

 

Для быстрого заполнения таблицы исходными данными и для удобства тестирования желательно пользоваться функцией СЛЧИС. Столбец C заполнили по формуле =ОКРУГЛ(СЛЧИС()*(500-200)+200;2), столбец D заполнили по формуле =ЦЕЛОЕ(СЛЧИС()*(20-5)+5), а столбец E – по формуле =ЦЕЛОЕ(СЛЧИС()*(15000-5000)+5000).

После заполнения таблиц начнем выполнять задания.

Скопируем таблицу 76 на новый лист. Между столбцами «Код работы» и «Дата» вставим столбец «Наименование работы». Для переноса наименований работ из таблицы 75 в пустую ячейку B2 введем формулу =ВПР(A2;Лист1!A2:B5;2;0). Скопируем ее вниз. Результат приведен в таблице 77.

 

Таблица 77 – Дополнение столбца «Наименование работы»

	A	B	C	D	E	F
	Код работы	Наименование работы	Дата	Количество древесины, м3	Число рабочих	Сумма заработной платы, руб.
		Погрузка	12.06.05	493,06
		Сортировка	13.06.05	374,44
		Транспортировка	14.06.05	487,46
		Обрубка сучьев	15.06.05	401,98
		Погрузка	16.06.05	291,59
		Сортировка	17.06.05	341,89
		Обрубка сучьев	20.06.05	252,01
		Транспортировка	21.06.05	484,15
		Транспортировка	22.06.05	305,79
		Погрузка	23.06.05	342,43

 

1 Новый столбец G назовем «Показатель1». В ячейку G2 введем формулу =ЕСЛИ(E2>10;1;0) и скопируем ее в нижние ячейки.

2 В ячейку H1 введем заголовок нового столбца «Показатель2». А в ячейку H2 введем формулу

=ЕСЛИ(ИЛИ(B2="Погрузка";B2="Сортировка");1;0). Затем скопируем ее вниз. После этого столбец H будет содержать метки: единицы, если в соответствующей строке в столбце B имеется текст «Погрузка» или «Сортировка», и нули напротив других работ.

3 Ниже, под таблицей напишем «Задание 3». Рядом введем формулу =СУММЕСЛИ(E2:E11;"<=10";D2:D11). Можно это задание выполнить табличной формулой, которая вводится сочетанием клавиш Ctrl-Shift-Enter:

{=СУММ(ЕСЛИ(E2:E11<=10;D2:D11;0))}.

Результат будет один и тот же: 2003,18.

4 Для выполнения 4-го задания требуется функция СЧЁТЕСЛИ. Но эта функция допускает только простое условие в качестве второго аргумента. Поэтому наше сложное условие (работа с кодом 12, количество древесины больше одного числа, но меньше другого) сначала упростим путем введения еще одного дополнительного столбца «Показатель3». В ячейку I2 введем =ЕСЛИ(И(A2=12;D2>300;D2<400);1;0) и скопируем ее вниз. Тем самым мы пометили единицами те записи, которые нам нужны для вычислений.

Далее под таблицей напишем «Задание 4» и введем формулу, используя метки столбца I: =СЧЁТЕСЛИ(I2:I11;1). Можно использовать табличную формулу

{=СЧЁТ(ЕСЛИ(A2:A11=12;ЕСЛИ(D2:D11>300;ЕСЛИ(D2:D11<400;1;"a");"a");"a"))}.

Результат должен быть равен 1.

5 Для выполнения 5 задания создадим дополнительный столбец «Показатель 4», введем в него формулу

=ЕСЛИ(B2="Обрубка сучьев";F2;0).

Затем под таблицей напишем «Задание 5» и впишем формулу

=МАКС(J3:J12)

Получим 13832.

6 Скопируем таблицу 77 на новый лист. На новом листе выполним сортировку скопированной таблицы по полю «Наименование работы» командой Данные-Сортировка.

Для автоматического получения итоговых значений выполним команду Данные-Промежуточные Итоги. В появившемся диалоговом окне укажем нужные значения (рис. 1).

Рисунок 1 – Окно для получения итогов

Щелкнем по кнопке Ok. На месте отсортированной таблицы будут выведены записи, представленные таблицей 78.

Щелчком мыши на значках «-» слева от таблицы скроем исходные записи, оставив только итоговые (таблица 79).

 

Таблица 78 – Дополнение данных промежуточными итогами по зарплате

Код работы	Наименование работы	Дата	Количество древесины, м3	Число рабочих	Сумма заработной платы, руб.
	Обрубка сучьев	15.06.05	401,98
	Обрубка сучьев	20.06.05	252,01
	Обрубка сучьев Итог
	Погрузка	12.06.05	493,06
	Погрузка	16.06.05	291,59
	Погрузка	23.06.05	342,43
	Погрузка Итог
	Сортировка	13.06.05	374,44
	Сортировка	17.06.05	341,89
	Сортировка Итог
	Транспортировка	14.06.05	487,46
	Транспортировка	21.06.05	484,15
	Транспортировка	22.06.05	305,79
	Транспортировка Итог
	Общий итог

 

Таблица 79 – Сумма зарплаты по видам работ

 

Код работы	Наименование работы	Дата	Количество древесины, м3	Число рабочих	Сумма заработной платы, руб.
	Обрубка сучьев Итог
	Погрузка Итог
	Сортировка Итог
	Транспортировка Итог
	Общий итог

 

Выделим указателем мыши два столбца «Наименование работы» и «Сумма заработной платы», захватывая сами заголовки. Эти столбцы представляют собой несмежные участки, поэтому при выделении надо удерживать клавишу Ctrl. Общий итог захватывать не надо. Вызовем Мастер диаграмм командой Вставка-Диаграмма. Следуя инструкциям Мастера, построим гистограмму (рис. 2).

 

Рисунок 2 – Гистограмма

 

Для построения круговой диаграммы надо опять выделить те же столбцы и вызвать Мастер диаграмм. Результат представлен на рисунке 3.

 

Рисунок 3 – Круговая диаграмма