Pokud v aplikaci Excel máte seznam hodnot a nemůžete najít přesnou shodu, musíte najít buď Nejbližší větší nebo Nejbližší menší hodnota. Pokud například provádíte porovnání čísel a v rozsahu nenajdete přesnou shodu, můžete použít další větší nebo menší číslo a pokračovat vpřed.
Můžeme použít kombinaci Countif, Large a Malý vzorec pro získání našeho výstupu.
Countif se používá k počítání počtu opakování konkrétních dat v rozsahu buněk.
Syntaxe = COUNTIF (rozsah, kritéria)
Velký: Vrátí k-tu největší hodnotu v sadě dat.
Syntaxe = LARGE (pole, k)
Malý: Vrátí k-tu nejmenší hodnotu v sadě dat.
Syntaxe = SMALL (pole, k)
Pojďme pochopit, co je nejbližší větší a menší číslo
Máme seznam s náhodnými čísly 1,2,3,5,8,10 a potřebujeme vzorec, který vyhodnotí následující:
- Pokud je k v seznamu, vraťte k
- Pokud k v seznamu není, vraťte další vyšší hodnotu. Například pokud k = 7, vraťte 8 (v případě nejbližšího většího čísla)
- Pokud k není v seznamu, vraťte další menší hodnotu. Pokud je například k = 7, vraťte 5
(v případě nejbližšího menšího počtu)
Vezměme si příklad, ve sloupci A máme nějaká náhodná čísla a potřebujeme najít hodnotu v tomto rozsahu, což je Nejbližší menší nebo Nejbližší větší do buňky B2, která obsahuje 46. Toto číslo není v našem sortimentu k dispozici. Viz níže uvedený snímek obrazovky
Podívejme se, jak vypočítáme Nejbližší větší číslo za prvé:
Další největší hodnotu můžeme ručně zkontrolovat na 46, což je 54.
Nyní se pokusíme získat stejný výsledek pomocí vzorce -
- Použijeme kombinaci VELKÉ & COUNTIF funkce pro generování nejbližšího většího čísla.
- Kritéria jsou větší než „>“ podepište vyhledávací hodnotou, tj. buňkou B2
- Znak větší než v kombinaci s hodnotou vyhledávání vrátí 5 hodnot, tj. 54, 57, 61, 74, 83, které jsou větší než 46.
- Když sloučíme funkce Velké a Počet, dostaneme 54. Viz vzorec v buňkách G2 a G3 na snímku níže.
PS: B2 v Countifformula je vyhledávací hodnota, tj. 46
- V buňce C2 vzorec použitý k nalezení Nejbližší větší číslo je = VELKÉ ($ A $ 2: $ A $ 8, COUNTIF ($ A $ 2: $ A $ 8, ">" & B2))
Nyní se podívejme, jak vypočítáme Nejbližší menší číslo -
Další menší hodnotu můžeme ručně zkontrolovat na 46, což je 28.
Umožňuje získat stejný výstup pomocí vzorce -
- Použijeme kombinaci MALÉ A COUNTIFfunkce pro generování nejbližšího menšího čísla.
- Kritéria jsou lessthan ‘<‘ s hodnotou vyhledávání, tj. buňkou B2
- Méně než znaménko v kombinaci s hodnotou vyhledávání vrátí 2 hodnoty, tj. 16 a 28, které jsou menší než 46.
- Když sloučíme Small & Countiffunctions, dostaneme 28. Viz vzorec v buňkách G5 a G6 na snímku níže.
Viz níže snímek obrazovky se vzorcem:
Poznámka: buňka B2 v CountifFormula je vyhledávací hodnota, tj. 46
- V buňce D2 vzorec použitý k nalezení Nejbližší menší číslo je = MALÉ ($ A $ 2: $ A $ 8, COUNTIF ($ A $ 2: $ A $ 8, "<" & B2))
To je způsob, jak můžeme vrátit nejbližší větší číslo a zavřít menší číslo.
