Añadir un selector desplegable de valores en una celda de la hoja de cálculo de Google Drive.

Si no hace mucho veíamos cómo obtener una lista de valores únicos en la hoja de cálculo de Google Drive hoy vamos a ver cómo crear un selector de valores desplegable en una celda.

Lo primero es ir al menú de la cabecera, pulsar sobre Datos y en el menú que desplegará elegir Validación de Datos. Eso nos abrirá una ventana como esta:

En criterios tenemos varias opciones: fecha, números… pero en este caso nos interesan dos: Lista a partir de un intervalo y Lista de Elementos, ya que ambas nos darán como resultado el selector desplegable de valores que queremos.

La primera opción nos permitirá elegir los valores contenidos en un intervalo de celdas, por ejemplo A1:A5, y nos mostrará como opciones en el desplegable los valores únicos (eliminará las duplicaciones) contenidos en ese intervalo. La segunda opcion nos permite definir manualmente una lista de valores, que irán separados por comas. Es importante que marquéis la opción Mostrar la lista desplegable de la Celda para que de esa forma muestre el desplegable, si no lo que hará será dejaros escribir y mostrará un aviso o no permitirá insertar el valor, según lo que elijamos en la opción Si los datos no son válidos. Si queréis que solo puedan elegir valores del desplegable y no poder meterlos manualmente en esa opción debéis marcar el valor Rechazar Entrada.

Hoja de Cálculo de Google Drive: obtener una lista de valores únicos de una columna

Vamos con un poco de ofimática, en este caso con la hoja de cálculo Sheets que incluye la suite ofimática gratuita de Google Drive. Imagina que quisiera obtener una lista de valores únicos de una columna ¿cómo lo harías?

Básicamente nos situaríamos en la celda en la que queremos que empiece nuestra lista y usaríamos la función UNIQUE() pasándole el intervalo de celdas en el que queremos buscar nuestros valores únicos. En la imagen podéis ver el ejemplo: en la columna A hay 12 elementos, algunos repetidos, en la columna C están solo los valores únicos de esa lista.

Pues para conseguir este resultado lo que hice fue ponerme en la celda C1 y usar la fórmula UNIQUE(A1:A12)

Excel: Fórmula para eliminar todos los caracteres no numéricos de una celda

Imagina que quieres eliminar todos los caracteres no numéricos de una celda en Excel. Con esta fórmula, que hace uso de la función TEXTJOIN() disponible en Office365 y Office ’19, podrías lograrlo. La fórmula genérica sería la siguiente

{=TEXTJOIN("",TRUE,IFERROR(MID(A1,ROW(INDIRECT("1:100")),1)+0,""))}

Vamos a explicarlo:

  • La variable A1 de la fórmula hace referencia al a cela que queréis limpiar, así que cuando la apliquéis tenéis que modificarla por la que corresponda en vuestra hoja de cálculo.
  • Esta fórmula convertirá en un array de caracteres los datos de vuestra celda (eso lo hará la función MID(), transformará la cadena en un array)
  • Los recorrerá (es lo que hace la función TEXTJOIN(), reconvertir el array de números en una cadena)
  • Quedándose solo con los números (eso es lo que hace la operación +0 de la fórmula, fuerza a Excel a convertir los valores de carácter a valores numéricos para esa operación, en caso de que sea un valor no numérico la función IFERROR() lo cambiará por un espacio vacío y en caso de que sea un valor numérico permanece con el mismo valor)

LibreOffice Calc: Proteger documentos

La suite ofimática LibreOffice, en su programa de hoja de cálculo Calc, nos ofrece la opción de proteger nuestros ficheros a varios niveles: a nivel de fihcero, a nivel de hoja y a nivel de libro. Veamos cómo hacerlo ahora:

A nivel de fichero: Pulsamos Archivo->Guardar Como y allí, a la hora de guardar podemos marcar la casilla Guardar con Contraseña. También tenemos la opción de utilizar una clave pública para cifrado marcado Cifrar con clave GPG.

A nivel de Hoja: Nos vamos a Herramientas->Proteger Hoja… y nos abrirá un menú donde introducir una contraseña y donde asignar qué podrán hacer los usuarios sin ella.

A nivel de libro: Nos vamos a Herramientas->Proteger Estructura de Libro… y allí definimos una contraseña que será obligatoria para poder modificar la estructura del mismo.

Plantilla para control horario semanal en LibreOffice Calc

Mientras la prensa apaniaguada sigue creando una polémica totalmente prefabricada y ficticia por la obligatoriedad de registrar las horas trabajadas en los centros laborales yo os ofrezco una solución rápida y barata. Existen múltiples soluciones informatizadas, como sistemas basados en apps en la nube, sistemas RFID o sistemas de fichaje con reconocimiento de huella dactilar.

Pero si no quieres invertir en ninguno de estos sistemas puedes llevar el control a través de una hoja de cálculo. La siguiente es una plantilla que puedes descargar para hacerlo con la suite ofimática libre y gratuita LibreOffice Calc. Bastaría con ir actualizando semana a semana. Esta diseñada para jornada partida, para jornada intensiva bastaría con no usar las columnas de la tarde o eliminarlas. Calcula las horas trabajadas de mañana, de tarde, el total diario y finalmente suma el total semanal.

Desde este enlace puedes descargar la plantilla para LibreOffice Calc, formato ODS, comprimida en un fichero zip.

Comprobar de forma fácil si un número es par en una hoja de cálculo de Google Drive

En su día habíamos visto varias soluciones para comprobar con PHP si un número es par o impar. Esto también podemos necesitarlo cuando trabajamos con una hoja de cálculo de Google Drive, por suerte ya hay una función específica para ello:

La funció ES.PAR() recibe un número como parámetro y nos devuelve un valor booleno: verdadero si es par y falso si no lo es. Así de simple. Su funcionamiento sería como en el siguiente ejemplo:

=ES.PAR(22)

Importar datos de una hoja de cálculo de Google Drive a otra con IMPORTRANGE()

En los últimos tiempos casi todos los artículos técnicos están siendo de ofimática y, más en concreto, de Google Drive. Y esta nueva entrega sigue por ahí: ¿podemos importar datos de una hoja de cálculo a otra en Google Drive? Pues sí, podemos.

La función IMPORTRANGE() se encarga de ello, recibiendo dos parámetros (dos cadenas de texto, por tanto se les envían siempre entre comillas dobles): el primer parámetro será la URL de la hoja de cálculo donde está el origen de los datos, el segundo será una cadena donde especificamos la hoja y el rango de celdas a recoger. La sintaxis es tal que así:

=IMPORTRANGE("UrlDeLaHojaDeCálculo";"NombreDeLaHoja!CeldaInicio:CeldaFin")

Os voy a dejar un ejemplo más o menos real. Tengo una hoja de cálculo donde voy guardando los discos que añadiré a la próxima revisión de los 1100 discos esenciales (cuando crezca hasta 1250, dentro de unos meses). Ahora voy a importar de esa tabla las dos primeras columnas de las 20 primeras filas. Lo haría así:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1QqGx-f4jC3ZHi1jkSa9eY9_vY-OiClXJ1nKTDwleVpfh3I/";"Hoja 1!A1:B20")

Otro dato importante: por seguridad la primera vez que importemos los datos se nos solicitará conceder permisos explícitamente sobre la hoja de origen. Una vez concedidos, la hoja de cálculo de destino podrá extraer datos de cualquier parte de la hoja de origen hasta que el permiso se revoque también de forma explícita.

Importar datos desde una web a una hoja de cálculo de Google Drive

Seguimos con el tema de la ofimática y con las hojas de cálculo de Google Drive, que parece ser un material que os interesa. Hoy vamos a ver una función muy útil aunque un poco liosa: Con IMPORTHTML() podemos importar a la hoja tablas o listados de una web.

La función recibe tres parámetros: el primero, como es lógico, es la URL de la página desde la que queremos importar los datos, irá entre comillas dobles por ser una cadena de texto. El segundo, el formato que queremos importar (puede ser «table» para tablas o «list» para listas». Finalmente el tercero es un índice para indicar qué tabla quieres traer si hay varias dentro de la web, algo que puede ser lioso ya que a veces vamos «a ciegas» y puede que necesitemos varios ensayos de prueba y error hasta traer el dato que queremos. Por tanto la sintaxis sería tal que así:

=IMPORTHTML (dirección url; tipo de listado; índice numérico)

Vamos con un caso práctico, seguro que recuerdas que en su día vimos como exportar datos desde la web Basketball-Reference a formato Excel. Ahora vamos a hacer lo mismo pero usando directamente esta fórmula en una hoja de Google Drive:

=IMPORTHTML("https://www.basketball-reference.com/players/s/smithjr01.html";"table";1)

La línea de arriba importaría la primera tabla de la ficha de JR Smith, es decir, sus promedios en temporada regular. El resultado sería tal que así:

La función SPARKLINE() en la hoja de cálculo de Google Drive: insertar gráficas en una celda

Si bien la hoja de cálculo de Google Drive, al igual que Excel o que LibreOffice Calc, nos aporta una serie de cómodas herramientas para la creación de gráficos también disponemos de una función que nos permite crear un gráfico dentro de una celda. Dicha fución se llama SPARKLINE().

Ejemplo con los cuatro tipos de gráficos disponibles.

Esta función recibe como primer parámetro el rango de celdas del que queremos recoger los valores. Opcionalmente puede recibir un array de valores como parámetros opcionales. Estas opciones serían pares de valores, irían encerradas entre llaves, separadas del rango por un punto y coma. La separación entre pares se marcaría con un punto y coma, y dentro de cada par con el carácter de la barra \. Debajo te dejo un ejemplo porque explicado así es un lío:

=SPARKLINE(C1:C25,{"charttype"\"bar";"max"\50;"empty"\"zero"})

El parámetro «charttype» indicaría el tipo de gráfico y puede tener los siguientes valores:

  • line: es el valor por defecto, el que tomasi no se especifica ninguno, genera un gráfico de líneas.
  • bar: genera un gráfico de barras apiladas.
  • column: genera un gráfico de columnas
  • winloss: es un gráfico de barras pero que solo representa si el valor es positivo o negativo.

En la imagen de arriba podéis ver cómo se visualizarían los cuatro. Para cada tipo de gráficos tienes después una serie de opciones, que puedes consultar en la documentación oficial de Google.

La función DETECTLANGUAGE() en la hoja de cálculo de Google Drive

Si no hace mucho veíamos cómo usar la función GOOGLETRANSLATE() para traducir el texto en una celda de una hoja de cálculo de Google Drive tenemos una opción similar: detectar el idioma de una celda.

Para eso nos serviremos de la función DETECTLANGUAGE(), que recibe como parámetro una cadena de texto y devuelve el código del idioma que corresponda. Tal que así:

=DETECTLANGUAGE(«Olá, bom dia») por ejemplo nos devolvería el código «PT» de portugués.