Giúp lập công thức lọc và sắp xếp dữ liệu

amesan · 11/9/09

Xin chào các bạn

Nhờ các bạn xem và lập giúp công thức lọc và sắp xếp dữ liệu cho bài toán trong file đính kèm. Cám ơn nhiều

huuthang_bd · 11/9/09

amesan đã viết:
Xin chào các bạn

Nhờ các bạn xem và lập giúp công thức lọc và sắp xếp dữ liệu cho bài toán trong file đính kèm. Cám ơn nhiều

Xem file nhé.
Ngoài ra có thể dùng Macro.

ndu96081631 · 11/9/09

amesan đã viết:
Xin chào các bạn

Nhờ các bạn xem và lập giúp công thức lọc và sắp xếp dữ liệu cho bài toán trong file đính kèm. Cám ơn nhiều

Thử code này xem:

PHP:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$C$2" Then
    Range("A4").CurrentRegion.ClearContents
    With Sheet1.Range("A2").CurrentRegion
      Intersect(.Cells, .Offset(1, 2)).NumberFormat = "m"
      .AutoFilter 3, Target
      .SpecialCells(12).Copy
      Range("A4").PasteSpecial 3
      .AutoFilter
      Intersect(.Cells, .Offset(1, 2)).NumberFormat = "dd-mm-yyyy"
    End With
    With Range("A4").CurrentRegion.Resize(, 4)
      If .Rows.Count > 1 Then
        .Resize(, 1).SpecialCells(2, 1).Value = Evaluate("ROW(R:R)")
        .Offset(1, 3).Resize(.Rows.Count - 1, 1).Value = "=DAY(RC[-1])+Year(RC[-1])/10000"
        .Sort .Cells(2, 4), 1, , , , , , xlGuess
        .Offset(1, 3).Resize(.Rows.Count - 1, 1).Clear
      End If
    End With
    Target.Select
  End If
End Sub

huuthang_bd · 11/9/09

ndu96081631 đã viết:

Thử code này xem:

PHP:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$C$2" Then
    Range("A4").CurrentRegion.ClearContents
    With Sheet1.Range("A2").CurrentRegion
      Intersect(.Cells, .Offset(1, 2)).NumberFormat = "m"
      .AutoFilter 3, Target
      .SpecialCells(12).Copy
      Range("A4").PasteSpecial 3
      .AutoFilter
      Intersect(.Cells, .Offset(1, 2)).NumberFormat = "dd-mm-yyyy"
    End With
    With Range("A4").CurrentRegion.Resize(, 4)
      If .Rows.Count > 1 Then
        .Resize(, 1).SpecialCells(2, 1).Value = Evaluate("ROW(R:R)")
        .Offset(1, 3).Resize(.Rows.Count - 1, 1).Value = "=DAY(RC[-1])+Year(RC[-1])/10000"
        .Sort .Cells(2, 4), 1, , , , , , xlGuess
        .Offset(1, 3).Resize(.Rows.Count - 1, 1).Clear
      End If
    End With
    Target.Select
  End If
End Sub

Code này chạy không đúng nha. Bạn kiểm tra lại tháng 2, tháng 7.
Và bị lỗi phần đánh số thứ tự.

chucuoi92 · 11/9/09

huuthang_bd đã viết:
Xem file nhé.
Ngoài ra có thể dùng Macro.

Tôi tải file của bạn về mà sao hàm bị lỗi hết(#VALUE!)
Bạn xem lại cái coi!

huuthang_bd · 11/9/09

Chu.cuoi92 đã viết:
Tôi tải file của bạn về mà sao hàm bị lỗi hết(#VALUE!)
Bạn xem lại cái coi!

Trong công thức tôi dùng toàn những hàm thông thường vì vậy cũng không hiểu tại sao máy bạn lại báo lỗi.
Bạn gửi lại file bị báo lỗi tôi kiểm tra lại xem.

amesan · 11/9/09

huuthang_bd đã viết:
Xem file nhé.
Ngoài ra có thể dùng Macro.

Cám ơn Huuthang & ndu nhiều. Vậy mà mình nghĩ mãi không ra ....

chucuoi92 · 11/9/09

huuthang_bd đã viết:
Trong công thức tôi dùng toàn những hàm thông thường vì vậy cũng không hiểu tại sao máy bạn lại báo lỗi.
Bạn gửi lại file bị báo lỗi tôi kiểm tra lại xem.

Tôi tìm ra rồi! trong DATA có một số ngày bị sai định dạng và có cả dấu (') ở đầu nữa.

amesan · 11/9/09

Chào Huu thang
Bạn có thể giải thích thêm về name GPE =IF(MONTH(NS)='Sinh nhat'!$C$2,DAY(NS)*10^7+(3000-YEAR(NS))*1000+OFFSET(NS,,-2),"") và công thức DATE(3000-INT(MOD(SMALL(GPE,ROW(A1)),10^7)/1000),$C$2,INT(SMALL(GPE,ROW(A1))/10^7)) không? Cám ơn

huuthang_bd · 11/9/09

amesan đã viết:
Chào Huu thang
Bạn có thể giải thích thêm về name GPE =IF(MONTH(NS)='Sinh nhat'!$C$2,DAY(NS)*10^7+(3000-YEAR(NS))*1000+OFFSET(NS,,-2),"") và công thức DATE(3000-INT(MOD(SMALL(GPE,ROW(A1)),10^7)/1000),$C$2,INT(SMALL(GPE,ROW(A1))/10^7)) không? Cám ơn

Mã:

GPE=IF(MONTH(NS)='Sinh nhat'!$C$2,DAY(NS)*10^7+(3000-YEAR(NS))*1000+OFFSET(NS,,-2),"")

Name GPE sẽ tạo ra một mảng có phần tử bằng số nhân viên. Những ứng với nhân viên có ngày sinh trong tháng cần xem là một giá trị số trong mảng, ứng với những nhân viên có ngày sinh khác tháng cần xem là giá trị "" trong mảng. Các giá trị số được tạo ra để phục vụ cho việc sắp xếp thứ tự. Bạn cắt công thức ra thàng từng đoạn rồi tự xem nhé. Giải thích sẽ rất dài dòng.

Mã:

DATE(3000-INT(MOD(SMALL(GPE,ROW(A1)),10^7)/1000),$C$2,INT(SMALL(GPE,ROW(A1))/10^7))

Công thức này đi ngược lại với Name GPE. Từ ngày tháng, bạn tạo ra con số trong Name GPE như thế nào thì bây giờ từ con số đó bạn tách lại thành ngày tháng.

tam8678 · 11/9/09

huuthang_bd đã viết:
Code này chạy không đúng nha. Bạn kiểm tra lại tháng 2, tháng 7.
Và bị lỗi phần đánh số thứ tự.

-------

Code này chạy không đúng nha. Bạn kiểm tra lại tháng 2, tháng 7.

Các tháng ra kết quả sai do khâu nhập liệu không chuẩn.

Và bị lỗi phần đánh số thứ tự

Đúng, phần này bị lỗi .

ndu96081631 · 12/9/09

tam8678 đã viết:
Đúng, phần này bị lỗi .

Đúng là do em sơ suất, lý ra sort trước rồi mới đánh STT, đàng này em lại làm ngược lại
Vậy ta chỉ cần mang code đánh STT
.Resize(, 1).SpecialCells(2, 1).Value = Evaluate("ROW(R:R)")
Cho nằm dưới code Sort là được rồi
Còn không có thể cải tiến thêm (bỏ cột phụ)
Thay

PHP:

    With Range("A4").CurrentRegion.Resize(, 4)
      If .Rows.Count > 1 Then
        .Resize(, 1).SpecialCells(2, 1).Value = Evaluate("ROW(R:R)")
        .Offset(1, 3).Resize(.Rows.Count - 1, 1).Value = "=DAY(RC[-1])+Year(RC[-1])/10000"
        .Sort .Cells(2, 4), 1, , , , , , xlGuess
        .Offset(1, 3).Resize(.Rows.Count - 1, 1).Clear
      End If
    End With

Thành:

PHP:

    With Range("A4").CurrentRegion
      If .Rows.Count > 1 Then
        .Resize(, 1).SpecialCells(2, 1).Value = "=DAY(RC[-1])+Year(RC[-1])/10000"
        .Sort .Cells(2, 1), 1, , , , , , xlGuess
        .Resize(, 1).SpecialCells(3, 1).Value = Evaluate("ROW(R:R)")
      End If
    End With

visaolaplanh · 14/9/09

Lọc và tính tổng giá trị

Chào các thành viên trong diễn đàn.
Hôm trước mình có hỏi về cách lọc và tính tổng giá trị dữ liệu trong bảng tính và đã có bạn giới thiệu về bài viết của bạn ndu nhưng mình tìm chưa được. Mong các bạn chỉ bảo mình chỗ này với, cảm ơn nhiều.
Để tính ra cột kết quả này mình làm thủ công tốn thời gian lắm vì số liệu bảng tính quá nhiều.

ndu96081631 · 14/9/09

visaolaplanh đã viết:
Chào các thành viên trong diễn đàn.
Hôm trước mình có hỏi về cách lọc và tính tổng giá trị dữ liệu trong bảng tính và đã có bạn giới thiệu về bài viết của bạn ndu nhưng mình tìm chưa được. Mong các bạn chỉ bảo mình chỗ này với, cảm ơn nhiều.
Để tính ra cột kết quả này mình làm thủ công tốn thời gian lắm vì số liệu bảng tính quá nhiều.

Hình như bạn chưa thử công cụ Consolidate thì phải, nó hoàn toàn đáp ứng nhu cầu của bạn mà không cần bất cứ công thức nào
Hãy thử với file đính kèm này, code được thu từ record macro quá trình Consolidate và chỉnh sửa lại

PHP:

Sub Test()
  With Application.InputBox("Chon vung du lieu nguon", Type:=8)
    ActiveCell.Consolidate "'" & .Parent.Name & "'!" & .Address(, , 2), 9, 1, 1
    ActiveCell.Resize(1, 2).Value = .Resize(1, 2).Value
    With ActiveCell.CurrentRegion
      .Sort .Cells(1, 1), 1, , , , , , xlYes
    End With
  End With
End Sub

MinhCong · 14/9/09

visaolaplanh đã viết:
Chào các thành viên trong diễn đàn.
Hôm trước mình có hỏi về cách lọc và tính tổng giá trị dữ liệu trong bảng tính và đã có bạn giới thiệu về bài viết của bạn ndu nhưng mình tìm chưa được. Mong các bạn chỉ bảo mình chỗ này với, cảm ơn nhiều.
Để tính ra cột kết quả này mình làm thủ công tốn thời gian lắm vì số liệu bảng tính quá nhiều.

1/Bạn chọn dữ liệu cột A ($A1$:$A14$) vào Data -> Filter -> advanced filter bấm lựa chọn copy to another location bấm dấu tick vào ô Unique records only. Trong ô copy to bấm vào vị trí muốn đặt (1 ô cụ thể trong bài của bạn là sheet1$C1$) -> ok.
2/Bấm lựa chọn vùng mà bạn vừa được kết quả vào data sort sắp xếp lại dữ liệu.
3/Kết quả sau cùng:
D2=SUMIF($A$2:$A$14;C2;$B$2:$B$14)

ndu96081631 · 14/9/09

MinhCong đã viết:
1/Bạn chọn dữ liệu cột A ($A1$:$A14$) vào Data -> Filter -> advanced filter bấm lựa chọn copy to another location bấm dấu tick vào ô Unique records only. Trong ô copy to bấm vào vị trí muốn đặt (1 ô cụ thể trong bài của bạn là sheet1$C1$) -> ok.
2/Bấm lựa chọn vùng mà bạn vừa được kết quả vào data sort sắp xếp lại dữ liệu.
3/Kết quả sau cùng:
D2=SUMIF($A$2:$A$14;C2;$B$2:$B$14)

Tôi góp ý thế này:
- Đã dùng công thức thì công thức toàn bộ, luôn cả phần trích duy nhất
- Nếu đã dùng đến công cụ hổ trợ sao không dùng cho đúng ---> Thay vì Advanced Filter sao bạn không dùng Consolidate ---> Nó sẽ ra kết quả luôn

tam8678 · 14/9/09

ndu96081631 đã viết:
Hình như bạn chưa thử công cụ Consolidate thì phải, nó hoàn toàn đáp ứng nhu cầu của bạn mà không cần bất cứ công thức nào

.
--------
Cách tốt nhất (nếu được) là anh thấy chú NDU nên minh họa bằng hinh ảnh về cách xử dụng công cụ Consolidate ( anh học từ chú, anh hiểu, nhưng anh không biết minh họa ), để có thể chủ động trong việc làm là tối ưu nhất.

ndu96081631 · 14/9/09

tam8678 đã viết:
.
--------
Cách tốt nhất (nếu được) là anh thấy chú NDU nên minh họa bằng hinh ảnh về cách xử dụng công cụ Consolidate ( anh học từ chú, anh hiểu, nhưng anh không biết minh họa ), để có thể chủ động trong việc làm là tối ưu nhất.

Anh ơi! Trên GPE đã có bài hướng dẩn về Consolidate với hình ảnh minh họa rồi --> Anh tìm xem!

tam8678 · 14/9/09

ndu96081631 đã viết:
Anh ơi! Trên GPE đã có bài hướng dẩn về Consolidate với hình ảnh minh họa rồi --> Anh tìm xem!

--------
Ý anh là em minh họa, hoặc cho bạn ấy đường dẫn (anh nhớ là em có hướng dẩn cho anh trong bài Mã khách hàng duy nhất và cùng loại), anh tìm làm gì: anh thuộc rồi chú ạ.
Nhắc lại: anh không biết làm đường dẫn trên GPE, anh biết thì đã làm cho bạn visaolaplanh rồi.

Giúp lập công thức lọc và sắp xếp dữ liệu

Thành viên mới

File đính kèm

Chuyên gia GPE

File đính kèm

Huyền thoại GPE

File đính kèm

Chuyên gia GPE

Thành viên lười biếng

Chuyên gia GPE

Thành viên mới

Thành viên lười biếng

Thành viên mới

Chuyên gia GPE

Đời Xá Chi

Huyền thoại GPE

Thành viên mới

File đính kèm

Huyền thoại GPE

File đính kèm

Thành viên gắn bó

Huyền thoại GPE

Đời Xá Chi

Huyền thoại GPE

Đời Xá Chi

Đếm ngược thời gian