[[20210127140259]] 『複数条件に合うデータ(数値)の抽出方法』(たくあん) >>BOT

[ 初めての方へ | 一覧(最新更新順) |

| 全文検索 | 過去ログ ]

 

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

お世話になります。

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

担当者別サイクル管理表
Sheet1:Aさん
              M2セル→氏名:A
   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:氏名Bさん〜 約20名程担当者1名につき1シート毎にサイクル別の売上金額が入力された管理表があります。

               ↓
最終シートに

リンゴ売上 1C 2C 3C 4C  5C  6C 7C  8C  9C  10C 11C 12C 13C 

Aさん            
Bさん
Cさん
Dさん
Eさん
〜20数名分

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

< 使用 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

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

      -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


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

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

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


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.