Tablas dinámicas – cambiar la función CONTAR por SUMA

lunes, junio 10, 2013

Para totalizar los valores en los campos introducidos en el área de valores Excel utiliza la siguiente regla:


  • Si todos los valores en el campo (columna de la tabla de datos) de origen son numéricos, utiliza SUMA.
  • Si alguno de los valores no es numérico, utiliza CONTAR.


El problema con esta regla es que, a diferencia de la función SUMA usada en una celda de la hoja, las celdas vacías son consideradas no numéricas. Esto produce no pocos dolores de cabeza ya que basta que una de las celdas no contenga ningún valor para que la función usada para resumir los valores sea CONTAR.

El problema se agrava cuando tenemos varios campos de valores ya que Excel no cuenta con un método para cambiar la función de resumen de todos los campos de una sola vez. Si tenemos una tabla con muchos campos de valores, nuestro sufrido usuario deberá cambiar manualmente la función de cada uno de los campos.

En este ejemplo tenemos una tabla con los datos de una cadena de comercios para los meses de enero, febrero y marzo. Los datos son: zona, mes, número de visitantes, número de compradores (cuantos de los visitantes realizaron alguna compra) y ventas.



Como puede apreciarse, faltan los datos de marzo de la zona Sur, por lo que las celdas están vacías.

Al crear un reporte dinámico, veremos que Excel usa CONTAR para resumir los datos en lugar de SUMA, que era lo que esperábamos



En este estado de cosas podemos optar por una de estos tres caminos:


  1. modificar una a una la función de resumen
  2. rellenar las celdas vacías con ceros (lo que se puede hacer eficientemente con Ir a-Especial)
  3. usar esta macro para cambiar la función programáticamente:


Sub change_pt_function()
'Cambia la funcion de los campos de valor de CONTAR a SUMA
'Desarrollada por Jorge Dunkelman - JLDExcelsp.blogspot.com

    Dim pt As PivotTable
    Dim iDataFieldsCount As Integer
    Dim iX As Integer
       
    On Error Resume Next
    Set pt = ActiveCell.PivotTable
    On Error GoTo 0
   
    If pt Is Nothing Then
        MsgBox "La celda seleccionada no pertenece a una tabla dinamica", _
                    vbCritical, _
                    "Cambiar funcion en TD"
        Exit Sub
    End If
   
    iDataFieldsCount = pt.DataFields.Count
   
    For iX = 1 To iDataFieldsCount
        With pt.DataFields(iX)
            If .Function = xlCount Then
            .Function = xlSum
            .Name = Replace(.Name, "Cuenta de", "Suma de")
            End If
        End With
    Next iX
       
End Sub


Esta macro recorre todos los campos en el área de los valores; en caso que la función de resumen sea CONTAR, la reemplaza por SUMA.

Para usar la macro hay que seleccionar previamente alguna celda de la tabla dinámica, por eso usamos un MSgBox para informar al usuario en caso que no se haya seleccionado un celda de la tabla dinámica.
Otro detalle es que al usar la propiedad DataFields, el nombre del campo (label) no cambia al cambiar la función. Por eso, suponiendo que el usuario no ha cambiado el nombre por defecto del campo ("Cuenta de…"), reemplazamos "Cuenta de…" por "Suma de…" programáticamente.
Si no queremos cambiar los nombres de los campos, podemos poner un apóstrofe (') al comienzo de la sentencia .Name = Replace(.Name, "Cuenta de", "Suma de"), de manera que no sea ejecutada.

Para usar este código con comodidad podemos agregar un icono en la barra de acceso rápido.

8 comments:

AMD,  10 junio, 2013 15:29  

Te agradezco la rapidez en contestar a mi duda y el grado de detalle en la explicación. Realmente útil. Y la referencia a "Ir a-Especial", igualmente buena.

Gracias por tu trabajo.

María Elena González González 05 mayo, 2014 23:41  

Hola gracias por la información, de mucha ayuda. Yo no tengo conocimiento de VB y viene bien, pero no me funciona todo el código, sólo la parte donde identifica que no está en la tabla dinámica, me podéis ayudar por favor.
Gracias.

Jorge Dunkelman 06 mayo, 2014 07:28  

Antes de correr la macro tienes que seleccionar alguna celda de la tabla dinámica.

María Elena González González 06 mayo, 2014 09:10  

Gracias por la pronta respuesta. Estoy en cualquier celda de la TD y la ejecuto y no funciona, me coloqué fuera para ver si por lo menos iba lo del error de que no estaba y eso si va.
Qué problema puede ser?

Jorge Dunkelman 06 mayo, 2014 09:39  

Maria Helena, si me mandas el archivo (ver el enlace Ayuda, en la parte superior de la plantilla) podría fijarme donde está el problema.

María Elena González González 06 mayo, 2014 17:02  

Jorge, ya me funciona. Gracias. No sé exactamente lo que estaba haciendo mal.
Un saludo.

Anónimo,  16 mayo, 2014 17:18  

Buenisimo Muchas gracias, muy facil de utilizar

Anónimo,  11 junio, 2015 12:54  

Muchas, muchas gracias! La macro es un ahorro de tiempo impresionante!! Gracias!

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP