Excel 関数を使う？ 抽出データ

質問

以下について教えてください。
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
【基になる表--「A」の表と呼ぶことにします。】
　列方向のリストには左から「月」「日」「地域」「数」が並んでいる。
　「月」には１月から１２月までのデータが、
「日」には１日から３０もしくは３１日のデータが、
「地域」には北海道、青森、新潟、東京、大阪の５つの地域が、
「数」には売上数が並んでいるとする。

【基になる表から抽出して作られた表--これから便宜上「B」の表と呼ぶことにします。】
　列には「地域」の中から北海道と青森を、
行には「月」から９月を、「日」から１５日を、
列と行が交差するセルには「数」を置き、「９月１５日の北海道と青森の売上数」の合計を抽出したい。

　さらに、Bの表において、
「地域名」の北海道を削除して新潟にしたり、９月１５日ではなく９月１８日に置き換えても、
列と行が交差するセルに自動的に集計結果が表されるようにしたいのです。

　ちなみに、Aの表もBの表も同じブックに作り、シートは別にします。
===================================
　DSumやIfSum関数だと私が作りたいBの表のような形式になりません。列方向にしかラベルを置けません。
　ピボットテーブルを使った集計ならば、求めているデータ以外のデータも表示されてしまいます。
　どうすれば求めていることができるでしょうか？どうか皆さんの知恵をお貸しください。

回答No.3

imogasi · ベストアンサー

#1です。
補足はありがとうございました。
したいことは明確になりました。
Ｓｈｅｅｔ１のＡ１：Ｄ８に
Ａ列　　Ｂ列　　　Ｃ列　　　Ｄ列　　　　　Ｆ列
月 日 地域 売上高
1 10 北海道 1
3 21 青森 2
5 7 東京 3
9 15 北海道 4 1
9 20 大阪 5
10 16 大阪 6
9 15 青森 7 2
Ｆ列をワーク列とします。
Ｆ２に=IF(SUMPRODUCT((A2=A$15)*(B2=$B$15)*(C2=$C$14))+SUMPRODUCT((A2=A$15)*(B2=$B$15)*(C2=$D$14))=1,MAX($F$1:F1)+1,"")
と入れてＦ８まで式を複写。結果は上記の通り。
Ｓｈｅｅｔ２に本来出すべきですが、同シートで済ませました。
Ａ１４：Ｄ１５に
北海道 青森
9 15 4 7
Ｃ１５に=INDEX($A$1:$D$8,MATCH(COLUMN()-2,$F$1:$F$8,0),4)
Ｃ１５の式をＤ１５へ式を複写・
結果は上記の通り。（imogasi方式）
結果をＳｈｅｅｔ２で実現するときの式はＳｈeeｔ１！などを添えて、修正してください。
これも県を３つ４つと増やす、日を２つ以上に増やすと、式が複雑になってお手上げです。
そうなると、ＶＢＡを使わざるを得ない気がする。

お礼

うわぁっ～、すごいですね！！！拍手しちゃいました。
　わかりにくい説明で申し訳なかったのですが、
とってもご丁寧に、再度ご回答いただけたことに、
非常に感謝しています。
　ほんとうにありがとうございます！！！
　実際に今から自分でやってみて、明日上司に堂々と報告できます！
　ほんとうれしーです。
　ありがとうございました！

補足

ちなみに、どうすればimogasiさんのような深い知識を持てるのでしょうか？
　お仕事はプログラミングかソフト開発をされていらっしゃるのですか？
　それくらいの深い知識をお持ちであれば、
普通に事務員として会社にいても、とっても重宝されそうですね。

回答No.4

Wendy02

Dsum では、補助セルを置かない限りは、ご指摘のような表示には向きません。

失礼ですが、
http://oshiete1.goo.ne.jp/kotaeru.php3?q=1626757

で解決したなら、こちらは閉じてください。そちらを読んでいますから、こちらに、いままで書いてくれた人は、もう書かないと思います。

回答No.2

Wendy02

こんにちは。

＞　DSumやIfSum関数だと私が作りたいBの表のような形式になりません。列方向にしかラベルを置けません。

列方向にしかラベルを置けません、というのは、具体的にどういうことですか？
ラベルってなんでしょうか？タイトル行のことですか？具体例がないので分りませんが、

=DSUM(Database,フィールド,Criteria)

単に、クライテリアの作り方の問題だと思いますが、そうではないのでしょうか？
一体、どんな書き方をしているのでしょうか？

A　　 B　　 C　　　D
----------------------
月　　日　 地域　　数　　←Database 部
8　　 1　　大阪　　13
8　　 2　　東京　　16
8　　 4　　青森　　14
8　　 4　　東京　　17
8　　 5　　北海道　13
8　　 5　　新潟　　 1
8　　 8　　東京　　11
8　　 8　　大阪　　19
8　　15　　北海道　 4
8　　15　　新潟　　 7
8　　15　　青森　　 9
8　　16　　北海道　 8
8　　16　　大阪　　19
8　　17　　東京　　20

別のシート
月　日　 地域　　　　　←Criteria
8　 15　 北海道
8　 15　 青森

=DSUM(database,"数",criteria)

答え:13

ただし、Criteriaは、空の行を入れると、それを検索させてしまいますから、全てという意味になります。注意が必要です。

他の方法もありますが、データベース関数を使うのでしたら、こうなるかと思います。

お礼

とってもご丁寧なアドバイスをありがとうございました！！
　ご指摘の通りに、質問締め切ります。

補足

わかりにくくてごめんなさい。大変ご丁寧にありがとうございます！！！
　例示しようと思いましたが、全部言いたいことをいえなかったら・・と思い、例示しませんでした。

　基になる表は、ご回答者様がご回答に例示してくださった通りです。
　抽出したデータで作られた表は、以下のようにしたいのです：

　　A　B　　C　　　　　D
１　　　　｜北海道　　青森　　　
２　　　　｜---------------------　　
３９月１日｜２５　　　３０
４９月８日｜４５　　　３５

　説明：セルC１とD１には地域名を置く。セルA３には月、セルB３には日付を置く。
他の地域名や月日をそのセルに入力すると、基の表からデータが引っ張られ、セルC３、C４、D３、D４には合計が自動的に集計されるようにしたい。

＞　DSumやIfSum関数だと私が作りたいBの表のような形式になりません。列方向にしかラベルを置けません。

　ラベルとは、ラベル名のことであり、「月」「日」「地域」などのことです。

　また質問し直した方がいいかな・・。
　とりあえず、補足をさせて頂きますね。もしよろしかったら、再度ご回答下されば嬉しいです。

回答No.1

imogasi

ＤＳＵＭの問題と思ったが、そうでないと。
質問が長いが、例示すればわかりやすいかも（ちなみに私の回答はほとんど例示つきです）。
Ｓｈｅｅｔ１のＡ１：Ｄ８に
月 日 地域 売上高
1 10 北海道 1
3 21 青森 2
5 7 東京 3
9 15 北海道 4
9 20 大阪 5
10 16 大阪 6
9 15 青森 7
そして
条件はＢの表に置くのですか
検索結果はＢの表に出すのでしょうね
その結果はどのようにすればよいのですか。
Ｓｈｅｅｔ２に
9 15 北海道 4
9 15 青森 7
売上高合計はどこに出す？
＞列と行が交差するセルには「数」を置き、「９月１５日の北海道と青森の売上数」の合計を抽出したい。
の意味がよくわからない。
・合計だけでよいのか　これならＤＳＵＭでよいはず
・明細を出すのか
・明細を出すなら、どのセルに出すのか（交叉するセル？

お礼

わかりにくくてごめんなさい。大変ご丁寧なご回答をありがとうございます。
　例示しようと思いましたが、例示しませんでした。申し訳ないです。

　Sheet1にある基になる表は、ご回答に例示してくださった通りです。
　Sheet2にある抽出したデータで作られた表は、以下のようにしたいのです：

　　A　B　　C　　　　　D
１　　　　｜北海道　　青森　　　
２　　　　｜---------------------　　
３９月１日｜２５　　　３０
４９月８日｜４５　　　３５

　説明：セルC１とD１には地域名を置く。セルA３には月、セルB３には日付を置く。
他の地域名や月日をそのセルに入力すると、基の表からデータが引っ張られ、セルC３、C４、D３、D４には「求めたい地域名の、なおかつ、求めたい月日の『売り上げの合計』」が自動的に集計されるようにしたい。

　抽出したデータで作られたsheet2にある表はとても単純なものですが、DSumをつかうと、「月」も「日」も、列方向に置かないと計算結果をだせません。
　また、ピボットテーブルでの集計は、いらない地域の売り上げ合計や月日までも表示されてしまいます。それならいらない行なり列を、「表示させない」を選択して折り畳んでしまえばいいんじゃないか、と私は思ったのです。しかし、これは会社の上司は「必要なデータだけが表示される単純明快な表」を望んでいます。上司は「確かこんな表を作れる関数があったはずだ・・」というのです。

　また質問し直した方がいいでしょうかね。
　とりあえず、補足をさせて頂きます。
　もしよろしかったら、恐縮ですが、再度ご回答下されば嬉しいです。
　長くてごめんなさい。