<p>Sub 首尾相连中级3()<br/>'结果输出到B列'''''''''''''''''ldy<br/> Dim aa(), l As Long, i As Long, ed As Long<br/> t = Timer<br/> ed = Range("a1").End(xlDown).Row<br/> Range("b:g").ClearContents<br/> rg = Range("a1").Resize(ed, 7) '建立一个7 列的数组,<br/> '1 原始数据, 2 字头, 3字尾 ,4 下家数量 5 上家数量 6 下家的位置(与天堂鼠思路相同),7 去掉字头的文字<br/> ' 取得字头字尾<br/> For i = 1 To ed<br/> rg(i, 2) = Left(rg(i, 1), 1)<br/> rg(i, 3) = Right(rg(i, 1), 1)<br/> Next</p><p>'<br/> For i = 1 To ed<br/> For j = 1 To ed<br/> If i <> j Then<br/> If rg(i, 3) = rg(j, 2) Then<br/> rg(i, 4) = rg(i, 4) + 1 '4 下家数量<br/> rg(i, 6) = rg(i, 6) & " " & j '6 下家的位置<br/> End If<br/> If rg(i, 2) = rg(j, 3) Then rg(i, 5) = rg(i, 5) + 1 ' 5 上家数量<br/> End If<br/> Next</p><p> If rg(i, 5) > 0 Then '如果有上家<br/> rg(i, 7) = Mid(rg(i, 1), 2) '去掉字头<br/> Else<br/> rg(i, 7) = rg(i, 1) '没有上家说明是句头 保留原样<br/> End If<br/> Next</p><p>' Range("a1").Resize(ed, 7) = rg '2激活可看数据结构</p><p>' 数据的各项属性建立完成,在递归的大循环中直接调用,快很多</p><p> </p><p><br/> l = 0<br/> For i = 1 To ed<br/> If rg(i, 5) = 0 And rg(i, 4) <> 0 Then<br/> TRT1 i, aa, l '递归求出所有 有效组合<br/> End If</p><p> If rg(i, 5) = 0 And rg(i, 4) = 0 Then '没有上家和下家的说明是独立的句子<br/> l = l + 1<br/> ReDim Preserve aa(1 To l)<br/> aa(l) = rg(i, 1)<br/> End If<br/> Next</p><p> <br/> MsgBox Timer - t & " 秒 " & l & " 条"<br/> <br/> '填表比较费时 大约5-8秒,想看结果请激活</p><p>' Range("h1").Resize(l, 1) = WorksheetFunction.Transpose(aa)</p><p>End Sub</p><p> </p><p>Sub TRT1(Y As Long, aa(), LL As Long)<br/>''''''''''递归过程''''''''''ldy<br/>'''''''初次写递归 效率不理想<br/> Dim j As Long, OL As Long, R7, x As Long<br/> R7 = Split(rg(Y, 6))<br/> For ii = 1 To rg(Y, 4) '下家的数量<br/> OL = LL + 1<br/> j = R7(ii) '第 ii 个下家的位置<br/> If rg(j, 4) = 0 Then<br/> LL = LL + 1<br/> ReDim Preserve aa(1 To LL)<br/> aa(LL) = rg(Y, 7) & rg(j, 7)<br/> Else<br/> TRT1 j, aa, LL<br/> aa(LL) = rg(Y, 7) & aa(LL)<br/> End If<br/> If rg(j, 4) > 0 Then<br/> For x = OL To LL<br/> If InStr(aa(x), rg(Y, 7)) <> 1 Then aa(x) = rg(Y, 7) & aa(x)<br/> Next<br/> End If<br/> Next<br/>End Sub</p><p> </p><p>把三个人的做到一起了<br/></p><br/> |