Hướng dẫn cách lập bảng tính công tác phí trong Excel chi tiết nhất

Việc quản lý, theo dõi tiền phụ cấp, công tác phí là một trong những công việc thường gặp của kế toán. Nhưng để làm tốt việc này trên excel lại không hề đơn giản. Trong bài viết này chúng ta hãy cùng tìm hiểu về cách kết hợp hàm VLOOKUP, chức năng Data validation và PivotTable để tạo một ứng dụng đơn giản theo dõi phụ cấp, công tác phí nhé.

Sử dụng hàm VLOOKUP để tính chi phí công tác

Đặc điểm bảng dữ liệu theo dõi chi phí công tác

Chúng ta cùng xét ví dụ cụ thể như sau: Để theo dõi phí công tác, chúng ta có 2 bảng tính:

• Bảng bên trái theo dõi giờ làm việc của mỗi nhân viên tại các địa điểm làm việc cụ thể.
• Bảng bên phải liệt kê từng địa điểm làm việc và mức chi hàng ngày.

Bạn có thể ghi nhớ số tiền và liệt kê vào bảng hàng giờ, nhưng điều đó gây ra rắc rối: bạn có thể nhập sai chính tả khi nhập các địa điểm làm việc, hoặc nhập sai về số tiền (như lỡ tay chèn 1 ký tự văn bản vào cột Số tiền - Stipend). Để tránh những sai sót có thể xảy ra, chúng ta nên sử dụng hàm VLOOKUP để tham chiếu các thông tin thay vì nhập thủ công trực tiếp vào bảng.

Lưu ý:

• Mỗi nhân viên có thể đến 1 hoặc nhiều địa điểm làm việc khác nhau trong một ngày. Chúng ta sẽ phải cộng tổng phụ cấp theo ngày tại toàn bộ các địa điểm làm việc.
• Mỗi nhân viên có thể chỉ đi công tác vài giờ trong ngày, vậy nên anh ta chỉ nhận được công tác phí cho những giờ làm việc ngoài văn phòng làm việc chính (Home Office).
• Khi nhập dữ liệu cần đảm bảo nhập đúng thông tin về Địa điểm làm việc và Số tiền công tác phí tại từng địa điểm tương ứng.

Trong suốt bài viết, chúng ta sẽ gọi danh sách bên trái là Danh sách giờ làm việc và danh sách bên phải là Danh sách công tác phí. Để thuận lợi hơn trong quá trình thao tác trên Excel, chúng ta có thể chuyển các bảng về dạng Table như sau (làm với từng bảng):

1. Nhấp vào 1 vị trí bất kỳ bên trong phạm vi bảng dữ liệu.
2. Bấm vào thanh công cụ tại thẻ Insert và sau đó chọn mục Table (bên trong nhóm Tables).
3. Vì bảng tính này có sẵn dòng tiêu đề nên bạn chú ý có sử dụng dấu tích trong lựa chọn My table has headers.
4. Bấm OK.

Kết quả chúng ta sẽ có bảng Table1 ở bên trái, Table2 ở bên phải. Đây là các tên mặc định của Excel khi bạn tạo Table. Bạn có thể đổi lại tên cho các bảng này tại thẻ Table Design nếu muốn (khi bấm vào bảng sẽ thấy xuất hiện thẻ Table Design trên thanh công cụ).

Cú pháp hàm VLOOKUP

Hàm VLOOKUP trong Excel có cú pháp như sau:

=VLOOKUP (lookup_value, table, column_index, range)

• lookup_value là ô hoặc dải ô chứa giá trị trong danh sách hàng giờ mà bạn đang tìm kiếm, trong ví dụ này là Vị trí (cột E)
• table xác định bảng tra cứu - H4: I6 (bảng danh sách cộng trừ các tiêu đề);
• column_index đại diện cho cột có chứa các giá trị bạn muốn quay trở lại trong mối quan hệ với giá trị tra cứu - lương
• range là một / giá trị FALSE TRUE rằng các lực lượng (hoặc không) một trận đấu chính xác.

Bây giờ, hãy nhập hàm VLOOKUP sau vào ô F4 (trong trường hợp bảng đã được chuyển sang dạng Table):

=VLOOKUP([Location],Table2,2,FALSE)

Nếu bạn chưa chuyển các bảng sang dạng Table trong excel thì bạn có thể viết theo tọa độ như sau:

=VLOOKUP($E$4,$H$4:$I$6,2,FALSE)

Lưu ý rằng hai phạm vi (đối với phạm vi dữ liệu thông thường) phải là tham chiếu tuyệt đối (cố định tọa độ tham chiếu với phím F4)

Hình B cho thấy kết quả sau khi định dạng cột mới thành Tiền tệ và thêm văn bản tiêu đề. Bảng sẽ tự động điều chỉnh để bao gồm cột mới.

Chú ý: Khi dùng hàm VLOOKUP bạn có thể gặp một số lỗi phát sinh như lỗi #N/A:

• Dòng 4: Lỗi này phát sinh do một số địa điểm làm việc không có trong bảng tham chiếu, khi đó hàm không tìm thấy => Lỗi #N/A nghĩa là Not Available = Không tồn tại.
• Dòng 8: hãy để ý chữ "McValey" tại ô E8 khác với "McValley" tại ô H6 (trong bảng tham chiếu) => khi đó hàm không tìm được giá trị nào là "McValey" trong bảng tham chiếu.

Cách khắc phục:

Thêm hàm IF vào phía trước hàm VLOOKUP biện luận trường hợp không tìm thấy thông tin, khi đó kết quả nhận được là số 0 thay vì lỗi #N/A

=IF(COUNTIF($H$4:$H$6, E4)=0, "", VLOOKUP(...))

Trong đó:

• Hàm COUNTIF đếm xem có bao nhiêu giá trị tại ô E4 trong bảng tham chiếu.
• Nếu hàm COUNTIF ra kết quả bằng 0 tức là không có, không xuất hiện trong bảng tham chiếu => Nhận kết quả là số 0. Nếu có (trường hợp lớn hơn 0) thì dùng hàm VLOOKUP để lấy kết quả.

Với trường hợp nhập sai chính tả (như ở dòng 8) thì chúng ta sẽ nhập lại nội dung cho đúng chính tả. Cách tối ưu hơn là thêm chức năng kiểm soát dữ liệu nhập vào bảng với Data Validation.

Thêm Data Validate giới hạn các giá trị được nhập vào bảng tính

Việc sử dụng chức năng Xác thực dữ liệu trong Data Validation sẽ giúp bạn hạn chế được các lỗi sai chính tả khi nhập nội dung trong Excel. Các bước thực hiện như sau:

1. Chọn vùng E4:E10 
2. Bấm vào tab Data, sau đó bấm Data Validation trong nhóm Data Tools.
3. Trong hộp thoại hiện ra, chọn List từ menu thả xuống Allow.
4. Chỉ ra các giá trị vị trí trong nhóm phụ thuộc trong điều khiển Source (Hình D).
5. Bấm OK.

Hình D: Tạo data validate hạn chế đầu vào chỉ các giá trị trong danh sách nguồn. 

Bây giờ, bạn hãy chọn E8 và sử dụng danh sách thả xuống data validation, nhập McValley, như thể hiện trong hình E.

Như bạn có thể thấy, sau khi bạn sửa lỗi chính tả của giá trị vị trí, hàm VLOOKUP () sẽ hoạt động như mong đợi. Ngoài ra, hộp điều khiển này là động, khi bạn cập nhật danh sách phụ cấp cũng sẽ cập nhật danh sách validation list.

Tính năng này đặt biệt hữu ích nếu bạn phải làm việc với hàng trăm hàng dữ liệu. Bạn không phải kiểm tra từng hàng một, và cũng không phải thay đổi công thức khi cần cập nhật địa điểm công tác hay số tiền công tác phí.

Hình E: Sử dụng danh sách validation list để tránh lỗi nhập sai. 

Sử dụng PivotTable để tính tổng phụ cấp công tác cho mỗi nhân viên

Bước tiếp theo, chúng ta sẽ tính tổng công tác phí theo ngày cho mỗi nhân viên. Hãy nhớ rằng mỗi nhân viên có thể làm việc tại nhiều vị trí trong cùng một ngày. Ví dụ, E-3 đã làm việc tại hai địa điểm vào ngày 1 tháng 5 và cả hai địa điểm đều có mức phụ cấp áp dụng. E-3 sẽ nhận được tổng cộng 75 đô la tiền phụ cấp cho ngày 1 tháng 5.

Có rất nhiều giải pháp, nhưng có lẽ cách dễ nhất là sử dụng PivotTable. Để thực hiện, hãy nhấp vào bất kỳ đâu bên trong danh sách giờ làm việc và nhấp vào tab Insert. Sau đó, làm như sau:

1. Bấm PivotTable trong nhóm Table. Trong hộp thoại hiện ra, bấm OK.
2. Bấm vào bên trong khung PivotTable, khung này sẽ hiển thị ngăn danh sách.
3. Trong ngăn fields, kiểm tra các trường Employee và trường Site Stipend  (Hình F).

Hình F: Hiển thị tổng số tiền phụ cấp cho mỗi nhân viên.

Như vậy, PivotTable hiển thị công tác phí tổng cộng cho mỗi nhân viên. Như bạn có thể thấy trong Hình F, tổng số tiền của E-3 là 75 đô la - cho hai địa điểm công tác trong cùng một ngày. Nếu bạn cần một tổng phụ hàng ngày, chỉ cần thêm trường Date trong Pivot Table, như thể hiện trong hình G .

Hình G: Thêm trường Date để hiển thị tổng phụ cho mỗi ngày.

Như vậy, chúng ta đã biết cách tính công tác phí trong Excel, với hàm VLOOKUP để xác định mức phí cho mỗi địa điểm công tác, Data valiation để giới hạn giá trị nhập, tránh sai xót và PivotTable để tính tổng tiền công tác phí theo ngày cho mỗi nhân viên.

Hy vọng bạn thấy hướng dẫn này hữu ích. Bên cạnh đó, đừng quên tham gia Gitiho ngay hôm nay để không bỏ lỡ những mẹo và thủ thuật tin học văn phòng hữu ích khác.