自動計算で出した値のうち、例外の行だけは人の手で直したい。実務ではよくある場面です。たとえば、振替休日の所定労働時間のように、数式では判定しきれない日があります。
数式のセルへ値を直接打ち込むと、数式が消えます。どの行を手で直したのかも、あとで分からなくなります。
この記事では、Excelで手入力と自動計算を同じ表で共存させる「上書き優先列」の作り方を紹介します。落とし穴や、Power Queryと組み合わせたときの挙動も、実機で確認した結果で示します。
想定読者
- 数式で自動計算した表に、例外の行だけ手で直したい方
- Power Queryで取り込んだ表に、手入力の列を足したい方
手入力と自動計算は3列に分けて共存させる
1つの値を、次の3列に分けて持ちます。
| 列 | 役割 | 中身 |
|---|---|---|
| ①機械計算列 | 通常ロジックの結果 | 数式 |
| ②手入力列 | 例外のときだけ人が入力 | 数式なし・通常は空欄 |
| ③確定列 | 実際に使う値 | ②があれば②、なければ① |
A列を①、B列を②、C列を③とすると、確定列（C列）の数式は次のとおりです。
=IF(B2<>"",B2,A2)B列が空欄ならA列の値、B列に何か入っていればB列の値になります。他の計算は、確定列だけを参照します。IF関数の書式はMicrosoft SupportのIF関数のページにまとまっています。
実機で、次の動きを確認しました。
- B列を空欄にすると、C列はA列の値に戻る
- B列が空欄の間は、A列を変えるとC列も追従する
- B列に値があるときは、A列を変えてもC列は動かない
数式は一度も壊れません。例外を入れるのも消すのも、B列を触るだけです。
よくある失敗1：判定に「>0」を使うと0を入力できない
手入力の有無は、B2<>""（空欄ではない）で判定します。B2>0で判定すると、0を入力したいときに拾えません。
A列が8のときに、B列へ0を入力して試しました。
| 判定式 | 結果 |
|---|---|
=IF(B1<>"",B1,A1) | 0（手入力が採用される） |
=IF(B1>0,B1,A1) | 8（0が拾われず、自動計算に戻る） |
「自動計算では8時間だが、この日はあえて0時間で確定させたい」という場面は、実務で起きます。判定は<>""にしてください。
よくある失敗2：空に見えるセルが、空ではない
手入力列が空に見えても、判定上は入力ありになるケースがあります。実機で次の3つを確認しました。
- 空文字を返す数式が入っている：
<>""なら空欄扱いで、自動計算値が使われます。一方、ISBLANK(B1)は「空欄ではない」と判定します。IF(ISBLANK(B1),A1,B1)にすると、確定列が空文字になり、値が消えたように見えます。 - 半角スペースが入っている：見た目は空欄でも、入力ありと判定されます。確定列がスペースになります。
TRIMを挟んで、IF(TRIM(B1)<>"",B1,A1)にすると、自動計算値に戻ります。 - 全角スペースが入っている：今回の環境（日本語環境のExcel）では、
TRIMを挟むと、全角スペースだけのセルも空欄扱いになりました。他の環境では未検証です。確実に防ぎたいときは、SUBSTITUTEで全角スペースを半角に置き換えてからTRIMします。この式でも、全角スペースは自動計算値に戻り、5を入力すれば5になりました。
よくある失敗3：Power Queryの更新後に手入力がずれる・消える
Power Queryで取り込んだ表の右に、手入力列と確定列を足す方法があります。更新で手入力が消えないかを確認しました。元データは5件で、手入力はK002に999、K005に0を入れました。
- 同じ元データで更新：手入力は残りました。
- 元データの並びを逆にして更新：999はK002ではなくK004の行に付きました。K002の確定は、自動計算の20のままでした。
- 元データを3件に減らして更新：表も3行に追随しました。下の行にあった手入力は、行ごと消えます。
- 3件から7件に増やして更新：K005に入れた0は消えていました。一方、新しく増えたK007の手入力欄に0が入り、確定が70ではなく0になりました。同じ手順で2回試して、同じ結果でした。原因までは調べていません。
手入力は、キーではなく行の位置に付いています。並びや件数が毎回変わるデータでは、この構成は使えません。
手入力を別テーブルに分けてキーで引く
そこで、手入力は別のExcelテーブルに分けます。キー列と上書き値の列だけを持たせます。
Power Queryの出力テーブルには、手入力値を引く列を足します。手入力テーブルの名前をtblManual、列名をKeyとOverrideとしています。
=IFERROR(IF(ISBLANK(INDEX(tblManual[Override],MATCH([@Key],tblManual[Key],0))),"",INDEX(tblManual[Override],MATCH([@Key],tblManual[Key],0))),"")ISBLANKを挟むのは、INDEXが空セルを参照すると0を返すためです。実機では、上書き値が空のキーをINDEXだけで引くと0になりました。ISBLANKで守ると空になり、0を入力したキーは0のまま、存在しないキーは空になりました。
確定列は、これまでと同じ形です。ここでは、手入力値を引く列をManualValue、自動計算の列をAutoとしています。
=IF([@ManualValue]<>"",[@ManualValue],[@Auto])元データを逆順にして更新しても、999はK002に、0はK005に付きました。上書き値が空のK004は自動計算の40、手入力のないK007は70のままです。次の画像は、逆順にした出力テーブルです。
手入力を持つ別テーブルは、次のとおりです。
キーの型がそろっていないと、引けません。型の扱いは、キーの型がバラバラで結合できない時の対処法を参照してください。
IFERRORでエラーを代替値に置き換える書き方は、ORやANDの中でも使えます。エラーが混ざったときの結果は、【Excel関数】OR・ANDは短絡評価されない原因と対処で実測しました。
キーに適用開始日が加わり、基準日時点の最新行を引く場合の式は、【Excel】適用開始日つきマスタから基準日時点の最新行を引く式で比べています。
別テーブルの値を構造化参照で引く式に @ を付けると、行番号が合わない行で #VALUE! になります。@ が指す行の仕組みは、【Excel】構造化参照の@は自分の行だけ｜1行マスタの引き方にまとめています。
応用例：手入力の有無を見える化する
手入力で上書きした行をあとで探せるよう、判定列を足します。
=IF(B2<>"","手入力","自動")0を入力した行も「手入力」と表示されました。フィルターで「手入力」だけを絞り込めば、例外の行を確認できます。
丸め処理の結果に例外を入れたいときにも使えます。丸めの順序については、時間の丸めは順序で結果が変わる理由と対処を参照してください。
手入力で上書きする行を絞るために、システムの集計値との不整合をフラグ化する方法は、【Excel】打刻と集計値の不整合を検知するフラグ列の設計にまとめています。
手入力と自動計算が混ざったブックを引き継いだときは、数式だけでなく外部リンクや名前の参照先も先に確認しておくと安心です。確認する場所の一覧は、【Excel】引き継ぎブックで先に見る外部リンク・名前・接続で紹介しています。
まとめ
- 値は「機械計算・手入力・確定」の3列に分ける
- 手入力の判定は
<>""にする。0の上書きも拾える - 空文字を返す数式やスペースが入る可能性に注意する
- Power Queryの出力の隣に手入力列を足すと、並びや件数の変化で手入力がずれる
- 手入力は別テーブルに分け、キーで引く。空セルは
INDEXが0を返すので、ISBLANKで守る
コメント