ПОВІДОМЛЕННЯ

Дата добавления: 2015-10-15; просмотров: 445

Для фільтрації списків програма Microsoft Excel крім команди Авто-фильтр пропонує користувачам команду Расширенный фильтр, яку використовують для створення складних умов відбору даних.

Команда Расширенный фильтр має додаткові можливості порівняно з командою Автофильтр, а саме:

- задавати для кількох стовпців умови,з'єднані логічним оператором ИЛИ. Наприклад, із загального списку співробітників можна вибрати осіб віком старше 50 років або таких, річна заробітна плата яких перевищує 10000 грн;

- задавати три або більше умовдля конкретного стовпця з використанням хоча б одного логічного оператора ИЛИ. Наприклад, можна вивести на екран список співробітників, чиї прізвища починаються з "А", "Г" чи "Н";

- задавати обчислюванні умови.Наприклад, можна вивести на екран списки тільки тих співробітників, заробітна плата яких на25 % перевищує середню.

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

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

Для використання розширеного фільтрадо створеного засобами Excel списку необхідно виконати такі дії.

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

• Задати діапазон умов відбору рядків, який повинен містити щонайменше два рядки. У верхній рядок вводять один або кілька заголовків стовпців, у другому і наступних задають умови відбору.

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

- умови одного рядка вважаються з'єднаними логічним оператором И (тобто всі умови повинні виконуватись одночасно);

- умови у двох рядках вважаються з'єднаними логічним оператором ИЛИ (тобто достатньо виконання хоча б однієї умови).

• Для того щоб знайти дані, які відповідають одній умові в одному стовпці або іншій умові в іншому стовпці, слід ввести умови відбору в різні рядки діапазону умов відбору. Наприклад, наведений далі діапазон умов відображає всі рядки, що містять значення "Артикул 1" у стовпці "Товар" або "Іванов" у стовпці "Менеджер", або обсяг продажів на суму понад 1000 грн у стовпці "Обсяг продажів".

• За наявності для одного стовпця двох і більше умов відбору треба ввести їх безпосередньо одна під одною в суміжні рядки. Наприклад, наведений діапазон умов відображає рядки, що містять у стовпці "Менеджер" список співробітників, чиї прізвища починаються з "А", "Г" та "Н".

• Для того щоб знайти дані, які відповідають одному з кількох наборів умов, кожний з яких містить умови більше як для одного стовпця, необхідно ввести ці умови відбору в окремі рядки. Наприклад, наведений діапазон умов відображає рядки, що містять у стовпці "Менеджер" співробітників, прізвища яких починаються з "І" і які мають обсяг продажів на суму понад 5000 грн., або прізвища яких починаються з "Г", а обсяг продажів на суму менше6000 грн.

• Для того щоб показати рядки, в яких на елементи одного стовпця одночасно накладається кілька умов, необхідно включити в діапазон умов кілька стовпців з однаковими заголовками. Наприклад, наведений діапазон умов відображає рядки, які містять у стовпці "Обсяг продажів" обсяг продажів від 5000 до 8000 грн.

• Задаючи текстові умови, слід дотримуватися таких правил:

- одна літера означає, що пошуку підлягають усі значення, що починаються із зазначеної літери;

- символ ">" або "<" означає, що пошуку підлягають значення, що за абеткою стоять після введеного текстового значення або перед ним;

- формула - текст означає, що пошуку підлягають значення, які точно співпадають з введеним рядком символів текст.

• Після введення діапазону умов відбору слід виконати командуДанные /Фильтр /Расширенный фильтр.

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

• Перемикач Обработка слід встановити в положення Фильтроватьсписок на месте і натиснути кнопку ОК.

• У результаті виконання команди Расширенный фильтр приховуються всі рядки, що не задовольняють введені умови. Номер відібраних рядків виділяються синім кольором.

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

- заголовок над обчислюваною умовою повинен відрізнятися відбудь-якого іншого заголовка стовпця у списку. Заголовок умови може бути порожній або містити довільний текст;

- посилання на комірки, що перебувають за межами списку, повинні бути абсолютними;

- посилання на комірки списку повинні бути відносними (за одним винятком, який розглянемо далі).

Наприклад, для відображення рядків, що містять список працівників, у яких обсяг продажів перевищує середній, необхідно:

1.у комірку, що перебуває за межами списку (наприклад, D2),ввести формулу =CP3HAЧ(F8:F30), за допомогою якої обчислюється середнє значення за стовпцем "Обсяг продажів" з діапазону F8:F30;

2.вважаючи, що діапазоном умов є А1:А2, ввести в комірку А2обчислювану умову =F8>$D$2;

- формула умови порівнює комірку F8 з коміркою D2. КоміркаF8 є першою у стовпці "Обсяг продажів" невідфільтрованого списку і згідно з формулою обчислюваної умови порівнюється з коміркою D2. У процесі фільтрації по черзі підставляється кожний елемент списку замість цього першого значення, тобто D2порівнюється з F8, потім з F9 і так до кінця списку;

- посилання на комірку D2 повинно бути абсолютним. Якщо б укомірку А2 діапазону умов було введено формулу =F8>D2, то спочатку F8 порівнювалося б з D2, потім F9 з D3 і т. д. І всі ці порівняння, за винятком першого, були б неправильними;

- значення "ЛОЖЬ", що повертає формула умови, саме по собі непотрібне. У розглядуваному прикладі воно вказує лише на те, що перший працівник у списку має обсяг продажів менший від середнього.

• При визначенні обчислюваної умови можна ввести формулу =F8>>CP3HAЧ($F$8:$F$30), яка прямо посилається на стовпець "Обсяг продажів", а не на комірку, що перебуває за межами списку. Згідно з наведеними правилами посилання на комірки у списку повинні бути відносними, проте формула містить абсолютні адреси діапазону комірок від F8 до F30. Це зроблено для того, щоб на кожному кроці фільтрації використовувався один і той самий діапазон комірок. Іншими словами, треба порівнювати F8 із середнім для F8:F30, потім F9 із середнім для F8:F30 і т. д. Якщо використати відносне посилання на F8:F30, то лише перше порівняння буде правильним.

• Копіювання відфільтрованих рядків віншу частину робочого аркуша.За допомогою вікна діалогу команди Расширенный фильтрможна встановити режим копіювання відібраних рядків в іншу частину аркуша замість виведення відфільтрованого списку. Щоб скопіювати рядки, треба перемикач Обработка встановити в положення Скопировать результат в другое место, а в полі Поместить результат в диапазон зазначити адресу діапазону, куди потрібно скопіювати інформацію.

• Режим Только уникальные записи у вікні команди Расширенный фильтр додає додатковий фільтр до заданих умов відбору. Він приховує рядки, що повторюються.