Вибірка даних в Microsoft Excel

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

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

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

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

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

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

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

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

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

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

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

6. Тепер в таблиці залишилися тільки рядки, в яких сума виручки не менше 10000 рублів, але не перевищує 15000 рублів.
7. Аналогічно можна налаштовувати фільтри і в інших стовпцях. При цьому є можливість зберігати також фільтрацію і за попередніми умовами, які були задані в колонках. Отже, подивимося, як проводиться відбір за допомогою фільтра для осередків в форматі дати. Кількома по значку фільтрації у відповідному стовпці. Послідовно натискаємо по пунктам списку «Фільтр за датою» і «Настроюваний фільтр».
8. Знову запускається вікно призначеного для користувача автофильтра. Виконаємо відбір результатів в таблиці з 4 по 6 травня 2016 року включно. У перемикачі вибору умов, як бачимо, ще більше варіантів, ніж для числового формату. Вибираємо позицію «Після або дорівнює». В поле праворуч встановлюємо значення «04.05.2016». У нижньому блоці встановлюємо перемикач в позицію «До або дорівнює». У правому полі вписуємо значення «06.05.2016». Перемикач сумісності умов залишаємо в положенні за замовчуванням - «І». Для того, щоб застосувати фільтрацію в дії, тиснемо на кнопку «OK».
9. Як бачимо, наш список ще більше скоротився. Тепер в ньому залишені тільки рядки, в яких сума виручки варіюється від 10000 до 15000 рублів за період з 04.05 по 06.05.2016 включно.
10. Ми можемо скинути фільтрацію в одному з стовпців. Зробимо це для значень виручки. Кількома по значку автофильтра у відповідному стовпці. У випадаючому списку клацаємо за пунктом «Видалити фільтр».
11. Як бачимо, після цих дій, вибірка по сумі виручки буде відключена, а залишиться тільки відбір по датах (з 04.05.2016 по 06.05.2016).
12. В даній таблиці є ще одна колонка - «Найменування». У ній містяться дані в текстовому форматі. Подивимося, як сформувати вибірку за допомогою фільтрації за цими значеннями.

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

13. Знову відкривається вікно призначеного для користувача автофильтра. Давайте зробимо вибірку за найменуваннями «Картопля» і «М'ясо». У першому блоці перемикач умов встановлюємо в позицію «Так само». В поле праворуч від нього вписуємо слово «Картопля». Перемикач нижнього блоку так само ставимо в позицію «Так само». В поле навпроти нього робимо запис - «М'ясо». І ось далі ми виконуємо те, чого взагалі раніше не робили: встановлюємо перемикач сумісності умов в позицію «АБО». Тепер рядок, що містить будь-який із зазначених умов, буде виводитися на екран. Клацаємо по кнопці «OK».
14. Як бачимо, в новій вибірці існують обмеження за датою (з 04.05.2016 по 06.05.2016) і по найменуванню (картопля і м'ясо). За сумою виручки обмежень немає.
15. Повністю видалити фільтр можна тими ж способами, які використовувалися для його установки. Причому неважливо, який саме спосіб застосовувався. Для скидання фільтрації, перебуваючи у вкладці «Дані» клацаємо по кнопці «Фільтр», яка розміщена в групі «Сортування і фільтр».

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

6. Як бачимо, таблиця заповнена даними, але зовнішній вигляд її не зовсім привабливий, до того ж, значення дати заповнені в ній некоректно. Потрібно виправити ці недоліки. Некоректність дати пов'язана з тим, що формат осередків відповідного стовпчика загальний, а нам потрібно встановити формат дати. Виділяємо весь стовпець, включаючи осередки з помилками, і натискаємо по виділенню правою кнопкою миші. У списку переходимо по пункту «Формат комірки ...».
7. У вікні форматування відкриваємо вкладку «Число». У блоці «Числові формати» виділяємо значення «Дата». У правій частині вікна можна вибрати бажаний тип відображення дати. Після того, як налаштування виставлені, тиснемо на кнопку «OK».
8. Тепер дата відображається коректно. Але, як бачимо, вся нижня частина таблиці заповнена осередками, які містять помилкове значення «# ЧИСЛО!». По суті, це ті осередки, даних з вибірки для яких не вистачило. Більш привабливо було б, якби вони відображалися взагалі порожніми. Для цих цілей скористаємося умовним форматуванням. Виділяємо всі елементи таблиці, крім шапки. Перебуваючи у вкладці «Головна» натискаємо на кнопку «Умовне форматування», яка знаходиться в блоці інструментів «Стилі». У списку вибираємо пункт «Створити правило ...».
9. У вікні вибираємо тип правила «Форматувати тільки осередки, які містять». У першому полі під написом «Форматувати тільки осередки, для яких виконується така умова» вибираємо позицію «Помилки». Далі тиснемо на кнопку «Формат ...».
10. У запустити вікні форматування переходимо у вкладку «Шрифт» і у відповідному полі вибираємо білий колір. Після цих дій клацаємо по кнопці «OK».
11. На кнопку з точно такою ж назвою тиснемо після повернення в вікно створення умов.

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

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

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

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

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

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

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

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

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

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

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

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

   =СЛЧИС()

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

2. Для того, щоб зробити цілий стовпець випадкових чисел, встановлюємо курсор в нижній правий кут комірки, яка вже містить формулу. З'являється маркер заповнення. Простягаємо його вниз з затиснутою лівою кнопкою миші паралельно таблиці з даними до її кінця.
3. Тепер у нас є діапазон комірок, заповнений випадковими числами. Але, він містить в собі формулу СЛЧИС. Нам же потрібно працювати з чистими значеннями. Для цього слід виконати копіювання в порожній стовпець праворуч. Виділяємо діапазон комірок з випадковими числами. Розташувавшись у вкладці «Головна», клацаємо по іконці «Копіювати» на стрічці.
4. Виділяємо порожній стовпець і натискаємо правою кнопкою миші, викликаючи контекстне меню. У групі інструментів «Параметри вставки» вибираємо пункт «Значення», зображений у вигляді піктограми з цифрами.
5. Після цього, перебуваючи у вкладці «Головна», натискаємо по вже знайомому нам значку «Сортування і фільтр». У випадаючому списку зупиняємо вибір на пункті «настроюється, сортування».
6. Активується вікно настройки сортування. Обов'язково встановлюємо галочку навпроти параметра «Мої дані містять заголовки», якщо шапка є, а галочки немає. В поле «Сортувати по» вказуємо найменування того стовпця, в якому містяться скопійовані значення випадкових чисел. В поле «Сортування» залишаємо налаштування за замовчуванням. В поле «Порядок» можна вибрати параметр як «По зростанню», так і «За спаданням '. Для випадкової вибірки це значення не має. Після того, як налаштування зроблені, тиснемо на кнопку «OK».
7. Після цього всі значення таблиці шикуються в порядку зростання або зменшення випадкових чисел. Можна взяти будь-яку кількість перших рядків з таблиці (5, 10, 12, 15 і т.п.) і їх можна буде вважати результатом випадкової вибірки.

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

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

• ← Застосування інтерполяції в Microsoft Excel
• Застосування табулирования функції в Microsoft Excel →