Excel Para la Toma de Decisiones

Además de las fórmulas, otra forma de realizar cálculos matemáticos en Excel es a través de funciones. Las funciones estadísticas aplican un proceso matemático a un grupo de celdas en una hoja de trabajo. Por ejemplo, la función SUMA se usa para agregar los valores contenidos en un rango de celdas. En la Tabla 2.4 se muestra una lista de funciones estadísticas de uso común. Las funciones son más eficientes que las fórmulas cuando se aplica un proceso matemático a un grupo de celdas. Si usa una fórmula para agregar los valores en un rango de celdas, tendría que agregar cada ubicación de celda a la fórmula de una en una. Esto puede llevar mucho tiempo si tiene que agregar los valores en unos pocos cientos de ubicaciones de celda. Sin embargo, cuando usa una función, puede resaltar todas las celdas que contienen valores que desea sumar en un solo paso. Esta sección muestra una variedad de funciones estadísticas que agregaremos al libro de Presupuesto Personal. Además de demostrar funciones, esta sección también revisa el porcentaje de cálculos totales y el uso de referencias absolutas.

Cuadro 2.4 Comúnmente Utilizados Funciones Estadísticas

La función SUMA se usa cuando necesita calcular totales para un rango de celdas o un grupo de celdas seleccionadas en una hoja de trabajo. Con respecto a la hoja de trabajo de Detalle del presupuesto, usaremos la función SUMA para calcular los totales en la fila 12. Es importante tener en cuenta que hay varios métodos para agregar una función a una hoja de trabajo, que se demostrarán a lo largo del resto de este capítulo. A continuación, se ilustra cómo se puede agregar una función a una hoja de trabajo al escribirla en una ubicación de celda:

1. Haga clic en la pestaña Hoja de trabajo con detalles de presupuesto para abrir la hoja de trabajo.
2. Haga clic en la celda C12.
3. Escriba un signo igual =.
4. Escriba el nombre de la función SUM.
5. Escriba un paréntesis abierto (.
6. Haga clic en la celda C3 y arrastre hacia abajo hasta la celda C11. Esto coloca el rango C3: C11 en la función.
7. Escriba un paréntesis de cierre ).
8. Pulse la tecla ENTER. La función calcula el total de la columna de gasto mensual, que es de $1,496.

La figura 2.11 muestra la apariencia de la función SUMA agregada a la hoja de cálculo de Detalles de presupuesto antes de presionar la tecla ENTER.

Como se muestra en la Figura 2.11, la función SUMA se agregó a la celda C12. Sin embargo, esta función también es necesaria para calcular los totales en las columnas Gasto anual y Gasto anual. La función se puede copiar y pegar en estas ubicaciones de celda debido a la referencia relativa. La referencia relativa sirve para el mismo propósito para las funciones que para las fórmulas. A continuación se muestra cómo se completa la fila total:

1. Haga clic en la celda C12 en la hoja de trabajo de Detalles del presupuesto.
2. Haga clic en el botón Copiar en la pestaña Inicio de la Cinta.
3. Resalte las celdas D12 y E12.
4. Haga clic en el botón Pegar en la pestaña Inicio de la Cinta. Esto pega la función SUMA en las celdas D12 y E12 y calcula los totales para estas columnas.
5. Haga clic en la celda F11.
6. Haga clic en el botón Copiar en la pestaña Inicio de la Cinta.
7. Haga clic en la celda F12, luego haga clic en el botón Pegar en la pestaña Inicio de la Cinta. Dado que ahora tenemos totales en la fila 12, podemos pegar la fórmula de cambio de porcentaje en esta fila.

La figura 2.12 muestra la salida de la función SUMA que se agregó a las celdas C12, D12 y E12. Además, la fórmula de cambio porcentual se copió y pegó en la celda F12. Tenga en cuenta que esta versión del presupuesto está planeando una disminución del 1,7% en el gasto en comparación con el año pasado.Los totales de

Referencias absolutas(Calculando el Porcentaje de los Totales)

Archivo de datos: Continúe con el Presupuesto Personal de CH2.

Dado que los totales se agregaron a la fila 12 de la hoja de cálculo de Detalles del presupuesto, se puede agregar un porcentaje del cálculo total a la Columna B que comienza en la celda B3. El porcentaje del cálculo total muestra el porcentaje para cada valor en la columna Gasto anual con respecto al total en la celda D12. Sin embargo, después de crear la fórmula, será necesario desactivar la función de referencia relativa de Excel antes de copiar y pegar la fórmula en el resto de las ubicaciones de celdas de la columna. Desactivar la función de referencia relativa de Excel se logra a través de una referencia absoluta. Los siguientes pasos explican cómo se hace esto:

1. Haga clic en la celda B3 en la hoja de trabajo de Detalles del presupuesto.
2. Escriba un signo igual =.
3. Haga clic en la celda D3.
4. Escriba una barra diagonal /.
5. Haga clic en la celda D12.
6. Pulse la tecla ENTER. Verá que los servicios públicos de los hogares representan el 16,7% del presupuesto de Gasto Anual (véase la Figura 2.14).

Figura 2.14 muestra la fórmula completa que calcula el porcentaje que representa el Gasto Anual en Servicios Públicos de los Hogares con respecto al Gasto Anual total del presupuesto (véase la celda B3). Normalmente, copiaríamos esta fórmula y la pegaríamos en el rango B4:B11. Sin embargo, debido a la referencia relativa, ambas referencias de celda aumentarán en una fila a medida que la fórmula se pegue en las celdas debajo de B3. Esto está bien para la primera referencia de celda de la fórmula (D3), pero no para la segunda referencia de celda (D12). La figura 2.15 ilustra lo que sucede si pegamos la fórmula en el rango B4:B12 en su estado actual. Observe que Excel produce el código de error # DIV/0. Esto significa que Excel está tratando de dividir un número por cero, lo cual es imposible. Mirando la fórmula en la celda B4, ve que la primera referencia de celda se cambió de D3 a D4. Esto está bien porque ahora queremos dividir el Gasto Anual para Seguros por el Gasto Anual total en la celda D12. Sin embargo, Excel también ha cambiado la referencia de celda D12 a D13. Debido a que la ubicación de la celda D13 está en blanco, la fórmula produce el código de error #DIV/0.

Para eliminar el error de división por cero que se muestra en la Figura 2.15, debemos agregar una referencia absoluta a la celda D12 en la fórmula. Una referencia absoluta impide que la referencia relativa cambie una referencia de celda en una fórmula. Esto también se conoce como bloquear una celda. A continuación se explica cómo se logra esto:

1. Haga doble clic en la celda B3.
2. Coloque el puntero del ratón delante de D12 y haga clic. El cursor parpadeante debe estar delante de la D en la referencia de celda D12.
3. Pulse la tecla F4. Verá un signo de dólar ($) añadido delante de la letra de columna D y el número de fila 12. También puede escribir los signos de dólar delante de la letra de la columna y el número de fila.
4. Pulse la tecla ENTER.
5. Haga clic en la celda B3.
6. Haga clic en el botón Copiar en la pestaña Inicio de la Cinta.
7. Resalte el rango B4: B11.
8. Haga clic en el botón Pegar en la pestaña Inicio de la Cinta.

La figura 2.16 muestra el porcentaje de la fórmula total con una referencia absoluta añadida a D12. Observe que en la celda B4, la referencia de celda sigue siendo D12 en lugar de cambiar a D13, como se muestra en la Figura 2.15. Además, verá que los porcentajes se están calculando en el resto de las celdas de la columna, y el error de dividir por cero ahora se elimina.

La función de RECUENTO

Archivo de datos: Continúe con CH2 Presupuesto personal.

La siguiente función que agregaremos a la hoja de cálculo de Detalles del presupuesto es la función COUNT. La función COUNT se utiliza para determinar cuántas celdas de un rango contienen una entrada numérica. La función COUNT no funcionará para contar texto u otras entradas no numéricas. Para la hoja de trabajo de Detalle del presupuesto, usaremos la función CONTAR para contar el número de elementos que se planifican en la columna Gasto anual (Columna D). A continuación, se explica cómo se agrega la función COUNT a la hoja de trabajo mediante la lista de funciones:

1. Haga clic en la celda D13 en la hoja de trabajo de Detalles del presupuesto.
2. Escriba un signo igual =.
3. Escriba la letra C.
4. Haga clic en la flecha hacia abajo en la barra de desplazamiento de la lista de funciones (consulte la Figura 2.17) y encuentre el NÚMERO de palabras.
5. Haga doble clic en el RECUENTO de palabras de la lista de funciones.
6. Resalte el rango D3: D11.
7. Puede escribir un paréntesis de cierre ) y luego presione la tecla ENTER, o simplemente presione la tecla ENTER y Excel cerrará la función por usted. La función produce una salida de 9, ya que hay 9 elementos planificados en la hoja de trabajo.

Figura 2.17 muestra el cuadro de lista de funciones que aparece después de completar los pasos 2 y 3 para la función COUNT. La lista de funciones proporciona un método alternativo para agregar una función a una hoja de trabajo.

La Figura 2.18 muestra la salida de la función de CONTEO después de presionar la tecla ENTER. La función cuenta el número de celdas en el rango D3:D11 que contienen un valor numérico. El resultado de 9 indica que hay 9 categorías previstas para este presupuesto.

La función PROMEDIO

La siguiente función que agregaremos a la hoja de trabajo de Detalle de presupuesto es la función PROMEDIO. Esta función se utiliza para calcular la media aritmética de un grupo de números. Para la hoja de trabajo de Detalle del presupuesto, usaremos la función para calcular el promedio de los valores en la columna Gasto anual. Agregaremos esto a la hoja de trabajo utilizando la Biblioteca de funciones. Los siguientes pasos explican cómo se logra esto:

1. Haga clic en la celda D14 en la hoja de trabajo de Detalles del presupuesto.
2. Haga clic en la pestaña Fórmulas de la Cinta.
3. Haga clic en el botón Más funciones del grupo de comandos Biblioteca de funciones.
4. Coloque el puntero del ratón sobre la opción Estadística de la lista desplegable de opciones.
5. Haga clic en el nombre DE función PROMEDIO de la lista de funciones que aparecen en el menú (consulte la Figura 2.19). Esto abre el cuadro de diálogo Argumentos de función.
6. Haga clic en el botón Contraer diálogo en el cuadro de diálogo Argumentos de función (consulte la Figura 2.20).
7. Resalte el rango D3: D11.
8. Haga clic en el botón Expandir Diálogo en el cuadro de diálogo Argumentos de función (consulte la Figura 2.21). También puede presionar la tecla ENTER para obtener el mismo resultado.
9. Haga clic en el botón ACEPTAR del cuadro de diálogo Argumentos de función. Esto agrega la función PROMEDIO a la hoja de trabajo.

La figura 2.19 ilustra cómo se selecciona una función de la Biblioteca de funciones en la pestaña Fórmulas de la Cinta de opciones.

Figura 2.20 muestra el cuadro de diálogo Argumentos de función. Esto aparece después de seleccionar una función de la Biblioteca de funciones. El botón Contraer diálogo se usa para ocultar el cuadro de diálogo para que se pueda resaltar un rango de celdas en la hoja de trabajo y luego agregarlas a la función.

La figura 2.21 muestra cómo se puede seleccionar un rango de celdas del cuadro de diálogo Argumentos de función una vez que se ha contraído.

Figura 2.22 muestra el cuadro de diálogo Argumentos de función después de definir el rango de celdas para la función PROMEDIO. El cuadro de diálogo muestra el resultado de la función antes de agregarla a la ubicación de la celda. Esto le permite evaluar la salida de la función para determinar si tiene sentido antes de agregarla a la hoja de trabajo.

La figura 2.23 muestra la función PROMEDIO completada en la hoja de cálculo de Detalles de presupuesto. El resultado de la función muestra que, en promedio, esperamos gastar 1.994 dólares por cada una de las categorías enumeradas en la columna A del presupuesto. Este cálculo de gasto promedio por categoría se puede usar como indicador para determinar qué categorías cuestan más o menos que el gasto promedio presupuestado en dólares.

Las funciones MAX y MIN

Archivo de datos: Continúe con el Presupuesto personal de CH2.

Las dos funciones estadísticas finales que agregaremos a la hoja de cálculo de Detalles del presupuesto son las funciones MAX y MIN. Estas funciones identifican los valores más altos y más bajos en un rango de celdas. Los siguientes pasos explican cómo agregar estas funciones a la hoja de cálculo de Detalles del presupuesto:

1. Haga clic en la celda D15 en la hoja de trabajo de Detalles del presupuesto.
2. Escriba un signo igual =.
3. Escriba la palabra MIN.
4. Escriba un paréntesis abierto (.
5. Resalte el rango D3: D11.
6. Escriba un paréntesis de cierre ) y presione la tecla ENTER, o simplemente presione la tecla ENTER y Excel cerrará la función por usted. La función MIN produce una salida de $1,200, que es el valor más bajo en la columna de Gasto anual (consulte la Figura 2.24).
7. Haga clic en la celda D16.
8. Escriba un signo igual =.
9. Escriba la palabra MAX.
10. Escriba un paréntesis abierto (.
11. Resalte el rango D3: D11.
12. Escriba un paréntesis de cierre ) y presione la tecla ENTER, o simplemente presione la tecla ENTER y Excel cerrará la función por usted. La función MAX produce una salida de 3 3,500. Este es el valor más alto de la columna de Gasto anual (véase la Figura 2.25).

Copiar y Pegar Fórmulas(Pegar sin formatos)

Archivo de datos: Continúe con CH2 Presupuesto personal.

Como se muestra en la Figura 2.25, las funciones COUNT, AVERAGE, MIN y MAX resumen los datos en la columna Gasto anual. También notará que hay espacio para copiar y pegar estas funciones bajo la columna LY Spend. Esto nos permite comparar lo que gastamos el año pasado y lo que planeamos gastar este año. Normalmente, simplemente copiaríamos y pegaríamos estas funciones en el rango E13: E16. Sin embargo, es posible que haya notado el borde de estilo de doble línea que se usó alrededor del perímetro del rango B13:E16. Si usamos el comando Pegar normal, la línea doble en el lado derecho del rango E13:E16 se reemplazaría por una sola línea. Por lo tanto, vamos a usar uno de los comandos especiales Pegar para pegar solo las funciones sin ninguno de los tratamientos de formato. Esto se logra a través de los siguientes pasos:

1. Resalte el rango D13: D16 en la hoja de trabajo de Detalles del presupuesto.
2. Haga clic en el botón Copiar en la pestaña Inicio de la Cinta.
3. Haga clic en la celda E13.
4. Haga clic en la flecha hacia abajo debajo del botón Pegar en la pestaña Inicio de la Cinta.
5. Haga clic en la opción Fórmulas de la lista desplegable de botones (consulte la Figura 2.26).

La figura 2.26 muestra la lista de botones que aparecen al hacer clic en la flecha hacia abajo debajo del botón Pegar en la pestaña Inicio de la Cinta de opciones. Una cosa a tener en cuenta sobre estas opciones es que puede previsualizarlas antes de hacer una selección arrastrando el puntero del ratón sobre las opciones. Como se muestra en la figura, cuando el puntero del ratón se coloca sobre el botón de Fórmulas, puede ver cómo aparecerán las funciones antes de realizar una selección. Observe que el borde de doble línea no cambia cuando se previsualiza esta opción. Es por eso que esta selección se realiza en lugar de la opción de pegar normal.