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