Rango interactivo de 3 valores mayores y menores en Excel

Compatibilidad: Truco compatible con Excel 365Truco compatible con Excel 2019Truco compatible con Excel 2016Truco compatible con Excel 2013Truco compatible con Excel 2010

Para la gestión de ventas necesitamos un rango interactivo de 3 valores mayores y menores en Excel.

Rango interactivo de 3 valores mayores y menores en Excel

Emplearemos las siguientes funciones anidándolas:

  • Función SI. Comprueba si se cumple una condición y devuelve un valor si se evalúa como verdadero y otro si se evalúa como falso.

  • Función K.ESIMO.MAYOR. Devuelve el valor k-ésimo mayor de un conjunto de datos.

  • Función K.ESIMO.MENOR. Devuelve el valor k-ésimo menor de un conjunto de datos.

Disponemos de las ventas de nuestros productos por país y necesitamos conocer el importe mayor y menor por país.

ABCDEF
1 EspañaMéxicoColombiaChilePerú
2Producto 11.250971.20014.5002.100
3Producto 210.5705.7002.1005.700975
4Producto 35.7005.2009755.2001.025
5Producto 45.2004.5001.0254.5005.700
6Producto 54.5002.1001.0252.1005.200
7Producto 62.1002.1005751.0254.500
8Producto 797597512.6005752.100
9Producto 81.0251.0251.02512.6001.025
10Producto 9575375575875575
11Producto 1012.6001.60012.60031012.600
 
  • FORMATO PRIMERA COLUMNA

  1. 3 VALORES MAYORES. Seleccionar el rango B2:B11.

  2. Ir a: Cinta de Opciones / Pestaña: Inicio / Grupo: Estilos / Formato condicional / Nueva regla. Mostrará la ventana del administrador.

    Ventana de nueva regla de formato Excel
  3. Ir a: Seleccionar un tipo de regla / pulsar sobre: Utilice una fórmula que determine las celdas para aplicar formato.

  4. Ir a: Dar formato a los valores donde esta fórmula sea verdadera / Escribir:

    =SI($A$1=1;SI(B2>=K.ESIMO.MAYOR($B$2:$B$11;3);VERDADERO;FALSO))
  5. Pulsar el botón formato / Aplicar relleno, borde o fuente. Para mostrar las celdas con la condición de los 3 valores más altos.

  6. Pulsar el botón Aceptar. Cierra la ventana de formato de celda.

  7. Pulsar el botón Aceptar. Cierra la ventana de nueva regla.

  8. 3 VALORES MENORES. Seleccionar el rango B2:B11.

  9. Ir a: Cinta de Opciones / Pestaña: Inicio / Grupo: Estilos / Formato condicional / Nueva regla. Mostrará la ventana del administrador.

    Ventana de nueva regla de formato Excel
  10. Ir a: Seleccionar un tipo de regla / pulsar sobre: Utilice una fórmula que determine las celdas para aplicar formato.

  11. Ir a: Dar formato a los valores donde esta fórmula sea verdadera / Escribir:

    =SI($A$1=2;SI(B2<=K.ESIMO.MENOR($B$2:$B$11;3);VERDADERO;FALSO))
  12. Pulsar el botón formato / Aplicar relleno, borde o fuente. Para mostrar las celdas con la condición de los 3 valores más bajos.

  13. Pulsar el botón Aceptar. Cierra la ventana de formato de celda.

  14. Pulsar el botón Aceptar. Cierra la ventana de nueva regla.

 
  • FORMATO SEGUNDA COLUMNA

Repetimos el proceso anterior para cada columna.

  1. 3 VALORES MAYORES. Seleccionar el rango C2:C11.

  2. Escribir la fórmula:

    =SI($A$1=1;SI(C2>=K.ESIMO.MAYOR($C$2:$C$11;3);VERDADERO;FALSO))
  3. 3 VALORES MENORES. Seleccionar el rango C2:C11.

  4. Escribir la fórmula:

    =SI($A$1=2;SI(C2<=K.ESIMO.MENOR($C$2:$C$11;3);VERDADERO;FALSO))
 
  • FORMATO TERCERA COLUMNA

Repetimos el proceso anterior para cada columna.

  1. 3 VALORES MAYORES. Seleccionar el rango D2:D11.

  2. Escribir la fórmula:

    =SI($A$1=1;SI(D2>=K.ESIMO.MAYOR($D$2:$D$11;3);VERDADERO;FALSO))
  3. 3 VALORES MENORES. Seleccionar el rango D2:D11.

  4. Escribir la fórmula:

    =SI($A$1=2;SI(D2<=K.ESIMO.MENOR($D$2:$D$11;3);VERDADERO;FALSO))
 
  • FORMATO CUARTA COLUMNA

Repetimos el proceso anterior para cada columna.

  1. 3 VALORES MAYORES. Seleccionar el rango E2:E11.

  2. Escribir la fórmula:

    =SI($A$1=1;SI(E2>=K.ESIMO.MAYOR($E$2:$E$11;3);VERDADERO;FALSO))
  3. 3 VALORES MENORES. Seleccionar el rango E2:E11.

  4. Escribir la fórmula:

    =SI($A$1=2;SI(E2<=K.ESIMO.MENOR($E$2:$E$11;3);VERDADERO;FALSO))
 
  • FORMATO QUINTA COLUMNA

Repetimos el proceso anterior para cada columna.

  1. 3 VALORES MAYORES. Seleccionar el rango F2:F11.

  2. Escribir la fórmula:

    =SI($A$1=1;SI(F2>=K.ESIMO.MAYOR($F$2:$F$11;3);VERDADERO;FALSO))
  3. 3 VALORES MENORES. Seleccionar el rango F2:F11.

  4. Escribir la fórmula:

    =SI($A$1=2;SI(F2<=K.ESIMO.MENOR($F$2:$F$11;3);VERDADERO;FALSO))
 
  • CONTROLES: BOTÓN DE OPCIONES

Mediante los botones de opciones indicamos los valores mayores o menores.

  1. Mostrar la pestaña de desarrollador en la cinta de opciones (ver proceso).

  2. CUADRO DE GRUPO. Ir a: Cinta de opciones / Pestaña: Desarrollador / Grupo: Controles / Insertar / Controles de formulario: Cuadro de grupo / Clic sobre la hoja Excel. Insertará el control cuadro de grupo.

  3. Pulsar doble clic sobre la etiqueta del control / Escribir: Ranking de Ventas.

    Hoja Excel con control Cuadro de grupo
  4. BOTÓN DE OPCIÓN MAYOR. Ir a: Cinta de opciones / Pestaña: Desarrollador / Grupo: Controles / Insertar / Controles de formulario: Botón de opción / Clic sobre el interior del cuadro de grupo.

  5. Pulsar doble clic sobre la etiqueta del control / Escribir: 3 mayores.

    Hoja Excel con botón de opciones 3 mayores
  6. Situar el cursor del ratón sobre el texto del botón de opciones / Botón derecho del ratón / Formato de control. Mostrará la ventana del control.

  7. Ir a Pestaña: Control / Valor: Activar la opción sin activar / Ir a: Vincular con la celda / Escribir: $A$1.

    Ventana de formato de control Excel
  8. BOTÓN DE OPCIÓN MENOR. Ir a: Cinta de opciones / Pestaña: Desarrollador / Grupo: Controles / Insertar / Controles de formulario: Botón de opción / Clic sobre el interior del cuadro de grupo.

  9. Pulsar doble clic sobre la etiqueta del control / Escribir: 3 menores.

    Hoja Excel con botón de opciones 3 menores
  10. Situar el cursor del ratón sobre el texto del botón de opciones / Botón derecho del ratón / Formato de control. Mostrará la ventana del control.

  11. Ir a Pestaña: Control / Valor: Activar la opción sin activar / Ir a: Vincular con la celda / Escribir: $A$1.

Nivel de dificultad: Avanzado Excel Avanzado

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.