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.数据概括性度量：
集中趋势：
众数： Mo = mode(array) 计算一组数值型数据的众数。
中位数和分位数：Me = median(array) 计算一组数值型数据的中位数。
四分位数 QL = quartile(array, quart) 计算一组数值型数据的众数。(0,1,2,3,4)
平均数：平方平均数≥算术平均数(简单平均，加权平均)≥几何平均数≥调和平均数
5.EXCEL数据展示
excel通过：数据透视表+图表进行可视化展示，通过增加切片器使展示实现动态化。
分类数据图示：
条形图（纵置也称柱形图）：适用于数据对比
帕累托图：柱形各类占比，折线累积百分比。 市场占有率较为适用。
饼图（复式饼图）：分类对比
环形图（多环）：饼图升级版，同时展示不同feature的分类占比
数值型数据图示：
茎叶图：用于反映原始数据分布的图形，适用小量数据观察 （Excel不支持，SPSS可绘制）
箱线图：用于观察数据分布，及异常值筛查 （Excel不支持，SPSS可绘制）
直方图：主要用于展示分组数据，用面积来表示频率分布
线图：时间序列数据，常用，反映现象随时间的变化的特征
散点图：展示数据2个特征数据
气泡图：展示数据3个特征数据,(加上颜色可展示4个特征)
雷达图(蜘蛛图)：展示数据n个特征数据
