CONSEJOS PRO de Excel Parte 3: Fórmulas

1. Introducción a los consejos de fórmula: bien, bienvenido a una de mis secciones favoritas consejos de fórmula. Ahora esta es una de las partes más desafiantes y técnicas del curso y abarca temas que van desde básicos como modos de cálculo y herramientas de auditoría hasta bastante avanzados, como demasiados look ups y algún producto para plantear. Tenga en cuenta, eso se estará moviendo muy rápido, por lo que puede ser difícil mantenerse al día. Si ya no te encuentras cómodo con lo básico, zambullémonos. 2. Calculación manual vs.: todo bien. Siguiente punta es una punta de fórmula. Este es un concepto de una estrella, bastante básico, pero es algo que no todos los usuarios de Excel están al tanto. Y es cómo cambiar tu fórmula. Modos de cálculo ahora Excel tiene dos modos de cálculo primarios. Automático, que generalmente es su predeterminado y manual. Por lo que si te diriges a tu pestaña de fórmulas hacia la derecha, verás tus opciones de cálculo. En realidad hay tres opciones diferentes. Ellos son los dos en los que nos vamos a centrar ahora mismo. Son automáticos, lo que permite su cálculo formalista por su cuenta con cualquier trabajo de cambio y manual, que esencialmente congela esos cálculos de fórmula hasta que decida actualizar un recalcularlos. Y puedes hacerlo haciendo clic en ese botón calcular ahora en la pestaña de fórmulas o usando el atajo F nueve. Ahora esa tercera opción automática, excepto las tablas de datos, vamos a hablar de tablas de datos en la sección analítica de este curso. Esos se utilizan a menudo para aleatorizar y ejecutar un gran número de simulaciones. Es parte de sobresalientes. ¿ Y si las herramientas de análisis y porque generalmente iteraban sobre un número muy, muy grande de filas, pueden ralentizar las cosas si están recalculando constantemente. Entonces esa tercera opción básicamente te da la opción de decir, ya sabes, tratar todas mis otras fórmulas de cuaderno como automáticas. Pero déjame decidir cuándo quiero actualizar esas tablas de datos específicamente. Entonces digamos que tienes tu modo de cálculo configurado en manual como vemos aquí, y luego escribes algún tipo de función como esta. Esta es la fórmula profeta que vamos a escribir en esta demo y básicamente da cuenta cantidad, precio al por menor y costos unitarios. Arrastras esa fórmula hacia abajo, aplicas a varias celdas y ves el mismo valor devuelto en cada caso. primer lugar para buscar está ahí el modo de cálculo, porque nueve de cada 10 eso significa que accidentalmente ha sido cambiado a manual. Pero hay buenas noticias. Es una solución muy fácil. Todo lo que haces es volver a esa pestaña, comprobar la opción automática, y entonces esas fórmulas se recalcularán automáticamente por sí mismas. Por lo que para resumir pareja casos de uso común, en algunos casos puede tener un cuaderno muy grande, muy pesado fórmula, usando cosas como buscar y funciones de índice y referencia, o un aumento que iterando muchos, muchas veces más. Un gran número de filas En aquellos casos en los que los cálculos tardan bastante tiempo, posible que quieras cambiar temporalmente al modo manual ya que estás haciendo otras ediciones de libros de trabajo . Sólo por el bien de la velocidad y la eficiencia, Sólo asegúrate de cambiarlo de nuevo a automático cuando quieras que esas fórmulas vuelvan a calcularse . El segundo caso de uso común. Nuevamente, como dije, es permitir que tus fórmulas se calculen por su cuenta automáticamente. Pero trata tus tablas de datos por separado. Entonces vamos a saltar a un cuaderno de trabajo pro tip y te mostraré cómo es esto. Está bien, así que desde nuestra tabla de contenidos, voy a mirar en nuestra categoría verde o punta de fórmula aquí. Los modos de cálculo de primer artículo siguen adelante y enlazan con ese pueblo, y aquí tenemos una muestra de unos 100 productos junto con la cantidad vendida. Tienes el precio de venta al por menor y el costo unitario para cada uno de estos productos, y solo queremos calcular el beneficio por ventas aquí. Ahora, antes de que yo lo haga, vamos a entrar en su pestaña de fórmulas. Perforar en nuestras opciones de cálculo. Verás que de forma predeterminada, mi libro de trabajo se establece en automático. Adelante y solo por el bien de las demostraciones, cambia esto a manual y luego a la derecha o funciona aquí. Por lo que nuestra fórmula de ganancia de ventas va a ser la cantidad veces el precio menos el costo por lo que dicen es igual a B dos veces paréntesis abiertas. Precio al por menor menos el costo unitario. Ciérrelo. Presionar enter. Eso devuelve $12,058.25 que es el valor correcto para camino a. Pero al igual que les mostré, si aplicamos esto todo el camino hacia abajo, veremos ese mismo duplicado repitiendo valor, lo cual obviamente es incorrecto. Por lo que voy a llegar a hacer volver a fórmulas opciones de cálculo automático. Y ahí tienes, todo. recalcularlo para reescribir la fórmula o algo así, y estamos bien para ir. Entonces otra vez, concepto muy sencillo, pero realmente útil. Uno a tener en cuenta una vez que sepas que existen estos dos modos de cálculo 3. Respiros de líneas de fórmula: este pro tip se trata de usar saltos de línea de fórmula, que es una gran manera de mejorar la legibilidad y organizar tus fórmulas. Ahora es una punta de una estrella muy, muy básica. Pero es algo de lo que en realidad no estaba consciente desde hace bastante tiempo. Y la idea es que mientras realmente estás escribiendo o editando dentro de la barra de fórmulas de Excel en lugar de solo presionar, enter, que presentará tu fórmula, sostén Alz primero para que cuando presiones ault e ingreses tu crea un salto de línea y divide tu fórmula en múltiples líneas. Entonces para darles un ejemplo en esta demo que caminará a través, tenemos esta declaración si anidada, eso no es extremadamente largo. Pero es bastante largo, y lo que vamos a hacer es tomar esa fórmula usó los saltos de línea del para romper cada condición, cada individuo if declaración en sus propias huevas. Y eso no cambiará nada de cómo calcula la fórmula ni de los resultados que produce. Todo lo que está haciendo es ayudar a mejorar la organización y la legibilidad. Por lo que cosas muy sencillas aquí. Casos de uso comunes nuevamente, desglosando funciones, especialmente como funciones condicionales como esta en estas múltiples líneas para aislar criterios individuales o hacer más fáciles de interpretar fórmulas anidadas más complejas por presentando cada una de las nuevas funciones de componente en una nueva línea. Por lo que llegarás al grano a medida que te adelantas muy con fórmulas y funciones donde estás combinando todo tipo de cosas como Offset e Index y Count Day y Rose. Y si los sientes a todos dentro de una sola cadena interminable de funciones, pueden ser muy, muy difíciles de interpretar y diagnosticar. Algo va mal. Entonces vamos a saltar a Excel y darle una oportunidad a esto. Muy bien, entonces desde nuestra tabla de contenidos, busco la línea de fórmulas, rompe demo y sigue adelante y presiona link, y esto te llevará a una pestaña que contiene información sobre 24 tiendas diferentes o supermercados. Tengo una región i D. Tienes Ciudad Estado, país, país, Y luego esta columna Total Square Foot, que está impulsando esta fórmula en la columna H. Y esta es una declaración si anidada, estamos probando múltiples criterios y esencialmente aplicando algún tipo de etiqueta basada en el total de pies cuadrados, por lo que dependiendo del tamaño de la tienda, lo estaban etiquetando como pequeño, mediano, grande o muy grande. Por lo que este será un buen candidato para esos saltos de línea de Ault aquí en la fórmula Bar. Ahora vamos a hacer clic en la fórmula Barra. Lo primero que podemos hacer es pasar el cursor sobre el borde inferior hasta que veas que dos apuntan una flecha haga clic y sosténgalo y arrástrelo hacia abajo. Eso sólo nos va a dar un poco más de espacio con el que trabajar dentro de esa barra de fórmulas. Y ahora lo que queremos hacer es crear saltos de línea entre cada una de las declaraciones if, cada una de las condiciones o criterios individuales que estaban probando. Entonces solo coloca tu cursor justo antes de eso. En primer lugar, si, después de la coma y mantenga las ALT mientras presiona enter, eso va a seguir adelante y mover la prisa. Esa fórmula hasta la siguiente línea. Lo mismo exacto aquí antes de la siguiente. Si el estado de cuenta Ault entra y luego una vez más para la final. Si el estado de cuenta Ault entrar. Por lo que ahora podemos ver mucho más claramente. De acuerdo, estamos probando los valores en G para escribir en la primera prueba. ¿ El metraje cuadrado es mayor a 35 mil? Si es así, vamos a etiquetar esa tienda muy grande. Si eso no es cierto, vamos a pasar a la siguiente línea es el tamaño mayor a los 30,000. En caso afirmativo, etiquetar en libertad. Y así es como estas anidadas si funcionan las declaraciones es que progresan a través de una serie de verdaderas pruebas falsas. Y luego si ninguno de esos aire es cierto, tienes este tipo de catch all value false. Y supongo que en realidad podríamos bajar esto a sus propias huevas también, porque ese es el resultado final. El sello final, que es otro. Y ahí no tienes presionamos, entramos y aplicamos eso hacia abajo. Nada debería cambiar en absoluto, con la excepción de que hemos hecho nuestra fórmula un poco más legible, un poco más fácil de usar. Por lo que ahí tienes, agregando saltos de línea tus fórmulas usando todo para entrar. 4. Conversión de unidades de la medición: Entonces esta siguiente demo es una de la que estoy realmente entusiasmada porque es una función que en realidad acabo de descubrir por primera vez hace unos meses. Llamado Convertir, podemos hablar de cambiar tus unidades de medida usando esta función de conversión Así que la fórmula actúa exactamente como suena permite cambiar valores de una unidad de medida a otra. Por lo que es posible que tengas datos que están en libras, y necesitas convertirlos en kilogramos. A lo mejor tienes lecturas de temperatura y Celsius, y quieres Fahrenheit dado aplicar, convertir a campos de fecha u hora convirtiendo minutos en segundos sin tener que multiplicar por 60 o agregar constantes adicionales a tus fórmulas. Entonces vamos a hablar ahí unas cuantas demos diferentes, una de las cuales va a ser temperaturas promedio que se registran en Fahrenheit. Y lo que haremos es simplemente usar esa función de conversión. Verás que aparece una caja con todas tus opciones, y lo que es importante tener en cuenta es que no verás la lista completa de unidades de medida cada vez que esa lista va a ser dinámica, y va a depender de tu unidad desde. Entonces en este caso, las únicas opciones que veo, dado que he ingresado a Fahrenheit. Las unidades de temperatura son Celsius, Fahrenheit Kelvin y esas otras, con las que no estoy familiarizado, pero sus opciones específicas de temperatura. Por lo que es un simple es seleccionar la opción que desea en este caso, la etiqueta de mar para Celsius. Y ahí tienes. Has creado una nueva columna calculada convertida. Tan bastante sencillo Usar casos aquí. Número uno. Hacer esas conversiones rápidas sin necesidad de buscar tasas de conversión o calculadoras, y en segundo lugar, convertir valores específicos de fecha u hora sin tener que agregar constantes de fórmula. Vamos a saltar a nuestro libro de trabajo pro Tips y hacer algunas conversiones. Por lo que desde su tabla de contenidos estaban mirando su sección de punta de fórmula aquí. Busca la demo de conversión de medición PressLink, y cuando aterrices en esa hoja, verás dos tablas diferentes. Datos obtuvieron temperaturas mensuales promedio aquí, cuales se registran en términos de Fahrenheit Columna B. Vamos a usar convert para convertir esos valores de Fahrenheit en Celsius, y también tenemos algunos individuos aquí con métricas de altura y peso, y tenemos altura en pulgadas y peso en libras sólo porque a EU le gusta confundir a la gente y no usar el sistema métrico, Así que vamos a convertir esas pulgadas en dos centímetros fueron a su vez las libras en kilogramos . Entonces empecemos temperatura en Celsius, vamos a empezar con un converso igual Abrir el príncipe es el número que queremos convertir vidas aquí mismo y ser tres, y vamos a convertir de Fahrenheit. Por lo que si conoces la etiqueta, simplemente puedes escribir f entre comillas o puedes buscar a través. Para ser honesto, no estoy seguro de qué orden estos, Aaron y yo no sabemos una forma rápida de buscar, pero aquí es como en el medio un poco hacia abajo. Después puedes hacer clic en esa opción ya sea doble clic o tabulador para tirarla a la fórmula. Y entonces el último argumento es, ¿en qué conviertes a Fahrenheit? Y ahora, al menos se encoge dinámicamente esa lista en remolque sobre Lee, las opciones específicas de temperatura. Entonces Celsius es el que necesitan Esta vez toda tableta de cerca, el paréntesis y pulsa OK, y ahí lo tienes. Por lo que 41.7 grados Fahrenheit es de unos 5.39 grados centígrados. Y sí revisé estos con una calculadora de conversión solo para asegurarme Pero puedes hacer lo mismo si te gusta y doble clic y su hace. Hemos convertido todas nuestras temperaturas Fahrenheit en Celsius con el clic de un botón . Ahora pasemos a alturas y pesos, así que empezaremos con la altura. Vamos a convertir pulgadas en dos centímetros. Por lo que iguala convertir. Estamos convirtiendo F tres de pulgadas. Busca un poco por ello. Ahí está, me m tablet en y la unidad que queremos convertir esas pulgadas demasiado bien. Aquí están las opciones. ¿ Tienes metros, millas? Millas náuticas atrás en pulgadas, pies, yardas, Angstrom. Pero notarás que aquí no vemos centímetros. Hecho. La opción más cercana que tenemos son los metros, así que podrías estar tentado a decir, Está bien, bueno, no tengo suerte. Dejemos caer metros, cerrarla y luego multiplicarla por 100 para convertir esos en centímetros, lo que te dará la respuesta correcta. 83 pulgadas es de 210.2 centímetros. Pero aquí está lo que descubrí, es que aunque la opción no exista en muchos casos, escribir la abreviatura funcionará independientemente. Entonces voy a decir, Escucha, Escucha, no quiero metros quiero centímetros lo cual es universalmente notado como cm poner eso dentro, citas cierran mi fórmula prensa enter y van a conseguir la respuesta correcta sin tener que introducir esa constante a la multiplicación por 100 en nuestra fórmula. Entonces ahí tienes, aplíquelo hacia abajo. Y ahora, caso muy similar vamos a tratar de convertir libras dos kilogramos igual convertir el número que estaba convirtiendo su celular h tres Vamos de libras, señora Libras Mass El bien Doble click, y lo estamos convirtiendo en kilogramos lo cual no vive en esta lista. Tenemos gramos. Entonces al igual que antes, una opción podría ser usar gramos y luego dividirse por 1000 para convertirse en kilogramos. O simplemente podemos decir: Hey, Hey, el símbolo para kilogramos es K g Cierra el paréntesis. Ahí vas. Obtenemos la respuesta correcta. 260 libras es igual a poco menos de 118 kilogramos. Por lo que ahí vas pareja demos por cómo puedes usar esta función de conversión súper práctica para cambiar tus unidades de medida 5. Herramientas en tiempo real con HOY, YA: todo bien. Esta próxima demo es divertida. Vamos a usar funciones volátiles hoy y ahora para agregar en tiempo real sin forma a nuestros libros de trabajo para que hoy y ahora las fórmulas básicamente acaben de devolver la fecha y hora actuales . Y debido a que son funciones volátiles, eso significa que automáticamente vuelven a calcular con cualquier cambio de libro de trabajo. Ahora bien, si por alguna razón no quieres que cambien, solo quieres estampar la fecha u hora fija actual. Puede utilizar el atajo de punto y coma de control para devolver el día actual o el punto y coma de turno de control para devolver la hora actual. Y a diferencia de las funciones de hoy y ahora, esas simplemente se mantendrán duras codificadas. Esos no cambiarán dinámicamente. Ahora para esta demo, vamos a construir un par de herramientas de ritmo de proyecto bastante rápido. Y en el primer caso, vamos a utilizar una función Today para construir un rastreador de proyectos basado en días pasados y días restantes. Y luego usaremos una función now para construir una herramienta similar que se basa en las horas pasadas y horas restantes para los proyectos a corto plazo. Ahora, casos de uso común. cualquier momento que desee mostrar la fecha u hora actual en una celda de hoja de cálculo, puede utilizar hoy o ahora y crear herramientas como esta, como herramientas de programación o cronología que desee actualizar en función del día u hora actual. Así que saltemos a Excel y realmente practiquemos trabajar con estas funciones de hoy y ahora. Muy bien, Así que desde tu pestaña de tabla de contenidos, busca eso hoy y ahora Demo en tu fórmula sección Consejos y sigue adelante y presiona link para saltar a esa pestaña verde. Y desde aquí verás que he construido una plantilla de marcador de posición en blanco para estas dos herramientas de rastreador de proyectos que vamos a construir, y el 1er 1 va a estar basado en la función Today. Por lo que seleccionando Celular C cuatro si fuéramos a usar el control semi Colon. Ese atajo caerá en el día actual, y ese día actual es correcto. Es 16 de octubre pero el problema es que, si vuelvo mañana o la próxima semana, este valor seguirá leyendo 10 16. No es dinámico. Es estático y codificado duro. Y eso no es lo que quiero aquí, porque queremos construir una herramienta que sea dinámica que cambie en base al día actual y a la hora actual. Entonces por eso, voy a usar la función de hoy, y ni siquiera necesito ingresar ningún argumento. Simplemente abre y cierra los paréntesis, presiona enter. Y ahí tienes. Ahora tenemos ese mismo valor. 10 16 2018. Pero cambiará cada día que acceda a este cuaderno de trabajo. Por lo que he caído en una muestra, inicio, fecha y fecha de fin Aquí. Podrás cambiar estos valores como más te convenga si quieres explorar y jugar con esto. Ahora, una cosa que es importante saber sobre las fechas de Excel es que cada fecha, igual que ésta tiene un valor de fecha subyacente. Ese es un número entero. Y ese número representa el número de días que han pasado desde que sobresale inicio de los tiempos, apenas el 1 de enero de 1900. Y debido a que cada fecha está respaldada por ese valor de fecha número entero, significa que podemos aplicar operaciones matemáticas y estadísticas simples contra celdas de fecha para hacer cosas como funciones de inteligencia de tiempo. Entonces, para darles un ejemplo de eso, si queremos calcular la duración del proyecto en términos de días, simplemente podemos restar la fecha de vencimiento y la fecha de inicio. Eso nos dirá OK ahí. 728 días en todo este proyecto. Cronología. Ahora, misma lógica para las otras fórmulas. Número de días que han pasado. Bueno, eso es sólo la fecha de hoy, menos el proyecto Fecha de inicio C cuatro menos C seis y los días restantes es un simple ya que la fecha de vencimiento menos la fecha de hoy C siete menos C cuatro. Al presionar OK, verás poblar este gráfico de rosquillas, lo que ahora nos dice. De acuerdo, estás 288 días en este proyecto que representa el 40% de toda la línea de tiempo del proyecto. Buena manera de mantener el pulso al progreso, sobre todo para proyectos a largo plazo como éste. Ahora, el remate que voy a hacer aquí es que si poblamos una fecha de vencimiento que ya pasó como el 1 de octubre de 2018 por ejemplo, verás que pasaron algunas cosas raras. Nos quedan unos días negativos que se traza en el gráfico y una especie de forma extraña de evitar que eso suceda. Vamos adelante y fórmula modificadora aquí y vendamos. Ver 11 y voy a colocar el cursor justo después del signo igual y añadí si declaración simplemente para decir si la fecha de hoy es mayor que la fecha de vencimiento. La fecha de vencimiento ya pasó en coma a nuestro valor. Si eso es cierto, Y si los valores verdaderos, si la fecha de vencimiento ha pasado, entonces sólo vamos a entrar un cero porque no quedan días en este proyecto común una vez más al valor de falso. De lo contrario, sólo vamos a ejecutar nuestra fórmula C siete menos C cuatro igual que antes. Cierra esa princesa prensa entrar. Y ahí vas ahora, o injerto tipo de gorras al 100%. Y podemos seguir adelante y agregar la fecha que quieras en el futuro y hacer 11. 25 2019 por ejemplo, Ese caso fueron 42% del camino a través de ese proyecto. Entonces ese es un buen ejemplo de cómo usar la función Today con cálculos de números enteros. Ahora vamos a ponernos un poco más granulares y practicar trabajando también con fechas y horarios . Y debido a que queremos la fecha hora, no sólo la fecha como un número entero, vamos a volver a usar la función ahora. No hay argumentos de fórmula sólo se abren. Cierra esos paréntesis, presiona enter y verás que me da un valor de 10 16 2018 15 42. Esa es la hora militar para las 3 42 PM Y ten en cuenta que esto no me está dando sólo el componente de tiempo . Me está dando la fecha y la hora combinadas. Entonces tengo igual que antes entré en algunas restricciones iniciales para este proyecto. Voy a empezar la hora de 9 a.m. Hacer tiempo de 5 p.m. Así que estamos lidiando con una línea de tiempo mucho más corta aquí dentro de un solo día. Y lo único que tengo que hacer antes de que pueda avanzar es en realidad despojar justo el tiempo de esto ahora. Entonces de lo contrario, no podré hacer comparaciones directas contra estos valores. 9 de la mañana y 5 de la tarde Entonces aquí, en la celda C 21 quiero que esta celda regrese justo el componente de tiempo de esa fecha. Te voy a mostrar dos maneras de hacer eso. El primero es usar función de tiempo sobresale, y busca una hora en poner una entrada de minuto y una segunda entrada, y voy a conseguir esas entradas usando funciones de tiempo y apuntando a mi ahora. Entonces el tiempo que quiero regresar se basa en actual nuestro que es la hora de ahora el minuto actual, que es el minuto de ahora, y el segundo actual, que es el segundo actual de ahora. Cerrar a paréntesis, Centro de prensa. Eso traduce toda la cadena de fecha y hora en sólo el reloj solo 3 44 PM, que es exactamente lo que quiero ahora. Hay una forma más rápida de hacer esto, pero requiere un poco de una comprensión más profunda de cómo estas funciones de fecha y hora y los valores de fecha están funcionando realmente. El fondo es que los días individuales y excel serán tratados como números enteros, ¿ verdad? Si hago clic derecho en esta fecha, vendo formato 10 16 2018, las celdas entran a categoría general. Esa es una buena manera de ver cómo se ve el valor de fecha subyacente real. 43389 Nuevamente, ese es el número de días que han pasado desde el 1 de enero de 1900. Tenga en cuenta que es un número entero si comparo eso, así que eso es cancelar para que no aplicemos ese formato. Comparemos eso con la función ahora, que recuerdan, es el día actual, más el reloj también. ¿ Puedes adivinar cómo se va a ver eso? Ve en general. Tenga en cuenta que es ese mismo valor de fecha con valores adicionales después del punto decimal. Y eso es porque los tiempos se tratan como fracciones de días y fracciones de días. En términos de fecha, los valores sólo parecen fracciones de números enteros. Entonces si queremos sólo el reloj, que sería sólo el 65577 que sigue a todo el número, todo lo que necesitamos hacer es escribir literalmente una fórmula que tome el ahora cálculo y resta el día, que es el número entero, y te queda justo ese resto, que ha pasado un minuto. Entonces ahora son 3 46 PM Forma ligeramente más fácil de hacerlo, pero de nuevo, requiere ese tipo de comprensión más profunda de los valores de fecha. De acuerdo, así que de vuelta al proyecto, todo lo que necesitamos hacer ahora para llevar esto a la línea de meta es calcular la duración del proyecto en horas, que es simplemente el tiempo debido menos dos tiempo de inicio. 19 millas, 18 horas pasadas va a ser nuestra hora actual menos la hora de inicio. Esto es igual que esos cálculos de fecha que acabamos de hacer arriba y luego duran pero no menos importante, las horas restantes van a ser el tiempo debido menos el tiempo actual C 19 menos C 21 Pulse entrar y allá vamos. Por lo que nuestro proyecto comenzó a las nueve. AM Va a terminar a las cinco. Actualmente, son 3 46 lo que significa que solo me queda una hora y 13 minutos en el 85% del camino. A través de esta línea de tiempo, mejor me agrieten si quiero terminar este proyecto ahora. último, pero no menos importante, queremos crear esa misma modificación si declaración para controlar para los casos en los que ya hemos pasado el debido tiempo. Podemos hacer eso usando exactamente la misma metodología aquí. Si nuestro tiempo actual en C 21 es mayor al debido tiempo. Si pasamos el debido tiempo, entonces no quedan horas en su proyecto. De lo contrario, calcule C 19 menos C 21. Entonces ese fue un curso acelerado muy rápido en valores de fecha de Excel y hoy y ahora funciones. Pero esperemos que eso te esté dando algunas buenas ideas para cómo puedes usar estas funciones volátiles para crear tus propios cálculos en tiempo real. 6. Herramientas de auditoría de fórmula: Este siguiente consejo se trata de encontrar y corregir errores en tu hoja de trabajo utilizando herramientas de auditoría de fórmulas . Ahora, en tu pestaña de fórmulas, encontrarás un grupo de herramientas individuales bajo la categoría de auditoría de fórmulas que están diseñadas para ayudarte a trazar referencias, evaluar tu fórmula, cálculos y en última instancia diagnóstico y corregir cualquier error en tu hoja de trabajo. Entonces vamos a hablar de todas estas diferentes opciones en esta demo. Vamos a empezar con trazar Precedencia Independencia, que básicamente dibuja flechas a cualquier celda que impacta o se ven impactadas por el valor seleccionado, algo así como una relación padre-hijo. Y aquí un ejemplo de cómo se ve eso en este caso, que hablará ahí en nuestra demo. Estamos rastreando la precedencia que impactan a estos efectivo para cerrar y valores de gastos mensuales para que podamos entender qué celdas de entrada impactarán esos valores o esas salidas. También tenemos una serie de otras herramientas de las que vamos a hablar de mostrar fórmulas, simplemente va a mostrar temporalmente cualquier fórmula de hoja de cálculo como texto, y luego la comprobación de errores va a escanear la hoja en busca de errores, ayúdenos a rastrear la fuente de nuevo a cualquier celda precedente. Y por último, mi favorito personal, la herramienta de fórmula de evaluación que nos va a permitir evaluar realmente cada componente individual de una función o fórmula paso a paso. Y eso es genial para determinar dónde podría romperse una fórmula, sobre todo si tienes una fórmula muy compleja o una serie de funciones anidadas. Entonces una tonelada de casos prácticos de uso aquí para uno, simplemente entendiendo cómo algunas de estas complejas fórmulas y funciones podrían estar operando el número dos. Visualizando, que vende factor en una determinada fórmula, salida o venta, y luego tres. Rastrear, diagnosticar y esperar arreglar la fuente de cualquier error con el que te encuentres para que saltemos a la demo. Pongámonos manos en nuestro cuaderno de trabajo pro tip y practiquemos trabajar con algunas de estas herramientas de auditoría . Está bien, así que si lo estás siguiendo, adelante y abre tu cuaderno de trabajo pro tip. Busca la demo de herramientas de auditoría de fórmulas en la sección de Consejos de Fórmula y sigue adelante y presiona vinculado para saltar directamente a esa hoja. Y lo que estamos viendo aquí es una calculadora de propiedades. Es modelo básico que en realidad usé para evaluar los costos de propiedad y préstamo, y esencialmente lo que está pasando aquí es que tienes alguna información básica sobre una propiedad , tienes un precio de compra y una tasa impositiva. Puedes ingresar aquí algunos términos del préstamo como un pago inicial y tasa de interés y un término como Y esos valores correrán a través de una serie de cálculos y finalmente escupirán el efectivo requerido para cerrar el inmueble y una estimación de gastos mensuales. Pero obviamente, no estamos aquí para hablar de pagos hipotecarios y costos de propiedad. Ahora mismo, estamos aquí para hablar de evaluar fórmulas. Entonces con eso, vamos a entrar en nuestra pestaña de fórmulas. Echa un vistazo a nuestro grupo de auditoría de Fórmula, cual contiene esas herramientas de las que hablamos. Y empecemos hablando de precedencia y dependencia porque suena muy complicado. En realidad es bastante simple, y una forma de pensarlo es, ya sabes, para este valor de gasto mensual, la celda que he seleccionado esto es una especie del final del flujo de cálculo. Este número de gastos mensuales no se alimenta en remolque ninguna otra fórmula. No hay otras celdas de salida que se ven impactadas por este valor de gasto mensual. En otras palabras, esta célula no tiene células dependientes, y puedo demostrarlo haciendo clic en dependencia de trazas, y obtendré este tipo de mensaje de advertencia que dice que no hay fórmulas que hagan referencia a esta célula activa. Pero hay células,por supuesto, por supuesto, que el servicio ingresa a este valor de gasto mensual. En otras palabras, hay células. Ese acto es precedencia a la celda seleccionada. Y para mostrar esos precedentes, todo lo que necesito hacer es hacer clic en ese botón y va a decir, Sí, Sí, estos cinco valores aquí H 14 a H 18 todos impactan este número de gastos mensuales. Es decir, si cambias alguno de estos valores, los gastos mensuales también cambiarán. Misma historia aquí, con efectivo para cerrar, no hay dependencia. Este es el final del flujo de cálculo, pero hay una serie de precedencia, por lo que el efectivo para cerrar valor es una función del precio de compra, pago inicial y los costos de cierre estimados. Ahora, otra forma de contar una historia similar aquí podemos eliminar a los árabes es seleccionar una fórmula, vender y o bien hacer clic en la barra de fórmulas o utilizar la F para atajar para editar. Y qué va a hacer esto. No obtendrás las flechas, pero seguirás viendo celdas seleccionadas que están referenciadas dentro de tu fórmula y lo que realmente me gusta de este enfoque es que también están codificadas por colores, por lo que puedes especie de mapearlas al individuo componentes dentro de esa fórmula, solo una herramienta más que puedes poner en tu bolsillo trasero para ayudarte a diagnosticar y entender tus fórmulas. Así que adelante, presione enter, y hemos rastreado la precedencia desde estas celdas. Pero la relación opuesta también es cierta, por lo que sabemos que el efectivo para cerrar era un dependiente del precio de compra. Por lo tanto, precio de compra es un precedente al efectivo para cerrar, y podemos demostrarlo rastreando la dependencia desde esta celda. Y ahora lo que esto nos está mostrando es que si cambiamos precio de compra, hay 1234 cinco celdas o salidas que van a cambiar como resultado. Ahí cinco celdas dependientes basadas en este valor y, ah, un consejo fresco que no descubrí hasta hace poco es que esto sólo te muestra el primer paso de la ruta de cálculo cuando a su vez, algunos de estos las células dependientes también tienen dependencia adicional desde ahí, por lo que el precio de compra impacta, monto del préstamo, costos de cierre e impuesto predial. Pero si volvemos a hacer clic en traza dependencia ahora, podemos ver que hay otra capa aquí donde el valor del impuesto predial, como hemos demostrado, también impacta esta salida de gastos mensuales. Por lo que ahora estamos viendo tipo de la cadena completa de eventos y el flujo de cálculo completo a través múltiples pasos. Herramienta realmente poderosa. Adelante y quita esas flechas. Te mostraré cómo se ve la herramienta show formulas. Bastante sencillo. Simplemente se estira ustedes mismos para que pueda mostrar las fórmulas reales. Texto preguntado tipo de todo lado a lado. Buena manera de comparar. Ya sabes qué vende en una hoja son constantes y cuáles son fórmulas y luego podría simplemente desactivar ese honor. Ahora a mi opción favorita, la herramienta Evaluar fórmula. Escojamos una de estas fórmulas aquí, Um, Um, algo así como el impuesto predial. Haga clic en evaluar fórmula. Se va a tirar hacia arriba este cuadro de diálogo y verás desde el principio la misma fórmula exacta que verás en tu barra de fórmulas. Pero lo que puedes hacer ahora es en realidad mirar el componente subrayado al hacer clic en evaluar aquí solo ese componente subyacente evaluará. Por lo que se acaba de decir que H tres evalúa a 499.000 porque H treses ese precio de compra y luego cuando eso se divide por 1000 que evalúa a 4 99 Entonces, medida que avanzamos, podemos continuar con el proceso. H cuatro es 9.5, 9.5 veces para 99. Este 47 40.5, dividido por 12 nos da ese 3 95 pago mensual del impuesto predial. Entonces otra vez, Gran manera de no entender tus fórmulas a un nivel más profundo. Y hay otra característica para que evalúe la fórmula. Herramienta que puede ser útil. Muéstrale que vamos a ir a nuestro efectivo para cerrar fórmula, y vamos a evaluar esa. Y es bastante simple. Fórmula H tres veces H siete más H 11 h tres es para 99 h siete Este punto para multiplicarse juntos. Es 99 800. Ahora esto es lo que llegamos a H 11. H 11 contiene un valor de 99 80 pero ese 99 80 no es un valor estático, codificado duro. Se produce a sí misma a partir de otra función. Por lo que este paso en botón permite decir Ok, cada 11 es un poco más complicado. Vamos a ver cómo se está produciendo H 11 y al ir un nivel más profundo pelando hacia atrás tipo de una capa más de la cebolla ahora podemos ver que H 11 se calcula como H tres veces 30.2 puede evaluar este tipo de fórmula anidada dentro de nuestra original y luego dar un paso atrás y decir: OK, OK, así es como estamos llegando al 99 80. Entonces cuando los sumamos, llegamos a nuestro producto final, que es 1097 80. Por lo que ahí lo tienes. Eso es evaluar fórmula. Obviamente, todo parece mucho más fácil y sencillo cuando las cosas iban bien. Entonces pasemos por una muestra rápida de cuándo las cosas podrían no estar funcionando como cabría esperar. Entonces digamos que queremos ingresar el pago inicial por ciento del 10% pero nuestro dedo resbala látigos y escribimos 10 p por ciento. Ya sabes, obviamente algo anda mal aquí. Tenemos cuatro celdas que ahora están escupiendo error de valor. Este es un buen momento para usar esa herramienta de comprobación de errores aquí, que en realidad recorrerá tu hoja y dirá: Oye, Oye, encontramos un montón de errores. Primeros en la Celda J nueve. Da click siguiente. Es otro en h 10 Tengo uno en cada 14 uno en J tres y luego tendrás un montón de opciones aquí para cada uno de los errores. Ah, ayuda en este error te llevará al sitio web de soporte de oficina. Mostrar pasos de cálculo realmente te llevará a la herramienta Evaluar fórmula y específicamente al paso que rinde el error. Y esto aquí mismo me va a mostrar que estamos tratando de multiplicar 499.000 que es un valor por esta cadena de texto rodeada de comillas. Porcentaje de 10 p que evalúa a ese error de valor que, obviamente, a medida que lo llevas a cabo, sólo va a arrojar un error final al final del día. Por lo que está cerrado que de otra manera se puede utilizar esta herramienta de comprobación de errores si vamos al siguiente error. ¿ Qué es? Lo completó. Este es el que queremos Gastos mensuales. Volver atrás y comprobación de errores. A veces se obtiene esta opción realmente rastreó el error también. Y cuando hagas eso, poblará esas flechas precedentes con una diferencia. Se pondrá roja la flecha en el escenario donde se producen los errores y esta es una buena manera de rastrear las cosas hacia atrás y decir Bien, paso uno está bien. El paso dos está bien. Punto donde debe evaluarse el Paso tres. Ahí es donde se está produciendo un error. Entonces sé que es el valor de entrada justo antes del Paso tres lo que está causando mi problema, que en este caso es vender H siete. Es ese porcentaje de pago inicial estropeado, y hemos aislado el problema para que podamos seguir adelante y cerrar esto. Podemos quitar nuestras flechas y podemos fijar ese porcentaje al 10% y presionar enter y todo está bien con el mundo. Por lo que muchas herramientas ahí hay mucho que cubrir, pero realmente útiles opciones valiosas para guardar en tu bolsillo trasero si trabajas con fórmulas y funciones y excel. 7. Informes de estilo dinámico con fórmulas: todo bien. Este pro tip es todo sobre el uso de estadísticas, funciones y específicamente funciones de estadísticas condicionales para generar informes de estilo pivote. Entonces tal vez te estés preguntando de qué diablos hablo de hablar de algunos ifs. Contar. Si el promedio de ifs, estas estadísticas condicionales funcionan que permiten resumir datos, dada una determinada condición o conjunto de condiciones. Y cuando lo piensas, así es exactamente como funciona la tabla pivot, donde esas condiciones aire definidas por etiquetas de fila, etiquetas columna y tus filtros. Entonces, para darles un ejemplo, vamos a estar viendo un subconjunto de esos datos de película I M D B aquí y Columnas A a F y lo que tenemos es una pequeña vista de tabla dinámica como esta, que básicamente se filtra hacia abajo al género iguala acción. Tenemos los cinco principales países por conteo de título, y aquí estamos resumiendo tres métricas o valores diferentes. Tenemos el conteo de los títulos surgieron. Tenemos la parte de la columna de ingresos, y tenemos el promedio de la columna IMDB Score, y la idea aquí es producir algo como esto, que esencialmente está replicando ese mismo diseño y funcionalidad de tabla exacto. Onley estará utilizando fórmulas celulares fuera del entorno de tabla dinámica. Para ello, vamos a usar estas estadísticas condicionales confiables. Funciones, renueva conteo, ifs para calcular título conteo algunos ifs para calcular los ingresos y promedio de ifs para calcular la puntuación media de IMDb. Ahora quizá te estés preguntando si podrías hacerlo en un pivote sin escribir una sola fórmula. ¿ Por qué pasarías por todo este trabajo extra y básicamente recrear la rueda desde cero ? Entonces mi opinión es que los pivotes son una gran herramienta para tipo de análisis no guiado no estructurado, lo que significa que son geniales cuando realmente no sabes lo que estás buscando y solo quieres rebanar y dar dados y explorar las cosas del dedo del pie. Conoce más sobre tus datos. Por otro lado, si sabes cómo quieres segmentar tus datos y sabes qué valores quieres mostrar como parte de algo así como un informe o panel, el uso de estas funciones de estadísticas te permite hacerlo dentro de las celdas del libro de trabajo, que te dará mucha más flexibilidad para diseñar y dar formato y diseñar tu informe exactamente como tú elijas, especialmente en comparación con la construcción de un informe de estilo pivote con cosas como rebanadoras y gráficos pivote , que también son grande por sus propias razones. Entonces los casos de uso aquí que me voy a centrar en nuestro diseño de informes dinámicos con formato personalizado sin usar pivotes y filtrar o segmentar tus datos sin procesar en base a un determinado conjunto de criterios, que es exactamente lo que son estas funciones diseñado para hacer. Entonces sigamos adelante en nuestra demo en nuestro cuaderno de trabajo pro tip y veamos si podemos hacer que esto funcione. De acuerdo, entonces desde tu tabla de contenidos, vas a buscar los informes de estilo pivot Demo la IP de reinicio. Se trata de una dificultad moderada, por lo que ayuda tener un poco de antecedentes en estas funciones de estadísticas. Ah, pero aunque no lo hagas, ojalá pueda seguir bastante de cerca. Vamos a enlazar a esa hoja de trabajo. Y al igual que describí aquí, tenemos un subconjunto de los datos del IMDB. Tiene alrededor de 3700 títulos, y las columnas con las que tenemos que trabajar son los propios títulos, el país de la lengua de género. Soy DB puntaje ingresos y presupuesto. Y aquí tenemos una tabla pivotante para ir a Rick Tools cambiar fuente de datos. Verás que este pivote está efectivamente conectado a nuestros datos y columnas A a G. Adelante. Presionar OK, Y de nuevo tenemos aquí es Ah, vista rápida que muestra el conteo de los títulos. Algunos de los ingresos y la puntuación media del IMDB se filtran en títulos de acción específicamente. Y tenemos un filtro de valor para mostrar los cinco primeros ítems o en este caso, países basados en cuenta de título. Entonces presiona bien y aquí acabamos de agregar un poco de ah, marcador de posición plantilla en blanco donde vamos a usar estas fórmulas de estadísticas condicionales para básicamente replicar los valores exactos que estamos viendo aquí arriba. Por lo que acabo de copiar caído en los mismos nombres de país. He agregado aquí una celda que sólo contiene la palabra acción. Por ahora, podrías convertir eso en validación de datos. Bajar si quieres. Todo lo que vamos a hacer aquí es intentar replicar estos valores sobre Así que empezar con el conteo del título obviamente son resúmenes. Modo ation aquí es un conteo. Entonces por esa razón, vamos a usar una función de conteo si y como estamos probando por dos criterios, queremos contar filas bajo dos casos. Uno donde el país es igual a U. S. A. Y a donde el género iguala acción porque tenemos múltiples condiciones. Vamos a utilizar la versión plural de esa función contada llamada Conde Ifs, y seguir adelante y abrir el paréntesis. Y aquí es donde simplemente alimentamos esta fórmula. Estos pares de rangos de criterios y criterios. Entonces nuestro primer criterio es el país. Hay valores de país Columna viva d presiona F cuatro para bloquearlo y los criterios que estamos buscando, lo que estamos tratando de filtrar dentro de esa columna D en este caso es el país llamado USA. Podemos dejar a este familiar en este caso, o puedes ir en bicicleta con F cuatro hasta que bloquees tu columna, porque las etiquetas de los países siempre vivirán justo aquí en la columna I. Pero queremos que el camino cambie hacia abajo a Reino Unido y subió 17 Francia y Fila 18 y así sucesivamente y así sucesivamente. Y eso se comin a nuestro próximo Rango Criteria, que es un género Range vive aquí y la columna B F cuatro para encerrarlo en los criterios para el género vive justo aquí y vender J 13 y siempre vivirá justo aquí en J 13 para que podamos arreglar toda esa referencia. Cerrar el paréntesis pulse enter. Y ahí tienes. 700 a títulos o se levantó para nosotros. A películas de acción que partidos son mesa pivotante arriba. Y como hemos establecido correctamente nuestros tipos de referencia, simplemente podemos arrastrar ese valor hacia abajo y obtener 69 27 24 14. Entonces tenemos un partido. Todo está bien ahí. Ahora, vamos a seguir un enfoque muy, muy similar aquí para calcular los ingresos. Excepto que esta vez no estamos contando. Estamos sumando tan misma lógica exacta. Vamos a usar algunos ifs. Y la única diferencia va a empezar con los algunos valores de rango que queremos sumar . Vive aquí en la columna F los valores de ingresos. Y eso sólo entraría en esos criterios, rangos y valores tal como lo hicimos antes. Por lo que primeros criterios país y criterios D es U S A bloque. El segundo criterio me remito es el género y ser y el criterio final a es acción en J 13. Bloquearlo en cerrar el paréntesis pulse Intro. Ahí vas. 59 mil millones de dólares lo aplican a la baja. 3.991 punto 766 49 8 43 Boom. Tenemos un partido. Básicamente acabamos de hacer el trabajo de una tabla pivotante utilizando estas funciones de estadísticas condicionales . Ahora, último pero no menos importante, queremos el promedio de la puntuación IMDb. Entonces lo adivinaste. Esta vez vamos a usar el Ifs promedio. Igual que algunos ifs. Excepto que en lugar de un cierto rango, vamos a enchufar un rango promedio. Este caso que está e encendido. Pasar por ese mismo proceso. Los criterios oscilan entre un país. Qué criterios de Estados Unidos van al género. Y queremos que la acción se bloquee, ciérrelo presione enter y arrástrelo hacia abajo. 623656 Todo el camino hasta 648 Y ahí lo tienes. Y ahora, debido a que estas funciones de estadísticas condicionales están apuntando a celdas que los usuarios realmente pueden actualizar o cambiar, eso significa que nuestros valores en este pequeño informe que hemos construido son completamente dinámicos, solo como un pivote. Entonces si, por ejemplo, filtra en películas de aventura aquí, podrías cambiar tus criterios Cell J 13 por aventura. Y ahí tienes. Obtienes los mismos valores coincidentes. Por lo que casi hemos construido nuestra propia pequeña tabla de pivote, um o informe de estilo de pivotes. construido nuestra propia pequeña tabla de pivote, Al menos usar estas estadísticas condicionales, funciones como contar Si cumbre y promedios herramienta realmente útil, especialmente si necesitas construir informes o paneles para resumir tus datos y quieres un poco más de flexibilidad en términos de el diseño y el diseño. 8. Cuenta las palabras en una célula: bien, Este próximo pro tip es uno de mis favoritos. Es una forma realmente inteligente de combinar fórmulas de texto en Excel para contar el número de palabras dentro de una celda. Y cualquiera que esté familiarizado con las funciones de texto probablemente haya oído hablar de la longitud o de la fórmula L E N , que esencialmente simplemente calcula el número de caracteres en una cadena de palabras o una cadena de texto . Y eso es útil, pero no del todo lo que necesitamos aquí, ya que estamos tratando de calcular cuenta de palabras enteras. Y así para hacer lo que necesitamos hacer, vamos a necesitar ponernos un poco creativos aquí y combinar algunas funciones adicionales como sustituto y recorte para conseguir lo que necesitamos. Entonces para nuestra demo, vamos a estar buscando algunos datos de cata de vinos donde tenemos estas descripciones de catadores en columna F. Y nuestro objetivo será crear un contador de palabras en la columna G que cuente. Las palabras de la columna F Simple es que ahora lo que terminará con es algo que se ve como esto, que es un poco de bestia. Puede que no tenga mucho sentido a primera vista. No te preocupes, te voy a guiar por cada paso de esta función hasta que entiendas exactamente cómo funciona ahora. Usa casos aquí, obviamente, obviamente, como si estuviéramos hablando de agregar contadores en casos en los que los conteos de palabras son importantes o analizando campos de texto cosas como la copia de anuncios de búsqueda del blog de tuits en lo que pueda estar basado en la longitud. Por lo que un montón de diferentes lugares prácticos donde podrías usar este consejo. Ahora vamos a saltar a nuestro libro de trabajo pro tips y en realidad construimos un contador de palabras propio. De acuerdo, entonces estamos buscando la demo de palabras de conteo a una de nuestras pestañas verdes. Si estás en tu tabla de contenidos, adelante y pulsa link y saltaremos directamente a la pestaña. Y aquí mismo tenemos nuestros datos de cata de vinos. Estamos mirando el nombre del vino y la columna A. La variedad y la columna B consiguieron los nombres de catador individuales aquí en la columna C y Colin D es el que nos importa esto. Hay descripción escrita real desde ahí cata y lo que buscamos hacer es usar esas funciones de texto aquí en la columna e calcular el número de palabras en esas descripciones. Entonces lo que voy a hacer es una especie de descomponer esto poco a poco, y luego vamos a armar las piezas juntas para crear lo que necesitamos. Entonces tal vez la pieza individual más simple es la función Eliane o longitud, y mostrarte lo que eso hace. Vamos a tomar la longitud de la celda D dos con una simple es que sólo apuntan a una celda que contiene algo de texto prensa, entrar y devuelve 58 que no es el recuento de palabras. Es el recuento de personajes. Entonces mantén ese número en la parte de atrás. Tu mente. Esta descripción original contiene 58 caracteres en total. Ahora, la siguiente pieza que vamos a estar usando es una fórmula sustitutos de culto. Voy a sobrescribir esa función de longitud con sustituto. Siempre me cuesta deletrear este por alguna razón. Y la forma en que funciona esta función sustituta, ahí vamos. Podemos ver un poco mejor es vamos a apuntar nuevamente a nuestro campo de texto. Derecho D a. Y yo decía: ¿Qué en la cadena de texto original buscamos sustituir? ¿ Cuál es el texto original más antiguo? Esto podría ser un personaje individual. Podría ser una palabra entera, toda una cadena de palabras en este caso lo que realmente queremos sustituir es un espacio. Entonces voy a abrir unas comillas en un espacio y cerrarlo y luego comentar el siguiente argumento, que está bien, esa es la textura. ¿ Buscas ahora? ¿ A qué quieres sustituir ese espacio? Y en este caso, aquí es donde se pone un poco inteligente. Correcto es que en realidad no queremos reemplazar ese espacio por nada. Queremos deshacernos del espacio por completo y decirle a Excel. Eso es lo que queremos. Voy a escribir dos comillas consecutivas, básicamente, igual que lo hice, pero sin el espacio entre ellos y porque queremos reemplazar o sustituir cada instancia de esos espacios, no realmente se preocupan por este último argumento opcional. El número de instancia. Simplemente vamos a cerrar el centro de prensa entre paréntesis y lo que terminas con algo como esto. Los aromas de bayas y cerezas fueron sorprendentemente robustos y limpios. Es la cadena de texto que teníamos originalmente en D dos sin ninguno de los espacios. Entonces ahora si seguimos adelante y modificamos esa fórmula, haga clic justo después del signo igual. ¿ Y si tomamos la longitud de esta nueva cuerda modificada que creamos aprendices cerrados al final Presione enter 50. De acuerdo, entonces sabemos que la cuerda original era 58. Ahora sabemos que la versión de la cadena sin espacios es de 50. Al tomar la diferencia de esos números, esencialmente podemos contar el número de espacios que sacamos o eliminamos o sustituimos . Entonces es un Ziff. Utilizamos una función como contar si texto es igual a espacio. Pero desafortunadamente, eso no existe en excel, razón por la que estamos tomando este enfoque. Entonces sigamos adelante y en realidad tomemos de nuevo esa diferencia en la fórmula. Voy a modificar justo después del signo igual, tomamos la longitud de nuestra cadena original de Tu menos nuestra nueva longitud de esa versión sustituida de la cadena, y eso nos dará el número ocho. Vamos a arrastrar esto de nuevo aquí. Por lo que hemos quitado ocho espacios y podemos comprobar porque esta es una descripción bastante corta. De hecho, se retiraron 12345678 espacios. Pero no estamos del todo ahí porque si cuentas las palabras, en realidad son nueve palabras porque cada frase va a empezar y terminar con una palabra. Entonces siempre va a haber una palabra más que el número de espacios. Ajuste muy sencillo a realizar. Sólo tienes que añadir uno a la que nunca fórmula presiona enter. Y ahí tienes. Se obtiene un conteo de palabras de 9123456789 Y deberíamos estar bien ir simplemente haciendo doble clic, aplicándolo hacia abajo. Y ahí tienes. Va a tomar el conteo de palabras para cada una de estas descripciones. Puedes contarlos para confirmar si quieres, Pero confía en mí, están haciendo el trabajo. Ahora, la única otra pieza que podemos agregar si queremos tomar una medida más de, ah, ah, seguridad Angus, podrías llamarla para dar cuenta de casos donde, ya sabes, puede haber un espacio líder, ya sabes, accidentalmente así, lo que indicaría que hay 1/10 palabra cuando están en realidad no es para dar cuenta eso. Vamos a usar esa función de recorte, y todo lo que vamos a hacer es simplemente anidarla aquí. Entonces en lugar de la longitud de D dos, vamos a tomar la longitud de la versión recortada de D dos y misma lógica, en lugar de sustituir D a vamos a sustituir la versión recortada F D a aquí mismo también. Entonces solo estoy anidando eso dentro de la función aquí. Y creo que necesitamos una princesa más de cierre ahí. Y vamos a darle un tiro a eso y presionar enter. Ahí vamos. Entonces ahora se corrigió a nueve, que es lo que queremos aquí. Lo mismo si agregamos espacios trailing como este, ya sea un whoops o dos o tres o cuatro, no importa. Ese recorte va a dar cuenta de eso para nosotros y despojar a esos espacios que llevan o siguen. Entonces ahora podemos aplicar eso hacia abajo. Y mientras que un tenemos un contador de palabras totalmente funcional en Excel. 9. Desplazamientos de dependencia con INDIRECT: todo bien. Este siguiente pro tip se inspiró en una pregunta que me llegó hace un tiempo. En realidad lo han conseguido un par de veces desde entonces. ¿ Cuál es? ¿ Cómo creo un menú desplegable o validación de datos que dependa de otro? Entonces, en otras palabras, ¿cómo creo un desplegable primario y luego uno dependiente donde cambiará la lista en función de la selección del usuario? Y es una excelente pregunta y una gran oportunidad para usar una función llamada Indirecto. Por lo que esta es una punta de cuatro estrellas, definitivamente llegando un poco hacia el extremo avanzado del espectro. Pero déjame guiarte por cómo va a funcionar esto. Por lo que esencialmente podemos usar indirectas combinadas con reglas de validación de datos para crear esas listas desplegables que se actualizan en base a una selección de usuario. Entonces vamos a correr por tres pasos aquí en nuestra demo. Vamos a empezar por crear listas individuales que contengan todos los conjuntos de selecciones posibles . Entonces tenemos nuestro desplegable primario, que en este caso es la temporada ya sea de verano o de invierno. Y luego tenemos las listas que potencialmente podrían poblar el segundo desplegable o dependiente , que es la lista de deportes específicos de verano y deportes específicos de invierno. Entonces, una vez que hayas creado esas listas de valores únicos que capturan esas posibles selecciones , Paso dos es convertir esas en rangos con nombre real. Puede usar el administrador de nombres en su pestaña de fórmulas, o simplemente puede escribir el nombre a la derecha en el cuadro de nombre situado a la izquierda de la barra de fórmulas. Entonces si miras las dos opciones en el desplegable primario de verano o invierno, vas a querer igualar exactamente esa redacción. Entonces en lugar de deportes de verano o deportes de invierno, estamos usando esas palabras exactas verano e invierno. Y luego a partir de ahí, todo lo que vamos a hacer es crear una gota. downs son primarios y secundarios y luego configuran el origen de lista para el secundario. Bajar el dependiente. Desplegable para hacer referencia a esa primera celda como parte de una función indirecta. Voy a hablar exactamente de por qué y cómo hacemos esto cuando nos metamos en la demo, pero ese es el rápido resumen del enfoque que estamos a punto de tomar. Por lo que algunos casos de uso común aquí creando informes dinámicos con múltiples entradas de usuario con validación de datos o quizás más comúnmente, configurando tus listas desplegables para evitar que los usuarios seleccionen combinaciones de opciones no válidas . Entonces en esta demo en particular, eso es exactamente lo que estamos haciendo. No queremos que el usuario pueda seleccionar la temporada de verano y el hockey sobre hielo o el esquí alpino como el deporte, porque cualesquiera que sean las células que dependan de estas selecciones de validación de datos, obviamente solo se mostrarán como en blanco en esos casos. Por lo que basta hablar suficiente teoría. Vamos a enrollarnos las mangas, diríjase a su libro de trabajo pro tips y construyamos algunos downs dependientes. Muy bien, así que desde tu pestaña de tabla de contenidos, vas a buscar la demo de drop downs dependientes en nuestra fórmula verde Sección cuatro Star Tip. Adelante y haz clic en link Saltar a la ciudad y lo que verás aquí es que tenemos algunos datos de atletas olímpicos desglosados por temporadas y por deportes y Collins y estar con los nombres de los atletas y ven a ver y he añadido sólo una especie de plantilla en blanco o marcador de posición donde vamos a poblar nuestra información con el fin de crear estos. A desplegable vende la temporada que es nuestro desplegable primario, y el deporte que es dependiente desplegable. Es decir, queremos que este deporte caiga abajo para mostrar una lista de deportes de verano o de invierno, dependiendo de lo que se haya seleccionado aquí y venda F tres y luego he añadido una fórmula aquí . Número de deportistas. Esto es sólo la función de cuenta ifs que dice, Vale, vamos a contar las filas dadas estos dos criterios para que podamos ver el número de atletas para un determinado deporte y temporada para que empecemos El paso uno es simplemente poblar todos nuestros listas, ¿verdad? Vamos a empezar con temporada para un descenso primario sólo dos valores aquí. Es un simple es ese verano invierno. Sólo tienes que teclearlos manualmente. Probablemente es la forma más rápida y cómo conseguir los únicos deportes de verano y deportes de invierno pocas maneras de hacerlo. Podríamos utilizar la opción de filtro avanzado, que recuperó otro pro tip. O en este caso lo voy a hacer es filtrar los datos de mi fuente aquí. Sólo veamos el minuto de verano. Agarra Selby a y controla el desplazamiento Flecha hacia abajo para seleccionar toda la Columna de Control de Valores. Ver para copiar, volver arriba, seleccionar I a y controlar V a Pegar y luego en mi ciudad de datos, va a eliminar duplicados y continuó justo con esa selección actual y calma. Presiono quitar. Está bien. Y ahí tienes. Tenemos 36 deportes únicos de verano listados aquí, y vamos a seguir ese mismo proceso para los deportes de invierno. Por lo que en lugar de filtrar en filtro de verano en invierno, agarra el primer turno de control de valor. Flecha abajo Control. Ver para copiar. Voy a desplazarme hacia arriba, y vamos a necesitar sin filtrar esto para que podamos tener acceso a fila de nuevo. Despeja ese filtro. Selecciona J para controlar V para pegar datos duplicados eliminados y recuerda continuar solo con esa selección actual y pulsa para eliminar. Por lo que se iban a conseguir 15 deportes de invierno únicos. Por lo que ahí lo tienes. El paso uno está completo. Consiguió nuestra lista única de opciones. Ahora, el Paso dos es realmente dar a estas listas nombres significativos. Entonces lo que vamos a hacer es seleccionar todos nuestros deportes de verano primero todo el camino a través lucha libre. La forma más fácil de hacer esto no es pasar por fórmulas nombre definido, sino simplemente escribir el nombre aquí mismo en el cuadro de nombre situado a la izquierda de la barra de fórmulas. Y recuerda, quiero nombrarlo exactamente consistente con mi desplegable primario para que el verano entre igual cosa con el invierno. Selecciona esos deportes en este invierno entran y ese es el paso dos. El paso final es realmente armar nuestros downs aquí en F tres y F cinco. Por lo que nuestro 1er 1 son selección sazonada. Muy, muy sencillo. Vamos a ir a la validación de datos. Vamos a permitir una lista aquí y esa lista. Contiene son dos temporadas prensa. OK, así que ahora puedes seleccionar verano nuestro invierno. Ahí lo tienes. Ahora aquí está la llave. Todo depende de esto, correcto, correcto, Porque esta selección deportiva y F cinco cuando nos dirigimos a la validación de datos, vamos a permitir una lista como lo haríamos normalmente. Pero en lugar de seleccionar un rango que crearía una referencia fija para organizar, eso no cambiaría, No sería dinámico. Aquí es donde necesitamos usar esa función indirecta. Y lo que estamos tratando de hacer es que vamos a indirecto y de referencia a la celda F tres ahora antes de que yo golpee. OK, lo que estamos haciendo aquí es que le estamos diciendo a excel. Oye, mira la celda F tres. Vas a ver una palabra ya sea verano o invierno usando indirecto lo que le estamos diciendo XLs que hay más en ello No es solo una cadena de texto que tiene la palabra verano o invierno. Esas palabras son más significativas. Sus objetos de libro definidos reales, por lo que al usar indirecto, le decían a Excel Toe en realidad tratarlos como los rangos nombrados que son. Y cuando presionamos OK, ¿ seleccionó el verano? Echa un vistazo que desplegable contiene todos nuestros deportes de verano y ninguno de nuestros deportes de invierno , ¿ verdad? Y si ahora cambiamos esto al invierno, échale un vistazo. Esquí alpino Biatlón Bobsleigh Estos son nuestros deportes de invierno hasta el patinaje de velocidad , por lo que la función indirecta es capaz de reconocer esta palabra como rango sin nombre y poblar la lista de tarifas en consecuencia. Por lo que ahora echa un vistazo. Tenemos nuestro pequeño contador de atletas diciéndonos que había 645 esquiadores alpinos en los Juegos de Invierno . Podemos cambiarlo al verano y seleccionar uno de nuestros deportes de verano como béisbol 191 atletas. Por lo que ahí lo tienes. Este es uno de mis consejos favoritos en el curso, porque no es tan difícil, pero es extremadamente potente y una opción realmente genial cuando estás creando informes y dashboards dinámicos . Por lo que ahí tienes, creando validación de datos dependientes, desplegables usando la función indirecta 10. hiperenlace entre las hojas de trabajo: todo bien para este pro tip. Quiero mostrarte cómo crear enlaces dinámicos de libro usando la función de hipervínculo. Entonces lo que vamos a hacer aquí es que vamos a usar hipervínculo para conectar a los usuarios directamente a ubicaciones específicas de libros o hojas de trabajo. Ahora bien, si estás siguiendo junto con el curso, esto debería sonar muy, muy familiar porque eso es exactamente lo que he hecho en la pestaña Tabla de Contenidos del cuaderno pro tip, verás una fórmula como esto que es impulsado por esa función de hipervínculo, y que te apuntará directamente a vender uno para una pestaña dada. En este caso, van a la pestaña de opciones. Ahora, en teoría, esto es bastante sencillo, pero notarás que es una punta de cuatro estrellas. Por lo que está en el extremo avanzado de la báscula. Y la única razón por la que lo he etiquetado como un consejo avanzado es porque la sintaxis es extremadamente específica y bastante inflexible. Por lo que he incluido un ejemplo aquí. Hipervínculo hoja una celda a una con una etiqueta de nombre de enlace y note todos esos caracteres especiales ahí dentro. Las comillas comillas simples, el hash, el signo de exclamación. Todos esos símbolos son críticos. Y si no las tienes todas en el lugar exacto. Esta función no funcionará. Entonces realmente más que nada, conseguir que el hipervínculo funcione es más una cuestión de atención al detalle que cualquier otra cosa en una cosa a tener en cuenta. Aquí, puede escribir una referencia de hoja real como el ejemplo de esta viñeta. O puedes reemplazar eso por una referencia celular como lo he hecho en la hoja de tabla de contenidos . Cuando haces eso, hace que las cosas sean un poco más complicadas porque esencialmente necesitas invertir la sintaxis exacta usando Can Canton ocho funciones toe realmente enchufar ese signo de exclamación hash y comillas. Entonces los casos de uso aquí, por ejemplo, agregar herramientas para ayudar a los usuarios a navegar libros de trabajo más grandes y complejos como, por ejemplo, un libro de trabajo pro tip que tiene alrededor de 100 pestañas de información en él. Um, y dos. Este es uno que realmente hace bastante, creando informes que tienen enlaces a detalles adicionales o fuentes de datos. Entonces tal vez has proporcionado un reporte para un cliente. Eso es la vista de resumen de líneas superiores. Se puede agregar un enlace que puede ser controlado hasta una hoja mucho más detallada. Si el usuario quiere acceder a ese nivel de detalle Herramienta realmente útil en ese tipo de casos. Entonces sigamos adelante y construyamos nuestro propio hipervínculo en el libro de trabajo pro tip. Muy bien, entonces, apropiadamente, aquí estamos en nuestra hoja de tabla de contenidos, que simplemente sucede que incluye decenas y decenas de ejemplos de esta demo de hipervínculos que estamos a punto de practicar. En este caso, vamos en realidad a la demo de enlaces de libro dinámico aquí mismo en nuestros consejos de fórmula. Y lo adivinaste. Vamos a hipervínculo directo a esa pestaña. Y lo que tenemos aquí es como una pequeña versión mini de esa tabla de contenidos que vamos a practicar construyendo nosotros mismos. Entonces la idea va a ser poblar la columna H con enlaces basados en estos nombres de libros de trabajo o nombres de hojas de trabajo y columna B. Pero antes de intentar ponernos todo loco y avanzado justo desde el primer paso, intentemos simplemente escribir un tipo simple de más función de hipervínculo básica para una pestaña cercana. Por lo que contar palabras está cerca. Trabajemos con eso primero para que podamos intentarlo. escribir es igual a la ubicación del enlace de hipervínculo va a ser contando palabras. Venda una coma sobre el siguiente argumento, que es el nombre amistoso como enlace. Cerrar cotización, cerrar el paréntesis prensa enter. Las cosas se ven bien, ¿verdad? Si tan solo seleccionamos esa celda, estamos hiperligando a la hoja de palabras de conteo. Específicamente celular uno y lo hemos etiquetado enlace, y se ha creado un enlace para nosotros. El problema es que cuando hacemos clic en él, obtenemos esto no puede abrir el error de archivo especificado. Y básicamente lo que necesitamos decirle a Excel es Hey, Excel, no estamos intentando abrir un archivo separado. Estamos intentando simplemente acceder a una de nuestras hojas o pestañas dentro de este archivo. Entonces para decirle a Excel, eso es lo que queremos. Tenemos que añadir algunos personajes aquí, y justo después de que abra Perrin, vamos a añadir una comilla doble seguida de una marca de hash. Y luego al final, justo después de la referencia celular, uno vamos a cerrar otra cita doble y luego presionar enter y echa un vistazo a esto. Ahora, cuando hacemos clic en link boom, una hoja de palabras de un recuento, ha funcionado perfectamente. Por lo que agregar esa comilla para rodear la referencia de hoja así como esa marca hash le ha dicho a Excel que en realidad estamos tratando de navegar a una ubicación de hoja de cálculo dentro de este archivo. Así que volvamos a nuestra hoja de enlaces de libro dinámico, y podríamos seguir el mismo enfoque y hacerlo, ya sabes, 15 veces más para poblar el resto de estos enlaces. Pero obviamente eso es un poco lento. Es un poco manual, y si necesitabas poblar varios 100 enlaces, esa realmente no es una opción factible. Y como tenemos nuestros nombres de hoja de trabajo aquí en la columna B, deberíamos poder simplemente hacer referencia a esos nombres como parte de nuestra función de hipervínculo. Pero aquí está la captura. Simplemente tratamos de reemplazar esa hoja de referencia aquí con una referencia celular como B 11 impresionado. De acuerdo, nos vincularíamos justo directamente a esa ubicación de celda dentro de la hoja actual sobresale no leerla como el nombre de una pestaña. Entonces vamos a necesitar hacer es ponernos un poco inteligentes, un poco astutos con Can Canton ocho funciones para forzar la sintaxis que necesitamos. Así que presta atención puesta en tu por ALS popular. Vamos a ponernos bien y acercarnos aquí porque estamos a punto de ponernos bastante detallados con esta sintaxis y eliminar lo que teníamos, y vamos a empezar con un lienzo limpio paréntesis abierto Sígueme de cerca aquí. Cotización doble, marca hash. Cotización sencilla. Por lo que esto parece familiar hasta ahora. Aquí es donde se pone un poco raro. Vamos a añadir otra cotización doble un ampersand Eso es un signo de dólar. Ampersand en una referencia, la celda que queremos ser 11 y otra y por ciento cierran la cotización doble en otra sola cita un signo de exclamación, y luego venden referencia que queremos. Entonces si queremos dejar a la gente de su celda una que tenga sentido, consigue teclear una aquí y luego cerrar una comillas dobles más coma en nuestro nombre amistoso o etiqueta como enlace. comillas, cierre el paréntesis y pulse enter. Ahora voy a pasar el cursor sobre esto otra vez para que puedas pausar tu pantalla. Asegúrate de haber cortado todos esos personajes individuales en su lugar. Aquí está la cosa. Añadiría espacios para que lo hicieras un poco más legible, pero incluso eso rompería la función tan, muy frágil sintaxis. Y por eso se trata de una punta de cuatro estrellas. Pero ahora vamos a darle una oportunidad a eso. Clic derecho de enlace celda de pluma. A uno en la pestaña de palabras de conteo ahora aquí está el momento de la verdad, ¿no? Volvamos y simplemente agarramos esto y arrástrelo a unos cuantos más. Tan dependiente. Drop down. Haga clic en enlace. Ahí somos dependientes. Celda desplegable a uno de los enlaces de libro dinámico. Esa es la celda en la que nos encontramos actualmente. Celda de auge Un índice y coincide con uno funcionando perfectamente. Entonces ahora básicamente acabamos de automatizar el proceso de tener que crear todos esos enlaces uno por uno. Y ahora, como cualquier otra fórmula, puedes simplemente hacer clic y arrastrar y aplicarlo dedo del pie en cualquier otro lugar. Deje que las referencias cambien para impulsar a los usuarios en función de las hojas de libro y la columna B para que casi lo haga buena manera de usar la función de hipervínculo para conducir a los usuarios a ubicaciones específicas de la hoja de cálculo en Su archivo Excel. 11. Buscadas de: Quiero tomarme unos minutos y hablar de algo que llamo fuzzy match v lookups. Y específicamente voy a describir cómo encontrar rangos de valores en contraposición a valores de búsqueda exactos usando este tipo de búsquedas. Ahora partido difuso, que se conoce más formalmente como coincidencia aproximada, se puede usar para determinar dónde cae un valor de look up específico en un conjunto determinado de rangos. Entonces la idea aquí, el punto clave es que en lugar de tratar de encontrar la coincidencia exacta para nuestro valor de look up, lo que estamos haciendo en este caso es buscar la coincidencia más cercana que sea igual o menor que el valor de búsqueda hacia arriba. Y para que esto suceda, todo lo que hacemos es modificar ese último argumento y la función de búsqueda. Observe el rango buscar hacia arriba introduciendo un valor de true o el número uno, lo que significa coincidencia difusa o coincidencia aproximada. Ahora, como la mayoría de las cosas en Excel, esto se ilustra mejor con un ejemplo contextual de Rheal. Así que echa un vistazo a este conjunto de datos aquí. Tenemos un montón de productos que se venden en la columna B. Tenemos una cantidad de ventas o volumen de pedidos y columna C. Pero el retail Precio y D La idea es, estamos tratando de calcular el nivel de descuento en la columna e basado en las ventas, cantidad o volumen de pedido. Y esencialmente, la idea aquí es que a los pedidos de mayor volumen se les dará un descuento mayor. Por lo que para pedidos de 0 a 5, no habrá descuento aplicado. Del 6 al 10 sería un 5% de descuento, todo el camino hasta pedidos de más de 50 unidades, lo que ganaría un descuento del 20%. Entonces para que esta lógica funcione realmente, vamos a usar una función de búsqueda V, y vamos a decir: Busquemos el valor en columna. Ver la cantidad. Lo vamos a buscar en esta tabla de descuentos de pedidos. Específicamente las columnas H a través. Yo Queremos terminar la segunda columna y aquí está la clave. Estamos usando el tipo de look up de rango verdadero, que es coincidencia aproximada. Y cuando entramos en esa fórmula, lo que está pasando ahora es que X Ellis bajando de la parte superior de la columna H a la parte inferior otra vez, no solo buscando el partido exacto en este caso 25 sino el partido más cercano que es o igual a 25 o menor a 25. Por lo que en este caso, se detiene en 21. El índice de la columna nos dice que pasemos a la siguiente columna en la matriz de la tabla, y luego ese 15% de descuento. Ese es el valor que se tira hacia arriba en nuestra columna de descuentos. E. Entonces, lo que esencialmente hemos hecho no se corresponde exactamente con el valor de la cantidad, sino identificar en qué rango de valores cae esa cantidad en nuestra matriz de tablas o en una tabla de búsqueda . Sugerencia tan útil para tener en tu bolsillo trasero. Algo a tener en cuenta que aquí es bastante crítico. Para que esto funcione correctamente, sois agrupaciones deben definirse por el valor mínimo, que es el número mínimo de orden que, ves que están en la columna H de la tabla de búsqueda y esos mínimos de orden. Esos valores mínimos se tienen que ordenar ascendente de pequeño a grande porque recuerden cómo funciona un V look up. Se va a empezar por arriba. Va a ir celda por celda hasta que encuentre esa coincidencia más cercana. Entonces el orden es crítico aquí. Ahora se trata de un consejo de cuatro estrellas. Es relativamente avanzado. Si no tienes un buen mango en la vista tradicional, busca funciones aún cómo esto puede ser un poco confuso. Yo sí cubro búsquedas V así como H Lookups Index Match offset, etcétera en mis fórmulas y funciones. Por supuesto, vamos a cubrir más de los fundamentos en profundidad, por lo que definitivamente te anima a revisar eso. Ahora un par de casos de uso aquí podemos definir algo así como una tasa de comisión variable basada en un volumen de ventas de agente. O podemos hacer algo como lo estamos mostrando aquí calculando descuentos de lágrima basados en algo así como una cantidad de compra. Así que saltemos al cuaderno de trabajo pro tip y practiquemos escribir uno de thes partido difuso o vista aproximada. Funciones de búsqueda Muy bien, Así que de nuestra tabla de contenidos estaban en la sección de consejos de Fórmula. Busca la demo de búsquedas de partidos difusos y sigue adelante y presiona link para salir a esa pestaña verde . Y lo que tenemos aquí básicamente ordena registros con diferentes ideas de pedido en cada línea, y estamos viendo qué producto se vendió. Cantidad vendida, precio al por menor, que no vamos a usar en este ejemplo, Y la clave es la columna E. Esa columna de descuento que necesitamos poblar usando Avi función buscar arriba que haga referencia este pedido y tabla de descuentos aquí. Ahora. Algo a notar esta columna h es la que realmente nos importa, y esta es la que va a servir la entrada para la función. Tenga en cuenta que se define en función del valor mínimo en cada grupo o cubo, y se ordena ascendendo estas etiquetas aquí y la columna G. Estos no se utilizan en realidad en la función. Acabo de añadirlos para un poco de legibilidad, así que saltando para venderlo para pensar en cómo podríamos acercarnos a este problema ¿verdad? Hay algunas maneras en las que posiblemente podríamos abordar esto. Podrías usar un montón de declaraciones si anidadas y decir, Ya sabes, si la cantidad es menor que X y mayor que por qué, entonces descuento equivale al 10%. Eso está totalmente bien. En este caso, estarías anidando. Ya sabes, cinco condiciones diferentes en la misma fórmula. Se va a poner un poco largo, un poco desordenado, y tendrías que modificar cada una de esas condiciones en cualquier momento que quisieras cambiar las cosas. Entonces esa no es realmente la opción más eficiente aquí. ¿ Y si usamos una coincidencia exacta para ser mirar hacia arriba tipo correcto del enfoque estándar. Dijimos: Busquemos cantidad y asiento a Tratemos de encontrar esa cantidad. Estás en nuestra matriz de tablas cada uno a tres por seis, Presiona cuatro para bloquear eso. Y lo que queremos es el descuento, que vive en la segunda columna de esa matriz de tablas. Y luego, si hiciéramos nuestro tipo estándar de falso o cero para significar coincidencia exacta y lo cerramos, podríamos presionar, entrar y empezar a tipo de aplicar esto hacia abajo. Y lo que verás es que algunos campos pasan a poblarse adecuadamente. Pero esos airean sobre Lee los que eran exactamente iguales a los mínimos de orden 21 6 por ejemplo, para ejemplo, cambiar esa lógica de una coincidencia exacta a una coincidencia cercana e igual o menor a la coincidencia todo lo que necesitamos hacer. Ahora se ha cambiado ese cero a un one o a un true press enter, y ahora se ha convertido en un partido aproximado o difuso. Buscar arriba que se aplicará correctamente ya que se aplica a todas estas filas, por lo que es doble clic para aplicarlo todo el camino hacia abajo. Parece que tenemos alrededor de 100 muestras de pedido aquí y eso parece estar funcionando bien. Debilita la comprobación puntual. Cinco serían 0 25 sería 15%. Una cantidad de pedido de 75 sería de 20. Se ve genial. Por lo que ahí lo tienes. Ese es un gran ejemplo de un caso de uso práctico riel para las funciones de búsqueda aproximadas de match o fuzzy match V . 12. Selección al azar con OFFSET y RANDBETWEEN: Muy bien, chicos, estoy tan emocionado por este próximo consejo. Es uno de mis favoritos de todos los tiempos. Sé que digo eso mucho, pero esta vez hablo en serio. Definitivamente es el top dos o tres. Al menos vamos a hablar de cómo aleatorizar elementos de una lista usando offset y corrió entre funciones. Entonces, esencialmente, lo que vamos a hacer aquí es combinar offset con un generador de números aleatorios entero usando rand between para saltar esencialmente alrededor de dos rosas aleatorias dentro de una lista o rango de celdas. Ahora, tal vez te estés preguntando, ¿Por qué querría yo hacer eso? ¿ Cuál es el punto? Y les puedo asegurar que existen razones muy serias, muy aplicables para aprender estas herramientas, como construir su propio generador de nombre de banda. Aquí tenemos dos listas de palabras. Tenemos adjetivos hilarantes en la columna A y tenemos hilarantes ahora posee y columna B, y nuestra misión aquí es usar esos offset y corrió entre funciones para generar aleatoriamente el nombre de nuestra próxima banda. Ahora la belleza de esto es que porque corrió entre es una función volátil. Significa que nuestros nombres de banda se regenerarán con cualquier cambio de cuaderno de trabajo o si golpeamos ese F nueve calculo atajo. Por lo que ya he perdido una vergonzosa cantidad de tiempo. Simplemente refrescando esto, ya sabes, tarde un viernes por la noche, reíéndome a mí mismo. Pero me encantaría escuchar algunos de los nombres de bandas que se les ocurre. Creo que mis mejores ahora mismo son Ah, beligerante muñeco de nieve. Eso fue bastante bueno en gatitos caprichosos fue uno agradable. Y, por supuesto, los girasoles blancos calientes. Yo diría los que hay en mi top tres, Así que avísame si puedes superarlos. Deja un comentario. Me encanta escucharlo. Ahora, por supuesto, hay otros casos de uso común, como crear modelos de negocio de riel o escenarios donde se desea aleatorizar ciertos insumos . O, por supuesto, construyendo tu propia hilarante banda llamada Generator. Porque por qué no Así con eso suficiente hablando de ello, Saltemos a excel y realmente construyamos esta herramienta. Muy bien, así que una vez que hayas abierto tu libro de trabajo pro tips, vamos a estar buscando los elementos de la lista aleatoria seleccionando. Demo. Se trata de una pestaña verde en nuestra sección de consejos de fórmulas, o bien puedes dirigirte a tu tabla de contenidos y enlazar directamente a ella. Y aquí tenemos nuestro generador de nombres de banda para listas cada una con 30 elementos, y todo lo que necesitamos hacer es sacar ese elemento aleatorio de la lista un elemento aleatorio de la Lista dos y puede Canton ocho o combinarlos juntos en el nombre de nuestra banda. Ahora en realidad hay algunas formas de hacer esto. Podría usar funciones de índice que podría usar offset tanto sus herramientas perfectamente válidas para extraer valores de una ubicación específica dentro de un rango. En este caso, vamos a usar Offset. Adelante y empecemos a escribir esta función. Vamos a desfasar de una referencia dada o punto de partida en este caso son cabecera de lista ¿Una celda? A uno. Encerrémoslo con F cuatro ahora el número de filas que queremos mover esta referencia hacia abajo. Aquí es donde entra la ización aleatoria. No siempre queremos bajar una o dos o 10 filas. Queremos bajar un número aleatorio entre un cierto mínimo y máximo conocido como los valores inferiores o superiores. Entonces el número mínimo de filas que queremos mover es uno, porque eso nos llevaría un camino abajo al primer ítem de nuestra lista Acuáticos y el número más grande que queremos devolver el valor máximo es de 30 lo que nos llevaría todo el camino al fondo de nuestra lista. Ahora eso es todo lo que necesitamos hacer para esta iteración de la función. Podemos seguir adelante y cerrar la coma entre paréntesis sobre el siguiente argumento, que es columnas. Y como no queremos movernos de nuestro punto de partida a la izquierda o a la derecha, vamos a usar un cero aquí. Y debido a que no estamos buscando devolver un rango o matriz con una altura o anchura dada, podemos cerrar la función aquí mismo y presionar enter. Por lo que se sacó un artículo aleatorio en este caso, elegante fuera de la lista uno. Y en lugar de reinventar la rueda, sigamos adelante y solo copiemos esa fórmula. Pégala hacia adentro, también. Y todo lo que necesitamos hacer es cambiar ese punto de partida de celular a uno a uno, y deberíamos estar bien para ir. Tan feas y obras de arte son las dos palabras que fueron devueltas aleatoriamente. El último paso aquí es sólo para contaminarlos. Combínalos juntos en ese nombre de banda, así que digamos que equivale al espacio entre comillas ampersand para inventar Nate. Agarra esa primera palabra y cotización por ciento abierta en un espacio y luego finalmente agarrar esa segunda palabra presiona enter y ahí vamos. The Magnificent Bubbles es nuestro primer nombre de banda aleatorizada. Y recuerda, por el hecho de que corrió entre su volátil, puedes presionar el atajo F nueve o ir a fórmulas. Calcula ahora para ver esto recalcular una y otra vez así podríamos simplemente llamarlo un día ahí mismo si nuestras listas no vamos a crecer ni a encogernos en ningún momento. Pero si queremos conseguir un poco más flexible, lo que podemos hacer es en realidad reemplazar este número aleatorio duro, máximo de 30 y usar una función como Count A para hacer que esa referencia sea más dinámica. Y lo que eso hará es contar el número de ítems en esa lista, ya sea 30 o 40 o 50 o 10 y usó eso como el rango máximo para nuestro corrió entre función. Por lo que de nuevo, nos da un poco más de flexibilidad para que podamos sumar o eliminar de estas listas . Entonces voy a eliminar el número 30 tipo A y referenció toda la columna. Un bloqueo con F cuatro y solo atrapar aquí es porque tenemos un encabezado que no está en blanco también. Simplemente vamos a cerrar ese paréntesis. Resta uno de eso y deberíamos ser buenos para ir suprimidos. Entra ahí. Podemos seguir adelante y copiar esa misma fórmula, pegarla y dónde cambiar las referencias para que sean una y el conteo de columna B presione entrar , y estamos bien para ir. Así que ahí está nuestro práctico generador de nombres de banda aleatorizado que hemos construido aquí mismo en Excel. 13. Combinación de ÍNDICE y Combining: todo bien para este pro tip va a hablar de cómo combinar las funciones de índice y coincidencia para servir como un look up más flexible. Ahora, esta es una punta de fórmula de cuatro estrellas, definitivamente un poco más avanzada. Ahora bien, si no te sientes cómodo con los fundamentos de las funciones de búsqueda o índice y partido, ten en cuenta que puede que te costes mucho seguir aquí puede valer la pena volver atrás y tomar algunos de esos cursos fundacionales. Esencialmente, esto es lo que vamos a estar haciendo. Vamos a estar combinando índice y match para reemplazar el propósito de un V Look up, y eso va a ofrecer varios beneficios diferentes. Por una parte, no tendremos que hacerlo. Código duro. Un índice de columna. En otras palabras, Match va a identificar automáticamente nuestros encabezados de columna, encontrar la coincidencia correcta en la tabla de búsqueda y sacar valores de las columnas correctas. En segundo lugar están mirar hacia arriba. Los valores no necesitarán vivir en la primera columna de la matriz de tablas de la misma manera que lo hacen con el V. Look up. Puede vivir en cualquier parte de la matriz a la que estamos referenciando. Y luego tercero, nos dará más flexibilidad para situaciones complejas como tratar de encontrar la segunda o tercera instancia de un partido, que cubrirá en una protesta separada. Entonces tratemos de visualizar lo que está pasando aquí antes de saltar a nuestra demo. Considerado conjunto de datos como este obtuvo datos brutos y columnas A a C mirando ingresos por tienda por año. Y lo que estamos tratando de hacer es poblar las columnas D, E y F para traer la ciudad, estado y el país a partir de una mesa de búsqueda como esta. Obtuvo información de cada tienda i d. una etiqueta en dirección, una finca urbana y un país. Ahora a ver cómo todo esto está cableado. Pongamos atención a una celda en particular como D cuatro, por ejemplo, por ejemplo, que está tirando del nombre de la ciudad de Bremerton, y verás esta bastante compleja función de índice y coincidencia ahí mismo en la barra de Fórmula . Pero esencialmente, esto es lo que está pasando. Estamos diciendo a Excel que indexe todo el rango de valores potenciales de nuestra matriz de tabla a continuación y luego use funciones de coincidencia para aislar la fila específica que queremos y la tierra puntera de columna específica en un valor individual. Por lo que la primera función de coincidencia está buscando la tienda I D número en este caso, Número tres dentro de la columna I de nuestra matriz de tabla. Es encontrar esa tienda que d en la tercera fila debajo de los encabezados y decirle a Excel. Está bien, esta tercera fila es donde quiero vivir. Aquí es de donde vamos a sacar nuestros valores para la función de índice. Y entonces ocurre un proceso muy similar con la segunda función de coincidencia, que determina nuestro índice de columna. Excepto en ese caso, estamos buscando estos nombre de ciudad o el nombre de cabecera en D uno dentro de la Fila uno o la fila de encabezado de nuestra matriz de tablas. En este caso, encontramos esa cabecera de ciudad. Coincidimos con ese nombre de cabecera en la cuarta columna, y eso le dice a nuestra función de índice. Necesitamos un valor de la columna número cuatro. Por lo que juntas, esas dos funciones de coincidencia están diciendo al índice que se mueva hacia abajo a esa tercera fila y más a esa cuarta columna y devolviendo el resultado que se cruza, que en este caso es Bremerton. Y ese es el valor que se mete de nuevo en nuestra mesa arriba y para vender Defour tan definitivamente complicado de conseguir una pizca de al principio. Pero una combinación realmente poderosa de funciones para usar casos de uso tan comunes aquí, poblando muchas columnas de búsqueda sin tener que actualizar manualmente las fórmulas. Y hay números de índice de columna y para trabajar con escenarios más complejos. Como mencioné, por ejemplo, búsquedas, que pueden tener demasiados partidos y múltiples coincidencias en esa matriz de tablas. Por lo tanto, saltemos a Excel y practiquemos escribir una de estas funciones de coincidencia de índice. Muy bien, así que en un libro de trabajo pro tip, busca combinar Index y match Green Tab y nuestros consejos de fórmula pueden vincularse directamente a él. Y aquí tenemos nuestros datos de ingresos por tienda por año y Collins A a C y estas tres columnas que queremos poblar usando una función de look up en una pole city, estado y país para cada tienda I d. En base a esta tabla buscar aquí en columnas h a través de em. Entonces, por el bien de la demostración, empecemos con una simple fórmula V buscar primero y veamos cómo va eso. Entonces buscamos la tienda i d de B para arreglar solo la columna porque ahí es donde esa historia que d siempre vivirá. Lo vamos a buscar dentro de la matriz de tablas y anotar. No puedo empezar en la columna H porque esa no es mi mirada hacia arriba una columna de inicio. Voy a jalar yo a través de m 11 presiona F cuatro para arreglar esa referencia y para la columna Ciudad aquí. Deseo devolver el valor de la 1ª 2ª 3ª columna más. Ese es mi número de índice de columna y mi rango Buscar arriba es coincidencia exacta porque quiero coincidir con la tienda exacta I D. En esa columna, yo para que poble Acapulco, que luce bien, puedo aplicarlo hacia abajo, y todo está funcionando como se esperaba. Y ahora así es como la mayoría de la gente seguiría con esto. Tenemos que poblar a más columnas, estado y país. Podrías arrastrar esta fórmula, y debido a que hemos establecido correctamente nuestras referencias, todo lo que necesitamos hacer es cambiar el índice de columna de la tercera columna a la cuarta para el estado y de la tercera a la quinta para el país. Y ahí vamos. Podemos agarrar esos para bajar, y hemos poblado todas estas celdas correctamente en cuestión de minutos, usando una función tradicional be look up. Y no hay absolutamente nada malo en hacer eso. Gillick up es increíblemente poderoso. El único atrapamiento es que y si tuviéramos que poblar Ah, 100 columnas en lugar de solo tres. Ahora todo el repentino actualizar todos esos números de índice de columna de forma manual comienza a sentirse como un dolor de cabeza mucho más tedioso. Ahora una opción es reemplazar el índice de columna por un partido y V combinado buscar hacia arriba con match, que en este caso funcionaría muy bien. El único inconveniente, recuerden, es que todavía tendríamos que estar haciendo referencia a eso Buscar esa tienda I d en la primera columna de la matriz de tablas. Entonces sigamos adelante y eliminemos estas funciones de búsqueda V. Quiero mostrarles el enfoque de coincidencia de índice como alternativa. Entonces vamos a empezar con un índice. Así es siempre como empezamos porque se lo estamos contando a Excel. Queremos agarrar un valor de toda esta matriz, y ahora también podemos incluir la columna H, y arreglaremos esa referencia. Y ahora los dos únicos argumentos que nos importan aquí necesitamos decirle al índice de qué camino tirar y cuál calma tirar desde dentro de esa matriz. Y recuerda por la demo, vamos a usar una función de coincidencia en cada uno de esos casos. Entonces para identificar la fila adecuada, vamos a decir, emparejemos con la tienda. Yo d aquí y estar sintonizado arreglará la columna. ¿ Y dónde buscamos la tienda? Yo d. Mientras buscamos la tienda, me d en el array de mesa donde viven nuestras ideas de historia, que es tranquilo yo a las cuatro. Para arreglar eso en, usa un cero para la coincidencia exacta y luego cierra esa primera función de coincidencia. Ese es nuestro número de fila que vamos a alimentar a nuestro índice. Sólo otra pieza que necesitamos es el número de columna. Otra vez. Vamos a tomar un enfoque muy similar aquí. Es que esta vez vamos a igualar nuestro nombre de cabecera, que actualmente es City. Y vamos a arreglar sólo la fila esta vez porque nuestros encabezados siempre viven en la Fila uno. ¿ Y dónde buscamos igualar ese encabezado? Bueno, estamos buscando igualarlo en el hetero desde el arte. Buscar array H uno a M uno a cuatro. Para arreglar ese cero para el partido exacto. Ropa que coincida con función cerró toda la función de índice y presione enter. Por lo que ahí tienes, volvemos a tener Acapulco. Y ahora aquí está la belleza de la misma. Si tomamos Acapulco, arrástrelo. Boom. Hemos poblado estado y país automáticamente sin tener que tocar esos números de índice de columna . Y, como mencioné que d o que busco valor no necesita vivir en la primera columna en este caso que vivió en la segunda columna. Y eso significa que podemos hacer cosas como esta y decir, en lugar de país, dame la etiqueta de la tienda Y ahí tienes. Se va a tirar de las etiquetas correctas, pesar de que esas estaban a la izquierda de la tienda idea de que estamos mirando hacia arriba, cual es algo que sería imposible con la función de mirar hacia arriba en V. Por lo que ahí vas curso rápido de crash en combinar índice y match para crear funciones de búsqueda más flexibles . 14. Combinar los elementos entre las listas: Muy bien, Siguiente arriba, tengo un propina pro para ustedes chicos. Es una punta de fórmula de cinco estrellas, nivel experto. Estos son los tipos de consejos a los que querrás prestar atención si quieres convertirte en un verdadero usuario de fórmula en Excel. Y lo que voy a mostrarles es un enfoque para contar elementos coincidentes entre dos o más listas. Y para ello, vamos a combinar estos algunos productos con el recuento si función para devolver el número de valores coincidentes a través de esas listas ahora, en esta demo tendrá dos tablas diferentes. Organiza los productos que contienen, lista de productos A y B. Pueden tener diferentes números de ítems en ellos, pero sabemos que hay alguna superposición entre ellos, y el objetivo es usar una fórmula celular para identificar y contar que se superponen. Entonces lo que haremos es calcular aquí algunos artículos. El número de elementos de la lista A y la lista estar utilizando simples contar a fórmulas. Y luego aquí está la llave. Vamos a calcular el número real de artículos coincidentes. Es decir, la superposición entre la lista A y B. Entonces lo que pasa aquí es el conteo. Si la función está iterando o recorriendo la rosa en una de las listas, y está comprobando si existe una coincidencia en la otra lista. Por cada carretera que encuentra un conteo de partidos, si señas en una y por cada fila donde no existe un partido, cuenta un cero, y a partir de ahí, la función de algunos productos básicamente simplemente suma. El array resultante de valores y ese total nos da el número de ítems coincidentes. Entonces una nota rápida aquí en este caso fueron referenciando lista una primera, seguida de Lista B. Puedes intercambiar el orden no importa. Cualquiera de las listas se puede utilizar como base. Entonces un par de casos de uso común aquí como lo estamos mostrando aquí, simplemente calculando la superposición entre dos listas y excel, o confirmando que las listas son únicas para asegurarte de que estás evitando cualquier tipo de errores de doble conteo. Así que sigamos adelante y saltemos al libro de trabajo de Pro tips y en realidad practicamos escribir una de estas algunas funciones contadas de productos. Muy bien, así que en su libro de trabajo de protesta, estamos buscando el conteo de artículos coincidentes Demostración Green pestaña en nuestra sección de consejos de Fórmula, adelante y presione link y saltaremos a nuestras listas de productos aquí y desplazaremos a través. Ya puedes ver tenemos dos listas de productos. Ah, producto Una lista mide alrededor de 100 unidades de largo. I Lista B es alrededor de 89 o 90. De hecho, podemos usar las funciones del día de conteo solo para especie de devolver esas estadísticas descriptivas. Por lo que cuenta un llámalo un menos el encabezado. Nos va a dar 100 artículos y columna A y luego mismo proceso. Aquí, contar una columna B menos el encabezado. 89 artículos alistan carne de res. Ahora, una cosa que nos va a ayudar, se refiere a la legibilidad de Justus faras es dar a estas listas nombres propios. Entonces en lugar de hacer referencia a un dos a través de un 101 en una fórmula, me gustaría nombrar a esto algo así como la lista A para poder seleccionar el primer elemento mantener el control desplazamiento flecha hacia abajo para agarrar esa lista completa y en el cuadro de nombre aquí a la izquierda de la fórmula bar, voy a entrar en lista un dado a nombre propio. Haz lo mismo con esta flecha de desplazamiento Be control hacia abajo en esta lista única B. Eso sólo va a hacer que nuestras fórmulas sean un poco más fáciles de trabajar a medida que continuemos. Entonces antes de seguir adelante y empiezo a poblar una función aquí y vendo e cinco. El modo en que me gusta aprender estas técnicas más avanzadas es comenzar con las piezas componentes , entenderlas realmente, muy bien y luego ensamblarlas juntas en el resultado final. Entonces sigamos adelante y hagamos eso. Creo que eso va a ser de ayuda. Eso es correcto. Haga clic y simplemente agregue una columna aquí. Esto va a ser como un espacio de trabajo en blanco. Y si ignoramos por completo el componente algún producto por ahora y solo nos enfocamos en lo que está haciendo el recuento si la paz, intentemos escribir función contabilizada aquí mismo, y el primer rango va a ser la lista A. Y como tú empieza a escribir, verás ese llamado Rango Poblar Injusto Tab it in. Y así vamos a contar los elementos de la Lista A que coinciden con un criterio dado, que en este caso, empecemos con Selby, también. Ciérralo, presiona enter. Y ahora si arrastramos esa fórmula hacia abajo o doble clic para aplicarla, verás una serie de unos y ceros yendo hasta la parte inferior de la Lista A. ¿ Y lo que está pasando aquí en cada fila es que el recuento? Si la función está diciendo: Vale, Vale, ¿existe B dentro de la lista A. Sí o no? En caso afirmativo, devuelve uno si no devuelve un cero. Y esas referencias cambiaron para ser tres antes de ser cinco. Entonces esencialmente todo el mundo que ves aquí en la columna c refleja un partido. Y de hecho, si seleccionamos toda la columna C o el rango específico y miramos al sol, esa suma es 33 que en teoría debería estar diciéndonos el número total de coincidencias entre estas dos listas. Ahora una cosa a tener en cuenta es que no tengo que sólo hacer referencia a estar aquí. Puede cambiar esa referencia a toda la lista. Sé referencia y presiona enter y mira ¿Qué pasa cuando aplico eso hacia abajo? Nada cambia a medida que hago clic a través. No ves que la fórmula cambie en absoluto. Pero la referencia real que el valor que estamos tratando de igualar está cambiando fila por fila. Por lo que todavía obtenemos ese número. 33 partidos entre las dos listas y ahora una cosa más. Si cambié el orden de las listas aquí en la columna D. Mismo recuento de acercamiento. Si esta lista de tiempo es la primera, seguida de la lista A. Verás un patrón diferente de ceros y unos porque ahora estamos revisando los ítems . Enlistar A y mapearlos a la lista B, pero echa un vistazo. El total cuando seleccionas todo el rango sigue siendo 33 por lo que dije que puedes usar cualquiera de las listas como base. Por lo que vale la pena señalar que podrías parar ahí mismo y usar un puntera normal de alguna función tradicional . Agrega estos valores y obtén ese número de artículo coincidente de 33. Pero vamos a llevar las cosas un paso más allá y crear ese valor. Calcula ese valor sin tener que usar estas dos columnas de ayuda. En cambio, vamos a utilizar algún producto que esencialmente podría crear estas matrices dentro de la propia fórmula para calcular las mismas algunas y devolver ese valor de 33. Entonces muéstrale cómo se ve eso aquí mismo y vende F cinco. Vamos a empezar con el algún producto. Y la matriz que se estaban sumando en este caso es literalmente esa matriz que acabamos de crear cualquiera de las versiones, que se basaba fuera de la cuenta. Si es así, podemos escribir esa misma función que acabamos de hacer dentro del recuento de funciones de algunos productos. Si lista. A criterio es lista. Estar otra vez. Cualquiera de los dos pedidos. Ciérralo y presiona enter. Ahí vas. 33 artículos ahora en realidad podemos eliminar estas columnas de ayudante. No importa. No impactará en absoluto en la fórmula. Todavía obtenemos ese valor correcto de 33. Ahora, para aquellos de ustedes que no están familiarizados con sobresale una fórmula de estilo de carrera. Puede que te estés rascando un poco la cabeza aquí porque básicamente acabamos crear una fórmula que se comporta de una manera muy única e inusual a la derecha. Está creando su propia matriz de valores detrás de escena y luego procesando esa matriz para producir un solo valor. Ahora, cuando estaba aprendiendo por primera vez sobre las funciones de matriz, una cosa que me pareció muy útil fue usar la herramienta de fórmula de evaluación. Y lo que esto nos ayudará a hacer es simplemente descomponer las cosas y diagnosticar cómo están trabajando juntas todas estas piezas . Entonces vamos a empezar de adentro hacia afuera. Entonces esta es una función anidada, y vamos a evaluar esta pieza por pieza. Entonces la lista A, que es el argumento de rango del conteo. Si es un simple s el rango celular que estaban haciendo referencia a un dos a través de uno a uno y la lista B va a ser nuestra lista de criterios. Y entonces lo que deberíamos ver cuando evaluamos esta lista, ser argumento es una matriz real que contiene cada producto o cada elemento dentro de esa lista, que es exactamente lo que vemos aquí. 89 artículos individuales todos embalados en un rayo, y luego el siguiente componente subrayado es toda la función contada. Ahora que hemos evaluado ambos argumentos, podemos evaluar el conteo si mismo y echar un vistazo a esto. Esta carrera debería parecer muy familiar porque es exactamente el mismo Siris de 89 unos y ceros que generamos al escribir cuenta. Si la fórmula celular al aplicar a 89 veces o 2 89 subió, las diferencias estaban haciendo eso aquí como parte de una función de algún producto y esencialmente reemplazando esas 89 funciones por una. Por lo que empieza a darte una sensación de por qué estas funciones de aire un estilo son tan poderosas Ahora. Última pieza aquí es que algún producto en este caso, estamos usando algún producto de una manera bastante sencilla. En realidad ni siquiera estamos usando la parte del producto porque lo estamos alimentando solo una sola matriz todo lo que algunos productos van a hacer es sumar esos valores arriba. Yo sí tengo una propina. Vamos a hablar de ustedes niks y duplicados donde le agregamos un nivel de complejidad a esto . Pero en este caso, mantenerlo sencillo. Simplemente voy a alimentar el algún producto, esta matriz única, sumar los unos y ceros y evaluamos ese paso final. Ahí vas. Conseguimos nuestro 33 y eso debería hacerlo ahora. Para aquellos de ustedes que están familiarizados con las funciones de array, la pregunta que garantizo está en algunas de sus mentes es ¿por qué no usaron el cambio de control ? Ingresa cuando escribes este algún producto al que estás llamando función Honore. Y la respuesta es que algún producto es una de las pocas funciones en excel que se comporta como una función de matriz pero no requiere el enfoque de cambio de control enter. Entonces al igual que te mostré, simple es presionar Enter y se comporta igual que cualquier otra función. En este caso, realidad podría reemplazar el algún producto por un simple algunos y en realidad usé el enfoque de matriz e ingresar esto con control shift enter y obtendré la respuesta correcta porque nuevo, no estamos utilizando el componente de producto de algún producto. Por lo que cualquiera de los enfoques es totalmente válido. En realidad prefiero el algún enfoque de producto y control z para volver a ello sólo porque, ya sabes, no me gusta recordar que tengo que usar control shift enter. Prefiero tratarlo como una fórmula normal. Por lo que ahí lo tienes. Gran demo mostrando cómo puedes usar estas herramientas como algún producto y contar si hacer cosas realmente poderosas en excel. 15. Cuenta con duplicados con SUMPRODUCT: todo bien para este pro tip. Quiero hablar un poco de contar, duplicar o única rosa en una lista usando autofórmulas. Ahora es importante tener en cuenta que Excel sí ofrece una serie de formas diferentes de identificar o contar valores duplicados y únicos. Podrías usar filtros avanzados. Podría eliminar duplicados y contar el número de filas. Puedes enchufar tus datos en una tabla dinámica y mirar elementos distintos o incluso usar herramientas como Power Query que han incorporado funciones distintas de conteo. Pero hasta que haya una fórmula equivalente para contar distinto, hacer esto solo con fórmulas celulares puede ser engañosamente complicado. Pero un enfoque realmente fascinante que quiero compartir es usar algún producto y contar si contar esos valores únicos y luego restar del total para llegar al número de duplicados. Definitivamente no es la forma más sencilla de llegar al resultado final, pero fascinante sin embargo. Entonces lo que vamos a ver aquí es mi lista de abarrotes, que apenas crece constantemente. Olvidé qué artículos pongo ahí, y como resultado, termino comprando productos duplicados. Entonces lo que quiero hacer aquí es analizar esa lista y usar funciones celulares para calcular el número total de ítems surgieron en esa lista, el número de únicos y cualquier duplicado se elevó en los casos donde pude haber anotado el mismo producto dos veces. Entonces para hacer esto, vamos a usar algunas funciones diferentes para el total de artículos que iban a usar Día de la Cuenta. En este caso, hemos definido el rango celular antes a través de B 52 como abarrotes. Ahora, el complicado es este segundo artículo artículos únicos aquí y para calcular esos únicos Así que vamos a usar una combinación de algún producto y contar si voy a desglosar esto y mostrarte exactamente cómo funciona en nuestra demo. Entonces una vez que tengas esas dos piezas, podremos simplemente tomar la diferencia para llegar al número de duplicados. Entonces de nuevo, no mi enfoque recomendado, necesariamente como herramienta cotidiana, sino una gran manera de ver exactamente cómo funcionan realmente funciones como esta especialmente como algún producto . Así que diferentes casos de uso aquí. Se puede utilizar para calcular el número de productos únicos o distintos pedidos, por ejemplo, por ejemplo, o puede usar algo como esto para ayudar con el error, comprobando para evitar la inflación que pudiera ser causada por registros duplicados. Así que vamos a saltar a nuestro libro de Excel y ver si podemos escribir una de estas algunas funciones contadas de productos . Está bien, así que vamos a estar buscando están contando demo única y duplicados. Una pestaña verde dentro de la sección de consejos de fórmula puede vincularse directamente a ella desde la tabla de contenidos. Y aquí puedes ver mi lista de abarrotes yendo a cerca de Roe 52 y lo primero que voy a hacer antes de escribir una sola función es darle rango y nombre a esta lista de abarrotes. Haga clic en el primer elemento control de desplazamiento flecha hacia abajo para agarrar el último artículo y en el cuadro de nombre a la derecha a la izquierda de la barra de fórmulas, déle un nombre como abarrotes. Eso va a hacer mucho más fácil referir esta lista en nuestra fórmula. Nombres avanzando. Entonces primero lo primero. Número total de artículos. Eso es un simple es usar un conteo una función contar un consejo, celdas no vacías en un rango y arreglar va a ser esa lista de abarrotes que acabamos de crear Tab que al cerrarlo, presione enter. Entonces tengo 49 artículos en esta lista, 49 rosa en mi lista de abarrotes. Ahora la clave es tratar de averiguar cómo calcular, cuantificar el número de único específicamente y tal como te mostré con la punta de ítems coincidentes antes en el curso. Lo que voy a hacer aquí es descomponer este enfoque en sus partes más pequeñas, mayoría de los componentes. Entiende exactamente cómo funcionan las mecánicas de esas piezas al aire, y luego las vamos a volver a ensamblar juntas en nuestra fórmula final. Entonces sigamos adelante y sumamos dos nuevas columnas aquí en inserto a Collins a la izquierda de deep. Y esto será como nuestra caja de arena con la que podemos jugar aquí. Y vamos a empezar con la cuenta si la función y vamos a usar el recuento si para simplemente devolver el número de instancias de cada elemento dentro de toda la lista. Entonces para hacer eso, estamos en un conteo de tipos Si y nuestra gama es toda la lista de abarrotes y por ahora, por criterios, vamos a seleccionar el primer artículo de la lista. Cierra ese paréntesis. Presionar enter. Ahora. Lo que nos dice este número es el número de instancias de este producto fuera de toda la lista de abarrotes y justo fuera del bate, tenemos un duplicado aquí. Esto me dice que los melocotones enlatados de cinta verde aparecen dos veces en la lista. Entonces, a medida que arrastramos esto hacia abajo a toda la lista, vamos a ver el número de instancias asociadas a cada elemento de una sola línea en la lista de abarrotes. Entonces el jugo de arándano de Washington solo aparece una vez que la bebida de mango aparece dos veces De hecho, aquí está la segunda instancia de la bebida de mango de Washington, también con la etiqueta de a. Y luego, por alguna razón loca, parece que he entrado al queso de cuerda club cuatro veces diferentes. Creo que podría haber estado realmente hambriento de queso. Por lo que como puedes ver, esta nueva columna básicamente nos ha dicho el número de instancias de cada producto en nuestro listado. Ahora, un pequeño retoque que podemos hacer de su consejo útil aquí es que en realidad no tenemos que hacer referencia antes. Si volvimos a hacer referencia a toda la lista y aplicamos esto hacia abajo. A pesar de que no vemos que la referencia cambie fila por fila, está logrando exactamente lo mismo. Es iterando, elemento por elemento, contando el número de instancias. Ahora eso es genial. Es útil tener el número de instancias, pero No nos llega hasta el final como siguiente paso. Lo que necesitamos hacer, en realidad es crear un nuevo cálculo aquí y tomar uno y dividirlo por ese valor que acabamos de crear usando esa función contada, y te mostraré exactamente por qué estamos haciendo esto en tan solo un segundo. Vamos a aplicarlo todo el camino hasta la lista completa, y miro. Lo que está pasando cuando puedes empezar a notar es que esencialmente estamos asignando un valor de uno a cada elemento único de la lista cada producto único. Eso significa que los artículos que solo aparecen una vez como Jeffers, avena o mapa de Queen City obtendremos un valor de uno aquí, ya que uno dividido por uno es igual a uno. Pero la captura es que para los artículos que aparecen más de una vez, esencialmente estamos tomando ese crédito de uno por el producto y difundiéndolo Entre el número de instancias. Por lo que la bebida de mango de Washington aparece dos veces, y cada una de esas instancias obtiene medio crédito o 0.5. De esa manera, cuando resumiremos esta columna, no estaremos contando dos veces ningún producto que aparezca más de una vez. De hecho, para los casos en los que tenemos dos o cuatro instancias. Esas fracciones por completo nunca igualarán a más de una. Entonces si seleccionamos toda la lista podemos ver hacia abajo, aquí hay algunos es 44. Ese es el número dorado. Ese es nuestro número de artículos únicos reales o rosa en nuestra lista de comestibles, por lo que podríamos simplemente tomar la suma y enchufar que alguna función aquí dentro y se haga con ella. Pero al igual que nuestros otros ejemplos, no queremos tener que depender de estas columnas de ayuda aquí. Montemos esta función contada en algún producto, que esencialmente va a crear toda esta matriz y resumirla todo dentro de la función misma. Va a actuar como una fórmula matricial. Entonces sigamos adelante y tecleemos igual a grupos, algún producto. Y todo lo que vamos a hacer es escribir esas mismas funciones que acabamos de utilizar en las columnas D y E, pero anidarlas dentro de esta fórmula. Por lo que algún producto uno dividido por conteo. Si abarrotes comas abarrotes, vamos a cerrar a paréntesis y presionar enter. Y ahí lo tienes, 44 artículos únicos o rosa en nuestra lista. Y ahora que conocemos el total y lo único debilita simplemente toma la diferencia y llega al número de rosa duplicada. Entonces tenemos el número cinco aquí. Eso significa que teníamos una fila duplicada para melocotones enlatados, un duplicado para bebida de mango, y esos tres duplicados El queso String también subió en nuestra lista, sumando para pelear. Entonces ahora puedo borrar D y E, y aún así obtendremos nuestro valor adecuado. Y ahí lo tienes. Entonces ciertamente no es la forma más fácil o rápida de llegar a ese número, sino una oportunidad realmente buena para practicar algunas de estas funciones más avanzadas, como algún producto. 16. Búsqueda de muchas opciones de Lookups muchas: todo bien. Este siguiente consejo es para aquellos de ustedes que están tratando de sumergirse profundamente en territorio de nivel experto con sus fórmulas y funciones de excel, vamos a hablar de cómo usar el recuento si, junto con el índice y el match, para ayudar a manejar muchos demasiados lookups y excel. Ahora, en términos generales, Excel está diseñado para trabajar con 1 a 1 o de una a muchas relaciones. En otras palabras, si estás tratando de conectar o unir dos tablas juntas usando las funciones buscar una referencia , al menos una de esas dos tablas solo debería tener una instancia de tu valor de búsqueda o clave primaria. Si ese no es el caso, ambas tablas tienen múltiples instancias de ese valor de búsqueda. Entonces tienes lo que se llama una relación de muchos a muchos. Entonces, para darte un ejemplo de cómo podría parecer eso, un vistazo a este conjunto de datos. Estamos viendo datos de ingresos aquí, rastreados a un pedido i D. En la columna A y una tienda i D y la columna B. Y el objetivo es poblar esta columna de direcciones en base a una tabla de búsqueda como la que ves a continuación. Ahora, si miras de cerca notarás que dos de esas tiendas I. D. Número cinco e I. D Número ocho, tienen múltiples instancias en esa mesa de búsqueda. Y debido a que tenemos múltiples pedidos rastreados de cada una de esas tiendas, se quedaron atrapados con una relación de muchos a muchos aquí. Y para darle una sensación de por qué este es un problema común. Piensa en lo que pasaría si utilizáramos una función tradicional de búsqueda V o de coincidencia de índice solo en esta relación, le dices a Excel toe look up Story D Número cinco. Se buscaría la columna G y la mesa de búsqueda, y se detendría a la primera instancia, devolvió 59 22 cancha LaSalle y no tienen idea de que alguna vez hubo otra instancia de tienda I. D. Número cinco en esa mesa. Entonces la buena noticia es que hay herramientas que podemos usar para manejar situaciones como esta, y en el enfoque que estamos a punto de demostrar, vamos a usar el conteo. Si las funciones de índice y coincidencia del dedo del pie realmente devuelven valores de búsqueda posterior hacia arriba coincidencias. Entonces en este caso en particular, la dirección de la segunda instancia de la tienda número cinco o de la tienda número ocho, así que para darte una idea de cómo va a funcionar esto. Vamos a afinar rápidamente en una de estas células. Celular E cuatro, que es 67 64 Glen Road, La dirección de la tienda número ocho. Y esa es la segunda dirección en la mesa de búsqueda. Ahora la clave para conseguir esa segunda dirección es este nuevo asiento de columna. Una serie de instancias y esa columna está impulsada por cuenta. Si la función. Eso es básicamente contar la tienda I D en cada fila y comprobar cuántas instancias de eso d existen en la columna g de nuestra tabla de búsqueda. Entonces para la mayoría de esas tiendas I DS, el número de instancias y la columna C es una. Pero para las ideas cinco y ocho, esa función contada regresa ya que sus dos instancias de cada uno de esos I DS y lo que esa pieza de información nos permite hacer es seguir escribiendo una función de índice y coincidencia como lo haríamos normalmente. Pero en lugar de detenerme en el primer partido de la tienda, yo D pude saltar hacia abajo a subsecuente rosa en casos donde hay múltiples instancias de eso, d en el look up. Entonces en este caso estamos igualando la Tienda I D. Número ocho, saltando hacia abajo una fila adicional y tirando de la dirección resultante. 67 64 Glen Road. Ahora nota muy importante aquí. Tienes que entender por qué hay múltiples instancias de tu clave, y es muy importante confirmar que son, de hecho, registros válidos. Porque muchas veces cuando la gente se encuentra con este tema de relación de muchos a muchos, es indicativo de un problema más profundo con su base de datos, en cuyo caso las soluciones de fórmulas como esta realmente no ayudarán a resolver el problema raíz. Dicho esto, hay algunos casos de uso común que sí crean situaciones como esta. Un ejemplo es si estás realizando un seguimiento de los cambios históricos dentro de una tabla de búsqueda existente. Entonces tal vez tengas un producto cuyo precio cambió con el tiempo, en cuyo caso tienes el mismo producto i d. Pero uno de sus atributos, en este caso el precio al por menor ha cambiado. O tal vez en el caso que estamos viendo aquí, tienda número cinco en la tienda número ocho ubicaciones movidas, pero queremos rastrearla como la misma entidad. Ambas son explicaciones potencialmente factibles de por qué podríamos encontrarnos con una relación como la que aquí vemos. Entonces con eso, saltemos a Excel. Abre nuestro cuaderno de trabajo pro tip y veamos qué podemos hacer con una de estas relaciones de muchas a muchas . Muy bien, así que desde nuestra tabla de contenidos, vamos a dirigirnos a tu sección de consejos de fórmula verde. Busca la demo de muchas a muchas búsquedas. Adelante y enlaza directamente a esa hoja. Y aquí tenemos rastreo de ingresos basado en el pedido I DS mapa a una tienda dada. Yo d aquí en la columna B y como hablamos, vamos a tratar de poblar esta columna de direcciones usando la mesa de búsqueda aquí en Collins, F y G. Pero recuerda, la captura es que tenemos a tiendas aquí cinco y ocho con duplicado rosa en la mesa lookup. Entonces si fuéramos a escribir una función tradicional de coincidencia de índice, por ejemplo, vamos a indexar que buscar rango justo desde F G, bloquearlo en el número de fila al que queremos trasladarnos es donde fuéramos capaces de igualar esa tienda I D y golpeó a y por hábito. Voy a bloquear la columna B y donde tratamos de igualar esa llaga? Yo D. Bueno, estamos tratando de igualarlo en la lista de tienda I ds en columna f Bloquearlo, vengan por encima partido exacto y la ropa que coincide función apagado. Entonces estamos indexando ese rango. Nos estamos moviendo hacia abajo a la fila en la que coincidimos con la tienda i D. Y la columna que queremos en este caso está llamando al número dos. Y eso debería hacerlo para la función de coincidencia de índice Presione. Entra y aplíquelo hacia abajo. Y al igual que esperarías, estamos obteniendo los valores correctos para las tiendas. 1234679 y 10. Empezar a capitanes están lejos. Tienda seis. Cañón Mitchell. Aquí la captura Tienda ocho Buenavista, que es la primera instancia. Almacena cinco LaSalle nuevamente primera instancia. Por lo que por su cuenta esta función de coincidencia de índice igual con la V. Look up. Tener débil en esa ruta no tiene ni idea de que estos rosa incluso existen en la mesa de mirar arriba. Por lo que necesitamos construir otra pieza de información que nos ayude a dar cuenta de eso. Y ahí es donde entra en juego esa columna de instancia. Entonces lo voy a agregar. Llámalo aquí entre B y D. Llámalo el número de instancias y Es una simple es cuenta si función y el rango en que estaban contando es la tienda I d rango desde la mirada hacia arriba. Eso lo bloquearemos en los criterios. Lo que estamos tratando de contar dentro de ese rango es nuestra tienda I d En este caso, Selby, también. Ciérrelo, ingresó y arrástrelo hacia abajo. Y ahí vamos. Por lo que ahora efectivamente hemos marcado o etiquetado la tienda I DS que aparecen más de una vez. Todo aquí dentro es uno, con excepción de nuestros octavos y son cincos, que es exactamente lo que esperaríamos ver aquí. Y ahora que tenemos esta pieza adicional de información, podemos utilizar estos datos e integrarlos en nuestra función de coincidencia de índice para saltar hacia abajo una fila extra, pero solo para aquellos de la tienda I DS Onley para cinco y ocho. Entonces, ¿cómo podemos hacer eso? Bueno, miramos su función de índice. El argumento de número de fila, que es donde vive nuestra función de coincidencia, es lo que está diciendo a excel de qué camino sacó datos. Por lo que necesitamos subir algún valor después de esta función de coincidencia para saltar hacia abajo una Fila cuatro tiendas adicionales cinco y ocho, y tendremos que hacerlo usando esta nueva columna de número de instancias que acabamos de crear ahora en la columna C. Pero si sólo tuviéramos que sumar el valor de la columna C, echar un vistazo a lo que eso va a hacer. En realidad va a saltar una fila extra por cada tienda y dos rosas extra para las tiendas cinco y ocho. Y si fuéramos a hacer eso, como puedes ver, todo se echa fuera. Entonces tienda a está regresando ahora. Ramsey Circle, que es uno fuera de tienda 10 está devolviendo un error de ref porque lo está empujando fuera del rango de look up. Y luego por cinco. Seguimos recibiendo las respuestas equivocadas porque ahora estamos encontrando la primera instancia de cinco y saltando a más filas hacia abajo, que es más de lo que necesitamos. Entonces con un ajuste rápido aquí, todo lo que necesitamos hacer es en lugar de agregar C dos, vamos a agregar C dos menos uno. Cierra el paréntesis y pulsa enter y ahora echa un vistazo a esto. Perfecto. Por lo que ahora todos los 1234679 10 de nuestra otra tienda están devolviendo los valores adecuados. Y ahora mira esta tienda. Cinco para 90 Risdon Road, que es la última o la segunda instancia de I D número cinco en ese look up número ocho Glen Road Exactamente lo que buscamos para que puedas seguir personalizando esto tanto como quieras. Se puede agregar lógica adicional. Puedes anidar funciones adicionales, pero espero que esto te dé una idea de cómo algunas de estas herramientas, como contar ifs e índice y coincidencia, pueden usarse para acomodar casos en los que podrías tener de muchas a muchas relaciones como esta .