SEMINARIO Analizar - Datos - 1

Seminario de Excel
Análisis de Datos
(Solver)
Parte II
Análisis de Datos / Filtros - Subtotales – Solver - Escenarios
Analizar Datos
Microsoft Excel dispones de varias herramientas para analizar los datos, que estan disponibles en
el menú de datos. Alguna de las más usadas se presentan a continuación.
Filtros
Aplicar filtros es una forma rápida y fácil de buscar un subconjunto de datos de un rango y
trabajar con el mismo. Un rango filtrado muestra sólo las filas que cumplen el criterio que se
especifique para una columna. Excel proporciona dos comandos para aplicar filtros a los rangos:
• Autofiltro, que incluye filtrar por selección, para criterios simples
• Filtro avanzado, para criterios más complejos
A diferencia de la ordenación, el filtrado no reorganiza los rangos. El filtrado oculta
temporalmente las filas que no se desea mostrar.
Cuando Excel filtra filas, le permite modificar, aplicar formato, representar en gráficos e
imprimir el subconjunto del rango sin necesidad de reorganizarlo ni ordenarlo.
Autofiltro: Cuando utilice el comando Autofiltro, aparecerán las flechas de Autofiltro a la

derecha de los rótulos de columna del rango filtrado.
Microsoft Excel indica los elementos filtrados en azul.

Puede utilizar Autofiltro personalizado para mostrar filas que contengan un valor u otro. También
puede utilizar Autofiltro personalizado para mostrar las filas que cumplan más de una condición
en una columna; por ejemplo, las filas que contengan valores comprendidos en un rango
específico (como un valor de Davolio).
Filtro avanzado: El comando Filtro avanzado permite filtrar un rango en contexto, como el
comando Autofiltro, pero no muestra listas desplegables para las columnas. En lugar de ello,
Elaborado por: Ing. Izael Urieta M. Pag.: 1

tiene que escribir los criterios según los cuales desea filtrar los datos en un rango de criterios
independiente situado sobre el rango. Un rango de criterios permite filtrar criterios más
complejos.
Filtrar un rango (Autofiltro)

Sólo puede aplicar filtros a un rango de una hoja de cálculo cada vez.
1. Haga clic en la celda del rango que desea filtrar.
2. En el menú Datos, seleccione Filtro y haga clic en Autofiltro.
Filtrar por el número menor o mayor
1. Haga clic en la flecha de la columna que contiene los números y haga clic en
(Diez mejores).
2. En el cuadro de la izquierda, haga clic en superiores o inferiores.
3. En el cuadro del medio, escriba un número.
4. En el cuadro de la derecha, haga clic en elementos.
Filtrar un rango para buscar filas que contengan texto específico
(Personalizar).
6. En el cuadro de la izquierda, haga clic en igual o en no igual, contiene o no
contiene
7. Introduzca el texto que desee en el cuadro de la derecha.
8. Si necesita buscar valores de texto que comparten algunos caracteres pero no
otros, utilice un carácter comodín.
Los siguientes caracteres comodín pueden usarse como criterios de comparación
para filtros, así como para buscar y reemplazar contenido.
Utilice Para buscar
Un único carácter
? (signo de interrogación)
Por ejemplo, Gr?cia buscará "Gracia" y "Grecia"
Un número de caracteres
* (asterisco)
Por ejemplo, *este buscará "Nordeste" y "Sudeste"
~ (tilde) seguida de Un signo de interrogación, un asterisco o una tilde.
?, *, o ~ Por ejemplo, fy91~? buscará "fy91?".
9. Para agregar otros criterios, haga clic en Y u O, y repita el paso anterior.

Filtrar por celdas vacías o celdas no vacías
Haga clic en la flecha de la columna que contiene los números y, a continuación, elija
(Vacías) o (No vacías).
Nota Las opciones Vacías y No vacías sólo están disponibles si la columna que desea filtrar
contiene una celda vacía.

Filtrar por números mayores o menores que otro número
(Personalizar).
11. En el cuadro de la izquierda, haga clic en es mayor que, es menor que, es mayor
o igual que o es menor o igual que.
12. En el cuadro de la derecha, escriba un número.
Filtrar por un número igual a o no igual a otro número
(Personalizar).
15. En el cuadro de la izquierda, haga clic en es igual a o no es igual a.
16. En el cuadro de la derecha, escriba un número.
Filtrar por el comienzo o el final de una cadena de texto
(Personalizar).
19. En el cuadro de la izquierda, haga clic en comienza por o no comienza por, o en
termina con o no termina con.
20. Introduzca el texto que desee en el cuadro de la derecha.
21. Si necesita buscar valores de texto que comparten algunos caracteres pero no
otros, utilice un carácter comodín.
Filtrar por los números superior o inferior, por porcentaje
(Diez mejores).
24. En el cuadro de la izquierda, haga clic en superiores o inferiores.
25. En el cuadro del medio, escriba un número.
26. En el cuadro de la derecha, haga clic en Por ciento.
Notas: Cuando aplique un filtro a una columna, los únicos filtros disponibles para las demás columnas
serán los valores visibles del rango filtrado actualmente. Sólo aparecen las 1000 primeras
entradas únicas de una lista al hacer clic en la flecha .

Filtrar utilizando criterios avanzados

1. Inserte al menos tres filas vacías sobre el rango que puede utilizarse como rango de
criterios. El rango de criterios debe tener rótulos de columna. Compruebe que existe al
menos una fila vacía entre los valores de criterios y el rango.
2. En las filas situadas bajo los rótulos de columna, escriba los criterios que desea buscar.
3. Haga clic en una celda del rango.
4. En el menú Datos, seleccione Filtro y haga clic en Filtro avanzado.
5. Para filtrar el rango ocultando las filas que no cumplen los criterios, haga clic en Filtrar
la lista sin moverla. Para filtrarlo copiando las filas que cumplen los criterios a otra área
de la hoja de cálculo, haga clic en Copiar a otro lugar, después en la casilla Copiar a y,
por último, en la esquina superior izquierda del área donde desea pegar las filas.
6. En la casilla Rango de criterios introduzca su referencia, incluidos los rótulos de
criterios. Para ocultar temporalmente el cuadro de diálogo Filtro avanzado mientras
selecciona el rango de criterios, haga clic en Contraer cuadro de diálogo .
7. Para cambiar la forma en que se filtran los datos, cambie los valores del rango de criterios
y vuelva a aplicar el filtro a los datos.
Sugerencias: Se puede asignar a un rango el nombre Criterios y la referencia del rango
aparecerá automáticamente en el cuadro Rango de criterios. También se puede definir el
nombre Base de datos para el rango de datos que deben ser filtrados y definir el nombre Extraer
para el área donde desea pegar las filas, y estos rangos aparecerán automáticamente en los
cuadros Rango de lista y Copiar a, respectivamente. Al copiar filas filtradas a otra ubicación, se
pueden especificar las columnas que deben incluirse en la copia. Antes de filtrar, copie los rótulos
de columna de las columnas deseadas a la primera fila del área donde va a pegar las filas
filtradas. Cuando filtre, escriba una referencia a los rótulos de columna copiados en el cuadro
Copiar a. De este modo, las filas copiadas incluirán sólo las columnas cuyos rótulos se hayan
copiado.
Importante: Puesto que el signo igual se utiliza para indicar que se utiliza una fórmula cuando
se escribe texto o un valor en una celda, Microsoft Excel evalúa lo escrito; sin embargo, esto
puede producir resultados de filtro inesperados. Para indicar que se trata de un operador de
comparación de igualdad para texto o para un valor, escriba los criterios como una expresión de
cadena en la celda apropiada en el rango de criterios:
=''=entrada''
En este caso, entrada es el texto o valor que desea buscar. Por ejemplo:
Lo escrito en la celda Lo que evalúa y muestra Excel
="=Davolio" =Davolio
="=3000" =3000
Cuando filtra los datos de texto, Excel no distingue entre caracteres en mayúsculas y minúsculas.
Sin embargo, puede utilizar una fórmula para realizar una búsqueda con distinción entre
mayúsculas y minúsculas. Para obtener un ejemplo, consulte Filtrar el texto utilizando búsquedas
con distinción entre mayúsculas y minúsculas.

Ejemplos De Criterios Complejos

Varios criterios en una columna
Lógica booleana: (Vendedor = "Davolio" O Vendedor = "Buchanan")
Para buscar las filas que cumplen varios criterios en una columna, escriba los criterios
directamente unos debajo de otros en filas independientes del rango de criterios. En el rango de
datos siguiente (A6:C10), el rango de criterios (B1:B3) muestra las filas que contienen "Davolio"
o "Buchanan" en la columna Vendedor (A8:C10).
A B C
1 Tipo Vendedor Ventas
2 =Davolio
3 =Buchanan
4
5
7 Bebidas Suyama $5122
8 Carne Davolio $450
9 Frutas Buchanan $6328
10 Frutas Davolio $6544
Múltiples criterios en varias columnas en los que deben cumplirse todos los criterios
Lógica booleana: (Tipo = "Frutas" Y Ventas > 1000)
Para buscar las filas que cumplen varios criterios en varias columnas, escriba todos los criterios
en la misma fila del rango de criterios. En el rango de datos siguiente (A6:C10), el rango de
criterios (A1:C2) muestra todas las filas que contienen "Frutas" en la columna Tipo y un valor
superior a 1.000 $ en la columna Ventas (A9:C10).
A B C
2 =Frutas >1000
3
4
5

Múltiples criterios en varias columnas en las que debe cumplirse cualquier criterio
Lógica booleana: (Tipo = "Frutas" O Vendedor = "Davolio")
Para buscar las filas que cumplen varios criterios en varias columnas, en las que puede cumplirse
cualquier criterio, escriba los criterios en distintas filas del rango de criterios. En el rango de
datos siguiente (A6:C10), el rango de criterios (A1:B3) muestra todas las filas que contienen
"Frutas" en la columna Tipo o "Davolio" en la columna Vendedor (A8:C10).
A B C
2 =Frutas
3 =Davolio
4
5
Múltiples conjuntos de criterios en los que cada conjunto incluye criterios para
varias columnas
Lógica booleana: ( (Vendedor = "Davolio" Y Ventas >3000) O (Vendedor = "Davolio" Y Ventas >
1500) )
Para buscar las filas que cumplen varios conjuntos de criterios, en los que cada conjunto incluye
criterios para varias columnas, escriba cada conjunto de criterios en filas independientes. En el
rango de datos siguiente (A6:C10), el rango de criterios (B1:C3) muestra las filas que contienen
"Davolio" en la columna Vendedor y un valor superior a 3.000 $ en la columna Ventas, o muestra
las filas que contienen "Buchanan" en la columna Vendedor y un valor superior a 1.500 $ en la
columna Ventas (A9:C10).
A B C
2 =Davolio >3000
3 =Buchanan >1500
4
5

Múltiples conjuntos de criterios en los que cada conjunto incluye criterios para una
columna
Lógica booleana: ( (Ventas > 6000 Y Ventas < 6500 ) O (Ventas < 500) )
Para buscar las filas que cumplen varios conjuntos de criterios, en los que cada conjunto incluye
criterios para una columna, incluya varias columnas con el mismo encabezado de columna.
En el rango de datos siguiente (A6:C10), el rango de criterios (C1:D3) muestra las filas que
contienen valores comprendidos entre 5.000 y 8.000, y valores inferiores a 500 en la columna
Ventas (A8:C10).
A B C D
1 Tipo Vendedor Ventas Ventas
2 >6000 <6500
3 <500
4
5
Criterios para buscar valores de texto que comparten algunos caracteres pero no
otros
Para buscar valores de texto que contengan algunos caracteres pero no otros, siga uno o varios de
estos procedimientos:
Escriba uno o más caracteres sin incluir el signo igual (=) para buscar las filas que tienen un
valor de texto en una columna que contenga esos caracteres. Por ejemplo, si escribe el texto
Dav como criterio, Excel buscará "Davolio", "David" y "Davis".
Utilice un carácter comodín.
Como criterios de comparación se pueden utilizar los siguientes caracteres comodín.
Uso Para buscar
? (signo de interrogación) Cualquier carácter
Por ejemplo, sm?th busca "smith" y "smyth"
* (asterisco) Cualquier cantidad de caracteres
Por ejemplo, *este encuentra "Noreste" y "Sureste"
~ (tilde) seguido de ?, * o ~ Un signo de interrogación un asterisco o una tilde
Por ejemplo, af91~? busca "af91?"
En el rango de datos siguiente (A6:C10), el rango de criterios (A1:B3) presenta las filas que
tienen "Ca" como primeros caracteres en la columna Tipo o las filas cuyo segundo carácter de la
columna Vendedor (A7:C9) es igual a "u".

A B C
2 Ca
3 =?u*
4
5
Criterios creados como resultado de una fórmula: Filtrar valores mayores que el
promedio de todos los valores contenidos en el rango de datos
En el siguiente rango de datos (A6:D10), el rango de criterios (D1:D2) muestra filas que tienen
un valor en la columna Ventas que es superior que el promedio de todos los valores de Ventas
(C7:C10). En la fórmula, "C7" se refiere a la columna filtrada (C) de la primera fila del rango de
datos (7).
A B C D
1 Tipo Vendedor Ventas Promedio calculado
2 =C7>PROMEDIO($C$7:$C$10)
3
4
5
Puede usar como criterio un valor calculado que sea el resultado de una Recuerde los siguientes
puntos importantes:
La fórmula debe tener como resultado VERDADERO o FALSO.
Puesto que va a utilizar una fórmula, escríbala como lo haría normalmente y no escriba la expresión
de esta manera: =''=entrada''
No utilice rótulos de columna para los rótulos de los criterios; deje los rótulos de los criterios en
blanco o utilice un rótulo que no sea ninguno de los rótulos de columna del rango (en los ejemplos
siguientes, Promedio calculado y Coincidencia exacta).
Si utiliza un rótulo de columna en la fórmula en lugar de una referencia relativa de celda o un nombre
de rango, Excel muestra un valor de error, como #¿NOMBRE? o #¡VALOR!, en la celda que contiene
el criterio. Puede omitir este error porque no afecta a la forma de filtrar el rango.

La fórmula utilizada para los criterios debe utilizar una referencia relativa para hacer referencia a la
celda correspondiente en la primera fila (en los ejemplos siguientes, C7 y A7).
Todas las demás referencias de la fórmula deben ser referencias absolutas.
Filtrar el texto utilizando búsquedas con distinción entre mayúsculas y minúsculas
En el rango de datos (A6:D10), el rango de criterios (D1:D2) muestra filas que contienen
"Frutas" en la columna Tipo utilizando la función IGUAL para realizar una búsqueda con
distinción de mayúsculas y minúsculas (A10:C10). En la fórmula, "A7" hace referencia a la
columna filtrada (A) de la primera fila del rango de datos (7).
A B C D
1 Tipo Vendedor Ventas Coincidencia exacta
2 =IGUAL(A7, "Frutas")
3
4
5
Subtotales
Microsoft Excel puede calcular automáticamente valores de
subtotales y de totales generales en una lista. Cuando se insertan
subtotales automáticos, Excel esquematiza la lista para que se puedan
mostrar y ocultar las filas de detalle de cada subtotal. Para insertar
subtotales, primero se ordena la lista para agrupar las filas cuyos
subtotales se desea calcular. Después pueden calcularse los
subtotales de cualquier columna que contenga números. Si los datos
no están organizados en una lista, o si sólo necesita un total, puede
utilizar Autosuma en lugar de subtotales automáticos.
Cómo se calculan los subtotales
Subtotales: Excel calcula los subtotales con una función de resumen, como Suma o
Promedio. Puede mostrar subtotales en una lista con más de un tipo de cálculo a la vez.
Totales generales: Los valores del total general se obtienen a partir de los datos de detalle y
no de los valores de las filas de subtotales. Por ejemplo, si se utiliza la función de resumen
Promedio, la fila Total general mostrará el promedio de todas las filas de detalle de la lista
y no el promedio de los valores de las filas de subtotales.
Actualización automática: Excel actualizará automáticamente los subtotales y el total

general cuando se modifiquen los datos de detalle.

Insertar subtotales individuales
1. Asegúrese de que los datos cuyos subtotales desea calcular están en el siguiente formato:
cada columna tiene un rótulo en la primera fila, contiene hechos similares y no hay filas ni
columnas en blanco en el rango.
2. Haga clic en una celda de la columna cuyos subtotales se desea calcular. En el ejemplo
anterior, haría clic en una celda de la columna Deporte, columna B.
3. Haga clic en Orden ascendente o en Orden descendente .

4. En el menú Datos, haga clic en Subtotales.
5. En el cuadro Para cada cambio en, haga clic en la columna cuyos subtotales desee
calcular. En el ejemplo anterior, haría clic en la columna Deporte.
6. En el cuadro Usar función, haga clic en la función de resumen que desee utilizar para
calcular los subtotales.
7. En el cuadro Agregar subtotal a, active la casilla de verificación de cada columna que
contenga valores cuyos subtotales desee calcular. En el ejemplo anterior, seleccionaría la
columna Ventas.
8. Si desea un salto de página automático después de cada subtotal, active la casilla de
verificación Salto de página entre grupos.
9. Si desea que los subtotales aparezcan encima de las filas cuyos subtotales se han
calculado en lugar de que aparezcan debajo, desactive la casilla de verificación Resumen
debajo de los datos.
10. Haga clic en Aceptar.
Nota Puede utilizar de nuevo el comando Subtotales para agregar más subtotales con diferentes
funciones de resumen. Para evitar que se sobrescriban los subtotales existentes, desactive la casilla de
verificación Reemplazar subtotales actuales.
Subtotales anidados
Puede insertar subtotales de grupos más pequeños en los grupos de subtotales existentes. En el
ejemplo a continuación, los subtotales de cada deporte están en una lista que ya tiene subtotales
para cada región.
Antes de insertar los subtotales anidados, compruebe que ha ordenado la lista por todas las
columnas para las que desea los valores subtotales de manera que las filas cuyos subtotales desea
estén agrupadas.

Insertar subtotales anidados

1. Asegúrese de que los datos cuyos subtotales desea calcular están en el siguiente formato:
cada columna tiene un rótulo en la primera fila, contiene hechos similares y no hay filas ni
columnas en blanco en el rango.
2. Ordene el rango por varias columnas, ordenando primero por la columna de subtotales
exteriores, después por la siguiente columna interior de los subtotales anidados, etc. En el
ejemplo anterior, ordenaría el rango primero por la columna Región y después por la
columna Deporte.
3. Inserte los subtotales exteriores.
2. En el cuadro Para cada cambio en, haga clic en la columna de los subtotales exteriores.
En el ejemplo anterior, haría clic en Región.
3. En el cuadro Usar función, haga clic en la función de resumen que desee utilizar para
calcular los subtotales.
4. En el cuadro Agregar subtotal a, active la casilla de verificación correspondiente a cada
columna que contenga valores cuyos subtotales desee calcular. En el ejemplo anterior, esa
columna sería Ventas.
5. Si desea un salto de página automático después de cada subtotal, active la casilla de
verificación Salto de página entre grupos.
6. Si desea que los subtotales aparezcan encima de las filas cuyos subtotales se han
calculado en lugar de que aparezcan debajo, desactive la casilla de verificación Resumen
debajo de los datos.
4. Inserte los subtotales anidados.
2. En el cuadro Para cada cambio en, haga clic en la columna de subtotales anidados. En el
ejemplo anterior, esa columna sería Deporte.
3. Seleccione la función de resumen y otras opciones.
4. Desactive la casilla de verificación Reemplazar subtotales actuales.
5. Repita el paso anterior para más subtotales anidados, empezando desde los más
exteriores.

Informes y gráficos de resumen

Crear informes de resumen Al agregar subtotales a una lista, ésta aparecerá esquematizada
para que pueda ver su estructura. Puede crear un informe de resumen haciendo clic en los
símbolos de esquema , y para ocultar los detalles y mostrar solamente los totales.
Presentar los datos del resumen en un gráfico Puede crear un gráfico que utilice sólo los
datos visibles de una lista que contenga subtotales. Si muestra u oculta los detalles de la lista
esquematizada, el gráfico también se actualiza para mostrar u ocultar los datos.
Por ejemplo, en una hoja de cálculo que contenga datos sobre ventas correspondientes a tres
categorías de productos distintas, primero puede ordenar los productos por categoría y, a
continuación, seleccionar todas las celdas que contienen datos y abrir el cuadro de diálogo
Subtotales (menú Datos, comando Subtotales).

En el cuadro de diálogo Subtotales puede elegir la columna en la que desea basar los subtotales
(como por ejemplo para cada uno de los valores distintos de la columna Semana), el cálculo de
resumen que desea realizar y la columna o columnas que contienen los valores que deben
resumirse. Por ejemplo (como se muestra en la imagen anterior), podría calcular los subtotales de
la cantidad de unidades vendidas en cada categoría. Una vez definidos los subtotales, aparecen en
la hoja de cálculo.
Como muestra la imagen anterior, cuando se agregan subtotales a una hoja de cálculo, Excel
también define grupos tomando como base las filas usadas para calcular los subtotales. Las
agrupaciones forman un esquema de su hoja de cálculo según los criterios usados para crear los
subtotales. Todas las filas que contienen productos de mobiliario aparecen en un grupo, las filas
que contienen herramientas en otro, etc. La sección de esquema de la parte izquierda de la hoja de
cálculo contiene controles que puede utilizar para mostrar u ocultar grupos de filas.

En la sección de esquema se pueden encontrar tres tipos de controles:
Botones Ocultar detalles Cuando las filas de un grupo están visibles, aparece un botón para
ocultar detalles junto a dicho grupo.
Botones Mostrar detalles Cuando se oculta un grupo de filas, el botón que aparece junto al
grupo cambia y se transforma en un botón para mostrar detalles . Al hacer clic en un botón
Mostrar detalles se restauran las filas de ese grupo y aparecen en la hoja de cálculo.
Botones Nivel Cada uno de los botones de nivel numerados representa un nivel de
organización dentro de una hoja de cálculo; al hacer clic en un botón de nivel se ocultan todos
los niveles de detalle situados debajo del botón en el que se hace clic.
La tabla siguiente identifica los tres niveles de organización del gráfico anterior.
Nivel Descripción
1 Total general
2 Subtotales de cada grupo
3 Filas de la hoja de cálculo
En la hoja de cálculo presentada en la imagen anterior, al hacer clic en el botón de nivel 2 se

ocultarían las filas que contuvieran datos sobre las ventas de productos concretos, pero quedarían
visibles en la hoja de cálculo la fila del total general (nivel 1) y todas las filas que contienen los
subtotales de cada producto (nivel 2).

Para mayor flexibilidad, puede agregar niveles de detalle al esquema creado por Excel, lo que le
permitirá ocultar detalles concretos de vez en cuando. Por ejemplo, puede que desee ocultar las
ventas de vallas, campanillas y estacas de bambú (que sabe vender bien) para ver el nivel de
ventas de los demás productos y compararlos entre sí.
Crear un nuevo grupo de esquema dentro de un grupo existente

1. Seleccione las filas que desea agrupar.
2. Elija el comando Agrupar y esquema del menú Datos y haga clic en Agrupar.
Excel creará un nuevo grupo en un nuevo nivel (nivel 4), como se puede ver en la imagen
siguiente.

Quitar un grupo
1. Seleccione las filas incluidas en un grupo.

2. Elija el comando Agrupar y esquema del menú Datos y haga clic en Desagrupar.
Sugerencia: Si desea quitar todos los subtotales de una hoja de cálculo, haga clic en la opción
Subtotales del menú Datos y, después, en Quitar todos.
Quitar subtotales
Si quita los subtotales de una lista, Microsoft Excel eliminará también el esquema y todos los
saltos de página que se hayan insertado en la lista.
1. Haga clic en una celda de la lista que contenga los subtotales.
3. Haga clic en Quitar todos.
Funciones de resumen para análisis de datos

Las funciones de resumen se utilizan en subtotales automáticos, consolidaciones de datos e
informes de gráfico dinámico y de tabla dinámica.
Función Resumen
Suma La suma de los valores. Esta es la función predeterminada para datos numéricos.
El número de los valores de datos. La función de resumen Contar funciona del mismo
Contar modo que la función de la hoja de cálculo CONTARA. Contar es la función predeterminada
para datos no numéricos.
Promedio El promedio de los valores.
Máx El valor máximo.
Mín El valor mínimo.
Producto El producto de los valores.
Contar El número de valores de datos que son números. La función de resumen Contar núm
números funciona del mismo modo que la función de la hoja de cálculo CONTAR.
Una estimación de la desviación estándar de una población, donde la muestra es un
DESVEST
subconjunto de toda la población.
Una estimación de la desviación estándar de una población, donde la población son todos
DESVESTP
los datos que van a resumirse.
Una estimación de la varianza de una población, donde la muestra es un subconjunto de
Var
toda la población.
Varp La varianza de una población, donde la población son todos los datos que van a resumirse.
Tablas de datos
Las tablas de datos son parte de una serie de comandos a veces denominados herramientas de
análisis Y si. Una tabla de datos es un rango de celdas que muestra cómo afecta el cambio de
algunos valores de las fórmulas a los resultados de las mismas. Las tablas de datos constituyen un

método abreviado para calcular varias versiones en una sola operación, así como una manera de
ver y comparar los resultados de todas las variaciones distintas en la hoja de cálculo.
Tablas de datos de una variable Por ejemplo, utilice una tabla de datos de una variable si
desea ver de qué manera afectan distintos tipos de interés al pago mensual de una hipoteca. En el
siguiente ejemplo, la celda D2 contiene la fórmula de pago, =PAGO(B3/12,B4,-B5), que hace
referencia a la celda variable B3.
Tabla de datos de dos variables Una tabla de datos de dos variables puede mostrar cómo
afectan los distintos tipos de interés y plazos del préstamo al pago de una hipoteca. En el
siguiente ejemplo, la celda C2 contiene la fórmula de pago, =PAGO(B3/12,B4,-B5), que utiliza
dos celdas variables, B3 y B4.
Cálculos de las tablas de datos Las tablas de datos se actualizan cuando se actualiza una hoja
de cálculo, aunque no hayan cambiado. Para acelerar el cálculo de una hoja de cálculo que
contenga una tabla de datos, pueden cambiarse las opciones de Calcular para que se actualice
automáticamente la hoja de cálculo pero no las tablas de datos.
Crear una tabla de datos de una variable

Deben diseñarse tablas de datos de una variable para que los valores de entrada aparezcan debajo
de una columna (orientación de columna) o en una fila (orientación de fila). Las fórmulas que se
utilicen en la tabla de una variable deberán hacer referencia a una celda variable.
1. Introduzca la lista de valores que desee sustituir en la celda variable debajo de una
columna o en una fila.
2. Siga uno de estos procedimientos:
o Si la tabla de datos está orientada a columna, escriba la fórmula en la fila situada
encima del primer valor y una celda a la derecha de los valores de
columna. Escriba las fórmulas adicionales a la derecha de la primera fórmula.

o Si la tabla de datos está orientada a fila, escriba la fórmula en la columna situada a

la izquierda del primer valor y una celda por debajo de los valores de fila.
Introduzca las fórmulas adicionales debajo de la primera fórmula.
3. Seleccione el rango de celdas que contenga las fórmulas y los valores que desee sustituir.
4. En el menú Datos, haga clic en Tabla.
o Si la tabla de datos está orientada a columna, escriba la referencia de celda en el
cuadro Celda variable columna.
o Si la tabla de datos está orientada a fila, introduzca la referencia de celda en el
cuadro Celda variable fila.
Agregar una fórmula a una tabla de datos de una variable

Las fórmulas que se utilicen en la tabla de datos de una variable deberán hacer referencia a la
misma celda variable.
o Si la tabla de datos estás orientada a columna, escriba la nueva fórmula en una
celda en blanco situada a la derecha de una fórmula existente en la fila superior de
la tabla.
o Si la tabla de datos está orientada a fila, escriba la nueva fórmula en una celda en
blanco situada debajo de una fórmula existente en la primera columna de la tabla.
2. Seleccione la tabla de datos, incluida la columna o la fila que contenga la nueva fórmula.
o Si la tabla de datos está orientada a columna, escriba la referencia de celda en el
cuadro Celda variable columna.
o Si la tabla de datos está orientada a fila, introduzca la referencia de celda en el
cuadro Celda variable fila.
Crear una tabla de datos de dos variables

Las tablas de datos de dos variables solamente utilizan una fórmula con dos listas de variables. La
fórmula deberá hacer referencia a dos celdas variables diferentes.
1. En una celda de la hoja de cálculo, introduzca la fórmula que haga referencia a las dos
celdas variables.
En el ejemplo siguiente, en el que los valores iniciales de la fórmula se especifican en las
celdas B3, B4 y B5, debe escribir la fórmula =PMT(B3/12,B4,-B5) en la celda C2.
2. Inserte una lista de variables en la misma columna, debajo de la fórmula.

En el ejemplo siguiente, debe escribir los diferentes tipos de interés en las celdas C3, C4 y
C5.
3. Inserte la segunda lista en la misma fila, a la derecha de la fórmula.
En el ejemplo siguiente, debe escribir los plazos del préstamo (en meses) en las celdas D2
y E2.
4. Seleccione el rango de celdas que contenga la fórmula y los valores de fila y de columna.
En el ejemplo siguiente, debe seleccionar el rango C2:E5.
6. En el cuadro Celda de entrada (fila), inserte la referencia de la celda variable para las
variables de la fila.
En el ejemplo siguiente, debe escribir la celda B4 en el cuadro Celda de entrada (fila).
7. En el cuadro Celda de entrada (columna), incluya la referencia de la celda variable para
las variables de la columna.
En el ejemplo siguiente, debe escribir la celda B3 en el cuadro Celda de entrada
(columna).
Ejemplo: Una tabla de datos de dos variables puede mostrar cómo afectan los distintos tipos de interés
y plazos del préstamo al pago de una hipoteca. En el siguiente ejemplo, la celda C2 contiene la fórmula
de pago, =PAGO(B3/12,B4,-B5), que utiliza dos celdas variables, B3 y B4.
Consolidar datos
Para consolidar datos, se combinan los valores de varios rangos de datos. Por ejemplo, si tiene
una hoja de cálculo de cifras de gastos para cada una de sus oficinas regionales, puede utilizar
una consolidación para reunir estas cifras en una hoja de cálculo de gastos para toda la
organización.
Excel permite consolidar datos de varios modos. El método más flexible es crear fórmulas que
hagan referencia a las celdas de cada rango de datos que se vaya a combinar. Las fórmulas que
hacen referencia a las celdas de varias hojas de cálculo se denominan fórmulas 3D.
Utilizar fórmulas 3D

Si utiliza referencias 3D en las fórmulas no habrá restricciones en el diseño de los rangos de datos
independientes. Puede cambiar la consolidación del modo que necesite. La consolidación se
actualiza automáticamente cuando cambian los datos de los rangos de origen.
Utilizar fórmulas para combinar datos En el siguiente ejemplo, la fórmula de la celda A2
agrega tres números de tres posiciones diferentes en tres hojas de cálculo distintas.
Agregar datos a una consolidación con referencias 3D Cuando todas las hojas de cálculo de
origen tengan el mismo diseño, podrá utilizar un rango de nombres de hojas en fórmulas 3D. Para
agregar otra hoja de cálculo a la consolidación, sólo tendrá que mover la hoja al rango al que
haga referencia la fórmula.
Consolidar por posición

Consolide los datos por posición cuando los datos de todas las áreas de origen se organicen en un
orden y una ubicación idénticos; por ejemplo, si cuenta con datos de una serie de hojas de cálculo
creadas desde la misma plantilla, puede consolidar los datos por posición.
Puede configurar la consolidación de modo que se actualice automáticamente cuando cambien
los datos de origen, pero no podrá modificar qué celdas y rangos se incluyen en la consolidación.
También puede actualizar la consolidación manualmente, lo que le permitirá cambiar las celdas y
los rangos incluidos en ella.
Consolidar por categorías
Consolide los datos por categorías si desea resumir un conjunto de hojas de cálculo que tienen los
mismos rótulos de filas y columnas pero organizan los datos de forma diferente. Este método
combina los datos que tienen rótulos coincidentes en cada hoja de cálculo.
Puede configurar la consolidación de modo que se actualice automáticamente cuando cambien
los datos de origen, pero no podrá modificar qué celdas y rangos se incluyen en la consolidación.
Como alternativa, puede actualizar la consolidación manualmente, lo que le permitirá cambiar las
celdas y los rangos incluidos en ella.
Otros modos de combinar datos

Puede crear un informe de tabla dinámica a partir de varios rangos de consolidación. Este método
es similar al de consolidación por categorías, pero ofrece una mayor flexibilidad para reorganizar
las categorías.
Buscar objetivo
Buscar objetivo es parte de una serie de comandos a veces denominados herramientas de análisis
Y si 1 . En el caso de que conozca el resultado deseado de una fórmula sencilla, pero no la variable
que determina el resultado, podrá utilizar la función Buscar objetivo haciendo clic en Buscar
objetivo en el menú Herramientas. Al realizar una búsqueda de objetivo 2 , Microsoft Excel varía
el valor de celda específica hasta que una fórmula dependiente de dicha celda devuelve el
resultado deseado.
Por ejemplo, utilice Buscar objetivo para cambiar de forma incremental el tipo de interés de la
celda B3 hasta que el valor del pago de B4 sea igual a $900,00.
Ajustar el valor de una celda para obtener un resultado específico para otra
celda
1. En el menú Herramientas, haga clic en Buscar objetivo.
2. En el cuadro Definir celda, escriba la referencia de la celda que contenga la fórmula que
desee resolver.
3. En el cuadro Con el valor, introduzca el resultado que desee.
4. En el cuadro Para cambiar la celda, introduzca la referencia de la celda que contenga el
valor que desee ajustar. A esta celda debe hacer referencia la fórmula en la celda
especificada del cuadro Definir celda
1
Análisis Y si: proceso de cambio de los valores de celdas para ver cómo afectan esos cambios al resultado de
fórmulas de la hoja de cálculo. Por ejemplo, variar la tasa de interés que se utiliza en una tabla de amortización para
determinar el importe de los pagos.
2
Buscar objetivo: método para buscar un valor específico para una celda ajustando el valor de otra celda. Cuando se
busca un objetivo, Excel varía el valor de una celda que especifique hasta que una fórmula que sea dependiente de
esa celda devuelva el resultado deseado.

Solver
Solver es parte de una serie de comandos a veces denominados herramientas de análisis Y si. Con
Solver, puede buscarse el valor óptimo para una fórmula de celda, denominada celda objetivo, en
una hoja de cálculo. Solver funciona en un grupo de celdas que estén relacionadas, directa o
indirectamente, con la fórmula de la celda objetivo. Solver ajusta los valores en las celdas
cambiantes que se especifiquen, denominadas celdas ajustables, para generar el resultado
especificado en la fórmula de la celda objetivo. Pueden aplicarse restricciones para restringir los
valores que puede utilizar Solver en el modelo y las restricciones pueden hacer referencia a otras
celdas a las que afecte la fórmula de la celda objetivo.
Utilice Solver para determinar el valor máximo o mínimo de una celda cambiando otras celdas,
por ejemplo, puede cambiar el importe del presupuesto previsto para publicidad y ver el efecto
sobre el margen de beneficio.
Ejemplo de una evaluación de Solver
En el siguiente ejemplo, el nivel de publicidad de cada trimestre afecta al número de unidades
vendidas, determinando indirectamente el importe de los ingresos por ventas, los gastos
asociados y los beneficios. Solver puede modificar los presupuestos trimestrales de publicidad
(celdas B5:C5), con una restricción total máxima de 20.000 $ (celda F5), hasta que el valor total
de beneficios alcance el importe máximo posible. Los valores en las celdas ajustables se utilizan
para calcular los beneficios de cada trimestre, por tanto están relacionados con la fórmula en la
celda objetivo F7, =SUMA(Q1 Beneficios:Q2 Beneficios).
Una vez ejecutado Solver, los nuevos valores son los siguientes:
Crear un informe de Solver

1. Definir y resolver un problema.
1. En el menú Herramientas, haga clic en Solver. Si el comando Solver no está
disponible en el menú Herramientas, deberá instalar el programa de
complemento Solver.

2. En el cuadro Celda objetivo, escriba una referencia de celda 3 o un nombre 4 para

la celda objetivo. La celda objetivo debe contener una fórmula 5 .
Para que el valor de la celda objetivo sea el valor máximo posible, haga
clic en Máx.
Para que el valor de la celda objetivo sea el valor mínimo posible, haga clic
en Mín.
Para que la celda objetivo tenga un valor determinado, haga clic en Valor
y, a continuación, introduzca el valor en el cuadro.
4. En el cuadro Cambiando la celda, introduzca un nombre o referencia para cada
celda ajustable, separando con comas las referencias no adyacentes. Las celdas
ajustables deben estar directa o indirectamente relacionadas con las celdas
objetivo. Pueden especificarse 200 celdas ajustables como máximo.
5. Si desea que Solver proponga automáticamente las celdas ajustables basadas en la
celda objetivo, haga clic en Autorredistribuir.
6. En el cuadro Sujetas a las siguientes restricciones, introduzca todas las
restricciones que desee aplicar.
7. Haga clic en Resolver, lleve a cabo una de las acciones siguientes:
3
Referencia de celda: conjunto de coordenadas que ocupa una celda en una hoja de cálculo. Por ejemplo, la
referencia de la celda que aparece en la intersección de la columna B y la fila 3 es B3.
4
Nombre: palabra o cadena de caracteres que representa una celda, rango de celdas, fórmula o valor constante.
Utilice nombres fáciles de entender, como Productos, para referirse a rangos difíciles de entender, como
Ventas!C20:C30.
5
Fórmula: secuencia de valores, referencias de celda, nombres, funciones u operadores de una celda que producen
juntos un valor nuevo. Una formula comienza siempre con el signo igual (=).

Para mantener los valores de la solución en la hoja de cálculo, haga clic en

Conservar la solución de Solver en el cuadro de diálogo Resultados de
Solver.
Para restaurar los datos originales, haga clic en Restaurar valores
originales.
Sugerencia: Puede interrumpirse el proceso de solución presionando ESC.
Microsoft Excel actualiza la hoja de cálculo con el último valor encontrado para
las células ajustables.
2. Si Solver encuentra una solución, puede hacer clic en un tipo de informe en el cuadro
Informes y, a continuación, hacer clic en Aceptar.
El informe se crea en una nueva hoja de cálculo del libro.
Agregar, cambiar o eliminar una restricción en Solver.

Agregar una restricción
1. En el cuadro de diálogo Parámetros de Solver de Sujetas a las restricciones,
haga clic en Agregar.
2. En el cuadro Referencia de celda, escriba la referencia de celda o el nombre del

rango de celdas para los que desee restringir el valor.
3. Haga clic en la relación ( <=, =, >=, Ent, o Bin ) que desee que haya entre la celda
a la que se hace referencia y la restricción. Si hace clic en Ent, en el cuadro
Restricción aparecerá "entero". Si hace clic en Bin, en el cuadro Restricción
aparecerá "binario".
4. En el cuadro Restricción, escriba un número, una referencia de celda, un nombre
o una fórmula.
Para aceptar una restricción y agregar otra, haga clic en Agregar.
Para aceptar la restricción y regresar al cuadro de diálogo Parámetros de
Solver, haga clic en Aceptar.
Notas: Solamente pueden aplicarse las relaciones Ent y Bin en las restricciones en celdas
ajustables.

o Si se activa la casilla de verificación Adoptar modelo lineal en el cuadro de

diálogo Opciones de Solver, no habrá límite en el número de restricciones. En
problemas no lineales, cada celda ajustable puede tener hasta 100 restricciones,
además de límites y restricciones enteras en las variables.
Cambiar o eliminar una restricción
7. En el cuadro de diálogo Parámetros de Solver bajo la sección Sujetas a las
siguientes restricciones, haga clic en la restricción que desee cambiar o eliminar.
8. Haga clic en Cambiar y realice los cambios, o haga clic en Eliminar.
Cambiar la forma en que Solver encuentra soluciones

1. En el menú Herramientas, haga clic en Solver. En el cuadro de diálogo Parámetros de
Solver, haga clic en Opciones.
2. En el cuadro de diálogo Opciones de Solver, defina una o más de las siguientes opciones:
Tiempo de solución e iteraciones
o En el cuadro Tiempo, escriba el número de segundos que desea permitir como
tiempo de solución.
o En el cuadro Iteraciones, escriba el número máximo de iteraciones que desea
permitir.
Nota Si el proceso de solución alcanza el tiempo máximo o el número máximo de iteraciones
permitidas antes de que Solver encuentre una solución, Solver mostrará el cuadro de
diálogo Mostrar solución tentativa.
Grado de precisión
o En el cuadro Precisión, escriba el grado de precisión que desee; cuanto menor sea
el número, mayor será la precisión.

Tolerancia de enteros
o En el cuadro Tolerancia, introduzca el porcentaje de error que desea permitir en
la solución.
Grado de convergencia
o En el cuadro Convergencia, escriba la cantidad de cambio relativo que desea
permitir en las últimas cinco iteraciones antes de que se detenga Solver con una
solución; cuanto menor sea el número, menor será el cambio relativo que se
permite.
Nota Puede utilizar el botón Ayuda del cuadro de diálogo para obtener más información sobre
otras opciones.
4. En el cuadro de diálogo Parámetros de Solver, haga clic en Resolver o en Cerrar.
Guardar un modelo de problema de Solver

1. Definir y resolver un problema.
2. En el menú Herramientas, haga clic en Solver.
3. Haga clic en Opciones.
4. Haga clic en Guardar modelo.
5. Introduzca la referencia de la primera celda de un rango vertical o de las celdas vacías en
que desee colocar el modelo de problema.
Sugerencia: Pueden guardarse las últimas selecciones realizadas en el cuadro de diálogo
Parámetros de Solver de una hoja de cálculo, haciendo clic en la opción Guardar del menú
Archivo. También puede definirse más de un problema en una hoja de cálculo, utilizando
Guardar modelo para guardar problemas individualmente.
Guardar en Solver los valores de celdas ajustables como un

escenario
1. Definir un problema.
2. En el cuadro de diálogo Resultados de Solver, haga clic en Guardar escenario.
3. En el cuadro Nombre del escenario, escriba un nombre para el escenario 6 .
Sugerencias:
• Para mostrar los diferentes conjuntos de valores de celdas ajustables que se han guardado,
muestre cada escenario.
6
Escenario: conjunto de valores de entrada, al que se ha asignado un nombre, que puede sustituirse en un modelo de
hoja de cálculo.

• Para crear un escenario sin guardar la solución de Solver o sin mostrar los resultados en la hoja
de cálculo, guarde el escenario en el cuadro de diálogo Resultados de Solver y, a continuación,
haga clic en Restaurar valores originales.
Resolvamos ahora el siguiente ejemplo del problema de mezcla de productos, mostrado

en la Figura:
Suponga que trabajamos para una empresa farmacéutica que puede producir seis productos en su
planta. La producción de cada producto requiere mano de obra y materia prima.
La fila 4 de la Figura contiene las horas de mano de obra necesarias para producir una libra
(454 gramos) de cada producto, y la fila 5 indica las libras de materia prima necesarias para
producir una libra de cada producto. Por ejemplo, para producir una libra del producto 1 se
requieren seis horas de mano de obra y 3,2 libras (1.453 gramos) de materia prima.
Para cada fármaco, el precio por libra se indica en la fila 6, el coste unitario por libra se
indica en la fila 7 y las ganancias por libra se indican en la fila 9. Por ejemplo, el producto
2 se vende a 11 dólares la libra, el coste unitario es de 5,7 dólares por libra y las ganancias
son 5,3 dólares por libra.
La demanda de este mes de cada fármaco se indica en la fila 8. Por ejemplo, la demanda del
producto 3 es de 1.041 libras (472,6 kilos).
Para este mes hay 4.500 horas de mano de obra y 1.600 libras (726,4 kilos) de materia prima
disponibles. ¿Cómo puede esta empresa aumentar sus ganancias mensuales?
Básicamente, Solver es un motor de optimización que realiza eficazmente la búsqueda por ensayo
y error. Un elemento clave para solucionar el problema de la mezcla de productos es calcular
eficazmente el uso de recursos y las ganancias asociadas a cada mezcla de productos. Una
herramienta importante que podemos utilizar para realizar este cálculo es la función
SUMAPRODUCTO.
La función SUMAPRODUCTO multiplica los valores correspondientes en los rangos de celdas y
devuelve la suma de esos valores. Todos los rangos de celdas utilizados en una evaluación de

SUMAPRODUCTO deben tener las mismas dimensiones, lo que implica que se puede utilizar
SUMAPRODUCTO con dos filas o dos columnas, pero no con una columna y una fila.
Para ejemplificar el uso de la función SUMAPRODUCTO en nuestro ejemplo de mezcla de
productos, tratemos de calcular nuestro uso de recursos. Para ello, necesitamos realizar el
siguiente cálculo:
(Mano de obra utilizada por libra del fármaco 1) *(Libras del fármaco 1 producidas) +
...
(Mano de obra utilizada por libra del fármaco 6) *(Libras del fármaco 6 producidas)
En nuestra hoja de cálculo, podríamos calcular el uso de mano de obra (de un modo tedioso)
como D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4. De igual forma, el uso de materia
prima se podría calcular como D2*D5+E2*E5+F2*F5+G2*G5+H2*H5+I2*I5. Si escribir estas
fórmulas en una hoja de cálculo para seis productos es una tarea laboriosa, imagínese cómo sería
si trabajara con una empresa que fabricara, por ejemplo, 50 productos en su planta.
Una forma mucho más sencilla de calcular el uso de mano de obra y materia prima consiste en
copiar de D14 a D15 la fórmula:
SUMAPRODUCTO($D$2:$I$2,D4:I4)
Esta fórmula calcula D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4 (que es nuestro uso de

mano de obra) y es mucho más fácil de escribir.
Observe que hemos utilizado el signo $ con el rango D2:I2, de forma que cuando copiemos la
fórmula podamos seguir obteniendo la mezcla de productos de la fila 2. La fórmula
de la celda D15 calcula el uso de materia prima.
De forma similar, podemos obtener nuestras ganancias calculando:
(Ganancias del fármaco 1 por libra) *(Libras del fármaco 1 producidas) +
(Ganancias del fármaco 2 por libra) *(Libras del fármaco 2 producidas) +
...
(Ganancias del fármaco 6 por libra) *(Libras del fármaco 6 producidas).
Las ganancias se calculan fácilmente en la celda D12 con la fórmula:

SUMAPRODUCTO(D9:I9,$D$2:$I$2)
Ahora podemos identificar las tres partes de nuestro modelo Solver de mezcla de productos:

Celda objetivo Celdas cambiantes Restricciones
Nuestro objetivo es El número de libras No utilizar más mano de obra y materia prima que
aumentar las producidas de cada la disponible. Es decir, los valores de las celdas
ganancias producto (indicado en el D14:D15 (los recursos utilizados) deben ser
(calculadas en la rango de celdas D2:I2). menores o iguales que los valores de las celdas
celda D12). F14:F15 (los recursos disponibles).
No producir más cantidad de un fármaco que la

demandada. Es decir, los valores de las celdas
D2:I2 (las libras producidas de cada fármaco)
deben ser menores o iguales que la demanda de
cada fármaco (indicada en las celdas D8:I8).
No podemos producir una cantidad negativa de

ningún fármaco.
¿Cómo puedo especificar este modelo en Solver?

Ahora explicaremos cómo especificar la celda objetivo, las celdas cambiantes y las restricciones
en Solver. Después, no tendrá más que hacer clic en el botón Resolver y Solver encontrará una
mezcla de productos que suponga un aumento de las ganancias.
1. Para empezar, seleccione Solver en el menú Herramientas. Aparecerá el cuadro de diálogo
Parámetros de Solver.
2. Para especificar la celda objetivo, haga clic en el cuadro Definir celda objetivo y, a
continuación, seleccione nuestra celda de ganancias (la celda D12). Para especificar nuestras
celdas cambiantes, haga clic en el cuadro Cambiando las celdas y, después, elija el rango
D2:I2, que contiene las libras producidas de cada fármaco. El cuadro de diálogo debe ser
similar al que se muestra en la figura siguiente.

3. Ahora podemos agregar restricciones al modelo. Haga clic en el botón Agregar. Aparecerá el
cuadro de diálogo Agregar restricción.
4. Para agregar las restricciones de uso de recursos, haga clic en el cuadro Referencia de celda
y, después, seleccione el rango D14:D15. Elija <= de la lista en el centro del cuadro de
diálogo. Haga clic en el cuadro Restricción y, a continuación, seleccione el rango de celdas
F14:F15.
Ahora nos hemos asegurado de que cuando Solver pruebe distintos valores para las celdas
cambiantes, sólo considerará las combinaciones que satisfagan D14 <= F14 (la mano de obra
utilizada es menor o igual que la mano de obra disponible) y D15 <= F15 (la materia prima
utilizada es menor o igual que la materia prima disponible).
5. Ahora haga clic en Agregar en el cuadro de diálogo Agregar restricción para especificar las
restricciones de demanda. Sólo tiene que rellenar el cuadro de diálogo Agregar restricción
tal como se muestra en la siguiente figura.

Al agregar estas restricciones nos aseguramos de que cuando Solver pruebe distintas
combinaciones para los valores de las celdas cambiantes, sólo considerará las combinaciones
que satisfagan las siguientes condiciones:
• D2 <= D8 (la cantidad del fármaco 1 es menor o igual que la demanda del fármaco 1)
• E2 <= E8 (la cantidad del fármaco 2 es menor o igual que la demanda del fármaco 2)
• F2 <= F8 (la cantidad del fármaco 3 es menor o igual que la demanda del fármaco 3)
• G2 <= G8 (la cantidad del fármaco 4 es menor o igual que la demanda del fármaco 4)
• H2 <= H8 (la cantidad del fármaco 5 es menor o igual que la demanda del fármaco 5)
• I2 <= I8 (la cantidad del fármaco 6 es menor o igual que la demanda del fármaco 6)
6. Haga clic en Aceptar en el cuadro de diálogo Agregar restricción. El cuadro de diálogo

Parámetros de Solver debe ser similar al que se muestra en la figura
siguiente.
7. Especificamos la restricción de que ninguna de las celdas cambiantes sea negativa en el

cuadro de diálogo Opciones de Solver, que se abre haciendo clic en
el botón Opciones del cuadro de diálogo Parámetros de Solver.

Seleccione las opciones Adoptar modelo lineal y Adoptar no-negativo y, a continuación,

haga clic en Aceptar.
Al seleccionar la opción Adoptar no-negativo nos aseguramos de que Solver sólo considere las
combinaciones de celdas cambiantes en las que cada celda cambiante adopte un valor no negativo.
Hemos seleccionado Adoptar modelo lineal porque el problema de mezcla de productos es un tipo
especial de problema de Solver denominado modelo lineal . Básicamente, un modelo de Solver es
lineal si se cumplen las siguientes condiciones:
• La celda objetivo se calcula sumando términos de la forma (celda cambiante)*(constante).
• Cada restricción satisface los requisitos del modelo lineal. Esto significa que cada restricción se
evalúa sumando términos de la forma (celda cambiante)*(constante)
y comparando estas sumas con una constante.
El cálculo sigue un modelo en el que el valor de la celda objetivo se obtiene sumando

términos de la forma (celda cambiante)*(constante).
Nuestra restricción de mano de obra se evalúa comparando la mano de obra disponible con el
valor obtenido de:
(Mano de obra utilizada por libra del fármaco 2)*(Libras del fármaco 2 producidas) +
...
(Mano de obra utilizada por libra del fármaco 6)*(Libras del fármaco 6 producidas)
Por tanto, la restricción de mano de obra se evalúa sumando términos de la forma (celda
cambiante)*(constante) y comparando estas sumas con una constante.
Tanto la restricción de mano de obra como la restricción de materia prima satisfacen los
requisitos del modelo lineal.

Nuestras restricciones de demanda adoptan la forma

(Fármaco 1 producido)<=(Demanda de fármaco 1)
...
Cada restricción de demanda satisface también los requisitos del modelo lineal, porque cada
una de ellas se evalúa sumando términos de la forma (celda cambiante)*(constante)
y comparando estas sumas con una constante.
8. Después de hacer clic en Aceptar en el cuadro Opciones de Solver, volvemos al cuadro de
diálogo Solver principal. Cuando hagamos clic en Resolver, Solver calculará una solución
óptima (si existe) para nuestro modelo de mezcla de productos.
Una solución óptima al modelo de mezcla de productos sería un conjunto de valores de celdas
cambiantes (libras producidas de cada fármaco) que aumentara las ganancias entre el conjunto de
todas las soluciones viables. Como ya hemos explicado, una solución viable es un conjunto de
valores de celdas cambiantes que satisfacen todas las restricciones. Los valores de las celdas
cambiantes mostrados en la Figura son una solución viable, ya que ninguno de los niveles de
producción es negativo, ninguno de ellos excede la demanda y el uso de recursos no es mayor
que los recursos disponibles.
Una solución viable al problema de mezcla de productos satisface las restricciones.
Los valores de las celdas cambiantes mostrados en la Figura representan una solución inviable
por los siguientes motivos:
Producimos más cantidad del fármaco 5 que la demandada.
Utilizamos más mano de obra que la disponible.
Utilizamos más materia prima que la disponible.

Una solución inviable al problema de mezcla de productos no satisface las restricciones que hemos
definido.
Después de hacer clic en Resolver, Solver encuentra rápidamente la solución óptima mostrada en
la Figura. Debe seleccionar Conservar la solución de Solver para conservar los valores de la
solución óptima en la hoja de cálculo.
La solución óptima al problema de mezcla de productos.
Nuestra empresa farmacéutica puede aumentar sus ganancias mensuales a un nivel de 6.625,20
dólares produciendo 596,67 libras (270,8 kilos) del fármaco 4, 1.084 libras (492,1 kilos) del
fármaco 5 y ninguno de los demás fármacos. No podemos determinar si somos capaces de
obtener el beneficio máximo de 6.625,20 dólares de otras maneras. De lo único de lo que estamos
seguros es de que con nuestra demanda y nuestros recursos limitados no hay forma de ganar este
mes más de 6.625,20 dólares.
¿Un modelo Solver tiene siempre una solución?
Supongamos que debe satisfacerse la demanda de cada producto. En ese caso, tendremos que
cambiar nuestras restricciones de demanda de D2:I2 <= D8:I8 a D2:I2 >= D8:I8. Para cambiar
esta restricción:

1. Abra Solver.
2. Haga clic en la restricción D2:I2 <= D8:I8 y luego en Cambiar.
Aparecerá el cuadro de diálogo Cambiar restricción.
3. En el cuadro del centro, elija >= y, a continuación, haga clic en Aceptar.
Con esto nos aseguramos de que Solver sólo considere los valores de las celdas cambiantes
que satisfacen todas las demandas.
Al hacer clic en Resolver, aparecerá el mensaje Solver no ha podido encontrar una solución
factible. Este mensaje significa que con nuestros recursos limitados no podemos satisfacer la
demanda de todos los productos. No hemos cometido ningún error en nuestro modelo. Solver
nos indica simplemente que si queremos satisfacer la demanda de cada producto, debemos
agregar más mano de obra, más materia prima o ambas cosas.
¿Qué ocurre si los valores definidos no convergen?
Veamos qué sucede si permitimos una demanda ilimitada para cada producto y permitimos
también que se produzcan cantidades negativas de cada fármaco. Para encontrar la solución
óptima para esta situación:
1. Abra Solver.
2. Haga clic en el botón Opciones y, a continuación, desactive la casilla de verificación
Adoptar no-negativo.
3. En el cuadro de diálogo Parámetros de Solver, haga clic en la restricción de demanda D2:I2
<= D8:I8 y luego en Eliminar para quitar la restricción.
Al hacer clic en Resolver, Solver muestra el mensaje Los valores de la celda objetivo no
convergen. Este mensaje significa que si debe aumentarse el valor de la celda objetivo
(como en nuestro ejemplo), hay soluciones viables con valores en la celda objetivo
arbitrariamente grandes. (Si debe reducirse el valor de la celda objetivo, este mensaje significa
que hay soluciones viables con valores en la celda objetivo arbitrariamente pequeños.)
En nuestra situación, al permitir la producción negativa de un fármaco, lo que de hecho estamos

"creando" son recursos que se pueden utilizar para producir cantidades arbitrariamente grandes de
otros fármacos. Dada nuestra demanda ilimitada, esto nos permite obtener ganancias ilimitadas.

En una situación real, no podemos ganar una cantidad infinita de dinero. En resumen, si aparece
el mensaje Los valores de la celda objetivo no convergen, el modelo contiene un error.
Escenarios
Los escenarios son parte de una serie de comandos a veces denominados herramientas de análisis
Y si. Un escenario es un conjunto de valores que Microsoft Excel guarda y puede sustituir
automáticamente en la hoja de cálculo. Puede utilizar los escenarios para prever el resultado de
un modelo de hoja de cálculo. Puede crear y guardar diferentes grupos de valores en una hoja de
cálculo y, a continuación, pasar a cualquiera de estos nuevos escenarios para ver distintos
resultados.
Crear escenarios Supongamos, por ejemplo, que desea preparar un presupuesto, pero no sabe
con exactitud sus ingresos. En este caso, podrá definir valores diferentes para dichos ingresos y,
seguidamente, pasar de un escenario a otro para realizar un análisis Y si.
En el ejemplo anterior, cuyo escenario podría denominarse Peor opción, establezca el valor de la
celda B1 en 50.000 $ y el de la celda B2 en 13.200 $.
Al segundo escenario podría denominarlo Mejor opción y cambiar los valores de B1 a 150.000 $
y de B2 a 26.000 $.
Informes de resumen de escenario Para comparar varios escenarios, puede crear un informe
que los resuma en la misma página. El informe puede listar los escenarios unos junto a otros o
resumirlos en un informe de tabla dinámica.
Crear un escenario
1. En el menú Herramientas, elija Escenarios.
2. Haga clic en Agregar.
3. En el cuadro Nombre de escenario, escriba un nombre para el escenario.
4. En el cuadro Celdas cambiantes, introduzca las referencias de las celdas que desee
cambiar.
Nota Para preservar los valores originales de las celdas cambiantes, cree un escenario
que utilice los valores originales de las celdas antes de crear escenarios que cambien
valores.

5. En Proteger, seleccione las opciones que desee.

7. En el cuadro de diálogo Valores del escenario, introduzca los valores que desee para las
celdas cambiantes.
8. Para crear el escenario, haga clic en Aceptar.
9. Si desea crear escenarios adicionales, haga clic de nuevo en Agregar y repita el
procedimiento. Cuando termine de crear los escenarios, elija Aceptar y haga clic en
Cerrar en el cuadro de diálogo Administrador de escenarios.
Crear un informe de resumen de escenario

2. Haga clic en Resumen.
3. Haga clic en Resumen o Tabla dinámica.
4. En el cuadro Celdas resultantes, escriba las referencias de las celdas que hacen
referencia a las celdas cuyos valores cambian los escenarios. Separe las referencias
múltiples mediante comas.
Nota No se necesitan las celdas resultantes para generar un informe de resumen de escenarios,
pero sí se necesitan en un informe de tabla dinámica.
Eliminar un escenario
2. Haga clic en el nombre del escenario que desee eliminar y, a continuación, haga clic en
Eliminar.
Mostrar un escenario
Cuando se muestra un escenario, se cambian los valores de las celdas guardadas como parte de
ese escenario.
2. Haga clic en el nombre del escenario que desee presentar.
3. Haga clic en Mostrar.
Modificar un escenario
Si se mantiene el nombre original de un escenario después de haber hecho cambios en el mismo,
los nuevos valores de las celdas cambiantes sustituirán a los valores del escenario original.


2. Haga clic en el nombre del escenario que desee modificar y, a continuación, haga clic en
Modificar.
3. Haga los cambios que desee.
4. En el cuadro de diálogo Valores del escenario, introduzca los valores que desee para las
celdas cambiantes.
o Para guardar los cambios, haga clic en Aceptar.
o Para regresar al cuadro de diálogo Administrador de escenarios sin cambiar el
escenario actual, haga clic en Cancelar.
Evitar cambios en un escenario

2. En el cuadro Escenarios, haga clic en el nombre del escenario.
3. Haga clic en Modificar.
o Para evitar que otros usuarios modifiquen un escenario, active la casilla de
verificación Evitar cambios.
o Para quitar un escenario de la lista que aparece en el cuadro de diálogo
Administrador de escenarios, active la casilla de verificación Ocultar.
6. En el cuadro de diálogo Valores del escenario, elija Aceptar y, a continuación, haga clic
en Cerrar.
7. En el menú Herramientas, elija Proteger y, a continuación, haga clic en Proteger hoja.
8. Active la casilla de verificación Proteger hoja y contenido de celdas bloqueadas y
compruebe que la casilla de verificación Modificar escenarios está desactivada.
Nota Si una hoja de cálculo está protegida pueden agregarse escenarios, pero no podrá modificar
ni eliminar los escenarios existentes mientras la hoja de cálculo se encuentre protegida a no ser
que se haya desactivado la casilla de verificación Evitar cambios en el cuadro de diálogo
Modificar escenario. Aún puede modificar los valores de las celdas cambiantes en la hoja de
cálculo, a no ser que las celdas estén bloqueadas.

SEMINARIO Analizar - Datos - 1

Cargado por

Copyright:

Formatos disponibles

SEMINARIO Analizar - Datos - 1

Cargado por

Información del documento

Título original

Derechos de autor

Formatos disponibles

Compartir este documento

Compartir o incrustar documentos

Opciones para compartir

¿Le pareció útil este documento?

¿Este contenido es inapropiado?

Copyright:

Formatos disponibles

SEMINARIO Analizar - Datos - 1

Cargado por

Copyright:

Formatos disponibles

Seminario de Excel

Autofiltro: Cuando utilice el comando Autofiltro, aparecerán las flechas de Autofiltro a la

Microsoft Excel indica los elementos filtrados en azul.

Elaborado por: Ing. Izael Urieta M. Pag.: 1

Filtrar un rango (Autofiltro)

9. Para agregar otros criterios, haga clic en Y u O, y repita el paso anterior.

Elaborado por: Ing. Izael Urieta M. Pag.: 2

Filtrar por números mayores o menores que otro número

Elaborado por: Ing. Izael Urieta M. Pag.: 3

Filtrar utilizando criterios avanzados

Elaborado por: Ing. Izael Urieta M. Pag.: 4

Ejemplos De Criterios Complejos

Elaborado por: Ing. Izael Urieta M. Pag.: 5

Elaborado por: Ing. Izael Urieta M. Pag.: 6

Elaborado por: Ing. Izael Urieta M. Pag.: 7

Elaborado por: Ing. Izael Urieta M. Pag.: 8

Actualización automática: Excel actualizará automáticamente los subtotales y el total

Elaborado por: Ing. Izael Urieta M. Pag.: 9

Insertar subtotales individuales

3. Haga clic en Orden ascendente o en Orden descendente .

Elaborado por: Ing. Izael Urieta M. Pag.: 10

Insertar subtotales anidados

Elaborado por: Ing. Izael Urieta M. Pag.: 11

Informes y gráficos de resumen

Elaborado por: Ing. Izael Urieta M. Pag.: 12

Elaborado por: Ing. Izael Urieta M. Pag.: 13

En la sección de esquema se pueden encontrar tres tipos de controles:

2 Subtotales de cada grupo

3 Filas de la hoja de cálculo

En la hoja de cálculo presentada en la imagen anterior, al hacer clic en el botón de nivel 2 se

Elaborado por: Ing. Izael Urieta M. Pag.: 14

Crear un nuevo grupo de esquema dentro de un grupo existente

Elaborado por: Ing. Izael Urieta M. Pag.: 15

1. Seleccione las filas incluidas en un grupo.

Funciones de resumen para análisis de datos

Elaborado por: Ing. Izael Urieta M. Pag.: 16

Crear una tabla de datos de una variable

Elaborado por: Ing. Izael Urieta M. Pag.: 17

o Si la tabla de datos está orientada a fila, escriba la fórmula en la columna situada a

Agregar una fórmula a una tabla de datos de una variable

Crear una tabla de datos de dos variables

Elaborado por: Ing. Izael Urieta M. Pag.: 18

Elaborado por: Ing. Izael Urieta M. Pag.: 19

Consolidar por posición

Elaborado por: Ing. Izael Urieta M. Pag.: 20

Elaborado por: Ing. Izael Urieta M. Pag.: 21

Crear un informe de Solver

Elaborado por: Ing. Izael Urieta M. Pag.: 22

2. En el cuadro Celda objetivo, escriba una referencia de celda 3 o un nombre 4 para

Elaborado por: Ing. Izael Urieta M. Pag.: 23

 Para mantener los valores de la solución en la hoja de cálculo, haga clic en

Agregar, cambiar o eliminar una restricción en Solver.

2. En el cuadro Referencia de celda, escriba la referencia de celda o el nombre del

Elaborado por: Ing. Izael Urieta M. Pag.: 24

o Si se activa la casilla de verificación Adoptar modelo lineal en el cuadro de

Cambiar la forma en que Solver encuentra soluciones

Elaborado por: Ing. Izael Urieta M. Pag.: 25

Guardar un modelo de problema de Solver

Guardar en Solver los valores de celdas ajustables como un

Para mantener los valores de la solución en la hoja de cálculo, haga clic en

Esta fórmula calcula D2D4+E2E4+F2F4+G2G4+H2H4+I2I4 (que es nuestro uso de

No producir más cantidad de un fármaco que la

No podemos producir una cantidad negativa de