[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『既存の表を整形するには』（なお）
前回[[20100309163432]]でお世話になりました。
今回もお力をお借りしたく、投稿いたします。
データ自体は別のシステムに入力をしていて、その中である条件を入れて検索した結果をエクセル出力しています。 それを開くと一番上のデータのように表で出てくる仕組みです。
シートの1～5行がタイトル行として設定されていて、B2に『会議資料（これは手入力しています）』 A2セルに『対象期間：平成22/03/01～平成22/03/15』と入力されています。 A4からR5の間に表の各項目が書かれていて、列幅は固定です。 A2セルですが、これはシステムから抽出してくる際に設定するのですが、 システム上で設定する抽出条件の対象期間と今回作成したい資料の対象期間が違うため、 実際はその期間以外のデータも上がってきてしまいます。 それをA2セルの対象期間以外の行を「表示しない」として、該当する表を作成したいのです。 対象としたい日付が入力されている列はH列で「2008/03/01」のように記載されています。 セルの書式設定で表示形式を見てみましたら、ユーザー設定「yyyy/mm/dd」となっていました。
基本的には1物件1行なのですが、その物件に対して担当者がシステムに折衝記録を入力をした場合、 Q列に入力した日付、R列にコメントが入ってきます。 複数日に渡って入力がある場合は1物件複数行になります。 その場合、A～P列は空白です（結合されていません）。
オートフィルタを使えばいいのかなと思い、過去ログを参照して H列のセルが空白の場合には上のセルと同じ値を白で入力するというコードを作ってみました。
この表を作るという作業が（システムから表を出力→表を整形）1回の作業に10回分、月2回作業する事になり、 なんとかマクロでやれないかな。と思っています。 A2セル内の文字を分割する方法も考えましたが、列幅が固定なので分割すると上手くいきません。 今回も説明不足かと思いますが、ご教授いただければと思います。 よろしくお願い致します。
オートフィルタで良いなら オートフィルタを使えば良いと思いますが。。。
何が駄目だったのですか？ A2セルの『対象期間：平成22/03/01～平成22/03/15』から 日付の範囲を抜き出すのが出来なかったのかな。。。？
Dim 開始 As Date, 終了 As Date
開始 = Mid(Range("A2").Value, 6, 10)
終了 = Mid(Range("A2").Value, 17, 10)
Range("H5").AutoFilter Field:=8, _
Criteria1:=">=" & 開始, Operator:=xlAnd, Criteria2:="<=" & 終了
(HANA)
HANA様
そうです。。。（汗 お教え頂いたコードを入れましたら完璧でした。 ありがとうございました！ しっかり勉強させていただきます。
（なお）
あ、上手く行きましたか。。。
Range("H5").AutoFilter
で、5行目以外が見出しとして認識されるように成ったら
もう少しまじめなコードに変更して下さいね。。。
(HANA)
HANA様
ありがとうございます。
>Range("H5").AutoFilter
こちらは最後に付け加える、という意味でしょうか。
お教え頂いたもので綺麗にできたので、深く考えてなかったのですが よくよく考えたら、 「A2のセルから特定範囲を抜き出す」ことが出来れば その範囲以外の行を表示しないという風にすればいいだけなので オートフィルターにしなくてもよさそうですよね。。。（汗 私の最初の思い込みが違っていたようでした。。。 これからそちらにもチャレンジしてみようと思います。
毎回、丁寧に教えてくださって本当にありがとうございます。
（なお）
＞その範囲以外の行を表示しないという風にすればいいだけなので ＞オートフィルターにしなくてもよさそうですよね。。。（汗 まぁ、フィルタオプションの設定でも良いかもしれませんが 何れにしても、一般機能と組み合わせるのが簡単だと思います。
上で載せたコードは、オートフィルタの範囲は エクセルが自動認識した範囲に成ります。 エクセルが自動認識して、見出し行だと思った行に「 ▼」が表示されます。
データの状況に依っては、5行目以外が見出し行として認識される可能性が有ります。 もしも、3行目辺りが見出し行だと思われたら、4,5行目のH列は 日付が入っていないので確実に抽出対象から外れてしまうと思います。
例えば、1行目が空行で 2行目に見出しが有る表が有ったとします。 ほぼ確実な↓の様なデータ。 [A] [B] [C] [D] [1] [2] A B C D [3] 1 1 1 1 [4] 1 1 2 1 [5] 1 1 1 1 [6] 1 1 2 1 [7] 1 1 1 1 [8] 1 1 2 1 [9] 1 1 1 1 [10] もしも、5行目から（実際は見出し行じゃないけど）オートフィルタを設定したいと思って A5セルをアクティブにして設定しても、「 ▼」は2行目に出ると思います。
A5:D9セルを選択して設定すると、5行目に「 ▼」が設定されると思います。 手作業で設定と絞り込みを行う場合は、5行目を選択するだけで良いですが。。。
4行目より上がどの様なデータ状況なのか分からないので 何とも言えないですが。。。
上手く行かない場合は、私なら 5行目に空行を挿入して以降のテーブルと分けたあと オートフィルタを設定後、挿入した行を削除 するかもしれません。 範囲を指定してオートフィルタを設定するのではなく。 いや、理由は 範囲を決めるのが面倒だ ってだけなんですけどね。 一番不安が無いのは、やはりデータ範囲を確認して オートフィルタを設定したい範囲を指定して 設定する事だとは思いますが。
(HANA)
HANA様
丁寧な解説、ありがとうございます。 基本的にシステムから出力される表は2行が見出しとして作られています。 この場合でしたらA4に大分類（結合されています）、A5・B5に小分類といった具合です。 ただ、項目によっては小分類が無い為4行目のみに項目が表示されています。 （今回のH列はH4に表示されています）
先日教えていただいた方法で、間違いなく5行目にオートフィルタが入っており、 エクセルが認識した範囲も私が指定したい範囲と同じでしたので 問題なく使えております。
ですが、この表に限らず今後もこういった作業があると思いますので ＞5行目に空行を挿入して以降のテーブルと分けたあと ＞オートフィルタを設定後、挿入した行を削除 このやり方も非常に勉強になりました。
次回のこの作業まで少し時間がありますので、オートフィルタを使わない場合、 5行目に空行を挿入してオートフィルタを設定する場合、どちらも 勉強したいと思います。
（なお）
早速追加で申し訳ありませんが。。。
参考書とこちらのログを頼りにオートフィルタを使わない場合のコードを 作成してみました。
Sub test()
Dim MyLast As Long, 開始 As Date, 終了 As Date
ActiveSheet.ResetAllPageBreaks
MyLast = Range("A65536").End(xlUp).Row
Range("H9:H" & MyLast).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Font.ColorIndex = 2
Selection.FormulaR1C1 = "=R[-1]C"
開始 = Mid(Range("A2").Value, 6, 10)
終了 = Mid(Range("A2").Value, 17, 10)
For i = 6 To MyLast
If Cells(i, 8).Value < 開始 Or Cells(i, 8).Value > 終了 Then
Cells(i, 8).EntireRow.Hidden = True
End If
Next i
End Sub
これ自体は問題なく動いてくれるのですが処理時間がかかります。 （200行弱の表で10秒くらいです） このコード自体になにか問題があるのでしょうか。 それとも通常このくらいはかかるものなのでしょうか。 オートフィルタの場合、すごくスムーズでしたので 通常でこのくらい時間がかかるのであれば オートフィルタにしようかな。と思うのですが。。。 初心者のコードですので手直しなどありましたら ご教授下さい。
（なお）
あ～そうですねぇ。 10秒が妥当かどうかは分からないですが どちらかというと時間がかかりそうなコードに成ってますね。
良く言われるのが「セルへのアクセス」「画面描画」です。 「セルへのアクセス」ってのは、 セルに値を見に行ったり、何かを書き込んだり 選択したり、書式を変更したり 「画面描画」ってのは Cells(i, 8).EntireRow.Hidden = True これを切り換えると 画面の表示が変わります。 Selection.FormulaR1C1 = "=R[-1]C" この辺りの値を書き込んでも （これは数式ですが）表示が変わりますね？ そう言った事になります。
前半の 文字色を白にして、数式を埋め込むのは いくつかの範囲を一括で処理していますが 後半の、非表示にする部分は一つずつ処理がされています。
例えば、A1:A10セルの行を選択して、非表示にするのと A1セルの行を非表示。次にA2セルの行を非表示。そしてA3セルの・・・・ と、A10セルまで処理を繰り返すのとでは 前者の方が処理が早く済みそうなのはそう難しくなくイメージ出来ると思います。
Application.ScreenUpdating = False なんて記述を見た記憶が有りませんか？ これは、画面の描画を一時停止させるコードです。
処理の前に入れて、最後に True を入れると 少しは早くなるかもしれません。
それから、直接セルの値を見るのではなく
一旦配列に入れて その値で確認をする事にすると
少しは早くなるかもしれません。
Dim tbl As Variant
tbl = Range("H1:H" & MyLast).Value
For i = 6 To MyLast
If tbl(i, 1).Value < 開始 Or tbl(i, 1).Value > 終了 Then
Cells(i, 8).EntireRow.Hidden = True
End If
Next i
また、その都度非表示にするのではなく 有る程度非表示にする行を調査しておいて 一度に非表示にする事にすると、もう少し早く成るかもしれません。
表示－非表示の切換で、数式の再計算が行われ 処理が遅くなることも有ります。
この手（処理速度）の話は色々な所に有るので探すのは大変ですが けっこうみなさん色々なテクニックを使って居られると思います。
＞Selection.SpecialCells(xlCellTypeBlanks).Select こういうのもその内の一つだと思います。 For i = 6 To MyLast If Cells(i, 8).Value ="" Then とやらずに済みますからね。
なんだか、とりとめが無くなってしまいましたが。。。 私ならオートフィルタを使います。メンドウナノデ。
▼が気になるなら、フィルタオプションの設定でも・・？
後は、何順になっているのか分かりませんが H列の日付順に成っているなら 非表示にする行は、「前の方か後の方に固まっているかな？」と思います。
取り敢えず、Application.ScreenUpdating を入れて見られては？
(HANA)
HANA様
早速に有難うございます！
＞▼が気になるなら、フィルタオプションの設定でも・・？ 全く気になりません（笑 ですので、基本的にはオートフィルタでやってみようと思っています。
ただ、途中でせっかく思いついたので、自力で何とかしてみようと思い 作ってみました。 フィルタオプションも、HANA様の最初のレスでも仰っていたので やってみようと思っています。
処理速度については、あんまり考えた事がなかったのでご説明いただき ありがとうございました。 このコードもマクロの記録を切って貼ってといった具合で作成したので とても勉強になります。
＞後は、何順になっているのか分かりませんが H列の日付順に成っているなら ＞非表示にする行は、「前の方か後の方に固まっているかな？」と思います。 表の順番の優先順位としては、担当者列（I）→成約ランク列（A)→物件区分列（B) →H列の日付けの順です。 なので、一人の担当者分が終わると次の担当者の古い日付の物が出てくるといった具合で、 分散しているわけではないのですが、そのエリアにしかないというのでもないような。。。
分かりにくい説明でスミマセン。 色々試してみたいので、また何かありましたらよろしくお願い致します。
（なお）
日付順では無いのですね。
こちらは行を削除してしまう物ですが 本質的には同じ様な事なので参考に成ると思います。 [[20090903213512]] 『もしB列とD列が同じ値の場合 行削除をしたい』（にしき） リンク先のリンク先まで見てもらえれば フラグをつけたり、まとめて書いたり、別の場所へ作ったり フィルタオプションの設定も有りますので 良いと思います。
自分で書いたコードと似たコードは 初めてみるコードよりも興味深く見られると思うので （「この前自分はこんな感じにしたけど、この人はどうやってるのかな？」 なんて思いながら見ると、処理内容も分かりやすいと思いますし 自分の持っていない部分も明確になって良いと思います。） たくさん作ってみて下さい。
(HANA)
HANA様
ありがとうございます！ 早速リンクを少し見てみましたが、とても興味深いものでした。 参考にしてまた挑戦してみたいと思います。
ありがとうございました！
（なお）
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.