Ugrás a tartalomra

Hogyan lehet megtalálni a legközelebbi vagy legközelebbi értéket (számot vagy szöveges karakterláncot) az Excelben?

Tegyük fel, hogy egy oszlopban van számlistája, és most meg kell találnia az adott értékhez legközelebb eső vagy legközelebbi értéket a számok listájából. Hogyan kezeled? Valójában a legközelebbi vagy a legközelebbi értéket az Excelben a következő lépésekkel találhatja meg.

Találja meg a legközelebbi vagy a legközelebbi számot tömbképlettel

Például van egy számlistája az A oszlopban, és most megtalálja a legközelebbi vagy 18-as értéket az A oszlopból. Megteheti az alábbiak szerint:

Jelöljön ki egy üres cellát, írja be a képlet alá, majd nyomja meg a gombot Ctrl + Shift + Enter kulcsokat együtt.

=INDEX(B3:B22,MATCH(MIN(ABS(B3:B22-E2)),ABS(B3:B22-E2),0))

Jegyzet: Ebben a tömbképletben {=INDEX(B3:B22,MATCH(MIN(ABS(B3:B22-E2)),ABS(B3:B22-E2),0))},

  • B3: B22 az a tartomány, amelyen meg akarja találni az adott értéket
  • E2 az a keresési érték, amellyel összehasonlítani szeretne.
jegyzetszalag A képlet túl bonyolult ahhoz, hogy emlékezzen rá? Mentse el a képletet automatikus szöveges bejegyzésként, és a jövőben csak egy kattintással használhatja újra!
Olvass tovább…     Ingyenes próbaverzió

Könnyen kiválaszthatja az összes legközelebbi számot az adott érték eltérési tartományában a Kutools for Excel segítségével

Néha érdemes megtudni és kiválasztani az összes szekrényértéket az adott értékhez egy tartományban. Valójában megadhatunk egy eltérési értéket, majd alkalmazhatjuk a Kutools for Excel programokat Válassza a Speciális cellák lehetőséget segédprogram segítségével megtudhatja és kiválaszthatja az összes legközelebb eső értéket az osztási tartományon belül, az érték megadásához egyszerűen

Kutools az Excel számára- Több mint 300 praktikus eszközt tartalmaz az Excel számára. Teljes funkciós ingyenes 60 napos próbaidőszak, nincs szükség hitelkártyára! Szerezd meg most

Például példánkban az eltérési értéket 2-ként definiáljuk, a megadott érték pedig 18. Ezért meg kell találnunk és ki kell választanunk értékeket a 16 (= 18-2) És 20 (= 18 + 2). Kérjük, olvassa el a következő lépéseket:

1. Válassza ki azt a tartományt, ahol az adandó értékhez legközelebb eső értékeket keresi, majd kattintson a gombra Kutools > választ > Válassza a Specifikus cellák lehetőséget.

2. A Specifikus cellák kiválasztása párbeszédpanelen a
(1) Ellenőrizze a Sejt lehetőség a Kiválasztás típusa szakasz;
(2) A Konkrét típus szakaszban kattintson az első legördülő listára, és válassza a lehetőséget Nagyobb vagy egyenlő belőle és írja be 16 a következő mezőbe, majd válassza a lehetőséget Kevesebb vagy egyenlő a második legördülő listából és írja be 20 a következő mezőbe. Lásd a bal oldali képernyőképet:

3. Kattintson az Ok gombra a segédprogram alkalmazásához. Ezután megjelenik egy párbeszédpanel, amely megmutatja, hogy hány cellát választottak ki. És látni fogja, hogy az adott érték eltérési tartományán belül az összes legközelebbi érték kiválasztva van, ahogy az alábbi képernyőkép látható:


Egy praktikus eszköz segítségével keresse meg a legközelebbi vagy a legközelebbi szöveges karakterláncot

Ha telepítette a Kutools for Excel alkalmazást, használhatja a Fuzzy Find funkciót, hogy könnyen megtalálja az adott tartomány legközelebbi szöveges karakterláncait az Excelben. Kérjük, tegye a következőket:

Kutools az Excel számára- Több mint 300 praktikus eszközt tartalmaz az Excel számára. Teljes funkciós ingyenes 60 napos próbaidőszak, nincs szükség hitelkártyára! Szerezd meg most

1. Kattints Kutools > Találjon > Fuzzy Lookup a Fuzzy Lookup ablaktábla engedélyezéséhez a munkafüzetben.

2. A Fuzzy Lookup panelen konfigurálja az alábbiak szerint:
(1) Ellenőrizze a Meghatározott opciót, és válassza ki azt a tartományt, ahol a legközelebbi szöveges karakterláncokat keresi;
(2) Ellenőrizze a Keressen megadott szöveg szerint választási lehetőség;
(3) Menj a szöveg mezőbe, és írja be a megadott szöveget, amelynek legközelebbi szöveges karakterláncát találja;
(4) A Különböző karakterek maximális száma mezőbe, kérjük, írjon be egy számot. Az én esetemben 1-es gépet írok;
(5) In A cellahúrok hossza legalább mezőbe, kérjük, írjon be egy számot. Az én esetemben 5-es gépet írok;

3. Kattintson az Találjon gombot.

Most bontsa ki a keresési eredményeket, és láthatja, hogy az összes legközelebbi szöveges karakterlánc tömegesen szerepel. Lásd a képernyőképet:


Demo: válassza ki az összes legközelebbi értéket az adott érték eltérési tartományában

Kutools az Excel számára: Több mint 300 praktikus eszköz a keze ügyében! Kezdje el a 30 napos ingyenes próbaidőszakot funkciók korlátozása nélkül. Töltse le most!

A legjobb irodai hatékonyságnövelő eszközök

Népszerű szolgáltatások: Ismétlődések keresése, kiemelése vagy azonosítása   |  Üres sorok törlése   |  Oszlopok vagy cellák kombinálása adatvesztés nélkül   |   Kerek Formula nélkül ...
Szuper keresés: Több kritérium VLookup    Többértékű VLookup  |   VLookup több munkalapon   |   Fuzzy Lookup ....
Speciális legördülő lista: Gyors legördülő lista létrehozása   |  Függő legördülő lista   |  Többszörösen válassza ki a legördülő listát ....
Oszlopkezelő: Adjon meg egy adott számú oszlopot  |  Oszlopok mozgatása  |  Kapcsolja be a Rejtett oszlopok láthatósági állapotát  |  Tartományok és oszlopok összehasonlítása ...
Kiemelt funkciók: Rács fókusz   |  Design nézet   |   Nagy Formula bár    Munkafüzet és lapkezelő   |  Erőforrás-könyvtár (Auto szöveg)   |  Dátumválasztó   |  Kombinálja a munkalapokat   |  Cellák titkosítása/dekódolása    E-mailek küldése listánként   |  Szuper szűrő   |   Speciális szűrő (félkövér/dőlt/áthúzott szűrés...) ...
A 15 legjobb eszközkészlet12 szöveg Eszközök (Szöveg hozzáadása, Karakterek eltávolítása,...)   |   50 + Táblázatos Típusai (Gantt-diagram,...)   |   40+ Praktikus képletek (Számolja ki az életkort a születésnap alapján,...)   |   19 beszúrás Eszközök (Helyezze be a QR-kódot, Kép beszúrása az útvonalból,...)   |   12 Átalakítás Eszközök (Számok szavakig, Valuta átváltás,...)   |   7 Egyesítés és felosztás Eszközök (Haladó kombinált sorok, Hasított sejtek,...)   |   ... és több

Töltsd fel Excel-készségeidet a Kutools for Excel segítségével, és tapasztald meg a még soha nem látott hatékonyságot. A Kutools for Excel több mint 300 speciális funkciót kínál a termelékenység fokozásához és az időmegtakarításhoz.  Kattintson ide, hogy megszerezze a leginkább szükséges funkciót...

kte lap 201905


Az Office lap füles felületet hoz az Office-ba, és sokkal könnyebbé teszi a munkáját

  • Füles szerkesztés és olvasás engedélyezése Wordben, Excelben és PowerPointban, Publisher, Access, Visio és Project.
  • Több dokumentum megnyitása és létrehozása ugyanazon ablak új lapjain, mint új ablakokban.
  • 50% -kal növeli a termelékenységet, és naponta több száz kattintással csökkenti az egér kattintását!
Comments (42)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
How do we do this if our data is filtered?
This comment was minimized by the moderator on the site
copy the filtered data to a new sheet
This comment was minimized by the moderator on the site
Using the formula how would you return the value next to 17 if there was another column next to number like names. So if 17 is the closest in rang the name next to 17 (John) would be returned?


Example: 18 is nearest to 17 so the return value would be John


Numbers Names
38 Tammy
17 John
20 Amy
This comment was minimized by the moderator on the site
You can use the Approximate match of VLOOKUP function to solve this problem.
=VLOOKUP(E2,A1:B15,2,TRUE)
This comment was minimized by the moderator on the site
I used this formula =INDEX(B3:B22,MATCH(MIN(ABS(B3:B22-E2)),ABS(B3:B22-E2),0)) and it works great. However i have found that where the source number is exactly between two numbers in the range, the lower range number is selected to be the closest.

eg: Searching for the closest number to 9 in the range: 6, 8, 10, 12. It will chose 8 instead of 10. Rounding convention is to round up if exactly half way between. Is there a workaround? Thanks.
This comment was minimized by the moderator on the site
ITS NOT WORKING its #N/A somehow
This comment was minimized by the moderator on the site
Hi DAKOT,
=INDEX(A1:A20,MATCH(MIN(ABS(A1:A20-D1)),ABS(A1:A20-D1),0)) is an array formula, after entering it, please remember to press the Ctrl + Shift + Enter keys together.
This comment was minimized by the moderator on the site
Hi,
im using that formula in finding closest date, it is working.but i want to add condition: closest date that is less than 30 days of the current date (today).it is possible?
Anyone can help please?thank you
This comment was minimized by the moderator on the site
Hi farolito,
How about changing the value you will compare with to =TODAY() in Cell D1?
This comment was minimized by the moderator on the site
Hi, I can get it to work, amazing, BUT not when I input '1' as my 'match_type', instead of the '0' that you used. I want to return values less than or equal to, not just closest to +/-. If I enter 1 instead of 0, it doesn't work. Thoughts on why this might be?
This comment was minimized by the moderator on the site
Great formula -thank you-just a quick question. Anyone know how to highlight the cell that is closest in the match so in long lists it is easy to find??
This comment was minimized by the moderator on the site
If you have the row number from the formula above then you could set a conditional formatting rule on the search array to highlight a cell if it lies on that row.
This comment was minimized by the moderator on the site
It does not work for me! Excel says that there is an error :(
This comment was minimized by the moderator on the site
My bad This will look for all nearest date to today in column d =LARGE(D:D,COUNTIF(d:D,">="&TODAY())) http://WWW.excelireland.com
This comment was minimized by the moderator on the site
Hi, I tried the above formula. however, it is giving me a #N/A. 1. Copy pasted the range from A2 to A43. 2. Formula given was : =INDEX(A2:A43,MATCH(MIN(ABS(A2:A43-H1)),ABS(A2:A43-H1),0)) 3. Press control +Shift +enter Can anybody help me as to what wrong I am doing. :(
This comment was minimized by the moderator on the site
Replace the " , " separator for " ; "
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations