Excel intermedio

CURSO DE EXCEL INTERMEDIO

TEMÁTICAS QUE SE VAN A VER EN EL CURSO

FORMULAS Y FUNCIONES 

VALIDACIÓN DE DATOS

TABLAS Y GRÁFICOS DINÁMICOS

FILTROS AVANZADOS

LISTAS
FORMULARIOS

REPASO

FUNCIÓN CONTAR Y SUMAR

La función SUMAR.SI sirve para sumar los valores en un funciónrango que cumple los criterios que se especifican. 
=SUMAR.SI(rango;criterio;rango_suma)

La función CONTAR.SI cuenta el número de celdas dentro de un rango que cumplen un solo criterio especificado por el usuario. 
=CONTAR.SI(rango,criterio)

La función CONTAR.SI.CONJUNTO en Excel nos permite contar los elementos de un rango que cumplen con los criterios definidos. Esta función nos permite especificar hasta 127 criterios.
=CONTAR.SI.CONJUNTO(rango_criterios1, criterio1, [rango_criterios2], [criterio1])

=SUMAR.SI.CONJUNTO que permite una suma condicional en base a varios criterios. Es similar a la función SUMAR.SI, pero en el caso de la nueva función se pueden utilizar hasta 127 nuevos criterios simultáneamente.

=SI(Criterios;valor verdadero;valor falso)

EL O SIRVE CUANDO UNA O MAS CONDICIONES SE CUMPLAN

AGREGA UNA COLUMNA LLAMADA MENCIONES DE HONOR, Y UTILIZA O PARA VER QUE ESTUDIANTES ESTÁN BECADOS. PARA ESTAR BECADOS DEBEN TENER PROMEDIO SUPERIOR A 4 O TENER MENCIÓN DE HONOR MERITORIA.

LA FUNCIÓN SI SE PUEDE USAR CON OTRAS FORMULAS, OPERACIONES Y VARIAS CONDICIONES.

EJEMPLO: =SI(FORMULA LÓGICA;"VALOR VERDADERO";SI(FORMULA LÓGICA;"VALOR VERDADERO";SI(FORMULA LÓGICA;"VALOR VERDADERO")))

SE USA CUANDO UTILIZAMOS MAS DE UN CRITERIO, TODAS LAS CONDICIONES SE CUMPLEN PARA QUE EL VALOR SEA VERDADERO
SE UTILIZA Y(A2>5,A2<10)

RESUELVE LAS SIGUIENTES PREGUNTAS

SE solicita personal F mayor de 18 años y de la Ciudad de Cali, para trabajar como meseras.

SE solicita personal M mayor de 18 años para trabajar como Mensajero.

 =SI(PRUEBA LÓGICA;"A2*10%;A2*3%")


PODEMOS AGREGAR EN LOS CRITERIOS OPERACIONES MATEMÁTICAS

FUNCIÓN CONSULTAV

Puede usar la función CONSULTAV para buscar la primera columna de un intervalo de celdas y devolver un valor de cualquier celda de la misma fila del rango.

=CONSULTAV(valor_buscado, matriz_buscar_en, indicador_columnas, [ordenado])

• Valor_buscado (obligatorio): Este es el valor que se va a buscar en la primera columna de la tabla. Podemos colocar el texto encerrado en comillas o podemos colocar la referencia a una celda que contenga el valor buscado. Excel no hará diferencia entre mayúsculas y minúsculas.
• Matriz_buscar_en (obligatorio): La tabla de búsqueda que contiene todos los datos donde se tratará de encontrar la coincidencia del Valor_buscado.
• Indicador_columnas (obligatorio): Una vez que la función BUSCARV encuentre una coincidencia delValor_buscado nos devolverá como resultado la columna que indiquemos en este argumento. El Indicador_columnas es el número de columna que deseamos obtener siendo la primera columna de la tabla la columna número 1.
• Ordenado (opcional): Este argumento debe ser un valor lógico, es decir, puede ser falso o verdadero. Con este argumento indicamos si la función BUSCARV realizará una búsqueda exacta (FALSO) o una búsqueda aproximada (VERDADERO). En caso de que se omita este argumento o que especifiquemos una búsqueda aproximada se recomienda que la primera columna de la tabla de búsqueda esté ordenada de manera ascendente para obtener los mejores resultados.

FUNCIÓN CONSUTAH

Busca un valor dentro de una fila y regresa el valor en la misma posición de una segunda fila. Siempre busca en la primera fila del rango especificado.

Sintaxis

CONSULTAH(valor_buscado, rango, valor_regresado, [aproximado])

• valor_buscado (obligatorio): Valor que se buscará en el rango
• rango (obligatorio): El rango de celdas que contiene la fila de valores y la fila de resultados.
• valor_regresado (obligatorio): Número de fila (dentro del rango) que contiene los resultados.
• aproximado (opcional): Indica si será una coincidencia aproximada. Si se omite se toma como verdadero.

Identificar las filas con los resultados

El primer paso para regresar múltiples resultados en Excel es encontrar las filas que contienen el valor buscado (que se encuentra en la celda F1), y para ello utilizaré la siguiente fórmula matricial:

=SI($A$2:$A$11=$F$1, FILA())

Recuerda que para que una fórmula sea matricial debes pulsar la combinación de teclas Ctrl + Mayus + Entrar al terminar de introducir la fórmula. Para probar la fórmula propuesta selecciono el rango de celdas F2:F11 e introduzco la fórmula en la barra de fórmulas de Excel y pulso Ctrl + Mayus + Entrar

Ordenar los resultados

Con la fórmula anterior podemos identificar las filas que concuerdan con el valor buscado pero debemos ordenar esos resultados de manera que los números 3, 5, 6 y 10 no estén separados por los valores FALSO. Podemos utilizar la siguiente fórmula matricial:

=K.ESIMO.MENOR(SI($A$2:$A$11=$F$1, FILA()), FILA()-1)

La función K.ESIMO.MENOR nos ayudará a ordenar los datos de manera ascendente. El primer argumento de la función es la misma fórmula del paso anterior y el segundo argumento es la función FILA que nos ayudará a indicar la posición que necesitamos. Observa el resultado de esta fórmula al aplicarla sobre el rango

Encontrar las coincidencias

Ahora que ya tenemos los números de las filas que nos interesan podemos utilizar la función INDICE para obtener los valores de la columna C que contienen los puntos de cada alumno que nos interesa mostrar. Considera la siguiente fórmula matricial:

=INDICE($C$1:$C$11, K.ESIMO.MENOR(SI($A$2:$A$11=$F$1, FILA()), FILA()-1))

El segundo argumento de la función INDICE es la misma fórmula del paso anterior. En el primer argumento he colocado el rango que contiene los puntos que deseo obtener. No debes olvidar pulsar la combinación de teclas Ctrl + Mayus + Entrar

Corrección de errores

Como puedes ver en la última fórmula obtenemos varios mensajes de error #¡NUM!, así que un último paso sería utilizar la función SI.ERROR para evitar desplegar los mensajes de error. Con la siguiente fórmula elimino dichos mensajes:

=SI.ERROR(INDICE($C$1:$C$11, K.ESIMO.MENOR(SI($A$2:$A$11=$F$1, FILA()), FILA()-1)),"")