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

Tabla de Posiciones para campeonato de Fútbol – ampliación

Hace ya un tiempo que varios de mis lectores piden que amplíe el números de equipos en la tabla de posiciones en Excel para un campeonato de fútbol.

La nueva versión de la tabla de posiciones permite la participación de hasta 40 equipos.

Hay otros pedidos como adaptar la tabla para un campeonato de rugby y agregar el manejo de estadísticas.

En la medida que disponga de tiempo libre intentaré adaptar la tabla a un campeonato de rugby, en especial después del éxito de Los Pumas en el torneo mundial en Francia.

Un buen fin de semana para todos!

21-11-2007 Actualización: acabo de reemplazar el archivo, que tenía una falla, por uno corregido

10-12-2007 Actualización: nuevamente he reemplazado el archivo después de descubrir otro "bug", gracias al lector Juán José G.

Technorati Tags: MS Excel

Una vejez tranquila con Excel

Ayer hablamos del manejo de las finanzas familiares con Excel. Y como ya sabemos que millonarios no seremos, veamos si podemos asegurarnos una vejez tranquila.

Bien, tal como decía ayer, no se si podremos asegurarnos una vejez tranquila. Con Excel o si él. Pero sí podemos hacer algunos cálculos que tal vez nos ayuden en la toma de decisiones estratégicas en los que a nuestros gastos se refiere.

Veamos el siguiente escenario. Juan y José, ambos de 25 años de edad, tienen por delante 40 años de trabajo hasta llegar a la edad de retiro,
Ambos deciden ahorrar 500 pesos por mes durante 15 años. José decide empezar a ahorrar desde ese mismo momento y dejar de ahorrar a los 40 años. Juan decide esperar hasta los 50 años y entonces empezar a ahorrar, con lo cual terminará de ahorrar a los 65 años, la edad de la jubilación.
¿Cuál será la situación de cada uno de ellos al llegar a los 65 años de edad?

Construimos un pequeño modelo en Excel

En la celda B8 (Total a los 65 años) ponemos esta fórmula

=VF($B$1/12;B7*12;-$B$2)*(1+$B$1)^($B$3-B6-B7)

que copiamos también en C8

La primer parte de la fórmula usa la función VF (valor futuro) para calcular la suma que acumularemos al cabo de 15 años de ahorro. La segunda parte calcula la acumulación de intereses sobre la suma en los años que corren desde que terminamos de ahorrar hasta la jubilación.

Como podemos ver, a José le espera una vejez más tranquila con 450.000 pesos ahorrados, mientras que Juan ha ahorrado sólo 134.000 pesos. Y esto a pesar que ambos han depositado durante 15 años la misma suma: 90.000 pesos (500 pesos X 12 meses X 15 años).

La diferencia se debe a que José ha dispuesto de 25 años adicionales para sumar intereses sobre la suma ahorrada durante 15 años. Esta es la fuerza del interés compuesto.

Ahora podemos usar la funcionalidad Datos-Tabla, para crear un análisis de sensibilidad y ver cuál sería la situación de cada uno de nuestros personajes con distintas tasa de interés y cuotas de ahorro:






Sobre cómo crear tablas de sensibilidad con Excel pueden leer mi nota Análisis de Tablas de Datos con MS Excel – Análisis de Sensibilidad.


Como dicen los gringos: "Time is money".

Technorati Tags: MS Excel

Como hacerse millonario con Excel

Bien, el título de la nota no es muy exacto que digamos. En realidad no tengo la menor idea de cómo hacerme millonario ni con Excel ni con ninguna otra herramienta. Sucede que hace unos días atrás, hablando con una amiga sobre ahorro y manejo de finanzas domésticas, traje a colación un ejemplo que había visto en algún sitio.
Le decía a mi amiga que si fuera capaz de ahorrar 50 pesos por mes (o dólares, o euros o la moneda que ustedes prefieran), al cabo de 35 años (al llegar a los 65) dispondría de la nada despreciable suma de 190.000 pesos (cálculo hecho con una tasa de interés del 10% anual). Si hacemos un cálculo más realista, con una tasa de interés del 5%, obtendríamos la bastante atractiva suma de 57.000 pesos.
Excel nos permite realizar estos cálculos con facilidad. Si nos gusta trabajar duro podemos crear una tabla con 420 filas (35 años de ahorro * 12 meses) como esta:




La celda B2 está ligada a la celda E1 y a partir de la celda B3 ponemos esta fórmula:
=B2*(1+$E$2/12)+$E$1

Luego miramos el resultado en la celda B421 (la cuota número 420) y vemos el resultado: 56.804,62



Pero mucho más fácil es usar alguna de las funciones financieras nativas de Excel. En nuestro caso usamos VF (valor futuro). Esta función da como resultado el valor futuro de una inversión basándose en pagos periódicos constantes y en una tasa de interés constante.



Pero sucede que no todo en la vida son números y hay otras cosas que tienen valor para nosotros. Por ejemplo, gastarme 50 pesos por mes en el pub tomando unos tragos con los amigos.
Miremos las cosas de otra manera. Acabamos de ver cual es el valor futuro de ahorrar 50 pesos por mes durante 35 años. ¿Cuál es el valor actual de ese flujo de ahorro? Para esto podemos usar la función VA, valor actual. En nuestro ejemplo, con una tasa del 5%, nos dará como resultado un valor actual de 9.900 pesos



Visto así ya no parece tan tremendo renunciar a los 57.000 pesos dentro de 35 años para pasarla bien con los amigos hoy. Además, ¿quien nos asegura que viviremos otros 35 años?

Technorati Tags: MS Excel

Algo más sobre nombres en Excel

Supongamos una hoja de un cuaderno Excel con la siguiente tabla



Si abrimos el diálogo de nombres (Insertar-Nombres-Definir), veremos que no hay ningún nombre definido



Empezamos por definir un nombre que contiene el rango B2:B13 (que llamamos "ventas")



Si abrimos ahora el diálogo Insertar-Nombres-Definir veremos el nombre que acabamos de crear



Digamos que queremos ver en qué meses las ventas fueron inferiores a 70.000, para lo cual aplicamos Autofiltro



Ahora queremos imprimir nuestro reporte, para lo cual definimos algunos parámetros con el menú Configurar Página-Hoja



Ahora volvemos a abrir el menú Insertar-Nombres-Definir



Vemos ahora que Excel a creado dos nombres que corresponden a las definiciones de impresión que hemos establecido: Área_de_impresión y Títulos_a_imprimir.

Estos nombres han sido creados como nombres "locales", es decir, son válidos sólo para la Hoja1 (en nuestro caso). Si abrimos el diálogo Insertar-Nombes-Definir en la Hoja2, sólo veremos "ventas".

¿Son estos todos los nombres en nuestro cuadernos? Excel no tiene un método nativo para exhibir todos los nombres definidos en el cuaderno. Para ver todos los nombres podemos usar una macro como la siguiente (sugerida por John Walkenbach)

Sub ListAllNames()
Row = 1
For Each n In ActiveWorkbook.names
Cells(Row, 4) = n.Name
Cells(Row, 5) = " " & n.RefersTo
Row = Row + 1
Next n
End Sub

o usar el excelente complemento Name Manager, creado por Jan Karle Pieterse,.



Como vemos, Excel ha creado el nombre Hoja1!_FilterDatabase que contiene el rango de la tabla a la que hemos aplicado Autofiltro. Este es un nombre "oculto", es decir, no parece en la lista de nombres (Insertar-Nombres-Pegar). Excel crea nombres ocultos en todo tipo de tareas, por ejemplo impresión como hemos visto, cuando usamos el Solver, etc.

Volviendo a Hoja1!_FilterDatabase, si agregamos datos a la tabla y volvemos a aplicar Autofiltro, veremos que Excel cambia el rango del nombre para adecuarlo a la nueva situación.

Un uso interesante, pero que hay que ejercer con cautela, consiste en reemplazar manualmente el rango de estos nombres. Esto es relevante, por supuesto, a los nombres que aparecen en el diálogo de Nombres-Definir.

Por ejemplo, el lector Carlos Clemente me envía una hoja para calcular tablas de amortización (que les recomiendo descargar y analizar). En esta hoja, Carlos reemplaza el rango del nombre Área_de_impresión por una fórmula, lo que le permite definir el área de impresión en forma dinámica de acuerdo a la cantidad de pagos del préstamo. Esto es necesario, ya que si reducimos el número de pagos y por ende el número de filas a imprimir, Excel seguirá imprimiendo el área mayor que haya sido impresa con anterioridad.
Otro característica interesante en el modelo del amigo Carlos es el uso de Formato Condicional para ocultar las filas sin datos.

Como vemos, el uso de nombres no sólo nos permite simplificar la interpretación de las fórmulas y crear rangos dinámicos, sino también afectar el comportamiento de Excel, como en el caso del área de impresión (o de Títulos a Imprimir).




Technorati Tags: MS Excel