ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

   
EH云课堂-专业的职场技能充电站 限时送,魔方网表将Excel变在线系统 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
Excel不给力? 何不试试FoxTable! Excel 2016函数公式学习大典 高效办公必会的Office实战技巧 免费下载Excel行业应用视频
300集Office 2010微视频教程 Tableau-数据可视化工具 ExcelHome出品 - VBA代码宝免费下载 13门Excel免费公开课任你学
你的Excel 2010实战技巧学习锦囊 欲罢不能, 过目难忘的 Office 新界面 免费的Excel考勤计算系统
查看: 2810|回复: 40

[Excel 函数与公式] [第122期]补齐缺数

[复制链接]

TA的精华主题

TA的得分主题

发表于 2018-4-13 23:29 | 显示全部楼层 |阅读模式
本帖最后由 wangg913 于 2018-5-16 07:35 编辑

题目说明
A:E列为数据源,数字在1-12之间;
F:L列为模拟结果,补齐的数字不是直接按照从小到大的顺序排列的,而是依照数据源数字的位置依次进行补齐。


补齐数字的方法
1、数据源的数字,从A列起向上补齐缺少的数字,一直补到“比当前位置大的最小值”为止;A列数字补完后,继续补齐B列缺少数字,依次进行直至E列结束。以第2行为例:
A2=9,B2至E2之间比9大的数字只有11(如果其他位置存在多个大于9的数字,则补最小值与9之间的数字),因此 F2=10;
B2=11,其他位置A2、C2:E2之中没有12,因此 G2=12;

2、最大的数字12,反向循环补齐数字,即:数字12应补齐的数字依次为1、2、3。。 。
以第5行为例:
C5=12,因为 A5:B5、D5:E5 中没有1,所以应补1,因此 H5=1;
同理,A5:B5、D5:E5 中也没有2、3,则 I5=2、J5=3。

3、如果数据源其他位置,已经存在比当前位置数字大1的数字,则继续下一下位置。
以第8行为例:A8=1,B8=2,B8就是比A8大1的数字,所以A8位置不需要补缺;
B8=2,C8=3,所以B8位置也不需要补缺;
C8=3,D8=4,所以C8位置也不需要补缺;
D8=4,E8=5,所以D8位置也不需要补缺;
E8=5,数据源中再也没有大于 E8 的数,这时需要把 6-12 全部补齐。


补缺的原则:从A列数字开始,补缺数字直至大于该数字的最小值为止,然后进行B列,依次至E列结束;
每行数据源是5个数字,所以模拟结果一定是7个数字,并且在1-12之间不可能重复。


规则及评分
1、函数题目,不得使用辅助列、自定义函数、名称,不限制EXCEL版本;
2、请在《解答》表的F:L列写公式;
3、竞赛期间, 2018-4-14 至 2018-5-12;
4、评分标准,公式长度(含等号)150字以内奖励1分技术分,110字以内奖励2分,最佳答案奖励3分。




题意不明确或者存在歧义的,请尽早给我发短信。




单选投票, 共有 16 人参与投票
您所在的用户组没有投票权限

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?免费注册

x

评分

参与人数 11财富 +100 鲜花 +28 技术 +2 收起 理由
wshcw + 100 出题奖励,再补财富分
Excel实战分享 + 6 优秀作品
horselyq + 2 优秀作品
moon2778 + 2 出题奖励
michaelaa + 2 优秀作品

查看全部评分

TA的精华主题

TA的得分主题

发表于 2018-4-14 12:14 | 显示全部楼层
本帖最后由 LSL1128xgw 于 2018-5-11 17:47 编辑

=MOD(MODE(IF(TRANSPOSE($A2:E2)<>MOD($A2:E2,12)+1,$A2:E2),E2-OR($A2:E2=MOD(E2,12)+1,F$1>0)*E2),12)+1
=MOD(MODE(IF(TRANSPOSE($A2:E2)<>MOD($A2:E2,12)+1,$A2:E2),(F$1=0)*E2),12)+1
=MOD(MODE((TRANSPOSE($A2:E2)<>MOD($A2:E2,12)+1)*$A2:E2,S2*9+E2),12)+1
=MOD(MODE((TRANSPOSE($A2:E2)<>MOD($A2:E2,12)+1)*$A2:E2,S2%+E2),12)+1  (无模拟数据的话借L列即可)
=MOD(MIN((SUMIF($A2:E2,MOD($A2:E2,12)+1)&MOD(COLUMN($B:F),COLUMN()+(F$1>0)))*12+$A2:E2),12)+1
=MOD(MODE(SUMIF($A2:E2,MOD($A2:E2,12)+1)*{0;9}+$A2:E2,S2%+E2),12)+1




评分

参与人数 9财富 +100 鲜花 +21 技术 +2 收起 理由
wshcw + 100 + 2 优秀作品
太阳之子 + 2 太强大了
象山海鲜 + 3 优秀作品
七夕、 + 3 太强大了
红旗雪 + 2

查看全部评分

TA的精华主题

TA的得分主题

发表于 2018-4-14 13:11 | 显示全部楼层
本帖最后由 象山海鲜 于 2018-4-17 12:06 编辑
  1. =MOD(IF(COUNTIF($A2:E2,MOD(E2-(F$1>0),12)+1),INDEX(2:2,MATCH(,COUNTIF($A2:E2,MOD($A2:E2,12)+1),)),E2),12)+1
复制代码
  1. =MOD(LOOKUP(,0/FREQUENCY(-1,COUNTIF($A2:E2,MOD($A2:E2,12)+1)-($A2:E2-E2=F$1)),$A2),12)+1
复制代码
  1. =MOD(MODE(COUNTIF($A2:E2,$A2:E2+{1;-11})%+$A2:E2,(F$1=0)*E2),12)+1
复制代码

评分

参与人数 12财富 +100 鲜花 +26 技术 +3 收起 理由
micch + 2 太强大了
wshcw + 100 + 3 最佳答案,评3分
太阳之子 + 2 太强大了
Excel实战分享 + 2 厉害了,海鲜老师
nsxlyj + 2 太强大了

查看全部评分

TA的精华主题

TA的得分主题

发表于 2018-4-15 07:44 | 显示全部楼层
本帖最后由 丢丢表格 于 2018-4-15 12:09 编辑

给两种思路吧:
  127字  区域数组  F2:L2
  1. =MOD(SMALL(IF(LOOKUP(A2:E2+ROW($1:7),SMALL(A2:E2+{0;12},ROW($1:10)))=A2:E2,A2:E2+ROW($1:7)+{0,1,2,3,4}*96),COLUMN(A:G))-1,12)+1
复制代码



   82字   单元格数组   F2
  1. =MOD(MIN(COUNTIF($A2:E2,MOD($A2:E2,12)+1)*9^4+$A2:E2+MOD(COLUMN($D:H),9)*96),12)+1
复制代码


.

评分

参与人数 9财富 +100 鲜花 +20 技术 +2 收起 理由
wshcw + 100 + 2 优秀作品
太阳之子 + 2 太强大了
象山海鲜 + 3 优秀作品
七夕、 + 3 太强大了
红旗雪 + 2

查看全部评分

TA的精华主题

TA的得分主题

发表于 2018-4-15 16:00 | 显示全部楼层
凑凑热闹。
F2:L2 输入多单元格数组公式:(139字)
  1. =MONTH(SMALL(DATE(COLUMN(B:F),A2:E2+ROW($2:$8),)+MMULT((ROW($7:$13)>COLUMN())/1%%,COUNTIF(A2:E2,MOD(A2:E2+ROW($1:$7)-1,12)+1)),COLUMN()-5))
复制代码


F2 输入公式,右拉下拉:(113字)
  1. =MOD(MIN(MMULT(IFNA(LEFT(1.7*MATCH(MOD(ROW($1:18)-{1,0},12)+1,$A2:E2,))*5^{1,9},ROW($1:18)*{99,1}),{60;1})),12)+1
复制代码



评分

参与人数 6财富 +50 鲜花 +11 技术 +2 收起 理由
wshcw + 50 + 2 值得肯定
太阳之子 + 2 太强大了
sayhi95 + 2
象山海鲜 + 2
丢丢表格 + 3

查看全部评分

TA的精华主题

TA的得分主题

发表于 2018-4-15 19:28 | 显示全部楼层
本帖最后由 opqazxc 于 2018-4-16 16:25 编辑

`
  1. =MODE(MOD(N(OFFSET($A2,,MOD(COLUMN($F:J)-COLUMN(),COLUMN()-1)))+ROW($12:$22),12)+1)
复制代码

`
  1. =MODE(MOD(N(OFFSET(E2,,MOD(COLUMN($F:J),1-COLUMN())))+ROW($12:$22),12)+1)
复制代码

``

评分

参与人数 7财富 +100 鲜花 +15 技术 +2 收起 理由
wshcw + 100 + 2 优秀作品
太阳之子 + 2 太强大了
七夕、 + 3 太强大了
sayhi95 + 3
象山海鲜 + 2

查看全部评分

TA的精华主题

TA的得分主题

发表于 2018-4-16 13:16 | 显示全部楼层
本帖最后由 mmlzh 于 2018-4-25 14:23 编辑

没达标,F2:L2区域数组公式153字符
=MOD(SMALL(IF(MMULT({-1,1},IFERROR(LARGE(A2:E2,RANK(A2:E2,A2:E2)+{0;-1}),MIN(A2:E2)+12))>ROW($1:$7),A2:E2+ROW($1:$7)+COLUMN(A:E)*24),COLUMN(A:G))-1,12)+1
刚刚及格140字符
=MOD(SMALL(IF(IFERROR(LARGE(A2:E2,RANK(A2:E2,A2:E2)-1),MIN(A2:E2)+12)-A2:E2>ROW($1:$7),A2:E2+ROW($1:$7)+COLUMN(A:E)*60),COLUMN(A:G))-1,12)+1
136
  1. =MOD(SMALL(TEXT(A2:E2+ROW($1:7),"[>="&IFERROR(LARGE(A2:E2,RANK(A2:E2,A2:E2)-1),MIN(A2:E2)+12)&"]98")+{1,2,3,4,5}*12,COLUMN(A:G))-1,12)+1
复制代码

换个思路,大同小异,留下几条参考
83=MODE(MOD(IF((A2:E2=A2)*($A2-A2),E2,$A2:$E2),12)+1,(ROW($1:12)<>$A2:E2)*ROW($1:12))
83=MODE(MOD(IF($A2=A2,A2,E2),12)+1,MOD($B2:$E2,12)+1,(ROW($1:12)<>$A2:E2)*ROW($1:12))
82
  1. =MODE(($A2<>A2)*(MOD(E2,12)+1),MOD($A2:$E2,12)+1,IF(ROW($1:12)-$A2:E2,ROW($1:12)))
复制代码



评分

参与人数 8财富 +100 鲜花 +17 技术 +2 收起 理由
wshcw + 100 + 2 优秀作品
太阳之子 + 2 太强大了
象山海鲜 + 3 优秀作品
七夕、 + 3 太强大了
红旗雪 + 2

查看全部评分

TA的精华主题

TA的得分主题

发表于 2018-4-16 14:43 | 显示全部楼层
  1. =MOD(MODE(IF(COUNTIF($A2:E2,MOD($A2:E2,12)+1)=0,$A2:E2*{1;1}),(COLUMN()>6)*E2),12)+1
复制代码

评分

参与人数 7财富 +100 鲜花 +16 技术 +2 收起 理由
wshcw + 100 + 2 优秀作品
太阳之子 + 2 太强大了
象山海鲜 + 3
七夕、 + 3 太强大了
sayhi95 + 3 太强大了

查看全部评分

TA的精华主题

TA的得分主题

发表于 2018-4-16 17:51 | 显示全部楼层
本帖最后由 nsxlyj 于 2018-5-2 15:38 编辑

=MOD(IF((COLUMN()<>6)*(COUNTIF($A2:E2,MOD(E2,12)+1)=0),E2,RIGHT(MIN(IF(COUNTIF($A2:E2,MOD($A2:E2,12)+1)=0,COLUMN($A:E)/1%+$A2:E2)),2)),12)+1
再优化一下,108字符。想整一分真是太难了
=RIGHT(MIN(IF(COUNTIF($A2:E2,MOD($A2:E2,12)+1)=0,COLUMN($A:E)/IF(COLUMN($A:E)<6,1%,1)%+MOD($A2:E2,12))),2)+1

评分

参与人数 6财富 +50 鲜花 +12 技术 +2 收起 理由
wshcw + 50 + 2 值得肯定
太阳之子 + 2 太强大了
sayhi95 + 3
红旗雪 + 2
丢丢表格 + 2

查看全部评分

TA的精华主题

TA的得分主题

发表于 2018-4-16 21:37 | 显示全部楼层
本帖最后由 太阳之子 于 2018-4-30 16:27 编辑
  1. =MOD(LARGE((ROW($1:24)<LARGE(A2:E2+{0;12},RANK(A2:E2,A2:E2)+4)-A2:E2)*COLUMN(A:E)*48+ROW($12:35)+A2:E2,13-COLUMN()),12)+1
复制代码

评分

参与人数 7财富 +50 鲜花 +15 技术 +2 收起 理由
wshcw + 50 + 2 按要求,改评2分
七夕、 + 3 太强大了
sayhi95 + 3
红旗雪 + 2
丢丢表格 + 2

查看全部评分

您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

关注官方微信,每天学会一个新技能

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

GMT+8, 2019-1-23 10:48 , Processed in 1.120379 second(s), 27 queries , Gzip On.

Powered by Discuz! X3.4

© 2001-2017 Wooffice Inc.

   

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

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

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