Top 5 thủ thuật Microsoft Excel cực hay mà bạn không nên bỏ qua!
Với bất kỳ phần mềm nào, bạn càng sử dụng nhiều, bạn càng khám phá ra nhiều mẹo và phím tắt, và điều này đặc biệt đúng đối với Microsoft Excel. Hơn nữa, khi chương trình phát triển, bạn lại bổ sung thêm các thủ thuật mới vào bộ công cụ của mình. Dưới đây là một số thủ thuật yêu thích của tôi – cả cũ và mới – mà tôi không thể sống thiếu.
Xem thêm: Hướng dẫn cách sử dụng hàm UNIQUE trong Microsoft Excel
1. Dán mảng động thành giá trị tĩnh
Một số hàm trong Excel, như FILTER, SEQUENCE, và RANDARRAY, tạo ra các mảng động (dynamic arrays). Kết quả của các mảng này sẽ tự động mở rộng từ ô bạn nhập công thức sang các ô liền kề – hành vi này được gọi là tràn (spilling). Mảng động thay thế cho các công thức mảng cũ ít linh hoạt hơn là Ctrl+Shift+Enter (CSE). Bạn có thể nhận biết một mảng là mảng động khi có một đường viền màu xanh lam bao quanh các ô chứa kết quả bị tràn.
Lưu ý: Bạn cần sử dụng Excel cho web hoặc ứng dụng Excel Microsoft 365 trên máy tính để bàn để tận dụng các tính năng Excel mới nhất, bao gồm cả các hàm mảng động mới.
Tuy nhiên, đôi khi, bạn có thể muốn cố định các giá trị được hiển thị trong mảng động thay vì để chúng liên tục tính toán lại và cập nhật. Ví dụ, bạn đã sử dụng các hàm DATE và SEQUENCE để tạo một mảng động chứa các ngày, dựa trên các tham số trong các ô C9, C10 và C11.
Giờ đây, bạn không muốn thay đổi thêm các ngày này nữa, vì vậy bạn muốn cố định chúng như hiện tại. Để làm điều này, hãy chọn các ô và nhấn tổ hợp phím Ctrl+C để sao chép chúng.
Sau đó, trong khi các ô đó vẫn được chọn, nhấn tổ hợp phím Ctrl+Shift+V để chỉ dán giá trị (paste values only). Bạn biết thao tác này đã hoạt động chính xác vì mặc dù các ngày vẫn xuất hiện như trước, nhưng khi bạn chọn một trong các ô, bạn sẽ không thấy đường viền màu xanh lam, và thanh công thức (formula bar) sẽ hiển thị một giá trị duy nhất thay vì một công thức.
Đây là một thủ thuật đặc biệt hữu ích khi bạn muốn định dạng dữ liệu dưới dạng Bảng Excel (Excel table), vì mảng động không thể tràn vào các cột của bảng (dynamic arrays cannot spill into table columns).
Cuối cùng, bạn có thể xóa các ô chứa các tham số (C9, C10, C11) vì các ngày đã được cố định và không còn sử dụng chúng nữa.
2. Chọn tham chiếu ô bằng chuột
Tôi luôn tìm kiếm các cách để tăng tốc quy trình làm việc trong Excel (speed up my Excel workflow), và mặc dù đây là một thủ thuật cơ bản, nhưng tôi sử dụng nó hàng ngày.
Hãy tưởng tượng bạn đang làm việc trên bảng tính này và muốn điền vào cột tổng bằng cách tính tổng điểm của mỗi đội qua sáu tuần.
Bắt đầu từ ô E2, công thức bạn muốn có là:
=SUM(B2:G2)
Tuy nhiên, việc gõ thủ công các tham chiếu ô có thể tốn thời gian và dễ dẫn đến lỗi. Một phương pháp nhanh hơn và đáng tin cậy hơn là sử dụng chuột.
Sau khi gõ:
=SUM(
vào ô H2, bạn hãy chọn ô B2, giữ chuột và kéo con trỏ đến ô G2.
Cuối cùng, gõ dấu ngoặc đóng ), nhấn tổ hợp phím Ctrl+Enter, và nhấp đúp vào ô vuông điền (fill handle) ở góc dưới cùng bên phải để áp dụng công thức cho các ô còn lại trong cột H.
Bạn cũng có thể sử dụng các phím mũi tên để thêm tham chiếu ô vào công thức của mình. Sau khi gõ dấu bằng, tên hàm và dấu ngoặc mở, hãy nhấn một phím mũi tên để kích hoạt chế độ con trỏ (Point mode), cho phép bạn chọn các ô khi xây dựng công thức.
Thủ thuật này đặc biệt hữu ích khi bạn đang tham chiếu tiêu đề cột trong các Bảng Excel được định dạng (formatted Excel tables). Điều này là do chúng đòi hỏi một cú pháp rất cụ thể mà nếu gõ thủ công sẽ mất quá nhiều thời gian.
3. Sao chép quy tắc xác thực dữ liệu ngay lập tức
Trong bảng tính Excel này, giả sử bạn đã tạo một quy tắc xác thực dữ liệu (data validation rule) tùy chỉnh bằng cách sử dụng một công thức phức tạp để đảm bảo rằng mã công ty được nhập vào cột C phải chứa một chữ cái, theo sau là bốn chữ số.
Bây giờ, giả sử bạn muốn sử dụng quy tắc xác thực dữ liệu tương tự đó trong một trang tính khác.
Tuy nhiên, việc gõ lại quy tắc sẽ tốn quá nhiều thời gian và bạn không muốn sao chép và dán các ô vì chúng đã được định dạng khác nhau.
May mắn thay, có một cách để sao chép các quy tắc xác thực dữ liệu từ một ô hoặc một phạm vi này sang một ô hoặc một phạm vi khác mà không ghi đè lên các công thức hiện có, sửa đổi bất kỳ dữ liệu nào hoặc thay đổi định dạng.
Đầu tiên, hãy chọn và sao chép một ô hoặc tất cả các ô đã chứa quy tắc xác thực dữ liệu.
Sau đó, chọn ô hoặc các ô nơi bạn muốn quy tắc được sao chép và nhấn tổ hợp phím Ctrl+Alt+V để mở hộp thoại Paste Special (Dán Đặc biệt). Hoặc, nhấp vào mũi tên xuống của nút “Paste” (Dán) trong tab Home (Trang chủ) trên thanh ribbon, và chọn “Paste Special…” (Dán Đặc biệt…).
Tiếp theo, đánh dấu chọn nút radio “Validation” (Xác thực), và nhấp vào “OK”.
Và thế là xong! Các quy tắc xác thực dữ liệu đã được sao chép, giữ nguyên mọi thứ khác. Bạn có thể kiểm tra xem thao tác này có hoạt động không bằng cách nhập một số mục nhập hợp lệ và không hợp lệ.
4. Loại bỏ khoảng trắng đầu hoặc cuối khỏi một phạm vi dữ liệu
Việc loại bỏ các khoảng trắng thừa (blank cells) ở đầu hoặc cuối bằng công thức Excel là một cách tuyệt vời để dữ liệu tự động co lại và mở rộng, làm cho các công thức trở nên động (dynamic) và sạch sẽ hơn. Thủ thuật này đặc biệt hữu ích cho các công thức mảng động (dynamic array formulas), vì chúng không tương thích với các Bảng Excel.
Trong bảng tính Excel này, giả sử bạn đã sử dụng hàm FILTER để trích xuất tất cả những cầu thủ có hơn 50 lần ra sân, cùng với đội bóng mà họ đang thi đấu.
Khi một cầu thủ vượt quá 50 lần ra sân, họ sẽ được tặng một chiếc áo đấu với màu sắc của đội. Mục tiêu của bạn là theo dõi xem cầu thủ nào có chiếc áo đấu nào. Để làm điều này, trong ô H2, bạn sử dụng công thức XLOOKUP mảng động:
=XLOOKUP(G2:G100,$J$2:$J$7,$K$2:$K$7)
Trong đó:
G2:G100là các ô chứa đội của cầu thủ (đã thêm các ô trống ở cuối để cho phép dữ liệu tăng trưởng).$J$2:$J$7là mảng tìm kiếm (lookup array).$K$2:$K$7là mảng trả về (return array).
Tuy nhiên, khi bạn nhấn Enter, lỗi #N/A xuất hiện từ ô H12 trở xuống vì không có đội nào trong các ô tương ứng ở cột G.
Một cách để khắc phục điều này là nhập hai dấu ngoặc kép ("") vào đối số if_not_found của hàm XLOOKUP, điều này sẽ thay thế lỗi bằng một ô trống. Tuy nhiên, công thức XLOOKUP vẫn trả về một mảng động mở rộng vượt ra ngoài phạm vi dữ liệu hiện tại, điều này không chỉ trông kém gọn gàng mà còn lãng phí bộ nhớ (wastes memory).
Thay vào đó, việc thêm một dấu chấm được đặt cẩn thận vào công thức là giải pháp tối ưu. Trong ngữ cảnh này, dấu chấm được gọi là toán tử tham chiếu cắt xén (trim ref operator).
- Thêm dấu chấm trước dấu hai chấm sẽ cắt xén các khoảng trắng ở đầu (leading blanks).
- Thêm dấu chấm sau dấu hai chấm sẽ cắt xén các khoảng trắng ở cuối (trailing blanks).
- Bạn có thể sử dụng cả hai để cắt xén đồng thời khoảng trắng ở cả đầu và cuối.
Lưu ý: Toán tử tham chiếu cắt xén (trim ref operators) chỉ khả dụng cho những người dùng Excel cho Microsoft 365, Excel cho web, và các ứng dụng Excel di động và máy tính bảng.
Trong trường hợp này, toán tử tham chiếu cắt xén được chèn trước dấu hai chấm trong công thức, có nghĩa là mảng động sẽ ngừng tại ô H11 (nơi dữ liệu thực tế kết thúc):
=XLOOKUP(G2:.G100,$J$2:$J$7,$K$2:$K$7)
Tuy nhiên, nếu một cầu thủ khác vượt qua 50 lần ra sân, mảng động ở cột F và G do hàm FILTER tạo ra sẽ mở rộng thêm một hàng, và mảng động ở cột H do công thức XLOOKUP được cắt xén cũng sẽ tự động mở rộng tương ứng.
5. Lặp lại tiêu đề cột khi in
Hãy tưởng tượng bạn đã tạo trang tính Excel này, chứa một bảng với ID, giới tính, quốc gia và phòng ban của 130 cá nhân.
Giờ đây, bạn muốn in bảng để dễ tham chiếu, nhưng khi in, bạn nhận thấy rằng tiêu đề cột chỉ được in trên trang đầu tiên, chứ không phải các trang tiếp theo. Kết quả là, khi bạn xem qua trang đầu tiên, bản in có thể gây nhầm lẫn, đặc biệt đối với những người không biết mỗi cột đại diện cho điều gì. Để khắc phục điều này, bạn có thể lặp lại tiêu đề cột trong bản in.
Truy cập tab “Page Layout” (Bố cục Trang) trên thanh ribbon và nhấp vào “Print Titles” (Thiết lập Tiêu đề In).
Lưu ý: Nút Print Titles sẽ bị làm mờ nếu bạn đang ở chế độ Chỉnh sửa (Edit mode), nếu một biểu đồ được chọn, hoặc nếu chưa cài đặt máy in trên thiết bị.
Tiếp theo, mở tab “Sheet” (Trang tính) và kích hoạt con trỏ trong trường được gọi là “Rows To Repeat At Top” (Các hàng lặp lại ở trên cùng).
Sau đó, nhấp vào bất kỳ tiêu đề cột nào trong bảng của bạn, và Excel sẽ tự động nhập tham chiếu đến toàn bộ hàng đó, nghĩa là mọi thứ trên hàng được chọn sẽ được coi là tiêu đề để lặp lại trên mỗi trang in.
Lưu ý: Các trường Rows To Repeat At Top và Columns To Repeat At Left sẽ bị làm mờ nếu bạn đã chọn nhiều hơn một trang tính.
Sau khi nhấp vào “OK” để đóng hộp thoại, nhấn tổ hợp phím Ctrl+P (hoặc nhấp vào File > Print) để mở bản xem trước khi in (print preview). Bây giờ, khi bạn cuộn qua bản xem trước, bạn sẽ thấy các tiêu đề cột được lặp lại trên mỗi trang.
Một cách để cải thiện sự nhanh nhẹn và tăng hiệu suất làm việc trong Microsoft Excel là sử dụng phím tắt bất cứ khi nào có thể, vì làm như vậy bạn sẽ không phải chuyển đổi giữa bàn phím và chuột thường xuyên. Tuy nhiên, vì Excel có rất nhiều phím tắt, bạn có thể in một bảng hướng dẫn phím tắt và ghim nó lên bảng thông báo để dễ dàng sử dụng.
6. Kết luận
Top 5 thủ thuật Microsoft Excel mà chúng tôi chia sẻ không chỉ giúp bạn tiết kiệm thời gian mà còn nâng tầm kỹ năng làm việc với bảng tính, từ quản lý dữ liệu đến tạo báo cáo chuyên nghiệp. Những mẹo này là chìa khóa để bạn khai thác tối đa tiềm năng của Excel, biến công việc phức tạp trở nên dễ dàng và hiệu quả hơn bao giờ hết. Hãy áp dụng ngay để trở thành bậc thầy Excel trong công việc và học tập!
Nếu bạn cần thêm tài liệu học Excel hoặc đang tìm kiếm các thiết bị công nghệ chất lượng để hỗ trợ công việc văn phòng, hãy ghé thăm COHOTECH – cửa hàng công nghệ uy tín với đa dạng sản phẩm hiện đại và dịch vụ tư vấn tận tâm, luôn sẵn sàng đồng hành cùng bạn trong mọi nhu cầu công nghệ.
Hãy để lại bình luận bên dưới để chia sẻ trải nghiệm của bạn với các thủ thuật Excel hoặc đề xuất thêm những mẹo hay mà bạn yêu thích. Đừng quên chia sẻ bài viết này với bạn bè và đồng nghiệp để cùng khám phá những kỹ năng Excel đỉnh cao! Cảm ơn bạn đã đọc và ủng hộ!
214 Tân Phước, Phường 6, Quận 10, TPHCM