データの抽出の仕方

質問

エクセルの表で、月別の店別売上の集計を行ないたいのですが、データの抽出がうまくいきません。

表は、月別で支店が50あるとして、横に4～3月の12ヶ月のデータがあり、縦に支店があるような表の形式です。支店の横に、その店で扱っている商品を入れています。

抽出したいデータの条件は、
(1)○支店の○月の単月の売上金額は？
(2)○支店の4月から○月までの累計売上金額は？
(3)支店により扱っている商品が違うので、全支店から○商品を取り扱っている支店の売上合計は？
といった3条件で毎月金額を抽出したいのですが、関数がうまく作れません。

何かいい方法やよく似たフォームをご存知の方がいましたら、教えて下さい。

回答No.1

fujillin

質問が具体的でなく一般形なので、回答も一般形になるので、理解しにくい表現になってしまいますが・・・
（詳しくは関数のヘルプ等を見て確認してください）

（１）表から値を読み取る形式なので、表のインデックス（行番号、列番号）がわかれば読み出せますよね？
例えばINDEX(領域、行番号、列番号）です。参照を簡単にするためには、各種の領域（表など）に名前を付けておくと便利です。
月は検索するまでもないかも知れませんが、支店名（行）は直接行番号を入力しないでしょうから、支店名で検索するとしましょう。
検索支店名が完全だとして、MATCH（検索支店名、支店名領域名、0）で支店の行番号が得られますので、これとINDEXを組み合わせることで、参照ができます。

（２）指定された支店の4月～3月のデータ範囲に対してｎ月までの合計を求めるには、配列数式で=SUM(IF(月の範囲<対象月),データ範囲)で求めることが可能です。（配列数式なのでCtrl + Shift+ EnterでFix）
前半の条件式は月で計算すると4～3の順なので面倒ですから、比較する月をMとした場合、IF（Columns（ダミー範囲）<M＋α）などとすると便利です。４～３月まで並んでいると仮定して、列番号で比較していますので「ダミー範囲」は４～３月までの１行分の範囲指定です。（列番号しか見ていないので、行は関係ありません。）
αは列番号と実際の月Mとに差が出るはずなので、その調整をするための定数で、データの位置によって決ります。
後半の合計する対象の1行の範囲は、（１）の支店名検索で求められるので、これを用いて定義します。求める行番号を「支店行」と表現すると。INDIRECT("4月列" & 支店行 & ":3月列" & 支店行）で指定することができます。
「4月列」「3月列」はデータの入っている列を意味しています。

（３）商品データの記載方法がよくわかりませんが、（１）、（２）が理解できればその応用で可能だと思われます。

・・・とここまで書いたら、支店名は縦に重複して商品名回並んでいるのでしょうか？という疑問が湧いてきました。
上の回答は支店は重複しないとし思って書いていましたので、計算の対象は1行と仮定していましたが、複数ある場合は縦方向方向にも集計が必要になります。

関数でやっているとさらに複雑になってくるものを、一般論で書くのは面倒すぎるので、各種テンプレートを検索されて見てはいかがでしょうか？
＜例＞
　http://www.juno-e.com/top-eigyo.html
　http://members.jcom.home.ne.jp/t.quantz/soft.files/soft.htm