formulas de excel
Función indice-coincidir
La Función INDICE
Cuando se utiliza sola, la función INDICE devuelve un valor en la intersección de una fila y columna específica. Por ejemplo, se puede hacer que Excel devuelva el valor en la intersección de la fila 2 y columna 3
La sintaxis de la función INDICE es bastante básica: =INDICE(matriz, núm_fila, [núm_columna]).
Hacer clic en la celda donde se desea agregar la función INDICE.
Hacer clic en la pestaña Fórmulas.
Hacer clic en el botón Búsqueda y referencia en el grupo Biblioteca de funciones.
Seleccionar INDICE.
Aparece el cuadro de diálogo Seleccionar argumentos. La función INDICE permite elegir entre dos conjuntos de argumentos diferentes.
Matriz: Devuelve el valor real en la intersección de la fila o columna especificada.
Referencia: Devuelve la referencia de la celda en la intersección de la fila y columna especificada.
Seleccionar el argumento de matriz y hacer clic en Aceptar.
Aparece el cuadro de diálogo Argumentos de función en donde se encontrarán los siguientes argumentos:
Matriz: El rango de celdas en las que se desea buscar.
Núm_fila: La fila en la matriz desde la que se desea devolver un valor.
Núm_columna: La columna en la matriz desde la que se desea devolver un valor. Este es un argumento opcional.
Introducir el rango de datos que se desea buscar en el campo Matriz.
Introducir la fila en la matriz que se desea buscar en el campo Núm_fila.
Introducir la columna en la matriz que se desea buscar en el campo Núm_columna.
ÍNDICE y COINCIDIR
Cómo ÍNDICE y COINCIDIR en Excel
Toggle navigation
ÍNDICE y COINCIDIR
Mientras que las funciones INDICE y COINCIDIR no son demasiado poderosas cuando se utilizan de manera independiente, juntas permiten devolver un valor de celda en función de su posición horizontal o vertical.
La Función INDICE
Cuando se utiliza sola, la función INDICE devuelve un valor en la intersección de una fila y columna específica. Por ejemplo, se puede hacer que Excel devuelva el valor en la intersección de la fila 2 y columna 3.
La sintaxis de la función INDICE es bastante básica: =INDICE(matriz, núm_fila, [núm_columna]).
Hacer clic en la celda donde se desea agregar la función INDICE.
Hacer clic en la pestaña Fórmulas.
Hacer clic en el botón Búsqueda y referencia en el grupo Biblioteca de funciones.
Seleccionar INDICE.
Indice y Coincidir
Aparece el cuadro de diálogo Seleccionar argumentos. La función INDICE permite elegir entre dos conjuntos de argumentos diferentes.
Matriz: Devuelve el valor real en la intersección de la fila o columna especificada.
Referencia: Devuelve la referencia de la celda en la intersección de la fila y columna especificada.
Seleccionar el argumento de matriz y hacer clic en Aceptar.
Indice y Coincidir
Aparece el cuadro de diálogo Argumentos de función en donde se encontrarán los siguientes argumentos:
Matriz: El rango de celdas en las que se desea buscar.
Núm_fila: La fila en la matriz desde la que se desea devolver un valor.
Núm_columna: La columna en la matriz desde la que se desea devolver un valor. Este es un argumento opcional.
Introducir el rango de datos que se desea buscar en el campo Matriz.
Introducir la fila en la matriz que se desea buscar en el campo Núm_fila.
Introducir la columna en la matriz que se desea buscar en el campo Núm_columna.
La fila y columna que se introduzcan aquí hacen referencia a la fila y columna dentro del rango de celdas especificado, no al número de fila y columna del libro de trabajo.
Hay una vista previa del resultado ubicada debajo de los tres campos de argumentos. Revisar para asegurarse que los argumentos están introducidos correctamente y que está extrayendo el valor correcto.
Hacer clic en Aceptar.
Indice y Coincidir
La función INDICE devuelve el valor en la intersección de la fila y columna especificada.
La Función COINCIDIR
Cuando la función COINCIDIR se utiliza por si sola, busca un valor en un rango de celdas y devuelve la posición relativa de ese valor en el rango. Por ejemplo, se puede hacer que Excel busque el nombre Nelson en una columna de apellidos y devuelva la ubicación en la columna en donde se encontró el nombre.
Así luce la sintaxis de la función COINCIDIR: =COINCIDIR (valor_buscado, matriz_buscada, [tipo_de_coincidencia]).
Hacer clic en la celda donde se desea agregar la función COINCIDIR.
Hacer clic en la pestaña Fórmulas.
Hacer clic en el botón Búsqueda y referencia.
Seleccionar COINCIDIR.
Indice y Coincidir
Aparece el cuadro de diálogo Argumentos de función en donde se encontrarán los siguientes argumentos:
Valor_buscado: El valor en el rango de celdas para el que se desea encontrar una referencia de celda.
Matriz_buscada: Un rango de celdas que contiene posibles valores de búsqueda.
Tipo_de_coincidencia: Este es un argumento opcional. Introducir 1, 0, o -1 para indicar qué valor devolver. 1 u omitido encuentra el valor más grande que es menor o igual al valor de búsqueda, 0 devuelve una coincidencia exacta, y -1 encuentra el valor más pequeño o igual al valor de búsqueda.
Introducir el valor que se desea buscar en el campo Valor_buscado.
Introducir el rango de celdas que se desea buscar en el campo Matriz_buscada.
Introducir 0 en el campo Tipo_de_coincidencia para buscar un valor exacto.
Hacer clic en Aceptar.
generación de menú en excell
Selecciona la celda
Ve a la pestaña Datos
Haz clic en 'Validación de datos'
Elige 'Lista'
Añade los elementos de la lista
Ya está, ahora copia la celda (si quieres)
Función indirecto
La función INDIRECTO nos permite obtener la referencia de otra celda y no tener que volverla a introducir en una función. Nos ayuda a poder referenciar diferentes pestañas y analizarlas en una única hoja, de una forma más visual. Parece complejo, pero es más sencillo de lo que parece y es de gran utilida
Ejemplo de aplicación
Analizar las ventas de diferentes ciudades entre pestañas
Contexto:
Imaginemos que estamos analizando las ventas de diferentes ciudades y queremos resumir los datos, que se encuentran en diferentes hojas, en una sola. De esta manera, podemos tener una hoja principal a modo de report donde poder seleccionar las ventas que queremos visualizar.
En este ejemplo: Tenemos un Excel con diferentes hojas, una por ciudad (Barcelona, Madrid, Sevilla y Bilbao). En cada hoja encontramos en el mismo sitio una tabla que nos muestra: VENTAS ONLINE, VENTAS OFFLINE Y VENTAS TOTALES, de cada MES DEL AÑO.
Pasos para aplicar la fórmula INDIRECTO en Excel
Abrimos una nueva hoja (Report), que será donde resumiremos los datos con la FUNCIÓN INDIRECTA.
Creamos una tabla resumen y una lista de las diferentes ciudades que utilizaremos de selector.
En la celda de la tabla E7 aplicamos la función: =INDIRECTO($D$3&»!B2″)
$D$3: Es la celda donde se encuentra la ciudad en nuestro selector de la hoja Report. La fórmula buscará los datos en la hoja con este nombre.
*como ya sabéis el signo $ sirve para fijar la casilla seleccionada.
“!B2”: Es la celda donde se encuentra la información que nos mostrará la fórmula. Esta información tiene que estar ubicada en la misma celda para todas las hojas.
SEGMENTOS DE DATOS
La segmentación de datos es una función en Excel que proporciona una manera fácil de filtrar datos de una tabla. Esto permite filtrar y volver a filtrar la información rápidamente, lo que vuelve fácil encontrar la información exacta necesaria. Las segmentaciones funcionan bien cuando se usan tanto con tablas como con tablas dinámicas.
Insertar una Segmentación de Datos
Hacer clic en cualquier celda en la tabla.
Hacer clic en la pestaña Diseño.
Hacer clic en el botón Insertar segmentación de datos.
Seleccionar las columnas que se desea utilizar como segmentaciones.
Hacer clic en Aceptar.
Filtrar con una Segmentación de Datos
Después de que una segmentación de datos se crea, aparece en la hoja de cálculo a lo largo de la tabla. Las segmentaciones se sobrepondrán una encima de otra si hay más de una, pero pueden ser reposicionadas fácilmente.
Seleccionar los valores que se desea incluir en el filtro.
Mantener presionada la tecla Ctrl para seleccionar múltiples filtros
Borrar y Quitar una Segmentación de Datos
Antes de borrar una segmentación de datos de una hoja de cálculo, asegurar que se ha vaciado. Eliminar una segmentación de datos no borra el filtro.
Hacer clic en el botón Borrar filtro.
Todos los filtros se borran, pero la Segmentación de datos permanece en la hoja de cálculo.
Hacer clic derecho en la segmentación.
Seleccionar Quitar “Nombre de filtro.”
PROMEDIO
Clicamos en la celda donde queremos que aparezca el resultado de la operación. Escribimos =PROMEDIO. A continuación, seleccionamos con el ratón el rango de celdas del cuál queremos calcular el promedio. Pulsamos ENTER y el resultado de la función aparecerá en la celda
SI
Por ejemplo, =SI(C2="Sí";1;2) dice: SI(C2 = Sí; entonces devolver un 1; en caso contrario devolver un 2)
FUNCION SI Y
Se tienen que cumplir todos los criterios
Y: = SI(Y(Algo es verdadero; Algo diferente es verdadero); Valor si es verdadero; Valor si es falso)
=SI(Y(valor_lógico1;[valor_lógico2];[valor_lógico3];valor_si_verdadero;valor_su_falso)
SI (función SI) La función SI es una de las funciones más populares de Excel y le permite realizar comparaciones lógicas entre un valor y un resultado que espera. Por esto, una instrucción SI puede tener dos resultados. El primer resultado es si la comparación es Verdadera y el segundo si la comparación es Falsa.
FUNCIÓN SI ANIDADA
Es una función dentro de otra.
=si(F6<2;”DEFICIENTE”;(F6<3;”INSUFICIENTE”;SI(F6<4;”ACEPTABLE”;SI(F6<5;”SOBRESALIENTE””;SI(F6=5;”EXCELLENTE”;”ERROR”)))))
TODAS LAS NOTAS MENORES A
DOS ES DEFICIENTE
TRES INSUFICIENTE
CUATRO ACEPTABLE
CINCO SOBRESALIENTE
IGUAL A CINCO EXCELLENTE
FUNCION SI O
Se pueden cumplir uno de los criterios
O: = SI(O(Algo es verdadero; Algo diferente es verdadero); Valor si es verdadero; Valor si es falso)
=SI(O(valor_lógico1;[valor_lógico2];[valor_lógico3];valor_si_verdadero;valor_si_falso)
SI CONDICIONAL CON ASIGNACION DE COLORES
FORMATO CONDICIONAL: Se selecciona el rango de celdas cuyo color quieres cambiar. Luego, vamos a la pestaña Inicio, damos clic en formato condicional, resaltar reglas de celdas. Elegimos una de las condiciones (por ejemplo, es menor que…). Ingresamos un valor de referencia, abrimos el menú desplegable y elegimos formato personalizado. Damos clic en la pestaña relleno y elegimos el color que deseamos colocar a las celdas. Por últimos damos clic en Aceptar.
SEPARAR NOMBRES Y APELLIDOS
En primer lugar, abrimos el documento de listado de alumnos a Excel y creamos una columna más al lado de la columna que contiene el nombre y apellido para que se importen los apellidos y así nos quede separado:
Selecciona la columna que quieres separar y pulsa la opción Datos> Dividir texto en columnas
Pulsa la opción y te aparecerá la opción de separador en la parte inferior del listado de alumnos.
En este momento podrás seleccionar qué separador quieres usar, nostros usaremos el Espacio, pero si tu comunidad autónoma lo separa por otro separador, deberás escoger el correcto.
Una vez lo selecciones el nombre y apellido quedarán separados de tal manera y podrás importarlos en la aplicación con la opción de Importar alumnos desde Excel
VALIDACION DE DATOS
1. Seleccione las celdas para las que quiere crear una regla.
2. Seleccione Datos > Validación de datos.
3. En la pestaña de configuración seleccione la opción mas indicada.
4. En datos seleccione una condición.
5. Defina el resto de valores obligatorios de permitir y Datos según su necesidad.
6. Seleccione la pestaña Mensaje de entrada y personalice el mensaje que verán el usuario al escribir los datos.
7. Seleccione la casilla mostrar mensaje de entrada al seleccionar la celda para mostrar el mensaje cuando el usuario seleccione las celdas.
8. Seleccione la pestaña Alerta de error para personalizar el mensaje de error para personalizar el mensaje
9. Clic en Aceptar.
FILTROS
FILTRAR UN RANGO DE DATOS
1. Seleccione cualquier celda del rango
2. Seleccione Filtro > datos
3. Seleccione la flecha de encabezado de columna
4. Seleccione filtro de texto o filtros de número, como Entre.
5. Escriba los criterios de filtro y clic en Aceptar
FILTRAR DATOS EN UNA TABLA
1. Seleccione la flecha de encabezado de columna, la columna que quiere filtrar.
2. Desactive (seleccionar todo) y seleccione los cuadros que quiere mostrar.
3. Clic en Aceptar.
Filtro avanzado con muchos criterios
COMO APLICAR UN FILTRO AVANZADO A LOS DATOS EN EXCEL?
Antes de aplicar el filtro avanzado debemos seleccionar la tabla de datos (A4:D13) y posteriormente ir al menú pestaña Datos y en el grupo de Ordenar y Filtrar presionamos clic en el icono de filtros Avanzadas.
Cuando presionamos clic en el icono nos abre la ventana de filtros avanzados donde tenemos varios items: debemos escoger la opción si Filtrar la lista sin moverla a otro lugar o seleccionar la opción de copiar a otro lugar.
Posteriormente como previamente seleccionamos la información automaticamente nos detecta el rango de los datos para aplicar el filtro y en la opción de rango de criterios debemos de seleccionar la información de las condiciones. Para el ejemplo tenemos la celda B1 hasta B2, se debe tener en cuenta que siempre debemos de seleccionar con los encabezados
Por ultimo presionamos el botón Aceptar para aplicar el filtro.
Como se observa en la imagen nos filtra de forma automática los filtros que cumplen con la condición
PASO A PASO TABLAS DINAMICAS
1. Seleccione las celdas a partir de las que quiera crear una tabla dinámica.
2. Seleccione insertar > tabla dinámica
3. En seleccione los datos que desea analizar, haga clic en seleccionar una tabla o rango.
4. En tabla o rango, compruebe el rango de celdas.
5. Elija donde desea colocar el informe de tabla dinámica, seleccione nueva hoja de calculo o si prefiere en la hoja existente, seguido de eso seleccione la ubicación en la que quiere que aparezca la tabla dinámica.
6. Seleccione Aceptar
7. Para agregar un campo a la tabla dinámica, active la casilla del nombre del campo en el panel de Campos de la tabla dinámica.
8. Para mover un campo de un área a otra, arrastre el campo al área de destino.
Buscar v
Ejemplo de la función BUSCARV Para hacer una búsqueda con la función BUSCARV seguiremos los siguientes pasos: 1. sobre los datos de ejemplo, En la celda E1 colocaré el valor que deseo buscar y que es uno de los nombres de la columna A. 2. 3. 4. 5. 6. =BUSCARV( En la celda E2 ingresaré el nombre de la función BUSCARV de la siguiente manera: Inmediatam ente 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, 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, 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 column a número 2. De igual manera finalizo el tercer argumento con una coma (,): =BUSCARV(E1,A2:B11,2, 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 co n 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 3
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.
