第二章-数据理论
第二章-数据理论
1. 基础数据理论概念
- 连续和分类
- 缺失和默认（缺省）
- 重复
- 差异过滤
- 索引和唯一键
- 升降序
- 时间格式
- 精度
2. 相关实际操作
- 连续加和SUM
- 分类计数COUNT
- 重复去重
- 过滤筛选
- 索引排序
- 升降序
- 时间格式转换
- 精度损失
3. 参考知识
时间格式和时间戳
- 内置分类 • 日期：yyyy-mm-dd、mm/dd/yyyy、[$-404]e/m/d（中文农历）…… • 时间：hh:mm:ss、hh:mm AM/PM、[h]:mm:ss（超24h） • 自定义：用占位符任意拼 y 年 | m 月/分（上下文决定） | d 日 | h 时 | m 分 | s 秒 | 0 强制补零 例：yyyymmddhhmmss → 20260110220500
- 快捷键 Ctrl+Shift+# 强制转为“yyyy-mm-dd” Ctrl+Shift+@ 转为“hh:mm AM/PM”
- 格式≠值：把2026/1/10 22:05 设成“;;;”（三个分号）单元格看似空白，F2仍能看到完整值。
二、真正的数据类型（Type）：
- 数字（Double）——Excel内部唯一合法“时间” 整数部分＝自1900-01-01起的天数；小数部分＝24h的比例。 2026/1/10 22:05 = 45271.921875
- 文本（String）——肉眼看着是日期，其实是“炸弹” 函数、透视表、Power Query、图表都会直接无视。
Unix时间戳（秒/毫秒）：定义：自1970-01-01 00:00:00 UTC起的秒数。 Excel公式（秒→Excel值）：=A1/86400 + DATE(1970,1,1) 反向（Excel值→秒）=(A1-DATE(1970,1,1))*86400
最易踩的10个坑（防坑清单）
- 1900闰年Bug：Excel把1900-02-29当成存在的一天，与SQL/Unix对不上。
- 两位年份：2026年输入“1/1/30”会被当成1930；用yyyy强行补全。
- 文本混排：SUM、COUNT直接跳过文本日期；用TYPE或ISNUMBER做体检。
- 区域设置：打开德文电脑，“3.1.26”是3-Jan，不是1-Mar；用Power Query“使用区域”显式指定。
- 超24小时：[h]:mm:ss 才能显示25:00:00，否则自动回0。
- 负时间：默认显示“####”；文件选项→“使用1904日期系统”可显示负时间，但与Windows时间戳错位1462天。
- 秒以下精度：单元格最多显示1/1000秒，但VBA可存1/10000000秒；金融高频数据需用Decimal或文本。
- 夏令时：Excel不懂时区；跨DST加减1小时要手动。
- 外部数据：CSV“2026-01-10T22:05:00Z”进来先变文本；PQ里“更改类型→使用区域→UTC”再转本地。
- 版本差异：Mac 1904系统起点比Win多1462天；跨平台发模板一定先测。
精度计算和进位
| 规则 | 口语描述 | Excel 函数 | 典型参数示例 | 备注 |
|---|---|---|---|---|
| 四舍五入 | ≥5 进位 | ROUND | =ROUND(A1,2) | 最常用 |
| 四舍六入五成双（银行家） | 5 看前一位奇偶，偶数舍奇数进 | VBA 自定义 / Power Query | —— | 统计学减少偏差 |
| 向上舍入 | 总是往绝对值大方向走 | ROUNDUP | =ROUNDUP(1.23,0) → 2 | 运费、阶梯价 |
| 向下舍入 | 总是往绝对值小方向走 | ROUNDDOWN | =ROUNDDOWN(-1.89,0) → -1 | 库存扣减 |
| 向零舍入 | 去掉小数，正负对称 | TRUNC | =TRUNC(-9.9) → -9 | 等同于 ROUNDDOWN 但语法更简 |
| 倍数舍入 | 舍入到指定倍数 | MROUND | =MROUND(23,5) → 20 | 包装箱、卡车吨位 |
| ceiling 型倍数 | 向上到倍数 | CEILING | =CEILING(23,5) → 25 | 注意：CEILING.MATH 已取代 CEILING（2021+） |
| floor 型倍数 | 向下到倍数 | FLOOR | =FLOOR(23,5) → 20 | 同上，FLOOR.MATH |