エクセルで条件を複数選んだ合計値を出したいです。

質問

関数初心者です。
ネットや本で調べ、試行錯誤の結果何通りか関数（数式？）を試しましたが、一番求めているものに辿り付けませんでした。

本物の表はもっと項目がありますが、簡単にすると↓です。

「　　　　Ａ 　 　　Ｂ　　　　Ｃ　　　　Ｄ　　　　Ｅ　　　　Ｆ
　１　　　 　　　　5/1　　　 5/2　　 5/2　　　 5/3 　　 5/4 　　　 ←出荷日　
　２　 　　　　　　あ社　　い社　　あ社　　　あ社　　　い社　　　　←客先
　３ No.12345 　 40　 　 　20　　　 80 　 　 　20 　　　　　　　　　　←出荷数
　４ No.67890 　　　　　　　　　　　　　　　　 　20　　　　50　　　　 ← 〃
　５　　　　　　　　40 　　　 20　　　 80　　　　 40　　　 50 」 　　 ←出荷数の合計
　　↑
　オーダーNo.

この表から、2007年5月に出荷した全体の数量を客先別に計算したいのです。（2007年を条件にすることはそんなにこだわっていません。）

今、とりあえず成功している式は、
=SUMPRODUCT((YEAR(B1:F1)=2007)*(MONTH(B1:F1)=5)*(B2:F2=A8),B5:F5)
※A8にあたるセルには客先名が入っています。
※日付の書式は「日付」にしました。

何が問題かというと、この表は出荷する度に横に列を増やしていくので、YEAR、MONTHの範囲（シリアル値？）を広げたいのですが、(B1:BZ1)などと列を増やしてしまうと、「#N/A」になってしまいます。

その他にも色々試しました。
（例）
=SUM(IF((B1:BZ1="2007/5/*")*(B2:BZ2=A44),B5:F5))
エラーは「#VALUE!」です。
日付の書式をかえてみたり、「"=2007/5/*"」を「"5/*"」にかえてみたりすると、何らかの計算はされることもありましたが、どこをどう計算しているのか求めている合計値ではない数値が出てきました。

また、思い切って出荷日の行を増やし、上段に「月」下段に「日」と分けてしまった方が、簡単でしょうか？

ご回答お待ちしています。よろしくお願いします。

回答No.1

redowl · ベストアンサー

サンプルの既存データの訂正は無しで、セル３カ所（Ａ８～Ａ１０）を下記のように使用。

A8 に　　数値　（１～１２　の範囲）※　後から、セルの書式設定で　　表示形式を「　○○月　」にする。
A9　に　社名（　あ社、い社、・・・・）

A10に、
=SUMPRODUCT((MONTH(B1:AZ1)=A8)*(B2:AZ2=A9)*(B3:AZ5))
この式は、A列内に　配置

条件１　MONTH(B1:AZ1)=A8・・・・・・Ａ８に一致する　「月」を　　　B1:AZ1領域から選択
条件２　B2:AZ2=A9・・・・・・Ａ９に一致する「社名」を　　　B2:AZ2領域から選択
B3:AZ5領域で、条件１と条件２　を同時に満たす　列　、総和を　SUMPRODUC関数で算出。

オーダーNoの行と　月日列を増やした場合の式　（１００行目,２５６列目　まで）
　=SUMPRODUCT((MONTH(B1:ＩＶ1)=A8)*(B2:IV2=A9)*(B3:IV１００))

条件に　「年」は、組み込んでいないので、１２ヶ月を１単位として使う。
（エクセルの　列の上限は　２５６列までなので、実質８ヶ月分まで。　Excel９７になれば16384列まで）

お礼

解決しました。ありがとうございます。
月を「5」月で指定するより、セルに「5」を入れて指定する方が応用もききますよね！
勉強になりました。

回答No.2

nishi6

何らかの事情があると思いますが、質問の表は１列が１データで、横方向に追加するように作られています。
データ数が多くなったり、オーダーNo.の処理方法に疑問が残ります。

このようなデータ処理を行うときには、横方向にデータ項目を展開していった方が管理しやすいでしょう。
また、１つのデータに複数のオーダーNo.があったりすると計算が複雑になります。

下は、質問の表を縦横逆にして、出荷No.としてオーダーNo.を追加しています。
このように作ることで、ExcelのCountIfやSumIf関数、フィルタ機能、並べ替え機能、集計機能、ピボットテーブル、統合機能などが使えるようになります。
質問の表ではこのようなExcelの機能を使いづらくしており、何をするにも大変でしょう。

＿＿Ａ＿＿＿Ｂ＿＿Ｃ＿＿＿Ｄ
１ 出荷日　客先　出荷No.　出荷数
２　　5/1　　あ社　12345　　40
３　　5/2　　い社　12345　　20
４　　5/2　　あ社　12345　　80
５　　5/3　　あ社　12345　　20
６　　5/3　　あ社　67890　　20
７　　5/4　　い社　12345　　50

お礼

ご指摘ありがとうございます。
簡単な表で説明してしまったのですが、もうちょっと表が複雑なんです。
まずシステムからデータを落とした時点で、
オーダーNo.　保管場所　製品名　製造日　数量　入荷日　等、横一列になっているのです。
これをそのまま活かして表を作ることが目的だったので、この形になりました。

それから、出荷数だけではなく在庫数も管理しているのですよ。
こんな風です。
オーダーNo.１　　・・・・・・・・　　元の数量　出荷数　在庫数　
　　〃　（製造日違い）・・・・・　　元の数量　出荷数　在庫数　　
　　〃　（製造日違い）・・・・・　　元の数量　出荷数　在庫数
　　〃　（合計）　　　　　　　　　　（小計）（小計）（小計）
オーダーNo.２　　・・・・・・・・　　元の数量　出荷数　在庫数
　　〃　（製造日違い）・・・・・　　元の数量　出荷数　在庫数
　　〃　（合計）　　　　　　　　　　（小計）（小計）（小計）
　　・
　　・
オーダーNo.９　　・・・・・・・・　　元の数量　出荷数　在庫数
　　〃　（製造日違い）・・・・・　　元の数量　出荷数　在庫数
　　〃　（合計）　　　　　　　　　　（小計）（小計）（小計）
全体の合計　　　　　　　　　　　　　〔合計〕〔合計〕〔合計〕

でも確かに縦に加えていった方が簡単ですよね。
う～ん・・・。
上の表は表で作り、出荷履歴は別で作った方が楽チンになりますかね？？
もう少しアイデアを絞ってみます。ありがとうございました。