标签归档：excel

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

发表于由
回复

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

直接上公式示例

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

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

根据相对位置定位一个单元格或一个区域

函数：OFFSET(reference, rows, cols, [height], [width])
reference是一个单元格，相对于它、偏移指定行数rows和列数cols，然后返回一个高height、宽width的区域。 height, width 省略默认为1，即返回一个单元格。rows, cols 可以为负数，表示反方向。

所以，非常适用于一组行数不确定的区域的计算。比如上例截图里的每组是周，固定为7行，如果是月，那就可以先计算一下行数，再通过 OFFSET 函数计算出区域再做汇总计算，避免痛苦地更新公式。

 

多条件查找

本方法来源于：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