Excel ya tiene un par de formas de ordenar los datos rápidamente.
Puede ordenar fácilmente un conjunto de datos utilizando los iconos de clasificación de la cinta o el cuadro de diálogo de clasificación.
Entonces, ¿por qué necesita saber cómo hacer esto usando VBA?
Saber cómo ordenar datos usando VBA puede ser útil cuando se incluye como parte de su código. Por ejemplo, suponga que obtiene un conjunto de datos diario / semanal que necesita formatear y ordenar en un orden específico.
Puede crear una macro para hacer todo esto por usted con un solo clic. Eso le ahorrará mucho tiempo y esfuerzo cada vez que lo haga.
Además, si crea paneles de Excel, puede llevar la capacidad de clasificación de Excel a un nuevo nivel al permitir que el usuario ordene los datos con solo hacer doble clic en el encabezado (como se muestra a continuación).
Cubriré cómo crear esto más adelante en este tutorial. Primero, aclaremos rápidamente los conceptos básicos.
Comprender el método Range.Sort en Excel VBA
Al ordenar usando VBA, necesita usar el método Range.Sort en su código.
El "Rango" serían los datos que está intentando ordenar. Por ejemplo, si está ordenando los datos en A1: A10, entonces "Rango" sería Rango ("A1: A10").
También puede crear un rango con nombre y usarlo en lugar de las referencias de celda. Por ejemplo, si creo un rango con nombre "DataRange" para las celdas A1: A10, también puedo usar Range ("DataRange")
Con el método de clasificación, debe proporcionar información adicional a través de parámetros. A continuación se muestran los parámetros clave que necesita conocer:
- Clave - aquí debe especificar la columna que desea ordenar. Por ejemplo, si desea ordenar la columna A, debe usar la clave: = Rango ("A1")
- Orden - aquí especifique si desea la clasificación en orden ascendente o descendente. Por ejemplo, si desea la clasificación en orden ascendente, utilizará Order: = xlAscending
- Encabezamiento - aquí especificas si tu conjunto de datos tiene encabezados o no. Si tiene encabezados, la clasificación comienza desde la segunda fila del conjunto de datos, de lo contrario, comienza desde la primera fila. Para especificar que sus datos tienen encabezados, usará Encabezado: = xlYes
Si bien estos tres son suficientes en la mayoría de los casos, puede leer más sobre los parámetros en este artículo.
Ahora veamos cómo usar el método Range.Sort en VBA para ordenar datos en Excel.
Ordenar una sola columna sin encabezado
Suponga que tiene una sola columna sin encabezado (como se muestra a continuación).
Puede usar el siguiente código para ordenarlo en orden ascendente.
Sub SortDataWithoutHeader () Range ("A1: A12"). Sort Key1: = Range ("A1"), Order1: = xlAscending, Header: = xlNo End Sub
Tenga en cuenta que he especificado el rango de datos manualmente como Rango ("A1: A12").
En caso de que haya cambios en los datos y se puedan agregar / eliminar valores, puede usar el siguiente código que se ajusta automáticamente en función de las celdas llenas en el conjunto de datos.
Sub SortDataWithoutHeader () Range ("A1", Range ("A1"). End (xlDown)). Sort Key1: = Range ("A1"), Order1: = xlAscending, Header: = xlNo End Sub
Tenga en cuenta que en lugar de Rango ("A1: A12"), he usado, Rango ("A1", Rango ("A1"). Fin (xlDown)).
Esto verificará la última celda llena consecutivamente en la columna y la incluirá en la clasificación. En caso de que haya espacios en blanco, solo considerará los datos hasta la primera celda en blanco.
También puede crear un rango con nombre y usar ese rango con nombre en lugar de las referencias de celda. Por ejemplo, si el rango nombrado es DataSet, su código ahora sería como se muestra a continuación.
Sub SortDataWithoutHeader () Range ("DataRange"). Sort Key1: = Range ("A1"), Order1: = xlAscending, Header: = xlNo End Sub
Ahora permítanme explicar rápidamente los parámetros utilizados en los ejemplos anteriores:
- Clave1: = Rango (“A1”): se especifica A1 para que el código sepa qué columna ordenar.
- Order1: = xlAscending: el orden especificado como xlAscending. Si desea que esté en orden descendente, use xlDescending.
- Encabezado: = xlNo: se especifica que no hay encabezados. Este también es el valor predeterminado. Entonces, incluso si omite esto, sus datos se ordenarán considerando que no tienen encabezados.
¿Se pregunta dónde poner este código VBA y cómo ejecutar la macro? ¡Lea este tutorial!
Ordenar una sola columna con encabezado
En el ejemplo anterior, el conjunto de datos no tenía encabezado.
Cuando sus datos tienen encabezados, debe especificar eso en el código para que la clasificación pueda comenzar desde la segunda fila del conjunto de datos.
Suponga que tiene un conjunto de datos como se muestra a continuación:
A continuación se muestra el código que ordenará los datos en orden descendente según las ventas de las tiendas.
Sub SortDataWithHeader () Range ("DataRange"). Sort Key1: = Range ("C1"), Order1: = xlDescending End Sub
Tenga en cuenta que he creado un rango con nombre - "DataRange", y usé este rango con nombre en el código.
Ordenar varias columnas con encabezados
Hasta ahora en este tutorial, hemos visto cómo ordenar una sola columna (con y sin encabezados).
Ahora, ¿qué sucede si desea ordenar en función de varias columnas?
Por ejemplo, en el siguiente conjunto de datos, ¿qué pasa si primero quiero ordenar por el código de estado y luego por la tienda?
Aquí está el código que ordenará varias columnas a la vez.
Sub SortMultipleColumns () With ActiveSheet.Sort .SortFields.Add Key: = Range ("A1"), Order: = xlAscending .SortFields.Add Key: = Range ("B1"), Order: = xlAscending .SetRange Range ("A1 : C13 ") .Header = xlYes .Apply End With End Sub
A continuación se muestra el resultado que obtendrá.
En el ejemplo anterior, los datos se ordenan primero por el código de estado (columna A). Luego, dentro de los datos del código de estado, se ordena nuevamente por la Tienda (Columna B). Este orden está determinado por el código en el que lo mencionas.
Clasificación de datos con doble clic en el encabezado
Si está creando un panel o desea más facilidad de uso en sus informes, puede escribir un código VBA que ordenará los datos cuando haga doble clic en los encabezados.
Algo como se muestra a continuación:
A continuación se muestra el código que le permitirá hacer esto:
Private Sub Worksheet_BeforeDoubleClick (ByVal Target As Range, Cancel As Boolean) Dim KeyRange As Range Dim ColumnCount As Integer ColumnCount = Range ("DataRange"). Columns.Count Cancelar = False Si Target.Row = 1 y Target.Column <= ColumnCount Entonces Cancelar = Verdadero Establecer KeyRange = Rango (Target.Address) Rango ("DataRange"). Ordenar Key1: = KeyRange, Encabezado: = xlYes End If End Sub
Tenga en cuenta que he creado un rango con nombre ("DataRange") y lo he usado en el código en lugar de usar las referencias de celda.
Tan pronto como hace doble clic en cualquiera de los encabezados, el código deshabilita la funcionalidad habitual de doble clic (que es entrar en el modo de edición) y usa esa celda como clave mientras ordena los datos.
También tenga en cuenta que a partir de ahora, este código ordenará todas las columnas solo en orden ascendente.
Tenga en cuenta que el doble clic es un disparador que permite a Excel ejecutar el código especificado. Estos desencadenantes, como hacer doble clic, abrir un libro de trabajo, agregar una nueva hoja de trabajo, cambiar una celda, etc., se denominan eventos y se pueden usar para ejecutar macros en Excel. Puede leer más sobre los eventos de Excel VBA aquí.¿Dónde poner este código?
Debe pegar este código en la ventana de código de la hoja en la que desea esta funcionalidad de clasificación de doble clic.
Para hacer esto:
- Haga clic con el botón derecho en la pestaña de la hoja.
- Haga clic en Ver código.
- Pegue el código en la ventana de código de la hoja en la que residen sus datos.
Ahora, ¿qué sucede si desea ordenar las dos primeras columnas ("Estado" y "Tienda") en orden ascendente, pero la columna "Ventas" en orden descendente?
Aquí está el código que lo hará:
Private Sub Worksheet_BeforeDoubleClick (ByVal Target As Range, Cancel As Boolean) Dim KeyRange As Range Dim ColumnCount As Integer ColumnCount = Range ("DataRange"). Columns.Count Cancelar = False Si Target.Row = 1 y Target.Column <= ColumnCount Entonces Cancelar = Verdadero Establecer KeyRange = Rango (Target.Address) If Target.Value = "Sales" Then SortOrder = xlDescending Else SortOrder = xlAscending End If Range ("DataRange"). Sort Key1: = KeyRange, Header: = xlYes, Order1: = SortOrder End If End Sub
En el código anterior, verifica si la celda en la que se hace doble clic es el encabezado Ventas o no. En caso afirmativo, asigna el valor xlDescending a la variable SortOrder, de lo contrario lo convierte en xlAscending.
Ahora llevemos esto un poco más lejos y mostremos un marcador visual (flecha y celda de color) en el encabezado cuando esté ordenado.
Algo como se muestra a continuación:
Para obtener esto, agregué una nueva hoja de trabajo y realicé los siguientes cambios (puede descargar el archivo de ejemplo y seguirlo):
- Se cambió el nombre de la nueva hoja a "BackEnd".
- En la celda B2, ingresó un símbolo de flecha (para hacer esto, vaya a Insertar y haga clic en la opción "Símbolo").
- Copie y pegue los encabezados del conjunto de datos en la celda A3: C3 de la hoja "Backend".
- Utilice la siguiente función en la celda A4: AC4:
= SI (A3 = $ C $ 1, A3 & "" & $ B $ 1, A3)
- El resto de las celdas se completarán automáticamente con el código VBA cuando haga doble clic en los encabezados para ordenar la columna.
Su hoja de backend se vería como se muestra a continuación:
Ahora puede usar el siguiente código para ordenar los datos haciendo doble clic en los encabezados. Cuando hace doble clic en un encabezado, aparecerá automáticamente la flecha en el texto del encabezado. Tenga en cuenta que también he usado formato condicional para resaltar la celda.
Private Sub Worksheet_BeforeDoubleClick (ByVal Target As Range, Cancel As Boolean) Dim KeyRange As Range Dim ColumnCount As Integer ColumnCount = Range ("DataRange"). Columns.Count Cancelar = False Si Target.Row = 1 y Target.Column <= ColumnCount Entonces Cancelar = True Worksheets ("Backend"). Range ("C1") = Target.Value Set KeyRange = Range (Target.Address) Range ("DataRange"). Ordenar Key1: = KeyRange, Header: = xlYes Worksheets ("BackEnd ") .Range (" A1 ") = Target.Column For i = 1 To ColumnCount Range (" DataRange "). Cells (1, i) .Value = Worksheets (" Backend "). Range (" A4 "). Offset (0, i - 1) .Valor Next i End If End Sub
Tenga en cuenta que este código funciona bien para la forma en que se construyen mis datos y mi libro de trabajo. Si cambia la estructura de los datos, deberá modificar el código en consecuencia.
Descargar el archivo de ejemplo
