Las 10 mejores fórmulas de Excel para trabajar de forma más inteligente

1. Excel: más de altos de forma de trabajar: Hola. Mi nombre es que miren a mi alrededor. Ayudo a las empresas a utilizar mejor Excel brindando capacitación personalizada e implementando tableros y mejorando las técnicas de visualización. En este curso de Excel, te mostraré cómo ser mejor, más rápido en diseño, hojas de cálculo sólidas que impresionarán a los demás. Cuando empecé a usar Excel on, me introdujeron para ser Buscar, lo usé en cada situación y muchas veces tuve que cambiar la configuración de mis datos en mi mesa para que funcionara. Eso fue tan frustrante, pero nunca se me ocurrió averiguar si hay mejores formas de hacer las cosas. Mi principal objetivo en este entrenamiento es aumentar tu conciencia fuera de las mejores herramientas y fórmulas en Excel para ayudarte a resolver problemas complejos. Es solo cuando conoces la extensión y posibilidades que Exel te ofrece. Sólo entonces podrás manejar cualquier tipo de análisis utilizando el mejor método. Ahora hay un puñado de fórmulas de Excel, que creo que todo usuario que se ocupa de Excel de manera frecuente debería saber en esta sección. Te enseñaré de una mirada arriba en caso de que no lo sepas. Pero te llevaré más allá de la mirada hacia arriba y miraré otras funciones extremadamente útiles que pueden manejar las consultas más complicadas para un análisis más rápido es con estas funciones. Estás bien equipado con los conocimientos que necesitas para manejar cualquier situación que requiera analizar datos o agarrar una pieza de información de una mesa grande. Aprender a ser rápido y efectivo en Excel lleva tiempo y practica, así que por favor no te saltes los ejercicios. También sé que la capacitación técnica puede ser aburrida, sobre todo la formación técnica en línea, donde solo estás mirando a alguien pantalla. Por lo que he tratado de llevar la interactividad y este ambiente de enseñanza en vivo fuera de un aula mi formación en línea para asegurarme de que se beneficien. ¿ Estás listo para aprender algunas técnicas avanzadas? Entonces empecemos. 2. Formula 1: SUMIFS, COUNTIFS, y de los de la AVERAGEIFS para responder excepciones: ¿ Alguna vez necesitó dar una respuesta a esta pregunta? Por lo que se le dijo que proporcionara un total de cuatro, por ejemplo sus ventas, pero no solo para engañar a algunos solo por un determinado producto, un mes específico y por un país específico. Ahora, básicamente, si alguna vez necesitas hacernos algunos con excepciones y sobre todo si estas excepciones son más de una cosa que normalmente es el caso en la vida real que esto algunos si s función es para ti. Aviso? No estoy diciendo algunos si, pero algunos si s ahora, podrías ya estar familiarizado con esto algunos si versión simplemente no con el plus s one. Pero en realidad ha aparecido desde Excel 2007 y sorprendentemente mucha gente lo perdió. Pero en realidad es una función de ahorro de tiempo, sin embargo. Ahora, ¿cuál es la diferencia? ¿ Podría preguntar? Bueno, algunos si sólo pueden comprobar una excepción, mientras que la versión if s puede comprobar hasta lo que creo que son 29 criterios. Ahora que alguna vez necesitarás tanto. No. En esta conferencia, les voy a mostrar la función de cumbre así como la suma si s version el promedio si s es similar al festival de la cumbre excepto que solo comprueba el promedio en lugar de los algunos cuentan. La defensa es ligeramente diferente. Te voy a mostrar eso también. Si nunca has acostumbrado a alguna función if antes, te recomiendo que no aprendas a usarlo ahora y acabas de aprender si s versión. El motivo de esto es que la versión si s puede hacer más que la versión if en. Porque la sintaxis de los dos difiere. No siempre tienes que pensar cuál debería usar Ahora? Yo personalmente uso la versión FS. Dicho esto, voy a empezar nuestro ejemplo mostrándote la suma si versión de la misma y solo te explicaré y d diferir y cómo se diferencian aquí tenemos productos de un mes país y ingresos de ventas de estos datos quisiéramos encuentra nuestros ingresos totales por ventas para estos criterios. Entonces el primero anuncia que queremos hacer es ¿cuántos ingresos tuvimos en febrero? Porque es sólo uno de los criterios estaban revisando. Puedo usar a algunos si funcionan los impuestos del pecado que primero viene el rango que rango. No es el rango algunos porque el rango algunos está al final, Así que el primer rango aquí es en realidad el rango de criterios. Entonces es el lugar donde se pueden encontrar estos criterios, y eso es básicamente aquí. Entonces voy a dejar de fumar aquí y luego usar mi corto, crikey control shift down. A continuación están los criterios en sí, que es éste y luego es el rango de suma. Entonces hago lo mismo. Controlar, cambiar hacia abajo y comprobemos eso. Eso se ve bien. De acuerdo, así que esa era la suma Si la función es muy simple, se puede ver que el algunos rango llegó al final. No, tenemos que crear área que queremos comprobar. Por lo que queremos conseguir esto algunos si es febrero y si nuestro producto es camisa bastante. Y aquí es donde a algunos si s función entra en juego. Esta función se introdujo desde Excel 2007 y facilitó tanto la vida de tantas personas porque ya no tenían que usar conflictos, fórmulas de matriz para hacer este tipo de cálculos. Podrían usar una fórmula si s muy simple. Y la forma en que funciona es que ahora el rango algunos está al principio, porque cuando mi misma programa esta fórmula. No pudieron dejar el rango algunos al final porque no saben cuántos criterios vas a estar poniendo en tu fórmula. Entonces tenías que venir al frente y obviamente no podían cambiar la vieja fórmula porque entonces no sería compatible con las versiones anteriores de Excel. Por eso se introdujo esta nueva función. Así son algunos de los rangos es nuestros ingresos. Y lo siguiente es el rango de criterios uno criterios rango dos criterios a y espada, dependiendo de cuántos criterios diferentes tengas en este caso, tenemos que hacerlo. Entonces el primer rango de nuestros criterios donde realmente encontramos febrero es éste. Después vienen los criterios reales. Y ahora pasamos al rango de criterios también. Entonces, ¿dónde podemos encontrar esto aquí? Y estos son los criterios reales estaban revisando para Vamos a comprobar eso también. Por lo que tenemos camisa fab. Blanco. ¿ Este es este tour de cuatro para ayudar gira a ocho? No. En el siguiente ejemplo, nos han dado un criterio más para decir camisa de febrero blanca y las ventas que teníamos en la U. S. Así que hacemos lo mismo solo en realidad voy a copiar esto. Simplemente mueve estos hacia abajo por las gamas, aire igual. Entonces todo lo que tengo que hacer es agregar un criterio adicional para U. S. Un primero es el rango donde puedo encontrarlo, y luego es el criterio real en sí. Vale, 16181618 Otra cosa que puedo hacer es usar comodines y operadores de comparación como más grandes o más pequeños o una combinación de thes. Entonces, por ejemplo, si en lugar de camisa ancha en realidad estaba buscando el texto dentro hay que poner comillas y poner camisa w asiática, entonces poner las comillas signo estrella. Lo encuentra. Eran como si me quito eso no encuentra nada por lo que puedo usar este letrero para encontrar cualquier cosa que empiece con camisa y luego tenga wh en ella. Ahora déjame mostrarte cómo usar los operadores de comparación. Entonces si tenemos esto, Pero en lugar de USA, queremos sumar los ingresos por ventas sólo si es mayor a 500. Si no es mayor a 500 no debería cumbre. Entonces ahora escribamos esa. Entonces lo hacemos Yo rápido. Algún rango. Siguen siendo nuestros ingresos por ventas. Zona loca uno. Es febrero. Crear área a sus productos. De acuerdo, ahora llegamos a criterios. Tres. Entonces queremos Onley algunos si es si los ingresos son mayores a 500. Entonces en este caso, ¿cuál es nuestra zona de cajas? Es el ingreso. Es éste. Por lo que puedes ser o algún rango. Pero también puede ser nuestro criterio, y el criterio en sí es que es mayor a 500. Comprobemos eso. Entonces tenemos seguro, Dwight, éste es que Esto no es mayor a 500. Esto es sí, y ese CS menos. Eso es 112611 de marzo al 6. El único que debes tener en cuenta al agregar los operadores de comparación es que necesitas ponerlos si tú Si no lo estás poniendo en una celda y realmente quieres ponerlo en una fórmula, no puedes hacerlo. Obtén un error. Necesitas ponerlo entre comillas porque es como texto. No tienes que poner necesariamente tu número en las obras de cotización. También puedes hacer esto, o puedes tener tu número en una celda y hacer una sub referencia al número como este. Entonces básicamente tener la mayor ahí dentro y luego y conectándola a esta celda. El caso de simplemente olvidar cuando estás agregando estos operadores, ponlos entre comillas. Esa es la función del festival de verano. El promedio si s funciona en la misma. Exactamente. La única diferencia es que toma el promedio en lugar de los algunos. Entonces en este caso, dirías promedio si está bien, no está tomando el promedio de los ingresos para Feb y camisas blancas. El conteo es ligeramente diferente porque no se necesita uno de estos criterios. No necesitas que algún rango. Entonces déjame mostrarte el conteo. Si primero solo necesitas rango y crear área. Entonces sólo somos una cuenta. Si en este rango hay febrero entonces ¿cuántas ocurrencias de febrero podemos encontrar aquí? Eso es un rango. Ese es nuestro criterio. Ocho. Por lo que tenemos febrero 8 veces. Eso es correcto. Ahora la versión if s nos permite comprobar más criterios y contar en base a estos. Entonces decimos que el rango de criterios uno es el mismo. Entonces ese es nuestro mes secretario arreglado. Esos son nuestros productos. Entonces cuatro, básicamente tenemos cuatro ocurrencias de febrero y camisa blanca aquí dentro. Es éste, Este Dónde está el otro en éste y el mismo para esto. No tenemos tres criterios para revisar de nuevo. Haces lo mismo tres, uno a y tres lo hicieron. Cualquier cosa que te voy a mostrar es la ventaja de las tablas excel. Entonces digamos en este ejemplo alguien te dice, Oh, nos faltaba algo en febrero. Tienes que sumar este número. Entonces solo voy a poner un gran número. Digamos que éste. Obviamente, tus valores aquí en tu reporte no van a actualizarse porque no están haciendo referencia a esta nueva línea. Tendrás que expandir manualmente estos, pero para evitar eso, puedes usar tablas excel y venden tablas son geniales para este tipo de casos. Por lo que todo lo que tienes que hacer es hacer clic en cualquier parte de una celda y presionar el control T. Estos son los datos de una tabla, y tiene encabezados pueden. Ahora es una mesa oficial. Y sabes que una vez que consigues esta tabla herramientas activadas aquí y ahora si agrego mis nuevos datos a esto tan corto, blanco. Mis números se actualizan automáticamente. Hagamos click en uno, y se puede ver que el rango se actualizó dinámicamente. Esa es la ventaja de las tablas de Excel. Ahora. Generalmente nunca me gusta este diseño que me da así que quitar que solo vas al diseño y dijiste que claro. Vuelves a tu propio formato fuera de la mesa. El otro necesita pensar en esto es que si eres tabla es realmente larga, ves que cambia tus encabezados de columna de Excel a los Encabezados de tabla. Cuando te desplazas hacia abajo dice algo así como un dolores de congelación parciales, solo lo hace por aquí. Y una vez que estás fuera de tu tabla para ver los encabezados de columna habituales, ya que puedes ver que si la versión S te permite agregar fácilmente excepciones a tus cálculos promedio y conteo, pero no hagas tu vida más difícil al intentar para usar filtros de Excel para llegar a tu algún uso, la versión FS en su lugar sabe retorno. Adelante y hacer el ejercicio para esta conferencia 3. Fórmula 2: que funcionas en la: ¿ utilizas si en tus oraciones diarias? Entonces cuando hablas con tus colegas, tu jefe, especialmente con tu jefe, Bueno, Bueno, si lo haces, lo más probable es que necesites usar si en tus archivos de Excel también. Pero probablemente conozcas la función if de una formación básica de Excel. Pero es posible que no esté familiarizado con su uso en met como fórmulas anidadas reunión usando más de una si en una fórmula. Porque la realidad es que siempre hay excepciones a las excepciones. Todavía tengo que ver una empresa donde el análisis y la estructura fueran realmente simples. En este ejemplo, tenemos a nuestros agentes de ventas, la cantidad que se vende sobre los ingresos que obtuvieron. Y nos gustaría hacer algún análisis en base a esto. Por lo que quisiéramos abanderar a los agentes de ventas que vendieron tres o menos. Ese es nuestro primer paso, y vamos a empezar de nuevo por ahí si la función la primera parte es básicamente lo que estamos revisando en este caso, queremos comprobar si la cantidad es menor o igual a tres. Entonces queremos revisar este número, y si este número es menor o igual 23 entonces qué queremos hacer. Si es cierto en este caso, quiero banderlos. Por lo que en realidad quería Escriba la palabra bandera ahí dentro. Tengo que ponerlo entre comillas. También podría hacer una referencia celular y hacer referencia a los agentes si quisiera tener su nombre ahí. El último argumento es qué hacer si es falso. Entonces, ¿qué hacer si la cantidad es en realidad mayor a tres? En este caso, no quiero hacer nada. Deja la celda vacía porque me gustaría filtrar o hacer análisis en los agentes de bandera para este caso. No debería conseguir nada, porque eso es lo que puse ahí. Nada. Pero para éste, éste y éste, debería conseguir una bandera, que hago. Y no olvides que la diferencia entre la función if sola y la versión de cumbre es que con si tienes control total para cambiar todo el resultado de la situación, mientras que con los algunos, si estás restringido a sumar eso resultado, pasemos a nuestro próximo análisis. Queremos bandera si el número que vendieron es menor o igual a tres, y también hicieron dinero que era menor a 200. Siempre que tengas tus sentencias no han terminado en ella. Tus fórmulas tendrán un fin en ella también. Usaremos if, para una prueba lógica. Vamos a usar la función final porque estamos revisando dos cosas. En este caso, una son cantidades del 1er 1 ¿Esta celda es menor o igual al árbol? Y si ingresos este es menor a 200 cierre de corchete. Entonces esa fue nuestra prueba. ¿ Qué debe hacer? Si esto es cierto, debería banderlos. Si es falso, No hagas nada. No. Nos avisan para estos dos sólo porque éste tiene un ingreso mayor a los 200. A pesar de que las cantidades menos de una manera similar, también se puede utilizar la función o y o se verificaría si se entiende cualquiera de estos criterios. Entonces fue bandera pasando a análisis más complejos, que en realidad es más o menos el caso en la vida real. Entonces aquí queremos ver si los ingresos son mayores o iguales a 300 si la cantidad es más de cuatro y debemos darle un regalo a estos agentes para que se queden conmigo aquí. De acuerdo, lo contrario, si los ingresos se rallan en 50 deberíamos alabarlos. De lo contrario lo dejamos vacío fue empezar a escribir esto. Empezamos con un If, no olvides, tu fórmula en realidad es muy similar a la forma en que explicarías algo. Ya sabes, como si este fuera el caso, entonces hacemos eso. De lo contrario hacemos eso. Es el mismo flujo en la fórmula. Es sólo que sin el texto. Entonces, ¿cómo empezaríamos a escribir esta sesión? No lo he hecho si y aquí no lo he hecho. Y así si ambos se cumplen, entonces les vamos a dar un regalo. Empiezo con el final. Los ingresos son mayores o iguales a 300. La cantidad es mayor para igual a cuatro. Esa es mi prueba lógica. ¿ Qué debe hacer? Si es cierto, deberíamos darles un regalo. Ahora, Si no es cierto, ¿qué debe hacer? Deberíamos revisar de nuevo. ¿ Los ingresos son mayores o iguales a 2 50? Y eso es otro. Si es así, entonces vuelve a comprobar. ¿ Los ingresos son mayores o iguales a 2 50? Y si lo es, ¿qué debe hacer? Debemos alabar a estos agentes, De lo contrario dejar vacíos. No creas que siempre nos ocupamos de tus corchetes, muchos dos corchetes y ahí está yo sólo acepto. Y esa es mi fórmula. Es prueba en este caso es Tree 100. Estoy vendiendo más de cuatro. Es un regalo. Este es un caso para un regalo, y ese también es un regalo. Estos tipos no cumplen con los criterios, así que los dejamos vacíos. El señor Fórmulas puede ser confuso. Es posible que no obtengas los resultados de inmediato como lo hicimos ahora, por lo que es posible que te encuentres con algunos errores y necesites hacer algo de depuración. Entonces aquí es donde entra mi anterior propina. Para desacreditar esto o para comprobar cómo funciona esto, podemos ir a fórmulas y evaluar esto, algunos violando para vender. Eso me está dando un regalo. Aquí estamos comprobando si C cinco es mayor que esto en realidad está traduciendo ese C cinco al número dos. El valor en la celda y en este caso es 300 mayor o igual al árbol 100. Sí, es cierto. ¿ Seis son mayores a cuatro? Eso es cierto. Entonces el resultado de mi primera parte es cierto, lo que significa que mi respuesta debe ser regalo. Si voy a chequear. Uno de estos vacíos es 210 mayor que 300 no es a mayor que para nuevo. Esto es falso, Así que básicamente es falso. Tiene que seguir adelante. No es nada. Aquí hay un error. Se está pasando a revisar. El 2do 1 es a 10 mayor que 2 50 No. Entonces esto va a ser el bien de una época, así que India y lo que queda no es nada. Por lo tanto, no olvides usar la fórmula de evaluación. Si no estás seguro de por qué te está dando una respuesta determinada. Realmente ayuda, sobre todo con fórmulas anidadas. Ahora también te voy a mostrar otro uso fuera de la función if. Ayer recibí esta pregunta sobre Cora, y pensé que sería bueno incluirla aquí. Acabo de hacer una versión más simple de la pregunta. ¿ Qué? El individuo buscaba a Waas para sacar la ruta sucia basada en la estructura de carpetas sus archivos, y utilizaron la función de concatenación, que básicamente conecta los valores de una celda a la siguiente. Entonces tenían estos y lo que hicieron es porque querían la estructura de carpetas correcta con e slash está ahí dentro. Agregaron las barras entre los nombres. De acuerdo, entonces, así , ves que Se está reportando Excel, y ellos hicieron lo mismo por el resto, dijo. Eso es lo que querían lograr. Su problema era que no querían el corte delantero si alguno de estos estaba vacío, querían que su nombre comenzara con el nombre fuera de la carpeta y publicaron esta pregunta y ahí había una respuesta que les mostraba cómo hacerlo con VB a Andi, Pensé que tal vez la persona no quiere usar V B A. Y por qué usar VB A. Cuando puedes usar una fórmula simple y la función if es realmente genial para esto, ¿cómo la usaríamos ahí dentro? Pero en lugar de arreglar esto, slash creará un if para ello y dirá, si no hay valor aquí, nos den nada. De lo contrario, danos un corte o puedes hacerlo al revés. Si hay un valor aquí dentro, danos un corte. De lo contrario, no nos des nada. Entonces nos colocaron esto. ¿ Y si esto no es nada entonces y creo que lo contrario, danos una slash hacia adelante y dependiendo de cuántos de estos podría BMT simplemente repite eso para las otras barras. Entonces en este caso también, decimos, decimos, si carpeta a no es nada, entonces nada de otra manera slash hacia adelante Ok, digamos La carpeta tres siempre tiene que llenarse para que podamos dejar eso vacío. Y si copio eso abajo, hago que funcione bien. No hay necesidad de V B A ni nada complejo. Para recapitular. La ventaja de usar una función if anidada es hacer excepciones y cambiar tu resultado. Basado en una situación ahora es tu turno de practicar. 4. Formula 3: IFERROR para informes creativos: la función if error proporciona una forma elegante y sencilla de ocultar los errores en los informes. Si te preguntas, ¿cómo se ve un error mientras es feo, tu jefe será muy probable que te lo señale. Pero aquí está el reporte que no tiene errores. Y aquí hay uno con errores. Si le llevas esto a tu jefe, él te va a decir que hay algo malo en tu reporte. Cometiste un error. ¿ Vas a ser como nuevo? Es tan correcto. Eso es simplemente excel. Es como se comporta Excel cuando no hay datos aquí o se está dividiendo por cero. Ni siquiera lo intentes porque tu credibilidad se ha ido hace tiempo y también sepas que los errores vienen en diferentes formas. Aquí tienes una visión general. A un hombre. Echemos un vistazo a cómo podemos manejarlos en Excel. Si has estado usando Excel desde hace un tiempo, posible que estés familiarizado con la función is error de Excel 2007. Microsoft agregó que si fórmula de error, que es una forma mucho más elegante y más rápida de corregir este tipo de fórmulas, así que decir adiós a es error. Ahora, si lo has estado usando en, déjame presentarte la fórmula if error. En este ejemplo, tenemos a nuestros clientes cantidades vendidas en 2016 y en contrario vendidas en 2015. Nos gustaría calcular el porcentaje del cambio. Ahora puedes ver que tenemos un problema aquí y aquí. Se trata de dos errores diferentes. Éste es porque se ha ido dividiendo por cero MT. Te da un def cero y este. El valor hash es porque tenemos un texto con el que estamos tratando de dividir este número, y obviamente esto no va a parecer creíble. Si imprimimos, decidimos y dáselo a nuestro jefe. Va a pensar que hemos cometido errores en este informe. Entonces en lugar de entrar y borrarlos manualmente, los vamos a mantener dentro. Pero los vamos a incluir dentro. Y si nuestra fórmula y es realmente, realmente simple, todo lo que tienes que hacer es iniciar tu fórmula con la función if error. Entonces básicamente escribes tu fórmula justo después del corchete, y luego solo tienes que especificar qué mientras haces, quieres ver si los resultados de esto son un error y digamos en este caso no queremos ver nada, y eso se ve mucho mejor. Generalmente cuando estés diseñando informes, querrás que la mayoría de tus fórmulas queden dentro. Y si el error funciona porque se asegura de que en caso de que los resultados de tu fórmula sean un error, por la razón que sea, no se va a mostrar en tu reporte y notó que la función if error no diferencia entre el tipo de error, sea lo que sea. Si es un valor o un devora ref, te va a dar esto. En mi caso, nada. También podría poner algo como Chequear esto si quería No, solo anotar esto. Se puede ver que estos se convierten. Comprueba esto. Entonces es lo que quiero ver en caso de que mi fórmula devuelva un error. En este caso, teníamos una fórmula simple. Muchas fórmulas con las que vas a estar trabajando probablemente van a ser más grandes, más complejas. Y mi enfoque es escribir siempre la fórmula Lee primero en cuando termine, luego la envuelvo dentro de la fórmula if error. Para los que tienen curiosidad, cómo la gente usa la función es error y por qué estoy diciendo adiós a ella, solo te voy a mostrar un ejemplo en eso. Entonces si uso es error y escribimos aquí nuestra fórmula, la respuesta es falsa o verdadera. Si es un error, te haces realidad. No es un error, es falso y cómo se usaba antes. Waas con un Si así dices Si esto es error, entonces ¿qué debe hacer? Si es un error, no debería poner nada. Y si no es un error, debería proporcionar el resultado de la fórmula. Obtienes el mismo resultado, pero puedes ver que esta fórmula es simplemente mucho más compleja de escribir que ésta. Esa es la función if error, una forma elegante y sencilla de ocultar errores en tus informes, las notas vuelven a hacer el siguiente ejercicio. 5. Formula 4: VLOOKUP y HLOOKUP búsquedas dinámicas: Tenía que incluir el V, Buscar en la edad, buscar funciones aquí, buscar funciones aquí, pesar de que creo que la mayoría de los usuarios de Excel Básico están familiarizados con ellos. Pero es una función que es una especie de entre básica y avanzada. Entonces pensé en caso de que te lo perdieras, lo voy a incluir aquí. Ahora, el propósito principal de estas dos funciones es buscar un valor en una tabla de big data y darle un valor de Chris ponding que esté sentado en la misma habitación. Ahora, una cosa a tener en cuenta es que el valor que estás mirando hacia arriba tiene que estar a la izquierda. La mayoría de la columna y el valor que se está devolviendo tiene que estar en una columna adyacente. El izquierdo es la palabra clave aquí, por lo que no se puede buscar un valor en el lado derecho y devolver un valor en el lado izquierdo que buscamos un H Buscar trabajo de la misma manera. Excepto que estamos para búsquedas verticales, y H es para búsquedas horizontales. Entonces si tu tabla de datos tiene encabezados de columna, vas a necesitar que busquemos. Y si eres tabla de datos tiene encabezados de rol, vas a necesitar un ahogo. Entonces eso es echar un vistazo a un ejemplo para cada uno para esta demo. Imagina que te han dado un gran conjunto de datos y te han dicho que podría desertar a los agentes de ventas . la cantidad que vendieron el dinero, consiguieron el precio que cobraron a sus clientes. Lo que te gusta hacer es crear un informe general de agente donde seleccionas agente y encuentres el precio y el cliente de esta tabla. Como primer paso, vamos a sumar nuestro desplegable. Es lo que aprendimos en una conferencia anterior sobre validación de datos. Por lo que vamos a ir a la validación de datos en lugar de cualquier lista de selección de valores. Y la fuente es nuestro agente suprimir el cambio de control y la jerarquía descendente. Y aunque llegamos a seleccionar a nuestros agentes a partir de aquí, por lo tanto al agente con selecto le gustaría encontrar el precio. Ahí es donde estamos miramos a Formula entra. El primer argumento en la fórmula re look up es el valor look up. De lo que estamos buscando en este caso estamos buscando al agente, que está en esta celda. A continuación viene el array de la tabla. Hay que recordar que esta es la mesa completa. Entonces el rango completo donde estás mirando hacia arriba, los valores en y tus respuestas se airan, y es un rango. Por lo que se supone que no debes seleccionar diferentes rangos manteniendo el control hacia abajo y yendo así . Pero en cambio seleccionas toda la gama, que en este caso, es de aquí a aquí. De verdad estoy presionando un cambio de control de ganancia hacia abajo. Ese es mi rango y ten en cuenta. ¿ Pero la llave? El principal es que este Agente D es el 1er 1 de este rango. Tiene que ser del lado izquierdo, aunque no necesite cantidad e ingresos. Tengo que destacarlos porque están en mi camino ahí en el camino fuera de mí llegando a precio y cliente. Por lo que siempre tienes que vender como toda la gama, aunque sus cosas ahí dentro que no necesitas a continuación sean el número de índice de la columna. Y eso básicamente significa ¿cuánto debe moverse? Por lo que asume que el 1er 1 es un 12345 Entonces, básicamente, en qué columna es la respuesta que quieres, queremos el precio, y eso lo hace 1234 La cuarta columna. Tenemos que poner al extranjero ahí. Y este último argumento es importante para acertar. Si estás buscando arriba, el valor es texto y no un número. Si lo dejas vacío, es cierto. Lo que significa que no es necesaria una coincidencia exacta. Pero requiere que tu columna look up esté ordenada en orden ascendente. Si no se ordena en orden ascendente y dejas vacío este argumento, entonces probablemente vas a recibir una hora. En nuestro caso, lo tenemos ordenado como ascendente. Pero generalmente no quiero ser frontera para pensar es este texto es Es el número no está ordenado ni nota. Por lo que siempre pongo falso aquí, lo que significa que estos deben ser una coincidencia exacta. Te recomiendo que generalmente pongas esto en falso Un oleaje. De acuerdo, entonces vamos a revisar este Agente D. El precio aquí es 39 redondeado 39. No. ¿ Qué pasa con el cliente? Puedo seguir de nuevo la misma lógica. Mi valor lookup es asiático D la tabla. Yo estoy buscando esto para arriba está aquí. debe incluir a mis clientes. ¿ Qué columna es el cliente? El último, que lo hace 1234 La Quinta columna y falso como último argumento. Cliente D para Agente D. Edad del cliente 37 para Agente H. Tengo un reporte muy sencillo, dinámico usando Be Luca. También puedes usar V Luke arriba dentro de la fórmula para que no tengan que ser utilizados solos solos así. Entonces, por ejemplo, si quería encontrar aquí Price y supongamos que no tenía precios, mis datos de origen realmente necesitan calcularlo. Podría escribir una fórmula sobre esto y podría decir, Bueno, dividir los ingresos por la cantidad y decirle qué ingresos dividir, por qué cantidad usaré. El que buscamos fórmula y decimos, Buscar arriba, Agente edad en esta área, quiero primero los ingresos. Entonces ese es el número tres False como último argumento. Y divida esto por los resultados de la fórmula Blue Cup por cantidad. Cantidades número dos. Y eso me da mi precio. La edad del agente es de 37 años. Como puedes ver, V look up no tiene que ser usado por sí solo pero se puede usar dentro de otros agentes de fórmulas, y es 42 cliente en. Y ahora permítanme mostrarles la versión H buscar arriba de esto Así que en caso de que sus datos originales de origen no se proporcionen de esta manera, sino en filas. Entonces voy a copiar esto. Nos vamos a mudar aquí abajo. Voy a pegar esto como transpuesto. Entonces en caso de que tengas tus datos proporcionados de esta manera, lo cual es menos probable, pero es posible. Y quieres hacer el mismo tipo de búsqueda en el mismo tipo de informe general, tendrás que usar la función de búsqueda H. Entonces, de la misma manera, si tienes a nuestro agente de ventas, déjame simplemente copiar esto. Escribimos nuevas fórmulas, así que de la misma manera, si quieres encontrar los agentes de precio de descuento y desde esta tabla, vamos a escribir h buscar arriba para buscar. El valor es éste. Nuestro array de mesa es este. No olvides que lo que estás buscando aquí necesita ser el primero en tu matriz de tablas. A continuación se presenta el número de índice de rol en lugar del índice de columna. Entonces, ¿dónde está Price? Y aquí 1234 Es la cuarta fila y otra vez hacer falso lo mismo con el cliente. En realidad puedo copiar este pegar esto aquí porque esto sigue siendo el mismo. Eso es lo que estoy mirando hacia arriba. El cuadro es el mismo en lugar de cuatro. ¿ Qué número necesito? Cinco. Cliente en guisantes de paloma aquí pero al final de los cuarenta. Mayo Precio y cliente P está ahí. Obtener si, en lugar de precio. Yo quería encontrar cantidad. El único que necesito cambiar es mi número de índice y la cantidad es el número dos. Entonces consigo ocho. Esa es la edad Función Buscar arriba para recapitular Una limitación importante de la V Look up y las fórmulas HQ Cup es que no pueden mirar hacia la izquierda. Entonces, ¿qué si lo quería al revés en lugar de seleccionar al agente aquí, íbamos a seleccionar al cliente y volver al agente aquí. No podríamos usar la fórmula clásica de corte villa. Vamos a necesitar tener que cambiar la mesa llevando al cliente al lado izquierdo, porque el valor que estamos buscando siempre tiene que estar en la esquina izquierda. Ahora hay otras formas de evitar esto mediante el uso de otras funciones dentro de la fórmula de corte villa . Pero no te molestes en aprender estos porque en la próxima conferencia. Te voy a presentar una de mis fórmulas de búsqueda favoritas, que puede mirar a la izquierda y a la derecha y también hacer búsquedas matriciales. Pero antes de que lleguemos, es tu turno de hacer un ejercicio. 6. Fórmula 5: INDEX y: Si hay una fórmula que te vas a quitar contigo de este entrenamiento, debería ser el índice una función de match Realmente me gustaría verte usar disfunción en tus faltas existentes porque te ahorrará mucho tiempo y frustración. El pequeño fórmula de la Copa Amarilla tiene sus limitaciones, y ahí es donde el índice una fórmula de partido, viene al rescate. ¿ Por qué es tan grande? Bueno, el A mix and match es menos propenso a errores de lo que miramos hacia arriba. Puede mirar a la izquierda o a la derecha en Lake Villa Cup, que sólo puede mirar a la derecha. También puedes hacer una búsqueda de dos vías hacia arriba mirando a lo largo de las filas y a lo largo de las columnas en una matriz para encontrar la intersección. El rendimiento solía ser un factor en las versiones anteriores de Excel, pero ya no es tanto. Recuerda, Index and Match no es una fórmula, sino que son dos fórmulas utilizadas como una. A continuación se presenta una visión general de cómo funciona la función de índice y los argumentos necesarios. Básicamente indexas o resalta el área donde tus respuestas y usas el argumento sobre cómo Maney subió para bajar Y cuántas columnas hay que moverse para encontrar la ubicación de tu respuesta coincide con la función que usas para hacer que la función de índice sea dinámica. Básicamente, mucho devuelve la posición de su respuesta a la función de índice. Esa es una visión general amplia. Entonces ahora pongámonos derecho a ello y empecemos con la función de índice. Te voy a enseñar índice y partido basado en la misma tabla. Ejemplo que teníamos para la V. Mira hacia arriba. Entonces si recuerdas en la última conferencia miramos la Copa Villa y teníamos la mesa en los agentes de ventas cantidad, precio y demás, y quisimos tener un reporte de agente donde realmente seleccionamos al agente. Y con nosotros buscamos arriba, encontramos el precio que cobraron al cliente que tienen. Vamos a hacer lo mismo ahora en este ejemplo, excepto que vamos a usar el índice y el partido. En primer lugar, les voy a mostrar la función de índice. Entonces vayamos aquí y empecemos con índice. No, Lo primero que debes recordar es que justo después del soporte, el rango que pongas tiene que ser el lugar donde está tu respuesta. Por lo que en este caso, queremos encontrar un precio en el rango para que está aquí. Después de eso, necesito especificar cuántas filas quiero bajar En este caso, lo voy a hacer manual en este momento, Así que busco Agente A y esa es una. Empieza desde el índice de uno es 12345 y así quiero uno. ¿ Cuántas columnas quiero mover? Nada. Yo quiero quedarme en esta columna, y ahora tengo la opción de poner nada. También puedo poner cero, o también puedo poner uno. Todos significan lo mismo porque solo tengo una columna seleccionada. Entonces ahora echemos un vistazo al Agente D. Obviamente, esto no se va a actualizar porque aún no es dinámico. Entonces en este caso, yo diría que el Agente Deace cuatro es la cuarta ubicación. Tendré que poner un cuatro aquí. Andi, ya lo tengo. ¿ Qué pasa si pongo en lugar de uno para la columna? Si se lo pongo a ellos reciben un error. El motivo de esto es que no puedo moverme fuera de mi rango de índice. Si sólo he indexado una columna, sólo puedo quedarme aquí. No puedo salir de ella. Si pensé que mi respuesta, digamos también estaba aquí lo puedo poner a Así que aquí tienes índice. Conoce esta zona. Digo Bajen cuatro filas y empieza desde aquí. Por lo que es 1234 y mover dos columnas 12 le devuelve 39. Si cambié la columna ahora dedo del pie uno, me da 3 50 mis ingresos. Entonces se ve cómo funciona esta función de índice por sí sola. Déjame volver a poner estos para que no pueda variar para ser No, sólo el precio. Cuatro y uno está bien. Por lo que obviamente mi objetivo es que este sea dinámico en lugar de escribir en los cuatro. Porque mirando hacia arriba donde está este agente y haciendo esto manualmente, quiero que la fórmula lo resuelva, y ahí es donde entra en juego el partido. Yo lo voy a escribir aquí para que quede claro qué hace partido por sí mismo. Lo único que debes recordar con match es que no es una fórmula normal en el sentido que no te proporciona el valor en la celda. Ya sabes cómo puedes hacer fórmulas y dices, como es igual a esta venta El número que se devuelve es como 39. Y si me referí a esta celda, lo que se devuelve es encontrar precio. El partido no hace eso. Lo que hace es que devuelve la posición de tu valor. Entonces echemos un vistazo a cómo hace eso. Quiero conseguir aquí la dirección de Asiático D. Mi look de valor es Agent D. Mi array look up es como el rango donde puedo encontrar Agent D agencias en algún lugar aquí en el último argumento es si quiero un palo exacto, mucho menos o mayor para la coincidencia exacta. Por lo tanto, no olvides cero como último argumento para la función de coincidencia. Yo obtengo los cuatro y los cuatro significa que es el cuarto lugar aquí en lo que necesitas para cuidar con la función de partido es que el rango que seleccionas tiene que ser una calle de un solo sentido . Tiene que ser una columna o una regla, porque el partido no puede hacer esto. No sabe debe verse así o debe verse, esta manera porque simplemente devuelve el número. Necesitas tener una calle de un solo sentido y puedes hacerlo a través de una regla también. Entonces sólo para demostrar si en lugar de este rango, si lo tenía así, ¿qué número me debe dar la fórmula en este caso? Y uno Porque el Agente D es la primera posición en mi rango aquí. Entonces ahora ves cómo funciona la función de coincidencia. Entonces en lugar de los cuatro voy a decir partido ¿qué gano un partido? ¿ Dónde está este agente? Aquí dentro. Yo quiero una coincidencia exacta. El resto puedo dejar como es justo En lugar de este cuatro, pongo en una función para que sea dinámico. Yo consigo 39 lo cual es bueno. Y ahora, si seleccionara, es un agente Jay. Yo consigo 40. Si selecciono Agente G, debería conseguir 88. Eso funciona bastante bien. Y ahora de la misma manera, me puede gustar la fórmula para encontrar al cliente. ¿ Qué debo indexar al cliente? Esta es la zona en la que se encuentra mi respuesta. Y eso es lo que necesito indexar. Cómo se levantó Maney. ¿ Debería moverme hacia abajo? Bueno, depende de dónde esté sentado los Agentes G. ¿ Y cómo hago esto dinámico? Utilizo la función de coincidencia. Estoy buscando Agente G. Lo estoy buscando aquí arriba. Yo quiero una coincidencia perfecta, el último argumento porque es sólo una columna. Cuando mi respuesta está dentro, puedo dejarla fuera. Cliente G. Cambiemos eso funciona bien. Y ahora mismo podrías estar pensando, ¿Por qué usaría alguna vez la función de índice de partido cuando podría usar una función más simple Villa Cup ? En este ejemplo exacto, probablemente también usaría la función Villa Cup. Pero si mi mesa era mucho más grande que ésta, así que como un gran extracto de datos del sistema y necesitaba buscar aquí un valor y recuperar una respuesta, estaba sentado lejos del lado derecho. Voy a usar la función de índice porque Villa Cup es más propensa a errores. Tendría que contar el número de columnas que necesito para llegar a la mía. Y si por casualidad elimino una columna en el medio, voy a tener que revisar, tal vez mirar hacia arriba y podría olvidar, pero no tendría que hacerlo si utilizo index y match. Entonces esa es una ventaja, ¿verdad? Podría no estar plenamente convencido. Entonces pasemos al siguiente ejemplo aquí. En este caso, queremos encontrar a nuestros clientes, seleccionar a nuestro cliente aquí y recuperar a nuestro agente. Recuerda, no pudimos hacer esto con la V. Mira hacia arriba porque miramos hacia arriba sólo podemos mirar en una dirección. Entonces tendríamos que cortar esta columna de clientes, ponerla aquí y luego. Correcto. Harvey, mira hacia arriba. En este caso, no tenemos que tocar nuestra tabla de datos, así que solo usemos su índice. ¿ Qué respuesta quiero aquí? Quiero un nombre de agente. ¿ Dónde pueden encontrar eso en esta columna? Siguiente. Mi número de habitación. ¿ Cuánto debo bajar? Bueno, eso depende de qué cliente quiera. Entonces por eso usé la gran función que podría mirar. Valor es el cliente. ¿ Dónde está este cliente? Este es el rango donde puedo encontrarlo, y quiero una coincidencia exacta. ¿ Necesito mover alguna columna? No, sólo tengo una columna. Así que pon 01 o déjalo vacío. Cierra el soporte. Se comprobó al agente E que es totalmente dinámico. Eso funciona bastante bien. Entonces ya ves, en este caso, no tengo que tocar mi tabla de datos. Sólo tengo que escribir mi índice esa fórmula. Ahora, pasemos a otro caso. Deseo seleccionar mi agente, pero también quiero seleccionar mi variable en este caso por variable. Es decir, quiero definir si quiero cantidad, ingresos, precio o cliente. Vamos a hacer un desplegable rápido porque está Ok, Así que básicamente quiero seleccionar a mi agente, y luego quiero seleccionar. Sí. Quiero ver los ingresos de este agente o la cantidad que vendió este asiático y tener aquí mi respuesta. Esto significa que necesito un dos vías Buscar hacia arriba. Necesito mirar el agente aparece, y luego necesito mirar la variable aquí un oleaje y obtener el índice de coincidencia. Ahora, ¿qué índice en este caso? Trabaja en mi respuesta. Estar Bueno , no puede estar aquí. No puede ser agente. No pueden ser una respuesta. Pero la respuesta podría ser cualquiera de estos porque dependiendo de qué variable, selecciono cantidad, ingresos, precio o cliente. Mi respuesta sería uno de estos decir podría ser cualquiera de esto. Siguiente es el número de fila. ¿ Cuántos caminos quiero bajar? Depende de dónde esté sentado mi agente, y ésa es la que necesito para mirar hacia arriba. Yo los busqué aquí. Quiero una coincidencia exacta y siempre tenga cuidado de que los rangos que seleccione para que su rango de índice y sus rangos de coincidencia coincidan en términos de la longitud del rango. Porque recuerda, la función de partido empieza a contar. 123 aquí dijeron que tenían. Tiene que estar en línea con tu índice. Extraño ahora para las columnas. ¿ Qué debo hacer? Necesito hacer otro partido y necesito hacer coincidir esta variable en. Ahora voy a seleccionar el rol aquí. Cero para el partido exacto. Cierre corchete. Vuelve a cerrar corchetes. 3 50 Agente D Ingresos 3 50 Agente E. Cliente. Cliente. E Ya ves Es totalmente dinámico y funciona en ambos sentidos. Ahora solo echemos un vistazo a la fórmula de evaluación solo para usar algunos de los consejos que aprendimos aquí. Tratemos de analizar esto con más detalle. Entonces esto evalúa este que tenemos índice está tratando de igualar Agente E. La respuesta para la Rosa es bajar cinco notas tratando de descifrar al cliente del segundo partido . La respuesta es cuatro. Entonces básicamente lo que está haciendo es que tienes este rango. Te estás moviendo hacia abajo. 512345 y cuatro a través 1234 y obtienes cliente e. Así que no olvides usar fórmula de evaluación si te quedas atascado con tus fórmulas. 7. Fórmula 5: INDEX y combinación de fundos reales compleja: ahora pasando a ejemplo más complejo. Bueno, déjame no. Carly Complex es en realidad un ejemplo de la vida más real. Casos más prácticos que vas a tener porque las faltas que vas a tener, admitamos que no van a ser todo perfectos. Con solo un encabezado de columna, un encabezado de rollo, podría tener casos en los que vas a tener múltiples encabezados de columna y una combinación de thes. Es lo que los va a hacer únicos en este ejemplo. Tengo cantidad real, ingresos reales y luego cantidad presupuestal. Fue ese ingreso y así sucesivamente y hasta podrías tener tres. Por lo que podrías tener cantidad real de enero, cantidad real de febrero y luego así sucesivamente para cada mes. Y también podrías tener muchos encabezados de rol. En este caso, no lo hago. Pero si lo haces, tienes que usar la misma técnica. Te voy a mostrar aquí, y no te voy a mostrar una técnica, sino tres técnicas diferentes sobre cómo puedes hacer esto y lo que en realidad pretendía hacer es que esta es una tabla de datos realmente y queremos conseguir un reporte. Entonces imagina que este es un bonito reporte de fantasía en otra página de archivo de Excel que queremos imprimir . Lo que queremos que haga el usuario es seleccionar agente de su lista en el informe proporciona la cantidad real, la cantidad presupuestaria y así sucesivamente de esta tabla de datos. Ahora, como pueden ver, yo pie método 12 y tres aquí abajo. El orden sí importa, porque empiezo con el más simple Primero. Si no estás tan familiarizado con la fórmula índice y match, apégate al Método uno del principio. Quiero empezar a usarlo y realmente, realmente entenderlo. Entonces puedes continuar en método a y método tres. Si quieres. Empecemos con el primer método que he llamado su simple índice de coincidencia. En realidad es como una coincidencia de índice, igual que lo que hicimos en el ejemplo anterior, porque queremos buscar a este agente en el Rose y luego queremos buscar esta cantidad real en las columnas aquí, así que necesitamos para bajar y cruzar. Pero la diferencia entre este y el ejemplo anterior es que necesitamos tener este cantor único, que no tenemos en este caso de una manera sencilla Esto es crear ese encabezado único simplemente diciendo que esta celda en esta celda es combinándolas. Porque, recuerden, partido era una calle de un solo sentido. No puede buscar algo así, pero tiene que buscar algo. Eso es sólo una cosa. Y podemos crear eso usando esta fórmula. Ahora podemos escribir nuestro índice match. ¿ Qué necesitamos indexar? ¿ Cuál es nuestra respuesta? Podría ser cualquiera de estos. Podría ser una cantidad, un ingreso, un cliente, cualquiera de estos. Porque, recuerda, quiero tirar de esta fórmula hacia abajo y a través. Debería aplicarse a todos estos en la respuesta podría estar en cualquier lugar aquí. Entonces pensemos en la fijación. Yo no arreglaría eso. ¿ Cuántas filas necesito para bajar? Eso depende de dónde esté sentado mi agente. Es que necesito un partido aquí. Encuentra a este agente. Vamos a arreglar eso aquí. Arregla eso, y quiero un partido perfecto, no una columna. Bueno, sí tengo un encabezado único. Ahora. Solo necesito que mi look up value también sea único, y eso básicamente me deja empezar con un partido. Esa es la combinación de esto y esto. Entonces pensemos en la fijación. Estoy planeando tirar mi fórmula hacia abajo por lo que necesitan arreglar la cantidad 46 y este caso en planear moverla a través. Necesito arreglar el M. Eso es lo que estoy emparejando. Donde eso sea fácil. Ahora mismo, es aquí cuando arreglas eso y un partido perfecto. Seis Agente ser cantidad real a las seis. Entonces vamos a ver si hicimos nuestra fijación correctamente. Eso es 300 50 cliente Ser 73 30. Eso se ve bien. Porque si esa es la forma sencilla de escribir tu índice y fórmula de coincidencia en caso de que no tengas encabezados únicos, solo tienes que crear esta clave única lo que puedas, entonces hazlo. Si no quieres molestar a nadie, no puedes agruparlo y ocultarlo. De acuerdo, vamos a comprobar eso con otros agentes. Vamos a comprobar la edad del agente. Eso son nueve 333. Eso está bien. Entonces ahora vamos a pasar al Método número dos, y con este método, vamos a saltar este paso aquí, y la forma en que vamos a saltarlo es Regan, añadir una función de índice adicional dentro de este último partido función. Te voy a mostrar cómo va a funcionar eso. Permítanme copiar esta fórmula aquí abajo y bajar nuestras referencias celulares. El resto está bien. Está bien, Esta parte está bien, ¿verdad? Estamos de acuerdo. No necesitamos tocar eso. Es la última parte. Eso es un problema para nosotros porque no es único. Entonces sabemos que estamos buscando esta combinación. Ahora, en lugar de tener este adicional, necesitas una llave. Vamos a usar una función de índice para darnos cantidad real, ingresos reales, precio real en celdas separadas que se va a escribir dentro de esta matriz. No van a ser células visibles. Cómo va a funcionar eso es que vamos a indexar esto y luego vamos a agregar un Y así mira lo que no estoy haciendo no es que no estoy indexando esto, pero eso no es lo que tengo que hacer, porque eso no va a funcionar para el partido. Recuerda, Calles de un solo sentido estoy indexando esto. Y esto y lo que hace esta fórmula es que va cantidad real como posición uno. Son recuerdos en su posición de ingresos extra a precio real posición tres. Por lo que tipo de hace crea una calle de un solo sentido de esta combinación en la memoria aquí y luego necesito especificar el número de fila. Pero quería hacerlo con el dedo del pie todo Rose. O sea, es que no es aplicable aquí. Entonces o puedo poner un cero. Yo sólo puedo poner la coma y dejarla fuera. Y para la columna también. Puedo poner un cero significado solo hazlo por todas las columnas y cerrar ese índice. Ahora, esta parte pertenece a alguien función de partido de antes. Entonces todo lo que hice fue reemplazar ese rango aquí, esto ayuda su rango que agrupamos antes con esta fórmula de índice, la prensa entrar y ellos obtienen el mismo resultado. Entonces estoy planeando copiar esto. Tengo que arreglar que estás arreglando es una de mis debilidades. Siempre. Me di cuenta demasiado tarde. Está bien. Y eso funciona bien, así que ahora vamos a pasar al método número tres, y eso es usar una matriz C S E. Si nunca has usado esto, C S. E. Este cambio de control entrar, va a ser difícil para ti digerir esta fórmula. Básicamente, en excel, eres capaz de convertir cualquier fórmula en una fórmula array. Si presiona Mayús de control, ingrese al final en lugar de simplemente presionar Enter. Lo que eso significa es que hace exactamente lo que nuestra función de índice hizo aquí, pero sin tener que escribir índice para que pueda convertir cualquier fórmula en una matriz una vez que haga control, cambio, entrada. Y en realidad hace esta cosa que Index hizo con decir esto en esto como posición uno en memoria, esto en este tipo de postre looping en estas células. Entonces te voy a mostrar esto, pero te voy a advertir. Entonces no te desanimes cuando veas esta fórmula. Si no lo sabes, solo continúa con las próximas conferencias. Se utiliza el método simple Siempre que te encuentres con el's encendido una vez que estás listo para digerir que la comodidad es otra vez y referirse a este material. Permítanme mostrarles esto en acción. Voy a copiar esto aquí ganancia Vamos a actualizar nuestras referencias celulares. Y ahora, en lugar de escribir el índice aquí para que puedas quitarte todo esto. Tampoco necesito el resto del índice. Sólo necesito decir de un partido, esto es puede Look up, Harry. Es esto en esto pero no veo, si presiono enter, obtengo valor. No entiende lo que debe hacer para que esto haga lo que sólo nuestra fórmula de índice. ¿ Presioné turno de control? Sujétalo. Entra eso Funciona. Haciendo copia esto hacia abajo y a través. ¿ Quién cambió esto? Agente K 10 3 20 Todo se ve bien. Como pueden ver, esta es la forma más corta de escribir esto Porque convertí esto en convento. Ahí se puede ver. Es decir, una carrera tiene ventajas, pero también tienen sus desventajas. El principal inconveniente que veo con un aumento es que hacen que tus hojas de cálculo sean más difíciles entender para otros. Entonces si te gustaría renunciar a la hoja de cálculo a alguien a tus compañeros o a alguien que no esté familiarizado con esto y estás tratando de mirar oh, ¿cómo se calcula esta fórmula? Hola, está tomando esta celda. Y eso sonó la prensa entrar. Se ha ido. Ya no funciona porque tienen que presionar turno de control enter. El otro es que te pueden hacer especialmente lento. Si tienes muchos datos, es algo que tienes que tener en cuenta también. Pero por lo demás son método muy limpio y magra de escribir esta fórmula. No personalmente cuando diseñé hojas de cálculo para otras empresas y tengo que hacer algo como esto. Iré con el Método uno porque mi público final puede ser diferente. Algunos pueden ser muy avanzados, pueden ser avanzados en algunos no tan avanzados y método. Uno sería el más fácil de seguir para cualquiera de estos. Entonces diría su método a y por último, es Method Tree, que es más propenso a errores. Entonces, como dije antes un palo al Método uno, si no estás acostumbrado a los días y vuelve y revisa estos métodos una vez que estés listo para ellos. Entonces ahora pasemos a nuestro último ejemplo de la función de índice y coincidencia. Y esta es una prueba que estaba haciendo para una empresa cuando estaba diseñando una plantilla para ellos y tenían una mesa como esta. Querían que el usuario seleccionara un producto que seleccionar un tipo, y el tipo es cualquiera de estos dentro de la tabla, y querían que la plantilla proporcionara el encabezado. Voy a quitar eso para que podamos hacerlo juntos. Por lo que ahora tenemos una búsqueda inversa en lugar de buscar de esta manera y esta forma de conseguir esto. Vamos a estar buscando esto y esto para conseguir esto para que puedas ver D puede estar en la segunda categoría. Y también está en el tercer Guerry Cáncer. Y también está en la cuarta categoría. Pero dependiendo del producto que estés usando, si alguna vez tienes un caso como este, también podemos usar para indexar y emparejar función. Pensemos cómo podemos hacer esto. ¿ Qué necesitamos indexar? Por lo que siempre Mira tu pregunta. Es solo encontrar categoría. Nuestra respuesta es una categoría fue todo lo que necesitamos para indexar. Ignora el resto. ¿ Cuántas filas queremos movernos aquí dentro? Nada. Nosotros sólo queremos tomar en cuenta este papel y ya está. Para que pueda poner otra coma. Paso al siguiente número de columna de argumento. Entonces empecemos de manera simple. En este caso, quiero la segunda columna porque mi producto a y D están en la segunda columna. Es solo estudiantes, Ver, obtenemos Categoría dos. Sí, entonces ahora nuestro objetivo es hacer que el a sea dinámico. Entonces, ¿cómo podemos hacer eso? ¿ Qué función se puede utilizar aquí? El dos es una posición derecha por lo que realmente necesitamos la función de partido porque match nos da una posición en lugar de los dos. Voy a mecanografiar match. ¿ Qué necesitamos para igualar? Bueno, sí necesito igualar el d aquí. Voy a empezar con eso. ¿ Por qué necesitamos igualarlo donde pueda estar en cualquier parte de aquí? Correcto. Pero recuerda, partido estaba fuera calle de un sentido, ¿verdad? Entonces voy a empezar de nuevo no hacer esto dinámico o semi dinámico, y luego haremos una dinámica. Entonces necesito en este caso, ¿Mi d está aquí porque mi producto a está aquí. Entonces voy a igualar este. Andi, necesito un partido perfecto para seguir consiguiendo categoría a, lo cual es bueno. Entonces ahora soy semi dinámico, no totalmente dinámico porque arreglo esto. He engañado y he dicho que sé que está aquí porque esa es la combinación. Entonces ahora en lugar de esto, voy a usar una fórmula dinámica. ¿ Cuál es la fórmula que puedo usar para rangos o un raise? Es la función de índice. Lo que puedo indexar es todo el asunto. Porque al final quiero ya sea éste o éste o el último, ¿no? Entonces voy a empezar con indexar todo el asunto. Después viene mi número de habitación. Eso depende de mis productos para que pueda igualar mi producto de este listado. Después viene mi columna, y en este caso quería incluir todas las columnas. O puedo dejarlo fuera o poner cero ahí dentro. Cierre el corchete categoría dos. Porque lo que pasa con esta fórmula es que indizo este derecho. Pero debido a que me mudé a, en realidad estoy terminando solo para indexar esto, y eso funciona perfectamente bien para alimentar mi función de coincidencia con solo necesita una fórmula de un solo sentido. Entonces, solo echemos un vistazo rápido a la fórmula de valor en este caso. ¿ Eso podría ayudar aquí? Entonces estamos emparejando D. Estamos indexando toda esta región B 77 a G 79 y ahora estamos emparejando producto con, Entonces eso debería darnos demasiado derecho. Es la segunda posición que nos da dos, y nuestro índice sigue aquí, y veo que dice Partido D en B 78 B 78 a G 78. Por lo que cambió eso a una calle de un solo sentido, que al partido le gusta y puede trabajar con. Esa es otra forma de utilizar la función índice que coincide. Esta es una de mis fórmulas favoritas. ¿ En serio? De verdad. Entender la disfunción es lo que separa a dos gurús de las normas. Y no vas a ser gurú si no practicas. Por lo que ahora, señor Regreso a la práctica. Pero aparte de este ejercicio, me gustaría ver que llevas esto un paso más allá y realmente tratas de ver prácticamente dónde puedes usar el índice de mucha función en tus caídas existentes y luego realmente lo usas. 8. Fórmula 6: las funciones de texto más útiles: fórmulas de texto pueden ahorrar mucho tiempo limpiando tus datos y consiguiéndolos en el formato que necesites. Pero a veces necesitas menuzar la policía o limpiar las descripciones de tus productos, tus clientes o de tus cuentas. O necesitas cortar sustitutos o reemplazar partes de tus cotizaciones de producto por otra capa. En esta conferencia, les mostraré las fórmulas de texto más útiles para confiar en mí. Son muy fáciles de usar. El punto clave es que sabes que existen y sabes cómo conseguir que sean dinámicos. Entonces cada vez que te encuentres con una tarea donde necesitas manipular partes de tu texto, vas a decir , Oh, sé que hay una fórmula que puedo usar para esto y no tienes que hacerlo manualmente. Esta es la lista de las que creo que son las funciones de texto más útiles. Te llevaré a través de cada uno en la demo. Vamos a empezar con la fórmula superior. Entonces digamos que aquí tenemos nuestra lista de nombres de clientes que extraemos espacio de nuestro sistema y están algo mezclados en minúsculas de verano. Algunas se devuelven adecuadamente y mezcla veraniega de mayúsculas y minúsculas. Lo que hace la fórmula superior es que solo escribes su corchete abierto y texto significa resaltar la celda que tiene tu texto o en realidad puedes ingresar texto como este ahí dentro. Pero queremos ser Dana hace, Así que vamos a destacar ese corchete de cierre celular. Obtienes todo en mayúsculas Lou. ¿ Dónde hace lo contrario? Pone todo en minúsculas y la apuntala pone las cosas en un formato adecuado. ¿ Cuál es el formato adecuado? Es así. Entonces la primera letra es mayúscula. El resto es minúscula. Esa es la fórmula adecuada. Entonces para texto como este, eso está todo mezclado. Se ve mucho mejor en esta forma. No, olvídate de la longitud. Es la función Len. Lo que hace la función de préstamo es que te proporciona el número de caracteres en tu texto. Entonces solo escribe Len Reference, el presentador celular encendido. En este caso, tengo 13 caracteres en esta celda. La función de préstamo que podrías ahora mismo pensar bien, ¿qué pueden usarla para todos. El uso es si tienes una plantilla específica que quieres que la gente sienta y no deben repasar un cierto número de personajes. Puedes usar esta función para rastrear cuántos personajes puso Aaron en la celda. Hay otros usos útiles a esto que ayudan a que tus otras fórmulas que vamos a aprender aquí sean más dinámicas. Entonces vamos a volver la longitud del dedo del pie unas cuantas veces en este ejercicio. Ahora, en este ejemplo, tenemos un montón de nombres de clientes que se han extraído, digamos, de un sistema, y vienen con cualquiera de los espacios finales. Mira este. O los espacios antes o con demasiados espacios entre los personajes. Voy a conseguir longitud antes de esto porque no lo olvides, un espacio es considerado un personaje. Ahora bien, si recorto lo que es trim do trim quita estos adicionales, este trailing espacios. Así que acabo de recortar estos. Ahora voy a conseguir la longitud después de recortar solo para mostrarte que sí le quitó personajes adicionales. Entonces ya ves, aquí tenía 17 porque tenía estos espacios trailing, y aquí tengo 13 porque lo recorté. Se los sacó, y en este caso le quitó este espacio extra que tenía entre Toby y Carter. Recuerdo cuando di un entrenamiento hace algún tiempo. Algunas personas en este entrenamiento se reían de esta función de recorte cuando se reían porque suena algo gracioso, pero también se ríen de que pensaban que era completamente inútil, supongo. ¿ Qué? Terminaron usando esto en casi todos sus archivos. ¿ Por qué? Porque me dijeron que han estado intentando usar la función re look up. ¿ Recuerdas la que aprendimos como nuestra cuarta función? Estaban tratando de usar esto, pero sí funcionó en sus datos y estaban extrayendo los datos de una SAPI. Y dijo: Simplemente no funciona en ese datum. No sabemos por qué. Bueno, adivina lo que necesitaban recortar. Están descargando porque cuando extraían en su caso, sus códigos de producto, venía con espacios trailing. Y cuando estaban creando su reporte, éste no tenía los mismos espacios finales, por lo que no podía igualarlo. Por lo que tuvieron que recortar estos primero y luego hacer su mirada hacia arriba así que recortar en realidad puede ser función muy útil. Ahora pasando a las funciones izquierda, derecha y media, pueden ser bastante útiles cuando estás manipulando tus datos, tus descripciones y solo quieres una pieza de tu texto a partir de un texto más grande. Entonces empecemos con la función izquierda que grabo a la izquierda, luego detecta a Texas básicamente, la celda en la que tienes tu texto. Y el último argumento es cómo personajes de Maney quieren quitarse de esto? Entonces digamos en este caso quiero los nombres que quiero. Toby, Cómo Maney personajes es ese cuatro para que pueda escribir en cuatro. Eso es lo que hace la izquierda. Entonces si escribiera en Lake ocho ver, es Toby Jen. lo que se hace do mid hace algo similar, excepto que tenemos un número de inicio y el número de caracteres que queremos. Por lo que seleccionaría este texto. Entonces puedo decir, ¿ desde dónde quieres empezar a leer? ¿ Es la primera posición? No es éste de aquí donde así en este caso, quiero empezar a leer. No, del 45 está vacío al sexto. Quiero empezar a leer el sexto, ¿ Y para cuántos personajes quiero leer? Entonces básicamente empieza a leer aquí y es 1234 Vamos a sacudir ese último tema Jane. Puedo volver a usar la función correcta en su texto de finales de mayo. ¿ Y cuántos personajes quiero leer de este sitio? ¿ Si sólo quisiera el caso Carter seis? Si amplío eso y hago ocho Ya ves, meto la e ahí y luego Carter. Entonces quiero seis que? No. Obviamente estás pensando, Bueno, donde me voy a molestar en mirar a través de cada nombre y poner un cuatro y luego el siguiente nombre es quizás más largo y necesito poner como, un seis o cinco. No voy a hacer eso manualmente. No. Y ahí es donde entran las funciones de hallazgo y búsqueda, así como nuestra función de longitud, que puede hacer que todo esto sea dinámico. ¿ Cuál es la función find? ¿ Hacer? Vamos a probarlo. Voy a encontrar ¿Qué quiero encontrar? Entonces el texto que quiero encontrar en este caso busco té, así que voy a poner t entre comillas. Si tomara té en una celda separada, puedo hacer referencia a eso también dentro del texto. ¿ Qué texto es este y número de inicio de nuevo? Se puede decir, ¿Dónde debe comenzar a leer desde el primero? O bien puedes empezar a leer desde la quinta posición, seis posición y así sucesivamente. Si solo querías leer desde el principio, puedes dejar ese argumento vacío. Yo consigo 14. Por lo que dice que t es la posición 14. Entonces esa mirada, tengo una t pequeña ahí. 123456789 10 11 12 13 14. Y note que no encontró la T mayúscula porque buscaba el té pequeño. Entonces ahora solo veamos lo que nos buscó. Antes de explicar la diferencia entre los dos, vamos a hacer lo mismo. Ya ve, los argumentos son los mismos que hallazgo. Entonces voy a buscar té. Voy a buscar dentro de este texto en mi posición inicial. En realidad, voy a dejar ese vacío porque quería empezar desde el principio. Da de vuelta una, aunque ves que aquí y aquí es la misma fórmula. Solo estoy usando una función diferente. Pero aquí consigo uno, y aquí consigo un 14. Esto resalta una de las diferencias entre búsqueda y búsqueda no es sensible a mayúsculas , por lo que a la búsqueda no le importa si buscas un té pequeño o una T mayúscula o este es un té pequeño o T. mayúscula Se encuentra la posición donde ocurre primero, mientras que find es sensible a mayúsculas y minúsculas. Entonces si buscaba T mayúscula, lo encuentro como la primera posición. Y aquí, si buscaba una T mayúscula, no le importa. No importa. El T Capital o un té pequeño la primera vez que ocurre es al principio. Esa es una de las diferencias entre hallazgo y búsqueda, y la otra diferencia entre ellos es que la búsqueda permite comodines, mientras que el hallazgo no lo hace en este caso, digamos que buscaba T E. R. De acuerdo, así que digamos buscar TR. Es la posición 14 aquí, y yo estaba encontrando también la posición 14 del TR. Pero ahora imaginemos que quiero usar un comodín en lugar de la E. Así que voy a usar el signo de interrogación en el trabajo de preguntas. Es como un marcador de posición para cualquier texto que pueda ocurrir entre T y R. Eso me devuelve todavía 14. Si uso el comodín aquí, no funciona para recapitular. El diferencia es que a la búsqueda no le importa si es mayúscula o minúscula. No es sensible a mayúsculas y minúsculas, y la búsqueda permite comodines. Por lo que la función find es en realidad más restringida que la función de búsqueda. Y debido a eso, generalmente usé la función de búsqueda en esto. Por supuesto, realmente necesito prestar atención al Capitolio y a las mayúsculas. Por lo que ahora pasando, cómo se puede utilizar esta función junto con esta función para conseguir que nuestros cálculos dinámicos de apellido de nombre se hagan por nosotros. 9. Fórmula 6: funciones avanzadas de texto: para obtener el nombre de pila de manera dinámica, vamos a usar la función izquierda. Ese es nuestro texto. Y ahora vamos a utilizar la función de búsqueda para obtener cuántos actores de atención queremos que nos despojen del lado izquierdo en adelante. Lo que estamos buscando es el espacio dentro de este texto. Debe empezar a leer desde los inicios. Puedo dejar fuera esa discusión y ya tengo a John. El resultado de mi búsqueda aquí es de cinco porque el espacio es el quinto conector en este nombre. Por lo que debió haber despojado el espacio también con esto. Por lo que peleas testes en un conseguir la longitud de esto. Ya ves , son cinco y no. Cuatro. Entonces lo que tendría que hacer es o hacer corrección a esto y hacer uno menos, o la forma en que prefiero hacer esto en este caso es recortar esto y deshacerme de este espacio extra . Entonces básicamente estoy recortando los resultados de esta fórmula, y puedes ver que es por ahora. También, para conseguir la primera letra que nos muestre mayúscula, puedo agregar la función adecuada a esto. Comprobemos que esto se ve bien. Ahora la forma en que trabajo con funciones de texto que se hacen grandes o en realidad con cualquier fórmula de excel que acabe haciéndose cada vez más grande, siempre empiezo por el interior primero. Entonces con lo más básico primero y luego empiezo a hacerlo más dinámico, y agrego otras funciones a esto. Entonces para el apellido, necesitamos la función correcta. Ese es nuestro texto. Sé de nuevo, cuántos coleccionistas deben ser despojados del lado derecho. En este caso Anderson, necesitamos ocho. Pero tenemos que conseguir la dinámica del sida. Y para conseguir eso, sólo tenemos que pensar un poco. ¿ Cómo llegamos a la edad en este caso y luego cuatro? En este caso, la forma en que podemos hacer eso es obtener la longitud de todo el asunto y luego deducir la longitud del nombre y el espacio. Entonces, básicamente, si encontramos la ubicación de ese espacio como lo hicimos aquí, podemos deducir esa espuma la longitud de todo el nombre. Voy a hacer esto por separado para que puedan ver los resultados de esta fórmula antes de una copia en así que si consiguen su longitud de todo el asunto, 13. Ahora bien, si deduzco esa posición de ese espacio, que es cinco, debería conseguir ocho. Entonces voy a buscar ese espacio dentro de este número de inicio de texto. Puedes dejar eso vacío. Se puede poner uno que sea ocho. Y ahora voy a copiar esto y pegar esto en lugar de mis ocho. Observe que no necesito hacer una corrección menos uno o agregar una función de recorte aquí porque empiezo a contar después del espacio. Entonces ahora agreguemos la función adecuada a esto un oleaje que funciona bastante bien. ¿ Qué pasa si en lugar de John Anderson, tengo a John Andrew Anderson. Por lo que tienen un segundo nombre. Ahora todo eso se va a tomar como apellido porque, recuerden, buscamos el primer espacio, y el resto es el apellido. Entonces eso nos lleva a nuestro siguiente ejemplo. El nombre es fácil. Es básicamente fórmula idéntica que tenemos aquí, así que solo voy a copiar y pegar esto aquí ahora para el segundo nombre, sus diferentes formas de escribir esto voy a usar la función media en este caso. Entonces llegamos a practicar con eso un poco. El texto que necesitamos está aquí. El número de inicio. De acuerdo, te voy a advertir, esto sólo se va a poner un poco complejo. Si eres muy nuevo en estas funciones de texto, mejor que no estés viendo esto por la noche o si estás cansado. Entonces tal vez pausas esto, mira esto por la mañana o toma un café ahora mismo solo para advertirte. Por lo que número de inicio en este caso, número de inicio estaría aquí donde está la J. Entonces esa sería nuestra sexta posición. Es eso lo que queremos en última instancia son seis. Y en este caso para la edición de Jane, pase más de cuatro. Vamos a probar eso antes, a ver si funciona. Está bien. Vamos a conseguir la longitud para asegurarnos de que no estamos agregando ningún espacio. En este caso, se ve bien. Por lo que ahora nuestros Amy para reemplazar el seis y un cuatro con una fórmula dinámica. Entonces empecemos con el número de inicio. El puesto para empezar a leer el segundo nombre es después de que termine el nombre, y ya tengo el nombre aquí. Entonces la longitud de mi nombre es lo que necesito en este caso. ¿ Eso es suficiente? Si sigo así, sólo intentémoslo. No, porque la longitud es de cuatro y la función media empieza a contar con la cuarta incluida. Entonces es el espacio más amplio para J y la A. Así que no quiero eso. Yo quiero dos conteos el nombre de pila. Pero entonces debería pasar a después porque tengo un espacio y tampoco debería contar la última letra, así que puedo agregar un más dos. Eso se ve bien. Ahora tenemos que sustituir a los cuatro. Entonces pensemos en cómo podemos meter a los cuatro ahí de manera dinámica. Voy a hacer esto en una celda separada, y luego lo voy a copiar aquí. Entonces, ¿cómo conseguimos cuatro si consigo la posición del segundo espacio aquí? Entonces eso sería 10. Y luego deduzco el nombre la longitud del nombre. Entonces eso son cuatro. Y luego los dos espacios que deberían darme cuatro. Entonces, solo comprobemos que lo voy a hacer por separado. Entonces busquemos el segundo espacio. ¿ Cómo hice eso? El modo en que puedo hacer eso es decir, empieza a leer después de que termines de leer esto en el espacio. Porque recuerda, tengo el número de inicio que he estado dejando fuera hasta ahora porque siempre quise que mi búsqueda empezara desde el principio. Ahora no lo hago. Yo quiero que la búsqueda empiece desde aquí desde el J. Y puedo hacerlo diciendo empezar a leer. Después de llegar a la longitud de esto, más dos a ellos, encuentra el siguiente espacio 10. Bien. Esa es la posición de este espacio. Ahora queremos decir, deducir el monto de los nombres para deducir los cuatro. Ese seis. Y ahora tengo dos espacios aquí. Uno aquí, uno aquí. lo hicieron los actores, eso es para. Y esto es lo que puedo copiar en lugar de estos cuatro. Aquí consigo a Jane. Entonces ahora ver lo que sucede cuando una copia esta abajo Errores. ¿ Por qué? Porque aquí no tengo un segundo espacio. Entonces, ¿qué puedo hacer para manejar este error? Ojalá dijera, si error en caso de que esto resulte en un error, lo que significa que no tengo un segundo espacio. No quiero conseguir nada porque esta persona no tiene un segundo nombre. Estamos sentados bien aquí. Vamos a revisar teniendo a John Andrew Anderson, eso también se ve bien. Entonces ahora para el apellido, voy a usar el texto de visualización de la función correcta. Ahora bien, no puedo buscar el primer espacio. Necesito buscar el segundo espacio y luego despojar todo lo que viene después de eso. Entonces una forma en que puedo hacer esto es simplemente usar la información que ya obtuve del primer tema y segundo nombre para obtener mi último cosa. Básicamente, si digo dame toda la longitud de esto, deducir la longitud del nombre. Me deduzco la longitud del segundo nombre. Todavía tengo dos espacios aquí, podría decir menos dos. Pero entonces si miras aquí abajo, no tengo que espacios. Dejémoslo con menos dos. Te voy a mostrar lo que pasa esto cuando trabajes. Bien, es Carter. Pero cuando copio esto abajo, ves que se despojaron de la A y de la M aquí. Y la razón es que estos tipos no tienen que espacios solo tienen uno. Entonces si miramos estos, siempre tienen al menos un espacio. Entonces lo que puedo hacer es hacer una corrección menos uno a esto para que estos funcionen bien. Y para que éste funcione correctamente es agregar la función de recorte a toda la fórmula, porque si tiene un espacio, lo recorta. Si no lo tiene, lo deja solo. Es así como puedes manejar casos en los que tienes segundos nombres para algunas personas y no para otras. Pero como dije antes, hay diferentes formas de llegar a la disolución, y va a estar realmente contento si compartes tu camino también con los demás. Entonces pasando a nuestro último bit, eso es lo que les presentaría al sustituto y a la función de reemplazo. Podrían ser útiles dependiendo de tu tarea. En ocasiones no quieres usar el Excel en una búsqueda y reemplazar si me gusta el control h, obtengo el hallazgo y reemplazo. En realidad no lo quieres duro. Cita eso en tu en tu falta, pero quizá lo desees de una manera dinámica para rastrearlo por separado en fórmulas. Ahí es cuando se puede usar la función sustituta. En primer lugar, hay que seleccionar cuál es la celda en la que se busca sustituir. Entonces, ¿cuál es el texto antiguo que tienes en este caso? Quiero un sustituto 80 con D para estos códigos, mi viejo Texas 80. O lo escribes entre comillas o puedes hacer referencia a una celda. Y el nuevo texto que quiero es D Cuántas instancias quiero. Voy a dejar eso fuera. Mi código 80 se ha convertido en D ive uno, señorita, echa un vistazo al resto. Este no ha sido tocado. Este no ha sido tocado en Lee, donde tengo 80 me dan d Esa es la función sustituta. Reemplazar es ligeramente diferente porque no estás buscando el texto real que estás reemplazando. En cambio, estás buscando una posición dentro de este texto para reemplazar, por lo que para demostrar, va a empezar volvería a reemplazar. Primero referenciaste al yo, y ahora necesitas seleccionar el número de inicio. Entonces, ¿cuál es la posición que quieres empezar a reemplazar? En este caso, es uno. ¿ Y cuántos personajes quieres reemplazar? Entonces si quiero reemplazar el 1er 2 caracteres por D, diría a y cuál es el texto que quiero reemplazarlo por su D Si copio esto hacia abajo , note que no estoy buscando 80 y reemplazando. Solo busco el puesto y digo: Empieza a leer a la una. Se reemplazaron los 1er 2 caracteres por D y así lo hace para todos. Ante esto, tienes algunas flexibilidades más. Entonces si quisiera reemplazar los dos últimos dígitos por 10 para estos códigos, podría hacer ese número de inicio. ¿ Cuál sería mi número de inicio en este caso, si todos los códigos tienen la misma longitud? Cuando puedo arreglarlo, puedo decir en total, ¿Qué es esto? Es 1234567 Así que quiero sustituir a los dos últimos. Y eso son seis y siete para que pueda ver. Empieza a las seis. ¿ Cuántos personajes no deberían leer también? ¿ Cuál debería ser el nuevo texto? Cero 10 Sí, así que gira mi 80 25 0 92 Se quita el 92 y pone 10 ahí dentro. También puedo decir que reemplace los dos últimos dígitos por dos ceros. Todo lo que tendría que hacer es hacer un reemplazo de nuevo texto escolar, número de inicio es el mismo. Seis. El número de caracteres a leer para reemplazar lo es, también. Y ahora nosotros dos ceros. Tengo que ponerlo entre comillas para decir que es texto cuando ponen en ceros. Por lo que estas dos funciones, sustitutos y reemplazo pueden venir muy útiles, sobre todo si estás extrayendo datos del sistema. Y es posible que necesites cambiarlos un poco y subirlos a otro sistema. Ahora, con esta función sustituta, ahora que aprendimos esto, también podemos usarlo aquí para el apellido. ¿ Cómo podemos hacer eso? Se me puede usar sustituto aquí. Podemos usarlo de manera bastante inteligente diciendo de este nombre completo, no vamos a sustituir el nombre en el segundo nombre si tienen uno sin nada, y eso significa que nos quedamos con el apellido. ¿ Cuál es mi texto antiguo? Mi vieja toma es básicamente mi nombre y mi segundo nombre. Eso es lo que estoy sustituyendo por nada. Y eso, básicamente es esto. Y pero recuerda, hay un espacio entre ellos, así que tengo que dar cuenta del espacio. Andi. Segundo nombre. Es eso que me da Toby Space Jane. De qué estoy sustituyendo esto con nada, Así que vamos a ver qué hace eso. Tengo a Carter con un espacio delante antes de recortar esto. Solo asegurémonos de que funcione para el resto. Anderson. Funciona bien. Este, funciona bien. Es todo lo que voy a hacer es en una función de recorte antes, y va a trabajar bien para todos ellos. Entonces así es como también puedes usar la función sustituta. Ahora. Esta no es una lista exhaustiva de funciones de texto que están disponibles. Es justo lo que encuentro que son las funciones de texto más útiles en Excel. Por lo que siempre que necesites manipular descripciones de códigos de texto, consulta esta conferencia porque probablemente encontrarás tu solución para ser una mezcla de estas técnicas aquí. Entonces, no, es tu turno de completar un ejercicio. 10. Fórmula 7: las funciones de la fecha principal de Excel: las funciones de estado son geniales cuando estás creando una línea de tiempo para tu proyecto, donde estás armando una cronología de informes que quieres enviar ahora. Lo que a menudo he visto hacer a la gente es que usan un calendario para hacer esto. No hagas esto porque puedes cometer errores. En su lugar, utilice las funciones de estado de Excel. Hay un montón de realmente útiles. El primer paso es saber que existen, y el segundo paso es aprender a usarlos. Aquí tienes una lista de las funciones de fecha que creo que son beneficiosas, y yo personalmente solía ahorrar tiempo como primer paso. Empecemos simple. Supongamos que tiene el año, mes y día cada uno en una columna separada, y desea obtener la fecha en una columna. Todo lo que tienes que hacer es usar la función de fecha. Simplemente tienes que seleccionar ¿qué columna tiene tu año en ella? Ese es éste. Entonces el mes. Entonces hoy cierra el corchete. Ahora puedes ver aquí que tengo un formato personalizado. Volvamos al formato con nuestros atajos, Control uno. Y aquí puedes ver el formato especial que he usado en este caso, puedes encontrar algún formato general de fecha aquí está bien, Pero si no encuentras lo que buscas, puedes crear el tuyo propio. Todo lo que debes saber es que por día usas D por mes es m por año es por qué y entonces la combinación y la forma en que lo usas define si ves el número o ves el texto. Por ejemplo, si escribiera d d d y luego otro d veo el espacio de coma de texto completo, entonces quiero ver la fecha real y luego quiero el mes que fue Mm. Y si escribo en otro M m a r, quiero la marcha completa, tengo que escribir en la M cuatro veces. Entonces puedo poner el año para el año completo. Eres bastante flexible en cómo quieres dar formato a tus fechas si usas el formato personalizado . Ahora bien, si esto fue formateado como general, ve un número y este número es básicamente el número de días desde enero de 1900. Porque Excel empieza a computar días a partir del año 1900 se puede ver que si cambio esto a 1900 este 1er esto se convierte en uno. Volvamos a poner estos. Y ahora de manera similar, puedo hacer lo que acabo de hacer por encima del revés. Entonces asuma que tiene la fecha. No llamará, Um, y en realidad necesitas dividirlo en tres columnas separadas. Lo que puedes hacer es usar estas funciones. Entonces por un año, solo usa año andan. Su referencia. El celular que tiene tus datos, Newt por meses, lo mismo. La última función para el día es día, y luego te refieres a tu fecha. Siguiente es la semana en que funcionan. Esta es una gran fórmula para conocer el día libre de la semana. Entonces si quieres saber fue el 21 de marzo y lunes o martes o jueves, puedes usar la semana que funcionan. Usted hizo referencia a la celda que tiene su fechado. Su tipo de retorno es importante porque eso es lo que define si tu semana comienza un domingo un lunes, martes y así sucesivamente por defecto. Excel considera tus semanas para empezar a partir del domingo. Esto a, en este caso a considerar las estrellas de cine del domingo significa que este es un lunes. Si dejo vacío este argumento, es lo mismo porque Excel tiene por defecto dos semanas a partir de un domingo, que es la que podría cambiar esto. Y si lo pongo a cuál sería ahora este número? El porque mi semana está protagonizada por un lunes y esto significa que este es un lunes. Ahora me puedo confundir si siempre tienes que pensar, ¿significa que es un lunes o un domingo porque esto era uno o dos? Lo que puedes hacer es ex esta célula para mostrarte el día. Pero hay que tener mucho cuidado aquí porque si vas a hacer eso, necesitas usar el predeterminado de Excel o básicamente nada, que por defecto es uno. Volvemos a nuestras celdas de formato, vamos a la costumbre. Vamos a cambiar esto por tratados o por cuarenta, y entonces realmente vemos el texto que está asociado al día de la semana. Pero como dije antes, para conseguir estos correctamente, hay que estar usando el predeterminado de Excel, porque si sí uso a a a no funciona. Piensa que el 21 es domingo y no lo es. Es lunes. Simplemente ten eso en cuenta si están cambiando el formato de ustedes mismos para reflejar el texto para el día de la semana. Siguiente en línea el número de semana. Ahora por disfunción, no es necesario buscar en el calendario para ver qué número de semana está asociado a este estado . Se puede utilizar la función de número de semana excels. Acabas de referenciar la celda con el Dayton. Es igual que lo hicimos arriba. Necesitas definir cómo tus estrellas de la semana dieron mi default. Uno es un domingo a es un lunes. Yo sólo voy a dejar ese vacío, para que eso lo revierta. A default. Semana de Mayo protagoniza a partir de un Domingo, el 21 de marzo de 2016. Echemos un vistazo al calendario que es el 20 de marzo es la semana 13, pero no en mi calendario. Tengo la semana a partir del lunes, así que tienes que tener bastante cuidado cuando estás usando esto. Entonces si cambiaría este no a una a obviamente esto seguirá siendo 13. Pero si mis fechas cambian al número 20 de la semana cambia a 12. Eso se convierte en domingo, y eso es en la semana 12. Si eres semana empieza el lunes. Por lo que siempre que utilices la función de número de semana, siempre toma esto en cuenta y también sé muy transparente en cómo eres débil. Los números están empezando, sobre todo si estás usando esto para definir plazos. O estás teniendo discusiones con otros compañeros que podrían suponer que el número de la semana comienza desde el día indiferente de la semana. Pero pasando a la función de jornada laboral. Esta es una gran función si estás creando un plan de proyecto o estás creando una cronología de informes o cualquier plantilla que tenga fechas y el número de días hábiles que la gente necesita o necesitas completar esta tarea. Entonces no confíes en el calendario. Porque si cometes errores, excel es la forma más segura. Este caso, tenemos fecha de inicio. Sabemos el número de días hábiles. Tenemos que completar esta tarea. Queremos saber en qué fecha necesitamos terminar esta tarea, excepto que tiene una gran función llamada función de día de trabajo. Workday por defecto excluye los fines de semana, lo que se excluyen los sábados y domingos. Y si lo desea, también puede excluir cualquier día festivo que defina usted en un rango separado. Este es mi día de inicio. Tengo tres días hábiles para esta tarea. Olvidémonos ahora mismo de los días santos. Asumamos que aquí no hay vacaciones. Necesito terminar esta tarea para el martes quinto. ¿ Y cómo funciona eso? Porque el fin de semana se excluye automáticamente. Ahora déjame mostrarte cómo también puedes excluir las vacaciones. Por lo que hemos trabajado. Día este. Ese es el número de días. Y ahora aquí este es el argumento opcional para los Días Santos. Todo lo que tienes que hacer es escribir o copiar y pegar tus vacaciones desde alguna plantilla en excel en algún lugar de una rabia específica y solo resaltarlo. Eso significa que terminaré el jueves 7 de enero. Podemos comprobar esto dos veces aquí. Yo solo quiero que esto nos dé una buena visión general el jueves. Empezamos aquí tres días, pero las vacaciones deben ser excluidas. Significa que no podemos tomar esto en cuenta. Tampoco es así que es 12 Eso es un día santo tres. Por lo que terminamos en realidad, una semana después del jueves a un jueves. Son tres días hábiles. En el último caso, solo quería mostrarte que también puedes hacerlo al revés. Por lo que también puedes regresar días atrás. Tienes tu fecha de fin y tus días pueden ser menos tres. Entonces retrocede tres días y tienes estos días santos Terminas el jueves 31. Es una obra en ambos sentidos. Otra función útil es la función de día de red y ese peor tipo de similar a ésta excepto que tienes una fecha de inicio en una fecha de finalización. Ambas son fechas, y quieres calcular el número de días hábiles que tienes a tu disposición entre estas fechas. Días de red, fecha de inicio. Esa es nuestra fecha de inicio. Fecha de fin es ésta. Y aunque gane, las vacaciones son opcionales. Entonces déjame simplemente creer ese argumento fuera. Yo consigo cuatro. Por lo que entre este jueves y martes, tengo cuatro días hábiles. Lo que hay que tener en cuenta cuando se utiliza la disfunción es que cuenta la fecha de inicio y la fecha de finalización también. Entonces aquí está la carrera del jueves. Entonces ese es uno dos porque aún no he excluido los días santos 34 Ahora excluyamos también los Días Santos . Por lo que tenemos días de red inicio y estos aires nuestros días festivos viernes está excluido. Esa es una, 23 y otra vez aquí. Yo quiero mostrarles que se puede ir hacia atrás. Entonces aquí tengo una fecha en 2016. Voy a volver al 2015 y quiero ver cuántos días tengo que volver. Entonces esa es mi fecha de inicio una ganancia, mi fecha de fin, mis vacaciones. Se puede ver que estas fórmulas de aire muy simples. Pero llegaron realmente, realmente no sólo te ahorran tiempo sino que también ahorran errores. Habría notado que cuando escribo estas fórmulas, obtengo una segunda opción. Entonces cuando estaba escribiendo en la función de jornada laboral, vendes Peor ocho Internacional. Esta función fue agregada desde Excel 2010 y la diferencia entre esta y la función normal de jornada laboral es que puedes controlar tus fines de semana para que puedas definir si tu fin de semana es un fin de semana normal de sábado domingo o es algo más y es viernes solamente. Tomemos el viernes. Sólo anticipadamente asume que el viernes es un fin de semana. Pero el sábado y el domingo son días hábiles, por lo que esto se introdujo desde Excel 2010. Para tener cuidado si estás usando esta función, pero distribuyendo tu archivo a personas que tienen versiones anteriores de Excel lo mismo está disponible para días de red. Tienes red Days International, donde podrás definir el fin de semana. Unos fines de semana siempre van a ser el sábado domingo. Basta con utilizar la función estándar de día de red y el trabajo que funcionan ahora solo para combinar estos, tal vez para un caso más realista. Entonces digamos que tienes días de inicio y fecha de fin. Tienes empresas en diferentes países, en este caso en Austria y Tailandia, y quieres calcular cuántos días hábiles cada persona en estos países tiene a su disposición para hacer esta tarea. Y tienes los días santos listados en algún lugar de Excel. Queremos escribir la fórmula de manera dinámica. Como primer paso, la fórmula que necesitamos calcular son días de trabajo son días de red. Fecha de inicio que sabíamos y fecha. Ya conocemos los días santos. Por lo que obviamente puedo escribir una fórmula diferente para Austria y una fórmula diferente para Tailandia. También podría usar las funciones. A. Si se trata de Austria utilizó este rango de lo contrario, utilice este rango. Pero supongamos que podría agregar en muchos países a esto, no usaría otra función, y voy a sugerir ¿Qué opinas la función de índice? ¿ Por qué? Porque quiero mostrarte otro uso fuera de la función de índice aquí. Quiero decir usado esta gama. Si es Austria utilizó este rango si es Tailandia. Entonces lo que quiero mostrarles es el otro uso fuera de la función de índice usando el número de área. Lo que puedes hacer aquí es seleccionar diferentes áreas separándolas con el Kama que solo hay que tener cuidado porque el movimiento para poner la coma aquí sobresalen las cosas. Pasas a tu número de rollo, pero no quiero pasar a mi número de habitación. En realidad quiero decir que esto es lo que estoy indexando. Pertenecen al mismo argumento. Por lo que tengo que trabajar con mis corchetes aquí y pone el soporte después de un terminado seleccionando el último rango. Y luego cuando ponen la coma, ya ves, luego pasa al número de carretera. ¿ Cuántas filas quiero mover? Bueno, no quiero mover ninguna regla. Yo solo quería tomar en cuenta cada una de las fechas que tengo en cada fila para que puedas saltarte ese argumento. Lo mismo para la columna. Ahora viene el número de área. Un número de área por defecto es este es uno. Entonces esa es mi primera área. Esa es mi segunda área porque seleccioné su segunda. Si sólo lo escribo. No, manualmente, pondría uno aquí. Escribo para Austria. Pensemos en nuestros corchetes. Necesito cerrar este. ¿ Quién es otra vez? Tengo cuatro días hábiles. Comprobemos eso de este jueves a este jueves. Y recuerda, día de la red cuenta fechas de inicio y fin también. Entonces ese es el uno. Es un día de fiesta en Austria. Esos son los fines de semana. Eso es 123 vacaciones Austria cuatro días. Si pongo en la zona número dos, lo que significa sacar a Tailandia en cuenta, obtengo cinco, porque aquí tengo la fiesta común entre Tailandia austriaca. Pero éste sólo es un día de fiesta en Austria y no en Tailandia. Entonces esa es una, 2345 Obviamente, quiero que esto sea dinámico, dependiendo de si se trata de Austria o Tailandia, que función devuelve un número o posiciona la función de partido. Coincidir con esto. ¿ Dónde quiero igualarlo aquí? Eso arregló eso Porque quiero jalar a través. Voy a arreglar estos en realidad también. Para que no cambien en estos también. Voy a igualar esto en este rango, y quiero tener una coincidencia exacta. Lo que esto devolvería es que mira esto aquí arriba y esta es posición un lado devuelve el que automáticamente significa área número uno. Si se trata de Tailandia, volvería a lo que significa zona número dos. Entonces eso debería funcionar. Bueno, si he hecho mi fijación correctamente funciona muy bien. Entonces en este ejemplo, se puede ver cómo podemos usar funciones siempre diferentes entre sí para obtener resultados dinámicos . Recuerda consultar estas funciones siempre que estés creando un plan de proyecto, una lista de tareas o un calendario de informes porque no solo ahorran tiempo, sino que te ahorran de cometer errores. Me regresaron a la práctica 11. Fórmula 8: cálculo dinámico: offset es una fórmula interesante, y es una que puede hacer tanto más de lo que parece a la vista. El offset es una forma de darle a Excel una dirección a la que acudir. Entonces comienzas diciéndole cómo Maney se levantó para moverse y luego cómo maney columnas para moverse para llegar a su destino. Ahora este destino puede ser una sola casa, como una sola celda. Pueden ser las calles, como muchas celdas en un cajón, una columna. Pero también puede ser como un pueblo como una zona. Organizar en excel. Ahora, podrías pensar Para qué diablos puede ser útil esto? ¿ Por qué alguna vez necesitaría usar una dirección en mis archivos de Excel en este ejemplo? Tengo meses aquí y ingresos por ventas aquí, y quiero sacar el promedio de los últimos seis meses. Por lo que obviamente una forma de hacer esto es usar la función promedio y luego resaltar este Blake esto. Pero ahora el problema es que este es un informe dinámico. Por lo que cada mes llegan nuevos datos para agosto, pongo en datos aquí para que este promedio de los últimos seis meses funcione correctamente. Tengo que arrastrar este rango hacia abajo manualmente, y eso es algo que quiero evitar. Y la función offset es lo que me ayuda a evitar esto. Para mostrarte cómo podemos hacer esto, te voy a enseñar offset por su cuenta. Como primer paso como segundo paso, vamos a integrarnos en la función promedio. El sintaxis de la función offset es que siempre se necesita un punto de partida una referencia. Es como antes de empezar tu paseo. ¿ Por dónde vas a empezar? Puedes escoger cualquier celda, pero debe estar cerca del rango en el que quieras terminar. Entonces voy a escoger esta celda y luego cómo Maney Rose, ¿quiero bajar? Entonces digamos que no me movería hacia abajo uno. ¿ Cuántas columnas quiero mover? Voy a poner cero aquí porque quiero quedarme en esta columna. Y los dos últimos argumentos son la altura y el con un 11 significa una celda. Estos dos últimos. Nunca pueden ser 00 Al mínimo, pueden ser un 11 Si quisiera, como estos tres, pondría 31 en el palo. Sería 11 1er obtenemos 100 por lo que empezamos aquí. Bajar Uno No te muevas. Columnas, Danos una celda. Son 100. Si me movería abajo tres. Obtengo 1 30 si muevo columnas. Si en realidad pongo uno menos. Entonces significando desde aquí. Ve. Aquí, me devuelven mi cita. Simplemente yo sólo tendría que cambiar el formato. Si hago un plus uno, obtengo un cero. Porque ¿dónde estoy? Yo estoy aquí. Pongámoslo a ver que estoy aquí. Eso es básicamente lo que hace offset. Si yo quería un rango, voy a poner esto Llamarlo de nuevo a cero. Y en lugar de tener uno para altura, voy a poner tres para alturas. Obtengo valor porque lo que hace es que estoy empezando aquí. Se movió hacia abajo 3123 y luego dame estas tres celdas para que en realidad no pueda poner estas tres celdas en una celda. Ahora, siempre que uses offset de una manera que estos dos últimos argumentos sean rangos, no solo están devolviendo una celda, sino un rango de celdas. Necesitas envolver tu fórmula offset en una fórmula que puedan manejar rangos. ¿ Cuál sería esa fórmula B, por ejemplo, La fórmula promedio, qué es alguna fórmula o fórmula de cuenta? Cualquier cosa que puedan manejar rangos. Entonces en este caso, vamos a usar la fórmula promedio 108. Por lo que ese sería el promedio de estos tres. Ahora, vamos a ampliar en esta fórmula para conseguir los últimos seis meses. Las diferentes formas de escribir esta sola manera es que primero puedes ver cuál es la última celda aquí usando la función de recuento y luego bajar a la última celda y luego como tu altura, como que vuelves atrás y resalta el menos seis o seis filas antes de esto, o básicamente encuentras tu última celda y luego vuelves a la seis para remar y tu altura son ellas más seis. No te confundas por esto. Sólo te voy a mostrar como primer paso. El sencillo es el que prefiero y luego al revés también. Posteriormente, también puedes jugar con él y ver cómo funcionan como primer método para escribir esto . Ahora empiezo aquí bien. Necesito saber cuánto debo bajar puedo hacer esto contando el número fuera celdas llenas que tengo aquí puedo usar aquí La función del recuento. Cuando cuenta una función cuenta a cuenta básicamente cuenta. Aunque su texto el conteo solo cuenta. Si estos son valores en este caso, no importa, cuenta o cuenta A y luego resaltan esto. Y lo importante es que destaques más de lo que tienes, porque en un futuro tendré datos aquí. Por lo que necesito tomarlos en cuenta también. El resultado de este recuento una función va a ser siete. va a decir ¡Muévete abajo! Siete, que es 1234567 Termino aquí. ¿ Cuántas columnas mover? Nada. Yo quiero quedarme en esta columna y ¿cuál es mi altura? Ahí entra menos seis porque quiero que el promedio de los últimos seis meses menos seis será esto de aquí para aquí, y uno es que soy yo con 113. Comprobemos ese 113. Entonces ahora cuando agregan un nuevo mes, dos días es del 1 al 7. Fuimos a siete actualizaciones de rango de mayo automáticamente también pueden comprobar esto yendo a fórmulas. Evaluar fórmula. Empecé aquí bajé ocho. No te muevas. Cualquier columnas menos 61 Así que el rango final que le está quitando el promedio es ser seis para ser 11 . ¿ Cuál es esto a esto? La otra forma de escribir esto. Como mencioné antes, solo muéstralo. ¿ Es la media de desplazamiento? Ahora podemos empezar aquí. En este caso, el número de filas que quiero bajar es que voy a hacer cuenta un bastón o vamos a hacer cuenta. Ahora me gusta esto y hago un menos seis. Entonces básicamente los resultados de la fórmula de conteo en este caso ahora van a ser ocho. Agregaron otro mes menos seis. También lo es. Sólo se mueve hacia abajo para subir 12 para mis columnas. Es cero no quiero mover ninguna columna. Y por mi estatura, no hago un menos seis, sino un pasado seis. Yo soy un con es uno. Entiendo eso que son los últimos seis meses. Dos formas diferentes de escribir esto Llegas a la misma conclusión ahora ¿Qué pasaría si tus datos estuvieran organizados de esta manera en su lugar? Entonces nos preguntamos Promedio de los últimos seis meses, pero los tenemos en filas. Hacemos lo mismo. Empiezo con la función promedio, luego el desplazamiento. Y mi referencia está aquí. Digamos que quiero empezar aquí. ¿ Cuántas filas quiero mover? Nada. Por lo que ese cero. ¿ Cuántas columnas quiero volver a mover? Eso depende de cuántas columnas tenga números en voy a usar para contar la función cuatro la función de recuento A. Y voy a destacar hasta donde creo que voy a tener números ahora para la altura. Debería ser sólo uno ahora, Pero porque con necesito de aquí ir aquí, necesitaba ser menos seis. De acuerdo, así que fue sólo al revés. Pero hace lo mismo de sacar el promedio de los últimos seis meses. Entonces si agrego datos aquí para agosto, obtengo del 1 al 7. Así que solo sé creativo y practica esto en todas direcciones hasta que te cuelgues del mismo. Ahora, otro buen ejemplo de esto es si tienes tus meses y tus ingresos por ventas y tienes un reporte donde siempre quieres el promedio de los próximos tres meses basado en una selección y dices que has incluido un desplegable aquí donde puedes seleccionar tu mes de esta lista y quieres sacar el promedio de descuento los próximos tres meses, vamos a hacer lo mismo. Por lo que vamos a utilizar la función promedio junto con el desplazamiento como punto de referencia que se puede arreglar. Siempre podemos empezar desde aquí. Y entonces, ¿cuántas filas queremos bajar? Bueno, en este caso, queremos bajar hasta llegar al 4 14 que está aquí. Nosotros queremos movernos hacia abajo tanto. Qué función puede un uso que me devuelve un número la función de coincidencia? Porque eso nos da la posición fuera 90 en esta lista y los resultados serían la cuarta posición. Entonces sé que necesito bajar por Rose primero. Necesito tener el valor de buscar arriba. Estoy buscando esto por aquí. Yo quiero un partido perfecto. Nunca olvides eso. Esa es la cantidad de filas que quiero bajar. Porque los resultados de esto son cuatro 1234 Ahora para las columnas. No quiero mover ninguna columna. Yo quiero quedarme en esta columna, así que eso va a ser cero para la altura. Eso depende de mi pregunta. Si quisiera incluir también este mes, pondría un tres y uno de un con 102. Entonces vamos a comprobar eso. Ese es éste. Estos tres. Son 100 y dos. Si no quería incluir este mes, solo tengo que sumar un plus uno a mi partido para poder decir Encuentra esto, luego bajar un extra. Esos serían los tres meses después de esto. Entonces esos serían estos. Esto es puramente dinámico. Entonces si bajaría en selecto diciembre 2014 llego a 32 En este caso, estoy haciendo los próximos tres meses a tres meses después de días. Serían estos tres, 232 Así se puede utilizar la función promedio, la función de desplazamiento, la función de coincidencia todos juntos para crear este efecto dinámico. Una palabra de precaución es que offset es una fórmula volátil, lo que significa que calcula cada vez que lo haces moverse en excel. No para ser honesto contigo en las faltas en las que he usado offset. Nunca he experimentado mi ayuno para volverse lento por mí usando offset. Pero también no les sobredosifico. Por lo que también te recomiendo que no los uses en exceso ahora. Como se puede ver, offset es una función que se puede utilizar dentro de muchas fórmulas para conseguir que tengan rangos flexibles en lugar de los fijos, lo que hace de la disfunción un candidato perfecto para su uso en informes dinámicos en tableros. Ahora te toca hacer un ejercicio. 12. Fórmula 9: FREQUENCY, PEQUEÑo y LARgo para organizar y clasificar: ¿ Alguna vez necesitó organizar sus datos en categorías y luego reportar sobre estas categorías ? Entonces, por ejemplo, suponga que se le ha pedido que informe esto. ¿ Cuántas personas hay dentro de estos grupos de edad en su empresa? Ahora le preguntas a tu departamento de RRHH, se lo dices. Por favor, dame la edad de cada persona que trabaja aquí, y luego te dan esto. Ahora, ¿a qué se dedica? ¿ Pasas por la lista manualmente y luego asignas cada una a una categoría y los sumas ? ¿ O vas por filtrar tu lista, o usas una función A compleja? Después de ver el sector, no harás nada de esto. Vas a usar la función de frecuencia. A menudo se pasa por alto la fórmula de frecuencia porque no es una fórmula normal. Está bajo muy función. Esto significa que tiene que ser mecanografiado de una manera especial. Pero lo bueno de ello es que es una fórmula extremadamente simple, y puedes ahorrar mucho tiempo. Entonces en este ejemplo, queremos saber muy rápidamente cuántas personas tenemos que caen entre 20 a 30 años edad, 30 a 40 40 a 50 y de 6200 y menores de 20 y vamos a utilizar nuestra función de frecuencia para ello. Ahora bien, es muy importante que le prestes atención al primer paso al último paso porque esos aires debido a pasos que la mayoría de la gente pierde y luego la fórmula no funciona. Entonces el primer paso es que no solo escribes tu fórmula en una sola celda, sino que resalta el área donde quieres que tus respuestas sean tus números. Ves cómo eso difiere de una fórmula normal. Y luego pongo en mi fórmula. En primer lugar está la velocidad de datos. Estos son tus datos de rol sobre los que te han dado. Nos han dado esto. Entonces viene su matriz de bins y sido zari es sus intervalos. Tienes que escribirlos así obviamente puede resaltar esto. Este es el texto para apoyar esto. Y a lo que esto se traduce es a lo que he escrito aquí. Entonces si tienes 2030 40 solo nota que 30 significa de 20 a 30 y treinta incluidos. Todo lo que sí sé es cerrar el corchete y ahora es el último paso. Preste atención aquí, no presione enter como presiona en una fórmula normal. En cambio, vas a presionar control, mantenerlo pulsado, y luego presionar turno. Sujétalo. No hay prensa. Entrar. Ahí vas. Esa es una fórmula array. Ahora, vamos a comprobar si lo hizo bien, Así que deberíamos tener tres personas que están abajo en, incluyendo 20. Por lo que estos tres Carrie acaba de conseguir más. Entonces si yo, por ejemplo, cambiara por ejemplo, este 21 a 18 eso es para ello a las siete. Dijo esto tratado como una sola fórmula porque tiene que mirar todo el conjunto de datos y reasignar, y no se pueden cambiar partes de esta fórmula, así que no puedo entrar aquí y escribir lo que sea menos uno. Como puedes ver, la función de frecuencia es una fórmula muy simple que te da el número de elementos fuera dentro de tus datos que caen entre intervalos específicos. Pero puedes usar esto para salarios, ingresos, ingresos, cantidades, cualquier cosa que sea numérica. Y no te olvides de control turno entrar. En esta conferencia, también he incluido para muchas funciones que ayudan con la clasificación y categorización de sus datos. Aquí hay una lista de ellos aquí. Tengo una lista de empresas y los ingresos, y quiero crear un reporte que muestre lo mejor el peor tercio mejor y justo peor ingreso y empresa para mejor. Probablemente conozcas esa fórmula. Es la función max. Entonces, Max, solo tienes que resaltar el rango donde quieres encontrar el máximo en. Ese es este corchetes de cierre, y ese es mi valor. Para la Compañía J, lo peor es la función principal. Nuevamente, hacemos lo mismo. Eso es 1000 Company K, excepto que tiene algunas otras fórmulas realmente útiles que puedes usar en el ranking. Entonces si quieres el número 1234 mejores empresas o la tercera empresa con peor desempeño, puedes usar estas fórmulas para la tercera mejor estaríamos usando la fórmula grande. Esta función es similar a las marcas, excepto que tienes la opción de decidir los números. ¿ Es como el tercero mejor, el cuarto mejor, y así sucesivamente. Lo primero es resaltar el rango y luego poner en el número el rango que desea obtener . Por lo que en este caso fueron los terceros mejores. Vamos a poner un tres muy querido, el segundo mejor al que se lo voy a poner. Y por supuesto, no tienes que escribirlo. También puedes hacer algunas referencias, Así que si tuvieras 1234 En una columna separada estarás haciendo referencia a esas celdas. Ese debería ser mi tercer mejor. Entonces este es mi número más alto. Y luego es éste. Y luego es éste para la tercera peor. Es la función pequeña. Haces lo mismo. Dejé su rango. Entonces lo puse tres. Si quiero encontrar lo peor, deberían ser mis hombres. ¿ Verdad? Ves, ese es el mismo. Si yo quería lo segundo peor, eso es 9000. Y el tercero peor nos lleva a 11 mil. De acuerdo, entonces estamos a mitad de camino. Pero también queremos quitarle el nombre al mejor. El peor. Tercera, mejor y tercera guerras. ¿ Qué función se puede usar para eso? El índice que mucho funciona? ¿ Qué? ¿ Alguna para indexar aquí? ¿ Qué quiero de vuelta una empresa, verdad? Por lo que sólo necesito indexar las empresas. ¿ Cuántas filas quiero bajar? Bueno, eso depende de los asesinatos. El número está sentado ahora. Puedo hacer referencia a la celda donde puedo escribir la fórmula en una fórmula. Lo primero es que quiero las posiciones que necesito usar la función mucho. Y dentro del partido En lugar de decir match this, también puedo decir match the max of this in. ¿ Dónde debería volver a mirar? Aquí, El tipo mucho es cero empresa J. Vale, así que no olvides que puedes hacer referencias celeb o escribir la función en la función para que pueda copiar este ritmo este año. Cambia esto a empresa de hombres K. Cambia esta fórmula aquí a qué era grande? Pero ahora tengo que definir cuál 13 Y aquí fue pequeño tres. Ese es mi informe. Pero, ¿cuál es el problema aquí? Bueno, ¿qué pasa si dos empresas tienen ingresos idénticos? Esa es una empresa G es 2500. ¿ Y si la empresa A también tuviera ese 3 de mayo mejor va a ser empresa A. Pero si cambio esto a mi segundo mejor también sería compañía. Una empresa G no va a aparecer en mi reporte en absoluto porque el índice que un partido va a parar aquí va a ver esto. Se va a igualar a la empresa A. Se va a dejar la fórmula para evitar esto. Lo que puedes hacer es introducir una columna auxiliar en tu tabla de datos. Digamos que lo llamamos ayuda para ingresos. Manipulas los números por cantidad muy, muy pequeña para hacerlos únicos. Son diferentes formas de manipularlos. La forma en que normalmente lo hago es agregar, y uso la función de rol. La función de rol básicamente devuelve el número de regla, pero obtengo es este rol más? A 47 básicamente devuelve 47. Es el número de fila de un 47 que es 47 algunos sumando 47 a este número. Esto es lo que hace la función de rol. No quiero agregar esto. Voy a dejar eso fuera. Si lo dejas vacío así, básicamente toma la fila. ¿ Estaba escribiendo o fórmula en? Si yo dejara que Usted lo ve suma 49 a esto. Entonces usé esto y luego dices, no quiero pedir un número tan enorme dos días. Divido esto por como un 1,000,000 y termino sumando un número muy pequeño a esto. Y la razón por la que uso rol es porque cuando arrastro esto hacia abajo, obviamente mi rol cambia porque aquí son 50. Aquí hay 51 algunos siempre agregando un pequeño número diferente a cada uno de estos, lo que los hace únicos. Nos fijamos en éste ahora, éste tiene un seis año, mientras que éste tiene un cinco. En base a esto, hago el ranking. Entonces volviendo a nuestra fórmula aquí, vamos a deshacernos de esto. Lo que no quieres hacer es hacer esto. Esto se ve bien. Pero algún colega o alguien podría encontrarse con este informe y van así. Y eran como, ¿Qué? ¿ Por qué tenemos estas cosas que agregaron estos números? ¿ Por qué nuestros números no coinciden exactamente con los del sistema que está manipulando esto y demás? Entonces para evitar cualquier discusión que pueda surgir de esto y cualquier credibilidad que pudiera perderse Debido a esto, no sugiero que hagas esto. En su lugar, se encuentra el número basado en los domingos, pero luego se muestra el número original. ¿ Y cómo podemos hacer eso? ¿ Qué fórmula se puede usar para esto? Son diferentes formas de hacer esto para que puedas usar offset. Un oleaje. Tú lo eres. Se puede utilizar índice. Y como hemos estado usando índice ahora, vamos a apegarnos a eso. El rango que necesito indexar es donde está mi respuesta aquí. Eso es lo que quiero mostrar y cuántas filas necesito bajar para mostrar que se basa en esto. Por lo que nuevamente empezamos con partido. Estamos buscando esto. Por lo que estamos buscando el tercero más grande mejor en este caso. Dónde lo estamos buscando aquí arriba. ¿ Quieres una coincidencia exacta? Porque así si abrí esto, todo está bien. Es nuestro número original. No. De la misma manera, haremos la función pequeña, no viene Air index a match. ¿ Qué necesito aquí? Una empresa justo sobre Lee Necesita hacer es destacar esta área. No, otra vez. Son mucho se basa en no este número más. Al no poder resaltar la celda en este caso, necesito volver a encontrar el tercero basado en mi columna Ingresos ayudante en este. Y quiero una coincidencia exacta para las columnas. Puedo saltarme eso. Obtengo empresa A como ahora. Mi tercero oficial, ¿es realmente mi tercero? Ese es mi primero No, Sólo segundo es éste porque este número es un poco más grande que este número. Ahora viene la prueba. Yo quiero buscar el segundo mejor ese número es el mismo. Pero ahora debería conseguir la Compañía G. Y lo hacen. Funciona muy bien. Yo lo sé. Yo sólo voy a copiar esto. Cambia esto a pequeño cambia esto a tres. Esa es mi empresa. E. Volvamos a poner estos también. Y así es como puedes usar eficazmente las funciones grandes y pequeñas para clasificar tus datos. Aunque tengas duplicados, no, no, para volver a hacer un ejercicio. 13. Fórmula 10 SUMPRODUCT de el Gurus: tener en cuenta la secuencia de mi top 10 no es importante. Lo importante es que conozcas estas funciones y sepas usarlas cuando llegue el momento. Dicho eso, como que he guardado lo mejor para el final. No estoy seguro de si algún producto fue realmente diseñado para ser así de genial o si acaba llegar a ser así. Tienes que ver esta conferencia para averiguarlo. El uso clásico de algún producto es hacer una suma de multiplicación. Pero para darte un ejemplo, tienes aquí precio y cantidad para calcular tus ingresos totales. Vas a multiplicar precio por cantidad y luego sumarás los valores en la Columna de Ingresos . Ahora tienes una columna calculada y un total para esta columna calculada, que es tu ingreso total con algún producto. Por otro lado, sólo tiene una fórmula que lo hace todo por ti. Empiezas escribiendo algún producto. Después solo resalta tu columna de precios Atacama para pasar al siguiente argumento, seguido de tu corchete de cantidad y tienes tus resultados. Lo bueno de algún producto es que puede funcionar en un aumento de valores, y no tiene que ser ingresado como una función de matriz. Entonces no hay necesidad de presionar este control shift enter como lo hicimos cuando aprendimos la función de frecuencia es como una muy elegante cada función. Lo que realmente está haciendo es tomar la primera celda, multiplicándola por el 2do 1 haciendo lo mismo para el siguiente rol y agregando el resultado. Entonces básicamente se multiplica y luego suma. Por eso es importante que tus rangos tengan el mismo tamaño. Echemos un vistazo más detallado a esta función. Entonces la forma en que lo escribí aquí, uso la coma, ¿no? Eso es por defecto. Es así ahora, la forma en que generalmente lo hago, Mis algunas fórmulas de producto es así, y les voy a explicar por qué, pasar por este ejemplo, este fue un caso sencillo. Si alguna vez has usado algún producto, probablemente lo hayas usado de esta forma con algún producto. Lo que algunas personas no saben es que también puedes hacer excepciones por lo que podría conseguir la suma de un say en este caso cantidad por solo producto A y puedes usar la misma fórmula y ahora porque estoy haciendo una excepción. Solo busco productos. Mejor producto A. Tengo que destacar esto. Entonces ven igual a esto. Esto es algo con lo que hay que tener tanto cuidado. ¿ Están los paréntesis aquí? Por lo tanto, siempre que utilices este signo igual en algún producto, asegúrate de que tu argumento esté por separado dentro de corchetes. Y entonces digamos que usé un argumento normal con este caso de coma. Quiero pasar a la siguiente Array. Yo quiero cantidad. Entonces todo lo que tengo que hacer es resaltar esto y ya ves, asegúrate de que eres rangos son obviamente del mismo tamaño. Entonces voy a cerrar esto en términos. ¿ Funciona? No. Permítanme cambiar esto a esto. Funciona. ¿ Por qué? Porque lo que esto hace aquí es comprobar si este es producto un verdadero o falso. Entonces ese es el resultado de sobresalientes. Check es en este caso, es cierto. Verdadero, verdadero falso cae Verdadero. Cuando lo dejas con el Kama, deja lo verdadero falso es texto y puede hacer cualquier cosa con él. Por lo que es cero cuando se utiliza el signo de multiplicación y Excel intenta multiplicar una celda. Ese es Bin. Entonces eso es cierto o falso. Se convierte en uno y ceros. Entonces, básicamente, ¿esto se convierte en 101? Entonces lo hace. Uno multiplicado por 200. Eso son 200 más uno, multiplicado por 8 33 más uno, multiplicado por 100 así sucesivamente. Es por eso que utilizo el sitio más aplico. Vamos a ver si nuestro número es correcto. Entonces solo sumemos dondequiera que tengamos producto. 80 cantidades. Eso es por tres. Eso funciona. Bien. Si sí quieres usar la coma, lo que puedes hacer es tener dos nuevos operadores numéricos en esta celda. Lo que hacen algunas personas es que hacen esto. Esto es básicamente menos para conseguir esto. Entonces es como si lo estuvieras multiplicando por uno menos, invertiéndolo. También puedes hacer como uno multiplicado por esto. Siempre y cuando estés haciendo algún tipo de multiplicación en este Excel va a traducir un verdadero dedo del pie falso, uno ceros. Entonces con el fin de simplemente evitar todo eso, solo cambio esta coma a esta. El movimiento va a calcular un ingreso. Yo hago lo mismo. Tengo algún producto. Yo quiero mi excepción ahí otra vez también. No olvides el corchete. Asumamos que no tengo esto. Llámalo aquí. Voy a hacer precio y luego puedo hacer una ganancia dos veces cantidad para doble comprobación. Eso van a ser estos Y éste. Es que ahora lo que la mayoría de la gente no sabe es que algún producto puede hacer tanto más que esto por su naturaleza fuera de la forma en que está diseñado, puede hacer lo que buscamos arriba o indexar un partido puede hacer. También puede hacer lo que algunos efectos pueden hacer y hacerlo aún mejor que algunos si s ahora puede quedarse conmigo en este, porque entender esta función te va a salvar de hacer mucho apagado entre cálculos e innecesarios en sus archivos existentes de Excel. Lo bueno de algún producto es que también puede hacer o condicionar. Y eso es algo con lo que no se puede hacer. Por ejemplo, algunos si s o cuentan si ISS Si recuerdas, cuando cubrimos esta función uno Murray cubre algunos efectos, solo pudimos comprobar una cosa por un criterio en cada columna. Por lo que sólo podíamos comprobar si las empresas A pero no podíamos hacer ambas no podíamos hacer y o a o B . Nos restringieron en cada columna a una condición. No estamos con algún producto porque se puede usar o condiciones aquí, Así que vamos a ver cómo se puede hacer eso. En este caso, quiero ver si es empresa A o empresas. Ves, ese es mi año en mi mes. Mira, estoy haciendo cuenta. Voy a usar algún producto para contar. Te voy a mostrar cómo. Ahora estoy diciendo si la empresa es a o B Así que de nuevo, los corchetes son muy importantes en este caso, esta es mi empresa. Si es igual a esta vez, voy a cerrar eso. Es mi primer argumento. Ahora el o argumento es un plus aquí. Ese es el o esto es. Y esto es o entonces si este rango es igual a este corchete de cierre, mi totalidad o condición necesita estar entre corchetes separados. ¿ Se puede ver que la lista sólo persona recurre para ver qué pasa? Obtengo un número. Entonces eso debería significar que son siete instancias fuera de la Compañía A y C aquí dentro. Eso es correcto. En realidad también estoy haciendo cuenta, pero no he terminado con mi argumento porque aún quiero revisar para el año 2014 y meses para ser enero. Entonces aquí mismo voy a dar la bienvenida a Bracket Year es igual a esto. Recuerda, siempre que tengas el signo igual, pon esa parte entre paréntesis y mi mes. Por lo que deben ser cinco instancias para esta combinación. ¿ Ahora qué? Te voy a mostrar, vamos a comprobar eso. Pero vamos a comprobar eso aquí. Porque lo que he hecho aquí abajo es mostrarles el algún producto en cámara lenta lo que está haciendo, sea cual sea la fórmula que haga, es que se convierte a estos dos verdaderos falsos como primera instancia. Entonces en nuestro caso, vimos, como la empresa A o C 2014 jam thes lines, ya sea uno donde todo es cierto como un segundo paso porque nos estamos multiplicando, los está convirtiendo en uno y cero. Entonces básicamente pasa por esto y hace una vez una vez cero que es 00 veces. ¿ Quieres insulina? Cero. Estos son uno y estos aire uno. Entonces en total, puedes ver tengo cinco instancias donde obtengo una cuando así funciona de la misma manera que tenemos los algunos para cantidades de una de alguna cantidad. Pero este sigue siendo nuestro criterio para hacer las cosas simples. Sólo voy a copiar esta fórmula, pegarla aquí, ¿de acuerdo? Porque hasta aquí, eso es lo que necesitamos es idéntico a lo que necesitábamos por cuenta. Pero ahora solo necesito traer mi cantidad. Llámalo. Eso es por 70. Porque vamos a revisar Aquí hace este sistema de tiempo ya que y luego veces esto, que básicamente es una vez esta una vez 91 veces 100. Y eso nos da por 70. Ése son nuestros 4 70 de aquí. Ahora voy a pasar a los ingresos. Yo solo quiero multiplicar cantidad por precio. Asumamos que no tenemos esta columna. Entonces todo lo que tengo que hacer es traer el precio. Simplemente expande en él y obtienes más. Ese es mi ingreso. Ese es éste como el último en precio medio. ¿ Cómo podría calcular esto en este caso? Porque recordar, a algunos de los productos, siempre está sumando suma. Entonces, ¿cómo podría obtener el precio medio? Bueno, podría conseguir la suma del precio y luego dividirme por mi cuenta. De nuevo, es lo mismo, pero no seamos perezosos. Sólo escribamos esto muy rápido. Son excepciones. Ven primero. Sé que quiero las o condiciones, así que sólo voy a hacer a corchetes adicionales. Esto es igual a este soporte de juego más. Sigue olvidando eso. Pero cuando te voy a mostrar lo que pasa si no haces el soporte, así que vamos a hacer esto es igual a esto sin corchetes mientras solo pongamos los corchetes. Por lo que ahora sólo tengo que añadir este que ven aquí. No necesito un corchete. No tengo signo igual por lo que puedo dejar eso tal como está, y eso va a ser May algo de descuento. Entonces sólo puedo decir, dividido por éste. Ya he hecho el cálculo aquí. Ese va a ser mi precio medio. Eso es sólo doble cheque precio medio. Obviamente, estas dinámicas del aire. Entonces si cambio esto a Empresa B o C, los resultados cambian bien. ¿ Ves por qué algún producto es una función tan inusual y útil? No es sólo la suma de productos, sino que se puede utilizar como un conteo si la función es alguna función if y también una función look up. Ahora bien, presta atención a tus corchetes, sin embargo, porque su colocación es realmente importante. Si estás escribiendo esto por primera vez, eso revisará tus resultados y luego confiará en él. Si alguna vez te olvidas de la sintaxis como ¿cuál fue el más cuatro? ¿ Pueden usar eso? ¿ Fueron los rasgos de culo o el coma o los paréntesis? Vuelve al cuaderno de trabajo de demostración para refrescar tu memoria. No hay avergonzar que lo haga yo mismo Ahora este regreso, amigo ejercicio.