Excel　関数　①特定のセルに色をつけたい、②①の内容を別シートにリンクして表示させたい。

お役に立てたようで良かったです。

私など常にミスの繰り返しです。それを次回に生かせれば良いのではありませんか？。

お忙しい中、大変かと思いますが、実用的で良いものを作るために頑張ってください！！。

さて、時刻の差分の計算方法ですが・・、

前提として、15:00 といったような時刻を表すのに 1500 という数値を入力していて、「セルの書式設定」で「 : 」を表示させているということですね。

例えば、A1セルと B1セルにそういった方法で時刻を表示させている場合、その差分を求める数式は以下のようになります。一例です。

・番外数式

=ABS(TEXT(A1,"0!:00")-TEXT(B1,"0!:00"))

※時間ではなく時刻同士の差分をとる場合、結果がマイナスの時刻になることは有り得ないので、ABS 関数で差分の絶対値をとっています。

尚、この数式を入れるセルでは、「セルの書式設定」の「表示形式」の「ユーザー定義」で [h]:mm を設定してください。

また、エラー処理は何も行っていませんので、必要であればご自身で工夫してみてください。

下記番外図は、A1:B10 に適当な数値（ 1500、1559、・・といった数値。「 : 」は「セルの書式設定」で付加）を入れ、上記の数式を C1:C10 の範囲にコピー・貼り付けした結果です。（この範囲に「セルの書式設定」で [h]:mm も設定しています。）

※具体的なコピー方法としては、C1セルに上記の数式を貼り付け、これをコピーし下方向（行方向）に C10セルまで貼り付けています。（慣れていれば、オートフィルでコピーしたほうが簡単です。）念のため追記。

・番外図

ご参考になれば幸いです。

尚、関連質問なら良いのですが、１スレッド１質問が原則ですので、全く関連のない新たな質問は新規スレッドでお願いいたします。

うっかりされたのかと思いますが、以後、お気を付けください。

上記の数式・図ともに、本来の質問と同様の通番を付加すると紛らわしいので、「番外」という文字列を付加して区別しています。他意はありません。

こんにちは。

＞検証する時間まで割いていただきありがとうございます。

”ひまじん”なのでお気になさらず。こういった問題を考えるのも好きなので、却って恐縮です。

本題ですが、提示されている A6セル、A7セル、A8セルの数式が、それで間違いないのであれば、原因はコピー・貼り付けの際の手順の間違いかと思います。

前回書いたように、数式４－２をコピーし、A6セルに貼り付けた後これを配列数式にするところまでは間違っていません。

おそらくですが、その後、A7セル以降にも数式４－２そのものを貼り付けていったのではありませんか？。

そうだとすれば、その貼り付け手順が間違っています。

数式４－２の最後のほうに ROW(A1) と書いている箇所がありますが、これはこの数式を A6セルに貼り付けて配列数式にした後、その配列数式をコピーし A7セル以降（行方向）に貼り付けていくことで、自動的に ROW(A2),ROW(A3),・・・というようにカウントアップさせたいために書いています。（コピー・貼り付けの後の各セルの数式の違いは、唯一この箇所だけです。）

これにより、1,2,3,・・・というような数値を SMALL 関数に渡すことができ、火曜日と金曜日の日付を小さい順に取り出すことが出来るからです。

つまり、A6セルの数式では ROW(A1) で良いのですが、その他のセルでは、

A7セル ROW(A2)

A8セル ROW(A3)

　・

　・

　・

というように自動的に変化しなければ、日付を小さい順に取り出すことが出来ません。

提示されている、A6セル、A7セル、A8セルの数式では、全て ROW(A1) となってしまっています。

各セルの数式が同一の状態だとすると、数式の他の部分は全て同一（正常）なので、表示される日付は全て 1/4 となります。

＜数式４－２の貼り付け手順＞

以下、数式４－２を A6セルに貼り付け、これを配列数式にした後の手順です。

• 誤った手順（推測です）
  数式４－２そのものを A6セルと同様に、A7セルから A15セルの 範囲で、1セルずつ貼り付けては配列数式にするといった手順を繰り返す。
• 正しい手順
  数式４－２そのものではなく、A6セルに貼り付けて配列数式にしたものをコピーし、A7セルから A15セルに貼り付ける。

※9月26日に書いた数式４－２のコピー方法の説明が紛らわしかったかもしれませんね・・。失礼いたしました。

尚、最新版の Excel であれば「スピル」が働くので、数式４－１を使い、これをコピーし A6セルに貼り付けるだけで済んでしまいます。

A6セルの数式を配列数式にする必要も、これをコピーして A7セル以降に貼り付ける必要もありません。

この辺が、旧来の Excel との大きな違いです。

※上記の説明文の中では、特に強調したい場合を除き「配列数式」も「数式」と書いています。必要であれば、適当に読み換えてください。

ご参考まで。

＜修正＞

最後のほうで最新版の Excel との違いを書きましたが、「数式４－２」と書いてしまっていましたので、「数式４－１」に修正しました。

この部分の文章そのものも修正しました。失礼いたしました。

おっとっと～・・・。

①、②を行うためには、これまでご紹介してきた数式を更に変更する必要があります。

数式がいくつもあって煩雑で分かりにくくなってきましたので、改めて、①、②を考慮した「提案１」としてまとめてみました。

「提案１」は、全て最初から作りなおす場合を想定して書いています。ご注意ください。

＜提案１＞

［ カレンダーの作成 ]

1. シート名（①を考慮。）
   何年かといった数字を加えずに、単に「カレンダー」とします。
2. １行目の入力（②を考慮。）
   A1セルに 2022/1/1 、B1セルに 2022/2/1 、・・・というように月初の日付を入力していき、最後に L1セルに 2022/12/1 を入力します。
   ※単に 1/1 とかではなく 2022/1/1 というように、必ず「年」の入力を行ってください。
3. ２行目以降の数式（②を考慮。）
   A2セルに以下の数式３を入力（あるいはコピー・貼り付け）し、これを右方向（列方向）に L2セルまでコピーし、更に、A2セルから L2セルまでを選択したまま 31行目までコピーします。
   ・数式３
   =IF(A1<EOMONTH(A$1,0),A1+1,"")
4. 曜日の表示
   日付部分全体（ A1:L31 の範囲）を選択し、「セルの書式設定」－＞「表示形式」－＞「ユーザー定義」で yyyy/m/d"("aaa")" を設定します。

図４は、上記の手順でカレンダーを作成した結果です。

・図４

※尚、各セルの背景色の色付けについては「条件付き書式」で行っていますが、ご自身で設定できているようですし、こういった色付けは各数式に影響しませんので設定方法等は省略します。

［ 物品発注書の作成 ］

※以下で書いている部分以外については書き込み済みと想定しています。

1. シート名（①を考慮。）
   まず最初に 1月のシートを作るので、シート名を「1月」とします。
2. 数式の入力（①を考慮。）
   A6セルに以下の数式４を入力（あるいはコピー・貼り付け）します。
   ・数式４（数式１－１修正版の改修版。）
   =LET(dt,OFFSET(カレンダー!$A$1,,TRIM(RIGHT(SUBSTITUTE(LEFT(CELL("filename",A1),LEN(CELL("filename",A1))-1),"]"," "),2))*1-1,31,),FILTER(dt,IFERROR((WEEKDAY(dt)=3)+(WEEKDAY(dt)=6),0)))

・変な箇所で自動的に改行されているかもしれませんが、全部で 1行の数式です。
・この数式では「スピル」が働きます（最新版の Excel ではこれが標準です）ので、A6セルに数式を入れるだけで A列に必要な日付データがすべて表示されます。（ FILTER 関数は、この「スピル」が働かないと動作しません。）
・この数式では、シート名から何月の日付データを表示しなければならないのかを判断しています。
例えば、シート名が「1月」でしたら、この数式４の入力と下記の数式５のコピーが終わった時点で、「カレンダー」シートのデータを参照して 1月の全ての日付データが表示されます。

C6セルに以下の数式５を入力（あるいはコピー・貼り付け）します。
・数式５（数式２と同じもの。）
=IF(A6="","",A6+1)

・この数式を C15セルまでコピーします。（ C15セルとしているのは、ここまでは表示されないだろうと推測できる最大の範囲だからです。）
・この数式では、単純に A6セルが空白だったら空白（空白の文字列）を表示し、空白でなければ A6セルの日付を +1 して表示します。

※両方の数式の入力後、A列と C列の日付データ両方に、「セルの書式設定」－＞「表示形式」－＞「ユーザー定義」で m/d"("aaa")" を設定します。

※おまけ（笑）
A1セルに以下の数式６を入力（あるいはコピー・貼り付け）します。
A1セル（結合セル）には「2022年　1月　物品発注書」といった文字列を入力しているかと思いますが、この部分も以下の数式６を入力することで「年月」の自動入力が可能です。よろしければ組み込んでみてください。
・数式６
=TEXT(カレンダー!$A$1,"yyyy")&"年　"&TRIM(RIGHT(SUBSTITUTE(LEFT(CELL("filename",A1),LEN(CELL("filename",A1))-1),"]"," "),2))&"月　物品発注書"

・変な箇所で自動的に改行されているかもしれませんが、全部で 1行の数式です。

・この数式では、「カレンダー」シートの A1セルの内容から「年」の情報を取り出し、このシートのシート名から「月」の情報を取り出して表示させています。

3. 各月のシート作成
   数式４では、シート名の中から「月」のデータを取り出して、何月の日付データを表示しなければならないかを判断し、「カレンダー」の中から該当の日付データを取り出しています。
   なので、「カレンダー」が出来ているのであれば（色付けは特に必要ありません）、以下のような方法で簡単に 12ヶ月分の「物品発注書」を一気に作り上げることが出来ます。

［１］「1月」のシートのコピーを作成し、シート名を「2月」に変更するだけで 2月分が出来る。
［２」シートのコピーを繰り返し、シート名の月の部分の数字を変えるだけで 12ヶ月分の「物品発注書」が出来上がる。
※もちろん、毎月手書きで修正しなければならない箇所については、修正が必要ですが・・。

図５は上記の手順で「物品発注書」を作成した結果です。（おまけ含む）
※画像キャプチャしきれないため、1月、2月、5月、12月のみ表示させています。
・図５

［ 翌年からの運用 ］

1. 今年（2022年）作成したファイルをコピーします。（ファイル名は何でも可。）
2. コピーしたファイルを開き、「カレンダー」シートの A1セルの日付データを 2023/1/1 に変更（年を変更）し、同様の変更を L1セル（ 12月分）まで繰り返します。

※これだけで、その年の「カレンダー」も「物品発注書」の日付データも 12ヶ月分が一気に出来上がります。

以上です。

尚、個人的な感想ですが、図４の「カレンダー」の表示は細かすぎて見にくくないですか？。

「セルの書式設定」で「年」データを表示しないように設定すれば良いかと思うのですが、そうすると今度は何年の日付なのか分からなくなってしまいます。

そこで例えば、図６のような「カレンダー」（いわゆる「万年カレンダー」です）にすれば、かなり見やすくなりますし、「年」データを 1行目（ 1ヵ所）に入力するだけで、その年の「カレンダー」も「物品発注書」の日付データも 12ヶ月分を一気に作成することも可能です。

・図６

これを「提案２」としてご紹介するつもりで「物品発注書」の数式も含め全て作ってみたのですが、ちょっと疲れ気味で・・、今はただ単に代案もあるということで憶えておいていただければと思います。

図６の「万年カレンダー」は、色々と応用も利きます。今回だけではなく別の機会に使ってみても良いかと思いますよ。

• A1セル（結合セル）には「年」のデータを数値で入れ、「セルの書式設定」で G/標準"年" を設定。
• A2セルには「月」のデータを数値で入れ、「セルの書式設定」で G/標準"月" を設定した後、これをオートフィルで L2セルまで連続データとしてコピー。
• A3セルの数式は =DATE($A1,COLUMN(A1),1) です。これを L3セルまでコピー。
• A4セルの数式は =IF(A3<EOMONTH(A$3,0),A3+1,"") です。これを A4:L33 の範囲にコピー。
• A3:L33 の範囲には、「セルの書式設定」で d"("aaa")" を設定。

お役に立てれば幸いです。