他人の Excel スプレッドシート、今私が抱えている問題 – これら 5 つのツールが解決に役立ちます

技術的には必要なものがすべて含まれているにもかかわらず、それを見つけるのに非常に苦労する必要がある Excel スプレッドシートを、何度誰かが私に送ったか忘れました。データが間違った列に詰め込まれているか、重複レコードがあらゆる場所に潜んでいるか、次にファイルを開いた人を混乱させるために数式が設計されているように見える可能性があります。長年にわたり、私は他人の混乱を実際に作業できるものに変えるのに役立ついくつかの Excel ツールに落ち着きました。

以下のスプレッドシートをご覧ください。一見すると、すべてが順調に見えます。しかし、よく見てみると、エントリの欠落、注文 ID の重複、命名の不一致、単一セルに詰め込まれた連絡先詳細、必要以上の作業を行っている数式に気づき始めます。これはまさに、修正が必要なものを特定し、必要な変更を加えるためにいくつかの Excel ツールに手を伸ばすようなスプレッドシートです。

スペシャルに移動

問題が発生する前にギャップを見つけます

乱雑なスプレッドシートを継承すると、すぐに変更を開始したくなります。しかし、まず最初に、自分が実際に何を見ているのかを知る必要があります。

[特別に移動]は、空白、数式、定数、エラーなどの内容に基づいてセルを選択できる便利な Excel ツールです。私はここでこれを使用して、変更を開始する前にスプレッドシート内のギャップをすばやく見つけます。ツールを使用して空白セルを特定できたら、それらのセルを黄色で強調表示し、不足している情報を入力し、完了したら強調表示を削除します。

ギャップを強調表示する代わりにマークしたい場合は、次のように入力します。 空白 最初に選択したセルに入力し、Ctrl+Enter を押して、選択したすべてのセルを一度に入力します。

Go To Special では他にもたくさんのものを見つけることができますが、空白は出発点として役立ちます。ギャップを修正する方法を決定する前に、ギャップを確認したいと思います。

条件付き書式設定

何も削除せずに重複を特定する

条件付き書式は、定義したルールに基づいてセルに書式を適用するため、調査が必要な値に注意を向けるのに役立ちます。ここではこれを使用して重複レコードを強調表示し、矛盾したエントリを見つけるのに役立ちます。

たとえば、[注文 ID]列には、複数回出現するいくつかの ID が含まれています。条件付き書式設定を使用してその列内の重複を特定すると、重複するエントリがすぐに強調表示されます。対応する行を確認すると、完全に重複していることがわかります。そのため、重複するレコードを削除し、一時的な書式設定ルールをクリアします。

また、条件付き書式設定を使用して、一貫性のない製品エントリを調査します。ただし、重複値ルールでは大文字と小文字が区別されないため、最初に UPPER を使用して Product 値を同じ大文字と小文字に正規化してから、一意のルールを使用して調査する価値のあるエントリを明らかにします。次に、製品が 1 回しか表示されないことが必ずしも問題になるわけではないことを思い出しながら、その新しい列を手動でクリーニングします。

州の場合、LEN を使用した単純な長さベースのルールにより、省略されていない完全な州名が強調表示されます。これらを手動で標準化し、完了したら書式設定ルールをクリアします。

条件付き書式を使用すると、基礎となるデータを変更せずに、注意が必要なものが表示されるため、このアプローチが気に入っています。

テキスト分割

別々の情報が詰め込まれている

TEXTSPLIT は、カンマやスペースなどの区切り文字に基づいてセルの内容を複数のセルに分割するテキスト関数です。これは、継承したデータセットの[連絡先の詳細]列の場合のように、誰かが 1 つの列に複数の情報を詰め込んだ場合に特に便利です。各セルには、メール アドレス、電話番号、郵便番号がパイプ (|) 記号で区切られて含まれています。

TEXTSPLIT を使用すると、これら 3 つの部分を一度に別々の列に分割できます。次に、結果を確認し、値として貼り付け、元の連絡先詳細列を削除します。

元のデータを置き換える前に結果を確認できるのが気に入っています。分離すると、これらのフィールドのフィルタリング、並べ替え、または別の数式での使用がはるかに簡単になります。

Excel テーブル

クリーンアップされたデータに何らかの構造を与える

範囲のクリーニングが完了したら、それを Excel テーブルに変換します。これにより、データに組み込みのフィルタリング、自動展開、および計算列が追加されます。わざとそうしてるんだよ 後 重複を削除して不整合に対処している間にテーブル構造を追加したくないため、データを整理しました。

また、この時点で計算列内の既存の数式を構造化参照に変換したいと考えています。 Excel では、範囲をテーブルに変換するときに通常のセル参照が自動的に変換されないため、最初の行の数式で手動で変更を加えます。 Enter キーを押すと、Excel は自動的に数式を関連する列に移動します。

結果は、ワークシートの座標ではなくテーブルの列名を参照するため、非常に読みやすい計算になります。

名前付き範囲とヘルパー列

計算を理解し、維持しやすくする

ヘルパー列を使用すると、複雑な計算を小さくて目に見えるステップに分割できます。また、名前付き範囲を使用すると、重要な定数を数式の中に埋め込むのではなく、意味のある名前を付けることができます。ここでは両方を使用して、維持するのが難しい計算を理解し、変更しやすくします。

Excel の[選択項目から作成]ツールを使用して、一定の値 (売上税率と 2 つの割引しきい値と率) の名前を作成します。次に、各行に適用される割引が表示されるように、テーブルに[割引]列を追加します。最後に、合計数式では、指定された売上税率とともにその割引列を使用できます。

これで、税率や割引ルールのいずれかを変更する必要がある場合、正しい数値を見つけるために長い式を調べる必要がなくなりました。

ちょっとした構造が大きな効果を発揮します

スプレッドシートを設定するとき、私はいくつかの基本的な構造原則に従います。フィールドには列を使用し、レコードには行を使用し、各セルに 1 つのデータ ポイントを保持し、空の列と行 (可能であれば空のセル) を避け、列ヘッダーには単一の行を使用します。これらの経験則は、あなた、そしてスプレッドシートを送信する相手が後で構造を修正する必要がないことを意味します。

もちろん、ワークブックに目を向ける前に被害が発生する場合もあります。そのような場合、上記のツールを使用すると、他人のデータのクリーンアップがはるかに簡単になり、長期的には多くの悩みを軽減できます。