Encontrar el mínimo entre valores repetidos en un rango en Excel.

Este blog trata sobre temas de interés general para los usuarios de Excel. Cuando recibo preguntas o consultas sobre problemas específicos, suelo dirigir a mis lectores a los foros de ayuda, como Exceluciones por ejemplo, o trato el problema por línea privada (a través del mail), si es que dispongo del tiempo.
A veces sucede que un problema específico deriva a otro de interés más general. Así han nacido no pocas de las entradas en este blog.

Supongamos esta situación(como siempre, el archivo del ejemplo esta a vuestra disposición aquí):

- en el rango A2:A16 tengo 15 valores únicos
- en el rango B2:B16 tengo 15 valores, algunos repetidos

Quiero encontrar el mínimo entre los números repetidos en B2:B16 y encontrar su ubicación en el rango de los valores únicos (A2:A16):

En esta entrada veremos dos soluciones:
- usando columnas auxiliares
- usando fórmulas matriciales

La solución, en ambos casos, consiste en generar una matriz que contenga sólo los valores repetidos en la columna B, y luego buscar el mínimo entre los miembros de esta matriz. Una vez hallado el valor mínimo en B, podemos encontrar su "dirección" (la celda donde se encuentra) usando las funciones DIRECCION y COINCIDIR.

La solución con columnas auxiliares comienza por generar una columna auxiliar en el rango C2:C15 con esta fórmula =CONTAR.SI($B$2:$B$16;B2)>1 que copiamos a todo el rango. Esta fórmula da FALSO si un número no esta repetido en el rango, y VERDADERO si lo está



Luego multiplicamos los valores del rango C2:C16 por los del rango B2:B16. Al multiplicar por FALSO el resultado será 0. VERDADERO es interpretado por Excel como 1 y por lo tanto el resultado al multiplicar por VERDADERO será idéntico al valor en la celda correspondiente de la columna B



Podemos ver que en la columna D los números que no se repiten en B han sido transformados en 0.

Para encontrar el menor de ellos, sin tomar en cuenta los ceros, usamos esta fórmula:

=K.ESIMO.MENOR(D2:D16;CONTAR.SI(D2:D16;0)+1)

La función K.ESIMO.MENOR (nombre espeluznante! en inglés se llama SMALL) tiene dos argumentos. EL primero es el rango dónde buscamos el menor de los miembros; el segundo representa la posición a partir de la cual buscar el menor. En nuestra matriz hay 6 ceros, por lo tanto queremos empezar a buscar de la séptima posición. Para determinar este número en forma dinámica usamos CONTAR.SI para determinar cuantos ceros hay y al resultado le agregamos 1



La solución con fórmulas matriciales es conceptualmente igual, pero las "columnas auxiliares" pasan a ser matrices generadas dentro de la fórmula. Con fórmulas matriciales resolvemos el problema con una única fórmula

=K.ESIMO.MENOR((B2:B16*(CONTAR.SI($B$2:$B$16;B2:B16)>1));CONTAR(SI((B2:B16*(CONTAR.SI($B$2:$B$16;B2:B16)>1))=0;1))+1)

Como toda fórmula matriciales la anotamos apretando simultáneamente Ctrl+Mayúsculas+Enter.

Si comparamos esta fórmula con la de la solución con columnas auxiliares, veremos que de hecho son similares. La expresión D2:D16 es reemplazada en la fórmula matricial por la expresión B2:B16*(CONTAR.SI($B$2:$B$16;B2:B16)>1).

La expresión CONTAR.SI(D2:D16;0)+1 es reemplazada por CONTAR(SI((B2:B16*(CONTAR.SI($B$2:$B$16;B2:B16)>1))=0;1))+1

Aquí notarán seguramente que estamos usando una combinación de CONTAR y SI y no la función CONTAR.SI, como sería de esperar. Si intentáramos usar CONTAR.SI recibiríamos un aviso de error. De acuerdo a la ayuda on-line de Microsoft esto se debe a que las funciones SUMIF(), COUNTIF() y COUNTBLANK() utilizan el mismo algoritmo criteria-matching como funciones de base de datos tal como DSUM(). Este algoritmo no admite matrices.
Debido a esto usamos el rodeo =CONTAR(SI(...

Finalmente, para ubicar la posición del resultado en el rango de la columna A usamos esta fórmula:

=DIRECCION(COINCIDIR(C19;A1:A16;0);1)

donde C19 es la celda que contiene la fórmula matricial.

La pregunta de mi lector era en realidad a la inversa. Encontrar en el rango de los números repetidos, el mínimo (y el máximo) del rango de los valores únicos. Para esto nos basta con encontrar el mínimo con =MIN(A2:A16) y usar este resultado como argumento en la función DIRECCION

=DIRECCION(COINCIDIR(MIN(A2:A16);B2:B16;0)+1;2)

Si el número está repetido en el rango B, siempre recibiremos la dirección de la primera ocurrencia del número.

Otra alternativa interesante es usar Formato Condicional, para marcar todas las celda en el rango B que contienen el mínimo del rango A:



En esta formula hay que prestar atención a las direcciones absolutas y relativas. Por defecto, Formato Condicional usa direcciones absolutas.

Technorati Tags: MS Excel

La Biblia en Excel

John Walkenbach publicó en su blog un cuaderno Excel que contiene toda la Biblia. Cada libro aparece en una hoja y cada versículo en una celda. Lo interesante en este uso de Excel es no sólo mostrar sus posibilidades casi ilimitadas, sino también el uso de funciones de texto y funciones matriciales.
Partiendo de la base que una buena parte de mis lectores no dominan el inglés, decidí hacer una adaptación al español.

La Biblia Excel en Español que publico aquí, comprende solamente los capítulos 1 al 9 del libro del Génesis.

Al igual que Walkenbach, empecé por buscar un archivo texto de la Biblia para importarlo a Excel. Después de descargar un archivo un tanto problemático (cada porción de texto tiene un formato distinto), logré importarlo a un cuaderno Excel usando algunas macros ad-hoc.

A diferencia de la versión en inglés, mi versión sólo contiene los primeros 9 capítulos del Génesis, cada capítulo en una hoja y cada versículo en una celda.

La primer hoja es el índice de dónde se puede acceder a las distintas hojas. Además contiene una serie de estadísticas por capítulo: número de versículos, número de letras, letras por versículo, número de palabras, palabras por versículo y concordancia (número de veces que una determinada palabra aparece en un capítulo).




Para elaborar las estadísticas usamos las siguientes fórmulas:

Versículos: =CONTARA(INDIRECTO("'"&B10&"'!A:A")). Usamos indirecto para crear una referencia a la hoja indicada usando el valor que aparece en la celda de la columna B de la fila correspondiente. En lugar de INIDRECTO podríamos usar una referencia explícita

Letras (Caracteres): ={SUMA(LARGO(INDIRECTO("'"&B10&"'!B1:B"&C10)))}. Esta es una fórmula matricial. LARGO suma el total de caracteres en cada celda del rango señalado por INDIRECTO, y SUMA en su forma matricial suma todos los valores obtenidos por LARGO.

Palabras: esta es la fórmula más elaborada
=CONTARA(INDIRECTO("'"&B10&"'!B1:B"&C10))+SUMAPRODUCTO(LARGO(ESPACIOS(INDIRECTO("'"&B10&"'!B1:B"&C10)))-LARGO(SUSTITUIR(ESPACIOS(INDIRECTO("'"&B10&"'!B1:B"&C10));" ";""))).

La primer función LARGO calcula la cantidad de caracteres que hay en la celda. La segunda función LARGO calcula la cantidad de caracteres después de haber sustraído los espacios entre las palabras.
La diferencia entre ambos resultados es la cantidad de palabras menos 1 (ya que no hay espacio antes de la primer palabra). Así que tenemos que agregar una unidad por cada versículo en el capítulo, lo que hacemos con la función CONTARA.

Finalmente tenemos la Concordancia, que lo que hace es contar cuantas veces aparece una palabra determinada en cada versículo. La fórmula es matricial:

=SUMA(LARGO(MAYUSC(INDIRECTO("'"&B10&"'!B1:B"&C10)))-LARGO(SUSTITUIR(MAYUSC(INDIRECTO("'"&B10&"'!B1:B"&C10));MAYUSC($H$7);"")))/LARGO($H$7)

La técnica es similar a la usada en la fórmula anterior. La primer función LARGO da el número de caracteres que hay en la celda. La segunda función LARGO calcula el número de caracteres después de haber extraído con SUSTITUIR la palabra que buscamos (celda $H$7). El resultado lo dividimos por la cantidad de caracteres de la palabra buscada, que calculamos con la última función LARGO en la fórmula.

Como se puede apreciar, Excel es muy flexible y ofrece muchas posibilidades en todo lo que respecta a cálculos vinculados con textos.



Technorati Tags: MS Excel

Números a letras en Excel – versión para México

En la nota Función números a letras en Excel en español - versión corregida había publicado la versión corregida de la función, publicada originariamente en la nota Como convertir números en palabras en Excel en Español.
Esta función está orientada a usos financieros. Es decir, no sólo traduce los números a su expresión en palabras, sino que también agrega el nombre de la moneda.
Uno de mis lectores, Javier Reséndiz, ha adaptado la función a las necesidades de México. Según sus palabras:

Entre otras cosas necesitaba convertir las cifras a texto y esa una de las
razones por la que te escribo. En las facturas se acostumbra escribir,
entre paréntesis, la cantidad con letra (para evitar remiendos o
añadiduras fraudulentas en la cifra), los decimales se escriben con número
y seguidos por "/100", y al final todo se remata con "M.N." (moneda
nacional), eso cuando menos en México, no lo sé de cierto en otras partes.

Así las cosas, necesitaba que una cifra dada, $125.11, p. eje, quedara
escrita así:
(CIENTO VEINTICINCO PESOS 11/100 M.N.)

Aquí pueden descargar el código de la función adaptada para México por gentileza de Javier.


Technorati Tags: MS Excel

Números aleatorios únicos con Excel

En mi nota sobre Cómo generar números aleatorios con Excel mostraba cómo generar una serie de números aleatorios únicos, es decir, sin repeticiones. Esta técnica se basaba en un generar intencionalmente una referencia circular.
Hay, por supuesto, otras posibilidades que detallo en este archivo




1 – "Unique Numbers": Microsoft propone un macro para generar números aleatorios únicos. Esta macro siempre pone los resultados a partir de la celda A3 (en el original desde la celda A1, pero la he modificado para poder poner el comando en el encabezamiento).

2 – "RndNum": no recuerdo de que foro tome esta macro.

3 – "UDF" (user defined function): tomada del sitio de Ozgrid. Esta es una función volátil, es decir, cada vez que se produce algún cambio en la hoja, Excel recalcula la función. El resultado aparece como texto en al celda que contiene la función.

4 – "MRAND": mi favorito. Esta función forma parte del complemento desarrollado por Laurent Longre que ya he mencionado en mi nota sobre la función INDIRECTO con cuadernos Excel cerrados. Esta función tiene muchas ventajas. Al ponerla como función matricial, nos permite definir una matriz (x filas X y columnas) de números aleatorios no repetidos. Además podemos definir si la función debe ser volátil o no. La sintaxis es:
{=MRAND(máximo, inicio, cantidad, volátil o estática)}.

Este archivo contiene los ejemplos y los códigos de las macros.




Technorati Tags: MS Excel

JLD de duelo

Ana, mi esposa, mi compañera de estos últimos 30 años, ha fallecido después de haber luchado durante más de un año contra un cáncer.

Este blog permancerá inactivo hasta la próxima semana.

15-Junio-2007

Gracias, gracias a todos los que me han acercado su consuelo y su afecto; algunos en este blog, otros por mail. Me han reconfortado en estos momentos tan difíciles.

Determinar si un valor existe en un rango de Excel

La forma más práctica de determinar si un valor (numérico o texto) existe en una hoja de Excel es usar el menú Edición—Buscar (o el atajo Ctrl+B). Si queremos limitar la búsqueda a un rango determinado en la hoja, lo que haremos es seleccionar primero el rango y luego efectuar la búsqueda.


También podemos usar fórmulas para determinar si un valor existe en un rango. Por ejemplo, en una celda ponemos el valor que buscamos y en otra celda ponemos una fórmula que de cómo resultado FALSO (si no existe) o VERDADERO (si existe).


Para efectuar la búsqueda en una matriz (un rango que comprende más de una columna) podemos usar esta fórmula matricial: {=O((B2:E7)=C9)}

Como con toda fórmula matricial, al introducirla en la celda apretamos Ctrl+Mayusculas+Enter.

Esta fórmula matricial funciona de la siguiente manera:

# la expresión {((B2:E7)=C9)} genera una matriz de resultados "VERDADERO" o "FALSO"

# esta matriz es evaluada por la función O. Esta función evalúa todos los resultados de la matriz y da como resultado VERDADERO si alguno de los elementos de la matriz es VERDADERO (sólo dará FALSO si todos los elementos son FALSO).

Pero, cómo hacemos una búsqueda exacta? Por ejemplo, en la celda D6 aparece el texto Xx, donde la primera X está en mayúsculas. La fórmula que hemos usado hasta ahora da VERDADERO también con Xx y con xx.

Para lograr una búsqueda exacta usamos la función IGUAL, en la siguiente fórmula matricial: {=O(IGUAL(B2:E7,C9))}

Technorati Tags: MS Excel

Validar fechas en Excel con un calendario

A partir de la nota sobre cómo insertar un calendario en una hoja de Excel, un lector me pregunta como crear un calendario que aparezca cuando elegimos una celda en la que queremos ingresar una fecha.
El objetivo, claramente, es validar la entrada. Es decir, asegurarnos que el usuario ingrese una fecha y no un número cualquiera.
Una forma práctica de hacerlo es usando Validación de datos. Por ejemplo, si queremos asegurarnos que el rango A2:A20 el usuario ingrese sólo fechas del año 2007, podemos definir esta validación de datos




De esta manera, si el usuario elige una fecha fuera del año 2007, recibirá un mensaje de error



Pero en realidad lo que queremos es que el usuario elija la fecha que quiere introducir en la celda. También esto podemos hacerlo con validación de datos, usando la opción Lista.
Creamos un nombre, año_2007, que contenga las fechas a usar. Para esto podemos, por ejemplo, poner las fechas en el rango A1:A365 de la Hoja2. Luego definimos la validación de datos



y así obtenemos una lista desplegable.



Coincidamos en que estos métodos no son muy flexibles que digamos. Así que nuestro objetivo será crear un calendario que aparezca cada vez que queremos elegir una fecha. Esto la haremos con una macro y el Calendar Control (el número depende de la versión de Excel), que ya mencionamos en nuestra nota anterior.

Nuestra macro puede estar almacenada en el Personal.xls y de esta manera estar disponible para todo cuaderno activo. O puede estar ligada a un cuaderno específico y sólo estará disponible cuando este cuaderno esté abierto. Esto último es lo que mostraremos en esta nota.

Nuestro proyecto se compone de lo siguiente:
- una forma que contenga el calendario
- una macro que muestre el calendario cada vez que la celda activa esté dentro del rango indicado e introduzca la fecha elegida en la celda activa
- un evento que dispare la macro cuando la celda activa esté en el rango indicado.

Empecemos por crear la forma que contenga al calendario. Para esto activamos el editor de Visual Basic (ALT+F11) y agregamos una forma (UserForm) al proyecto



En la ventana de Propiedades el proyecto (si no está visible usamos el menú Ver—Ventana Propiedades del VBe, o pulsamos F4) cambiamos el valor estándar en Caption por "Elija un fecha"



Para agregar el Calendar Control debemos hacer visible el cuadro de herramientas (menú Ver—Cuadro de Herramientas). Inmediatamente usamos el menú Herramientas—Controles Adicionales



Y elegimos el Calendar Control. Esto hace que su icono aparezca en el cuadro de controles a la izquierda de la forma. Pulsamos el icono y creamos el calendario sobre la forma



Las propiedades del calendario pueden ser cambiadas en la ventana de Propiedades, estando este seleccionado.


Ahora tenemos que escribir el código para el calendario. Para esto, con la forma seleccionada, usamos el menú Ver—Código (atajo: F7). Esto nos lleva a la ventana de código de la forma. En ella escribimos



Para probar el funcionamiento de la forma la seleccionamos con Ctrl+Tab y pulsamos F5. La forma aparecerá en la Hoja1. Para cerrarla pulsamos la X en el ángulo superior derecho.

Nuestro próximo paso es poner un código que transfiera la fecha elegida en el calendario a la celda activa en la hoja.
Hacemos un doble clic sobre el calendario en la forma, lo que abre la ventana de código donde escribimos


Private Sub Calendar1_Click()
ActiveCell.Value = Calendar1.Value
Unload Me
End Sub


Volvemos a probar el funcionamiento de nuestra forma, seleccionándola con Ctrl+Tab y F5 para accionarla. Elegimos una fecha del calendario y veremos que este se cierra y la fecha elegida aparecerá en la celda activa.

Ahora necesitamos un método para que el calendario se abra sólo cuando la celda seleccionada (activa) este comprendida en el rango donde queremos poner las fechas (en nuestro caso A2:A20).
Para esto necesitamos dos elementos: una rutina que haga aparecer la forma y un evento que accione la rutina cuando la celda seleccionada cae en el rango indicado.

La macro para mostrar la forma la escribimos en un módulo de Vba, que agregamos usando el icono o con el menú Insertar—Módulo:


Sub abrir_calendario()
UserForm1.Show
End Sub


El evento debemos escribirlo en la ventana de código de la Hoja1, que abrimos haciendo un doble clic en el icono de la Hoja1 en la ventana del proyecto.

Allí agregamos este código


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rngFechas As Range

Set rngFechas = Range("A2:A20")

If Union(Target, rngFechas).Address = rngFechas.Address Then _
Call abrir_calendario


End Sub


Con esto hemos terminado nuestro proyecto. Ahora, cada vez que seleccionemos una celda en el rango A2:A20 de la Hoja1, aparecerá el calendario y podemos elegir la fecha.



Como abran notado, podemos cambiar el año y el mes libremente.





Technorati Tags: MS Excel

Excel BUSCARV (VLOOKUP) a través de varias hojas

Supongamos que queremos usar BUSCARV, pero la matriz de búsqueda no se encuentra en una sola hoja como sucede por lo general, sino en varias hojas de un mismo libro.
En este ejemplo tenemos una lista de ciudades de la Argentina y su población de acuerdo a los censos de 1992 y 2001. La lista se encuentra repartida entre las hojas Hoja2, Hoja3 y Hoja4.
En la celda A1 escribimos el nombre de una las ciudades y en la celda A2 queremos poner una fórmula que nos de la población de la ciudad. La función indicada para esta tarea es BUSCARV, sólo que tenemos que indicarle en qué hoja realizar la búsqueda.
Como en muchos otros casos tenemos dos opciones: usar funciones "nativas" de Excel o escribir una función UDF (función definida por el usuario).

Si queremos limitarnos al uso de funciones nativas de Excel, una solución posible es

=SI(ESNOD(BUSCARV(A1,Hoja2!A:D,4,0)),SI(ESNOD(BUSCARV(A1,Hoja3!A:D,4,0)),BUSCARV(A1,Hoja4!A:D,4,0),BUSCARV(A1,Hoja3!A:D,4,0)),BUSCARV(A1,Hoja2!A:D,4,0))

Aquí usamos una cadena de condiciones de manera que si la búsqueda en la Hoja2 da un resultado erróneo (que evaluamos con la función ESNOD), pasamos a una segunda condición que evalúa entre la Hoja3 y la Hoja4.

Esta solución tiene varios inconvenientes:
- si agregamos otra hoja al libro, tendremos que modificar manualmente la fórmula
- la función SI esta limitada a 7 condiciones

El sitio Ozgrid.com propone esta función definida por el usuario que da respuesta a estos problemas.

Function VLOOKAllSheets(Look_Value As Variant, Tble_Array As Range, _
Col_num As Integer, Optional Range_look As Boolean)

''''''''''''''''''''''''''''''''''''''''''''''''
'Written by OzGrid.com
'''''''''''''''''''''''''''''''''''''''''''''''''
Dim wSheet As Worksheet
Dim vFound

On Error Resume Next

For Each wSheet In ActiveWorkbook.Worksheets
With wSheet
Set Tble_Array = .Range(Tble_Array.Address)
vFound = WorksheetFunction.VLookup _
(Look_Value, Tble_Array, _
Col_num, Range_look)
End With
If Not IsEmpty(vFound) Then Exit For
Next wSheet

Set Tble_Array = Nothing
VLOOKAllSheets = vFound
End Function

Para usar esta función hay que copiarla a un módulo del editor de VB. Luego podemos usar el asistente de función en la categoría "definidas por el usuario".

Esta función usa la función BUSCARV nativa de Excel (WorksheetFunction.VLookup) aplicándola cada vez a otra hoja (For Each wSheet In ActiveWorkbook.Worksheets).
Cuando la función da una resultado no vacío, la rutina se interrumpe y el valor hallado es pasado a la función.
Para que WorksheetFunction.VLookup de resultados "vacíos" en lugar de error, usa On Error Resume Next.

Esta función definida por el usuario usa los mismos argumentos como la función BUSCARV nativa de Excel





Technorati Tags: MS Excel

Función números a letras en Excel en español - versión corregida

He recibido algunas y justificadas observaciones en relación a la nota Como convertir números en palabras en Excel en Español :

- dado el número 1250000 (un millón doscientos cincuenta mil) la función da el resultado "un millones doscientos….", en lugar de "un millón…";

- en lugar de "un millón de pesos", la función da como resultado "un millón pesos"

- el número 20 daba como resultado "veinti pesos" en lugar de "veinte pesos"

- el número 100 daba como resultado "ciento pesos" en lugar de "cien pesos"


Aquí pueden descargar la versión mejorada de la función "números a letras" (SpellNumber2).

05-05-2007: nueva corrección de la rutina (1.30 aparece ahora como "uno con treinta centavos" y no como "uno con treinta y centavos")

Y otra más. Ahora 1000000 da "un millón de pesos" y 1100000 da "un millón cien mil pesos"



Technorati Tags: MS Excel

Activar y desactivar Autocompletar en Excel

Ya conocerán, o habrán notado, la funcionalidad Autocompletar de Excel. Esta funcionalidad permite llenar entradas repetidas en una columna rápidamente.
El principio es que si coinciden con una entrada existente en alguna celda de una columna determinada los primeros caracteres que se escriben en otra celda de la misma columna, Excel inserta los caracteres restantes automáticamente. Excel completa sólo aquellas entradas que contienen texto o una combinación de texto y números; entradas que contienen números, fechas u horas solamente no se completan.

A muchos usuarios de Excel esta funcionalidad les resulta más molesta que útil. Para desactivarla usamos el menú Opciones—Edición, y quitamos la marca de la opción Habilitar Autocompletar para valores de celda




De manera similar, Excel copia fórmulas que se repiten en una misma columna. Por ejemplo, en la celda B1 ponemos el número 2, luego en la celda A2 ponemos el número 1 y en la celda B2 ponemos la fórmula =A2+$B$1.
Ahora procedemos así: en la celda A3 ponemos el número 2 y en la celda B3 copiamos la fórmula de B2. En A4 ponemos el número 3 y en la celda B4 copiamos nuevamente la fórmula. En la quinta instancia, es decir, al introducir el número 5 en la celda A6 Excel copia automáticamente la fórmula en la celda B6.

Si queremos desactivar esta funcionalidad, quitamos la marca de la opción Extender formatos de lista y fórmula, en el menú Opciones—Edición



Esta funcionalidad no funciona en todos los casos y está sujeta a una serie de reglas. Para más información pueden leer la entrada en la ayuda de Microsoft. La versión en español de esta entrada es absolutamente ilegible (traducción automática).

Technorati Tags: MS Excel

Extraer números o letras de códigos alfanuméricos en Excel

En esta nota veremos cómo extraer con Excel los números, o las letras, de códigos alfanuméricos.
Muchas empresas usan códigos alfanuméricos, por ejemplo, para los números de catálogo, como por ejemplo 123ABC. A veces se presenta la necesidad de aislar la parte numérica o las letras de estos códigos.

Actualización: Power Query nos permite hacer la tarea con más facilidad. Ver este post.

En la primera parte de esta nota veremos una solución con fórmulas Excel, útil para los casos en los que las cifras y las letras están agrupadas.
En la segunda parte veremos una solución con una UDF (función definida por el usuario) para los casos en los que no hay agrupación de cifras y letras (por ejemplo, 12A3BC).

Solución con fórmulas (cifras y letras agrupadas)

Fuente: Extracting numbers from alphanumeric strings

Supongamos que en la celda A1 de nuestra hoja de Excel tenemos el código ABC123. Nuestra tarea es extraer, con fórmulas Excel, la parte numérica, es decir, 123.
Como los números y las letras están agrupados, nuestra fórmula deberá hacer lo siguiente:

1 – descomponer el código en sus componentes

Esto lo hacemos combinando las funciones EXTRAER y FILA de la siguiente manera

=EXTRAE(A1;FILA($1:$7);1)

Esta fórmula descompone el codigo ABC123 y pone cada uno de sus componentes en una matriz virtual definida por la función FILA. En este ejemplo FILA define 7 filas, pero podemos agregar más en caso de ser necesario.

2- determinar si hay un número en el código

Para esto combinamos la función ESNUMERO en la fórmula anterior

=ESNUMERO(1*EXTRAE(A1;FILA($1:$7);1))

Esta fórmula genera una matriz de valores VERDADERO y FALSO.

Al usar EXTRAE los números son convertidos en texto, por lo tanto tenemos que multiplicar la expresión por 1 para forzar la conversión nuevamente a números.

3 – encontrar la posición del primer número (dígito) en el código

Para esto usamos la función COINCIDIR de la siguiente manera:

=COINCIDIR(VERDADERO;ESNUMERO(1*EXTRAE(A1;FILA($1:$7);1));0)

Ésta es una función matricial y debe ser introducida pulsando simultáneamente Ctrl+Mayúsculas+Enter

4 – contar cuántos números hay en el código
Finalmente necesitamos contar cuantas cifras componen la parte numérica del código, lo que hacemos con la función CONTAR

={CONTAR(1*EXTRAE(A1;FILA($1:$8);1))}

Ahora combinamos todo junto en esta fórmula matricial

=EXTRAE(A1;COINCIDIR(VERDADERO;ESNUMERO(1*EXTRAE(A1;FILA($1:$8);1));0);CONTAR(1*EXTRAE(A1;FILA($1:$8);1)))


Si queremos extraer las letras tenemos que modificar la fórmula

- cambiando el argumento VERDADERO en la fórmula a FALSO
- agregando la función LARGO para calcular la cantidad de letras a extraer, reatando de ella la función CONTAR

=EXTRAE(A1;COINCIDIR(FALSO;ESNUMERO(1*EXTRAE(A1;FILA($1:$9);1));0);LARGO(A1)-CONTAR(1*EXTRAE(A1;FILA($1:$9);1)))

En hoja 1 del cuaderno con el ejemplo podemos ver los distintos casos.

Solución con UDF (función definida por el usuario)

Cuando las letras y las cifras no están agrupadas en el código, el resultado de las fórmulas es incorrecto. Para esos casos, podemos escribir una función definida por el usuario.

En un módulo del editor de Vba escribimos este código


Function ext_num_let(celda As Range, tipo As Boolean)
Dim iX As Integer, resultado As String, temp As String

Select Case tipo
Case 1
For iX = 1 To Len(celda)
temp = Mid(celda, iX, 1)
If IsNumeric(temp) Then
resultado = resultado & temp
End If
Next iX
Case Else
For iX = 1 To Len(celda)
temp = Mid(celda, iX, 1)
If Not IsNumeric(temp) Then
resultado = resultado & temp
End If
Next iX
End Select


ext_num_let = resultado

End Function


Esta función usa dos argumentos:
- Celda: la celda que contiene el código
- Tipo: 1 para extraer cifras, 0 para extraer letras



Technorati Tags: MS Excel

Generar una lista de archivos en Excel

En mi nota sobre como importar una lista de archivos a uno hoja de Excel vimos que podíamos usar las macrofunciones (funciones XLM) para realizar esta tarea.
Uno de mis lectores intentó crear una lista de un directorio que contiene 1600 archivos. El resultado fue que la macrofunción crea una lista de 256 archivos, y partir de allí recibimos #REF como resultado.
Mi sugerencia de usar TRANSPONER tampoco parece dar resultados, así que la solución es usar una macro (Vba) para crear la lista.
Una macro de este tipo fue publicada en el sitio Vbax con las correspondientes explicaciones.
Para los lectores que prefieren las explicaciones en español, procedemos de la siguiente manera:

1 – descargamos la macro en el enlace de arriba o aquí
2 – copiamos la macro a un módulo del editor de Vba (Herramientas—Macro—Editor VBa o Alt+F11)
3 – creamos una referencia al al Windows Script Host Object Model en el menú Herramientas—Referencias del editor de Vba




marcando la casilla al del Windows Script Host Object Model (antes de marcarla, los objetos aparecen en orden alfabético)



4 – Hacemos correr la macro con Herramientas—Macro—Ejecutar



Se abrirá una ventanilla donde elegimos el directorio/carpeta cuyos archivos queremos poner en la lista. La macro genera un nuevo cuaderno con la lista de los archivos y sus propiedades.

Technorati Tags: MS Excel

Cálculo de días específicos con Excel

El cálculo de fechas con Excel es muy flexible y ofrece muchas posibilidades. Para ejemplificar esto, responderé en esta nota a tres preguntas que me hace un lector del Perú:

1.Como podría hacer para calcular cuantos sábados y/o domingos hay en un mes dado el primer y ultimo día.

2.Para saber que día cae un segundo o tercer domingo de un mes X, por ejemplo el día del padre o de la madre.

3.Calcular el día de Pascua (Domingo Santo)


Calcular la ocurrencia de un día determinado.

Para calcular cuantas ocurrencias tiene un día determinado de la semana en un mes (o cualquier otro período) usamos esta fórmula

={SUMA(SI(DIASEM(A2-1+FILA(INDIRECTO("1:"&TRUNCAR(B2-A2)+1));2)=C2;1;0))}

Esta es una fórmula matricial y se pone en la celda apretando simultáneamente Ctrl + Mayúscula + Enter




En la celda A2 ponemos la fecha del primer día del mes/período, en la celda B2 el último día del mes/período, en C2 el número de orden del día de la semana (1 = Lunes, 2= Martes, etc). Es decir si queremos saber cuántos domingos hay en un período, ponemos 7 en la celda C2.

Calcular la fecha de un día determinado, dado el mes y el año.
Si queremos calcular, por ejemplo, la fecha del tercer viernes de abril de este año, usamos esta fórmula

=FECHA(E2;D2;1+((C2-(B2>=DIASEM(FECHA(E2;D2;1);2)))*7)+(B2-DIASEM(FECHA(E2;D2;1);2)))

donde B2 contiene el día buscado (1 = Lunes, 2 = Martes, etc); C2 contiene el número de orden dentro del mes; D2 contiene el mes y E2 el año



También aquí usamos DIASEM con el argumento Tipo = 2.

Calcular la fecha de Pascua con Excel.

Este cálculo es más complicado y mostraré aquí tres posibilidades

1 – una función definida por el usuario (UDF), citada por Chip Pearson


Public Function EasterDate(Yr As Integer) As Date

Dim d As Integer
d = (((255 - 11 * (Yr Mod 19)) - 21) Mod 30) + 21
EasterDate = DateSerial(Yr, 3, 1) + d + (d > 48) + 6 - ((Yr + Yr \ 4 + _
d + (d > 48) + 1) Mod 7)

End Function

Esta función hay que copiarla a un módulo del editor de Vba, y luego a activa con el asistente de fórmula (categoría: definidas por el usuario)



2 – con la fórmula =MULTIPLO.INFERIOR(FECHA(A3;5;DIA(MINUTO(A3/38)/2+56));7)-34

donde A3 contiene el año

3 – con esta fórmula, citada por John Walkenbach, sobre la cual ni él, y mucho menos yo, tenemos la más mínima idea de cómo funciona

=MONEDA(("4/"&A3)/7+RESIDUO(19*RESIDUO(A3;19)-7;30)*14%;)*7-6

Nuevamente A3 contiene el año.






Technorati Tags: MS Excel

Como convertir números en palabras en Excel en Español

Un compañero de trabajo me preguntó si había alguna función en Excel para convertir números a palabras(expresar los números en letras). Es decir, si el la celda A1 pongo 123,45 en la celda B1 donde pongo la función aparece "ciento veintitrés pesos con cuarenta y cinco centavos". Una rápida búsqueda en Google me llevó a está página de la base de datos de conocimientos de Microsoft sobre Cómo convertir un valor numérico en palabras en Excel en inglés que es una traducción automática del articulo How to convert a numeric value into English words in Excel.
Esta UDF (user defined function = función definida por el usuario) de Microsoft tiene varios problemas:
1 – sólo sirve para convertir números al inglés
2 – sólo usa el dólar como moneda
3 – sólo usa el punto como separador de decimales.

Para adaptar esta función a los usuarios de Excel en español, tuve que introducir varios cambios. Por ejemplo, en la mayoría de los países hispano parlantes se usa la coma como separador de decimales. Pero en Guatemala, Honduras, República Dominicana, México, Nicaragua, Puerto Rico y El Salvador, se usa el punto, como en los Estados Unidos.
También la moneda cambia de acuerdo al país.

Aquí pueden descargar una adaptación de la función para convertir números al español, que toma en cuenta todos estos problemas.



Para usarla hay que copiar el código de la función a un módulo del editor de Vba, por ejemplo en el Personal.xls. Luego podemos usar el asistente de funciones,




Esta versión de la función tiene tres argumentos, la celda que contiene el número (MyNumber), el separador (Sep) y la moneda (Mon)



El resultado:




Technorati Tags: MS Excel

Excel y años bisiestos

Si queremos construir una tabla que muestre el primer y último día de cada mes como ésta



usamos estas fórmulas:

en la celda B4 ponemos =FECHA(B1;1;1), que nos da el primero de Enero del año que aparece en la celda B1

en la celda B5 ponemos =FECHA(AÑO($B$4);MES(B4)+1;1) y la copiamos a todo el rango B6:B15

en la celda C4 ponemos =FECHA(AÑO(B4);MES(B4)+1;0) y la copiamos a todo el rango C4:C15.

Para calcular el último día del mes podemos usar también =EOMONTH(B4;0), si tenemos instalado el Anaysis Toolpak.

Si cambiamos el año en la celda B1 veremos que Excel toma en cuenta los años bisiestos para calcular el último día de Febrero.

Excel usa el calendario gregoriano para los cálculos que involucran fechas. De acuerdo al calendario gregoriano, cada año que es divisible por 4 es un año bisiesto. Pero como la duración del año solar es 365,25635, existe un pequeño error que hay que corregir. Para esto agregamos a la regla que un año que es divisible por 100, es bisiesto sólo si también es divisible por 400. Por este motivo el 1800,1900 o 2100 por ejemplo, no son años bisiestos.

Podemos establecer si un año es bisiesto o no, usando esta fórmula:

=SI(O(RESIDUO(B1;400)=0;Y(RESIDUO(B1;4)=0;RESIDUO(B1;100)<>0));"Año Bisiesto"; "Año no bisiesto")

Podemos agregar esta fórmula a nuestra tabla para enriquecer la información que presenta:



Descarga del ejemplo:

Fuente: XL: Método para determinar si el año es bisiesto

Technorati Tags: MS Excel

Nuevos términos en Excel.

En el blog Juice Analytics leo una nota sobre nuevos términos para Excel. La idea de la nota, escrita en tono obviamente irónico, es generar una serie de términos "profesionales" para crear una impresión de autoridad. Algo así como las frases en latín de los abogados o los términos médicos.
Uno de los ejemplos que menciona la nota es el "chartjunk" (gráfico basura), popularizado por Edward Tufte, un guru de la presentación gráfica. Este término describe un gráfico, no necesariamente generado con Excel, que incluye elementos innecesarios o que no aportan nada a la comprensión de los datos que el gráfico analiza.

La nota invita a los lectores a aportar nuevos términos que describan una serie de situaciones. Los aportes de los lectores han sido realmente ingeniosos y graciosos. Por ejemplo:

- presentación que busca distraer la atención sobre la falta de contenido o evidencia: "flufferpoint" (cruza de fluffer y Powerpoint, y también "clipterfuge", resultado de "clip" y "subterfuge" (subterfugio).

- un archivo de datos con más de 65536 filas, lo que lo hace imposible importar a una hoja de Excel en versiones anteriores a Excel 2007: "spreadalanche", cruza de "spreadsheet" y "avalanche".

- una hoja Excel que ha crecido de tal manera que se ha vuelto incomprensible para todos aparte del autor: "frankensheet" (Frankenstein y Spreadsheet).


Evidentemente el inglés goza de esa ventaja de generar palabras "cruzando" términos existentes. Será posible generar términos semejantes en español?
Por ejemplo, el "chartjunk" de Tufte podría ser "grafura", el "frankensheet" podría ser "monstruoja".

Invito a mis lectores a aportar nuevos términos a estas situaciones o cualquier otra que se les ocurra.

Technorati Tags: MS Excel

Comentarios en celdas de Excel – algunos trucos

No creo que exista algún usuario de Excel que desconozca el uso de comentarios en las celdas. Esta funcionalidad permite agregar notas textuales a la celda y, como veremos más adelante, también imágenes.
Para agregar un comentario a una celda, simplemente seleccionamos la celda y pulsamos el menú Insertar—Comentarios



También podemos usar el menú contextual seleccionando la celda y pulsando el botón derecho del mouse



Otra forma, menos conocida, es usar la barra de herramientas Revisión



El uso de la barra de herramientas Revisión es conveniente si tenemos que agregar varios comentarios en la hoja. Esta barra tiene botones que nos permiten agregar o editar un comentario, pasar el comentario siguiente o al anterior, mostrar el comentario de la celda seleccionada o todos los comentarios y también borrar un comentario.

El comentario aparece cuando pasamos con el mouse sobre la celda. El comentario es un cuadro de texto y podemos hacer con él todo lo que es posible con cualquier cuadro de texto.
Para cambiar las características del comentario tenemos que mostrarlo y luego, señalando el borde del cuadro con el mouse, abrir el menú de Formato de Comentario.






Este menú nos permite cambiar casi todas las características del comentario, por ejemplo el tamaño de la fuente y el fondo del cuadro



También podemos cambiar la forma del cuadro que contiene al comentario. Por ejemplo, para lograr este efecto



mostramos el comentario, seleccionamos el borde y en la barra de dibujo apretamos el botón Dibujo;



elegimos la opción Cambiar Autoforma y elegimos una de ellas.

Otro efecto interesante es agregar imágenes a los comentarios. Por ejemplo, supongamos que tenemos una lista de nombres y queremos que cada vez que señalamos un nombre, aparezca la imagen de la persona.

Agregamos un comentario a la celda con el nombre y señalando el borde abrimos el menú Formato de Comentario. Abrimos la pestaña Colores y Líneas y en Efectos de Relleno abrimos la pestaña Imagen. Pulsamos el botón Seleccionar Imagen y elegimos la imagen que queremos que aparezca



Apretamos Aceptar en los dos diálogos. Al pasar con el mouse por sobre la celda, aparece la imagen de Luis



Para imprimir el contenido de los comentarios Excel nos ofrece dos alternativas en el menú de Configuración de Página: imprimir el texto como en la hoja o al final de la hoja.



Para imprimir todos los comentarios de una hoja en forma ordenada, podemos copiarlos a otra hoja, en forma de lista, usando esta macro que aparece en el sitio Contextures:


Sub showcomments()
'posted by Dave Peterson 2003-05-16
   Application.ScreenUpdating = False

   Dim commrange As Range
   Dim mycell As Range
   Dim curwks As Worksheet
   Dim newwks As Worksheet
   Dim i As Long

   Set curwks = ActiveSheet

   On Error Resume Next
   Set commrange = curwks.Cells _
     .SpecialCells(xlCellTypeComments)
   On Error GoTo 0

   If commrange Is Nothing Then
     MsgBox "no comments found"
     Exit Sub
   End If

   Set newwks = Worksheets.Add

   newwks.Range("A1:D1").Value = _
     Array("Dirección", "Nombre", "Valor", "Comentario")

   i = 1
   For Each mycell In commrange
     With newwks
       i = i + 1
     On Error Resume Next
       .Cells(i, 1).Value = mycell.Address
       .Cells(i, 2).Value = mycell.Name.Name
       .Cells(i, 3).Value = mycell.Value
       .Cells(i, 4).Value = mycell.Comment.Text
     End With
   Next mycell

   Application.ScreenUpdating = True

End Sub


Esta macro copia todos los comentario en una lista que muestra la dirección de la celda que contiene el comentario, el nombre de la celda (si lo tiene), el valor y el texto del comentario.

Technorati Tags: MS Excel

Suma condicional con errores en Excel

Si el rango de la función SUMA incluye valores de error, por ejemplo #N/A (valor no disponible), el resultado de la función será también #N/A.
Por lo general lo que hacemos es investigar la celda o celdas que generan esos valores de error y corregirlas.
Pero a veces el rango reside en una hoja remota o sencillamente no queremos corregir estas celdas ya que el resultado, a pesar de ser #N/A, es válido.
Para sumar un rango que incluye valores #N/A sencillamente usamos SUMAR.SI

Dado este rango, por ejemplo



La fórmula SUMAR.SI(A1:A12;"<>#N/A") da el resultado correcto, 408.

Supongamos ahora que en nuestro rango tenemos varios tipos de errores. Por ejemplo, #N/A y también #¡DIV/0!. Por ejemplo



Cómo hacemos para sumar los valores que no son error?

Ya vimos que SUMAR.SI no acepta más de un criterio. Por lo general para sumar, contar, con más de una condición usamos SUMAPRODUCTO o fórmulas matriciales.

actualización de la entrada, siguiendo la observación de Vicente
Pero en este caso, tampoco SUMAPRODUCTO o funciones matriciales pueden ayudarnos. Esto se debe a que al multiplicar los elementos de las matrices entre si, incluimos valores #N/A o #¡DIV/0! en la operación y el resultado es, obviamente, #error!


En un caso de rango "mixto" como el nuestro, la única solución que encuentro es podemos usar esta función matricial (sugerida por Vicente en su comentario)

={SUMA(SI(ESERROR(A1:A12);"";A1:A12))}

o escribir una función definida por el usuario (UDF).

En un módulo del editor de Visual Basic escribimos esta función

Option Explicit

Public Function Sum_Err(rng)
Dim cell As Range, Count


Count = 0
For Each cell In rng
If Not IsError(cell.Value) Then
Count = Count + cell.Value
End If
Next

Sum_Err = Count

End Function

Para usar la función podemos usar el asistente de funciones, seleccionando la categoría "definidas por el usuario"



Seleccionamos el rango que queremos sumar



Y obtenemos el resultado esperado

Technorati Tags: MS Excel