インターネットでは、次の 6 つのことを Excel で行うように言い続けられていますが、やめてください。

Excel のチュートリアルはどこにでもありますが、「プロ向けのヒント」の多くは、実際には隠れた悪い習慣です。壊れた数式から乱雑なデータの書式設定まで、密かにワークブックを台無しにする一般的なヒントとその代わりに何をすべきかをここに示します。

データの機能を維持するには、選択範囲全体の中央を使用します

ほぼすべての「美しい」Excel チュートリアルでこのようなことが行われます。誰かがレポートの上部にあるセル範囲を選択し、[結合して中央揃え]ボタンを押します。見た目は素晴らしいかもしれませんが、セルの結合は、データセット内で行うことができる最も破壊的な書式設定の選択の 1 つです。これを行うとすぐにグリッド構造が破壊され、列を並べ替えたり、きれいな数式を記述したり、クリーンアップせずにピボットテーブルや Power Query などのツールを確実に使用したりすることが困難になります。

代わりに、選択範囲全体の中央を使用してください (Ctrl+1 > 配置 > 水平 > 選択範囲の中心）。結合されたセルと同じように見えますが、すべてのセルは独立したままです。これを頻繁に使用する場合は、時間を節約するためにクイック アクセス ツールバーに追加することもできます。

大丈夫なとき: セルの結合は、実際のデータ分析には決して使用されない 1 回限りのプレゼンテーション カバーや印刷可能なフォームには適しています。

時代遅れの VLOOKUP 関数を捨てて、より現代的な関数を使用しましょう

XLOOKUP に切り替えてスプレッドシートの復元力を高めます

何十年もの間、VLOOKUP はゴールド スタンダードでしたが、脆弱な場合もあります。静的な列インデックス番号に依存しているため、列を挿入または削除すると数式が壊れる可能性があります。また、左から右へのみ検索しますが、これは複雑なデータセットでは大きな制限となります。

そのため、XLOOKUP に切り替える必要があります。列インデックスは必要なく、任意の方向に検索でき、複数の条件を処理したり双方向検索を実行したりするように適応できます。これは、データ関係を管理するためのより堅牢で読みやすい方法です。

大丈夫なとき: 新しい関数をサポートしていない Excel 2019 以前のバージョンでスタックしている人々とファイルを共有する場合は、VLOOKUP を使用してください。

関連している

古い Excel スプレッドシートが「レガシー コード」である理由 (およびその修正方法)

従来のスプレッドシートを、拡張性があり、時の試練にも耐えられる保守可能な自動ツールに変換します。

手動でのセルの色分けをやめる

組み込みツールを使用してビジュアルを自動化する

インターネットでは色分け用のペイント バケット ツールが人気ですが、手動による色付けは静的であり、プロジェクトのステータスが変化したり、ワークブック全体のカラー テーマを変更する必要がある場合に維持するのが困難です。それとは別に、手動ペイントでは、時間の経過とともにデータが変化するにつれて誤解を招くスプレッドシートが作成される可能性が高くなります。

より良い方法は、セル スタイルと条件付き書式を組み合わせることです。を使用します。 セルスタイル のギャラリー 家 タブには静的なヘッダーと入力セルが表示されるため、どこに入力して何を放っておけばよいのかが誰でも正確にわかります。これらは、後でワークブックのテーマを変更すると自動的に更新されます。ロジックに基づいて変化するデータの場合は、 条件付き書式設定、また、 家 タブをクリックすると、セルの値に基づいてセルが自動的に異なる色に変わります。これにより、視覚的な手がかりが常に実際のデータに対して正確であることが保証されます。

大丈夫なとき: 手動の色付けは、簡単な修正、データの公式記録や永続的な構造の一部ではない自分自身への一時的なメモ、または Web サイトやアプリのように見えるよう慎重に作成されたホームページ ワークシートやダッシュボードに適しています。

インターフェイスをクリーンアップするために行と列を非表示にすることは避けてください。

グループ化ツールを使用してワークスペースを切り替え可能に保つ

列を右クリックして[非表示]を選択すると、乱雑なシートを簡単に解決できるように思えるかもしれませんが、特に共同ワークブックの場合、レイアウトを管理する最も信頼できる方法であるとは限りません。通常、列見出しは何かが欠落していることを示す微妙なヒントを提供しますが、これらの視覚的なインジケーターは見落としやすく、隠れたデータが見落とされやすくなります。

列をグループ化する必要があります (データ > アウトライン > グループ）それらを隠すのではなく。グループ化は多くの場合、データを展開または折りたたむための明確な切り替えを備えており、よりユーザーフレンドリーです。非表示とは異なり、グループ化では複数レベルのサブグループを作成できるため、表示される内容をきめ細かく制御できます。

結果を確認するためだけに大量のデータを非表示にしていることに気付いた場合は、3 つのタブ ルールを使用して、開発者のようにスプレッドシートを構造化した方が良いかもしれません。多くの場合、非表示にする必要があると思われるデータは、実際には完全に別の背景シートに属しています。

大丈夫なとき: PDF エクスポート用の最終的な静的レポートを準備する場合は、ビューアが実際のグリッドを操作しないため、非表示にしても問題ありません。

誰も読めない巨大な数式を構築するのはやめてください

ヘルパー列、LET、Power Query を使用して複雑なロジックを簡素化する

メガフォーミュラ (10 行の長さの入れ子になった巨大な数式) は、デバッグするのが非常に困難です。結果が間違っている場合は、原因を見つけるために括弧の層を解析する必要があります。これらは、誰もフォローできない巨大な連続文として小説を書くのに相当するスプレッドシートです。

最初の代替案は、ヘルパー列を使用することです。アマチュアだと見る人もいますが、実際はその逆です。これらにより、ロジックが追跡可能になり、修正が容易になります。また、目に見えない計算を、操作したり、ピボットテーブルなどの他のツールに入力したりできる使用可能な数値に変換します。

もしあなたの論理が しなければならない ただし、LET 関数を使用すると、数式内の計算に名前を割り当てることができ、複雑なロジックが読みやすくなり、効率が向上します。

おそらく、最も強力な代替アプローチは、Power Query を使用して条件付き列を作成することです。これにより、ロジックが専用のインターフェイスに移動され、メイン シートがすっきりし、数式が短くなります。

大丈夫なとき: ほとんどの場合、Excel の巨大な数式に依存する理由はほとんどありません。そうは言っても、長い式とネストされた式を区別することが重要です。最近の関数の多くは正しく動作するために複数の引数を必要とするため、必然的に式が長くなりますが、それは問題ありません。複数の論理テストをネストし始めると、問題が発生します。

入力値を数式に直接ハードコードしないでください。

名前付き範囲と専用の変数テーブルを使用する

=(@Sales)*0.07 など、数式に値を直接入力することは、データが古くなる一般的な原因です。税率が 8% に変更されると、すべての計算式を探して手動で更新する必要があります。 1 つでもミスすると、ワークブック全体が間違っている可能性があります。

値をハードコーディングするのではなく、ワークブック内の専用の場所に変数を配置し、これらのセルに名前を割り当てます ( 数式 > 名前マネージャー または ネームボックス 左上隅にある）ので、数式は =(@Sales)*TaxRate のようになります。これは理解しやすく、単一の入力セルを変更するとワークブックが自動的に更新されます。

さらに良いことに、 選択範囲から作成 複数の変数に同時に名前を付けるツール。

関連している

Excel オブジェクトにソフトウェア開発者のような名前を付ける方法

一貫した表記法、テーブルベースのアーキテクチャ、グローバル定数、自己文書化ロジックを通じて、ユーザーから開発者に移行します。

大丈夫なとき: 1 日 24 時間、1 ドル 100 セント、円の 360 度など、決して変更されない普遍的な定数の場合、ハードコーディングは安全です。

派手なスプレッドシートのトリックに騙されがちですが、将来の自分は物事をシンプルかつ論理的にしてくれたことに感謝するでしょう。こうした問題の多くは、しっかりした基礎から始めることで回避できます。フィールドに列を使用し、レコードに行を使用するなど、データを正しく構造化すると、作業が確実に行われます。 と エクセルではなく、 に対して それ。

OS

Windows、macOS、iPhone、iPad、Android

無料トライアル

1ヶ月

Microsoft 365 には、最大 5 台のデバイスで Word、Excel、PowerPoint などの Office アプリ、1 TB の OneDrive ストレージなどへのアクセスが含まれています。