Fórmulas matriciales i II

Ya había tratado antes el tema de las fórmulas matriciales, pero estaba contento de cómo lo había hecho. Además la sintaxis que expliqué no era la más comprensible visualmente, por lo tanto esta entrada es una aclaración y extensión de la primera.

Repasemos los aspectos clave:

  • Las fórmulas matriciales son muy potentes y permiten analizar tablas con condiciones complejas.
  • Las fórmulas matriciales consumen muchos recursos del ordenador, se deben usar con moderación y cuando no exista una fórmula nativa de Excel que sirva.
  • No se puede evaluar una columna entera tipo "A:B", se debe definir un rango con un límite definido de filas como "A1:B3000".
  • Las fórmulas matriciales pueden evaluar matrices verticales (fila a fila) o horizontales (columna a columna).
  • Para que una fórmula se evalúe de forma matricial se debe introducir apretando "Ctrl"+"Mayúsculas"+"Enter".
Bien, entremos un poco en detalle de como trabaja Excel con la fórmula. Tendremos una tabla que puede ser tan grande como queramos y le pondremos unas condiciones sobre algunas de las columnas o filas. Cada vez que introducimos una condición el Excel se hace un mapa de la columna tipo (verdadero, falso, verdadero, falso, falso, ...). Si queremos poner condiciones adicionales, Excel irá superponiendo estas matrices de condiciones.
Ejemplo: (verdadero, falso, falso, verdadero, falso) y (falso, verdadero, falso, verdadero, falso) = (falso, falso, falso, verdadero, falso)
Se debe resaltar que el orden de las condiciones es importante, ya que en una condición tipo "y" cada condición actúa como filtro de las siguientes.
Ejemplo: (verdadero, falso, falso, verdadero, falso) o (falso, verdadero, falso, verdadero, falso) = (verdadero, verdadero, falso, verdadero, falso)
Una condición tipo "o" hace que Excel realice una suma de las matrices.

Normalmente pondremos diversas condiciones comparándolas con una referencia:
  • > mayor que
  • <>
  • = igual que
  • >= mayor o igual que
  • <= menor o igual que
  • <> diferente que
Los operadores que podemos usar son:
  • * operador "y"
  • + operador "o"
  • { } como matriz de condiciones "o" (separador ; para filas y \ para columnas)
La sintaxis es la siguiente (recordad que las llaves las pone Excel):
{=FÓRMULA(SI(((condición 1a)+(condición 1b))*(condición 2);(rango valores)))}

{=FÓRMULA(SI((condición={1a; 1b})*(condición 2);(rango valores)))}
Se lee: si se cumple la condición 1a o la condición 1b y también se cumple la condición 2 entonces aplica la fórmula sobre el rango de valores. Las dos fórmulas son iguales si los datos están en columnas y se evalúan fila a fila.
Ejemplo real de fórmula:
{=SUMA(SI((B5:F5="b")*(B6:F6={"azul"\"rojo"});B8:F8))}
La ventaja de las fórmulas matriciales también reside en que no sólo permiten hacer sumas complejas sino que admiten otros tipos de funciones como promedio, todas las medianas, mínimo, máximo, contar, k ésimo mayor o menor, percentil y aún debo dejarme alguna.

Existe un caso particular para calcular sumas matriciales en el que nos podemos ahorrar el condicional:
{=SUMA((condición={1a; 1b})*(condición 2)*(rango de valores))}

{=SUMA((condición={1a; 1b})*(condición 2)*(rango de valores<>0))}
En la primera formula sumamos os valores del rango de valores y en la segunda contamos los valores diferentes de 0.

Fichero con ejemplos: Fórmulas matriciales II
----------------------------------------------

Comprobar si un archivo existe

Suponemos que hay un informe diseñado para ejecutarse diariamente y que genera una serie de ficheros de resultados con distintos datos automáticamente. Éstos ficheros se guardan en directorios específicos que están definidos en la macro de exportación.
La realidad nos dice que al final éste informe se ejecuta más de una vez al día y lo que se quiere no es regenerarlo sino actualizar los datos. De hecho la primera ejecución puede haber generado ficheros o no o sólo unos pocos. ¿Cómo sabemos si un fichero existe para abrirlo y actualizarlo?
Para saberlo utilizaremos un objeto que iremos viendo a menudo cuando queramos acceder a las funciones de sistema, el FileSystemObject. Éste objeto nos permite operar con Windows usando scripts de sistema.

Ejemplo:
Sub abrir_fichero_si_existe()
Dim Archivo As String
Archivo = "C:\test.xls"
Set fso = CreateObject("Scripting.FileSystemObject")

If fso.FileExists(Archivo) Then
    Workbooks.Open FileName:= Archivo
Else
    Workbooks.Add
End If

End Sub

Éste ejemplo abre el fichero test.xls si existe o crea un libro nuevo.

Macro que borra otra macro

Alguna vez necesitaremos que una macro se ejecute una única vez. En otro caso querremos que en unas determinadas condiciones se elimina una macro. Bien, éste código os puede ayudar.

Sub EliminaMacro(macro as string, mòdul as string)

Dim liDeb, NbLi

With ThisWorkbook.VBProject.VBComponents(mòdul).CodeModule
    liDeb = .ProcStartLine(macro, 0)
    NbLi = .ProcCountLines(macro, 0)
    .DeleteLines liDeb, NbLi
End With

End Sub
La llamada a la macro se realiza desde otra macro:
EliminaMacro “[nombre_de_la_macro]”, “ “[nombre_del_módulo]”
Ésta macro elimina sólo la macro especificada dentro el módulo especificado, deja todas las otras líneas de código intactas. Cómo es una subrutina con variable, la macro queda oculta y no se puede ejecutar desde el menú Macros > Macros… (Alt+F8). También podemos eliminar las variables y poner directamente el nombre del módulo y de la macro manualmente en los sitios especificados, de ésta manera la macro seria visible. Si queremos que la macro se elimine a sí misma sólo debemos especificar su propio nombre.

Introducción a las macros

Muchos de vosotros me habéis pedido alguna vez que os enseñe a hacer macros. Otros no queréis macros en vuestros archivos Excel porque no veis que hacen y “no las podéis controlar”. En ésta serie de artículos intentaré que aprendáis a dominar las macros y os sentáis a gusto con ellas.
Hay diversas razones para decidirnos por las macros. Una tarea repetitiva en el tiempo, una tarea puntual pero con un alto grado de repeticiones o una tarea en la que sea necesaria la aparición de una macro para poderla hacer.
Para aprender a hacer macros lo que normalmente se hace es grabar una serie de acciones. Esto lo hacemos mediante el menú Herramientas > Macros > Grabar nueva Macro… Esto nos da una base de trabajo, pero no siempre es suficiente. La mayoría de veces deberemos pasar por el editor de VBA [botón] (Alt + F11). Una macro es una rutina de código de Visual Basic for Applications (es un Visual Basic adaptado para Office), debido a esto cuando modificamos una macro lo hacemos en Visual Basic.
La ventana de edición es ésta:


Aquí podéis ver el código que se ha escrito al grabar la macro. A la izquierda se muestran los proyectos (libros Excel) y a la derecha hay el código que puede estar en los módulos de código, formularios, hojas o el libro mismo.
Si observáis detenidamente el código generado veréis que es bastante sencillo de entender. Aún así hay dos problemas que aparecen frecuentemente. Al grabar una macro, nuestras equivocaciones y todas las órdenes intermedias que hacemos con el cursos (como desplazar la página) también quedan grabadas y obtenemos un exceso de código inútil. Esto hace que el código “sucio” parezca confuso y se ejecute más lentamente. El otro problema es que veremos que hay acciones que no quedan grabadas i entonces tendremos que escribir el código nosotros mismos.
En resumen, casi siempre tendremos que pasar por el editor de VBA para tener unas macros potentes, flexibles y bien escritas; lo ideal es que se puedan reutilizar y sean comprensibles.

Fórmulas más leíbles

Seguro que más de una vez habéis escrito una de esas fórmulas espectacularmente grandes en la barra de fórmulas del Excel. Al cabo de unos cuantos caracteres y paréntesis no sabéis dónde estáis y para ver qué hacer la fórmula o dónde falla podéis estar más de diez minutos perdidos entre cinco o seis paréntesis. Bien, hay una fórmula muy sencilla de hacer las fórmulas más leíbles.
Cuando escribáis una fórmula podeis hacerlo en diversas líneas para que sea más fácil de leer. Con [Alt] + [Enter] haréis una nueva línea dentro de la barra de fórmulas que no afectará a comportamiento de la fórmula.

Si hacéis esto escribiendo texto, el texto resultante en la celda sí que tendrá diversas líneas.

VBA: lenguaje orientado a objeto

VBA es un Visual Basic (de Microsoft) destinado a usarse con las aplicaciones Office. El VBA es un lenguaje orientado a objeto, es decir, hay unos objetos que representan algo y nosotros modificamos estos objetos. Ejemplos de objetos en Excel son la aplicación (Application), otro es el libro con el que trabajamos (ThisWorkbook), un rango de celdas (Range(“A1:C30”)) o una selección (Selection).
Estos objetos los podemos manipular mediante métodos como un Copy o cambiar sus propiedades como Name. Un método nos permite transformar un objeto o trabajar con él mientras que una propiedad modificará algún aspecto del propio objeto. El editor de VBA nos ayudará a encontrar qué métodos o propiedades se pueden aplicar a un objeto en concreto. Cuando pongamos un punto detrás de un objeto el editor nos mostrará la lista de métodos o propiedades que podemos aplicar. Si encontramos la opción que buscamos la podemos seleccionar con [Tab].

Ejemplo:
ThisWorkbook.Sheets(1).Range("A1:C3").Select
Selection.Copy
Sheets(2).Range("A1").PasteSpecial xlValues

Lo qué hace: Selecciona el Rango “A1:C3” de la primera hoja del libro dónde está escrita la macro. Copia la selección. I finalmente la pega como valores en la celda A1 de la segunda hoja del libro activo (independientemente que sea dónde está escrita la macro). Os debéis fijar en que la primera línea se crea el objeto “Selección” que en la segunda se le aplica el método “Copiar”.
Para ver qué objetos, métodos y propiedades están disponibles mientras escribís código lo podéis hacer mediante el explorador de objetos. La disponibilidad depende de las referencias cargadas en el editor. Las referencias cargadas en Excel normalmente son las de VBA, las comunes de Office, las específicas de Excel i las de automatización OLE. Si, por ejemplo, queremos manipular un archivo de PowerPoint desde Excel tendríamos que hacer referencia a la librería de PowerPoint para poder tener los objetos, métodos y propiedades disponibles. De esto hablaremos más adelante.

Selecciones

Ahora que ya hemos introducido el concepto de lenguaje orientado a objeto hablaremos de un objeto muy importante, sobre todo para las primeras macros. Normalmente querremos seleccionar alguna cosa del libro de Excel para trabajar con ella. Hoy hablaremos de cómo realizar ésta selecciones.
A Excel podemos seleccionar diversos objetos: hojas, rangos, gráficos, elementos de los gráficos o formas. Como es un método siempre estará al final de la línea de código.

Seleccionar una hoja:
Sheets("[Nombre_de_la_hoja]").Select
Seleccionar un rango:
Range("[Rango]").select
Seleccionar una serie de un gráfico:
ActiveChart.SeriesCollection([Num_Serie]).Select
Cuando trabajamos con hojas podemos hacer referencia a ellas de dos formas: por el nombre o por su índice. Si nos referimos al nombre lo pondremos entre comillas: Sheets(“Nombre”); si nos referimos por el índice pondremos la posición que la hoja ocupa dentro del libro: Sheets(1). Cuidado al cambiar de orden las hojas si se utiliza éste método.
Si trabajamos con celdas lo haremos mediante los objetos Range y Cells.
Con Cells.Select seleccionamos todas las celdas de una hoja, con Cells(2,3) seleccionamos la celda C2 (en formato R1C1).
Con Range podemos seleccionar una celda (“A1”) o un rango (“A1:B23”) tal y como lo haríamos dentro de una fórmula. También podemos referirnos a grupos de celdas (“A1”, “B3”, “H4:H5”). Un objeto Range puede estar compuesto por otros objetos Range, éste método es útil cuando queremos seleccionar un rango de celdas que cambia o que coge una referencia de otra celda.
Range("A1", Range("A1").End(xlDown)).Select
Aquí selecciona un rango desde la celda A1 hacia abajo sea el que sea el numero de celdas. Utilizamos la propiedad End que nos permite recorrer (hacia abajo en éste caso) todas las celdas no vacías situadas contiguamente (equivale a [Ctrl]+ o Fin] + [flecha abajo]).
Range("C3").End(xlToRight).Offset(0, 1).Select
Ésta orden selecciona la celda de la fila 3 que hay en la primera columna libre a la derecha de C3. Usamos la propiedad Offset(F,C) que cogiendo una celda de referencia nos permite desplazarnos F filas y C columnas.

Fórmulas matriciales

Vamos a hablar de una de las funcionalidades de Excel más potentes i seguramente una de las más desconocidas: las fórmulas matriciales.
La potencia de las fórmulas matriciales es que nos permiten analizar matrices de datos aplicando filtros complejos por columnas o filas. La fórmula más simple y parecida es el “SUMAR.SI” que actúa como una fórmula matricial con una sola condición. Una típica fórmula matricial es aplicar una serie de condiciones para obtener una suma final, pero se puede hacer con otras funciones como “PROMEDIO”, “MEDIA”, “MAX” o “MIN”.

Fórmula matricial con dos condiciones:
{=SUMA(SI(B7:B23="A4";SI(D7:D23="negro";E7:E23)))}
Una fórmula matricial se marca poniéndola entre llaves. Esta se lee de la siguiente manera: suma los valores del rango E7:E23 que, en la misma fila, tengan A4 en la columna B i “negro” en la columna D. Se escribe la fórmula que queremos evaluar sin llaves y dentro ponemos condicionales simples (“SI”). Los condicionales y operaciones lógicas se pueden ir jerarquizando unas dentro de otras hasta hacer fórmulas extremadamente complejas.
¡Atención! Una vez escrita la fórmula no se introduce con un simple [Enter] ya que no se evaluaría, tenemos que hacer [May.] + [Ctrl] + [Enter] y veremos como aparecen las llaves conteniendo la fórmula.
Este tipo de fórmulas se debe usar con moderación ya que , cuando más compliquemos la fórmula, analicemos matrices muy grandes o tengamos muchas fórmulas matriciales en el mismo libro de Excel veremos como el rendimiento del programa disminuye mucho.
Archivo de ejemplo y ejercicios (9kb).

Las macros son la solución? Macros vs. fórmulas

No, las macros no son la solución a los problemas que nos podemos encontrar en Excel, sólo son otra herramienta. Muchas veces podemos solucionar los problemas puntuales que tengamos con una buena fórmula o conjunto de fórmulas.

Las fórmulas tienen diversas ventajas respecto a las macros. Son visibles, fácilmente auditables, se calculan automáticamente y muchas veces son mas sencillas. También debemos pensar que Excel está optimizado para ser utilizado mediante las fórmulas y procedimientos que lleva incorporados, si nos inventamos nuevos para hacer lo mismo probablemente sean más lentos.

Aún así las fórmulas tienen un problema muy evidente: cuesta leerlas. Cuando tenemos formulas bastante complejas con funciones anidadas dentro de otras funciones muchas veces nos perdemos debido a la cantidad de opciones disponibles. Más adelante daremos consejos para solucionar este pequeño inconveniente.

Sobre Excelente

Excelente es un blog destinado a analistas de información, ésa gente que abre el Excel antes de coger un làpiz y un papel, y usuarios medios que necesiten ir un poco más allá de lo que siempre han hecho con una hoja de cálculo. Muchos, sino la mayoría, han desarrollado sus habilidades con la práctica y la pelea diaria con el Excel pero saben que dominan una ínfima parte del programa.


Yo he aprendido un poco por necesidad y otro poco por curiosidad pero siempre intentando ser práctico.


Éste blog es el fruto de mi experiencia Exceliana.


Contactame si tienes alguna pregunta o duda y trataré de responderla.