MANUAL
EXCEL
AVANZADO
Manual Excel Avanzado
BIOS
Módulo Funciones Avanzadas
Primer Ejemplo – Funciones simples
Dada la siguiente planilla:
Supongamos que la nota final se calculará basándonos en el promedio de notas que obtuvo cada estudiante en sus parciales.
Por lo tanto aplicaremos la función promedio:
=PROMEDIO (B2:D2)
Recordemos que cualquier función que vayamos a aplicar debemos posicionarnos sobre la celda, y comenzar con el signo = o en su defecto el signo +.
Por otro lado si hubiéramos querido tomar en cuenta solo dos de los parciales dentro del promedio tendríamos que:
=PROMEDIO (B2; D2)
En el caso de que queramos tomar rangos discontinuos esto se aplicaría de la siguiente forma:
=PROMEDIO (B2:D2; H2:J2)
La planilla hasta ahora quedaría así:
Además en el caso de copiar esta fórmula, podríamos realizarlo mediante la opción clásica de dar sobre el botón derecho sobre la celda, seleccionar la opción copiar, y luego marcar el rango (en este caso de E3:E8) y darle pegar.
Mas allá de esta clásica opción también se podría copiar la formula de forma simple dando DOBLE CLIC en el borde inferior derecho de la celda que contiene la formula a copiar.
En la siguiente columna, se hará un breve repaso de la formula condicional SI. De acuerdo a esta formula deberá aplicarse:
=SI (prueba _ lógica; valor verdadero; valor falso)
En el caso de la planilla que manejamos anteriormente, esta quedaría de esta manera:
=SI (E2<5; “Reprobado”; “Aprobado”)
Si tuviéramos más de una función SI para establecer, recordar que siempre vamos a tener que evaluar el conjunto de opciones de nuestro problema (conviene comenzar desde el nivel mas bajo) y haremos una función menos que opciones de nuestro caso.
=SI (E2<5; “Reprobado”; SI (E2<=7; “Parcial”; “Aprobado”)
No debemos perder de vista que la función SI puede ser utilizada en este tipo de casos, y concatenar hasta 7 funciones SI (tenemos este límite).
De la misma manera que anteriormente lo hicimos, podemos copiar la fórmula sin problemas. No olvidemos que hasta ahora hemos dejado de lado el tema de Referencias Relativas y Absolutas ya que no hizo falta fijar ninguna de las celdas a copiar.
Vamos a armar un cuadro adicional anexo a la planilla para implementar otras funciones:
En este cuadro calcularemos cuantos fueron los alumnos que obtuvieron como calificación Aprobado, Parcial y Reprobado. A su vez calcularemos con respecto al total de alumnos, el porcentaje que obtuvo cada calificación.
Lo haremos aplicando la función CONTAR.SI, de la siguiente manera:
=CONTAR.SI (F2:F8; “Aprobado”)
También podríamos haber escrito:
=CONTAR.SI (F2:F8; A12)
Veamos a su vez que si pensáramos en copiar esta fórmula debería quedar:
=CONTAR.SI($F$2:$F$8;A12)
La razón del signo $ es el hecho de fijar el rango F2:F8 ya que las calificaciones son fijas, mientras que la celda A12 es relativa ya que cambiará de fila al copiar.
En la función CONTAR.SI tenemos en cuenta el rango a evaluar, y como segundo argumento tenemos el criterio a tomar en cuenta para copiar.
Si aplicamos la funcion CONTAR, o también CONTARA, podremos saber cuantos alumnos tendremos.
=CONTAR (E2:E8)
En este caso la función CONTAR aplica perfectamente para este caso puesto que las celdas que se evaluan tienen contenido numérico.
En el caso de querer hacer un recuento por apellidos:
Pasando a otro tema veremos a continuación alguna aplicación adicional de la función SI.
En la siguiente planilla se evaluará la calidad de los productos según su diámetro. Si el diámetro está comprendido entre 10 y 20 su calidad será A, y de lo contrario será B.
Vemos que en este caso no solo se aplicó la función SI, sino que se combinó con la opción Y (también podriamos haber aplicado la opción O), de la siguiente manera:
=SI(Y(opcion 1; opcion 2;…..); valor verdadero; valor falso)
=SI(O(opcion 1; opcion 2;…..); valor verdadero; valor falso)
En el caso de combinar con la opción Y, recordemos que estaríamos hablando de que si se cumplen UNA Y CADA UNA DE LAS OPCIONES EN FORMA SIMULTANEA se aplicaría la opción verdadera, y en el caso que no fuera así se aplica la opción falsa.
Mientras que en el caso de las opciones que damos mediante el O, con que una de las opciones fuere verdadera, ya se aplicaría la opción del valor verdadero en la condición, y solo si no se cumple ninguna de las opciones se aplicaría el valor falso.
Pasemos ahora a un nuevo ejemplo y continuamos en la aplicación de funciones:
En este caso aplicamos una función de búsqueda, que se denomina BUSCARV.
Recordemos que la forma de utilizar esta función de búsqueda es la que se demuestra a continuación:
=BUSCARV(valor buscado; matriz; número de columna; [ordenamiento])
En el caso particular de la planilla
=BUSCARV(A13; A2:C8; 3; FALSO)
Vemos aquí que el valor buscado hace referencia a la celda donde el usuario cargará el dato en el que se basará la búsqueda. Este dato no debe ser parte de la matriz de búsqueda (A2:C8) si no, esto no tendría sentido en la búsqueda. Luego se carga la matriz, al hacer referencia a la matriz no cargamos los títulos de la misma, solo el contenido (registros de la misma), luego cargamos la referencia al número de columna que se va a extraer, en este caso la columna 3 ya que es la del stock (columna con dato a extraer) y por último como argumento OPCIONAL el orden dentro de la matriz, en este caso falso ya que la primera columna de la matriz no está ordenada en forma ascendente.
Además en el caso del argumento opcional de ORDEN esto tambien es realmente significativo al momento de aplicar la coincidencia en la búsqueda. Si este argumento es FALSO y la busqueda no encuentra el dato preciso retorna un error. Por ejemplo:
En este caso al no encontrar el Artículo A5565 devuelve un error del tipo #N/A, o dato NO DISPONIBLE (Not Available).
En cambio veamos que pasa cuando el argumento de orden lo cambiamos a VERDADERO.
En este caso, mas allá que el producto A5565 no se encontró de todas formas retornó el valor de stock del artículo cuya coincidencia no era exacta, sino aproximada.
En muchos casos cuando la búsqueda no tiene porque ser exacta, este argumento VERDADERO puede ayudarnos a encontrar el dato más cercano que coincida con nuestra búsqueda.
En cambio anteriormente cuando el argumento fue cambiado por FALSO, la búsqueda no nos retornaba ningún valor ya que el dato no lo encontraba (con coincidencia exacta) en la matriz de registros.
En otro ambito también sería bueno en una planilla controlar los ingresos de información, y es por esto que vamos a introducir el tema VALIDACIÓN DE REGISTROS.
VALIDACIÓN DE DATOS
En el caso de la planilla que presentamos anteriormente, vamos a comenzar este tema de validación tratando de que el usuario se limite a agregar artículos con STOCK entre 0 y 300, y no pueda introducir un número menor a cero, ni mayor a 300.
Veamos como operar:
1. Pintamos las celdas de stock (celdas en las que vamos a dar la regla de validación) 2. Luego vamos al menú de datos – validación
3. Establecemos la regla de validación a operar 4. Opcionalmente definimos mensaje entrante 5. Opcionalmente definimos mensaje de error
Veamos el paso en cada pantalla:
En este primer paso marcamos las celdas de Stock y luego nos dirigimos al menú de Datos – Validación
En el segundo paso definimos la regla de validación:
En este caso, definimos que lo que vamos a permitir es un número entero en dichas celdas y este número deberá estar comprendido entre 0 y 300.
Tenemos otras opciones al desplegar el cuadro Permitir, que las usaremos en algunos ejemplos posteriores.
Poniendo otro ejemplo:
En este caso nos posicionamos en la celda donde la persona va a realizar la búsqueda del stock de determinado artículo (A13), y damos una regla de validación para que solo pueda ingresar códigos de artículos existentes en la matriz de datos:
Veamos que sucede cuando aplicamos también el uso de la solapa de Mensaje Entrante
Esto resultará cuando la persona se posicione sobre la celda A13 (en la cual se determinó la regla de validación).
Veamos como en la pantalla que se muestra a continuación, se muestra el mensaje entrante:
Un comentario con respecto a esto, es el hecho de poder trabajar en las reglas de validación, tanto con rangos dinámicos como también con rangos con un nombre determinado.
Veamos como funciona:
En este caso, al dar como origen de la lista la selección: =$A:$A (referencia a la columna A)
La ventaja que se logra es el hecho de poder incorporar mas artículos con su respectivo stock a la matriz de esa manera integrarlos a la regla de validación de forma automática.
Aplicando lo que comentabamos:
En este caso agregamos un artículo a la lista, y luego vemos como el artículo se integra en forma automática al cuadro combinado en la celda que tiene la validación.
DEFINIR NOMBRES A RANGOS
En el siguiente ejemplo veremos como aplicamos en lugar de una referencia a una lista formada por determinadas celdas, a un rango con un nombre preasignado
En primer término asignaremos al rango, un nombre como por ejemplo ARTICULOS
En el área señalada asignamos el nombre al rango de artículos. O sea primero, señalamos el rango de artículos (A2:A9) y luego le designamos un nombre a este rango. De ahora en mas cuando yo quiera hacer referencia a las celdas A2:A9 ya podremos escribir simplemente ARTICULOS que Excel entenderá las direcciones a las que se hacer referencia.
Otra manera de plantear las referencias de rangos a los que se le aplica un nombre sería:
Mediante el menú Insertar – Nombre – Definir, también podremos tanto crear rangos con nombre, eliminar rangos con nombre, y también modificarlos.
Veamos que en dicha pantalla podemos asignar el nombre, y en donde define Se refiere a: podremos determinar la dirección de las celdas que vamos a tener dentro del rango.
En este sentido esta pantalla nos da mejor manipulación sobre los rangos, ya que no solo
podremos asignarles nombres a determinadas celdas o rangos, sino modificar la dirección a la que hacen referencias, como también eliminar determinados nombres que ya no sean de aplicación para nuestra planilla.
En el caso de querer asignar un rango con nombre, en una regla validación, esto quedaría:
Vemos que aquí como origen de la lista determinamos el rango con nombre que denominamos ARTICULOS. (Nota: Recordar el signo = puesto que no queremos hacer referencia a un texto sino a un rango).
FORMATO CONDICIONAL
Este tema a tratar será interesante para aplicar dinamismo en cuanto al formato de celdas. También nos servirá como aviso en determinadas planillas en las que busquemos que la misma cambie cuando un factor se cumpla.
Esto se trata de una combinación entre la función condicional SI y la aplicación de formatos (bordes, sombreados, subrayado) en celdas y rangos.
Tenemos básicamente dos maneras de aplicar este tema:
1. Aplicación del formato condicional de acuerdo al valor de una celda 2. Aplicación del formato condicional vinculado a una formula.
Aplicaremos en primer lugar un formato condicional de acuerdo al valor de una celda.
Por ejemplo si queremos que la planilla nos señale todo artículo cuyo Stock este por debajo de las 70 unidades.
Señalamos en primer lugar el rango al que le vamos a aplicar el formato condicional, en este caso el rango formado por las celdas que determinan el stock.
En segundo lugar vamos al menú de formato – formato condicional.
En el menú de formato condicional, en este caso la condición depende del Valor de la Celda, y a este valor lo podemos condicionar con operadores lógicos (<, >, <=, >=, =) por lo tanto podremos comparar los valores de las celdas con dichos operadores.
En el caso que veíamos anteriormente determinamos que evalúe si el valor de la celda es menor a 70, y en caso de ser verdadera esta prueba lógica, aplicamos el formato, dándole una trama con color rojo, lo que señalará la celda de este color cuando se cumpla esta condición.
En el caso que queramos aplicar más de una condición podremos agregar hasta 3 condiciones en el formato condicional, como se muestra en el ejemplo a continuación:
La opción de agregar nos permitirá ir agregando condiciones, con dicho límite de 3 condiciones en esta herramienta.
NOTA: La posibilidad de agregar más condiciones en formato condicional se podrá manejar desde Macros en VBA.
En el ejemplo visto anteriormente estamos aplicando color rojo a los artículos cuyo stock este por debajo de las 70 unidades, aplicaremos color azul a los artículos entre 70 y 99 unidades y por último si el artículo tiene 100 o mas unidades en su stock la celda se pintará de color de fondo verde.
En caso de que queramos aplicar dicho formato condicional con estas mismas condiciones pero deseamos marcar toda la fila cuando se de las condiciones propuestas, esto lo haremos
seleccionando en primer lugar toda la matriz de datos (desde A2:C9).
Veamos que aquí comenzamos señalando el área (A2:C9), luego fuimos al menú formato – formato condicional, y dentro del área de definición de formato, aplicamos como condición la dependencia de una Fórmula, y dentro del rango donde se evalúa la fórmula, establecimos una referencia a la celda C2.
Nótese que la celda C2 la reverenciamos aplicando en forma fija la columna y variable para la fila ($C2) ya que el formato condicional lo aplicará para cada una de las filas marcadas (desde fila 2 a fila 9) pero lo aplicará siempre basándose en el valor de la columna C.
A su vez es importante notar que la celda a la que refiere toda formula del formato condicional
DEBERÁ SER SEÑALADA CON EL MOUSE PARA QUE NO LO TOME EN FORMATO TEXTO Y AGREGUE AUTOMATICAMENTE COMILLAS A LA SINTAXIS.
De la misma manera que la vista anteriormente podríamos aplicar varias condicionales en este formato, pero siempre con la limitación de hasta 3 condiciones aplicadas. Como vemos a continuación:
Un comentario adicional es el hecho de que para eliminar alguna de las condiciones propuestas en el formato condicional, simplemente volvemos a seleccionar el área a la que le queremos quitar en este caso el formato (A2:C9) y luego nos dirigimos nuevamente al menú de formato – formato condicional, y seleccionamos sobre el botón eliminar:
Como vemos tenemos la opción de eliminar una de las condiciones propuestas, solo 2, o todas las condiciones que hayamos determinado.
Es importante señalar el rango de antemano ya que si no, Excel no encontraría en que celdas se aplicó el formato condicional.
En el siguiente ejemplo se mostrará esta herramienta aplicada con fórmulas de Excel, y la idea será que automáticamente queden marcados los cheques vencidos a la fecha actual (la fecha actual se calculará mediante la función HOY()).
En este caso seleccionamos en primer lugar la matriz (A2:D5) y luego lo que hicimos fue aplicar el formato condicional, donde la condición depende de una fórmula y en la misma definimos la celda C2 en la cual fijamos la columna y dejamos como variable la fila ($C2) y comparamos con el operador menor o igual al resultado de la función HOY(), la cual nos obtendrá en forma automática la fecha actual del sistema.
BÚSQUEDAS DE INFORMACIÓN
Mencionamos en un ejemplo anterior que mediante la función BUSCARV podiamos realizar búsquedas en matrices, y extraer de las mismas un dato de determinada columna en particular. En este ejemplo veremos no solamente la aplicación de BUSCARV, sino también la función BUSCARH y las limitaciones que tenemos en el uso de dichas funciones.
En este caso vemos la función BUSCARV, aplicada para buscar en base al código del artículo el stock del mismo.
1. Seleccionaremos mediante el Mouse toda la matriz de datos (A1:C9) 2. Botón Derecho – Copiar
3. Seleccionaremos la celda E1 4. Botón Derecho – Pegado Especial
5. En las opciones seleccionaremos – Trasponer (como lo vemos en la pantalla a continuación)
Veamos como queda transpuesta la matriz original:
El tema de transponer la matriz también nos servirá para ver la aplicación de la función BUSCARH que nos permitirá extraer el contenido de una FILA en particular de la matriz.
Por otro lado, una de las restricciones importantes que hay que destacar tanto en el BUSCARV como en el BUSCARH es el hecho de no contar con la posibilidad de basarme en un dato buscado y querer obtener una columna a la izquierda en el orden de la matriz, o una fila hacia arriba del dato buscado en el caso de BUSCARH.
Por ejemplo si en el ejemplo anterior yo me baso en la descripción (como dato buscado) y en base a la descripción pretendo obtener el código de dicho artículo.
Veamos por lo tanto la aplicación de dos funciones que nos permitirán solucionar este tema. Las funciones son INDICE y COINCIDIR. La aplicación es la siguiente:
El resultado será 5 (Nro de índice)
En este caso aplicamos la función COINCIDIR, y dentro de sus argumentos colocamos, el valor buscado, en dicho ejemplo la celda A13, donde el usuario ingresó la descripción del artículo, como segundo argumento indicamos la columna B (rango B2:B9) que es donde realizaremos la búsqueda de dicha descripción, y por último como tercer argumento el número 0 que está indicando que la coincidencia debe ser exacta con el dato buscado.
El resultado de la función COINCIDIR será 5, que es la posición del dato buscado en la lista de valores. O sea la devolución siempre me la da en el número de índice del dato buscado en la nomina de valores.
Por otro lado si yo aplico la función INDICE de la siguiente forma:
En cuanto a la función INDICE los argumentos son, la matriz de búsqueda, donde aquí señale la nomina de códigos de artículos, y como segundo argumento el número de fila del índice que debe buscar, en este caso 5. Este número 5 lo obtuve viendo el resultado obtenido por la función COINCIDIR en el dato anterior.
Por lo tanto, y resumiendo, esto podría ser simplificado en una sola celda, de la siguiente forma:
La función INDICE, me extrae el dato de una matriz de acuerdo a la ubicación de este dato en dicha matriz. Mientras tanto la función COINCIDIR lo que está haciendo es buscando el dato en una matriz de información, y buscando la posición de este dato, con una coincidencia exacta.
FUNCIONES DE FECHA Y HORA
Aplicaremos en este ejemplo las funciones de fecha y hora que nos provee Excel.
Para comenzar, si yo quisiera obtener los días corridos en que se implementó cada una de las tareas, tenemos que entender que TODA fecha que ingresemos en Excel, esta fecha la lectura que tendrá será en número de días, comenzando con el número 1 correspondiente al 01/01/1900.
Por lo tanto si en una celda yo coloco la función =HOY() y esta me devuelve la fecha actual, supongamos 17/10/2007, en realidad el contenido REAL de la celda será el número 39372.
Basta con cambiar el formato de la celda, a un formato numérico y vemos el resultado.
Entonces, si yo necesito saber los días que tomó cada tarea simplemente puedo restar las celdas de columna C y columna B, y tengo el resultado esperado. (NO OLVIDAR ASIGNAR FORMATO NUMÉRICO A LA COLUMNA D, para obtener en cantidad de días).
Hasta aquí no hemos aplicado ninguna función en particular. El tema se da cuando tengo que calcular días habiles entre la fecha de inicio y fecha de fin. Para utilizar este tipo de funciones, Excel nos provee de COMPLEMENTOS. En este caso debemos habilitar el COMPLEMENTO de HERRAMIENTAS PARA ANÁLISIS, de la siguiente manera:
En el menú de Herramientas – Complementos tendremos la posibilidad de ver los complementos que tenemos y podremos marcar los que
necesitemos.
Recordar que todos los complementos que
marquemos quedarán habilitados de ahora en más en TODAS las sesiones que iniciemos con Excel, dentro de dicha PC.
En este caso habilitamos el COMPLEMENTO de HERRAMIENTAS PARA ANÁLISIS, que es el que nos proveerá de las funciones necesarias a aplicar.
Para el cálculo de días hábiles utilizaremos la función DIAS.LAB
En dicha función los argumentos serán =DIAS.LAB (FECHA INICIO; FECHA FIN; [FERIADOS])
El argumento de FERIADOS es opcional, ya que podríamos omitirlo y la función de todas maneras me realizaría el cálculo. La idea es que al tener el argumento de FERIADOS, no solo excluya de los días hábiles sábados y domingos sino también los feriados que aparecen en la nómina.
Podríamos comprender dentro de esta nómina feriados no laborables, y laborables, eso lo seleccionará quien esté a cargo del desarrollo de la planilla.
No olvidemos fijar el rango donde dispusimos los FERIADOS ya que al copiar la fórmula se debe contemplar este rango como FIJO.
En el siguiente ejemplo vamos contemplar los días para la entrega de determinados pedidos.
Si los días fueran corridos simplemente digitamos: =B2+C2
Pero en el caso de que a partir de la fecha de pedido yo quiera agregar cierta cantidad de DIAS HÁBILES, aquí el tema cambia, y aplicaremos para esto la función: DIA.LAB(FECHA INICIO; DIAS LABORALES; [FERIADOS])
Lo que nos implementa dicha función es la posibilidad de que a cierta fecha se le agreguen o se le descuenten cierta cantidad de días hábiles, agregando como argumento opcional los feriados.
Recordar que, para estos casos, también puede resultar común querer visualizar a partir de determinada fecha, el día de la semana a la que corresponde. Para ello tenemos la función DIASEM, que, a partir de una fecha nos dice qué día de la semana corresponde.
Veamos que la función DIASEM, nos devuelve un número correspondiente al día de la semana que nos cae esa fecha, desde el 1 para domingo, hasta el 7 para sábado.
Si quisiéramos que esto nos de exactamente en letras el día de la semana, basta con cambiar el formato de la celda para personalizarlo como vemos a continuación:
Al dar botón derecho, formato sobre dichas celdas en las que aplicamos la función DIASEM, vamos a Personalizada, y en el Tipo, podremos optar por DDDD para que nos de en letras el día
correspondiente de la semana.
Nota: si hubiéramos escrito DDD, nos daría la abreviación del día de la semana.
Trabajando con HORAS
Veamos en la siguiente planilla en la que
ingresamos los horarios de entrada y salida de un empleado a la empresa.
No se presentan problemas, al restar en cada día de la semana las horas de salida y las de entrada. Ahora al momento de calcular el total de horas que este empleado realizó en toda la semana, veamos lo que sucede:
En este caso aplicamos la función SUMA para tomar en cuenta el rango de horas, y poder obtener el total de horas. Pero Excel muestra un resultado que no es del todo correcto.
En realidad el cálculo internamente lo realiza
correctamente pero el resultado lo expresa en días, y por lo tanto esto sería 1 día, 19 horas, y 14 minutos.
Lo único que hacemos es cambiar el formato de la celda, para que refleje el resultado en TOTAL de horas. Veamos como realizar este paso:
Al momento de calcular el pago a realizar, tomo la celda del total de horas, multiplico por 24 para pasar el resultado a horas, y luego por el precio hora. Recordar que mas allá del cambio de formato que hicimos, internamente Excel sigue reconociendo dicha celda en termino de días y no en termino de horas.
BUSCAR OBJETIVO Y SOLVER
Estas dos herramientas nos servirán en determinadas ocasiones en las que queramos realizar previsiones o estimaciones en Excel.
Pasemos al siguiente ejemplo:
En este caso manejamos un ejemplo donde un restaurante que vende café, tiene su precio unitario en 30 pesos, y a través de la cantidad de tazas vendidas genera el total de la recaudación.
Debemos tomar en cuenta que esta estructura se debe contemplar para la aplicación de BUSCAR OBJETIVO, donde una fórmula debe generar la dependencia de un conjunto de factores (celdas dependientes) que resuelven el esquema (en este caso la recaudación depende de la cantidad de tazas vendidas y del precio unitario).
Para utilizar la herramienta, vamos a Menú Herramientas – Buscar Objetivo:
En la pantalla de configuración: Definir la celda: en este caso B3, celda que contiene fórmula dentro de la planilla.
Con el valor: vamos a definir como valor 6000 pesos (total de recaudación estimada, o previsión de recaudación).
Para cambiar la celda: B2 (celda que actúa como variable del sistema, y que calculará automáticamente la herramienta)
En este caso, yo podría haber resuelto el sistema perfectamente mediante una regla de tres simple, pero en casos más complejos como el que vemos a continuación el cálculo de la estimación sería posible con mayor esfuerzo:
En este caso planteamos un ejemplo donde se otorga un préstamos de 20000 a una tasa anual del 8% y donde se pagarán 12 cuotas. La función pago como vemos nos calculará de forma automática la cuota a pagar, que en dicho caso será de 1739 (Nota: el valor resultante será negativo ya que las funciones financieras toman los egresos con este signo).
Ahora, veamos que sucede si en este caso quisiéramos estimar cual sería el monto del préstamo que podríamos obtener si se mantienen las condiciones, pero sabemos que podemos pagar una cuota máxima de 1500. Para esto aplicamos la Herramienta Buscar Objetivo de la siguiente manera:
El sistema nos resuelve y dirá que mediante el pago de una cuota de 1500 y manteniendo las condiciones, llegaremos a obtener un préstamo de 17243.
La herramienta siempre trabaja con una variable a calcular y una fórmula cuyo resultado final será estimado por el operador.
Veamos que en este último ejemplo nuestro supuesto es estimar un valor de cuota de 1500 (léase que debe ser negativo, por tomarse como un egreso financiero), y manteniendo las condiciones, se calculará el montó del préstamo al que la persona podrá acceder.
En el caso de la herramienta SOLVER, actúa como COMPLEMENTO de Excel por lo tanto hay que habilitarlo, dentro del menú Herramientas – Complementos, y tildar la casilla de verificación en caso de que dicha herramienta no estuviere previamente instalada en la PC que estemos manejando.
SOLVER
Esta herramienta es similar a Buscar Objetivo con la salvedad de contar con la ventaja de resolver sistemas con determinada cantidad de variables, y con la posibilidad de establecer restricciones, que acerquen el sistema a una realidad planteada.
Veamos el siguiente caso:
En este caso establecimos una planilla similar a la planilla que trabajamos anteriormente. Se trata de un restaurante que vende varios tipos de Café, entre ellos Café común, Café Cortado, Café Capuchino. Aplicamos en el menú Herramientas – Solver la configuración de dicho sistema.
Entonces: en Celda Objetivo establecemos la celda que vamos a estimar, en este caso la celda del total recaudado por el restaurante, luego establecemos el importe del valor estimado para dicha celda.
En la referencia de cambiando las celdas establecemos las celdas que se van a calcular
automáticamente por la resolución del sistema, en el caso nuestro marcaremos mediante la tecla CTRL cada una de las celdas que tiene la cantidad de tazas de cada tipo de café.
Por último, en el área inferior izquierda de esta pantalla establecemos mediante el botón agregar, la/las restricciones del sistema, en este caso nuestra única restricción fue agregar a la celda B10 que contiene el total de tazas vendidas de café capuccino, que el limite es que dicha celda contenga un valor menor a 50. Podemos agregar múltiples restricciones ya que cada vez que queramos agregar una restricción mas, solo presionaremos en el botón agregar lo que nos permitirá ir agregando nuevas restricciones. También tenemos la posibilidad de cambiar, y eliminar restricciones mediante los botones asociados.
El esquema queda resuelto y vemos la solución final en la planilla. Podemos en esta pantalla marcar la opción de Utilizar la solución encontrada por SOLVER, o también podemos Restaurar valores originales para regresar al estado inicial de la planilla antes de resolver el esquema.
Las aplicaciones de dicha herramientas son muchísimas, tanto en ámbitos de estadística, probabilidad, como también en materia de Matemáticas, Ingeniería y Economía.
MÓDULO
ANÁLISIS
DE
INFORMACIÓN
Módulo: Análisis de Información
En este módulo se verá la aplicación de herramientas volcadas al análisis de información dentro de Excel.
En primer lugar, con un ejemplo se aplicará el uso de los diferentes tipos de filtros que nos provee la herramienta.
Veamos el siguiente ejemplo:
En esta planilla aplicaremos el primer tipo de filtro, que será el Autofiltro y que nos permitirá aplicar filtros automáticos en la planilla de una forma muy sencilla.
Para aplicarlo bastará con posicionarnos en cualquier celda de nuestra base de datos y luego mediante el menú de Daots – Filtro – Autofiltro comenzaremos la aplicación del mismo.
La planilla nos quedará de la siguiente manera:
Veamos en primer lugar, que cuando nosotros marcamos en el menú de datos – filtros – autofiltro, este actúa como un subrayado, donde podemos tildar y destildar esta opción, en caso que queramos deja de utilizar el Autofiltro en algún momento.
El autofiltro nos da esta opción del cuadro desplegable en el título de cada campo de la base de datos.
Bastará con pinchar en el cuadro desplegable para poder seleccionar el criterio a aplicar para filtrar la información sobre dicho campo. Por ejemplo, si en esta planilla yo quisiera solo ver las ventas del vendedor Díaz, solo seleccionaré Díaz en las opciones del campo Vendedores, y esto me aplicará automáticamente el filtro con dicho criterio.
En el caso anterior el sistema en forma automática oculta las filas del resto de vendedores y deja solo visibles las ventas del vendedor seleccionado.
Podemos a su vez aplicar otro filtro en forma simultanea sobre otro de los campos como por ejemplo el campo de Región, solo bastará con seleccionar de dicho campo la opción de la región que necesitaremos visualizar y la herramienta aplicará este filtro en forma totalmente dinámica.
QUITAR AUTOFILTROS – RESTABLECER BASE DE DATOS
En caso de que en algún momento, apliquemos varios filtros de acuerdo a varios campos y en algún momento queramos quitar dicho filtro, existen dos opciones: podemos quitar el autofiltro directamente yendo al menú de datos – filtros – autofiltro, o también mediante el menú de datos – filtros – mostrar todos (esto muestra nuevamente todos los datos originales de la base de datos pero mantiene el autofiltro.
CRITERIOS PERSONALIZADOS
También muchas veces se puede dar el caso de que el criterio para filtrar la información no este disponible en forma directa sobre el campo que queramos filtrar, y por lo tanto, del cuadro desplegable recurrimos a la opción de Personalizar, para este campo:
Cuando seleccionamos la opción Personalizar, podremos aplicar criterios un poco mas avanzados para filtrar dicho campo:
iguales a 100000. Podemos ver a su vez que existen múltiples criterios que podemos aplicar sobre dicho campo.
La planilla luego de aplicar dicho filtro ser verá de la siguiente manera:
Un detalle sumamente importante a tomar en cuenta es el hecho de que si en dicha planilla quisiéramos en algún momento obtener la suma de ventas del 2005 pero únicamente de los datos visibles no podemos aplicar la función =SUMA (C2:C15), puesto que tomaría todas las filas (tanto visibles como no visibles).
Para este tipo de casos aplicamos la función =SUBTOTALES (9;C2:C15)
SUBTOTALES (núm_función; ref1, ref2,...)
Núm_función Función
1 PROMEDIO
2 CONTAR
3 CONTARA
4 MAX
5 MIN
6 PRODUCTO
7 DESVEST
8 DESVESTP
9 SUMA
10 VAR
11 VARP
Veamos en el ejemplo dado, los resultados de la función SUMA y SUBTOTALES aplicados para dicho rango de celdas (C2:C15).
NOTA: También podríamos haber utilizado el botón ∑
Dicho botón aplica automáticamente la función correcta según encuentre celdas ocultas o no en ese rango (aplica función SUMA en caso de que no existan filas ocultas, y aplica en forma predeterminada SUBTOTALES cuando encuentra filas ocultas en el rango).
El autofiltro también nos permite filtros personalizados en el caso de columnas con datos tipo TEXTO, por ejemplo si yo quisiera obtener los datos de ventas de aquellos vendedores cuyo apellido termine con letra S.
En dicho ejemplo se aplica sobre el campo Vendedores, el criterio de personalización y luego el filtro que determina que dicho campo muestre únicamente registros que termina con letra s.
Hay una limitación en la aplicación del autofiltro y es, justamente la consigna para pasar al siguiente tema. No podemos mediante el autofiltro aplicar criterios que comparen campos por ejemplo, en el siguiente caso, vamos a aplicar un filtro que nos exponga únicamente las ventas del 2005 superiores a las del 2006.
Eso lo lograremos con el Filtro Avanzado:
Recordemos que para poder aplicar el filtro avanzado debemos hacerlo sin tener la opción de autofiltro encendido. Por lo tanto si tenemos el autofiltro, debemos apagarlo yendo a Datos – Filtro – Autofiltro.
Para aplicar el filtro avanzado vamos a Datos – Filtro – Filtro Avanzado, y luego:
Veamos que como criterios aplicamos la comparación entre la celda C2 y D2, donde solo colocamos la fórmula =C2>D2
En el cuadro que nos aparece cuando aplicamos el filtro:
Como Rango de la lista: A1:D19 (marcamos la base de datos completa)
Rango de Criterios: F7:F7 (marcamos las dos celdas, el título del criterio, y la celda que contiene el criterio propiamente dicho)
Veamos que en dicho cuadro en este caso aplicamos la opción que aparece por defecto que es: Filtrar la lista sin moverla a otro lugar, ya que lo que queríamos era aplicar el filtro directamente en la base de datos.
En la siguiente pantalla se expone como el filtro aplica dicho criterio directo sobre la base de datos:
Aquí volvemos a comentar el hecho de que si queremos obtener la sumatoria de ventas de 2005 o 2006, no debemos usar la función SUMA, puesto que deberíamos utilizar la función SUBTOTALES.
Para restaurar la base de datos volvemos al menú Datos – Filtro – Mostrar Todo, como se presenta en la siguiente pantalla:
En el caso de querer aplicar filtros en forma simultánea, tendremos que armar dichos criterios en la misma línea. En el siguiente ejemplo veremos la aplicación de un filtro donde vemos las ventas 2005 superiores a las del 2006, pero solo de la Región Sur, veamos no solo la aplicación del filtro, sino el armado de dichos criterios:
Aquí veamos que como Rango de Criterios marcamos F6:G7, ya que los criterios en este caso son 2, uno que aplica sobre el campo Región, donde se extrae solo datos que coincidan con Región SUR, y por otro lado, el siguiente criterio (le dimos un título arbitrario, en este caso “Criterio”), donde hicimos la comparación de uno de los registros de ventas 2005 contra un dato de ventas 2006.
Así nos quedarían los datos filtrados, de ventas de Región SUR, donde las Ventas 2005 superan a las del 2006:
Veamos que sucede si los criterios son los mismos pero no respetamos el tema de que sean en la misma línea:
Aquí se puede ver que aplicamos un filtro donde la Región es SUR, pero no hace al mismo tiempo comparativo de ventas entre el año 2005 y 2006, mientras que para el resto de ventas de otras Regiones SÍ aplica el criterio de comparar las ventas 2005, y que dichas ventas sean superiores a las del año 2006.
Podemos seguir aplicando criterios, pero siempre que apliquemos varios criterios, tienen que estar armados diferenciando los campos de la base de datos, pero también en el mismo orden que aparecen dichos campos en la base de datos.
Veamos ahora en un ejemplo donde apliquemos este mismo filtro pero con una extracción de una sub base de datos para no modificar la base original:
En la pantalla que nos aparece en este caso marcamos la opción de Copiar a otro lugar, y nuevamente en:
Rango de la lista: A1:D19 (nuevamente marcamos toda la base de datos)
Rango de criterios: F6:G8 (aquí marcamos todos los criterios, en este caso los dos criterios)
Copiar a: Aquí marcamos una celda cualquiera vacía ya que a partir de ahí se va a generar la sub base de datos con los datos que cumplan con los criterios pautados.
Si en dicho ejemplo planteamos que la sub base de datos solo obtenga los campos de Vendedores, Ventas 2005 y Ventas 2006 excluyendo la Región, deberíamos armar a priori los títulos de los campos que van a formar dicha sub base de datos:
Aquí marcamos en el cuadro, en el renglón Copiar a: F11:H11 (celdas donde se encuentran los títulos de la sub base de datos que se va a armar con los datos filtrados)
A continuación vemos el resultado final de dicho filtro:
Tablas
Dinámicas
Utilizando la base de datos del ejemplo anterior generaremos nuestra primera tabla dinámica. Esta herramienta nos permitirá cruzar de diversas formas información de una base de datos, tanto sea una base armada en Excel, como también en fuentes de datos externas (como archivos de texto, bases de Access, SQL, DBase, etc.)
Para comenzar nos guiaremos mediante el Asistente, que encontramos en Datos – Informe de Tablas y Gráficos Dinámicos, como vemos en la siguiente pantalla:
En la primera pantalla del asistente tendremos las opciones para elegir el origen de los datos que se van a tomar en la tabla dinámica, y en dicha pantalla en la parte inferior elegimos el tipo de informe a obtener, y que puede ser Tabla Dinámica o Gráfico Dinámico:
En esta primera pantalla nosotros elegimos las opciones que vienen como predeterminadas ya que la base de datos nuestra proviene de una lista que armamos previamente en Excel y el informe de salida es efectivamente una tabla dinámica.
Presionamos en el botón siguiente para pasar a la segunda pantalla:
El Rango es el que se forma por toda nuestra base de datos, recordar que en el caso de querer tomar solo determinados campos, mediante la tecla control presionada podemos marcar solo dichos campos para que formen parte de nuestra tabla dinámica.
En esta tercera pantalla del asistente, podremos elegir la ubicación de nuestra tabla dinámica, la cual podemos establecer en una hoja de cálculo nueva, o también en una hoja de cálculo existente. Tomemos en cuenta que en el caso de que marquemos hoja de cálculo existente, debemos marcar una celda que formará el ángulo superior izquierdo de nuestra tabla dinámica por lo tanto tener presente que desde esta celda hacia abajo y hacia la derecha exista lugar vacío y disponible en dicha planilla de cálculo.
Veamos el resultado que obtenemos al dar finalizar (en este caso seleccionamos la opción de volcar la tabla dinámica en una hoja nueva):
Toda tabla dinámica se formará por 4 cuadrantes que serán:
• Campos de Fila
• Campos de Columna
• Campos de Datos
Podremos arrastrar cualquiera de nuestros campos de la base de datos que tenemos en la lista que aparece a la derecha hacia el área de nuestra tabla dinámica donde queramos disponer dicho campo.
Es sumamente importante tener presente en un principio cual es el resultado que se desea obtener, para no perdernos entre tantas opciones que tenemos en los campos que tenemos en la base de datos.
En este primer ejemplo vamos a pedir el total de ventas del 2005 para cada uno de los vendedores:
Aquí lo que hicimos fue arrastrar (con el botón izquierdo presionado) el campo de vendedores al área de Fila (vemos que cada vendedor aparece en una fila diferente), y llevamos el campo de Ventas 2005 al área de Datos (vemos que las ventas de 2005 aparecen cada una en dicha área).
Hay que tener presente que Excel es realmente sensible respecto a cada una de estas áreas por lo tanto tratemos siempre de trasladar cada campo hacia el área correspondiente en la forma mas exacta posible.
En caso de que cambiemos de opinión y nos parezca mejor obtener el total de ventas 2005 pero de cada región y no de cada vendedor puedo trasladar el campo de vendedores de regreso a la Lista de campos de tabla dinámica, y trasladar a continuación, el campo de Región nuevamente al área de fila:
Aquí vemos que quitamos el campo de vendedores, y ahora vamos a colocar el campo de Región, solo volvemos a arrastrar dicho campo al área de filas y nos queda como vemos a continuación:
En dicho ejemplo vemos que lo que logramos en este último caso es totalizar las ventas del 2005 para cada una de las regiones.
En este caso trasladamos el campo de Vendedores al área de FILA y el campo de Regiones al área de COLUMNAS. El campo de ventas 2005 quedó dispuesto como antes en el área de DATOS.
FILTROS EN TABLAS DINÁMICAS
También tenemos la opción de aplicar filtros en la tabla dinámica, mediante el cuadro desplegable de los títulos del campo fila (en este caso vendedores) y también en el título del campo de
columna (en este caso Región).
En este caso al igual que como hacíamos en el autofiltro, elegimos los criterios de filtrado que vamos a aplicar sobre dicho campo. En el caso anterior filtramos para que solo despliegue las ventas de los vendedores Morales y Vega, que son los que dejamos tildados en el cuadro desplegable.
Si quisiéramos obtener además de las ventas del 2005, también las del 2006, simplemente agregamos este último campo en el área de datos:
Vemos que aquí obtenemos dos campos en el área de datos, y tenemos los totales tanto por vendedor como también por región de los años 2005 y 2006.
Podemos aplicar también un filtro que nos muestre por ejemplo determinada región (SUR por ejemplo):
Cada filtro aplicado se refleja inmediatamente en el resultado de nuestra tabla dinámica.
ACTUALIZACIONES EN TABLAS DINÁMICAS
Apliquemos en principio el primer tipo de actualización que se puede dar:
‐ Cambiando datos en la base de dato original. Tomemos algún dato de la base de datos original y modifiquemos este dato sustancialmente para que veamos reflejado este cambio en la tabla dinámica.
Veamos que en este caso se modificó el primer registro de la base de datos, sin embargo cuando regresamos a la tabla dinámica, esta permanece exactamente igual que como estaba antes de realizar el cambio.
Para que el cambio se realice y se vea reflejado en la tabla dinámica deberemos dar clic en el botón de actualizar (botón con el signo de !) en la barra de herramientas de tabla dinámica.
En caso de no tener dicho botón disponible podremos recurrir al menú VER – BARRA DE HERRAMIENTAS – TABLAS DINÁMICAS, y luego dar clic sobre este botón pare ver el cambio realizado:
Al presionar sobre el botón vemos que la tabla dinámica recalcula los valores y nos muestra el detalle que vemos a continuación:
Hay un tema muy importante a destacar, y es el hecho de que la tabla dinámica no es sensible a la incorporación de nuevos registros (nuevas líneas) en la base de datos original, así como a la inserción de columnas en la base de datos original.
A continuación haremos referencia al concepto y metodología a manejar cuando agregamos nuevos registros en la base de datos original.
ACTUALIZACIÓN DE TABLAS DINÁMICAS (CON INSERCIÓN DE DATOS EN LA BASE DE DATOS ORIGINAL)
En este ejemplo vamos a agregar una línea adicional en la base de datos, haremos referencia a una venta en una región que hasta ahora no habíamos cargado:
El último registro fue agregado en la base de datos, sin embargo cuando regresamos a la tabla dinámica, ésta no refleja el cambio que realizamos en la base de datos. Pero nos encontramos que presionando el botón de actualizar en la barra de herramientas de tabla dinámica tampoco nos refleja el cambio.
Para que el cambio se refleje en nuestra tabla dinámica realizaremos el siguiente procedimiento:
1. Vamos a la barra de herramientas tabla dinámica, mediante el menú VER – BARRA DE HERRAMIENTAS – TABLA DINÁMICA
2. Una vez que tenemos la barra de herramientas disponible, vamos al botón Tabla Dinámica, y allí elegimos Asistente:
Cuando presionamos en la opción de asistente, este nos despliega la última pantalla del asistente de tablas dinámicas, al presionar el botón de atrás vemos que nos permite redimensionar la base de datos original:
En este caso marcaremos el rango A1:D20, agregando de esta manera el nuevo registro a la base de datos, luego presionamos en el botón siguiente, pasando a la última pantalla del asistente y por último finalizar.
Vemos a continuación la imagen de nuestra tabla dinámica actualizada mediante el asistente de tablas dinámicas:
Agreguemos a continuación en nuestra base de datos original una nueva columna que nos refleje el país a donde se realizó cada una de las transacciones con lo que la base de datos quedará de la siguiente manera:
Al presionar el botón de actualizar en la barra de herramientas de tabla dinámica, nos aparece en la lista de campos, el nuevo campo PAÍS que podremos utilizar para agregar en la tabla dinámica:
Agreguemos dicho campo en el área de Página y veamos el efecto que resulta de esto:
Al agregar dicho campo en el área de página podremos resumir la tabla dinámica de acuerdo a dicho campo. En el siguiente caso, al seleccionar un país (por ejemplo Argentina) veamos el efecto que produce sobre los datos de nuestra tabla dinámica:
La tabla dinámica pasa ahora a mostrar las ventas de cada vendedor de los años 2005 y 2006, también con la posibilidad de ver la división por región pero UNICAMENTE DEL PAÍS ARGENTINA (que actúa como filtro de nuestra tabla dinámica).
CAMBIOS SOBRE LAS OPERACIONES DE CAMPOS DE DATOS
En este caso seguiremos trabajando con la base de datos de ejemplo pero vamos a aplicar otra operación sobre el campo de ventas 2005, en lugar de pedir el total de ventas 2005 (suma de ventas 2005) solicitaremos el promedio de ventas 2005.
Para simplificar solo trabajaremos con los campos de vendedores y ventas 2005:
Aquí por ahora solo vemos la operación simple donde nos muestra la suma de ventas 2005, veamos como hacemos para cambiar la operación predeterminada del campo de datos:
1. Nos posicionamos sobre cualquiera de los datos, de importes de nuestra tabla dinámica y presionamos el botón derecho del Mouse, y allí seleccionamos la opción de
CONFIGURACIÓN DE CAMPO
2. En la pantalla que nos aparece podremos seleccionar el tipo de operación que aplicaremos sobre dicho campo:
Vemos que podemos aplicar como operación la SUMA, CUENTA, PROMEDIO, MÁXIMO, MÍNIMO, PRODUCTO…
En nuestro caso vamos a seleccionar como opción PROMEDIO, para que nos aplique dicha operación sobre el campo de Ventas 2005.
También podremos en dicho cuadro, sobre la línea de Nombre, ajustar el nombre a reflejar de dicho campo.
Tenemos la opción también de agregar el mismo campo un sin numero de veces para que este refleje operaciones diferentes.
Por ejemplo, si necesito que la tabla dinámica refleje la suma de ventas 2005 pero además, nos provea del promedio de ventas 2005 para cada vendedor. Arrastramos dos veces el campo de ventas 2005 hacia el área de datos, y simplemente a uno de los campos le cambiamos la configuración de campo:
También cuando realizamos la configuración de campo podremos tener mas opciones, como por ejemplo desplegar el % de ponderación de cada venta, con respecto al total de ventas del año como podemos ver a continuación:
En la pantalla de configuración de campo:
La tabla dinámica nos mostrará el siguiente resultado:
No olvidemos que podríamos presentar los importes de ventas 2005, y luego arrastrar
nuevamente el campo para realizar esta configuración para que además de los importes refleje también los porcentajes de ventas.
TABLAS DINÁMICAS CON IMPORTACION DE DATOS DESDE ACCESS
Veamos en un ejemplo los pasos que debemos cumplir si queremos armar una tabla dinámica para analizar información de una base de datos que la empresa tiene en Microsoft Access.
Comenzamos el asistente de la misma manera que lo hicimos antes, mediante el menú de Datos – Informe de Tablas y Gráficos dinámicos
En la primera pantalla del asistente, elegimos como opción FUENTE DE DATOS EXTERNA:
Al presionar el botón siguiente, vemos que el asistente cambia con respecto al que veíamos en el capítulo anterior:
Seleccionamos en este caso MS Access Database, que es el software donde tenemos la base de datos, y al dar aceptar, podremos recorrer las carpetas hasta encontrar la base de datos, en este caso recorremos C:\Archivos de Programa\Microsoft Office\Office11\Samples
Cuando seleccionamos la base de datos, en este caso NEPTUNO, nos aparece la siguiente pantalla:
podremos presionar en el signo + para desplegar los campos de los que está compuesta la tabla. En el siguiente ejemplo tomaremos determinados campos de las tablas de Clientes, Productos, Categorías, y Productos.
Cada uno de los campos que queramos incorporar en la tabla dinámica lo pasaremos al área derecha de la pantalla, en el área de columnas en la consulta.
Cuando presionamos el botón siguiente:
En esta pantalla tendremos la posibilidad de filtrar la información de los campos de acuerdo a uno o varios criterios que seleccionemos.
En la siguiente pantalla podremos seleccionar un orden determinado a establecer, de acuerdo a un campo que seleccionemos, en forma ascendente o descendente.
En la última pantalla seleccionamos la opción que aparece en forma predeterminada, y devolvemos los datos directamente a Microsoft Excel, y le damos siguiente en la pantalla del asistente:
Llegamos en esta etapa final, a la misma pantalla, que en el capítulo anterior donde tendremos el esqueleto de la tabla dinámica:
Aquí podremos armar la tabla dinámica simplemente ajustando los campos y llevándolos al área de fila, área de columnas, y al área de datos.
Recordemos que desde aquí podemos lograr lo mismo que ya realizamos en el capítulo anterior con datos de una base de datos de Excel:
GRAFICOS DINÁMICOS
Para generar una tabla dinámica, y a partir de la misma, reflejar un gráfico dinámico, simplemente nos posicionamos en cualquiera de los campos de la tabla dinámica y mediante el botón derecho del Mouse tendremos la opción de generar el gráfico.
Veamos en el siguiente ejemplo:
Mediante la opción de Gráfico dinámico, este nos generará en una nueva hoja el gráfico dinámico que se desprende de la tabla dinámica generada anteriormente.
Dentro del Gráfico dinámico podremos ajustar y cambiar de posición cualquiera de los campos, pero siempre tomando en cuenta que al cambiar el gráfico dinámico, también cambiará en forma simultanea la tabla dinámica de la cual depende.
El Gráfico dinámico tiene a su vez 4 áreas diferenciadas, y son:
1. Campos de categorías (en este caso vendedores) 2. Campos de serie (en este caso no tenemos)
3. Campos de página (en este caso no incluimos ninguno aún) 4. Campos de datos (en este caso el campo Ventas 2005)
En el siguiente ejemplo vemos el caso de tener un campo en cada posición:
MÓDULO
GRÁFICOS
PERSONALIZADOS
Y
Módulo de Gráficos Personalizados y Formularios
Dentro de este módulo se verán diferentes opciones al momento de graficar en Excel así como opciones de gráficos poco utilizadas, y casos prácticos donde apliquemos usos de gráficos para diferentes actividades.
Comencemos con un gráfico sencillo en el que se pretenda gráficas exportaciones de un producto a diversos países a través de 4 años.
Nos basaremos en la siguiente planilla:
Para comenzar nos posicionamos en cualquiera de los datos de nuestra planilla y vamos al asistente para gráficos mediante el menú Insertar – Gráfico
En la primera pantalla que nos despliega el asistente para gráficos tendremos la posibilidad de seleccionar la categoría del gráfico que generaremos y dentro de cada categoría también
Veamos que tenemos un botón de Presionar para ver muestra, que, dependiendo en que categoría nos posicionamos nos va a dar una imagen preliminar del gráfico que generaremos a través de la lista o base de datos que tenemos seleccionada.
Al presionar siguiente, y pasando al segundo paso del asistente:
Aquí se destacan dos puntos, en primer lugar, el hecho de que Excel tomó en forma automática como Rango de datos desde A1:E6, incluyendo aquí los títulos de ambos campos, pero tomando la fila 1 como si fuera una serie a graficar, y esto nos genera un error, ya que aparecerá una serie que no contiene información (veamos en la muestra).
En este caso corrigiendo el área a graficar y tomando desde la fila 2, o sea marcando como Rango de datos de origen desde A2:E6, nos quedará la muestra correspondiente al área que
efectivamente debemos graficar.
Si vemos las opciones de graficar las series en: FILAS, COLUMNAS, en forma predeterminada Excel marca las serien como columnas, y por lo tanto cada año formará una serie de nuestro gráfico, en cambio si intercambiamos y pasamos a la opción de Series en FILAS, se verá de la siguiente manera:
En este caso lo que estamos representando son las ventas de cada uno de los años (1, 2, 3,4) y dentro de cada año las series están representadas por las exportaciones a cada país.
A su vez si damos clic en la solapa de SERIE en esta misma pantalla veremos lo siguiente:
En esta pantalla podremos definir:
• Nombre: El rango (o celda) donde encontramos el nombre de cada serie (en este caso de cada país)
• Valores: El rango de celdas donde encontramos los valores correspondientes a dicha serie (valores de exportaciones a través de los años de dicho país)
• Rótulos del eje de categorías (X): Aquí vamos a señalar el rango B1:E1 que es el rango donde encontramos los títulos de las categorías (en este caso los años)
Presionamos en siguiente y pasamos al 3er paso del asistente:
Aquí completamos en la solapa de Títulos los títulos de cada sector del gráfico, en primer lugar el Título General del gráfico, luego completamos el Título de Eje X, y por último el titulo de Eje Y.
Pasamos a la solapa de EJE:
