Робота зі списками

Для роботи з даними також використовують списки — сукупність даних, які мають упорядковану структуру (однакову кількість стовпців і типів даних у кожному стовпці). Обов'язковим елементом списків є наявність заголовків стовпців.

Фільтрування даних за допомогою команди Автофильтр

За допомогою команди Автофильтр здійснюють по­шук та виведення на екран тільки тих даних, які задовольняють поставлену умову. З цією метою курсор вводять у список та активізують команди Данные, Фильтр, Артофильтр, унаслідок чого у верхній частині кожного стовпця з'являються кнопки списків.

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

Наприклад, потрібно працювати тільки з тими за­писами списку, в яких термін кредиту перевищує 5 років (60 місяців). Розкривши список поля «Термін кре­диту», активізують команду Условие. Після цього у вікні Пользовательский автофильтр (рис. 5.26) зазна­чають необхідну умову (больше 60) й активізують кнопку ОК.

Унаслідок цих дій на екрані дисплея з являється список у відфільтрованому вигляді (рис. 5.27).

З відібраними за певною умовою значеннями можна проводити розрахунки. На рис. 5.27 показано розрахунок суми даних поля «Сума кредиту» тільки стосовно тих записів, де термін кредиту перевищує 60 місяців. На панелі формул розташована відповідна функція, що з'являється на екрані дисплея після активізації кнопки Автосумма.

Щоб скасувати дії команди Автофильтр, слід знову активізувати команди Данные, Фильтр, Автофильтр після чого в таблиці з'являться всі значення, які були приховані після фільтрування даних.

Фільтрування даних за допомогою команди Расширенный фильтр

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

Нехай, наприклад, треба створити список, який міс­тить тільки записи банку «Укрсоцбанк».

Для цього спочатку створюють критерій пошуку да­них, який розміщується поза значеннями списку і від­окремлюється від нього хоча б одним вільним рядком. Критерій складається з назв полів та умови, записаної під відповідною назвою поля. Щоб його створити, копію­ють назви полів у вільний рядок (наприклад, 11) і під назвою «Банк» створюють умову («Укрсоцбанк»). Далі вводять курсор у таблицю й активізують команди Дан­ные, Фильтр, Расширенный фильтр, унаслідок чого на екрані з'являється вікно, в якому зазначають парамет­ри (рис. 5.28):

• активізують параметр скопировать результат в другое место;

• у полі Исходный диапазон: зазначають діапазон значень усього списку, включаючи назви полів (A2:G9), для цього натискають на клавішу миші й проводять по діапазону);

• у полі Диапазон условий: зазначають діапазон ко­мірок, у яких створено критерій (A11:G12);

• у полі Поместить результат в диапазон: зазнача­ють адресу вільної комірки (А14).

Після цього активізують кнопку ОК.

Починаючи із зазначеної комірки (в наведеному прикладі А14), з'являється новий список (рис. 5.29), значений якого відповідають поставленій умові (банк «Укрсоцбанк»).

Створення значень списку

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

Нехай, наприклад, треба відсортувати список за двома ключами:

• перший ключ (поле «Банк») — за зростанням;

• другий ключ (поле «Термін кредиту») — за зростанням.

Для виконання цієї операції курсор установлюють у будь-яку комірку таблиці й активізують команди Даные, Сортировка. У результаті на екрані дисплея з'являється вікно, зображене на рис. 5.30.

У вікні Сортировка диапазона в списку поля Сортировать по вибирають перший ключ сортування (поле «Банк») та зазначають вид сортування (по возрастанию); аналогічно встановлюють параметри другого ключа у полі Затем по. При активізованому параметрі подписям (І) заголовки полів не входять у діапазон cop- тування. Після цього активізують кнопку ОК.

У результаті сортування список буде таким, як на рис. 5.31, де значення другого ключа («Термін кредиту») впорядковані лише в межах групи однакових значень першого ключа «Банк».

Розрахунок проміжних підсумків

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

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

Наприклад, треба знайти мінімальне та максимальне значення суми кредиту для кожного банку. З цією метою спочатку слід упорядкувати дані в полі з назвами банків (команди Данные, Сортировка, Сортировать по — поле «Банк»). Далі, ввівши курсор у базу даних, активізують команди Данные, Итоги, внаслідок чого на екрані дисп­лея з'являється вікно (див. рис. 5.32), в якому визнача­ють параметри:

• При каждом изменении в: у списку вибирають по­ле, що має дані, які повторюються і заздалегідь відсор­товані (поле «Банк»);

• Операция: у списку вибирають відповідну функ­цію для розрахунку (функція Минимум);

• Добавить итоги по: активізують перемикачі ліво­руч від тих полів, значення яких необхідно розрахувати (поле «Сума кредиту»);

• натискають на кнопку ОК.

Для розрахунку другої функції (максимальне зна­чення суми кредиту) знову активізують команди Дан­ные, Итоги. В екранному вікні зазначають відповідні ^Параметри, при цьому перемикач Заменить текущие итоги (1)має бути неактивним, і натискають на кнопку ОК. Унаслідок цих операцій список даних матиме виглядзображений на рис. 5.33.

На цьому рисунку ліворуч міститься структура вкладеності рівнів даних. Кнопки (1)дають змогу згортати (розгортати) дані одного рівня, а кнопки (2) — дані кожного розрахованого підсумку.

Для вилучення розрахованих проміжних підсумків слід установити курсор в БД й активізувати команди Данные, Итоги та кнопку Убрать все.

Функції БД

Функції БД використовують для розрахунку значень, що задовольняють певну умову. Існує дванадцять таких функцій, зокрема БДСУММ, БСЧЕТ, ДСРЗНАЧ, ДМАКС тощо. Всі вони мають три аргументи (рис. 5.34):

• База_данных, у якій зазначають весь діапазон БД або списку, включаючи і назви полів;

• Поле — стовпець, зі значеннями якого проводять розрахунок. Його позначають за допомогою порядкового номера (1, 2,...), назви поля («Сума кредиту») або адреси комірки, де зберігається назва поля (В2);

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

Якщо, наприклад, треба знайти максимальне значення стовпця «Сума кредиту» для записів банку «Аваль», то необхідно послідовно виконати такі дії:

• створити критерій, виділивши рядок із назвами стовпців (рядок 2), скопіювати його під таблицею з да­ними у вільний рядок (рядок 11), а під назвою стовпця («Банк») створити умову для пошуку відповідних зна­чень («Аваль») — комірка А12;

• встановити курсор у вільну комірку (А15), активі­зувати кнопку Вставка функции, з переліку категорій вибрати Работа с базой данных, функцію ДМАКС й ак­тивізувати кнопку ОК;

• унаступному вікні Аргументи функции (рис. 5.34) зазначити такі параметри:

— База_данных (діапазон A2:G9);

— Поле (комірку В2, де вміщено назву стовпця, для якого виконується розрахунок);

— Критерий (діапазон комірок, у якому створено критерій, тобто All:А12);

—увівши всі значення, активізують кнопку ОК.

У вікні відразу можна побачити розраховане значен­ня (1 на рис. 5.34).

Після цього в комірці А15 з'являється розраховане значення (15000), а на панелі формул — створена функція (=AMAKC(A2:G9;B2;A11:A12).

Аналогічно працюють з іншими функціями БД.

Поиск по сайту: