Resumen

Esta guía es para ayudarle a comprender las mejores maneras de desagrupar y fraccionar números de cuenta y descripciones de cuentas que aparecen en la misma columna dentro de un archivo.

Solución

Considere el siguiente extracto de un archivo de libro mayor:

Los problemas

1. Este archivo se agrupa por cuenta: solo una fila contiene información de la cuenta (filas 7, 5695), seguida de todas las transacciones para esa fila (filas 8-5691, 5696-6176 en adelante) (infringe el requisito de fila 1)
2. La información de la cuenta se encuentra en la columna A, pero contiene tanto el número de cuenta como la descripción de la cuenta separados por " - " (carácter de espacio más - más carácter de espacio) (filas 7, 5695) (infringe el requisito de columna 1)
3. Hay filas totales/subtotales innecesarias (5692-5693, 6177-6178) y filas en blanco debajo de ellas (infringe el requisito general 2)

La solución (se necesita formato)

1. Desagrupar por cuenta (arrastrar la información de la cuenta a cada fila de transacción)
2. Fraccione el Identificador de cuenta y la Descripción de la cuenta en diferentes columnas
3. Excluya filas extrañas totales/subtotales/en blanco y filas extrañas (filas 1-4) por encima de la fila del encabezado (fila 5)

Insertamos dos columnas, "Identificador de cuenta" y "Descripción de cuenta", antes del comienzo del archivo para separar el número de cuenta y la descripción de la cuenta y arrastrarlos hacia abajo:

Identificador de cuenta: Celda A7 e inferior

=IFERROR(LEFT(C7,SEARCH(" - ",C7)-1),A6)

• SEARCH(" - ",C7)

  encuentra la posición en la cadena C7 donde aparece la primera cadena " - ". A continuación, restamos 1 a esa posición para obtener el número de caracteres del identificador de cuenta (5 caracteres para el identificador de cuenta 11110),
• luego usamos IZQUIERDA(C7) para obtener el número de caracteres más a la izquierda de esa cadena de cuenta (los primeros 5 caracteres en la celda C7).
• Finalmente, IFERROR(A, B) devuelve A si A no es un error, y B si A es un error.
  • Queremos extraer el número de cuenta y la descripción de la cuenta solo de las filas con esa información en la columna C.
  • Observamos que estas celdas con el número de cuenta y la descripción de la cuenta se identifican por la presencia de " - " que separa el número de cuenta y la descripción de la cuenta. SEARCH() devuelve un error (#VALUE!) si no se encuentra la cadena; por lo tanto, para el resto de las filas, #VALUE! es devuelto por SEARCH() (que fluye hasta la fórmula LEFT() en la que está anidada. Para estas otras filas, solo queremos devolver el número de cuenta que habíamos extraído en la fila anterior.
  • Así que en este ejemplo, la IZQUIERDA anidada() y la BÚSQUEDA() en la celda A7 devuelve 1110, porque la celda C7 tiene " - " en ella.
  • Pero en la celda A8,

    =IFERROR(LEFT(C8,SEARCH(" - ",C8)-1),A7)

    la IZQUIERDA anidada () y la BÚSQUEDA() devuelve #VALUE! porque la celda C8 (2019-04-01) no tiene " - ".
  • Esto es capturado por IFERROR(), que, viendo el #VALUE!, pasa a la A7, que es 1110, como se comentó anteriormente y, por lo tanto también devuelve 1110. Todo esto continúa hasta que llega a la fila 5695, cuando descubre que la celda C5695 tiene " - " y, por lo tanto, extrae la cuenta 1-1112

Descripción de la cuenta: Celda B7 e inferior

=IFERROR(MID(C7,SEARCH(" - ",C7)+3,LEN(C7)),B6)

Las fórmulas de esta columna siguen la misma lógica que la de la columna identificador de cuenta (columna A), excepto que, en lugar de usar LEFT(), utiliza MID(), que es útil para extraer texto desde cualquier lugar que no esté al principio hasta el final, o no al final. La sintaxis es:

MID(text, start_num, num_chars)

donde el texto es la cadena de búsqueda (C7)

start_num es la posición en la que se debe empezar. Utilizamos…

SEARCH(" - ",C7)+3

…porque queremos empezar después de “ - “.

Recuperar desde arriba, SEARCH(" - ",C7) indica la posición donde comienza " - ". Dado que " - " tiene 3 caracteres de longitud, añadimos 3 para obtener la posición donde comienza la descripción de la cuenta.

Por último, el LEN(C7) proporciona la longitud de la cadena en la celda C7, para garantizar que lo llevamos hasta el final.

Subtotales

Las siguientes son las fórmulas para las células G1 y H1:

=SUBTOTAL(9,G5:G50000)
=SUBTOTAL(9,H5:H50000)

Utilizamos 50 000 porque este archivo en particular tiene menos de 50 000 filas. Lo cambiamos por un número lo suficientemente grande como para acomodar el número total de filas que tiene su archivo. Añadimos estas fórmulas para comprobar que el archivo se equilibra cuando realizamos el paso final de filtrado, para mantener solo las filas de transacción (siguiente).

Filtrar para mantener solo filas de transacciones

Después de activar los filtros, comprobamos la columna C (Fecha) y vemos que están las fechas de 2020 y 2019 y, el resto, son números de cuenta:

Copiar y pegar valores en un libro de trabajo nuevo

Teniendo en cuenta que todas las fechas son las filas de transacciones, seleccione solo las fechas:

Observe que los resultados de nuestras fórmulas SUBTOTAL() en las celdas G1 y H1 muestran que las transacciones totales están en equilibrio (¡sí!)

Nuestro paso final es seleccionar la fila de encabezado y las filas de transacción seleccionadas (es decir, la fila 5 en adelante en la vista filtrada; puede comenzar en la celda A5, pulsar Ctrl-Mayús-Fin para llegar al final del archivo y ajustar la posición según sea necesario), luego copiar y pegar valores especiales en un nuevo libro de trabajo

Por último, en el nuevo libro de trabajo, asegúrese de formatear la columna Fecha como una fecha con formato AAAA-MM-DD (pulse Ctrl-1 en PC o ⌘1 en Mac):

Y ahora tenemos un archivo con un formato precioso, listo para importar en MindBridge.

Desagrupar en Excel, fraccionar en MindBridge

MindBridge ya proporciona una función durante la correlación de columnas que permite el fraccionamiento de columnas, por lo que, si prefiere utilizar una fórmula más sencilla solo para desagrupar, sin fraccionar al mismo tiempo, puede añadir una columna Cuenta que extraerá el valor de la columna B si contiene " - "; de lo contrario, extraiga el valor encontrado anteriormente:

=IF(ISERROR(SEARCH(" - ",B7)),A6,B7)

A continuación, siga los pasos descritos anteriormente para filtrar, copiar y pegar en un nuevo libro de trabajo.

Comience a importar el archivo del libro mayor formateado en MindBridge; luego, en el Paso 2 para la correlación de columnas, haga clic en los tres puntos a la derecha y seleccione Fraccionar columna. Aprenda a fraccionar columnas.

Fraccionamiento de cuentas más complicadas

Aprenda a crear una fórmula para fraccionar un número de cuenta .

¿Tiene alguna otra idea en mente? Converse en línea con nosotros o envíe una solicitud de asistencia adicional.