列表条件中的最接近值 [英] FInd nearest value within list condition

问题描述

我想找到一个单元格的最接近的值，但是不知道怎么做.

I want to find nearest value of a cell but don't know how.

在我的excel表格中，单元格B1具有一个值(列表名称)，单元格B2具有其他要通过条件搜索的值.如果单元格B1具有值GP_42(列表名称)，则在列表GP_42中搜索单元格B2的值(D4:D13)如果单元格B1具有值GP_42(列表名称)，则se [！[在此处输入图像描述] [2]] [ 2]使用列表GP_42(E4:E13)来存储单元格B2的值.如果值不匹配，则结果应为最接近的匹配值.结果应显示在单元格B3中.

In my excel sheet cell B1 has a value(LIST NAME) and cell B2 has other value which is to be searched with condition. If cell B1 has value GP_42(list name) then search the value of cell B2 withing list GP_42 (D4:D13) If cell B1 has value GP_42(list name) then se[![enter image description here][2]][2]arch the value of cell B2 withing list GP_42 (E4:E13). If value doesn't match then result should be the nearest matched value. Result should be display in the cell B3.

推荐答案

我假设您想要最接近的值.这意味着您需要绝对( ABS函数)，将GP_42中的值与您的搜索值之间的差额计算出来.

I'll assume you want the closest value. This means that you need the absolute (ABS function) of the difference between the value in GP_42 and your search value.

在B3中作为数组公式¹

=INDEX(GP_42, AGGREGATE(15, 6, ROW(GP_42)/(ABS(GP_42-B$2)=MIN(ABS(GP_42-B$2))), ROW(1:1))-ROW(GP_42)+1)

我已经使用ROW(1:1)表示数字1.这是您遇到的第一个匹配项.在我的扩展示例中，第三个具有两个符合B9和B10中最小差异"的匹配项. B10是通过填充来实现的.这样会将ROW(1:1)前进到ROW(2:2)，它表示2，并为您提供第二个可用的匹配项.

I have used ROW(1:1) to represent the number 1. This gives you the first encountered match. In my expanded examples, the third has two matches that meet the 'minimum difference' in B9 and B10. B10 is achieved by filling down. This advances ROW(1:1) to ROW(2:2) which represents 2 and gives you the second available match.

¹_{数组公式需要使用 Ctrl + Shift + Enter↵完成.如果输入正确，Excel会将公式用大括号括起来(例如 {和} ).您不要在自己中键入大括号.一旦正确输入第一个单元格，就可以像填充其他任何公式一样向下或向右填充或复制它们.尝试将全列引用减少到更接近代表实际数据范围的范围.数组公式对数地缩短了计算周期，因此，最好将参考范围缩小到最小.请参阅有关数组公式的准则和示例以获取更多信息.}

¹ _{Array formulas need to be finalized with Ctrl+Shift+Enter↵. If entered correctly, Excel with wrap the formula in braces (e.g. { and }). You do not type the braces in yourself. Once entered into the first cell correctly, they can be filled or copied down or right just like any other formula. Try and reduce your full-column references to ranges more closely representing the extents of your actual data. Array formulas chew up calculation cycles logarithmically so it is good practise to narrow the referenced ranges to a minimum. See Guidelines and examples of array formulas for more information.}

这篇关于列表条件中的最接近值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！