Excel：在一个单元格中插入一系列数字

I'm creating an Excel template for one client, and there's one thing I can't figure out.

我正在为一个客户创建一个Excel模板,有一点我无法弄清楚。

I would like to create a table in Excel which looks like this:

我想在Excel中创建一个如下所示的表:

             A             B           C
Row 1:    2100-2200    2200-2300    2300-2400

I'm using a VLOOKUP formula, and I want this formula to find number e.g. 2152 in the example table above. But Excel "can't see" that in my table the number 2152 is actually in cell A1 (because cell A1 is supposed to contain numbers from 2100 to 2200).

我正在使用VLOOKUP公式,我想要这个公式来查找数字,例如上面的示例表中的2152。但Excel“无法看到”在我的表中,数字2152实际上在单元格A1中(因为单元格A1应该包含从2100到2200的数字)。

Can somebody please help? Is this possible to do in Excel?

有人可以帮忙吗?这可以在Excel中做到吗?

3 个解决方案

#1

I dont know exactly what you want to return, this array formula will return the correct interval in A1:C1:

我不知道你想要返回什么,这个数组公式将在A1中返回正确的间隔:C1:

=INDEX($A$1:$C$1;MATCH(1;(E1>=VALUE(LEFT($A$1:$C$1;4)))*(E1<=VALUE(RIGHT($A$1:$C$1;4)));0))

Numeric value your looking for in E1

您在E1中寻找的数字值

Dont forget to Ctrl Shift Enter to enter the formula...

别忘了按Ctrl Shift Enter输入公式...

#2

Instead of providing the ranges, you need to provide only the lower bound. I.e. try this data:

您需要仅提供下限,而不是提供范围。即试试这个数据:

             A             B           C
Row 1:    2100          2200        2300

Because it is a horizontal setup, you need to use HLOOKUP (VLOOKUP will check the cells in the first column of a table, HLOOKUP the cells in the first row) - and you need to leave the fourth parameter of HLOOKUP/VLOOKUP blank (or set it to TRUE which is the same as leaving it blank). E.g. if you number 2152is in cell A2, use this formula:

因为它是一个水平设置,你需要使用HLOOKUP(VLOOKUP将检查表的第一列中的单元格,HLOOKUP第一行中的单元格) - 并且你需要将HLOOKUP / VLOOKUP的第四个参数留空(或者将其设置为TRUE,这与将其留空是一样的。例如。如果您在单元格A2中编号2152,请使用以下公式:

=HLOOKUP(A2,$A$1:$C$1,1)

and you'll get 2100.

你会得到2100。

If you want to have the full range returned, you should use the MATCH function instead:

如果要返回全范围,则应使用MATCH函数:

=INDEX($A$1:$C$1,MATCH(A2,$A$1:$C$1))&"-"&INDEX($A$1:$C$1,MATCH(A2,$A$1:$C$1)+1)

This will return 2100-2200

这将返回2100-2200

#3

use lower range of your numbers

使用较低的数字范围

                     A        B          C      D
Lower range        2100     2200       2300     2400
Ranking              1        2         3        4

You want to find a number or ranking corresponding to say 2350

你想找到一个对应2350的数字或排名

Formula:   = HLOOKUP(2350,range (range of your values),2,TRUE)

your range includes 2100 -2400 plus 1-4.

你的范围包括2100 -2400加1-4。

the value 2 in the formula indicates the row that you want result returned from - in this case you want ranking - where 2350 will return a 4; change this number to 1 to return exact value.

公式中的值2表示您希望从中返回结果的行 - 在这种情况下您需要排名 - 其中2350将返回4;将此数字更改为1以返回准确值。

sample formula:

= HLOOKUP(O65,J74:N75,2,TRUE)

#1