În Excel, dacă aveți o listă de valori și nu puteți găsi o potrivire exactă, atunci trebuie să găsiți fie Cel mai apropiat mai mare sau Cel mai aproape Mai mic valoare. De exemplu, dacă faceți o comparație a numerelor și nu găsiți o potrivire exactă în interval, puteți utiliza următorul număr mai mare sau mai mic și puteți continua.
Putem folosi o combinație a Contif, mare și Mic formula pentru a obține rezultatul nostru.
Countif este utilizat pentru a număra de câte ori datele specifice sunt repetate într-un interval de celule.
Sintaxă = COUNTIF (interval, criterii)
Mare: Returnează a k-a cea mai mare valoare dintr-un set de date.
Sintaxă = LARGE (matrice, k)
Mic: Returnează a k-a cea mai mică valoare dintr-un set de date.
Sintaxă = SMALL (matrice, k)
Să înțelegem ce este cel mai mare număr mare și cel mai mic
Avem o listă cu numere aleatoare 1,2,3,5,8,10 și avem nevoie de o formulă pentru a evalua următoarele:
- Dacă k este în listă, returnați k
- Dacă k nu este în listă, întoarceți următoarea valoare mai mare. De exemplu, dacă k = 7, returnează 8 (în cazul celui mai apropiat număr mai mare)
- Dacă k nu este în listă, întoarceți următoarea valoare mai mică. De exemplu, dacă k = 7, returnează 5
(în cazul celui mai apropiat număr mai mic)
Să luăm un exemplu, avem câteva numere aleatorii în coloana A și trebuie să găsim valoarea din acest interval, care este Cel mai mic Mai mic sau Cel mai apropiat mai mare la celula B2 care conține 46. Acest număr nu este disponibil în gama noastră. Vedeți captura de ecran de mai jos
Să vedem cum calculăm Cel mai apropiat număr mai mare primul:
Putem verifica manual următoarea cea mai mare valoare la 46, adică 54.
Acum, încercăm să obținem același rezultat folosind formula -
- Vom folosi o combinație a LARGE & COUNTIF funcții pentru a genera cel mai apropiat număr mai mare.
- Criteriile sunt mai mare decât ‘>’ semnează cu Value Lookup, adică celula B2
- Semnul mai mare decât combinat cu valoarea Căutare va returna 5 valori, adică 54, 57, 61, 74, 83 care sunt mai mari decât 46.
- Când îmbinăm funcțiile Large & Countiff, vom obține 54. Consultați formula din celulele G2 și G3 din instantaneul de mai jos.
PS: B2 în Countifformula este valoarea de căutare, adică 46
- În celula C2, formula utilizată pentru găsirea Cel mai aproape mai mare numărul este = LARGE ($ A $ 2: $ A $ 8, COUNTIF ($ A $ 2: $ A $ 8, ">" & B2))
Acum să vedem cum calculăm Cel mai mic număr mai mic -
Putem verifica manual următoarea valoare mai mică la 46, adică 28.
Să obținem aceeași ieșire folosind formula -
- Vom folosi o combinație a MICĂ ȘI COUNTIFfuncții pentru a genera cel mai apropiat număr mai mic.
- Criteriile sunt mai puțin decât „<” cu valoare de căutare, adică celula B2
- Semnul mai mic decât combinat cu valoarea Căutare va returna 2 valori, adică 16 și 28 care sunt mai mici decât 46.
- Când îmbinăm funcțiile mici și contrafuncționale, obținem 28. Consultați formula din celulele G5 și G6 din instantaneul de mai jos.
Vedeți mai jos captura de ecran cu formula:
Notă: celula B2 din CountifFormula este valoarea de căutare, adică 46
- În celula D2, Formula utilizată pentru găsire Cel mai aproape Mai mic numărul este = MIC ($ A $ 2: $ A $ 8, COUNTIF ($ A $ 2: $ A $ 8, "<" & B2))
Acesta este modul în care putem returna cel mai apropiat număr mai mare și dulapul mai mic.