Excel中为排名调研结果赋值并计算策略平均排名求助

Hey Seth, let's break down how to solve this ranking averaging problem— I’ve worked through similar survey data challenges before, so here’s a practical, step-by-step approach that handles respondents ranking different numbers of strategies:

First: Restructure Your Data for Easy Calculation #

First off, you’ll want to turn your raw ranking lists into a structured table. This makes all the subsequent math way simpler. Here’s what that should look like:

If a respondent didn’t rank a specific strategy, leave that cell blank (no need to fill in zeros or placeholders— Excel will handle this automatically later).

Step 1: Extract Rankings From Raw Lists (If You Haven’t Already) #

If your data is still in the "single string of ranked strategies" format (like your example), use this formula to pull the rank for a specific strategy. Let’s say Person 1’s ranking string is in cell A2:

To get the rank of "Develop Sites", use:

=MATCH("Develop Sites", TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",100)),(ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1))-1)*100+1,100)),0)

How this works: #

• It replaces spaces with 100 blank spaces (enough to cover any strategy name length)
• Splits the string into individual strategy names using MID
• Uses MATCH to find the position of your target strategy (which equals its rank)

For Excel 365 or newer, you can use the simpler XLOOKUP version:

=XLOOKUP("Develop Sites", TEXTSPLIT(A2, " "), SEQUENCE(COUNTA(TEXTSPLIT(A2, " "))), "")

This splits the string with TEXTSPLIT, generates a matching list of rank numbers with SEQUENCE, and pulls the correct rank for your strategy. If the strategy isn’t in the list, it returns a blank cell.

Step 2: Calculate Average Rank for Each Strategy #

Once you have all ranks in the structured table, calculating averages is straightforward. For example, if "Develop Sites" ranks are in cells B2:B8 (7 respondents), use:

=AVERAGE(B2:B8)

AVERAGE automatically ignores blank cells, so it only calculates the average of ranks from respondents who actually included that strategy in their list— perfect for handling varying list lengths.

Quick Tip for Edge Cases #

If you need to account for unranked strategies (e.g., treat unranked as a "worst possible rank"), you can use this array formula (press Ctrl+Shift+Enter for older Excel versions, or just Enter for Excel 365):

=AVERAGE(IF(B2:B8="", MAX(SEQUENCE(COUNTA(A2:A8)))+1, B2:B8))

This assigns a rank one higher than the longest list from that respondent to any unranked strategies before calculating the average.

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