Джоран проводит сравнение 13 различных моделей финансовой помощи студентам (по провинциям / территориям в Канаде). Каждая модель имеет свой собственный рабочий лист, который переходит в сводную страницу. Джоран хотел бы иметь возможность сравнить сложность каждого отдельного рабочего листа (провинция / территория) конкретным образом. При использовании инструмента «Оценить формулу» каждый шаг данной формулы изолирован. Он хотел бы знать, есть ли способ подсчитать количество «шагов» оценки, содержащихся во всей электронной таблице. Это дало бы Джорану несколько объективную оценку, которую можно было бы сопоставить по всей рабочей тетради.

К сожалению, похоже, что Excel не предоставляет инструмент «Вычислить формулу» как объект в VBA. Это означает, что для оценки рабочего листа таким образом потребуется разработка макроса для просмотра всех ячеек, извлечения формул и подсчета «функциональных фраз» в каждой формуле. Вскоре это может стать довольно сложным.

Даже если это можно сделать (не говоря о сложности), это может не дать желаемых результатов. В качестве примера рассмотрим следующую формулу:

=A1+A2+A3

В этой формуле есть две или три оценки, в зависимости от того, как вы подсчитываете оценки. Теперь рассмотрим следующую формулу:

=SUM(A1:A3)

Эта формула функционально эквивалентна предыдущей, но требует только одной оценки. Ни одна из формул не является более сложной, чем другая, но если вы посчитаете только оценки, первая окажется в два или три раза сложнее, чем вторая. Некоторые функции, даже если они считаются единственной оценкой, намного сложнее простого сложения, и это также игнорируется при простом подсчете фраз.

Кроме того, если ваши формулы включают структуру принятия решений любого типа (обычно с использованием функции ЕСЛИ), то сложность формулы не связана напрямую с количеством фраз или оценок в формуле. Причина в том, что то, что на самом деле вычисляет Excel, определяется во время вычисления на основе условий, существующих в то время.

Признание того, что подсчет фраз — не лучший способ определения сложности модели, естественно, приводит к вопросу о том, как это сделать. Что можно изучить на листе, чтобы объективно определить сложность. Нет простого способа сделать такое определение.

Частично это объясняется Стивом, подписчиком ExcelTips, который является химиком-разработчиком с докторской степенью. В рамках своей работы он разрабатывает эксперименты и анализирует их для создания логистических и бизнес-моделей. Вот его комментарии:

=== [begin Steve's comments on model complexity]===

Более простая оценка сложности модели — это количество терминов, используемых в модели для получения результатов. По моему опыту, при оценке моделей я обнаружил, что вы хотите найти баланс между простыми и результатами. Один добавляет элементы, которые придают реальный результат результатам, и исключает те, которые этого не делают.

Мера соответствия, используемая в моделях, называется коэффициентом детерминации (также известный как R ^ 2, R-квадрат). Это показатель того, насколько хорошо модель соответствует фактическим данным. Это отношение «суммы квадратов» регрессии, означающее квадрат значения модели и фактического значения для каждого отдельного значения, деленное на общую сумму квадратов, означающую квадрат разницы каждой точки к среднее значение точек.

Но по мере увеличения сложности (путем добавления дополнительных членов) R ^ 2 становится выше, когда степени свободы снижаются. Скорректированный R ^ 2 — хорошее сравнение:

Adjusted R^2 = 1 - (1-R^2) * (n-1) / (n-k-1)

где R ^ 2 было таким, как описано выше, n — количество точек, а k — количество терминов, используемых в модели.

Обычно стремятся к максимальному скорректированному R ^ 2.

=== [end Steve's comments on model complexity]===

Если человек не разбирается в статистических методах и математике, легко потеряться в деталях определения сложности таким образом.

Если идти по такому пути не нравится вам (или вашей тренировке), то есть другой способ определения сложности: сколько времени нужно, чтобы прийти к результату с использованием каждой модели? Если вы поместите рабочий лист каждой модели в отдельную книгу, вы можете использовать макрос для измерения времени, необходимого для выполнения пересчета в книге. Поскольку Excel использует одни и те же внутренние методы для выполнения пересчета для каждой книги, вы можете легко сравнить время в каждой из книг. Для общей оценки сложности, рабочая тетрадь, которая требует больше всего времени для перерасчета, является самой сложной.

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (13096) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365.