「作業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ベストアンサー
- 回答日時:
こんにちは
なんだか、項目の並びも全然違うし、セルの列も飛び飛びのようですし、およそ表計算には向いていない表の作り方としか言いようがありません。
関数でできないことはないですけれど、無駄が多くなる上に不必要に複雑になるばかりなので、困るのはその通りだと想像します。
困らないようにするために、表の作り方を見直すことをお勧めします。
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)),"")
No.3
- 回答日時:
No.2です。
大変失礼しました。No.1さんの回答についての説明で「fujillinさん」とすべきところ、敬称を漏らして投稿してしまいました。
誠に申し訳ありませんでした。お詫びして訂正いたします。
No.2
- 回答日時:
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セルに、配列数式を記述できるご質問者の実力からすれば、比較的容易なことだと思います。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) ExcelのVBAコードについて教えてください。 6 2022/06/08 12:55
- Excel(エクセル) excelで検索した商品の画像(ネットワーク上の)を表示させたい。 3 2023/06/28 00:32
- Visual Basic(VBA) VBAコードが作動せず、どこに問題があるのか教えて下さい。 3 2023/06/13 13:20
- Excel(エクセル) VBA 複数ファイルの同じ項目を一行に合計したいです 4 2023/12/13 18:23
- Visual Basic(VBA) 複数シートの複数列に入力されているデータを重複なしで抽出するVBAを作りたいです。 9 2022/06/17 10:33
- Visual Basic(VBA) VBA 指定した回数分、別シートにコピー 2 2024/02/05 17:38
- Excel(エクセル) エクセルで”入力シート”の文字書式の変更を”出力シート”で同じ文字書式で印刷したいです。VBA希望 4 2023/04/24 11:07
- Visual Basic(VBA) A2~I4179列にあるリストを支社名ごとにシートに分けたいです。 各シート名はA列にある支社名とし 3 2023/08/29 16:46
- Excel(エクセル) IFERROR、SMALL関数について 2 2022/08/22 23:40
- Excel(エクセル) VLOOKUPで、検索先が3シートに分かれていて、番号の大小で検索するとエラーになります。 6 2023/07/14 19:09
このQ&Aを見た人はこんなQ&Aも見ています
-
性格の違いは生まれた順番で決まる?長男長女・中間子・末っ子・一人っ子の性格の傾向
同じ環境で生まれ育っても、生まれ順で性格は違うものなのだろうか。家庭教育研究家の田宮由美さんに教えてもらった。
-
エクセルのセル統合について
Excel(エクセル)
-
Excel 大なり小なりを表すとき、 例えば「10以上」なら、>=10 と表せますが、 この10の部
Excel(エクセル)
-
excelの不要な行の削除ができない!
Excel(エクセル)
-
-
4
Excel VBAについて【図形を組み合わせて作成した、料金別納表示の削除の仕方】
Excel(エクセル)
-
5
1行目のデータ(A1:G1)をコピーして、2~49999行へ貼り付ける効率的な操作方法は?
Excel(エクセル)
-
6
マクロについて教えてください。
Excel(エクセル)
-
7
Excelの関数で起きた現象の原因がわかりません。
Excel(エクセル)
-
8
VBA 指定した回数分、別シートにコピー
Visual Basic(VBA)
-
9
同一セルに入力規則のリストと参照する設定併用できるような設定はありますでしょうか!!
Excel(エクセル)
-
10
ExcelでA列をコピーしたいのですがコピー範囲内に空白セルがあるとそこで終わってしまいます。 全て
Excel(エクセル)
-
11
エクセル関数の使い方を教えてください。 2000万円の貯蓄を5%で運用しながら毎年100万円ずつ取り
Excel(エクセル)
-
12
ExcelのVBAで、認識しない書式はありますか?
Excel(エクセル)
-
13
複数エクセルファイルの合成
Excel(エクセル)
-
14
エクセル関数について質問です。 希望日を該当者の各リストに記載して、最終的に項目の多い希望日のランク
Excel(エクセル)
-
15
VBAコードが作動しません。修正したいのですが何処に原因かあるか教えて下さい。
Visual Basic(VBA)
-
16
エクセルでマクロでテキストデータを保存したい
Excel(エクセル)
-
17
他人が作ったマクロの理解
Excel(エクセル)
-
18
Excelのテーブルでmatch関数の使い方について
Excel(エクセル)
-
19
エクセルでビット1をカウント Javaには Integer.bitCount() というメソッドがあ
Excel(エクセル)
-
20
エクセルの表の参照値から円を取って数字で扱えるようにしたい
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルで 自動的に◯や数字を...
-
【マクロ】2回実行したら、エ...
-
エクセルのツールバーから数値...
-
特定の文字列を含む、住所を抽...
-
Excel 2019 [オプション]の[リボンのユ...
-
祝日と土曜、日曜の合計をカウ...
-
EXCELの散布図で日付が1900年に...
-
マイクロソフトのPADを使ってい...
-
絶対参照
-
エクセルでCtrl+Tでテーブルの...
-
【マクロ】名前を保存する際に...
-
エクセルのクイックアクセスツ...
-
【マクロ】VLOOKUPにて参照元に...
-
Excel分数の表示について
-
【EXCEL】画像の黄色部分の抽出...
-
DATE関数で現在の年齢を出した...
-
Excelについて
-
マクロエクセルのブロック解除
-
Excelピボットテーブルの1行目
-
REGEXREPLACE関数について、
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
半角カタカナをヘボン式ローマ...
-
(マクロ)vlookupの元データを同...
-
エクセルで上位バイトのセルと...
-
exselの質問です
-
Excel 大小比較演算子による「...
-
Excel VBについての質問です。
-
エクセルの問題です。絶対値の...
-
非表示列の再表示に失敗
-
職場の人から聞かれており、こ...
-
Excel関数-文字列で自動作成さ...
-
Excelデータをコピペして、ペー...
-
ユーザー定義関数をアドイン登...
-
【マクロ】for next構文について
-
エクセルの日付を編集する
-
【マクロ】VLOOKUPにて参照元に...
-
exselで最小数で並び替える関数
-
libre 表計算ソフトの計算がう...
-
エクセルで表
-
エクセルの表で1年間の曜日を...
-
西暦和暦
おすすめ情報