函数vs VBA：关于城市名个数与二级下拉列表
本帖最后由 兰色幻想 于 2011-5-25 08:59 编辑今天兰色出一个函数和VBA综合题，第一问需要用函数完成，第二问用VBA完成。完成一个即有奖励，完成两个有双重奖励
题目：
一、函数题目:
要求在E列生成每个城市的个数
注:
1 已知右表区域为A1:B12,省市个数不定,每个省城市个数不定.
2 生成的个数是可以参与后续计算的内存数组形式
3、公式长度不要超过88个字符（算=号）
4、公式不能借用D列区域。
二、VBA题目:
1 在I列生成可以选取的下拉列表，列表里是所有省市的名称，列表内不得有空行。
2 在J列生成二级下拉列表，根据I列的省市显示出这个省市的所有城市名称。列表内也不得有空行
图：
本题已设置仅作者可见，不必设置回复可见，两天后解除。
回复 兰色幻想 的帖子
=MMULT(N(TRANSPOSE(LOOKUP(ROW(2:12),IF(A2:A12>0,ROW(2:12)),A2:A12))=D3:D7),ROW(1:11)^0)
连等号87字符。{:011:}
先交函数，再去想VBA
做个记号:lol 本帖最后由 lisachen 于 2011-5-23 16:50 编辑
=MMULT(N(D3:D7=TRANSPOSE(LOOKUP(ROW(1:11),IF(A2:A12>0,ROW(1:11)),A2:A12))),ROW(1:11)^0)先交函数部分
呵呵 不用D列
88字符有难度
再想想
这个行不=FREQUENCY(SUBTOTAL(3,OFFSET(A2,,,ROW(A1:A11))),ROW(OFFSET(A1,,,COUNTA(A2:A12)-1))) 本帖最后由 chunlin1wang 于 2011-5-23 17:39 编辑
=FREQUENCY(ROW(2:12),IF((A2:A12<>"")*(ROW(2:12)>2),ROW(1:11)))
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 9 And Target.Row > 2 And Target.Row < 12 Then
For i = 2 To Range("A65536").End(xlUp).Row
If Range("A" & i) <> "" Then
l = l & "," & Range("A" & i)
n = n + 1
End If
Next i
With Target.Validation
.Delete
.Add Type:=xlValidateList, Formula1:=Mid(l, 2, Len(l) - 1)
End With
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 9 And Target.Row > 2 And Target.Row < 12 Then
For i = 2 To Range("b65536").End(xlUp).Row
If Range("A" & i).Value = Target.Value Then
Cells(Target.Row, Target.Column + 1) = Range("b" & i)
For k = i + 1 To Range("b65536").End(xlUp).Row
If Range("A" & k) = "" Then
m = m + 1
Else
Exit For
End If
Next k
With Cells(Target.Row, 10).Validation
.Delete
.Add Type:=xlValidateList, Formula1:="=B" & i & ":B" & m + i
End With
Exit Sub
End If
Next i
End If
End Sub
看来思路还是有问题，122字符。:dizzy:
VBA更是笨办法 :dizzy:
=FREQUENCY(ROW(2:12),IF(A3:A13<>"",ROW(2:12))) =INDEX(FREQUENCY(ROW(2:13),IF(A2:A13<>"",ROW(2:13))),N(IF(1,{2;3;4;5;6})))