Macrosubtotal

El SUBTOTAL es una maravillosa alternativa a la función SUMA, y esta publicación muestra cómo configurar un botón de acceso directo que inserta la función SUBTOTAL. Nuestro nuevo botón de acceso directo será similar al botón Autosuma que inserta la función SUMA porque incluirá automáticamente las celdas de arriba.

Objetivo

Antes de comenzar, dejemos claro lo que intentamos hacer. Si desea insertar la función SUMA y desea que incluya automáticamente los valores adyacentes anteriores, puede usar el botón Autosuma incorporado o el método abreviado de teclado Alt+=. Esto se ilustra a continuación.

Construiremos una macro que inserte una función SUBTOTAL de manera similar, como se muestra a continuación.

También configuraremos un botón y el método abreviado de teclado correspondiente para que sea fácil de ejecutar en cualquier momento. ¿Podemos hacer eso? Podemos y lo haremos. Ahora comencemos.

Descripción general

Los pasos para crear una macro de este tipo son los siguientes.

• Macro de inicio de grabación
• Editar macro
• Botón de configuración en el QAT

Comencemos con el primer paso.

Nota: si desea obtener la macro de Excel sin toda la explicación, simplemente vaya al final de la publicación y descargue el archivo de Excel que contiene la macro de trabajo.

Macro de inicio de grabación

Queremos grabar una macro inicial que configurará solo lo básico. No logrará nuestro objetivo, pero configurará una macro funcional que podremos editar fácilmente.

Primero, cree un nuevo libro en blanco.

Luego, ingrese algunos números en algunas celdas. Luego, seleccione la celda debajo de los números. Su hoja de trabajo debería verse así.

A continuación, queremos decirle a Excel que nos registre ingresando una función SUMA. Para iniciar la grabadora de macros, seleccione el siguiente icono de cinta.

• Ver Macros Grabar macro

Nota: El ícono de Macros está dividido y la mitad superior abre el cuadro de diálogo Macro y la mitad inferior muestra un menú desplegable donde puede seleccionar Grabar macro.

Después de seleccionar Grabar macro, Excel mostrará el cuadro de diálogo Grabar macro. Para el nombre de la macro, puede usar cualquier nombre que desee, pero evite los espacios y los caracteres extravagantes. Usé el nombre InsertSUBTOTAL. Deje vacío el campo Tecla de acceso directo. Luego, desea almacenar la macro en el Libro de macros personal. En este punto, el cuadro de diálogo Grabar macro debería verse así.

Haga clic en el botón Aceptar del cuadro de diálogo y, en este momento, Excel está observando todo lo que hace y registra los comandos del mouse y del teclado. Por lo tanto, ahora mismo, antes de hacer clic en cualquier otra cosa, desea indicarle a Excel que inserte la función SUMA, y puede hacerlo haciendo clic en el botón Fórmulas Autosuma o escribiendo el método abreviado de teclado Alt+=. Luego presione Enter en su teclado para almacenar la fórmula en la celda activa.

Ahora, DETENGA la grabadora. Para detener la grabadora de macros, puede seleccionar el siguiente comando de la cinta.

• Ver Macros Detener grabación

Nota: Detener la grabadora es un paso muy importante. Si olvida detener la grabadora, los siguientes comandos de teclado y mouse de Excel se almacenarán como parte de la macro.

En este punto, debería haber insertado la función SUMA, presionado Enter, detenido la grabadora y su hoja de trabajo debería verse así.

Ahora tenemos la macro inicial básica grabada y es hora de editarla.

Editar macro

En el paso anterior, almacenamos la macro en el Libro de macros personal. Este es un libro oculto (llamado PERSONAL.XLSB) que se abre siempre que se abre Excel. Eso significa que podemos ejecutar la macro en cualquier momento que estemos ejecutando Excel. Para editar una macro almacenada en el Libro de macros personal, debemos mostrarla. Para hacerlo, haga clic en el siguiente comando de la cinta.

• Ver Mostrar

En el cuadro de diálogo Mostrar resultante, confirme que PERSONAL.XLSB esté seleccionado como se muestra a continuación y luego haga clic en Aceptar.

Ahora podemos ver el libro de trabajo PERSONAL.XLSB en Excel y estamos listos para editar la macro.

Para editar la macro, seleccionamos el siguiente icono de Cinta.

• Ver Macros

En el cuadro de diálogo Macros resultante, seleccionamos nuestra nueva macro InsertSUBTOTAL como se muestra a continuación.

Ahora hacemos clic en el botón Editar para abrir el Editor de Visual Basic, que debería parecerse a la captura de pantalla siguiente.

Si no ha utilizado el Editor de Visual Basic antes, funciona de forma muy parecida a un procesador de textos y puede editar el texto, eliminarlo, escribir nuevas líneas, copiar y pegar, etc. Nuestras modificaciones deberían ser bastante fáciles.

En lugar de mostrar capturas de pantalla de la macro a medida que avanzamos con nuestras ediciones, simplemente mostraré el texto de la macro como se muestra a continuación.

Sub Insertar SUBTOTAL() ' 'Insertar Macro SUBTOTAL ' ActiveCell.FormulaR1C1 = "=SUMA(R[-3]C:R[-1]C)" Rango("A5").SeleccionarSubtítulo final

Primero, eliminemos las líneas superfluas. La macro actualizada se muestra a continuación.

Sub Insertar SUBTOTAL() ActiveCell.FormulaR1C1 = "=SUMA(R[-3]C:R[-1]C)"Subtítulo final

Nota: si la macro que grabó es diferente a la anterior, puede reemplazar la macro grabada copiando las tres líneas anteriores y pegándolas en su editor visual basic.

La línea de macro restante es la que inserta la función SUMA. Tiene una notación extraña. En lugar de utilizar una referencia de rango de estilo tradicional A1, como A1:A3, utiliza una notación RC (Fila Columna). La notación R[-3]C le dice a Excel que suba tres celdas y permanezca en la misma columna que la celda activa. La notación R[-1]C le dice a Excel que suba una celda y permanezca en la misma columna que la celda activa. Dado que nuestra fórmula se insertó en A4, podemos ver que A1:A3 tiene el mismo rango que R[-3]C:R[-1]C.

Entonces, tenemos que realizar dos ediciones en esta línea de macro: necesitamos usar SUBTOTAL en lugar de SUMA y necesitamos actualizar el rango RC para que incluya dinámicamente los valores en las celdas encima de la celda activa.

Empecemos por la parte fácil. Necesitamos decirle a Excel que inserte la función SUBTOTAL en lugar de la función SUMA. La función SUBTOTAL tiene un argumento adicional que la función SUMA no tiene, y este argumento adicional le dice a Excel qué tipo de matemáticas aplicar al rango. Queremos que nuestra función SUBTOTAL sume los valores en el rango, por lo tanto, necesitamos usar el código 9, porque 9 es el código para la suma. Por lo tanto, queremos reemplazar “SUM(” por “SUBTOTAL(9”, como se muestra a continuación.

Sub Insertar SUBTOTAL() ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-3]C:R[-1]C)"Subtítulo final

Ahora, necesitamos reemplazar el -3 con una variable que se adaptará a la hoja de trabajo para que la función SUBTOTAL haga referencia al rango poblado inmediatamente encima de la celda activa.

Primero, tomaremos nota de la fila actual. Esto se hace insertando la línea macro “currow = ActiveCell.Row” como se muestra a continuación.

Sub Insertar SUBTOTAL() currow = ActiveCell.Row ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-3]C:R[-1]C)"Subtítulo final

A continuación, debemos tomar nota de la primera fila con datos. Esto se hace insertando la línea “firstrow = ActiveCell.End(xlUp).End(xlUp).Row” como se muestra a continuación.

Sub Insertar SUBTOTAL() currow = ActiveCell.Row primera fila = ActiveCell.End(xlUp).End(xlUp).Row ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-3]C:R[-1]C)"Subtítulo final

A continuación, necesitamos determinar el número de filas entre la primera fila y la fila actual, y esto se hace con una simple resta sumando la línea “diff = currow – firstrow” como se muestra a continuación.

Sub Insertar SUBTOTAL() currow = ActiveCell.Row primera fila = ActiveCell.End(xlUp).End(xlUp).Row diff = currow - primera fila ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-3]C:R[-1]C)"Subtítulo final

A continuación, necesitamos actualizar la fórmula para que en lugar de usar -3 use nuestra diferencia calculada, que se muestra a continuación.

Sub Insertar SUBTOTAL() currow = ActiveCell.Row primera fila = ActiveCell.End(xlUp).End(xlUp).Row diff = currow - primera fila ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-"  diff  "]C:R[-1]C)"Subtítulo final

Ahora, queremos guardar nuestra macro, por lo que guardamos el Libro de macros personal. Podemos volver a ocultarlo si lo deseamos seleccionando el siguiente icono.

• Ver Ocultar

Con nuestra macro configurada, solo necesitamos facilitar su ejecución. Para ello, configuraremos un botón en la barra de herramientas de acceso rápido (QAT).

Botón de configuración en el QAT

Para agregar un botón a la barra de herramientas de acceso rápido (QAT), use el pequeño menú desplegable a la derecha de QAT y seleccione Más comandos. En el cuadro de diálogo Opciones de Excel resultante, desea elegir comandos de macros. Luego, selecciona la macro InsertSUBTOTAL y la agrega al QAT, como se muestra a continuación.

Ahora deberíamos estar bien. Para probarlo, seleccionamos una celda y presionamos el nuevo botón QAT, como se muestra a continuación.

La macro inserta la función SUBTOTAL… ¡lo logramos!

El QAT también agrega automáticamente teclas de acceso directo Alt. Presione la tecla Alt en su teclado y verá aparecer pequeños números para los botones de comando QAT. Puede activar la macro SUBTOTAL haciendo clic en el icono con el mouse o usando el método abreviado de teclado Alt correspondiente.

Con nuestra macro implementada, ahora podemos insertar rápidamente la función SUBTOTAL en nuestros libros de trabajo. Si tiene otras macros divertidas o formas de insertar la función SUBTOTAL, compártalas publicando un comentario a continuación… ¡gracias!

Nota: Cuando cierra Excel, es posible que reciba un cuadro de diálogo que le pregunte si desea guardar los cambios en el Libro de macros personal. Haga clic en Sí; de lo contrario, es posible que su trabajo no se guarde.

Recursos adicionales

• Archivo de Excel de muestra: SUBTOTALMacro.xlsm
• SUBTOTAL artículo
• SUBTOTAL publicaciones