（エクセル）日付に相当するデータを入力する

質問

シート1に以下のように、Ａ列には氏名、Ｃ列に日付がランダムに入力されています。
　　Ａ　　　Ｂ　　　Ｃ
1　山田　　　　　4/4 3:00
2　佐藤　　　　　4/3 2:00
3　石井　　　　　4/4 3:00
4　加藤　　　　　4/3 2:00
5　田中　　　　　4/1 5:00

シート2に上から順位に並び替えたいです
　　Ａ　　　Ｂ　　　Ｃ　　　Ｄ　　　Ｅ　　　Ｆ　　　Ｇ
1　　　　　　　　 　4/1　 　 4/2 　　 4/3 　　4/4　　　4/5
2　　1　　　田中　4/1 5:00
3　　2　　　佐藤　 　　　　　　　4/3 2:00
4　　3　　　加藤　 　　　　　　　4/3 2:00
5　　4　　　山田　　　　　　　　　　　　　4/4 3:00
6　　5　　　石井　　　　　　　　　　　　　4/4 3:00
1行目のＣ～Ｇには既に4/1～4/5が入力されています。
対応するところに日付を入力し、さらにＢ列には氏名を表示したいです。

C2には「=IF(AND(SMALL(Sheet1!$C$1:$C$5,$A2)<D$1,SMALL(Sheet1!$C$1:$C$5,$A2)>=C$1),SMALL(Sheet1!$C$1:$C$5,$A2),"")」としてうまくいきました（C1:F6も同様）。

問題はＢ列なのですが、B2に「=INDEX(Sheet1!$A$1:$A$5,MATCH(SUM(C2:G2),Sheet1!$C$1:$C$5,0))」や「=INDEX(Sheet1!$A$1:$A$5,MATCH(SMALL(Sheet1!$C$1:$C$5,A2),Sheet1!$C$1:$C$5,0))」としても同じ失敗結果になりました。

両方とも、上から順に
田中
佐藤
佐藤　←失敗（加藤が正解）
山田
山田　←失敗（石井が正解）
となってしまい、重複する日付が失敗してしまいます。

Ｂ列にどのようにしたら良いか教えてください。
よろしくお願いします。

回答No.13

kagakusuki

回答No.6、10です。

>C列～F列が1900/1/0となってしまいました。

　何度も失礼してしまい申し訳御座いません。
　C列～F列のセルの書式設定の表示形式を[時刻]の

13:30

か、又は[ユーザー定義]の

h:mm

にして頂ければ、

Sheet2
　　 Ａ　　　Ｂ　　　Ｃ　　　Ｄ　　　Ｅ 　　　 Ｆ　　　 Ｇ
1　　No.　　氏名 　　4/1 　　4/2 　　 4/3 　　　4/4　　　4/5
2　　1　　　田中　　5:00
3　　2　　　佐藤　 　　　　　　　　 2:00
4　　3　　　加藤　 　　　　　　　　 2:00
5　　4　　　山田　　　　　　　　　　　　　　 3:00
6　　5　　　石井　　　　　　　　　　　　　　 3:00

という形式で表示される筈です。
　只、これではC列～G列のデータが時刻のみとなり、日付を含んでいないデータとなりますので、質問者様が御質問文で挙げておられる例とは少し異なる事になります。
　こうなってしまったのは、「日付自体は1行目に入力されているため、2行目以下は時刻のみを表示させれば良い」と私が勘違いしていたためです。
　そんな勘違いで質問者様には御迷惑をおかけしてしまい誠に申し訳御座いません。

　それで、C列～G列の2行目以下の表示を時刻だけではなく、月日と時刻を合わせて表示させる様にするためには、まずSheet2のC2セルに入力する関数である

=IF($A2="","",IF(INT(SMALL(Sheet1!$C:$C,$A2))=C$1,SMALL(Sheet1!$C:$C,$A2)-C$1,""))

の中の

-C$1

という部分を削除して

=IF($A2="","",IF(INT(SMALL(Sheet1!$C:$C,$A2))=C$1,SMALL(Sheet1!$C:$C,$A2),""))

として下さい。
　そして、Sheet2のC2セルをコピーして、C列～G列の2行目以下に貼り付けて下さい。
　その上で、C列～G列の2行目以下のセルの書式設定の表示形式を[ユーザー定義]の

m/d h:mm

として下さい。

お礼

何度も回答していただきありがとうございます。

時刻のみの場合、日付+時刻の場合、両方確認できました。
時刻だけ表示するなら表示形式を時刻にし、さらに日付まで表示させたいならば、表示形式もそれに合わせればいいので、データとして正確な日付と日時が反映されるために、どちらにしても「-C$1」でやっていた方が、良いのではと思ってしまいました。

ありがとうございました。

回答No.12

Chiquilin

#7です。

> B列が＃NAME？になってしまいました。
つづりをどこかで間違えているか どこにも書いてませんが あなたの Excel
のバージョンが 2003以下なんでしょう。

１０年以上も前のバージョンを利用しているのなら その旨最初に書いておく
べきかと思います。

やり方はいくらでもありますし 数式を変更すればいいだけですが どうもまだ
後付け条件が出てきそうなので しばらく様子を見ます。

お礼

追加回答ありがとうございます。
2003、2010、2013といくつかのバージョンがあったのですが2003で確認していました。
すみませんでした。

回答No.11

Chiquilin

#8です。

> 本当のデータでは日付が空白のこともあり、そうするとグループ化でき
> ないんですかね。
先にデータを埋めた状態でピボットテーブルを作成しておけばできます。
後から空白が増えても問題ありません。範囲は名前定義などで可変にして
おけばいいでしょう。

お礼

追加での回答ありがとうございます。
随時データを入力をしていく形です。
そのため、関数で準備しておくと完成までの時間が短縮されるのかと思いました。
当初、日付が不明だったところが後から分かったりするので、多少手間なんですかね。
特に自分がなれていないところが大きいかと思います。

回答No.10

kagakusuki

>まずC列ですが、これでは1900/1/0になってしまいます。
>そしてB列は、＃N/Aです。

　失礼しました。時間がない時に急いで投稿したため、重要な事を書き忘れておりました。
　御質問文では

シート1
　　Ａ　　　Ｂ　　　Ｃ
1　山田　　　　　4/4 3:00
2　佐藤　　　　　4/3 2:00
3　石井　　　　　4/4 3:00
4　加藤　　　　　4/3 2:00
5　田中　　　　　4/1 5:00

シート2
　　Ａ　　　Ｂ　　　Ｃ　　　Ｄ　　　Ｅ　　　Ｆ　　　Ｇ
1　　　　　　　　 　4/1　 　 4/2 　　 4/3 　　4/4　　　4/5
2　　1　　　田中　4/1 5:00
3　　2　　　佐藤　 　　　　　　　4/3 2:00
4　　3　　　加藤　 　　　　　　　4/3 2:00
5　　4　　　山田　　　　　　　　　　　　　4/4 3:00
6　　5　　　石井　　　　　　　　　　　　　4/4 3:00

となっているところを、次の様なレイアウトに変更して下さい。

シート1
　　Ａ　　　Ｂ　　　Ｃ
1　氏名　　　　　日時
2　山田　　　　　4/4 3:00
3　佐藤　　　　　4/3 2:00
4　石井　　　　　4/4 3:00
5　加藤　　　　　4/3 2:00
6　田中　　　　　4/1 5:00

シート2
　　Ａ　　　Ｂ　　　Ｃ　　　Ｄ　　　Ｅ　　　Ｆ　　　Ｇ
1　　No.　　氏名 　4/1　 　 4/2 　　 4/3 　　4/4　　　4/5
2　　1　　　田中　4/1 5:00
3　　2　　　佐藤　 　　　　　　　4/3 2:00
4　　3　　　加藤　 　　　　　　　4/3 2:00
5　　4　　　山田　　　　　　　　　　　　　4/4 3:00
6　　5　　　石井　　　　　　　　　　　　　4/4 3:00

お礼

追加での回答ありがとうございます。
B列は成功しましたが、C列～F列が1900/1/0となってしまいました。
ただ、今回知りたかったのがB列だったのでありがとうございました。

回答No.9

MackyNo1

>「＃NAME？」になってしまいます。

NAMEエラーは数式内に関数として判断できない名前が入っているためです。

シート名部分を含めてスペルミスがないか確認してください。

ちなみに、提示した数式そのもので、エラーなくご希望の表示ができることを確認しています(適宜シート名を参照してください）。

お礼

追加の回答ありがとうございます。
あらためて自分が投稿した内容をエクセルにコピーしました。
そして教えていただいた数式を入力しましたが、同じ状況になってしまいました・・・

シート名は初期設定のとおり「Sheet1」「Sheet2」となっています。
日付ですが、例えばSheet1のC1は「4/4 3:00」と表示させていますが、実際は「2015/4/4 3:00:00」と入力されていることも影響しているのでしょうか。
Sheet2の1行目を除いて、m/d h:mm（ユーザー定義）で表示しています。

回答No.8

Chiquilin

#3です。

> 日付にしたんですが、1行目が4/1、4/2、4/3、4/4、4/5ではなく、
> 4/1 5:00、4/3 2:00、4/4 3:00となってしまいました。
[日付]の[フィールドの設定]で [レイアウトと印刷]タブの[データのない
アイテムを表示させる]にチェックして[OK]

更に[日付]上で 右クリック[グループ化]
表示させる範囲を「日」単位だけにし [開始日]と[終了日]を入力して[OK]
最後に行ラベルフィルタで 必要な年月を[非表示]

お礼

追加での回答ありがとうございます。
本当はもっとデータが複雑で、それをすべて書くわけにはいかないので、今回は要点のみ質問しました。
本当のデータでは日付が空白のこともあり、そうするとグループ化できないんですかね。
使いこなすにはもっと慣れが必要のようです。

回答No.7

Chiquilin

数式以外の回答には返事もなさそうなんで

=IFERROR(INDEX(Sheet1!A:A,MOD(SMALL(INDEX(COUNTIF(Sheet1!C:C,"<"&Sheet1!C$1:C$5)+A$2:A$6/100,0),A2),1)*100),"")
Enter確定で下方向にコピー

ついでだから C2の数式も

=SUMIFS(Sheet1!$C:$C,Sheet1!$A:$A,$B2,Sheet1!$C:$C,">="&C$1,Sheet1!$C:$C,"<"&C$1+1)
表示形式を「m/d h:mm;;」に

=SUM(SUMIFS(Sheet1!$C:$C,Sheet1!$A:$A,$B2,Sheet1!$C:$C,">="&C$1+{0,1})*{1,-1})

でも。

お礼

回答ありがとうございます。
返信が遅れて申し訳ありません。

B列が＃NAME？になってしまいました。
そのためC列も＃NAME？です。

回答No.6

kagakusuki

下記の様にされては如何でしょうか?
シート2のA2セルの関数

=IF(ROWS($2:2)>COUNT(Sheet1!$C:$C),"",ROWS($2:2))

シート2のB2セルの関数

=IF($A2="","",INDEX(Sheet1!$A:$A,MATCH(SMALL(Sheet1!$C:$C,$A2),INDEX(Sheet1!$C:$C,MATCH(B1,Sheet1!$A:$A,0)*ISNUMBER(1/(SMALL(Sheet1!$C:$C,$A2)=SMALL(Sheet1!$C:$C,$A2-1)))+1):INDEX(Sheet1!$C:$C,MATCH(9E+307,Sheet1!$C:$C)),0)+MATCH(B1,Sheet1!$A:$A,0)*ISNUMBER(1/(SMALL(Sheet1!$C:$C,$A2)=SMALL(Sheet1!$C:$C,$A2-1))))&"")

シート2のC2セルの関数

=IF($A2="","",IF(INT(SMALL(Sheet1!$C:$C,$A2))=C$1,SMALL(Sheet1!$C:$C,$A2)-C$1,""))

お礼

回答ありがとうございます。
まずC列ですが、これでは1900/1/0になってしまいます。
そしてB列は、＃N/Aです。

回答No.5

bunjii

>Ｂ列にどのようにしたら良いか教えてください。
シート1のC列に対してRANK関数とROW関数で配列値を数式内に作成し、RANK関数の返り値の大きい順にROW関数を使って行番号を取り出します。
その返り値を使ってINDEX関数で氏名を抽出すれば良いでしょう。
B2=INDEX(Sheet1!A:A,MOD(SMALL(INDEX(RANK(Sheet1!C$1:C$5,Sheet1!C$1:C$5,1)+ROW(C$1:C$5)/100,0),ROW(B1)),1)*100)
提示のデータが5人分なのでそれに合わせた数式になっていますので、実際のデータ数に合わせて行番号と定数を変更してください。

お礼

回答ありがとうございます。
成功しました。
同じ値を区別するには、他の方も回答していますが、ROWを使うんですね。
ROW(C$1:C$5)、ROW(B1)と2箇所にROWを使用していますが、別にsheet1でＲＯＷしなくていいんですね。
勉強になりました。ありがとうございました。

回答No.4

MackyNo1

>ただ、エラーというか、0になってしまいます。

よく見たら、日時が入力されているのはB1:B10ではなくC1:C10セルなのですね。

その場合はセル番地のBをCに変更してください。

お礼

追加での返信ありがとうございます。
=IFERROR(INDEX(A:A,MATCH(SMALL(INDEX($C$1:$C$10+ROW($C$1:$C$10)/10000+($C$1:$C$10="")*100000,),ROW(1:1)),INDEX($C$1:$C$10+ROW($C$1:$C$10)/10000,),0)),"")
に変更ということでよかったでしょうか。
「＃NAME？」になってしまいます。

A:Aを「Sheet1!A:A」の間違いかと思って勝手に訂正しましたが、それでも同じ結果でした。