manejo de tablas en excel
La posibilidad de convertir un rango en Tabla es uno de los mayores avances de Excel de los últimos años. Como veremos, las Tablas nos facilitan la gestión y el análisis de datos. Son varias e importantes las ventajas.
Referencias a las Tablas
Antes de las Tablas, cualquier referencia a un rango tenía que modificarse manualmente si cambiaba el número de filas o columnas de dicho rango o cambiaba su posición dentro de la hoja. Nada de esto debe preocuparte ya si utilizas Tablas, las referencias se actualizan automáticamente. Esta importante mejora es aplicable tanto a las referencias que hagas en el código VBA de tus macros como en cualquier lugar donde utilices fórmulas con referencias: celdas, formatos, validación de datos, etc.
Para ello, Excel utiliza unas referencias especiales que mejoran la claridad y comprensión de las fórmulas, haciendo su diseño y mantenimiento mucho más sencillo e intuitivo. Además, el menú contextual nos va ayudando a escribir las referencias:
referenciando tablas en excel.
Para convertir un rango en Tabla basta con hacer clic dentro del rango y pulsar la combinación de teclas: CTRL+T. También puedes hacerlo desde el menú INICIO -> Dar formato como tabla.
Referencias como las siguientes son las que verás en tus fórmulas:
- Para sumar los datos de la columna Ventas: =SUMA(MiTabla[Ventas])
- Para referirnos a la celda de la fila actual en la columna Ventas: =MiTabla[@Ventas]
- Importe de la fila Total de la columna Ventas: =MiTabla[[#Totales];[Ventas]]
- El nombre del encabezado de la columna Ventas, sería: =MiTabla[[#Encabezados];[Ventas]]
- Una combinación de 2 ó más columnas: =MiTabla[Pais];MiTabla[Ventas]
- Toda la tabla, incluidos los encabezados de columna, datos y totales: =MiTabla[#Todas]
- Sólo datos: =MiTabla[#Datos]
Trabajar con Tablas en VBA
Pero es sobre todo cuando desarrollas en VBA cuando descubres todas las ventajas. Vamos a ver ejemplos del código que necesitarás para trabajar con ellas:
Crear un Tabla
El siguiente código crea una Tabla en un rango, o da formato Tabla al rango:
'Crea una Tabla en Excel VBA
Hoja1.ListObjects.Add(xlSrcRange, Range("A1:D10"), xlYes).Name = "MiTabla"
Eliminar las filas de una Tabla
Para eliminar filas de una Tabla podemos hacer:
Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects("MiTabla")
'Para borrar una columna determinada
tbl.ListColumns(3).Delete
'Para borrar una fila determinada
tbl.ListRows(6).Delete
Para limpiar la tabla eliminando todas las filas de datos puedes utilizar la siguiente instrucción. Ten en cuenta que si en la Tabla no existe ninguna fila que borrar arrojará un error, por lo que es conveniente utilizarlo con un condicional como el del ejemplo:
'Elimina todas las filas de MiTabla
If ActiveSheet.ListObjects("MiTabla").ListRows.Count > 0 Then
ActiveSheet.Range("MiTabla").Delete
End If
Insertar filas y columnas
Para insertar en una posición determinada:
'Insertar una columna en una posición específica
Hoja1.ListObjects("MiTabla").ListColumns.Add Position:=5
'Insertar una fila en la fila 3
Hoja1.ListObjects("MiTabla").ListRows.Add(3)
Para insertar una columna a la derecha o una fila al final:
'Insertar en una columna a la derecha
Hoja1.ListObjects("MiTabla").ListColumns.Add
'Añadir una fila al final
Hoja1.ListObjects("MiTabla").ListRows.Add AlwaysInsert:= True
Añadir fila de totales
'Incluye las cabeceras y totales
Hoja1.ListObjects("MiTabla").Range.Select
'Incluye sólo los datos, sin cabeceras ni totales
Hoja1.ListObjects("MiTabla").DataBodyRange.Select
Seleccionar elementos de la tabla
Selección de una Tabla completa:
'Incluye las cabeceras y totales
Hoja1.ListObjects("MiTabla").Range.Select
'Incluye sólo los datos, sin cabeceras ni totales
Hoja1.ListObjects("MiTabla").DataBodyRange.Select
Para seleccionar una determinada columna de una Tabla:
'Para seleccionar la segunda columna de la Tabla, sólo los datos
'sin cabecera:
Hoja1.ListObjects("MiTabla").ListColumns(2).DataBodyRange.Select
'Si quieres incluir también la cabecera:
Hoja1.ListObjects("MiTabla").ListColumns(1).Range.Select
Para seleccionar la tercera fila de la Tabla:
Hoja1.ListObjects("MiTabla").ListRows(3).Range.Select
Para seleccionar una determinada celda dentro de la tabla, por ejemplo la fila 5 y columna 2:
Hoja1.ListObjects("MiTabla").DataBodyRange(5, 2).Select
Selección de la fila de encabezados:
Hoja1.ListObjects("MiTabla").HeaderRowRange.Select
Selección de la fila de totales:
Hoja1.ListObjects("MiTabla").TotalsRowRange.Select
Rellenar un ComboBox o ListBox con datos de una Tabla
'Para llevar datos de una Tabla a un ComboBox Me.cbxMiComboBox.RowSource = "MiTabla" 'Para llevar datos de una Tabla a un ListBox Me.lbxMiListBox.RowSource = "MiTabla"
Rellenar un Array con datos de una Tabla
Sub CargarArray()
Dim MiArray() As Variant
Dim tbl As Range
Set tbl = Hoja1.Range("MiTabla")
MiArray = tbl
End Sub
Fuente original: http://blogexcelyvba.com/tablas-excel/
Ejemplo practico:
Se tiene una tabla de pedidos de productos como se muestra a continuación, su nombre es “tblPedidos”, que esta en la hoja4 :
Se intenta filtrar por “Nro” de pedido para luego enviar los item visibles a otra tabla que la hara de facturador. pero solo se enviara los datos filtrados, como se aprecia en la siguiente imagen, el nombre de esta tabla es “tblFactura”, que esta en la hoja 8:
La solucion la implementariamos en un userform, como el que sigue:
Todo el codigo estaria en el boton de “Realizar Factura” y seria el siguiente:
Private Sub CommandButton1_Click()
Dim FilasActuales As Integer
Dim FilasActualesPedido As Integer
Hoja8.Activate
Hoja8.Range("NroPedidoFactura").Value = ComboBox1.Value
FilasActuales = Hoja8.ListObjects("tblFactura").ListRows.Count
Hoja4.ListObjects("tblPedidos").Range.AutoFilter Field:=1, Criteria1:=ComboBox1.Value
Application.ScreenUpdating = True
FilasActualesPedido = Hoja4.Range("tblPedidos[Nro]").SpecialCells(xlCellTypeVisible).Count
lblfilas.Caption = FilasActualesPedido
For i = 1 To FilasActualesPedido
FilasActuales = FilasActuales + 1
Hoja8.ListObjects("tblFactura").ListRows.Add AlwaysInsert:=True
Hoja8.ListObjects("tblFactura").DataBodyRange(FilasActuales, 1).Value = Hoja4.ListObjects("tblPedidos").DataBodyRange.SpecialCells(xlCellTypeVisible).Cells(i, 1)
Hoja8.ListObjects("tblFactura").DataBodyRange(FilasActuales, 2).Value = Hoja4.ListObjects("tblPedidos").DataBodyRange.SpecialCells(xlCellTypeVisible).Cells(i, 5)
Hoja8.ListObjects("tblFactura").DataBodyRange(FilasActuales, 3).Value = Hoja4.ListObjects("tblPedidos").DataBodyRange.SpecialCells(xlCellTypeVisible).Cells(i, 7)
Hoja8.ListObjects("tblFactura").DataBodyRange(FilasActuales, 4).Value = Hoja4.ListObjects("tblPedidos").DataBodyRange.SpecialCells(xlCellTypeVisible).Cells(i, 8)
Hoja8.ListObjects("tblFactura").DataBodyRange(FilasActuales, 5).Value = Hoja4.ListObjects("tblPedidos").DataBodyRange.SpecialCells(xlCellTypeVisible).Cells(i, 8)
Next i
Hoja8.ListObjects("tblFactura").DataBodyRange(FilasActuales, 5).Select
End Sub