Nối (tổng hợp) dữ liệu theo điều kiện

[redevinte]

Gửi các anh chị em, tôi có một bảng dữ liệu lỗi phát sinh, và muốn nhờ anh chị em giúp công thức để tổng hợp những ngày phát sinh lỗi vượt giới hạn theo từng ca (mẫu và yêu cầu tôi có ghi chi tiết trong file gửi kèm). Mong anh chị em giúp đỡ (VBA hoặc công thức excel đều được). Xin cảm ơn mọi người!

 

[dhn46]

Bạn cũng biết về VBA vậy bạn dùng Code sau:

Sub Test()
Dim Arr, Res
Dim i As Long, j As Long
Arr = Range("A1:N" & Range("A65536").End(3).Row)
ReDim Res(1 To UBound(Arr, 1) - 3, 1 To 2)
For i = 4 To UBound(Arr, 1)
    For j = 2 To UBound(Arr, 2)
        If Arr(i, j) > Arr(3, j) Then
            If j / 2 - Int(j / 2) = 0 Then 'Ngay
                Res(i - 3, 1) = Res(i - 3, 1) & "," & Format(Arr(1, j), "d/m")
            Else 'dem
                Res(i - 3, 2) = Res(i - 3, 2) & "," & Format(Arr(1, j), "d/m")
            End If
        End If
    Next
    Res(i - 3, 1) = Right(Res(i - 3, 1), Len(Res(i - 3, 1)) - 1)
    Res(i - 3, 2) = Right(Res(i - 3, 2), Len(Res(i - 3, 2)) - 1)
Next
Range("O4").Resize(UBound(Arr, 1) - 3, 2) = Res
End Sub

 

[redevinte]

Bạn cũng biết về VBA vậy bạn dùng Code sau:

Sub Test()
Dim Arr, Res
Dim i As Long, j As Long
Arr = Range("A1:N" & Range("A65536").End(3).Row)
ReDim Res(1 To UBound(Arr, 1) - 3, 1 To 2)
For i = 4 To UBound(Arr, 1)
    For j = 2 To UBound(Arr, 2)
        If Arr(i, j) > Arr(3, j) Then
            If j / 2 - Int(j / 2) = 0 Then 'Ngay
                Res(i - 3, 1) = Res(i - 3, 1) & "," & Format(Arr(1, j), "d/m")
            Else 'dem
                Res(i - 3, 2) = Res(i - 3, 2) & "," & Format(Arr(1, j), "d/m")
            End If
        End If
    Next
    Res(i - 3, 1) = Right(Res(i - 3, 1), Len(Res(i - 3, 1)) - 1)
    Res(i - 3, 2) = Right(Res(i - 3, 2), Len(Res(i - 3, 2)) - 1)
Next
Range("O4").Resize(UBound(Arr, 1) - 3, 2) = Res
End Sub

Cảm ơn bạn đã hỗ trợ. Tôi chạy thử code trên có báo lỗi như hình ảnh bên dưới.
Nhờ bạn viết thành 1 hàm tự tạo được không, thực tế tôi có nhiều sheet nên muốn dùng công thức cho linh động.

 

[concogia]

Nhờ bạn viết thành 1 hàm tự tạo được không, thực tế tôi có nhiều sheet nên muốn dùng công thức cho linh động.

Một cách viết hàm tự tạo

Public Function DemND(VungA, VungB, Dk) As String
    Dim I, Kq, A
        For I = 1 To VungA.Columns.Count
            If VungA(2, I) = Dk Then
                If VungB(1, I) > VungA(3, I) Then
                    A = Day(VungA(1, I)) & "/" & Month(VungA(1, I))
                    Kq = IIf(Kq = "", A, Kq & ", " & A)
                 End If
            End If
        Next I
    DemND = Kq
End Function

 

[redevinte]

Một cách viết hàm tự tạo

Public Function DemND(VungA, VungB, Dk) As String
    Dim I, Kq, A
        For I = 1 To VungA.Columns.Count
            If VungA(2, I) = Dk Then
                If VungB(1, I) > VungA(3, I) Then
                    A = Day(VungA(1, I)) & "/" & Month(VungA(1, I))
                    Kq = IIf(Kq = "", A, Kq & ", " & A)
                 End If
            End If
        Next I
    DemND = Kq
End Function

Cảm ơn bạn! Theo file mẫu gửi thì kết quả đã đạt được. Nhưng trong vùng điều kiện đó thực tế còn một số hàng khác (do mẫu báo cáo như vậy), mình chỉ muốn bổ sung mục này vào phía sau để thống kê chứ không sửa báo cáo để các vùng điều kiện và lấy dự liệu này nằm cạnh nhau được. Bạn có thể hỗ trợ công thức có dạng kiểu như sau không: = DemND(Vùng 1, Phép so sánh với điều kiện 1, Vùng 2, Phép so sánh với điều kiện 2, Vùng lấy kết quả)
Theo đề bài này thì diễn giải như sau
=DemND(Vùng dữ liệu NG, >= Vùng giới hạn quản lý, Vùng dữ liệu ca, = Ô chứa chuỗi ca cần lấy, Vùng giữ liệu ngày để lấy kết quả)

 

[concogia]

Cảm ơn bạn! Theo file mẫu gửi thì kết quả đã đạt được. Nhưng trong vùng điều kiện đó thực tế còn một số hàng khác (do mẫu báo cáo như vậy), mình chỉ muốn bổ sung mục này vào phía sau để thống kê chứ không sửa báo cáo để các vùng điều kiện và lấy dự liệu này nằm cạnh nhau được. Bạn có thể hỗ trợ công thức có dạng kiểu như sau không: = DemND(Vùng 1, Phép so sánh với điều kiện 1, Vùng 2, Phép so sánh với điều kiện 2, Vùng lấy kết quả)
Theo đề bài này thì diễn giải như sau
=DemND(Vùng dữ liệu NG, >= Vùng giới hạn quản lý, Vùng dữ liệu ca, = Ô chứa chuỗi ca cần lấy, Vùng giữ liệu ngày để lấy kết quả)

Chưa hiểu hết, tạm thời thế này.
Chép code này vào:

Public Function DemND(VungLoi, GioiHan, VungDk, Dk, DlLay) As String
    Dim I, Kq, A
        For I = 1 To VungLoi.Columns.Count
            If VungDk(I) = Dk Then
                If VungLoi(I) > GioiHan(I) Then
                    A = Day(DlLay(I)) & "/" & Month(DlLay(I))
                    Kq = IIf(Kq = "", A, Kq & ", " & A)
                 End If
            End If
        Next I
    DemND = Kq
End Function

Công thức ở [O7]:

=demnd($B7:$M7,$B$5:$M$5,$B$3:$M$3,O$2,$B$1:$M$1)

Kéo sang phải, kéo xuống

 

[redevinte]

Chưa hiểu hết, tạm thời thế này.
...
Kéo sang phải, kéo xuống

Ra kết quả chuẩn rồi bạn (Đấy là bạn mới chưa hiểu hết, tạm thời thế này...)

 

[dhn46]

Cảm ơn bạn đã hỗ trợ. Tôi chạy thử code trên có báo lỗi như hình ảnh bên dưới.
Nhờ bạn viết thành 1 hàm tự tạo được không, thực tế tôi có nhiều sheet nên muốn dùng công thức cho linh động.
View attachment 164817

Bạn phải Copy vào Module mới chạy được code.

 

[phulien1902]

Bạn phải Copy vào Module mới chạy được code.

Code của em bị lỗi như bạn ấy nói đấy, anh thêm dòng:

On Error Resume Next

là chạy OK

 

[dhn46]

Code của em bị lỗi như bạn ấy nói đấy, anh thêm dòng:

On Error Resume Next

là chạy OK

Anh đã xóa phần chú thích chưa?

Khoảng dữ liệu chuẩn data mới được anh ah, hạn chế dùng bẫy lỗi vì cứ cho chạy cứ bỏ qua lỗi thì sai không biết đâu mà lần.

 

[ndu96081631]

Cảm ơn bạn! Theo file mẫu gửi thì kết quả đã đạt được. Nhưng trong vùng điều kiện đó thực tế còn một số hàng khác (do mẫu báo cáo như vậy), mình chỉ muốn bổ sung mục này vào phía sau để thống kê chứ không sửa báo cáo để các vùng điều kiện và lấy dự liệu này nằm cạnh nhau được. Bạn có thể hỗ trợ công thức có dạng kiểu như sau không: = DemND(Vùng 1, Phép so sánh với điều kiện 1, Vùng 2, Phép so sánh với điều kiện 2, Vùng lấy kết quả)
Theo đề bài này thì diễn giải như sau
=DemND(Vùng dữ liệu NG, >= Vùng giới hạn quản lý, Vùng dữ liệu ca, = Ô chứa chuỗi ca cần lấy, Vùng giữ liệu ngày để lấy kết quả)

Đối với bài toán nối chuỗi theo điều kiện, muốn "đa năng" bạn nen dùng hàm JoinText

Function JoinText(ByVal Delimiter As String, ParamArray Arrays()) As String
  Dim aTmp, arr(), Item, tmp As String
  Dim i As Long, n As Long
  'On Error Resume Next
  For i = LBound(Arrays) To UBound(Arrays)
    aTmp = Arrays(i)
    If Not IsArray(aTmp) Then aTmp = Array(aTmp)
    For Each Item In aTmp
      If TypeName(Item) <> "Error" Then
        tmp = CStr(Item)
        n = n + 1
        ReDim Preserve arr(1 To n)
        arr(n) = tmp
      End If
    Next
  Next
  If n Then JoinText = Join(arr, Delimiter)
End Function

Áp dụng tại O7

=JoinText(", ",IF(($B7:$M7>$B$5:$M$5)/($B$3:$M$3=O$2),TEXT($B$1:$M$1,"d/m"),NA()))

Bấm Ctrl + Shift + Enter để kết thúc rồi kéo fill sang phải và xuống dưới
Điều kiện nối chuỗi phức tạp kiểu gì, miễn là bạn có thể triển khai, phát biểu chúng là giải quyết được hết
(Na ná cách dùng của hàm SUMPRODUCT)