Transcripciones
1. Introducción de la introducción:: Muy bien, bienvenido a la Sección nueve. Estamos en el homestretch ahora, y esta sección va a estar hablando de una nueva raza de fórmula llamada la fórmula array . Y para aquellos de ustedes que nunca han trabajado con las fórmulas de rayos, operan un poco diferente a las fórmulas no arias a las que tienen una sintaxis y un uso ligeramente inusual que lleva a algunos acostumbrarse. Por lo que ahí hay un poco de curva de aprendizaje. Pero una vez que te pongas cómodo y aprendas a usarlas, empezarás a ver que pueden abrir la puerta para hacer algunas cosas que realmente no puedes hacer otra manera. Entonces vamos a empezar la sección con las reglas de las fórmulas de matriz, cómo usarlas y cuáles son algunos de los pros y contras comunes de las fórmulas de matriz. Hablaremos de constantes de matriz vertical, horizontal y bidimensional. Y luego cómo usar esas constantes fórmulas cómo usar nombrado un raise. Entonces entraremos en un montón de demos y sobresalir. Comience con transpuesto. Hablaré de vincular datos entre hojas. Te mostraré cómo usar el enfoque no Array versus el enfoque de matriz. Comparar y contrastar eso con. Hablaremos de cómo devolver los X valores más grandes de organizar en un solo paso. Usaremos la función Len que cuenta caracteres a través de un rango de celdas y por último pero no menos importante, creará un max personalizado si la función así como de costumbre a los archivos para descargar. Excel para analistas, fórmulas de
matriz que pdf y una Sección nueve o un archivo de Excel fórmulas. Así que adelante y abre esos y vamos a darle un golpe.
2. Reglas de las funciones de la Array: De acuerdo,
entonces, ¿qué diablos tienen razón? Funciones? Básicamente, están diseñados para realizar múltiples cálculos en uno o más elementos de una matriz. Lo que las hace muy diferentes de las funciones celulares normales es que las funciones de un rayo
pueden tomar la forma de una sola fórmula celular, en cuyo caso existen dentro de una célula o una fórmula multi celular, en cuyo caso es la misma fórmula aplicado a través de una serie de celdas. Nota muy importante es que debes presionar control,
desplazar, desplazar, ingresar dedo del pie, ingresar, editar o eliminar cualquier fórmula de matriz. Si no presionas control, turno enter. Si solo presionas enter como estás acostumbrado, estos no funcionarán por lo que vale la pena repetirlo de nuevo. Presione turno de control. Introduzca cada vez que introduzca, edite o elimine una fórmula de matriz. Cuando hagas eso, verás que Excel automáticamente entre paréntesis alrededor de tu fórmula, comenzando antes del signo igual y yendo hasta el final de tu fórmula, lo que indica que la función se aplica a una matriz. Entonces echemos un vistazo a un ejemplo. Si fuera a seleccionar el rango de D a tres d cinco aquí y escribir la fórmula sea a
través ser cinco Veces C dos a C cinco y golpear Enter. La fórmula sólo se aplicará para vender D a Si selecciono ese mismo tipo de rango la
misma fórmula exacta y golpeo control turno enter. Se me ha creado fórmula Honore y se aplica a todas las celdas del rango que había destacado. Entonces como puedes ver, los resultados se poblan a través de las cuatro filas dentro de mi rayo. Por lo que las reglas de las funciones de matriz lamentablemente, hay bastantes en usted debe obedecer todas ellas con el fin de usarlas correctamente. El primero, como ya he mencionado varias veces ahora es que debes presionar control shift enter como a veces abreviado como C S E. Algunas personas lo llaman funciones de rayo. Funciones de CSE por esa misma razón para editar o ingresar a Andre Fórmula Regla dos Para funciones de
matriz de celdas múltiples , debe
seleccionar el rango de celdas antes de ingresar a la fórmula. Entonces el paso uno es entender a qué rango de celdas quieres que se aplique tu fórmula y luego el paso dos es realmente escribir la fórmula. Regla número tres Esta es una importante. No se puede cambiar el contenido de ninguna celda individual que forma parte de una fórmula de matriz. Es un poco estricto y que en cuanto escribes en una fórmula de rayos que es específica de una matriz no puedes insertar Rose. No se pueden, uh, eliminar valores. No se pueden mover celdas si fueran parte de esa matriz. Regla cuatro. Se puede mover o eliminar una fórmula de matriz completa, pero no una parte de ella. Tan a menudo veces. Si eres array necesita cambiar, tienes que eliminar toda tu fórmula de matriz y reconstruir desde cero y luego durar pero no menos importante el número cinco. No se pueden insertar celdas en blanco o eliminar celdas de una fórmula de matriz de celdas múltiples, por lo que la línea inferior presiona control shift enter. Seleccione el rango de celdas antes de introducir la fórmula. Y asegúrate de que tu matriz no va a cambiar una vez que hayas escrito la fórmula. Entonces esas son las reglas de las funciones de un rayo. En pocas palabras.
3. Pros y desventajas de las funciones de la Pros: está bien, por lo que puede que ya empiezo. El sentido de que funciona Ray, aunque sin duda pueden ser increíblemente poderosos, también
puede ser un zumbido total con el que trabajar, sobre todo como tu primera familiarizarte con ellos. Ellos sí tienden a ser un poco más rígidos y estrictos que las funciones no array, por lo que la curva de aprendizaje es un poco más pronunciada aquí. todas
formas, echemos un vistazo a algunos de los pros y contras de comentarios en el lado pro. En primer lugar, son realmente grandes herramientas para condensar múltiples cálculos en una sola fórmula, lo que tiene el beneficio añadido de reducir a menudo el tamaño de los archivos. En segundo lugar
, puede realizar algunas funciones complejas que las fórmulas no array no pueden. Te voy a guiar por algunos ejemplos que demuestran que, y luego tercero, ayudaron a reducir el riesgo de error humano, como borrar accidentalmente partes de un aumento o faltar fórmulas de mecanografía en el lado de la estafa. Podría ser muy difícil modificar o eliminar las fórmulas de matriz existentes. Podría ser un poco torpe trabajar con. Tienes una visibilidad limitada de la función de fórmulas, especialmente para usuarios que no están familiarizados con los rayos. Por lo que sólo hace un poco más complicado, toa que manera de solucionar problemas e identificar cómo pueden estar funcionando las combinaciones de funciones. En tercer lugar, elimina la opción de modificar las celdas contenidas dentro de un raise. Entonces si tienes un conjunto de datos que tiende a cambiar bastante, son unas funciones pueden ser muy difíciles de trabajar con. Y por último pero no menos importante, en realidad
pueden reducir la velocidad de procesamiento si se usan múltiples funciones de matriz tan personalmente en el trabajo que
hago, me encuentro trabajando con un dato muy dinámico que está cambiando y actualizando constantemente, en cuyo caso, hay muchos casos que son funciones de rayos no son el adecuado para mí. Pero dicho eso, ciertamente
hay oportunidades donde he usado funciones de array porque no hay otra opción que pudiera haber logrado lo mismo. Entonces, al final del día, realmente
es solo una función de qué datos estás trabajando y qué estás tratando de
lograr a la hora de usar funciones de array versus funciones no de array. Entonces con eso, hablemos de los diferentes tipos de tasas y nombrados raise, y luego saltaremos justo en excel
4. Constantes de Array brazos verticales, horizontales y 2-Dimensional de la familia de la 2-Dimensiona: De acuerdo, Así que las constantes de rayo son un aumento que se crean introduciendo manualmente una lista de elementos directamente en la barra de fórmulas y rodeando esa lista con corchetes. Por lo que hay tres tipos. Hay horizontal o una constante, que creando array contenido dentro de una sola cuerda, esos aire delimitado por comas. Entonces lo que haces es seleccionar la matriz horizontal de celdas que quieres crear en este caso , un 1 31 Si queríamos que esa matriz se poblara con los números 123 y cuatro éramos tipo igual. Abrir corchete una coma, dos comas, tres comas cuatro. Cierra el soporte y luego toca turno controlado Enter. Es importante tener en cuenta que hay que escribir manualmente este conjunto de corchetes, y cuando presiona control shift, enter. A continuación, Excel agregará un segundo par de corchetes alrededor de todo el asunto. Entonces esa es la matriz horizontal o una matriz vertical constante. Constance funciona de la misma manera que solo seleccionas tu matriz vertical de celdas, y luego delimitas tus valores por semi puntos en lugar de comas. Entonces si fuéramos a poblar una matriz de una a a cuatro con los valores 1234 éramos tipo igual corchete abierto un punto y coma a punto y coma, tres punto y coma cuatro. Cierre el soporte y,
a continuación, pulse el cambio de control. Entrar Y por último pero no menos importante, puede combinar constantes de matriz horizontal y vertical para crear constantes de velocidad
bidimensionales . Entonces, para escribir una constante de matriz bidimensional para crear una desde cero, básicamente
ingresas tus valores de la misma manera que leerías un libro comenzando por la parte superior izquierda cada fila moviéndose de izquierda a derecha. Por lo que se enumeran los valores secuenciales de las columnas de ayuda B a C a D, separados por comas y luego se usa un punto y coma para indicar un salto hacia abajo a la siguiente fila. Entonces en este caso, tenemos los valores de uno a ocho y desde 123 y cuatro apagado, todos en la misma fila. Esos aire separados con comas y luego el semi colon indica que los cinco se está iniciando en la segunda fila atrás y columna un así nuevamente presione turno de control. Entra y tu matriz se poblará. Entonces vamos a rebotar para sobresalir. Sólo voy a abrir trabajo en blanco. Simplemente consigue algo de práctica trabajando con esto. Así que vamos a seleccionar un 1 31 entrar en la barra de fórmulas y tomaremos igual corchete abierto una coma , dos comas tres comunes para cerrar el corchete. Y si solo presiono enter ahora, verás que el valor se pobla y venderás uno. El número uno pero B uno C uno y d uno no se ven afectados. El tema es que no presioné turno de control, entra. Y cuando hago eso ahora puedes ver que se agrega el segundo par de corchetes alrededor de mi función, y se llena la matriz que había seleccionado con los valores que tenía entrada aquí. Entonces así es como crear una matriz horizontal básica. Echemos un vistazo a cómo editar y eliminar ese arreglo. Entonces el reto es, sabes, ahora que tengo mi función de matriz escrita, si quiero cambiar el Ford de cinco, no
puedo simplemente cambiar el valor de celda porque es parte de esta matriz. Por lo que presiona cancelar. Um, igual manera, no
puedo simplemente entrar en la barra de fórmulas y cambiarla ahí por la misma razón. Entonces lo que necesito hacer es básicamente seleccionar toda la matriz, entrar en la fórmula, cambiar el Ford de cinco y luego presionar control shift enter de nuevo para actualizar la matriz cuando se trata de eliminar la matriz. Un caso similar. No puedo simplemente eliminar células individuales. Lo que tiene que hacer es seleccionar toda la matriz y eliminar los valores de esa manera, en cuyo caso las funciones desaparecieron. O voy a deshacer eso. Puede seleccionar la matriz, entrar en la barra de fórmulas, eliminar la fórmula. Y ahora aquí está la captura. A pesar de que no hay fórmula escrita ahí por prensa enter, no pasa nada. Para que pueda seguir pegando. Entra una y otra vez, y no va a pasar nada después de presionar control, shift, enter incluso cuando haya borrado la función para borrar completamente la matriz que acabo de crear. Entonces de nuevo, la curva de aprendizaje es un poco empinada. Una vez que comienzas a jugar con
esto, empieza a volverse un poco más natural, pero de nuevo, definitivamente mucho más estrictas y rígidas que las fórmulas celulares tradicionales. Entonces vamos a crear una matriz vertical. Seleccionas un 134 Ir a la barra de fórmulas equivale a corchete abierto. Uno. Punto y coma a punto y coma. Tres semi cono. Cuatro. ¿ Recuerdas los semi colones, o qué? Indicar o delimitar diferentes valores que caen en rosa individual. Así que cierra el cambio de control de corchete Entrar y ahí vas. Está poblado Mireille en celdas un 134 Así que otra vez para borrar eso, borrar la función, presionar control, turno, entrar y se ha ido. Entonces hagamos un ejemplo más. Haremos una matriz bidimensional desde,
Ah, Ah, una hasta Be for So en este caso, escriba igual corchetes. Y luego recuerda, es como si estuvieras leyendo un libro. Entonces va a empezar con cualquier valor que quiera en uno seguido de B uno que A a B a un tres B tres un cuatro antes. Entonces, ¿por qué no simplemente hacemos, Ah, números
secuenciales otra vez? Entonces uno y luego comentario me lleva al siguiente Colin Value, que es a semi colon salta hacia abajo a la siguiente fila. Tres. Común para el punto y coma 56 se acercan. El corchete control shift enter y ¿qué se necesita? A. Necesitaba un par más de números para que esto llenara la matriz completa que había seleccionado, por lo que este es un buen ejemplo. Cuando acabamos de volver a la función, agrega un semi colon más siete turno de control común ocho, entra y ahí vas. Esa es la matriz adecuada. Por lo que la raza horizontal, vertical y bidimensional
5. Usar de constantes de arresto en fórmulas: por lo que dos cosas importantes a tener en cuenta sobre una constante de tasa. El primero es que no sólo tienen que contener valores como te he estado mostrando. También pueden contener texto rodeado de comillas o incluso valores lógicos o valores de error en segundo
lugar, en lugar de ser utilizados sólo para poblar celdas allí utilizadas con mayor frecuencia como parte de unas
fórmulas de rayos . Entonces viendo este ejemplo aquí, como se puede notar, hay una constante array anidada en una fórmula de rayos más grande. Y en realidad están pasando cuatro cosas aquí. Tenemos nuestra alguna función un array almacenado, que es sólo la referencia a uno a través de un cinco, el operador de multiplicación y finalmente, nuestra constante de rayos rodeó corchetes. Y debido a que estos números son valores están delimitados por semiccomas. Sé que esta es una constante de matriz vertical. Entonces todo lo que esta función está haciendo es tomar cada valor en la matriz de uno a cinco y multiplicarlo contra su valor correspondiente en la constante array que hemos anidado aquí. Entonces básicamente la alguna función es simplemente sumar o agregar todos esos productos, por lo que cinco veces uno ocho veces 22 veces 37 veces cuatro y tres veces cinco para llegar en un total de 70 eso tip aquí. Como mencioné, estos corchetes que rodean la constante de tasa se agregan manualmente. Los corchetes exteriores se agregan una vez que presiona control shift enter para convertir esto en una función de
matriz. Está bien, así que vamos a
sobresalir, a practicar un poco con esto. Primero lo primero, voy a crear matriz vertical. Constante. Es decir, el tipo es igual a corchete. 12345 Cerrar el cambio de control del soporte, Enter. Y ahora voy a seleccionar B uno a B cinco y voy a crear un
problema de multiplicación . Esos sólo van a ser los valores de uno a cinco. Esa es mi historia. Tiempos de matriz. Y ahora voy a anidar una nueva matriz vertical dentro de esta fórmula en lugar de crear una por separado y luego referirme a las celdas que está poblada. Simplemente voy a trabajar justo en la fórmula. Por lo que 10 punto y coma. 20 30 40 50. Tenga en cuenta que si pusiera comentarios aquí, lo estropearía todo porque estará tratando de multiplicar valores moviéndose hacia abajo,
fila por fila valores moviéndose hacia arriba llamando a mi columna y las cosas no se alinearían de la manera que quiero que lo hagan. Entonces en este momento estoy creando una matriz con las mismas dimensiones que uno a través de un cinco. Yo estoy para que consiga un tipo limpio de relación 1 a 1. Cierra el cambio de control de corchete, entra para terminar eso. Y como se puede ver, esto parece funcionar correctamente. Básicamente, lo que está haciendo es decir una una vez 10 que es 10 a dos veces 22 veces veinte, 40 tres veces, treinta, 94 veces, cuarenta, 1 60 finalmente, cinco veces 50 es a 50. Entonces, como puede ver, he guardado un paso simplemente insertando esta constante en la función de matriz. Entonces en la siguiente sección, vamos a hablar de cómo nombrar realmente estas matrices eso que no tengo que escribir 10 2030 40 50 desde cero. Si lo voy a usar una y otra vez, puedo darle un nombre y luego simplemente escribir el nombre en la fórmula de la misma manera que usamos nombró un aumento en algunas de las secciones anteriores.
6. Constantes de arsenales nombrados: bien, así que al igual que los rangos de celdas normales, puede asignar un nombre a una determinada constante de matriz. La única diferencia es que en el cuadro se refiere a, lo
escribes igual que lo harías si estás tratando de poblar celdas, lo que agregas los corchetes y ya sea delimitar por semi puntos. Si se trata de una matriz vertical, comas si es una matriz horizontal o una combinación de ambos. Si es un rayo bidimensional. Entonces todo lo que necesitas hacer es simplemente entrar en fórmulas, ya sea nombre, gerente o definir nombre. Crea un nombre para tu una constante y luego escríbalo igual que normalmente lo harías en el cuadro
refiere a y sabe que no tienes que presionar control shift enter aquí. Simplemente lo escribe con los corchetes y presiona OK que almacenará la constante de matriz con nombre para que pueda poblar celdas simplemente escribiendo la matriz con nombre. O puede reemplazar la constancia de matriz por la denominada Array. Si vive dentro de una fórmula. Eso, incluso cuando solo estás escribiendo el nombre Furnari Constant, todavía
tienes que presionar control shift enter para que se poble, así que está saltando para sobresalir y hacer un poco de práctica con esto, um, otra vez, Apenas trabajando con el cuaderno en blanco Por ahora. Um, ¿por qué no empezamos por crear, ya
sabes, matriz vertical
estándar de uno a cinco es igual a corchetes. 12345 Ya debería ser muy familiar. Control turno entrar. Entonces esa es una forma de agregar la matriz. Y como se puede ver, se almacena como una constante de matriz con cada uno de los cinco valores. Ahora lo que podría hacer es entrar en fórmulas para encontrar nombre, darle un nombre llamado Numbers y luego aquí, donde dice se refiere a eliminar todo después del signo igual, escriba el corchetes y luego exactamente lo mismo. Uno. punto y coma 2345 Cierre la prensa del corchete. OK, y ahora, si selecciono ser ganado a través de ser cinco y en el tipo de barra de fórmulas igual a números, puede ver excelencia marcado porque sabe que hay un nombre para Ray llamado números. Podría presionar control shift enter y se aplicará esa matriz a mi rango seleccionado. Entonces hagamos lo mismo. Excepto en lugar de usar números, vamos a crear una matriz con nombre con texto. Alguien llama a esto un texto, y se va a referir a otra matriz vertical que va a estar entre comillas. Un semi colon a semi colon tres semi colon. Perdón, bloquearte ahí por químico y cinco. Así que solo asegúrate de que tu entorno a cada uno de tus valores comillas que estás rodeando todo
el asunto con corchetes. Entonces presiona OK. Se guardó ese llamado Array como texto, Ahora en C uno a C cinco, Hay tipo igual texto podría ver se reconoce que controla. Turno. Entra y se aplicará la matriz de texto allí. Entonces ahora, solo para usar aquellos almacenados o nombrados raise como parte de una función, realmente
funciona de la misma manera. Entonces digamos que solo tenemos cinco números aleatorios en una matriz vertical de un siete a un 11 . Um, lo que podría hacer B séptimo o ser 11 si quiero hacer un problema de multiplicación similar que
hicimos en la última conferencia pero usar o referirme a matriz sin nombre en lugar de una
matriz manual incrustada , podría hacer eso así que escriba equivale a un 73 11 veces. Y entonces aquí es donde te habríamos hecho Nadie punto y coma 2345 Así que así es como solíamos hacerlo. Ahora es editar que en lugar de poner la matriz manual ahí, vamos a los números correctos. Por lo que un siete a 11 veces los números son un turno de control. Entra y obtendremos la misma respuesta. Por lo que solo hay algunos ejemplos de cómo usar matrices con nombre con constantes de tasa para hacer tu vida un poco más fácil. A continuación estaban a punto de sumergirse en excel y caminar allí un montón de demos diferentes.
7. La función de transpose: todo bien. Entonces la primera o una demo de fórmula que vamos a hacer se llama transponer. Y básicamente, la función transpuesta solo permite cambiar la orientación de una matriz de datos dada. Entonces, por ejemplo, si tienes una matriz de cinco rosas de alto pero dos columnas de ancho, transponiendo eso lo cambiaría a una matriz de dos filas de alto a cinco columnas de ancho. Algo a tener en cuenta que hace que esto sea un poco complicado es que el rango en el que ingresas función
transpuesta deben ser las dimensiones exactas de los datos transpuestos. Entonces la sintaxis sabia no podría ser más simple. Se acaba de transponer. Y luego la referencia a su matriz original en de nuevo. Ya que esto va a ser un cambio de control de función de matriz, entrar dedo del pie, bloquearlo en una pequeña punta. Aquí hay múltiples formas de transponer datos, y si quieres transponer el conjunto de datos que querrás editar posteriormente, puedes transponerlo como valores justos a diferencia de una fórmula usando pago especial transpuesto, que es el altar H v T atajo que te mostré de vuelta en la primera sección. Entonces saltemos a sobresalir esta vez. Vamos a abrir nuestras fórmulas de matriz de Excel Workbook de Sección nueve. Es un cuaderno bastante sencillo. Acabamos de tener una pestaña para trabajar con los datos de ventas llamados. Como pueden ver, tengo una columna de año aquí. Ah, lista de ubicaciones de tiendas, incluyendo Boston, Nueva York y Detroit y luego algunos números hipotéticos para ganancias de gasto de ingresos y están vivos. Y sólo vamos a trabajar con este estado que rayo mientras practicamos funciones sumarias. Entonces te voy a mostrar dos formas de transponer estos datos la primera vía, que es la sencilla ruta A. Si solo necesitas que se transpongan los valores y eso es
todo, es solo para agarrar la matriz de uno a F 10. Controla C a copiar. Selecciono cualquier celda donde quieras soltar tus datos de transposición que impacten a Ault H V T. Eso es Pegar Especial transpuesto. Y como se puede ver, se ha cambiado de una matriz seis por 10 a una matriz de 10 por seis, y básicamente se acaba de mover los valores a una nueva orientación Aquí. La segunda forma es usar una función de matriz. Entonces recuerda, necesito seleccionar las dimensiones perfectas de mis datos de transposición antes de escribir esta función y en realidad te voy a mostrar la manera equivocada de hacerlo primero,
para que tengas una idea de lo que pasa si no sigues esos reglas. Voy así que voy a escoger una matriz aleatoria como esta y en el tipo de barra de fórmulas es igual transpuesto. Y luego otra vez, mi elevar el conjunto de datos original. Cierra el paréntesis, y estoy listo para presionar control shift enter para transponerlo. Entonces, ¿qué hará la función de transposición es que llenará la matriz que he seleccionado y cualquier celda donde no pueda encontrar el valor apropiado del derecho original? Acaba de caer con un N A. Así que voy a borrar ese turno de control enter para deshacerme de él. Y ahora sé que quiero 10 por seis. Entonces hay 10 columnas de ancho 23456 Y empecemos de nuevo y tecleemos transponer una
vez más . Se levanta una a través de f 10 cerrarla turno de control, entrar. Y ahí tienes. Si quiero darle a esto el mismo formato, eso es una especie de lo que es bonito de todo th e v t es que preservó el formateo así que solo
pude agarrar este trozo en el dedo del pie casa tenía formato de prensa pintor cocinar la esquina superior aquí y lo hará aplicar el mismo formato. Entonces como puedes ver en este momento, la misma función transpuesta aparecerá en cualquier lugar. Doy click dentro de esto. Está bien, um, así que ahí vas a diferentes formas de transponer datos. Y de nuevo, depende de lo que intentes hacer aquí. Para determinar qué enfoque tomar si necesita mantener un enlace a su matriz original no transpuesta que la función de rayo es probablemente la mejor apuesta. Si buscas solo reorganizar tus datos y luego editar o modificar esos datos más adelante, entonces el antiguo atajo H V T es probablemente tu mejor apuesta. De cualquier manera, eso es función de transposición.
8. Enlace de datos entre hojas de hojas: Array vs. Comparación de la Array no arresto: todo bien. Demo número dos se trata de vincular datos entre hojas. Entonces volvamos a nuestra pestaña de datos de ventas. En realidad sólo voy a eliminar las columnas que habíamos creado a partir del
ejercicio de transposición . Ahora, lo que quiero hacer es simplemente duplicar los valores en una la ref 10 simplemente vinculándolos a una nueva hoja. Entonces voy a mostrarles dos enfoques primero el enfoque no array, seguido del enfoque array. Entonces voy a crear una nueva pestaña. Llama a este duplicado uno, y lo voy a hacer es escribir una fórmula auto y vender 81 justo igual a datos de ventas. A una prensa entrar. Y como esta es una referencia relativa, solo
puedo arrastrarla hacia fuera a f hacia abajo a la fila 10 y duplicará,
uh, uh, la matriz en la pestaña de datos de ventas. Como puedes ver, cada celda tiene una función diferente porque se vincula a una celda diferente en la pestaña de
datos de ventas . Y de nuevo, si quiero ser particular con mi formato, también puede usar el pintor de formatos para solicitar muchos entre celdas. Entonces tengo datos duplicados en dos pestañas. Vamos a crear 1/3 tab llamado duplicado, también. Y en este caso, voy a seleccionar toda la matriz que va a llenar y luego presionar iguales datos de ventas uno a uno a F 10. La única diferencia es que voy a golpear control shift enter como una función de matriz. Y ahí tienes. Se tira de nuevo en todos los datos correctos de la ciudad de datos de ventas. Simplemente copiaré el formato y lo pegaré ahí. Y así ahí van tres versiones del mismo conjunto de datos. La diferencia Ahora, cuando nos fijamos en estas celdas es que en la versión duplicada de una, tengo un total de 60 fórmulas aquí concedidas sus fórmulas simples. Pero cada célula tiene una fórmula diferente en la versión de rayos en duplicado, también. Dondequiera que haga clic, verá que es solo una función porque se está aplicando a una matriz completa en un solo paso. Entonces eso es solo una demostración de cómo las funciones de Ray pueden ayudarte a consolidar fórmulas en un caso como este. Y así, ya
sabes, aunque yo fuera a cambiar valores aquí, así que 2015 el gasto de Boston fue en realidad 1000 que en realidad se actualizará correctamente tanto en la matriz las versiones de Honore. Por lo que ahí tienes, vinculando datos entre hojas usando funciones de matriz
9. Vuelve los "X" mayores valores en un rango: no son así. Ahora que tienes una sensación de cómo funcionan estas fórmulas air a, empecemos a integrar algunas de las herramientas y funciones que hemos aprendido en
conferencias anteriores y veamos cómo esas podrían utilizarse como parte de un rayo funciones. Entonces el primer ejemplo que quiero mostrarles es, Ah, digamos que quiero sacar los tres primeros años de ingresos de la columna C. Así que en cada uno, sólo
voy a los tres primeros y, ah, la primera forma en que yo pensaría hacerlo. Pero se usaría el non o una versión que ya hemos aprendido. El gran función por lo que es igual a gran rayo C dos a C 10. Presione eso para arreglar eso y luego, para el número uno más grande que solo escriba sino uno para el valor K. Ciérrelo y golpea. Entrar. Podría simplemente copiar eso, pegarlo dos veces más y simplemente cambiar el valor K a un dos para el segundo más grande y 23 para el tercero más grande. Entonces esa es la versión no array. Quiero formatear de la misma manera. Ella es la pintora de formatos, así que son tres fórmulas separadas para devolver los tres valores más grandes de la columna C. Um, Um,
hagamos lo mismo aquí, Top tres. Esta vez voy a usar una función de array para hacer lo mismo. Entonces recuerda, empezaré haciendo mi selección de las tres celdas que a través de I para y
tipo de barra de fórmula es igual a ray grande que estoy buscando. El mayor valor sigue siendo C dos a C 10. La única diferencia aquí es que para el valor k, porque busco los tres valores más grandes. No se puede simplemente poner uno o dos o tres. Necesito poner aquí una constante de tasa. Va a abrir el corchete y hacer una constante de matriz vertical de uno a tres. Ya que estoy buscando los tres valores más grandes y la matriz que he seleccionado es una matriz
vertical de tres filas de alto. Entonces solo necesito cerrar ese turno de control de paréntesis entrar y ahí tienes. Tan mismo formato que se puede ver obteniendo exactamente los mismos tres primeros valores. La única diferencia es que tengo tres fórmulas aquí en edad de columna, solo una fórmula de matriz única y columna I Ahora y si quisiera cambiar esto de los
tres primeros a los cinco primeros. Um, otra vez, podría simplemente copiar esto. Pegar dos veces más. Cambie el 3 a 4 y un 4 a 5 para la versión de matriz. Lo que podría hacer. Es seleccionar una matriz vertical con cinco rosas en ella. Podría agregar un semi colon cuatro, semi colon cinco y turno de control. Entrar. Y ahí vas otra vez. Simplemente actualiza el formato Aquí, cambia esto al, uh, top cinco y otra vez dos maneras de lograr lo mismo.
10. Cuenta personajes en las células: bien, Así que la siguiente demo que quiero compartir contigo es sobre contar personajes a través de una gama de celdas, y ésta no es particularmente difícil. En realidad es una función de matriz bastante simple, pero es un buen ejemplo de una tarea que no puedes lograr en un solo paso a menos que uses teoría una fórmula. Yo soy así si nuestro objetivo, por la razón que sea, es contar el número de personajes a lo largo de toda esta gama de localizaciones de tiendas. B dos a través de Beat 10. Si tuviera que tomar el enfoque no array cuando tengo que hacer es tipo de
funciones Siris de Len que se relacionan con la columna B y simplemente aplicarlo hacia abajo. Entonces eso me da el número de personajes en cada uno de estos nombres de ciudad, incluyendo espacios. Y entonces tendría dedo del pie una función bastante suma aquí abajo para darme el total. Por lo que 63 personajes a lo largo de toda esta gama. También puedo hacer lo mismo con una función de matriz en un solo paso, y podría comenzar con la función de longitud y aplicarla para ser 23 b 10. Pero el problema es, aunque lo entre en con el turno de control. Entro, solo
consigo seis porque está en Lee calculando la longitud de la primera celda en la referencia. Entonces lo que necesito hacer es simplemente insertar una alguna función aquí también. Entonces van a ser algunas de las longitudes de cada una de las cuerdas en cada fila de mi rayo. Y ahora, una vez que golpee turno de control, entra. Como pueden ver, obtengo el mismo total en un solo paso, a diferencia de alrededor de una docena así que un ejemplo de cómo podría ser capaz de trabajar de
manera más eficiente tomando el enfoque de rayos versus el enfoque de fórmula celular tradicional.
11. Crear una fórmula de arsenal "MAXIMO" de la matrícula de "MAXIMO": Muy bien, así que para la demo final te voy a mostrar cómo construir una función max if personalizada. Este es un gran ejemplo de donde las funciones de matriz pueden entrar en juego cuando no eres array o las fórmulas celulares tradicionales pueden quedar cortas. Entonces si recuerdas en la sección de funciones estadísticas, hablamos de algunos ifs, conteo ifs, ifs
promedio. Desafortunadamente, no
existe una fórmula equivalente si se desea calcular el valor máximo en una columna en función algunos criterios. Por lo que no hay max si fuera de la función de caja que podamos usar. Por suerte, las funciones de
matriz pueden hacer el trabajo. Entonces lo que quiero hacer aquí es devolver el valor máximo para los ingresos y la columna C basado en un nombre
específico de la ubicación de la tienda Así que uno de estos tres nombres de ciudad. Entonces lo primero que quiero hacer sólo por el bien de mostrar un ejemplo. Es sólo nombre a esta ciudad array, y no estoy haciendo ah array constante. Todo lo que estoy haciendo es solo nombrar el rango de B a tres b 10 solo para que recordemos que
esa es una opción para ayudar a ahorrar tiempo al escribir esta función para que los arrays ahora llamados ciudad Lo que voy a hacer es escribir uno de los nombres de ciudad aquí mismo en H ocho, toma Boston y luego en h nueve. Y aquí es donde voy a escribir mi max. Si la función así va a ser una función relativamente simple vamos a empezar con iguales ,
Max, Max, abre la princesa si y ahora la prueba lógica va a ser ciudad. Ya ves, es una especie de disparado en la matriz porque lo reconoció como un Ray llamado, y la ciudad es igual a cualquier valor en h ocho. Ahora mismo es Boston. Y si cambio ese valor, tirarías en el máximo para Nueva York o Detroit,
por ejemplo, por ejemplo, en entonces el valor de verdadero solo va a ser la matriz de C dos a C 10 que es mi campo de
ingresos. Ahí es donde estoy tratando de extraer el valor máximo de tan cerca a paréntesis y control shift enter. Y ahí tienes. Así que agarra el formato de aparecer en la pestaña de inicio. Boston, 1726 dólares. Podríamos hacer una revisión puntual rápida. Confirmar que ese es el valor máximo para Boston. Y después de cambiar esto a Nueva York, por ejemplo, en 1944. Ella también tiene razón. Y Detroit, 17 43. Ahí lo tienes, tan gran manera de tipo de crear nuestra propia función personalizada donde no teníamos la opción. Usando nuestras fórmulas estadísticas tradicionales que envuelve la sección de fórmulas de matriz. Esto es realmente sólo una pequeña muestra de los tipos de análisis que se podría hacer con la carrera . Por lo que te animo. Una vez que te pongas cómodo, solo comienza a explorar cómo y dónde puedes usar los's en otras áreas. Entonces a continuación, tenemos una sección final que cubre algunas funciones de bonus o para hacer algunas demos realmente, realmente interesantes ahí, así que mantente atentos para el tramo final.
12. BONO: "MAX IF" con múltiples criterios: está bien, así que en realidad mintió. Tengo algunas demostraciones de función de array adicionales con las que quiero compartir. Ustedes chicos que han surgido desde que se publicó originalmente el curso realmente ayudaron a demostrar lo versátiles y poderosas que podrían ser las funciones de array. Entonces en esta conferencia, quiero realmente retomar sobre la función max if que acabamos de completar y hacerla un poco más complicada. Entonces en lugar de simplemente basar el máximo, si en un criterio donde City es igual a Detroit o Boston o Nueva York, quiero actualizar ese Max. Si la función de matriz para dar cuenta de múltiples criterios específicamente tanto ubicación o ciudad el año así saltando de nuevo en
el libro de trabajo, lo primero que haré es solo seleccionar un dos a 8 10 y nombrar ese rango año solo para sea un poco más fácil trabajar con y por aquí. Y comí. Sólo pongamos un año podría ser cualquier cosa. Pondré el 2014 aquí, y vendería yo nueve. Aquí es donde quiero entrar a mi nuevo max. Si la función, en realidad solo
arrastraré esto para darme un buen punto de partida, puedo editar las referencias City todavía necesita igualar H ocho, y los valores estaban buscando el máximo dentro. No es D a tres d 10. Todavía se ve al Recibo 10 el campo de ingresos. Entonces si presiono control shift enter, verás que tengo el mismo punto de partida que antes. Y ahora sólo podría agregar ese segundo criterio aquí Así que quiero hacer es realmente rodear mis primeros criterios. Ciudad equivale a H ocho entre paréntesis. Multiplica eso por mi segundo criterio, que es año. Se puede ver que identifica el rango de nombres igual que comí cerrar ese paréntesis. Entonces tengo dos criterios, cada uno rodeado de paréntesis, separados por un multiplicador. Te mostraré por qué en tan solo un minuto. Entonces si controlo turno, entra ahora devuelve 16 09 que es el valor máximo Si City es igual a Detroit y año igual 2014. Entonces Detroit 2014 16 09 Ahora, obviamente, es el único valor que existe para Detroit 2014. Pero para darle una prueba a eso, podemos cambiar el 2013 a 2014. Y ahora, como se puede ver, el valor máximo cambia de 16 09 a 17 43. Entonces sólo deshacer ese cambio de nuevo al 16 09 Ahora, vamos a cavar un poco, tratando de entender exactamente cómo está operando esto. El mejor modo de hacerlo es usar la herramienta Evaluar fórmula. Entonces con esta celda seleccionada, puedo entrar en fórmulas, evaluar fórmula, y esto me guiará por cada paso de la función y cómo se evalúa. Entonces vamos a empezar con la ciudad subrayada, y cuando evaluemos la ciudad llamada rango, lo traduce en la matriz de nueve filas diferentes de datos. Boston, Boston, Boston, Nueva York, Nueva York, Nueva York, Detroit , Detroit. Por lo que ahora estamos buscando casos en los que los valores dentro de esa matriz igualen el valor en la edad de
ocho años , que evalúa a Detroit y a medida
que Excel recorre esto, va a traducir esa afirmación en una matriz de tregua y lo falso es tan por cada fila donde esa condición es verdadera o la ciudad
es igual a Detroit, devolverá un verdadero y por cada fila donde ese criterio no
sea cierto, devolverá un falso para que primer criterio ciudad sea igual a ocho años se traduzca en falso, falso, falso, falso, falso, falso verdadero. Verdadero, Verdadero, ya que sólo las tres últimas filas dentro de esta matriz cumplen con esos criterios. A continuación, vamos a hacer lo mismo con nuestros criterios de año. Por lo que año evalúa a una matriz que contiene todas nuestras fechas en estas nueve filas y columnas A . Y estamos buscando casos en los que los valores en esa matriz sean iguales al valor y yo ocho, que en este momento es 2014. Por lo que esto evaluará de manera muy similar traducirá esta afirmación en otra. Siris de verdadero y falso es en este caso, tenemos falso,
verdadero, falso, falso, verdadero, falso, falso, falso, verdadero, falso. Y como se puede ver, eso tiene sentido porque por cada tres años, sólo uno es 2014. Entonces ahora aquí está la clave que permite que esta función devuelva el valor adecuado que hemos creado a un aumento. En base a nuestros dos criterios. El primer array contiene valores verdaderos y falsos basados en filas que coinciden con la ciudad de ubicación. El segundo array contiene valores verdaderos y falsos basados en rosa que cumplen el año. Entonces cuando multiplicamos estos dos juntos, el resultado es una sola matriz de ceros y unos que se pueden interpretar exactamente de la misma
manera que interpretarías tregua y False es donde verdadero es igual a uno falso es igual a cero. Entonces lo que tenemos aquí ahora es una matriz que se parece a 000000010 Así que Onley un valor en esa matriz poblada como verdadero. Y eso se debe a que cualquier instancia donde uno u otro criterio no se cumpliera. Cuando esos dos valores se multiplican, uno de los cuales tiene que ser cero, dará como resultado un cero en la matriz final. Entonces uno indica la fila dentro de nuestra matriz donde se han cumplido todos los criterios, y en este caso es la segunda a última fila. Y si solo miras nuestros datos aquí, esa segunda a última fila Fila nueve es la carretera que contiene tanto el año Nos preocupamos por el 2014 la ciudad que nos preocupa Detroit ahora la última parte de esta función. El enunciado if esencialmente simplemente asigna un valor desde dentro del rango de C dos a C 10 a cualquier valor en nuestro rayo que no sea igual a cero. Entonces, en otras palabras, vamos a sacar el valor del segundo dedo último venta en el rango de C dos
a C 10 desde el segundo dedo del pie. Último lugar en nuestras matrices, el único valor distinto de cero. Y como puedes ver, da como resultado 16 09 ya que ese es el segundo en último valor en nuestra matriz de C dos a C 10 y ahora nos queda una simple función max. Entonces cuál es el máximo de falso o 000000 16 090 Es 16 09 Y ese es el valor que se devuelve. Entonces como se puede ver un rayo, las funciones son de forma
muy, muy diferentes, funciones celulares
normales en que tipo de ciclo a través de estos procesos todo dentro de la misma fórmula dentro de la misma celda, y eso es lo que los hace tan poderosos.
13. BONO: usa el operador de doble Unary ("--"): De acuerdo, pasemos por un ejemplo más. Solo para mostrarte cómo las fórmulas de matriz a veces se pueden usar para hacer cosas que venden fórmulas simplemente no pueden lograr. Entonces en este caso, apegándose a nuestro ejemplo Data, Digamos que alguien te pregunta, ¿cuántos años excedió el beneficio en este conjunto de datos? Si lograras eso usando fórmulas celulares, podrías hacer algo así como una declaración condicional que dice, Si es mayor que D, entonces devolvió una. De lo contrario cero y se podría aplicar eso hacia abajo y luego tomar la parte de esa columna y ahí se puede ver bien ahí cuatro años o cuatro filas en este conjunto de datos en el que la ganancia superó el gasto. Pero eso dio dos pasos. Usé 10 fórmulas. Las funciones de matriz total podrían hacer esto con mucha más elegancia y con mucho menos
poder de procesamiento para hacer eso, digamos, ya
sabes, beneficio. Es mayor que gastar ir línea de menú de inicio que hacer cursiva Y aquí es donde quiero
devolver ese mismo valor de cuatro. Así que en realidad voy a usar una función de matriz usando algunos, así que voy a decir igual algunos y ahora, en lugar de solo un solo número o un solo rango tradicional de números aquí que
realmente puede ingresar a rangos de datos aquí todos a la vez. Por lo que puedo decir E a través de e 10 es mayor que D a tres d 10 y cerrar el paréntesis. Entonces en teoría, lo que esto debería hacer como una función de matriz, es ciclo a través de cada fila dentro tanto de un aumento como de un recuento de un verdadero o uno en cualquier caso donde el criterio es verdadero o donde la columna es grande y la columna d y un cero de lo contrario, para luego tomar el sol y regreso de cuatro. Entonces si controlamos turno enter, verás que obtenemos un cero, que obviamente no es lo que queremos. Entonces se va a fórmulas, evaluar fórmula y ver qué está pasando aquí. Entonces en esta declaración mayor que se evalúa, sí da como resultado la matriz que esperaríamos falso,
falso, verdadero, verdadero, falso, verdadero, verdadero, verdadero, falso, falso y, como se puede ver por tanto valores verdaderos en las posiciones 34 seis y siete. Y si nos fijamos en esas subidas específicas dentro de nuestra fila de datos, 1239 68 es genial y en 7 58 49 ochenta, genial en los 9 64 6 y siete. Entonces esas son las posiciones correctas o las instancias correctas de filas que coincidan con eso
mayor que los criterios. El problema es que
cuando tratamos de sumar esta afirmación, se evalúa a cero. Y la razón es que a pesar de que en muchos casos Excel tratará falso está en ceros y True's como uno con una afirmación alguna, no sabe cómo manejar esos valores. Por lo que Excel realmente tiene una pequeña herramienta muy útil llamada la W Nery y que esencialmente convierte valores booleanos no numéricos verdaderos y falsos en unos reales y ceros. Entonces si volvemos a sumergirnos en su función aquí justo después de que ese primer paréntesis va a hacer dos guiones, ese es el doble tú Nery. No hace falta volver a abrir el paréntesis y cerrar el segundo al final. Ahora bien, si
controlo shift enter, devuelve el valor adecuado de cuatro. Entonces, cualquier vez que tengas una función de matriz donde necesites convertir una matriz específica de valores
booleanos en unos y ceros que W Nery debería hacer el truco
14. INICIO: funciones de la Array tienen: todo bien. Es hora de palmaditas en la espalda. Acabas de terminar la sección de carreras. Cosas locas, definitivamente un poco complicadas de acostumbrarse y muy,
muy diferentes de las funciones celulares tradicionales de las que hemos estado hablando ahora otra vez . Como dije antes, esto es realmente solo arañar la superficie y darte algunos ejemplos selectos de cómo nuestras fórmulas
A se pueden usar en Excel. Dicho esto, una vez que domines los fundamentos de cómo funcionan las fórmulas de Ray, puedes empezar a aplicarlas con bastante rapidez y formas más complicadas o más avanzadas. Por lo que la hora de la tarea para cualquier persona interesada en cavar un poco más profundo y conseguir algo más práctica con estos en el archivo del ejercicio de la tarea que está disponible en el curso. Recurso es que tenemos dos pestañas con ejercicios de array. El 1er 1 es un rayo funciones, y aquí vamos a practicar algunas cosas como Max. Si Max si con múltiples criterios usando ese doble te Nery para convertir valores booleanos en números y luego durar pero no menos importante, tenemos esta pestaña de función de matriz de bonus, que es donde las cosas empiezan a ponerse realmente peludo. Esencialmente, lo que vamos a tratar de hacer aquí es usar la fórmula Honore para devolver un subconjunto de este rango de un tres d 14 basado en criterios específicos. Por lo que queremos devolver ese subconjunto justo aquí en yo tres a l 14 pero Onley para Rose que cumplan con la fecha de inicio, fecha de
fin y criterios mínimos de ingresos asignados aquí en la columna G. Así que en realidad te voy a dar un pequeño adelanto al archivo de respuesta porque lo que he hecho se presenta una explicación paso a paso en mis propias palabras de cómo esta cosa realmente está funcionando. Y si echas un pequeño ojeo a la función, puedes ver que es una especie de bestia. Dicho
esto, probablemente esta no sea la única forma de hacerlo. Tenga en cuenta que este es el enfoque que elegí para lograr esto, Pero muy bien puede haber otros. Entonces, como siempre, los posts al panel de discusión. Si necesitas ayuda, envíame un mensaje directamente, o si solo quieres decir hola, siempre aquí para ayudar. Gracias, chicos. Buena suerte
Chris Dutton, Founder, Excel Maven
