小ネタ・便利技

Excelで数式の計算過程を表示する方法

投稿日:2016.06.27 更新日:

Excelで作業しているとやたら長ったらしい数式ってたまにありますよね。

『この数式は関数がいっぱい書いてあるけど何でこの値を返すんだろう?』

とか

『あれ?数式がエラーになっちゃったけど原因はどこだ?』

とか、こんな経験ありませんか?

そういった複雑な数式を分析するのは一苦労ですX(
そんな場合はこのエントリーでご紹介する方法で業務効率化しましょう:)

数式の途中経過表示はF9キー

Excel でF9キーといえば真っ先に「再計算」が浮かびますよね。もっとも、デフォルトでは再計算は自動になっているので明示的にF9キーを押下して再計算するケースって普通に Excel 使ってる上ではあんまないんですけどね。

で、このF9キーですが、数式の任意の箇所を計算するっていう機能もあるんです。この機能を利用すれば計算途中経過を表示して数式のデバッグが可能になります。

複雑な数式

ではどのようにF9キーを使用するのか、ってのを説明する前に、説明で必要となる複雑な数式準を備しましょう。今回は「うるう年を判定する数式」を説明で使用したいと思いますので簡単に概要を説明しますね。

うるう年は以下の①または②いずれかの条件に合致する年です。

①「年」が400で割り切れる
②「年」が4で割り切れ、100で割り切れない

これを数式にすると↓こうなります、うるう年なら「閏年」を返し、以外なら「平年」を返します。

=IF(MOD(判定年,400)=0,”閏年”,IF(MOD(判定年,4)=0,IF(MOD(判定年,100)<>0,”閏年”,”平年”),”平年”))
※「判定年」には任意の年(数値)が入ります
MOD関数は剰余を返す関数です

OR関数、AND関数を使えばもうちょいすっきりした数式にできるんですが、説明のためにあえて冗長にしています。

難解って程では全然ないですが、IF関数をネストしてたりしてぱっとみではわかりづらい数式です。

F9キーで途中経過を表示する方法

さて、ようやく本題の「数式の計算途中結果を表示する方法」です。

以下の例では[判定年]としてセルA2に入力した年がうるう年かどうか判定する数式がセルB2に入っています。

うるう年を判定する数式 | =IF(MOD(A2,400)=0,"閏年",IF(MOD(A2,4)=0,IF(MOD(A2,100)<>0,"閏年","平年"),"平年"))

IF関数が3つネストされているので途中結果を表示しながら順を追って数式を分析してみます。

①まず1つ目のIF関数のMOD(A2,400)を選択します。途中経過表示STEP1 | MOD(A2,400) を選択

F9キーを押下します。これで選択したMOD(A2,400)の数式の計算結果が表示されます。MOD(A2,400)の計算結果、つまり判定年(2016)を400で割った余りである16が表示されました。途中経過表示STEP2 | MOD(A2,400) の計算結果

③表示された16=0の部分を選択してもう1度F9キーを押下すると計算結果のFALSEが表示されます。途中経過表示STEP4 | MOD(A2,400)=0 の計算結果

④上記③でFALSEが返されたので、同様に2つ目、3つ目のIF関数の論理式も評価してみます。いずれのIF関数もTRUEを返しているので、3つ目のIF関数の真の場合の値である「閏年」が返されるのがわかります。途中経過表示STEP5 | "閏年"が表示される過程

注意点

計算結果の確認が終了したら、Escキーなどでセルの入力をキャンセルしてください。
Enterキーなどでセルの入力を確定してしまうとF9キーでの計算結果表示したTRUE/FALSEなどの値がそのまま残ってしまいます。F9キーで途中経過表示をしたら入力はキャンセルしましょう

もし間違って入力を確定してしまった場合でも、「元にも戻す(Ctrl+Z)」で戻せますのでご安心を:)

まとめ。

複雑な数式は作成しないのがベストですが、複雑な計算を Excel の関数だけで行おうとするとどうしても数式も長く複雑なものになってしまいます。

自分が作ったものでも後で見るとわからないのに他人が作ったものだと更に分析が難しいのではないかと…

そんなときはこの方法で数式の分析をしてみて下さい。

ご質問などありましたらお気軽にお問い合わせくださいまし:)

実は今回の内容に似た機能で「数式の検証」っていうのが Excel に標準実装されているんですが、分かりづらい&見づらいし、こちらの方法の方が簡単だったのでこちらを採用しました。興味がある方は「数式の検証」を Excel のヘルプで調べてみてください:)

HAYs

-小ネタ・便利技
-, ,


comment

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

関連記事

book

大量シートの中から目的のシートを簡単に選択する方法

ブック内にシートがたくさんある時って、目的のシートを探して選択するのって面倒じゃないですか?面倒ですよね?面倒なんですか?そうですか。そんなあなたに目的のシートを簡単に選択する方法をご紹介します。  

alphabet

文字列Aに含まれる文字列Bの数をカウントする方法

以前(というかかなり昔)、とある方から 『例えば「ABCABCBC」っていう文字列に「A」がいくつあるか数えるエクセルの関数ってある?』 って問い合わせがあったんですが、皆様にもその方法を連携しますね …

number

文字列として保存された”数字”を計算などで使用できる”数値”に一括で変換する方法

文字列として保存された”数字”は計算などには使用出来ません。そういった”数字”は”数値”に変換してあげなければならないんですが、 …

hide-returned-zero

数式の計算結果で0を表示しない方法

VLOOKUP関数やINDEX関数、あるいは「=A1」などセルを参照する数式で、その計算結果が空白セルを参照する場合、Excel は空白(“”)ではなく0(ゼロ)を返します。今 …

【Excel】ユーザー設定リストを活用してオートフィルと並べ替えをカスタマイズ

Excel には「ユーザー設定リスト」という順序に関する情報を保存している機能があります。順序に関する情報なので、使いこなせばオートフィルや並べ替えの機能をより使いやすくすることも可能です。今回はこの …