オートフィルターの合計値を別シートに反映

質問

ポイントカードのデータを別シートに反映させたいのです。アドバイスお願いします。

Sheet1の「住所ID」ごとの「金額」「回数」「客数」の“合計値”を、Sheet2の同じ「住所ID」の行に挿入させたいのですが、上手く出来ません。

オートフィルターで「住所ID」ごとに「合計値」は出せたのですが、住所IDごとに件数が１件とか１００件違うのでズレで上手くいきません。

オートフィルターで作成しましたが、もしかしたらエクセルのままとかピポッドテーブルとかのほうが適当だったかもしれません。
解決するのならどの方法でも構いません。
具体的に教えていただけると助かります。
宜しくお願いいたします。

回答No.9

tom04 · ベストアンサー

No.3・4・6・7です。

何とかご希望通りになって良かったです。
補足の
＞どうしても作業列の値が1,6,4,5,9になってしまいます。
の件に関してはNo.4での数式ですとこれで大丈夫です。
画像はNo.3の方の数式でしたのであのように5桁になっていました。
No.3の方の数式は無意味ですので、No.4に訂正したまでです。
（No.3の方でも同じ結果にはなりますが・・・）

次に
＞この数値自体が何に反応して構成されているのかスゴク不思議です。
＞こちらは急ぎませんのでご面倒でなければまた教えてください

は、ここで数式の説明をするより実際に手を動かしてもらった方が判りやすいと思います。

前回の作業列（H列）より右側は使用していないとして・・・
J2セルに
=IF(B2="","",RANK(B2,B:B,2))
という数式を入れオートフィルで下へコピーしてみてください。
これでB列数値の小さい順のランクが表示されます。

K2セルに
=IF(COUNTIF(B$2:B2,B2)=1,J2,"")
という数式を入れこれもオートフィルで下へコピー！
これでJ列に表示されている数値で、B列に最初に出現した行だけが表示されます。
ここまでが前回の作業列（H列）の操作になります。
（前回の数式の意味は、B列が空白、またはB列が2回目以降の出現の場合は空白に、
それ以外、すなわち初出現の場合はB列の小さい順からの順位を！というコトになります）
※　ここまでがB列データを重複なしに小さい順に表示させるための準備段階です。

次にL2セルに
=IFERROR(SMALL(K:K,ROW(A1)),"")
という数式を入れオートフィルで下へコピー！
これでK列に表示されている数値を空白なしに小さい順に表示させます。
※　数式内の　ROW(A1)　部分はA列でなくても構いません（Z列やAA列等他の列でも、同じ結果になります。
数式を入れたセルが　ROW(A1)=1　となりますので、
これはSMALL関数の1番小さいもの！　というコトです。
これを下へオートフィルでコピーするたびに　
ROW(A2)=2　ROW(A3)=3　ROW(A4)=4　・・・　といった具合に2番目・3番目・4番目・・・と順に表示されます。

M2セルに
=IF(L2="","",INDEX(B:B,MATCH(L2,K:K,0)))
という数式を入れ下へコピー！

M列はL列の数値と一致する行のB列を表示させるためにINDEX関数とMATCH関数を併用しています。
結局このL・M列の操作が前回のSheet2のA列の数式となります。

これでSheet2のA列にSheet1のB列データが重複なしに昇順に表示されるという訳です。

最後は単にSUMIF関数で合計する訳ですが、列を決定するためにOFFSET関数を使っています。

この程度でよろしいでしょうかね？m(_ _)m

お礼

何度も何回もありがとうございました。

作業列の部分、大分理解できました。
＞ここで数式の説明をするより実際に手を動かしてもらった方が判りやすいと思います。

わざわざ例題まで作成していただき、お手数かけました。
早速やってみました。実際に前回のシートに反映させてみたらこの私でも理解できました。
本当に感謝です！

また機会あれば色々と教えてください。
※関数、私もこれを機会にもっと勉強してみます。
覚えたら、楽しそう♪
ありがとうございました。

回答No.8

noname#191575

No.2です。

すみません、一つ書けば後はわかるかな？と・・・
申し訳無かったです。

=SUMIF(Sheet1!B2:B11,A2,Sheet1!D2:D11)

もしA2と同じ数字がSheet1のB2～B11にあるならば、A2との数値と同じSheet1の行のD列を計算しなさい。
という命令文です。

また、「$」マークですが、これは絶対値と言います。
簡単な数式で説明しますが、
B1に「=A1」という数式を入れると、B1にはA1と同じ数字が入ります。
これをB2にスライドさせると「=A2」となります。
B2の値をA2の数値にするならこれで良いのですが、B2にもA1の数値を入れたい場合はA1でないといけません。
そこでB1の数式の「=A1」の1の前に「&」を入れ、「=A$1」とします。
するとこの数式を下にスライドさせても「=A$1」のままになります。

同じようにB1の数式を右横にスライドさせるとAがB、Cと変化していきます。
Aを変化させたくない場合は「=$A$1」とします。
これなら上下左右のどこにスライドさせても「=$A$1」のままとなります。

さて、本題の数式ですが
基準となる数値は『Sheet2のA1』になりますが、A2～下に移動します。
しかしA列は変化しませんので、正しくは「$A1」となります。

このA1と同じ数字がどこにあるかはSheet1のB列ですので、「B」には「$」をつけます。
行ですが2行目から始まりますので、「$B2」から下に伸びます。
でもこの2行目からというのは変化しませんので「$B$2」となります。
同じようにB11まで下に伸びていますので、「$B$11」となります。

最後同じように、計算の元となる数字がSheet1のD列ですので、「$D$2:$D$11」としたい所ですが、回数や客数にも反映させたいのであれば「D列」を固定させない方が便利だと思います。
固定しなければ、右にスライドさせると「E」「F」と変化しますので。
なので「D$2:D$11」が正解になります。

よって数式は
=SUMIF(Sheet1!$B$2:$B$11,$A2,Sheet1!D$2:D$11)
となります。

最後にこの方法で使用する場合、Sheet2のD列に何か入っていますよね？
Sheet1は金額・回数・客数の順番ですので、同じ順番に並べなければ単純にスライドだけでは無理で、一つずつE、Fと打ち直さなければいけなくなります。
できればSheet2のD列別の場所にF列の右横に移動させる方が便利です。

お礼

ありがとうございます。

できました。
＄と＄で囲めば良いのですね。
これからも色々と活用出来そうです。

また何かありましたらお知恵拝借させてください。
本当にありがとうございました。

回答No.7

tom04

何度もごめんなさい。

もう一度補足を読み返してみました。
＞(1)まず作業列の値が私のだとtom04さんと違って1、6、4、5と表示されていました
というコトですので、B列はちゃんと数値での入力ですね！
前回の回答は無視してください。

次に
＞(2)またSheet1のＡ２セル、Ｃ２セルには計算式は入れられましたが無反応でした。セル表示は空白のままでした

Sheet1の作業列にちゃんと数値が表示され、A列が無反応はちょっと考えにくいのですが・・・
IFERROR関数を使っていますので、何らかのエラーになっているのが原因だと思います。
今一度、数式の参照列等間違いがないか確認してみだください。
もし、A列が空白の場合はC列に入れた数式も当然空白になってしまいます。
根本的な原因はA列の数式のような気がします。

他の原因としては、Sheet1の項目名とSheet2の項目名が一致しないとエラーになります。
SUMIF関数で列参照するのに、項目名でSheet1の列を決めていますので、
これが違った場合でも空白になってしまいます。

文面から推測できることといえばこの程度ですが、ごめんなさいね。m(_ _)m

回答No.6

tom04

No.3・4です。
補足について

（1）は
Sheet2のA列にSheet1のB列データ（住所ID）を重複なしに昇順に表示させるための操作です。

（2）に関して・・・
もしかして、Sheet1のB列の表示形式「文字列」になっていませんか？
今までの回答はB列「ID」は数値で表示形式がユーザー定義から　000　となっている！という前提の回答でしたので
文字列だと、Sheet1の作業列もエラーになると思います。
万一文字列の場合は「数値」にしてやる必要がありますので、
前回使っていないSheet1のG2セル（G列でなくても構いません）に
=B2*1
という数式を入れ、B列最終行までオートフィルでコピー　→　G2～最終行を範囲指定　→　右クリック　→　コピー
→　B2セルを選択　→　右クリック　→　形式を選択して貼り付け　→　「値」を選択しOK
最後にB列すべてを範囲指定　→　右クリック　→　セルの書式設定　→　「表示形式」タブで「ユーザー定義」を選択　
→　「種類」のところに入っているデータを消去　→　000　と入力しOK

これでSheet1のB列は3桁表示になり、ちゃんと表示されると思います。
（当選のことながらSheet2のA列のセルの表示形式も同様に設定します）

※　今後データが増えていく場合、B列は普通に数値として入力していきます。

不具合の理由としてはこのくらいしか思いつきません。m(_ _)m

補足

ご連絡大変遅くなりました。

出来ました♪
多分前回出来なかったのは、入力ミスでしょう。
お騒がせしました。

この文章の式をこのままコピー出来たのですね。
tom04さんの数式をそのままコピペしたら一発で出来ました。
本当にありがとうございます。

これから5000件ほどのデータですが、応用してやってみます。

※また、これは関係無いのですが、どうしても作業列の値が1,6,4,5,9になってしまいます。
tom04さんは5桁だったのにどうして私は１桁なの・・・
また、この数値自体が何に反応して構成されているのかスゴク不思議です。
こちらは急ぎませんのでご面倒でなければまた教えてください。
厚かましいお願いばかりで申し訳ありません。

取り急ぎ「出来た！」の報告と御礼まで。

回答No.5

noname#191575

No.2です。

画像をもう一度確認しました。
Sheet1の『住所ＩＤ』はB列でしたので

=SUMIF(Sheet1!$A2:$A11,$A2,Sheet1!$D2:$D11)
ではなく
=SUMIF(Sheet1!$B2:$B11,$A2,Sheet1!$D2:$D11)
でした。

申し訳ございませんでした。

補足

何度もありがとうございます。

訂正してやってみました。
Ｃ２は2500となりましたが、Ｃ５（住所ＩＤ021）の買上金額は2000となってしまいました。
正解は7000なので計算式を見てみると、=SUMIF(Sheet1!$B5:$B14,$A2,Sheet1!$D5:$D14)
と絶対値では無く１づつずれていました。

Ｃ２を単純に下と横にコピペしたからいけなかったのでしょうか。
簡潔な方法だっただけに残念です。

回答No.4

tom04

No.3です。
たびたびごめんなさい。

投稿後に気づきました。
前回の作業列の数式はもっと単純でよかったです。
Sheet1のH2セルの数式を
=IF(OR(B2="",COUNTIF(B$2:B2,B2)>1),"",RANK(B2,B:B,2))
に変更してください。

※　Sheet2の数式はそのままです。

どうも失礼しました。m(_ _)m

補足

ご丁寧に画像までありがとうございます。

でもうまくいきません・・・＞＜

(1)まず作業列の値が私のだとtom04さんと違って1、6、4、5と表示されていました。
多分、桁・単位の違いなのかなと思ったのですが良く分かりません。
また、この作業列自体どういう意味があるのでしようか？
(2)またSheet1のＡ２セル、Ｃ２セルには計算式は入れられましたが無反応でした。セル表示は空白のままでした。

おそらく何か私の方で問題があるのでしようから再度確認、やってみています。

取り急ぎ、(1)の要因だけでも教えていただけると助かります。
素人質問ばかりでゴメンなさい。
宜しくお願いいたします。

回答No.3

tom04

こんにちは！

オートフィルタを使う場合は　
SUBTOTAL関数 =SUBTOTAL(9,"参照範囲")
といった方法がありますが、表示されているデータのみの集計になってしまいます。

今回の質問は「住所ID」ごとの集計を行いたい！というコトのようですので、
↓の画像のように別Sheetに表示させる方法です。

画像では上側がSheet1で下側がSheet2とします。
Sheet1に作業用の列を設けています。
作業列H2セルに
=IF(OR(B2="",COUNTIF(B$2:B2,B2)>1),"",RANK(B2,B:B,2)*10000+ROW())
という数式を入れオートフィルでずぃ～～～！っと下へコピーしておきます。

次にSheet2のA2セルに
=IFERROR(INDEX(Sheet1!B:B,MATCH(SMALL(Sheet1!H:H,ROW(A1)),Sheet1!H:H,0)),"")
という数式を入れます。

C2セルに
=IF($A2="","",IFERROR(SUMIF(Sheet1!$B:$B,$A2,OFFSET(Sheet1!$A:$A,,MATCH(C$1,Sheet1!$1:$1,0)-1)),""))
という数式を入れ、F2セルまでオートフィルでコピー！
最後にA2～F2セルを範囲指定　→　F2セルのフィルハンドルで下へコピーすると
画像のような感じになります。m(_ _)m

回答No.2

noname#191575

Sheet2のC2なら
=SUMIF(Sheet1!$A2:$A11,$A2,Sheet1!$D2:$D11)
でどうですか？

補足

ありがとうございます。

やってみたのですけれど、なぜか「0」が表示されました。
再度試みてみます。

回答No.1

mshr1962

ピボットテーブルの値取得用の関数があります。

GETPIVOTDATA 関数

例
=GETPIVOTDATA("金額",Sheet1!$A$4,"住所ID","1")

参照URL
http://www.eurus.dti.ne.jp/~yoneyama/Excel/kansu/getpivotdata.htm