Giải các bài tập về nhân sự từ đơn giản đến fức tạp

[SA_DQ]

Mình có dự định tạo ra topic này để các thành viên có nhu cầu nâng cao trình độ về VBA tham gia giải các bài tập.
Các bài tập xoay quanh về việc quản lý nhân sự của 1 cơ quan khoảng hơn 700 CNV

Bạn nào có nhu cầu thì nhấn nút "cảm ơn" để mình cập nhất danh sách;

Các bài tập sẽ do các SMOD, MOD của BOX này cũng như 1 số thành viên khác quan tâm ra đề, được chọn lọc & đưa lên.

Khai giảng kể từ lúc danh sách tham gia có hơn 9 thành viên đăng ký. Lúc đó mình sẽ đưa lên file CSDL xài trong suốt topic.
Thời gian giành cho mỗi bài tập của mỗi thành viên tham gia là 100 giờ được cộng thêm thời gian trước đó chưa sử dụng từ các bài trước.

(Thành viên đã được cập nhất vô danh sách xóa giúp nút "Cảm ơn" để đỡ rối nha.)
(Các bạn cho ý kiến thêm về quy chế các thành viên tham gia giúp nha)

-=DANH SÁCH ĐĂNG KÝ=-

STT | NICKNAME | Cập nhật |
01|NguyenManhHao|22/03|11h49|
02|Num1710|22/03|11h49|
03|MinhCong|22/03|16h40|
04|Bemyself|22/03|17h40|
05|NghiaPhuc|22/03|22h40|
06|KhoaVu87|23/03|13h40|
07|TungNguyen_KT|22/03|10h40|
08|ThaoVinh|23/03|16h40|
09|HHung84|23/03|21h40|
10|Thangsra|23/03|21h40|
11|LuuVanTam|24/03|10h0 |
12|Teo2010|24/03|14h30 |
13|TrungChinh|25/03|16h30 |
14|Tram.NguyenThi|01/04|11h30 |
15|HoangSPK|07/04|18h30 |
16|Hoile|07/04|18h30 |
17|VieHoai|07/04|18h30 |
18|CCNA_PTHue|10/04|08h00 |
. . .

Rất cảm ơn Lê Văn Duyệt nhận lòi làm giáo viên hướng dẫn;
Đang fần vân việc cập nhật anh DomFootwear: Hãy đợi thêm nha!

 

[tungnguyen_kt]

Mình có dự định tạo ra topic này để các thành viên có nhu cầu nâng cao trình độ về VBA tham gia giải các bài tập.
Các bài tập xoay quanh về việc quản lý nhân sự của 1 cơ quan khoảng hơn 700 CNV

Vậy là như em thì không tham gia được rồi phải không bác Sa.

 

[SA_DQ]

Đề các bài tập từ 01 đến 04​

Đề 01​

(Chọn 1 trong 2 đề)
​

Bài !A

Để tạo mã cho toàn bộ nhân viên trong cơ quan, người ta đã viết ra một thủ tục như sau:

Option Explicit
Sub TaoMaNV()
 Dim Rng As Range, Clls As Range
 Dim Nam As Integer, Jj As Integer, Thg As Byte
 Const Chu As String = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"
 
 For Each Clls In Range("B2:B" & [A65500].End(xlUp).Row)
   With Clls.Offset(, 4)
      Jj = Jj + 1
      Nam = Year(.Value) - 1950
      Thg = 12 * (Nam Mod 3)
      Clls.Value = "A" & Mid(Chu, 1 + Nam \ 3, 1) & _
         Mid(Chu, Thg + Month(.Value), 1) & _
         Mid(Chu, Day(.Value), 1) & Right("00" & CStr(Jj), 3)
   End With
 Next Clls
End Sub

Câu 1: Hãy diễn dịch các dòng lệnh trên ra nghĩa tiệng Việt;
Câu 2: Toán tử “\” khác với toàn tử “/” như thế nào
Câu 3: Hãy nêu hết những nét đặc trưng của mã nhân viên được tạo thành bằng thủ tục trên mà bạn biết;

Bài 1B
Hãy tham khảo công thức tại cột “I” để chuyển sang viết thành hàm người dùng tạo ra mã nhân viên;

Yêu cầu
(a) Các mã tạo thành có dạng chuỗi & có cùng độ dài
(b) Các mã đảm bảo không trùng lắp trong trường hợp phải lưu hồ sơ nhân viên 15 năm.
(c) Hàm người dùng có khả năng tạo mã tự động khi nhập dữ liệu cho nhân viên mới tuyển. (Lúc đó hàm sẽ được cung cấp mã nhân viên vừa nhập trức đó cũng như ngày tháng năm sinh người dự tuyển)

Đề 02​

Việc tiếp theo lí ra là fải chỉnh sửa lại CSDL tiếp quản;
Như chúng ta thấy trường "NoiSinh" còn rất lôi thôi;
Nhưng do công việc trước mắt đòi hỏi, nên ta đành gác lại lần sau;
Còn giờ đây ta fải thực thi nhiệm vụ này của Công đoàn giao:

Bài 2

Đề nghị các bạn lên danh sách sinh nhật của công nhân viên trong cơ quan của quý sau hay tháng sau liền kề tại 1 trang tính

Yêu cầu đề ra:

(1) Danh sách thể hiện chỉ trên 1 trang tính có tiêu đề danh sách cũng như tiêu đề các cột giống nhau.
(2) Danh sách được sắp theo thứ tự tăng dần của ngày cũng như của tháng (với trường hợp tạo danh sách quý)
(3) Cuối danh sách có phần tổng hợp số liệu, như số lượng người trong danh sách & số tiền fải chi (Nếu biết rằng mỗi người đến kỳ sinh nhật được chi 100.000đ

Bài 3
​

Chúng ta vừa tiếp quản CSDL từ người tiền nhiệm giao lại. Nhưng CSDL này còn có nhiều khuyết tật mà ta fải khắc fục
Nhiệm vụ bài này đề ra là nhờ sự trợ giúp của VBA chỉnh sửa trường nơi sinh cho đúng chuẩn.

Nội dung là:

(1) Dùng bộ thu, ghi lại macro lập danh sách duy nhất (bằng AdvancedFilter) của trường ‘NoiSinh’ đến cột ‘K’ của trang tính. (Macro này có tên là DSNoiSinh)
Quan sát kết quả thu được ta sẽ thấy người nhập liệu gây rất nhiều lỗi khi thực thi nhiệm vụ. Trong đó có lỗi để nhiều khoảng trắng cuối hay đầu từ.
(2) Để loại trừ lỗi này, ai đó đã thu 1 macro có nội dung như sau:

Sub ThemCot()
    Columns("G:G").Select
    Selection.Insert Shift:=xlToRight
    Range("H1").Select
    Selection.AutoFill Destination:=Range("G1:H1"), Type:=xlFillDefault
    Range("G1:H1").Select
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "=TRIM(RC[1])"
    Range("G2").Select
    Selection.AutoFill Destination:=Range("G2:G20"), Type:=xlFillDefault
    Range("G2:G20").Select
    Columns("G:G").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
End Sub

Nhiệm vụ của bạn là
Bỏ bớt những dòng lệnh do những hành động dư thừa khi thu macro đang có trong ThemCot ở trên
(3) Sửa lại sao cho macro lập công thức cho toàn bô CSDL chứ không fải chỉ 20 dòng. Như trong macro đã ghi.
(4) Viết thêm câu lệnh gọi thực hiện DSNoiSinh vô dòng cuối của ThemCot
(5) Viết tiếp thêm dòng lệnh xóa cột ‘G’ cũ (giờ là cột ‘H’)

Ghi chú: Một cách thô nhất để hiểu lần lượt các dòng lệnh là ta thêm dòng lệnh

   MsgBox “GPE”

sau những câu lệnh chưa hiểu, đễ xem macro làm điều gì với câu lệnh đó.

(Đã nhận được bài giải của NghiaPhuc, MinhCong; Rất mong chúng ta tiếp tục)

Bài 4​

Thực ra, trong một CSDL, chúng ta không nên để có quá nhiều thông tin trùng lắp
Những thông tin mà CSDL trên đang & sẽ bị trùng lắp đó là:
(*) Trường phái tính; Trường này có người khuyên chúng ta nên dùng kiểu dữ liệu Yes/No.. .
(*) Trường đơn vị (hay bộ phận); như nhân viên đang ở các phòng ban hay fân xưởng
(*) Trường nơi sinh mà chúng ta đang xét tới
(*) . . . .

Các trường sau, ta không nên nhập cả tên của chúng, như Phòng TCHC, PX Cơ điện,.. . . hay TP Hồ Chí Minh, hoặc Bà rịa-Vũng tàu

Mà chỉ nên mã gắn liền với chúng;
Như C để chỉ cho các nhân viên thuộc fòng TCHC; F – cho công nhân viên thuộc PX Cơ điện;. . . . .
F để chỉ cho những người sinh ra tại TP Hồ Chí Minh & K chỉ tới tỉnh Bà rịa-Vũng tàu. . . .
(Các bạn xem thêm trong tập tin đính kèm dưới đây)

Và đây là nội dung câu hỏi 4:

Trên trang tính mới, hãy lấy 1 ô nào đó, ví dụ B1. Dùng chức năng Validation . . hãy lập tại ô này danh sách duy nhất của các đơn vị.
Tiến hành viết 1 macro gắn liền với sự kiện sau:
Khi ta chọn 1 mã đơn vị nào đó thì các cột bên cạnh hiện lên danh sách của đơn vị đó một cách đầy đủ.
(Gợi ý: Dùng AdvancedFilter)


Chú ý:
Bài làm xong, gởi theo dạng tin nhắn tới LeVanDuyet & Sa_DQ;
Ngoài ra bạn có thể gởi những vấn đề bạn quan tâm liên quan đến bài này để được cộng đồng GPE giải đáp

Rất cảm ơn các bạn đã quan tâm.

 

[nghiaphuc]

To bác SA_DQ: Em có ý kiến về Bài 1A một xíu, bác nghĩ sao khi em bỏ biến Jj đi và thay câu lệnh

Clls.Value = "A" & Mid(Chu, 1 + Nam \ 3, 1) & _
    Mid(Chu, Thg + Month(.Value), 1) & _
    Mid(Chu, Day(.Value), 1) & [COLOR=red]Right("00" & CStr(Jj), 3)[/COLOR]

bởi câu lệnh:

Clls.Value = "A" & Mid(Chu, 1 + Nam \ 3, 1) & _
    Mid(Chu, Thg + Month(.Value), 1) & _
    Mid(Chu, Day(.Value), 1) & [COLOR=red]Format(Clls.Row - 1, "000")[/COLOR]

Liệu khi thay thế như vậy, code có thể chạy nhanh hơn không?

 

[SA_DQ]

Rất tốt, cảm ơn bạn đã có sự trao đổi như vậy.

Đồng ý với bạn về cách làm; Còn về nhanh chậm chưa dám nói;

Vì, trị trong biến là do fép cộng mà có, còn trị trong trang tính cũng fải tìm mới có;

Nhưng là một CSDL, cho nên có khi các hàng sẽ bị xóa vào buổi mát trời nào đó. (Vì có nhân viên bỏ việc giữa chừng; Bị buộc thôi việc hay thuyên chuyển, điều động do cấp trên,. . . . ). Lúc đó cũng rối một chút đó nhe!

 

[ChanhTQ@]

"Hãy nêu những nét đặc trưng của mã nhân viên được tạo thành bằng thủ tục trên"

(*) Trước tiên chúng luôn là 1 chuỗi gồm cả ký số & ký tự; Chúng có chiều dài như nhau;
(*) Với 1.000 nhân viên đầu tiên của cơ quan được áp mã nhân viên ta dự định sắp xếp theo trường này. Kết quả sẽ theo như xếp theo trường 'NgaySinh'

Nhân xét khác:
Mã này sẽ làm việc tốt khi cơ quan dưới 1.000 nhân viên;
Khi mã NV sắp vượt quá 1.000 ta fải sửa lại đôi chút, đó là:
Dời chữ cái "A" đến đứng vị trí thứ 4, thay vì đang đứng đầu tiên; (Tất nhiên 3 ký tự tiếp theo sẽ đứng đầu của mã)
Sau 1.000 người đầu tiên, thì bằng cách nào đó, chuyển kí tự "A" thành "B", (và cứ thế tăng. . . => "Z")

Mã NV sau cải tiến như vậy sẽ thỏa điều kiện lưu hồ sơ nhân viên sau khi nghỉ đến 15 năm.

(*) Còn đây là 1 bài toán khó:
Nếu tình cờ ai đó bị xóa mất dữ liệu 'NgaySinh', thì ta có thể truy tìm lại từ mã NV được không?. Bằng thủ công là được đó!

Chúc vui!

 

[SA_DQ]

Đây là lời giải của một bạn

Các 'pac oi, em noi that la xem ma em ko hieu gi het a, co bac nao cho em 1 bài mẫu ko ạ. email: hay_nice@yahoo.com
Đa tạ các bác

Bạn xem thử bài giải này, xem có hiểu hơn được gì không nha:


Trả lời bài tập 1

Bài 1A
Để tạo mã cho toàn bộ nhân viên trong cơ quan, người ta đã viết ra một thủ tục như sau:

    (1) Option Explicit
    (2) Sub TaoMaNV()
    (3)     Dim Rng As Range, Clls As Range
    (4)     Dim Nam As Integer, Jj As Integer, Thg As Byte
    (5)     Const Chu As String = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"
     
    (6)     For Each Clls In Range("B2:B" & [A65500].End(xlUp).Row)
    (7)         With Clls.Offset(, 4)
    (8)         Jj = Jj + 1
    (9)         Nam = Year(.Value) - 1950
    (10)         Thg = 12 * (Nam Mod 3)
    (11)         Clls.Value = "A" & Mid(Chu, 1 + Nam  3, 1) & _
                        Mid(Chu, Thg + Month(.Value), 1) & _
                        Mid(Chu, Day(.Value), 1) & Right("00" & CStr(Jj), 3)
    (12)         End With
    (13)     Next Clls
    (14) End Sub

Câu 1: Hãy diễn dịch các dòng lệnh trên ra nghĩa tiếng Việt.

(1) Tùy chọn bắt buộc phải khai báo biến trước khi dùng
(3,4,5) Phần khai báo biến và hằng
(6) Vòng For duyệt tất cả các ô ở cột B trong vùng dữ liệu (từ hàng 2 đến hàng cuối cùng có đánh STT), mỗi ô trong cột này sẽ chứa 1 mã nhân viên.
(7) Xét ô ở bên phải ô Clls 4 cột (là ô ngày sinh)
(8) Tăng biến đếm lên 1 đơn vị
(9) Gán giá trị cho biến Nam = Năm sinh - 1950
(10) Gán giá trị cho biến Thg = 12 * số dư của Nam chia cho 3
(11) Gán giá trị cho ô Clls (là mã số của nhân viên)
(13) Chuyển đến ô kế tiếp (để tạo mã cho nhân viên kế tiếp)


Câu 2: Toán tử “\” khác với toán tử “/” như thế nào?

- Toán tử "\" được dùng để tính phần nguyên của phép chia, kết quả là một số nguyên (tương ứng với hàm INT trong Excel và hàm DIV trong Pascal)
- Toán tử "/" được dùng để tính phép chia thông thường, kết quả là một số thực.

Câu 3: Hãy nêu hết những nét đặc trưng của mã nhân viên được tạo thành bằng thủ tục trên mà bạn biết.

1. Mã có độ dài bằng 7 và luôn bắt đầu bởi chữ "A".
2. Ký tự thứ 2 được mã hóa từ biến Nam (=Mid(Chu, 1 + Nam \ 3, 1))
3. Ký tự thứ 3 được mã hóa từ biến Thg và tháng sinh của nhân viên (=Mid(Chu, Thg + Month(.Value), 1))
4. Ký tự thứ 4 được mã hóa ngày sinh của nhân viên (=Mid(Chu, Day(.Value), 1)).
Vì ngày sinh chỉ nhận các giá trị từ 1 đến 31 nên ký tự thứ 4 của mã nhân viên nhận các giá trị từ 0 đến U.
5. 3 ký tự cuối là số thứ tự của nhân viên trong danh sách.
6. Tất cả mã nhân viên được tạo ra dựa vào ngày sinh và thứ tự của nhân viên đó trong CSDL. Như vậy, 2 nhân viên có cùng ngày sinh thì 4 ký tự đầu của mã nhân viên là hoàn toàn giống nhau, chỉ khác nhau ở 3 ký tự cuối do thứ tự khác nhau.
7. Thủ tục này đảm bảo tính duy nhất của mã được tạo ra, do 3 ký tự cuối của mỗi mã là duy nhất.
8. Câu lệnh Nam = Year(.Value) - 1950 cho thấy thủ tục chỉ tạo được mã cho những nhân viên có năm sinh >=1950. Điều này là chấp nhận được vì những người sinh trước đó đã "nghỉ hưu" hết rồi.

 

[nghiaphuc]

To SA_DQ: Theo em thì sau khi hết thời gian cho mỗi bài tập, bác nên đưa lên bài giải hay nhất (tối ưu, ngắn gọn, nhanh,...), đồng thời giải thích ý nghĩa của code để các thành viên khác được học hỏi (vì ý nghĩa ban đầu đưa ra của topic này là tạo một lớp học VBA cho mọi người mà).

 

[SA_DQ]

Giải bài 2, 3, và 4

Lời giải bài 02 của 1 bạn​

Option Explicit
[B]Sub SinhNhat()[/B]
    Dim ThgSau, QuySau As Byte, Opt
    Dim Cll As Range, Cuoi As Long, Sh As Worksheet, DK As Boolean
    
    Opt = MsgBox("Chon Yes      = Thang sau." & vbCr & "Chon No       = Quy sau." & vbCr & _
        "Chon Cancel = Huy lenh.", vbYesNoCancel, "Tuy chon loc du lieu")
    If Opt = vbCancel Then Exit Sub
    Set Sh = Sheets.Add                     [COLOR=RoyalBlue] 'Them Sheet moi de luu tru du lieu sau khi loc'[/COLOR]
    Sheets("CSDL").Select:                      Cuoi = 3
    ThgSau = (Month(Date) + 1) Mod 12:          QuySau = (Month(Date) - 1) \ 3 + 2
    
    If QuySau = 5 Then QuySau = 1
    Sh.[A1] = "DANH SÁCH NHÂN VIÊN SINH TRONG " & IIf(Opt = vbYes, "THÁNG " & ThgSau, "QUÝ " & QuySau)
[COLOR=RoyalBlue]    'Nhap tieu de danh sach'[/COLOR]
    Intersect([1:1], [A1].CurrentRegion).Copy Sh.[A3] [COLOR=RoyalBlue]'Sao chep tieu de cot'[/COLOR]
    For Each Cll In Range([F2], [F65536].End(xlUp)) 'Duyet cac o trong cot NgaySinh'
        DK = IIf(Opt = vbYes, Month(Cll) = ThgSau, Month(Cll) >= 3 * QuySau - 2 And Month(Cll) <= 3 * QuySau)
      [COLOR=RoyalBlue]  'Chon dieu kien tuy thuoc vao viec chon loc theo thang hay theo quy'[/COLOR]
        If DK Then[COLOR=RoyalBlue] 'Sao chep ban ghi thoa man dieu kien '[/COLOR]
            Cuoi = Cuoi + 1
            Intersect(Cll.EntireRow, Cll.CurrentRegion).Copy Sh.Cells(Cuoi, 1)
            Sh.Cells(Cuoi, 1) = Cuoi - 3 '[COLOR=RoyalBlue]Danh STT'[/COLOR]
        End If
    Next Cll
    Sh.Select
    If Cuoi > 4 Then[COLOR=RoyalBlue] 'Chi sap xep khi trong danh sach co it nhat 2 thanh vien '[/COLOR]
        [F:F].Insert Shift:=xlToRight 'Chen them cot phu de phuc vu cho sap xep du lieu'
        Set Cll = Range("F4:F" & Cuoi)
        Cll.FormulaR1C1 = "=TEXT(MONTH(RC[1]),""00"")&TEXT(DAY(RC[1]),""00"")"
        Intersect([B:IV], [A3].CurrentRegion).Sort Key1:=[F3], Order1:=xlAscending, Header:=xlYes, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
        [F:F].Delete [COLOR=RoyalBlue]'Xoa cot phu'[/COLOR]
    End If
    Cells(Cuoi + 2, 1) = "Danh sách này có " & Cuoi - 3 & " nhân viên."
    Cells(Cuoi + 3, 1) = "Sô tiên chi: " & (Cuoi - 3) * 10^5
    Sh.Name = "SNhat"
[B]End Sub[/B]

Chưa nhận được bài giải nào bằng cách sử dụng AdvancedFilter!

Chắc kiểu này, chúng ta không nên đưa ra thời hạn; Xin mời các bạn gởi bài giải theo như thông báo. Trân trọng cảm ơn!



Trả lời Bài tập 3

Bài 3:
(1) Tạo Macro DSNoiSinh: Sau khi thu Macro và chỉnh sửa, ta được Macro như sau:
Sub DSNoiSinh()
Range([G1], [G65536].End(xlUp)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=[K1], Unique:=True
End Sub
(2, 3, 4, 5) Chỉnh sửa Macro ThemCot để thực hiện sửa lỗi trong toàn bộ CSDL, gọi thủ tục DSNoiSinh. Cuối cùng là xóa cột NoiSinh cũ (cột H):
Ý tưởng: Khai báo thêm một biến Cuoi kiểu Long để lưu hàng cuối trong CSDL và một biến MyRange kiểu Range để lưu vùng dữ liệu của cột NoiSinh. Ngoài ra, vì Sub DSNoiSinh chỉ có một câu lệnh nên thay vì dùng câu lệnh DSNoiSinh để gọi Sub này, ta có thể thêm câu lệnh duy nhất của Sub này vào trong Sub ThemCot.
Dưới đây là Sub ThemCot đã chỉnh sửa:

    Sub ThemCot()
        Dim Cuoi As Long, MyRange As Range
        Cuoi = [A65536].End(xlUp).Row
        [G:G].Insert Shift:=xlToRight
        [H1].Copy [G1]
        Set MyRange = Range("G2:G" & Cuoi)
        MyRange.FormulaR1C1 = "=TRIM(RC[1])"
        MyRange.Copy
        MyRange.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        'DSNoiSinh 'hoac them cau lenh duoi day
        Range("G1:G" & Cuoi).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=[K1], Unique:=True
        [H:H].Delete
    End Sub

Rất cảm ơn các bạn đã gởi đáp án cho chúng tôi.

Lời giải bài tập 4 của một thành viên tích cực:​

Thông tin ban đầu:

- Sheet CSDL có CodeName là Sheet3, trong đó vùng dữ liệu hiện có là vùng bắt đầu bởi ô A1, số cột và số hàng không quan trọng vì có thể dùng thuộc tính CurrentRegion, vùng chứa mã đơn vị là vùng AD2:AD11.

- Trên Sheet mới, vùng chứa điều kiện lọc là vùng B1:B2, trong đó, B1="Dvi" (là tiêu đề của cột H trong CSDL), B2 chứa 1 Validation list là mã đơn vị.

- Trong dự định, vùng dữ liệu sau khi lọc được sao chép tới vùng tính từ ô C1 của Sheet mới.
Với các thông tin ban đầu như trên, ta có thể tiến hành như sau:


1. Tạo Validation list ở ô B2:

- Chọn vùng AD2:AD11 của Sheet CSDL, đặt tên cho vùng là MaDV.
- Chọn ô B2 ở Sheet mới, chọn Data\Validation..., chọn Allow là List và Source là =MaDV


2. Viết Macro để khi chọn mã đơn vị thì hiện thông tin các nhân viên thuộc đơn vị đó:

Em thấy việc này tốt nhất nên gắn với sự kiện Worksheet_Change (đặt trong Sheet mới). Code như sau:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address <> "$B$2" Then Exit Sub 'Chi thuc hien thu tuc khi co su thay doi o B2'
    [C:IV].Clear 'Xoa thong tin hien co'
    Sheet3.[A1].CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=[B1:B2], CopyToRange:=[C1]
End Sub

 

[SA_DQ]

Bài tập thứ 5 (Các bài tập về tìm kiếm thông tin từ CSDL)

Bài 5 Tìm kiếm thông tin​

Thực ra một CSDL sẽ luôn thay đổi thông tin. Ví dụ được xếp lương mới của 1 nhóm người theo định kỳ hay đột xuất, thuyên chuyển từ đơn vị này sang đơn vị khác, thay đổi cương vị công tác,. . .
Để sửa đổi bổ sung hay cập nhật hồ sơ của nhân viên, trước tiên ta fải tìm kiếm nhân viên đó. Để tránh nhầm lẫn do trùng họ tên, ta fải tìm theo mã duy nhất của người ấy.
Cái khó là không thể nào nhớ hết mã của hơn 700 CNV
Vậy còn cách có thể là duy nhất: Tìm theo đơn vị & trong đơn vị ta tìm theo tên

Và đây là nội dung câu hỏi 5:
Trên trang tính mới, như dưới đây:

|A|B|C|D|
|Tên ĐVị|Mã ĐV|Tên|Mã NV|
|||||
|Họ||||
|Giới tính||.||
|Ngày sinh||.||
|Nơi sinh||.||
|Chức vụ||.||
|. . .||.||

Nhiệm vụ đề ra là:

(1) Tại [B2] ta lập danh sách, mà khi chọn 1 trong đó, sẽ hiện tên đơn vị tại [A2]
Hơn nữa, tại [C3] sẽ hiện hết tên NV của đơn vị đó ở dạng 1 danh sách để có thể chọn 1 người từ danh sách đó (Tham khảo bài tập 4)

(2) Khi chọn 1 người từ danh sách đó, tất cả thông tin của NV đó hiện lên ở [D2] & các ô tương ứng của cột ‘B’.
Hơn nữa, tại [D3] hiện lên số người trùng tên của đơn vị đó.

Bài 5B Tìm dữ liệu của một nhân viên khi đã biết mã của nhân viên đó​

Như bài số 5 đã giải, chúng ta thấy macro đã liệt kê tất cả các mã nhân viên có trùng tên trong đơn vị đó.
Hơn nữa, khi có trùng tên, thì dữ liệu trên cột ‘B’ kể từ [B3] là dữ liệu của người có mã được liệt kê cuối cùng trong cột ‘D’

(Cụ thể ta chọn ‘H’ tại [B2] & Tại [C3] ta chọn người có tên Nguyên, thì cột ‘D’ hiện lên 7 mã của 7 nhân viên)

Vậy vấn đề đặt ra là:
Nếu ta chọn 1 ô trong các mã nằm trên mã cuối vừa nêu & cho chạy macro có tên TimTheoMa để hiện dữ liệu tương ứng của mã đó trong trang tính
Vậy bạn hãy nêu đầy đủ các câu lệnh của macro này giúp nha.

Bài 5C Chỉnh sửa & cập nhật dữ liệu​

Mục đích của bài 5 nói chung là tìm kiếm thông tin của nhóm người hay 1 người cụ thể trong cơ quan & chỉnh sửa & cập nhật thông tin.
Tất nhiên 1 số thông tin sẽ fải hạn chế tối đa việc chỉnh sửa, như ngày sinh, mã của nhân viên; Họ & tên
Còn các thông tin về đơn vị hay bộ fận công tác, ngày thay đổi lương, chức vụ hay trình độ học vấn,. . . luôn thường thay đổi.

Nhiệm vụ bài này sẽ là cập nhật sao cho các đơn vị đều có người phụ trách
Như phòng ban thì có trưởng fó fòng, fân xưởng có Quản đốc, . . . Tại bộ fận Giám đốc ta cũng tự bổ nhiệm cho 1 Giám đốc & 2 fó Giám đốc
Sau đó ta xây dựng macro để các dữ liệu này (Nhập vô [B7] của trang tính “Nhap” đảm bảo cập nhật cho thành viên có mã NV đó bên trang tính “CSDL”.

Điều lưu ý là ta nhập mã chức vụ vô trường chức vụ đó nha. Xem thêm các cột AG & AH của trang “CSDL”. Nói rõ hơn là tại cột I (Cvu) Ta fải nhập ký tự A thay cho chức danh Giám đốc; K thay cho chức danh tổ trưởng. Để tránh sai sót ta tham khảo thêm cách dùng Validation để nhập liệu)


Chú ý:

Bài làm xong, gởi theo dạng tin nhắn tới LeVanDuyet & Sa_DQ;
Ngoài ra bạn có thể gởi những vấn đề bạn quan tâm liên quan đến bài này để được cộng đồng GPE giải đáp

Rất cảm ơn các bạn đã quan tâm.

 

[ChanhTQ@]

Một cách để giải bài tập thứ 5, dùng AdvancedFilter & phương thức tìm kiếm

Để giải bài này, chúng ta cần đặc tên cho hai vùng trong trang tính CSDL

(1) Ta gán tên MaDV cho vùng =CSDL!$AD$2:$AD$14
(2) Gán tên DSDV cho vùng =CSDL!$AN$2:$AN$400

Để dùng fương thức AdvancedFilter, ta copy các ô từ [B1:I1] đến ô bắt đầu từ [AL1]
Sau đó Cắt ô [AR1] đem đến [AJ1] & xóa cột AR này đi (bằng cách chọn nó & nhấn fím Delete trên thanh menu Edit);
Tiếp tục ta nhập công thức =Nhap!B2 vô {AJ2] để dùng 2 ô đầu tiên của cột này làm Criteria cho fương thức lọc
(Nhap là tên trang tính mới tinh mà đề bài #5 iêu cầu ta tạo ra)

Tại trang tính nhập, ta tạo danh sách mã đơn vị tại ô [B2] & tại [C3] là danh sách tên của DSDV nói trên (Bằng fương thức validation)

Sau đó ta fải chuột vô SheetName có tên "Nhap" & chọn dòng cuối để hiện CS VBA của trang tính này & chép toàn bộ nôi dung các macro sau vô đó

Option Explicit
Dim Rng As Range, Sh As Worksheet
Private Sub Worksheet_Change(ByVal Target As Range)
 If Not Intersect(Target, [B2]) Is Nothing Then
   LapDSDonVi Target
 ElseIf Not Intersect(Target, [C3]) Is Nothing Then
   Dim MyAdd As String, sRng As Range
   Dim rW As Byte, jJ As Byte
   Set Sh = Sheets("CSDL"):               [D2].Resize(40).ClearContents
   Set Rng = Sh.Range("DSDV")
   Set sRng = Rng.Find(Target.Value, , xlFormulas, xlWhole)
   If Not sRng Is Nothing Then
      MyAdd = sRng.Address
      Do
         jJ = jJ + 1:            [c2].Value = jJ
         [B3].Value = sRng.Offset(, -1).Value
         For rW = 4 To 9
            [D1].Offset(jJ).Value = sRng.Offset(, -2).Value
            Cells(rW, "B").Value = sRng.Offset(, rW - 3).Value
         Next rW
         Set sRng = Rng.FindNext(sRng)
      Loop While Not sRng Is Nothing And sRng.Address <> MyAdd
   End If
   Set Sh = Nothing
 End If
End Sub

[B]Sub LapDSDonVi(Targ As Range)[/B]
    
 Set Sh = Sheets("CSDL")
 Set Rng = Sh.Range("MaDV")
 [A2].Value = Rng.Find(Targ.Value, , xlFormulas, xlWhole).Offset(, 1).Value
 Sh.Columns("B:I").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sh.[AJ1].Resize(2), _
   CopyToRange:=Sh.[AL1].Resize(, 7), Unique:=False
[B]End Sub[/B]

Sau đó ta thử chọn lần lượt các ô tại B2=> H & vài lần thay đổ tại [C3] cho đến khi chán thì nghỉ.

Chúc thành công!

 

[SA_DQ]

Hàm tự tạo tìm mã nhân viên lớn nhất trong cơ quan.

Tại Cơ quan nọ có danh sách nhân sự khoảng 700 người. Hơn nữa cột B đang chứa mã duy nhất của từng người.
Mã này được lập theo quy luật, có độ dài là 7 ký tự, theo kiểu CXXX***
Ở đây ký hiệu C đại diện cho 1 ký tự; XXX lả 3 ký số & *** có thể là ký tự hay ký số.

Như bảng dưới đây:

|A|B|C|
1 | STT | Ma | Ho. . . ||
2 |1|B456LN0|Lê Văn|
3 |2|B987C0C|Hồ Thị|
|.|.|...|
709 |708|B321PO0|Đỗ Vỹ|

Các bạn hãy viết giúp tôi viết hàm tự tạo để tìm ai đó đang có trị XXX đang là lớn nhất trong CSDL này.

Rất cảm ơn các bạn đã quan tâm.

 

[nghiaphuc]

Trao đổi về bài tập 5B

To SA_DQ: Theo em, với bài tập này, không nhất thiết phải lập macro TimTheoMa, chúng ta có thể sử dụng sự kiện Worksheet_SelectionChange để khi ta chọn một ô trong vùng chứa mã, thông tin của nhân viên đó sẽ được hiển thị ngay trên sheet.
Giả sử vùng chứa thông tin mã nằm trên cột D và bắt đầu từ [D4], khi đó, ta có thể sử dụng code như sau:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim Cll As Range
    If Intersect(Target, Range([D4], [D65536].End(xlUp))) Is Nothing Then Exit Sub
    [D2] = Target 'Ghi Ma nhan vien dang chon vao [D2]'
    Set Cll = Sheet3.Cells.Find(Target, , xlValues, xlWhole) 'Tim ma dang chon trong CSDL'
    Cll.Offset(, 1).Copy [B3] 'Ho'
    Cll.Offset(, 3).Copy [B4] 'Gioi tinh'
    Cll.Offset(, 4).Copy [B5] 'Ngay sinh'
    Sheet3.[AA:AA].Find(Cll.Offset(, 5), Sheet3.[AA1], xlValues, xlWhole).Offset(, 1).Copy [B6] 'Noi sinh'
End Sub

 

[SA_DQ]

To NghiaPhuc

Trong macro của bạn xin được lưu ý hai chổ; Chúng liên quan đến 2 câu lệnh tìm kiếm;

Trong câu lệnh áp dụng fương thức tìm kiếm thứ nhất, ở đây bạn nên giới hạn vùng tìm kiếm trên cột 'B' mà thôi; Thậm chí chỉ là dòng đầu đến dòng cuối có chứa dữ liệu trong cột mà thôi.

Trong câu lệnh thức hai chúng ta nên khẳng định với nhau là không điền tên tỉnh/thành vô [B6] mà điền vô đây là mã tỉnh thành của nó;
Cũng giống như mã đơn vị/bộ fận ta cũng chỉ điền mã;
Điều này lợi rất lớn về tốc độ xử lý dữ liệu trong 1 CSDL loại vừa & lớn.

(Thử để bạn thấy bằng fép tính thế này nha:
Tên tỉnh/thành nước ta thường có 2,5 từ & mỗi từ có chiều dài trung bình là 6 -7 ký tự; Ta đem chúng nhần với 770 nhân viên hiện trong CSDL sẽ thấy chúng chiếm chổ hơn rất nhiều làn so với cách mà chúng ta đang thỏa thuận & thực hện từ các bài tập đầu. ( Với 770 chữ cái treong cột mã tỉnh & 1 bảng tra khoảng 120*2.5 *7 từ tối đa.))

Vì là những bài tập sẽ áp dụng được trong thực tế. Nên chúng ta fải suy nghĩ rốt ráo vấn đề này.

Bạn hãy tham khảo macro TimTheoMaNV sau đây:

Option Explicit
Sub TimTheoMaNV()
 Dim Rng As Range, Sh As Worksheet, jJ As Byte
 
 Set Sh = Sheets("CSDL")
 Set Rng = Sh.Range(Sh.[B1], Sh.[b65500].End(xlUp))
  With Rng.Find(Selection.Value, , xlFormulas, xlWhole)
   [B3].Value = .Offset(, 1).Value
   [b7].Value = .Offset(, 7).Value
   [b8].Value = .Offset(, 8).Value
   For jJ = 4 To 6
      Cells(jJ, "B").Value = .Offset(, jJ - 1).Value
   Next jJ
 End With
 [D2].Value = Selection.Value:      [D3].Resize(40).ClearContents
End Sub

Mong bạn đạt được nhiều thành công!

 

[SA_DQ]

Bài 6B: Nhập hồ sơ cho nhân viên mới.

(Nhân sự kiện hơn 1.000 lượt người đã ghé thăm topic của chúng ta)

Nội dung bài tập là:

Tại trang tính 'Nhap' (Xin xem trong file đính kèm) ta có vùng từ G4:H12 để điền thông tin của nhân viên mới về cơ quan;
Các bạn giúp tôi, chúng ta cùng tạo ra macro để nhập dữ liệu từ H4:H12 vô dòng cuối của CSDL.

Chú ý: Tại H3 là mã gơi ý để giúp ta 1 fần khi tạo ra mã tiếp theo của nhân viên này.
(Đến đây các bạn nên tạo ra hàm người dùng để nó lấy số liệu đã nhập tại [H8] kết hợp với [H3] tạo cho ta mã NV 1 cách tự động & chính xác (vô ô [H4]))
Ô H5:H6 & [H8], [h11] nhập bằng tay trực tiếp;
Hai ô [H9:H10] nên nhờ danh sách từ Validation tại ô fải liền kề để nhập cho chính xác.

Chúc các bạn thành công & rất mong thông tin từ các bạn gần xa!

Tiện đây xin fép được cảm ơn sự nhiệt tình & tích cực của bạn NghiaPhuc qua các bài tập từ đầu đến nay.

 

[SA_DQ]

Bài 7 & vấn đề nghỉ việc của nhân viên.

Bài 07 Chuyển dữ liệu nhân viên đã đi khỏi cơ quan​

Cơ quan nào rồi cũng có lúc tăng, lúc giảm người;
Tại bài 6 ta đã xét đến trường hợp tăng người – Nhập mới;
Bài này ta xét đến khía cạnh giảm người.
Để thực hiện trường hợp giảm này, ta tận dụng vùng trước cột G của trang tính ‘Nhap’

(1) Về CSDL
Xin tạm dừng để nói thêm một điều rằng, trong thực tiển, để quản lý hồ sơ nhân viên, ta không chỉ quản các trường [STT], [MaNV], [Ho], [Ten],. . . ,[NgayCQ] không thôi. Mà thực ra là cần quản không dưới 20 trường khác nữa, như [HSL] (Hệ số lương CB), Trình độ học vấn [HVan] (Trình độ học vấn khác xa trình độ văn hóa à nha!), [PCap] để quản về fụ cấp theo lương, Để quản vế thuế TNCN, ta còn mở rọng ra quản những thân nhân, người nuôi dưỡng liên quan, . . . . . & còn rất nhiều trường khác nữa.
Nhưng dù sao trong dạng bài tập ta chỉ xét các trường tiêu biểu như trong file cuối cùng đính kèm. Là tạm đủ.

(2) Về giảm trong nhân sự
Ta sẽ có các loại giảm sau: Hưu trí, Thôi việc, Buộc thôi việc, Điều động (do quyết định điều chuyển của cấp chủ quản), thuyên chuyển (do nhân viên yêu cầu), chết do bệnh hay tai nạn, & cuối cùng là khác (Nghỉ chờ chế độ, NVQS, biệt fái, tội tù,. . . )

Để giảm người nào đó thì ta tìm đến dòng dữ liệu của người đó & đem dòng đó đến trang tính khác, mà ta gọi là ‘NViec’
Thực ra cấu trục cùa trang tính này giống y xì trang CSDL; Nó có thêm hơn 2 trường, đó là mã loại nghỉ (mà ta vừa liệt kê) & [NgayNghi]

Cách tìm mã số của 1 người thì ta xem lại các bài tập #5

(3) Cách làm cụ thể:
a) Tại cột AJ của trang ‘Nhap’ Ta nhập các mã nghỉ việc & cột bên fải liền kề, ta nhập tên gọi của dạng nghỉ việc đó (tương ứng theo dòng).
Ta có thể gán tên cho vùng này để dễ bề sử dụng.
b) Tại dòng 40 của trang tính này, ta nhập các tiêu đề: [MaNV], [Ho], [Ten], [MaNghi], [LoaiNghi]
[A41] là ô ta fải tự tay nhập mã người nghỉ vô.
Tại 2 ô liền kề bên fải ta lập công thức:
=IF(A41="","",VLOOKUP(A41,CSDL!B2:C999,2,0)) &
=IF(A41="","",VLOOKUP(A41,CSDL!B2:C999,3,0))
Tại [E41] ta thiết lập Validation (Xem lại các bài #5); Tại ô bên trái liền kề, ta lập công thức =OFFSET(AJ1,MATCH(E41,ChDo,0),0)
(Ở đây ChDo là vùng đã gán tên mà ta vừa nói tới.
Hơn nữa, các bạn có thể cải tiến các công thức này cho thanh thoát & dễ dụng hơn đối với các bạn)
c) Nhiệm vụ đề ra là viết 1 macro & nút lệnh liên quan đến nó nằm tại vùng này; Mà khi ta nhấn vô nó, Nó sẽ làm đúng cho ta các việc sau:
(*) Tìm theo mã NV đó trong trang CSDL;
(*) Chép sang trang tính lưu nghỉ việc,
Thêm vô trường [MaNghi] của trang NViec ký hiệu có tại [D41], & trường [NgayNghi]
(*) Xóa record của nhân viên đó.


Chú ý:

Bài làm xong, gởi theo dạng tin nhắn tới LeVanDuyet & Sa_DQ;
Ngoài ra bạn có thể gởi những vấn đề bạn quan tâm liên quan đến bài này để được cộng đồng GPE giải đáp

Rất cảm ơn các bạn đã quan tâm.

 

[ChanhTQ@]

Xin giới thiệu các bạn 1 hàm tự tạo hỗ trợ ta lập mã cho nhân viên mới vô cơ quan

Chú ý: Tại H3 là mã gơi ý để giúp ta 1 fần khi tạo ra mã tiếp theo của nhân viên này.
(Đến đây các bạn nên tạo ra hàm người dùng để nó lấy số liệu đã nhập tại [H8] kết hợp với [H3] tạo cho ta mã NV 1 cách tự động & chính xác (vô ô [H4]))

Option Explicit
[B]Function MaNVMoi(Targ As Range) As String[/B]
    Dim Nam As Integer, Thg As Byte, dMa As String
    Const Chu As String = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"

    With Targ
      Nam = Year(.Value) - 1950
      Thg = 12 * (Nam Mod 3) + Month(.Value)
      dMa = Left(MaxSTT, 1)
      If Mid(MaxSTT, 2, 3) = "999" Then
         dMa = Chr(Asc(dMa) + 1) & "001"
      Else
         dMa = dMa & CStr(CInt(Mid(MaxSTT, 2, 3)) + 1)
      End If
      MaNVMoi = dMa & Mid(Chu, 1 + Nam \ 3, 1) & Mid(Chu, Thg, 1) & _
         Mid(Chu, Day(.Value), 1)
    End With
[B]End Function[/B]

 

[SA_DQ]

Bài tập thứ 8 (Liên quan đến bảng tính lương)

Bài gồm 2 phần:

(1) Tại cơ quan (CQ) này, người ta trả lương theo hình thức khoán sản fẩm tập thể. Có nghĩa là cấp trên đã đưa ra đơn giá cho các sản fẩm của 3 fân xưởng chính của CQ. Tại CQ lại chia ra ba cách tính lương cho 3 nhóm lao động. Ba nhóm đó gồm Quản lý, Phụ trợ & trực tiếp.
Trực tiếp là Toàn thể Lãnh đạo & nhân viên của 3 fân xưỡng, hưởng lương theo kết quả cuối cùng của mình trong tháng theo đơn giá đề ra cho fân xưởng mình.
Phụ trợ . . . (Bàn sau)
Quản lý Gồm toàn thể những người có chức vụ từ fó ban trở lện tại các fòng ban (giám đốc)
Toàn thể họ được hưởng 10% tổng số lương sản fẩm trong CQ.

Vấn đề nếu ra của bài tập này là hãy chép toàn bộ những người có chức danh từ fó ban trở lên vô bảng lương chức danh. (Hãy xem thêm trong file)
Biết rằng việc chép này có thể dùng nhiều fương thức khác nhau.

Như dưới đây, người ta dùng fương thức tìm kiếm (FIND())

Và đây là nhiệm vụ phần I:

Dịch nghĩa & giải thích các câu lệnh sau:

Option Explicit
Sub LapBangLuongChucDanh()
 Dim Sh As Worksheet, Rng As Range, sRng As Range, Clls As Range, Sh0 As Worksheet
 Dim MyAdd As String, GPE As String, rW As Long
 
 Sheets("Nhap").Select:                   Set Sh = Sheets("Luong")
 Sh.Range("B6:E26").ClearContents:        Set Sh0 = Sheets("CSDL")
 Set Rng = Sh0.Range("I1:I" & Sh0.[B65500].End(xlUp).Row) 'Cot Chuc Vu'
 For Each Clls In Range("AG2:AG6") 'Dòng 6: Truong Ban'
   Set sRng = Rng.Find(Clls.Value, , xlFormulas, xlWhole)
   If Not sRng Is Nothing Then
      MyAdd = sRng.Address
      Do
         With Sh.[B65500].End(xlUp).Offset(1)
            rW = sRng.Row
            If Clls.Value > "B" Then
               With Application.WorksheetFunction
                  GPE = .VLookup(sRng.Offset(, -1), Range("MaDV").Resize(, 2), 2, 0)
               End With
            End If
            .Offset(, 1).Value = Clls.Offset(, 1).Value & Mid(GPE, 6)
            .Value = Sh0.Cells(rW, "C").Value & " " & Sh0.Cells(rW, "D").Value
            .Offset(, 2).Value = Clls.Offset(, 2).Value
            .Offset(, 3).Value = sRng.Offset(, 2).Value
         End With
         Set sRng = Rng.FindNext(sRng)
      Loop While Not sRng Is Nothing And sRng.Address <> MyAdd
   End If
 Next Clls
 Sh.Select
End Sub

Chú ý: Xem thêm trong file kèm theo

(2) Nhiệm vụ phần II: Hãy dùng fương thức AdvancedFilter làm việc này thay cho cách trên

Rất mong các bạn tiếp tục ửng hộ & tham gia topic này!

 

[SA_DQ]

Bài 8B: Tổng hợp các loại công & tính lương cho bộ fận quản lý.

Trong file đính kèm có đưa ra hàm tự tạo để tính các loại công trong tháng của 1 đơn vị sản xuất cônghiệp để chúng ta tham khảo.

Nội dung yêu cầu của bài tập này là:

(1) Tính các loại công trong tháng của 6 người thuộc bộ fận quản lý của xí nghiệp theo hàm tự tạo có trong file;

(2) Tính ra tiền lương cho các đương sự này trong tháng.
Chú ý:
- Đơn giá lương sản fẩm & sản lượng tham khảo ở trang tính bảng chấm công của file bài trước.
- Mức lương tối thểu để tính lương thời gian & lương chế độ tạm lấy là: 650.000 đồng;
Các dạng nghỉ chế độ ôm đau thai sản, tai nạn LĐ đều tính được hưởng 70% lương thời gian.
- Ngày công danh định trong tháng để hưởng lương là 24 ngày.
Do đặc thù của khối này làm hết việc chứ không hết giờ, nên làm thêm giờ không được tính như 2 khối còn lại.
Khối này khi fải làm ngày lễ sẽ được bồi dưỡng ngoài quỹ lương, như là chi fí sản xuất.

Cảm ơn các bạn đã, đang & sẽ quan tâm thường xuyên đến topic này!

 

[SA_DQ]

Bài 8C: Tổng hợp bảng lương của các tháng

Giả dụ chúng ta đã có bảng tính lương của các tháng (tính từ đầu năm)

Nhiệm vụ đề ra là viết macro để tổng hợp số liệu vô trang tính "THop" như trong file đính kèm


( Các bạn có thể tham khảo tại:đây)