Excelでフィルタが途中で止まってしまった時の対処法《空白行編》

Excelでフィルタを行った際、途中で処理が無効となってしまい、フィルタのかかっていないセルがずるずると続いてしまう事がある。
前記事でも書いたが、これはExcelの仕様で、データの途中に何も無い空白行が存在するとそこまでをひとつの表と見做し、空白行の下から新たな表が開始されるものとして認識してしまうのである。

今回は、この空白行があるが為にフィルタが途中で止まってしまう場合の対処法に限定して書くこととする。

「空白行なんて手作業で消せば済む話じゃないか」とお思いの方は、膨大な量の設定を詰め込んだXMLファイルや十数年間分の帳簿、読み手のことなど全く無視したメモ書き程度の要件定義書（崩れ）を見たことが無いのだろうか。あんな大量の空白行を手作業で右クリック⇒Dキー押下で消していくなんて夢にも見たくない。

そんな経緯で作業開始である。至って簡単だ。

==============================

用意するもの：
・Microsoft Office Excel（バージョンは問わない）

手順：
１．空白行の入っているファイルをExcelで開く。
　　因みにExcelで開くことが出来れば拡張子は問わない。
２．フィルタが掛かっている場合は全て解除する。
　　［データ］タブから［フィルタ］をクリックすることで解除可能だ。
３．シートの最も左上にある四角を押下する。全選択となる筈である。
４．ここで漸くフィルタを設定する。
　　手順２と同じ操作でフィルタの設定が可能である。

あとは空白行のみ省いたり、必要なセルのみ抽出したりとお好みでフィルタ設定を行って構わない。

==============================

ここでのキモは手順３の全選択だ。

バージョンによっても挙動が異なるかもしれないが、いつもの癖で［Ctrl+A］を押下すると表ひとつ分しか全選択できないのをご存知であっただろうか。そのままフィルタを掛けた所で、やはり空白行の手前でフィルタリングが中断してしまう。

ここではシート全体をひとつの表としてExcelに認識させる必要がある為、すべてのセルを問答無用で選択できる「例の角っこ」を採用した。
因みに正式名称は「全セル選択ボタン」というそうだ。そのまんまじゃねえか。

CSVファイルにXAML文が入ってしまった時の対処法

WEBサービスからエクスポートしたCSVファイルを普通にExcelで開いた際、XMLにも似た「これ明らかに中身だろ」みたいな記述が挟まっていた経験はないだろうか。
僕の環境ではXAML文が行間に挟まっていて、Excelで表示した際に無駄な空白行があったり、本来記載されていてしかるべきデータがXAML文に呑み込まれて表示されない事態に陥っていた。

因みにExcelの仕様なのだが、データの途中に空白行が入っているとその部分を表の区切りとして認識してしまう。
何が困るかといえば、フィルタなどを使用した場合、表の区切りたる空白行まででフィルタリングが止まってしまうのである。即ち、ひとつの表に対して実行する処理はすべて、その空白行に辿り着いた時点で処理を中断するわけだ。
このままでは統計を取るにも整形をするにも不可能に近いので、シート内にはみ出てしまった中身たるXAML文をしまいしまいする。

以下が対処手順である。

==========================

用意するもの：
・バイナリエディタ
これについては何を使用しても構わないが、文字列のプレビューがあり、検索＆置換の出来るものが望ましい。
僕は有名どころのStirling(スターリング)を使用した。

手順：
１．何は無くともバイナリエディタを起動。
２．バイナリエディタで問題のCSVファイルを開く。
３．バイナリエディタのメニューから置換ウィンドウを開く。
４．取り除きたいデータを16進数で入力。(※後述)
５．置換後の文字列を入力するフォームは空白のままで置換実行。
６．CSVファイルを保存してからExcelで開き直す。

==========================

これで恐らく空白行もはみ出ていた中身も表示されない筈だ。

ここでサンシタエンジニア諸君を最も悩ませるのは手順４だろう。
取り除きたいデータが分かっている人は上記手順を素直に実施して構わないが、そもそも何であるのか分からない場合は、はみ出たXAML文の任意の行を追い、末尾に何が記載されているのかをExcelで確認しよう。

<br />
　↑こんなものが無いだろうか。
何処かで見覚えがあるこのスクリプトはこれ自体で改行を表すものである。XHTMLやXAMLなどに用いられる。
Excelではこれを変換できないため、中身がはみ出てしまっているというわけだ。

では改めてバイナリエディタで<br />を探してみよう。
半角英数字・半角記号はASCIIエンコーディングなので、この部分のバイナリデータを見ると、

3C 62 72 20 2F 3E

となっている筈だ。
この部分をまずはコピーしよう。上記バイナリの直後に［0A 0D］が重複している場合は、これも含めてコピーすること。
因みに［0A 0D］は改行（CR LF）を表している。［0A 0D］が二回記述されていれば空白行が１行出来上がることは自明である。

次に、置換ウインドウを開く。上記手順では３に当たる部分だ。

手順４で、置換したい文字列を入力するフォームに［3C 62 72 20 2F 3E (0A 0D)］を入力（先程コピーしたものをペーストで可）し、忘れずに16進数指定しておく。

あとは上記手順をそのままなぞればOK。

P.S.
XAML文については筆者自身あまり触れたことが無いので、指摘等あればコメントにて承ります。

サンシタのニッチな勘所

Excelでフィルタが途中で止まってしまった時の対処法《空白行編》

CSVファイルにXAML文が入ってしまった時の対処法