VBA 別シートに表示

「作業1」シートの内容を「作業2」シートの表の形にしたいのです。
「作業2」シートの「注番」「氏名」列には、
「作業1」シート　I11　に　COUNTA(Q11:Z11)　の式を入れ、
「作業2」シート　B2　に　{=IF(ROW(B1)>SUM(作業1!$I$1:$I$200),"",INDEX(作業1!$J$1:$J$200,MIN(IF(SUBTOTAL(9,OFFSET(作業1!$I$1,0,0,ROW(作業1!$J$1:$J$200)))>=ROW(B1),ROW(作業1!$J$1:$J$200)))))}　を入れると、うまくいきました。
しかし、「商品コード」「商品名」「数量」列が、さっぱり、わかりません。
関数で表示できるのであれば、関数でやりたいのですが、VBAもわかりません。
大変困っています。どうか、助けて下さい。よろしくお願い致します。

No.1 ベストアンサー 回答者： fujillin 回答日時： 2024/02/10 14:25

こんにちは

なんだか、項目の並びも全然違うし、セルの列も飛び飛びのようですし、およそ表計算には向いていない表の作り方としか言いようがありません。
関数でできないことはないですけれど、無駄が多くなる上に不必要に複雑になるばかりなので、困るのはその通りだと想像します。
困らないようにするために、表の作り方を見直すことをお勧めします。
VBAなら、まだ独自でルール化をできるので、対応しやすいですけれど、それにしてもセル位置がアチコチに・・・


ご質問文にご提示の式もそれなりに複雑ですけれど、作業列を利用して元データを一旦整形し、その上で抽出する方が遥かにわかりやすく、式も簡単になると思います。
作業列はどこでも良いですが、仮に、作業用の中間シートを作成して非表示のシートにしておくことを想定して以下に示します。
データの列がバラバラですが、ひとまずA:E列に並べてしまうことにします。
内容としては、元データを求める形式に整形して、1行のデータを3行に単純参照するというものです。
1）中間シートのA:E列1行目に、ご提示の作業2シートのタイトルを記入
2）中間シートの
・A2セルに以下の式を入れ、B2にコピペ
　=INDEX(作業1!J:J,INT(ROW(A3)/3)+10)
・C2セルに以下の式を入れ、D2セルにコピペ
　=INDEX(作業1!$Q$9:S$10,COLUMN(A1),MOD(ROW(A3),3)+1)
・D2セルに以下の式を入力
　=INDEX(作業1!$Q:$S,INT(ROW(A3)/3)+10,MOD(ROW(A3),3)+1)
その上で、A2:E2セルを選択し、充分に下方までフィルコピー
文章と式だけではわかりにくいかもしれませんが、やってみれば何をしているかわかると思います。

中間シートができれば、E列の値（＝数量）が0以外の行を抽出して、最終の作業2シートへ移せば良いことがわかると思います。
（ご提示のような複雑な式を作成できるのなら、これは簡単であろうと思います）
手っ取り早いのは、中間シートでオートフィルターをかけて、作業2シートにコピペでコピーする方法かもしれません。
関数で行うにしても、スピル機能を使えるエクセルのバージョンであれば、FILTER関数を利用できるので、最初のセルに関数を設定すれば自動で必要な範囲にスピルできますね。
仮に、C、D列（＝コード、商品名）を抽出するなら、AU1セルに
　=FILTER(中間シート!C:D,中間シート!E:E<>0,"")
を入力すれば、2列分の内容がスピルされます。


『そんなことはとっくに承知だけれど、中間シートなんて作りたくないから質問しているんだ！』というのであれば・・
お薦めはしませんけれど、以下の方法で。
ご提示の作業2シートで
・AU2セルに以下の式を入力し、下方にフィルコピー
=IFERROR(INDEX(作業1!Q$9:S$9,MOD(AGGREGATE(15,6,(ROW($A$1:$C$99)*100+COLUMN($A1:$C99))/(SUBTOTAL(2,OFFSET(作業1!$Q$10,0,0,ROW($1:$99),3))+SUBTOTAL(2,OFFSET(作業1!$Q$10,ROW($1:$99),0,1,COLUMN($A:$C)))>ROW()-2),1),100)),"")

・AV列はAU列から参照するタイトルを1行下げれば良いので、上式中の「Q$9:S$9」を「Q$10:S$10」に変えれば良いです。

・BE列が一番厄介ですが、BE2セルに以下を入力し、同様に下方にフィルコピー
=IFERROR(INDEX(作業1!$Q:$S,AGGREGATE(15,6,ROW($1:$99)/(SUBTOTAL(2,OFFSET(作業1!$Q$11,0,0,ROW($1:$99),3))>ROW()-2),1)+10,MOD(AGGREGATE(15,6,(ROW($A$1:$C$99)*100+COLUMN($A$1:$C$99))/(SUBTOTAL(2,OFFSET(作業1!$Q$10,0,0,ROW($1:$99),3))+SUBTOTAL(2,OFFSET(作業1!$Q$10,ROW($1:$99),0,1,COLUMN($A:$C)))>ROW()-2),1),100)),"")

この回答へのお礼

頂いた式でうまくいきました。あなたは天才ですか！って思うくらい私にとって神です。ありがとうございました。

お礼日時：2024/02/11 12:56

No.3 回答者： goomania 回答日時： 2024/02/10 23:49

No.2です。

大変失礼しました。No.1さんの回答についての説明で「fujillinさん」とすべきところ、敬称を漏らして投稿してしまいました。
誠に申し訳ありませんでした。お詫びして訂正いたします。

No.2 回答者： goomania 回答日時： 2024/02/10 21:56

fujillinにより、「作業列と数式」による解決方法が示されました。

ご質問者が「関数で表示できるのであれば、関数でやりたいのですが、･･･」と仰っているので、そのような回答になるのは自然です。

ただ、このようなケースでは、EXCELのバージョンにもよりますが、Power Queryを使う方法が効率的だと思います。
というのは、Power Queryなら、数式もVBAも不要だからです。

添付画像をご覧ください。そもそもご質問者が掲出された作業1シートの表は列の一部が非表示になっているものと思われます。
非表示を解除すると画像(a)のような表になるのではないかと思います。これを以下の手順で作業2の表に変換します。

(1)画像(a)のように、作業1シートの表がテーブルになっていない場合はテーブル化する
(2)テーブル内のセルのいずれかを選択した状態で、メインメニューの「データ」タブから「データの取得と変換」の「テーブルまたは範囲から」をクリックする
(3)Power Queryエディタが起動し、画像(b)のようにデータが読みこまれるので、Power Queryエディタのメニューの「変換」タブを選択する
(4)エディタ画面で「注番」「氏名」の二つの列を選択し、「列のピボット解除」のプルダウンから「その他の列のピボット解除」を選択する
(5)すると画像(c)のように、表が整形されるので、Power Queryエディタのメニューの「ホーム」タブを選択する
(6)画像(c)のように「閉じて読みこむ」アイコンをクリックすると「閉じて読み込む」が選択可能になるので、これを選択する
(7)Power Queryの設定が標準的設定のままであれば、(6)により新規シートに画像(d)のような整形された表が作成される

実際は、作業1シートの表には商品コードもあるので、これも含めて当然Power Queryで整形できますが、若干ハードルがあるので、こでは商品コードは除いて整形しています。
なので、冒頭に「数式もVBAも要らない」と申し上げたのですが、「看板に偽りあり」となりますが、商品コードの列を追加し、商品コードを表示するための数式が必要になってしまいます。

そうはいっても、画像(c)の表が作成できてしまえば、ここに商品コードの列を追加・表示する作業は、作業2シートのB2セルに、配列数式を記述できるご質問者の実力からすれば、比較的容易なことだと思います。

この回答へのお礼

Power Queryなんて知りませんでした。ご丁寧に画像まで作って説明して頂き、ありがとうございました。

お礼日時：2024/02/11 12:40