Question

excel如何实现在1-12当中，第一排为1 3 4 5 6 7 8 时，第二排自动填写2 9 10 11 12 呢

excel如何实现在1-12当中，第一排为1 3 4 5 6 7 8 时，第二排自动填写2 9 10 11 12 呢如图中所示，该如何实现呢

Answer 1

公式很难实现，楼上的公式似乎有误。

如图效果。

在excel表单按Alt+F11呼出工程管理器，在工具栏点插入-模块，将下面代码复制到里面。即可使用aa公式来直接套你所要的单元格得出结果

Function aa(x)

a = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12)

b = Replace(x, " ", ",")

b = "," & b & ","

For i = 0 To 11

If Not InStr(b, "," & a(i) & ",") > 0 Then

If c = "" Then

c = a(i)

Else

c = c & " " & a(i)

End If

End If

Next

aa = c

End Function

Answer 2

B1=INDIRECT(ADDRESS(ROW(A1)*3-1,1))
公式下拉即可。

追问

怎么无法实现呢 能详细说明一下吗

Answer 3

B5公式：（B3中数据，每个数据后需要输入一个半角空格）
=TRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTITUTE(SUMPRODUCT(ISERR(FIND(ROW($1:$9)&" ",B3))*ROW($1:$9)*10^(9-ROW($1:$9))),0," "),2,0," "),4,0," "),6,0," "),8,0," "),10,0," "),12,0," "),14,0," "),16,0," "),18,0," "))&" "&TRIM(REPLACE(REPLACE(SUMPRODUCT(ISERR(FIND({10,11,12},B3))*{10,11,12}*10^(6-{2,4,6})),3,0," "),6,0," "))