エクセルで項目ごとに集計して行列を入れ替える方法を教えてください。 A列に項目、B列に詳細が複数ある

Question

エクセルで項目ごとに集計して行列を入れ替える方法を教えてください。

A列に項目、B列に詳細が複数あるようなエクセル（添付）
1項目1行になるように集計してB列の行列を入れ替えるには
どうするといいでしょうか。

動物|ゴリラ|さる|ライオン|犬
昆虫|クワガタ|カブトムシ|コオロギ
花|朝顔|チューリップ|タンポポ

このようにしたいです。

mike_g · Accepted Answer

[No.3]ですが、
Ｃ列は、「Alt+AM(データの重複削除)で手輕に行かうヨ」を想定して居たけど、此処も關數に拘泥するなら、
次式を入力したセル C1 を下方にオートフィルすればＯＫかと。
=IFERROR(INDEX(A$1:A$10,SMALL(IF(O$1:O$10=1,ROW(O$1:O$10)),ROW(A1))),"")
【御斷はり】上式は必ず配列數式として入力の事

序で乍ら、出來るだけ動作を“輕快”にする爲に、先に擧げたセル C1 の式も下記にマイナーチェンジして措きませう。
=IF(COLUMN(A1)>COUNTIF($A$1:$A$10,$C1),"",OFFSET($B$1,MATCH($C1,$A$1:$A$10,0)-1+COLUMN(A1)-1,))

goomania · Answer

前提として行のデータとしてみた場合、全く同じ行は無い(データの重複がない。ただし、種類が異なる場合、名称が同じことはある)という前提であれば、列の追加とピボットテーブルを使って、ご質問者の望む表を作成できると思います。
添付画像では、あえて、元データに「クロヅル」という動物にも花にも存在する名称を加えた表にしてあります。
(1)添付画像のように、元データの表の右側に「列見出し」という列を追加し、C2には
="名称"&COUNTIF($A$2:A2,A2)
という数式を記述して下方向へコピーします。
(2)さらに「番号」という列を追加し、E2には
=ROW()
という数式を記述して下方向へコピーします。添付画像①
(3)このデータ全体を対象範囲として、ピボットテーブルを作成します。添付画像の例でいえば、「種別」を行エリア、「列見出し」を列エリア、「番号」を値エリアへプロットします。(表形式とし、総計・小計は表示しないものとします)
添付画像②
(4)元データがsheet1にあり、ピボットテーブルがsheet2に作成されたとして、希望の表を作成したい位置の左上のセルに（別シートでも可）に以下のような数式を記述し、右方向、下方向へピボットテーブルで作された表の範囲でコピーします。
=IF(A4="","",IFERROR(INDEX(Sheet1!$B$2:$B$13,MATCH(A4,Sheet1!$D$2:$D$13,0),1),A4))
ご希望の表が完成したと思います。添付画像③
ピボットテーブルの「種別」の表示順は種別表示の右の「▼」をクリックして昇順・降順を指定できます。(添付画像では元データと順番が逆になっています)

aouze_ya · Answer

＞　C列に数は入ったのですが、E3に教えていただいた関数を入れても空欄のままです。。なにが悪いのでしょうか。

想像すると、　名前定義をしてないのではないでしょうか。
https://www.tipsfound.com/excel/01406
http://www.eurus.dti.ne.jp/~yoneyama/Excel/name.htm
名前定義をしてあります。
　A列、B列、C列の3列に、1行目から100行目を定義しました。

｢区分｣という名前の定義を、｢A1:A100｣の範囲に設定する
｢名称｣という名前の定義を、｢B1:B100｣の範囲に設定する
｢順位｣という名前の定義を、｢C1:C100｣の範囲に設定する

tom04 · Answer

No.2です。

マクロではなく、関数での方法をご希望だというコトなので・・・

↓の画像のように元データはSheet1にあり、Sheet2に表示するとします。
Sheet1に作業用の列を設けてみました。

作業列C2セルに
=IF(COUNTIF(A$2:A2,A2)=1,ROW(),"")

という数式を入れフィルハンドルでこれ以上データはない！という位まで下へコピーしておきます。

これはA列データを重複なしにSheet2に表示するためです。

Sheet2のA1セルに
=IFERROR(INDEX(Sheet1!A:A,SMALL(Sheet1!C:C,ROW(A1))),"")

として下へフィル＆コピー！

B1セルに
=IFERROR(INDEX(Sheet1!$B$1:$B$1000,SMALL(IF(Sheet1!$A$1:$A$1000=$A1,ROW($A$1:$A$1000)),COLUMN(A1)))&"","")

配列数式なので、Ctrl＋Shift＋Enterで確定！（←必須★）し
フィルハンドルで列・行方向にコピーすると
画像のような感じになります。

※　作業列が目障りであれば、遠き離れた列にするか
非表示にしてみてください。m(_ _)m

aouze_ya · Answer

なんとなく、マクロでやりたくなるし、　　ですが、

EXCELって、｢作業領域をバンバン使って、力で押し込んで、とにかく、結果を出す｣という、ズブ素人の方法もあるのではないかと、思います。

これが良いとは思いませんが、マクロを考えるよりも、関数を入れておけば、データを追加、変更しても、自動反映されるし、わかりやすい場合もあると思います。

名前定義をしてあります。
　A列、B列、C列の3列に、1行目から100行目を定義しました。

都合があって、データの実質開始行は2行目以降になりますが、画像の例は3行目からがデータの行です。　C列のデータ開始行の直上（画像ではC2)に"0"を入れます。

C3に　=IF(A2<>A3,LARGE(C$2:C2,1)+1,"")　と入れて、
C3を下にオートフィルします。

結果を得たい場所を決めます。（画像例は、E3からの例、E20からの例の２例）
E3に　=IFERROR(INDEX(区分,MATCH(ROW()-2,順位,0)),"")
F3に　=IF(E3="","",COUNTIF(区分,E3))
E20に　=IFERROR(INDEX(区分,MATCH(ROW()-19,順位,0)),"")
F20に　=IF(E3="","",COUNTIF(区分,E3))
E3:F3を下にオートフィル　　E20:F20を下にオートフィル
これで、区分と区分に入るべき名称の個数のリストが出来ます。

G3に　
=IF($F3<>"",IF(COLUMN()-7<$F3,INDEX(名称,MATCH($E3,区分,0)+COLUMN()-7),""),"")
G20に
=IFERROR(IF(COLUMN()-7<$F20,OFFSET(INDEX(名称,MATCH($E20,区分,0)),COLUMN()-7,0),""),"")

G3を右に適当列までオートフィルし、また下に適当行までオートフィル
G20を右に適当列までオートフィルし、また下に適当行までオートフィル

mike_g · Answer

添附圖參照(Excel 2019)
D1: =IF(COLUMN(A1)>COUNTIF($A:$A,$C1),"",OFFSET($B$1,MATCH($C1,$A:$A,0)-1+COLUMN(A1)-1,))

tom04 · Answer

こんにちは！

手っ取り早くVBAでの一例です。
元データはSheet1にあり、Sheet2に表示するとします。
標準モジュールにしてください。

Sub Sample()
　Dim myDic As Object
　Dim i As Long, k As Long
　Dim myStr As String
　Dim myKey, myItem, myAry
 
　　Set myDic = CreateObject("Scripting.Dictionary")
　　　With Worksheets("Sheet1")
　　　　For i = 1 To .Cells(Rows.Count, "A").End(xlUp).Row
　　　　　myStr = .Cells(i, "A")
　　　　　If Not myDic.exists(myStr) Then
　　　　　　myDic.Add myStr, .Cells(i, "B")
　　　　　Else
　　　　　　myDic(myStr) = myDic(myStr) & "_" & .Cells(i, "B")
　　　　　End If
　　　　Next i
　　　End With
    　
　　　　myKey = myDic.keys
　　　　myItem = myDic.items
   
　　　With Worksheets("Sheet2")
　　　　.Cells.ClearContents
　　　　　For i = 0 To UBound(myKey)
　　　　　　.Cells(i + 1, "A") = myKey(i)
　　　　　　　myAry = Split(myItem(i), "_")
　　　　　　　　For k = 0 To UBound(myAry)
　　　　　　　　　.Cells(i + 1, k + 2) = myAry(k)
　　　　　　　　Next k
　　　　　Next i
　　　　　　.Activate
　　　End With
     
　　　　Set myDic = Nothing
　　　　MsgBox "完了"
End Sub

こんな感じではどうでしょうか？m(_ _)m

fujillin · Answer

こんにちは

処理するのが一度だけなら、手作業で。
何度も行うのなら、マクロか関数利用で。

◇手作業の方法
・Ａ列をキーにして、並べ替え（ソート）
　（すでにソートされていれば必要ありません）
・ご提示の図の例で、B1:B5をコピー
・別シートのＢ1セルに「形式を選択してペースト」で「行列を入れ替える」にチェックを入れてペースト

これで、1行目に横並びになりますので、項目数だけ繰り返します。

◇関数による方法
FILTERやUNIQUE関数が使用できる環境の場合はそちらを利用した方が簡単です。
私の環境では使えないので、以下、ローテクな方法です（添付図参照）

・添付図では、D1セルに
=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(A$1:A$99)/(COUNTIF(OFFSET(A$1,,,ROW(A$1:A$99)),A$1:A$99)=1),ROW(A1))),"")
の式を入れて、下方にフィルコピー
・E1セルに
=IF($D1="","",IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($A$1:$A$99)/($A$1:$A$99=$D1),COLUMN(A1))),""))
の式を入れて、右方、下方にフィルコピーしてあります。
・関数の結果を固定値にしたければ、全体を選択して「コピー」→「値をペースト」で固定値化できます