如何使用Excel查找树的定向根? [英] How to find directional roots of a tree using Excel?

问题描述

我有以下问题.谢谢你的帮助

I have the following question. Thank you for helping

有一棵从下到上具有定向根的树. 如何使用Excel查找所有的连接(成对)?

There is a tree with directional roots from bottom to top. How can find all connections (in pairs) by using Excel?

例如，对于子项12的E->列；儿童9:12-9、12-6、12-3和12-1； 9-6、9-3和9-1，儿童6； 6-3和6-1，对于孩子3、3-1是连接.

For example, for column E-> for the child 12; 12-9, 12-6, 12-3 and 12-1, for the child 9; 9-6, 9-3 and 9-1, for the child 6; 6-3 and 6-1, for the child 3, 3-1 are the connections.

我应该写给L2和M2以抄写什么? (不应有空白或重复的连接)

What am I supposed to write to L2 and M2 to copy down? (it should not have blank or repeating connections)

感谢您的帮助.

推荐答案

大图:Stage1:列出每一列的所有可能对|第2阶段:组合线对&删除重复项| Stage3:将结果以所需的格式(L& M列表..)

Big Picture : Stage1 : list all possible pairs for each column | Stage2 : combine the pairs & remove duplicate | Stage3 : put result in the desired format (L & M list..)

[[阶段1]]

想法:加载原始列表[A]>为L& M [B]>负载L&使用索引号[C]的M值

Idea : load the original list[A] > generate index# for L & M [B] > load L & M value using index# [C]

假定数据完全按照工作表中的表1定位.在Sheet2中完成所有这些操作.

Assuming the data is exactly located as per in table1 in Sheet. Do all these in Sheet2..

[创建模板]

• 在A1中输入选择"

• type "choose" in A1

在D1中输入"list:"

type "list :" in D1

在A7中输入多少件商品:"

type "how many item :" in A7

在A9中输入可能的对数:"

type "how many possible pairs :" in A9

放置:

=IF(LEN(OFFSET(Sheet1!$A$1,ROW()-1,$B$1-1))=0,"",OFFSET(Sheet1!$A$1,ROW()-1,$B$1-1)) in E1 and drag until E5

表E1是加载原始列表" [A]的一部分.

table E1 is the "load the original list" [A] part.

然后:

=COUNT(E1:E5) in D7
=COMBIN(D7,2) in D9

现在，开始部分[B]:为L& M生成索引号"

Now, start part [B] : "generate index# for L & M"

然后:

• 在A11中键入生成列表"

• type "generate list" in A11

在A13中输入#"

A13中的类型1

在K13中输入"L"

type "L" in K13

在L13中输入"M"

type "M" in L13

输入以下公式:

=IF(A14<$D$9,A14+1,"") in A15 and drag until A24

然后:

=D7 in C13
=C13-1 in C14

=IF(COUNTIF($C$14:C14,C14)=C14,C14-1,C14) in C15 and drag until C24

然后:

=$C$13-C14 in D14 and drag until D24
=INDEX($E$1:$E$5,D14) in E14 and drag until E24

=IF(E14<>E13,G13+1,G13) in G14 and drag until G24
=COUNTIF($G$14:G14,G14)+G14 in H14 and drag until H24
=INDEX($E$1:$E$5,H14) in I14 and drag until I24

[B]部分的结尾，生成索引号".

End of part [B], "generating the index#".

最后..

=IF(A14="","",E14) in K14 and drag until K24
=IF(A14="","",I14) in L14 and drag until L24

列I& E是[C]部分，使用索引号加载L& M值". 其中K& L列是已清除"版本

Column I&E is the [C] part, "load L & M value using index#" . Where column K&L is the 'cleaned' version

完成创建模板..以获得更好的理解..做这些..

Done creating the template.. for better comprehension.. do these..

• 在C26中键入使用多少项目"值生成索引号""

• type "use "how many item" value to generate "index #"" in C26

在K26中键入按照所需格式对输出列表进行排序"

type "sort the output list as per wanted format" in K26

在D27中键入生成索引号"

type "generate index #" in D27

在E28中键入使用索引号从列表中获取L值"

type "get L value from list using index #" in E28

在G30中键入从值列表中获取项目计数"

type "get item count from value list" in G30

在H31中键入使用项目计数"值生成索引号""

type "use "item count" value to generate "index #"" in H31

在I32中键入使用索引号从列表中获取M值"

type "get M value from list using index #" in I32

[使用模板]

要使用，请在单元格B1中输入第1-10列，结果在K13表中.

To use, enter the column number 1 - 10 in cell B1, and the result is in K13 table..

[注意]

这将为1列生成对列表.您可以通过在sheet3中执行相同操作将其扩展为另一列.更改sheet3中的B1(选择列)值以查看/操纵K13表中的结果/列表.

This is to generate pairs list for 1 column.. you may extend it another column, by doing the same in sheet3. Change the B1 (select column) value in sheet3 to see/manipulate the result/list in K13 table.

直到完成&尝试理解公式的每个部分..，请问您是否卡住/(^ _ ^)

Do it till finish & try to understand each part of the formula.., ask if u r stuck /(^_^)

一些参考:

https://exceljet.net/index-and-match

https://exceljet.net/excel-functions/excel-combin-function

[[Stage 2]]

[[ Stage 2 ]]

想法:为每个工作表生成地址>使用indirect()将列表作为组合列表加载

Idea : generate address for each sheet > use indirect() to load list as a combined list

步骤:将以上内容复制到sheet3并选择第2列(Sheet2中的第1列)..然后重复..Sheet4>列5.Sheet6>列5 ..直到Sheet10 ..现在创建Sheet11并执行这些操作.

Steps : Copy the above to sheet3 and choose column 2 (column 1 in Sheet2).. then repeat.. Sheet4 > Column 5.. Sheet6 > Column 5 .. until Sheet10.. Now create Sheet11 and do these..

• 在A1中输入"Sheet [x]"

• type "Sheet[x]" in A1

在A2中输入"2"

在B1中输入列号"

在B2中输入"1"

在C1中输入"SheetAddrs"

type "SheetAddrs" in C1

在E1中输入"AddrsOfColmnK"

type "AddrsOfColmnK" in E1

在F1中输入"AddrsOfColmnK"

type "AddrsOfColmnK" in F1

在H1中键入每列列出多少对"

type "how many pairs listed per column" in H1

在J1中键入来自"

在J2中输入"1"

在K1中输入至"

然后:

= A3中的A2 + 1并拖动到A10 = B3中的B2 + 1并拖动到B10 ="Sheet"& A2&！"在C2中拖动，直到C10 = E2中的C2&"K:K"并拖动到E10 = F2中的C2&"L:L"，然后拖动到F10 在H2中= COUNT(INDIRECT(E2，TRUE))并拖动到H10 = K2中的J2 + H2-1并拖动到K10 = J3中的K2 + 1并拖动到J10

=A2+1 in A3 and drag until A10 =B2+1 in B3 and drag until B10 ="Sheet"&A2&"!" in C2 and drag until C10 =C2&"K:K" in E2 and drag until E10 =C2&"L:L" in F2 and drag until F10 =COUNT(INDIRECT(E2,TRUE)) in H2 and drag until H10 =J2+H2-1 in K2 and drag until K10 =K2+1 in J3 and drag until J10

使用以上内容作为输入..现在，为每一列中的每一对生成确切的地址并加载它..

Using the above as input.. now generate the exact address for each pair in each column and load it..

在M1中输入"CombineColumnPairList"

type "CombineColumnPairList" in M1

在P1中输入列号"

在第一季度输入"pair#"

type "pair#" in Q1

在S1中输入"Gnerate-L-Addrs"

type "Gnerate-L-Addrs" in S1

在V1中输入"Gnerate-M-Addrs"

type "Gnerate-M-Addrs" in V1

在Y1中输入"FinalLstWithDuplicate"

type "FinalLstWithDuplicate" in Y1

在Z1中输入"L"

在AA1中输入"M"

type "M" in AA1

=IF(N1<SUM(H:H),N1+1,"") in N2 and drag until N60
=IF(N2="","",MATCH(N2,$J$2:$J$11,1)) in P2 and drag until P60
=IF(N2="","",IF(P2<>P1,1,Q1+1)) in Q2 and drag until Q60
=INDEX(C:C,MATCH(P2,B:B,0))&"K" in S2 and drag until S60
=13+Q2 in T2 and drag until T60
=INDEX(C:C,MATCH(P2,B:B,0))&"L" in V2 and drag until V60
=T2 in W2 and drag until W60
=INDIRECT(S2&T2,TRUE) in Z2 and drag until Z60
=INDIRECT(V2&W2) in AA2 and drag until AA60

在Z& AA列中完成了完整列表..

Done with the full listing.. in column Z&AA

[[Stage 3]]

[[ Stage 3 ]]

想法:从stage2完整列表开始.>使用countif()，如果为1，则该对为唯一>使用"1"生成另一个列表，仅该唯一对.

Idea : from stage2 full list.. > use countif(), if 1, then the pair is unique > Use the "1" to generate another list, the unique pairs only.

• 在AC1中输入"[第3阶段]找到唯一"

• type "[Stage 3] Find Unique" in AC1

键入有多少个独特的?"在AG1中

type "howmany unique?" in AG1

在AI1中输入"FinalLstWithNoDuplicate"

type "FinalLstWithNoDuplicate" in AI1

在AK1中输入"L"

type "L" in AK1

在AL1中输入"M"

type "M" in AL1

然后:

=Z2&"-"&AA2 in AD2 and drag until AD30
=COUNTIF($AD$2:AD2,AD2) in AE2 and drag until AE30
=IF(AE2=1,AF1+1,AF1) in AF2 and drag until AF30
=COUNTIF(AE:AE,1) in AG2
=IF(AJ1<$AG$2,AJ1+1,"") in AJ2 and drag until AJ30
=IFERROR(INDEX(Z:Z,MATCH(AJ2,AF:AF,0)),"") in AK2 and drag until AK30
=IFERROR(INDEX(AA:AA,MATCH(AJ2,AF:AF,0)),"") in AL2 and drag until AL30

列AK& AL应该是您想要的结果.(:

Column AK&AL should be ur desired resutls.. ( :

注意:由于您已经了解了公式/方法的逻辑，所以您现在知道根据输入表的大小..所有给定的公式都是可调整的.

Note : Since you had get the logic of the formula/method.. then u know by now that depending on the input table size.. all the given formula are adjustable..

希望有帮助.

这篇关于如何使用Excel查找树的定向根?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！