Tạo chương trình quản lí cán bộ bằng Excel 2003

Hôm nay ngày 03 tháng 8 năm 2011
Để quản lí cán bộ trong các cơ quan bạn có thể dùng Excel 2003 làm việc này, bạn chỉ cần nhập họ và tên sau đó ấn Enter là ảnh, thông tin của người đó sẽ hiện ra, sau đây là cách thực hiện chương trình.

Bước 1: Tạo thư mục chứa ảnh và qui ước đặt tên ảnh.
+Tại ổ C bạn tạo thư mục Quan li can bo chẳng hạn.
+Trong thư mục Quan li can bo bạn tạo 1 thư mục con có tên là Anh.
+Trong thư mục Anh chứa các tập tin ảnh của từng người, tên tập tin được đặt theo mã ảnh là 1.jpg, 2.jpg, 3.jpg cho đến hết.

Bước 2: Tạo bảng danh sách và đặt tên cho bảng.
+Khởi động Excel, tại Sheet2 bạn tạo một bảng gồm những cột cơ bản sau:

Bảng này chỉ mang tính tượng trưng, bạn cần thêm nhiều thông tin khác nữa
+Cột E Ngày sinh phải định dạng là Text
+Hàm tính tuổi ô F5 như sau: =IF(E5="",0,YEAR(NOW())-RIGHT(E5,4)) sao chép hàm này xuống các ô khác trong cột F, hàm này cho phép tính tuổi khi nhập ngày tháng năm sinh tùy tiện như hình trên.
+Cột D là mã ảnh tại ô D5 gõ =B5 sau đó sao chép xuống các ô khác trong cột B, mã ảnh chính là tên File ảnh của bạn, thí dụ: ông Dương Mạnh Hùng mã ảnh là 2 thì tên tập tin ảnh của ông Dương Mạnh Hùng sẽ là 2.jpg
+Cột B là số thứ tự tại ô B5 sẽ lập hàm =IF(C5="","",COUNTA($C$5:C5)) sao chép công thức xuống các ô khác trong cột B, hàm này sẽ tự đánh số thứ tự khi nhập Họ và tên ở cột C bắt đầu từ ô C5.
+Đặt tên cho bảng: Bạn đặt con trỏ vào ô C5 nhấn chuột kéo sang các cột ở bên phải sau đó kéo xuống khoảng 500 hàng nếu danh sách cán bộ là 500 người.
-Nháy vào Insert \ Name \ Define gõ chữ A bấm OK, như vậy bảng này có tên là A.
-Bôi đen từ ô C5 đến C500 nháy vào Insert \ Name \ Define gõ List bấm OK.
+Tại cột A thiết lập hàm đếm số người nhập vào như sau:
-Công thức tính tổng tại ô A6 bạn viết =COUNTA(C5:C500)
-Công thức tính số người là Nam giới =COUNTIF(H5:H500,"Nam")
-Công thức tính số người là Nữ giới =COUNTIF(H5:H500,"Nữ")
-Công thức tính số người là dân tộc kinh =COUNTIF(I5:I500,"Kinh")

Bước 3: Tạo bảng hiển thị thông tin cá nhân khi nhập họ và tên.
+Tại Sheet bạn đổi tên là Tra tim (Không có dấu tiếng Việt).
+Bôi đen khoảng 100 cột, nháy vào Format \ Column \ Wictth gõ số 3 bấm OK, mục đích để làm cột nhỏ lại.
+Nháy vào Tools \ Options \ View đánh dấu vào Page break bấm OK, mục đích để hiện đường phân trang, bạn chỉ được thiết kế trong đường giới hạn của trang.
+Đóng khung khu vực hiện ảnh của cán bộ thí dụ tọa độ là B5, E11
+Thiết lập các ô chứa thông tin như: Họ và tên, Ngày sinh, Dân tộc v.v......
+Tại trang bên phải ta thiết lập nơi nhập Họ và tên, trong thí dụ này là ô AB19 bạn nháy chuột vào ô AB19 nháy vào Data \ Validation chọn nhãn Settings, trong mục Allow chọn List, tại mục Source gõ =List bấm OK.

+Thiết lập hàm để hiện thông tin cá nhân

+Tại ô J5 nhập hàm
=IF(ISNA(VLOOKUP($AB$19,A,1,0)),"Không có ai tên là:"&AB19,VLOOKUP($AB$19,A,1,0))
+Tại ô J6 nhập hàm
=IF(ISNA(VLOOKUP($AB$19,A,3,0)),"Không có ai tên là:"&AB20,VLOOKUP($AB$19,A,3,0))
+Tại ô C8 là nơi hiện mã ảnh nhập hàm (ô hiện mã ảnh bắt buộc phải có)
=IF(ISNA(VLOOKUP($AB$19,A,2,0)),"Không có ai tên là:"&AB19,VLOOKUP($AB$19,A,2,0))
+Tại ô J7 nhập hàm
=IF(ISNA(VLOOKUP($AB$19,A,5,0)),"Không có ai tên là:"&AB21,VLOOKUP($AB$19,A,5,0))
+Tại ô T5 nhập hàm
=IF(ISNA(VLOOKUP($AB$19,A,6,0)),"Không có ai tên là:"&AB19,VLOOKUP($AB$19,A,6,0))
+Tại ô T6 nhập hàm
=IF(ISNA(VLOOKUP($AB$19,A,4,0)),"Không có ai tên là:"&AB20,VLOOKUP($AB$19,A,4,0))
+Tại ô T7 nhập hàm
=IF(ISNA(VLOOKUP($AB$19,A,7,0)),"Không có ai tên là:"&AB21,VLOOKUP($AB$19,A,7,0))



+Thiết lập hàm hiện ảnh
-Nháy vào Tools \ Macro \ Visual Basic Editor.
-Nháy đúp vào mục Tra tim ở khung bên trái và nhập đoạn mã dưới đây vào
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$AB$19" Then
Dim ExcelApp, p As Object
Dim pathApp, path1 As String
pathApp = ThisWorkbook.Path & "\Can bo.xls"
Set ExcelApp = GetObject(pathApp)
Dim Wkb As New Excel.Workbook
Dim targetCell As Range, t As Double, l As Double, w As Double, h As Double, d As Double, r As Double
On Error Resume Next
Set p = ExcelApp.Sheets("Tra tim").Pictures(1)
p.Delete
Set p = ExcelApp.Sheets("Tra tim").Pictures(2)
p.Delete
path1 = Trim(ExcelApp.Sheets("Tra tim").Range("C8"))
path1 = ExcelApp.Path & "\Anh\\" & path1 & ".jpg"
Set p = ExcelApp.Sheets("Tra tim").Pictures.Insert(path1) 'chen anh
Set targetCell = ExcelApp.Sheets("Tra tim").Range("B5")
With targetCell
t = .Top
l = .Left
End With
Set targetCell = Nothing
Set targetCell = ExcelApp.Sheets("Tra tim").Range("E11")
With targetCell
d = .Top + .Height
r = .Left + .Width
End With
With p
.Top = t
.Left = l
.Width = r - l
.Height = d - t
End With
End If
End Sub

+Nháy vào File chọn Save để ghi lại.
Giải thích: "$AB$19 là tọa độ nhập Họ và tên cán bộ cần tra tìm.
+Can bo.xls là tên tập tin của Excel.
+C8 là ô hiện mã ảnh ô này tôi để trong khung hiện ảnh
+B5, E11 là tọa độ hiện ảnh trong khung.
+Tra tim là tên của Sheet1 đã được đổi.
Bạn có thể xem các thủ thuật Excel tại đây

Posted in: Excel