Лабораторна робота № 8

Мал.14

Структура зведеної таблиці

Зведена таблиця являє собою інтерактивну форму, яка складається з наступних компонентів.

• Поля рядків і поля стовпців. Це поля, які складені з даних вихідного списку і яким у зведеній таблиці задана орієнтація відповідно рядків і стовпців. У розглянутому прикладі полем рядка є Вид, а полем шпальти - Країна. Часто зведені таблиці містять кілька полів рядків, які поділяються на зовнішні і внутрішні.

• Елемент. Поля зведеної таблиці розпадаються на підкатегорії звані елементами. Так, елементами поля Вид на малюнку 2 є Лікування, Відпочинок, Екскурсії, а елементами поля Країна - Болгарія, Греція. Елементи відображаються як підписи рядків або стовпців.

• Поле сторінки. Це також поле вихідного списку, якому в зведеній таблиці задана орієнтація сторінки. У наведеному прикладі поле сторінки є поле Проїзд. Поле сторінки також складається з елементів, причому назва поточного елемента (в даному прикладі - це назви поточної сторінки Всі) розташоване правіше назви поля.

• Поле даних. Поле зі складу вихідного списку, на основі якого формуються узагальнює дані зведеної таблиці, називається полем даних. У розглянутому прикладі Сума по полю Кількість являє собою поле даних.

• Область даних. Це частина зведеної таблиці, яка містить підсумкові дані. Значення в області даних формуються на основі даних записів вихідного списку. Осередки в області даних відображають узагальнені дані за елементами полів рядків і стовпців. Кожне значення в області даних являє зведення даних з вихідних записів або рядків. У прикладі область даних займає діапазон B5: F8.

• Розкриваються списки поля. Ці списки представлені стрілками в правій частині кожного поля. Клацнувши таку стрілку, можна вибрати елементи, які потрібно відобразити.

Створення звіту зведеної таблиці

Створити зведену таблицю в Excel можна лише одним шляхом - за допомогою модуля Майстри зведених таблиць і діаграм. Робота Майстра, як звичайно, розбивається на кілька кроків (в даному випадку їх 3). Розглянемо створення зведеної таблиці, наведеної на малюнку 2. Для побудови зведеної таблиці виконайте наступну послідовність дій:

§ Створіть таблицю на підставі Мал.13. І збережіть її у вигляді окремої Робочої книги (ТУРФІРМА)

§ Введіть дані в поле Країна, користуючись маркером заповнення. Для введення даних в поля Вид і Проїзд використовуйте копіювання осередків. Наприклад, наберіть в комірці СЗ текст «авіа», скопіюйте його в буфер обміну, виділіть при клавіші Ctrl осередку С5, С7... і вставте в них вміст буфера.

§ Заповніть числовими даними поля Кількість, Ціна і Сума. Для цього наберіть з клавіатури дані для перших двох полів, а в верхній осередок поля Сума введіть формулу = D3 * E3. Протягнете маркером заповнення цю формулу через діапазон F3: F22.

§ Заповніть нижню рядок 24 даними, отриманими за допомогою інструменту Автосумма.

§ Відсортуйте записи за допомогою отриманого списку за допомогою команди Дані → Сортування (Країна, Вид, Проїзд - за збільшенням, ідентифікувати - по підписам).

§ клацніть по будь-якій комірці списку.

§ Виберіть команду меню Дані - Зведена таблиця, після чого з'явиться перше вікно Майстра зведених таблиць і діаграм (мал.).

§ У вікні Майстра визначте джерело даних, для чого в розглянутому прикладі встановіть перемикач У списку або базі даних Microsoft Excel. Перемикач Вид створюваного звіту за замовчуванням буде встановлений в положення Зведена таблиця. Клацніть по кнопці Далі.

§ У наступному вікні Майстра вам буде запропоновано виділити діапазон комірок, що містять вихідні дані. За замовчуванням буде виділена вся область, займана таблицею. У розглянутому прикладі погодитеся з цим виділенням і клацніть по кнопці Далі.

§ Що з'явилося, третій, вікно Майстра (мал.) призначено для визначення місця розміщення зведеної таблиці. За умовчанням пропонується розмістити таблицю на новому листі. Погодьтеся з цим або встановіть перемикач Помістити таблицю в існуючий лист і визначте область розміщення на аркуші з допомогою виділення мишею.

§ Клацніть по кнопці Макет, після чого у вікні макетування (рис.) вам потрібно буде задати структуру зведеної таблиці. Макет таблиці розділений на чотири області: Сторінка, Стовпець, Рядок і Дані. Праворуч від макета розташовані кнопки полів вихідної таблиці. Вам потрібно перетягнути, мишею ці кнопки в ту чи іншу область макета. В область Стовпець і Рядок перетягуються ті кнопки, які будуть заголовками стовпців і рядків зведеної таблиці. В область Дані переміщаються кнопки тих полів, які складуть вміст зведеної таблиці. В область Сторінка можна перетягнути ті кнопки, які не були в попередні області, - за допомогою цих кнопок можна буде також керувати структурою таблиці.

§ Перетягніть в область Рядок кнопку Вид, а в область Стовпець - кнопку Країна. В область Дані помістіть кнопку Кількість, а в область Сторінка - кнопку Проїзд. Після завдання макета, показаного на малюнку клацніть по кнопці ОК.

§ Конструювання зведеної таблиці завершується клацанням по кнопці Готово у вікні Майстра, що відповідає третього кроку.

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

Макетування за допомогою панелі Зведені таблиці

Вище було описано, як побудувати макет зведеної таблиці за допомогою додаткового діалогу Майстра. Виявляється, це вікно викликати не обов'язково, оскільки можна провести макетування безпосередньо на робочому листі. Припустимо, ви не натискали кнопку Макет у третьому діалозі Майстра зведених таблиць, а відразу після зазначення місця розміщення таблиці клацнули по кнопці Готово. Після цього Excel надасть вам можливість заповнити на аркуші разметенную область таблиці (мал.). Для цього на екрані з'явиться панель інструментів Зведені таблиці. У верхній частині цієї панелі завжди размешаются інструменти, а в нижній частині, панелі будуть вказані назви полів вашого вихідного списку даних. Зведена таблиця створюється перетягуванням назв полів з панелі Зведені таблиці в потрібну область таблиці. При цьому керуються запропонованої розміткою майбутньої таблиці. Відразу після приміщення назву якого-небудь поля в поле даних на екрані будуть відображені перші дані зведеної таблиці. Додайте інші потрібні поля в області рядків, стовпців і сторінок.

Завдання параметрів зведеної таблиці

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

• У текстовому полі Ім'я можете задати ім'я зведеної таблиці (воно буде відображене тільки в даному вікні параметрів).

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

• Прапорець Автоформат рекомендується тримати встановленим, щоб Excel сам підбирав формат створюваної зведеної таблиці.

• Прапорець Включати приховані значення потрібно встановлювати, коли в початковій таблиці є приховані осередки і ви хотіли б, щоб їх значення враховувалися при підрахунку підсумків.

• Якщо ви хочете об'єднати комірки з підписами всіх зовнішніх рядків і стовпців, потрібно встановити прапорець Об'єднувати осередку заголовків.

• Щоб зберегти формати клітинок зведеної таблиці при оновленні, встановлюється прапорець Зберігати форматування.

• Прапорець Повторювати підписи на кожній сторінці друку має очевидний сенс.

• Користуючись списком Макет сторінки, можна вибрати порядок відображення полів сторінок. Варіант Вниз, потім поперек, встановлений за умовчанням, можна замінити на варіант Поперек, потім вниз.

• Якщо ви бажаєте, щоб помилки або відсутні дані в осередках відображалися певними символами, введіть ці символи в поля Для помилок відображати й Для порожніх клітинок відображати, попередньо встановивши відповідні прапорці.

• Прапорець Зберігати дані разом з таблицею встановлюється за умовчанням і означає одночасне копіювання вихідних даних разом з таблицею.

• Якщо ви встановите прапорець Розгортання дозволити, то при подвійному натисканні в області даних зведеної таблиці будуть розгортатися докладні дані.

• Встановлений прапорець Оновити при відкритті означає, що дані зведеної таблиці будуть оновлюватися при відкритті, якщо були внесення зміни у вихідний список.

• Якщо при створенні зведеної таблиці використані зовнішні дані, то встановіть необхідні параметри у групі Зовнішні дані.

Задавши параметри, закрийте діалог клацанням по кнопці ОК. Потім продовжите побудова зведеної таблиці згідно з пунктом «Створення звіту зведеної таблиці».

Управління висновком даних у зведеній таблиці

Після того, як ви створили на основі вихідного списку зведену таблицю, ви легко можете модифікувати її вигляд. У Excel 97 / 2000 передбачені наступні можливості управління видом отриманої таблиці.

§ Зміна складу елементів поля. На кнопках з назвами полів є стрілки, що відповідають спадним списками. Клацніть з якої-небудь стрілкою, праворуч від полів Країна або Вид на малюнку 2, і ви зможете в списку задати виводяться на екран елементи поля. Припустимо, ви хочете в зведеній таблиці приховати рядки Бізнес і Лікування. Тоді в списку, зніміть відповідні прапорці і клацніть по кнопці ОК. Якщо ж немає необхідності у висновку даних з якої-небудь країні, то потрібно зняти прапорець відповідної країни у списку.

§ Фільтрація даних. Вибір тієї чи іншої сторінки зведеної таблиці називається також фільтрацією даних. Дійсно, погляньте на список поля сторінки - його відкриває опція Все, тобто опція, що включає дані всіх сторінок в таблицю. Наступні опції в цьому списку дозволяють відібрати певні сторінки. Так, за допомогою списку поля Проїзд ви можете переходити між різними сторінками зведеної таблиці. На малюнку показана таблиця, в якій врахована реалізація путівок з обох видів проїзду (сторінка Усі). Якщо ви виберете із списку Проїзд опцію авіа або автобус, то отримаєте сторінку, показану на одному з малюнків 8.

§ Додавання нових полів. Макет зведеної таблиці, який ви створили за допомогою Майстра зведених таблиць і діаграм або за допомогою панелі Зведені таблиці, не є чимось незмінним і непорушним. Ви легко можете додати в таблицю нові поля, при цьому зведена таблиця буде автоматично перерахована. Викличте панель управління зведеними таблицями (меню Вид - Зведені таблиці) і перетягніть за панелі лівої кнопки миші назву додається поля в потрібну область таблиці.

§ Переміщення і видалення полів. За допомогою операції Drag and Drop ви можете легко переміщати поля в існуючій зведеній таблиці. Для цього достатньо захопити кнопку поля покажчиком миші і перетягнути її в нову область. Для видалення поля потрібно захопити кнопку цього поля мишею і витягнути кнопку за межі зведеної таблиці.

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

§ Відображення на окремих аркушах. Якщо ви зробите подвійне клацання на комірці в області даних, то деталізовані дані будуть виведені на окремий лист Робочої книги. На окремий лист можна також вивести кожну сторінку зведеної таблиці. Клацніть правою кнопкою миші по будь-якій комірці таблиці і виберіть у контекстному меню опцію Відобразити сторінки. У вікні діалогу, виділіть потрібне поле сторінки та натисніть ОК. У результаті кожна сторінка зведеної таблиці буде поміщена на окремому аркуші з назвою, що відповідає даному елементу поля сторінки (в розглянутому прикладі це будуть листи «авіа» та «автобус»).

§ Відображення кордонів області полів. В зведеній таблиці області полів рядків, стовпців і сторінок можуть бути обведені суцільною синьою лінією. Щоб ці кордони були видні, клацніть по кнопці Відобразити поля на панелі Зведені таблиці. Після цього на місці кнопки Відобразити поля з'явиться кнопка Приховати поля, дія якої буде зворотним.

§ Завдання нової функції для підбиття підсумків. При підведенні підсумків у зведеній таблиці може бути використана функція, відмінна від тієї, яка застосовувалася при побудові вихідної таблиці. Для завдання нової функції клацніть по обчислюваному полю таблиці, натисніть кнопку Параметри поля на панелі Зведені таблиці і в діалозі,. виділіть потрібну функцію. Після натискання ОК область даних зведеної таблиці буде заповнена новими підсумками.

Створення підкладки листа.

При оформленні документів Excel корисною може виявитися можливість створення підкладки, тобто графічного шару, що знаходиться під робочим листом. Виберіть аркуш, для якого потрібно створити підкладку, і увійдіть в меню Формат - Лист - Підкладка. У діалозі виберіть файл, що містить малюнок підкладки і клацніть по кнопці Вставка в результаті на робочому листі з'явиться фоновий малюнок, який буде розмножений як, щоб займати всю поверхню листа.

Щоб видалити малюнок підкладки листа, відкрийте лист з підкладкою. Потім в меню Формат - Лист виберіть команду Видалити фон.

Завдання1. Прайс -лист з фоновим малюнком.

Створення прайс -лист туристичної фірми. У якості вихідних s даних ви можете взяти таблицю, показану на рісунке1. Ваші дії по кроках.

• Продумайте компоновку листа. У нашому прикладі фоновий малюнок буде розташовуватися в лівій частині, а сама таблиця - у правій. Для цього розширте стовпець А, простягнувши вправо кордон заголовка даного стовпця.

• Введіть назву таблиці в об'єднану комірку А1. Для об'єднання виділіть діапазон А1: Е1 і клацніть по кнопці Об'єднати за рядками на панелі Форматування. Наберіть текст назви та натисніть Enter.

• Розширте по висоті об'єднану комірку. Це можна зробити, просто перетягнувши вниз кордон заголовка рядка 1. Але можна задати точне значення висоти рядка, клацнувши по кнопці Висота на панелі форматування і ввівши значення висоти (в пунктах) в з'явилося віконце діалогу

• Відформатуйте назву, для чого клацніть по комірці А1 і натисніть клавіші Ctrl + I. На вкладці Шрифт задайте необхідну гарнітурутуру, накреслення і розмір шрифту.

Контрольні питання

1. Поняття зведеної таблиці?
2. Створення звіту зведеної таблиці?
3. Макетування?
4. Створення підкладки листа?

ВИКОРИСТАННЯ ЗАСОБИ EXCEL «ПОШУК РІШЕННЯ» ДЛЯ ВИРІШЕННЯ ЗАВДАННЯ РОЗМІЩЕННЯ ТУРИСТІВ В ГОТЕЛЯХ

Мета роботи: освоєння засобу «Пошук рішення» для вирішення практичних завдань.

Постановка задачі та побудова математичної моделі

1. Необходімость мінімізувати транспортні витрати з доставки туристів, що прибувають різними видами транспорту, в п'ять готелів, розташованих у різних місцях.

2. Турагенство приймає 85 туристів, що прибувають по залізниці, літаком, теплоходом і автобусом.

3. Колічество прибулих різними видами транспорту, вартість перевезення однієї людини від місця прибуття до готелю і кількість місць, заброньованих в кожному готелі, наведені в таблиці 1.

Таблиця 7. Вихідні дані

Вартість проїзду 1 туриста

N№ п/п

Откуда

Прибыло

Гостин.1

ГГостин.2

Гостин.3

Гостин.4

Гостин.5

Ж.д

Аэрпорт

Мор.вокзал

Автовокзал

Заброневано

1.1.Обозначім: i - номер пункту прибуття, j - номер готелю, Sij - вартість проїзду одного туриста з пункту i в готель j, pj - число туристів прибулих в пункт j, Xi j - кількість туристів, що перевозяться з пункту i в готель j. Xi j - керовані параметри задачі оптимізації. Тоді транспортні витрати, що представляють в даній задачі функцію мети, визначаться як:

Q =  Sij Xi j

або

Q = 10X11 + 0X12 + 20X13 + 11X14 + 15X15 +

12X21 + 7X22 + 9X23 + 20X24 + 25X25 +

0X31 +14 X32 +16 X33 + 18X34 + 5X35 +

5X41 + 15X42 +15 X43 + 10X44 + 7X45

Функцію Q необхідно мінімізувати:

Q  min

1.2.Прі рішенні необхідно врахувати обмеження завдання:

- Число туристів, перевезених з кожного пункту прибуття, повинна дорівнювати числу прибулих в цей пункт туристів;

 Xj =  pj

- Число туристів, які приїхали в кожну готель, повинна дорівнювати кількості заброньованих місць в цьому готелі bj

 X j = bj

- Змінні Xi j ненегативні.

1.Порядок виконання розрахунків

Розмістіть на робочому листі таблицю вихідних даних з числом прибулих, вартістю проїзду і кількість заброньованих місць.

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

За допомогою автозаповнення внесіть нульові початкові значення керованих змінних.

За допомогою автосуммирования підрахуйте суми по стовпцях - кількість туристів, які приїхали в кожну готель, і по рядках - кількість туристів приїхали з кожного пункту прибуття.

Виберете вільну комірку, в якій буде обчислюватися функція якості. Для обчислення транспортних витрат використовуйте функцію = СУММПРОИЗВ (діапазон1, діапазон2,...) Ця функція перемножує відповідні елементи декількох діапазонів, підсумовує твори і повертає суму творів. Діапазони повинні мати однакові розмірності.

Для розглянутої задачі - діапазон1 - діапазон, в якому знаходяться вартості проїзду одного туриста з пункту прибуття в готель, діапазон2 - діапазон, в якому знаходяться кількості перевезених туристів. Введіть формулу для обчислення функції якості.

Виконайте команду Сервіс - Пошук рішення, задайте цільову комірку, мета рішення - мінімізувати значення, діапазон змінюваних осередків (номери осередків можуть відрізнятися від вказаних на малюнку).

Малюнок 15. Діалог " Пошук рішення "

Задайте обмеження:

- Керовані параметри повинні бути цілими;

- Число туристів, перевезених з кожного пункту прибуття, повинна дорівнювати числу прибулих в цей пункт туристів;

- Число туристів, які приїхали в кожну готель, повинна дорівнювати числу місць, заброньованих в цьому готелі.

У діалозі Пошуку рішення «Параметри» встановіть Лінійна модель і невід'ємні значення.

Виконайте Пошук рішення. Збережіть сценарій вирішення. Проаналізуйте результати.

Дата добавления: 2014-12-08