Excel-用于对多对非顺序列中的条目进行计数的公式 [英] Excel - Formula For Counting Entries Within Multiple Pairs of Non-Sequential Columns
问题描述
我有以下示例矩阵：
A B C D E F G H I J K L M N O P Q R S T
1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
2 1 1 1
3 1 1 1 1 1 1
4 1 1 1 1 1 1
5 1 1 1 1 1 1 1
我正在计算至少一对非行的行数-除A行以外的连续列在两个列中都包含条目。已知成对的列，但列之间的距离不是恒定的。在当前示例中，列B& K和C& L成对（距离或d = 9），E&我和F& J成对（d = 4），而G& A S和H& T是成对的（d = 12）。请注意，列D没有与任何其他列配对。这经常发生：通常，配对之间的列距离在没有内在配对的列之后改变，例如A&A。 E（d = 4），B& F（d = 4），C没有配对，D& K（d = 7），E& L（d = 7），等等。
I am trying to count the number of rows for which at least one pair of non-sequential columns excluding row A includes entries in both columns. The pairs of columns are known but the distance between columns is not constant. In the current example, columns B & K and C & L are paired (distance or d = 9), E & I and F & J are paired (d = 4), and G & S and H & T are paired (d = 12). Notice that column D is not paired with any other column. This frequently occurs: usually the column distance between pairings changes after a column with no intrinsic pairing, e.g., A & E (d = 4), B & F (d = 4), C has no pairing, D & K (d = 7), E & L (d = 7), etc.
在当前示例中，至少一对列（如上所述）包含条目的行数为3例如，第1行的所有列对均具有条目，第3行的列对F&中具有条目。 J以及G& S，第4行包含用于列配对C& L。
In the current example, the number of rows where at least one pair of of columns (as specified above) include entries is 3. For example, row 1 features entries for all pairs of columns, row 3 has entries for column pair F & J, as well as G & S, and row 4 has entries for column pairing C & L.
以下是我写的数组公式的示例：
Here's a sample of an array formula I wrote:
=COUNT(IFS(SUBTOTAL(2,OFFSET(B1,ROW(B1:B5)-ROW(B1),0,1,COLUMNS(B1:C1)))>1,SUBTOTAL(2,OFFSET(A1,ROW(A1:A5)-ROW(A1),0,1,COLUMNS(A1))),SUBTOTAL(2,OFFSET(C1,ROW(C1:C5)-ROW(C1),0,1,COLUMNS(C1:D1)))>1,SUBTOTAL(2,OFFSET(A1,ROW(A1:A5)-ROW(A1),0,1,COLUMNS(A1)))))
它计算成对的顺序列（例如B& C，C& D）的条目数）。我不确定如何更新它，以便它指定成对的非顺序列（例如，如上所述的B& K，C& L，E& I等）。
It counts the number of entries for pairs of sequential columns (e.g., B & C, C & D). I am unsure how to update it so that it specifies pairs of non-sequential columns (e.g., B & K, C & L, E & I, etc. as specified above).
有以下条件：
- 您可能不认为SUBTOTAL是最有效的选择（例如MMULT abd SUBTOTAL可能更合适）； 但是，我仅限于在单个excel公式中表达这一点（不幸的是，没有VBA代码）。我也无法创建新的列或行。
- 实际矩阵中的矩阵条目不一定为1，但它们是整数（范围为1至100）。
- 矩阵，有8个列配对，其距离= 9，11个列配对的距离为12，和3个列配对的距离为3。
- You may not think SUBTOTAL is the most efficient option (e.g., MMULT abd SUBTOTAL might be more appropriate); however, I am limited to expressing this within a single excel formula (no VBA code, unfortunately). I cannot create new columns or rows either.
- The matrix entries are not necessarily 1 in the actual matrix, but they are whole numbers (ranging from 1-100).
- Within the full matrix, there are 8 column pairings with a distance = 9, 11 column pairings with a distance of 12, and 3 column pairings with a distance of 3.
推荐答案
尝试以下公式
=SUM(IF((
IF(B1:B5>0,IF(K1:K5>0,1))+
IF(C1:C5>0,IF(L1:L5>0,1))+
IF(E1:E5>0,IF(I1:I5>0,1))+
IF(F1:F5>0,IF(J1:J5>0,1))+
IF(G1:G5>0,IF(S1:S5>0,1))+
IF(H1:H5>0,IF(T1:T5>0,1))
) > 0, 1, 0))
您可以在上面的列表中添加其余对，我已经添加了对您已经提到过。
You can add the remaining pairs in the above list, i have added the ones you have mentioned.
这篇关于Excel-用于对多对非顺序列中的条目进行计数的公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
