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

Liên hệ QC
Nguyễn Hồng Quang

Nguyễn Hồng Quang

Thành viên GPE Hà Nội
Tham gia
8/6/07
Bài viết
1,203
Được thích
876
Giới tính
Nam
Nghề nghiệp
Kế toán
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 //**/
 

File đính kèm

  • Kiểu 1-rút gọn.xlsx
    78.2 KB · Đọc: 22
  • Kiểu 2-rút gọn.xlsx
    78.1 KB · Đọc: 15
Lần chỉnh sửa cuối:
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 //**/
Nhấp chuột vào đây để mở rộng...
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.
 

File đính kèm

  • Kiểu 1-rút gọn.rar
    72.7 KB · Đọc: 8
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.
Nhấp chuột vào đây để mở rộng...
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 ạ
 
Lần chỉnh sửa cuối:
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 ạ
Nhấp chuột vào đây để mở rộng...
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
 
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 //**/
Nhấp chuột vào đây để mở rộng...
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
 

File đính kèm

  • Kiểu 1-rút gọn.xlsx
    77.9 KB · Đọc: 26
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
Nhấp chuột vào đây để mở rộng...
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:

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
Nhấp chuột vào đây để mở rộng...
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 ô????
 

File đính kèm

  • Kiểu 2-rút gọn.xlsx
    83.5 KB · Đọc: 6
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 ô????
Nhấp chuột vào đây để mở rộng...
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
 

File đính kèm

  • Kiểu 2-rút gọn.xlsx
    79.5 KB · Đọc: 15
Lần chỉnh sửa cuối:
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.
/-*+//-*+//-*+/
Nhấp chuột vào đây để mở rộng...
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
 
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
Nhấp chuột vào đây để mở rộng...
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 đã 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 //**/
Nhấp chuột vào đây để mở rộng...
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.
 
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.
Nhấp chuột vào đây để mở rộng...
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
 
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) :)
Nhấp chuột vào đây để mở rộng...
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:
  1. '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().
  2. 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.
/-*+//-*+//-*+/
 

File đính kèm

  • Vidu.xlsx
    10.6 KB · Đọc: 21
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
Nhấp chuột vào đây để mở rộng...
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:​
  1. 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)
  2. 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.
/-*+//-*+//-*+/
 

File đính kèm

  • Conditional Formatting.xlsx
    10.5 KB · Đọc: 18
Lần chỉnh sửa cuối:
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:​
  1. 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)
  2. 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.
/-*+//-*+//-*+/
Nhấp chuột vào đây để mở rộng...
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
 
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 ạ
Nhấp chuột vào đây để mở rộng...
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).
 

File đính kèm

  • 2 kieu rut gon.xlsm
    91 KB · Đọc: 12
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).
Nhấp chuột vào đây để mở rộng...
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:

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
Nhấp chuột vào đây để mở rộng...
Cảm ơn dhn46 đã góp vui và giúp đỡ mình
 
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:​
  1. 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)
  2. 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.
/-*+//-*+//-*+/
Nhấp chuột vào đây để mở rộng...
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
 
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:
  1. '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().
  2. 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.
/-*+//-*+//-*+/
Nhấp chuột vào đây để mở rộng...
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
 
Bạn phải đăng nhập hoặc đăng ký để trả lời bài viết tại đây.
Web KT

Bài viết mới nhất

8
  • Question
Xin giúp: Nhóm (group data) các dòng trong bảng tính
Trả lời
10
Đọc
354
DeTong
D
pupuchcha
  • Question
Viết code để tìm xích ma, mong mọi người giúp đỡ em ạ
Trả lời
6
Đọc
205
VetMini
V
longhungvnpost
kết hợp hàm vlookup và hlookup như thế nào, giúp e với
Trả lời
4
Đọc
64
longhungvnpost
longhungvnpost
Emyeuexcel
  • Question
VBA lưu file, in, và mở lại file gốc ban đầu
2
Trả lời
29
Đọc
508
Emyeuexcel
Emyeuexcel
TrinhThiHai_Kt
  • Dán lên cao
Các câu hỏi về sử dụng diễn đàn
5 6 7
Trả lời
122
Đọc
185K
Hai Lúa Miền Tây
Hai Lúa Miền Tây
Xem thêm…

Facebook

Giaiphapexcel.com

Group

DIỄN ĐÀN GIẢI PHÁP EXCEL Group 1
DIỄN ĐÀN GIẢI PHÁP EXCEL Group 2

Thành viên có số lượng bài viết cao nhất tháng

Thành viên có điểm tương tác cao nhất tháng

Back
Top Bottom