Практическая работа 8. Сводные таблицы порядок построения сводной таблицы
 Скачать 440.45 Kb.
|
|
Замечание. Формула для вычисляемого элемента перекрывается формулой для вычисляемого поля, если ячейка в области данных находится на пересечении вычисляемого элемента и вычисляемого поля. Формулы для вычисляемых полей и элементов можно вывести на рабочий лист командой Ф ОРМУЛЫ – В ЫВЕСТИ ФОРМУЛЫ Они будут представлены на рабочем листе в виде, представленном на рис. 32. Вычисляемое поле Порядок решения Поле Формула 1 Налог =Стоимость *0,2/1,2 2 Итого без налога =Стоимость -Налог Рис. 32. Формулы для вычисляемых полей 24 СВОДНЫЕ ДИАГРАММЫ Построение диаграмм по сводным таблицам в Excel-97 выполняется обычным образом, используя Мастер диаграмм. Перед построением диаграммы можно скрыть лишние элементы сводной таблицы или скопировать значения в свобод- ную область. Например, на рис. 33 показана диаграмма, построенная по сводной таблице, представленной на рис. 20. Структура поставок 0 50 100 150 200 250 300 350 400 450 Евразия+ Мангнолия Скиф Лимоны Апельсины Мандарины Бананы Яблоки Рис. 33. Диаграмма для таблицы рис. 20 в Excel-97 В Excel-2000 появился новый вариант отчета – отчет в виде сводной диаграм- мы. Если при построении сводной таблицы выбрать вариант «Сводная диаграмма (со сводной таблицей)», то на новом листе с именем «Диаграмма» появится свод- ная диаграмма, а соответствующая сводная таблица появится в области, указан- ной пользователем. При этом поля строк таблицы становятся полями категорий в диаграмме, а поля столбцов – полями рядов в диаграмме (рис. 34). Поля страниц являются удобным средством выделения подмножества данных и также могут использоваться в сводных диаграммах. Элементы легенды и оси категорий будут представлены списками, позволяю- щими скрывать и показывать элементы, как и в сводной таблице. Поля можно ме- нять местами, т.е. изменять структуру сводной таблицы непосредственно на диа- грамме. Эти изменения произойдут и в соответствующей сводной таблице. Соот- ветственно, при изменении отчета сводной таблицы произойдут изменения и в сводной диаграмме. Диаграмму можно отобразить в обычном виде, если на панели «С ВОДНЫЕ ТАБ- ЛИЦЫ » выбрать команду С ВОДНАЯ ДИАГРАММА – С КРЫТЬ КНОПКИ ПОЛЕЙ СВОДНОЙ ДИАГРАММЫ , тогда она примет вид, как на рис. 33. 41 ЗАДАНИЯ ДЛЯ САМОСТОЯТЕЛЬНОЙ РАБОТЫ Построение сводной таблицы Задание 1 (исходные данные – табл. 1, приложение 1) 1. Определите максимальную цену товаров в каждой группе. 2. Определите минимальную цену товаров в каждой группе для каждой фирмы. 3. Составьте ежемесячный список товаров по сроку годности с указанием стоимости каждого наименования (в столбце «Годен до» не должно быть пустых значений, введите значения самостоятельно). 4. Определите количество и стоимость товаров для каждой фирмы. Пред- ставьте данные в процентном выражении от общего итога. 5. Определите стоимость каждого наименования товара для каждой фирмы, изготовленных в течение одного месяца (измените период группирования). Задание 2 (исходные данные – табл. 2, приложение 1) 1. Определите количество и стоимость товаров для каждой накладной. 2. Определите количество и список товаров каждой накладной по каждой фирме. 3. Постройте таблицу ежемесячных поступлений товаров от каждой фирмы; установите дополнительное вычисление «доля от суммы по строке». 4. Определите общую стоимость товаров по каждой накладной для каждой фирмы. Составьте список накладных для каждой фирмы с указанием номе- ра, даты и общей суммы. 5. Определите еженедельную стоимость полученных товаров от каждой фир- мы (сгруппируйте данные по дням с шагом 7). Задание 3 (исходные данные – табл. 3, приложение 1) 1. Определить, сколько средств поступило на 51 счет и сколько израсходова- но в целом за квартал и по месяцам. Установите дополнительное вычисле- ние «с нарастающим итогом» в поле «Месяц». 2. Сколько средств перечислено в Налоговую инспекцию всего за квартал и по каким налогам? 3. Когда были сделаны перечисления Логике, по каким документам и на ка- кую сумму? Когда были сделаны перечисления в Пенсионный фонд и на ка- кую сумму? 4. Определить, сколько перечислено налогов (счета 68, 69) всего за квартал, по месяцам и по каждому счету в отдельности (сгруппировать субсчета 68-1, 68-2 и т.д.). 5. Сколько средств получено от реализации товаров магазинами Альфа, Гамма, Сигма всего, и в отдельности по каждому магазину? 6. Постройте таблицу, отражающую корреспонденцию счетов, используя поля Дт, Кт и Сумма. 42 Задание 4 (исходные данные – табл. 4, приложение 1) 1. а) Составить ежедневный отчет о весе посылок для каждого направления. б) Выполнить группировку по дате с шагом два дня. 2. Составить отчет о весе и стоимости бандеролей для каждого направления (использовать поле страниц). 3. Определить вес отправленной корреспонденции для каждого направления. 4. Составить отчет о стоимости каждого вида корреспонденции для каждого направления по каждому виду доставки. 5. Определить количество заказов для каждого вида доставки. Задание 5 (исходные данные – табл. 5, приложение 1) 1. Определить суммы окладов по должностям для каждого подразделения. 2. Определить для каждого подразделения суммы каждого вида надбавок (15%, 25%, 40%). 3. Определить для каждой должности количество человек каждого года рож- дения. 4. Определить количество человек, работающих в каждом подразделении. 5. Определить суммы надбавок по должностям. 6. Определить для каждого подразделения количество человек со стажем больше 10 лет (Столбец «Стаж» в таблицу не добавлять!). Задание 6 (исходные данные – табл. 6, приложение 1) Вставьте в таблицу столбец «Семестр» и заполните его в зависимости от столбцов «Экзамен» и «Зачет» (запишите формулу). 1. Составить таблицу, отражающую количество часов каждого вида занятий в каждом семестре. Скопировать полученную таблицу и добавить в нее поле «Название». 2. Составить списки экзаменов и зачетов в каждом семестре, с указанием для каждой дисциплины общего количества часов и аудиторных часов, наличие курсовой работы или проекта. 3. Определить количество экзаменов и зачетов в каждом семестре. 4. Составить общий посеместровый список зачетов и список экзаменов. 5. Составить таблицу, которая позволит просматривать список предметов для каждого семестра, с указанием общего количества часов, лекционных, ла- бораторных, практических занятий, контрольных мероприятий (зачет, экза- мен, курсовая). 6. * Составить таблицу, отображающую общую нагрузку преподавателей по дисциплине в одном семестре, если предполагается, что в группе 20 студен- тов, зачет предполагает 0,33 часа на студента, экзамен – 0,5 часа, курсовая работа (проект) – 2 часа на студента. (Используйте вычисляемые поля). 48 Таблицы данных для заданий Таблица 1 Текущая дата 13.10.02 № Группа товаров Код товара Наименование Фирма Дата изготовления Годен до Снять с реализации Количество Цена ед. Стоимость 1 1 10 Мармелад КонФи 11.12.01 04.05.02 Снять 23 32 736 2 1 11 Монпасье КонФи 12.03.02 12.12.02 Годен 56 29 1624 3 1 12 Печенье КонФи 19.12.01 12.12.02 Годен 23 32 736 4 2 20 Макароны МАКФА 25.03.01 Нет данных 34 11 374 5 2 21 Крупа манная МАКФА 27.03.01 28.03.02 Снять 45 9 405 6 2 22 Вермишель МАКФА 28.03.01 24.10.02 Уценить 56 11 616 7 2 23 Крупа гречневая УКК 25.03.02 28.12.02 Годен 45 9 405 8 2 24 Крупа рисовая УКК 26.03.02 29.12.02 Годен 23 9 207 9 2 25 Геркулес УКК 29.03.01 29.12.02 Годен 34 8 272 10 3 30 Пельмени особые ФИРЭН 13.12.01 09.01.02 Снять 45 60 2700 11 3 31 Пельмени русские ФИРЭН 14.12.01 10.02.02 Снять 7 61 427 12 3 32 Котлеты столичные ФИРЭН 15.10.01 11.12.01 Снять 3 72 216 13 3 33 Шницель по-челябински ФИРЭН 16.12.01 Нет данных 56 74 4144 14 3 34 Биточки "Гурман" ФИРЭН 17.12.01 17.01.02 Снять 2 65 130 15 3 35 Бифштекс ФИРЭН 18.12.01 03.03.02 Снять 45 87 3915 16 3 36 Вареники карт.-гриб. ФИРЭН 19.12.01 19.01.02 Снять 76 68 5168 17 1 13 Печенье ЮУК 30.03.02 20.07.02 Снять 76 44 3344 18 1 14 Вафли ЮУК 31.03.02 01.05.02 Снять 78 35 2730 19 1 15 Шоколад ЮУК 10.06.02 10.12.02 Годен 3 90 270 20 1 16 Карамель ЮУК 10.12.01 24.10.02 Уценить 6 39 234 ПРИЛОЖЕНИЯ Приложение 1 49 Таблица 2 № п.п. Дата поступления № накладной Наименование Фирма Годен до Кол-во Цена Стои- мость 1 03.01.02 3 Яблоки Скиф 31.01.02 334 32 10688 2 09.01.02 23 Бананы Евразия+ 09.02.02 45 25 1125 3 18.01.02 2 Бананы Евразия+ 18.02.02 23 24 552 4 18.01.02 33 Апельсины Мангнолия 18.03.02 43 28 1204 5 18.01.02 45 Мандарины Евразия+ 18.02.02 98 38 3724 6 23.01.02 1 Мандарины Скиф 11.03.02 55 39 2145 7 24.01.02 2 Лимоны Евразия+ 24.03.02 44 30 1320 8 25.01.02 4 Яблоки Мангнолия 25.03.02 22 33 726 9 05.02.02 5 Апельсины Мангнолия 05.04.02 77 20 1540 10 12.02.02 34 Яблоки Скиф 12.04.02 78 25 1950 11 15.02.02 6 Мандарины Мангнолия 15.04.02 88 40 3520 12 19.02.02 7 Лимоны Скиф 19.04.02 55 31 1705 13 19.02.02 35 Апельсины Евразия+ 19.04.02 123 22 2706 14 20.02.02 11 Бананы Евразия+ 20.03.02 45 30 1350 15 22.02.02 8 Яблоки Евразия+ 22.04.02 44 38 1672 16 22.02.02 8 Мандарины Евразия+ 22.04.02 66 41 2706 17 16.03.02 47 Апельсины Мангнолия 16.05.02 67 35 2345 18 20.03.02 21 Бананы Скиф 20.04.02 33 29 957 19 20.03.02 21 Апельсины Скиф 20.05.02 45 26 1170 20 20.03.02 21 Мандарины Скиф 20.05.02 67 36 2412 21 21.03.02 22 Бананы Мангнолия 21.04.02 87 26 2262 22 06.10.02 9 Апельсины Скиф 06.12.02 77 28 2156 23 06.10.02 9 Мандарины Скиф 06.12.02 99 33 3267 24 06.10.02 122 Лимоны Мангнолия 06.12.02 33 33 1089 25 06.10.02 122 Яблоки Мангнолия 06.12.02 88 28 2464 26 06.10.02 20 Апельсины Скиф 06.12.02 99 26 2574 50 Таблица 3 № п.п. Дата Исх/вх № Содержание операции Получатель Дт Кт Сумма 1 04.01.99 744 За расчетно-кассовое обслуживание Банк 20 51 5800 2 19.01.99 1 НДС за 4 кв. 2002 года Нал. инспекция 68-5 51 70000 3 19.01.99 2 НДС за 3 кв. 2002 года Нал. инспекция 68-6 51 5200 4 24.01.99 802 За расчетно-кассовое обслуживание за янв.2002 Банк 20 51 20000 5 25.01.99 13616 Плата 3% по остаткам за январь 02 Антей 51 80 505 6 26.01.99 501 Предоплата по договору 2 от 25.12.01 Антей 51 64 729000 7 06.02.99 300 Предоплата по договору "союз" Антей 51 64 64 8 16.02.99 67 Предоплата по договору 3 от 12.01.02 Антей 51 64 1800000 9 20.02.99 3 Получено в кассу з/пл Антей 50 51 670000 10 23.02.99 802 За кассовое обслуживание Банк 20 51 38310 11 23.02.99 951410 По счету 468623 за 21.01-20.02.02 Антей 51 80 5255 12 07.03.99 5 Взнос в Федер. фонд ОМС за февраль 02 ОФМС 69-4 51 4074 13 07.03.99 3 Взнос в Фонд занятости за февраль 2002 Нал. инспекция 69-5 51 30550 14 07.03.99 4 Взнос в Челяб.территор. фонд ОМС за февраль 02 ОФМС 69-4 51 69246 15 07.03.99 7 Подоходный налог из з/пл за февраль 2002 Нал. инспекция 68-1 51 226774 16 07.03.99 6 Взнос в Пенсионный фонд за февраль 2002 ОПФ по г.Челябинску 69-1 51 590628 17 21.03.99 9 Налог на имущество за 3 кв.2002 года областн. Казначейство 68-4 51 13450 18 21.03.99 8 Налог на имущество за 3 кв.2002 года местн. Казначейство 68-4 51 13450 19 21.03.99 798 За кассовое обслуживание за март Банк 20 51 20000 20 22.03.99 133 Оплата по договору Логика 60 51 200000 21 21.02.99 100 Оплата по договору Логика 60 51 100000 22 19.01.99 50 Оплата по договору Логика 60 51 300000 23 10.01.99 56 Выручка Альфа 51 46 1000000 24 13.02.99 59 Выручка Гамма 51 46 1500000 25 20.02.99 60 Выручка Альфа 51 46 500000 26 17.03.99 77 Выручка Сигма 51 46 3000000 51 Таблица 4 Дата Направление Вид корреспонденции Вид доставки Вес Стоимость 01.01.02 Москва Посылка Авто 2 14 01.01.02 Новосибирск Заказное письмо Авиа 0,5 3,25 01.01.02 Екатеринбург Бандероль Жел. дор. 1 7 01.01.02 Москва Бандероль Авиа 0,8 5,2 01.01.02 Новосибирск Посылка Авто 3 21 02.01.02 Екатеринбург Бандероль Жел. дор. 0,6 3,9 02.01.02 Москва Посылка Авто 2 14 02.01.02 Екатеринбург Бандероль Авиа 0,8 5,2 02.01.02 Тверь Посылка Авиа 1,5 10,5 02.01.02 Москва Заказное письмо Авто 0,5 3,25 03.01.02 Москва Посылка Авиа 2 14 03.01.02 Новосибирск Бандероль Авто 0,9 5,85 03.01.02 Новосибирск Посылка Авиа 1 7 03.01.02 Екатеринбург Заказное письмо Авиа 0,5 3,25 03.01.02 Екатеринбург Посылка Авиа 3 21 03.01.02 Новосибирск Бандероль Жел. дор. 0,4 2,6 03.01.02 Иркутск Посылка Жел. дор. 1 7 04.01.02 Екатеринбург Бандероль Авто 0,6 3,9 04.01.02 Тверь Посылка Авто 1 7 04.01.02 Москва Бандероль Жел. дор. 0,7 4,55 04.01.02 Караганда Посылка Жел. дор. 2 14 04.01.02 Екатеринбург Бандероль Жел. дор. 0,9 5,85 04.01.02 Москва Посылка Авто 1 7 04.01.02 Новосибирск Бандероль Авто 0,8 5,2 52 Таблица 5 № п.п. Ф.И.О Подразделение Дата рождения Дата поступления Должность Оклад Надбавка за стаж Сумма надбавки 1 Аникеев А.А Бухгалтерия 01.02.50 01.01.70 Бухгалтер 5000 40% 2000 2 Аникеева В.Г. Бухгалтерия 12.12.51 01.01.71 Гл. бухгалтер 4000 40% 1600 3 Бергер А.Н. Бухгалтерия 30.05.78 01.01.97 Бухгалтер 2500 25% 625 4 Жуков М.И. Гараж 06.05.78 01.01.97 Экспедитор 2500 25% 625 5 Зайцев Р.А. Отдел сбыта 01.01.63 06.03.83 Диспетчер 3000 40% 1200 6 Иванов К.Д. Отдел сбыта 01.10.47 10.10.67 Диспетчер 3000 40% 1200 7 Иванова Т.Т. Бухгалтерия 30.03.75 10.10.98 Бухгалтер 2500 15% 375 8 Китаев Е.Н. Отдел сбыта 08.08.77 10.10.97 Диспетчер 2500 25% 625 9 Лев С.С. Юр.отдел 11.11.77 01.01.97 Юрист 3000 25% 750 10 Медведев М.К. Отдел сбыта 02.04.70 10.10.90 Менеджер 2500 40% 1000 11 Морозов Т.Г. Фин.отдел 06.07.72 10.10.92 Экономист 3000 40% 1200 12 Носик В.Е. Отдел сбыта 28.09.69 10.10.89 Менеджер 3000 40% 1200 13 Носкова И.И. Бухгалтерия 25.07.80 06.10.99 Бухгалтер 2000 15% 300 14 Орлова О.Н. Юр.отдел 01.07.78 06.10.97 Юрист 3000 25% 750 15 Петров А.В. Фин.отдел 26.12.78 06.10.97 Экономист 3000 25% 750 16 Романов С.С. Гараж 05.02.71 06.03.91 Диспетчер 3000 40% 1200 17 Сидоров Т.И. Отдел сбыта 08.08.74 06.03.93 Менеджер 3000 25% 750 18 Скворцов О.Л. Гараж 10.04.65 01.01.85 Водитель 3000 40% 1200 19 Шмелев Р.П. Отдел сбыта 22.01.65 01.01.85 Менеджер 3000 40% 1200 20 Шульц Р.Р. Гараж 07.05.50 01.01.70 Водитель 5000 40% 2000 53 Таблица 6 Название Семестр Экзамен Зачет Курсовой проект Курсовая работа Всего часов Лекции Лабор Раб Практ Занят 1 семестр 2 семестр 3 семестр 4 семестр Иностранный язык 1 1 170 28 28 Иностранный язык 2 2 170 28 44 Отечественная история 1 1 100 16 16 Правоведение 1 1 80 12 12 Философия 2 2 130 16 8 24 Экономика 1 1 100 16 12 28 Экономика 2 2 100 16 12 28 Культурология 1 1 60 12 12 Политология 1 1 60 12 12 Психология и педагогика 2 2 70 12 12 Линейная алгебра 1 1 150 16 16 32 Дифференциальное и интегральное исчисление 1 1 100 16 16 32 Логические исчисления 2 2 100 12 12 24 Линейное программирование 3 3 150 12 12 24 Информатика и программирование 2 2 2 100 16 20 36 Концепции современного естествознания 3 3 120 16 8 24 Теория вероятностей 3 3 100 16 16 32 54 Название Семестр Экзамен Зачет Курсовой проект Курсовая работа Всего часов Лекции Лабор Раб Практ Занят 1 семестр 2 семестр 3 семестр 4 семестр Теория систем и системный анализ 4 4 100 12 12 24 Информационные системы 3 3 3 90 16 16 32 С++ 4 4 4 90 16 20 36 Бухгалтерский учет 3 3 60 16 16 32 Бухгалтерский учет 4 4 60 16 16 32 Статистика 4 4 4 130 16 16 32 ТЭИС 3 3 80 12 12 24 Маркетинг 4 4 155 12 12 24 Основы бизнеса 3 4 120 12 12 24 Реклама 3 3 200 12 16 28 Автоматизация экономи- ческих расчётов 4 4 200 16 16 32 Русский язык и культура речи 1 1 70 12 8 20 Экономика предприятия (организации) 3 3 100 16 12 28 История российской ин- теллигенции 2 2 75 12 8 20 Комбинаторика и теория графов 3 3 135 16 16 32 Логистика 4 4 99 16 12 28 Документирование 3 3 105 16 16 32 Окончание таблицы 6 55 Приложение 2 Примеры выполненных заданий Построение сводной таблицы Задание 3, № 4 Сумма по полю Сумма Дт счета Субсчет 68 69 Общий итог Дата 69-1 69-4 69-5 янв 75200 75200 мар 253674 590628 73320 30550 948172 Общий итог 328874 590628 73320 30550 1023372 Задание 3, № 6 Сумма по полю Сумма Кт Дт 51 64 80 46 Общий итог 20 84110 84110 50 670000 670000 51 2529064 5760 6000000 8534824 60 600000 600000 68-1 226774 226774 68-4 26900 26900 68-5 70000 70000 68-6 5200 5200 69-1 590628 590628 69-4 73320 73320 69-5 30550 30550 Общий итог 2377482 2529064 5760 6000000 10912306 Задание 6, № 1 Данные Семестр Сумма по полю Лекции Сумма по полю Лабор. Раб. Сумма по полю Практ. Занят. 1 112 80 2 84 20 68 3 160 68 84 4 104 36 68 Общий итог 460 124 300 Задание 6, № 2 Экзамен (Все) Зачет 1 Сумма по полю Часов 1 семестр Название Всего часов Всего Иностранный язык 170 28 Культурология 60 12 Русский язык и культура речи 70 20 Экономика 100 28 Общий итог 88 Экзамен 1 Зачет (Все) Сумма по полю Часов 1 семестр Название Всего часов Всего Дифференциальное и интегральн 100 32 Линейная алгебра 150 32 Отечественная история 100 16 Политология 60 12 Правоведение 80 12 Общий итог 104 56 Задание 6, № 2 Экзамен 2 Зачет (Все) Данные Название Всего часов Сумма по полю Часов 2 Курсовой_проект Курсовая_работа Иностранный язык 170 28 Информатика и программировани 100 36 1 Философия 130 24 Экономика 100 28 Общий итог 116 1 Задание 6, № 4 Сумма по полю Всего часов Зачет (семестр) Название дисциплины Всего 1 Иностранный язык 170 Культурология 60 Русский язык и культура речи 70 Экономика 100 1 Кол-во значений 4 2 История российской интеллигенции 75 Логические исчисления 100 Психология и педагогика 70 2 Кол-во значений 3 3 Бухгалтерский учет 60 Документирование 105 Комбинаторика и теория графов 135 Концепции современного естествознан 120 Реклама 200 3 Кол-во значений 5 4 Логистика 99 Маркетинг 155 Теория систем и системный анализ 100 4 Кол-во значений 3 Общий итог 1619 Для экзаменов таблица выглядит аналогично. Консолидация данных Задание 9 Консолидированная сводная таблица с двумя страничными полями и преобразо- ванная таблица с дополнительным вычислением. Район (Все) Вид ресурса (Все) Стоимость Месяц Вид потребителя Январь Февраль Март Общий итог Бюджетные орг. 189997 901412 1272933 2364342 Жилой фонд 106664 240044 440044 786752 Промышленность 3762995 2124076 17781080 23668151 Сельское хоз. 2101106 911917 1725298 4738321 Торговля 247773 1029289 359600 1636662 Общий итог 6408535 5206738 21578955 33194228 Район (Все) Вид потребителя (Все) Отличие от января Месяц Вид ресурса Январь Февраль Март Газ -1560000 13435599 Электричество 358203 1734821 Общий итог -1201797 15170420 |