[[20250410115846]]　『フィルターで見えている金額を小計する方法』（愛子）　ページの最後に飛ぶ

『フィルターで見えている金額を小計する方法』（愛子）

売上の集計表があります。
Ｈ列は、金額欄　Ｏ列は入金予定額欄　
金額は１０行目から１．０００行目迄

フィルターは、２種類の色別で　一つはグリーン（個人客）、もう一つはオレンジです（業者）

質問は、例えばフィルターでグリーンを出した時、グリーンだけの
同じ入金予定日の小計の導き方を教えていただけますでしょうか

＜使用 Excel：Excel2021、使用 OS：Windows11 ＞

質問が悪いのでしょうか？
（愛子） 2025/04/10(木) 13:21:42

 О列は入金予定日の間違いですか？
 こういう集計にはピボットテーブルが有力かと思います。
 ピボットテーブルにはスライサーと言うフィルタに相当するものがあります。

 ところで、そのフィルタの元になっている項目はあるんですか？
 それとも、個人客、業者の区別を表す項目は無くて、手動で色をつけているのでしょうか？
 そのあたりの前提をもう少し説明されたらいかがでしょう。
（xyz） 2025/04/10(木) 13:30:56

xyz様
有難うございます。

＞О列は入金予定日の間違いですか？
すみません、入金予定日です。（入力間違いでした。読み直したのですが申し訳ありませんでした。）

＞そのフィルタの元になっている項目はあるんですか？
Ｈ列の金額欄です。（これも忘れていました、すみませんでした）

エクセルの金額そのものに色別をしています。フィルタのところで色フィルタとしてグリーンとオレンジの二つの色を付けて各色の選択をするようにしています。

これでお分かりになられますか？宜しくお願い致します。

（愛子） 2025/04/10(木) 13:47:40

 金額列に色を着けているのは判りました。
 別の言い方をすると、個人客と業者を判断する項目はないのですか？
 金額だけでは判断できないと思いますが。
（xyz） 2025/04/10(木) 13:53:20

私の伝え方が悪いと思います。

Ｈ列の９列目に売上金額の見出しがあります。
そこのフィルタをクリックすると色フィルタがあります。

（事前に各金額をグリーンとオレンジの色別をしておきます。）

そこをクリックしますとセルの色でフィルタとなってその下にグリーンとオレンジがあります。

そして例えばグリーンをクリックしますとグリーンばかりの金額が出ます。
そのグリーンの金額で、同じ入金日ごとに余白欄に小計をとって、銀行の入金明細書と
合わしていきたいのです。

（愛子） 2025/04/10(木) 14:37:58

 　　 H　I　J　K　L　M　N　　 O　P　　 Q　　　R 
 1　　　　　　　　　　　　　　　　　 入金　 小計 
 2　　　　　　　　　　　　　　　　　 5/10　12000 
 3　　　　　　　　　　　　　　　　　 5/11　 7000 
 4　　　　　　　　　　　　　　　　　 5/12　18000 
 5　　　　　　　　　　　　　　　　　 5/13　 8000 
 6　　　　　　　　　　　　　　　　　　　　　　　 
 7　　　　　　　　　　　　　　　　　　　　　　　 
 8　　　　　　　　　　　　　　　　　　　　　　　 
 9　金額　　　　　　　　　　入金　　　　　　　　 
10　1000　　　　　　　　　　5/10　　　　　　　　 
11　2000　　　　　　　　　　5/11　　　　　　　　 
12　3000　　　　　　　　　　5/12　　　　　　　　 
13　4000　　　　　　　　　　5/10　　　　　　　　 
14　5000　　　　　　　　　　5/11　　　　　　　　 
15　6000　　　　　　　　　　5/12　　　　　　　　 
16　7000　　　　　　　　　　5/10　　　　　　　　 
17　8000　　　　　　　　　　5/13　　　　　　　　 
18　9000　　　　　　　　　　5/12

 Q2
=LET(a,H10:H1000,b,O10:O1000,
c,SUBTOTAL(2,INDIRECT("H"&ROW(a))),
d,FILTER(b,c),e,FILTER(a,c),f,SORT(UNIQUE(d)),
g,MMULT(TRANSPOSE(e)*(f=TRANSPOSE(d)),1^d),
IF({1,0},f,g))

（んなっと） 2025/04/10(木) 14:50:45

んなっと様
有難うございます。

実際に関数を入れて行っていきます。
（愛子） 2025/04/10(木) 14:55:54

Q2
=LET(a,H10:H1000,b,O10:O1000,
c,SUBTOTAL(2,INDIRECT("H"&ROW(a))),
d,FILTER(b,c),e,FILTER(a,c),f,SORT(UNIQUE(d)),
g,MMULT(TRANSPOSE(e)*(f=TRANSPOSE(d)),1^d),
IF({1,0},f,g))

実際には、余白の場所のセル番地は変わりますが
上の関数でセル番地が変わってもa,b,c,d,e,f,g,fは
そのままのアルファベットでいいのですね。

ちなみに、そのアルファベットは、どういう意味をあらわしているのか
教えていただけますでしょうか
（愛子） 2025/04/10(木) 15:16:00

 結果はどうでしたか?
（んなっと） 2025/04/10(木) 15:28:07

 入金予定日に"未定"などの文字列があるとエラーになるので、その場合は下に変更
=LET(a,H10:H1000,b,O10:O1000,
c,SUBTOTAL(2,INDIRECT("H"&ROW(a))),
d,FILTER(b,c),e,FILTER(a,c),f,SORT(UNIQUE(d)),
g,MMULT(TRANSPOSE(e)*(f=TRANSPOSE(d)),1^e),
IF({1,0},f,g))

（んなっと） 2025/04/10(木) 15:31:45

 されたいことは理解していた積りです。
 私の推奨は、色を使わず、個人、企業の区分を列項目に持つことです。
 そうすれば、ピボットテーブルのようなツールで、ごく標準的な作業で集計ができるはずです。
 ピボットテーブルの話もし、その積りで関連質問をしたつもりでしたが、
 頓珍漢な質問をしているように受け止められたようです。
 # 既に計算式も提示されたようですので、私はこれで失礼します。

（xyz） 2025/04/10(木) 15:47:07

誤解を招きまして申し訳ございません。
私は、先月にこの会社に入社しました。
エクセルは、１．０００件ほどのデータがありそのエクセルを使用して
仕事をしています。
＞色を使わず、個人、企業の区分を列項目に持つことです。
良いアイデアと私も思っています。
なので勝手にピボットテーブルの提案をされているのは有り難いのですが
私は、変更することが出来ません。ですから頓珍漢な質問などしていると全然思ってもいませんでした
御親切に教えていただいて感謝している次第です。

業者からの入金（オレンジ）はｸﾚｼﾞｯﾄ会社から１週間ごとに
まとめて入金されます。
売上表と照合するのはむつかしいのです。入金が遅れる場合があります。検討をつけて銀行の入金と照合しています。そのために入金予定で小計をとれば✓がしやすいと思いました。
・・・・・
最後に教えていただけますでしょうか。
下記の関数を入力しましたが、５行分の文字が表れているだけです。
式として表示する方法と上で質問をしましたようにa,b,c,d,e,f,g

=LET(a,H10:H1000,b,O10:O1000,
c,SUBTOTAL(2,INDIRECT("H"&ROW(a))),
d,FILTER(b,c),e,FILTER(a,c),f,SORT(UNIQUE(d)),
g,MMULT(TRANSPOSE(e)*(f=TRANSPOSE(d)),1^e),
IF({1,0},f,g))

（愛子） 2025/04/10(木) 16:04:01

 まずは
そのセルの上で右クリック→セルの書式設定→標準
式の先頭にスペースがある場合は削除
試してみてください。
（んなっと） 2025/04/10(木) 16:17:26

んなっと様
ありがとうございます。

夕刻４時に仕事が終わります。
明日、会社で確認をいたしますので
よろしくお願い申し上げます。
（愛子） 2025/04/10(木) 19:42:29

んなっと様

おはようございます。

先ほど、会社に来ました。

＞そのセルの上で右クリック→セルの書式設定→標準
式の先頭にスペースがある場合は削除
試してみてください。

試しましたら、１行目の式で最初＝が漏れていましたので修正後に
一行目の式=LET(a,H10:H1000,b,O10:O1000,)で結果は、０になっています。
式の２行目は、c,SUBTOTAL(2,INDIRECT("H"&ROW(a))),
式の3行目はd,FILTER(b,c),e,FILTER(a,c),f,SORT(UNIQUE(d)),
式の４行目は、g,MMULT(TRANSPOSE(e)*(f=TRANSPOSE(d)),1^e),
式の５行目は、IF({1,0},f,g))で2行目から５行目迄は、文字だけになっています。
1行目の式に続いて2行目から５行目までをつないでいくのでしょうか？
宜しくお願い致します。

（愛子） 2025/04/11(金) 11:35:04

 一つのセル(例の場合Q2セル)に全部の式を入れます。
 Q2
=LET(a,H10:H1000,b,O10:O1000,
c,SUBTOTAL(2,INDIRECT("H"&ROW(a))),
d,FILTER(b,c),e,FILTER(a,c),f,SORT(UNIQUE(d)),
g,MMULT(TRANSPOSE(e)*(f=TRANSPOSE(d)),1^e),
IF({1,0},f,g))

 書き込みを見ていると、無理かもしれませんね。
（んなっと） 2025/04/11(金) 11:56:24

んなっと様

＞書き込みを見ていると、無理かもしれませんね。

出来ました。色々試しました。至極便利ですね。

言葉足らずで申し訳なかったのですが、日ごとの小計をとるときに非表示の金額を除いて小計をとれないでしょうか？

最初の質問で、私は、

フィルターは、２種類の色別で　一つはグリーン（個人客）、もう一つはオレンジです（業者）

 質問は、例えばフィルターでグリーンを出した時、グリーンだけの  
 同じ入金予定日の小計の導き方を教えていただけますでしょうか？　と言いました。

グリーン色を出した場合には、オレンジの非表示は計算されないようにしていただきたかったのです。

言葉足らずで申し訳ありませんでした。よろしくお願い申し上げます。

（愛子） 2025/04/11(金) 20:50:30

 こんにちは。

 伝わっておらず、非表示のものを含めて合計していると決めつけていますけど、
 そんなことないですよ。 
 私の手元では、望む結果が得られています。
 簡単なサンプルで計算させました。
 H列に色フィルタを掛ければ、それに応じて結果が変わることを確認しました。
 （数式のなかで、Filter関数を使って可視セルだけ抽出していますよ。）

 何か手違い・勘違いがあると思います。よく確認されたらどうでしょう。
 提示された式に変更を加えていることはないですよねえ。(a,bのセル範囲は別として）
 もしそうであれば、それを提示されたらいかがですか?

（xyz） 2025/04/12(土) 10:14:30

Xyzさん、
有難うございます。

上記の
んなっと様の 2025/04/10(木) 14:50:45の
サンプルと同じようにデータを用いて
関数をq2にいれました。

日毎に小計が出ました。検算をすると
当然合っていましたので、
関数を間違っていなかったと喜んで
いました。

そこで非表示を思い出して
一行だけ書式で非表示にしました。
そしたら非表示の数字も合計されました。

今度、フィルターを使って確認をします。

（愛子） 2025/04/12(土) 13:19:00

 手動による非表示もあるのであれば
 c,SUBTOTAL(102,INDIRECT("H"&ROW(a))),
 です。（　2→102 です ）

 フィルタによる非表示と、手動による非表示は異なるものです。
 勝手に仕様を変更したら支障が起きるのは当然です。
 その結果で全否定ですか.....
（xyz） 2025/04/12(土) 13:27:39

〉フィルタによる非表示と、手動による非表示は異なる
知りませんでした。すみません。

さっき、フィルターを作り
色別で小計を取りましたら
当然でしょうが、非表示は、計算されずに
上手くいきました。有難うございます。

最後にこの関数を理解したいので、
関数の中でa,b,c,s,e,f,gは、どういう意味をもつのですか？
=LETからの意味を教えていただけますか？
覚えて利用したいのです。
よろしくお願いします。

（愛子） 2025/04/12(土) 13:37:53

 手動による非表示への効果はどうだったのでしょうか。
 色フィルターで動作することは既に伺いました。

 簡単に書けるような内容ではありません。
 また、一日二日で理解できるものでもありません。
http://officetanaka.net/excel/function/function/
 などで、ご自分でひとつひとつ勉強されことから始めたらいかがですか?
 （なお、これを読んですべてが簡単にわかるものではありません。
   色々なテクニックが詰まっているようですから）

 ちなみに、イメージだけであれば、こんな風に説明的な変数にすることもできます。
 =LET(
     a,H10:H1000,
     b,O10:O1000,
     可視,SUBTOTAL(102,INDIRECT("H"&ROW(a))),
     f予定日,FILTER(b,可視),
     f金額,FILTER(a,可視),
     sort済み日付,SORT(UNIQUE(f予定日)),
     合計金額,MMULT(TRANSPOSE(f金額)*(sort済み日付=TRANSPOSE(f予定日)),1^f予定日),
     IF({1,0}, sort済み日付,合計金額)
 )

 # f予定日などのfは"フィルタされた"という意味の積りです。
 # 私もワークシート関数初心者です。話半分の積りで読んで下さい。
（xyz） 2025/04/12(土) 13:58:54

早速にお返事をいただき有難うございました。
〉手動による非表示への効果はどうだったのでしょうか
計算がされています。(前に上手くいきませんといいましたので、
違う意味ですか？)

http:を読みました。下記の内容でa,からg迄の意味が幾つも組み合わせができると
いうことが少しわかったような気がしました。

〉LET関数の続きです。先の使い方の意味は理解できましたか？では、もう少し複雑にします。
「変数の名前」と「変数に入れる値」は必ずセットで指定します。そして、このセットは何組でも(126組まで)指定できます。

有難うございました。嬉しいです。
んなっと様も教えていただき有難うございました。
（愛子） 2025/04/12(土) 16:14:29

 ピボットテーブルがお好みでなければ、以下の「小計機能」の活用も選択肢になるのでは？
 ・別シート（作業用）に色別のデータをそれぞれコピーペイスト
 ・入金予定日でソート
 ・「小計」機能を使って、入金予定日ごとの小計を計算させる（ダイアログで選択するだけ）
 こうすれば、
 ・小計とその内訳データを同じグループで閲覧できる
 ・小計のみ表示、全表示を簡単に切り替えられる
 ので照合に使いやすいと思われる。
（xyz） 2025/04/12(土) 16:59:58

 > 〉手動による非表示への効果はどうだったのでしょうか
 > 計算がされています。(前に上手くいきませんといいましたので、
 > 違う意味ですか？)
 2を102に変更することによって、
 手動で非表示にしたものも、小計計算の対象外になるのですが、
 そのように伝わっていますか？
 # 私にとっては、もうどうでもいいようなことですが。
（xyz） 2025/04/12(土) 17:05:58

前の会社で、親会社から来た人が
ピボットテーブルを使ってやってほしいと
強引に説明もなく、パタパタと自分勝手に言って
難しいことをさせるので
他の人も理解出来ないまま、難しいまま、止めてしまいました。
1から覚えるのがわかり辛いのです。すみません。

小計機能、便利ですね、有難うございます。

〉2を102に変更することによって、

 手動で非表示にしたものも、小計計算の対象外になるのですが、
 そのように伝わっていますか？

2を102に変更したら手動での非表示も対象外になるのですね。
すみません、わかっていませんでした。

有難うございました。感謝です。

（愛子） 2025/04/12(土) 20:11:23

 xyzさん、ありがとうございました。
（んなっと） 2025/04/14(月) 07:50:32

 いえいえ、こちらも勉強になりました。わざわざ恐縮でした。

（xyz） 2025/04/14(月) 11:47:14

コメント返信：

[ 一覧(最新更新順) ]

[[20250410115846]] 『フィルターで見えている金額を小計する方法』（愛子） ページの最後に飛ぶ

[[20250410115846]]　『フィルターで見えている金額を小計する方法』（愛子）　ページの最後に飛ぶ