Тема: Використання засобів MS Excel для автоматизації розрахунків із руху товарів.

Мета: підготуватися до проведення та захисту практичної роботи 18-19. Познайомитись із засобами Excel по розрахунку проміжних підсумків та побудові зведених таблиць.

План

1. Засоби сортування та фільтрування даних.

2. Обчислення групових характеристик.

3. Використання зведених таблиць

Література:

Інформаційні системи в маркетингу М. І. Бублик. Львів: В-во ЛДІНТУ, 2007.-162с.

Завдання:

1. Ознайомитись із матеріалом для вивчення, оскільки він потрібен для виконання та захисту практичної роботи 18-19;

2. В письмовій формі виконати завдання:скласти опрний конспект матеріалу для вивчення

3. При захисті практичної роботи відповідати на питання для самоконтролю.

Матеріал для вивчення:

Засоби сортування та фільтрування даних

При аналізі інформації потреба в її упорядкуванні та відборі за заданими критеріями виникає частіше, ніж будь-яка інша. В Ехсеl можна виконувати таку переструктуризацію даних, якщо вони по­дані у вигляді списків. При цьому списки можуть мати як числові, так і текстові дані.

Сортування списків

При сортуванні текстові дані упорядковуються за алфавітом або у зворотному алфавітному порядку, а числові — за убуванням або зрос­танням залежно від заданого порядку сортування (упорядкування).

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

Однак Ехсеl дає змогу проводити багаторівневе (до трьох рівнів) сортування, тобто сортування за кількома ключами. Якщо список містить кілька елементів, які мають той самий ключ, то порядок розташування цих елементів у групі записів з цим ключем буде ви­падковим. Але якщо задати сортування ще й за іншим ключем, то будуть відповідним чином упорядковані елементи списку з однако­вим першим ключем і т. д. Наприклад, дані про збут можна відразу відсортувати (упорядкувати) за назвами товарів, кожну групу това­рів — за містами їх продажу, а кожну групу товарів у кожному місті — за прізвищами покупців.

Хоча виділення зони списку виконується автоматично (якщо табличний курсор знаходиться у зоні списку), користувач може по­передньо виділити частину списку, яку потрібно упорядкувати. При виборі команди Дані/Сорту­вання на екрані з'явиться діало­гове вікно, у якому можна задати ключі сортування записів списку. Якщо виділена частина списку не містить рядок із назвами колонок, то у групі кнопок Ідентифікувати поля за треба вибрати параметр для іден­тифікації поля за Позначками ко­лонок аркуша. Якщо ж перший рядок містить назви колонок, то треба встановити параметр для ідентифікації поля за Підписами (перший рядок діапазону).

За допомогою полів списків Сортувати за, Потім за та В останню чергу за можна вибрати назву ключа сортування. Тут слід обрати і порядок сортування — за зростаннямабо за убуванням.

Якщо при сортуванні в алфавітному порядку треба враховувати різ­ницю між регістром літер, то після натискання кнопки Параметри слід відкрити діалогове вікно Параметри сортування та увімкнути параметр Враховувати регістр.

За умовчання список буде упо­рядковано за рядками. Проте мож­на також задати сортування за ко­лонками. Це необхідно, наприклад, у випадку, коли перша колонка мі­стить назви рядків, а записи у ко­лонках однорідні. Для цього потрі­бно у діалоговому вікні Параметри сортування встановити параметр Сортувати/Колонки діапазону.

Користувач може задати і власний порядок сортування даних у списку. Так, назви місяців немає сенсу розташовувати у алфавітно­му порядку — краще застосовувати користувацький порядок сорту­вання, тобто подати назви місяців у їхній послідовності.

Для того, щоб задати користувацький порядок сортування, по­трібно відкрити діалогове вікно Параметри сортування. У полі списку Порядок сортування за першим ключем, яке містить список усіх визначених користувачем порядків сортування, виділе­но елемент Не здійснюється. Для того, щоб розкрити список, треба клацнути кнопкою миші на його стрілці і вибрати бажаний порядок сортування.

Щоб задати свій порядок, слід вибрати команду Сервіс/Парамет­ри та активізувати у діалоговому ві­кні вкладку Списки.

На вкладці Списки представлено всі складені раніше списки. Для то­го, щоб додати новий список, треба на цій вкладці у полі Списки ви­ділити елемент Новий список. У полі Елементи списку з'явиться курсор уведення. Елементи списку вводяться у тій послідовності, в якій вони мають бути представлені при подальших сортуваннях. Після уведення кожного елемента необхідно натискати клавішу Enter. Пі­сля закінчення введення всіх елементів списку для внесення його у перелік існуючих списків натискається кнопка Додати.

Використовуючи поле Імпорт списку з комірок, можна створити список на основі даних у клітинах. Для цього потрібно розташувати курсор уведення в цьому полі й потім мишею виділити клітини, які містять дані для нового списку.

Усі введені порядки сортування можуть використовуватись і для функції автоматичного заповнення.

Фільтрування списків

Фільтри — це інструмент відбору даних, відповідних визначе­ним умовам. При фільтрації переупорядкування даних не відбуває­ться. Результатом є лише або приховування рядків таблиці (списку), що не відповідають заданому критерію, або копіювання тих рядків, що відповідають заданому критерію, у визначений діапазон робочо­го аркуша для подальшого використання.

За допомогою автофільтра вибір окремих рядків таблиці можна здійснювати лише безпосередньо у робочому аркуші. Активізація автофільтра виконується командою Дані/Фільтр/Авто­фільтр. Для коректного виконання команди покажчик активної клі­тини (табличний курсор) повинен знаходитись у межах списку да­них. Якщо перед виконанням команди було виділено певний діапа­зон таблиці, то у першому рядку цього діапазону для кожного поля (колонки) даних буде встановлена окрема кнопка для введення кри­терію фільтрації за цим полем. Дія критерію фільтрації поширюєть­ся на всі рядки таблиці, які знаходяться нижче кнопок.

Якщо жод­ного діапазону таблиці не було виділено перед виконанням команди (але покажчик активної клітини знаходився у межах таблиці даних), то місцем розташування кнопок буде перший рядок таблиці, де звичайно знаходяться назви колонок. Після натискання на кнопку розкривається список елементів, які міс­тить відповідна колонка і в якому мо­жна вибрати необхідний елемент для визначення критерію відфільтрову­вання даних. Це приведе до вилучення з екрана тих рядків списку даних, які не відповідають уведеному критерію, і до забарвлення відповідної кнопки у синій колір. Критерії фільтрації мож­на задавати послідовно для кількох полів. Кожен додатковий критерій зв'язаний із попереднім умовним опе­ратором «І», через що кожна наступна умова завжди буде належа­ти до тих рядків даних, які будуть отримані при використанні по­передніх умов.

Критерій фільтрації, що складається з двох умов для однієї ко­лонки, можна визначити після натискання на відповідну кнопку і вибору елемента Умова. На екрані з'явиться діалогове вікно Користувацький автофільтр, у якому можна задати два критерії, об'єднуючи їх умовними операторами «І» чи «Або».В умовах відбору рядків є можливість задавати також і шаблони, ви­користовуючи знаки-замінники — «?» та «*». Знак запитання може використовуватися замість будь-якого одного символу поля, а зіро­чка — замість будь-якої кількості символів.

Для відновлення рядків таблиці можна натиснути на відповідну кнопку фільтра, розкрити список елементів і вибрати елемент Все. Це дає змогу скасувати дію критерію фільтрації за відповідною ко­лонкою. Другий спосіб — це вибір з меню Дані команди Фільтр/По­казати все. Він використовується, коли потрібно відновити відо­браження всіх рядків даних за рахунок відміни дії критеріїв фільт­рації за усіма колонками.

Для того, щоб вилучити з таблиці кнопки фільтрування, треба ще раз виконати команду Дані/Автофільтр, тобто зняти позначку з цієї команди.