データをクリーンアップする方法トップ 10
Excel for Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 その他…表示数を減らす
単語のスペル ミスの、迷惑な末尾のスペース、不要な接頭辞、不適切な大文字/小文字、印刷されない文字などがあると、第一印象が悪くなります。 データが汚くなる原因はこれだけではありません。 準備はいいですか。 Microsoft Excel のワークシートの大掃除を始めましょう。
__
データのクリーンアップの基本
データベース、テキスト ファイル、Web ページなど、外部データ ソースからインポートするデータの書式設定と種類を、常に制御できるわけではありません。 データを分析する前に、データのクリーンアップが必要になることがよくあります。 さいわい、Excel には、データを目的の書式に正確に設定するのに役立つさまざまな機能があります。 場合によっては、簡単な作業で、それを行う特定の機能が存在することもあります。 たとえば、スペル チェックを使えば、コメントや説明を含む列のスペル ミスがある単語を簡単にクリーンアップできます。 または、[重複の削除] ダイアログ ボックスを使うと、重複する行をすばやく削除できます。
また他の場合には、式を使って列を操作し、インポートした値を新しい値に変換することが必要になる場合もあります。 たとえば、末尾のスペースを削除する場合は、式を使ってデータをクリーンアップする新しい列を作成し、新しい列に入力して、その新しい列の式を値に変換してから、元の列を削除します。
データをクリーンアップする基本的な手順は次のとおりです。
外部データ ソースからデータをインポートします。
別のブックに元のデータのバックアップ コピーを作成します。
データが行と列の表形式であることを確認します。各列の類似データ、すべての列と行が表示され、範囲内に空白の行がないことを確認します。 最適な結果を得るには、Excel テーブルを使用します。
スペル チェックや [検索と置換] ダイアログ ボックスを使って、列の操作を必要としない作業を最初に行います。
次に、列の操作が必要な作業を行います。 列の操作の一般的な手順は次のとおりです。
1. クリーンアップする必要がある元の列 (A) の隣に、新しい列 (B) を挿入します。
2. データを変換する式を、新しい列 (B) の先頭に追加します。
3. 新しい列 (B) の下方向に式をコピーします。 Excel のテーブルでは、入力された値で集計列が自動的に作成されます。
4. 新しい列 (B) を選び、コピーして、値として新しい列 (B) に貼り付けます。
5. 元の列 (A) を削除します。新しい列が B から A に変換されます。同じデータ ソースを定期的にクリーンするには、マクロを記録するか、コードを記述してプロセス全体を自動化することを検討してください。 また、サード パーティのベンダーによって作成された外部アドインも多数あります。このアドインは、「 サード パーティ プロバイダー 」セクションに記載されており、自分でプロセスを自動化する時間やリソースがない場合の使用を検討できます。
| 詳細情報 | 説明 |
|---|---|
| target=”_blank” title=”ワークシートのセルに自動的にデータを入力する” rel=”noopener”>ワークシートのセルに自動的にデータを入力する | [フィル] コマンドの使い方を説明します。 |
テーブル
target=”_blank” title=”の作成と書式設定” rel=”noopener”>の作成と書式設定行と列
target=”_blank” title=”を追加または削除してテーブルのサイズを変更” rel=”noopener”>を追加または削除してテーブルのサイズを変更する target=”_blank” title=”Excel テーブルで計算列を使用する” rel=”noopener”>Excel テーブルで計算列を使用する | Excel のテーブルを作成し、列または計算列を追加または削除する方法を説明します。
target=”_blank” title=”マクロ記録で作業を自動化する” rel=”noopener”>マクロ記録で作業を自動化する | マクロを使って繰り返し行うタスクを自動化するためのいくつかの方法について説明します。
__
スペル チェック
スペル チェック機能を使うと、スペル ミスのある単語を探すだけでなく、ユーザー辞書に製品名や会社名といった値を追加することで、それらの値の使用に一貫性がない場合を発見できます。
| 詳細情報 | 説明 |
|---|---|
| target=”_blank” title=”スペル チェックと文章校正を行う” rel=”noopener”>スペル チェックと文章校正を行う | ワークシートでスペル ミスがある単語を修正する方法を示します。 |
| target=”_blank” title=”スペル チェック辞書に単語を登録する” rel=”noopener”>スペル チェック辞書に単語を登録する | ユーザー辞書を使う方法について説明します。 |
__
重複する行の削除
行の重複は、データをインポートするときによくある問題です。 重複する値を削除する前にまず、重複しない値を抽出して結果が意図したとおりになるか確認することをお勧めします。
| 詳細情報 | 説明 |
|---|---|
| target=”_blank” title=”重複しない値を抽出する、または重複する値を削除する” rel=”noopener”>重複しない値を抽出する、または重複する値を削除する | 重複しない行を抽出する方法と、重複する行を削除する方法という、密接に関連する 2 つの手順を示します。 |
__
テキストの検索と置換
一般的な先行文字列 (後にコロンとスペースが続くラベルなど) またはサフィックス (使われなくなったり不要になった、文字列の最後にある説明的語句など) を、削除したいことがあります。 そのテキストのインスタンスを検索して、非テキストまたは他のテキストに置き換えることで、これを行うことができます。
| 詳細情報 | 説明 |
|---|
target=”_blank” title=”セルにテキストが含まれているかどうかを調べる (大文字と小文字を区別する)” rel=”noopener”>セルにテキストが含まれているかどうかを調べる (大文字と小文字を区別する) | [検索] コマンドといくつかの関数を使ってテキストを検索する方法を示します。
target=”_blank” title=”テキストから文字を削除する” rel=”noopener”>テキストから文字を削除する | [置換] コマンドといくつかの関数を使ってテキストを削除する方法を示します。
target=”_blank” title=”ワークシートの文字列と数値を検索または置換する” rel=”noopener”>ワークシートの文字列と数値を検索または置換する | [検索] および [置換] ダイアログ ボックスの使い方を示します。
target=”_blank” title=”FIND、FINDB” rel=”noopener”>FIND、FINDB
target=”_blank” title=”SEARCH、SEARCHB” rel=”noopener”>SEARCH、SEARCHB
target=”_blank” title=”REPLACE、REPLACEB” rel=”noopener”>REPLACE、REPLACEB
target=”_blank” title=”SUBSTITUTE” rel=”noopener”>SUBSTITUTE
target=”_blank” title=”LEFT、LEFTB” rel=”noopener”>LEFT、LEFTB
target=”_blank” title=”RIGHT、RIGHTB” rel=”noopener”>RIGHT、RIGHTB
target=”_blank” title=”LEN、LENB” rel=”noopener”>LEN、LENB
target=”_blank” title=”MID、MIDB” rel=”noopener”>MID、MIDB | 文字列内のサブ文字列の検索と置換、文字列の一部の抽出、文字列の長さの特定など、さまざまな文字列操作タスクの実行に使うことができる関数があります。
__
文字列の大文字と小文字の変換
テキストが寄せ集めで、特に大文字と小文字が問題になることがあります。 3 つの Case 関数から適切なものを使って、テキストを小文字に (メール アドレスなど)、大文字に (製品コードなど)、または適切な使い分けに (名前や書籍のタイトルなど) 変換できます。
| 詳細情報 | 説明 |
|---|---|
| target=”_blank” title=”文字列の大文字、小文字の変換を行う” rel=”noopener”>文字列の大文字、小文字の変換を行う | 3 つの Case 関数を使う方法を示します。 |
| target=”_blank” title=”LOWER” rel=”noopener”>LOWER | テキストに含まれるすべての大文字を小文字に変換します。 |
| target=”_blank” title=”PROPER” rel=”noopener”>PROPER | 英字文字列の単語の先頭の文字、および記号の次の文字を大文字に変換します。 それ以外の英字はすべて小文字にします。 |
| target=”_blank” title=”UPPER” rel=”noopener”>UPPER | テキストを大文字に変換します。 |
__
テキストからスペースや印刷されない文字を削除します。
テキスト値の先頭や末尾に、または途中に連続する複数のスペース文字 (Unicode 文字セットの値 32 および 160) が含まれていたり、テキスト値に印刷されない文字 (Unicode 文字セットの値 0 から 31、127、129、141、143、144、157) が含まれるていることがあります。 これらの文字は、並べ替え、フィルター、検索を行うときに予期しない結果の原因になることがあります。 たとえば、外部データ ソースに誤って余分なスペース文字が追加されていたり、外部ソースからインポートしたテキスト データに印刷されない文字が含まれていたりすることがあります。 これらの文字は簡単にはわからないので、予期しない結果を理解するのが難しい場合があります。 これらの不要な文字を削除するには、TRIM、CLEAN、SUBSTITUTE 関数を組み合わせて使うことができます。
| 詳細情報 | 説明 |
|---|---|
| target=”_blank” title=”CODE” rel=”noopener”>CODE | テキスト文字列内の先頭文字の数値コードを返します。 |
| target=”_blank” title=”CLEAN” rel=”noopener”>CLEAN | 7 ビット ASCII コードの先頭から 32 個の印刷されない文字 (値 0 から 31) をテキストから削除します。 |
| target=”_blank” title=”TRIM” rel=”noopener”>TRIM | 7 ビット ASCII のスペース文字 (値 32) をテキストから削除します。 |
| target=”_blank” title=”SUBSTITUTE” rel=”noopener”>SUBSTITUTE | SUBSTITUTE 関数を使うと、大きい値の Unicode 文字 (値 127、129、141、143、144、157、160) を、TRIM および CLEAN 関数を使うことができる 7 ビット ASCII 文字に置き換えることができます。 |
__
数値と符号の修正
数値に関してデータのクリーンアップが必要になる 2 つの主な問題があります。数値が誤ってテキストとしてインポートされた場合と、負の符号を組織の標準に変更する必要がある場合です。
| 詳細情報 | 説明 |
|---|---|
| target=”_blank” title=”文字列として保存されている数値を数値形式に変換する” rel=”noopener”>文字列として保存されている数値を数値形式に変換する | テキストとして書式設定されてセルに格納されたために、計算で問題が発生したり並べ替え順序で混乱を招いたりする可能性のある数値を、数値形式に変換する方法を示します。 |
| target=”_blank” title=”DOLLAR” rel=”noopener”>DOLLAR | 数値をテキスト形式に変換し、通貨記号を適用します。 |
| target=”_blank” title=”TEXT” rel=”noopener”>TEXT | 値を特定の数値形式のテキストに変換します。 |
| target=”_blank” title=”FIXED” rel=”noopener”>FIXED | 指定した桁数に四捨五入し、結果をピリオド (.) とコンマ (,) を使って書式設定した文字列に変換して返します。 |
| target=”_blank” title=”VALUE” rel=”noopener”>VALUE | 数値を表す文字列を数値に変換します。 |
__
日付と時刻の修正
多くの異なる日付形式があり、これらの形式はスラッシュやハイフンを含む番号付きの部品コードやその他の文字列と紛らわしい場合があるため、多くの場合、日付と時刻の変換や書式の再設定が必要になります。
| 詳細情報 | 説明 |
|---|---|
| target=”_blank” title=”日付システム、日付形式、2 桁で表された西暦の解釈方法を変更する” rel=”noopener”>日付システム、日付形式、2 桁で表された西暦の解釈方法を変更する | Office Excel での日付システムの動作方法について説明します。 |
| target=”_blank” title=”時間を変換する” rel=”noopener”>時間を変換する | 異なる時間単位の間の変換方法を示します。 |
| target=”_blank” title=”文字列形式の日付を日付形式に変換する” rel=”noopener”>文字列形式の日付を日付形式に変換する | テキストとして書式設定されてセルに格納されたために、計算で問題が発生したり並べ替え順序で混乱を招いたりする可能性のある日付を、日付形式に変換する方法を示します。 |
| target=”_blank” title=”DATE” rel=”noopener”>DATE | 指定した日付を表す通し番号を返します。 この関数を挿入する前のセルの表示形式が [標準] であった場合、結果は日付形式になります。 |
| target=”_blank” title=”DATEVALUE” rel=”noopener”>DATEVALUE | テキストで表された日付をシリアル値に変換します。 |
| target=”_blank” title=”TIME” rel=”noopener”>TIME | 指定した時刻に対応する小数を返します。 この関数を挿入する前のセルの表示形式が [標準] であった場合、結果は日付形式になります。 |
| target=”_blank” title=”TIMEVALUE” rel=”noopener”>TIMEVALUE | 文字列で表された時刻を小数に変換します。 この小数は 0 から 0.99999999 の範囲内の値であり、0:00:0 (午前 00 時) から 23:59:59 (午後 11 時 59 分 59 秒) までの時刻を表します。 |
__
列の結合と分割
外部データ ソースからデータをインポートした後の一般的なタスクは、複数の列を 1 つに結合したり、1 つの列を複数の列に分割することです。 たとえば、姓名を含む列を姓と名に分割することがあります。 または、住所フィールドを含む列を、番地、市区町村、都道府県、郵便番号の列に分割することがあります。 逆の場合もあります。 姓と名を結合したり、住所を 1 つの列にまとめたりする場合です。 それ以外に列の分割や結合を行う一般的な値としては、製品コード、ファイル パス、インターネット プロトコル (IP) アドレスなどがあります。
| 詳細情報 | 説明 |
|---|
target=”_blank” title=”姓と名を結合する” rel=”noopener”>姓と名を結合する
target=”_blank” title=”テキストと数値を結合する” rel=”noopener”>テキストと数値を結合する
target=”_blank” title=”テキストを日付または時刻と結合する” rel=”noopener”>テキストを日付または時刻と結合する
target=”_blank” title=”関数を使用して複数の列を結合する” rel=”noopener”>関数を使用して複数の列を結合する | 複数の列の値を結合する一般的な例を示します。
target=”_blank” title=”区切り位置指定ウィザードを使用して、テキストをさまざまな列に分割する” rel=”noopener”>区切り位置指定ウィザードを使用して、テキストをさまざまな列に分割する | このウィザードを使い、さまざまな一般的な区切り記号に基づいて列を分割する方法を示します。
target=”_blank” title=”関数を使用してテキストを複数の列に分割する” rel=”noopener”>関数を使用してテキストを複数の列に分割する | LEFT、MID、RIGHT、SEARCH、LEN 関数を使って、名前の列を 2 つ以上の列に分割する方法を示します。
target=”_blank” title=”セルの内容を分割して隣接する列に配置する” rel=”noopener”>セルの内容を分割して隣接する列に配置する | CONCATENATE 関数、& (アンパサンド) 演算子、区切り位置指定ウィザードの使い方を示します。
target=”_blank” title=”セルの結合または結合されたセルの分割” rel=”noopener”>セルの結合または結合されたセルの分割 | [セルの結合]、[横方向に結合]、[セルを結合して中央揃え] コマンドの使い方を示します。
target=”_blank” title=”CONCATENATE” rel=”noopener”>CONCATENATE | 2 つ以上のテキスト文字列を 1 つのテキスト文字列に結合します。
__
列と行の変換と再配置
Office Excel のほとんどの分析および書式設定機能では、データは 1 つのフラットな 2 次元テーブルに存在するものと仮定されています。 場合によっては、行を列、列を行にしたいことがあります。 または、データが表形式で構成されておらず、表以外の形式から表形式にデータを変換する方法が必要な場合があります。
| 詳細情報 | 説明 |
|---|---|
| target=”_blank” title=”TRANSPOSE” rel=”noopener”>TRANSPOSE | セルの縦の範囲を横の範囲として、または横を縦として返します。 |
__
結合または照合によるテーブルのデータの調整
場合によっては、データベース管理者は、複数のテーブルを結合するときに、Office Excel を使って、検索およびマッチング エラーの修正を行います。 これには、異なるワークシートの 2 つのテーブルの調整が含まれることがあります (たとえば、両方のテーブルのすべてのレコードを表示したり、テーブルを比較して一致しない行を検索したりする場合)。
| 詳細情報 | 説明 |
|---|---|
| target=”_blank” title=”データ リスト内の値を検索する” rel=”noopener”>データ リスト内の値を検索する | 検索関数を使ってデータを検索する一般的な方法を示します。 |
| target=”_blank” title=”LOOKUP” rel=”noopener”>LOOKUP | 1 行または 1 列の範囲、または配列に含まれる値を返します。 LOOKUP 関数には、ベクトル形式と配列形式の 2 種類の書式があります。 |
| target=”_blank” title=”HLOOKUP” rel=”noopener”>HLOOKUP | テーブルの最初の行または値の配列の値を検索し、テーブルまたは配列で指定した行から同じ列の値を返します。 |
| target=”_blank” title=”VLOOKUP” rel=”noopener”>VLOOKUP | テーブル配列の最初の列の値を検索し、テーブル配列の別の列から同じ行の値を返します。 |
| target=”_blank” title=”INDEX” rel=”noopener”>INDEX | テーブルまたはセル範囲にある値、あるいはその値のセル参照を返します。 INDEX 関数には、配列形式とセル範囲形式の 2 種類があります。 |
| target=”_blank” title=”MATCH” rel=”noopener”>MATCH | 指定した順序で指定した値と一致する配列内の項目の相対的な位置を返します。 項目自体ではなく範囲内の項目の位置が必要なときは、いずれかの LOOKUP 関数ではなく MATCH を使います。 |
| target=”_blank” title=”OFFSET” rel=”noopener”>OFFSET | セルまたはセル範囲から指定された行数と列数だけシフトした位置にあるセル範囲の参照を返します。 返されるセル参照は、単一のセル、セル範囲のいずれかの参照です。 また、返されるセル参照の行数と列数を指定することもできます。 |
__
サードパーティのプロバイダー
以下は、さまざまな方法でデータをクリーンアップするために使用できる製品のあるサードパーティ プロバイダーの一覧の一部です。
注: Microsoft はサードパーティ製品をサポートしていません。
ListCleaner Pro
Clean and Match 2007
ページの先頭へ