Створення та ведення списків – ЧАСТИНА 8

► зірочка (*) замінює будь-яку послідовність символів

► знак питання () Замінює будь-який символ

Так, для пошуку всіх прізвищ, що починаються з літери В, умова відбору прийме вигляд більше або дорівнює В * Якщо знак питання () Або зірочку (*) треба включити до умова як літерал, введіть перед ними тильду (~)

Обчислення підсумків після застосування автофільтра

Необхідно враховувати, що при застосуванні звичайних статистичних функцій, таких як СУМА (SUM), СЕРЕДНЯ (AVERAGE), МІН (MIN), МАКС (МАХ), обчислення проводяться над даними списку, а не тільки для рядків, відібраних фільтром Щоб виконати обчислення тільки для рядків, що задовольняють умовам відбору, використовуйте функцію ПРОМЕЖУТОЧНИЕІТОГІ (SUBTOTAL) з відповідним аргументом номер_фуікціі Інформація про цю функцію приведена в розділі «Функція ПРОМЕЖУТОЧНИЕІТОГІ» даної глави

Видалення автофільтра

Щоб скасувати автофільтр для конкретного стовпця, розкрийте відповідний список автофільтра і виберіть у ньому пункт Всі (Аll) Щоб скасувати всі застосовані автофільтри, виберіть команду Дані ► Фільтр ► Відобразити всі (Data ► Filter

► Show All) Для видалення всіх кнопок автофільтрів виберіть команду Дані ► Фільтр ► Автофільтр (Data ► Filter ► AutoFilter) – тим самим ви знімете прапорець поруч з командою Автофільтр

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

Команда Розширений фільтр (Advanced Filter) надає більш широке поле діяльності в порівнянні з командою Автофільтр:

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

► Можна задавати три і більше умови для конкретного стовпця з використанням принаймні одного логічного оператора АБО

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

ПРИМІТКА

Не можна витягти рядки зі списку і вставити їх в окремий лист, хоча діапазон умов не зобовязаний перебувати на поточному аркуші Після вилучення рядків ви, звісно, ​​маєте право копіювати і переміщати їх в інше місце

Завдання діапазону умов

Команда Розширений фільтр, на відміну від команди Автофільтр, вимагає завдання умов відбору рядків в окремому діапазоні робочого аркуша Оскільки при фільтрації ховаються цілі рядки, діапазон умов краще помістити вище списку або на окремому аркуші Діапазон умов має містити принаймні два рядки Введіть один або кілька заголовків стовпців у верхньому рядку, а умови відбору – в другу і наступні рядки За винятком обчислюваних умов, заголовки в діапазоні умов повинні збігатися з заголовками стовпців у списку (вони можуть відрізнятися регістром букв і форматуванням) Для забезпечення точності ці заголовки найкраще створювати, виділяючи заголовки стовпців у списку і копіюючи їх в

верхній рядок діапазону умов за допомогою команд Копіювати і Вставити меню

Виправлення

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

Накладення умов на два стовпці за допомогою оператора АБО

На рис 2823 показаний список будинків для продажу (Підкреслені числа в стовпці А є гіперпосиланнями на фотографії будинків) Припустимо, що для нас привабливі будинку з розміром ділянки (стовпець F) не менш двох акрів Інтерес представляють також будинки і з меншим ділянкою, розташовані в окрузі U (стовпець К), в безпосередній близькості від шкіл Щоб відібрати пропозиції, відповідні будь-якого з цих двох критеріїв, ми створили діапазон умов у трьох вставлених над списком нових рядках (рис 2824)

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

Якщо ви виберете команду Дані ► Фільтр ► Розширений фільтр (Data ► Filter

► Advanced Filter) і заповніть діалогове вікно Розширений фільтр, як показано на рис 2825, Excel відобразить відфільтрований список, зображений на рис 2826

Деякі поля в стовпці F в відфільтрованому списку порожні, оскільки у вихідних

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

Рис 2824 Діапазон умов у комірках А1: ВЗ дозволяє відібрати будинку з ділянкою не менше двох акрів або розташовані в шкільному окрузі U

Рис 2825У діалоговому вікні Розширений фільтр встановіть перемикач в положення Фільтрувати список на місці (Filter The List, In-Place) і вкажіть місцезнаходження списку і діапазону умов

Рис 2826Після натискання кнопки ОК в діалоговому вікні Розширений фільтр Excel

відфільтрує список і покаже цікавлять нас вдома

Подібно команді Автофільтр, команда Розширений фільтр приховує всі відсіяні рядки, а номери відібраних рядків відображає контрастним кольором Щоб зясувати, скільки рядків задовольняють заданим умовам, використовуйте формулу ПРОМЕЖУТОЧНИЕІТ0ГІ (3 А: А), звичайно, за умови, що стовпець А включений в відфільтрований список

Зверніть увагу, що дві умови на рис 2824 введені на різних рядках Якщо ви помістіть їх на одному рівні, Excel відбере тільки ті рядки, для яких виконуються обидві умови Іншими словами, умови на одному рядку вважаються зєднаними логічним оператором І, а умови на різних рядках вважаються зєднаними оператором АБО При цьому кількість рядків з умовами може бути будь-яким

Обидві умови введені як простий текст Значення U нижче заголовка Округ наказує Excel знайти рядки, в яких значення в стовпці Округ починається з букви U (Іншими словами, після букви U неявно присутній символ шаблону * (зірочка)) Якщо ви хочете задати точний збіг з буквою U, введіть = = U. Ця незграбна формула відображається в комірці як = 11 Умова> = 2 нижче заголовка Ділянка вказує знайти рядки, в яких значення в стовпці Ділянка більше або дорівнює 2 У числових умовах застосовні будь оператори порівняння (=,>, <,> =, <=). При цьому ви маєте право ввести число без знака рівності, якщо потрібно точний збіг.

Врахуйте, що порожня клітинка в діапазоні умов означає «будь-яке значення» для відповідного стовпця Якщо ви випадково включите порожній рядок в діапазон умов, отримаєте невідфільтрованих список

РАДА

У тому випадку, коли діапазон умов знаходиться на одному аркуші зі списком, Excel призначає цьому діапазону імя Критерії (Criteria) відразу після застосування фільтра Ви можете використовувати це імя для переміщення по робочому листу Наприклад, якщо потрібно повернутися в діапазон для зміни умови, натисніть клавішу F5 і виберіть пункт Критерії (Criteria) у діалоговому вікні Перехід (Go To)

Використання трьох операторів АБО для одного стовпця

Припустимо, що нам потрібно вибрати зі списку всі будинки, що знаходять в трьох шкільних округах – U, F або Т В цьому випадку слід включити в діапазон умов тільки заголовок Округ і ввести букви U, F і Т в трьох рядках безпосередньо нижче заголовка Після застосування команди Розширений фільтр Excel представить список, показаний на рис 2827

Рис 2827 Використовуючи діапазон умов А1: А4, ми скоротили список, і тепер в ньому представлені тільки будинки, розташовані у шкільних округах L), F або Т

Спільне використання операторів АБО і І

Якщо ви хочете обмежити відображається список будинками в шкільних округах F або Т з корисною площею не менше 2000 квадратних футів, створіть діапазон умов

аналогічно рис 2828 Зауважте, умова> = 2000 введено в двох рядках, оскільки для кожного шкільного округу (F і Т) ми хочемо бачити в списку будинку площею 2000 квадратних футів або більше

Рис 2828 Щоб побачити список будинків площею не менше 2000 квадратних футів в шкільних округах F або Т, потрібно повторити умова> = 2000 в кожному рядку діапазону умов

РАДА

При кожному виконанні команди Розширений фільтр Excel аналізує повний список, а не поточний безліч раніше відфільтрованих рядків Внаслідок цього перед зміною фільтра зовсім не обовязково використовувати команду Відобразити всі (Show All)

Накладення декількох умов на один стовпець

Щоб застосувати декілька умов до одного й того ж стовпцю, повторіть стовпець у діапазоні умов

Наприклад, щоб отримати вибірку з будинків вартістю від $ 50 000 до $ 90 000, діапазон умов повинен мати наступний вигляд:

&gt=50000                                          &lt=90000

А щоб виключити рядки з цінами, що потрапляють в цей діапазон, потрібно створити такий діапазон умов:

&lt=50000

                                               &gt=90000                                                                         

Обчислювані умови

Обчислювані умови відрізняються від звичайних умов порівняння тим, що дозволяють оперувати значеннями, повертаними формулою При пошуку будинків,

ціна яких перевищує $ 100 000, обчислювані умови не вимагаються Однак для будинків, оцінених нижче медіани індивідуальних вартостей всіх будинків, без обчислюваних умов вже не обійтися

Джерело: Ефективна робота: Microsoft Office Excel 2003 / М Додж, К Стінсон – СПб: Питер, 2005 – 1088 с: ил

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*