现在的位置: 首页 > 电脑技巧 > 正文

Excel技巧收集

2015年11月25日 电脑技巧 ⁄ 共 1804字 ⁄ 字号 Excel技巧收集已关闭评论 ⁄ 阅读 715 次

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

计算公式

  • 换行符替换
    • 删除文本中不能打印的字符(如换行符):=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范例
    1
    2
    3
    4
    5
    6
    7
    -- 用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的公式。
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      #!/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('"&"','')
      1
      2
      3
      4
      # 执行结果
      ./mkexcel.py "#B1,C1,D1,#I1"
      ="('"&B1&"',"&C1&","&D1&",'"&I1&"'),"

VBA自定义函数

  • Office 2010/2013开启VBA功能菜单:
    • 文件->选项->自定义功能区
    • 从下列位置选择命令(主选项卡)
    • 选中"开发工具",并添加到自定义功能区
  • 提取字符串中的数字:VBA自定义函数
    1
    2
    3
    4
    5
    6
    7
    8
    9
    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

抱歉!评论已关闭.