Microsoft Excel

Curso práctico de hoja de cálculo

Con el programa Microsoft Excel, versiones 97 – 2000

Nivel Avanzado
 E X C E L – N I V E L A V A N Z A D O

Indice

Tema 1: Bases de datos en Excel

1. Filtros automáticos
2. Filtros avanzados
3. Ordenar los datos de una lista
4. Subtotales
5. Introducción a las tablas dinámicas
6. Tablas dinámicas basadas en listas
7. Tablas dinámicas basadas en rangos de consolidación múltiples

Tema 2: Herramientas de análisis de la hoja de cálculo

1. Las Auditorías

Tema 3: Resolver problemas matemáticos

1. Introducción
2. Buscar Objetivo
3. Solver

Tema 4: El formato de los párrafos

1. Las alineaciones
2. Aplicar sangrías
3. Tabulaciones
4. Listas numeradas y con viñetas
5. Texto en columnas periodísticas
6. Notas al pie de página y al final del documento
7. Encabezados y pies de página

T E M A 1

Bases de datos en
Excel
todos los elementos únicos de la
1 Fitros Automáticos columna. Si seleccionamos un
elemento de la lista de una columna
específica, ocultará automáticamente
Los filtros automáticos permiten mostrar todas las filas que no contengan el
un subconjunto dentro de una lista con valor seleccionado.
tan solo hacer clic en el botón del
mouse. Veamos paso a paso como crear un filtro
automático.
Cuando seleccionamos el comando
Filtro en el menú Datos, y luego Partiremos para nuestro ejemplo de
Autofiltro Excel colocará flechas una tabla como la de la ilustración, en
desplegables directamente en los rótulos la que a varios vendedores se
de columnas de la lista. Si hacemos clic atribuyen determinados productos.
en una flecha aparecerá una lista con

2 – Nivel Avanzado Excel

 opción todas en el menú desplegable del
filtro aplicado, o Mostrar todas del menú
Situaremos el cursor en alguna celda Datos-Filtros).
dentro de la tabla para que la
aplicación pueda identificarla. Para cancelar el autofiltrado, basta con
acudir de nuevo al menú Datos,
Dirigiremos el cursor al menú Datos, y Filtros, y quitar la marca de la opción
dentro de éste, a la opción Filtros y Autofiltro haciendo clic sobre ella.
Autofiltro.
Haciendo uso de la opción
Hecho esto, junto a los encabezados Personalizar que encontramos en
de cada una de las columnas de todos los filtros podremos establecer
datos de nuestra tabla habrá criterios más complejos, donde
aparecido un pequeño botón con una intervengan, por ejemplo, criterios de
flecha que apunta hacia abajo. comparación, creando enunciados del
estilo Mostrar todos aquellos que
contengan una cifra mayor que 500.
Si pulsamos cualquiera de esas flechas
se desplegará un pequeño menú cuyo Configurar el filtro personalizado es
contenido serán los valores contenidos sencillo: en el cuadro de diálogo
en esa columna una sola vez. Personalizar seleccionaremos, primero,
el criterio de comparación en el menú
desplegable, y a continuación,
escribiremos el valor buscado.

Basta con escoger un valor de esa lista

para que la tabla pase a mostrar
únicamente las entradas que hacen
referencia al valor seleccionado (en En las dos casillas que vemos debajo
nuestro ejemplo hemos escogido el podemos establecer otro criterio de
nombre de un vendedor). filtrado: por ejemplo, si en la superior
escogemos mostrar las filas que tengan
un valor superior a 500, en la inferior
podemos pedir que sean visibles las que
tengan un valor inferior a 1000.
Veremos las que están entre 500 y 1000.

Justo entre las dos filas de criterios hay

un par de botones de opción
representadas por las conjunciones O e
Truco: si te fijas, las columnas Y. La opción Y está activada por
que se encuentran filtradas defecto: para que una fila sea mostrada
tienen el botón con una flecha deberán cumplirse necesariamente los
en su interior de color azul. dos criterios de filtrado. Si sólo una falla,
Haciendo clic sobre ella, no aparecerá en la lista.
desplegando la lista, y
escogiendo la opción Todas
retiraremos los filtros
aplicados.

Podemos combinar diferentes filtros, para ir

concretando cada vez más el objeto de
nuestra búsqueda, así como deshacer las
cribas ya realizadas (basta con escoger la

3 – Nivel Avanzado Excel

En cambio, si activamos la opción “O”,
simplemente con que se cumpla una de las
dos condiciones la fila será mostrada. Por
ejemplo, podemos seleccionar que sean
mostrados los que tengan escrita en la
celda la palabra “Barcelona” o bien las que
tengan “Girona”.

2 Fitros Avanzados

Puodemos recurrir a los filtros columnas que vamos a emplear en otro

avanzados cuando los automáticos se lugar de nuestra hoja de cálculo.
muestran insuficientes para resolver los
problemas que se nos puedan plantear:
por ejemplo, para aplicar más de dos
criterios a una sola columna, o crear
criterios que resulten de una fórmula, o
simplemente, para copiar el listado
filtrado en un lugar diferente al listado
original.

Cuando se usa un filtro avanzado, las

flechas desplegables de Autofiltro no
aparecen. En su lugar, se especifica el
criterio en un rango de criterios en la
hoja de cálculo. Para cambiar la forma
en que se filtran los datos, deberemos
cambiar los valores del rango de En las filas situadas bajo los rótulos de
criterios y volveremos a aplicar el filtro a criterios deberemos introducir los crite-
los datos. rios que deseamos buscar.

Para utilizar los filtros avanzados

deberemos repetir los encabezados de las

4 – Nivel Avanzado Excel

 Finalmente, pulsamos aceptar. En
efecto, como podemos ver en la imagen,
En esta ilustración hemos introducido un sólo aparecen aquellas filas que en
filtro para la columna Mesas y para la Mesas y en Sillas tengan un número
Sillas. Para que se muestren las filas superior al 30.
correspondientes, en ambas columnas
deberá haber una cifra superior a 30.

Una vez hemos introducido los criterios,

situaremos el cursor sobre cualquier lugar
de la tabla original, acudiremos al menú
Datos, la opción Filtros y Filtros
Avanzados. Surgirá una ventana como
ésta:

Naturalmente, el ejemplo es muy simple.

A partir de aquí puedes elaborar
condiciones tan complejas como te
interese.

En primer lugar deberemos determinar si Como pequeño truco, debo comentarte

deseamos que los datos sean filtrados que si en lugar de utilizar la condición
sobre la tabla original, o que la información “Y”, que hemos aplicado aquí, deseas
filtrada quede en otra tabla. En nuestro ver las que contengan una cifra mayor a
caso escogeremos la opción 50 en cualquiera de las dos columnas,
Copiar a otro lugar. deberás escribir los criterios en filas
diferentes, más o menos, así:
La casilla Rango de la lista aparece
cumplimentada con las referencias a las
celdas que contienen la tabla a filtrar. En En las ayuda en pantalla de la hoja de
principio deberían ser correctas. cálculo Excel podrás encontrar una
buena colección de ejemplos de filtros
En la casilla Rango de criterios indicaremos avanzados. A continuación
las celdas en que se encuentran los reproduciremos algunos de ellos
filtros. En nuestro explicados en detalle:
ejemplo, B16:C17.
Varias condiciones en una sola
Finalmente, deberemos indicar dónde debe columna Si deseas incluir más de una
ser insertada la tabla filtrada. Basta con condición en una columna, debemos
que indiquemos una sola celda: Excel escribirlas en filas distintas. Por ejemplo:
copiará los datos hacia abajo a partir de
ella. En nuestro ejemplo, lo haremos
desde A19.

También podemos activar la casilla Solo

registros únicos. En caso de datos
repetidos, no aparecerían en pantalla. En Mostrará aquellas filas en las que la
nuestro ejemplo, la dejaremos desactivada. columna provincia tenga los rótulos
“Barcelona”, “Girona” o “Tarragona”.

5 – Nivel Avanzado Excel

 Deseamos mostrar a aquellas personas
del departamento “Administración” que
tengan una nómina superior a 150.000
Y este ejemplo filtrará aquella en cuya pesetas. Deberemos escribir:
columna Ventas figure un 50, un 40 o un
30.

En este tipo de casos trabajamos con una

sola columna, y con la disyuntiva “O”. Si
deseamos, por ejemplo, ver los registros
que tengan una cantidad inferior a 50 Y
superior a 30 deberemos indicar los
criterios en la misma fila, tal y como
comentamos antes. Veamos el ejemplo:

Este supuesto, aplicado al ejemplo que

antes hemos estudiado, daría el siguiente
resultado:

Atención: También puedes

especificar varias condiciones
para diferentes columnas y
presentar sólo las filas que
cumplan todas las condiciones
mediante el comando
Autofiltro del menú Datos.

Veamos otro ejemplo en la misma línea.

En la siguiente tabla

6 – Nivel Avanzado Excel

 Uno de dos conjuntos de condiciones
para dos columnas Para buscar filas
que cumplan uno de dos conjuntos de
condiciones, donde cada conjunto inclu-
ye condiciones para más de una colum-
na, deberemos insertar los criterios en
filas independientes.

Por ejemplo, en la tabla del supuesto

anterior, el siguiente rango de criterios
presenta las filas que para el departa-
mento “Administración” incluyen una
nómina superior o igual a 200000, y
para “Almacén”, una inferior a 150000.

En cambio, si queremos ver a todos los

de Administración (sea cual sea su nó-
mina), o a aquellos que tengan una
nómina superior a 150000 (de cualquier
departamento) deberemos hacerlo en
filas diferentes, tal y como muestra la
siguiente ilustración

3 Ordenar los datos de una lista

A veces puede ser muy útil ordenar
alfabéticamente, o de mayor a menor los
datos de un listado. Existen dos formas
de llevar a cabo esta tarea, la primera,
más rápida, es también la más limitada.

Siguiendo este proceso sencillo,

deberemos seguir estos pasos:
Situaremos el cursor sobre cualquier
celda de la columna que vamos a
ordenar

Atención: Es muy importante

que no selecciones esa

7 – Nivel Avanzado Excel

 cursor en cualquier casilla Para resolver problemas tan comunes
de esa columna, pero no como este, nos dirigiremos al menú
selecciones nunca la Datosy la opción
Ordenar. Aparecerá
columna entera. una ventana como la siguiente:
columna, puesto que si lo
A continuación, pulsaremos alguno de
estos dos botones:

El primero ordenará de forma

ascendente los datos, y el segundo lo

hará de forma descendente.

Como podrás comprobar, además de esa

columna, los datos de las adyacentes
también se han movido, para que las filas
no pierdan relación.

Tenemos tres casillas para establecer

El otro procedimiento que te diferentescriterios de ordenación
comentábamos es más complejo, pero alfabética. Junto a cada uno de ellos,
permite realizar ordenaciones imposibles podemos determinar que sea
con el anterior, por ejemplo, ordenar por ascendente oscendente.
de
más de una columna al mismo tiempo (tal
vez deseemos ordenar los datos a partir
de la columna “1er. Apellidos”, y en caso
de repetidos, por la columna “2º Apellido”.

4 Subtotales

La inserción de subtotales automáticos Una manera fácil de hacerlo es ordees una

manera rápida de resumir datos nando la lista alfabéticamente con el en una lista. No
es necesario introducir comando Ordenar del menú Datos, fórmulas en la hoja de
cálculo para usar como hemos visto en el apartado antelos subtotales automáticos. Al
usar los rior. grupos de datos elegidos, Excel auto-
máticamente calcula los valores del Para calcular subtotales seguiremos el subtotal y
del total general, inserta y siguiente proceso:
rotula las filas de totales y esquematiza
la lista. Como en los ejemplos anteriores, parti mos de una sencilla hoja de cálculo
con
Para usar subtotales automáticos, los este aspecto (hay que observar que en
datos deben estar organizados en co-esta hoja de cálculo los nombres de lumnas
rotuladas. Por ejemplo, una lista vendedores, que será el campo en funde
registros de ventas puede incluir una ción del cual crearemos los subtotales columna
llamada vendedor, que contie-están ordenados):
ne nombre y una columna llamada Precio
que indica el precio unitario del producto.

Antes de insertar subtotales automáticos

deberemos agrupar las filas de los
elementos cuyo subtotal vamos a
calcular.

8 – Nivel Avanzado Excel

Para crear los subtotales dirigiremos el
cursor al menú Datos y ejecutaremos la
opción
Se Subtotales.
abrirá un pequeño cuadro de diálogo Para cada vendedor se calculará
en el que podremos configurar los el importe total de sus ventas,
siguientes parámetros:
mostrándose en negrita, y al final
Para cada cambio en... En esa de la tabla aparecerá el total
casilla indicaremos la columna que general de las ventas de todos
va a servir para agrupar los ellos.
subtotales. En nuestro ejemplo
agruparemos las ventas en base a Podemos cambiar la forma en que
los vendedores que las han se presentan los datos. Pulsando
efectuado.
los botones + y – que aparecen
Usar la función... Podemos escoger en estos casos en la parte
entre un amplio catálogo de izquierda de la hoja de cálculo
funciones. En nuestro caso haremos aumentar o disminuir el
sumaremos. grado de detalle de la tabla
(eliminando de la pantalla las
Agregar subtotal a... Finalmente ventas individuales y dejando sólo
indicaremos a qué campo debemos
agregar el subtotal. En el ejemplo
los subtotales, por ejemplo).
que estamos siguiendo agregaremos
el subtotal de cada vendedor al
campo precio total para saber qué
han vendido por separado. Este
parámetro siempre deberá recoger
alguna columna que contenga
números

Una vez establecidos todos

estos criterios pulsaremos el
botón Aceptar. Nuestra tabla
cambiará de aspecto para
convertirse en algo similar a
esto:

9 – Nivel Avanzado Excel

5 Introducción a las tablas dinámicas
Una tabla dinámica es una hoja de
cálculo interactiva que resume
rápidamente grandes cantidades de
datos usando el formato y los métodos
de cálculo que nosotros escojamos. Esa
tabla podrá girar los encabezados de
filas y columnas alrededor del área de
los datos principales para lograr distintas
presentaciones de los datos de origen.
Las tablas dinámicas proporcionan una
manera fácil de mostrar y analizar la
información resumida acerca de datos ya
creados.

Para crear tablas dinámicas usaremos

un asistente que nos guiará con
precisión en el proceso.. Lo
encontraremos en el menú Datos,
Asistente para tablas dinámicas. Los
diferentes pasos que componen el
proceso son:

Tipo de datos a utilizar. Se nos ofrecen

diversas configuraciones para nuestra
tabla dinámica. Son estas:

Lista o base de datos de Micro-

soft Excel. Crea un resumen de
datos de una lista con múltiples
columnas creadas en Excel. Las
columnas de la lista deberán
contener rótulos. Luego veremos un
ejemplo de este tipo de tabla
dinámica.

Fuente de datos externa. Se crea

una consulta y un resumen de los
datos a partir de archivos o tablas de
una base de datos externa creada
usando aplicaciones o sistemas de
administración de bases de datos
(Access, dBase, Oracle, etc.).

Rangos de consolidación múltiple.

Combina y resume datos de múltiples
rangos de hojas de cálculo de Excel
que disponen de rótulos de filas y de
columnas. También veremos un
ejemplo de este tipo próximamente.

Otra tabla dinámica. Utiliza datos de

otra tabla dinámica existente en el
mismo libro de trabajo

Origen de los datos. A continuación

deberemos seleccionar la ubicación de
los datos fuentes. En caso de trabajar

10 – Nivel Avanzado Excel

con rangos de consolidación múltiple columnas, podremos crear una tabla
deberemos indicar de forma separada dinámica que agrupe y resuma la
cada una de las tablas que contienen los información de manera clara. Para
datos. ilustrar este tema partiremos de una hoja
de cálculo en la que se contienen los
siguiente datos:

Distribución de la tabla dinámica. En

este paso organizaremos la distribución
de la tabla dinámica. Simplemente
deberemos arrastrar a las áreas Filas y
Columnas los campos que vamos a
utilizar como rótulos de campos de las
filas y las columnas en la tabla.
Deberemos seguir los siguientes pasos
para crear una tabla dinámica basada en
esta lista:

En el menú datos ejecutaremos el

Atención: Los puntos que

hemos indicado en este
proceso pueden sufrir algu-
na variación dependiendo
del tipo de tabla dinámica
que escojamos en el primer
paso del asistente.

6 Tablas dinámicas basadas en listas

asistente para tablas dinámicas.

En el primer paso del asistente

indicaremos que vamos a trabajar
con una lista o base de datos de
Microsoft Excel.

En el segundo paso seleccionaremos

el rango en el que se encuentran los
datos que vamos a mostrar a través
de la tabla dinámica.

A continuación deberemos construir la

Ubicación de la tabla dinámica. En el
tabla dinámica arrastrando los
cuarto y último paso de este proceso
botones situados a la derecha de la
deberemos seleccionar dónde queremos
ventana sobre la tabla de muestra
que aparezca la tabla dinámica. La
que vemos en la parte central. En
podemos incluir en cualquier hoja de
cálculo o elegir cualquier libro de trabajo.
el área de las filas y las columnas
organizaremos los campos de la
Si los datos que deseamos utilizar se manera en que deseemos. En el
encuentran en una tabla con varias área de datos incluiremos algún

11 – Nivel Avanzado Excel

 cálculo adicional con los datos
contenidos en la tabla (por ejemplo,
podemos obtener una suma total por
categorías, o promedios, valores El asistente para tablas dinámicas
máximos, mínimos, etc.). En este permite consolidar datos
ejemplo distribuiremos los campos
de la siguiente forma:
provenientes de diversas hojas de
cálculo creadas con Excel, lo que
es de gran utilidad para combinar
información de hojas de
presupuestos o informes que
tienen una distribución similar y
rótulos de filas y columnas
idénticos. Por ejemplo, las
siguientes cuatro hojas de cálculo
contienen un informe de las
ventas de los años 1992 y 1993
en los supermercados de Madrid
Finalmente seleccionaremos una y Barcelona de nuestro centro
ubicación para la tabla dinámica.
comercial.
Bastará con señalar la que será la
primera celda superior-izquierda de
la tabla dinámica. El resultado, en
nuestro ejemplo, es como el de la
ilustración siguiente:
Para crear una tabla dinámica a
partir de información derivada de
Si escogemos la opción
varias tablas, escogeremos en el
Crear un solo campo de
menú Datos la opción Asistente
página Excel creará una
para tablas dinámicas. En el
tabla dinámica que mostrará
primer paso del asistente
una página de datos para
seleccionaremos el tipo de tabla
cada una de nuestras
dinámica que vamos a crear. En
tablas, o una página que
este caso se trata de una tabla
agrupe todos los rangos de
basada en rangos de
7 Tablas dinámicas basadas en rangos de
consolidación múltiples
consolidación múltiples. A los datos de origen.
continuación seguiremos estas
instrucciones: En cambio, si optamos por
Personalizar campos de
En el segundo paso del asistente página podremos usar el
se nos permite entre crear un asistente para asignar
solo campo de página o diversos rangos a nombres
personalizar los campos de de elementos en campos
página. con un máximo de cuatro
páginas. Es una opción útil,
por ejemplo, cuando
trabajamos con una tabla
dinámica que resume
información proveniente de
muchas tablas.

12 – Nivel Avanzado Excel

 T E M A 2

Herramientas de
Análisis de la Hoja de Cálculo
1 Las Auditorias

En el tercer paso del asistente pulsaremos el botón Asistente

deberemos distribuir, como para gráficos. A partir de ahí,
vimos antes, las filas y las el proceso será el tradicional.
columnas en la tabla dinámica.
En este caso es un trabajo que Nada que ver con el concepto
Excel ha hecho en gran económico de auditoría, aunque,
medida. En todo caso, remotamente, su origen pueda ser
podremos modificar la similar. Partimos de una hoja de
distribución propuesta por el cálculo ya creada y en la que existen
asistente. multitud de celdas que realizan
operaciones matemáticas entre sí, se
relacionan, se vinculan, etc. llega un
Finalmente seleccionaremos la momento en que la telaraña puede
situación de la tabla dinámica. tener un tamaño considerable, hasta el
En nuestro caso, su aspecto es extremo de no saber de que datos
el de la ilustración. En este tipo procede el resultado de una
de tabla dinámica determinada fórmula.
dispondremos de un menú
Mediante estas auditorías la hoja de
desplegable, en nuestro caso, cálculo nos mostrará, de una forma
rotulado como Resumen, desde sencilla y muy gráfica, como se
el que podremos acceder a la relacionan las distintas celdas entre sí,
información de cualquiera de como son recalculadas y como afecta
las cuatro tablas dinámicas la modificación de un dato en otras
originales o ver todos los datos celdas. También podremos en
algunos casos rastrear el origen de un
de estas agrupados. error.

Veamos un ejemplo práctico. Partimos

de esta hoja de cálculo.

Trabajando con cualquier tipo de

tabla dinámica, es posible crear
gráficos basados en éstas, de En ella tenemos una columna de cifras
manera que al actualizar la (de la C4 a la C8) que son sumadas en
información de la tabla dinámica, la celda C 1 0. El resultado de esta
el gráfico también se modificará suma es dividido en la celda E10 por
mostrando la nueva información. cinco. En la hoja de cálculo no
veremos, como en nuestro ejemplo, la
Será, en cierto modo, un gráfico fórmula en las casillas, sino sólo el
dinámico. resultado de la operación.

Para crear un gráfico basado en Gracias a la auditoría podremos ver de

una tabla dinámica simplemente forma gráfica de donde son obtenidos
deberemos seleccionarla y los resultados, o como afecta un

13 – Nivel Avanzado Excel

determinado dato en la hoja de cálculo. proceden los datos que operan en
Vayamos por partes. la fórmula seleccionada. Esta
opción de menú, por tanto, sólo
En el menú Herramientas encontramos será operativa en celdas que
la opción Auditoría. Si pulsamos sobre contengan fórmulas, o como
ella se despliega otro pequeño menú con mínimo, referencias a otras celdas.
todas las opciones disponibles, parecido
a éste: Rastrear Dependientes. La
segunda opción de auditoria es
exactamente lo contrario: situados
sobre una celda que contenga un
valor numérico el ordenador nos
muestra a qué otras celdas afecta
su existencia.

En nuestro ejemplo, si nos

situamos sobre la celda C4, que
contiene sola y exclusivamente un
número, y seleccionamos esta
opción de menú, nuestra hoja de
cálculo pasará a tener este
aspecto:
Estudiemos cada una de las diferentes
opciones:

Rastrear precedentes. Situado el

cursor sobre una celda que contenga
una fórmula (en nuestro ejemplo,
C10, que contiene la suma de la
columna que tiene sobre ella), si
seleccionamos esta opción de menú
Sin mover el cursor de la celda C4,
en pantalla nos aparecerá estos
si volvemos a efectuar la operación
extraños dibujos:
de rastrear dependientes
(recordemos, menú Herramientas,
opción Auditoría), aparecerá otra
flecha más, que ahora apuntará a
la celda E10.

Surge '”de la nada” una flecha que

apunta hacia la celda donde nos
hemos situado. En este ejemplo es
bastante evidente de donde proceden Esto es por que ese dato, además
los datos de la fórmula, pero en otros de ser sumado en la celda C10
casos la situación puede ser mucho afecta en cierto modo a la E10,
más compleja, y esta operación, de donde se divide el resultado de la
gran utilidad. suma por 5, de tal manera que si
variásemos el contenido de C4
variarían C10 y E10.
Si hacemos lo propio en la celda E10
comprobaremos que ese resultado se
deriva, a su vez, de la celda C10. Rastrear Errores. La tercera opción
Cada vez que pulsemos el menú la del submenú Auditoría es esta.
auditoria se retrocederá un paso en Nos permite, en determinadas
las operaciones previas. ocasiones verificar de que celda
proviene el error que impide el
cálculo adecuado de las diferentes
Como podemos ver, rastrear
celdas. En este caso la
precedentes muestra de donde

14 – Nivel Avanzado Excel

 procedencia del error quedará Mostrar barra de auditoría.
señalada por una flecha de color rojo. Aparecerá en pantalla una
pequeña barra de herramientas
Vamos a realizar unas pequeñas donde están reflejadas de forma
modificaciones en nuestro ejemplo: gráfica, y en sus correspondientes
en la celda E10 sustituiremos la botones, las diferentes funciones
división que había antes esta: de la auditoría.

=C10/0

Un número dividido por cero es igual

a infinito, lo cual para Excel es
incalculable. En la celda E12 Surgen dos botones que no habíamos
escribiremos estudiado todavía: uno nos permitirá
añadir nuestras notas aclaratorias
sobre el funcionamiento de nuestra
=E10+2
hoja de cálculo y el otro visualizarlo.

En este caso los mensajes de error

provocados son muy evidentes, pero
en otras ocasiones con las que nos
enfrentaremos cuando trabajemos
con Excel no lo serán tanto. Tenidas
en cuenta estas modificaciones,
nuestra hoja de cálculo tendrá este
aspecto.

Situados sobre la celda E12

seleccionamos la opción de menú
Rastrear Error dentro de la
Auditoría. La apariencia de la
pantalla será esta:

Eliminar todas las flechas. A medida

que ejecutamos las diferentes
opciones de la auditoría la pantalla se
va cubriendo de flechas. Aquí se nos
ofrece la posibilidad de limpiarla para
poder seguir trabajando con
comodidad.

15 – Nivel Avanzado Excel

T E M A 3

Resolver problemas matemáticos

1 Introducción
Veremos
dos
Con estas funciones
potentes básicas.
utilidades En la
incorporada función
s a Excel Buscar
podremos Objetivo
resolver resolverem
ecuaciones os
matemática ecuaciones
s y del tipo:
optimizar el
resultado
de los datos
introducidos
en nuestra donde "X"
hoja de será el
cálculo. resultado
que el
Partimos, ordenador
por tanto, deberá
de calcular.
situaciones Como
en las que, siempre, en
dados una el caso de
serie de la ecuación
elementos, que
el acabamos
ordenador de ver
deberá encontrar
calcular un
otros resultado
tantos, es muy
teniendo en fácil, pero
cuenta una en los
determinad supuestos
as que nos
condiciones plantee
que nuestro
establecere trabajo
mos cotidiano
previament las
e. ecuaciones
pueden ser
mucho más de
complejos. establecer
condiciones
La otra a la hora de
aplicación resolver
que nuestros
veremos problemas
será el (por
Solver. Se ejemplo,
trata de una que 'X' sea
variación un número
del Buscar entre el 1 y
Objetivo, el 3)
con la
posibilidad
(la 'X' en la
ecuación) y
A3 es la
2 fórmula que
Buscar vamos a
aplicar (y
Objetivo que debe
dar en
Partiremos, nuestro
para el ejemplo
análisis de 567).
esta
herramienta En el menú
de un Herramient
ejemplo as
muy seleccionar
sencillo: emos la
resolveremo opción
s la Buscar
ecuación Objetivo.
23*X=567. Se abrirá
Para ello, una
cumpliment pequeña
aremos ventana
nuestra hoja parecida a
de cálculo ésta
de la
siguiente
Cumpliment
forma:
aremos las
tres casillas
que la
componen
de la
siguiente
forma:
Donde, A1
es el primer En la
elemento de casilla
la “Definir
multiplicació la
n, A2 es el celda"
que vamos tecleare
a calcular mos la
 referenc abrir
ia de la esta
celda opción
que de
vamos a menú
modifica aparece
r (en rá
nuestro directam
caso, la ente
de la cumplim
multiplic entada.
ación En esta
que casilla,
cuyo en
resultad resume
o n,
deseam siempre
os que escribire
sea 567, mos el
la A3). nombre
Si de la
hemos celda
dejado que
el cursor contiene
sobre la
esta fórmula.
celda
antes de
En la casilla “con el valor"
teclea-
el botón
remos el resultado que debe
apare- Aceptar.
cer en la celda antes reseñada
(en
nuestro caso, 567). Tras
unas breves operaciones, el
orde nador mostrará la solución
en la celda
Finalmente, en la casilla “para
seleccionada (A2) y un
mensaje inforcambiar la
celda" mencionaremos
mativo del resultado de
la operación. la celda que
habrá de ser modifica-
da para conseguir nuestro
resulta- Si pulsamos el botón
Aceptar la modifido. En otras
palabras, aquí Indica- cación se
aplicará definitivamente a remos
cual es la Incógnita de nues-
nuestra hoja de cálculo. Si no
deseatra ecuación. En nuestro
ejemplo mos que es realice esa
operación pulsaesta celda será
la A2, que se encon- remos el
botón Cancelar, volviendo a la
traba en blanco. situación
anterior de ejecutar esta fun
ción.

3 Solver

Ante todo, la herramienta tado también estará presente la op-

conocida como Solver se ciónsolver
. Activaremos la casilla
encuentra en el menú correspondiente y pulsaremos el bo-
Herramientas. Si no la tónaceptar.
encuentras tal vez sea por que
no ha sido instalada en tu
equipo (se instala de forma
independiente al resto de la
aplicación).

Agregar esta opción al menú es

muy sencillo. Sigue estos
pasos:

En primer lugar, en el menú

Herramientas escoge la
opción Complementos. Posiblemente sea neceario el cd
original de instalación del Office 97
Se desplegará una lista con
los
accesorios disponibles, pero
no ins- talados (el
autoguardado, asistente
para informes, el asistente
para la suma condicional,
etc). En ese lis-
o 2000 que
(depend trabajes
iendo ).
de la
versión
con la
 El Solver se
utiliza para
El determinar
sistema el valor
copiará máximo o
los mínimo de
archivos una celda
necesari modificando
os otras
desde el celdas; por
cd-rom ejemplo, el
en unos beneficio
pocos máximo que
segundo puede
s, y generarse
complet modificando
ada la los gastos
operació de
n, publicidad.
volvere Las celdas
mos a la que se
hoja de seleccionen
cálculo deberán
tal y estar
como la relacionada
habíam s mediante
os fórmulas en
dejado. la hoja de
cálculo. Si
no están
relacionada
s, cuando
se
modifique
una celda
no se
A partir modificará
de la otra.
ahora,
tendrem
Con Solver,
os
puede
disponib
buscarse el
le esta
valor óptimo
herrami
para una
enta en
celda,
ese
denominada
menú.
celda
Si
objetivo, en
quieres
una hoja de
desinsta
cálculo.
larla,
Funciona en
simplem
un grupo de
ente
celdas que
desactiv
estén
ala en
relacionada
Herrami
s, directa o
entas
indirectame
comple
nte, con la
mentos.
fórmula de
la celda Celda
objetivo. objetivo
Solver : Celda
ajusta los que se
valores en desea
las celdas definir
cambiantes con un
que se valor
especifique determi
n, nado o
denominada que se
s celdas desea
ajustables, maximiz
para ar o
generar el minimiz
resultado ar.
especificad
o en la Valor de
fórmula de la celda
la celda objetivo
objetivo. :
Pueden Especifi
aplicarse ca si se
restriccione desea
s para maximiz
restringir los ar o
valores que minimiz
puede ar la
utilizar celda
Solver en el objetivo,
modelo y o bien
las definirla
restriccione con un
s pueden valor
hacer específi
referencia a co el
otras celdas cual se
a las que introduci
afecte la rá en el
fórmula de cuadro.
la celda
objetivo. Cambian
do las
Tras activar celdas:
esta opción Celdas
en el menú que
Herramienta pueden
s, veremos ajustars
una pantalla e hasta
similar a la que se
de la satisfag
ilustración an las
restricci
ones del
Las problem
diferentes a,
opciones pueden
contenidas especifi
son: carse
200 Resolver
celdas : Inicia
como el
máximo. proceso
de
Estimar: solución
Estima del
todas problem
las a
celdas definido.
que no
contiene Cerrar:
n Cierra el
ninguna cuadro
fórmula de
a las diálogo
que se sin
hace resolver
referenc el
ia en la problem
fórmula a.
de la Retiene
celda todos
objetivo los
y cambios
escribié que se
ndola en hayan
el realizad
cuadro o
Cambia mediant
ndo las e los
celdas. botones
Opcione
Sujeto a s,
las Agregar,
siguient Cambiar
es o
restricc Borrar.
iones:
Muestra Opcione
una lista s:
de las Muestra
restricci el
ones cuadro
actuales de
en el diálogo
problem Opcione
a, s de
permitié Solver,
ndose donde
editarlas pueden
. cargars
e y
guardar
se los
modelos
de
problem
a y las
 caracter Cada
ísticas opción tiene
de una
control configuració
avanzad n
o del predetermin
proceso ada
de adecuada a
solución la mayoría
. de los
problemas.
Podemos
Restabl describirlas
ecer brevemente
todo: de la
Borra siguiente
los forma:
valores
actuales Tiempo
del máximo
problem : Limita
a y el
restable tiempo
ce todos que
los tarda el
valores proceso
a sus de
valores solución
originale . Puede
s. introduci
rse un
También valor tan
pueden grande
controlarse como 32
las 367,
característic pero el
as valor
avanzadas predeter
del proceso minado
de solución, 100
cargarse o (segund
guardarse os) es
definiciones adecuad
de o para la
problemas y mayor
definirse parte de
parámetros los
para los problem
problemas as.
lineales y
no lineales,
simplement Iteraciones
e pulsando : Limita el
el botón tiempo que
Opciones tarda el
situado a la proceso
derecha de de
esta solución
ventana ,
limitand inferior o
o el superior.
número Debe
de indicars
cálculos e la
provisio precisió
nales. n
Aunque mediant
puede e una
introduci fracción
rse un entre 0 y
valor 1.
tan Cuantos
grande más
como decimal
32.767, es tenga
el valor el
predeter número
minado que se
100 es introduz
adecua ca,
do para mayor
la será la
mayor precisió
parte de n; por
los ejemplo,
problem 0,0001
as indica
pequeñ una
os. precisió
n mayor
que
Precisió 0,01.
n: Cuanto
Controla mayor
la sea la
precisió precisió
n de las n, más
solucion tiempo
es se
utilizand tardará
o el en
número encontra
que se r una
introduc solución
e para .
averigua
r si el
valor de Tolerancia:
una El
restricci porcentaje
ón mediante
cumple el cual
un la celda
objetivo objetivo
o de una
satisfac solución
e un que
límite satisfag
 a las La
restricci convergen
ones cia se
externa aplica
s puede únicament
diferir e a los
del valor problemas
óptimo no
verdade lineales y
ro y debe
todavía indicarse
consider mediante
arse una
aceptabl fracción
e. Esta entre 0 y
opción 1.
sólo se Cuantos
aplica a más
los decimales
problem tenga el
as que número
tengan que se
restricci introduzca
ones , menor
enteras. será la
Una convergen
toleranci cia; por
a mayor ejemplo,
tiende a 0,0001
acelerar indica un
el cambio
proceso relativo
de menor
solución que 0,01.
. Cuanto
menor
Convergen sea el
cia: Si el valor de
valor del convergen
cam- cia, más
bio tiempo se
relativo en tardará en
la celda encontrar
objetivo una
es menor solución.
que el
número Adoptar
introducid modelo
o en el lineal:
cuadro Selección
Converge alo para
ncia para acelerar el
las proceso
últimas de
cinco solución
iteracione cuando
s, Solver todas las
se relaciones
detendrá. en el
modelo millón de
sean dólares.
lineales y
desee Adoptar
resolver no-
un negativo:
problema Supone
de un límite
optimizaci de cero
ón o una para
aproximac todas las
ión lineal celdas
a un ajustables
problema en las que
no lineal. no se
haya
Mostrar definido
resultado un límite
de inferior en
iteracion el cuadro
es: restricción
Muestra .
los
resultados Estimació
de cada n:
iteración. Especifica
el enfoque
Usar que se
escala utiliza
automáti para
ca: obtener
Selección las
alo para estimacio
utilizar la nes
escala iniciales
automátic de las
a cuando variables
haya básicas
grandes en cada
diferencia una de las
s de búsqueda
magnitud s
entre las dimension
entradas y ales.
los • Tan
resultados gen
; por te,
ejemplo, utiliz
cuando se a la
maximiza extr
el apol
porcentaje ació
de n
beneficios line
basándos al
e en una de
inversión un
de medio vect
or
 tang • Pro
ente gre
. siva
s,
• Cua se
drát utiliz
ica, an
utiliz en
a la la
extr may
apol or
ació part
n e de
cua los
dráti prob
ca, lem
que as,
pue en
de que
mej los
orar valo
los res
resu de
ltad restr
os icció
de n
prob cam
lem bien
as relat
no iva
line men
ales te
en poc
gran o.
med
ida. • Cen
tral
es,
Derivadas: se
Especifica utiliz
la diferencia a en
que se los
utiliza prob
para lem
estimar as
las en
derivad que
as las
parciale restr
s del iccio
objetivo nes
y las cam
funcione bian
s de la rápi
restricci dam
ón. ente
,
esp
ecial
 men n en que
te se hace
cerc la
a de búsqued
los a.
límit
es. • New
Aun ton,
que es
esta un
opci mét
ón odo
nec casi
esit New
a ton,
más nor
cálc mal
ulos men
, te
pue nec
de esit
ser a
útil más
cua me
ndo mori
Solv a
er pero
dev men
uelv os
e un itera
men cion
saje es
dicie que
ndo el
que mét
no odo
pue de
de grad
mej ient
orar e
se conj
la uga
solu da.
ción
. • Con
juga
Hallar da,
por: nec
Especifi esit
ca el a
algoritm men
o que se os
utiliza me
en cada mori
iteración a
para que
determin el
ar la mét
direcció odo
New és
ton, de
pero itera
nor cion
mal es
men se
te des
nec cubr
esit a un
a prog
más reso
itera lent
cion o.
es
para Cargar
alca modelo:
nzar Especifi
un ca la
dete referenci
rmin a del
ado modelo
nivel que se
de desee
prec cargar.
isión
. Guardar
Esta modelo:
opci Muestra
ón el
se cuadro
usa de
cua diálogo
ndo Guardar
se modelo,
trate donde
de puede
un especific
prob ar la
lem ubica-
a ción en
gran que
de y desee
la guardar
utiliz el
ació modelo,
n de se usa
me únicame
mori nte
a o cuando
cua se
ndo guardar
al más de
hac un
er modelo
un con una
reco hoja de
rrido cálculo,
a el
trav primer
 modelo originale
se s en las
guarda celdas
de ajustabl
forma es.
automát
ica. Informes
:
Una vez Genera
hemos el tipo
ejecutado de
Solver, se informe
muestra un que se
mensaje de especifi
finalización que y lo
y los coloca
valores en una
resultantes hoja
más indepen
próximos a diente
la solución en la
que se hoja de
desee: cálculo.

• Res
pue
sta,
mue
stra
una
lista
con
Conserv la
ar la celd
solució a
n de obje
Solver: tivo
Aceptar y las
la celd
solución as
y ajus
colocar tabl
los es
valores con
resultant sus
es en valo
las res
celdas origi
ajustabl nale
es. s y
sus
Restaurar valo
valores res
originales final
: Res- es,
taurar las
los restr
valores iccio
 nes del
y la cua
infor dro
mac de
ión diál
acer ogo
ca Par
de áme
las tros
mis de
mas Solv
. er o
de
• Sen las
sibil restr
idad iccio
, nes.
facili No
ta se
infor gen
mac era
ión este
acer infor
ca me
de para
la los
sen mod
sibili elos
dad que
de teng
la an
solu restr
ción iccio
a nes
que ente
se ras.
reali En
cen mod
peq elos
ueñ no
os line
cam ales
bios , el
en infor
la me
fórm facili
ula ta
defi los
nida valo
en res
el para
cua las
dro grad
Defi ient
nir es y
celd los
a mult
obje iplic
tivo ador
 es lista
de con
Lagr la
ang celd
e. a
En obje
los tivo
mod y las
elos celd
line as
ales ajus
, el tabl
infor es
me con
inclu sus
ye valo
cost res
os corr
redu esp
cido ondi
s, ente
otro s,
s los
prec límit
ios, es
coef infer
icien ior y
te sup
de erior
obje así
tivos com
(con o
aum los
ento valo
s y res
dis del
min obje
ucio tivo.
nes No
per se
miti gen
dos) era
y este
rang infor
os me
de para
restr los
iccio mod
nes elos
haci que
a la teng
dere an
cha. restr
iccio
• Lími nes
tes, ente
mue ras.
stra El
una límit
e Guardar
infer escenar
ior io: Abre
es el
el cuadro
valo de
r diálogo
míni Guardar
mo escenari
que o, donde
pue pueden
de guardar
tom se los
ar la valores
celd de celda
a para
ajus utilizarlo
tabl s en el
e Administ
mie rador de
ntra escenari
s se os de
man Microsof
tien t Excel.
en
toda Son muchos
s las los
dem mensajes
ás que pueden
celd aparecer en
as este último
ajus paso del
tabl proceso, y
es algunos no
fijas resultan
y se demasiado
cont claros.
inúa Veamos
satis cuales son
faci las
end diferentes
o posibilidade
las s:
restr
iccio Cuando
nes. Solver
El encuentra
límit una
e solución,
sup muestra
erior uno de los
es siguientes
el mensajes
valo en el cuadro
r de diálogo
máx Resultados
imo. de Solver:
Solver ha en la
encontr celda
ado una objetivo
solució es
n. Se menor
han que el
satisfec valor de
ho Converg
todas encia en
las el
restricci cuadro
ones y de
condici diálogo
ones. Opcione
Se han s de
satisfec Solver.
ho todas Si se
las introduc
restricci e un
ones valor
dentro menor
de los que el
valores valor de
de Converg
precisió encia,
n en el Solver
cuadro puede
de buscar
diálogo una
Opcione solución
s de mejor
Solver y pero
se ha tardará
encontra más
do un tiempo
valor en
máximo encontra
o rla.
mínimo
local
para la
celda
objetivo.

Solver ha
llegado
a la
solució
n
actual.
Todas
las
restricci
ones se
han
satisfec
ho. El
cambio
relativo
Si encuentra
Solver una
no solución
óptima,
mostrará
uno de los
siguientes
mensajes
en el cuadro
de diálogo
Resultados
de Solver.

Solver no
puede
mejorar
la
solución
actual.
Todas las
restriccio
nes se
han
satisfech
o.
Solament
e se ha
encontrad
o una
solución
aproximad
a, pero el
proceso
iterativo
no puede
encontrar
un
conjunto
de valores
mejor que
los que se
presentan
. No
puede
alcanzars
e mayor
precisión
o el valor
de
precisión
es
demasiad
o bajo.
Cambie el
valor de
precisión
a un
número
mayor en
 el cuadro se ha
de diálogo seleccion
Opciones ado
de Solver Detener.
y ejecute Se ha
otra vez el alcanzado
programa. el número
máximo
Cuando se de
ha iteracione
alcanzad s sin que
o el límite se haya
de encontrad
tiempo, o una
se ha solución
seleccion satisfactor
ado ia. Puede
Detener. ser útil
Ha aumentar
transcurri el número
do el de
tiempo iteracione
máximo s, pero
sin que se deberán
haya examinars
encontrad e los
o una valores
solución finales
satisfactor para
ia. Para investigar
guardar el
los problema.
valores Para
encontrad guardar
os hasta los
este valores
momento encontrad
y guardar os hasta
el tiempo este
de un momento
nuevo y guardar
cálculo en el tiempo
el futuro, de un
haga clic nuevo
en cálculo en
Conservar el futuro,
la haga clic
solución en
de Solver Conservar
o Guardar la
escenario. solución
de Solver
Cuando se o Guardar
ha escenario.
alcanzad
o el límite Los
máximo valores
de de la
iteración, celda
objetivo prueba
no que
converge satisfag
n. El a todas
valor de la las
celda restricci
objetivo ones
aumenta dentro
(o de los
disminuye valores
) sin de
límites, precisió
aunque se n. Es
hayan probable
satisfecho que las
todas las restricci
restriccion ones no
es. Puede sean
haberse coherent
omitido es.
una o Examine
varias la hoja
restriccion de
es al cálculo
definir el por si
problema. hubiera
Comprueb algún
e los error en
valores las
actuales fórmulas
de la hoja de
de cálculo restricci
para ver ón o en
la la
divergenci opción
a en la de las
solución, restricci
comprueb ones.
e las
restriccion Se ha
es y detenid
ejecute o
otra vez el Solver a
programa. petición
Solver del
no ha usuario.
podido Se ha
encontr hecho
ar una clic en
solució Detener
n en el
factible. cuadro
Solver de
no ha diálogo
podido Mostrar
encontra solución
r una tentativa
solución , tras la
de interrupc
ión del real.
proceso Para
de comprob
solución ar si se
, o trata de
cuando un
se hacía problem
un a no
recorrid lineal,
o a active la
través casilla
de de
solucion verificaci
es ón Usar
tentativa escala
s. automáti
ca y
No se ejecute
han otra vez
satisfec el
ho las program
condici a. Si
ones aparece
para de
Adoptar nuevo
modelo este
lineal. mensaje
Se ha ,
activado desactiv
la casilla e la
de casilla
verificaci de
ón verificaci
Adoptar ón
modelo Adoptar
lineal, modelo
pero los lineal y
cálculos ejecute
finales otra vez
en los el
valores program
máximo a.
s de
Solver Solver ha
no están encontr
de ado un
acuerdo valor de
con el error en
modelo una
lineal. celda
La objetivo
solución o
no es restring
válida ida.
para las Una o
fórmulas varias
de la fórmulas
hoja de ha
cálculo producid
o un entero,
valor de haga
error en clic en
el último Ent en
cálculo. la lista
Busque de los
la celda operado
objetivo res de
o la compara
celda ción.
restringi Para
da que definir
contiene una
el error restricci
y ón
cambie binaria,
la haga
fórmula clic en
para Bin.
que Memoria
produzc disponi
a un ble
valor insufici
numéric ente
o para
adecuad resolve
o. r el
proble
Se ha ma.
introduci Microsof
do un t Excel
nombre no ha
o una podido
fórmula asignar
incorrect la
os en el memori
cuadro a
de necesari
diálogo a para
Agregar Solver.
restricci Cierre
ón o algunos
Cambiar archivos
restricci o
ón, o program
bien se as e
ha inténtelo
introduci otra vez.
do
"entero" Otro
o element
"binario" o de
en el Excel
cuadro está
Restricci utilizan
ón. Para do
restringir SOLVE
un valor [Link].
a un Se está
 ejecutan óptima de
do más productos
de una que se van
sesión a fabricar.
de Pero la
Microsof ganancia
t Excel y por unidad
otra disminuye
sesión al aumentar
ya está el volumen
utilizand fabricado
o puesto que
Solver.d se
ll. necesitan
Solame más
nte incentivos
puede de precio
utilizars para
e en producir un
una incremento
sesión en la
cada demanda.
vez.
El problema
Veamos un consiste en
caso determinar
práctico el número
donde se de cada
apliquen producto del
todos estos inventario
conocimient disponible
os: que se
utilizara
Se fabrican para
televisores, construir los
estéreos y componente
bocinas, s,
usando maximizand
piezas en o así los
común tales beneficios.
como
generadore Especificaci
s de ones del
electricidad problema:
y conos de
altavoces, Celda
cada una Objetivo:
con un D11 cuya
margen de fórmula es
beneficio SUM(D10:F
diferente 10), el
por unidad. objetivo es
Debido a maximizar
que las el beneficio
piezas son
limitadas, Celdas a
se debe cambiar:
determinar D2:F2,
la mezcla
unidades de total de
cada estéreos *
producto número de
que se van bastidores
a construir. que
necesita el
Restriccion estéreo +
es total de
altavoces *
C4:C8 < número de
= bastidores
B4:B8, que
el necesita los
número altavoces.
de
piezas Las
utilizada fórmulas de
s debe beneficio
ser por
menor o producto en
igual al las celdas
número D10:F10
de incluyen el
piezas factor ^H8
del para
inventari mostrar que
o. el beneficio
por unidad
D2:F2 > disminuye
=0 El con el
número volumen.
del valor H8 contiene
a 0,9, lo que
construir hace que el
debe problema
ser sea no
mayor o lineal, para
igual a las
0. diferentes
La columna piezas la
C tiene la formula es:
cantidad de
piezas Tele
usadas, por viso
ejemplo res
para C4 la 75 *
cantidad de MA
bastidores, X(D
la formula 2,0)
sería igual a ^H8
D2*D4+E2* Esté
E4+F2*F4, reos
total de 50 *
televisores * MA
bastidores X(D
que 2,0)
necesita el ^H8
televisor + Alta
voc
 es
35 *
MA
X(D
2,0)
^H8

Los valores
antes de
ejecutar
solver eran
los
siguientes:
Después de ejecutar solver siguiendo las premisas comentadas la hoja de cálculo quedó
de esta forma:

Si cambia H8 a 1,0 para indicar que el beneficio por unidad permanece constante con
relación al volumen, el problema será lineal y los resultando variarán.