2.4: Modelado Excel - modelos lógicos, optimización con solucionador para regresión no lineal, muestreo de números aleatorios

Introducción

El programa Microsoft Excel es uno de los programas informáticos más populares y útiles para una amplia variedad de aplicaciones numéricas. Excel cuenta con varias funciones diferentes, interfaces y herramientas gráficas que se pueden aplicar en muchos campos. Las siguientes funciones podrían ser especialmente útiles para la programación lógica en Excel:

• Funciones lógicas (por ejemplo: IF, OR y AND): Estas funciones se pueden utilizar en el análisis de control, particularmente en la regulación de seguridad (por ejemplo, si la temperatura supera los X grados, apaga el reactor y envía agua de refrigeración a la camisa)
• Solucionador: Esta función se puede utilizar para maximizar, minimizar o intentar obtener un valor de entrada en una celda variando celdas referenciadas.
• Generador de Números Aleatorios. Esto se puede utilizar como una herramienta para la probabilidad o seleccionar aleatoriamente puntos de datos para su análisis a partir de un gran grupo de puntos.

Si bien puede ser menos preciso que otros programas de modelado, el modelo de Excel proporciona buenas estimaciones y facilita la observación y comprensión del comportamiento del proceso al tiempo que requiere un conocimiento mínimo de computadora. Las siguientes secciones le proporcionarán una mejor comprensión de estas funciones comunes de Excel, seguidas de ejemplos en aplicaciones de ingeniería.

Métodos Lógicos

Una prueba lógica es cualquier expresión que se pueda evaluar como VERDADERO o FALSO con condiciones de entrada especificadas. Las pruebas lógicas de uso común son las sentencias IF, OR y AND. Las tres declaraciones se pueden usar conjuntamente entre sí para un modelado más complejo y completo.

Nota: todas las funciones y fórmulas en Excel requieren un signo igual (=) delante de la sintaxis.

Declaración IF de Excel

La función IF en Excel prueba valores y fórmulas en las celdas de destino y devuelve un valor especificado “si” la entrada de condición evalúa como VERDADERO y otro valor si se evalúa como FALSO. La sentencia IF en Excel se llama con la siguiente sintaxis:

• IF (prueba_lógica, valor_si_verdadero, valor_si_falso)

Lógical_test: cualquier expresión o correlación de valores que puedan ser evaluados como VERDADERO o FALSO.

VALUE_IF_TRUE: el valor o expresión que se devuelve SI la condición en la prueba lógica devuelve TRUE.

VALOR_IF_FALSE: el valor o expresión que se devuelve SI la condición en la prueba lógica devuelve FALSE.

La parte valor_si_verdadero y valor_si_falso en la sintaxis pueden variar y pueden ser valores numéricos, expresiones, modelos y fórmulas. Si alguno de los valores se deja en blanco, la prueba devolverá 0 (cero) correspondientemente.

Codificación de muestras:

• SI (1+1=2, “¡Correcto!” , “¡Incorrecto!”)

Para la parte lógica de prueba, 1+1 es igual a 2, y la iteración devolverá TRUE. El valor_si_verdadero se llama para la respuesta VERDADERO, y “¡Correcto!” se dará salida en la celda correspondiente.

Es posible anidar múltiples funciones IF (llamadas IF anidadas) dentro de una fórmula de Excel. Tal fórmula es útil para devolver múltiples respuestas, en comparación con una sola función IF que puede devolver solo dos respuestas posibles.

Una instrucción IF anidada tiene la siguiente sintaxis:

• IF (prueba_lógica1, valor_si_verdadero1, IF (prueba_lógica2, valor_si_verdadero2, valor_si_falso))

El siguiente ejemplo muestra cómo se utiliza una función IF anidada para hacer una hoja de cálculo que asigna una calificación de letra a diferentes partituras.

Supongamos que la distribución de calificaciones es:

@ A= Mayor o igual a 80

@ B= Mayor o igual a 60, pero menor que 80

@ C= Menos de 60

Esta función lógica se puede escribir de varias maneras diferentes. Si la puntuación se prueba para que sea la letra grado C primero, luego se verifica para ver si es una B o una A, la sintaxis podría escribirse de la siguiente manera:

• SI (A5<60, "C”, SI (A5<80, "B”, "A”))

Tenga en cuenta que la instrucción IF anidada se puede colocar en las posiciones value_if_false O en las posiciones value_if_true. En el ejemplo anterior, se colocó en la posición valor_si_false por cómo se escribió el condicional.

Se podrían agregar calificaciones de letras adicionales al incluir más funciones IF anidadas dentro de la función existente. Una fórmula puede incluir hasta 7 IF anidadas, lo que significa que puede devolver hasta 8 resultados diferentes.

Declaración OR de Excel

La declaración OR en Excel es similar a la declaración IF. Devuelve TRUE si algún argumento o expresión de valores es TRUE, y devuelve FALSE si todos los argumentos o expresiones de valores son FALSE. La sintaxis para OR es:

• O (lógico1, lógico2,...)

lógico1: puede haber hasta 30 argumentos para que la expresión lógica pruebe y devuelva VERDADERO o FALSO. Si todos los argumentos son falsos, la sentencia devuelve FALSE para toda la expresión.

Codificación de muestras:

• O (1+1=2, 2+2=5)

En la parte lógica1, 1+1 es igual a 2 y devolverá VERDADERO. En la parte lógica2, 2+2 no es igual a 5 y devolverá FALSE. Sin embargo, dado que una de las pruebas devuelve TRUE (lógico1), la función devuelve TRUE incluso si lógico2 es FALSO. TRUE se emitirá en la celda de función correspondiente.

Exce's Y estado de cuenta

La instrucción AND devuelve TRUE si todos sus argumentos o valores son TRUE y devuelve FALSE si uno o más argumentos o valores son FALSE. La sintaxis de la sentencia AND es:

• Y (lógico1, lógico2,...)

logical1: similar a la sentencia OR con hasta 30 argumentos para probar que podrían devolver VERDADERO o FALSO.

Codificación de muestras:

• Y (1+1=2, 2+2=5)

Lógical1 devolverá VERDADERO para el cálculo correcto mientras que lógico2 devolverá FALSE para el cálculo incorrecto. No obstante, dado que una de las expresiones es FALSE, la prueba lógica AND devolverá FALSE aunque una de las pruebas sea verdadera.

Es común y útil entender cómo implementar diferentes funciones lógicas entre sí. Un buen ejemplo sería:

• SI (Y (2+2=4,3+3=5), "Cálculo correcto”, "Cálculo incorrecto”)

Durante la iteración, la sentencia AND debe devolver FALSE ya que una de las expresiones/cálculos es incorrecta. El retorno FALSE generará “Cálculo incorrecto” para la iteración de la sentencia IF.

Otras funciones útiles de Excel

Excel tiene muchas funciones útiles que pueden ayudar a cualquier ingeniero que esté usando Excel para modelar o controlar un sistema. Un ingeniero puede querer saber el caudal promedio, el número de veces que el líquido en un tanque alcanza un cierto nivel, o tal vez el número de pasos de tiempo que una válvula está completamente abierta o completamente cerrada. Todas estas situaciones y muchas más pueden ser abordadas por muchas de las funciones simples incorporadas de Excel. A continuación se presentan algunas de estas funciones y una breve definición de lo que calculan y cómo utilizarlas. A ellos les sigue una imagen de un archivo excel de muestra con un ejemplo de cada función en uso. Para ver una lista de todas las funciones integradas que Excel tiene para ofrecer, haga clic en shift-enter en la celda.

PROMEDIO (número1, número2,...) - Devuelve la media media o aritmética de los argumentos. Los argumentos pueden ser números, matrices o referencias que contienen números.

TECHO (número1, significancia) - Redondea un número hasta el entero más cercano o múltiplo de significancia.

CHIDIST (x, grados_libertad) - Devuelve la probabilidad de la distribución chi-cuadrado.

COUNT (valor1, valor2,...) - Cuenta el número de celdas que contiene números.

COUNTIF (rango, criterios) - Cuenta el número de celdas dentro del rango que cumplen con los criterios dados.

COUNTIPS (criteria_rango1, criterio1, criteria_rango2, criteriosa2,...) - Cuenta el número de celdas dentro de múltiples rangos que cumplen múltiples criterios.

PISO (número1, significancia) - Redondea un número hacia abajo hacia cero, hasta el múltiplo más cercano de significancia.

FRECUENCIA (data_array, bins_array) - Calcula la frecuencia con la que se produce un valor dentro de un rango de valores (bins). La función devolverá una matriz vertical de números que tiene un elemento más que el bins_array. Una vez que la fórmula se ingresa en la celda de destino, resalte la celda de destino haciendo una matriz vertical una celda más grande que bins_array. Una vez seleccionado presione F2 para abrir la fórmula de la celda de destino y luego presione CTRL+MAYÚS+ENTRAR para ejecutar su función.

LARGE (array, k) - Devuelve el k-ésimo valor más grande en un conjunto de datos. Por ejemplo, el segundo número más grande.

MAX (número1, número2,...) - Devuelve el número mayor en un conjunto de valores.

MEDIAN (número1, número2,...) - Devuelve la mediana de un conjunto de valores.

MIN (número1, número2,...) - Devuelve el valor mínimo en un conjunto de valores.

MODE (número1, número2,...) - Devuelve el valor que ocurre con más frecuencia en una matriz o rango de valores.

PERCENTIL (array, k) - Devuelve el percentil k-ésimo de valores en un rango

REDONDO (número1, núm_dígitos) - Redondea un número al número especificado de dígitos.

SMALL (array, k) - Devuelve el k-ésimo valor más pequeño en un conjunto de datos. Por ejemplo, el número 7 más pequeño.

STDEV (número1, número2,...) - Devuelve la desviación estándar de un conjunto dado de valores ignorando texto y valores lógicos.

SUM (número1, número2,...) - Agrega todos los valores en un rango de celdas.

SUMIF (rango, criterios, rango,...) - Agrega valores de celdas en un rango que cumple con criterios.

VAR (número1, número2,...) - Estima la varianza de un conjunto dado de valores.

Anidamiento

Aunque no es complejo ni difícil, colocar declaraciones lógicas unas dentro de otras puede ser una práctica poderosa. Esto se conoce como anidación.

A continuación se muestra un ejemplo que demuestra el uso de una instrucción IF simple anidada dentro de otra instrucción IF:

Si uno preguntara a tres individuos si están de acuerdo o no en que la energía tiene una velocidad, estos individuos responderán hipotéticamente de una de tres maneras: “Sí”, "No”, u (otra). En este caso, si el individuo respondiera “Sí”, entonces el interrogador respondería “¡Estoy de acuerdo!”. Si el individuo respondiera “No”, el interlocutor respondería amargamente con un “Bah”. Y si el individuo respondiera con una respuesta sin sentido, entonces el interrogante respondería “qué..” en confusión.

El punto importante para sacar de este ejemplo es que las declaraciones anidadas nos dan el potencial de procesar más información en una celda (o línea de código de programación).

Herramientas de análisis de datos de Excel

Excel tiene varios complementos útiles incorporados que pueden facilitar mucho el análisis de datos. De estos cubriremos la herramienta solver, así como el análisis ANOVA.

Herramienta Solver

El solucionador en Excel es una herramienta integrada con múltiples capacidades. Podría usarse para optimizar (encontrar el valor máximo o mínimo) una celda específica (la celda objetivo) variando los valores en otras celdas (las celdas ajustables). También se puede utilizar para resolver un sistema de ecuaciones no lineales. Las fórmulas de Solver incrustadas en la hoja de cálculo vinculan el valor en la celda de destino a los valores en las celdas ajustables. El usuario también puede especificar restricciones de rango numérico para las celdas involucradas.

Modelo de Optimización

En la optimización, se busca maximizar o minimizar el valor de una función real. Así como un estudiante en una clase de cálculo podría usar técnicas de optimización para encontrar máximos o mínimos locales de una función compleja, un CEO podría usar técnicas similares para encontrar cuánto producto debería fabricar su compañía para maximizar las ganancias. Existen numerosas técnicas de optimización, muchas especializadas para problemas de tipos específicos. En su mayor parte, MS Excel utiliza técnicas basadas en gradientes.

La herramienta solucionador se encuentra en el menú Herramientas. Si no aparece ahí, debe agregarse seleccionando Complementos y seleccionando la casilla de verificación correspondiente.

La ventana del solucionador contiene los siguientes campos:

• Establecer celda de destino: Esta es la celda que contiene el valor que desea optimizar.

• Igual a: Elija si desea maximizar el valor, minimizar el valor o establecerlo en un número específico.
• Por Cambio de Celdas: Aquí se especifican todas las celdas que se pueden variar al optimizar el valor de la celda de destino.
• Sujeto a las Restricciones: Las restricciones son opcionales, pero se pueden agregar haciendo clic en Agregar y definiendo cada restricción.
• Lista de restricciones disponibles

• <= (Menor o igual a) Estipula que una celda seleccionada debe ser menor o igual a cierto valor.
• = (Igual a) Estipula que una celda seleccionada debe ser igual a cierto valor.
• >= (Mayor o igual a) Estipula que una celda seleccionada debe ser mayor o igual que un cierto valor.
• int (Entero) Estipula que una celda seleccionada debe ser un entero
• bin (Binario) Estipula que una celda seleccionada debe ser igual a 1 o 0.

A continuación se muestra la ventana donde se pueden ingresar estas restricciones.

Es importante asegurarse de que las restricciones no se violen entre sí, de lo contrario, el solucionador no funcionará sin informar de un error. Por ejemplo, si una celda debe ser grande que 2 y menos de 1 Solver no cambiará nada.

Una vez que todos los campos de la ventana Solver se hayan completado adecuadamente, haga clic en Resolver. Si solver puede encontrar una solución, aparecerá una ventana que te lo indica, y los valores aparecerán en las celdas objetivo y ajustables. Si solver no puede encontrar una solución, seguirá mostrando los valores que encontró pero declarará que no pudo encontrar una solución factible. Tenga en cuenta que el solucionador de Excel no es una herramienta lo suficientemente poderosa como para encontrar una solución a ecuaciones no lineales, donde programas como Matlab podrían ser una mejor opción.

* Ejemplo Guiado: Supongamos que tiene una hoja de cálculo de Excel establecida como se muestra a continuación, con la celda C6 dependiendo de la celda B6 de acuerdo con la relación cuadrática que se muestra, y desea minimizar el valor en C6 variando el valor en B6.

1) Abra la ventana del solucionador.

2) Ingrese C6 en el campo de celda objetivo establecido, verifique el botón min e ingrese B6 en el campo cambiando celdas:

3) Haga clic en resolver para encontrar la solución.

Puede colocar más restricciones o especificar más cómo desea que se resuelva su tarea en “Opciones”. Algunas de las especificaciones más utilizadas incluyen:

• Tiempo máximo: Esta es la cantidad máxima de tiempo que Excel pasará tratando de encontrar un valor convergente antes de darse por vencido.
• Iteraciones: Este es el número de iteraciones que Excel realizará para converger a un valor.
• Precisión: Esto está relacionado con lo cerca que debe llegar Excel antes de que un valor sea “aceptable”. En general, hacer que este valor sea varios órdenes de magnitud más pequeño arrojará un resultado más preciso, pero llevará más tiempo hacerlo, y puede tener más dificultades para converger.
• Tolerancia: Esto también está relacionado con lo precisa que es una solución, esto está relacionado con el porcentaje de error. Hacer esto más pequeño también debería producir un resultado más preciso, pero con más dificultad para encontrar una solución.
• Convergencia: Controla la cantidad de cambio relativo que se produce durante las últimas cinco iteraciones que realiza Solver. Cuanto menor sea el número de la convergencia menor será el cambio que se producirá.
• Opciones de caja Haga clic en el cuadro para seleccionar las siguientes opciones:
• Asumir Modelo Lineal: Esto acelerará el tiempo para encontrar una solución porque hará que Solver asuma que el modelo es lineal.
• Asumir No Negativo: Solver hará el límite inferior de ajuste cero para todas las celdas que están programadas para ser ajustadas en el modelo que no están limitadas por una restricción elegida.
• Use Escalado Automático: Se utiliza para grandes diferencias de magnitud para escalar las entradas y salidas.
• Mostrar resultados de iteración: Solver hará una pausa entre cada iteración para mostrar los resultados de esa iteración en el modelo.
• Estimaciones Especifica a Solver sobre cómo usar estimaciones iniciales para iteraciones
• Tangente: Utiliza extrapolación lineal mediante la utilización de un vector tangeta.
• Cuadrática: Utiliza extrapolación cuadrática. Lo mejor para usar en modelos no lineales.
• Derivadas Especifica qué tipo de diferenciación se utiliza para las derivadas parciales tanto para el objetivo como para las restricciones.
• Adelante: Los valores restringidos cambian lentamente entre iteraciones.

Regresión no lineal

La función solucionadora de Excel también se puede utilizar para encontrar una solución para la regresión no lineal de dos variables. La descripción de los datos por una función se lleva a cabo mediante el proceso de regresión no lineal iterativa. Este proceso minimiza el valor de la suma al cuadrado de la diferencia entre los datos reales y el valor predicho (residual).

Hay tres requisitos básicos para usar solucionador que incluyen:

1. Datos brutos
2. Una ecuación modelo para encontrar valores predichos
3. Conjeturas iniciales para valores variables

Antes de usar la función solver, los datos brutos deben organizarse en columnas separadas para la variable independiente (primera columna) y la variable dependiente (segunda columna). Una tercera columna mostrará el valor predicho que se obtiene mediante el uso de la ecuación modelo mencionada anteriormente, y deberá hacer referencia a varias celdas individuales que tengan valores iniciales que Excel utilizará como punto de partida para la regresión. Se debe crear una cuarta columna con el cuadrado de la diferencia entre el valor predicho y el valor experimental real (datos brutos); esto también se conoce como el cuadrado del residuo. Una celda en la parte inferior de esta cuarta columna debe incluir la suma de todos los valores de diferencia al cuadrado por encima de ella. Dos celdas más en cualquier otra parte de la hoja de trabajo deben designarse como las conjeturas iniciales para que los valores se varíen para encontrar la solución.

Es importante elegir un valor razonable para la suposición inicial. Esto no aumentará la posibilidad de que Excel llegue a una optimización global más que a una optimización local, sino que también reduce el tiempo que le toma a Excel resolver el sistema de ecuaciones no lineales.

Ahora, el solucionador se puede aplicar a la hoja de cálculo para optimizar la regresión. Consulte la sección 3.1 para saber cómo acceder y usar el solucionador. La celda más inferior de la cuarta columna que tenga la suma de los cuadrados de los residuos será la celda objetivo. Dado que los residuos son esencialmente el error, la suma de los residuales al cuadrado debe ser lo más pequeña posible, por lo que se debe seleccionar el círculo mínimo. Las conjeturas iniciales deben ingresarse en el “cambiando celdas”. Se pueden introducir restricciones si los valores de las celdas están limitados a un cierto rango. Solver producirá los valores de los parámetros variables que dan el mejor ajuste a los datos no lineales.

• Ejemplo Guiado: A continuación se muestra una captura de pantalla de un problema poblacional simple. Las celdas resaltadas en amarillo son las conjeturas iniciales para las variables. En este caso, se ingresa como restricción la población inicial (P0, celda E2).

Análisis ANOVA

ANOVA es otra herramienta en Excel que puede ser utilizada para fines de análisis de datos. Se utiliza principalmente para comparar mediciones continuas para determinar si se muestrean a partir de la misma distribución o de diferentes distribuciones. Esto se suele hacer sobre un conjunto de datos que consta de numerosas muestras y/o grupos. ANOVA de factor único analiza la diferencia entre “grupos” que pueden ser diferentes unidades, sensores, etc. Por otro lado ANOVA de 2 vías analiza las diferencias en base tanto a muestras (ensayos) como a grupos, lo que permite comparar, cuál afecta más al sistema. El ANOVA bidireccional se puede emplear para dos casos diferentes: datos con o sin réplicas. Los datos sin réplicas se utilizan cuando se recopila un solo punto de datos para una condición especificada, mientras que los datos con réplicas se utilizan al recopilar múltiples puntos de datos para una condición específica.

El resultado revelador del análisis ANOVA es un valor p que le indica si hay una diferencia estadísticamente significativa entre los conjuntos de datos. Aquí se puede ver una discusión más profunda sobre los usos e implementación del ANOVA. [1]

Muestreo de números aleatorios

Una técnica numérica útil es el generador de números aleatorios. Este generador utiliza muestreo de números aleatorios para crear un modelo predictivo. Esto puede ser especialmente útil en problemas de probabilidad.

Un generador típico de números aleatorios produce cualquier número entre 0 y 1 con igual frecuencia. En cada paso se genera un número aleatorio que recoge un posible resultado. Una declaración típica de Excel aparecería como la siguiente: IF (RAND () <X, valor1, valor2). Esto significa que X por ciento del tiempo, obtendrías un valor1 y 1-X por ciento del tiempo, obtendrías un valor2. Por ejemplo, supongamos que existe una situación con dos posibles resultados, A y B, donde A tiene lugar el 70% del tiempo. Si se genera un número aleatorio que es mayor a 0.3, entonces el resultado es A. Inversamente si el número aleatorio es menor o igual a 0.3, entonces B es el resultado. Esto se representaría como IF (RAND () <0.7, A, B). Cada paso dentro del generador no afecta al siguiente paso, es decir, cada paso se determina individualmente. Un proceso en el que los resultados previos no afectan los resultados posteriores se conoce como método estocástico o método “Montecarlo”. Una simulación de Montecarlo utiliza un modelo que toma entrada aleatoria que sigue una distribución supuesta (en este caso, 70% A y 30% B) y produce salida que modela algún fenómeno. El siguiente es un ejemplo sencillo de tal simulación:

El Excel está equipado con un generador de números aleatorios que se puede llamar usando la función RAND. Un número aleatorio entre 0 y 1 se puede generar en una celda mediante el siguiente comando:

En Microsoft Excel 2007 la función RAND devuelve un nuevo número aleatorio cada vez que se calcula la hoja de trabajo o cuando se presiona F9. Si se necesita hacer referencia al mismo número aleatorio más de una vez en una instrucción Excel IF, AND, OR, se puede crear una columna de números aleatorios usando =RAND (), y hacer referencia de nuevo a estas celdas en una columna adyacente.

Hay muchas formas de manipular los números aleatorios que produce Excel. Se puede generar un número aleatorio dentro de un rango diferente multiplicando la función RAND por una constante, es decir, multiplicar por 100 para cambiar un decimal a porcentaje. Otra opción para generar números dentro de un rango diferente es la función RANDBETHEN. RANDBETHEN genera un entero aleatorio dentro de un rango especificado. El siguiente comando genera un entero aleatorio entre -6 y 21:

También es posible modificar sus números aleatorios anidando la función RAND dentro de otra función. Por ejemplo, se pueden cuadrar los resultados de una distribución para crear una distribución diferente de números.

Otra forma de utilizar la función RAND es tomar un muestreo aleatorio de un conjunto de datos. Si uno tiene una hoja de cálculo con varios cientos de puntos de datos, y quisiera analizar solo 100 puntos aleatorios, se puede crear otra columna titulada “Número aleatorio” o algo similar. En la primera celda de esta columna, escriba la función =RAND (). A continuación, arrastre esta función hasta el final del conjunto de datos. En este punto, el usuario puede ir al menú de arrastre DATA y seleccionar Ordenar... En este menú, selecciona tu columna de número aleatorio en el menú desplegable ORDENAR POR. Se puede seleccionar ya sea ascendente o descendente, según se desee. Una vez que la hoja de cálculo está ordenada por las celdas numeradas al azar, los primeros 100 puntos de datos se pueden usar como una sección transversal estadísticamente aleatoria de los datos.

La herramienta de clasificación puede ser útil en los casos en los que se está tratando de averiguar la probabilidad de tener ciertos valores por encima de un cierto umbral. Por ejemplo, si los 100 puntos de datos que tiene representan las variaciones de temperatura que ocurren dentro de un sistema, puede usar la herramienta de clasificación para determinar cuántos valores de temperatura superan su umbral. A partir de eso, se puede calcular aproximadamente la probabilidad de que la temperatura del sistema supere el umbral. A continuación se muestran dos capturas de pantalla del proceso de clasificación. Microsoft Excel 2007 utiliza un menú diferente, lo que permite elegir entre ordenar de más grande a más pequeño o viceversa. Una captura de pantalla a continuación muestra este nuevo menú.

Configuración de clasificación:

Resultados a Aleatorización:

Microsoft Excel 2007:

Funcionado Ejemplo 1

La ecuación de Arrhenius define la relación entre una constante de velocidad de reacción k y la temperatura:

\[k(T)=A e^{-E / R T} \nonumber \]

donde T es la temperatura absoluta, A es el factor de frecuencia, E es la energía de activación y R es la constante universal del gas.

Frecuentemente es deseable poder predecir velocidades de reacción a una temperatura dada, pero primero es necesario conocer los valores de A y E. Una forma de obtener estos valores es determinar la constante de velocidad de reacción a unas pocas temperaturas y luego realizar una regresión no lineal para ajustar los datos a la ecuación de Arrhenius. Dados los siguientes datos constantes de velocidad para la descomposición del cloruro de benceno diazonio, determinar el factor de frecuencia y la energía de activación para la reacción.

\[\\ (\mbox{s}^{-1}) \nonumber \]	\[\\ (\mbox{K}) \nonumber \]
0.00043	313.0
0.00103	319.0
0.00180	323.0
0.00355	328.0
0.00717	333.0

Solución: El siguiente archivo Excel contiene una solución al problema que utiliza la herramienta solucionadora de Excel para realizar la regresión no lineal y determinar los valores de A y E:

Ejemplo 1

La hoja de cálculo se configura como se describe en la sección de regresión no lineal anterior.

1. Solver abierto (Herramientas/Solver)

2. Establecer la celda de valor Suma de Residuales Cuadrados como la celda de destino

3. Haga clic en el botón de radio Min

4. Establezca el factor preexponencial y la energía de activación como celdas ajustables. Para este problema, mantenga A entre 1E+13 y 1E+14 s^-1 y E entre 9.5E+4 y 1.05E+5 J

5. En ocasiones, dependiendo de qué versión de excel estés usando, aparecerá un mensaje como el de abajo. Si esto sucede, haga clic en Aceptar. El gráfico se actualizará con las nuevas curvas.

6. Haga clic en Resolver y observe los valores de A y E calculados por Solver y los cambios en la gráfica mostrando la constante de velocidad en función de la temperatura.

NOTA: Si solver no ajusta bien los datos, haga clic en el botón de opciones en la ventana del solucionador y haga que el valor de precisión sea más pequeño en varios órdenes de magnitud.

Funcionado Ejemplo 2

La cinética se puede utilizar para determinar la probabilidad de que se produzca una reacción entre moléculas en fase gaseosa. Considera las moléculas A y B, que tienen un 60% de probabilidad de reaccionar ante la colisión. Cree una hoja de cálculo de Excel que utilice un generador de números aleatorios y una sentencia if para modelar una colisión individual.

Solución

En este archivo de Excel, se muestra la codificación de muestra para el generador de números aleatorios y la sentencia if.

Collision_ex

El generador de números aleatorios se utiliza para generar números entre 0 y 1 para cuatro ensayos, diez ensayos y cien ensayos en las hojas uno, dos y tres, respectivamente. La fórmula para el generador de números aleatorios se emplea en la celda donde desea que se emita el número (en este caso, B6 es la celda). Luego se emplea una prueba lógica IF para probar si el número sigue el requisito o no. La celda de instrucción IF emitirá entonces el resultado correspondiente en función de la prueba lógica. En este caso de colisión, si la función de número aleatorio generó un número mayor que 0.4, la prueba lógica devolverá TRUE y luego emitirá “Reacciona” como resultado. Suponiendo que el generador de números aleatorios produce una distribución par entre 0 y 1, el número aleatorio será mayor que 0.4 sesenta por ciento del tiempo. Se muestra la codificación de la sentencia IF:

Por otro lado, si el número generado es menor a 0.4, la prueba lógica devuelve FALSE y se mostrará “No Reacciona” como resultado. Se generan diferentes números cada vez que se actualiza la hoja de cálculo, por lo que se devolverán diferentes resultados para la función IF. También se pueden modificar los números y la declaración return para entender mejor el funcionamiento de las funciones.

El número de reacciones se resume y se divide entre el número total de ensayos para comparar la probabilidad de reacción predicha con el valor dado en el problema. Se puede ver comparando las hojas uno, dos y tres que aumentar el número de ensayos disminuye la variación en la probabilidad de reacción predicha.

Acceder a las herramientas de Excel en Windows

En muchas computadoras, las herramientas de excel como solver y el paquete de análisis de datos no están ya incluidas y deben instalarse antes de usarse. En esta sección se repasará cómo acceder a estos complementos para aquellos que usan Windows en lugar de Macs.

1) Haga clic en el símbolo de ventanas en Excel 2007. En la parte inferior de la ventana abierta, seleccione el cuadro Opciones de Excel.

2) En el panel lateral izquierdo verás una sección etiquetada como “Complementos”. Seleccione esto.

3) Aquí verás una lista de “Aplicaciones inactivas” que están disponibles para excel. De estos los que nos interesan son el “Solver Add-In” y el “Analysis ToolPak”. Seleccione uno de estos y haga clic en “Ir” en la parte inferior de la ventana.

4) Aparecerá una nueva ventana preguntándote qué componentes del paquete deseas instalar. Seleccione las herramientas necesarias (solucionador y toolpak de análisis). Haga clic en “Ok” y windows deberá instalar las herramientas correspondientes en excel.

5) Una vez hecho esto, estas herramientas se encontrarán bajo la sección de datos de Excel, en el extremo derecho de la ventana.

Referencias

1. =RAND ()

• =RANDBETWEEEN (-6,21)
• Archivo Excel de ejemplo de colisión:
• =SI (B6>=0.4, "Reacciona”, "No reacciona”)
• Bender, E.A. Una introducción al modelado matemático, Mineola, NY: Dover Publications, Inc.
• Fogler, H.S. (2006). Elementos de Ingeniería de Reacciones Químicas, Upper Saddle River, NJ: Prentice Hall Referencia Técnica Profesional. ISBN 0-13-047394-4
• Microsoft, Excel, EULA Propietaria, www.microsoft.com.

Colaboradores y Atribuciones

Autores: (12 Septiembre 2006/Fecha de revisión: 19 Septiembre 2006) Curt Longcore, Ben Van Kuiken, Jeffrey Carey, Angela Yeung

Stewards: (13 de septiembre de 2007) So Hyun Ahn, Kyle Goszyk, Michael Peterson, Samuel Seo