【Excel】1セル1データになっているか？
総務省のルールに沿った表の修正シリーズ1回目
総務省のルールに沿って統計表を修正するシリーズの第一回目のテーマは『1セル1データになっていない表の修正』です。
パワークエリを使った“値の分割”が作業の主軸になっています。
この手の修正作業は、日常業務でもよく使うテクニックですので、ぜひ参考にしてください。
※当ブログの画像はクリックすると拡大表示されます。ダイアログボックスの表示などが見にくい場合は、大きな画像で確認いただけます。
以上で、作業完了です。
本記事での内容は、動画でもご視聴いただけます。
＜目次＞
- 概要
- 例1の表の修正
- パワークエリで開く
- 空白行の削除
- 句読点で値を分割
- 列と行の入れ替える
- カッコで値を分割
- 閉じカッコを削除
- 1行目をヘッダーに昇格
- 必要な値の抽出
- データ型を変更
- 閉じて読み込む
- 例2の表の修正
- スペースで値を分割
- 絶対値に変換
- 閉じて読み込む
- まとめ
1セルに複数のデータを入力しちゃダメ！
総務省が公表した『Excelの統一ルール』では、チェック項目1－2において、“1セル1データになっているか？”がチェックポイントに挙げられています。
作っている本人はそれが見やすいと思っているのかも知れませんが、１セルに複数のデータが⼊⼒されていると、計算や昇順・降順の並べ替え、コピペやグラフ作成など、ありとあらゆる場面で、余計な手作業や処理のためのプログラムが必要となり、データとしての利用価値が下がってしまうので、絶対にやめましょう。
例1の表の修正
例１の表では、セルが結合されており、金額とカッコで括られた年度を示す値が「、」で区切られて、連続で入力されています。
「、」で区切られているだけであれば苦労しませんが、カッコで括られた年度の値を取り出して、別の列に並べなければならないですし、表の縦横を入れ替える必要もあります。
 |
| 例1の表 |
パワークエリで開く
区切り記号で値を分割するだけであれば、標準的なExcelの機能である“値の分割”を使って作業できますが、その他にも色々とやらなければならない事が多いので、パワークエリで作業します。
表を選択して、データタブ、“テーブルまたは範囲から”のアイコンをクリックします。
表を選択して右クリック、“テーブルまたは範囲から…”というメニューからでもデータを取り込むことができます。
データ取得の範囲を確認して、OKを押します。
空白行の削除
パワークエリに取り込むと、自動的にセルの結合が解除され空白行となりますので、削除します。
ホームタブ、行の削除のアイコンをクリックし、“空白行の削除”を選択します。
句読点で値を分割
句読点を区切り記号として、値を分割します。
該当の列を選択して、変換タブ、「列の分割」のアイコンをクリックして、“区切り記号による分割”を選択します。
ウィンドウが開きますので、リストから“‐‐カスタム--”を選択し、テキストボックスに句読点を入力し、「区切り記号の出現ごと」にチェックを入れて、OKを押します。
列と行の入れ替える
目指す表のレイアウトを踏まえ、列と行を入れ替えます。
変換タブの中の“入れ替え”をクリックすると、下図のように変換されます。
パワークエリは縦（列）方向で作業するのが基本スタイルですので、私は作業性が悪い場合、列と行の入れ替えを積極的に使っています。
※先にヘッダーを下げておくなどの配慮が必要です。
カッコで値を分割
金額の値と、カッコで括られた年度の値を分割します。
該当の列を選択して、変換タブ、「列の分割」のアイコンをクリックして、“区切り記号による分割”を選択します。
ウィンドウが開きますので、リストから“‐‐カスタム--”を選択し、テキストボックスに“始まりのカッコ”を入力し、OKを押します。
閉じカッコを削除
分割した値の閉じカッコを削除します。
変換タブの“値の置換”をクリックして、「検索する値」の欄に“閉じカッコ”を入力し、置換後の欄は空欄のままOKを押します。
列をドラッグして左端に移動しておきます。
1行目をヘッダーに昇格
1行目の値は変換タブの“１行目をヘッダーとして使用”のアイコンをクリックして、列名に使います。
併せて、1列目の列名を“年度”に編集しておきましょう。
必要な値の抽出
「出荷額（3列目）」の値から、必要な値を取り出します。
年度の値はカッコで括られているので、カッコを基準にして値を抽出します。
変換タブの「抽出」から、“区切り記号の前のテキスト”を選択し、区切り記号の欄に“始まりのカッコ”を入力し、OKを押します。
データ型を変更
出荷額の列のデータ型（入力値の属性）がテキストのままになっているので、数値属性に変更します。
ヘッダー名の左の「ABC」の表示されたアイコンをクリックして、“整数”を選択します。
変換タブにある“データ型の検出”をクリックして、自動判別させてもOKです。
 |
| マイナスの値をプラスの値に変換できた |
閉じて読み込む
編集作業は終わりましたので、ワークシートに読み込みます。
ホームタブに戻って、“閉じて読み込む”をクリックしてパワークエリを閉じます。
クエリを右クリックし、読み込み先…
テーブル、OKとクリックして…
完了です。
例2の表の修正
例２も1セルに複数の値が入力された表ですが、値の位置を揃えるために複数の␣（スペース）が連続して入力されています。
入力値の長さ（桁数）によって使用される␣の数が異なっているので、このシート作っている風景を想像するとかなりイラっとしますが、修正作業はとても簡単です。
 |
| 例2の表 |
スペースで値を分割
前述と同様の手順でパワークエリにデータを取り込んだら、売上金額と（費用総額）の値を分割します。
列を選択して、変換タブ、「テキストの列」グループにある、「列の分割」アイコンをクリックし、“区切り記号による分割”を選択します。
ウィンドウが開くと、既に「（」（←始まりのカッコ）が表示されていると思いますが、「--カスタム--」をクリックして“スペース”に変更し、「分割」の選択肢の中から、“一番左の区切り記号”にチェックを入れて、OKを押します。
値が分割されると同時にデータ型が自動的に変更され、スペースの残骸が削除され、カッコ内の値がマイナス表示に変換されます。
列名も編集しておきましょう。
絶対値に変換
値がマイナスに表示されてしまったので、正の値に変換します。
該当の列を選択して、変換タブ、「数値の列」グループの「指数」の中から、“絶対値”を選択します。
列のヘッダーを右クリックして、変換、絶対値とクリックしてもOKです。
閉じて読み込む
以上で編集作業は終わりですので、ホームタブ、“閉じて読み込む”をクリック。
クエリを右クリックして、読み込み先。
テーブル、OKとクリックします。
以上で、作業完了です。
まとめ
パワークエリを使って、1セルに複数のデータが入っている表を修正しました。とても簡潔なステップで作業することができたと思います。
値を区切るだけであれば、“区切り位置”で作業することもできますが、値の変換や表レイアウトの修正を伴うのであれば、作業性はパワークエリのほうがいいと思います。
パワークエリはプログラミングが分からなくても、ほぼマウス操作だけでExcelでの処理を自動化できますので、覚えておいて損はありません。
この機会にぜひ挑戦してみましょう！
今回の解説は以上です。
それでは、また！
動画もチェック！
本記事での内容は、動画でもご視聴いただけます。
是非参考にしてください！
#Excel #初心者講座 #値の分割 #置換 #パワークエリ #
コメント