En esta sesión vamos a ver las funciones condicionales y lógicas y una de las funciones de búsqueda. Estas funciones son muy útiles para diseñar  hojas de cálculo con cierto nivel de complejidad. Además vamos a ver una serie de herramientas que sirven para facilitar ciertas tareas y para realizar simulaciones, optimización de valores y análisis de datos.

Las herramientas se presentan en su utilización más básica, quedando otras posibilidades de utilización fuera del alcance de este curso.
Las explicaciones se acompañan con ejemplos con información suficiente para reproducir el ejemplo con una hoja de cálculo (Excel).

Funciones lógicas y condicionales
El uso de funciones condicionales en modelos de hojas de cálculo permite dotar a éstos de una gran flexibilidad, ya que van a posibilitar que una celda o rango adopten distintos valores en función de cuáles sean los valores de otras celdas (sea de la misma hoja, de otras hojas del mismo libro o, incluso, de libros diferentes). En su formato más simple, la sintaxis de la función lcondicional es:

=SI (condición; acción l; acción 2)

Así, el valor que se le asignará a la celda que contenga esta fórmula será el que se derive de la acción 1 si la condición es cierta y el de la acción 2 en caso de que la condición sea falsa. La condición puede ser más o menos simple; algo similar ocurre con las acciones. Analicémoslo más detenidamente.

Condiciones
Para construir las condiciones se usan los operadores lógicos:
mayor     mayor o igual     igual     menor o igual     menor     distinto
    >                 >=               =                 <=                    <            <>

Excel evalúa la condición y devuelve VERDADERO si la condición se cumple o FALSO en caso contrario (puede comprobarlo escribiendo en cualquier celda la expresión =5>4, por ejemplo). Podemos usar números, rótulos o fórmulas para construir condiciones; todo dependerá del problema concreto que nos estemos planteando. A continuación mostramos algunos ejemplos:

A3<7         (1000/B6)*SUMA(A1:B4)<>F5+F8               G6="Contado"

Además, podemos diseñar una condición que sea la unión de varias condiciones simultáneamente; para ello, usamos las funciones lógicas Y y O

función  lógica Y
=SI(Y(subcondición l; subcondición 2; ... ; subcondición 30); acción l; acción 2)

Cada subcondición puede ser verdadera o falsa; para que el resultado conjunto de la condición Y sea verdadero, todas las subcondiciones han de ser verdaderas; dicho de otro modo, basta con que una de las subcondiciones sea falsa para que el resultado global sea falso, tal como se muestra en la tabla de verdad.

cond1 cond2     Y

F         F          F

F         V          F

V         F          F

V         V          V
tabla de verdad de la función lógica Y

Si el resultado global es falso, se ejecuta la acción 2 y la acción 1 en caso contrario.
 

función  lógica O
=SI(O(subcondición l; subcondición 2; ... ; subcondición 30); acción l; acción 2)

En esta ocasión, la evaluación de la condición difiere: para que el resultado conjunto sea verdadero, al menos una de las subcondiciones ha de serlo, ejecutándose la acción l. Sólo en el caso de que todas las subcondiciones sean falsas, el resultado conjunto lo será y se ejecutará la acción 2.

cond1 cond2     O

F         F          F

F         V          V

V         F          V

V         V          V
tabla de verdad de la función lógica O
 

Existe una tercera función lógica denominada NO cuya misión es alterar el sentido de la condición o subcondición: NO(condición) devuelve verdadero si la condición es falsa y viceversa.

cond         NO
F               V

V               F
tabla de verdad de la función lógica NO

Como puede observarse, esta función es  perfectamente eludible, ya que podemos hacer lo mismo mediante el uso correcto de los operadores lógicos, como vemos en el ejemplo siguiente:

NO(A 1 >B 1) es igual que A 1 <=B1         NO(A 1 =B 1) es igual que A 1 <>B 1
 

Acciones
Las acciones son las que van a asignar los valores a las celdas en donde se encuentren escritas las funciones lógicas. Esta asignación puede ser muy simple (haz que la celda tome el valor 100), o muy compleja, mediante el uso de funciones que pueden, a su vez, incluir funciones condicionales: de esta manera, podemos anidar (es decir, usar una función condicional como argumento de otra función condicional) hasta siete funciones condicionales, de forma que la flexibilidad a la hora de resolver los modelos de hoja de cálculo es muy amplia.

Estructura condicional simple

=SI (condición; acción 1; acción2)
                                ^            ^
            V                  |             |
               ¯¯¯¯¯¯¯¯               |
            F                                |
                ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
En  caso de que la condición sea verdadera se ejecutará la acción1 y la acción2 en caso contrario.

Estructura condicional compleja
=SI (condición1; SI(condición2;acción1;acción2); acción 3 )
                                                       ^            ^                  ^
             V                    |V                 |             |                   |
               ¯¯¯¯¯¯¯¯¯¯ |    ¯¯¯¯¯¯¯¯             |                   |
                                    |F                               |                   |
                                        ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯                   |
             F                                                                          |
                ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
Imaginemos que la acción1 de una condicional es, a su vez, otra condicional. Se trata, en otras palabras, de una anidación de dos condicionales: en caso de que la condición1 sea cierta tendremos que ver  qué ocurre con la condición2; si ésta es verdadera se ejecuta la acción1 y la acción2 si fuera falsa. La acción3 se ejecutará sólo cuando la condición1 haya resultado ser falsa; cada una de estas acciones podrían, a su vez, ser otras funciones condicionales, de manera que podemos anidar, como anteriormente comentamos, hasta siete de estas funciones.

Para construir funciones lógicas correctas es preciso dominar la sintaxis de las mismas y ser lógicos en el diseño; es muy común que se cometan fallos como los indicados a continuación:

=SI(C7>C8; 1;SI(C7<=C8;0;2))

En esta función lógica comparamos el contenido de las celdas C7 y C8, de manera que, en el caso de que C7 sea mayor que C8 se asigne el valor l; en caso contrario, comparamos si C7 es menor o igual que C8: podemos observar como esta última comparación carece de lógica, ya que siempre se cumplirá. En otras palabras, nunca se asignará al valor 2 en la condicional anterior.

=SI(A1>B1;"Al es mayor que B1";SI(Al=B1;"A1 es igual que B1";SI(A1<B1;"Al es menor que B1 ";"¿Y aquí qué pongo?"))

En esta función sobra la última condicional ya que siempre se cumplirá: en el caso de que las dos primeras sean falsas, por tanto Al no sea ni mayor ni igual que Bl,  la tercera condición (A1 < B1) siempre será cierta (es decir, Al será menor que B1). La construcción correcta será:

=SI(A 1 >B 1;"A 1 es mayor que B1 ";SI(A1 =B 1;"A1 es igual que B1 "; "Al es menor que B1"))
 
 

Función BuscarV
Busca un valor específico en la columna más a la izquierda de una matriz y devuelve el valor de la celda cuya fila sea la fila donde encuentre el valor y cuya columna sea aquella de la matriz que corresponda a un número especificado.
Esta función se utiliza normalmente para obtener un valor correspondiente a otro dado, p.e dado el código de un artículo obtener su precio, dado un número de cliente obtener su nombre.

Sintaxis·
BUSCARV(valor_buscado;matriz_de_comparación;indicador_columna;ordenado)
donde:

• Valor_buscado es el valor que se busca en la primera columna de la matriz. Valor_buscado puede ser un valor, una referencia o una cadena de texto.
• Matriz_de_comparación es el conjunto de información donde se buscan los datos. Se puede utilizar una referencia a un rango, como por ejemplo B3:H14 o Clientes!C10:E101, o un nombre de rango, como por ejemplo Base_de_datos o Lista. Si el argumento ordenado es VERDADERO, los valores de la primera columna del argumento matriz_de_comparación deben colocarse en orden ascendente.
• Indicador_columna es el número de columna de la matriz_de_comparación desde la cual debe devolverse el valor coincidente. Si el argumento indicador_columnas es igual a 1, la función devuelve el valor de la primera columna del argumento matriz_de_comparación; si el argumento indicador_columna es igual a 2, devuelve el valor de la segunda columna de la matriz_de_comparación y así sucesivamente.  Si indicador_columna es menor que 1, BUSCARV devuelve el valor de error #¡VALOR!; si indicador_columna es mayor que el número de columnas de matriz_de_comparación, BUSCARV devuelve el valor de error #¡REF!
• Ordenado Es un valor lógico que indica si desea que la función BUSCARV busque un valor igual o aproximado al valor especificado. Si el argumento ordenado es VERDADERO o se omite, la función devuelve un valor aproximado, es decir, si no encuentra un valor exacto, devolverá el valor inmediatamente menor que valor_buscado. Si ordenado es FALSO, BUSCARV devuelve el valor buscado. Si no encuentra ningún valor, devuelve el valor de error #N/A.

• Si BUSCARV no puede encontrar valor_buscado y ordenado es VERDADERO, utiliza el valor más grande que sea menor o igual al valor_buscado
• Si el valor_buscado es menor que el menor valor de la primera columna de matriz_de_comparación y ordenado es VERDADERO, BUSCARV devuelve el valor de error #N/A.
• Si BUSCARV no puede encontrar valor_buscado y ordenado es FALSO, devuelve el valor de error #N/A.

En la hoja de cálculo de la figura anterior, donde al rango A4:C12 se le da el nombre Rango. En este rango podremos encontrar dada una densidad:

• su  viscosidad ( para ello el indicador de columna debe ser 2)
• su temperatura (el indicador de columna debe ser el 3)

BUSCARV(1;Rango;2) es igual a 2,17

BUSCARV(1;Rango;3;VERDADERO) es igual a 100

BUSCARV(0,746;Rango;3;FALSO) es igual a 200

BUSCARV(0,1;Rango;2;VERDADERO) es igual a #N/A debido a que 0,1 es menor que el menor valor de la columna A

BUSCARV(2;Rango;2;VERDADERO) es igual a 1,71

de tal manera que, si por ejemplo, la celda E7 tiene el valor 0,835, la celda D7 el valor 160 y la celda E7, la fórmula
=SI(BUSCARV(E7;Rango;3;FALSO)<=D7;"La temperatura es igual o menor que "&D7;"La temperatura supera los "&D7)

el valor que aparecería en la celda E7 sería La temperatura es igual o menor que 160

Analizando la fórmula de la celda E7 vemos que es una función condicional donde el primer argumento de la condición es la función BUSCARV(E7;Rango;3;FALSO), que para los valores dados devuelve 150, que al ser menor que el contenidp de D7 (160) hace que la condición se evalue como verdadera, por lo que se ejecutará la acción1 ("La temperatura cae por debajo de "&D7); esta acción es la concatenación del texto La temperatura es igual o menor que  con el contenido de la celda D7, lo que hace que el resultado de la fórmula en E7 dé, en este caso, La temperatura es igual o menor que 160
 
 
 

Ordenar
Excel permite la ordenación de un rango de celdas por orden alfabético, numérico o temporal, de una forma rápida y sencilla, haciendo uso de la opción DATOS|ORDENAR (con esta simbología queremos decir que primero se pulsa la opción de la barra de menú DATOS y, a continuación, la opción ORDENAR). La ordenación consiste en cambiar los contenidos de unas celdas por el de otras, de forma que aparezca en la distintas columnas (o filas) ordenados. Para ello, en primer lugar, habrá que seleccionar el rango a ordenar; este rango puede incluir también otras celdas en la misma fila (o columna) que las que se vayan a ordenar, conservándose después de la ordenación la misma posición respecto a las celdas clave de la ordenación. Al pulsar sobre el submenú indicado aparece el cuadro Ordenar

En este cuadro se puede indicar por qué columna (o fila) se quiere realizar la ordenación en primer lugar (Ordenar por). En caso de que haya varias celdas con el mismo contenido, es posible ordenar las filas (o columnas) en función de los datos de una segunda o tercera columna (Luego por). Además, es posible indicar también si la ordenación será Ascendente (por orden alfabético, de menor a mayor, o de anterior a más reciente, según la ordenación sea alfabética, numérica o cronológica) o bien será Descendente. En el caso de que el rango tenga una columna (o fila) de títulos, que no se deba ordenar, deberá activarse la opción Sí en La lista tiene fila de encabezamiento. Si la ordenación no es en sentido vertical, sino horizontal, deberá señalarse desde el cuadro que aparece al pulsar sobre el botón Opciones, en el campo Orientación.

En este cuadro puede indicarse, también, si la ordenación tendrá en cuenta si las letras son mayúsculas o no; si se tiene en cuenta (marcando sobre la casilla Coincidir mayúsculas y minúsculas), en caso de dos textos iguales, aparecerá primero el que tenga las letras minúsculas. El campo Primer criterio de ordenación se utiliza cuando los datos no obedecen a un orden normal (números, fechas o textos), sino que son textos que representan los días de la semana o los meses del año.
 
 

Escenarios
Hasta el momento, cada celda de una hoja sólo podía tener asociado un valor (fuera un dato tecleado o el resultado de una fórmula). Gracias a los escenarios se va a conseguir que una misma celda tenga asociados distintos valores, uno por cada escenario, de manera que seleccionando un escenario hacemos que la celda tome el valor que está almacenado en el mismo.

Pensemos en una hoja de cálculo en la que se modela la previsión de las ventas de una empresa; imaginemos que el aumento de ventas depende del incremento del mercado, del crecimiento del Producto Interior Bruto y del comportamiento de las empresas competidoras. Estas tres variables se miden mediante unos índices situados resspectimanebte  en las celdas Al, A2 y A3. Concretamente, tal como se ve en la figura para el primer mes, la fórmula del cálculo de la previsión es:

La empresa se plantea tres posibilidades(escenarios): optimista (incremento de demanda del 10%, incremento del PIB del 4% e incremento de la competencia del 0%), neutra (incremento de demanda del 5%, incremento del PIB del 2% e incremento de la competencia del 2%), y pesimista (incremento de demanda del 2%, incremento del PIB del l% e incremento de la competencia del 8%). Si quisiéramos analizar la previsión de ventas optimista, introduciríamos la terna de valores en el rango A1:A3 y observaríamos los resultados; si deseáramos analizar la previsión pesimista, volveríamos a realizar la operación pero con los datos que correspondan a esa previsión, y así cada vez que deseáramos analizar la previsión de ventas de alguno de los tres posibles escenarios que nos hemos planteado.

Para facilitar este tipo de problemática y no tener que introducir los valores cada vez que se quiera ver cual es el comportamiento en cada situación, disponemos de los escenarios en Excel.

• En primer lugar, seleccionamos HERRAMIENTAS|ESCENARIOS. La ventana Administrador de escenarios aparecerá en la pantalla, indicando que no hay definido escenario alguno y que debemos pulsar sobre Agregar para crear nuestro primer escenario.

• En segundo lugar, indicar cuáles serán las celdas que contendrán los valores del escenario, en el cuadro Celdas cambiantes, que, en nuestro ejemplo se corresponden con $A$1, $A$2 y $A$3 (Excel escribe las direcciones con referencias absolutas, aunque ello carece totalmente de importancia, ya que no vamos a copiar las direcciones citadas). Por último, podremos escribir un comentario que documente el escenario, así como activar dos opciones: Evitar cambios y Ocultar.

Cuando pulsemos en el botón Aceptar, una nueva ventana, Valores del escenario, nos interrogará acerca de los valores de las celdas. Basta con teclear los valores de nuestro escenario OPTIMISTA.

Con esta operación, terminamos la creación de nuestro primer escenario. Al pulsar sobre Agregar, volveremos a la ventana Agregar escenario, crearemos el que hemos llamado NEUTRO y escribiremos los valores de las celdas cambiantes. De forma similar procederemos para el escenario PESIMISTA. Una vez finalicemos, dispondremos de tres escenarios, tal como aparece en la ventana Administrador de escenarios

Una vez seleccionado uno de los escenarios, al pulsar sobre el botón Mostrar, los datos de las celdas cambiantes del escenario en cuestión se situarán en las celdas Al, A2 y A3. Estas tienen, por tanto, tres ternas de valores asociados, una terna por cada escenario creado.
 
 

Tablas
Las tablas permiten conocer la influencia de una o dos variables sobre una fórmula dada, estudiando el resultado de la misma cuando dichas variables toman una serie de valores concretos. Por ejemplo, si el coste del agua depende de su consumo y del precio por metro cúbico, podríamos conocer mediante una tabla cuáles serían los diferentes costes cuando el coste/m³ varíe entre 50 y 250 Pta. de 10 en 10 Pta., y el consumo entre 5 y 25 m³, de 5 en 5 m³. Una vez que tenemos la tabla, podemos saber inmediatamente el coste de 15 m³ a 70 Pta., mirando en la intersección de la fila y la columna que contienen esos datos. Podemos crear también una tabla de una sola entrada, en la que varía un solo valor, por ejemplo, si suponemos fijo el precio y únicamente varía el consumo.

Vamos a ver la utilización de esta herramienta realizando un ejemplo sencillo, la tabla de multiplicar:

El resultado de una multiplicación concreta, por ejemplo, 5 x 6, se encuentra en la intersección de la columna del cinco con la fila del 6, así, en F7 tenemos el valor 30. Esta tabla se puede crear de dos modos:

• haciendo uso de fórmulas
• utilizando la herramienta tabla.

Utilizando la herramienta tabla nos liberamos de pensar en que formato escribir la referencia a las celdas de lafórmula.
Para ello, en primer lugar crearemos los datos iniciales de la tabla, en la primera columna (A2:A11 en nuestro ejemplo) pondremos los valores de una variable, y en la fila superior (B1:Kl) los valores de la otra. Normalmente, el relleno de series suele ser muy útil aquí, si los datos siguen un cierto orden. En la celda de intersección de la fila y columna de los datos, Al, tendremos que poner la fórmula o una referencia a la fórmula que se quiere calcular (en nuestro ejemplo, ponemos una referencia a la formula que habremos  creado en Al6, siendo esta =A14*A15, aunque no se muestra en la Figura; así, en Al pondremos la fórmula =Al6).

Una vez que tenemos creado el esqueleto de la tabla, tenemos que indicar a Excel que obtenga los resultados. Para ello, en primer lugar, deberemos seleccionar toda la tabla, incluyendo la fila y columna de los datos (A1:K11 en el ejemplo). Una vez seleccionado el rango, utilizaremos el menú DATOS|TABLA, apareciendo un cuadro en el que deberemos indicar las Celdas de entrada (fila y columna).

Celda de entrada (fila) pregunta por la referencia de la celda en la fórmula original cuyos valores los obtendrá de la fila superior del rango. Si en nuestro ejemplo, la fórmula original, situada en Al6, es =A14*A15, podemos decir que es la celda Al4.
Celda de entrada (columna) hace lo propio para la celda cuyos valores se encuentran en la primera columna (A15).

Cuando pulsemos sobre el botón Aceptar, se rellenará la tabla con los valores adecuados. Veamos como se han obtenido estos valores. Por ejemplo, en la celda F7 tenemos el valor 30. La fórmula original era =K14*K15, e indicamos a Excel que los valores de la celda Kl4 se tomaran de la fila superior, y los de la Kl5, de la primera columna. El valor de Kl4 para F7 se tomará entonces de Fl (5), y el de K 15 de A7 (6); sustituyendo estos valores en la fórmula, el resultado será 30 (5*6).

Si desea conocer el resultado en el caso de que varíen los valores de más de dos celdas simultáneamente, no será posible hacerlo, ya que sólo se admiten tablas bidimensionales; no se pueden enlazar con otras hojas, ya que, y esto es importante, la tabla a crear debe estar en la misma hoja donde se encuentran los datos.

Si se desea crear una tabla de una sola entrada en la que sólo varíe un dato, sí es posible hacerlo con Excel. Para ello sólo será necesario crear la columna (o fila) donde se encuentran los nuevos valores de la celda cambiante; la referencia a la fórmula original se coloca ahora no sobre la columna de los datos (o a la izquierda de la fila), sino sobre la columna adyacente de la izquierda (o fila inferior), justo encima de donde se obtendrán los resultados (o justo a la izquierda).

Es posible que cuando se cree la tabla, los resultados no sean los correctos (normalmente serán todos iguales al primero). Esto se debe a que es posible que esté inhabilitado el recálculo automático. Para obtener los resultados, en este caso, será necesario pulsar sobre la tecla <F9>, o elegir el menú HERRAMIENTAS|OPCIONES, y pulsar sobre el botón Calcular ahora que aparece en la pestaña Calcular.
 
 

Simulación y optimización
Buscar objetivo

Buscar objetivo es una herramienta que de forma simple y efectiva consigue que obtengamos el valor deseado en una celda cambiando para ello el valor de otra celda. Su potencialidad radica en que entre la celda objetivo y la que se ha de cambiar pueden existir un gran número de celdas relacionadas, que también verán alterado su valor para conseguir el resultado deseado.

Para llevar a cabo una búsqueda de objetivo hay que ejecutar HERRAMIENTAS|BUSCAR OBJETIVO, apareciendo una única ventana en la que hemos de indicar la celda objetivo y el valor que deseamos obtener en ella y la celda que deseamos que varíe su valor para conseguirlo. Para mostrar su funcionamiento vamos a seguir el siguiente ejemplo.

Supongamos que estamos pensando en abrir una tienda de "todo a 100", para lo que deseamos conocer cuántos productos hemos de vender en un mes para conseguir una cifra objetivo de beneficio de 250.000 pesetas mensuales, teniendo en cuenta los gastos mensuales propios de ese tipo de negocio, salarios (95.000), alquiler (125.000) y resto de gastos (85.000), siendo el precio de compra de todos los productos de 72 pesetas y el de venta de 100.

El beneficio mensual esperado será igual al número de productos a vender por la diferencia entre el precio de compra y venta, menos los gastos, B8=B7*(B6-B5)-B2- B3-B4. Si deseamos que sea 250.000, indicaremos en la ventana de buscar objetivo que deseamos definir la celda B8 con un valor 250.000, haciéndola depender del número de productos a vender, B7.

El resultado de la búsqueda de objetivo es que hemos de vender mas de 11.785 productos para conseguir dicho beneficio, lo que supone 491 productos al día, 61 a la hora, más de 1 al minuto (un análisis tan simple como este podría haber evitado el cierre de un gran número de establecimientos de este tipo).

Solver
Solver es una herramienta que nos permite optimizar los valores de una hoja en función de un conjunto de celdas a las que se pueden indicar que cumplan una serie de restricciones. Es un instrumento muy útil para realizar simulaciones y conseguir resultados de forma relativamente fácil utilizando, sin darnos cuenta, mecanismos de cálculo complejos.

Observemos su funcionamiento mediante un pequeño ejemplo, en el que suponemos que deseamos comercializar vehículos pequeños, de los que no necesitan carnet de conducir, y pretendemos determinar si nos interesa más comprar un coche acabado o montar nosotros las piezas. Los datos son los siguientes, teniendo en cuenta que al hacerlo nosotros tendremos un 23% de gastos, sobre el coste de las piezas y la mano de obra y que hemos de financiar el coste de fabricación, lo que supone un 3%. El precio de venta, en ambas opciones, será el coste del vehículo más el 10%.

Como puede observarse a priori, parece mejor encargar el vehículo, pero si  la empresa puedise negociar todas las partidas correspondientes al montaje del vehículo para comprobar que también si es mejor  montarlo y obtener, incluso, un margen algo mayor.
Supongamos que: el coste de las piezas no se puede variar, la mano de obra se puede bajar hasta 190.000 pesetas, se  podría rebajar otros gastos hasta el 21,5%, y la financiación en un 0,25%, y deseamos un margen mínimo del 10,75%.

Bajo estas restricciones deseamos conocer si nos interesa más montar los vehículos o pedirlos por encargo, para lo cual recurrimos a la herramienta solver, ejecutando HERRAMIENTAS|SOLVER.
Aparece una ventana en la que nos pregunta la celda objetivo, en este caso la del precio de venta del vehículo montado por nosotros, D13, al que deseamos que tome un valor de 605.000 pesetas, el mismo que para el vehículo por encargo.

Las celdas que deseamos variar son las de los costes que pueden cambiar C9:C11(C9, C10 y C11) y la del margen Dl2, . Indicamos al modelo las restricciones pulsando Agregar, apareciendo una ventana en la que pondremos las celdas y los valores máximos o mínimos deseados

Las celdas de las restricciones y aquéllas que pueden variar, deben estar relacionadas mediante fórmulas con la celda objetivo, de forma que, al pulsar Resolver, se realizan una serie de cálculos en iteraciones que intentan conseguir unos valores de las celdas implicadas que cumplan el objetivo y todas las restricciones. Si la encuentra nos muestra la ventana siguiente.

Si no ha encontrado una solución válida nos lo indica, de forma que hemos de volver a plantear el problema o especificar mejor las posibles variaciones de las celdas a cambiar mediante restricciones más precisas.

Una vez conseguida la solución deseada, podemos cambiar los datos de la hoja con los valores calculados por solver eligiendo la opción Utilizar solución de Solver.

Solver es una herramienta de una gran potencia cuya explicación en detalle cae fuera del alcance de este curso.

Si no encuentra Solver al pulsar de HERRAMIENTAS debe instalarla desde HERRAMIENTAS|COMPLEMENTOS, teniendo en cuenta que debe tener el fichero SOLVER.XLA en el CD-ROM de instalación o en su disco duro. Si no lo encuentra inicie el proceso de instalación de Excel.
 

Tablas dinámicas
Las tablas dinámicas son muy útiles para organizar, de una manera sencilla y rápida, grandes cantidades de datos procedentes de una hoja de calculo. Permiten agrupar y filtrar la información por cualquiera de los campos y de múltiples maneras, creando una tabla de resumen. Gracias a las tablas dinámicas se puede analizar los datos de una base de datos en función de determinados campos interrelacionados. Estas tablas se califican de dinámicas porque en ellas es muy fácil modificar la agrupación de datos realizada.

Para poder crear una tabla dinámica a partir de los datos de una hoja de cálculo, es necesario que los datos estén organizados en listas (cada campo con sus datos en una columna) con sus respectivos rótulos, es decir, como una base de datos de Excel, en donde los registros serían las filas y, los campos las columnas.

El procedimiento a seguir para su creación, utilizando el asistente para tablas dinámicas, es el siguiente:
Ejecutar el comando DATOS|TABLAS DINÁMICAS.

Aparecerá el primer cuadro del Asistente para tablas dinámicas.

donde hay  que  indicar la procedencia de los datos (Lista o base de datos de Microsoft Excel, Fuente de datos externa o Rangos de consolidación múltiples). Pulsando sobre el botón Siguiente se muestra el siguiente cuadro de diálogo del asistente de tablas dinámicas.

En él se escribe o selecciona con el ratón el rango de celdas de la hoja (incluido los rótulos, que se utilizarán como nombres de los campos) que se desean organizar en una tabla dinámica. Si previamente se activó el comando DATOS|TABLAS DINÁMICAS, habiendo seleccionado previamente el rango, el asistente sugiere el citado rango. Veámos esto con un ejemplo. Supongamos que disponemos de una hoja de cálculo que contiene las horas trabajadas durante una semana por los programadores de una empresa en unos determinados proyectos, tal como aparece en la Figura siguiente

El rango de celdas con los datos que se desean organizar en una tabla dinámica será: A1:D10. Obsérvese como este rango tiene una estructura similar a las tablas de una base de datos: la información de cada empleado en una fila, como registro, organizada en cuatro columnas o campos.
En este segundo paso del asistente, le indicamos o confirmamos (si el asistente propone el rango correctamente) el rango de celdas con los datos fuentes: A1:D10.

En este cuadro de diálogo aparece el comando Examinar, que sirve para elegir un libro de trabajo, especificando la unidad y directorio, es decir la ruta en donde se encuentre. Esta opción se utilizará cuando los datos fuente se encuentran en un libro que no estuviera abierto.

Se deberá pulsar sobre el botón Siguiente para que se muestre el tercer cuadro de diálogo del asistente de tablas dinámicas.

En el tercer cuadro se diseñará la distribución de los campos en la tabla a crear. En la parte derecha de la ventana se muestra un botón para cada campo del rango de datos (ver en la Figura siguiente los botones Empleado, Proyecto, Fecha y Horas) y en la parte izquierda aparece el área dinámica en donde se diseñará la tabla, que está dividida en cuatro secciones (PÁGINA, FILA, COLUMNA y DATOS), en las que se pueden colocar los distintos campos (pulsando sobre el botón del campo y arrastrándolo a una sección), teniendo presente que se organizarán los datos en la tabla así:

• El campo que se coloque en la sección PÁGINA, presentará cada uno de sus valores en una página diferente. Este tipo de campo aparecerá en forma de una lista desplegable, desde la que se podrá seleccionar aquél elemento del que se desee mostrar el resumen.
• El campo que se coloque en la sección FILA, mostrará sus elementos como encabezados o títulos de las filas en la tabla.
• El campo que se coloque en la sección COLUMNA, mostrará sus elementos como encabezados de las columnas de la tabla.
• Las entradas del campo que se coloque en la sección DATOS, se someterán a una determinada operación de cálculo o, también denominada en este contexto, función de resumen: Suma (que es la que se ofrece por defecto cuando las celdas con los datos contienen solamente valores numéricos), Contar (que es la que se ofrece por defecto para el resto de los casos), Promedio, Mínimo, Máximo, Producto, etc. Si la operación de cálculo que Excel ofrece por defecto no es la que queremos utilizar, deberá hacerse clic sobre ella para elegir otra. Se podrá añadir más de un campo en cada sección y es imprescindible colocar al menos uno en la sección DATOS.

Cada campo del rango de datos a incluir en la tabla dinámica se puede personalizar (en el ejemplo serán los campos Empleado, Proyecto, Fecha y Horas); bastará con pulsar con el ratón, dos veces, sobre el campo a modificar, para que se muestre el cuadro de diálogo de campo de la tabla dinámica, desde donde se puede, entre otras opciones:

• insertar subtotales a los campos filas y columnas,
• cambiar el nombre del campo en la tabla,
• cambiar su posición en las secciones de la tabla (orientación de los elementos del campo)
• ocultar algunos de sus elementos.

Una vez diseñada la tabla, al pulsar sobre el botón Siguiente se mostrará el cuarto y último cuadro de diálogo del asistente de tablas dinámicas.

Desde este último cuadro de dialogo, debemos indicar la posición de destino de la tabla dinámica (eligiendo entre Hoja de cálculo nueva u Hoja de cálculo existente), y tecleando, o seleccionando si la hoja destino estuviera abierta, la dirección de la celda superior izquierda a partir de la cual se colocará la tabla. Si la hoja de cálculo destino no estuviera abierta, se deberá teclear la ruta de acceso, el nombre de libro de trabajo, el nombre de la hoja y la dirección de la celda.

También se puede pulsar sobre el botón Opciones para ponerle un nombre a la tabla, indicar si se desea o no que se calculen totales para las filas y/o columnas, aplicar o no un autoformato, guardar o no los datos fuente duplicados junto a la tabla creada, actualizar o no los datos siempre que se abra el libro, etc. Sólo resta pulsar sobre el botón Terminar para que Excel cree la tabla dinámica.

Aunque los datos de una tabla dinámica tienen el mismo aspecto que cualquier hoja de cálculo según se ve en las siguientes figuras, no se podrá introducir ni editar los datos directamente en el área de datos, ya que la tabla se encuentra vinculada a unos datos fuentes, y serán éstos los que se puedan modificar.

 Obsérvese en las figuras citadas, como el campo Empleado, que actúa como una tercera dimensión, funciona como filtro, de manera que si pulsamos sobre el botón de filtro situado en la celda B1, se muestra una lista desplegable con los nombres de los empleados y con la opción Todas. Sólo tenemos que seleccionar el empleado en cuestión para que la tabla muestre únicamente las horas invertidas por ese trabajador en los diferentes proyectos (su página correspondiente). A continuación se muestra la tabla dinámica para cada uno de los empleados.



Una vez creada la tabla, se puede editar su diseño fácilmente, utilizando cualquiera de estos dos métodos:

• Volver a repetir los cuatro pasos del asistente para tablas dinámicas.
• Directamente desde la tabla, podemos colocar el cursor sobre el botón sombreado con el nombre de un campo y arrastrarlo hasta una nueva posición. También se puede activar el menú contextual asociado a la tabla, pulsando con el botón derecho del ratón sobre cualquier parte de ella para acceder, entre otras opciones, por ejemplo al comando Campo, con el que se muestra el cuadro de diálogo de campo de la tabla dinámica desde el que se pueden modificar algunos de los parámetros que le afectan.