Excel技巧收集

计算公式
Excel中用公式生成SQL
VBA自定义函数

Excel表格是一个伪装成办公软件的的小型的数据库系统，拥有最快捷、易用的数据编辑和处理界面，无需专业知识、人人均可使用，是日常数据处理、系统数据配置的最佳选择。

ExcelEXCEL财务函数IRR、XIRR、MIRR
Excel文件尺寸压缩
- Excel 2007以后版本，采用xlsx格式是自带压缩的，需要有上百万条记录才能达到10MB、几十MB的大小，而我们通常使用中一般就是几千上万条数据而已。
- 表中的空行、空列存在问题时(设置了格式等)，会造成文件大小超过实际数据量的大小，有时会文件大小会达到10倍。
- 删除所有空行、空列后，可将文件变回正常大小，存储占用小、传输更快，打开也不会卡顿。
```
操作方法：
1.选中最后一列，按Crrl+Shift+右方向键，选中所有右边的列，删除。
2.选中最后一行，按Ctrl+Shift+下方向键，选中所有下面的行，删除。
3.保存，检测文件是否变小，再对其他工作表做同样操作。
```

计算公式

换行符替换
- 删除文本中不能打印的字符(如换行符):=CLEAN(A1)
- 替换单元格中的换行符：=SUBSTITUTE(SUBSTITUTE(A4,CHAR(10),"<BR>"),CHAR(13),"")
获取指定单元格引用名:
- 相对引用：=CELL("address",A1)
- 绝对应用：=SUBSTITUTE(CELL("address",A1),"$","")

Excel中用公式生成SQL

将表格转换为SQL INSERT语句，方便运营、策划人员在Excel中进行数据配置后，IT人员生成SQL导入数据库。

范例表格：
- "单元格"行用于定义需要作为字段导入的列名称，不需要导入的列不填写列名称，字符型字段使用#前缀，方便后面的mkexcel.py工具生成excel公式。
- "字段名"行用于生成INSERT语句的字段列表。
- SQL公式:="('"&B1&"',"&C1&","&D1&",'"&I1&"'),"
- "职业属性"内容是用公式生成的:
```
=$E$2&":"&E13&","&$F$2&":"&F13&","&$G$2&":"&G13&","&$H$2&":"&H13
```

说明	所属职业	职业代码	等级	生命值上限	外功攻击	外功防御	内功攻击	内功防御	职业属性	SQL公式
单元格		#B1	C1	D1					#I1
字段名		c	level	hp_limit	EA	ED	SA	SD	attrs	=...
数据行	攻击型	A	1	36	7.6	3	11.5	2.8	EA:7.6,ED:3,SA:11.5,SD:2.8	=...
数据行	攻击型	A	2	59	15.2	6	23	5.6	EA:15.2,ED:6,SA:23,SD:5.6	=...

SQL范例

-- 用Excel生成的SQL列填充到字段列表、数据行处，将最末的,号改为;号，就可以在MySQL中执行数据插入了。
TRUNCATE TABLE disciple_level;
INSERT INTO `disciple_level` 
(`c`,`level`,`hp_limit`,`attrs`,`acupoints`)
VALUES
('A',1,36,'EA:7.6,ED:3,SA:11.5,SD:2.8',''),
('A',2,59,'EA:15.2,ED:6,SA:23,SD:5.6');

Excel公式生成工具mkexcel.py，替代手工编写Excel公式

命令行参数：传入需要导入的列名称，逗号分隔，字符型字段使用#前缀

执行结果为Excel中生成SQL的公式。

#!/usr/bin/env python
# -*- coding=utf8 -*-

import sys

if len(sys.argv)<2:
    print """eg: 
    Excel公式生成(#开头为字符串型字段，其余为数值型)
    %s "#B1,C1,D1,#I1"
    
    """%(sys.argv[0])
    sys.exit(0)

fs=sys.argv[1]
_s = [('"\'"&%s&"\'"'%f[1:] if f.startswith('#') else f) for f in fs.split(',')]
print ('="("&%s&"),"'%'&","&'.join(_s)).replace('"&"','')

# 执行结果
./mkexcel.py "#B1,C1,D1,#I1"

="('"&B1&"',"&C1&","&D1&",'"&I1&"'),"

VBA自定义函数

Office 2010/2013开启VBA功能菜单：
- 文件->选项->自定义功能区
- 从下列位置选择命令(主选项卡)
- 选中"开发工具"，并添加到自定义功能区

提取字符串中的数字:VBA自定义函数

Function tqsz(rng As Range)
    Dim R As Object
    Set R = CreateObject("VBSCRIPT.REGEXP")
    R.Pattern = "\D"
    R.IgnoreCase = True
    R.Global = True
    tqsz = R.Replace(rng.Value, "")
    Set R = Nothing
End Function