excel数据模板的一些小经验.docx

Excel数据模板的一些小经验（申明：此文档内容仅代表个人观点，如有错误，敬请指正，同时也欢迎有见解的小盆友补充完善和共享）一，Excel特征快捷方式右键（右手）+对应字母（左手）！！双手操作是好习惯，不管第一次觉得多别扭、多慢，都尽量双手来使用快捷方式，孰能生巧，熟练之后肯定比单手点快Excel的默认计算模式等距特性循环特性随机数计算特性（演示）二，数据模板的思路1，规范化（小数点，逻辑，格式）2，模块化（公司，分区，分类，分引用等）3，简单化（模板可以通过复制，递增公司名单数量，分区分类数量等）（演示）三，基本逻辑假定全球销量=全球消费量产销比=销量/产量假定本地供应=本地产量 或本地供应=本地产量+进口（实际供应=本年产量+上年库存+进口）当地销量=当地产量（或当地供应）+进口-出口（默认本地供应=本地产量时）当地销量=当地供应-出口（默认本地供应=本地产量+进口时）进口率=进口/供应出口率=出口/供应各地供应之和-各地进口之和=全球供应量各地总进口=各地总出口假定出口均为本地产量假定所有出厂价格均为含税不含运费产销价比=当地销售价/当地出厂价（演示）四，数据模板制作引用项编辑单位单词连接编辑（，” “）锁定行、列的灵活运用参数项参数小数位数（基本常数设定）产值、销售额等系数（基本常数设定）其它（公司，分区，分类，分应用等名词）（演示）模块编辑引用参数项锁定行、列的灵活运用（$）模块行数及列数的选定技巧常用Excel函数：ROUND（），RAND（），INDIRECT（），IF（），ROW（），CHAR（），AND（），SUM（）, 加减乘除开方等。开方有两个编辑方式 POWER(X, 1/n)=X^(1/n)公司（地区等）1模块编辑完之后，公司（地区等）2至公司（地区等）Xn，只需依次复制Xn-1遍即可。（演示）函数用法INDIRECT（）与等差数列配合使用可实现各种隔行引用：隔行引用不隔行表格数据从数据由总而分常用N=1,2,3,4… （ROW（）可在Excel中实现自动生成非零自然数列）X2=2,3,4…（等差+1）；Y2=1,25,49…（等差+24）隔行（Y2）引用不隔行数据 X2=(Y2+23)/24+1，N=(Y2+23)/24（Y2=N*24-23）。（实例：=INDIRECT(引用!G((ROW(引用!$G1)+23)/24+1))）不隔行引用隔行数据N=1,2,3,4… （ROW（）可在Excel中实现自动生成非零自然数列）X1=3,27,51…（等差+24）隔行引用不隔行表格数据不隔行引用隔（X1）行数据X1=N*24-21 （实例：=INDIRECT(基地分计!引用!R$2(ROW(引用!G1)*24-21))）隔Y行引用隔X行表格数据较少用，中间汇总数据再按其它标准拆分时会用，如：基地产量→企业汇总→企业分类（分应用，分区销售等）N=1,2,3,4… （ROW（）可在Excel中实现自动生成非零自然数列）X3=3,27,51…（等差+24） Y3=2,8,14…（等差+6）隔Y（Y3）行引用隔X（X3）行表格数据X3=(Y3+4)/6*24-21，N=(Y3+4)/6（Y3=N*6-4）。（实例：=INDIRECT(企业图表!引用!R$2(((ROW(引用!G2)+4)/6)*24-21))）行列转置隔行引用特殊用途，极少用，也相对简单。INDIRECT使用注意事项：与” “配合使用时必须注意字母的位置，如果字母在” “内则，右拉时字母序列不会递增，如需实现字母递增，可预设字母序列以引用或者用CHAR函数生成字母。ROW使用注意事项：ROW所引用的行或者列删除都会导致相关公式出错，因此ROW引用的行列最好设置在同一个表格里，并保证该表格里原则上不会删除任何行列。CHAR（）生成字符序列。例：=CHAR（65）是对应A字母。IF（）将错误显示为零或者为空例：=IF(A=0,0,A)直观判断数据区间例：=IF（mA, “正确”, ”错误”）； =IF（AND（mA, An）, “正确”, ”错误”）直观判断数据逻辑例：=IF(L90+L100+L110+L120L131,IF(L91+L101+L111+L121L130,其他出口过大,进出口正确),其它进口过大)VALUE（）和TEXT（）配合使用可以达到不用刷部分数据格式的效果VALUE（）变成数值TEXT（）变成文本，可以设定格式。例：=TEXT（5/10，”0.00”）就是显示两位小数0.50。（演示）五，使用过程基本信息输入单位、小数点确定值、产值计算系数、公司名称、地区名、分类名词、应用名词等多余表格的处理隐藏：可增可减，便于升级删除：不利于增加公司（地区、分类、应用等）（演示）核心数据的输入产能产量或产能利用率（可以相互转换）价格或