取り込んだデータに列を追加した後データ更新をすると、数値がずれる？
Excelの「データの取得」を使って取り込んだデータに関数や数値などの
列を追加した後、データを更新してみたらデータの位置が合わなくて
困ったことはありませんか？
取り込んだデータをそのまま使う時はあまり関係しないのですが、
取り込んだデータに列を追加した後、元データを一部削除したりすると
「外部データのプロパティ」によって、更新したときの挙動が変わります。
Excelの画面でいうと、この部分の設定の話です。
今回の記事では、この設定の違いにより、データ更新時に隣接しているデータが
どのように動くのかを見ていきます。
なお、「外部データのプロパティ」のうち、
「列の並び替え/フィルター/レイアウトを保持する」をオンにした状態での
検証となります。
取り込んだデータに行列を追加した後、テーブル更新時に数値が
上手く整わなくて困っている場合は、この設定を変えることで解決できる
かもしれません。
実験！プロパティの違いによる変化を実際に見てみる
プロパティの違いによるデータ更新の動きの違いを実際に見ていきます。
今回の設定は次の通りです。
取り込み元データになるのはこのようなシンプルな表です。
この表を「データの取り込み」した後、データを１列追加します。
追加した列のデータは数式ではなく、数値です。
（数式の場合、「列の並び替え/フィルター/レイアウトを保持する」をオンに
していれば自動的に列全体に数式が適用されます）
表の黄色部分が取り込んだデータ、緑部分が取り込んだ後に追加したデータです。
この状態で元データの行を追加・削除した時に、
更新した後のデータがどのように動くのか見ていきます。
プロパティの違いによるデータ更新の違い
設定１：新しいデータのセルを挿入し、使用されていないセルを削除する
元データに行を追加した場合
元データのA10の下に1行追加し、データ更新しました。
元データに追加された行の分、取り込み後のデータに追加した列の
数値がずれ込んでいます。
X20までは元々の順番ですが、元々X21だったセルが空白となっています。
元データから行を削除した場合
元データのA11～14の行を削除しました。
追加のときと同じように、元データから削除された行の分、
取り込み後のデータに追加した列の数値がずれ込んでいます。
X16までは元々の順番ですが、X17～20までが削除されています。
追加でも削除でも、取り込んだデータに追加した列の最終セルのデータは
維持されているようですね。
削除の場合は、取り込みデータのセルを削除した後、隣接データについても行の末尾
（最終セルを除く）から削除するって感じでしょうか。
ちょっと挙動がわかりにくいですね。(^_^;)
設定２：新規データの行全体を挿入し、使用されていないセルはクリアする
元データに行を追加した場合
さきほどと同じように、元データのA10の下に1行追加しデータ更新しました。
「新しいデータのセルを挿入し、使用されていないセルを削除する」と異なり、
取り込み後のデータに追加した列の数値はズレず、元データが追加された分、
最終セルに空白ができています。
元データから行を削除した場合
さきほどと同じように、元データのA11～14の行を削除しました。
行の追加と同じように、取り込み後のデータに追加した列の数値はズレていません。
しかし、元データが削除された分、追加した列の数値X18以降も削除されています。
セルが削除されているわけではないので、更新前の書式設定などは残っています。
設定３：既存のセルを新規データで上書きし、使用されていないセルはクリアする
元データに行を追加した場合
元データのA10の下に1行追加しデータ更新しました。
この場合の挙動は
「新規データの行全体を挿入し、使用されていないセルはクリアする」と同じですね。
元データから行を削除した場合
これまでと同じように、元データのA11～14の行を削除して更新しました。
この場合の挙動も
「新規データの行全体を挿入し、使用されていないセルはクリアする」で
行を削除したときと同じです。
追加・削除ともに
「新規データの行全体を挿入し、使用されていないセルはクリアする」と
変わらない結果でした。
「新規データの行全体を挿入し、使用されていないセルはクリアする」
「既存のセルを新規データで上書きし、使用されていないセルはクリアする」には
それほど差がないのかもしれません。
まとめ
今回は、Excelで外部データを取り込んだ後、「外部データのプロパティ」の
違いによって、更新したときに数値がどのように動くのかを見てみました。
「新しいデータのセルを挿入し、使用されていないセルを削除する」の場合
- 元データが追加された場合、追加された行の分、隣接セルの範囲が
伸びていく。ただし、最終セルの数値は固定。
- 削除の場合、取り込みデータのセルが先に削除され、隣接セルの数値も
元データが削除された分削除される。ただし、最終セルは固定。
「新規データの行全体を挿入し、使用されていないセルはクリアする」の場合
- 元データが追加された場合、追加された行の分、隣接セルの範囲が
伸びていく。隣接セルの数値に変化はない。
- 削除の場合も追加と同様。
「既存のセルを新規データで上書きし、使用されていないセルはクリアする」の場合
- 元データの追加、削除ともに「新規データの行全体を挿入し、使用されていないセルはクリアする」と同じ。
「新しいデータのセルを挿入し、使用されていないセルを削除する」の場合、
データ取り込み後に追加した列のデータが、元データの構成にかなり
引っ張られてしまいます。
もし数式ではなく数値を付与する場合は、他の設定のほうが
わかりやすいかもしれません。
本当は元データに合わせて数値もバッチリハマってくれると嬉しいのですが、
そもそもクエリで取り込んだデータに数値をくっつけるというのがあまり
一般的ではないので、仕方がないですね。
ちなみにExcelのデータ取り込みについては以前、外部データのプロパティで
「列の幅を～」というオプションを外すことによって、更新の度に
列幅が変わってしまうことを防ぐことをご紹介しました。
もしよかったらこちらも読んでみてください。
帳票にデータ取り込みを活かす時には必須の設定です。
