Як зробити вибірку в Excel за допомогою формул масиву

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

Як зробити вибірку в Excel за умовою

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

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

Інший спосіб розв'язання – використання формули масиву. Відповідні запиту рядки помістяться до окремого звіту-таблиці.

Спочатку створюємо порожню таблицю поруч із вихідною: дублюємо заголовки, кількість рядків та стовпців. Нова таблиця займає діапазон Е1: G10. Тепер виділяємо Е2: Е10 (стовпець «Дата») і вводимо таку формулу: < >.

Щоб вийшла формула масиву, натискаємо клавіші Ctrl + Shift + Enter. У сусідній стовпець - "Товар" - вводимо аналогічну формулу масиву: <>. Змінився лише перший аргумент функції ІНДЕКС.

У стовпець «Ціна» введемо таку саму формулу масиву, змінивши перший аргумент функції ІНДЕКС.

В результаті отримуємо звіт по товарах із ціною більше 200 рублів.

Така вибірка є динамічною: при зміні запиту або появі у вихідній таблиці нових товарів автоматично зміниться звіт.

Завдання №2 – вибрати з вихідної таблиці товари, що надійшли у продаж 20.09.2015. Тобто критерій відбору – дата.Для зручності шукану дату введемо в окрему комірку, I2.

Для розв'язання задачі використовується аналогічна формула масиву. Тільки замість критерію.

Подібні формули вводяться й у інші стовпці (принцип див. вище).

Тепер використовуємо текстовий критерій. Замість дати в осередок I2 введемо текст «Товар 1». Трохи змінимо формулу масиву: <>.

Така велика функція вибірки Excel.

Вибірка за кількома умовами в Excel

Спочатку візьмемо два числові критерії:

Завдання - відібрати товари, які коштують менше ніж 400 і більше 200 рублів. Об'єднаємо умови знаком «*». Формула масиву виглядає наступним чином: < = C2: C10); РЯДКУ (C2: C10); "");

Це для першого стовпця таблиці-звіту. Для другого та третього – змінюємо перший аргумент функції ІНДЕКС. Результат:

Щоб зробити вибірку за кількома датами або числовими критеріями, використовуємо аналогічні формули масиву.

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

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

Вихідний набір даних:

Спочатку вставимо зліва два порожні стовпці. У комірку А2 впишемо формулу СЛЧИС (). Розмножимо її на весь стовпець:

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

Щоб вставити значення, а не формула, клацаємо правою кнопкою миші по стовпцю В і вибираємо інструмент «Спеціальна вставка». У вікні ставимо галочку навпроти пункту «Значення»:

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

• Створити таблицю
• Форматування
• Функції Excel
• Формули та діапазони
• Фільтр та сортування
• Діаграми та графіки
• Зведені таблиці
• Друк документів
• Бази даних та XML
• Можливості Excel
• Налаштування параметри
• Уроки Excel
• Макроси VBA
• Завантажити приклади

Як зробити вибірку в Excel зі списку з умовним форматуванням

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

Вибір унікальних і повторюваних значень в Excel

Наприклад візьмемо історію взаєморозрахунків із контрагентами, як показано малюнку:

У цій таблиці нам потрібно виділити кольором всі транзакції по конкретному клієнту. Для перемикання між клієнтами будемо використовувати список, що випадає. Тому в першу чергу слід підготувати зміст для списку, що випадає. Нам потрібні всі прізвища клієнтів зі стовпця A, без повторень.

Перед тим як вибрати унікальні значення в Excel, підготуємо дані для списку:

1. Виділіть перший стовпець таблиці A1: A19.
2. Виберіть інструмент: «ДАНІ»-«Сортування та фільтр»-«Додатково».
3. У вікні «Розширений фільтр» увімкніть «скопіювати результат в інше місце», а в полі «Помістити результат у діапазон:» вкажіть $F$1.
4. Позначте галочкою пункт "Тільки унікальні записи" та натисніть ОК.

В результаті ми отримали список даних із унікальними значеннями (прізвища без повторень).

Тепер нам потрібно трохи модифікувати нашу вихідну таблицю. Виділіть перші 2 рядки та оберіть інструмент: «ГОЛОВНА»-«Отвори»-«Вставити» або натисніть комбінацію гарячих клавіш CTRL+SHIFT+=.

У нас додалося 2 порожні рядки. Тепер у комірку A1 введіть значення «Клієнт:».

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

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

1. Перейдіть до комірки B1 і виберіть інструмент «ДАНІ»-«Робота з даними»-«Перевірка даних».
2. На вкладці «Параметри» у розділі «Умова перевірки» зі списку «Тип даних:» виберіть «Список».
3. У полі введення «Джерело» введіть =$F$4:$F$8 і натисніть ОК.

В результаті в осередку B1 ми створили список прізвищ клієнтів, що випадають.

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

Вибірка осередків із таблиці за умовою в Excel:

1. Виділіть табличну частину вихідної таблиці взаєморозрахунків A4:D21 і виберіть інструмент: «ГОЛОВНА»-«Стилі»-«Умовне форматування»-«Створити правило»-«Використовувати формулу для визначення осередків, що форматуються».
2. Щоб вибрати унікальні значення зі стовпця, введіть у поле введення формулу: =$A4=$B$1 і натисніть на кнопку «Формат», щоб виділити однакові комірки кольором. Наприклад, зеленим. І натисніть кнопку ОК на всіх відкритих вікнах.

Як працює вибірка унікальних значень Excel? При виборі будь-якого значення (прізвища) зі списку B1, в таблиці підсвічуються кольором всі рядки, які містять це значення (прізвище). Щоб переконатися в цьому списку B1, виберіть інше прізвище. Після цього автоматично будуть виділені кольором вже інші рядки. Таку таблицю тепер легко читати та аналізувати.

Принцип дії автоматичного підсвічування рядків за критерієм запиту є дуже простим. Кожне значення у стовпці A порівнюється зі значенням у комірці B1. Це дозволяє знайти унікальні значення у таблиці Excel. Якщо дані збігаються, тоді формула повертає значення ІСТИНА і для цілого рядка автоматично надається новий формат. Щоб формат присвоювався для цілого рядка, а не тільки осередку в стовпці A, ми використовуємо змішане посилання у формулі = $ A4.

• Створити таблицю
• Форматування
• Функції Excel
• Формули та діапазони
• Фільтр та сортування
• Діаграми та графіки
• Зведені таблиці
• Друк документів
• Бази даних та XML
• Можливості Excel
• Налаштування параметри
• Уроки Excel
• Макроси VBA
• Завантажити приклади

Вибір значень із таблиці Excel за умовою

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

Як зробити вибірку в Excel за умовою

Щоб визначити відповідні значення першому найменшому числу, потрібна вибірка з таблиці за умовою. Допустимо ми хочемо дізнатися перший найдешевший товар на ринку з даного прайсу:

Автоматичну вибірку реалізує нам формула, яка матиме наступну структуру:

У місці «діапазон_даних_для_вибірки» слід вказати область значень A6:A18 для вибірки з таблиці (наприклад, текстових), з яких функція ІНДЕКС вибере одне результуюче значення. Аргумент «діапазон» означає область осередків із числовими значеннями, з яких слід вибрати перше найменше число. В аргументі «заголовок_стовпця» для другої функції РЯДКУ, слід зазначити посилання на комірку із заголовком стовпця, який містить діапазон числових значень.

Звичайно цю формулу слід виконувати в масиві. Тому для підтвердження її введення слід натискати не просто клавішу Enter, а комбінацію клавіш CTRL+SHIFT+Enter. Якщо все зроблено правильно, у рядку формул з'являться фігурні дужки.

Зверніть увагу нижче на малюнок, де в клітинку B3 була введена дана формула в масиві:

Вибір відповідного значення з першим найменшим числом:

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

Як працює вибірка за умовою

Ключову роль тут відіграє функція ІНДЕКС. Її номінальне завдання – це вибирати з вихідної таблиці (вказується у першому аргументі – A6:A18) значення, відповідні певним числам. ІНДЕКС працює з урахуванням критеріїв, визначених у другому (номер рядка всередині таблиці) та третьому (номер стовпця в таблиці) аргументах.Так як наша вихідна таблиця A6:A18 має лише 1 стовпець, то третій аргумент функції ІНДЕКС ми не вказуємо.

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

Функція ЯКЩО дозволяє вибрати значення зі списку за умовою. У її першому аргументі вказано, де перевіряється кожна осередок в діапазоні B6:B18 на наявність найменшого числового значення: ЕСЛИB6:B18=МІНB6:B18 Таким чином у пам'яті програми створюється масив з логічних значень БРЕХНЯ. У нашому випадку 3 елементи масиву будуть утримувати. значення ІСТИНА, так як мінімальне значення 8 містить ще 2 дублікати в стовпці B6: B18.

Наступний крок - це визначення в яких рядках діапазону знаходиться кожне мінімальне значення. номерів віднімається номер проти першого рядка таблиці – B5, тобто число 5. Це робиться тому, що функція ІНДЕКС працює з номерами всередині таблиці, а не з номерами робочого аркуша Excel. 5-й рядок аркуша означає кожен рядок таблиці буде на 5 менше ніж відповідний рядок аркуша.

Після того, як будуть відібрані всі мінімальні значення та зіставлені всі номери рядків таблиці, функція МІН вибере найменший номер рядка.Цей рядок міститиме перше найменше число, яке зустрічається в стовпці B6:B18. На підставі цього номера рядка функції ІНДЕКС вибере відповідне значення таблиці A6:A18. У результаті формула повертає це значення в комірку B3 як результат обчислення.

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

Зрозумівши принцип дії формули, тепер можна її модифікувати і налаштовувати під інші умови. Наприклад, формулу можна змінити так, щоб вибрати перше максимальне значення в Excel:

Якщо необхідно змінити умови формули так, щоб можна було в Excel вибрати першу максимальну, але менше ніж 70:

Як Excel вибрати перше мінімальне значення крім нуля:

Як легко помітити, ці формули відрізняються між собою лише функціями МІН та МАКС та їх аргументами.

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

• Створити таблицю
• Форматування
• Функції Excel
• Формули та діапазони
• Фільтр та сортування
• Діаграми та графіки
• Зведені таблиці
• Друк документів
• Бази даних та XML
• Можливості Excel
• Налаштування параметри
• Уроки Excel
• Макроси VBA
• Завантажити приклади