简化网络日功能，包含几个IF选项 [英] Simplify Networkdays Function, contains several IF options

问题描述

我经常收到电子表格，我需要计算2列日期之间的网络日。

I regularly receive spreadsheets where I need to calculate the Networkdays between 2 columns of dates.

假日日期范围也需要包含在该函数中。我已经存储在单独的电子表格中有10个可能的假期范围（名为Prov1，Prov2等）。

A holiday date range also needs to be included in the function. There are 10 possible holiday ranges (named Prov1, Prov2, etc.) that I have stored in a separate spreadsheet.

下面的公式选择每个省份适当的假日日期范围，并且正在工作。但是我需要把它扩大到10个省份，所以这将变得有点麻烦。

The formula below selects the appropriate holiday date range per the province and is working. But I will need to expand it to cover 10 provinces so it will become a bit cumbersome.

   =IF(M18="Prov1",NETWORKDAYS(M21,N21,Holidays.xls!Prov1),_
    IF(M18="Prov2",NETWORKDAYS(M21,N21,Holidays.xls!Prov2)))

有没有办法简化这个公式？或者一个更好的方法？

Is there a way to simplify this formula? Or a better approach?

推荐答案

如果你不介意使用volatile公式，那么INDIRECT函数就足够了。 >

If you do not mind using a volatile formula, then the INDIRECT function should suffice.

=NETWORKDAYS(M21,N21, INDIRECT("Holidays.xls!" & M18))

这篇关于简化网络日功能，包含几个IF选项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！