Ir al contenido principal

Cómo hacer un producto cartesiano o Cruzada (Crossjoin) en HCG

Originalmente publicado por mi mismo en inglés como respuesta a Show all combinations for a selection of columns

Respuesta corta

QUERY () permite el uso de un subconjunto de SQL pero este subconjunto no incluye la cláusula FROM.

QUERY () requiere una sola matriz como tabla fuente, por lo que no se puede usar, en su lugar usa ARRAYFORMULA (), TRANSPOSE (), JOIN (), SPLIT (), REPT (), FILTER (), COUNTA (), SORT () y los operadores de división ( / ) y concatenación ( & ) crean una "fórmula flexible" ya que su rendimiento es mejor que una función personalizada.

Procedimiento

Suponiendo que para "código de función de hoja de cálculo flexible", el OP significa una fórmula,
  1. Para simplificar las cosas, agregue un apóstrofo antes de cada valor numérico para formatearlo como texto. 
Nota: De otra manera, se requerirá usar COUNT en lugar de COUNTA para las columnas numéricas, y esto hará que el siguiente procedimiento sea menos flexible, ya que esto solo funcionará en el "futuro" si todos los valores de columna añadidos más tarde son números. 

  1. Calcule el tamaño del producto cartesiano .
H1:
=COUNTA(A:A)*COUNTA(B:B)*COUNTA(C:C)*COUNTA(D:D)*COUNTA(E:E)*COUNTA(F:F)
Nota: Hay cinco columnas para hacer coincidir los datos de muestra proporcionados por el OP.
  1. Calcule el producto cartesiano:
J1:
 =ArrayFormula( { SORT( TRANSPOSE( split(REPT(JOIN(",",TRANSPOSE(filter(A:A,LEN(A:A))))&",",$H$1/COUNTA(A:A)),",")) &TRANSPOSE( split(REPT(JOIN(",",TRANSPOSE(filter(B:B,LEN(B:B))))&",",$H$1/COUNTA(B:B)),",")) ,1,TRUE) &TRANSPOSE( split(REPT(JOIN(",",TRANSPOSE(filter(C:C,LEN(C:C))))&",",$H$1/COUNTA(C:C)),",")) &TRANSPOSE( split(REPT(JOIN(",",TRANSPOSE(filter(D:D,LEN(D:D))))&",",$H$1/COUNTA(D:D)),",")) &TRANSPOSE( split(REPT(JOIN(",",TRANSPOSE(filter(E:E,LEN(E:E))))&",",$H$1/COUNTA(E:E)),",")) &TRANSPOSE( split(REPT(JOIN(",",TRANSPOSE(filter(F:F,LEN(F:F))))&",",$H$1/COUNTA(F:F)),",")) } ) 

f1 (A): $H$1/COUNTA(A:A) número de veces que los valores de la columna A deben repetirse.

f2 (A): valores de filter(A:A,LEN(A:A)) en la columna A.

f3 (A): TRANSPOSE(split(REPT(JOIN(",",TRANSPOSE(f2))&",",f1),",")) repite los valores de la columna

f4 (AB): SORT(f3(A)&f3(B),1,TRUE) Concatena el primer par de columnas y clasifícalas en orden ascendente.

f5 (): f4(AB)&f3(C)&f3(D)&f3(E)&f3(F)

Concatena las filas de las otras columnas. Como solo una de estas columnas tiene más de un elemento, no es necesario ordenarlas de nuevo.

Nota: Hay una fórmula f3 () para cada columna. Hay cinco que coinciden con los datos de muestra proporcionados por el OP.

f6 (): ArrayFormula(f5()) : "Habilita la visualización de valores devueltos por una fórmula de matriz en varias filas y / o columnas y el uso de funciones que no son de matriz con matrices" 1 .


Hoja de cálculo de demostración

Referencias

Entradas más populares de este blog

Escribir respuestas de formulario a hoja de cálculo de Google

Aplicación web que agrega una fila a una hoja de cálculo con los valores obtenidos por medio de un formulario HTML.

NOTA: Publicado originalmente aquí por mi mismo.

Referencias
Google Spreadsheet Programming With Google Apps Script (GAS)Get form input text value to insert in a Google Spreadsheet Actualización 16 de agosto de 2018
Este este uno de los artículos con más visitas en mi antiguo sitio Ejemplos para Foros. El código incluido abajo es una adaptación de código en la respuesta al segundo enlace , básicamente una adaptación al español con la finalidad de compartirlo en el foro de ayuda de Google Drive.
Consta de dos archivos, uno para código del lado del servidor Código.gs y otro para código del lado del cliente inicio.html. Pueden agregarse a un proyecto de Apps Script independiente el cual deberá ser publicado como aplicación web. Requiere contar con el id de una Hoja de Cálculo de Google (HCG) que corresponda a al archivo al cual se desean enviar los datos.
Esto tiene sentido …

Les presento Visor de Registros

El día de hoy Google ha tenido a bien aprobar la publicación de mi primer complemento público para Hojas de Cálculo de Google (HCG). El nombre de este complemento es Visor de Registros. En la barra superior pueden encontrar el enlace al sitio con la información sobre su uso bastante sintetizada. A continuación les comparto de que se trata de forma "mas platicada".

Visor de Registros es un complemento gratuito que muestra los encabezados y los valores de la fila que contiene la celda activa en un panel lateral.

Me imagino que será especialmente útil para quienes abren archivos CSV con muchos datos. A continuación les muestro cómo se ve los datos de Hospital2016.csv disponibles en datos.gob.mx


El archivo tiene 18 columnas y 1831 renglones. Una de las columnas es una marca temporal, otra contiene números correspondientes a la edad de los sujetos y el resto son categorías, algunas de las cuales no se muestran completas en el ancho y altura predeterminada de una celda.

Si se ajus…

¿Qué es __xludf.DUMMYFUNCTION?

Resumen
__xludf.DUMMYFUNCTION es una función de las Hojas de Cálculo de Google agregada al descargar una hoja de cálculo en formato Microsoft Excel (.xlsx) a las fórmulas que utilizan funciones no compatibles.
He descargado mi hoja de cálculo y veo __xludf.DUMMYFUNCTION ¿Qué es? ¿Por qué Google modificó mis fórmulas?
Si usas funciones de Hojas de Cálculo de Google (HCG) que no son compatibles con Microsoft Excel (Excel) como IMPORTRANGE y descargas una hoja de cálculo como Microsoft Excel (.xlsx) podrías haber visto que las fórmulas originales fueron modificadas para incluir 
=IFERROR(__xludf.DUMMYFUNCTION(tu_formula),"valor") (funciones en inglés)

=SI.ERROR(__xludf.DUMMYFUNCTION(tu_formula),"valor") (funciones en español)

o bien

=IFERROR(__xludf.DUMMYFUNCTION("""COMPUTED_VALUE"""),"valor") (funciones en inglés)

=SI.ERROR(__xludf.DUMMYFUNCTION("""COMPUTED_VALUE"""),"valor") (funciones …