Інформаційні системи та технології в фінансах

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1. Якщо задана одна літера (декілька літер), то за знаком рівності будуть знайдені всі значення, що починаються на цю літеру (відповідно, ці літери).
2. За умовами >, >=, <, <= будуть знайдені значення, що розташовуються за абеткою від заданого значення, відповідно, після, або починаючи з, або до, або до, включаючи зазначене.
3. По обмеженню =”=текст” відбираються значення, точно збіжні з заданим виразом «текст». Наприклад, щоб знайти записи з прізвищем Мороз, варто задати =”=Мороз”. Якщо задати не формулу, а просто — Мороз, то можливо будуть виділені записи з прізвищами Мороз, Морозов, Морозовський і т. д.
4. Символи шаблона (* та ?) опрацьовуються так само, як в автофільтрі.
5. Щоб символ шаблона «?» або «*» включити у критерій фільтра як звичайний символ, поставте перед ним тільду «~».

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

1. Заголовок стовпчика над обчислювальним критерієм (перший рядок діапазону критеріїв) не повинний збігатися з іменем якогось поля в аналізованому списку. Він може бути порожнім або містити будь-який інший текст.
2. Формула повинна повертати значення логічного типу.
3. Задавати посилання на поле списку можна шляхом указівки відносної адреси першої клітинки поля.
4. Посилання на клітинки поза списком повинні бути абсолютними.
5. Посилання на клітинки усередині списку повинні бути відносними.
6. Посилання на постійний діапазон клітинок усередині списку потрібно задавати в абсолютному вигляді.

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

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

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