职场力!-最有效的ExcEL商业数据分析技法

chapter 01 商务数据分析的基础知识 101　开始商务数据分析 ·····························································2step 01　现在可以马上进行的商务数据分析 ····························202　日常业务和商务数据分析的关系 ·······································8step 01　日常业务改善的流程和商务数据分析的关系 ·············803　商务数据分析的流程 ·························································9step 01　商务数据分析的方法 ·················································904　商务数据分析的基本方法 ················································10step 01　算出比率加以分析 ···················································10step 02　绘制图表加以分析 ··················································· 11step 03　改变切入点加以分析 ···············································12step 04　改变维度加以分析 ···················································13chapter 02　导入外部数据 1501　将分析数据导入excel ·····················································16step 01　数据的导出与导入 ···················································1602　将文本数据导入excel ·····················································17step 01　文本数据格式的种类 ···············································17step 02　导入文本数据 ··························································17step 03　美化并以excel 格式存储 ········································2003　在excel 中导入access 的数据·······································25step 01　导入access 的数据 ················································2504　加上筛选条件后导入数据 ················································28step 01　加上筛选条件导入数据 ············································2805　直接将外部数据制作成数据透视表 ··································31step 01　运用数据透视表导入外部数据 ·································3106　将web 数据导入excel ···················································35step 01　web 数据的导入······················································3507　将web 数据以外部数据的形式导入excel ······················37step 01　以外部数据的形式导入web 数据 ···························3708　练习问题 ·········································································40chapter 03　数据透视表 4101　何为数据透视表·······························································42step 01　数据透视表概要 ·······················································42step 02　数据表与数据透视表的构成 ·····································4402　制作数据透视表·······························································46step 01　准备数据透视表 ·······················································46step 02　制作数据透视表 ·······················································4903　更改数据透视表的布局 ····················································52step 01　更改布局 ·································································5204　筛选数据透视表的数据 ····················································55step 01　显示指定的数据 ·······················································5505　制作添加筛选条件的数据透视表 ·····································57step 01　添加页字数后根据各项来更改数据透视表 ···············5706　根据报表筛选的项目区分工作表 ·····································60step 01　制作项目的统计表 ···················································6007　将数据透视表中的数值数据进行排序 ······························62step 01　数值的排序 ······························································62step 02　将明细显示的数值进行排序 ·····································6308 显示前10 项 ······································································64step 01　显示前10 项 ····························································6409　将项目的顺序加以排序 ····················································67step 01　将项目的顺序进行排序 ············································6710　将数据透视表的合计值用百分比表示 ······························68step 01　显示百分比 ······························································68step 02　设置基准值以比较数据 ············································6911　更改总计的类型 ·······························································71step 01　更改值汇总方式 ·······················································7112　将数据透视表的数据创建成组 ·········································73step 01　将日期组成组 ··························································7313　更新数据透视表的数据 ····················································76step 01　更新数据 ·································································7614　数据透视图 ······································································78step 01　制作数据透视图 ·······················································7815　数据透视表的格式化 ·······················································82step 01　空白单元格中显示“0” ··········································8216　将数据透视表以“值”的形式显示在别的工作表中 ·········85step 01　以“值”的形式显示在其他工作表中 ······················8517　练习问题 ·········································································87chapter 04　图表（excel 2010） 8901　图表的构成元素·······························································90step 01　图表的构成元素和可以设置的内容 ··························9002　制作图表 ·········································································91step 01　制作图表操作 ··························································91step 02　更改图表类型 ··························································9503　更改图表的布局·······························································98step 01　更改图表整体的布局 ···············································98step 02　更改网格线单位 ·····················································100step 03　更改网格线 ····························································101step 04　在图表内显示数据 ·················································10204　选择元素设置格式 ·························································104step 01　选择元素与格式设置对话框 ···································10405　在既有的图表上添加数据 ··············································106step 01　在和图表相同的工作表中添加数据 ························10606　在既有图表上追加其他工作表的数据 ····························109step 01　添加与图表分属不同工作表的数据 ························10907　组合不同类型的图表 ····················································· 111step 01　制作图表 ································································11108　保存自定义图表····························································· 116step 01　保存图表为模板 ····················································· 116step 02　使用已保存的图表 ················································· 11709　练习问题 ·······································································122chapter 05　单变量求解●规划求解●方案●运算列表 12301　何为单变量求解·····························································124step 01　单变量求解概要 ·····················································124step 02　单元格与“单变量求解”对话框的对应 ·················12502　利用单变量求解逆运算数值 ···········································126step 01　运用单变量求解求出目标值的zui佳数值 ·················12603　何为规划求解 ································································128step 01　规划求解的概要 ·····················································128step 02　单元格与“规划求解参数”对话框的对应 ·············13004　添加规划求解功能 ·························································132step 01　安装规划求解 ························································13205　用规划求解求出zui佳解答 ··············································134step 01　明确列出规划求解的目的与限制条件 ····················134step 02　在规划求解中设置参数后求解 ·······························13506　何为方案 ·······································································140step 01　方案概要 ·······························································140step 02　“添加方案”与“方案变量值”对话框 ·················141step 03　“方案管理器”对话框 ··········································14207　创建方案 ·······································································143step 01　方案的创建 ····························································14308　执行方案 ·······································································146step 01　方案的执行 ····························································14609　综合显示方案的结果 ·····················································147step 01　将多个方案综合显示 ·············································14710　表格（excel 2010）······················································149step 01　转换成表格 ····························································149step 02　在表格中添加合计行 ·············································153step 03　在表格中输入计算式 ·············································15411　练习问题 ········································································158chapter 06　关于销售的商业数据分析 15901　运用z 图表来分析销售额变化的倾向 ····························160step 01　需准备的商务数据 ·················································163step 02　excel 操作①：算出营业额累计和移动年计 ··········164step 03　excel 操作②：制作z 图表 ···································165step 04　解读结果 ·······························································16802　运用abc 分析来弄清重点管理项目 ······························169step 01　需准备的商务数据 ·················································172step 02　excel 操作①：销售额的排序和算出销售实际成果的合计值 ·················································172step 03　excel 操作②：计算销售构成比率和构成比率的累计 ····························································175step 04　excel 操作③：显示划分的等级顺位 ·····················176step 05　excel 操作④：制作柏拉图 ····································178step 06　excel 操作⑤：显示abc 的划分等级顺位 ············181step 07　解读结果 ·······························································18403　提早预测并选定能增加利润的商品 ································187step 01　需准备的商务数据 ·················································189step 02　excel 操作①：输入运算公式 ································189step 03　excel 操作②：交叉比率的排序 ····························191step 04　excel 操作③：制作气泡图 ····································191step 05　excel 操作④：编辑图表 ·······································199step 06　解读结果 ·······························································20304　预测畅销商品的销售数量 ··············································205step 01　需准备的商务数据 ·················································206step 02　excel 操作①：制作变化倾向图 ····························207step 03　excel 操作②：添加趋势线 ····································209step 04　excel 操作③：算出销售数量的预测 ·····················213step 05　解读结果 ·······························································21505　选定拍卖商品 ································································217step 01　需准备的商务数据 ·················································220step 02　excel 操作①：制作需求曲线 ································220step 03　excel 操作②：算出基准价格的预测销售数量 ·······229step 04　excel 操作③：算出价格弹性 ································231step 05　解读结果 ·······························································23306　练习问题 ·······································································236chapter 07　关于企划的商业数据分析 23901　确立商品的市场定位 ·····················································240step 01　需准备的商务数据 ·················································242step 02　excel 操作①：输入计算式 ····································243step 03　excel 操作②：制作气泡图 ····································243step 04　excel 操作③：编辑图表 ·······································250step 05　excel 操作④：在图表中标出商品名称 ··················253step 06　分割成4 个领域 ····················································254step 07　解读结果 ·······························································25602　从气象数据预测夏季的销售数量 ···································258step 01　需准备的商务数据 ·················································262step 02　excel 操作①：算出相关系数与制作散点图 ··········262step 03　解读结果① ····························································267step 04　excel 操作②：添加回归直线 ································267step 05　excel 操作③：算出预测值 ····································269step 06　解读结果② ····························································27103　求出新开店铺的有效卖场面积 ·······································273step 01　需准备的商务数据 ·················································275step 02　excel 操作①：求出商圈人口、卖场面积以及销售额的关系 ························································275step 03　解读结果① ····························································277step 04　excel 操作②：在新店铺用地的商圈人口附近加上趋势线 ····························································278step 05　解读结果② ····························································281step 06　excel 操作③：根据商圈人口算出预测销售额 ·······282step 07　excel 操作④：根据trend 函数算出预测销售额·······································································282目录.indd 5 2016-1-12 16:53:59contents6step 08　excel 操作⑤：使用单变量求解算出卖场面积 ·······283step 09　解读结果③ ····························································28504　运用雷达图来分析商品的强项与弱项 ····························287step 01　需准备的商务数据 ·················································289step 02　excel 操作①：制作雷达图 ····································289step 03　excel 操作②：编辑图表 ·······································290step 04　解读结果 ·······························································29305　判定对提升利润有贡献的方法 ·······································296step 01　需准备的商务数据 ·················································303step 02　excel 操作①：掌握现状 ·······································303step 03　解读结果① ····························································310step 04　excel 操作②：模拟的准备 ····································310step 05　excel 操作③：改善方案的分析 ····························315step 06　excel 操作④：制作方案 ·······································316step 07　解读结果② ····························································32006　练习问题 ·······································································323chapter 08　关于生产的商业数据分析 32501　分析劳动生产力以确切指出原因 ···································326step 01　需准备的商务数据 ·················································328step 02　excel 操作①：算出劳动生产力及其3 个指标 ·······329step 03　excel 操作②：比较去年和今年 ····························333step 04　解读结果 ·······························································33502　导出产生zui大利润的产量 ··············································337step 01　需准备的商务数据 ·················································339step 02　excel 操作①：目的与生产条件的公式化 ··············340step 03　excel 操作②：规划求解的设置与操作 ··················342step 04　解读结果① ····························································347step 05　excel 操作③：产品数据的更改与规划求解的操作 ···································································347step 06　excel 操作④：运算结果报表的编辑 ·····················349step 07　解读结果② ····························································35003　求出zui佳的订货点 ·························································352step 01　需准备的商务数据 ·················································357step 02　excel 操作①：算出研制周期中的平均销售数量 ···358step 03　excel 操作②：算出安全库存 ································359step 04　excel 操作③：算出订货点与订货量 ·····················360step 05　解读结果 ·······························································361目录.indd 6 2016-1-12 16:53:597contents04　练习问题 ·······································································363chapter 09　关于会计、财务的商业数据分析 36501　根据客户的业绩设置信用范围（交易限额） ·················366step 01　需准备的商务数据 ·················································370step 02　excel 操作①：求出信用范围的基准金额，准备评价表 ····························································371step 03　excel 操作②：求出流动比率以及自有资本比率 ···372step 04　excel 操作③：根据流动比率与自有资本比率设置信用范围 ························································373step 05　excel 操作④：信用范围的设置 ····························376step 06　解读结果 ·······························································37702　决定购入或是租借 ·························································379step 01　需准备的商务数据 ·················································384step 02　excel 操作①：准备资金流动表 ····························384step 03　excel 操作②：求出净现值 ····································387step 04　解读结果 ·······························································39003　练习问题 ·······································································393附录　图表（excel 2003）（见二维码）01　整理图表的格式step 01　图表的构成元素和可以设置的内容02　制作图表（excel 2003）step 01　制作图表03　更改图表类型step 01　更改图表的类型04　选择元素设置格式step 01　选择元素与格式设置对话框05　在既有的图表上添加数据step 01　在和图表相同的工作表中添加数据step 02　在既有的图表上添加其他工作表的数据06　组合不同类型的图表step 01　制作图表07　保存自定义图表step 01　保存图表step 02　使用已保存的图表08　练习问题目录.indd 7 2016-1-12 16:53:59目录.indd 8 2016-1-12 16:53:59