提示:其他語言是Google翻譯的。 你可以訪問 English 版本。
登入
x
or
x
x
註冊
x

or

如何在Excel中找到最接近或最近的值(數字或文本字符串)?

假設列中有數字列表,現在您需要找出數字列表中給定值的最接近或最接近的值。 你如何解決? 實際上,您可以通過以下步驟在Excel中找到最接近的值或最接近的值。

通過Excel中的給定單詞輕鬆查找所有最接近的文本字符串

通常,您可以應用數組公式以在Excel中查找最接近的數字。 但最近的文本字符串怎麼樣? 在這裡,隨著 模糊查找 的特點 Kutools for Excel,您可以通過Excel中的給定工作輕鬆找到所有最接近的文本字符串。 全功能免費試用30天!

Kutools for Excel -包括多個用於Excel的300便捷工具。 全功能免費試用30天,無需信用卡! 立即行動吧

使用數組公式查找最接近或最接近的數字

例如,您在列A中有一個數字列表,現在您可以從列A中找到最接近的值或18的最接近的值。您可以按如下所示進行操作:

選擇一個空白單元格,然後輸入以下公式,然後按 Ctrl + Shift + Enter 鑰匙在一起。

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

請注意: 在這個數組公式中 {=INDEX(B3:B22,MATCH(MIN(ABS(B3:B22-E2)),ABS(B3:B22-E2),0))},

  • B3:B22 是您要查找特定值的範圍
  • E2 是你想要比較的搜索值。
注意絲帶 公式太難記了? 將公式保存為自動文本條目,以便將來只需點擊一下即可重複使用!
閱讀全文... 免費試用

使用Kutools for Excel輕鬆選擇給定值偏差範圍內的所有最接近數字

有時,您可能想要查找並選擇某個範圍內給定值的所有壁櫥值。 實際上,我們可以定義偏差值,然後將Excel應用於Kutools 選擇特殊單元格 實用程序可以輕鬆找出並選擇在給定值分配範圍內的所有最接近的值。

Kutools for Excel - 包括多個用於Excel的300便捷工具。 全功能免費試用60天,無需信用卡! 立即行動吧

例如,在我們的示例中,我們將偏差值定義為2,給定值為18。 因此,我們需要找出並選擇兩者之間的值 16 (= 18-2)和 20 (= 18 + 2)。 請查看以下步驟:

1。 選擇要在給定值中搜索最接近值的範圍,然後單擊 Kutools > 選擇 > 選擇特定單元格.

2。 在“打開選擇特定單元格”對話框中,
(1)檢查 細胞 在選項 選擇類型 部分;
(2)在 具體類型 部分,點擊第一個下拉列表並選擇 大於或等於 從它和類型 16 進入下面的框,然後選擇 小於或等於 從第二個下拉列表中輸入 20 進入下面的框。 請參閱左側截圖:

3。 點擊 Ok 按鈕以應用此實用程序。 然後會出現一個對話框,顯示已選擇了多少個單元格。 您將看到在給定值的偏差範圍內的所有最接近的值都被選中,如下面的屏幕截圖所示:


使用方便的工具查找最近或最近的文本字符串

如果安裝了Kutools for Excel,則可以應用其模糊查找功能,以便在Excel中輕鬆找到給定範圍內最接近的文本字符串。 請做如下:

Kutools for Excel - 包括多個用於Excel的300便捷工具。 全功能免費試用60天,無需信用卡! 立即行動吧

1。 點擊 Kutools > 發現 > 模糊查找 在工作簿中啟用“模糊查找”窗格。

2。 在模糊查找窗格中,請配置如下;
(1)檢查 規定 選項,並選擇您要查找最接近的文本字符串的範圍;
(2)檢查 按指定文本查找 選項;
(3)轉到 文本 框,並鍵入您將找到其最接近的文本字符串的指定文本;
(4)在 最大不同字符數 框,請輸入一個數字。 就我而言,我鍵入1;
(5)中 細胞串長度至少是 框,請輸入一個數字。 就我而言,我鍵入5;

3。 點擊 發現 按鈕。

現在展開搜索結果,您將看到所有最接近的文本字符串都是批量列出的。 看截圖:


演示:選擇給定值偏差範圍內的所有最接近的值

Kutools for Excel 包含多個用於Excel的300便捷工具,可以在30天內不受限制地免費試用。 立即下載並免費試用!

Kutools for Excel解決了您的大多數問題,並使您的生產率提高了80%

  • 重用: 快速插入 複雜的公式,圖表 以及你以前用過的任何東西; 加密單元格 密碼; 創建郵件列表 並發送電子郵件...
  • 超級方程式酒吧 (輕鬆編輯多行文字和公式); 閱讀佈局 (輕鬆讀取和編輯大量單元格); 粘貼到過濾範圍...
  • 合併單元格/行/列 不丟失數據; 分裂細胞含量; 組合重複的行/列...防止重複的細胞; 比較範圍...
  • 選擇複製或唯一 行; 選擇空行 (所有細胞都是空的); 超級查找和模糊查找 在許多工作簿中; 隨機選擇......
  • 精確複製 多個單元格而不更改公式參考; 自動創建參考 多張表; 插入項目符號,複選框等等......
  • 提取文本,添加文本,按位置刪除, 刪除空間; 創建和打印分頁小計; 在單元格內容和註釋之間轉換...
  • 超級過濾器 (將過濾方案保存並應用到其他工作表); 高級排序 按月/週/日,頻率等; 特殊過濾器 用粗體,斜體......
  • 結合工作簿和工作表; 根據鍵列合併表; 將數據拆分為多個表格; 批量轉換xls,xlsx和PDF...
  • 超過300強大的功能。 支持Office / Excel 2007-2019和365。 支持所有語言。 在您的企業或組織中輕鬆部署。 全功能30天免費試用。
kte tab 201905

Office選項卡為Office提供選項卡式界面,使您的工作更輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,Publisher,Access,Visio和Project。
  • 在同一窗口的新選項卡中打開並創建多個文檔,而不是在新窗口中。
  • 通過50%提高您的工作效率,每天為您減少數百次鼠標點擊!
官方底部
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 · 29 days 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.