FUNCIONES AVANZADAS

FUNCIONES AVANZADAS
If, sumar si, contar.si, buscarv, buscarh


1.- FUNCIÓN IF 

La función IF en Excel es una de las funciones más utilizadas por los usuarios de la hoja de cálculo ya que nos permite indicar una condición y evaluarla para saber si se cumple. Si dicha evaluación es verdadera, podremos ejecutar como respuesta una acción pero también podremos ejecutar una acción diferente si el resultado es falso.

ARGUMENTOS DE LA FUNCIÓN IF EN EXCEL

La función IF en Excel tiene tres argumentos y aunque la documentación oficial indica que solo el primer argumento es obligatorio, la realidad es que Excel devolverá un error si no indicamos los primeros dos argumentos. A continuación describo cada uno de los argumentos de esta función:

• Logical_test (Obligatorio): Es una expresión que puede ser evaluada como verdadera o falsa. Generalmente crearemos esta expresión utilizando los operadores de comparación o funciones de Excel que devuelvan como resultado un valor verdadero o falso.
• Value_if_true (Obligatorio): Es la acción que se ejecutará si la evaluación realizada en Logical_test resulta verdadera.
• Value_if_false (Opcional): Es la acción que se ejecutara si el resultado de la evaluación es falso. Si este argumento es omitido, se devolverá el valor FALSE.

Para comprender la función IF a fondo haremos varios ejemplos, primero para mostrar el tipo de pruebas lógicas que podemos construir y posteriormente la manera en que dicha función nos permitirá reaccionar al resultado obtenido en dicha evaluación.

OPERADORES DE COMPARACIÓN EN EXCEL

Antes de hacer los primeros ejemplos de la función IF en Excel, haremos un pequeño repaso de los operadores de comparación que nos permitirán crear las pruebas lógicas que serán evaluadas por la función. Como probablemente sabes, Excel tiene seis operadores de comparación:

• Igual a (=)
• Mayor que (>)
• Menor que (<)
• Mayor que o igual a (>=)
• Menor que o igual a (<=)
• Diferente a (<>)

Estos operadores funcionan de la misma manera que los símbolos utilizados en las ecuaciones matemáticas y que al ser utilizados para evaluar un par de valores obtendremos un valor falso o verdadero como resultado de la comparación. De hecho podemos utilizar estos operadores directamente en la barra de fórmulas de Excel por ejemplo, en la siguiente imagen puedes ver el resultado de hacer una comparación para saber si los valores de la columna A son mayores que los valores de la columna B:

Solo en aquellos casos en los que el valor de la columna A sea mayor que el de la columna B obtendremos como resultado el valor TRUE.  La fórmula especificada en la columna C es un ejemplo de una prueba lógica en Excel que puede devolvernos un valor verdadero o falso y es precisamente este tipo de expresiones las que podemos utilizar como el primer argumento de la función IF en Excel.

EJEMPLO DE LA FUNCIÓN IF EN EXCEL

Ahora que ya conocemos un poco más sobre las expresiones lógicas podemos hacer un primer ejemplo de la función IF en Excel. Para nuestro ejemplo haré uso de una lista de personas con sus respectivas edades que se encuentran en la columna B. Lo que deseamos hacer es colocar la leyenda “Mayor de edad” en la columna C para todas aquellas personas que tienen más de 21 años y para eso puedo utilizar la siguiente fórmula:

=IF(B2>21, "Mayor de edad")

La prueba lógica empleada evaluará si el valor de la celda B2 es mayor que 21 y en caso de ser verdadero la función nos devolverá el segundo argumento que en este caso es la cadena de texto “Mayor de edad”. Observa el resultado de emplear esta función en nuestros datos:

Para todas aquellas personas que tienen una edad mayor a 21 tenemos la leyenda correctamente desplegada en la columna C. Para los que no cumplen esta condición se muestra el valor FALSE ya que no hemos indicado un valor alterno para esos casos, pero eso lo resolveremos más adelante.

Por ahora quiero llevar tu atención al valor mostrado para Gerardo que tiene precisamente la edad de 21 años y se muestra el valor FALSE en la columna C y eso se debe a que dicha edad no entra en el criterio que indicamos en la función IF. Si quisiéramos mostrar la leyenda también para aquellos que tienen exactamente 21 años, entonces nuestra prueba lógica debería cambiar de la siguiente manera:

=IF(B2>=21, "Mayor de edad")

El operador utilizado ya no es Mayor que (>) sino que lo he sustituido por Mayor que o igual a (>=) y de esa manera la leyenda se desplegará inclusive para aquellos valores que sean iguales a 21:

EL TERCER ARGUMENTO DE LA FUNCIÓN IF

En el ejemplo anterior hemos desplegado correctamente una leyenda para aquellas personas que son mayores de edad, sin embargo para aquellas personas que no cumplen con dicho criterio se muestra el valor FALSE y todo eso se debe a que no hemos utilizado el tercer argumento de la función IF el cual indica el valor a regresar en caso de que la condición no se cumpla. Si queremos mostrar el mensaje “Menor de edad” cuando la prueba lógica es falsa, entonces debemos modificar nuestra fórmula de la siguiente manera:

=IF(B2>=21,"Mayor de edad", "Menor de edad")

La única diferencia con las fórmulas anteriores es que en esta ocasión estamos utilizando el tercer argumento de la función IF. Observa el resultado de utilizar esta fórmula en nuestros datos:

Ahora ya no tenemos valores FALSE en nuestros datos sino que en su lugar se muestra la leyenda “Menor de edad”. De esta manera hemos utilizado todos los argumentos de la función IF en Excel y como has aprendido, el segundo argumento será el valor devuelto si la prueba lógica es verdadera o por el contrario, si la prueba lógica es falsa se devolverá el tercer argumento de la función.

UNA FÓRMULA COMO PRUEBA LÓGICA

Hasta ahora hemos utilizado los operadores de comparación para las pruebas lógicas en nuestros  ejemplos, pero tal como lo mencioné al principio, también es posible utilizar funciones de Excel como la prueba lógica de la función IF. La única condición es que la fórmula utilizada devuelva como resultado un valor falso o verdadero. En el siguiente ejemplo tengo una lista de números aleatorios en la columna A y utilizaré la función ISEVEN para saber si un número es par y entonces desplegar la leyenda “Par” o de lo contrario mostrar el mensaje “Impar”. Esto lo lograré utilizando la siguiente fórmula:

La función ISEVEN devuelve el valor TRUE en caso de que el número evaluado sea par y por lo tanto la función IF muestra la leyenda “Par”. Si la función ISEVEN devuelve el valor FALSE, entonces se muestra el tercer argumento de la función IF que es el mensaje “Impar”.

UNA FÓRMULA COMO VALOR DE REGRESO

De la misma manera en que podemos utilizar una fórmula para la prueba lógica, también podemos utilizar fórmulas para el segundo y tercer argumento de la función IF. En los ejemplos anteriores solamente hemos desplegado la cadena de texto indicada en dichos argumentos, pero ahora que estamos evaluando si un número es par o impar, deseo hacer una operación matemática en cada caso. Si el número es par haremos una multiplicación de dicho número por dos y si el número es impar le sumaremos uno y luego lo multiplicaremos por dos. Para lograr este objetivo utilizaré la siguiente fórmula:

Si el resultado de la función ISEVEN es verdadero se devolverá el segundo argumento de la función IF que en este caso se encarga de multiplicar el valor por dos. Pero si el número es impar, entonces la función ISEVEN devolverá un valor falso y por lo tanto la función IF ejecutará la fórmula indicada en su tercer argumento que utiliza la función SUM para sumar uno y posteriormente hace la multiplicación por dos. Observa el resultado de utilizar esta fórmula:

2.- FUNCIÓN SUMAR SI

Use la función sumar.Si para sumar los valores de un intervalo que cumplen los criterios especificados. Por ejemplo, supongamos que en una columna que contiene números, desea sumar solo los valores mayores que 5. Puede usar la fórmula siguiente: = sumar.Si (B2: B25, ">5")

Sintaxis

SUMAR.SI(rango, criterio, [rango_suma])

La sintaxis de la función SUMAR.SI tiene los argumentos siguientes:

• rango   Obligatorio. El rango de celdas que se desea evaluar según los criterios. Las celdas de cada rango deben ser números, nombres, matrices o referencias que contengan números. Los valores en blanco y de texto se ignoran. El rango seleccionado puede contener fechas en formato estándar de Excel (a continuación se incluyen ejemplos).
• Criterio   Obligatorio. Es el criterio en forma de número, expresión, referencia de celda, texto o función que determina las celdas que va a sumar. Por ejemplo, los criterios pueden expresarse como 32, ">32", B5, "32", "manzanas" u HOY().
  Importante: Cualquier criterio de texto o cualquier criterio que incluya los símbolos lógicos o matemáticos debe estar entre comillas dobles ("). Si el criterio es numérico, las comillas dobles no son necesarias.
• Rango_suma   Opcional. Son las celdas reales que se sumarán, si es que desea sumar celdas a las ya especificadas en el argumento rango. Si omite el argumento rango_suma, Excel suma las celdas especificadas en el argumento rango (las mismas celdas a las que se aplica el criterio).

3.- FUNCION CONTAR SI 

Como ya hemos comentado muchas veces previamente Excel es mucho más que una calculadora. La función CONTAR en Excel y la función CONTAR SI en Excel junto con la función CONTAR SI CONJUNTO son funciones increíblemente potentes y versátiles y su utilidad está fuera de dudas.

 esta función es:

=CONTAR(rango1;rango2;rango3...)

Es decir, podemos tener varios rangos de celdas con los que poder contar los valores numéricos.

Ejemplo 1: contar valores numéricos en un rango

En el siguiente ejemplo vamos a ver como contar valores numéricos en un rango de celdas continuo. Para ello usaremos la siguiente sintaxis:

=CONTAR(A1:A10)

El resultado de aplicar esta función será:

El resultado de la operación anterior será 3 puesto que sólo contaremos los 3 valores numéricos del rango.¡

Ejemplo 2: contar valores numéricos en varios rangos

En este ejemplo vemos en la siguiente imagen como los argumentos de la función CONTAR son dos. No hace falta que usemos varias funciones CONTAR sino una único función.

4.- FUNCION BUSCARV 

La función BUSCARV en Excel nos permite encontrar un valor dentro de un rango de datos, es decir, podemos buscar un valor dentro de una tabla y saber si dicho valor existe o no. Esta función es una de las más utilizadas para realizar búsquedas en Excel por lo que es importante aprender a utilizarla adecuadamente.

PREPARAR LOS DATOS PARA LA FUNCIÓN BUSCARV

Comenzaremos con un ejemplo sencillo donde utilizaremos la función BUSCARV para realizar una búsqueda dentro de un directorio telefónico. Pero antes de escribir la fórmula que nos ayudará a realizar la búsqueda, será importante poner atención a los datos de origen.

Para utilizar la función BUSCARV debemos cumplir con algunas condiciones en nuestros datos. En primer lugar debemos tener la información organizada de manera vertical, es decir organizada por columnas.

Esto es necesario porque la función BUSCARV recorre los datos de manera vertical (por eso la letra “V” en el nombre de la función) hasta encontrar la coincidencia del valor que buscamos. Por ejemplo, los datos de nuestro directorio telefónico deberán estar organizados de la siguiente manera:

Algo que nunca debes olvidar es que la función BUSCARV siempre realizará la búsqueda sobre la primera columna de los datos. En el rango mostrado en la imagen anterior (A2:B11), la función BUSCARV realizará la búsqueda sobre la columna A.

Esto quiere decir que para nuestro ejemplo podremos buscar un nombre y obtener el teléfono, pero no podremos buscar un teléfono y obtener el nombre ya que la búsqueda siempre se realiza sobre la primera columna de la izquierda. Por esta razón es importante preparar los datos adecuadamente para obtener los resultados que necesitamos.

Algo que también debemos cuidar con la tabla de búsqueda es que, si existen otras tablas de datos en la misma hoja de Excel debemos dejar al menos una fila y una columna en blanco entre nuestros datos de búsqueda y las otras tablas de manera que la función detecte adecuadamente el rango donde se realizará la búsqueda.

Una vez que nuestros datos cumplen con estas condiciones estaremos listos para utilizar la función BUSCARV y realizar búsquedas en Excel.

SINTAXIS DE LA FUNCIÓN BUSCARV

La gran mayoría de las funciones de Excel tienen argumentos que son la manera en cómo le indicamos los datos con los que trabajará así como ciertos criterios de ejecución. En el caso de la función BUSCARV tenemos cuatro argumentos que describo a continuación:

• Valor_buscado (obligatorio): Este es el valor que queremos encontrar y el cual será buscado en la primera columna del rango de datos. Podemos colocar el texto encerrado en comillas o podemos colocar la referencia a una celda que contenga el valor buscado. La función BUSCARV no hará diferencia entre mayúsculas y minúsculas.
• Matriz_buscar_en (obligatorio): El segundo argumento es una referencia al rango de celdas que contiene los datos.
• Indicador_columnas (obligatorio): El Indicador_columnas es el número de columna que deseamos obtener como resultado. Una vez que la función BUSCARV encuentra una coincidencia del Valor_buscado nos devolverá como resultado la columna que indiquemos en este argumento.
• Ordenado (opcional): Este argumento es un valor lógico, es decir falso o verdadero. Con este argumento indicamos a la función BUSCARV el tipo de búsqueda que realizará y que puede ser una búsqueda exacta (FALSO) o una búsqueda aproximada (VERDADERO). Si este argumento se omite se supondrá un valor VERDADERO.

Como puedes ver, la función BUSCARV tiene tres argumentos obligatorios y uno opcional. Sin embargo, te recomiendo siempre utilizar los cuatro argumentos indicando en el último de ellos el valor FALSO para asegurar una búsqueda exacta.

Por otra parte, es importante mencionar que no todos los países de habla hispana utilizamos las misma normas en cuanto al carácter separador de listas. Así que, dependiendo de la configuración regional de tu equipo, deberás separar los argumentos de la función BUSCARV por una coma (,) o por un punto y coma (;).

Los ejemplos están hechos en una versión de Excel instalada en un equipo con configuración regional Español (México) y por lo tanto verás una coma en la separación de los argumentos de la función BUSCARV pero deberás estar consciente de que existe la posibilidad de que debas utilizar el punto y coma.

Como regla general puedo decir que, si en tu país se acostumbra utilizar la coma como el separador de miles, entonces también utilizarás la coma (,) para separar los argumentos de la función BUSCARV. Por el contrario, si en tu país se utiliza el punto como el separador de miles, entonces utilizarás el punto y coma (;) en los argumentos de la función.

EJEMPLO DE LA FUNCIÓN BUSCARV

Para hacer una búsqueda con la función BUSCARV sobre los datos de ejemplo, seguiremos los siguientes pasos:

1. En la celda E1 colocaré el valor que deseo buscar y que es uno de los nombres de la columna A.
2. En la celda E2 ingresaré el nombre de la función BUSCARV de la siguiente manera:=BUSCARV(
3. Inmediatamente después de ingresar el paréntesis haré clic en la celda E1 para incluir la referencia de celda e introduzco una coma (,) para concluir con el primer argumento de la función:=BUSCARV(E1,
4. Para especificar el segundo argumento, debo seleccionar la tabla de datos sin incluir los títulos de columna que para nuestro ejemplo será el rango A2:B11. Una vez especificada la matriz de búsqueda debo introducir una coma (,) para finalizar con el segundo argumento:=BUSCARV(E1,A2:B11,
5. Como tercer argumento colocaré el número 2 ya que quiero que la función BUSCARV me devuelva el número de teléfono de la persona indicada en la celda E1. Recuerda que la numeración de columnas empieza con el 1 y por lo tanto la columna Teléfono es la columna número 2. De igual manera finalizo el tercer argumento con una coma (,):=BUSCARV(E1,A2:B11,2,
6. Para el último argumento de la función especificaré el valor FALSO ya que deseo hacer una búsqueda exacta y finalmente terminará el ingreso de los argumentos con un paréntesis.=BUSCARV(E1,A2:B11,2,FALSO)

De esta manera, la función BUSCARV hará la búsqueda del valor de la celda E1 sobre los valores del rango A2:A11 y como resultado nos devolverá la celda de la columna B2:B11 que le corresponda. Observa el resultado de la función recién descrita:

Una ventaja de haber colocado el valor buscado en la celda E1 es que podemos modificarlo para buscar el teléfono de otra persona y la función BUSCARV actualizará el resultado automáticamente.

5.- FUNCIÓN BUSCARH

La función BUSCARH tiene tres argumentos que son los siguientes:

• Valor_buscado (obligatorio): El valor que estamos buscando.
• Matriz_buscar_en (obligatorio): El rango que contiene los valores y que debe ser una fila.
• Indicador_filas (obligatorio): El número de fila que contiene el valor que regresará la función.
• Ordenado (opcional): El valor debe ser FALSO si queremos una coincidencia exacta o VERDADERO para una coincidencia aproximada.

Si la función BUSCARH no encuentra el valor que está siendo buscado regresará el valor de error #N/A.

EJEMPLO DE LA FUNCIÓN BUSCARH

En la siguiente tabla tengo la información de los artículos que ha vendido cada uno de los vendedores en los últimos meses. Como título de columnas están los nombres de los vendedores y como filas los meses.

Ahora quiero saber los productos vendidos en el mes de Febrero por Paco y para ello puedo utilizar la función BUSCARH de la siguiente manera:

=BUSCARH("Paco", B1:D6, 3,FALSO)

El primer argumento es “Paco” porque es el vendedor que estoy buscando. El segundo argumento contiene todo el rango de datos sin incluir la columna de meses (columna A) porque no me interesa dicha información.

El tercer argumento es el número de fila que deseo que la función BUSCARH regrese como resultado. Ya que la fila con los nombres es la fila uno, entonces la fila para el mes de febrero es la fila número 3.

Finalmente coloca el valor FALSO en el cuarto argumento para indicar que deseo una coincidencia exacta al buscar a Paco. El resultado de esta fórmula es el siguiente:

Si quisiera busca la información del mismo mes para Luis, entonces la función cambiará de argumentos de la siguiente manera:

=BUSCARH("Luis", B1:D6, 3,FALSO)