Usar INDICE y COINCIDIR en Excel en lugar de BUSCARV

No se cómo se me pasó de publicar esta entrada sobre el uso de INDICE y COINCIDIR, que publiqué en mi blog en inglés, en enero. Así que disculpas, y aquí va.
Cuando usamos BUSCARV, la función supone que el valor buscado se encuentra a la izquierda de la matriz de búsqueda. Por ejemplo, si en esta tabla buscamos quien es el cliente al cual le hemos enviado la factura no. 9459, no podemos usar BUSCARV.

Tendríamos que usar como indicador de columnas (el tercer argumento de la función) el número -1. El valor de este argumento no puede ser menor que 1.
La solución es usar una fórmula que combina las funciones INDICE y COINCIDIR de la siguiente manera

INDICE($B$4:$B$13,COINCIDIR(A17,$C4:$C$13,0))

Y si utilizamos

nombres para los rangos

cliente =Hoja1!$B$4:$B$13
factura =Hoja1!$C$4:$C$13

INDICE(cliente,COINCIDIR(A17,factura,0))

Podemos aprovechar los rangos que acabamos de nominar, para general una lista desplegable con Validación de Datos en la celda A17 que será argumento para nuestra fórmula, que anotamos en la celda B17 (el cuaderno con el ejemplo se puede descargar aquí)

Desagraciadamente hace varios días que la interfaz de Blogger para subir imágenes no funciona. Así que recommiendo descargar el archivo para ver la solución la implementación de validación de datos.

Categorías: Funciones&Formulas_

Technorati Tags: INDICE, COINCIDIR

Categorías: Funciones&Formulas_

Technorati Tags: BUSCARV, INDICE, COINCIDIR

MS Excel – Formato parcial del contenido de una celda

A veces surge la necesidad de dar un formato especial sólo a parte del contenido de una celda.
Por ejemplo, queremos que en un encabezamiento aparezca una referencia a una nota al pie. Otro ejemplo sería usar distintos tamaños de letra en una misma celda.
Excel permite editar y aplicar formatos al contenido de celdas. Supongamos esta tabla de ventas



Supongamos que queremos agregar una nota al pie que indique que los datos de marzo son provisionales. Esta nota al pie llevará el número uno con formato de superíndice. Para lograrlo hacemos:

1 – editamos la celda D1 agregamos el número 1.
2 – seleccionamos el 1 y pulsamos el botón derecho del mouse. En el diálogo que se abre seleccionamos el menú Formato de celdas.



3 – en la zona Efectos señalamos la opción Superíndice y apretamos Aceptar.



Apretamos Enter para terminar la edición de la celda. Esta se verá ahora así:



De la misma manera podemos usar todas las demás opciones del menú, como cambiar la fuente o el color de parte del contenido de la celda, usar subíndices, etc.

Categorías: Varios_

Technorati Tags: Excel

Construir una tabla de posiciones usando las funciones JERARQUIA, SUMAPRODUCTO, INDICE y COINCIDIR

En mi entrada anterior sobre ranking de listas con más de un criterio mostré como construir una tabla de posiciones para un campeonato de fútbol. Para determinar la posición de cada equipo tomamos en cuenta la cantidad de puntos obtenidos en caso de equipos con el mismo puntaje, la cantidad de goles a favor. De persistir el empate la posición se determina por la mejor diferencia de goles.

Este es un ejemplo simplificado de una tabla de posiciones, donde sólo tomamos en cuenta los puntos obtenido y la diferencia de goles.
A diferencia del ejemplo de la nota anterior, aquí no hay necesidad de ordenar la tabla con Datos--Ordenar. La tabla de posiciones se ordena automáticamente (con fórmulas INDICE---COINCIDIR).

Este modelo esta formado por dos tablas:

La tabla de posiciones





La tabla de cálculos auxiliares.




Todos los cálculos para de terminar las posiciones se hacen en la tabla auxiliar. En la tabla de posiciones usamos una fórmula que combina las funciones INDICE y JERARQUIA para ordenar la tabla.

En la tabla de cálculos auxiliares usamos las siguientes fórmulas:

En al celda Q7: =O7-P7, para calculas la diferencia de goles

En la celda R7: =L7*3+M7, para calcular el puntaje (victoria = 3 puntos; empate = 1 punto)

En la celda S7: =JERARQUIA(R7,$R$7:$R$11), para calcular la posición relativa de cada equipo. Cuando dos equipos tienen la misma cantidad de puntos, ambos reciben el mismo número de de orden.

En la celda T7: =SUMAPRODUCTO(($R$7:$R$11=R7)*(Q7<$Q$7:$Q$11)) para generar un ranking entre los equipos de igual cantidad de puntos, de acuerdo a la diferencia de goles. Esta fórmula calcula un número de orden interno sólo para los equipos que tienen el mismo puntaje (el mismo número de orden con la función JERARQUIA). Si esto no se cumple, da como resultado cero.
En la celda U7: =S7+T7, para calcular la posición final del equipo.



Categorías: Funciones&Formulas_



Technorati Tags: Funciones Excel

COINCIDIR