Excel. Практическая работа №13. (4 часа).

"Обработка данных в электронных таблицах"

Цели:

• Научиться создавать расчетные таблицы с использованием операций над данными в формате дата\время, логической функции если() и других вычислительных возможностей Excel.
• Научиться интерпретировать созданную расчетную таблицу как базу данных, применять основные операции по обработке баз данных - сортировка, фильтрация, группировка и др.

 

Ход работы:

1. Получить (уточнить) у преподавателя номер своего варианта.

2. Выбрать из списка номер вашего варианта и внимательно прочитать условие задания:

1 2 3 4 5 6 7 8 9 10 11 12

3. Если возникли вопросы по условию задания, задайте их преподавателю.

4. Внимательно изучить пример.

5. Выполнить задание, созданный документ сохранить в своей рабочей папке.

6. Сдать работу - скопировать папку с работой НомерГруппы_Фамилия_НомерРаботы, например, 218т_Иванов_13 в папку //Aud27_0/ЛР_Сдача/ (ярлык к папке на рабочем столе).

Индивидуальные задания

Вариант 1.

1) Создать следующий документ:

Журнал учета времени эксплуатации ПЭВМ и почасовой оплаты за предоставление аренды.

Тариф оплаты за 1 час: 1,20р.

 

Примечания:

а) Размер скидки зависит от продолжительности эксплуатации:

                    - если продолжительность < 10 часов: скидка = 0;

                    - от 10 до 24 часов: скидка = 0.2 * Оплата;

                    - если продолжительность > 24 часов: скидка = 0.4 * Оплата.

б) Тариф оплаты может изменяться.

(5 баллов)

2) Скопировать созданную таблицу с 1-го на 2-й лист. Установив на 1-м листе «Автофильтр», отобразить данные, у которых срок эксплуатации закончился в октябре 2019 г. и сумма больше 20р.

(2 балла)

3) На 2-м листе выполнить группировку (Сортировка… + Итоги…) данных по полю "Клиент" с вычислением итоговой продолжительности и суммы оплаты для каждого клиента за все его заказы и общий итог для всех клиентов.

(3 балла)

 

 Вариант 2.

 

1) Создать следующий документ:

Журнал учета времени нахождения в рейсе и почасовой оплаты труда водителей.

Тариф оплаты за 1 час: 4,20р.

 

Примечание:

а) Размер премии зависит от продолжительности рейса:

                    - если продолжительность < 6 часов: премия = 0.1 * Оплата;

                    - от 6 до 12 часов: премия = 0.2 * Оплата;

                    - если продолжительность > 12 часов: премия = 0.4 * Оплата.

б) Тариф оплаты может изменяться.

(5 баллов)

2) Скопировать созданную таблицу с 1-го на 2-й лист. Установив на 1-м листе «Автофильтр», отобразить данные за сентябрь 2019 г. у которых сумма больше 30р.

(2 балла)

3) На 2-м листе выполнить группировку (Сортировка… + Итоги…) данных по полю "Водитель" с вычислением итоговой продолжительности и суммы оплаты для каждого водителя за все его рейсы и общий итог для всех водителей.

(3 балла)

 

 Вариант 3.

 

1) Создать следующий документ:

Журнал учета времени нахождения в рейсе и почасовой оплаты труда водителей.

Тариф оплаты за 1 час: 4,20р.

 

Примечания:

а) Размер премии зависит от продолжительности рейса:

                    - если продолжительность < 4 часов: премия = 0.15 * Оплата;

                    - от 4 до 8 часов: премия = 0.25 * Оплата;

                    - если продолжительность > 8 часов: премия = 0.4 * Оплата.

б) Тариф оплаты может изменяться.

(5 баллов)

2) Скопировать созданную таблицу с 1-го на 2-й лист. Установив на 1-м листе «Автофильтр», отобразить данные за сентябрь 2019 г. у которых время выезда из гаража раньше 10:00.

(2 балла)

3) На 2-м листе выполнить группировку (Сортировка… + Итоги…) данных по полю "Дата" с вычислением итоговой продолжительности и суммы оплаты за каждый день для всех водителей и общий итог по всем данным.

(3 балла)

 

 

Вариант 4.

 

1) Создать следующий документ:

Вычисление износа и текущей цены для автомобилей автопарка №2 г. Лида

на _______________(текущая (сегодняшняя) дата)

Коэффициент амортизации (доля износа от начальной цены за 1 год): 0,05

 

Примечания:

а) Если «Износ» превысил 95% от «Начальной цены», то считать его равным 95% «Начальной цены».

б) Коэффициент амортизации может изменяться.

(5 баллов)

2) Скопировать созданную таблицу с 1-го на 2-й лист. Установив на 1-м листе «Автофильтр», отобразить данные, у которых год начала эксплуатации  1998 и текущая цена больше $5000.

(2 балла)

3) На 2-м листе выполнить группировку (Сортировка… + Итоги…) данных по полю "Модель" с вычислением средней суммы износа и текущей цены для автомобилей одной модели и общий итог по всем данным.

(3 балла)

 

Вариант 5.

 

1) Создать следующий документ:

Журнал учета телефонных переговоров с посекундной тарификацией.

Тариф оплаты за 1 cек.: внутри сети: 0,01р.; в другие сети: 0,03р.

 

Примечание: Тарифы оплаты могут изменяться.

(5 баллов)

 

2) Скопировать созданную таблицу с 1-го на 2-й лист. Установив на 1-м листе «Автофильтр», отобразить данные по звонкам за октябрь 2019 внутри сети.

(2 балла)

3) На 2-м листе выполнить группировку (Сортировка… + Итоги…) данных по полю "Дата" с вычислением итоговой суммы за каждый день и общий итог по всем данным.

(3 балла)

 

 Вариант 6.

 

1) Создать следующий документ:

Журнал учета телефонных переговоров  с поминутной тарификацией.

Тариф оплаты за 1 мин.: внутри сети: 0,10р.; в другие сети: 0,25р.

 

Примечание: Тарифы оплаты могут изменяться.

(5 баллов)

 

2) Скопировать созданную таблицу с 1-го на 2-й лист. Установив на 1-м листе «Автофильтр», отобразить данные по звонкам в период с 1.09.2019 по 15.09.2019 во внешние сети.

(2 балла)

3) На 2-м листе выполнить группировку (Сортировка… + Итоги…) данных по полю "Абонент" с вычислением итоговой суммы для каждого абонента по всем его звонкам и общий итог по всем данным.

(3 балла)

 

 Вариант 7.

 

1) Создать следующий документ:

Ведомость оплаты за электроэнергию

Тариф оплаты за 1 Квт * ч: 0,85р. 

Пеня за 1 просроченный день: 0,05% от суммы.

 

Примечание: Тариф оплаты и % пени могут изменяться.

(5 баллов)

 

2) Скопировать созданную таблицу с 1-го на 2-й лист. Установив на 1-м листе «Автофильтр», отобразить данные по просроченным платежам за сентябрь 2019.

(2 балла)

3) На 2-м листе выполнить группировку (Сортировка… + Итоги…) данных по полю "Абонент" с вычислением итоговой суммы к оплате и количества электроэнергии для каждого абонента по всем его платежам и общий итог по всем данным.

(3 балла)

 

Вариант 8.

 

1) Создать следующий документ:

Ведомость оплаты за электроэнергию

Тариф оплаты за 1 Квт * ч: 0,85р. 

Пеня за 1 просроченный день: 0,05% от суммы.

 

Примечание: Тариф оплаты и % пени могут изменяться.

(5 баллов)

 

2) Скопировать созданную таблицу с 1-го на 2-й лист. Установив на 1-м листе «Автофильтр», отобразить данные по платежам за октябрь 2019 с итоговой суммой к оплате меньше 100р.

(2 балла)

3) На 2-м листе выполнить группировку (Сортировка… + Итоги…) данных по полю "Дата оплаты" с вычислением итоговой суммы к оплате и количества электроэнергии за каждый день и общий итог по всем данным.

(3 балла)

 

Вариант 9.

 

1) Создать следующий документ:

Ведомость оплаты за эксплуатацию оборудования

Тариф оплаты за 1 час: 0,85р.

 

Примечания:

а) Ставка налога зависит от суммы к оплате:

                    - если сумма < 4р.: налог = 0.07 * Сумма;

                    - от 4р. до 8р.: налог = 0.12 * Сумма;

                    - если сумма > 8р.: налог = 0.20 * Сумма.

б) Тариф оплаты может изменяться.

(5 баллов)

 

2) Скопировать созданную таблицу с 1-го на 2-й лист. Установив на 1-м листе «Автофильтр», отобразить данные за октябрь 2019 с суммой к оплате больше 8р.

(2 балла)

3) На 2-м листе выполнить группировку (Сортировка… + Итоги…) данных по полю "Клиент" с вычислением итоговой суммы к оплате и продолжительности эксплуатации для каждого клиента за все его заказы и общий итог по всем данным.

(3 балла)

 

Вариант 10.

 

1) Создать следующий документ:

Ведомость оплаты за эксплуатацию оборудования

Тариф оплаты за 1 час: 0,85р. 

 

Примечания:

а) Ставка налога зависит от суммы к оплате:

                    - если сумма < 2р.: налог = 0.05 * Сумма;

                    - от 2р. до 5р.: налог = 0.15 * Сумма;

                    - если сумма > 5р.: налог = 0.25 * Сумма.

б) Тариф оплаты может изменяться.

(5 баллов)

 

2) Скопировать созданную таблицу с 1-го на 2-й лист. Установив на 1-м листе «Автофильтр», отобразить данные за текущий месяц с прибылью больше 5р.

(2 балла)

3) На 2-м листе выполнить группировку (Сортировка… + Итоги…) данных по полю "Дата" с вычислением итоговой прибыли и продолжительности эксплуатации за каждый день и общий итог по всем данным.

(3 балла)

 

Вариант 11.

 

1) Создать следующий документ:

Учет выполненных работ с повременной оплатой труда.

Налоговая ставка: 15% от суммы.

Тариф оплаты за 1 час: разгрузка – 2,10р.; загрузка – 3,20р.; другие виды работ – 1,90р.

 

Примечание: Налоговая ставка и тарифы оплаты могут изменяться.

(5 баллов)

 

2) Скопировать созданную таблицу с 1-го на 2-й лист. Установив на 1-м листе «Автофильтр», отобразить данные за текущий месяц с суммой к выдаче больше 9р.

(2 балла)

3) На 2-м листе выполнить группировку (Сортировка… + Итоги…) данных по полю "Сотрудник" с вычислением итоговой суммы к выдаче и продолжительности работ для каждого сотрудника и общий итог по всем данным.

(3 балла)

 

Вариант 12.

 

1) Создать следующий документ:

Учет выполненных работ с повременной оплатой труда.

Тариф оплаты за 1 час: разгрузка – 2,10р.; загрузка – 3,20р.; другие виды работ – 1,90р.

 

Примечание: Тарифы оплаты могут изменяться.

(5 баллов)

 

2) Скопировать созданную таблицу с 1-го на 2-й лист. Установив на 1-м листе «Автофильтр», отобразить данные за заданный период, с суммой к оплате меньше 6р.

(2 балла)

3) На 2-м листе выполнить группировку (Сортировка… + Итоги…) данных по полю "Вид работы" с вычислением итоговой суммы к оплате и продолжительности работ для каждого вида работ и общий итог по всем данным.

(3 балла)