Microsoft Excel でデータをクリーンアップする 8 つの方法

2023/06/18

特にスプレッドシートでは間違いが起こります。タイプミス、余分なスペース、重複、数式エラー、空白セルはすべて、データを分析するときに厄介になる可能性があります。Microsoft Excel スプレッドシートをクリーンアップするには、「ダーティデータ」を処理する次の方法を 1 つ以上試してください。

1. 余分なスペースを削除する

インポートや入力ミスによって、データ内に余分なスペースが入ってしまう可能性があります。これには、セルの先頭の先頭のスペース、末尾の末尾のスペース、または単語や値の間のその他のスペースが含まれる場合があります。無害に見えるかもしれませんが、余分なスペースを使用してデータをフィルタリング、並べ替え、または作成するときに簡単に問題が発生する可能性があります。

Excel シートの余分なスペースを削除するには、TRIM 関数と検索と置換ツールの 2 つの簡単な方法があります。

TRIM機能を使う

Excel の TRIM 関数は、特にデータから空白を削除するために機能します。数式の構文は単純ですTRIM(text)。セル参照または引数のテキストを入力します。

例を見てみましょう。セル A1 では、テキストの間に先頭、末尾、および余分なスペースがあります。別のセルに移動し、次の数式を入力して空白を削除します。

=TRIM(A1)

この関数はスペースを取り除き、クリーンなデータを提供します。

別の例として、セル参照の代わりにテキストを引数として入力します。次の式を使用して、テキストを必ず引用符で囲んでください。

=TRIM(“ John Jacob Smith ”)

スペースが削除されていることがわかります。

TRIM 関数を使用する際に注意すべき重要な点は、データを置き換える場合を除き、データが含まれているセル以外のセルに数式を入力することです。

検索と置換ツールを使用する

シート内に余分なスペースが散在している場合は、TRIM 関数の代わりに Excel の検索と置換ツールを使用することをお勧めします。この方法を使用すると、すべてまたは特定の数のスペースを削除できます。

「ホーム」タブに移動し、編集セクションの「検索と選択」ドロップダウンメニューを開き、「置換」を選択します。

表示される「検索と置換」ボックスで、すべてを削除する場合は「検索対象」ボックスを使用して 1 つのスペースを入力するか、削除する正確な数のスペースを入力します。

すべてのスペースを削除する場合、または保持したい正確な数のスペースを削除する場合は、「置換後の文字列」ボックスに何も入力しません。

[すべて置換] を選択して余分なスペースを削除すると、シートがすぐに更新されることがわかります。

2. 印刷されない文字を削除する

非印刷文字は、ポップアップされる可能性のある余分なスペースに似ています。これらは、NULL 文字、見出しの開始、フォームフィード、キャリッジリターン、単位区切り文字などの、7 ビット ASCII コードのゼロを含む最初の 32 文字です。スペースと同様、これらの文字は、 Excel シートで数式を計算したり、並べ替えたり、フィルター処理したりするときに問題を引き起こす可能性があります。

便利なことに、1 つの簡単な関数を使用して、Excel で印刷されない文字を削除できます。この関数は CLEAN と呼ばれ、数式の構文はですCLEAN(text)。ここに、引数のテキストまたはセル参照を入力できます。

この例では、セル A1 の数式の最初と最後に非印刷文字 CHAR(10) があります。

それらを削除するには、次の式を使用します。

=CLEAN(A1)

CLEAN 式の一部としてテキストを入力することもできます。別のセルを使用するのではなく、既存のテキストをクリーンなテキストに置き換える場合は、次の数式を使用します。

=CLEAN(CHAR(10)&"John Jacob Smith"&CHAR(10))

印刷されない文字は削除され、テキストだけが残ります。

3. 空白行の削除

シート内に空白行が残ってしまうと、実際のデータに必要なスペースがその空白行によって占有されてしまう可能性があります。わずか数ステップで Excel の空白行を削除し、シート内のスペースを取り戻すことができます。

「ホーム」タブに移動し、編集セクションの「検索と選択」ドロップダウンメニューを開き、「特別に移動」を選択します。

表示されるウィンドウで「空白」を選択し、「OK」をクリックします。

すべての空白が一時的に強調表示されます。行を削除する前に、他のデータがないことを確認してください。これは、右にスクロールしないと追加の列が表示されない場合に特に重要です。空白行を削除するには、「ホーム」タブに戻り、「削除」ドロップダウンメニューを開き、「シート行の削除」を選択します。

空白行が消え、シート内の残りの行が上に移動するのがわかります。

4. 大文字と小文字を修正する

データをインポートしている場合、データ内で大文字と小文字が混在していることが見つかる可能性があります。Excel でいくつかの簡単な case 関数を使用すると、大文字と小文字の一貫性を保つことができます。

LOWER はすべての文字を小文字に変換し、UPPER はすべての文字を大文字に変換します。PROPER はテキスト文字列内の各単語の最初の文字を大文字にし、他のすべての文字を小文字に変更します。さらに、PROPER は、1a から 1A のように、文字以外の文字の後の最初の文字を大文字にします。

それぞれの構文は同じで、LOWER(text)、UPPER(text)、およびでPROPER(text)、引数にはテキストまたはセル参照を使用できます。例を見てみましょう。

セル A1 のすべての文字を小文字に変更するには、次の数式を使用します。

=LOWER(A1)

同じセル内のすべての文字を大文字に変更するには、次の数式を使用します。

=UPPER(A1)

セル A1 のテキスト文字列内の各単語の最初の文字を大文字にするには、次の数式を使用します。

=PROPER(A1)

数式の引数にセル範囲を使用することもできます。PROPER 関数を使用すると、次の式でセル A1 から A5 までの大文字と小文字を変更できます。

=PROPER(A1:A5)

5. 重複を検索して削除する

重複データは、Excel シート内でクリーンアップする必要があるデータです。同じ顧客名、電子メールアドレス、電話番号、または類似のものを持つ可能性があります。この場合、重複するのは不要な追加データです。

Microsoft Excel で重複を検索して削除する方法はいくつかあります。さまざまな方法については、このトピックに関するチュートリアルを参照してください。ここでは、Excel の組み込みの重複の削除機能を使用する最も単純なオプションについて説明します。

Excel は最初のインスタンスを保持し、次のインスタンスを削除することに注意してください。

まず重複を含むセル範囲を選択し、次に「データ」タブに移動し、データツールグループの「重複の削除」をクリックします。

ポップアップウィンドウで、確認および削除するデータを絞り込むことができます。まず、重複を確認したい列のチェックボックスをオンにするか、「すべて選択」を使用します。次に、データにヘッダーが含まれる場合は、ボックスをオンにします。「OK」をクリックして続行します。

Excel が重複データを正常に削除すると、削除されたデータと保持されたデータの番号を知らせるポップアップメッセージが表示されます。

これらの重複が削除されてシートがクリーンアップされます。

6. フォーマットをクリアする

複数のユーザーが 1 つのシートで作業している場合、または別の場所からデータをコピーして貼り付けている場合は、必要のない余分な書式設定が存在する可能性があります。これには、太字のフォント、塗りつぶしの色、境界線が含まれる場合があります。幸いなことに、各セル、行、列を 1 つずつ変更する必要はありません。クリックするだけでシート内の書式設定をクリアできます。

シートに条件付き書式を設定している場合、ここで説明するように書式をクリアすると、その書式も削除されることに注意してください。

削除する書式設定を含むセル範囲を選択します。太字、色付き、斜体がテキストに適用され、塗りつぶしの色と境界線も付けられます。

「ホーム」タブに移動し、「クリア」ドロップダウンメニューを開き、「フォーマットのクリア」を選択します。

これらのセル内のすべての書式設定が消え、白紙の状態になることがわかります。

7. テキストを列に変換する

別のソースからデータを取り込んだ場合、必ずしも希望通りに配置されるとは限りません。列内の個別のセルに配置する必要があるデータを含む行がある場合があります。わずか数ステップでこのテキストを列に変換できるので、操作が簡単になります。

変換したいセルを選択し、「データ」タブに移動し、データツールグループの「テキストを列に変換」を選択します。

テキストを列に変換ウィザードが開いたら、いくつかの手順に従って、現在の状態と表示方法に基づいてデータを変換します。まず「区切り文字」または「固定幅」を選択します。Excel ではデータに基づいた推奨事項が提供されますが、最も適切なオプションを選択できます。「次へ」をクリックします。

最初のステップで選択したオプションに応じて、対応する 2 番目のステップが表示されます。たとえば、区切りデータを選択した場合は区切り文字を選択でき、固定データの場合は列幅を調整できます。「次へ」をクリックします。

テキストや日付などの列のデータ形式を選択し、変換されたデータの宛先を入力します。「完了」をクリックします。

テキストが列に変換され、すぐに作業できるようになります。

8. エラーを強調表示する

Excel は、数式によるエラーなど、セル内のエラーをうまく指摘しますが、長いスプレッドシートがある場合は、これらのエラーに気付かない可能性があります。条件付き書式を使用すると、エラーを強調表示できるため、エラーを見つけて修正しやすくなります。

シートの左上にある「すべて選択」（三角形）ボタンをクリックしてシート全体を選択します。特定のセルのみをチェックしたい場合は、代わりにそれらを選択してください。

「ホーム」タブに移動し、スタイルグループの「条件付き書式」ドロップダウンメニューを開き、「新しいルール」を選択します。

[新しい書式設定ルール] ウィンドウのオプションで、上部の [以下を含むセルのみ書式設定する] を選択し、下部のドロップダウンリストで [エラー] を選択します。

「フォーマット」ボタンをクリックして、適用するフォーマットを選択します。セルを色で塗りつぶしたり、テキストを太字にしたり、暗い枠線を追加したりすることができます。フォーマットを設定したら「OK」を選択します。

[新しい書式設定ルール] ウィンドウの下部に選択内容のプレビューが表示されます。「OK」をクリックしてルールを保存し、適用します。

シートまたは選択したセルを表示すると、これらのエラーがポップアップ表示されます。エラーを修正すると書式設定が消え、次の書式設定に進むことができます。

よくある質問

Excel でスペルミスをチェックするにはどうすればよいですか?

他の Microsoft Office アプリケーションと同様に、Excel でもスペルチェックを使用できます。これは、よくあるタイプミスやスペルミスを見つけるのに便利です。

「レビュー」タブに移動し、リボンの校正セクションで「スペルチェック」を選択します。スペルチェックボックスが表示されると、辞書に載っていない単語がスプレッドシートに表示されます。スペルを無視したり、単語を辞書に追加したり、手動で変更したり、提案のいずれかを使用したりできます。

Excel のエラーを修正する方法を知るにはどうすればよいですか?

シートに特定のエラーが表示された場合は、セルを選択して [数式] タブに移動します。「数式監査」グループの「エラーチェック」をクリックします。[エラーチェック] ウィンドウが表示され、エラーの簡単な詳細が表示されます。Microsoft on the Web からエラーに関するサポートを受けるか、計算手順を表示するか、エラーを無視するか、数式を編集するかを選択できます。

さらに、 Microsoft サポートサイトの「壊れた数式を回避する方法」ページにアクセスすると、一般的な数式エラーのリストと説明が表示されます。

Excel で行を列に、またはその逆に変換するにはどうすればよいですか?

ここで説明したテキストから列への機能と同様に、Excel でデータを行から列、または列から行に変換できます。

最も簡単な方法は、トランスポーズ機能を使用してコピーと貼り付けを使用する方法です。あるいは、TRANSPOSE 機能を使用することもできます。Excel でのデータの転置に関する完全なチュートリアルをご覧ください。両方の方法について説明しています。

画像クレジット: Pixabay。すべてのスクリーンショットは Sandy Writtenhouse によるものです。