Cursos Excel Avanzado y Tablas dinámicas... y más. Mayo 2013 por Excelforo.

Presento la edición de Cursos de Excel y Macros online con tutor personal de Mayo 2013.

Los cursos de Excel abiertos para el proximo Mayo son:

Curso Excel Avanzado para versiones 2007/2010

(ver más)

Curso Excel Nivel Medio (Nuevo!!!)

(ver más)

Curso Excel Financiero

(ver más)

Curso Tablas dinámicas en Excel

(ver más)

Curso Macros Iniciación

(ver más)

Curso Macros Medio

(ver más)

Curso preparación MOS Excel 2010 (Examen 77-882)

(ver más)

Esta nueva edición de Cursos de Excel y macros en modalidad elearning (online) dará comienzo el próximo día 1 de Mayo de 2013.
Con la confianza de siempre....Anímate!!

También formación Excel a empresas. Explota los recursos a tu alcance (ver más).

Próximamente Curso Microsoft Access.

Informarte sin compromiso en cursos@excelforo.com o directamente en www.excelforo.com.

Recuerda que ahora también imparto clases particulares de Excel en Madrid te interesa?

Seguir leyendo...

Copiar y Pegar como imagen en un libro de Excel.

Hace bastante tiempo escribí dando una aplicación al pegado vinculado de imágenes (ver).
En esta ocasión le daremos otro enfoque al asunto, no orientado al tema de gráficos, si no a salvar una incompatibilidad con los Anchos de columnas en la plantilla compuesta.
Comencemos viendo nuestra disposición de datos:

El problema a solucionar es que al marcar el rango de columnas B:F y autoajustar su ancho, la Tabla de alumnos (rango B8:F19) o el rango de cabecera informativa B2:F6, quedan desproporcionados... lógicamente el Autoajuste de ancho de columna toma el Ancho mayor de cada columna.
Sin embargo, nosotros queremos independencia absoluta entre ambos rangos (Tabla y Cabecera).
La solución podría pasar por probar a combinar celdas en la Cabecera informativa (rango B2:F6) hasta lograr una proporción y aspecto que nos guste... pero es algo muy manual.
Apostaremos entonces por el copiado de imágnes vínculadas.

Lo primero que haremos será cortar y pegar el rango B2:F6 (cabecera informativa) en otro lugar, yo lo he copiado en otra hoja (Hoja 2); además he eliminado de esta hoja las líneas de división (ficha Vista > grupo Mostrar > check Líneas de cuadrícula):

Siguiente paso, en la Hoja2, seleccionamos el rango B3:F7 (donde hemos pegado la cabecera anterior) y le asignamos un Nombre:
datos =Hoja2!$B$3:$F$7

Con el rango 'datos' (Hoja2!$B$3:$F$7) seleccionado, accedemos a la ficha Inicio > grupo Portapapeles > botón Copiar > Copiar como imagen, lo que nos abrirá una ventana diálogo donde configurar el tipo de copiado, en cuanto a Apariencia (como en pantalla o impresora) y en cuanto a Formato (imagen o mapa de bits):

Ahora nos iremos a nuestra Hoja1, y pegaremos la imagen donde queramos... aproximadamente comenzando en la celda B2 (aunque esto no importa). Una vez pegada la imagen, y con esta Activa, nos iremos a la barra de fórmulas y escribiremos
=datos
con esto estamos vinculando nuestra imagen al contenido del rango 'datos':

Seguir leyendo...

Doble búsqueda anidada en Excel.

Veremos hoy un ejercicio típico de oposiciones a las administraciones públicas; se trata de emplear una doble búsqueda, con la función BUSCAR y BUSCARV. Son dos funciones muy conocidas, pero en este caso combinaremos ambas, añadiéndole algo de valor al usar Nombres definidos.
El objetivo es conseguir un Precio por Curso de Excel, a partir de una Tabla llamada 'TablaPrecios', y asociarla al alumno matriculado, conociendo el curso al que pertenece en base a una marca en otra tabla de referencias cruzadas. Veamos el planteamiento y solución en la imagen siguiente:

haz clic en la imagen

La meta a lograr es completar el rango E18:E27, obteniendo el curso en el que se encuentra matriculado el alumno en la Tabla D2:K12, para posteriormente determina el Precio del curso en la TablaPrecios.
Para facilitar el trabajo hemos asignado a ciertos rangos de nuestra hoja los siguientes nombres definidos:
Alumno001 =Hoja1!$E$3:$K$3
Alumno002 =Hoja1!$E$4:$K$4
Alumno003 =Hoja1!$E$5:$K$5
Alumno004 =Hoja1!$E$6:$K$6
Alumno005 =Hoja1!$E$7:$K$7
Alumno006 =Hoja1!$E$8:$K$8
Alumno007 =Hoja1!$E$9:$K$9
Alumno008 =Hoja1!$E$10:$K$10
Alumno009 =Hoja1!$E$11:$K$11
Alumno010 =Hoja1!$E$12:$K$12

La fórmula buscada es para E12 (luego bastará arrastar hacia abajo):
=BUSCARV(BUSCAR("xx";INDIRECTO($D18);$E$2:$K$2);TablaPrecios;2;0)

La explicación es sencilla, ya que replica los pasos comentados. Primero buscamos para cada alumno el curso que le corresponde en el rango D2:K12, mediante la función BUSCAR:
BUSCAR("xx";INDIRECTO($D18);$E$2:$K$2)
Con esta función encontramos el valor 'xx' en el rango correspondiente al alumno en cuestión; rango que determinamos mediante la función INDIRECTO. BUSCAR encuentra en el vector comparción del rango definido y devuelve del Vector resultado en E2:K2 el nombre del curso.
En la imagen anterior podemos ver el resultado en la columna auxiliar G17:G27 (o una variante con la función INDICE y COINCIDIR en el rango J17:J27).
Una vez conseguido el nombre del curso correspondiente a cada alumno, es fácil alcanzar el Precio de cada curso, ya que es suficiente aplicar este nombre en una función BUSCARV, para que encuentre en la primera columna de la matriz de búsqueda (para nosotros la TablaPrecios) y retornar el Precio que corresponda.
=BUSCARV(Nombre Curso;TablaPrecios;2;0)
Como podemos comprobar el resultado es válido tal como esperabamos.

Seguir leyendo...

Obtener Número de semana dentro de un mes para una fecha dada. La función NUM.SEMANA.

Todos conocemos la función NUM.SEMANA que nos devuelve el número de semana para un día cualquiera del año, un valor entre 1 y 52 (número de semanas total del año). Es una función bastante sencilla, ya que sólo requiere dos argumentos:
=NUM.DE.SEMANA(núm_de_serie;[tipo_de_devolución])
siendo el segundo argumento con el que indicamos en qué día empieza la semana (lunes, martes, etc).

La misión de hoy va algo más lejos, ya que no queremos concoer el número de semana respecto al total del año, si no el número de semana correspondiente dentro de un mes... es decir, cada mes, tendrá un valor de 1,2,3,4,5 o incluso 6 semanas.
Para disponer de un ejemplo, y poder comprobar nuestro cálculo, recuperaremos una macro explicada en este blog tiempo atrás (ver como construir un calendario con macros).
Si bien he realizado una mínima corrección para comenzar las semanas de los meses en Lunes (contante vbMonday). Y esto es lo que conseguimos:

Por verificar lo que buscamos. Si nos fijamos en el mes de diciembre de 2013, vemos que el mes comienza el 01/12/2013, Domingo; esta sería la semana 1 del mes, y el 31/12/2013, Martes correspondería a la semana 6 del mes.

Con este aspecto claro, vamos a ver nuestra fórmula, calculada sobre cualquier día entre el 01/01/2013 y 31/12/2013, logrado con una función =ALEATORIO.ENTRE("01/01/2013";"31/12/2013"), que hemos colocado en al celda AB3. Nuestra fórmula en AC3 es:
=NUM.DE.SEMANA(AB3;2)-NUM.DE.SEMANA(FIN.MES(AB3;-1)+1;2)+1

haz clic en la imagen

Podemos actualizar la hoja cuantas veces queramos y comprobar como efectivamente la fórmula es correcta...

Pasamos a explicarla en detalle. Comenzaremos por la parte más profunda:
FIN.MES(AB3;-1)+1
con esta función obtenemos, primero, el último día del mes anterior al de la fecha de estudio, al que sumando +1 posteriormente, obviamente logramos el primer día del mes de la fecha a analizar.
Con la fecha primero de mes anidada en la función NUM.SEMANA logramos entonces el número de semana absoluto (de 1 a 52) en que comienza el mes al que corresponde la fecha a estudiar.
NUM.DE.SEMANA(FIN.MES(AB3;-1)+1;2)
Si este valor lo restamos de la semana absoluta de la fecha de estudio:
NUM.DE.SEMANA(AB3;2)
por diferencias tendríamos el valor dentro del mes.. sólo hace falta un ajuste a la fórmula, sumándo +1 a esa diferencia tendremos el valor exacto.
=NUM.DE.SEMANA(AB3;2)-NUM.DE.SEMANA(FIN.MES(AB3;-1)+1;2)+1

Seguir leyendo...

Un Espirógrafo en Excel.

Seguro que de niños alguna vez jugamos con un Espirógrafo (ver wikipedia). Ese juguete compuesto de varias reglas 'raras' que nos permitían realizar dibujos imposibles punteando con un lápiz.
Bien, pues aquellos dibujos, eran parte de complejas curvas matemáticas, con sus correspondientes fórmulas geométricas
En la entrada de hoy, a partir de una función geométrica, apoyada en diferentes parámetros, replicaremos el dibujo realizado por un Espirógrafo. Se hace necesario aplicar una macro que actualice los valores de nuestro dibujo, y simule nuestro lápiz sobre el papel y el extraño camino tomado...
Existen multitud de fórmulas geométricas, prácticamente todas basadas en cálculos con SEN y COSEN. Podemos ver algunas en nuestra amiga Wikipedia:
http://es.wikipedia.org/wiki/Epicicloide
http://es.wikipedia.org/wiki/Epitrocoide
En nuestro ejemplo emplearemos otra, que utiliza igualmente SEN y COSEN. ( la veremos más adelante).
La clave de todo nuestro ejemplo pasa por el empleo adecuado de Nombres definidos en su formato de fórmulas. Estas son todas las empleadas, prestando especial interés en las cuatro últimas:
p_1 =Espirografo!$B$11
p_2 =Espirografo!$B$12
p_3 =Espirografo!$B$13
r_1 =Espirografo!$B$3
r_2 =Espirografo!$B$4
r_3 =Espirografo!$B$5
s_1 =Espirografo!$B$7
s_2 =Espirografo!$B$8
s_3 =Espirografo!$B$9
t =(FILA(DESREF(Espirografo!$A$1;0;0;Espirografo!$B$1;1))- 1)*2*PI()/1000
tmax =MAX(t)
Xpoint =SI(Espirografo!$B$1>165;70;r_1*COS(2*PI()*s_1*tmax+p_1)+r_2*COS(2*PI()*s_2*tmax+p_2)+r_3*COS(2*PI()*s_3*tmax+p_3))
XSeries =r_1*COS(2*PI()*s_1*t+p_1)+r_2*COS(2*PI()*s_2*t+p_2)+r_3*COS(2*PI()*s_3*t+p_3)
Ypoint =r_1*SENO(2*PI()*s_1*tmax+p_1)+r_2*SENO(2*PI()*s_2*tmax+p_2)+r_3*SENO(2*PI()*s_3*tmax+p_3)
Yseries =r_1*SENO(2*PI()*s_1*t+p_1)+r_2*SENO(2*PI()*s_2*t+p_2)+r_3*SENO(2*PI()*s_3*t+p_3)

Veamos la disposición de los datos originales:

haz clic en la imagen

La macro, a insertar en un Módulo del Explorador de proyectos, dentro del Editor de VBA, sería:

Sub Dibujar()
'Iniciamos proceso con valores a cero
Range("B12").Value = 0
Range("B1").Value = 0

'Limitamos el contador en B1 hasta 150
While Range("B1").Value < 165
    'Incrementamos el contador en B1 de uno en uno
    Range("B1").Value = Range("B1").Value + 1
    'añadiendo un retraso controlado con un FOR NEXT después de cada incremento en B1
    For i = 1 To 150000
    Next i
    'Cede el control de la ejecución al sistema operativo,
    'para que éste pueda procesar otros eventos.
    'Permite la actualización del gráfico...
    DoEvents
Wend
'Añadimos un extra en la animación

'Para ello incrementamos el contador en la variable B12
'Limitamos el contador en B4 hasta 10
While Range("B12").Value < 10
     'Incrementamos el contador en B12 de 0.1 en 0.1
    Range("B12").Value = Range("B12").Value + 0.1
    'añadiendo un retraso controlado con un FOR NEXT después de cada incremento en B12
    For i = 1 To 150000
    Next i
    'Cede el control de la ejecución al sistema operativo,
    'para que éste pueda procesar otros eventos.
    'Permite la actualización del gráfico...
    DoEvents
Wend
End Sub

En este momento tenemos los datos, los cálculos realizados mediante las fórmulas en los Nombres definidos, y una macro que nos actualizará y simulará el movimiento, falta construir el Gráfico. Lo primero que debemos saber es que nuestro gráfico debe ser de Tipo Dispersión con lineas suavizadas, es importante ya que necesitamos incluir dos variables, dos puntos (que hemos llamado XSeries e YSeries y también Xpoint e Ypoint). Por pasos, desde el principio. Desde la Ficha Insertar > grupo Gráficos > Dispersión > Dispersión con líneas suavizadas. Esto incluirá en blanco un objeto gráfico. A continuación, desde la Barra de herramientas de gráficos > pestaña Diseño > grupo Datos > botón Seleccionar datos, y dentro de la venta diálogo, presionamos Agregar Serie. La completaremos con los valores siguientes: para los Valores X de la serie: =AnimacionGrafica.xlsm!XSeries para los Valores Y de la serie: =AnimacionGrafica.xlsm!YSeries Ojo con esta forma de introducir Nombres definidos en un gráfico!!!.

Repetimos la operación para una segunda serie, pero ahora asignándole los valores: para los Valores X de la serie: =AnimacionGrafica.xlsm!Xpoint para los Valores Y de la serie: =AnimacionGrafica.xlsm!Ypoint

Siguiente paso a trabajar sobre el gráfico, fijamos las Escalas de los ejes (horizontal y vertical), bastará marcar a Fijo los valores dados:

Seguir leyendo...

Posiciones en un rango de un valor buscado.

La mayoría de las funciones de búsqueda (BUSCAR, BUSCRAV, COINCIDIR, etc) que existen en Excel son muy prácticas a la hora de encontrar un valor buscado, pero todas ellas se paran en el primer valor que encuentran y coincide. En esta entrada aprenderemos una manera, mediante funciones, de localizar no sólo el primer valor coincidente, si no sucesivos.
Partiremos de un listado con diferentes elementos, entre los cuales se halla nuestro valor buscado: 'Excelforo' (remarcado en fondo amarillo):

El proceso de búsqueda se basa precisamente en la funcionalidad comentada, Excel devuelve el primer valor encontrado; de tal forma que el trabajo consistirá en construir un rango dinámico (donde buscar las coincidencias), adaptándolo al último valor encontrado. Veamos el resultado y la fórmula principal:
=COINCIDIR($E$2;INDIRECTO("A$"&E5+1&":$A$"&CONTARA(A:A));0)+E5

En el rango de resultados E5:E9 observamos como cada fórmula se basa en la anterior para componer el nuevo rango de búsqueda, que comienza en la fila siguiente al anterior valor encontrado. Nuestra fórmula emplea las funciones COINCIDIR e INDIRECTO para operar. Desglosamos nuestra fórmula explicándola paso a paso, centrándonos en la segunda posición (celda E6)...
Lo más profundo de la fórmula es el literal que se convertirá posteriormente en rango, lo que conseguimos con un concatenado:
"A$"&E5+1&":$A$"&CONTARA(A:A)

Seguir leyendo...

Formato personalizado avanzado en Excel.

Hoy nos adentraremos algo más profundamente en el tema del formato personalizado de celda, algo que he tratado con anterioridad (ver).
Comenzaremos recordando cuál es la estructura, cuáles son las cuatro secciones, de estos formatos personalizados:
Positivos; Negativos; Cero; Texto

En general, debemos saber también el uso de los caracteres más habituales. Así en la ayuda de Excel podemos leer:
0 (cero): Este marcador de posición de dígitos muestra los ceros no significativos si un número tiene menos dígitos que los ceros especificados en el formato. Por ejemplo, si escribe 8,9 y desea que se muestre como 8,90, use el formato #,00.
# (almohadilla): Este marcador de posición de dígitos sigue las mismas reglas que el 0 (cero). Sin embargo, Excel no muestra ceros adicionales cuando el número que escribe tiene menos dígitos a ambos lados de la coma decimal que el número de símbolos # especificados en el formato. Por ejemplo, si el formato personalizado es #,## y escribe 8,9 en la celda, se muestra el número 8,9.
? (cierre interrogación):Este marcador de posición de dígitos sigue las mismas reglas que el 0 (cero). Sin embargo, Excel agrega un espacio para los ceros no significativos a ambos lados de la coma decimal para que las comas decimales queden alineadas en la columna. Por ejemplo, el formato personalizado 0,0? alinea las posiciones decimales de los números 8,9 y 88,99 en una columna.
, (coma): Este marcador de posición de dígitos muestra la coma decimal en un número.
. (punto): Este marcador de posición de dígitos muestra la coma de miles, millones, etc en un número. Excel separa los millares con puntos si el formato contiene un punto incluido entre signos de almohadilla (#) o ceros. Un punto detrás de un marcador de posición de dígitos ajusta el número a 1.000. Por ejemplo, si el formato personalizado es #,0. y escribe 12.200.000 en la celda, se muestra el número 12,200,0.
@ (arroba): Si se incluye, la sección de texto siempre es la última sección del formato de número. Incluya un carácter de arroba (@) en la sección en la que desea mostrar el texto que escribe en la celda. Si el carácter @ se omite en la sección de texto, no se mostrará el texto que escriba. Si desea mostrar siempre caracteres de texto específicos con el texto escrito, incluya el texto adicional entre un carácter de dobles comillas (" "). Por ejemplo, "recibos brutos de "@
Si el formato no incluye una sección de texto, todos los valores no numéricos que escriba en una celda con ese formato aplicado no resultarán afectados por el formato. Además, toda la celda se convertirá en texto.

Veamos algunos ejemplos empleando estos caracteres antes de avanzar algo más:

haz clic en la imagen

Abordemos ahora el tema de los colores en el formato personalizado. Sabemos existen algunos colores predefinidos, y que estos deben incorporarse entre corchetes al principio de cada sección. Los colores básicos son:
[Negro], [Verde], [Blanco], [Azul], [Magenta], [Cian], [Amarillo] y [Rojo]
Ahora bien, podemos disponer de la paleta general de colores (los 56 estándar), escribiendo entre corchetes [Color n] (siendo n un valor entre 1 y 56).
Por ejemplo:
[Color 13]##.##0,000
[Cian]##.##0,000

Y llegamos al momento 'avanzado' de la entrada. Existen muchos más caracteres que podemos emplear como parte de nuestras cuatro secciones (€, $, +, (, :, ^, ', {, <, =, -, /, ), !, &, ~, }, >, carácter de espacio), sin embargo, nos detendremos en dos muy especiales.

El guión bajo (_) (Para agregar espacios): Para crear un espacio que tenga el ancho de un carácter en un formato de número, incluiremos un carácter de subrayado (_), seguido del carácter que deseamos utilizar. Por ejemplo, si colocáramos un paréntesis de cierre detrás de un carácter de subrayado, como _), los números positivos se alinearán correctamente con los números negativos que se incluyan entre paréntesis.
El asterisco (*) (Para repetir caracteres): Para repetir el siguiente carácter del formato hasta rellenar el ancho de la columna, incluiremos un asterisco (*) en el formato de número. Por ejemplo, escribir 0*- para incluir los guiones que hagan falta detrás de un número para rellenar la celda, o escriba *0 delante del formato para incluir ceros iniciales.
Estos dos caracteres nos permitirán jugar de una manera muy especial replicando alineaciones y justificaciones en nuestras celdas. Veamos en la imagen algunas aplicaciones:

haz clic en la imagen

Las explicaciones a estos formatos. El primero empleado para el rango B3:E3 ha sido:
;;;
Con este formato personalizado conseguimos que no se ve nada, aunque, aunque el dato existe, como se pueda ver en la barra de fórmulas.

Otro formato es para B4:E4:
##.##0,000_{_{;(##.##0,000)_{_{;0,00;_{_{@
donde hemo empleado el caracter _ (guión bajo o subrayado), que según decíamos genera un espacio, seguido en este ejemplo de una llave { deja un espacio en blanco a la izquierda o a la derecha del dato escrito en la celda.

De manera similar en B5:E6 formateamos con:
##.##0,000_X_X;(##.##0,000)_X_X;0,00;_X_X@
Vemos que es la misma estructura de formato, pero hemos sustituido la llave { por otro caracter X; el resultado es fácilmente observable, existe más espacio a izquierda a derecha... lógico ya que el tamaño de un caracter y otro son diferentes (ocupa más X que {).
En definitiva, replicamos el efecto de la Sangría o Indentado en las celdas.

En B6:E6 practicamos con la repetición de caracteres, es decir, aplicamos el asterisco. el formato es:
*. ##.##0,000;(##.##0,000) *.;0,00;*/ @
Vemos como repetimos a izquierda y derecha con diferentes caracteres (punto y barra), repitiendo dichos caracteres hasta completar el ancho de la celda.

De igual forma en B7:E7 con este formato:
* ##.##0,000;(##.##0,000) * ;0,00*/;@ *-
repetimos caracteres (en este caso el espacio en blanco, un guión - o una barra /) por la izquierda o derecha, imitando el efecto de alineación; ya que si repetimos el espacio por la derecha estaríamos viendo un número alineado por la izquierda igual que si fuera un texto!!!.

Finalizaremos con un ejemplo que recopile todo lo visto, aplicando colores en función del valor (de acuerdo a la tabla adjunta siguiente), con repeticiones y espacios:
[Rojo][<0]"Negativo "#.###0,000 * ;[Verde][>500]"Mayor que 500 "#.###0,000 * ;[Azul]"Entre 0 y 500 "#.###0,000 * ;[Cian]*-@

Seguir leyendo...

Ultimos días Cursos Online Excel Avanzado e Iniciación a las Macros... y más. Abril 2013

Últimos días para la matriculación en los Cursos de Excel online de Abril 2013.

Los cursos de Excel abiertos para el mes Abril son:

Curso Excel Avanzado para versiones 2007/2010

(ver más)

Curso Excel Nivel Medio (Nuevo!!!)

(ver más)

Curso Excel Financiero

(ver más)

Curso Tablas dinámicas en Excel

(ver más)

Curso Macros Iniciación

(ver más)

Curso Macros Medio

(ver más)

Curso preparación MOS Excel 2010 (Examen 77-882)

(ver más)

Con la confianza de siempre....Anímate!!

También formación Excel a empresas. Explota los recursos a tu alcance (ver más).

Próximamente Curso Microsoft Access.

Informarte sin compromiso en cursos@excelforo.com o directamente en www.excelforo.com.

Recuerda que ahora también imparto clases particulares de Excel en Madrid te interesa?

Seguir leyendo...

La función FILA de Excel en una ordenación.

Días atrás, mediante un comentario, un lector planteaba cómo lograr una ordenación directa e inversa desde el valor máximo de un rango
La cuestión planteada por un lector fue:

...Necesito que me rellene datos en una columna pero a partir de la celda que sea 0 y 0 es cuando sea el máximo equivalente de otra columna, pongo un ejemplo: Tengo una columna con 1,2,3,5,3,2,1 y otra columna al lado vacía, entonces en la columna de al lado, necesitaría que en la casilla correspondiente al máximo de la otra columna se pusiera un 0 y se rellenara hacia arriba y hacia abajo, en este caso se tendría que quedar algo así: 3,2,1,0,-1,-2,-3...

Me pareció interesante el reto, por lo particular de la ordenación, en un conteo desde la celda con valor máximo. Esto sería lo que indicaba y lo que buscamos:

La fórmula buscada es:
=FILA(INDIRECTO("$A$"&COINCIDIR(K.ESIMO.MAYOR($A$1:$A$7;1);$A$1:$A$7;0)))-FILA()

Si bien habrá que indicar que sólo es válida en caso de un valor máximo único!!!.

Detallemos y desgranemos nuestra fórmula.
En primer lugar averigüamos el valor máximo del rango con la función
K.ESIMO.MAYOR($A$1:$A$7;1)
valdría también
MAX($A$1:$A$7)
con ellas conocemos, en nuestro ejemplo, que el valor máximo es 5.
El siguiente paso es encontrar la posición dentro del rango de estudio $A$1:$A$7 de este valor máximo, lo que hacemos con la función COINCIDIR:
COINCIDIR(K.ESIMO.MAYOR($A$1:$A$7;1);$A$1:$A$7;0)
En nuestro ejemplo, coincide posición con fila de la celda, en otros casos habría que sumar alguna cantidad por la diferencia...
Al concatenar Fila obtenida al texto "$A$"&fila, tenemos una referencia (una celda). Para convertirla en algo entendible por Excel, anidamos el resultado en la función INDIRECTO.
El siguiente paso es obtener la fila de esa referencia en cuestión, por lo que nuestra referencia la utilizamos como argumento de la función FILA.
Finalizamos el cálculo restando la fila del valor máximo a la fila de cada uno de los elementos del rango, lo que devuelve precisamente la ordenación buscada.

Una manera, similar, pero más sencilla sería directamente:
=COINCIDIR(K.ESIMO.MAYOR($A$2:$A$9;1);$A$2:$A$9;0)+1-FILA()
Como vemos en este otro ejemplo, con filas de cabecera de por medio:

Seguir leyendo...