Excelハック

[Excel]取り込んだデータに列を追加してデータ更新したときの、外部データのプロパティによる動作の違い

Excelハック
この記事は約5分で読めます。

取り込んだデータに列を追加した後データ更新をすると、数値がずれる?

Excelの「データの取得」を使って取り込んだデータに関数や数値などの
列を追加した後、データを更新してみたらデータの位置が合わなくて
困ったことはありませんか?

取り込んだデータをそのまま使う時はあまり関係しないのですが、
取り込んだデータに列を追加した後、元データを一部削除したりすると
「外部データのプロパティ」によって、更新したときの挙動が変わります。

Excelの画面ではこの部分の設定の話です。

今回はこの設定の違いにより、データ更新時に隣接しているデータが
どのように動くのかについて書きます。

なお、「外部データのプロパティ」のうち、
「列の並び替え/フィルター/レイアウトを保持する」をオンにした状態での
検証となります。

取り込んだデータに行列を追加した後、テーブル更新時に数値が
上手く整わなくて困っている場合は、この設定を変えることで解決できる
かもしれません。

実験!プロパティの違いによる変化を実際に見てみる

プロパティの違いによるデータ更新の動きの違いを実際に見ていきます。

今回の設定は次の通りです。
取り込み元データになるのはこのようなシンプルな表です。

この表を「データの取り込み」した後、データを1列追加します。
追加した列のデータは数式ではなく、数値です。
(数式の場合、「列の並び替え/フィルター/レイアウトを保持する」をオンに
していれば自動的に列全体に数式が適用されます)

表の黄色部分が取り込んだデータ緑部分が取り込んだ後に追加したデータです。

この状態で元データの行を追加・削除した時に、
更新した後のデータがどのように動くのか見ていきます。

プロパティの違いによるデータ更新の違い

設定1:新しいデータのセルを挿入し、使用されていないセルを削除する

元データに行を追加した場合

元データのA10の下に1行追加し、データ更新しました。

元データに追加された行の分、取り込み後のデータに追加した列の
数値がずれ込んでいます。
X20までは元々の順番ですが、元々X21だったセルが空白となっています。

ひだりが更新前、右が更新後

元データから行を削除した場合

元データのA11~14の行を削除しました。

追加のときと同じように、元データから削除された行の分、
取り込み後のデータに追加した列の数値がずれ込んでいます。
X16までは元々の順番ですが、X17~20までが削除されています。

追加でも削除でも、取り込んだデータに追加した列の最終セルのデータは
維持されている
ようですね。

削除の場合は、
取り込みデータのセルを削除した後、隣接データについても行の末尾
(最終セルを除く)から削除するって感じでしょうか。
ちょっと挙動がわかりにくいですね。

設定2:新規データの行全体を挿入し、使用されていないセルはクリアする

元データに行を追加した場合

さきほどと同じように、元データのA10の下に1行追加しデータ更新しました。

「新しいデータのセルを挿入し、使用されていないセルを削除する」と異なり、
取り込み後のデータに追加した列の数値はズレず、元データが追加された分、
最終セルに空白
ができています。

元データから行を削除した場合

さきほどと同じように、元データのA11~14の行を削除しました。

行の追加と同じように、取り込み後のデータに追加した列の数値はズレていません。
しかし、元データが削除された分、追加した列の数値X18以降も削除されています。

セルが削除されているわけではないので、更新前の書式設定などは残っています

設定3:既存のセルを新規データで上書きし、使用されていないセルはクリアする

元データに行を追加した場合

元データのA10の下に1行追加しデータ更新しました。

この場合の挙動は
「新規データの行全体を挿入し、使用されていないセルはクリアする」と同じですね。

元データから行を削除した場合

これまでと同じように、元データのA11~14の行を削除して更新しました。

この場合の挙動も
「新規データの行全体を挿入し、使用されていないセルはクリアする」で
行を削除したときと同じです。

追加・削除ともに
「新規データの行全体を挿入し、使用されていないセルはクリアする」と
変わらない結果
でした。

「新規データの行全体を挿入し、使用されていないセルはクリアする」
「既存のセルを新規データで上書きし、使用されていないセルはクリアする」には
それほど差がないのかもしれません。

終わりに

今回は、Excelで外部データを取り込んだ後、「外部データのプロパティ」の
違いによって、更新したときに数値がどのように動くのかを見てみました。

プロパティの違いによるまとめ

「新しいデータのセルを挿入し、使用されていないセルを削除する」の場合

  • 元データが追加された場合、追加された行の分、隣接セルの範囲が
    伸びていく。ただし、最終セルの数値は固定
  • 削除の場合、取り込みデータのセルが先に削除され、隣接セルの数値も
    元データが削除された分削除される。ただし、最終セルは固定

「新規データの行全体を挿入し、使用されていないセルはクリアする」の場合

  • 元データが追加された場合、追加された行の分、隣接セルの範囲が
    伸びていく。隣接セルの数値に変化はない
  • 削除の場合も追加と同様。

「既存のセルを新規データで上書きし、使用されていないセルはクリアする」の場合

  • 元データの追加、削除ともに「新規データの行全体を挿入し、使用されていないセルはクリアする」と同じ。

「新しいデータのセルを挿入し、使用されていないセルを削除する」の場合、
データ取り込み後に追加した列のデータが、元データの構成にかなり
引っ張られてしまいます。

もし数式ではなく数値を付与する場合は、他の設定のほうが
わかりやすいかもしれません。

本当は元データに合わせて数値もバッチリハマってくれると嬉しいのですが、
そもそもクエリで取り込んだデータに数値をくっつけるというのがあまり
一般的ではないので、仕方がないですね。


ちなみにExcelのデータ取り込みについては以前、外部データのプロパティで
「列の幅を~」というオプションを外すことによって、更新の度に
列幅が変わってしまうことを防ぐことをご紹介しました。

もしよかったらこちらも読んでみてください。
帳票にデータ取り込みを活かす時には必須の設定です。

タイトルとURLをコピーしました