excel関数の活用方法について

質問

初めて投稿いたします。
excel2007を活用したデータ加工について、ご教示願います。

現在2つのデータをまとめ、統計データを作成したいと考えています。

統計資料の集計を行うにあたり、必要な数字だけを抽出したいのですが、どのような関数が適しているのでしょうか。

例えば・・・

Aのホテルに2012/5/1～2012/5/31まで滞在した方がいたとします。Bのホテルには2012/5/10～2012/6/10まで滞在した方がいました。AとBの滞在期間が1日でも重なっていれば、if関数等で区別をつけたいのですが、どのような計算式を立てれば良いのでしょうか。

データ内の期間に関する項目はチェックインとチェックアウトの日付のみと仮定します。

大変申し訳ないのですが、excelの操作経験が浅いので、なるべく詳細にご説明いただければ幸いです。

よろしくお願いいたします。

回答No.1

keithin · ベストアンサー

A1セルとB1セルにＡさんのチェックインとアウト
C1セルとD1セルにＢさんのチェックインとアウト

Ｅ１セルに
=IF(MAX(A1:D1)-MIN(A1:D1)<=B1-A1+D1-C1,"overlap","")
のように計算できます。

チェックイン・アウトの日付は
2012/6/1
のように年/月/日の書式で記入します。

お礼

早速の回答ありがとうございました。
おかげさまで、無事資料を作成することができました！
厚く御礼申し上げます。

回答No.4

MackyNo1

ご希望の操作がA列にチェックイン日時、B列にチェックアウト日時が入力してあるシートで宿泊時間の重複を調べたいということなら以下のような関数を入力します。

=IF(SUMPRODUCT(($A$2:$A$100<A2)*($B$2:$B$100>A2)+($A$2:$A$100<B2)*($B$2:$B$100>B2)+($A$2:$A$100>A2)*($B$2:$B$100<B2)),"重複","")

上記の数式の意味は、チェックインがチェックインとチェックアウト日時の間にある、またはチェックアウトがチェックインとチェックアウト日時の間にある、またはチェックインがチェックイン日時より大きくかつチェックアウトがチェックアウト日時よりも小さい場合に「重複」と表示するようにしています。

お礼

イメージ図を通した解説、とてもわかりやすかったです。
ありがとうございました！

回答No.3

KURUMITO

目的がよくわかりませんので次のようなデータが有るとして同じ名前の方がホテルの滞在期間がダブっていた場合に「重なり」と表示させるとして次のようにします。
1行目が項目名としてA1セルに氏名、B1セルにホテル名、C1セルにチェックイン、D1セルにチェックアウトの文字が入力されているとして下行にデータが並んでいるとします。
最初にデータの並び替えを行ってA列を最優先に昇順で、C列を第二優先で昇順で並べます。
その後に重なりを表示させるためにE2セルには次の式を入力して下方にドラッグコピーします。

=IF(C2="","",IF(COUNTIF(A$2:A2,A2)=1,"",IF(MAX(INDEX(D:D,MATCH(A2,A:A,0)):INDEX(D:D,MATCH(A2,A:A,0)+COUNTIF(A$2:A2,A2)-2))>C2,"重なり","")))

なお、この式では同じ日にチェックインとチェックアウトが有っても重なりが有るとは判定しません。

お礼

KURUMOTOさん、とても丁寧なご説明ありがとうございます。
なかなか上手く文章がまとめられず、申し訳ありませんm(__)m

本日いただいた回答は、今後も業務に活かしていきたいと思います。

回答No.2

MarcoRossiItaly

A1、B1、C1、D1セルにそれぞれ、Aホテルへのチェックイン、Aホテルからのチェックアウト、Bホテルへのチェックイン、Bホテルからのチェックアウトの日が入力されているものとします。

●次の数式は、重なりの日数を算出します。

=max(,min(b1,d1)-max(a1,c1)+1)

●上の数式の最後に「>0」を追記すると、重なりがあるときは「TRUE」を、ないときは「FALSE」を表示します。

=max(,min(b1,d1)-max(a1,c1)+1)>0

●「TRUE」、「FALSE」以外の文字列を表示させたい場合は、IF関数と組み合わせます。

=if(max(,min(b1,d1)-max(a1,c1)+1),"あり","なし")

お礼

回答ありがとうございます
こちらの説明もわかりやすく、とても参考になりました！