Referencias 1 / 4 (Referencias Absolutas)

Hoy comienzo una serie de cuatro capítulos en los que repasaremos algunos aspectos de como usar las referencias en Microsoft Excel. (Esto es aplicable a todas las hojas de cálculo).

Existen cuatro formas de nombrar una celda o un rango para poder acceder a sus datos desde otra celda.

Son las siguientes:

• Referencia absoluta ($A$1)
• Referencia relativa (A1)
• Referencia mixta ($A1, A$1)
• Referencia por nombre.

Dedicaré un capítulo a cada una.

Empezaremos por las referencias absolutas, ya que su funcionamiento es el más sencillo de entender.

Las referencias absolutas son aquellas que tienen dos dólares en su nombre, fijando de esta forma tanto la fila como la columna de la referencia, lo que implica realmente que la celda queda siempre fija.

Esto es, casi siempre, cierto.

Cuando se explica que es una referencia absoluta, se suele indicar que nunca cambia, y eso es algo ambiguo.

El ejemplo típico muestra que si tengo una celda original por ejemplo B3, y desde otra celda, por ejemplo D3, hago referencia a esta primera, con una referencia absoluta =$B$3, al arrastrar o copiar la celda D3, el contenido de la misma no varía, y siempre es =$B$3

Lo que la gente suele entender que, pase lo que pase, en la celda D3 siempre se mostrará =$B$3,y eso no siempre es así.

Sin embargo, la realidad es que aunque al arrastrar o cortar y pegar la celda original, el valor almacenado se mueve a otra celda. Y las referencias absolutas se actualizan para seguir haciendo referencia al mismo dato en la nueva ubicación.

Esto es si se mueve la celda B3 a B6, todas las celdas que contenían $B$3 pasarán a contener $B$6, eso sí, mostrando el mismo valor.

Os lo muestro en una animación.

Otra razón por la que el valor almacenado cambie, es que se inserten o eliminen filas o columnas.

Y finalmente existe la posibilidad de que eliminando filas, columnas o celdas se eliminé la celda original.

En este caso la referencia se transforma en el error #¡REF!

Os dejo el videotutorial:

Como pueden ver una referencia absoluta, no es siempre absoluta.

Esto hace que en el caso de que sea posible insertar o eliminar celdas, las referencias absolutas, no sean la mejor forma de hacer referencia a una celda desde VBA.

En el último capítulo veremos un método mejor para interactuar con las celdas de Excel desde el lenguaje de programación VBA.

Contar el número de apariciones de un elemento en una lista con CONTAR.SI en Microsoft Excel

Hoy os muestro como con CONTAR.SI, podemos conocer el número de repeticiones exacto de un elemento en una lista.

Usualmente a CONTAR.SI, se le pasa un rango equivalente a una columna entera, del estilo A:A o un rango más reducido mediante referencias absolutas (las de los dólares) del estilo $A$1:$A$16.

Cualquiera de los dos métodos es igualmente válido, ya que Microsoft Excel solo contará hasta el final de la lista. (En el ejemplo que os muestro solo hasta la fila 16).

=CONTAR.SI(A:A;A2)

=CONTAR.SI($A$1:$A$16;A2)

Sin embargo como se ve en el vídeo, la forma A:A permite que las fórmulas tengan en cuenta elementos nuevos a medida que se añadan.

En el ejemplo de hoy, tenemos una columna A con códigos de artículos hasta la línea 16, de los cuales algunos están repetidos.

Si bien se puede aplicar el formato condicional para localizar los duplicados, como explicaba en el post Localizar valores duplicados en Excel sin utilizar fórmulas, con ese método solo sabemos si se repite, pero no cuantas veces aparece.

Con esta fórmula si podremos saber cuantas veces aparece el elemento repetido.

NOTA:

Existe una diferencia entre usar la referencia de la columna completa (A:A) o la referencia de rango $A$1:$A$16.

En el caso de la referencia de rango completo, se pueden continuar añadiendo elementos a la lista, y solo es preciso arrastrar la fórmula hasta el final de la lista, mientras que en el caso del rango, será preciso modificar la fórmula original, ya que de solo arrastrarlo, a partir del final de la lista original se generarán datos incorrectos, ya que el rango será menor del esperado.

Se puede apreciar que en este ejemplo en las celdas C16 y C17, el resultado es cero, cuando el elemento si que está en la lista ampliada, aunque no en la original.

Mucho cuidado con los rangos, es muy importante tener en cuenta el rango real que estamos indicando.

Localizar valores duplicados en Excel sin utilizar fórmulas

Una de las tareas repetitivas que suelo tener que realizar en Microsoft Excel es la localización de valores repetidos en un listado.

Se puede realizar con fórmulas, pero existe un método más sencillo, rápido y visual.

Dentro de las posibilidades de la herramienta Formato condicional existe una opción para aplicar formato a los valores duplicados. En realidad aplica el formato a cualquier valor que aparezca dos o más veces en el listado.

En Inicio seleccionamos Formato Condicional, y ahí la primera ("Resaltar Reglas de Celdas") y luego la última opción ("Duplicar valores ...") 

La utilidad de este truco aumenta si se combina con el filtro por color.

Una vez aplicado el filtro, si se modifica una fila duplicada el color desaparece.

Veamos cómo funcionan en conjunto.

A continuación os dejo la animación para aquello que no puedan ver el vídeo correctamente.

Cargando 3 millones de registros en una hoja de cálculo de Microsoft Excel a partir de un fichero de texto con Power Query

Ampliando el ejemplo de la semana pasada, os muestro un truco que permite que duplicando una consulta de Power Query hacer que distintas secciones de un fichero origen se importen a distintas hojas de un mismo libro de Microsoft Excel, burlando de esta manera la limitación de un millón de líneas por hoja de Excel.

En este caso disponemos de un único fichero de datos con tres millones de registros que deseamos importar completamente a Microsoft Excel.

Es bien sabido que Microsoft Excel tiene un límite de algo más de un millón de filas por hoja.

Aprovechando que podemos crear varias hojas y mezclándolo con el truco que vimos la semana pasada para "Tratar más de un millón de datos en Microsoft Excel", cargaremos tres fragmentos del mismo fichero en otras tantas hojas.

Y para se un poquito más productivos, crearemos una consulta que luego duplicaremos las veces necesarias.

De esta forma podremos indicar un destino distinto para los resultados de cada copia de la consulta.

Si el fichero se modifica, al abrir el Excel y actualizar la hoja, se volverán a importar los nuevos datos sin tener que repetir el proceso.

Os dejo el videotutorial.

Y por si no podéis verlo, la animación:

Copia de seguridad incluyendo la fecha mediante archivo por lotes.

A la hora de hacer una copia de seguridad, es un ahorro de tiempo poder crear una carpeta en la que parte del nombre es la fecha actual, tal como os mostraba en este post https://pildorasofimaticas.blogspot.com/2021/02/crear-carpeta-con-el-nombre-del-dia.html?m=0

Sin embargo si nos limitamos a copiar ficheros en la carpeta del día, cuando tengamos que buscar uno concreto podrá ser un tanto incómodo.

Yo suelo aprovechar el mismo archivo .bat de proceso por lotes para copiar los ficheros que me interesan aprovechando la oportunidad para incluir en el nombre del fichero la fecha actual.

Este proceso se hace en cuatro pasos:

1. Se crea la carpeta con el nombre del día.

2. Se copian los ficheros con su nombre original.

3. Nuevo el directorio de trabajo a la carpeta recién creada.

4. Renombro los ficheros copiados.

Os muestro un ejemplo para un solo fichero.

Si el día es hoy 11/03/2021, creo una carpeta denominada 20210211datos, y a los nombres de los ficheros le añado la fecha en el mismo formato.

Por ejemplo si tuviese "facturas.docx" lo renombraría como "20200311facturas.docx".

Para ello uso un fichero bat, con el siguiente contenido:

@echo off

MKDIR %date:~6,4%%date:~3,2%%date:~0,2%" diario"

copy "C:\carpetaOriginal\facturas.docx" "./"%date:~6,4%%date:~3,2%%date:~0,2%" diario"

cd ./%date:~6,4%%date:~3,2%%date:~0,2%" diario"

ren "facturas.docx" %date:~6,4%%date:~3,2%%date:~0,2%" facturas.docx"

Tratar más de un millón de datos en Microsoft Excel

Siempre tenemos en mente el límite de filas de Microsoft Excel como si fuese el número máximo de datos a tratar, y no siempre es así.

Gracias a Power Query es posible importar datos desde ficheros con muchas más filas.

En el ejemplo se accede a un fichero de texto con 40 Millones de líneas y se importa un millón a una hoja y el siguiente millón a la otra.

Como se puede ver al principio del vídeo esto no es posible desde la importación por defecto de Microsoft Excel, pero si desde Power Query.

No os perdáis este importante complemento de Microsoft Excel.

También está disponible en Power BI.

Borrar todos lo objetos de una hoja de cálculo de Microsoft Excel

En muchas ocasiones al pegar datos de una web en Microsoft Excel, se arrastran componentes de la web que no son deseados, como pueden ser botones, cuadros desplegables o imágenes.

Existe un método poco conocido para seleccionar todos lo objetos de una hoja de cálculo y eliminarlos en un único paso.

En la cinta de opciones, Modificar, Buscar y seleccionar, Ir a..., se puede elegir la opción "Objetos".

También se puede acceder directamente a esta ventana mediante F5.

Al elegir esa opción se seleccionan todos los objetos de la hoja activa, luego solo hace falta pulsar la tecla suprimir y se consigue eliminar en un solo paso todos los objetos incrustados desde la web.

A continuación en el vídeo https://youtu.be/LCURRvIKGNE podéis ver la secuencia completa, en este caso se accede a la ventana directamente con la tecla F5.

Para los que no podéis ver el vídeo correctamente os subo una animación.

Espero que con este método os resulte un poco más sencillo limpiar esos ficheros con restos no deseados de páginas web.