LWP | 可視セルへの貼り付け――見える順番とIDの対応を分けて考える
可視セルへの貼り付け――見える順番とIDの対応を分けて考える
フナさんとシャケモンが、フィルターで見える行と修正値の対応を確かめます。
ストーリー
フナさんは、照合で見つかった3件のエラーを直そうとしています。修正データのIDは分かっていますが、表の並び順は同じとは限りません。シャケモンと、表示・セル位置・データの対応を分けて考えます。表と会話は説明用の例です。
1 見えている3行と、実際のセル位置は違う
図1｜フィルターは表示を絞ります。行を削除して詰める機能ではありません。
2 可視セルを選んでも、対応表はできない
図2｜選択範囲の形と、どのIDへ値を入れるかという対応は別の問題です。
3 同じ3件でも、順番で配ると全部ずれる
図3｜この並びでは3件ともずれます。件数の一致だけでは対応を保証できません。
4 IDで結ぶと、並び順が違っても対応する
図4｜IDで修正値を探し、更新条件で対象を絞ります。
5 一致0件・1件・複数件で、判断を分ける
図5｜XLOOKUPが返した1件と、一意に決まった1件を区別します。
6 照合してから反映し、対象外も確かめる
図6｜更新した行と、変えてはいけない行の両方を確認します。
7 Ctrl＋Rを使うなら、値と対象2列を確認する
図7｜Ctrl＋Rは左端の内容を右へコピーします。ID照合を代行する操作ではありません。
8 方法を選ぶ前に、対応の保証を決める
図8｜IDも順序の保証もない場合は、書き込む前に対応を確認します。
まとめ
表示されている行数が同じでも、修正データと同じ順番とは限りません。共通するIDがあれば、IDの表記と一致件数を確認して修正候補を作り、更新条件を満たす行だけへ反映します。未一致・重複は保留し、反映後は対象外の正常値も確認します。
並べ替えで対応させる場合は表全体を一緒に動かし、件数・順序・元の順序へ戻す方法を確認します。フィルターされた複数領域への操作は、対象環境で小さな範囲から確かめます。
今回の仕掛け
会計の照合経験を持つフナさんが「正常値を残す」「件数だけでは足りない」という条件を整理し、シャケモンがExcelの選択・検索・コピーを分けて説明します。操作名を覚える前に、表示・位置・対応の構造を図で捉える構成です。
今回のソースコードのサンプル
元表には「ID」「元の値」、修正表というExcelテーブルには「ID」「修正値」がある前提です。IDは文字列として表記をそろえ、COUNTIFでの比較とIDの業務上の一致が同じ意味になるデータを使います。
サンプルコード 元の正常値を保持する補助列
01 |
|
|---|
関数の説明
ISNAは元の値が#N/Aかを判定し、COUNTIFは修正表での一致件数を数えます。XLOOKUPは対応する修正値を返します。IFで、対象外の元の値と「要確認」を分けます。
コードの説明
元の値が#N/Aで、一致件数が1件の場合だけ修正値を候補にします。未一致・重複は「要確認」、正常値は元の値を返します。XLOOKUP自体に重複警告の機能はありません。
入力・概要
元表はA01=#N/A、A02=20、A03=#N/A、A04=#N/A。修正表はA04=400、A01=100、A03=300です。XLOOKUPに対応したExcelを使います。Excel 2016・2019では、この例をそのまま使えません。
結果
期待する補助列は100、20、300、400です。この掲載式は元ネタにある例を引き継いだもので、今回の制作ではExcelで実行していません。
読むポイント
「要確認」が残る間は反映しません。これは補助列の式で、元の列を直接更新する式ではありません。修正値そのものの妥当性も別に確認します。値を反映する場合は、計算済みの補助列を値にしてから対象範囲を確認します。
出典メモ
元ネタ「Excelフィルター可視セル貼り付け問題」を、8枚の図版と短い説明に構成しました。表の値は説明用で、Excel操作の実測結果や実際の警告画面ではありません。
可視セルのコピーと貼り付けの挙動はMicrosoft：Copy visible cells only、XLOOKUPの検索と対応版はMicrosoft：XLOOKUP functionを参照しました。右方向へのコピーが内容と書式を扱うことはMicrosoft Learn：Range.FillRightを参照しました。
Copyright © 2026 LWP 山中 一弘
本資料は、出典を明記いただければ、商用・非商用を問わず、ご自由に複製・改変・再配布していただけます。なお、著作権表示は改変せず、そのまま記載してご利用くださいますようお願いいたします。