EXCELで行数が変わる場合のカウントについて

質問

「EXCEL2010」で、行数が変わる場合のカウント方法について
教えて下さい。
例の場合で説明すると、大分類「AAA」「BBB」毎に「中分類」の数と「有無」の
数をカウントしたいのですが、「AAA」と「BBB」の行数が増減してしまいます。
　　　　　　　　　　　　　　中分類　　　有無
　添付イメージでは「AAA」　　5　　　　　3
　　　　　　　　　「BBB」　　3　　　　　1

カウントする時の範囲を、可変にすることは可能でしょうか。
よろしくお願いします。

回答No.8

bunjii · ベストアンサー

回答No.6の追加です。
INDIRECT関数で大分類の範囲を可変にするときは次の数式で良いと思います。
F2セルへ次の数式を設定して右と下へオートフィルコピーします。
=COUNTIF(INDIRECT(ADDRESS(MATCH($E2,$A:$A,0),COLUMN(B2),4)&":"&ADDRESS(MATCH($E2&" ",OFFSET($A$1,0,1,MAX(INDEX(($B:$B<>"")*ROW(B:B),0))),1),COLUMN(B2),4)),"> ")

回答No.9

bunjii

回答No.6の追加です。
VBAでは次のコードで同等の処理ができました。
Sub Counter()
Row = 2
maxrow = Cells(Rows.Count, 2).End(xlUp).Row
For i = 2 To maxrow
If Cells(i, 1) <> Cells(Row, 5) And Cells(i, 1) <> "" Then Row = Row + 1
If Cells(i, 2) <> "" Then Cells(Row, 6) = Cells(Row, 6) + 1
If Cells(i, 3) <> "" Then Cells(Row, 7) = Cells(Row, 7) + 1
Next i
End Sub

こちらの方が考え易いですよね。

お礼

取りあえず、No.8のやり方を解読してやってみたいと思います。
VBAは不慣れなので、まだちょっと難しいかもしれませんが、おっしゃる様に分かりやすいかもしれませんね。

いろいろと、有難うございました。

回答No.7

bunjii

回答No.6の数式に誤りはありました。
=COUNTIF(OFFSET($A$1,MATCH($E2,$A:$A,0)-1,COLUMNS($E2:F2)-1,MATCH($E2&" ",OFFSET($A$1,1,1,14),1)-MATCH($E2,$A:$A,0)+2),">"&" ")
　　　　　↓
=COUNTIF(OFFSET($A$1,MATCH($E2,$A:$A,0)-1,COLUMNS($E2:F2)-1,MATCH($E2&" ",OFFSET($A$1,1,1,MAX(INDEX(($B:$B<>"")*ROW($B:$B),0))),1)-MATCH($E2,$A:$A,0)+2),">"&" ")

OFFSET関数の高さを常数にしている部分があり、全体の行数が増えたとき対応できなくなります。
OFFSET($A$1,1,1,14)の14は誤りなので、MAX(INDEX(($B:$B<>"")*ROW($B:$B),0))と差し替えます。
OFFSET($A$1,1,1,14)
　　　↓
OFFSET($A$1,1,1,MAX(INDEX(($B:$B<>"")*ROW($B:$B),0)))

回答No.6

bunjii

>カウントする時の範囲を、可変にすることは可能でしょうか。
できます。
方法はB列とC列の検索範囲を可変にする方法を見つけ出すことにあります。
関数式ではOFFSET関数またはINDIRET関数で開始行と終了行を指定すればよいことです。しかし、目的の行番号を可変で探すのは面倒な論理を組み立てないとできません。
次のような数式をあなたは解読できるでしょうか？
=COUNTIF(OFFSET($A$1,MATCH($E2,$A:$A,0)-1,COLUMNS($E2:F2)-1,MATCH($E2&" ",OFFSET($A$1,1,1,14),1)-MATCH($E2,$A:$A,0)+2),">"&" ")
貼付画像は上記の数式をF2に設定し、右へG2まで、そのまま下へ4行目までオートフィルコピーしたものです。
VBAで処理する方が考え易いかも知れません。

回答No.5

Chiquilin

困るくらいなら 素直に大分類を埋めたらいいんじゃないですか？

空白セルを選択して 上のセルを参照させるだけなら一瞬で終わり
ますよね。

回答No.4

noname#232800

まず、あなたのパソコンの表計算で、最大行を調べてみましょう。
Ctrl　＋　↓

私のは　1,048,576でした。

次に（Ctrl　＋　↑でA1を表示）　A1　▼　Fx　　　と表示されてるA1にアドレスを入れてジャンプします。

A10000
ここに式を入れ、例えば　＝SUM(A1:A9999）

次に「表示」というツールバーの「分割」をクリックしてみる。1/2とは限らず、何回かやると1/8で表示を切ることが出来る。

下のウインドウに　A10000　を固定して、上のウインドウだけスクロールできる。

印刷は、手作業で必要なページ数を与える。

回答No.3

mt2015

ANo.1です。

> データを見ると、中分類が無い行がいくつかありました。
> 中分類が無いと、その行以降の大分類Ｗがブランクになってしまいます。
> こうなると、ちょっと難しいですかね。

じゃあ、B2以下に入れる式を↓にします。
=IF(A2<>"",A2,B1)

で、中分類の数もCountifsでカウントします。
ANo.1の添付の図の例だとG2セルに↓です。
=COUNTIFS(B:B,F2,C:C,"<>")

回答No.2

aokii

大分類「AAA」「BBB」毎に「中分類」の数をカウントするには、
AAAの下の行に空白が有る場合は、AAAが自動的に記入されるようにして、
BBBの下の行に空白が有る場合は、BBBが自動的に記入されるようにして、
ピボットテーブルでAAAとBBBの数を集計してはいかがでしょう。
大分類「AAA」「BBB」毎に「有無」の数をカウントする場合も、〇の数をピボットテーブルでAAAとBBB毎に集計できます。
なお、AAAの下の行に空白が有る場合は、AAAが自動的に記入されるようにして、BBBの下の行に空白が有る場合は、BBBが自動的に記入されるようにするには、D2セルに以下の式を入れて下にドラッグコピーしてみてください。
=IF(A2="",D1,A2)

回答No.1

mt2015

人間の見やすさを優先すると集計に不向きな表になります。
添付の図のように作業列を作って各行に大分類のデータを付与しましょう。
図の例ですと、B2セルに↓の式を入れて下にコピーしています。
=IF(C2="","",IF(A2<>"",A2,B1))

これなら、Countifで中分類の数、Countifsで有無の数がカウントできます。
作業列が邪魔なら、もっと右の見えない列に作るか、非表示にしましょう。

補足

たびたびすみません。
データを見ると、中分類が無い行がいくつかありました。
中分類が無いと、その行以降の大分類Ｗがブランクになってしまいます。
こうなると、ちょっと難しいですかね。
もし、解決策がありましたらご教授下さい。
よろしくお願いします。