下記のような表があったと仮定します。
A B C D
1 月度 店舗 品目 売上金額
2 4 A あ 22300
3 4 B あ 18700
4 4 C あ 14500
5 4 A い 17950
6 5 B あ 44000
7 5 C い 35000
8 5 A う 12000
9 5 B い 13400
この表から、
店舗「A」、品目「あ」の売上合計は、22300になり、
数式「=SUMPRODUCT(($B$2:$B$9="A")*($C$2:$C$9="あ")*$D$2:$D$9)」で計算できます。
店舗「A,B」、品目「あ」の売上合計は、85000になり、
数式「=SUMPRODUCT(($B$2:$B$9={"A","B"})*($C$2:$C$9="あ")*$D$2:$D$9)」で計算できます。
店舗「A」、品目「あ,い」の売上合計は、40250になり、
数式「=SUMPRODUCT(($B$2:$B$9="A")*($C$2:$C$9={"あ","い"})*$D$2:$D$9)で計算できます。
しかし、
店舗「A,B」、品目「あ,い」の売上合計は、116350なのですが、
数式「=SUMPRODUCT(($B$2:$B$9={"A","B"})*($C$2:$C$9={"あ","い"})*$D$2:$D$9)」では、数値がおかしく(35700に)なります。
数式の使い方が間違っているのでしょうか?
アドバイスをお願いします。
No.1ベストアンサー
- 回答日時:
「A」が「あ」と、「B」が「い」の合計ですね。
こちらの数式の原理
http://miyahorinn.fc2web.com/faq/faq100.html
SUMPRODUCTとか配列関数とかを熟知してませんので、もっとましな方法があるかも知れませんけど、、、
=SUMPRODUCT(($B$2:$B$9={"A","A","B","B"})*($C$2:$C$9={"あ","い","あ","い"})*$D$2:$D$9)
とか。
紹介していただいた数式で、答えは求めることができました。
なぜそうなるのかは理解できていないため、これから勉強していきます。
今回はありがとうございました。
No.6
- 回答日時:
こんにちは。
#5の回答者です。
>いろいろな方法があるのですね。
>なぜそうなるのかは理解できていないため、これから勉強していきます。
ちょっと思うことですが、いくつかの方法は、掲示板ならではの回答だと思います。
掲示板の回答って、省スペースなものになる傾向が強いようです。
ただ、やっぱり
>補助列を使って、
>
>E2: ~下へ
>=AND(OR(B2="A",B2="B"),OR(C2="あ",C2="い"))
>
>=SUMIF($E$2:$E$9,TRUE,$D$2:$D$9)
の方法が、オーソドックスではないでしょうか?
自分の書いたものでも、後で読みきれない数式がありますから、納まりの良さというものは、実務でも、必ずしも必要ないと違いますか?
わざわざ補足ありがとうございます。
確かに理解していない数式を使うと後々、苦労するかも知れませんね。
補助列の場合は、後から見ても判りやすいので状況に応じて、
使い分けしようと思います。
No.5
- 回答日時:
こんにちは。
----------------------------------
1. データベース関数なら、
DataBase:
=$A$1:$D$9
Criteria:
=$H$1:$I$5
H I
店舗 品目
A あ
B い
A い
B あ
D1: (念のため)
売上金額
=DSUM(database,D1,Criteria)
----------------------------------------
配列数式なら、
=SUMPRODUCT(($B$2:$B$9&$C$2:$C$9={"Aあ","Aい","Bあ","Bい"})*$D$2:$D$9)
複雑になると、ミスが多くなりそうです。
----------------------------------------
補助列を使って、
E2: ~下へ
=AND(OR(B2="A",B2="B"),OR(C2="あ",C2="い"))
=SUMIF($E$2:$E$9,TRUE,$D$2:$D$9)
という式でもよいと思います。
-----------------------------------------
いろいろな方法があるのですね。
なぜそうなるのかは理解できていないため、これから勉強していきます。
今回はありがとうございました。
No.4
- 回答日時:
($B$2:$B$9={"A","B"})という式が返す結果は
{TRUE,FALSE;FALSE,TRUE;FALSE,FALSE;TRUE,FALSE;FALSE,TRUE;...}
という2列の配列になります。これを踏まえて、
($C$2:$C$9="あ")という式は
{TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE}
という1列の配列なのですが、それでは計算出来ないので列数の一番
大きな配列に揃えて
{TRUE,TRUE;TRUE,TRUE;TRUE,TRUE;FALSE,FALSE;TRUE,TRUE;...}
と扱います。これが2番目と3番目の式で一見or条件での計算が成り
立っていた理由です。
ここで、($B$2:$B$9={"A","B"})*($C$2:$C$9={"あ","い"})とすると
{TRUE,FALSE;FALSE,TRUE;FALSE,FALSE;TRUE,FALSE;FALSE,TRUE;...}
と
{TRUE,FALSE;TRUE,FALSE;TRUE,FALSE;FALSE,TRUE;TRUE,FALSE;...}
のANDになるので、結果はpapayukaさんやimogasiさんの喝破したと
おり、(A and あ) or (B and い)だけがTRUEになってしまいます。
というわけで、中括弧は配列の記述だと決まっているのにor条件の
つもりで使うのは危険であることの解説でした。解決法は、他の回
答者の皆さんがおっしゃるとおり、マジメにor条件を評価していく
ことですね。
なぜだめか、詳しく回答していただきありがとうございます。
この手の、複数列の配列数式についてのサイトがありましたらご紹介をお願いします。
No.3
- 回答日時:
>店舗「A,B」、品目「あ,い」の売上合計は
この意味があいまいでしょう。質問者には判っているが、一般的に、こう書いても判らない。文章で正確に表現すべし。
ただ合計116350だと質問者が言うので、それに当たる組み合わせを、回答者が探さないといけない。
"→第2条件
↓第1条件"あいう
A○○✕
B○○✕
C✕✕✕
らしい。
ーー
配列数式でやると
=SUM(IF(((B2:B9="A")+(+B2:B9="B"))*((C2:C9="あ")+(C2:C9="い")),D2:D9))
と入れて
SHIFTとCTRKとENTERキーを押す。 結果 116350
ーーー
SUMPRODUCTの書き方に書き換えると
=SUMPRODUCT(((B2:B9="A")+(+B2:B9="B"))*((C2:C9="あ")+(C2:C9="い"))*(D2:D9))
結果 116350
両方とも「+」は「または」の場合に使う。「*」は「および」の意味で使うのは、ご存知でしょう。
{"A","B"}は確かに両方の要素について行うことになると思うが、
なぜダメなのか、小生の知識が整理できていないので、自分流の回答をとりあえず出す。
選択条件A,Bなどの項目数が多いと、そういう表現ができればよいですが。
>この意味があいまいでしょう。質問者には判っているが、一般的に、こう書いても判らない。文章で正確に表現すべし。
申し訳ありません。今後の教訓にいたします。
>両方とも「+」は「または」の場合に使う。「*」は「および」の意味で使うのは、ご存知でしょう。
そうなんですか?知りませんでした。これから勉強して理解します。
今回はありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- その他(お金・保険・資産運用) 至急!【Wolt】各メニューの価格設定の簡単な計算方法 3 2023/03/05 11:58
- Excel(エクセル) Excelの計算式についてお教えください 8 2023/06/05 10:01
- Excel(エクセル) 【!】Excel 2つの条件付き書式が反映されません。。 5 2023/07/14 16:47
- Excel(エクセル) マクロだと数式が表示される 2 2022/09/10 14:48
- Excel(エクセル) Excel(エクセル)でフィルター抽出後、非表示の行を計算しないで、合計を算出する方法 【内容】 添 4 2023/01/30 17:17
- Excel(エクセル) エクセルの遅刻計算について 3 2023/01/15 13:31
- Excel(エクセル) SUMIFのIF分岐について 4 2023/04/15 12:57
- Excel(エクセル) エクセルでSUMIFS関数で条件範囲の部分が#valueになる。 4 2023/04/28 12:42
- Excel(エクセル) Excel関数の質問です。 5 2022/04/23 12:53
- 高校 日商簿記3級の勉強中なのですが 精算表が完成せず困っています。 こちらの問題の回答を教えていただきた 2 2023/03/02 09:07
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Excelの関数について教えてくだ...
-
VBAについての質問です
-
Excelにいついて質問です。
-
一つのセルに(例えばA1)入力...
-
エクセルファイルがファイルの...
-
【マクロ】名前を保存する際に...
-
Excel 複数列 A列B列C列一致 D...
-
エクセル vbaについて教えてく...
-
エクセルであるセルに数字を入...
-
Excelについて教えてください
-
エクセル マクロ チェックボックス
-
【マクロ】顧客番号にて一致さ...
-
エクセル共有したが、アクセス...
-
Excel分析ツールでのポアソン回...
-
【Excel】数字を3倍にし、なお...
-
エクセルのデーターが2か月前の...
-
LOOKUP関数を使えばいいのでし...
-
エクセルの神よ、ご回答を! エ...
-
Excelでセルの値が同じか...
-
エクセルを使っていて2024/5/15...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報