[[20210127140259]]　『複数条件に合うデータ(数値)の抽出方法』（たくあん）　ページの最後に飛ぶ

『複数条件に合うデータ(数値)の抽出方法』（たくあん）

お世話になります。

複数条件に合うデータを抽出し別シートへの取りまとめ方法についてご教授を宜しくお願い致します。

担当者別サイクル管理表
Sheet1：Aさん
　　　　　　　　　　　　　　M2セル→氏名：Ａ
　　　1ｻｲｸﾙ(C) 2C　3C 4C　5C　6C　7C　8C　9C　10C　11C　12C　13C
リンゴ　1000 500　400 300 150 700 850 99 654 321　120　400　710　　

みかん　100 500　200 800 50 140 200 44 45 111　607　900　718

　etc…10種類程の売上金額が入力されています。　　
　
　　　　　　　　　　　　　　　↓

Sheet2：氏名Ｂさん～　約２０名程担当者１名につき１シート毎にサイクル別の売上金額が入力された管理表があります。

　　　　　　　　　　　　　　　↓
最終シートに

ﾘﾝｺﾞ売上　1C 2C 3C 4C　 5C 　6C 7C 　8C 　9C 　10C　11C　12C 13C　

Ａさん　　　　　　　　　　　　
Ｂさん
Ｃさん
Ｄさん
Ｅさん
～２０数名分

上記の様な表を作成し、各担当者のシートからそれぞれのサイクルのリンゴの
売上額を抽出した表を作成したいと思っております。
　シートの担当者の氏名と売上商品名とサイクル名が一致する金額を別シートに作成できずにおります。
何卒宜しくお願い致します。

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

 シート毎に担当者が分かれているならシート毎にVLOOKUPで持ってくれば良いのでは
（なるへそ） 2021/01/27(水) 15:07

 なるへそさんの回答に、INDIRCT関数を加えて一つの数式で表示できるようにした案です。

 シート名 Aさん　　　　　　　　　　　　　　　　　　　　　　　　シート名 Bさん
 	[A]	[B]	[C]	[D]	[E]			[A]	[B]	[C]	[D]	[E]
 [1]	Aさん	1C	2C	3C	4C			Bさん	1C	2C	3C	4C
 [2]	リンゴ	1000	500	400	300			リンゴ	150	700	850	99
 [3]	みかん	100	500	200	800			みかん	50	140	200	44

 集計シート
 	[A]	[B]	[C]	[D]	[E]
 [1]	リンゴ	1C	2C	3C	4C
 [2]	Aさん	1000	500	400	300
 [3]	Bさん	150	700	850	99

 上記のようなシートレイアウトだとして（F列以下は説明のため省略）

 集計シートで
 B2=VLOOKUP($A$1,INDIRECT($A2 &"!A:N"),COLUMN(B2),FALSE)

 と入力して右・下にコピーするとこのようになります。
 　
（tora） 2021/01/27(水) 15:37

早速ありがとうございます。やってみます！ありがとうございます！
（たくあん） 2021/01/27(水) 15:50

お邪魔します。
すでに解決だと思うので、場所をつかわせてください。
Power Queryです。

 Sheet1：Aさん
     -A-      -B-     -C-      -D-     -E-     -F-
 1
 2
 3  商品       1C      2C      3C      4C      5C   …   
 4  リンゴ     1000    500    400     300     150
 5  みかん      100    500    200     800      50

１）担当者別の表↑をテーブル設定（テーブル名は、Aさん等の担当者名）にしておけば
２）Power Queryで、各シートのテーブルを結合
３）こんなレイアウトのピボットテーブルとして出力できます。

      -A-      -B-   -C-   -D-  -E-   -F-
 1   商品     みかん 			
 2								
 3 合計/売上  サイクル							
 4 担当者       1     2     3     4     5   …   
 5  Aさん      100   500   200   800    50
 6  Bさん      200   500   100   100   100

'----

 let
    ソース = Excel.CurrentWorkbook(),
    #"展開された Content" = Table.ExpandTableColumn(ソース, "Content", {"商品", "1C", "2C", "3C", "4C", "5C", "6C", "7C", "8C", "9C", "10C", "11C", "12C", "13C"}, {"商品", "1C", "2C", "3C", "4C", "5C", "6C", "7C", "8C", "9C", "10C", "11C", "12C", "13C"}),
    ピボット解除された他の列 = Table.UnpivotOtherColumns(#"展開された Content", {"Name", "商品"}, "属性", "値"),
    置き換えられた値 = Table.ReplaceValue(ピボット解除された他の列,"C","",Replacer.ReplaceText,{"属性"}),
    #"名前が変更された列 " = Table.RenameColumns(置き換えられた値,{{"Name", "担当者"}, {"属性", "サイクル"}, {"値", "売上"}}),
    変更された型 = Table.TransformColumnTypes(#"名前が変更された列 ",{{"サイクル", Int64.Type}, {"売上", Currency.Type}})
 in
    変更された型

'-----
参考にしたサイト
https://trumpexcel.com/combine-multiple-worksheets/
重要：後半の注意事項まで目を通してから使用すること。
　
（マナ） 2021/01/27(水) 20:29

マナ様、ありがとうございます。解決できないでいるところにコメントを拝見いたしました。
私はエクセルの初心者です。テーブルでの作業自体を行ったことがありませんので、詳しく教えていただけませんでしょうか理想はマナ様が教えてくださっていることだと思うのですが、レベルが高すぎて。。。
各担当者のシートは先に示した通りなのですが、別シートに抽出して取りまとめた表は

1　　　　　Aさんグループ　　1C(サイクル)
2　　　　　　Aさん　　　Bさん　　Cさん　　Dさん　　Eさん　…　　合計
3　リンゴ　　　100　　　200　　　400　　　500　　　150
4　　ナシ　　　400　　　300　　　200　　　100　　　90　　　　
5　ぶどう
6　柿
…
合計　etc…　の様に1C(サイクル)～13Cまでサイクル毎並びにグループ毎に取りまとめることになっております。
他にも新規獲得件数・金額などが下の行に続きます。
担当者別のシートは同じ配置にしております。
マナ様が教えてくださっていることができれば、仮に担当者の増減でシートを加減しても抽出し出力できるということかと思います。
どうかマナ様見てくださってることを祈ります！！！
初心者でも何とかたどり着けるようにご指導宜しくお願い致します！！！
（たくあん） 2021/02/03(水) 19:07

１）ピボットテーブルであれば、１つの元データから、色々な見せ方（集計の仕方）が可能です。
２）ただし、ピボットテーブルを利用するには、複数シートのデータを１つのテーブルにまとめる必要があります。
３）今回は、データを１つのテーブルにまとめる　という作業に、Power Queryを利用しています。

なので、まずは、ピボットテーブルを使えるようになる必要があります。

（マナ） 2021/02/03(水) 20:38

コメント返信：

[ 一覧(最新更新順) ]

[[20210127140259]] 『複数条件に合うデータ(数値)の抽出方法』（たくあん） ページの最後に飛ぶ

[[20210127140259]]　『複数条件に合うデータ(数値)の抽出方法』（たくあん）　ページの最後に飛ぶ