Búsqueda por campo con INDICE y COINCIDIR

Supongamos que tenemos una lista como ésta



Queremos crear una fórmula que nos permita hacer una búsqueda o por nombre o por número de identidad. Esta situación refleja varias de las consultas que he recibido últimamente.

Podemos pensar en una solución usando BUSCARV, pero en esta nota veremos como hacerlo usando INDICE, COINCIDIR y validación de datos.

Empezamos por crear dos nombres, que usaremos en validación de datos:

Identidad = Hoja1!$B$2:$B$8
nombre = Hoja1!$A$2:$A$8

En otro lugar de la hoja creamos una tabla donde mostraremos los datos extraídos



En la celda A12 creamos una lista desplegable con Validación de datos



En la celda B12 también creamos una lista desplegable. Aquí usamos la función INDIRECTO para que en la lista aparezcan los valores del rango del nombre que ha sido elegido en la celda A12



En las celdas C12 y D12, donde queremos que aparezcan los datos buscados, ponemos esta fórmula, basada en INDICE y COINCIDIR:

Para el campo Edad: =INDICE(C2:C8,COINCIDIR(B12,INDIRECTO(A12),0))

Para el campo Profesión: =INDICE(D2:D8,COINCIDIR(B12,INDIRECTO(A12),0))

Hay que prestar atención al uso de la función INDIRECTO para interpretar el valor de la celda A12 como rango nominado.



Un detalle a agregar es dar formato condicional a las celdas C12 y D12, para evitar ver el valor #N/A cuando cambiamos el valor de la celda A12




Usamos la función ESERROR y el color de la fuente, en caso de ser VERDADERO, blanco. De esta manera, en caso de error, el contenido de la celda se vuelve "invisible".

El archivo del ejemplo se puede descargar aquí

Technorati Tags: MS Excel

Generar hojas con nombre a partir de una lista

Una de las consultas que recibo con bastante frecuencia es cómo generar hojas en un cuaderno a partir de una lista.
Supongamos que tenemos una hoja en un cuaderno con la lista de los meses del año.



Queremos que una macro genere 12 hojas en el cuaderno, un para cada mes, y les ponga el nombre del mes



Esto podemos hacerlo con una macro bastante sencilla

Sub crear_hojas()
Dim Lista As Range
Dim iX As Long

Set Lista = Application.InputBox(prompt:="Señalar rango de la lista", _
Title:="Lista de nombres", Type:=8)


Application.ScreenUpdating = False

For iX = Lista.Count To 1 Step -1
Sheets.Add.Name = Lista(iX)
Next iX

Sheets(1).Select

Application.ScreenUpdating = True

End Sub

Al correr esta macro, se abre un diálogo donde ponemos el rango que contiene la lista (pueden ser meses, días de semana o cualquier otra cosa); al pulsar el botón Aceptar



se generarán las hojas en el orden de la lista.

Si queremos usar esta macro con cierta frecuencia o pasársela a un compañero del trabajo, descubriremos que tenemos que hacer algunas mejoras.
Por ejemplo, si en el diálogo de elegir rango pulsamos Cancelar, se abrirá el editor de Vba y veremos este mensaje de error



Para evitar esto usamos la sentencia On Error

Sub crear_hojas()
Dim Lista As Range
Dim iX As Long


On Error GoTo Cancelar

Set Lista = Application.InputBox(prompt:="Señalar rango de la lista", _
Title:="Lista de nombres", Type:=8)


Application.ScreenUpdating = False

For iX = Lista.Count To 1 Step -1
Sheets.Add.Name = Lista(iX)
Next iX

Sheets(1).Select

Application.ScreenUpdating = True

Cancelar:End Sub
Otro problema puede generarse cuando ya existe en el cuaderno una hoja con uno de los nombres que aparecen en la lista. Por ejemplo, si la hoja Enero existe antes de correr la macro, obtenemos este resultado



Para evitar este problema tendremos que hacer algunas modificaciones a nuestra macro. Primero creamos una función para verificar si una hoja a crear ya existe


Function chequear_hoja(sheetName As String) As Boolean

Dim wkb As Worksheet

On Error Resume Next
Set wkb = Sheets(sheetName)
On Error GoTo 0

chequear_hoja = IIf(Not wkb Is Nothing, True, False)
End Function
Esta función da como resultado FALSE si la hoja no existe, y TRUE si la hoja ya existe en el cuaderno.
Lo que hacemos es usar esta función (adaptada de la propuesta por Colo) en nuestra macro para crear una condición. Si la hoja no existe, entonces creamos una nueva con el nombre correspondiente; de existir, pasamos a verificar el próximo nombre en la lista.

Sub crear_hojas2()
Dim Lista As Range
Dim iX As Long


On Error GoTo Cancelar

Set Lista = Application.InputBox(prompt:="Señalar rango de la lista", _
Title:="Lista de nombres", Type:=8)


Application.ScreenUpdating = False

For iX = Lista.Count To 1 Step -1
If chequear_hoja(Lista(iX)) = False Then
Sheets.Add.Name = Lista(iX)
End If
Next iX

Sheets("Hoja1").Activate

Application.ScreenUpdating = True

Cancelar:
End Sub
El cuaderno con las macros se puede descargar aquí



Technorati Tags: MS Excel

Operaciones con rangos discontinuos en Excel.

No todas las funciones de Excel permiten realizar cálculos con rangos discontinuos. Por ejemplo, supongamos estos dos rangos, A1:A3 y A5:A8




Supongamos que por algún motivo no podemos operar con el rango A1:A8. Si queremos sumar todos los valores en los dos rango podemos usar la fórmula

=SUMA(A1:A3,A5:A8)

Lo mismo si queremos contar cuántos elementos hay en ambos rangos

=CONTAR(A1:A3,A5:A8)

Pero si queremos usar CONTAR.SI para averiguar cuantos números mayores a 200 hay en los dos rangos, la fórmula

=CONTAR.SI((A1:A3,A5:A8),">200")

da como resultado #¡VALOR! Lo mismo sucede si queremos usar SUMAR.SI

La solución, por lo general, será combinar dos funciones CONTAR.SI. En nuestro caso sería

=CONTAR.SI(A1:A3,">200")+CONTAR.SI(A5:A8,">200")

El problema con esta solución es que en muchos casos tenemos que combinar muchas funciones CONTAR.SI, lo que convierte en engorrosa la tarea de construir nuestra fórmula.
Veamos cuáles son nuestras posibilidades:

Una posibilidad más compacta es ésta (propuesta por Juan Pablo González en el foro de MrExcel):

=SUMA(CONTAR.SI(INDIRECTO({"A1:A3";"A5:A8"}),">200"))

o utilizar la función ARRAY.JOIN del complemento Morefunc que tantas veces he recomendado

={SUMA((ARRAY.JOIN(A1:A3,A5:A8)>200)*1)}

Esta última fórmula es matricial y debe introducirse en la celda apretando simultáneamente Ctrl+Mayúsculas+Enter.

Los "puristas" pueden utilizar también

=SUMA(--(ARRAY.JOIN(A1:A3,A5:A8)>200))

donde usamos el doble signo menos (--) para forzar la conversión de valores lógicos a 1 (VERDADERO) o 0 (FALSO).

Ambas fórmulas dan como resultado 6.

Para sumar condicional podemos usar estas posibilidades:

=SUMA(SUMAR.SI(INDIRECTO({"A1:A3";"A5:A8"}),">200"))

una variante de la fórmula con INDIRECTO, adaptada a suma condicional, o

=SUMAPRODUCTO((ARRAY.JOIN(A1:A3,A5:A8)>200)*ARRAY.JOIN(A1:A3,A5:A8))

donde usamos SUMAPRODUCTO junto con ARRAY.JOIN

Podemos, y es recomendable, usar rangos nominados. Definimos dos nombres, cada uno refiriéndose a cada uno de los rangos

rango1 =Hoja1!$A$1:$A$3
rango2 =Hoja1!$A$5:$A$8

y utilizarlos en nuestras fórmulas. Por ejemplo:

=SUMA(SUMAR.SI(INDIRECTO({"rango1";"rango2"}),">200"))

Habrán notado que en las fórmulas con INDIRECTO usamos la expresión {"A1:A3";"A5:A8"}. Esta expresión crea un matriz en base a los datos de los rangos expresados como texto (que aparecen entre comillas y por ese motivo usamos INDIRECTO). Pueden consultar esta nota sobre funciones y constantes matriciales.

Technorati Tags: MS Excel