Joran está haciendo una comparación de 13 modelos diferentes de ayuda financiera para estudiantes (por provincias / territorios en Canadá). Cada modelo tiene su propia hoja de trabajo que se inserta en una página de resumen. A Joran le gustaría poder comparar la complejidad de cada hoja de trabajo individual (provincia / territorio) de una manera concreta. Cuando se utiliza la herramienta «Evaluar fórmula», se aísla cada paso de una fórmula determinada. Le gustaría saber si hay una manera de contar el número de ‘pasos’ de evaluación contenidos en una hoja de cálculo completa. Esto le proporcionaría a Joran una medida algo objetiva que sería comparable en todo el libro de trabajo.

Desafortunadamente, parece que Excel no hace que la herramienta ‘Evaluar fórmula’ esté disponible, como un objeto, dentro de VBA. Eso significa que evaluar una hoja de trabajo de esta manera requeriría desarrollar una macro para examinar todas las celdas, extraer fórmulas y contar las «frases funcionales» en cada fórmula. Esto podría volverse bastante complejo en poco tiempo.

Aunque esto se podría hacer (aparte de la complejidad), puede que no produzca los resultados deseados. Como ejemplo, considere la siguiente fórmula:

=A1+A2+A3

Esta fórmula tiene dos o tres evaluaciones, dependiendo de cómo las cuente. Ahora considere la siguiente fórmula:

=SUM(A1:A3)

Esta fórmula es funcionalmente equivalente a la anterior, pero solo requiere una evaluación. Ninguna fórmula es más compleja que la otra, pero si solo cuenta evaluaciones, la primera se mostraría como dos o tres veces más compleja que la segunda. Algunas funciones, aunque se cuentan como una única evaluación, son mucho más complejas que la simple suma y esto también se ignora cuando se cuentan frases.

Además, si sus fórmulas incluyen cualquier tipo de estructura de toma de decisiones (generalmente usando la función SI), entonces la complejidad de la fórmula no está directamente relacionada con la cantidad de frases o evaluaciones en la fórmula. La razón es que lo que Excel realmente calcula se determina en el momento del cálculo en función de las condiciones presentes en ese momento.

Reconocer que contar frases no es la mejor manera de determinar la complejidad del modelo conduce naturalmente a la pregunta de cómo se puede hacer la determinación. Qué se puede examinar dentro de una hoja de trabajo para determinar objetivamente la complejidad. No hay una manera fácil de tomar tal determinación.

Esto lo explica, en parte, Steve, un suscriptor de ExcelTips que es un químico de desarrollo de doctorado. Como parte de su trabajo diseña experimentos y los analiza para crear modelos logísticos y de negocio. Estos son sus comentarios:

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

Una evaluación más simple de la complejidad del modelo es el número de términos usados ​​en el modelo para obtener los resultados. Al evaluar modelos, en mi experiencia he descubierto que desea encontrar un equilibrio entre resultados simples y resultados. Uno agrega elementos que aportan sustancia real a los resultados y elimina los que no lo hacen.

Una medida de ajuste utilizada en los modelos se denomina coeficiente de determinación (también conocido como R ^ 2, R-cuadrado). Es una medida de qué tan bien se ajusta el modelo a los datos reales. Es la razón de la «suma de cuadrados» de la regresión, es decir, el cuadrado del valor del modelo frente al valor real de cada valor individual, dividido por la suma de cuadrados total, es decir, el cuadrado de las diferencias de cada punto con respecto a la media de los puntos.

Pero a medida que se agrega complejidad (agregando términos adicionales), R ^ 2 aumenta a medida que disminuyen los grados de libertad. Un R ^ 2 ajustado es una buena comparación:

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

donde R ^ 2 fue como se describió anteriormente, n es el número de puntos y k es el número de términos usados ​​en el modelo.

Por lo general, uno se esfuerza por obtener el máximo R ^ 2 ajustado.

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

A menos que uno esté muy familiarizado con los métodos estadísticos y las matemáticas, es fácil perderse en los detalles para determinar la complejidad de esta manera.

Si recorrer ese camino no es de su agrado (o de su entrenamiento), entonces hay otra forma de determinar la complejidad: ¿Cuánto tiempo se tarda en llegar a un resultado usando cada modelo? Si coloca la hoja de trabajo de cada modelo en su propio libro de trabajo, puede usar una macro para medir cuánto tiempo lleva realizar un recálculo en el libro de trabajo. Dado que Excel utiliza los mismos métodos internos para realizar el recálculo en cada libro de trabajo, puede comparar fácilmente el tiempo en cada uno de los libros de trabajo. Para una indicación general de complejidad, el libro de trabajo que tarda más en recalcular es el más complejo.

ExcelTips es su fuente de formación rentable en Microsoft Excel.

Este consejo (13096) se aplica a Microsoft Excel 2007, 2010, 2013, 2016, 2019 y Excel en Office 365.