ワークシートの複雑さの判断（Microsoft Excel）

Joranは、学生の学資援助の13の異なるモデル（カナダの州/準州別）の比較を行っています。各モデルには、要約ページにフィードする独自のワークシートがあります。 Joranは、個々のワークシート（州/準州）の複雑さを具体的な方法で比較できるようにしたいと考えています。 「数式の評価」ツールを使用すると、特定の数式の各ステップが分離されます。彼は、スプレッドシート全体に含まれる評価の「ステップ」の数を数える方法があるかどうかを知りたいと思っています。これにより、Joranは、ワークブック全体で比較できるやや客観的な尺度を得ることができます。

残念ながら、Excelでは「数式の評価」ツールをオブジェクトとしてVBA内で使用できるようにしていないようです。つまり、この方法でワークシートを評価するには、すべてのセルを調べ、数式を引き出し、各数式の「機能フレーズ」を数えるマクロを開発する必要があります。これは、短期的にはかなり複雑になる可能性があります。

これは可能ですが（複雑さは別として）、望ましい結果が得られない場合があります。例として、次の式について考えてみます。

この式には、評価のカウント方法に応じて、2つまたは3つの評価があります。ここで、次の式について考えてみます。

この式は機能的には前の式と同等ですが、必要な評価は1つだけです。どちらの式も他の式より複雑ではありませんが、評価のみを数えると、最初の式は2番目の式の2倍または3倍複雑になります。一部の関数は、単一の評価としてカウントされますが、単純な加算よりもはるかに複雑であり、単にフレーズをカウントする場合も無視されます。

さらに、数式に何らかのタイプの意思決定構造が含まれている場合（通常はIF関数を使用）、数式の複雑さは、数式内のフレーズや評価の数に直接関係していません。その理由は、Excelが実際に計算するものは、その時点での条件に基づいて計算時に決定されるためです。

フレーズを数えることがモデルの複雑さを判断するための最良の方法ではないことを認識することは、当然、どのように判断を下すことができるかという問題につながります。複雑さを客観的に判断するためにワークシート内で調べることができるもの。そのような決定をする簡単な方法はありません。

これは、博士号開発化学者であるExcelTipsサブスクライバーであるSteveによって部分的に説明されています。彼の仕事の一環として、彼は実験を設計し、それらを分析して、ロジスティクスおよびビジネスモデルを作成します。彼のコメントは次のとおりです：

モデルの複雑さのより簡単な評価は、結果を得るためにモデルで使用される用語の数です。モデルを評価するとき、私の経験では、単純なものと結果のバランスを見つけたいと思っています。結果に実際の実体を提供する要素を追加し、そうでない要素を排除します。

モデルで使用される適合度は、決定係数（R ^ 2、R-squaredとも呼ばれます）と呼ばれます。これは、モデルが実際のデータにどの程度適合しているかを示す尺度です。これは、回帰の「二乗和」の比率であり、モデルの値の二乗と個々の値の実際の値を、合計の二乗和で割ったものです。これは、各ポイントの差の二乗を意味します。ポイントの平均。

しかし、（項を追加することによって）複雑さを増すと、自由度が下がるにつれてR ^ 2が高くなります。調整されたR ^ 2は良い比較です：

ここで、R ^ 2は上記のとおり、nは点の数、kはモデルで使用される項の数です。

通常、調整されたR ^ 2の最大値を目指して努力します。

統計的手法と数学に精通していない限り、この方法で複雑さを判断する詳細に迷うのは簡単です。

そのような道を進むことがあなたの好み（またはあなたのトレーニング）に合わない場合は、複雑さを判断する別の方法があります：各モデルを使用して結果に到達するのにどのくらい時間がかかりますか？各モデルのワークシートを独自のワークブックに配置すると、マクロを使用して、ワークブックの再計算の実行にかかる時間を測定できます。 Excelは同じ内部メソッドを使用して各ブックの再計算を実行するため、各ブックのタイミングを簡単に比較できます。複雑さを示す一般的なパンツのシートの場合、再計算に最も時間がかかるワークブックが最も複雑です。

_ExcelTips_は、費用効果の高いMicrosoftExcelトレーニングのソースです。

このヒント（13096）は、Microsoft Excel 2007、2010、2013、2016、2019、およびOffice365のExcelに適用されます。