Como es sabido, la validación de datos nos permite establecer reglas que determinan lo que puede y lo que no puede ser ingresado en una celda. Podemos especificar un mensaje de entrada y un mensaje de error (y el tipo de este mensaje, es decir, de información, de advertencia o de límite).
Un usuario me envía la siguiente pregunta: "... lo que necesito hacer es que de acuerdo a la selección que hagan de un campo, en el siguiente solo me den las opciones referentes a ese campo y no todas,... un ejemplo es cuando abres una correo y te piden tu país, le das "México" y en el siguiente campo te aparecen solo los estados de México..."
Desde luego podemos intentar con un par de controles y un código VBA más o menos sencillo aunque, en realidad, es posible lograrlo sin necesidad de utilizar macros. Al igual que sucede con el formato condicional, al utilizar criterios personalizados (formulados), la validación de datos se vuelve una herramienta muy potente.
El primer paso es organizar nuestros datos. En la primera columna ponemos los valores independientes y, a la derecha, los dependientes. Es necesario que la lista esté ordenada por la primera columna. En otra columna, pongamos la D, escribimos una lista de los elemento únicos de la primer columna:
Por comodidad definimos los siguientes nombres:
La celda A1 con el nombre "inicio"; la columna A con el nombre "independiente"; la columna B como "independiente" y la lista de la columna D como "lista". Opcionalmente, en otra celda en blanco, escribimos "Seleccione un valor en la columna A" y la definimos con el nombre "mensaje_error"
En otra hoja, creamos un tabla sencilla para crear las listas de validación:
Seleccionamos el rango A2:A10 y vamos a Datos - Validación... Como valor Permitir seleccionamos Lista. En el campo Origen escribimos:
=SI(O(B2="",B2="Seleccione un Ramo"), lista, INDICE(independiente, COINCIDIR(B2, dependiente, 0)))
Esto sirve para que, si no hay ningún valor en la columna B, o bien, el valor en la columna B sea "Seleccione un ramo", en la lista de la columna A aparezcan todos los valores. Por el contrario, si ya tenemos un valor establecido en la columna B (la dependiente), en la lista de validación de la columna A solo aparecerá su correspondiente valor, y no todos.
Solo nos falta crear la validación de la columna B. Seleccionamos el rango B2:B10, vamos a Datos - Validación... en Permitir seleccionamos Lista y, en Origen, escribimos la fórmula:
=SI(A2="",mensaje_error,DESREF(inicio,COINCIDIR(A2,independiente,0)-1,1,
CONTAR.SI(independiente,A2),1))
Con esta fórmula, si el usuario pretende seleccionar un valor en la columna B, sin haber seleccionado primero el correspondiente valor de la columna A, la lista solo mostrará la opción "seleccione un valor en la columna A".
De otra forma, la lista mostrará los valores adecuados.
Validación de datos dependiente
Convertir números a letras
Algo que los usuarios preguntan con cierta frecuencia, es si Excel cuenta con alguna función que convierta un número (40), a su forma "verbal" o textual ("cuarenta"). Principalmente, para elaborar facturas.
Bien, la respuesta es no. Las única formas son utilizar una macro o descargar algún complemento que pueda hacerlo. Hay varios sitios que proveen dichas macros. La que utilizo es la siguiente:
Option Explicit
Dim cTexto As String 'Variable para las funciones
Public Function NumLetras(ByVal Numero As Double, ByVal Mayusculas As Integer) As String
Dim NumTmp As String
Dim c01 As Integer
Dim c02 As Integer
Dim pos As Integer
Dim dig As Integer
Dim cen As Integer
Dim dec As Integer
Dim uni As Integer
Dim letra1 As String
Dim letra2 As String
Dim letra3 As String
Dim Leyenda As String
Dim Leyenda1 As String
Dim TFNumero As String
If Numero < numero =" Abs(Numero)" numtmp =" Format(Numero," c01 =" 1" pos =" 1" tfnumero = "" style="color: rgb(51, 51, 255);">Do While c01 <= 5 c02 = 1
Do While c02 <= 3 'Extrae un digito cada vez de izquierda a derecha
dig = Val(Mid(NumTmp, pos, 1))
Select Case c02
Case 1: cen = dig
Case 2: dec = dig
Case 3: uni = dig
End Select
c02 = c02 + 1
pos = pos + 1
Loop
letra3 = Centena(uni, dec, cen)
letra2 = Decena(uni, dec)
letra1 = Unidad(uni, dec)
Select Case c01
Case 1
If cen + dec + uni = 1 Then
Leyenda = "Billon "
ElseIf cen + dec + uni > 1 Then
Leyenda = "Billones "
End If
Case 2
If cen + dec + uni >= 1 And Val(Mid(NumTmp, 7, 3)) = 0 Then
Leyenda = "Mil Millones "
ElseIf cen + dec + uni >= 1 Then
Leyenda = "Mil "
End If
Case 3
If cen + dec = 0 And uni = 1 Then
Leyenda = "Millon "
ElseIf cen > 0 Or dec > 0 Or uni > 1 Then
Leyenda = "Millones "
End If
Case 4
If cen + dec + uni >= 1 Then
Leyenda = "Mil "
End If
Case 5
If cen + dec + uni >= 1 Then
Leyenda = ""
End If
End Select
c01 = c01 + 1
TFNumero = TFNumero + letra3 + letra2 + letra1 + Leyenda
Leyenda = ""
letra1 = ""
letra2 = ""
letra3 = ""
Loop
If Val(NumTmp) = 0 Or Val(NumTmp) <1 leyenda1 = "Cero Pesos " style="color: rgb(51, 51, 255);">Or Val(NumTmp) <2 leyenda1 = "Peso " style="color: rgb(51, 51, 255);">Or Val(Mid(NumTmp, 10, 6)) = 0 Then
Leyenda1 = "de Pesos "
Else
Leyenda1 = "Pesos "
End If
TFNumero = TFNumero & Leyenda1 & Mid(NumTmp, 17) & "/100 M.N."
If Mayusculas = 1 Then
TFNumero = UCase(TFNumero)
Else
TFNumero = LCase(TFNumero)
End If
NumLetras = TFNumero
End Function
Private Function Centena(ByVal uni As Integer, ByVal dec As Integer, _
ByVal cen As Integer) As String
Select Case cen
Case 1
If dec + uni = 0 Then
cTexto = "cien "
Else
cTexto = "ciento "
End If
Case 2: cTexto = "doscientos "
Case 3: cTexto = "trescientos "
Case 4: cTexto = "cuatroscientos "
Case 5: cTexto = "quinientos "
Case 6: cTexto = "seiscientos "
Case 7: cTexto = "setescientos "
Case 8: cTexto = "ochoscientos "
Case 9: cTexto = "novescientos "
Case Else: cTexto = ""
End Select
Centena = cTexto
cTexto = ""
End Function
Private Function Decena(ByVal uni As Integer, ByVal dec As Integer) As String
Select Case dec
Case 1
Select Case uni
Case 0: cTexto = "diez "
Case 1: cTexto = "once "
Case 2: cTexto = "doce "
Case 3: cTexto = "trece "
Case 4: cTexto = "catorce "
Case 5: cTexto = "quince "
Case 6 To 9: cTexto = "dieci"
End Select
Case 2
If uni = 0 Then
cTexto = "veinte "
ElseIf uni > 0 Then
cTexto = "veinti"
End If
Case 3: cTexto = "treinta "
Case 4: cTexto = "cuarenta "
Case 5: cTexto = "cincuenta "
Case 6: cTexto = "sesenta "
Case 7: cTexto = "setenta "
Case 8: cTexto = "ochenta "
Case 9: cTexto = "noventa "
Case Else: cTexto = ""
End Select
If uni > 0 And dec > 2 Then cTexto = cTexto + "y "
Decena = cTexto
cTexto = ""
End Function
Private Function Unidad(ByVal uni As Integer, ByVal dec As Integer) As String
If dec <> 1 Then
Select Case uni
Case 1: cTexto = "un "
Case 2: cTexto = "dos "
Case 3: cTexto = "tres "
Case 4: cTexto = "cuatro "
Case 5: cTexto = "cinco "
End Select
End If
Select Case uni
Case 6: cTexto = "seis "
Case 7: cTexto = "siete "
Case 8: cTexto = "ocho "
Case 9: cTexto = "nueve "
End Select
Unidad = cTexto
cTexto = ""
End Function
En realidad, son cuatro funciones las que se utilizan para lograr el cometido.
Para usarla, simplemete escribimos en alguna celda:
=NUMLETRAS(A2,1)
Esto, claro, si hemos guardado la función en el mismo libro en el que la vamos a usar. Si la guardamos en el libro de macros personal (de forma que esté disponible en todos los libros), entonces tendremos que escribir:
=Personal.xls!NUMLETRAS(A2,1)
Para mayor seguridad, utilicen el asistente de funciones (el pequeño botón fx situado a la izquierda de la barra de fórmulas). La función estará en la categoría Definidas por el usuario.
Si queremos el resultado en minúsculas, escribimos 0 (pero no FALSO) como segundo argumento. Al final, la función agrega el texto " pesos 00/100 m.n.", el cual puede ser ajustado en el código.
La fórmula =NUMLETRAS(1425300,1) devuelve:
un millon cuatroscientos veinticinco mil trescientos pesos 00/100 m.n.
Generar un número aleatorio - Tip
Realizando una inspección de rutina de Excel, encuentro este tip en la ayuda de la función ALEATORIO:
"Si desea usar ALEATORIO para generar un número aleatorio pero no desea que los números cambien cada vez que se calcule la celda, puede escribir =ALEATORIO() en la barra de fórmulas y después presionar la tecla F9 para cambiar la fórmula a un número aleatorio."
Obviamente, después de presionar F9 hay que presionar Enter para aceptar el número.
Muy útil cuando estamos probando fórmulas.
Representar escalas en Excel
En el dibujo técnico o arquitectónico, es un "principio generalmente aceptado" el uso de los dos puntos (:) para especificar la escala en la que está hecho determinado dibujo. Por ejemplo, 1:125, lo cual quiere decir que una pulgada en el dibujo representa 125 pulgadas del modelo real.
¿Cómo representar esto en Excel? Supongamos la siguiente tabla de medidas originales y las utilizadas en el dibujo:
Primeramente, dividimos la cantidad final entre la original, resultando un número decimal, lo cual no nos sirve.
¿Cuestión de formato? Intentemos dar formato de fracciones. Seleccionamos el rango, Ctrl + 1, ficha Número, Fracciones. Seleccionamos la opción Hasta tres décimas. El resultado:
Se acerca, pero sigue sin ser lo que buscamos. Intenté elaborar algún formato personalizado y tampoco. No obtuve el resultado deseado.
La única vía que veo es la utilización de fórmulas, como la siguiente:
=B2/M.C.D(B2,A2) & ":" & A2/M.C.D(B2,A2)
M.C.D devuelve el máximo común divisor de dos hasta 29 números. Está disponible en el complemento Herramientas para análisis (en las versiones 2003 o anteriores; es función nativa en 2007).
La importancia de Excel en el mundo
Microsoft Office Excel (Excel) es la hoja de cálculo líder en el mercado. Es además, el software más potente, más flexible y más utilizado del mundo. Ningún otro programa puede competir con él en cuanto a funciones o flexibilidad. Su ámbito de aplicabilidad va de la economía a la sicología, de la biología al dibujo, de las matemáticas aplicadas a la administración de los recursos humanos.
En el mundo, miles de millones de dólares se mueven gracias a este programa. Miles de decisiones se toman apoyadas en en él. Millones de empresas de todo el mundo simplemente no podrían operar si no tuvieran Excel en sus equipos de cómputo. Gran parte de los programas a la medida o "independientes" que existen, en realidad utilizan a Excel como motor de cálculo. Casi todos muestran sus resultados en una hoja Excel. Cuando el usuario tiene un nivel avanzado del mismo, tareas que a un usuario con un nivel normal de Excel le tomaría varias horas terminar, es posible formularlas, optimizarlas y en el último de los casos, programarlas en lenguaje VBA (Visual Basic for Applications o, más exactamente, Visual Basic for Excel) de forma que puedan realizarse en unos pocos segundos. Al dominar plenamente la programación en Excel, el lenguaje VBA, es posible elaborar en minutos el trabajo que anteriormente llevaba días enteros.
Imaginemos, por un momento, que el día de mañana Excel desapareciera. Seguramente habría pérdidas económicas. La primera acción que nos vendría a la mente sería migrar a otra hoja de cálculo, pero ¿a cuál? ¿Lotus? ¿Multiplan? ¿habría suficientes copias para distribuir a todos los equipos del mundo? ¿entonces, Google Spreadsheets, on-line y por lo tanto más lenta...? Iniciaría una nueva guerra por establecer un nuevo estándar de hoja de cálculo, con los previsibles problemas de compatibilidad entre los usuarios. ¿Cuánto tiempo llevaría capacitar a los nuevos usuarios? ¿cuánto tiempo llevaría convertir los archivos al nuevo formato? ¿soportarían las macros Excel o los gráficos al menos, podrían interactuar con el resto de programas de oficina? probablemente no. ¿Cuánto tiempo llevaría integrar todos los programas que actualmente utilizan Excel como motor de cálculo al nuevo programa?
Pero no nos preocupemos, esto nunca va a ocurrir (espero). En cambio, si Lotus desapareciera, ¿habría algún efecto de importancia?
Imprimir los comentarios de una hoja
En ciertos casos, podemos necesitar imprimir el contenido de todos los comentarios de la hoja, o de todo el libro. Sobre todo si es un libro compartido en el que varias personas realizan comentarios.
Para imprimir los comentarios de una hoja en Excel, hacemos el siguiente ajuste:
Archivo - Configurar página - ficha Hoja. En la sección imprimir, está la opción Comentarios:
Aquí, podemos seleccionar las opciones Al final de la hoja (en esta, Excel hará un resumen de los comentarios al final del área de impresión) , o Como en el libro (para imprimir los comentarios tal y como aparecen en al hoja al pasar el mouse por las celdas, o bien, al dar clic derecho en una celda con comentario - Mostrar u ocultar comentarios).
Anivesario de Lotus 1-2-3
Nuevo gurú Excel
Acabo de recibir la Taza Oficial del Gurú Excel, de parte de MrExcel.com:
Esto me convierte oficialmente en un auténtico gurú del Excel.
El único detalle es que me costó 13 dólares (mas envío).
Si desean adquirir una (entre algunos otros artículos alusivos a Excel), clic aquí.
Ordenar con más de tres criterios
En alguna ocasión escuché a alguien decir que quería ordenar una lista de datos considerando cinco criterios de ordenación. Recuerdo también que alguien comentó que eso era imposible, ya que el comando Datos - Ordenar solo admitía tres entradas como criterio:
Efectivamente, Ordenar solo acepta tres criterios de ordenación. No hay una forma directa de especificarle a Excel un número mayor de criterios. Aún así, podemos ordenar una lista con cualquier número de criterios. Solo tenemos que dar un par de pasos extras.
Primeramente jerarquizamos nuestros criterios del más al menos importante. Después tomamos los tres menos importantes y los establecemos como criterios 1, 2 y 3. Explico: supongamos que queremos ordenar una lista con cinco criterios: ciudad, fecha de ingreso, sueldo, cuota y nombre, en ese orden de importancia. Entonces damos Datos - Ordenar y establecemos el criterio sueldo como primer criterio, cuota como segundo criterio y nombre como tercer criterio.
Damos Aceptar.Ahora, como ya tenemos los datos ordenados por los últimos tres criterios, simplemente volvemos a ir a Datos - Ordenar y ordenamos la lista por los primeros dos criterios.
Eso es todo. La lista estará ordenada siguiendo los cinco criterios solicitados. Recordemos aquí que si Excel encuentra varios registros que cumplen con los criterios establecidos, los presentará en el mismo orden en el que estaban en la lista original. En nuestro caso, cuando ejecutamos Ordenar por segunda vez, la lista "original" ya cumplía con los últimos tres criterios.Tal como comenté en la presentación de este blog, Excel es el programa más potente del mundo, pero no hace milagros. Habrá ocasiones en que tendremos que echarle una mano.
Arte en Excel
Hemos sabido de expresiones artísticas inverosímiles. Entre ellas está el realizar obras de arte utilizando Excel, como esta persona:
Una muestra más de la increíble flexibilidad que nos proporciona Excel.
Otra muestra, que pudiera no catalogarse como arte, pero que es tanto o más sorprendente:
Reconozco el tiempo que han invertido estas personas en sus obras, aunque no obstante, no recomendaría utilizar Excel como herramienta de dibujo. Hay infinidad de programas de dibujo (como Corel Draw), con los se obtienen mucho mejores resultados, y en menos tiempo. Con tantísimos trazos, ha sido una suerte que no se dañara el archivo.
Sumar por colores
Un usaurio me pregunta si es posible sumar los valores de celdas que tengan determinado color.
En Excel 2007 sí es posible, pero el usuario tiene la versión 2003.
En Excel 2003 la única forma es a través de una función personalizada, como la siguiente:
Function SUMARCOLOR(RangoColor As Range, CeldaColor As Range) As Long
Dim rngCelda As Range
'revisamos cada celda del rango
For Each rngCelda In RangoColor
'si los colores coinciden, sumar el valor de la celda al resultado previo
If rngCelda.Interior.ColorIndex = CeldaColor.Interior.ColorIndex Then
SUMARCOLOR = SUMARCOLOR + rngCelda.Value
End If
Next
End Function
Como vemos, no es un código muy complejo que digamos. Simplemete comparamos cada celda del rango contra el color del segundo argumento y, si coinciden, lo vamos sumando al resultado de la función.
Para utilizarla, utilizamos el asistente para funciones, buscamos la función en la categoría Definidas por el usuario, y especificamos los argumentos, quedando una fórmula similar a:
=SUMARCOLOR(A2:I20, K2)
Aunque por otra parte, lo mejor hubiera sido establecer alguna condición para los colores, y después sumar los valores usando esta condición, así como utilizar esta misma condición en un formato condicional para obtener el color en las celdas. Hacerlo hubiera llevado menos tiempo que el estar coloreando cada una de las celdas.
Tipos de errores
Al estar depurando alguna fórmula, es posible que obtengamos un resultado de error, un valor que comienza con un signo #. Esto no siempre es malo (de hecho, puede ser un resultado correcto). Si sabemos interpretar el error, podremos corregirlo fácilmente. Téngase en cuenta que para deshacerse del error puede ser necesario modificar ya sea la fórmula misma, o bien alguna de las celdas a las que hace referencia la fórmula.
En Excel existen siete resultados de error:
#N/A
#REF!
#NUM!
#NOMBRE?
#DIV/0!
#VALOR!
#NULO!
Veamos que significa cada uno de ellos. De esta manera, podremos depurarlo y corregirlo fácilmente.
#N/A
Este error se produce cuando una fórmula de búsqueda o referencia no encuentra ninguna coincidencia exacta en la correspondiente matriz de búsqueda. Significa que el valor buscado no existe en la matriz de búsqueda.
#REF!
Este tipo de error surge cuando tenemos una referencia de celda inválida en la fórmula. Por ejemplo, en la fórmula: =BUSCARV("mi_string",A2:B8,3,FALSO), obtenemos #REF! ya que no podemos buscar en la tercera columna de una matriz que solo tiene dos columnas. En esta otra:
DESREF(Hoja1!A1, -1,0,1,1) también obtenemos #REF! ya que no hay ninguna fila encima de la celda A1. Siguiendo con esta fórmula, si eliminamos la primera fila de la hoja "Hoja1", o si eliminamos la Hoja1, la fórmula mostrará #REF!, ya que se ha "perdido" la referencia a la celda Hoja1!A1.
#NUMERO!
Este se produce cuando ingresamos algún valor no numérico como un argumento de función que Excel espera que sea argumento numérico (o una referencia a un valor numérico). Otra posibilidad es ingresar un número inválido, como uno negativo cuando se espera uno positivo, o un 2 cuando el argumento solo admite 0 ó 1. La fórmula =COINCIDIR(123, B1:B10,3) devuelve #NUM!, ya que el último argumento de COINCIDIR solo puede ser -1, 0 ó 1.
#NOMBRE!
Este error lo obtenemos cuando escribimos mal el nombre de alguna función. También puede surgir cuando utilizamos alguna función personalizada y tenemos deshabilitadas las macros o el complemento correspondiente. Otra situación que dispara este error es el escribir mal el nombre de algún rango nombrado. La fórmula =SUMARSI(A2:A10,"criterio",C2:C10) develve #NOMBRE! (¿Por qué?). Finalmente puede suceder también que no utilizamos comillas al ingresar un argumento de texto.
#DIV/0!
Este es fácil. Se produce al hacer una división por cero, o bién, por una referencia a un cero.
#VALOR!
Similar a #NUMERO!, lo obtenemos cuando el tipo de argumento solicitado por la función, es distinto al ingresado por el usuario. Por ejemplo, al ingresar un argumento lógico cuando la función requiere un rango, o un número cuando la función espera texto.
#NULO!
Este es muy poco frecuente. Una fórmula devolverá #NULO! cuando la celda de intersección de dos rangos, no existe. En Excel, el operador de intersección es un espacio en blanco. Por tanto, la fórmula =A2:D2 J1:J10, devuelve #NULO! ya que los rangos A2:D2 y J1:J10 no se intersectan en ningún punto. En cambio, =A2:D2 C1:C10 devuelve C2, celda común a ambos rangos.
A menudo sucede que una celda de error está correctamente escrita pero, al hacer referencia a un resultado de error, refleja este resultado. Para saber cuál es la celda exacta que está generando el error, podemos ejecutar (previa selección de la celda con error) Herramientas - Auditoría de fórmulas - Rastrear error. Excel señalará con una línea roja la celda que está produciendo el error.
Otro error común es cuando la celda aparece llena de símbolos #. Esto se debe a que la celda no es lo suficientemente ancha para mostrar el resultado o bien, cuando contiene una fecha inválida.
Obtener los elementos únicos de una lista
Una tarea común es obtener los elementos únicos de una lista. La siguiente macro permite realizarlo:
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.
Excel on-line
Recientemente, Microsoft anunció el lanzamiento de Microsoft Office Live, una versión on-line de sus productos insignia Word, Power Point, One Note y por supuesto, Excel. MS promete que estas aplicaciones web permitirán crear, editar y compartir con el explorador nuestros documentos. Desde luego que no podrán igualar la funcionalidad de sus contrapartes de escritorio, pero hay que ver las ventajas: nada de actualizaciones periódicas, incompatibilidad de versiones, corre en Firefox, es gratis...
Finalmente, Microsoft ha decidido incursionar en el software de oficina on-line, mercado actualmente dominado por Google (¿alguna objeción?) y su suite Docs.
De acuerdo a Read Write Web, las aplicaciones tendrán los mismos nombres que sus contrapartes de escritorio. La versión Beta estará disponible este mismo año.
Lamentablemente, parece ser que la de Excel será una versión muy parecida a 2007. Como sea, démosle una oportunidad. Si llega a igualar la funcionalidad de una Google Spreadsheet,
bienvenido sea.
