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

Gráficos con imágenes - algunos trucos

Una buena forma de enfatizar la representación de un presupuesto en un gráfico es mostrar un billete partido en pedazos, donde cada porción representa en forma proporcional un concepto del presupuesto



La técnica para hacerlo esta explicado en mi blog sobre gráficos y presentación de datos, en la nota Gráfico de presupuesto con Autoformas.

Si quieren el mismo gráfico, pero orientado horizontalmente



pueden leer la nota Otro gráfico de presupuesto con imágenes.

Technorati Tags: MS Excel

La vida (en Excel) es más sencilla con Tablas Dinámicas

En las últimas semanas he recibido varias consultas que tienen un común denominador: no conocer o no apreciar cabalmente las ventajas de usar tablas dinámicas.

Queremos manejar una serie de datos, para lo cual los vamos registrando en una hoja. Luego en otra hoja del cuaderno montamos un resumen de los datos, lo que por lo general nos llevará a armar fórmulas bastante complicadas.
Pero la vida en Excel puede ser mucho más sencilla si sabemos explotar las posibilidades de tablas dinámicas.

Veamos el caso de un lector que quiere manejar un registro de tasas de cambios del dólar y del euro frente al peso chileno. En una planilla se van registrando las tasas de cambio por día. El problema de mi lector, tal como lo describe en su consulta es:

…en otra planilla … tengo un cuadro [con] los promedios de cada mes, entonces cuando están llenos los campos del mes de agosto por ejemplo se llena la casilla con el valor promedio de agosto, y asi una serie de indicadores…
Mi idea es que exista una casilla en la cual se pueda elegir año, mes y dia...y busque los valores segun los datos proporcionados, o sea que si elijo en año 2006, me muestre en pantalla el promedio de enero de 2006 en su respectiva casilla, el promedio de febrero de 2006 en su respectiva casilla etc. y si lo cambio a 2007 haga lo mismo y asi con con "n" años mas

Esta tarea es sencilla si usamos tablas dinámicas y en esta nota mostraremos cómo hacerlo.
En una hoja de Excel ponemos los datos de tipo de cambio del peso chileno frente al euro y al dólar en los años 2006 y 2007. Los datos los descargamos gratuitamente del sitio Oanda. El nombre de la hoja será "BD" (base de datos)





Hemos puesto los datos del dólar (USD) y del euro (EUR) en dos columnas contiguas para evitar tener que duplicar las líneas con las fechas. Esto nos obligará a hacer una pequeña manipulación en nuestra tabla dinámica.

Una vez que hemos completado nuestra base de datos, armamos la tabla dinámica con el menú Datos—Informe de tablas y gráficos dinámicos



Arrastramos el campo Fechas al área de campos de filas y los campos USD y EUR al área de datos



Ahora empezamos a hacer nuestras manipulaciones en la tabla. Primero hacemos clic sobre "Datos" y lo arrastramos sobre "Total"



Como ven, ha desaparecido la columna Total, que no necesitamos. También eliminamos el total de las columnas abriendo el menú Opciones de Tabla y quitando la marca de las opciones Totales Generales de filas y columnas.

Ahora agrupamos las filas por mes y por año con el menú Agrupar de las tablas dinámicas



y eligiendo las opciones "meses" y "años"



En este momento nuestra tabla nos muestra la suma de las tasas de cambio de cada mes para moneda. Para cambiar la función a "promedio", abrimos el menú Configuración de campo y elegimos la función promedio



Hacemos lo mismo para el campo de USD. Ahora cambiamos el formato de los números y ya tenemos nuestra tabla con los promedios por año y por mes. Cinco minutos de trabajo y éste es el resultado



Si queremos agregar promedios anuales, abrimos el menú de configuración de campo para "Fecha"



y marcamos "Subtotales"



Todo lo que nos queda por hacer es definir el rango de la tabla en forma dinámica, como esta explicado en la nota del enlace. De esta manera podemos seguir agregando datos a nuestra base de datos, sin necesidad de redefinir el rango de la tabla dinámica.

Technorati Tags: MS Excel

Nota sobre gráficos enlazados

En mi blog sobre gráficos y presentación de datos con Excel, acabo de publicar una nota sobre gráficos enlazdos.
La nota se generó a partir de la consulta de uno de mis lectores sobre cómo crear una situación de "drill down" en un gráfico. Es decir, supongamos que tenemos un gráfico de columnas y al hacer clic en una de las columnas, pasamos a otro gráfico que detalla los datos de la columna.
Las explicaciones y los detalles de cómo crear estos gráficos se pueden leer en la nota mencionada.

Technorati Tags: MS Excel

Resolver el error #NUM en el cálculo de la media geométrica en Excel

Esta nota será un tanto exótica para la mayoría de mis lectores, pero supongo que será útil para otros, como lo ha sido para una lectora de México que me consulta sobre la función MEDIA.GEOM.
Esta función, citando la ayuda de Excel devuelve la media geométrica de una matriz o de un rango de datos positivos. Por ejemplo, es posible utilizar la función MEDIA.GEOM para calcular la tasa de crecimiento promedio, dado un interés compuesto por tasas variables.

El uso de la palabra "devuelve" para indicar cuál es el resultado previsto de una función, sigue molestándome a pesar de los años que llevo leyéndola en la ayuda de Excel y en distintas notas y comentarios. No le hemos dado nada a la función, por lo que no veo que es lo que tendrá para devolvernos. Pero dejemos las cuestiones del lenguaje para otra oportunidad.

Mi lectora quería saber por qué la función MEDIA.GEOM que usaba en su cálculo daba como resultado el error #NUM, aún después de haber revisado que todos los valores en el rango fuesen numéricos.
Para entender por qué se genera el error empecemos por definir media geométrica:

la raíz n-ésima del producto de n números.

Cuando nuestro rango de número incluye números de gran magnitud puede generarse una situación de "overflow".

En elarchivo del ejemplo hay una lista con 50 valores.
En la columna B calculamos en cada línea el producto de todos los valores de la columna A hasta esa línea incluida. Podemos ver que al llegar a la línea 50, el resultado es #NUM



La forma de resolver el problema, es usar logaritmos, aquellos viejos (y odiados) conocidos de la época del secundario.
Empezamos por calcular los logaritmos de cada valor con la función LN




Luego calculamos el promedio de los logaritmos



Y finalmente calculamos la inversa con la función EXP, es decir, elevamos el número e al resultado del promedio



Podemos resumir todo este proceso en una sola fórmula matricial



La fórmula matricial =EXP(PROMEDIO(LN(A2:A51))) la introducimos pulsando simultáneamente Ctrl+Mayúsculas+Enter.

La idea de solucionar le problema con el uso de logaritmos fue tomada de la página Geometric Mean Calculations publicada por el Dr. Joe Costa

Technorati Tags: MS Excel

Gráficos dinámicos – Mostrar puntos en función de valores.

Ya hemos visto una técnica para determinar cuantos puntos de una serie mostrar en un gráfico.
En esta entrada veremos como determinar la cantidad de puntos a mostrar en función de un determinado valor.

Supongamos que queremos generar un gráfico de columnas a partir de esta tabla de ventas (el archivo con el ejemplo se puede descargar aquí)



Nuestro objetivo es determinar la cantidad de puntos a exhibir en función de un determinado valor. Digamos que queremos exhibir los 5 meses con más ventas.

Empezamos por crear una columna auxiliar, con la fórmula

=JERARQUIA(B2,$B$2:$B$13)+CONTAR.SI($B$2:B2,B2)-1



Esta fórmula la otorga a cada valor un número de orden. Usamos CONTAR.SI para "desempatar" en caso que dos meses tenga la misma suma de ventas.

Ahora creamos una tabla auxiliar donde ordenamos la tabla original de mayor a menor. Para lograr esto usamos las funciones INDICE y COINCIDIR



En el rango E2:E13 ponemos la serie del 1 al 12 que nos servirá como referencia para ordenar los valores. En el rango F2:F13 ponemos la fórmula

=INDICE($A$2:$A$13,COINCIDIR(E2,$C$2:$C$13,0))

Esta fórmula usa los valores del rango E2:E13 para obtener el mes adecuado. Lo mismo hacemos en el rango G2:G13 para poner la suma del mes.

Nuestro próximo paso es crear el gráfico. En esta etapa veremos todos los meses



Ahora tenemos que crear dos rangos dinámicos usando nombres (ver la nota del enlace más arriba).
Creamos dos nombres

mes =DESREF(Hoja1!$F$2,0,0,Hoja1!$I$1,1)
suma = DESREF(Hoja1!$G$2,0,0,Hoja1!$I$1,1)

Como ven, hemos ligado los nombres a la celda I1 de la hoja. En esta celda ponemos, en esta etapa, la cantidad de puntos de la serie que queremos mostrar. Como estos nombres se refieren a la tabla auxiliar, donde hemos ordenado los datos en forma decreciente, si ponemos 5 en la celda I1, los rangos dinámicos mostrarán los primeros cinco meses de la tabla, que son los primeros 5 meses con mayores ventas.

Nuestro próximo paso es reemplazar en la función SERIES del gráfico, los rangos de los valores por los nombres que acabamos de crear.

Seleccionamos el gráfico y abrimos el menú Datos de Origen



Y reemplazamos los rangos del gráfico por los nombres



Esto también se puede hacer seleccionando la serie de datos en el gráfico y reemplazando los valores en la función SERIES que aparece en la barra de fórmulas.

Cada vez que reemplacemos el valor en la celda I1, el gráfico mostrará los valores correspondientes



Nuestro último paso es que el gráfico dependa de un valor de ventas. Por ejemplo, ponemos 60000 en una celda y que el gráfico nos muestre todos los meses con ventas mayores a 60000. Para esto agregamos una celda auxiliar, I2, donde ponemos el monto de ventas a partir del cual queremos mostrar los meses. En la celda I1 ponemos esta fórmula

=CONTAR.SI(G2:G13,">"&I2)

Ahora nuestro gráfico muestra todos los meses con ventas mayores al monto introducido en la celda I2

Technorati Tags: MS Excel

Sumas condicionales con SUMAPRODUCTO

La función SUMAR.SI nos permite realizar sumas de acuerdo a una condición.
Ya hemos mostrado en este blog cómo hacer sumas condicionales con más de una condición
.
En esta nota veremos como enfrentarnos con situaciones especiales del tipo, por ejemplo, cómo sumar los cinco mayores números de una lista.

Por ejemplo, supongamos esta lista de números

Los cinco números mayores de la lista ( 94, 98, 93, 88, 87) suman 454. No hay forma en la cual podamos usar SUMAR.SI para sumar los números mayores de la lista. Pero si podemos hacerlo con SUMAPRODUCTO (o con fórmulas matriciales). EN nuestro caso

=SUMAPRODUCTO(lista*(JERARQUIA(lista;lista)<=5)) donde lista es un nombre que define el rango A2:A21 SUMAPRODUCTO crea dos matrices. Una contiene todos los números del rango "lista" (A2:A21); la otra matriz contiene valores VERDADERO y FALSO producidos por la función JERARQUIA. Cuando el valor calculado es menor o igual a 5, es VERDADERO; en caso contrario, FALSO. Como ya hemos visto, Excel interpreta VERDADERO como 1 al emplearlo en una operación y FALSO como 0.

Para calcular los 5 menores podemos recurrir a la función CONTAR, para determinar cuantos miembros hay en el rango y así poder determinar cuales son los últimos 5 en orden decreciente

=SUMAPRODUCTO(lista*(JERARQUIA(lista;lista)>CONTAR(lista)-5))

o esta mas sencilla, sin CONTAR

=SUMAPRODUCTO(lista*(JERARQUIA(lista;lista;1)<=5))

Si queremos sumar todos los números pares usamos la fórmula

=SUMAPRODUCTO(lista*(RESIDUO(lista;2)=0))

o

=SUMAPRODUCTO(lista*(RESIDUO(lista;2)<>1))

Para los impares usamos

=SUMAPRODUCTO(lista*RESIDUO(lista;2))

que es el equivalente a

=SUMAPRODUCTO(lista*(RESIDUO(lista;2)=1))

pero nos ahorra dos paréntesis.

La función RESIDUO(número;2) da 0 si el número es par. Por eso la expresión RESIDUO(lista;2)=0 es VERDADERO si el número en la "lista" es par.

Para sumar los múltiplos de un determinado número, también podemos usarla función RESIDUO. Por ejemplo, para sumar todos los números que son múltiplos de 3 en nuestra lista, usamos la fórmula

=SUMAPRODUCTO(lista*(RESIDUO(lista;3)=0))

Otro uso de RESIDUO es sumar todos los valores que de las filas pares o impares. Por ejemplo, para sumar todos los valores del rango "lista" que están en filas pares, usamos

=SUMAPRODUCTO(lista*(RESIDUO(FILA(lista);2)=0))

De la misma manera, para sumar los valores en filas impares podemos usar:

=SUMAPRODUCTO(lista*(RESIDUO(FILA(lista);2)<>0))

Si queremos sumar cada tres filas, a partir de la fila 3 podemos usar esta fórmula

=SUMAPRODUCTO(lista*(RESIDUO(FILA(lista);3)=0))

Si queremos sumar cada 3 filas, pero empezando de la primer fila del rango, usamos esta fórmula

=SUMAPRODUCTO((RESIDUO(FILA(lista);3)=2)*lista)

donde "2" es el número de la primer fila del rango.

La fórmula general es =SUMAPRODUCTO((RESIDUO(FILA(lista);n)=m)*lista)

Donde n es el "escalón" (número de filas que queremos saltear en la cuenta) y m es la fila de donde comenzamos a sumar.

El archivo con las fórmulas se puede descargar aquí.

Technorati Tags: MS Excel

Crear un índice de hojas en un cuaderno de Excel.

Una de las consultas que recibo con cierta frecuencia es cómo crear un índice de las hojas de un cuaderno Excel.

Cuando queremos navegar de una a otra hoja del cuaderno podemos usar varios métodos, por ejemplo usar las flechas de navegación en la esquina inferior izquierda de la hoja o hacer un clic con el botón derecho del mouse sobre ellas para abrir el menú de navegación



Pero cuando tenemos muchas hojas en un cuaderno, por ejemplo una por semana del año, estos métodos resultan incómodos.
La solución es crear una hoja que haga de índice. Las entradas del índice son los nombres de las hojas, a las cuales le hemos incorporado un enlace (hyperlink), de manera que con un solo clic podamos navegar a la hoja deseada.

Cuando se trata de pocas hojas, podemos hacer esto en forma manual. Pero cuando tenemos un cuaderno con un gran número de hojas, lo mejor es utilizar una macro.
Esta macro, que pueden descargar aquí, nos ofrece la opción de crear una nueva hoja que contenga el índice




Si aceptamos la opción, la macro crea la nueva hoja, en la celda A1 pone el título "Índice" y a partir de la fila 3, pondrá los nombres de las hojas con un enlace a la celda A1 de cada una de ellas.



Si no aceptamos la opción de crear una nueva hoja, el índice es creado a partir de la celda activa del cuaderno.




Technorati Tags: MS Excel

Búsqueda aproximada en Excel – tercera nota

En las últimas dos notas vimos cómo encontrar en una lista el valor más cercano al valor buscado, o extraer el valor asociado a él.
La fórmula matricial que expusimos, y que he mejorado gracias al aporte de Natxo (ver comentario en la nota anterior), nos da el valor más cercano al valor buscado. A veces buscamos el mayor valor más cercano y a veces el menor. Por ejemplo, si observamos la tabla del ejemplo de la nota anterior



vemos que Pedro, con 76, es el más cercano al promedio, 76,2. Pero si buscamos quién tiene la calificación más cercana por encima del promedio, la respuesta es María con 79.

Esta fórmula matricial nos permite encontrar el valor más cercano por encima del valor buscado

={=INDICE($A$2:$A7;COINCIDIR(MIN(SI(B2:B7-B9>=0;B2:B7;FALSO));SI(B2:B7-B9>=0;B2:B7;FALSO);FALSO))}

Esta fórmula crea una matriz con las diferencias entre los valores de la lista y el valor buscado; luego encuentra el menor que sea mayor a cero, es decir, el más cercano que sea mayor al valor buscado.

De la misma manera, esta fórmula no da el valor más cercano que es menor que el buscado

={INDICE($A$2:$A7;COINCIDIR(MIN(SI(B2:B7-C9>=0;B2:B7;FALSO));SI(B2:B7-C9>=0;B2:B7;FALSO);FALSO))}

El archivo con las fórmulas se puede descargar aquí.

Technorati Tags: MS Excel

Búsqueda aproximada en Excel - segunda nota

Ayer vimos como superar el problema de búsqueda aproximada al valor más cercano en Excel.
Podemos ampliar esta técnica para extraer el valor asociado al valor más cercano al valor buscado. Veamos un ejemplo. Supongamos esta lista de alumnos con notas en distintas asignaturas

En la fila 9 hemos calculado el promedio de cada asignatura. Si queremos averiguar quién es el alumno más cercano al promedio, podemos usar la fórmula que expusimos ayer



La fórmula

={INDICE($A$2:B7,COINCIDIR(MIN(ABS(B2:B7-B9)),B2:B7-B9),1)}

que hemos puesto en la celda B11 nos da como resultado Pedro, que es el alumno con la nota más cercana al promedio.

La única diferencia con la fórmula matricial que presentamos ayer es que hemos agregado el argumento "número de columna" a la función INDICE, que al argumento "matriz" comprende dos columnas y el nombre de los alumnos se encuentra en la primera.

Esta fórmula tiene un serio inconveniente. Si la lista no está ordenada en orden ascendiente, el resultado puede ser erróneo. Si copiamos la fórmula a las celdas contiguas

vemos que el resultado para Literatura es "María", cuando tendría que se "Mercedes". Lo mismo con Matemáticas, donde el resultado es "Pedro" siendo el resultado correcto "Mercedes".

Una solución posible es reordenar las listas antes de aplicar la fórmula. Pero en casos como el nuestro esto nos obligaría a dividir nuestra lista en tres listas separadas.

Otra solución es emplear una fórmula que no sea dependiente del orden de los resultados. La fórmula que aplicaremos es la siguiente:

={ INDIRECTO((DIRECCION(COINCIDIR(MIN(ABS(C9-C2:C7)),ABS(C9-C2:C7),0)+1,1)))}


Para hacer la fórmula más legible, la he dividido en la barra de fórmulas usando Alt+Enter



Esta fórmula emplea la función INDIRECTO que devuelve la referencia especificada por una cadena de texto, como ya hemos explicado.

La función DIRECCION crea una referencia a una celda, en forma de texto, cuyo valor es interpretado por INDIRECTO.

La función COINCIDIR calcula cuál es la posición del valor más cercano al promedio, usando los valores absolutos de las diferencias (como vimos en la nota de ayer).

El valor 1 se refiere a la columna A.

La combinación de estas funciones en la fórmula da como resultado $A$6. IINDIRECTO convierte este resultado al contenido de la celda, "Pedro".

Esta fórmula también tiene un serio inconveniente. Si movemos la lista hacia abajo, o agregamos líneas por encima de ella, obtenemos resultados erróneos. Esto se debe a que COINCIDIR sigue dando la posición correcta, pero ahora esta no esta coincide con los números de fila.

Para evitar esto corregimos nuestra fórmula de la siguiente manera:

={INDIRECTO((DIRECCION(FILA(D2:D7)+COINCIDIR(MIN(ABS(D9-D2:D7)),ABS(D9-D2:D7),0)-1,1)))}



El cuaderno se puede descargar aquí

Technorati Tags: MS Excel

Buscar el valor más cercano en una lista de Excel

Excel cuenta con varias funciones de búsqueda, como BUSCARV, BUSCARH y COINCIDIR.
Estas funciones permiten realizar búsquedas exactas o aproximadas. Supongamos esta situación

Para encontrar la posición del valor buscado (8.5) en la tabla usamos la función COINCIDIR, omitiendo el tercer argumento de la función. En esta situación la función da como resultado 1. Si hubiéramos hecho una búsqueda exacta, el resultado sería #N/A.

El problema con este resultado es que si buscamos la posición del valor más cercano al valor buscado, el resultado tendría que haber sido 2, ya que el valor buscado (8.5) está más cerca del segundo valor en la lista (10) que del primero (5).

Para obtener la posición del valor más cercano al valor buscado tenemos que usar esta fórmula matricial:

={COINCIDIR(MIN(ABS(B2:B7-B9)),B2:B7-B9)}



Para entender esta fórmula tendremos que analizarla de "adentro hacia fuera".

La expresión ABS(B2:B7-B9), al estar dentro de una fórmula matricial, de cómo resultado el menor de los valores del rango B2-B9, B3-B9,…,B7-B9.
El mínimo de esta serie de valores es 1.5, que ocupa el segundo lugar en la serie



El resultado de la fórmula COINCIDIR puede usarse como argumento de la función INDICE para encontrar el valor más cercano al buscado en la lista de valores. En nuestro caso:

={INDICE(B2:B7,COINCIDIR(MIN(ABS(B2:B7-B9)),B2:B7-B9))}

Technorati Tags: MS Excel

Tabla de tasas de cambio con la función TRANSPONER

Un compañero de trabajo me consultaba hoy sobre el uso de la función TRANSPONER sobre la cual ya he escrito en el pasado.
Mi explicación fue interrumpida con un "y para qué sirve en la vida real?". El ejemplo de la nota (ver enlace) le pareció complicado y, aceptémoslo, un tanto rebuscado.
No me quedó más remedio que crear un ejemplo más ilustrativo, que es el siguiente: una tabla de tasas de cambios cruzados con la función TRANSPONER. Una tabla de este tipo aparece en mi nota sobre cómo construir un presupuesto de viaje, pero allí no explico cómo construirla.
Empezamos por una tabla sencilla con el nombre de las monedas y su tasa de cambio, por ejemplo, respecto al Euro



Para trabajar con más comodidad, definimos un nombre que contendrá el rango B3:B6, que llamaremos "tasas"



Ahora construimos el marco para nuestra tabla



Seleccionamos el rango B11:E11 y ponemos la fórmula matricial =$B$3/TRANSPONER(tasas)



Los paréntesis "{" y "}" aparecen al introducir la fórmula pulsando simultáneamente Ctrl+Mayúsculas+Enter, como con toda función matricial.

Copiamos la fórmula al resto de las filas, cambiando la celda de referencia (B4 para el franco suizo, B5 para la libra esterlina y B6 para el yen japonés).



Si queremos orientar la tabla en otro sentido, podemos usar la fórmula matricial ={=$B$3/tasas}



O transponer la primer tabla con TRANSPONER



El cuaderno del ejemplo se puede descargar aquí.

Technorati Tags: MS Excel