在Excel中如何根据特定列中的连续行值对以AA，AB，AC，BA，BB，BC，CA，CB，CC的形式进行A，B，C配对
我需要帮助才能做以下事情. 假设我的A列包含A，B或C的值，如下所示.
I need a help to do below thing. Suppose I have column A which contains values either of A,B or C as Follows.
Column A
A
A
B
A
B
B
C
A
现在我要做的是，我想基于两个连续的值对AA，AB，AC进行计数，如果第一行包含A，第二行包含A，那么AA计数也应为1开启.
我很累的是我只能使用以下公式计算两个细胞的数量
=COUNTIFS(A1, "=A",A2,"=B")
但是我不怎么继续增加行数.
请帮助
Now what I have to do is, I want to count pair of AA, AB, AC likewise, based on the two consecutive values, if First row contains A and 2nd row contains A, then AA count should be 1 likewise go on.
What I tired is I can get count for two cells only using following formula
=COUNTIFS(A1, "=A",A2,"=B")
But I don't how to go on increasing the rows.
Please help
您可以使用SUMPRODUCT.
列出所有可能的组合(例如，在下面的C列中).
然后在D1中使用=SUMPRODUCT((($A$1:$A$8&$A$2:$A$9)=C1)*1).向下复制.
或使用两列(例如，下面的F，G列)列出所有可能的组合.
然后在H1中使用=SUMPRODUCT(($A$1:$A$8=F1)*($A$2:$A$9=G1)).向下复制.
You may use SUMPRODUCT.
List all possible combinations (e.g., in column C below).
Then in D1 use =SUMPRODUCT((($A$1:$A$8&$A$2:$A$9)=C1)*1). Copy downwards.
Or list all possible combinations using two columns (e.g., in columns F,G below).
Then in H1 use =SUMPRODUCT(($A$1:$A$8=F1)*($A$2:$A$9=G1)). Copy downwards.
或者您可以按照预期使用COUNTIFS.
在I1中使用两列，使用=COUNTIFS($A$1:$A$8,F1,$A$2:$A$9,G1).向下复制.
Or you may use COUNTIFS as you meant to do.
Using two columns, in I1 use =COUNTIFS($A$1:$A$8,F1,$A$2:$A$9,G1). Copy downwards.