ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

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

[Excel 函数与公式] 【67期】员工调动统计

[复制链接]

TA的精华主题

TA的得分主题

发表于 2010-11-2 15:48 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
=INDEX(T(OFFSET(题目!A2,IF({1,1,1,1,0,0},ROW(1:187),MATCH(题目!H3:H189&0,题目!C3:C189&0,)),{7,8,5,6,0,1})),SMALL(IF(T(OFFSET(题目!A2,MATCH(题目!H3:H188,题目!C3:C188,),{0,1}))<>题目!F3:G188,ROW(1:186),187),ROW(1:25)))
字符数: 202

如版主所说,考量之处都很常见
T(OFFSET(…))三维转二维
IF({1,0},…)组合内存数组
MATCH(…&"",…&"")排错处理
期待精彩答案

思路:
架构公式
INDEX(数组,{行,行,行,行,行,行})

关键是将分散的7月与8月资料按姓名索引,组合为新的内存数组
OFFSET(INDIRECT("题目!"&{"H","I","F","G","A","B"}&2),IF({1,1,1,1,0,0},ROW(1:187),MATCH(题目!H3:H189&"",题目!C3:C189&"",)),)
简化:
OFFSET(题目!A2,IF({1,1,1,1,0,0},ROW(1:187),MATCH(题目!H3:H188&0,题目!C3:C188&0,)),{7,8,5,6,0,1})

判断原有岗位是否变动
OFFSET(题目!A2,MATCH(题目!H3:H188,题目!C3:C188,),{0,1}))<>题目!F3:G188

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2010-11-3 14:19 | 显示全部楼层

员工调动统计表fugb-2010

选定 结果!B4:G28 区域
公式=INDEX(题目!A:I,LEFT(RIGHT(TEXT(SMALL(IF(INDEX(题目!A3:A188&题目!B3:B188,MATCH(题目!H3:H188,题目!C3:C188,))=题目!F3:F188&题目!G3:G188,10^6-1,ROW(3:188)*10^3+MATCH(题目!H3:H188,题目!C1:C188,)),ROW(1:25)),"000000"),{6,6,6,6,3,3}),3),{8,9,6,7,1,2})&""
数组公式一次完成!
229字元

本帖子中包含更多资源

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

x

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2010-11-7 12:53 | 显示全部楼层
方法一:
=INDEX((题目!A:D,题目!F:I),CHOOSE({1,1,2,2,1,1},SMALL(IF(ISNA(MATCH(题目!C3:C188&题目!B3:B188&题目!A3:A188,题目!H3:H188&题目!G3:G188&题目!F3:F188,)),ROW(3:188)),ROW(1:8)),MATCH(HLOOKUP("*",题目!C1:C188&"",SMALL(IF(ISNA(MATCH(题目!C3:C188&题目!B3:B188&题目!A3:A188,题目!H3:H188&题目!G3:G188&题目!F3:F188,)),ROW(3:188)),ROW(1:8)),0),题目!H1:H188,)),{3,4,1,2,1,2},{1,1,2,2,1,1})

===

TA的精华主题

TA的得分主题

发表于 2010-11-8 16:30 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
1.添加辅助列依8月人员求出对应7月的部门和职位
2.在用7、8月做比对,求出调动人员
3.求出结果
若不符合要求,请帮忙告知,以便再改进!谢谢!

本帖子中包含更多资源

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

x

TA的精华主题

TA的得分主题

发表于 2010-11-8 20:56 | 显示全部楼层
请先生指教!

本帖子中包含更多资源

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

x

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2010-11-11 09:33 | 显示全部楼层
很简单的一道题,却让精减难住了几个字符。
先占个位置再想想有没办法精减吧
IF(OR(COLUMN()={4,5}),LOOKUP(2,1/($B4=题目!$H$3:$H189),题目!D$3:D189),OFFSET(题目!$A$1,SMALL(IF(ISNA(MATCH(题目!$A$3:$A188&题目!$B$3:$B188&题目!$C$3:$C188,题目!$F$3:$F188&题目!$G$3:$G188&题目!$H$3:$H188,)),ROW($2:187),188),$A4),MOD(COLUMN(),6)))&""

如果不处理错误返回
IF(OR(COLUMN()={4,5}),INDEX(题目!D:D,MATCH($B4,题目!$H:$H,)),OFFSET(题目!$A$1,SMALL(IF(ISNA(MATCH(题目!$A$3:$A188&题目!$B$3:$B188&题目!$C$3:$C188,题目!$F$3:$F188&题目!$G$3:$G188&题目!$H$3:$H188,)),ROW($2:187),188),ROW(A1)),MOD(COLUMN(),6)))

[ 本帖最后由 dengyf 于 2010-11-13 09:32 编辑 ]

TA的精华主题

TA的得分主题

发表于 2010-11-16 10:35 | 显示全部楼层
做来做去只能做成这样,字符多了一个,还不能容错。

本帖子中包含更多资源

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

x

TA的精华主题

TA的得分主题

发表于 2010-11-16 14:35 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
=INDEX(T(OFFSET(题目!A2,IF({1,1,1,1,0,0},ROW(1:200),MATCH(题目!H3:H202&"",题目!C3:C202&"",)),{7,8,5,6,0,1})),SMALL(IF(T(OFFSET(题目!A2,MATCH(题目!H3:H188,题目!C:C,)-2,{0,1}))<>题目!F3:G188,ROW(1:186),200),ROW(1:25)),COLUMN(A:F))&""
思路:构造员工原情况和现情况的数组,然后用index配合small从中取值。
利用了没有员工原情况的部门工种和现情况都不一样的这个有利条件,不知算不算取巧……

[ 本帖最后由 wcymiss 于 2010-11-16 14:58 编辑 ]

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2010-11-18 09:32 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
=OFFSET(题目!A2,--MID(TEXT(SMALL(IF(--(题目!A3:B188=T(OFFSET(题目!F2,MATCH(题目!C3:C188,题目!H3:H188,),{0,1})))={1,1},999.9,ROW(1:186))+MATCH(题目!C3:C188,题目!H3:H188,)%%,ROW(1:186)),"0000.0000"),{1,1,6,6,1,1},4),{2,3,5,6,0,1})&""

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2010-11-18 10:55 | 显示全部楼层
=OFFSET(题目!A2,LEFT(RIGHT(900&SMALL(IF(PHONETIC(OFFSET(题目!A2,MATCH(题目!H3:H188,题目!C3:C188,),,,4))=PHONETIC(OFFSET(题目!F2,ROW(1:186),,,4)),999,ROW(1:186)+MATCH(题目!H3:H188,题目!C3:C188,)/10^3),ROW()-3),{7,7,7,7,3,3}),3),{7,8,5,6,0,1})&""
选中B4:G28,输入多单元格数组公式,刚好230字符,汗!

思路:首先利用MATCH函数找到调动前的人员在调动后的位置,再用OFFSET得到一个和调动后顺序一样的,利用PHONETIC分别对调动前后的每个人资料合并,进而判断每个人调动前后是否一致,如果一致,取999,如果不一致,取调动后的排序号加调动前的排序号除1000(因题目中最大排序号为三位数),对所取得的数字进行排序,然后以小数点为界,利用RIGHT和LEFT取两边的数字,考虑到调动后的序号有可能为一位或者两位(但至少有一位)且999无法正常取到,故在排序后的数字前加900(此处处理有瑕疵,当源数据多于900条时会出错),最后用OFFSET实现取数,完成!
PS:判断部分最初是用OFFSET及MMULT实现的
=MMULT(N(T(OFFSET(题目!A2,MATCH(题目!H3:H188,题目!C3:C188,),{0,1,2,3}))<>题目!F3:I188),{1;1;1;1})
字符虽少,但最后嵌套超限,无奈只得出此下策。

=========================================================================================

稍做修改
=OFFSET(题目!A2,MID(SMALL(IF(PHONETIC(OFFSET(题目!A2,MATCH(题目!H3:H188,题目!C3:C188,),,,4))=PHONETIC(OFFSET(题目!F2,ROW(1:186),,,4)),1-10^-6,ROW(1:186)/10^3+MATCH(题目!H3:H188,题目!C3:C188,)/10^6),ROW()-3),{3,3,3,3,6,6},3),{7,8,5,6,0,1})&""
比之前节省三个字符,现227字符,且节省一层嵌套,解决了源数据大于900可能出错的情况,拓展性更强,如果源数据达到四位,只需要将幂数改为4/8,MID的参数修改为4/8即可,字符数不受影响,但仍然不完善,即可能出现“甲”、“乙丙”合并得到“甲乙丙”,与“甲乙”、“丙”合并同样得到“甲乙丙”,导致判断出错的情况,利用MMULT可避免出现此情况,但仍然嵌套超限,努力改进,加油!

=========================================================================================

再改
=OFFSET(题目!A2,MID(SMALL(IF(PHONETIC(OFFSET(题目!A2,MATCH(题目!H3:H188,题目!C3:C188,),,,4))=PHONETIC(OFFSET(题目!F2,ROW(1:186),,,4)),1-1%%%%,ROW(1:186)%%+MATCH(题目!H3:H188,题目!C3:C188,)%%%%),ROW()-3),{3,3,3,3,7,7},4),{7,8,5,6,0,1})&""
以%代替幂的表示方式,公式减少到223字符,再努力!

========================================================================================

用MMULT实现判断,逻辑上更严密
=OFFSET(题目!A2,MID(SMALL(MMULT(N(T(OFFSET(题目!A2,MATCH(题目!H3:H188,题目!C3:C188,),{0,1,2,3}))<>题目!F3:I188),{1;1;1;1})*(ROW(1:186)%%+MATCH(题目!H3:H188,题目!C3:C188,)%%%%-1+1%%%%)+1-1%%%%,ROW()-3),{3,3,3,3,7,7},4),{7,8,5,6,0,1})&""
221字符(此公式结果正确,但仅提供思路参考,有待确认,因有可能产生浮点误差)

[ 本帖最后由 sunbin200388 于 2010-11-22 16:12 编辑 ]

评分

1

查看全部评分

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

本版积分规则

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

GMT+8, 2024-3-29 19:44 , Processed in 0.058788 second(s), 16 queries , Gzip On, Redis On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

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

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

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