Microsoft Excelは、いわずと知れたネ申ツールで、本来の表計算だけでなくワープロからゲーム作成までいろいろな目的に使われています。データ加工も可能で、方法も様々なものが用意されていて玄人心をくすぐられます。
ここで触れている「データ加工」とは、スプレッドシート上で文字列や数値などの値を入力したり加工していくことを指します。主なものだけでもオートフィル、フラッシュフィル、関数、マクロ・VBAの利用、などいろいろな選択肢があります。関数も含めたプログラミング的な機能を使うと高度なことがいろいろと自動化できますが、今回は、テキストとクリップボードを駆使したノンプログラミング的な手法で意外とできることをご紹介しようと思います！
利用ツール
- Excel
- テキストファイルウィザード
- クリップボード
- メモ帳 (お気に入りのテキストエディタでも可)
Excelデータをメモ帳にコピペして貼り付けなおしテキストファイルウィザードでデータ加工する技
Excel上のデータをいったんメモ帳などのテキストエディタに貼り付けると、貼り付けのオプションから「テキストファイルウィザード」を起動できます。
ちなみに、Excel上のデータをCtrl+Cでクリップボードにコピーすると、下の図の下段のようにExcelアイコンで表示され、Excel形式 (もしくはHTML形式)でクリップボードにデータがあることがわかります。
一旦、Excel上のデータをテキストエディタにコピーしてそのデータを再びクリップボードにコピーすると、今度は図の上段のようにテキストアイコンで表示されます。この時はテキストとしてクリップボード上にデータが存在します。
操作方法の概要
Excel上のデータをCtrl+Cでクリップボードにコピー、それをテキストエディターに貼り付け、そのデータをテキストエディタ上でCtrl+Cでクリップボードにコピーして、それをExcelに貼り付けると、以下の通りはりつけのオプションから「テキストファイルウィザードを使用...」メニューが利用できます。(※ Excelのセルをコピーしたときに出る点線の選択範囲はいったんESCキーを押すなどして解除しておいてください。そうしないとExcel上の選択範囲からの貼り付けになってしまう場合があります)
ウィザードを起動すると、1ページ目は区切り文字を選択するか、固定長データなのかを選択する画面です。
2ページ目はどちらのオプションを選択したかによって異なります。区切り方を選択します。
3ページ目はデータ形式を選択する画面です。この後「完了」ボタンを押すと終了です。
例題1: 米国の日付形式を日本の形式に変換する
症状
SFDCなどの米国のソフトウェアからエクスポートしたデータは、"7/1/2019"のように年が最後になっているパターンがよくあります。このままだとExcel上で日付形式ではなく文字列として認識されてしまいます。
ちなみに、最近のバージョンのExcelだと、日付形式だとフィルターは割と便利で年や月単位でのフィルターができるようになっています。
また、Excel上で日付形式として認識されているかどうかは、列の幅を狭めてみたときに "#####"という表示になるかどうかでもわかります。
文字列として認識されている場合は、以下のような表示となります。フィルターを表示すると、ひとつひとつのセルの中身が文字列で一覧になります。(しかも、順番は7月からではなく先頭文字の文字コード順になってしまうため10月の方が先に来ます。)
列の幅を狭くしても、"#####"という表示にはならずに中の文字列がそのまま表示されます。
こうなってしまうと、後からいくら「セルの書式設定」ダイアログボックスで米国の日付形式 (MM/dd/yyyy)をセルに適用しても、日付形式には認識されません。これで困っている方も多いのではないでしょうか。
解決策
Excel上の対象範囲を選択してメモ帳にいったんコピペし、その後メモ帳の内容をコピーしてExcel上の元の対象範囲上に貼り付けます。そうすると、貼り付けのオプションからテキストファイルウィザードを起動できます。
ウィザード上で「次へ」ボタンを2回押して、ステップ3で「日付」を選び、順番に「MDY」を選びます。これで「完了」ボタンを押します。
すると、yyyy/DD/ddの順番でデータが入ります。フィルターを見てみると日付としてきちんと認識されていることがわかりますね！
例題2: 日付形式から年月日を抜き出す
通常はYear関数、Month関数、Day関数とかを使うのでしょうが、ここではノンプログラミングな方法を使います。
例題1と同様に、日付データの対象範囲をメモ帳にコピペして貼り付けなおして起動するテキストファイルウィザードで、区切り文字を"/"にすると、3つの列にデータを分けることができます。
完了すると、以下の通りに貼り付けれます。
例題3: スペースで区切られた姓名を姓と名に分ける
これはもう簡単ですね。姓名の列をメモ帳にコピペしてそれをExcelにコピペしなおします。
テキストファイルウィザードでは、スペース区切りにして完了ボタンを押します。
するとこんな感じで貼り付きます。
終わりに
いかがでしたでしょうか。いままでの例題のほかにも応用できる場面はきっといろいろあるはずです。テキストエディタとクリップボードを駆使してExcelのデータ加工をより便利にしてみてください。