EXCELで作ったカレンダー(縦6マス×横7マス:カレンダーシート)の表の中から、隣のシート(スケジ

Question

詳しい人求む！

EXCELで作ったカレンダー(縦6マス×横7マス:カレンダーシート)の表の中から、隣のシート(スケジ

解決済

質問者：moguko1173
質問日時：2017/02/23 21:09
回答数：7件

1日は固定の場所になく、表の中にランダムに置かれ(大体は1～2行目にはあるが、確実ではないです)そこからカレンダーのような書き方で表があるところまで入力されます。

なるべくシンプルな数式でと思っているのですが、なかなかうまくいきません。

翌月以降も日付を直して使いたいです。
わかりにくくて申し訳ありませんが、もしよい数式があればどうかご教授頂ければと思います。
よろしくお願いいたします。

yuji3690 · Accepted Answer

7列でもいいとのことですので、Aを日曜、Fを土曜とします。
（式を加工すればどの曜日から始まるのであっても修正はできます）
WEEKDAY(日付となるセルなり式）とすれば、その日付の曜日が日＝1～土＝7という数字になります。
前回のものと合わせて具体的な式を書いてみましょう。

※スケジュールシートのB1には1日と書かれていますが、ここにはその月の初めの日付（この場合2017年3月1日）を入力し、書式によって日付のみ表示するようにしてください。
もしくは、どこかに年と月を入力するセルを作り、そのセルによって日付を取得しましょう。
（A1に年、A2に月があるとすれば、B1=DATE(A1,A2,1)とします）
日付でないと曜日が分からないからです。
C1以降はC1=B1+1というように、左のセル+1日で表示できます。
2月などの月末で31日までない部分を空白として表示する場合は、
C1の式を=IF(B1="","",IF(DAY(B1+1)=1,"",B1+1))としましょう。
これであれば、左のセルの次の日の日付が1日であった場合に空白、違っていれば左のセルの次の日を表示させることができ、左のセルが空白であれば空白となります。
（2月28日の次の日が3月1日なら、1日なので空白となり、その右のセルは左側のセルが空白なのでやはり空白となります）
C1の式を必要なだけ（31日まで？）コピーしてください。

なお無いとは思いますが、カレンダーシートの日付の下にある予定の部分には、数字のみの入力はないものとします。（日付の検索に予定の欄がヒットしないように）

WEEKDAY(B1)でB1の曜日を数字に変えて、
ADDRESS(1,WEEKDAY(B1),4)でその曜日の1行目のセル番地（日曜ならA1、土曜ならG1）を取得。
A~G列つまりアルファベット1文字と分かっているので、
LEFT(ADDRESS(1,WEEKDAY(B1),4),1)でその列のアルファベットを取得。
INDIRECT("カレンダー!"&LEFT(ADDRESS(1,WEEKDAY(B1),4),1)&":"&LEFT(ADDRESS(1,WEEKDAY(B1),4),1))で、カレンダーシートのその曜日の列全体を指定できます。

カレンダーの方は1日と書かずに1と書いていますが、数字の1が入っているのか、2017年3月1日と入っているのか分からないです。
よってカレンダーのセルに年月日のデータで入っている場合、
MATCH(B1,INDIRECT("カレンダー!"&LEFT(ADDRESS(1,WEEKDAY(B1),4),1)&":"&LEFT(ADDRESS(1,WEEKDAY(B1),4),1)),FALSE)
日にちの数字だけの場合、
MATCH(DAY(B1),INDIRECT("カレンダー!"&LEFT(ADDRESS(1,WEEKDAY(B1),4),1)&":"&LEFT(ADDRESS(1,WEEKDAY(B1),4),1)),FALSE)
とします。これで、その列の中でB1に一致する日のセルが何番目にあるかを取得します。

これによって該当するセルの列と行が分かったので、その１つ下のセルのデータをINDIRECTによって取得します。

※※まとめです。※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※

カレンダーが年月日なら
=INDIRECT("カレンダー!"&LEFT(ADDRESS(1,WEEKDAY(B1),4),1)&MATCH(B1,INDIRECT("カレンダー!"&LEFT(ADDRESS(1,WEEKDAY(B1),4),1)&":"&LEFT(ADDRESS(1,WEEKDAY(B1),4),1)),FALSE)+1)

カレンダーが日にちの数字だけなら
=INDIRECT("カレンダー!"&LEFT(ADDRESS(1,WEEKDAY(B1),4),1)&MATCH(DAY(B1),INDIRECT("カレンダー!"&LEFT(ADDRESS(1,WEEKDAY(B1),4),1)&":"&LEFT(ADDRESS(1,WEEKDAY(B1),4),1)),FALSE)+1)

をスケジュールシートのB2に入れる。
必要なだけコピーする。B1が1日なら31日はAF1です。

スケジュールシートのB1は年月日データを入れる。
年月を別のセルで指定する場合は、
B1=DATE(A1,A2,1)　（A1,A2はそれぞれ年、月、を入力するセルを指定）
とする。

C1=IF(B1="","",IF(DAY(B1+1)=1,"",B1+1))
として、必要なだけコピーする。

yuji3690 · Answer

No2です。
一応聞いておきますが、カレンダーぽい形式なだけで、カレンダーと同じではないですよね？
（横が６列しかないですし）
カレンダーと同じであるなら、日付から曜日に換算して、検索列を指定するようにすれば、行毎にあるかどうか検索しなくても良くなりますので、一応情報確認として。

tom04 · Answer

No.3・4です。

たびたびごめんなさい。
No.4で誤記がありました。
お判りかと思いますが、

＞前回のSheet1の1行目シリアル値・・・
は
＞前回のSheet2・・・
の間違いです。

どうも失礼しました。m(_ _)m

tom04 · Answer

No.3です。

投稿後気づきました。
前回のSheet1の1行目シリアル値も「年」「月」の数値の変更だけで対応できる方が良いと思いますので、
B1セルの数式を
=IFERROR(SMALL(Sheet1!$A$4:$G$15,COLUMN(A1)),"")
に変更し、右へずぃ～～～！っとフィル＆コピー！

これで1日からのシリアル値になると思います。m(_ _)m

tom04 · Answer

こんばんは！

一例です。
↓の画像のように元データのカレンダーはSheet1に作成し、Sheet2に表示するとします。
いずれにしてもシリアル値で扱った方が簡単だと思います。
まずカレンダーの作成方法です。
画像ではSheet1のA1セルに「西暦年」、C1セルに「月」の数値を入力すると
その月の7列カレンダーができるようにしてみました。
F1セルに
=DATE(A1,C1,1)
という数式を入れセルの表示形式はユーザー定義から　mmm　としています。

A4セル（セルの表示形式はユーザー定義から　d　とだけしておく）に
=IF(MONTH($F$1-WEEKDAY($F$1)+COLUMN(A1)+7*(ROW(A2)/2-1))=$C$1,$F$1-WEEKDAY($F$1)+COLUMN(A1)+7*(ROW(A2)/2-1),"")
という数式を入れ　→　A4・A5セルを範囲指定　→　A5セルのフィルハンドルで右へコピー　→　そのまま下へ2行ずつコピー！
これでA1・C1の数値を入れ替えるだけでその月のカレンダーができます。

Sheet2の1行目（セルの表示形式はユーザー定義から　ｄ日　としておき）
B1セルに3/1のように最初のシリアル値を入力　→　右へフィル＆コピー！

B2セルに
=IFERROR(INDEX(Sheet1!$A$4:$G$15,SUMPRODUCT((Sheet1!$A$4:$G$15=B$1)*(ROW($A$4:$A$15)-2)),SUMPRODUCT((Sheet1!$A$4:$G$15=B$1)*COLUMN($A$1:$G$1)))&"","")
という数式を入れ右へコピーすると
画像のような感じになります。m(_ _)m

yuji3690 · Answer

作業用セルなりシートなりを使う前提とします。
使わなくても可能ですが、長くなって面倒なだけです。

カレンダーシートが6行目までで、日付が入ってるのは奇数行のみ？のようなので、
IFとCOUNTIFを3つずつ使い、該当するセルの行番号を数字で取得します。
その数値とMATCHを用いて、該当するセルの列番号（列のアルファベットではなく、数字になります）を取得します。
この列番号と行番号＋１（＋１するのは、1つ下にあるセルのデータを取得したいからです）を用いて、ADDRESSによりセル番地（A1など）の表示に変換します。
INDIRECTにより、このセル番地にあるセルのデータを取得します。