Excel関数 条件達成までのセルの数

質問

Ａ列が１～１０まであり、Ａ１～Ａ４とＡ６・Ａ７・Ａ９・Ａ10が空白、
Ａ列をランダムに動く文字「◎」がＡ５とＡ８にある場合、
そのＡ１とＡ５との空白の数(この場合４)をカウントしたいのです。
また、できれば別にＡ５～Ａ８間の空白の数(この場合２)もカウントできるでしょうか？

よろしくお願いします。

回答No.6

tom04 · ベストアンサー

No.3です！
たびたびごめんなさい。

前回の配列数式は10行まで対応の式でなおかつ1行目からデータが入っている場合ですので
たぶん、普通のデータは1行目が見出し（タイトル）行だと思います。
その関係でのエラーではないかと思いますので
もう一度、画像をアップし数式も書いておきます。
（1000行目まで対応できる数式です）

本来であればNo.2さんのように作業用の列を使った方が
コンピュータの負担は少なくて済みます。

あまりにデータ量が多い場合は配列数式はオススメではありません。

それを踏まえた上での回答になります。
↓の画像で
C2セルは
=SMALL(IF($A$2:$A$1000<>"",ROW($1:$999)),1)-1
（配列数式です）

C3セルは（これも配列数式です）
=IF(COUNTA($A$2:$A$1000)<ROW(A2),"",SMALL(IF($A$2:$A$1000<>"",ROW($1:$999)),ROW(A3)-ROW($A$1))-(SMALL(IF($A$2:$A$1000<>"",ROW($1:$999)),ROW(A2)-ROW($A$1)))-1)

としてC3セルを下へコピーすると画像のような感じになります。

（C2・C3セルともShift＋Ctrl＋Enterキーで確定してください）

これでなんとなく理解していただけるのではないかと思いますが、
配列数式の中の行数とIFの中の行数が同じ数にならないとエラーになります。

以上、長々と書きましたが
参考になれば幸いです。m(__)m

回答No.5

MackyNo1

>範囲を広げてＡ100やＡ1000まででやってみると、
自分なりのアレンジではできませんでしたが、
できる方法はあるのでしょうか？

回答した数式がが少し検証不足だったようですね。
お分かりと思いますが、相対参照している部分は絶対参照する必要があります。

たとえば、A100セルまで対応する数式なら、２番目以降の空白セルの数は以下の式になります（下方向にオートフィル）。

=LARGE(INDEX(($A$1:$A$100="◎")*ROW($A$1:$A$100),),COUNTA($A$1:$A$100)-ROW(A1))-LARGE(INDEX(($A$1:$A$100="◎")*ROW($A$1:$A$100),),COUNTA($A$1:$A$100)-ROW(A1)+1)-1

回答No.4

MackyNo1

>ちなみに、◎の数がランダムという条件でもできる方法はあるのでしょうか？

一般的な数式にする場合は、以下のように変更します。

最初の◎までの空白

=LARGE(INDEX((A1:A10="◎")*ROW(A1:A10),),COUNTA(A1:A10))-1

2番目から3番目以降の空白数(下方向にオートフィル）

=LARGE(INDEX((A1:A10="◎")*ROW(A1:A10),),COUNTA(A1:A10)-ROW(A1))-LARGE(INDEX((A1:A10="◎")*ROW(A1:A10),),COUNTA(A1:A10)-ROW(A1)+1)-1

数式が複雑になるのでエラー処理をしていませんが、必要な場合は、COUNTA関数でデータ数以上の場合は空白になるようなIF関数の数式を入力してください。

お礼

度々ありがとうございます。
Ａ列10桁目までのカウントはできました！
範囲を広げてＡ100やＡ1000まででやってみると、
自分なりのアレンジではできませんでしたが、
できる方法はあるのでしょうか？

回答No.3

tom04

こんばんは！
すでにNo.1さんの回答で解決済みかと思いましたが・・・

お礼欄に
>ちなみに、◎の数がランダムという条件でも
できる方法はあるのでしょうか？

とありましたので、
余計なお世話かもしれませんが、顔を出してしまいました。

↓の画像で説明させていただきます。

数式は配列数式になってしまいますので
この画面から単にコピー＆ペーストしただけではエラーになると思います。
セルに貼り付け後、F2キーを押すか、数式バー内で一度クリックします。
編集可能になりますので
Shift＋Ctrl＋Enterキーで確定します。
数式の前後に｛　｝マークが入り配列数式になります。

まず、C1セルに
=SMALL(IF($A$1:$A$10<>"",ROW($1:$10)),1)-1

C2セルに
=IF(COUNTA(A:A)<ROW(A2),"",SMALL(IF($A$1:$A$10<>"",ROW($1:$10)),ROW(A3)-ROW($A$1))-SMALL(IF($A$1:$A$10<>"",ROW($1:$10)),ROW(A2)-ROW($A$1))-1)
（先ほど書いたようにC1・C2セルとも配列数式です！）

という数式を入れC2セルをオートフィルで下へコピーすると
画像のような感じになります。

数式は10行まで対応ですが、もっと行数を増やしたければ
数式の範囲指定の領域をアレンジすれば可能です。

以上、長々と書きましたが
参考になれば幸いです。m(__)m

お礼

ご回答ありがとうございます。
私が「配列数式」って言うのをよく理解してないので、
自分なりに編集してもエラーが出てしましました。
もう少し関数を勉強して、このやり方に挑戦してみます。

回答No.2

某HN クロメート(Chromate)（@CoalTar）

A1セルの処理が面倒でした
B1セル =(A1="◎")*9+1
B2セル =IF(A2="◎",COUNTIF($A$1:A2,"◎")*10,SUM(B1,1))
下へオートフィル

D2セル =IF(COUNTIF(A:A,"◎")>=ROW()-1,ROW()-1,"")
下へオートフィル

E2セル =IF(D2="","",IF(A1="◎",0,INDEX(B:B,MATCH(D2*10,B:B)-1)-(D2-1)*10))
E3セル =IF(D3="","",INDEX(B:B,MATCH(D3*10,B:B)-1)-(D3-1)*10)
下へオートフィル

もっと効率的なやり方があるかもしれませんが、
たたき台で参考まで

お礼

ご回答ありがとうございます。
このやり方だとＡ列を100や1000までにしても、
自分なりにアレンジしてできました！

回答No.1

MackyNo1

Ａ１：Ａ１０セルに◎を2つしか入力しないという条件なら、以下のような関数で表示できます。

Ａ１から最初の◎までの空白数

=LARGE(INDEX((A1:A10="◎")*ROW(A1:A10),),2)-1

最初の◎から次の◎までの空白数

=MAX(INDEX((A1:A10="◎")*ROW(A1:A10),))-LARGE(INDEX((A1:A10="◎")*ROW(A1:A10),),2)-1

お礼

ご回答ありがとうございます。
ちなみに、◎の数がランダムという条件でも
できる方法はあるのでしょうか？