Question

我在谷歌电子表格里有一堆行看起来是这样的:

a,b,c,d
a,d
c,d
b,d,f
a,b,f,g,h,i
q,b,b

和继续。

我需要一种方法来创建一种独特的列表相关配对以这种格式:

a,b
a,c
a,d
b,c
b,d
c,d
b,f
d,f
a,f
a,g
a,h
a,i

和继续。

使用Google电子表格脚本语言、Excel 2004 Swriting语言或PHP等其它语言,

谢谢!

Answer 1

不清楚这是否满足了您的平台要求, 但这里有一个 < em> 电子表格 < / em > 公式, 可用于 Google 电子表格本身( 任何Excel 版本都没有):

=ArrayFormula(SORT(TRANSPOSE(SPLIT(CONCATENATE(REPT(UNIQUE(TRANSPOSE(SPLIT(JOIN(",";A:A);",")))&","&TRANSPOSE(UNIQUE(TRANSPOSE(SPLIT(JOIN(",";A:A);","))));(UNIQUE(TRANSPOSE(SPLIT(JOIN(",";A:A);",")))<=TRANSPOSE(UNIQUE(TRANSPOSE(SPLIT(JOIN(",";A:A);",")))))*REGEXMATCH(CONCATENATE(","&SUBSTITUTE(A:A;",";",,")&","&CHAR(9));"(,"&UNIQUE(TRANSPOSE(SPLIT(JOIN(",";A:A);",")))&",[^	]*,"&TRANSPOSE(UNIQUE(TRANSPOSE(SPLIT(JOIN(",";A:A);","))))&",)|(,"&TRANSPOSE(UNIQUE(TRANSPOSE(SPLIT(JOIN(",";A:A);","))))&",[^	]*,"&UNIQUE(TRANSPOSE(SPLIT(JOIN(",";A:A);",")))&",)"))&CHAR(9));CHAR(9)))))

它还假设你不想列出“b,a”和“a,b”。

EDIT:对于庞大的数据集来说,这种公式可能非常低效,所以只有在处理几百行或更少的行时才考虑使用。

Answer 2

以下是制作配对的函数 :

<?php
function make_pairs($str) {
  $chars = explode( , , $str);
  for ($i = 0; $i <= count($chars); $i++) {
    $f = array_shift($chars);
    foreach ($chars as $char) 
      echo "$f,$char
";
  }
}

make_pairs( a,b,c,d );

结果:

a,b
a,c
a,d
b,c
b,d
c,d

Answer 3

既然您已经用 VBA 标记了上述问题, 请使用 vba 解决方法。

这将给你们所有45个独特的组合你上面的例子应该有的。

" 强 " 我的假设 " /强 "

<% 1> 数据在工作表 1 的 A 栏中。

2 < 2 > A上校没有页眉

<% 1 > > 3 中生成的输出

4>) 您正在使用Excel 2007+

b,b 被视为有效的组合,因为 q,b,b 。如果不这样做,需要添加一个小节奏。

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long, nRow As Long, n As Long
    Dim i As Long, j As Long, k As Long
    Dim Myar() As String, TempAr() As String

    Set ws = Sheet1
    lRow = ws.Range("A" & Rows.count).End(xlUp).Row

    n = 0: nRow = 1

    With ws
        For i = 1 To lRow
            Myar = Split(.Range("A" & i).Value, ",")
            If UBound(Myar) > 1 Then
                For j = LBound(Myar) To UBound(Myar)
                    For k = LBound(Myar) To UBound(Myar)
                        If j <> k Then
                            ReDim Preserve TempAr(n)
                            TempAr(n) = Myar(j) & "," & Myar(k)
                            n = n + 1
                        End If
                    Next k
                Next j
            Else
                ReDim Preserve TempAr(n)
                TempAr(n) = .Range("A" & i).Value
                n = n + 1
            End If
        Next i

        For i = LBound(TempAr) To UBound(TempAr)
            .Range("B" & nRow).Value = TempAr(i)
            nRow = nRow + 1
        Next i

         ~~> Remove duplicates
        .Range("$B$1:$B$" & UBound(TempAr) + 1).RemoveDuplicates _
        Columns:=1, Header:=xlNo

         ~~> Sort data
        .Range("$B$1:$B$" & UBound(TempAr) + 1).Sort _
        .Range("B1"), xlAscending

        Debug.Print "Total Combinations : " & _
        Application.WorksheetFunction.CountA(Columns(2))
    End With
End Sub

""https://i.sstatic.net/qbU0a.png" alt="此处输入图像描述"/ >

< 强 > FOLLOWUP

无法确定这是否与 2004 Excel Excel 使用, 但替换此行

        .Range("$B$1:$B$" & UBound(TempAr) + 1).RemoveDuplicates _
        Columns:=1, Header:=xlNo

与

        For i = 1 To UBound(TempAr)
            If Application.WorksheetFunction.CountIf(.Range("B" & i).Value) > 1 Then
                .Range("B" & i).ClearContents
            End If
        End With

休息还是我猜的一样测试一下,如果出错通知我?

友情链接