Master Microsoft Excel: ¡Aprende cómo coincidir con el índice rápido!

1. introducción: índice y fórmulas: Hola a todos. Soy Jeremy, o como algunos me llaman Perro grande. Bienvenido a mi curso de Skillshare sobre el índice y fórmulas de partido. Antes de saltar al contenido, déjame tomar un segundo para presentarme. Llevo casi siete años en el mundo laboral y todo ese tiempo se ha pasado en Excel por mi tiempo en la universidad a mi papel actual hoy, Excel ha sido parte de mi conjunto de herramientas diarias. He desarrollado una pasión por la analítica y los intereses en descubrir las soluciones más eficientes y un amor por la automatización. Mi objetivo para esta serie de videos es convertirte en un experto en las fórmulas de índice y partido. Las fórmulas de índice y coincidencia, cuando se combinan, le proporcionan una función de búsqueda que supera las miradas de vLookup y H y asegura que sus cálculos no cambiarán en función de filas adicionales o columnas, la coincidencia de índice puede impulsar la efectividad de las hojas de cálculo ad hoc y los modelos de costos complejos. Únete a mí en esta serie mientras desbloqueamos el siguiente nivel en tu potencial celular y dejamos las otras funciones de búsqueda en el polvo. Vamos a ir después de él. 2. Conceptos básicos de índices y coincidencias, parte 1: combinación exacta: Hola a todos, Bienvenidos al primer video de la serie. En los próximos videos, estaremos cubriendo las fórmulas de índice y partido. La fórmula de índice nos tiene definir un rango de celdas y nos permite devolver el valor de una celda desde dentro de ese rango dadas las coordenadas de las celdas. La fórmula de coincidencia pide un valor de búsqueda y el rango definido, o aparece el valor de búsqueda, la fórmula de coincidencia devolverá entonces el número de columna o fila en el rango definido o la búsqueda se encuentra valor. Si bien estas fórmulas se pueden usar por sí solas, el caso de uso más común es cuando se combinan. En los próximos videos, trabajaremos a través de algunos ejemplos de las diferentes formas en que se pueden usar estas dos fórmulas. Vamos a bucear. En este ejemplo, tenemos una tabla de facturas para marzo que falta la ciudad cliente. Nos gustaría sacar a la ciudad cliente de la mesa de búsqueda a la mesa de trabajo. Podemos utilizar las fórmulas combinadas de índice y emparejamiento para ayudarnos. La fórmula de índice comienza pidiendo el rayo IRB que tiene nuestros datos deseados, que es B3 a B6 en la tabla de búsqueda. Tenga en cuenta que esto se establecerá como una referencia absoluta para que el rango no cambie a medida que copiamos la fórmula a otras celdas. A continuación, la fórmula de índice quiere el número de fila en la matriz que acabamos de definir de la celda donde residen nuestros datos deseados. Porque el número de fila específico para la ciudad que queremos devolver cambiará en función del cliente. Necesitaremos que el número de fila sea dinámico. Para ello, usaremos la fórmula de partido. La fórmula del partido pide primero el valor de búsqueda un diez. A continuación, nos pide que ingresemos a la matriz de búsqueda, que será A3 a través de un seis. Al igual que la matriz de índice anterior, esto también se establecerá en una referencia absoluta. Por último, se pide el tipo de partido, que en este caso será 0 porque queremos un partido exacto menor que y mayor que los partidos se explicarán en videos posteriores. Pero sepan que esos típicamente solo se usan para hacer coincidir números. La fórmula de índice tiene un número de columna de entrada opcional final que no vamos a utilizar en este ejemplo, pero vamos a echar un vistazo más adelante. De acuerdo, hemos terminado la fórmula. Vamos a copiarlo abajo y ver si estamos consiguiendo las ciudades correctas. Fantástico. Ahora, dado que esta combinación de fórmulas puede ser un poco confusa, revisemos rápidamente lo que pasó. En primer lugar, utilizamos la fórmula de índice para obtener el rango de celdas donde se ubicaba la información de la ciudad cliente que queríamos. Dado que no queríamos ingresar manualmente el número de fila de la ciudad del cliente para cada línea de factura, utilizamos la fórmula de coincidencia para devolver dinámicamente el número de fila. La fórmula de coincidencia devuelve el número de fila del cliente en la tabla de búsqueda basado en el valor de búsqueda que proporcionamos de la tabla de trabajo, pudimos utilizar el número de fila que el fórmula de emparejamiento proporcionada como entrada en la fórmula de índice para luego devolver la ciudad del cliente. Tenga en cuenta que las fórmulas de índice o ray y la matriz de búsqueda de fórmulas de coincidencia tienen que ser los mismos valores de fila. En nuestro caso, las filas tres a seis, para que las fórmulas funcionen adecuadamente juntas. De acuerdo, pasemos a otro ejemplo para solidificar nuestra comprensión. 3. Conceptos básicos de índices y coincidencias, parte 2: menos que coincidan: Hola a todos, Bienvenidos al segundo video de la serie. En este video, vamos a construir sobre la combinación de las fórmulas de índice y partido y profundizar en lo que la fórmula del partido puede hacer. Vamos a meternos en ello. En este ejemplo, tenemos una lista de clientes y las ventas anuales logradas por cada uno. Para cada cliente, queremos determinar cuáles deben ser sus reembolsos base en los desgarros de rebaja asociados a los objetivos de ventas anuales en la tabla de búsqueda. Al igual que en el video anterior, la fórmula de índice comienza con la matriz de los datos deseados, B3 a B8. A continuación, para hacer que la fórmula de índice es número de fila Input Dynamic, tenemos que usar la fórmula de coincidencia. En el ejemplo de video anterior, usamos al cliente como valor de búsqueda y 0 una coincidencia exacta como el tipo de coincidencia. En este ejemplo, vamos a utilizar un número, ventas anuales como valor de búsqueda en uno menos que como tipo de partido. Vamos a caminar por esto. El tipo de partido serán las ventas anuales entre el array de búsqueda serán de tres a ocho, y el tipo de partido será uno. Pasemos rápidamente por lo que está sucediendo en la fórmula del partido. Esta vez, el colchón primero toma el valor anual de búsqueda de ventas y lo hace coincidir con las ventas anuales en la matriz de búsqueda. Desde que ingresamos menos que como el tipo de coincidencia, la fórmula de coincidencia va a encontrar el valor más cercano en la matriz de búsqueda que es menor que nuestro valor de búsqueda. A continuación, devolverá el número de fila del menor que partido más cercano, y se alimentará en la fórmula de índice como el número de fila dinámico. Ya habíamos dado la fórmula de índice, la matriz de datos. Y ahora con un número de fila dinámico de la fórmula de coincidencia, devolverá el contenido de la celda en la posición de números de fila dinámicos dentro de la matriz. Copiemos abajo la fórmula para ver si obtuvimos los resultados que esperábamos. Impresionante, todo se ve bien. Pasemos al siguiente ejemplo. 4. Conceptos básicos de el índice y la canción, parte 3: combinación de índice: Hola a todos, Bienvenidos al tercer video de la serie. En este video, vamos a agregar una capa adicional a la combinación de las fórmulas de índice y partido y fórmula de coincidencia adicional. En los dos últimos ejemplos, miramos a devolver solo la fila número dos, la fórmula de índice para obtener los datos deseados. En este ejemplo, vamos a utilizar para hacer coincidir fórmulas para devolver tanto los números de fila como de columna. Esto nos permitirá devolver una gama más amplia de datos con solo un poco más de trabajo por anticipado. Aquí vamos. En este ejemplo, tenemos una tabla de facturas para marzo que falta los clientes, ciudad, estado y código postal. Utilizaremos la información de la tabla de búsqueda para rellenar nuestra mesa de trabajo. A diferencia de los otros dos videos de ejemplo, comenzaremos con una matriz de índice que incluye filas y columnas, B3 a D6. El array index tendrá tanto filas como columnas bloqueadas. Una nota importante aquí, a lo largo de esta fórmula, será muy importante prestar atención a los tipos de referencia de las celdas. Algunas celdas tendrán las filas y columnas bloqueadas en algunas solo tendremos filas o columnas bloqueadas. Si se utiliza el tipo de referencia de celda incorrecto, la fórmula no devolverá lo que pretendíamos. A continuación, usaremos la fórmula de primer partido para devolver dinámicamente el número de fila. El primer valor de búsqueda de fórmulas de coincidencia será el cliente un diez. Este valor de búsqueda tendrá la columna a bloqueada, pero la fila diez abierta, la matriz de búsqueda será A3 a A6 con ambas filas y columnas bloqueadas. Por último, el tipo de partido será 0 porque queremos un partido exacto. Para la segunda fórmula de partido para devolver el número de columna. El valor de búsqueda será la cabecera de columna ciudad. En la celda C9. Esta celda tendrá la fila nueve bloqueada, pero las columnas C se abren. El array de búsqueda será B2 a D2, con tanto las filas como las columnas bloqueadas. Por último, el tipo de partido volverá a ser 0 porque queremos un partido exacto. Copiemos esta fórmula al resto de las celdas de la columna de la ciudad y a las columnas del estado y del código postal también. Dulce, todo está funcionando como se esperaba. Retrocedamos un poco y hablemos de cómo está funcionando esta fórmula. Comenzamos la fórmula de coincidencia de índice indicando el rango de celdas que contienen los datos que queremos devolver eventualmente. Una nota rápida, queremos estar seguros que este rango de celdas solo incluye datos que queremos que nos devuelvan y no incluye valores de búsqueda que usaremos para obtener los datos. ¿ De acuerdo? Ahora que tenemos el rango de celdas que contienen nuestros datos de retorno para encontrar, la fórmula de índice ahora necesita tanto el número de fila el número de columna de donde se almacenan los datos. En lugar de dar números estáticos como la fila uno y la segunda columna para devolver Ohio, vamos a usar fórmulas de coincidencia para devolver dinámicamente estos valores. No tenemos que ingresar las coordenadas manualmente. La primera fórmula de partido va a devolver dinámicamente el número de fila. Damos la fórmula, el valor de búsqueda, el cliente en el rango de celdas donde está contenido el valor de búsqueda de clientes. Y devuelve la fila del valor de búsqueda de clientes. Dado que cada cliente en la tabla de búsqueda tiene la información correspondiente de ciudad, estado y código postal. El dato que queremos, devolviendo el número de fila de donde se encontró la fórmula de partido. El cliente también nos da el número de fila de los datos que queremos devolver. La segunda fórmula de partido va a devolver dinámicamente el número de columna. Damos la fórmula, el valor de búsqueda, el encabezado de columna, y el rango de celdas donde corresponde el encabezado de columna. Y devolverá la ubicación del encabezado de columna como un número dado ambas coordenadas de los dos valores de coincidencia, la fórmula de índice ahora es capaz devolver el valor de la celda que estábamos después. Eso fue un poco de información para absorber, pero te alimentaste a través de ella. Ya que este es el último video de la serie, me gustaría agradecerles por ver. Si te gusta este curso, por favor tómate un minuto para revisar mis otros cursos. Y como siempre, si te sientes atascado, siéntete libre de detenerte y hacer una pregunta. Gracias.