EXCEL与数据分析

EXCEL与数据分析

1.EXCEL数据获取

①数据导入

录入方式
从文件导入	支持txt，xml，csv等文本文件；数据对齐或者拥有分隔符,均能很好的导入
从数据库	支持自家公司的Access数据库，SQL Server数据库
从网页加载	可获取大多数网站网页数据，需进一步进行数据清洗

②数据快速录入

• 用于快速填充的自定义列表
• 填充柄快速填充+[ 右键菜单辅助]
• CTRL + ENTER 多表格快速录入
• 针对重复文本录入：右键菜单： ‘从下拉列表选择’
• 录入区域构建数据验证：序列
• 自定义数据格式：以简代繁，ex. [=1]“男”;[=0]“女” 以0,1代替实现快速录入
• 以宏VBA来替换重复性动作

2.EXCEL数据清洗

数据清洗
定位条件	定位：空值，公式，差异值，条件格式，数据验证等，快速定位空值，异常值，进行处理
查找替换	定向替换数据 ，使用时需确认是否需要 ‘单元格匹配’
选择性粘贴	可选择性粘贴数据；粘贴过程进行运算；跳过空值，行列转置
数据分列	通过步长，分隔符进行分列
数据类型转换	主要是文本与数值型转换，日期格式标准化

文本转换为数值常用方式：

• = A1 * 1
• = A1 / 1
• = A1 + 0
• = A1 - 0
• = – A1
• =VALUE(A1)

3.EXCEL数据分析

分析方式
合并计算	多工作表字段一致时，可以进行合并计算进行汇总
组合功能键	ALT+=: 汇总数据；CTAL+SHIFT+方向键 : 快速选中数据； SHIFT+F8: 开启‘添加选定模式’
函数公式	数学函数，统计函数，日期函数，文本函数，逻辑函数，查询函数
数组，数组公式	基于数组的快速运算Ctrl+Shift+Enter

常用函数：

统计函数	数学函数	日期函数	文本函数	逻辑函数	查询函数
max,min,average,sum	abs(number)取绝对值	now()当前日期时间	text(format,“your format”)格式化	if(logical_test,true,false)	column()列号
count(value1,value2…)计数	int(number)取整	today()当前日期	len(text)字符个数	and(logical1,logical2…)	row()行号
counta(value1,value2…)计数非空	mod(number,divisor) 取模	year(serial_number)取年	rept(text,num_times)重复文本	or(logical1,logical2…)	index(range,row,column)返回单元格内容
countif(criteria_range,criteria)条件计数	rand()随机数0~1	month(serial_number)取月	find(find_text,within_text,start_num)查询字符位置	not(logical)	offset(基点,行偏,列偏,行高,列高)范围取值
countifs(criteria_range1,criteria1,criteria_range2,criteria2…)多条件计数	randbetween(m,n)随机数m~n	day(serial_number)取日	choose(index,value1,value2…)根据index返回value		indirect(‘str’)字串引用
sumif(range,criteria,sum_range)条件求和	round(number,num_digits)四舍五入	date(year,month,day)合成日期序列	left(text,num_chars)左取字符		sumproduct(array1,array2…)数组乘积和
sumifs(sum_range,criteria_range1,criteria1,criteria_range2,criteria2…)多条件求和	roundup((number,num_digits))向上舍入	edate(start_date,months)	right(text,num_chars)右取字符		lookup(1,0/(array1*array2…),array)
frequency(data_arry,bins_arry)分段求频率	rounddown(number,num_digits)向下舍入	eomonth(start_date,months)月最后一日	mid(text,start_num,num_chars)取字符		vlookup(str,array,n,0/1)列查询
rank(number,ref,order)排名		datedif(start_date,end_date,unit)计算日期差	replace(old_text,start_num,num_chars,new_text)替换		hlookup(str,array,n,0/1)行查询
large(array,k)第k大的数			substitute(text,old_text,new_text,instance_num)替换指定字符		match(str,range,0/1)返回目标位置
small(array,k)第k小的数

常用计算函数：

LN(number)	计算自然对数
MDETERM(array)	计算方阵行列式
MINVERSE(array)	矩阵求逆
MMULT(array,array)	矩阵积
MULTINOMIAL(number1,number2,…)	多项式回归
MUNUIT(dimension)	生成指定维度单位矩阵
SQRT(number)	平方根
统计专用函数

快捷功能：
Excel提供的快捷排序，筛选。数据透视表功能。

4.EXCEL数据挖掘

1.数据概括性度量：

集中趋势：

众数： M_o = mode(array) 计算一组数值型数据的众数。
中位数和分位数：M_e = median(array) 计算一组数值型数据的中位数。
四分位数 Q_L = quartile(array, quart) 计算一组数值型数据的众数。(0,1,2,3,4)
平均数：平方平均数≥算术平均数(简单平均，加权平均)≥几何平均数≥调和平均数

5.EXCEL数据展示

excel通过：数据透视表+图表进行可视化展示，通过增加切片器使展示实现动态化。
分类数据图示：
条形图（纵置也称柱形图）：适用于数据对比
帕累托图：柱形各类占比，折线累积百分比。 市场占有率较为适用。
饼图（复式饼图）：分类对比
环形图（多环）：饼图升级版，同时展示不同feature的分类占比

数值型数据图示：
茎叶图：用于反映原始数据分布的图形，适用小量数据观察 （Excel不支持，SPSS可绘制）
箱线图：用于观察数据分布，及异常值筛查 （Excel不支持，SPSS可绘制）

直方图：主要用于展示分组数据，用面积来表示频率分布
线图：时间序列数据，常用，反映现象随时间的变化的特征
散点图：展示数据2个特征数据
气泡图：展示数据3个特征数据,(加上颜色可展示4个特征)
雷达图(蜘蛛图)：展示数据n个特征数据