"取得と変換" を使えば、"セル内改行" を行に分割することができます
- 「複数のデータを1つのセルにカンマで区切って入力」してあるデータを「データ別に1つのセルにする」作業を手作業でするのは大変です
- 1."県別" に、1つのセルに入力してある"市の名前"を別々のセルに分割
- 2."セル内改行"を「データの区切り」に使っている場合
- "取得と変換" で、「1つのセルにまとめられた」データを分割する
- Power Query で作成された「ワークシート」は "Power Query" とリンクしている
「複数のデータを1つのセルにカンマで区切って入力」してあるデータを「データ別に1つのセルにする」作業を手作業でするのは大変です
"取得と変換" を使えば、1つのセルにまとめて入力してあるデータを "1つのデータ" を "1つのセル" に分割することができます。
1."県別" に、1つのセルに入力してある"市の名前"を別々のセルに分割
県ごとに1つのセルにカンマで区切って入力してある「市名」を1つのセルに1つの市名が入るようにします。
関連付けられた「セル」と連結して分割する
データを分割する際に、同じ列にあるセルと関連付けて分割することができます。
「岡山県」の行にある "市名" には「岡山県」のセルを表示
同じ行にある「県名」と関連付けた形で分割することができます。
「岡山県」にある「市の名前」を分割する際に、「岡山県」のセルをコピーして付加することができます。
営業部別の「職員名」
"部署別" に "職員名" をまとめて入力してある「一覧表」を分割することで職員のデータベース用のDATAに使える形式にすることができます。
2."セル内改行"を「データの区切り」に使っている場合
取得と変換を使えば、厄介な問題のデータを区切る方法に"セル内改行(Alt+改行)" を使っているデータを "分割" することができます。
セルの幅が広くなりすぎることを防ぐための "セル内改行" を解除するのは簡単ですが、"データの区切りのため" の "セル内改行" を解除してデータをバラバラにすることは "VBA" を使っても難しい作業です。
「セル内改行( Alt + 改行 )」を削除する - 事務作業の省力化や資料作成に役立つ、Excelの使い方を紹介
取得と変換は、"セル内改行" を区切り文字「#(lf)」として認識
「職員名」を区切る方法として"セル内改行" が使われている場合、それをバラバラにすることは "VBA" を使ったとしてもかなり困難な作業になります。
取得と変換を使えば、"セル内改行" を「区切り文字 『#(lf)』」として認識し、その記号を基準にして分割します。
"取得と変換" で、「1つのセルにまとめられた」データを分割する
1.1つのセルにまとめられた「市名」を分割する
「県名」のセルの隣の "1つのセルに「市名」がまとめられている" 表の「市名」を分割して、「県名」の隣に「1つの市名」が表示される「表」に変換します。
「取得と変換」を使う(その1)複数のファイル(CSV)を結合する - 事務作業の省力化や資料作成に役立つ、Excelの使い方を紹介
"テーブルまたは範囲から" のコマンドを実行
データタブにある「テーブルまたは範囲から」のコマンドを実行すると、「表」が "テーブル化" されます。
「表」が "テーブル化" されていなくても、「自動で表がテーブル化」されます。
- 「表」のどこか1つのセルを選択する
- 「データ」タブを選択
- 「テーブルまたは範囲から」をクリック
- "テーブルの作成" ダイアログで、テーブルに変換する範囲が正しく選択されているのを確認(※ 自動で選択される)
- 「先頭行をテーブルの見出しとして・・・」にチェックを入れる
- 「OK」ボタンをクリック
表を「テーブル化」して作業効率を上げる - 事務作業の省力化や資料作成に役立つ、Excelの使い方を紹介
"Power Query エディター" が立ち上がります
"取得と変換" の機能は、外部プログラム(アドイン)の「Power Query」をExcelの "機能" として簡単に使えるようにしたものです。
「Power Query」は、Excelとは別のプログラムとして動作するので注意が必要です。
作業の流れは、取得と変換の機能でExcelから「データ」が "Power Query" に読み込ま(送り込ま)れ、データ返還後に "Power Query" でExcelの「ワークシート」が作成されます。
- 変換(分割)する、列(項目)の「市」の項目名の部分を "クリック" して選択する(※ 列(項目)全体の色が変わって選択状態になる)
- "Power Query" エディタで、「変換」タブを選択
- 「列の分割」をクリック
- プルダウンリストから、「区切り文字による分割」を選択
※ 変換する「列」を選択する際に、「項目名」以外を "クリック" すると「項目(列)全体」が選択されないので注意
"区切り記号による列の分割" ダイアログで、区切り文字の「 、」を指定
"区切り記号による列の分割" ダイアログで、分割するためのキーとなる区切り文字を指定します。
問題なければ自動的に、区切り文字として使用している「 、」が選択されます。
- 区切り記号「 、」が正しく選択されているのを確認
- 折りたたまれている「詳細設定オプション」の部分をクリックして展開
- "分割の方法" を「列」に指定
- 「OK」ボタンをクリック
※ デフォルトでは "分割方法" は「行」になっています
分割された「表」を、ワークシートにしてExcelに戻す
「表(項目)」が正しく分割されているのを確認して、ワークシートに変換してExcelに書き込みます。
※ 作業(変換)内容は、適用したステップのウインドに「ステップ」として表示される
- 「ホーム」タブを選択
- 「閉じて読み込む」をクリック
- プルダウンリストから「閉じて読み込む」を選択
"Power Query" が終了し、Excelに戻ります
"Power Query" が終了(閉じて)、Excel画面に戻ります。
「市名」が分割(変換)された、新しいワークシートが追加されています。
画面の右端に「クエリと接続」ウィンドゥが表示され、作成されたワークシートが「テーブル2」の名称で表示され、そこをクリックするとPower Queryの画面に戻り編集(修正)することができます。
2."セル内改行"を「データの区切り」に使っているセルのデータを分割
セル内改行は、区切り記号「 #(if) 」として認識されます。
取得と変換で、この区切り記号を使ってデータを分割します。
"取得と変換"で、区切り記号「 #(if) 」でデータを分割
分割したい項目(列)を指定すると、その項目で使用されている「区切り記号」が自動で表示されます。
区切り記号「 #(if) 」で、分割されました
Power Query で作成された「ワークシート」は "Power Query" とリンクしている
Power Query で作成した「ワークシート」は、"Power Query" とリンクしているので、"取得と変換" の作業内容を後からやり直す(変更)することができます。
- "元のデータ" のどこか1つのセルを選択
- 「クエリ」タブを選択
- 「編集」をクリック
元の「表(データ)」の編集(追加)内容が反映される
Power Query で作成された「表(ワークシート)」は、Power Query の「ステップ」とリンクしています。
変換(分割)の元の「表(データ)」を修正(追加・削除)して、「クエリタブ」で "更新" のコマンドを実行すると修正内容が反映されます。
元の「表」の「広島県の『市名』に『修正』を追加」
"クエリタブ"で、「更新」を実行
クエリタブの「更新」コマンドを実行すると、元の「表」の修正内容が反映されます。
- 項目を "変換(分割)"したワークシート「テーブル10」を選択
- 「クエリ」タブを選択
- 「更新」をクリック
「部署名一覧表」を "データ形式" に変更
「部署名一覧表」などの表は、「見やすい様式」で作成すると "データベース的" な使い方をする時の「データ」としては使えません。
「1つのセルにまとめられた『職員名』をバラバラにする」ことで、「データ」として使える形式にすることができます。
"取得と変換" は、今回の「データを分割」する以外にも、「複数の表を結合」したり、「特定の項目だけを結合」したりと様々な「変換作業」ができる機能です。
外部プログラム(アドイン)を使うので難しい感じがしますが、最新のバージョンではとても使いやすくなっています。
マクロを使わなければならないような複雑な作業も、Excelの標準機能のような簡単な操作で使うことができます。
- 「複数のデータを1つのセルにカンマで区切って入力」してあるデータを「データ別に1つのセルにする」作業を手作業でするのは大変です
- 1."県別" に、1つのセルに入力してある"市の名前"を別々のセルに分割
- 2."セル内改行"を「データの区切り」に使っている場合
- "取得と変換" で、「1つのセルにまとめられた」データを分割する
- Power Query で作成された「ワークシート」は "Power Query" とリンクしている