Excel与SQL数据分析：数据运算全面对比（第三篇）

➕ 算术运算

Excel操作方式：

· 基本运算符：+（加）、-（减）、（乘）、/（除）、^（幂）
· 单元格引用：=A1+B1、=C2D2
· 相对/绝对引用：=$A$1*B1（混合引用）
· 括号优先级：=(A1+B1)*C1
· 示例：

=A2*1.1                    # 增加10%
=(B2-C2)/C2                # 计算增长率
=D2*(1+E2)^F2              # 复利计算

SQL操作方式：

· SELECT中计算：SELECT price * quantity AS total FROM orders
· UPDATE中运算：UPDATE products SET price = price * 1.1 WHERE category = ‘电子’
· 常用算术函数：

-- 基础运算
SELECT 
    salary + bonus AS 总收入,
    salary * 0.08 AS 公积金,
    price / quantity AS 单价
FROM employees;

-- 幂运算
SELECT POWER(2, 3) AS 幂结果;      -- 8
SELECT SQRT(16) AS 平方根;         -- 4

-- 取整运算
SELECT CEILING(15.2) AS 向上取整,   -- 16
       FLOOR(15.8) AS 向下取整,     -- 15
       ROUND(15.456, 2) AS 四舍五入; -- 15.46

⚖️ 比较运算

Excel操作方式：

· 比较运算符：=、<>、>、<、>=、<=
· IF函数应用：=IF(A1>100,“达标”,“未达标”)
· 条件格式：开始 → 条件格式 → 突出显示单元格规则
· 数组比较：=IF(AND(A1:A10>100), “全达标”, “有未达标”)
· 示例：

=B2>C2                         # 简单比较
=IF(A2>=60,"及格","不及格")     # 带输出的比较
=COUNTIF(C:C,">1000")          # 统计满足条件的数量

SQL操作方式：

· WHERE子句过滤：SELECT * FROM products WHERE price > 100
· CASE条件判断：

SELECT product_name,
       CASE 
           WHEN price > 1000 THEN '高价'
           WHEN price > 500 THEN '中价'
           ELSE '低价'
       END AS 价格等级
FROM products;

· 多条件比较：

-- AND/OR组合
SELECT * FROM orders 
WHERE amount > 1000 AND status = '已完成';

-- BETWEEN范围
SELECT * FROM employees 
WHERE salary BETWEEN 5000 AND 10000;

-- IN集合比较
SELECT * FROM products 
WHERE category IN ('电子', '服装', '食品');

-- NULL值比较
SELECT * FROM customers 
WHERE phone IS NOT NULL;

🔀 逻辑运算

Excel操作方式：

· 逻辑函数：AND()、OR()、NOT()、XOR()
· IF嵌套：=IF(AND(A1>0, B1<100), “有效”, “无效”)
· 数组公式：{=AND(A1:A10>0)}（Ctrl+Shift+Enter）
· IFS函数（新版Excel）：=IFS(A1>90,“优秀”,A1>60,“及格”,TRUE,“不及格”)
· 示例：

=AND(B2>0, B2<100)                    # 与运算
=OR(C2="A", C2="B")                   # 或运算
=IF(NOT(ISBLANK(D2)), "有数据", "")   # 非运算
=IFERROR(A2/B2, "除零错误")           # 错误处理

SQL操作方式：

· 布尔运算符：AND、OR、NOT
· WHERE中的逻辑：

SELECT * FROM orders 
WHERE (status = '已完成' OR status = '已发货') 
  AND amount > 500 
  AND NOT payment_method = '货到付款';

· 布尔表达式：

-- 直接返回布尔值
SELECT product_name, price > 100 AS 是否高价
FROM products;

-- 复杂逻辑判断
SELECT *,
       CASE 
           WHEN score >= 90 AND attendance > 0.9 THEN '优秀'
           WHEN score >= 60 OR makeup_exam = true THEN '合格'
           ELSE '不合格'
       END AS 评定结果
FROM students;

📐 数学运算

Excel操作方式：

· 数学函数库：
· 基础：SUM()、AVERAGE()、MIN()、MAX()
· 舍入：ROUND()、ROUNDUP()、ROUNDDOWN()、INT()
· 随机：RAND()、RANDBETWEEN()
· 其他：MOD()、ABS()、SQRT()、LOG()
· 数组运算：{=SUM(A1:A10*B1:B10)}
· 示例：

=SUM(A1:A10)                          # 求和
=AVERAGEIF(B1:B100,">0")              # 条件平均
=ROUND(C2*1.17, 2)                    # 含税价格四舍五入
=MOD(ROW(),2)=0                       # 判断奇偶行

SQL操作方式：

· 聚合函数：SUM()、AVG()、MIN()、MAX()
· 数学函数：

-- 绝对值与符号
SELECT ABS(-15) AS 绝对值, SIGN(-5) AS 符号;

-- 取整函数
SELECT CEILING(15.2), FLOOR(15.8), ROUND(15.456, 2);

-- 取余运算
SELECT MOD(10, 3) AS 余数;           -- 1

-- 指数对数
SELECT EXP(1) AS 自然指数,          -- 2.71828
       LOG(100, 10) AS 对数,        -- 2
       LN(EXP(1)) AS 自然对数;      -- 1

-- 三角函数
SELECT SIN(PI()/6), COS(0), TAN(PI()/4);

-- 随机数
SELECT RAND() AS 随机数,            -- 0-1随机
       FLOOR(RAND()*100) AS 随机整数; -- 0-99随机

🔤 字符串运算

Excel操作方式：

· 文本函数：
· 连接：&、CONCAT()、TEXTJOIN()
· 提取：LEFT()、RIGHT()、MID()
· 查找：FIND()、SEARCH()
· 替换：SUBSTITUTE()、REPLACE()
· 清理：TRIM()、CLEAN()
· 转换：UPPER()、LOWER()、PROPER()
· 示例：

=A2 & " " & B2                        # 连接字符串
=LEFT(C2, 3)                          # 取前3字符
=FIND("@", D2)                        # 查找位置
=SUBSTITUTE(E2, " ", "-")             # 替换空格
=TRIM(F2)                             # 去除首尾空格
=TEXT(G2, "¥#,##0.00")                # 数字转文本格式

SQL操作方式：

· 字符串连接：

-- 标准连接
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

-- 带分隔符连接
SELECT CONCAT_WS(', ', city, state, country) AS 地址
FROM customers;

· 字符串处理函数：

-- 长度与位置
SELECT LENGTH('Hello') AS 长度,          -- 5
       CHAR_LENGTH('你好') AS 字符数,     -- 2
       POSITION('lo' IN 'Hello') AS 位置; -- 4

-- 大小写转换
SELECT UPPER('hello'), LOWER('WORLD'), INITCAP('hello world');

-- 提取子串
SELECT SUBSTRING('Hello World', 7, 5) AS 子串, -- World
       LEFT('Hello', 3),                      -- Hel
       RIGHT('Hello', 3);                     -- llo

-- 替换与移除
SELECT REPLACE('Hello World', 'World', 'SQL') AS 替换后,
       TRIM('  Hello  ') AS 去除空格,
       LTRIM('  Hello'), RTRIM('Hello  ');

-- 填充与重复
SELECT LPAD('123', 5, '0') AS 左填充,    -- 00123
       RPAD('123', 5, '*') AS 右填充,    -- 123**
       REPEAT('Ha', 3) AS 重复;          -- HaHaHa

-- 反转与格式化
SELECT REVERSE('SQL') AS 反转,           -- LQS
       FORMAT(1234567.89, 2) AS 格式化;  -- 1,234,567.89

📊 聚合运算

Excel操作方式：

· 基础聚合函数：SUM()、AVERAGE()、COUNT()、MAX()、MIN()
· 条件聚合：
· SUMIF()、SUMIFS()
· COUNTIF()、COUNTIFS()
· AVERAGEIF()、AVERAGEIFS()
· 分类汇总：数据 → 分类汇总
· 数据透视表：插入 → 数据透视表（最强大的聚合工具）
· 示例：

=SUMIF(C:C, ">=1000", D:D)              # 条件求和
=AVERAGEIFS(E:E, F:F, "北京", G:G, ">0") # 多条件平均
=COUNTIF(H:H, "<>已完成")                # 计数非"已完成"
=MAX(IF(I:I="A", J:J))                   # 数组公式求最大值

SQL操作方式：

· GROUP BY聚合：

-- 基本分组
SELECT department, 
       COUNT(*) AS 人数,
       AVG(salary) AS 平均工资,
       SUM(salary) AS 总工资
FROM employees
GROUP BY department;

-- 多级分组
SELECT department, job_title,
       COUNT(*) AS 人数,
       MAX(salary) AS 最高薪
FROM employees
GROUP BY department, job_title;

· HAVING子句：

SELECT category, 
       AVG(price) AS 平均价格,
       COUNT(*) AS 商品数量
FROM products
GROUP BY category
HAVING AVG(price) > 100 
   AND COUNT(*) > 5;

· 窗口函数（高级聚合）：

-- 排名与排序
SELECT name, score,
       ROW_NUMBER() OVER(ORDER BY score DESC) AS 排名,
       RANK() OVER(ORDER BY score DESC) AS 等级排名,
       DENSE_RANK() OVER(ORDER BY score DESC) AS 密集排名;

-- 分区聚合
SELECT department, name, salary,
       AVG(salary) OVER(PARTITION BY department) AS 部门平均工资,
       SUM(salary) OVER(PARTITION BY department) AS 部门总工资,
       salary - AVG(salary) OVER(PARTITION BY department) AS 与平均差值;

-- 累计计算
SELECT order_date, amount,
       SUM(amount) OVER(ORDER BY order_date) AS 累计销售额,
       AVG(amount) OVER(ORDER BY order_date 
           ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS 近7日移动平均;