Una colección de funciones para hojas de cálculo de Google
¿Para qué sirve?
HdC+ incorpora diversas funciones personalizadas y herramientas que en algún momento u otro he necesitado implementar en mis propias hojas de cálculo y que he acabado desparramando por aquí y allá, así que me he animado a juntarlas todas en este engendro. Si alguna de ellas te resulta de utilidad, ¡estupendo!
instalación
HdC+ está disponible en dos versiones:
(A) Complemento para hojas de cálculo. Lo encontrarás en el Google Workspace Marketplace. Puedes instalarlo siguiendo estas instrucciones generales.
(B) Plantilla. Solo tienes que hacer clic aquí para abrir la última versión disponible y, a continuación, pinchar en UTILIZAR PLANTILLA.
Te recomiendo que escojas la primera opción. No solo te resultará más sencillo utilizar HdC+ en tus propias hojas de cálculo, sino que recibirás automáticamente las nuevas versiones publicadas.
DEscripción detallada e instrucciones de uso
HdC+ incluye en estos momentos las siguientes características, los resultados de cuya aplicación pueden deshacerse mediante el comando Editar ↩️ Deshacer.
Barajado de datos: Desordenar por filas · Desordenar por columnas. Grupos de casillas de verificación: Activar · Desactivar · Invertir.
Estructura de datos: Consolidar dimensiones · Transponer rangos (preservando formato, notas y validación).
Estructura de hoja de cálculo: Eliminar filas y columnas no utilizadas · Añadir filas y columnas · Mostrar, ocultar o eliminar pestañas en función de su visibilidad o color.
Generación de datos: Nombres · DNIs.
Ofuscación: Recodificar texto en Base64 · Generar hash MD1/5 · Generar hash SHA-1/256/384/512.
Transformación de texto: Eliminar caracteres especiales · Eliminar espacios / espacios tras comas / saltos de línea · Convertir comas en espacios / comas en saltos de línea / espacios a comas / espacios en saltos de línea / saltos de línea en espacios / saltos de línea en comas · Iniciales a mayúsculas · Inicial a mayúsculas · Todo a mayúsculas · Todo a minúsculas.
Funciones personalizadas: ACOPLAR() · CONTARCOLOR() · UNPIVOT() · BARAJARDATOSFIL() · BARAJARDATOSCOL() · DESACOPLAR() · DISTANCIA_EDICION() · DISTANCIA_EDICION_MINIMA() · HASH() · BASE64() · REPETIRFC() · RELLENAR() · TROCEAR()
Atención: Los complementos instalados deben activarse de manera independiente en cada hoja de cálculo donde se deseen utilizar. Por tanto, para que las funciones personalizadas de HdC+ puedan ser empleadas en tus fórmulas, utiliza por primera vez cualquiera de los comandos del menú del complemento, por ejemplo Acerca de o simplemente actívalo en la hoja de cálculo correspondiente.
Sigue leyendo para saber más sobre cómo usar HdC+. Si prefieres algo más ligero, quizás quieras echarle un vistazo a este #hilotutorial.
Licencia y código fuente
HdC+ se distribuye bajo licencia GNU GPL v3. Puedes ver el código fuente en su repositorio de GitHub.
Soporte
Utiliza el formulario de contacto de esta página web para hacerme llegar comentarios, sugerencias o plantear cualquier duda que te surja utilizando esta herramienta.
DESORDENAR
Aunque el editor de hojas de cálculo ya incluye un comando de menú para ordenar aleatoriamente los datos de un intervalo, su funcionamiento se limita a alterar la posición de los elementos de cada columna de manera independiente. HdC+ permite desordenarlos (barajarlos) tanto por filas como por columnas. Esta característica también puede utilizarse por medio de las funciones personalizadas BARAJARDATOSFIL y BARAJARDATOSCOL. Ej:
=BARAJARDATOSCOL(B2:C6)
=BARAJARDATOSFIL(B8:F9)
casillas de verificación
Mediante estos dos comandos es posible activar o desactivar todas las casillas de verificación contenidas dentro del rango de celdas seleccionado. Realmente se consigue exactamente lo mismo simplemente arrastrando con el ratón para seleccionarlas y pulsando la barra espaciadora. No obstante HdC+ añade una tercera posibilidad, la de invertir su estado, así que por coherencia se mantienen las tres funciones dentro del menú del complemento. Se admite la selección múltiple de rangos disjuntos.
ESTRUCTURA DATOS→ consolidar Dimensión
Realiza una consolidación dimensional de la información que se encuentra en varias columnas contiguas, agrupando sus valores en categorías. Esto suele facilitar la generación de paneles de análisis significativos. Aquí tienes una explicación más detallada del proceso. Esta característica también está disponible mediante la función personalizada UNPIVOT:
=UNPIVOT(intervalo; núm_columnas_fijas; matriz_fila_encabezados)
Ej:=UNPIVOT(B1:E5; 1 ;{"Alumno"\"Pregunta"\"Respuesta"})
ESTRUCTURA DATOS→ TRANSPONER
Realiza una transposición de las celdas contenidas en el rango seleccionado, intercambiando filas y columnas. A diferencia de la función integrada TRANSPONER, se preserva el formato (también condicional), notas y reglas de validación aplicadas previamente. Cuidado, este proceso es destructivo (aunque reversible deshaciendo cambios en la hoja de cálculo), dado que los datos transpuestos pueden sobreescribir el contenido de celdas anexas.
ESTRUCTura hdc → eliminar f/C
Elimina de manera selectiva las filas y/o columnas situadas más allá de las celdas con información. Eso no solo tiene ventajas estéticas, sino que puede contribuir a agilizar el funcionamiento de hojas de cálculo complejas en las que se empleen fórmulas matriciales con rangos infinitos. El panel de ajustes permite realizar la operación seleccionada sobre la hoja actual (de manera predeterminada) o sobre todas las de la hoja de cálculo.
ESTRUCTURA HDC → Insertar f/C
Se utiliza para insertar el número deseado de filas y/o columnas. La inserción puede hacerse a) al inicio de la hoja b) al final de la hoja c) antes de la celda actual d) tras la celda actual.
ESTRUCTURA HDC → MANIPULAR HOJAS
Cuando las hojas de cálculo tienen muchas pestañas comienza a ser un problema ocultarlas o eliminarlas en bloque. En este submenú hay algunas funciones que pretenden resolverlo, al menos parcialmente. Destacar la posibilidad de mostrar u ocultar hojas a cuyas pestañas se les haya aplicado previamente un color determinado (naranja #ff9900 o azul #0000ff en el selector de colores).
GENERAR
Permite generar nombres y apellidos (se recurre a una lista con los 200 más frecuentes en España) y NIFs ficticios. Si se selecciona un rango de exactamente 3 columnas, primer apellido, segundo apellido y nombre se distribuirán adecuadamente en ellas. Ideal para construir tablas con datos personales (falsos) en formaciones o demos. No se garantiza que no aparezcan duplicados.Se admite la selección múltiple de rangos disjuntos.
OFUSCAR
Sustituye el contenido de las celdas seleccionadas por sus hashescalculados, valores de longitud fija obtenidos a partir de los datos iniciales aplicando algoritmos de resumen MD2, MD5, SHA-1, SHA-256, SHA-384 o SHA-512 con recodificación a base64 (representación más compacta) Esto permite enmascarar la información, mostrándola de manera diferenciada sin revelar el contenido original. También es posible recodificar el contenido de las celdas seleccionadas en base64.Se admite la selección múltiple de rangos disjuntos. Además, estas transformaciones también pueden efectuarse utilizando las funciones personalizadas siguientes:
=BASE64(valores, esHexa)
esHexa = VERDADERO | FALSO. Indica si valores son datos binarios como cadena hexadecimal.=HASH(valores; base64_VoF; función_hash) base64VoF = VERDADERO si se desea que el hash se recodifique en base 64.
TRANSFORMAR
Solo hay que seleccionar las celdas con el texto a transformar y, a continuación, el comando de menú correspondiente. En el caso de la eliminación de caracteres especiales, se emplea una tabla de sustitución. Esta función puede resultar de utilidad, por ejemplo, a la hora de generar direcciones de email a partir de nombres y apellidos. También se dispone de numerosos comandos para realizar transformaciones de espacios, saltos de línea y comas.Se admite la selección múltiple de rangos disjuntos.
Asimismo, se puede utilizar la función personalizada TROCEAR:
=TROCEAR(intervalo; cadena_separador)
Ej:=TROCEAR(C2:C50; ",")
Genera un vector columna construido a partir de la segmentación de cada una de las celdas del rango C2:C50, utilizando la cadena de texto ", " (coma espacio) como elemento divisor del texto que contienen. Esto puede resultar muy útil, por ejemplo, para facilitar el conteo individualizado de las respuestas de tipo casilla de verificación procedentes de un formulario.
Si necesitas ir un paso más allá, también dispones de las funciones ACOPLAR y DESACOPLAR, que permiten consolidar o duplicar las filas de un intervalo de datos en función de los valores múltiples, separados por una secuencia de caracteres delimitadora arbitraria, presentes en una o más de sus columnas.
=ACOPLAR(intervalo; encabezado; separador; permitirRepetidos; columna; [más_columnas])
=DESACOPLAR(intervalo; encabezado; separador; forzarNum; columna; [más_columnas])
En este artículo encontrarás más información sobre su utilidad. Las versiones de estas funciones incluidas en HdC+ están mejoradas. Ambas disponen de la capacidad de omitir o preservar los valores duplicados y, además,DESACOPLARpuede devolver opcionalmente como números los valores de texto que puedan interpretarse como tales.Consulta la amplia ayuda contextual de ambas funciones para conocer todas sus posibilidades.
FX CONTARCOLOR / FORZAR recálculo
La función CONTARCOLOR es una pequeña excentricidad. Cuenta las celdas del rango indicado con un determinado color de fuente (texto) o celda (fondo). El color puede expresarse en hexadecimal (#rrggbb) o por medio de la referencia a una celda cuyos atributos de color quieren emplearse como referencia. Ej:
=CONTARCOLOR("B2:F6"; "fondo"; "como"; "A9")
=CONTARCOLOR("B2:F6"; "fondo"; "#d9d9d9")
=CONTARCOLOR("B2:F6"; "fuente"; "como"; "A10")
Sí, las referencias se deben entrecomillar (ya dije que esto era una rareza), lo que impide que las referencias a las celdas cambien del modo esperado al arrastrar la fórmula. Esto puede solventarse construyendo cadenas de texto como referencias mediante la concatenación de elementos calculados y/o recurriendo a la notación RC. Ej:
=CONTARCOLOR("B2:F6"; "fuente"; "como"; "A" & FILA(A10))
=CONTARCOLOR("B2:F6"; "fuente"; "como"; "R" & FILA(A10) & "C1")
En general no es buena idea utilizar el formato para codificar información en una hoja de cálculo, pero a mí me ha resultado práctico ocasionalmente en tablas de horarios. Como la aplicación de formato no desencadena (afortunadamente) un recálculo de las fórmulas existentes, he añadido también el comando de menú Forzar recálculo de hoja, cuyo uso es necesariamente complementario a esta función CONTARCOLOR.
DISTANCIA DE EDICIÓN
Dos funciones personalizadas que permiten cuantificar las diferencias entre secuencias de texto utilizando una variante de la distancia de Levenshtein (distancia de edición entre cadenas).
=DISTANCIA_EDICION(
c1;c2;
permiteTrans; distingueMayusculas;
fuerzaTexto; costeInsercion;costeEliminacion; costeSustitucion; costeTransposicion
)
=DISTANCIA_EDICION_MINIMA(
cadena; referencia; numCadenas; distanciaMax;
devuelveDistancia; permiteTrans; distingueMayusculas; fuerzaTexto;
costeInsercion; costeEliminacion; costeSustitucion; costeTransposicion
)
FX REPETIRFC / RELLENAR
Aunque es posible combinar funciones ya disponibles en las hojas de cálculo para repetir un valor o el contenido de una celda un número arbitrario de veces, las fórmulas resultantes pueden resultar excesivamente complejas. Por ello HdC incluye también estas funciones personalizadas:
=REPETIR(elemento; nº_de_repeticiones; modo)
...donde modo = "FIL o "COL".
=RELLENAR (elemento; nº_de_filas; nº_de columnas)
POLÍTICA DE PRIVACIDAD / privacy policy
[ES] HdC+ ofrece diversas funciones adicionales a las disponibles en las hojas de cálculo de Google de manera predeterminada (ver detalles en el apartado anterior) que en ningún caso suponen el envío de correos electrónicos. HdC+ muestra paneles laterales dentro de la interfaz de las hojas de cálculo de Google para facilitar la introducción de datos por parte del usuario. HdC+ solo manipula los datos de las hojas de cálculo sobre las que se ha activado para realizar sus operaciones y nunca extrae ni almacena externamente información procedente de ellas. HdC+ no almacena ni comparte información del usuario. HdC+ no registra datos de uso, ni siquiera anónimos.
[EN] HdC+ provides users with several additional features not available in vanilla Google Spreadsheets (check details in former section) that in no case imply the sending of e-mails. HdC+ shows sidepanels inside Google Sheets' user interface to ease data input. HdC+ only manages data inside the spreadsheets on which it has been activated to perform its operations and never extracts or stores any information from the data sheet elsewhere. HdC+ neither stores nor shares any user data. HdC+ does not collect any usage data, not even anonymously.
CONDICIONES DE USO / terms of use
[ES] Las herramientas que se facilitan han sido desarrolladas a título personal por el propietario de esta página web, que carece de todo objetivo o interés comercial, y son de uso libre y gratuito. Han sido probadas y utilizadas internamente previamente a su publicación y, en todo caso, se proporcionan a la comunidad de buena fe y tal cual, no asumiendo el autor responsabilidad alguna derivada de su uso sobre los datos de los usuarios que deciden emplearlas libremente.
[EN] The tools provided herein have been developed by the owner of this website, that is devoid of any commercial interests whatsoever, and are free and gratis. They have been tested and used internally before being published, and they are, in any case, provided to the community "as is" in good faith. The author does not assume any liability for their usage with the data of those users that freely elect to use them.
