如何用Excel公式实现行内数字对组合出现次数统计

Got it, let's replace that VBA code with a clean, fast Excel formula approach—no macros required, and it'll calculate your pair counts in a flash, even for your 50-row dataset.

Setup Context #

First, let's align with your original setup:

• Your raw data (5 columns of 1-9 numbers, 50 total rows) lives in Plan1, columns A-E, starting at row 2 (so range Plan1!A2:E51).
• Your 9×9 combination matrix is in Plan2, where:
  • Row 1 (B1:J1) and Column A (A2:A10) contain the numbers 1-9 (these are your pair values to count).
  • Cells B2:J10 will hold the count of rows where both the row number and column number appear together.

Formula Options #

Option 1: Excel 365/2021 (Dynamic Array Support)

This is the simplest and fastest method. Drop this formula into Plan2!B2, then drag it across the entire matrix (B2:J10):

=IF($A2=B$1,"",SUMPRODUCT(--(MMULT(--(Plan1!$A$2:$E$51=$A2),ROW($1:$5)^0)>0),--(MMULT(--(Plan1!$A$2:$E$51=B$1),ROW($1:$5)^0)>0)))

Let's break down what this does:

• --(Plan1!$A$2:$E$51=$A2): Creates a TRUE/FALSE matrix where each cell is TRUE if it matches the row header number (e.g., 4 in row 2). The -- converts TRUE/FALSE to 1/0.
• MMULT(...,ROW($1:$5)^0): Sums the 1s across each row. A sum greater than 0 means the row contains the row header number.
• We repeat this logic for the column header number, then SUMPRODUCT counts how many rows have both numbers present.
• The IF($A2=B$1,"",...) skips the diagonal (same-number pairs), matching your VBA's behavior.

Option 2: Older Excel Versions (No Dynamic Arrays)

If you're using an older Excel version that doesn't support dynamic arrays, use this array formula (enter it with Ctrl+Shift+Enter instead of just Enter):

=IF($A2=B$1,"",SUMPRODUCT((COUNTIF(OFFSET(Plan1!$A$2:$E$2,ROW(Plan1!$A$2:$A$51)-2,0),$A2)>0)*(COUNTIF(OFFSET(Plan1!$A$2:$E$2,ROW(Plan1!$A$2:$A$51)-2,0),B$1)>0)))

How this works:

• OFFSET(Plan1!$A$2:$E$2,ROW(...) - 2,0): Extracts each individual row of data from Plan1.
• COUNTIF(..., $A2)>0: Checks if the row contains the row header number.
• Multiplying the two COUNTIF results gives 1 only if both numbers are present in the row, and SUMPRODUCT adds those up.

Why This Is Better Than VBA #

• Faster Calculation: Formulas run natively in Excel and will compute your 9×9 matrix instantly, even with more than 50 rows.
• No Macros: No need to enable macros, which is safer and avoids compatibility issues.
• Easy to Maintain: No VBA code to debug or update—just adjust the data range if your dataset grows.

内容的提问来源于stack exchange，提问作者Musicodelic