Clasificación de datos en Excel VBA
Excel tiene un medio excelente para ordenar un rango de datos tabulares usando la cinta en el front-end de Excel, y en algún momento, probablemente querrá usar esta funcionalidad dentro de su código VBA. Afortunadamente, esto es muy fácil de hacer.
El cuadro de diálogo de la interfaz se encuentra al hacer clic en el icono "Ordenar" en el grupo "Ordenar y filtrar" de la pestaña "Datos" en la cinta de Excel. Primero debe seleccionar un rango de datos tabulares.
También puede usar Alt-A-S-S para mostrar el cuadro de diálogo para una clasificación personalizada.
El método de clasificación se ha mejorado mucho en versiones posteriores de Excel. La clasificación solía estar restringida a tres niveles, pero ahora puede ingresar tantos niveles como necesite, y esto también se aplica dentro de VBA.
Puede incorporar todas las funciones de clasificación que se ofrecen en el cuadro de diálogo Ordenar de Excel en su código VBA. La función de clasificación en Excel es rápida y más rápida que cualquier otra cosa que pueda escribir usted mismo en VBA, así que aproveche la funcionalidad.
Tenga en cuenta que cuando realiza una ordenación en VBA, los parámetros de ordenación siguen siendo los mismos en el cuadro de diálogo de ordenación de front-end. También se guardan cuando se guarda el libro.
Si un usuario selecciona el mismo rango de datos tabulares y hace clic en el icono Ordenar, verá todos sus parámetros que han sido ingresados por su código VBA. Si quieren hacer una especie de diseño propio, primero tendrán que eliminar todos los niveles de clasificación, lo que les resultará muy molesto.
Además, si no cambia los parámetros dentro de su código y confía en los valores predeterminados, puede encontrar que el usuario ha realizado cambios que se reflejarán en su clasificación de VBA y pueden dar resultados inesperados, que pueden ser muy difíciles de depurar. .
Afortunadamente, hay un método Clear en VBA para restablecer todos los parámetros de ordenación para que el usuario vea un cuadro de diálogo de ordenación limpia
| 1 | Hojas de trabajo ("Hoja1"). Sort.SortFields.Clear |
Es una buena práctica borrar los parámetros de clasificación en VBA antes y después de que se haya completado la clasificación.
Uso práctico del método de clasificación en VBA
Cuando se importan datos tabulares a Excel, a menudo se hace en un orden muy aleatorio. Podría importarse de un archivo CSV (valores separados por comas) o podría provenir de un enlace a una base de datos o página web. No puede confiar en que esté en un orden establecido de una importación a otra.
Si presenta estos datos a un usuario dentro de su hoja de trabajo, es posible que al usuario le resulte difícil ver y comprender una gran cantidad de datos que, en términos de orden, están por todas partes. Es posible que quieran agrupar los datos o cortar y pegar ciertas secciones en otra aplicación.
También pueden querer ver, por ejemplo, al empleado mejor pagado o al empleado con el servicio más antiguo.
Usando el método de clasificación en VBA, puede ofrecer opciones para permitir una clasificación fácil para el usuario.
Datos de muestra para demostrar la clasificación de Excel con VBA
Primero necesitamos algunos datos de muestra para ingresar en una hoja de trabajo, para que el código pueda demostrar todas las facilidades disponibles dentro de VBA.
Copie estos datos en una hoja de trabajo (llamada "Hoja1") exactamente como se muestra.
Tenga en cuenta que se han usado diferentes colores de fondo de celda y colores de fuente, ya que estos también se pueden usar como parámetros de clasificación. La clasificación mediante colores de fuente y celda se demostrará más adelante en el artículo. También tenga en cuenta que en la celda E3, el nombre del departamento está en minúsculas.
No necesita el interior de la celda y los colores de fuente si no desea utilizar los ejemplos de clasificación por celda y color de fuente.
Grabación de una macro para una ordenación de VBA
El código de VBA para la clasificación puede volverse bastante complicado y, a veces, puede ser una buena idea hacer la clasificación en la interfaz de Excel y grabar una macro para mostrarle cómo funciona el código.
Desafortunadamente, la función de grabación puede generar una gran cantidad de código porque establece prácticamente todos los parámetros disponibles, aunque los valores predeterminados para muchos parámetros son aceptables para su operación de clasificación.
Sin embargo, le da una muy buena idea de lo que implica escribir el código de clasificación de VBA, y una ventaja es que el código grabado siempre funcionará para usted. Es posible que sea necesario probar y depurar su propio código para que funcione correctamente.
Recuerde que para una operación realizada en VBA, no existe la función de deshacer, por lo que es una buena idea hacer una copia de los datos tabulares en otra hoja de trabajo antes de comenzar a escribir su código de clasificación.
Como ejemplo, si hiciera una ordenación simple en los datos de muestra anteriores, ordenando por Empleado, la grabación generaría el siguiente código:
| 123456789101112131415161718 | Sub Macro1 ()Rango ("A1: E6"). SeleccionarActiveWorkbook.Worksheets ("Hoja1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add2 Key: = Rango ("A2: A6"), _SortOn: = xlSortOnValues, Orden: = xlAscending, DataOption: = xlSortNormalCon ActiveWorkbook.Worksheets ("Hoja1"). Ordenar.SetRange Range ("A1: E6").Encabezado = xl Sí.MatchCase = Falso.Orientación = xlTopToBottom.SortMethod = xlPinYin.SolicitarTerminar conEnd Sub |
Se trata de una gran cantidad de código y una gran parte es innecesaria debido a que se utilizan parámetros predeterminados. Sin embargo, si tiene presión de tiempo para completar un proyecto y necesita un código rápidamente que funcione, puede pegarlo fácilmente en su propio código VBA.
Sin embargo, si desea que su código sea comprensible y más elegante, existen otras opciones disponibles.
Código VBA para hacer una clasificación de un solo nivel
Si desea ordenar el código de muestra en función de Empleado solo como antes al grabar una macro, el código es muy simple:
| 1234567 | Sub SingleLevelSort ()Hojas de trabajo ("Hoja1"). Sort.SortFields.ClearRango ("A1: E6"). Clave de clasificación 1: = Rango ("A1"), Encabezado: = xlSíEnd Sub |
Esto es mucho más fácil de entender que el código grabado porque acepta los valores predeterminados, por ejemplo, ordenar en forma ascendente, por lo que no es necesario establecer los parámetros en los valores predeterminados. Esto supone que ha utilizado una declaración "clara" de antemano.
El método "Borrar" se utiliza inicialmente para garantizar que todos los parámetros de clasificación para esa hoja de trabajo se restablezcan a los valores predeterminados. Un usuario puede haber configurado previamente los parámetros a diferentes valores, o una clasificación anterior en VBA puede haberlos cambiado. Es importante comenzar desde una posición predeterminada al ordenar, de lo contrario, podría terminar fácilmente con resultados incorrectos.
El método Clear no restablece el parámetro Encabezado, y es recomendable incluirlo en su código; de lo contrario, Excel puede intentar adivinar si una fila de encabezado está presente o no.
Ejecute este código con los datos de muestra y su hoja de trabajo se verá así:
Código VBA para hacer una clasificación de varios niveles
Puede agregar tantos niveles de clasificación como sea necesario dentro de su código. Suponga que desea ordenar primero por departamento y luego por fecha de inicio, pero en orden ascendente para el departamento y en orden descendente para la fecha de inicio:
| 12345678 | Sub MultiLevelSort ()Hojas de trabajo ("Hoja1"). Sort.SortFields.ClearRango ("A1: E6"). Ordenar clave1: = Rango ("E1"), Clave2: = Rango ("C1"), Encabezado: = xlYes, _Order1: = xlAscending, Order2: = xlDescendingEnd Sub |
Tenga en cuenta que ahora hay dos claves en la instrucción de ordenación (Key1 y Key2). La clave1 (columna E del departamento) se ordena en primer lugar y luego la clave2 (columna C de fecha de inicio) se ordena según la primera clasificación.
También hay dos parámetros de orden. Order1 se asocia con Key1 (Departamento) y Order2 se asocia con Key2 (Fecha de inicio). Es importante asegurarse de que las claves y los pedidos se mantengan sincronizados.
Ejecute este código con los datos de muestra y su hoja de trabajo se verá así:
La columna Departamento (E) está en orden ascendente y la columna Fecha de inicio (C) está en orden descendente.
El efecto de este tipo es más notable cuando se mira a Jane Halfacre (fila 3) y John Sutherland (fila 4). Ambos están en Finanzas, pero Jane Halfacre comenzó antes que John Sutherland y las fechas se muestran en orden descendente.
Si el rango de datos tabulares puede tener cualquier longitud, puede utilizar el objeto UsedRange para definir el rango de clasificación. Esto solo funcionará si solo hay datos tabulares en la hoja de trabajo, ya que cualquier valor fuera de los datos dará resultados incorrectos para el número de filas y columnas.
| 1234567 | Sub MultiLevelSort ()Hojas de trabajo ("Hoja1"). Sort.SortFields.ClearHojas de trabajo ("Sheet1"). UsedRange.Sort Key1: = Range ("E1"), Key2: = Range ("C1"), Header: = xlYes, _Order1: = xlAscending, Order2: = xlDescendingEnd Sub |
Esto evita el problema si usa el método "End (xlDown)" para definir el rango de clasificación. Si hay una celda en blanco en el medio de los datos, no se incluirá nada después de la celda en blanco, mientras que UsedRange baja a la última celda activa en la hoja de trabajo.
Ordenar por color de celda
Desde Excel 2007, ahora es posible ordenar por el color de fondo de una celda, lo que proporciona una enorme flexibilidad al diseñar su código de clasificación en VBA.
| 123456789101112 | Sub SingleLevelSortByCellColor ()Hojas de trabajo ("Hoja1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add2 Key: = Rango ("A2: A6"), _SortOn: = xlSortOnCellColor, Order: = xlAscending, DataOption: = xlSortNormalCon ActiveWorkbook.Worksheets ("Hoja1"). Ordenar.SetRange Range ("A1: E6").SolicitarTerminar conEnd Sub |
Este código ordenará el rango de datos de muestra (A2: A6) según el color de fondo de la celda. Tenga en cuenta que ahora hay un parámetro adicional llamado "SortOn" que tiene el valor de "xlSortOnCellColor".
Tenga en cuenta que el parámetro "SortOn" solo puede ser utilizado por un objeto de hoja de trabajo y no por un objeto de rango.
Debido a esto, el código es más complicado que para una ordenación que usa valores de celda.
Este código usa un valor clave para la clasificación que cubre todo el rango de datos, pero puede especificar columnas individuales como la clave para la clasificación del color de fondo y usar múltiples niveles como se mostró anteriormente.
Después de ejecutar este código, su hoja de trabajo ahora se verá así:
Ordenar por color de fuente
La función de clasificación en Excel VBA ofrece aún más flexibilidad, ya que puede ordenar por colores de fuente:
| 1234567891011121314 | Sub SingleLevelSortByFontColor ()Hojas de trabajo ("Hoja1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add (Range ("A2: A6"), _xlSortOnFontColor, xlAscending, xlSortNormal) .SortOnValue.Color = RGB (0, 0, 0)Con ActiveWorkbook.Worksheets ("Hoja1"). Ordenar.SetRange Range ("A1: E6").Encabezado = xl Sí.Orientación = xlTopToBottom.SolicitarTerminar conEnd Sub |
El código para ordenar por color de fuente es mucho más complicado que para el color de fondo de la celda. El parámetro "SortOn" ahora contiene el valor de "xlSortOnFontColor".
Tenga en cuenta que debe especificar la orientación como "xlTopToBottom" y debe especificar un color para ordenar. Esto se especifica en términos RGB (rojo, verde, negro) con valores que van de 0 a 255.
Después de ejecutar este código con los datos de muestra, su hoja de trabajo ahora se verá así:
Ordenar usando colores en VBA es mucho más complicado que una ordenación de varios niveles, pero si su código de ordenación no funciona (lo que puede suceder si falta un parámetro o no ha ingresado el código correctamente), siempre puede recurrir a la grabación una macro e integrando el código grabado en su VBA.
Uso de otros parámetros en la clasificación de VBA
Hay una serie de parámetros opcionales que puede usar en su código VBA para personalizar su clasificación.
SortOn
SortOn elige si la clasificación utilizará valores de celda, colores de fondo de celda o colores de fuente de celda. La configuración predeterminada es Valores de celda.
| 1 | SortOn = xlSortOnValues |
Pedido
Orden elige si la clasificación se hará en orden ascendente o descendente. El valor predeterminado es ascendente.
| 1 | Orden = xl Ascendente |
DataOption
DataOption elige cómo se ordenan el texto y los números. El parámetro xlSortNormal ordena los datos numéricos y de texto por separado. El parámetro xlSortTextAsNumbers trata el texto como datos numéricos para la ordenación. El valor predeterminado es xlSortNormal.
| 1 | DataOption = xlSortNormal |
Encabezamiento
Encabezado elige si el rango de datos tabulares tiene una fila de encabezado o no. Si hay una fila de encabezado, no desea que se incluya en la clasificación.
Los valores de los parámetros son xlYes, xlNo y xlYesNoGuess. xlYesNoGuess deja que Excel determine si hay una fila de encabezado, lo que fácilmente podría conducir a resultados inconsistentes. No se recomienda el uso de este valor.
El valor predeterminado es XNo (sin fila de encabezado dentro de los datos). Con datos importados, generalmente hay una fila de encabezado, así que asegúrese de establecer este parámetro en xlYes.
| 1 | Encabezado = xl Sí |
MatchCase
Este parámetro determina si la clasificación distingue entre mayúsculas y minúsculas o no. Los valores de las opciones son Verdadero o Falso. Si el valor es Falso, los valores en minúsculas se consideran iguales que los valores en mayúsculas. Si el valor es Verdadero, la clasificación mostrará la diferencia entre los valores en mayúsculas y minúsculas dentro de la clasificación. El valor predeterminado es falso.
| 1 | MatchCase = Falso |
Orientación
Este parámetro determina si la clasificación se llevará a cabo hacia abajo en las filas o en todas las columnas. El valor predeterminado es xlTopToBottom (ordenar por filas). Puede utilizar xlLeftToRight si desea ordenar horizontalmente. Valores como xlRows y xlColumns no funcionan para este parámetro.
| 1 | Orientación = xlTopToBottom |
SortMethod
Este parámetro solo se utiliza para clasificar los idiomas chinos. Tiene dos valores, xlPinYin y xlStroke. xlPinYin es el valor predeterminado.
xlPinYin ordena utilizando el orden fonético chino para los caracteres. xlStroke ordena por la cantidad de trazos en cada carácter.
Si graba una macro de ordenación, este parámetro siempre se incluirá en el código y es posible que se haya preguntado qué significaba. Sin embargo, a menos que se trate de datos en chino, es de poca utilidad.
| 1 | SortMethod = xlPinYin |
Uso de un evento de doble clic para ordenar datos tabulares
En toda la funcionalidad que Microsoft incluyó en los métodos de clasificación para VBA, no incluyó un medio simple para hacer doble clic en el encabezado de una columna y ordenar la totalidad de los datos tabulares en función de esa columna en particular.
Esta es una característica realmente útil y es fácil escribir el código para hacerlo.
| 12345678910111213141516171819202122232425262728293031323334 | Private Sub Worksheet_BeforeDoubleClick (ByVal Target As Range, Cancel As Boolean)'Se asume que los datos comienzan en la celda A1'Cree tres variables para capturar la columna de destino seleccionada y la columna y fila máximas de _'los datos tabularesDim Col como entero, RCol tan largo, RRow tan largo'Verifique que el usuario haya hecho doble clic en la fila del encabezado; de lo contrario, salga del subSi Target.Row 1, salga de Sub'Capture el máximo de filas en el rango de datos tabulares utilizando el objeto' UsedRange 'RCol = ActiveSheet.UsedRange.Columns.Count'Capture el máximo de columnas en el rango de datos tabulares utilizando el objeto' UsedRange 'RRow = ActiveSheet.UsedRange.Rows.Count'Verifique que el usuario no haya hecho doble clic en una columna fuera del rango de datos tabularesSi Target.Column> RCol, salga de Sub'Capture la columna en la que el usuario ha hecho doble clicCol = Target.Column'Borrar los parámetros de clasificación anterioresActiveSheet.Sort.SortFields.Clear'Ordene el rango tabular según lo definido por filas y columnas máximas del objeto' UsedRange ''Ordene los datos tabulares utilizando la columna en la que el usuario ha hecho doble clic como clave de clasificaciónActiveSheet.Range (Cells (1, 1), Cells (RCol, RRow)). Sort Key1: = Cells (1, Col), Header: = xlYes'Seleccione la celda A1: esto es para asegurarse de que el usuario no se quede en modo de edición después de que la clasificación sea _'completadoActiveSheet.Range ("A1"). SeleccioneEnd Sub |
Este código debe colocarse en el evento de doble clic en la hoja que contiene los datos tabulares. Para ello, haga clic en el nombre de la hoja de trabajo en la ventana del Explorador de proyectos (esquina superior izquierda de la pantalla VBE) y luego seleccione "Hoja de trabajo" en el primer menú desplegable de la ventana de código. Seleccione "Antes de DoubleClick" en el segundo menú desplegable y, a continuación, puede introducir su código.
Tenga en cuenta que no hay nombres, rangos o referencias de celda codificados en este código, excepto para mover el cursor a la celda A1 al final del código. El código está diseñado para obtener toda la información requerida de las coordenadas de la celda en las que el usuario ha hecho doble clic y el tamaño del rango de datos tabulares.
No importa qué tan grande sea el rango de datos tabulares. El código seguirá recogiendo toda la información requerida y se puede utilizar en los datos almacenados en cualquier lugar dentro de su libro de trabajo sin tener que codificar valores.
La única suposición que se hace es que hay una fila de encabezado en los datos tabulares y que el rango de datos comienza en la celda A1, pero la posición inicial del rango de datos se puede cambiar fácilmente dentro del código.
¡Cualquier usuario quedará impresionado con esta nueva función de clasificación!
Ampliación de la función de clasificación mediante VBA
Microsoft ha permitido una tremenda flexibilidad en la clasificación utilizando una amplia gama de parámetros. Sin embargo, dentro de VBA, puede llevar esto más lejos.
Suponga que desea ordenar los valores con una fuente en negrita en la parte superior de sus datos. No hay forma de hacer esto en Excel, pero puede escribir el código VBA para hacerlo:
| 123456789101112131415161718192021222324252627282930313233343536373839404142 | Sub SortByBold ()'Cree variables para contener el número de filas y columnas para los datos tabularesAtenuar RRow tan largo, RCol tan largo, N tan largo'Desactive la actualización de la pantalla para que el usuario no pueda ver lo que está sucediendo; es posible que vea _'valores que se alteran y se preguntan por quéApplication.ScreenUpdating = Falso'Capture el número de columnas en el rango de datos tabularesRCol = ActiveSheet.UsedRange.Columns.Count'Capture el número de filas dentro del rango de datos tabularesRRow = ActiveSheet.UsedRange.Rows.Count'Itera a través de todas las filas en el rango de datos tabulares ignorando la fila del encabezadoPara N = 2 a Row'Si una celda tiene una fuente en negrita, coloque un valor 0 a la izquierda contra el valor de la celdaSi ActiveSheet.Cells (N, 1) .Font.Bold = True EntoncesActiveSheet.Cells (N, 1) .Value = "0" & ActiveSheet.Cells (N, 1) .ValueTerminara siSiguiente N'Borrar cualquier parámetro de clasificación anteriorActiveSheet.Sort.SortFields.Clear'Ordene el rango de datos tabulares. Todos los valores con un valor 0 a la izquierda se moverán a la parte superiorActiveSheet.Range (Cells (1, 1), Cells (RCol, RRow)). Sort Key1: = Cells (1, 1), Header: = xlYes'Itera a través de todas las filas en el rango de datos tabulares ignorando la fila del encabezadoPara N = 2 a Row'Si una celda tiene una fuente en negrita, elimine el valor 0 inicial del valor de la celda a _'restaurar los valores originalesSi ActiveSheet.Cells (N, 1) .Font.Bold = True EntoncesActiveSheet.Cells (N, 1) .Value = Mid (ActiveSheet.Cells (N, 1) .Value, 2)Terminara siSiguiente N'Activar la actualización de la pantalla de nuevoApplication.ScreenUpdating = TrueEnd Sub |
El código calcula el tamaño del rango de datos tabulares utilizando el objeto "UsedRange" y luego recorre todas las filas dentro de él. Cuando se encuentra una fuente en negrita, se coloca un cero a la izquierda delante del valor de la celda.
Entonces tiene lugar una especie. Como la clasificación está en orden ascendente, todo lo que tenga un cero al frente irá al principio de la lista.
Luego, el código recorre todas las filas y elimina los ceros iniciales, restaurando los datos a sus valores originales.
Este código se clasifica usando fuentes en negrita como criterio, pero podría usar fácilmente otras características de celda de la misma manera, por ejemplo, fuente en cursiva, tamaño en puntos del texto, fuente de subrayado, nombre de fuente, etc.
