今まで、何度か試みたものの「結局無理」と思って諦めてたのができたので メモします。若干感動しました。

仕事柄、お客さんにデータを貰うのに、

じゃあExcelで入力テンプレート作るので、これに埋めてくださいね。

であるとか、

マスタの内容をExcelに吐いたので必要な部分だけ修正してくださいね。

みたいな事が結構あるんですが、大体の場合、担当の方はコピペを多用したり、 勝手に列や行を追加してメチャクチャにしてくれる。 加えて、その結果本人も意図していない部分が変更されちゃってたりする。

「そんなんシート保護しときゃいいじゃん」と言う意見もあるが、 部分的に正しく保護するのって結構面倒だったりするし、 シート保護すると、「作業しにくいから保護とかそーゆーのやめてくれ」 みたいな苦情を受けたりするもんです。

「いや、そんなんそもそもExcelなんか使うからそーゆー事になるんでしょ。 Excelって『表計算ソフト』だよ?」 と言う非常にもっともな意見もあるが、 普通の方々が矩形データの登録に使うもの、と言ったらExcelくらいしか無い。

で、そんな時、シート上で 「値が元データから変更された時点で赤字になる」 等の処理が行えると、見た目にもどこが変更されたかが分かるし、 お客さん側も間違って隣のセルまで更新した事に気付きやすくなる。

と言うわけで、変更箇所をリアルタイムにマーキングする方法。

  1. マスタ等からsheetに値を書き出す(以下sheet1)
  2. sheetをコピーして全く同じシートをもう1つ作る(以下sheet2)
  3. sheet2は[ツール] - [保護] - [シートの保護]でロックしてしまう
    • 必須では無いがパスワード付ロックしといたほうが無難
  4. sheet1!A1を選択し、[書式] - [条件付書式]
  5. 条件1を"数式が"に設定し、値に以下を設定

    =(A1<>INDIRECT(ADDRESS(ROW(),COLUMN(),4,,"sheet2")))

    • "sheet2"の部分はsheet2のシート名を入力
    1. [書式]でマーキングに利用する書式を設定
    2. [OK]でsheet1に戻る
    3. sheet1!A1からデータ全体を選択し、[書式] - [条件付書式]
      • シート全体を選択しちゃっても多分大丈夫
    4. 既に先程A1で設定した条件が入っているダイアログが表示されるのでそのまま[OK]

以上で、sheet1のデータを変更すると書式が自動で変わります。 元データを見せる必要が無ければロックしたsheet2は非表示にしてしまったほうがよいかもしれません。

この方法のミソは、

=(A1<>INDIRECT(ADDRESS(ROW(),COLUMN(),4,,"sheet2")))

にあります。要するに「値がsheet2と違ったら」と言う条件です。 素直に書くと、

=(A1<>sheet2!A1)

となるんですが、このように指定すると、

抽出条件 条件付き書式で、他のワークシートまたはブックへの参照は使用しません。

と言うエラーになります。「しません。」って。宣言かよ。 と言う事で条件付書式の設定では通常他シートへの参照を含める事はできません。 他シートの値が条件に使えない、って結構その他の場面でも不便なんですが、 上記のように回りくどいやり方で無理矢理指定すると行ける、と言う事です。

条件の内容は全て相対参照になっている為、 作業中にシート内でセルをコピペしたり、フィルしても、問題なく動作します。

勝手に列を追加したりするとそれ以降の列がいきなり真っ赤になるので、 そこそこ抑止力になります。

選択肢 投票
はい 65  
いいえ 13  

作成日 2007-08-07 (火) 21:53:50

投稿者メソ | パーマリンク | トラックバック(96) | コメント(7)

Tag: Tips Office


  • INDIRECTの使い方賢い!marbowの賢さが1あがりました。 -- marbow? 2008-02-26 (火) 23:10:33
  • 役に立ちました。ありがとうございます -- [[ (^^) ]] 2009-02-08 (日) 16:28:24
  • は~(T^T) -- ワカナ? 2010-07-29 (木) 18:48:33
  • 感動しました。保護云々のところなんて、激しくうなずいてしまいました。早速使わせていただきます。本当にありがとうございました。 -- ワカナ? 2010-07-29 (木) 18:49:49
  • は~(T^T) -- ワカナ? 2010-07-29 (木) 18:50:16
  • Indirect 使い倒してますが、これは気付かなかったです。 -- apro? 2012-07-05 (木) 13:22:55
  • うまい方法ないかと探していたら、こんな方法が…。有り難う御座います! -- 春夏? 2012-10-20 (土) 18:21:32
  • すごい!ありがとうございます。 -- 2013-02-22 (金) 11:30:56
  • ありがとうございます。効率が上がります。助かりました! -- 2013-03-29 (金) 14:58:18

URL B I U SIZE Black Maroon Green Olive Navy Purple Teal Gray Silver Red Lime Yellow Blue Fuchsia Aqua White