8.1.6.5. Фільтрація даних у списку

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

В Excel для фільтрації даних використовуються команди Автофильтр і Расширенный фильтр. У випадку простих критеріїв для вибірки потрібної інформації досить команди Автофильтр у комірках виділених заголовків повинні з’явитися нові елементи: . З їх допомогою можна встановлювати критерії фільтрації даних.

Рис. 8.30. Вибір критерію

Рис. 8.31. Умови критерію

Під час використання складних критеріїв варто застосовувати команду Расширенный фильтр.

Обидві команди викликаються командою Данные/Фильтр.

8.1.6.6. Підбиття підсумків у базі даних

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

Після виконання команди Данные/Итоги ви можете виконати такі операції:

• вибрати одну чи кілька груп для автоматичного підбиття підсумків щодо цих груп;

• вибрати функцію для підбиття підсумків;

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

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

• виводити один рядок підсумків щодо кількох стовпців;

• виводити багаторівневі, вкладені рядки підсумків щодо кількох стовпців;

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

• ховати чи показувати детальні дані в цьому списку

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

Синтаксис: ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер_функції; посилання)

Номер_функції — це число від 1 до 11, що вказує, яку функцію використовувати під час обчислення підсумків усередині списку.

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

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

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

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

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

Спочатку заповнюються поля, що містять вихідні дані: «Найменування», «Фірма-постачальник», «Ціна (грн)» і «Кількість». Поля, що обчислюються, таблиці заповнюються формулами. Для створення формули в полі «Ціна ($)» використовується ім’я, присвоєне комірці, що містить числове значення поточного курсу валюти. Для створення формул у полі «Сума реалізації» використовується часткова заборона модифікації адреси комірок (за допомогою значка $ перед номером рядка), що містять роздрібну ціну для кожного з товарів.

Проміжні підсумки заповнюються через меню Данные/Ито­ги.../Промежуточные итоги:

Рис. 8.32. Виведення проміжних підсумків

Попередньо необхідно виділити ділянку таблиці, в якій ви збираєтеся підбивати підсумки, включивши в неї рядок з назвами полів. У наведеному вище прикладі — це заголовок таблиці («№ п/п»; «Найменування»; ... «Валовий дохід») і всі записи щодо товарів (запис — це один рядок у таблиці, що починається з порядкового номера запису і закінчується цифрою валового доходу).

1. У списку При каждом изменении в: установити значення «Найменування».

2. У списку Операція встановити значення Сума.

3. У списку Добавить итоги по: відзначити елементи: «Сума реалізації», «Сума закупівлі», «Кількість», «Валовий дохід».

4. Звести прапорці: Заменить поточные итоги і Итоги под данными.

5. Такі формули мають бути записані в комірки таблиці (табл. 8.5).

Такий вигляд матиме результат обчислення формул (табл. 8.6):

Таблиця 8.5

	A	B	C	D	E	F	G	H	I
5	№ п/п	Найменування	Фірма- постачальник	Ціна, грн	Ціна, $	В	Сума закупівлі	Сума реалізації	Валовий дохід
6	1	Банани	Frutis	2	=D6/kurs	200	=D6*F6	=F6*I$2	=H6-G6
7	2	Банани	SUMP	2.32	=D7/kurs	80	=D7*F7	=F7*I$2	=H7-G7
8	3	Банани	Forum	1.98	=D8/kurs	165	=D8*F8	=F8*I$2	=H8-G8
9	4	Банани	Like	1.86	=D9/kurs	320	=D9*F9	=F9*I$2	=H9-G9
10	5	Банани	UFO	2.05	=D10/kurs	120	=D10*F10	=F10*I$2	=H10-G10
11			середн.:	=ПРОМІЖНІ.ПІДСУМКИ (1;D6:D10)	Разом:	=ПРОМІЖНІ.ПІДСУМКИ (9;F6:F10)	=ПРОМІЖНІ.ПІДСУМКИ (9;G6:G10)	=ПРОМІЖНІ. ПІДСУМКИ (9;H6: H10)	=ПРОМІЖНІ. ПІДСУМКИ (9;I6: I10)
12	6	Виноград	Frutis	4	=D12/kurs	90	=D12*F12	=F12*I$3	=H12-G12
25		Підсумок:			Підсумок:	=ПРОМІЖНІ.ПІДСУМКИ (9;F6:F22)	=ПРОМІЖНІ.ПІДСУМКИ (9;G6:G22)	=ПРОМІЖНІ. ПІДСУМКИ (9;H6:H22)	=ПРОМІЖНІ. ПІДСУМКИ (9;I6:I22)

Таблиця 8.6

	Сьогодні курс $=		1.3				Роздрібна ціна
							Банани	2.90 грн
	Розрахунок валового доходу від реалізації товарів						Виноград	5.20 грн
							Ананас	6.80 грн
№ п/п	Наймену- вання	Фірма постачальник	Ціна, грн	Ціна, $	Кількість	Сума закупівлі	Сума реалізації	Валовий дохід
1	Банани	Frutis	2.00 грн	$ 1.54	200	400.00 грн	580.00 грн	180.00 грн
2	Банани	SUMP	2.32 грн	$ 1.78	80	185.60 грн	232.00 грн	46.40 грн
3	Банани	Forum	1.98 грн	$ 1.52	165	326.70 грн	478.50 грн	151.80 грн
4	Банани	Like	1.86 грн	$ 1.43	320	595.20 грн	928.00 грн	332.80 грн
5	Банани	UFO	2.05 грн	$ 1.58	120	246.00 грн	348.00 грн	102.00 грн
Банани		Середн:	2.04 грн	Підсумок:	885	1 753.50 грн	2 566.50 грн	813.00 грн
24	Ананаси	Like	5.99 грн	$ 4.61	60	359.40 грн	408.00 грн	48.60 грн
25	Ананаси	UFO	6.60 грн	$ 5.08	55	363.00 грн	374.00 грн	11.00 грн
Ананаси		Середн:	6.04 грн	Підсумок:	315	1 861.55 грн	2 142.00 грн	280.45 грн
	Разом:			Підсумок:	1 517	4 865.02 грн	6 356.90 грн	1 491.88 грн