Практическая работа №5 «Табличный процессор MS Excel. Анализ данных с помощью сводных таблиц» Данные о задолженности по счетам. Индивидуальные_задания_к_практической_работе_№_5 (1). Табличный процессор ms excel. Анализ данных с помощью сводных таблиц
 Скачать 372.53 Kb.
|
|
Лабораторная работа №4. Тема: «Табличный процессор MS Excel. Анализ данных с помощью сводных таблиц» 1. Заполните таблицу согласно варианту. 2. Рассчитайте ячейки где содержатся звездочки (*). 3. Создайте сводные таблицы согласно вариантам задач. 4. Для третьей сводной таблицы постройте объемную диаграмму. 5. Выполните фильтрацию данных в начальной таблице с помощью автофильтра. 6. Сохраните файл на диске, дав ему имя (фамилия студента). Вариант № 1 Сведения о поставке продукции Дата Количество Товар Страна поставщик Цена за единицу Сумма Январь 30 000 Пуговицы Франция 2 * Январь 240 000 Застежки Италия 5 * Февраль 121 000 Пуговицы Франция 1,7 * Январь 98 000 Пуговицы Франция 1,7 * Март 345 000 Застежки Италия 5 * Февраль 215 000 Пуговицы Канада 3 * Март 480 000 Застежки Франция 6,8 * Январь 212 000 Пуговицы Италия 3,6 * Апрель 420 000 Пуговицы Канада 4,5 * Май 540 000 Пуговицы Франция 1,7 * Май 311 000 Пуговицы Италия 3,6 * Июнь 120 000 Застежки Канада 7 * Май 98 000 Пуговицы Франция 1,7 * Май 300 000 Пуговицы Италия 3,6 * Июнь 120 000 Застежки Италия 5 * Вместе: * Используя мастер сводных таблиц создайте сводные таблицы: a) Суммы поставок товаров по странам поставщицам и товарам в разрезе дат. b) Поставок товаров с определением минимальной цены и среднего количества. c) Максимального количества продукции по товарам и странам в разрезе наименований товаров. Вариант № 2 Данные о прибыли за год Месяц Отдел Доход Затраты Рентабельность (гр.3/гр.4) Прибыль (гр.3 - гр.4) Январь обувь детская 127 735 45 495 * * Февраль обувь детская 127 246 47 710 * * Март обувь детская 127 289 48 402 * * Апрель обувь детская 127 169 47 217 * * Май обувь детская 131 330 49 082 * * Июнь обувь детская 130 996 49 862 * * Июль обувь детская 131 054 51 872 * * Август обувь детская 135 284 61 427 * * Сентябрь обувь детская 138 903 62 342 * * Октябрь обувь детская 136 368 62 353 * * Ноябрь обувь детская 135 199 60 571 * * Декабрь обувь детская 135 144 59 848 * * Январь одежда женская 400 000 256 354 * * Февраль одежда женская 558 654 425 862 * * Вместе * Используя мастер сводных таблиц создайте сводные таблицы: a) Общего дохода и общих затрат по отделам в разрезе месяцев. b) Средней прибыли и средней рентабельности по месяцам в разрезе отделов. c) Минимальных затрат и доходов по отделам в разрезе месяцев. Вариант № 3 Сведения об объемах продаж Месяц Объем продаж Заказчик Наименование товара Процент скидки Сумма скидки Июнь 2011 $47 926 Амстор Апельсины 12 * Июль 2011 $45 875 БУМ Лимоны 10 * Август 2011 $28 800 БУМ Мандарины 15 * Сентябрь 2011 $60 000 Метро Грейпфрут 8 * Октябрь 2011 $58 051 Сокол Бананы 9 * Ноябрь 2011 $53 438 Атлант Ананасы 11 * Март 2012 $72 938 Метро Апельсины 8 * Апрель 2012 $44 000 Сокол Лимоны 9 * Май 2012 $85 900 Атлант Мандарины 11 * Июнь 2012 $82 253 Амстор Грейпфрут 12 * Декабрь 2012 $84 000 Амстор Апельсины 10 * Январь 2013 $62 900 БУМ Лимоны 15 * Злой 2013 $76 500 БУМ Мандарины 8 * Март 2013 $67 100 Метро Грейпфрут 9 * Апрель 2013 $59 700 Сокол Бананы 11 * Май 2013 $82 041 Атлант Ананасы 12 * Июнь 2013 $61 300 Амстор Папайя 10 * Июль 2013 $80 700 БУМ Авокадо 15 * Вместе: * Используя мастер сводных таблиц создайте сводные таблицы: a) Суммы процента скидки и среднего процента скидки по заказчикам в разрезе месяцев. b) Общей суммы скидки и минимальной суммы скидки по наименованием товаров в разрезе заказчиков. c) Максимального объема продаж и максимальной суммы скидки по наименованиям товаров в разрезе заказчиков. Вариант № 4 Объем продаж по представителям Дата Представитель Объем продажи % вознаграждени я Сумма вознагражден ия 9.12.12 Петерс $9 237 10 * 14.10.12 Вильямс $949 15 * 5.11.12 Петерс $6 829 10 * 10.07.12 Джонсон $9 946 12 * 18.02.12 Симпсон $5 777 8 * 5.09.12 Вильямс $2 299 15 * 19.11.12 Девис $6 530 14 * 23.02.12 Джонсон $7 437 12 * 16.02.12 Симпсон $4 428 8 * 15.02.12 Едвардс $9 164 9 * 3.10.12 Едвардс $9 858 9 * 15.09.12 Девис $7 488 14 * 26.11.12 Томпсон $4 128 13 * 18.10.12 Петерс $9 151 10 * Вместе: * Используя мастер сводных таблиц создайте сводные таблицы: a) Общей суммы вознаграждения и общего объема продаж по представителям в разрезе дат. b) Минимального и максимального объема продаж продукции по датам в разрезе представителей. c) Минимального объема продукции и минимального процента вознаграждения по представителям в разрезе дат. Вариант № 5 Данные о вознаграждениях по торговым агентам в январе 20012 г. Дата Торговый агент Наименова ние продукции Объем продажи % вознагражден ия Сумма вознагражден ия 01.01.2012 Иванов Ноутбук $741,41 10 * 01.01.2012 Петров ПК $875,52 10 * 01.01.2012 Сидоров ПК $1 004,58 5 * 02.01.2012 Иванов ПК $704,47 20 * 02.01.2012 Петров Ноутбук $560,14 15 * 02.01.2012 Сидоров ПК $725,67 10 * 03.01.2012 Иванов Ноутбук $774,50 20 * 03.01.2012 Петров Ноутбук $486,98 10 * 03.01.2012 Сидоров ПК $477,58 5 * 04.01.2012 Сидоров ПК $746,15 5 * 05.01.2012 Иванов Ноутбук $707,03 5 * 05.01.2012 Петров Ноутбук $501,68 10 * Вместе * Используя мастер сводных таблиц создайте сводные таблицы: a) Среднего, максимального и минимального объема продаж по наименованиям продукции в разрезе торговых агентов. b) Общей суммы вознаграждения и общего объема продаж по торговым агентам и товарам в разрезе дат. c) Минимального объема продаж и % вознаграждения по торговым агентам в разрезе наименований продукции. Вариант № 6 Данные о продажах товаров за год Месяц Подраздел Агент Товарная линия Объем продаж % торговой наценки Сумма торговой наценки Апрель NE Алиев Ноутбук $6 664 15 * Январь SE Новиков ПК $26 212 10 * Август SW Молотов ПК $17 011 10 * Март NE Алиев ПК $6 745 10 * Март SE Новиков Ноутбук $12 552 15 * Август SW Молотов ПК $12 304 10 * Июнь NE Алиев Ноутбук $18 880 15 * Октябрь SE Новиков Ноутбук $6 938 15 * Июль SW Молотов ПК $19 261 10 * Октябрь NE Алиев Ноутбук $12 311 15 * Ноябрь SE Новиков Ноутбук $20 055 15 * Июнь SW Молотов ПК $21 638 10 * Март NE Алиев ПК $6 724 10 * Сентябрь SE Новиков Ноутбук $6 581 15 * Июнь SW Молотов ПК $15 384 10 * Всего: * Используя мастер сводных таблиц создайте сводные таблицы: a) Максимального, минимального и общего объемов продаж по агентам в разрезе месяцев. b) Минимального % торговой наценки и суммы торговой наценки по товарным линиям в разрезе подразделений. c) Максимального объема продаж и суммы торговой наценки по товарным линиям в разрезе агентов. Вариант № 7 Сведения о реализации продукции по регионам Регион Вид продукции Стоимость Фирма производи тель % наценки Выручка с учетом наценки Донецк Компьютеры $33721,53 Acer 15 * Киев Компьютеры $27214,79 Asus 12 * Киев Ноутбуки $33208,98 LG 10 * Львов Компьютеры $45768,62 Philips 10 * Донецк Компьютеры $24280,12 HP 15 * Киев Ноутбуки $38538,92 Acer 20 * Киев Ноутбуки $37246,16 Asus 14 * Донецк Компьютеры $79998,88 LG 12 * Львов Ноутбуки $66185,68 HP 10 * Донецк Компьютеры $67751,96 Acer 12 * Донецк Компьютеры $77383,85 Asus 14 * Донецк Ноутбуки $30852,08 LG 8 * Киев Ноутбуки $2426,69 Samsung 5 * Киев Ноутбуки $47201,92 Acer 12 * Львов Ноутбуки $12249,30 Asus 15 * Вместе: * Используя мастер сводных таблиц создайте сводные таблицы: a) Максимальной стоимости и выручки с учетом наценки по видам продукции в разрезе регионов. b) Максимального, минимального и среднего % наценки по видам продукции в разрезе фирм производителей. c) Средней стоимости и % наценки по фирмам производителям в разрезе регионов. Вариант № 8 Данные о задолженности по счетам Наименование счета Номер счета Сумма к уплате Срок платежа Уплачено Сумма задолженности Brimson Furniture 10-0009 $ 2 144,55 19.01.04 $ 2000,00 * Brimson Furniture 10-0009 $ 1 847,25 01.02.04 $ 1600,00 * Chimera Illusions 02-0200 $ 3 005,14 14.01.04 $ 2500,00 * Door Stoppers Ltd. 01-0045 $ 78,85 16.01.04 $ 50,00 * Door Stoppers Ltd. 01-0045 $ 101,01 26.01.04 $ 60,00 * Emily's Sports 08-2255 $ 1 584,20 12.01.04 $ 1100,00 * Katy's Paper 12-1212 $ 234,69 20.01.04 $ 100,00 * Lone Wolf 07-4441 $ 2 567,12 29.01.04 $ 1800,00 * Refco Office 14-5741 $ 854,50 21.04.04 $ 600,00 * Renaud & Son 07-0025 $ 1 125,75 09.04.04 $ 950,00 * Reston Solicitor 07-4441 $ 2 144,55 30.03.04 $ 1900,00 * Rooter Office 07-4441 $ 78,85 15.02.04 $ 50,00 * Simpson's Ltd. 16-6658 $ 4 347,21 08.04.04 $ 3200,00 * Вместе: * Используя мастер сводных таблиц создайте сводные таблицы: a) Общих сумм уплаченных счетов и задолженности по наименованиям счетов в разрезе номеров счетов. b) Общей суммы уплаты и средней суммы задолженности по номеру счета в разрезе сроков платежей. c) Максимальной суммы к уплате и максимальной сумме задолженности по номерам счетов в разрезе наименований счетов. Вариант № 9 Данные о реализации продукции Дата Торговый агент Наименовани е продукции Объем продажи % вознагражде ния Сумма вознагражден ия 01.01.2012 Иванов Ноутбук $741,41 10 * 01.01.2012 Петров ПК $875,52 5 * 03.01.2012 Иванов Ноутбук $774,50 20 * 03.01.2012 Петров Ноутбук $486,98 10 * 04.01.2012 Петров Ноутбук $1 015,69 2 * 05.01.2012 Сидоров ПК $742,16 7 * 06.01.2012 Иванов ПК $984,99 8 * 06.01.2012 Петров Ноутбук $869,89 5 * 07.01.2012 Сидоров ПК $1 038,79 10 * 08.01.2012 Сидоров Ноутбук $1 056,47 15 * 09.01.2012 Иванов ПК $839,73 5 * 10.01.2012 Иванов ПК $714,13 5 * 11.01.2012 Иванов ПК $677,53 5 * 12.01.2012 Иванов Ноутбук $839,02 10 * Вместе: * Используя мастер сводных таблиц создайте сводные таблицы: a) Общего объема продажи и средней суммы вознаграждения по торговым агентам в разрезе дат. b) Минимального % вознаграждения и минимальной суммы вознаграждения по торговым агентам в разрезе наименований продукции. c) Среднего и максимального объема продукции по наименованиям продукции в разрезе дат. Вариант № 10 Данные о прибыльности фирмы Штат продавцо в Доход , млн.$ Дата Товар Объем реализации Рентабельность (гр.2/гр.5*100) 116 $34 01.02.2012 ПК $78,00 * 143 $83 02.02.2012 Ноутбук $107,76 * 137 $111 03.02.2012 ПК $994,08 * 162 $148 04.02.2012 Ноутбук $789,84 * 163 $157 05.02.2012 ПК $666,12 * 147 $71 06.02.2012 Ноутбук $83,56 * 148 $58 07.02.2012 ПК $1 259,01 * 114 $20 09.02.2012 ПК $62,77 * 146 $54 10.02.2012 Ноутбук $91,97 * 128 $88 11.02.2012 ПК $116,21 * 136 $37 12.02.2012 Ноутбук $410,97 * 128 $21 13.02.2012 ПК $607,44 * 141 $56 14.02.2012 Ноутбук $1 166,06 * Вместе: * * Используя мастер сводных таблиц создайте сводные таблицы: a) Среднего дохода и максимального объема реализации по штату и товарам в разрезе дат. b) Максимального объема и минимальной рентабельности по дате в разрезе товаров. c) Общего объема реализации и максимального дохода по товарам и датам в разрезе штата продукции. Вариант № 11 Данные о прибыли за год Месяц Отдел Товары Доход Затраты Рента- бельность (гр.4/гр.5) Прибыль (гр.4 - гр.5) Январь Бытовая техника Стиральная машина 127 735 45 495 * * Январь Спортивн ые товары Мяч 127 246 47 710 * * Март Бытовая техника Утюг 127 289 48 402 * * Март Спортивн ые товары Мяч 127 169 47 217 * * Март Бытовая техника Стиральная машина 131 330 49 082 * * Июнь Бытовая техника Стиральная машина 130 996 49 862 * * Июнь Спортивн ые товары Кеды 131 054 51 872 * * Август Бытовая техника Утюг 135 284 61 427 * * Август Спортивн ые товары Кеды 138 903 62 342 * * Октябрь Спортивн ые товары Кеды 136 368 62 353 * * Октябрь Бытовая техника Фен 135 199 60 571 * * Декабрь Бытовая техника Фен 135 144 59 848 * * Декабрь Спортивн ые товары Мяч 400 000 256 354 * * Всего: * Используя мастер сводных таблиц создайте сводные таблицы: a) Максимальных затрат и минимального дохода по отделам в разрезе месяцев. b) Минимальной прибыли и средних затрат по товарам в разрезе отделов. c) Максимальной рентабельности при минимальном доходе по товарам в разрезе месяцев. Вариант № 12 Сведения реализации товаров Месяц Объем продаж Заказчик Наименова ние товара Страна поставщик Процент скидки Сумма скидки Июнь 2015 $47 926 Амстор Апельсины Испания 12 * Июль 2015 $45 875 Обжора Лимоны Турция 10 * Август 2015 $28 800 БУМ Мандарины Греция 15 * Сентябрь 2015 $60 000 Метро Грейпфрут Кипр 8 * Октябрь 2015 $58 051 Сокол Бананы Абхазия 9 * Ноябрь 2015 $53 438 Атлант Ананасы Испания 11 * Декабрь 2015 $52 920 Амстор Мандарины Греция 12 * Январь 2016 $66 247 Обжора Авокадо Турция 10 * Февраль 2016 $55 453 БУМ Апельсины Турция 15 * Март 2016 $72 938 Метро Апельсины Греция 8 * Апрель 2016 $44 000 Сокол Лимоны Испания 9 * Май 2016 $85 900 Атлант Мандарины Израиль 11 * Июнь 2016 $82 253 Амстор Грейпфрут Израиль 12 * Вместе: * Используя мастер сводных таблиц создайте сводные таблицы: a) Максимального объема продажи при минимальному % скидки по месяцам и наименованиям в разрезе заказчиков. b) Общей суммы скидки при минимальном объеме продаж по наименованиям и заказчикам в разрезе стран поставщиков. c) Среднего процента скидки по наименованиям и странам в разрезе месяцев. Вариант № 13 Сведения реализации продукции по регионам Регион Вид продукции Стоимость Фи рм а прои звод и те ль % наценки Выручка с учетом наценки Западная Украина Компьютеры $33 721,53 Acer 15 * Западная Украина Компьютеры $27 214,79 Asus 12 * Восточная Украина Компьютеры $45 768,62 Philips 10 * Восточная Украина Компьютеры $24 280,12 HP 15 * Крым Компьютеры $79 998,88 LG 12 * Крым Ноутбуки $66 185,68 HP 10 * Центральная Украина Компьютеры $67 751,96 Acer 12 * Центральная Украина Ноутбуки $24 926,69 Samsung 5 * Юг страны Компьютеры $52 803,43 Philips 7 * Юг страны Ноутбуки $47 201,92 Acer 12 * Россия Компьютеры $55 256,00 Acer 12 * Россия Компьютеры $45 879,00 Samsung 15 * Север Ноутбуки $12 249,30 Asus 15 * Вместе: * Используя мастер сводных таблиц создайте сводные таблицы: a) Максимальной стоимости при среднему % наценки по регионам и фирмам производителях в разрезе видов продукции. b) Максимальной выручки при максимальной стоимости по видам продукции в разрезе регионов. c) Минимальному проценту наценки по регионам в разрезе фирм производителей. Вариант № 14 Данные о продажах за год Месяц Подраздел Агент Товарна я линия Объем продажи % торговой наценки Сумма торговой наценки Январь NE Алиев Ноутбук $6 664 10 * Январь NE Алиев ПК $26 212 15 * Февраль NE Алиев ПК $6 938 15 * Февраль NE Алиев Ноутбук $19 261 16 * Февраль SE Новиков Ноутбук $8 902 12 * Март NE Алиев ПК $6 201 5 * Март SE Новиков Ноутбук $6 724 16 * Апрель SE Новиков Ноутбук $3 136 10 * Май NE Алиев ПК $6 581 15 * Май SE Новиков ПК $7 555 14 * Май SE Новиков Ноутбук $23 846 20 * Июнь SE Новиков Ноутбук $17 011 14 * Июнь NE Алиев ПК $6 745 18 * Сентябрь SE Новиков ПК $13 608 12 * Сентябрь NE Алиев ПК $19 906 15 * Октябрь NE Алиев Ноутбук $3 858 12 * Ноябрь SE Новиков ПК $21 674 15 * Ноябрь SE Новиков Ноутбук $26 093 12 * Декабрь SE Новиков ПК $23 846 15 * Декабрь NE Алиев ПК $26 093 17 * Вместе: * Используя мастер сводных таблиц создайте сводные таблицы: a) Общей суммы торговой наценки при среднем объеме продаж по месяцам и подразделам в разрезе агентов. b) Максимального процента торговой наценки и суммы торговой наценки по товарным линиям и агентам в разрезе подразделов. c) Максимальной суммы торговой наценки по подразделам в разрезе товарных линий. Вариант № 15 Сведения объемов реализации Штат продавцов Доход Подраздел Агент Объем реализации Затраты Прибыль гр.2-гр.6 116 127 169 NE Алиев 127 735 45 495 143 131 330 NE Алиев 127 246 47 710 137 130 996 NE Алиев 127 289 48 402 162 131 054 NE Алиев 127 169 47 217 163 135 284 SE Новиков 131 330 49 082 147 138 903 NE Алиев 130 996 49 862 148 136 368 SE Новиков 131 054 51 872 114 135 199 SE Новиков 135 284 61 427 146 135 144 NE Алиев 138 903 62 315 128 400 000 SE Новиков 136 368 63 452 Вместе * * * Используя мастер сводных таблиц создайте сводные таблицы: a) Минимальных затрат при максимальной прибыли по подразделам и агентам в разрезе штата продавцов. b) Среднего объема реализации по штату продукции в разрезе подразделов. c) Среднего дохода при максимальном объеме реализации по агентам. |