集計の表の数量をすばやく計算したいのですが、品種・JANとコードが混じっています。
①支店別 ✕ ②取引先別 ✕ ③年月別 ✕ ④JANor品種コード別 の数量です。
実績表から数字を充てたいのです。
今まで、実績をソートしながら手打ちしていたようです。
これから3月までずっと手打ちはちょっといやなので・・
SUMIFSとSUMIFSと足せばいいのでは?聞いたのでやってみたのですが
(画像添付 例です 元の表は実績は別ファイルです。)
=SUMIFS($M$3:$M$16,$H$3:$H$16,A4,$I$3:$I$16,B4,$K$3:$K$16,C4)+SUMIFS($M$3:$M$16,$H$3:$H$16,A4,$I$3:$I$16,B4,$L$3:$L$16,C4) 長くなってしまいました。
条件は①~③まで一緒です。④だけ違うのですが OR?が良く分からなくてエラーになってしまいます。
もっと 短くできる方法があれば教えて頂きたいです。
元の表は、他の列に別の情報・数値等があり、列は増やせない状況です。
(より詳しく知りたいものがJAN表記で、品種の中でひとつでもJAN表記する場合はその品種は全部JANにする決まりです)
どうぞよろしくお願いいたします。
No.1ベストアンサー
- 回答日時:
こんにちは
ご提示の式で結果を算出できているのですよね?
それを短くしたいという意味のご質問と解釈しました。
とは言っても、たいして短くなってはいませんけれど・・
=SUMPRODUCT((H$3:H$16=A3)*(I$3:I$16=B3)*((K$3:K$16=C3)+(L$3:L$16=C3))*M$3:M$16)
では、いかがでしょうか?
(縦方向にしかフィルコピーしないものと判断し、列番号の$記号も除きました)
※ 画像がはっきりとしないので、セル位置等を間違えているかもしれませんがご容赦。
※ ご提示の式も同様ですが、万一JANコードと同じ品種番号が存在すると、意図とは異なる結果になります。
(桁数が全く違うようなので、そのようなことは無いとは思いますが・・)
fujillin様 回答ありがとうございます。
上記式を入れてみましたが、#VALUE!になってしまいました。
また次月の実績が追加になったときに、別の列に5月 6月をあるので年月も条件にいれたいのですがどこに条件を足せばよいのでしょうか。。。
No.2
- 回答日時:
No1です
>上記式を入れてみましたが、#VALUE!になってしまいました。
めったにエラーになる式ではないのですが・・・
もしかすると、M列に数字以外の文字などが入っていませんか?
(ご提示の図の「・・・」が文字なのかな?)
No1の式にM列の値が数値であるかの判断を付け加えても良いのですが、数式が長くなってしまうので、効果がほぼなくなってしまうかもですね・・・
M列だけは「データの無いセルは空白」とすることはできませんか?
(そうすれば、エラーなく計算できるものと想像します)
配列数式にしてしまってもかまわなければ、
=SUM(IF((H$3:H$16=A3)*(I$3:I$16=B3)*((K$3:K$16=C3)+(L$3:L$16=C3)),M$3:M$16,0))
に変えることで、文字列が含まれていても計算が可能になります。
ただし、こちらの式の場合は、入力時にCtrl+Shift+Enterで確定が必要です。
(正しく入力されると、数式バーの表示が{ }付きの式に変わります)
あるいは、もしも、Office365環境をご利用なら、
=SUM(FILTER(M$3:M$16,(H$3:H$16=A3)*(I$3:I$16=B3)*((K$3:K$16=C3)+(L$3:L$16=C3)),0))
とすることで、M列に文字列があってもそれを除いて計算することが可能です。
>別の列に5月 6月をあるので年月も条件にいれたいのですがどこに条件を足せばよいのでしょうか。。
ご覧になればわかると思いますが、式が変わっても、条件の部分はみな同じ計算をしています。
個々の条件式
・(H$3:H$16=A3)
・(I$3:I$16=B3)
・・・・
などの意味はわかると思いますが、これらをANDで判定したいときは乗算を、ORで判定する時は加算することで、結果が0または1になるようにしています。
(真偽値を四則演算させると、TRUEは1に、FALSEは0に変換されますので)
ただし、ORの条件が両方同時に成立するような内容だと、加算結果が2になってしまうので注意が必要です。
(これが、No1で「JANコードと同じ品種番号が存在すると・・」の場合に該当します)
上の例示の式の、条件の部分をAND、ORを用いて書き直してみると
AND(H$3:H$16=A3,I$3:I$16=B3,OR(K$3:K$16=C3,L$3:L$16=C3))
となります。
(配列計算の場合、AND、ORの式にしてしまうと動作しなくなる場合がありますのでご注意)
もしも、条件を増減なさりたい場合は、この要領で加えたり減らしたりすれば良いです。
fujillin様 続けての解説ありがとうございます。
・・・ を消してコードの列を数字のみにしたら、最初に提示いただいた式で計算できました!
(表が続いてる雰囲気をだそうと余計なことをしました 大変失礼いたしました 図も見ずらくてすみませんでした)
20人分の資料を手打ちしなくて済みます。。。
本当に助かりました。またお知恵をお借りすることがあるかもしれませんが、どうぞよろしくお願いいたします。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) VBA シート間の転記で、条件の追加コードの書き方について教えて下さい。 13 2023/02/26 09:31
- Excel(エクセル) エクセルでSUMIFS関数で条件範囲の部分が#valueになる。 4 2023/04/28 12:42
- Visual Basic(VBA) VBAで、1つのエクセルで、2つのシートからもう1つのシートに条件のある転記コードを教えてください。 1 2023/03/16 18:07
- その他(買い物・ショッピング) JANコードの登録について 1 2022/07/23 14:19
- その他(Microsoft Office) 【スプレドシート】IMPORTRANGE関数とSUMIFSの組み合わせ 1 2023/03/05 18:17
- Excel(エクセル) DSUMとSUMIFSについて。 1 2022/09/16 00:51
- Excel(エクセル) エクセルのSUM関数について 4 2023/04/18 10:37
- Excel(エクセル) 【!】Excel 2つの条件付き書式が反映されません。。 5 2023/07/14 16:47
- Excel(エクセル) エクセルの条件付き書式 個人シートを参照して集計シートに色付けしたい 1 2023/06/22 00:39
- Excel(エクセル) SUMIFSと日付変換 10 2023/04/16 15:38
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルのデーターが2か月前の...
-
エクセルVBA、別ブックへ転記す...
-
【マクロ】顧客番号にて一致さ...
-
エクセル共有したが、アクセス...
-
エクセル②
-
(マクロ)データをAブックからB...
-
Excelでセルの値が同じか...
-
エクセルを使っていて2024/5/15...
-
指定文字の間に
-
Microsoft 365の Excel を使用...
-
エクセルの計算
-
エクセルでの作業計算方法について
-
Excelで全角を半角にしたいので...
-
エクセル関数に詳しい方教えて...
-
Googleスプレッドシートでファ...
-
エクセル 文字を増やしたい。
-
はがきについて。
-
エクセルの暗号化なしのバーの...
-
【マクロ】必要な項目(列)の...
-
Excel
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報