EJERCICIOS
Esta es un recopilación de ejercicios originales de los niveles básico, medio y avanzado
NIVEL BASICO
Ejercicio 1:
1. Dada la factura del comercio " ELEGANT" completar los espacios vacíos
Utilizar FORMULAS y la función SUMA
2. Completar los espacios utilizando la función SUMAPRODUCTO
Ejercicio 2
1. Mejorar el aspecto y funcionalidad de la factura del comercio "ELEGANT" ,
incorporando la fecha actual, un slogan y un logotipo. Utilizar la función HOY y ver
el tutorial INSERCION DE IMAGENES
NIVEL MEDIO
Ejercicio 1:
Un comercio dispone de la siguiente tabla con las ventas del mes Enero de sus
empleados correspondientes a las sucursales A y B
se quiere saber:
1. La cantidad de empleados de cada sucursal (Función [Link])
2. La cantidad total de empleados. Usar la función CONTARA
3. La cantidad total vendida (función SUMA)
4. ¿Cuál fue la mayor venta ( función MAX )
5. ¿Cuál fue la menor venta ( función MIN )
6. El promedio de ventas de ambas sucursales ( función PROMEDIO)
7. El promedio de ventas de la sucursal A.
8. El promedio de ventas de la sucursal B.
9. ¿Cuál fue la máxima venta de la sucursal A?
10. ¿Cual fue la máxima venta de la sucursal B? Para los puntos 7,8,9 y 10 usar
FORMULAS MATRICIALES
Ejercicio 2:
Empleando la misma tabla que en el ejercicio anterior, averiguar
1. Cuánto vendieron los empleados de la sucursal A, ( función SUMAR,SI)
2. Cuánto vendieron los empleados de la sucursal B.
Ejercicio 3:
Con la tabla del ejercicio 1 se quiere saber
1. Cuál fue el empleado que vendió por $ 24.000.
2. Cuál fue el empleado que vendió mas de ambas sucursales.
3. Cuál fue el empleado que vendió mas de la sucursal A, y cual fue el monto
4. Cuál fue el empleado que vendió mas de la sucursal B, y cual fue el monto
Ejercicio 4:
Con la tabla del ejercicio 1:
1. Hacer un gráfico de barras que represente las ventas que hicieron en ambas
sucursales. Ponerle el título " Ventas de sucursales mes de Enero".
2. Hacer un gráfico de barras que represente las ventas que hicieron los empleados
de ambas sucursales. Ponerle el título " Ventas de empleados mes de Enero".
3. Imprimir el documento.
Ejercicio 5 :
Considerando el mismo comercio de los ejercicios anteriores
El negocio está prosperando y se ve obligado a contratar nuevos empleados durante el
mes de Enero(se pueden colocar los nuevo empleados en cualquier lugar de la tabla).
Se pide:
1. Hacer los mismos cálculos del ejercicio 1, pero teniendo en cuenta esta nueva
circunstancia( Rangos variables)
2. Hacer los gráficos correspondientes( Gráficos con rangos variables)
Ejercicio 6
1. A cuales de las preguntas de los Ejercicios 1, 2, 3, 4 y 5 se pueden responder
usando Tablas dinámicas
2. Responder las respuestas del punto 1 empleando Tablas Dinámicas.
Ejercicio 7
Mejorar la funcionalidad de la factura del comercio "ELEGANT".
1. Incorporando el Nº de factura.
2. Haciendo que los campos de la misma sean variables.
SUMAPRODUCTO
Si en una Hoja de Excel tenemos las tablas A (con borde rojo) y B (con borde verde),
las cuales tienen el mismo nùmero de filas y de columnas, podemos definir celdas que
ocupan la misma posición relativa respecto de A y B, a estas celdas se las denomina
"celdas correspondientes". Por ejemplo en la figura
las celdas C5 y G5 son correspondientes.
Ahora estamos en condiciones de definir la función SUMAPRODUCTO.
La función SUMA PRODUCTO multiplica el contenido de las celdas correspondientes
de hasta 30 tablas y devuelve la suma de esos productos.
La sintaxis de SUMAPRODUCTO es:
SUMAPRODUCTO(taba1; tabla2; tabla3;.......)
En la figura de arriba tenemos un ejemplo con 2 tablas. Notar que hubiéramos llegado
al mismo resultado con la función SUMA usando como argumentos los productos de las
celdas correspondientes
Si en el argumento de SUMAPRODUCTO hay una sola tabla, el resultado es la suma de
los elementos de ella
FUNCION [Link]
Esta función es una combinación de las funciónes CONTAR y SI , tiene dos
argumentos, el primero es el rango cuyas celdas se desean contar y el segundo es el
criterio que determina que celda sera contada o no
con esta misma tabla podríamos preguntar cuántos hombres hay
FUNCION CONTARA
Cuenta todas las celdas que no están vacías de un rango, veamos este ejemplo
en este caso el rango C1:D7 tiene 12 celdas pero como CONTARA no cuenta la vacía, el
resultado de la función, que está en la celda C9 es 11.
FORMULAS MATRICIALES
INTRODUCCION:
Con las fórmulas matriciales se pueden hacer muchas cosas, es una herramienta de gran
potencia, en general estas fórmulas o funciones se usan para hacer 2 tipos de cosas.:
1. Ejecutar varias operaciónes y devolver un único valor en la celda donde se la
introduce.
2. Ejecutar varias operaciónes y devolver múltiples valores en distintas celdas.
Las fórmulas matriciales actúan en 2 o mas rangos de valores, los que se denominan,
argumentos matriciales, los cuales tienen la característica de tener el mismo número de
filas y de columnas, por ejemplo, podrían actuar sobre los rangos A1:A12 y BI:B12.
Una fórmula matricial se introduce de la misma forma que la fórmula común, la
diferencia es que luego de introducirla hay que apretar las teclas Control+shift+ENTER,
con lo que automáticamente es rodeada por llaves y es por eso que se las conoce como fórmulas CSE. Para una
formula matricial multiplicar 2 argumentos matriciales, como A1:A12 *BI:B12. significa multiplicar
las
celdas A1*B1, A2*B2, A3*B3......A12*B12 si quiero sumar estos resultados parciales uso
la formula matricial {SUMA(A1:A12*B1:B12)}, para aclarar los conceptos vamos a
tener que hacer mas de un ejemplo, Empecemos por un ejemplo del tipo 1-.
El dueño de una mueblería quiere aumentar la variedad de los productos que
vende para lo que decide comprara, parte de los tradicionales, muebles de computación,
para lo que cuenta con la siguiente planilla
y quiere saber cuanto tiene que gastar. Decide tomar el camino corto y usa una simple
fórmula matricial, veamos lo que hizo
se ve que introdujo la fórmula matricial
de esta forma hizo 3 pasos en uno. Los 3 pasos hubieran sido:
1- Introducir la fórmula =D2*E2 en la celda F2.
2- Arrastrar esta fórmula hasta la celda F7
3- Ubicarnos en la celda F8 y pulsar el icono
o ubicarnos en la celda D9( por ejemplo ) e introducir la función
=SUMA(D2*E2;D3*E3;D4*E4;D5*E5;D6*E6;D7*E7)
se ve que ambas maneras, si bien dan el mismo resultado, son mucho mas tediosas
Se puede aprovechar este mismo ejemplo para mostrar como usar las fórmulas
matriciales que devuelven múltiples valores y así explicamos todo el [Link] la
misma tabla que al principio vamos a obtener todos los productos parciales
1º seleccionamos la columna donde queremos que aparezcan los valores
2º introducimos la fórmula, seleccionando los rangos D2:D7 y E2:E7 y
multiplicándolos
3º apretamos las teclas Control+shift+ENTER
y obteniendo los productos parciales y por lo tanto múltiples resultados como se ve en el
recuadro rojo.
FUNCION [Link]
INTRODUCCION
La función [Link] permite sumar valores de un rango de acuerdo a un criterio o
condición.
La función [Link] tiene 3 parámetros:
El primero es la referencia o el rango que contiene los valore sobre los que se evaluará
la condición.
El segundo es el que contiene el criterio a aplicar con el objeto de determinar que se
suma y que no
El tercero es opcional, esto quiere decir que si la condición esta en el mismo rango
donde se efectúa la suma, no hace falta el tercer parámetro, pero si el criterio esta en un
rango y donde se hace la suma en otro (u otros )rangos, entonces tiene que colocarse el
tercer parametro.
Para aclarar las cosas que mejor que un ejemplo: Supongamos que una inmobiliaria
tiene un listado con el valor de las propiedades que se vendieron en Enero y quiere saber
la suma de aquellas que superaron los $160.000, para obtener la respuesta se emplea la
función [Link] como se muestra en el gráfico
En este caso con dos parámetros alcanza puesto que el criterio esta en la rango E2:E5,
que el mismo rango donde se efectúa la suma con la condición dada y no hace falta
poner =SUMA(E2:E5;">160000";E2:E5)..Si en cambio tenemos esta otra tabla
aquí si hace falta el tercer parámetro ya que el rango donde se efectúa el criterio
(D2:D5) no es el mismo que el rango donde se efectúa la suma (E2:E5).
Dejo como ejercicio averiguar las comisiónes que se cobran al vendedor por propiedades
cuyo costo es inferior a $ 400.000.
UN EJEMPLO SENCILLO DE RANGO
VARIABLE
Voy a dar un ejemplo sencillo de referencia dinámica, también llamada rango variable.
Suponganos que en una familia se anotan los gastos diarios confeccionando la siguiente
tabla en Excel
una forma de calcular los subtotales, por ejemplo hasta el día 4, sería emplear la función
SUMA con el rango fijo C2:C5 , pero si al día 5 queremos ingresar otro dato, este no es
tomado hasta que no actualicemos el rango a C2:C6, se entiende que es muy poco
práctico hacer esto toda vez que queramos ingresar un valor, lo que necesitamos es un
rango que varíe en forma automática o sea un rango variable. Para hacer que nuestro
rango se actualice usaremos la función CONTAR anidada con DESREF dentro de la
función SUMA . Como puede verse, estamos ante el caso particular de una columna
donde el rango debe alargarse(cambiar de alto) y por lotanto al usar DESREF solo nos
hacen falta 2 parámetros; el parametro de partida C2 y alto, en los parametros de fila y
columna( que son obligatorios) se pone cero o ""(blanco) y elparametro ancho ( que no
es obligatorio ) se omite. Todo el truco está en hacer que alto se expanda hacia abajo y
para eso lo reemplazamos con la función CONTAR , que cuenta las celdas que no estan
vacías, por lo tanto siempre nos pondrá el valor correcto en "alto" y finalmente nuestra
formula queda
en CONTAR seleccionamos toda la columna C poniendo C:C o pulsando en
y nuestra tabla queda finalmente asi
Si al día 11 se gasto tanto, no quiero ni pensar lo que va a ser al día 31!!
Les dejo un archivo para que comprueben como funciona
Como se puede ver, la actualizacion se produce no solo al agregar un valor al final de la
lista sino tambien al cambiar un valor intermedio.
GRAFICOS CON RANGOS VARIABLES
Los gráficos son una gran herramienta para la visualización de datos, sería bueno que
estos gráficos se pudieran actualizar automáticamente, a medida que se van agregando
más datos a la tabla en la cual están basados dichos gráficos, ya que de lo contrario
habría que hacerlo manualmente.
Vamos a utilizar una tabla por ustedes conocida de tutoriales anteriores:
Podríamos fácilmente hacer un gráfico que represente los gastos hasta el día 10
pero a no ser que actualicemos los rangos, el día 11no quedaría representado en el
gráfico. Sería mucho mas práctico que los rangos se actualizaran automáticamente.
Para hacer esto vamos a crear 1 nombre como lo hicimos en el tutorial RANGO
VARIABLE UTILIZANDO NOMBRES , en este caso crearemos el nombre GASTOS
(podríamos haber elegido cualquier nombre) para la columna que representa a los
valores en el eje que queremos que se actualice su rango, para esto utilizaremos las
fórmula
=DESREF(Hoja1!$B$2;0;0;CONTAR(Hoja1!$B:$B))
que introducimos pulsando en la pestaña "Fórmula" y luego en" Nombre Nuevo"
donde ponemos la fórmula en "Hace referencia a"
y ya tenemos definido el nombre GASTOS. A continuación seleccionamos cualquier
barra del gráfico y veremos que aparece la función SERIES, en la barra de fórmulas
solo nos falta reemplazar el nombre "GASTOS" en la referencia marcada en rojo
o sea que reemplazamos los rangos del gráfico estático de la columna GASTOS por el
nombre que hace dinámica a esta columna. Como se ve no hace falta poner un nombre
para los rangos de la columna Nº1(DIA) como lo hubiéramos tenido que hacer en
versiones anteriores a la [Link] esto hemos terminado y ahora si el día 11 y todos los
que agreguemos de aquí en mas, quedaran representados en el gráfico, como se puede
ver
No es que lo anterior sea demasiado comoplicado, pero con las versiónes de Excel 2007 y
Excel 2003 se pueden actualizar gráficos de forma mucho mas sencilla, veamos:
Excel 2007
Una vez confeccionado nuestro gráfico estático, seleccionamos otra vez la tabla de datos,
luego vamos a la pestaña Insertar y después pulsamos en tabla
y luego de aceptar en siguiente panel
ya está nuestro gráfico actualizable. Notar que Excel 2007 por defecto tilda la casilla de
encabezados, por lo que si no los hubiera, tendríamos que destildarla..
Excel 2003
En Excel 2003 seleccionamos cualquier celda de la tabla de datos, digamos la B3, luego
vamos al menú Datos->Lista->Crear lista
luego aparece panel "crear lista "( en Excel 2007 era crear tabla),
si todos los datos están bien aceptamos y ya está creado nuestro gráfico que toma los
datos de una lista que se puede agrandar o achicar, según sea el caso de que quitemos o
agreguemos valores
en la figura se ve la lista bordeada por un color azul y un asterisco, también azul, que
indica que podemos agregar un valor en esa fila. Como verán el tema de los gráficos con
rangos variables se ha simplificado mucho en Excel 2007 y Excel 2003.
TABLAS DINAMICAS
INTRODUCCIÓN:
Las Tablas Dinámicas son una forma alternativa de presentar o resumir los datos de una
lista, es decir, una forma de ver los datos desde puntos de vista diferentes.
El nombre Tabla Dinámica se debe a que los encabezados de fila y columna de la lista
pueden cambiar de posición y también pueden ser filtrados.
Con las Tablas Dinámicas también podremos preparar los datos para ser utilizados en la
confección de gráficos.
La comprensión cabal de este tema se obtiene con la práctica y es así como se verá que
es uno de los tópicos mas potentes de Excel, principalmente en las versiones mas
recientes.
Empezaremos con un ejemplo:
Una empresa de exportación de máquinas agrícolas tiene la siguiente tabla en una Hoja
de Excel [Link] figuran los datos del 1º trimestre del año.
a partir de ella se quiere crear una nueva tabla en la que se informe la cantidad de
maquinarias exportadas y el detalle de cuantas se vendieron de cada una.
Para crear la tabla que nos responda a estas preguntas, nos ubicamos en cualquier celda
de la tabla, luego vamos a la pestaña "insertar" panel "Tablas"
en el que pulsamos en "Tabla dinámica", al hacer esto aparece un menú en el que
tenemos las opciones de "Gráfico dinámico" y "Tabla dinámica" , pulsaremos en este
último como se puede ver
a continuación aparece el panel "Crear tabla dinámica"
en este caso el rango de la tabla base (tabla de partida) queda automáticamente
seleccionado, incuyendo los rótulos, también seleccionamos "Nueva hoja de cálculo" y
pulsamos en aceptar y automáticamente se crea la Hoja4 en la que se destacan:
1. Una nueva cinta de opciones denominada "Herramienta de tabla dinámica"
2. Un panel llamado "Lista de campos de tabla dinámica" que es una novedad de
Excel 2007 y que tiene un rectángulo en la parte superior, donde se ubican los campos
o rótulos de la tabla de origen, también hay cuatro rectángulos, en la parte inferior,
denominados " Filtro de informe", "Rótulos de columna", "Rótulos de fila" y
"Valores" donde irán apareciendo los rótulos de la tabla a medida que los
seleccionemos en la parte suprior en forma de botones como el que se nuestra
Los botones se pueden arrastrar de un rectángulo a otro aunque los rótulos que tienen
valores numéricos, siempre aparecen en rectángulo "Valores".
como se ve, hasta este momento, tiene las casillas de verificación de rótulos sin marcar ,
pues bien, es justamente seleccionar las casillas "MAQUINA" y "CANTIDAD" lo
debemos hacer en el próximo paso
Observar que aparecen automáticamente 2 botones.
Listo ya tenemos la primera tabla con las respuestas pedidas recuadradas en rojo
si nos interesara saber solamente el dato de cuantas fertilizadora y sembradora se
exportaron, junto con su total, tenemos un comando de filtrado en la parte superior y así
obtenemos la siguiente tabla
luego de aplicar el filtro.
Este es un ejemplo didáctico para hacer una introducción, pero se le puede sacar mucho
mas el jugo a esta herramienta.
Podemos querer saber el detalle de las máquinas que fueron exportadas y por cual
vendedor. En este caso tendremos que seleccionar la casilla del rótulo VENDEDOR y en
la nueva Hoja aparece una tabla y el panel "Lista de campos de tabla dinámica"
se ve que en los rectángulos, mas precisamente en el llamado Rótulo de fila, aparece un
nuevo botón, el botón VENDEDOR en forma simultánea a la la selección de la casilla
de verificación VENDEDOR.
La tabla responde a lo que queremos saber, pero le podemos dar otro aspecto
arrastrando el botón VENDEDOR al rectángulo "Rótulo de columna"
y la tabla queda como la que esta abajo , luego de haberle dado algo de formato
En esta tabla se puede ver, por ejemplo, que Peña vendió 16 fertilizadoras y un tractor.
Sería interesante saber el número de maquinarias exportadas a que país y por cual
vendedor.
Para hacer lo , verificamos la casilla "País" y filtramos MAQUINA
y se genera la tabla
donde se puede ver que Chuan le vendió 30 máquinas a China.
Hasta ahora nuestra tabla dinámica efectúa sumas, pero puede hacer otras operaciones
tales como porcentajes, máximos, mínimo y otras mas que iremos viendo.
Podemos preguntarnos cual fue la máxima cantidad de maquinarias que vendió Peña.
Para hacer esto nos ubicamos en una celda cualquiera de la tabla de arriba y apretando
el botón derecho del mouse aparece el siguiente menú emergente
en el que pulsamos en "Configuración de campo de valor", como indica la flecha, con lo
que aparece el panel también llamado "Configuración de campo de valor"
en el que si vamos a la pestaña "Resumen por" están las opciones de resumen en las
que elegiremos Máx y luego de aceptar, la tabla se transforma en
que nos dice que la cantidad Máxima de maquinarias que vendió Peña es 9, como se ve
en el recuadro rojo, en forma adicional podemos ver que esta cantidad fue vendida a
Brasil ( verificar con la tabla de partida o tabla base)
Este resultado se puede ver con una simple inspección de los datos, que en este caso son
tres, pero cuando estos aumentan es donde vemos la utilidad del cálculo de un máximo.
TABLAS DINAMICAS CON RANGOS DINAMICOS
Una aplicación de los RANGOS VARIABLES, es cuando trabajamos con TABLAS DINAMICAS, ya
que podemos agregar o quitar elementos de la tabla origen de datos (tabla base)sin necesidad de
actualizar la referencia al rango en forma manual, o sea que se hace en forma automática. Para hacer
esto vamos a utilizas NOMBRES , pero no le vamos a dar un nombre a un rango, le daremos un
nombre a una fórmula ( Excel considera a las fórmulas como si fueran rangos)dicha fórmula sera el
ANIDAMIENTO entre las funciones DESREF Y CONTARA
como ya se vio, la sintaxis de DESREF es
DESREF(referencia ;filas;columnas;alto;ancho)
donde los argumentos serán:
referencia: la celda en el ángulo superior izquierdo de la tabla base (A1 si consideramos la tabla del
tutorial TABLAS DINAMICAS
filas:para este caso es 0
columnas: para este caso es 0
alto: la cantidad de filas en nuestra tabla base
ancho: la cantidad de columnas en nuestra tabla base
Esta fórmula se anidara con CONTARA para que DEREF se transforme en dinámica quedando
=DESREF(Hoja1!$A$1,0,0,CONTARA(Hoja1!$A:$A),CONTARA(Hoja1!$1:$1))
a esta fórmula le daremos el nombre tabla_base_dinamica
con este nombre creamos la tabla dinámica
en este momento ya estamos en condiciones de agregar datos a la tabla base original
si con ella hubiéramos confeccionado la siguiente tabla dinámica
y agregamos otro país, por ejemplo Italia
esta misma tabla se actualiza incorporando este país
luego de ir al panel "DATOS" de la pestaña "OPCIONES" y pulsar en actualizar
PROMEDIO CON UNA CONDICION
Excel 2007 tiene una función que nos da el promedio de un conjunto de valores con una
condición, tal función se llama [Link], con las fórmulas matriciales podemos
hacer lo mismo. Haremos un ejemplo con ambas opciones, aclarando que estando la
función [Link], siempre es preferible usar esta alternativa debido a que utiliza
menos recursos de nuestro equipo.
Otra historia es para Excel 2003, puesto que en esta versión la única alternativa son las
fórmulas matriciales, siendo la razón que [Link] directamente no existe.
Supongamos que una fábrica de autos, lanzó un nuevo modelo en el mes de Enero y
quiere saber cual fue el promedio de ventas de los 3 primeros días del mes en cada una
de las zonas en las que esta divide al país. Las zonas son: Norte, Sur, Este, Oeste y
centro. Para lograr su objetivo se vuelcan los datos de las ventas de esos días en una
tabla, con un sector a la derecha para los resultados
la fórmula, para la zona Norte es:
en la figura se hace la comparación con la función [Link] y se ve que
coinciden
MAXIMO CON UNA CONDICION
Sería muy útil que Excel 2003/2007 tuviera la fórmula del tipo "máximo con una
condición", algo como [Link] o [Link] , pero si se fijan en el conjunto de
funciones de Excel, esta no aparece nada parecido, ni siquiera en Excel 2007, esta es
una mala noticia ya que, sin duda seria muy útil; hay muchos problemas que se podrían
resolver. Por suerte podemos resolver esta falencia con las FORMULAS
MATRICIALES.
Supongamos este problema:
Un grupo de alumnos rindio 3 materias, Matemática, física y química y quieren saber el
puntaje máximo que sacaron.
Los datos se podrían representar en una tabla como la siguiente
Para responder a la inquietud de los alumnos se vuelca la tabla en una Hoja de Excel
poniendo una tabla a la derecha para los resultados
donde la fórmula matricial usada es, por ejemplo para el alumno Marquez
como puede verse, es muy parecida a la del tutorial PROMEDIO CON UNA
CONDICION
PONER NOMBRES A RANGOS
En la Hoja de Excel hay una barra que se destaca y es común a todas las versiones, esta
es la barra que contiene el cuadro de nombres y la barra de fórmulas
en el cuadro de nombres, como puede verse, esta la referencia a la celda activa, que en
este caso es la A1, este es el nombre por defecto, pero podemos darle otro nombre
escribiéndolo en dicho cuadro y pulsando ENTER, teniendo el cuidado de no dejar
espacios.
De la misma manera podemos darle nombres a rangos, seleccionándolos primero, luego
dñandoles un nombre, en el cuadro de nombres y apretando ENTER.
En el caso de la figura de arriba Mi_rango es el nombre que se le dio al rango A1:B3, de
esta manera podremos darle un nombre a la cantidad de rangos que se nos ocurra, estos
aparecen en una lista desplegable pulsando en el triángulo negro apuntado por la flecha.
Esto que parece algo no muy
importante tiene su razón de ser, sino porque EXCEL se tomaría el trabajo de
brindarnos otras posibilidades de introducir nombres?. Una de ellas, en Excel 2007, es ir
a la pestaña de fórmulas y en la sección nombres definidos pulsar en asignar nombre aun
rango, como se muestra en la figura, en la que
también se ve el administrador de nombres que nos permite editar, borrar y agregar
nombres
Para utilizar los nombres que hemos definido en una fórmula podemos pulsar en utilizar
en la fórmula
Vamos a ver un ejemplo:
Un negocio que vende artículos de computación desea saber el porcentaje, sobre el volumen de total de ventas, de lo
que se vedió de un artículo en un día determinado, para esto cuenta con los datos en una Hoja de Excel y el problema
resuelto sin usar nombres
Para resolver el problema con nombres vamos a: asignar nombre aun rango +y en el menú emergente le damos el
nombre VENTAS, seleccionamos el rango B2:B7, lo introducimos en la casilla Hace referencia a y aceptamos
y ya estamos en condiciones de usar el nombre VENTAS, quedando nuestra fórmula como sigue
=B2*100/SUMA(VENTAS)
éste es un ejemplo sencillo, en donde los nombres no parecen ser muy útiles, pero hay problemas en los que las
fórmulas son muy complicadas y que incluso pueden tener referencias que están en otras hojas, pues bien, es aquí
donde los NOMBRES muestran toda su potencia.
ANIDAMIENTO DE FUNCIONES
INTRODUCCION IR A TUTORIALES
El anidamiento de funciónes junto con la programación VBA es lo que mas potencia da
al programa EXCEL, aquí todo depende de a donde nos pueda llevar nuestra habilidad e
imaginación y es donde se convierte en un programa muy versátil pudiendo abarcar
diversas disciplinas como son la Ingeniería, Estadística, Matemática, Finanzas,
Contabilidad por decir algunas que se me ocurren.
El anidamiento de funciónes no es otra cosa que ubicar una función en el argumento de
otra de forma adecuada, dicho así parece muy simple pero veremos que la cosa puede
complicarse mucho dado que la anidación pude hacerse en muchos niveles e involucrar
a muchas funciónes dando expresiones muy largas y difíciles de manejar, esto dista
mucho de querer desalentar, mas bien insta a la curiosidad y a la práctica.
Empezaremos por lo mas simple para ir a lo mas complejo en forma progresiva pero
antes voy a aclarar esto de los niveles y el límite que hay y la forma adecuada de hacerlo,
para esto ,como siempre nada mejor que un ejemplo
Se sabe que el promedio de las temperaturas del año en curso de la provincia de
Misiónes es de 27º y se tiene una tabla con los promedios de las temperaturas de los
meses del año anterior, se quiere saber si es verdadero que los 27º entran en el rango de
los promedios de los meses del año anterior
se ve que 27º no entra en rango de las temperatura promedio de los meses del año
anterior y que en la fórmula usada hemos anidado las funciónes MAX() y MIN() en dos
argumentos de una función Y() la que se denomina de primer nivel, siendo MAX() y
MIN() de segundo nivel ya que forman parte de los argumentos de Y(). MAX() y MIN()
están ubicadas correctamente pues forman parte de proposiciónes lógicas que son las
que aceptan los argumentos de Y().Por otra parte las funciónes se pueden anidar hasta
64 veces en Excel 2007 y solo 7 veces en Exel 2003 y versiones anteriores.
FUNCION DESREF
La función DESREF es tan útil como difícil de entender al principio.
DESREF devuelve una referencia a partir de otra que podemos llamar referencia de partida, vamos
a tratar de aclarar esto. Recordemos que una referencia es el código de una celda( A1;F3;H124,
etc) o el código de un rango de celdas(A3:G6;H5:K7;etc) y aquí pasan dos cosas distintas según se
trate de una celda o un rango de celdas; veamos:
Aquí se ve que si se trata de la referencia a una celda Excel devuelve el contenido de esa celda( la
fórmula está puesta en el recuadro negro) y en este caso DESREF funciona así
La referencia que devuelve( y por tanto su contenido) es el que resulta de ubicarse en la celda B2 y
desplazace x filas y luego x columnas. Concretamente una posibilidad podría ser
y esta expresión puesta en una hoja de Excel ( en la celda de partida B2) resulta en lo siguiente
y obtengo la referencia a una celda, que en este caso es la D5 y por lo tanto su contenido.
Hablando en forma simple: parto de B3 me desplazo 3 celdas hacia abajo, luego 2 celdas hacia la
derecha devuelve la referencia a la celda D5 y muestra su contenido.
Una aclaración: si me desplazo hacia arriba o a la izquierda tengo que anteponer el signo menos y
cuidar siempre de no salirme de los límites de la hoja porque sino da error, como podemos ver
la referencia está fuera de la hoja.
Cuando nuestra referencia de partida es un rango, la sintaxis de DESREF() cambia un poco
si dejamos los argumentos para celda en cero, partimos de C2:E7 y ponemos 9 para alto y 4 para
ancho
colocando la función con sus argumentos en una hoja de Excel
vemos que la referencia de partida, que está resaltada en rojo, se transforma en la referencia
C2:F10, resaltada en verde, es como si la referencia de partida se dilatara de C2:E7 a C2:F10, que
finalmente es lo que devuelve DESREF, pero como se ve nos da un error y es lógico que así sea pues
en este caso Excel no sabe que hacer ¿qué número va a devolver si tiene 3 opciónes?, lo mas
coherente es que haga algo con ellos, como sumarlos por ejemplo, pero DESREF por si sola no
puede y tiene que anidarse con otras funciónes como SUMA ( que sumaria 42+100+450=592), pero
también puede anidarse con PROMEDIO, MAX, MIN, etc y así se elimina el error, como vemos en la
siguiente tabla
Se preguntaran; tienen alguna función los argumentos de fila y columna?, la respueste es si, ellos
actúan como si el rango de partida fuese una celda( despues de todo una celda es in rango de 1x1)
y lo mueven de acuerdo alos valores que adopta: hacia abajo, arriba derecha e izquierda con las
mismas reglas de las celdas, luego los parámetros de ancho y alto se encargan de teminar el
trabajo contrayendolos, dilatándolos o dejándolos como estan. Veamos un ejemplo en que
participan todos los argumentos
Se ve que la referencia al rango final es F8:H12, partiendo del rango inicial C2:D5, y su suma es
140( también está el máximo y mínimo.)
En estos momentos no se alcanza a ver el potencial que tiene la función DESREF, una muestra se
puede ver en el tutorial RANGOS DINAMICOS
INDICE Y COINCIDIR POR BUSCARV
La función BUSCARV tiene el problema de que en la matriz de busqueda la columna
del valor a buscar tiene ser la misma o estar a la izquierda del valor a devolver , esta
función no nos sirve si queremos devolver un valor hacia la izquierda, veamos la
siguiente tabla
si quisiéramos saber en que mes la venta fue de 80.230 no podríamos usar BUSCARV,
pero el problema se resuelve con el adecuado anidamiento de INDICE y COINCIDIR, a
este anidamiento se le llama FORMULA, veamos como:
INDICE puede extraer el valor de una matriz si le damos los datos de fila y columna,
pues el valor estará en la intersección de ellos, el valor de la columna lo tenemos, ya que
este debe estar en la columna nº1 que es la del mes, solo nos falta el valor de la fila, que
muy amablemente nos lo entrega la función COINCIDIR quedando la siguiente fórmula
si la referencia en COINCIDIR es B15, la tabla en la que se busca la posición C2:C13,
en INDICE la matriz de la cual se saca el valor B2:C13 y la columna es la nº1.
Si ponemos los datos en una Hoja de Excel
y la fórmula se introduce en la celda E15, obtenemos el mes de Agosto, como se puede
ver en la tabla
FUNCION SI() ANIDADA CON LA FUNCIION Y() y O()
La función SI es una de las que mas se usan para el anidamiento ya que su estructura es
muy adecuada para esto:
1. En el parámetro 1 hay que poner una fórmula lógica, un anidamiento con
funciones lógicas o la propia función si actuando como función lógica.
2. En loa parámetros 2 y 3 se pueden anidar una multitud de funciones, como
funciones lógicas, lafunciñon si() actuando como función condicional o lógica etc.
Ahora vamos a ver un un ejemplo relativamente simple de la situación del punto 1.
Una empresa quiere promover a una nueva sección a los empleado que cumplan con las
siguientes condiciones :
1. Pertenecer al turno mañana.
2. Ser de la categoría 1 o que su sueldo sea menor o igual a 7.000$.
Para esto cuenta con la siguiente tabla que debe ser completada; donde los turnos son
M,T ,N ,correspondientes a mañana, tarde y noche respectivamente y las secciones van
de 1 a 4
Que se resuelve utilizando la fórmula
=SI(Y(O(E2=4;D2<=7000);Y(C2="M"));"PROMUEVE";"NO PROMUEVE")
como se ve, en el 1º parámetro tenemos una función Y que tiene anidadas en sus
parámetros, una función O y otra función Y, lo que aumenta el número de posibilidades
que se están evaluando o condiciones que se tienen que cumplir como:
ser del turno mañana (se tiene que cumplir siempre).
ser de la categoría 1 o que su sueldo sea <=7.000$ o ambas cosas.
Este es un ejemplo relativamente simple, anidando en el parámetro 1 y sin anidar nada
en los otros dos, pero la complejidad puede aumentar grandemente.
ELIMINAR MENSAJES DE ERROR EN BUSCARV
En la función BUSCARV muchas veces ocurre que el primer parámetro, que es la
referencia a una celda, esta vacío en forma momentánea, por ejemplo cuando se hace
una consulta a una base de datos, dando el error #N/A (no aplicable), también este error
puede aparecer cuando un valor buscado no está en la matriz de datos. Para eliminar
este antiestético mensaje recurrimos a un anidamiento de las funciones SI, ESERROR Y
BUSCARV.
Por ejemplo: supongamos que una línea aérea dispone de un momnitor, donde los
pasajeros pueden consultar el descuento que tiene determinado destino, teniendo el
formulario de consulta el siguiente aspecto
Para resolver el problema con Excel introducimos la fórmula de búsqueda en la celda
que esta debajo de DESCUENTO y elaboramos de una matriz de búsqueda en la Hoja2
(para que quede oculta a los usuarios) con los destinos y sus descuentos respectivos.
Damos el nombre "descuento" a la matriz de búsqueda e introducimos la función
BUACARV con la con la siguiente sintaxis:
BUSCARV(C2;descuento;2;FALSO)
como se muestra en la figura.
Se ve que BUSCARV da error aunque se introdujo la función correctamente, esto se
debe a que la referencia C2 esta vacía porque aun no se introdujo ningún destino. Otro
error se presentaría si se introdujera un destino que no esta en "descuento"
ambos mensajes de error desaparecen si se reemplaza a BUSCARV por la fórmula
donde se ha puesto una leyenda de advertencia si el destino no se encuentra
y se ve que funciona para la celda C3 vacía
o para un destino que sí está en la tabla "descuento".
Se preguntarán como ESERROR se da cuenta de cual de los 2 errores se está
cometiendo; la respuesta es que no tiene manera de darse cuenta, evitamos que tenga
que decidir con C2="" , que es la proposición lógica que junto con SI detectan si hay
un blanco y si lo hay colocan otro blanco enD3 , si este error no está, entra a jugar el
tercer argumento de SI, donde ya hay solo 2 posibilidades; que este el segundo error o
no, si está es detectado por SI, ESERROR Y BUSCARV, descartado el segundo error se
hace la búsqueda normal del principio.
Las funciones anidadas son muy poderosas, aunque al principio suelen ser un verdadero
dolor de cabeza, es por eso que hay algunos métodos para trabajar con ellas, lo que nos
dará pie para un tutorial mas adelante.
FUNCION BUSCARV
La función BUSCARV busca datos que están en primera columna de una tabla(a esta
tabla se la denomina matriz de búsqueda o de datos), si el valor es encontrado devuelve
el dato asociado (valor que esta en la misma fila que el dato a buscar) de una columna
especificada, la sintaxis es;
los primeros tres argumentos son obligatorios y el cuarto es opcional
Veamos el siguiente ejemplo:
Un profesor tiene una tabla con las notas de un alumno puestas en números y quiere
completarla poniendo las notas en palabras
para hacer esto cuenta con otra tabla de equivalencias
vuelca estos datos en un libro de Excel poniendo en la Hoja1 la tabla a completar y en la
Hoja2 la tabla con las equivalencias pero sin los rótulos para tener directamente la
matriz de datos
despues coloca la siguiente fórmula en la celda E3 de la Hoja 1:
en la que D3 es una referencia donde está el contenido , que en este caso es el valoor 2,
aunque hay casos en que por la naturaleza del problema, por ejemplo una consulta, la
referencia puede al principio estar vacia, dando el error #N/A (no aplicable), en el
tutorial ELIMINAR MESAJE DE ERROR EN BV, daremos una solución a este
antiestético mensaje.
A continuación se arrastra la función hasta completar la tabla
en este caso la matriz de búsqueda está en otra hoja, pero puede estar en cualquier lado,
incluso dentro de otra tabla.
FUNCION INDICE
La función INDICE tiene la particularidad de tener dos sintaxis:
1. SINTAXIS MATRICIAL : devuelve un valor o matriz de valores
2. SINTAXIS REFERNCIAL: devuelve un rango o referencia.
SINTAXIS MATRICIAL : En matemática una matriz es un arreglo de números, una
tabla de valores o dicho de otra manera una forma de ordenar números identificándolos
por su ubicación en filas y columnas o mas precisamente por la intersección de una fila
con una columna. En Excel, un rango, es lo que para la matemática una matriz,
vayamos a una Hoja de Excel
aquí podemos identificar el rango B1:E5 ( recuadrado en rojo) con una matriz de 4 filas
por 4 columnas donde estas se numeran, desde arriba y a la izquierda empezando por 1,
en forma creciente, con lo que por ejemplo el numero 567 correspondería a la
intersección de la fila 3 con la columna 2, el numero 23 con la intersección de la fila 1
con la columna 4 etc. Esto es lo que hace la función INDICE, devolver el numero que
esta en la celda que es la intersección de una fila con una columna, aclaro que en este
caso en la celda puede haber un numero, una cadena de caracteres, un mensaje de error,
una formula etc. Dicho esto se entenderá mejor la sintaxis de la función INDICE
en este caso INDICE nos devuelve el valor 567
CASOS PARTICULARES
1. Si el primer argumento es una matriz columna ( 1columna por n filas) se omite el
argumento columna.
2. Si el primer argumento es una matriz fila ( 1 fila por n columnase) se omite el
argumento fila
3. Si el primer argumento es una matriz de n columnas por m filas y se pone cero
como segundo argumento INDICE puede devolver una columna o una fila de la matriz
n X m,para hacer esto INDICE se introduce como una FORMULA MATRICIAL
SINTAXIS REFERENCIAL:
Devuelve la referencia de la celda ubicada en la intersección de una fila y de una
columna determinadas de un rango. Si hay mas de un rango se podrá elegir, mediante
un tercer argumento llamado área, en cual de ellos se buscará la intersección de filas y
columnas, el primer rango se relaciona con el área 1, el segundo rango con el área 2 y
así sucesivamente.
Para el siguiente ejemplo
la sintaxis es
que da como resultado "autos"
FUNCION COINCIDIR
La función COINCIDIR es una función de búsqueda como BUSCARV pero a
diferencia de esta, COINCIDIR no devuelve un valor sino una posición dentro de un
rango, este rango puede ser una columna o una fila y contener números, palabras o una
combinación de ambos . La sintaxis tiene 3 parámetros; el 1º es el valor referencia cuya
posición se quiere encontrar, el 2º el rango y el tercero pude ser -1, 0 y 1, que tomen
esos valores va a depender de:
1. Si la lista está desordenada el tercer parámetro es 0, dando error si el numero no
está en dicha lista.
2. Si la lista está ordenada en forma ascendente el valor es 1 o no se pone ninguno,
si el valor no está pero se encuentra entre otros dos , o sea a<valor<b, se elige la
posición del valor a.
3. Si la lista está ordenada en forma descendente el valor es -1 y si el valor no está
pero se encuentra entre otros dos valores o sea a>valor>b se da la posición del valor b)
Vamos a dar ejemplos para aclarar los conceptos:
Caso 1: lista desordenada
La lista está desordenada y el valor 325 se encuentra en la lista siendo su posición 2
Caso2: Lista ordenada en forma ascendente
como se ve el valor no está en la lista pero 50,6<81<84 y en este caso se da la posición de
50,6 que es 3
Caso 3: Lista ordenada en forma descendente
el valor no está pero se encuentra entre 50,6 y 80 por lo tanto la función da la posición
de 80 que es 2 .
Los casos 2 y 3 tienen el problema de que no podemos saber de antemano si la función
nos devolverá la posición del valor mas cercano al buscado, en los ejemplos mostrado
tuvimos suerte, pero veamos este otro caso con una lista ascendente
COINCIDIR nos devolvió el valor 4 correspondiente a 25,3 que no es la posición del
valor mas cercano al buscado, este valor tendría que haber sido 5 que es la posición de
100 que es el valor que mas se aproxima a 99. Este problema se soluciona con
FORMULAS MATRICIALES
COMPROBACION: en los ejemplos anteriores se podría haber puesto una referencia en
lugar del valoren sí, en el primer parámetro; se aconseja hacer esto para averiguar lo
que devuelve COINCIDIR cuando se introducen valores que no están en la lista( para
los 3 casos).
FUNCION Y()
La función Y() ,como O() es una función lógica ya que sus argumentos son
proposiciones lógicas, la función evalúa los argumentos y devuelve un resultado
VERDADERO o FALSO ( aclaro que esta función puede tener un solo parametro sin
dar error, aunque no tiene mucho sentido práctico)
Su sintaxis es:
Y(parámetro1;parámetro2;parámetro3;.....)
La función devuelve VERDADERO si la evaluación de todos los parámetros es
VERDADERA y dara FALSO si la evaluación al menos uno de sus parámetros es
FALSA o si todos son FALSOS.
Veamos un ejemplo
vemos que si cambiamos una desigualdad, o las dos el resultado es FALSO
FUNCION O()
Como Y() la función O() es una función lógica, porque sus argumentos son proposiciones lógicas o
pruebas lógicas la función evalúa los argumentos y devuelve un resultado VERDADERO o FALSO.,
su sintaxis es
O(parámetro1;parámetro2;parámetro3;.....)
La función devuelve FALSO si la evaluación de todos los parámetros es FALSO y dara VERDADERO
si la evaluación almenos uno de sus parámetros es VERDADERO o si todos son VERDADEROS.
Veamos un ejemplo
