いつもお世話になっております。excel97の関数に関してです。
過去問を検索し、
http://oshiete1.goo.ne.jp/kotaeru.php3?q=118918において、

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

回答:関数でやるとすると。。。。
データが、A1~A10に入っているとします。
=SUMPRODUCT(1/COUNTIF(A1:A10,A1:A10))
とA11に数式を入力します。
これではいかがでしょうか??
解説
COUNTIF関数でそれぞれの会社の数をカウントし、
SUMPRODUCT関数で配列の積をもとめます。

というのがあり、未熟者の私は理屈はよく分からないまま、この式でやってみたあと実際に数えてみたのですが、いつも正解数より1多くなってしまうのですが、この式の最後に-1を付ければいいのかな? と思ったのですが、いかがでしょうか?

このQ&Aに関連する最新のQ&A

A 回答 (8件)

GO! ・・・(*_*)?



※一応、適当な箇所で会社名を" "に変えてみて、結果が正しいかご確認を・・・
    • good
    • 0
この回答へのお礼

無理を聞いて頂き、ありがとうございます!
確認もしました。

お礼にhiromuyさまの質問が、私のわかることならお答えしようと思い(パソコン関係は無理そうですが(^^;)、検索してみましたが、残念ながら、ひっかかりませんでした。(文章中に名前が出てこないと、ひっかからないそうですね。)
感謝の表しようがありませんが、本当に助かりました。

お礼日時:2002/02/13 14:06

hiromuyです。


あれ!?そうですか。う~ん?
数式後半のIF文中で、
COUNTIF(A1:A10,0)=0が条件式ですが、これはA1:A10の範囲にあるブランクセル(数式は入力されている)の個数が0の場合ということで、
TRUEの場合、0
FALSEの場合、1
を返すようにしています。
従って、ブランクセルが1つ以上あれば、FALSEの場合の「1」が返り、-1されると思ったんですが・・・
(ブランクセルが0個(ない)場合はTRUEの「0」が返り、-1はされない)
IF(COUNTIF(A1:A10,0)=0,0,1)
これでいけると思ったんですが、最後の並びが(0,1,0)でうまくいくのであれば、他の原因があるのでしょうか?
色々想像してみたのですが、申し訳ありませんが今のところ思いつきません。

この回答への補足

たびたびほんとうにありがとうございます。遅くなり申し訳ありません(風邪でダウンしておりました)。
確かに、そう言われてみると、理屈としては(0,1,0)ですね。
それで、ブランクをゼロと認識しているのかどうなのかがそもそも疑問なので、(A1:A10," ")と、この部分を0の代わりに" "としてみました。そうしたら、IF(COUNTIF(A1:A10," ")=0,0,1)
この式で、正しい結論が導けるようです。
hiromuy様、また甘えて申し訳ありませんが、これで良し! と、ゴーサインを頂けると大変心強いのですが...。

補足日時:2002/02/12 11:15
    • good
    • 0

遅ればせながら再登場です。


ブランクセル(数式は入力されている)は「0」と見なされている様ですので、下記のように、
=SUMPRODUCT(1/COUNTIF(A1:A10,A1:A10))-IF(COUNTIF(A1:A10,0)=0,0,1)
として、ブランクが1つでも存在した場合に-1するようにしてみるのはどうでしょうか。

この回答への補足

ありがとうございます。
やってみました。もしや、最後の(・・・=0,0,1)は、(・・・=0,1,0)ではないでしょうか? それだと理屈上も納得ですし計算もうまくいくのですが。
手取り足取り教えて頂かないとだめなもので、いちいちホント申し訳ありません。

補足日時:2002/02/05 17:13
    • good
    • 0

再び こんばんは!



>ブランクセルを1と数えてしまうようです。
MATCH()関数ではブランクセルがあると#N/Aとなりますので
たぶんスペースが入力されているセルがあるのでは?

・両方(ブランク スペース)をカウントしない式

配列数式です

 =SUM(IF(TRIM(A1:A10)="",0,(MATCH(A1:A10,$A$1:$A$10,0)=ROW(A1:A10))*1))

と数式バーに入力後(数式バーにカーソルがある状態で)
 [Ctrl]+[Shift]を押したまま[Enter] で入力確定
    • good
    • 0
この回答へのお礼

本当にたびたびありがとうございます。計算式をすべてコピーペーストしたので、スペースが入力されたセルは無いと思います。今回は、no.6の方が教えて下さったやり方で行こうと思うのですが、後学の為に、もしよろしければ教えて下さい。[Ctrl]+[Shift]を押したまま[Enter] というのは、単にEnterで入力確定するのと、どう違うのでしょうか。やってみたところ、{}のカッコが自動的に付いた気がするのですが、それは関係無いでしょうか?

お礼日時:2002/02/05 17:43

こんにちは!



A1からA10であれば 記載された式でも問題なく
結果が返る筈ですが、皆さんの書かれている通り
項目行を含めているのでは?

ただし範囲が大きいと非常に重くなります。
配列数式の中でCOUNTIFで最終範囲まで配列検索
しますから・・・・

以下の式にすれば

 =SUMPRODUCT((MATCH(A1:A10,$A$1:$A$10,0)=ROW(A1:A10))*1)

MATCH()はヒットしたところで検索を終了しますので
多少は軽くなると思います。

範囲がA2:A1000の場合でも ROW()内の範囲は必ず
A1から初めて同配列数で終了にして下さい→ A1:A999

 =SUMPRODUCT((MATCH(A2:A1000,$A$2:$A$1000,0)=ROW(A1:A999))*1)
    • good
    • 0
この回答へのお礼

ニューアイディアをありがとうございます。

やってみましたが、やはり、1多くなってしまいました。

下にも書きましたが、ブランクセルを1と数えてしまうようです。
もー、嫌いっ変なパソコン! (八つ当たりですね(^^;)

お礼日時:2002/02/01 20:03

>理屈はよく分からないまま...



=SUMPRODUCT(1/COUNTIF(A1:A10,A1:A10))
の意味を考えてみると、これは、

 1/COUNTIF(A1:A10,A1)
 1/COUNTIF(A1:A10,A2)
 1/COUNTIF(A1:A10,A3)
    :
 1/COUNTIF(A1:A10,A10)

の合計でしょう。例えば、SUMPRODUCT(B1:B10)=SUM(B1:B10)が成立するのと同じ理屈です。
配列の積を求めているよりも、配列の和の機能(×1をしてたしている)ですね。

上の式を具体化してみると、6行あったとして、

 AA 3 1/3
 AA 3 1/3
 AA 3 1/3
 BB 2 1/2
 BB 2 1/2
 CC 1 1/1

となります。2つ目が『COUNTIF(A1:A10,A1)』に対応、3つ目が 『1/COUNTIF(A1:A10,A1)』に対応。
3つ目を全部たすと『3』になるわけです。
重複個数を数えて、例えば10個あればその価値を1/10にして、10個たして『1』が出てくるわけです。

そう考えると、算式で誤差がでるとは考えにくいですね。『-1』するよりも、先頭行に『会社名』とかの表題が入っていないでしょうか。その場合は、算式のA1をA2に変えればいいと思います。

参考になった?
    • good
    • 0
この回答へのお礼

ありがとうございます!

1/COUNTIF(A1:A10,A1)
 1/COUNTIF(A1:A10,A2)
 1/COUNTIF(A1:A10,A3)
    :
 1/COUNTIF(A1:A10,A10)
これを見て、理屈が分かりました! 感動です。
でも、下にも書きましたが、空欄をカウントしているようなのです・・・。

お礼日時:2002/02/01 19:52

関数に問題はなさそうですので、同じ会社名でも文字が全角/半角で違っていたり、会社名の最後または途中にスペースが入ってないか確認してみてください。


もし、上記のようなものがあれば、違う内容と判断されてしまいます。
    • good
    • 0
この回答へのお礼

あっ、昨日お世話になったhiromuyさま、この質問にもお答え頂き、ありがとうございます!
「会社名」は私の場合会社コードで、しかも元データからそのままコピー&ペーストしているので、間違いないはずなのです。
ただ、下にも書きましたが、空欄を1とカウントしてしまうようなのです。

お礼日時:2002/02/01 19:50

> =SUMPRODUCT(1/COUNTIF(A1:A10,A1:A10))


これを実行すると「10」が取得できます。

「会社名が9件なのに」ということならば「タイトル行」を加算してませんか?

この回答への補足

ありがとうございます!
タイトル行加算していません。
どうやら、ブランクを1とカウントしてしまうようなのです。
どの列もブランク(計算式は入っているが、その結果ブランクとなっている)のセルがあるので、それで、必ず、1加算されてしまうようなのです。
変ですよね?

補足日時:2002/02/01 19:40
    • good
    • 0

このQ&Aに関連する人気のQ&A

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

このQ&Aを見た人が検索しているワード

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

QSUMPRODUCT($A$1:$A$10,$B$1:$B$10)とSUMPRODUCT($A$1:$A$10*$B$1:$B$10)

エクセルのSUMPRODUCT関数についてお尋ねします。

=SUMPRODUCT($A$1:$A$10,$B$1:$B$10)

=SUMPRODUCT($A$1:$A$10*$B$1:$B$10)
の違いは何でしょうか?
両者とも同じ答えを返しますが、いろいろ試したところ、前者は範囲内に文字列があってもそれを無視して計算し、後者は文字列があればエラーになるようですが、その理解で正しいでしょうか?
正しいとすれば、なぜでしょうか?

Aベストアンサー

こんばんは。

配列計算をしているのは言うまでもありませんが、この、SUMPRODUCT は、必ず、内部のひとまとまりから計算するという性質を持っています。ただ、VBAのように左から時系列かどうかは、言語が違いますから、分かりません。しかし、この引数は、パラメータ配列になっています。そして、その引数それぞれの有効値を調べて、それを演算出来る値かどうか調べます。

文字列を入れてもエラーにならないのは、元々、そこに注釈などを入れるために考えられたものです。値を文字列か数値かを精査して演算するように出来ています。最初、SUM関数に対し、そのように作られました。プログラムとしては、初歩的なものですが、そのアイデアは、今日まで、踏襲しているようです。このオリジナルの仕組みを考えたのは、VisiCalc の開発者、ブルックリン氏です。昔々、本で読んだことがあります。 PRODUCT関数とも共通です。3つの関数は、同じような仕様を持っています。

こちらが、本来の使い方です。

=SUMPRODUCT($A$1:$A$10,$B$1:$B$10)

つまり、
$A$1:$A$10 と $B$1:$B$10 の値を別々に取得しています。

では、
=SUMPRODUCT($A$1:$A$10,$B$1:$B$11)

とすると、なぜ、エラーが出るかというと、$B$11 に対応する数値が、NULL値(有効な値がない)だからです。それは、全体に反映させてしまいます。(理由は分かると思いますが、プログラムが途中で、エラーで止まってしまうからです)

ところが、こちらは、

=SUMPRODUCT($A$1:$A$10*$B$1:$B$10)

$A$1:$A$10*$B$1:$B$10 は、分割出来ません。したがって、この数式の中でのエラーは、そのまま、元の数式に反映されます。

ご自分で、ユーザー定義関数を作ってみれば分かります。VBAでも、同じように作ることが可能です。一度、試してみるとよいです。

こんばんは。

配列計算をしているのは言うまでもありませんが、この、SUMPRODUCT は、必ず、内部のひとまとまりから計算するという性質を持っています。ただ、VBAのように左から時系列かどうかは、言語が違いますから、分かりません。しかし、この引数は、パラメータ配列になっています。そして、その引数それぞれの有効値を調べて、それを演算出来る値かどうか調べます。

文字列を入れてもエラーにならないのは、元々、そこに注釈などを入れるために考えられたものです。値を文字列か数値かを精査して演算...続きを読む

Q=IF(ISNA(VLOOKUP($A1,sheet2!$A$1:$B$12,2,FALSE)),"",VLOOKUP($A1,sheet2!$A$1:$B $

いつもお世話になってます。
以下の関数式について、お時間がありましたらどうぞご教示ください。

=IF(ISNA(VLOOKUP($A1,sheet2!$A$1:$B$12,2,FALSE)),"",VLOOKUP($A1,sheet2!$A$1:$B $12,2,FALSE))

「シート2の範囲指定した表にA1セルの値と同じ値の右隣になる値を返せ。ただし該当なき場合は空白とせよ。」

純粋になんでこのような構文になるのかが解りません。

1.ISNAってそもそもなんでしょう?
2.同じ式を繰り返すのはなぜ?
(模範式で、このように同じ式を繰り返す構文があまり無いように思えたのです。)

・参考となる他所のページがあれば教えて下さい。
・素人です。お手柔らかにお願いします。

(エクセル2003)

Aベストアンサー

1.ISNAってそもそもなんでしょう?
ISで始まる情報関数の一つで、#N/A!エラーのみを判定する関数
結果はTRUE(真),FALSE(偽)のいずれかになります。
エラー判定のIS関数には他に
ISERR:#N/A!を除くすべてのエラーを判定する関数
ISERROR:すべてのエラーを判定する関数
があります。

2.同じ式を繰り返すのはなぜ?
ISNAの判定する値がセルでなく数式の結果だからです。
A2=VLOOKUP($A1,sheet2!$A$1:$B$12,2,FALSE)
なら
A3=IF(ISNA(A2),"",A2)
ということになります。A2のように計算の為のセルを省略する為に
=IF(ISNA(数式),"",数式)のように同じ数式を2回繰り返しになってます。

QSUMPRODUCT関数とCOUNTIF関数

A-001
A-001
A-002
A-003
B-001
B-002
A-001
C-001
C-002
とあったときに仮に9個の製品コードであるとして、
A-001は3回でてきていますので種類としては7種類の
製品コードということになります。

"質問:様々ある素材のうち重複しているものは1つだけカウントして上記の7というような値を求める関数がありますか?"

以前このような質問をし、

”=SUMPRODUCT(1/COUNTIF($A$1:$A$9,A1:A9))”
との回答をいただきました。回答通りにやると値が求められました。とても大助かりでした。

そこで、関数のヘルプで
どういう関数かを勉強しましたが僕には理解できませんでした。配列とかなんとかやらでてきて……超ムズイ!!

だれかこんな僕にとても分かりやすいことばで
SUMPRODUCT関数とCOUNTIF関数を教えていただけませんか?EXCELのヘルプは難しすぎる……
宜しくお願いします。

Aベストアンサー

SUMPRODUCT関数は配列の積の和を求める関数です。
SUMPRODUCT({1,2,3,4},{1,2,3,4})=1*1+2*2+3*3+4*4=30
になります。

配列に条件を設定した場合、結果はTRUE,FALSEで返されますが
これを計算に使う場合、TRUE=1,FALSE=0として使用できます。
COUNTIFと同じ用法
=SUMPRODUCT(({1,2,3,4}>2)*1)=0*1*0*1+1*1+1*1=2
となり条件(>2)にあう件数は2となります。
SUMIFと同じ用法
=SUMPRODUCT(({1,2,3,4}>2)*{1,2,3,4})=0*1*0*2+1*3+1*4=7
となり条件(>2)にあうデータの集計は7となります。

上記の数式の場合は
=COUNTIF($A$1:$A$9,A1)=3
=COUNTIF($A$1:$A$9,A2)=3
=COUNTIF($A$1:$A$9,A3)=1
・・・
=COUNTIF($A$1:$A$9,A9)=1

=SUMPRODUCT(1/COUNTIF($A$1:$A$9,A1:A9))
は上記の結果を分母とした数値の和なので
=1/3+1/3+1+1+1+1+1/3+1+1=7
のように、1/件数*件数=1となるため結果として重複した値を1件として
カウントすることが可能になります。

SUMPRODUCT関数は配列の積の和を求める関数です。
SUMPRODUCT({1,2,3,4},{1,2,3,4})=1*1+2*2+3*3+4*4=30
になります。

配列に条件を設定した場合、結果はTRUE,FALSEで返されますが
これを計算に使う場合、TRUE=1,FALSE=0として使用できます。
COUNTIFと同じ用法
=SUMPRODUCT(({1,2,3,4}>2)*1)=0*1*0*1+1*1+1*1=2
となり条件(>2)にあう件数は2となります。
SUMIFと同じ用法
=SUMPRODUCT(({1,2,3,4}>2)*{1,2,3,4})=0*1*0*2+1*3+1*4=7
となり条件(>2)にあうデータの集計は7となります。

上記の数式の...続きを読む

Qエクセルの数式で、COUNTIF(A1:A100,">""") 

http://oshiete1.goo.ne.jp/kotaeru.php3?q=2224824

の関連質問です。

表A1:A100にある、各セル内の数式で求められた計算結果が文字列の場合、その数を調べるには、

=COUNTIF(A1:A100,">""")

で、各セルの計算式の答えが "" 以外の文字列の数を返してくれるようです。(数値や空白セルはカウントされません。)

非常に便利なのですが、ちょっと腑に落ちないのは、  >""  って、""より大きいということですよね?
しかし文字列は数字とちがい大小はないのではないでしょうか?

もう一点疑問です。
ためしに、=COUNTIF(A1:A100,">=""")  としてみました。
今度は、  >=""  ですから、""も含むということになるはずですよね?
ところが答えが  ""  となるものをカウントしません。

不思議でたまりません。
よろしくお願いします。

Aベストアンサー

#1 の回答前半部は、

  ”文字列と文字列”の比較に">"や"<"が使えるか?

についてのコメントです。結論として #1 で説明したとおり”使えます。”

  ・論理式("A" > "B")  --> False
  ・論理式("A" < "B")  --> True
  ・論理式("A" > "")  --> True
  ・論理式("1" > "A")  --> False (文字列の数字と文字列)

ところで、Excel のワークシート上ではユーザーの利便性のため、本来
型不一致でエラーとなるはずの論理式(1 > "A") で False が返ります。(数値と文字列)

このおかげで、ユーザーはデータ型について普段意識せずに済むわけ
ですが、これはイレギュラーで、True にならないなら False 、、と単純
に評価しているに過ぎません。本来プログラムの世界では、

  ・論理式(1 > "A")   --> 型不一致でエラー

で比較不能です。比較する以前にエラーになってしまいます。ここで、
COUNTIF 関数も当然プログラムが提供している機能だということに注目
して下さい。

> ... =COUNTIF(A1:A100,">""") でなぜ数値は除外されるのでしょうか?

数値がカウント除外されているのは、COUNTIF 関数が "" を長さ 0 の
文字列と解釈しているのであれば、文字列と数値の比較のように比較不能
でエラーとなる場合には、カウントしないように実装されているから
だと思います。

=COUNTIF(A1:A100,">""")
=COUNTIF(A1:A100,">=""")

この両者がなぜ同じ結果を返すかについては、=COUNTIF(A1:A100,"ABC")
で ABC と入力されたセル数を返すところを見る限り、文字列の場合は、
イコールを書かなくても補完されるようです。

そのため、イコールは書いても書かなくても結果が同一になるのでしょう。


COUNTIF 関数の第 2 引数である条件部は、文字列で指定します。

内部的にどのように処理されているか正確なところは、COUNTIF 関数の
作成者にしか分からないのですが、比較を行うためには、この文字列で
渡された条件部を論理式に展開する必要があります。

その過程で、数値化できないものは文字列として扱う、としているよう
ですね。

したがって、

> =COUNTA(A1:A100)-COUNT(A1:A100)

は、次のように書いても OK みたいです。

=COUNTIF(A1:A100,"=*") ... イコールはあってもなくてもOK

以下は文字列以外のセルです。ブランクセル+文字列以外のデータがあるセル

=COUNTIF(A1:A10,"<>*")


> =SUMPRODUCT((A1:A100>="")*1)ですと、式が入っていない空白セルも
> カウントするようです。

すみません。これだとブランクセルを含めてしまいますね。その通りです。

#1 の回答前半部は、

  ”文字列と文字列”の比較に">"や"<"が使えるか?

についてのコメントです。結論として #1 で説明したとおり”使えます。”

  ・論理式("A" > "B")  --> False
  ・論理式("A" < "B")  --> True
  ・論理式("A" > "")  --> True
  ・論理式("1" > "A")  --> False (文字列の数字と文字列)

ところで、Excel のワークシート上ではユーザーの利便性のため、本来
型不一致でエラーとなるはずの論理式(1 > "A") で False が返ります。(数値と文字列)

...続きを読む

Q「A1:A100までを1、A101:A200までを2・・・・」のような操作をA30000くらいまで行いたいのですが

エクセルでサイン波をアップサンプリングしたいのですが

具体的には{A1、A2、A3、A4、A5、A6・・・・}={1,2,3,4,3,2、・・・}
A1:A100までが1、A101:A200までが2・・・・のように入力し、


_________________________________________________4・4・・・・・・4・4
________________________________3・3・・・・・3・3__________________3・3・・・・・3・3
________________2・2・・・・2・2_____________________________________________________2・2・2・2・2・
1・1・・・・1・1

のようにするイメージです。(ただ、グラフにするわけではありません)
オートフィルで行うとセル数が何万という数の場合、途方もなくなってしまうのでよろしくお願いします。

Aベストアンサー

A列に1,2,3,4,3,2とあってこれをB列に100セル単位にするというのであれば
B1=INDIRECT("A"&INT((ROW()-1)/100+1))
でこれを下方にコピーしてください。


このカテゴリの人気Q&Aランキング

おすすめ情報