Bạn đã học qua cách dùng các hàm và các kỹ thuật phức tạp trong excel, Hôm nay tôi hướng dẫn các bạn cách làm một ứng dụng nho nhỏ nhưng rất hữu ích, đặt biệt nó rất cần thiết đối với các bạn kế toán thường phải làm tem nhãn để dán lên tài sản cố định, hay các bạn nhân viên hành chính nhân sự phải in phiếu lương, thư mời...
Ứng dụng tôi sắp giới thiệu dưới đây rất giống với chức năng Mail Mergetrong Microsoft Words. Ứng dụng này được thiết lập dựa trên các hàm mà chúng ta đã học.
Ứng dụng tôi sắp giới thiệu dưới đây rất giống với chức năng Mail Mergetrong Microsoft Words. Ứng dụng này được thiết lập dựa trên các hàm mà chúng ta đã học.
Ví dụ tôi có bản danh sách nhân viên nằm ở Sheet2 như sau:
| No | P.ban_No | Pban&No | MaNV | HoVaTen | Phongban |
| 1 | 1 | =B2&F2 | NV_010 | Nguyễn Hương Trà | Hành Chính |
| 2 | 2 | =B3&F3 | NV_011 | Ngô Thu Hương | Hành Chính |
| 3 | 3 | =B4&F4 | NV_012 | Đồng Minh Tiến | Hành Chính |
| 4 | 1 | =B5&F5 | NV_001 | Nguyễn Phú Quý | Kế Toán |
| 5 | 2 | =B5&F5 | NV_002 | Phạm Cao Lương | Kế Toán |
| 6 | 1 | =B6&F6 | NV_007 | Đinh Văn Tuấn | Kinh Doanh |
| 7 | 2 | =B7&F7 | NV_008 | Thái Phát Đạt | Kinh Doanh |
| 8 | 3 | =B8&F8 | NV_009 | Lưu Hương Giang | Kinh Doanh |
| 9 | 1 | =B9&F9 | NV_005 | Trần Dịu Kỳ | Sản Xuất |
| 10 | 2 | =B10&F10 | NV_006 | Mai Đức Hiền | Sản Xuất |
| 11 | 3 | =B11&F11 | NV_013 | Tô Hiểu Minh | Sản Xuất |
| 12 | 1 | =B12&F12 | NV_003 | Trần Thanh Tùng | Vật Tư |
| 13 | 2 | =B13&F13 | NV_004 | Phan Nghĩa Dũng | Vật Tư |
| 14 | 3 | =B14&F14 | NV_014 | Nguyễn Văn Thương | Vật Tư |
Và trên phiếu lương tôi cần xuất 3 thông tin xuất hiện đó là Mã Nhân Viên, Họ tên nhân viên và Phòng ban.
Theo danh sách bên trên thì mỗi nhân viên ứng với 1 con số ở bên cột No. Như vậy Cách đơn giản nhất là dùng Hàm Vlookup để gọi thông tin của từng nhân viên dựa trên số thứ tự của nó tương ứng. do đó tôi thiết kế một cái nhãn ở một trang tính khác như sau:
A B
1 Số thứ Tự
2
3 Mã Nhân Viên =vlookup[B1,sheet2!A:D,4,0]
4 Họ Và Tên =vlookup[B1,sheet2!A:E,5,0]
5 Phòng Ban =vlookup[B1,sheet2!A:F,6,0]
Mỗi khi bạn nhập các số từ 1 cho đến số cuối cùng trong bản danh sách trên thì mọi thông tin của nhân viên đó được gọi ra để bạn tiến hành in.
Và lưu ý là số thứ tự trong cột A không được trùng nhau.
Tuy nhiên cách làm như trên thì mỗi lần in chỉ hiện ra thông tin của một nhân viên trên một trang giấy như thế thì mất thời gian và tốn kém khi danh sách nhân viên, hay tài sản dài hàng trăm, hàng ngàn dòng.
Lúc này đòi hỏi ta thiết kết lại trang in sao cho mối lần in là nhiều nhất [tùy theo khổ giấy in đặc biệt là loại giấy có thiết kế và định dạng sẵn có keo dán.
Ở đây tôi in trên nền giấy in sẵn A5 với bốn nhãn nên tôi thiết kế trang in tương ứng như sau:
Cũng theo cách làm trên mỗi khi ta nhập một giá trị tương ứng với số thứ tự trong cột A của sheet2 vào ô D3 thì các nhãn sẽ tự động điền thông tin của 4 nhân viên có số thứ tự liền kề với giá trị của ô D3.
Như vậy thay vì in 1 lần 1 người như ở cách làm thứ 1, lúc này một lần in ta có thể in được 4 người. Nếu khổ giấy của bạn có thể in nhiều hơn thì bạn chỉ việc copy cả ba dòng chứa mã nhân viên, Họ Tên và phòng ban rồi dán xuống bên dưới. hoặc bên phải. Sau đó chỉnh sửa lại giá trị Lookup_valuecủa hàm Vlookup thành D3+4; D3+5 v.v.
Đến đây thì ứng dụng hoàn thành 90% rồi. Sở dĩ như vậy vì nếu bạn làm việc cho công ty lớn, mỗi phòng ban có nhiều người, Nếu in theo thứ tự từ 1~ hết nhân viên trong công ty sau đó mới bọc nhãn ra, sắp xếp lại rồi bàn giao cho từng phòng. Làm như thế này cũng rất mất thời gian phân loại và sắp xếp sau khi in.
Lúc này bạn nghĩ đến việc làm sao đểin theo phòng ban để không phải sắp xếp các bản in?
Cách thứ nhất,
rất đơn giản đó là bạn chuyển sang Sheet2 chứa danh sách nhân viên, ta thiết lập chức năng Data Filterở dòng thứ 1 rồi dùng chức năng Sort để sắp sếp cột Phongban theo thứ tự Alphabet, và sau đó đánh số lại cột No [Cột A], Lúc này ta có thể in theo từng phòng ban rồi.
Cách thứ 2:
Như bạn thấy ở danh sách nhân viên bên trên tôi có thêm cột P.ban_No được đánh số thứ tự theo số người trong mỗi phòng ban. Đây là điểm mấu chốt của cách 2, Vậy làm sao để đánh số thứ tự theo từng phòng ban? Ta dùng Hàm IF để thực hiện việc đánh số tự động.
Lúc này ta đảm bảo là cột Phongban [D] luôn được Sort nhé.
Quy luật đánh số như sau, nếu ô sau bằng ô trước trong cột phòng ban thì thứ tự tăng lên một bậc, nếu không thì bắt đầu đánh số lại từ số 1.
Tôi có công thức cho ô B2 [Cột P.ban_No] như sau:
=IF[F1="Phongban",1,IF[F2=F1,B1+1,1]]
Bạn copy công thức rồi dán vào ô B2, sau đó kéo công thức cho đến hết danh sách nhé.
Bây giờ ta thiết lập lại công thức ở trang in sao cho Hàm Vlookup chỉ tìm kiếm thông tin của nhân viên thuộc phòng ban xuất hiện ở ô D1 và giá trị của ô D3.
Để Hàm Vlookup tìm kiếm chính xác và không bị lỗi thì ta phải thiết lập Data Validation cho cột phòng ban ở danh sách nhân viên [Sheet2] và ô D1 của trang in
Lưu ý lúc này Lookup_Array là vùng từ cột C:F, và công thức hàm bên trên ta có thể lấy vùng tham chiếu từ C:F áp dụng cho tất cả các công thức trên.
Tối ưu thêm kết quả in:
Theo thiết lập trên thì ta đã hoàn chỉnh được chức năng in tự động giống với Merge Mail rồi, tuy nhiên sẽ có vấn đề phát sinh trong quá trình in, ví dụ như trang in bị rách một phần, làm cho chất lượng của một vùng nào đó của kết quả in bị xấu hoặc bị mất, lúc này ta muốn in lại một nhân viên bất kỳ mà không phải dò tìm nhân viên đó ở vị trí nào trong danh sách.
Giả sử như cột A đã được đánh số theo thứ tự từ 1 ở dòng số 2 thì, ta sẽ định vị được vị trí của nhân viên là kết quả của Hàm Match trừ đi 1 đơn vị. từ đó ta dùng hàm Vlookup để trả về số thứ tự của nó trong mỗi phòng ban, nó thuộc phòng ban nào.
Ta đặt công thức ở cột C2 như sau:=Vlookup[MATCH[D2,Sheet2!D:D,0]-1,Sheet2!A:B,2,0]&" "&Vlookup[MATCH[D2,Sheet2!D:D,0]-1,Sheet2!A:F,6,0]
lúc này mỗi khi nhập mã nhân viên vào ô D2 thì C2 sẽ hiện ra vị trí của nhân viên đó ở phòng ban nào. Căn cứ vào đó ta sẽ nhập lại tùy chọn ở ô D1 và D3 và in lấy kết quả.
Mỗi khi bạn nhập các số từ 1 cho đến số cuối cùng trong bản danh sách trên thì mọi thông tin của nhân viên đó được gọi ra để bạn tiến hành in.
Và lưu ý là số thứ tự trong cột A không được trùng nhau.
Tuy nhiên cách làm như trên thì mỗi lần in chỉ hiện ra thông tin của một nhân viên trên một trang giấy như thế thì mất thời gian và tốn kém khi danh sách nhân viên, hay tài sản dài hàng trăm, hàng ngàn dòng.
Lúc này đòi hỏi ta thiết kết lại trang in sao cho mối lần in là nhiều nhất [tùy theo khổ giấy in đặc biệt là loại giấy có thiết kế và định dạng sẵn có keo dán.
Ở đây tôi in trên nền giấy in sẵn A5 với bốn nhãn nên tôi thiết kế trang in tương ứng như sau:
| Phòng Ban | |||
| Mã Nhân viên | |||
| Số cần In | |||
| Dự báo số trang in | |||
| Dự báo số lần in còn lại | |||
| Mã Nhân Viên | =vlookup[D3,sheet2!A:D,4,0] | Mã Nhân Viên | =vlookup[D3+1,sheet2!A:D,4,0] |
| Họ Tên | =vlookup[D3,sheet2!A:E,5,0] | Họ Tên | =vlookup[D3+1,sheet2!A:E,5,0] |
| Phòng Ban | =vlookup[D3,sheet2!A:F,6,0] | Phòng Ban | =vlookup[D3+1,sheet2!A:F,6,0] |
| Mã Nhân Viên | =vlookup[D3+2,sheet2!A:D,4,0] | Mã Nhân Viên | =vlookup[D3+3,sheet2!A:D,4,0] |
| Họ Tên | =vlookup[D3+2,sheet2!A:E,5,0] | Họ Tên | =vlookup[D3+3,sheet2!A:D,5,0] |
| Phòng Ban | =vlookup[D3+2,sheet2!A:F,6,0] | Phòng Ban | =vlookup[D3+3,sheet2!A:D,6,0] |
Cũng theo cách làm trên mỗi khi ta nhập một giá trị tương ứng với số thứ tự trong cột A của sheet2 vào ô D3 thì các nhãn sẽ tự động điền thông tin của 4 nhân viên có số thứ tự liền kề với giá trị của ô D3.
Như vậy thay vì in 1 lần 1 người như ở cách làm thứ 1, lúc này một lần in ta có thể in được 4 người. Nếu khổ giấy của bạn có thể in nhiều hơn thì bạn chỉ việc copy cả ba dòng chứa mã nhân viên, Họ Tên và phòng ban rồi dán xuống bên dưới. hoặc bên phải. Sau đó chỉnh sửa lại giá trị Lookup_valuecủa hàm Vlookup thành D3+4; D3+5 v.v.
Đến đây thì ứng dụng hoàn thành 90% rồi. Sở dĩ như vậy vì nếu bạn làm việc cho công ty lớn, mỗi phòng ban có nhiều người, Nếu in theo thứ tự từ 1~ hết nhân viên trong công ty sau đó mới bọc nhãn ra, sắp xếp lại rồi bàn giao cho từng phòng. Làm như thế này cũng rất mất thời gian phân loại và sắp xếp sau khi in.
Lúc này bạn nghĩ đến việc làm sao đểin theo phòng ban để không phải sắp xếp các bản in?
Cách thứ nhất,
rất đơn giản đó là bạn chuyển sang Sheet2 chứa danh sách nhân viên, ta thiết lập chức năng Data Filterở dòng thứ 1 rồi dùng chức năng Sort để sắp sếp cột Phongban theo thứ tự Alphabet, và sau đó đánh số lại cột No [Cột A], Lúc này ta có thể in theo từng phòng ban rồi.
Cách thứ 2:
Như bạn thấy ở danh sách nhân viên bên trên tôi có thêm cột P.ban_No được đánh số thứ tự theo số người trong mỗi phòng ban. Đây là điểm mấu chốt của cách 2, Vậy làm sao để đánh số thứ tự theo từng phòng ban? Ta dùng Hàm IF để thực hiện việc đánh số tự động.
Lúc này ta đảm bảo là cột Phongban [D] luôn được Sort nhé.
Quy luật đánh số như sau, nếu ô sau bằng ô trước trong cột phòng ban thì thứ tự tăng lên một bậc, nếu không thì bắt đầu đánh số lại từ số 1.
Tôi có công thức cho ô B2 [Cột P.ban_No] như sau:
=IF[F1="Phongban",1,IF[F2=F1,B1+1,1]]
Bạn copy công thức rồi dán vào ô B2, sau đó kéo công thức cho đến hết danh sách nhé.
Bây giờ ta thiết lập lại công thức ở trang in sao cho Hàm Vlookup chỉ tìm kiếm thông tin của nhân viên thuộc phòng ban xuất hiện ở ô D1 và giá trị của ô D3.
Để Hàm Vlookup tìm kiếm chính xác và không bị lỗi thì ta phải thiết lập Data Validation cho cột phòng ban ở danh sách nhân viên [Sheet2] và ô D1 của trang in
| Phòng Ban | Hành ChínhKế ToánKinh DoanhSản XuấtVật Tư | ||
| Mã Nhân viên | |||
| Số cần In | |||
| Dự báo số trang in | |||
| Dự báo số trang in còn lại | |||
| Mã Nhân Viên | =vlookup[D3&D1;sheet2!C:D,2,0] | Mã Nhân Viên | =vlookup[D3+1&D1,sheet2!C:D,3,0] |
| Họ Tên | =vlookup[D3&D1,sheet2!C:E,3,0] | Họ Tên | =vlookup[D3+1&D1,sheet2!C:E,3,0] |
| Phòng Ban | =vlookup[D3&D1,sheet2!C:F,4,0] | Phòng Ban | =vlookup[D3+1&D1,sheet2!C:F,4,0] |
| Mã Nhân Viên | =vlookup[D3+2&D1,sheet2!C:D,2,0] | Mã Nhân Viên | =vlookup[D3+3&D1,sheet2!C:D,2,0] |
| Họ Tên | =vlookup[D3+2&D1,sheet2!C:E,3,0] | Họ Tên | =vlookup[D3+3&D1,sheet2!C:E,3,0] |
| Phòng Ban | =vlookup[D3+2&D1,sheet2!C:F,4,0] | Phòng Ban | =vlookup[D3+3&D1,sheet2!C:F,4,0] |
Lưu ý lúc này Lookup_Array là vùng từ cột C:F, và công thức hàm bên trên ta có thể lấy vùng tham chiếu từ C:F áp dụng cho tất cả các công thức trên.
Tối ưu thêm kết quả in:
Theo thiết lập trên thì ta đã hoàn chỉnh được chức năng in tự động giống với Merge Mail rồi, tuy nhiên sẽ có vấn đề phát sinh trong quá trình in, ví dụ như trang in bị rách một phần, làm cho chất lượng của một vùng nào đó của kết quả in bị xấu hoặc bị mất, lúc này ta muốn in lại một nhân viên bất kỳ mà không phải dò tìm nhân viên đó ở vị trí nào trong danh sách.
Giả sử như cột A đã được đánh số theo thứ tự từ 1 ở dòng số 2 thì, ta sẽ định vị được vị trí của nhân viên là kết quả của Hàm Match trừ đi 1 đơn vị. từ đó ta dùng hàm Vlookup để trả về số thứ tự của nó trong mỗi phòng ban, nó thuộc phòng ban nào.
Ta đặt công thức ở cột C2 như sau:=Vlookup[MATCH[D2,Sheet2!D:D,0]-1,Sheet2!A:B,2,0]&" "&Vlookup[MATCH[D2,Sheet2!D:D,0]-1,Sheet2!A:F,6,0]
lúc này mỗi khi nhập mã nhân viên vào ô D2 thì C2 sẽ hiện ra vị trí của nhân viên đó ở phòng ban nào. Căn cứ vào đó ta sẽ nhập lại tùy chọn ở ô D1 và D3 và in lấy kết quả.
Thêm một ý tưởng nữa là chúng ta sẽ dự báo số lần in cho mỗi một phòng ban và số lần in còn lại để giúp ta chuẩn bị giấy in tốt hơn.
Dự báo số trang in [ô D4] : Ta dùng Hàm CountIF để đếm số nhân viên căn cứ vào giá trị của ô D4 [Phòng ban] sau đó chia số nhân viên trong mỗi lần in
C4=countif[sheet2!D:D,Sheet1!D1]
D4=C4/4
Dự báo số trang in còn lại [ô D5] ta dùng Hàm And để kết hợp với Hàm IF để xuất ra các hướng dẫn cho người in.
=IF[AND[D10,D3=0,C30,D3>0,C4-D40,D3=0,C4>4],"Bạn hãy in từ số "&D4+4&" cho đến hết "&C4,IF[AND[D1>0,D3>0,C4>4],"Hãy in từ số "&D4+4&" Cho đến hết "&"["&C4&"]"]]]]]
Lưu ý: Số4là số nhân viên trong mỗi trang in. do đó nếu trang in của bạn lớn hơn 4 thì bạn chỉ cần thay đổi tương ứng nhé.
Chúc bạn thành công