見えない違いを調べる機能
たとえば下図のような表があったとします。
このデータの中で、"違っている"セルはどこでしょう？まぁ、すぐ分かりますよね。セルA4です。ほかは全部"100"なのに、こいつだけが"200"です。仲間はずれです。このように、セルに入力されている値であれば、ほかと違っているセルは一目瞭然です。しかし、Excelには、目で見ただけでは分からないけど、でも違っているというセルがあります。むしろ、よくあります。今回は、そうした「ただワークシートを見ただけでは分からないけど、でも本当は違ってるんだよ」というセルの調べ方をご紹介します。実はExcelには、そうした機能が用意されています。でも、ほとんどの人は、この機能の存在すら知りません。
ケース1：本当に合ってますか？
下図の表をご覧ください。簡単な集計表です。
簡単ですね。使っているのは「Excelは、SUMに始まりSUMに終わる」と言われている(私が勝手に言ってるだけですが)SUM関数だけです。これ、業務だと思ってください。あなたが、何かの集計をExcelですることになったと。もちろん実際はこれほど簡単な集計ばかりではないでしょう。何はともあれ、苦労して集計が終わりました。「課長、できました」と提出します。「うむ、ありがとう。これで明日の経営会議に間に合うよ」あなたも一安心です。「それじゃ、お先に失礼しま～す」って、いやいやいや！ちょっと待ってください！この集計表、本当に合ってますか？
種明かしをしましょう。セルE2では、正しい計算をしています。
しかし、セルE3は、
SUM関数で参照しているセル範囲が違っています。ちなみに、セルE5も違うんです。
最初の、計算結果だけが表示されている図を見て、これに気づきますか？この程度の表だったら、気づく方がいるかもしれませんね。しかし実務では、もっと複雑な集計をすることが多いです。E列のセルに、複数の関数を組み合わせた、難解な計算式が入力されていたらどうしますか？計算しているデータが数万行あったらどうしますか？目で見ただけで、間違いに気づきますか？無理です。ここまでを読んで「え～？こんな間違いしないっしょ、さすがに。普通、1個セルに入れたらコピーするっしょ、そしたら全部同じになるっしょ、これ、わざとやったんでしょ？こんな間違いなんて、ないないｗ」な～んて、心の中で思ったあなた。本当に、そう言い切れますか？自分は絶対に間違えないと断言できますか？それに、何度も言いますけど、実務で作る表は、これほど簡単ではありません。複数のシートやブックを参照するケースも多いです。何より、こうした計算式を毎回作るのではなく、別のシートやブックからコピーしたりしませんか？しますよね。そのとき、コピーした計算式で参照しているセルを、毎回ちゃんと確認していますか？コンピュータはミスをしません。ミスするのはヒトです。ただ、困ったことにヒトは「自分は間違えない」という、根拠のない自信を持ちがちです。
問題は、セルに計算式を入力すると「計算結果が表示される」ということです。もちろん、計算式を入力したセルにアクティブセルを移動すれば、数式バーに計算式が表示されます。セルを編集状態にして確認することもできます。何なら、FORMULATEXT関数でセルの計算式を表示したり、
シート上で計算式を表示するオプションもあります。
いずれにしても、データの件数が多かったら、目で確認するのは難しいでしょう。実はExcelには、このように「ほかとは違った計算式が入力されているセル」を一発で見つける機能があります。それが「アクティブ列との相違」です。使い方をご紹介します。まずは、「アクティブ列との相違」を実行するために使うダイアログボックスの表示方法をご紹介します。
もし、マウスで操作するのでしたら、[ホーム]タブ[編集]グループの[検索と選択]ボタンをクリックして表示されるメニューから[条件を選択してジャンプ]をクリックします。
実行すると[選択オプション]ダイアログボックスが表示されます。使うのは、この[選択オプション]ダイアログボックスです。
キーボードで操作するのでしたら、ワークシートで操作している状態で、Ctrl + Gまたは[F5]キーを押します。実行すると[ジャンプ]ダイアログボックスが表示されます。
ダイアログボックス左下にある[セル選択]ボタンをクリックするか、ここまでキーボードを使ってきたのですから、ここはAlt + Sを押しましょう。実行すると、先の[選択オプション]ダイアログボックスが表示されます。
この[選択オプション]ダイアログボックスを使うと、特定のセルだけを選択状態にできます。選択してしまえば、それらのセルを削除しようが、色を塗ろうが、コピーしようが、自由自在です。たとえば「数値が入力されているセル」だけを選択したり、「計算式が入力されていて、その計算式の計算結果がエラーになっているセル」だけを選択するなど、用途は多彩です。この[選択オプション]をよく使うという方は、おそらく「表示されているセル」だけを選択する"可視セル"選択を利用しているのではないでしょうか。さて、この[選択オプション]ダイアログボックスの右上に[アクティブ列との相違]という項目があります。
名称を見ただけでは「ちょっと何言ってるのか分からない」と、富沢のように感じるかもしれません。まずは、動作をご紹介します。
上図をご覧ください。いくつかのセルに数値が入力されています。選択しているセル範囲の、アクティブセルには"100"が入力されています。この選択しているセル範囲内で、アクティブセルの"100"とは異なっているセル、つまり"相違"しているセルを選択状態にするのが[アクティブ列との相違]です。実行すると、次のようになります。
もちろん、文字列でもいけます。
正直、これだけだったら「ふ～ん、で？」って感じるかもしれません。それが普通です。でも、この[アクティブ列との相違]は、セルの中に入力されているモノの相違をチェックします。つまり、セルに計算式が入力されていると、その計算式のパターン(相対的に見て、どこのセルを参照しているかなど)も調べてくれるんです。つまり、先にご紹介した、見た目は分からないけど、実はいくつかの計算式が間違っているケースにも活躍します。やってみましょう。
上図のように、計算式が入力されているセル範囲を選択します。このとき注意しなければいけないのは「正しい計算式が入力されているセルをアクティブセルにする」ということです。この状態で[アクティブ列との相違]を実行すると、次のようになります。
異なっているセルが複数あるようなら、この状態で選択されているセルに色をつけるなどしておき、1つずつ計算式を確認してください。あるいは面倒くさかったら、正しい計算式が入力されているセルE2を下までコピーしてください。
この機能は、"計算式で参照しているセルを、うっかり間違えちまった！なんてこった！"という、あってはならないミスを事前に防ぐことができる、超便利機能です。ぜひ、すべてのExcelユーザーに活用していただきたいです。しかし、ご存じの方はほとんどいません。その理由のひとつは、[アクティブ列との相違]という機能名称です。この名称から、何をする機能かを推測するのは不可能でしょう。これ実は、英語版Excelでは次のようになっています。
どこにも、"アクティブ"なんて言葉は出てきません。これは、最初に日本語訳した人のミスであり、そのミスをそのまま採用してしまったマイクロソフト社員のミスです。まぁ、ミスという表現は手厳しいかもしれませんけど、日本語の機能名称として分かりにくいのは事実です。ちなみに、このダイアログボックス内には、もっと重大で致命的な誤訳も含まれていますが、それは後述します。[アクティブ列との相違]とは、正確に言うと[アクティブセルがある列の中で、アクティブセルとはとの相違しているセル]ということです。日本語である「との」の使い方が間違っています。こんなの、分かるわけねーだろ！って訳です。いっそのことシンプルに[列内での相違]って訳しておけば、この便利機能の知名度も今より高まったことでしょう。
「こんな便利な機能があったのかぁ！もっと早く知りたかったぁ！」と、この素晴らしさに喜び庭駆け回っている皆様へ、さらに朗報です。この[アクティブ列との相違]には、ショートカットキーが割り当てられています。Ctrl + Shift + \です。「\」は、一般的な日本語キーボードではBackSpaceの左隣にある「\」と「|」のキーです。ただし、注意が必要です。この機能は「セルに入力されているモノ」をチェックします。セルに設定されている書式の違いなどは検出できません。そのへんを意識しながら活用してください。
ケース2：本当に合ってますか？Ⅱ
下図の表をご覧ください。いくつかの数値が入力されています。その数値の中で「70より小さい」セルは"赤い文字"で表示されるように条件付き書式を設定しています。
注意力の高い方でしたら「ん？あれ？これ変じゃね？」って気づくかもしれません。大多数の"気づかない方"に説明します。「70より小さい」数値のセルは"赤い文字"で表示されるように条件付き書式を設定してあると言いましたよね。じゃ、セルC5やセルB7を見てください。これ、70より小さいですよ。でも、赤くなっていません。「70より小さい数値は赤くなっているはず」という前提で、このデータを分析・集計したら大変なことになります。本稿のテーマは「見た目では分からないセルの違い」です。先の「計算式が入力されているセル」は、画面に表示されている計算結果の一覧だけを見ていると、セルに入力されている計算式の違いに気づきにくいという話でした。しかし、「見た目では分からない」という点では、この条件付き書式の方がやっかいです。アクティブセルを移動すれば数式バーに表示される計算式と違い、そのセルにどんな条件付き書式が設定されているかは、[条件付き書式ルールの管理]ダイアログボックスを開かなければなりません。
実は、こんなときにも[選択オプション]が役立ちます。やってみましょう。まずは、条件付き書式が設定されているであろうセル範囲を選択します。
このときも「アクティブセルには正しい条件付き書式が設定されている」状態にしてください。さて、先に解説した方法で[選択オプション]ダイアログボックスを開きます。
ダイアログボックス右下の方に[条件付き書式]という項目があります。何となく、これを使うとできそうな気がします。そう、できるんです。できるんですけど、ここ、ちょっとややこしい話なので、じっくり聞いてください。まず、[条件付き書式]の下に[データの入力規則]という項目がありますね。この[データの入力規則]を選択すると、その下にある[すべて]と[同じ入力規則]を選べます。"入力規則"に関して調べるのですから、よく意味は分からなくても、何となく[すべて]と[同じ入力規則]という選択肢があるってのも、まぁ、分からなくもないわけではありません。
でも、ここでちょっと変なことがあります。[条件付き書式]を選択したときでも、[すべて]と[同じ入力規則]を選べてしまうんです。
おかしくないですか？[条件付き書式]について調べるんです。その選択肢が[すべて]と[同じ入力規則]です。"条件付き書式＋入力規則"って？いったい何を調べるというのでしょう。
説明が面倒くさいので結論を書きます。これ、誤訳です。明らかに間違っています。まず[データの入力規則]＋[すべて]で実行すると、選択したセル範囲内で何らかの入力規則が設定されているセルだけが選択されます。それぞれのセルに設定している入力規則の内容は問いません。
一方の[データの入力規則]＋[同じ入力規則]を実行すると、選択したセル範囲内でアクティブセルと同じ入力規則が設定されているセルだけが選択されます。
さあ、問題は次です。[条件付き書式]＋[すべて]を選択して[OK]ボタンをクリックすると、選択したセル範囲内で何らかの条件付き書式が設定されているセルだけが選択されます。それぞれのセルに設定している条件付き書式の内容は問いません。
そして、意味の分からない[条件付き書式]＋[すべての入力規則]ですが、これは選択したセル範囲内でアクティブセルと同じ条件付き書式が設定されているセルだけを選択する機能です。入力規則ではありません。条件付き書式です。
つまり、選択肢[すべて]と[同じ入力規則]というのは、正しく表現すると
- すべて → すべての○○(その前で選択した条件付き書式または入力規則)
- 同じ入力規則 → アクティブセルと同じ○○(その前で選択した条件付き書式または入力規則)
ということです。これが正しい動作です。ちなみに、これも英語版Excelでは次のようになっています。
[Same]では、"入力規則"と特定していません。これは日本語訳をした人が、[All]と[Same]は"条件付き書式"でも使えるという機能的な仕様を知らなかったか、あるいは勘違いしたのでしょう。にしても、その訳をそのまま採用したマイクロソフト社員にとっては「うわ！やっべ！やっちまった！」っていうレベルの恥ずかしいミスです。まぁ、やっちまったものはしかたありませんが、できればこの訳、そのうちコッソリ修正してくれませんかね。この誤訳のおかげで「[同じ条件付き書式]が設定されているセルだけを選択するという機能がExcelに存在する」ということを、ほとんどの日本人Excelユーザーが知りません。なんか、それって不公平な気がします。
では、実際の動作をご覧ください。まず、条件付き書式が設定されているであろうセル範囲を選択します。
この状態で[条件付き書式]＋[すべて]を実行すると、次のようになります。
実は、セルD3、セルB7、セルC10、セルD16には、条件付き書式を設定していませんでした。見間違えないよう注意してください。セルB2はアクティブセルです。アクティブセルは選択されていますので、セルB2にも何らかの条件付き書式が設定されています。では次に、[条件付き書式]＋[同じ入力規則]で実行しています。ここで選択されるのは、アクティブセルと"同じ条件付き書式"が設定されているセルです。
さっきより"非選択"のセルが3つ増えました。セルC5、セルD11、セルB15です。この3つのセルには条件付き書式が設定されています。ただし「70より小さい」数値のセルを"赤い文字"で表示するではなく、「60より小さい」数値のセルは"赤い文字"で表示しろという条件です。ややこしいですね。でも、このややこしいトラブルは、ワークシートを見ただけでは決して分かりません。ここでもまた「え～こんなことする？」って感じた方はいませんか？するんです(キリッ。皆さん自身が。まず、条件付き書式はセルごとに設定する仕組みだということを正確に理解していない方が多いです。さらに、セルをコピーすると、そのセルに設定されているすべてがコピーされるという事実も軽視されがちです。セルをコピーしたとき、コピーされるのはセル内の値だけではありません。セルに設定されている表示形式や罫線、そしてセルに設定されている条件付き書式や入力規則などの設定も一緒にコピーされます。ちょっと、やってみましょう。
上図では、セル範囲B2:D4に「70より小さいとき赤文字にする」という条件付き書式を設定しています。[条件付き書式ルールの管理]ダイアログボックスの[適用先]が「=$B$2:$D$4」となっているのを覚えてください。この状態で、セルF3をセルC3にコピーします。コピー元であるセルF3には、条件付き書式を設定していません。
さて、[条件付き書式ルールの管理]ダイアログボックスを表示してみましょう。
[適用先]が変なことになっているのが分かりますか？[適用先]ボックスの右端にある[セル選択]ボタン(↑)をクリックすると、次のようになります。
ちょっと分かりにくいですが、セルC3には条件付き書式が設定されていません。こういう操作を、多くのユーザーが、何も考えずに、日々繰り返しています。結果的に、設定されているはずの条件付き書式がグダグダになって大騒ぎです。あなたも心当たりがあるのではありませんか？ぜひ、[選択オプション]でチェックする習慣を身につけてください。そして、何よりも、セルのコピーでは条件付き書式や入力規則なども一緒にコピーされるという認識を持ってください。
本稿執筆時点(2021年5月)では、まだ緊急事態宣言が続いています。飲食店では、お酒を飲ませてくれません。飲みに行けないので、必然的に"事務所時間"が増えました。おそらく、それが原因だと思われますが、どうも最近のコンテンツは長文になりがちです。「読むのが大変です！(｀ﾍ´)」というお叱りも受けています。すみません。今回も長くなりましたので、このへんでやめておきます。みなさんが不用意に行うセルのコピーによって、望まない条件付き書式や入力規則、果ては個人情報までもが一緒にコピーされてしまう件につきましては、別のコンテンツとしてまとめたいです。ああ、おそらく、こちらも長文になるかと。先に謝っておきます。ごめんなさい。