vba - 分析数组中的数据，删除，然后重新分析VBA-6ren

vba - 分析数组中的数据，删除，然后重新分析VBA

转载作者：行者123 更新时间：2023-12-03 03:34:14

正如现在的代码，我可以分析数据集并根据存储在 LowConf 和 HighConf 中的值检测异常值，这些值基于数据集的大小。然而，我只能确定其中一个异常值。我想扩展代码或添加代码，以便我可以执行以下操作:

首先确定最外围的元素
判断是否为异常值，如果是，则从DataSet中删除该元素，如果不是，则结束程序。
更改样本大小(减去 1)并再次分析数据集以查找下一个最外围元素并循环 k 次。

我的想法是创建一个新数组，它是每个 DataSet 元素与平均值的距离(绝对值)，然后找到它的 UBound 并在下面的 If 语句中进行分析。我的问题是，一旦我确定它是否是异常值，如何返回 DataSet 中的相应元素并将其删除？如果是这样，有没有更简单的方法来解决这个问题？另外，我是编码新手，因此任何有关可接受格式/如何清理代码的提示也值得赞赏。

Sub CalculateOutliers()

    Dim n As Integer
    Dim mean As Double
    Dim SD As Double
    Dim k As Integer
    Dim DataSet As Variant
    Dim LowConf As Single
    Dim HighConf As Single


'--------------------------------------------------------
    DataSet = Selection.Value
'Copies highlighted data into DataSet variable
'Cell A1 is (1,1) Because it starts at 0 which is out of range
'--------------------------------------------------------



'--------------------------------------------------------
    n = Selection.CountLarge
'Counts number of entries
'--------------------------------------------------------



'--------------------------------------------------------
 'DEFINES 95(LowConf) AND 99(HighConf) PERCENT CONFIDENCES BASED ON
 'SAMPLE SIZE

        If n <= 5 Then

            LowConf = 1.72
            HighConf = 1.76
    End If

        If n = 6 Then

            LowConf = 1.89
            HighConf = 1.97

    End If

        If n = 7 Then

            LowConf = 2.02
            HighConf = 2.14

    End If

        If n = 8 Then

            LowConf = 2.13
            HighConf = 2.28

    End If

        If n = 9 Then

            LowConf = 2.21
            HighConf = 2.39

    End If

        If n = 10 Then

            LowConf = 2.29
            HighConf = 2.48

    End If

        If n = 11 Then

            LowConf = 2.36
            HighConf = 2.56

    End If

        If n = 12 Then

            LowConf = 2.41
            HighConf = 2.64

    End If

        If n = 13 Then

            LowConf = 2.46
            HighConf = 2.7

    End If

        If n = 14 Then

            LowConf = 2.51
            HighConf = 2.75

    End If

        If n = 15 Then

            LowConf = 2.55
            HighConf = 2.81

    End If

        If n = 16 Then

            LowConf = 2.59
            HighConf = 2.85

    End If

        If n = 17 Then

            LowConf = 2.62
            HighConf = 2.9

    End If

        If n = 18 Then

            LowConf = 2.65
            HighConf = 2.93

    End If

        If n = 19 Then

            LowConf = 2.68
            HighConf = 2.97

    End If

        If n = 20 Then

            LowConf = 2.71
            HighConf = 3

    End If

        If n = 21 Then

            LowConf = 2.73
            HighConf = 3.03

    End If

        If n = 22 Then

            LowConf = 2.76
            HighConf = 3.06

    End If

        If n = 23 Then

            LowConf = 2.78
            HighConf = 3.08

    End If

        If n = 24 Then

            LowConf = 2.8
            HighConf = 3.11

    End If

        If n = 25 Then

            LowConf = 2.82
            HighConf = 3.14

    End If

        If n = 26 Then

            LowConf = 2.84
            HighConf = 3.16

    End If

        If n = 27 Then

            LowConf = 2.86
            HighConf = 3.18

    End If

        If n = 28 Then

            LowConf = 2.88
            HighConf = 3.2

    End If

        If n = 29 Then

            LowConf = 2.89
            HighConf = 3.22

    End If

        If n = 30 Then

            LowConf = 2.91
            HighConf = 3.24

    End If

        If n <= 35 And n > 30 Then

            LowConf = 2.98
            HighConf = 3.32

    End If

        If n <= 40 And n > 35 Then

            LowConf = 3.04
            HighConf = 3.38

    End If

        If n <= 45 And n > 40 Then

            LowConf = 3.09
            HighConf = 3.44

    End If

        If n <= 50 And n > 45 Then

            LowConf = 3.13
            HighConf = 3.48

    End If

        If n <= 60 And n > 50 Then

            LowConf = 3.2
            HighConf = 3.56

    End If

        If n <= 70 And n > 60 Then

            LowConf = 3.26
            HighConf = 3.62

    End If

        If n <= 80 And n > 70 Then

            LowConf = 3.31
            HighConf = 3.67

    End If

        If n <= 90 And n > 80 Then

            LowConf = 3.35
            HighConf = 3.72

    End If

        If n <= 100 And n > 90 Then

            LowConf = 3.38
            HighConf = 3.75

    End If

        If n <= 150 And n > 100 Then

            LowConf = 3.52
            HighConf = 3.89

    End If

        If n <= 200 And n > 150 Then

            LowConf = 3.61
            HighConf = 3.98

    End If

        If n <= 300 And n > 200 Then

            LowConf = 3.72
            HighConf = 4.09

    End If

        If n <= 400 And n > 300 Then

            LowConf = 3.8
            HighConf = 4.17

    End If

        If n <= 500 And n > 400 Then

            LowConf = 3.86
            HighConf = 4.32

    End If

        If n > 500 Then

            MsgBox "Sample size cannot exceed 500."

    End If
'--------------------------------------------------------



'--------------------------------------------------------
        If n < 50 Then

            k = Int(n / 10)

        Else

            k = 5

    End If
'determines k = number of possible outliers
'--------------------------------------------------------



'--------------------------------------------------------
    mean = Application.WorksheetFunction.Average(DataSet)
'Calculates mean of Data Set
'--------------------------------------------------------



'--------------------------------------------------------
    SD = Application.WorksheetFunction.StDev(DataSet)
'Calculates Standard Deviation of Data Set
'--------------------------------------------------------



'--------------------------------------------------------


        For Each element In DataSet


            If (Abs(element - mean) / SD) > LowConf Then

                MsgBox "95% outlier: " & element


        End If

            If (Abs(element - mean) / SD) > HighConf Then

                MsgBox "99% outlier: " & element


        End If


        Next element
'--------------------------------------------------------


End Sub

更新:我已经弄清楚了以下代码块。这个循环应该可以工作，但现在我只需要弄清楚如何从数据集中删除可疑对象。一旦删除，它将循环返回并重新计算平均值、SD 和可疑值。我意识到我没有关于何时 UBound 等于 LBound 的代码，但这是我之后要处理的问题。使用此代码，Suspect 始终显示值 1。我不确定为什么会这样，也不确定 DataSet.Remove(Suspect) 是否有效。

Dim i As Long

    For i = 1 To k


'--------------------------------------------------------
    mean = Application.WorksheetFunction.Average(DataSet)
'Calculates mean of Data Set
'--------------------------------------------------------



'--------------------------------------------------------
    SD = Application.WorksheetFunction.StDev(DataSet)
'Calculates Standard Deviation of Data Set
'--------------------------------------------------------



'--------------------------------------------------------
    Dim Suspect As Double
        If (Abs(UBound(DataSet) - mean)) > (Abs(LBound(DataSet) - mean)) Then
            Suspect = UBound(DataSet)
        End If
        If (Abs(UBound(DataSet) - mean)) < (Abs(LBound(DataSet) - mean)) Then
            Suspect = LBound(DataSet)
        End If
'Defines what the most outlying value is
'--------------------------------------------------------



'--------------------------------------------------------
    Dim Retest As Boolean

            If (Abs(Suspect - mean) / SD) > LowConf Then

                MsgBox "95% outlier: " & Suspect
                Retest = True

        End If

            If (Abs(Suspect - mean) / SD) > HighConf Then

                MsgBox "99% outlier: " & Suspect
                Retest = True

        End If

            If Retest = True Then
                DataSet.Remove (Suspect)
            End If
   MsgBox Suspect
'--------------------------------------------------------
Next i

更新:我稍微更改了部分。我可以将其应用到最小值，但如何指定可疑的位置并将其用作删除和上移的范围？

                        Dim Retest As Boolean

                        If (Abs(Suspect - mean) / SD) > LowConf Then

                            MsgBox "95% outlier: " & Suspect
                            Retest = True

                    End If

                        If (Abs(Suspect - mean) / SD) > HighConf Then

                            MsgBox "99% outlier: " & Suspect
                            Retest = True

                    End If
                    Dim pos As Range

                        Set pos = Application.Match(Suspect, DataSet)
                        MsgBox pos
                        If Retest = True And Suspect = Application.WorksheetFunction.Max(DataSet) Then

                           Range(pos).Delete Shift:=xlUp

                        End If

最佳答案

这解决了有关如何清理代码的问题。包括空白在内，您有近 300 行代码(确定配置级别)，可以压缩为大约十几行。首先，定义一个函数:

Function ConfLevels(n As Long) As Variant
    Dim i As Long
    Dim cutpoints As Variant, lowconfs As Variant, highconfs As Variant

    cutpoints = Array(5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 35, 40, 45, 50, 60, 70, 80, 90, 100, 150, 200, 300, 400, 500)
    lowconfs = Array(1.72, 1.89, 2.02, 2.13, 2.21, 2.29, 2.36, 2.41, 2.46, 2.51, 2.55, 2.59, 2.62, 2.65, 2.68, 2.71, 2.73, 2.76, 2.78, 2.8, 2.82, 2.84, 2.86, 2.88, 2.89, 2.91, 2.98, 3.04, 3.09, 3.13, 3.2, 3.26, 3.31, 3.35, 3.38, 3.52, 3.61, 3.72, 3.8, 3.86)
    highconfs = Array(1.76, 1.97, 2.14, 2.28, 2.39, 2.48, 2.56, 2.64, 2.7, 2.75, 2.81, 2.85, 2.9, 2.93, 2.97, 3#, 3.03, 3.06, 3.08, 3.11, 3.14, 3.16, 3.18, 3.2, 3.22, 3.24, 3.32, 3.38, 3.44, 3.48, 3.56, 3.62, 3.67, 3.72, 3.75, 3.89, 3.98, 4.09, 4.17, 4.32)
    i = 0
    Do While n > cutpoints(i)
        i = i + 1
    Loop
    ConfLevels = Array(lowconfs(i), highconfs(i)) 
End Function

然后在主子中将所有代码替换为:

Dim levels As Variant 'in the declaration part, use a different name if you want

'if n > 500, handle error and exit sub
levels = ConfLevels(n)
LowConf = levels(0)
HignConf = levels(1)

您甚至可以跳过变量 LowConf 和 HighConf 并使用 levels(0) 和 levels(1) 直接。

如果您解释了所使用的启发式方法，则可以通过使用诸如 T.Inv.2T 等工作表函数来进一步缩短此时间。这些数字从何而来？

就代码的其余部分而言，合理的策略是编写一个递归函数，该函数输入 VBA 数组并返回删除了所有异常值的 VBA 数组(基本情况是返回数组不变)因为没有更多的异常值需要删除)。主子系统(实际上与电子表格和用户交互)可能相对较小。

关于vba - 分析数组中的数据，删除，然后重新分析VBA，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/44421289/

文章推荐： sql-server - SSIS Excel 到 CSV - 64 位 True

文章推荐： VBA使用行输入时如何从下面的行获取文本？

文章推荐： VBA 保存工作簿 - 替换同名文件时出错

iPhone:删除/删除 SQLite 数据库？
我知道如何通过iPhone开发创建sqlite数据库、向其中插入数据、删除行等，但我试图以编程方式删除整个数据库本身，但没有得到任何帮助。请有人指导我如何通过代码从设备中删除/删除整个 sqlite
teradata - 删除/删除 Teradata 中的数据库
请帮助指导如何在 Teradata 中删除数据库。当我运行命令DROP DATABASE database_name时，我收到错误消息: *** Failure 3552 Cannot DROP d
azure - 删除/删除 Azure 警报规则
Azure 警报规则的删除命令似乎不起作用，尝试了下面的方法，它返回状态为无内容，并且警报未被删除使用的命令Remove-AzAlertRule -ResourceGroup "RGName"-Na
elasticsearch - Elasticsearch 数据丢失/删除/删除
我在 flex 搜索中为大约50000个视频建立了索引，但是当它达到52000左右时，所有数据都被删除。嗯，这对我来说真的很奇怪，我没有为ES设置任何Heap大小或最小或最大大小的内存大小，因此它们没
python - Django:删除/删除 slug
我正在处理的问题是表单错误“输入由字母、数字、下划线或连字符组成的有效‘slug’。” 以下是我的表单字段验证: def clean_slug(self): slug = self.c
jQuery 删除 : $ ("..."). 删除() 与 .remove ("...")
阅读文档，我希望 $("#wrap2").remove(".error") 从中删除所有 .error 元素#wrap2。然而看看这个 JSFiddle: http://jsfiddle.net/h
php - 删除/删除 laravel 项目
嗨，我第一次尝试发现 laravel 我从 laravel 4.2 开始，我刚刚创建了一个新项目，但我误以为我写了这样的命令行 composer create-project laravel/lara
Apache 2.4 - 删除 |删除 |卸载
我已经在网上搜索了很长一段时间，但我找不到如何完全删除 apache 2.4 。使用: Windows 7 c:\apache24\ 我已经尝试了所有命令，但没有任何效果。 httpd -k shu
python - 删除/删除 pandas DataFrame 中任意列中具有特定字符串的行
可能是一个简单的答案，所以提前道歉(最少的编码经验)。我正在尝试从任何列中删除具有特定字符串(经济 7)的任何行，并且一直在尝试离开此线程: How to drop rows from pandas
c++ - 删除/删除 vector 项的最有效/最快的方法
有几种方法可以删除/移除 vector 中的项目。我有一个指针 vector ，我需要在类的析构函数中删除所有指针。什么是最有效/最快甚至最安全的方式？ // 1º std::for_each(v
linux - 删除 xinetd 导致 plesk 删除
我安装了一个 VNC 服务器并在某处阅读了我必须安装 xinetd 的信息。稍后我决定删除 VNC 服务器，所以我也删除了 xinetd。似乎 xinetd 删除了一些与 plesk 相关的文件，如果
android - 如何完全杀死/删除/删除/停止 AsyncTask
我制作了一个从我们的服务器下载视频的应用。问题是: 当我取消下载时，我打电话: myAsyncTask.cancel(true) 我注意到，myAsyncTask 并没有在调用取消时停止...我的 P
machine-learning - 删除/删除 Vertica 中的机器学习模型(如果存在)
是否可以在使用DELETE_MODEL删除模型之前检查模型是否存在我试图避免在尝试删除尚未创建的模型时收到错误消息。基本上我正在寻找对应的: DROP TABLE IF EXISTS 但对于模型。最
php - 如何使用 php 删除/删除 mySQL 中的特定表行？
我已经有了这个代码: 但它仍然会生成一个表行条目。我想做的是，当输入的数量为0时，表行将被删除。请耐心等待，因为我是 php 和 mySQL 编码新手。最佳答案您忘记执行查询。应该是 $que
c# - 删除 EWS 中的 Exchange 事件修改/删除(即恢复事件)
在 SharePoint 中，如果您删除/修改重复日历条目的单次出现，则不会真正删除/修改任何内容 - 相反，会创建一个新条目，告诉 SP 对于特定日期，该事件不存在或具有新参数. 因此，这可以通过删
javascript - Laravel 5.2 & Dropzone.js - 删除(删除)上传的图片
在 routes.php 中我有以下路由: Route::post('dropzone', ['as' => 'dropzone.upload', 'uses' => 'AdminPhotoContr
Node.JS app.get 错误..无法获取/删除/删除/15
在我的应用程序中，我正在尝试删除产品。当我第一次删除产品时，它会成功并且 URL 更改为/remove_category/15。我正在渲染到同一页面。现在，当我尝试删除另一个产品时，网址更改为/rem
bash - 匹配后如何使用 BSD/OS X sed 删除/删除 X 行
这个问题被问了很多次，但给出的答案都是 GNU sed 特定的。 sed -i '' "/${FIND}/,+2d""$FILE" 给出“预期的上下文地址”错误。有人可以给我一个例子，说明如何使用
javascript - Google Maps JavaScript API V3 - 卸载/解构/删除/删除
在使用 V3 API 时，我找不到任何方法来删除和清理 Google map 。我已经在 AJAX 站点中运行它，所以我想完全关闭它而无需重新加载页面。我希望有一个 .unload() 或 .de
sql-server - 如何创建一个可以创建/更改/删除 View 但不能表、可以读取数据但不能插入/更新/删除/截断数据的 Azure SQL 数据库用户？
是否可以创建一个 Azure SQL 数据库用户来执行以下操作: 针对所有表和 View 进行 SELECT 创建/更改/删除 View 但用户不应该不拥有以下权限: 针对任何表或 View 插入/更

行者123

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

vba - 分析数组中的数据，删除，然后重新分析VBA