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

виконання вибірки

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

Спосіб 1: застосування розширеного автофильтра

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

  1. Виділяємо область на аркуші, серед даних якої потрібно зробити вибірку. У вкладці «Головна» клацаємо по кнопці «Сортування і фільтр». Вона розміщується в блоці налаштувань «Редагування». У відкритому після цього списку виконуємо клацання по кнопці «Фільтр».

    Включення фільтра в Microsoft Excel

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

  2. Включення фільтра через вкладку Дані в Microsoft Excel

  3. Після цього дії в шапці таблиці з'являються піктограми для запуску фільтрування у вигляді перевернутих вістрям вниз невеликих трикутників на правому краю осередків. Кількома по даному значку в заголовку того стовпця, по якому бажаємо здійснити вибірку. У запустити меню переходимо по пункту «Текстові фільтри». Далі вибираємо позицію «Настроюваний фільтр ...».
  4. Перехід в настроюється фільтр в Microsoft Excel

  5. Активується вікно користувальницької фільтрації. У ньому можна задати обмеження, за яким буде проводитися відбір. У випадаючому списку для стовпця містить осередки числового формату, який ми використовуємо для прикладу, можна вибрати одне з п'яти видів умов:
    • одно;
    • не дорівнює;
    • більше;
    • більше або дорівнює;
    • менше.

    Давайте як приклад задамо умову так, щоб відібрати тільки значення, за якими сума виручки перевищує 10000 рублів. Встановлюємо перемикач в позицію «Більше». У праве поле вписуємо значення «10000». Щоб зробити виконання дії, клацаємо по кнопці «OK».

  6. Пользвательскій фільтр в Microsoft Excel

  7. Як бачимо, після фільтрації залишилися тільки рядки, в яких сума виручки перевищує 10000 рублів.
  8. Результати фільтрації в Microsoft Excel

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

    Крім того, існує ще перемикач умов. У нього два положення «І» та «АБО». За замовчуванням він встановлений в першому положенні. Це означає, що у вибірці залишаться тільки рядки, які задовольняють обом обмеженням. Якщо він буде виставлений в положення «АБО», то тоді залишаться значення, які підходять під будь-який з двох умов. У нашому випадку потрібно виставити перемикач в положення «І», тобто, залишити це налаштування за замовчуванням. Після того, як всі значення введені, клацаємо по кнопці «OK».

  10. Установка верхньої межі в призначеному для користувача фільтрі в Microsoft Excel

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

  13. Аналогічно можна налаштовувати фільтри і в інших стовпцях. При цьому є можливість зберігати також фільтрацію і за попередніми умовами, які були задані в колонках. Отже, подивимося, як проводиться відбір за допомогою фільтра для осередків в форматі дати. Кількома по значку фільтрації у відповідному стовпці. Послідовно натискаємо по пунктам списку «Фільтр за датою» і «Настроюваний фільтр».
  14. Перехід до фільтрації за датою в Microsoft Excel

  15. Знову запускається вікно призначеного для користувача автофильтра. Виконаємо відбір результатів в таблиці з 4 по 6 травня 2016 року включно. У перемикачі вибору умов, як бачимо, ще більше варіантів, ніж для числового формату. Вибираємо позицію «Після або дорівнює». В поле праворуч встановлюємо значення «04.05.2016». У нижньому блоці встановлюємо перемикач в позицію «До або дорівнює». У правому полі вписуємо значення «06.05.2016». Перемикач сумісності умов залишаємо в положенні за замовчуванням - «І». Для того, щоб застосувати фільтрацію в дії, тиснемо на кнопку «OK».
  16. Пользвательскій фільтр для формату дати в Microsoft Excel

  17. Як бачимо, наш список ще більше скоротився. Тепер в ньому залишені тільки рядки, в яких сума виручки варіюється від 10000 до 15000 рублів за період з 04.05 по 06.05.2016 включно.
  18. Результати фільтрації за сумою і датою в Microsoft Excel

  19. Ми можемо скинути фільтрацію в одному з стовпців. Зробимо це для значень виручки. Кількома по значку автофильтра у відповідному стовпці. У випадаючому списку клацаємо за пунктом «Видалити фільтр».
  20. Видалення фільтра з одного із стовпців в Microsoft Excel

  21. Як бачимо, після цих дій, вибірка по сумі виручки буде відключена, а залишиться тільки відбір по датах (з 04.05.2016 по 06.05.2016).
  22. Обмеження тільки за датою в Microsoft Excel

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

    Кількома по значку фільтра в найменуванні стовпчика. Послідовно переходимо за найменуваннями списку «Текстові фільтри» і «Настроюваний фільтр ...».

  24. Перехід до текстової фільтрації в Microsoft Excel

  25. Знову відкривається вікно призначеного для користувача автофильтра. Давайте зробимо вибірку за найменуваннями «Картопля» і «М'ясо». У першому блоці перемикач умов встановлюємо в позицію «Так само». В поле праворуч від нього вписуємо слово «Картопля». Перемикач нижнього блоку так само ставимо в позицію «Так само». В поле навпроти нього робимо запис - «М'ясо». І ось далі ми виконуємо те, чого взагалі раніше не робили: встановлюємо перемикач сумісності умов в позицію «АБО». Тепер рядок, що містить будь-який із зазначених умов, буде виводитися на екран. Клацаємо по кнопці «OK».
  26. Пользвательскій фільтр для формату тексту в Microsoft Excel

  27. Як бачимо, в новій вибірці існують обмеження за датою (з 04.05.2016 по 06.05.2016) і по найменуванню (картопля і м'ясо). За сумою виручки обмежень немає.
  28. Обмеження за датою і по найменуванню в Microsoft Excel

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

    Очищення фільтра в Microsoft Excel

    Другий варіант передбачає перехід у вкладку «Головна». Там виконуємо клацання на стрічці по кнопці «Сортування і фільтр» в блоці «Редагування». В активувати списку натискаємо на кнопку «Фільтр».

Очищення фільтра у вкладці Головна в Microsoft Excel

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

Фільтр скинутий в Microsoft Excel

урок: Функція автофильтр в Excel

Спосіб 2: застосування формули масиву

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

  1. На тому ж аркуші створюємо порожню таблицю з такими ж назвами стовпців в шапці, що і у исходника.
  2. Створення порожній таблиці в Microsoft Excel

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

    =ИНДЕКС(A2:A29;НАИМЕНЬШИЙ(ЕСЛИ(15000<=C2:C29;СТРОКА(C2:C29);"");СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1))

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

  4. Введення формули в Microsoft Excel

  5. Так як це формула масиву, то для того, щоб застосувати її в дії, потрібно натискувати не кнопку Enter, а поєднання клавіш Ctrl + Shift + Enter. Робимо це.
  6. Формула масиву введена в стовпець найменувань в Microsoft Excel

  7. Виділивши другий стовпець з датами і встановивши курсор в рядок формул, вводимо такий вираз:

    =ИНДЕКС(B2:B29;НАИМЕНЬШИЙ(ЕСЛИ(15000<=C2:C29;СТРОКА(C2:C29);"");СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1))

    Тиснемо поєднання клавіш Ctrl + Shift + Enter.

  8. Формула масиву введена в стовпець дати в Microsoft Excel

  9. Аналогічним чином в стовпець з виручкою вписуємо формулу такого змісту:

    =ИНДЕКС(C2:C29;НАИМЕНЬШИЙ(ЕСЛИ(15000<=C2:C29;СТРОКА(C2:C29);"");СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1))

    Знову набираємо поєднання клавіш Ctrl + Shift + Enter.

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

  10. Формула масиву введена в стовпець виручки в Microsoft Excel

  11. Як бачимо, таблиця заповнена даними, але зовнішній вигляд її не зовсім привабливий, до того ж, значення дати заповнені в ній некоректно. Потрібно виправити ці недоліки. Некоректність дати пов'язана з тим, що формат осередків відповідного стовпчика загальний, а нам потрібно встановити формат дати. Виділяємо весь стовпець, включаючи осередки з помилками, і натискаємо по виділенню правою кнопкою миші. У списку переходимо по пункту «Формат комірки ...».
  12. Перехід до форматування осередків в Microsoft Excel

  13. У вікні форматування відкриваємо вкладку «Число». У блоці «Числові формати» виділяємо значення «Дата». У правій частині вікна можна вибрати бажаний тип відображення дати. Після того, як налаштування виставлені, тиснемо на кнопку «OK».
  14. Установка формату дати в Microsoft Excel

  15. Тепер дата відображається коректно. Але, як бачимо, вся нижня частина таблиці заповнена осередками, які містять помилкове значення «# ЧИСЛО!». По суті, це ті осередки, даних з вибірки для яких не вистачило. Більш привабливо було б, якби вони відображалися взагалі порожніми. Для цих цілей скористаємося умовним форматуванням. Виділяємо всі елементи таблиці, крім шапки. Перебуваючи у вкладці «Головна» натискаємо на кнопку «Умовне форматування», яка знаходиться в блоці інструментів «Стилі». У списку вибираємо пункт «Створити правило ...».
  16. Перехід до створення правила в Microsoft Excel

  17. У вікні вибираємо тип правила «Форматувати тільки осередки, які містять». У першому полі під написом «Форматувати тільки осередки, для яких виконується така умова» вибираємо позицію «Помилки». Далі тиснемо на кнопку «Формат ...».
  18. Перехід до вибору формату в Microsoft Excel

  19. У запустити вікні форматування переходимо у вкладку «Шрифт» і у відповідному полі вибираємо білий колір. Після цих дій клацаємо по кнопці «OK».
  20. Формат ячеек в Microsoft Excel

  21. На кнопку з точно такою ж назвою тиснемо після повернення в вікно створення умов.

Створення умови форматування в Microsoft Excel

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

Вибірка зроблена в Microsoft Excel

урок: Умовне форматування в Excel

Спосіб 3: вибірка по декількох умовах за допомогою формули

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

  1. Вписуємо в окремому стовпці граничні умови для вибірки.
  2. Умови в Microsoft Excel

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

    =ИНДЕКС(A2:A29;НАИМЕНЬШИЙ(ЕСЛИ(($D$2=C2:C29);СТРОКА(C2:C29);"");СТРОКА(C2:C29)-СТРОКА($C$1))-СТРОКА($C$1))

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

    Щоразу після введення не забуваємо набирати поєднання клавіш Ctrl + Shift + Enter.

  4. Результат вибірки по декільком умовам в Microsoft Excel

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

Зміна результатів вибірки в Microsoft Excel

Спосіб 4: випадкова вибірка

У Ексель за допомогою спеціальної формули СЛЧИС можна також застосовувати випадковий відбір. Його потрібно проводити в деяких випадках при роботі з великим об'ємом даних, коли потрібно представити загальну картину без комплексного аналізу всіх даних масиву.

  1. Зліва від таблиці пропускаємо один стовпець. В осередку у наступній колонці, яка знаходиться навпроти першого осередку з даними таблиці, вписуємо формулу:

    =СЛЧИС()

    Ця функція виводить на екран випадкове число. Для того, щоб її активувати, тиснемо на кнопку ENTER.

  2. Випадкове число в Microsoft Excel

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

  5. Тепер у нас є діапазон комірок, заповнений випадковими числами. Але, він містить в собі формулу СЛЧИС. Нам же потрібно працювати з чистими значеннями. Для цього слід виконати копіювання в порожній стовпець праворуч. Виділяємо діапазон комірок з випадковими числами. Розташувавшись у вкладці «Головна», клацаємо по іконці «Копіювати» на стрічці.
  6. Копіювання в Microsoft Excel

  7. Виділяємо порожній стовпець і натискаємо правою кнопкою миші, викликаючи контекстне меню. У групі інструментів «Параметри вставки» вибираємо пункт «Значення», зображений у вигляді піктограми з цифрами.
  8. Вставка в Microsoft Excel

  9. Після цього, перебуваючи у вкладці «Головна», натискаємо по вже знайомому нам значку «Сортування і фільтр». У випадаючому списку зупиняємо вибір на пункті «настроюється, сортування».
  10. Перехід до настроюваної сортуванні в Microsoft Excel

  11. Активується вікно настройки сортування. Обов'язково встановлюємо галочку навпроти параметра «Мої дані містять заголовки», якщо шапка є, а галочки немає. В поле «Сортувати по» вказуємо найменування того стовпця, в якому містяться скопійовані значення випадкових чисел. В поле «Сортування» залишаємо налаштування за замовчуванням. В поле «Порядок» можна вибрати параметр як «По зростанню», так і «За спаданням '. Для випадкової вибірки це значення не має. Після того, як налаштування зроблені, тиснемо на кнопку «OK».
  12. Налаштування сортування в Microsoft Excel

  13. Після цього всі значення таблиці шикуються в порядку зростання або зменшення випадкових чисел. Можна взяти будь-яку кількість перших рядків з таблиці (5, 10, 12, 15 і т.п.) і їх можна буде вважати результатом випадкової вибірки.

Випадкова вибірка в Microsoft Excel

урок: Сортування і фільтрація даних в Excel

Як бачимо, вибірку в таблиці Excel можна зробити, як за допомогою автофільтра, так і застосувавши спеціальні формули. У першому випадку результат буде виводитися в вихідну таблицю, а в другому - в окрему область. Є можливість проводити відбір, як по одній умові, так і за кількома. Крім того, можна здійснювати випадкову вибірку, використавши функцію СЛЧИС.