Encuentre la última aparición de un delimitador para recuperar la subcuenta más baja de Quickbooks en Excel

En esta publicación, exploraremos un enfoque basado en fórmulas que no usa VBA para encontrar la última aparición de un delimitador y lo usaremos para recuperar la subcuenta más baja de una lista de cuentas de Quickbooks en Excel.

Cuando estaba investigando para un proyecto que tenía hace un par de años, me sorprendieron las muchas formas creativas en que los usuarios de Excel han abordado esta tarea. Existen varios métodos bien documentados, cada uno de ellos creativo y hermoso.

Esta publicación recorre la lógica de una de esas soluciones y explora cada una de las funciones que utiliza. Si solo desea la respuesta, sin toda la narrativa, salte hasta el final para ver la fórmula y el libro de trabajo de muestra.

Objetivo

Aunque esta técnica se puede aplicar en muchas situaciones, la ilustración utilizada en esta publicación es que recibimos una lista de cuentas exportada de Quickbooks y necesitamos dividir el nombre completo de la cuenta para encontrar la subcuenta más baja.

Quickbooks normalmente usa dos puntos (:) entre cuentas, como se muestra en la siguiente captura de pantalla:

Necesitamos una sola columna que contenga la subcuenta más baja. Para la fila 11, la fórmula debería devolver la cuenta. Pero, para la fila 12, la fórmula debería devolver la cuenta de Ingresos por Diseño. Para la fila 15, la fórmula debe devolver la cuenta de Ingresos del trabajo subcontratado. Necesitamos la subcuenta más baja, independientemente del número de subcuentas en una fila determinada.

Esto es exactamente lo que haremos con una fórmula sofisticada. Pero antes de hacer eso, analicemos algunas características que ofrecen enfoques alternativos.

Alternativas

Hay al menos dos funciones que podríamos utilizar para ayudar con esta tarea. Una característica es la conversión de texto a columnas (Datos Texto a columnas). Esto funciona bien cuando hay la misma cantidad de subcuentas en cada fila. Sin embargo, si algunas cuentas tienen 2 subcuentas y otras tienen 3 subcuentas, la subcuenta más baja no aparecerá en la misma columna.

Si utiliza Excel 2013, la función FlashFill es fácil de usar y puede ser una alternativa que funcionaría bien. Consulte la publicación FlashFill para obtener más información.

Si esta tarea fuera parte de un proyecto único, probablemente usaría FlashFill y listo. Es rápido y fácil de usar. Sin embargo, si este fuera un libro de trabajo de uso recurrente, entonces probablemente consideraría usar un enfoque basado en fórmulas.

Verá, preferimos eliminar todos los pasos manuales de un libro de uso recurrente. Si se utilizará un libro de trabajo en períodos futuros, preferimos utilizar un enfoque de fórmula en lugar de un enfoque de funciones manual. De esa manera, cuando pegamos los datos actualizados, como la exportación de la cuenta actualizada, las fórmulas prepararán automáticamente los datos para nuestro uso sin ningún paso manual adicional. Nos encanta almacenar nuestros datos en tablas porque se expanden y completan automáticamente las columnas calculadas. Entonces, incluso si hay más filas que el último período, la fórmula se completará para incluir cualquier fila nueva.

Para eliminar un paso manual de un libro de trabajo de uso recurrente, invertiré una cantidad significativa de tiempo tratando de ajustar la fórmula. Esta inversión inicial genera atractivos dividendos de eficiencia en cada período posterior. El enfoque presentado en esta publicación es una de esas inversiones. Puede que al principio lleve más tiempo que usar FlashFill, pero la ventaja es que en cada período subsiguiente puedes simplemente pegar y listo.

Acercarse

Hay varias formas excelentes de realizar esta tarea utilizando fórmulas. Tuve problemas para determinar quién debería recibir el crédito por la idea utilizada a continuación, ya que este enfoque se presenta en muchas páginas web. Pero seas quien seas, tu enfoque es brillante… ¡gracias!

Queremos configurar una fórmula que recupere la subcuenta más baja. Si solo hubiera un delimitador en una fila, como en la fila 12 anterior, entonces esta tarea es muy fácil. Sin embargo, cuando hay más de un delimitador, como en las filas 15 y 16, esta tarea se vuelve más difícil. Cuando intenta escribir una fórmula coherente que pueda completar en todo un rango y desea que funcione en celdas sin delimitador (p. ej., fila 11), celdas con un delimitador (p. ej., fila 12), celdas con dos delimitadores (p. ej., fila 15) y celdas con cualquier número de delimitadores, la tarea de repente se vuelve desafiante.

Si encontrar un único delimitador es fácil, pero encontrar el último delimitador cuando hay varios delimitadores es difícil, podemos simplificar nuestra vida reemplazando el último delimitador (el de la derecha) con un carácter único.

Para ilustrar esta idea, simplifiquemos los nombres de nuestras cuentas por un momento para que sea más fácil de visualizar. Aquí hay un nombre de cuenta completo tal como nos llega en la exportación:

SegmentoA: SegmentoB: SegmentoC: SegmentoD

Al utilizar las funciones integradas de Excel, es difícil recuperar SegmentD porque hay varios delimitadores (:). Pero, si tuviéramos una manera de identificar o etiquetar de alguna manera el último delimitador y reemplazarlo con un carácter único, como $, entonces nuestro trabajo es fácil. Idealmente, podríamos convertir la cuenta completa a algo como esto:

SegmentoA: SegmentoB: SegmentoC $ SegmentoD

Ahora podríamos pedirle a Excel que busque la etiqueta ($) y luego recupere los valores a la derecha de ella.

Esto es esencialmente lo que nuestra fórmula logrará. Antes de escribir la fórmula, tomemos un momento para explorar las funciones que utilizará.

Funciones

Las funciones que necesitaremos en nuestra fórmula son:

• LEN: devuelve el número de caracteres en una cadena de texto
• SUSTITUIR – reemplaza un carácter por otro carácter
• DERECHA: devuelve caracteres de la derecha de una cadena de texto
• ENCONTRAR: devuelve el número de posición de un carácter en una cadena de texto; error si no se encuentra
• IFERROR: reemplaza un error con un valor específico

Tomémoslos en el orden en que los necesitaremos. Mientras lee estos pasos, recuerde que el objetivo final es identificar el delimitador final, reemplazarlo con una etiqueta única y luego recuperar los caracteres que se encuentran a su derecha.

Paso 1: calcular el número de delimitadores

Nuestra primera tarea es calcular cuántos delimitadores (:) aparecen dentro de la cuenta completa. Por ejemplo, necesitamos devolver 3 para la siguiente cuenta:

SegmentoA:SegmentoB:SegmentoC:SegmentoD

Si de alguna manera pudiéramos contar el número de caracteres en la cuenta completa y luego restarle el número de caracteres en una cadena de texto que excluye los delimitadores, entonces lo tendríamos.

Por ejemplo, si pudiéramos contar el número de caracteres en la cadena de texto de arriba y luego restarle el número de caracteres en la cadena de texto de abajo, tendríamos:

SegmentASegmentoBSegmentoCSegmentoD

Para lograr esto primero necesitamos la función LEN. La función LEN devuelve la longitud de una cadena de texto, lo que básicamente significa que cuenta la cantidad de caracteres.

Suponiendo que la celda A1 contenga la cuenta completa (SegmentoA:SegmentoB:SegmentoC:SegmentoD), la siguiente función devolvería el número de caracteres, 35:

=LARGO(A1)

Ahora que tenemos la longitud total, debemos restarle la longitud de una cadena de texto que no tiene delimitadores. La función que necesitaremos para lograr esto es la función SUSTITUIR. Esta función devuelve una cadena de texto después de haber reemplazado los caracteres seleccionados con los caracteres deseados.

Por ejemplo, podríamos usar la función para reemplazar cada aparición del delimitador con cualquier otro carácter. O bien, podríamos reemplazar cada delimitador con una cadena de texto vacía, esencialmente eliminando todos los delimitadores del resultado. Suponiendo que nuestra cuenta completa esté en A1, podríamos devolver SegmentASegmentBSegmentCSegmentD usando lo siguiente:

=SUSTITUIR(A1,":","")

Esta función encontrará cada aparición del delimitador (:) y la reemplazará con una cadena de texto vacía “”, eliminando efectivamente todos los delimitadores.

Volviendo a la tarea que nos ocupa, calculamos el número de delimitadores en A1 tomando la longitud de A1 y restando la longitud de una cadena de texto a la que se le han eliminado los delimitadores. Esto se logra utilizando lo siguiente:

=LARGO(A1)-LARGO(SUSTITUIR(A1,":",""))

La fórmula toma la longitud de A1, que es 35, y le resta la longitud de una cadena de texto sin delimitadores, o 32, lo que deja el número de delimitadores, 3.

Ahora que tenemos el número total de delimitadores en la cuenta, podemos pasar al siguiente paso, reemplazando el último delimitador con un carácter único.

Paso 2: Reemplace el delimitador final con una etiqueta de carácter único

Necesitamos reemplazar el delimitador final con un carácter único que luego podamos usar como marcador, para que podamos recuperar los caracteres a su derecha.

¿Se te ocurre alguna función que podamos utilizar para sustituir un carácter por otro? Sí, la función SUSTITUIR. Sin embargo, un pequeño detalle. La función SUSTITUIR como se usó anteriormente reemplaza todas las apariciones del carácter coincidente. Sin embargo, la función SUSTITUIR tiene un cuarto argumento opcional que nos permite reemplazar una aparición específica del carácter. El cuarto argumento, conocido como num_instancia, nos permite reemplazar solo el delimitador final con nuestro carácter único.

Para que podamos saber el número de instancia del delimitador final, necesitamos saber cuántos hay. Afortunadamente, determinamos cuántos delimitadores hay en el paso anterior. Si descubrimos que hay 3 delimitadores en la cadena de texto, podemos reemplazar la tercera instancia (última) con un carácter único, como $, usando la siguiente fórmula:

=SUSTITUIR(A1,":","$",3)

En lugar de reemplazar todos los delimitadores, esta función solo reemplaza la tercera instancia o aparición del delimitador y devuelve la siguiente cadena de texto:

SegmentoA:SegmentoB:SegmentoC$SegmentoD

Como queremos completar la fórmula, necesitaremos reemplazar el número entero 3 con las funciones subyacentes que lo calculan, de la siguiente manera:

=SUSTITUIR(A1,":","$",LARGO(A1)-LARGO(SUSTITUIR(A1,":","")))

¿Hasta ahora, todo bien? Bien, pasemos al siguiente paso.

Paso 3: correcto

Ahora que hemos etiquetado el delimitador final con un carácter único, podemos simplemente extraer los valores que se encuentran a su derecha.

Podemos usar la función DERECHA para recuperar caracteres del lado derecho de una cadena de texto. La función requiere que le digamos la cantidad de caracteres que se encuentran más a la derecha para recuperar. La cadena de texto que hemos creado hasta ahora es:

SegmentoA:SegmentoB:SegmentoC$SegmentoD

Queremos usar la función DERECHA para recuperar SegmentD, por lo que podríamos usar lo siguiente:

=DERECHA(A1,8)

Esto devolvería los 8 caracteres correctos de la cadena. Dado que los nombres de cuentas tienen una cantidad variable de caracteres, no podemos usar un valor entero para el segundo argumento, 8. En su lugar, necesitaremos usar funciones para calcular la cantidad de caracteres.

Afortunadamente, hemos etiquetado la posición con un carácter único ($) que podemos usar para determinar la cantidad de caracteres en el nombre de la cuenta. Para calcular la cantidad de caracteres que se encuentran a la derecha de la etiqueta, calculamos la cantidad total de caracteres en la cadena de texto y luego restamos el número de posición de la etiqueta.

Ya sabemos cómo calcular el número total de caracteres en una cadena de texto usando la función LEN, así que descubramos cómo determinar el número de posición de la etiqueta.

Podemos usar la función ENCONTRAR para devolver el número de posición de nuestra etiqueta de carácter única $. Supongamos por un momento que almacenamos el siguiente valor en la celda A1:

SegmentoA:SegmentoB:SegmentoC$SegmentoD

Podríamos calcular la posición de la etiqueta usando la siguiente función:

=BUSCAR("$",A1)

Esta fórmula devuelve 27, lo que nos indica que la etiqueta $ aparece como el carácter número 27 dentro de la cadena de texto.

Si restamos la longitud total, 35, de la posición de la etiqueta, 27, nos queda el número de caracteres a la derecha de la etiqueta, 8.

Por lo tanto, calculamos el número de caracteres a la derecha de la etiqueta usando lo siguiente:

=LARGO(A1)-BUSCAR("$",A1)

Ahora que sabemos cuántos caracteres hay en el nombre de la cuenta, podemos usar la función DERECHA para devolverlo:

=DERECHA(A1,LEN(A1)-BUSCAR("$",A1)

Esto devolverá los caracteres a la derecha de la etiqueta, que en nuestro caso es el nombre de la cuenta SegmentD.

Nuestra cadena de texto etiquetada no está almacenada en A1, ha sido calculada y, por lo tanto, la fórmula real que necesitamos, que combina todos los pasos hasta ahora, es la siguiente:

=DERECHA(A1,LARGO(A1)-BUSCAR("$",SUSTITUIR(A1,":","$",LARGO(A1)-LARGO(SUSTITUIR(A1,":","")))))

Paso 4: trampa de errores

En el caso de que no se encuentre un delimitador, la función ENCONTRAR devolverá un error. Cuando no se encuentra ningún delimitador en la celda, queremos devolver el valor de la celda original en lugar de un error. Por lo tanto, incluiremos una función IFERROR alrededor de todo. La función SIERROR nos permite sustituir un valor cuando una función devuelve un error.

Por ejemplo, si la función BUSCAR no puede encontrar un delimitador, podríamos sustituir el valor de la celda original por el error con la siguiente fórmula:

=SIERROR(BUSCAR("$",A1),A1)

Envolver una función IFERROR alrededor de nuestra fórmula da como resultado lo siguiente:

=SIERROR(DERECHA(A1,LEN(A1)-BUSCAR("$",SUSTITUIR(A1,":","$",LARGO(A1)-LEN(SUSTITUIR(A1,":",""))) )),A1)

Esta fórmula final funciona para recuperar los valores a la derecha de la última aparición del delimitador. En cierto modo, es como realizar una búsqueda de derecha a izquierda. Pero, dado que la función ENCONTRAR de Excel no admite la búsqueda desde la derecha, utilizamos la fórmula anterior.

Cuando usamos una fórmula similar con nuestra lista de cuentas de Quickbooks, podemos completar una columna que contiene la subcuenta más baja, como se muestra a continuación:

Para obtener información más detallada sobre las funciones o sus argumentos, consulte el sistema de ayuda de Excel.

¿Solo números de cuenta?

¿Podríamos colocar los números de cuenta en su propia columna? Claro, ahora que tenemos una columna que es la subcuenta más baja, podemos dividirla en columnas de número y nombre de cuenta con una variedad de enfoques diferentes.

Podríamos usar la función IZQUIERDA para recuperar los 5 caracteres izquierdos. Por supuesto, esto supone que todos los números de cuenta tienen 5 caracteres. Si tienen una longitud variable, necesitaríamos confiar en un delimitador y usar la función ENCONTRAR.

Dado que la función IZQUIERDA devuelve una cadena de texto, podríamos envolverla con la función VALOR para convertirla en un valor numérico si es necesario.

Podríamos usar la función DERECHA junto con la función LEN para colocar los nombres de las cuentas en su propia columna.

Otras Consideraciones

Aunque Quickbooks usa el delimitador de dos puntos, esta fórmula podría modificarse para hacerla más universal configurando una celda que almacene el carácter delimitador y luego nombrándolo. De manera similar, podríamos colocar el carácter de etiqueta en una celda y asignarle un nombre también. Si nombramos la celda delimitadora d y la etiqueta de la celda del carácter de etiqueta, la fórmula actualizada sería:

= SI ERROR (DERECHA (A1, LARGO (A1) - ENCONTRAR (etiqueta, SUSTITUIR (A1, d, etiqueta, LARGO (A1) - LARGO (SUSTITUIR (A1, d, ""))))), A1)

Conclusión

El enfoque presentado en esta publicación ciertamente utiliza una fórmula sofisticada. Descubrir e implementar una fórmula como esta tiene sentido para los libros de uso recurrente, ya que podrá mejorar su productividad en períodos futuros.

El libro de trabajo de muestra que se proporciona a continuación incluye una fórmula de trabajo como referencia:

Cuenta más baja

Si tiene un enfoque alternativo que prefiera, publíquelo en los comentarios a continuación. ¡Me encantaría conocerlo!