人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

エクセルの質問です。

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

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

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

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

●質問者: saetel
●カテゴリ:ビジネス・経営 コンピュータ
✍キーワード:RED YELLOW エクセル データ 代々木
○ 状態 :終了
└ 回答数 : 4/4件

▽最新の回答へ

1 ● koriki-kozou
●23ポイント

関数を自分で作るしかないけど今回限りなら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,

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


2 ● SALINGER
●23ポイント

これは一番簡単なのは、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

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

◎質問者からの返答

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


3 ● koriki-kozou
●22ポイント

マクロを作ってみた

動かす前に 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万行超えるので限界まで使うことはないだろう)までデータが詰まっている場合は最後の行でエラーを起こすはず(そこまではテストしてない)


4 ● yamasenman
●22ポイント

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

◎質問者からの返答

2003です。

関連質問


●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ