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

ミスは起こります、特にスプレッドシートでは。タイプミス、余分なスペース、重複、数式エラー、空のセルは、データを分析する際に問題になることがあります。Microsoft Excelのスプレッドシートをクリーンアップするために、「汚れたデータ」を処理するためのこれらの方法の1つ以上を試してみてください。

FYI: 「C」ドライブのデータもクリーンアップする必要がありますか？方法をお見せします。

目次

• 1. 余分なスペースを排除する
• 2. 非印刷文字を削除する
• 3. 空の行を削除する
• 4. 文字の大文字小文字を修正する
• 5. 重複を見つけて削除する
• 6. 書式をクリアする
• 7. テキストを列に変換する
• 8. エラーを強調表示する
• よくある質問

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

インポートやタイプミスから、データに余分なスペースが含まれることがあります。これには、セルの先頭のスペース、末尾のスペース、または単語や値の間の雑多なスペースが含まれます。無害に見えるかもしれませんが、余分なスペースがあると、データをフィルタリング、ソート、または数式を作成する際に問題が発生することがあります。

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

TRIM関数を使用する

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

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

=TRIM(A1)

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

別の例として、セル参照の代わりに引数としてテキストを入力します。この数式を使用して、テキストを引用符で囲むことを忘れないでください：

=TRIM(“   John    Jacob    Smith   ”)

スペースが削除されるのがわかります。

TRIM関数を使用する際の重要な点は、データを含むセルとは異なるセルに数式を入力することです – そのデータを置き換えたい場合を除いて。

ヒント: これらの高度なExcel数式が非常に役立つことがあります。

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

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

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

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

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

1. 「すべて置換」を選択して余分なスペースを削除すると、シートが即座に更新されます。

2. 非印刷文字を削除する

非印刷文字は、余分なスペースと似たようなもので、出現することがあります。これらは、7ビットASCIIコードの最初の32文字、ゼロを含むもので、ヌル文字、見出しの開始、フォームフィード、キャリッジリターン、ユニットセパレーターなどがあります。スペースと同様に、これらの文字はExcelシートで数式を計算したり、ソートしたり、フィルタリングしたりする際に問題を引き起こす可能性があります。

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

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

これを削除するためにこの数式を使用します：

=CLEAN(A1)

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

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

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

3. 空の行を削除する

シートに空の行があると、実際のデータのために必要なスペースを占有することがあります。Excelで空の行を削除して、シートのスペースを取り戻すことができます。

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

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

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

1. 空の行が消え、シート内の残りの行が上に移動します。

知っておくと良いこと: Microsoft Excelでテキストを結合する方法を学びましょう。

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

データをインポートしている場合、データの中に混合された大文字小文字があることがあります。Excelのいくつかの簡単なケース関数を使用して、大文字小文字を一貫性のあるものにすることができます。

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シートでクリーンアップしたいデータの1つです。同じ顧客名、メールアドレス、電話番号、またはそれに類似するものがあり、重複は不要な追加データです。

Microsoft Excelで重複を見つけて削除する方法はいくつかあり、このトピックに関するチュートリアルでさまざまな方法を確認できます。ここでは、Excelの組み込みの重複削除機能を使用するという最も簡単なオプションを見ていきます。

Excelは最初のインスタンスを保持し、次のインスタンスを削除します。

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

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

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

1. 重複が削除された状態でシートがクリーンアップされます。

FYI: PDFのデータをExcelスプレッドシートに変換する方法を学びましょう。

6. 書式をクリアする

複数の人がシートで作業している場合や、他の場所からデータをコピー＆ペーストした場合、不要な書式が含まれていることがあります。これには、太字フォント、塗りつぶし色、または境界線が含まれることがあります。幸いなことに、各セル、行、または列を1つずつ変更する必要はありません。クリックするだけでシートの書式をクリアできます。

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

1. 削除したい書式を含むセル範囲を選択します。太字、色付き、イタリック体のテキストに加えて、塗りつぶし色と境界線が適用されています。

1. 「ホーム」タブに移動し、「クリア」ドロップダウンメニューを開き、「書式をクリア」を選択します。

1. そのセルのすべての書式が消え、クリーンな状態になります。

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

他のソースからデータを取り込むと、必ずしも希望する形で配置されているわけではありません。データが別々のセルに配置される必要がある行があるかもしれません。このテキストを列に変換することで、操作しやすくすることができます。

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

1. テキストを列に変換ウィザードが開いたら、現在の状態に基づいてデータを変換するためのいくつかの手順を進めます。「区切り記号付き」または「固定幅」を選択します。Excelはデータに基づいて推奨を提供しますが、最も適用可能なオプションを選択できます。「次へ」をクリックします。

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

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

1. テキストが列に変換され、作業できる状態になります。

知っておくと良いこと: GrammarlyとMicrosoft Editorのどちらを使用するかを学びましょう。

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

Excelは、数式からのエラーなど、セル内のエラーを指摘するのが得意ですが、長いスプレッドシートではこれらのエラーに気づかないことがあります。条件付き書式を使用すると、エラーを強調表示して、より目立たせて修正しやすくすることができます。

1. シート全体を選択するには、シートの左上にある「すべて選択」(三角形)ボタンをクリックします。特定のセルのみを確認したい場合は、それらを選択します。

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

1. 新しい書式ルールウィンドウのオプションで、「含まれているセルのみ書式設定」を選択し、下部のドロップダウンリストで「エラー」を選択します。

1. 「書式」ボタンをクリックして、適用したい書式を選択します。セルを色で塗りつぶしたり、テキストを太字にしたり、暗い境界線を追加したりできます。書式を設定したら、「OK」を選択します。

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

1. シートや選択したセルを表示すると、エラーが表示されます。エラーを修正すると、書式が消え、次のエラーに進むことができます。

よくある質問

Excelでスペルエラーを確認するにはどうすればよいですか？

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

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

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

シートに特定のエラーが表示された場合、そのセルを選択し、「数式」タブに移動します。「数式監査」グループで「エラーの確認」をクリックします。エラー確認ウィンドウが表示され、エラーの簡単な詳細が表示されます。MicrosoftのWebからエラーのヘルプを取得する、計算手順を表示する、エラーを無視する、または数式を編集することができます。

さらに、Microsoftサポートサイトの「壊れた数式を避ける方法」ページを訪れて、説明付きの一般的な数式エラーのリストを確認できます。

Excelで行を列に変換するにはどうすればよいですか？

ここで説明した「テキストを列に」機能と同様に、Excelで行を列に、または列を行に変換できます。

最も簡単な方法は、転置機能を使用してコピー＆ペーストすることです。あるいは、TRANSPOSE関数を使用することもできます。Excelでデータを転置する方法に関する完全なチュートリアルを確認してください。これには両方の方法が説明されています。

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