エクセル関数で参照する表を条件により可変する
- エクセルで有給管理表を作っており、関数で参照する祝日の表の列を年により変えたいのですが、どうしても解決できずに相談します。
- 具体的には、祝日シートに過去の祝日と未来の祝日を入力し、関数で今日の祝日を参照していますが、年が変わるたびに更新する手間があります。
- 年が変わった場合でも、関数で祝日の表の列を自動的に変える方法があれば教えていただきたいです。
- ベストアンサー
エクセル関数で参照する表を条件により可変する
お世話になります。 エクセルで有給管理表を作っており、関数で参照する祝日の表の列を年により変えたいのですが、どうしても解決できずに相談します。 祝日シートを作り、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関数を使ってみたのですが、任意の列を返す事が出来ず相談させてください。 よろしくお願いします。
- isicorosun
- お礼率80% (70/87)
- Excel（エクセル）
- 回答数5
- ありがとう数1
- みんなの回答 （5）
- 専門家の回答
質問者が選んだベストアンサー
最後の範囲の指定を以下のようにするとできると思います。 OFFSET(祝日シート!$A2,0,1,30,1) でA2,0,1の1を基準の年の列からの差を計算して出してください。
その他の回答 (4)
- imogasi
- ベストアンサー率27% (4737/17068)
もう一つの方法で、やってみました。 前の回答と同じデータ例でやってます。 月ごとの休日のリストが、列方向にしてます。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)を思いつきました。
- msMike
- ベストアンサー率20% (363/1775)
[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さんの回答での考え方が一番近いのかなと思っています。 頭がクルクル回ってきたので糖質を補給してから再度挑戦します。
- imogasi
- ベストアンサー率27% (4737/17068)
>質問者の方向と違う回答ですが、「＞エクセル関数で参照する表を条件により可変する」、下記のように表を作れば、普通の関数のやり方になると思います。 ーー 例データ 月 月初日 月末日 稼働日数 休日（祝日・振休＋特別） １月 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など）で本件を考えましたが、短い時間内に完成せず、上記を挙げます。
補足
代替案をありがとうございます。 書式が変えられないのでヒントにしてみます。 以前も他サイトで色々お世話になった事を覚えています。 今回もその節もありがとうございます。
- msMike
- ベストアンサー率20% (363/1775)
式中の 祝日シート!$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))
関連するQ&A
- Excel 参照行の削除でも可変しない関数
Excel 参照行の削除でも可変しない関数 同じ形式で、数シートにわたり入力された文字をまとめた表を別のシート(シート１)に作成しています。 シート2以降の参照元データーは一定のルールで入力されていますが、削除や追加が頻繁に行われます。削除や追加の度にシート1Ｂ列・Ｃ列の関数が参照する行数が追随して可変しないようにしたいのですが可能でしょうか。 具体的には、シート2において6行目から9行目を削除した際に、シート1では「B2＝みかん」／「C2＝イチゴ」と繰り上がり、B行・C行は常に指定した行を参照するようにしたいと思っています。 ＜シート1＞ Ａ Ｂ Ｃ 1 タイトル りんご ばなな （← 3行目） 2 タイトル かき なし （← 8行目） 3 タイトル みかん イチゴ （←13行目） ・B列:「=IF(ISERROR('Sheet2'!$B3:$B3),"",'Sheet2'!$B3:$B3)」 ・C列:「=IF(ISERROR('Sheet2'!$D3:$D3),"",'Sheet2'!$D3:$D3)」 ＜シート2、3…＞ 参照したいセルは、Ｂ列とＤ列の3行目、8行目、13行目…と一定の間隔で入力されており、全てのシートにおいて同じ箇所に入力されています。 シートごとにカテゴリーの個数は(図A1:E4までを１カテゴリーと考えています)異なり、作業の度にカテゴリーの追加や削除が頻繁にあります。 うまく説明できているか不安ですが、良い方法を教えてください。
- ベストアンサー
- オフィス系ソフト
- エクセルでこんな関数は？
A2:B30に表１があります。 使用者がこのシートに入力するさまざまな条件により、A列がTRUEかFALSEになります。 表１ではA列がTRUEのとき、B列に文字列が表示されるようにB列に式が入れてあります。 A列がすべてTRUEになることはありません。したがってB列の文字列の表示は飛び飛びというか歯抜けのような表になっています。 このB列に表示された文字列を、表２（別シート）に上から歯抜けが無いように２行目以降に順番で表示させたいのです。 考えたのはどこかのセルに =B2&B3&B4&～略～&B30 として文字列をまとめ、（文字列の区切りがわかるように、それぞれの文字列の頭に※印をつけておく）、さらにこれを※印を目標に分割する関数はないか？ということなのですがわかりません。 あるいは他の方法でもかまいません。ただし、表１をおもてに出したくないので表１をソートするやりかたは使えません。 どうかお教えください。お願いします。
- ベストアンサー
- オフィス系ソフト
- エクセル関数
エクセル関数を教えてください。 下記のシフト表のようなものを作成しているのですが、 【Sheet1】 Ａ列 Ｂ列 Ｃ列 Ｄ列 ・・・・ 1 氏名 4/8 4/9 4/10・・・・ 2 勤務者名 ● △ ■ Ｂ列以降は連続した日付で 2行目は出勤日に記号を入力します。 記号は仕事種類によって違うので、種類はいろいろあります。 ちなみに、【Sheet1】のひな形は変更不可です。 この表を元に【Sheet2】に勤務者名を入力すると、 最初に出勤した日と、最後に出勤した日を表示させたいのです。 ＩＦやVLOOK関数を使うのかな？と思いいろいろやってみたのですが、 うまくできませんでした。。。 ご協力よろしくお願いします。
- ベストアンサー
- その他MS Office製品
- エクセルのVLOOKUP関数で…（複数条件？の抽出）
●シート２、A列に部品正式名称、B列に部品略称の一覧表（部品の種類は約５００点） ●シート３、A列に略称、B,C,D,E,F,G列と続けて寸法などの詳細を記した一覧表 があります。 ●シート１に検索一覧表として、Ｂ列（Ｂ３～Ｂ８）は項目、セルＣ３～Ｃ８にVLOOKUPでシート３の情報が抽出されるようにしてあります。 Ｃ１で略称を入力し抽出するのはOKなのですが、正式名称で入力しても同じようにシート３の情報を抽出するようにしたいです。 （IFを使って思いつく関数を組合わせてみたりしたのですが、うまくいきませんでした(TT) できたら関数で何か良い方法ありますでしょうか？ 宜しくお願いします。
- ベストアンサー
- オフィス系ソフト
- エクセルでSheet1の表を参照して条件に合った値を返したい
エクセルでSheet1に A B C 1 1月1日 10時 10 2 1月1日 12時 25 3 1月1日 14時 17 4 1月2日 10時 15 5 1月2日 12時 23 6 1月2日 14時 19 ・ ・ ・ というような表があります。 Sheet2に A B C D 1 日付 10時 12時 14時 2 1月1日 3 1月2日 ・ ・ ・ の表があり、Sheet2のB2からD3にSheet1の表を参照して 条件に合うC列の値を返したいのです。 （例えばSheet2のB2には、1月1日の10時なのでSheet1C列の10が入る） VLOOKUP関数等を使って試してみましたがうまくいきません。 ご教授お願い致します。
- ベストアンサー
- オフィス系ソフト
- EXCEL LOOKUP関数で別シートをセルから参照したい
シート：集計表・4月・5月・6月・・・・3月 （4月～3月までは売上表が入っていて、表の形式はまったく同じです。） 集計表のシートにおいて、 A1にシート名を入力することによって、そのシートを使った範囲を取得したいと思ってます。 B2に =vlookup(B1,'A1'!A:B,2,0) この場合、'A1'! は有効しませんが、何かの関数を使ってこれと同じ結果を得たいと考えています。 いろいろ検索しましたが、(検索の仕方も悪いかもしれません。) VLOOKUP関数を使って複数列を範囲としたいので、よく出てくるINDIRECT関数はセル番地を指定する以上、無理だと判断しました・・・。 よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- EXCEL関数の質問です。A列に2010/10/1という日付が並んだ表
EXCEL関数の質問です。A列に2010/10/1という日付が並んだ表。土日と祝日の日はB列に「休」と返す関数を作成したいです。 土日だけ「休」と返す関数は作成できますが、祝日を追加できません。 =IF(OR(WEEKDAY(A1,1)=7,WEEKDAY(A1,1)=1),"休","") 今このように作成しています。恐らく祝日の一覧を別シートに設けないといけないと思うのですが・・・ ご助力お願いします
- ベストアンサー
- その他MS Office製品
- エクセルで、2つのセル値（2つの条件）から、別シートの表から該当する値を参照する方法
シート(1)のA列とB列のセルに入力されている値を元に、 シート(2)の表を参照し、該当する値をシート(1)のC列に 表示させる方法があれば 教えて頂けると助かります。 なおシート(1)のA列は、参照するシート(2)の表の列タイトルに対応し、 シート(1)のB列は、シート(2)の行タイトルに対応するマトリックス表に なっております。 また、この列行タイトルの表記は「１から」または「１から９まで」 という範囲での表示になっています。 （例:シート(1)元データ） No| A列 | B列 | C列 | --------------------------------- 1 | 07 | 22 | 20 | ←C列は、シート(2)表を参照 2 | 18 | 15 | 30 | 3 | 01 | 09 | 05 | 4 | 21 | 03 | 30 | 5 | 30 | 28 | 35 | （例:シート(2)参照する表） A列＼B列 | 1～9| 10～19 | 20～29 | ----------------------------------- 01～ | 05 | 10 | 20 | 05～ | 15 | 15 | 20 | 10～ | 20 | 25 | 30 | 15～ | 25 | 30 | 30 | 20～ | 30 | 35 | 35 | 現在、手作業でC列への入力を行っております。 何とか、作業効率を上げたいと思っておりますので、 ご指導下さいます様 宜しくお願い致します。
- ベストアンサー
- その他MS Office製品
- エクセルの表引き
こんにちは いつもお世話になっています。 Sheet1に以下の表があります。 すみません、表示がうまくいかず、A列には文字列、B列には数字が入ります。 A列に数字があるように表示されていましたらB列に表示されているものとしてみてください。 Sheet1 A B 和歌山 2 愛知 3 東京 1 大阪 2 栃木 1 石川 3 A列のデータに重複はなく、B列のデータには重複があります。 B列の数字を検索値として振り分けるような形でA列の値を表引きしたいです。 表引き先は Sheet2のB列の複数セルに1が入力済みの状態。 Sheet3のB列の複数セルに2が入力済みの状態。 Sheet4のB列の複数セルに3が入力済みの状態。 例えば Sheet2 A B 1 1 1 1 1 Sheet3 A B 2 2 2 2 2 Sheet4 A B 3 3 3 3 3 以上の各シートが以下のようになるようにしたいです。 Sheet2 A B 東京 1 栃木 1 1 1 1 Sheet3 A B 和歌山 2 大阪 2 2 2 2 Sheet4 A B 愛知 3 石川 3 3 3 3 Sheet2-4のA列に各B列のデータに一致する値をSheet1から表引きしたいです。例えばSheet2に関して、東京、栃木と出てしまったら後のA列は空欄になるようにしたいです。A列数字が表示されていたらB列にあるものとしてください。 フィルタとかでの手作業でなく、何とか関数で持ってこられないでしょうか。 作業列とか必要でしたら教えてください。 わかりにくい表現ですみません。 よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- 違うシートの対応表を使った関数（？）
入力の仕事をしていますが、入っていた関数を消してしまったので教えてください。 年齢を入力すると、対応する数字が次のセルに入るようになっていました。 年齢の対応表は次のシートに入っています。 ＩＦ関数ではなく、これを使った関数が入っていたようなのですが、わからなくて困っています。 対応表は、２０歳以下は１、２１歳から２５歳は２、２６歳から３０歳は３、となるので、入力をしている次のシートの、Ａ列に１から３０までの数字が、Ｂ列に１から３までの数字が対応する年齢毎に入っています。（実際は８５歳まで、４，５歳きざみに対応数字があります） どこにどんな関数を入れればいいか、教えていただけたら助かります。 すみませんが、よろしくお願いいたします。
- ベストアンサー
- オフィス系ソフト
お礼
私の能力不足で全面解決はできませんでしたが、つたない文章をくみとってこちらの書式に合わせた回答をしていただきありがとうございました。