エクセルで1カ月の利用者一覧表を作成したい

質問

Ａ列　 Ｂ列　　Ｃ列　　にそれぞれ
氏名　入居日　退去日　を入れたとします。
あい　11/2　　11/4
別シートに表を作り
　　　　　　１日　２日　３日　４日　５日
あい　　　　　　 　○　　○　　○　　　
というように、利用する日に○をつけたいのですが、
エクセルを駆使して出来るものなのでしょうか？
今エクセル関数やマクロを覚えたいと、つい最近勉強し始めたのですが、
仕事をしていて、こういうのができればいいのになと思ったので質問してみました。

欲をいえば、「あい」さんが何度も利用する場合も同じ列に○がつくとうれしいです。

よろしくお願いします。

回答No.2

tom04 · ベストアンサー

こんばんは！
一例です。

↓の画像で説明させていただきます。
上側がSheet1でSheet2に表示させるようにしています。
Sheet1のD列を名前用の作業列とさせてもらっています。
（名前が重複する場合は一つにまとめるためです）

D2セルに
=IF(COUNTIF($A$2:A2,A2)=1,ROW(A1),"")
としてオートフィルで下へコピーします。

Sheet1・2とも数値の欄はシリアル値としています。

Sheet2の日付セルB4のセルの書式設定から
表示形式をユーザー定義で　d とし、
=IF(COUNTBLANK($A$1:$A$2)>0,"",IF(MONTH(DATE($A$1,$A$2,COLUMN(A1)))=$A$2,DATE($A$1,$A$2,COLUMN(A1)),""))
という数式を入れ、31日まで列方向にオートフィルでコピーします。

そして、氏名のA5セルに
=IF(COUNT(Sheet1!$D$2:$D$100)<ROW(A1),"",INDEX(Sheet1!$A$2:$A$100,SMALL(Sheet1!$D$2:$D$100,ROW(A1))))
としてオートフィルで下へコピー

最後にB5セルに
=IF($A5="","",IF(SUMPRODUCT((Sheet1!$A$2:$A$100=Sheet2!$A5)*(Sheet1!$B$2:$B$100<=Sheet2!B$4)*(Sheet1!$C$2:$C$100>=Sheet2!B$4)),"○",""))

という数式を入れ、列方向と行方向にオートフィルでコピーすると
画像のような感じになります。

これで、何とか希望に近い形にならないでしょうか？

以上、長々と書きましたが
参考になれば幸いです。m(__)m

お礼

画像付きでの回答
とてもわかりやすかったです。
ありがとうございました。
Book2.2の「年」と「月分」のところも作ってくださり、
見やすい表になってより作業がしやすくなりました。

回答No.5

某HN クロメート(Chromate)（@CoalTar）

レイアウトなどは添付図参照
作業列1 D2セル =$A2&B2
作業列2 E2セル =$A2&C2+1
D2:E2セル フィルハンドルダブルクリック

B9セル
=IF(ISNA(MATCH($A9&B$8,$D$2:$D$6,0)),"","○")
下へオートフィル
C9セル
=IF(B9="○",IF(ISNA(MATCH($A9&C$8,$E$2:$E$6,0)),"○",""),
　 IF(ISNA(MATCH($A9&C$8,$D$2:$D$6,0)),"","○"))
右へ下へオートフィル
8行目は必要に応じて表示形式 ユーザー設定 d日
別表は切り取り&貼り付けで別シートへ

6行目にあるような 同一人物で退去日の翌日入居はないものとする。
もちろん、重なりも。参考まで

お礼

わざわざ画像付きでの回答
ありがとうございました。
とてもわかりやすかったです。

回答No.4

imogasi

VBAでやると簡単になる課題だと思う。
ーーー
関数では、配列数式だが
例データ
Ａ２：Ａ７
今井 11月2日 11月5日
山田 11月6日 11月8日
今井 11月10日 11月11日
近藤
今井 11月15日 11月19日
山田 11月9日 11月16日
日日は11/2のように入れて、日付シリアル値で入っているとする。
ーー
Ａ１１とＡ１３より右列に
氏名 11月2日 11月3日 11月4日 11月5日 11月6日 11月7日 11月8日・・・・月末日まで
今井
山田
を用意する。
ーー
改めて、人間が入力しないなら、
データーフィルターフィルタオプションの設定ー重複するレコードは無視するで、ダブりのない、人名リストが作れる。
ーー
Ｂ１２に
=SUM(IF(($A$1:$A$10=$A12)*($B$1:$B$10<=B$11)*($C$1:$C$10>=B$11),1,0))
と入れて、ＳＨＩＦＴ，ＣＴＲＬ，ＥＮＴＥＲの３つのキーを同時押し。（配列数式）
月末まで右方向に式を複写。そして
１－月末日の行まで範囲指定し＋ハンドルを出して、式を下方向に人数分式を複写。
ーー
結果
- 11月2日 11月3日 11月4日 11月5日 11月6日 11月7日 11月8日 11月9日 11月10日 11月11日 11月12日 11月13日 11月14日 11月15日 11月16日 11月17日　以右略
今井 1 1 1 1 0 0 0 0 1 1 0 0 0 1 1 1
山田 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
１を○にするのはＩＦ関数を冠して簡単に変更できる。

お礼

わかりやすい解説ありがとうございました。
関数が一つでできるところが素敵です。

回答No.3

hige_082

詳細が不明なので適当です
なぜ、別シートの表には列表記がないのでしょうか？
なぜ、シートが複数あるのにシート名がないのでしょうか？
関数やマクロを作成するのに必要だと思いませんか？

入力シートがsheet1、別シートがsheet2
日付はすべてシリアル値が入力されているものとしています

Sub test()
Dim a As Variant
Dim x As Integer, y As Integer, z As Integer
With Worksheets("sheet1")
a = .Range("a2", .Cells(Rows.Count, 3).End(xlUp))
End With
With Worksheets("sheet2")
For x = 1 To UBound(a)
For y = 2 To .Cells(Rows.Count, 1).End(xlUp).Row + 1
If .Cells(y, 1).Value = a(x, 1) Or .Cells(y, 1).Value = "" Then
If .Cells(y, 1).Value = "" Then .Cells(y, 1).Value = a(x, 1)
For z = 2 To 32
If .Cells(1, z).Value >= a(x, 2) And .Cells(1, z).Value <= a(x, 3) Then
.Cells(y, z).Value = "○"
End If
Next z
Exit For
End If
Next y
Next x
End With
End Sub

そんな訳で動作確認も適当です
勉強しているとのことなので、解説もしません

お礼

言葉足らずですみませんでした。
上の作業で動きましたが、
なぜそうなるのかがまだまだ勉強不足でわかりません。
解説していただいてもわからなかったと思います。
自分で調べて自分でわかるように勉強していきます。
ありがとうございました。

回答No.1

hallo-2007

勉強のネタとしてですが、
　　A　　　　B　　　　C　　　D　
1　　　　　　１日　２日　３日　４日　５日
2　あい　　　　　　 　○　　○　　○
の日付は、きちんと日付(2009/10/1)で入力しておいて、表示形式で　1日と表示するようにしておきます。
B2セルには、B1セルの日付が入居日より大きく、退去日より小さく、
かつ、A2セルが氏名と同じときは○、それ以外は　空白
=IF(And(B$1>=データ!$B2,B$1<=データ!$C2,$A2=データ!$A2),"○","")
あとは右へコピィします。

お礼

とてもわかりやすい回答
ありがとうございました。