17174

Ms Excel. Інструменти Сценарій і Зведена таблиця

Практическая работа

Информатика, кибернетика и программирование

Практична робота №19 Тема: Ms Excel. Інструменти Сценарій і Зведена таблиця. Мета: Вміти будувати сценарії і зведені таблиці для аналізу підприємницької діяльності й прийняття рішень. Обладнання: ПЕОМ. Табличний процесор MS Excel. Хід виконання Правила ТБ Індив...

Украинкский

2013-06-29

298.5 KB

22 чел.

Практична робота №19

Тема: Ms Excel. Інструменти Сценарій і Зведена таблиця.

Мета: Вміти будувати сценарії і зведені таблиці для аналізу підприємницької діяльності й прийняття рішень.

Обладнання: ПЕОМ. Табличний процесор MS Excel.

Хід виконання

  1.  Правила ТБ
  2.  Індивідуальне завдання

Задача 1. Аналіз сценаріїв покупки

Фірма планує здійснити оптову покупку шести видів товарів і розглядає чотири можливі варіанти їх кількості. Побудувати сценарії для прийняття рішення про найкращий варіант покупки.

Задача 2. Побудова зведеної таблиці

Створити таблицю з 15 рядків, яка відображає облік проданих фірмою товарів з такими назвами стовпців: Номер операції, Назва товару, Ціна, Кількість, Вартість, Дата, Продавець, Покупець. Назви товарів, фірм-покупців, прізвища продавців, дати повинні повторюватися по три-чотири рази. Заповнити таблицю даними на свій розсуд (див. зразок рис. 4). Побудувати зведену таблицю, яка характеризує ефективність роботи кожного продавця, тобто відображає, які товари він продав, скільки і на яку суму; модифікувати зведену таблицю, відобразивши в ній дати проведення операцій продавцями. Побудувати зведену таблицю, яка характеризує уподобання клієнта-покупця, тобто відображає, які товари він купив, коли, скільки і на яку суму.

Теоретичні відомості

1. Аналіз сценаріїв оптової покупки. Сценарії (scenarious) — це набори значень параметрів і значень залежних величин, які подають на екран у зручному для аналізу і прийняття рішень вигляді, а також оформляють у вигляді звіту.

Розв'яжемо задачу 1. За зразком задачі  про товарний чек створіть таблицю про купівлю шести видів товарів з такими назвами стовпців: Назва, Ціна, Кількість, Вартість (рис. 1). Заповніть таблицю довільними даними: конкретними назвами товарів, цінами за одиницю кожного товару, кількостями кожного товару. Введіть формулу для обчислення вартості кожного товару = Ціна* Кількість і скопіюйте її в діапазон Вартість. Виберіть клітинку під цим діапазоном і натисніть на кнопки Автосума і вводу — отримаєте шукану сумарну вартість покупки. Отже, ця клітинка міститиме результат, що залежить від параметрів.

Рис.1. Сюжет одного сценарію.

Щоб проаналізувати чотири варіанти покупки для різних кількостей товарів, застосуйте інструмент Сценарії. Виконайте команди Сервіс => Сценарії — отримаєте вікно Диспетчер Сценаріїв. За його допомогою можна додавати новий сценарій під деякою назвою до множини сценаріїв, вилучати невдалий чи редагувати його, виводити на екран результати застосування сценарію, створювати звіт за всіма сценаріями, скористатися зі сценаріїв, створених на інших сторінках.

Натисніть на кнопку Додати і у новому вікні введіть назву першого сценарію, наприклад var1, зазначте діапазон клітинок, що містять параметри, які досліджуються (у нашому випадку — це клітинки зі стовпця Кількість, наприклад С2:С7), і натисніть на кнопку ОК. Отримаєте вікно зі значеннями клітинок-параметрів стартового варіанта покупки  їх значення не змінюйте, натисніть ОК. Додайте новий сценарій з назвою var2, але значення клітинок-параметрів тепер поміняйте довільним чином. Таким способом створіть усі чотири сценарії (рис.2).

Розглянемо, як використовувати створені сценарії. У вікні Диспетчер сценаріїв вибирайте по черзі назви сценаріїв і натискайте на кнопку Вивести — стежте за результатами обчислень згідно із цим сценарієм і переписуйте у свій звіт сумарні вартості покупок. Для якого сценарію сумарна вартість найбільша?

Результати застосування всіх сценаріїв можна подати у вигляді звіту. Для цього у вікні Диспетчер сценаріїв натисніть на кнопку Звіт і в новому вікні виберіть тип звіту: Структура, вкажіть клітинку-результат (клітинку зі значенням сумарної вартості покупки) і натисніть на кнопку ОК. Звіт отримаєте на окремій сторінці. Перегляньте його і поекспериментуйте з кнопками « + » і «-» ліворуч, які дають змогу згортати чи розгортати рівні звіту. Який варіант покупки вам найбільше підходить?

Рис. 2. Три сценарії оптової покупки.

2. Побудова зведених таблиць. Зведені таблиці (pivot tables) використовують для аналізу значної кількості даних у великих таблицях. Зведена таблиця містить всі або лише потрібні для аналізу дані основної таблиці, які відображені на екрані так, щоб залежності між ними було видно якнайліпше. Зведену таблицю будує програма-майстер (рис. 3). Користувач залежно від умови задачі має лише зазначити, що відображати в заголовках рядків і стовпців, які дані відображати на їх перетині, а також по якому полю з основної таблиці групувати дані.

Рис. 3. Вікно створення структури зведеної таблиці.

Щоб розв'язати задачу 2, виберіть створену таблицю і виконайте команди Дані > Зведена таблиця (Pivot Table). Виконайте чотири кроки побудови зведеної таблиці. Двічі натисніть на кнопку Далі і перейдіть до найважливішого третього кроку майстра, де створюється структура зведеної таблиці (див. рис. 3).

Поле Продавець перемістіть на макеті структури на поле з назвою Сторінка, поле Назва — на поле Рядок (Строка), поля Кількість і Вартість — на поле Дані. Переконайтеся, що в полі даних діє операція сума: на кнопці має бути напис «Сума по полю Кількість». Якщо діє інша операція (середнє, максимум, мінімум, кількість тощо), то двічі клацніть на назві поля і поміняйте операцію на суму. Перейдіть до четвертого кроку і зазначте, де розміщати зведену таблицю: на новій сторінці чи на поточній. Зазначте клітинку, яка буде верхнім лівим кутом зведеної таблиці. Після натискання на кнопку Готово отримаєте зведену таблицю (рис. 4).

Рис. 4. Зразок розв'язування задачі 2 (основна і зведена таблиці).

Щоб модифікувати зведену таблицю, знову виконайте команди Дані => Зведена таблиця і перетягніть поле Дата в поле структури з назвою Стовпець — тепер дати з основної таблиці стануть заголовками стовпців у зведеній. У зведеній таблиці поле Продавець є полем-списком. Якщо вибрати у ньому конкретне прізвище, зведена таблиця продемонструє ефективність роботи цього продавця протягом деякого періоду за критерієм кількості і сумарної вартості продажу (див. рис. 4).

Самостійно виконайте друге завдання задачі 2.

Деякі задачі аналізу (наприклад, ефективності роботи продавця тощо) можна розв'язати іншим способом — за допомогою функції СУММЕСЛИ(діапазон1; умова; діапазон2). Англійська назва функції SUMIF. Функція обчислює суму тих значень з діапазону2, для яких відповідні значення з діапазону 1 задовольняють умову. Наприклад, щоб визначити суму виторгу продавця Дацко, у деяку клітинку потрібно ввести формулу =СУММЕСЛИ(Продавець; "Дацко"; Вартість). Щоб дізнатися, скільки автомобілів купила фірма "Деол", потрібно ввести формулу =СУММЕСЛИ(Покупець; "Деол"; Кількість). Застосуйте подібним способом цю функцію у своїй практичній роботі.

Хід роботи

1. Розв'яжіть задачу 1.

2. Розв'яжіть задачу 2.

  1.  Контрольні запитання:

  1.  Як використовувати Мастер сводных таблиц в  MS Excel?
    1.  Які типи даних опрацьовують за допомогою ЕТ?
      1.  Яке значення функції СРЗНАЧ(15; 20; МИН(25; 45))?
      2.  З чого складається діаграма? Що таке пелюсткова діаграма?
      3.  Що таке багатофакторний регресійний аналіз?
      4.  Яке значення функції ЯКЩО(АБО(3<5; 4<8); 8; 12)?
      5.  Яке призначення команди Підбір параметра?
      6.  Що таке принцип дисконтування?
      7.  Яке призначення таблиць підстановки?
      8.  Що таке сценарії?
      9.  Які є види ліній тренду?

  1.  Оформлення звіту.

  1.  Захист роботи.


 

А также другие работы, которые могут Вас заинтересовать

20701. Cтенографічний захист інформації 165.67 KB
  Для запуску програми необхідно задати: 1 звуковий файл формату МРЗ; 2 впроваджуваний файл будьякого формату; 3 пароль; 4 коефіцієнт стиснення; 5 рівень скритності. На першому етапі роботи програми впроваджуваний файл стискається з заданим користувачем коефіцієнтом стиснення. Блоксхема алгоритму роботи програми Puff представлена ​​на рисунку. Відповідно до класифікації методів впровадження інформації всі розглянуті в статті програми реалізують форматні методи.
20702. Гамування 75.04 KB
  Відкрите повідомлення MYNAMEІSARTEM Зашифруемо повідомлення Ключ k=i36mod 26 MYNAMEISARTEM 1 2 3 4 5 лат. Зашифроване повідомлення Шифрування Ci=tigimod N 16 8 4 2 1 k=i36 1 2 3 4 5 21 0 1 1 1 0 7 1 0 1 1 0 16 0 0 0 1 0 20 1 0 1 1 0 15 0 1 0 1 0 16 0 0 0 1 0 14 1 0 0 1 0 11 0 0 0 0 0 15 0 1 0 1 0 15 0 1 0 1 0 8 1 0 1 1 1 9 1 1 1 0 1 17 0 0 1 0 1 11 0 1 1 1 1 Висновки: В даній лабораторній роботі було розглянуто принципи гамування створено гаму і зашифровано за допомогою неї повідомлення.
20703. Шифри заміни 14.03 KB
  Ключ k=i27mod 33; i позиція букви у вхідному алфавіті k позиція букви у вихідному алфавіті Вхідний алфавіт: а б в г ґ д е є ж з и і ї й к л м н о п р с т у ф х ц ч ш щ ь ю я Відкрите повідомлення: Морозов Зашифроване повідомлення: Єіліціи 2. Ключ 0 1 2 3 4 5 0 ж р ш в щ г 1 о у м х ф і 2 ч а п л к з 3 д ц ь ю н ґ 4 ї и я б т с 5 е є й Відкрите повідомлення: Морозов Зашифроване повідомлення: 12100110251003 Висновки: Шифри заміни почали використовувати ще до н.е але попри те вони є популярними і на даний...
20704. Шифри перестановки 19.62 KB
  Ключ Сонечко 5 4 3 1 6 2 4 С о н е ч к о 1 2 4 4 3 5 6 м е н і т р и н а д ц я т и й м и н а л о я п а с я г н я т а з а с е л о м Виписуємо у порядку зростання цифр кожен стовбець :мнйяял еампто тяаяа ндиаам іцнсз ртлгс иионе 2 Побудова шкали рознесення і по ній шкалу набору для шифрування з подвійною перестановкою Ключ: Сонечко веселе с о н е ч к о 5 4 3 1 6 2 4 В 3 М Я Т А С л О Е 7 Е Ц И П Я Е М С 21 Н Д Й Я Г С е 7 І А М О Н А л 16 т Н И Л Я З е 7 р И н А т А Маршрут запитуваннязчитування Змінюємо рядки у відповідності зростання цифр е...
20705. Стандарт шифрування даних DES 70.76 KB
  Data Encryption Standard це симетричний алгоритм шифрування даних стандарт шифрування прийнятий урядом США із 1976 до кінця 1990х з часом набув міжнародного застосування. DES дав поштовх сучасним уявленням про блочні алгоритми шифрування та криптоаналіз. Вхідні дані MYNAMEISARTEM Шифрування з використанням випадкового ключа Результат шифрування даних ТЭ1oЋ HЎ т ПqАgy Результати розшифрування L .
20706. Гамування з зворотнім зв’язком 111.8 KB
  1КІ08 Морозов Артем Вінниця 2012 Вхідні дані My Name is Artem Ключ ч7є'V B1{XKСтЌuЭ0UБlЋоJј Шифрування простою заміною Гамування Зашифроване повідомлення г ЎвжЃЫjґЎqkіп'gИ Гамування з зворотнім звязком зворотний зв'язок не залежить від відкритого і зашифрованого тексту. Вона в цьому випадку відбувається за гамою з виходу алгоритму блочного шифрування У цьому режимі алгоритм блочного шифрування використовується для організації процесу поточного зашифрування так само як і у вищеперелічених режимах гамування.
20708. Экстремумы и точки перегиба 99 KB
  Определение: Если то называется точкой строгого локального минимума. Определение: Если то называется точкой локального максимума. Определение: Если то называется точкой строгого локального максимума.
20709. Первообразная функция и неопределенный интеграл 82 KB
  Опр: Функция называется первообразной для функции на промежутке если . Если первообразная для функции на и с произвольная постоянная то функция также является первообразной для . Если первообразная для функции на и первообразная для функции на то найдется с: . Вывод: Таким образом множество всех первообразных для на представимо в виде Опр: Множество всех первообразных функции на наз.