Buscar un valor de una matriz de 3 dimensiones en excel
Tengo una tabla en Excel de facturas por cliente y fecha a la que debo asignar a cada una de esas líneas el comercial correspondiente; y ese dato hay que encontrarlo en una matriz con tres dimensiones: Cliente, comercial y Fecha de baja. Las dos primeras dimensiones son claras, la tercera (fecha de baja) representa la fecha a partir de la cual deja de ser el comercial asignado para ese cliente, con lo que en la línea siguiente de la matriz figurará el mismo cliente con otro comercial. Como es un poco lioso describirlo, os "pinto" la matriz:
CLIENTE COMERCIAL FECHA_BAJA
A JUAN 01/04/2016
A PACO -
B LUIS 01/07/2016
C PACO -
D MANUEL -
E LUIS 01/07/2016
E MANUEL -
... Donde la "-" representa que no tiene fecha de baja ese comercial para esa empresa, es decir, sigue siendo el mismo. Esa matriz no es fija, y puede tener más líneas (nuevos comerciales, nuevas bajas..) y se irá alimentando constantemente a través de una consulta ODBC de una base de datos Oracle a través de Excel.
Los datos de facturación vienen de otra consulta ODBC, (igual que la matriz expuesta); y se trata de cruzarlas para poder representar el histórico de facturación por comercial y cliente, de tal forma que, por ejemplo, JUAN tendrá asignadas ventas del cliente A hasta el 31/03/2016, y las que se hayan generado después se deben asignar a PACO.
Para rizar el rizo, decir que el listado de facturación viene con el campo fecha, que es la fecha de la factura, y que NO TIENE PORQUE COINCIDIR con la fechas de la matriz. Ejemplo de tabla de datos de facturación:
CLIENTE FECHA IMPORTE
A 31/03/2016 150
A 30/04/2016 40
El resultado correcto debe ser que la formula de como resultado JUAN en el primer registro y PACO en el segundo.
1 respuesta
Te propongo una solución.. no ideal, pero sin entrar en programar es lo que he podido sacar:
En primer lugar necesitarás tres columnas formuladas en la consulta de los comerciales:
La columna E transforma el nombre del cliente a un número (funcionará siempre y cuando los datos estén ordenados por cliente); en E2 se pone un 1, en E3 se formula esto:
=SI(B3=B2;E2;E2+1)
.. Y se arrastra hacia abajo (más optimo y si tienes muchos registros hacerlo con fórmulas de matriz).
En la columna F transformamos las fechas, formulamos en F2:
=SI(E2=E1;D1;1)
... y se arrastra.
Finalmente la columna A, es simplemente concatenar número de cliente con fecha tratada, se formula en A2 esto:
=CONCATENAR(E2;F2)
... y se arrastra.
Entonces la tabla de facturación queda así:
Se formula en M2 así:
=SIERROR(CONSULTAV(CONCATENAR(CONSULTAV(J2;$B$2:$E$5;4;FALSO);K2);$A$2:$C$5;3;VERDADERO);"SIN COMERCIAL")
Fíjate que hay un par de deficiencias: la factura del cliente B del 02/08/2016 está fura de rango, debería avisar, y por otro lado tiene la limitación ya indicada que los datos deben estar correctamente ordenados como en tu ejemplo, caso contrario no funcionará.
¡Gracias!
Me es válido y funciona perfectamente, ya que mis datos de facturación puedo importarlos ordenando por cliente; así que muchas gracias!
