Ordenar y comparar valores mediante controles en Excel

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

Podemos crear un informe para ordenar y comparar valores mediante controles en Excel.

Ordenar y comparar valores mediante controles en Excel

Disponemos de 25 productos vendidos y deseamos comparar las ventas con los más vendidos por país.

  • ORIGEN DE DATOS

En la Hoja: Datos / Rango B1:H26 disponemos del origen de datos con las ventas de los productos por país.

Origen de datos con las ventas producto y país
  • CÁLCULOS

La hoja Cálculos incluyen los cálculos para la plantilla.

  1. REFERENCIA CONTROLES. Ir a: Celda C1 / Escribir: 1.

  2. Ir a: Celda C2 / Escribir: 1.

  3. Ir a: Celda C3 / Escribir:

    =ELEGIR(C2;Datos!D1;Datos!E1;Datos!F1;Datos!G1;Datos!H1;Datos!B1)
    Hoja Excel con las referencias de controles
  4. CÁLCULOS: 10 MEJORES. Ir a: Celda B7 / Escribir:

    =$C$1+FILAS($A$7:A7)-1
  5. Copiar la celda B7 / Pegar en el rango B8:H16. Mostrará los números de referencia para los 10 primeros productos.

  6. Ir a: Celda C6 / Escribir:

    =Datos!C1
  7. Copiar la celda C6 / Pegar en el rango D6:H6. Como los encabezados de los datos.

  8. Ir a: Celda C7 / Escribir:

    =INDICE(DatosOrdenados;$B7;COLUMNAS($B$6:C6))
  9. Copiar la celda C7 / Pegar en el rango C7:H16.

    Hoja Excel con los cálculos de los 10 mejores
  10. POSICIÓN DELEGACIÓN. Ir a: Celda B19 / Escribir:

    =C3
  11. Ir a: Celda B20 / Escribir:

    =ELEGIR($C$2;Datos!D2;Datos!E2;Datos!F2;Datos!G2;Datos!H2;26-Datos!B2)+0,000001*FILAS(Datos!$A$2:A2)
  12. Copiar la celda B20 / Pegar en el rango C21:C44.

  13. Ir a: Celda C20 / Escribir:

    =JERARQUIA(B20;$B$20:$B$44)
  14. Copiar la celda C20 / Pegar en el rango C21:C44.

    Hoja Excel con los cálculos de posición de delegación
  15. REGERENCIA GENERAL. Ir a: Celda E20 / Escribir:

    =FILAS($E$20:E20)
  16. Ir a: Celda E21 / Escribir:

    =FILAS($E$20:E21)
  17. Copiar la celda E21 / Pegar en el rango E22:E44.

  18. Seleccionar el rango F20:F44 / Escribir:

    =INDICE(Datos!B2:B26;COINCIDIR($E20:$E44;$C$20:$C$44;0))
  19. Pulsar la tecla F2, para editar la fórmula / Pulsar la tecla Control y mantenerla pulsada / Pulsar la tecla Mayús y mantner las 2 teclas pulsadas / Pulsar la tecla Enter. Insertará la fórmula entre llaves como matricial {=INDICE (Datos!B2:B26; COINCIDIR($E20:$E44; $C$20:$C$44; 0))}.

  20. Seleccionar el rango G20:G44 / Escribir:

    =INDICE(Datos!C2:C26;COINCIDIR($E20:$E44;$C$20:$C$44;0))
  21. Pulsar la tecla F2, para editar la fórmula / Pulsar la tecla Control y mantenerla pulsada / Pulsar la tecla Mayús y mantner las 2 teclas pulsadas / Pulsar la tecla Enter. Insertará la fórmula entre llaves como matricial {=INDICE(Datos!C2:C26; COINCIDIR($E20:$E44; $C$20:$C$44; 0))}.

  22. Seleccionar el rango H20:H44 / Escribir:

    =INDICE(Datos!D2:D26;COINCIDIR($E20:$E44;$C$20:$C$44;0))
  23. Pulsar la tecla F2, para editar la fórmula / Pulsar la tecla Control y mantenerla pulsada / Pulsar la tecla Mayús y mantner las 2 teclas pulsadas / Pulsar la tecla Enter. Insertará la fórmula entre llaves como matricial {=INDICE (Datos!D2:D26; COINCIDIR($E20:$E44; $C$20:$C$44; 0))}.

  24. Seleccionar el rango I20:I44 / Escribir:

    =INDICE(Datos!E2:E26;COINCIDIR($E20:$E44;$C$20:$C$44;0))
  25. Pulsar la tecla F2, para editar la fórmula / Pulsar la tecla Control y mantenerla pulsada / Pulsar la tecla Mayús y mantner las 2 teclas pulsadas / Pulsar la tecla Enter. Insertará la fórmula entre llaves como matricial {=INDICE(Datos!E2:E26; COINCIDIR($E20:$E44; $C$20:$C$44; 0))}.

  26. Seleccionar el rango J20:J44 / Escribir:

    =INDICE(Datos!F2:F26;COINCIDIR($E20:$E44;$C$20:$C$44;0))
  27. Pulsar la tecla F2, para editar la fórmula / Pulsar la tecla Control y mantenerla pulsada / Pulsar la tecla Mayús y mantner las 2 teclas pulsadas / Pulsar la tecla Enter. Insertará la fórmula entre llaves como matricial {=INDICE(Datos!F2:F26; COINCIDIR($E20:$E44; $C$20:$C$44; 0))}.

  28. Seleccionar el rango K20:K44 / Escribir:

    =INDICE(Datos!G2:G26;COINCIDIR($E20:$E44;$C$20:$C$44;0))
  29. Pulsar la tecla F2, para editar la fórmula / Pulsar la tecla Control y mantenerla pulsada / Pulsar la tecla Mayús y mantner las 2 teclas pulsadas / Pulsar la tecla Enter. Insertará la fórmula entre llaves como matricial {=INDICE(Datos!G2:G26; COINCIDIR($E20:$E44; $C$20:$C$44; 0))}.

  30. Seleccionar el rango L20:L44 / Escribir:

    =INDICE(Datos!H2:H26;COINCIDIR($E20:$E44;$C$20:$C$44;0))
  31. Pulsar la tecla F2, para editar la fórmula / Pulsar la tecla Control y mantenerla pulsada / Pulsar la tecla Mayús y mantner las 2 teclas pulsadas / Pulsar la tecla Enter. Insertará la fórmula entre llaves como matricial {=INDICE(Datos!H2:H26; COINCIDIR($E20:$E44; $C$20:$C$44; 0))}.

    Datos de referencia
     
  32. TODOS. Ir a: Celda N20 / Escribir: 1.

  33. Seleccionar el rango N21:N45 / Escribir:

    =INDICE($H$20:$L$44;Datos!$B$2:$B$26;Calculos!$N$20)
  34. Pulsar la tecla F2, para editar la fórmula / Pulsar la tecla Control y mantenerla pulsada / Pulsar la tecla Mayús y mantner las 2 teclas pulsadas / Pulsar la tecla Enter. Insertará la fórmula entre llaves como matricial {=INDICE($H$20:$L$44; Datos!$B$2:$B$26; Calculos!$N$20)}.

    Datos resumen de todos los datos
     
  35. 10 MEJORES. Ir a: P20 / Escribir:

    =N20
  36. Seleccionar el rango P21:P30 / Escribir:

    =INDICE($D$7:$H$16;$E$20:$E$29;$P$20)
  37. Pulsar la tecla F2, para editar la fórmula / Pulsar la tecla Control y mantenerla pulsada / Pulsar la tecla Mayús y mantner las 2 teclas pulsadas / Pulsar la tecla Enter. Insertará la fórmula entre llaves como matricial {=INDICE($D$7:$H$16; $E$20:$E$29; $P$20)}.

    Datos resumen de los 10 mejores
  • PLANTILLA

  1. NÚMERO PRODUCTO. Ir a: Celda B4 / Escribir:

    =Calculos!B7
  2. Copiar la celda B4 / Pegar en el rango B5:B13.

    Hoja Excel con número de producto
  3. PRODUCTO. Ir a: Celda C4 / Escribir:

    =Calculos!C7
  4. Copiar la celda C4 / Pegar en el rango C4:H13.

    Hoja Excel con datos de productos
  5. CONTROL PRODUCTOS. Ir a: Cinta de opciones / Pestaña: Desarrollador / Grupo: Controles / Insertar / Controles de formulario / Botón de opción.

  6. Ir sobre la celda C3 / Trazar un área rectangular. Para insertar el control.

  7. Sobre el control / Botón derecho del ratón / Modificar texto / Escribir: Producto.

    Hoja Excel con control Productos
  8. FORMATO DE CONTROL. Sobre el control / Botón derecho del ratón / Formato de Control / Pestaña: Control / Vincular con la celda / Escribir:

    Calculos!$C$2
  9. Pulsar el botón Aceptar. Cierra la ventana y personaliza el control.

    Ventana formato de control
  10. CONTROL: ESPAÑA. Ir a: Cinta de opciones / Pestaña: Desarrollador / Grupo: Controles / Insertar / Controles de formulario / Botón de opción.

  11. Ir sobre la celda D3 / Trazar un área rectangular. Para insertar el control.

  12. Sobre el control / Botón derecho del ratón / Modificar texto / Escribir: España.

  13. FORMATO DE CONTROL: ESPAÑA. Sobre el control / Botón derecho del ratón / Formato de Control / Pestaña: Control / Vincular con la celda / Escribir:

    Calculos!$C$2
  14. Pulsar el botón Aceptar. Cierra la ventana y personaliza el control.

    Hoja Excel con control España
  15. CONTROL: MÉXICO. Ir a: Cinta de opciones / Pestaña: Desarrollador / Grupo: Controles / Insertar / Controles de formulario / Botón de opción.

  16. Ir sobre la celda E3 / Trazar un área rectangular. Para insertar el control.

  17. Sobre el control / Botón derecho del ratón / Modificar texto / Escribir: México.

  18. FORMATO DE CONTROL: MÉXICO. Sobre el control / Botón derecho del ratón / Formato de Control / Pestaña: Control / Vincular con la celda / Escribir:

    Calculos!$C$2
  19. Pulsar el botón Aceptar. Cierra la ventana y personaliza el control.

    Hoja Excel con control México
  20. CONTROL: COLOMBIA. Ir a: Cinta de opciones / Pestaña: Desarrollador / Grupo: Controles / Insertar / Controles de formulario / Botón de opción.

  21. Ir sobre la celda F3 / Trazar un área rectangular. Para insertar el control.

  22. Sobre el control / Botón derecho del ratón / Modificar texto / Escribir: Colombia.

  23. FORMATO DE CONTROL: COLOMBIA. Sobre el control / Botón derecho del ratón / Formato de Control / Pestaña: Control / Vincular con la celda / Escribir:

    Calculos!$C$2
  24. Pulsar el botón Aceptar. Cierra la ventana y personaliza el control.

    Hoja Excel con control Colombia
  25. CONTROL: PERÚ. Ir a: Cinta de opciones / Pestaña: Desarrollador / Grupo: Controles / Insertar / Controles de formulario / Botón de opción.

  26. Ir sobre la celda G3 / Trazar un área rectangular. Para insertar el control.

  27. Sobre el control / Botón derecho del ratón / Modificar texto / Escribir: Perú.

  28. FORMATO DE CONTROL: PERÚ. Sobre el control / Botón derecho del ratón / Formato de Control / Pestaña: Control / Vincular con la celda / Escribir:

    Calculos!$C$2
  29. Pulsar el botón Aceptar. Cierra la ventana y personaliza el control.

    Hoja Excel con control Perú
  30. CONTROL: CHILE. Ir a: Cinta de opciones / Pestaña: Desarrollador / Grupo: Controles / Insertar / Controles de formulario / Botón de opción.

  31. Ir sobre la celda H3 / Trazar un área rectangular. Para insertar el control.

  32. Sobre el control / Botón derecho del ratón / Modificar texto / Escribir: Chile.

  33. FORMATO DE CONTROL: CHILE. Sobre el control / Botón derecho del ratón / Formato de Control / Pestaña: Control / Vincular con la celda / Escribir:

    Calculos!$C$2
  34. Pulsar el botón Aceptar. Cierra la ventana y personaliza el control.

    Hoja Excel con control Chile
  35. CONTROL BARRA DE DESPLAZAMIENTO. Ir a: Cinta de opciones / Pestaña: Desarrollador / Grupo: Controles / Insertar / Controles de formulario / Barra de desplazamiento.

  36. Trazar un área rectangular sobre el rango I3:I13. Para insertar el control.

  37. FORMATO DE CONTROL. Sobre el control / Botón derecho del ratón / Formato de Control / Pestaña: Control:

    Ventana de formato de control

    - Ir a: Valor actual / Escribir: 1.
    - Ir a: Valor mínimo / Escribir: 1.
    - Ir a: Valor máximo / Escribir: 16.
    - Ir a: Incremento / Escribir: 1.
    - Ir a: Cambio de página / Escribir: 10.
    - Ir a: Vincular con la celda / Escribir:

    Calculos!$C$1
  38. Pulsar el botón Aceptar. Cierra la ventana y personaliza el control.

    Hoja Excel con control de barra de desplazamiento
  • FORMATO CONDICIONAL

Al pulsar sobre el control del país, se remarca el color de su columa.

  1. Seleccionar el rango C4:C13. Columna para remarcar al activarla.

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

    Ventana de editar regla de formato
  3. Ir a: Seleccionar un tipo de regla / Clic 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:

    =Calculos!$C$2=6
  5. Pulsar el botón Formato / Aplicar el color de relleno deseado.

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

  7. Pulsar el botón Aceptar. Cierra la ventana del administrador de reglas y aplica el formato condicional.

Hoja Excel con formato condicional en Producto al seleccionarlo
  • FORMATO

  1. Aplicar formato de celdas, relleno, bordes, fuente, etc.

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.