Tuesday, 14 July 2020

ExcelのSUBSTITUTE関数で文字を置換する方法【DTP・InDesign向け】


はじめに

DTP業界に従事していて、Excelを使ってのテキストの整形作業を行っている人はどれだけいるのかわかりませんが、自身はかなり多用しています。

それは仕事の内容がそうさせているのですが。


  • 名刺の大量発注
  • DMなどの宛名印字
  • 情報を冊子用テキストに変換(申請書類のような決まったフォーマットがExcelで作られており、そこからInDesignなどに流用できるように加工すること)


    上記の上2つは、InDesignで「データ結合」をする前段階に、レイアウトフォーマットに適合するようにデータをExcelの関数で加工する場合です。

    3番目は、InDesignで「データ結合」ができるように、セルの移動などをExcelの関数で加工する場合です。(これは実際に例をご覧いただかないと理解していただけないと思いますので、後のブログで改めて説明します)

    これらは、Excelの同じシート内の別セル、または別シートに関数を仕込んで自動的にテキストを整形させます。

    これをすることで、作業効率は飛躍的に向上します。
    手作業がほぼ無くなるので、反復作業の苦行から開放されます。

    ということで、ここではテキスト整形およびセル移動に絞ったExcel関数の使い方を紹介して参ります。

    また、関数の挙動についてはMac版Excelでの確認になります。
    Windows版でも、一部を除いて同様の動作になります。

    処理の仕方の考え方

    関数を使用することで、作業に伴うリスクを極力抑えることも考慮します。

    • 原稿であるExcelに近いところで作業を行う。
      →Excelデータを他のソフトへ移す際のコピペミスなどのリスクが発生する
    • 原稿のExcelを複製して元原稿は残しておく。
      →原稿のミスかどうかが分かるようにしておく
    • 原稿のデータ自体には触らない。
      →原稿のミスかどうかが分からなくなる

    特に最後の項目を考慮するには、関数を使用するのが良いことになります。
    関数は元データを参照するだけで、元データを変更することはないからです。

    目次―使用頻度の多い関数

    テキスト整形で使用頻度の多い関数です。
    今回はSUBSTITUTE関数を紹介しますが、順次紹介していきます。

    • ADDRESS
    • AND
    • ASC
    • CHAR
    • CLEAN
    • COLUMN
    • CONCATENATE
    • FIND
    • IF
    • INDIRECT
    • ISBLANK
    • JIS
    • LEFT
    • LEN
    • MID
    • NOT
    • OR
    • ROW
    • RIGHT
    • SUBSTITUTE
    • TRIM
    • VLOOKUP


    一番最初に考慮するテキスト整形は「置換」―SUBSTITUTE関数

    まず最初に確認しておきたいのが、Excelのテキストは統一されたフォーマットで入力されているかどうか、です。

    「スペース」を例にしますと、「氏名」の「氏」と「名」の間にスペースの入っていることが多いかと思います。
    このスペースが半角、全角どちらかで統一されているかどうか。
    下手をすると、全角スペース1つかと思ったら半角スペース2つだったりすることもあります。

    1つのExcelを複数の人間が操作するための不統一もよくあることです。
    これは中々統一させるのが難しいのはよく分かります。

    ただ、入力が統一されていないと、レイアウト上不統一で不細工になります。
    関数での処理上でも不具合が発生する可能性があります。

    このようなことを考慮して、あるテキストを別のテキストに差し替える作業をすることになります。

    それをするのが、テキストを置換するSUBSTITUTE関数になります。

    SUBSTITUTE関数の書式は以下のようになります。
    =SUBSTITUTE(元の文字列,検索する文字列,置き換える文字列[,位置])

    SUBSTITUTE関数のカッコ内の各文字列には直接文字を入れるか、セルを指定するかします。直接文字を入れる場合は「"置換"」のように「""」で囲みます。

    例1:A1セルのテキストの「痴漢」を「置換」に差し替える。
    A1:この文字列を痴漢します。
    =SUBSTITUTE(A1,"痴漢","置換")
    →この文字列を置換します。


    SUBSTITUTE関数の書式の最後にある[,位置]は、対象テキスト内に同じ文字列が複数ある場合に、テキストの左から何番目の文字列かを指定することができます。

    例2:A1セルのテキストの左から3番めの「痴漢」を「置換」に差し替える。
    A1:この文字列を痴漢して痴漢され痴漢いたします。
    =SUBSTITUTE(A1,"痴漢","置換",3)
    →この文字列を痴漢して痴漢され置換いたします。


    他に、REPLACE関数というものがありますが、自分の実務経験上使用する場面はありませんでしたので紹介に留めます。(ネット検索すると使用方法が出てきます)

    この関数は

    • Macに付属の「Numbers
    • Google Spread Sheet

    でも使用可能です。

    Excel関数以外でも置換はできるが…

    Excel関数以外でも置換は行なえます。


    • Excelの「検索・置換」機能
    • テキストエディタの「検索・置換」機能
    • InDesignの「検索・置換」機能


    自分も作業状況や作業リスクを考慮して上記3つのいずれかを使用する場合もあります。

    しかし、上記「処理の仕方の考え方」で示した通りに処理した方が結果は良くなると思います。。

    また、SUBSTITUTE関数はこれ単体での使用ではなく、他の関数との合わせ技で使用する方が多いのです。(合わせ技についてはまた改めて記事にします)







    No comments:

    Post a comment