Home-theater-designers
Excel es poderoso. Si lo usa mucho, probablemente ya conozca muchos trucos usando fórmulas o autoformato, pero haciendo uso de Células y Distancia funciones en VBA, puede impulsar sus análisis de Excel a un nivel completamente nuevo.
El problema con el uso de las funciones Cells y Range en VBA es que en los niveles avanzados, la mayoría de las personas tienen dificultades para comprender cómo funcionan realmente estas funciones. Usarlos puede resultar muy confuso. A continuación, le indicamos cómo puede utilizarlos de formas que probablemente nunca imaginó.
La función de las células
Las funciones de celdas y rango le permiten saber su secuencia de comandos de VBA exactamente en qué lugar de la hoja de trabajo desea obtener o colocar los datos. La principal diferencia entre las dos celdas es a lo que hacen referencia.
Células generalmente hacen referencia a una sola celda a la vez, mientras que Distancia hace referencia a un grupo de celdas a la vez. El formato de esta función es Celdas (fila, columna) .
Esto hace referencia a cada una de las celdas de toda la hoja. Es el único ejemplo en el que la función Celdas no hace referencia a una sola celda:
|_+_|
Esto hace referencia a la tercera celda de la izquierda, de la fila superior. Celda C1:
|_+_|El siguiente código hace referencia a la celda D15:
|_+_|
Si lo desea, también puede hacer referencia a la celda D15 con 'Celdas (15,' D ')' --- puede usar la letra de la columna.
Hay mucha flexibilidad para poder hacer referencia a una celda usando un número para columna y celda, especialmente con scripts que pueden bucle a través una gran cantidad de celdas (y realizar cálculos en ellas) muy rápidamente. Llegaremos a eso con más detalle a continuación.
La función de rango
En muchos sentidos, la función Rango es mucho más poderosa que usar Celdas, porque le permite hacer referencia a una sola celda oa un rango específico de celdas, todo a la vez. No querrá 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 de esta función es Rango (celda n. ° 1, celda n. ° 2) . Cada celda se puede designar con una letra-número.
Veamos algunos ejemplos.
cómo recuperar viejos mensajes de Facebook
Aquí, la función de rango hace referencia a la celda A5:
|_+_|Aquí, la función de rango hace referencia a todas las celdas entre A1 a E20:
|_+_|Como se mencionó anteriormente, no tiene que usar asignaciones de celdas de números y letras. De hecho, podría usar dos funciones de Celdas dentro de una función de Rango para identificar un rango en la hoja, como esta:
|_+_|El código anterior hace referencia al mismo rango que hace la función Rango ('A1: E20'). El valor de usarlo es que le permitiría escribir código que funcione dinámicamente con rangos usando bucles.
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 función de celdas
La función Celdas es más útil cuando tiene una fórmula compleja que desea realizar en varios rangos de celdas. Estos rangos también pueden existir en varias hojas.
Tomemos un ejemplo sencillo. Supongamos que administra un equipo de ventas de 11 personas y todos los meses desea ver su desempeño.
Es posible que usted tenga Hoja1 que rastrea su recuento de ventas y su volumen de ventas.
Sobre Hoja2 es donde realiza un seguimiento de la calificación de los comentarios de los clientes de su empresa durante los últimos 30 días.
Si desea calcular la bonificación en la primera hoja utilizando valores de las dos hojas, hay varias formas de hacerlo. Puede escribir una fórmula en la primera celda que realice el cálculo utilizando datos en las dos hojas y arrastrarla hacia abajo. Eso funcionará.
Una alternativa a esto es crear un script de VBA que puede activar para ejecutar cada vez que abre la hoja, o activarlo mediante un botón de comando en la hoja para que pueda controlar cuándo calcula. De todos modos, puede usar un script VBA para extraer todos los datos de ventas de un archivo externo.
Entonces, ¿por qué no activar los cálculos para la columna de bonificación en el mismo script 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 Expediente > Opciones . Haga clic en Personalizar la cinta . Finalmente, elija Desarrollador en el panel izquierdo, Agregar en el panel derecho y asegúrese de que la casilla de verificación esté seleccionada.
Ahora, cuando haces clic OK y vuelva a la hoja principal, verá la opción de menú Desarrollador.
Puedes usar el Insertar menú para insertar un botón de comando, o simplemente haga clic en Ver código para comenzar a codificar.
En este ejemplo, haremos que el script se ejecute cada vez que se abra el libro. Para hacer esto, 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.
|_+_|Su ventana de código se verá así.
Ahora está listo para escribir el código para manejar el cálculo. Con un solo ciclo, puede recorrer los 11 empleados y, con la función Celdas, extraer las tres variables necesarias para el cálculo.
Recuerde que la función Celdas tiene filas y columnas 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 comentarios.
El cálculo final utiliza los siguientes porcentajes para sumar hasta el 100 por ciento del puntaje total de bonificación. Se basa en un recuento de ventas ideal de 50, un volumen de ventas de 50.000 dólares y una puntuación de retroalimentación de 10.
- (Recuento de ventas / 50) x 0,4
- (Volumen de ventas / 50.000) x 0,5
- (Puntuación de votos / 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 una puntuación de 10, obtienen la bonificación máxima completa del mes. Sin embargo, cualquier cosa que no sea perfecta en cualquier factor reduce la bonificación. Cualquier cosa mejor que lo ideal aumenta la bonificación.
Ahora veamos cómo se puede llevar a cabo toda esa lógica en un script VBA muy simple y corto:
|_+_|Así es como se verá la salida de este script.
que puedes hacer con una frambuesa pi
Si desea que la columna de bonificación muestre la bonificación real en dólares en lugar del porcentaje, puede multiplicarla por la cantidad máxima de bonificación. Mejor aún, coloque esa cantidad en una celda de otra hoja y haga referencia a ella en su código. Esto facilitaría cambiar el valor más tarde sin tener que editar su código.
La belleza de la función Cells es que puede crear una lógica bastante creativa para extraer datos de muchas celdas a través de muchas hojas diferentes, y realizar algunas cálculos bastante complejos con ellos.
Puede realizar todo tipo de acciones en las celdas utilizando la función Celdas, como borrar las celdas, cambiar el formato de fuente y mucho más.
Para explorar todo lo que puede hacer, consulte el Página de Microsoft MSDN para el objeto Cells.
Formateo de celdas con función de rango
Para recorrer muchas celdas de una en una, la función de las celdas es perfecta. Pero si desea aplicar algo a un rango completo de celdas de una vez, la función Rango es mucho más eficiente.
Un caso de uso para esto podría ser formatear un rango de celdas usando un script, si se cumplen ciertas condiciones.
Por ejemplo, digamos que si el recuento de todo el volumen de ventas de todos los empleados de ventas supera los $ 400,000 en total, desea resaltar todas las celdas en la columna de bonificación en verde para indicar que el equipo ha ganado una bonificación de equipo adicional.
Echemos un vistazo a cómo puede hacer eso con una declaración IF .
|_+_|Cuando esto se ejecuta, si la celda está por encima del objetivo del equipo, todas las celdas en el rango se rellenarán en verde.
Este es solo un ejemplo simple de las muchas acciones que puede realizar en grupos de celdas usando 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
- Busque en un rango con el método 'Buscar'
- Mucho más
Asegúrate de leer el Página de Microsoft MSDN para que el objeto Range vea todas las posibilidades.
Lleve Excel al siguiente nivel
Ahora que comprende las diferencias entre las funciones Cells y Range, es hora de llevar sus scripts de VBA al siguiente nivel. El artículo de Dann sobre el uso de funciones de conteo y suma en Excel le permitirá crear scripts aún más avanzados que pueden acumular valores en todos sus conjuntos de datos muy rápidamente.
Y si recién está comenzando con VBA en Excel, no olvide que tenemos una fantástica guía introductoria a Excel VBA para ti también.
Cuota Cuota Pío Correo electrónico Canon vs.Nikon: ¿Qué marca de cámara es mejor?Canon y Nikon son los dos nombres más importantes de la industria de las cámaras. Pero, ¿qué marca ofrece la mejor gama de cámaras y lentes?
Leer siguiente Temas relacionados- Productividad
- Programación
- Programación Visual Basic
- Microsoft Excel
Ryan tiene una licenciatura en Ingeniería Eléctrica. Ha trabajado 13 años en ingeniería de automatización, 5 años en TI y ahora es ingeniero de aplicaciones. Antiguo editor en jefe de MakeUseOf, ha hablado en conferencias nacionales sobre visualización de datos y ha aparecido en la televisión y la radio nacionales.
Más de Ryan DubeSuscríbete a nuestro boletín
¡Únase a nuestro boletín de noticias para obtener consejos técnicos, reseñas, libros electrónicos gratuitos y ofertas exclusivas!
Haga clic aquí para suscribirse