Материал: Информационные системы в экономике. лабораторный практикум (MS Excel 2010). Лубянская Э.Б., Лукаш Е.Н
Чтобы консолидация обновлялась автоматически при изменении исходных данных, установите флажок Создавать связи с
исходными данными.
Чтобы получить возможность обновления консолидации вручную путем изменения включаемых в нее ячеек и диапазонов,
снимите флажок Создавать связи с исходными данными.
10. В разделе использовать в качестве имен установите следующие флажки, если это необходимо:
Если верхняя строка каждого из выделенных диапазонов содержит подписи столбцов - установите флажок подписи верхней
строки.
Если левый столбец каждого из выделенных диапазонов содержит подписи строк - установите флажок значения левого
столбца.
Все названия, не совпадающие с названиями в других исходных областях, приведут к появлению дополнительных строк или столбцов в консолидированных данных.
Все категории, которые не нужно консолидировать, должны иметь уникальные подписи, которые встречаются только в одном диапазоне исходных данных.
11. Нажмите кнопку ОК.
111
ЛАБОРАТОРНАЯ РАБОТА № 1
1.Создать новую книгу Ведомость.xlsx и сохранить
еес указанным именем в папке Мои документы.
2.Ввести на Листе 1 таблицу следующего вида:
Расчетно-платежная ведомость
|
|
|
|
|
|
|
|
|
|
|
|
|
Начис |
|
|
|
|
|
Пен- |
|
|
Подо- |
|
|
К |
|
|
№ |
|
|
|
|
|
|
|
|
Отра- |
|
|
|
|
|
|
|
сион- |
|
|
ход- |
|
|
|
||
|
|
|
ФИО |
|
|
Оклад |
|
|
|
|
лено |
|
|
Аванс |
|
|
|
|
|
|
выда- |
|
||||
|
п/п |
|
|
|
|
|
|
ботано |
|
|
|
|
|
|
ный |
|
|
ный |
|
|
|
|||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
че |
|
||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
фонд |
|
|
налог |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||
1 |
|
|
Иванов И.И. |
6500 |
|
22 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||||
2 |
|
|
Антонов В.К. |
7000 |
|
20 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||||
3 |
|
|
Борисов Е.В. |
5000 |
|
21 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||||
4 |
|
|
Петров А.Е. |
7500 |
|
22 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||||
5 |
|
|
Лазарев А.В. |
7200 |
|
18 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||||
|
|
|
ИТОГО: |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||
|
|
|
|
2. Выполнить расчет незаполненных клеток первой |
||||||||||||||||||||||
строки следующим образом: |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|||||||||||
|
|
|
|
начисленная заработная плата |
исчисляется путем |
|||||||||||||||||||||
умножения среднедневного заработка (оклад, деленный на количество рабочих дней в месяце – 22 дня) на отработанное количество дней в месяце;
аванс составляет 40 % от оклада; отчисления в пенсионный фонд равны 1 % от начис-
ленного заработка; подоходный налог составляет 13 % от разницы между
начисленной заработной платой и отчислением в пенсионный фонд;
последняя графа равна разнице между начисленным заработком и всеми удержаниями (включая аванс).
3. Скопировать введенные формулы в оставшиеся строки (выделите мышью ячейку с формулой, подведите курсор в правый нижний угол (курсор приобретет вид черного крестика) и, держа нажатой левую кнопку мыши, переместите курсор на нужный диапазон).
112
4. Выполнить расчет итога в последних пяти столбцах, воспользовавшись кнопкой автосуммирования.
Построить круговую диаграмму, отражающую распределение доходов страховых агентов
Создание круговой диаграммы
Для этого выделите одновременно столбцы «Фамилия» и «К выдаче» (используя кнопку Ctrl), во вкладке «Вставка» выберите группу “Диаграмма” и в ней выберете вид диаграммы.
113
ЛАБОРАТОРНАЯ РАБОТА № 2
1.Создать новую книгу Комиссия.xlsx и сохранить ее с указанным именем в папке Мои документы. Ввести на Листе 1 таблицу следующего вида (при создании таблицы некоторые ячейки необходимо будет объединять с помощью команды “Ячейки” в меню «Формат», вкладка Выравнивание):
2.Сделать ширину столбца «№ п/п» равным 5.
3.Задать для всех граф шапки расположение текста по центру столбца и строки, для столбца «Комиссионное вознаграждение» - перенос по словам.
Отчет о деятельности торговых агентов
|
№ |
|
|
|
Объем оформленных сделок |
|
Комиссион- |
||||||
|
|
Фамилия |
|
июль |
|
август |
|
сен- |
|
3-ий кв- |
|
ное возна- |
|
|
п/п |
|
|
|
|
|
|
||||||
|
|
|
|
|
|
|
|
тябрь |
|
л |
|
граждение |
|
|
|
|
|
|
|
|
|
|
|
||||
|
|
|
|
|
|
|
|
|
|
|
|||
1 |
|
Иванов |
400 |
120 |
390 |
|
|
|
|
||||
2 |
|
Федоров |
250 |
200 |
270 |
|
|
|
|
||||
3 |
|
Орлов |
290 |
|
|
360 |
|
|
|
|
|||
4 |
|
Владимиров |
410 |
|
|
|
|
|
|
|
|
||
5 |
|
Егоров |
360 |
290 |
340 |
|
|
|
|
||||
Всего: |
|
|
|
|
|
|
|
|
|
|
|
||
Средний объем сделок |
|
|
|
|
|
|
|
|
|
|
|||
Максимальный объем |
|
|
|
|
|
|
|
|
|
|
|||
Минимальный объем |
|
|
|
|
|
|
|
|
|
|
|||
Количество неотчи- |
|
|
|
|
|
|
|
|
|
|
|||
тавшихся |
|
|
|
|
|
|
|
|
|
|
|||
4.Заполнить все незаполненные ячейки в столбце «Июль», используя соответствующие встроенные функции: СУММ, СРЗНАЧ, МАКС, МИН, СЧИТАТЬ ПУСТОТЫ.
5.Используя копирование формул заполнить столбцы «Август» и «Сентябрь» аналогичными расчетами.
6.Рассчитать для каждого агента объем сделок за 3-ий квартал как сумму сделок за июль, август и сентябрь.
7.Рассчитать для каждого агента «Комиссионное вознаграждение» как 15 % от объема сделок за квартал.
114
8. Если комиссионное вознаграждение превышает 120 рублей, установить в этих ячейках заливку красным и полужирным шрифт, используя возможности условного форматирования (вкладка «Главная», группа «Стили», кнопка «Ус-
ловное форматирование»).
Использование возможностей «Условного форматирования»
115