添付図参照(Excel 2019)
添付した左中央図に示した4列13行のデータベースを考えます。
最上行で[オートフィルタ]を設定しておき、D列のデータβだけを抽出したときの結果は右上図になります。つまり、A列の連番は通常は飛び飛びの番号になりますが、右下図のように改めて連番として自動採番される方法を教えてください。
[オートフィルタ]を解除したときは元の連番 1、2、3、…、12 に戻らねばなりません。
Excel 2019 またはそれより古いバージョンの関数でも実現できるものを希望します。
古兵ですが、私はマクロが使えません(*^_^*)
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も見ています
-
「どうして捨てられないの?」前妻の物を捨てられない男性の心理って?
前妻の物を捨てられない理由に加え、捨てるための手段はあるのかを専門家に聞いてみた!
-
[オートフィルタ]の適用範囲の確認は可能か?
Excel(エクセル)
-
条件付き書式を教えてください
Excel(エクセル)
-
自動入力で今月はMONTH(TODAY())で良いのですが、翌月はどのようにすれば良いでしょうか。
Excel(エクセル)
-
-
4
Excel初心者です。 詳しい方、効率の良いやり方を教えてください。 職場で、抽出したデータの表を見
Excel(エクセル)
-
5
エクセル関数に詳しい方教えてください
Excel(エクセル)
-
6
エクセルでセルに「氏名を入力してください」に氏名を打ち込める方法について
Excel(エクセル)
-
7
INDEX関数やMATCH関数で値を取得した参照先シートのセルの行番号を1つ増やしたい
Excel(エクセル)
-
8
スマートな関数を教えて下さい。
Excel(エクセル)
-
9
各ページの1番上の表示について
Excel(エクセル)
-
10
エクセル②
Excel(エクセル)
-
11
Excelについて教えてください
Excel(エクセル)
-
12
エクセルの「入力規則」のプルダウンメニュの▼をセルの左側に出したい
Excel(エクセル)
-
13
エクセルをエクセレントに究める
Excel(エクセル)
-
14
Excel関数-文字列で自動作成された関数を実行する方法
Excel(エクセル)
-
15
エクセルの表示について
Excel(エクセル)
-
16
UNIQUE関数が使えないバージョンで重複削除したい。
Excel(エクセル)
-
17
Excel 2019 は、SPILL機能がありやなしや?
Excel(エクセル)
-
18
[関数について]わかる方教えてください
Excel(エクセル)
-
19
Excelで「時間の足し算」はどうすればいいですか?
Excel(エクセル)
-
20
Excelにてある膨大なデータを管理しています。 そこで、特定の市町村にのみ色を付けたいです。 Ex
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
-1234 を (1234) と表記する由...
-
エクセルで現場ごとに機材の移...
-
他部署からもらう データで、 ...
-
Excel 1の位の数字で処理を分岐...
-
Excelのセルの色を変えた行(す...
-
エクセルの数式について教えて...
-
Excelでの記号と特殊文字につい...
-
とびとびの大量セルを選択した...
-
MACにおけるエクセルの入力トラ...
-
ある表にフィルターをかけて出...
-
Lookup関数
-
エクセルの数式バーのフォント...
-
再質問です。マクロの修正箇所...
-
エクセルで「ページレイアウト...
-
EXCELの散布図で日付が1900年に...
-
【マクロ】VLOOKUPにて参照元に...
-
exselの質問です
-
エクセルでファイルの最終更新...
-
Excel 大小比較演算子による「...
-
エクセルの問題です。絶対値の...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
半角カタカナをヘボン式ローマ...
-
(マクロ)vlookupの元データを同...
-
エクセルで上位バイトのセルと...
-
exselの質問です
-
Excel 大小比較演算子による「...
-
Excel VBについての質問です。
-
エクセルの問題です。絶対値の...
-
非表示列の再表示に失敗
-
職場の人から聞かれており、こ...
-
Excel関数-文字列で自動作成さ...
-
Excelデータをコピペして、ペー...
-
ユーザー定義関数をアドイン登...
-
【マクロ】for next構文について
-
エクセルの日付を編集する
-
【マクロ】VLOOKUPにて参照元に...
-
exselで最小数で並び替える関数
-
libre 表計算ソフトの計算がう...
-
エクセルで表
-
エクセルの表で1年間の曜日を...
-
西暦和暦
おすすめ情報
[No.3お礼]コメント用の添付図です。