Excel中如何选择统计范围 筛选90%中心数据并匹配关联日期

核心逻辑 #

你需要的居中90%数据筛选本质是对数据集做上下对称的比例截断，完全可以规避IQR方法在重复值过多场景下Q1、Q3相等的问题，执行步骤如下：

• 首先将数值数组和对应的日期数组做索引绑定，禁止单独对数值排序，避免丢失数值与日期的对应关系
• 对绑定后的<数值,日期>数据对按照数值大小做升序排序
• 计算首尾截断量：设数据集总长度为N，截断量为trim_count = int(N * 0.05)，若数据量较小计算得到的截断量为0，可手动设为1避免全部保留
• 截取排序后数据对的[trim_count : N - trim_count]区间，即可得到居中90%的数值，以及对应的关联日期

以你给出的示例验证：示例数组总长度为20，计算得到截断量为1，首尾各截断1个元素，原排序数组为[1,1,1,1,1,1,1,1,2,3,4,5,7,8,9,9,9,9,9,9]，截断后刚好得到你给出的期望结果。

代码实现（Python） #

import numpy as np

# 输入：单设备的数值序列、对应的日期序列
value_arr = np.array([1,1,1,1,1,1,1,1,2,3,4,5,7,8,9,9,9,9,9,9])
date_arr = np.array(["2024-01-01","2024-01-02","2024-01-03","2024-01-04","2024-01-05",
                     "2024-01-06","2024-01-07","2024-01-08","2024-01-09","2024-01-10",
                     "2024-01-11","2024-01-12","2024-01-13","2024-01-14","2024-01-15",
                     "2024-01-16","2024-01-17","2024-01-18","2024-01-19","2024-01-20"])

# 按数值排序并保留索引关联
sorted_idx = np.argsort(value_arr)
sorted_pairs = list(zip(value_arr[sorted_idx], date_arr[sorted_idx]))

# 计算截断量
n = len(sorted_pairs)
trim_cnt = int(n * 0.05)
# 数据量<10时不截断，避免有效数据损失
trim_cnt = max(trim_cnt, 1) if n >= 10 else 0

# 截取居中90%数据
filtered_pairs = sorted_pairs[trim_cnt : n - trim_cnt]
# 拆分得到过滤后的数值和对应日期
filtered_values = [pair[0] for pair in filtered_pairs]
filtered_dates = [pair[1] for pair in filtered_pairs]

多设备批量处理说明 #

• 每行对应单设备数据的场景下，逐行遍历设备数据，对单设备的数值、日期序列单独执行上述逻辑即可，不同设备的处理互不干扰
• 可根据实际数据质量调整截断比例：若异常值占比偏高，可调整为上下各截断10%保留居中80%数据，仅需修改trim_cnt = int(n * 0.1)即可
• 趋势线计算直接使用过滤后的filtered_values和filtered_dates拟合即可，已经排除两端极值的干扰

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