lunes, agosto 24, 2015

Formato numérico de campos de datos en tablas dinámicas

Podemos considerar dos formas de organizar datos en una hoja de Excel: en forma plana ("flat file") y en forma tabular ("tabular dataset").  Supongamos una de tabla de ventas que muestra las cantidades vendidas de distintos productos por año. Si organizamos la tabla en forma plana tendremos algo así

En cambio si organizamos los datos en forma tabular, tendremos esta tabla


Esta última forma donde todos los valores (datos numéricos) están en una única columna (un solo único campo numérico) es la más eficiente para trabajar con tablas dinámicas.

Pero si tenemos que crear una tabla dinámica a partir de una matriz de datos plana descubriremos que cada campo de valor (las columnas 2010, 2011, etc, en el primer ejemplo) debe ser arrastrado individualmente al área de los datos. Y lo mismo cuenta para el formato de los números. Excel da por defecto formato "General" a los datos numéricos.

Al crear esta tabla dinámica


si queremos cambiar el formato de los valores tendremos que hacerlo campo por campo, cinco veces en nuestro caso. En nuestro auxilio vendrán las macros, como cada vez que tenemos que queremos automatizar una tarea repetitiva.

Si tenemos una única tabla dinámica en la hoja activa podemos usar esta macro

Sub format_NUM_1()
    Dim strFormatSelected As String
    Dim oPTable As PivotTable
    Dim oPField As PivotField
    Dim iPTCount As Integer
 
    iPTCount = ActiveSheet.PivotTables.Count
    If iPTCount = 0 Then
        MsgBox "No se encontraron tablas dinamicas en la hoja", _
                    vbInformation, _
                    "Formato numerico"
        Exit Sub
    End If

    Set oPTable = ActiveSheet.PivotTables(1)

    Application.Dialogs(xlDialogFormatNumber).Show

    strFormatSelected = ActiveCell.NumberFormat

    For Each oPField In oPTable.DataFields
        oPField.NumberFormat = strFormatSelected
    Next oPField
 
End Sub


Usamos el método Application.Dialogs(xlDialogFormatNumber).Show para abrir el diálogo de formato de números, capturamos la elección de usuario y con el loop For Each...Next lo aplicamos a todos los campos de datos de la tabla.

Este video muestra el funcionamiento



Si hay más de una tabla dinámica en la hoja activa tendremos que complicar un poco nuestro código

Sub format_NUM_all()
    Dim strFormatSelected As String
    Dim oPTable As PivotTable
    Dim oPField As PivotField
    Dim iPTCount As Integer
    Dim iX As Integer

    iPTCount = ActiveSheet.PivotTables.Count
    If iPTCount = 0 Then
        MsgBox "No se encontraron tablas dinamicas en la hoja", _
                    vbInformation, "Formato numerico"
        Exit Sub
    End If
    
    With Application
        .ScreenUpdating = False
        .Dialogs(xlDialogFormatNumber).Show
        strFormatSelected = ActiveCell.NumberFormat

        For iX = 1 To iPTCount
            For Each oPField In ActiveSheet.PivotTables(iX).DataFields
                oPField.NumberFormat = strFormatSelected
            Next oPField
        Next iX
        .ScreenUpdating = True
    End With



Podemos llevar nuestra macro un paso más adelante y dar al usuario la posibilidad de elegir que tabla dinámica formar de las que se encuentran en la hoja activa.

En este caso tendremos que agregar un Userform con una combobox, que contendrá los nombres de las tablas dinámicas presentes en la hoja activa (un evento crea la lista dinámicamente de acuerdo a la hoja), y una rutina que recibe como variable el nombre de la tabla elegida, abre el diálogo de formato numérico y aplica el formato elegido a la tabla.

Podemos reunir todas las macros en un complemento (Add in) e instalarlo de manera que podamos usarlo en todo cuaderno activo de Excel.
Otra ventaja del complemento es que agregará una pestaña en la cinta de comandos para activar las macros con facilidad.


El complemento se puede descargar sin cargo aquí

Después de descargar y guardar el complemento lo instalamos usando el menú Programador-Complementos (en caso de ser necesario usamos el botón Examinar para encontrar la ubicación del complemento)


En caso de recibir una advertencia de seguridad aceptamos la opción "Habilitar contenido".

Este video muestra la instalación y el funcionamiento de la macro



Los códigos pueden verse con el editor de Vba (el complemento no está protegido con contraseña).

Algunas observaciones:
  • como norma de buena práctica es recomendable reemplazar el nombre por defecto de la tablas dinámica (Tabla dinámica1, Tabla dinámica2, etc.) por algo más significativo

lunes, agosto 17, 2015

Copiar y pegar formatos de tabla dinámica

En ciertas ocasiones queremos evitar que el usuario pueda ver todos los datos que alimentan una tabla dinámica. Dado que Excel no tiene un método para "desconectar" la tabla de la base de datos, la forma de hacerlo es con Copiar-Pegar Valores.

Pero este método tiene un inconveniente: Excel sólo copia los datos sin copiar los formatos que hayamos establecido en el reporte.

Por ejemplo, si copiamos con Pegar-Valores-Formato y Origen este informe dinámico al que hemos aplicado uno de los estilos


ek resultado no será el esperado; sólo el formato de los números aparece y ancho de las columnas son copiados; el resto de los formato desaparece.


Sin embargo podemos hacerlo usando este truco (tomado de este post de John Walkenbach)

Empezamos por seleccionar el rango de la tabla (o el rango de las columnas que ocupa), aplicamos Copiar y seleccionamos la primer celda del rango al cual queremos copiar la tabla.

Luego en la cinta Vamos a Inicio y abrimos el Portapapeles usando la flecha que aparece en el angulo inferior derecho

Hacemos un clic en el elemento que deseamos copiar y obtenemos este resultado

Coo podemos ver todos los formatos han sido copiados con la única excepción del ancho de las columnas.

miércoles, agosto 12, 2015

La función CELDA de Excel

En un post anterior sobre funciones Excel raramente usadas tendría que haber mencionado a la función CELDA(). Por ejemplo, a lo largo y a lo ancho de los 670 posts de esta blog, hay solamente dos menciones a esta función.

Tal como lo insinúa su nombre, esta función da como resultado información sobre una celda y su entorno. La función CELDA() acepta dos argumentos, el tipo de información, que es obligatorio y la celda de referencia, que es opcional.


El uso más obvio de esta función es en fórmulas donde el resultado depende de cierta característica del contenido de la celda o del entorno donde se halla.

Esta función tiene dos características importantes:

  1. es volátil, es decir que es recalculada con cualquier cambio en la hoja. Por eso se suele recomendar como buena práctica evitar, en la medida de lo posible el uso de estas funciones. 
  2. Si se omite la referencia a una celda la información especificada en el argumento tipo_de_info el resultado muestra la información de la última celda cambiada

Veamos algunos usos posibles.

Determinar si la celda contiene una fecha.
Excel no tiene una función nativa para determinar si el valor de una celda es una fecha (existe la función ESNUMERO pero no la función ESFECHA). Podemos usar la opción "Formato" para evaluar si el contenido es una fecha en lugar de usar la función IsDate de Vba (lo que implica crear una UDF).

Sucede que si la celda contiene una fecha la función CELDA con la opción Formato da como resultado "Dx", donde "x" es un número que identifica el tipo de formato


En este ejemplo, la fórmula =IZQUIERDA(CELDA("formato",B5),1)="D" nos permite determinar que la celda B5 contiene una fecha



SUBTOTALES con columnas ocultas 
Podemos hacerlo usando la opción "ancho" que da el ancho de la columna de referencia como número entero (SUBTOTALES realiza cálculos sin tomar en cuenta filas ocultas, pero no funciona con columnas ocultas). 
Si la columna esta oculta su ancho es 0, dato que podemor usar en conjunto con SUMAPRODUCTO tal como muestra Ismael Romero en esta nota de su blog Excelforo


El rango C1:H1 contiene la fórmula =CELDA("ancho",C4)<>0; la celda I4 contiene la fórmula =SUMAPRODUCTO(C1:H1*C4:H4).
Al multiplicar las matriz C1:H1 por la matriz C4:H4, SUMAPRODUCTO convierte los valores VERDADERO en 1 y los FALSO en 0.
Hay que tomar en cuenta que ocultar o mostrar filas no dispara el mecanismo de cálculo de Excel y por lo tanto hay que apretar F9 para actualizar el resultado de la fórmula.

Extaer el nombre de la hoja
Lo hacemos usando la opción "nombrearchivo" que da la  ruta de acceso completa de la celda referencia, en forma de texto.  
Supongamos que tenemos un cuaderno con una hoja por mes; si el nombre de la hoja es "agosto", esta fórmula dará como resultado "agosto"

=EXTRAE(CELDA("nombrearchivo",A1),ENCONTRAR("]",CELDA("nombrearchivo",A1))+1,256)

En esta fórmula es obligatorio usar la referencia a una celda de la hoja ya que de lo contrario elresultado será el nombre de la hoja donde se produjo el último cambio.