Excel es poderoso. Si lo usa mucho, probablemente ya conozca muchos trucos con fórmulas o autoformato, pero haciendo uso de las funciones de Celdas y Rango en VBA, puede aumentar sus análisis de Excel a un nivel completamente nuevo.
El problema con el uso de las funciones de Células y Rango en VBA es que en los niveles avanzados, la mayoría de las personas tienen dificultades para entender cómo funcionan realmente estas funciones. Usarlos puede ser muy confuso. Así es como puedes usarlos de formas que probablemente nunca imaginaste.
La función de las células
Las funciones de Células y Rango le permiten contar su secuencia de comandos de VBA 4 Errores que puede evitar al programar macros de Excel con VBA 4 Errores que puede evitar al programar macros de Excel con VBA El código simple y las macros son las claves de los superpoderes de Microsoft Excel. Incluso los no programadores pueden agregar fácilmente funcionalidades impresionantes a sus hojas de cálculo con Virtual Basics for Applications (VBA). ¡Solo evita estos errores de programación para principiantes! Lea más exactamente en qué lugar de su hoja de trabajo desea obtener o coloque datos. La principal diferencia entre las dos células es a lo que hacen referencia.
Por lo general, las células hacen referencia a una sola célula a la vez, mientras que Range hace referencia a un grupo de células a la vez. El formato para esta función es Celdas (fila, columna) .
Esto hace referencia a cada celda en la hoja completa. Es el único ejemplo donde la función de Celdas no hace referencia a una sola celda:
Worksheets("Sheet1").Cells Esto hace referencia a la tercera celda de la izquierda, de la fila superior. Celda C1:
Worksheets("Sheet1").Cells(3) El siguiente código hace referencia a la celda D15:
Worksheets("Sheet1").Cells(15, 4) Si lo desea, también puede hacer referencia a la celda D15 con "Celdas (15, " D ")" - puede usar la letra de la columna.
Hay una gran flexibilidad en poder hacer referencia a una celda usando un número para columna y celda, especialmente con scripts que pueden recorrer una gran cantidad de celdas (y realizar cálculos sobre ellas) muy rápidamente. Vamos a llegar a eso con más detalle a continuación.
La función de rango
En muchos sentidos, la función Range es mucho más poderosa que el uso de Cells, porque le permite hacer referencia a una sola celda o a un rango específico de celdas, todo a la vez. No va a querer recorrer una función de rango, porque las referencias para las celdas no son números (a menos que incruste la función de celdas dentro de ella).
El formato para esta función es Rango (Celda n. ° 1, Celda n . ° 2) . Cada celda puede ser designada por un número de letra.
Veamos algunos ejemplos.
Aquí, la función de rango hace referencia a la celda A5:
Worksheets("Sheet1").Range("A5") Aquí, la función de rango hace referencia a todas las celdas entre A1 y E20:
Worksheets("Sheet1").Range("A1:E20") Como se mencionó anteriormente, no tiene que usar asignaciones de celdas de letras de números. En realidad, podría usar dos funciones de Celdas dentro de una función de Rango para identificar un rango en la hoja, como este:
With Worksheets("Sheet1") .Range(.Cells(1, 1), _ .Cells(20, 5)) End With El código anterior hace referencia al mismo rango que la función de rango ("A1: E20"). El valor de usarlo es que le permitiría escribir código que funciona dinámicamente con rangos usando bucles Cómo funcionan los bucles Do-While en programación de computadora Cómo funcionan los bucles "While-While" en computadora Los bucles de programación son uno de los primeros tipos de control que ' Aprenderé en programación. Probablemente conozca los ciclos while y for, pero ¿qué hace un ciclo do-while? Lee mas .
Ahora que comprende cómo formatear las funciones de Celdas y Rango, profundicemos en cómo puede hacer un uso creativo de estas funciones en su código VBA.
Procesamiento de datos con la función de células
La función de Celdas es más útil cuando tiene una fórmula compleja que desea realizar en múltiples rangos de celdas. Estos rangos también pueden existir en varias hojas.
Tomemos un ejemplo simple. Digamos que administras un equipo de ventas de 11 personas y cada mes quieres ver su rendimiento.
Es posible que tenga Sheet1 que rastree su recuento de ventas y su volumen de ventas.
En Sheet2 es donde realiza un seguimiento de su calificación de retroalimentación durante los últimos 30 días de los clientes de su empresa.
Si desea calcular la bonificación en la primera hoja utilizando los valores de las dos hojas, existen múltiples formas de hacerlo. Puede escribir una fórmula en la primera celda que realiza el cálculo utilizando datos en las dos hojas y arrastrar hacia abajo. Eso funcionará
Una alternativa a esto es crear un script de VBA que se active para que se ejecute cada vez que abra la hoja o que se active mediante un botón de comando en la hoja para que pueda controlar cuándo se calcula. Puede utilizar una secuencia de comandos de VBA para obtener todos los datos de ventas de un archivo externo de todos modos.
Entonces, ¿por qué no activar los cálculos para la columna de bonificación en el mismo guión en ese momento?
La función de las células en acción
Si nunca antes ha escrito VBA en Excel, deberá habilitar el elemento del menú Desarrollador. Para hacer esto, vaya a Archivo > Opciones . Haga clic en Personalizar cinta . Finalmente, elija Desarrollador desde el panel izquierdo, agréguelo al panel derecho y asegúrese de que la casilla de verificación esté seleccionada.
Ahora, cuando haces clic en Aceptar y vuelves a la hoja principal, verás la opción del menú Desarrollador.
Puede usar el menú Insertar para insertar un botón de comando o simplemente hacer clic en Ver código para comenzar la codificación.
En este ejemplo, haremos que el script se ejecute cada vez que se abra el libro. Para hacerlo, simplemente haga clic en Ver código en el menú del desarrollador y pegue la siguiente función nueva en la ventana de código.
Private Sub Workbook_Open() End Sub Su ventana de código se verá algo así.
Ahora está listo para escribir el código para manejar el cálculo. Usando un solo bucle, puede pasar por los 11 empleados, y con la función de Células extraer las tres variables necesarias para el cálculo.
Recuerde que la función de Celdas tiene una fila y una columna como parámetros para identificar cada celda individual. Haremos "x" la fila, usaremos un número para solicitar los datos de cada columna. El número de filas es el número de empleados, por lo que será de 1 a 11. El identificador de columna será 2 para el recuento de ventas, 3 para el volumen de ventas y 2 de la hoja 2 para la puntuación de votos.
El cálculo final usa los siguientes porcentajes para sumar hasta el 100 por ciento de la puntuación de bonificación total. Se basa en un conteo de ventas ideal de 50, un volumen de ventas de $ 50, 000 y un puntaje de comentarios de 10.
- (Recuento de ventas / 50) x 0.4
- (Volumen de ventas / 50, 000) x 0.5
- (Puntuación de comentarios / 10) x 0.1
Este enfoque simple les da a los empleados de ventas una bonificación ponderada. Por un recuento de 50, un volumen de $ 50, 000 y un puntaje de 10, obtienen la bonificación máxima completa para el mes. Sin embargo, cualquier cosa en perfecto en cualquier factor reduce la bonificación. Cualquier cosa mejor que ideal aumenta la bonificación.
Ahora veamos cómo se puede extraer toda esa lógica en un script VBA corto y muy simple:
Private Sub Workbook_Open() For x = 2 To 12 Worksheets("Sheet1").Cells(x, 4) = (Worksheets("Sheet1").Cells(x, 2).Value / 50) * 0.4 _ + (Worksheets("Sheet1").Cells(x, 3).Value / 50000) * 0.5 _ + (Worksheets("Sheet2").Cells(x, 2).Value / 10) * 0.1 _ Next x End Sub Así es como se verá el resultado de este script.
Si desea que la columna de bonificación muestre la bonificación en dólares real en lugar del porcentaje, puede multiplicarla por la cantidad máxima de bonificación. Mejor aún, coloque esa cantidad en una celda en otra hoja, y haga referencia a ella en su código. Esto haría que sea más fácil cambiar el valor más tarde sin tener que editar el código.
La belleza de la función de Células es que puedes construir una lógica bastante creativa para extraer datos de muchas celdas en muchas hojas diferentes y realizar algunos cálculos bastante complejos con ellas.
Puede realizar todo tipo de acciones en las celdas mediante la función Celdas, como borrar las celdas, cambiar el formato de las letras y mucho más.
Para explorar todo lo que puede hacer más, consulte la página de Microsoft MSDN para el objeto Cells.
Formateo de celdas con función de rango
Para recorrer varias celdas de a una por vez, la función Celdas es perfecta. Pero si desea aplicar algo a un rango completo de células a la vez, la función de Rango es mucho más eficiente.
Un caso de uso para esto podría ser formatear un rango de celdas usando script, si se cumplen ciertas condiciones.
Por ejemplo, supongamos que si el recuento de todos los volúmenes de ventas de todos los empleados de ventas supera los $ 400, 000 en total, debe resaltar todas las celdas en la columna de bonificación en verde para indicar que el equipo ha ganado un bono extra por equipo.
Echemos un vistazo a cómo puede hacer eso con una declaración IF Cómo usar las declaraciones IF en Microsoft Excel Cómo usar las declaraciones IF en Microsoft Excel Ya sea que sea un experto experimentado o un principiante de una hoja de cálculo, querrá ver esto. guía para las declaraciones IF en Excel. Lee mas .
Private Sub Workbook_Open() If Worksheets("Sheet1").Cells(13, 3).Value>400000 Then ActiveSheet.Range("D2:D12").Interior.ColorIndex = 4 End If End Sub Cuando esto se ejecuta, si la celda supera el objetivo del equipo, todas las celdas del rango se llenarán en verde.
Este es solo un ejemplo simple de las muchas acciones que puede realizar en grupos de celdas que usan la función Rango. Otras cosas que puede hacer incluyen:
- Aplicar un esquema alrededor del grupo
- Verifique la ortografía del texto dentro de un rango de celdas
- Borrar, copiar o cortar celdas
- Buscar en un rango con el método "Buscar"
- Mucho más
Asegúrese de leer la página de Microsoft MSDN para el objeto Range para ver todas las posibilidades.
Lleva a Excel al siguiente nivel
Ahora que comprende las diferencias entre las funciones Cell y Range, es hora de llevar su scripting VBA al siguiente nivel. El artículo de Dann sobre el uso de funciones de contar y agregar en Excel le permitirá construir scripts aún más avanzados que pueden acumular valores en todos sus conjuntos de datos muy rápidamente.
Y si recién está empezando con VBA en Excel, no olvide que tenemos una fantástica guía de introducción a Excel VBA. El Tutorial de programación de VBA de Excel para principiantes. El Tutorial de programación de VBA de Excel para principiantes VBA es una poderosa herramienta de Microsoft Office. Puede usarlo para automatizar tareas con macros, establecer desencadenantes y mucho más. Le presentaremos la programación visual básica de Excel con un proyecto simple. Lea más para usted también.