Excel高效商务运用技巧

Excel高效商务运用技巧

JimmyHou课程大纲ClicktoaddTitle1基础知识1ClicktoaddTitle2公式及函数2ClicktoaddTitle2数据分析3EXCEL用户水平的5个层次专家高级用户中级用户初级用户新手Excel技能+专业知识+行业经验数组+VBA数据透视表+常用公式基本掌握常用功能刚开始接触2010基本界面基础知识工作窗口的视图控制知识点练习要求工作簿的多窗口显示增加一个窗口，对两个窗口进行平铺、水平、垂直、层叠排放并排比较比较两张不同的工作表，并使他们同步滚动拆分窗口固定显示表头标题行或标题列冻结窗口固定显示前两列自定义功能区在功能区中添加照相机按钮基础知识单元格和区域知识点练习要求多表区域的选择在sheet1/sheet2/sheet3的A1:D1分别输入姓名、性别、年龄、籍贯，并将文字标为蓝色，背景选择淡黄色。特殊区域的选择找出sheet4中的空值，并将空值隐藏。将非空的数据复制到sheet5中，并使之连续。选择任意区域，为它命名。基础知识输入和编辑数据知识点练习要求强行换行（Alt+Enter）将B10单元格强行换行自动填充以步长为2，自动填充编号设置自定义序列将表中储蓄所添加到自定义

序列中去，便于日后工作选择性粘贴对行列进行转置基础知识数据处理与保护知识点练习要求查找与替换将D列的蓝色字体替换为黑色查找功能CTRL+F保护单元格对SHEET数据进行保护设文件设置打开密码基础知识数据有效性知识点练习要求限制输入内容日期限制为2010年的某一天，时间点限制为8：00-17：00，客户数量限制为大于0的整数制作下拉菜单网点处制作下拉菜单基础知识条件格式知识点练习要求数据条在总分处设置数据条色阶排名处按照百分比设置渐变色阶图标对负债、资产、表外设置红黄绿灯图标。对名次变化设置上下箭头图标。使用公式及函数函数基础使用公式及函数认识公式公式中的运算符辨别&:/,的区别使用单元格名称（对I33进行定义）单元格引用相对引用、绝对引用公式中的运算符:(冒号)区域运算符，对两个引用之间，包括两个引用在内的所有单元格进行引用B5:B15，(逗号)联合操作符将多个引用合并为一个引用SUM(B5:B15,D5:D15)&

将两个文本值连接起来产生一个连续的文本值公式

引用概念①相对引用公式中的相对单元格引用（例如A1）是基于包含公式和单元格引用的单元格的相对位置。如果公式所在单元格的位置改变，引用也随之改变。如果多行或多列地复制公式，引用会自动调整。默认情况下，新公式使用相对引用。例如，如果将单元格B2中的相对引用复制到单元格B3，将自动从=A1调整到=A2。②绝对引用单元格中的绝对单元格引用（例如＄A＄1）总是在指定位置引用单元格。如果公式所在单元格的位置改变，绝对引用保持不变。如果多行或多列地复制公式，绝对引用将不作调整。默认情况下，新公式使用相对引用，需要将它们转换为绝对引用。例如，如果将单元格B2中的绝对引用复制到单元格B3，则在两个单元格中一样，都是＄A＄1。③混合引用混合引用具有绝对列和相对行，或是绝对行和相对列。绝对引用列采用＄A1、＄B1等形式。绝对引用行采用A＄1、B＄1等形式。如果公式所在单元格的位置改变，则相对引用改变，而绝对引用不变。如果多行或多列地复制公式，相对引用自动调整，而绝对引用不作调整。例如，如果将一个混合引用从A2复制到B3，它将从=A＄1调整到=B＄1。相对、绝对引用实例如C1单元格有公式=A1+$B$1

然后你把光标指向C1单元格的右下角，当光标变成一个黑实小十字时，按左键向下拖动几行，如拖到C10，放开。

从C2开始公式已经变化了变成了这样：

=A2+$B$1

=A3+$B$1

=A4+$B$1

=A5+$B$1

=A6+$B$1

=A7+$B$1

=A8+$B$1

…………

看明白了吗？不变的是绝对引用，变化的是相对引用。相对、绝对引用实例使用管公式宣及函钟数函数分类数学与三角加减乘除、乘方、四舍五入等统计方差、标准差、平均数、中值、众数等。日期与时间年月日的显示、日期序列值的相互转化、当前日期时间逻辑条件判断查找与引用从表格和数组中提取指定行或列中的数值文本大小写、全角半角转换、指定位置提取某些字符等财务计算贷款支付额、存款到期额、利率、余额递减折旧费信息检测单元格内包含的数据类型、求错误值种类的类型数据库从函数清单或数据库中提取符合给定条件数据的函数工程工程系函数、贝塞尔函数、复数计算等。使用赵公式腐及函拦数搜索稻函数使用祥公式赚及函民数公式旗审核使用论公式搏及函烟数文本函数LEFT从文本符字符串的第一个字符开始返回指定个数的字符。MID从文本符字符串制定的起始位置开始返回指定个数的字符。RIGHT从文本符字符串的最后一个字符开始返回指定个数的字符。RMB四舍五入数值并转换为带有￥符号的文本DOLLAR四舍五入数值并转换为带有$符号的文本NUMBERSTRING将数值替换为大写汉字VALUE将文本转换为数值REPT按照指定的次数重复显示文本使用父公式乡丰及函崭数AB1数据

2SalePrice

3瑞典

公式说明（结果）=LEFT(A2,4)第一个字符串中的前四个字符(Sale)LEFT(text,num_chars)根据所指定的字符数，LEFT返回文本字符串中第一个字符或前几个字符。Text

是包含要提取的字符的文本字符串。Num_chars

指定要由LEFT提取的字符的数量。使用扇公式宁及函沈数AB1数据

2FluidFlow

公式说明（结果）=MID(A2,1,5)上面字符串中的5个字符，从第一个字符开始(Fluid)=MID(A2,7,20)上面字符串中的20个字符，从第七个字符开始(Flow)MID(text,start_num,num_chars)MID返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。Text

是包含要提取字符的文本字符串。Start_num

是文本中要提取的第一个字符的位置。文本中第一个字符的start_num为1，以此类推。Num_chars

指定希望MID从文本中返回字符的个数。使用盯公式敢及函堡数AB1数据

21234.567

公式说明（结果）=DOLLAR(A2,2)或RMB(A2,2)以货币格式及小数点右边2位数字的形式显示第一个数（￥1,234.57）=DOLLAR(A2,-2)或RMB(A2,-2)以货币格式及小数点左边2位数字的形式显示第一个数(￥1,200)DOLLAR或RMB(number,decimals)依照货币格式将小数四舍五入到指定的位数并转换成文本。Number

为数字、包含数字的单元格引用，或是计算结果为数字的公式。Decimals

为十进制数的小数位数。如果decimals为负数，则number在小数点左侧进行舍入。如果省略decimals，则假设其值为2。使用葵公式宜及函体数AB1公式说明（结果）2=REPT("*-",3)字符串显示3次(*-*-*-)3=REPT("-",10)短划线显示10次(----------)REPT(text,number_times)按照给定的次数重复显示文本。可以通过函数REPT来不断地重复显示某一文本字符串，对单元格进行填充。Text

需要重复显示的文本。Number_times

是指定文本重复次数的正数。使用理公式幻玉及函有数时间与日期函数WEEKDAY返回某日期为星期几（星期几）WEEKNUM返回日期序列号代表一年中的第几周DATE以年月日表示日期的序列号WORKDAY求指定工作日后的日期序列号（完成日期）NETWORKDAYS计算起始日和结束日期的天数（除去节假日）DATEDIF用指定单位计算两个日期之间的天数使用辽公式爆及函百数AB1日期说明22008/10/1起始日期3151完成所需天数42008/11/26假日52008/12/4假日62009/1/21假日公式说明（结果）=WORKDAY(A2,A3,A4:A6)从起始日期开始151个工作日的日期，除去假日(2009-5-5)WORKDAY(start_date,days,holidays）返回某日期（起始日期）之前或之后相隔指定工作日的某一日期的日期值。工作日不包括周末和专门指定的假日。Start_date

为一个代表开始日期的日期。Days

为Start_date不含周末及节假日的天数。Holidays

需要从工作日历中排除的一个或多个日期。使用哈公式垂及函身数DATEDIF(start_date,days,unit)UNIT“Y”整年“M”整月“D”整日“YM”不足一年的月“YD”不足一年的日“MD”不足一月的日使用货公式榨及函未数概念使用弓函数WE欺EK浅NU汤M可以绣返回清一个隔数字炭，该械数字盲代表瓜该日垂期在兽一年狂中的娇第几常周。2语法WE巷EK示NU肤M（se为ri晨al给_n切um，re比tu垫rn吹_t笋yp组e）3参数参数Se豪ri解a_挂nu迎m代表蛮要确铜定它夏位于艳一年析中的受几周孝的特婚定日乐期。泰参数Re掏tu扮rn孟_t眠yp窃e为一询数字脂，它愤确定苦星期彩计算砍从哪羞一天乔开始家，其医默认屑值为1，参毯数具所体含茄义如尼表所界示。Re危tu防rn闷_t好yp待e星期脾开始面于1星期牙从星晶期日描开始哪。星辽期内俱的天现数从1到7记数较。2星期般从星锈期一胁开始谣。星邮期内兴的天莲数从1到7记数少。数据伴分析数据透视表创建数据透视表改变数据透视表的布局在数据透视表中排序在数据透视表中执行计算：字段计算、项计算、自定义计算更新数据数据透视