Mong các bác giúp code về kiểm định outlier và xử lí missing data (1 người xem)

[NDKhoa01]

Chào các bác.


Xin các bác giúp em code về kiểm định outlier và xử lí missing data ạ.


1. Kiểm định outlier bằng phương pháp phi tham số:
- Kiểm định theo từng cột
- Tính giá trị tứ phân vị của dãy: Q1, Q3
- Tính t = Q3-Q1
- Tính giá trị thấp của biên L = Q1-1.5t
- Tính giá trị cao của biến U = Q3+1.5t
- Nếu trong dãy số có số nào thấp hơn L hay cao hơn U thì đó là outlier, cần xóa dữ liệu đó đi
- Ô outlier được tô màu đỏ
2. Xử lí missing data sau khi kiểm định outlier
- Những ô nào còn trống thì điền vào bằng giá trị trung bình của 5 giá trị trước đó trong cột (trừ ô đầu tiên thì tính bằng giá trị trung bình của 5 số sau nó)

Em xin cảm ơn các bác.

 

[NDKhoa01]

Các bác cho em hỏi đoạn code này của em bị sai ở đâu với ạ

Sub th() Dim size As Integer
Dim i As Integer
size = Application.WorksheetFunction.CountA(Columns("C"))
For i = 2 To size
If IsNull(Worksheets("Temperature").Cells(i, 3).Value) = True Then
Worksheets("Temperature").Cells(i, 3).Value = average(i)
End If
Next i
End Sub


Function average(i As Integer) As Integer
Dim arg1, arg2, arg3, arg4 As Integer

arg1 = Worksheets("Temperature").Cells(i - 1, 3).Value
arg2 = Worksheets("Temperature").Cells(i - 2, 3).Value
arg3 = Worksheets("Temperature").Cells(i + 1, 3).Value
arg4 = Worksheets("Temperature").Cells(i + 2, 3).Value
average = (arg1 + arg2 + arg3 + arg4) / 4

End Function

 

[gtri]

Các bác cho em hỏi đoạn code này của em bị sai ở đâu với ạ

đổi IsNull bằng isempty xem sao
---
Bài 1
Những số liệu rỗng ( nằm giữa 02 dấu "," ) có bị loại hay không?

 

[NDKhoa01]

đổi IsNull bằng isempty xem sao
---
Bài 1
Những số liệu rỗng ( nằm giữa 02 dấu "," ) có bị loại hay không?

Cảm ơn bác, code của em chạy được rồi

Có loại bác ạ.

 

[gtri]

Cảm ơn bác, code của em chạy được rồi

Có loại bác ạ.

Bài 1 có yêu cầu "Kiểm định theo cột", phải khẳng định cái này mới được, vì nếu có loại --->số liệu của từng "Temp" ( theo từng cột ) sẽ bị sai khác
---
Theo cột hay theo dòng là phải chắc cú mới code được chính xác

 

[NDKhoa01]

Bài 1 có yêu cầu "Kiểm định theo cột", phải khẳng định cái này mới được, vì nếu có loại --->số liệu của từng "Temp" ( theo từng cột ) sẽ bị sai khác
---
Theo cột hay theo dòng là phải chắc cú mới code được chính xác

Kiểm định theo từng cột bác ạ, số liệu từng Temp khác nhau không sao cả.

 

[gtri]

Kiểm định theo từng cột bác ạ, số liệu từng Temp khác nhau không sao cả.

Mới làm yêu cầu 1. bạn kiểm tra xem sao, nếu đúng, sẽ xử lý yêu cầu 2

Public Sub Tu_Phan_Vi()
Dim DL, Tam(), kq(), r As Long, c As Long, i As Long

Sheet1.UsedRange.Interior.ColorIndex = 0
DL = Sheet1.UsedRange
ReDim Tam(1 To UBound(DL) - 1), kq(1 To 5, 1 To UBound(DL, 2))

For c = 3 To UBound(DL, 2)
i = 0
For r = 2 To UBound(DL)
If DL(r, c) <> "" Then i = i + 1: Tam(i) = DL(r, c)
Next r

If i > 0 Then
kq(1, c) = Application.Quartile(Tam, 1)
kq(2, c) = Application.Quartile(Tam, 3)
kq(3, c) = kq(2, c) - kq(1, c)
kq(4, c) = kq(1, c) - 1.5 * kq(3, c)
kq(5, c) = kq(2, c) + 1.5 * kq(3, c)

For r = 2 To UBound(DL)
If DL(r, c) < kq(4, c) Or DL(r, c) > kq(5, c) Then
Sheet1.Cells(r, c).Interior.ColorIndex = 3
End If
Next r
End If

Next c
End Sub

 

[gtri]

Bổ sung file đính kèm

 

[NDKhoa01]

Mới làm yêu cầu 1. bạn kiểm tra xem sao, nếu đúng, sẽ xử lý yêu cầu 2

Public Sub Tu_Phan_Vi()
Dim DL, Tam(), kq(), r As Long, c As Long, i As Long

Sheet1.UsedRange.Interior.ColorIndex = 0
DL = Sheet1.UsedRange
ReDim Tam(1 To UBound(DL) - 1), kq(1 To 5, 1 To UBound(DL, 2))

For c = 3 To UBound(DL, 2)
i = 0
For r = 2 To UBound(DL)
If DL(r, c) <> "" Then i = i + 1: Tam(i) = DL(r, c)
Next r

If i > 0 Then
kq(1, c) = Application.Quartile(Tam, 1)
kq(2, c) = Application.Quartile(Tam, 3)
kq(3, c) = kq(2, c) - kq(1, c)
kq(4, c) = kq(1, c) - 1.5 * kq(3, c)
kq(5, c) = kq(2, c) + 1.5 * kq(3, c)

For r = 2 To UBound(DL)
If DL(r, c) < kq(4, c) Or DL(r, c) > kq(5, c) Then
Sheet1.Cells(r, c).Interior.ColorIndex = 3
End If
Next r
End If

Next c
End Sub

Không đúng hết bác ạ. Chỉ tô màu một số ô thôi

 

[gtri]

Không đúng hết bác ạ. Chỉ tô màu một số ô thôi

Chỉnh lại đoạn code

Public Sub Tu_Phan_Vi()
Dim DL, Tam(), kq(), r As Long, c As Long, i As Long

Sheet1.UsedRange.Interior.ColorIndex = 0
DL = Sheet1.UsedRange
ReDim kq(1 To 5, 1 To UBound(DL, 2))

For c = 3 To UBound(DL, 2)
i = 0
For r = 2 To UBound(DL)
If DL(r, c) <> "" Then
i = i + 1
ReDim Preserve Tam(i)
Tam(i) = DL(r, c)
End If
Next r

If i > 0 Then
kq(1, c) = Application.Quartile(Tam, 1)
kq(2, c) = Application.Quartile(Tam, 3)
kq(3, c) = kq(2, c) - kq(1, c)
kq(4, c) = kq(1, c) - 1.5 * kq(3, c)
kq(5, c) = kq(2, c) + 1.5 * kq(3, c)

For r = 2 To UBound(DL)
If DL(r, c) < kq(4, c) Or DL(r, c) > kq(5, c) Then
Sheet1.Cells(r, c).Interior.ColorIndex = 3
End If
Next r
End If

Next c
End Sub

---
Bạn kiểm tra bằng thủ công cột temp3 xem sao.
Liệu có phải nguyên nhân do lựa chọn biên L và U?

 

[NDKhoa01]

Có thể là do có những cột dữ liệu nhiễu nhiều quá nên giá trị tứ phân vị khá lớn nên cách này không phân biệt được

 

[gtri]

Có thể là do có những cột dữ liệu nhiễu nhiều quá nên giá trị tứ phân vị khá lớn nên cách này không phân biệt được

Có lẽ là không phải, vì khi nhập số liệu vào mảng Tam ( để đưa vào hàm quartile tính toán ) đã loại những ô rỗng như theo yêu cầu
hệ số 1.5 của bạn có phải là mặc định?

 

[gtri]

Bạn xem file đính kèm. Có tìm Max, Min cho mỗi cột và so sánh với biên L & U

 

[NDKhoa01]

Bạn xem file đính kèm. Có tìm Max, Min cho mỗi cột và so sánh với biên L & U

Cảm ơn bác đã nhiệt tình giúp đỡ, giá trị 1.5 đấy là em đọc tài liệu thấy thế.
Em nghĩ những temp này được đo cùng lúc và trong không gian giới hạn (trong 1 tòa nhà) nên outlier của temp này có thể xem là outlier của temp khác. vậy cần lấy L lớn nhất và U nhỏ nhất làm tiêu chuẩn để kiểm định.