質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.35%

  • Excel

    1637questions

    Excelは、マイクロソフト社が開発しているデータ集計や分析を行う表計算ソフトの一つです。文書作成や表計算、資料作成などの多彩な機能を備えており、統合パッケージであるMicrosoft Officeに含まれています。

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

解決済

回答 1

投稿

  • 評価
  • クリップ 1
  • VIEW 2,997

x__x

score 2

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")

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

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

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 1

checkベストアンサー

+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列の式を自前で変更しなくてよくなるので若干使い勝手がよいと思います。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/12/25 13:44

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

    キャンセル

  • 2017/12/25 13:51

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

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

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

    キャンセル

  • 2017/12/25 14:07

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

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

    キャンセル

同じタグがついた質問を見る

  • Excel

    1637questions

    Excelは、マイクロソフト社が開発しているデータ集計や分析を行う表計算ソフトの一つです。文書作成や表計算、資料作成などの多彩な機能を備えており、統合パッケージであるMicrosoft Officeに含まれています。