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