Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Pro : その他)
横に並んだ値を縦に並べたい
投稿日時: 21/08/27 09:51:27
投稿者: ラングドシャ

たびたびの投稿で申し訳ありません。 Excel2019です。
よろしくお願いいたします。
 
元データとして、このようなデータがあります。

	A	B	C	D	E	F
1	品番	1	2	3	4	5
2	10-aaa	赤				
3	10-aab					
4	20-11a	赤	青			
5	30-22b					
6	40-aaa	黄	赤	青	黒	緑

 
これを
 
	A	B
1	品番	色
2	10-aaa	赤
3	20-11a	赤
4	20-11a	青
5	40-aaa	黄
6	40-aaa	赤
7	40-aaa	青
8	40-aaa	黒
9	40-aaa	緑

 
この表のように並べたいのですが、どのようにするのが良いでしょうか?
下の表は、現在何もデータがなく元データから品番・色ともに抽出したいです。
・色が複数ある場合は品番を複数行にする
・色がない場合は除く
 
色が複数ある場合には品番を複数行にするというところでどうにも躓いてしまいます。
エクセルで可能でしょうか? よろしくお願いいたします。

回答
投稿日時: 21/08/27 10:25:19
投稿者: taitani

Excel の一般機能ではできない認識です。
VBA であれば可能です。

回答
投稿日時: 21/08/27 10:50:32
投稿者: Suzu

1. ピボットテーブル ウィザードを起動  (「ALT」「D」 「P」)
2. ウィザード の 1/3 分析するデータ に、「複数のワークシート範囲」 を指定
  作成するレポート種類は ピボットテーブルのまま 【次へ】
3. ウィザード の 2a/3 ページフィールド作成方法 に 「指定」を指定 し 【次へ】
4. ウィザード の 2b/3 範囲 に、A1:F5 を指定 他は触らずに 【次へ】
5. ウィザード の 3/3 新規ワークシート を指定して 【完了】
6. できた ピボットテーブルの 総計 の 総計位置を ダブルクリック
7. 希望の形式に近い テーブル が作成されますから
   列 を削除
   値 のフィルター にて、「空白」のみを選択し、【OK】し、データを削除

回答
投稿日時: 21/08/27 11:25:44
投稿者: んなっと

●Sheet1
 
    A  B  C  D  E  F
1  品番  1  2  3  4  5
2 10-aaa 赤        
3 10-aab          
4 20-11a 赤 青      
5 30-22b          
6 40-aaa 黄 赤 青 黒 緑
 
●Sheet2
 
    A  B  C
1  品番 色   
2 10-aaa 赤 202
3 20-11a 赤 402
4 20-11a 青 403
5 40-aaa 黄 602
6 40-aaa 赤 603
7 40-aaa 青 604
8 40-aaa 黒 605
9 40-aaa 緑 606
 
A2
=IF(C2="","",INDEX(Sheet1!A:A,INT(C2/100)))
下方向・↓
B2
=IF(C2="","",INDEX(Sheet1!A:J,INT(C2/100),MOD(C2,100)))
下方向・↓
C2
=IFERROR(AGGREGATE(15,6,ROW($B$2:$F$500)*100+COLUMN($B:$F)/(Sheet1!$B$2:$F$500<>""),ROW(A1)),"")
下方向・↓
 
 
どうしても作業列を使いたくないときは[悪い方法]
 
    A  B
1  品番 色
2 10-aaa 赤
3 20-11a 赤
4 20-11a 青
5 40-aaa 黄
6 40-aaa 赤
7 40-aaa 青
8 40-aaa 黒
9 40-aaa 緑
 
A2
=IFERROR(INDEX(Sheet1!A:A,AGGREGATE(15,6,ROW($B$2:$F$500)/(Sheet1!$B$2:$F$500<>""),ROW(A1))),"")
下方向・↓
B2
=IFERROR(INDIRECT("Sheet1!"&TEXT(AGGREGATE(15,6,ROW($B$2:$F$500)*100+COLUMN($B:$F)/(Sheet1!$B$2:$F$500<>""),ROW(A1)),"!R0!C00"),0),"")
下方向・↓

回答
投稿日時: 21/08/27 11:45:24
投稿者: んなっと

取得と変換[またはPowerPivot]が使える環境なら
  
 【データ」【テーブルまたは範囲から】
→列1〜5をShiftキーを併用して全選択
→【変換】【列のピボット解除】
→属性列を選択して【ホーム】【列の削除】
→【閉じて読み込む】
 
これだとわかりにくいと思いますから、まずはPowerPivotでGoogle検索してみてください。

回答
投稿日時: 21/08/27 12:49:37
投稿者: taitani

Suzu さん
すごいですね!こんな機能があるとは、勉強になります。
 
んなっと さん
流石です、、、思いつきませんでした。
 
ラングドジャ さん
できないと言って恥ずかしい限りです。。。

回答
投稿日時: 21/08/27 13:18:26
投稿者: んなっと

PowerPivotではなくてPowerQueryでした。
それと、追加。取得と変換の方について。
 
元データに変更があったら
【データ】→【すべて更新】

投稿日時: 21/08/27 13:27:25
投稿者: ラングドシャ

tanita様
私も別の方法を考えていました。
最後の手段として質問させていただいたので・・・
 
Suzu様
ピボットテーブルは本当にたまに使うだけですが、
>6. できた ピボットテーブルの 総計 の 総計位置を ダブルクリック
でほぼ近い形ができるのにびっくりしました。
ありがとうございました。
 
んなっと様
関数でできるのですね。すごいです。
わからないながら関数を分解して調べてみました。indexでの位置の取得にもいろんな方法があるのですね。
PowerPivot初めて使ってみました。まだよくわからない点が多いのでこちらも調べてみます。
本当にありがとうございました。