Če imate v Excelu seznam vrednosti in ne najdete natančnega ujemanja, morate najti bodisi Najbližje Večje ali Najbližje Manjše vrednost. Če na primer primerjate številke in v obsegu ne najdete natančnega ujemanja, lahko uporabite naslednje večje ali manjše število in nadaljujete.
Lahko uporabimo kombinacijo Številka, velika in Majhna formulo, da dobimo naš rezultat.
Countif se uporablja za štetje, kolikokrat se določeni podatki ponovijo v nizu celic.
Sintaksa = COUNTIF (obseg, merila)
Velik: Vrne k-to največjo vrednost v nizu podatkov.
Sintaksa = LARGE (matrika, k)
Majhna: Vrne k-to najmanjšo vrednost v nizu podatkov.
Sintaksa = MALO (matrika, k)
Razumejmo, kaj je najbližje večje in manjše število
Imamo seznam z naključnimi številkami 1,2,3,5,8,10 in potrebujemo formulo za oceno naslednjega:
- Če je k na seznamu, vrnite k
- Če k ni na seznamu, vrnite naslednjo višjo vrednost. Na primer, če je k = 7, vrnite 8 (v primeru najbližjega večjega števila)
- Če k ni na seznamu, vrnite naslednjo manjšo vrednost. Na primer, če je k = 7, vrnite 5
(v primeru najbližjega manjšega števila)
Vzemimo primer, v stolpcu A imamo nekaj naključnih števil in moramo poiskati vrednost v tem razponu, ki je Najbližje Manjše ali Najbližje Večje v celico B2, ki vsebuje 46. Ta številka ni na voljo v našem asortimanu. Oglejte si spodnji posnetek zaslona
Poglejmo, kako izračunamo Najbližje večje število najprej:
Naslednjo največjo vrednost lahko ročno preverimo na 46, to je 54.
Poskusimo dobiti enak rezultat s formulo -
- Uporabili bomo kombinacijo VELIKO & COUNTIF funkcije za ustvarjanje najbližjega večjega števila.
- Kriteriji so večji od '>' podpišite z Lookup Value, to je celica B2
- Znak večji od v kombinaciji z vrednostjo iskanja vrne 5 vrednosti, to je 54, 57, 61, 74, 83, ki so večje od 46.
- Ko združimo velike in nasprotne funkcije, dobimo 54. Glejte formulo v celicah G2 in G3 na spodnjem posnetku.
PS: B2 v Countifformuli je iskalna vrednost, tj. 46
- V celici C2 se uporablja formula za iskanje Najbližje Večje številka je = VELIKO ($ A $ 2: $ A $ 8, COUNTIF ($ A $ 2: $ A $ 8, ">" & B2))
Zdaj pa poglejmo, kako izračunamo Najbližje manjše število -
Naslednjo manjšo vrednost lahko ročno preverimo na 46, kar je 28.
Dobimo enak rezultat po formuli -
- Uporabili bomo kombinacijo MALO & COUNTIFfunkcije za ustvarjanje najbližjega manjšega števila.
- Kriteriji so manj kot "<" z iskalno vrednostjo, to je celica B2
- Manj kot znak v kombinaciji z vrednostjo iskanja vrne 2 vrednosti, tj. 16 in 28, ki sta manjši od 46.
- Ko združimo Small & Countiffunctions, dobimo 28. Glej formulo v celicah G5 in G6 na spodnjem posnetku.
Oglejte si spodnji posnetek zaslona s formulo:
Opomba: celica B2 v CountifFormuli je iskalna vrednost, tj. 46
- V celici D2 se uporablja formula za iskanje Najbližje Manjše številka je = MALO ($ A $ 2: $ A $ 8, COUNTIF ($ A $ 2: $ A $ 8, "<" & B2))
Tako lahko vrnemo najbližje večje število in omaro manjšega števila.