根据键的最近数字匹配显示行值 [英] Display Row Values based on Nearest Numeric Match of Key

问题描述

说我在A列中有一个排序的值列表.它们充当每个数字行的键".像这样:

Say I have a sorted list of values in column A. They serve as a 'key' to each numerical row. Something like this:

   ___A_(key)_______B______         ______G_____
1 |____2.58___|____________|   ... |____________|   ... 
2 |____2.69___|____________|   ... |____________|   ...
        ...         ...                  ...
   ________________________         ____________
x |____5.69___|____________|   ... |____________|   ...

B到G列的每个键都有一些值，因此它基本上是一个2D数字数据电子表格.

Columns B through G have some values for each of the keys, so it's basically a 2D spreadsheet of numerical data.

我需要能够通过 approximate 值快速访问特定行.

I need to be able to quickly access a certain row by the approximate value.

现在，我在列上设置了一个过滤器，可以搜索行，但是为此，我需要准确输入键值，即 2.58 或 2.69 ，但不是 2.64 之类的东西.

Right now I have set a Filter on the column, and can search for the rows, but for that I need to enter the key value exactly, i.e. 2.58, or 2.69, but not something like 2.64.

我希望能够输入 2.64 之类的东西，并让键值最接近的行显示给我，最好显示在单独的位置，例如在桌子的侧面，例如从第I列开始，在那里可以精确定义，但基本上任何方法都可以输入值并方便地显示行值.我该怎么办?

I want to be able to enter something like 2.64, and have the row with nearest key value be shown to me, preferably in a separate location, such as on the side of the table, like starting in column I where exactly can be defined, but basically any method where I type in a value and get conveniently shown the row values will do. How can I do this?

我的经验水平是:大约10年前，我在VBA/excel中做过一些简单的事情……基本上是在寻找一个好的入门方向和方法.

My experience level is: I did some simple stuff in VBA/excel ~10 years ago... and basically looking for a good starting direction and approach.

推荐答案

假设您的数据位于 A1:A20 中，而您的查找值位于 B1 中，则此公式将返回最接近的:

Assuming your Data is in A1:A20 and your look up value is in B1 this formula will return the closest:

=INDEX(A1:A20,MATCH(MIN(ABS(A1:A20-B1)),ABS(A1:A20-B1),0))

输入时，请确保按 Ctrl + Shift + Enter 和否，只需输入.

When entering make sure to press Ctrl + Shift + Enter and NOT just Enter.

得到的差异最小，然后将其行返回索引，然后索引返回值.

It is getting the smallest difference then returning its row into the index, index then returns the value.

这篇关于根据键的最近数字匹配显示行值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！