Excelの一般的なデータクリーンアップ式

数式に優れています

何年もの間、私はこの出版物を、物事のやり方を説明するだけでなく、後で調べるための記録を残すためのリソースとして使用してきました。 今日、私たちは災害であった顧客データファイルを私たちに手渡したクライアントを持っていました。 事実上すべてのフィールドのフォーマットが正しくありませんでした。 その結果、データをインポートできませんでした。 Visual Basicを使用してクリーンアップを実行するExcel用の優れたアドオンがいくつかありますが、マクロをサポートしないOffice forMacを実行しています。 代わりに、私たちは支援するためのまっすぐな公式を探します。 他の人が使用できるように、ここでそれらのいくつかを共有したいと思いました。

数字以外の文字を削除する

システムでは、多くの場合、電話番号を国コードを使用して句読点を使用せずに特定の11桁の数式に挿入する必要があります。 ただし、多くの場合、代わりにダッシュとピリオドを使用してこのデータを入力します。 これがのための素晴らしい公式です 数字以外の文字をすべて削除する Excelで。 数式はセルA2のデータを確認します。

=IF(A2="","",SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$25),1))*
ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10))

これで、結果の列をコピーして使用できます 編集>値の貼り付け 適切にフォーマットされた結果でデータを上書きします。

ORを使用して複数のフィールドを評価する

インポートから不完全なレコードを削除することがよくあります。 ユーザーは、複雑な階層式を常に記述する必要はなく、代わりにORステートメントを記述できることに気づいていません。 以下のこの例では、A2、B2、C2、D2、またはE2でデータが欠落していないかどうかを確認します。 欠落しているデータがある場合は0を返し、それ以外の場合は1を返します。これにより、データを並べ替えて、不完全なレコードを削除できます。

=IF(OR(A2="",B2="",C2="",D2="",E2=""),0,1)

フィールドのトリムと連結

データに[名]フィールドと[名前]フィールドがあり、インポートにフルネームフィールドがある場合は、組み込みのExcel関数連結を使用してフィールドを適切に連結できますが、TRIMを使用して、テキスト。 フィールドのXNUMXつにデータがない場合は、フィールド全体をTRIMでラップします。

=TRIM(CONCATENATE(TRIM(A1)," ",TRIM(B1)))

有効なメールアドレスを確認してください

@と。の両方を検索する非常に単純な式。 メールアドレス:

=AND(FIND(“@”,A2),FIND(“.”,A2),ISERROR(FIND(” “,A2)))

姓名を抽出する

時々、問題は反対です。 データにはフルネームフィールドがありますが、名前と名前を解析する必要があります。 これらの数式は、名前と名前の間のスペースを探し、必要に応じてテキストを取得します。 ITは、A2に姓がない場合、または空白のエントリがある場合にも処理します。

=IFERROR(IF(SEARCH(" ",A2,1),LEFT(A2, SEARCH(" ",A2,1)),A2),IF(LEN(A2)>0,A2,""))

そして姓:

=IFERROR(IF(SEARCH(" ",A2,1),RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1)),A2),"")

文字数を制限して追加…

メタディスクリプションをクリーンアップしたいと思ったことはありませんか? コンテンツをExcelにプルしてから、メタ記述フィールド(150〜160文字)で使用するためにコンテンツをトリミングする場合は、次の数式を使用してこれを行うことができます。 マイスポット。 スペースで説明をきれいに分割してから、…を追加します。

=IF(LEN(A1)>155,LEFT(A1,FIND("*",SUBSTITUTE(A1," ","*",LEN(LEFT(A1,154))-LEN(SUBSTITUTE(LEFT(A1,154)," ",""))))) & IF(LEN(A1)>FIND("*",SUBSTITUTE(A1," ","*",LEN(LEFT(A1,154))-LEN(SUBSTITUTE(LEFT(A1,154)," ","")))),"…",""),A1)

もちろん、これらは包括的であることを意味するものではありません…あなたがジャンプスタートをするのを助けるためのいくつかの簡単な公式だけです! 他にどのような数式を使用していますか? コメントにそれらを追加してください。この記事を更新するときにクレジットを差し上げます。

どう思いますか?

このサイトはAkismetを使用して迷惑メールを減らします。 コメントの処理方法を学ぶ.