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

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

Щоб побудувати зведену таблицю слід подати команду меню Данные – Сводная таблица. У результаті буде відчинено вікно майстра зведених таблиць. На першому кроці роботи з майстром зведених таблиць потрібно обрати джерело даних для зведених таблиць - в списке или базе данных Microsoft Excel і тип звіту, що створюється, - сводная таблица. Натиснути на кнопці Далее.

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

На третьому кроці роботи з майстром слід визначити макет зведеної таблиці та її місце розташування. За допомогою кнопки Макет можна визначити порядок групування та обробки. Щоб згрупувати дані за певним показником слід перетягти назву показника в область Строка або Столбец. Якщо елемент показника поміщено в область Строка, то в зведеній таблиці буде стільки рядків, скільки значень даного показника існує в таблиці – джерелі даних. Відповідно можна згруповані дані вивести у стовпцях. В область Строка або Столбец можна помістити декілька показників. У цьому випадку перший показник буде створювати групу, другий підгрупу в групі і т.д. Для обробки даних відповідні елементи слід перетягти у область Данные. За замовчуванням застосовується операція сумування. Для зміни операції можна виконати подвійне клацання на елементі в області Данные.

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

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

Завантажити табличний процесор Microsoft Excel і створити таблицю “Відомість надходження товарів”, починаючи з чарунки А1.

Щоб створити зведену таблицю по товарах і постачальниках (рис.1) треба:

Рисунок 1 - Зведена таблиця по товарах і покупцях

Виконати команду меню Данные – Сводная таблица.

На першому кроці роботи з майстром зведених таблиць обрати перемикачі в списке или базе данных Microsoft Excel і сводная таблица. Натиснути Далее.

На другому кроці потрібно визначити данні для побудування зведеної таблиці. Виділити усю таблицю без заголовка та підсумкового рідка незалежно від того, які показники далі будуть використовуватися для групування та обробки даних. Виділити чарунки А2:F11, натиснути на кнопці Далее.

Для визначення порядку групування та обробки даних натиснути на кнопці Макет. Перетягти з лівої частини вікна діалогу елемент “Найменування товару” в область Строка для групування по рядках, елемент “Постачальник” в область Стоблец для групування по колонках, елемент “Сума” в область Данные для обробки значень у групах (рис.2). Натиснути на кнопці Ок.

Обрати місце розташування зведеної таблиці – новый лист. Натиснути Готово.

Рисунок 2 - Визначення макету зведеної таблиці

Щоб створити зведену таблицю, в якій групування здійснюється по товарах і датах реалізації (рис. 3) треба:

Перейти на лист робочої книги, де міститься таблиця “Відомість надходження товарів”. Виконати команду меню Данные – Сводная таблица.

На першому кроці роботи з майстром зведених таблиць обрати перемикачі в списке или базу данных Microsoft Excel і сводная таблица. Натиснути на кнопці Далее.

Виділити усю таблицю без заголовка та підсумкового рідка - чарунки А2:F11, натиснути на кнопці Далее. Натиснути на кнопці Нет для створення незалежної від попередньої зведеної таблиці.

Натиснути на кнопці Макет. Перетягти з лівої частини вікна діалогу елемент “Найменування товару” в область Строка для групування по рядках, елемент “Дата поставки” в область Строка для групування по рядка всередині груп, що створюються по товарах, елемент “Сума” в область Данные для обробки значень у групах. Натиснути на кнопці Ок.

Обрати місце розташування зведеної таблиці – новый лист. Натиснути Готово.

Рисунок 3 - Зведена таблиця по товарах і датах поставок

Тема: Розрахунок вартості замовлення з використанням елементів управління

Мета: Навчитись складати кошторис використовуючи елементи управління.

Потрібно розрахувати вартість замовлення на придбання комп’ютера визначеної конфігурації. Для спрощення прикладу, у якості змінюваних параметрів замовлення будемо розглядати тільки тип процесора, оперативної пам’яті, вінчестера та монітора. При цьому також врахуємо вартість гарантії і доставки. Всі дії і розрахунки будуть виконуватися на робочому листі „Кошторис”. На таких додаткових робочих листах, як: „Процесори”, „Пам’ять”, „Вінчестери” та „Монітори” будуть розміщені відповідні прайс - листи для підбору конфігурації ПК. Для вибору комплектуючих із прайс - листів і для проведення розрахунків будуть використані відповідно елементи управління та функції табличного процесора Excel.

2. Перейменувати робочі аркуші книги, щоб назви відбивали зміст розміщених на аркушах даних. Для цього потрібно клацнути правою кнопкою миші на ярличку робочого аркуша „Лист1”. На екрані з’явиться контекстне меню. Виконати команду „Перейменувати” та ввести нову назву аркуша „Кошторис”. Натиснути клавішу „Enter”. Аналогічним чином перейменувати інші робочі аркуші, що будуть використовуватися: „Лист2” на „Процесори”, „Лист3” на „Пам’ять”, „Лист4” на „Вінчестери”, „Лист5” на „Монітори”.

3. Підготувати робочий аркуш „Кошторис”. Для цього потрібно активізувати курсором даний аркуш і ввести текст у відповідні клітинки А1 Вартість замовлення на придбання комп'ютера; А3 Дата:; С3 Курс доллара; А5 Процесор; А7 Пам'ять; А9 Вінчестер; А11 Монітор; А13 Сума; А15 Гарантія; А17 Доставка; А19 Разом

4. Виділити курсором миші діапазон А1:D2 і натиснути кнопку „Об’єднати та розмістити по центру”. Виконати команди меню „Формат/Клітинки”, закладка „Вирівнювання”. У списку „по горизонталі” та „по вертикалі” вибрати „по центру”. Далі виконати команди „Межі/ Зовнішні межі” на панелі „Форматування”.

5. Встановити ширину стовпчиків А, В, С, D відповідно 10, 17, 30, 4. Для цього необхідно встановити курсор миші на заголовок стовпчика і через контекстне меню виконати команду „Ширина стовпчика” та з клавіатури ввести необхідне число.

6. Виділити діапазон А3:А19 і виконати команди „Межі/ Зовнішні межі” на панелі „Форматування”. Аналогічно встановити зовнішні межі для діапазонів клітинок В3:В19 і С3:D19.

7. Виділити діапазон В5:В19 і виконати команди меню „Формат/Клітинки”, закладка „Число”. Вибрати формат „Фінансовий”. Відкрити поле зі списком „Значення”, встановити: „грн” і натиснути „ОК”.

8. Зробити так, щоб на бланку „Вартість замовлення на придбання комп’ютера” автоматично з’являлася поточна дата. Для цього вибрати В3 і виконати меню „Вставка/Функція” (або натиснути на кнопку ¦_х). На екрані з’явиться діалог майстра функцій. Вибрати зі списку Категорія – „Дата і час” Вибрати зі списку „Функція” – „СЕГОДНЯ()”. Натиснути двічі кнопку „ОК”. У клітинці В3 з’явиться поточна дата.

10. Підготувати прайс - листи на окремих робочіх аркушах „Процесори”, „Пам’ять”, „Вінчестери”,„Монітори”.

11. На робочому аркуші „Кошторис” встановити елемент управління „Поле зі списком”, через який можна переглядати список процесорів і вибирати потрібне найменування.

Для цього необхідно активізувати аркуш „Кошторис”. Виконати команду меню „Вид/ Панелі інструментів/Форми”. На екрані з’явиться панель з елементами управління. Натиснути кнопку „Поле зі списком” на панелі інструментів „Форми”. Встановити курсор миші на верхній лівий кут клітинки С5, натиснути кнопку миші і, не відпускаючи її, акуратно провести курсор-хрестик по лінії, що розділяє рядки 4 і 5 так, щоб хрестик був у клітинці D5, десь на дві третини ширини клітинки від лівого її краю. Відпустити кнопку миші. Елемент управління „Поле зі списком” буде встановлено (для редагування встановленого елемента управління необхідно на ньому клацнути правою, а потім лівою кнопкою миші. З’являться маркери навколо елемента управління. Тепер можна курсором миші змінити положення та розмір елемента управління).

12. Щоб призначити елементу управління „Поле зі списком” список типів процесорів необхідно клацнути правою кнопкою миші на створеному елементі управління. В контекстному меню вибрати команду „Формат об’єкта”. На екрані з’явиться діалог „Формат елемента управління”. По умовчанню в діалозі обрана закладка „Елемент управління”. Клацнути в полі „Формировать список по диапазону”. Активізувати курсором миші закладку „Процесори”. Виділити курсором діапазон А2:А6 із назвами типів процесорів. Рядок у полі „Формувати список по діапазону” буде мати вигляд: „Процесори!$A$2:$A$6”. Клацнути в полі „Связь с ячейкой”, при цьому в робочій книзі відчиниться аркуш „Кошторис”. Клацнути мишею на клітинці Е5. Межі клітинки виділяться пунктирною лінією, а в полі ведення діалогу „Форматування об’єкта” з’явиться „$E$5”. В цій клітинці на робочому аркуші „Кошторис” буде розміщено порядковий номер обраного елемента. У полі „Количество строк списка” ввести з клавіатури – 5. Натиснути „ОК”. Тепер через встановлене поле зі списком можна не тільки переглядати список процесорів, але і вибирати елементи цього списку. При натисканні кнопки списку з’явиться перелік типів процесорів. Оберіть перший.

13. Розмістити ціну процесора в клітинку В5 на основному робочому аркуші „Кошторис”. Для цього потрібно встановити курсор миші в клітинку В5 і натиснути кнопку „Вставка функції”. Вибрати „Посилання та масиви” зі списку „Категорія”. У списку „Функція” вибрати „ИНДЕКС”. Натиснути „ОК”. На екрані з’явиться діалог з виділеним рядком „массив; номер_строки; номер_столбца”. Натиснути знову кнопку „ОК”. На екрані з’явиться діалог „Аргументи функції „ИНДЕКС”. Клацнути в полі „Масив”. Активізувати курсором миші закладку „Процесори”. Виділити діапазон В2:В6 з цінами процесорів. Після цього рядок у полі „Масив” буде мати вигляд: „Процесори!В2:В6”. Клацнути в полі „Номер рядка”. При цьому активізується аркуш „Кошторис”. Клацнути на клітинці Е5. Межі клітинки будуть виділені пунктиром, а в полі введення параметрів з’явиться рядок „Е5”, натиснути кнопку „ОК”. Діалог введення параметрів зникне з екрана. В клітинці В5 з’явиться ціна обраного процесора.

14. Встановити для клітинки Е5 білий колір шрифту, тобто сховати її зміст, оскільки порядковий номер є службовою інформацією.

15. Для вибору конфігурації пам’яті необхідно встановити на робочому аркуші „Кошторис” елемент управління „Лічильник”, за допомогою якого можна переглядати список типів пам’яті. Для цього натиснути кнопку „Лічильник” на панелі інструментів „Форми”. Встановити „Лічильник” в клітинці D7. Встановлення елемента управління „Лічильник” виконується аналогічно елементу „Поле зі списком”, що описано раніше. Розмір поля елемента управління варто встановити приблизно на дві третини ширини клітинки D7.

16. Задати параметри „Лічильника”. Для цього потрібно клацнути правою кнопкою миші на елементі „Лічильник” вибрати „Формат об’єкта”. На екрані з’явиться діалог „Формат елемента управління”. Ввести значення полів із клавіатури: „Поточне значення”: 1; „Мінімальне значення”: 1; „Максимальне значення”: 5; „Крок зміни”: 1. Значення в поле „Зв’язок з клітинкою” заноситься, як описано раніше при використання елемента управління „Поле зі списком”. Для цього вибрати клітинку Е7 на основному робочому аркуші „Кошторис”.

17. Тепер необхідно розмістити ціну і тип обраної конфігурації пам’яті відповідно в клітинки В7 і С7. Для цього потрібно вибрати С7 викликати майстр функцій. Вибрати функцію „ИНДЕКС()”, вказати робочий аркуш – „Пам’ять” і діапазон клітинок відповідного списку, тобто стовпчик типів (А2:А6). Вказати клітинку, в якій буде знаходитися порядковий номер обраного в списку рядка, це клітинка Е7. Натисніть кнопку „ОК”. Аналогічно зробити для В7 тільки вибрати з аркуша „Пам’ять” стовпчик цін (В2:В6). У В7 і С7 з’явиться інформація щодо ціни і типу обраної конфігурації пам’яті. Встановити для клітинки Е7 білий колір шрифту.

18. У подальшому для розрахунку вартості замовлення на придбання ПК вінчестер будемо вибирати за допомогою „Лічильника”, а монітор - за допомогою елемента управління „Поле зі списком”. Виконайте це самостійно аналогічно тому, як описано раніше.

19. Розрахунок вартості гарантії потребує використання такого елемента управління як „Перемикач”, який застосовується в ситуації, коли необхідно зробити вибір одного з декількох взаємовиключних варіантів. Кошторис на основному аркуші передбачає два види гарантії по бажанню замовника: безкоштовна - терміном на півроку, або вартістю 10% від ціни комп’ютера - терміном на рік. Спочатку потрібно встановити перемикач для першого виду гарантії. Для цього необхідно натиснути кнопку „Перемикач” на панелі інструментів „Форми”. Перемістити курсор миші до верхнього лівого кута клітинки С15 і натиснути ліву кнопку миші.. На робочому аркуші з’явиться поле „Перемикач” в режимі редагування. Вилучити стандартний заголовок поля і ввести новий заголовок: „6 міс.”. Для встановлення параметрів елемента управління клацнути правою кнопкою миші на полі „Перемикач” і через контекстне меню виконати команду „Формат об’єкта”. На екрані з’явиться діалог „Формат елемента управління”. Ввести значення полів: „встановлений”, „зв’язок з клітинкою” - Е15. Натиснути кнопку „ОК”. Перемикач виділиться (кружок стане темним), а в клітинці Е15 з’явиться значення 1 - номер активного перемикача на робочому аркуші.

20. Встановити другий перемикач (для гарантії на рік) в клітинці С15 зліва від першого. Встановлення виконується аналогічно п.19. Вилучити стандартний заголовок перемикача і ввести новий: „1 рік” Активізація діалогу „Формат елемента управління” в даному випадку зайва. Параметри другого перемикача налаштовуються автоматично.

21. Перевірити дію перемикачів. Для цього потрібно клацнути мишею на перемикачі „1 рік”. Виділення переміститися з першого на другий перемикач. У клітинці Е15 з’явиться значення „2” - номер активного (другого) перемикача. Клацнути мишею на перемикачі „6 міс.”. Виділення переміститися на перший перемикач. У клітинці Е15 відновиться значення „1”.

22. Оскільки вартість гарантії залежить від вартості комп’ютера, необхідно обчислити спочатку вартість комп’ютера в клітинці В13 за допомогою кнопки „Автосума” як суму цін комплектуючих елементів в діапазоні клітинок В5:В11.

23. Ввести в клітинку В15 формулу розрахунку вартості гарантії з використанням в цій формулі значення активного перемикача: =В13*10%*(Е15-1).

24. Встановити (активізувати) перемикач „1 рік”. У клітинці В15 з’явиться значення, яке дорівнює 10% від вартості комп’ютера. У формулі „В13*10%” – це 10% від суми комплектуючих „(Е15-1)” дає нам 0, якщо в Е15 - значення „1” (встановлено перемикач „6 міс.”). Якщо ж встановлено перемикач „1 рік”, тоді в Е15 буде значення „2”, „(Е15-1)” дорівнюватиме „1”, а загальне значення формули буде дорівнювати „В13*0,1”. Встановити для клітинки Е15 білий колір шрифту.

25. Для обліку вартості доставки доцільно використати такий елемент управління, як „Прапорець”. Прапорець застосовується в ситуації, коли потрібно вирішити: потрібна або не потрібна доставка комп’ютера. При цьому для опрацювання результатів рішення використовується логічна функція „ЕСЛИ()”. Якщо доставка потрібна, то у вартість комп’ютера потрібно включити вартість доставки. Для встановлення на основному робочому аркуші „Кошторис” елемента управління натиснути кнопку „Прапорець” на панелі інструментів „Форми”. Перемістити курсор миші до верхнього лівого кута клітинки С17 і натиснути ліву кнопку миші. На робочому аркуші з’явиться поле „Прапорець” в режимі редагування. Вилучити стандартний заголовок поля і ввести новий заголовок: „Потрібна”. Знову клацнути правою кнопкою миші на поле „Прапорець” і через контекстне меню вибрати команду „Формат об’єкта”. На екрані з’явиться діалог елемента управління. Ввести значення полів: „встановлений”, „зв’язок з клітинкою” - Е17. Натиснути кнопку „ОК”. Діалог зникне з екрана. При встановленні курсором миші прапорця всередині елемента управління з’являється значок-позначка, а в клітинці Е17 – значення „ИСТИНА”.

26. Ввести логічну формулу розрахунку вартості доставки в клітинку В17. Для цього потрібно встановити курсор миші в клітинку В17 і натиснути кнопку „Вставка функції” (fx). З’явиться діалог майстра функцій. Вибрати „Логічні” в списку „Категорія”. У списку „Функція” вибрати функцію „ЕСЛИ()”. Натиснути кнопку „ОК”. Ввести значення параметрів: „лог_выражение” - Е17, „значение_если_истина” - 50, „значение_если_ложь” - 0. Натиснути кнопку „ОК”. В результаті вартість доставки в клітинці В17 буде формуватися прапорцем в клітинці С17. Клацнути мишею на прапорці (або на його заголовку). Позначка з прапорця зніметься і в клітинці В17 з’явиться значення „0” або „-„. Клацнути мишею на пра порці ще раз. У клітинці В17 з’явиться значення „50,00 грн”.

Встановити для клітинки Е17 білий колір шрифту.

27. Для підрахунку суми вартості комплектуючих зібраного комп’ютера, гарантії і доставки ввести в клітинку В19 формулу: =В13+В15+В17.

28. Увести в клітинку С19 формулу перерахування вартості комп’ютера в долари: =В19/D3. В клітинці D3 зберігається поточний курс долара. Встановити для клітинки С19 доларовий формат. Для цього необхідно виконати команду головного меню „Формат/Клітинки”, закладка „Число”. Вибрати курсором числовий формат „Усі формати”. Далі вибрати у вікні маску формату # ##0. Встановити курсор у полі „Тип” перед встановленою маскою і ввести з клавіатури знак долара: $. Маска формату буде мати вигляд $# ##0. Вона означає, що головні нулі в сумі відображатися не будуть, а перед першою значущою цифрою буде виводитися символ долара. Натиснути кнопку „ОК”. У клітинці С19 вартість замовлення буде показана в доларах. Основний робочий аркуш „Кошторис” буде мати свій остаточний вигляд. Закрити панель інструментів „Форми”.

29. Розрахувати вартість замовлення на придбання комп’ютера визначеної конфігурації.

32. Зберегти файл у власній папці з іменем „Вартість замовлення_Прізвище”.

Мета: Навчитись використовувати пошук рішення при розв’язанні системи рівнянь.

Команда меню Сервис – Пошук рішення Microsoft Excel дає змогу розв’язувати системи рівнянь, задачі лінійної оптимізації.

Щоб скористатися даним сервісом Microsoft Excel слід спочатку підготувати дані на листі робочої таблиці:

1. Зарезервувати для кожної змінної чарунку електронної таблиці.

2. Ввести в вигляді формули цільову функцію (для задачі лінійної оптимізації), підставляючи адреси чарунок у позиції змінних.

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

Далі можна починати розв’язання задачі. Для цього слід подати команду меню Сервис – Пошук рішення. У вікні діалогу команди слід визначити:

1. адресу чарунки, у якій міститься цільова функція, якщо розв’язується задача лінійної оптимізації, при розв’язанні системи рівнянь поле повинно бути порожнім, а також визначити якого значення повинна досягти цільова функція – мінімального, максимального або певного;

2. адреси чарунок, що змінюються, ці чарунки, які було зарезервовано для змінних;

3. в області Ограничения добавити обмеження, що визначають яке значення повинно прийняти кожне рівняння;

ЧТО ТАКОЕ УВЕРЕННОЕ ПОВЕДЕНИЕ В МЕЖЛИЧНОСТНЫХ ОТНОШЕНИЯХ? Исторически существует три основных модели различий, существующих между...

ЧТО И КАК ПИСАЛИ О МОДЕ В ЖУРНАЛАХ НАЧАЛА XX ВЕКА Первый номер журнала «Аполлон» за 1909 г. начинался, по сути, с программного заявления редакции журнала...

Конфликты в семейной жизни. Как это изменить? Редкий брак и взаимоотношения существуют без конфликтов и напряженности. Через это проходят все...

Что вызывает тренды на фондовых и товарных рынках Объяснение теории грузового поезда Первые 17 лет моих рыночных исследований сводились к попыткам вычислить, когда этот...

Не нашли то, что искали? Воспользуйтесь поиском гугл на сайте: