Excel新增数据后SORT(UNIQUE())排序错误且不遵循单元格格式

问题核心成因 #

1. 公式不遵循单元格格式的原因

Excel的单元格格式属于显示层规则，仅改变数据的展示效果，不会修改单元格底层存储的实际数据类型：

• 你批量修改列格式为「文本」后，没有触发已输入数据的存储类型更新，只有点击单元格进入编辑状态再确认，才会将已有数据的存储类型同步为当前设置的单元格格式，这就是你点击1000单元格后才出现数值型、文本型1000并存的原因。
• SORT函数排序、UNIQUE函数去重都是基于底层存储的实际数据类型计算，Excel默认排序规则为「数值 < 文本」，所以数值型的7000、1000会排在所有文本型编码前面，同时数值和文本类型的相同编码会被UNIQUE判定为两个不同值，出现重复条目。

2. TEXT函数输出异常的原因

你使用的格式码"Text"不符合Excel TEXT函数的格式规范：
格式码中的e会被Excel识别为「年份」格式符、t会被识别为「时间」格式符，不会原样输出，才会出现T1902xt这类异常转换结果。如果要将数值统一转换为文本类型，格式码应该使用"0"（固定位数数字转文本）或者"@"（通用文本格式）。

解决方法 #

• 方案1：统一原始列的数据类型
  选中B3:B43整列，先设置单元格格式为「文本」，再点击顶部菜单栏「数据」选项卡下的「分列」功能，不需要修改任何参数直接点击「完成」，即可强制将整列所有已输入数据的存储类型转换为文本，之后新增的数据也会自动按文本格式存储，原有公式=SORT(UNIQUE(B3:B43))即可正常运行。
• 方案2：修改公式统一转换类型，不改动原始列
  将公式调整为=SORT(UNIQUE(TEXT(B3:B43,"@")))，即可在计算时自动将所有编码统一转换为文本类型，避免因类型混杂导致的排序、去重错误。

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