Originalmente publicado por mi mismo en inglés como respuesta a Show all combinations for a selection of columns
Nota: Hay cinco columnas para hacer coincidir los datos de muestra proporcionados por el OP.
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
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 (
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,- 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.
- 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)- Calcule el producto cartesiano:
=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
- 1 : ARRAYFORMULA