Làm thể nào để vét cạn nếu cùng 1 từ khóa yêu cầu

vugialinh · 24/8/11

Nhờ các anh chị trên diễn đàn giúp đỡ giải giúp tôi bài toán này:

Giả sử tôi có 2 sheet, sheet CN nghỉ và Sheet kiem tra (có file đính kèm)

Yêu cầu của bài toán:
Dựa vào số CMND để cho biết tất cả số thẻ và lý do nghỉ của các lần công nhân nghỉ việc (kết quả giống ở sheet kiem tra)

Tôi dùng hàm Vlookup hoặc Index và Match thì chỉ lấy được giá trị đầu tiên tìm thấy, còn các giá trị sau hàm lại bỏ qua không duyệt.

Có cách nào để "vét" hết tất cả không?

Rất mong các anh chị trên diễn đàn giúp đỡ

Xin cảm ơn

==========================================================
(Xin lỗi, tôi post bài tiếp vào đây để các anh chị tiện đọc hơn)

Cám ơn tất cả các anh chị đã quan tâm đến bài viết này. Chỉ chưa đầy 4 tiếng đồng hồ mà đã có rất nhiều anh chị vào giúp đỡ và bàn luận sôi nổi. vugialinh xin nói rõ thêm 1 chút về bài toán để các anh chị có thể giúp đỡ tôi tốt hơn.

1. File gửi kèm chỉ là ví dụ đã được rút gọn, còn thực tế trong cty có thể có lúc đến hơn 10.000 công nhân và rất nhiều cột thông tin khác nữa. Đã dùng Filter nhưng không ổn (không thống kê nhanh như yêu cầu; có những lúc lọc rất lâu, thậm chí là treo máy)
2. Công nhân nghỉ và xin vào làm lại có thể sẽ là n lần và mỗi lần vào làm lại được cấp 1 số thẻ khác trước.
3. Trong sheet kiem tra tôi muốn hiển thị như đã ví dụ (còn có thể kiểm tra nhiều số CMND khác nữa bằng cách nhập vào các hàng tiếp theo)
4. Làm như bạn Luc Van; HYen17 và bạn concogia rất hợp ý và dễ hiểu nhưng vì tôi đang làm giúp cho phòng nhân sự (chứ không phải tôi dùng) nên nếu làm macro như các bạn, lỡ chương trình diệt VR "tóm gọn" thì các anh chị trong phòng chắc sẽ bị "loạn". Còn làm theo các anh chị khác, bài toán cũng đã được giải quyết nhưng chưa được như mong muốn.

-> Vậy có cách nào khác để giải quyết các bài toán đặt ra? Rất mong nhận được sự giúp đỡ của mọi người.

Xin cảm ơn.

P/S: Như bạn Luc Van; HYen17 và bạn concogia đã hướng dẫn thì các cột Số thẻ, Lý do nghỉ phải cố định như vậy chứ thay đổi ra cột khác là không được. Các bạn có thể cho phép người dùng tự chọn cột thứ n (cột cần thống kê) trong vùng dữ liệu được không?

hophoang · 24/8/11

Bạn xem thử có đúng không

Luc Van · 24/8/11

vugialinh đã viết:
Nhờ các anh chị trên diễn đàn giúp đỡ giải giúp tôi bài toán này:

Giả sử tôi có 2 sheet, sheet CN nghỉ và Sheet kiem tra (có file đính kèm)

Yêu cầu của bài toán:
Dựa vào số CMND để cho biết tất cả số thẻ và lý do nghỉ của các lần công nhân nghỉ việc (kết quả giống ở sheet kiem tra)

Tôi dùng hàm Vlookup hoặc Index và Match thì chỉ lấy được giá trị đầu tiên tìm thấy, còn các giá trị sau hàm lại bỏ qua không duyệt.

Có cách nào để "vét" hết tất cả không?

Rất mong các anh chị trên diễn đàn giúp đỡ

Xin cảm ơn

Có phải bạn muốn thế này.

PHP:

Option Explicit
Function JoinIf(CritArr, Criteria, ResArr, Sep As String) As String
  Dim CritTmp, ResTmp, i As Long, j As Long, k As Long, Arr()
  CritTmp = CritArr: ResTmp = ResArr
  On Error Resume Next
  For i = LBound(CritTmp, 1) To UBound(CritTmp, 1)
    For j = LBound(CritTmp, 2) To UBound(CritTmp, 2)
      If CritTmp(i, j) = Criteria Then
        k = k + 1
        ReDim Preserve Arr(1 To k)
        Arr(k) = ResTmp(i, j)
      End If
    Next
  Next
  JoinIf = Join(Arr, Sep)
End Function

anh033 · 24/8/11

Copy toàn bộ cột số CMND sang 1 cột bên sheet kiemtra rồi làm cái data validation cho ô D1, thế là khỏi cần nhập, chỉ chọn ở danh sách.( cho cái test1 của bạn hophoang)

anh033 · 24/8/11

Bạn Luc van tôi tải về mở ra sao toàn thấy #Name? vậy, tôi dùng Excel 2007.

bebo021999 · 24/8/11

Xem file đính kèm nhé. Tôi để sẵn cho 4 lần nghỉ. Nếu cần nhiều cột hơn thì insert rồi copy qua nhé.

SA_DQ · 24/8/11

Dùng hàm tự tạo thử xem nha.

Trong trang tính có ô đã được gán tên cần dùng, bạn làm ơn đừng xóa nó nha! --=0

concogia · 24/8/11

Topic này Zdui quá
Mình tham gia một cách
Nhập dữ liệu, chọn sheet "kiemtra" xem kết quả

Mã:

Private Sub Worksheet_Activate()
    Dim Vung, I, d, Mg(), Ws, K, kK, Dau
    Set d = CreateObject("scripting.dictionary")
    Set Ws = Sheets("CN Nghi"): Dau = " - "
    Vung = Ws.Range(Ws.[b3], Ws.[b10000].End(xlUp)).Resize(, 5).Value
    ReDim Mg(1 To UBound(Vung), 1 To 4)
        For I = 1 To UBound(Vung)
            If Not d.exists(Vung(I, 1)) Then
                K = K + 1
                d.Add Vung(I, 1), K
                Mg(K, 4) = 1: Mg(K, 1) = Vung(I, 1): Mg(K, 2) = "ST lân " & Mg(K, 4) & ": " & Vung(I, 2): Mg(K, 3) = Vung(I, 5)
            Else
                kK = d.Item(Vung(I, 1))
                Mg(kK, 4) = Mg(kK, 4) + 1
                Mg(kK, 2) = Mg(kK, 2) & Dau & "ST lân " & Mg(kK, 4) & ": " & Vung(I, 2)
                Mg(kK, 3) = Mg(kK, 3) & Dau & Vung(I, 5)
            End If
        Next I
    [a3:d10000].ClearContents
    [b3].Resize(d.Count, 3) = Mg
    Range([b3], [b10000].End(xlUp)).Offset(, -1) = [row(A:A)]
End Sub

anh033 · 24/8/11

Hyen17 lại toàn #name?, bebo021999 thì cả bảng rối mắt, người ta chỉ kiểm tra một người thôi thì sao, mà 4 lần chưa đủ, thêm cột thành rườm. Chung qui như bạn Hophoang làm là tối ưu, chọn ai thì ra ngay rất gọn.

bebo021999 · 24/8/11

anh033 đã viết:
Hyen17 lại toàn #name?, bebo021999 thì cả bảng rối mắt, người ta chỉ kiểm tra một người thôi thì sao, mà 4 lần chưa đủ, thêm cột thành rườm...

Theo yêu cầu của tác giả:

Vì thấy có STT 1, suy ra sẽ có 2,3 nên kết quả sẽ là:

Trên thực tế, 1 công nhân có thể nghỉ >3 lần, để khỏi phải hỏi đi hỏi lại, dự trù luôn 4 lần trở lên

ndu96081631 · 24/8/11

anh033 đã viết:
Hyen17 lại toàn #name?, bebo021999 thì cả bảng rối mắt, người ta chỉ kiểm tra một người thôi thì sao, mà 4 lần chưa đủ, thêm cột thành rườm. Chung qui như bạn Hophoang làm là tối ưu, chọn ai thì ra ngay rất gọn.

Còn tôi thì cho rằng TỐI ƯU nhất là chẳng code, công thức gì cả ---> AutoFilter cái ra ngay!

vugialinh · 25/8/11

Cám ơn tất cả các anh chị đã quan tâm đến bài viết này. Chỉ chưa đầy 4 tiếng đồng hồ mà đã có rất nhiều anh chị vào giúp đỡ và bàn luận sôi nổi. vugialinh xin nói rõ thêm 1 chút về bài toán để các anh chị có thể giúp đỡ tôi tốt hơn.

1. File gửi kèm chỉ là ví dụ đã được rút gọn, còn thực tế trong cty có thể có lúc đến hơn 10.000 công nhân và rất nhiều cột thông tin khác nữa. Đã dùng Filter nhưng không ổn (không thống kê nhanh như yêu cầu; có những lúc lọc rất lâu, thậm chí là treo máy)
2. Công nhân nghỉ và xin vào làm lại có thể sẽ là n lần và mỗi lần vào làm lại được cấp 1 số thẻ khác trước.
3. Trong sheet kiem tra tôi muốn hiển thị như đã ví dụ (còn có thể kiểm tra nhiều số CMND khác nữa bằng cách nhập vào các hàng tiếp theo)
4. Làm như bạn Luc Van; HYen17 và bạn concogia rất hợp ý và dễ hiểu nhưng vì tôi đang làm giúp cho phòng nhân sự (chứ không phải tôi dùng) nên nếu làm macro như các bạn, lỡ chương trình diệt VR "tóm gọn" thì các anh chị trong phòng chắc sẽ bị "loạn". Còn làm theo các anh chị khác, bài toán cũng đã được giải quyết nhưng chưa được như mong muốn.

-> Vậy có cách nào khác để giải quyết các bài toán đặt ra? Rất mong nhận được sự giúp đỡ của mọi người.

Xin cảm ơn.

P/S: Như bạn Luc Van; HYen17 và bạn concogia đã hướng dẫn thì các cột Số thẻ, Lý do nghỉ phải cố định như vậy chứ thay đổi ra cột khác là không được. Các bạn có thể cho phép người dùng tự chọn cột thứ n (cột cần thống kê) trong vùng dữ liệu được không?

ndu96081631 · 25/8/11

vugialinh đã viết:
Cám ơn tất cả các anh chị đã quan tâm đến bài viết này. Chỉ chưa đầy 4 tiếng đồng hồ mà đã có rất nhiều anh chị vào giúp đỡ và bàn luận sôi nổi. vugialinh xin nói rõ thêm 1 chút về bài toán để các anh chị có thể giúp đỡ tôi tốt hơn.

1. File gửi kèm chỉ là ví dụ đã được rút gọn, còn thực tế trong cty có thể có lúc đến hơn 10.000 công nhân và rất nhiều cột thông tin khác nữa. Đã dùng Filter nhưng không ổn (không thống kê nhanh như yêu cầu; có những lúc lọc rất lâu, thậm chí là treo máy)
2. Công nhân nghỉ và xin vào làm lại có thể sẽ là n lần và mỗi lần vào làm lại được cấp 1 số thẻ khác trước.
3. Trong sheet kiem tra tôi muốn hiển thị như đã ví dụ (còn có thể kiểm tra nhiều số CMND khác nữa bằng cách nhập vào các hàng tiếp theo)
4. Làm như bạn Luc Van; HYen17 và bạn concogia rất hợp ý và dễ hiểu nhưng vì tôi đang làm giúp cho phòng nhân sự (chứ không phải tôi dùng) nên nếu làm macro như các bạn, lỡ chương trình diệt VR "tóm gọn" thì các anh chị trong phòng chắc sẽ bị "loạn". Còn làm theo các anh chị khác, bài toán cũng đã được giải quyết nhưng chưa được như mong muốn.

-> Vậy có cách nào khác để giải quyết các bài toán đặt ra? Rất mong nhận được sự giúp đỡ của mọi người.

Xin cảm ơn.

P/S: Như bạn Luc Van; HYen17 và bạn concogia đã hướng dẫn thì các cột Số thẻ, Lý do nghỉ phải cố định như vậy chứ thay đổi ra cột khác là không được. Các bạn có thể cho phép người dùng tự chọn cột thứ n (cột cần thống kê) trong vùng dữ liệu được không?

Vậy bạn có thể suy nghĩ theo hướng dùng PivotTable thử xem!
Thứ nhất: Đây là công cụ có sẳn và tốc độ tính toán rất nhanh
Thứ hai: Không có tí công thức nào nên không cần lo file nặng
Thứ ba: không có tí macro nào nên không cần lo chương trình diệt virus xóa mất
Thứ tư: PivotTable có khả năng cập nhật khi dữ liệu thay đổi

vugialinh · 25/8/11

ndu96081631 đã viết:
Vậy bạn có thể suy nghĩ theo hướng dùng PivotTable thử xem!
Thứ nhất: Đây là công cụ có sẳn và tốc độ tính toán rất nhanh
Thứ hai: Không có tí công thức nào nên không cần lo file nặng
Thứ ba: không có tí macro nào nên không cần lo chương trình diệt virus xóa mất
Thứ tư: PivotTable có khả năng cập nhật khi dữ liệu thay đổi

Mình chưa dùng PivotTable, bạn có thể ví dụ minh họa giúp mình được không? Cảm ơn bạn nhiều

ndu96081631 · 25/8/11

vugialinh đã viết:
Mình chưa dùng PivotTable, bạn có thể ví dụ minh họa giúp mình được không? Cảm ơn bạn nhiều

Bạn có thể download tài liệu về PivotTable để nghiên cứu:
http://www.giaiphapexcel.com/diendan/threads/92958.Quà-tặng-thành-viên-nhân-dịp-SN4-Pivot-Table-2003
Ngoài ra, với file của bạn, tôi hướng dẫn chi tiết qua đoạn video clip sau đây:

[video=youtube;O_qAakP6HLI]http://www.youtube.com/watch?v=O_qAakP6HLI[/video]

vugialinh · 26/8/11

Rất sinh động và chi tiết, để mình làm thử. Cám ơn bạn nhiều

vugialinh · 26/8/11

Dùng PivotTable để thống kê rất nhanh nhưng có nhược điểm cùng lúc thống kê theo nhiều số CMND lại không được, và trong cùng 1 bảng thống kê lại lấy dữ liệu từ nhiều sheet (hay do tôi chưa biết rõ hết chức năng của PivotTable)

vu_tuan_manh_linh · 26/8/11

bạn thử dùng 2 Sub Worksheet_Change xem thế nào nhé. Tại Sheet CN nghi: Khi bạn nhập thêm số liệu mới vào, nó sẽ tự động ghi sang cột Số CMND ở sheet Kiem tra. Nếu là số mới thì ghi lại, là số đã có thì bỏ qua.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
Dim Gt As Variant
Dim kt As Boolean
kt = False
If Target.Column = "2" Then
Gt = Target.Value
For i = 3 To 30000
If Sheets("kiem tra").Cells(i, 2).Value = Gt Then
kt = True
End If
If Sheets("kiem tra").Cells(i, 2).Value = 0 Then Exit For
Next i
If kt = False Then
For i = 3 To 30000
If Sheets("kiem tra").Cells(i, 2).Value = 0 Then
Sheets("kiem tra").Cells(i, 2).Value = Gt
Exit For
End If
Next i
End If
End If
End Sub
-----------
Ở sheet Kiem tra, khi bạn chọn 1 dòng trong ô Validation... sẽ tự động lọc ra số thẻ và toàn bộ lý do nghỉ sang các cột bên cạnh:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer, j As Integer
Dim cmnd As Long
Dim sothe As Integer
Dim lydo(1 To 3000) As String
Application.ScreenUpdating = False
If Target.Address = "$B$3" Then
cmnd = Target.Value
For i = 3 To 30000
If Sheets("CN Nghi").Cells(i, 2).Value = cmnd Then
j = j + 1
sothe = Sheets("CN Nghi").Cells(i, 3).Value
lydo(j) = Sheets("CN Nghi").Cells(i, 6).Value
End If
Next i
Sheets("kiem tra").Range("C3

65536").ClearContents
Sheets("kiem tra").Cells(3, 3).Value = sothe
For i = 1 To j
Sheets("kiem tra").Cells(i + 2, 4).Value = lydo(i)
Next i
End If
Application.ScreenUpdating = True
End Sub

vu_tuan_manh_linh · 26/8/11

Đây là File đính kèm mà mình đã sửa. Bạn tham khảo, nếu cần thêm thông tin có thể sửa code theo ý muốn.
View attachment 69037

vugialinh · 27/8/11

vu_tuan_manh_linh đã viết:
Đây là File đính kèm mà mình đã sửa. Bạn tham khảo, nếu cần thêm thông tin có thể sửa code theo ý muốn.
View attachment 69037

Cám ơn bạn nhiều. Để mình thử xem kq thế nào?

Làm thể nào để vét cạn nếu cùng 1 từ khóa yêu cầu

Thành viên mới

File đính kèm

Thành viên tiêu biểu

File đính kèm

Thành viên tiêu biểu

File đính kèm

Thành viên mới

Thành viên mới

Thành viên gạo cội

File đính kèm

/(hông là gì!

File đính kèm

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

File đính kèm

Thành viên mới

Thành viên gạo cội

Huyền thoại GPE

Thành viên mới

Huyền thoại GPE

Thành viên mới

Huyền thoại GPE

Thành viên mới

Thành viên mới

linhvtm84@gmail.com

linhvtm84@gmail.com

Thành viên mới

Đếm ngược thời gian