データを分割するデータの区切り位置機能

2017.04.24文字列操作その他

様々なデータは、Excelで扱うことができます。
ネット上の一覧表はコピーして貼り付けしたり、csv形式のファイルは開くだけで利用できます。
しかし、開いてみたら複数列に分かれてほしかったものが1列に纏まってしまうことがあります。
そのようなデータを開いた後で分割する「区切り位置」機能について紹介します。
実際に操作する例題のファイルがあります。こちらからダウンロードして操作してみましょう。

区切り位置機能の概要

データをExcelで開いたときや、取り込んだ時にセルに分かれずに1列のデータになっている場合があります。

次のデータの例は、A列に都道府県、事業所数、従業員数、総売上高が同じ位置(固定長)で始まるように空白で区切られ、記録されています。

空白区切りの例

 

見やすいものの、Excelとして扱う場合には、1セルごとにデータが分かれていなければなりません。

次のデータの例は、A列に都道府県、事業所数、従業員数、総売上高が@の記号で区切られ、記録されています。

記号区切りの例

 

このような記号やある文字で区切られている例もあります。

これらの1列に入ってしまったデータを複数列に分けることができるのが[区切り位置]の機能です。

区切り位置の操作

では、実際に操作してみましょう。
操作の手順は大きく分けると、各データが同じ位置で始まるデータなのか、記号で区切られているデータなのかによって変わります。

空白区切りの例

ダウンロードしたファイルの「空白区切り」シートをご覧ください。
同じ位置(固定長)で始まるように区切られているデータです。
まず、実際に区切り位置の操作する前に注意しておきたいことがあります。
今回はA列をAからD列に分割するのですが、分割した際にB列以降のデータを上書きしてしまうので、あらかじめBからD列に1列挿入し、もともとB列だったデータは上書きされないようにE列に移動しておきましょう。

分割前準備

 

では実際に区切り位置機能の操作をします。
分割するA列を選択します。

固定長データA列選択

 

[データ]タブの[区切り位置]を選択します。

区切り位置

 

次のようなダイアログボックスが出てくるので、[スペースによって右または左に揃えられた固定長フィールドのデータ]を選択し、[次へ]のボタンをクリックします。

固定長データ区切り位置

 

区切る位置が、自動で認識され、次の図の例だと11、24、38文字目で区切り線が入っています。
今回は自動で認識された位置で構いませんが、もし、位置が間違っている場合は、この区切り線をクリックすることで解除、線のないところをクリックすることで追加、ドラッグすることで移動することができます。

固定長データのフィールドの幅

 

ここで、[完了]ボタンを押せば、分割ができます。

固定長データ分割後

記号文字での区切りの例

ダウンロードしたファイルの「@区切り」シートをご覧ください。
各データが@記号で区切られているデータです。
この場合も、区切り位置の操作をするとB列以降が上書きされますので、あらかじめBからD列に列を挿入しておきましょう。
A列を選択後、[データ]タブの[区切り位置]を選択します。
次のようなダイアログボックスが出てくるので、今度は[カンマやタブなどの区切り文字によってフィールドことに区切られたデータ]を選択し、[次へ]のボタンをクリックします。

区切られたデータ

 

いくつかの[区切り文字]にチェックが入っていると思いますが、一番下の[その他]のみのチェックを入れ、今回は半角の「@」で区切られているので、[その他]のボックスの右側のボックスに半角の「@」を入力します。

区切り文字区切り位置

 

[完了]ボタンを押せば、分割ができます。

区切り文字データ分割後

区切り位置の便利な使い方

区切り位置機能を応用したテクニックを一つ紹介します。
ダウンロードしたファイルの「日付データ」シートをご覧ください。
日付と売上が「-」の区切り文字で結合されたものがA列に入力されています。
このA列を分割しようと思うのですが、日付のデータをよく見ると日付ではなく「20,170,401」という数字にも見えます。Excelは数字に見えるものは自動で数字と認識してしまうので、そのまま分割すると、日付データではなくなってしまいます。
区切り位置で日付データなどのデータの種類を指定することがます。
実際に操作してみましょう。
まずA列を選択し、[データ]タブの[区切り位置]を選択します。
[カンマやタブなどの区切り文字によってフィールドことに区切られたデータ]を選択し、[次へ]のボタンをクリックします。
[区切り文字]は一番下の[その他]のチェックを入れ、今回は半角の「-」で区切られているので、[その他]のボックスの右側のボックスに半角の「-」を入力し、ここで、[完了]ボタンを押さずに[次へ]のボタンをクリックします。

データの種類の設定

 

この画面が区切り位置設定の3番目で最後の設定画面なのですが、ここでは、各列のデータの種類を設定できるのです。

データの種類の設定

 

日付の列を選択し、[列のデータ形式]から[日付]を選択し、今回は「年月日」の並びのデータですので、[日付]の右側のボックスから「年月日」のそれぞれ英語の頭文字をとった「YMD」を選択します。

データの日付の指定

 

売上の列はデータの種類が[標準]でも数値として認識します。

数値データから日付データへの変換結果

 

ちなみにここで[列のデータ形式]を削除するに設定するとその列は分割先に出力されませんので、もし、無駄なデータがある場合は利用するとよいでしょう。
[完了]ボタンを押せば、分割ができA列は数値ではなく日付として取り込むことができました。

この日付としての取り込みは、分割する必要がない1列の「20170401」と入力されているセルに対しても、そのセルや列を選択し、[データ]タブの[区切り位置]、[次へ]のボタンを2回クリックして3番目のデータ種類の設定画面で日付形式に設定することで、変換が行えます。
E1からE4の範囲にある数値データを、区切り位置機能を使って日付に変換してみましょう。

1列だけの数値データから日付データへの変換結果

区切り位置機能の注意点

区切り位置機能を使う上で、注意したいことがあります。
「注意しなければならないデータ」シートをご覧ください。
A列、D列、G列に1から始まる番号と、様々な内容がカンマ区切りで結合されています。

注意点の分割前

 

内容は、A列は「=」などの算術記号で始まっている計算式のようなものです。
D列は一見すると日付に見えますが「30分の4」のような分数のつもりなのです。
G列は数値なのですが、桁区切りのカンマが入っています。

想定外の分割の結果

では、実際に上の説明の手順を参照し、A列をカンマ区切りでAとB列に、D列をカンマ区切りでDとE列に、G列をカンマ区切りでGとH列に、分割してみましょう。

想定外の結果

 

A列に対するB列は計算式となり、その答えが出ました。セルの始まりの文字が「=」以外でも算術記号の場合にはExcelは計算式と捉えます。
D列に対するE列は日付となってしまいました。
G列は桁区切りのカンマまで区切り文字として認識され全3列に分割してしまいました。

対処方法

これらは、それぞれ対処方法があります。
「注意点の対策」シートをご覧ください。

  • A列の計算式になってしまったデータは、3番目のデータ種類の設定画面で「文字列」に設定すればそのまま文字列として取り込むことができます。
    実際にA列をカンマ区切りでAとB列に分割し、B列が「文字列」になるように分割してみましょう。

文字列取り込みとして対処結果
  •  D列の日付になってしまったデータは、次の手順での対処になります。
    実際に操作してみましょう。
    3番目のデータ種類の設定画面で「文字列」に設定し、そのまま文字列として取り込みます。

分数取り込み対処1

 

さらにE列に分割された文字を半角の「/」の区切り文字で分割します。

分数取り込み対処2

 

E列とD列に分割されたデータを割り算で計算することで数値にすることができます。

分数取り込み対処3

 

最後に表示形式で、分数の表示形式や、ユーザー定義で分数の表示形式である「?/30」や「?/?」と設定します。

分数取り込み対処4

 

分数表示になります。内容の表示が右揃えになっているので文字ではなく数値になっていることがわかります。

分数取り込み対処結果

 

桁区切りのカンマまで区切り文字になったものは、もともとのデータの作り方から注意が必要なのですが、カンマ区切りのデータで桁区切りを扱う場合は「" "」で囲んであればExcelは囲んだ内容を1つの数字と捉えます。
実際にI列に数値を「" "」で囲んだデータを用意しました。IとJ列に分割してJ列にきちんと分割されることを確認しましょう。

桁区切りカンマ区切りの対処結果

まとめ

区切り位置機能は、もともとのデータがしっかりしていれば、とても使いやすい機能です。
80から90年代は桁区切りされたうえでカンマ区切りが「" "」がないようなデータも数多くありましたが、今日においては、そのようなことはほとんどなくなりました。
区切り位置機能は分割を失敗しても元に戻る機能が使えますので、気軽にデータを分割してみておかしかったら元に戻り、原因を考えるというような使い方がお勧めです。

Back to top