Excel函数从没有VBA的值构造数组

Question

注意这个问题是指一些特性，比如可选的 LAMBDA 参数和 ISOMITTED 函数，它们仅在 Beta channel 中可用(在撰写本文时)(更多信息 here)


我试图在 Excel LAMBDA 函数中模仿 VBA 的参数数组，所以想要一个函数:

=ARRAY(arg_1, [arg_2], [arg_3], ...)

...返回数组 {arg_1, arg_2, arg_3, ...}根据传递的许多参数动态调整大小。
如果我知道 args 的数量，我可以像这样使用选择函数:

=CHOOSE(SEQUENCE(number_of_args), arg_1, arg_2, arg_3, ...))

但我不想将参数的数量作为参数传递，我希望它是动态的。一个想法是使用 ISOMITTED(arg_n) 进行二分搜索。找到第一个缺失的论点。但这仍然硬编码了我的 ARRAY 函数的参数数量上限，更不用说创建硬编码的讨厌的二叉树了。

这当然很容易使用 vba:

Public Function ARRAYFROMARGS(ParamArray args()) As Variant
    ARRAYFROMARGS = args
End Function

尽管这只能接受可以强制转换为 Variants 的值，但不能接受 lambdas 或链接数据类型。这使得非 VBA 版本更加灵活。

Answer 1

正如我在问题中提到的那样，我找到了一种使用二进制搜索来评估缺少多少参数的方法。不过，它确实涉及很多复制粘贴。因此，这里的代码通过查找缺少的第一个来评估传递的参数数量:


标签
值(value)


姓名
ARGSCOUNT

范围
工作簿

评论
使用硬编码二进制搜索查找第一个省略的参数，最多 63 个 block

指=LAMBDA([p_1],[p_2],[p_3],[p_4],[p_5],[p_6],[p_7],[p_8],[p_9],[p_10],[p_11],[p_12],[p_13],[p_14],[p_15],[p_16],[p_17],[p_18],[p_19],[p_20],[p_21],[p_22],[p_23],[p_24],[p_25],[p_26],[p_27],[p_28],[p_29],[p_30],[p_31],[p_32],[p_33],[p_34],[p_35],[p_36],[p_37],[p_38],[p_39],[p_40],[p_41],[p_42],[p_43],[p_44],[p_45],[p_46],[p_47],[p_48],[p_49],[p_50],[p_51],[p_52],[p_53],[p_54],[p_55],[p_56],[p_57],[p_58],[p_59],[p_60],[p_61],[p_62],[p_63],IF(ISOMITTED(p_32),IF(ISOMITTED(p_16),IF(ISOMITTED(p_8),IF(ISOMITTED(p_4),IF(ISOMITTED(p_2),IF(ISOMITTED(p_1),0,1),IF(ISOMITTED(p_3),2,3)),IF(ISOMITTED(p_6),IF(ISOMITTED(p_5),4,5),IF(ISOMITTED(p_7),6,7))),IF(ISOMITTED(p_12),IF(ISOMITTED(p_10),IF(ISOMITTED(p_9),8,9),IF(ISOMITTED(p_11),10,11)),IF(ISOMITTED(p_14),IF(ISOMITTED(p_13),12,13),IF(ISOMITTED(p_15),14,15)))),IF(ISOMITTED(p_24),IF(ISOMITTED(p_20),IF(ISOMITTED(p_18),IF(ISOMITTED(p_17),16,17),IF(ISOMITTED(p_19),18,19)),IF(ISOMITTED(p_22),IF(ISOMITTED(p_21),20,21),IF(ISOMITTED(p_23),22,23))),IF(ISOMITTED(p_28),IF(ISOMITTED(p_26),IF(ISOMITTED(p_25),24,25),IF(ISOMITTED(p_27),26,27)),IF(ISOMITTED(p_30),IF(ISOMITTED(p_29),28,29),IF(ISOMITTED(p_31),30,31))))),IF(ISOMITTED(p_48),IF(ISOMITTED(p_40),IF(ISOMITTED(p_36),IF(ISOMITTED(p_34),IF(ISOMITTED(p_33),32,33),IF(ISOMITTED(p_35),34,35)),IF(ISOMITTED(p_38),IF(ISOMITTED(p_37),36,37),IF(ISOMITTED(p_39),38,39))),IF(ISOMITTED(p_44),IF(ISOMITTED(p_42),IF(ISOMITTED(p_41),40,41),IF(ISOMITTED(p_43),42,43)),IF(ISOMITTED(p_46),IF(ISOMITTED(p_45),44,45),IF(ISOMITTED(p_47),46,47)))),IF(ISOMITTED(p_56),IF(ISOMITTED(p_52),IF(ISOMITTED(p_50),IF(ISOMITTED(p_49),48,49),IF(ISOMITTED(p_51),50,51)),IF(ISOMITTED(p_54),IF(ISOMITTED(p_53),52,53),IF(ISOMITTED(p_55),54,55))),IF(ISOMITTED(p_60),IF(ISOMITTED(p_58),IF(ISOMITTED(p_57),56,57),IF(ISOMITTED(p_59),58,59)),IF(ISOMITTED(p_62),IF(ISOMITTED(p_61),60,61),IF(ISOMITTED(p_63),62,63)))))))

更容易复制版本here
它被称为 =ARGSCOUNT(arg_1, arg_2, ..., arg_63)来自封闭的 LAMBDA。请注意，它最多需要 63 个可选参数，因为我的二叉树是对称的，因此必须是 2 的幂(对于 0 参数的情况，减 1)并且命名引用的字符限制在 2000 左右。但是您可以多次调用它来自父函数并对结果求和，例如ARGSCOUNT(arg_1, ..., arg_63) + ARGSCOUNT(arg_64, ..., arg_126)然后可以在第二个 LAMBDA 函数中使用该计数来构建数组:


标签
值(value)


姓名
大批

范围
工作簿

评论
从逗号分隔的参数创建一个数组，最多 130 个

指=LAMBDA(_0,[_1],[_2],[_3],[_4],[_5],[_6],[_7],[_8],[_9],[_10],[_11],[_12],[_13],[_14],[_15],[_16],[_17],[_18],[_19],[_20],[_21],[_22],[_23],[_24],[_25],[_26],[_27],[_28],[_29],[_30],[_31],[_32],[_33],[_34],[_35],[_36],[_37],[_38],[_39],[_40],[_41],[_42],[_43],[_44],[_45],[_46],[_47],[_48],[_49],[_50],[_51],[_52],[_53],[_54],[_55],[_56],[_57],[_58],[_59],[_60],[_61],[_62],[_63],[_64],[_65],[_66],[_67],[_68],[_69],[_70],[_71],[_72],[_73],[_74],[_75],[_76],[_77],[_78],[_79],[_80],[_81],[_82],[_83],[_84],[_85],[_86],[_87],[_88],[_89],[_90],[_91],[_92],[_93],[_94],[_95],[_96],[_97],[_98],[_99],[_100],[_101],[_102],[_103],[_104],[_105],[_106],[_107],[_108],[_109],[_110],[_111],[_112],[_113],[_114],[_115],[_116],[_117],[_118],[_119],[_120],[_121],[_122],[_123],[_124],[_125],[_126],[_127],[_128],[_129],CHOOSE(SEQUENCE(ARGSCOUNT(_1,_2,_3,_4,_5,_6,_7,_8,_9,_10,_11,_12,_13,_14,_15,_16,_17,_18,_19,_20,_21,_22,_23,_24,_25,_26,_27,_28,_29,_30,_31,_32,_33,_34,_35,_36,_37,_38,_39,_40,_41,_42,_43,_44,_45,_46,_47,_48,_49,_50,_51,_52,_53,_54,_55,_56,_57,_58,_59,_60,_61,_62,_63)+ARGSCOUNT(_64,_65,_66,_67,_68,_69,_70,_71,_72,_73,_74,_75,_76,_77,_78,_79,_80,_81,_82,_83,_84,_85,_86,_87,_88,_89,_90,_91,_92,_93,_94,_95,_96,_97,_98,_99,_100,_101,_102,_103,_104,_105,_106,_107,_108,_109,_110,_111,_112,_113,_114,_115,_116,_117,_118,_119,_120,_121,_122,_123,_124,_125,_126)+ARGSCOUNT(_127,_128,_129)+1),_0,_1,_2,_3,_4,_5,_6,_7,_8,_9,_10,_11,_12,_13,_14,_15,_16,_17,_18,_19,_20,_21,_22,_23,_24,_25,_26,_27,_28,_29,_30,_31,_32,_33,_34,_35,_36,_37,_38,_39,_40,_41,_42,_43,_44,_45,_46,_47,_48,_49,_50,_51,_52,_53,_54,_55,_56,_57,_58,_59,_60,_61,_62,_63,_64,_65,_66,_67,_68,_69,_70,_71,_72,_73,_74,_75,_76,_77,_78,_79,_80,_81,_82,_83,_84,_85,_86,_87,_88,_89,_90,_91,_92,_93,_94,_95,_96,_97,_98,_99,_100,_101,_102,_103,_104,_105,_106,_107,_108,_109,_110,_111,_112,_113,_114,_115,_116,_117,_118,_119,_120,_121,_122,_123,_124,_125,_126,_127,_128,_129))

我知道很讨厌，但这使您可以访问一个非常简单的功能:

=ARRAY(A1, A2, 3, "foo", "bar") // array of anything, dynamically sized

奖金:
你甚至可以创建一个 LAMBDAS 数组来传递给 map 之类的东西:

=MAP(ARRAY(LAMBDA(x, x^2), LAMBDA(y, y+1)),LAMBDA(f, f(3)) // -> {9,4} i.e. 3^2, 3+1

...并且 vba ARRAYFROMARGS 函数不能将 LAMBDAS 作为参数。