Rút gọn dữ liệu trùng theo hàng ngang (theo 2 kiểu) (2 người xem)

Nguyễn Hồng Quang · 21/9/18

Mình xin Tóm lược sơ qua như sau: Trong file có bảng dữ liệu nhập xuất hàng hoá, đã qua 1 bước sử dụng pivot để làm báo cáo.
Tại sheet Pivot, vùng AC5:AH58 cũng đã liệt kê được đơn giá theo các lần nhập. Tuy nhiên vấn đề phát sinh là đến vùng AI5:AN58 mình cần rút gọn lại phần liệt kê đơn giá (loại bỏ các đơn giá trùng theo 2 kiểu khác nhau), nhưng chưa nghĩ được công thức . Rất mong các anh chị em và các bạn giúp đỡ mình công thức, hoặc code VBA cũng được.
Về các chi tiết , ngọn nghành mình đã nêu rõ trong 2 file tương ứng với 2 kiểu rút gọn, mọi người vui lòng mở file đọc và giúp mình với nhé.
Mình cảm thấy vui khi được các anh, chị em và các bạn bỏ chút thời gian quý báu để quan tâm tới vấn đề của mình //**/

Ba Tê · 21/9/18

Nguyễn Hồng Quang đã viết:
Mình xin Tóm lược sơ qua như sau: Trong file có bảng dữ liệu nhập xuất hàng hoá, đã qua 1 bước sử dụng pivot để làm báo cáo.
Tại sheet Pivot, vùng AC5:AH58 cũng đã liệt kê được đơn giá theo các lần nhập. Tuy nhiên vấn đề phát sinh là đến vùng AI5:AN58 mình cần rút gọn lại phần liệt kê đơn giá (loại bỏ các đơn giá trùng theo 2 kiểu khác nhau), nhưng chưa nghĩ được công thức . Rất mong các anh chị em và các bạn giúp đỡ mình công thức, hoặc code VBA cũng được.
Về các chi tiết , ngọn nghành mình đã nêu rõ trong 2 file tương ứng với 2 kiểu rút gọn, mọi người vui lòng mở file đọc và giúp mình với nhé.
Mình cảm thấy vui khi được các anh, chị em và các bạn bỏ chút thời gian quý báu để quan tâm tới vấn đề của mình

Làm file 1 kiểu
Kiểu kia chưa xem để hiểu khác với kiểu này chỗ nào.

Nguyễn Hồng Quang · 21/9/18

Ba Tê đã viết:
Làm file 1 kiểu
Kiểu kia chưa xem để hiểu khác với kiểu này chỗ nào.

cảm ơn anh Ba Tê, nhưng mà hơi ngược với file em gửi
Code của anh là giải cho kiểu 2 chứ không phải kiểu 1 anh à.
Trong file Kiểu 1-Rút gọn theo quy tắc là các đơn giá giống nhau mà nằm liên tiếp cạnh nhau thì dồn về 1 đơn giá, nếu không nằm cạnh nhau thì không dồn. Ví dụ dòng 5. Có AC5=AD5=47,641 thì dồn về thành 47,641 như ở AI5,còn AF5 tuy là cũng = 47,641, nhưng nằm cách 1 ô AD5 nên không dồn mà vẫn để lại như ở AK5
Hoặc như dòng 53. Có AC53=149,880 và AE53=AF53 cũng =149,880 nhưng vì cách 1 ô là AD53 nên chỉ dồn AE53 vâAF53 về thành 149,880 v..v các tình huống còn lại theo quy tắc trên
Mong anh giúp em theo kiểu này ạ

concogia · 21/9/18

Nguyễn Hồng Quang đã viết:
cảm ơn anh Ba Tê, nhưng mà hơi ngược với file em gửi
Code của anh là giải cho kiểu 2 chứ không phải kiểu 1 anh à.
Trong file Kiểu 1-Rút gọn theo quy tắc là các đơn giá giống nhau mà nằm liên tiếp cạnh nhau thì dồn về 1 đơn giá, nếu không nằm cạnh nhau thì không dồn. Ví dụ dòng 5. Có AC5=AD5=47,641 thì dồn về thành 47,641 như ở AI5,còn AF5 tuy là cũng = 47,641, nhưng nằm cách 1 ô AD5 nên không dồn mà vẫn để lại như ở AK5
Hoặc như dòng 53. Có AC53=149,880 và AE53=AF53 cũng =149,880 nhưng vì cách 1 ô là AD53 nên chỉ dồn AE53 vâAF53 về thành 149,880 v..v các tình huống còn lại theo quy tắc trên
Mong anh giúp em theo kiểu này ạ

Kiểu 1:

Mã:

Public Sub ChoiKieu1()
    Dim Vung, Kq, I, J, K
    Vung = [AC5:AH58]
    ReDim Kq(1 To UBound(Vung), 1 To UBound(Vung, 2))
        For I = 1 To UBound(Vung)
            K = K + 1: Kq(I, K) = Vung(I, 1)
            For J = 2 To UBound(Vung, 2)
                If Vung(I, J) = 0 Then Exit For
                    If Vung(I, J) <> Kq(I, K) Then
                        K = K + 1
                        Kq(I, K) = Vung(I, J)
                    End If
            Next J
            K = 0
        Next I
    [AO5:AT58] = Kq
End Sub

Kiểu 2:

Mã:

Public Sub ChoiKieu2()
    Dim Vung, Kq, I, J, K, DitTo
    Vung = [AC5:AH58]
    Set DitTo = CreateObject("scripting.dictionary")
    ReDim Kq(1 To UBound(Vung), 1 To UBound(Vung, 2))
        For I = 1 To UBound(Vung)
            For J = 1 To UBound(Vung, 2)
                If Vung(I, J) = 0 Then Exit For
                    If Not DitTo.exists(Vung(I, J)) Then
                        DitTo.Add Vung(I, J), ""
                        K = K + 1
                        Kq(I, K) = Vung(I, J)
                    End If
            Next J
            K = 0: DitTo.RemoveAll
        Next I
    [AO5:AT58] = Kq
End Sub

Phan Thế Hiệp · 21/9/18

Nguyễn Hồng Quang đã viết:
Mình xin Tóm lược sơ qua như sau: Trong file có bảng dữ liệu nhập xuất hàng hoá, đã qua 1 bước sử dụng pivot để làm báo cáo.
Tại sheet Pivot, vùng AC5:AH58 cũng đã liệt kê được đơn giá theo các lần nhập. Tuy nhiên vấn đề phát sinh là đến vùng AI5:AN58 mình cần rút gọn lại phần liệt kê đơn giá (loại bỏ các đơn giá trùng theo 2 kiểu khác nhau), nhưng chưa nghĩ được công thức . Rất mong các anh chị em và các bạn giúp đỡ mình công thức, hoặc code VBA cũng được.
Về các chi tiết , ngọn nghành mình đã nêu rõ trong 2 file tương ứng với 2 kiểu rút gọn, mọi người vui lòng mở file đọc và giúp mình với nhé.
Mình cảm thấy vui khi được các anh, chị em và các bạn bỏ chút thời gian quý báu để quan tâm tới vấn đề của mình

Công thức cho "Kiểu 1"

Mã:

AQ5=IFERROR(OFFSET($AC5,,AGGREGATE(15,6,COLUMN($A:$F)/SIGN(ABS($AC5:$AH5-$AD5:$AI5))/($AD5:$AI5>0),COLUMN(A$1))),"")

Enter, fill qua phải, rồi fill cả hàng xuống dưới.
Xem file kèm.

Thân

Nguyễn Hồng Quang · 21/9/18

concogia đã viết:

Kiểu 1:

Mã:

Public Sub ChoiKieu1()
    Dim Vung, Kq, I, J, K
    Vung = [AC5:AH58]
    ReDim Kq(1 To UBound(Vung), 1 To UBound(Vung, 2))
        For I = 1 To UBound(Vung)
            K = K + 1: Kq(I, K) = Vung(I, 1)
            For J = 2 To UBound(Vung, 2)
                If Vung(I, J) = 0 Then Exit For
                    If Vung(I, J) <> Kq(I, K) Then
                        K = K + 1
                        Kq(I, K) = Vung(I, J)
                    End If
            Next J
            K = 0
        Next I
    [AO5:AT58] = Kq
End Sub

Kiểu 2:

Mã:

Public Sub ChoiKieu2()
    Dim Vung, Kq, I, J, K, DitTo
    Vung = [AC5:AH58]
    Set DitTo = CreateObject("scripting.dictionary")
    ReDim Kq(1 To UBound(Vung), 1 To UBound(Vung, 2))
        For I = 1 To UBound(Vung)
            For J = 1 To UBound(Vung, 2)
                If Vung(I, J) = 0 Then Exit For
                    If Not DitTo.exists(Vung(I, J)) Then
                        DitTo.Add Vung(I, J), ""
                        K = K + 1
                        Kq(I, K) = Vung(I, J)
                    End If
            Next J
            K = 0: DitTo.RemoveAll
        Next I
    [AO5:AT58] = Kq
End Sub

Cảm ơn anh concogia, code của anh ra kết quả đúng ý em rồi và có 1 cái hay của code là biến Vung; biến Kq rất thuận tiện cho việc sửa đổi vùng dữ liệu, vùng trả kết quả. Những lúc bế tắc thế này gặp được sự giúp đỡ của các anh mới thấy thật là đáng trân trọng

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

Phan Thế Hiệp đã viết:
Công thức cho "Kiểu 1"

Mã:

AQ5=IFERROR(OFFSET($AC5,,AGGREGATE(15,6,COLUMN($A:$F)/SIGN(ABS($AC5:$AH5-$AD5:$AI5))/($AD5:$AI5>0),COLUMN(A$1))),"")

Enter, fill qua phải, rồi fill cả hàng xuống dưới.
Xem file kèm.

Thân

Cảm ơn a. Phan Thế Hiệp đã bổ túc thêm cho em 1 biểu thức mới khi đưa vào dùng kèm với hàm aggregate. Lúc mới nhìn vào thì hơi ngại vì hàm sign và abs này em ít gặp, phải lên google search rồi mới nhìn lại biểu thức thì mới hiểu và thấy được sự tài tình và ảo diệu của công thức anh lập. Ngoài ra nhờ anh ngó giúp em cái công thức loại bỏ trùng theo kiểu 2, em mày mò trên diễn đàn đem vào áp dụng trong file mà nó chưa ra được như ý (vì toàn bị thụt vào 1 ô)
AK5=IFERROR(INDEX(AGGREGATE(15;6;$AD5:$AJ5;ROW($1:$6));MATCH(0;INDEX(COUNTIF($AJ5:AJ5;AGGREGATE(15;6;$AD5:$AJ5;ROW($1:$6)))

;0));"") Khi Chạy ra Kết quả ở vùng AK5:AP58 đa số là bị thụt lùi vào 1 ô????

Phan Thế Hiệp · 21/9/18

Nguyễn Hồng Quang đã viết:
Ngoài ra nhờ anh ngó giúp em cái công thức loại bỏ trùng theo kiểu 2, em mày mò trên diễn đàn đem vào áp dụng trong file mà nó chưa ra được như ý (vì toàn bị thụt vào 1 ô)

Mã:

AK5=IFERROR(INDEX(AGGREGATE(15;6;$AD5:$AJ5;ROW($1:$6));MATCH(0;INDEX(COUNTIF($AJ5:AJ5;AGGREGATE(15;6;$AD5:$AJ5;ROW($1:$6)));0));"")

Khi Chạy ra Kết quả ở vùng AK5:AP58 đa số là bị thụt lùi vào 1 ô????

Anh cố tình không làm 'Kiểu 2' gửi lên cùng lúc 'Kiểu 1', vì anh biết chắc em sẽ làm được bài này.
Em tham khảo và so sánh cách của hai anh em, rồi tự em khắc biết điều chỉnh chỗ nào nha!

Chúc em ngày vui.
/-*+/

p/s:
- Link giải thích cú pháp hàm của MS.Excel

Theo thứ tự chữ cái: https://support.office.com/vi-vn/ar...efb-bb96-c6d90033e188?ui=vi-VN&rs=vi-VN&ad=VN
Theo thể loại: https://support.office.com/vi-vn/ar...2ea-9da1-fcfe585c79cc&ui=vi-VN&rs=vi-VN&ad=VN

Nguyễn Hồng Quang · 21/9/18

Phan Thế Hiệp đã viết:
Anh cố tình không làm 'Kiểu 2' gửi lên cùng lúc 'Kiểu 1', vì anh biết chắc em sẽ làm được bài này.
Em tham khảo và so sánh cách của hai anh em, rồi tự em khắc biết điều chỉnh chỗ nào nha!

Chúc em ngày vui.

Trước đây trong file thư viện của em có lưu trữ đoạn công thức na ná như của anh (chỉ khác là người ta áp dụng theo 1 cột), lúc sáng cũng lôi ra loay hoay mãi mà ko áp dụng được vào file theo hàng ngang.Thôi thì vừa là người anh cũng như là người thầy; em trăm sự nhờ anh giảng giải thêm giúp em về cách thức vận hành của đoạn công thức mà anh đưa ra được không a, để em hiểu thêm và áp dụng chính xác cho các vấn đề sau này. Em cảm ơn anh nhiều

Phan Thế Hiệp · 21/9/18

Nguyễn Hồng Quang đã viết:
Trước đây trong file thư viện của em có lưu trữ đoạn công thức na ná như của anh (chỉ khác là người ta áp dụng theo 1 cột), lúc sáng cũng lôi ra loay hoay mãi mà ko áp dụng được vào file theo hàng ngang.Thôi thì vừa là người anh cũng như là người thầy; em trăm sự nhờ anh giảng giải thêm giúp em về cách thức vận hành của đoạn công thức mà anh đưa ra được không a, để em hiểu thêm và áp dụng chính xác cho các vấn đề sau này. Em cảm ơn anh nhiều

Công thức cho Rút gọn 'Kiểu 1' hay 'Kiểu 2', hả em trai!?

Thân

Nguyễn Hồng Quang · 21/9/18

Phan Thế Hiệp đã viết:
Công thức cho Rút gọn 'Kiểu 1' hay 'Kiểu 2', hả em trai!?

Thân

Kiểu 2 ấy anh ơi
(Còn kiểu 1 em hiểu cái ảo diệu của công thức anh lập rồi)

lameco411 · 21/9/18

Nguyễn Hồng Quang đã viết:
Mình xin Tóm lược sơ qua như sau: Trong file có bảng dữ liệu nhập xuất hàng hoá, đã qua 1 bước sử dụng pivot để làm báo cáo.
Tại sheet Pivot, vùng AC5:AH58 cũng đã liệt kê được đơn giá theo các lần nhập. Tuy nhiên vấn đề phát sinh là đến vùng AI5:AN58 mình cần rút gọn lại phần liệt kê đơn giá (loại bỏ các đơn giá trùng theo 2 kiểu khác nhau), nhưng chưa nghĩ được công thức . Rất mong các anh chị em và các bạn giúp đỡ mình công thức, hoặc code VBA cũng được.
Về các chi tiết , ngọn nghành mình đã nêu rõ trong 2 file tương ứng với 2 kiểu rút gọn, mọi người vui lòng mở file đọc và giúp mình với nhé.
Mình cảm thấy vui khi được các anh, chị em và các bạn bỏ chút thời gian quý báu để quan tâm tới vấn đề của mình

Mình xin góp vui với kiểu một như sau:

Mã:

Public Sub xuly_dulieu()
Dim i As Long
Dim j As Long
Dim lr As Long
Dim c As Long
Dim b As Long
Dim k As Long
Dim tam() As Variant
lr = Sheets("Pivot").Range("AC" & Rows.Count).End(xlUp).Row
For i = 5 To lr
    c = WorksheetFunction.CountIf(Sheets("Pivot").Range("AC" & i & ":AH" & i), "<>0")
    If c = 0 Or c = 1 Then GoTo ABC
    ReDim tam(1 To c)
    tam(1) = Range("AC" & i).Value
    b = 2
    For j = 1 To c - 1
       If Range("AC" & i).Offset(0, j).Value - tam(b - 1) <> 0 Then
        tam(b) = Range("AC" & i).Offset(0, j).Value
        b = b + 1
       End If
    Next j
If b - 1 <> c Then
Sheets("Pivot").Range("AC" & i & ":AH" & i).ClearContents
For k = 1 To UBound(tam)
    Range("AC" & i).Offset(0, k - 1).Value = tam(k)
Next k
End If
ABC:
Next i
End Sub

Cách này xử lý tại chỗ từ cột AC đến cột AH luôn, không copy qua chỗ khác.

Nguyễn Hồng Quang · 21/9/18

lameco411 đã viết:

Mình xin góp vui với kiểu một như sau:

Mã:

Public Sub xuly_dulieu()
Dim i As Long
Dim j As Long
Dim lr As Long
Dim c As Long
Dim b As Long
Dim k As Long
Dim tam() As Variant
lr = Sheets("Pivot").Range("AC" & Rows.Count).End(xlUp).Row
For i = 5 To lr
    c = WorksheetFunction.CountIf(Sheets("Pivot").Range("AC" & i & ":AH" & i), "<>0")
    If c = 0 Or c = 1 Then GoTo ABC
    ReDim tam(1 To c)
    tam(1) = Range("AC" & i).Value
    b = 2
    For j = 1 To c - 1
       If Range("AC" & i).Offset(0, j).Value - tam(b - 1) <> 0 Then
        tam(b) = Range("AC" & i).Offset(0, j).Value
        b = b + 1
       End If
    Next j
If b - 1 <> c Then
Sheets("Pivot").Range("AC" & i & ":AH" & i).ClearContents
For k = 1 To UBound(tam)
    Range("AC" & i).Offset(0, k - 1).Value = tam(k)
Next k
End If
ABC:
Next i
End Sub

Cách này xử lý tại chỗ từ cột AC đến cột AH luôn, không copy qua chỗ khác.

Cảm ơn bạn lameco411 đã giúp đỡ, mình sẽ lưu trữ và áp dụng vào các tình huống thực tế sau này

Phan Thế Hiệp · 21/9/18

Nguyễn Hồng Quang đã viết:
Kiểu 2 ấy anh ơi
(Còn kiểu 1 em hiểu cái ảo diệu của công thức anh lập rồi)

1/ 'Kiểu 1': Còn có thể rút gọn không cần dùng SIGN((ABS()) (2 cái hàm này anh dùng theo thói quen và ngẫu hứng lúc tạo công thức), như sau:

AQ5=IFERROR(OFFSET($AC5,,AGGREGATE(15,6,COLUMN($A:$F)/($AC5:$AH5-$AD5:$AI5<>0)/($AD5:$AI5>0),COLUMN(A$1))),"")

2/ 'Kiểu 2':

AP5=IFERROR(INDEX($AC5:$AH5,MATCH(,INDEX(COUNTIF($AO5: AO5,$AC5: $AH5),),)),)

Dù kết quả là cột hay dòng, khi xử lý bài toán liệt kê danh sách loại bỏ dữ liệu trùng thường áp dụng công thức tổng quát:
=INDEX( 'Vùng dữ liệu' , MATCH( 0 , COUNTIF( 'Vùng lần lượt hiện kết quả' , 'Vùng dữ liệu' ) , 0 ) )
(hay có cách viết khác: =INDEX( 'Vùng dữ liệu' , MATCH( , INDEX( COUNTIF( 'Vùng lần lượt hiện kết quả' , 'Vùng dữ liệu' ) , ) , ) chỉ Enter kết thúc)

Công thức dưới đây quá quen thuộc, chắc em nhìn ra:
=INDEX( 'Vùng dữ liệu' , MATCH( 'Giá trị tìm' , 'Vùng giá trị cần so khớp' , 0 ))
Anh chỉ muốn nói thêm những điểm nhấn của dạng bài toán này:

'Vùng dữ liệu': Là Vùng (hoặc Mảng) 1 chiều, chứa dữ liệu có thể có trùng lắp cần loại ra. Tùy theo yêu cầu của đề bài, nếu chỉ lấy đúng theo thứ tự xuất hiện (từ trên xuống, hay từ trái qua phải) thì chỉ cần đưa Vùng/Mảng như tự nhiên của dữ liệu đang có, nếu yêu cầu còn phải sắp xếp thứ tự sau khi đã loại trùng thì mới kết hợp hàm khác như: Small(), Large() hay Aggregate(). Như bài của em hỏi thì chỉ cần đưa Vùng $AC5: $AH5 (kéo xuống thành $AC6: $AH6, và cứ tương tự như thế) là đủ không cần phối hợp thêm hàm Aggregate().
COUNTIF( 'Vùng lần lượt hiện kết quả' , 'Vùng dữ liệu' ): Đây là công thức "cốt lõi" để loại bỏ dữ liệu trùng lắp, là "biểu thức điều kiện" mà em có thể áp dụng trong bất cứ dạng công thức kết hợp khác nhưng muốn loại bỏ dữ liệu trùng lắp. Đơn giản nó là công thức Mảng: Countif( Range, Array ). Để ý em sẽ thấy rằng: các hàm họ Countif() hoặc Sumif() sẽ trả về kết quả đơn hay mảng đúng theo số phần tử và chiều (dọc hay ngang) có trong khai báo đối số Array trong các hàm đó.
- Ví dụ: như trong bài COUNTIF($AO5: AO5,$AC5: $AH5) sẽ tạo ra Mảng 1 chiều theo chiều ngang tương ứng với chiều dài các cột AC: AH.
- Do AO5 hiện là rỗng, nên Mảng trả về của COUNTIF($AO5: AO5,$AC5: $AH5) là {0,0,0,0,0,0}, dùng MATCH(0,{0,0,0,0,0,0},0) sẽ ra 1, ứng với giá trị ô AC5="47641" đem trả về kết quả đó tại ô AP5 nhờ vào Index(). (xem thêm công thức tại B2: G2 file đính kèm)
- Công thức qua ô AQ5, do AP5="47641", nên COUNTIF($AO5: AP5,$AC5: $AH5) <--> COUNTIF({"",47641},$AC5: $AH5) tạo nên 1 mảng {1,1,0,1,0,0}, dùng MATCH(0,{1,1,0,1,0,0},0) sẽ ra 3, ứng với giá trị ô AE5="52405" đem trả về kết quả đó tại ô AQ5 nhờ vào Index(). Để ý lại mảng trên em sẽ thấy giá trị "47641" tại AC5 và AD5 sẽ "được" đếm là 1, mà 'Giá trị tìm' của MATCH() là giá trị 0, nên giá trị 1 như vậy kể như "bị loại", không cần so khớp nữa. (xem thêm công thức tại B3: G3 file đính kèm).
- Tương tự cho các số còn lại.

Chúc em ngày vui.
/-*+/

Phan Thế Hiệp · 21/9/18

Nguyễn Hồng Quang đã viết:
Trước đây trong file thư viện của em có lưu trữ đoạn công thức na ná như của anh

Thấy em rất chịu khó sưu tầm, nay anh muốn đưa ra thêm một trường hợp loại bỏ các mã hàng trùng lắp, nhưng chỉ dùng cách tô màu báo hiệu. Cụ thể như sau:

Cho một bảng dữ liệu A1: D8 chứa các mã hàng, đặt một công thức dùng chung cho Vùng A1: D8 trong 'Conditional Formatting' để tô màu cho các mã hàng trùng lắp như bảng theo file đính kèm, với yêu cầu:

Tô các mã xuất hiện từ lần thứ hai trở đi (hiện bảng đã được tô mẫu bằng tay)
Thứ tự xuất hiện được tính như sau:
- Từ trái qua phải
- Từ trên xuống
- Ví dụ:
  - Mã GHY6215, theo thứ tự yêu cầu thì xuất hiện lần thứ 1 được tính tại ô C1, lần thứ 2 tại ô D1, lần thứ 3 tại ô B2, lần thứ 4 tại ô A3.....
  - Mã VIO6749, xuất hiện lần 1 tại ô D5, lần thứ 2 tại ô A8.

Rất mong có sự góp vui của các anh em thành viên.

Chúc anh em ngày cuối tuần thiệt vui cùng gia đình.
/-*+/

dhn46 · 22/9/18

Phan Thế Hiệp đã viết:
Thấy em rất chịu khó sưu tầm, nay anh muốn đưa ra thêm một trường hợp loại bỏ các mã hàng trùng lắp, nhưng chỉ dùng cách tô màu báo hiệu. Cụ thể như sau:

Cho một bảng dữ liệu A1: D8 chứa các mã hàng, đặt một công thức dùng chung cho Vùng A1: D8 trong 'Conditional Formatting' để tô màu cho các mã hàng trùng lắp như bảng theo file đính kèm, với yêu cầu:

Tô các mã xuất hiện từ lần thứ hai trở đi (hiện bảng đã được tô mẫu bằng tay)

Thứ tự xuất hiện được tính như sau:

Từ trái qua phải

Từ trên xuống

Ví dụ:

Mã GHY6215, theo thứ tự yêu cầu thì xuất hiện lần thứ 1 được tính tại ô C1, lần thứ 2 tại ô D1, lần thứ 3 tại ô B2, lần thứ 4 tại ô A3.....

Mã VIO6749, xuất hiện lần 1 tại ô D5, lần thứ 2 tại ô A8.

Rất mong có sự góp vui của các anh em thành viên.

Chúc anh em ngày cuối tuần thiệt vui cùng gia đình.

Mình góp vui với công thức

Mã:

=IFERROR(COUNTIF(OFFSET($A$1:$D$1,,,ROW(A1)-1),A1),0)+COUNTIF($A1:A1,A1)>1

Ba Tê · 22/9/18

Nguyễn Hồng Quang đã viết:
cảm ơn anh Ba Tê, nhưng mà hơi ngược với file em gửi
Code của anh là giải cho kiểu 2 chứ không phải kiểu 1 anh à.
Trong file Kiểu 1-Rút gọn theo quy tắc là các đơn giá giống nhau mà nằm liên tiếp cạnh nhau thì dồn về 1 đơn giá, nếu không nằm cạnh nhau thì không dồn. Ví dụ dòng 5. Có AC5=AD5=47,641 thì dồn về thành 47,641 như ở AI5,còn AF5 tuy là cũng = 47,641, nhưng nằm cách 1 ô AD5 nên không dồn mà vẫn để lại như ở AK5
Hoặc như dòng 53. Có AC53=149,880 và AE53=AF53 cũng =149,880 nhưng vì cách 1 ô là AD53 nên chỉ dồn AE53 vâAF53 về thành 149,880 v..v các tình huống còn lại theo quy tắc trên
Mong anh giúp em theo kiểu này ạ

Tôi có nói là chưa xem cả 2 file để hiểu nó khác nhau chỗ nào, chỉ làm 1 kiểu (không phải kiểu 1).

Phan Thế Hiệp · 22/9/18

dhn46 đã viết:
Mình góp vui với công thức

Mã:

=IFERROR(COUNTIF(OFFSET($A$1:$D$1,,,ROW(A1)-1),A1),0)+COUNTIF($A1:A1,A1)>1

Làm 1 lần, đúng phóc những gì cần trao đổi.

Chúc bạn ngày vui.
/-*+/

Nguyễn Hồng Quang · 22/9/18

Ba Tê đã viết:
Tôi có nói là chưa xem cả 2 file để hiểu nó khác nhau chỗ nào, chỉ làm 1 kiểu (không phải kiểu 1).

Cảm ơn anh, đã giúp đỡ. Cách đảo lệnh trên 1 Command của anh cũng thật thú vị, giúp em mở mang thêm kiến thức

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

dhn46 đã viết:
Mình góp vui với công thức

Mã:

=IFERROR(COUNTIF(OFFSET($A$1:$D$1,,,ROW(A1)-1),A1),0)+COUNTIF($A1:A1,A1)>1

Cảm ơn dhn46 đã góp vui và giúp đỡ mình

Nguyễn Hồng Quang · 23/9/18

Phan Thế Hiệp đã viết:
Thấy em rất chịu khó sưu tầm, nay anh muốn đưa ra thêm một trường hợp loại bỏ các mã hàng trùng lắp, nhưng chỉ dùng cách tô màu báo hiệu. Cụ thể như sau:

Cho một bảng dữ liệu A1: D8 chứa các mã hàng, đặt một công thức dùng chung cho Vùng A1: D8 trong 'Conditional Formatting' để tô màu cho các mã hàng trùng lắp như bảng theo file đính kèm, với yêu cầu:

Tô các mã xuất hiện từ lần thứ hai trở đi (hiện bảng đã được tô mẫu bằng tay)

Thứ tự xuất hiện được tính như sau:

Từ trái qua phải

Từ trên xuống

Ví dụ:

Mã GHY6215, theo thứ tự yêu cầu thì xuất hiện lần thứ 1 được tính tại ô C1, lần thứ 2 tại ô D1, lần thứ 3 tại ô B2, lần thứ 4 tại ô A3.....

Mã VIO6749, xuất hiện lần 1 tại ô D5, lần thứ 2 tại ô A8.

Rất mong có sự góp vui của các anh em thành viên.

Chúc anh em ngày cuối tuần thiệt vui cùng gia đình.

Cảm ơn anh đã gửi em 1 bài tập hay. Nhưng mà thú thật với anh là phần dùng công thức trong Condition Format (CF) em biết ít lắm . Đọc bài của anh xong, em cũng có lên tìm kiếm trên diễn đàn, nhưng mà chỉ dừng lại được ở công thức trong CF =countif($A$1:$D$8;A1)>1. Công thức này là tô toàn bộ các mã bị trùng , nhưng không làm được Tô các mã xuất hiện từ lần thứ hai trở đi theo đúng đề bài của anh.

Mong anh giúp đỡ thêm

Nguyễn Hồng Quang · 23/9/18

Phan Thế Hiệp đã viết:
1/ 'Kiểu 1': Còn có thể rút gọn không cần dùng SIGN((ABS()) (2 cái hàm này anh dùng theo thói quen và ngẫu hứng lúc tạo công thức), như sau:

AQ5=IFERROR(OFFSET($AC5,,AGGREGATE(15,6,COLUMN($A:$F)/($AC5:$AH5-$AD5:$AI5<>0)/($AD5:$AI5>0),COLUMN(A$1))),"")

2/ 'Kiểu 2':

AP5=IFERROR(INDEX($AC5:$AH5,MATCH(,INDEX(COUNTIF($AO5: AO5,$AC5: $AH5),),)),)

Dù kết quả là cột hay dòng, khi xử lý bài toán liệt kê danh sách loại bỏ dữ liệu trùng thường áp dụng công thức tổng quát:
=INDEX( 'Vùng dữ liệu' , MATCH( 0 , COUNTIF( 'Vùng lần lượt hiện kết quả' , 'Vùng dữ liệu' ) , 0 ) )
(hay có cách viết khác: =INDEX( 'Vùng dữ liệu' , MATCH( , INDEX( COUNTIF( 'Vùng lần lượt hiện kết quả' , 'Vùng dữ liệu' ) , ) , ) chỉ Enter kết thúc)

Công thức dưới đây quá quen thuộc, chắc em nhìn ra:
=INDEX( 'Vùng dữ liệu' , MATCH( 'Giá trị tìm' , 'Vùng giá trị cần so khớp' , 0 ))
Anh chỉ muốn nói thêm những điểm nhấn của dạng bài toán này:

'Vùng dữ liệu': Là Vùng (hoặc Mảng) 1 chiều, chứa dữ liệu có thể có trùng lắp cần loại ra. Tùy theo yêu cầu của đề bài, nếu chỉ lấy đúng theo thứ tự xuất hiện (từ trên xuống, hay từ trái qua phải) thì chỉ cần đưa Vùng/Mảng như tự nhiên của dữ liệu đang có, nếu yêu cầu còn phải sắp xếp thứ tự sau khi đã loại trùng thì mới kết hợp hàm khác như: Small(), Large() hay Aggregate(). Như bài của em hỏi thì chỉ cần đưa Vùng $AC5: $AH5 (kéo xuống thành $AC6: $AH6, và cứ tương tự như thế) là đủ không cần phối hợp thêm hàm Aggregate().

COUNTIF( 'Vùng lần lượt hiện kết quả' , 'Vùng dữ liệu' ): Đây là công thức "cốt lõi" để loại bỏ dữ liệu trùng lắp, là "biểu thức điều kiện" mà em có thể áp dụng trong bất cứ dạng công thức kết hợp khác nhưng muốn loại bỏ dữ liệu trùng lắp. Đơn giản nó là công thức Mảng: Countif( Range, Array ). Để ý em sẽ thấy rằng: các hàm họ Countif() hoặc Sumif() sẽ trả về kết quả đơn hay mảng đúng theo số phần tử và chiều (dọc hay ngang) có trong khai báo đối số Array trong các hàm đó.

Ví dụ: như trong bài COUNTIF($AO5: AO5,$AC5: $AH5) sẽ tạo ra Mảng 1 chiều theo chiều ngang tương ứng với chiều dài các cột AC: AH.

Do AO5 hiện là rỗng, nên Mảng trả về của COUNTIF($AO5: AO5,$AC5: $AH5) là {0,0,0,0,0,0}, dùng MATCH(0,{0,0,0,0,0,0},0) sẽ ra 1, ứng với giá trị ô AC5="47641" đem trả về kết quả đó tại ô AP5 nhờ vào Index(). (xem thêm công thức tại B2: G2 file đính kèm)

Công thức qua ô AQ5, do AP5="47641", nên COUNTIF($AO5: AP5,$AC5: $AH5) <--> COUNTIF({"",47641},$AC5: $AH5) tạo nên 1 mảng {1,1,0,1,0,0}, dùng MATCH(0,{1,1,0,1,0,0},0) sẽ ra 3, ứng với giá trị ô AE5="52405" đem trả về kết quả đó tại ô AQ5 nhờ vào Index(). Để ý lại mảng trên em sẽ thấy giá trị "47641" tại AC5 và AD5 sẽ "được" đếm là 1, mà 'Giá trị tìm' của MATCH() là giá trị 0, nên giá trị 1 như vậy kể như "bị loại", không cần so khớp nữa. (xem thêm công thức tại B3: G3 file đính kèm).

Tương tự cho các số còn lại.

Chúc em ngày vui.

lúc đầu em hiểu sai vì cứ tưởng COUNTIF($AO5: AP5,$AC5: $AH5) <--> COUNTIF({"",47641},$AC5: $AH5) nó sinh ra 2 mảng là {0,0,0,0,0,0} và {1,1,0,1,0,0}, rồi gộp thành 1 mảng {1,1,0,1,0,0}...., vì bị tư duy kiểu đó nên em bị rối rắm không hiểu được cách thức hoạt động. May có anh giải thích rõ và kèm ví dụ minh họa nên bây giờ em mới hiểu là nó tạo ra 1 mảng luôn theo kiểu là duyệt lần lượt từng phần tử trong range $AO5: AP5 , hễ cứ phần tử nào xuất hiện trong array $AC5: $AH5 là nó bật ra số 1 luôn.

Một lần nữa cảm ơn anh nhiều nhiều

Rút gọn dữ liệu trùng theo hàng ngang (theo 2 kiểu) (2 người xem)

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

Thành viên GPE Hà Nội

File đính kèm

Cạo Rồi Khỏi Gội

File đính kèm

Thành viên GPE Hà Nội

Gội rồi mới Cạo

-⍟-

File đính kèm

Thành viên GPE Hà Nội

File đính kèm

-⍟-

File đính kèm

Thành viên GPE Hà Nội

-⍟-

Thành viên GPE Hà Nội

Thành viên hoạt động

Thành viên GPE Hà Nội

-⍟-

File đính kèm

-⍟-

File đính kèm

Hướng tới tương lai

Cạo Rồi Khỏi Gội

File đính kèm

-⍟-

Thành viên GPE Hà Nội

Thành viên GPE Hà Nội

Thành viên GPE Hà Nội

Đếm ngược thời gian