エクセルの質問です。


新宿   RED
新宿   BLACK
代々木  RED
代々木  YELLOW
代々木  BLACK
代々木  BROWN
渋谷   WHITE

こういった状態のデータを

一発で
新宿   RED BLACK
代々木  RED YELLOW BLACK BROWN
渋谷   WHITE

こういったエクセルデータにするには、
関数を使うと思うのですが、どうしたら良いでしょうか?

回答の条件
  • 1人2回まで
  • 13歳以上
  • 登録:2010/06/14 18:12:51
  • 終了:2010/06/21 18:15:03

回答(4件)

id:koriki-kozou No.1

koriki-kozou回答回数480ベストアンサー獲得回数792010/06/14 18:43:52

ポイント23pt

関数を自分で作るしかないけど今回限りならExcelの機能だけでもできるよ(誰も回答しなかったら深夜にでもVBAでも作ってみる)

(1)ダミーデータ準備

都市 個数
新宿 RED 1
新宿 BLACK 1
代々木 RED 1
代々木 YELLOW 1
代々木 BLACK 1
代々木 BROWN 1
渋谷 WHITE 1

(2)ピボットテーブルを作る(別シートに作ってみた)

A B C D E F G
1 合計 : 個数
2 都市 BLACK BROWN RED WHITE YELLOW 総計
3 渋谷       1   1
4 新宿 1   1     2
5 代々木 1 1 1   1 4
6 総計 2 1 2 1 1 7

(3)集計(ピボットの下に作ったけど、新しいシートでもかまわない)

A B C D E F G
8 =A3 =IF(B3>0,B$2&",","")         =CONCATENATE(B8,C8,D8,E8,F8)

A8とG8は下方向に、B8は右および下方向にコピー

(4)結果

A B C D E F G
8 渋谷       WHITE,   WHITE,
9 新宿 BLACK,   RED,     BLACK,RED,
10 代々木 BLACK, BROWN, RED,   YELLOW, BLACK,BROWN,RED,YELLOW,

まだ少し余計なカンマが残ってるけど、とりあえず使えるはず

id:SALINGER No.2

SALINGER回答回数3454ベストアンサー獲得回数9692010/06/14 19:32:53

ポイント23pt

これは一番簡単なのは、RED、BLACKなどを選択して、行列を入れ替えて貼り付けです。

マクロも簡単にできるでしょう。


ここでは関数の方法を紹介します。

次のような表だとします。

A B
1 新宿 RED
2 新宿 BLACK
3 代々木 RED
4 代々木 YELLOW
5 代々木 BLACK
6 代々木 BROWN
7 渋谷 WHITE

場所はどこでもいいのですが、次のようにD列から集計表とすると

D E F G H
1 新宿 数式
2 代々木
3 渋谷

数式のところに次の数式をコピペして、Ctrl+Shift+Enterを押して配列数式にしてください。

=IF(ISERROR(INDEX($B$1:$B$7,SMALL(IF($A$1:$A$7=$D1,ROW($A$1:$A$7),99),COLUMN()-4))),"",INDEX($B$1:$B$7,SMALL(IF($A$1:$A$7=$D1,ROW($A$1:$A$7),99),COLUMN()-4)))

後はその数式を入れたセルを縦横にコピーします。

数式では集計表の項目はD列としたところが、2箇所ある$D1です。

また式中の2箇所ある-4はD列が4列目なので-4です。(E列なら-5です)

式中の99は最大行よりも大きな数字を指定します。


この結果を一つのセルに表示するならば

=E1& " " & F1& " " &G1& " " &H1

のように連結してください。

id:saetel

最高です。ありがとうございます。

2010/06/16 14:43:36
id:koriki-kozou No.3

koriki-kozou回答回数480ベストアンサー獲得回数792010/06/15 00:32:12

ポイント22pt

マクロを作ってみた

動かす前に Sheet3 と A1:B7 は実際のデータの在り処にあわせる必要あり

その他の改造用にコメント盛りだくさんサービスね

Sub Macro1()
    ' 準備
    Dim vRange As Range: '対象範囲
    Dim vTopRow As Long: '開始行
    Dim vLastRow As Long: '最終行
    Dim vCity As String: ' 都市
    Dim vColor As String: ' 色
    Dim vRow As Long: '作業中の行
    
    '対象に応じて書き換えが必要(セレクションをセットするに改造してもいいけど)
    Set vRange = Sheets("Sheet3").Range("A1:B7"): ' 対象範囲を書く("A:B"などの指定でもいい)
    
    'プログラムによるソートが必要ならここに追加すればいい
    'マクロでソートすると戻るボタンで戻せないから、今回は入れてない
    '必要ならばマクロ記録でソートを記録したものをここに追加すればいい
    
    '既存シートへの書き込みはレイアウトを崩すかもしれないので、シートを自動的に追加する方法を採用
    Sheets.Add: 'シート追加
    Range("A1").Select: '追加したシートのA1をアクティブ
        
    '読み取りと書き込み
    vTopRow = vRange.Row + 1: '開始行取得(ループ時の計算軽減のため+1しておく)
    vLastRow = vRange.End(xlDown).Row + 1: '最終行を取得(ループ時の計算軽減のため+1しておく)
    vCity = vRange.Cells(vTopRow - 1, 1): '左列一行目
    vColor = vRange.Cells(vTopRow - 1, 2): '右列一行目
    For vRow = vTopRow To vLastRow
        '残りの行数分ループ
        If vRange.Cells(vRow, 1) = vCity Then
            '一つ上と同じ場合の処理
            vColor = vColor & "," & vRange.Cells(vRow, 2): '右列読み取りって追加(分解用にカンマで結合)
        Else
            '一つ上と異なる場合の処理
            ActiveCell.Value = vCity: '左列書き込み
            ActiveCell.Offset(0, 1).Value = vColor: '右列書き込み
            ActiveCell.Offset(1, 0).Select: '次の行に移動
            vCity = vRange.Cells(vRow, 1): '左列読み取り
            vColor = vRange.Cells(vRow, 2): '右列読み取り
        End If
    Next vRow
    
    'カンマでセルに分解(不要なら削ってOK)
    Columns("B:B").Select
    Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1))

    '別になくてもいいけど、見ばえの問題で一応入れとく
    Range("A1").Select
End Sub

問題点:Excelの限界行数(Excel2003までなら65536行、Excel2007は100万行超えるので限界まで使うことはないだろう)までデータが詰まっている場合は最後の行でエラーを起こすはず(そこまではテストしてない)

id:yamasenman No.4

yamasenman回答回数23ベストアンサー獲得回数02010/06/14 19:40:52

ポイント22pt

Excelのバージョンは2003? 2007?

id:saetel

2003です。

2010/06/16 12:52:30

コメントはまだありません

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません