lunes, 16 de abril de 2012

Obtener encabezados

Hemos dispuesto en una tabla las ventas de varios productos en diferentes zonas (B2:F11). Queremos saber cuáles fueron los tres productos más vendidos y en qué zonas se vendieron (B13:E17).

Para resolver el ejercicio por el método que vamos a explicar, es necesario que todos los números de la tabla sean distintos. Usaremos las celdas del rango H2:K4 para hacer cálculos auxiliares; terminado el ejercicio podremos borrarlos.

Ponemos el número más grande (que corresponde al producto más vendido) en la celda C15:
=K.ESIMO.MAYOR($C$3:$F$11;1)    [Resultado: 7.002]

Ese número está en E6; es decir, en la fila 6 y en la columna 5. Vamos a determinar estos valores y ponerlos en H4 e I4 respectivamente.

En H4:
=SUMAPRODUCTO(FILA($C$3:$F$11)*(($C$3:$F$11)=C15))    [Resultado: 6]

En I4:
=SUMAPRODUCTO(COLUMNA($C$3:$F$11)*(($C$3:$F$11)=C15))    [Resultado: 5]

Para ver cómo funcionan estas fórmulas, analizaremos la primera (la otra funciona igual).

FILA($C$3:$F$11) devuelve una matriz con los números de las filas que ocupan cada uno de los valores de la tabla. Para comprobar que esto es cierto, seleccionamos M3:P11 y escribimos: =FILA($C$3:$F$11); terminamos pulsando Ctrl + Mayús + Intro.

(($C$3:$F$11)=C15) comprueba si cada valor de la tabla es igual a 7.002 (el valor de C15), devolviendo una matriz de valores VERDADERO y FALSO. Como el 7.002 no está repetido, sólo habrá un valor VERDADERO y el resto serán valores FALSO. Lo comprobaremos seleccionando R3:U11 y escribiendo: =(($C$3:$F$11)=C15); terminaremos con Ctrl + Mayús + Intro.

(FILA($C$3:$F$11)*(($C$3:$F$11)=C15)) multiplica las dos matrices anteriores. Como VERDADERO, a efectos numéricos, es 1 y FALSO es 0, el producto será una matriz que tendrá un único valor distinto de cero. Para realizar la comprobación, seleccionamos W3:Z11 y escribimos: =(FILA($C$3:$F$11)*(($C$3:$F$11)=C15)); terminamos con Ctrl + Mayús + Intro.

Sólo nos falta sumar todos los valores para obtener la fila en la que se encuentra el número 7.002. Podemos hacerlo con la función SUMA (en cuyo caso tendremos que terminar con Ctrl + Mayús + Intro) o con SUMAPRODUCTO (en cuyo caso terminaremos con Intro).

Una vez conocida la fila y la columna en la que está el producto más vendido, podemos determinar fácilmente los encabezados con DIRECCION e INDIRECTO.

En J4:
=INDIRECTO(DIRECCION(H4;2))    [Resultado: Microondas]

En K4:
=INDIRECTO(DIRECCION(2;I4))    [Resultado: Este]

Finalmente, pondremos sendas fórmulas compuestas en D15 y E15 para poder eliminar, si lo deseamos, las celdas auxiliares.

En D15:
=INDIRECTO(DIRECCION(SUMAPRODUCTO(FILA($C$3:$F$11)*(($C$3:$F$11)=C15));2))

En E15:
=INDIRECTO(DIRECCION(2;SUMAPRODUCTO(COLUMNA($C$3:$F$11)*(($C$3:$F$11)=C15))))

Las fórmulas para el segundo y tercer producto más vendidos son evidentes:

En C16:
=K.ESIMO.MAYOR($C$3:$F$11;2)    [Resultado: 6.375]

En C17:
=K.ESIMO.MAYOR($C$3:$F$11;3)    [Resultado: 5.688]

Arrastramos las fórmulas del rango D15:E15 hasta la fila 17 y el ejercicio estará terminado.

Como es lógico, hay otras alternativas al método propuesto para resolver el ejercicio. Ésta es una de ellas:

En H4:
=MAX(($C$3:$F$11=C15)*FILA($C$3:$F$11))    [Terminar con Ctrl + Mayús + Intro]

En I4:
=MAX(($C$3:$F$11=C15)*COLUMNA($C$3:$F$11))    [Terminar con Ctrl + Mayús + Intro]

En J4:
=INDIRECTO(DIRECCION(H4;2))

En K4:
=INDIRECTO(DIRECCION(2;I4))

Creamos las fórmulas compuestas:

En D15:
=INDIRECTO(DIRECCION(MAX(($C$3:$F$11=C15)*FILA($C$3:$F$11));2))    [Terminar con Ctrl + Mayús + Intro]

En E15:
=INDIRECTO(DIRECCION(2;MAX(($C$3:$F$11=C15)*COLUMNA($C$3:$F$11))))    [Terminar con Ctrl + Mayús + Intro]

Descargar archivo (SE21-Más vendidos.xls)

Descargar archivo (SE21-Más vendidos.xlsx)


1 comentario:

  1. Gracias por tu post, fue de mucha ayuda para lo que necesitaba. muy bien explicado y con los fundamentos para conocer cada uno de las funciones con las que deberían estar explicadas la mayoría de ejercicios que se encuentran en la web.
    Saludos desde Colombia.

    ResponderEliminar