Astuce: Les autres langues sont Google-Traduction. Vous pouvez visiter le English version de ce lien.
Se connecter
x
or
x
x
S'enregistrer
x

or

Comment trouver la valeur la plus proche ou la plus proche (nombre ou chaîne de texte) dans Excel?

Supposons que vous ayez une liste de nombres dans une colonne, et maintenant vous devez trouver la valeur la plus proche ou la plus proche d'une valeur donnée dans la liste des nombres. Comment gères-tu cela? En fait, vous pouvez trouver la valeur la plus proche ou la valeur la plus proche dans Excel avec les étapes suivantes.

Trouvez facilement toutes les chaînes de texte les plus proches avec un mot donné dans Excel

En général, vous pouvez appliquer une formule matricielle pour trouver les nombres les plus proches dans Excel. Mais qu'en est-il des chaînes de texte les plus proches? Ici, avec le Recherche floue caractéristique de Kutools for Excel, vous pouvez facilement trouver toutes les chaînes de texte les plus proches d’un travail donné dans Excel. Fonctionnalité complète Essai gratuit 30-day!

Kutools for Excel - Comprend plus que des outils pratiques 300 pour Excel. Fonctionnalité complète d'essai gratuit 30-day, aucune carte de crédit requise! Get It Now

Trouver le nombre le plus proche ou le plus proche avec une formule matricielle

Par exemple, vous avez une liste de nombres dans la colonne A, et maintenant vous trouverez la valeur la plus proche ou la valeur la plus proche de 18 de la colonne A. Vous pouvez le faire comme suit:

Sélectionnez une cellule vide, entrez la formule ci-dessous, puis appuyez sur la touche. Ctrl + Maj + Entrée clés ensemble.

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

Veuillez noter que : Dans cette formule de tableau de {=INDEX(B3:B22,MATCH(MIN(ABS(B3:B22-E2)),ABS(B3:B22-E2),0))},

  • B3: B22 est la plage dans laquelle vous voulez trouver la valeur spécifique
  • E2 est la valeur recherchée que vous voulez comparer.
note ruban La formule est trop compliquée à retenir? Enregistrez la formule en tant qu'entrée de texte automatique pour la réutilisation avec un seul clic à l'avenir!
Lire la suite ... Essai gratuit

Sélectionnez facilement tous les nombres les plus proches dans la plage de déviations d'une valeur donnée avec Kutools for Excel

Parfois, vous voudrez peut-être trouver et sélectionner toutes les valeurs placard à la valeur donnée dans une gamme. En fait, nous pouvons définir une valeur d'écart, puis appliquer Kutools pour Excel Sélectionnez des cellules spéciales utilitaire pour trouver et sélectionner toutes les valeurs les plus proches dans la gamme de divination de donner facilement la valeur.

Kutools for Excel- Comprend plus que des outils pratiques 300 pour Excel. Fonctionnalité complète d'essai gratuit 60-day, aucune carte de crédit requise! Get It Now

Par exemple, dans notre exemple, nous définissons la valeur de déviation comme 2 et la valeur donnée est 18. Par conséquent, nous devons trouver et sélectionner des valeurs entre 16 (= 18-2) et 20 (= 18 + 2). S'il vous plaît voir les étapes suivantes:

1. Sélectionnez la plage dans laquelle vous chercherez les valeurs les plus proches de la valeur donnée, puis cliquez sur Kutools > Sélectionner > Sélectionner des cellules spécifiques.

2. Dans la boîte de dialogue Ouvrir des cellules spécifiques,
(1) Vérifiez le Cellule option dans la Type de sélection section;
(2) Dans le Type spécifique section, cliquez sur la première liste déroulante et sélectionnez Plus grand ou égal à de cela et tapez 16 dans la case suivante, puis sélectionnez Inférieur ou égal à à partir de la deuxième liste déroulante et tapez 20 dans la case suivante. Voir la capture d'écran de gauche:

3. Clique le Ok bouton pour appliquer cet utilitaire. Ensuite, une boîte de dialogue apparaît et vous indique combien de cellules ont été sélectionnées. Et vous verrez que toutes les valeurs les plus proches comprises dans la plage de déviation de la valeur donnée sont sélectionnées comme suit:


Trouvez la chaîne de texte la plus proche ou la plus proche avec un outil pratique

Si Kutools for Excel est installé, vous pouvez appliquer sa fonctionnalité Recherche floue pour rechercher facilement dans Excel les chaînes de texte les plus proches d'une plage donnée. Veuillez faire comme suit:

Kutools for Excel- Comprend plus que des outils pratiques 300 pour Excel. Fonctionnalité complète d'essai gratuit 60-day, aucune carte de crédit requise! Get It Now

1. Cliquez Kutools > Trouvez > Recherche floue activer le volet de recherche floue dans votre classeur.

2. Dans le volet Fuzzy Lookup, configurez comme suit
(1) Vérifiez le Spécifié option, et sélectionnez la plage dans laquelle vous chercherez les chaînes de texte les plus proches;
(2) Vérifiez le Rechercher par texte spécifié l'option;
(3) Aller à la Texte et tapez le texte spécifié dont vous trouverez les chaînes de texte les plus proches;
(4) Dans le Nombre maximum de caractères différents case, veuillez taper un nombre. Dans mon cas, je tape 1;
(5) Dans La longueur de la chaîne de cellules est au moins case, veuillez taper un nombre. Dans mon cas, je tape 5;

3. Clique le Trouvez .

Développez maintenant les résultats de la recherche et vous verrez que toutes les chaînes de texte les plus proches sont listées en bloc. Voir la capture d'écran:


Démo: sélectionnez toutes les valeurs les plus proches dans la plage de déviation de la valeur donnée

Kutools for Excel comprend plus que des outils pratiques 300 pour Excel, gratuit à essayer sans limitation dans les jours 30. Téléchargez et testez maintenant!

Kutools for Excel résout la plupart de vos problèmes et augmente votre productivité de 80%

  • Réutilisation: Insérer rapidement formules complexes, graphiques et tout ce que vous avez utilisé auparavant; Crypter les cellules avec mot de passe Créer une liste de diffusion et envoyer des emails ...
  • Super Formula Bar (éditez facilement plusieurs lignes de texte et de formule); Disposition de lecture (facilement lire et éditer un grand nombre de cellules); Coller à la gamme filtrée...
  • Fusionner les cellules / rangées / colonnes sans perdre de données; Contenu des cellules divisées; Combiner les lignes / colonnes en double... Prévenir les cellules en double; Comparer les plages...
  • Sélectionnez Dupliquer ou Unique Des rangées; Sélectionnez les lignes vierges (toutes les cellules sont vides); Super Find et Fuzzy Find dans de nombreux cahiers d'exercices; Sélection aléatoire ...
  • Copie exacte Plusieurs cellules sans changer la référence de la formule; Créer automatiquement des références à plusieurs feuilles; Insérer des balles, Cases à cocher et plus ...
  • Extrait du texte, Ajouter du texte, Supprimer par position, Supprimer l'espace; Créer et imprimer des sous-totaux de pagination; Conversion entre contenu de cellules et commentaires...
  • Super filtre (enregistrer et appliquer des schémas de filtrage à d'autres feuilles); Tri avancé par mois / semaine / jour, fréquence et plus; Filtre spécial en gras, en italique ...
  • Combinaison de classeurs et de feuilles de calcul; Fusionner les tables en fonction des colonnes clés; Fractionner les données en plusieurs feuilles; Conversion par lots xls, xlsx et PDF...
  • Plus que de puissantes fonctionnalités 300. Prend en charge Office / Excel 2007-2019 et 365. Prend en charge toutes les langues. Déploiement facile dans votre entreprise ou organisation. Fonctionnalités complètes Essai gratuit du jour 30.
kte tab 201905

Office Tab apporte une interface à onglets à Office et simplifie grandement votre travail

  • Activer l'édition par onglets et la lecture dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
  • Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
  • Augmente votre productivité de 50% et réduit le nombre de clics de souris pour vous chaque jour!
fond officetab
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Avel · 1 months ago
    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
  • To post as a guest, your comment is unpublished.
    Daniel King · 1 months ago
    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.
  • To post as a guest, your comment is unpublished.
    DAKOT · 5 months ago
    ITS NOT WORKING its #N/A somehow
    • To post as a guest, your comment is unpublished.
      kellytte · 4 months ago
      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.
  • To post as a guest, your comment is unpublished.
    farolito · 1 years ago
    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
    • To post as a guest, your comment is unpublished.
      kellytte · 11 months ago
      Hi farolito,
      How about changing the value you will compare with to =TODAY() in Cell D1?
  • To post as a guest, your comment is unpublished.
    Liam Ward · 2 years ago
    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?
  • To post as a guest, your comment is unpublished.
    RaymB · 4 years ago
    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??
    • To post as a guest, your comment is unpublished.
      Phil1982 · 4 years ago
      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.
  • To post as a guest, your comment is unpublished.
    Katri · 4 years ago
    It does not work for me! Excel says that there is an error :(
  • To post as a guest, your comment is unpublished.
    Seymour · 4 years ago
    My bad
    This will look for all nearest date to today in column d
    =LARGE(D:D,COUNTIF(d:D,">="&TODAY()))
    WWW.excelireland.com
  • To post as a guest, your comment is unpublished.
    Pradnya · 4 years ago
    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.

    :(
  • To post as a guest, your comment is unpublished.
    Debra · 4 years ago
    Does anyone know why this won't work when I use the whole column for a range (D:D)
  • To post as a guest, your comment is unpublished.
    Seymour Platt · 4 years ago
    I went about this a little differently, Find how many numbers are bigger then the one you are looking for with CountIf()
    Then I used =Large this will find the nth number in a list we have the nth we are looking for in the countIF()

    =LARGE('Fixture and results'!A:A,COUNTIF('Fixture and results'!A:A,T">="&D1))

    It also works with dates - for example nearest date to today
    =LARGE('Fixture and results'!A:A,COUNTIF('Fixture and results'!A:A,">="&TODAY()))
    WWW.excelireland.com
  • To post as a guest, your comment is unpublished.
    Gursh · 4 years ago
    Great formula and advice
  • To post as a guest, your comment is unpublished.
    Roman · 4 years ago
    Hi,
    I think I have done everything correct but it wont give me the closest number it gives me one that is 2 away from it? can anyone help?
  • To post as a guest, your comment is unpublished.
    JN · 5 years ago
    Hi,

    Can someone please tell me how to modify this formula to choose the next higher number instead of the closest one?

    Your response will be sincerely appreaciated.

    Thanks,
  • To post as a guest, your comment is unpublished.
    JN · 5 years ago
    Awesome. You rock buddy. Thanks a million.
  • To post as a guest, your comment is unpublished.
    Srikanth · 5 years ago
    Hi,

    Hope there will be some help sometime, as I have not been able to figure out the solutions for the already posted two problems. Next new one is below.

    In ExcelDoc1.xls, I have 10 values (2,5,7,25,12,52,90,54,66,99) from A1 till J1 and I have got 10 rankings on these 10 values.

    I use the above rankings in another excel ExcelDoc2.xls for reference purposes only, but this excel also gets any other number that may be one of the above 10 values or outside this 10 values.

    Let us say I have a new value, how do I find its ranking dynamically in this ExcelDoc2 itself, removing the first value of 2 dynamically from the 10 values (inside ExcelDoc1.xls) ?

    Let us say I get an existing value, viz., 25, now how do I find out what ranking it equals from the already existing 10 ranks ?

    Thanks,
    S Srikanth
  • To post as a guest, your comment is unpublished.
    %seeker · 5 years ago
    Hello,

    how can I apply it with %. Like if I want to get the closet percentage value within a percentage set of data.
    WIth this formula I get an error all the time…


    Thanks
  • To post as a guest, your comment is unpublished.
    Srikanth · 5 years ago
    Hi,

    I have two distinct arrays. These two arrays are present across thousands of rows. Row#1 has these values 1,3,5,7,9 respectively, from A1 till E1, G1 has 11. Please note that there is no value present in F1. I would like to combine these inside excel LARGE and SMALL functions for the 1st array param.

    Note :
    1) Cell G1's value is a result or a computed value.
    2) I'm neither able to use NAMED RANGE feature of MS Excel nor in a position to remove Column F as a shortcut, due to thousands of rows.

    Thanks,
    S Srikanth
  • To post as a guest, your comment is unpublished.
    Trisch · 5 years ago
    Love this! Thanks so much for posting it :lol:
  • To post as a guest, your comment is unpublished.
    Srikanth · 5 years ago
    Hi,

    This does not seem to work if the array contains a mix of +ve and -ve numbers with decimal parts. For instance, how do I find the next lowest or highest number for -6392.52. You can copy paste the below 17 values in a row. Please help me know in case you find it out.

    5182.47 4432.65 5285.95 3259.14 1731.73 1011.25 66.45 -203.18 -926.70 -1857.41 -3488.99 -4006.90 -4804.79 -5339.44 -6046.62 -6414.55 -6392.52

    Thanks,
    S Srikanth
  • To post as a guest, your comment is unpublished.
    Ashley · 5 years ago
    Can you use this formula when you want to select the value from a data set on another sheet? I tried it, and it doesn't seem to work.
  • To post as a guest, your comment is unpublished.
    Clarification · 5 years ago
    For clarification: you need to be in the formula when you hit Ctrl+Shift+Enter. Simply selecting the cell and punching the buttons will NOT work. Hopefully this clears up any confusion
  • To post as a guest, your comment is unpublished.
    OriginalMAF · 5 years ago
    Good afternoon,
    How can I select the next larger number? Setting up a shipping calculation sheet and it does me no good to select the smaller (if not exact) number. My formula is based on Vt³(xyz) * Qnty of parts. My data set is a rather large selection of box dimensions.

    =VLOOKUP(AB9,Sheet6!A2:L87,1,TRUE)
    &
    =VLOOKUP(V13,BoxSize,2,-TRUE)
  • To post as a guest, your comment is unpublished.
    ast · 5 years ago
    Good one , and working
  • To post as a guest, your comment is unpublished.
    Jeff G · 5 years ago
    This formula works great, but one callout. Make sure you do not have any text in the search range. It will return an error. I had tried to search a list of zip codes, and the list included Canadian zips (Alphanumeric). It would not work until I removed them. Otherwise it works great! Thanks!
  • To post as a guest, your comment is unpublished.
    Austin J · 5 years ago
    Can i use a array with this formula?
  • To post as a guest, your comment is unpublished.
    Tom D · 5 years ago
    Tis is great but . . . How can I use this to return another value for example, I have two columns and I want to find the match in one column but return the vaule in the adjacent column.
  • To post as a guest, your comment is unpublished.
    Ali · 5 years ago
    This is an Array Formula, and to enter this formula you need to hit CTRL + SHIFT + ENTER (true for excel 2010)
  • To post as a guest, your comment is unpublished.
    Tudor · 5 years ago
    Thanks! Fabulous formula!
  • To post as a guest, your comment is unpublished.
    DavidK81 · 5 years ago
    Absolutely AWESOME!!
  • To post as a guest, your comment is unpublished.
    JARED · 5 years ago
    Actually, I meant thanks anonymous rockin' Rockstar... Marcus, you're wrong, it does work and I'm pretty sure you didn't hit ctrl + Alt + Enter
  • To post as a guest, your comment is unpublished.
    JARED · 5 years ago
    TOTALLY AWESOME!! Thanks Marcus! You ARE a rockin' rockstar
  • To post as a guest, your comment is unpublished.
    Not Marcus Kirby · 5 years ago
    Yes it does Marcus, You didn't do something right!!

    Probably didn't hit clt-Alt-enter
  • To post as a guest, your comment is unpublished.
    Marcus Kirby · 5 years ago
    Oh no it doesn't! I have used your precise example and it does not do what you say.
    • To post as a guest, your comment is unpublished.
      Michael hannifan · 4 years ago
      hey Marcus Kirby you might have the formula wright but your ref. to the cell range A1:A20 could have a formula in it. for instance if A2 has this formula =if(c15>10,100,50). so your answer will be either 50 or 100 BUT the numbers aren't numbers anymore excel looks at them as words and if you click on the cell A2 and press F9 you will get the answer "50". if that is so then you need to put the Absolute formula in your cell so it will be a number again. like this =abs(if(c15>10,100,50)) the answer now will be just 50 or just 100 and this will fix your error.