Trích xuất danh sách duy nhất khác biệt tự động

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://masothuecongty.com

Xem thêm bài viết khác: https://masothuecongty.com/tra-cuu