Лабораторная работа E5 «Консолидация данных»
Консолидация данных позволяет создать таблицу-сводку по одной или нескольким категориям данных, используя один или несколько блоков исходных данных. Данные должны представлять собой единообразно организованные блоки строк или столбцов. Выполним консолидацию данных, представленных в строках 1-10 следующей таблицы.
Рис. 4.9. Пояснение лабораторной работы «Консолидация данных»
В этом примере исходные данные для консолидации собраны в одну таблицу, хотя они могут быть представлены и в нескольких таблицах (по каждой кафедре). В таком случае столбец с названием кафедры не потребуется.
Последовательность работы:
1) Выделить ячейку, которая будет соответствовать левому верхнему углу новой таблицы консолидированных данных. Пусть, например, это будет ячейка B12.
2) Вызвать команду Данные/Консолидация.
3) В окне Консолидация (Рис. 4.9 – внизу) в списке Функция укажите Сумма. В этом же окне установите флажок Использовать в качестве имен значения левого столбца (имеется в виду левый столбец в каждом выделяемом далее исходном блоке ячеек).
4) Установите курсор в строку Ссылка. Выделите первую исходную область. В нашем примере это B3: E5и нажмите (щелчок мышью) кнопку Добавить.
5) Повторите эти действия для блоков B6:E8и B9:E10, затем нажмите кнопку ОК -будет построена таблица консолидированных данных (Рис 4.9 – строки 12-15).
Лабораторная работа E6 «Сводная таблица»
|
|
|
Построение сводной таблицы выполняется с помощью событийной процедуры, называемой Мастером сводных таблиц. Для построения сводной таблицы используйте те же самые данные, которые были исходными для консолидации (строки 1-10 на рис. 4.9).
Порядок работы:
1) Установите курсор в ячейку, начиная с которой желательно поместить сводную таблицу, например, в ячейку A20. Выполните команду Данные/Сводная таблица и выберите режим Создать таблицу на основе данных, находящихся в списке или базе данных Microsoft Excel. Перейдите к следующему шагу – кнопка Далее.
2) Укажите диапазон данных. В нашем случае это A2:E10 (Рис. 4.9)
3) На третьем шаге нужно создать макет сводной таблицы. Для этого выберите вкладку Макет (Рис. 4.10) и переместите мышкой кнопку Кафедра на поле Страница, кнопку Наименование – на поле Строка, кнопку Цена – на поле Столбец, кнопку Сумма – на поле Данные (Рис. 4.10). После нажатия кнопки Далее будет создана сводная таблица, изображенная на рисунке 4.11. Из нее видно, по какой цене и на какую сумму приобретено оборудование каждого типа.
Рис. 4.10. Мастер сводных таблиц, шаг третий
|
|
|
|
|
|
|
|
|
| ||
| Кафедра
| (Все) |
|
|
|
|
|
|
| ||
|
|
|
|
|
|
|
|
|
| ||
| Сумма по полю Сумма | Цена |
|
|
|
|
|
|
| ||
| Наименование | 250 | 1900 | 2000 | 5400 | 5800 | 20000 | 22000 | Общий итог | ||
| Аудиоколонки | 500 |
|
|
|
|
|
| 500 | ||
| Компьютеры |
|
|
|
|
| 220000 | 88000 | 308000 | ||
| Принтеры |
|
|
| 16200 | 5800 |
|
| 22000 | ||
| Сканеры |
| 3800 | 2000 |
|
|
|
| 5800 | ||
| Общий итог | 500 | 3800 | 2000 | 16200 | 5800 | 220000 | 88000 | 336300 | ||
|
|
|
|
|
|
|
|
|
|
Рис. 4.11. Сводная таблица
Самостоятельные работы по Excel
Самостоятельная работа «Проходной балл»
Подготовить таблицу (Рис. 4.12): ввести данные по оценкам (от 2 до 5) с использованием функции получения случайных чисел ЦЕЛОЕ(СЛМЕЖДУ( )), к полученным оценкам (случайным числам) применить специальную вставку, чтобы заменить формулы полученными значениями. Вычислить суммарный и средний балл. В столбец «Сообщение о зачислении» занесите “Зачислен”, если сумма баллов больше проходного или “Отказать” в обратном случае. Используя условное форматирование выделите цветом все сообщения “Зачислен”.
|
|
|
| Ведомость | ||||||||||
| приема на обучение | ||||||||||
| в институт | ||||||||||
|
|
|
|
|
|
|
|
|
| ||
|
|
| Проходной балл |
|
|
|
|
| |||
|
|
|
|
|
|
|
|
|
| ||
| № абит. | Фамилия | Математика | Физика | Литература | Химия | Суммарный балл | Средний балл | Сообщение о зачислении | ||
| 1 |
|
|
|
|
|
|
|
| ||
| 2 |
|
|
|
|
|
|
|
| ||
| 3 |
|
|
|
|
|
|
|
| ||
| 4 |
|
|
|
|
|
|
|
| ||
| 5 |
|
|
|
|
|
|
|
| ||
| 6 |
|
|
|
|
|
|
|
| ||
| 7 |
|
|
|
|
|
|
|
| ||
| 8 |
|
|
|
|
|
|
|
| ||
| 9 |
|
|
|
|
|
|
|
| ||
| 10 |
|
|
|
|
|
|
|
| ||
| Средний балл за экзамен |
|
|
|
|
|
|
| |||
Рис. 4.12. Пояснение самостоятельной работы «Проходной балл»
Самостоятельная работа «Работники бюджетной сферы»
Предлагается создать электронную таблицу, позволяющую автоматизировать начисление заработной платы работникам бюджетной сферы.
Последовательность выполнения задания:
1. Заработная плата работников бюджетной сферы исчисляется на основе Единой тарифной сетки, утвержденной Правительством Российской Федерации (до введения новой системы оплаты труда – вероятно, с 01.01.2009).
| Разряды оплаты труда | Тарифные коэффициенты | Разряды оплаты труда | Тарифные коэффициенты |
| 1 | 1.0 | 10 | 3.99 |
| 2 | 1.3 | 11 | 4.51 |
| 3 | 1.69 | 12 | 5.1 |
| 4 | 1.91 | 13 | 5.76 |
| 5 | 2.16 | 14 | 6.51 |
| 6 | 2.44 | 15 | 7.36 |
| 7 | 2.76 | 16 | 8.17 |
| 8 | 3.12 | 17 | 9.08 |
| 9 | 3.53 | 18 | 10.07 |
Рис. 4.13. Единая тарифная сетка
| Ставка первого разряда | 2200 руб. |
| Минимальная заработная плата | 2000 руб. |
| Прожиточный минимум в Иркутске | 5000 руб. |
Рис. 4.14. Пояснение самостоятельной работы «Работники бюджетной сферы»
2. Сформируйте таблицу Расчет заработной платы, состоящую из столбцов, имеющих заголовки:
─ № п/п
─ Фамилия, инициалы работника
─ Должность
─ Разряд
─ Количество ставок
─ Процент доплат
─ Начисленная зарплата с районным коэффициентом
─ Количество льгот по подоходному налогу
─ Подоходный налог
─ Удержания в Пенсионный фонд
─ Заработная плата к выдаче
─ Доплаты из социальных фондов
3. Заполните составленную таблицу на основании следующих данных:
─ Фёдоров А.В., терапевт, имеет 13 разряд, работает на 1.5 ставки, установлена доплата 15% за выполнение общественных обязанностей, 1 льгота по подоходному налогу;
─ Терёхина В.М., повар, работает по 2-му разряду, имеет 2 ставки, доплат не имеет, 1 льгота по подоходному налогу;
─ Иволгина А.В., санитарка, имеет 7-й разряд, 1.25 ставки, установлена доплата в размере 15% за вредные условия труда, 1 льгота по подоходному налогу;
─ Бороев А.Н., программист, имеет 11 разряд, 1 ставка, доплата в размере 12% за работу с излучающей аппаратурой, 2 льготы по подоходному налогу.
4. В заполненной таблице рассчитайте путем задания формул:
─ начисленную зарплату с районным коэффициентом: (Тарифный коэффициент соответствующего разряда × Ставка первого разряда × Количество ставок + Величина доплат ) ×1.15;
─ подоходный налог: (Начисленная зарплата с районным коэффициентом – Минимальная зарплата × Количество льгот по подоходному налогу) ×0.13;
─ удержания в Пенсионный фонд: 1% от Начисленной зарплаты с районным коэффициентом;
─ заработную плату к выдаче: Начисленная зарплата с районным коэффициентом - Подоходный налог - Удержания в Пенсионный фонд.
5. Столбец Доплаты из социальных фондов заполните по условию:
· если Зарплата к выдаче меньше прожиточного минимума, то Доплата из социальных фондов равна: Прожиточный минимум – Зарплата к выдаче;
· если наоборот, то Доплата из социальных фондов равна нулю.
6. К ячейкам столбца Зарплата к выдаче примените условное форматирование: если Зарплата к выдаче меньше прожиточного минимума, то ячейка должна закрашиваться в красный цвет.
7. В построенной таблице подсчитайте общую сумму платежей по Подоходному налогу, общую сумму удержаний в Пенсионный фонд и общую величину Зарплаты к выдаче;
8. Отформатируйте ячейки, содержащие цифровые данные, измеряемые в денежном выражении, с точностью до 0.01 (до двух знаков после запятой);
9. По данным заполненной таблицы постройте столбиковую диаграмму, которая бы отображала фамилии сотрудников и величину заработной платы к выдаче.
Самостоятельная работа «Кинотеатр»
Предположим, вам предлагают стать директором кинотеатра, но, сколько денег вы будете зарабатывать, говорить не спешат. Тогда вы решаете провести свое маркетинговое исследование.
1. Заполните зал (13 рядов по 25 мест в каждом) случайными цифрами от 1 до 4, кроме последнего ряда, который заполните цифрами 5. Цифры 1 и 2 будут обозначать посетителей, на которых распространяются скидки (С). Цифры 3 и 4 – обычных посетителей (О), а цифры 5 – «места для поцелуев». Считается, что все места в кинотеатре заняты на каждом сеансе.
| Название фильма | Сеанс | Цена | |||||||||||
|
|
|
|
|
|
|
|
|
| С |
| О |
| Л |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Рис. 4.15. Пояснение самостоятельной работы «Кинотеатр»
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Курс $ = |
| ||
|
|
| ||
| Всего зрителей | ф4 | ||
| Из них |
| ||
| со скидкой | ф1 | ||
| обычных | ф2 | ||
| LoveSeat | ф3 | ||
|
|
| ||
| Выручка: |
| ||
| сеанс 1 | ф5 | ||
| сеанс 2 | ф6 | ||
| сеанс 3 | ф7 | ||
| сеанс 4 | ф8 | ||
| сеанс 5 | ф9 | ||
| Итого за день | ф10 | ||
| Итого за месяц | ф11 | ||
| Из них: | % | в руб. | в $ |
| амортизация | 12 | ф12 | ф18 |
| налоги | 22 | ф13 | ф19 |
| з/п сотрудникам | 16 | ф14 | ф20 |
| охранные структуры | 25 | ф15 | ф21 |
| новые фильмы | 22 | ф16 | ф22 |
| директор | 3 | ф17 | ф23 |
Рис. 4.16. Схема зала
Согласны ли Вы, Ф. И.О., стать
директором кинотеатра?
| ф24 |
2. По формулам ф1 – ф3 подсчитывается:
· количество льготников;
· количество обычных посетителей;
· количество влюбленных.
3. Формула ф4 суммирует полученные числа, чтобы получить контрольную цифру 325.
4. Формулы ф5 – ф9, исходя из известной стоимости билетов и подсчитанного количества зрителей, вычисляют выручку соответствующего сеанса. В нашей модели предполагаем, что размещение зрителей в зале на всех сеансах одинаково.
5. Формула ф10 вычисляет выручку за день, формула ф11 – за месяц (принимаем, что в месяце 30 дней).
6. Исходя из выручки в рублях за месяц и процентов расходов по статьям, формулы ф12 – ф17 вычисляют денежные расходы на соответствующие статьи затрат.
7. Формулы ф18 – ф23 показывают, сколько эти суммы будут значить в долларах при известном заранее курсе.
8. Формула ф24 определяет, что если директор в результате получает больше 1500 долларов в месяц, то он согласен быть директором. В ячейке должно появиться слово «Да» или «Нет».
9. Постройте объемную диаграмму расходов.
Самостоятельная работа «Стипендия»
Вычислить размер стипендии в зависимости от среднего балла, полученного в сессию и наличия детей у студента.
| Минимальная заработная плата | ||||||||||||||
| СТИПЕНДИЯ
 Мы поможем в написании ваших работ! | ||||||||||||||