Пошук найближчого значення excel за допомогою формули
Кожен користувач Excel без проблем може знайти найменше або найбільше значення в діапазоні чисел, використовуючи для цього функції: = МІН (), = МАКС () або = НАЙМЕНШИЙ () і = НАЙБІЛЬШИЙ (). Так само легко знайти номер позиції початкового значення в діапазоні осередків за допомогою функції = ПОИСКПОЗ (). Але в даному прикладі буде більш цікаве рішення, яке дозволяє виконати пошук найближчого значення в Excel. Створимо формулу, яка здатна знаходити найбільш наближене значення до відповідності запиту користувача. Наприклад, несли діапазон даних не містить значень для точного збігу з запитом користувача то функція ПОИСКПОЗ повертає помилку # Н / Д. Але користувача цілком влаштував би і наближений результат, не залежно від того чи буде він трохи менше або трохи більше відповідати запиту. Важливою перевагою такої формули полягає в тому, що немає необхідності використовувати умовну сортування для вирішення такого роду завдань.
Як знайти найближче значення в Excel?
Візьмемо для прикладу, конкретну ситуацію. Фірма переводить склад на нове місце, і щоб повністю заповнити фуру товарами з однаковим об'ємом упаковок (наприклад, офісний папір для принтера формат A4 по 500 аркушів) потрібно вкласти ще 220 пачці. Але бажано не перемішувати асортимент продукції. Тобто потрібно постаратися уникнути пересорта товарів при переїзді на новий склад. Заповнимо залишився порожнє місце в фурі пачками офісного паперу одного і того ж типу стандарту якості. У нас є залишки по товарах всіх асортиментів:
Нам потрібно виконати пошук найближчого меншого значення Excel. Щоб знайти асортимент з найбільш підходящим кількістю по залишкам (не більше 220 шт.) Створюємо формулу:
- В осередку E2 введіть значення 220 - це кількість пачок офісного паперу, яке відповідає для заповнення вільного об'єму в фурі.
- В осередку E3 вводимо формулу: Подібну формулу можна використовувати для пошуку найближчого значення в діапазоні Excel, а не тільки в одному стовпці.
- Для підтвердження введення формули натискаємо комбінацію клавіш CTRL + SHIFT + Enter, так як формула повинна виконуватися в масиві. Якщо ви зробили все правильно, то в рядку формул ви повинні помітити фігурні дужки.
Результат обчислення формули для пошуку найбільш наближене значення:
В результаті поїде комплект одного асортименту паперу тип-9 (195шт.). Так як його кількість на залишках найбільш наближено відповідає обсягу в 220 пачок. Фура буде максимально можливо заповнена, а на складі буде менше пересорта за асортиментом товарів.
Принцип пошуку найближчого значення за формулою:
З кожного числа залишків в діапазоні осередків B3: B12 віднімається вихідне значення в осередку E2. Таким чином створюється умовна таблиця значень рівних цій різниці, з розміром відповідає числу осередків в діапазоні B3: B12. Функція ABS повертає абсолютну величину числа по модулю і в цій же умовної таблиці замінює все значення негативних чисел на позитивні (без знака мінус). З отриманих даних знаходимо найменше значення за допомогою функції = МІН (). А функція = ПОИСКПОЗ () повертає нам позицію в умовній таблиці для найменшого значення знайденого функцій МІН. Отриманий результат обчислення функцією ПОИСКПОЗ є аргументом для функції = ІНДЕКС (), яка повертає нам значення осередки знаходяться під номером позиції в діапазоні B3: B12.
Інші можливості Excel для пошуку значень
Дуже часто працівникам офісу доводиться затримуватися на роботі при підготовки складних звітів в Excel. Але деякі співробітники примудряються підготувати швидше звіти подібні за складністю. Майстерність - це отримання великих результатів при додатку менших зусиль. У чому ж секрет майстерності Excel? Відповідь ховається в готових рішеннях, які дозволяють вирішувати одні й ті ж завдання в Excel, але значно швидше.
Крім того, будуть представлені допоміжні інструменти Excel стосуються пошуку інформації. Наприклад, виділення цікавлять нас значень за допомогою умовного форматування. Розглянемо також можливості автофільтра, за допомогою якого можна швидко відобразити на екрані тільки потрібні нам дані. Ви дізнаєтеся, як в кілька кліків мишки відкривати необхідні на даний момент діапазони даних без використання багаторазово повторюваних тих же самих дій, пов'язаних зі складним фільтруванням.