Fórmulas y funciones de Excel, parte 5: funciones de búsqueda y referencia

1. Introducción a las funciones de búsqueda y referencia: Está bien. Bienvenidos a la Sección cinco. Buscar funciones de referencia. Ahora estamos empezando a meternos en algunas de las fórmulas de bateo pesado realmente, realmente poderosas s. Oh, no puedo esperar para empezar en esta sección. Hablaremos de nombrada una carrera, que es una forma de trabajar más eficientemente con tus fórmulas de búsqueda En los casos en que estés usando las mismas matrices una y otra vez. Voy a hablar V, mirar hacia arriba en edad, mirar hacia arriba, Que son una especie de dos funciones de look up clásicas que se utilizan todo el tiempo y excel. Vamos a hablar de algunas fórmulas como columnas de roe y filas, columnas , que podrían ser componentes realmente interesantes. Como parte de otras funciones de búsqueda entrarán en index and match talk sobre cómo combinar esos cómo usar match con una función de búsqueda V para hacer algunas cosas realmente poderosas. Y luego hablaremos offset, que es una fórmula algo complicada pero realmente, realmente interesante y lo hará en demo de Excel durante esa conferencia donde vamos a construir este gráfico dinámico que puede desplazarse y hacer zoom y moverse, básicamente hacer algunas cosas que la mayoría de la gente nunca pensaría sobresale, capaz de algunos emocionados de mostrarte que tenemos a archivos para descargar. Consiguió el PDF Excel para analistas, buscar Funciones de Referencia y el Archivo de Excel, Sección cinco Buscar referencia. Así que adelante y descarga los dos y preparémonos para rodar. 2. Trabajar con Arrays denominados: Por lo que quiero tomarme sólo un segundo para hablar de algo llamado Raise antes de sumergirnos en funciones específicas de búsqueda. Las matrices con nombre son muy buena herramienta para ayudarle a simplificar sus funciones de búsqueda, especialmente si se encuentra utilizando la misma matriz de datos exacta en múltiples lugares o a través de múltiples fórmulas. Entonces, por ejemplo, si tengo datos de nivel de producto en celdas a 1 36 y estoy usando esta fecha exacta una referencia en varios lugares, podría darle un nombre. En este caso, lo estoy llamando un peril para que en el futuro, cuando escribo fórmulas que hagan referencia a este array, pueda o bien escribir un uno Colin D seis o bien puedo simplemente acercar la ropa, que es el nombre que le he asignado a este array. Por lo que sobresalen. Reconocer eso y entender que estoy hablando de la matriz de un 1 36 Um, así que vamos a saltar a Excel y echar un vistazo al archivo con el que vamos a estar trabajando. Sección cinco. Buscar referencia. Tenemos cuatro pestañas aquí. Primera pestaña llamada Hitting Data, es una de nuestras pestañas de datos sin procesar. Tenemos nombres de jugadores, años después, y luego un montón de datos de golpear, métricas y columnas ver a través de datos de campo final. También tenemos un jugador en tu columna y luego algunas métricas de datos de fielding adicionales y de C a G. Tenemos una pestaña de comprobador de precios, que vamos a estar rellenando una vez que aprendamos a usar índice y partido y combinar el dos y luego tenemos diagrama de desplazamiento, que va a ser una herramienta realmente impresionante que vamos a construir usando la función de desplazamiento en . Vamos a crear un gráfico que podría ser de scroll herbal y zoológico móvil y dinámico y básicamente hacer cosas de las que la mayoría de la gente nunca pensaría que excel es capaz. Tan emocionado de compartir eso, um, um, pero saltando de nuevo al concepto de matrices con nombre vamos a mostrar un ejemplo. Vayamos a la pestaña de datos de fielding. Si quiero usar toda esta matriz en esta pestaña para algo así como una función de búsqueda hacia arriba, lo que puedo hacer es hacer clic en una flecha de desplazamiento de control de un golpe, derecha árabe abajo para que tenga toda la matriz seleccionada de la primera manera para definir esto como un Array llamado es hacer clic en este cuadro aquí, que es el cuadro de nombre y simplemente darle un nombre. Lo voy a llamar Fielding. Y ahora, si entras a la pestaña de fórmulas, haz clic en el gestor de nombres. Ya verás que se creó un nuevo Array llamado Fielding que se refiere a la pestaña de datos de fielding A 1/3 G 12,056 que es exactamente lo que acabo de seleccionar. Te voy a mostrar otra forma de hacer eso. Entonces voy a borrar esto. Sí, cierra esta caja. Y ahora, en cambio, la otra opción es iniciar en la pestaña de fórmulas, click en este botón de nombre definido, darle un nombre o llamarlo fielding nuevamente. Puedo añadir un comentario si quiero. Esto es sintiendo datos y luego haga clic en el botón Selector de datos y luego seleccione esa misma matriz exacta que acabo de hacer. Vuelve a pulsar el botón y bloquearlo. Entonces si vuelvo al encargado de nombres, se puede ver exactamente lo mismo que sucede. Creó un nuevo nombre DeRay llamado Fielding. Eso se refiere a un 1/3 G 12 56 en la pestaña de datos de fielding. Um, así que ahí vas. Es así como se crea raza nombrada 3. ROW/ROWS y COLUMN: todo bien. Quiero empezar con algunas de las funciones de referencia de búsqueda más simples. Eso Vamos a hablar de columnas de columna ro ros, que normalmente no se usan por sí solas. Normalmente se usan como componentes de fórmulas más grandes, más complicadas, pero sí sirven un propósito muy interesante y en algunos casos, muy útil. La función de fila simplemente devuelve el número de carretera de una referencia dada. Entonces si digo cuál es la fila de un cinco, regresaría cinco porque vender un cinco vidas en la quinta fila. De igual manera, la versión plural Rose sólo me dice el número de rosa total dentro de una matriz dada. Um, este tercer ejemplo de aquí abajo, usa una matriz definida por el usuario, que hablaremos en, creo, Sección nueve, la sección de fórmula array, um, Colin y columnas esencialmente hacen exactamente lo mismo. Acaban de devolver el número de columna de una referencia dada o el número total de columnas dentro de s cualquier matriz dada. Um, un pequeño consejo adicional aquí. Si deja la referencia fuera y justo la fila derecha con un paréntesis de cierre abierto o columna abierto paréntesis de cierre, devolverá el número de fila o columna de la celda en la que se escribe la fórmula. Entonces saltemos a sobresalir y echemos un vistazo a eso. Tengo un cuaderno en blanco aquí solo para darme algunos datos. Voy a hacer un rand rápido entre 1 y 100. Aplica eso abajo y otra vez. Entonces me dieron un trozo de datos puede golpear. Copia, Haz mi Ault H V V Atajo. Y ahora tengo una matriz de datos aleatorios de 1 a 100 entre celdas, una una y C 12. Entonces juguemos un poco con Roe y columnas funciones. Ahora mismo estoy en E uno y tipo es igual a fila. Abrir, cerrar, entrar. Esa es una porque la escribía en la primera fila. Y mientras aplico esto hacia abajo, verás cómo eso solo se actualiza para devolver Cualquiera que sea el camino en el que resida actualmente la fórmula , uh, qué más hacer Una fórmula de columna aquí. Abre, cierra, entra y sécala a la derecha y verás lo mismo. Simplemente sube por uno cada vez. Entonces eso es fila y columna sin referencia. Si quisiera probar el número de fila o columna de una referencia específica, podría hacer eso para poder decir ¿Qué es Thea Row de seis devolvió un seis o podría decir, ¿Cuál es la columna? Grupos de, Ah, un tres devuelven uno porque está en la columna A el entonces último pero no menos importante, podría hacer igual a Rose de toda esta matriz. Aquí, devolviste 12 porque tiene 12 filas de alto o podría hacer columnas de toda la matriz. Regresará un tres desde sus tres columnas de ancho. Eso otra vez. Estos no se suelen utilizar por sí solos, como se podía ver allí tipo de funciones simplistas, pero como componentes de fórmulas más interesantes, conservan un propósito realmente valioso. Entonces ahí tienes. Columnas de columna Ro Ros. 4. VLOOKUP/HLOOKUP: bien. Quiero empezar hablando de dos de las funciones de búsqueda más comunes pero poderosas en Excel. Miramos hacia arriba y h miramos hacia arriba. Estos aire generalmente se utilizan cuando se necesita combinar dos datos diferentes se levantan con un campo común . Entonces, por ejemplo, si tengo datos de nivel de producto en una matriz donde tengo cantidad y producto, yo d. Y luego tengo otra matriz de datos con los mismos productos y la información de precio, y quiero fusionar esas fuentes de datos para que tenga cantidad I D y Precio todo en una hoja de cálculo. El look up es una gran herramienta para hacer eso. Por lo que hay algunos componentes de la función lookup. Se inicia con un valor look up. Entonces este es el valor que estás tratando de igualar su campo típicamente ser común que existe entre los dos borran la fuente y la matriz de búsqueda. El segundo componente es la matriz de búsqueda. Entonces aquí es donde estás tratando de encontrar ese valor look up. Y la tercera pieza es el número de índice de la columna. Entonces en tu matriz de tablas, donde estás buscando tu valor de búsqueda Qué columna de datos te gustaría devolver fue, Ahí es típicamente donde está tu variable que estás tratando de tirar a tu primera fuente un derecho y luego por último pero no menos importante, tienes un rango. Mira hacia arriba, Um, que básicamente solo dice que están tratando de igualar el valor exacto de look up o algo similar. Estás intentando buscar el valor exacto que usarás para texto, cadenas y palabras. Pondrás un cero o la palabra falsa ahí. De lo contrario puedes poner uno. Por ejemplo, si estás buscando un número con un montón de decimales y no lo necesitas para que coincida , exactamente, podrías usar eso. Pero sólo para que lo sepas, el 99% del tiempo, vas a usar un cero o exacto como arregles, mirar hacia arriba. Entonces en este ejemplo aquí otra vez, tengo estos datos de nivel de producto que acaba de describir y en una matriz han conseguido nombre del producto, cantidad y producto I D. Y en mi segunda matriz, tengo producto y precio. Entonces como puedes ver, producto es el campo común entre ellos, y lo que quiero hacer es encontrar el precio en mi array look up y tirarlo a mi fuente. Muy bien, entonces en la celda D haber creado una columna de precios, voy a decir V mirar hacia arriba. Lo que sea que esté en un dos, que pasa a ser el producto camiseta dentro de mi matriz de mesa G uno a h cinco. Entonces va a ir fila por fila hasta que encuentre el valor look up, que en este caso, lo está encontrando en la Fila cinco. Y luego quiere devolver los datos de la segunda columna más. Ya que esa es la columna en el siguiente número Así que encuentra Camiseta se mueve a la segunda columna sobre , encuentra el valor de 14 99 y lo devuelve en la Celda D a. Y luego, al aplicar este look arriba mesa hacia abajo, va a estar buscando suéter que pantalones cortos que calcetines todo dentro de esta matriz look up. Entonces eso es Velicka H Look up funciona casi exactamente de la misma manera. Es realmente la misma sintaxis. La única diferencia es que necesitas usar un H Look up. Si estás mirando hacia arriba, se transpone Array, lo cual es una especie de forma impar de mostrar los datos, pero a veces correrás a través de conjuntos de datos que se construyen de esta manera, por lo que funciona de la misma manera. La única diferencia es que se moverá columna por columna hasta que encuentre su valor de búsqueda hacia arriba y luego se moverá hacia abajo a una fila especificada en el siguiente número, contraposición a mover fila por fila y pasar a una columna y siguiente número para el V. Buscar hacia arriba. Entonces otra vez, mismo concepto exacto. Simplemente lo usas cuando se transponen tus datos. Entonces dos reglas muy, muy importantes que debes recordar cuando usas V buscar o h buscar funciones. El primero es que tu valor de búsqueda tiene que estar en la primera columna. Si estás usando una vista V hacia arriba o la primera fila. Si estás usando una H, busca hacia arriba de tu tabla, una lluvia para que no puedas tener una matriz de tablas que vaya desde la columna ocho D, donde tus valores de búsqueda o una columna coincidente están llamando a Be. Excel simplemente no le gusta eso. La segunda regla es que Excel siempre devolverá el valor de la fila más alta. Si estás haciendo un B mirar hacia arriba o hacia la izquierda. Columna de la mayoría. Si está haciendo una búsqueda de edad de una matriz de tabla si están presentes múltiples instancias de su valor de búsqueda. Entonces esta es una distinción muy, muy importante que hacer aquí, que es que, uh, si vuelvo a mi V, busco ejemplo. Por ejemplo, en estoy viendo la camiseta Value T en este look up array, digamos que dos líneas seguidas tenían camiseta como el producto con diferentes precios. El V look up va a parar la primera vez que encuentre el valor que busca en este caso Camiseta y devolver el precio en la segunda columna más para esa primera instancia, ni siquiera va a saber que hubo múltiples instancias porque se detiene en el 1er 1 Así que la forma de evitar eso es identificar una clave que sea común tanto al sentido de datos como única para cada fila. Esa es la pieza clave aquí, por lo que normalmente toma la forma de una contaminación de múltiples campos. Hablaremos más de esos detalles en la sección de funciones de texto, pero básicamente puede Cat Nation, que se puede lograr solo usando el signo de ampersand y sobresalir, solo mashes juntos a campos de texto en una um así que veremos en nuestro ejemplo de béisbol de que tenemos jugadores y años y vamos a crear una clave común basada en ambos para usar correctamente la función de búsqueda V. Entonces eso si miras hacia arriba y h miras hacia arriba en la siguiente conferencia. Vamos a hablar de realmente poner esto en práctica fusionando nuestros datos. 5. Combinación de datos con VLOOKUP: todo bien. Suficiente hablando de V. Mirar hacia arriba en hte. Mira hacia arriba. En realidad lo usemos. Um, es realmente difícil aprender realmente estas fórmulas hasta que las practicas con datos reales. Entonces sigamos adelante y pongamos unas manos en la práctica usando la función V Look up Abre la sección cinco. Buscar arriba Referencia Excel Duck y funcionará fuera de ahí para recapitular Got my hitting data tab, que está organizado por jugador por año con una serie de métricas de bateo desde la columna C hasta final y luego de manera similar tener una pestaña de datos de sensación por jugador por año, con métricas de campo adicionales aquí, el objetivo de este ejercicio va a ser tirar de esas métricas de datos de fielding en columnas. Ah, a través de s en mi ficha de datos de golpear. Entonces esto va a ser como mi ficha fuente o datos maestros que une esos dos conjuntos de métricas juntos en un solo lugar. Entonces lo que no puedo hacer es simplemente empezar a escribir de ustedes. Función de búsqueda en el jugador llamado, por ejemplo, como mi valor de búsqueda hacia arriba porque también tengo los datos por año. Entonces si miraba el nombre del jugador en la matriz de datos de fielding. Va a encontrar la primera instancia de ese jugador, ya sabes, si jugaste en 2010 por ejemplo, pero ignora cualquier instancia posterior. Entonces si tengo un jugador que existe en el conjunto de datos cinco veces porque jugó los cinco años de los datos, entonces esa función de búsqueda V basada en León Nombre del jugador va a faltar cuatro de esas instancias se va a encontrar el 1er 1 va para devolver los valores asociados e ignorar los demás. Entonces el primer paso que tenemos que hacer es crear una columna clave. Por lo que al golpear datos en un clic derecho e insertar una columna a la izquierda de la columna, una tecla llamarlo y para definir esta función o esta nueva dimensión destruida equivale a B dos ampersy C dos. esto se le llama una función ocho de Can Katyn, y solo machaca esas cadenas de texto juntas para crear un campo nuevo y único. Entonces hay dos cosas que son importantes aquí, una su única. Sólo hay una instancia de cada uno de estos valores en toda esta columna, y a ella está en la primera columna columna A. Eso es muy importante. Si recuerdas la primera regla de las funciones de búsqueda. Entonces voy a saltar a sentir datos y hacer exactamente lo mismo. Nueva columna. Una llave de la universidad. Va a ser igual ser a ampersy C dos tranquilas abajo. Y ahora tengo mi clave común única entre ambas pestañas que puedo usar desde busco hacia arriba Así el siguiente paso que me gusta hacer es solo decir nuestro Lo Saltemos a mis datos de búsqueda y encontremos qué campos o qué variables busco jalar en mi hoja maestra. Y van a ser estos cinco. Entonces solo voy a pasarles el ritmo. Aquí hay marcador de posición y aquí es donde van a vivir todas mis funciones de búsqueda de vista. Entonces, empecemos con la posición. Uh, Colin P Sólo voy a empezar a escribir. Todo look arriba tan igual a V. Mira arriba, Abre el paréntesis. Busco valor va a ser cualquiera que sea mi clave aquí. Entonces a a busco tabla array, ¿Dónde estoy tratando de encontrar a este jugador? Nombre clave está en los datos de fielding de uno todo el camino y todo el camino hasta H 12,000 y 56 antes de que haga algo más? Voy a presionar F four, que arregla toda esa matriz en su lugar para que pueda aplicar esta fórmula de búsqueda hacia arriba hacia abajo. Una vez que lo he escrito y no tengo este cambio de matriz de datos junto con él, no es un número de índice de columna de coma de prensa. Es básicamente la columna donde mi posición se siente vive, porque esa es la métrica que estoy tratando de poblar en este momento, que está en la columna 1234 Así que índice de columna cuatro y luego solo voy a hacer cero para coincidencia exacta . Cierra el acierto de paréntesis. Entrar. Entonces ahora si aplico esto todo el camino hacia abajo, como pueden ver, está buscando el nombre de cada jugador. Si presiono f para sumergirme todavía, la matriz de tabla o la matriz de búsqueda no está cambiando. Entonces ahí lo tienes. Esa es la posición ahora. Otro pequeño truco que uso para ahorrarme algo de tiempo. Te darás cuenta si solo trato de arrastrar esta fórmula hacia la derecha, voy a obtener un error. Y la razón es que si miras cómo cambia la fórmula, mira primero el valor de look up. Es a a que cambia a ser, también, porque esa era una matriz completamente relativa. Por lo que lo moví a una columna a la derecha. Un cambio a sería. Pero en este caso, no importa en qué columna esté tratando de jalar con una función de búsqueda hacia arriba. Siempre voy a estar buscando el mismo campo clave. Entonces la A nunca va a cambiar de en a Así que lo que voy a hacer es presionar F 43 veces para solo arreglar la columna, pero dejar el relativo de fila De esa manera puedo volver a aplicar esto hacia abajo. A la Rosa le dejará ocho, que es lo que quiero. Pero ahora si me cambié, se queda un dos, que es lo que quiero. Ahora notarás que en realidad es la misma función exacta porque nada está cambiando para mis referencias. En este punto, todo lo que necesito hacer es cambiar el número de índice de columna porque recuerda, posición estaba tirando del valor en la cuarta columna sacada está en la quinta Columna. Entonces solo necesito cambiar el 4 al 5. Y ahí tienes, cumple eso abajo. Lo mismo. Podría arrastrar esto. Cambia el cinco a un seis para jalar en asistencias, que viven en la sexta columna sobre Y entonces haré lo mismo por errores y jugadas dobles , que viven en columnas siete y ocho, respectivamente. Y luego agarra los tres de esos. Haga doble clic en esa esquina para aplicarlos hacia abajo. Entonces ahí lo tienes. He escrito una función de búsqueda para tirar de mis datos de campo en una pestaña de fuente maestra. 6. Corrección de errores con IFERROR y VLOOKUP: De acuerdo, entonces te he mostrado cómo usar una sentencia if error de nuevo en la sección de operador lógico. Yo quiero volver a visitar eso y mostrarte cómo funciona exactamente de la misma manera. No importa qué fórmula use en este caso, vamos a aplicar para ser función de búsqueda que ya hemos escrito. Pero es importante volver a visitarlo también, porque se vuelve cada vez más relevante y útil a medida que empiezas a aplicarlo para buscar en funciones de referencia específicamente. Por ejemplo, si no tienes una coincidencia exacta de 1 a 1 entre tus datos y tu tabla de origen y tu tabla de búsqueda, eso va a impulsar un N un valor y en este caso, con los datos que realmente estamos usando, Sé de hecho que vamos a conducir errores porque hay jugadores que aparecen en la pestaña de datos de bateo pero no se mostrarán en la pestaña de datos de fielding porque solo golpean, por ejemplo. Entonces cuando la función de búsqueda V intenta encontrar a ese jugador en la matriz de búsqueda, que está en nuestro intento de fecha de fielding, va a salir con las manos vacías y regresar en N A. Así que veamos si eso realmente está sucediendo, primero lo que necesito hacer es simplemente aplicar filtros al resto de las columnas que acabamos crear. Seleccione la fila uno yendo a la pestaña de datos desmarcar el filtro, y luego vuelva a verificarlo. Eso sólo volverá a aplicar los filtros todo el camino a Conte. Y ahora, si miro alguna de las columnas se escribieron estas buscamos funciones. Entonces P Q R, S t Sólo usaré el desplegable y veré que hay, de hecho, en un error que se está poblando. Entonces lo que voy a hacer, igual que hice en la última sección, es que voy a entrar en la barra de fórmulas, haga clic justo después de la declaración igual y derecha y si error. Entonces si el error se abre, el valor del paréntesis va a ser toda la función que acabo de escribir saltó la coma de prensa final . Y en este caso para el campo de posición, ya que el jugador realmente no tiene una posición, si solo está en el conjunto de datos de bateo, solo voy a llamarlo otro por ahora. Ciérralo, presiona enter y luego aplica esa fórmula hacia abajo. Y ahora cuando re filtro, puede ver que el ha cambiado a otro valor no para las estadísticas reales y columnas Q, R, S y T. Aquí es donde la declaración de asunto adquiere una aún más importante papel. Entonces en este momento estoy obteniendo algún valor en todas estas columnas. Y el problema con eso es que si trato de que selecciones toda la columna para ver que el sol sobresale ser echado porque está viendo estos mensajes de error y es que está impidiendo excel sume o haga cualquier otra operación estadística en el valores legítimos. Por lo que puedes comparar cuando seleccionas. Ya sabes, una columna de datos como M o N, donde estás viendo conteo promedio, y algunos en comparación con nuestros campos calculados, que solo estás mostrando conteos porque tienen estos errores construidos ahí lanzando ellos fuera. Entonces lo que voy a hacer es otra vez, voy a escribir en una declaración justa, saltar hasta el final, y sólo voy a tomar cero. No voy a usar la cotización, así que eso va a introducir el valor numérico real de cero en los casos donde tú ahí dentro. Entonces, cuando lo aplico hacia abajo, selecciona toda la columna que podrías ver ahora sobresale, capaz de calcular un conteo promedio y una suma que me permita hacer cualquier tipo de operación estadística que yo quiera, solo como si fuera cualquier otro campo numérico para que pueda tirarlo a una tabla pivotante. Puedo ejecutar estadísticas, funciones en él, lo que quiera. Entonces voy a hacer exactamente lo mismo para las siguientes tres columnas. Hay dos formas de hacer esto. Simplemente puedo agregar si errores a cada uno, lo cual estoy haciendo ahora, o puede agregar el asunto a éste, aplicarlo de nuevo y luego cambiar la columna. El índice número 27 y dos y ocho, igual que lo hicimos solo creó primero la función. Depende totalmente de ti , con lo que estés más cómodo. Pero ambos enfoques funcionan. Entonces ahora, como pueden ver, si selecciono la columna R s o el té, estoy obteniendo el conteo promedio en algunos. Y si miro hacia abajo en el filtro que algún valor ha desaparecido y ha sido reemplazado por cero así de nuevo, si son realmente, realmente importantes, sobre todo cuando se trata de buscar funciones 7. Opciones de arreglos de referencia de VLOOKUP: Está bien. Por lo que tienes una hermosa fórmula V look up escrita. Está poblando los datos correctamente. Incluso lo hemos envuelto en una declaración justa para dar cuenta de los valores que faltan. Todo está bien. Quiero hablar de otro retoque que podemos hacer a la matriz de la tabla, Um, Um, y otros dos enfoques que podemos usar que pueden ser beneficiosos en ciertas situaciones. Entonces en este momento hemos escrito nuestra mirada hacia arriba con una matriz fija, uh, tabla. Entonces en este momento, la búsqueda hacia arriba está buscando el valor de búsqueda dentro de la matriz de uno a H 12,056 exactamente esa matriz. Hay otros dos enfoques que podríamos tomar. El primero es usar en named Array. Entonces si recuerdas Ah, en una de las primeras conferencias de esta sección, creamos un Array llamado basado en nuestros datos de fielding. Pero si recuerdas cuando lo creamos, aún no habíamos definido esta columna clave. Um, así que si en realidad seleccionas ser uno el recuperarlo h y luego controlar el cambio hacia abajo. Ahí está mi array de fielding. Entonces básicamente cambió mi matriz nombrada sobre, Um, y en este caso, mi mirada hacia arriba. Array necesita incluir la columna A porque ahí es donde van a vivir mis valores de búsqueda. Entonces lo que puedo hacer es ir a las fórmulas Tab, Seleccionar nombre Gestor. Aquí está mi matriz de fielding que habíamos creado. Voy a editar eso. Todo lo que voy a hacer es cambiar el B uno. Eliminar el ser cambiando a un hit. De acuerdo, cuando cerré eso ahora, si selecciono un turno de control uno alguna vez flecha a la derecha hacia abajo, se puede ver que se define como mi fielding llamado Array. Entonces ahora que hemos hecho ese pequeño retoque, puedo volver a la mirada hacia arriba y simplemente saltar a la mesa o a un pedazo de mi fórmula, borrar todo lo que creo, y justo fielding. Y como puedes ver , aplicará todos los mismos valores. Podría hacer lo mismo aquí otra vez. Voy a eliminar toda la matriz de tablas, comenzando con los datos de fielding de nombre de tabulación seguido del signo de exclamación para dejar todo eso y el fielding justo. Y nuevamente, Excel reconocerá eso como referencia con nombre, y sabrá exactamente a qué referencia se refiere y poblará los errores correctos. Entonces solo estoy pasando por columnas. Q R S T. Y sólo haciendo la misma actualización aquí en cada caso, tenga en cuenta que no estoy poniendo fielding y citas ni nada porque no es una cadena de texto. Es una referencia a un Array llamado y, como pueden ver, X Ellis apareciendo esta pequeña caja porque conforme empiezo a escribir, campista su búsqueda a través del gestor de nombres y ver si he creado algún llamado raise con eso nombre. Y así, de hecho, sí encuentra que es algo que simplemente le das. Un click lo dejará caer, y estamos bien para ir así puedo seguir adelante y aplicar todos esos cambios que acabo de hacer abajo. Y una vez que esto termine de calcular, verás que es fielding en todos los casos para que veas que es un poco más compacto, un poco de una fórmula más limpia. El problema que tengo con nombrado un raise es que a necesito recordar que han creado este nombre para él y usar el gestor de nombres para comprobar exactamente a qué vende Eso llamado Array se refiere. El segundo es que es arreglado. Entonces si necesito modificar o agregar datos, voy a necesitar editar la referencia de campo para contabilizar eso. Entonces una última opción para la matriz de tablas, que en realidad prefiero y uso bastante, es en lugar de usar y nombrada matriz sin nombre. Y en lugar de usar una matriz fija, específica como una a través de H, lo que sea que waas, lo que voy a hacer es seleccionar datos de campo. Tenga en cuenta que estoy en la mesa o un pedazo de mi fórmula en este momento. Seleccionar datos de campo. Basta con hacer click. Toda la columna puede mantener pulsado el turno y todo de la columna H y golpear entrar. Aplica eso hacia abajo. Tan mismo resultado exacto Ya puedes ver ahora está mirando hacia arriba. Básicamente cualquier fila y columnas A a h no sólo abajo a 12,056. Y el beneficio de hacer esto es que Ahora, si consigo un nuevo año adicional de datos o si actualizo los datos en mi pestaña de datos de fielding, no tendré que tocar esas búsquedas v. Quiero tocar la referencia y quiero tocar el gestor de nombres porque cada vez que realiza el look up, va a buscar cada fila poblada de la Columna A a la H s. por lo que es una es una forma más efectiva más eficiente de usar. Buscar funciones cuando se tiene una tabla de referencia de búsqueda en constante cambio o actualización dinámicamente . Entonces esa es mi pequeña propina. Yo lo uso bastante. Simplemente te mostraré lo mismo exacto para las últimas cuatro columnas solo para que estés cómodo con él un a través de H entrar. Y de nuevo, esto sólo parece ahorrarme bastante tiempo. Eso que no tengo que recordar. Ya sabes, todos estos matices sobre cómo, si se define, ciertos nombraron un aumento o exactamente qué? Mi tabla de búsqueda, uh, está definida actualmente como en términos de la referencia celular. Simplemente siempre sé que pase lo que pase, mis búsquedas van a funcionar correctamente. Entonces suministre esos abajo, ya sabes, tómate un minuto para calcular. Y ahí tienes. Entonces ves que nada ha cambiado en la superficie porque los tres métodos de escribir esta fórmula que te han mostrado hacen exactamente lo mismo, es solo que todos tienen beneficios e inconvenientes muy específicos. Entonces ahí lo tienes. Esto es lo que recomendaría usar solo referencias de columna totales en lugar de nombre para elevar o fijar una carrera para que te vayas ahora tenemos una función realmente, realmente eficiente, be look up 8. La función INDEX: la siguiente función de referencia de búsqueda que quiero compartir con ustedes se llama Índice y antes incluso de saltar, sólo oírme fuera. Sé que esto se va a sentir como una de esas funciones donde lo lees. Ves lo que hace, y de inmediato te piensas a ti mismo. Muy bien, Chris, ¿por qué alguna vez voy a necesitar usar la función de índice? Lo que hace es tan sencillo que no hay formas más fáciles de hacerlo. Um, y tienes razón. El hecho es, igual que fila y columna. Es una de esas funciones que nadie usa nunca por sí mismo. Um, por esa misma razón, es tan simplista. Pero una vez que empieces a combinarlo y usar Index como componente de otras fórmulas, puedes abrir la puerta para empezar a hacer algo muy cool para que la promesa dé sus frutos . Sólo escúchame. Por lo que el índice funciona por sí mismo. Todo lo que hace es devolver el valor de una celda específica dentro de una matriz. Entonces solo estás diciendo que no son hey, Excel? Aquí está esta matriz de datos o matriz de celdas. Quiero que bajen a la fila número cinco y a la columna número tres y me digan el valor que obtienen. Eso es todo. Entonces, sabia sintaxis, la fórmula del índice tiene tres componentes. La carrera. Entonces, ¿qué rango estás mirando el número de fila, cuál es cuántas filas hacia abajo quieres ir? Y el número de columna, ¿ cuál es cuántas columnas más quieres mover? Entonces en este caso aquí, vas a empezar desde la esquina superior izquierda de cualquier matriz que especifiques, luego bajar al número de carretera y más de dos columnas, y Excel te dirá lo que encuentra en eso. Entonces así aquí. Estamos diciendo, Vamos a indexar la matriz de uno a C cinco. Bajemos a la quinta fila y pasemos a la tercera columna. Y cuando haces eso, devuelve 2 34 porque ese es el valor que encontró en la celda en la que aterrizó. Entonces otra vez, muy, muy sencillo. Confía en mí, esto tendrá más sentido en un minuto, así que agárrate tus sombreros 9. La función MatCH: Muy bien, A continuación, tenemos la función de coincidencia, que al igual que Index, es muy simple que no es probable que lo use por sí mismo. Pero más bien es un componente para fórmulas anidadas más complicadas. Por lo que el tipo de función de coincidencia funciona de la forma opuesta del índice devuelve la posición de un valor específico dentro de una columna o fila. Entonces en inglés llano, básicamente, estás diciendo: De acuerdo, Excel. Busco este valor, ya sea una palabra o número o lo que sea, dentro de esta columna o dentro de esta fila, y devolverá la posición de ese valor. Entonces, um, tres componentes la función de coincidencia. Tienes tu valor de look up, que es qué valor. Estamos tratando de encontrar la posición fuera de tu array look up, cuál es dónde estás buscando? Y nota Tiene que ser una matriz unidimensional. Te diré por qué, en tan solo un segundo, el tercer componente es el tipo de partido. Entonces si estás buscando el valor exacto, que serás el 99% del tiempo, solo ponlo cero. Si estás buscando algo similar, puedes usarlo uno o uno negativo. Aquí s 02 ejemplos aquí en el primer caso estaban haciendo coincidir la palabra alicates en la columna A eso. Está mirando a través de la columna A empezando por la parte superior y trabajando su camino hacia abajo hasta encontrar que buscan alicates de valor y regresan. Cuatro porque lo encontró en la cuarta fila hacia abajo. En este caso, estamos diciendo que coincida con el número 66 dentro de la Fila tres. Entonces un tres ahí c tres. Por lo que Excel comienza por la izquierda, se mueve hacia la derecha hasta que encuentra una mirada al Valor 66 devuelve tres porque lo encontró en la tercera columna sobre Ahora, recuerda, mencioné que tiene que ser una matriz unidimensional. El motivo que es el caso es que si dijéramos, ya sabes, igualamos el número 2.5 dentro de la matriz de uno a Be cinco, ¿cómo nos diría Excel la posición del número 2.5, en este caso? 2.50$ dentro de una matriz tridimensional? Correcto. No hay un solo número que comunique la posición de ese valor, razón por la que hay que decir, mira dentro de una sola columna o una sola fila y Excel te dirá el número de carretera o el número columna en el que se encontraba el valor encontrado. Entonces eso es partido en la próxima conferencia. Voy a hablar de cómo el índice y el partido se pueden combinar para hacer algunas cosas épicas. 10. Usar INDEX y INDEX: está bien, así que hablamos de Index. Hablamos de partido. Ahora es el momento de hablar de cómo combinarlos para hacer algunas cosas realmente geniales. De hecho, cuando los combinas, actúan casi como una función de look up, pero en algunos casos con aún más flexibilidad. Por lo que confinaron valores en cualquier columna o cualquier fila dentro de una matriz dada. Entonces echemos un vistazo a un ejemplo. Es un poco complicado envolver tu cabeza al principio, pero una vez que la descompongas en sus piezas o componentes individuales, verás que en realidad tiene bastante sentido. Entonces lo que vamos a hacer es escribir una función de índice, pero vamos a anidar funciones de coincidencia dentro de ella. Entonces en este caso, estamos diciendo nuestro Excel. Encontremos un precio específico dentro de esta pequeña matriz de precios de B a D four. Entonces va a decir índice que matriz sea a 34. Pero en lugar de simplemente decir, ya sabes, pasar a la tercera fila hacia abajo y la segunda columna sobre la que le está dando direcciones muy fijas , quiero hacerlo dinámico. Quiero que el usuario sea capaz de ingresar. Algún valor que determinará en qué celda aterrizamos dentro de esa matriz. Y para eso se utilizan exactamente las funciones de coincidencia. Entonces si recuerdas la primera pieza de la función de índice es teoría, que es B a 34 la segunda pieza es el número de carretera. Entonces, ¿cuántas filas hasta en movimiento? En este caso, el número de filas hacia abajo que queremos mover depende de qué productos te importan suéter, chamarra o pantalón. Entonces lo que podemos hacer es crear una función de coincidencia que diga, Vale, vamos a dejar que el usuario entre cualquier producto que estén buscando en Selby seis y vamos a decir, está bien, bien, igualar ese producto dentro del rango de un dos a un cuatro. Y entonces la respuesta te va a decir cuántas filas hacia abajo para indexar y luego de manera similar permitir al usuario seleccionar en qué tamaño le interesa. Pequeño, mediano o grande. Y cuando insertes eso en una función de coincidencia basada en la matriz de B uno a D uno , eso te dirá cuántas columnas hay que mover para que parezca una especie de bestia de una función. Pero cuando consideras cada fórmula de coincidencia individual tipo de partida de adentro hacia afuera y piensas cuál es la salida de cada una de esas funciones de coincidencia. Terminas con solo un índice realmente simple. Entonces en este caso, si has seleccionado pantalón y medio, esto solo va a ahorrar Índice B a 34. Baja a la tercera Roma y a la Segunda Columna y devuelve el número 30 porque un pantalón mediano de padres cuesta 30 dólares. Entonces vamos a saltar a Excel y en realidad escribir una de estas cosas desde cero. Mismo concepto exacto. Aquí. Tenemos una hipotética herramienta de comprobación de precios. En este caso, tenemos cinco opciones de tamaño diferentes y cinco opciones de producto diferentes, y nuestro objetivo va a ser poblar esta celda aquí con el precio que estoy usando una función de índice con componentes de coincidencia para tirar de un determinado o específico precio fuera de esta matriz. Lo primero que tenemos que hacer es agregar algo de validación de datos porque quiero que los años podrán bajar las opciones para productos y las opciones para tamaños en lugar de tener un tipo it in. Por lo que sólo voy a seleccionar ser 10 yendo a validación de datos permitir una lista. Y esa lista sólo va a ser la lista de productos que puedas elegir. Entonces como, OK, como puedes ver, eso ha funcionado. Lo mismo con los datos C 10 Validación de datos. Permitir una lista. Y la fuente de esa lista va a ser todos los tamaños que puedes elegir en este caso, Esos viven y ven 2 32 Así que solo voy a usar esa lista, acuerdo? Y ahora soy capaz de seleccionar cualquier combinación de productos y tamaños, así que ese es el paso uno. paso dos es escribir la función de coincidencia de índice. Entonces vamos a empezar con el índice igual. Y qué rara re indexación Va a ser la matriz total de precios que podemos elegir . Y como esta fórmula sólo va a vivir en esta sola celda, o puedo presionar que cuatro para arreglar eso o puede dejarlo relativo realmente no importa en este caso, pero ahora voy a comas ya, sobresale buscando el número de vialidad. Entonces, ¿cuántas filas hacia abajo para moverse? Y de nuevo , no tiene sentido. Simplemente dile a Excel, ya sabes, fila 123 o cuatro porque es una función de qué producto ha seleccionado el usuario. Voy por lo que voy a insertar una coincidencia, función aquí, digamos, coincidir con el valor que se pone al usuario en Selby 10. ¿ Y dónde lo voy a buscar? En la lista de productos de B tres a B siete. Por lo que ahora mismo tengo calcetines seleccionados. Esto generará el número uno porque es el primero en esa matriz. Está en la primera fila dentro de esa matriz. Y así la función de índice permanecerá en la fila uno, que es lo que necesitamos. Por último pero no menos importante, el tipo de partido. Voy a hacer cero, porque quiero una coincidencia exacta aquí. Cerrar eso apagado en esa función de partido está hecho. Coma sobre. Ahora estoy en el componente de número de columna de mi fórmula de índice, así que voy a insertar otra función de coincidencia. Ahora vamos a buscar qué tamaño le importa al usuario o qué tamaño ha seleccionado el usuario. Um, ven a la matriz de búsqueda, que va a ser la lista de tallas de C a G dos y luego otra vez coincidencia exacta para el tipo de partido . Cierra eso para terminar la función de coincidencia, y luego un paréntesis más para cerrar la función de índice, y eso debería hacerlo. Por lo que consigo por 25 ahora mismo. Tener unos calcetines pequeños seleccionados, que se ve bien, y yo podría simplemente, ya sabes, Papa redondea diferentes valores para asegurarme de que esté funcionando pantalones pequeños. 25. Ahí vamos. Pantalones extra grandes o 28 camisetas extra grandes. 17. Ahí vas índice partido. 11. Combinar MATCH con VLOOKUP: Por lo que quiero hablar de una aplicación más de la función de coincidencia que puede ser increíblemente útil para ahorrar tiempo y trabajar de manera más eficiente con las funciones de búsqueda. Entonces vamos a saltar de nuevo a esa fórmula de búsqueda que escribimos en las conferencias anteriores. Por lo que estoy en la pestaña de datos de golpear. Estoy mirando la celda P dos encendido y sé que probablemente estás pensando, ¿Qué más podríamos hacer con esta función? Hemos trabajado en tipos de referencia. Hemos cambiado diferentes matrices de tablas. Lo hemos envuelto y si error. Pero hay una última pieza que podemos automatizar para ahorrarnos mucho tiempo en el futuro, sobre todo si vamos a replicar esta función y una serie de lugares. Entonces la pieza a la que me refiero es la columna en Next Number o el número cuatro. Si recuerdas cuando escribimos por primera vez este Siris de funciones de búsqueda, aplicamos la primera función que escribimos a las cinco columnas a la derecha. Entonces aplicamos la fórmula de Colin P a Q R S y T, y lo único que teníamos que cambiar de dedo actualizar esas funciones eran los números de índice de columna . Entonces cambiamos el 4 a 55 a un 66 a 7, y así sucesivamente y así sucesivamente. Podemos hacerlo incluso mejor que eso. Y podemos reemplazar este número de índice de columna por una función de coincidencia que hará lo mismo automáticamente. Entonces lo que voy a hacer es eliminar los cuatro aquí y voy a escribir función de coincidencia. Entonces partido. ¿ Y qué busca valorizador? Va a ser el nombre de la variable o el encabezado de columna variable que busco en este caso Pos o posición. ¿ Y dónde voy a tratar de encontrar eso en la cabeza? Una fila de mi matriz de búsqueda, que está en la pestaña de datos de fielding. Entonces va a ser fielding datos uno a uno H uno. Y luego recuerda, la tercera pieza de la función de partido es el tipo de partido será cero y cerró la función de partido apagado. Entonces ahora lo que estoy haciendo es, en lugar de sólo decir, dame el valor de la cuarta columna, estoy diciendo que me des el valor de cualquiera que lo llames en el que encuentres a P. O. S en el hetero, que en este caso devolvería el número cuatro igual que teníamos. Entonces cuando golpeo enter, ves, devuelve p para pitcher. Todo se ve bien. Pero antes de aplicar esto hacia abajo y más de dos columnas adicionales, tengo un poco más de trabajo que hacer en cuanto a mis tipos de referencia y configurarlos correctamente. Por lo que para poder aplicar esta función hacia abajo sin atornillar nada, necesito asegurarme de que las referencias en mi fórmula de coincidencia estén correctamente establecidas. Por lo que ahora mismo, acabo de decir todo como relativo. Pero lo que tenemos que hacer es si dejamos el P uno relativo que si arrastro esta fórmula hacia abajo una fila, va a tratar de igualar los valores de P dos, lo cual es una tontería. Por lo que necesito recorrer el f cuatro. O sea, lo golpeé dos veces hasta que acabo de arreglar el camino uno. Ahora, cuando arrastre esto hacia abajo, siempre va a pullin lo que sea la etiqueta de encabezado que esté en la primera fila, para que ese número de fila nunca cambie. De igual manera, independientemente de qué variable o qué encabezado estoy buscando, esos siempre vivirán en un A uno a h uno. Entonces voy a arreglar eso por completo. Y entonces la última pieza que tengo que hacer aquí es fijar la referencia A a H en la tabla de búsqueda. De esa manera puedo arrastrar esta fórmula hacia la derecha sin que el octavo o H cambie por algo fijo la A a la H, y eso debería apenas hacerlo. Tan hit. Entrar. Prueba esto aplicándolo hacia abajo. Todo se pobla correctamente. Eso es genial. Y ahora aquí está la llave. Cuando vuelvo a aplicar esto, no debería tener que cambiar nada. Todo debería actualizarse adecuadamente, lo que hace ahora. Lo que haré es que me llevaré esos cuatro, los aplico. Es calculador. Ahí vas. Entonces, como pueden ver, lo único que está cambiando ahora en mi fórmula lookup es el valor lookup en mi función match . Entonces en este caso, está mirando hacia arriba. El nombre del encabezado put outs en el array Fielding Data, que está en la quinta columna sobre en este caso, está buscando arriba son uno que es el nombre del encabezado asists, que está en la sexta columna sobre y así sucesivamente y así sucesivamente. Por lo que está haciendo exactamente lo mismo que nuestro número de columna original, excepto que ahora está completamente automatizado, lo que podemos escribir una función de búsqueda y aplicarla a través de un número de columnas y un número de filas sin tener que tocar nada. 12. Actualización: corrección de VLOOKUP: lo que a unos meses de publicar, este estudiante del curso me mandó un mensaje y señaló que en realidad hubo un error en la sección de ejercicios de búsqueda, y tiene toda la razón, y te mostraré lo que está pasando aquí. Si entras en la pestaña de datos de fielding en el cuaderno de trabajo de referencia de búsqueda, realidad verás que hay múltiples instancias de ciertas claves de jugador, y la razón que sucede es porque ciertos jugadores pueden haber jugado en múltiples posiciones incluso en el mismo año. Entonces, por ejemplo, Bobby, un valiente en Rose cuatro a seis, está registrado como campista izquierda, un outfielder y un campista derecho, todos generando la misma clave porque la posición no es incluido. Y lo que eso significa es que nuestra función lookup es buscar sus ideas de jugador. Tecla de jugador, deteniéndose en fila y devolviendo los apagados, errores de asistencia y jugadas dobles asociadas a la primera instancia de la clave, que no devuelve datos. Y si saltamos a la ficha de bateo, Bobby, un valiente, está aquí mismo en la fila cuatro, y como pueden ver, tiene ceros en todo el tablero, a pesar de que ese año de 2010 tuvo 240 para poner fuera siete asistencias, seis errores en dos jugadas dobles. Ese dato es invisible para la V. Mira hacia arriba porque ahora estamos lidiando con una relación de uno a muchos. Tenemos una clave en nuestros datos de hit, y tres claves aquí son datos de campo. Entonces, ¿cómo nos enfrentamos a esto? Una opción sería transformar realmente este conjunto de datos de campo y agregados en el jugador de su nivel. Es decir, eliminar la posición, el campo y el agregado o algunas las métricas y columnas e a través de H. Pero hay una manera más fácil, y en realidad es usar la función algunos ifs que cubrimos en la fórmula de estadísticas del curso. Entonces volvamos a saltar a la pestaña de bateo. Voy a copiar los encabezados de columna y lindo 30 pegarlos comenzando y columna usted. Voy a darle un color diferente solo para que pueda mantener mis funciones de look up match por aquí y compararlas lado a lado contra este nuevo enfoque, usando algunos ifs. Entonces empezaré por teclear el sol. Esta fórmula es igual a algunos ifs. Ahora bien, si recuerdas la primera pieza, el primer componente de la función de cumbres es el rango de suma ¿Cuáles son los datos que estás intentando? En realidad, algunos aquí. En este caso, son mis datos de fielding. Colin E. Que se apaga va a dejar a ese pariente de referencia, y te voy a mostrar por qué en un minuto. El segundo componente es el rango de criterios. Entonces, ¿dónde busco cumplir con algunos criterios? Bueno, es una clave de jugador, que es la columna A Presionaré F cuatro para bloquear esa columna y luego el último criterio de pieza . Uno. ¿ Qué criterios le llama necesidad de cumplir? Y eso es volver a mi bateador Data Player Key, que es y vender ocho a. En este caso, voy a arreglar sólo la A sólo la referencia de columna para que mi rosa conciosa hacia abajo mientras aplico esta fórmula hacia abajo. Por lo que ahí lo tienes, una prensa entrar y aplicarlo hacia abajo. Ahora eso se calcula, verás una gran cantidad de valores coincidentes con algunas excepciones, Así que una excepción es Bobby, un valiente en la Fila cuatro, donde habíamos estado grabando cero puestos outs con la V. Mira hacia arriba ya 're grabando 242 porque esencialmente estamos sumando todos los valores en la columna e donde la columna a es igual a valiente B 0012010 de los cuales hay tres instancias un caso similar con el siguiente jugador. También hubo tres instancias de su nombre. En su caso, generó 16 put outs en segunda base, 11 en tercera base y 16 en shortstop, que totaliza 43 put outs Con el enfoque V look up, capturamos 16 que es sólo la primera instancia. Pero ahora que hemos pasado a un enfoque de cumbres, estamos capturando todos esos put outs, los 43. Y debido a que configuro correctamente mis tipos de referencia, Lo que puedo hacer ahora es incluso simplemente desplazar esto a las columnas restantes. Ya que sé que estas columnas son secuenciales y eso me permite básicamente solo permitir que esa columna coma un turno a F, G y H, y podría simplemente agarrar esas celdas DoubleClick y aplicarlas hacia abajo. Por lo que ahí lo tenemos. Hemos contabilizado el hecho de que ya no teníamos una relación 1 a 1 con nuestra clave, y hemos logrado los resultados que buscamos usando una función estadística en su lugar . Ahora, la única salvedad que voy a hacer es que este trabajo, porque estamos tratando de devolver valores numéricos aquí, que algunos ifs pueden entender. Si en cambio estamos tratando de devolver posición, por ejemplo, que es un campo de texto. De ninguna manera podría haber usado algunos ifs para lograr esto, porque Excel no sabe agregar P y ser un L f o cualquier otra cadena de texto. Entonces de nuevo, si estás trabajando con valores, a veces puedes usar el look up o algunos ifs indistintamente. Otras veces necesitarás usar solo una u otra. 13. La función de OFFSET: está bien, así que la última función de referencia de la que quiero hablar Se llama Offsets tipo de una función menos conocida. Pero es una realmente útil a la hora de hacer algún tipo de cosas únicas, poderosas que te mostrarán en la próxima conferencia. Pero básicamente solo para darte algún trasfondo, es similar al índice. Devuelve el valor de una celda dentro de una matriz, pero también puede devolver una referencia a un rango específico de celdas, lo cual puede ser muy útil cuando se desea crear un aumento dinámico. Y sé que eso suena como galimatías. Tendrá más sentido en la próxima conferencia en promesa. Entonces, básicamente, compensaciones sensatas de impuestos sin comienza igual que una función de índice. Determinas una referencia, ¿ cuál es tu punto de partida? Por lo general, eso es una referencia de una sola celda, y luego tienes un componente de rosa, lo que te dice bien, ¿cuántas filas hacia abajo debo mover? Y luego columnas, ¿ cuáles son cuántas columnas más de la referencia debo seguir adelante? Y si lo dejas así, funcionará exactamente como un índice. Te dará el valor de lo que sea que venda que aterrice. Lo que hace que el offset sea diferente es que incluye estos dos componentes adicionales altura y anchura, uh, lo que le permite devolver una matriz multidimensional en lugar de solo el valor dentro de una sola celda. Por lo que estos son opcionales, razón por la cual se muestran rodeados de paréntesis. Y si dejas esos en blanco, si simplemente cierras tu fórmula después del indicador de columnas, uh, nuevo, será igual que un índice sobre la altura y el ancho por defecto dedo uno y uno, así que vas a devolver el valor de una celda que tiene una fila de alto y una columna de ancho, punta pro rápida Aquí. Te estoy mostrando todas estas funciones como Offset Index Match, que tienen aplicaciones muy específicas en y en algunos casos, muy particulares. No te pongas demasiado atrapado en usarlos sólo por el bien de usarlos. Por lo tanto, no uses un offset o en el próximo partido, cuando un simple V look up hará el truco. Entonces con eso, así funciona la función offset. Te voy a mostrar cómo se puede usar para construir un scroll dinámico realmente genial en el gráfico en la próxima conferencia, así que prepárate 14. Combinar OFFSET con COUNTA: Entonces, antes de sumergirnos en el ejemplo de gráfico de ardilla, que es realmente, realmente genial, te lo prometo. Quiero mostrarles otra aplicación muy común de la función offset, que es combinarla con una sencilla función de estadísticas llamada Cuenta A. Ahora hay un ejemplo de esto en el ejercicio de tarea al final del capítulo, pero quiero te muestran un ejemplo muy básico solo para darte una sensación de lo que se trata . Entonces en este cuaderno que estoy viendo aquí, obviamente es extremadamente complicado. Tengo seis elementos en una lista en la columna A Lo que puedo hacer es usar el conteo un tipo de función equivale a contar día para contar los valores no en blanco o celdas dentro de un rango. En este caso, mis rangos, toda la columna A. Cuando cierre eso, devolverá un seis ya que sus seis valores no en blanco justo aquí y lo llaman ahora, ¿qué tiene eso que ver con offset? Bueno, ¿y si quisiéramos devolver el último elemento de la lista? No importa cuánto tiempo sea esa lista? Um así que digamos último ítem y justo aquí en D dos empecemos con una función offset esta vez. Y entonces lo que vamos a hacer aquí es que vamos a compensar desde el inicio de la lista 81 Y el número de filas que vamos a bajar es contar una función. Así que contar a esencialmente significa que estamos haciendo dinámica esta función de desplazamiento para que pueda identificar el último elemento de la lista incluso cuando se agregan o eliminan datos y el tamaño de esa lista crece o se contrae. Entonces tomaremos el día de conteo de toda la columna A que sabemos devuelve un seis. Y ahora, antes de seguir adelante, esto me está diciendo que voy a mover seis filas hacia abajo de una que me llevaría a una 234567 que está en blanco. Hemos ido un camino demasiado lejos. Entonces todo lo que necesito hacer para dar cuenta de eso es Adam menos uno ahí mismo. Y no quiero desplazar ninguna columna porque solo tengo una columna de datos. Entonces voy a poner cero ahí y podría ignorar la altura y el ancho por ahora. Tan sólo cerrar mis instalaciones. Entonces ahora cuando golpeo, Enter me dice que mi último elemento es una lima, que es bastante cool, pero la belleza de ella es que puedo agregar valores aquí y ese valor de D a cambia en consecuencia. Entonces uva, cereza y lo mismo va ya que borro valores que también se actualizan, así que eso es bastante fresco por sí mismo. La otra aplicación realmente valiosa de esto es en realidad con gráficos y gráficos donde puedes definir tus datos de origen usando un offset con Count Day, que a medida que agregues nuevos datos a tu matriz de origen, tu gráfico se actualizará automáticamente . Entonces para más información al respecto, echa un vistazo a Mi Excel fecha de es curso de gráficos avanzados y gráficos, y te mostraré algunas demos de cómo funciona eso. Pero ahí tienes, combinación básica de Offset y Count a. 15. Contáctate de proyectos: usar OFFSET para crear un cuadro de desplazamiento dinámico: Está bien, Así que es hora de poner a prueba a Excel. Veamos qué podemos hacer para crear un gráfico a diferencia de la mayoría de la gente alguna vez ha visto en Excel. Adelante y abre la Sección cinco Buscar archivo de referencia. Voy a saltar a la última pestaña aquí, pestaña de gráfico de ardilla. Y nuestro objetivo aquí va a ser crear un gráfico con una referencia de datos dinámica. Eso lo vamos a hacer usando la función de desplazamiento. Y el objetivo va a ser crear un gráfico que podamos hacer zoom y ardilla para cambiar la vista de los datos que estamos viendo. Entonces vamos a ir más allá de los gráficos estáticos que el 99% de los usuarios de Excel están por ahí usando así que un poco de artículos de limpieza. El primero es que vamos a usar barras de desplazamiento. Y para hacer eso, necesitas insertarlos a través de la pestaña de desarrollador. Así que echa un vistazo a la parte superior de la pantalla de tu celular si no ves la pestaña de desarrollador donde debes hacer Y esto es en 2013 ojos yendo a la pestaña de inicio, Click opciones personalizar cinta. Y luego por aquí a la derecha, deberías ver una casilla de verificación junto a Años de desarrollador es probable en cheques para seguir adelante y darle un cheque. Presionar OK? Y deberías verlo aparecer. Um, si la experiencia del usuario es un poco diferente y excel 2010 o por usar una versión más antigua, honestamente, solo googlearla y asegúrate de tener activada tu pestaña de desarrollador. Entonces estoy en Desarrollador. Voy a ir al botón de inserción y encontrar este control de formulario de barra de desplazamiento. Haga clic en él. Voy a arrastrar uno al lado de este bolso de línea de pergamino, acuerdo? Y voy a hacer exactamente lo mismo y arrastrar un segundo junto a esa línea de zoom. Uh, y si quieres editar la apariencia de ellos, puedes controlar click ellos y hacer que ese un poco gordo. Y soy perfeccionista, así que voy a querer algún tipo de que esté cerca de Idéntico no se puede. Eso está bien. Entonces ahora vamos a hacer es editar lo que realmente hacen las gráficas de ardilla de la realidad. Entonces, um, voy a dar clic derecho en el 1er 1 y hacer clic en control de formato. Entonces esto es básicamente decir, ¿qué valores puede tomar esta palancla? A qué vende aire le vincularon básicamente cuáles son estas barras de desplazamiento que van a controlar. Entonces en este caso, la barra de desplazamiento de desplazamiento. Yo quiero tener un valor mínimo de cero. Voy a establecer el valor máximo en seis incrementos de uno y cambio de página. Eso está bien. no me importa realmente. El enlace celular va a ser la celda D 16 que está justo al lado del texto del índice de desplazamiento. Entonces eso es todo para esa. Voy a hacer clic derecho en el zoom Barra de desplazamiento, Haga clic en Formato de nuevo. En este caso, quería tener un valor mínimo de uno Un valor máximo de 12 y verás por qué en un poco el de la 10 o bien. En este caso, la venta va a ser D 17 junto al texto del índice de zoom. Así que golpeó. Está bien. Y ahí tienes. Hemos configurado ambas barras, y ahora, y ahora, si solo haces clic, verás que están vinculadas a los valores de celda en D 16 y 17. Entonces ese es el paso uno. El segundo paso va a ser trabajar con algunos nombrados una carrera que van a alimentar nuestra referencia de datos de gráfico . Entonces voy a entrar en fórmulas nombre Gestor. Este es mi nombre de fielding. o un solo puedo dejarlo como va a crear uno nuevo. El primero se va a llamar Scroll Index, y eso sólo se va a referir a vender d 16. Por lo que presioné el pequeño selector de datos. Presiona otra vez. Entonces una carta de ardilla d 16 bien. Y crear otro llamado Zoom Index, y eso sólo se va a referir así el 17. Por lo que he creado dos nuevos nombrados una raza llamada Scroll Index y Zoom Index. Por lo que aquí es donde se pone un poco más complicado. Tengo mi índice escolar. Tengo mi índice de zoom. La siguiente pieza que necesito, um, es la matriz dinámica que se va a alimentar en mi gráfico. Y voy a necesitar al menos dos de estos para mis valores ex, que son los meses y uno para mis valores y, con los que empezaremos trazando impresiones. Si quisiéramos trazar tanto las impresiones como los clics, necesitaríamos nombrar matrices para cada serie de por qué Valores. Entonces, empecemos con los valores X aquí. Voy a crear un nuevo Array llamado. Voy a llamarlo X valores y anotar exactamente cómo escribes estos nombres para elevar porque necesitaremos usar exactamente la misma sintaxis en el mismo nombre exacto con las mismas mayúsculas y minúsculas una vez que las alimentemos en los gráficos. Entonces estoy usando Capital X y en minúscula V. Así que mis valores ex van a referirse a una fórmula, y esa fórmula va a ser un offset. Entonces tipo offset y mis valores ex viven en la columna B. Así que mi referencia o punto de partida será de tres. Y luego recuerda la segunda pieza de la función molesto, que fue ¿cuántas filas hacia abajo quieres mover? Es un poco más difícil ahora porque no tenemos la caja auxiliar tipo de guiarnos a través de la fórmula. Esto es una especie del Salvaje Oeste. Estamos por nuestra cuenta aquí, así que necesitamos recordar cómo construyeron las fórmulas offset. En todo caso, la segunda pieza es cuántas filas abajo en. Y eso depende de cuál sea el valor de la barra de desplazamiento que vive en tan d 16 que está atado al índice de desplazamiento llamado Array. Entonces voy a escribir índice de desplazamiento aquí La segunda pieza, ¿Cuántas columnas vencidas? Yo quiero moverme en este caso, mis valores ex o mis meses solo viviendo columna B. Así que no quiero mover ninguna columna en absoluto. Entonces en tipo de cero ahí, la siguiente pieza va a ser la altura de la matriz que quiero devolver. Eso Esto depende de la barra de desplazamiento de zoom eso cualquier valor, Uh, zoom Barra de desplazamiento tiene salida en D 17 es la altura de la matriz que quiero alimentar en el gráfico porque ese es el número de meses que quiero mirar cualquier vista dada, ya sea crujido hacia abajo a uno o dos meses o se alejó a los 12. Entonces ese sólo va a ser mi índice de zoom llamado Array. Y luego por último pero no menos importante, el ancho de mi referencia sólo va a ser uno porque es una columna de ancho cerca, el paréntesis y golpeó OK, y eso se creó mi campo de valores ex. Um, voy a crear mi campo de valores y exactamente de la misma manera, así que va a ser una función de desplazamiento. La única diferencia es que el punto de partida, en lugar de ser B tres, va a ser el campo que quiera aquí una trama. Entonces, trazar impresiones para este ejemplo. Entonces mi punto de partida será C tres y luego recuerda cuántas filas hacia abajo va a ser índice de desplazamiento? Uh, cuántas columnas sobre cero. ¿ Cuál es la altura? Índice de zoom y ¿cuál es el con uno? Cierra el paréntesis y pulsa OK, Y ahora he creado una matriz de valores X y una matriz de valores Y junto con mi índice de desplazamiento y mi índice de zoom. Así que adelante y cierra el nombre manager. El último pedazo que necesito hacer es poblar este gráfico que básicamente he montado un caparazón de aquí mismo. Por lo que solo voy a hacer clic derecho Seleccionar datos. Voy a añadir un dato. Siri está aquí. Nombre serio va a ser impresiones, ya que ese es el campo con el que voy para mis valores Lai y los valores de Siri, en lugar de solo seleccionar una celda individual o rango de celdas aquí, lo que voy a hacer es hacer una selección sólo para darme esa parte inicial de esta referencia porque quiero el diagrama de desplazamiento y el signo de exclamación. Pero voy a borrar todo siguiendo el signo de exclamación, y voy a reemplazar todo eso por por por qué los valores escritos exactamente como tenía que encontrarlo. Así que desmarque ese golpe, ¿de acuerdo? Ya se puede ver de fondo cosas que están empezando a poblar. Y ahora, en mis etiquetas de eje horizontal o de categoría en una pulsación, el botón de edición y el rango va a volver a ser en lugar de esta matriz fija. Voy a hacer una selección sólo para poner el punto de partida en su lugar, eliminar todo siguiendo el signo de exclamación, y voy a poner valores X aquí. Mi cabeza. De acuerdo, ya verás que nuestro eje X se ha actualizado para mostrar nombres meses y ahí vamos. Entonces, como pueden ver, estamos trazando impresiones y ahora, como barra de desplazamiento de zoom de movimiento el ancho de nuestra vista, que en realidad es capturado por la altura de esta matriz, los ojos cambiando. Y de igual manera, si cambio el índice de desplazamiento ahora toda nuestra vista, muy amplia o que se hayan reanudado, dijimos que se está moviendo de izquierda a derecha en el gráfico. Entonces ahora estoy viendo seis meses de datos y estoy viendo cómo tendencias mes a mes mientras acerco, puedo mirar solo una sola fecha a la vez o dos meses o tres y así sucesivamente y así sucesivamente. Um, entonces si queríamos incluir clics como secundario, por qué valor Eso es bastante fácil de hacer. Sólo vamos al encargado de nombres. Cree un valor Y a Array llamado, y luego simplemente cambie el punto de inicio de esa función de desplazamiento a D tres. Y el resto será exactamente lo mismo. Um, así que ahí vas un gráfico dinámico presumible de desplazamiento usando la función de desplazamiento. 16. HOMEWORK: funciones de búsqueda y referencia: Está bien. Muy bien hecho. Has completado el capítulo de referencia de búsqueda. El curso, Definitivamente Uno de mis favoritos ojalá encontrara algunas cosas útiles ahí dentro. Y para aquellos de ustedes que quieren un poco más manos en práctica, tengo algunos grandes ejercicios de tarea para ustedes. Así que adelante y abre los ejercicios de tarea de Excel. Libro de trabajo, Vamos a empezar en la ficha de funciones de búsqueda. Aquí tenemos tres instrucciones. Esto va a utilizar las siguientes dos pestañas en abreviaturas estatales grises e ingresos estatales. Una vez que hayas envuelto los ejercicios de funciones de búsqueda, sigue adelante y salta directamente al índice y coincide. Ejemplo. Y luego por último pero no menos importante, tengo algunas demos geniales para Offset y Count Day. Um, buenas preguntas desafiantes. Esperemos que te ayude realmente a tener un buen entendimiento de cómo funcionan estas funciones. Por lo que nuevamente, el archivo de respuesta está disponible en el curso. Recurso es. Pero si necesitas ayuda, no dudes en llegar. Buena suerte