Regresar automáticamente a la función de aceleración

En nuestra comprensión anterior de las fórmulas, cuando seleccionamos una celda y escribimos la fórmula en ella, el resultado de la fórmula solo aparecerá en esta celda. Y si está utilizando la versión de Office 365, la nueva fórmula de matriz dinámica de esta versión subvertirá por completo su comprensión de las fórmulas.

¿Qué es una fórmula matricial dinámica? Echemos un vistazo a la explicación oficial:

Excel devuelve un conjunto de valores (también llamado matriz) que se devuelven a celdas adyacentes. Este comportamiento se llama "desbordamiento". Las fórmulas que pueden devolver matrices de tamaño variable se denominan fórmulas de matriz dinámica. La fórmula actual que devuelve una matriz que se desborda correctamente se puede llamar fórmula de matriz desbordada.

Aquí se menciona un concepto de "desbordamiento". ¿Qué es "desbordamiento"? En pocas palabras, la fórmula actual puede invadir el territorio de otros pueblos y tiene la función de expansión automática.

Por ejemplo, la tabla de ventas de productos muy simple que se muestra en la siguiente figura. El método tradicional para calcular las ventas es calcularlas fila por fila. La primera fórmula simplemente multiplica el volumen de ventas y el precio unitario del producto A. y luego complete la fórmula en toda la columna.

Si la fórmula de la primera fila hace referencia a varias celdas, como B3:B7*C3:C7 en este ejemplo, el resultado devuelto incluirá cinco filas y la fórmula se desbordará automáticamente de la celda E3. Vaya a la celda E7, esta es la fórmula de matriz dinámica. Excel marcará las fórmulas desbordadas con un borde exterior azul.

Con este comportamiento de matriz desbordante, muchos cálculos que antes requerían fórmulas muy complejas ahora se implementan de forma sencilla. Por ejemplo, la tabla de multiplicar 99 en la imagen a continuación. Antes de completar el cálculo de esta tabla, debemos bloquear la dirección, luego arrastrarla manualmente hacia la derecha y hacer doble clic para completar la parte inferior. Ahora es muy simple, simplemente seleccione una celda, escriba una fórmula, no es necesario bloquear la dirección, simplemente presione la tecla Intro y todo estará listo. ¡afilado!

Si desea que se actualicen los datos de volumen de ventas y precio unitario en el primer caso, el volumen de ventas de la derecha se puede expandir automáticamente a nuevos datos, y solo necesita realizar una pequeña configuración en el estructura de tabla de la tabla detallada, es decir, en Agregar "Aplicar formato de tabla" a la tabla de datos. La siguiente es la explicación oficial:

Si está escribiendo una fórmula de matriz dinámica para operar con una lista de datos, es útil colocarla en una hoja de Excel y luego usar referencias estructuradas para hacer referencia a los datos. Esto se debe a que las referencias estructuradas se ajustan automáticamente cuando se agregan o eliminan filas de la tabla.

Por ejemplo, quiero extraer una lista de departamentos no duplicada de la lista de departamentos con información repetida en el lado izquierdo de la imagen a continuación. Un método es utilizar eliminar duplicados en la pestaña de datos, pero si los datos se actualizan, hay que eliminarlos nuevamente. Muchas veces se utiliza la lista no duplicada que queremos en el proceso de la función. En este momento, podemos usar las nuevas características únicas de Office 365 para obtener una lista dinámica no duplicada.

Agreguemos "Aplicar formato de tabla" desde la pestaña Inicio a la lista de Departamentos a la izquierda para convertirla en una "Tabla". Luego puede usar la función ÚNICA para extraer los valores únicos en esta tabla y, dado que el resultado son valores múltiples, se desbordará en las celdas adyacentes en la parte inferior. Y cuando agregamos datos a la lista de departamentos, los datos se actualizan dinámicamente.

Después de eso, usamos la función CONTARA a la derecha para contar el número de departamentos dinámicos. ¿Qué se debe escribir en la fuente de datos de la función para que el resultado devuelto pueda actualizarse dinámicamente? Solo necesitamos ingresar G2# en el parámetro de la función. G2# representa todos los datos que comienzan con G2 en este resultado dinámico. Por lo tanto, si se actualizan los datos de la lista de departamentos en la columna E, los resultados de la fórmula de matriz dinámica de la lista de departamentos no duplicados en la columna G se actualizarán automáticamente, y cuando se actualicen los datos en la columna G, el número de departamentos relacionados en la columna también estaré actualizado, verdaderamente entrelazando y vinculando, renovando.

Hay dos puntos a tener en cuenta al utilizar fórmulas de matriz dinámica:

1. Solo se puede editar la fórmula de matriz dinámica de la primera celda en el área de desbordamiento. Porque solo esta celda tiene la fórmula escrita. Otras celdas desbordadas pueden ver la fórmula (gris), pero esto es solo una "imagen" y no se puede editar.

2. Los datos no se pueden almacenar en la ubicación que se va a desbordar.

¿Qué devolverá Excel si esto sucede? #¡Dilo! Error que indica un bloqueo. Si elimino el "bloqueo", la fórmula se desborda como se esperaba.