• Списки, робота зі списками
• Створення звіту зведеної таблиці
• Побудова діаграм

Списки, робота зі списками

В Microsoft Excel в якості бази даних можна використовувати список.

Список - набір рядків таблиці, що містить зв'язані дані.

Наприклад, база даних рахунків або набір адрес і телефонних клієнтів.

Використання списку в якості бази даних

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

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

Рекомендації по створенню списку на листі книги

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

Розмір і розташування списку

• На листі не слід поміщати більше одного списку.

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

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

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

• В самому списку не повинно бути порожніх рядків і стовпців. Це спрощує ідентифікацію і виділення списку.
• Важливі дані не слід поміщати біля лівого або правого краю списку; після застосування фільтру вони можуть виявитися прихованими.

Заголовки стовпців

• Заголовки стовпців повинні знаходитися в першому стовпці списку. Вони використовуються Microsoft Excel при складанні звітів, пошуку і організації даних.
• Шрифт, вирівнювання, формат, шаблон, межа і формат прописних і рядкових букв, привласнені заголовкам стовпців списку, повинні відрізнятися від формату, що привласнений рядкам даних.
• Для відділення заголовків від розташованих нижче даних слід використовувати межі клітин, а не порожні рядки або переривчасті лінії.

Зміст рядків та стовпців

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

Сортування рядків за двома чи більше стовпцями

Краще всього, якщо список, що підлягає сортуванню матиме заголовки стовпців.

Щоб відсортувати дані по більш, ніж трьом стовпцям одночасно, відсортуйте список спочатку за трьома найменш значущими стовпцями. Наприклад, якщо список містить відомості про співробітників і його треба відсортувати за полями "Відділ", "Посада", "Прізвище" та "Ім'я", сортування потрібно буде виконати двічі.

1. Вкажіть клітину в списку, який необхідно сортувати.
2. У меню Данные виберіть команду Сортировка.
3. Вкажіть стовпці сортування в полях Сортировать по і Затем по(Натиснути кнопку Копировать уровень).
4. Виберіть спочатку Ім'я в полі Сортировать по і відсортуйте список. Потім виберіть Відділ в полі Сортировать по, Посада в полі Затем по, Прізвище в полі В последнюю очередь, по та кроки 2 - 4 для наступних більш значущих стовпців.

Сортування і вирішення питань, що виникають при сортуванні

Якщо результати сортування виявилися несподіваними, переконайтеся в тому, що:

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

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

Для цього виберіть команду в меню Главная - Формат→ Ячейки, а потім на вкладці Число встановіть текстовий  формат.

Після зміни формату слід наново ввести значення в клітину.

Якщо діапазон клітин був відформатований як текст і був невірно відсортований, встановіть числовий формат клітин, а потім перетворіть текст в числа за допомогою команди Специальная вставка.

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

• Перед даними не застосовувати пропуски для заповнення. Для вирівнювання даних замість заповнюючих пропусків використовуйте кнопки вирівнювання на панелі інструментів Форматирование.
• У діалоговому вікні Язык и стандарты (панель управління Microsoft Windows) встановлені правила сортування, прийняті в Росії.

Порядок сортування змінюється залежно від конкретної країни.

Про зміну прийнятих стандартів дивіться документацію по Microsoft Windows.

• Заголовки стовпців введені тільки в одному рядку.

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

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

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

• Можливо, параметри об'єкту були задані таким чином, що при переміщенні клітин переміщення об'єкту не відбувається. Щоб об'єкт переміщувався при сортуванні клітин, натисніть кнопку Выбор объектов на панелі інструментів Рисование, після чого виділіть об'єкт за допомогою миші. Виберіть в меню Формат команду, відповідну даному об'єкту, а потім виберіть параметр, що дозволяє виконувати переміщення об'єкту разом з клітинами, не змінюючи його розмірів, на вкладці Свойства.

Приклад

Відсортувати дані (список) за 3-ма ключами:

1. «П.І. П.» за абеткою
2. «Стаж» у порядку зростання
3. «Ставка» у спадному порядку

Етапи сортування:

1. Сховати рядок 3.
2. Виділити діапазон В2:J13;
3. У меню Данные → Сортировка
4. У вікні Сортировка диапазона встановити ключі сортування і параметри

Встановлення параметрів для сортування

Відсортована таблиця

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

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

• Автофільтр, включаючи фільтр по виділеному, для простих умов відбору.
• Розширений фільтр для складніших умов відбору

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

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

Фільтри можуть бути використані тільки для одного списку на листі.

1. Виділити діапазон (клітини) у списку, що фільтрується. A2:J13 (див. Рисунок 6.2  . Відсортована таблиця ).
2. Виберіть в меню Данные → Сортировка и фильтр → Фильтр.
3. Перейти до заголовку того стовпця, значення якого треба відфільтрувати.
4. Натисніть кнопку у стовпчику, в якому містяться шукані дані, Наприклад "Стаж".
5. Виберіть зі списку критеріїв фільтрації, що з'явився, пункт: Настраеваемый фильтр.
6. Вказати критерій, якому повинні відповідати дані, що відображаються. "Стаж" дорівнює 9 та дорівнює 12.
7. Повторіть кроки 3 і 4, щоб ввести додаткові обмеження для значень в інших стовпцях. 120<="Налог"<=800 .

Щоб відфільтрувати список за двома значеннями в одному стовпці або застосувати відмінність від рівності операторів порівняння

• Натисніть кнопку зі стрілкою,
• виберіть пункт Настраеваемый фильтр.

Типи критерій

Елементи, які вказуються в діапазоні критеріїв, можна розділити на наступні типи.

• Текстові або числові критерії. При фільтрації використовується порівняння з числом або рядковою (текстової) константою за допомогою операторів, таких як дорівнює (=), більше ніж (>), не дорівнює (<>) і т.д.
• Обчислювані критерії. Фільтрація, що використовує будь-які обчислення.

Умови відбору фільтру

 

Щоб відфільтрувати	Виберіть
Всі рядки списку	Все
Задане число рядків з максимальними або мінімальними значеннями клітин поточного стовпця (можна відобразити 10% рядків, що мають максимальні значення, наприклад «Премія»)	Первые 10
Рядки, що задовольняють дві умови або одну умову з оператором порівняння, відмінним від И (оператор за замовчуванням)	Настраеваемый фильтр
Всі рядки, що мають порожні клітини в поточному стовпці	Пустые
Всі рядки, що мають непорожні клітини в поточному стовпці	Непустые

 

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

Для відображення на екрані всіх записів знову клацніть на стрілці випадаючого списку і виберіть опцію (Выделить все).

Для повернення в звичний режим перегляду (фільтрування даних) виберіть в меню Данные→Сортировка и фильтр→Фильтр. Стрілки спливаючих списків зникнуть. На екрані знову відображатимуться всі записи.

Види умов відбору

При настройці Фільтру або введенні умов відбору в діапазоні умов розширеного фільтру можна використовувати перераховані нижче елементи умов.

Послідовності символів

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

Наприклад

Щоб відібрати рядки, в яких індекс відділення зв'язку рівний 115522, введіть в діапазоні умов число 115522 нижче за заголовок «Індекс відділення зв'язку».

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

Наприклад

При введенні умови «Анг» будуть відібрані рядки з клітинами, що містять слова Ангара, Англія і Ангола.

Щоб одержати точну відповідність відібраних значень заданому зразку, наприклад, текст, слід ввести умову: =''текст''

Знаки підстановки

Щоб відібрати рядки з клітинами, що містять послідовність символів, в деяких позиціях якої можуть стояти довільні символи, слід використовувати знаки підстановки.

Знак підстановки еквівалентний одному символу або довільній послідовності символів.

Таблиця 1  .Знаки підстановки

Щоб знайти	Використовуйте знак	Приклад
Будь-який символ в тій же позиції, що і знак питання	? (знак питання)	д?м задає пошук "дим" і "дім"
Будь-яку послідовність символів в тій же позиції, що і зірочка	* (зірочка)	*іно задає пошук "Любліно" і "Вихіно"
Знак питання, зірочка або тильда	~ (тильда), за якою слідує ?, * або ~	Що~? Задає пошук "Що?"

 

Текстові або числові критерії

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

Таблиця 2  . Оператори порівняння

Оператор	Тип порівняння	Оператор	Тип порівняння
=	Дорівнює	<	Менше
>	Більше ніж	<=	Менше або дорівнює
>=	Більше або дорівнює	<>	Не дорівнює

У Табл. показані приклади рядкових (текстових) критеріїв.

Таблиця 3  . Приклади рядкових критеріїв

Критерій	Дія
>K	Слова, що починаються з букв від Л до Я
<>С	Всі слова, окрім тих, що починаються з букви С
="Січень"	Всі слова "Січень"
См*	Слова, що починаються з букв "См"
с*с	Слова, що починаються з букви "с" і закінчуються буквою "с"
с?с	Слова з трьох букв, що починаються з букви "с" і закінчуються буквою "с"

 

Значення порівняння

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

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

Наприклад, щоб відібрати рядки, що мають значення клітин більше або дорівнює 1000, введіть умову відбору>=1000 нижче заголовока «Кількість».

Примітка

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

Фільтр

Приклад 12 Відфільтрувати дані, в яких 120<="Налог"<=800 на підставі таблиці (Рис.). Використовуємо Фільтр

Початкова таблиця «Нарахування заробітної плати»

Порядок виконання фільтрації:

1. Стати в клітину G9.
2. Данные → Сортировка и Фильтр → Фильтр.
3. Перейти до заголовку того стовпця, значення якого треба відфільтрувати
4. Натисніть кнопку  у стовпці, в якому містяться шукані дані, Наприклад "Ставка"

Вибір критерію фільтру

Завдання умови фільтрації по критерію

120<="Налог"<=800

Відфільтровані записи по заданому критерію

Фільтрація списку за допомогою розширеного фільтру

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

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

Ці рядки будуть використані як діапазон умов відбору.

Діапазон умов відбору- діапазон клітин, що містить набір умов пошуку, який можна використовувати спільно з командою расширенный фильтр(меню Данные→ Сортировка и Фильтр) для відбору списку даних.

Діапазон умов складається, принаймні, з одного рядка підписів умов і, принаймні, з одного рядка власне умов.

Діапазон умов відбору - обов'язково та кількість рядків, на яких є критерії відбору.

Для цього треба:

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

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

c) Введіть в рядки під заголовками умов необхідні критерії відбору.

Переконайтеся, що між значеннями умов і списком знаходиться як мінімум один порожній рядок.

При цьому число рядків визначається кількістю критеріїв пошуку.

Включення порожніх рядків в Діапазон критеріїв неприпустиме, оскільки відображатимуться всі записи списку

1. Створення таблиці критеріїв
2. Виберіть в меню Данные →Сортировка и Фильтр→ Дополнительно.
3. Введіть в полі Диапазон критериев посилання на діапазон умов відбору, включаючий заголовки стовпців і рядка, в яких задано умову відбору.
4. Визначити вид вихідного документу і місце розташування:

a) Щоб показати результат фільтрації, приховавши непотрібні рядки, встановіть перемикач Обработка в положення Фильтровать список на месте. (Рис.7)

Результат за умовою Стаж менше 10 років та Заробітна плата більше 3000 грн.

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

Щоб прибрати діалогове вікно Расширенный фильтр на час виділення діапазону умов відбору, натисніть кнопку згортання діалогового вікна.

Щоб відобразити всі дані Данные →Сортировка и Фильтр →Отобразить все

 

Приклади умов відбору розширеного фільтру

В умови відбору розширеного фільтру можуть входити

1. Декілька умов, що накладаються на один стовпець,
2. Декілька умов, що накладаються одночасно на декілька стовпців
3. А також умови, що накладаються на значення, яке повертаэться формулою.

На клітини одного стовпця накладаються три або більше умов відбору

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

Наприклад, для наступного діапазону умов будуть відібрані рядки, що містять або «Білов», «Батурін» або «Сушкін» в стовпці «Продавець».

Продавець
Білов	"ИЛИ"
Батурін
Сушкін

 

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

Щоб накласти умови відбору не декілька стовпців одночасно, введіть умови в клітини, розташовані в одному рядку діапазону умов. Наприклад, для наступного діапазону умов будуть відібрані рядки, що містять «Продукти» в стовпці «Товар», «Білов» в стовпці «Продавець», і що мають суму реалізації більше 1000.

Товар	Продавець	Продаж
Продукти	Білов	>1000	"И"

Товар	Продавець	Продаж
Продукти			"ИЛИ"
	Білов
		>1000

Щоб накласти складну умову відбору, введіть її складові частини в окремі рядки діапазону умов.

Наприклад, для наступного діапазону умов будуть відібрані рядки, що містять «Білов» в стовпці «Продавець» і що мають суму реалізації більше 3000 або рядки, що містять «Батурін» в стовпці «Продавець» і що мають суму реалізації більше 1500.

Продавець	Продаж
Білов	>1000	"И"	"ИЛИ"
Батурін	>1500	"И"

 

Фільтр "ИЛИ"

Приклад 13. Відфільтрувати дані, з категорією 1 або у кого «Премия»>100 з фільтрацією на місці

Для цього треба:

a) Вставити рядки для критерію.

b) Скопіювати зі списку заголовки стовпців, що фільтруються в ту частину робочого листа, де буде розташована таблиця критеріїв.

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

d) Введіть в рядки під заголовками умов необхідні критерії відбору.

Переконайтеся, що між значеннями умов і списком знаходиться як мінімум один порожній рядок.

1. Створення таблиці критеріїв
2. У меню Данные →Сортировка иФильтр→ Дополнительно - діалогове вікно - Расширенный фильтр і т.д. .

Розширений фільтр «ИЛИ»

Фільтр "И"

Приклад 14 Вибрати дані по категорії =1 і з премією >=100

Діапазон умов обов'язково $A$5:$H$6, а не H7

 Розширений фільтр «И»

Складний фільтр "И", "ИЛИ"

Приклад 15. Вибрати дані по категорії 1 з ПРЕМІЄЮ>100 або по категорії 2 з ПРЕМІЄЮ>500 і помістити результати в інше місце

Складній фільтр "И", "ИЛИ", результати в інше місце

Використання обчислювального критерію

В умові відбору використовується значення, що повертається формулою: ИСТИНА або ЛОЖЬ

В умові фільтрації можна використовувати значення, що повертається формулою.

При використанні обчислюваних критеріїв рекомендується пам'ятати деякі правила.

1. Створіть новий заголовок або просто залиште порожню клітину.
2. Не використовуйте заголовки полів списку в діапазоні критеріїв.
3. Можна використовувати будь-яку кількість обчислюваних критеріїв, а також поєднання обчислюваних критеріїв з необчислюваними.
4. Не звертайте уваги на значення, що повертаються формулами в діапазоні критеріїв. Вони посилаються на перший рядок списку.
5. Якщо ваша обчислювана формула посилається на значення поза списком, використовуйте абсолютні, а не відносні посилання.

Наприклад, замість С1 використовуйте посилання $С$1.

При створенні формул обчислюваних критеріїв використовуйте перший рядок списку (не рядок заголовків!).

Використовуйте відносні, а не абсолютні посилання.

Наприклад, замість $С$5 використовуйте посилання С5.

Формула, що використовується для створення умови відбору, повинна використовувати для посилання на відповідне поле в першому записі відносне посилання.

Вся решта посилань у формулі повинна бути абсолютними, а формула повинна повертати результат ИСТИНА або ЛОЖЬ.

Наприклад, для наступного діапазону умов будуть відображені рядки, в яких значення в стовпці H перевищує середнє значення в клітинах H12:H19;

Якщо необхідно відфільтрувати список так, щоб були показані тільки проекти, які використовують ресурси більше середнього, застосуйте наступну формулу: =H12>=СРЗНАЧ($H$12:$H$19)

СЕРЕДНЄ

=H12>=СРЗНАЧ($H$12:$H$19)

 

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

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

Фільтрація по обчислюваному критерію

Інші можливості розширеної фільтрації

В діалоговому вікні Расширенный фильтр представлено ще дві опції, які розглядаються далі:

• Скопировать результат в другое место
• Только уникальные записи.

​Копіювання відібраних рядків

Якщо ви виберете перемикач Скопировать результат в другое место в діалоговому вікні Расширенный фильтр, то відібрані рядки будуть скопійовані в інше місце активного робочого листа або на інший лист. Місце визначається в полі Поместить результат в диапазон. Зверніть увагу, що при використанні цієї опції сам список не фільтрується.

Відображення тільки унікальних рядків

При виборі опції Только уникальные записи всі однакові рядки, що відповідають певному критерію, будуть приховані. Якщо ви не встановили діапазон критеріїв, то в списку не будуть приховані всі однакові рядки.

Обчислення суми даних за допомогою проміжних підсумків та структур

Якщо дані представлені у вигляді списку, то існує можливість підведення в листі проміжних підсумків.

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

Якщо при обчисленні суми даних були використані формули, що містять такі функції, як СУММ, то дані структуруються автоматично.

Якщо замість формул рядки і стовпці підсумкових даних містять значення, то можна створити структуру вручну.

1. Підведення проміжних підсумків.
2. Автоматична структуризація листа.
3. Структуризація листа вручну.

Проміжні підсумки(SUBTOTAL)

Синтаксис

ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер_функции;ссылка1;ссылка2;...)

Номер_функции - це число від 1 до 11, яке вказує, яку функцію треба використовувати при обчисленні підсумків всередині списку (Табл. 4).

Таблиця 4   Функції проміжних підсумків

Номер_функції	Операція
1	СРЗНАЧ
2	СЧЁТ
3	СЧЁТЗ
4	МАКС
5	МИН
6	ПРОИЗВЕД
7	СТАНДОТКЛОН
8	СТАНДОТКЛОНП
9	СУММ
10	ДИСП
11	ДИСПР

Ссылка1; Ссылка2; - від 1 до 29 інтервалів або посилань, для яких підводяться підсумки.

Звичайно простіше створити список з проміжними підсумками, використовуючи в меню Данные→Структура команду Промежуточный итог.

Якщо список з проміжними підсумками вже створений, його можна модифікувати, редагуючи формулу з функцією ПРОМЕЖУТОЧНЫЕ ИТОГИ.

Таблиця 5   Призначення функцій проміжних підсумків

Операція	Результат
Сумма	Сума чисел. Ця операція використовується за замовчуванням для підведення підсумків по числових полях
Количество значений	Кількість записів або рядків даних. Ця операція використовується за замовчуванням для підведення підсумків по нечислових полях
Среднее	Середнє значення чисел
Максимум	Максимальне число
Минимум	Мінімальне число.
Произведение	Добуток чисел
Кол-во чисел	Кількість записів або рядків, що містять числа
Несмещенное отклонение	Незміщена оцінка середнього квадратичного відхилення генеральної сукупності по вибірці даних.
Смещенное отклонение	Зміщена оцінка середнього квадратичного відхилення генеральної сукупності по вибірці даних
Несмещенная дисперсия	Незміщена оцінка дисперсії генеральної сукупності по вибірці даних.
Смещенная дисперсия	Зміщена оцінка дисперсії генеральної сукупності по вибірці даних.

 

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

Щоб підвести підсумки даних списку необхідно:

Діалогове вікно «Промежуточные итоги»

1. Відсортувати список в порядку, згідно якому потрібно підвести підсумки.
2. Виділити діапазон списку, в якому треба підвести підсумки.
3. В меню вибрати пункт Данные→Итоги.
4. В діалоговому вікні «Промежуточные итоги» (Рис.) вибрати стовпець, при зміні даних якого треба підвести підсумки.
5. Вибрати функцію для підведення підсумків.
6. Вибрати стовпці, по яких треба підвести підсумки.
7. Після виконання всіх параметрів натиснути кнопку «ОК»

Видалення підсумків

Щоб видалити підсумкові дані зі списку необхідно:

1. Виділити будь-яку клітину списку.
2. В меню вибрати пункт Данные→Структура→Промежуточный итог.
3. В діалоговому вікні «Промежуточный итог» натиснути кнопку «Убрать все».

Приклад 16

ПРОМЕЖУТОЧНЫЙ ИТОГ(9;C3:C5) підведе підсумки для клітин C3:C5, використовуючи функцію СУММ

Приклад 17

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

Відомість нарахувань заробітної плати

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

1. Відсортувати початкову таблицю  за полями:
   • П.І.П за абеткою
   • Категорія у спадному порядку
2. Видалити рядок 10, інакше дані цього рядка братимуть участь в розрахунках проміжних підсумків
3. Виділити область A10:H17
4. В головному меню вибрати пункт Данные→Структура→Промежуточный итог
5. У вікні «Промежуточный итог» встановити відповідні параметри 

Встановлення параметрів для проміжних підсумків

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

Щоб відобразити який-небудь рівень структури, потрібно клацнути на відповідному символі структури.

Символи структури

Ці символи є кнопками з номерами 1, 2, 3, і т.д. і кнопки зі знаками + або -, розташовані вздовж лівої межі робочого листа (Рисунок 6.13   Таблиця з проміжними підсумками)

 

Кнопка	Дія
1	1-ий рівень (максимальне стиснення)
2, 3 і т.д.	2-ий, 3-ій рівні
+	Розкрити приховані дані
-	Приховати дані

Контролювати процес показу і приховання деталей структури можна також за допомогою меню Данные→Группа и структура→Скрыть детали (Отобразить детали) і т.д.

Таблиця з проміжними підсумками

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

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

Таблиця з проміжними підсумками у формульному вигляді

Структуризація листа вручну

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

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

Наприклад, якщо рядок 6 містить суми для рядків з 3 по 5, то виділіть рядки 3 - 5

Поряд з групою на екрані повинні з'явитися символи структури.

1. Виділіть рядки або стовпці, що містять детальні дані.
2. Виберіть в меню Данные команду Структура, Сгруппировать.
3. Продовжуйте виділення детальних рядків або стовпців і виконуйте команду Сгруппировать до тих пір, поки в структурі не будуть створені всі необхідні рівні.

Примітка.

Структура може мати до 8 рівнів детальних даних, де кожен "внутрішній" рівень надає детальні дані для попереднього "зовнішнього".

Консолідація робочих листів

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

Консолідація даних

Передбачено декілька способів консолідації:

• Консолідація даних з використанням тривимірних посилань
• Консолідація даних по розташуванню
• Використання майстра шаблонів
• Консолідація даних по категорії
• Консолідація даних шляхом створення звіту зведеної таблиці

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

При використанні тривимірних посилань відсутні обмеження на розташування даних в початкових областях.

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

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

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

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

Консолідація даних по розташуванню

1. Вкажіть верхню ліву клітину кінцевої області даних, що консолідуються.
2. В меню Данные виберіть команду Консолидация.
3. Виберіть зі списку Функция, що розкривається, функцію, яку слід використати для обробки даних
4. Введіть в полі Ссылка початкову область даних, що консолідуються.
5. Натисніть кнопку Добавить.
6. Повторіть кроки 4 і 5 для всіх початкових областей, що консолідуються.
7. Щоб автоматично обновляти підсумкову таблицю при зміні джерел даних, встановіть прапорець Создавать связи с исходными данными.

Зв'язки не можна використовувати, якщо початкова область і область призначення знаходяться на одному листі

Після установки зв'язків не можна додавати нові початкові області і змінювати початкові області, що вже входять в консолідацію.

Консолідація даних по категоріях

1. Вкажіть верхню ліву клітину кінцевої області даних, що консолідуються.
2. В меню Данные виберіть команду Консолидация.
3. Виберіть із списку Функция, що розкривається, функцію, яку слід використати для обробки даних.
4. Введіть початкову область даних, що консолідуються в полі Ссылка. Переконайтесь, що початкова область має заголовок.
5. Натисніть кнопку Добавить.
6. Повторіть кроки 4 і 5 для всіх початкових областей, що консолідуються.
7. В наборі прапорців Использовать в качестве имен встановіть прапорці, що відповідають розташуванню в початковій області заголовків: у верхньому рядку, у лівому стовпці або у верхньому рядку і в лівому стовпці одночасно.
8. Щоб автоматично обновляти підсумкову таблицю при зміні джерел даних, встановіть прапорець Создавать связи с исходными данными.

Зв'язки не можна використовувати, якщо початкова область і область призначення знаходяться на одному листі. Після установки зв'язків не можна додавати нові початкові області і змінювати початкові області, що вже входять в консолідацію.

 

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

Якщо звіт створюється на основі списку Microsoft Excel або бази даних, клацніть на клітині в цьому списку або базі даних.

1. Відкрийте книгу, в якій вимагається створити звіт зведеної діаграми.
2. Виберіть в меню Вставка команду Сводная таблица.
3. На кроці 1 виконання майстра зведених таблиць і діаграм встановіть перемикач Вид создаваемого отчета в положення Сводная таблица.
4. Слідуйте інструкціям на кроці 2 майстра.
5. На кроці 3 потрібно вирішити, чи треба натискати кнопку Макет.
6. Виконайте одну з наступних дій:

Якщо на кроці 3 була натиснута кнопка Макет, виконайте формування макету звіту, натисніть кнопку OK в діалоговому вікні Мастер сводных таблиц и диаграмм - Макет, а потім кнопку Готово для створення звіту.

Якщо кнопка Макет на кроці 3 не була натиснута, натисніть кнопку Готово, а потім сформуйте макет звіту на листі.

Зміна підсумкової таблиці консолідації даних

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

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

Дії, що передбачаються

• Додавання області даних в підсумкову таблицю
• Зміна області даних в підсумковій таблиці
• Видалення області даних з підсумкової таблиці
• Створення зв'язків підсумкової таблиці з початковими даними для її автоматичного оновлення

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

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

1. В меню Данные виберіть команду Консолидация.
2. Встановіть прапорець Создавать связи с исходными данными.

Консолідація даних по розташуванню без оновлення зв'язків (статична)

Приклад.

Є дані по заробітній платні по місяцях Січень, Лютий, Березень.

Необхідно підвести підсумки за квартал по кожному працівнику окремо.

Порядок виконання консолідації даних без оновлення зв'язків 

1. На окремий лист скопіювати таблицю.
2. Очистити клітини з даними, які треба консолідувати (E11:H18).
3. Виділити діапазон, по якому треба виконувати консолідацію (E11:H18).
4. Вибрати команду меню Данные → Консолидация
5. Вибрати у вікні «Консолидация» зі списку Функция, що розкривається, функцію СУММА, яку слід використати для обробки даних.
6. Введіть початкову область даних, що консолідуються, в полі Ссылка
7. Натисніть кнопку «Добавить»
8. Повторіть кроки 5 і 6 для всіх початкових областей, що консолідуються.
9. В наборі прапорців Использовать в качестве имен не встановлювати прапорці, що відповідають розташуванню в початковій області заголовків: у верхньому рядку, у лівому стовпці або у верхньому рядку і в лівому стовпці одночасно .

Установка параметрів консолідації без оновлення зв'язків.

Результат консолідації без оновлення зв'язків (статична)

Приклад.

Консолідація даних по розташуванню з оновленням зв'язків(динамічна)

Порядок виконання консолідації.

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

1. На окремий лист скопіювати початкову таблицю
2. Очистити клітини з даними, по яких треба виконувати консолідацію (E11:H18).
3. Виділити діапазон, по якому треба виконувати консолідацію (E11:H18)
4. Вибрати команду Данные → Консолидация
5. Вибрати зі списку Функция, що розкривається, функцію СУММА, яку слід використати для обробки даних.
6. Введіть початкову область даних, що консолідуються, в полі Ссылка
7. Натисніть кнопку «Добавить»
8. Повторіть кроки 5 і 6 для всіх початкових областей, що консолідуються.
9. Щоб автоматично обновляти підсумкову таблицю при зміні джерел даних, встановити прапорець Создавать связи с исходными данными.

Установка параметрів консолідації з оновленням зв'язків

Результат консолідації з оновленням зв'язків (динамічна)

Видалення структури повністю

При видаленні структури дані в листі не змінюються.

1. Вкажіть будь-яку клітину на листі.
2. Виберіть команду Данные →Структура→Удалить структуру.

Побудова діаграм

Створення діаграм за допомогою Майстра діаграм

Створення будь-яких діаграм починається з:

• Виділення діапазону даних, які відображаються на діаграмі (не обов'язково)
• Активізація Майстра діаграм

Етапи створення діаграм:

1. Вибір діапазону даних, на підставі яких буде побудована діаграма
2. Вибір типу діаграми 
3. Вибір макету діаграми та введення заголовків та її осей, встановлення будь-яких параметрів діаграми, пов'язаних з її зовнішнім оформленням
4. Вибір варіанту розміщення діаграми (на поточному або на окремому листі) 

Область діаграми	Область, що займається самою діаграмою. Можна змінювати заливку, шрифт, рамку
Ряд даних	Один з наборів даних, що відображаються на діаграмі одним конкретним кольором, описаним в легенді. Можливо проводити зміни порядку рядів, колір заливки і т.д.
Область побудови діаграми	Область, в якій власне відображається співвідношення між чисельними значеннями. Можна змінювати рамку, заливку
Лінії сітки	Прямі лінії в області діаграми
Вісь значень	Вісь У.
Вісь категорій	Вісь Х
Легенда	Показує яким кольором який ряд даних виводиться. Можливі зміни параметрів шрифту, кольору, розташування легенди

Для того, щоб з усвідомленням справи вибирати параметри діаграми корисно ознайомитися з термінами, які використовує Майстер для позначень елементів плоскої та об'ємної діаграми на прикладі таблиці «На рыбалке».

На рыбалке
	ПН	ВТ	СР	ЧТ	ПТ	СБ	ВС	Всего
Щукин	4	6	10	5	0	2	6	33
Сомов	6	7	14	6	2	5	7	47
Окунев	6	9	18	9	3	4	5	54
Карасев	8	12	20	10	5	5	8	68
					Итого			202

Вибір діаграми

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

Таблиця6   Типи діаграм

Зразок вигляду	Тип діаграми	Опис
	Гистограмма	Тип, що використовується за замовчуванням. Показує зміну даних за певний період часу та ілюструє співвідношення окремих значень даних. Категорії розташовуються по горизонталі, а значення - по вертикалі. Завдяки цьому більша увага надається змінам в часі. Підтипи: обычная, гистограмма с накоплением, нормированная на 100%, их объемные варианты
	Линейчатая	Нагадує гістограму, в якій категорії розташовуються по вертикалі, а значення - по горизонталі. Завдяки цьому надається більша увага зіставленню значень, і менше - змінам в часі. Підтипи: обычная, линейчатая диаграмма с накоплением, нормированная на 100%, их объемные варианты
	График	Відображає тенденції зміни даних за певні проміжки часу
	Круговая	Показує не стільки абсолютну величину кожного елемента ряду даних, скільки його внесок в загальну суму. На круговій діаграмі може бути представлений тільки один ряд даних
	Точечная	Відображає взаємозв'язок між значеннями декількох рядів чисел. Часто використовується для представлення даних наукового характеру. При підготовці даних слід розташувати в одному рядку (або стовпчику) всі значення змінної х, а відповідні значення у - в суміжних рядках (або стовпцях)
	С областями	Підкреслює величину зміни протягом певного періоду часу, показуючи суму введених значень. Також відображає внесок окремих значень в загальну суму
	Кольцевая	Показує внесок кожного елемента в загальну суму, але, на відміну від кругової діаграми, може містити декілька рядів даних. Кожне кільце представляє окремий ряд даних

 

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

по «Нараховано» «Премія», «Всього нараховано»

Етапи:

курсор в B9 Виділити діапазон B9:B18

або SHIFT+ діапазон (для несуміжних областей)

1. Приховати рядок 10.
2. Виділити діапазон:
3. CTRL + діапазон F9:H18 (для суміжних областей)
4. Вибрати тип графіка:

Крок 1 Вибір типу графіка: Графік - График с маркерами

Крок 3-1 Введення заголовків

Крок 3-2 Установка осей

 

Крок 3-3 Установка Лінії сітки

 

Крок 3-6 Установка таблиці (необов'язково - вибрати з Макету діаграм)

Крок 4 Розміщення графіка - на панелі Работа с диаграммами - натиснути кнопку Переместить диаграмму

 

Результат

Побудова кругової діаграми

Побудувати кругову діаграму процентного змісту «Всього нараховано» по співробітниках.

Етапи:

1. Приховати рядок 10.
2. Виділити діапазон: B9:B18; H9:H18
3. Вибрати тип діаграми круговая
4. Вибрати у вікні «Параметры диаграммы» в закладці «Подписи данных» Доля і т.д.

Побудова графіка залежності

Побудувати графік залежності «Премія» від «Відроблено днів».

Етапи:

1. Приховати рядок 10.
2. Виділити діапазон: G9:G18
3. Вибрати тип діаграми Графік - График с маркерами
4. Вибрати у вікні «Исходные данные» в закладці «Ряд» в полі «Подписи оси Х» виділити діапазон стовпця «Відроблено днів» $E$11:$E$18.
5. У вікні «Параметры диаграммы» встановити заголовки та інші параметри на свій розсуд

 

Друк документів Excel 

Підготовка до друку

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

Перегляд листа перед друком. Microsoft Excel надає три способи перегляду і настройки зовнішнього вигляду листа перед друком.

• Обычный режим. Використовується за замовчуванням. Найкращий режим для перегляду роботи на екрані.
• Предварительный просмотр. Показує сторінку для друку і дозволяє легко настроювати стовпці та поля.
• Режим разметки страницы. Показує розподіл даних по сторінках і дозволяє швидко настроювати область друку і розриви сторінок.

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

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

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

Вибрати пункт меню ФАЙЛ → Параметры страницы → Лист→ активізувати пункт Заголовки строк и столбцов.

Настройки друку таблиці з заголовками рядків і стовпців.

Виконання настройок розташування листа при підготовці до друку виконується в меню ФАЙЛ → Параметры страницы в закладках:

 

Страница	Поля
Колонтитулы	Лист

Страница

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

Поля

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

Колонтитули

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

Лист

Настройка друку сітки, адресних смуг (заголовків) і т. д..

Попередній перегляд

Перед друком робочого листа слід перейти в режим попереднього перегляду (кнопка Предварительный просмотр на стандартній панелі інструментів). Режим попереднього перегляду не допускає редагування документу, але дозволяє побачити його на екрані точно у такому вигляді, в якому він буде і дозволяє змінити відступи (поля) від країв сторінки.

Друк документу

Щоб налаштувати принтер і вивести на друк документ, окремі сторінки, треба вибрати:

- меню ФАЙЛ → Печать