COUNTIFでの条件式と動作の理由を教えて下さい

Question

添付の図のように　=COUNTIF($A$4:$A$8,"<=" & $A$4:$A$8)という数式を作ると、
数式のあるセルの位置（行位置）によって、表示される値が変わります。
この数式を使うと、ある範囲にある文字列の大小順位がだせるようなのですが、
どうしてこのような結果を出せるのがわかりません。
動作理由や、使用上の留意点を教えて下さい。

WindFaller · Accepted Answer

私が#5で書いたものでは通じていないようなので、もう一度まとめさせていただきました。

この関数の原型は、LotusグループからIBMにノウハウが移動したものを、マイクロソフトの開発チームがあれこれと考えて、手を加えたものだとは思います。その仕組みを知りたいというなら、C言語や、せめてVBAで同じ表現ができるように作ってみて、ある程度の概要が分かるもので、それをワークシート上では、表面的なものでしか分からないと思います。

また、私は、その中身を、深く追求するような興味はありませんでしたが、少々、このままでは、治まらないので、VBAで作ってみました。文字比較とは言っても、文字コードではなく、バイナリ比較(binary compare[ワークシート上の制約あり])になっているだけです。しかし、数値と文字の比較に関して、別途に出てくるというようなので、数値のcountを別々に取りました。そういう仕様で振り分けているのだというしかありません。

もっと複雑なものを想像していましたが、どうやら、その結果は偶然の産物のようです。意図して作られたものではないようです。まだ、修正点はあるかもしれませんが、一応、仕様は分かったような気がします。

興味のある方はどうぞ試してみてください。
'//標準モジュール
'//

Public Function mCountIf(arg1 As Range, arg2 As Variant)
Dim bln As Boolean
Dim fml As String
Dim cnt As Long, cnt2 As Long, cnt3 As Long
Dim expr As String
Dim ope As String
Dim c As Variant
arg2 = Trim(arg2)
Select Case True
Case arg2 Like "=>*"
　 mCountIf = 0:　 Exit Function
Case arg2 Like "=<*"
　 mCountIf = 0:　 Exit Function
Case arg2 Like ">=*"
　 ope = ">=": expr = Mid(arg2, 3)
Case arg2 Like "<=*"
　 ope = "<=": expr = Mid(arg2, 3)
Case arg2 Like "<>*"
　 ope = "<>": expr = Mid(arg2, 3)
Case arg2 Like ">*"
　 ope = ">": expr = Mid(arg2, 2)
Case arg2 Like "<*"
　 ope = "<": expr = Mid(arg2, 2)
Case arg2 Like "=*"
　 ope = "=": expr = Mid(arg2, 2)
Case Else
　 ope = "=": expr = arg2
End Select
For Each c In arg1
　If VarType(c) = vbString Then
　fml = "=""" & c.Value & """" & ope & """" & expr & """"
　 If Evaluate(fml) Then
　　 cnt = cnt + 1
　 End If
　ElseIf VarType(c) = vbDouble Then
　 fml = "=""" & c.Value & """" & ope & """" & expr & """"
　　If Evaluate(fml) Then
　　 cnt2 = cnt2 + 1
　 End If
　Else
　　 cnt3 = 0
　End If
Next c
　Select Case True
　Case VarType(expr) = vbString And expr <> "" And Not IsNumeric(expr)
　mCountIf = cnt
　Case IsNumeric(expr)
　 mCountIf = cnt2
　Case expr = ""
　 mCountIf = cnt3
　End Select
End Function
'//

moto_koukousei · Answer

確かに、No.4の回答のように
１．セル B4 の式　=COUNTIF($A$4:$A$8,"<="&$A$4:$A$8)　をドラッグコピーすると
'　　　　　　　　　=COUNTIF($A$4:$A$8,"<="&C4)　のドラッグコピーは等しいですね。
配列数式の際にそうしたことが起きるのと似ていると思います。

No.1の回答と回答に対するコメントをやってみると、確かに、何が起きているのかが、私にはわかりません。単純に文字列コードをベースにしているのではなさそうです。（No.4さんのを参考にしました）

No.3の回答は、根気不足だけではなく、内容が難しくて、｢★だから、B6に入力した"<=" & $A$4:$A$8という部分は実際は "<=" & $A$6となるのわかる｣とは私にはならないです。
文字列と数値のことも、｢★最初が要するに最初2文字までに来る文字で、不等号の種類を判別して、それを除いたやつをmaStringに代入して　pFormatter(多分nullptrではない)がnullptrでないときは、数値等として解釈可能であるか調べ、解釈可能であったときは、数値比較、それ以外は文字列比較、ということ｣というのも理解できないですが、添付画像のように、C12:C26、E12:E26、I12:I26　となることを説明できているのかさえも、私にはわからないです。

WindFaller · Answer

こんにちは。

=COUNTIF($A$4:$A$8,"<=" & $A$4:$A$8)

これは、一種の配列計算ですね。どこが配列かというと、"<=" の後は、本来、数字か文字列なのですが、ここに配列になっているわけで、これは、制作側の非公開の隠しコマンドだと思います。マニュアルには載っていませんから。
理由は分かりません、制作側の気まぐれだと思います。

制作上、配列の引数をエラーにしても良かったのではないかと思います。
配列式ではあるのですが、この数式表現は別の配列数式にも書き換えられます。

B4: =SUMPRODUCT(($A$4:$A$8<=$A4)*1) 下にドラッグコピー

添付図をご覧になってください。
仕組みは、

B4:=($A4<=B$3)*1
B5:=($A5<=B$3)*1

= ("hg" <= "hg")*1 
=1

= ("hhggfs" <= "hg")*1 
=0

となっています。
9行目は、SUM合計で作られています。

たんなる文字の比較ですが、COUNTIF <= は、等しいか、それ以上(文字コード)なら、1と数えることになっています。

なお、必ず、数値 <文字列です。もし、比較するなら、数値を文字列に変えて、文字列と比較しなければなりません。

567 <"123" という等式が成り立ちます。単純な比較する時は、文字列と数値とは混在はできません。

mike_g · Answer

こういう不思議な現象に遭遇した場合の私の常套手段は推理に基づいて実験することです。
推理のネタは文字コードです。
１．式 =$A$4:$A$8 を入力したセル C4 を下方にオートフィル
￣￣その結果を添付図の列Ｃ(橙色)です。
もうお分かりと思うけど、貴方が示したセル B4 の式
=COUNTIF($A$4:$A$8,"<="&$A$4:$A$8)
は
=COUNTIF($A$4:$A$8,"<="&C4)
と同じことで、貴方の添付図に示した他のセルの式もその「同じ」ものをオートフィルしたものに等しいですね。

先を急ぎませう。
２．式 =CODE(MID($A4,COLUMN(A4),1))*1 を入力したセル D4 を右隣および
￣￣下方にオートフィル(黄色)
３．式 =(D4&TEXT(E4,"000"))*1 を入力したセル F4 を下方にオートフィル(薄
￣￣青色)
４．式 =COUNTIF(F$4:F$8,"<="&F4) を入力したセル G4 を下方にオートフィ
￣￣ル(赤地に白抜数値)

私の推理、当たっているかしらン？

以上、此れでも、ゴチャゴチャ講釈は出来るだけ省いたつもり。

himajin100000 · Answer

部分点狙いの回答。(【なかなか本題に行かない】) 満足してもらえるかわからないが、根気よく読んでもらえれば理解はしてもらえるだろう。 ★Excelのソースコードは当然配布されないのでわからないが、LibreOfficeもある程度追従するだろう、ということを飲み込んでもらえると嬉しいなぁ。ここを妥協してもらえないとあと全部無駄になるからなあ * 逆ポーランド記法を用いた電卓の実装方法 https://knowledge.sakura.ad.jp/220/ これを最後まで読んだ上で。 >高級な言語ではコンパイルといえばもう少し複雑な手法と手順で構文解析を行います。 * 演算子と関数は本来同じもの！シンタックスシュガー。(ただし、言語の文法が中置記法でしか書けないものだったりするだけ)。オーバーロードの概念がある言語の場合、演算子オーバーロードの説明を読んでおくのも吉。 * BNF記法について http://www.atmarkit.co.jp/fxml/ddd/ddd004/ddd004-bnf.html ** 直接関係ないけど、練習としてJavaの仕様とか見ると面白い。上記BNFがわかっていればそれほど苦労しないはず。 https://docs.oracle.com/javase/specs/jls/se9/html/jls-14.html#jls-14.8 再帰的に定義されている。 StatementExpression(式文) => Assignment(代入) => Expression(式) => AssignmentExpression(代入式) => ConditionalExpression 以下略と辿っていくとこんなのにぶち当たる。 15.18. Additive Operators AdditiveExpression: MultiplicativeExpression AdditiveExpression + MultiplicativeExpression AdditiveExpression - MultiplicativeExpression 15.17. Multiplicative Operators MultiplicativeExpression: UnaryExpression MultiplicativeExpression * UnaryExpression MultiplicativeExpression / UnaryExpression MultiplicativeExpression % UnaryExpression ところで、15.2にはこんな記述がある。 Precedence among operators is managed by a hierarchy of grammar productions. The lowest precedence operator is the arrow of a lambda expression (->), followed by the assignment operators. Thus, all expressions are syntactically included in the LambdaExpression and AssignmentExpression nonterminals: 演算子の優先順位は文法生成規則(BNFや上記Javaの文法で見てきた各定義のことね！)の階層構造による。最も優先度の低い演算子はラムダ式のアロー演算子であり、代入演算子が続く。こういう感じで全ての式は文法上LamdaExressionかAssignmentExpressionの非終端記号に含まれる。というわけで。【少なくともJavaにおいては、掛け算の方が足し算よりも優先される】のでした。 * 暗黙の型変換 https://asciidwango.github.io/js-primer/basic/implicit-coercion/ * 実際のLibreOfficeの実装は？ ScCompiler関連クラスが主にユーザーが入力した、中置記法を含む式を逆ポーランド記法に変換している。以下例 https://opengrok.libreoffice.org/xref/core/formula/source/core/api/FormulaCompiler.cxx?r=9c50b0ed#1885 ScInterpreterクラスが主に逆ポーランド記法になった式(実際にはTokenArray型)を評価している、練習として余りを求める関数Modを読んでみよう。【スタックから引数を逆順に取り出していること】を忘れずに。まぁ最初は引数の数を1バイト乗っけてあってGetByte()で取ってるみたいだけど。 ScInterpreter::GetDouble()はスタックの一番上の要素を取り出し、【double型に型変換して返す】。GetString()なんかは、スタックの一番上の要素をStringに変換して返す。GetStackType()なんかは情報見るだけでスタック弄らないみたいだけど。ちなみにScInterpreter::PopString()は型変換しない。欲しい型のデータじゃない場合はエラーになる。 https://opengrok.libreoffice.org/xref/core/sc/source/core/tool/interpr2.cxx?r=00bc5a09#2371 そうScInterpreter::ScMod()側で型変換している。結果としてエンドユーザー側からは暗黙の型変換に見える。本題だ。文字列連結演算子&は、ScInterpreter::ScAmpersand()で当然 ScInterpreter::GetString()を呼んでいる。 https://opengrok.libreoffice.org/xref/core/sc/source/core/tool/interpr4.cxx?r=00bc5a09#2347 A1みたいなやつはsvSingleRef,A1:A2みたいなやつはSvDoubleRefって型だ。 ScInterpreter::DoubleRefToPosSingleRef( aRange, aAdr )って奴が呼ばれるみたいだからちょっと定義読んでみようかな。各引数は参照渡しだ。行列(Matrix)周りは全部読み飛ばしてっと。 https://opengrok.libreoffice.org/xref/core/sc/source/core/tool/interpr4.cxx?r=00bc5a09#1982 あ、aPosは式を入力したセルで、Tab()はシート番号を返すんだよねー。 ★だから、B6に入力した"<=" & $A$4:$A$8という部分は実際は "<=" & $A$6となるのわかるだろうか？同じA列で、4 <= 6 かつ 6 <= 8だからね。いや正直絶対参照か相対参照かはちゃんと調べてなくて自信ないんだけど(汗) さてこの調子で、エンドユーザー視点の使い方を思い出しながら、ScInterpreter::ScCountIf()も読んでみようか(にっこり) https://opengrok.libreoffice.org/xref/core/sc/source/core/tool/interpr1.cxx?r=00bc5a09#5442 まず最後の引数は条件だったね。 Value(数値等)の場合その値をfValに代入して、bIsStringがfalseになり… 可変長引数絡みでwhileでくくっている部分があるのを臨機応変に解釈。ここはそのままSvDoubleRefを変換せず扱う、っと。 5580行周りで、数値比較であることを確認して5595行あたりの分岐で行列とそれ以外に応じて分岐しているけどどっちにしても要素列挙しながら数え上げているだろうことは同じ。、最後の引数が文字列だったら…？ ScQueryParamBase::FillInExcelSyntaxが呼ばれているな…。 https://opengrok.libreoffice.org/xref/core/sc/source/core/tool/queryparam.cxx?r=db808563#226 copyメソッドは、文字列の一部を取ってくるメソッドとして読むと… ★最初が要するに最初2文字までに来る文字で、不等号の種類を判別して、それを除いたやつをmaStringに代入して pFormatter(多分nullptrではない)がnullptrでないときは、数値等として解釈可能であるか調べ、解釈可能であったときは、数値比較、それ以外は文字列比較、ということだ。だから例えば、 =COUNTIF(A1:A2,">3") は数値3との比較だが、B1に3が入っていたとして =COUNTIF(A1:A2,">B1")であったときには "B1"という文字列との比較になる、ということだ。 ★手元のLibreOfficeの環境だと =COUNTIF(A1:A2,">2017-01-01")は日付=>数値比較になったりする。面倒くさいから調査してないだけだが、「どんな文字列が数値比較になるか」をちゃんと把握しておく必要がありそう。注意事項は★に記しておいた。ちゃんと把握するのはそれ自体難しい。

tom04 · Answer

No.1です。

＞データはA$3:A$20　（文字のセルと、数値のセルの混在です）
＞しかし、順位は正しくないです。　異なる値に同順位がでます。

具体的にどのようなデータが入っているのか判らないのですが、
文字コードが基準になっていると思います。

ただ気を付けなければならないのは
「文字列」セルと「数値セル」が混在している場合は思ったような並び替えができないことがあります。
私にはサイトで検索してもその原因ははっきり判りません。

前回投降した数式は文字列の並び替えより数値セル（RANK関数）の同順位を回避するために使うコトがほとんどです。
（文字列の並び替えで使ったコトはないと思います）

↓のサイトが参考にならないでしょうか？

http://officetanaka.net/excel/function/tips/tips40.htm

的確な回答でなくてごめんなさい。m(_ _)m

tom04 · Answer

こんばんは！

個人的にそのような使い方をしたことはありませんが、
お示しの使い方は少し裏技的な使い方になります。
式の説明となると当方もなぜそうなるかは、はっきり説明できません。

仮に一気に別列に小さい順（昇順）に表示する場合
=INDEX(A$4:A$8,MATCH(ROW(A1),INDEX(COUNTIF(A$4:A$8,"<="&A$4:A$8),),))

という数式を入れフィルハンドルで下へコピーすると
お示しの画像のB列の昇順にA列データが表示されるはずです。

自分であれば、単に順位付けだけならそんな難しい使い方をせず
B4セルに
=COUNTIF(A$4:A$8,"<="&A4)

という数式を入れ下へフィル＆コピーします。
（数値データのRANK関数と同じコトですかね）

※　仮に同順位（重複データ）があり、順位の差をつけたい場合は
数値データでもRANK関数を使わず
=COUNTIF(A$4:A$8,"<"&A4)+COUNTIF(A$4:A4,A4)
という数式を入れフィル＆コピーで同順位なしの順位が返ります。
（同順位の場合は上側のデータが上位となる）
尚、降順の場合は不等号の向きを変えます。