W programie Excel, jeśli masz listę wartości i nie możesz znaleźć dokładnego dopasowania, musisz znaleźć albo Najbliższy Większy lub Najbliższy mniejszy wartość. Na przykład, jeśli porównujesz liczby i nie znajdziesz dokładnego dopasowania w zakresie, możesz użyć następnej większej lub mniejszej liczby i przejść dalej.
Możemy użyć kombinacji Licznik, duży oraz Mały formuła, aby uzyskać nasze wyjście.
Countif służy do zliczania, ile razy określone dane są powtarzane w zakresie komórek.
Składnia =LICZ.JEŻELI(zakres;kryteria)
Duży: Zwraca k-tą największą wartość w zestawie danych.
Składnia =LARGE(tablica;k)
Mały: Zwraca k-tą najmniejszą wartość w zestawie danych.
Składnia =MAŁA(tablica;k)
Pozwól nam zrozumieć, co to jest Najbliższa Większa i Mniejsza Liczba
Mamy listę z losowymi liczbami 1,2,3,5,8,10 i potrzebujemy formuły do oceny:
- Jeśli k jest na liście, zwróć k
- Jeśli k nie znajduje się na liście, zwróć następną wyższą wartość. Na przykład, jeśli k =7, zwróć 8 (w przypadku najbliższej większej liczby)
- Jeśli k nie znajduje się na liście, zwróć następną mniejszą wartość. Na przykład, jeśli k =7, zwróć 5
(w przypadku najbliższej mniejszej liczby)
Weźmy przykład, mamy kilka liczb losowych w kolumnie A i musimy znaleźć wartość z tego zakresu, czyli Najbliższy mniejszy lub Najbliższy Większy do komórki B2, która zawiera 46. Ten numer nie jest dostępny w naszym asortymencie. Zobacz poniższy zrzut ekranu
Zobaczmy, jak obliczamy Najbliższa większa liczba pierwszy:
Możemy ręcznie sprawdzić kolejną największą wartość do 46, czyli 54.
Teraz spróbujmy uzyskać ten sam wynik za pomocą wzoru -
- Użyjemy kombinacji DUŻY I LICZONY funkcje do wygenerowania najbliższej większej liczby.
- Kryteria to większe niż „>” znak z wartością wyszukiwania, tj. komórką B2
- Znak większa niż w połączeniu z wartością Lookup zwróci 5 wartości, tj. 54, 57, 61, 74, 83, które są większe niż 46.
- Kiedy połączymy funkcje Large &Countif, otrzymamy 54. Odwołaj się do formuły w komórkach G2 i G3 na poniższym zrzucie ekranu.
PS: B2 w Countifformula to wartość wyszukiwania, tj. 46
- W komórce C2 formuła użyta do znalezienia Najbliższy Większy numer to =DUŻA(2 $ A $: 8 $ A $;LICZ.JEŻELI (2 $ A $: $ A $ 8;">"&B2))
Zobaczmy teraz, jak obliczamy Najbliższa mniejsza liczba -
Możemy ręcznie sprawdzić kolejną mniejszą wartość do 46, czyli 28.
Uzyskajmy to samo wyjście za pomocą wzoru -
- Użyjemy kombinacji MAŁE I LICZ.funkcje do wygenerowania najbliższej mniejszej liczby.
- Kryteria to mniej niż „<” z wartością wyszukiwania, tj. komórką B2
- Znak mniej niż połączony z wartością Lookup zwróci 2 wartości, tj. 16 i 28, które są mniejsze niż 46.
- Kiedy połączymy funkcje Small &Countif, otrzymamy 28. Odwołaj się do formuły w komórkach G5 i G6 na poniższym zrzucie ekranu.
Zobacz poniższy zrzut ekranu ze wzorem:
Uwaga: komórka B2 w CountifFormula to wartość wyszukiwania, tj. 46
- W komórce D2 formuła użyta do znalezienia Najbliższy mniejszy numer to =MAŁY($A$2:$A$8;LICZ.JEŻELI($A$2:$A$8;"<"&B2))
W ten sposób możemy zwrócić najbliższą większą liczbę i zamknąć mniejszą liczbę.
