エクセル関数で参照する表を条件により可変する

質問

お世話になります。
エクセルで有給管理表を作っており、関数で参照する祝日の表の列を年により変えたいのですが、どうしても解決できずに相談します。

祝日シートを作り、a1に2019/1/1、b1に2020/1/1、a2:a30に2019年の祝日、b2:b30に2020年の祝日を入力しており、c列以降に来年以降の祝日を入力していく予定です。
複数の職員シートのa4に2019/1/1、b4に2019/2/1などと横に一年分入力していますが、a4の日付は入職日によって年月がバラバラです。

具体的にはa3セルに
(NETWORKDAYS.INTL(a4,EOMONTH(a4,0),"0111110",祝日シート!$a2:$a30))
と入力していますが、最後の「祝日シート!$a2:$a30」の部分を年が変わると自動でひとつ右の列に移動できないかと思っています。
offsetやmatch関数を使ってみたのですが、任意の列を返す事が出来ず相談させてください。
よろしくお願いします。

回答No.1

kkkkkm · ベストアンサー

最後の範囲の指定を以下のようにするとできると思います。
OFFSET(祝日シート!$A2,0,1,30,1)
でA2,0,1の1を基準の年の列からの差を計算して出してください。

お礼

私の能力不足で全面解決はできませんでしたが、つたない文章をくみとってこちらの書式に合わせた回答をしていただきありがとうございました。

回答No.5

imogasi

もう一つの方法で、やってみました。
前の回答と同じデータ例でやってます。
月ごとの休日のリストが、列方向にしてます。E列からI列です。
コピーー＞行と列を入れ替えて貼り付けの操作で入れ替えは簡単です。
ーー
月 月初日 月末日 稼働日数 １月 ２月 ３月 ４月 ５月
１月 2020/1/1 2020/1/31 18 2020/1/1 2020/2/23 2020/3/20 2020/4/29 2020/5/3
２月 2020/2/1 2020/2/29 19 2020/1/2 2020/2/24 2020/4/30 2020/5/4
３月 2020/3/1 2020/3/31 21 2020/1/3 2020/4/23 2020/5/6
４月 2020/4/1 2020/4/30 19 2020/1/13
５月 2020/5/1 2020/5/31 19 2020/1/28
ーー
E2の式を　=NETWORKDAYS.INTL(B2,C2,1,INDIRECT("_"&A2))
下方向に式を複写します。
ーー
なぜ　第４引数が　INDIRECT("_"&A2)　なのか？
ーー
E1:I6選択
数式ー定義された名前ー選択は荷から作成
上端行
で範囲に名前定義すると、_1月のように、半角のアンダーバー が付いてしまう。
名前定義の「名前」には、先頭が数字は禁止だったと思うので、アンダーバーが、付いてしまうのだと思う。全角数字（１月の１）でも同じのようです。
それで、それに合わせて、INDIRECT("_"&A2)を思いつきました。

回答No.4

msMike

[No.2補足]へのコメント、

》 土日祝日が休みの人で2020年5月を計算すると、
》 土日祝日が13日あるので31-18で「13」と出ます
小學生でも解ける計算ですよね？その「18」の由來は何？
31-13＝18 じゃないの？

セル A4 が 2020/5/1 のとき、
=NETWORKDAYS.INTL(A4,EOMONTH(A4,0),"0000011",OFFSET(祝日シート!B1,1,,29,))
は 18 を返しますよン
此処で祝日シートの A列は 2019年度としていたので、2020年度はB列。だから上式では B1 を適用してます。
シッカリしてくださいネ！

補足

補足への回答ありがとうございます。
まず、5月の土日祝日を引いた稼働日「18」を出して、月の日数31から引く事で休日「13」を出しました。
>A列は 2019年度としていたので、2020年度はB列。だから上式では B1 を適用
この部分を自動で可変させたいと思っています。
kkkkkmさんの回答での考え方が一番近いのかなと思っています。
頭がクルクル回ってきたので糖質を補給してから再度挑戦します。

回答No.3

imogasi

>質問者の方向と違う回答ですが、「＞エクセル関数で参照する表を条件により可変する」、下記のように表を作れば、普通の関数のやり方になると思います。
ーー
例データ
月 月初日 月末日 稼働日数 休日（祝日・振休＋特別）
１月 2020/1/1 2020/1/31 18 2020/1/1 2020/1/2 2020/1/3 2020/1/13 2020/1/28
２月 2020/2/1 2020/2/29 19 2020/2/23 2020/2/24
３月 2020/3/1 2020/3/31 21 2020/3/20
４月 2020/4/1 2020/4/30 19 2020/4/29 2020/4/30 2020/4/23
５月 2020/5/1 2020/5/31 19 2020/5/3 2020/5/4 2020/5/6
休日（祝日・祝日振休＋特別休日）はE列ーJ列に入力するものとします。
特別休日とは、土日・祝日以外の会社独自の休日がある場合です。
これらは土日に該当する日を指定していても、NETWORKDAYS.INTL関数の結果日数には悪影響はないようです。
上記では、特別休日として入れたのは、2020/1/28、2020/4/30 、2020/4/23で
例として思い付きです。
休日（祝日・祝日振休＋特別休日）は、E列より右列に詰めて入力します。一応式では、J列までとしましたが増やしてもOKと思います。
D2セルに　=NETWORKDAYS.INTL(B2,C2,1,E2:J2)
D３：D6に式を複写します。
結果は稼働日数＝D列です。
＝＝
関数では
・VLOOKUP関数の参照表
・入力規則のリストのデータ
・その他
などで参照（列の場合がが多い）を相対化したい、しなければならないこととがあり、ここの質問にも出ますが、その回答の路線（名前、INDIRECTなど）で本件を考えましたが、短い時間内に完成せず、上記を挙げます。

補足

代替案をありがとうございます。
書式が変えられないのでヒントにしてみます。
以前も他サイトで色々お世話になった事を覚えています。
今回もその節もありがとうございます。

回答No.2

msMike

式中の
祝日シート!$a2:$a30)
の部分を、
OFFSET(祝日シート!A1,1,,29,)
に變更する丈で御之字にならうかと。御試しあれ。

補足

式に誤りがあったと思うので下記の式(該当月の日数-出勤日)に変更してみました。
土日祝日が休みの人で2020年5月を計算すると、土日祝日が13日あるので31-18で「13」と出ますが、教えていただいた式を代入すると「10」と出ます。
もしよろしければ再度お知恵を拝借したいと思います。
よろしくお願い致します。
DAY(EOMONTH(a4,0))-(NETWORKDAYS.INTL(a4,EOMONTH(a4,0),"0000011",祝日シート!$b2:$b30))