data-cleaning

Data Cleaning Operation Standards

Null/Blank Handling Strategies

Choose a strategy based on user intent; do not decide to delete data on your own:

var ws = Application.ActiveSheet;
var ur = ws.UsedRange;
var lastRow = ur.Row + ur.Rows.Count - 1;
var lastCol = ur.Column + ur.Columns.Count - 1;

// Strategy 1: Delete entirely blank rows for (var r = lastRow; r >= 2; r--) { var empty = true; for (var c = 1; c <= lastCol; c++) { if (ws.Range(CL(c) + r).Value2 !== null && ws.Range(CL(c) + r).Value2 !== "") { empty = false; break; } } if (empty) ws.Range(r + ":" + r).Delete(); }

// Strategy 2: Fill blanks with a specified value for (var r = 2; r <= lastRow; r++) { if (ws.Range(CL(col) + r).Value2 === null || ws.Range(CL(col) + r).Value2 === "") { ws.Range(CL(col) + r).Value2 = fillValue; } }

// Strategy 3: Fill downward with the value from the previous row for (var r = 3; r <= lastRow; r++) { if (ws.Range(CL(col) + r).Value2 === null || ws.Range(CL(col) + r).Value2 === "") { ws.Range(CL(col) + r).Value2 = ws.Range(CL(col) + (r - 1)).Value2; } }

### 去重逻辑

```javascript
// 按指定列去重，保留首次出现
var seen = {};
var delRows = [];
for (var r = 2; r <= lastRow; r++) {
  var key = String(ws.Range(CL(keyCol) + r).Value2);
  if (seen[key]) { delRows.push(r); }
  else { seen[key] = true; }
}
for (var i = delRows.length - 1; i >= 0; i--) {
  ws.Range(delRows[i] + ":" + delRows[i]).Delete();
}

// 多列组合去重
var key = cols.map(function(c) { return String(ws.Range(CL(c) + r).Value2); }).join("||");

Format Normalization

// 日期格式统一（文本 → 标准日期）
var v = String(ws.Range(CL(c) + r).Value2);
// 处理 2024/1/5、2024.1.5、20240105 等变体
var m = v.match(/(\d{4})[\/\.\-]?(\d{1,2})[\/\.\-]?(\d{1,2})/);
if (m) {
  ws.Range(CL(c) + r).Value2 = m[1] + "-" + ("0" + m[2]).slice(-2) + "-" + ("0" + m[3]).slice(-2);
}

// 数字格式统一
ws.Range(CL(c) + "2:" + CL(c) + lastRow).NumberFormat = "#,##0.00";

// 文本去空格/换行
var v = String(ws.Range(CL(c) + r).Value2).replace(/[\s\n\r]+/g, " ").trim();

Outlier Detection

// IQR 方法检测异常值
var vals = [];
for (var r = 2; r <= lastRow; r++) {
  var v = ws.Range(CL(c) + r).Value2;
  if (typeof v === "number") vals.push(v);
}
vals.sort(function(a, b) { return a - b; });
var q1 = vals[Math.floor(vals.length * 0.25)];
var q3 = vals[Math.floor(vals.length * 0.75)];
var iqr = q3 - q1;
var lower = q1 - 1.5 * iqr, upper = q3 + 1.5 * iqr;
// 标记异常值（黄色背景）
for (var r = 2; r <= lastRow; r++) {
  var v = ws.Range(CL(c) + r).Value2;
  if (typeof v === "number" && (v < lower || v > upper)) {
    ws.Range(CL(c) + r).Interior.Color = 0x00AAFF; // 橙黄标记
  }
}

Safety Rules

• Ask the user for a strategy before cleaning; do not decide to delete/modify on your own
• For large-scale operations, it is recommended to preview the results in a new worksheet first
• Null handling must state which strategy was chosen and why