excel 我如何得到一个单元格中的排列列表?

kx7yvsdv  于 2023-05-08  发布在  其他
关注(0)|答案(4)|浏览(350)

下面是5列-第6列包含所需的结果:在每一个可能的排列中的5列中的名称。
两行只有两列有值--所以第6列中只有两个排列(由“;“).
一行有4个值,所以有24个排列。

对不起的形象,我不能工作出如何粘贴表格从Excel到堆栈,而不必重写它的一切。
真实的数据集中的行可以有1个值、5个值或介于两者之间的任何值。
答案here似乎只适用于二维数组-这个数组是一维的。我想不出任何明显的方法来使它二维,使这些解决方案的工作,也不似乎是一个有效的方式来做到这一点。下面是上面答案中的一个公式(我看了所有这些公式,不知道如何使它们适应我的需要):

=LET(A,A1:C3,B,ROWS(A),C,COLUMNS(A),D,B^C,E,UNIQUE(MAKEARRAY(D,C,LAMBDA(rw,cl,INDEX(IF(A="","",A),MOD(CEILING(rw/(D/(B^cl)),1)-1,B)+1,cl)))),FILTER(E,MMULT(--(E<>""),SEQUENCE(C,,,0))=C))

我在谷歌上搜索过的大多数解决方案似乎也不能在1行5列的简单数组上工作(或者我不能让它们工作)。
我试着从头开始做,并尽可能生成一个只包含数字1-5的数字列表,并且没有重复-

=LET(firstperm,VALUE(CONCAT(SEQUENCE(1,COLUMNS(Tablestu[@[First Name]:[Preferred Last Name]])))),lastperm,VALUE(CONCAT(SORT(SEQUENCE(1,COLUMNS(Tablestu[@[First Name]:[Preferred Last Name]])),,-1,TRUE))),diff,(lastperm-firstperm)+1,list,SEQUENCE(diff,1,firstperm),wanted,(IF((ISNUMBER(SEARCH("1",list))*ISNUMBER(SEARCH("2",list))*ISNUMBER(SEARCH("3",list))*ISNUMBER(SEARCH("4",list))*ISNUMBER(SEARCH("5",list))),list,"")),FILTER(wanted,wanted<>"",""))

我想我可以把这5个数字分开,然后用一个索引公式按顺序返回单词。即31452将返回第三个单词,然后是第一个,然后是第四个,依此类推。但我仍然有一百万个键盘头粉碎远离把它变成我需要它,它将是愚蠢的效率,当我甚至到达那里。
链接的答案似乎是正确的事情-我使用Excel 365,我需要一个使用公式的解决方案,而不是VBA或Power Query。
LAMDA和LET都很好。

y3bcpkx1

y3bcpkx11#

注意事项:这并不被推荐作为一个通用的解决方案,尽管由于OP给出了条目数量的上限5,这也许值得考虑。

=LET(
    ζ,A2:E2,
    ξ,COUNTA(ζ),
    κ,SEQUENCE(,ξ),
    λ,LEFT(12345,ξ),
    γ,REPT(1+ξ,ξ)-λ,
    δ,SEQUENCE(γ-λ+1,,λ),
    α,INDEX(FILTER(ζ,ζ<>""),
        MID(FILTER(δ,MMULT(N(ISERR(FIND(MID(λ,κ,1),δ))),TOCOL(κ))=0),κ,1)
      ),
    TEXTJOIN(";",,BYROW(α,LAMBDA(β,CONCAT(β))))
)
hgb9j2n6

hgb9j2n62#

替代溶液:

=LET(range, A1:E1,
     f,     FILTER(range,range<>""),
     c,     COLUMNS(f),
     s,     c^c,
     a,     MAKEARRAY(  s, c,
               LAMBDA( rw, cl,
                       MOD(CEILING(rw/(s/(c^cl)),1)-1,c)+1)),
     p,     FILTER(a,
                   BYROW(  a,
                   LAMBDA( x,
                           AND(MMULT(N(TRANSPOSE(x)=x),SEQUENCE(c)^0)=1)))),
TEXTJOIN(";",,UNIQUE(BYROW(p,LAMBDA(x,TEXTJOIN("",1,CHOOSECOLS(f,x)))))))

这还考虑到如果在范围内使用重复的名称,则不会生成重复的排列。

tvokkenx

tvokkenx3#

我把这个作为一种替代方案,因为这种方法与我以前的回答中使用的方法有很大的不同。

=LET(
    ζ,A2:E2,
    ξ,FILTER(ζ,ζ<>""),
    TEXTJOIN(";",,
        SORT(UNIQUE(BYROW(RANDARRAY(2^10,COLUMNS(ξ)),LAMBDA(γ,CONCAT(SORTBY(ξ,γ))))))
    )
)

只要RANDARRAY的第一个参数被选择为足够大,那么所得到的数组不是唯一的概率将非常小,以至于实际上保证了正确的输出。在这里,2^10似乎足够用于最多5个非空条目。
同样,这种设置不容易扩展到超过5个非空白条目,尽管由于其简洁性和/或性能,它可能值得考虑。

aydmsdu9

aydmsdu94#

考虑到以前的答案中的评论,关于Excel性能和Excel不提供开箱即用的置换生成器的事实。此解决方案使用了一种高效的方法,通过免费的加载项使用Excel javascript集成:脚本实验室这是一个Microsoft Garage项目。它是多平台的,我在Excel的Web版本下测试,但你可以尝试与Excel桌面。
关于进行排列的JavaScript算法,我从问题中改编了其中两个:JavaScript中的排列?。
“适应”的意思是:

  • 添加参数定义所需的注解。有关详细信息,请查看Office脚本元数据参考。
  • 将输入数组(arr)从二维数组转换为一维数组。javascript问题中的所有算法都假设一维数组,但是当我们调用一个接收Excel范围的javascript函数时,即使是行/列范围,这种范围也表示为2D数组([][]),因此我们在调用算法之前使用javascript数组内置的flat()方法进行转换。

来自@caub:最短的一个,但效率较低

/**
 * Generates all permutation without repetition of the input argument
 * @customfunction
 * @param {any[][]} arr Array of input values
 * @returns {any[][]} 2D array with all permutations.
 */
function permute(arr) {
  if (Array.isArray(arr[0])) {
    arr = arr.flat();
  }
  function recur(a) {
    if (!a.length) return [[]];
    return a.flatMap((x, i) => {
      return recur(a.filter((v, j) => i !== j)).map((vs) => [x, ...vs]);
    });
  }
  return recur(arr);
}

来自@le_m:更大,但最高效的一个

/**
 * Generates all permutation without repeatiton of the input argument
 * @customfunction
 * @param {any[][]} arr Array of input values
 * @returns {any[][]} 2D array with all permutations.
 */
function permute_eff(arr) {
  if (Array.isArray(arr[0])) {
    arr = arr.flat();
  }
  var length = arr.length,
    result = [arr.slice()],
    c = new Array(length).fill(0),
    i = 1,
    k,
    p;

  while (i < length) {
    if (c[i] < i) {
      k = i % 2 && c[i];
      p = arr[i];
      arr[i] = arr[k];
      arr[k] = p;
      ++c[i];
      i = 1;
      result.push(arr.slice());
    } else {
      c[i] = 0;
      ++i;
    }
  }
  return result;
}

有了javascript函数,下一步就是从Excel中调用它。感谢脚本实验室,这是一个简单的任务,我创建了一个新的片段,我叫它lib。然后才能调用它。例如:

定义的函数可以按预期的方式用于行/列数组输入以及数字或文本数据类型。我测试了这两个函数,以找到SEQUENCE(8)的所有排列,即超过40K排列。它返回的结果(两个JavaScript公式)在2-4秒的范围内,它们之间没有显著差异。
现在回到原来的问题。它会产生一个非常简单的公式,然后将其拖下:

=TEXTJOIN(";",,BYROW(SCRIPTLAB.LIB.PERMUTE(FILTER(A2:E2,A2:E2<>"")),
  LAMBDA(x, CONCAT(x))))

下面是输出:

这个解决方案唯一不方便的地方是,似乎不能使用LAMBDA的名称作为动态数组帮助函数(如BYROW)的输入来调用自定义脚本实验室函数。例如,以下内容不起作用:

=BYROW(A2:E4, LAMBDA(x, 
 TEXTJOIN(";",,BYROW(SCRIPTLAB.LIB.PERMUTE(FILTER(x,x<>"")),
  LAMBDA(y, CONCAT(y))))))

所以你不能生成一个会溢出整个结果的数组版本。你会得到以下错误,没有太多的信息:


显然,你可以创建一个更具体的javascript函数来解决这个问题,所以它接收一个2D数组,并为每行生成预期的结果,在javascript中构建所有的逻辑。在这种情况下,它会溢出整个结果,但这将超出我的回答范围。目前提供的解决方案足以说明如何在Excel函数/计算效率低下的情况下将Excel与JavaScript函数集成。
我希望它能有所帮助,我认为这是一个很好的解决方案,在使用excel函数不会提供良好的性能或它冻结的情况下,例如生成所有排列等繁重的计算过程。

相关问题