TRÍCH XUẤT DANH SÁCH DUY NHẤT, KHÁC BIỆT TỰ ĐỘNG.
Sử dụng các hàm: Sumif; Countif; Max; Index; Match; If; Sum.
Câu 1: Soạn thảo lập Bảng lương Bộ phận quản lý Tháng 1/2018 đến hết Tháng 12/2018. Sheet: L1; L2 …… L12.
• Soạn thảo: Sử dụng bộ gõ tiếng việt Unikey 4.3 RC3; kiểu gõ Telex; bảng mã Unicode; font Time new roman; Soạn Tên bảng chữ hoa liên tục bật Caps Lock; Khi Caps lock nhấn giữ Shift cộng chữ cái để viết hoa chữ cái đầu dòng và ngược lại; Mã số thuế hoặc CMND hoặc CCCD nếu bắt đầu bằng số 0 hãy gõ thêm ký tự ‘ ở đầu ô cell; Thêm ghi chú cho ô cell.
• Căn chỉnh: Merge dòng Tên bảng; dòng Chức danh ký tên, dòng tên người ký; dòng ngày tháng ký tên. Dòng tiêu đề bảng in đậm; Nội dung chung Font Time new roman, cỡ chữ 12pt. Tên bảng in hoa, cỡ chữ 14pt; Chức danh /Nên in hoa/, Tên người ký cỡ chữ 14pt; Cố định dòng cột theo ý muốn.
Câu 2: Tạo Bảng tổng hợp lương cán bộ Quản lý năm 2018. Sheet: BANGLUONG; tô màu tiêu đề bảng. Tương tự tạo Bảng lương bộ phận Quản lý
Câu 3: Tạo Bảng Xử lý danh sách loại bỏ giá trị trùng lặp. Sheet: Xu ly danh sach.
• Link các dữ liệu cần thiết từ Sheet L1; L2;….;L12 sang Sheet Xu ly danh sach.
• Tạo hàm cho cột đếm lần 1.
Đếm điều kiện cột Danh sách họ và tên cột K từ trên xuống. Ô cell G5 gõ: Hàm =COUNTIF/$K$5:K5;K5/. Kéo fill cho đến hết.
• Tạo hàm cho cột Đếm lần 2.
Đếm điều kiện cột G những giá trị bằng 1 từ trên xuống. Ô cell H5 gõ Hàm: =COUNTIF/$G$5:G5;1/. Kéo fill cho đến hết.
• Tạo hàm cho cột Điều kiện 1.
Hàm if nếu cột K bằng 0 thì giá trị cột I /cột điều kiện 1/ bằng không; nếu khác 0 thì thì bằng cột H. Tại ô I5 nhập hàm: =IF/K5=0;0;H5/. Kéo fill cho đến hết.
• Tạo hàm cho cột Điều kiện 2.
Riêng ô J5 nhập Hàm: =IF/I5lớn hơn0;1;0/. Nếu Cột I giá trị lớn hơn0 thì bằng 1 còn không bằng 0;
Từ J6 trở đi Nếu Ở cột H 2 ô cell liên tục mà bằng nhau thì bằng 0 còn không thì Tiếp điều kiện Nếu cột Ilớn hơn0 thì bằng giá trị lớn nhất của cell nhập hàm trở về ô J5 cộng thêm 1 còn không thì bằng 0.
Tại ô J6 nhập Hàm: =IF/H6=H5;0;IF/I6lớn hơn0;MAX/$J$5:J5/cộng1;0//. Kéo fill cho cho đến hết
• Tạo hàm Loại bỏ trùng lặp.
Dùng hàm Iferror// để ko hiển thị giá trị lỗi của hàm Index. Nếu lỗi cho về giá trị trắng. “”
Dùng Hàm Index// với vùng Array $K$5:$K$136 cố định. Giá trị dò dòng là giá trị đưa về từ hàm Match// với giá trị dò là giá trị cột thứ tự cột M. và giá trị cột là 0. Tại ô N5 nhập Hàm:
=IFERROR/INDEX/$K$5:$K$136;MATCH/M5;$J$5:$J$136;0//;””/. Kéo fill cho đến hết.
Câu 4: Ở Sheet BANGLUONG. Đặt hàm để lấy các thông tin: Danh sách duy nhất khác biệt từ bảng lương của 12 tháng trong năm 2018. Lấy kèm thông tin CMND; MST; Tổng thu nhập trong 1 năm.
• Danh sách duy nhất: cột Họ và tên. Link dữ liệu từ cột N bên sheet Xu ly danh sach.
• Lấy thông tin CMND; MST. Dùng hàm Vlookup// dò tìm bên Sheet Xu ly danh sach – bảng từ cột C đến cột F. Kết hợp hàm Iferror// để ko hiển thị giá trị lỗi khi hàm Vlookup ko có giá trị. Tại Ô D4 Sheet BANGLUONG: nhập hàm:
=IFERROR/VLOOKUP/B4;’Xu ly danh sach’!$C$5:$F$136;2;0/;””/. Kéo fill cho đến hết.
Tại Ô E4 Sheet BANGLUONG: nhập hàm:
=IFERROR/VLOOKUP/B4;’Xu ly danh sach’!$C$5:$F$136;3;0/;””/. Kéo fill cho đến hết.
• Lấy thông tin Tổng thu nhập trong cả năm 2018. Dùng hàm Sumif// với điều kiện là cột Họ và tên. dò bên Sheet Xu ly danh sach, cột tổng là cột Tổng thu nhập chịu thuế. Tại Ô P4 Sheet BANGLUONG: nhập hàm:
=SUMIF/’Xu ly danh sach’!$C$5:$F$136;BANGLUONG!B4;’Xu ly danh sach’!$F$5:$F$136/
Ghi chú: dấu lớn hơn đc thay bằng chữ; dấu ngoặc thay bằng dấu /.
Link file để thực hành. đã loại bỏ hàm trong sheet BANGLUONG và Xu ly danh sach:
Link các video hữu ích khác.

Nguồn: https://vikings-jersey.net/

Xem thêm bài viết khác: https://vikings-jersey.net/tong-hop/

Author

2 Comments

  1. Trung tâm Máy chiếu JAV Việt Nam Reply

    Cho em hỏi, em muốn tạo 1 bảng tổng hợp theo dõi phát sinh đơn hàng, lấy thông tin khách hàng từ 1 sheet khác sang sheet tổng hợp này mà chỉ cần gọi tên khách hàng là tự động các thông tin còn lại của khách hàng có tên đó hiên lên ạ. Em cảm ơn!

Write A Comment