2020-08-05
171
Задание
Имеется таблица 73 с наименованиями работ. В таблице 74 приведены данные по учету выполнения этих работ бригадами рабочих в течение некоторого периода.
Заполнение таблиц
Все таблицы разместить на разных листах.
Таблица 73
Таблица заполняется произвольно неповторяющимися значениями (не менее 4 записей).
Таблица 74
1 Столбец A «Код работы» заполняется повторяющимися значениями из таблицы 73.(не менее 10 записей).
2 Поле «Дата» заполнить значениями дат в порядке возрастания.
3 Столбец C заполнить числами в диапазоне от 200 до 500.
4 Столбец D заполнить произвольно целыми числами от 5 до 20, а столбец E - числами из диапазона от 5000 до 15000.
Таблица 73 – Наименования работ
| A | B | |
| 1 | Код работы | Наименование работы |
| 2 | 12 | Погрузка |
Таблица 74 – Учет выполнения работ
| A | B | C | D | E | |
| 1 | Код работы | Дата | Количество древесины, м3 | Число рабочих | Сумма заработной платы, руб. |
Задание
Скопировать таблицу 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 (название листа – Лист1), а на Листе 2 – таблицу 76 (название листа – Лист2).
Таблица 75 – Заполнение наименованиями работ
| A | B | |
| 1 | Код работы | Наименование работы |
| 2 | 12 | Погрузка |
| 3 | 14 | Сортировка |
| 4 | 15 | Транспортировка |
| 5 | 16 | Обрубка сучьев |
Таблица 76 – Заполнение исходными данными учета работ
| A | B | C | D | E | |
| 1 | Код работы | Дата | Количество древесины, м3 | Число рабочих | Сумма заработной платы, руб. |
| 2 | 12 | 12.06.05 | 493,06 | 5 | 6714 |
| 3 | 14 | 13.06.05 | 374,44 | 7 | 14020 |
| 4 | 15 | 14.06.05 | 487,46 | 10 | 6742 |
| 5 | 16 | 15.06.05 | 401,98 | 15 | 12946 |
| 6 | 12 | 16.06.05 | 291,59 | 13 | 14354 |
| 7 | 14 | 17.06.05 | 341,89 | 15 | 14688 |
| 8 | 16 | 20.06.05 | 252,01 | 19 | 13832 |
| 9 | 15 | 21.06.05 | 484,15 | 18 | 10189 |
| 10 | 15 | 22.06.05 | 305,79 | 5 | 9675 |
| 11 | 12 | 23.06.05 | 342,43 | 10 | 11612 |
Для быстрого заполнения таблицы исходными данными и для удобства тестирования желательно пользоваться функцией СЛЧИС. Столбец C заполнили по формуле =ОКРУГЛ(СЛЧИС()*(500-200)+200;2), столбец D заполнили по формуле =ЦЕЛОЕ(СЛЧИС()*(20-5)+5), а столбец E – по формуле =ЦЕЛОЕ(СЛЧИС()*(15000-5000)+5000).
Эти же столбцы можно заполнить с использованием функции СЛУЧМЕЖДУ. Для заполнения столбца С формула =СЛУЧМЕЖДУ(200;500). Для столбца D формула =СЛУЧМЕЖДУ(5;20). Для столбца E формула =СЛУЧМЕЖДУ(5000;15000).
После заполнения таблиц начнем выполнять задания.
Скопируем таблицу 76 на новый лист. Между столбцами «Код работы» и «Дата» вставим столбец «Наименование работы». Для переноса наименований работ из таблицы 75 в пустую ячейку B2 введем формулу
=ЕСЛИ(A2=Лист1!$A$2;Лист1!$B$2;ЕСЛИ(A2=Лист1!$A$2;Лист1!$B$3;ЕСЛИ(A2=Лист1!$A$4;Лист1!$B$4;ЕСЛИ(A2=Лист1!$A$5;Лист1!$B$5;"нет такого кода"))))
Скопируем ее вниз. Результат приведен в таблице 77.
ПРИМЕЧАНИЕ! Допускается так же копирование информации в соответствующие ячейки таблицы БЕЗ ИСПОЛЬЗОВАНИЯ ФОРМУЛ!!!
Таблица 77 – Дополнение столбца «Наименование работы»
| A | B | C | D | E | F | |
| 1 | Код работы | Наименование работы | Дата | Количество древесины, м3 | Число рабочих | Сумма заработной платы, руб. |
| 2 | 12 | Погрузка | 12.06.05 | 493,06 | 5 | 6714 |
| 3 | 14 | Сортировка | 13.06.05 | 374,44 | 7 | 14020 |
| 4 | 15 | Транспортировка | 14.06.05 | 487,46 | 10 | 6742 |
| 5 | 16 | Обрубка сучьев | 15.06.05 | 401,98 | 15 | 12946 |
| 6 | 12 | Погрузка | 16.06.05 | 291,59 | 13 | 14354 |
| 7 | 14 | Сортировка | 17.06.05 | 341,89 | 15 | 14688 |
| 8 | 16 | Обрубка сучьев | 20.06.05 | 252,01 | 19 | 13832 |
| 9 | 15 | Транспортировка | 21.06.05 | 484,15 | 19 | 10189 |
| 10 | 15 | Транспортировка | 22.06.05 | 305,79 | 5 | 9675 |
| 11 | 12 | Погрузка | 23.06.05 | 342,43 | 10 | 11612 |
Вместо функции ЕСЛИ можно использовать функцию ВПР. Для этого формула следующая: =ВПР(A2;Лист1!$A$2:$B$5;2;0).
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 В пятом задании функция МАКС не допускает условий в качестве аргументов. Поэтому введем еще один дополнительный столбец «Показатель4». В ячейку J2 введем =ЕСЛИ(B2="Обрубка сучьев";F2;0) и скопируем ее вниз. Тем самым мы пометили единицами те записи, которые нам нужны для вычислений.
Далее под столбцом таблицы напишем «Задание 5» и введем формулу, используя метки столбца J: =МАКС(J2:J11).
При выполнении задания можно воспользоваться табличной формулой
{=МАКС(ЕСЛИ(B2:B11="Обрубка сучьев";F2:F11;0))}.
В обеих вариантах получим 13832.
6 Скопируем таблицу 77 на новый лист. На новом листе выполним сортировку скопированной таблицы по полю «Наименование работы» командой Данные-Сортировка.
Для автоматического получения итоговых значений выполним команду Данные-Итоги. В появившемся диалоговом окне укажем нужные значения (рисунок 1).
Рисунок 1 – Окно для получения итогов
Щелкнем по кнопке Ok. На месте отсортированной таблицы будут выведены записи, представленные таблицей 78.
Щелчком мыши на значках «-» слева от таблицы скроем исходные записи, оставив только итоговые (таблица 79).
Таблица 78 – Дополнение данных промежуточными итогами по зарплате
| Код работы | Наименование работы | Дата | Количество древесины, м3 | Число рабочих | Сумма заработной платы, руб. |
| 16 | Обрубка сучьев | 15.06.05 | 401,98 | 15 | 12946 |
| 16 | Обрубка сучьев | 20.06.05 | 252,01 | 19 | 13832 |
|
| Обрубка сучьев Итог |
|
|
| 26778 |
| 12 | Погрузка | 12.06.05 | 493,06 | 5 | 6714 |
| 12 | Погрузка | 16.06.05 | 291,59 | 13 | 14354 |
| 12 | Погрузка | 23.06.05 | 342,43 | 10 | 11612 |
|
| Погрузка Итог |
|
|
| 32680 |
| 14 | Сортировка | 13.06.05 | 374,44 | 7 | 14020 |
| 14 | Сортировка | 17.06.05 | 341,89 | 15 | 14688 |
|
| Сортировка Итог |
|
|
| 28708 |
| 15 | Транспортировка | 14.06.05 | 487,46 | 10 | 6742 |
| 15 | Транспортировка | 21.06.05 | 484,15 | 19 | 10189 |
| 15 | Транспортировка | 22.06.05 | 305,79 | 5 | 9675 |
|
| Транспортировка Итог |
|
| 26606 | |
|
| Общий итог |
|
|
| 114772 |
Таблица 79 – Сумма зарплаты по видам работ
| Код работы | Наименование работы | Дата | Количество древесины, м3 | Число рабочих | Сумма заработной платы, руб. |
| 16 | Обрубка сучьев Итог |
|
|
| 26778 |
| 12 | Погрузка Итог |
|
|
| 32680 |
| 14 | Сортировка Итог |
|
|
| 28708 |
| 15 | Транспортировка Итог |
|
| 26606 | |
|
| Общий итог |
|
|
| 114772 |
Выделим указателем мыши два столбца «Наименование работы» и «Сумма заработной платы», захватывая сами заголовки. Эти столбцы представляют собой несмежные участки, поэтому при выделении надо удерживать клавишу Ctrl. Общий итог захватывать не надо. Вызовем Мастер диаграмм командой Вставка-Диаграмма. Следуя инструкциям Мастера, построим гистограмму (рис. 2).
Рисунок 2 – Гистограмма
Для построения круговой диаграммы надо опять выделить те же столбцы и вызвать Мастер диаграмм. Результат представлен на рисунке 3.
Рисунок 3 – Круговая диаграмма
