今日はブログといいますか備忘録です。
エクセルで問合わせがあり調べた結果をブログにあげただけです。
なので興味ない人はスル―で。
＜参照元シート削除時の#REF!エラーの削除方法について＞
エクセルでは別シートのセルを参照元にしてその数値を
別のシートで関数で使用する事ができます。
これを使い、シート毎に各社のマトリックスを作成し
その各社のデータを集めた全社集計シートを作成する事が
できたりします。
というかどこでもよく作成していると思います。
例１：
Ａ社～Ｄ社のシートにある金額を全て加算し全社集計シートにまとめる
全社集計 Ａ社 Ｂ社 Ｃ社 Ｄ社
└────┴──┴──┴──┘
この機能はとても便利なのですが、統廃合等でＡ社とＢ社を統合する事になり
Ａ社・Ｃ社・Ｄ社となった場合、Ｂ社のシートを削除すると今までＢ社のシート
を参照していた全社集計シートの参照先のセルにエラーがでてしまいます。
この参照先のセルが１つや２つであればそのセルの関数からＢ社を削除すれば
いいのですが、この参照先が５０個や１００個となった場合は手作業ですと間違いも
発生しますし手間な作業となります。
そこで機械的？に修正する方法をご案内。
（といっても私も調べて把握した方法なのですが。）
例２：
集計シートの関数の修正： 全社集計シートのセルの関数
修正前： ＝A社!C10+B社!C10+C社!C10+D社!C10
修正後： ＝A社!C10+C社!C10+D社!C10
＜修正方法手順＞
①削除したいシートをシート名のところで右クリックし削除する
（これで修正したい参照先のシートのセルはエラー#REF!となります。）
②修正を行いたいセルの参照先のシートに移動し、画面上部リボンの
リボンの「数式」 - ワークシート分析の「数式の表示」をクリックします。
そうする事で、セルに、数式の結果ではなく関数（数式）が表示されます。
③現在、参照元シートを削除した為その中の関数は削除したシートのセル指定箇所が
#REF!となっていると思われます。
例：
＝A社!C10+#REF!C10+C社!C10+D社!C10
この#REF!部分をエクセルの置換を使い削除する事により、シート内にある
削除したシートを参照している全てのセルの関数を修正する事ができます。
④置換によって削除シートの参照部を削除
置換のボックスを開き
検索する文字列に ： +#REF!*
置換後の文字列に ： （何も指定しない。）
と指定します。
検索する文字列では +#REF! という数式と セル部分 C10の代わりに
全てを意味する[*]を記入します。
その後置換を実行する事により削除シート部のセル式だけを削除する事ができます。
⑤最後に画面上部リボンの リボンの「数式」 - ワークシート分析の「数式の表示」
のチェックをはずしてください。
以上となります。
実際に私がしたのは722個の修正とかなってました。
さすがにそんなには直せないよね＾＾；
という事で備忘録でした。
エクセルで問合わせがあり調べた結果をブログにあげただけです。
なので興味ない人はスル―で。
＜参照元シート削除時の#REF!エラーの削除方法について＞
エクセルでは別シートのセルを参照元にしてその数値を
別のシートで関数で使用する事ができます。
これを使い、シート毎に各社のマトリックスを作成し
その各社のデータを集めた全社集計シートを作成する事が
できたりします。
というかどこでもよく作成していると思います。
例１：
Ａ社～Ｄ社のシートにある金額を全て加算し全社集計シートにまとめる
全社集計 Ａ社 Ｂ社 Ｃ社 Ｄ社
└────┴──┴──┴──┘
この機能はとても便利なのですが、統廃合等でＡ社とＢ社を統合する事になり
Ａ社・Ｃ社・Ｄ社となった場合、Ｂ社のシートを削除すると今までＢ社のシート
を参照していた全社集計シートの参照先のセルにエラーがでてしまいます。
この参照先のセルが１つや２つであればそのセルの関数からＢ社を削除すれば
いいのですが、この参照先が５０個や１００個となった場合は手作業ですと間違いも
発生しますし手間な作業となります。
そこで機械的？に修正する方法をご案内。
（といっても私も調べて把握した方法なのですが。）
例２：
集計シートの関数の修正： 全社集計シートのセルの関数
修正前： ＝A社!C10+B社!C10+C社!C10+D社!C10
修正後： ＝A社!C10+C社!C10+D社!C10
＜修正方法手順＞
①削除したいシートをシート名のところで右クリックし削除する
（これで修正したい参照先のシートのセルはエラー#REF!となります。）
②修正を行いたいセルの参照先のシートに移動し、画面上部リボンの
リボンの「数式」 - ワークシート分析の「数式の表示」をクリックします。
そうする事で、セルに、数式の結果ではなく関数（数式）が表示されます。
③現在、参照元シートを削除した為その中の関数は削除したシートのセル指定箇所が
#REF!となっていると思われます。
例：
＝A社!C10+#REF!C10+C社!C10+D社!C10
この#REF!部分をエクセルの置換を使い削除する事により、シート内にある
削除したシートを参照している全てのセルの関数を修正する事ができます。
④置換によって削除シートの参照部を削除
置換のボックスを開き
検索する文字列に ： +#REF!*
置換後の文字列に ： （何も指定しない。）
と指定します。
検索する文字列では +#REF! という数式と セル部分 C10の代わりに
全てを意味する[*]を記入します。
その後置換を実行する事により削除シート部のセル式だけを削除する事ができます。
⑤最後に画面上部リボンの リボンの「数式」 - ワークシート分析の「数式の表示」
のチェックをはずしてください。
以上となります。
実際に私がしたのは722個の修正とかなってました。
さすがにそんなには直せないよね＾＾；
という事で備忘録でした。