生成组合列的列表 [英] Generate a list of combined columns

问题描述

在给定 5 行唯一信息的情况下，我需要生成可能结果的列表.假设 A1=12，A2=34，那么 B1=Some，B2=sOme，B3=soMe，B4=somE，那么 C1=56，C2=78

你得到了最后一行是E的想法，所有的结果都将显示在F行

我只想显示独特的结果，如何在 Google 表格或 Excel 中进行此操作?

这是之前回答的问题的扩展

I need to generate a list of possible outcomes given 5 unique rows of information. say A1=12, A2=34, then B1=Some, B2=sOme, B3=soMe, B4=somE, then C1=56, C2=78

you get the idea all with the final row being E and all the outcomes will be shown in row F

I just want to show unique outcomes, how could do I go this in either Google Sheets or Excel?

this is an expansion of a previously answered question here

Generate all possible combinations for Columns in Google SpreadSheets

解决方案

paste in F1 cell:

=ARRAYFORMULA(
 TRANSPOSE(SPLIT(REPT(CONCATENATE(TRANSPOSE(SPLIT(REPT(CONCATENATE(TRANSPOSE(SPLIT(REPT(CONCATENATE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE(FILTER(A1:A, A1:A<>"")&CHAR(9)),COUNTA(B1:B)),CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(FILTER(B1:B, B1:B<>"")&CHAR(9),COUNTA(A1:A))),CHAR(9)))&CHAR(9)),COUNTA(C1:C)),CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(FILTER(C1:C, C1:C<>"")&CHAR(9),COUNTA(
 TRANSPOSE(SPLIT(REPT(CONCATENATE(FILTER(A1:A, A1:A<>"")&CHAR(9)),COUNTA(B1:B)),CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(FILTER(B1:B, B1:B<>"")&CHAR(9),COUNTA(A1:A))),CHAR(9)))))),CHAR(9)))&CHAR(9)),COUNTA(D1:D)),CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(FILTER(D1:D, D1:D<>"")&CHAR(9),COUNTA(TRANSPOSE(SPLIT(REPT(CONCATENATE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE(FILTER(A1:A, A1:A<>"")&CHAR(9)),COUNTA(B1:B)),CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(FILTER(B1:B, B1:B<>"")&CHAR(9),COUNTA(A1:A))),CHAR(9)))&CHAR(9)),COUNTA(C1:C)),CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(FILTER(C1:C, C1:C<>"")&CHAR(9),COUNTA(
 TRANSPOSE(SPLIT(REPT(CONCATENATE(FILTER(A1:A, A1:A<>"")&CHAR(9)),COUNTA(B1:B)),CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(FILTER(B1:B, B1:B<>"")&CHAR(9),COUNTA(A1:A))),CHAR(9)))))),CHAR(9)))))),CHAR(9)))&CHAR(9)),COUNTA(E1:E)),CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(FILTER(E1:E, E1:E<>"")&CHAR(9),COUNTA(TRANSPOSE(SPLIT(REPT(CONCATENATE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE(TRANSPOSE(SPLIT(REPT(CONCATENATE(A1:A&CHAR(9)),COUNTA(B1:B)),CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(FILTER(B1:B, B1:B<>"")&CHAR(9),COUNTA(A1:A))),CHAR(9)))&CHAR(9)),COUNTA(C1:C)),CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(FILTER(C1:C, C1:C<>"")&CHAR(9),COUNTA(
 TRANSPOSE(SPLIT(REPT(CONCATENATE(FILTER(A1:A, A1:A<>"")&CHAR(9)),COUNTA(B1:B)),CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(FILTER(B1:B, B1:B<>"")&CHAR(9),COUNTA(A1:A))),CHAR(9)))))),CHAR(9)))&CHAR(9)),COUNTA(D1:D)),CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(FILTER(D1:D, D1:D<>"")&CHAR(9),COUNTA(TRANSPOSE(SPLIT(REPT(CONCATENATE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE(FILTER(A1:A, A1:A<>"")&CHAR(9)),COUNTA(B1:B)),CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(FILTER(B1:B, B1:B<>"")&CHAR(9),COUNTA(A1:A))),CHAR(9)))&CHAR(9)),COUNTA(C1:C)),CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(FILTER(C1:C, C1:C<>"")&CHAR(9),COUNTA(
 TRANSPOSE(SPLIT(REPT(CONCATENATE(FILTER(A1:A, A1:A<>"")&CHAR(9)),COUNTA(B1:B)),CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(FILTER(B1:B, B1:B<>"")&CHAR(9),COUNTA(A1:A))),CHAR(9)))))),CHAR(9)))))),CHAR(9)))))),CHAR(9))))

这篇关于生成组合列的列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！