Пример выполнения контрольной работы В данном документе показаны способы выполнения заданий в 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 м3?
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 – Круговая диаграмма