No.6ベストアンサー
- 回答日時:
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
- 回答日時:
>範囲を広げてA100やA1000まででやってみると、
自分なりのアレンジではできませんでしたが、
できる方法はあるのでしょうか?
回答した数式がが少し検証不足だったようですね。
お分かりと思いますが、相対参照している部分は絶対参照する必要があります。
たとえば、A100セルまで対応する数式なら、2番目以降の空白セルの数は以下の式になります(下方向にオートフィル)。
=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
- 回答日時:
>ちなみに、◎の数がランダムという条件でもできる方法はあるのでしょうか?
一般的な数式にする場合は、以下のように変更します。
最初の◎までの空白
=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関数の数式を入力してください。
度々ありがとうございます。
A列10桁目までのカウントはできました!
範囲を広げてA100やA1000まででやってみると、
自分なりのアレンジではできませんでしたが、
できる方法はあるのでしょうか?
No.3
- 回答日時:
こんばんは!
すでに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
- 回答日時:
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)
下へオートフィル
もっと効率的なやり方があるかもしれませんが、
たたき台で参考まで
No.1
- 回答日時:
A1:A10セルに◎を2つしか入力しないという条件なら、以下のような関数で表示できます。
A1から最初の◎までの空白数
=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
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) エクセル関数についてお教えください 3 2023/07/24 12:33
- Excel(エクセル) Excelについて質問です(ver2019) 1 2023/06/30 21:20
- Excel(エクセル) Vba エクセルマクロで、 A列の、A1セルからA10セルに空白のある行を削除する、のは Range 3 2022/11/05 17:44
- Excel(エクセル) Excelの空文字判定について 7 2023/01/06 13:25
- Excel(エクセル) EXCEL関数(数式)を教えてください 11 2023/05/09 13:19
- Visual Basic(VBA) エクセルVBAについて 2 2023/01/31 16:21
- Excel(エクセル) xlDownの使い分けについての質問です vbaでxlDownを使って一覧近い空白までのセルをコピー 3 2022/08/04 12:20
- Excel(エクセル) capeofdragonと申します Excel2016を使っておりまして 半角又は全角の任意文字列が 2 2022/10/31 13:51
- Excel(エクセル) こんにちは。Excelのことで教えてください。vLOOK関数の埋め込まれた列があり、その列の中で引用 3 2022/07/30 16:36
- その他(データベース) Accessのクエリで1フィールドの抽出条件設定をNullでなく全角半角含む空白のみの文字列でない文 1 2023/04/24 15:20
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
英数字のみ全角から半角に変換
-
【Microsoft Forms】回答を削除...
-
会社PCのメールが更新されない
-
マイクロソフト 一時使用コード...
-
Outlook で宛先が複数の場合の人数
-
【スプレッドシート】指定の日...
-
Microsoft Formsの「個人情報や...
-
Microsoft Edgeの「ニュースと...
-
VLOOKUP関数について
-
【Excel VBA】PDFを作成して,...
-
エクセルでXLOOKUP関数...
-
outlookのメールが固まってしま...
-
teams設定教えて下さい。 ①ビデ...
-
Microsoft365で写真をアルバム...
-
Microsoft Officeに似たキング...
-
会社におけるOfficeライセンス...
-
【スプレッドシート】白色のセ...
-
Excel VBA 日程表からスケジュ...
-
VBAファイルの保存先について
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
マイクロソフト 一時使用コード...
-
英数字のみ全角から半角に変換
-
Office2021を別のPCにインスト...
-
Microsoft Formsの「個人情報や...
-
officeビジネス型のワードやエ...
-
会社PCのメールが更新されない
-
【スプレッドシート】指定の日...
-
Microsoft Officeを2台目のPCに...
-
何このステータスバー
-
2つのシートの一致する行のセ...
-
会社のTeamsのことで相談です。...
-
エクセルにリンクされるのをし...
-
Windows 11で、IME言語バー(IM...
-
office2010とoffice365の共存で...
-
Microsoftのパソコンです。 エ...
-
エクセルでXLOOKUP関数...
-
Excel関数について質問ですm(__)m
-
VBA
-
自分の専門分野の仕事。初見で...
おすすめ情報