Copiar transacciones automáticamente en rangos de derrames

Digamos que tenemos una tabla de entrada de datos, donde escribimos o pegamos nuevas transacciones. Nos gustaría que se dirija una copia de cada transacción a una hoja de trabajo específica, según el tipo de transacción. Por ejemplo, las transacciones de ventas deben fluir desde la tabla de entrada de datos a otra hoja de trabajo que muestre todas las transacciones de ventas. Las devoluciones deben fluir a una hoja de trabajo dedicada a las devoluciones, y así sucesivamente.

Como ocurre con todo en Excel, existen varios enfoques para esto. Por ejemplo, podríamos aplicar un filtro y luego copiar/pegar. Podríamos usar Power Query. Incluso podríamos usar una tabla dinámica de varias maneras (profundizar para crear páginas de detalles, filtrar o usar el comando mostrar páginas de filtro de informes). Bueno, ahora hay otra opción… Rangos de derrame. Una ventaja notable de usar una gama de derrames es que son literalmente manos libres… sin filtros manuales, sin copiar/pegar manualmente, ni siquiera la necesidad de hacer clic en un botón de actualización o cualquier otro icono de comando. Entonces, dependiendo del libro de trabajo, los usuarios y los objetivos, esta puede ser una buena opción. Vamos a profundizar en.

Objetivo

Antes de llegar demasiado lejos, retrocedamos y analicemos nuestro objetivo. Tenemos una tabla donde ingresamos (o pegamos) transacciones. Quizás algo como esto:

Nos gustaría que las transacciones de ventas fluyan automáticamente a otra hoja de trabajo. Quizás algo como esto:

Además, nos gustaría que todas las devoluciones fluyan a otra hoja de trabajo, como esta:

Lograr esto es bastante fácil con matrices dinámicas y rangos de derrame. Y la mejor parte es que a medida que ingresa nuevas transacciones en la tabla de datos, éstas fluirán automáticamente a las hojas correspondientes.

Narrativo

Estos son los pasos que usaremos:

• Crear la tabla de entrada de datos
• Escriba una fórmula para recuperar transacciones de ventas.
• Escribe una fórmula para recuperar devoluciones.

Hagámoslo.

Nota: las matrices dinámicas y los rangos de derrame están disponibles de forma limitada en el momento en que escribo esta publicación. Por lo tanto, es posible que su versión de Excel no tenga estas capacidades. En el momento en que escribo esto, están disponibles para los suscriptores de O365 con el canal Insiders Fast. Si es suscriptor de O365, es posible que pueda cambiar al canal de actualización Insiders Fast. Consulte el sitio web de Microsoft para obtener más información.

Crear la tabla de entrada de datos

Primero, queremos crear una tabla para que los usuarios escriban o peguen transacciones. Generalmente, querremos almacenar estos datos en una tabla. Para convertir un rango normal en una tabla, seleccione cualquier celda del rango y use el comando Insertar Tabla .

Si los datos se almacenan en una tabla, podemos usar el nombre de la tabla y otras referencias de tablas estructuradas en las fórmulas. Por ejemplo, Tabla1 para hacer referencia a toda la tabla y Tabla1[Tipo] para hacer referencia a la columna de tipo de la tabla.

Si los datos se almacenan en un rango normal en lugar de una tabla, entonces podemos usar referencias de solo columnas para garantizar que se incluyan nuevos datos. Por ejemplo, A:C para hacer referencia a todas las filas de tres columnas de datos, o B:B para hacer referencia a todas las filas de una sola columna.

En este ejemplo, utilicé una tabla para almacenar los datos y se llama Tabla1. La tabla se muestra a continuación.

Ahora es el momento de escribir las fórmulas. Comenzaremos recuperando las transacciones de ventas.

Escriba una fórmula para recuperar transacciones de ventas.

En una nueva hoja de trabajo, queremos escribir una fórmula que recupere transacciones donde el valor de la columna Tipo es igual a “Venta”. Para lograr esto, usaremos una función de matriz dinámica llamada FILTRO. La función FILTRO nos permite devolver un rango, pero solo incluir filas que cumplan una o más condiciones.

Para recuperar transacciones de la Tabla 1, donde el valor de la columna Tipo es igual a “Venta”, podríamos usar esta fórmula:

=FILTRO(Tabla1, Tabla1[Tipo]="Venta")

Le damos a enter y… ¡guau! Todas las columnas de todas las transacciones de venta fluyen hacia la hoja:

Ni siquiera necesitamos completar la fórmula hacia abajo o hacia la derecha. Esto se debe a que la fórmula devuelve múltiples valores… y los resultados se extienden a las celdas adyacentes (filas y columnas) según sea necesario.

Probémoslo para asegurarnos de que funciona. Volvemos a la tabla de datos y agregamos una nueva transacción de ventas, como la 1006 a continuación:

Volvemos a la hoja de trabajo de ventas y… OH DIOS MÍO… está ahí:

Sin filtrado manual, sin copiar/pegar manualmente, sin actualización manual, sin ningún paso manual. Es completamente automático.

Recuperar las devoluciones es igual de fácil.

Escribe una fórmula para recuperar devoluciones.

En otra hoja de trabajo nueva, simplemente escribimos una única fórmula:

=FILTRO(Tabla1,Tabla1[Tipo]="Retorno")

Le damos enter y bam… listo:

Esa fórmula única devuelve múltiples valores, distribuyéndose hacia abajo y hacia la derecha según sea necesario para acomodarlos. Si agregamos una nueva transacción de devolución, la fórmula también la recupera automáticamente.

Bueno, esa es otra forma en que podemos aplicar fórmulas matrices dinámicas y rangos de derrame. Sin duda, estas capacidades están abriendo nuevas formas de realizar tareas.

Si tiene otros consejos sobre el alcance de los derrames, compártalos publicando un comentario a continuación… ¡gracias!

Archivo de muestra: CopyToSpillRange.xlsx