[[20201112000911]]　『別シートに抽出後，自動的に行数が変化し，最後の』（そら）　ページの最後に飛ぶ

『別シートに抽出後，自動的に行数が変化し，最後の行に平均値も出すマクロの作り方。』（そら）

関数やＶＢＡなどを使って，便利なものを作りたいと考えています。

1枚目のシートを入力シートとして，下記のように項目で入力します。

部　係　担当者名　売上個数　売上種　売上額　　地域
Ａ　a 高田　　　　20 34 200 愛知
Ａ　b 金田　　　　12 14 140 群馬
Ａ　c 高井　　　　56 44 980 山梨
Ａ　d 大場　　　　38 12　　　806 愛知
Ａ　e 門井　　　 178 245 1209 群馬
Ａ　f 外田　　　 12 198 2098 神奈川
Ａ　g 山県　　　 289 33 908 愛知
Ａ
・
・
・
とします。

そして，愛知，群馬，山梨，神奈川と地域ごとのシートをつくります。
この地域のシートに，上の入力シートの「地域」の欄に，その地域を入れたら（たとえば，愛知，と入れたら），自動的に，「愛知」タグのシートに，下記のように，抽出されるようにしたいです。さらに，

部　係　担当者名　売上個数　売上種　売上額　　地域
Ａ　a 高田　　　　20 34 200 愛知
Ａ　d 大場　　　　38 12　　　806 愛知
Ａ　g 山県　　　 289 33 908 愛知
・
・
・
　　　　平均　　　115.6 26.3 638

のように，下に平均を出すようにしたのですが・・・。

ちなみに，その日によって，担当者や地域の数が変わるので､行数も自動的に変化（平均の行までの行数が自動的に増減する）するようになっていてほしいです。

どのように，作ればよいか，，，教えてください。

＜使用 Excel：Excel2016mac、使用 OS：Windows10 ＞

 一行目に行挿入してsubtotal関数で平均列指定して、
 2行目に項目名
 項目名でオートフィルタじゃいかんのですか？
（稲葉） 2020/11/12(木) 07:48

 どうしてもシートを分けたい、というのであれば…
 エクセル シート名を取得する
 https://www.tipsfound.com/excel/01306
 この記事を参考にシート名をどこかのセルに取得して
 それを検索値にしてindex&matchなどで値を持ってくるもよし
 稲葉さんの方法でオートフィルタでもよし、でどうでしょうか。
（ななし） 2020/11/12(木) 10:11

 実用的かどうかはともかく、とりあえず関数で。

 元のデータが入力されているシート名を「入力」、
 その中で A1 セルから順にデータが入力されているものとします。

 抽出するシートは地域ごとに作成するとのことですから、まず「愛知」シートを作り、
 一行目に項目を入れておきます。

        [A]	[B]	[C]		[D]		[E]	[F]	[G]
 [1]	部	係	担当者名	売上個数	売上種	売上額	地域
 [2]	Ａ	a	高田		20		34	200	愛知
 [3]	Ａ	d	大場		38		12	806	愛知
 [4]	Ａ	g	山県		289		33	908	愛知
 [5]			平均		115.7		26.3	638

 まず最初に、
 G2=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,20) と入力すると、「愛知」と表示されます。
 次に
 G3=IF(A3="","",G2)　と入力して下にコピーします。この段階では何も表示されませんが構わず次に進みます。

 A2=IFERROR(INDEX(入力!A:A,AGGREGATE(15,6,ROW($A$1:$A$10)/(入力!$G$1:$G$10=$G$2),ROW(A1))),"")
 と入力して、B2にコピー、それぞれ下にコピーします。

 C2=IF(COUNTIF(入力!G:G,愛知!$G$2)+2=ROW(),"平均"
,IF(COUNTIF(入力!$G:$G,愛知!$G$2)>=ROW(C1),INDEX(入力!C:C,AGGREGATE(15,6,ROW($A$1:$A$10)/(入力!$G$1:$G$10=$G$2),ROW(C1))),""))
 と入力して下にコピーします。

 D2=IF(COUNTIF(入力!$G:$G,愛知!$G$2)+2=ROW(),ROUND(AVERAGE(OFFSET(D$1,1,0,ROW()-2,1)),1)
,IF(COUNTIF(入力!$G:$G,愛知!$G$2)>=ROW(D1),INDEX(入力!D:D,AGGREGATE(15,6,ROW($A$1:$A$10)/(入力!$G$1:$G$10=$G$2),ROW(D1))),""))

 と入力して F列までコピー、それぞれ下にコピーします。
 これで上記のような表ができます。

 なお、元データの範囲はここでは10行まで、平均は小数点第一位までの表示としてありますので適宜修正した下さい。

 これで愛知の表ができますので、あとはシートのコピーで新規シートを作り、シート名を変更すればその地域の表になります。
 　
（tora） 2020/11/12(木) 10:32

 頂いた回答ですでに解決されているかもしれませんが。

 シートを分けなくても、
 ピボットテーブルの利用が適しているように思います。
 データが更新されても、簡単に追随できますし。
 検討されてはいかがでしょうか。
（γ） 2020/11/12(木) 10:37

　みなさん，いろいろとアイディアをいただき，ありがとうございました。
どうしても，私だけではなく，ほかの担当者がファイルを開いて，すぐに，地域ごとの平均値がリアルタイム？にわかるようにしておく必要がありましたので，みなさんのお知恵をお借りしたく質問させてもらいました。
　まずは，toraさんの関数を使わせていただきます。正直，コピペして使わせてもらうことになります。
それで，わからなければ，稲葉さんのおっしゃるように，こつこつアナログ？的に作業をしようと思います。

　ほかに何かよい方法があれば，教えてください。

（そら） 2020/11/12(木) 20:00

＞　ほかに何かよい方法があれば，教えてください。

γさんの提案については、何がだめですか。
シートを選択するより、フイルターリストから選択するほうが簡単な気がします。
どうしてもというなら、地域の数だけピボットを複製しておいてもよいです。

＞ファイルを開いて，すぐに，地域ごとの平均値がリアルタイム？にわかるようにしておく必要がありました

ファイルを開くときにデータを更新する設定があります。

というわけで、おすすめは、ピボットです。
わたしの場合、数式が苦手で、理解できないというのが一番の理由ですが。

（マナ） 2020/11/12(木) 20:28

いえいえ！γさんの提案については・・・・実は，ピボットを使ったことがなくて。
もしよければ，上記のようにシートを作るとしたら，どのようにピボットをつくったら？よいか，教えてほしいです。
（そら） 2020/11/12(木) 20:47

 ＞　ほかに何かよい方法があれば，教えてください。

お使いのエクセルに、
スライサーという機能が付いていればそれはいかがでしょう？

知らなければ検索を

なければ、ピボットテーブル案に1票

プルダウンリストから選択というのにどうしてもなじめなく、
タブ選択が直感的でよいというのならVBAを使って、
エクセルをカスタマイズすることになりますが、
人にただで作らせてコピペで何とかしようという姿勢なら、
VBAに手を出さない方が賢明です。

数式は僕も苦手で、今も自分の本業で1時間以上四苦八苦してやっと結果がでたとこ＞＜
（まっつわん） 2020/11/12(木) 21:06

＞ピボットを使ったことがなくて

ならば、ネットで検索して調べてください。
ここで、文章だけで説明するより、
図解でわかりやすいところが、たくさんあります。

そのうえで、できなかったら質問してください。

（マナ） 2020/11/12(木) 21:12

ピボットテーブルは一度覚えると、
Excelならではの使い勝手のいい道具になります。是非。

項目の順序も自由にできますし、平均も既定のメニューにあります。
「レポートのレイアウト」は「表形式」にすると希望のものに近いかも知れませんね。

（γ） 2020/11/12(木) 22:11

まっつわんさん，マナさん，γさん，いろいろとご意見，アドバイスありがとうございます。
ピボットですね，，，一から勉強してみます。ありがとうございました。
（そら） 2020/11/12(木) 22:51

コメント返信：

[ 一覧(最新更新順) ]

[[20201112000911]] 『別シートに抽出後，自動的に行数が変化し，最後の』（そら） ページの最後に飛ぶ

[[20201112000911]]　『別シートに抽出後，自動的に行数が変化し，最後の』（そら）　ページの最後に飛ぶ