
添付図参照(Excel 2019)
添付した左中央図に示した4列13行のデータベースを考えます。
最上行で[オートフィルタ]を設定しておき、D列のデータβだけを抽出したときの結果は右上図になります。つまり、A列の連番は通常は飛び飛びの番号になりますが、右下図のように改めて連番として自動採番される方法を教えてください。
[オートフィルタ]を解除したときは元の連番 1、2、3、…、12 に戻らねばなりません。
Excel 2019 またはそれより古いバージョンの関数でも実現できるものを希望します。
古兵ですが、私はマクロが使えません(*^_^*)
![「[オートフィルタ]で抽出されたデータに自」の質問画像](http://oshiete.xgoo.jp/_/bucket/oshietegoo/images/media/b/298588_66124aada4346/M.jpg)
No.7
- 回答日時:
No.3の回答者です。
SUBTOTAL関数でフィルターを掛けたときのD列に余計な行が
表示される件ですが、ある条件が必要なようです。
α,β,γだけがフィルターの一覧にある場合では、補足されている
13行目のγが表示されるようです。
α,β,γと[空白セル]となっている場合は正しくフィルターされて
連番とカウント数が一致します。
私が試したときには、B列最終行に表示されているのとは別に、
削除したデータが残った状態でD列のみを対象にしたときだけ
正しくなるようです。
B列にSUBTOTAL関数の対象外の1行分データがあるときのみ
フィルタ―される対象列に[空白セル]が表示されます。
質問画像の場合なら、B14セルに ="" のようなものを入れて
空白セルとして、SUBTOTAL関数の連番対象にしないセルを
用意して、[空白セル]をフィルターに表示させます。
このときのみ、連番が正しく表示してくれます。
以下のように連番の対象行の範囲を空白セルまで含めれば、
フィルタ―されるときの不具合が発生しません。
=IF(B2="","",SUBTOTAL(103,$B$2:B2))
A列に対してのみ、14行目以降に数式を入れておくことで、
フィルタ―されたときの不具合が解消されます。
(フィルターに[空白セル]が表示されるのが問題点だが)
No.2の回答者さんの方法がベストなのかも。
》 α,β,γと[空白セル]となっている場合は正しくフィルターされて
》 連番とカウント数が一致します。
ご指摘のとおりであることを確認できました。
ありがとうございました。
No.6
- 回答日時:
No2です。
>どうしてそう仰ったのですか?驚いています。
回答者には質問文に記載されている事しかわかりません。
ご例示のデータがどのような性質のものなのか不明なので、仮にB列やC列をカウントする方法をとった場合、もしも、その中に空白セルが存在するとうまくカウントできなくなってしまいます。
(こちらの方が、式は「+1」しなくても済むので、単純にはなりますが・・)
質問者様にはデータの性質がわかっているはずですので、「空白セルは存在しない」という保証があるのなら、B列、C列などのカウントでも問題はないと思いますけれど。
(B列、C列に空白セルがなくても、Subtotal関数が使われている場合は、下に述べる理由で、Subtotalではうまくカウントできなくなります。)
質問文にはこのあたりに関する説明が一切ありませんので、回答者にとって、一番確実なのはA列をカウントする方法ということになります。
(A列には、回答した数式が入力されるはずなので、結果もわかりますので。)
一方で、Subtotal関数でA列をカウントしようとした場合、Subtotal関数は、Subtotal関数の結果を無視しますので、残念ながらA列のカウントには利用できないということになります。
・・・などなどの考察から、No2の回答を行っています。
No.4
- 回答日時:
No.3 のお礼への返答になります。
('ω') SUBTOTAL関数の 103番は、表示されているデータの数を数えるファンクションになります。
βだけでなくγも表示されているなら、γも連番の対象になりますよ。
銀鱗さん、コメント多謝です。
》 βだけでなくγも表示されているなら、γも連番の対象になりますよ。
それは理解できるのですが、オートフィルタでβだけを抽出したはずなのに、「γも表示されている」のが奇妙奇天烈なのです。
A列を埋め込んだのがSUBTOTAL関数でなく、AGGREGATE関数の場合はγは表示されないのですが…
No.3
- 回答日時:
No.1の回答者です。
A列への連番なのにCount対象にしたら循環参照でしたね。
=SUBTOTAL(103,$B$2:B2)
の間違いでした。
》 A列への連番なのにCount対象にしたら循環参照でしたね。
その件は私も気付いておりました。(^_^)
だけど、別の奇怪な現象に悩まされています。(;˃_˂;)
添付図参照(Excel 2019)⇒最初の質問文の下部にあろうかと。
A列にSUBTOTAL関数を埋め込んで、D列でβだけを抽出したつもりが、抽出された12行目に釣られたようにお隣りの最終行の13行目(のβならぬγまで)も抽出されるのです。
貴方の方ではその現象出ませんか?
No.2
- 回答日時:
こんにちは
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
No.1
- 回答日時:
SUBTOTAL関数で103を指定すれば可能です。
=SUBTOTAL(103,$A$2:A2)
この数式をA列に入れておけば、フィルター前後どちらでも
連番になってくれます。
https://kokodane.com/2013_waza_075.htm
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) エクセルでフィルタ後、考えている場所に値コピーができない。 1 2022/05/02 21:01
- Visual Basic(VBA) 【補足欄が足りなかったため、こちらで再質問させていただきます。】 Excelの転記マクロについて、教 4 2023/09/27 08:51
- Excel(エクセル) Excel(エクセル)でフィルター抽出後、非表示の行を計算しないで、合計を算出する方法 【内容】 添 4 2023/01/30 17:17
- Excel(エクセル) Excel 2019 のピボットテーブルの更新 3 2024/02/10 21:58
- Excel(エクセル) Excelで日付のグラフへの表示 2 2022/04/15 11:04
- Excel(エクセル) Excelのセル上の日付の不具合 3 2022/05/22 18:20
- Excel(エクセル) この記号、手短(テミジカ)に日本語/英語で何と称されますか? 1 2022/07/25 16:12
- Excel(エクセル) Excelの数式についての質問 1 2022/10/31 15:50
- Excel(エクセル) Excel>マクロ>特定のセルで同じ情報が登録されている行を1行にまとめたい(文字連結) 6 2023/01/05 16:30
- Access(アクセス) Accessのクエリの結果を、既存のエクセルに追加したい 2 2022/07/31 22:44
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルのVBAで集計をしたい
-
【関数】同じ関数なのに、エラ...
-
【マクロ】【配列】3つのシー...
-
vba テキストボックスとリフト...
-
【画像あり】オートフィルター...
-
Office2021のエクセルで米国株...
-
【マクロ】元データと同じお客...
-
【マクロ】実行時エラー '424':...
-
【マクロ】数式を入力したい。...
-
【マクロ】【相談】Excelブック...
-
【マクロ】列を折りたたみ非表...
-
他のシートの検索
-
【条件付き書式】シートの中で...
-
ページが変なふうに切れる
-
【マクロ】オートフィルターの...
-
特定のセルだけ結果がおかしい...
-
エクセル ドロップダウンリスト...
-
【マクロ】アクティブセルの時...
-
【マクロ】3行に上から下に並...
-
9月17日でサービス終了らし...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
9月17日でサービス終了らし...
-
エクセル
-
【マクロ】WEBシステムから保存...
-
エクセルの循環参照、?
-
エクセル ドロップダウンリスト...
-
エクセルのdatedif関数を使って...
-
特定のセルだけ結果がおかしい...
-
【マクロ】A列にある、日付(本...
-
【マクロ】EXCELで読込したCSV...
-
【マクロ】アクティブセルの時...
-
【エクセル】期限アラートについて
-
iPhoneのExcelアプリで、別のシ...
-
【関数】同じ関数なのに、エラ...
-
Excelの新しい空白のブックを開...
-
【マクロ】3行に上から下に並...
-
【マクロ】宣言は、何のために...
-
VBA チェックボックスをオーバ...
-
Excelについての質問です 並べ...
-
【マクロ】アクティブセルの2...
-
【関数】不規則な文章から●●-●●...
おすすめ情報
[No.3お礼]コメント用の添付図です。