Як зробити фільтр у excel по стовпцях. Розширений фільтр у Excel та приклади його можливостей. Не використовуйте змішані типи даних

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

Недостатньо відразу запустити розширений фільтр – для цього необхідно виконати ще одну умову. Далі ми розповімо про послідовність дій, які слід зробити.

Крок 1: Створення таблиці з умовами відбору

Щоб встановити розширений фільтр, насамперед потрібно створити додаткову таблицю з умовами відбору. Її шапка точно така, як у основної, яку ми, власне, і фільтруватимемо. Для прикладу ми розмістили додаткову таблицю над основною та пофарбували її комірки у помаранчевий колір. Хоча розміщувати її можна у будь-якому вільному місціі навіть на іншому аркуші.

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

Крок 2: Запуск розширеного фільтра

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

Таким чином, можна зробити висновок, що розширений фільтр надає більше можливостей, ніж стандартна фільтрація даних. Але не можна не відзначити, що робота з цим інструментом менш зручна, ніж зі стандартним фільтром.

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

Як додати

Якщо Ви оформляли інформацію через вкладку «Вставка» – «Таблиця» або вкладка «Головна» – "Форматувати як таблицю", то ній можливість фільтрації буде включена за замовчуванням. Відображається потрібна кнопкау вигляді стрілочки, яка розташована у верхньому осередку з правого боку.

Якщо Ви просто заповнили блоки даними, а потім відформатували їх у вигляді таблиці – фільтр потрібно увімкнути. Для цього виділіть весь діапазон осередків, включаючи рядок із заголовками, тому що потрібна нам кнопочка буде додана у верхню строчку. А ось якщо виділити блоки починаючи з осередку з даними, то перша строчка не буде відноситися до інформації, що фільтрується. Потім перейдіть на вкладку «Дані» та натисніть кнопку «Фільтр» .

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

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

Як працює

Тепер давайте розглянемо, як працює фільтр Ексель. Наприклад скористаємося такими даними. У нас є три стовпці: "Назва продукту", "Категорія" та "Ціна" , до них будемо застосовувати різні фільтри.

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

Наприклад, залишимо в Категорії тільки фрукти. Знімаємо галочку в полі «овоч» та натискаємо «ОК».

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

Як видалити

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

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

Використання фільтра

Числовий

Застосуємо "Числовою ..." до стовпця "Ціна" . Клацаємо на кнопку у верхньому осередку і вибираємо відповідний пункт із меню. З списку, що випадає, можна вибрати умову, яку потрібно застосувати до даних. Наприклад, відобразимо всі товари, ціна яких нижче «25». Вибираємо "менше".

У відповідному полі вписуємо потрібне значення. Для фільтрації можна застосовувати кілька умов, використовуючи логічне «І» та «АБО». При використанні «І» – повинні дотримуватися обох умов, при використанні «АБО» – одна із заданих. Наприклад, можна задати: "менше" - "25" - "І" - "більше" - "55". Таким чином ми виключимо товари, ціна яких знаходиться в діапазоні від 25 до 55.

У прикладі мені вийшло так. Тут відображені всі дані з "Ціною" нижче 25.

Текстовий

"Текстовий фільтр"у таблиці прикладу, можна застосувати до стовпчика "Назва продукту". Натискаємо на кнопку зі стрілкою вгорі і вибираємо з меню однойменний пункт. У списку, що відкривається, для прикладу використовуємо «починається з» .

Залишимо в таблиці продукти, що починаються з «ка». У наступному вікні в полі пишемо: «ка*» . Натискаємо "ОК".

"*" у слові, замінює послідовність знаків. Наприклад, якщо задати умову "містить" - "с * л", залишаться слова: стіл, стілець, сокіл і так далі. «?» замінить будь-який знак. Наприклад, "б?тон" - батон, бутон, бетон. Якщо потрібно залишити слова, що складаються з 5 літер, напишіть "?????" .

Ось так я залишила потрібні «Назви продуктів».

За кольором осередку

Фільтр можна настроїти за кольором тексту або за кольором комірки.

Зробимо «Фільтр за кольором»осередки для стовпчика "Назва продукту". Клацаємо по кнопочці зі стрілкою та вибираємо з меню однойменний пункт. Виберемо червоний колір.

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

За кольором тексту

Тепер у прикладі, що використовується, відображені тільки фрукти червоного кольору.

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

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

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

Розширеним фільтром можна відфільтрувати в таблиці все, що завгодно! Межею можливостей цього інструменту є лише фантазія користувача!

Ви читаєте четвертий пост у циклі статей про створення баз даних у MS Excel та організації обробки інформації.

Про те, як , можна прочитати у попередніх статтях циклу.

Фільтр №2 – розширений фільтр!

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

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

1. Відкриваємо у MS Excel файл.

2. Створюючи таблицю критеріїв відбору розширеного фільтра, рекомендую спочатку діяти шаблонно, не замислюючись про завдання, які вирішувати. Копіюємо Усезаголовки стовпців-полів з осередків A7…F7 до осередків A1…F1 – заготівлядля таблиці критеріїв готова! Можна розпочинати основну роботу.

Для стабільної та безпомилкової роботи фільтра між таблицею критеріїв відбору та таблицею бази даних обов'язково має бути хоча б один порожній рядок!

Продовжимо вивчати розширений фільтр Excel, вирішуючи практичні завдання з прикладу роботи з базою даних БД2 «Випуск металоконструкцій ділянкою №2».

Завдання №5:

Показати інформацію про випуск балок на всі замовлення за весь період.

1. Записуємо параметр фільтрації – слово "балка" у стовпці "Виріб" верхньої таблиці критеріїв відбору.

2. Активуємо («встаємо мишею») будь-яку комірку всередині таблиці бази даних – це забезпечить автоматичне заповнення віконця «Вихідний діапазон» у діалоговому вікні «Розширений фільтр», що випадає.

3. Включаємо розширений фільтр Excel 2003 через головне меню програми. Вибираємо: "Дані" - "Фільтр" - "Розширений фільтр".

4. У вікні «Розширений фільтр» заповнюємо вікна так, як показано на знімку екрана, розташованому нижче цього тексту.

Розширений фільтр дозволяє фільтрувати список на місці, але може скопіювати результат фільтрації в інше, вказане користувачем місце.

5. Результат розширеного фільтра – на наступному знімку екрана. Розширений фільтр показав усі записи бази даних, які містять слово «балка» у стовпці «Виріб» – завдання виконане.

Регістр букв не впливає на результати фільтрації!

Правила спільної «роботи» кількох умов відбору

Розширений фільтр в Excel дозволяє виконати відбір з різних складних поєднань умов фільтрації. Можна задати кілька різних умов для кількох стовпців, можна задати кілька умов для одного стовпчика або задати параметри відбору формулою – дуже багато варіантів!

Але головних правил лише два! Решта випадків — різні поєднання цих двох правил.

1. Умови відбору, розміщені на одному рядкув одному або кількох різних стовпцях таблиці критеріїв, наказують розширеному фільтру показувати рядки, для яких виконуються одночасно все без виняткуці умови. (Параметри відбору з'єднані логічними операторами "І".)

2. Умови відбору, розміщені на різних рядкахв одному або кількох стовпцях таблиці критеріїв, наказують розширеному фільтру показувати всі рядки, для яких виконується хоча б однеіз цих умов. (Параметри відбору з'єднані логічними операторами "АБО".)

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

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

Завдання №6:

Відфільтрувати інформацію про пластини масою менше 0,1 тонн по всій базі.

В результаті роботи розширеного фільтра показані всі пластини бази даних, що мають масу менше ніж 0,1 тонни (ілюстрація правила №1).

Завдання №7:

Вивести інформацію про всі наявні в базі пластини, а також про інші будь-які вироби масою більше 1 тонни.

Завдання вирішено – показані записи про всі пластини бази даних та про всі вироби важчі за 1 тонну (ілюстрація правила №2).

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

Звертаю увагу на необхідність уважного контролю коректності вказівки вихідного діапазону бази даних та діапазону таблиці умов у діалоговому вікні «Розширений фільтр», що випадає!

Зокрема, в останньому прикладі слід зазначити: «Діапазон умов: $A$1: $ F$3 »!

Якщо в діапазоні умов або у вашій базі будуть повністю порожні рядки, то розширений фільтр працювати не буде!

Підсумки.

При введенні умов пошуку можна використовувати загальноприйняті знаки підстановки та математичні знаки:

• * - будь-яка кількість будь-яких символів
• ? – один будь-який символ
• = - одно
• < — меньше
• > - більше
• <= — меньше или равно
• >= — більше чи одно
• <>- не дорівнює

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

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

«Поколупатися» самостійно в можливостях цього інструменту — він вартий того, щоб у ньому розібратися! Зросла ефективність вашої роботи багаторазово перекриє витрати часу, витраченого на вивчення!

Продовження теми зберігання та управління великими обсягами інформації- У наступних статтях циклу.

Прошу поважають працю автора підписатися на анонси статей у вікні, розташованому наприкінці кожної статті або у вікні вгорі сторінки!

Шановні читачі, запитання та зауваження пишіть у коментарях унизу сторінки.

Microsoft Excel - це поширений і зручний інструмент для роботи з електронними таблицями. Широкі функціональніші можливостіроблять цю програму другою за популярністю після MS Word серед усіх офісних програм. Використовують її економісти, бухгалтери, вчені, студенти та представники інших професій, яким необхідно обробляти математичні дані.

Однією з найзручніших функцій у програмі є фільтрація даних. Розглянемо, як налаштувати та використовувати MS Excel фільтри.

Де в Excel фільтри – їх види

Знайти фільтри в цій програмі легко – потрібно відкрити головне меню або просто затиснути клавіші Ctrl+Shift+L.

Як задати фільтр у Excel

Основні функції фільтрації в Excel:

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

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

Як використовувати розширений фільтр в Excel - як його настроїти

Стандартний фільтр має істотний недолік - щоб згадати, які саме критерії відбору використані, потрібно відкривати меню. І тим більше це викликає незручності, коли поставлено не один критерій. З цього погляду зручніший розширений фільтр, який відображається окремою таблицею над даними.

ВІДЕО ІНСТРУКЦІЯ

Порядок налаштування:

1. Створити таблицю з даними для подальшої роботи з нею. У ній має бути порожніх рядків.
2. Створити таблицю з умовами відбору.
3. Запустіть розширений фільтр.

Розглянемо приклад налаштування.
У нас є таблиця зі стовпцями Товар, Кількість та Ціна.

Наприклад, потрібно відсортувати рядки, назви товарів яких починаються зі слова «Цвяхи». Під цю умову потрапляють кілька рядків.

Таблиця з умовами розміститься у осередках А1:А2. Важливо вказати назву стовпця, де відбуватиметься відбір (осередок А1) та саме слово для відбору – Цвяхи (осередок А2).

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

Потім необхідно:

1. виділити будь-яку з осередків,
2. відкрити «Розширений фільтр» шляхом: Дані – Сортування та фільтр – Додатково,
3. перевірити, що в полі «Вихідний діапазон» - туди має потрапити вся таблиця з інформацією,
4. у «Діапазоні умов» необхідно задати значення осередків з умовою відбору, даному прикладіце діапазон А1: А2.

Після натискання на кнопку «ОК» відбудеться відбір потрібної інформації, і в таблиці з'являться лише рядки з потрібним словом, у нашому випадку це «Цвяхи». Номери рядків, що залишилися, пофарбуються в синій колір. Щоб скасувати заданий фільтр, натисніть клавіші CTRL+SHIFT+L.

Також легко налаштувати відбір по рядках, що містять слово «Цвяхи» без урахування регістру. У діапазоні В1:В2 розмістимо колонку з новим критерієм відбору, не забувши вказати заголовок стовпця, в якому буде відсівати. У осередку В2 необхідно вказати таку формулу = цвяхи.

• виділити будь-яку з осередків таблиці,
• відкрити «Розширений фільтр»,
• перевірити, що у «Вихідний діапазон» потрапила вся таблиця з даними,
• у «Діапазоні умов» вказати В1: В2.

Після натискання "ОК" відбудеться відсівання даних.

Це найпростіші приклади роботи з фільтрами в excel. У розширеному варіанті зручно задавати інші умови для відбору, наприклад, відсів з параметром «АБО», відсів з параметром «Цвяхи» і значенням у стовпці «Кількість» >40.

Як зробити фільтр в Excel по стовпцях

Інформацію в таблиці можна фільтрувати по стовпцях – одному чи кількох. Розглянемо з прикладу таблиці зі стовпцями «Місто», «Місяць» і «Продажи».

Якщо необхідно відсіяти дані по стовпцю з назвами міст в алфавітному порядку, потрібно виділити будь-яку осередку цього стовпця, відкрити «Сортування» та «Фільтр» і вибрати параметр «АЯ». В результаті інформація з'явиться з урахуванням першої літери у назві міста.

Для отримання інформації за зворотним принципом необхідно користуватися параметром «ЯА».

Необхідне відсівання інформації по місяцях, а також місто з великим обсягом продажів має стояти в таблиці вище міста з меншим обсягом продажів. Щоб розв'язати завдання, потрібно в «Сортування та фільтрі» вибрати параметр «Сортування». У вікні з налаштуваннями уточнити «Сортувати по» - «Місяць».

Далі слід додати другий рівень сортування. Для цього потрібно вибрати в «Сортуванні» – «Додати рівень» та вказати стовпець «Продаж». У колонці параметрів "Порядок" вказати "За спаданням". Після натискання "ОК" відбудеться відбір даних за заданими параметрами.

ВІДЕО ІНСТРУКЦІЯ

Чому можуть не працювати фільтри в Excel

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

Проблема з фільтром за датою – одна з найпопулярніших. Виникає після вивантаження даних з облікової системи як масиву. При спробі фільтрації рядків по стовпцю, що містить дати, відсівання відбувається не за датою, а за текстом.

Рішення проблеми:

1. виділити стовпець з датами,
2. відкрити вкладку Excel у головному меню,
3. вибрати кнопку «Комірки», у списку вибрати параметр «Перетворити текст на дату».

До популярних помилок користувачів під час роботи з цією програмою також варто віднести:

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

І ще одна критична помилка, що не дозволяє повною мірою використовувати можливості Excel- Це застосування неліцензійного продукту.

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

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

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

Як зробити розширений фільтр у Excel?

Розширений фільтр дозволяє фільтрувати дані щодо необмеженого набору умов. За допомогою інструмента користувач може:

1. поставити більше двох критеріїв відбору;
2. скопіювати результат фільтрації на інший аркуш;
3. поставити умову будь-якої складності за допомогою формул;
4. отримати унікальні значення.

Алгоритм застосування розширеного фільтра простий:

1. Робимо таблицю з вихідними даними або відкриваємо існуючу. Наприклад, так:
2. Створюємо таблицю умов. Особливості: рядок заголовків повністю збігається з «шапкою» таблиці, що фільтрується. Щоб уникнути помилок, копіюємо рядок заголовків у вихідній таблиці та вставляємо на цей самий аркуш (збоку, зверху, знизу) або на інший аркуш. Вносимо до таблиці умов критерію відбору.
3. Переходимо на вкладку «Дані» – «Сортування та фільтр» – «Додатково». Якщо відфільтрована інформація повинна відобразитись на іншому аркуші (НЕ там, де знаходиться вихідна таблиця), то запускати розширений фільтр потрібно з іншого аркуша.
4. У вікні «Розширеного фільтра», що відкрилося, вибираємо спосіб обробки інформації (на цьому ж аркуші або на іншому), задаємо вихідний діапазон (табл. 1, приклад) і діапазон умов (табл. 2, умови). Рядки заголовків повинні бути включені до діапазонів.
5. Щоб закрити вікно "Розширеного фільтра", натискаємо ОК. Бачимо результат.

Верхня таблиця – результат фільтрації. Нижня табличка з умовами дана для наочності поряд.

Як користуватися розширеним фільтром у Excel?

Щоб скасувати дію розширеного фільтра, поставимо курсор у будь-якому місці таблиці і натисніть клавіші Ctrl + Shift + L або «Дані» - «Сортування та фільтр» - «Очистити».

Знайдемо за допомогою інструмента «Розширений фільтр» інформацію про значення, що містять слово «Набір».

У таблицю умов внесемо критерії. Наприклад, такі:

Програма у даному випадкушукатиме всю інформацію щодо товарів, у назві яких є слово «Набір».

Для пошуку точного значення можна використати знак "=". Внесемо до таблиці умов такі критерії:

Excel сприймає знак "=" як сигнал: зараз користувач задасть формулу. Щоб програма працювала коректно, у рядку формул має бути запис виду: =»=Набір обл.6 кл.»

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

Тепер відфільтруємо вихідну таблицю за умовою «АБО» для різних стовпців. Оператор "АБО" є і в інструменті "Автофільтр". Але там його можна використати в рамках одного стовпця.

У табличку умов введемо критерії відбору: =»=Набір обл.6 кл.» (у стовпець «Назва») та =»

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

Результат відбору:

Розширений фільтр дозволяє використовувати як критерій формули. Розглянемо приклад.

Відбір рядка із максимальною заборгованістю: =МАКС(Таблица1).

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

Як зробити кілька фільтрів у Excel?

Створимо фільтр за декількома значеннями. І тому введемо в таблицю умов відразу кілька умов відбору данных:

Застосуємо інструмент «Розширений фільтр»:

Тепер із таблиці з відібраними даними вилучимо нову інформацію, відібрану за іншими критеріями Наприклад, лише відвантаження за 2014 рік.

Вводимо новий критерій у табличку умов та застосовуємо інструмент фільтрації. Вихідний діапазон - таблиця з відібраними за попереднім критерієм даними. Так виконується фільтр кількома стовпцями.

Щоб використати кілька фільтрів, можна сформувати кілька таблиць умов на нових аркушах. Спосіб реалізації залежить від поставленого користувачем завдання.

Як зробити фільтр у Excel по рядках?

Стандартними методами – ніяк. Програма Microsoft Excel відбирає дані лише у стовпцях. Тому потрібно шукати інших рішень.

Наводимо приклади рядкових критеріїв розширеного фільтра Excel:

1. Перетворити таблицю. Наприклад, із трьох рядків зробити список із трьох стовпців і до перетвореного варіанту застосувати фільтрацію.
2. Використовувати формули для відображення тих даних у рядку, які потрібні. Наприклад, зробити якийсь показник випадаючим списком. А в сусідній осередок ввести формулу, використовуючи функцію ЯКЩО. Коли зі списку вибирається певне значення, поруч з'являється його параметр.

Щоб навести приклад, як працює фільтр по рядках в Excel, створимо табличку:

Для списку товарів створимо список, що випадає:

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

Поруч із випадаючим списком осередок введемо таку формулу: Її завдання – вибирати з таблиці ті значення, які відповідають певному товару

Завантажити приклади розширеного фільтра

Таким чином, за допомогою інструмента «Випадаючий список» та вбудованих функцій Excel відбирає дані у рядках за певним критерієм.

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

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

Якщо Ви просто заповнили осередки даними, а потім відформатували їх у вигляді таблиці – фільтр потрібно увімкнути. Для цього виділіть весь діапазон осередків таблиці, включаючи заголовки стовпців, так як кнопочка фільтра розташовується у верхньому стовпці, і якщо виділити стовпець, починаючи з комірки з даними, то вона не буде ставитися до даних стовпця, що фільтруються. Потім перейдіть на вкладку «Дані» та натисніть кнопку «Фільтр».

У прикладі стрілочка фільтра знаходиться в заголовках, і це правильно - фільтруватимуться всі дані в стовпці, розташовані нижче.

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

Тепер давайте розглянемо, як працює фільтр Ексель. Наприклад скористаємося наступною таблицею. У ній три стовпці: «Назва продукту», «Категорія» та «Ціна», до них застосовуватимемо різні фільтри.

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

Наприклад, залишимо в Категорії тільки фрукти. Знімаємо галочку в полі «овоч» та натискаємо «ОК».

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

Якщо Вам потрібно видалити фільтр даних в Excel, натисніть в осередку на піктограму фільтра та виберіть з меню «Видалити фільтр з (назва стовпця)».

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

Застосуємо "Числовий фільтр" до стовпця "Ціна". Клацаємо на кнопку у верхньому осередку і вибираємо відповідний пункт із меню. З списку можна вибрати умову, яку потрібно застосувати до даних стовпця. Наприклад, відобразимо всі товари, ціна яких нижча за «25». Вибираємо "менше".

У відповідному полі вписуємо потрібне значення. Для фільтрації даних можна застосовувати кілька умов, використовуючи логічне «І» та «АБО». При використанні «І» – повинні дотримуватися обох умов, при використанні «АБО» – одна із заданих. Наприклад, можна задати: "менше" - "25" - "І" - "більше" - "55". Таким чином ми виключимо товари з таблиці, ціна яких знаходиться в діапазоні від 25 до 55.

Таблиця з фільтром по стовпцю "Ціна" нижче 25.

«Текстовий фільтр» у таблиці прикладу можна застосувати до стовпця «Назва продукту». Натискаємо на кнопку фільтра в стовпці і вибираємо з меню однойменний пункт. У списку, що відкривається, для прикладу використовуємо «починається з».

Залишимо в таблиці продукти, що починаються з «ка». У наступному вікні в полі пишемо: «ка*». Натискаємо "ОК".

"*" у слові, замінює послідовність знаків. Наприклад, якщо задати умову "містить" - "с * л", залишаться слова стіл, стілець, сокіл і так далі. «?» замінить будь-який знак. Наприклад, "б?тон" - батон, бутон. Якщо потрібно залишити слова, що складаються з 5 букв, напишіть "?????".

Фільтр для стовпчика «Назва продукту».

Фільтр можна настроїти за кольором тексту або за кольором комірки.

Зробимо "Фільтр за кольором" комірки для стовпця "Назва продукту". Клацаємо по кнопочці фільтра та вибираємо з меню однойменний пункт. Виберемо червоний колір.

У таблиці залишилися лише продукти червоного кольору.

Фільтр кольору тексту застосовується до стовпця «Категорія». Залишимо лише фрукти. Знову вибираємо червоний колір.

Тепер у таблиці прикладу відображено лише фрукти червоного кольору.

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

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

А потім, щоб мати можливість швидковідбирати тільки необхідні вам дані, приховуючинепотрібні рядкитаблиці. Таким чином фільтр дозволяє без видаленнярядків таблиці Ексель просто тимчасово приховати їх.

Приховані за допомогою фільтра рядки таблиці не зникають. Можна умовно уявити, що їхня висота стає рівною нулю (про зміну висоти рядків і ширини стовпців я раніше розповідав). Таким чином, інші рядки, не приховані фільтром, як би "склеюються". Те, що виходить в результаті, є таблиця з накладеним фільтром.

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

Тепер давайте подивимося, як додати фільтри в таблицю.

Як зробити (накласти) фільтр на таблицю Excel

Це дуже просто! Допустимо, у нас є таблиця із заголовками. Оскільки фільтр на таблицю Ексель накладається на певну частину таблиці, то цю частину потрібно спочатку виділити. Сам Excel нізащо не здогадається, що ви хочете, тому виділяємо у рядку із заголовками таблиці потрібні осередки, як показано на малюнку нижче.

[натисніть на картинку для збільшення]

Зверніть увагу:

Якщо виділити ВСЮ рядок, Ексель автоматично встановить фільтр на ВСІ стовпці, в яких є хоч щось. Якщо у стовпчику нічого немає, то фільтр не накладається.

Після того як виділили потрібні стовпці, необхідно встановити фільтр. Найпростіший спосіб - це зайти на вкладці стрічки "Головне" / "Фільтр та сортування" та натиснути фільтр. Приводжу скріншот із Office 2010, в інших версіях Excel фільтрна таблицю накладається аналогічно.

[натисніть на картинку для збільшення]

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

Якщо не виділяти кілька осередків, а просто клацнути на один, то фільтр додається на ВСЮ рядок, як би ви його виділили.

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

[натисніть на картинку для збільшення]

Наведений приклад включення фільтра у таблиці є найпростішим. Давайте подивимося, як він працює.

Як користуватися фільтром Ексель

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

[натисніть на картинку для збільшення]

Сенс фільтра:

полягає в тому, що Excel залишить тільки ті рядки таблиці, які в цьому (з налаштованим фільтром) стовпці містять комірку з вибраним значенням. Інші рядки будуть приховані.

Щоб прибрати фільтрацію (не видаляючи фільтр!), просто позначте галочками всі пункти. Такий же ефект буде при видаленні фільтра зовсім - таблиця знову набуде початкового вигляду.

Накладення кількох фільтрів на таблицю Ексель

Фільтри у таблиці Ексель можна комбінувати. Накладення кількох фільтрів відбувається за принципом логічного "І". Що це означає, погляньмо на прикладі.

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

При накладенні кожного наступного фільтра кількість рядків, що відображаються в таблиці, задовольняють умовам ВСІХ фільтрів, буде зменшуватися. Таким чином, послідовно накладаючи кілька фільтрів, можна з величезної таблиці тисячі рядків швидко зробити невелику вибірку.

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

Тепер подивіться відеоролик, в якому я показую реальний приклад фільтрації даних у прайс-листі більш ніж з 15 000 рядків.

Підведемо підсумки

Якщо вам доводиться працювати з великими таблицями в Ексель, причому потрібно мати можливість швидко відібрати ті чи інші дані, застосування фільтрів заощадить вам дуже багато часу. Чим більша таблиця, тим більша вигода від застосування фільтрів.

Наведений приклад взято з мого навчального курсу Microsoft Excel. Використання фільтрів із складнішими умовами відбору розглядаю на заняттях.

Завантажити файл, на прикладі якого я розглядав роботу з фільтрами, ви можете