数千行の表で「なんか少ない」｜フィルターと非表示の行が計算とコピーに混ざる仕組み

数千行の表で、件数が「なんか少ない」と感じた日

数千行ある表から必要なデータをコピーして、別の表で件数を数えたときのことです。出てきた数字を見て、なんとなく少ない気がしました。根拠があったわけではなく、ふだん見ている数字と比べて「こんなに少なかったかな」という勘です。

元の表を見直すと、フィルターがかかったままになっていました。一部の行が隠れた状態でコピーしていたので、隠れていた行がまるごと抜け落ちていたのです。どこまでが正しくてどこからが抜けているのかを切り分けられず、結局その作業は一からやり直しました。

逆の立場も経験しています。自分がフィルターをかけたまま表を保存して共有し、受け取った相手が「データが足りない」と困ってしまったことがあります。

この2つの経験から、いまはコピーする前に必ずフィルターと非表示を解除するようにしています。この記事では、見えていない行が計算やコピーでどう扱われるのか、いま隠れている行があるかをすぐ見分ける方法、そして安全な作業の順番を整理します。

見えていない行は、操作によって扱いが違う

「隠れている行」には、フィルターで隠れた行と、右クリックの［非表示］で隠した行の2種類があります。やっかいなのは、この2つが計算やコピーで同じ扱いにならないことです。

操作	フィルターで隠れた行	［非表示］で隠した行
SUM・COUNTA で集計	含まれる	含まれる
SUBTOTAL(9,…)・SUBTOTAL(3,…)	含まれない	含まれる
SUBTOTAL(109,…)・SUBTOTAL(103,…)	含まれない	含まれない
範囲を選んでコピー	含まれない（見えている行だけ）	含まれる
その範囲へ貼り付け	隠れた行にも貼られる	隠れた行にも貼られる

ここから読み取れる注意点は3つです。

• SUMは隠れた行も足す：フィルターで絞り込んだのに、合計は全件のまま、ということが起きます。
• コピーはフィルターなら見えている行だけ、非表示なら全部：同じ「見えない行」なのに、コピーの結果が変わります。
• 貼り付けは隠れた行にも入る：フィルター中の表に貼り付けると、見えていない行のデータを上書きしてしまいます。

筆者のケースは、フィルターで隠れた行がコピーで抜けたパターンでした。隠れた行を含めて全部コピーしたつもりが、実際には見えている行だけが移っていたのです。

いま隠れている行があるかを10秒で見分ける

隠れた行があるかどうかは、画面の何か所かを見れば判断できます。

• 行番号の色：フィルターで絞り込んでいると、行番号が青い文字になります。
• 行番号の飛び：「5、6、9、10」のように番号が飛んでいたら、間の行が隠れています。［非表示］の場合も同じです。
• 見出しのボタン：フィルターがかかっている列は、見出しの▼ボタンが漏斗の形のアイコンに変わります。
• ステータスバー：フィルターで絞り込んだ直後は、画面左下に「3,249 レコード中 2,780 個が見つかりました」のような表示が出ます（数字は作例です）。
• 列番号の飛び：行だけでなく列も見ます。「C、D、F」のようにEが抜けていたら、列が隠れています。

漏斗のアイコンが付いた見出しの▼を押すと、どの項目のチェックが外れているかも確かめられます。「部署」の列で営業だけにチェックが入っている、といった絞り込みの中身まで見ておけば、誰かが意図して絞り込んだのか、解除し忘れただけなのかを判断する手がかりになります。複数の列で絞り込まれていることもあるので、アイコンが付いた列は全部見ておきます。

数千行の表では、画面の上のほうだけ見ても行番号の飛びに気づけないことがあります。行番号の色と見出しのアイコンを最初に確かめる癖を付けると、見落としが減ります。

件数のずれを式で見つける

勘に頼らず確かめたいときは、式を2つ並べると隠れた行の数が出ます。作例として、データがA列の2行目から3250行目まであるとします。

• 全部の行数：=ROWS(A2:A3250)
• 見えている行数：=SUBTOTAL(103,A2:A3250)

2つの数字が同じなら、隠れた行はありません。違っていれば、その差が隠れている行の数です（空白のセルがある列で数えると、SUBTOTAL側が空白を数えないぶん少なく出るので、必ず値が入っている列を選びます）。

たとえば全部の行数が3,249で、見えている行数が2,780なら、469行が隠れています。筆者があのとき感じた「なんか少ない」を、数字で確かめられるわけです。

表の端の空いているセルにこの2つを置いておくと、コピーや集計の前に一目で確認できます。受け取った表を集計の前に点検する手順は、受け取った表を集計前に点検する4つの操作でも、フィルターの一覧を使う方法として紹介しています。

コピーで抜ける・混ざるのはなぜか

コピーの結果が変わるのは、Excelが「フィルターで隠れた行は、利用者があえて外したもの」とみなしているからです。絞り込んだ結果だけを別の表に移したい、という使い方を想定して、見えている行だけをコピーします。

一方、［非表示］は「見た目を整えるために隠しただけ」とみなされ、コピーにはそのまま含まれます。印刷や画面の見やすさのために隠していた行が、コピー先にいきなり現れて驚くのはこのためです。

［非表示］の行を外して、見えている行だけをコピーしたいときは、次の手順を使います。

1. コピーしたい範囲を選ぶ
2. Alt+;（セミコロン）を押す。見えているセルだけが選ばれた状態になる
3. Ctrl+C でコピーして、貼り付け先に貼る

逆に、フィルターで隠れた行も含めて全部コピーしたいときは、フィルターを解除してからコピーするしかありません。筆者がいま「コピーの前に解除」を徹底しているのは、この扱いの違いを毎回思い出すより、解除してしまうほうが確実だからです。

フィルター中の貼り付けがいちばん危ない

コピーより怖いのが、フィルターがかかった表への貼り付けです。

たとえば、「部署＝営業」で絞り込んで、見えている20行のステータス欄に、別の表からコピーした20行を貼り付けたとします。見た目は20行にきれいに貼れたように見えますが、実際には隠れている行も含めて、上から順に20行分のセルに貼られます。営業以外の部署の行も上書きされ、営業の行には途中までしか入りません。

フィルターを解除した瞬間に、データがずれていることに気づくことになります。しかも上書きされた値は、元に戻すで取り消さない限り戻りません。

同じ値を見えている行だけに入れたいなら、範囲を選んで Alt+; で見えているセルだけを選び、値を入力して Ctrl+Enter を押す方法なら、隠れた行には入りません。行ごとに違う値を貼りたいときは、フィルターを解除し、並べ替えなどで対象の行をまとめてから貼るのが安全です。

貼り付けてしまったと気づいたら

フィルター中に貼り付けてしまったと気づいたら、ほかの操作をする前に Ctrl+Z で取り消すのが最優先です。フィルターを解除したり並べ替えたりすると、どの行が上書きされたのかを追えなくなります。

すでに保存してしまった場合は、上書きされた行を目で探すのはほぼ不可能です。元のデータが別のブックやシステムに残っているなら、そこから取り直すほうが早く確実です。筆者がフィルターの件で一からやり直したのも、どこまでが正しいのかを切り分けるより、最初からやるほうが確実だと判断したからでした。

見えている行だけを合計したいときは

フィルターで絞り込んだ結果を合計したいのに、SUMだと全件の合計が出てしまう。この場合は SUBTOTAL 関数を使います。

=SUBTOTAL(9,C2:C5000) と書くと、フィルターで隠れた行を除いて合計します。最初の数字が集計の種類を表していて、9が合計、3がデータの個数です。［ホーム］の［オートSUM］ボタンをフィルター中に押すと、自動でこのSUBTOTALの式が入ります。

ただし、表の早見表で見たとおり、9や3では［非表示］で隠した行は合計に含まれてしまいます。両方とも除きたいときは109や103を使います。SUMで合計が合わないときの基本的な確認は、SUMの合計が合わないときの確認にまとめています。

フィルターと非表示が混ざった表で、エラーのセルも飛ばしながら合計したい、といった一段複雑な集計になると、SUBTOTALだけでは足りなくなる場面も出てきます。たとえば、フィルターで絞り込んだうえで一部の行を手で隠し、さらに列の中に#N/Aが混ざっているような表です。SUBTOTALは、範囲の中にエラーのセルが1つでもあると、結果もエラーになってしまいます。

列の非表示とグループ化にも同じ落とし穴がある

ここまでは行の話をしてきましたが、列にも似た落とし穴があります。

• 隠した列も、横方向のSUMには入る：月ごとの列を横に並べた表で、不要な月の列を隠して =SUM(B2:M2) で合計すると、隠した列の数字も足されます。
• SUBTOTALは隠した列を外してくれない：SUBTOTALの109や103が除外するのは、隠れた「行」だけです。列を隠しても、計算の結果は変わりません。横方向に見えている列だけを合計したいなら、隠すのではなく、合計の範囲から外すか、表の形を見直します。
• 隠した列はコピーにも入る：［非表示］の行と同じで、範囲をコピーすると隠した列も一緒に貼られます。見えている列だけにしたいときは、行と同じく Alt+; を使います。

もう1つ見落としやすいのが、［データ］タブの［グループ化］で折りたたんだ行や列です。画面の左や上に「＋」「－」のボタンが出ているあれです。折りたたんだ状態は、Excelの中では［非表示］と同じ扱いになります。つまり、SUMやコピーには含まれ、SUBTOTALの9や3では外れません。グループ化を多用している表は、「＋」がないかも確かめておきます。

なお、範囲をテーブルに変換して集計行を表示すると、集計行には最初から SUBTOTAL(109,…) の式が入ります。フィルターで絞り込んだときも、手で行を隠したときも、見えている行だけで集計し直してくれるので、絞り込みを多用する表ならテーブル化しておくのも1つの手です。

共有する前に、フィルターと非表示を戻す

自分がフィルターをかけたまま表を渡して、相手を困らせてしまった経験から、表を保存して共有する前にも次のことを確かめています。

1. ［データ］タブの［クリア］で、すべての列のフィルターを解除する（Ctrl+Shift+L でフィルター自体を外してもよい）
2. Ctrl+A で全体を選び、行番号の上で右クリック→［再表示］、列番号の上でも右クリック→［再表示］
3. あえて隠したまま渡したい行や列があるなら、そのことを表の上やメールに一言書いておく

受け取る側は、フィルターがかかっていることに気づかないまま作業を始めることが多いものです。数千行の表なら、なおさらです。渡す側が戻しておくのが、いちばんの思いやりです。

受け取る側に回ったときも同じです。人からもらった表は、開いた直後に行番号の色と見出しのアイコンを見る。それだけで「最初から何行か隠れていた」という事故の多くは防げます。とくに、毎月同じ人から届く表は「いつもと同じだろう」と思い込みやすいので、件数の確認式を表の端に置いておくと、前月との比較も一緒にできます。

一からやり直さないための作業の順番

最後に、筆者が一からやり直す羽目になった経験をもとに、コピーや集計の前にやる順番を並べておきます。

1. 行番号の色、見出しの漏斗アイコン、行番号・列番号の飛びを見る
2. 隠れている行や列があれば、フィルターを解除し、非表示を再表示する
3. 必要なら =ROWS() と =SUBTOTAL(103,…) を並べて、件数が一致しているかを確かめる
4. そのうえで、コピーや集計をする
5. 絞り込んだ結果だけを集計したいときは、SUMではなくSUBTOTALを使う
6. 表を共有する前に、もう一度フィルターと非表示を戻す

「なんか少ない」という勘は、意外と当たります。ただ、勘に頼らなくても済むように、確認の順番を決めておくほうが確実です。

非表示とフィルターが混ざった表や、エラーを含む列を、見えている行だけで正しく集計する式をもう一歩踏み込んで確かめたい方は、合計が合わないときの応用テンプレート集で、SUBTOTALとAGGREGATEの使い分けを扱っています。