如何用Excel函数查找出现次数第1、2、3多的公司名称？

Got it, let's break this down—you need to extract the most frequent, second most frequent, etc., company names from a column where repeats tie to ticket counts. Here are tailored solutions based on your Excel version:

For Excel 365/2021 (Dynamic Array Support) #

This is the easiest route thanks to dynamic array functions that handle most of the heavy lifting automatically:

1. Get a sorted list of companies by frequency (descending)
   In an empty cell (say, B1), enter this formula:

   =SORTBY(UNIQUE(A:A), COUNTIF(A:A, UNIQUE(A:A)), -1)
   

   This will "spill" a full list of unique companies, ordered from the one with the most occurrences to the least. No need to drag down—Excel fills the rest automatically.

2. Extract a specific top N entry
   If you only want, say, the 3rd most frequent company, use INDEX to target that position:

   =INDEX(SORTBY(UNIQUE(A:A), COUNTIF(A:A, UNIQUE(A:A)), -1), 3)
   

   Just change the last number to get the 1st, 2nd, 4th, etc., entry.

For Older Excel Versions (2019 & Earlier, No Dynamic Arrays) #

You'll need to use array formulas (press Ctrl+Shift+Enter after entering each formula instead of just Enter) to make this work:

1. Get the most frequent company (1st place)
   In B1, enter:

   =INDEX($A:$A, MATCH(MAX(COUNTIF($A:$A, $A:$A)), COUNTIF($A:$A, $A:$A), 0))
   

   Press Ctrl+Shift+Enter—you'll see curly braces {} around the formula if done right.

2. Get the second most frequent company (2nd place)
   In B2, enter this formula to exclude the already found top company:

   =INDEX($A:$A, MATCH(LARGE(COUNTIF($A:$A, $A:$A)+(COUNTIF($B$1:B1, $A:$A)*10^9), 2), COUNTIF($A:$A, $A:$A)+(COUNTIF($B$1:B1, $A:$A)*10^9), 0))
   

   Again, press Ctrl+Shift+Enter, then drag this formula down to get 3rd, 4th, etc., places. The *10^9 trick adds a huge number to the count of companies we've already pulled, so LARGE skips them entirely.

Quick Notes #

• If multiple companies have the same frequency (a tie), the 365/2021 method will list all tied companies consecutively. For older versions, it will pick the first occurrence of the tied company in your original column.
• Make sure your column A doesn't have blank cells—if it does, add a filter to UNIQUE (like UNIQUE(FILTER(A:A, A:A<>""))) to exclude blanks from the list.

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