Извличане на най -близките по -големи и по -малки стойности от списък в Microsoft Excel

Anonim

В 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))

Това е начинът, по който можем да върнем най -близкия по -голям номер и по -малък номер в килера.