これからの季節に親子でハイキング! >>

重複を除いてデータ数をカウントする「関数」を教えてください。よろしくお願いいたします。
単純に全データからカウントするのであれば、sumproduct等を使えばできますが、困っているのは、一部のデータ(晴のみ)から、重複データを除いて(同じ日に複数回稼働していても1とカウント)集計するという点です。

稼働日  天気  管理番号
2月10日  晴   10022
2月20日  晴   31555
2月20日  晴   10022
2月20日  晴   10022
2月20日  晴   10022
2月24日  晴   44200
2月25日  雨   31555
2月25日  雨   10022

【導きたい答え】
「晴れの日」の「管理番号10022」の稼働日数は、「2」日でした。
(4個ではなく、2個(2日)とカウントしたい)

*Excel2016を使用しています。
*実際はもっと多くのデータが対象となります。あくまで例は簡略化したものです。
*ピボットやVBA?ではなく、エクセル関数で求めたいです。
*可能であれば、作業領域でいったん計算して再集計する等ではなく、一度で求められる方法がいいです。

質問者からの補足コメント

  • 「作業領域でいったん計算して再集計する等ではなく、一度で求められる方法希望」と書きましたが、無理そうであれば、いったん「作業領域」で計算する方法でもかまいません。

    改めてピボットやVBAは対象外でお願いします。

      補足日時:2018/04/21 20:43
  • HAPPY

    出来ました!

    すみません、いろいろ試していたら、2になりました!
    入力項目だったんですね。
    ありがとうございます。

    No.2の回答に寄せられた補足コメントです。 補足日時:2018/04/21 20:46

A 回答 (4件)

No.1です。



ユーザー定義関数の方法をと思っていましたが、VBAはダメ!というコトなので・・・
ただ作業用の列は使ってもOKのようなので、一例です。

↓の画像のようにD列を作業用の列としています。
作業列D2セルに
=IF(AND(B2=G$1,C2=G$2),IF(COUNTIFS(A$2:A2,A2,B$2:B2,B2,C$2:C2,C2)=1,1,""),"")

という数式を入れこれ以上データはない!というくらいまで下へフィル&コピー!
結果のG3セルには
=SUM(D:D)
という数式を入れています。m(_ _)m
「Excel 重複を除いてデータ数をカウン」の回答画像3
    • good
    • 0
この回答へのお礼

ありがとうございます。
とてもすっきりとした式で求められるのでびっくりしました。

=IF(AND(B2=G$1,C2=G$2),IF(COUNTIFS(A$2:A2,A2,B$2:B2,B2,C$2:C2,C2)=1,1,""),"")

この式なんですが、IF(COUNTIFS(A$2:A2,A2,B$2:B2,B2,C$2:C2,C2)=1,1,""),"")
の部分って何を計算?しているんでしょうか。
稼働日の情報、天気の情報、管理番号の情報が 1,1,空白 ならば、空白?

お礼日時:2018/04/21 22:13

No.3です。



>IF(COUNTIFS(A$2:A2,A2,B$2:B2,B2,C$2:C2,C2)=1,1,""),"")
>の部分って何を計算?しているんでしょうか。

説明を少しだけ・・・
前半部分の
>IF(AND(B2=G$1,C2=G$2)
は問題ないですよね。
(画像のB列がG1、C列がG2と一致するものだけを対象としています)

さて、上記のIF関数が「TRUE」の場合ですが
前回アップした画像の配置で
2行目から検索し
A列・B列・C列が重複するデータがある場合、最初に出現する行だけに「1」が表示されるようにしています。
これが質問文の中で一番重要な部分になると思います。

※ どこか使っていない列の2行目に
=COUNTIFS(A$2:A2,A2,B$2:B2,B2,C$2:C2,C2)

という数式を入れ、フィルハンドルで下へコピーしてみてください。
2行目以降、A~C列が重複するデータの出現回数が表示されます。
その中の最初に出現した行だけ(「1」の行だけ)を作業列に表示するようにしています。

あとは単純にプラスすれば質問の回答がそのまま簡単に導き出せます。
    • good
    • 0
この回答へのお礼

とても参考になりました。
ありがとうございます!!!

お礼日時:2018/04/25 20:11

添付図参照


D1: =A2&"_"&B2&"_"&C2
E2: =IF(COUNTIF(D$2:D2,D2)>1,"",A2)
F2: =IF(AND(B2=G$3,C2=G$2),E2,"")
F1: =COUNT(F2:F500)
「Excel 重複を除いてデータ数をカウン」の回答画像2
この回答への補足あり
    • good
    • 0
この回答へのお礼

ご回答ありがとうございます。
関数を入れてみたのですが、下記の通り「0」となります。
色がついている箇所(稼働日数や管番、天)というのがよくわかりません。
そこは手入力すべきなのか、自動で返ってくるものなのでしょうか。
Fの列はちゃんと計算式を入れても空白の結果となります。

稼働日 天気 管理番号 稼働日_天気_管理番号         0
2月10日 晴 10022  43141_晴_10022  2月10日
2月20日 晴 31555  43151_晴_31555  2月20日
2月20日 晴 10022  43151_晴_10022  2月20日
2月20日 晴 10022  43151_晴_10022 
2月20日 晴 10022  43151_晴_10022 
2月24日 晴 44200  43155_晴_44200  2月24日
2月25日 雨 31555  43156_雨_31555  2月25日
2月25日 雨 10022  43156_雨_10022  2月25日

お礼日時:2018/04/21 20:39

こんばんは!



関数で作業列なしで!となると結構厄介だと思います。
手っ取り早くVBAでの一例です。
↓の画像のような配置になっていて、
F1・F2セルに各条件を入力した時点でマクロが実行されるようにしてみました。
シートモジュールにしてください。

Private Sub Worksheet_Change(ByVal Target As Range) '//この行から//
If Intersect(Target, Range("F1:F2")) Is Nothing Or Target.Count > 1 Then Exit Sub
Dim myDic As Object
Dim i As Long, lastRow As Long, cnt As Long
Dim myStr As String
Dim myR
If WorksheetFunction.CountBlank(Range("F1:F2")) = 0 Then
Set myDic = CreateObject("Scripting.Dictionary")
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
myR = Range(Cells(2, "A"), Cells(lastRow, "C"))
For i = 1 To UBound(myR, 1)
If myR(i, 2) = Range("F1") And myR(i, 3) = Range("F2") Then
myStr = myR(i, 1) & "_" & myR(i, 2) & "_" & myR(i, 3)
If Not myDic.exists(myStr) Then
myDic.Add myStr, ""
cnt = cnt + 1
End If
End If
Next i
Range("F3") = cnt
Set myDic = Nothing
Else
Range("F3").ClearContents
End If
End Sub '//この行まで//

※ A~C列の変更ではマクロは動きません。
F1またはF2セルのデータが変わった時点でマクロが実行されます。m(_ _)m
「Excel 重複を除いてデータ数をカウン」の回答画像1
    • good
    • 0
この回答へのお礼

質問文の通りVBAは対象外です。

お礼日時:2018/04/21 20:40

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aと関連する良く見られている質問

QEXCELで条件に合致したデータから、さらに重複するデータを1と数える

EXCELで条件に合致したデータから、さらに重複するデータを1と数える
ことが関数で出来るでしょうか?

例えば
  A ,B ,C
1 見積No 、担当者、商品名
2 111、山田、パソコン
3 111、山田、プリンタ
4 222、佐藤、HDD
5 333、山田、パソコン
6 444、青木、パソコン
7 555、山田、HDD

パソコンの担当者ごとの見積もり件数はという問いに対して
 山田 2件
 佐藤 0件
 青木 1件
という解答を求めたいのです。
見積Noが同じ場合は同じ見積書で明細が2行の場合で、これを1件と数えたいのです。
フィルタで絞った後に、=SUMPRODUCT(1/COUNTIF(A2:A100,A2:A100))
という関数を試しましたが、フィルタを無視して全ての件数が対象となってしまいました。
SUMに対するSUBTOTAL関数のようなものがあれば便利だったのですが…。

うまく質問出来たのか不安ですが、判る方お願いします。 m(__)m
 

Aベストアンサー

こんにちは!
外していたらごめんなさい。

↓の画像のようにSheet1のデータをSheet2にまとめるようにしてみました。
Sheet2のC2セルに検索商品名を入力すると、B列に件数を表示させるようにしています。
Sheet2の氏名欄はあらかじめ入力されているものとします。

Sheet1の作業用の列を2行使わせてもらっています。
D2セルに
=A2&C2
E2セルに
=IF(OR(Sheet2!$C$2="",C2<>Sheet2!$C$2,COUNTIF($D$2:D2,D2)<>1),"",ROW(A1))
として、D2・E2セルを範囲指定し、E2セルのフィルハンドルで下へずぃ~~~!っとコピーします。

そして、Sheet2のB2セルに
=IF(A2="","",SUMPRODUCT((Sheet1!$B$2:$B$100=A2)*(Sheet1!$E$2:$E$100<>"")))
という数式を入れ、オートフィルで下へコピーしています。

尚、数式はSheet1の100行目まで対応できるようにしていますが、
データ量によって範囲指定の領域はアレンジしてみてください。

以上、参考になれば幸いですが
的外れなら読み流してくださいね。m(__)m

こんにちは!
外していたらごめんなさい。

↓の画像のようにSheet1のデータをSheet2にまとめるようにしてみました。
Sheet2のC2セルに検索商品名を入力すると、B列に件数を表示させるようにしています。
Sheet2の氏名欄はあらかじめ入力されているものとします。

Sheet1の作業用の列を2行使わせてもらっています。
D2セルに
=A2&C2
E2セルに
=IF(OR(Sheet2!$C$2="",C2<>Sheet2!$C$2,COUNTIF($D$2:D2,D2)<>1),"",ROW(A1))
として、D2・E2セルを範囲指定し、E2セルのフィルハンドルで下へずぃ~~~!...続きを読む

Qエクセル関数 重複をのぞいて個数を数える方法

いつもお世話になっています
セルに
a  b  c  a  b b
と入力したとき、個数を数える方法は知っていますが、種類が3個だと集計するにはどんな関数を使えばいいのでしょうか。

Aベストアンサー

下記URLの方法は如何でしょう。

「重複しないデータを数える」
http://integer.exblog.jp/2132536/

「リストから重複するデータをはぶいた件数(個数)をカウントする」
http://ameblo.jp/xls/entry-10073848203.html

「重複しないセル数」ユーザー定義関数の説明
http://www.katch.ne.jp/~kiyopon/soft/juhukunai.html

Q複数条件で重複しないデータをカウントする方法

困っています。
エクセルの関数を教えてください。
複数条件に該当するデータをカウントする方法を教えてください。

A列とB列には不特定多数の名称と地名が入力されています。
このような表です。

A列(品名)    B列(出荷先)    
いちご       東京
いちご       埼玉
いちご       東京 
みかん       山梨
みかん       岐阜
りんご       埼玉 
りんご       大阪
りんご       大阪
以下1500品目

A列のいちごでB列の出荷先が東京であるものは2件あります。
この「件数」を関数で表示させる方法を教えてください。
よろしくお願いいたします。

Aベストアンサー

分かり易く簡単で計算に負担のかからない方法は作業列を作って対応することです。
作業列が目障りでしたらその列を選択して右クリックして「非表示」を選択すればよいでしょう。
1行目は項目名が有るとしたらC2セルには次の式を入力して下方にオートフィルドラッグコピーします。

=A2&B2

D2セルには次の式を入力して下方にオートフィルドラッグコピーします。

=IF(C2="","",IF(COUNTIF(C$2:C2,C2)>1,COUNTIF(C$2:C2,C2),""))

これでダブったデータが該当する行までにあればダブりの回数がD列に表示されます。単にダブりの回数を表示させるのに比べてダブりの行がはっきり示されるのがよいでしょう。

また、ダブった品名と出荷先を例えばシート1に元のデータがあるとしてシート2に表示させるとしたらシート1のD2セルには次の式を入力して下方にオートフィルドラッグコピーします。

=IF(C2="","",IF(COUNTIF(C:C,C2)>1,MAX(D$1:D1)+1,""))

シート2の例えばA2セルには次の式を入力してB2セルまでオートフィルドラッグコピーしたのちに下方にオートフィルドラッグコピーしたのちに下方にもオートフィルドラッグコピーします。

=IF(ROW(A1)>MAX(Sheet1!$D:$D),"",INDEX(Sheet1!$A:$B,MATCH(ROW(A1),Sheet1!$D:$D,0),COLUMN(A1)))

A列には品名がB列には出荷先は表示されます。
シート2のC2セルには次の式を入力して下方にオートフィルドラッグコピーします。

=IF(A2="","",COUNTIF(Sheet1!$C:$C,A2&B2))

これでC列にはダブりの回数が表示されます。

分かり易く簡単で計算に負担のかからない方法は作業列を作って対応することです。
作業列が目障りでしたらその列を選択して右クリックして「非表示」を選択すればよいでしょう。
1行目は項目名が有るとしたらC2セルには次の式を入力して下方にオートフィルドラッグコピーします。

=A2&B2

D2セルには次の式を入力して下方にオートフィルドラッグコピーします。

=IF(C2="","",IF(COUNTIF(C$2:C2,C2)>1,COUNTIF(C$2:C2,C2),""))

これでダブったデータが該当する行までにあればダブりの回数がD列に表示されます。...続きを読む

Qエクセルで重複した文字列を1としてカウントする方法

こんにちは
エクセルで親子の名簿を作っているのですが
複数子供がいる場合の世帯数をカウントしたいのですが
簡単な方法はありますか?
Microsoft Office Excel 2007です。

阿部
阿部
黒木
黒木
安藤
井上
田中

この場合5と数えたいです。
よろしくお願いします。

Aベストアンサー

初心者向きではないが、列は使わずともできます。
データの範囲が、A2~A99なら、
=SUMPRODUCT(1/COUNTIF(A2:A99,A2:A99))

QEXCELでの重複データカウント方法について

会社名のデータが1万件あります。
その中には、同じ会社名が重複しているものがあります。
そこで、重複しているデータは1つのものとしてカウントし、全部で何件の会社が存在するかカウントする方法はあるでしょうか?

Aベストアンサー

1)集計したSHEETを全選択してコピー
2)別のSHEETに形式を選択して貼り付け(値だけ)
3)会社名カラムで、[データ]-[フィルタ]-[オートフィルタ]
4)出てきた三角形をクリックして、(オプション)
5)「個数」で「終わる」を選択してフィルタ実行
6)コピーして別のシートに貼り付け

EXCELのバージョンによって(6)の動作は変るかもしれません。
上手くいかなかったら、セルで選択してコピー、列で選択してコピー、行で選択してコピー、全部試してみてください。

それでもだめなら、
(1)集計行の前に一列追加
(2)追加した空の列に =RIGHT(B1,6) と入力(注:B1は会社名のセル)
   これで、追加した列は「データの個数」と表示されるはず
(3)(2)を全行にコピー
   つまり、集計行の頭には「データの個数」が並ぶ。他の行はブランク。
(4)追加した行を[データ]-[並べ替え]

これで、集計行だけが一箇所にあつまるはず
   

Qエクセルで重複を除いた日付のカウント(条件付き)

過去の記録にもなく、困っています。

エクセルで(A列の)日付をカウントしたいのですが、重複して困っています。

「C1」に任意の日付(○月○日)を入れると、「D1」に○月○日以下の日付がカウントされるようにしたいのです。

     A     B    C     D
1  4月5日        
2  4月6日
3  4月6日
4  4月6日
5  4月7日
6  4月7日
7  4月8日


D1に
=COUNTIF(A:A,"<="&C1)
といれてみると、重複してカウントされてしまうのですが、重複を除いてカウントがしたいのです。

ちなみに(C1)に「4月7日」を入れると、(D1)に「3」になるようにしたいのに、重複して「6」となってしまいます。


おいそがしいと思いますが、どうかご教授をお願いいたします。

Aベストアンサー

日付データがA1セルから入力されているなら以下の式でC1セル以下の日付の数が求められます。

=COUNT(INDEX(1/(MATCH(A1:A10,A1:A10,0)=ROW(A1:A10)*(A1:A10<=C1)),))

A2セルからのデータの場合は、ROW(A1:A10)の部分を以下のように変更します

=COUNT(INDEX(1/(MATCH(A2:A10,A2:A10,0)=(ROW(A2:A10)-1)*(A2:A10<=C1)),))

Qエクセルで条件に一致したセルの隣のセルを取得したい

下のような「得点」という名前のシートがあります。
(「田中」のセルがA1です。)

 [ 田中 ][ 10 ][ 200 ]
 [ 山田 ][ 21 ][ 150 ]
 [ 佐藤 ][ 76 ][ 250 ]
 [ 鈴木 ][ 53 ][ 350 ]

別のシートのA1セルに、「佐藤」と入力すると、

 [ 佐藤 ]

「得点」シートから「佐藤」の列を見つけて、B1、C1に

 [ 佐藤 ][ 76 ][ 250 ]

のように表示させたいのですが、B1、C1にはどのような式を書けば良いのでしょうか。
「得点」シートでは氏名が重複する事はありません。
IF文を使うと思うのですが、いまいち良く分かりませんでした。

よろしくおねがい致します。

Aベストアンサー

こんにちは!
VLOOKUP関数で対応できます。
IF関数と併用すればエラー処理が可能です。

Excel2007以降のバージョンであれば
B1セルに
=IFERROR(VLOOKUP($A1,得点!$A:$C,COLUMN(B1),0),"")
としてC1セルまでオートフィルでコピー!
そのまま下へコピーすると行が2行目以降でも対応できます。

Excel2003までの場合は
=IF($A1="","",VLOOKUP($A1,得点!$A:$C,COLUMN(B1),0))

としてみてください、m(_ _)m

Qエクセルのセルにある同じ値が何件あるかを調べたいのですが・・

教えてください。
マイクロソフトエクセルで各セルの値が何件あるかを調べたいのです。
例えば
A1セルに4300
A2セルに1500
A3   1000
A4   4300
A5   1000
・・・・・
など同じAセルに約300近くの値が入っていたとします。
この場合
4300 は 2件
1500 は 1件
1000 は 2件など「件」はつかなくて数値だけでも助かります。
代金の返金をする時に紙幣や硬貨を用意する為に同じ値がいくつあるかが知りたいです。
オートフィルタで同じ数値だけを出す方法もありますが、こちらの方法で出来ることでしたら教えて下さい。
宜しくお願い致します。
エクセルのバージョンは古くて2002です。

Aベストアンサー

こんばんは!
色々方法はあるかと思いますが・・・

一例です。

↓の画像のようにB列を作業用の列とさせてもらっています。

B2セルに
=IF(COUNTIF($A$2:A2,A2)=1,ROW(A1),"")
という数式を入れオートフィルでずぃ~~~!っと下へコピーします。

そして、D2セルに
=IF(COUNT($B$2:$B$1000)<ROW(A1),"",INDEX($A$2:$A$1000,SMALL($B$2:$B$1000,ROW(A1))))

E2セルに
=IF(D2="","",COUNTIF($A$2:$A$1000,D2))

という数式を入れ、D2・E2セルを範囲指定し、E2セルのフィルハンドルで
下へコピーすると画像のような感じになります。

尚、数式は1000行目まで対応できるようにしていますが、
データ量によって範囲指定の領域はアレンジしてみてください。

以上、長々と書きましたが
参考になれば幸いです。m(__)m

QエクセルのIF関数で、文字が入力されていたならば~

エクセルのIF関数で文字が入力されていたならば~、という論理式を組み立てたいと思っています。

=IF(A1="『どんな文字でも』","",+B1-C1)

A1セルに『どんな文字でも』入っていたならば、空白に。
文字が入っていなければB1セルからC1セルを引く、という状態です。

この『どんな文字でも』の部分に何を入れればいいのか教えてください。

またIF関数以外でも同様のことができれば構いません。

宜しくお願いします。

Aベストアンサー

=IF(ISTEXT(A1),"",B1-C1)

でどうでしょうか?

Q[EXCEL]列の項目を何種類かカウントする方法

いつもお世話になっています。Excelで3000件ぐらいのデータを整理したいのですが、普通に合計などはできますが、1列に何種類かのデータが色々な順で混在している場合、それが全部で何個かではなく、何種類かをすぐに計算する方法はありますか?

たとえば、"りんご"・"みかん"・"いちご"・"りんご"・"ばなな"
とそれぞれ入力しているとして、個数は5個だけれども種類は4種類ですよね。この種類の数を出せるとうれしいのですが。どなたか教えてください。
今、使っているのはOffice2003ですが、方法が違うようならOffice2000での方法もわかる方いらっしゃれば教えてください、お願いします。

Aベストアンサー

自信はありませんが、
以下の数式は、1件目のデータを1と返し、2件目移行に出現する重複データを0と返す数式です。

=IF(A1=0,0,IF(COUNTIF($A$1:A1,A1)>1,0,1))

はじめに、上記式のセル番地A1とはデータの始まりがセルA1の場合ですので、ご自身のデータで始まりのセル番地と置き換えて下さい。あとはオートフィルでコピーです。
最後に、ランダムに算出された1と0に対し、SUM関数で合計を求めれば種類(重複しない個数)を出すことができるかと思います。


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング