Simplificando la función SI (IF) combinando expresiones lógicas.

Una de las funciones más usadas en Excel es la función SI. La ayuda on-line de Excel la define así: "devuelve un valor si la condición especificada es VERDADERO y otro valor si dicho argumento es FALSO."
La sintaxis de esta función es:

=SI(prueba_lógica;valor_si_verdadero;valor_si_falso)

Para crear pruebas más elaboradas Excel nos permite anidar hasta siete funciones SI como argumentos valor_si_verdadero y valor_si_falso.
En entradas anteriores ya había comentado que crear formulas con varias funciones SI anidadas, suele resultar en fórmulas "chorizo" difíciles de entender y conllevan una nada despreciable inversión de tiempo.

Algunas de las alternativas, dependiendo del caso, pueden ser usar la función ELEGIR (CHOOSE) en lugar de SI o utilizar la función BUSCARV en lugar de anidar varias funciones SI.

Otra alternativa, en especial cuando tenemos varias condiciones que deben cumplirse, es crear fórmulas con expresiones lógicas combinadas con operadores "+".

Qué es una expresión lógica? Funciones (expresiones) en Excel pueden dar resultados numéricos o ser resueltas como expresión lógica con el valor VERDADERO o el valor FALSO. Si escribimos la fórmula "=A1=B1", Excel dará como resultado o FALSO o VERDADERO.
Esta característica puede ser aprovechada para crear una alternativa a la función SI. Veremos esto con un ejemplo. Supongamos un club de compras, donde los clientes reciben descuentos sobre las compras mensuales basados en las siguientes reglas

Nuestra tarea es determinar el descuento que le corresponde a cada cliente dados estos datos:



Para determinar el descuento de cada cliente no utilizaremos funciones SI, sino expresiones lógicas. Dado que tenemos que tener en cuenta dos condiciones (las compras del mes corriente y las del anterior) usaremos la función Y (AND en su versión inglesa) para armar la expresión lógica.
La fórmulas que expresan las condiciones de los descuentos son:



Por ejemplo, si la expresión =Y(B5<5000,c5<5000) es verdadera, la fórmula

=Y(B5<5000,c5<5000)*0.05

da como resultado 5%. Si la expresión no se cumple, el resultado de la fórmula será 0.

Combinamos las fórmulas para cada uno de los casos en una única formula con el operador "+"

=Y(B5<5000,C5<5000)*0.05+Y(B5<5000,c5>=5000)*0.07+Y(B5>=5000,C5<5000)*0.10+Y(B5>=5000,C5>=5000)*0.12

y la copiamos en el rango D5:D14

Esta fórmula es más explicita y manejable que la equivalente usando funciones SI anidadas.


Categorías: Funciones&Formulas_

Technorati Tags: Funciones Excel

Calculando las semanas del año en Excel

Últimamente he recibido varias preguntas sobre temas relacionados con cálculos de fechas. Una de ellas era cómo calcular el número de semana de una determinada fecha.

A primera vista parece una pregunta sencilla. Si tenemos instalado el Análisis Toolpak, podemos utilizar la función NUM.DE.SEMANA(núm_de_serie;tipo), donde "num_de_serie" es el número de serie de la fecha y "tipo" es una variable que indica si la semana comienza el domingo (tipo = 1) o el lunes(tipo = 2).

Si NUM.DE.SEMANA da resultado #¿NOMBRE?, podemos usar su equivalente en inglés WEEKNUM.

Sencillo, no? Pues bien, no!. Sucede que hay que tener en cuenta varias cuestiones antes de lanzarnos a calcular el número de semana.

El resto de esta entrada está basado en los excelentes artículos de Chip Pearson y Ron de Bruin sobre el tema.

La primer semana comienza el 1 de enero; pero, cuando comienza la segunda, al 8 de enero o el primer lunes después del 1 de enero? Que día marca el comienzo de la semana, el lunes o el domingo?

Veamos las distintas formas de calcular el número de semana. Un archivo con las distintas fórmulas se puede bajar aquí.

Número de semana absoluto.

De acuerdo este concepto, la primer semana empieza siempre el 1 de enero y concluye el 7 de enero, sin tener en cuenta el día de la semana. De esta manera tendremos 53 semanas al año. La semana 53 tendrá un día o dos, si se trata de un año bisiesto.
Para calcular el número de semana absoluto usamos la fórmula
=TRUNCAR(((A1-FECHA(AÑO(A1),1,0))+6)/7)
Si en la celda A1 tenemos la fecha de hoy (28/06/2006), esta fórmula da el resultado 26.

Número de semana de Excel.

Excel ofrece la función WEEKNUM (o NUM.DE.SEMANA). Para usar esta fórmula debemos instalar previamente el Analysis Toolpak.
Esta función calcula el número de semana contando desde el domingo o el lunes. Por lo tanto, la primer semana puede tener entre 1 y 7 días.
La sintaxis de esta fórmula es
NUM.DE.SEMANA(núm_de_serie;tipo)
Núm_de_serie es una fecha dentro de la semana. Las fechas deben introducirse mediante la función FECHA o como resultados de otras fórmulas o funciones.
Tipo es un número que determina en qué día comienza la semana. El valor puede ser 1 (domingo) o 2 (lunes).
La función =WEEKNUM(A1,2), donde A1 contiene la fecha 28/06/2006 da el resulta 27. En cambio la función =WEEKNUM(A1,1) da 26.

Número de semana ISO (International Organization for Standardisation )

De acuerdo a la norma ISO, la semana empieza siempre un lunes y termina un domingo. La primer semana del año es la que contiene el primer jueves. Es decir, la primer semana tendrá siempre 4 días por lo menos.
La fórmula para calcular el número de semana de acuerdo al estándar ISO es

=ENTERO((A1-FECHA(AÑO(A1-DIASEM(A1-1)+4),1,3)+DIASEM(FECHA(AÑO(A1-DIASEM(A1-1)+4),1,3))+5)/7)

Si la celda A1 contiene la fecha 28/06/2006, el resultado será 26.

Finalmente, si queremos utilizar fórmulas en lugar de la función WEEKNUM,

Si la semana empieza en domingo:

=1+ENTERO((A1-(FECHA(AÑO(A1),1,2)-DIASEM(FECHA(AÑO(A1),1,1))))/7)

Si la semana empieza en lunes:

=1+ENTERO((A1-(FECHA(AÑO(A1),1,2)-DIASEM(FECHA(AÑO(A1),1,0))))/7)


Categorías: Funciones&Formulas

Technorati Tags: WEEKNUM

La función INDIRECTO con cuadernos Excel cerrados.

Con la función INDIRECTO de Excel, podemos establecer referencias dinámicas a un rango o una celda en un cuaderno remoto (referencia externa). En esta entrada sobre el uso de la función INDIRECTO, daba un ejemplo de cómo establecer referencias a celdas que se encuentran en distintas hojas de un mismo cuaderno Excel.
De la misma manera podemos establecer referencias a celdas en hojas de otros cuadernos (lo que he llamado cuadernos remotos). Como ya he explicado, la función INDIRECTO devuelve la referencia especificada por una cadena de texto. El problema con INDIRECTO es que si se hace referencia a un libro remoto (una referencia externa), ese libro debe estar abierto. Si el libro de origen no está abierto, INDIRECTO devolverá el valor de error #¡REF!

En esta nota sobre el tema en Daily Dose of Excel se señalan varias soluciones, de las que mencionaré sólo dos:

1 – El complemento desarrollado por Laurent Longre, MOREFUNC.XLL, que incluye la función INDIRECT.EXT que funciona como INDIRECTO, pero también con cuadernos cerrados. Esta función tiene varias limitaciones:
- permite establecer referencias sólo a una única celda, por lo que no sirve como segundo argumento en la función BUSCARV (VLOOKUP)
- no funciona con si se refiere a nombres en cuadernos cerrados


2 – La función PULL de Harlan Grove. Esta es una UDF (user defined function) cuyo código se puede obtener aquí. Este función permite crear referencias a rangos y por lo tanto, puede usarse con BUSCARV (VLOOKUP), por ejemplo =VLOOKUP("search_text",PULL("'C:temp[book1.xls]sheet!'A1:B20"),2,0)

Ambas funciones tienen en común el ser lentas.


Categorías: Funciones&Formulas
Technorati Tags: INDIRECTO

Hojas de Calculo on-line – Google Spreadsheets, IRows, NumSum y ZohoSheet

Hace unos días atrás Google anunció la salida de su hoja de cálculo on-line,
Google Spreadsheets. A pesar de lo que afirman algunos comentaristas, no creo que en esta etapa, Google Spreadsheets sea una amenaza a la hegemonía de Excel en lo que a hojas de cálculo se refiere, lo cual supondría un masivo pasaje de las aplicaciones desktop a las aplicaciones on-line.
Google Spreadsheets está desprovista de herramientas como gráficos, tablas dinámicas, funciones matriciales, macros y uso de nombres, para mencionar los más evidentes. Además supongo que hay no pocas cuestiones de seguridad y privacidad no resueltas.

En mi opinión, en esta etapa, las distintas hojas de cálculo on-line que existen cumplirán dos funciones:
1 – alternativa gratuita para particulares e instituciones pequeñas, siempre que el problema de privacidad no sea crítico;
2 – herramienta para publicar archivos existentes en la red cómodamente

Google Spreadsheets no es la única hoja de cálculo on-line disponible. Curiosamente, ninguno de los comentaristas que leído menciona a alguna de estas aplicaciones. Antes de Google Spreadsheets ya existían IRows, NumSum y ZohoSheet.

He estado "jugando" un poco con estas aplicaciones y aquí resumo mis impresiones:

Google Spreadsheets:
No me gustó: el interfaz es un tanto incómodo al principio para quien esté acostumbrado al de Excel; no tiene la posibilidad de crear gráficos. Al tratar de importar una hoja donde uso nombres, recibo un aviso del tipo "Ooops, server error", pero no me dan ningún indicio de cuál es el problema.
Me gustó: tiene muchas funciones, incluyendo SUMAPRODUCTO; permite pasar de una hoja a otra con un interfaz similar al de la pestañas en Excel; al importar un archivo, cuando no puede resolver una fórmula, exhibe los valores numéricos que aparecen en las celdas de la hoja.


No me gustó: ciertos aspectos del interfaz no son cómodos, como pasar de una hoja a otra; como Google, tampoco IRows sabe interpretar rangos con nombres; tampoco tiene una fórmula equivalente a SUMAPRODUCTO. Al surgir problemas en la subida de archivos, el anuncio que aparece es más explícito que el de Google, pero no lo suficiente. Al subir un archivo, si no puede resolver las fórmulas o interpretar los rangos, muestra el valor #import error# en las celdas, en lugar del valor numérico, como hace Google.
Me gustó: en general el interfaz resulta bastante familiar; permite crear gráficos.


No me gustó: por definición los archivos importados son públicos!; sólo sube la primer hoja, de manera que si tenemos un cuaderno con varias, tendremos que subirlo "en etapas".


No me gustó: al subir un archivo, sino consigue resolver una fórmula, muestra un valor de "#invalid formula!#" en lugar del valor numérico original. Según dicen permite usar casi todas las funciones principales de Excel, pero no cuenta con una lista de funciones de donde poder elegir.
Me gustó: el interfaz, fuera del menú, es muy intuitivo; permite crear gráficos; utiliza pestañas para pasar de una hoja a otra.

Mis conclusiones: tomando como cierto lo del uso de las funciones de Excel, ZohoSheet parece ser la más completa para desarrollar hojas on-line junto con IRows, que me parece más intuitivo y fácil de usar. Google Spreadsheets parece más adecuada para subir archivos existentes aunque le falten los gráficos. Pero es de suponer que seguirá desarrollándose e incorporará gráficos y nuevas funcionalidades en el futuro.



Categorías: Manejo de Datos_, Varios_

Technorati Tags: Hojas de cálculo online

Regresión lineal en MS Excel

Por lo general escribo mis entradas en castellano y luego, si tengo tiempo, las publico traducidas en mi blog inglés. En algunos casos procedo al revés, como en esta entrada sobre regresión linear en Excel. Aquí va la entrada en castellano.
Excel tiene una herramienta que permite realizar regresiones lineales sobre listas de datos. Para poder usar esta herramienta tenemos que activar el complemento Análisis ToolPak (Herramientas---Complementos)



Una vez activado el complemento, pulsamos Data análisis en el menú Herramientas



La herramienta de análisis Regresión realiza un análisis de regresión lineal utilizando el método de los "mínimos cuadrados" para ajustar una línea a una serie de observaciones. De esta manera podemos analizar como el valor de una variable dependiente es afectado por una o más variables independientes. El máximo admitido de variables independientes es 16.

Supongamos el siguiente ejemplo



Elegimos la opción Regresión en el menú Herramientas---Data Análisis y completamos los datos requeridos para el análisis




Las opciones de la ventanilla de diálogo son evidentes. Señalamos el rango de la variable dependiente y el de las independientes; si estos rangos contienen rótulos hay que señalar la casilla correspondiente y donde ubicar los resultados del análisis (en la misma hoja, en otra hoja o en un nuevo cuaderno).
El resultado del análisis se ve así:



Data Análisis incluye otras herramientas estadísticas como:

Varianza
Correlación
Covarianza
Estadística descriptiva
Suavización exponencial
Prueba t para varianzas de dos muestras
Análisis de Fourier
Histograma
Media móvil
Jerarquía y percentil
Muestreo
Prueba t
Prueba z


Categorías: Varios_

Technorati Tags: Regresion Lineal, Excel

Ligando celdas a objetos en Excel – Uso de Validación de Datos, INDICE, COINDICIR, y otras funciones Excel un tanto exóticas

Ayer hablaba de la "cámara fotográfica" de Excel. Vimos que esta herramienta permite tomar "instantáneas" de porciones de datos de una hoja Excel, y que esta "foto" es dinámica.
Lo que hace esta herramienta es ligar un rango de celdas al objeto (la imagen), como se puede ver aquí


De manera similar, se puede ligar (referenciar) una celda a un objeto en la hoja de cálculo. Por ejemplo, a un rectángulo. Todo lo que tenemos que hacer es insertar el objeto en la hoja y, estando este seleccionado, tipear la referencia en la barra de fórmulas. Utilizando esta técnica podemos armar un modelo como este


Este modelo nos permite distribuir operarios en distintas máquinas, representando la distribución en forma gráfica. Usando solamente fórmulas y validación de datos, este modelo nos permite controlar que no ubiquemos un mismo operario más de una vez. A medida que vamos ubicando a los operarios, sólo los disponibles aparecen en la lista despegable. Como es costumbre en este blog, el archivo está a vuestra disposición.Paso a explicar cómo funciona el modelo. Cada uno de los rectángulos que representan a los operarios, está ligado a la celda correspondiente en el rango C4:C15 (cuyo nombre es "ocupados").



Este rango contiene validación de datos que genera una lista desplegable que está contenida en el rango E4:E19 de la hoja "operarios", al que dimos el nombre de "lista_operarios". Este es un rango dinámico definido por esta fórmula:

lista_operarios =DESREF(operarios!$E$4,0,0,CONTARA(operarios!$E$4:$E$19)-CONTAR.BLANCO(operarios!$E$4:$E$19),1)

Como ven, este rango contiene sólo los nombres de los operarios disponibles (aquellos que todavía no han sido ubicados en alguna máquina).
Para lograr este efecto, hemos construido algunas columnas auxiliares en la hoja "operarios":

Columna A, Operarios: contiene la lista de todos los operarios (a ver si alguien adivina en quienes está inspirada la lista).

Columna B, Ocupado: muestra quienes están ocupados y quienes disponibles usando esta fórmula

=SI(CONTAR.SI(ocupados,A4)>=1,"ocupado","disponible")
El nombre "ocupados" se refiere al rango =Hoja1!$C$4:$C$15

Columna C, Disponibles: sólo aparecen los nombres de los disponibles.

Columna D, No. De orden: muestra el número de fila del operario disponible, o blanco si no lo está. Aquí utilizamos la fórmula

=SI(CELDA("contenido",C4)="","",FILA(C4))

La función CELDA analiza, en este caso, el contenido de la celda. Aquí la usamos en una proposición lógica para saber si la formula de C4, por ejemplo, da como resultado BLANCO o no. No podemos utilizar la función ESBLANCO, ya que todas las celda en el rango C4:C15 contienen una fórmula y por lo tanto ESBLANCO daría FALSO en todos los casos.

Columna E, lista de operarios: para mostrar sólo los nombres de los disponibles en forma dinámica, usamos la siguiente fórmula:

=SI(ESERROR(INDICE(disponibles,COINCIDIR(K.ESIMO.MAYOR($D$4:$D$19,FILA()-3),$D$4:$D$19,0))),"",INDICE(disponibles,COINCIDIR(K.ESIMO.MAYOR($D$4:$D$19,FILA()-3),$D$4:$D$19,0)))

La primer parte (ESERROR…..) cumple la única función de evitar resultados #NUM cuando la celda de referencia no contiene un valor numérico (como en D10, por ejemplo).


Categorías: Funciones&Formulas_, Varios_

Utilizando la cámara fotográfica de Excel.

Hace unos días atrás alguien preguntó, en uno de los foros que suelo leer, sobre la funcionalidad del "snapshot" en Excel. Si bien la pregunta estaba orientada a otro tema, me trajo a la memoria una herramienta de Excel poco conocida: la cámara fotográfica.
Esta herramienta permite tomar "fotos" de porciones de de datos en una hoja de Excel. Lo particular de esta herramienta es que la "fotos" son dinámicas, es decir, se actualizan cuando introducimos cambios en los datos.
Para usar esta herramienta lo primero que hay que hacer es "desenterrarla" y ubicarla en alguna de la barras de iconos.
Activamos el menú Ver--->Barras de Herramientas--->Personalizar; en la pestaña Comandos activamos la opción Herramientas y en la ventanilla de la derecha buscamos el icono de la cámara

Seleccionamos el icono y lo arrastramos con el mouse a alguna de las barras. Un buen lugar sería cercano a los iconos de copias y pegar



Con el icono a nuestra disposición, veremos cómo usarlo. Supongamos esta tabla (que muestra las visitas diarias por hora de un blog imaginario)



Seleccionamos los datos correspondientes a las horas de la mañana (el rango A5:C12). Apretamos el icono de la cámara, elegimos la zona (en la hoja activa o en otra hoja) como con cualquier imagen que queramos pegar



En nuestro caso pegamos la imagen al lado de la tabla. Si cambiamos cualquier dato en el rango elegido, los datos en la imagen cambiarán de acuerdo.

De la misma manera podemos pegar la imagen de los datos en otra hoja, lo que permite exhibir datos que no pueden ser editados por el usuario si ocultamos la hoja con los datos y protegemos el cuaderno.


Categorías: Varios_

Technorati Tags: Excel

Fondos y marcas de agua en hojas de Excel

Quien haya descargado el archivo del Calendario del Campeonato Mundial de Fútbol habrá notado el fondo con el logo de Alemania 2006



Como hacemos para poner un fondo en una hoja de Excel? Seguimos los siguientes pasos:

1 – seleccione la hoja de cálculo donde queremos que aparezca el fondo (asegurarse que sólo una hoja este seleccionada)
2 – Activar el menú Formato—Hoja—Fondo



3 – Seleccionar la imagen que queremos para el fondo pulsar Insertar.

Excel no imprime los fondos y por lo tanto no pueden ser usados como marcas de agua. Pero se puede imitar una marca de agua (por ejemplo, que en cada hoja impresa aparezca el rótulo "Confidencial"), de la siguiente manera:

1 – en el menú Ver---Encabezado y Pie de Página elegir una de las zonas
2 - en la barra apretar el botón Personalizar encabezado o Personalizar pie de página
3 – elegir una de las secciones en la barra de iconos apretar el botón de insertar imágenes
4 – insertar la imagen deseada con un doble clic
5 – para afectar cambios en la imagen usar el botón Formato de imagen en la barra de iconos.


Categorías: Varios_

Technorati Tags: Excel

Evitar resultados #¡DIV/0!– La formula mas corta que conozco.

Cuando alguna fórmula realiza una división por cero, Excel exhibe el resultado #¡DIV/0!. Por ejemplo, en la fórmula =A1/A2, si A2=0, Excel muestra un resultado de error.
Cuando la división por cero es inevitable y no queremos que Excel exhiba #¡DIV/0! podemos usar formulas como: =SI(ESERROR(A1/A2),0,A1/A2) o esta que es un poco más corta =SI(A2=0,0, A1/A2).

Cuál es la fórmula más corta para este caso? En el forum Exceluciones, en el cual participo últimamente, KL propone la siguiente fórmula:

=--SI(A2,A1/A2)

Como él, no creo que exista una fórmula mas breve para este caso. Cómo funciona? Hay que prestar atención a estos detalles:

Excel asocia el 0 (cero) con el resultado FALSO de una propuesta lógica y el número 1 con el resultado VERDADERO.

La función SI tiene en nuestro ejemplo solo dos argumentos, la prueba lógica y el resultado en caso que la prueba lógica de VERDADERO.

Cuando A2 = 0, Excel evalúa el primer argumento de la función SI, al ser este argumento igual a 0, Excel lo evalúa como FALSO, por lo tanto da como resultado FALSO.

Los dos signos "-" que preceden a la función cumplen la tarea de multiplicar el resultado por 1 (-1X-1).
Al multiplicar FALSO por 1, Excel convierte a FALSO en su expresión numérica: 0.

Categorías: Funciones&Formulas_

Technorati Tags: Excel, #¡DIV/0!

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