分享学习经验、使用技巧及收获感悟帖

yhyppboy

来到EH大家庭一个月了，收获很多，感触很大。对这么多无私、慷慨的EH朋友表示深深的感谢。在这，我没有楼上各位的水平，没有成果可以展示，但是我想借用这个平台汇报下我的收获及对帮助过的朋友以示感激之情。
1、数据透视：学会了数据透视表和数据透视图的简单应用，对于现在的数据管理很是有帮助，大大提高了工作效率。想想先前那样一个表格一个表格找的日子真是辛苦。
2、函数：知道了几个简单的函数，sumif、counta、countif等，对VLOOKUP函数有了更多更深的认识。
3、图表：知道了饼图是如何添加照片，对图表颜色的搭配有所进步，更多地了解到了图表的类型及大致的使用方法。
4、资料下载：在这里我下载了很多实用、漂亮的模版。
5、索取：我目前还处于只进不出的阶段-----没法去帮助其他朋友解决问题。可在这我得到了很多朋友的帮助，对工作效率的提高、工作的改进有了非常大的帮助。
下面是我的索取，对帮助过我的朋友，再一次表示感谢。
1、EXCEL中的饼图上是如何搞上照片的？  2009-11-29 17:29

2、哪些因素会影响EXCEL的运行速度  2009-11-29 08:38

3、如果一行单元格已经是用公式引用过来的了，如何计算个数？（已解决，谢谢）  2009-11-29 11:46

4、一个单元格中的数字有多个空格怎么处理（问题已经解决，谢谢）  2009-11-27 23:37

5、关于时间的设置和人员加班时间的汇总问题  2009-11-26 22:18

6、如何在某列中直接插入我想要的列数  2009-11-26 10:38

7、请问数据透视表生成的表格数据能否进行排序？（问题已经解决，谢谢penghaifeng）  2009-11-24 15:53

8、将“20091119-1120”变成“20091119-20091120”？（问题已解决。谢谢）  2009-11-17 11:39

9、哪位能分享下人员培训效果评价表或者是培训满意度调查样表不？谢谢！  2009-11-5 11:50

10、如何用IF函数计算这样的公式，谢谢！  2009-10-30 11:55

11、如何实现用柱状图方向的旋转（在用数据透视表的情况）  2009-10-27 11:37

12、如何实现同一个格式的多份表格用公式进行汇总，谢谢！  2009-10-22 17:49

[ 本帖最后由 yhyppboy 于 2009-11-29 19:57 编辑 ]

wjzwang

系统里的"Wingdings"字体可以根据不同的字母不同的图形,其中大写"O"就可以显示成红旗(当然要把字体颜色设成红色了)
严格说来,这已经不算是EXCEL的知识了,只能算是电脑知识
这可以用作在条件格式里当单元格在某一值时,设定成"Wingdings"字体以显示相应的图形,当然用在数据透视表里也行

monvzhilei

这样经常来分享经验。想不成高手都困难。

wjzwang

昨天看到http://club.excelhome.net/viewthread.php?tid=516279&page=1#pid3400267，JSSY版主是通过SQL语句来实现增加所需的合计项：
数据源如下：


想要得到的效果


利用透视表得到类似的效果


SQL语句如下：

select 单位,专业类别,专业,学历,需求人数,计划与招聘  from [数据库$] union all select  单位,  专业类别,专业,计划与招聘,需求人数,计划与招聘  from [数据库$] union all select  "汇总" as 单位,  专业类别,专业,学历,需求人数,计划与招聘  from [数据库$] union all select  "汇总" as 单位,  专业类别,专业,计划与招聘,需求人数,计划与招聘  from [数据库$]

此SQL是由4个SELECT语句组成，如上不同颜色所示，它们都是以同一工作表“数据库”为数据源，同时也以第一个SELECT为标准生成相应字段内容的透视表数据源。

其中第二个SELECT中的第四个字段“计划与招聘”是以计划与招聘里的内容代入到“学历”字段里去，可理解成在最初数据的学历里增加“计划”和“招聘”两个“学历”，从而在生成透视表时能够得到“计划与招聘”的数据。“汇总" as 单位”同理，把“汇总”两个字作为新的“单位”内容，从而得到汇总内容。

值得注意的是，此例子中，“数据库”的原数据共有90行，但经过4个SELECT组合后，得到新的数据共有90*4＝360行。可以通过双击透视表右下角单元格得到明细查看。

由此看来，当使用多个SELECT把不同区域的数据连接起来时，它们的所提取的“字段”的顺序应该是一致的，否则会出现数据错误。


[ 本帖最后由 wjzwang 于 2009-12-24 09:50 编辑 ]

wjzwang

哦,天啊,刚给老婆分担完家务活,偷闲上来转一下,发现这个帖被willin2000版加分了, 激动得想哭,谢谢willin2000版,我会继续把收获与大家分享的

wjzwang

之前发一帖http://club.excelhome.net/thread-519405-1-1.html,关于数据透视表连接字符串问题的问题
是在EXCEL 2007或以上的版本里导入文件所生成的SQL数据透视表,在没装EXCEL 2007或以上版本的机器上不能刷新的问题,通过改变"连接字符串"即可解决,当然,前题是确保连接里的文件地址要正确,如附件中的地址是"桌面"

[ 本帖最后由 wjzwang 于 2009-12-31 17:23 编辑 ]

wjzwang

昨天又一次中招了,在EXCLE文件里自动生成RESULTS.XLS,是一个"宏病毒",它让每个打开的EXCEL文件生成一个RESULTS.XLS的宏,很讨厌的,

论坛上的解决方法如下:

第一步RESULTS这个工作簿在

C:\Documents and Settings\Administrator\Application Data\Microsoft\Excel\XLSTART中将此文件删除。

C:\Documents and Settings\Administrator\Application Data\Microsoft\Excel中的Excel11,Excel12文件也删除。

第二步在代码框中移除此工作簿的VBA代码

特别是Excel11,Excel12这两个文件,我一开始忽略了,后来删除了就没事了,至于"第二步在代码框中移除此工作簿的VBA代码"没找到,呵呵

注:C:\Documents and Settings\Administrator\Application Data\Microsoft\Excel\XLSTART中的"Administrator"应该是你系统的用户名,所以如果不是以Administrator登陆的话,要换成其它相应路径了.


注意:vista和WIN7的用户目录结构和XP的不一样了，不是放在C:\Documents and Settings\(这个文件夹在VISTA下已经无用,只是为了兼容某些程序而存在)

而重新被定位在 C:\Users\***\AppData\Roaming\  这个位置

[ 本帖最后由 wjzwang 于 2010-2-7 09:37 编辑 ]

wjzwang

最近有人问我学函数要怎么学???

       这个问题我也回答不了,不外乎多看帮助,多练习吧,呵呵,记得我第一个在EH查的函数是MID,当时我是要提取身份证里的出生日期,有了目标才能有方向去学习.到现在为止,自认为函数还勉强可以应该日常的工作,我觉得掌握以下的这几十个函数并加以灵活运用,基本上就可以了.

       在这里只列出函数名只是给大家一个学习的方向而已,例子就请自己看帮助吧.希望对大家有用.

SUM	求和
SUMIF	单条件求和,可配合通配符使用,如=SUMIF(型号区域,"*除湿机*",数量区域),即求所以包含"除湿机"的数量)
SUMPRODUCT	这个乘积函数,不能不精,可以当作多条件求和函数使用,原格式为SUMPRODUCT(数据1,数据2,数据3…),可变为SUMPRODUCT((数据1&条件1)*(数据2条件2)*数据3),或SUMPRODUCT((数据1&条件1)*(数据2条件2),数据3),当"数据3"中有非数值型数据时,用后一种格式
MAX	取数据区域中的最大值
MIN	取数据区域中的最小值
AVERAGE	取数据区域中的平均值
COUNT	统计数据区域中的数字个数
COUNTA	统计数据区域中的非空个数
COUNTIF	统计符合指定条件的数据区域的个数,可配合通配符使用,如=COUNTIF(A21:A27,"*吉德*"),即求包含"吉德"的个数
SUBTOTAL	这个用在筛选状态下使用,设置不同的参数可统计不同类型的数值,常用的有"最小值","最大值","求和","平均值","记录数","非空记录数"等
MATCH	在指定的范围里找到某个数的位置,常作为INDEX或OFFSET之类的查找函数的参数使用,可与通配符配合使用,如=MATCH("*B17S7-S*",数组,0),在数组中查找"B17S7-S"的位置
LOOKUP
VLOOKUP	多用于类似按型号提单价之类的情况,其中LOOKUP()的数组要注意升序排序,而VLOOKUP,HLOOKUP注意最后一个参数"rang_lookup",一般0代替FALSE(精确查找),1代替TURE(模糊查找)
HLOOKUP
INDEX	根据给出的偏移量,在一数组中找到指定的数据
OFFSET	根据给出的偏移量,以一指定的数据为参照位置,找到指定的数据
SMALL	最第N个最小值
LARGE	最第N个最大值
MID	在一字符串中根据起始位置和所需长度提取内容,典型例子就是从身份证号码中提取出年年月
LEFT	从左边或右边开始,提取指定长度的内容
RIGHT	从左边或右边开始,提取指定长度的内容
TEXT	把数字转换成指定格式的文本显示,如把"1"显示成"001"是TEXT(1,"000"),在某些情况下,还可以作为IF的替代品使用
SUBSTITUTE	替换指定字符为其它新的字符,如=SUBSTITUTE("我是肉包","肉包","肉松包"),把"肉包"替换成"肉松包",同时也可以配合MID来取数,如=--MID(SUBSTITUTE("10*20*30","*",REPT(" ",10)),{1;10;20},10),结果为{10;20;30}
REPLACE	和SUBSTITUTE类似
FIND	在字符串中查找指定字符的位置
SEARCH	在字符串中查找指定字符的位置
DATE	日期函数
YEAR	日期函数
MONTH	日期函数
DAY	日期函数
IF	条件语句,正常情况下最多嵌套7层使用,如遇类似根据不同销售额定义不同提成系数情况时,可改用VLOOKUP
ISNUMBER	判断数据是否是数值,常与IF配合使用,用于排错
ISERROR	判断函数是否出错,常与IF配合使用,用于排错
ROW	返回行号,常用于生成自然序列
COLUMN	返回列号,常用于生成自然序列
INDIRECT	返回指用位置的数据,如INDIRECT("A1"),返回A1单元格的数据,可与ROW配合使用,生成动态的自然序列,如ROW(INDIRECT("A1:A"&动态变量))

xiangzi728

谢谢wjwang整理，都是非常不错的经验！
收藏学习了~~

XUZHANCHENG

有时候是觉得该整理一下了