次の方法で共有

Office Space: Microsoft Excel の [区切り位置] 機能を使用する

  • [アーティクル]

Office Space

Office Space へようこそ。Office Space は、Microsoft® Office アプリケーションのスクリプト作成に関するヒントとテクニックを紹介する新しいコラムです。過去のヒントについては、Office Space アーカイブを参照してください。Microsoft Office でのスクリプト作成について質問がある場合は、scripter@microsoft.com (英語のみ) までお送りください。すべての質問に回答することはできないかもしれませんが、可能な限り対応いたします。

Microsoft Excel の [区切り位置] 機能を使用する

Microsoft Scripting Guys が常に心がけていることは、「ありのままを伝える」ということです。Microsoft 製品を過度にほめることのないよう努め、むしろ同僚が渋い顔をするような欠点すら指摘していきます。それは、別に隠れた意図があるからではなく、製品がどのように機能するか (また、まれなケースですが、どのように機能しないか) に対する理解を深めていただきたいと願うからです。 **

もちろん、このルールに対する 1 つの例外は Microsoft Excel です。それは、Excel が 100% 完璧だと思っているからでもなく、Excel チームから口止め料をもらっているわけでもありません (Excel チームからそのような申し出があれば、喜んで検討したいところですが)。理由は、Excel が本当に優れたソフトウェアだからであり、使うたびにすばらしい機能に出会うからです。たとえば、次のスプレッドシートを検討してみましょう。

Microsoft Excel

ご覧のとおり、列 A には都市名と州名が一緒に入っています。これは Excel では一般的なことです。1 つの列に個人の氏名 (Myer, Ken) やユーザー名と部署 (Ken Myer, Finance) が記録されたりもします。一般に、これはスプレッドシートのデザインとして優れているとは言えません。なぜでしょうか。たとえば、サンプルのスプレッドシートにこれらのエントリが数百件あり、このデータを州別に並べ替えるとします。このように都市名と州名が同じ列にあると、州別に並べ替えることはほとんど不可能です。したがって、都市名と州名を次のように設定し、2 つの列に分ける必要があります。 **

Microsoft Excel

残念ながら、元のデータはこのようになっていません。では、どうすることもできないのでしょうか。

いえ、そんなことはありません。Excel には [区切り位置] という機能があります。この機能で都市や州などの列データを取得し、2 つの列 ("都市" 列と "州" 列) に変換することができます。 ** ** ** この機能の動作を確認するため、次の短いスクリプトを実行してみてください。

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)

objExcel.Cells(1, 1).Value = "Olympia, WA"
objExcel.Cells(2, 1).Value = "Salem, OR"
objExcel.Cells(3, 1).Value = "Boise, ID"
objExcel.Cells(4, 1).Value = "Sacramento, CA"

Set objRange = objExcel.Range("A1").EntireColumn
Set objRange2 = objExcel.Range("B1")

objRange.TextToColumns objRange2,,,,,,TRUE

さて、このスクリプトを実行すると、スプレッドシートは次のようになります。

Microsoft Excel

なかなか便利だと思いませんか。列 B に都市が入力され、列 C に州が入力されました。このスプレッドシートを完成させるために必要なことは、スクリプトの最後に次の 2 行のコードを追加して、不要になった列 A をプログラムで削除するだけです。

Set objRange = objExcel.Range("A1")
objRange.EntireColumn.Delete

では、この処理の詳細を説明します。このスクリプトでは、まず Excel.Application オブジェクトのインスタンスを作成し、Visible プロパティを True に設定します (この設定を行わないと、Excel は非表示ウィンドウで実行され、目で見て確認することができません)。次に、ブックと空白のワークシートを追加します。以上の処理を次の 4 行のコードで行います。

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)

次に、データをセル A1 ～ A4 に追加します。

objExcel.Cells(1, 1).Value = "Olympia, WA"
objExcel.Cells(2, 1).Value = "Salem, OR"
objExcel.Cells(3, 1).Value = "Boise, ID"
objExcel.Cells(4, 1).Value = "Sacramento, CA"

これで、サンプル スプレッドシートを使用できるようになりました。ここからが本題です。

まず、Excel の Range オブジェクトのインスタンスを 2 つ作成します。1 つのインスタンス (オブジェクト参照は objRange) では列 A のすべてのセルを囲みます。そのためには、セル A1 を開始点として指定し、EntireColumn プロパティを使用して列 A のすべてのセルを選択します。この最初の範囲を作成するコード行を次に示します。

Set objRange = objExcel.Range("A1").EntireColumn

この 1 つ目の範囲は、既存データが存在する列を表します。もう 1 つの範囲のオブジェクト参照には objRange2 というタイトルを付けました。この範囲は、変換したデータを配置するスプレッドシート内の位置を示します。この範囲に指定する必要があるセルは 1 つのみです。Excel で必要に応じて自動的に範囲が広げられ、変換したすべてのデータが貼り付けられるようになります。

Set objRange2 = objExcel.Range("B1")

後は、TextToColumns メソッドを呼び出して適切なパラメータを渡すだけです。このメソッド呼び出しは奇妙に思われるかもしれませんが、これについては後述します。

objRange.TextToColumns objRange2,,,,,,TRUE

コンマの連続は何を意味するのでしょうか。キーボード上で眠ってしまったためではありません (少なくとも今回は)。多くの Office メソッドと同様、TextToColumns には順番に渡す必要がある複数のパラメータがあります。 ** パラメータに既定値を使用する場合は、この例のように、パラメータの値を入力する必要はありませんが、コンマを入力しなければなりません。 ** サンプル コードでは、TRUE は第 7 パラメータであり、これが第 7 パラメータであることを Excel に通知する唯一の方法として、空白のパラメータとコンマを指定します。これを次のように記述したとします。 **

objRange.TextToColumns objRange2,TRUE

この場合、TRUE は第 2 パラメータと解釈され、スクリプトは予想どおりに動作しません。このように、コンマの連続にはそれなりの理由があります。

TextToColumns メソッドに使用できるパラメータは数が多すぎるので、そのすべてについて説明することは控えます。TextToColumns には、列の分割方法を多様に制御する機能 (コンマ区切りでの指定、タブ区切りでの指定など) だけでなく、分割後の列の書式を事前定義する機能もあります。詳細については、MSDN の Microsoft Excel 用の「VBA Language Reference」(英語) を参照してください。

ここでは、サンプル コードの動作についてのみ説明します。まず、列 A (objRange) に対してメソッドを呼び出します。列 A にはデータの初期セットが存在します。貼り付け先の範囲 (objRange2) を最初のパラメータとして渡し、以下の項目には既定の設定を使用します。

  • DataType - 区切り文字には既定値を使用していますが、代わりに固定長のデータ型を指定することも可能です。

  • TextQualifier - テキスト修飾子には各文字列を囲む二重引用符 ("Olympia, WA" など) を指定します。ここでは不要なので、このパラメータは空白のままにしておきます。

  • ConsecutiveDelimiter - このパラメータは、,, などの連続した区切り文字を 1 つの区切り文字として扱うかどうかを Excel に通知します。このパラメータも不要なので空白のままにしておきます。

  • Tab - タブを区切り文字として使用するかどうかを Excel に通知します。ここではコンマを区切り文字として使用するので、既定値の FALSE のままにしておきます。

  • Semicolon - Tab の場合と同様、FALSE のままにしておきます。

残るは第 7 パラメータの Comma です。コンマを区切り文字として使用することを Excel に通知するため、このパラメータの値を TRUE に設定します。Comma の後にもパラメータがありますが、それらについては既定値を使用することにしました。したがって、以降のパラメータはメソッド呼び出しでは省略します。

またしても、Excel はいざというときに威力を発揮してくれました。Microsoft Excel が多大な努力と成果の賜物であることがわかります (Excel チームの諸君、お礼の支払いは Scripting Guys まで ** よろしく)。