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

¿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"""),"v

Marca temporal (timestamp) en Hojas de Cálculo de Google

NOTA: Esto fue publicado originalmente aquí . Una de las preguntas más recurrentes (PP.FF. / FAQ) es acerca de cómo insertar una marca temporal (timestamp). En ocasiones, en la pregunta se menciona la función AHORA(), en inglés NOW() . Pero esta se actualiza cada vez que se recalcula la hoja de cálculo por lo que no es útil como indicador de cuando se realizó alguna acción. En su lugar hay varias alternativas Alternativas: 1) Usar una combinación de teclas. Lamentablemente este no funciona en todos los equipos debido a los diferentes tipos de teclados. 2) Usar Google Apps Script. Sobre las anteriores opciones hay varios hilos en el foro de Google Drive y los editores de documentos de Google .  Algunos de los que he encontrado buscando "timestamp": Imprimir hora automáticamente - Foros de productos de Google Para que formula con Now, se ejecute solo una vez. - Foros de productos de Google Almacenamiento de fecha en una celda sin recalculo o dependenc

Mensajes de error en las HCG

Es posible que al utilizar una hoja de cálculo de Google (HCG) te hayas encontrado con un resultado como alguno de los siguientes: #DIV/0! #VALOR! #REF! #NOMBRE? ó#NAME? #NUM! #N/A #ERROR! Se tratan de mensajes cortos de error que nos da la HCG para indicar que ha ocurrido un error. Estos mensajes de error podrían estar acompañados de un pequeño triángulo rojo en la esquina superior derecha de la celda. Este discreto indicador podría pasar desapercibido si no estamos atentos, especialmente si la celda en la que ocurre el error ha sido resaltada por ser la celda activa La excepción es cuando la fórmula ha hecho uso de un función de error para dar como resultado uno de estos errors, como es el caso de la función NA() que da Este indicador nos podría ser de ayuda para entender que está pasando ya que al pasar el puntero del ratón sobre este mostrará un mensaje emergente con una descripción de lo que ha sucedido: #NULL! En las HCG este error sólo lo he visto en la