【Excel2010】列と行の交わるセルの値

質問

Excel2010に関しての質問になります。

シフト表を作成しているのですが、上段にある表を別シートで下段の様な表の形で7行目や9行目にシフト時間を表示させたいと考えています。（理由は個人に配布するため）
単純にシフト時間を表示することなら出来ましたが、翌月以降は日付と曜日が当然の様に異なるので毎回数式を組み直す必要が出てしまうので、日付と名前から検索してシフト時間を引っ張ってきたいのですが、どのような数式にすれば可能でしょうか？

回答No.3

bunjii · ベストアンサー

>日付と名前から検索してシフト時間を引っ張ってきたいのですが、どのような数式にすれば可能でしょうか？
VLOOKUP関数とMATCH関数で氏名の行と日付の列の交点の値を抽出できます。
エラーの対策にはIFERROR関数が使えますので効果的な使い方で対応します。
尚、氏名と日付の交点が未入力の場合は数値の0が戻りますので、＆演算子で""（空欄）を連結すると良いでしょう。
Sheet2!A7=IFERROR(VLOOKUP($D$1,Sheet1!$F$4:$AO$9,MATCH(A6,Sheet1!$F$2:$AO$2),FALSE)&"","")
貼付画像はExcel 2013で検証した結果ですが、Excel 2010でも再現できるはずです。
Sheet2（カレンダー）の作り方については質問の対象外と解釈しました。

回答No.2

kagakusuki

両シートとも何というシート名なのか説明がありませんので、取り敢えず仮の話として上段の表があるシートがSheet1、下段の表があるシートがSheet2であるものとします。
　それと、

>翌月以降は日付と曜日が当然の様に異なるので毎回数式を組み直す必要が出てしまうので、日付と名前から検索してシフト時間を引っ張ってきたいのですが

との事ですが、同じ日付の日であっても年によって曜日は変わって来ますので、「11月シフト表」というだけでは、何年の11月であるのかが判らないため、その月の何日が何曜日であるのかが定まりません。
　ですから、Sheet1の適当なセルに年月を指定するための日付データを入力する様にして下さい。
　具体的にはまず、Sheet1のK1セルの書式設定を[ユーザー定義]の

yyyy"年"m"月"

か又は

gge"年"m"月"

としておいた上で、Sheet1のK1セルの右隣のセルに「シフト表」と入力して下さい。
　そして、Sheet1のK1セルに年月を入力する際には、例えば

2015/11

や

2015年11月

といった形式で年月のデータを入力して下さい。

　次に、Sheet1のK2セルに次の関数を入力して下さい。

=IF(ISERROR(1/DAY($K$1)),"",IFERROR((TEXT($K$1,"yyyy/m/")&COLUMNS($K:K))+0,""))

　次に、Sheet1のK2セルの書式設定を[ユーザー定義]の

d"日"

に設定して下さい。
　次に、Sheet1のK3セルに次の関数を入力して下さい。

=IF(K$2="","",TEXT(K$2,"aaa"))

　次に、Sheet1のK3セルの書式設定を[ユーザー定義]の

aaa

に設定して下さい。
　次に、Sheet1のK2～K3のセル範囲をコピーして、Sheet1のL2～AQ3のセル範囲に貼り付けて下さい。

　次に、Sheet2のA1セルに次の関数を入力して下さい。

=IF(ISERROR(1/DAY(Sheet1!$K$1)),"(年月未入力)",TEXT(Sheet1!$K$1,"yyyy年m月シフト表"))

　次に、Sheet2のA1セルの書式設定を[ユーザー定義]の

yyyy"年"m"月シフト表"

か又は

gge"年"m"月シフト表"

に設定して下さい。
　次に、Sheet2のB3セルに次の関数を入力して下さい。

=IFERROR(COUNTIF(INDEX(Sheet1!$K:$AO,MATCH($D$1,Sheet1!$F:$F,0),),"*?-*?"),"")

　次に、Sheet2のB3セルの書式設定を[ユーザー定義]の

d"日"

に設定して下さい。
　次に、Sheet2のA6セルに次の関数を入力して下さい。

=IF(ISERROR(1/DAY(Sheet1!$K$1)),"",IFERROR((TEXT(Sheet1!$K$1,"yyyy/m/")&(ROW()-ROW($A$6))/2*7+COLUMNS($A$6:A6)-WEEKDAY(Sheet1!$K$1)+1)+0,""))

　次に、Sheet2のA6セルの書式設定を[ユーザー定義]の

d"日"

に設定して下さい。
　次に、Sheet2のA7セルに次の関数を入力して下さい。

=IF(OR(A6="",$D$1=""),"",IF(COUNTIF(Sheet1!$F:$F,$D$1),INDEX(Sheet1!$K:$AO,MATCH($D$1,Sheet1!$F:$F,0),MATCH(A6,Sheet1!$K$2:$AO$2,0))&"",""))

　次に、Sheet2のA6～A7のセル範囲をコピーして、Sheet2のA6～G17のセル範囲に貼り付けて下さい。

　尚、Sheet2のE3セルの勤務時間数を表示させるための関数に関しては、1回目の休憩時間が何時から何時までで、2回目の休憩時間が何時から何時までで、3日回目の・・・と言う具合に、各々の休憩時間が何時から何時までなのかという事に関して質問者様から何の御説明も無いため、情報不足で関数を組む事が出来ません。

回答No.1

keithin

＞日付と名前から検索してシフト時間を引っ張ってきたい

名前はさておき、「日付」が各セルに具体的にどんな内容で記入してあるのか「正確に」情報提供しないと、関数だけ聞いてもしょーがありませんよ。

例えば）
上の表の「1日」は「文字通り半角数字の1と日の２文字で記入してある」とする
下の表の「1」は、関数を使って日付のないセルは空白("")が、日付のあるセルは半角数字の1,2,3が計算してあるとする
上の表のシート名はSheet1であるとする

下の表のＡ７：
=IF(A6="","",VLOOKUP($D$1,Sheet1!$F:$AO,MATCH(A6&"日",Sheet1!$F$2:$AO$2,0),FALSE))
右にコピー
下にコピー

あぁ、それとも下の表の１，２，３…も、手で入れてるんですかね。
例えば下記などを参考に。
http://www.eurus.dti.ne.jp/~yoneyama/Excel/jituyou/calendar.htm

補足

色々と情報不足で申し訳ありません。

シート名は上段がSheet1、下段はSheet2とします。
Sheet1の日付は=DATE(2015,$K$1,1)で「d"日"」になっています。日付欄の上の黄色部に11(表記は11"月")が入っています。曜日は日付から引っ張ってきてaaaで表記しています。
Sheet2は日付・曜日ともに手入力になっています。

※Sheet1は以前に別の人が作成したため、この様な仕様になっているのか理由は分かりません。