无需VBA：计算游戏玩家当前与最高连胜的Excel公式需求

Got it, let's tackle your win streak calculation problem for your game player stats—no VBA needed, just Excel formulas tailored to your 8 possible result values. First, let's align on what counts as a win, loss, or ignored entry (since these determine how streaks are tracked):

• Win (counts toward streak): Attack Won, Both Attacks Won, Lost, then Win
• Loss (resets streak): Attack Lost, Both Attacks Lost, Win, then Lost
• Ignored (no impact on streak): Did not Participate, Kicked

1. Current Win Streak Formula #

Assume your match results live in column A (starting at A2, ending at A100—adjust this range to match your actual data).

For Excel 365/Online (dynamic arrays): #

This formula uses modern Excel functions to keep things clean and readable:

=LET(
  matchData,A2:A100,
  winValues,{"Attack Won","Both Attacks Won","Lost, then Win"},
  lossValues,{"Attack Lost","Both Attacks Lost","Win, then Lost"},
  // Filter valid results, convert wins to 1, losses to -1, drop ignored entries
  filteredResults,TOCOL(IF(ISNUMBER(MATCH(matchData,winValues&lossValues,0)),IF(ISNUMBER(MATCH(matchData,winValues,0)),1,-1),NA()),3),
  // Find the position of the most recent loss
  lastLossPos,XLOOKUP(-1,filteredResults,SEQUENCE(ROWS(filteredResults)),0,-1),
  // Calculate streak from last loss to end
  currentStreak,SUM(INDEX(filteredResults,lastLossPos+1):INDEX(filteredResults,ROWS(filteredResults))),
  // Ensure we don't return negative numbers (if last result was a loss)
  MAX(currentStreak,0)
)

How it works:

• We first filter out ignored entries and convert valid results to numerical values (1 for win, -1 for loss)
• We find the last time a loss occurred, then sum all results after that point
• If the last result was a loss, the sum will be negative, so we cap it at 0

For older Excel versions (pre-365, no dynamic arrays): #

Use this array formula (enter it with Ctrl+Shift+Enter instead of just Enter):

=IFERROR(MAX(ROW(A2:A100)*(ISNUMBER(MATCH(A2:A100,{"Attack Won","Both Attacks Won","Lost, then Win"},0)))*(ROW(A2:A100)>MAX(IF(ISNUMBER(MATCH(A2:A100,{"Attack Lost","Both Attacks Lost","Win, then Lost"},0)),ROW(A2:A100),0))))-MAX(IF(ISNUMBER(MATCH(A2:A100,{"Attack Lost","Both Attacks Lost","Win, then Lost"},0)),ROW(A2:A100),0)),COUNTIFS(A2:A100,"Attack Won",ROW(A2:A100)>MAX(IF(ISNUMBER(MATCH(A2:A100,{"Attack Lost","Both Attacks Lost","Win, then Lost"},0)),ROW(A2:A100),0)))+COUNTIFS(A2:A100,"Both Attacks Won",ROW(A2:A100)>MAX(IF(ISNUMBER(MATCH(A2:A100,{"Attack Lost","Both Attacks Lost","Win, then Lost"},0)),ROW(A2:A100),0)))+COUNTIFS(A2:A100,"Lost, then Win",ROW(A2:A100)>MAX(IF(ISNUMBER(MATCH(A2:A100,{"Attack Lost","Both Attacks Lost","Win, then Lost"},0)),ROW(A2:A100),0))))

How it works:

• It first finds the row number of the most recent loss
• Then it counts all wins that occurred after that row; if there are no losses, it counts all wins in the range

2. Highest Win Streak Formula #

For Excel 365/Online: #

This formula uses SCAN to build a running streak count, then grabs the maximum value:

=LET(
  matchData,A2:A100,
  winValues,{"Attack Won","Both Attacks Won","Lost, then Win"},
  lossValues,{"Attack Lost","Both Attacks Lost","Win, then Lost"},
  // Filter valid results, convert wins to 1, losses to 0, drop ignored entries
  filteredResults,TOCOL(IF(ISNUMBER(MATCH(matchData,winValues&lossValues,0)),IF(ISNUMBER(MATCH(matchData,winValues,0)),1,0),NA()),3),
  // Build running streak count (reset to 0 on loss)
  streakCounts,SCAN(0,filteredResults,LAMBDA(currentStreak,result,IF(result=1,currentStreak+1,0))),
  // Get the highest streak
  MAX(streakCounts)
)

How it works:

• We filter out ignored entries and convert wins to 1, losses to 0
• SCAN iterates through the filtered list, incrementing the streak count for each win and resetting to 0 for each loss
• MAX picks the largest number from the streak counts, which is your highest win streak

For older Excel versions: #

Use this array formula (enter with Ctrl+Shift+Enter):

=MAX(FREQUENCY(IF(ISNUMBER(MATCH(A2:A100,{"Attack Won","Both Attacks Won","Lost, then Win"},0)),ROW(A2:A100)),IF(ISNUMBER(MATCH(A2:A100,{"Attack Lost","Both Attacks Lost","Win, then Lost"},0)),ROW(A2:A100))))

How it works:

• FREQUENCY calculates how many consecutive win rows exist between each loss row (or the start/end of your data)
• MAX extracts the largest frequency value, which is your longest win streak

Quick Tips to Adjust the Formula #

• Replace A2:A100 with your actual data range (make sure it covers all match results)
• If you need to update which values count as wins or losses, modify the comma-separated lists inside the MATCH functions

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