スポンサーリンク
例題
上図のようなExcel表で、1月～6月の数値が全て0の行を削除したい、ということはしばしばある。
「数値が少なくとも1つは0でない行だけ、削除せず残したい」と言い換えても良い。
もちろん削除対象となるのは、背景色を少し灰色にした№01・02・05の行だ。
№03の行は、数値の合計は0になるが、全て0というわけではないから削除しては駄目だ。
この例題について、今回の記事では、Excelの通常機能の範囲内で計算式による解決方法を取り上げる。
そして次回の記事で、マクロによる方法を示す。
IFやANDを用いた例(非効率)
ちょっとIF系の関数が分かる人なら、右端のJ列を作業列として
そしてJ列の縦方向にコピーしていくということだ。
このIF式の中身を詳説はしないが、要するに1個1個のセル全部について0かどうか調べているわけだ。
そして式の結果が「削除」となった行を、オートフィルタ機能でフィルタリングして、行削除するというわけだ。
しかしこの例なら数値は6列しかないからまだ良いが、もっと数値の列が多くなると、こんな長い式を書くのはよろしくない。
正答例(0の数をカウント)
ではどうするか。
1月～6月の「6つの列の数値が全て0」ということは、言い換えれば「0の数が6個ある」ということなので、0の数をカウントすれば良い。
つまりCOUNTIFS関数の出番だ。まあ、末尾にSの付かないCOUNTIF関数でも良いのだけど、ここでは上位互換といえるCOUNTIFS関数でいくことにする。
COUNTIFS関数で行く場合は簡単だ。
右端に作業列が必要なのは先程のIF関数と同じだけど、J2セルに
と入れれば良い。
後はその式を縦方向にコピーし、結果(0の数)が6となった行にオートフィルタでフィルタリングして、行削除すれば良い。
行削除のショートカットキーは
Ctrl + – キー
だ。
関数を入れた後の処理
このCOUNTIFS関数を書いたJ列は、コピー → 値のみ貼り付け により、早めに値のみに直しておくと良いだろう。
どうせ削除のためにしか使わないものだし、こういうところで関数を残して処理が重くなってはいけない。
また、COUNTIFS関数を書いた列だけを値のみに直しても、他のあちこちに書かれた計算式のせいで、行削除においてExcelの動作が重くなることもある。
だから行削除をするに当たっては、一時的に計算方法を「手動」に設定する対策も、した方が良いことがある。
どうしても面倒さが残る
ただこの方法も、やはり面倒だ。
一回限りなら私も手作業でCOUNTIFS関数を書いてから実施するが、何度もやりたくはない。
- 作業列の追加がいちいち必要。
- COUNTIFS関数を書くのも、セル範囲を指定したりするのが面倒。
- オートフィルタで「全て0」の削除対象データ数が多くなると、それらを行削除するにあたりExcelの動作が重くなりやすい。たとえ計算方法を手動に設定しようとも。
といった理由からだ。
またこの方法では、あくまで「0」の数をカウントするので、空白セルについては「0」にカウントできない。
いや、数値を入れるセルに空白セルを入れることじたいが良くないし、空白を「0」と同列扱いしようという運用じたいが良くないのだが、やるからには念のため空白セルもカウントできるようにしたいだろう。
ということで、Excelの通常機能・計算式による手作業の方法では、痒いところに手が届かない面倒さがどうしても残るというのが私の結論だ。
私も最近、こういう作業をやる機会が多かったのだが、どうにも不便を感じたので今回の記事を書くことにした。
そこで次回は、大したマクロでもないが、マクロで処理するサンプルを書いてみる。
スポンサーリンク
