Resumen

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

Resolución

Considere el siguiente extracto de un archivo de libro mayor:

Los problemas

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

La solución (formateo necesario)

1. Desagrupar por cuenta (arrastrar la información de la cuenta a cada fila de transacción)
2. Fraccionar el número de cuenta y la descripción de la cuenta en diferentes columnas
3. Excluir filas de total/subtotal/en blanco innecesarias y filas innecesarias (filas 1-4) por encima de la fila de encabezado (fila 5)

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

ID de cuenta: Celda A7 y siguientes

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

• SEARCH(" - ",C7)

  encuentra la posición en la cadena C7 donde aparece la primera cadena " - ". Restamos 1 de eso para obtener el número de caracteres que ocupa el número de cuenta (5 caracteres para la cuenta 11110),
• luego usamos LEFT(C7,) para obtener el número más a la izquierda de caracteres de esa cadena de cuenta (así que los primeros 5 caracteres en la celda C7).
• Finalmente, IFERROR(A,B) devuelve A si A no es un error, 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.
  • Notamos que estas celdas con número de cuenta y descripción de cuenta están identificadas 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 todas las demás filas, #VALUE! es devuelto por SEARCH() (que fluye hacia la fórmula LEFT() en la que está anidado. 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, el LEFT() y SEARCH() anidados en la celda A7 devuelven 1110, porque la celda C7 tiene " - " en ella.
  • Pero en la celda A8,

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

    el LEFT() y SEARCH() anidados devuelven #VALUE! porque la celda C8 (2019-04-01) no tiene " - " en ella.
  • Esto es capturado por IFERROR(), que, al ver el #VALUE!, pasa a A7, que es 1110 como se discutió anteriormente, y por lo tanto también devuelve 1110. Todo esto continúa hasta que llega a la fila 5695, cuando encuentra que la celda C5695 tiene " - " en ella y por lo tanto extrae la cuenta 1-1112

Descripción de cuenta: Celda B7 y siguientes

=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 de ID de cuenta (columna A), excepto que en lugar de usar LEFT(), usa MID, que es útil para extraer texto desde cualquier lugar que no esté al principio hasta el final, o no hasta el final. La sintaxis es:

MID(texto, start_num, num_chars)

donde texto es la cadena a buscar (C7)

start_num es la posición para comenzar. Usamos...

SEARCH(" - ",C7)+3

...porque queremos comenzar después de " - ".

Recuerde de arriba, SEARCH(" - ",C7) da la posición donde comienza " - ". Dado que " - " tiene 3 caracteres de largo, sumamos 3 para obtener la posición donde comienza la descripción de la cuenta.

Finalmente, el LEN(C7) da la longitud de la cadena en la celda C7, para asegurarnos de que la tomamos hasta el final.

Subtotales

Las siguientes son las fórmulas para las celdas G1 y H1:

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

Usamos 50000 porque este archivo en particular tiene menos de 50000 filas. Cambie eso a un número lo suficientemente grande para acomodar el número total de filas que tiene su archivo. Añadimos estas fórmulas para verificar que el archivo esté balanceado cuando hacemos el paso final de filtrado para mantener solo las filas de transacción (a continuación).

Filtrar para mantener solo las filas de transacción

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

Copiar y pegar valores en un nuevo libro de trabajo

Notando que todas las fechas son las filas de transacción, seleccione solo las fechas:

Tenga en cuenta que los resultados de nuestras fórmulas SUBTOTAL() en las celdas G1 y H1 muestran que las transacciones totales están en balance (¡yay!)

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, presionar Ctrl-Shift-End para llegar al final del archivo y ajustar la posición según sea necesario), luego copiar, luego pegar valores especiales en un nuevo libro de trabajo

Finalmente, en el nuevo libro de trabajo, asegúrese de formatear la columna Fecha como Fecha en formato YYYY-MM-DD (presione Ctrl-1 en PC o ⌘1 en Mac):

¡Y ahora tenemos un archivo bellamente formateado, listo para importar a 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 usar una fórmula más simple solo para desagrupar, sin fraccionar al mismo tiempo, puede simplemente agregar una columna de cuenta que extraerá el valor de la columna B si contiene " - ", de lo contrario, extraerá el valor encontrado arriba:

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

Luego proceda con los pasos descritos anteriormente para filtrar, copiar y pegar en un nuevo libro de trabajo.

Comience a importar el archivo de 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 Columna dividida. Aprenda cómo fraccionar columnas.

Fraccionamiento de cuentas más complicado

Aprenda cómo construir una fórmula para fraccionar un número de cuenta.

¿Algo más en tu mente? Chatea con nosotros o envíe una solicitud para más ayuda.