Excel生成规范化数据 [英] Excel Generate Normalized Data

问题描述

我正在寻找生成这样的数据，其中给定两个平均值(平均点击)和(一段时间内的平均点击)将正确输出间隔的数据.

I am looking to generate data in which Given two averages (Average Click) and (Average Clicks over Time) will correctly output spaced out data.

很难解释，但最好举个例子.

Hard to explain, but best with an example.

给出以下数据:

Days: 10
Average Click Value: 3.5
Average Clicks over days: 0.7

然后您可以制作以下数据:

You could then make data such as:

Day 1   
Day 2   
Day 3   4
Day 4   
Day 5   
Day 6   
Day 7   
Day 8   
Day 9   3
Day 10  

第9天(3)和第3天(4)之间的平均值为3.5，而总平均值(10天中的7)为0.7.

Where the average between Day 9 (3) and Day 3 (4) would be 3.5, and the total average (7 over 10 days) is 0.7.

以下内容不有效，因为它的平均点击价值为3.5，但总体平均为1.4:

The following would NOT work because it has a click value average of 3.5, but an overall average of 1.4:

Day 1   4
Day 2   
Day 3   3
Day 4   
Day 5   
Day 6   4
Day 7   
Day 8   
Day 9   3
Day 10  

以下内容不有效，因为它的总体平均值为0.7，而点击值为1.75:

The following would NOT work because it has an overall average of 0.7 but a click value of 1.75:

Day 1   1
Day 2   
Day 3   2
Day 4   
Day 5   3
Day 6   
Day 7   
Day 8   
Day 9   1
Day 10  

点击值必须为整数，因此以下操作无效:

The click value must be a whole number, so the follow would NOT work:

Day 1   
Day 2   
Day 3   3.5
Day 4   
Day 5   
Day 6   
Day 7   
Day 8   
Day 9   3.5
Day 10  

每个元素之间的间距应该相当随机，但要遵循严格的偏差.该模式应类似于用户访问自己喜欢的网站的频率.

The spacing between each should be rather random but follow a tight deviation. The pattern should be similar to how often a user visits their favorite website.

我知道有时并不能完全准确，因为某些组合无法通过数学方法完成，但应尽可能接近.

I understand it will sometimes not be exact because some combinations cannot be done mathematically, but it should attempt to be as close as it possibly can.

使用excel是否可以使用这种类型的模式(可以使用VBA).

Is this type of pattern possible using excel (VBA can be used).

推荐答案

这是一个入门数组函数:您垂直输入该函数:选择行数=天数，输入该函数并按Ctrl-Shift-进入.随机程度由变化常数控制.

Here is a starter array function: you enter it vertically: select the number of rows = to the number of days, enter the function and press Ctrl-Shift-Enter. The degree of randomness is controlled by the Variation constant.

Function ClickSpacer(nDays As Long, ClickAvg As Double, ClicksPerDay As Double)

    Dim Spacing As Long
    Dim Clicks() As Long        ''' output
    Dim Total_Clicks As Double
    Dim nDaysClicked As Double
    Dim j As Long
    Dim ClicksSoFar As Long
    Dim RandSpacing As Long
    Dim RandClicks As Long
    Dim ClickOffset As Long
    ReDim Clicks(1 To nDays, 1 To 1)
    Const Variation As Double = 0.2

    Total_Clicks = Round(nDays * ClicksPerDay, 0)
    nDaysClicked = Round(Total_Clicks / ClickAvg, 0)

    Spacing = nDays / (nDaysClicked + 1)
    RandSpacing = Round(Spacing * Variation, 0) * 2
    ClickOffset = Spacing + Round(Rnd() * RandSpacing, 0) - Round(Rnd() * RandSpacing, 0)
    RandClicks = ClickAvg * Variation * 2

    For j = 1 To nDaysClicked
        If j > 1 Then ClickOffset = ClickOffset + Spacing + Round(Rnd() * RandSpacing, 0) - Round(Rnd() * RandSpacing, 0)
        If j = nDaysClicked Then
            Clicks(ClickOffset, 1) = Round((Total_Clicks - ClicksSoFar) / (nDaysClicked - j + 1), 0)
        Else
            Clicks(ClickOffset, 1) = Round((Total_Clicks - ClicksSoFar) / (nDaysClicked - j + 1) + (RandClicks * Rnd() - RandClicks * Rnd()), 0)
        End If
        ClicksSoFar = ClicksSoFar + Clicks(ClickOffset, 1)
    Next j

    ClickSpacer = Clicks
End Function

这篇关于Excel生成规范化数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！