Excel：セル結合時のCOUNTIFSの動きについて

Question

ExcelのCOUNTIFSを使用する際に、セルが結合している場合に、
想定している結果とは異なる結果が表示されます。
以下の使用方法は制限事項になるのか、また、回避方法を、
ご存知の方がいらっしゃいましたら教えてください。

以下、4パターンの結果を載せています。
※本来は、複数の条件を設定していますので、COUNTIFSを使用しています。

■やりたいこと：
左列に「1」がセットされているときに、右列の「○」の個数をカウントしたいです。
カウントする「○」の個数は、見た目の個数で、結合してある場合は、1個としてカウントをしたいです。

①　想定どおり、3個とカウントされます。　　　　　　　　　=COUNTIFS(D2:D16,"○",C2:C16,"1")
②　8行目に、「1」を追加しても、3個とカウントされます。　=COUNTIFS(G2:G16,"○",F2:F16,"1")
③　12行目に、「1」を追加すると、4個とカウントされます。　=COUNTIFS(K2:K16,"○",J2:J16,"1")
④　13行目に、「1」を追加しても、4個とカウントされます。　=COUNTIFS(O2:O16,"○",N2:N16,"1")

以上の結果から、結合されたセルの先頭に検索する条件があれば、
カウントできるようですが、結合されたセルの中間にある場合は、
カウントできないようです。

これは、この関数の制限事項なのか、また、
セル結合を解除することなく、回避する方法がありましたら、
教えていただけると助かります。

Answer 1

この関数の制限というよりは「セルの結合の仕様」だと思います。セルを結合すると左上のセルの内容が代表値となる以外に左上以外のセルの値が自動的に空になりますよね？

結合前に値が入っていても結合してそれを解除すると左上以外のセルの値が消えていることからもそれがわかります。

対応策は色々あると思いますが、隠しフィールドを設け、vlookupなどを使って結合範囲に1があるかを調べる方法などが考えられると思います。以下は1があったら対応するC列のセルとなり、1がなかったら空文字列になるようにしています。

D2には=IF(ISNA(VLOOKUP(1,B2:B3,1,FALSE)),"",C2)
D3には""
D4には=IF(ISNA(VLOOKUP(1,B4:B5,1,FALSE)),"",C4)
D5には""

このようにしておき

=COUNTIF(D2:D16, "〇")

とすると期待通りになると思います。

---

上記は結合範囲を意識してD列に式を設定しなければならない点がイマイチな点です。結合されているかどうかを判定できればもう少しスマートな式を書けると思ったのでちょっと調べてみたのですが、残念ながらワークシート関数ではセルの結合状態を調べられないようです。

もしVBAを用いるならセルの結合状態も調べられますので上記に都合の良い関数を定義することはできそうです。標準モジュールを作成し以下のような関数を定義する方法です。

Function MergedVLookup(v As Variant, lookup As Range, merged As Range) As Variant
    Application.Volatile
    b = False
    Set ma = merged.MergeArea
    If lookup.Row = ma.Row Then
        row1 = ma.Row
        row2 = row1 + ma.Rows.Count
        col = lookup.Column
        Set lookupRange = Range(Cells(row1, col), Cells(row2, col))
        On Error GoTo L1
        r = WorksheetFunction.VLookup(v, lookupRange, 1, False)
        On Error GoTo 0
        Debug.Print r
        If Not WorksheetFunction.IsNA(r) Then b = True
    End If
    If b Then
        MergedVLookup = ma.Cells(1, 1).Value
        Exit Function
    End If
L1:
    MergedVLookup = ""
End Function

このようにしておいて、

D2には=MergedVLookup(1, B2, C2)
D3には=MergedVLookup(1, B3, C3)
D4には=MergedVLookup(1, B4, C4)
...

このようにしておき

=COUNTIF(D2:D16, "〇")

とすると同様のことができます。この方がC列のマージを変更した際に一々D列の式を自前で変更しなくてよくなるので若干使い勝手がよいと思います。

Comments

[KSwordOfHaste]

あ・・・関数定義にデバッグプリントが残ってました。Debug.Printの行は消してくださいね orz

[x__x]

>>この関数の制限というよりは「セルの結合の仕様」だと思います。セルを結合すると左上のセルの内容が>>代表値となる以外に左上以外のセルの値が自動的に空になりますよね？
そう考えると、他の関数についても同じような動作になると考えられますね。
仰るとおりだと思います。

VBAが触れないメンバーがいるため、今回は、セル結合を解除して、
重複データを取り除く形で、対応しようと思います。

私からセル結合解除しないで、という条件を出したがために、
お手数をおかけしました。

[KSwordOfHaste]

実際問題、自分もVBAを呼び出すことには若干抵抗があります。多少面倒でもワークシート関数のみでなんとかしたくなりますよね・・・「マクロが入ってますよ」と言われるのが鬱陶しいという話もありますし。

> 私からセル結合解除しないで、という条件を...
後半のVBAは「できるのかなぁ」と思ってちょっと調べてみただけですので気になさらないでください。