基于动态范围/矩阵公式实现JSON数据集字段分析需求

1. 动态表头公式（A1单元格） #

替代手动复制，输入后自动扩展适配字段数量：

=LET(
    SUB, INDIRECT("Sheet1!A8:"&ADDRESS(8, number_of_fields)),
    BYCOL(SUB, LAMBDA(cell, SUBSTITUTE(LEFT(cell, FIND(":", cell)-1), """", "")))
)

作用：遍历原始数据第一行（A8开始）的所有字段单元格，提取冒号前的字段名并去除双引号，作为分析表的表头。

2. 字段唯一性/变体数量（第2行，输入在A2） #

动态返回每列是否全唯一，或列内唯一值数量，自动适配列数：

=LET(
    data_range, INDIRECT("Sheet1!A9:"&ADDRESS(ROWS(Sheet1!A:A), number_of_fields)),
    BYCOL(data_range, LAMBDA(col, 
        LET(
            uniq_vals, UNIQUE(col),
            count_uniq, COUNTA(uniq_vals),
            IF(count_uniq = ROWS(col), "全唯一", count_uniq)
        )
    ))
)

说明：

• 遍历每一列数据，用UNIQUE提取唯一值并计数
• 若唯一值数量等于该列记录总数，返回「全唯一」；否则返回唯一值的数量
• 公式会随原始数据的字段数、记录数自动扩展/收缩

3. 字段Top3值（第3-5行，输入在A3） #

优化空值问题，自动向上填充现有变体，不足3个的行留空：

=LET(
    data_range, INDIRECT("Sheet1!A9:"&ADDRESS(ROWS(Sheet1!A:A), number_of_fields)),
    BYCOL(data_range, LAMBDA(col, 
        LET(
            freq, COUNTIF(col, col),
            uniq_with_freq, UNIQUE(HSTACK(col, freq)),
            sorted_vals, SORT(uniq_with_freq, 2, -1),
            top3, TAKE(sorted_vals, 3, 1),
            IFERROR(top3, "")
        )
    ))
)

说明：

• 对每一列数据，先计算每个值的出现频率，再按频率降序排序
• 取排序后的前3个值，若列内不足3种变体，剩余行自动留空，不会出现中间空值
• 输入后自动扩展到第3-5行的所有列，无需手动复制

注意事项 #

• 以上公式均需Excel 365/2021及以上版本支持动态数组功能
• 若未定义number_of_fields名称，可替换为COLUMNS(Sheet1!A8:INDEX(Sheet1!8:8, COUNTA(Sheet1!8:8)))，实现完全动态的字段数量识别

内容的提问来源于stack exchange，提问作者Jan Willem