使用vlookup对范围内的数字进行积分 [英] Integrating number in between range with a vlookup

问题描述

我正在使用一个包含两列的数据表(最大房间高度"，灯泡数").目的是创建一个公式，在用户输入房间高度后，提供灯泡数量的输出以供使用.这是用户输入的提示，房间高度可以是一个随机数，可以介于两个最大房间高度之间.例如，房间高度数据为10英尺，12英尺，14英尺，16英尺，并且用户输入房间高度为15英尺，公式应能够提取与16英尺高度相对应的灯泡数量.

I am working with a data table that contains two columns ( Max Room Height, # of bulbs). Intent is to create a formula which provides the output on # of bulbs to use after the user inputs room height. Here’s the trick, room height number that user inputs can be a random number, and could lie between two max room heights. For example , room height data is available as 10 feet, 12 feet, 14 feet, 16 feet and the and user inputs room height as 15 feet, formula should be able to pick up the # of bulbs corresponding to 16 feet height.

推荐答案

您可以尝试使用一些内置的Excel函数根据房间高度确定要使用的灯泡数量.这是使用INDEX(...)，MATCH(...)和MIN(...)的示例:

You can try using some of the built-in Excel functions to determine the number of bulbs to use based on the room height. Here's an example using INDEX(...), MATCH(...), and MIN(...):

目前我无法访问Excel，但这在LibreOffice Calc v5.1.6.2中有效. Excel似乎具有可比的功能.

I do not have access to Excel at the moment, but this worked in LibreOffice Calc v5.1.6.2. Excel seems to have comparable functions.

请继续阅读，以防图片不值一千字！

Read on, in case a picture is not worth a thousand words!

1. 创建一个包含两列的表(示例图像中的E和F列).
   • 第一列(列E)以降序表示高度上限.请注意上限10000.
   • 第二列(F列)表示在给定的房间高度落在高度范围内时要使用的灯泡数量.
   • 因此，在此示例中，介于10000(含)和16(不含)之间的值应声明为忘记灯泡而只使用太阳.介于16(含)和14(不含)之间的值应声明使用4个灯泡.介于14(含)和12(不含)之间的值应声明使用3个灯泡...等等.

1. Create a table with two columns (Columns E and F in the example image).
   • The first column (Column E) represents the upper height boundaries in descending order. Note the upper bound of 10000.
   • The second column (Column F) represents the number of bulbs to use if a given room height falls within the height boundary.
   • So for this example, values between 10000 (inclusive) and 16 (exclusive) should state to forget light bulbs and just use the sun. Values between 16 (inclusive) and 14 (exclusive) should state to use 4 bulbs. Values between 14 (inclusive) and 12 (exclusive) should state to use 3 bulbs... etc.

• 第一列(A列)将包含用户输入"(即变量房间高度"，应查询灯泡的数量).
• 第二列(B列)将包含用于根据用户输入"和在我们创建的第一张表中定义的高度边界来计算要使用的灯泡数量的公式.

公式/计算

让我们分解将进入B列单元格的公式.我在C列中保留了该公式的文本.您会注意到，对于每行，仅MIN(...)函数的第一个参数会更改.每行公式的其余部分都相同.

Formula/Calculation

Let's break down the formula that will go into the Column B cells. I left the text for the formula in Column C. You'll notice that only the first parameter of the MIN(...) function changes for each row. The rest of the formula is the same for each row.

以第2行为例，我们利用嵌套在一起的3个函数:

Using row 2 as an example, we make use of 3 functions, nested together:

• MIN(A2,E2)-我们要确保房间高度在我们处理的范围内.这与添加到列E的任意上限10000结合使用.如果我们不强制数据适合上限，则如果用户超过最大值，我们可能会看到某种错误在E列中指定.
• MATCH(MIN(A2,E2),E2:E6,-1)-本质上，此函数查找用户输入的数据所处的高度范围边界.该功能具有三个参数.第一个是用户为此行输入的数据(或任意上限)... MIN(A2,E2).第二个是高度范围边界(按降序排列)... E2:E6.第三个是匹配类型... -1.匹配类型-1表示搜索值的降序列表，并在给定值(即第一个参数)等于或小于降序列表中的值时停止搜索".如果降序列表中的第一项符合条件，则MATCH(...)函数将返回索引1.如果降序列表中的第二项符合条件，则函数将返回索引2 ...等等.
• INDEX(F2:F6,MATCH(MIN(A2,E2),E2:E6,-1))-此功能实质上是为用户输入查找我们的答案".我们使用MATCH(...)函数找到了用户输入的高度范围的索引"或列表位置"，并创建了表格，以使每个高度范围的灯泡计数都在同一行中(即它具有相同的索引"或列表位置"). INDEX(...)函数接受两个参数.第一个是包含答案" ... F2:F6的单元格范围.第二个参数是我们要返回的答案单元格范围中的索引或列表位置(即MATCH(...)函数的结果).因此，如果我们的MATCH(...)函数调用返回"1"，则将返回F2:F6范围内的第一个单元格(即F2-使用太阳！).如果我们的MATCH(...)函数调用返回"2"，则将返回F2:F6范围内的第二个单元格(即F3-4个灯泡)...等等.

• MIN(A2,E2) - We want to make sure the room height falls within our handled range. This works in conjunction with the the arbitrary upper bound of 10000 that was added to Column E. If we didn't force the data to fit within the upper boundary, we'd probably see some kind of error if the user exceeded the largest value specified in Column E.
• MATCH(MIN(A2,E2),E2:E6,-1) - Essentially, this function finds the height range boundary under which the user entered data falls. This function has three parameters. The first is the user entered data (or the arbitrary upper bound)... MIN(A2,E2) for this row. The second is the height range boundaries (in descending order)... E2:E6. The third is the matching type... -1. The matching type of -1 means "search a descending list of values and stop when your given value (i.e. the first parameter) is equal to or less than a value in the descending list". If the first item in the descending list meets the criteria, the MATCH(...) function returns an index of 1. If the second item in the descending list meets the criteria, the function returns an index of 2... etc.
• INDEX(F2:F6,MATCH(MIN(A2,E2),E2:E6,-1)) - This function essentially looks up our "answer" for the user's input. We found the "index" or "list position" of the height range for the user's input using the MATCH(...) function and we created our table such that the bulb-count for each height range is in the same row (i.e. it has the same "index" or "list position"). The INDEX(...) function accepts two parameters. The first is the range of cells containing the "answers"... F2:F6. The second parameter is the index or list position from the answer cell range that we want to return (i.e. the results of our MATCH(...) function). So if our MATCH(...) function call returns "1", the first cell from the F2:F6 range will be returned (i.e. F2 - Use the Sun!). If our MATCH(...) function call returns "2", the second cell from the F2:F6 range will be returned (i.e. F3 - 4 bulbs)... etc.

根据您使用的Excel版本，可能会有更好的解决方案.根据撰写本文时的Office文档，此处使用的功能应该对Excel 2007到2016年有效.

There might be better solutions out there depending on the version of Excel you are using. According to the Office documentation as of this writing, the functions used here should be valid for Excel 2007 through 2016.

这篇关于使用vlookup对范围内的数字进行积分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！