Жіночі теревеньки - сайт для кожної жінки
9-06-2018, 23:10

Розширений фільтр в Excel: приклади. Як зробити розширений фільтр в Excel і як ним користуватися?

Рекламний блок

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

расширенный фильтр в Excel

Звичайний і розширений фільтр

В Excel представлений найпростіший фільтр, який запускається з вкладки «Дані» - «Фільтр» (Data - Filter в англомовній версії програми) або за допомогою ярлика на панелі інструментів, схожого на конусоподібну воронку для переливання рідини в ємності з вузьким горлечком.

Для більшості випадків цей фільтр є цілком оптимальним варіантом. Але, якщо необхідно здійснити відбір за великою кількістю умов (та ще й по декілька стовпців, рядків та клітинок), багато хто задається питанням, як зробити розширений фільтр в Excel. В англомовній версії називається Advanced filter.

использование расширенного фильтра в Excel

Перше використання розширеного фільтра

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

Як використовувати розширений фільтр в Excel, приклади, розглянемо нижче.

ABCDEF
1ПродукціяНайменуванняМісяцьДень тижняМістоЗамовник
2овочіКраснодар"Ашан"
3
4ПродукціяНайменуванняМісяцьДень тижняМістоЗамовник
5фруктиперсиксіченьпонеділокМосква"Пятерочка"
6овочіпомідорлютийпонеділокКраснодар"Ашан"
7овочіогірокберезеньпонеділокРостов-на-Дону"Магніт"
8овочібаклажанквітеньпонеділокКазань"Магніт"
9овочібуряктравеньсередаНоворосійськ"Магніт"
10фруктияблукочервеньчетверКраснодар"Бакаль"
11зеленькріплипеньчетверКраснодар"Пятерочка"
12зеленьпетрушкасерпеньп'ятницяКраснодар"Ашан"

Застосування фільтра

У наведеній таблиці рядки 1 і 2 призначені для діапазону умов, рядки з 4 по 7 - для діапазону вихідних даних.

Для початку слід ввести в рядок 2 відповідні значення, від яких буде відштовхуватися в Excel розширений фільтр.

Запуск фільтра здійснюється з допомогою виділення клітинок вихідних даних, після чого необхідно вибрати вкладку «Дані» і натиснути кнопку «Додатково» (Data - Advanced відповідно).

У вікні відобразиться діапазон виділених осередків в полі «Вихідний діапазон». Згідно з наведеним прикладом, рядок приймає значення «$A$4:$F$12».

Поле «Діапазон умов» має заповнитися значеннями «$A$1:$F$2».

Віконце також містить дві умови:

  • фільтрувати список на місці;
  • скопіювати результат в інше місце.
  • Перша умова дозволяє формувати результат на місці, відведеному під осередку вихідного діапазону. Друга умова дозволяє сформувати список результатів в окремому діапазоні, який слід вказати в полі «Помістити результат у діапазон». Користувач вибирає найзручніший варіант, наприклад, перший, вікно «Розширеного фільтра» Excel закривається.

    На основі введених даних, фільтр сформує наступну таблицю.

    ABCDEF
    1ПродукціяНайменуванняМісяцьДень тижняМістоЗамовник
    2овочіКраснодар"Ашан"
    3
    4ПродукціяНайменуванняМісяцьДень тижняМістоЗамовник
    5овочіпомідорлютийпонеділокКраснодар"Ашан"

    При використанні умови «Скопіювати результат в інше місце» значення 4 і 5 рядків відобразяться у заданому користувачем діапазоні. Вихідний діапазон ж залишиться без змін.

    excel расширенный фильтр диапазон условий

    Зручність використання

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

    Якщо користувач володіє знаннями VBA, рекомендується вивчити ряд статей даної тематики та успішно реалізовувати задумане. При зміні значень комірок рядків 2, відведеної під Excel розширений фільтр, діапазон умов буде змінюватися, налаштування скидатися, відразу запускається наново і в необхідному діапазоні будуть формуватися потрібні відомості.

    условия расширенного фильтра Excel

    Складні запити

    Крім роботи з точно заданими значеннями, в Excel розширений фільтр здатний обробляти складні запити. Такими є дані, де частина знаків замінена символи символами.

    Таблиця символів для складних запитів наведена нижче.

    Приклад запитуРезультат
    1п*

    повертає всі слова, що починаються з літери П:

  • персик, помідор, петрушка (якщо ввести в комірку B2);
  • Пятерочка (якщо ввести в комірку F2).
  • 2=результатом буде виведення всіх пустих клітинок, якщо такі є в межах заданого діапазону. Буває вельми корисно вдаватися до даної команді з метою редагування вихідних даних, адже таблиці можуть з часом змінюватися, вміст деяких осередків видалятися за непотрібністю або неактуальностью. Застосування даної команди дозволить виявити порожні клітинки для їх подальшого заповнення, або реструктуризації таблиці.
    3виведуться всі непорожні осередку.
    4*ію*всі значення, де є буквосполучення «січ»: червень, липень.
    5=?????усі клітинки стовпця, мають чотири символи. За символи прийнято вважати літери, цифри та знак пробілу.

    Варто знати, що значок * може означати будь-яку кількість символів. Тобто при введеному значенні «п*» будуть повернуті всі значення, незалежно від кількості символів після літери «п».

    Знак «?» передбачає тільки один символ.

    как сделать расширенный фильтр в Excel

    Зв'язки OR та AND

    Слід знати, що відомості, задані одним рядком в «Діапазоні умов», розцінюються записаними у зв'язку логічним оператором (AND). Це означає, що кілька умов виконуються одночасно.

    Якщо ж дані записані в один стовпець, розширений фільтр в Excel розпізнає їх пов'язаними логічним оператором (OR).

    расширенный фильтр в Excel примеры

    Таблиця значень прийме наступний вигляд:

    ABCDEF
    1ПродукціяНайменуванняМісяцьДень тижняМістоЗамовник
    2фрукти
    3овочі
    4
    5ПродукціяНайменуванняМісяцьДень тижняМістоЗамовник
    6фруктиперсиксіченьпонеділокМосква"Пятерочка"
    7овочіпомідорлютийпонеділокКраснодар"Ашан"
    8овочіогірокберезеньпонеділокРостов-на-Дону"Магніт"
    9овочібаклажанквітеньпонеділокКазань"Магніт"
    10овочібуряктравеньсередаНоворосійськ"Магніт"
    11фруктияблукочервеньчетверКраснодар"Бакаль"

    Зведені таблиці

    Ще один спосіб фільтрування даних здійснюється з допомогою команди Вставка - Таблиця Зведена таблиця» (Insert Table - PivotTable в англомовній версії).

    Згадані таблиці аналогічно працюють з виділеним заздалегідь діапазоном даних і відбирають унікальні значення, щоб надалі піддати аналізу. На ділі це виглядає, як робота з випадаючим списком унікальних полів (наприклад, прізвищ співробітника компанії) і діапазоном значень, які видаються при виборі унікального поля.

    Незручність використання зведених таблиць полягає в необхідності ручного коректування вихідних даних при зміні таких.

    как пользоваться расширенным фильтром в Excel

    Висновок

    На закінчення слід зазначити, що область застосування фільтрів в Microsoft Excel досить широка і різноманітна. Досить застосувати фантазію і розвивати власні знання, вміння і навички.

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

    Величезною популярністю користуються макроси, які складаються на мові VBA. Вони дозволяють запустити значна кількість фільтрів, які сприяють відбору значень та виведення їх у відповідні діапазони.

    Макроси успішно замінюють багатогодинний працю по складанню зведеної, періодичної та іншої звітності, замінюючи тривалий час аналізу величезних масивів всього лише односекундним кліком.

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

    Рекламний блок

    Обов'язково для перегляду