Khám phá những thủ thuật Excel đỉnh cao

Excel đã trở thành công cụ không thể thiếu của dân văn phòng. Ngày nay, các tác vụ như báo cáo, thống kê hay phân tích số liệu đều rất quan trọng trong doanh nghiệp. Khối lượng và độ phức tạp của chúng ngày càng lớn, nhân sự liên quan đến những công việc này bắt buộc cần trau dồi liên tục về kỹ năng. Do đó, việc nắm vững các thủ thuật dùng Excel từ cơ bản đến nâng cao rất cần thiết.

Trong bài viết, ICTGO sẽ giới thiệu chi tiết các thủ thuật Excel hữu ích nhất. Những thủ thuật này chắc chắn giúp bạn tăng năng suất, tối ưu hoá quy trình và dễ dàng xử lý dữ liệu phức tạp. Mỗi thủ thuật đi kèm ví dụ thực tiễn và hướng dẫn cụ thể để bạn dễ thực hiện. Bắt đầu nhé!

Hàm tính toán thông minh

VLOOKUP và HLOOKUP

Hàm VLOOKUP (Vertical Lookup) được thiết kế để tìm kiếm một giá trị trong cột đầu tiên của một bảng dữ liệu dọc. Nó sẽ trả về giá trị từ một cột khác trên cùng hàng. Hàm này rất hữu ích khi bạn cần tra cứu thông tin dựa trên một khóa chính. Chẳng hạn như mã nhân viên, mã sản phẩm,… Công thức của nó là:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Trong đó.

• lookup_value: Giá trị cần tìm (ví dụ: “NV001”).
• table_array: Vùng dữ liệu chứa giá trị cần tra cứu (ví dụ: A2:C100).
• col_index_num: Số thứ tự cột trong bảng cần lấy dữ liệu (ví dụ: 2 nếu muốn lấy tên nhân viên).
• [range_lookup]: Xác định tìm kiếm chính xác (FALSE) hay gần đúng (TRUE).

Ví dụ:

=VLOOKUP(“NV001”, A2:C100, 2, FALSE)

Nếu “NV001” nằm ở hàng đầu tiên của bảng, công thức trả về giá trị ở cột thứ 2 của hàng đó.

HLOOKUP hoạt động theo cách tương tự. Nó tìm kiếm theo hàng ngang. Hàm này hữu ích khi dữ liệu sắp xếp theo hàng.

INDEX và MATCH

Trong khi VLOOKUP có giới hạn chỉ tra cứu từ cột đầu tiên. Tổ hợp INDEX và MATCH cung cấp giải pháp linh hoạt hơn. INDEX có thể trả về giá trị từ một mảng dựa trên vị trí hàng và cột. Công thức là:

=INDEX(array, row_num, [column_num])

Trong đó:

• array: Vùng dữ liệu chứa giá trị cần trả về (ví dụ: B2:B100).
• row_num: Số thứ tự hàng trong mảng (ví dụ: 5).
• [column_num]: Số thứ tự cột (nếu vùng dữ liệu có nhiều cột).

Đối với hàm MATCH, nó cho phép tìm kiếm một giá trị trong một dãy và trả về vị trí của giá trị đó. Công thức là:

=MATCH(lookup_value, lookup_array, [match_type])

Trong đó:

• lookup_value: Giá trị cần tìm (ví dụ: “NV001”).
• lookup_array: Vùng chứa giá trị cần so sánh (ví dụ: A2:A100).
• [match_type]: 0 cho khớp chính xác, 1 hoặc -1 cho khớp gần đúng.

Ví dụ về hàm INDEX trả về giá trị theo vị trí hàng và cột. Hàm MATCH tìm vị trí của giá trị trong mảng như sau:

=INDEX(B2:B100, MATCH(A2, A2:A100, 0))

Công thức này tìm vị trí của ô A2 trong dãy A2:A100. Sau đó, nó trả về giá trị tương ứng từ B2:B100.

SUMIF, COUNTIF

Hàm SUMIF được sử dụng để tính tổng các giá trị trong một phạm vi dựa trên một điều kiện cụ thể. Nó giúp bạn tổng hợp dữ liệu theo điều kiện đặt ra. Chẳng hạn như tính tổng doanh số của một sản phẩm cụ thể.

=SUMIF(range, criteria, [sum_range])

Trong đó:

• range: Vùng dữ liệu đánh giá điều kiện (ví dụ: A2:A10 chứa tên sản phẩm).
• criteria: Điều kiện cần thoả mãn (ví dụ: “Sản phẩm A”).
• [sum_range]: Vùng dữ liệu cần tính tổng (ví dụ: B2:B10 chứa doanh số).

Ví dụ tính tổng doanh số của các ô có giá trị “Sản phẩm A”:

=SUMIF(A2:A10, “Sản phẩm A”, B2:B10)

Đối với hàm COUNTIF, nó được dùng để đếm số ô thỏa mãn điều kiện nhất định trong một phạm vi. Rất hữu ích khi bạn cần đếm số lượng xuất hiện của một giá trị cụ thể trong bảng dữ liệu.

=COUNTIF(range, criteria)

Trong đó:

• range: Vùng dữ liệu cần đếm (ví dụ: A2:A10).
• criteria: Điều kiện đếm (ví dụ: “Sản phẩm A”).

Ví dụ đếm số ô chứa “Sản phẩm A”:

=COUNTIF(A2:A10, “Sản phẩm A”)

SUMIFS và COUNTIFS

Hàm SUMIFS mở rộng chức năng của SUMIF. Nó cho phép tính tổng dựa trên nhiều điều kiện cùng lúc. Điều này giải quyết việc tổng hợp dữ liệu có nhiều tiêu chí phải thỏa mãn.

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2],…)

Trong đó:

• sum_range: Vùng cần tính tổng (ví dụ: B2:B10).
• criteria_range1: Vùng dữ liệu đánh giá điều kiện thứ nhất (ví dụ: A2:A10).
• criteria1: Điều kiện thứ nhất (ví dụ: “Sản phẩm A”).
• criteria_range2, criteria2: Điều kiện bổ sung (tuỳ chọn).

Ví dụ tính tổng doanh số của “Sản phẩm A” ở khu vực “Miền Bắc”.

=SUMIFS(B2:B10, A2:A10, “Sản phẩm A”, C2:C10, “Miền Bắc”)

Tương tự như SUMIFS, hàm COUNTIFS cho phép đếm số ô thỏa mãn nhiều điều kiện cùng lúc. Đây là công cụ hữu ích để phân tích tần suất xuất hiện của các giá trị khi có nhiều tiêu chí cần kiểm tra.

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], …)

Trong đó:

• criteria_range1: Vùng dữ liệu để kiểm tra điều kiện thứ nhất (ví dụ: A2:A10).
• criteria1: Điều kiện thứ nhất (ví dụ: “Sản phẩm A”).
• criteria_range2, criteria2: Điều kiện bổ sung (tuỳ chọn).

Ví dụ đếm số ô chứa “Sản phẩm A” ở khu vực “Miền Nam”.

=COUNTIFS(A2:A10, “Sản phẩm A”, C2:C10, “Miền Nam”)

Pivot Table và Pivot Chart

Pivot Table là công cụ phân tích dữ liệu mạnh mẽ. Nó tóm tắt và phân loại dữ liệu lớn nhanh chóng. Chọn bảng dữ liệu cần phân tích. Sau đây là hướng dẫn tạo Pivot Table:

1.  Chọn toàn bộ bảng dữ liệu cần phân tích.
2.  Vào thẻ Insert rồi chọn Pivot Table.
3. Chọn phạm vi dữ liệu và vị trí hiển thị (trang tính mới hoặc hiện tại).
4.  Kéo trường dữ liệu vào khu vực Rows, Columns, Values và Filters.
5. Pivot Table được tạo tự động hiển thị kết quả tóm tắt.

Đối với cách tạo Pivot Chart:

1. Chọn Pivot Table vừa tạo.
2.  Vào thẻ Insert rồi chọn loại biểu đồ phù hợp.
3.  Tùy chỉnh tiêu đề và màu sắc cho biểu đồ.
4. Pivot Chart sẽ tự động cập nhật khi Pivot Table thay đổi.

Conditional Formatting

Conditional Formatting thay đổi định dạng ô theo giá trị. Công cụ này giúp nhận diện xu hướng và bất thường. Chọn vùng dữ liệu cần áp dụng. Sau đây là hướng dẫn:

1. Chọn vùng dữ liệu cần áp dụng định dạng.
2.  Vào tab Home và chọn Conditional Formatting.
3. Chọn kiểu định dạng.
   Ví dụ: “Greater Than” để làm nổi bật các ô có giá trị vượt 100.
4.  Nhập giá trị điều kiện (100) và chọn định dạng (màu nền, màu chữ).
5. Nhấn OK để áp dụng. Các ô vượt 100 sẽ tự động thay đổi màu sắc.

Macro và VBA

Macro và VBA tự động hóa các tác vụ lặp đi lặp lại. Chúng hữu ích cho quy trình phức tạp. Hướng dẫn ghi Macro:

1. Kích hoạt tab Developer (nếu chưa có, vào File > Options > Customize Ribbon).
2. Nhấn Record Macro.
3. Thực hiện chuỗi thao tác cần tự động hóa.
4. Nhấn Stop Recording khi hoàn tất.

Để chỉnh sửa Macro với VBA. Bạn cần mở Visual Basic Editor bằng cách nhấn ALT + F11. Tìm Macro vừa ghi và chỉnh sửa mã lệnh. Bạn có thể thêm vòng lặp hoặc điều kiện logic. Ví dụ, đoạn mã dưới đây tự động định dạng ô.

Power Query và Power Pivot

Power Query và Power Pivot xử lý và phân tích dữ liệu từ nhiều nguồn. Power Query giúp nhập dữ liệu từ nhiều nguồn, làm sạch và chuyển đổi dữ liệu hiệu quả hơn. Các bước cơ bản như sau:

1. Vào thẻ Data và chọn Get Data.
2. Chọn nguồn dữ liệu mong muốn.
3. Trong cửa sổ Query Editor, lọc, chia cột và thay thế giá trị.
4. Chọn Close & Load để đưa dữ liệu vào Excel.

Trong khi đó, Power Pivot xây dựng mô hình dữ liệu cho lượng dữ liệu lớn. Nó tạo mối quan hệ giữa các bảng dữ liệu. Sử dụng ngôn ngữ DAX để tính toán chỉ số chuyên sâu.

1. Kích hoạt add-in Power Pivot qua File.
2. Chọn Options > Add-Ins.
3. Import dữ liệu từ các bảng và thiết lập mối quan hệ.
4. Viết công thức DAX để tạo cột tính toán hoặc measures.

Tối Ưu Hóa Quy Trình

Ngoài các chức năng đã đề cập, Excel còn cung cấp nhiều thủ thuật giúp tự động hóa và tối ưu hóa quy trình làm việc.

Data Validation

Data Validation kiểm soát dữ liệu nhập vào. Nó đảm bảo tính nhất quán và chính xác. Các bước thực hiện:

1. Chọn ô hoặc vùng dữ liệu.
2. Vào tab Data và chọn Data Validation.
3. Thiết lập điều kiện (số, danh sách, ngày tháng).
4. Thêm thông báo lỗi khi dữ liệu không hợp lệ.

Biểu đồ nâng cao

Biểu đồ giúp trình bày dữ liệu một cách trực quan. Trong khi đó Excel hỗ trợ nhiều loại biểu đồ. Có thể kể đến là biểu đồ kết hợp so sánh các loại dữ liệu khác nhau. Ngoài ra, biểu đồ động tự cập nhật khi dữ liệu thay đổi cũng đáng để thử. Để tạo biểu đồ động:

1. Tạo Pivot Table với dữ liệu cần hiển thị.
2. Vào tab Insert và chọn loại biểu đồ phù hợp.
3. Chỉnh sửa tiêu đề, màu sắc và định dạng.

Shortcut và Tùy hỉnh Ribbon

Phím tắt giúp thao tác nhanh hơn. Shortcut thông dụng gồm Ctrl + C, Ctrl + V và Ctrl + Z. Sử dụng Alt kết hợp với phím chữ để truy cập các tab nhanh. Để tùy chỉnh Ribbon:

1. Vào File > Options > Customize Ribbon.
2. Thêm các nhóm lệnh thường dùng vào tab riêng.

Case Study 

Báo cáo doanh số tự động

Một công ty bán lẻ cần tổng hợp doanh số hàng tháng. Dữ liệu được thu thập từ nhiều chi nhánh. Phương án giải quyết vấn đề có thể là:

Đầu tiên, hãy thu thập dữ liệu qua Power Query. Dữ liệu được làm sạch và chuẩn hoá tự động. Tiếp theo, sử dụng Pivot Table để tổng hợp doanh số theo chi nhánh, tháng và sản phẩm. Áp dụng Conditional Formatting để làm nổi bật doanh số vượt mốc.

Cuối cùng, tạo biểu đồ động dựa trên dữ liệu Pivot Table. Quy trình này giảm thời gian báo cáo. Nó tăng tính chính xác và cung cấp cái nhìn trực quan.

Quản lý dự án

Phòng dự án cần theo dõi tiến độ công việc. Dữ liệu gồm mã dự án, tiến độ, ngân sách và thời gian hoàn thành. Giải pháp có thể cân nhắc:

1. Tạo bảng dữ liệu chứa các trường thông tin cần thiết.
2. Sử dụng hàm INDEX-MATCH để tự động cập nhật thông tin.
3. Áp dụng Data Validation để kiểm soát dữ liệu nhập.
4. Dùng Macro gửi báo cáo tự động qua email hàng tuần. Phương pháp này giúp quản lý dự án hiệu quả. Nó giảm thiểu sai sót và theo dõi tiến độ chặt chẽ.

Phân tích chi phí

Bộ phận tài chính cần phân tích chi phí hoạt động. Họ phải đưa ra các giải pháp tối ưu ngân sách. Đây có thể là cách thức triển khai:

Bắt đầu với hàm SUMIFS và COUNTIFS để tổng hợp chi phí theo hạng mục. Sau đó, tạo Pivot Table để phân tích các yếu tố ảnh hưởng chi phí.

Tiếp theo, áp dụng Conditional Formatting làm nổi bật chi phí vượt mức. Cuối cùng là dùng Power Pivot xây dựng mô hình dữ liệu phức tạp. Phương pháp này giúp nhà quản lý đưa ra quyết định kịp thời. Nó tối ưu ngân sách và nâng cao hiệu quả hoạt động.

Đúc kết

Trước khi áp dụng thủ thuật nâng cao, hãy nắm vững kiến thức cơ bản. Bạn cần thành thạo nhập liệu và định dạng ô. Điều này giúp tiếp cận các tính năng nâng cao dễ dàng hơn.

Không phải mọi thủ thuật đều phù hợp với công việc của bạn. Hãy đánh giá tính khả thi của từng thủ thuật. Tùy chỉnh thủ thuật sao cho phù hợp nhất. Thử nghiệm từng bước và đo lường hiệu quả.

Cuối cùng, hãy xem việc học Excel như một quá trình liên tục. Sự kiên trì và sáng tạo sẽ mở ra nhiều cơ hội mới. Có lẽ chủ đề này nên thực hiện thành một series, vì quá nhiều thứ để chia sẻ. Chúc bạn thành công trong việc chinh phục Excel. Nếu có suy nghĩ về chủ đề này, hãy để lại bình luận ở phía bên dưới nhé!

Xem thêm: Phím tắt Excel mới nhất 2025