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

質問

Excel2000のオートフィルタ機能を利用して、１シート３～４万件あるデータの集計などをしています。
その中で以下の点についてお教えください。

１．フィルタリングしてセルが飛び飛びに表示された状態で、ある列に1から順に連番を振りたいのです。
たとえばフィルタリングして
A1
A3
A7
A8
A13
：
のように絞り込まれたセルに、上から順に01,02,03,04,05...というように番号を振りたいのですが、オートフィル機能では間のセルまで入ってしまうし、=A1+1 のような関数でもやはり間のセルが邪魔です。
フィルタをかけた状態で連番を振ることはできますでしょうか。（書式は数値でも文字列でも構いません）

２．フィルタリングした状態で、連続した複数のセルをコピー＆「形式を選択して貼り付け」をしたら、間に隠れているセルに値が入ってしまいます。
フィルタをかけて表示されているセルにのみ貼り付ける方法はありますでしょうか。

以上、よろしくお願いいたします。

回答No.4

Wendy02 · ベストアンサー

こんばんは。
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

お礼

ありがとうございます。
VBAは「見たことがある」程度でやったことはないので、これから教えてくださった内容を元に挑戦します。
取り急ぎ御礼まで…

回答No.7

Wendy02

こんにちは。

＞Alt＋Cを押しても何事も起こらないんです。

メニューからマクロ－マクロ－「SetMyShortCut」(Visual Basic エディタ画面だったら、カーソルを、そのコードの中に置いて、F5を押せば、)設定されます。このマクロで、ショートカットはブックに登録されます。

後は、ショートカットで、マクロが起動します。

使い方:
最初に、コピーされる範囲を選択し、Ctrl　+　C で、範囲をコピーします。そうすると、クリップボードにデータが入ります。

そして、貼り付けされるところの一番上の場所に、セルポインターを置いてから、貼り付けに、登録したショートカット「Alt + C」 を使います。

あと、もう少しですから、投げ出さないで、がんばってくださいね。

なお、ショートカットを外すほうは、

SetOffMyShortCut

です。

Alt + C を Alt + V に変えたい場合は、
　Application.OnKey "%v", "SkipCopy"
　Application.OnKey "%V", "SkipCopy"

と中のコードを書き換えてあげます。

お礼

ありがとうございます！できました！！
何度も何度もお世話をおかけいたしました。
たいへん助かりました<(_ _)>

回答No.6

Wendy02

こんにちは。

＞CB As DataObject
＞のところで
＞「コンパイルエラー：
＞　ユーザ定義型は定義されていません」
＞というエラーが出ます…

#4 に書かれているように、

#　'要　参照設定 Microsoft Forms x.x Object Library

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

参照設定をしないと、そのようなエラーが出ます。データオブジェクトという入れ物(ユーザーフォームの中のツール)が必要なのですね。

お礼

ありがとうございます。すみません、エラーは私の設定ミスでした…（チェックを入れたつもりで別のに入れてました…）申し訳ありません。
で、エラーは出なくなったんですが、コピー＆ペーストができません…
Alt＋Cを押しても何事も起こらないんです。
値のみ貼り付けもできないし…
貼り付けるのは文字列にして１列でやってます。
何故なんでしょう…
何度も申し訳ございません<(_ _;)>

補足

一応念のため、使用しているPCは富士通のノート型　FMV-830NU/Lで、OSはWindowsXPSP2です。

回答No.5

Wendy02

#4 の　補足です。
2.

かならず、1列でのみの貼り付けにしてください。

お礼

ありがとうございます。
やってみます。

補足

すみません、やってみたのですが
CB As DataObject
のところで
「コンパイルエラー：
　ユーザ定義型は定義されていません」
というエラーが出ます…
色々調べてはみたのですが、よく分からなくて…アドバイスをお願いしますm(_ _;)m
教えてちゃんですみません。

回答No.3

shiotan99

こんにちは～

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はわかりません・・。

お礼

ありがとうございます。
参考URLも拝見して、よく理解できました。
助かりました。

回答No.2

mshr1962

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

お礼

ありがとうございます。１番はできました！
あとはこの関数を２番のように「値だけ貼り付け」したいんですが…やはりフィルタを解除しなければ無理でしょうか…

回答No.1

bazz11

こんにちは。

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

お礼

ありがとうございます。
すみません、説明不足でした。
２番は例えば、そのシートでフィルタリングした状態で、別のシートから複数セルを貼り付けたり、同じくフィルタリングした状態で関数を埋め込んだのを、その状態のまま（フィルタを解除せずに）値だけ貼り付けたりしたいのです。
できますでしょうか。