[[20211207225041]]　『重複を省きながら複数条件でカウント』（yu）　ページの最後に飛ぶ

『重複を省きながら複数条件でカウント』（yu）

    A      B    C     D      E       F          G
 1  日付　会社名　金額        会社名　　金額　　　取引日数
 2  5/1   ○社   300         ○社    1,100円      2日
 3  5/1   ○社   500         △社      900円      2日
 4  5/2   △社     0
 5  5/3   △社   500
 5  5/4   ○社   300
 6  5/4   △社   400

 こんばんは。拙い文章で恐縮ですがお助けください。

 A〜Cのデータを元に、E〜Gの会社ごとの合計金額と取引日数を計算したいです。

 A〜Cはシステムからデータを貼り付けます。毎月に行数が変わるため、式ではA:Aという列単位で範囲指定をしたいです。
 E〜Gは顧客数が確定しているためF2:F3で大丈夫です。

 Fは会社ごとの取引金額の合計を出したいです。
 Gは、会社ごとに、取引のある日数を出したいです。ここに条件の重複があって、
 ?@取引日数は、○社は5/1に2回取引がありますが、同じ日に複重する取引は1カウント
 ?A5/2は取引がないのに0円と表示されているのでカウントしません。

 色々なサイトを参考にして計算式を入れて試してみましたが、エラーになってしまいます。どなたか、どうぞご教示ください。

＜使用 Excel：Excel2019、使用 OS：Windows10 ＞

 >計算式を入れて試してみましたが、エラーになってしまいます。
 数式を提示されたらどうですか。
 その数式に対してアドバイスがあると思いますよ。
 SUMIFS で出来るような気がしますけど。

（PP） 2021/12/08(水) 08:36

 >式ではA:Aという列単位で範囲指定をしたいです。
 これが満たせないが。
 F2セル:=SUMIF(B:B,E2,C:C)
 G2セル:=SUM((FREQUENCY(IF((A$2:A$100<>"")*(B$2:B$100=E2)*(C$2:C$100>0),MATCH(A$2:A$100&B$2:B$100,A$2:A$100&B$2:B$100,0),""),ROW($2:$100))>0)*1)
 で、G2セルの式は入力時にShiftキーとCtrlキーを押しながらEnterキーで式を確定してくれ。
 (確定時に式が{}で囲まれればOK)
 その後F2セル、G2セルを下へフィルコピーではどうだろうか？

（ねむねむ） 2021/12/08(水) 09:27

 なお、G列の式は元データが最大100行までに対応している。
 もっと行がある場合は式中のすべての$100を大きくしてくれ。
（ねむねむ） 2021/12/08(水) 09:28

 ＞毎月に行数が変わるため、式ではA:Aという列単位で範囲指定をしたいです。
 最大で何行ぐらいになるか、見当つきますよね？
 配列数式になるので、範囲を列全体にすると処理が重くなると思いますよ。

 最大100行だとして･･･

 G2 =COUNT(0/FREQUENCY(IF(($B$2:$B$100=E2)*($C$2:$C$100<>0),$A$2:$A$100),$A$2:$A$100))
 Ctrl＋Shift＋Enterで確定し、下コピー

 範囲を列全体にすることもできますけど、おすすめはしません。

 以上
（笑） 2021/12/08(水) 10:14

 PP様　
 本当そうですよね。
 業務中に時間を取れず自宅で状況を思い出しながらスマホで入力したので
 曖昧な質問になってしまい失礼いたしました。

 ねむねむ様　笑様
 ありがとうございました！正直、計算式の意味を理解できていませんが、
 希望した通りの数字が返ってきました。すごいです！
 ただ、返信で教えていただいた計算式を実際の書式に当てはめて
 入力してみたところ、なぜか0日になってしまいましたが、
 無知のためどう質問していいものやら分からない状態です。
 もう少し勉強して頑張ってみます。
（yu） 2021/12/08(水) 23:14

 実際のレイアウトと実際に入力した式を書いてみてくれないだろうか？
（ねむねむ） 2021/12/09(木) 09:18

  　A   　 B　    C    　D     　E   　F　  G　  H   I J　  K　    L 　　　M 　　 N　　  O 　P 　　Q 　　R　 　S
 1 日付 取引先CD 枝番 得意先名 現場名 個数 重量 金額     取引先CD 枝番 得意先名 現場名 個数 重量　（計） 備考 取引日数
 2　11.1　 123　　1　　○社　　○工場　1　8000　25000　　　123　　　1　　○社　　○工場　1　17000　30000　-　　13日
 3　11.2　 124　　1　　×社　　×工場　1　5000　15000　　　123　　　2　　○社　　△工場　1　13000　(○工場と△工場は○社として計上)

 =COUNT(0/FREQUENCY(IF(($D$2:$D$3500=M2)*($G$2:$G$3500<>0),$A$2:$A$3500),$A$2:$A$3500))

  こんにちは。ねむねむ様、返信ありがとうございます。
 実際のデータに近い状態を作成してみました。うまく伝わるといいのですが・・・・。

（yu） 2021/12/09(木) 16:59

 S2＝が抜けていました。

 表がずれてしまいました。 S列＝取引日数です。
 G列までがデータで、I列とJ列が空白、K列からS列が表です。

（yu） 2021/12/09(木) 19:37

 ＞=COUNT(0/FREQUENCY(IF(($D$2:$D$3500=M2)*($G$2:$G$3500<>0),$A$2:$A$3500),$A$2:$A$3500))

 この式、 Ctrl＋Shift＋Enter　で確定しましたか？
 ※CtrlキーとShiftキーを押しながら Enter

 式を入れたセルを選択し、数式バーを見てください。
 式が、{=COUNT(0/FREQUENCY(…中略…),$A$2:$A$3500)}　のように｛　｝で囲まれていればオッケーですが
 囲まれていなければ正しく確定できてません（式が正しく機能しません）

 うっかり Enter だけで確定してしまった場合は、そのセルを選択し
 数式バーにカーソルを置いて　Ctrl＋Shift＋Enter
 または、F2キーを押してから　Ctrl＋Shift＋Enter　で確定

 とりあえず以上です
（笑） 2021/12/09(木) 20:37

ありがとうございます。
やったつもりで出来ていなかったのもしれませんね…
週明けに出勤したら早速やってみます！
（yu） 2021/12/09(木) 23:04

やってみましたが、簡易版では計算できても実際の書式ではCtrl＋Shift＋Enterで確定しても0でした。
何回やってもできなかったので、試しに下記の式を入れてみたら答えが返って来ました。

=SUM((FREQUENCY(IF((A$2:A$3500<>"")*(D$2:D$3500=M2)*(G$2:G$3500>0),MATCH(A$2:A$3500&D$2:D$3500,A$2:A$3500&D$2:D$3500,0),""),ROW($2:$3500))>0)*1)

数式を理解していないので原因もわかりませんが、計算できたので良かったです。

ねむねむ様、（笑）様、ご協力ありがとうございました。
（yu） 2021/12/13(月) 12:30

 A列の日付が、数値ではなく文字列になってませんか？

 実際の日付はどう表示されてるんですか？
「11.1」とか「11.2」？

 だとしたら、11月10日は「11.10」？

 以上、確認だけ
（笑） 2021/12/13(月) 13:27

システムで抽出したデータをそのまま貼り付けてます。書式設定は標準でした。
2021.11.1
2021.11.10
となっております。
（yu） 2021/12/14(火) 20:24

でも、2021.11.01だったかもしれません。
自信がないので、出勤したら確認してみます。
（yu） 2021/12/14(火) 20:36

 ＞2021.11.1
 ＞2021.11.01
 これはどっちでもいいです。
 どっちにしても、データの型としては数値ではなく「文字列」ですね。
 だったら当方提示の式では「0」になります。

 A列を日付データに変換してもいいのなら･･･

 1）A列全体を選択し、データタブの「区切り位置」
　　　　　↓
 2）ダイアログが表示されるので、「次へ」を2回クリックして、3画面目へ
　　　　　↓
 3）左上、列のデータ形式から「日付」を選択して「完了」をクリック

 以上の操作で、「2021.11.1」という文字列が日付データ（数値）に変換される。
 表示も「2021/11/1」になります（書式はもちろん変更可能）

 これなら　↓　の式でも結果が出るでしょう。
 =COUNT(0/FREQUENCY(IF(($D$2:$D$3500=M2)*($G$2:$G$3500<>0),$A$2:$A$3500),$A$2:$A$3500))

 A列は文字列のまま、この式に手を加えてもできますけど、ダラダラと長くなるのでやめておきます。

 ■作業列を使ってもいいのなら･･･
 A列は文字列のまま、作業列で日付に変換

 どこでもいいですけど、I列だとして

 I2 =IF(A2="","",SUBSTITUTE(A2,".","/")*1)
 表示形式「日付」で、3500行目まで下コピー

 =COUNT(0/FREQUENCY(IF(($D$2:$D$3500=M2)*($G$2:$G$3500<>0),$A$2:$A$3500),$A$2:$A$3500))
 ↑ の式の $A$2:$A$3500（2か所とも）を　$I$2:$I$3500　に変更

 =COUNT(0/FREQUENCY(IF(($D$2:$D$3500=M2)*($G$2:$G$3500<>0),$I$2:$I$3500),$I$2:$I$3500))
 Ctrl＋Shift＋Enter で確定

 ■ついでに･･･
 ねむねむさん提示の式ですけど
 最後の方に　ROW($2:$3500)　とありますよね。
 　　　　    ROW($1:$3500)
                ~~~~
 にしないとマズいと思いますよ。

 どうマズいのか、具体例を挙げることもできますけど、今日は紙数が尽きたのでこの辺で。

 以上
（笑） 2021/12/14(火) 23:00

（笑）様

 A列を日付データに変換して

 =COUNT(0/FREQUENCY(IF(($D$2:$D$3500=M2)*($G$2:$G$3500<>0),$A$2:$A$3500),$A$2:$A$3500))

 を入力しました。今後はデータ貼付けの際に値のみ貼付ければいいですね。

 すごいなーと思うばかりです。

 親切に教えていただいてありがとうございました！

（yu） 2021/12/16(木) 10:48

コメント返信：

[ 一覧(最新更新順) ]

[[20211207225041]] 『重複を省きながら複数条件でカウント』（yu） ページの最後に飛ぶ

[[20211207225041]]　『重複を省きながら複数条件でカウント』（yu）　ページの最後に飛ぶ