Excel (一般機能)

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

 
(Windows 10 Pro : Excel 2016)
集計表をリスト表に変換
投稿日時: 19/01/17 13:37:27
投稿者: 水の味

エクセルの集計表をデータベース形式のデータに置き換えしたいのです。
マクロを使わず関数だけでできないでしょうか。
例えばいろいろな商品の納期、数量が一覧になっているエクセルの表になっている場合を
それぞれ一つの商品のデータを取り出し追加情報を組み込んで再度表(データベースの表)に組み込んで使用するためです。

回答
投稿日時: 19/01/17 13:57:19
投稿者: WinArrow
投稿者のウェブサイトに移動

だいぶ抽象的な説明ですね?
 
できるかも、できないかも・・・・
 
元表のレイアウトとか、作成したい表の形式とか、具体的な説明は・・・・秘密ですか?

投稿日時: 19/01/17 19:27:41
投稿者: 水の味

説明が悪くすいません
 
元表は縦軸:商品、横軸:日にち(1日〜31日)、中部分:数量となっております。
 
   1/7 1/8 1/9 1/10 1/11 1/12 1/13 1/14
A商品 10        32           16
B商品    12     22 
C商品                58
 
 
 
これを   A商品 1/7  10個
         1/10 32個
         1/14 16個
      B商品 1/8  12個
         1/10 22個 
      C商品 1/12 58個
 
というように商品列、日にち列、数量列と縦軸にし
1行1データとしデータベースのデータとして使いたいのです。
お分かりいただけたでしょうか。
宜しくお願いします。

回答
投稿日時: 19/01/17 20:26:50
投稿者: んなっと

●Sheet1
 
    A  B  C  D   E   F   G   H   I
1     1/7 1/8 1/9 1/10 1/11 1/12 1/13 1/14
2 A商品  10       32           16
3 B商品    12     22            
4 C商品                58      
 
●Sheet2
 
D列に行列番号
D2
=IFERROR(AGGREGATE(15,6,100*ROW($2:$500)+COLUMN($B:$AZ)/(Sheet1!$B$2:$AZ$500<>""),ROW(D1)),"")
下方向・↓
 
    A   B   C   D
1  品名 日付 数量 位置
2 A商品  1/7  10  202
3 A商品 1/10  32  205
4 A商品 1/14  16  209
5 B商品  1/8  12  303
6 B商品 1/10  22  305
7 C商品 1/12  58  407
 
A2
=IF(D2="","",INDEX(Sheet1!A:A,INT(D2/100)))
下方向・↓
B2
=IF(D2="","",INDEX(Sheet1!$1:$1,MOD(D2,100)))
下方向・↓
C2
=IF(D2="","",INDEX(Sheet1!$1:$500,INT(D2/100),MOD(D2,100)))
下方向・↓
 
 
※下のような作業列を使わない方法は、なるべく使わないでください。
 
    A   B   C
1  品名 日付 数量
2 A商品  1/7  10
3 A商品 1/10  32
4 A商品 1/14  16
5 B商品  1/8  12
6 B商品 1/10  22
7 C商品 1/12  58
 
A2
=IFERROR(INDEX(Sheet1!A:A,AGGREGATE(15,6,ROW($2:$500)/(Sheet1!$B$2:$AZ$500<>""),ROW(A1))),"")
下方向・↓
B2
=IFERROR(INDEX(Sheet1!$1:$1,MOD(AGGREGATE(15,6,100*ROW($2:$500)+COLUMN($B:$AZ)/(Sheet1!$B$2:$AZ$500<>""),ROW(B1)),100)),"")
下方向・↓
C2
=IFERROR(INDIRECT("Sheet1!"&TEXT(AGGREGATE(15,6,100*ROW($2:$500)+COLUMN($B:$AZ)/(Sheet1!$B$2:$AZ$500<>""),ROW(C1)),"!R0C00"),FALSE),"")
下方向・↓

回答
投稿日時: 19/01/17 20:28:55
投稿者: WinArrow
投稿者のウェブサイトに移動

関数ではありませんが、
手操作になりますが、
↓に変換方法が掲載されています。
 
数量が空白セルも変換されてしまいますが、
並べ替えれば、一括削除できます。
 
http://www.shegolab.jp/entry/excel-macro-flatten-matrix

投稿日時: 19/01/18 15:48:03
投稿者: 水の味

早速の回答 んなっとさんありがとうございます。
しかし私エクセルあまり使いこなしてないので、今回の関数の使い方がどこにどのように使うかが分かりません。
もう少し初心者レベルで説明をお願いできませんでしょうか。
お手数かけます。
 
 
WinArrowさん回答ありがとうございました。
これはピボットテーブルでの表の変換ですね、
これは以前使用したことがあり、うまくいきましたが、
今回は普通(関数も何も使っていない)の手書きの表を変換するのでうまくいかないと思いますが。
どうでしょう

回答
投稿日時: 19/01/18 17:09:07
投稿者: WinArrow
投稿者のウェブサイトに移動

水の味 さんの引用:

WinArrowさん回答ありがとうございました。
これはピボットテーブルでの表の変換ですね、
これは以前使用したことがあり、うまくいきましたが、
今回は普通(関数も何も使っていない)の手書きの表を変換するのでうまくいかないと思いますが。
どうでしょう

 
紹介のページで説明しているのは、
既に作成されているピボットテーブルを操作するのではなく、
手操作で作成した表(投稿日時: 19/01/17 19:27:41 に書かれているような表)を、
対象にしています。
 
手順の中では、手操作で作成した表を、一旦、ピボットテーブルに変換しています。
変換されたピボットテーブルの右下のセルをダブルクリックすると、
表形式の表が自動作成されます。
 
この方法では、数量が空白も含まれていますので、数量列で並べ替えて、行削除すれば、
お望みの表にすることができます。
 
実際に、投稿日時: 19/01/17 19:27:41 掲示の表でテストしてみましたので、
できると思います。

回答
投稿日時: 19/01/18 18:42:55
投稿者: Mike

》 …というように商品列、日にち列、数量列と縦軸にし
》 1行1データとしデータベースのデータとして使いたい
のはよく分かるけど、下記の 6〜11行目に示すものでは駄目よ、ダメ、ダメですか?
 
   A  B  C  D  E  F  G  H  I  J  K  L  M  N  O  P  《 AF
1 日付 1/01 1/02 1/03 1/04 1/05 1/06 1/07 1/08 1/09 1/10 1/11 1/12 1/13 1/14 1/15 》1/31
2 A商品                 10       32         16   《
3 B商品                   12    22              》
4 C商品                             58        《
5
6 A商品 1/07 1/10 1/14
7    10個 32個 16個
8 B商品 1/08 1/10
9    12個 22個
10 C商品 1/12
11    58個

回答
投稿日時: 19/01/18 18:49:37
投稿者: んなっと

水の味さんは、2回目の書き込みの時にシート名や行・列の記号を書き込みませんでしたね。
こちらの方で適当に情報を補って回答しました。

回答
投稿日時: 19/01/18 20:22:42
投稿者: masExcel

Excel 2016 以降であれば、手作業が発生するものの、取得と変換で対応が可能です。
 
まず、表をテーブルにします。(Ctrl + T)
 
その後、データタブの取得と変換から、「テーブルまたは範囲から」を起動します。
 
すると、PowerQueryが起動します。
 
そこで、変換タブから、「その他のピボット列解除」を実行し、
ホームタブから、「閉じて保存する」と、実行すると、思いのリストが完成します。
 
 
Excel 2016 より前のバージョンであれば、VBA で実装するのが自然ですが、かなりのプログラミング技術を要します。
自分は、アドインを自作し、組み込んで使用していますが、2016のバージョンが出来てから、使用していません。取得と変換で、事足ります。

投稿日時: 19/01/18 21:19:01
投稿者: 水の味

皆さんありがとうございました。
関数での変換も、ピボットテーブルでの変換もうまくいきました。
おかげさまで表内の数値がデータとして活用できるようになりました。
お世話掛けました。ありがとうございました。