Xác định phần tử (dạng chuổi) xuất hiện nhiều lần nhất trong vùng (post bài giùm bạn) (6 người xem)

[ndu96081631]

Bạn ithow gữi tin nhắn cho tôi nhờ giúp với nội dung:

Mong anh giúp đỡ
Mong anh giúp tôi vấn đề này với, tôi làm mãi mà không được, còn search trên google và diễn đàn này thì không có bài nào đúng với ý mình cả.

Hình như phần gửi tin nhắn này không cho "đính kèm tệp" nên tôi up lên đây: http://mega.1280.com/file/0U263XG9RA/ . Mong anh giúp đỡ

Xin chân thành cảm ơn và chúc anh sức khỏe!

Đại khái yêu cầu xác định phần tử nào là phần tử xuất hiện nhiều nhất trong 1 vùng và tìm các record liên quan với nó
Câu hỏi cụ thể nằm trong file, các bạn tải file về và nghĩ xem có hướng nào không
(tôi nghĩ đây cũng là yêu cầu khó, vì tác giả không muốn dùng VBA)

 

[chucuoi92]

Anh NDU ơi "CÁC mã hàng hóa xuất hiện NHIỀU LẦN NHẤT" tức là nếu có 2 hoặc 3.. mã hàng có số lần xuất hiện bằng nhau nhiều nhất thì cũng liệt kê cả ra hay sao ?

 

[ndu96081631]

Anh NDU ơi "CÁC mã hàng hóa xuất hiện NHIỀU LẦN NHẤT" tức là nếu có 2 hoặc 3.. mã hàng có số lần xuất hiện bằng nhau nhiều nhất thì cũng liệt kê cả ra hay sao ?

Đây chính là điều tôi đang muốn hỏi, mong tác giả làm rõ vấn đề này:
- Nếu có trên 2 mã cùng xuất hiện nhiều nhất thì tính thế nào

a) Một cái xếp nhất, một cái xếp nhì?
b) Cả 2 cùng xếp nhất? ---> Trong trường hợp này thì liệt kê cái nhất nào

​

- Tương tự thế cho số tiền
------------------------------
Hôm qua tôi đã thử và thấy rằng: Đừng nói là làm bài toán này bằng công thức, ngay cả tác giả cho phép dùng VBA để xử lý cũng không phải dể "nhai" đâu
Cái khó của bải toàn này là:
- Khả năng trùng của dử liệu
- Vùng dử liệu nằm ở cách khu vực không liền kề

 

[ithow]

Cái khó của bải toàn này là:
- Khả năng trùng của dử liệu
- Vùng dử liệu nằm ở cách khu vực không liền kề

Anh nói đúng, thực ra việc chia thành 2 vùng dữ liệu không liền kề là do tôi cố ý làm như vậy

Khả năng trùng dữ liệu có lẽ là không vì mỗi câu tôi hỏi đều có mục đích khác nhau..... Vì không tiện nói ra mục đích của mình nên tôi xin được gửi lại câu hỏi cho rõ ràng hơn, còn cách giải quyết những vấn đề như trong bài đầu tiên thì dựa vào bài giải lần này tôi sẽ làm thêm những công thức khác theo ý mình. Tóm lại là bây giờ chỉ cần giải quyết bài này thôi.

Cảm ơn mọi người đã quan tâm, mong được giúp đỡ.

 

[ndu96081631]

Bạn ơi, bạn yêu cầu dùng công thức cho bài này, tôi e rằng.. chịu không nổi quá
Hy vọng bạn nghĩ đến giải pháp VBA (mà cũng chưa chắc đã có thể giải quyết được ---> Khó ăn nhất ở chổ sắp xếp)

 

[thanh_tks]

Anh nói đúng, thực ra việc chia thành 2 vùng dữ liệu không liền kề là do tôi cố ý làm như vậy

Khả năng trùng dữ liệu có lẽ là không vì mỗi câu tôi hỏi đều có mục đích khác nhau..... Vì không tiện nói ra mục đích của mình nên tôi xin được gửi lại câu hỏi cho rõ ràng hơn, còn cách giải quyết những vấn đề như trong bài đầu tiên thì dựa vào bài giải lần này tôi sẽ làm thêm những công thức khác theo ý mình. Tóm lại là bây giờ chỉ cần giải quyết bài này thôi.

Cảm ơn mọi người đã quan tâm, mong được giúp đỡ.

Bài của bạn nếu xác định một trong hai cột có đầy đủ các mã của cột kia thì mới có thể làm được, bạn có thể tham khảo file mình làm.
Sửa lại name:

LOC=IF(MATCH(CONG,CONG,)=ROW(LIST)-1,CONG+(ROW(LIST)-1),"")

thành:

LOC=IF(MATCH(LIST,LIST,)=ROW(LIST)-1,CONG+(ROW(LIST)-1),"")

 

[ithow]

Cảm ơn thanh_tks và mọi người đã quan tâm.

Bài của bạn nếu xác định một trong hai cột có đầy đủ các mã của cột kia thì mới có thể làm được

Vì đây là bài mẫu nên tôi cố tình sắp xếp cho hai cột có đầy đủ các mã của nhau, chứ thực tế với công việc của mình thì chắc chắn sẽ không giống nhau hoàn toàn. Nhưng thực sự là bài giải của thanh_tks đã mở ra một hướng mới, biết đâu các cao thủ sẽ giải quyết được.....
Nhờ mọi người!!!!!

Bạn ơi, bạn yêu cầu dùng công thức cho bài này, tôi e rằng.. chịu không nổi quá
Hy vọng bạn nghĩ đến giải pháp VBA (mà cũng chưa chắc đã có thể giải quyết được ---> Khó ăn nhất ở chổ sắp xếp)

Bây giờ thì cách gì cũng được anh ndu96081631 à. Thật ra kết quả là cái đích cuối cùng, cho dù dùng cách nào cũng được, nhưng tại tôi muốn hiểu rõ ngọn ngành luôn để sau này gặp trường hợp tương tự sẽ có hướng mà đi mà lại không biết gì về VBA. Chuyến này phải đi bổ túc 1 khóa về VBA quá, hic

 

[ndu96081631]

Bây giờ thì cách gì cũng được anh ndu96081631 à. Thật ra kết quả là cái đích cuối cùng, cho dù dùng cách nào cũng được, nhưng tại tôi muốn hiểu rõ ngọn ngành luôn để sau này gặp trường hợp tương tự sẽ có hướng mà đi mà lại không biết gì về VBA. Chuyến này phải đi bổ túc 1 khóa về VBA quá, hic

Phù... cũng may, bạn chấp nhận giải pháp VBA ---> Dù vậy, nó cũng làm tôi đau đầu suốt 2 ngày
Tạm xong giải pháp cho bạn, code đây!

Function CountIfM(MRange As Range, Criteria) As Long
  Dim Area As Range
  For Each Area In MRange.Areas
    CountIfM = CountIfM + WorksheetFunction.CountIf(Area, Criteria)
  Next Area
End Function

Function LargeFreq(MRange As Range, Order As Long)
  'Vao menu Tools\References và check vào Microsoft Scripting Runtime
  Dim Clls As Range, i As Long
  With New Dictionary
    For Each Clls In MRange
      If (Not IsEmpty(Clls)) And (Not .Exists(Clls.Value)) Then
        .Add Clls.Value, CountIfM(MRange, Clls.Value) - .Count/ 10 ^ 10
      End If
    Next Clls
    If Order > .Count Then LargeFreq = "": Exit Function
    For i = 0 To .Count - 1
      If .Items(i) = WorksheetFunction.Large(.Items, Order) Then
        LargeFreq = .Keys(i)
        Exit Function
      End If
    Next i
  End With
End Function

- Code này cho phép hoạt động trên mọi vùng (liên tục hay không liên tục)
- Dử liệu trùng sẽ ưu tiên cái nào "nhìn thấy" trước
Chú ý: Nếu copy code về file khác thì nhớ vào menu Tools\References và check vào mục Microsoft Scripting Runtime nhé
Xem file và test giúp, nếu có trục trặc nhớ báo tôi biết

 

[Đào Việt Cường]

Dear ithow,
------------
Bạn có thể dùng công thức tính tổng các vùng số lần xuất hiện và số tiền của mỗi mã được không? Ví dụ:
- Đếm số lần xuất hiện của mã "HH10":

=COUNTIF($A$2:$A$19;$G2)+COUNTIF($D$2:$D$19;$G2)

- Tổng số tiền của mã "HH10":

=SUMIF($A$2:$A$19;$G2;$B$2:$B$19)+SUMIF($D$2:$D$19;$G2;$E$2:$E$19)

Dù cho mục đích nào, theo mình việc tạo ra nhiều vùng dữ liệu có cấu trúc giống nhau không phải là giải pháp tối ưu. Ban nên nêu mục đích của mình buộc phải tách ra, mình nghĩ không phải là không có cách để gộp chúng lại.

 

[ndu96081631]

Dear ithow,
------------
Bạn có thể dùng công thức tính tổng các vùng số lần xuất hiện và số tiền của mỗi mã được không? Ví dụ:
- Đếm số lần xuất hiện của mã "HH10":

=COUNTIF($A$2:$A$19;$G2)+COUNTIF($D$2:$D$19;$G2)

- Tổng số tiền của mã "HH10":

=SUMIF($A$2:$A$19;$G2;$B$2:$B$19)+SUMIF($D$2:$D$19;$G2;$E$2:$E$19)

Dù cho mục đích nào, theo mình việc tạo ra nhiều vùng dữ liệu có cấu trúc giống nhau không phải là giải pháp tối ưu. Ban nên nêu mục đích của mình buộc phải tách ra, mình nghĩ không phải là không có cách để gộp chúng lại.

Vấn đề là người ta muốn lấy ra được các Item, và Item nào có số lần xuất hiện nhiều nhất phải được xếp trên (Sort)

 

[ChanhTQ@]

Phù... cũng may, bạn chấp nhận giải pháp VBA ---> Dù vậy, nó cũng làm tôi đau đầu suốt 2 ngày
Tạm xong giải pháp cho bạn, code đây! . . .

Tại chú cứ nhìn sự vật theo chiều fức tạp, chứ đâu đến nổi mất hai ngày zữ zậy?!
Theo mình phải đơn giản như nó vốn có:

B1: Chép [A2].CurentRegion sang [G1];
B2: Chép [D2].CurentRegion.Ofset(1) tiếp vô cột 'H'
B3: Lập danh sách duy nhất tứ cột 'G' đến cột 'J'
B4: Xếp cột 'J' này
B5: Thiết lập vòng lặp theo dữ liệu cột 'J' để tìm trong cột 'G'
(+) Nếu tìm thấy thì tăng 1 biến đếm & Tăng vô biến tổng giá trị chứa trong ô bên phải liền kề;
Lặp lại phương thức tìm cho đến hết;
(+) Ghi trị trong 2 biến vô cột 'K' & 'L' cùng hàng với ô đang tìm. Sau đó cho 2 biến này nhận giá trị 0 để chuẩn bị vòng lặp với ô tìm kiếm mới dưới nó.
. . . .
(Kết thúc vòng lặp)
B6: Xóa 2 cột 'G:H'
B7: Gán STT vô cột H căn cứ vô dữ liệu cột 'J'
B8: Thực hiện xếp 4 cột theo cột J (Số lần) & cột 'H' (STT theo chiều giảm)

 

[ndu96081631]

Tại chú cứ nhìn sự vật theo chiều fức tạp, chứ đâu đến nổi mất hai ngày zữ zậy?!
Theo mình phải đơn giản như nó vốn có:

B1: Chép [A2].CurentRegion sang [G1];
B2: Chép [D2].CurentRegion.Ofset(1) tiếp vô cột 'H'
B3: Lập danh sách duy nhất tứ cột 'G' đến cột 'J'
B4: Xếp cột 'J' này
B5: Thiết lập vòng lặp theo dữ liệu cột 'J' để tìm trong cột 'G'
(+) Nếu tìm thấy thì tăng 1 biến đếm & Tăng vô biến tổng giá trị chứa trong ô bên phải liền kề;
Lặp lại phương thức tìm cho đến hết;
(+) Ghi trị trong 2 biến vô cột 'K' & 'L' cùng hàng với ô đang tìm. Sau đó cho 2 biến này nhận giá trị 0 để chuẩn bị vòng lặp với ô tìm kiếm mới dưới nó.
. . . .
(Kết thúc vòng lặp)
B6: Xóa 2 cột 'G:H'
B7: Gán STT vô cột H căn cứ vô dữ liệu cột
B8: Thực hiện xếp 4 cột theo cột J (Số lần) & cột 'H' (STT theo chiều giảm)

Không anh à! Đúng là nó phức tạp thật, vì trên thực tế vùng này tùy ý, có thể nó nằm ở 5 hoặc 10 khu vực khác nhau, chẳng lẽ mổi lần thay đổi vùng dử liệu anh lại phải sửa code!
Mà cho dù là thế em cũng không muốn dùng bất cứ cột phụ hay cell phụ nào ---> Dể bị "théc méc" lắm (khi vùng phụ đè vào vùng dử liệu chính)
Chứ nếu mà làm được như anh nói thì em... Consolidate 1 nhát là ra toàn bộ liền, khỏi vòng lập luôn!

 

[ndu96081631]

Nhân có ý kiến của sư phụ ChanhTQ@, em thử dùng Consoldate xem thế nào
Đầu tiên gõ chữ Mã hàng hóa vào H1, gõ chữ Số lần vào I1 và gõ chữ Số tiền vào J1
Sau đó chạy code này:

Sub Loc()
  Dim RngAdd
  On Error GoTo Thoat
  With Application.InputBox("Chon vung du lieu, khong tinh tieu de" _
   & Chr(10) & " Bam giu phím Ctrl  de chon nhieu vung không liên tuc", Type:=8)
    RngAdd = Split(.Address(, , 2), ",")
  End With
  Range("H2:J60000").ClearContents
  With Range("I2")
    .Consolidate Array(RngAdd), 9, 0, 1
    Range(.Cells, .End(xlDown)).ClearContents
    .Offset(, -1).Consolidate Array(RngAdd), 2, 0, 1
    .CurrentRegion.Sort .Cells, 2, Header:=xlNo
  End With
Thoat:
End Sub

Nhanh gọn, không vòng lập
Lưu ý: Khi hộp InputBox hiện ra, hãy dùng chuột kết hợp phím Ctrl để chọn nhiều vùng không liên tục

 

[anhtuan1066]

Chơi cái InputBox bằng tiếng Việt cho nó hoành tráng
Hãy xem file
Săn đây mọi người thử tìm hiểu xem cái InputBox ấy được "đẻ" ra từ đâu vậy?
Hi... hi...

 

[ithow]

Phù... cũng may, bạn chấp nhận giải pháp VBA ---> Dù vậy, nó cũng làm tôi đau đầu suốt 2 ngày
Tạm xong giải pháp cho bạn, code đây!
Xem file và test giúp, nếu có trục trặc nhớ báo tôi biết

Thật là không biết lấy gì để cảm ơn anh nữa, tôi đã check và file chạy rất tốt. Một lần nữa cảm ơn anh và mọi người thật nhiều

 

[TrungChinhs]

Chơi cái InputBox bằng tiếng Việt cho nó hoành tráng
Hãy xem file
Săn đây mọi người thử tìm hiểu xem cái InputBox ấy được "đẻ" ra từ đâu vậy?
Hi... hi...

@ anhtuan1066 Tôi đã bất lực tìm cái InputBox của bạn (đã tìm trong những chỗ có thể cất dấu, trong code... nhưng chẳng thấy gì cả). Nó được "đẻ" ra từ đâu vậy? Rất mong được bạn chia xẻ vụ này. Thanks!

 

[anhtuan1066]

@ anhtuan1066 Tôi đã bất lực tìm cái InputBox của bạn (đã tìm trong những chỗ có thể cất dấu, trong code... nhưng chẳng thấy gì cả). Nó được "đẻ" ra từ đâu vậy? Rất mong được bạn chia xẻ vụ này. Thanks!

Cảm ơn anh đã quan tâm đến câu hỏi này!
Anh nhìn sơ qua code cũng có thể đoán được InputBox này chính là 1 DialogSheets

With DialogSheets("Input")

Nếu không tìm thấy, thì đương nhiên nó đã bị làm cho ẩn đi rồi, anh dùng code này cho nó hiện ra nhé:

Sub Test()
  Dim i As Long
  For i = 1 To Sheets.Count
    Sheets(i).Visible = -1
  Next
End Sub

Xem nó thật đơn giản, vì tiếng Việt do anh tự tay gõ vào
---------------------------------------
Một câu hỏi tiếp theo:
Hãy xem file đính kèm này, trong file tôi đã set Focus cho Edit Box... Vậy các bạn hãy cho biết tôi đã làm việc này như thế nào?
Code thì các bạn đã thấy hết, đương nhiên có thể hiểu được... nhưng điều quan trọng là khi các bạn copy code trong file đính kèm này sang 1 file mới tinh mà nó chạy y chang file gốc là xem như các bạn thành công!
---------------------------------------
(Nếu các Mod cảm thấy 2 bài của tôi đặt tại đây chưa thích hợp, xin dời đến 1 nơi nào đó hợp lý hơn... Cảm ơn)

 

[TrungChinhs]

Cảm ơn anh đã quan tâm đến câu hỏi này!
Anh nhìn sơ qua code cũng có thể đoán được InputBox này chính là 1 DialogSheets

With DialogSheets("Input")

Nếu không tìm thấy, thì đương nhiên nó đã bị làm cho ẩn đi rồi, anh dùng code này cho nó hiện ra nhé:

Sub Test()
  Dim i As Long
  For i = 1 To Sheets.Count
    Sheets(i).Visible = -1
  Next
End Sub

Xem nó thật đơn giản, vì tiếng Việt do anh tự tay gõ vào

(Nếu các Mod cảm thấy 2 bài của tôi đặt tại đây chưa thích hợp, xin dời đến 1 nơi nào đó hợp lý hơn... Cảm ơn)

Cảm ơn anhtuan1066! Trước đây tôi đã xem bài tạo Msgbox từ DialogSheets của bạn nên cũng đoán được cái Input ấy là 1 DialogSheets nhưng tôi không nghĩ là nó được siêu ẩn vì khi chuột phải chọn Wiew Code để siêu ẩn thì dòng này bị mờ và cũng không thấy code cho nó ẩn nên mới bó tay.

Vậy bạn cho hỏi:
1. Có phải bạn dùng VBA cho nó ẩn rồi xoá code đi không?
2. Tôi chưa hiểu câu EditBoxes("Input1").Text = "" để làm gì? vì khi tôi xoá dòng này đi thì không thấy có gì khác.

 

[anhtuan1066]

Cảm ơn anhtuan1066! Trước đây tôi đã xem bài tạo Msgbox từ DialogSheets của bạn nên cũng đoán được cái Input ấy là 1 DialogSheets nhưng tôi không nghĩ là nó được siêu ẩn vì khi chuột phải chọn Wiew Code để siêu ẩn thì dòng này bị mờ và cũng không thấy code cho nó ẩn nên mới bó tay.

Vậy bạn cho hỏi:
1. Có phải bạn dùng VBA cho nó ẩn rồi xoá code đi không?
2. Tôi chưa hiểu câu EditBoxes("Input1").Text = "" để làm gì? vì khi tôi xoá dòng này đi thì không thấy có gì khác.

1> Đúng rồi, em dùng VBA cho ẩn sheet, xong thì xóa code đi (làm ngược lại để ẩn sheet Input nhé)... Anh có thể áp dụng cách này để ẩn Name (khá an toàn đấy). Người ta sẽ thấy Name có trong công thức mà chẳng thấy tên của nó trong hộp Define name
2> câu lệnh .EditBoxes("Input1").Text = "" nhằm mục đích "dọn sạch" Edit Box khi Dialog hiện ra
Xóa dòng này đi, chạy code lần đầu anh sẽ chẳng thấy gì... chạy lần thứ 2 trở đi anh sẽ thấy khác biệt (Edit Box vẫn còn lưu giữ lại Text của lần trước)
-----------------------------
Anh tiếp tục với câu hỏi tiếp theo của em đi, khá thú vị đấy... Thú vị ở chổ:
- Chạy code trên file gốc thì OK
- Copy code về file mới, xây dựng cấu trúc file y chang file gốc nhưng nó vẫn không Set Focus được cho Edit Box
Hi... hi...

 

[S3r3n4]

Phù... cũng may, bạn chấp nhận giải pháp VBA ---> Dù vậy, nó cũng làm tôi đau đầu suốt 2 ngày
Tạm xong giải pháp cho bạn, code đây!

Function CountIfM(MRange As Range, Criteria) As Long
  Dim Area As Range
  For Each Area In MRange.Areas
    CountIfM = CountIfM + WorksheetFunction.CountIf(Area, Criteria)
  Next Area
End Function

Function LargeFreq(MRange As Range, Order As Long)
  'Vao menu Tools\References và check vào Microsoft Scripting Runtime
  Dim Clls As Range, i As Long
  With New Dictionary
    For Each Clls In MRange
      If (Not IsEmpty(Clls)) And (Not .Exists(Clls.Value)) Then
        .Add Clls.Value, CountIfM(MRange, Clls.Value) - .Count/ 10 ^ 10
      End If
    Next Clls
    If Order > .Count Then LargeFreq = "": Exit Function
    For i = 0 To .Count - 1
      If .Items(i) = WorksheetFunction.Large(.Items, Order) Then
        LargeFreq = .Keys(i)
        Exit Function
      End If
    Next i
  End With
End Function

- Code này cho phép hoạt động trên mọi vùng (liên tục hay không liên tục)
- Dử liệu trùng sẽ ưu tiên cái nào "nhìn thấy" trước
Chú ý: Nếu copy code về file khác thì nhớ vào menu Tools\References và check vào mục Microsoft Scripting Runtime nhé
Xem file và test giúp, nếu có trục trặc nhớ báo tôi biết

Nếu dữ liệu có Mã hàng giống nhau nhưng khác nhau ở chữ hoa và chữ thường, ví dụ: "HH1" và "hh1", thì kết quả lại cho ra "HH1" và "hh1" có số lần xuất hiện như nhau và = tổng số lần xuất hiện của cả "HH1" và "hh1"). Vậy phải sửa code như thế nào để khắc phục điều này hả bạn?