TRUNG TÂM GEC

Một số công cụ excel tiện ích cho kế toán

Công cụ tiện ích trong excel dành cho kế toán.

CỐ ĐỊNH DÒNG CỘT (Đóng băng – Freeze Panel)

            Khi bạn làm việc với một bảng dữ liệu có nhiều cột thông tin và khối lượng dòng dữ liệu lớn thì mỗi lần làm việc với các dòng dữ liệu phía dưới hay các cột thông tin bên phải thì phần dữ liệu phía trên hoặc bên phải sẽ bị cuộn mất, bạn có thể sử dụng thao tác cố định dòng hoặc cố định hoặc cố định cả dòng và cột để giữ cố định phần tiêu đề như sau:

Cố định dòng:

- Xác định vị trí đặt trỏ ô (cell pointer): tại ô đầu tiên của dòng phía dưới dòng cần cố định. Ví dụ: bạn cần cố định dòng 3 -> đặt con trỏ ô tại ô A4

- Chọn View->Freeze Panes.

- Kết quả và hiệu ứng khi cuộn xuống các dòng bên dưới:

Cố định cột

- Xác định vị trí đặt trỏ ô (cell pointer): tại ô đầu tiên của cột bên phải cột cần cố định. Ví dụ: bạn cần cố định cột C -> đặt con trỏ ô tại ô D1

- Chọn View->Freeze Panes.

- Kết quả và hiệu ứng khi cuộn sang các cột bên phải:

Lưu ý: trong trường hợp muốn cố định dòng 1 thì ta chọn:

Cố định dòng – cột

- Xác định vị trí đặt trỏ ô (cell pointer): tại ô thuộc dòng phía dưới dòng cần cố định và thuộc cột bên phải cột cần cố định.

Ví dụ: bạn cần cố định dòng 3 cột C -> đặt con trỏ ô tại ô D4

- Chọn View->Freeze Panes.

- Kết quả và hiệu ứng khi cuộn dữ liệu:

Hủy cố định dòng / cột: chọn View -> Unfreeze Panel.

CHỌN VÙNG NHANH:

Khi bạn chọn 1 vùng làm việc trong Excel, ngoài thao tác sử dụng trỏ chuột để quét vùng thì bạn có thể sử dụng một trong 2 cách sau đây để thao tác, nhất là khi chọn

Cách 1. Sử dụng phím:

- Đặt trỏ ô tại ô đầu tiên của bảng dữ liệu

- Nhấn Ctrl + Shift + (®) Phím di chuyển sang phải: chọn toàn bộ các cột

- Nhấn Ctrl + Shift + (¯) Phím di chuyển xuống dưới: chọn toàn bộ các dòng

Cách 2. Sử dụng hộp thoại Go To:

- Xác định phạm vi (hoặc ước lượng) của bảng dữ liệu (ví dụ: A3:P50)

- Nhấn phím F5 -> xuất hiện hộp thoại Go To

- Nhập phạm vi của bảng dữ liệu vào mục Reference, nhấn Enter (hoặc chọn OK)

TÊN VÙNG - NAME

            Trong trường hợp lập báo cáo sẽ có những thông tin mà bạn luôn phải nhập đi nhập lại, để tránh sai sót các bạn có thể dùng công cụ Name của Excel để tạo ra các giá trị có sẵn

            - Tạo 1 sheet, đổi tên là Thongtin

            - Trong sheet nhập những thông tin thường xuyên sử dụng

            - Đặt Name cho từng ô dữ liệu

                        + Chọn ô

                        + Chọn vùng Name Box trên thanh công thức

                        + Nhập Name (phải khác với địa chỉ ô)

                        + Nhấn Enter

- Lần lượt thực hiện cho các thông tin còn lại

- Để tùy chỉnh các Name: chọn Fomulas -> Name Manager

- Sử dụng Name trong lập bảng báo cáo, gõ =ten_Name

- Kết quả:

            Lưu ý: khi bạn muốn thay thế bất kỳ thông tin nào hãy chọn sheet Thongtin và chỉnh sửa, Excel sẽ tự động cập nhật trên tất cả các bảng báo cáo.

SẮP XẾP - SORT

- Chọn vùng dữ liệu (database) (Lưu ý: bao gồm cả dòng tiêu đề - header row và không chọn cột STT – số thứ tự)

- Chọn Data -> Sort hoặc Home -> Sort & Filter -> Custom Sort …

 Ví dụ: Sắp xếp bảng dữ liệu theo Tên hàng

Ví dụ: Sắp xếp bảng dữ liệu theo Tên hàng, theo Loại và theo Doanh thu

LỌC NHANH DỮ LIỆU:

- Chọn dòng tiêu đề của bảng dữ liệu

- Chọn Home -> Sort & Filter -> Filter

- Tùy thuộc vào nhu cầu, bạn thực hiện click dấu [mũi tên chỉ xuống] trên mỗi tiêu đề để chọn dữ liệu cần lọc. Ví dụ: Lọc các dòng dữ liệu của mặt hàng Cam sành

THỐNG KÊ 1 CHIỀU – SUBTOTAL

- Chọn phạm vi database

- Sắp xếp bảng dữ liệu theo chỉ tiêu phân nhóm thống kê (bắt buộc)

- Chọn Data -> Subtotal

      + Mục At each change in: chọn chỉ tiêu phân nhóm thống kê

      + Mục Use function: chọn hàm thống kê

      + Mục Add subtotal to: chọn chỉ tiêu tính thống kê

Ví dụ: Tính tổng số lượng, tiền vốn, doanh thu, thuế và lãi gộp của từng mặt hàng

Kết quả thực hiện Subtotal:

Lưu ý: khi thực hiện Subtotal, Excel sẽ dùng hàm SUBTOTAL để thực hiện tính thống kê. Cú pháp hàm: SUBTOTAL(function_num, ref1, [ref2],…)

      Trong đó:

+ function_num: chọn hàm tính thống kê

+ ref1, ref2,…: vùng tính toán

THỐNG KÊ ĐA CHIỀU – PIVOT TABLE

            - Chọn phạm vi database

            - Chọn Insert -> Pivot Table

            - Xuất hiện vùng làm việc của bảng thống kê như hình dưới

Hoặc điều chỉnh thành giao diện làm việc có đủ 4 vùng như sau:

            + Chọn Ribbon Options -> Options

            Giao diện làm việc của Pivot Table 4 vùng:

Để thực hiện lập bảng thống kê bạn hãy drag thả các tiêu đề cột từ Field List vào các vùng tương ứng trong phạm vi của bảng thống kê.

Ví dụ: Tính tổng Doanh thu theo Tên hàng và Chi nhánh

Tùy chỉnh cho bảng thống kê:

            Đối với các chỉ tiêu thống kê hay số liệu tính thống kê, khi bạn muốn tùy chỉnh hãy chọn [mũi tên chỉ xuống] để xuất hiện hộp thoại thông tin

Ví dụ: Tính tổng Số lượng, Doanh thu, Lãi gộp theo Tên hàng, Loại và Chi nhánh

Chúc các bạn thành công...!