Лекція з курсу «Прикладні програми (Електронні таблиці Excel)»

Скачати 261.67 Kb.

Назва	Лекція з курсу «Прикладні програми (Електронні таблиці Excel)»
Сторінка	5/5
Дата	25.03.2013
Розмір	261.67 Kb.
Тип	Лекція

bibl.com.ua > Інформатика > Лекція

1 2 3 4 5

6.3. Розрахунок загальних підсумків в базі даних

Підведення проміжних підсумків

Відсортувавши список та відфільтрувавши у ньому тільки потрібні записи, можна скористатися командою «Данные\Итоги» для одержання різноманітних підсумкових даних.

Командою «Итоги» можна додати підсумкові рядки для кожної групи даних у списку, а також виконати інші розрахунки на груповому рівні. За допомогою цієї команди неважко обчислити, наприклад, середнє значення заданого поля для кожної групи записів, знайти кількість записів у кожній групі, кількість порожніх елементів у кожній групі, обчислити стандартне відхилення для кожної групи і т. д. Можна підвести також і загальні підсумки, застосувавши обрану функцію не тільки до груп записів, але і до всього списку. Користувач може задати також місце розташування підсумків наприкінці або на початку списку, що буває істотно для довгих списків. Можна додати також проміжні підсумки для окремих підгруп існуючих груп записів і створити, таким чином, вкладені проміжні підсумки.

Для створення проміжних підсумків необхідно:

Відсортувати список по полю, по якому окремі записи повинні бути розбиті на групи.
Виконати команду «Данные\Итоги».
У вікні діалогу «Промежуточные итоги» зі списку «При каждом изменении в» виберіть поле, по якому список розбитий на групи записів.
У списку «Операция» оберіть функцію, що повинна використовуватися при визначенні проміжних підсумків.
У полі «Добавить итоги по» вкажіть поля, по яким повинні бути визначені проміжні підсумки.
У разі потреби, скиньте прапорець «Итоги под данными», щоб відобразити рядки з підсумками над відповідними даними.
Якщо окремі групи записів повинні бути розташовані та виведені до друку на різних сторінках, встановіть прапорець параметра «Конец страницы между группами».
Закрийте вікно діалогу натисканням кнопки «OK».

Аналогічним чином можна додати вкладені проміжні підсумки. Для цього потрібно спочатку впорядкувати записи у списку та скинути прапорець параметра «Заменить текущие итоги». Excel відобразить тепер проміжні підсумки, визначені з урахуванням заданої функції, і для підгруп записів. Якщо прапорець параметра «Заменить текущие итоги» скинутий, то для окремих груп записів можна визначити декілька проміжних підсумків із застосуванням різних функцій.

Список з проміжними підсумками завжди структурований. Символічне представлення структури (

) зображено у лівій частині вікна робочої сторінки. Цим можна скористатися, щоб:

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

Для видалення підсумків, а разом із ними і структури, потрібно відкрити вікно діалогу «Промежуточные итоги» та натиснути кнопку «Убрать все». Щоб замінити поточні підсумки новими, одержуваними по іншій формулі, задайте у цьому вікні потрібні параметри та встановіть прапорець «Заменить текущие итоги». Проміжні підсумки будуть автоматично видалені при пересортуванні списку. При цьому на екран буде виведене попереджувальне повідомлення.

Функції роботи зі списками

Excel пропонує користувачеві групу функцій «Работа с базой данных», призначених для обробки списків. Більшість з цих функцій мають “аналоги” у групі функцій «Статистические». Наприклад, БДСУММ() та СУММ(), ДСРЗНАЧ() та СРЗНАЧ() і т. д. Головна відмінність зводиться до того, що функції зазначеної групи баз даних дозволяють опрацьовувати тільки ті клітинки діапазону даних, що задовольняють заданим критеріям.

Загальні правила звертання до функцій баз даних наступні:

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

Зазначені функції виглядають дещо складніше, ніж їх “статистичні аналоги”, але є більш потужними та гнучкими. Вони дозволяють об’єднати виконання обчислень та відбір даних, не вимагаючи попередньої фільтрації та копіювання потрібних даних. Функції даної групи дозволяють обчислювати суму та добуток, знаходити мінімальні та максимальні значення, визначати середнє значення, підраховувати кількість записів, оцінювати дисперсію та стандартне відхилення, вибирати окреме значення зі списку.
6.4. Побудова зведених таблиць
Побудова зведених таблиць. Зведеними називаються допоміжні таблиці, які містять частину даних основної таблиці, відібраних таким чином, щоб залежності між ними відображалися найкращим чином.

Зведені таблиці створюються на основі області таблиці, цілої таблиці або декількох таблиць. Для створення зведеної таблиці виконуємо команду DATA (дані) => PIVOT TABLE REPORT (зведена таблиця). Відкриється перше діалогове вікно PIVOT TABLE WIZARD STEP 10F 4, у якому вказуємо джерело даних для зведеної таблиці. По замовчуванню активізована опція MICROSOFT EXCEL LIST OR DATABASE (база даних) оскільки наша таблиця розташована на даному робочому аркуші. Натискуємо клавішу NEXT (наступний) i переходимо до другого вікна, у якому висвітлюється діапазон комірок, дані яких будуть входити у зведену таблицю. Якщо перед виконанням даних команд вказівник знаходився в середині таблиці, то програма автоматично вставить діапазон цієї таблиці. Якщо вибираємо дані з таблиці, яка знаходиться в іншому файлі, то натискуємо клавішу BROWSE (огляд) знаходимо потрібний файл i завантажуємо його. Натискуємо NEXT (наступний), переходимо до третього вікна. У цьому вікні задається структура зведеної таблиці. У центрі знаходяться області рядків, стовпців, сторінок i даних. Bci заголовки полів таблиці висвітлюються праворуч від перерахованих областей. Цi заголовки потрібно з допомогою мишки перетягнути у відповідні області.

3 допомогою четвертого вікна вибираємо місце розташування таблиці. При активній опції EXISTING WORKSHEET (поточний аркуш) - на поточному аркуші, тобто курсор розташовуємо в комірку, яка буде верхнім лівим кутом зведеної таблиці. Опція NEW WORKSHEET (новий аркуш) – зведена таблиця розташовується на новому аркуші.

Натискуємо клавішу FINISH (готово). Параметри зведеної таблиці можна встановлювати з допомогою команди OPTION (параметри), яка знаходиться у четвертому вікні зведеної таблиці. Якщо таблиця створена i є потреби змінити опції, то клацаємо правою клавішею мишки на зведеній таблиці i вибираємо OPTIONS (параметри). Відкриється вікно PIVOT TABLE OPTION (властивості зведеної таблиці), яке складається з області FORMAT OPTIONS (властивості формату) i DATA OPTIONS (властивості даних). У першій області встановлюються параметри форматування i вигляд зведеної таблиці, а в другій - параметри джерела даних зведеної таблиці. Опції GRAND TOTALS FOR COLUMNS (загальна сума по стовпцях) i GRAND TOTALS FOR ROWS (загальна сума по рядках) задають автоматично проміжкові підсумки, а опція AUTO FORMAT TABLE (автоформат)- використання автоформату при форматуванні таблиці. Якщо активна опція PRESERVE FORMATTING (зберегти форматування), то форматування зберігається i після перегрупування даних. Опція SUBTOTAL HIDDEN PAGE ITEMS дозволяє враховувати значення, які знаходяться в схованих комірках. 3 допомогою опцій FOR ERROR VALUES SHOW (для помилок висвітлити), i FOR EMPTY CELLS, SHOW (для пустих комірок висвітлити) можна задати значення, які виводяться на екран замість стандартних повідомлень про помилки або пусті комірки.

Опція SAVE DATA WITH TABLE LAYOUT (зберегти дані разом з макетом) з області DATA OPTIONS (параметри даних) дозволяє зберегти разом з макетом зведеної таблиці всі початкові дані. Якщо активізована опція REFRESH ON OPEN (поновити при відкритті), то при відкритті зведеної таблиці її значення автоматично поновлюються. Опція ENABLE DRILLDOWN забезпечує одержання на новому аркуші детальну інформацію про ту комірку в області даних, на якій виконується подвійне клацання.

Опція EXTERNAL DATA OPTIONS встановлює режими доступу до зовнішніх джерел даних. Запит на одержання інформації з зовнішньої бази даних може бути виконаний у фоновому режимі. Цей режим встановлюється при допомозі опції BACKGROUND QUERY (фоновий запит).
6.5. Операції редагування та форматування об’єктів зведеної таблиці
Для редагування зведеної таблиці використовується палітра інструментів PRIVOT TABLE (зведена таблиця), яка висвітлюється автоматично після створення зведеної таблиці. Якщо редагування хочемо проводити Дещо пізніше, то для висвітлення цієї палітри інструментів виконуємо команди VIEW (вигляд) => TOOLBARS (палітра інструментів) => PRIVOT TABLE (зведена таблиця) або клацаємо правою клавішею мишки по палітрі інструментів i вибираємо PRIVOT TABLE (зведена таблиця).

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

У цьому контекстному меню розглянемо деякі команди: FORMAT CELLS (формат комірки) – для вибору формату; INSERT (вставка) – вставляння нового поля з використанням обчислень, DELETE (витерти) – витирання позначених полів; REFRESH DATA (перечитати дані) – перечитування зміненої таблиці; SELECT (позначити) - позначення елементів таблиці; GROUP AND OUTLINE (групування i розгрупування) - групування i розгруповування даних по окремих полях i також можна висвітлити або заховати детальну інформацію позначених полів.

В результаті побудови зведеної таблиці вона вся з6ерігається на одному робочому аркуші. 3 допомогою клавіш SHOW PAGE (показати сторінки) на пaлiтpi інструментів PRIVOT TABLE (зведена таблиця) можна створити ряд зведених таблиць, кожна з яких буде висвітлювати один елемент поля сторінки.

Можна змінити розташування клавіш полів сторінки з допомогою команди OPTION (параметри) на пaлiтpi PRIVOT TABLE (зведена таблиця). 3’явиться діалогове вікно, в якому з допомогою команд PAGE LA YOUT (макет сторінки) i FIELD PER COLUMN (число пoлів в cтoвпці) можна задати потрібне розташування клавіш.
Контрольні питання

У чому полягає сутність поняття база даних?
Як здійснити сортування записів у базі даних?
Що таке фільтрація даних?
Які є різновиди фільтрів для виконання фільтрації у базі даних MS Excel?
Як створити розширений фільтр?
Як створити автофільтр?
Яке призначення мають проміжні підсумки? Яка технологія створення проміжних підсумків?
Які ви знаєте функції для роботи з базами даних у MS Excel?
Яка технологія застосування функцій баз даних?
Які різновиди критеріїв для функцій баз даних?
Для чого призначені зведені таблиці та діаграми?
Яка технологія створення зведених таблиць і зведених діаграм?

1 2 3 4 5

Схожі:

Лекція з курсу «Прикладні програми (Електронні таблиці Excel)» Колесников А. Excel 2000 (русифицированная версия). – К.: Изд группа ВНУ, 1999. – 496 с	Лекція з курсу «Прикладні програми (Електронні таблиці Excel)» Колесников А. Excel 2000 (русифицированная версия). – К.: Изд группа ВНУ, 1999. – 496 с
Лекція з курсу «Прикладні програми (Електронні таблиці Excel)» Лекція Робота з фінансовими функціями. Створення, редагування і форматування графіків і діаграм (2 год.)	Лекція з курсу «Прикладні програми (Електронні таблиці Excel)» Вейскорн Джен. Excel 2000. Базовый курс (русифицированная версия). – К.: М., Спб.: Век+Энтроп, Корона, 2000. 464 с
Лекція з курсу «Прикладні програми (Електронні таблиці Excel)» Вейскорн Джен. Excel 2000. Базовый курс (русифицированная версия). – К.: М., Спб.: Век+Энтроп, Корона, 2000. 464 с	Тема: Електронні таблиці. Програма ”Microsoft EXCEL” Мета: навчити учнів розуміти призначення програм для опрацювання табличної інформації; запускати програму EXCEL; вводити інформацію...
УРОКУ Тема: Загальні відомості про електронні таблиці Мета: познайомити учнів з поняттям електронних таблиць та функціями програми Excel	Тема: Ознайомлення з вікном програми MS Excel Щоб запустити Excel, виконаєте команду Пуск / Програми / Microsoft Office / Microsoft Excel
Лекція: Робота з таблицями: версія для друку і PDA Лекція присвячена... Показані можливості сортування даних в таблиці. Дано уявлення про можливості обчислень в таблицях документів Microsoft Word 2007....	Тема: Електронні таблиці Нарахувати стипендію учням за результатами сесії за умовою: якщо середній бал становить

Додайте кнопку на своєму сайті:
Портал навчання