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

Зміст

• Виконання вибірки
  • Спосіб 1: Застосування розширеного автофільтра
  • Спосіб 2: застосування формули масиву
  • Спосіб 3: вибірка за кількома Умовами за допомогою формули
  • Спосіб 4: випадкова вибірка
• Питання та відповіді

При роботі з таблицями 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. Зліва від таблиці пропускаємо один стовпець. В осередку наступного стовпця, яка знаходиться навпроти першого осередку з даними таблиці, вписуємо формулу:

   =SLCHIS()

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


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

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

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

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

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

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

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

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