Excel如何查询国家与城市唯一组合对应的最高温度

适用场景说明 #

现有Excel表格结构：A列为国家（country）、B列为城市（city）、C列为温度（temp），需要提取每个国家对应最高温度的国家+城市+温度组合。样例输出：usa, NY : 20、uk , Manchester : 10.8

Excel 365 / 2021 及以上版本（支持动态数组） #

直接在空白单元格输入以下公式，会自动溢出所有符合要求的结果：

直接返回结构化三列结果（国家/城市/温度分开展示） #

=UNIQUE(SORTBY(A2:C100,C2:C100,-1),TRUE)

公式说明：先将所有数据按温度从高到低排序，再按国家列去重，保留每个国家第一次出现的（即温度最高的）行记录

直接返回指定格式的文本结果 #

=BYROW(UNIQUE(SORTBY(A2:C100,C2:C100,-1),TRUE),LAMBDA(x,TEXTJOIN({" , "," : "},,x)))

旧版Excel（2019及更早版本） #

需要分步骤操作：

1. 先提取去重的国家列表：复制A列数据到空白列（比如E列），点击「数据」选项卡-「删除重复值」，得到所有不重复的国家
2. F列（对应E列国家的最高温度）输入公式，选中F2单元格输入后按Ctrl+Shift+Enter触发数组计算，下拉填充：
   =MAX(IF(A$2:A$100=E2,C$2:C$100))
3. G列（对应最高温度的城市）输入公式，选中G2单元格输入后按Ctrl+Shift+Enter触发数组计算，下拉填充：
   =INDEX(B:B,MATCH(1,(A$2:A$100=E2)*(C$2:C$100=F2),0)+1)
4. 如果需要合并成指定格式的文本，H列输入公式下拉即可：
   =E2&", "&G2&" : "&F2

注意事项 #

• 公式中的范围A2:C100、A$2:A$100需要替换为你实际的数据源范围，避免整列引用造成计算卡顿
• 如果同一个国家有多个城市温度同为最高值，旧版公式会返回排序中第一个出现的城市，365版本会返回所有符合条件的记录

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