EXCELeINFO

Factura en Excel Parte 4.1 – Consultar factura histórica usando Tablas dinámicas

by · 6 September, 2018

 Factura en Excel Parte 4.1 - Consultar factura histórica

PREVIO: FACTURA EN EXCEL PARTE 3

Esta cuarta parte de nuestro Proyecto Factura en Excel estará dividida en dos partes, ya que fue tan extenso el video que creí que ameritaba dejarla en dos partes. En esta primera parte veremos cómo hacer la consulta de facturas ya guardadas y reimprimirlas o simplemente consultar el detalle de los productos facturados. Para devolver el detalle de códigos facturados de la factura elegida lo haremos mediante fórmulas que consulten a una Tabla dinámica, y esas fórmulas estarán en una hoja que será réplica de la hoja donde capturamos los datos de la factura, sin embargo esta réplica nos servirá para consultar facturas. En el siguiente video desarrollaremos una macro VBA para que la consulta de facturas sea automatizada.

Convertir el detalle de facturas a Tabla

Como comenté al principio, para consultar el detalle de códigos de una factura lo haremos mediante una Tabla dinámica y aplicando filtros en esta Tabla dinámica de la factura elegida. Vamos a necesitar convertir nuestro rango de Detalle de facturas a Tabla.

  • Elegimos la hoja Detalle de facturas.
  • Seleccionamos el rango y presionamos [Control] + [T].
  • Le ponemos como nombre a la Tabla tblDetalle.

Convertimos el Detalle de facturas a Tabla.

Figura 1. Convertimos el Detalle de facturas a Tabla.

Creando Tabla dinámica en base al Detalle de facturas

Ahora crearemos una Tabla dinámica la cual contendrá el dato de FACTURA, CÓDIGO y CANTIDAD. Armaremos la Tabla dinámica en Formato Tabular para posteriormente aplicar filtros por Número de factura y tomar de ahí los códigos y cantidades. Seguimos los siguientes pasos para armar la Tabla dinámica.

  • Seleccionamos la Tabla recién creada, tblDetalle.
  • Nos vamos a la pestaña Insertar > Tabla dinámica.
  • Elegimos que se inserte en una Nueva hoja de cálculo.
  • Ya en la Tabla dinámica movemos los campos de CONSECUTIVO, CODIGO y CANTIDAD a la sección Filas.
  • Elegimos la Tabla dinámica y nos vamos a la pestaña Diseño > Diseño de informe > Mostrar en formato tabular.
  • Ahora de nuevo en la pestaña Diseño > Subtotales > No mostrar subtotales.
  • Pestaña Diseño > Totales generales > Desactivado para filas y columnas.
  • Pestaña Diseño > Diseño de informe > Repetir todas las etiquetas de elementos.
  • Le cambiamos el nombre a la hoja a TD-Consulta-Factura.

Ver Video Factura en Excel Parte 4.1

Suscríbete al canal de EXCELeINFO en YouTube para aprender más de Excel y macros.

Haciendo los pasos anteriores tenemos la siguiente Tabla dinámica como resultado.

Tabla dinámica en Formato tabular.

Figura 2. Tabla dinámica en Formato tabular.

Replicando la hoja Factura y creando la hoja Consulta factura

La hoja Factura nos sirve para dar de alta códigos. Vamos a copiar esta hoja para crear otra carátula, pero ahora para consultar facturas.

  • Damos clic derecho en la hoja Factura y elegimos Mover o copiar.
  • Marcamos la opción Crear una copia.
  • Le cambiamos de nombre a Consulta-Factura.

En la nueva hoja, en la celda A8 ingresamos la función BUSCARV para buscar el cliente al cual pertenece la factura a consultar. Usamos la siguiente fórmula:

=BUSCARV(E4,tblDetalle[[#Todo],[CONSECUTIVO]:[IMPORTE]],2,0)

En la celda B13, de la columna CÓDIGO ingresamos una fórmula que buscará el número de factura que ingresemos en la celda E4 en la hoja TD-Consulta-Factura en la celda A4. Si el número de factura se encuentra en la columna A de la Tabla dinámica, entonces traeremos el valor del código. Usamos la siguiente fórmula:

=SI($E$4=’TD-Consulta-Factura’!A4,’TD-Consulta-Factura’!B4,”-“)

En la celda D13 de la columna CANTIDAD usamos la siguiente fórmula que hará lo mismo que la anterior, pero ahora devolveremos la cantidad:

=SI($E$4=’TD-Consulta-Factura’!A4,’TD-Consulta-Factura’!C4,”-“)

Actualizando la Tabla dinámica al momento de capturar una nueva Factura

Cuando registramos una nueva Factura es importante que luego de grabar el detalle de la factura en la hoja Detalle de facturas, se actualice la Tabla dinámica para que automáticamente tome los datos de la nueva factura y no tener que hacer la actualización de manera manual. Para ello usaremos la siguiente línea en la macro GuardarFactura:

ThisWorkbook.Sheets(“TD-Consulta-Factura”).PivotTables(“tdDetalle”).PivotCache.Refresh

Haciendo pruebas de Consulta de factura de manera manual

Para realizar la consulta de facturas históricas seguimos los siguientes pasos:

  • En la hoja Consulta-Factura ingresamos una factura válida en la celda E4.
  • En la hoja TD-Consulta-Factura aplicamos un Filtro en la Tabla dinámica mostrando sólo el Consecutivo correspondiente a la factura ingresada en el paso anterior.
  • Vemos ahora en la hoja Consulta-Factura que se devuelven los valores del cliente factura y todos los Códigos y cantidades correspondientes a la factura elegida.

Aplicamos el filtro para mostrar una factura.

Figura 3. Aplicamos el filtro para mostrar una factura.

Ingresamos la factura 6 y vemos el detalla de Códigos y cantidades.

Figura 4. Ingresamos la factura 6 y vemos el detalla de Códigos y cantidades.

Nota: En el siguiente tutorial desarrollaremos una macro VBA para que el proceso de Consulta de facturas, actualización y filtrado de la Tabla dinámica sea automatizado sin intervención del usuario.

Código VBA de la macro

La siguiente macro es la macro que nos guarda los datos de la Factura en la hoja Detalle de facturas.

Option Explicit

'EXCELeINFO
'MVP Sergio Alejandro Campos
'http://www.exceleinfo.com
'https://www.youtube.com/user/sergioacamposh
'http://blogs.itpro.es/exceleinfo

Sub GuardarFactura()
Dim NombreHoja As String
Dim HojaDestino As Range
Dim NuevaFila As Integer
Dim FilasFactura As Integer
Dim i As Integer
Dim j As Integer
Dim NumFactura As Integer
Dim Ruta As String
Dim Respuesta As Integer

NombreHoja = "Detalle de facturas"
FilasFactura = Application.WorksheetFunction.CountA(Range("Factura[CÓDIGO]"))
NumFactura = ThisWorkbook.Sheets("Factura").Range("E4").Value
'''''''''''''''''''''''''''''''''''''
If FilasFactura = 0 Or Range("valCliente").Value = "" Then _
MsgBox "Debes elegir un ciente e ingresar un código", vbExclamation, "EXCELeINFO": Exit Sub

ThisWorkbook.ActiveSheet.PrintOut Copies:=1

 'Propiedad FileDialog
        With Application.FileDialog(msoFileDialogFolderPicker)
            .InitialFileName = Application.DefaultFilePath & " \ "
            .Title = "EXCELeINFO - Seleccionar carpeta"
            .Show
            If .SelectedItems.Count = 0 Then
            Else
                Ruta = .SelectedItems(1)

                    MsgBox "Guardando en PDF Factura '" & NumFactura & "'. Presione Aceptar para continuar...", _
                    vbInformation, "EXCELeINFO"
                    
                    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                    Ruta & "\" & "Factura-" & NumFactura & ".pdf", Quality:=xlQualityStandard, _
                    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
            End If
        End With

'''''''''''''''''''''''''''''''''''''
With ThisWorkbook.Sheets(NombreHoja)

    For i = 1 To FilasFactura
        Set HojaDestino = ThisWorkbook.Sheets(NombreHoja).Range("A1").CurrentRegion
        NuevaFila = HojaDestino.Rows.Count + 1
        .Cells(NuevaFila, 1).Value = Date
        .Cells(NuevaFila, 2).Value = NumFactura
        .Cells(NuevaFila, 3).Value = Range("valCliente").Value
            
            For j = 1 To 4
                .Cells(NuevaFila, j + 3).Value = ThisWorkbook.Sheets("Factura").Cells(12 + i, 1 + j)
            Next j
    Next i

End With

MsgBox "Alta exitosa", vbInformation, "EXCELeINFO"

ThisWorkbook.Sheets("TD-Consulta-Factura").PivotTables("tdDetalle").PivotCache.Refresh

'''''''''''''''''''''''''''''''''''''
Respuesta = MsgBox("Deseas borrar los datos?", vbYesNo + vbQuestion, "EXCELeINFO")

    If Respuesta = vbYes Then
        With ThisWorkbook.Sheets("Factura")
            .Range("valCliente").ClearContents
            .Range("B13:B23").ClearContents
            .Range("d13:d23").ClearContents
    End With
    Else
    End If

'''''''''''''''''''''''''''''''''''''
End Sub

Descarga el archivo de ejemplo

Descargar el ejemplo Factura en Excel Parte 4.1 – Consultar factura histórica usando Tablas dinámicas.zip

Si te gustó este tutorial por favor regístrate en nuestra Lista de correo y Suscríbete a nuestro canal de YouTube para que estés siempre enterado de lo nuevo que publicamos.

Nota: En el siguiente tutorial desarrollaremos una macro VBA para que el proceso de Consulta de facturas, actualización y filtrado de la Tabla dinámica sea automatizado sin intervención del usuario.

SIGUIENTE: FACTURA EN EXCEL PARTE 4.2

Tags:

You may also like...