Знайти найближчий номер

зміст

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

Розрахунок знижки в залежності від обсягу.
Розрахунок розміру премій залежно від виконання плану.
Розрахунок тарифів на доставку в залежності від відстані.
Підбір відповідної тари для товарів тощо.

Причому округлення може знадобитися як в більшу, так і в меншу сторону, в залежності від ситуації.

Існує кілька способів – очевидних і не дуже – вирішити таку проблему. Розглянемо їх по черзі.

Для початку уявімо постачальника, який надає знижки на опт, і відсоток знижки залежить від кількості товару, що купується. Наприклад, при покупці більше 5 штук надається знижка 2%, а при покупці від 20 штук – вже 6% і т.д.

Як швидко і красиво порахувати відсоток знижки при введенні кількості купленого товару?

Спосіб 1: Вкладені IF

Метод із серії «Що тут думати – треба стрибати!». Використання вкладених функцій IF (ЯКЩО) щоб послідовно перевірити, чи потрапляє значення комірки в кожен з інтервалів, і відобразити знижку для відповідного діапазону. Але формула в цьому випадку може виявитися дуже громіздкою:

Знаходження найближчого числа

Я вважаю очевидним, що налагодити таку «ляльку-монстра» або спробувати додати пару нових умов через деякий час — це весело.

Крім того, Microsoft Excel має обмеження вкладеності для функції IF – 7 разів у старих версіях і 64 рази в новіших версіях. А якщо вам потрібно більше?

Спосіб 2. VLOOKUP з переглядом інтервалів

Цей спосіб набагато компактніше. Для розрахунку відсотка знижки скористайтеся легендарною функцією VPR (ВПР) в режимі орієнтовного пошуку:

Знаходження найближчого числа

де

B4 – вартість кількості товару в першій угоді, для якої ми шукаємо знижку
$G$4:$H$8 – посилання на таблицю знижок – без «шапки» та з адресами, фіксованими знаком $.
2 — порядковий номер стовпця в таблиці знижок, з якого ми хочемо отримати значення знижки
ІСТИНА – тут «собака» зарита. If як останній аргумент функції VPR вкажіть БРЕХНЯ (ПОМИЛКОВИЙ) або 0, тоді функція буде шукати суворий збіг у стовпці кількості (і в нашому випадку це дасть помилку #N/A, оскільки в таблиці знижок немає значення 49). Але якщо замість цього БРЕХНЯ запис ІСТИНА (ПРАВДА) або 1, то функція буде шукати не точне, а найближчий найменший вартість і дасть нам потрібний відсоток знижки.

Мінусом цього методу є необхідність сортувати таблицю знижок у порядку зростання за першим стовпцем. Якщо такого сортування немає (або вона виконується у зворотному порядку), то наша формула працювати не буде:

Знаходження найближчого числа

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

Спосіб 3. Знаходження найближчого найбільшого за допомогою функцій INDEX і MATCH

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

Функція VLOOKUP тут не допоможе, тому доведеться використовувати її аналог – купу функцій INDEX (ІНДЕКС) і БІЛЬШ ВИКРИТИЙ (МАТЧ):

Знаходження найближчого числа

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

Спосіб 4. Нова функція VIEW (XLOOKUP)

Якщо у вас версія Office 365 з усіма встановленими оновленнями, то замість VLOOKUP (ВПР) можна скористатися її аналогом – функцією ПЕРЕГЛЯД (XLOOKUP), які я вже детально аналізував:

Знаходження найближчого числа

тут:

B4 – початкове значення кількості товару, на який ми шукаємо знижку
$G$4:$G$8 – діапазон, де ми шукаємо збіги
$H$4:$H$8 – діапазон результатів, з яких ви хочете повернути знижку
четвертий аргумент (-1) містить пошук найближчого найменшого числа, яке нам потрібно, замість точного збігу.

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

Але, на жаль, поки що не всі мають таку можливість – лише щасливі власники Office 365.

Спосіб 5. Power Query

Якщо ви ще не знайомі з потужною та абсолютно безкоштовною надбудовою Power Query для Excel, тоді вам сюди. Якщо ви вже знайомі, то давайте спробуємо використати його для вирішення нашої проблеми.

Спочатку проведемо підготовчу роботу:

Давайте перетворимо наші вихідні таблиці на динамічні (розумні) за допомогою комбінації клавіш Ctrl+T або команда Домашня сторінка – Формат у вигляді таблиці (Домашня сторінка — форматувати як таблицю).
Для наочності назвемо їх. Sales и знижки таб Конструктор (Дизайн).
Завантажте кожну з таблиць по черзі в Power Query за допомогою кнопки З таблиці/діапазону таб дані (Дані — з таблиці/діапазону). В останніх версіях Excel ця кнопка була перейменована на З листям (З аркуша).
Якщо таблиці мають різні назви стовпців із кількістю, як у нашому прикладі («Кількість товарів» і «Кількість від…»), тоді їх потрібно перейменувати в Power Query та назвати однаково.
Після цього ви можете повернутися до Excel, вибравши команду у вікні редактора Power Query Головна — Закрийте та завантажте — Закрийте та завантажте в… (Додому — Закрити&Завантажити — Закрити&Завантажити до…) а потім варіант Просто створіть зв'язок (тільки створити з'єднання).
Далі починається найцікавіше. Якщо у вас є досвід роботи з Power Query, то я припускаю, що подальший напрямок думок має бути в напрямку об’єднання цих двох таблиць за допомогою запиту на об’єднання (злиття) а-ля VLOOKUP, як це було в попередньому методі. Насправді нам потрібно буде злити в режимі додавання, що зовсім не очевидно на перший погляд. Виберіть у вкладці Excel Дані – Отримати дані – Об’єднати запити – Додати (Дані — Отримати дані — Об’єднати запити — Додати) а потім наші столи Sales и знижки у вікні, що з'явиться:
Натиснувши на OK наші столи будуть склеєні в єдине ціле – один під одним. Зверніть увагу, що колонки з кількістю товару в цих таблицях підпали один під одного, т.к. вони мають однакові назви:
Якщо для вас важлива вихідна послідовність рядків в таблиці продажів, то щоб після всіх наступних перетворень ви потім могли її відновити, додайте в нашу таблицю пронумерований стовпець за допомогою команди Додавання стовпця – стовпець індексу (Додати стовпець — стовпець індексу). Якщо послідовність рядків для вас не має значення, то можете пропустити цей крок.
Тепер, використовуючи розкривний список у заголовку таблиці, відсортуйте її за стовпцями Кількість За зростанням:
І головний трюк: клацніть правою кнопкою миші на заголовку стовпця Знижка вибрати команду Заливка – вниз (Заливка — Вниз). Порожні клітинки с нулю автоматично заповнюється попередніми значеннями знижки:
Залишається відновити вихідну послідовність рядків шляхом сортування по стовпцях індекс (ви можете сміливо видалити його пізніше) і позбутися непотрібних рядків за допомогою фільтра нулю за стовпцем Код транзакції: