Obtener los elementos únicos de una lista

Una tarea común es obtener los elementos únicos de una lista. La siguiente macro permite realizarlo:

Sub elementosunicos()

Dim celda As Range
Dim unicos As Collection
Dim sh As Worksheet
Dim i As Long

'nos aseguramos que la seleccion sea un rango
If TypeName(Selection) = "Range" Then

'inicializamos la coleccion
Set colUnique = New Collection

'loop en todas las celdas y agregarlas a la coleccion
For Each celda In Selection.Cells

'si el elemento existe, se genera un error, ignorarlo
On Error Resume Next

'una coleccion solo agrega elementos no repetidos
unicos.Add celda.Value, CStr(celda.Value)
On Error GoTo 0
Next celda

'agregar hoja para la lista
Set sh = ActiveWorkbook.Worksheets.Add

'escribir los datos unicos
For i = 1 To unicos.Count
sh.Range("A1").Offset(i, 0).Value = unicos(i)
Next i

'ordenar
sh.Range(sh.Range("A2"), sh.Range("A2").End(xlDown)) _
.Sort sh.Range("A2"), xlAscending, , , , , , xlNo

End If

End Sub

Otras alternativas son generar una tabla dinámica y agregar el campo correspondiente al área de filas, o bién, utilizar el filtro avanzado. El análisis que hagamos del modelo nos dirá cuál método es el mejor.

La entrevista en español

Como lo prometido es deuda, les presento en español la entrevista que el Excel MVP Charley Kyd le concedió al sr. Chandoo en su blog.

-¿Cuáles son tus tres fórmulas favoritas?
-No tengo fórmulas favoritas, pero hay tres funciones que utilizo todo el tiempo:

INDICE
COINCIDIR (con el tercer argumento igual a cero)
SUMAPRODUCTO

-Si soy un principiante en Excel, qué libros o recursos me recomendarías?
-El foro de Mr. Excel.com para hacer preguntas. También los grupos de discusión de Microsoft y el grupo de noticias microsoft.public.excel para preguntar.

-¿Cómo podrían los gerentes y analistas ser más productivos con Excel?
-No actualicen a Excel 2007. Y si lo hacen, mantener una copia de Excel 2003 en sus equipos. (Al instalar Excel 2007 sobre 2003, responder No cuando el programa de instalación pregunte si quieren actualizar a la nueva versión).

Donde sea posible, separar datos de presentaciones. Después, utilizar fórmulas para jalar los datos en la presentación. (Mis tres funciones favoritas te ayudarán a hacerlo).

Aprender métodos abreviados con el teclado. En las versiones anteriores a 2007, los comandos Alt... son consistentes. Y 2007 te permite utilizar las combinaciones Alt+tecla de las versiones anteriores.

-¿Qué recursos (libros, sitios web) recomiendas para este tipo de usuarios?
-Estaré hablando más sobre separación de datos y presentaciones en ExcelUser.com en el próximo año. Suscríbete a mi newsletter para estar al tanto de los nuevos desarrollos.

-Piensas que un microempresario puede administrar su negocio utilizando solo Excel y otros programas gratuitos?
-Sí y no. No recomendaría utilizar Excel para llevar la contabilidad. Quicken es muy barato y hace un mucho mejor trabajo. Pero Excel puede ayudar de muchas otras maneras, como en análisis de datos, presupuestos, cálculo de precios y más.

-¿En dónde crees tú que la mayoría de nosotros desperdiciamos más tiempo...?

...importando datos de otros sistemas/fuentes?
-Realizando la misma actividad analítica o de reporteo una y otra vez, pero con diferentes datos. Cuando te descubras haciendo esto, intenta buscar otro método en el que utilices fórmulas para jalar los datos que necesites de otro libro de datos. Así, podrás enfocar tu atención únicamente en la actualización de los datos, en lugar de comenzar de cero cada vez.

...en fórmulas y errores?
-Mucha gente no sabe cómo cambiar al Modo de cálculo manual. (Herramientas - Opciones - Calcular - Manual). Esto nos permite trabajar en libros extensos sin tener que esperar a que recalcule todo el tiempo. Después, cuando queramos calcular, simplemente presionamos F9. Mucha gente elabora hojas y libros mucho más grandes de lo que deberían, y entonces se pierden en ellos. Yo intento mantener mis libros y hojas reducidos, a menos que tenga una razón específica para no hacerlo.

Mucha gente crea demasiados vínculos entre libros. Esto es un problema porque los vínculos pueden dañar o dañarse, o generar errores de referencias circulares. Yo trato de vincular solo de mis datos a mi presentación.

Supongamos que tenemos una columna de datos en el rango A5:A10. Si queremos sumar estos datos, regularmente utilizamos la fórmula =SUMA(A5:A10). Yo en su lugar, formateo las celdas A4 y A11 con borde medio y relleno gris. Después sumo utilizando el rango A4:A11. Esto me permite agregar o eliminar filas entre los bordes grises sin tener que preocuparme por las fórmulas que hacen referencia a este rango. Mientras no toque las filas de límite grises, me siento seguro. (No utilizo este método si voy a imprimir las hojas para alguien más, porque luce mal. Pero esto no es problema la mayor parte del tiempo).

...aplicando formatos?
-Trato de no utilizar nunca el comando Combinar y centrar para centrar rótulos en varias columnas. (De hecho, dudo haber utilizado este comando más de media docena de veces *en mi vida*). En su lugar, utilizo Formato - Celdas - Alineación - Centrar en la selección. Esto proporciona el mismo efecto pero sin obligarme a lidiar con los contratiempos que generan las celdas combinadas.

...en VBA?
-VBA es muy potente, y puede ser muy divertido. Pero hay que tener cuidado, puede volverse una adicción. Muchos usuarios de VBA pasan muchas horas creando macros que les ahorran varios minutos de trabajo. Esto obviamente no es un buen uso de nuestro tiempo. Por otra parte, en lo personal trato fuertemente de comentar mi código detalladamente. Y cuando reviso código viejo, *siempre* deseo haberlo comentado con más detalle todavía . Cuando estás a mitad de un proyecto, la razón de cada línea de código parece obvia. Pero seis meses después, todo es un misterio. COMENTA TU CÓDIGO.

-¿Cuál es la mejor forma para un no programador de aprender y utilizar VBA en su trabajo cotidiano?
-Continúa con una versión anterior a 2007, por dos razones: no hay buenos libros acerca de macros 2007, y la grabadora de macros no funciona con muchas acciones en 2007.

Consigue un libro para principiantes y comienza a experimentar.

Utiliza la grabadora de macros y mira los resultados.

Haz preguntas en grupos de trabajo y foros.

Familiarízate con el Explorador de objetos. (En el VBE, presiona Ver - Explorador de objetos. O simplemente presiona F2).

Diccionario de funciones en inglés

Navegando por la red, me encontré con un Diccionario de funciones de Excel, propiedad del sr. Peter Noneley. Es una excelente compilación de funciones explicadas y ejemplos de fórmulas.

Las funciones son alrededor de 150.

También tiene numerosos ejemplos de uso.

Finalmente, agrupa las funciones en las categorías que maneja Excel.


Recomiendo ampliamente descargar el archivo y dedicarle unas cuantas horas. Aprenderán bastante. Es un archivo plano, sin macros ni VBA. El único detalle es que está en inglés. Es necesario conocer la traducción de las funciones al español.

Entrevista con Charley Kyd

En su excelente blog Pointy Hairy Dilbert, Chandoo publica la entrevista que sustuvo con Charley Kyd, quien es uno de los escasos 74 Excel MVP (Most Valuable Professional) reconocidos por Microsoft en todo el mundo. Aunque breve, es sumamente ilustrativa. Para ver la entrada original (en inglés) pueden dar clic aqui.

Para quienes no dominan el inglés, intentaré realizar una decente traducción al español, misma que publicaré lo antes posible.

Dividir en múltiplos de 10

Problema:

Dado cierto número, por ejemplo el 123456, obtener una fórmula que devuelva los resultados:

12345
1234
123
12
1

Es decir, dividirlo sucesivamente por 10, 100, 1000 etc.

Para lograrlo utilizamos la función MULTIPLO.INFERIOR. Esta, redondea un número hacia cero, al múltiplo especificado. La sintaxis:

MULTIPLO.INFERIOR(número, cifra_significativa)

Ejemplo:

=MULTIPLO.INFERIOR(2530,100) devuelve 25, ya que divide el número (2530) entre 100 (25.3) y lo redondea hacia abajo a cero.

De vuelta a nuestro caso, construyamos un tabla auxiliar que muestre los resultados (y algunos ejemplos más) como sigue:

Escribimos esta fórmula en la celda B3:

=MULTIPLO.INFERIOR($A3/10^B$2,1)

Copiamos y pegamos al resto del rango y listo.

Introducción a la programación VBA

En lo sucesivo comenzaré a publicar ejemplos y técnicas de programación útiles en el desarrollo de modelos Excel. Así que supondré que el lector tiene conocimientos intermedios en el lenguaje VBA (Visual Basic for Aplications). Si no es así y desean una introducción al tema, les recomiendo el siguiente vínculo del sitio Excel Worker, que además está en español:

http://www.excelworker.virtuabyte.cl/index.php?option=content&task=section&id=3&Itemid=27

En cambio, si tienen conocimientos intermedios - avanzados de inglés, entonces recomiendo Erlandsen Data Consulting o el sitio de Chip Pearson.

Todos contienen material suficiente para iniciarse por lo que no pretenderé reinventar la rueda.

Saludos.

Funciones personalizadas

Excel cuenta con un inmenso número de funciones integradas, de la más diversa índole. Asimismo, hay numerosos complementos (el más famoso es Herramientas para análisis) que agregan todavía más funciones a Excel. Por si fuera poco, podemos crear nuestras propias funciones, lo cual puede ser útil en el (improbable) caso de que no exista una función que haga lo que necesitemos; o bien, para simplificar nuestras fórmulas. Aunque para lograrlo es necesario tener conocimientos más o menos sólidos en programación de macros.

Programar una función es parecido a programar una macro. De hecho, algunos autores llaman también macros a las funciones personalizadas. No obstante, existe una diferencia fundamental entre ambas: una función solo puede devolver un resultado. No puede operar o cambiar el entorno de Excel. (OK, sí puede, pero son excepciones ya muy rebuscadas). Lo siguiente es una macro:

Sub insertahoja()
ActiveWorkbook.Worksheets(1).Copy Before:=ActiveWorkbook.Worksheets(4)
ActiveWorksheet.Name = "Hoja número 3"
End Sub

En cambio, esto es una función:

Private function micalculo(num_1 As Integer, num_2 As Integer) As Integer

Dim j As Integer

If num_1 <= num_2 Then
j = num_2 - num_1
Else
j = num_1 - num_2
End If

micalculo = j

End function

Como vemos, en el primer caso, alteramos el entorno Excel al copiar una hoja en el libro activo, mientras que en el segundo, solo obtuvimos el resultado j (que es la diferencia absoluta entre dos números). No hicimos nada más. Otra diferencia evidente es la forma de declarar y terminar una función con las cláusulas Private function y End function. Por lo demás el lenguaje utilizado es el mismo.

Por otra parte, el resultado que devuelve una función puede no ser (como pudiera pensarse) un número. Puede ser texto, un resultado lógico (FALSO o VERDADERO), un rango, un nombre, entre otros. Por ello, es necesario declarar el tipo de dato del resultado (el último "As Integer").

Una función puede ser utilizada o llamada por una macro, por otra función, o a través de la interfaz de Excel. Para el primer caso veamos este ejemplo:

Sub insertahoja()
ActiveWorkbook.Worksheets(1).Copy Before:=ActiveWorkbook.Worksheets(4)
ActiveWorksheet.Name = "Hoja número 3"

ActiveWorksheet.Range("A3") = micalculo(ActiveWorksheet.Range("A1").Value, ActiveWorksheet.Range("A2").Value)

End Sub

Con esta macro, modificación de la previa, escribiremos en la celda A3 el resultado de la fórmula =micalculo(A1, A2)

Para el segundo caso, veamos:

Private function otrocalculo(num_1 As Integer, num_2 As Integer) As Integer

j = micalculo(num_1 As Integer, num_2 As Integer)

j = j * -1

otrocalculo= j

End function

Esta función simplemente multiplica por - 1 el resultado de micalculo(num_1 As Integer, num_2 As Integer)

Como último caso, si queremos usar esta función desde la interfaz de Excel, simplemente escribimos en una celda:

=MICALCULO(A1, A2)

Si queremos utilizar el Asistente para funciones de Excel, necesitamos dar clic en la categoría Definidas por el usuario, al final de la lista de categorías. Un detalle importante a tener en cuenta es que Excel de alguna manera "recuerda" la forma en que escribimos el nombre de la función la primera vez, es decir, con mayúsculas o minúsculas. Si la primera vez que escribamos la función utilizamos minúsculas, Excel la mostrará siempre así en la barra de fórmulas.

Hemos visto un ejemplo sencillo, pero cuando tenemos necesidad de hacer cálculos complejos o grandes, podemos mejorar la legibilidad de la fórmula simplemente agregando argumentos a una sola función.