新知一下
海量新知
6 5 1 3 9 2 6

熟练掌握它们,如“我客戾止,亦有斯容”般也非难事!

EXCEL应用之家 | 传播EXCEL新技能 2022/09/22 08:42

新知达人, 熟练掌握它们,如“我客戾止,亦有斯容”般也非难事!

小伙伴们好,今天要和大家分享一道文本查找的烧脑题目。

题目是这个样子的:

周天子设宴,三个诸侯国派兵保卫,共21名护卫,抽签定次序。宴席上仅安排7个席位,按照各国抽出的人数,以诸侯顺序和各国编号为序,顺次坐入长席。

新知达人, 熟练掌握它们,如“我客戾止,亦有斯容”般也非难事!

这道题目很有意思,东周时候周天子政令不出城,吃个饭还要诸侯派兵保护。请问,如何安排座位?

思考十秒钟…

01

这道题目相对有写复杂,但这道题目里却运用了两个重复显示数据的套路,请大家注意体会。

新知达人, 熟练掌握它们,如“我客戾止,亦有斯容”般也非难事!

在单元格H2中输入公式“=INDEX(CHOOSE(MATCH(LOOKUP(,0/FREQUENCY(ROW(A1),SUMIF(OFFSET($F$2,0,0,ROW($1:$3),1),">0")),$E$2:$E$4)&"?",$A$1:$C$1,),$A$2:$A$8,$B$2:$B$8,$C$2:$C$8),MOD(SMALL(IF($F$2:$F$4<column(a:ac),4^8,row($f$2:$f$4)*100+column(a:ac)),row(a1)),100))”,三键回车并向下拖曳即可。

思路:

  • SUMIF(OFFSET($F$2,0,0,ROW($1:$3),1),">0")部分,利用SUMIF函数生成一个累加的内存数组,结果是{2;3;7}

  • LOOKUP(,0/FREQUENCY(ROW(A1),SUMIF(OFFSET($F$2,0,0,ROW($1:$3),1),">0")),$E$2:$E$4)部分,利用FREQUENCY函数计频,再用LOOKUP函数返回诸侯名

  • MATCH(LOOKUP(,0/FREQUENCY(ROW(A1),SUMIF(OFFSET($F$2,0,0,ROW($1:$3),1),">0")),$E$2:$E$4)&"?",$A$1:$C$1,)部分,返回各国士兵所在的列数

  • CHOOSE(MATCH(LOOKUP(,0/FREQUENCY(ROW(A1),SUMIF(OFFSET($F$2,0,0,ROW($1:$3),1),">0")),$E$2:$E$4)&"?",$A$1:$C$1,),$A$2:$A$8,$B$2:$B$8,$C$2:$C$8)部分,根据当前诸侯返回对应士兵所在的数据区域

  • MOD(SMALL(IF($F$2:$F$4<column(a:ac),4^8,row($f$2:$f$4)*100+column(a:ac)),row(a1)),100)部分是这则公示的第二个部分,返回的是index函数的第二个参数。它的基本含义是,对$f$2:$f$4<column(a:ac)做判断,满足条件的部分返回一个极大数4^8,否则返回row($f$2:$f$4)*100+column(a:ac)。再利用small函数配合mod函数返回各个诸侯国抽签得到的参加宴席的人数

  • 最后利用INDEX返回最终答案。

02

新知达人, 熟练掌握它们,如“我客戾止,亦有斯容”般也非难事!

在单元格H2中输入公式“=IFERROR(INDEX($A$2:$C$8,MOD(SMALL(IF($F$2:$F$4>=COLUMN($A:$Z),ROW($F$2:$F$4)*100+COLUMN($A:$Z)),ROW(A1)),100),MOD(SMALL(IF($F$2:$F$4>=COLUMN($A:$Z),MATCH($E$2:$E$4&"*",$A$1:$C$1,0)+ROW($F$2:$F$4)*100),ROW(A1)),100)),"")”,三键回车并向下拖曳即可。

思路和上例大同小异,这里不详细介绍了。如有问题,朋友们可以私信给我哦!

03

新知达人, 熟练掌握它们,如“我客戾止,亦有斯容”般也非难事!



更多“EXCEL”相关内容

更多“EXCEL”相关内容

新知精选

更多新知精选