如何用Excel处理B2C网站数据？

1）数据导入

通常需要使用Excel处理数据的时候，我们首先需要把一些文件导入Excel表格中。数据导入可以通过“数据”标签项中的“获取外部数据”功能组实现，操作步骤如下所述。

打开Excel 2010，点击“数据”“获取外部数据”“自文本”，如图3-66所示。在“导入文本文件”窗口中选择需要导入的文件，点击“导入”。

图3-66　导入文本数据

在弹出“文本导入向导”对话框中选择“分隔符号”，点击“下一步”，如图3-67所示。

图3-67　文本导入向导第1步

“文本导入向导”对话框中选择“逗号”，点击“下一步”，如图3-68所示。

图3-68　文本导入向导第2步

“文本导入向导”对话框选择“文本”或者“常规”，点击“完成”，如图3-69所示。

图3-69　文本导入向导第3步

弹出对话框“导入数据”，选择“新工作表”，按“确定”按钮即可，如图3-70所示。

图3-70　导入新工作表

返回Excel工作表，就可以看到数据的导入情况，如图3-71所示。

图3-71　导入结果

2）重复数据处理

采集网页信息形成的原始数据通常存在重复情形。如何识别和去重？下面介绍Excel中两种常用方法。

（1）高级筛选法

如果只是需要将目标数据的非重复值筛选出来，可以选择“数据”选项卡下“排序筛选”组中的“高级”按钮，如图3-72所示。

图3-72　高级筛选法步骤1

在弹出的对话框中进行设置，如图3-73所示。

图3-73　高级筛选法步骤2

点击“确定”后，处理结果如图3-74所示。红框部分为“来源”数据的非重复项。

图3-74　高级筛选法步骤3

（2）条件格式法

Excel 2010里面内设标识重复项的功能。打开Excel目标文件，如图3-75所示。

图3-75　条件格式法步骤1

选择“开始”“条件格式”“突出显示单元格规则”“重复值”，如图3-76所示。

图3-76　条件格式法步骤2

可以在弹出的对话框中把重复的数据标注为红色，点击“确定”，如图3-77所示。

图3-77　条件格式法步骤3

重复数据处理部分结果如图3-78所示。

图3-78　条件格式法步骤4

3）缺失数据处理

Excel表中如果出现缺失数据，一般表现为空值或者错误表示符，运用“定位条件”和“查找替换”功能可以进行缺失数据的处理，原始数据如图3-79所示。

图3-79　缺失数据示例

原始数据中“F列”项目为“已售”，具体包括“x+sold”“x+watching”“x%off”，分别表示“已销售的数”“关注数”“折扣值”。要求将“F列”的缺失数据全部标记为“0”，便于后期数据分析。

在Excel“开始”主选项卡的“编辑”功能区下拉菜单里选择“定位条件”，或者直接使用快捷键“Ctrl+G”，如图3-80所示。

图3-80　定位操作1

弹出“定位”对话框，选择“条件定位”“空值”，如图3-81、图3-82所示。

图3-81　定位操作2

图3-82　定位操作3

点击“确定”后，直接输入“0”，按“Ctrl+Enter”，空值单元格一次性全部输入“0”，如图3-83所示。

图3-83　定位操作4

Excel中的“查找替换”功能也可以用来处理缺失数据，在“开始”选项卡的“编辑”功能区中可以找到此功能，如图3-84所示。

图3-84　查找替换操作1

可以尝试使用此功能，将“F列”中的缺失数据全部标记为“0”。查找替换功能“选项”按钮中的细节如图3-85所示。

图3-85　查找替换操作2

图3-86所示为某网店订单数据。通过查找替换功能可以将“D列”中手机订单标注为“M”。

图3-86　查找替换操作3

若查找单元格内容需要区分大小写，勾选如图3-85所示对话框中“区分大小写”选项，替换结果上的变化如图3-87所示。

图3-87　查找替换操作4

4）错误数据处理

使用Excel能够控制和检查数据统计中存在的错误。假设某一网店邀请新老客户参加“最喜爱宝贝”有奖调查活动，请每位客户从店铺热销的5款商品中选择不超过3件自己喜爱的宝贝。调查数据经过采集导入Excel表格中，截取部分数据展示如图3-88所示。

图3-88　数据示例

图3-88中“0”表示未选择，“1”表示选择，空值和非“0”“1”的数据都是错误的，一行超出3个“1”，该行存在错误。

（1）圈出表格中的无效数据

选择表中B3：F7区域，点击“数据”“数据有效性”，如图3-89所示。

图3-89　数据有效性检查操作1

弹出窗口中有效条件设置为“序列”，取消勾选“忽略空值”，单击“确定”，如图3-90所示。

图3-90　数据有效性检查操作2

选择“数据有效性”下拉菜单中“圈释无效数据”，操作如图3-91所示。

图3-91　数据有效性检查操作3

显示结果如图3-92所示。

图3-92　数据有效性检查结果

（2）使用COUNTIF函数进行逻辑检查

检查B3：F3区域，在G3单元格输入公式如图3-93所示。

图3-93　函数逻辑检查

G4：G6已有红圈圈释无效数据，将G3中的公式复制填充到G7，检验结果如图3-94所示。

图3-94　函数逻辑检查结果

这里用到IF函数、OR函数和COUNTIF函数。IF函数是判断语句，用途如图3-95所示。

图3-95　IF函数

通过举例说明IF函数，如图3-96所示。

图3-96　IF函数示例

OR函数用途如图3-97所示。

图3-97　OR函数

举例说明OR函数：判断一个数值是否落在指定的区间外。判断数字18，是否落在区间（10，15）以外，返回结果为TURE。判断数字14，是否落在开区间（10，15）以外，返回结果为FALSE，操作示例如图3-98所示。

图3-98　OR函数示例

与OR函数对比学习AND函数，AND函数用途如图3-99所示。(www.xing528.com)

图3-99　AND函数

试试在Excel中用AND函数判断18，14是否在（10，15）区间内。

COUNTIF函数用途如图3-100所示。

图3-100　COUNTIF函数

举例说明COUNTIF函数：如图中A列是网店客户ID，计算每一ID在A列中出现的次数，在B1单元格中输入函数公式。下拉B1单元中公式到B2：B9，即可得到每一个ID在A列中出现的次数，如图3-101所示。

图3-101　COUNTIF函数示例1

若要依次标注每一ID在A列中出现的次数，仍然可以用COUNTIF函数，具体操作如图3-102所示。

图3-102　COUNTIF函数示例2

5）数据抽取

数据抽取包括字段分列、字段合并和字段匹配。以某店铺的发货地址信息为例，需要将发货地址分列成三列字段，有助于达到细分客户来源的目的，操作过程如下所示。

（1）字段分列

选择需要进行字段分列的数据区域，单击“数据”“分列”，选择“分隔符号”，如图3-103所示。

图3-103　字段分列操作1

选择“空格”选项，观察数据预览区域，如图3-104所示。

图3-104　字段分列操作2

若数据分列为三列，可忽略第四列。点击忽略列，选择“不导入此列”，如图3-105所示。

图3-105　字段分列操作3

点击“完成”，结果如图3-106所示。

图3-106　字段分列结果

（2）数据合并

与“数据分列”相对的数据抽取方式是“数据合并”，可以使用“&”运算符或CONCATENATE函数实现，以上例结果为例，将三列数据合并为一列。应用“&”函数，操作如图3-107所示。

图3-107　“&”函数操作

应用CONCATENATE函数，操作如图3-108所示。

图3-108　CONCATENATE函数操作

（3）数据匹配

数据匹配是将原数据表没有但其他数据表中有的字段，有效地匹配过来，经常使用VLOOKUP函数来实现。VLOOKUP函数功能如图3-109所示。

图3-109　VLOOKUP函数

VLOOKUP函数参数，如图3-110所示。

图3-110　VLOOKUP函数参数

VLOOKUP函数参数解释，见表3-2。

表3-2　VLOOKUP函数参数解释

请用VLOOKUP函数从lookup表中查找相关数据填写lookup 2表中E、F列内容。lookup表如图3-111所示，lookup 2表如图3-112所示。

图3-111　lookup表截图

图3-112　lookup 2表截图

单元格E2中VLOOKUP函数设置参数，如图3-113所示。

图3-113　单元格E2设置

单元格F2中VLOOKUP函数设置参数，如图3-114所示。

图3-114　单元格F2设置

6）数据分组

VLOOKUP函数使用比较广泛，例如需要对“买家实际支付金额”进行分组，同样可以使用VLOOKUP函数实现。

在表格空白处制定分组标准，0～100，100～500，500以上，如图3-115中L列。其中“阈值”是指每组覆盖范围中的最小值。

单元格J2中设置VLOOKUP函数参数，如图3-115所示。注意参数Range_lookup填写“1”或“TRUE”，表示模糊查找。

在单元格J2：J7进行VLOOKUP函数填充。

图3-115　数据分组操作

7）数据计算

（1）加、减、乘、除运算

如何在Excel中进行简单的加、减、乘、除运算？除了可以用“+、-、∗、/”运算符，最常使用的是SUM、AVERAGE函数。

如图3-116所示，求和D2：D6，点击空格输入“=SUM（D2：D6）”；求D2：D6平均值，点击空格输入“=AVERAGE（D2：D6）”。

图3-116　SUM和AVERAGE操作

（2）日期的加减法

快速输入当前日期的小窍门，如图3-117所示。

图3-117　快速输入当前日期

DATE函数功能如图3-118所示。

图3-118　DATE函数

DATE函数参数如图3-119所示。

图3-119　DATE函数参数

经常会在Excel中进行时间加减运算，举例如图3-120所示。

图3-120　时间加减运算

计算两个日期之间年、月、日时间差，举例如图3-121所示。

图3-121　计算两个日期时间差

8）数据转换

Excel 2010行列转化操作非常简便。

复制数据区域A1：D4，点击行列转化后起始单元格F1，点击鼠标右键选择粘贴方式“转置”，如图3-122所示。

图3-122　数据转置操作

运用Excel将二维表转化为一维表的情形是非常普遍的。Alt+D，松开再按P，快捷打开数据透视表对话框，选择如图3-123所示。

图3-123　数据透视向导步骤1

数据透视表和数据透视图向导窗口设置如图3-124、图3-125所示。

图3-124　数据透视向导步骤2

图3-125　数据透视向导步骤3

点击“完成”，即可完成数据透视表的创建，如图3-126所示。

图3-126　数据透视表结果

在“新工作表”中双击E8，Excel自动创建又一新工作表，并基于原二维表生成一维表，如图3-127所示。

图3-127　创建新工作表

选中A1：D10区域，单击“设计→表格样式→无”。点击“工具→转换为区域”，如图3-128所示。

图3-128　数据转换

弹出对话框点击“是”，二维表转为一维表，如图3-129所示。根据数据处理的具体需求可以删除多余的D列。

图3-129　转换结果