sábado, 27 de febrero de 2010

Uso de la Función SUBTOTALES



"Tengo una tabla con mucha información sobre la que aplico habitualmente filtros para realizar cálculos ¿Hay alguna manera de, por ejemplo, sumar sólo las celdas que aparecen una vez filtrada la tabla?"

Para realizar esta labor vamos a ver la función SUBTOTALES. Partimos del siguiente ejemplo:


Como se puede apreciar en la imagen, he aplicado autofiltros a la tabla. Para ello sólo tenemos que situarnos en cualquier celda de dicha tabla, debajo de los rótulos (nombres de campo), e ir al menú Datos/Filtro/Autofiltro. La función SUBTOTALES está especialmente pensada para realizar cálculos en tablas. Veamos cómo:

1. Disponemos la siguiente salida de datos encima de nuestra tabla:


2. En la celda C2 escribimos la fórmula:

=SUBTOTALES(9;C$9:C$28)

La función SUBTOTALES tiene dos argumentos, a saber:

Num_funcion: Es un número del 1 al 11 o del 101 al 111 que indica que función debe ser aplicada a la lista. Las correspondencias de dichos números de función son las que se muestran a continuación:


Ref1: Es la referencia o rango de los que queremos calcular el subtotal (podemos incluir hasta 29 referencias o rangos).

De esta manera, la fórmula =SUBTOTALES(9;C$9:C$28) calculará la SUMA (el 9 es el número de función correspondiente a la suma) del rango C9:C28. Pero con una peculiaridad (ya que en caso contrario podríamos realizar directamente el sumatorio) y es que si procedemos a filtrar la tabla realizará la suma de los valores visibles. Para comprobarlo procedemos a filtrar el campo denominado Variable. Vamos a ver sólo aquellos cargos que tienen como sueldo variable 20.000€:


Como puede apreciar en la imagen, hay seis cargos con este variable y la función SUBTOTALES de la celda C2 nos muestra el sumatorio del sueldo bruto pero precisamente de sólo esos seis cargos (y no del total de la tabla).

3. Copiamos la fórmula de C2 en el rango C3:C6 y procedemos a sustituir el número de función para que realice el cálculo correspondiente:

En C3 =SUBTOTALES(1;C$9:C$28)
En C4 =SUBTOTALES(4;C$9:C$28)
En C5 =SUBTOTALES(5;C$9:C$28)
En C6 =SUBTOTALES(2;C$9:C$28)

4. Como hemos utilizado referencias mixtas en el rango de la función podemos proceder a copiar el rango C2:C6 en D2:D6. El resultado es el que podemos ver en la siguiente imagen:


Puede comprobar que si cambiamos el filtro y le pedimos que, por ejemplo, nos muestre los cargos con un sueldo variable mayor o igual que 15.000€ y menor o igual que 25.000€, la función SUBTOTALES recalculará y mostrará los siguientes resultados:


jueves, 25 de febrero de 2010

Truco en el Copiado Inteligente por Bloques



"Tengo una tabla con datos de ingresos y gastos mensuales. Necesito calcular el margen bruto bimestral. He utilizado el sistema de copiado inteligente por bloques. A continuación he necesitado añadir una nueva columna de reporte con el mismo dato del margen bruto pero en porcentaje. El problema es que como las primeras celdas de la izquierda están en blanco no funciona el copiado inteligente por bloques ¿Hay alguna manera de solucionarlo?"

Hay una manera muy sencilla y rápida. Para entender mejor el problema recomiendo la lectura del post Copiado Inteligente . Partimos del siguiente ejemplo:


Queremos añadir en la columna F el dato del margen bruto en porcentaje. Para ello realizamos los siguientes pasos:
1. En la celda F4 escribimos la fórmula =E4/C4
2. Si quisiéramos aplicar el copiado inteligente por bloques seleccionaríamos el rango F3:F4 y haríamos doble clic en la parte inferior derecha de la selección. Si lo hacemos no ocurre nada ya que la celda E3 está en blanco. Y este es precisamente el problema que me planteáis.
3. SOLUCIÓN: Ocultamos la columna E.
4. Seleccionamos el rango F3:F4 y ahora sí aplicamos el copiado inteligente por bloques como indico en el punto 2:


5. Volvemos a mostrar la columna E y problema resuelto:


miércoles, 10 de febrero de 2010

Campos Calculados en Tablas Dinámicas


Siguiendo las indicaciones de tu DNI "oficialmente" hoy es tu día y eso nadie lo puede discutir...

¡¡ FELICIDADES NESO !!

"La vida es muy dura"

Y ahora a trabajar que hay que levantar el país...


"Cada día recibo una tabla dinámica con los datos clasificados por zonas referentes a las ventas y a sus costes asociados. Sólo recibo la tabla dinámica (no la tabla de origen) y me gustaría realizar algunos cálculos con estos campos, tales como calcular el margen y el porcentaje de los costes sobre los ingresos ¿Hay alguna manera de hacerlo dentro de la propia tabla dinámica para que si luego reordeno los campos siga funcionando correctamente?"

Mi sufrido lector, la solución es bastante sencilla. Para ello vamos a utilizar la herramienta de las Tablas Dinámicas Fórmulas / Campo calculado. Supongamos que partimos del siguiente ejemplo:


1. Nos situamos en cualquier celda de la Tabla. En la barra de herramientas de Tablas Dinámicas abrimos el menú desplegable Tabla dinámica y seleccionamos Fórmulas / Campo calculado:


Aparecerá la siguiente ventana:


2. En el campo Nombre escribimos el título que deseemos dar al campo que vamos a calcular. En nuestro ejemplo utilizaremos Margen.
3. En el campo Fórmula escribiremos la fórmula a calcular. Para realizar esta tarea podemos hacer doble clic encima de los nombres que tenemos en el cuadro Campos (o seleccionar el campo en cuestión y pulsando después el botón Insertar campo). En nuestro caso la primera fórmula será: =Ingresos-C.Directo
4. Pulsamos Aceptar. Fíjese que en la ventana de Lista de campos de la tabla dinámica aparecerá ahora un nuevo campo denominado Margen:


y nuestra Tabla Dinámica mostrará lo siguiente:


Puede proceder a continuación a "reordenar" los campos de la tabla y verá que los cálculos siguen siendo correctos. Por ejemplo:


martes, 9 de febrero de 2010

Eliminar Celdas en Blanco


"Todas las semanas importo un listado de ventas a excel. El resultado que obtengo presenta varias filas en blanco intercaladas ¿Existe alguna manera rápida de borrar dichas filas?"

La solución es muy sencilla. Partimos del siguiente ejemplo:


1. Seleccionamos el rango B3:C19
2. Vamos al menú Edición/Ir a (o directamente la tecla F5) y pulsamos el botón Especial.
3. Seleccionamos la opción Celdas en Blanco y aceptamos.
4. Pulsamos la combinación de teclas Ctrl - (tecla control y el signo menos). Se abrirá la siguiente ventana en la que seleccionamos la opción Toda la fila:


El resultado obtenido será el que se muestra a continuación: