La función FORMULATEXTO y Excel 2010

Una de las nuevas funciones en Excel 2013 es FORMULATEXTO. Esta función, disponible solamente en Excel 2013 y Excel 365, transforma la fórmula de una celda en texto.

¿Para que sirve?, me preguntarán Básicamente, para documentar fórmulas, como la que aparece en la imagen. La fórmula calcula la fecha del tercer lunes de un mes determinado (la nota sobre el tema la publicaré próximamente).

En algunas de mis notas suelo incluir el texto de la fórmula. Para obternerlo suelo copiar la fórmula directamente de la barra de las fórmulas y pegarla en una celda previamente formada como Texto o pegarla quitando el símbolo "=" para que Excel no la interprete como fórmula.

Dado que la mayor parte de mis notas las desarrollo con Excel 2010, decidí que podría crear una UDF (función definida por el usuario) que imite el funcionamiento de FORMULATEXTO para poder usarla en versiones de Excel anteriores a Excel 2013.

El código de la función es el siguiente


Function formulaText(rcell As Range, vType As Boolean) As String

    If rcell.HasFormula = False Then
        formulaText = "#NA!"
        Exit Function
    End If
  
    Select Case vType
        Case Is = False
            formulaText = rcell.FormulaLocal
        Case Is = True
            formulaText = Mid(rcell.FormulaLocal, 2, Len(rcell.FormulaLocal) - 1)
    End Select
    
End Function


La función tiene dos argumentos:

• rcell: es la celda que contiene la fórmula

• vType: que puede ser 0 o FALSO (incluye el símbolo "=" al principio de la fórmula) o 1  o VERDADERO (el texto no muestra el símbolo "=")

Totalizar datos en Excel con Datos-Consolidar

Uno de los métodos con los que cuenta Excel para consolidar datos de distintas hojas o cuadernos es Datos-Consolidar (pueden ver esta prehistórica nota en el blog o apretar el enlace Consolidar Datos en la nube de etiquetas).

También podemos usar este método para consolidar rápidamente los datos de una tabla. Supongamos que tenemos una tabla de 1000 filas que detalla las ventas del mes de noviembre. Dado que cada venta se anota por separado, cada fecha del mes aparece varias veces. Nuestro objetivo es totalizar las ventas por día (si, por supuesto que podemos hacerlo con una tabla dinámica; pero aquí mostraremos como hacerlo con Datos-Consolidar),

Supongamos que las ventas están en el rango A1:B1001; el primer paso será definir un nombre que se refiera a este rango. Seleccionamos el rango y introducimos el nombre en la cuadro de nombres

El próximo paso es seleccionar una celda, en la misma hoja o en la hoja donde queremos que aparezcan los datos totalizados, y activar el menú Datos-Consolidar

Nos aseguramos que la función sea "Suma", en la referencia pegamos el nombre que se refiere al rango de los datos (podemos usar F3 para pegar el nombre) y marcamos las opciones "Fila superior" y "Columna izquierda" en la opción "Usar rótulos". Finalmente apretamos Aceptar

Excel crea instantáneamente una tabla totalizando las ventas por fechas.

Detalles a tener en cuenta:

• Excel no pone el encabezamiento en la primer columna (la celda D2 en nuestro ejemplo), por lo que debemos agregarla manualmente;
• en la tabla de totales las fechas aparecerán con formato General, por lo que debemos aseugrarnos de pre-formar el rango de las celdas o hacerlo después de crear la tabla.

Señalar series por color en gráficos de Excel

Supongamos esta situación:

Hemos representado en el gráfico una serie de valores que pertenecen a tres grupos (en la "vida real" podría tratarse de ventas de grupos de productos o de salarios de grupos de trabajadores, etc.).
Lo que queremos es representar cada grupo con un color distinto. Por defecto, Excel da a todos los puntos de una serie el mismo color. Podríamos cambiar el color de la columna de cada punto manualmente, pero podemos con hacerlo fácilmente usando la técnica que mostraré a continuación (y que ya he mostrado en alguna nota en el pasado).

El truco consiste en descomponer la única serie en tres series, una para cada grupo. Lo hacemos creando tres columnas auxiliares

En este ejemplo me he limitado a copiar los valores del grupo 1 a la columna D, los del grupo 2 a la columna E y los del grupo 3 a la columna F.
Para crear el nuevo gráfico seleccionamos el rango B2:B11 e inmediatamente, apretando y manteniendo apretada la tecla Ctrl. el rango D2:F11; seleccionamos Insertar-Gráfico-Columna, y obtenemos

Excel otorga automáticamente un color distinto a cada serie. Por supuesto podemos ocultar las columnas auxiliares o ponerlas en un rango oculto.

Mis perspicaces lectores habrán notado que existe un problema con las etiquetas del eje de las categorías (el eje de las X). Estas están alineadas a la izquierda, coincidiendo con el primer valor de cada grupo, cuando lo que quisiéramos es que estuvieran en el centro del grupo.

Para obligar a Excel a centrar los rótulos del eje agregamos una columna a nuestra tabla, entre las columna B y C; esta columna contiene un espacio (no está vacía, sencillamente seleccionamos la celda, apretamos la tecla de espacios e inmediatamente Enter)