Міністерство освіти, науки, молоді та спорту України

Міністерство освіти, науки, молоді та спорту України

Дніпропетровський національний університет
ім. Олеся Гончара

Кафедра статистики, обліку та економічної інформатики

 

 

МЕТОДИЧНІ ВКАЗІВКИ

ДО САМОСТІЙНОЇ РОБОТИ З РОЗДІЛУ

ДИСЦИПЛІНИ «ІНФОРМАТИКА»

"ЗАСТОСУВАННЯ MICROSOFT EXCEL 2010"

 

 

Дніпропетровськ

Розглянуто один з розділів дисципліни «Інформатика». Викладено основні принципи роботи в табличному процесорі Microsoft Excel 2010. Наведено докладний опис змін у інтерфейсі, оновлених функцій та настройок програмного додатка, необхідних для роботи досвідченого користувача в середовищі MS Excel 2010.

Для студентів I курсу спеціальностей «Фінанси і кредит», «Маркетинг», «Облік і аудит» денного та заочного відділень економічного факультету ДНУ.

 

Вступ

 

Порівняно з попередніми версіями електронних таблиць Microsoft Excel 2010 відкриває багато нових можливостей аналізу даних, візуалізації, керування і спільного доступу до інформації.

Розширені можливості для створення складних електронних таблиць дозволяють здійснювати аналіз величезних обсягів інформації. На відміну від попередніх версій, у Microsoft Excel 2010 тепер можлива робота з файлами розміром, більшим двох гігабайтів, значно збільшено обсяги робочих аркушів книги.

Новий режим Microsoft Office Backstage ™ прийшов на зміну звичайному меню «Файл» і дозволяє швидко виконувати операції збереження, друку, публікації та надання загального доступу. Покращена стрічка прискорює доступ до найбільш часто застосовуваних команд і допомагає створювати власні вкладки, які оптимально підходять до персонального стилю роботи.

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

Excel 2010 дозволяє максимально наочно використовувати режим Зведена таблиця для динамічного поділу та фільтрації даних, що дозволяє відобразити тільки найнеобхідніше. За допомогою фільтра пошуку можна значно скоротити час, необхідний для перегляду великих обсягів даних у звичайних і зведених таблицях. Функція спільного редагування в додатку Excel Web App дає можливість декільком користувачам, що знаходяться в різних місцях, одночасно працювати з однією електронною таблицею.

У цілому, порівняно з попередніми версіями (Microsoft Excel 2000, 2003, XP) у Microsoft Excel 2010 істотно змінено інтерфейс електронних таблиць, розташування багатьох функцій додатку і настройок, тому ця версія електронних таблиць потребує детального вивчення.

Основи роботи в табличному процесорі Excel 2010

Рис. 4. Елементи вікна Excel 2010

1 - поле назви головного вікна; 2 - стрічка меню;
3 – панель швидкого доступу; 4 - групи команд вкладок стрічки; 5 - поле імені; 6 - рядок формул;
7 - активний аркуш робочої книги; 8 - активна (поточна) комірка; 9 - ім'я стовпця; 10 - ім'я рядка.

 

Робочі книги - це файли MS Excel, які можуть містити один або декілька робочих аркушів, у Excel 2010 вони мають розширення. xlsx.

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

Розміри робочого аркушаExcel2010порівняно з попередніми версіями електронних таблиць значнозбільшено, він тепер складається з 16 384 стовпців і 1 048 576 рядків.

Рядки мають числову нумерацію, стовпці іменовані буквами англійського алфавіту: A, B, C і т.д., коли алфавіт закінчується, за Z ідуть AA, AB, AC,… AZ; AAA, AAB, AAC, …, AAZ і т.д.

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

Рядок формул з находиться безпосередньо під стрічкою (див. рис. 4), він призначений для відображення вмісту активної комірки – даних або формули, за допомогою якої отримані дані. Рядок формул зручно використовувати для перегляду або редагування вмісту активної комірки.

У Excel2010 рядок формул набув змінного розміру, який автоматично збільшується, щоб у рядку розмістилися довгі формули з більшим рівнем вкладеності. Тепер можна не затуляти формулою інші дані в таблиці. Для збільшення розміру рядка формул необхідно клацнути на ньому правою кнопкою миші і вибрати в контекстному меню Розгорнути рядок формул або використати сполучення клавіш Ctrl+Shift+U.

Під час роботи з книгами (файлами) програма Excel2010 надає деякі нові можливості.

Відновлення попередніх версій. Завдяки функціям автовідновлення, створеним на основі попередніх версій системи Microsoft Office, в Excel 2010 з’явилася можливість відновлення версій файлів, які було закрито без збереження. Це зручно, якщо забуто зберегти книгу вручну, збережено непотрібні зміни або потрібно повернутися до попередньої версії книги.

Безпечне подання. Програма Excel 2010 містить безпечне подання, яке дає змогу приймати обґрунтовані рішення, перш ніж піддавати комп’ютер можливому ризику. За замовчуванням документи, отримані з інтернет-джерел, відкриваються в безпечному поданні. При цьому в рядку повідомлень відображається попередження та параметр активації можливості редагування. Можна керувати джерелами, які мають відкриватися в безпечному поданні, а також настроїти певні типи файлів, що мають відкриватися в безпечному поданні незалежно від їх джерела.

Надійні документи. У системі Office 2010 з’явилася функція «Надійні документи», призначена для документів, які мають активний вміст, наприклад макроси. Тепер після підтвердження того, що активний вміст у документі можна безпечно активувати, немає потреби робити це повторно. Програма Excel 2010 запам’ятовує надійні книги, тому запит не з’являтиметься кожного разу під час відкриття книги.

Рис. 8. Автозаповнення комірок

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

Для створення користувацького списку потрібно ввести набір значень, виділити його, вибрати на стрічці команду Файл – Параметри – Додатково – Змінити списки, натиснути кнопку Імпорт.

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

Рис. 9. Вікно для створення послідовностей

Форматування даних

Дані в Excel виводяться на екран у певному форматі, за замовчуванням - в універсальному форматі Загальний. Можна змінити формат подання даних, застосовуючи функцію стрічки Головна – команда Формат згрупи Комірки - Формат комірок (викликають комбінацією клавіш Ctrl+1) або Формат комірок у контекстному меню. З'явиться вікно діалогу Формат комірок, у якому потрібно вибрати вкладку Число (рис. 10).

Рис. 10. Вікно числових форматів

Програма Excel округляє числові дані. Для збільшення кількості чисел після коми потрібно в групі Число вкладки стрічки Головна натиснути кнопку , для зменшення - (рис. 11).

Рис. 11. Група Число вкладки стрічки Головна

Числові формати мають такі різновиди:

- числовий (цілі числа та числа з дробовою частиною, можна задати кількість десяткових знаків, розділення розрядів і формат від’ємних чисел);

- грошовий і фінансовий (можна вибрати позначення валюти);

- процентний (0,01 = 10% - автоматично множить число на 100, необхідно призначати до введення даних і враховувати цю властивість у ході обчислень);

- дата і час (число днів від 01.01.1900 р. – це ціла частина, час – дробова частина);

- дробовий (відображення дробової частини різними частками звичайного дробу);

- експонентний (науковий – стандартна математична форма подання чисел);

- всі формати (містить шаблони форматів, редагуванням яких можна створити користувацький формат).

Змінити тип значення в комірці можна за допомогою кнопок в групі Число вкладки стрічки Головна: – грошовий формат, – процентний, – числовий (рис. 11).

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

Зміну ширини стовпців і висоти рядків можна виконати перетягуванням за натиснутої лівої кнопки миші роздільника в області нумерації або за допомогою функції вкладки стрічки Головна – команда Формат згрупи Комірки - Ширина стовпця або Висота рядка.

Для підбору розмірів комірок за обсягом вмісту в Excel застосовують функції вкладки стрічки Головна – команда Формат згрупи Комірки - Автопідбір ширини або Автопідбір висоти. Для окремого стовпця або рядка можна виконати автопідбір вручну подвійним клацанням на роздільнику в заголовку.

Умовне форматування. Excel 2010 пропонує більш гнучке умовне форматування, за допомогою якого можна визначити й продемонструвати важливі тенденції, виділити відхилення в даних, позначити різними стилями форматування дані, які задовольняють певні вимоги. У Excel 2010 доступні додаткові стилі, параметри стовпців даних і набори позначок. На аркушах книги можна використовувати одночасно будь-яку кількість правил умовного форматування. Для зручності перегляду й керування правилами можна застосувати спеціальне вікно: перейти до вкладки Головна, група Стилі, розкрити меню кнопки Умовне форматування і вибрати пункт Керування правилами. У вікні Створення правил умовного форматування (рис. 12) необхідно вибрати тип правила, стиль та інші параметри умовного форматування.

 

 

Рис. 12. Вікно правил умовного форматування

 

Рис. 13. Приклад неправильної і правильної фіксації посилань

Наприклад, для створення таблиці множення (за допомогою лише однієї формули), у комірку B2 необхідно ввести формулу =$A2*B$1, тобто зафіксувати перший стовпець A і перший рядок таблиці 1 (рис. 14), а потім скопіювати формулу до всіх комірок таблиці перетягуванням маркера автозаповнення спочатку до кінця стовпця В, а потім, не знімаючи виділення, до кінця таблиці.

Рис. 14. Фіксація посилань у формулі
для створення таблиці множення

Застосування функцій

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

Аргументи функції записують у круглих дужках відразу за назвою функції та відокремлюють один від одного символом «;». Як аргументи можна використовувати числа, текст, логічні значення, масиви або посилання. Аргументи можуть бути як константами, так і формулами. У свою чергу, ці формули можуть містити інші функції. Функції, що є аргументами інших функцій, називають вкладеними (складними). В Excel 2010 кількість рівнів вкладення функцій збільшена з 7 до 64, а кількість аргументів функції збільшена з 30 до 255. Деякі функції мають необов'язкові аргументи, які можна не задавати під час введення аргументів і обчислення значення функції.

Найпростішою і часто застосовуваною є функція підсумовування (автосума). Щоб підсумувати числа за стовпцем або рядком, достатньо натиснути кнопку
Автосума в групі Редагування вкладки стрічки Головна, при цьому з'являється пунктирна рамка, що показує комірки, значення яких будуть складені. Для зміни області обчислення суми достатньо виділити потрібні комірки і натиснути клавішу Enter. Найшвидшим способом застосування автосуми є такий: виділити діапазон підсумовуваних комірок, включаючи комірки, у які буде записано результат, і натиснути кнопку автосуми.

Excel містить більше 400 вбудованих функцій, тому безпосередньо вводити з клавіатури у формулу назви функцій і значення аргументів не завжди зручно. В Excel є спеціальний засіб для роботи з функціями – Майстер функцій. У процесі роботи з цим засобом спочатку пропонується вибрати потрібну функцію зі списку категорій, а потім у вікні діалогу ввести вхідні значення. Майстер функцій в
Excel 2010 викликається натисненням на кнопку Майстер функцій , яка розташована в рядку формул. Інший спосіб викликати Майстра функцій – вибрати Інші функції зі списку, який відкривається на позначці автосуми (рис. 15).

Рис. 15. Вибір Майстра функцій

При цьому з'явиться вікно вибору функцій (рис. 16). Усі функції у вікні поділені на категорії. Найбільш корисна є категорія 10 нещодавно використаних - це ті функції, які останніми застосовував користувач. Крім того, є інші категорії функцій: статистичні, текстові, математичні, логічні та ін. Для створення складеної функції необхідно під час редагування формули перейти в адресну частину рядка формул, у якій при цьому відкривається список 10 нещодавно використаних функцій, останній елемент цього списку Інші… відкриває вікно Майстра для повторного вибору функції.

 

Рис. 16. Вікно Майстра функцій

Наприклад, запис формули складеної функції для обчислення значення виразу за заданих аргументів має такий вигляд (рис. 17):
КОРІНЬ(СУМ(СТЕПІНЬ(B1;2);СТЕПІНЬ(B2;2))). Однак не завжди зручно застосовувати складені функції, в даному прикладі для складення і піднесення до степеню можна використати позначення арифметичних дій і тоді формула набуде спрощеного вигляду: КОРІНЬ(B1^2+B2^2).

Рис. 17. Обчислення складеної функції

Серед безлічі функцій Excel певний інтерес для вирішення економічних завдань становить функція ЯКЩО, яка належить до категорії логічних і дозволяє виконати обчислення з перевіркою умови. Синтаксис функції:

ЯКЩО(логічний_вираз;значення_якщо_істина;значення_якщо_неправда), де

логічний_вираз - це умова, яку перевіряють - будь-яке значення або вираз, що набуває значення ІСТИНА або НЕПРАВДА;

значення_якщо_істина - це значення, яке повертається, якщо логічний_вираз дорівнює ІСТИНА, тобто якщо умова виконана;

значення_якщо_неправда - це значення, яке повертається, якщо логічний_вираз дорівнює НЕПРАВДА, тобто якщо умова не виконана.

Приклад – обчислення комісійних для менеджерів із продажів, виходячи з умови, що звичайні комісійні становлять 5,5% від обсягу продажів. Якщо обсяг продажів перевищує $150 000, то виплачують комісійні за преміальною
ставкою 7%.

Розв’язання прикладу обчислення комісійних за допомогою функції ЯКЩО наведено на рис. 18.

Рис. 18. Застосування логічної функції ЯКЩО

Для перевірки декількох умов потрібно застосувати вкладення функції ЯКЩО. Слід нагадати, що в Excel 2010 допускається до 64 вкладень функцій.

У відповідь на побажання академічного, інженерного й наукового співтовариств в Excel 2010 запропонований набір більш точних статистичних та інших функцій. Крім того, деякі з існуючих функцій перейменовані таким чином, щоб імена краще описували їх призначення.

Низку функцій було оптимізовано з метою підвищення точності. Наприклад, статистичні функції бета-розподілу й розподілу хі-квадрат в Excel 2010 повертають більш точні результати.

Імена деяких статистичних функцій приведено у відповідність до термінів, використовуваних науковим співтовариством, а також до інших імен функцій Excel. Нові імена більш точно описують призначення відповідних функцій. Книги, створені в попередніх версіях Excel, будуть продовжувати працювати, незважаючи на ці зміни, оскільки вихідні функції, як і раніше, доступні в категорії Сумісність.

Рис. 19. Створення діаграми

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

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

Рис. 20. Створення діаграми

Рис. 21. Настроювання вмісту легенди і категорій діаграми

Найбільш швидким способом створення діаграми є такий: виділити таблицю з даними, включаючи заголовки стовпців і рядків, натиснути функціональну клавішу [F11] – буде створено діаграму заданого за замовчуванням типу на окремому аркуші.

Побудова графіків функцій

Для побудови графіка функції необхідно:

1) створити на аркуші діапазон значень аргументу функції з урахуванням проміжку та кроку, використовуючи автозаповнення числового ряду;

2) обчислити значення функції для кожного значення аргументу, використовуючи копіювання формули;

3) виділити діапазон тільки зі значеннями функції;

4) при створенні діаграми обрати тип - графік.

Після побудови графіка функції підписами до осі значень будуть номери значень аргументу. Для додавання власне значень аргументу як підписів до осі Х необхідно вибрати в контекстному меню діаграмипункт Вибрати дані, в області Підписи горизонтальної вісі (категорії) вікна Вибір джерела даних (рис. 21) натиснути кнопку [Змінити] і указати мишею діапазон значень аргументу.

Приклад: побудова графіка функції y = sin2x, якщо x змінюється в діапазоні від 30° до 120° із кроком 5° (рис. 22).

Рис. 22. Побудова графіка функції y = sin2x

Слід звернути увагу на те, що в разі застосування тригонометричних функцій в Excel необхідно переводити аргумент із градусів у радіани, це можна зробити за допомогою однойменної функції РАДІАНИ (рис. 22, див. адресний рядок).

Рис. 23. Нестандартна діаграма із двома вертикальними осями

Рис. 26. Підбір параметра

4.2. Пошук рішення

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

Функція Пошук рішення перебуває на вкладці стрічки Дані і може бути відсутня в Excel 2010 при стандартному варіанті установки пакета Microsoft Office. Для додавання функції Пошук рішення необхідно застосувати настроювання стрічки Файл - Параметри – Надбудови, вибрати в нижній частині вікна розділ Керування: Надбудови Excel, натиснути кнопку [Перейти] і включити опцію «Пошук рішення». За наявності установчих файлів Microsoft Office компонент буде автоматично доданий до функцій вкладки Дані.

Відправною в процесі пошуку оптимального рішення є створена на робочому аркуші модель обчислення, яка включає:

- Цільову комірку, значення в якій повинне бути максимізоване, мінімізоване або ж дорівнювати певному значенню; містить формулу, що прямо або опосередковано посилається на змінювані комірки;

- Змінювані комірки - невідомі задачі, комірки, значення в яких будуть послідовно (методом ітерацій) змінюватися доти, поки не буде отримане оптимальне значення в цільовій комірці;

- Обмеження на співвідношення змінних - формули з посиланнями на змінювані комірки.

У моделі пошуку рішення допускається до 200 змінюваних комірок (змінних), по два обмеження для кожної змінюваної комірки та 200 обмежень для всієї моделі.

Технологія пошуку рішення така:

1) за необхідності ввести в комірки аркуша сталі величини, які впливають на результат;

2) зарезервувати комірки під невідомі величини (змінювані комірки);

3) увести в цільову комірку формулу із прямими або непрямими посиланнями на змінювані комірки;

4) увести формули для обмежень із посиланнями на змінювані комірки;

5) установити курсор у цільову комірку;

6) вибрати функцію вкладки Дані - Пошук рішення;

7) у вікні пошуку рішення вказати вид оптимізації та діапазон змінюваних комірок (рис. 27);

8) для введення обмежень натиснути кнопку [Додати] у вікні пошуку рішення (рис. 28), для введення наступного обмеження натиснути кнопку [Додати] у вікні додавання обмежень, для завершення - кнопку [ОК];

9) натиснути кнопку [Знайти рішення] у вікні пошуку рішення.

 

Рис. 27. Вікно пошуку рішення

Рис. 28. Вікно додавання обмежень під час пошуку рішення

В економічних задачах лінійної оптимізації, як правило, необхідні так звані природні обмеження на змінювані комірки - їх значення повинні бути невід’ємними (>= 0) та цілими, перше обмеження можна задати за допомогою опції у вікні пошуку рішення (рис. 27), а друге – за допомогою кнопки [Параметри] у вікні пошуку рішення (рис. 27) – після її натиснення необхідно відключити опцію «Ігнорувати цілочислені обмеження».

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

Обробка списків (баз даних)

У загальному значенні термін база даних можна застосувати до будь-якої сукупності зв'язаної впорядкованої інформації, об'єднаної за певною ознакою, наприклад телефонні списки, списки клієнтів, транзакцій, активів, пасивів і т. ін. Основне призначення баз даних - швидкий пошук інформації.

Одним з найбільш часто виконуваних за допомогою електронних таблиць завдань є обробка списків. Електронні таблиці мають набір функцій, призначених для аналізу списків, в Excel 2010 ці функції розширено та покращено.

Список - таблиця, побудована за принципом бази даних, складається з рядків і стовпців однакової структури (відсутні об'єднання комірок), які в базах даних називаються записами і полями відповідно.

Кожен рядок таблиці-списку можна розглядати як одиничний запис. Інформація в межах кожного запису міститься в полях (стовпцях) з унікальними заголовками. Кожен стовпець повинен містити однорідну інформацію. Необхідно уникати порожніх рядків і стовпців усередині списку.

На робочому аркуші можна обробляти кілька списків, але все-таки краще помістити кожен список на окремий аркуш робочої книги.

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

Робота з командою Форма. Команда Форма відображає на екрані форму, що являє собою вікно, призначене для перегляду та редагування записів у базі даних, а також для додавання нових і видалення існуючих записів. Крім того, за допомогою форми можна здійснити пошук конкретних записів на підставі складних критеріїв. Для використання форми в Excel 2010 її потрібно додати до вкладки Дані за допомогою настроювання Файл - Параметри – Настройка стрічки, далі слід обрати категорію «Всі команди» і додати команду Форма в нову групу. Після цих настроювань можна використати вікно форми (рис. 29).

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

Рис. 29. Вікно форми

Для пошуку записів, що задовольняють певні умови, у вікні форми потрібно натиснути кнопку [Критерії], після цього з'явиться порожнє вікно запису, у відповідних полях якого вводяться умови. Для перегляду записів, що задовольняють критерії, потрібно використати у вікні критеріїв кнопки [Назад] і [Далі].

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

Найбільш швидке та просте сортування за зростанням або убуванням проводиться за допомогою відповідних кнопок в групі Сортування і фільтр.

У разі вибору команди Сортування відкривається вікно діалогу “ Сортування ”, в якому слід додати рівні (стовпці) для сортування та визначити критерій. За допомогою списку, що розкривається, Сортувати по можна вибрати поле для сортування. Порядок сортування встановлюється перемикачами за зростанням або убуванням (рис. 30). Додаткові розділи «Потім за» дозволяють визначити порядок вторинного сортування для записів, в яких є збіжні значення.

 

Рис. 30. Вікно сортування списку

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

В Excel 2010 для фільтрації даних використовуються функції вкладки стрічки Дані групи Сортування і фільтр - Фільтр (автофільтр) і Додатково – Розширений фільтр. У випадку, якщо критерії прості, то для вибірки потрібної інформації достатньо команди Фільтр. У комірках виділених заголовків з'являться нові елементи , за допомогою яких можна встановлювати критерії фільтрації даних.

Для перевірки одного або декількох умов використовується команда Числові (або інші) фільтри в списку фільтра, що випадає (рис. 31).

Рис.31. Вибір критерію фільтрації

У разі використання складних критеріїв слід застосовувати команду Додатково - Розширений фільтр групи Сортування і фільтр. У такому випадку умови фільтрації задаються у вигляді окремої таблиці (рис.32).

Рис. 32. Застосування розширеного фільтра

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

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

У вікні Проміжні підсумки (рис. 33) необхідно вибрати операцію (підсумкову функцію), що буде застосовуватися для груп записів, які утворюються за кожної зміни значення у відсортованому аналізованому стовпці, і вказати, в якому зі стовпців буде відображатися підсумок.

У списку із проміжними підсумками можна застосувати три рівні угруповання даних (рис. 33). Для скасування підсумків у вікні Проміжні підсумки потрібно натиснути кнопку [Видалити все].

 

Рис. 33. Підбиття підсумків

Створення зведених таблиць

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

Технологія створення зведеної таблиці така:

1) помістити покажчик у будь-яку комірку в межах таблиці-списку;

2) вибрати вкладку стрічки Вставка, команду Зведена таблиця;

3) вказати джерело даних у вікні «Створення зведеної таблиці», краще розмістити зведену таблицю на новому аркуші (рис. 34);

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

У результаті створюється зведена таблиця на окремому аркуші, що буде вставлений перед поточним аркушем у робочу книгу (рис. 36).

Разом зі зведеною таблицею на аркуші автоматично з'являється вікно Список полів зведеної таблиці, яке можна використовувати для зміни її структури, те ж саме можна виконати перетягуванням полів у відповідні частини таблиці.

 

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

Рис. 35. Створення зведеної таблиці

 

Рис. 36. Зведена таблиця

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

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

Лабораторні роботи

Лабораторна робота 1
Основи роботи в табличному процесорі Excel

Завдання

1. Форматування чисел. Ввести в комірки одне й те саме число - 1234,5.
Звести числа до вигляду, наведеного нижче:

1234,5

1 234,50

$1 235

? 1 234,50

18 травень 03

18.05.03 12:00

1234 1/2

1,235E+03

2. Введення тексту. Ввести наступний текст у комірки (Формат - Комірки, Вирівнювання, Переносити за словами):

Реальні дані 2011 р.

Реальні дані 2011 р.

Реальні дані 2011 р.

3. Застосовуючи метод автозаповнення, створити наведені нижче списки:

Січ	Пн	Товар 1	Тов. 1 Зам.3			2,1	5,4
Лют	Вв	Товар 2	Тов. 1 Зам.4			2,3
Бер	Ср	Товар 3	Тов. 1 Зам.5			2,5	4,6
Кві	Чт	Товар 4	Тов. 1 Зам.6			2,7	4,2
Тра	Пт	Товар 5	Тов. 1 Зам.7			2,9	3,8

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

Обсяг реалізації
	Січ	Лютий	Бер	Усього
Товар 1	300,00 грн	250,00 грн	360,00 грн	910,00 грн
Товар 2	450,00 грн	400,00 грн	500,00 грн	1 350,00 грн
Товар 3	600,00 грн	550,00 грн	640,00 грн	1 790,00 грн
Товар 4	750,00 грн	700,00 грн	780,00 грн	2 230,00 грн
Товар 5	900,00 грн	850,00 грн	920,00 грн	2 670,00 грн
Разом	3 000,00 грн	2 750,00 грн	3 200,00 грн	8 950,00 грн

5. Застосовуючи формули з абсолютними, відносними та змішаними посиланнями, створити: а) таблицю множення; б) таблицю приросту депозитного внеску в 10 доларів для різних процентних ставок і тимчасових періодів за формулою складних відсотків: сума внеску*(1+%)^період.