Otro modelo para administración de horas de trabajo con Excel

No pocas de las entradas del blog reproducen, de una manera u otra, respuestas que doy a consultas de lectores. Esta nota se originó en la consulta de un lector sobre cómo crear en forma automática una hoja para administrar horas de trabajo. Mi lector quería que hubiera una hoja por cada mes del año y que en ella hubiera una columna para día del mes, con la fecha del día y el nombre del día. Esta hoja tendría que actualizarse automáticamente de acuerdo al nombre del mes. Es decir, para enero tendría que haber 31 columnas, para febrero de este año (2008) 29, etc.
La hoja se vería así




En la primer fila tenemos una inicial para cada día de la semana, L – lunes, M – martes, etc. Este valor tiene que actualizarse de acuerdo al día del mes. Así si el 1ro. de enero del 2008 es un martes, tiene que aparecer M.

El desafío era hacerlo sin macros, sólo con fórmulas. En esta nota describo la solución que propuse.

Empezamos por definir una constante matricial, "meses", cuyo objetivo veremos más adelante

meses={"enero";"febrero";"marzo";"abril";"mayo";"junio";"julio";"agosto";"setiembre";"octubre";"noviembre";"diciembre"}

Ahora necesitamos una fórmula que extraiga el nombre de la hoja activa. Para esto usamos una fórmula con las funciones EXTRAE y CELDA

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

Esta fórmula funciona de la siguiente manera:

1 – la fórmula =CELDA("filename",A1) da como resultado el nombre completo del archivo. Por ejemplo, en una hoja llamada Marzo la fórmula daría "D:\Blog\[horas mantenimiento.xls]Marzo".



Hay que tener en cuenta que la función CELDA sólo con archivos que hayan sido guardados previamente. Así que antes de poner esta fórmula por primera vez, debemos guardar el archivo.

2 – La fórmula =ENCONTRAR("]",CELDA("filename",A1))+1 nos da la posición del símbolo "]", que funciona como indicador de donde empieza el nombre de la hoja. En nuestro ejemplo, la fórmula da 34



3 – Usamos ambas fórmulas como argumentos en la función EXTRAE, para extraer el nombre de la hoja.

Nuestro próximo paso es "armar" la fecha en la línea 2, usando el nombre del mes que obtenemos del nombre de la hoja. Para esto ponemos en la celda B2 esta fórmula

=FECHA(2008,COINCIDIR(EXTRAE(CELDA("filename",A1),ENCONTRAR("]",CELDA("filename",A1))+1,256),meses,0),1)

Aquí ven el uso del nombre "meses". Como no podemos usar el nombre de la hoja, que es texto, para armar la fecha con la función FECHA, calculamos la posición del nombre del mes en la constante matricial.

El resultado es "01/01/2008", pero como estamos interesados en exhibir sólo el número del día, usamos el formato personalizado "dd"



En las celdas del rango C2:AF2 ponemos sencillamente la fórmula C2+1, los que nos da la sucesión del día del mes.

Para obtener el día de la semana usamos esta fórmula

=ELEGIR(DIASEM(B2,2),"L","M","M","J","V","S","D")

En la función DIASEM ponemos 2 como valor del segundo argumento para indicar que el lunes es el primer día de la semana.

Nuestro modelo está casi completo, excepto por un pequeño problema. Por ejemplo, si creamos la hoja "Febrero", tendremos demasiadas columnas. Después del 29, veremos que aparece 01 y 02. La fecha completa es, por supuesto, 01/03/2008 y 02/03/2008. Recordemos que hemos dado formato "dd" a las celdas.
Para corregir esto podemos hacer sencillamente eliminar las columnas innecesarias. Pero mucho más elegante es usar Formato Condicional, para hacer desaparecer las columnas supernumerarias.
Todo mes tiene necesariamente 28 días. Por lo tanto aplicamos el formato condicional sólo a las columnas AD, AE y AF. Después de seleccionar las tres columnas, abrimos el menú de formato condicional y en la opción "la fórmula es", ponemos esta fórmula

=MES(AE$2)<>MES($B$2)

Es decir comparamos el mes de las últimas tres fechas con el mes de la primer fecha. En caso de ser distinto, aplicamos el formato condicional



En la pestaña Fuente ponemos el color blanco (o el mismo color del fondo); en la pestaña Bordes elegimos la opción "ninguno" y en la pestaña Tramas elegimos la opción "sin color".
Ahora, sólo veremos las columnas de los días del mes en cuestión.

Technorati Tags: MS Excel

Operaciones con rangos que contienen errores

Si una fórmula en nuestra hoja de Excel se refiere a un rango que contiene errores, como #N/A, el resultado será también error.
Si el rango contiene sólo algunas celdas, una solución es, sencillamente, borrar el contenido de las celdas con error.
Pero hay situaciones en las cuales no queremos borrar el contenido de las celdas ya sea porque se trata de una gran cantidad de datos o por cualquier otro motivo.
En esos casos necesitamos crear fórmulas que ignoren las celdas con un valor de error en el rango.
En esta nota veremos como sumar, contar e imitar la función SUMAPRODUCTO en estas situaciones.

Empezamos por considerar un rango en el cual hay vatios tipos de errores (de hecho, todos los errores que se pueden dar en Excel)




Si usamos la fórmula =SUMA(A2:A11), el resultado será #¡VALOR!.

Para superar este inconveniente usamos esta fórmula matricial

={SUMA(SI(ESERROR(A2:A11),0,A2:A11))}

Como siempre recordamos que las fórmulas matriciales son introducidas en las celdas pulsando simultáneamente Ctrl+Mayúsculas+Enter.

Esta fórmula crea un vector donde si un valor del rango da resultado error al ser evaluado por la función ESERROR, este valor es convertido a 0. En caso contrario se mantiene el valor original. La función SUMA realiza la operación con los valores del vector creado por la función SI.

Si queremos contar cuantos valores que no son error hay en el rango usamos esta fórmula matricial

={SUMA((NO(ESERROR(A2:A11)))*1)}

Esta misma fórmula puede escribirse de esta forma más "académica"

={SUMA(--NO(ESERROR(A2:A11)))}

También podemos usar funciones base de datos para resolver este problema.
Si el tipo de error es el mismo en todas las celdas erróneas del rango, por ejemplo #N/A , como en esta situación



definimos el área de criterios en el rango C1:C2 como mostramos en la imagen arriba y creamos la fórmula

=BDSUMA(A1:A11,A1,C1:C2)

Para contar cuantos valores no erróneos hay en la lista usamos esta otra fórmula

=BDCONTAR(A1:A11,A1,C1:C2)

Si tenemos distintos tipos de valores en la lista, en lugar de crear varios criterios, podemos usar un único criterio, como se muestra aquí



Hay que prestar atención que el área de criterios sigue siendo el rango C1:C2, pero hemos dejado la celda C1 vacía y en la celda C2 tenemos una fórmula y no un valor. Las fórmulas para sumar y contar son idénticas a las del ejemplo anterior.



Finalmente tenemos que enfrentarnos con el caso que el rango con errores aparezca en una función SUMAPRODUCTO. En este caso tenemos que desistir de usar esta función e imitarla con una fórmula matricial.
Si queremos obtener la suma de los productos de los miembros de las listas 1 y 2 como en esta imagen



tendremos que usar esta fórmula matricial

={SUMA(SI(ESERROR(A2:A11*B2:B11),0,(A2:A11*B2:B11)))}

Si tenemos más de dos vectores, es conveniente usar nombres para señalar los rangos. Por ejemplo, en este caso creamos tres nombres Lista1, Lista2 y Lista3



El cuaderno con los ejemplos se puede descargar aquí

Technorati Tags: MS Excel

Convertir datos de matriz a columna o fila en Excel

Hace unos días atrás me dejaron en un comentario esta consulta:

COMO PUEDO PASAR LOS DATOS DE UNA MATRIZ A SOLO UNA COLUMNA??

En esta nota veremos como hacerlo, no sólo cómo poner todos los datos de una matriz en una sola columna, sino también en una sola fila.

Actualización: mucho más fácil y eficiente hacerlo con Power Query; ver este post

Empecemos por plantearnos una matriz, que por comodidad, será de tres fila por tres columnas



Nuestro objetivo es crear una columna que contenga todos los elementos de la matriz



Mi primer paso fue decidir si hacerlo con fórmulas o con macros. Decidí hacerlo con fórmulas.

El archivo con las fórmulas puede descargarse aquí.

De todas las funciones la que me pareció más apropiada es DESREF. Como ya hemos explicado en el pasado, esta función tiene cinco argumentos




En este caso, nuestro "ancla" será toda la matriz, el rango A1:C3. Este rango lo definimos en un nombre, "matriz", para mayor comodidad.

Para obtener el primer elemento de la matriz usamos la fórmula

=DESREF(matriz;0;0;1;1)



Para poner el segundo elemento de la matriz en la columna usamos la fórmula

=DESREF(matriz;1;0;1;1)



A esta altura de los acontecimientos está claro que nuestra fórmula tiene que cambiar en forma dinámica el argumento de la fila y de la columna. Es decir, una fórmula que a medida que la copiamos a lo largo de una columna, vaya generando en forma dinámica los elementos de la matriz.

Para resolver el número de fila usaremos esta fórmula

=RESIDUO(FILA()-FILA($E$2);FILAS(matriz))

Esta fórmula produce estos resultados



Usamos $E$2 como argumento ya que en esa celda empezará nuestra columna.

Ahora tenemos que lograr que el argumento de la columna cambie de 1 a 3, en nuestro caso, cada tres filas. Para esto usaremos la fórmula

=TRUNCAR((FILA()-FILA($E$2))/FILAS(matriz))

Los resultados de esta fórmula son



A los argumentos de alto y ancho de DESREF les damos un valor de 1. Finalmente, nuestra fórmula será

=DESREF(matriz;RESIDUO(FILA()-FILA($E$2);FILAS(matriz));TRUNCAR((FILA()-FILA($E$2))/FILAS(matriz));1;1)

En nuestro ejemplo empezamos la columna en la celda E2 y obtenemos este resultado



Esta fórmula copia los elementos de la matriz primero por columna y luego por fila. Si queremos copias los elementos primero por fila y luego por columna, usamos la fórmula anterior modificada de la siguiente manera

=DESREF(matriz;TRUNCAR((FILA()-FILA($E$2))/COLUMNAS(matriz));RESIDUO(FILA()-FILA($E$2);COLUMNAS(matriz));1;1)

Si queremos copiar los elementos de la matriz a lo largo de una fila tendremos que usar esta fórmula

=DESREF(matriz;TRUNCAR((COLUMNA()-COLUMNA($B$13))/COLUMNAS(matriz));RESIDUO((COLUMNA()-COLUMNA($B$13));COLUMNAS(matriz));1;1)

para copiar por columna y luego por fila, o ésta para copiar primero por fila y luego por columna

=DESREF(matriz;RESIDUO((COLUMNA()-COLUMNA($B$14));FILAS(matriz));TRUNCAR((COLUMNA()-COLUMNA($B$14))/(FILAS(matriz)));1;1)




Esta nota está basada en las fórmulas desarrolladas por Chip Pearson.

Technorati Tags: MS Excel

Calcular Porcentaje de Subtotales en tablas dinámicas de Excel

Un lector me pregunta como podemos calcular porcentajes de subtotales en una tabla dinámica de Excel.
Excel permite mostrar porcentajes del total de una tabla con facilidad, pero no tiene ningún método incorporado para calcular los porcentajes de subtotales. Veamos a qué me refiero.

Supongamos esta lista de datos



Con facilidad podemos crear una tabla dinámica que totalice las ventas por región y agente



Luego podemos usar el menú de opciones de campo para mostrar los resultados como porcentaje del total



Todo esto con algunos clics del Mouse!

Pero qué pasa si queremos calcular el porcentaje de cada agente sobre el total de cada región? Para hacer esto tendremos que dar un rodeo.

Empezamos por crear un campo auxiliar en nuestra lista de datos, que llamamos "% de Región"



Los valores de este campo los calculamos con la fórmula:

=C2/SUMAR.SI($B$2:$B$13,B2,$C$2:$C$13)

que calcula el porcentaje de cada valor sobre el total de los valores de la región. Este nuevo campo lo arrastramos al área de datos



Para evitar que Excel totalice los subtotales y muestre un total del 400%, cancelamos la opción Total de Columnas en el menú de opciones de tabla.

Technorati Tags: MS Excel

Búsqueda por campo con INDICE y COINCIDIR

Supongamos que tenemos una lista como ésta



Queremos crear una fórmula que nos permita hacer una búsqueda o por nombre o por número de identidad. Esta situación refleja varias de las consultas que he recibido últimamente.

Podemos pensar en una solución usando BUSCARV, pero en esta nota veremos como hacerlo usando INDICE, COINCIDIR y validación de datos.

Empezamos por crear dos nombres, que usaremos en validación de datos:

Identidad = Hoja1!$B$2:$B$8
nombre = Hoja1!$A$2:$A$8

En otro lugar de la hoja creamos una tabla donde mostraremos los datos extraídos



En la celda A12 creamos una lista desplegable con Validación de datos



En la celda B12 también creamos una lista desplegable. Aquí usamos la función INDIRECTO para que en la lista aparezcan los valores del rango del nombre que ha sido elegido en la celda A12



En las celdas C12 y D12, donde queremos que aparezcan los datos buscados, ponemos esta fórmula, basada en INDICE y COINCIDIR:

Para el campo Edad: =INDICE(C2:C8,COINCIDIR(B12,INDIRECTO(A12),0))

Para el campo Profesión: =INDICE(D2:D8,COINCIDIR(B12,INDIRECTO(A12),0))

Hay que prestar atención al uso de la función INDIRECTO para interpretar el valor de la celda A12 como rango nominado.



Un detalle a agregar es dar formato condicional a las celdas C12 y D12, para evitar ver el valor #N/A cuando cambiamos el valor de la celda A12




Usamos la función ESERROR y el color de la fuente, en caso de ser VERDADERO, blanco. De esta manera, en caso de error, el contenido de la celda se vuelve "invisible".

El archivo del ejemplo se puede descargar aquí

Technorati Tags: MS Excel

Generar hojas con nombre a partir de una lista

Una de las consultas que recibo con bastante frecuencia es cómo generar hojas en un cuaderno a partir de una lista.
Supongamos que tenemos una hoja en un cuaderno con la lista de los meses del año.



Queremos que una macro genere 12 hojas en el cuaderno, un para cada mes, y les ponga el nombre del mes



Esto podemos hacerlo con una macro bastante sencilla

Sub crear_hojas()
Dim Lista As Range
Dim iX As Long

Set Lista = Application.InputBox(prompt:="Señalar rango de la lista", _
Title:="Lista de nombres", Type:=8)


Application.ScreenUpdating = False

For iX = Lista.Count To 1 Step -1
Sheets.Add.Name = Lista(iX)
Next iX

Sheets(1).Select

Application.ScreenUpdating = True

End Sub

Al correr esta macro, se abre un diálogo donde ponemos el rango que contiene la lista (pueden ser meses, días de semana o cualquier otra cosa); al pulsar el botón Aceptar



se generarán las hojas en el orden de la lista.

Si queremos usar esta macro con cierta frecuencia o pasársela a un compañero del trabajo, descubriremos que tenemos que hacer algunas mejoras.
Por ejemplo, si en el diálogo de elegir rango pulsamos Cancelar, se abrirá el editor de Vba y veremos este mensaje de error



Para evitar esto usamos la sentencia On Error

Sub crear_hojas()
Dim Lista As Range
Dim iX As Long


On Error GoTo Cancelar

Set Lista = Application.InputBox(prompt:="Señalar rango de la lista", _
Title:="Lista de nombres", Type:=8)


Application.ScreenUpdating = False

For iX = Lista.Count To 1 Step -1
Sheets.Add.Name = Lista(iX)
Next iX

Sheets(1).Select

Application.ScreenUpdating = True

Cancelar:End Sub
Otro problema puede generarse cuando ya existe en el cuaderno una hoja con uno de los nombres que aparecen en la lista. Por ejemplo, si la hoja Enero existe antes de correr la macro, obtenemos este resultado



Para evitar este problema tendremos que hacer algunas modificaciones a nuestra macro. Primero creamos una función para verificar si una hoja a crear ya existe


Function chequear_hoja(sheetName As String) As Boolean

Dim wkb As Worksheet

On Error Resume Next
Set wkb = Sheets(sheetName)
On Error GoTo 0

chequear_hoja = IIf(Not wkb Is Nothing, True, False)
End Function
Esta función da como resultado FALSE si la hoja no existe, y TRUE si la hoja ya existe en el cuaderno.
Lo que hacemos es usar esta función (adaptada de la propuesta por Colo) en nuestra macro para crear una condición. Si la hoja no existe, entonces creamos una nueva con el nombre correspondiente; de existir, pasamos a verificar el próximo nombre en la lista.

Sub crear_hojas2()
Dim Lista As Range
Dim iX As Long


On Error GoTo Cancelar

Set Lista = Application.InputBox(prompt:="Señalar rango de la lista", _
Title:="Lista de nombres", Type:=8)


Application.ScreenUpdating = False

For iX = Lista.Count To 1 Step -1
If chequear_hoja(Lista(iX)) = False Then
Sheets.Add.Name = Lista(iX)
End If
Next iX

Sheets("Hoja1").Activate

Application.ScreenUpdating = True

Cancelar:
End Sub
El cuaderno con las macros se puede descargar aquí



Technorati Tags: MS Excel

Operaciones con rangos discontinuos en Excel.

No todas las funciones de Excel permiten realizar cálculos con rangos discontinuos. Por ejemplo, supongamos estos dos rangos, A1:A3 y A5:A8




Supongamos que por algún motivo no podemos operar con el rango A1:A8. Si queremos sumar todos los valores en los dos rango podemos usar la fórmula

=SUMA(A1:A3,A5:A8)

Lo mismo si queremos contar cuántos elementos hay en ambos rangos

=CONTAR(A1:A3,A5:A8)

Pero si queremos usar CONTAR.SI para averiguar cuantos números mayores a 200 hay en los dos rangos, la fórmula

=CONTAR.SI((A1:A3,A5:A8),">200")

da como resultado #¡VALOR! Lo mismo sucede si queremos usar SUMAR.SI

La solución, por lo general, será combinar dos funciones CONTAR.SI. En nuestro caso sería

=CONTAR.SI(A1:A3,">200")+CONTAR.SI(A5:A8,">200")

El problema con esta solución es que en muchos casos tenemos que combinar muchas funciones CONTAR.SI, lo que convierte en engorrosa la tarea de construir nuestra fórmula.
Veamos cuáles son nuestras posibilidades:

Una posibilidad más compacta es ésta (propuesta por Juan Pablo González en el foro de MrExcel):

=SUMA(CONTAR.SI(INDIRECTO({"A1:A3";"A5:A8"}),">200"))

o utilizar la función ARRAY.JOIN del complemento Morefunc que tantas veces he recomendado

={SUMA((ARRAY.JOIN(A1:A3,A5:A8)>200)*1)}

Esta última fórmula es matricial y debe introducirse en la celda apretando simultáneamente Ctrl+Mayúsculas+Enter.

Los "puristas" pueden utilizar también

=SUMA(--(ARRAY.JOIN(A1:A3,A5:A8)>200))

donde usamos el doble signo menos (--) para forzar la conversión de valores lógicos a 1 (VERDADERO) o 0 (FALSO).

Ambas fórmulas dan como resultado 6.

Para sumar condicional podemos usar estas posibilidades:

=SUMA(SUMAR.SI(INDIRECTO({"A1:A3";"A5:A8"}),">200"))

una variante de la fórmula con INDIRECTO, adaptada a suma condicional, o

=SUMAPRODUCTO((ARRAY.JOIN(A1:A3,A5:A8)>200)*ARRAY.JOIN(A1:A3,A5:A8))

donde usamos SUMAPRODUCTO junto con ARRAY.JOIN

Podemos, y es recomendable, usar rangos nominados. Definimos dos nombres, cada uno refiriéndose a cada uno de los rangos

rango1 =Hoja1!$A$1:$A$3
rango2 =Hoja1!$A$5:$A$8

y utilizarlos en nuestras fórmulas. Por ejemplo:

=SUMA(SUMAR.SI(INDIRECTO({"rango1";"rango2"}),">200"))

Habrán notado que en las fórmulas con INDIRECTO usamos la expresión {"A1:A3";"A5:A8"}. Esta expresión crea un matriz en base a los datos de los rangos expresados como texto (que aparecen entre comillas y por ese motivo usamos INDIRECTO). Pueden consultar esta nota sobre funciones y constantes matriciales.

Technorati Tags: MS Excel

Gráficos con imágenes - algunos trucos

Una buena forma de enfatizar la representación de un presupuesto en un gráfico es mostrar un billete partido en pedazos, donde cada porción representa en forma proporcional un concepto del presupuesto



La técnica para hacerlo esta explicado en mi blog sobre gráficos y presentación de datos, en la nota Gráfico de presupuesto con Autoformas.

Si quieren el mismo gráfico, pero orientado horizontalmente



pueden leer la nota Otro gráfico de presupuesto con imágenes.

Technorati Tags: MS Excel

La vida (en Excel) es más sencilla con Tablas Dinámicas

En las últimas semanas he recibido varias consultas que tienen un común denominador: no conocer o no apreciar cabalmente las ventajas de usar tablas dinámicas.

Queremos manejar una serie de datos, para lo cual los vamos registrando en una hoja. Luego en otra hoja del cuaderno montamos un resumen de los datos, lo que por lo general nos llevará a armar fórmulas bastante complicadas.
Pero la vida en Excel puede ser mucho más sencilla si sabemos explotar las posibilidades de tablas dinámicas.

Veamos el caso de un lector que quiere manejar un registro de tasas de cambios del dólar y del euro frente al peso chileno. En una planilla se van registrando las tasas de cambio por día. El problema de mi lector, tal como lo describe en su consulta es:

…en otra planilla … tengo un cuadro [con] los promedios de cada mes, entonces cuando están llenos los campos del mes de agosto por ejemplo se llena la casilla con el valor promedio de agosto, y asi una serie de indicadores…
Mi idea es que exista una casilla en la cual se pueda elegir año, mes y dia...y busque los valores segun los datos proporcionados, o sea que si elijo en año 2006, me muestre en pantalla el promedio de enero de 2006 en su respectiva casilla, el promedio de febrero de 2006 en su respectiva casilla etc. y si lo cambio a 2007 haga lo mismo y asi con con "n" años mas

Esta tarea es sencilla si usamos tablas dinámicas y en esta nota mostraremos cómo hacerlo.
En una hoja de Excel ponemos los datos de tipo de cambio del peso chileno frente al euro y al dólar en los años 2006 y 2007. Los datos los descargamos gratuitamente del sitio Oanda. El nombre de la hoja será "BD" (base de datos)





Hemos puesto los datos del dólar (USD) y del euro (EUR) en dos columnas contiguas para evitar tener que duplicar las líneas con las fechas. Esto nos obligará a hacer una pequeña manipulación en nuestra tabla dinámica.

Una vez que hemos completado nuestra base de datos, armamos la tabla dinámica con el menú Datos—Informe de tablas y gráficos dinámicos



Arrastramos el campo Fechas al área de campos de filas y los campos USD y EUR al área de datos



Ahora empezamos a hacer nuestras manipulaciones en la tabla. Primero hacemos clic sobre "Datos" y lo arrastramos sobre "Total"



Como ven, ha desaparecido la columna Total, que no necesitamos. También eliminamos el total de las columnas abriendo el menú Opciones de Tabla y quitando la marca de las opciones Totales Generales de filas y columnas.

Ahora agrupamos las filas por mes y por año con el menú Agrupar de las tablas dinámicas



y eligiendo las opciones "meses" y "años"



En este momento nuestra tabla nos muestra la suma de las tasas de cambio de cada mes para moneda. Para cambiar la función a "promedio", abrimos el menú Configuración de campo y elegimos la función promedio



Hacemos lo mismo para el campo de USD. Ahora cambiamos el formato de los números y ya tenemos nuestra tabla con los promedios por año y por mes. Cinco minutos de trabajo y éste es el resultado



Si queremos agregar promedios anuales, abrimos el menú de configuración de campo para "Fecha"



y marcamos "Subtotales"



Todo lo que nos queda por hacer es definir el rango de la tabla en forma dinámica, como esta explicado en la nota del enlace. De esta manera podemos seguir agregando datos a nuestra base de datos, sin necesidad de redefinir el rango de la tabla dinámica.

Technorati Tags: MS Excel

Nota sobre gráficos enlazados

En mi blog sobre gráficos y presentación de datos con Excel, acabo de publicar una nota sobre gráficos enlazdos.
La nota se generó a partir de la consulta de uno de mis lectores sobre cómo crear una situación de "drill down" en un gráfico. Es decir, supongamos que tenemos un gráfico de columnas y al hacer clic en una de las columnas, pasamos a otro gráfico que detalla los datos de la columna.
Las explicaciones y los detalles de cómo crear estos gráficos se pueden leer en la nota mencionada.

Technorati Tags: MS Excel

Resolver el error #NUM en el cálculo de la media geométrica en Excel

Esta nota será un tanto exótica para la mayoría de mis lectores, pero supongo que será útil para otros, como lo ha sido para una lectora de México que me consulta sobre la función MEDIA.GEOM.
Esta función, citando la ayuda de Excel devuelve la media geométrica de una matriz o de un rango de datos positivos. Por ejemplo, es posible utilizar la función MEDIA.GEOM para calcular la tasa de crecimiento promedio, dado un interés compuesto por tasas variables.

El uso de la palabra "devuelve" para indicar cuál es el resultado previsto de una función, sigue molestándome a pesar de los años que llevo leyéndola en la ayuda de Excel y en distintas notas y comentarios. No le hemos dado nada a la función, por lo que no veo que es lo que tendrá para devolvernos. Pero dejemos las cuestiones del lenguaje para otra oportunidad.

Mi lectora quería saber por qué la función MEDIA.GEOM que usaba en su cálculo daba como resultado el error #NUM, aún después de haber revisado que todos los valores en el rango fuesen numéricos.
Para entender por qué se genera el error empecemos por definir media geométrica:

la raíz n-ésima del producto de n números.

Cuando nuestro rango de número incluye números de gran magnitud puede generarse una situación de "overflow".

En elarchivo del ejemplo hay una lista con 50 valores.
En la columna B calculamos en cada línea el producto de todos los valores de la columna A hasta esa línea incluida. Podemos ver que al llegar a la línea 50, el resultado es #NUM



La forma de resolver el problema, es usar logaritmos, aquellos viejos (y odiados) conocidos de la época del secundario.
Empezamos por calcular los logaritmos de cada valor con la función LN




Luego calculamos el promedio de los logaritmos



Y finalmente calculamos la inversa con la función EXP, es decir, elevamos el número e al resultado del promedio



Podemos resumir todo este proceso en una sola fórmula matricial



La fórmula matricial =EXP(PROMEDIO(LN(A2:A51))) la introducimos pulsando simultáneamente Ctrl+Mayúsculas+Enter.

La idea de solucionar le problema con el uso de logaritmos fue tomada de la página Geometric Mean Calculations publicada por el Dr. Joe Costa

Technorati Tags: MS Excel