Načítanie najbližších väčších a menších hodnôt zo zoznamu v programe Microsoft Excel

Anonim

Ak máte v programe Excel zoznam hodnôt a nemôžete nájsť presnú zhodu, musíte nájsť buď Najbližšie väčšie alebo Najbližšie menšie hodnotu. Ak napríklad robíte porovnanie čísel a v rozsahu nenájdete presnú zhodu, môžete použiť ďalšie väčšie alebo menšie číslo a pokračovať ďalej.

Môžeme použiť kombináciu Gróf, veľký a Malé vzorec na získanie nášho výstupu.

Countif sa používa na spočítanie počtu opakovaní konkrétnych údajov v rozsahu buniek.

Syntax = COUNTIF (rozsah, kritériá)

Veľký: Vráti k-tú najväčšiu hodnotu v množine údajov.

Syntax = LARGE (pole, k)

Malý: Vráti k-tú najmenšiu hodnotu v množine údajov.

Syntax = SMALL (pole, k)

Poďme pochopiť, čo je najbližšie väčšie a menšie číslo

Máme zoznam s náhodnými číslami 1,2,3,5,8,10 a potrebujeme vzorec na vyhodnotenie nasledujúceho:

- Ak je k v zozname, vráťte k

- Ak k nie je v zozname, vráťte najbližšiu vyššiu hodnotu. Ak je napríklad k = 7, vráťte 8 (v prípade najbližšieho väčšieho čísla)

- Ak k nie je v zozname, vráťte nasledujúcu menšiu hodnotu. Ak je napríklad k = 7, vráťte 5

(v prípade najbližšieho menšieho počtu)

Zoberme si príklad, v stĺpci A máme niekoľko náhodných čísel a musíme nájsť hodnotu v tomto rozsahu, čo je Najbližšie menšie alebo Najbližšie väčšie do bunky B2, ktorá obsahuje 46. Toto číslo nie je v našom sortimente k dispozícii. Pozrite sa na nižšie uvedenú snímku obrazovky

Pozrime sa, ako vypočítame Najbližšie väčšie číslo najprv:

Nasledujúcu najväčšiu hodnotu môžeme manuálne skontrolovať na 46, čo je 54.

Skúsme teraz dosiahnuť rovnaký výsledok pomocou vzorca -

  • Použijeme kombináciu VEĽKÉ A COUNTIF funkcie na generovanie najbližšieho väčšieho čísla.
  • Kritériá sú väčšie ako „>“ podpíšte pomocou vyhľadávacej hodnoty, tj. bunky B2
  • Znak väčší ako v kombinácii s hodnotou vyhľadávania vráti 5 hodnôt, tj. 54, 57, 61, 74, 83, ktoré sú väčšie ako 46.
  • Keď zlúčime funkcie Veľké a Počet, dostaneme 54. Pozrite sa na vzorec v bunkách G2 a G3 na obrázku nižšie.

PS: B2 v Countifformula je vyhľadávacia hodnota, tj. 46

  • V bunke C2 vzorec použitý na nájdenie Najbližšie väčšie číslo je = VEĽKÉ ($ A $ 2: $ A $ 8, COUNTIF ($ A $ 2: $ A $ 8, ">" & B2))

Teraz sa pozrime, ako vypočítame Najbližšie menšie číslo -

Nasledujúcu menšiu hodnotu môžeme manuálne skontrolovať na 46, čo je 28.

Umožňuje získať rovnaký výkon pomocou vzorca -

  • Použijeme kombináciu MALÉ & COUNTIFfunkcie na generovanie najbližšieho menšieho čísla.
  • Kritériá sú najmenej „<“ s hodnotou vyhľadávania, tj. bunkou B2
  • Znak menší ako znak v kombinácii s hodnotou vyhľadávania vráti 2 hodnoty, tj. 16 a 28, ktoré sú menšie ako 46.
  • Keď zlúčime malé a početné funkcie, dostaneme 28. Pozrite sa na vzorec v bunkách G5 a G6 na obrázku nižšie.

Pozrite si nižšie uvedenú snímku obrazovky so vzorcom:

Poznámka: bunka B2 v CountifFormula je vyhľadávacia hodnota, tj. 46

  • V bunke D2 vzorec použitý na nájdenie Najbližšie menšie číslo je = MALÉ ($ A $ 2: $ A $ 8, COUNTIF ($ A $ 2: $ A $ 8, „<“ a B2))

Takto môžeme vrátiť najbližšie väčšie číslo a zavrieť menšie číslo.