[オートフィルタ]で抽出されたデータに自動的に連番を付けるには？

添付図参照(Excel 2019)

添付した左中央図に示した４列13行のデータベースを考えます。
最上行で[オートフィルタ]を設定しておき、D列のデータβだけを抽出したときの結果は右上図になります。つまり、A列の連番は通常は飛び飛びの番号になりますが、右下図のように改めて連番として自動採番される方法を教えてください。
[オートフィルタ]を解除したときは元の連番 1、2、3、…、12 に戻らねばなりません。

Excel 2019 またはそれより古いバージョンの関数でも実現できるものを希望します。
古兵ですが、私はマクロが使えません(*^_^*)

質問者からの補足コメント

• [No.3お礼]コメント用の添付図です。

  
    補足日時：2024/04/07 21:13

No.1 回答者： enunokokoro 回答日時： 2024/04/07 17:25

SUBTOTAL関数で103を指定すれば可能です。

　=SUBTOTAL(103,$A$2:A2)
この数式をA列に入れておけば、フィルター前後どちらでも
連番になってくれます。
https://kokodane.com/2013_waza_075.htm

No.2 回答者： fujillin 回答日時： 2024/04/07 17:30

こんにちは

SUBTOTAL関数でできると思ったのですが、うまくいかないようですね。

ご提示のレイアウトで、全部を表示した状態で、A2セルに
　=AGGREGATE(2,5,A$1:A1)+1
を入力して、A2:A13までフィルコピーしておけば、ご希望のようになると思います。

この回答へのお礼

》 SUBTOTAL関数でできると思ったのですが、うまくいかないようですね。
えｯ？どうしてそう仰ったのですか？驚いています。

実は、[No.1]の enunokokoroさんから提案された式を一部変更した
=SUBTOTAL(102,C$1:C2) を入力したセル A2 を下方にオートフィルしたことで上手く行ってた(と思ってた)のですが、今はどうしても「うまくいかない」のです。私には原因不明です。今は、fujillinさんのご提案を改変した式 =AGGREGATE(2,5,C$1:C2) をセル A2 に入力して「うまくい」っています。
ありがとうございました。m(_._)m

お礼日時：2024/04/07 20:49

No.3 回答者： enunokokoro 回答日時： 2024/04/07 17:55

No.1の回答者です。

A列への連番なのにCount対象にしたら循環参照でしたね。
　　=SUBTOTAL(103,$B$2:B2)
の間違いでした。

この回答へのお礼

》 A列への連番なのにCount対象にしたら循環参照でしたね。
その件は私も気付いておりました。(^_^)

だけど、別の奇怪な現象に悩まされています。(;˃_˂;)
添付図参照(Excel 2019)⇒最初の質問文の下部にあろうかと。
A列にSUBTOTAL関数を埋め込んで、D列でβだけを抽出したつもりが、抽出された12行目に釣られたようにお隣りの最終行の13行目(のβならぬγまで)も抽出されるのです。
貴方の方ではその現象出ませんか？

お礼日時：2024/04/07 21:10

No.4 回答者： 銀鱗 回答日時： 2024/04/07 21:29

No.3 のお礼への返答になります。

('ω') SUBTOTAL関数の 103番は、表示されているデータの数を数えるファンクションになります。

βだけでなくγも表示されているなら、γも連番の対象になりますよ。

この回答へのお礼

銀鱗さん、コメント多謝です。
》 βだけでなくγも表示されているなら、γも連番の対象になりますよ。
それは理解できるのですが、オートフィルタでβだけを抽出したはずなのに、「γも表示されている」のが奇妙奇天烈なのです。
A列を埋め込んだのがSUBTOTAL関数でなく、AGGREGATE関数の場合はγは表示されないのですが…

お礼日時：2024/04/07 21:41

No.5 ベストアンサー 回答者： 銀鱗 回答日時： 2024/04/07 21:47

βだけを選んでるわけではなくγも選ぶような設定になってるか、

最後の行がフィルタの対象外という話のオチを期待してみます。

この回答へのお礼

お礼日時：2024/04/08 06:07

No.6 回答者： fujillin 回答日時： 2024/04/07 22:01

No2です。

＞どうしてそう仰ったのですか？驚いています。
回答者には質問文に記載されている事しかわかりません。

ご例示のデータがどのような性質のものなのか不明なので、仮にB列やC列をカウントする方法をとった場合、もしも、その中に空白セルが存在するとうまくカウントできなくなってしまいます。
（こちらの方が、式は「+1」しなくても済むので、単純にはなりますが・・）

質問者様にはデータの性質がわかっているはずですので、「空白セルは存在しない」という保証があるのなら、B列、C列などのカウントでも問題はないと思いますけれど。
（B列、C列に空白セルがなくても、Subtotal関数が使われている場合は、下に述べる理由で、Subtotalではうまくカウントできなくなります。）


質問文にはこのあたりに関する説明が一切ありませんので、回答者にとって、一番確実なのはA列をカウントする方法ということになります。
（A列には、回答した数式が入力されるはずなので、結果もわかりますので。）
一方で、Subtotal関数でA列をカウントしようとした場合、Subtotal関数は、Subtotal関数の結果を無視しますので、残念ながらA列のカウントには利用できないということになります。

・・・などなどの考察から、No2の回答を行っています。

この回答へのお礼

お礼日時：2024/04/08 06:14

No.7 回答者： enunokokoro 回答日時： 2024/04/08 00:53

No.3の回答者です。

SUBTOTAL関数でフィルターを掛けたときのD列に余計な行が
表示される件ですが、ある条件が必要なようです。

α,β,γだけがフィルターの一覧にある場合では、補足されている
13行目のγが表示されるようです。
α,β,γと[空白セル]となっている場合は正しくフィルターされて
連番とカウント数が一致します。

私が試したときには、B列最終行に表示されているのとは別に、
削除したデータが残った状態でD列のみを対象にしたときだけ
正しくなるようです。
B列にSUBTOTAL関数の対象外の1行分データがあるときのみ
フィルタ―される対象列に[空白セル]が表示されます。
質問画像の場合なら、B14セルに ="" のようなものを入れて
空白セルとして、SUBTOTAL関数の連番対象にしないセルを
用意して、[空白セル]をフィルターに表示させます。
このときのみ、連番が正しく表示してくれます。

以下のように連番の対象行の範囲を空白セルまで含めれば、
フィルタ―されるときの不具合が発生しません。
　=IF(B2="","",SUBTOTAL(103,$B$2:B2))
A列に対してのみ、14行目以降に数式を入れておくことで、
フィルタ―されたときの不具合が解消されます。
（フィルターに[空白セル]が表示されるのが問題点だが）

No.2の回答者さんの方法がベストなのかも。

この回答へのお礼

》 α,β,γと[空白セル]となっている場合は正しくフィルターされて
》 連番とカウント数が一致します。
ご指摘のとおりであることを確認できました。
ありがとうございました。

お礼日時：2024/04/08 06:15

No.8 回答者： enunokokoro 回答日時： 2024/04/08 01:40

No.7での再度の修正です。

つい癖で[空白セル]と記載してしまいましたが、(空白セル)での
表記でしたね。回答内容を(空白セル)と読み取ってください。

この回答へのお礼

お礼日時：2024/04/08 06:12