グッドデザイン賞を受賞したウォーターサーバー >>

オートフィルタで抽出したデータに連番をふりたいと思っています。このサイトで調べた結果、オートフィルタをかけてのオートフィルでの連番入力はできないと知りました。
現在しようとしているのは
  A  B  C
1 クラス NO 氏名  
2 1    ○△
3 2    □×
4 1    ★♪
というデータがあり、項目にオートフィルタをかけている状態です。クラスを抽出した場合にそのクラスでの通し番号(連番)を付けたいのです。SUBTOTAL関数だと、連番が表示されるのはオートフィルタで抽出している間だけです。SUBTOTALでつけられた連番を、オートフィルタを解除しても残しておくにはどうしたらよいでしょうか。やはりコピー&ペーストしかないのでしょうか?似たような質問が多々ありますが、コピー&ペースト以外の方法をご存知の方、どうか教えて下さい。

このQ&Aに関連する最新のQ&A

A 回答 (3件)

#01です


>したがってオートフィルタで抽出を解除すれば同じ通し番号がクラス数分あることになります。
これは最初から書いていただきたかったですが(^^;、それならこのようなマクロでもできます。なお#02さんのご指摘のようにSpecialCellsを用いる方が処理が速くなりますが、手抜きして元のマクロを修正するにとどめます。

Sub Macro4()
Dim idx, cnt As Long
Application.ScreenUpdating = False
For idx = 2 To Range("A65536").End(xlUp).Row
  If Rows(idx).Hidden = False Then
    cnt = cnt + 1
    Cells(idx, "B").Value = cnt
  End If
Next idx
Application.ScreenUpdating = True
End Sub
    • good
    • 0
この回答へのお礼

説明がたりず分かりにくくてすみません。助かりました。
ありがとうございまあした。

お礼日時:2007/11/22 11:17

http://www.moug.net/skillup/nksw/nksw02-01.htm
.SpecialCells(xlCellTypeVisible)
フィルタ後に 表示されているセルだけ選択する時は
可視セル を選択することをすすめます
全ての行を処理すると時間がかかりますが
可視セルなら検索結果のセルだけ処理できます

フィルタ後
for each myR in range("A1:A500").SpecialCells(xlCellTypeVisible)
msgbox myR.value
next

最終行 = Range("A" & activesheet.rows.count).End(xlUp).Row
65536行以上のシートでも共通で使うことができます
    • good
    • 0
この回答へのお礼

ありがとうございます。とても役に立ちました!!

お礼日時:2007/11/22 11:16

表示されている行のB列に連番を振るマクロサンプルです。


質問文からではどのような使い方をするのかが分かりませんし、前に振った通し番号はどうすればよいのかもわかりません。ですから期待通りの動きはしないかもしれませんが、とりあえずヒントにはなるかもしれません

以下のマクロをALT+F11でVBE画面を開き、左上のVBA Projectでシート名を右クリックし「挿入」→「標準モジュール」で表示される画面に貼り付けて下さい。マクロの実行はワークシート画面に戻ってALT+F8でマクロ一覧を開き、マクロ名を選択して「実行」ボタンです。

Sub Macro4()
Dim idx, cnt As Long
For idx = 2 To Range("A65536").End(xlUp).Row
  If Rows(idx).Hidden Then
    Cells(idx, "B").ClearContents
  Else
    cnt = cnt + 1
    Cells(idx, "B").Value = cnt
  End If
Next idx
End Sub

この回答への補足

使い方としては、氏名にクラス番号を割り振った後、そのクラスごとにオートフィルタで抽出し、マクロで通し番号をつけるといった使い方です。なので、つけた通し番号はそのまま残したいと思います。したがってオートフィルタで抽出を解除すれば同じ通し番号がクラス数分あることになります。

補足日時:2007/11/20 16:36
    • good
    • 0

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

QExcelでフィルタをかけた状態でオートフィルタで連番を振りたい

Excelでオートフィルタを設定し、抽出したデータがあります。(ですので表示範囲があります)その抽出したデータのある列に連番を振りたいのですが、非表示ににゅうりょくされてしまうためうまくできません。
またオートフィルタで抽出した範囲の可視セルをコピーすることはできても、再度貼り付けることもうまくできません。
何か方法はないでしょうか。

Aベストアンサー

質問を絞って頂けると回答しやすいのですが。

とりあえず ひとつ目に関しては B列にデータがあって A列
に連番を振りたい場合 先頭行(例えば B2セル)に

=SUBTOTAL(2,B$2:B2)
下方向にオートフィルコピー

で問題ないかと思います。

Qオートフィルタのデータに連番をつける時。

オートフィルタのデータに連番をつける時。

オートフィルタで抽出したデータに連番をつける時は、
subtotal関数を使う方法しかないのでしょうか?
これが一番簡単な方法なのでしょうか?

エクセル2007には ‘アウトライン’という機能がありますが、
これを使えば連番をだすことはできるのでしょうか?
挑戦してみたのですが、私にはできませんでした。

エクセル2003・2007においてsuubtotal関数以外で
方法があれば教えてください。
どうぞ宜しくお願いします。

Aベストアンサー

>suubtotal関数以外
抽出された状態で
空白列で、抽出データ行の先頭セルに、
=MAX($A$1:A1)+1
このセルを下方にコピー

マクロの例
セル範囲は実状に合わせて書き換えてください。
抽出された状態で実行

Dim rng As Range
Dim c As Range
Dim i As Long

Set rng = Range("A2:A16").SpecialCells(xlCellTypeVisible)
For Each c In rng
i = i + 1
c.Value = i
Next

Qエクセル2010の技

エクセル2010です。フィルタをかけて抽出したセル(行)だけに、連番をふりたいのですが、うまくいきません。どなたかやり方をご教示ください。

Aベストアンサー

たとえばA列(A2セル以下)に連番を振りたいなら、A2セルに以下の数式を入力し下方向にオートフィルコピーしてください。

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

このような関数を設定しておけば、自動的にフィルタされたデータで連番を表示することができます。

Qオートフィルタで抽出した行にだけ連番を振りたい

500件程度の製品データに対して、製品1、製品2、製品3でフィルタ抽出後、製品1に該当する行にだけ、1~連番を振りたいのですが・・・普通に連番コピーしますと、隠れている製品2・3のセルを含めた状態で、連番が振られてしまいます。
これを回避する操作法はないのでしょうか?
例えば、ツールメニューや書式設定メニューから、「可視セルにのみ連番コピーする」などと、指定しておく事は出来ないのでしょうか?

Aベストアンサー

#2 の回答の補足に対して。
こんにちは。

関数不可というところで、これを付け足すのはやぶへびかもしれませんが、Excel2003 のヘルプより引用しておきます。

# SUBTOTAL 関数では、集計方法の値にかかわらず、フィルタの結果に含まれていない行はすべて無視されます。

これは、旧バージョンでも、同じ仕様です。

# 集計方法として 101 111 の定数を指定すると、[書式] メニューの [行] の [表示しない] コマンドで非表示にされている行は無視されます。リストで表示されている行だけを集計する場合は、これらの定数を使用します。

バージョンによって引数が変わることはありません。定数を、100番台で行うか、通常の1桁で行うか、こういう話は、問題外かもしれませんが、何か初歩的な思い込みによる問題であれば、これで解決するはずです。しかし、もしもExcelのバグのようなものが存在するなら、#3で解決するはずです。マクロの解決もやぶさかではないのですが、それは、逆に物事が複雑になるだけだと思います。

QExel VBA 別ブックから該当データを検索し、必要なデータを取得する方法について

部品表というブックがあります
A列に商品名、B列に商品番号が入力してあります。C列のコードは未入力です。
A列     B列     C列      
商品名  商品番号  コード
モータ  U-1325-L  
ホルダ  R-134256

また、コード一覧表という別のブックには、A列に商品番号と、B列にコードが、何千件も入力されています。

やりたいことは
部品表のC列のコード欄に、コード一覧表ブックから商品番号と一致するコードを貼り付けしたいのです。

部品表は、何百種類もありますので、関数ではなく、マクロで処理を希望します。

自分では、部品表の商品番号をコピーして、コード一覧表で検索し、検索結果の右隣のセル(B列のコード)の値を部品表のC列に貼り付ければよいかと思い、書いてみたんですが…

Sub 別ブックから貼り付ける()
  Dim 検索する As Long
Windows("部品表.xls").Activate
検索する = cells(i,2).Value
Windows("コード一覧表.xls").Activate
ActiveWindow.SmallScroll Down:=-3
Selection.AutoFilter Field:=3, Criteria1:="=検索する", Operator:= xlAnd

と、してみたものの、検索しても、その検索結果の隣のセルのコードをどうやって取得すればいいのかが、わかりませんでした。

基本事項は本で学びましたが、呪文のようなコードはよく理解できません。懸命にネットで検索して、訳して理解する努力をしてはいますが。

どうぞよろしくお願いします。

部品表というブックがあります
A列に商品名、B列に商品番号が入力してあります。C列のコードは未入力です。
A列     B列     C列      
商品名  商品番号  コード
モータ  U-1325-L  
ホルダ  R-134256

また、コード一覧表という別のブックには、A列に商品番号と、B列にコードが、何千件も入力されています。

やりたいことは
部品表のC列のコード欄に、コード一覧表ブックから商品番号と一致するコードを貼り付けしたいのです。

部品表は、何百種類もありますので、関数...続きを読む

Aベストアンサー

こんにちは。
とりあえず実用性も踏まえました。
メインの動作はワークシート関数のVLOOKUPをVBA上で使用していますので理解はしやすいかと思います。
また、質問文から察するに「部品表.xls」と「コード一覧表.xls」の両方を開いて処理されていますが「コード一覧表.xls」はプログラム内で開いて閉じているので実行するときは「コード一覧表.xls」は閉じて置いてください。
Option Explicit
Sub Sample()
 Application.ScreenUpdating = False
 Dim I As Long
 Dim xlBook
 Set xlBook = Workbooks.Open("C:\★★\コード一覧表.xls") '★要変更★
 I = 2
 Do While Range("A" & I).Value <> ""
  ThisWorkbook.Worksheets("Sheet1").Range("C" & I).Value = Application.VLookup(ThisWorkbook.Worksheets("Sheet1").Range("B" & I).Value, xlBook.Worksheets("Sheet1").Range("A2:B65535"), 2, 0)
  I = I + 1
 Loop
 xlBook.Close
 Application.ScreenUpdating = True
 MsgBox ("完了")
End Sub

こんにちは。
とりあえず実用性も踏まえました。
メインの動作はワークシート関数のVLOOKUPをVBA上で使用していますので理解はしやすいかと思います。
また、質問文から察するに「部品表.xls」と「コード一覧表.xls」の両方を開いて処理されていますが「コード一覧表.xls」はプログラム内で開いて閉じているので実行するときは「コード一覧表.xls」は閉じて置いてください。
Option Explicit
Sub Sample()
 Application.ScreenUpdating = False
 Dim I As Long
 Dim xlBook
 Set xlBook = Workbooks....続きを読む

Q《エクセル2000》オートフィルタで寄せたものに連番を振りたいのですが

オートフィルタの条件で抽出したデータのみ連番を振りたいのですが、上から1、2、3と入力し後はオートフィルで入力しようとしても全部1になってしまい、うまくいきません。

どうすればいいのでしょう?

Aベストアンサー

>これがうまくいきません
確かに... orz

申し訳ないですが、問題が解決しない場合、下記コードを
標準モジュールに貼り付けて実行してください
(事前に対象範囲を選択しておきます)

Sub Macro1()
Dim r As Range
Dim t As Range

Application.ScreenUpdating = False
Set r = Selection.SpecialCells(xlCellTypeVisible)
For Each t In r
t = t.Value
Next
Application.ScreenUpdating = True
End Sub

番号を振るマクロは、いまいち動作に確信が持てなかったので保留です
ごめんなさい

QExcelVBAを使って、値がある場合は作業を繰り返し実行するプログラムを作成したい。

以下のようなプログラムをVBAで作成したいと考えています。

A1のセルに値があれば、その値をB1に返す。
次にA2のセルに値があれば、その値をB2に返す。
A行に値がある一番下のセルまで同じようなことをさせたいと考えています。

VBAは初心者です。
どなかた宜しくお願い致します。

Aベストアンサー

#2さんと似たものですが・・・・参考にしてください。

Sub test001()
Dim i As Long
i = 1
Do While Cells(i, 1) <> ""
Cells(i, 2) = Cells(i, 1)
i = i + 1
Loop
End Sub

Q[初心者です]VBAで指定列からAを検索し、発見したら隣のセルに値0を入れるマクロ。

VBAで指定列からAを検索し、発見したら隣のセルに0を入れるマクロを組みたいのですが、組み方がVBA初心者の為わかりません。
(例)
L列に、A、B、C、D、E、Fとランダムに文字が入っていて、
文字Aを検索し、発見したら隣のI列に値0を入れるというマクロです。

Sub Search()
Dim A As String
Set A = Worksheets("Sheet1").Cells.Find("A")
If A Is Nothing Then
ActiveCell.Offset(0, 1).Value = 0

End If
End Sub
と過去の質問で考えてみたのですが、Aがあった時、、、、
とコードが書けないです。
大変困っているので、ご教授頂けないでしょうか?
出来れば、そのままマクロに出来るコードを教えて頂けないでしょうか?
宜しくお願い致します。

Aベストアンサー

こんばんは。

#3さんのおっしゃっていることも、もっともなのですが、気になる点がありましたので、自分のことを踏まえて、書かせていただきます。

いずれ、また、同じようなケースが出会うと思います。こんな原則を考えてみたらどうでしょうか?それは、私も自身も同じなのですが、ワークシートのコマンドで行われるものは、記録マクロから作ってみるということです。他にも、「統合」とか、「置換」とか「オートフィルタ」「フィルタオプション」とかは、みんなパターンが決まっています。
その中の代表格が、この「Find」 です。

>Set A = Worksheets("Sheet1").Cells.Find("A")

>過去の質問で考えてみたのです

どうも、Find メソッドは、あるレベル以下の人は、省略する傾向があるようです。何が大事で、何が大事でないかというのは、やってみなければ分かりませんが、検索語だけを入れる書き方は、実務では、あまりしないほうがよいと思います。

だいたい、以下のTestFind2 ぐらいまでに、省略は、とどめたほうがよいです。

それは、Find は、必ずしも自分が思っているデフォルトとは違うことがあるので、「明示的(意図的に)」にオプションは入れたほうがよいです。
例えば、大文字小文字の違いを付けるなら、MatchCase:=True, 数式まで探すなら、LookIn:=xlFormulas

なお、Find メソッドは、5年経っても、たぶん完全に覚えられません。面倒なコードのひとつです。ですが、これはパターンが決まっているので、ひとつパターンが決まったら、それに当てはめればよいだけです。

#3さんで示されているMougのサンプルコードと似てはいるのですが、Mougのサンプルコードでは、Verionによって、失敗することがあります。

'--------------------------------------
'記録マクロをそのまま使う方法
Sub TestFind1()
Dim c As Range
 Set c = Columns("L:L").Find(What:="A", _
           After:=ActiveCell, _
           LookIn:=xlValues, _
           LookAt:=xlPart, _
           SearchOrder:=xlByRows, _
           SearchDirection:=xlNext, _
           MatchCase:=False, _
           MatchByte:=False, _
           SearchFormat:=False)
 c.Offset(0, 1).Value = 0
End Sub
'--------------------------------------
'TestFind1 をアレンジしてみる
Sub TestFind2()
Dim c As Range
'検索語
Const MYTXT As String = "A"
 Set c = ActiveSheet.Columns("L:L").Find(What:=MYTXT, _
           LookIn:=xlValues, _
           LookAt:=xlPart, _
           MatchCase:=False)
 If Not c Is Nothing Then
    c.Offset(0, 1).Value = 0
 End If
End Sub

'---------------------------------------
'複数ある場合(パターンを使った方法)
'---------------------------------------
Sub TestFind3()
  Dim c As Range
  Dim FirstAdd As String
  Const MYTXT As String = "A"
  Set c = ActiveSheet.Columns("L:L").Find( _
    What:=MYTXT, _
    LookIn:=xlValues, _
    LookAt:=xlPart, _
    MatchCase:=False)
  
  If Not c Is Nothing Then
    FirstAdd = c.Address
    Do
      c.Offset(, 1).Value = 0
      Set c = ActiveSheet.Columns("L:L").FindNext(c)
      If c.Address = FirstAdd Then Exit Sub
    Loop Until c Is Nothing
  End If
End Sub

こんばんは。

#3さんのおっしゃっていることも、もっともなのですが、気になる点がありましたので、自分のことを踏まえて、書かせていただきます。

いずれ、また、同じようなケースが出会うと思います。こんな原則を考えてみたらどうでしょうか?それは、私も自身も同じなのですが、ワークシートのコマンドで行われるものは、記録マクロから作ってみるということです。他にも、「統合」とか、「置換」とか「オートフィルタ」「フィルタオプション」とかは、みんなパターンが決まっています。
その中の代表...続きを読む

QExcel VBAのオートフィルタ解除について

こんばんわ。
現在、コマンドボタン(オートフィルタの設定)にオートフィルタを設定するようなマクロを登録しています。
そして、別のコマンドボタンにはオートフィルタの解除を実施するマクロを書きたいんです。

しかし、オートフィルタを設定していない時に、解除のマクロを実行するとエラーになってしまいます。どうしたら良いのでしょうか?

If・・・文を使って、対象セルにオートフィルタが設定している時は解除を実施し、オートフィルタが設定されてない時は、何も実施しない。

このようなマクロはどうしたら良いのでしょうか?

よろしくお願いします。

Aベストアンサー

フィルタされていないシートに対して実行すると、
実行時エラー'1004':WorksheetクラスのShowAllDataメソッドが失敗しました。
というエラーが出ます(多分)

対象のシートが必ずアクティブなら、
If ActiveSheet.AutoFilterMode Then
 'オートフィルタを解除
 ActiveSheet.AutoFilterMode = False
End If


対象のシートがアクティブでなく、シートを指定する必要があるなら
If Worksheets("sheet1").AutoFilterMode Then
 'オートフィルタを解除
Worksheets("sheet1").AutoFilterMode = False
End If
(シート名は適時変更要)

これで、どうでしょうか?

QSub ***( ) と Private Sub ***( ) の違い

初歩的な質問で申し訳ありませんが・・・

自分でコードを書いていても、イベントが発生したりした時の処理で、コードのウィンドウで上のドロップダウンリストで選択できる時の処理などは自動的に[Private Sub Command1_Click( )]などと出てくるのでそのまま使っています。自分で別途プロシージャーを作成する時は[Sub ****( )]としています。
ですがその違いを理解しないまま、自分で作成する時は[Private Sub]ではなくて[Sub]を使っています。

Sub ***( ) と Private Sub ***( ) の違いは何なんでしょうか?
どなたか説明頂けませんか?
よろしくお願いします。

Aベストアンサー

「Sub」の部分にカーソルを置いて[F1]を押せばヘルプが起動します。
「指定項目」のところに「Public」と「Private」の説明がありますよ。
省略して「Sub hogehoge()」とした場合は「Public」とみなされます。

Publicは「すべてのモジュールから呼び出せるプロシージャ」ということになります。
Privateとすると「同じモジュールの中からしか呼び出せないプロシージャ」となります。

もしExcelをお持ちでしたらExcelのVBEで標準モジュールを追加し、「Sub Test1()」と「Private Sub Test2()」を作成してみてください。
そしてExcelの[ツール]-[マクロ]-[マクロ(Alt+F8)]でマクロ実行のダイアログを表示させてみるとわかります。
ここには実行できるプロシージャの一覧が表示されますが、Test1は表示されているけれどTest2は表示されません。
Test1はPublicで、Test2はPrivateだからです。


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング