「作業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も見ています
-
プロが教えるわが家の防犯対策術!
ホームセキュリティのプロが、家庭の防犯対策を真剣に考える 2組のご夫婦へ実際の防犯対策術をご紹介!どうすれば家と家族を守れるのかを教えます!
-
複数エクセルファイルの合成
Excel(エクセル)
-
Excelのテーブルでmatch関数の使い方について
Excel(エクセル)
-
エクセルの表の参照値から円を取って数字で扱えるようにしたい
Excel(エクセル)
-
-
4
エクセルでビット1をカウント Javaには Integer.bitCount() というメソッドがあ
Excel(エクセル)
-
5
エクセルのセル統合について
Excel(エクセル)
-
6
Excelの関数で起きた現象の原因がわかりません。
Excel(エクセル)
-
7
エクセル関数の使い方を教えてください。 2000万円の貯蓄を5%で運用しながら毎年100万円ずつ取り
Excel(エクセル)
-
8
excel access連携 このテーブルは空ですと表示
Excel(エクセル)
-
9
ExcelのVBAで、認識しない書式はありますか?
Excel(エクセル)
-
10
マクロについて教えてください。
Excel(エクセル)
-
11
excelの不要な行の削除ができない!
Excel(エクセル)
-
12
Excel 大なり小なりを表すとき、 例えば「10以上」なら、>=10 と表せますが、 この10の部
Excel(エクセル)
-
13
同一セルに入力規則のリストと参照する設定併用できるような設定はありますでしょうか!!
Excel(エクセル)
-
14
エクセル関数について質問です。 希望日を該当者の各リストに記載して、最終的に項目の多い希望日のランク
Excel(エクセル)
-
15
下記のような条件付き書式はどうやって設定したら良いのか教えていただきたいです。 180時間を満たない
Excel(エクセル)
-
16
エクセル 条件付き書式設定
Excel(エクセル)
-
17
エクセル VBAでの転記の方法について
Visual Basic(VBA)
-
18
Excelセルの参照先の取得
Excel(エクセル)
-
19
ある値に対して3番目に大きな値を、なければ2番目に大きな値、それもなければ1番大きな値を表示させたい
Excel(エクセル)
-
20
関数を教えていただきたいです。 添付のような「data sheet」があります。 他に、「集計 sh
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルのデーターが2か月前の...
-
エクセルVBA、別ブックへ転記す...
-
【マクロ】顧客番号にて一致さ...
-
エクセル共有したが、アクセス...
-
エクセル②
-
(マクロ)データをAブックからB...
-
Excelでセルの値が同じか...
-
エクセルを使っていて2024/5/15...
-
指定文字の間に
-
Microsoft 365の Excel を使用...
-
エクセルの計算
-
エクセルでの作業計算方法について
-
Excelで全角を半角にしたいので...
-
エクセル関数に詳しい方教えて...
-
Googleスプレッドシートでファ...
-
エクセル 文字を増やしたい。
-
はがきについて。
-
エクセルの暗号化なしのバーの...
-
【マクロ】必要な項目(列)の...
-
Excel
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報