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。 （演示） 五，使用过程 基本信息输入 单位、小数点确定值、产值计算系数