Grafico de columnas con maximo y minimo marcados

Existen situaciones en las cuales cuando representamos una tabla de valores en un gráfico de columnas, no es fácil distinguir entre el valor máximo y el mínimo de la serie.
Una posibilidad es dar color distinto a a las columnas que representan estos valores, como mostramos en la nota de ayer.
Otra posiblidad es agregar una flecha de cloque con el texto "Max" o "Min" sobre la columna correspondiente, como en este ejemplo



La técnica para hacerlo está explicada en la nota Remarcar máximos y mínimos en gráfico de columnas de Excel, en mi blog sobre gráficos y presentación de datos.

Technorati Tags: MS Excel

Formato condicional en graficos Excel de columnas

En mi blog sobre gráficos y presentación de datos en Excel acabo de publicar una nota sobre cómo lograr que las columnas de un gráfico cambien de color en función del valor que representan.

Supongamos que queremos señalar en un gráfico de columnas que representa las ventas del año por meses, en que meses las ventas han estado por debajo de un mínimo esperado, en qué meses han estado en los valores aceptados y en que meses han superado estos valores. Por ejemplo, este gráfico



donde los meses con ventas por debajo de los 50.000 aparecen en rojo; los meses con ventas entre 50.000 y 80.000 en verde y los meses que superan los 80.000 en azul.

El archivo con el ejemplo se puede descargar aquí

Technorati Tags: MS Excel

Gráficos de columnas flotantes en Excel

En mi blog sobre gráficos y presentación de datos con Excel, acabo de publicar una nota sobre cómo construir un gráfico de columnas flotantes.

La nota surgió como respuesta a una consulta de uno de mis lectores

llevo semanas investigando si se puede hacer una gráfica donde cada barra sea un rango de valores... es decir, por ej la primera barra vaya de 30 a 50, la segunda barra de 90 a 234 y la tercera de -23 a 45...

Espero que encuentren la nota útil.

Technorati Tags: MS Excel

Escala logarítmica en gráficos de Excel

Supongamos que tenemos esta serie de datos que queremos representar en un gráfico de Excel

Elegimos el gráfico de dispersión y este es el resultado



Como pueden ver el gráfico, por decirlo con suavidad, no sirve para nada y en el mejor de los casos sólo puede crear confusión.
A pesar que el punto 2 es dos veces y media más grande que el punto 1, ambos en el gráfico ambos parecen tener la misma magnitud. Peor todavía con el punto 4 que es 12,5 veces mayor que el punto 1.
Ahora hagamos lo siguiente: seleccionamos el eje de la Y y abrimos el menú de formato del eje



En la parte inferior del diálogo marcamos la opción Escala Logarítmica. El resultado será el siguiente:



Como pueden ver, tenemos ahora un gráfico claro y explicativo. Excel permite en cierto tipo de gráficos usar escalar logarítmicas lo que nos permite representar en un gráfico valores de magnitudes distintas, como el de nuestro ejemplo.
Para mejorar aún más el gráfico podemos agregar rótulos con los valores



Intuitivamente entendemos que el gráfico representa los puntos de acuerdo a sus magnitudes. Pero si queremos corregir la escala del eje de las Y, de manera que el máximo sea 15000 y no 100000, veremos que no podemos hacerlo con las opciones nativas de Excel.

Si queremos que la escala del eje de las Y de nuestro gráfico se extienda del 0 a 15000, tendremos que usar otra técnica.
Empezamos por calcular los logaritmos de los valores de la tabla (no tengan miedo, Excel tiene la función LOG para esta tarea)



Y representamos estos nuevos datos en un gráfico de dispersión



Ahora borramos de nuestro gráfico de las líneas de división y las marcas del eje



quedando este gráfico



Ahora tenemos que crear una serie de valores para las líneas de división del eje de las Y. En una tabla ponemos los valores que queremos que aparezcan en la escala de la Y, y calculamos sus logaritmos. Entre ambos valores ponemos una serie de valores 0



Seleccionamos Valores X y Log Y de la tabla y los agregamos al gráfico, seleccionándolo y abriendo el menú Gráfico—Agregar Datos



y luego



El resultado se verá de la siguiente manera



Ahora tenemos que ocuparnos de la nueva serie que acabamos de agregar para crear la escala y las líneas de división del eje de la Y. Empezamos por seleccionar la serie y abrir el diálogo de Formato de serie de datos



poniendo los valores de Línea y Marcador a "ninguno". En Rótulo de datos señalamos Valor de X



Vamos a la pestaña de Barras de Error y en la opción Valor Fijo ponemos 8



El resultado es un tanto desalentador, pero enseguida lo corregiremos



Seleccionamos las barras de error y abrimos el diálogo de Formato de Barras de Error. Allí ponemos un formato más conveniente



Ahora nos tenemos que ocupar de los rótulos de la Y, que por ahora aparecen todos como 0.
Empezamos por cambiar la ubicación de los rótulos a la izquierda del eje



Ahora, seleccionamos el primer rótulo de la serie (con un segundo clic), en la barra de las fórmulas ponemos el signo = y seleccionamos la celda que contiene el valor de la línea (en nuestro caso A11).



Repetimos el procedimiento para el resto de las líneas de división y obtenemos este gráfico



Si no estamos satisfechos con el resultado, podemos cambiar los valores en la tabla y obtener mejores resultados visuales. Por ejemplo, las líneas divisorias del 10 y del 15 quedan muy cerca una de la otra. Cambiamos el 10 en la tabla por 5



Y obtenemos este gráfico mejorado



Este gráfico es "semilogarítmico", es decir, sólo uno de los ejes tiene una escala logarítmica. También podemos crear gráficos "doble logarítmicos" donde ambos ejes tienen una escala logarítmica.

Technorati Tags: MS Excel

Funciones matriciales de rango – Un ejemplo

En el pasado en mis notas sobre fórmulas matriciales mencionaba que las funciones matriciales de Excel también pueden usarse para obtener el resultado de un cálculo en varias celdas simultáneamente, lo que llamamos fórmulas matriciales "multicelulares".
En una nota más reciente sobre cómo extraer elementos únicos de un rango en Excel mencionaba el uso de la función UNIQUEVALUES del complemento MoreFunc de Laurent Longre.

Menciono esto porque en esta nota mostraré cómo combinar todos estos ingredientes para resolver una consulta de uno de mis lectores.

La situación es la siguiente: en una hoja de Excel tenemos esta tabla



En una segunda tabla queremos poner debajo del nombre del cliente a que empresa pertenece



Como no puede ser de otra manera, queremos que esta segunda tabla sea dinámica. Es decir, si cambiamos el nombre del cliente en el encabezamiento, los valores deberán adaptarse automáticamente. Lo mismo si cambiamos la ubicación de las "X" en la primer tabla.

Nuestro primer paso consiste en crear una fórmula matricial que de como resultado un rango de valores.
Debemos prestar atención al hecho que en la primer tabla, los clientes están ordenados en columna y las empresas en filas, es decir, a la inversa de lo que queremos obtener en la segunda tabla.
Empezamos por crear la fórmula matricial que nos de los nombres de las empresas por cliente donde haya una X y si no la hay, un espacio en blanco. Tomando como ejemplo el primer nombre de la segunda tabla, LUIS, escribimos esta fórmula matricial

={SI(I5:N5="X";I2:N2;"")}

simultáneamente en el rango B3:E3, presionado al mismo tiempo Ctrl+Mayúsculas+Enter



Para "girar" el rango 90 grados, usamos la función TRANSPONER, que también es matricial

={TRANSPONER(SI(G5:L5="X";G2:L2;""))}



Nuestro próximo paso es convertir nuestro modelo en dinámico. Para esto creamos nombres que contengan los rangos de los clientes:

JUAN ='Hoja1 (3)'!$G$3:$L$3
LUCAS='Hoja1 (3)'!$G$8:$L$8
LUIS ='Hoja1 (3)'!$G$5:$L$5
MARIO='Hoja1 (3)'!$G$6:$L$6
PEDRO='Hoja1 (3)'!$G$7:$L$7
ROSA ='Hoja1 (3)'!$G$4:$L$4
SONIA='Hoja1 (3)'!$G$10:$L$10
VICTOR='Hoja1 (3)'!$G$9:$L$9

Estos nombres nos servirán para crear una referencia dinámica en nuestra fórmula con la ayuda de la función INDIRECTO. Reemplazamos nuestra fórmula anterior por

{=TRANSPONER(SI(INDIRECTO(B$2)="X";$G$2:$L$2;""))}



Ahora, si cambiamos el nombre en el encabezamiento, los resultados cambiarán automáticamente.

Nuestra solución es casi perfecta. Nos falta eliminar los espacios en blanco entre los nombres de las empresas. Para esta tarea usaremos la función UNIQUEVALUES del complemento MoreFunc ya mencionado. Esta función también es matricial y debe ser introducida pulsando al mismo tiempo Ctrl+Mayúsculas+Enter

{=UNIQUEVALUES(TRANSPONER(SI(INDIRECTO(B$2)="X";$G$2:$L$2;"")))}

Nuestro modelo se ve ahora así



que es lo que queríamos obtener.

Technorati Tags: MS Excel

Extraer elementos únicos de un rango en Excel

Ya hemos visto cómo generar una lista de elementos únicos a partir de un rango en una columna en Excel. Para esta tarea usamos Datos—Filtro--Filtro Avanzado—Elementos Únicos.
El problema surge cuando el rango incluye más de una columna. En estos casos no podemos usar Filtro Avanzado.
Dado que últimamente he recibido varias consultas sobre este tema, presentaré aquí dos soluciones posibles.

Supongamos esta situación




En este rango tenemos 6 elementos, pero sólo 4 elementos únicos.

Para generar un lista de elementos únicos podemos usar la función UNIQUEVALUES que forma parte del complemento MoreFunc que tantas veces he recomendado y que recomiendo descargar e instalar.
Una vez instalado el complemento, seleccionamos la función con el asistente de funciones seleccionando la categoría MoreFunc




Dado que esta función da como resultado una matriz, pero un una celda sólo podemos ver un resultado, la combinamos con la función INDICE para exponer todos los elementos de la matriz en un rango de celdas. La fórmula, en nuestro ejemplo, es

=INDICE(UNIQUEVALUES($A$1:$C$2);FILAS($5:5))

que ponemos en la celda A5 y copiamos hasta la celda A9




También podemos usar esta variante con la función FILA en lugar de la función FILAS

=INDICE(UNIQUEVALUES($A$1:$C$2);FILA()-4)

Este complemento incluye también la función COUNTDIFF que da como resultado el número de elementos únicos en el rango




Otra alternativa es copiar el código de la función UNIQUEITEMS de John Walkenbach. Esta función también permite contar el número de elementos únicos en un rango y, combinándola con la función TRANSPONER, generar una lista de elementos únicos





La fórmula con la función TRANSPONER debe ser introducida como fórmula matricial (pulsando al mismo tiempo Ctrl+Mayúsculas+Enter)

Technorati Tags: MS Excel

Es posible jubilarse a los 40?

Hace unas semanas atrás publiqué en este una entrada sobre como hacerse millonario con Excel (y también advertía allí que no tengo la menor idea de cómo hacerlo, con o sin Excel).

Por una coincidencia me encuentro ayer una nota de Dick Kusleika en Daily Dose of Excel en la que menciona un artículo de John Walkenbach, How to retire at age 40 (cómo jubilarse a los 40), quien a su vez menciona un artículo publicado en MoneyCentral.msn.com sobre el tema.

Al día siguiente John Walkenbach publicó en su blog J-Walk Blog el modelo en Excel basado en las premisas de la nota de MoneyCentral.

El modelo que propone Walkenbach tiene un mecanismo de "autoajuste". La idea es que cuando el monto anual de los intereses sobre el ahorro igualan el ingreso anual, ese el momento en que podemos retirarnos (y seguir disfrutando del ingreso corriente al cual estamos acostumbrados).
Este enfoque ignora la existencia de la inflación y de la erosión del ingreso real. También ignora la existencia de impuestos al ahorro, que existen en varios países. Es decir, el modelo puede encontrar su punto de equilibrio en un ingreso muy bajo.

Por eso me parece más acertado este modelo para calcular la edad de jubilación en el cual nos fijamos como meta el ingreso anual con el cual queremos vivir cuando nos retiremos y luego hagamos el cálculo cuando podremos convertir nuestro sueño en realidad.

En definitiva, he agregado al modelo de Walkenbach los parámetros inflación anual promedio, impuestos al ahorro y jubilación anual deseada. La inclusión de estos parámetros hace el cálculo más realista.

Por ejemplo, en mi modelo, tomando en cuenta una tasa de inflación del 3%, un interés anual del 7% sobre la inversión y un impuesto del 10% sobre los intereses, si empezamos a ahorrar a los 20 y queremos llegar a una jubilación de 60 mil anuales, tendremos que ahorrar hasta los 55 años.

Y, además, si alguien conoce algún joven de 20 años que esté dispuesto a ahorrar el 20% de su ingreso, me lo presenta por favor. Yo no conozco ninguno.

Technorati Tags: MS Excel

Ordenar texto en Excel con fórmulas.

Excel permite ordenar datos, ya sean texto o número, con facilidad y flexibilidad usando el menú Datos-Ordenar.
Pero hay situaciones en las cuales queremos realizar la tarea con fórmulas, como me consultaba uno de mis lectores hace unos días atrás.
La solución consiste en usar la función CONTAR.SI con un pequeño truco. Empecemos por plantear la situación. Supongamos esta lista de nombres que queremos ordenar alfabéticamente con fórmulas



El primer paso consiste en crear una columna auxiliar con esta fórmula:

=CONTAR.SI($A$2:$A$11;"<="&A2)

que copiamos a todo el rango de la columna B

Es importante notar que el segundo argumento de la función está formado por el texto "<=" concatenado con el operador "&" a la celda correspondiente de la columna A.



Como ven la función CONTAR.SI hace aquí las veces de la función JERARQUIA, que sólo funciona con números, no con texto.

El segundo paso consiste en crear una tabla con dos columnas. Una columna auxiliar donde ponemos los números de posición en forma ordenada y una segunda columna donde aparecerán los nombres. En esta columna ponemos esta fórmula

=INDICE($A$2:$A$11;COINCIDIR(D2;$B$2:$B$11;0))

Usamos INDICE y COINCIDIR ya que los números auxiliares en la tabla original están a la derecha de los nombres. Si estuvieran a la izquierda podríamos usar la función BUSCARV.



Como ven, tenemos nuestra lista ordenada en la tabla D1:E11.

Esta técnica tiene un serio inconveniente. Si un nombre aparece repetido, obtendremos un resultado #N/A



Para superar este problema creamos una segunda columna auxiliar. En esta columna ponemos esta fórmula relacionada a la primera columna auxiliar

=JERARQUIA(B2;$B$2:$B$11;2)+CONTAR.SI($B$2:B2;B2)-1

Ya hemos mostrado esta técnica para lograr "desempates" usando la función JERARQUIA.



Como ven, el primer Daniel recibe el número de orden 5 y el segundo, 6.

Todo lo que nos queda por hacer es aplicar la misma fórmula INDICE y COINCIDIR que usamos más arriba

Technorati Tags: MS Excel

Área de impresión dinámica en Excel, con macros.

La consulta que dio origen a la nota anterior sobre cómo determinar un área de impresión en forma dinámica, pedía hacerlo con macros.

Así que para saldar mi deuda con el lector mostramos aqu[i una de las formas de hacerlo con Vba.

Volviendo a nuestro ejemplo anterior,

podemos usar las celdas G2 y G3 con esta macro:

ActiveSheet.PageSetup.PrintArea = [G2] & ":" & [G3]

Cambiando los valores de las celdas G2 y G3, al correr la macro cambiará la selección del área de impresión.

Technorati Tags: MS Excel

Área de impresión dinámica en Excel, sin macros.

En mi última nota sobre nombres en Excel vimos que Excel crea nombres "reservados" para ciertas funciones, como determinar el área de impresión.
En respuesta a una consulta, vamos a mostrar cómo crear un área de impresión dinámica en una hoja, sin usar macros.

Como vimos, al determinar el área de impresión (Archivo-Configurar Página-Hoja), Excel crea el nombre local Área_de_impresión




Este nombre contiene un rango estático, pero podemos convertirlo en dinámico con esta relativamente simple técnica como mostramos en este ejemplo:

1 – definimos dos celdas que contendrán las direcciones de los vértices del rango que queremos imprimir. Por ejemplo en nuestro ejemplo el vértice superior izquierdo será la celda A1 y el vértice inferior derecho E15. En la celda G2 ponemos el valor A1 y en la celda G3 el valor E15



2 – Abrimos el diálogo de definición de nombres



y cambiamos la definición del nombre Área_de_impresión por la fórmula =INDIRECTO(CONCATENAR($G$2;":";$G$3))



Ahora podemos probar cambiar los valores en las celda G2 y G3 y veremos como el área de impresión va cambiando (veremos una línea partida delimitando el área seleccionada).

Technorati Tags: MS Excel