Crear acumulado de ventas (usando rangos con referencias absolutas y relativas)

 Otro enfoque al problema del cálculo del acumulado en una hoja de cálculo como Microsoft Excel, es calcularlo para cada línea. En el post Crear acumulado de ventas (incrementando acumulado mas valor de fecha actual) se explicaba como calcular el acumulado basándose en el acumulado anterior.

En este caso usaremos la fórmula sumar, pero el truco consiste en indicar un rango que sea más grande en cada nueva línea. Para ello aprovecharemos la posibilidad de usar referencias absolutas y relativas.

Como se desea que la parte superior del rango a sumar sea siempre la misma, se usará una referencia absoluta, en el caso del ejemplo será $B$2, mientras que la parte inferior del rango nos interesa que se vaya incrementando en cada nueva línea, por lo tanto usaremos una referencia relativa (sin poner dólares) B2.

La fórmula para la primera fila de datos quedará =SUMA($B$2:B2), dando como resultado el valor de la celda B2.

Al arrastrar la fórmula, el rango se va actualizando, creciendo a medida que se modifica la referencia relativa. Para la fila 3, la segunda fila de datos, la fórmula quedará =SUMA($B$2:B3).

Crear acumulado de ventas (incrementando acumulado mas valor de fecha actual)

 La forma más sencilla de crear una columna con un valor acumulado en una hoja de cálculo como Microsoft Excel, es tras copiar el valor de la primera fila, crear una fórmula en la segunda fila de datos en la que se suma el valor anterior del acumulado, con el valor de la fecha actual.

De esta forma lo que estaremos es sumando el valor actual al acumulado de la fecha anterior.

Nota: El acumulado de la primera fecha, coincide con el valor de la primera fecha, por lo que para simplificar la fórmula simplemente copiamos ese valor y comenzamos escribiendo la fórmula en la segunda fila de datos.

Detectar números almacenados como texto.

Una de las tareas más habituales que se realizan en Microsoft Excel el cruzar / puntar listados, para comprobar que datos de un listado se muestran en el otro.

Cuando los datos a cruzar son numéricos aparece una problemática adicional.

En ocasiones los datos numéricos son almacenados como si de texto se tratase. 

Es importante tener claro que para una aplicación informática los números y sus valores equivalentes en formato texto son cosas distintas y no comparables. Por ejemplo, el número 5 no es lo mismo que el texto '5'.

Una de las principales diferencias entre un número y una letra, a nivel de Microsoft Excel, es que los números se pueden sumar y las letras no.

Esto nos va a permitir comprobar fácilmente si el dato está almacenado como texto o como valor numérico.

En la imagen muestro dos columnas con el código postal de las Poblaciones.

En la columna A los datos están en formato numérico, y en la columna C están en formato texto.

Podríamos pensar que si tiene ceros a la izquierda está en formato texto, pero eso no es necesariamente cierto, ya que ese efecto se puede conseguir mediante un formato personalizado de la celda.

También podemos ver si el dato es texto por el triangulo verde que aparece en la parte superior de las celdas de la columna C, pero puede llegar a confundirse con el triángulo que muestra los comentarios.

Un truco infalible para saber si los datos son numéricos es seleccionar varias celdas y fijarse en la barra de estado, en la que se muestra un resumen de los datos seleccionados.

Si los datos seleccionados son numéricos, se mostrará tanto el recuento de celdas con datos como la suma de los valore almacenados.

En caso de ser texto solo se mostrará el recuento, ya que las palabras no se pueden sumar.

Filtrar por texto por más de una condición

En Microsoft Excel es posible filtrar por más de una condición de texto.

Para ello, se procederá normalmente aplicando la primera condición.

Usando el ejemplo del post anterior podríamos tener los siguiente datos filtrados por la palabra "real".

Con este filtro activo se mostrarán los siguientes resultados:

Si a estos resultados queremos añadir los que se correspondan al filtro de la palabra "cinta", escribiremos la palabra "cinta" en el cuadro de texto del filtro, y además para que se mantengan los datos ya filtrados, se marcará el checkbox "Agregar la selección actual al filtro".

Esto hará que se apliquen simultáneamente ambas condiciones de filtro, mostrando tanto las poblaciones que contengan el texto "real" como las que contengan el texto "cinta".

Filtrar por texto

En un filtro en Microsoft Excel es posible filtrar por un texto.

Por ejemplo podemos filtrar la población "Cortijo Real" escribiendo en la casilla de texto el nombre de la población.

Sin embargo también aplicará el filtro si escribimos una parte del filtro, de esta manera se mostrarán todos los textos que contengan el conjunto de caracteres escritos, en el ejemplo todas las poblaciones que contienen "corti"

Aunque en el ejemplo anterior pueda parecer que solo se filtran las poblaciones que empiezan por los caracteres indicados, no es así, se muestran todas las poblaciones que contengan ese conjunto de caracteres, como demuestra la búsqueda de las poblaciones que contengan el texto "real"

El resultado de aplicar ese filtro es el mostrado en la siguiente imagen.

Filtrar por más de un valor

Cuando tenemos que seleccionar valores del listado del filtro, si trabajamos con pocos elementos es factible seleccionarlos haciendo scroll.

Sin embargo, esta forma de aplicar filtros puede ser un tanto incómoda, ya que si pinchamos fuera de la ventana de selección del filtro antes de haber pulsado el botón aceptar, no se aplica ningún filtro.

Esto es especialmente molesto cuando tenemos que seleccionar una gran cantidad de valores aislados.

Para evitar que nos pase esto, podemos seleccionar uno o varios elementos y pulsar aceptar.

Esto hará que una parte del filtro se aplique.

Para aplicar el resto de los valores, volvemos a desplegar el filtro y seleccionamos el siguiente o los siguientes valores y pulsamos el botón aceptar.

De esta forma los nuevos elementos se unirán a la selección anterior, y nos evitaremos perder la selección al completo.

En este ejemplo en la segunda ocasión que se desplegó el filtro se añadió el valor 25666.

Pegar datos solo en celdas filtradas (eliminando datos no filtrados)

Como ya hemos visto anteriormente, si se copia un rango de celdas filtradas y se pega en una zona vacía de la hoja de cálculo, los datos se pegan de forma continua, no al lado de los originales, como suele ser de esperar.

Para conseguir ese efecto se puede recurrir a dos técnicas:

1 - Invertir el filtro y eliminar los datos que no deseamos, manteniendo de esa forma los que si deseamos.

2- Mediante programación en VBA (Visual Basic for Applications)

En este post explicaremos el primer método.

Se trata simplemente de aplicar lógica negativa a nuestro problema.

Si tenemos que pegar los datos de edad de una columna de las personas de sexo masculino, lo que haremos será seleccionar las personas de sexo femenino y borrar los datos.

Mostraremos el proceso, paso a paso:

Primero creamos los datos.

Estos serán los datos que deseamos mantener, y que en principio intentaríamos copiar y pegar, pero como hemos visto en el post anterior (Pegar celdas filtradas en filas filtradas), esto provocará que no se peguen al lado de los datos originales, sinó consecutivamente desde la primera fila.

En su lugar, haremos una selección usando la lógica inversa, esto es, mostrando los datos que no deseamos mantener. 

En el caso del ejemplo, los salarios de las personas de sexo Femenino.

Una vez mostrados, seleccionamos los datos que no deseamos.

Y lo eliminamos.

Un vez quitado el filtro, obtenemos el resultado deseado.

Nota:

En caso de tener que seleccionar una cantidad importante de datos, es útil, usar una columna auxiliar para indicar los datos a los que se aplicará el filtro.