Lección 5: Gráficos y funciones avanzados y análisis de

hipótesis

Elementos avanzados de gráfico

Aplicar formato a un gráfico sencillo

Aprender a crear y personalizar un

gráfico sencillo
Este ejercicio es un repaso sobre cómo crear un gráfico, moverlo y cambiar su tamaño, y realizar algunas
personalizaciones comunes.

: Por favor tengan el archivo Ventas por tipo y año Gráfico sencillo abierto en pantalla.

1. Crear un gráfico.

1 Haga clic en una celda en el rango A5:J14, luego en la pestaña Insertar, en el grupo Gráficos, haga
clic en Insertar gráfico de columna o de barras. Haga clic en Columna agrupada en la selección
en la Columna en 2-D.

2 Haga clic y arrastre el gráfico a la ubicación nueva en la hoja de cálculo con la esquina superior
izquierda en la celda A17.

3 Haga clic y arrastre el controlador de la esquina inferior derecho hasta la celda L40.

2. Cambiar los datos por el eje horizontal.

4 Bajo Herramientas de gráficos, en la pestaña Diseño, en el grupo Datos, haga clic en Cambiar entre
filas y columnas.

5 Seleccione la etiqueta Título del gráfico, y cambie la etiqueta a: Ventas por tipo y año.

El gráfico incluye la fila Total ya que estaba directamente bajo el resto de los datos. Está sesgando
el gráfico al crear las columnas mu altas

3. Elimina una fila para ver otras columnas de datos.

6 Bajo Herramientas de gráficos, en la pestaña Diseño, en el grupo Datos, haga clic en Seleccionar
datos.

etciberoamerica
7 En el cuadro de diálogo Seleccionar origen de datos, desplácese al final de la lista Entradas de
leyenda (Series). Haga clic en un lugar de la línea Total (excepto por la casilla a la izquierda) y haga
clic en Quitar.

8 Haga clic en Aceptar para cerrar el cuadro de diálogo.

La pantalla debe ser similar al siguiente ejemplo:

etciberoamerica
Agregar un eje vertical secundario
Objetivo 4.1.2

Aprender a establecer una eje Y

secundario
La gerente del departamento de mercadotecnia de Aventuras Tolano ha estado revisando sus datos de
ventas de los últimos años buscando un patrón. En este ejercicio modificará un gráfico como un gráfico
combinado y utilizará un eje Y secundario.

: Por favor tengan el archivo Ventas por tipo y año Eje secundario abierto en pantalla.

1. Agregar una nueva fila de datos al gráfico.

1 Haga clic en un área en blanco del gráfico para seleccionarlo.

2 Bajo Herramientas de gráficos, en la pestaña Diseño, en el grupo Datos, haga clic en Seleccionar
origen de datos.

3 En el cuadro de diálogo Seleccionar origen de datos, haga clic en Agregar.

4 En el cuadro de diálogo Modificar serie, haga clic en el cuadro de texto Nombre de la serie,
luego haga clic en la celda A16.

5 Elimine el contenido actual del cuadro de texto Valores de la serie, seleccione las celdas B16 a
J16, luego haga clic en Aceptar.

6 En el cuadro de diálogo Seleccionar origen de datos, haga clic en Aceptar para agregar esta
nueva fila de dato al gráfico.

Estos datos nuevos representan un gasto, a diferencia de las demás series de datos que
representan ingresos. Por tanto, tendría más sentido mostrarlo como tipo de gráfico diferente
con su propio eje Y por separado.

2. Cambiar el tipo de gráfico.

etciberoamerica
1 Bajo Herramientas de gráficos, en la pestaña Diseño, en el grupo Tipo, haga clic en Cambiar tipo
de gráfico.

2 En el cuadro de diálogo Cambiar tipo de gráfico, haga clic en el tipo de gráfico Cuadro
combinado del lado izquierdo.

Al cambiar por el tipo de gráfico combinado, Excel cambia la segunda mitad de las series de dato
por el tipo de línea.

3. Cambiar manualmente los tipos de gráfico.

1 Desplácese hacia abajo en el cuadro de lista Elija el tipo de gráfico y el eje para la seria de datos,
y asegúrese de que Tipo de gráfico para cada serie sea Columna agrupada. La última, Publicidad
y promociones (al fina) debería ser Línea.

2 Haga clic en la casilla Eje secundario de la serie Publicidad y promociones, y haga clic en Aceptar.

La pantalla debe ser similar al siguiente ejemplo:

Observe que, de haber dejado la nueva fila de datos como otra columna de barra, su importancia
no sería obvia. Al cambiar la serie de datos a una línea, ahora puede ver un patrón muy distinto.
La cantidad de Publicidad y promociones sube y baja en proporción directa a las ventas de los
distintos tipos de viaje.

etciberoamerica
Plantillas de gráfico personalizado
Objetivo 4.1.3

Aprender a guardar un gráfico como

una plantilla
Este ejercicio demuestra cómo guardar un gráfico como una plantilla y cómo utilizar esa plantilla para crear
gráficos nuevos.

: Por favor tengan el archivo Ventas por tipo y año Crear plantilla con la hoja 1 abierta en
pantalla.

1. Personalizar este gráfico.

1 Haga clic en un área en blanco del gráfico para seleccionarla.

2 Bajo Herramientas de gráficos, en la pestaña Diseño, en el grupo Estilos de diseño, haga clic en
Estilo 5.

3 Haga clic derecho en un área en blanco del gráfico y haga clic en Formato del área del gráfico
en el menú contextual.

4 En el panel Formato del área del gráfico, haga clic en el icono Relleno y línea, luego abra el
menú de opciones de Relleno.

5 Haga clic en Relleno degradado, seleccione el color Verde claro (Colores estándar, quinto
desde la izquierda), y cambie Ángulo a 45°.

6 Cierre el panel Formato del área del gráfico.

2. Guardar el gráfico como una plantilla.

1 Haga clic derecho en un área en blanco del gráfico, y haga clic en Guardar como plantilla.

2 En el cuadro de texto Nombre de archivo, escriba: Ventas y publicidad Combinado y haga clic
en Guardar.

etciberoamerica
 Observe que esta plantilla de gráfico se guarda en la carpeta de plantillas privadas; esto es, solo
usted tiene acceso a esta plantilla desde esta ubicación.

3 Cierre el libro Venta por tipo y año Crear plantilla y descarte todos los cambios.

3. Abrir un libro para usarlo con una plantilla de gráfico.

1 Abra el libro Gastos de publicidad y promociones.

2 Seleccione una celda en el rango A5:E12.

El cuadro de diálogo Insertar gráfico es el único lugar desde el que puede tener acceso a las
plantillas de gráfico definidas por el usuario.

3 En la pestaña Insertar, en el grupo Gráficos, haga clic en el iniciador de cuadro de diálogo Ver
todos los gráficos.

4 Haga clic en la pestaña todos los gráficos, luego haga clic en la categoría Plantillas en el lado
izquierdo del cuadro de diálogo Insertar gráfico.

5 Haga clic en el icono Ventas y publicidad Combinado en la sección Mis plantillas para
seleccionarlo, y haga clic en Aceptar.

El gráfico se muestra ahora en la hoja de cálculo actual, utilizando el mismo formato que el
gráfico origina

4. Mover un gráfico a una hoja nueva.

1 Bajo Herramientas de gráficos, en la pestaña Diseño, en el grupo Ubicación, haga clic en Mover
gráfico.

2 Haga clic en Hoja nueva y haga clic en Aceptar.

etciberoamerica
 El formato de gráfico que no se transmite en la plantilla de gráfico es el título del gráfico (más
que una etiqueta predeterminada, la selección de series de datos, y el tipo de gráfico para la
serie de datos individual en un gráfico combinado (como el gráfico original utilizado para crear
esta plantilla).

5. Aplicar formato al gráfico nuevo.

1 Seleccione el título de gráfico y reemplace el texto predeterminado con: Gatos de publicidad.

2 En la pestaña Diseño, en el grupo Datos, haga clic en Cambiar entre filas y columnas.

3 Haga clic en una de las barras en la serie de datos Total (las barras más altas).

4 Bajo Herramientas de gráficos, en la pestaña Diseño, en el grupo Tipo, haga clic en Cambiar
tipo de gráfico.

5 En el cuadro de diálogo Cambiar tipo de gráfico, desplácese hacia abajo hasta el final del cuadro
de lista elija el tipo de gráfico y el eje para la serie de datos, cambie la serie de datos Total a
Línea, y haga clic en la casilla Eje secundario. Haga clic en Aceptar.

El gráfico terminado debe ser similar al siguiente ejemplo:

6. Administrar plantillas de gráfico.

1 Haga clic otra vez en la etiqueta Hoja1.

2 En la pestaña Insertar, en el grupo Gráficos, haga clic en el iniciador de cuadro de diálogo Ver
todos los gráficos.

etciberoamerica
3 Haga clic en la pestaña Todos los gráficos, y haga clic en la categoría Plantilla. Haga clic en el
botón Administrar plantillas.

Una ventana del Explorador muestra ahora la carpeta en donde se ubica la plantilla. Desde aquí
puede copiarla o moverla a otra ubicación, o eliminarla.

4 Cierre la ventana del Explorador y haga clic en Cancelar en el cuadro de diálogo Insertar gráfico.

etciberoamerica
Gráfico de líneas de tendencia
Objetivo 4.1.1

Aprender a crear y a predecir una línea

de tendencia en un gráfico
Este ejercicio demuestra cómo crear una línea de tendencia en un gráfico, y cómo utilizarla para crear una
predicción en el futuro.

: Por favor tengan el libro Ventas por tipo y año Línea de tendencia abierto en pantalla.

1. Agregar una línea de tendencia al gráfico.

1 Haga clic en un área en blanco del gráfico para seleccionarlo.

2 Bajo Herramientas de gráficos, en la pestaña Diseño en el grupo diseño de gráfico, haga clic en
Agregar elemento de gráfico, mueva el mouse sobre Línea de tendencia y haga clic en Lineal.

3 En el cuadro de diálogo Agregar línea de tendencia, haga clic en Paquetes vacacionales

estadounidenses, y haga clic en Aceptar.

Su hoja de cálculo debe ser similar a la siguiente:

etciberoamerica
 Esta línea muestra una tendencia descendente en las ventas entre el principio y el final del
periodo que se analiza. La línea se calcula utilizando una fórmula matemática sofisticada. Sin
embargo, los años más recientes indican una tendencia ascendente. Quizá una línea de
tendencia distinta puede estar más adecuada para estos datos.

2. Aplicar formato a la línea de tendencia.

1 Haga clic derecho en la línea de tendencia, y haga clic en Formato de línea de tendencia.

2 En el panel Formato de línea de tendencia, haga clic en Media móvil, luego cambie el número
de Periodo a 4.

El gráfico ahora debe ser similar al siguiente:

La línea de tendencia representa una media móvil de 4 años. Por ejemplo, el primer punto de
datos en la línea de tendencia es la media de los cuatro años anteriores, desde 2008 hasta 2011.
El segundo punto de datos es la media desde 2009 hasta 2012. Esta línea de tendencia indica

etciberoamerica
 que las ventas de los paquetes vacacionales estadounidenses han superado su punto bajo y
ahora están en una tendencia ascendente.

3 Haga clic en cada una de las demás opciones de línea de tendencia y observe cómo cambiar la
línea de tendencia en el gráfico.

La línea de tendencia también puede ser utilizada para crear una predicción de valores futuros,
con base en la dirección y el rango de cambio en los datos actuales.

4 Haga clic en la opción de línea de tendencia Polinómica.

5 Desplácese hacia abajo en el panel Formato de línea de tendencia, haga clic en el cuadro de
entrada Extrapolar, En el futuro, reemplace el valor actual con: 5 y presione la tecla TAB.

El gráfico ahora se ha ampliado para agregar otros 5 años al eje X-axis. Ya que la tabla de datos
no cuenta con ningún número para esos años adicionales, las etiquetas de datos no aparecen
a lo largo de ese eje.

6 Incremente el valor Grado de Polinómica a 3.

Observe que la escala en el eje Y primario ha cambiado para incluir el nuevo rango de la línea
de tendencia, saltando hasta 1,000,000 lo que indica unas ventas proyectadas muy grande en
los próximos 5 años.

3. Agregar otra línea de tendencia en el gráfico.

1 Haga clic en un área en blanco del gráfico para quitar la selección de la línea de tendencia.

2 Bajo Herramientas de gráficos, en la pestaña Diseño, en el grupo Diseños de gráfico, haga clic
en Agregar elemento de gráfico, mueva el mouse sobre Línea de tendencia y haga clic en
Exponencial.

3 En el cuadro de diálogo Agregar línea de tendencia, haga clic en Cruceros, y haga clic en
Aceptar.

4 Haga clic en la nueva línea de tendencia azul (cruceros) para seleccionarla.

5 En el panel Formato de línea de tendencia, asegúrese de que esté viendo el panel Opciones de
línea de tendencia, luego haga clic en el cuadro de texto Extrapolar, En el futuro, reemplace
el valor predeterminado a 5, y presione la tecla TAB.

El gráfico debe ser similar al siguiente:

etciberoamerica
 El gráfico ahora muestra una línea de tendencia verde que indica que las ventas de los paquetes
de las vacaciones estadounidenses se aproximarán a los $1,000,000 para el año 2024, mientras
que la línea de tendencia azul indica que las ventas de los cruceros se desviarán a alrededor de
$180,000 en ese mismo periodo.

6. Ajustar la extrapolación para las dos líneas de tendencia.

1 Con la línea de tendencia azul todavía seleccionada, haga clic en Polinómica, e incremente
Grado a 3.

2 Haga clic en la línea de tendencia verde (paquetes de vacaciones estadounidenses), y haga clic
en Exponencial en el panel Formato de línea de tendencia.

La extrapolación ahora ha cambiado drásticamente: la línea de tendencia verde indica que las
ventas de los paquetes vacacionales estadounidense se desviarán a alrededor de $320,000 para
el año 2024, mientras que la línea de tendencia azul indica que las ventas de los cruceros se
dispararán a alrededor de $440,000.

Este ejercicio demuestra que las herramientas de análisis estadístico en Excel son potentes, pero
deben ser utilizadas correctamente por analistas expertos, de lo contrario, las personas tomarán
decisiones incorrectas, lo que puede llevar a resultados desastrosos.

etciberoamerica
Utilizar funciones y herramientas avanzadas
Funciones financieras
Objetivo 3.4.5

Aprender a utilizar la función PAGO

Este ejercicio demuestra el uso de la función PAGO en un préstamo utilizado para pagar un viaje de
vacaciones.

: Por favor tengan el libro Pagos mensuales abierto en pantalla.

Una tabla de análisis de préstamo le permite ver el monto del principal y el interés que se paga mensual o
anualmente en un préstamo. Una vez terminada, la hoja de cálculo puede ser utilizada para determinar
rápidamente los pagos requeridos para cualquier monto de préstamo, la tasa de interés y el periodo.

1. Introducir una función de PAGO.

1 Utilice los siguientes datos para el cálculo del pago del préstamo:
Monto de préstamo 5,000
Tasa de interés .06
Periodo del préstamo 12
Saldo final 0
Tipo de pago 0
Pago/Mes =PAGO (B2/12,B3,-B1,B4,B5)
Total/Periodo =B7*B3
Interés total =B8-B1

Observe que la tasa de interés utilizada aquí es para un año completo, pero el periodo está en
meses. La tasa de interés debe ser convertida a una tasa de interés mensual. El préstamo será
pagado por complete al final del periodo, por lo que el balance final será cero.

Nota: ya que ambos valores Saldo final y Tipo de pago son cero, los dos últimos argumentos de la
función pueden ser omitidos en este caso.

2 En la celda B13, introduzca: =B1 para representar el monto de préstamo inicial.

3 En la celda C13, introduzca: =$B$7 como el monto de pago mensual.

4 En la celda D13, introduzca: =B13*$B$2/12 para calcular el monto de interés pagado para el mes.

etciberoamerica
 Esto mostrará el monto de interés a ser pagado en el primer mes. Estará copiando esta fórmula por
lo que tendrá que realizar la entrada en la celda Tasa de interés en una referencia de celda absoluta.

5 En la celda E13, introduzca: =C13-D13 para calcular el monto del préstamo pagado para este mes al
substraer el interés pagado del pago mensual.

6 En la celda F13, escriba: =B13-E13 para calcular el saldo restante a ser pagado substrayendo el
monto pagado con relación al préstamo del salo inicial del préstamo para el mes actual.

2. Introducir las fórmulas para el resto de la tabla.

1 En la celda B14, escribe =F13 para representar el Saldo final del primer mes.

2 Copie el rango de celdas C13:F13 a las celdas C14:F14.

3 Copie el rango de celdas B14:F14 a las celdas B15:F24.

El saldo final en la celda F24 (después de que se realizó el último pago) es $0.00, mostrando que el
préstamo has sido completamente pagado.

3. Realizar algunos cálculos financieros.

1 Seleccione la celda B26, e introduzca: =VA(B2/12,B3,-B7,B4,B5)

2 Seleccione la celda B27, e introduzca: =VF(B2/12,B3,-B7,0,B5)

Ya que la función TIR requiere que se incluya el principal inicial en el rango de celdas como un valor
negativo, el monto del préstamo debe ser agregado en la parte superior de la lista de pagos.

3 Seleccione la celda C12, e introduzca: -5000

4 Seleccione la celda B28, e introduzca: =TIR(C12:C24)

La hoja de cálculo ahora debe ser similar a la siguiente:

etciberoamerica
etciberoamerica
Anidar funciones
Objetivo 3.1.2

Aprender a anidar una función

Este ejercicio demuestra cómo anidar una función dentro de otra función.

: Por favor tengan el libro Funciones de anidación con la etiqueta de hoja de cálculo BUSCAR
mostrada.

1. Introducir algunas funciones TEXTO.

En esta hoja de cálculo introducirá fórmulas de texto para extraer los nombres de la columna A y
colocarlos en la columna B, luego extraerá los apellidos de la columna A y los colocará en la columna
C. Introduzca el primer conjunto de fórmulas para la fila 4.

1 Introduzca las siguientes fórmulas:

Celda Fórmulas
B4 =EXTRAE(A4,9,5)
C4 =IZQUIERDA(A4,6)

Utilizando estas fórmulas, el nombre de la persona es correctamente separado en los

componentes del nombre y el apellido. Copie estas mismas fórmulas en las filas restantes para
extraer los nombre y apellidos para las personas restantes.

2 Copie las fórmulas en las celdas B4:C4 en el rango de celdas B5:C12.

etciberoamerica
 Los resultados demuestran que necesitará ajustar los parámetros para casi todas las fórmulas
para extraer correctamente los nombres. Las fórmulas necesitarán ser cambiada de nuevo si
uno de los nombres en la columna A se cambia posteriormente. Sin embargo, hay una mejor
forma de realizar esta tarea, utilizando funciones anidadas.
Al v Al ver los nombres en la columna A, verá que una coma siempre separa el nombre del apellido.
Cambie las fórmulas en las celdas B4 y C4 para calcular la posición de la coma en la celda A4.

3 Seleccione la celda B4 e introduzca la fórmula: =ENCONTRAR(",",A4)

4 Introduzca la misma fórmula en la celda C4 (pero no la copie desde la celda B4):

=ENCONTRAR(",",A4)

Ambas fórmulas producen exactamente los mismos resultados, como era de esperar.
Cuando ve el nombre en la celda A4, puede observar que el apellido está a la izquierda de la
coma. Entonces puede usar la función IZQUIERDA para extraer el apellido de la celda A4. La
función IZQUIERDA requiere dos parámetros: el hilo de texto original, y el número de
caracteres a extraer. El segundo parámetro será calculado por usted por la función BUSCAR, la
cual acaba de introducir en el paso 3.

5 Seleccione la celda C4 otra vez, y modifique la fórmula a:

=IZQUIERDA(A4,ENCONTRAR(",",A4))

Los resultados demuestran que la función BUSCAR regresa la posición de la coma, que sigue
inmediatamente después del apellido. Para extraer el apellido sin la coma, solo reste uno de
los resultados de la función BUSCAR.

6 Seleccione la celda C4 otra vez, y modifique la fórmula a:

=IZQUIERDA(A4,ENCONTRAR(",",A4)-1)

etciberoamerica
 Utilice la misma técnica para extraer el apellido, que está ubicado a la derecha de la coma,
utilizando la función EXTRAE. La función EXTRAE requiere de tres parámetros: el hilo de texto
original, la posición inicial, y el número de caracteres. La posición inicial puede ser calculada
de nuevo utilizando la función BUSCAR.
Dado que el nombre está a la derecha de la coma, entonces el número de caracteres a extraer
es simplemente el número de caracteres a la derecha de la coma. Esto se puede calcular al
anidar incluso otra función para calcular la longitud total del hilo de texto y substraer la
longitud del apellido. Sin embargo, la función EXTRAE es muy condescendiente; si utiliza un
número que sea mayor que el número de caracteres disponibles, solo regresará lo que hay y
no los espacios en blanco al final. Por tanto, puede utilizar cualquier número que elija, pero
debe ser lo suficientemente grande para extraer caracteres suficientes. Para este ejercicio
utilizará una longitud de 20 caracteres que puede ajustar si es necesario.

7 Seleccione la celda B4, y modifique la fórmula a: =EXTRAE(A4,ENCONTRAR(",",A4),20)

Como con el apellido, la función BUSCAR incluye la coma en su cálculo. Agregue 2 al resultado
para saltarse la coma y el espacio en blanco después de ella.

8 Seleccione la celda B4 de nuevo y modifique la fórmula:

=EXTRAE(A4,ENCONTRAR(",",A4)+2,20).

9 Copie las fórmulas en las celdas B4:C4 al rango de celdas B5:C12.

La fórmula está diseñada para ser flexible para manejar cualquier cambio de nombre, siempre
y cuando la coma esté en posición correcta.

10 Seleccione la celda A4 e introduzca: Núñez, Juan

Nota: la herramienta Relleno rápido de Excel también realizará esta misma tarea sin requerir el uso
de una fórmula. Después de que introduce el nombre y el apellido en las celdas B4 y C4, Excel
detectará el patrón y ofrecerá llenar automáticamente el resto de las columnas B y C.

2. Anidar la función SI dentro de otras funciones SI.

1 Haga clic en la etiqueta de hoja de cálculo SI.

etciberoamerica
 Esta hoja de cálculo será utilizada para calcular el número de puntos de premios corporativos
que serán otorgados a los viajeros frecuentes:
Rango Número de puntos
Menor que 5 1
5a9 3
10 a 19 6
20 a 29 10
30 y mayor 20

Una buena forma de abordar este problema es empezar con una fórmula muy sencilla y luego
seguir agregando por partes.

2 Seleccione la celda F5, e introduzca: =SI(D5<5,1,3)

3 Copie esta fórmula en el rango de celdas F6:F29.

Puede ver que cada cliente tiene 1 o 3 puntos asignados. El máximo que un cliente puede
obtener es 3 puntos, a pesar de que la mayoría deberían obtener más. Modifique esta fórmula
para que la segunda función SI esté anidada dentro de la primera. La fórmula completa será:
=SI(D5<5,1,SI(D5<10,3,6)).

4 Seleccione a celda F5 otra vez, presione F2, elimine el 3 y reemplácelo con: SI(D5<10,3,6)

5 Copie esta fórmula al rango de celdas F6:F29.

Observe que la segunda función SI anidada dentro la otra función SI utiliza la condición D5<10,
a pesar de que los 3 puntos aplican solo a los clientes con 5 a 9 reservaciones. La condición
entonces debería ser (D5>4) Y (D5<10). Resulta que la prueba lógica adicional (D5>4) no es
necesaria porque la función anidada es realizada solo si la primera condición SI (D5<5) resulta
en un valor FALSO.

El El valor de los puntos ahora se están acercando a lo que deberían ser. Los clientes con menos
de 10 reservaciones están obteniendo correctamente 1 o 3 puntos, pero muchos de los clientes
restantes deberían estar obteniendo más de 6 puntos. La siguiente versión de la fórmula será:
=SI(D5<5,1,SI(D5<10,3,SI(D5<20,6,10))

6 Seleccione la celda F5 otra vez, presione F2, elimine el 6 y reemplácelo con: SI(D5<20,6,10)

7 Copie esta fórmula en el rango de celdas F6:F29.

Se necesita agregar una fórmula anidada más para crear la fórmula final:
=SI(D5<5,1,SI(D5<10,3,SI(D5<20,6,SI(D5<30,10,20)))). Como puede ver, esta fórmula
puede ser abrumadora cuando trata de introducirla toda a la vez.

8 Selecciona la celda F5 otra vez, presione F2, elimine el 10 y reemplácelo con: SI(D5<30,10,20)

etciberoamerica
9 Copie esta fórmula en el rango de celdas F6:F29.

10 Vea cada uno de los valores de puntos en la columna F para verificar que están calculadas
correctamente.

Nota: las funciones BUSCAR o BUSCARV también producirán los mismos resultados.

La hoja de cálculo terminada debe ser similar a la siguiente:

etciberoamerica
Funciones condicionales lógicas
Objetivo 3.1.1

Aprender a utilizar las funciones Y, NO,

yO
Este ejercicio está diseñado para demostrar el uso de las funciones Y, NO, y O.

: Por favor tengan el libro Funciones Y O abierto en pantalla.

1. Utilizar Y, NO y O en algunas funciones SI.

Introduzca una fórmula para otorgar la designación Oro si un cliente más de 25 reservaciones y gastó
más de $75,000 en viajes este año.

1 Seleccione la celda F5, e introduzca: =SI(Y(D5>25,E5>75000),"Oro","")

2 Copie está fórmula en el rango de celdas F6:F29.

Utilizando esta fórmula, solo dos clientes han calificado para la membresía de oro. Así que
intente una fórmula diferente para ver cuántos clientes califican para la designación Oro si un
cliente tuvo más de 25 reservaciones o gastó más de $75,000 en viajes este año.

2. Introducir una fórmula diferente para encontrar clientes calificados.

1 Seleccione la celda G5, e introduzca: =SI(O(D5>25,E5>75000),"Oro","")

2 Copie esta fórmula en el rango de celdas G6:G29.

3. Crear una tercera fórmula para calificar a los clientes.

Intente una tercera fórmula alternativa para etiquetar a cualquier cliente que ha gastado $75,000
menos en viajes este año con una designación de Bronce. La fórmula entonces puede ser
=SI(E5<=75000,"Bronce",""). Sin embargo, también puede revertir la prueba lógica utilizando la
función NO.

1 Seleccione la celda H5, e introduzca: =SI(NO(E5>75000),"Bronce","")

2 Copie esta fórmula en el rango de celdas H6:H29.

etciberoamerica
La hoja de cálculo terminada debe ser similar a la siguiente:

etciberoamerica
Funciones condicionales de resumen
Objetivo 3.1.3

Aprender a utilizar funciones de suma

condicionales
Este ejercicio demuestra el uso de las funciones de suma condicionales.

: Por favor tengan el libro Funciones de resumen abierto en pantalla.

1. Introducir una función condicional de resumen.

Introduzca el primer conjunto de las formulas condicionales de resumen que calcularán las estadísticas
para todos los clientes que gastaron más de $50,000. Estás formulas son simples ya que solo tienen
un criterio: las ventas en la columna E son mayores que $50,000.

1 Introduzca las siguientes fórmulas en las celdas:

Celda Texto
I5 =CONTAR.SI(E5:E29,">50000")
I6 =SUMAR.SI(E5:E29,">50000")
I7 =PROMEDIO.SI(E5:E29,">50000")

Observe que las formulas condicionales SUMAR.SI y PROMEDIO.SI utilizadas aquí realizan sus
cálculos en la misma columna en la que se aplican los criterios de selección. En otras palabras,
estas fórmulas seleccionan solo aquellas celdas que cumplen con los criterios establecidos y
luego realizan la función CONTAR, SUMAR, y PROMEDIO solo en aquellas celdas. La
importancia de esto se hará más clara cuando examine los siguientes conjuntos de fórmulas
condicionales.

2. Introducir el siguiente conjunto de fórmulas condicionales de resumen.

Suponga que estas estadísticas son similares al primer conjunto, pero el criterio es que ese cliente es
un cliente Oro. Sin embargo, un cliente Oro pudo haber gastado menos que $50,000 este año, como
los clientes #7 y #11. Otros clientes pueden haber gastado más que $50,000, pero no son clientes Oro,
como los clientes #4 y #12.

1 Introduzca las siguientes fórmulas en las celdas:

Celda Texto
I9 =CONTAR.SI(F5:F29,"Oro")
I10 =SUMAR.SI(F5:F29,"Oro",E5:E29)
I11 =PROMEDIO.SI(F5:F29,"Oro",E5:E29)

etciberoamerica
 Estas son las mismas fórmulas condicionales que el primer conjunto, excepto que se ha
agregado un argumento adicional a las funciones SUMAR.SI y PROMEDIO.SI. En esta variación,
los criterios son aplicados a las celdas en la columna F (= “Oro”), pero las funciones SUMAR y
PROMEDIO son aplicada a las celdas correspondientes en la columna E, en la que están
ubicadas las cantidades de venta. El valor promedio se puede verificar al dividir el valor de
sumar entre el valor de contar. En contraste, el primer conjunto de las fórmulas condicionales
de resumen calculó las estadísticas en la misma columna E en las que está aplicando los
criterios de selección.

3. Introducir un tercer conjunto de fórmulas condicionales de resumen

Estas fórmulas son incluso más complejas porque tienen dos criterios: la venta debe ser con un cliente
Oro y el monto de ventas debe ser mayores a $50,000. Estas funciones son diferentes a las utilizadas
en el primer y segundo conjunto de fórmulas.

1 Introduzca las siguientes formulas condicionales de resumen en las celdas:

Celda Texto
I13 =CONTAR.SI.CONJUNTO(F5:F29,"Oro",E5:E29,">50000")
I14 =SUMAR.SI.CONJUNTO(E5:E29,F5:F29,"Oro",E5:E29,">50000")
I15 =PROMEDIO.SI.CONJUNTO(E5:E29,F5:F29,"Oro",E5:E29,">50000")

Observe que la secuencia de los parámetros para las formulas sumar y promedio es diferente
en las versiones de criterios sencillos y varios criterios.

También observe que solo 5 clientes cumplen con los criterios de selección. Esto es porque no
todos los clientes Oro gastaron más de $50,000, y no todos los clientes que gastaron más de
$50,000 son miembros Oro.

La hoja de cálculo ahora debe ser como la siguiente:

etciberoamerica
etciberoamerica
Análisis de hipótesis
Análisis de hipótesis manual

Aprender a utilizar un análisis hipótesis

Este ejercicio demostrará cómo utilizar un Análisis de hipótesis utilizando el método de prueba y erro para
realizar un cálculo sencillo de punto de equilibrio.

: Por favor tengan el libro Observar ballenas Hipótesis abierto en pantalla.

1. Identificar cuándo querría utilizar la herramienta Análisis de hipótesis.

Suponga que su compañía, Aventuras Tolano, está investigando la posibilidad de lanzar una compañía
nueva que proporcionará recorridos para observar ballenas para sus clientes, así como para clientes de
otras agencias de viaje. Una tarea importante es investigar la viabilidad financiera de esta empresa nueva.

El costo de rentar los botes es de un estimado del 25% de los ingresos, y el combustible es otro 35%. Los
costos restantes son gastos mensuales fijos para los salarios, una oficina pequeña cerca del muelle, y varios
gastos relacionados a los botes.

Esta hoja de cálculo no utiliza el formato tradicional de estado de resultados que se utiliza para fines de
reportes financieros. El formato utilizado aquí está diseñado para la rentabilidad o el análisis CVP (costo-
volumen-beneficio). Los elementos variables, incluyendo los ingresos y los costos, están colocados
conjuntamente en la parte superior de la hoja de cálculo; los elementos fijos se colocan conjuntamente en
la parte inferior.

1 En las siguientes celdas, introduzca las fórmulas especificadas:

Celda Fórmula
B4 =B3*0.25
B5 =B3*0.35
B6 =B3-B4-B5
B14 =SUMA(B8:B13)
B16 =B6-B14

Con unos ingresos mensuales de $15,000, esta hoja de cálculo muestra una pérdida de $2,250 al
mes.

2 Seleccione la celda B3 e introduzca: 20,000.

Excel ha calculado rápidamente un nuevo ingreso neto de -$250, que muestra claramente que la
compañía no tendrá rentabilidad en este nivel de ingresos. El término punto de equilibrio significa el
punto en el que los ingresos totales coinciden con los gastos totales, dejando un ingreso neto de

etciberoamerica
 $0. Un nivel de ingresos por encima de eso resultará en una ganancia y un nivel de ingresos por
debajo de eso resultará en una pérdida.

3 Repita el paso 2 con otros valores de ingresos para tartar de lograr un ingreso neto de $0. Ponga
atención sobre cuantas suposiciones tuvo que realizar.

Tendrá que hacer muchas suposiciones para lograr un punto de equilibrio. Cada una de sus
suposiciones será o muy alta o muy baja hasta que disminuya sus suposiciones al número correcto.

4 Una vez que tenga un ingreso neto tan cercano a $0 como sea posible, guarde y cierre el libro.

etciberoamerica
Utilizar la herramienta Buscar objetivo
Objetivo 3.4.3

Aprender a utilizar la herramienta

Buscar objetivo
Este ejercicio demuestra cómo utilizar la herramienta Buscar objetivo para llevar a cabo un Análisis de
hipótesis que realizó manualmente con anterioridad, y utilizar Buscar objetivo para situaciones más
complejas.

: Por favor tengan el libro Observar ballenas Buscar objetivo abierto en pantalla con la etiqueta
Estado de resultados como la hoja de cálculo actual.

1. Configurar la herramienta Buscar objetivo.

1 Seleccione la celda B16.

2 En la pestaña Datos, en el grupo Previsión, haga clic en Análisis de hipótesis, y haga clic en
Buscar objetivo.

El cuadro de diálogo Buscar Objetivo aparece con el cuadro de texto Definir la celda haciendo
referencia a la celda de respuesta.

3 Seleccione el cuadro de texto Con el valor y establezca el valor en: 0.

4 Seleccione el cuadro de texto Cambiando la celda y escriba: B3 o utilice el mouse para

seleccionar la celda B3.

5 Haga clic en Aceptar para iniciar el análisis Buscar objetivo.

La herramienta Buscar objetivo muestra el resultado en el cuadro de diálogo Estado de la

búsqueda de objetivo. Si Excel es capaz de encontrar una solución, el Valor del objetico y Valor
actual serán los mismos. El cuadro de diálogo Buscar objetivo debe ser como el siguiente:

A diferencia del ejercicio anterior, que involucró hacer suposiciones, esta herramienta calcula
rápidamente la respuesta por usted.

etciberoamerica
6 Haga clic en Aceptar en el cuadro de mensaje Estado de la búsqueda de objetivo para aceptar
los resultados de su búsqueda de objetivo.

2. Ejecutar un Análisis de hipótesis para encontrar cuántos ingresos se necesitan si los valores
cambian.

1 Cambie la celda B8 a: 3,000 y B12 a: 700.

2 Repita los pasos 2 a 6 para determinar los ingresos con una meta de ingresos netos de $5,000
por mes.

La hoja de cálculo debe ser similar a la siguiente captura de pantalla.

3 Haga clic en Aceptar para cerrar el cuadro de mensaje Estado de la búsqueda de objetivo.

3. Utilizar la herramienta Buscar objetivo con la función PAGO.

1 Seleccione la hoja de cálculo Préstamo.

2 Seleccione la celda B4 e introduzca: =PAGO(B2/12,B3,-B1)

3. Cambiar los valores de la herramienta Buscar objetivo.

Asumamos que tiene la oportunidad de adquirir un bote adecuado para llevar a cabo su negocio. No
desea pagar más de lo que gasta actualmente en su renta de bote, pero está dispuesto a pagar el
préstamo en un periodo de tiempo razonable que no excede los 30 meses. Como la hoja de cálculo
lo muestra actualmente, necesitará más de 24 meses para pagar el préstamo.

1 Con el cursor en la celda B4, configure los siguientes valores Buscar objetivo:

Cuadro de texto Valor

Definir la celda B4
Con el valor 8812.50
Cambiando la celda B3

etciberoamerica
2 Haga clic en Aceptar para iniciar el análisis Buscar objetivo.

La hoja de cálculo ahora debe ser similar a la siguiente:

3 Haga clic en Aceptar para cerrar el cuadro de mensaje Estado de la búsqueda de objetivo.

La herramienta Buscar objetivo es capaz de encontrar el número exacto de periodos de pago a

pesar de que no sea un número entero. Las instituciones financieras generalmente establecerán
el número de periodos de pago en un número entero, y ajustará el monto de pago como sea
necesario. En este caso está haciendo un análisis de hipótesis por lo que tiene sentido establecer
su objetivo para fijar el monto de pago a una mete y permitir que Buscar objetivo calcule el
número de pagos. Entonces puede tomar una decisión sobre si su plan es sensato o no.

4 Cierre el libro.

4. Probar la herramienta Buscar objetivo con otro libro.

1 Abra el libro Rebate corporativo Buscar objetivo con la hoja de cálculo SI activa.

En este libro nuevo está calculando las bonificaciones que estará otorgando a sus mejores clientes
como un incentivo. Las bonificaciones serán escaladas con base en cuánto le remuneraron durante el
año pasado.

2 Seleccione cada una de las celdas F1, F2, y F3 para ver su contenido.

En estas 3 celdas ha establecido inicialmente sus tasas de bonificación en 1% para su nivel más
bajo, el doble de la taza para el nivel intermedio, y el triple de esta tasa para sus mejores clientes.

etciberoamerica
 Ha decidido que los clientes Bronce son aquellos que generaron menos que $45,000, los clientes
Plata generaron hasta $90,000, y los clientes Oro son aquellos que desembolsaron $90,000 o
más.

3 Seleccione la celda F6, e introduzca: =SI(E6<45000,$F$1,IF(E6<90000,$F$2,$F$3))

4 Seleccione la celda G6, e introduzca: =E6*F6

5 Copie las formulas en las celdas F6:G6 al rango de celdas F7:G30.

El costo toral de las bonificaciones es más de lo que había presupuestado, que eran $15,000.
Puede utilizar la herramienta Buscar objetivo para ajustar los niveles de bonificación para
asegurarse de que no gaste más que su presupuesto. La complicación es que Buscar objetivo
solo puede especificar una celda variable, y tiene 3 valores de bonificación diferentes, como se
muestra en la función SI dentro de la columna F. En esta hoja de cálculo omitió esa limitación
al configurar las tasas Plata y Oro como dependientes de la tasa Bronce. Se percatará de esto
en las fórmulas utilizadas en las celdas F2 y F3.

6 Seleccione la celda G31, luego en la pestaña Datos, en el grupo Previsión, haga clic en Análisis
de hipótesis, y haga clic en Buscar objetivo.

7 Configure los siguientes valores de Buscar objetivo y haga clic en Aceptar:

Cuadro de texto Valor

Definir la celda G31
Con el valor 15000
Cambiando la celda F1

8 Haga clic en Aceptar para cerrar el cuadro de mensaje Estado de la búsqueda de objetivo.

etciberoamerica
 La herramienta Buscar objetivo fue capaz de realizar su análisis de hipótesis por usted a pesar
de que uno de los cálculos involucraba un conjunto de funciones SI.

5. Utilizar las funciones Y u O con la función SI.

1 Seleccione la hoja de cálculo Y-O.

Esta hoja de cálculo tiene los mismos datos que la hoja de cálculo SI, pero ahora quiere utilizar
un conjunto de criterios distinto para establecer las tasas Bronce, Plata y Oro: los clientes que
reservaron más de 25 viajes y gastaron más de $90,000 serán sus clientes Oro. Sus clientes Plata
serán los que reservaron más de 25 viajes o gastaron más de 90,000. Todos los demás serán sus
clientes Bronce.

2 Seleccione la celda F6, e introduzca:

=SI(Y(D6>25,E6>90000),$F$3,SI(O(D6>25,E6>90000),$F$2,$F$1))

3 Seleccione la celda G6, e introduzca: =E6*F6

4 Copie las fórmulas en las celdas F6:G6 al rango de celdas F7:G30.

5 Seleccione la celda G31, luego en la pestaña Datos, en el grupo Previsión, haga clic en Análisis
de hipótesis, y haga clic en Buscar objetivo.

6 Configure los siguientes valores de Buscar objetivo y haga clic en Aceptar:

etciberoamerica
 Cuadro de texto Valor
Definir la celda G31
Con el valor 15000
Cambiando la celda F1

7 Haga clic en Aceptar para cerrar el cuadro de mensaje Estado de la búsqueda de objetivo.

En esta hoja de cálculo puede dar mejores bonificaciones a cada uno de sus clientes, e incluso
mantenerse dentro de su presupuesto de $15,000. Esto parece ser una mejor oferta tanto para
usted como para sus clientes. Al examinar con mayor detalle esta hoja de cálculo, puede
percatarse que menos clientes pudieron calificar como Plata y Oro.

etciberoamerica
Trabajar con escenarios
Objetivo 3.4.3

Aprender a crear tres escenarios para

una hoja de cálculo
Este ejercicio demuestra cómo crear una hoja de cálculo sencilla con tres secciones.

: Por favor tengan el libro Escenarios de vuelo chárter abierto en pantalla.

1. Utilizar el Administrador de escenarios.

Aventuras Tolano utiliza está hoja de cálculo para decidir la oferta del paquete de vuelo chárter. La
compañía de viajes compra todos los asientos en una aerolínea chárter y vende los asientos a sus
clientes. Estos costos están establecidos por la aerolínea chárter, los aeropuertos, empresas de
catering, y así sucesivamente. Sin embargo, Tolano tiene que utilizar su mejor suposición sobre qué
precio cargar y cuántos asientos serán vencidos en ese precio.

Utilizará el Administrador de escenarios para ayudarle a decidir qué precio resultará en la mayor
ganancia para la empresa.

1 En la pestaña Datos, en el grupo Previsión, haga clic en la flecha de Análisis de hipótesis, y haga
clic en Administrador de escenarios.

2 Haga clic en Agregar.

3 En el cuadro de texto Nombre del escenario, escriba: Fijación de precios estándar.

4 Haga clic en el cuadro de texto Celdas cambiantes y elimine la referencia de celda que está ahí
actualmente.

5 Seleccione la celda A2 en la hoja de cálculo y luego mantenga presionada la Tecla CTRL mientras
selecciona el rango de celdas B4:B5.

etciberoamerica
 La celda A2 no es esencial para los escenarios; solo es una celda que muestra un comentario
cuando el escenario sea activado para una hoja de cálculo.
También puede introducir su propia información en el campo comentario. Al ingresar algunos
detalles (tales como suposiciones y explicaciones), puede ahorrarse potencialmente muchas
horas cuando está utilizando y cambiando los escenarios en una fecha posterior.

Nota: puede seleccionar un máximo de 32 celdas de hoja de cálculo por escenario.

6 Haga clic en Aceptar. Se muestra el cuadro de diálogo Valores del escenario.

7 Haga clic en Agregar para guardar este escenario. El cuadro de diálogo Agregar escenario se
muestra otra vez.

2. Agregar otro escenario.

1 En el cuadro de texto Agregar escenario, escriba: Descuento de fijación de precios y haga clic
en Aceptar.

2 Cambie los valores en el cuadro de diálogo Valores del escenario como a continuación:

$A$2 Descuento de fijación de precios

$B$4 140
$B$5 599

3 Haga clic en Agregar para guardar este escenario.

etciberoamerica
3. Agregar un tercer escenario.

1 En el cuadro de texto Nombre del escenario, escriba: Fijación de precios premium y haga clic
en Aceptar.

2 Cambie los valores en el cuadro de diálogo Valores del escenario como a continuación:
$A$2 Fijación de precios premium
$B$4 75
$B$5 799

3 Haga clic en Aceptar para guardar este escenario.

4 Con el escenario Fijación de precios premium seleccionado, haga clic en Mostrar.

5 Haga clic en Mostrar por cada uno de los tres escenarios en la hoja de cálculo y observe los
cambios en la hoja de cálculo.

6 Haga clic en Cerrar para regresar a la hoja de cálculo.

4. Asignar nombres de rango.

1 Seleccione la celda B6 y, en la pestaña Fórmulas, en el grupo Nombres definidos, haga clic en

Asignar nombre y luego en Definir nombre.

Nota: si crea nombres para estas celdas, el cuadro de diálogo Valores del escenario muestra estos
nombres en vez de la referencia de celda. Puede encontrar los nombres de rango más significativos
que las referencias de celda.

2 Haga clic en Aceptar para guardar este nombre de celda.

3 Repita los pasos 18 y 19 para las celdas B13 (Costos_totales) y B15 (Ganancias_netas).

5. Mostrar el resumen del escenario.

1 En la pestaña Datos, en el grupo Previsión, haga clic en la flecha de Análisis de hipótesis, y

haga clic en Administrador escenarios.

etciberoamerica
2 En el cuadro de diálogo Administrador de escenarios, haga clic en Resumen.

Nota: también puede elegir Informe de tabla dinámica de escenario en este cuadro de diálogo. La
tabla dinámica es útil cuando la hoja de cálculo se comparte por varios usuarios; esto le permite
seleccionar escenarios por usuario.

3 Verifique que Resumen esté seleccionado.

4 Seleccione la celda B6 en la hoja de cálculo y luego mantenga presionada la Tecla CTRL mientras
seleccione las celdas B13 y B15.

El cuadro de texto Celdas de resultado es utilizado para seleccionar las celdas en la hoja de
cálculo que muestran los resultados del escenario. Estas celdas son importantes porque
contienen fórmulas que muestran los resultados de cada escenario. En este ejercicio, las
celdas de resultado importantes son las celdas Ingresos totales, y Ganancias netas. Puede
incluir tantas o tan pocas celdas de resultado como quiera.

5 Haga clic en Aceptar.

Observe que las celdas a las que había nombrado anteriormente aparecen en este resumen con
esos nombres, hacienda la tabla más significativa para usted.

etciberoamerica
Utilizar la inspección de celdas
Objetivo 3.5.2

Aprender a utilizar la ventana de

inspección de celdas
Este ejercicio demostrará cómo utiliza la ventana de inspección de celdas.

: Por favor tengan el libro Bono de personal abierto en pantalla.

1. Utilizar la Ventana de inspección.

En esta hoja de cálculo, el bono anual de cada empleado es calculado utilizando la tasa de bono en la
parte superior de la hoja de cálculo. La tasa de bono está limitada al monto de dinero disponible a
pagar, $200,000 para fines de este ejercicio. La columna D calcula el bono de cada empleado. El monto
de bono total está en la parte inferior de la lista, pero necesitará ajustar la tasa de bono mientras
inspecciona el monto de bono total. Puede lograr esto al configurar una ventana de inspección de
celdas.

1 En la pestaña Fórmulas, en el grupo Auditoría de fórmulas, haga clic en Ventana Inspección.

Una Ventana Inspección en blanco aparece ahora.

2 Haga la Ventana Inspección más grande al hacer clic y arrastrar la esquina inferior derecha de
la Ventana Inspección.

3 Haga clic en Agregar inspección en la Ventana Inspección.

4 Con el cuadro de texto seleccionado, desplácese en la hoja de cálculo y seleccione la celda

D36.

El cuadro de diálogo Agregar inspección ahora muestra esta referencia de celda:

5 Haga clic en el botón Agregar del cuadro de diálogo Agregar inspección.

La Ventana Inspección ahora tiene esta referencia de celda.

6 Desplácese de nuevo a la parte superior en la hoja de cálculo.

etciberoamerica
7 Mueva la Ventana Inspección a un área de la hoja de cálculo en la que pueda verla, pero que
pueda actualizar fácilmente la celda B1.

8 Cambie el valor de la tasa de bono en la celda B1 hasta que la celda D36 sea tan cercana a
$200,000 como sea posible (puede exceder esa cantidad por no más de $499).

2. Quitar la Ventana Inspección.

1 Seleccione la referencia de celda D36 en la Ventana Inspección.

2 Haga clic en Eliminar inspección en la Ventana Inspección.

3 Cierre la Ventana Inspección.

etciberoamerica
Referencias estructuradas
Objetivo 1.1.4

Aprender a utilizar la herramienta de

referencia estructurada
Este ejercicio demostrará cómo utilizar la herramienta de referencia estructurada.

: Por favor tengan el libro Bonificación de cliente Referencia estructurada abierto en pantalla.

1. Convertir un rango de celdas a una tabla.

1 Seleccione una celda en el rango A4:F29, luego en la pestaña Insertar, en el grupo Tablas, haga
clic en Tabla.

2 En el cuadro de diálogo Crear tabla, verifique que el rango sea A4:G29 y que la casilla La tabla
tiene encabezados esté activada, luego haga clic en Aceptar.

3 Bajo Herramientas de tabla, en la pestaña Diseño, en el grupo Propiedades, haga clic en el cuadro
de texto Nombre de la tabla reemplace el nombre con: Clientes.

4 Seleccione la celda G5, escriba: = para empezar la fórmula.

5 Haga clic en la celda E5, escriba: * para introducir el símbolo de multiplicación, luego haga clic
en la celda F5.

Observe cómo la fórmula se está creando utilizando el nombre de columna.

6 Presione INTRO para completar la fórmula.

Ahora toda la columna se completa automáticamente con la misma fórmula.

7 Seleccione toda la columna G, luego en la pestaña Inicio, en el grupo Número, haga clic en la
flecha del cuadro de lista Formato de número y seleccione Moneda.

2. Activar la Fila de totales para esta tabla.

1 Bajo Herramientas de tabla, en la pestaña Diseño, en el grupo Opciones de estilo de tabla, haga
clic en Fila de totales para activarla.

etciberoamerica
2 Si la celda G30 no muestra el valor del total de suma para la columna Bonificación, seleccione la
celda G30, haga clic en el botón de flecha a la derecha y haga clic en Suma.

3 Seleccione la celda F30, haga clic en el botón de flecha a la derecha, y haga clic en Promedio

4 En la pestaña Inicio, en el grupo Número, haga clic dos veces en Aumentar decimales para
agregar dos dígitos decimales al valore del promedio de tasa en la celda F30.

5 Seleccione la celda E30, haga clic en el botón de flecha a la derecha y haga clic en Suma.

6 Seleccione la celda G30.

Observe que la fórmula creada por Excel para la columna Bonificación es

=SUBTOTALES(109,[Bonificación]). También observe que cuando se introduce la fórmula
mientras está dentro de la tabla, no se necesita utilizar el nombre de tabla.

3. Comparar esta fórmula con una función SUBTOTALES.

1 Seleccione la celda G32, e introduzca: =SUBTOTALES(

Se muestra una ventana emergente que muestra todos los números de funciones SUBTOTALES
disponibles.

2 Desplácese hacia abajo en la lista de función SUBTOTALES, haga clic en 109 – SUM y presione
TAB.

3 Siga escribiendo el resto de fórmula: ,Clientes[

4 Haga clic en Bonificación y presione TAB, escriba: ]) y presione INTRO.

3. Insertar una fórmula diferente para mostrar el valor total de suma.

1 Seleccione la celda G33, escriba: = para iniciar una fórmula nueva, luego haga clic en G30 y
presione INTRO para completar la fórmula.

Este es un método rápido de obtener la referencia de celda desde dentro de una tabla. La celda
ahora debe tener la fórmula =Clientes[[#Totales],[Bonificación]]

etciberoamerica
2 Seleccione la celda G34, e introduzca: =SUMA(Clientes[Bonificación])

3 Seleccione el rango de celdas G32:G34, luego en la pestaña Inicio, en el grupo Número, haga
clic en la flecha desplegable del cuadro de lista Formato de número y seleccione Moneda, si es
necesario.

4 Tome nota del valor total de suma mostrado actualmente al final de la columna Bonificación.
Debe ver que es el mismo número mostrado en las celdas G32, G33, y G34.

5 Haga clic en el icono de Filtro automático a la derecha del nombre de columna Oficina en la
tabla, haga clic en Seleccionar todo para desactivarlo, luego haga clic en Nueva York y Toronto
para activarlas y haga clic en Aceptar.

La hoja de cálculo terminada debe ser similar a la siguiente:

Observe que los valores de la Fila de totales de la tabla en la fila 30 se ha ajustado a lo que se
muestra actualmente. También observe que los números en las celdas G32 y G33 siguen
coincidiendo con el total de suman de la columna Bonificación. Sin embargo, el número en la
celda G34 aún muestra el total de suma de la columna Bonificación de la tabla original y sin
filtros. Esto demuestra que necesita elegir la función correcta para resumir datos de una tabla,
dependiendo de sus requerimientos.

6 Bajo Herramientas de tabla, en la pestaña Diseño, en el grupo Opciones de tabla, haga clic en
Fila de totales para desactivarla.

La fórmula que hace referencia a la fila de totales ahora muestra un error.

7 Haga clic otra vez en la casilla Fila de totales en la Cinta de opciones para volver a activarla.

etciberoamerica