Siguiendo con la temática acerca de funciones para consolidar información, presento una de las novedades de la versión 2007-2010; esta es:
Donde:
Rango_suma: celdas que se van a sumar.
Rango_Criterio1: primer rango a evaluar segun condición dada.
Criterio1: primera condición a evaluar en el Rango_Criterio1.
Rango_Criterio2: segundo rango a evaluar según condición dada.
Criterio2: segunda condición a evaluar en el Rango_Criterio2.
El plus de usar esta función es la posibilidad de filtrar los datos en función de dos o mas criterios. Aclaro que la función permite 127 parejas de criterios. En nuestro ejemplo se buscará sumar el número de artículos por mes; ello exige filtrar la información tanto por mes como por artículo.
Aquí la información a filtrar:
Ahora, el filtro por artículo se hará usando una lista desplegable tipo formulario de la opción de programador. Instalamos el control y damos click derecho.
Vinculamos el control con la celda H2, es decir, debajo de la misma celda donde está ubicado el control; esto por razones de estética.
Previamente hemos creado el nombre "producto" que hace que podamos ingresar más artículos posteriormente.
Teniendo la lista desplegable, en la celda H3 formulamos:
En esta nota buscaremos darle una utilidad diferente a la función Desref, dado que hasta el momento se ha utilizado para crear rangos dinámicos en las notas:
ref: es el origen desde donde se se mueve la referencia.
filas: es la cantidad de filas que se desplazará verticalmente.
columnas: es la cantidad de columnas que se desplazará horizontalmente.
alto: es opcional e indica la cantidad de filas que tendrá el rango devuelto.
ancho: es opcional e indica la cantidad de columnas que tendrá el rango devuelto.
Se debe aclarar que esta función es de tipo referencial; por tanto sólo devuelve la longitud de un rango de celdas, no hace alguna operación con este. Entonces, los tres primeros argumentos (ref, filas y columnas) definen el primer punto de referencia, y los dos últimos(alto y ancho) definen el tamaño del rango.
Recordemos que al final del ejercicio Función Sumar.Si.Conjunto , se sumaban las entradas de todo el año.
Pero ¿cómo saber la cantidad de pernos que ingreso de febrero a octubre? por ejemplo. Pues la respuesta a esta necesidad es utilizar la función DESREF, indicando mes inicial y mes final de la consulta a través de listas desplegables.
Buscaremos llegar a este modelo:
Como en notas anteriores, se instalan las listas desplegables de la pestaña Programador, se da click derecho, se asigna un rango de entrada que en este caso corresponde al rango denominado "meses"; es decir, el rango G8:G19 y se vincula con una celda ( mes inicial y mes final con las celdas M8 y M10 respectivamente).
Un truco para que al insertar el control, este quede justo entre las dimensiones de las celdas es insertarlo mientras se mantiene oprimida la tecla Alt.
Ocupándonos de la consolidación de los datos, formulamos en la celda L21 lo siguiente :
La función opera así:
La referencia inicial será la celda H7, una celda antes de la celda que contiene el primer mes (enero).
M8 corresponde a la fila inicial del rango, el valor cero en el argumento columna indica que no habrá desviación hacia otra columna.
El alto del rango se establecerá al restar M10 (vínculo del control que contiene el mes final)- M8(vínculo del control que contiene el mes inicial)+1. El hecho de sumar una celda permite que se incluya al mes inicial seleccionado desde el control.
Es necesario aclarar que no es lógico seleccionar un mes final que sea menor en términos de arreglo que el mes inicial; por tanto, la fórmula arrojaría un error.
Hoy presento un ejemplo de una función muy interesante de la categoría matemáticas y trigonométricas, esta es:
Donde:
rango: es el rango que se pretende evaluar.
criterio: es la condición a evaluar en cada elemento del rango.
[rango suma]: es el rango que contiene los valores a sumar de acuerdo con lo especificado en el rango criterio. Es opcional. Si se omite, se sumaran las celdas del argumento "rango".
Una de las virtudes de usar esta función es el hecho de poder evaluar un criterio o condición en un rango distinto del rango que se va a sumar.
En este ejemplo se tiene un consolidado de ganancias por mes; la idea es sumar los valores de las facturas por mes, y al final sumar las ganancias de todo el año. Este es el modelo:
Previamente he nombrado las columnas Mes y costo de la factura como "mes_1" y "costo", respectivamente.
A continuación, en la columna Ganancias por mes formulo lo siguiente:
=SUMAR.SI(mes_1;"Enero";costo)
Recordemos que mes_1 es el rango evaluado, es decir, mes a sumar.
"Enero" es el criterio o condición, en este caso sumará todos los costos de factura del mes de enero.
"costo" es el rango sobre el que se realizará la suma.
Aquí, el criterio va cambiado de acuerdo con el mes sumado.
Al final realizamos una suma de las ganancias por año. =SUMA(P5:P16)
Cuando tenemos un valor con formato fecha (05/01/2012), y necesitamos obtener el nombre del mes y no el número del mismo, podemos pensar en la función MES() o en un formato personalizado. Pues bien, la función MES() devuelve el número del mes y el formato personalizado podría funcionar así: "mmmm". Pero en esta nota buscaremos resolver la necesidad mediante la función BuscarV.
Primero haremos uso de la función MES() solo para extraer el número del mes .
= MES(núm de serie)
núm de serie: es un valor, celda o función que devuelve un valor de tipo fecha.
Ahora tendremos que armar una matriz que servirá de consulta para que la función BuscarV de como resultado el nombre del mes. Se recomienda nombrarla para evitar errores al copiar la fórmula; el nombre de la matriz será: "mes".
Finalmente formularemos en una columna contigua a la columna que contiene el número de mes lo siguiente:
=BUSCARV(H5;mes;2;FALSO)
H5 será la celda que contiene el número de mes; es decir, el valor buscado.
mes será la matriz consultada .
El argumento Ordenado será FALSO o cero para obtener una coincidencia exacta.
Nótese que si hubiese un valor por fuera de la matriz mes, el resultado seria NO aplica (#N/A), pero dicho valor no tendría sentido dado que solo hay doce meses. Bueno, la excepción sería si la celda que contiene el número del mes estuviese vacía.
En esta segunda entrega de la función SI, se pretende clasificar una calificación dentro de un referente cualitativo que viene dado por rangos. Se usará la función en cuestión acompañada de la función Y, vista en nuestra entrega anterior. Este es el modelo:
Nota: Para que la función trabaje correctamente con el primer criterio de valoración, es decir, <= 3,00 se debe utilizar un formato personalizado, así:
Damos click derecho sobre la celda que contiene el valor (3,00), elegimos la opción formato de celda, número_personalizada, y aparece el siguiente formulario:
En el ítem "Tipo:" ponemos entre comillas la desigualdad "<=" (menor o igual que).
En la celda C18 pondremos la calificación a evaluar, y en la celda E18 formularemos:
=SI($C$18="";"";SI($C$18<=$D$7;$E$7;SI(Y($C$18>=$C$8;$C$18<=$D$8);$E$8;SI(Y($C$18>=$C$9;$C$18<=$D$9);$E$9;SI(Y($C$18>=$C$10;$C$18<=$D$10);$E$10;SI(Y($C$18>=$C$11;$C$18<=$D$11);$E$11;"Valor Fuera del Referente"))))))
Como es de esperarse, no se clarifica como actúa cada parte de la formulación. A continuación se presenta una explicación de cada componente.
Para calificaciones entre 3,01 y 3,50 se formula el rango, haciendo uso de la función "Y". Hemos usado referencias absolutas con la tecla F4. Y($C$18>=$C$8;$C$18<=$D$8);$E$8. Si el valor está en este rango, La función "SI" devuelve el mensaje "Se Cumple insatisfactoriamente", es decir, el texto que está en la celda E8. Si no se cumple con el criterio, la funcón SI evaluará el siguiente criterio: Calificaciones entre 3,51 y 4,00. De esta manera se evalúa hasta llegar al rango entre 4,51 y 5.00.
Al final de la fórmula he adicionado un mensaje a presentar si el valor ingresado en la celda C18 es superior a 5,00. Este es: "Valor Fuera del Referente".
Imagen que explica cada componente de la formulación
Cuando se ingresa en la celda C18 la calificación 3,45 la valoración cualitativa arrojará el mensaje: se cumple insatisfactoriamente.
