Як зробити вибірку в 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
• Завантажити приклади