Lọc dữ liệu cho bảng tính (1 người xem)

[quangdiepctmbk]

Chào mọi người.
Mình có một vấn đề rất mong mọi người giúp đỡ.
Minh đang muốn lọc dữ liệu như trong file excel đính kèm nhưng không biết phải làm thế nào.
Ai chỉ giúp mình nhé.
Cảm ơn nhiều.

 

[Ba Tê]

Chào mọi người.
Mình có một vấn đề rất mong mọi người giúp đỡ.
Minh đang muốn lọc dữ liệu như trong file excel đính kèm nhưng không biết phải làm thế nào.
Ai chỉ giúp mình nhé.
Cảm ơn nhiều.

------
Làm theo kiểu ABC, thêm một cột phụ rồi Countif()

 

[havietchuong]

Chào mọi người.
Mình có một vấn đề rất mong mọi người giúp đỡ.
Minh đang muốn lọc dữ liệu như trong file excel đính kèm nhưng không biết phải làm thế nào.
Ai chỉ giúp mình nhé.
Cảm ơn nhiều.

Nếu không có gì phức tạp hơn nữa thì cần gì dùng hàm.

 

[TrungChinhs]

Tôi thấy dạng bài này rất thực tế nhưng chưa nghĩ ra cách nào hay hơn cách của Ba Tê (ý muốn nói là cách nào đó độc đáo hơn và luôn giải quyết được vấn đề khi mà người thực hiện kiểm tra <=> 1 lần). Chờ bài của các bạn.

 

[ndu96081631]

Tôi thấy dạng bài này rất thực tế nhưng chưa nghĩ ra cách nào hay hơn cách của Ba Tê (ý muốn nói là cách nào đó độc đáo hơn và luôn giải quyết được vấn đề khi mà người thực hiện kiểm tra <=> 1 lần). Chờ bài của các bạn.

Đương nhiên là được chứ anh!
- Gõ vào C11 chữ: Nhóm A
- Gõ vào C12 chữ: Nhóm B
- Gõ vào C13 chữ: Nhóm C
- Dùng công thức COUNTIF kết hợp SUMPRODUCT như sau:

=SUMPRODUCT(COUNTIF(OFFSET($D$2:$D$4,,MATCH($C11,$D$1:$F$1,0)-1),$B$2:$B$9))

Khỏi cột phụ!
Offset trong công thức nhằm mục địch dùng 1 công thức có thể kéo fill xuống!
Nếu muốn tách riêng từng công thức, có thể bỏ Offset, sẽ càng gọn hơn
Chẳng hạn:

D11 =SUMPRODUCT(COUNTIF($D$2:$D$4,$B$2:$B$9))

D12 =SUMPRODUCT(COUNTIF($E$2:$E$4,$B$2:$B$9))

D13 =SUMPRODUCT(COUNTIF($F$2:$F$4,$B$2:$B$9))

 

[concogia]

Đương nhiên là được chứ anh!

=SUMPRODUCT(COUNTIF(OFFSET($D$2:$D$4,,MATCH($C11,$D$1:$F$1,0)-1),$B$2:$B$9))

Quái, người ta bảo chỗ để đội nón gọi là cái đầu, nhưng dưới cái nón của mình là cái gì không biết mà nghĩ mãi không ra, "tèo", đành phải hỏi Thầu Ndu vậy:
Trong công thức Thầy đã sử dụng Countif ==> kết quả phải là số ==> rồi lại Sumproduct mà không sử dụng Sum
Không biết mình hiểu sai chỗ nào? Trong công thức sử dụng Sum có được không hay Sumproduct còn có tác dụng nào khác vậy Thầy?
Thân

 

[ndu96081631]

Đương nhiên là được chứ anh!

=SUMPRODUCT(COUNTIF(OFFSET($D$2:$D$4,,MATCH($C11,$D$1:$F$1,0)-1),$B$2:$B$9))

Quái, người ta bảo chỗ để đội nón gọi là cái đầu, nhưng dưới cái nón của mình là cái gì không biết mà nghĩ mãi không ra, "tèo", đành phải hỏi Thầu Ndu vậy:
Trong công thức Thầy đã sử dụng Countif ==> kết quả phải là số ==> rồi lại Sumproduct mà không sử dụng Sum
Không biết mình hiểu sai chỗ nào? Trong công thức sử dụng Sum có được không hay Sumproduct còn có tác dụng nào khác vậy Thầy?
Thân

SUM vẫn được anh à!
Dùng SUMPRODUCT để khỏi phải Ctrl + Shift + Enter ấy mà (vì bản thân SUMPRODUCT là hàm mãng nên cứ Enter bình thường)
Anh thí nghiệm bằng cách vầy:
Nhìn lên thanh Formula, bôi đen đoạn COUNTIF(OFFSET($D$2:$D$4,,MATCH($C11,$D$1:$F$1,0)-1),$B$2:$B$9) rồi bấm F9 sẽ thấy kết quả là 1 mãng gồm nhiều số, thế nên phải SUM chúng lại
Có điều nếu anh dùng SUM anh lại phải mất công bấm tổ hợp phím Ctrl + Shift + Enter
Có thế thôi!

 

[TrungChinhs]

=SUMPRODUCT(COUNTIF(OFFSET($D$2:$D$4,,MATCH($C11,$D$1:$F$1,0)-1),$B$2:$B$9))

Công thức của Ndu rất hay, đặc biệt là việc sử dụng hàm offset đã làm cho công thức tuỳ biến hoàn toàn, ta có thể tăng số dòng - cột (chữ, số màu đỏ) là có thể tính được n lần kiểm tra với n nhóm.
Trong ví dụ pos lên, chủ topic đã vô tình làm hạn chế vùng $D$2:$D$4 nên tôi đã di chuyển dữ liệu ra vùng khác và sửa lại địa chỉ trong công thức để test:

=SUMPRODUCT(COUNTIF(OFFSET($H$2:$H$[COLOR=Red][B]100[/B][/COLOR];;MATCH($B5;$H$1:$[B][COLOR=Red]Z[/COLOR][/B]$1;0)-1);$F$2:$F$[B][COLOR=Red]100[/COLOR][/B]))

Cảm ơn Ndu rất rất nhiều.

 

[SA_DQ]

Với mình, như vầy ít fức tạp hơn!

Option Explicit:              Option Base 1
Sub ThgKe()
 Dim Rng As Range, Clls As Range, Col As Byte, Jj As Long
 
 Set Rng = [D1].CurrentRegion
 ReDim MDL(Rng.Columns.Count)
 For Each Clls In Range([B2], [B65500].End(xlUp))
   Col = Rng.Find(Clls.Value, , xlFormulas, xlWhole).Column - Rng.Cells(1, 0).Column
   MDL(Col) = MDL(Col) + 1
 Next Clls
 For Each Clls In Range([c1].End(xlDown), [c65500].End(xlUp))
   Jj = Jj + 1
   With Clls.Offset(, 1)
      .Value = MDL(Jj)
   End With
 Next Clls
End Sub

Chúc cả nhà vui vẻ!

 

[concogia]

Option Explicit:              Option Base 1
Sub ThgKe()
 Dim Rng As Range, Clls As Range, Col As Byte, Jj As Long
 
 Set Rng = [D1].CurrentRegion
 ReDim MDL(Rng.Columns.Count)
 For Each Clls In Range([B2], [B65500].End(xlUp))
   Col = Rng.Find(Clls.Value, , xlFormulas, xlWhole).Column - Rng.Cells(1, 0).Column
   MDL(Col) = MDL(Col) + 1
 Next Clls
 For Each Clls In Range([c1].End(xlDown), [c65500].End(xlUp))
   Jj = Jj + 1
   With Clls.Offset(, 1)
      .Value = MDL(Jj)
   End With
 Next Clls
End Sub

Chúc cả nhà vui vẻ!

Chúc cả nhà vui vẻ, cả nhà chưa vui vẻ, vì code chạy không thông ( cũng có thể máy em thiếu cái gì nên không chịu chạy), chỗ cái tím sắc (Col = Rng.Find(Clls.Value, , xlFormulas, xlWhole).Column - Rng.Cells(1, 0).Column) Thầy sửa lại cho nó chạy đi thì cả nhà vui vẻ
Chúc Thầy vui vẻ
Huhu, "xó zì" Thầy Sa và ChanhTQ, tại mình dốt quá hổng kiểm tra kỹ, có đứa nào nó chèn vào cell c2 một cái công thức ( hổng phải mình) làm code Thầy bị nghẽn. Hổng dám trả lời Thầy ChanhTQ mà chỉ sửa ở đây cho đỡ quê quê
Cả nhà vui vẻ
Chúc Thầy Sa vui vẻ

 

[ChanhTQ@]

Chúc cả nhà vui vẻ, cả nhà chưa vui vẻ, vì code chạy không thông ( cũng có thể máy em thiếu cái gì nên không chịu chạy), chỗ cái tím sắc (Col = Rng.Find(Clls.Value, , xlFormulas, xlWhole).Column - Rng.Cells(1, 0).Column) Thầy sửa lại cho nó chạy đi thì cả nhà vui vẻ
Chúc Thầy vui vẻ

Liên quan đến việc báo lỗi này có thể là:

Rng Is Nothing: Bạn xem Rng có tồn tại hay không (?)

Clls.Value Có ở 1 trong những ô của Rng hay không
(Nếu phương thức FIND() không tìm thấy dĩ nhiên báo lỗi)

& Cuối cùng: Nó báo câu gì mà bạn cho là lỗi vậy?

 

[TrungChinhs]

Nếu dùng VBA thì tôi cũng xin có ý kiến "cho thêm phần sôi nổi".

Sub ThongKe()
  On Error Resume Next
  Application.ScreenUpdating = False
  [b:c].ClearContents
  [h1:z1].SpecialCells(2).Copy
  [b5].PasteSpecial , Transpose:=True
  Application.CutCopyMode = False
  For Each cls1 In [f2:f100].SpecialCells(2)
    For Each cls2 In [b5:b100].SpecialCells(2)
      temp = [h2:z100].Find(cls1).End(3)
      If temp = cls2.Value Then cls2.Offset(, 1) = cls2.Offset(, 1).Value + 1
    Next
  Next
 End Sub

 

[ndu96081631]

Nếu dùng code sao ta không tạo 1 UDF nhỉ?
Tôi dùng cái này:

Function DCountIf(SrcRng As Range, FStr As String, FRng As Range) As Long
  Dim Clls As Range
  On Error Resume Next
  With FRng.Resize(1).Find(FStr, , , 1)
    For Each Clls In Intersect(FRng.Offset(1), FRng, .EntireColumn)
      DCountIf = DCountIf + WorksheetFunction.CountIf(SrcRng, Clls)
    Next
  End With
End Function

Hoặc gọn hơn:

Function DCountIf(SrcRng As Range, FStr As String, FRng As Range) As Long
  On Error Resume Next
  With FRng.Resize(1).Find(FStr, , , 1)
    With Intersect(FRng.Offset(1), FRng, .EntireColumn)
      DCountIf = Evaluate("SUMPRODUCT(COUNTIF(" & .Address & "," & SrcRng.Address & "))")
    End With
  End With
End Function

 

[Dauthivan]

SUM vẫn được anh à!
Dùng SUMPRODUCT để khỏi phải Ctrl + Shift + Enter ấy mà (vì bản thân SUMPRODUCT là hàm mãng nên cứ Enter bình thường)
Có thế thôi!

Em vẫn gà mờ về một số hàm, thày có thể nó sơ qua ví dụ cho em một số hàm nào mà bản thân nó đã là hàm mảng rồi không ah?

 

[ndu96081631]

Em vẫn gà mờ về một số hàm, thày có thể nó sơ qua ví dụ cho em một số hàm nào mà bản thân nó đã là hàm mảng rồi không ah?

Hàm ROW, COLUMN bản thân nó là mảng rồi
Ví dụ bạn gõ công thức =ROW(C5:C10) rồi Enter.. xong, quét chọn đoạn công thức trên thanh Formula rồi bấm F9 ---> Sẽ thấy kết quả trả về gồm nhiều giá trị

 

[Phanhanhdai]

Đương nhiên là được chứ anh!
- Gõ vào C11 chữ: Nhóm A
- Gõ vào C12 chữ: Nhóm B
- Gõ vào C13 chữ: Nhóm C
- Dùng công thức COUNTIF kết hợp SUMPRODUCT như sau:

=SUMPRODUCT(COUNTIF(OFFSET($D$2:$D$4,,MATCH($C11,$D$1:$F$1,0)-1),$B$2:$B$9))

Khỏi cột phụ!
Offset trong công thức nhằm mục địch dùng 1 công thức có thể kéo fill xuống!
Nếu muốn tách riêng từng công thức, có thể bỏ Offset, sẽ càng gọn hơn
Chẳng hạn:

D11 =SUMPRODUCT(COUNTIF($D$2:$D$4,$B$2:$B$9))

D12 =SUMPRODUCT(COUNTIF($E$2:$E$4,$B$2:$B$9))

D13 =SUMPRODUCT(COUNTIF($F$2:$F$4,$B$2:$B$9))

Thưa thày, bài toán này nếu dùng kiểu Pivot table có giải quyết được vấn đề không ah?

 

[bimbim1412]

Em thấy công thức đơn giản thế này cũng được mà

=SUMPRODUCT(COUNTIF($B$2:$B$9,OFFSET($D$2:$D$4,,ROWS($1:1)-1)))