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