ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
300集Office 2010微视频教程 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
查看: 4433|回复: 21

[求助] 返回数据源里排名前三的学生姓名的,多个不同写法的公式

[复制链接]

TA的精华主题

TA的得分主题

发表于 2019-1-15 22:24 | 显示全部楼层 |阅读模式
本帖最后由 WYS67 于 2019-1-16 15:19 编辑

20190115221816.gif

返回数据源里排名前三的学生姓名.zip (10 KB, 下载次数: 43)

INDIRECT+ADDRESS的公式:
=INDIRECT(ADDRESS(4,MATCH(LARGE($A5:$G5,COLUMN(A$1)),$A5:$G5,))) COLUMN(A$1)可以调用I4:J4的1~3作为名次参数使用
  ,右拉至K5,3列同时下拖到K22获得计算结果。
     
INDEX的公式:
在I5里输入 =INDEX($A$4:$G$4,MATCH(LARGE($A5:$G5,P$4),$A5:$G5,)),右拉则依次是第二名、第三名,里边的COLUMN(A1)代表名次。

OFFSET的公式:
在I5里输入=OFFSET($A$4,,MATCH(LARGE($A5:$G5,COLUMN(A$1)),$A5:$G5,)-1),右拉则依次是第二名、第三名,里边的COLUMN(A1)代表名次。

LOOKUP的公式:
在I5里输入=LOOKUP(1,0/($A5:$G5=LARGE($A5:$G5,COLUMN(A$1))),$A$4:$G$4),右拉则依次是第二名、第三名,里边的COLUMN(A1)代表名次。

HLOOKUP的公式:
在I5里输入=HLOOKUP(LARGE($A5:$G5,COLUMN(A$1)),IF({1;0},$A5:$G5,$A$4:$G$4),2,FALSE),右拉则依次是第二名、第三名,里边的COLUMN(A1)代表名次。

2楼飞天篮球猪老师和9楼我走我流老师写的代码和公式太艰深,不是我辈一时半会儿能够学会和并灵活运用的,只能慢慢领悟了。


不知不觉,已经费心写了五个用不同函数查询同一问题的公式了!如果哪位老师有更言简意赅的好公式,敬请奉上,本人感谢不尽!

TA的精华主题

TA的得分主题

发表于 2019-1-15 23:38 | 显示全部楼层
提供一个Power Query的解法,供参考。excel2016+office365内置功能,2010+2013版本需下载插件。左侧数据源变动,右侧结果表右键刷新更新。
两种情况,分数一样的按美式排名,就简单一些,M语句如下:
  1. let
  2.     源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],

  3.     变 = #table( List.TransformMany({"姓名","成绩"},each {"1".."3"},(x,y)=>x&y),
  4.                 Table.TransformRows(源,(x)=> List.Combine(Table.ToColumns(Table.MaxN(Record.ToTable(x),each [Value],3)))))
  5. in
  6.     变
复制代码

分数一样的按中式排名,有并列第一,第二,第三的稍微复杂一些。M语句如下,这种写法也包含了上述第一种情况,更全面。
  1. let
  2.     源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],

  3.     变 = #table(  List.TransformMany({"姓名","成绩"},each {"1".."3"},(x,y)=>x&y),
  4.                   Table.TransformRows(源,(x)=>List.Combine(
  5.                                                            List.Zip(
  6.                                                                     Table.MaxN(
  7.                                                                                Table.Group( Record.ToTable(x),
  8.                                                                                             "Value",
  9.                                                                                              {"a",each {Text.Combine([Name],"、"),[Value]{0}}},1),
  10.                                                                                 "Value",3)[a]))))
  11. in
  12.     变
复制代码
一并附上文件:
返回数据源里排名前三的学生姓名.xlsx.zip (17.95 KB, 下载次数: 18)

评分

2

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2019-1-16 02:36 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
飞天篮球猪 发表于 2019-1-15 23:38
提供一个Power Query的解法,供参考。excel2016+office365内置功能,2010+2013版本需下载插件。左侧数据源 ...

代码很强大!但我只是想用最简单的函数公式提取到前一、二、三名的姓名就行了!老师的代码有点高射炮打蚊子--大材小用了!

TA的精华主题

TA的得分主题

 楼主| 发表于 2019-1-16 08:56 | 显示全部楼层
恳请老师们用INDEX,OFFSET,LOOKUP......函数,各写出简洁明快的公式

TA的精华主题

TA的得分主题

发表于 2019-1-16 08:59 来自手机 | 显示全部楼层
WYS67 发表于 2019-1-16 02:36
代码很强大!但我只是想用最简单的函数公式提取到前一、二、三名的姓名就行了!老师的代码有点高射炮打蚊 ...

哈哈,打扰了,兄弟。

TA的精华主题

TA的得分主题

 楼主| 发表于 2019-1-16 09:07 | 显示全部楼层
飞天篮球猪 发表于 2019-1-16 08:59
哈哈,打扰了,兄弟。

老师:主要原因是我太笨,孤陋寡闻:excel函数公式还写不好,VBA也只是会比葫芦画瓢地使用别人写好的代码,至于您2楼的方法更是闻所未闻。着实让您见笑了!

TA的精华主题

TA的得分主题

发表于 2019-1-16 09:15 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
WYS67 发表于 2019-1-16 09:07
老师:主要原因是我太笨,孤陋寡闻:excel函数公式还写不好,VBA也只是会比葫芦画瓢地使用别人写好的代码 ...

您志不在此。。。
Excel目前有三种函数,
工作表函数
M函数用于Power Query
Dax函数用于PowerPivot....这是题外话了,坐等大佬的公式。

TA的精华主题

TA的得分主题

 楼主| 发表于 2019-1-16 09:19 | 显示全部楼层
飞天篮球猪 发表于 2019-1-16 09:15
您志不在此。。。
Excel目前有三种函数,
工作表函数

主要是:上学时没有学过一天英语,基础太差!

TA的精华主题

TA的得分主题

发表于 2019-1-16 09:55 | 显示全部楼层
=LOOKUP(1,0/(LARGE(OFFSET($A5,,,,7)/1%%+COLUMN($A:$G),MOD(COLUMN(A1)-1,3)+1)=OFFSET($A5,,,,7)/1%%+COLUMN($A:$G)),IF(COLUMN(A1)<=3,$A$4:$G$4,$A5:$G5))
数组右下拉

评分

1

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2019-1-16 10:44 | 显示全部楼层
我走我流 发表于 2019-1-16 09:55
=LOOKUP(1,0/(LARGE(OFFSET($A5,,,,7)/1%%+COLUMN($A:$G),MOD(COLUMN(A1)-1,3)+1)=OFFSET($A5,,,,7)/1%%+CO ...

老师:假如指定I4:K4为排位名次1~3,
公式:=MATCH(LARGE($A5:$G5,I$4),$A5:$G5,)  的计算结果等于7【代表最高分数在第七列G】;如果现在用OFFSET函数编写公式,以$A$4为原点,那么在I5里,不是应该把=MATCH(LARGE($A5:$G5,I$4),$A5:$G5,)   当作公式 =OFFSET($A$4, ..... )的列偏移参数,才能得出正确的返回结果“陈小春”吗?可是我试了好多次都不行。无论是=OFFSET($A$4,,MATCH(LARGE($A5:$G5,I$4),$A5:$G5,)),还是=OFFSET($A$4,,,,MATCH(LARGE($A5:$G5,I$4),$A5:$G5,)),都无法返回正确的结果?恳请老师指出原因何在?
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-3-28 22:47 , Processed in 0.051459 second(s), 10 queries , Gzip On, Redis On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表