こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

締切り済みの質問

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

シート1に以下のように、A列には氏名、C列に日付がランダムに入力されています。
  A   B   C
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に上から順位に並び替えたいです
  A   B   C   D   E   F   G
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行目のC~Gには既に4/1~4/5が入力されています。
対応するところに日付を入力し、さらにB列には氏名を表示したいです。

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も同様)。

問題はB列なのですが、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))」としても同じ失敗結果になりました。

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

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

投稿日時 - 2015-07-14 01:26:22

QNo.9011924

すぐに回答ほしいです

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(13)

ANo.13

 回答No.6、10です。

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

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

13:30

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

h:mm

にして頂ければ、

Sheet2
   A   B   C   D   E     F    G
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

として下さい。

投稿日時 - 2015-07-22 00:42:54

お礼

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

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

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

投稿日時 - 2015-07-29 00:08:52

ANo.12

#7です。

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

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

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

投稿日時 - 2015-07-17 10:06:19

お礼

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

投稿日時 - 2015-07-21 22:31:14

ANo.11

#8です。

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

投稿日時 - 2015-07-17 10:01:35

お礼

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

投稿日時 - 2015-07-21 22:05:39

ANo.10

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

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

シート1
  A   B   C
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
  A   B   C   D   E   F   G
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
  A   B   C
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
  A   B   C   D   E   F   G
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

投稿日時 - 2015-07-17 03:57:17

お礼

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

投稿日時 - 2015-07-21 22:25:41

ANo.9

>「#NAME?」になってしまいます。

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

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

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

投稿日時 - 2015-07-16 17:24:40

お礼

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

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

投稿日時 - 2015-07-17 03:59:57

ANo.8

#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]
最後に行ラベルフィルタで 必要な年月を[非表示]

投稿日時 - 2015-07-16 08:05:28

お礼

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

投稿日時 - 2015-07-17 04:56:14

ANo.7

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

=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})

でも。

投稿日時 - 2015-07-15 10:22:48

お礼

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

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

投稿日時 - 2015-07-17 04:10:38

ANo.6

 下記の様にされては如何でしょうか?
シート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,""))

投稿日時 - 2015-07-14 13:29:13

お礼

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

投稿日時 - 2015-07-17 03:27:48

ANo.5

>B列にどのようにしたら良いか教えてください。
シート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人分なのでそれに合わせた数式になっていますので、実際のデータ数に合わせて行番号と定数を変更してください。

大変申し訳ございませんが、この投稿に添付された画像や動画などは、「BIGLOBEなんでも相談室」ではご覧いただくことができません。 OKWAVEよりご覧ください。

マルチメディア機能とは?

投稿日時 - 2015-07-14 12:53:08

お礼

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

投稿日時 - 2015-07-16 07:50:39

ANo.4

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

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

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

投稿日時 - 2015-07-14 12:37:54

お礼

追加での返信ありがとうございます。
=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」の間違いかと思って勝手に訂正しましたが、それでも同じ結果でした。

投稿日時 - 2015-07-16 07:45:23

ANo.3

何故 ピボットテーブルを使わないのですか?

行フィールド:氏名
列フィールド:日付
集計フィールド:日付(集計方法:最大値/[表示形式]も設定)

行フィールドの[並べ替え]設定→[その他の並べ替えオプション]
[昇順]の[最大値 / 日付]で[OK]

以上。

投稿日時 - 2015-07-14 10:53:43

お礼

回答ありがとうございます。
ピボットテーブルは、あまり使用していなかったもので・・・
列フィールド:日付にしたんですが、1行目が4/1、4/2、4/3、4/4、4/5ではなく、4/1 5:00、4/3 2:00、4/4 3:00となってしまいました。

投稿日時 - 2015-07-16 00:01:24

ANo.2

1.Sheet1 の右側の空き列を利用して、セル E1 に式 =C1+ROW(A1)/10^10 を入力して、此れを下方にズズーッとドラッグ&ペースト

以下は Sheet2 における作業です。
1.セル B2 に式 =INDEX(Sheet1!A:A,MATCH(SMALL(Sheet1!E:E,ROW(A1)),Sheet1!E:E,0)) を入力
2.h:mm に書式設定したセル C2 に次式を入力して、此れを右方にズズーッとドラッグ&ペースト
__=IF(INT(VLOOKUP($B2,Sheet1!$A:$C,3,FALSE))=C$1,VLOOKUP($B2,Sheet1!$A:$C,3,FALSE),"")
3.範囲 B2:G2 を下方にズズーッとドラッグ&ペースト

大変申し訳ございませんが、この投稿に添付された画像や動画などは、「BIGLOBEなんでも相談室」ではご覧いただくことができません。 OKWAVEよりご覧ください。

マルチメディア機能とは?

投稿日時 - 2015-07-14 10:33:27

お礼

回答ありがとうございます。
デメリットをあげるとしたら、シート1のE列に作業が必要ということでしょうか。
できたら、他の人も使用するエクセルなので、余計なデータが無い方が助かります。

投稿日時 - 2015-07-15 22:46:30

ANo.1

あまり数式を吟味せずに作成しましたので、もう少し簡略化できると思いますが、ひとまず以下の式をコピー貼り付けして下方向にオートフィルしてみてください(10行目までのデータの場合)。

=IFERROR(INDEX(A:A,MATCH(SMALL(INDEX($B$1:$B$10+ROW($B$1:$B$10)/10000+($B$1:$B$10="")*100000,),ROW(1:1)),INDEX($B$1:$B$10+ROW($B$1:$B$10)/10000,),0)),"")

投稿日時 - 2015-07-14 02:00:19

お礼

早速の回答ありがとございます。
ただ、エラーというか、0になってしまいます。

投稿日時 - 2015-07-14 07:32:28

あなたにオススメの質問