Excelで以下のようなIDごとに日付が入った元データがあるのですが、
そのデータを元にその下のような表を作りたいと思っています。
| ID | 氏名 | 日付 |
| 11 | ○○○ | 20110105 |
| 11 | ○○○ | 20110208 |
| 11 | ○○○ | 20110812 |
| 22 | △△△ | 20110319 |
| 22 | △△△ | 20110525 |
| 22 | △△△ | 20110914 |
| 22 | △△△ | 20111012 |
| 22 | △△△ | 20111225 |
| 33 | □□□ | 20110629 |
| 33 | □□□ | 20110820 |
| 33 | □□□ | 20111223 |
| 33 | □□□ | 20111227 |
| ID | 氏名 | 日付(1) | 日付(2) | 日付(3) | 日付(4) | 日付(5) |
| 11 | ○○○ | 20110105 | 20110208 | 20110812 | | |
| 22 | △△△ | 20110319 | 20110525 | 20110914 | 20111012 | 20111225 |
| 33 | □□□ | 20110629 | 20110820 | 20111223 | 20111227 | |
要するに一人一行で横へ日付を並べたいだけなのですが、
色々考えてみたものの行き詰っています。
IDを元に氏名はVLOOKUP関数で何とか出来たのですが、
日付部分にどのような関数を組んだらいいのか分かりません。
どなたか分かる方教えて頂けないでしょうか。
よろしくお願いします。
ちなみにExcelは2000を使っています。
No.6ベストアンサー
- 回答日時:
添付図参照
F2: =INDEX(B:B,MATCH($E2,$A:$A,0))
G2: =IF(COLUMN(A1)>COUNTIF($A:$A,$E2),"",INDEX($C:$C,COLUMN(A1)+MATCH($E2,$A:$A,0)-1))
この回答への補足
回答ありがとうございました。
おかげさまで簡単に思っていた通りの表を作ることができました。
ただ、COLUMN関数やINDEX関数に慣れておらず初めて使ったので、
もしよろしければNo.様、関数の意味を教えて頂けないでしょうか?
まず、最初の判定の条件で何故COLUMN関数を使っているのでしょうか?
そして後ろのINDEX関数は「INDEX(範囲,行位置,列位置 [,領域番号])」
となると思うのですが、今回の場合は列位置がない状態に思えます。
領域番号だけでなく、列位置も省略できるものなんでしょうか?
基本的なことかもしれませんが、もしよろしければ教えて下さい。
初心者ですみません。
No.9
- 回答日時:
[No.6この回答への補足]へのコメント、
》 最初の判定の条件で何故COLUMN関数を使っているのでしょうか?
当該式を入力したセル G2 中の COLUMN(A1) は 1 を返すことはお分かりでしょうか?
この式を右隣にドラッグ&ペーストすると COLUMN(A1) だったところは COLUMN(B1) に変化します。 COLUMN(B1) は 2 ですね。
セル I2 では COLUMN(C1) となって 3 になります。
つまり、右へ右へで 1、2、3、… なる連続数値を得るために「COLUMN関数を使っている」のです。
》 列位置も省略できるものなんでしょうか?
良い質問ですねぇ!
=INDEX(array,row_num,column_num)
上式で column_num が 1(1列)の場合は
=INDEX(array,row_num,1)
でも
=INDEX(array,row_num,)
でも
=INDEX(array,row_num)
でもOKです。
なるほど!!
特に意味があるってわけでもないんですね。
要は使いよう、アイデアってことですか!
ん~納得!すごい!これはテクニックですね。
ぜひこれから使って行きたいと思います。
INDEX関数そういうことだったんですね。
調べはしたものの、そこまで分かってませんでした。
これも理由が分かってスッキリしました。
解説までして頂いて、ほんとにありがとうございました。
おかげさまで仕事もはかどり、周りからすごいと言われました。
私の力じゃないけど…(^^;
No.8
- 回答日時:
>思っていたよりややこしくなるんですね(^^;
そう感じるのは当然でしょう。
数式なんてのは
○○の結果でXXして、さらに△△して□□して・・・と
途中何が起きているか見えないので、
それを整理できないと、馴れるまではいざ一気に(1行で)は書けないもの。
作業用D列を用意して
| ID | 氏名 | 日付 |順
| 11 | ○○○ | 20110105 |1
| 11 | ○○○ | 20110208 |2
| 11 | ○○○ | 20110812 |3
| 22 | △△△ | 20110319 |1
| 22 | △△△ | 20110525 |2
これがあるだけでも見方が変わります。
それに、
回答は、
結果が出るだけで理屈までは説明がないのがほとんど・・・。
使われている関数を復習して調べるまでしないと力になりません。
No.7
- 回答日時:
>配列数式…初めて聞きました(^^;
配列数式の概要は、以下のページを参考にしてください。
http://pc.nikkeibp.co.jp/pc21/special/hr/
配列数式は、複雑な計算が補助列なしに計算ができ、数式を簡略化するには極めて有効な手段ですが、オートフィルコピーなどで配列数式のセルが多くなると(例えば数式セルが100を超えるような場合)、再計算に時間がかかり、シートの動きが重くなることもありますので、ケースバイケースで使用されるとよいと思います。
ちなみに、今回提示した数式はCtrl+Shift+Enterで確定しない数式もありますが、いずれも配列数式です。
なるほど!配列数式…恐るべしです。便利!!
私はいつも補助列使いまくりです。
私のような初心者にはまだまだ慣れが必要そうですね。
これを機会に配列数式やVBAを勉強したいと思います。
ありがとうございました。
No.5
- 回答日時:
データはSheet1のABC列にあるものとします。
Sheet2に転記するVBAを組んでみました。
手順は以下のとおりです。簡単ですよ。
1.エクセルの画面から、AltキーとF11キー同時に押し(以下Alt+F11キーと記述)て Visual Basic Editor を呼び出します。
2.Visual Basic Editor のメニューから「挿入」、「標準モジュール」で出てきたコードウィンド(右側の白い広い部分)に以下のコード(Sub~End Sub)をコピペします。
'********これより下**********
Sub test01()
Dim myRng As Range, myC As Range
Dim myDic As Object
Dim myV
Dim i As Long
Set myDic = CreateObject("Scripting.Dictionary")
With Sheets("Sheet1")
Set myRng = .Range("A1", .Cells(Rows.Count, "A").End(xlUp))
For Each myC In myRng
If Not myDic.Exists(myC.Value) Then
myDic.Add myC.Value, myC.Offset(, 1).Value
End If
Next
ReDim myV(1 To myDic.Count, 1 To 3)
For i = 1 To myDic.Count
myV(i, 1) = myDic.Keys()(i - 1)
myV(i, 2) = myDic.Items()(i - 1)
Next i
myDic.RemoveAll
For Each myC In myRng
If Not myDic.Exists(myC.Value) Then
myDic.Add myC.Value, CStr(myC.Offset(, 2).Value)
Else
myDic(myC.Value) = myDic(myC.Value) & "@" & CStr(myC.Offset(, 2).Value)
End If
Next
For i = 1 To myDic.Count
myV(i, 3) = myDic.Items()(i - 1)
Next i
End With
With Sheets("Sheet2")
.Range("A1").Resize(myDic.Count, 3).Value = myV
.Columns("C:C").TextToColumns Destination:=Range("C1"), Other:=True, OtherChar:="@"
End With
Set myDic = Nothing
Set myRng = Nothing
End Sub
'********これより上**********
3.Alt+F11キーでワークシートへもどります。
4.Alt+F8キーで出てきたマクロ名(test01)を選択して実行します。
これでSheet2にご希望の形で展開されたと思います。
わざわざプログラムを書いて頂いてありがとうございます。
VBAは使ったことないのでなんか難しそうです。(^^;
出来るかな…。
でもこれってデータ内容が変わった時もそのまま使えるんでしょうか?
No.4
- 回答日時:
No3の回答の補足です。
もし、Sheet2のリストから重複のないID番号を自動取得したいなら(データを変更すると自動的にその番号だけ表示される)、A列のセルに以下のような関数を入力して下方向にオートフィルしてください。
=INDEX(Sheet2!B:B,SMALL(INDEX((MATCH(Sheet2!$A$2:$A$200&"",Sheet2!$A$2:$A$200&"",)<>ROW($A$2:$A$200)-1)*1000+ROW($A$2:$A$200),),ROW(A1)))&""
なお、上記のROW($A$2:$A$200)-1)の「200」の入力されている部分はデータ範囲よりも少し大きめに範囲指定して、「-1」の部分はデータの開始行によって適宜調節してください(2行目からなら2-1で「1」)。
また表示シートのC列の数式は、例えば「=IF(A2="","",元の式)」のような数式にすると良いかもしれません。
No.3
- 回答日時:
元データがSheet2にあり、日付が8桁の数字で入力されているなら、直接C列を参照する以下のような配列数式で表示できます。
=IF(COUNTIF(Sheet2!$A:$A,$A2)>COLUMN(A1),SMALL(IF(Sheet2!$A$2:$A$200=$A2,Sheet2!$C$2:$C$200,""),COLUMN(A1)),"")
上記の式はCtrl+Shift+Enterする必要がありますが、以下のような数式にすれば、そのまま入力するだけで表示できます・
=IF(COUNTIF(Sheet2!$A:$A,$A2)>COLUMN(A1),SMALL(INDEX((Sheet2!$A$2:$A$200<>$A2)*10^9+Sheet2!$C$2:$C$200,),COLUMN(A1)),"")
日付の数字を文字列で表示してもよいなら以下のような数式が簡単です。
=INDEX(Sheet2!$C:$C,SMALL(INDEX((Sheet2!$A$2:$A$200<>$A2)*1000+ROW($A$2:$A$200),),COLUMN(A1)))&""
配列数式…初めて聞きました(^^;
Excelはそこそこに使ってきてますが、全然知りませんでした。
そういうものがあるんですね。
どういった時に使えるものなんでしょう?
いまいちどういうものか理解できませんが…。
書いて頂いた数式はNo.1の方のと似ていますね。
試してみたいと思います。
ありがとうございました。
No.1
- 回答日時:
一例です。
仮にSheet1に元表、Sheet2のC2に以下の数式を入力(入力完了時にshift+ctrl+enterキーを同時押下)、縦横に必要分コピー
=IF(COUNTIF(Sheet1!$A:$A,$A2)>=COLUMN(A1),INDEX(Sheet1!$C:$C,SMALL(IF(Sheet1!$A$1:$A$100=$A2,ROW(Sheet1!$A$1:$A$100),9999),COLUMN(A1))),"")
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Access(アクセス) アクセス where句を使用して複数条件抽出をするには 2 2022/08/29 13:24
- Excel(エクセル) DATE関数で指定する「日」のセルが関数の場合の対処法 5 2022/09/14 15:46
- Excel(エクセル) Excelで日付のグラフへの表示 2 2022/04/15 11:04
- Visual Basic(VBA) Excel VBAのリストボックスの値を他のフォームに反映させる方法を教えてください。 2 2023/07/14 14:06
- Excel(エクセル) Excelの並び替え(先頭の文字以外を基準に並び替えたい) 3 2023/07/07 22:21
- その他(データベース) Accessフォームからパラメーターで表示したレコードを指定のExcelのセルへ転送する方法について 2 2022/08/22 18:04
- Visual Basic(VBA) 【困っています2】VBA 追加処理の記述を教えてください。 2 2022/08/26 11:42
- Excel(エクセル) Excel教えてください。 下記のことが出来るのは、マクロですか?条件付き書式でしょうか、、?知恵を 5 2022/11/12 09:33
- Excel(エクセル) Excelでのデータ管理 6 2022/12/24 09:33
- Excel(エクセル) Excelで教えてください。 入力順に日付と名前が記載されてる一覧表で、同じ日付けの1番下の行(その 4 2023/08/03 09:14
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
英数字のみ全角から半角に変換
-
teams設定教えて下さい。 ①ビデ...
-
Microsoft 365 の一般法人向け...
-
outlookのメールが固まってしま...
-
Microsoft Formsの「個人情報や...
-
会社PCのメールが更新されない
-
会社におけるOfficeライセンス...
-
Outlook 電源OFFの受診の仕方
-
大学のレポート A4で1枚レポー...
-
MicrosoftOfficeについて質問で...
-
office365って抵抗感ないですか?
-
ドメイン購入をしたので同時に...
-
office2016のパソコン2台インス...
-
マイクロソフト 一時使用コード...
-
エクセルでXLOOKUP関数...
-
Excelのセルの重複チェックが出...
-
outlookで宛先が異なるメールを...
-
エクセルで英文字に入れた下線...
-
エクセル 表の移動
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
【スプレッドシート】指定の日...
-
英数字のみ全角から半角に変換
-
会社PCのメールが更新されない
-
マイクロソフト 一時使用コード...
-
Office 2021 Professional Plus...
-
エクセルで例えば、関数を使っ...
-
Microsoft Formsの「個人情報や...
-
1つのPCに「Excel 2010」「Exc...
-
エクセルで例えば、A1に㈱ベ...
-
理由を教えてください。
-
エクセルでXLOOKUP関数...
-
マイクロソフト オフィスについて
-
VLOOKUP関数について
-
teams設定教えて下さい。 ①ビデ...
-
Googleのスプレッドシートでシ...
-
【Excel VBA】PDFを作成して,...
-
Microsoft365で写真をアルバム...
-
Outlook で宛先が複数の場合の人数
-
Excel テーブル内の空白行の削除
おすすめ情報