Conciliando cuentas con Excel por Héctor Miguel

Esta aportación es un ensayo escrito por Héctor Miguel, conocido colaborador de los foros de Excel, donde durante años ha resuelto las dudas de una innumerable cantidad de personas. Con autorización de Héctor me permito compartirles este gran trabajo.

Conciliaciones en Excel

Conciliar (cifras y documentos) es un término aplicado administrativamente a la revisión de dos documentos cuyos registros deben ser equilibrados y correspondientes entre sí, ejemplo: los cheques y movimientos relacionados en una cuenta contable versus un reporte de la cuenta de cheques emitido por el banco.

Para este caso de cotejos “uno a uno”, la conciliación es sencilla ya que los reportes generalmente se vinculan por el número de referencia al cheque (expedido por una entidad y presentado al cobro en el banco). Como resultado de esta revisión, señalar en uno de los documentos (o en ambos) permite identificar si quedan “partidas a conciliación”, es decir, los movimientos que se encuentran registrados (in)debidamente en uno de los documentos pero no en el otro, para corregir (donde y como corresponda) y equilibrar los documentos.

Una conciliación se puede volver +/- compleja, cuando el caso es de cotejos “uno a varios”, donde se requiere de una inequívoca identificación (p.ej.) de un importe abonado en el estado de cuenta del banco, a cuantos importes de cargo (y a cuales) se corresponde en el estado de cuenta de un cliente y similar en el caso de un pago a proveedor por dos o más facturas.

En este caso, para efectuar una búsqueda de importes (“sumandos”) para identificar aquellos cuya suma sea igual a la del importe pretendido (“objetivo”), es conveniente trasladar a algún modelo o tabla (de preferencia identificando antes -si es posible-) SOLAMENTE aquellos registros que sean viables para su cotejo, es decir, si en el estado de cuenta del cliente algunos registros han sido plenamente identificados como ya liquidados, NO TIENE SENTIDO su traslado.

También es importante trasladar a esa tabla o modelo algunos “elementos de análisis” adicionales (fechas y referencias a documentos e identificación del cliente, etc.) que permitan una identificación inequívoca de que la “colección de sumandos” que se encuentre puede ser confirmada como “la correcta”.

Conciliaciones “uno a uno”

Si el cotejo “uno a uno” no es tan sencillo como conciliar cheques entre reportes contabilidad-banco, o donde la referencia (número de cheque) es un poco más engorrosa, se pueden utilizar Tablas Dinámicas haciendo un “arreglo” previo de los reportes a conciliar, para no ahondar mucho en este asunto (digamos) “sencillo”, puedes consultar un artículo (con archivo-ejemplo para su descarga) desde el blog de Alejandro Quiceno.

Conciliaciones “uno a varios”

Este caso empieza a ponerse interesante, cuando consideramos que la ecuación para saber el número de combinaciones posibles en las que hay que buscar cuales suman un objetivo es: k = 2ⁿ (incluyendo la posibilidad de que con ninguna cifra o combinación se alcance el objetivo) siendo ‘n’ el número de “sumandos” a considerar, motivo por el que se sugiere trasladar al modelo SOLO aquellos registros viables para su cotejo.

Un ejemplo de esta ecuación; si suponemos 3 cantidades a considerar para sumar un objetivo “c”, que nos indica que las combinaciones posibles son: 2³=8 y que puedes visualizar (p.ej. en base 2) como sigue:

Si las cifras que pones a consideración del modelo fueran 95, la ecuación resultante (2⁹⁵) concluye que las combinaciones a evaluar son del orden de… (Imagina un ‘4’ y 28 ceros por delante).

Una vez encontradas ‘n’ combinaciones posibles de valores con los que se satisface la suma del importe buscado, será necesario tener en cuenta los otros “elementos de análisis” (fechas y referencias a documentos e identificación del cliente, etc.) para validar cuál de entre las combinaciones encontradas (si es que alguna) ES “la correcta”, puesto que aun si solo se encontrara UNA combinación entre todas las cifras consideradas, sin un análisis de los elementos de soporte adicionales, NADA puede asegurar que la combinación localizada ERA/ES aplicable para considerar “conciliados” tales registros.

En este ensayo se analiza una alternativa (por la vía de programación) para hacer conciliaciones “uno a varios” para lo que es conveniente tener en cuenta los lineamientos mencionados en los próximos párrafos.

Los códigos trabajan sobre la “región actual” (.CurrentRegion) en un modelo sobre el que se han trasladado los “registros viables” (previa depuración) en dos tablas distribuidas conforme a la siguiente muestra:

Figura 1. Los registros a consultar.

1. La tabla izquierda contiene los registros del auxiliar contable y la derecha los del extracto del banco

2. La primera fila contiene los títulos y el número de columnas puede ser distinto siempre y cuando…

a. La primera columna de las tablas (A y J) contiene las fechas de los registros en orden ascendente

b. La penúltima columna de las tablas (G y R) contiene los importes a considerar (sumandos u objetivos)

c. La última columna de cada tabla (H y S) la utilizan los procedimientos para Auto-Filtrar los resultados

3. Debe existir (al menos) una columna -y una fila al final- de separación entre las tablas y otros datos (I y T)

Las demás columnas deben ser representativas y contener datos de los otros “elementos de análisis” (obviamente, los datos aleatorios que contiene el ejemplo son meramente “ilustrativos”).

La celda [U1] tiene incrustadas 3 imágenes cuyos objetivos son:

Iniciar la macro para la localización de sumandos y registrar las combinaciones encontradas.

Aplicar Auto-Filtros y mostrar la combinación de sumandos seleccionada de la lista obtenida.

Borrar los Auto-Filtros.

Las opciones para los Auto-Filtros no requieren de mayores explicaciones. Al iniciar la macro de localización se muestran 5 diálogos/pasos (cancelables en cualquier momento, antes de seguir con el procedimiento):

Para seleccionar la celda con el importe “objetivo” que se busca concertar con la combinación de algunos importes de la tabla “contraria” a aquella donde se encuentra la celda seleccionada (si seleccionas de la tabla del banco, en el siguiente paso deberás seleccionar alguna celda de la tabla con el auxiliar contable -o viceversa-).

En este paso, debes seleccionar alguna celda (cualquiera) que se encuentre dentro del rango de la “otra” tabla (si en el paso anterior seleccionaste de la tabla del extracto bancario, en este paso seleccionas de la tabla del auxiliar contable -o viceversa-).

Ahora se necesita seleccionar alguna celda en un área “libre” de tu hoja de cálculo, en la que se depositará una lista con todas las combinaciones posibles encontradas por el procedimiento (si las hay). Recuerda mantener una columna/fila de separación entre las tablas (para no obstaculizar otras búsquedas para otros objetivos).

Puesto que las búsquedas pueden requerir que el procedimiento se ejecute durante un tiempo prolongado (recuerda la ecuación), en los siguientes dos pasos tienes opciones para restringir el proceso (en número de combinaciones a buscar o en tiempo de ejecución):

En este paso tienes oportunidad de indicar si prefieres que el procedimiento se interrumpa luego de alcanzar un número específico de combinaciones posibles, introduciendo un valor mayor a 0 (cero) o, si prefieres que “vaya por todas”, indica un 0 (cero), puedes usar esta restricción en combinación con el siguiente paso…

En este último paso puedes limitar el tiempo de ejecución para el proceso, indicando EN SEGUNDOS el tiempo máximo que estás dispuesto a “esperar”. Si no has trasladado a las tablas muchos/demasiados importes a escrutinio, indica un 0 (cero) y “siéntate a esperar” (considera tus opciones de combinación de estos dos últimos pasos).

Al finalizar el proceso obtendrás la lista con las combinaciones posibles encontradas, algo +/- como la siguiente ilustración, donde (para el caso de este ejemplo) se puede apreciar que…

a) en el paso 1, la cifra-objetivo fue seleccionada de la celda [R18] (de la tabla del extracto bancario)

b) en el paso 2 se eligió cualquier celda de la tabla del auxiliar contable (sumandos en la columna “G”)

c) en el paso 3 fue seleccionada la celda [U3] para depositar los resultados

d) al proceso se le dio un tiempo límite de 15 segundos

encontró y listó 10 combinaciones posibles con los importes de la columna “G” (tabla auxiliar contable)

Como dato adicional, haciendo esta misma selección, pero dándole un tiempo más amplio (90 segundos) devolvió la nada despreciable cantidad de CIENTO VEINTE combinaciones posibles, por ello (insisto en que) ES MUY IMPORTANTE incluir en el modelo los demás “elementos de análisis” para evitar “dar por conciliados” (incluso “desaparecer”) registros ambiguos, confusos o (definitivamente) improcedentes.

En caso de que para los pasos 1 y 2 llegues a seleccionar celdas de la misma tabla para objetivo y sumandos, o que decidas cancelar (en cualquier momento) la ejecución del procedimiento obtendrás avisos como…

El archivo de ejemplo contiene (aleatoriamente inventados) 222 datos/registros en la tabla del auxiliar contable y 134 en la tabla del extracto bancario, por si quieres calcular (con la ecuación conocida) el número de combinaciones posibles en las que habría que evaluar cuales sumandos satisfacen un objetivo (?).

Para reducir tiempo de proceso (en la medida de lo posible), el procedimiento determina, con base en la fecha correspondiente de la cifra/celda seleccionada para el importe-objetivo del paso 1, un “rango de fechas” en donde buscar posibles combinaciones de la taba “contraria”, es decir, si el objetivo es del extracto bancario, el rango de fechas en la tabla auxiliar-contable será desde fecha(s) anterior(es) a la fecha del objetivo y hasta (inclusive) la fecha del objetivo, si por el contrario, el objetivo es de la tabla del auxiliar-contable, el rango de fechas en la tabla del extracto bancario será a partir de la fecha del objetivo en adelante.

Lo anterior obedece a que no se espera que (p.ej.) una cuenta por cobrar sea abonada con anticipación a la fecha del documento de cobro (pedido, aviso de embarque, factura, etc.).

Terminado el procedimiento, en el rango donde se haya depositado una lista con las combinaciones posibles, podrás seleccionar (una a la vez) las celdas con las direcciones de cada colección de sumandos (ver imagen de resultados) y tales referencias de celda serán identificadas por formatos condicionales para que se pueda verificar (después de considerar los otros “elementos de análisis”) cuál de las combinaciones reportadas es “la correcta” (si alguna lo fuera).

Ejemplo: seleccionando la celda [U4] y aplicando los Auto-Filtros obtienes de la tabla auxiliar-contable…

Figura 2. Fondo amarillo: los registros aplicables en la tabla para los sumandos de la colección seleccionada [U4]. Fórmula en columna “I”: [I2] =0+ESNUMERO(HALLAR(DIRECCION(FILA(G2),COLUMNA(G2),4)&”+”,posibles)) ó igual para la columna “S”.

Figura 3. Fondo rojo: los registros aplicables en la tabla con el objetivo en cuestión (podría ocultarse al aplicar filtros). Fórmula para el formato condicional: =CELDA(“address”,$G2)=buscado ó ver imagen con los formatos condicionales.

Después de haber seleccionado celdas del listado obtenido (una a la vez), NO CAMBIES la selección de la “celda activa”. Si quieres “navegar” por la hoja para ver aquellas a las que se ha aplicado formato condicional, usa las barras o la rueda de desplazamiento con el puntero (mouse) o aplica los Auto-Filtros.

Nota: la navegación por la hoja se verá ralentizada por efecto de las funciones utilizadas en nombres para su aplicación en las reglas de formato condicional (en los dos casos) para los registros en cada tabla:

Nota: el uso de la función =CELDA(… en vez de funciones como DIRECCION(), FILA(), COLUMNA(), etc. para evitar caer en referencias circulares al seleccionar celdas de la colección de sumandos del listado obtenido (y… mi sistema usa coma para separar argumentos).

Los formatos condicionales aplicados para identificar sumandos y objetivo.

Notarás que cada celda de la lista con la colección de sumandos tiene una presentación/formato (casi) “listo” para que compruebes que la suma de las celdas referidas es igual al objetivo buscado, con un estilo de: “g2+g3+g5+g17+g52” o sea, {+} la dirección de las celdas con las que se ha compuesto la suma.

Puedes editar “la celda” y anteponer el signo ‘=’ para realizar la operación de comprobación, o puedes copiar la celda con la serie de referencias a otro lugar y hacer la edición allí, o puedes usar el método “Evaluate” para que VBA solicite a Excel la operación de suma necesaria, o puedes definir otro nombre con la macro-función del (viejo?) Excel v4: =EVALUAR(… (o =EVALUATE(… si tu Excel es en inglés), o… (se te ocurre algo más?)

Este ensayo se acompaña con un archivo de Excel para que puedas (primero) analizar y comprobar lo aquí expuesto y estés en condiciones de adaptar (después) a las necesidades específicas de alguna situación real que pudieras encarar.

El algoritmo que se encarga de hacer correr los ciclos que sean necesarios para el armado de la colección de posibles sumandos (una función que se llama y ejecuta de manera recursiva), está basado en el desarrollo publicado en este artículo de Tushar Mehta (yo solo le he puesto “una buena mexicanizada” por lo que) aprovechando que TM ya ha puesto los comentarios pertinentes en su artículo (aunque en inglés), me he permitido omitir comentar “que hace” la mencionada función.

Un detalle que me parece digno de mención, es el hecho de que TM haya logrado “hacer tanto con tan poco” (sintetizado en no más de 25 líneas de código entre dos funciones y la declaración de las variables “globales”).

Por el resto de los códigos utilizados en el libro de ejemplo, las líneas con macro-instrucciones (en su gran mayoría) son para los efectos de “maquillaje” (puro y duro), por lo que solo he agregado unos pocos comentarios, confiando en que no será difícil entender el propósito y accionar de las líneas no comentadas.

Código vba

Ubicación: Hoja1

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    [a1].Calculate
End Sub

Ubicación: Módulo procedimientos

Option Private Module
'
Sub BorraFiltro()
    ActiveSheet.AutoFilterMode = False
End Sub
'
Sub FiltraSumandos()
    BorraFiltro
    Application.ScreenUpdating = False
    If ActiveCell.HasFormula Or InStr(ActiveCell.Value, "+") = 0 Then Exit Sub
    With Range(Split(ActiveCell, "+")(0)).CurrentRegion
        .AutoFilter Field:=.Columns.Count, Criteria1:=1
    End With
    [a1].Calculate
End Sub

Ubicación: Módulo vTM

Option Base 1
' declaracion de constantes de texto para los mensajes en cada paso del procedimiento
Private Const _
        curia As Double = 0.00000001, _
        txtComb As String = " combinacion(es) encontrada(s)", _
        tForm As String = "hh:mm:ss", _
        vForm As String = "#,##0.#######", _
        paso1 As String = "selecciona la celda con la suma a buscar", _
        paso2 As String = "selecciona UNA CELDA del rango-tabla con los sumandos", _
        canPaso2 As String = "las tablas del objetivo y los sumandos NO DEBEN ser la misma !!!", _
        canFecha As String = "no hay rango de fechas para la busqueda !!!", _
        paso3 As String = "selecciona la celda para devolver los resultados", _
        paso4 As String = "indica el numero de combinaciones a devolver", _
        paso4a As String = "para buscar TODAS, indica un 0 (cero) !!!", _
        paso5 As String = "indica el tiempo maximo para el proceso (EN SEGUNDOS)", _
        paso5a As String = "para busquedas SIN limite, indica un 0 (cero) !!!", _
        canTexto As String = "operacion cancelada por el usuario !!!", _
        canTitulo As String = "en que quedamos ? <\º|º/> !!!"
' declaracion de variables "globales" de tipo Variant (por omision)
Private objetivo, lista(), valores()
' declaracion de variables "globales" de tipo especifico
Private nComb As Long, col As String, fila0 As Long, ini As Single, basta As Integer, tiempo As Boolean
' funcion para ir ampliando las referencias de los sumandos encontrados para cada coleccion de "posibles"
Private Function ampRef(refAct, refNva) As String
    ampRef = IIf(refAct = "", refNva, refAct & "+" & refNva)
End Function
' funcion "recursiva" | es la que se encarga de la verificacion/evaluacion en cada (intento de) combinacion
' fuente: http://www.tushar-mehta.com/excel/templates/match_values/index.html#VBA_multiple_combinations
Private Function evaluaCifra(nVal As Long, sumaActual, refAct As String)
Dim n As Long
    For n = nVal To UBound(valores)
        If tiempo Then If (Timer - ini) > basta Then Exit Function
        If Abs((sumaActual + valores(n)) - objetivo) <= curia Then
            lista(UBound(lista)) = ampRef(refAct, col & n + fila0)
            If nComb <> 0 Then
                If UBound(lista) > nComb Then Exit Function
            End If
            ReDim Preserve lista(UBound(lista) + 1)
        ElseIf sumaActual + valores(n) > objetivo + curia Then
        ElseIf nVal < UBound(valores) Then
            evaluaCifra n + 1, sumaActual + valores(n), ampRef(refAct, col & n + fila0)
            If nComb <> 0 Then If UBound(lista) > nComb Then Exit Function
        Else
        End If
    Next
End Function
' procedimiento que se encarga de "llevar de la mano" al usuario (paso a paso)
Private Sub ListaSumandos()
' declaracion de las variables especificas de este procedimiento
Dim importe As Range, x As String, tablaOrigen As Range, fechaLimite As Long, _
    sumandos As Range, tablaFuente As Range, y As String, filaUno As Long, _
    xFilas As Long, destino As Range, tmp
    ' este tratamiento de errores es por si se cancela al establecer referencias de tipo Range _
      y se resetea previo al inicio del procedimiento "efectivo"
    On Error Resume Next
    tiempo = False
    ' pasos 1 a 5 (antes del procedimiento "efectivo")
    ' OJO: NO inhibas el refresco de la pantalla previo al uso de los Application.InputBox <=
    Set importe = Application.InputBox(paso1, "paso 1 de 5", "", , , , , 8)(1)
    If importe Is Nothing Then GoTo cancela
    ' se da formato al importe del objetivo para su presentacion al final
    x = Format(importe.Value, vForm)
    Set tablaOrigen = importe.CurrentRegion
    ' se obtiene la fecha del objetivo para establecer limites a los sumandos a considerar
    fechaLimite = CLng(importe.Offset(, -tablaOrigen.Columns.Count + 2))
    Set sumandos = Application.InputBox(paso2, "paso 2 de 5", "", , , , , 8)(1)
    If sumandos Is Nothing Then GoTo cancela
    Set tablaFuente = sumandos.CurrentRegion
    ' se comprueba que las tablas (objetivo y sumandos) NO sean la misma
    If tablaFuente.Address = tablaOrigen.Address Then
        CreateObject("wscript.shell").PopUp canPaso2, 2, "cancelando ..."
        GoTo termina
    End If
    If tablaFuente.Cells(2, 1) > fechaLimite Then
        CreateObject("wscript.shell").PopUp canFecha, 2, "cancelando ..."
        GoTo termina
    End If
    Set destino = Application.InputBox(paso3, "paso 3 de 5", "", , , , , 8)(1)
    If destino Is Nothing Then GoTo cancela
    ' se pregunta si se establece un maximo al numero de combinaciones a procesar
    tmp = Trim(InputBox(paso4 & vbCr & paso4a, "paso 4 de 5"))
    If tmp = "" Then GoTo cancela
    nComb = Val(tmp)
    ' se pregunta si se le pone "tiempo limite" al procedimiento
    tmp = Trim(InputBox(paso5 & vbCr & paso5a, "paso 5 de 5"))
    If tmp = "" Then GoTo cancela
    basta = Val(tmp)
    tiempo = (basta <> 0)
    ' cancelamos la prevencion de errores
    On Error GoTo 0
    ' ya no es necesario inhibir el refresco de la pantalla
    Application.ScreenUpdating = False
    With tablaFuente
        If tablaOrigen.Column > .Column Then
            ' si el objetivo es "bancario", el rango de fechas (en el auxiliar) es desde
            'la fecha inicial hasta la fecha del objetivo
            Set sumandos = .Offset(1, .Columns.Count - 2).Resize(Application.Match(fechaLimite, .Columns(1)) - 1, 1)
            GoTo define
        End If
        filaUno = Application.Match(fechaLimite - 1, .Columns(1))
        xFilas = .Rows.Count - filaUno
        ' si el objetivo es contable, el rango de fechas (en el bancario) es a partir de la fecha del objetivo
        Set sumandos = .Offset(filaUno, .Columns.Count - 2).Resize(xFilas, 1)
    End With
define:
    objetivo = Abs(importe.Value)
    valores = Evaluate("transpose(" & sumandos.Address & ")")
    col = LCase(sumandos(1).Address(, 0))
    col = Left(col, InStr(col, "$") - 1)
    fila0 = sumandos.Row - 1
    ' ok, todo listo para iniciar el procedimiento "efectivo"
    ReDim lista(1)
    ' comenzamos por tomar el tiempo en el "punto de partida"
    ini = Timer
    ' y nos lanzamos a la funcion recursiva de la cual volveremos a este punto hasta que termine
    evaluaCifra 1, 0, ""
    ' obtenemos el numero de ciclos que llevo a cabo la funcion recursiva
    tmp = UBound(lista) - 1
    ' aplicamos un formato al tiempo de duracion del procedimiento completo
    y = Format((Timer - ini) / 86400, tForm)
    ' depositamos en la celda elegida los resultados de la busqueda
    destino.Value = tmp & txtComb & IIf(tmp = 0, "", " en " & y) & " para " & x & " en " & importe.Address
    ' si NO hubo exito en la composicion, terminamos...
    If tmp = 0 Then GoTo termina
    ' depositamos la coleccion de combinaciones de sumandos encontrada
    destino.Offset(1).Resize(tmp).Value = Application.Transpose(lista)
    ' saltamos al final de este procedimiento (limpiar las variables de objeto)
    GoTo termina
cancela:
    CreateObject("wscript.shell").PopUp canTexto, 2, canTitulo
termina:
    Set importe = Nothing
    Set tablaOrigen = Nothing
    Set sumandos = Nothing
    Set tablaFuente = Nothing
    Set destino = Nothing
End Sub

Anexos

:: Descarga el ejemplo conciliaciones en excel.rar