查看: 105|回复: 5

[电脑] excel,某行开始向下累加到固定值后返回该最后行对应的另一个单元格信息

[复制链接]

2万

主题

2万

帖子

7万

积分

论坛元老

Rank: 8Rank: 8

积分
79306
发表于 2019-7-22 09:41:08 | 显示全部楼层 |阅读模式



需要计算某日的次日开始,第5个工作日的日期。因为需要跳过双休、节假日,我把对应工作日的对应数值都计为1,双休日、节假日的数值都设为0。例如:接报日期为“2018/12/13”,需要从2018/12/14开始计算累加的工作日,12/14、12/17、12/18、12/19、12/20对应数值都是1,12/15、12/16是双休日,对应数值为0,所以累加12/20才到5,则接报日期“2018/12/13”旁的“第五个工作日”单元格内容需能显示“2018/12/20”。请问下怎么写公式实现?如下图

本帖子中包含更多资源

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

x
回复

使用道具 举报

0

主题

2万

帖子

6836

积分

论坛元老

Rank: 8Rank: 8

积分
6836
发表于 2019-7-22 09:41:58 | 显示全部楼层
如果没有重复,用VLOOKUP函数解决。在第一个表格中需要的单元格输入公式=vlookup(sheet2!查找值所在单元格,第一个表格中查找值应在的列开始到另一个值所在列为止的单元格范围,另一个值所在列的数字序号,0)
回复 支持 反对

使用道具 举报

0

主题

2万

帖子

6836

积分

论坛元老

Rank: 8Rank: 8

积分
6836
发表于 2019-7-22 09:42:31 | 显示全部楼层
=WORKDAY(A2,5)
WORKDAY返回在某日期(起始日期)之前或之后、与该日期相隔指定工作日的某一日期的日期值。 工作日不包括周末和专门指定的假日。 在计算发票到期日、预期交货时间或工作天数时,可以使用函数 WORKDAY 来扣除周末或假日。
提示: 若要通过使用参数来指示哪些天是周末以及有多少天是周末来计算指定工作日天数之前或之后日期的序列号,请使用 WORKDAY.INTL 函数。
语法
WORKDAY(start_date, days, [holidays])
WORKDAY 函数语法具有下列参数:
Start_date    必需。 一个代表开始日期的日期。
Days    必需。 start_date 之前或之后不含周末及节假日的天数。 Days 为正值将生成未来日期;为负值生成过去日期。
Holidays    可选。一个可选列表,其中包含需要从工作日历中排除的一个或多个日期,例如各种省/市/自治区和国家/地区的法定假日及非法定假日。该列表可以是包含日期的单元格区域,也可以是由代表日期的序列号所构成的数组常量。
重要: 应使用 DATE 函数输入日期,或者将日期作为其他公式或函数的结果输入。 例如,使用函数 DATE(2008,5,23) 输入 2008 年 5 月 23 日。 如果日期以文本形式输入,则会出现问题。
备注
Microsoft Excel 可将日期存储为可用于计算的序列号。默认情况下,1900 年 1 月 1 日的序列号是 1,而 2008 年 1 月 1 日的序列号是 39448,这是因为它距 1900 年 1 月 1 日有 39448 天。
如果任何参数为无效日期值,则函数 WORKDAY 将返回 错误值 #VALUE!。
如果 start_date 加 days 产生非法日期值,函数 WORKDAY 返回 错误值 #NUM!。
如果 days 不是整数,将截尾取整。
回复 支持 反对

使用道具 举报

0

主题

2万

帖子

6836

积分

论坛元老

Rank: 8Rank: 8

积分
6836
发表于 2019-7-22 09:42:56 | 显示全部楼层
:=vlookup(c2,a:b,2,0) 注意日期格式一定要一致!
回复 支持 反对

使用道具 举报

0

主题

2万

帖子

6836

积分

论坛元老

Rank: 8Rank: 8

积分
6836
发表于 2019-7-22 09:43:53 | 显示全部楼层
上午的回答中,公式中A2单元格没固定,且漏掉了一个假日,下午修正下。
只要计算A2单元格日期开始多少个工作日后的日期,A列无需列出日期序列,直接用Workday函数即可,如果有双休日要上一天班的或休息日是星期一到星期五中的,就用Workday.intl。
如图,在B列列出一年的法定假日,如果法定假日与双休日重叠的,就把兑换休息的日期填上。
C1、D1、E1中只写数字,通过单元格格式来显示汉字,自定义格式:"第"#"个工作日"。
C2=WORKDAY($A2,C$1,$B2:$B12)
右拉
修改A2中的日期,得到新的数据
[/url]
解决实际问题,推荐上面用上面的公式,简单快捷。但按题主的提问思路,需要的公式倒是很有趣的问题,所以下面按题主的思路来编写公式。当然B列的标示应该也用公式来得到,否则一个个日期来标示0和1也是挺麻烦的。同样在C列中把全年的假日列出来,遇上假日与双休日重叠的,就修改为换休日期(2019年的所有法定假日没有与双休日重叠的,无需替换)。
B2=IF(OR(WEEKDAY(A2,2)>5,COUNTIF(C$2:C$12,A2)),0,1)
下拉,得到A列日期是否为休息日的判断,工作日为1,休息日为0

D2=OFFSET($A2,MATCH(D$1,SUBTOTAL(9,OFFSET($B2,1,,ROW(1:39))),),)
同时按Ctrl+Shift+Enter三个键,完成数组公式的输入,右拉。
公式中的39为A列日期数据行数-2
[url=https://gss0.baidu.com/-vo3dSag_xI4khGko9WTAnF6hhy/zhidao/pic/item/0b7b02087bf40ad187d9c30d592c11dfa9ecce1b.jpg]

本帖子中包含更多资源

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

x
回复 支持 反对

使用道具 举报

0

主题

2万

帖子

6836

积分

论坛元老

Rank: 8Rank: 8

积分
6836
发表于 2019-7-22 09:44:33 | 显示全部楼层
这种问题可用专门的workday函数解决,而且无需B列辅助列,建立除周六周日外的其它节假日列表区域,并定义名称(如定义为:JJR),=workday($A2,5,JJR)
回复 支持 反对

使用道具 举报

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

本版积分规则

QQ|手机版|Archiver|我要投稿|洛阳IT论坛 ( 豫ICP备18021590号 )|网站地图

GMT+8, 2019-12-6 01:00 , Processed in 0.104134 second(s), 31 queries .

Powered by 洛阳IT论坛网 X3.2

© 2018-2020 China Inc.

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