Excel精英培训网

 找回密码
 注册
数据透视表40+个常用小技巧,让你一次学会!
查看: 1769|回复: 1

[已解决]根据两列数据判定其列号,并引用其匹配的字符

[复制链接]
发表于 2017-9-3 17:13 | 显示全部楼层 |阅读模式
本帖最后由 好运来5599 于 2017-9-5 21:41 编辑

详见截图和附件,附件中也有详细说明。
最佳答案
2017-9-4 07:17
本帖最后由 cabcyvr 于 2017-9-4 07:36 编辑

以下是慢动作分解
1. 建立两个辅助单元格
R4=MAX(IF(计件!AR$4:AR$1000<>"",ROW(计件!AR$4:AR$1000)))-3   数组执行后复制到 S4

R4为68-3,表示AR列最后一个非空单元格位置为AR68,有效范围为65
R5为14-3,表示AS列最后一个非空单元格位置为AS14,有效范围为11

目的是为了减少B列公式的长度和让你看的清楚一些。


2. B4=IF(A4="","",IF(AND(ROW(A1)<=$R$4,ISNUMBER(MATCH(A4&C4,计件!$AR$4:$AR$1000,))),"司机",IF(AND(ROW(A1)<=$R$4+$S$4,ISNUMBER(MATCH(A4&C4,计件!$AS$4:$AS$1000,))),"跟车1","跟车2")))        普通公式执行,下拉到底

因为 A列的数值肯定是从 AR,AS和 AT提取得来的,所以只需用下拉后的ROW(A1) 数值来判断应该在哪一列去查询“日期/姓名”,
当 B列公式下拉到B69时,参数中ROW(A66)=66,正好超出AR列 的有效范围,所以改到AS列去查找,条件是ROW()序列号必须要小于等于65+11,再超出范围的话(只要不是空)绝对是AT列的数据,不用再浪费时间去设第三个条件去判断。

或者
如果想在B列一个公式得到结果,把R4和S4的数组公式替代现在公式中的R4 和 S4 即可,那样的话B列的公式就又成为了数组。。。。

=IF(A4="","",IF(AND(ROW(A1)<=MAX(IF(计件!AR$4:AR$1000<>"",ROW(计件!AR$4:AR$1000)))-3,ISNUMBER(MATCH(A4&C4,计件!$AR$4:$AR$1000,))),"司机",IF(AND(ROW(A1)<=MAX(IF(计件!AR$4:AR$1000<>"",ROW(计件!AR$4:AR$1000)))-3+MAX(IF(计件!AS$4:AS$1000<>"",ROW(计件!AS$4:AS$1000)))-3,ISNUMBER(MATCH(A4&C4,计件!$AS$4:$AS$1000,))),"跟车1","跟车2")))数组

或者 (此公式不在附件内)
=IF(A4="","",IF(ROW(A1)<=MAX(IF(计件!AR$4:AR$1000<>"",ROW(计件!AR$4:AR$1000)))-3,"司机",IF(ROW(A1)<=MAX(IF(计件!AR$4:AR$1000<>"",ROW(计件!AR$4:AR$1000)))-3+MAX(IF(计件!AS$4:AS$1000<>"",ROW(计件!AS$4:AS$1000)))-3,"跟车1","跟车2")))
数组
如前所述,A列数据肯定是从AR/AS/AT列得到,直接用公式中的累计ROW(A1)数值来判断是哪一个职位,此时不需要去判断AC列组合是否在三列中的哪一列存在。

B列的提取方法.png
计件表.png

根据两列数据判定其列号,并引用其匹配的字符.rar

39.35 KB, 下载次数: 2

excel精英培训的微信平台,每天都会发送excel学习教程和资料。扫一扫明天就可以收到新教程
发表于 2017-9-4 07:17 | 显示全部楼层    本楼为最佳答案   
本帖最后由 cabcyvr 于 2017-9-4 07:36 编辑

以下是慢动作分解
1. 建立两个辅助单元格
R4=MAX(IF(计件!AR$4:AR$1000<>"",ROW(计件!AR$4:AR$1000)))-3   数组执行后复制到 S4

R4为68-3,表示AR列最后一个非空单元格位置为AR68,有效范围为65
R5为14-3,表示AS列最后一个非空单元格位置为AS14,有效范围为11

目的是为了减少B列公式的长度和让你看的清楚一些。


2. B4=IF(A4="","",IF(AND(ROW(A1)<=$R$4,ISNUMBER(MATCH(A4&C4,计件!$AR$4:$AR$1000,))),"司机",IF(AND(ROW(A1)<=$R$4+$S$4,ISNUMBER(MATCH(A4&C4,计件!$AS$4:$AS$1000,))),"跟车1","跟车2")))        普通公式执行,下拉到底

因为 A列的数值肯定是从 AR,AS和 AT提取得来的,所以只需用下拉后的ROW(A1) 数值来判断应该在哪一列去查询“日期/姓名”,
当 B列公式下拉到B69时,参数中ROW(A66)=66,正好超出AR列 的有效范围,所以改到AS列去查找,条件是ROW()序列号必须要小于等于65+11,再超出范围的话(只要不是空)绝对是AT列的数据,不用再浪费时间去设第三个条件去判断。

或者
如果想在B列一个公式得到结果,把R4和S4的数组公式替代现在公式中的R4 和 S4 即可,那样的话B列的公式就又成为了数组。。。。

=IF(A4="","",IF(AND(ROW(A1)<=MAX(IF(计件!AR$4:AR$1000<>"",ROW(计件!AR$4:AR$1000)))-3,ISNUMBER(MATCH(A4&C4,计件!$AR$4:$AR$1000,))),"司机",IF(AND(ROW(A1)<=MAX(IF(计件!AR$4:AR$1000<>"",ROW(计件!AR$4:AR$1000)))-3+MAX(IF(计件!AS$4:AS$1000<>"",ROW(计件!AS$4:AS$1000)))-3,ISNUMBER(MATCH(A4&C4,计件!$AS$4:$AS$1000,))),"跟车1","跟车2")))数组

或者 (此公式不在附件内)
=IF(A4="","",IF(ROW(A1)<=MAX(IF(计件!AR$4:AR$1000<>"",ROW(计件!AR$4:AR$1000)))-3,"司机",IF(ROW(A1)<=MAX(IF(计件!AR$4:AR$1000<>"",ROW(计件!AR$4:AR$1000)))-3+MAX(IF(计件!AS$4:AS$1000<>"",ROW(计件!AS$4:AS$1000)))-3,"跟车1","跟车2")))
数组
如前所述,A列数据肯定是从AR/AS/AT列得到,直接用公式中的累计ROW(A1)数值来判断是哪一个职位,此时不需要去判断AC列组合是否在三列中的哪一列存在。

根据两列数据判定其列号,并引用其匹配的字符.rar

49.44 KB, 下载次数: 4

回复

使用道具 举报

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

本版积分规则

小黑屋|手机版|Archiver|Excel精英培训 ( 豫ICP备11015029号 )

GMT+8, 2024-3-29 21:50 , Processed in 0.548776 second(s), 10 queries , Gzip On, Yac On.

Powered by Discuz! X3.4

Copyright © 2001-2020, Tencent Cloud.

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