エクセルでセルの結合数を数えたい

質問

添付しました画像のように、リストを作成しました。
親番G、順番、名前、出席と項目を作成し、出席した人は、出席項目に「１」と表示しました。
親番グループは各６グループあり、その中にランダムで人数がいます。
リストの左下に、親番グループ合計（個数）と人数をサブトータルで集計（個数）で表示してあります。

さて、
出席欄にデータフィルターをかけ、「１」をソートすると、左下の合計のところの親番の個数があいません。
出席した人数は正確に表示されますが、親番個数は「４」なのに「３」と表示されます。
親番をセルの結合してあるのが原因と思われますが、
ちゃんと表示させるにはどうしたらよいですか？

回答No.3

MackyNo1 · ベストアンサー

No2の回答の訂正と補足です。

結合セルの空白セルに数式を埋める方法を回答しましたが、このやり方だと簡単なSUBTOTAL関数では重複するデータもカウントするので、正しい結果を得られないですね。

結合セルに数式を埋めた場合は、以下のような配列数式で重複のないデータをカウントすることができます。
配列数式ですので数式を入力後、Ctrl＋Shift+Enterで確定してしてください。

=COUNT(1/(MATCH(IF(SUBTOTAL(3,INDIRECT("A"&ROW(2:20))),A2:A20,"XX"),IF(SUBTOTAL(3,INDIRECT("A"&ROW(2:20))),A2:A20),0)=ROW($A$2:$A$20)-1))

お礼

回答ありがとうございます。
やってみました。できました。

お願いなのですが、
関数式もう少し短くなりませんか？
入力間違えをし、数回やり直してしまいました。

よろしくお願いします。

回答No.5

nishi6

そもそも結合セルに関しては制約があり、結合セルの左上以外はクリアされているのでフィルターも普通ではうまくいきませんね。（当方、Ｅｘｃｅｌ2010での解答です）

質問を見て最初、
「=(SUM(D3:D5)>0)*1」のような式を結合セルの一番上の行（補助列です）にセットしてみて、うまくいくんですが、フィルターをかけない状態がおかしく「×」にしました。

まず、「親番Ｇ」の個数ですが、ユーザー定義関数を作ってみました。「出席」のフィルター結果は当然として、「親番Ｇ」やＢ列についても機能します。やっていることは単純で、非表示になっていない結合セルの個数（種類）をカウントしています。

添付図では、A22セルに、「=MergeCellCount(A3:A20)」のようにして使っています。

ALT+F11 でVBE画面に移動し、メニューの「挿入」＞標準モジュールを行い、標準モジュールに貼り付けます。

Function MergeCellCount(ALLRng As Range) As Long
　　Dim Adr1 As String, Adr2 As String '上下のセルの結合範囲
　　Dim rg As Range　　'各々のセル
　　Dim num As Integer '親番Ｇ数

　　For Each rg In ALLRng
　　　　If Rows(rg.Row).Hidden = False Then '表示セル
　　　　　　Adr2 = rg.MergeArea.Address
　　　　　　If Adr1 <> Adr2 Then
　　　　　　　　'結合範囲が違えばカウント
　　　　　　　　num = num + 1: Adr1 = Adr2
　　　　　　End If
　　　　End If
　　Next
　　MergeCellCount = num
End Function

※また、結合された「親番Ｇ」のフィルターが気になりました。当然といえば当然ですが。
添付図の右にあるのが「親番Ｇ」のフィルター結果です。普通と違うはずです。

やり方は（この質問の場合）、どこか邪魔にならないセルに、1、2、3、4、5、6（セル単位です）と入力します。

次に「1」をコピーして、「親番Ｇ」の結合セル「1」を選択して、右クリック＞形式を選択して貼り付け＞数式　とします。2～6も同様に行います。

これを行ったあと「親番Ｇ」でフィルターをかけてみてください。普通と違った景色になるはずです。（添付図の右）

回答No.4

MackyNo1

>関数式もう少し短くなりませんか？

今回のような集計は、基本的に配列数式とSUBTOTAL関数を併用したきわめて複雑な数式で対応する必要がありますので、数式を工夫して簡略化できる場合でも、提示した数式と比べて大きな改善は期待できないと思います。

回答No.2

MackyNo1

今のままのレイアウトでご希望の集計操作をしたいなら、以下のような手順でシートを修正してください。

A列のデータ範囲を選択してCtrl+Cでコピーし、空白列(たとえばF列）にCtrl+Vで貼り付けます。
その後、A列のデータ範囲を選択して、結合セルを解除し、そのままCtrl+Gでジャンプダイアログを出して「セル選択」から「空白セル」にチェックを入れ「OK」し、そのまま「=A2」（アクティブセルの1つ上のセル番地を指定）と入力しCtrl+Enterで確定します。
これですべての空白セルに上のデータと同じデータが入力されますので、最初に貼り付けたF列のデータ範囲を選択し、Ctrl+Cでコピーし、A列のセルで右クリック「形式を選択して貼り付け」で「書式」を選択すれば完成です。

回答No.1

aokii

セルの結合を解除して空白には同じ親番を入れて種類をカウントしてみて下さい。