Identificar proveedor en asientos contables con BUSCARX y BYROW

El problema: el proveedor está en otra línea del mismo asiento

Juan compartió en el grupo una fórmula de John Vergara diciendo que le había "salvado la vida laboral". Y cuando ves el problema que resuelve, se entiende la reacción.

En un diario contable, un asiento no es una fila: son varias. Una línea para el gasto, otra para el IVA, otra para el proveedor. Todas comparten el mismo número de asiento, pero el nombre del proveedor solo aparece en su línea. Si quieres analizar los gastos por proveedor, te encuentras con que la línea de gasto no sabe a quién se le compró.

La estructura del fichero es la típica del plan contable español:

• Las cuentas que empiezan por 6 o 7 son gastos e ingresos.
• Las que empiezan por 400, 410 o 430 son proveedores y clientes.

El reto: rellenar automáticamente el nombre del proveedor en cada línea de gasto, buscándolo en la línea de proveedor del mismo asiento.

La fórmula de John, en una sola expresión

`` =SI( BYROW(IZQUIERDA(E2:E11)1 = grupoGastos; O); BUSCARX( B2:B11; B2:B11 / BYROW(IZQUIERDA(E2:E11;3)1 = grupoProveedores; O); F2:F11 ); "" ) ``

Donde grupoGastos es una constante matricial horizontal con los valores 6 y 7, y grupoProveedores otra con 400, 410 y 430. En Excel se escriben entre llaves, con el separador de columnas que corresponda a tu configuración regional (más sobre esto al final).

La fórmula tiene dos ideas dentro, y las dos merecen la pena por separado.

Idea 1: comparación perpendicular para "empieza por 6 o por 7"

Mira la primera parte: BYROW(IZQUIERDA(E2:E11)*1 = grupoGastos; O).

IZQUIERDA sin segundo argumento devuelve el primer carácter de cada cuenta, y la multiplicación por 1 lo convierte en número. Eso da una columna de dígitos.

Al compararla con una constante matricial horizontal (los valores 6 y 7 en fila), Excel hace lo que se llama comparación perpendicular: cruza la columna con la fila y genera una matriz de dos columnas. La primera dice si cada cuenta empieza por 6; la segunda, si empieza por 7.

Ahí entra BYROW con la función O: recorre esa matriz fila a fila y colapsa cada par a un único valor lógico. Verdadero si alguna de las dos comparaciones acertó. En una sola expresión, sin anidar condicionales, tienes "empieza por 6 o por 7".

Y lo mejor: ampliar la lista no cambia la fórmula. Si mañana hay que incluir también el grupo 2, añades el valor a la constante y ya está.

Idea 2: la división que borra las filas que no interesan

La segunda parte es la que hace levantar una ceja: BUSCARX(B2:B11; B2:B11 / BYROW(...); F2:F11).

Se busca el número de asiento dentro de la propia columna de números de asiento. Suena absurdo hasta que ves qué le pasa a esa columna antes de buscar en ella.

El divisor es la misma comparación perpendicular de antes, pero aplicada a los tres primeros caracteres y contra los códigos de proveedor. Devuelve verdadero en las filas de proveedor y falso en el resto. Y en Excel, falso vale cero.

Así que al dividir la columna de asientos por esa máscara ocurre lo siguiente:

• En las filas de proveedor, se divide entre 1 y el número de asiento se conserva intacto.
• En todas las demás, se divide entre cero y sale un error de división.

BUSCARX ignora los errores al buscar. Resultado: solo puede encontrar coincidencia en las filas de proveedor, y como busca el número de asiento, encuentra la línea de proveedor de ese asiento concreto. La tercera columna del BUSCARX devuelve el nombre.

Es el truco clásico de "dividir para filtrar", el mismo que se usaba con la función de búsqueda antigua, pero aplicado aquí con una máscara construida al vuelo.

El SI exterior remata: si la línea no es de gasto o ingreso, devuelve cadena vacía en lugar de ensuciar la columna.

El fichero del caso incluye además un segundo escenario donde las cuentas de pago empiezan por 5 en vez de por 6 o 7, y la única modificación necesaria es cambiar los valores de la constante. Buena señal de que la fórmula está bien planteada.

El detalle regional que pilla a todo el mundo

En el hilo, Héctor Mendoza replicó la fórmula y descubrió algo que da muchos quebraderos de cabeza al compartir fórmulas: el separador de las constantes matriciales horizontales cambia según la configuración regional. En España es la barra invertida; en buena parte de Latinoamérica es la coma.

Si copias una fórmula con constantes matriciales de un compañero de otro país y te da error, casi seguro que es esto y no la lógica. Cambia el separador y funciona.

Funciones clave

• IZQUIERDA — extrae los primeros caracteres de la cuenta para clasificarla por grupo contable.
• Comparación perpendicular — cruzar una columna con una constante horizontal genera una matriz de todas las combinaciones; la base para probar varios criterios de golpe.
• BYROW con O — colapsa esa matriz a una columna de valores lógicos, uno por fila.
• BUSCARX — busca ignorando errores, que es lo que permite el truco de la división.
• División por una máscara lógica — convierte en error todo lo que no cumple la condición, dejando visible solo lo que interesa.

Conclusión

Lo bonito de esta fórmula es que no usa nada exótico: IZQUIERDA, BYROW, BUSCARX y una división. Lo que la hace especial es combinarlas para que la búsqueda solo pueda mirar donde tú quieres.

El patrón es reutilizable mucho más allá de la contabilidad. Siempre que tengas que buscar un valor dentro de un subconjunto de filas definido por una condición, la división por máscara te ahorra montar columnas auxiliares. Casos como este, traídos por gente que se pelea a diario con diarios contables reales, son el pan de cada día en la comunidad de InflueXcel.

Más casos con estas funciones

• BYROW
• BUSCARX

Más contenido de Excel en InflueXcel

• Filtrado inteligente de asientos contables con BUSCARX y BYROW Caso
  Nuevo caso de contabilidad en la comunidad: dado un listado de asientos con múltiples cuentas (gastos 6xx/7xx e IVA 4xx), se necesita extrae
• Unpivot de asientos contables con conceptos múltiples Caso
  Juan plantea un problema de contabilidad con una estructura de datos compleja: tiene una tabla de asientos contables donde cada fila contien
• Buscar prefijos de longitud variable en otra columna: BYROW, MAP, REGEX y COINCIDIRX Caso
  Interesante problema planteado por un miembro: tiene una columna A con ~1.200 referencias de longitud variable y una columna C con ~276 text
• Categorizar automáticamente conceptos con BUSCARX REGEX y MAP Caso
  Un miembro de la comunidad tiene una lista de compra (conceptos como "leche entera", "pan integral", etc.) y una tabla de categorías donde c
• BUSCARX explicado fácil Tutorial
  Desde la sintaxis básica hasta trucos avanzados como expresiones regulares y cruce de rangos. Además, aprenderás a manejar errores y a usar
• Buscar todos los valores coincidentes cuando BUSCARX solo devuelve uno Caso
  Juan se encuentra con un problema frecuente: tiene una tabla de búsqueda con claves duplicadas (A aparece varias veces con valores distintos
• BUSCARX con valor devuelto dinámico: elige la columna con un botón o un segmentador Caso
  Una integrante de la comunidad planteó un reto muy habitual al trabajar con tablas de varias columnas: tiene una lista de municipios con cua
• ¿MAP o BYROW? Descubre cuándo usar cada una Tutorial
  ¿MAP o BYROW? Descubre cuándo usar cada una y cómo estas funciones pueden transformar tu forma de trabajar en Excel. En este vídeo aprenderá
• Limitación de BYCOL: cómo obtener múltiples valores por columna Caso
  Surge una duda sobre por qué una fórmula aparentemente simple con BYCOL genera error. El usuario intenta obtener los 5 mayores valores de ca
• Crear una función LAMBDA reutilizable para agrupar ventas por punto de venta Caso
  Un miembro de la comunidad comparte un fichero con una función personalizada llamada xMGP_SUG registrada en el Administrador de nombres. El