Hàm thống kê thông tin từ bảng về 1 ô (1 người xem)

[Ngoc Nguyen X]

Nhờ cả nhà giúp em bài toán này, em có 1 bảng dạng như kiểu 1 bảng pivot, em muốn gộp lại thông tin của bảng này về 1 ô với điều kiện chỉ lấy về những title của cột có giá trị khác 0 (hoặc khác blank) thu về 1 ô, tương ứng với từng dòng.
Em giải thích hơi khó hiểu nhưng em có up file đính kèm lên và có ghi chú trong file đính kèm, nên các bác xem file đính kèm sẽ hiểu được yêu cầu của em ạ. Hiện tại em chỉ làm được với những site (dòng) có duy nhất 1 item (cột) đáp ứng điều kiện trên bằng cách dùng hàm indirect như trong file đính kèm em có viết, còng các site có số lượng item >1 thì em không làm được.

Em cảm ơn cả nhà.

 

[vanthinh3101]

Nhờ cả nhà giúp em bài toán này, em có 1 bảng dạng như kiểu 1 bảng pivot, em muốn gộp lại thông tin của bảng này về 1 ô với điều kiện chỉ lấy về những title của cột có giá trị khác 0 (hoặc khác blank) thu về 1 ô, tương ứng với từng dòng.
Em giải thích hơi khó hiểu nhưng em có up file đính kèm lên và có ghi chú trong file đính kèm, nên các bác xem file đính kèm sẽ hiểu được yêu cầu của em ạ. Hiện tại em chỉ làm được với những site (dòng) có duy nhất 1 item (cột) đáp ứng điều kiện trên bằng cách dùng hàm indirect như trong file đính kèm em có viết, còng các site có số lượng item >1 thì em không làm được.

Em cảm ơn cả nhà.

Excel 2016 trở lên thì dùng hàm TEXTJOIN bạn nhé!

C3=TEXTJOIN(",",TRUE,IF($D3:$DV3<>0,$D$2:$DV$2,""))

Ctrl + Shift + Enter
Fill xuống.

 

[Ngoc Nguyen X]

Em chào anh chị,

Em có file dữ liệu như file đính kèm, dạng pivot, em cần gộp thông tin thỏa mãn điều kiện về 1 ô ở cột C tương ứng với từng site, chi tiết như file đính kèm.

Em nhờ anh chị code giúp em với ạ, em chưa tìm được câu trả lời cho bài toán này.

Em cảm ơn nhiều.

 

[Maika8008]

Em chào anh chị,

Em có file dữ liệu như file đính kèm, dạng pivot, em cần gộp thông tin thỏa mãn điều kiện về 1 ô ở cột C tương ứng với từng site, chi tiết như file đính kèm.

Em nhờ anh chị code giúp em với ạ, em chưa tìm được câu trả lời cho bài toán này.

Em cảm ơn nhiều.

Bạn thử file nhé!

 

[be_09]

Excel 2016 trở lên thì dùng hàm TEXTJOIN bạn nhé!

C3=TEXTJOIN(",",TRUE,IF($D3:$DV3<>0,$D$2:$DV$2,""))

Ctrl + Shift + Enter
Fill xuống.

1/ Yêu cầu của chủ Topic là thay các con số trong bảng bằng tiêu đề dòng 2 từ D2: DV2, rồi mới dùng hàm nối chuổi từ C3: C5245. Nhưng chủ Topic lại không giải thích rõ.
2/ Nếu đúng vậy thì chắc phải dùng VBA, chứ duyệt qua gần 645.000 Cell hàm chắc chịu không nổi.

 

[vanthinh3101]

1/ Yêu cầu của chủ Topic là thay các con số trong bảng bằng tiêu đề dòng 2 từ D2: DV2, rồi mới dùng hàm nối chuổi từ C3: C5245. Nhưng chủ Topic lại không giải thích rõ.
2/ Nếu đúng vậy thì chắc phải dùng VBA, chứ duyệt qua gần 645.000 Cell hàm chắc chịu không nổi.

Em đọc kỹ lại thì cũng hiểu như bác nói.
Đúng là phải làm thêm 1 bước rồi mới nối chuỗi.
Vậy trường hợp này chắc chỉ có VBA trị thôi.

 

[HUONGHCKT]

Nhờ cả nhà giúp em bài toán này, em có 1 bảng dạng như kiểu 1 bảng pivot, em muốn gộp lại thông tin của bảng này về 1 ô với điều kiện chỉ lấy về những title của cột có giá trị khác 0 (hoặc khác blank) thu về 1 ô, tương ứng với từng dòng.
Em giải thích hơi khó hiểu nhưng em có up file đính kèm lên và có ghi chú trong file đính kèm, nên các bác xem file đính kèm sẽ hiểu được yêu cầu của em ạ. Hiện tại em chỉ làm được với những site (dòng) có duy nhất 1 item (cột) đáp ứng điều kiện trên bằng cách dùng hàm indirect như trong file đính kèm em có viết, còng các site có số lượng item >1 thì em không làm được.

Em cảm ơn cả nhà.

Bạn thử dùng hàm UDF này xem sao.
Vào trang tính Check Final Data=> bỏ dấu nháy trước dấu bằng Ô C3 và Enter.
Copy xuống dưới.

Function ThongKeCot(target As Range, Rng As Range)
Dim i&, J&, Lr&
Dim Arr(), KQ()
J = target.Row
Arr = Rng.Value
For i = 1 To UBound(Arr, 2)
    If Arr(J - 1, i) <> 0 Then
    If ThongKeCot = Empty Then ThongKeCot = Arr(1, i) Else ThongKeCot = ThongKeCot & ", " & Arr(1, i)
    End If
Next i
End Function

 

[Ngoc Nguyen X]

Bạn thử dùng hàm UDF này xem sao.
Vào trang tính Check Final Data=> bỏ dấu nháy trước dấu bằng Ô C3 và Enter.
Copy xuống dưới.

Function ThongKeCot(target As Range, Rng As Range)
Dim i&, J&, Lr&
Dim Arr(), KQ()
J = target.Row
Arr = Rng.Value
For i = 1 To UBound(Arr, 2)
    If Arr(J - 1, i) <> 0 Then
    If ThongKeCot = Empty Then ThongKeCot = Arr(1, i) Else ThongKeCot = ThongKeCot & ", " & Arr(1, i)
    End If
Next i
End Function

Thanks bác, em thấy là code này dùng được, nhưng rất chậm ạ. Dữ liệu của em có thể lên đến hàng 10k hoặc thậm chí hàng 100k dòng

Bài đã được tự động gộp: 1/9/21

1/ Yêu cầu của chủ Topic là thay các con số trong bảng bằng tiêu đề dòng 2 từ D2: DV2, rồi mới dùng hàm nối chuổi từ C3: C5245. Nhưng chủ Topic lại không giải thích rõ.
2/ Nếu đúng vậy thì chắc phải dùng VBA, chứ duyệt qua gần 645.000 Cell hàm chắc chịu không nổi.

Dạ đúng rồi bác ạ, tại em cũng không biết giải thích cụ thể nên em có đưa ví dụ ra kết quả vào trong file đính kèm đấy ạ

 

[Hoàng Tuấn 868]

nhưng rất chậm ạ.

theo bạn thì thời gian hết bao nhiêu sẽ là nhanh nhỉ.

 

[Ngoc Nguyen X]

Bạn thử file nhé!

Tuyệt vời bác ạ, excel thật vi diệu. Em cảm ơn bác nhiều

 

[Ngoc Nguyen X]

theo bạn thì thời gian hết bao nhiêu sẽ là nhanh nhỉ.

Từ lúc em reply bác là em đang chạy thử, đến giờ file mới chạy xong. Chắc mất khoảng hơn 5' treo excel cho việc này bác ạ. Nhưng đây mới là một dữ liệu nhỏ, dự án của em còn nhiều dữ liệu cần tổng hợp lớn hơn.

À, có bác trên này đã code giúp em rồi ạ, rất nhanh. Em paste code đây các bác tham khảo nhé.

Em cảm ơn các bác nhiều.

Option Explicit

Sub Ghi()
Dim arrS, arrR
Dim i&, j&, k&

arrS = Range(Cells(2, 4), Cells(Range("A" & Rows.Count).End(xlUp).Row, 126))
ReDim arrR(1 To UBound(arrS) - 1, 1 To 1)

For i = 2 To UBound(arrS)
For j = 1 To UBound(arrS, 2)
If arrS(i, j) <> 0 Then
arrR(i - 1, 1) = arrR(i - 1, 1) & arrS(1, j) & ", "
End If
Next
If Len(arrR(i - 1, 1)) > 0 Then arrR(i - 1, 1) = Left(arrR(i - 1, 1), Len(arrR(i - 1, 1)) - 2)
Next
Range("C3").Resize(UBound(arrR), 1).ClearContents
Range("C3").Resize(UBound(arrR), 1) = arrR
End Sub

 

[HUONGHCKT]

Thanks bác, em thấy là code này dùng được, nhưng rất chậm ạ. Dữ liệu của em có thể lên đến hàng 10k hoặc thậm chí hàng 100k dòng

Bài đã được tự động gộp: 1/9/21

Không biết bạn đã thử chưa và xem lại kỹ code chưa.
Hàm UDF này duyệt từng cột của 1 dòng (dòng chứa công thức) nên chạy cũng nhanh như trong trường hợp của đề bài- các trường hợp khác số lượng cột rất rất nhiều thì tôi không dám khẳng định. chứ nhiều dòng thì chỉ lúc fildown thì mới có vấn đề -tuy nhiên cũng có thể chấp nhận được Khi fildown thì phải click vào copy cell và chờ code chạy lâu chậm cũng có thể do có nhiều Cell <> empty trong một dòng.

 

[Ngoc Nguyen X]

Không biết bạn đã thử chưa và xem lại kỹ code chưa.
Hàm UDF này duyệt từng cột của 1 dòng (dòng chứa công thức) nên chạy cũng nhanh như trong trường hợp của đề bài- các trường hợp khác số lượng cột rất rất nhiều thì tôi không dám khẳng định. chứ nhiều dòng thì chỉ lúc fildown thì mới có vấn đề -tuy nhiên cũng có thể chấp nhận được Khi fildown thì phải click vào copy cell và chờ code chạy lâu chậm cũng có thể do có nhiều Cell <> empty trong một dòng.

Em thử luôn mà bác, nãy bác đưa code lên em tải về và làm luôn, function này rất nhanh khi em làm thử vài dòng, nhưng khi em double click cho xuống hết các dòng thì excel calculate mỗi giây lên 1% bác ạ.

Bài đã được tự động gộp: 1/9/21

Excel 2016 trở lên thì dùng hàm TEXTJOIN bạn nhé!

C3=TEXTJOIN(",",TRUE,IF($D3:$DV3<>0,$D$2:$DV$2,""))

Ctrl + Shift + Enter
Fill xuống.

TEXTJOIN ổn đấy bác, ra kết quả đúng bác ạ, nhanh nữa, em lại biết thêm 1 hàm tuyệt vời, thanks bác.

 

[vanthinh3101]

Em thử luôn mà bác, nãy bác đưa code lên em tải về và làm luôn, function này rất nhanh khi em làm thử vài dòng, nhưng khi em double click cho xuống hết các dòng thì excel calculate mỗi giây lên 1% bác ạ.

Bài đã được tự động gộp: 1/9/21

TEXTJOIN ổn đấy bác, ra kết quả đúng bác ạ, nhanh nữa, em lại biết thêm 1 hàm tuyệt vời, thanks bác.

Ô thế là đúng luôn yêu cầu rồi à?
Tôi tưởng còn phải thay giá trị vào các ô đã rồi mới nối chuỗi?

 

[Ngoc Nguyen X]

Ô thế là đúng luôn yêu cầu rồi à?
Tôi tưởng còn phải thay giá trị vào các ô đã rồi mới nối chuỗi?

Không bác, em chỉ lấy title của các ô từ DV mà có giá trị <>0 thôi bác, nếu lấy giá trị tương ứng thì em unpivot ra được bác ạ.
Em nãy cũng chưa hiểu ý bác nói
Thanks bác

 

[be_09]

Em thử luôn mà bác, nãy bác đưa code lên em tải về và làm luôn, function này rất nhanh khi em làm thử vài dòng, nhưng khi em double click cho xuống hết các dòng thì excel calculate mỗi giây lên 1% bác ạ.

Bài đã được tự động gộp: 1/9/21

TEXTJOIN ổn đấy bác, ra kết quả đúng bác ạ, nhanh nữa, em lại biết thêm 1 hàm tuyệt vời, thanks bác.

Xem khúc dưới chưa, tới dòng 3022 là nó đơ rồi.

 

[Ngoc Nguyen X]

Xem khúc dưới chưa, tới dòng 3022 là nó đơ rồi.

File của mình chạy hàm này ok mà, không đơ đâu bạn

 

[BuiQuangThuan]

Em chào anh chị,

Em có file dữ liệu như file đính kèm, dạng pivot, em cần gộp thông tin thỏa mãn điều kiện về 1 ô ở cột C tương ứng với từng site, chi tiết như file đính kèm.

Em nhờ anh chị code giúp em với ạ, em chưa tìm được câu trả lời cho bài toán này.

Em cảm ơn nhiều.

Thử 1 cách khác

Sub ABC()
Dim arr(), res(), iR&, iC&, i&, j&, tmp, k&
With Sheet1
    iR = .Range("A" & Rows.Count).End(3).Row
    iC = .Range("A2").End(2).Column
    arr = .Range("D2").Resize(iR - 1, iC - 3).Value
End With
ReDim res(1 To UBound(arr, 1), 1 To 1)
For i = 2 To UBound(arr, 1)
    k = k + 1
    For j = 1 To UBound(arr, 2)
        If arr(i, j) <> Empty Then
            tmp = tmp & "," & arr(1, j)
            res(k, 1) = Mid(tmp, 2, Len(tmp) - 1)
        End If
    Next
    tmp = Empty
Next
Sheet1.Range("C3").Resize(k).Value = res
End Sub

 

[ptm0412]

Tôi vừa gộp 2 chủ đề lại. Theo nội quy thì xoá nhưng cả 2 đều đã có bài trả lời. Tác giả Ngoc Nguyen X phải lưu ý.

 

[Ngoc Nguyen X]

Tôi vừa gộp 2 chủ đề lại. Theo nội quy thì xoá nhưng cả 2 đều đã có bài trả lời. Tác giả Ngoc Nguyen X phải lưu ý.

Vâng, em sẽ lưu ý nội quy diễn đàn lần sau. Thanks bác