エクセルのマクロ(関数？)について質問です。

質問

会社で、以下の資料作成を依頼されましたが、いろいろ調べましたが全くやり方の見当がつかないので、教えてください。。

病院に来院された患者さんで、

(1)予約時間に遅刻した
(2)予約時間よりも早く受付、予約時間後検査開始
(3)予約時間よりも早く受付、予約時間よりも早く検査実施

以上３パターンの患者さんの数を把握したく、表を作成しなくてはなりません。
以下のURLに、サンプルファイルをアップしました。
http://kie.nu/yF2

列H(のセルH11からずっと下まで)に、上記3パターンが一発(もしくは2～3発)で表示されるようにしたいのです。
列A、B、Dの日時は、別のデータベースから抽出したものなので、最初から入力されています。

列Hのパターンを出す方法ですが、まず列C、E、F、Gにそれぞれ数式を入れないといけません。

例として、行11を試しに算出してみると、

・セルC11=D11－B11(時間のみ引き算したいのですが、日付も入っているので出来なくて困ってます。。以下同様)
・セルE11=D11－A11
・セルF11=D11－A11(計算結果がマイナスになる場合はゼロと表示されるようにしたい)
・セルG11=A11－B11

この計算で、G11の数字がマイナスになれば(1)のパターン、
E11の待ち時間のセルと、G11のセルの数字が両方プラスになっていれば(2)のパターン、
F11の待ち時間がゼロ、E11がマイナス、G11がプラスになっていれば(3)のパターン

ということになります。。

あと、セルの色も、パターン(1)の時は列Cのセルが、パターン(2)の時は列Eのセルが、パターン(3)の時は列Fのセルを
それぞれ黄色になるようにしたいのです。

すごく説明も複雑になってしまい大変申し訳ないのですが、
要は列Hに上記3パターンを簡単に表示させる方法が知りたいのです。

無理なお願いをしていることは承知しているので、「こんなの答えられない」といった趣旨の回答はご遠慮いただけたらと思います。。

出来れば至急でお返事いただきたいのですが、宜しくお願いいたします。

回答No.1

kagakusuki · ベストアンサー

「患者様の外来予約、受付状況」に関しては、まず、H11セルに次の関数を入力して下さい。

=IF(COUNT(INDEX($E:$E,ROW()),INDEX($G:$G,ROW()))=2,"予約時間"&INDEX({"に遅刻した。","通りの時間に受付。","よりも早く受付、"},SIGN(INDEX($G:$G,ROW()))+2)&IF(INDEX($G:$G,ROW())>0,"予約時間"&INDEX({"よりも早く検査実施。","通りの時間に検査開始。","後検査開始。"},SIGN(INDEX($E:$E,ROW()))+2),""),"")

　そして、H11セルをコピーして、H12以下に貼り付けて下さい。

お礼

有難うございます！！
ほんとに、なんとお礼を言ったらいいのか。。
先ほど早速試してみましたが、全てうまくいきました。

本当に助かりました！！！
有難うございました。

回答No.6

tom04

No.5です！
画像をアップするのを忘れていました。

もう一度トライしてみます。
どうも失礼しました。m(_ _)m

回答No.5

tom04

こんばんは！
外しているかもしれませんが・・・

＞・セルC11=D11－B11(時間のみ引き算したいのですが、日付も入っているので出来なくて困ってま＞＞＞す。。以下同様)
に関しては計算式が入っているセルの表示形式をユーザー定義から
[m]
としてみてください。
おそらく普通に表示されると思います。

F11セルに
=IF(E11<0,0,E11)
という数式を入れF11セルのフィルハンドルでダブルクリック
（F列の表示形式も当然 [m]　とします）

H列の表示方法ですが、一案として、Sheetを一つ挿入します（おそらくSheet1というSheet名になると思います）

挿入したSheet（Sheet1）に↓の画像のように表を作成しておきます。
H11セルに
=VLOOKUP(SIGN(F11)*SIGN(G11),Sheet1!$A$1:$B$3,2,0)
という数式を入れ、H11セルのフィルハンドルでダブルクリック！

最後に色づけですが
すべて条件付き書式で

Excel2003の場合で説明します。

C列すべてを選択　→　メニュー　→　書式　→　条件付き書式　→　数式が　→　数式欄に
=G1<0
として　書式　→　パターン　→　黄色を選択

E列すべてを範囲指定　→　・・・中略・・・　→　数式欄に
=F1*G1>0
として　黄色を選択

F列すべてを範囲指定　→　・・・中略・・・　→　数式欄に
=AND(F1<>"",F!=0)
として　黄色を選択

Excel2007以降のバージョンでも同様ですが、最初の入りだけちょっと違います。
（2007以降の場合　範囲指定　→　条件付き書式　→　新しいルール　→　数式を使用して・・・）
あとの操作は2003と一緒です。

何とかご希望に近い形にならないでしょうか？m(__)m

お礼

有難うございます！！
いろんなやり方があるのですね。。勉強になります。

わたしの職場のエクセルのバージョンは2003なのですが、家だと2007なので
両方のやり方を知ることが出来て大変参考になりました。
有難うございました！！

回答No.4

kagakusuki

因みに、他の列の関数ですが、C,G,F,G列の書式設定の表示形式を[標準]とした上で、
C列の関数を

=IF(ISNUMBER(1/DAY(INDEX($B:$B,ROW()))/DAY(INDEX($D:$D,ROW()))),IF(INDEX($B:$B,ROW())>INDEX($D:$D,ROW()),"入力ミス",ROUND((INDEX($D:$D,ROW())-INDEX($B:$B,ROW()))*1440,0)),"")

E列の関数を

=IF(ISNUMBER(1/DAY(INDEX($A:$A,ROW()))/DAY(INDEX($D:$D,ROW()))),ROUND((INDEX($D:$D,ROW())-INDEX($A:$A,ROW()))*1440,0),"")

F列の関数を

=IF(ISNUMBER(INDEX($E:$E,ROW())),IF(INDEX($E:$E,ROW())<0,0,INDEX($E:$E,ROW())),"")

G列の関数を

=IF(ISNUMBER(1/DAY(INDEX($A:$A,ROW()))/DAY(INDEX($B:$B,ROW()))),ROUND((INDEX($A:$A,ROW())-INDEX($B:$B,ROW()))*1440,0),"")

にするという方法もあります。
　これら、4つの関数は、どの行に入力しても構いませんし、元テータであるA列、B列、D列のセルに対して、セルの「切り取り」、「削除」、「挿入」、「貼り付け」等の、「セルの配置をずらしてしまう編集操作」を行った場合でも、正常に動作するようになっています。

回答No.3

kagakusuki

【Excel2007よりも前のバージョンの場合】
「C11セル～表の枠内で最も下にある行のC列のセル」のセル範囲をまとめて範囲選択
　　↓
メニューの[書式]ボタンをクリック
　　↓
現れた選択肢の中にある[条件付き書式]をクリック
　　↓
現れた「条件付き書式の設定」ダイアログボックスの左端の欄をクリック
　　↓
現れた選択肢の中にある「数式が」をクリック
　　↓
「条件付き書式の設定」ダイアログボックスの左から2番目の欄に

=AND(ISNUMBER(1/DAY(INDEX($A:$A,ROW()))/DAY(INDEX($B:$B,ROW()))),INDEX($A:$A,ROW())<INDEX($B:$B,ROW()))

か或いは

=INDEX($H:$H,ROW())="予約時間に遅刻した。"

と入力
　　↓
「条件付き書式の設定」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[パターン]タブをクリック
　　↓
現れた色のサンプルの中にある黄色の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式の設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「E11セル～表の枠内で最も下にある行のE列のセル」のセル範囲をまとめて範囲選択
　　↓
メニューの[書式]ボタンをクリック
　　↓
現れた選択肢の中にある[条件付き書式]をクリック
　　↓
現れた「条件付き書式の設定」ダイアログボックスの左端の欄をクリック
　　↓
現れた選択肢の中にある「数式が」をクリック
　　↓
「条件付き書式の設定」ダイアログボックスの左から2番目の欄に

=AND(ISNUMBER(1/DAY(INDEX($A:$A,ROW()))/DAY(INDEX($B:$B,ROW()))/DAY(INDEX($D:$D,ROW()))),INDEX($A:$A,ROW())>INDEX($B:$B,ROW()),INDEX($A:$A,ROW())<INDEX($D:$D,ROW()))

か或いは

=INDEX($H:$H,ROW())="予約時間よりも早く受付、予約時間後検査開始。"

と入力
　　↓
「条件付き書式の設定」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[パターン]タブをクリック
　　↓
現れた色のサンプルの中にある黄色の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式の設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「F11セル～表の枠内で最も下にある行のF列のセル」のセル範囲をまとめて範囲選択
　　↓
メニューの[書式]ボタンをクリック
　　↓
現れた選択肢の中にある[条件付き書式]をクリック
　　↓
現れた「条件付き書式の設定」ダイアログボックスの左端の欄をクリック
　　↓
現れた選択肢の中にある「セルの値が」をクリック
　　↓
「条件付き書式の設定」ダイアログボックスの左から2番目の欄にをクリック
　　↓
現れた選択肢の中にある[次の値に等しい]をクリック
　　↓
「条件付き書式の設定」ダイアログボックスの左から3番目の欄に

0

と入力
　　↓
「条件付き書式の設定」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[パターン]タブをクリック
　　↓
現れた色のサンプルの中にある黄色の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式の設定」ダイアログボックスの[OK]ボタンをクリック

回答No.2

kagakusuki

>あと、セルの色も、パターン(1)の時は列Cのセルが、パターン(2)の時は列Eのセルが、パターン(3)の時は列Fのセルをそれぞれ黄色になるようにしたいのです。

　条件付き書式を設定する事で行います。
　Excelでは、条件付き書式の設定方法を始めとして、幾つかの機能や操作方法が、そのバージョンによって異なっています。
　ですから、Excelに関する御質問をされる場合には、必ず、御使用になられているExcelのバージョンを、質問文中に明記して戴く様、御願い致します。

【ExcelのバージョンがExcel2007以降の場合】
Excelウィンドウの[ホーム]タブをクリック
　　↓
表中の適当なセルを選択
　　↓
選択されているセル範囲を変えないまま、「スタイル」グループの中にある[条件付き書式]ボタンをクリック
　　↓
現れた選択肢の中にある[ルールの管理]をクリック
　　↓
現れた「条件付き書式ルールの管理」ダイアログボックスの[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの「ルールの種類を選択して下さい」欄の中にある[数式を使用して、書式設定するセルを決定]をクリック
　　↓
現れた「次の数式を満たす場合に値を書式設定」欄の中に

=AND(ISNUMBER(1/DAY(INDEX($A:$A,ROW()))/DAY(INDEX($B:$B,ROW()))),INDEX($A:$A,ROW())<INDEX($B:$B,ROW()))

か或いは

=INDEX($H:$H,ROW())="予約時間に遅刻した。"

と入力
　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[塗りつぶし]タブをクリック
　　↓
現れた色のサンプルの中にある黄色の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの「適用先」欄の中で、尤も上の欄をクリック
　　↓
C1セルをクリック
　　↓
表中の最下段の行（表の枠内であれば空欄も含む）のC列のセルにカーソルを合わせてから、[Shift]キーを押しながら、マウスの左ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの「ルールの種類を選択して下さい」欄の中にある[数式を使用して、書式設定するセルを決定]をクリック
　　↓
現れた「次の数式を満たす場合に値を書式設定」欄の中に

=AND(ISNUMBER(1/DAY(INDEX($A:$A,ROW()))/DAY(INDEX($B:$B,ROW()))/DAY(INDEX($D:$D,ROW()))),INDEX($A:$A,ROW())>INDEX($B:$B,ROW()),INDEX($A:$A,ROW())<INDEX($D:$D,ROW()))

か或いは

=INDEX($H:$H,ROW())="予約時間よりも早く受付、予約時間後検査開始。"

と入力
　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[塗りつぶし]タブをクリック
　　↓
現れた色のサンプルの中にある黄色の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの「適用先」欄の中で、尤も上の欄をクリック
　　↓
E11セルをクリック
　　↓
表中の最下段の行（表の枠内であれば空欄も含む）のE列のセルにカーソルを合わせてから、[Shift]キーを押しながら、マウスの左ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの「ルールの種類を選択して下さい」欄の中にある[指定する値を含むセルだけを書式設定]をクリック
　　↓
現れた「次の数式を満たす場合に値を書式設定」の左端の欄をクリック
　　↓
現れた選択肢の中にある[セルの値]をクリック
　　↓
「次の数式を満たす場合に値を書式設定」の左から2番目の欄をクリック
　　↓
現れた選択肢の中にある[次の値に等しい]をクリック
　　↓
「次の数式を満たす場合に値を書式設定」の左から3番目の欄に

0

と入力
　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[塗りつぶし]タブをクリック
　　↓
現れた色のサンプルの中にある黄色の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの「適用先」欄の中で、尤も上の欄をクリック
　　↓
F11セルをクリック
　　↓
表中の最下段の行（表の枠内であれば空欄も含む）のF列のセルにカーソルを合わせてから、[Shift]キーを押しながら、マウスの左ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[適用]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[OK]ボタンをクリック