excel公式实现:根据当前单元格偏移位置引用计算、多条件查找

根据当前单元格偏移位置引用计算

直接上公式示例

=INDIRECT(ADDRESS(ROW()-1,COLUMN()))

作用:引用当前单元格上一行同列的值。
原理:自己查excel自带帮助的公式说明。
特点:可以无脑的将该公式粘贴到任意位置,即做相对引用,不受表格其他内容、位置的影响。
使用场景示例:如下图所示,尾行是计算周变化值/比例。选中格事实就是=B706-B698。每周会插入8行数据。使用了上面偏移位置方案,就不用每次更新下面公式,省心且不出错。

 

多条件查找

本方法来源于:https://www.zhihu.com/question/41820039/answer/93329442 在此表示感谢。

通用公式

=lookup(1,0/((条件1)*(条件2)*(条件3)),结果区域)

原理:参看出处说明

另外还有更多方法

Microsoft Excel的坑:数据处理方面

首先一个不可否认的事实:Microsoft Excel是个使用简便、功能丰富的数据处理工具;但这不能掩饰它本身的不少缺陷,本文只讲其在数据转换时的陷阱,就是“坑”。

脑残的转义约定:双引号转义、不转义换行符

csv是常用的数据交换格式,但excel生成的csv文件却有几个脑残的约定(大概是微软自己的历史包袱所致),通常excel使用双引号括起来字段内容,如果字段内容中有双引号,则使用 \" 代替;还有其他特殊符号,也会做相应转义。所以,使用excel转换数据(如导入到MySQL)时,要注意处理这些问题。

换行符转义。可以事先在excel中替换,excel查找格里先清空格中内容,按几次Backspace再按几次Delete,确保真正的清空。按住Alt键不松开,输入数字10,此时格里没有任何显示,然后在替换格中输入\n,全部替换,即完成换行符的转义。

双引号转义。这个问题有点恶心。1)如果对内容一致性要求不高,可以将双引号完全删除(查找替换成空字符串)。2)将双引号替换成一个其他字符串(比如~^这类特殊字符、或者~~这样的组合字符串),事先在excel文件里查找、以确保该字符串不会在内容中出现。保存成csv后,再将该保护字符串替换成\",完成转义替换。

 Excel表导入MySQL的几个可选方案

导数据前一般需要事先建好目标表,目标表的字段长度可以适当大一此,避免导入失败。或者通过事先计算每个字段的最大长度,比如用awk 参考。下面是几种导入方法,按操作难度从易到难排列

  • 使用第三方工具(如navicat 等)直接导入,但不少时候会报错,而且原因不明。
  • excel转为csv,通过phpMyAdmin或Load Data等导入MySQL。
  • excel公式构造一系列insert 语句,拿到mysql里执行(粘贴到客户端里运行,或保存到文件让mysql加载)。
  • excel导入到ms access(微软自家产品内部有良好兼容性),再使用navicat等第三方工具导到mysql。
  • 自己写脚本处理。

后面只是几张图,随便参考一下吧。

navicat导excel到mysql报错

navicat导excel到mysql报错

vlookup函数匹配到空格,但返回结果却是 0

解决方案:vlookup函数结果拼接上个空格,比如  =vlookup(C1,sheet2!A:F,5,0)&""

EOF

excel表格导入mysql方法(手工导入)

这不是最好的办法,但对于只导一次的数据,却是一个有效的方法,虽然非常麻烦。

microsoft office/excel 在日常的应用中,还是相当广泛,但它不是个好工具。把excel导入mysql表,是一个无法忍受的麻烦工作;尤其是表格里带大字段的列,使用phpMyAdmin导入经常是失败的。如果你也遇到这种问题,可以尝试以下方法。

1. 在excel里选中所有数据列,执行替换,把 ' 替换成 \' (把单引号替换成斜杠加单引号,即mysql中的单引号转义)

2. 在excel表格里插入一列,根据mysql表结构,写个excel公式,其实只是个字符串拼接操作,目的是构造一个mysql插入语句,形式大至如下

="insert into `article`(cat_id,title,content,add_time) values('"&D3&"','"&A3&"','"&B3&"',1330516016);"

3. 复制该列,并作为新列粘贴值到旁边(选择性粘贴);或者粘贴到新表格里也可以。

4. 除了刚才的新数据列,把其它列全部删掉,另存为unicode文本格式(实际是utf-16的csv)。

5. 使用记事本打/或其它纯文本编辑工具打开该文件,另存为utf-8编码。(windows下所谓的unicode格式实际是utf-16,这是M$在误导大家)

6. 使用editplus等文本编辑工具,打开该utf-8格式的文件,查找替换,把 ""  替换成 " (两个连续单引号替换成一个单引号); 因为M$的CSV使用两个双引号来转义双引号,这是个非常愚蠢的约定!MS/windows很多地方都是这样愚蠢的做双字符转义!而标准的csv使用斜杠做特殊字符转义的。

7. 把 "\n\" (行尾双引号与下一行的双引号) 替换成 \n (亦即把行首及行尾的双引号删掉)

8. 把文件最开头及文件最末尾的双引号删掉。

9. 现在就是可以在mysql里执行的insert插入语句了,拿到mysql里执行吧。如果数据量大,推荐使用mysql命令行下导入,连接时最好加上参数 --default-character-set=utf8 ,这样不容易出现乱码。

mysql -uroot -p --default-character-set=utf8