Excel商务数据处理与分析 第1章 商务数据分析基础.pdf

Excel商务数据处理与分析第1章使用数据透视图表分析数据OH商务数据概述1.2商务数据的清洗1.3 商务数据的分析1.4 提高与技巧在电商运营的过程中会积累大量的数据，对于这些数据进行采集、存储、分析成为当前 的首要问题。特别是大数据概念的提出，使数据处理的理论和方法都得到了较大的提高。既 然说到数据，那我们首先就要弄清楚数据、信息和大数据这三者之间的含义和关系。数据数据是指对客观事件进行记录并可以鉴别的符号，是构成信息或者知识的原始材料。信息信息与数据既有区别，又有联系。数据本身并没有什 么价值，有价值的是我们从数据中提取出来的信息。大数据近年来，大数据在互联网和信息行业的发展引起了 广泛关注。数据数据（各种数字、符号、图片、视频 等信息）信自信息（从数据中提 取的信息）无论是传统企业还是新型的互联网企业，在其发展的不同阶段，数据都起着重要作用。尤其是在电商行业中，数据呈井喷式的增长，从未停歇，这些庞大的数据意味着我们已经进 入了大数据的时代。大数据在电子商务应用中的作用主要体现在以下5个方面。eQ关联营销：通过大 数据挖掘技术，保 证数据之间得到有 效的关联性，能够 更好地保证用户的 潜在需求得到一定地理营销：利用大 数据的技术优势，充分对网站的交易 数据进行有效的分 析。推荐营销：在市场 实际的分析过程中,满足消费者的个性 化需求显得越来越 重要。分析营销：分析营 销主要是分析消费 者的历史记录和购 买行为，这样就能 有效获得用户的消 费习惯。网络营销：社会网络营 销的传播速度正在飞速 的发展，把握好社会化 网络传播媒介对于消费 者偏好的分析，并在相 关的社会媒介上进行分激发。享活动的积极开展，扩大传播范围，有效提高 营销效率。回1.1.3商务数据的采集A由于大数据的迅速发展，目前已经逐步渗透到电商、金融、交通旅游及零售业等多个领 域。但是大数据在哪里可以获取？又该如何进行数据采集呢？下面我们以商务数据为例，介 绍商务数据的具体采集流程和方法。1.商务数据采集流程明确采集要求明确分析对象按需求采集数据回1.1.3商务数据的采集A2.商务数据采集方法数据采集主要是针 对定性数据和定量数据 这两种数据的采集。其 中，定性数据主要采用 问卷调查和用户访谈的 方式获取，而定量数据 则分为内部数据和外部 数据两部分。数据采集方法!网站日志库回1.1.4将商务数据导入ExcelExcel不仅可以存储处理本机的数据，还可以导入来自网站的外部数据信息。Excel可以导入 的数据类型有多种，如Access数据、网站数据及文本内容等。下面将介绍利用Excel导入来自网 站数据的方法，其具体操作如下。1.单击自网站按钮2.输入并需要下载数据的网址回。J-工作簿 1-Microsoft Excel开始 插入 负面布置 公式1.外部致振.全都刷新一箍选*清除0星新应用，褥R定眸卦,分列U除叶分级显示 重复项酚，排序丽透A 2 D I E I F I G TS A ccess白网站日文本目其他来源 现有连接_外_5（2.单击）7891011121314K Sheet，Sheet2、Sheet3 4 I i川就场I|画D 100%击 单外防0回1.1.4将商务数据导入Excel3.选择要导入的表格提小4.设置导入数据的放置位置在新建Web查询对话框中，如果不单击网页中的右箭头按钮，而是直接单击 导入按钮，则表示导入网页中的全部内容。在实际操作中，一般都是导入网页中的表格 内容，这样才能在Excel进一步进行编辑和处理。回1.1.4将商务数据导入ExcelA5.将网页数据成功导入到工作表中提不国U，?不斑1*11-Microsoft Excel睡一；开*Q U3D布息 公式1防得色功胡 i A _i _ij3 A ccess目网S目文本目具砂 现有连接支创连撞才修住nill 排序T重 分列利总魁超福噩例静 款存 舍并计n 则分析 由学俎取;由福合分葡I萼世gS分析,*“雨舱掩aI球卷项有效性 *云取外购&S3ETA分安密示 G分折F7 二匚 八1指标BCD 水平也长率E F I GH I2元3-）全国居民人均可支配收入14063 8.7 6.6)4按常住地分：5地道居民19770 7.9 5.8)6农村居民7142 8.8.0展1格式，鹑贴板 口 字体 6对齐方式 G数字T单元格F23BcDE1所售商品名称购买用户数量（人）总销售额（元）人均消费额（元）2连衣裙、衬衫、牛仔裤625138560.36221.6965703连衣裙、T恤、半身裙563145000.3257.54937834背心吊带、雷丝衫、牛仔裤598136589228.40969,5连衣裙、衬衫、牛仔裤603146872243.56882266连衣裙、阔腿裤556136546.8245.58776987时尚套装、牛仔裤587d8棉麻连衣裙旗袍663189632.36286.02165919短裤、半身裙610185263303.709836110真丝连衣裙、大码女装605168752.3278.929421511连衣裙、衬衫、牛仔裤512155683.5304.069335912短裤、衬衫、牛仔裤556168523303.098920913雪纺连衣裙、防晒衫603189655314.519071314连衣裙、衬衫、牛仔裤559165835.6296.6647585H !Sheet l.Sheet 2,Sheet 3 匕1F1111 nFt e 插入 文面布局 公式 病S 而同 飕 特色功麓 G顿O曲）S3 11 b/U KN 8 a 笠。字体 G三三三聿菱常规 噂%，*dS葬GJ.单诵 单J&E.查找（E替搅出7 f kBC2.选存11定位条件）5品名称h衬衫、牛仔祷 打T恤、半身裙购买用户数量（A）1H用告南儿）525 138560.36563 145000.3批注（M）；带,雷丝初、牛仔裤5981365891村杉、牛仔檐S03146872、阔腿裤556136546.8E装、牛仔裤587U衣裙.旗袍 半身裙三衣褶、大码女装663610605189632.36185263 168752.3 4鳏对象（Q）媾密怕（）.“3村杉、牛仔精512155683.5304.06!衬杉、牛仔裤556168523303.08浓热防晒移603189655314.51 衬衫、牛仔挺559165835.6296.66,+1 Shaa 十少aat甘9214IM回1.2.1清洗缺失的数据3.定位空值单元格4.计算平均值定位条件 Q IHI输入选择批注C）厂行内容差异单元格常里&）列内苔差异单元格c公式S）章王选中）逻辑值（G）引错误口野面!篡当儒区标当前数组。）对象唾）匚单击,引用单元格U）从属单元格S）直属d）所有辍他）最后一个单元格）可见单元格CL）条件格式复）数据有效性叟）够全部0J相同制确定二II-取消CONCATENATE X A=(D6+D8)/2VBc VI_DEL 1所售商品名称 购买用户数量（人）总销售额（元）人均消费额（72连衣裙、衬衫、牛仔谯625138560.36221.63连衣褶、T恤、半身褶563145000.3257.5414背心吊带、雷丝杉、牛仔裱598136589228.4(5连衣裙、衬衫、牛仔推603146872243.561=6连衣裙、阔胧秸556匚二口6.二：245.58,7时尚套装、牛仔裤587|=(D6+D8)/28棉麻连衣裙、旗袍663匚189632.3a286.02：9短裤,半身裙610185263303.70110真丝连衣裙、大码女装605168752.3278.92,11连衣裙、衬衫、牛仔裱512155683.5304.0612短裤、衬衫、牛仔裤556168523303.09(13雪纺连衣裙、防晒衫603189655314.5114连衣裙、衬留、牛仔被559165835.6296.6614 4”Sh eetl Sh eet2 Sh eet3。0IUI E偏言|留回国:回1.2.1清洗缺失的数据5.清洗并设置数据6.设置数字格式后的效果区I u 一网店人均销售酸统计jdsx-Microsoft Excel回文件rfia质入 5TH布局 公式 以森 亩河 项圉 特色功惫 公 11林贴AN三三高善言 ABC 123曳贴我 7字体 G对齐方式E2二I/_ _，12D1总销售额（元）3.速择片 人均消息、FE】?|138560.361.选彳华偿-4687236,789|10111.12 13：14136546.8163089.58189632.36185263168752.3155683.5168523189655165835.6221.696576257.5493783228.409699243.5688226245.5877698277.8357411286.0216591303.7098361278.9294215304.0693359303.0989209314 5190713296,6647585会计受用M2 2 L70%把日期1900/8/8货而 2 2 1.702 2 1.7072.单型号M LU m无特定格式长日期190（年8月8日时间16:43:04百分比22169.66%区dq,J 1*网W人均销隹额统计xlsx-Microsoft Excel法 市角 犍黝1共插入兵面布局馆:X 未体 11 -s=二 工一 上.B Z U-N晨 室叁善药.吗，/遗笠率率诊，笈当 剪贴板 G 字体 G 对齐方式 g|数字 g|E2 6=D2/C2C DE12购买用户翻量人）总销售额（元）人均用铛额（元）625 138560.36221.703563 145000.3257.554598 136589228.41*243.575603 14687267556 136546.8587 163089.58245.59277.848663 189632.36286.02*303.719610 1852631011605 168752.3512 155683.5278.93304.0712556 168523303.10 314.5213603 18965514559 165835.6296.66提不在清洗数据的过程中，如果数据量较大，且缺失值较多，可以打开定位条件对话框，定位出数据中的所有空值，然后在活动单元格内输入平均值，最后利用Ct rl+Ent ed组合键一次性在选中的空值单元格中输入样本平均值；当缺失数据较少时，也可以通过选取数据前后若干天的数据取平均值作为缺失数据进行填充。回1.2.2清洗重复的数据重复数据一般分为实体重复 和字段重复两种。其中，实体重 复是指所有字段完全重复；字段 重复则表示某一个或多个不该重 复的字段重复。为了保证数据的 一致性，需要对重复数据进行处 理，在对重复数据进行清洗前，首先应该查找重复数据，一般采 用如图所示的几种方法进行重复 数据的查找。数据透视表法/条件格高级筛式法_-_一 一函数法回1.2.2清洗重复的数据1.数据透视表法首先使用Excel 2010打开要清洗的数据，然后插入数据透视表，再通过拖动相应的字段到 数据透视表字段任务窗格中的行标签 列标签和数值等区域完成数据透视表的创 建。通过数据透视表可以统计出各数据出现的频次，若出现两次及以上的数据就属于重复项，如 图所示为利用数据透视表查找重复员工的效果。注意：数值字 段要设置为计数汇 总方式才能查找到重复 数据。回1.2.2清洗重复的数据A2.函数法利用Excel 2010提供的统计函数C O U N TIF z可以对指定区域中符合指定条件的单元格计数，并以此对重复数据进行识别。COU N TIF函数语的语法 结构为:CO U N TIF(Rang ezC rit eria),其中，Rang e表示计算 其中非空单元格数目的区域；C rit eria表示以数字、表达式或者 文本形式定义的条件。图为利用 COUN TIF 函数统 sheet l 工作表中重复值的效果，辅助 列中大于的数字就表示 为重复值。115 飞&=COUNTIF($A$3:A15,A15)ABCDEFGHIJK1员工工资表2姓名部门基本工资出差补助饮食津贴效绩奖金总金额辅助列3周大龙销售部1500.00200.00180.00300.002180.0014徐小凤销售部1500.000.00180.00300.001980.0015赵凌销售部1500.00200.00180.00300.002180.0016万剑锋策划部1130.00470.00180.00450.002230.0017李菁设计部1130.0080.00180.00450.001840.0018李菁设计部1130.0080.00180.00450.001840.0029李菁设计部1130.0080.00180.00450.001840.00310李玉君销售部1500.00200.00180.00300.002180.00111赵丽华策划部1500.00200.00180.00300.002180.00112赵丽华策划部1500.00200.00180.00300.002180.00213王静策划部1130.0080.00180.00450.001840.00114谢想设计部1000.00120.00180.00600.001900.00115谢想设计部1000.00120.00180.00600.001900.001_21M 4(Sh eet4 Sh eetl Sh eet2Sh eets/匕11111回1.2.2清洗重复的数据3.高级筛选法利用Exce提供的高级筛选功能，可以快速查找并删除大量的重复数据。首 先利用Excel打开带有重复记录的工作簿，然后在数据选项卡的排序和筛选 组中单击高级筛选按钮。打开高级 筛选对话框，在其中可以设置筛选结果 的存放位置、参与筛选的数据区域和筛选 条件等参数，如图所示，然后单击选中 选择不重复的记录复选框，最后单击 确定按钮，即可在查找重复数据的同 时自动将重复数据删除。高级择选 MsjT方式O在原有区域显示筛选结果也）0将筛选结果复制到其他位置（Q）列表区域&）：$A$2:$G$18 医）条件区域C）：|因复制到煲）：Sheet l!$A$20 国切选择不重复的记录国）确定|取消 回1.2.2清洗重复的数据4.条件格式法利用Excel处理数据时，如果要突出选中区域中的重复值，可使用条件格式中的突出显示 单元格规则来实现。1.突出显示单元格中的重复值2.突出显示重复数据的效果ffttJ插入 玄面布曷 公式 数亮 亳阅 视圉 特色力能 公 Q O 目S3文件23456二8910111213A2 G 听:A I B D|都门基本工资出差补 泊售部 1500.00 200.销售部 1500.00 0.销售部 1500.00 200.策划部 1130.00 470.姓名 周大龙 徐小凤赵凌 万剑锋 李善 李菁李菁 李玉君 赵丽华 赵丽华王静 谢想刍突出至示单皿3，常规差SH4唉套用 好 表悟恪式，适中销售部 1500.00 200.设计部1130.0080.设计郃1130.0080.设计部1130.0080.策划郃 1500.00 200.策划郃 1500.00 200.策划郃 1130.00 80.设计郃 1000.00 120.空原目选取规则倒数据条必e|翻小3新建规则必=dT W 马大于&)-小于_介于_等于(D_文本包含一发生日期超值6ABCDEF5赵凌销售部1500.00200.00180.00300.006万剑锋策划部1130.00470.00180.00450.007李菁设计部1130.0080.00180.00450.008李菁设计部1130.0080.00180.00450.009李善设计部1130.0080.00180.00450.0010李玉君销售部1500.00200.00180.00300.0011赵丽华策划部1500.00200.00180.00300.0012赵丽华策划部1500.00200.00180.00300.0013王静策划部1130.0080.00180.00450.0014谢想设计部1000.00120.00180.00600.0015谢想设计部1000.00120.00180.00600.0016171 RM 卜 Sheet 4 Sheet l Sheet 2Sheet 3 它1mi回1.2.3清洗错误的数据1.清洗手工录入的错误数据利用Exce提供的条件格式功能，可以快速查找出手工录入的错误数据。1.选择单元格区域2.新建规则国修9，J V手工人的教宗xkxMicrosoft Excelc恁入 页面布局 公式 以提 15间 视图 何色功能 Q文件=手心 395WB/U,A?A*田 变字体 Q垂妻美里，季母涉常规吗%A单元格TB3 0AB-cDEFG序号T22n*jAB_DEF_31)011004二101021531302001 64101002H9101112备注1:T2要求最多选授3个匚星超过3个则视为错误。备注2：T2要求选项中只能出现。或1两种数字，其余数字视为错误，需要清以H *Sheet 1(Sheet 2Sheet 3、七Jifci I平iSfi：0.75班Illi数：二8 米和：二1|回口四100%-回1.2.3清洗错误的数据提小Exce件是供的条件格式功能，对于数据的清洗和分析都是非常实用的。运用条件格式功能可以 基于指定条件而更改单元格区域的外观，用于直观地注释数据以供分析和演示。所以，通过条件格式 可以达到突出显示所关注的单元格或单元格区域，强调异常值，直观显示数据等目的。新建喑式规则曾li-qd汉选择规则类型）:A基于各自值设置所有单元格的格式A只为包含以下内容的单元格设置格式-仅对排名靠前或靠后的数值峻置格式A仅对高于或低于平均值的数值设置格式A仅对唯一值或重复值设置格式-：-使用公式确定要设置格式的单元格-J.单击3.编辑条件规则设置单元格格式数字体单击字体on：空号纣stsstst良 Adobe S Adobe Adobe Adobe特殊效果删除线口上标建）口下标8编揖规则说明直）：/1 1 r*、I脸 I取消条件格式可以包括字体样式、下划线、4.设置字体颜色3.单击J-一越道回1.2.3清洗错误的数据5.利用条件格式清洗错误数据的效果区X q J卜插入T录入的数据.xlsx-Microsoft Excel页面布局 公式 数据 亩阅 视图 特色功能1=1 1公回C国次粘贴电B/口 y 田1/里贴板j 字体 11 N K1天I=率率|诊对齐万式春 常规 国雪%，军 too 宜一 数字 G|A 齐播x 约弃删除困辨，单元格 编辑118AVABCDEFGHIL=_1序号T2检脸2ABCDEFG31Cj01100二421010210=5313020016410100217_891011备注1：T2要求最多选择3个选项，若实际选择超过3个则视为错误。备注2：T2要求选项中只能出现。或1两种数字，其余数字视为错误，需要清洗。12 H Sheetl Sheet2Sheet3/飞 r1111 E回1.2.3清洗错误的数据2.清洗被调查者输入的错误数据在进行问卷调查时，涉及到多项选择题时最多可选三项，而答题者却选择了四项及以上。对于这种情况，可以综合利用Excel提供的C OU N TIF函数和IF函数来判断数据的正确性。下面 将以客户满意度调查中的多项选择题为例来介绍错误信息的清洗方法，其具体操作如下。9*J t 微源查者墟入的数SS xlsx Microsoft Excel开始 插入 其面布局 公式 数霜 亩四 槌5 特色功能 40*奥姑板、，11 三 B a SSB Z U*A?A*手音 囱一田 金变*诊，字体 1 对齐方式 中 A A瓶2M.吗%，圆：一.00,。数字 行 明元格I:1国T513E A=IF（C0UNTIF（B：、，错误/正确）_BC D E F G H|IRaBcT2DF31 601100 2：正确4 101021o!56789130200ii101002li1011 of2000拖动0012_pJ o|1011101110o!201110oi121301234561_00101-2.选择单元格回1.2.3清洗错误的数据ABCDEFGHIJ1序号T22ABCDEFG310011002ST421ij1ij210错误531302ij。1错误641010021错误751011010错误862000001正确970012100正确1081011100错误1192011100错误12100123456错误13111001010正确14121010111错误15131010111错误16142100010正确17152222222错误18160102000正确19170100000正确20180010001正确21190241000正确3.清洗出错误数据1.1商务数据概述1.2商务数据的清洗1.3商务数据的分析1.4提高与技巧回1.3.1数据分析的基本步骤商业数据分析的目标是利用大数据为所有职场人员做出高质量、高效率的决策，提供可规 模化的解决方案。那么，在面对海量的数据信息时，我们该从何入手呢？先做什么、后做什么?下面总结了商务数据分析的6个基本步骤供大家参考。01 第一步：明确分析目的。首先要明确分析目的，并把分析目的分解成若干个不同的分析要点，然 时。n 后梳理分析思路，最后搭建分析框架。Q2 第二步：数据采集。一般可以通过数据库、第三方数据统计工具、专业的调研机构的统计年鉴或。,。N 报告以及市场调查4种方式来采集数据。QQ 第三步：数据处理。数据处理主要包括：数据清洗、数据转化、数据抽取以及数据计算等处理方O-ON 法，将各种原始数据加工成数据分析所要求的样式。04 第四步：数据分析。常用的数据分析工具包括：Excel.SQ L、Tblleau.Pow erB I、Pyt hon,op.h ive以及SPSS等。05 第五步：数据可视化。一般能用图说明问题的就不用表格，能用表说明问题的就不用文字。OPTION回1.3.2数据分析方法数据分析，是指选择合适的统计分析方法和思路，从大量的原始数据中抽取出有价值的信 息，并对数据加以详细研究和概括总结的过程。数据分析不是一劳永逸的，产品在不断迭代，业务在不断更新，从认知到决策，数据更多的是起着辅助作用。订单状态数据通常是按照一 定的时间进行划分，显示某 一时间段内的各种订单情况 订单状态数据主要包括：新 客户、老客户、未付款客户、付款客单价和全部订单金额。销售额数据主要包括：总销 售额、新客户销售额和回头 客销售额3种订单时间数据主要针对一周或某 一天的订单数据进行分析。关联订单就是购买某一关联商品所 产生的订单。做好网站的关联销售,不仅能降低网站的跳出率，而且还 能有效提升客户转化率，以此达到 网站利益最大化的目的。回1.3.3常用数据分析工具数据分析最关键的就是工具，再好的数据分析方法也需要分析工具来支撑。选择什么分析 工具跟工作岗位、分析场景息息相关，每种场景都有若干种工具可以选择。如图汇总了一些常 用的数据分析工具，供大家参考使用。应用领域适用工具数据采集数据清洗 数据可视化 统计分析Python、Google Analytics、数极客等ExceL SQL、Hives Hadoop等Excel Echart、PowerBk Tableau等Excel、Python、SAS、Stata Eviews等提不Pyt hon是一种广泛应用的高级编程语言，功能非常强大，被广泛用于编写爬虫程序，按照一定规则 自动抓取网络上的信息，比如房产网上的价格。Pyt hon提倡简单明了的编程哲学，便于初学者入门，同 时Pyt hon有非常强大的第三方库可以调用。回1.3.4数据分析报告数据分析处理项目完成后，一般要撰写工作总结和数据分析报告。数据分析报告，它是项 目可行性判断的重要依据，也是数据分析过程和思路的最后呈现。一份数据分析报告一般应具 备以下3个要素。总体分析：从项目的实际需求出发，对该项目的财务、业务数据进行总量分析，把握全局。确定重点，合理配置资源：在对项目全局掌握的基础上，根据被分析项目的特点，通过具体的趋势分析、对比分析等手 段，合理确定分析重点，协助分析人员作出正确的项目分析决策，调整人力、物力等资源以 达到最佳状态。建立模型：针对不同的分析事项建立具体的分析模型，将主观的经验固化为客观的分析模型，从而指导 以后项目实践中的数据分析。1.1 商务数据概述1.2 商务数据的清洗1.3商务数据的分析1.4提高与技巧回1.4.1数据拾掘数据挖掘，一般是指从大量的数据中通过算法搜索出隐含的、先前未知的并有潜在价值的 信息的过程。数据挖掘通常与计算机科学有关，该流程主要包括如图1-36所示的6个步骤，下面 分别进行介绍。业务理解：通过反复沟通，准确理解业务问题，将其转换成数据挖掘问题，并拟定初步构想。数据理解：收集、理解及过滤所需的数据并进行数据质量评估。数据准备：对数据进行清洗、抽取、转换、组合、存储以及处理等，便于数据挖掘的使用。建立模型：采用各种方法建立分析模型，解决提出的业务问题。模型评估：对建立的模型进行评估。撰写报告：最终生成报告。回1.4.2 BI工具为了应对企业业务人员对大数据分析的需求，近几年，市面上涌现了一批自助式BI（商业 智能分析）工具，如Tableau、Pow erBIs Spot fire.神策分析和魔镜等。自助式BI工具，实质上就是指大数据前端分析工具。目前的自助式BI工具已经将维度的选 择集成到控件组件的拖选操作，自动建模技术代替了手动建模，使数据分析工作更加简单和便 捷。通过BI工具，一方面可以帮助业务分析员更快速的响应业务需求，另一方面业务和数据的 快速结合能够提高决策的效率。当然，每一个数据分析工具都有其各自的优缺点，对于工具的选用应视情况、视侧重点不 同来进行选择。只有适合自己的分析工具，才能够大大地简化数据分析的繁杂工作，提高分析 的效率与质量。感谢聆听！学习进步!Excel商务数据处理与分析