【Excel系列】Excel数据分析:抽样设计

一、随机数发生器

1. 随机数发生器主要功能

“随机数发生器”分析工具可用几个分布之一产生的独立随机数来填充某个区域。可以通过概率分布来表示总体中的主体特征。例如,可以使用正态分布来表示人体身高的总体特征,或者使用双值输出的伯努利分布来表示掷币实验结果的总体特征。

2. 随机数发生器对话框简介

随机数发生器对话框

该对话框中的参数随分布的选择而有所不同,其余均相同。

变量个数:在此输入输出表中数值列的个数。 随机数个数:在此输入要查看的数据点个数。每一个数据点出现在输出表的一行中。 分布:在此单击用于创建随机数的分布方法。包括以下几种:均匀分布、正态分布、伯努利分布、二项式、泊松、模式、离散。 随机数基数:在此输入用来产生随机数的可选数值。可在以后重新使用该数值来生成相同的随机数。 输出区域:在此输入对输出表左上角单元格的引用。如果输出表将替换现有数据,Excel 会自动确定输出区域的大小并显示一条消息。 新工作表:单击此选项可在当前工作簿中插入新工作表,并从新工作表的 A1 单元格开始粘贴计算结果。若要为新工作表命名,请在框中键入名称。 新工作簿:单击此选项可创建新工作簿并将结果添加到其中的新工作表中。

3. 随机数发生器应用举例

3.1 均匀随机数的产生

均匀:以下限和上限来表征。其变量是通过对区域中的所有数值进行等概率抽取而得到的。普通的应用使用范围 0 到 1 之间的均匀分布。相当于工作表函数:“= a+RAND()*(b-a)”,与RANDBETWEEN (a,b)”的区别是,RANDBETWEEN产生的是离散型随机数,而随机数发生器产生的是连续型随机数。

离散型函数产生可重复随机数,若想产生无重复随机数,应使用连续型,再从中利用RANK函数产生整型。通常在进行抽样设计时要产生无重复的整型均匀随机数。

例:在编号为1至20之间随机抽取10个无重复的均匀随机数。

均匀随机数对话框

单击“确定”生成连续型随机数(如图)。

产生随机数

由图可见,所产生的是连续型随机数,若四舍五入取整,在B1单元格输入公式“=ROUND(A1,0)”,并复制到B1:B10,得到整型随机数。

由图可见,数字13出现了两次,为可重复随机数。在统计调查时,不能对同一调查对象调查两次,应产生无重复随机数。处理的办法如下:

在A列对总体进行编号;在B2输入公式“=RAND()”,生产0至1之间的均匀随机数,并复制到B3:B21;C列显示样本序号;选择D2:D11单元格区域,在D2单元格输入公式“=RANK(B2:B21,B2:B21)”,按住Ctrl+Shift不放再按回车键,生成随机数。该随机数是无重复的。当然也可由VLOOKUP函数实现,所处从略。

无重复随机数的产生

3.2 正态随机数的产生

正态分布描述:

正态分布描述

正态:以平均值和标准偏差来表征,相当于工作表函数“=NORMINV(rand(),mu,sigma)”

例:产生10行8列来自均值为100、标准差为10的总体随机数。

随机数发生器选择“分布”为“正态”,设置对话框如下:

随机数发生器对话框的正态分布设置

单击“确定”生成随机数如下:

产生的正态分布随机数

3.3 产生0-1分布随机数

伯努利:以给定的试验中成功的概率(p 值)来表征。伯努利随机变量的值为 0 或 1。等价于函数:“=IF(RAND())”.

例:产生5列10行的成功概率为0.5的0-1随机数。验证概率的频率法定义。

随机数发生器“分布”选择柏努利,设置对话框如下:

0-1随机数对话框

单击“确定”生成随机数。

在G列输入累积的试验次数;H2输入公式,统计正态朝上的次数(1的个数);I2求得频率(=H2/G2);将H2:I2复制到H3:I21单元格区域。

以H列为横坐标,I列为纵坐标,绘制不带标志点的折线型散点图。由图可见,随机试验次数的增加,频率逐步趋于0.5

频率法概率定义的验证

3.4 产生二项分布随机数

二项式:以一系列试验中成功的概率(p 值)来表征。例如,可以按照试验次数生成一系列伯努利随机变量,这些变量之和为一个二项式随机变量。

二项分布描述:

二项分布描述

例:某射手中靶的概率为0.8,每次射击10发子弹,射击10次,模拟每次中靶的次数。

随机数发生器选择“分布”为“二项”,设置对话框如下:

随机数发生器对话框的二项分布设置

单击“确定”生成随机数如下:

产生的二项分布随机数

3.5 产生泊松分布随机数

泊松:以值 λ 来表征,λ 等于平均值的倒数。泊松分布经常用于表示单位时间内事件发生的次数,例如,汽车到达收费停车场的平均速率。其描述如下:

泊松分布描述

例:某加油站,平均每小时前来加油的车辆为10辆,试进行100次模拟,并求其分布情况。

随机数发生器选择“分布”为“泊松”,设置对话框如下:

随机数发生器对话框的泊松分布设置

单击“确定”生成随机数如下:

产生的泊松分布随机数

求得最大值,最小值,确定组限,利用frequency函数统计频数,并求频率如下图。选择P2:P10单元格区域,在P2单元格输入公式“=FREQUENCY(A1:J10,O2:O10)”,同时按ctrl+shift+enter:

频数统计

3.6 产生重复序列

模式:以下界和上界、步幅、数值的重复率和序列的重复率来表征。在生物遗传学中常用到重复序列。EXCEL的“模式”所产生的重复序列是按相同步长产生的重复序列。

如:下列对话框设置:

重复序列对话框

可产生的重复序列为:112233112233112233

3.7 产生离散随机数

离散:以数值及相应的概率区域来表征。该区域必须包含两列,左边一列包含数值,右边一列为与该行中的数值相对应的发生概率。所有概率的和必须为 1。

例如:某商品销售情况根据某段时期统计如下(经验分布):

试进行80次模拟。

(1)在A列和B列输入参数(经验分布)

(2)随机数发生器选择“离散”,设置如下:

离散分布对话框

(3)单击确定,在C1:M8产生80个随机数。

(4)对产生的随机数利用frequency函数统计频数,并求频率(略)。

备注:

数据文件:https://pan.baidu.com/s/1qYi35Y8

二、抽样

“抽样”分析工具以数据源区域为总体,从而为其创建一个样本。当总体太大而不能进行处理或绘制时,可以选用具有代表性的样本。如果确认数据源区域中的数据是周期性的,还可以仅对一个周期中特定时间段中的数值进行采样。例如,如果数据源区域包含季度销售量数据,则以四为周期进行采样,将在输出区域中生成与数据源区域中相同季度的数值。

1.随机抽样

(1)打开一张工作表,输入总体编号或总体标志值(本例A2:J11单元格区域,使用“填充”-“序列”可以快速生成该区域)。

随机抽样

(2)抽样对话框设置:

随机抽样对话框设置

单击“确定”生成随机样本。注意,该样本是可重复抽样,重复率与总体单位数成反比,与样本量成正比。

2.周期抽样

例:从1至10编号按固定周期间隔分别为2、3、4、5抽样。

周期抽样对话框设置

单击“确定”抽得样本(D列),取间隔依次取3、4、5,输出区域依次改为E2、F2、G2,得随机数如图。

周期抽取的样本

该种抽样类似等距抽样,但不同的是统计学中的等距抽样是在第1组进行简单随机抽样,以后的样本等于首样本位置依次加组距的k倍。

备注:

数据文件:https://pan.baidu.com/s/1o858nmM

原文发布于微信公众号 - 大数据杂谈(BigData07)

原文发表时间:2017-05-16

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏用户2442861的专栏

相似图片搜索的原理

你输入网片的网址,或者直接上传图片,Google就会找出与其相似的图片。下面这张图片是美国女演员Alyson Hannigan。

2551
来自专栏tkokof 的技术,小趣及杂念

数学笔记(三)之镜面矩阵

  镜面变换在游戏中并不少见,相关资料网上也俯拾即是,不过自己总是感觉略显生疏,在此简单一记,算作是加深印象吧~

981
来自专栏数说工作室

最重要的一集 | 【SAS Says·扩展篇】IML:6.作业

咱们的SAS矩阵交互语言IML就要完结了,本文将会用IML编一个回归程序。无论你需要一元的还是多元的回归模型,该程序都能计算出模型的系数、t检验以及t检验的p值...

3518
来自专栏生信小驿站

Python数据处理从零开始----第四章(可视化)(3)目录正文

plt.scatter相对于plt.plot的主要优势在于,前者在创建散点图时具有更高的灵活性,可以单独控制每个散点与数据匹配,也可以让每个散点具有不同的属性(...

812
来自专栏AI研习社

用 OpenCV 检测图像中各物体大小

在图像中测量物体的大小与计算从相机到物体之间的距离是相似的,在这两种情况下,我们需要定义一个比值,它测量每个给定指标的像素个数。

5531
来自专栏Petrichor的专栏

深度学习: sliding window (滑动窗口)

sliding window (滑动窗口) 在深度学习中得到了极其广泛的运用。从卷积层到池化层,都能看见它的身影。

3783
来自专栏数据小魔方

excel数据分析工具库系列三|回归分析

今天跟大家分享excel数据分析工具库系列三——回归分析! 主要内容有: 相关系数 协方差矩阵 回归 相关系数: 原数据区域是我用randbetween函数生成...

3877
来自专栏社区的朋友们

3D 图形学基础 (下)

本文主要针对一些对3D有兴趣的同学,普及图形学知识,不涉及深入的技术探讨和样例介绍。对于不是从事相关开发的同学也能了解相关的知识。

7620
来自专栏fangyangcoder

使用颜色空间进行图像分割

原文地址:https://realpython.com/python-opencv-color-spaces/

9753
来自专栏雷经纬的专栏

老司机带你检测相似图片

本文从从图片的dhash,ahash,phash,颜色分布向量到基于语义的sift,surf,gist特征,构建一套分层相似图片检测系统。本文致力于零基础单机快...

4.1K3

扫码关注云+社区

领取腾讯云代金券