Excel：オートフィルタ機能について質問です

Question

Excel2000のオートフィルタ機能を利用して、１シート３～４万件あるデータの集計などをしています。
その中で以下の点についてお教えください。

１．フィルタリングしてセルが飛び飛びに表示された状態で、ある列に1から順に連番を振りたいのです。
たとえばフィルタリングして
A1
A3
A7
A8
A13
：
のように絞り込まれたセルに、上から順に01,02,03,04,05...というように番号を振りたいのですが、オートフィル機能では間のセルまで入ってしまうし、=A1+1 のような関数でもやはり間のセルが邪魔です。
フィルタをかけた状態で連番を振ることはできますでしょうか。（書式は数値でも文字列でも構いません）

２．フィルタリングした状態で、連続した複数のセルをコピー＆「形式を選択して貼り付け」をしたら、間に隠れているセルに値が入ってしまいます。
フィルタをかけて表示されているセルにのみ貼り付ける方法はありますでしょうか。

以上、よろしくお願いいたします。

Wendy02 · Accepted Answer

こんばんは。

1. は式でも可能ですが、4万行にもなると重過ぎます。
2. は、VBA以外には不可能だと思います。

1.
最終列の検査行は、B列になっています。
Range("B65536")の部分を適宜替えてください。

'<シートモジュールか標準モジュールでお使いください。>
Sub SkipNumbering()
Dim i As Long, k As Long
Dim lastrow As Long
Const c As Long = 1　'列数を入れてください。　例:1 = A列

lastrow = Range("B65536").End(xlUp).Row　'B列の最終行を調べます
k = 1
Application.ScreenUpdating = False
For i = 1 To lastrow
If Cells(i, c).EntireRow.Hidden = False Then
　　Cells(i, c).Value = k
　　k = k + 1
End If
Next
　Cells(1, c).Resize(lastrow).NumberFormatLocal = "00"
　Application.ScreenUpdating = True
End Sub

2.
Visual Basic Editor の画面を開けたら、
ツール－参照設定で、Microsoft Forms x.x Object Library (x.x は数字が入ります）にチェックを入れてください。

挿入－標準モジュールをクリックしてから、以下のものを貼り付けてください。

一応、ショートカット設定をしましたので、このショートカットを実行すれば、Alt + C で、貼り付けできます。なお、貼り付けられるデータは、Text（文字、数字の定数）データに限ります。他の表示形式などは貼り付けられません。

'<標準モジュール>

Sub SkipCopy()
　'要　参照設定 Microsoft Forms x.x Object Library
　Dim myData As Variant
　Dim i As Long, j As Long, buf As String
　Dim CB As DataObject
　Set CB = New DataObject
　On Error Resume Next
　With CB
　.GetFromClipboard
　buf = .GetText
　End With
　On Error GoTo 0
　If Len(buf) = 0 Then MsgBox "クリップボードが空です。", 16: Exit Sub
　myData = Split(buf, vbCrLf)
　Do
　With ActiveCell
　　If .Offset(i).EntireRow.Hidden = False Then
　　.Offset(i).Value = myData(j)
　　j = j + 1
　　End If
　　i = i + 1
　End With
　Loop Until j > UBound(myData)
End Sub
'
Sub SetMyShortCut()
　'ショートカット設定
　'Alt + C
　Application.OnKey "%c", "SkipCopy"
　Application.OnKey "%C", "SkipCopy"
End Sub
'
Sub SetOffMyShortCut()
　'ショートカット解除
　Application.OnKey "%c"
　Application.OnKey "%C"
End Sub

bazz11 · Answer

こんにちは。

早速ですが、
フィルタで抽出した範囲を、そのまま別シートにコピーすれば、間のセルは抜かれたままでコピーできるはずですが。
これでどーでしょうか？

mshr1962 · Answer

A2=SUBTOTAL(3,$B$2:$B2)
上記をコピーで出来ませんか

shiotan99 · Answer

こんにちは～

1についてですが、No.2さんのように SUBTOTAL関数を使えばできますが、
No.2さんの式では不具合が発生すると思いますよ。
→最終行に条件に一致しないものが表示される。

これを回避するためには

=IF(B2="","",SUBTOTAL(3,$B$2:B2))

のようにしてください。

↓フィルタ実行時に連番を表示させたい－SUBTOTAL関数
http://www.relief.jp/itnote/archives/000439.php

↓オートフィルタで絞り込んだ件数を表示する
http://www.officetanaka.net/excel/function/tips/tips21.htm

2はわかりません・・。

Wendy02 · Answer

#4 の　補足です。
2.

かならず、1列でのみの貼り付けにしてください。

Wendy02 · Answer

こんにちは。

＞CB As DataObject
＞のところで
＞「コンパイルエラー：
＞　ユーザ定義型は定義されていません」
＞というエラーが出ます…

#4 に書かれているように、

#　'要　参照設定 Microsoft Forms x.x Object Library

#Visual Basic Editor の画面を開けたら、
#ツール－参照設定で、Microsoft Forms x.x Object Library (x.x は数字が入ります）にチェックを入れてください。


参照設定をしないと、そのようなエラーが出ます。データオブジェクトという入れ物(ユーザーフォームの中のツール)が必要なのですね。

Wendy02 · Answer

こんにちは。

＞Alt＋Cを押しても何事も起こらないんです。

メニューからマクロ－マクロ－「SetMyShortCut」(Visual Basic エディタ画面だったら、カーソルを、そのコードの中に置いて、F5を押せば、)設定されます。このマクロで、ショートカットはブックに登録されます。

後は、ショートカットで、マクロが起動します。

使い方:
最初に、コピーされる範囲を選択し、Ctrl　+　C で、範囲をコピーします。そうすると、クリップボードにデータが入ります。

そして、貼り付けされるところの一番上の場所に、セルポインターを置いてから、貼り付けに、登録したショートカット「Alt + C」 を使います。

あと、もう少しですから、投げ出さないで、がんばってくださいね。

なお、ショートカットを外すほうは、

SetOffMyShortCut

です。

Alt + C を Alt + V に変えたい場合は、 
　Application.OnKey "%v", "SkipCopy"
　Application.OnKey "%V", "SkipCopy"

と中のコードを書き換えてあげます。

Excel：オートフィルタ機能について質問です

こんにちは。

A2=SUBTOTAL(3,$B$2:$B2)

こんにちは～

こんばんは。

#4 の 補足です。

この回答への補足

こんにちは。

この回答への補足

こんにちは。

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング

#4 の　補足です。