Mostrar valores como % del total de filas principales.

Como respuesta a un lector, en la entrada de hoy, analizaremos en qué forma, al trabajar con tablas dinámicas en nuestras hojas de cálculo de Excel 2010, podremos Mostrar valores como % del total de filas principales:

...Hola, necesito ayuda en la forma de mostrar los datos en una tabla dinámica. Me explico, tengo una tabla dinámica con un campo (una columna con datos). Necesito que esos datos vengan mostrados como porcentaje de uno de los totales. No tengo problema en mostrar los datos como porcentaje del total de toda la columna, pero yo lo que necesito no es sobre el total de toda la columna, sino sobre cada uno de los subtotales...

Disponemos de un listado en forma de tabla con tres campos (Fechas, Equipo y Goles), en los que tratamos de averigüar para cada mes la distribución porcentual de goles para cada Equipo, así como, al tiempo, el peso de cada mes respecto al total de goles marcados en el periodo.
Veamos una imagen de nuestra tabla origen de datos:

Siendo esta la tabla dinámica que dará respuesta a nuestras peticiones:

Para llegar a este resultado nos aprovecharemos de una funcionalidad que aparece en nuestro Excel 2010: Mostrar valores como % del total de filas principales.
Pero antes debemos construir nuestra tabla dinámica, agregando el campo 'Fechas' al área de filas agrupándolo automáticamente por meses, igualmente agregaremos en un segundo nivel el campo 'Equipo' en el mismo área de filas, y acabaremos añadiendo el campo 'Goles', por duplicado, al área de valores:

Seguir leyendo...

Una matricial para obtener una lista de valores únicos en Excel.

Veremos hoy cómo con una función matricial en Excel podremos obtener un listado ordenado de elementos únicos, sin repetir:
Supongamos un listado de con diferentes Equipos repetidos, sobre una Tabla de Excel, que previamente ha quedado ordenada:

Como se ve en la imagen, el objetivo es lograr un listado como el que se aprecia en B2:B5; listado que se consigue aplicando en el rango B2:B20 la siguiente función matricial (ejecutada presionando Ctrl+Mayusc+Enter):
{=SI.ERROR(INDICE(Equipo;K.ESIMO.MENOR(SI(COINCIDIR(Equipo;Equipo;0)=FILA(INDIRECTO("1:"&FILAS(Equipo)));COINCIDIR(Equipo;Equipo;0);"");FILA(INDIRECTO("1:"&FILAS(Equipo)))));"")}
una función un poco larga, pero que vamos a analizar paso a paso.
Antes de comenzar explicar que hemos asignado un nombre al rango A2:A20 de la Hoja 'Matricial'
Equipo =Matricial!$A$2:$A$20
En primer lugar en lo más profundo de nuestra fórmula nos encontramos con
=COINCIDIR(Equipo;Equipo;0)
que tiene el efecto de numerar los diferentes elementos según su agrupación, como vemos en la imagen:

Seguir leyendo...

VBA: Función personalizada para agregar un caracter.

Hoy desarrollaremos una función personalizada en Excel, conocidas como UDF, que modificará ciertos registrso incluidos en celdas de nuestra hoja de cálculo, de tal forma que añadirá a los caracteres de texto de dichos códigos un nuevo caracter '0'. Veámos que pedía nuestro lector:

...Trabajo con varios libros enlazados mediante datos con valores tipo alfanumérico A001; A002; A003… B001… N999. Cada cierto tiempo me actualizan el inventario y suelo compararlo utilizando buscarv, de esta forma encuentro los códigos cambiados, nuevos registros, bajas, etc.. El problema es que me han actualizado el inventario y como hay referencias que ya pasan de X999, han introducido un 4º dígito y lo que antes era V567 es ahora V0567. ¿Se puede sustituir en todos los registros que yo he empleado en mis libros enlazados con el inventario este código antiguo para poder emplear el nuevo?...

El objetivo es construir un procedimiento Function que evalúe caracter a caracter cada contenido de una celda, de tal manera, que si encontrara un carcter de texto (realmente uno no numérico) lo reemplace por ese mismo caracter de texto (sea cual sea) y le añada, según la peteción del lector un '0' a su derecha.
En esta ocasión he optado por una función personalizada, aunque con una mínima variación, se podría construir un procedimiento Sub, esto es, una macro.
Veamos entonces nuestro código completo, que incluiremos en un módulo del Editor de VBA:

Option Base 1
Public Function AgregaLetras(cadena As String) As String

Dim Posicion As Integer, Caracter As Variant
'definimos una matriz sin especificar elementos
Dim Matr() As Variant
'la redefinimos y le asignamos un número de elementos
'igual al número de caracteres que componen la cadena a evaluar
ReDim Preserve Matr(Len(cadena))

'recorremos cada uno de dichos caracteres
For Posicion = 1 To Len(cadena)
    Caracter = Mid$(cadena, Posicion, 1)
    'si no es numérico (es de texto) entonces agrega '0'
    'si es numérico, se deja tal cual
    If IsNumeric(Caracter) = False Then
    Matr(Posicion) = Caracter & "0"
    Else
    Matr(Posicion) = Caracter
    End If
Next Posicion
'finalmente con JOIN, la función devuelve
'un concatenado de los elementos de la matriz
AgregaLetras = Join(Matr, "")
End Function

El trabajo ha consistido en disgregar caracter a caracter cada código a evaluar, verificando si éste es numérico o no, en caso de ser texto añadíamos el caracter modificado como elemento de un Array, de igual forma que si era numérico, en cuyo caso se agregaba como elemento del Array. La función finaliza sencillamente recuperando, concatenados, todos los elementos de nuestra Array.
Podemos ver el resultado en la imagen siguiente:

Seguir leyendo...

Fórmulas 3D tridimensionales en Excel.

Es muy habitual cuando trabajamos resumiendo o consolidando datos en nuestros libros de trabajo de Excel el empleo de las fórmulas tridimensionales o fórmulas 3D, estas fórmulas son especiales por que nos permiten trabajar u operar al tiempo sobre celdas de diferentes hojas de nuestro libro, en especial, cuando las disposiciones de esas celdas responden a un mismo patrón o estructura en las diferentes hojas. Según la ayuda de Excel, a esto le llamamos Referencia 3D.
Es importante mencionar que este tipo de fórmula 3D no admite operar con todas las funciones estándar existentes, sólo podremos hacerlo con las siguientes:
SUMA, PROMEDIO, PROMEDIOA, CONTARA, MAXA,MIN, MINA, PRODUCTO, DESVEST, DESVESTA, DESVESTP, DESVESTPA, VAR, VARA, VARP y VARPA.
El método para componer estas funciones consiste en seleccionar la celda en la que desee escribir la función, después escribiremos el nombre de alguna de las funciones anteriores e iniciaremos o abriremos un paréntesis. Seguidamente, con el ratón, haremos clic en la pestaña de la primera hoja de cálculo a la que desea hacer referencia.; y con la tecla MAYUSC presionada (sin soltar) haremos clic en la pestaña de la última hoja de cálculo a la que desea hacer referencia. Es importante aclarar que al trabajar con estas fórmulas 3D, se hace necesario que todas las hojas con datos estén contiguas (una al lado de la otra), ya que realmente estamos indicando una operación entre la primera hoja y la última seleccionada, es decir, un intervalo de hojas.
Por último seleccionaremos la celda o el rango de celdas sobre el que deseamos trabajar en las diferentes hojas; completaremos nuestra fórmula 3D y, a continuación, presionaremos ENTER.
El aspecto de una función 3D para una suma sería este:
=SUMA(Hoja1:Hoja5!A1:C3)
que sumaría el rango A1:C3 de cada una de las hojas del libro de trabajo situadas entre las Hoja1 y la Hoja5, ambas incluidas.

Veamos un ejemplo sencillo. Partiremos de los datos de tres empresas, situados cada uno en una hoja (llamadas 'Datos1', 'Datos2' y 'Datos3'); serán datos sencillos de Ingresos y Gastos. Datos en todo caso que queremos tener tratados o consolidados en una cuarta hoja (Resumen-Consolidado):

En la hoja 'Resumen-Consolidado', en la celda B3 escribiremos:
=SUMA(
y a continuación con el ratón apuntaremos a la pestaña de la hoja 'Datos1', con lo que veremos en la celda:
=suma(Datos1!
ahora, con la tecla MAYUSC presionada, nuevamente con el ratón, haremos clic en la última de las hojas por la derecha que queramos incluir en el consolidado, en nuestro ejemplo, la hoja 'Datos3', ya vemos en nuestra celda activa:
=suma('Datos1:Datos3'!
Finalmente nos queda seleccionar, para esta celda, la referencia a incluir en nuestra fórmula a sumar, en el ejemplo planteado, la B3. Cerramos el paréntesis y validamos:
=SUMA(Datos1:Datos3!B3)

Seguir leyendo...

Matriz de constantes en Excel.

Daré otro ejemplo del uso para una Matriz de constantes. En concreto se trata de evaluar sobre una tabla de datos ciertos elementos de un campo para sumar, de manera condicionada a unos criterios dados. Concretamente deseamos acumular únicamente los movimientos de aquellos 'Comerciales' en cuyo concepto aparezca o bien el texto 'CONSIG' o bien el texto 'MY12-'.
Veamos nuestra tabla de partida con los textos remarcados:

Como siempre existen muchas formas de resolver este problema, pero en esta ocasión propondré una forma alternativa de trabajo mediante el empleo de una matriz de constantes. La fórmula matricial (ejecutada presionando Ctrl+Mayusc+Enter) que añadiremos en la columna H de nuestro ejemplo, y que resuelve la cuestión es:
{=SUMA(SI(O(CONTAR.SI($A3;"*CONSIG*")>=1;CONTAR.SI($A3;"*MY12-*")>=1);SUMAPRODUCTO(B3:G3;{1\1\1\1\1\1})))}
Lo primero que hacemos con esta función matricial es evaluar los elementos del campo concepto, mediante un CONTAR.SI, ya que esta función admite el uso de comodines (asterisco e interrogación) en sus argumentos de criterios(como otras), anidadas en una función lógica O, conseguimos averiguar si el elemento contiene bien el texto 'CONSIG' o bien el texto 'MY12-' en alguna parte de la celda.
Mediante un condicional SI efectuamos la operación en caso de que sea cierta o verdadera dicha condición. Y es aquí, cuando sea cierto, que empleamos la matriz de constantes, ya que aplicaremos la función:
SUMAPRODUCTO(B3:G3;{1\1\1\1\1\1})
que realiza el producto elemento a elemento de la cantidad de cada 'Comercial'(rango B3:G3) por el elemento correspondiente de nuestra matriz {1\1\1\1\1\1}, acumulando dichos productos.
Nuestra función finaliza anidando el acumulado con una SUMA, que devuelve el resultado esperado.
Aunque ciertamente es una fórmula algo 'retorcida', es interesante cómo podemos generar a nuestro antojo, matrices de constantes, para conseguir nuestros objetivos, permitiéndonos Excel su uso como argumentos en ciertas funciones estándar (siempre que admitan rangos de celdas como argumentos).

Seguir leyendo...

Un ejemplo de la función ES.IMPAR en Excel

Explicaré hoy otro uso de la función ES.IMPAR orientado a localizar ciertos valores en un rango de datos de nuestra Hoja de cálculo, este rango lineal tiene la peculiaridad de alternar los valores buscados con los valores a devolver:

...quisiera saber por favor si es posible una solución a lo siguiente : Tengo la siguiente tabla : fecha|valor|fecha|valor|fecha|valor (una misma fila con datos ordenados por fecha) Lo que necesito hacer es, dada una fecha indicada en otra celda, encontrar el valor correspondiente, ordenadamente. Por ejemplo : A B C D E F 01/02/12 | 1.000 | 01/03/12 | 3.000 | 01/06/12 | 5.555 | Ingresando 01/03/12, el valor obtenido debería ser 3000 Ingresando 01/04/12, el valor obtenido debería ser 3000 Ingresando 01/05/12, el valor obtenido debería ser 3000 Ingresando 01/06/12, el valor obtenido debería ser 5555...

Veamos dispuesto en nuestra hoja de trabajo el problema:

Nuestro trabajo consiste en localizar del rango A2:F2 sólo las celdas donde se encuentran los importes, en función de una celda dada donde hemos introducido una Fecha; la complicación está en que se debe localizar el Importe también para fechas que no están en ese rango, es decir, tenemos que encontrar Importes por aproximacción.
La función que resuelve esta tarea es la siguiente:
=INDICE(A2:F2;1;SI(ES.IMPAR(COINCIDIR(C9;A2:F2;1));COINCIDIR(C9;A2:F2;1)+1;COINCIDIR(C9;A2:F2;1)))
como podemos ver en la imagen:

Seguir leyendo...

VBA: Modificar etiquetas de datos en un gráfico de Excel.

En esta ocasión, partiendo del gráfico apilado visto en una entrada anterior, mediante una macro, conseguiremos personalizar las Etiquetas de datos de los diferentes puntos de nuestras Series.
Nuestro objetivo es conseguir este aspecto en nuestra ya conocida gráfica:

La modificación consiste en añadir un Texto personalizado en las Etiquetas de datos de los diferentes puntos que componen nuestra serie, añadiendo no solo un texto 'Total' que no existe en la Tabla de datos, si no además debajo, en una nueva línea, el sumatorio de ambos semestres.
Para no dispersarnos con el código de nuestra macro, supondremos ya existen las Etiquetas de datos, y ya tienen la posición y formato de color y fuente. Sobre esta premisa, el desarrollo de nuestra macro, a incluir en un Módulo del Explorador de proyectos, dentro de nuestro Editor de VBA (Alt+F11), escribimos:

Sub ModificarEtiqueta()
ActiveSheet.ChartObjects("4 Gráfico").Activate
'contamos el número de puntos existentes en las series
puntos = ActiveChart.SeriesCollection(2).Points.Count

Application.ScreenUpdating = False
'pasamos por todos los puntos de la serie con el bucle FOR...NEXT
For i = 1 To puntos
    'definimos el texto y valor aincluir en la etiqueta
    ActiveChart.SeriesCollection(2).Points(i).DataLabel.Text = "Total" & Chr(10) & _
    (Cells(2, i + 1).Value + Cells(3, i + 1).Value)
Next i
Application.ScreenUpdating = True
End Sub

Al ejecutar nuestra macor conseguiremos el formato deseado.

Seguir leyendo...

Grafico de columnas apiladas en Excel.

Sentía la necesidad, desde hacía algún tiempo, de explicar un tipo de gráfico en Excel bastante peculiar como es el Gráfico de columnas apilada; asi que aprovechando la consulta de un lector, voy a intentar explicar algunas de las ventajas y requisitos de este Tipo de gráfico:

...quisiera saber si se puede realizar un grafico combinado de columnas agrupadas con columnas apiladas?...

El objetivo planteado es resumir en un mismo gráfico una parte de gráfico apilado y otra de columnas 'normales'; si bien, lo más importante es sin duda tener claro qué vamos a apilar, esto es, que los datos apilados tengan sentido.
Para que se vea lo más claro posible, dispondré un ejemplo en que se se resumen datos de Ventas del 2012, son datos para el 'Semestre1' y para el 'Semestre2' (series de datos a apilar), además disponemos una Serie más que indica la media semestral de ventas para nuestro ejercicio económico. Veamos los datos y disposición:

Siendo el objetivo inicial lograr un gráfico con esta forma:

Se puede apreciar que por un lado, al apilar las dos primeras series de datos ('Semeste1' y 'Semestre2') conseguimos al tiempo visualizar los datos independientemente, pero también un aspecto de Ventas totales por año, dejando como información de 'segunda' la Serie de 'Media semestral'.
Los pasos a seguir para obtener este gráfico será en primer lugar Seleccionar el rango de datos, y desde ficha Insertar > Gráficos > Columna > Columna apilada:

Seguir leyendo...

Gráfico circular con subgráfico circular en Excel.

Preguntaba una lectora del blog cómo conseguir que el subgráfico circular de su gráfico, mostrara los elementos que lo componían:

...Tengo que hacer un grafico que representa las ventas de tres vendedores. Pero quiero que el bloque de mayor porcentaje me desglose los clientes que lo componen...

Veamos el planteamiento del problema y así comprender mejor dónde y qué queremos conseguir.

Como podemos observar disponemos de una tabla principal con datos de Ventas de tres comerciales distintos, y una segunda tabla anexa que desglosa las Ventas por clientes de uno de los comerciales. La idea, como se ve en el gráfico es que mediante este tipo de gráfico circular con subgráfico circular, se visualice no solo las proporciones entre Comerciales, si no también el detalle del Vendedor C.
El trabajo comienza entonces unificando ambas Tablas en una sola, eso si, deberemos eliminar (para evitar duplicidades) el registro del Vendedor C de la tabla principal. Nos quedará entonces la siguiente tabla única, sobre la que crearemos nuestro gráfico. Es muy importante, por facilidad, que los registros que vayan al Subgráfico queden juntos al final de la Tabla única:

Generamos nuestro Gráfico circular, que tras Agregarla las 'Etiquetas de datos' queda:

Seguir leyendo...