Сортування бази даних в MS Excel

Лекція № 10

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

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

При виконанні сортування необхідно пам'ятати, що у відфільтрованій БД впорядковуються тільки видимі рядки. Якщо режим фільтрування відміняється, то БД буде невідсортованою. Особливо обережно слід впорядковувати БД, що містять у клітинках формули. При цьому доцільно дотримуватись таких правил:

1. Якщо у формулі використовуються посилання на клітинки того ж рядка в межах діапазону БД, то необхідно задавати відносні адреси клітинок.

2. Якщо у формулі є посилання на клітинки поза діапазоном БД, то необхідно використовувати їх абсолютні адреси.

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

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

Для кожного типу даних в Excel встановлені свої правила сортування.

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

2. Текст. Сортування текстових даних здійснюється за першим символом у такій послідовності: 0 1 2 3 4 5 6 7 8 9 (пробіл)! " # $ % & ’ () * +, -. /:; < = >? @ [ \ ] { | } ~ A B C D E F G H I K L M N O P Q R S T U V W X Y Z А Б В Г Д Е Ё Ж 3 И Й К Л М Н О П Р С Т У Ф Х Ч Ц Ш Щ Ъ Ы Ь Э Ю Я

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

3. Логічні значення. Порядок сортування логічних значень: FALSE, TRUE.

4. Помилкові значення. Помилкові значення, такі як #N/A і #REF з'являються в їхньому початковому порядку. Сортування за типом помилок не відбувається.

5. Порожні клітинки. Порожні клітинки завжди з'являються останніми.

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

Сортування текстових рядків найчастіше виконується за алфавітом. Однак у деяких випадках буває необхідно відсортувати дані особливим чином. Для здійснення такого сортування необхідно задати спеціальний список значень з чітко встановленим порядком їх слідування. За замовчуванням в Ехсеl визначено чотири стандартних списки:

Скорочені назви днів тижня: Пн, Вв, Ср, Чт,Пт, Сб, Нд.

Повні назви днів тижня: Понеділок, Вівторок, Середа, Четвер, П'ятниця, Субота, Неділя.

Скорочені назви місяців: Січ, Лют,Бер, Кві, Трав, Чер, Лип, Сер, Вер, Жов, Лис, Гру.

Повні назви місяців: Січень, Лютий, Березень, Квітень, Травень, Червень, Липень, Серпень, Вересень, Жовтень, Листопад, Грудень.

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

Просте сортування. Щоб відсортувати БД за зростанням значень деякого поля, треба помістити табличний курсор у будь-яку клітинку цього поля та клацнути на кнопці сортування Sort by Ascending (по возрастанию), яка розташована на панелі інструментів Standard. Кнопка сортування Sort by Descending (по убыванию)використовується аналогічно для сортування БД за спаданням. В обох випадках Ехсеl самостійно визначить розмір БД і впорядкує всі її записи.

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

Для того, щоб відсортувати БД за двома або трьома полями одночасно, а також, щоб задати додаткові параметри сортування, можна скористатись діалоговим вікном Sort (Сортировка диапазона), що викликається командою Data, Sort (Данные, Сортировка).

Діалогове вікно Sort містить три групи керуючих елементів, у кожній з яких задаються параметри сортування за деяким полем. Групи містять:

1. Список, що випадає, з назвами полів, серед яких необхідно вибрати назву поля, за даними якого буде виконуватися сортування.

2. Перемикачі Ascending та Descending, що встановлюють порядок сортування відповідно за зростанням або за спаданням значень.

У списку Sort by (Сортировать по) необхідно вибрати назву першого ключа сортування. У списках Then by (Затем по) встановлюються назви другого та третього ключів сортування.

Наприклад, для того, щоб відсортувати вихідну БД за полем "Кафедра", а в межах цього поля – за полем "Посада", потрібно встановити параметри сортування, як показано на рис.10.

У нижній частині діалогового вікна Sort роміщена пара радіокнопок. Якщо активізувати кнопку Header row, то у діалоговому вікні Sort будуть відображатися назви полів БД, а рядок із заголовками полів не підлягатиме сортуванню. Якщо активізувати кнопку No header row, то в списках, що випадають, відображатимуться позначення стовпців електронної таблиці, а рядок з назвами полів буде вважатися першим записом БД і підлягатиме сортуванню.

Якщо необхідно сортувати БД більш ніж за трьома полями треба виконати додатковий крок. Припустимо, потрібно відсортувати БД за полями: Поле1, Поле2, Поле3, Поле4 і Поле5. Для цього необхідно спочатку її відсортувати за елементамиПоле3, Поле4 і Поле5, а потім ще раз — за елементами Поле1 і Поле2.

Табличний процесор Ехсеl дозволяє сортувати не тільки записи БД, але й поля, може розпізнавати великі і малі літери. Ці додаткові параметри сортування задаються у діалоговому вікні Sort Options (Параметры сортировки) (рис.11), що активізується кнопкою Options… (Параметры…) діалогового вікна Sort (Сортировка).

Діалогове вікно Sort Options містить:

1) First Key Sort Order (Сортировка по первому ключу) – список, що випадає, елементи якого дозволяють встановити особливий порядок сортування (наприклад, за місяцями).

2) Case Sensitive (Учитывать регистр) – прапорець, встановлення якого задає режим розпізнавання при сортуванні символів верхнього та нижнього регістрів, зокрема великих та малих літер. Після сортування за зростанням першими розташовуються текстові значення, що починаються з малої літери. За замовчуванням відмінність між великими і малими літерами не враховується.

3) Групу радіокнопок Orientation (Сортировать), які дозволяють встановити режим сортування не тільки записів БД – Sort top to bottom, а й полів – Sort left to right.