Las tablas en Excel

En muchas ocasiones tenemos datos en nuestras hojas en Excel y en base a esos datos tenemos fórmulas, tablas dinámicas u otras. Normalmente lo tenemos hecho tipo tablas, algo así por ejemplo:

Si para ese caso quisiéramos saber el total de ventas tendríamos que usar una fórmula de este tipo:

=SUMA(D2:D15)

Del mismo modo para crear una tabla dinámica, elegiríamos el rango “A1:D15”. Pero ¿qué pasa en esos casos cuando agregamos un registro? Es decir, si agregamos datos a la fila 16. Pues dicho registro no estará entre los datos que muestra la tabla dinámica y la fórmula anterior no tomará en cuenta el nuevo dato de la columna, es decir la celda “D16”. Podríamos, por ejemplo, usar rangos dinámicos con nombre (usando por ejemplo la función DESREF) pero existe una forma mucho más simple: Usar las tablas de Excel.

Las tablas son un conjunto de filas y columnas con datos y que Excel puede relacionar entre sí. La primera fila de una tabla contiene los encabezados de cada columna y eso ayudará a identificarlas. Las tablas facilitan el trabajo de datos con Excel.

Para convertir un rango en “Tabla” de Excel, colocamos el cursor en cualquier celda del rango en donde tenemos nuestros datos, luego elegimos, en la cinta de opciones, la pestaña “Insertar” y en el grupo “Tablas” damos click al botón “Tabla”, Excel seleccionará todo nuestro rango de datos y nos mostrará un cuadro de dialogo llamado “Crear tabla”. Ahí verificamos que todos los datos estén seleccionados y elegimos, de ser el caso, que “La tabla tiene encabezados”.

Los datos pasaran a tener esta apariencia:

Puedes cambiar los colores/estilo del diseño, el nombre de la tabla, exportar los datos, etc., haciendo uso de las “Herramientas de tabla”.

Si tenemos los datos como tabla, podríamos usar fórmulas de este tipo:

=SUMAR.SI(Tabla1[Local],"A",Tabla1[Venta])

=SUMA(Tabla1[Venta])

De agregar más datos a la tabla, si comenzamos por la primera fila sin datos inmediata inferior a dicha tabla y usamos las mismas columnas, automáticamente el Excel hará que la tabla extienda su rango. De esa forma las fórmulas seguirán actuando sobre los datos si necesidad de que tengamos que volver a modificarlas. Lo mismo ocurrirá, por ejemplo, con las tablas dinámicas cuyo origen de datos sea una tabla. Además de eso, las tablas, en cada columna cuentan con filtros que podemos usar de ser necesario:

Y eso es todo en esta ocasión. Hasta la próxima.

Abraham Valencia

Trabajar, desde Excel-VBA, con otros programas de Microsoft

Cuando programas, una de las grandes ventajas de trabajar con Excel y toda la suite de Microsoft es la compatibilidad entre su lenguaje Visual Basic for Applications (VBA). De este modo podemos trabajar con más de uno de sus programas a la vez y/o interactuar entre ellos solo debemos aprender, tal como en el caso de Excel, los objetos, propiedades y otros de cada programa.

Una forma de abrir o crear archivos desde el VBA es usar la función “CreateObject”. La sintaxis de dicho método es la siguiente:

CreateObject (clase, nombre del servidor)

En donde “clase” es el nombre de la aplicación y/o la clase del objeto para crear y “nombre del servidor” es justamente el nombre del servidor de red en donde se crea el objeto. Si el nombre de servidor se deja en blanco, el objeto se creará en el equipo desde donde se usa la función.

Vamos a dejar de lado lo del servidor y centrarnos en la creación de objeto en nuestro propio equipo. Por ejemplo, vamos a suponer que queremos crear un archivo de Word nuevo entonces lo primero que vamos a hacer es declarar una variable con la que nombraremos a nuestro objeto “Word”, después crearemos el objeto, lo haremos visible (por defecto se crea el objeto como no visible) y agregaremos un documento nuevo:

Sub CreandoWord()
Dim MiappWord As Object 
Set MiappWord = CreateObject("Word.Application") 
MiappWord.Documents.Add 
MiappWord.Visible = True 
Set MiappWord = Nothing 
End Sub

La línea “MiappWord.Documents.Add”, que es la que agrega el documento nuevo de Word, puede ser obtenida fácilmente con la grabadora de macros de Word, aunque lo menciono solo como referencia ya que ese sería un tema ajeno a Excel.

Supongamos ahora lo que queremos es abrir un archivo ya existente, pues usaríamos algo como esto:

Sub CreandoWord() 
Dim MiappWord As Object 
Set MiappWord = CreateObject("Word.Application") 
MiappWord.Documents.Open ("D:\Filatelia\Actividades CAF 2018.docx") 
MiappWord.Visible = True 
Set MiappWord = Nothing 
End Sub

Una forma en que el editor de VBA nos puede “ayudar” con los objetos, propiedades y métodos de, en este caso, Word, es si declaramos la referencia respectiva:

No olviden que el 15.0 puede variar dependiendo de la versión de Office que estén usando. Luego cambiamos la declaración de variable a tipo “Word.Application” y tendremos el siguiente resultado:

También podemos, por ejemplo, abrir una presentación hecha en Power Point del siguiente modo:

Sub AbrirPPT() 
Dim appPpt As Object 
Set appPpt = CreateObject("Powerpoint.Application") 
appPpt.Presentations.Open Filename:=ThisWorkbook.Path & "\Hola.pptx" 
appPpt.ActivePresentation.SlideShowSettings.Run 
appPpt.Visible = True 
Set appPpt = Nothing 
End Sub 

En este caso el archivo de Power Point está en la misma carpeta que el archivo Excel. La línea que abren dicho archivo y la que activa la presentación, son parte del manejo de objetos y métodos de dicho programa pero lamentablemente en este caso dicho programa ya no cuenta con grabadora de macros que nos ayude pues Microsoft decidió retirarla desde la versión de Office 2007.

Es casi el mismo procedimiento para Infopath, Publisher, etc., pero no olvidemos que puede servir para otros programas como por ejemplo Internet Explorer:

Sub Guglear() 
Dim ie As ObjectDim 
PaginaWeb As String 
PaginaWeb = "www.google.com.pe" 
Set ie = CreateObject("InternetExplorer.Application") 
ie.Navigate PaginaWeb 
Do     
 DoEvents 
Loop Until ie.readyState = 4 
ie.Visible = True 
Set ie = Nothing 
End Sub  

Y eso es todo en esta ocasión. Hasta la próxima.

Abraham Valencia

En memoria de "Chip" Pearson

No conocí personalmente a Charles "Chip" H. Pearson, pero soy uno de los miles que recurrió a su página web para absolver alguna duda sobre VBA de Excel. Si mal no recuerdo la primera vez que lo leí fue el año 2006 y desde ese entonces use muchos de los enlaces a sus ejemplos y explicaciones para ayudar a guiar a muchas personas en este apasionante mundo del Excel que compartíamos.

Fue nombrado en 16 oportunidades, por Microsoft, como Most Value Professional (MVP) de Excel, galardón sin duda más que merecido. Creo que es uno de los expertos en Excel anglo parlantes más conocido por nosotros los hispano parlantes. Hace muy pocos días quise hace runa consulta en su web, como tantas veces hice, y estaba “fuera de línea”, realmente pensé que estaba migrando de “Hosting” o actualizando su web, o haciendo alguna copia de seguridad, no sé, algo de eso, jamás pensé en otra cosa. Hoy a través del Facebook de mi querido amigo Sergio Alejandro Campos (MVP Excel – México) me enteré que el gran “Chip” sufrió un accidente automovilístico hace unos días y que el día 19 de este mes falleció producto de las heridas sufridas. Una gran pérdida para su familia, pero también para esta gran familia mundial del Excel de la que muchos somos parte. Que en paz descanse el gran “CPearson”.

DESREF: Función "poderosa" y poco conocida

La función DESREF de Excel es, desde mi punto de vista, quizá una de las que podría ser más útil en cosas habituales pero aun así no es de las más usadas. Pero ¿qué hace y/o cómo trabaja dicha función? Microsoft define dicha función del siguiente modo: “Devuelve una referencia a un rango que es un número de filas y de columnas de una celda o rango de celdas. La referencia devuelta puede ser una celda o un rango de celdas. Puede especificar el número de filas y el número de columnas a devolver”.

¿Se entendió esa definición? ¿No tanto? Bueno, continuemos hasta entenderla completamente. Microsoft también nos dice que la sintaxis de la función es la siguiente:

DESREF (ref, filas, columnas, [alto], [ancho])

Asimismo, la definición de los argumentos de la función son los siguientes:

Nombre	Descripción
Ref	Argumento obligatorio. Es la referencia en la que desea basar la desviación. La referencia debe referirse a una celda o un rango de celdas que son adyacentes entre ellas.
Filas	Argumento obligatorio. Es el número de filas, hacia arriba o hacia abajo, al que se desea hacer referencia tomando como base la celda del argumento “Ref”. Filas hacia abajo se señala en positivo y hacia arriba en negativo.
Columnas	Argumento obligatorio. Es el número de columnas, hacia la derecha o izquierda, al que desea que haga referencia tomando como base la celda del argumento “Ref”. Columnas hacia la derecha se señala en positivo y hacia la izquierda en negativo.
Alto	Argumento opcional. Es el alto, en número de filas, que se desea que tenga la referencia devuelta. El alto debe ser un número positivo.
Ancho	Argumento opcional. Es el ancho, en número de columnas, que se desea que tenga la referencia devuelta. El argumento ancho debe ser un número positivo.

 

Es importante comentar que si en el argumento “Ref” usamos un rango de dos celdas o más, y usamos la función “DESREF” como única fórmula, no dará como resultado un error del tipo #¡VALOR!. Otra cosa a resaltar es que si alguno de los argumentos de la función se “salen” de la hoja, es decir que hacen referencia a filas menores a uno o columnas “menores” a la “A”, pues dará como resultado el error #¡REF!.

Comencemos con ejemplos simples para que se entienda como funciona:

Si bien las fórmulas están en las celdas de la columna “E”, las he colocado en texto en la columna “F” (en las celdas adyacentes correspondientes de la derecha) para que se aprecie más fácilmente. Las referencias son a una sola celda y a la posición, usando “Filas” y “Columnas”, de la que se requiere obtener el dato.

En este caso tomamos también una sola celda como referencia pero “mantenemos” dicha celda (Argumentos “Filas” y “Columnas” en cero – 0) y lo que hacemos es usar los argumentos “Alto” y “Ancho” para indicarle a la función “Suma” que es un rango más amplio que la referencia el que queremos usar.

En este caso, tenemos como referencia un rango de dos columnas de ancho y tres filas de alto, en el primer caso hacemos referencia a dos filas abajo y tres columnas a la izquierda y al no usar los argumentos “Alto” y “Ancho”, la referencia inicial mantiene su tamaño (dos columnas de ancho y tres filas de alto) por lo que el rango sumado corresponde a “B3:C5”. Para que quizá se entienda mejor, es como desplazar todo el rango inicial de referencia sin cambiarle el tamaño.

Con esos mismos datos, miren la segunda fórmula, solo suma el rango “B3:B4” dado que sí hemos usado los argumentos “Alto” y “Ancho” con los valores 2 y 1 respectivamente, cambiando, por decirlo de un modo, el tamaño inicial.

¿Hasta el momento no les convencen los ejemplos? Bueno, veamos algo quizá más habitual y/o práctico. Vamos a suponer que queremos usar una lista desplegable de validación que constantemente incrementa sus registros/elementos/datos y en cada ocasión tenemos que agregar más celdas o datos al origen, entonces, para evitar eso usaremos “DESREF”.

Supongamos que la lista está en la columna “A”, entonces vamos en la pestaña “Fórmulas” al grupo “Nombres definidos”, le damos “Click” a “Asignar nombre”, en “Nombre” colocamos “Mi_Lista” (o el nombre que quieran – sin las comillas) y en “Se refiere a” vamos a ingresar la siguiente fórmula: “=DESREF($A$1,0,0,CONTARA($A:$A),1)” (sin las comillas, no se olviden). Ahora van a la celda en donde desean la lista desplegable y en la pestaña “Datos”, en el grupo “Herramientas de datos” le dan “Click” al botón “Validación de datos”, eligen la pestaña “Configuración”, en “Permitir” eligen “Lista” y en “Origen” colocan “=Mi_Lista” (o el nombre que hayan elegido, y sin las comillas). Como resultado, cada vez que agreguemos datos en la columna “A”, dichos datos se agregaran automáticamente en la lista desplegable.

Espero haberlos ayudado. Hasta la próxima.

Abraham Valencia