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

Ngoc Nguyen X · 1/9/21

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 · 1/9/21

Ngoc Nguyen X đã viết:
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é!

PHP:

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

Ctrl + Shift + Enter
Fill xuống.

Ngoc Nguyen X · 1/9/21

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 · 1/9/21

Ngoc Nguyen X đã viết:
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 · 1/9/21

vanthinh3101 đã viết:
Excel 2016 trở lên thì dùng hàm TEXTJOIN bạn nhé!

PHP:

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/9/21

be_09 đã viết:
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 · 1/9/21

Ngoc Nguyen X đã viết:
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.

Mã:

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 · 1/9/21

HUONGHCKT đã viết:
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.

Mã:

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

be_09 đã viết:
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 · 1/9/21

Ngoc Nguyen X đã viết:
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 · 1/9/21

Maika8008 đã viết:
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 · 1/9/21

Hoàng Tuấn 868 đã viết:
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 · 1/9/21

Ngoc Nguyen X đã viết:
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 · 1/9/21

HUONGHCKT đã viết:
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

vanthinh3101 đã viết:
Excel 2016 trở lên thì dùng hàm TEXTJOIN bạn nhé!

PHP:

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 · 1/9/21

Ngoc Nguyen X đã viết:
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 · 1/9/21

vanthinh3101 đã viết:
Ô 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ừ D

V 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 · 1/9/21

Ngoc Nguyen X đã viết:
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 · 1/9/21

be_09 đã viết:
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 · 1/9/21

Ngoc Nguyen X đã viết:
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

Mã:

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 · 2/9/21

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 · 3/9/21

ptm0412 đã viết:
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

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

Người dùng đang xem chủ đề này

Thành viên mới

File đính kèm

Thành viên tích cực

Thành viên mới

File đính kèm

Thành viên gạo cội

File đính kèm

Biên Hòa, Đồng Nai

Thành viên tích cực

Zalo 0986997214

File đính kèm

Thành viên mới

Đăng ký hôm qua

Thành viên mới

Thành viên mới

Zalo 0986997214

Thành viên mới

Thành viên tích cực

Thành viên mới

Biên Hòa, Đồng Nai

File đính kèm

Thành viên mới

❆❆❆❆❆❆❆❆❆❆❆❆

Bad Excel Member

Thành viên mới

Sinh nhật lần 19 GPE