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

Бібліотека Excel містить тринадцять вбудованих функцій списків (баз даних), що дозволяють отримати інформацію зі списку або зробити в ньому необхідні обчислення. При цьому деякі із цих функцій відповідають уже відомим Excel- Функціям таблиці (наприклад, БДСУММ (Список; Поле; Критерий поиска), БСЧЕТ (Список; Поле; Критерий поиски), ДМАКС (Список; Поле; Критерий поиска) і ін.), але є більше гнучкими, тому що в них можна вказувати певні критерії.

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

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

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

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

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

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

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

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

2.Якщо значення розташовані в стовпчику, то введіть формулу в чарунку, розташовану на один рядок вище й на одну чарунку правіше першого значення. Правіше першої формули введіть інші формули (табл.1). Якщо значення розташовані в рядку, то введіть формулу в чарунку, розташовану на один стовпчик лівіше й на один рядок нижче першого значення. У тім же стовпчику, але нижче наберіть інші формули (табл.2).

Таблиця 1

	Формула 1	Формула 2	...
Значення 1	Область результатів підстановки
Значення 2
...
Значення N

 

 

Таблиця2

	Значення 1	Значення 2	...
Формула 1	Область результатів підстановки
Формула 2
...

 

3. Виділите діапазон чарунок, що містять формули й значення підстановки.

4. Виберіть команду Данные/Таблица подстановки.

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

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

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

Зведені таблиці

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

Створення зведених таблиць здійснюється за допомогою Мастера сводных таблиц.

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

1. Встановите курсор у будь-яку чарунку списку й виберіть команду Данные/Сводная таблица.

У діалоговому вікні, що відкрилося, Мастер сводных таблиц відзначте опцію в списку або базі даних Microsoft Excel.

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

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

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

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

Використовуючи панель інструментів Сводные таблицы можна змінювати вид зведеної таблиці.

Вставка інформації в список Excel з іншої програми

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

Такі перетворення істотно полегшує Мастер текста Excel:

1. Використовуючи буфер обміну скопіюйте з документа Word потрібний текст і вставте його на робочий лист Excel.

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

3. Виберіть команду Данные/Текст по столбцам.

4. Після вибору формату даних у вікні, що з'явилося, Мастер текстов (наприклад, опції з роздільниками) клацніть на кнопці Далее й задайте вид роздільника (наприклад, <3апятая > і <Пробел>).

5. На наступному кроці найкраще встановити опцію Загальний (у цьому випадку числа будуть відображатися як числа, дати як дати, а текст як текст).

6. Клацніть на кнопці Готово.

7. Додайте заголовний рядок і виконайте необхідні операції форматування.

Хід роботи

ЗАВДАННЯ 1

1. Створіть наступну таблицю:

	A	B	C	D
	Дата	Витрата	Сума	Одержувач
	01.06.99	Накладні витрати		ЗАТ БИН
	02.06.99	Накладні витрати		ТОО Надія
	04.06.99	Матеріали	16 000	АТ Престиж
	05.06.99	Зарплата	2 000	Васильєва М.Ф.
	05.06.99	Зарплата	2 540	Козаків С.С.
	05.06.99	Зарплата		Іванов И.И.
	30.06.99	Накладні витрати	1 000	АТ ИНВЕСТ
	04.07.99	Накладні витрати		ЗАТ БИН
	04.07.99	Накладні витрати		ТОО Надія
	04.07.99	Матеріали	13 200	АТ Оргсинтез
	05.07.99	Зарплата	2 000	Васильєва М.Ф.
	05.07.99	Зарплата	2 540	Козаків С.С.
	05.07.99	Зарплата	1 890	Іванов И.И.
	31.07.99	Накладні витрати	1 000	АТ ИНВЕСТ
	04.08.99	Накладні витрати		ЗАТ БИН
	05.08.99	Зарплата	2 000	Васильєва М.Ф.
	05.08.99	Зарплата	2 540	Козаків С.С.
	05.08.99	Зарплата	1 890	Іванов И.И.
	04.09.99	Накладні витрати		ОО Надія
	05.09.99	Зарплата	2 000	Васильєва М.Ф.
	05.09.99	Зарплата	2 540	Козаків С.С.
	05.09.99	Зарплата	1 890	Іванов И.И.

2. Використовуючи форму даних, додайте в список дані об АТ Престиж: «30.06.97, Матеріали, $800, АТ Престиж».

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

4. Використовуючи форму даних, перегляньте всі дані списку про витрати на матеріали, що перевищують 12000.