☰

Excel：如何将表格转换为记录列表结构

tag-icon • tag-icon microsoft-excel worksheet-function
Excel：如何将表格转换为记录列表结构

给定下表，我想找到满足输入值且具有给定公差的最高标题编号（x在 中用 的值表示）和伴随的行索引（在 中用 的值J4表示）：yJ5I2J2

如您所见，表中有两个值满足输入值和公差：单元格F3和G3。但是，由于 中的标题行号G2(48) 高于 中的F2(44)，我们将将其作为最终解决方案，并打印出和G3中的相应数字。J4J5

我有一个解决方案，使用中间矩阵，其中绝对值减去输入值。然后从后往前遍历每一行，G在B冗长的公式中使用 IF 和 SMALL（非常有限且非常硬编码）。

我希望使用现代 Excel 函数（LAMBDA、MAP 等）将此表转换为记录列表，然后简单地输出相应的值。本质上，将表转换为内存中的记录结构，而无需在工作表中使用中间矩阵，如下所示（ 、 和 的转换G2示例G3）E7：

List<Record> vals = [ 
   (36.22, 48, 1),
   (69.31, 48, 2),
   ...
   (169.99, 40, 6),
   ...
]

使用这种结构，就可以更容易地找到在输入值和公差范围内的第一个值并输出 x和 yvals.[field1]相应的列表条目。vals.[field2]vals.[field3]

可以使用现代 Excel 函数和内存计算优雅地完成此操作吗？

输入数据可以在这里找到：https://pastebin.com/JRaA14e8

二十八 三十二 三十六 40 四十四 四十八
1 23.02 25.66 28.30 30.94 33.58 36.22
2 42.91 48.19 53.47 58.75 64.03 69.31
3 62.80 70.72 78.64 86.56 94.48 102.40
4 82.69 93.25 103.81 114.37 124.93 135.49
5 102.58 115.78 128.98 142.18 155.38 168.58
6 122.47 138.31 154.15 169.99 185.83 201.67
7 142.36 160.84 179.32 197.80 216.28 234.76
8 162.25 183.37 204.49 225.61 246.73 267.85
9 182.14 205.90 229.66 253.42 277.18 300.94
10 202.03 228.43 254.83 281.23 307.63 334.03
11 221.92 250.96 280.00 309.04 338.08 367.12
12 241.81 273.49 305.17 336.85 368.53 400.21
十三 261.70 296.02 330.34 364.66 398.98 433.30

编辑 1：附加背景和示例

我最初的问题似乎没有明确说明哪种优化条件具有普遍性以及原因。此示例是可折叠/可伸缩太阳能电池板经济可行性计算工具链的一小部分。标题行 ( 1:1) 表示每条轨道的模块/面板数量，( A:A) 列表示轨道数量。因此，x 实际上表示每条轨道的模块或面板数量，y 表示轨道数量。

在这个特定的工业案例模型中，对于钢支撑结构而言，每条轨道上太阳能电池板的密度越高，总是比拥有更多轨道越经济。

当使用元组 (200,3) 作为 (输入值，容差) 时，生成的三个元组符合选择标准：

(28,10)
(40,7)
(48,6)

由于 48 个模块/轨道比 40 个或 28 个模块提供更高的密度，因此正确的最终解决方案是 x=48 和 y=6。

目前提供的解决方案的情况如下（尽管我可能没有正确整合 Redy 的解决方案）：

编辑 2：实施建议的解决方案

看起来至少有两种可行的解决方案：

答案1

我喜欢 INDEX/AGGREGATE。我们可以使用 LET 来限制需要更新的引用数量：

=IFERROR(LET(
    rng,B2:G14,
    trgt,L2,
    tl,L3,
    hd,1:1,
    rw,A:A,
    clm,AGGREGATE(14,7,COLUMN(rng)/(ABS(rng-trgt)<=tl),1),
   rlclm,clm-MIN(COLUMN(rng))+1,
    VSTACK(
        INDEX(hd,clm),
        INDEX(rw,AGGREGATE(14,7,ROW(INDEX(rng,0,rlclm))/(ABS(INDEX(rng,0,rlclm)-trgt)<=tl),1)))),{-1;-1})

注意索引部分使用了全列和全行。我们使用全列和全行，这样我们只需要返回实际的列和行，而不是相对的列和行，减少数学运算。

在此处输入图片描述

答案2

注意：此答案依赖于拥有最新版本的 Excel for Microsoft 365（已测试）或可能的 Excel for the Web（未经测试）。

编辑以解释多个匹配标准中的“最大列索引”：

调整方法以选择与最大列标题匹配的方法，这应该在单元格 J4 中起作用：

=LET(
    data, A1:G14,
    row_headers, DROP(TAKE(data, , 1), 1),
    col_headers, DROP(TAKE(data, 1), , 1),
    d, DROP(data, 1, 1),
    v, I2,
    tol, J2,
    cols, COLUMNS(d),
    ascol, TOCOL(d),
    position, SEQUENCE(ROWS(ascol)),
    row, ROUNDUP(position / cols, 0),
    col, MOD(position + cols - 1, cols) + 1,
    arr, HSTACK(ascol, row, col),
    include, BYROW(ascol, LAMBDA(x, AND(x >= (v - tol), x <= (v + tol)))),
    matches, FILTER(arr, include),
    sorted, SORT(matches, {3, 2}, {-1, -1}),
    is_best, TAKE(sorted, 1),
    result, VSTACK(
        INDEX(col_headers, 1, INDEX(is_best, 1, 3)),
        INDEX(row_headers, INDEX(is_best, 1, 2), 1),
        INDEX(is_best, 1, 1)
    ),
    result
)

在此处输入图片描述

或者作为 LAMBDA：

get_best_match = LAMBDA(data, value, tolerance,
    LET(
        //get the headers
        row_headers, DROP(TAKE(data, , 1), 1),
        col_headers, DROP(TAKE(data, 1), , 1),

        //just the search array
        d, DROP(data, 1, 1),
        v, value,
        tol, tolerance,
        cols, COLUMNS(d),

        //convert the array to a column with row and column positions added
        ascol, TOCOL(d),
        position, SEQUENCE(ROWS(ascol)),
        row, ROUNDUP(position / cols, 0),
        col, MOD(position + cols - 1, cols) + 1,
        arr, HSTACK(ascol, row, col),

        //filter for those rows that meet the criteria
        include, BYROW(ascol, LAMBDA(x, AND(x >= (v - tol), x <= (v + tol)))),
        matches, FILTER(arr, include),

        //sort descending on column header, row header
        sorted, SORT(matches, {3, 2}, {-1, -1}),

        //take the first row of the sorted matches
        is_best, TAKE(sorted, 1),

        result, VSTACK(
            INDEX(col_headers, 1, INDEX(is_best, 1, 3)),
            INDEX(row_headers, INDEX(is_best, 1, 2), 1),
            INDEX(is_best, 1, 1)
        ),
        result
    )
);

添加新标准之前的原始答案：

我相信这将在单元格 J4 中起作用：

=LET(
    data, A1:G14,
    row_headers, DROP(TAKE(data, , 1), 1),
    col_headers, DROP(TAKE(data, 1), , 1),
    d, DROP(data, 1, 1),
    v, I2,
    tol, J2,
    cols, COLUMNS(d),
    matches, MAP(d, LAMBDA(x, AND(x >= (v - tol), x <= (v + tol)))),
    is_best, d = MAX(d * matches),
    best_position, MAX(is_best * SEQUENCE(ROWS(d), cols)),
    y, ROUNDUP(best_position / cols, 0),
    x, MOD(best_position + cols - 1, cols) + 1,
    result, VSTACK(
        INDEX(col_headers, 1, x),
        INDEX(row_headers, y, 1),
        MAX(d * matches)
    ),
    result
)

在此处输入图片描述

我们可以在高级公式环境中对其进行注释并将其保存为名为 LAMBDA 的函数，如下所示：

get_headers = LAMBDA(data,test,tolerance,
    LET(
        //get just the row index from column 1, rows 2:max
        row_headers, DROP(TAKE(data, , 1), 1),

        //get just the column headers from row 1, columns 2:max
        col_headers, DROP(TAKE(data, 1), , 1),

        //get just the array of data to test against
        d, DROP(data, 1, 1),

        //get the number of columns in the array (the )
        cols, COLUMNS(d),

        //test each value against the boundaries
        matches, MAP(d, LAMBDA(x, AND(x >= (test - tolerance), x <= (test + tolerance)))),

        //identify the best value
        is_best, d = MAX(d * matches),

        //find the position of the best value
        best_position, MAX(is_best * SEQUENCE(ROWS(d), cols)),

        //identify the row index
        y, ROUNDUP(best_position / cols, 0),

        //identify the column index
        x, MOD(best_position + cols - 1, cols) + 1,

        //return the column header, the row header and the best value
        result, VSTACK(
            INDEX(col_headers, 1, x),
            INDEX(row_headers, y, 1),
            MAX(d * matches)
        ),
        result
    )
)

答案3

  1. LARGE我们使用、MAP和函数的组合来找到最大数字LAMBDA。
=LARGE(($B$2:$G$14)*(--MAP($B$2:$G$14,LAMBDA(r,AND($I$2-$J$2<=r,$I$2+$J$2>=r)))),1)

        - MAP($B$2:$G$14,LAMBDA(r,AND($I$2-$J$2<=r,$I$2+$J$2>=r)))：根据数组中的数字是否在公差值范围内，返回 TRUE 或 FALSE 数组。

        - ($B$2:$G$14)*(--MAP($B$2:$G$14,LAMBDA(r,AND($I$2-$J$2<=r,$I$2+$J$2>=r))))：然后，我们将前一个公式的结果与我们的数组（由于现在是 1 和 0 --）相乘，得到符合条件的数字。

        - LARGE(($B$2:$G$14)*(--MAP($B$2:$G$14,LAMBDA(r,AND($I$2-$J$2<=r,$I$2+$J$2>=r)))),1)：返回符合条件的最大数字。

  1. 我们使用最大的数字作为标准来返回 x 和 y

        -x公式：

=IFERROR(INDEX(B1:G1,,SUMPRODUCT(($B$2:$G$14=J5)*COLUMN($B$2:$G$14))-@COLUMN($B$2:$G$14)+1),-1)

        - y 公式：

=IFERROR(SUMPRODUCT(($B$2:$G$14=J5)*ROW($B$2:$G$14))-@ROW($B$2:$G$14)+1,-1)

x 和 y 的公式改编自本文描述的公式：获取二维数组中值的位置]。

-1如果未找到任何内容，则两个公式都会返回。

如上所述，值必须是唯一的这里：

注意如果数组中有重复的值，这些公式将会失败

相关内容