В Excel, ако имате списък със стойности и не можете да намерите точно съвпадение, тогава трябва да намерите или Най -близък по -голям или Най -близък по -малък стойност. Например, ако правите сравнение на числа и не намирате точно съвпадение в диапазона, можете да използвате следващото по -голямо или по -малко число и да продължите напред.
Можем да използваме комбинация от Countif, Голям и Малка формула за получаване на нашата продукция.
Countif се използва за преброяване на броя повторения на конкретни данни в диапазон от клетки.
Синтаксис = COUNTIF (диапазон, критерии)
Голям: Връща k-тата най-голяма стойност в набор от данни.
Синтаксис = LARGE (масив, k)
Малък: Връща k-тата най-малка стойност в набор от данни.
Синтаксис = МАЛКИ (масив, k)
Нека разберем кое е най -близкото по -голямо и по -малко число
Имаме списък със случайни числа 1,2,3,5,8,10 и се нуждаем от формула за оценка на следното:
- Ако k е в списъка, върнете k
- Ако k не е в списъка, върнете следващата по -висока стойност. Например, ако k = 7, върнете 8 (в случай на най -близкото по -голямо число)
- Ако k не е в списъка, върнете следващата по -малка стойност. Например, ако k = 7, върнете 5
(в случай на най -близкото по -малко число)
Нека вземем пример, имаме някои случайни числа в колона А и трябва да намерим стойността в този диапазон, който е Най -близък по -малък или Най -близък по -голям към клетка В2, която съдържа 46. Този номер не е наличен в нашия диапазон. Вижте екранната снимка по -долу
Нека да видим как изчисляваме Най -близкото по -голямо число първо:
Можем ръчно да проверим следващата най -голяма стойност до 46, която е 54.
Сега нека се опитаме да получим същия резултат, използвайки формула -
- Ще използваме комбинация от ГОЛЯМ & БРОЙ функции за генериране на най -близкото по -голямо число.
- Критериите са по -голямо от „>“ знак с Lookup Value, т.е. клетка B2
- Знакът по -голям, комбиниран със стойността за търсене, ще върне 5 стойности, т.е. 54, 57, 61, 74, 83, които са по -големи от 46.
- Когато обединим Големите и контрафункциите, ще получим 54. Вижте формулата в клетки G2 и G3 в снимката по -долу.
PS: B2 в Countifformula е стойността за търсене, т.е. 46
- В клетка C2, формулата, използвана за намиране Най -близък по -голям номер е = ГОЛЯМ ($ A $ 2: $ A $ 8, COUNTIF ($ A $ 2: $ A $ 8, ">" & B2))
Сега нека видим как изчисляваме Най -близкото по -малко число -
Можем ръчно да проверим следващата по -малка стойност до 46, която е 28.
Нека получим същия изход, използвайки формула -
- Ще използваме комбинация от МАЛКИ И БРОЙНИфункции за генериране на най -близкото по -малко число.
- Критериите са по -малко от "<" с Lookup Value, т.е. клетка B2
- По -малък от знак, комбиниран със стойността за търсене, ще върне 2 стойности, т.е. 16 и 28, които са по -малки от 46.
- Когато обединим Small & Countiffunctions, получаваме 28. Вижте формулата в клетки G5 и G6 в снимката по -долу.
Вижте екранна снимка по -долу с формула:
Забележка: клетка В2 в CountifFormula е стойността за търсене, т.е. 46
- В клетка D2, формулата, използвана за намиране Най -близък по -малък номер е = МАЛКИ ($ A $ 2: $ A $ 8, COUNTIF ($ A $ 2: $ A $ 8, "<" & B2))
Това е начинът, по който можем да върнем най -близкия по -голям номер и по -малък номер в килера.
