Сортування списків і діапазонів

Бази даних і списки

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

Крім перелічених можливостей Excel підтримує обмін даними (імпорт та експорт даних) із системами керування базами даних, дозволяючи прочитати або зберегти дані у традиційних форматах баз даних. Крім того до складу пакета Excel входить прикладна програма MS-Query. З її допомогою можна опрацьовувати дані (бази даних), збережені програмами керування базами даних, будувати запити до цих баз, зробити дані доступними для опрацювання засобами Excel. При цьому вхідні дані можуть бути подані у форматах dBase або Paradox, Microsoft Access, FoxPro, а також у будь-яких інших форматах, доступ до яких можливий через інтерфейс ODBC (Open Data Base Connectivity).

Що стосується термінів база даних і список, будемо дотримуватися прийнятої у Microsoft угоди та називати таблиці у документах Excel списками. Базами даних будемо називати файли даних, створені іншими системами обробки даних (системами керування базами даних).

Створення списку

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

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

Діапазону клітинок, що містить список, можна дати ім'я. Присвоєння імені дозволить простіше переходити до списку у робочій книзі. Якщо діапазону клітинок списку присвоєно ім’я «База_данных», то Excel інтерпретує вміст кожної клітинки першого рядка, як ім’я окремого поля. Щоб виділити рядок з іменами полів використовуйте шрифти та рамки. Не варто вставляти окремий рядок із дефісів або знаків рівності, тому що Excel може сприйняти таке “підкреслення” за дані.

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

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

Форма даних

Хоча нові дані можна добавляти безпосередньо у кінець списку, для уведення та зміни окремих записів Excel надає у допомогу користувачу стандартну екранну форму даних. Для виводу на екран вікна форми даних помістіть покажчик клітинки в область списку та виконайте команду «Данные\Форма…». Нижче наведений приклад такої форми для списку з попереднього рисунка.

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

У рядку заголовка форми виводиться ім'я робочого листа, що містить список, який редагується. У лівій частині форми розташовуються заголовки стовпчиків (імена полів) і поля уведення відповідних значень. Обчислювальні та захищені поля даних змінити не можна. Ці поля відображаються разом із відповідними значеннями, але поля уведення для них відсутні (у нашому прикладі вік студента розраховується за формулою, тому для поля «Вік» просто виводиться результат розрахунку). Ширина та висота вікна форми визначається кількістю стовпчиків і максимальними розмірами імен полів і ширини стовпчиків.

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

Натиснувши певні клавіші, можна швидко “пролистати” записи у списку:

· Натискання клавіші <Enter> призводить до відображення наступного запису. Натискання сполучення клавіш <Shift+Enter> — до попереднього запису. Записи також можна “перегортати” клавішами <¯> та <­>.

· Натискання клавіш <PageUp> та <PageDown> дозволяє перейти до запису, розташованого на 10 записів вище або, відповідно, нижче поточного.

· Натискання сполучень клавіш <Ctrl+PageUp> та <Ctrl+PageDown> використовується для переходу до першого або, нового, розташованого за останнім, запису списку.

· Клавіші <Tab> та <Shift+Tab> можна використовувати для переходу до наступного або, відповідно, до попереднього поля усередині запису.

· Крім того, як звичайно, можна користуватися смугою прокрутки для “листання” записів і покажчиком миші для переміщення між полями усередині запису.

Для керування вікном форми даних у розпорядженні користувача є 7 кнопок:

· Кнопка «Добавить» дозволяє ввести новий запис.

· Кнопка «Удалить» дозволяє видалити поточний запис. Розташовані нижче записи перемістяться угору. Виконання цієї команди скасувати не можна, і на екран буде виведене попереджувальне повідомлення.

· Кнопка «Вернуть» дозволяє скасувати виконані зміни та повернутися до початкового вигляду запису. Ця кнопка доступна тільки у тому випадку, якщо після внесення змін ви ще не перейшли до іншого запису.

· Кнопки «Назад» та «Далее» дозволяють перейти до попереднього або наступного запису списку. При цьому враховуються задані критерії відбору записів.

· Кнопка «Критерии» дозволяє відобразити на екрані порожню форму даних і ввести критерії для відбору записів списку. При указанні критеріїв можна використовувати символи шаблона «*» — для заміни довільного числа символів і «?» — для заміни одного символу) та традиційні відношення порівнянь (=, <, >, <>, <=, >=). Наприклад, для пошуку студентів, прізвища яких починаються на літеру Б, як критерій у полі «Прізвище» можна ввести «Б*». Кнопки «Далее» та «Назад» будуть здійснювати пошук потрібних записів з урахуванням заданого критерію. Кнопка «Критерии»дозволить у разі потреби змінити критерій. Для видалення заданих критеріїв і відображення всіх записів використовується кнопка «Очистить».

· Кнопка «Закрыть» дозволяє закрити вікно форми даних.

У вікні форми даних можна представити до 32 полів.

Сортування списків і діапазонів

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

Для сортування списку спочатку розташуйте покажчик клітинки усередині списку та виконайте команду «Данные\Сортировка…». Excel автоматично визначить розмір списку, рядок заголовків (який не треба сортувати) та відкриє вікно діалогу «Сортировка диапазона».

Використовуючи списки полів «Сортировать по» та «Затем по» виберіть поля, по яким буде здійснюватися сортування, з урахуванням їхніх взаємних пріоритетів. Встановіть для кожного поля сортування необхідний перемикач «по возрастанию» або «по убыванию». Перевірте правильність установки перемикача у розділі «Идентифицировать поля по». Кнопка «OK» дає вказівку до виконання сортування.

Якщо результат сортування списку вас не влаштовує, його можна скасувати, виконавши, наприклад, команду «Правка\Отменить Сортировка» (<Ctrl+Z>). Якщо після різноманітних складних сортувань потрібно швидко відновлювати початковий порядок сортування рядків у списку, можна до виконання сортування створити стовпчик із номерами рядків і включити його у список. Тепер, щоб відновити початковий порядок рядків, достатньо відсортувати список по цьому стовпчику.

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

Якщо список потрібно відсортувати тільки по одному полю, то порядок сортування можна задати, скориставшись кнопками — «Сортировка по возрастанию» та — «Сортировка по убыванию» панелі інструментів «Стандартная».

Для сортування не всього, а тільки частини списку, необхідно виділити потрібний діапазон клітинок і виконати розглянуту вище команду «Данные\
Сортировка…». Будьте уважні, бо якщо виділені не всі стовпчики списку, то переміщення торкнуться рядків тільки у деяких стовпчиках, в інших стовпчиках переміщень не буде, тобто записи (внутрішні зв'язки інформації) будуть зруйновані та список зіпсується.

Можна також задати сортування стовпчиків діапазону замість рядків. Для цього натисніть у вікні діалогу «Сортировка диапазона» (див. рисунок вище) кнопку «Параметры…» та у вікні діалогу «Параметры сортировки» у групі «Сортировать» поставте перемикач у пункті «столбцы диапазона». При сортуванні стовпчиків заголовки рядків не враховуються, тому спочатку варто виділити дані, що сортуються.

Як уже відмічалося, можливості сортування не обмежуються стандартним впорядкуванням по зростанню або убуванню. При сортуванні можна враховувати порядок, заданий у власному створеному списку (створення таких списків розглядалося раніше). Прикладом можуть служити дні тижня та місяці року, що у Excel уже визначені як послідовності сортування. Власний список, що задає порядок сортування, можна вибрати у вікні діалогу «Параметры сортировки» у полі «Сортировка по первому ключу». Власний порядок сортування можна застосовувати тільки для сортування по першому ключу (по головному критерію). Використання власного порядку сортування для поля, зазначеного у групі «Затем по», неможливе.

При сортуванні списків звертайте увагу на клітинки з формулами. Після сортування по рядкам “горизонтальні” посилання у межах одного рядка залишаться правильними, тоді як “перехресні” посилання на клітинки в інших рядках можливо стануть хибними. Аналогічно, після сортування по стовпчикам “вертикальні” посилання у межах одного стовпчика залишаться вірними, а посилання на клітинки в інших стовпчиках можливо стануть хибними. Щоб уникнути проблем із сортуванням списків і діапазонів, що містять формули, додержуйтесь наступних правил:

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

· При сортуванні по рядкам не застосовуйте формули з посиланнями на клітинки в інших рядках.

· При сортуванні по стовпчикам не застосовуйте формули з посиланнями на клітинки в інших стовпчиках.

Фільтрація даних

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

Автофільтр

Можливостями автофільтра можна скористатися, помістивши покажчик клітинки усередині списку та виконавши команду «Данные\Фильтр\Автофильтр». Справа від кожного заголовка стовпчика з'явиться кнопка зі стрілкою униз, натиснувши на яку, можна відкрити список значень даного поля. Після вибору зі списку потрібного елемента на екрані будуть відображені тільки ті записи, що відповідають заданому фільтру. При цьому у рядку стану з'явиться повідомлення про те, скільки записів, що відповідають заданому критерію, знайдено у списку: .

Запропоновані для вибору списки значень полів можуть бути достатньо довгими. Для швидкого переходу до певного елемента списку введіть початкову літеру. Елемент «(Все)» дозволяє зняти обмеження по даному полю. За допомогою елемента «(Первые 10…)» користувач може відібрати певну кількість найбільших або найменших значень розглянутого списку. При виборі вказаного елемента відкривається наступне вікно діалогу «Наложение условия по списку».

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

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

При визначенні критеріїв по текстових полях можна використовувати не тільки оператори порівняння, але і символи шаблона: «*» — для представлення будь-якої послідовності символів та «?» — для представлення будь-якого окремого символу.

Відбір записів можна продовжити, задаючи критерії в інших стовпчиках (по іншим полям). Кожний доданий критерій зв'язується з попереднім логічним оператором «И».

Якщо перед виконанням команди «Данные\Фильтр\Автофильтр»виділитичастину списку, то кнопки зі списками, що розкриваються, будуть додані тільки по відповідних полях (включених до області виділення).

Щоб знову відобразити на екрані всі записи списку, виконайте команду «Данные\Фильтр\Отобразить все». Для деактивації автофільтра повторно скористайтеся командою «Данные\Фильтр\Автофильтр».

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

Команди «Копировать» та «Вставить» із меню «Правка» або їхні аналоги у контекстному меню, а також відповідні кнопки панелі інструментів «Стандартная» дають можливість копіювати відфільтровані записи так само, як будь-які інші дані, на нове місце поточного або іншого листа робочої книги. Більш розвинені можливості відбору та копіювання даних надає команда «Расширенный фильтр».

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

При використанні розширеного фільтра критерії відбору задаються безпосередньо у робочому листі (діапазон критеріїв). Користувач завжди може контролювати та швидко змінювати критерії, що застосовуються. Але головне, можна задавати як завгодно складні критерії, що зв'язують елементарні умови по одному полю або різноманітним полям логічними операторами «И» та «ИЛИ». При копіюванні можна не тільки враховувати складні критерії, але і відбирати окремі потрібні поля з відфільтрованих записів.

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

У діапазоні критеріїв можна вводити та сполучати два типи критеріїв:

· Порівняльні критерії — порівнюють вміст полів за заданою умовою (аналогічно застосуванню автофільтра).

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

Діапазон критеріїв повинен складатися не менше ніж із двох рядків. У першому рядку для порівняльних критеріїв необхідно зазначити імена полів. Причому можна ввести тільки ті імена полів, для яких вказуються обмеження. Враховуючи необхідність точного збігу імен полів, їх простіше усього одержати шляхом копіювання. У рядку (рядках), розташованих безпосередньо під рядком з іменами полів, потрібно зазначити обмеження, що мають традиційний вигляд (наприклад: >=5000, або <=30) та інтерпретуються наступним чином:

· Обмеження розташовані в одному рядку зв'язуються логічним оператором«И».

· Обмеження у різних рядках зв'язуються логічним оператором «ИЛИ».

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

Для застосування створеного діапазону критеріїв до списку:

· Розташуйте покажчик клітинки усередині списку.

· Виконайте команду «Данные\Фильтр\Расширенный фильтр…».

· У вікні діалогу «Расширенный фильтр» розташуйте курсор уведення у полі «Диапазон условий» та виділіть відповідний діапазон у робочому листі (у разі потреби перемістіть вікно діалогу).

· Переконайтеся, що встановлений перемикач «фильтровать список на месте» та натисніть кнопку «OK».

Якщо у результаті відбору не повинні бути відображені однакові записи, у вікні діалогу «Расширенный фильтр» потрібно встановити прапорець параметра «Только уникальные записи».

Щоб після фільтрації задати відображення у робочому листі усіх записів списку, виконайте команду «Данные\Фильтр\Отобразить все».

При створенні діапазону критеріїв у вікні діалогу «Расширенный фильтр»йому призначається ім'я «Критерии», яке може використовуватися для швидкого переходу по робочому листу. Якщо при опрацюванні списку регулярно використовується декілька діапазонів критеріїв, то сформуйте їх в окремих областях, призначте їм імена та підставляйте їх імена замість адрес у вікні «Расширенный фильтр». Це дозволить спростити переключення з одного фільтра на інший. При виконанні команди «Расширенный фильтр»Excel завжди переглядає увесь список, а не тільки його відфільтровану частину. Тому при зміні критеріїв фільтра не потрібно виконувати команду «Данные\Фильтр\Отобразить все».

Інтерпретація обмежень по текстовим полям має деякі особливості. Обробка таких критеріїв виконується в Excel за такими правилами:

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

· За умовами >, >=, <, <= будуть знайдені значення, що розташовуються за абеткою від заданого значення, відповідно, після, або починаючи з, або до, або до, включаючи зазначене.

· По обмеженню =”=текст” відбираються значення, точно збіжні з заданим виразом «текст». Наприклад, щоб знайти записи з прізвищем Мороз, варто задати =”=Мороз”. Якщо задати не формулу, а просто — Мороз, то можливо будуть виділені записи з прізвищами Мороз, Морозов, Морозовський і т. д.

· Символи шаблона (* та ?) опрацьовуються так само, як в автофільтрі.

· Щоб символ шаблона «?» або «*» включити у критерій фільтра як звичайний символ, поставте перед ним тільду «~».

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

· Заголовок стовпчика над обчислювальним критерієм (перший рядок діапазону критеріїв) не повинний збігатися з іменем якогось поля в аналізованому списку. Він може бути порожнім або містити будь-який інший текст.

· Формула повинна повертати значення логічного типу.

· Задавати посилання на поле списку можна шляхом указівки відносної адреси першої клітинки поля.

· Посилання на клітинки поза списком повинні бути абсолютними.

· Посилання на клітинки усередині списку повинні бути відносними.

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

Наприклад, обчислюємий критерій може мати вигляд:

· =J3/H3<10;

· =C8–D8<18*365 — таким чином, маючи інформацію про дату прийому на навчання та дату народження, можна відібрати тих, хто поступив у віці до 18 років;

· =F3>=СРЗНАЧ($F$3:$F$325)*1,25 — таким чином, маючи інформацію про зарплату, можна відібрати тих, чия зарплата не менш ніж на 25% перевищує середню.

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

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

Як діапазон розташування результату, можна задати діапазон тільки поточного робочого листа. Задати посилання на діапазон іншого робочого листа у полі уведення «Поместить результат в диапазон»не можна. Проте поточний робочий лист не обов'язково повинний збігатися з листом розташування списку, що дозволяє одержати копію відібраних даних на іншому листі, якщо почати виконання команди «Расширенный фильтр» з “цільового” листа.

Діапазону, що задається у полі «Поместить результат в диапазон», призначається ім'я «Извлечь», яке можна використовувати для переходів у листі.

При роботі з відфільтрованими списками потрібно враховувати ряд особливостей:

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

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

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

· При створенні діаграми також будуть враховані тільки відображені на екрані дані. Якщо відібрані записи списку змінилися, діаграма автоматично оновлюється. Якщо діаграма не повинна оновлюватися кожного разу, коли відбувається приховання або відображення даних, на вкладці «Диаграмма» вікна діалогу «Параметры», що викликається командою «Сервис\Параметры», скиньте прапорець параметра «Отображать только видимые ячейки».