Excel:列出两个数字之间的数字 [英] Excel: Listing Numbers In Between 2 Numbers

问题描述

我想知道是否有人知道列出所有2个值之间的数字的公式，例如，如果单元格F2中有12个，而G2中有17个，我想要一个显示13,14的公式， 15,16在单元格H2中.

I was wondering if anyone knew of a formula to list all the numbers between 2 values, so for example if cell F2 had 12 in it and G2 had 17 in it I'd like a formula that would show 13,14,15,16 In cell H2.

谢谢

推荐答案

这无法通过Excel工作表函数完成.为此，您将需要VBA.可以使用用户定义的函数(UDF)来完成.

This cannot be done with an Excel worksheet function. You will need VBA for that. It can be done with a user-defined function (UDF).

以下代码需要存储在代码模块中.您需要右键单击工作表标签，选择查看代码.这将打开Visual Basic编辑器.单击插入>模块，然后粘贴以下代码:

The following code needs to be stored in a code Module. You need to right-click the sheet tab, select View Code. This will open the Visual Basic Editor. Click Insert > Module and then paste the following code:

Function InBetween(MyFirst As Integer, MyLast As Integer)
Dim foo As String
Dim i As Long
foo = MyFirst + 1
For i = MyFirst + 2 To MyLast - 1
    foo = foo & "," & i
Next i
InBetween = foo
End Function

现在，您可以使用类似=InBetween(F2,G2)的公式来生成您描述的结果.

Now you can use a formula like =InBetween(F2,G2) to produce the result you describe.

您需要将文件另存为具有XLSM扩展名的启用宏的工作簿.有关代码和实际使用的用户定义功能，请参见屏幕截图.

You need to save the file as a macro-enabled workbook with the XLSM extension. See screenshot for code and user defined function in action.

这篇关于Excel:列出两个数字之间的数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！