Thủ thuật excel nâng cao

Để xử lý hàng tấn dữ liệu lớn, bạn không thể bỏ qua vai trò quan trọng của Excel trong công việc hàng ngày. Tuy nhiên, cả đối với người mới bắt đầu và người dùng nâng cao, vẫn còn nhiều mẹo và thủ thuật hữu ích chắc chắn bị bỏ qua

Dưới đây là 27 bí mật bảng tính Excel hữu ích có thể bạn chưa biết

1. Một cú nhấp chuột để chọn tất cả

Có thể bạn đã biết cách chọn tất cả bằng cách sử dụng phím tắt Ctrl + A, nhưng ít ai biết rằng chỉ với một lần bấm vào nút ở góc, như trong ảnh chụp màn hình bên dưới, tất cả dữ liệu sẽ được chọn trong vài giây

2. Mở tệp Excel hàng loạt

Thay vì mở từng tệp một khi bạn có nhiều tệp cần xử lý, có một cách tiện dụng để mở tất cả chúng chỉ bằng một cú nhấp chuột. Chọn các tệp bạn muốn mở rồi nhấn phím Enter trên bàn phím, tất cả các tệp sẽ mở đồng thời

3. Chuyển đổi giữa các tệp Excel khác nhau

Khi bạn mở các bảng tính khác nhau, việc chuyển đổi giữa các tệp khác nhau thực sự khó chịu vì đôi khi thao tác trên nhầm trang tính có thể làm hỏng toàn bộ dự án. Sử dụng Ctrl + Tab, bạn có thể tự do chuyển đổi giữa các tệp khác nhau. Chức năng này cũng có thể áp dụng cho các tệp khác như các tab Windows khác nhau trong Firefox khi được mở bằng Windows 7

4. Tạo một menu lối tắt mới

Nói chung, có ba phím tắt trong menu trên cùng, đó là Lưu, Hoàn tác Nhập và Nhập lại. Tuy nhiên, nếu muốn sử dụng thêm các phím tắt như Copy và Cut, bạn có thể thiết lập như sau

File->Options->Quick Access Toolbar, thêm Cut và Copy từ cột bên trái sang bên phải, lưu lại. Bạn sẽ thấy thêm hai phím tắt được thêm vào trong menu trên cùng

5. Thêm một đường chéo vào một ô

Ví dụ: khi tạo danh sách địa chỉ bạn cùng lớp, bạn có thể cần một liên kết chéo trong ô đầu tiên để phân tách các thuộc tính khác nhau của hàng và cột. Làm thế nào để làm cho nó? . Tuy nhiên, nếu bạn nhấp vào More Borders, bạn sẽ nhận được nhiều điều bất ngờ hơn, chẳng hạn như một đường chéo. Nhấp vào nó và lưu—bây giờ bạn có thể thực hiện nó ngay lập tức

⌄ Cuộn xuống để tiếp tục đọc bài viết ⌄

6. Thêm nhiều hơn một hàng hoặc cột mới

Bạn có thể biết cách thêm một hàng hoặc cột mới, nhưng sẽ thực sự lãng phí rất nhiều thời gian nếu bạn cần chèn nhiều hơn một trong số chúng bằng cách lặp lại thao tác này X số lần. Cách tốt nhất là kéo và chọn X hàng hoặc cột (X là hai hoặc nhiều hơn) nếu bạn muốn thêm X hàng hoặc cột bên trên hoặc bên trái. Nhấp chuột phải vào các hàng hoặc cột được đánh dấu và chọn Chèn từ trình đơn thả xuống. Các hàng mới sẽ được chèn phía trên hàng hoặc bên trái cột bạn đã chọn đầu tiên

7. Nhanh chóng di chuyển và sao chép dữ liệu trong ô

Nếu muốn di chuyển 1 cột dữ liệu trong bảng tính, cách nhanh nhất là chọn cột đó và di chuyển con trỏ đến đường viền, sau khi nó chuyển sang biểu tượng mũi tên gạch chéo, hãy kéo để di chuyển cột tùy ý. Nếu bạn muốn sao chép dữ liệu thì sao?

8. Xóa nhanh các ô trống

Một số dữ liệu mặc định sẽ trống, vì nhiều lý do. Nếu bạn cần xóa những ô này để duy trì độ chính xác, đặc biệt là khi tính giá trị trung bình, cách nhanh nhất là lọc ra tất cả các ô trống và xóa chúng bằng một cú nhấp chuột. Chọn cột bạn muốn lọc, vào Data->Filter, sau khi hiện nút xuống, hoàn tác Select All rồi chọn tùy chọn cuối cùng, Blanks. Tất cả các ô trống sẽ hiển thị ngay lập tức. Quay trở lại Trang chủ và nhấp trực tiếp vào Xóa, tất cả chúng sẽ bị xóa

9. Tìm kiếm mơ hồ với Wild Card

Bạn có thể biết cách kích hoạt tìm kiếm nhanh bằng cách sử dụng phím tắt Ctrl + F, nhưng có hai thẻ đại diện chính—Dấu chấm hỏi và Dấu hoa thị—được sử dụng trong bảng tính Excel để kích hoạt tìm kiếm mơ hồ. Điều này được sử dụng khi bạn không chắc chắn về kết quả mục tiêu. Dấu chấm hỏi là viết tắt của một ký tự và Dấu hoa thị đại diện cho một hoặc nhiều ký tự. Nếu bạn cần tìm kiếm Dấu chấm hỏi và Dấu hoa thị làm kết quả mục tiêu thì sao?

10. Tạo một giá trị duy nhất trong một cột

Bạn đã biết chức năng chính của Bộ lọc, nhưng ít người sử dụng Bộ lọc nâng cao, bộ lọc này sẽ được áp dụng nhiều lần khi bạn cần lọc một giá trị duy nhất từ ​​dữ liệu trong một cột. Nhấp để chọn cột và đi đến Dữ liệu->Nâng cao. Một cửa sổ bật lên sẽ hiển thị. Như ảnh chụp màn hình hiển thị, hãy nhấp vào Sao chép sang vị trí khác, vị trí này phải phù hợp với khu vực hình chữ nhật màu đỏ thứ hai. Sau đó chỉ định vị trí mục tiêu bằng cách nhập giá trị hoặc nhấp vào nút chọn khu vực. Trong ví dụ này, độ tuổi duy nhất có thể được tạo từ Cột C và hiển thị trong Cột E. Đừng quên chọn Chỉ bản ghi duy nhất, sau đó nhấp vào OK. Giá trị duy nhất hiển thị trong cột E có thể là độ tương phản của dữ liệu gốc trong C, đó là lý do tại sao nên sao chép sang vị trí khác

11. Hạn chế đầu vào với chức năng xác thực dữ liệu

Để duy trì tính hợp lệ của dữ liệu, đôi khi bạn cần hạn chế giá trị đầu vào và đưa ra một số mẹo cho các bước tiếp theo. Ví dụ: tuổi trong trang tính này phải là số nguyên và tất cả những người tham gia khảo sát này phải từ 18 đến 60 tuổi. Để đảm bảo rằng dữ liệu bên ngoài độ tuổi này không được nhập, hãy đi tới Dữ liệu->Xác thực dữ liệu->Cài đặt, nhập điều kiện và chuyển sang Thông báo đầu vào để đưa ra lời nhắc như, “Vui lòng nhập tuổi của bạn với số nguyên, nên nằm trong khoảng . ” Người dùng sẽ nhận được thông báo này khi treo con trỏ tại khu vực này và nhận được thông báo cảnh báo nếu thông tin nhập vào không hợp lệ

12. Điều hướng nhanh bằng Ctrl + Nút mũi tên

Khi bạn bấm Ctrl + bất kỳ nút mũi tên nào trên bàn phím, bạn có thể nhảy tới mép của trang tính theo các hướng khác nhau. Nếu bạn muốn chuyển đến dòng dưới cùng của dữ liệu, chỉ cần cố gắng nhấp Ctrl + nút hướng xuống

⌄ Cuộn xuống để tiếp tục đọc bài viết ⌄

13. Chuyển dữ liệu từ hàng sang cột

Bạn sẽ sử dụng tính năng này nếu muốn chuyển đổi dữ liệu để hiển thị tốt hơn; . Đây là cách. sao chép khu vực bạn muốn chuyển đổi, di chuyển con trỏ đến vị trí trống khác. Vào Home->Paste->Transpose, xin lưu ý rằng chức năng này sẽ không kích hoạt cho đến khi bạn sao chép dữ liệu trước

14. Ẩn dữ liệu triệt để

Hầu như người dùng nào cũng biết cách ẩn dữ liệu bằng cách click chuột phải chọn chức năng Ẩn, tuy nhiên điều này có thể dễ dàng nhận thấy nếu chỉ có một chút dữ liệu. Cách tốt nhất và dễ dàng nhất để giấu dữ liệu triệt để là sử dụng chức năng Format Cells. Chọn khu vực và đi tới Trang chủ->Phông chữ->Mở ô định dạng->Tab số->Tùy chỉnh->Loại ;;;

15. Soạn văn bản với &

Công thức phức tạp là không cần thiết, miễn là bạn biết cách sử dụng &. Bạn có thể soạn bất kỳ văn bản nào một cách tự do với biểu tượng này. Dưới đây tôi có bốn cột với các văn bản khác nhau, nhưng nếu tôi muốn soạn chúng thành một giá trị trong một ô thì sao? . Nhấp vào Nhập. tất cả các văn bản trong A2, B2, C2 và D2 ​​sẽ được kết hợp với nhau để trở thành LizaUSA25@ trong F2

16. Chuyển đổi trường hợp của văn bản

Với tất cả các thủ thuật được chia sẻ ở đây, tôi đã cố gắng hết sức để tránh các công thức phức tạp. Nhưng vẫn có một số công thức đơn giản và dễ sử dụng để chỉ cho bạn, như UPPER, LOWER và PROPER, có thể chuyển đổi văn bản cho các mục đích khác nhau. UPPER sẽ viết hoa tất cả các ký tự, LOWER có thể thay đổi văn bản thành tất cả chữ thường và PROPER sẽ chỉ viết hoa ký tự đầu tiên của một từ

17. Giá trị đầu vào Bắt đầu bằng 0

Khi một giá trị đầu vào bắt đầu bằng số không, Excel sẽ xóa số không theo mặc định. Thay vì đặt lại Định dạng ô, vấn đề này có thể được giải quyết dễ dàng bằng cách thêm một dấu ngoặc kép trước số 0 đầu tiên, như được hiển thị

18. Tăng tốc độ nhập thuật ngữ phức tạp với AutoCorrect

Nếu bạn cần lặp lại cùng một giá trị và nhập liệu phức tạp, cách tốt nhất là sử dụng chức năng AutoCorrect, chức năng này sẽ thay thế văn bản của bạn bằng văn bản chính xác. Lấy ví dụ tên tôi, Liza Brown, có thể thay thế bằng LZ. Do đó, mỗi khi tôi nhập LZ, nó có thể tự động sửa thành Liza Brown. Vào File->Options->Proofing->AutoCorrect Options và nhập Thay thế văn bản bằng văn bản chính xác trong vùng hình chữ nhật màu đỏ, như bên dưới

⌄ Cuộn xuống để tiếp tục đọc bài viết ⌄⌄ Cuộn xuống để tiếp tục đọc bài viết ⌄

19. Một cú nhấp chuột để có thêm trạng thái

Hầu hết người dùng đều biết cách kiểm tra trạng thái dữ liệu ở cuối trang tính Excel, chẳng hạn như Giá trị trung bình và Tổng. Tuy nhiên, bạn có biết bạn có thể di chuyển con trỏ đến tab dưới cùng và nhấp chuột phải để có thêm trạng thái, như hình bên dưới không?

20. Đổi tên một trang tính bằng cách nhấp đúp chuột

Có nhiều cách để đổi tên trang tính và hầu hết người dùng sẽ nhấp chuột phải để chọn Đổi tên, điều này thực sự lãng phí rất nhiều thời gian. Cách tốt nhất là chỉ cần nhấp hai lần, sau đó bạn có thể đổi tên trực tiếp

21. Điều khiển con trỏ ô khi nhập dữ liệu

Excel tự động di chuyển con trỏ ô xuống ô tiếp theo khi bạn nhấn Enter sau khi nhập dữ liệu vào một ô. Điều này gây mất tập trung, đặc biệt nếu bạn muốn thêm dữ liệu vào một hàng thay vì một cột. Để thay đổi cài đặt, hãy sử dụng hộp thoại tùy chọn Excel

• Lựa chọn. Tệp -> Tùy chọn Excel -> Nâng cao -> Tùy chọn chỉnh sửa

• Bật hộp kiểm “Sau khi nhấn Enter, hãy di chuyển lựa chọn” và trình đơn thả xuống tương ứng, kiểm soát hành động của con trỏ ô trong khi Nhập dữ liệu
• Mẹo. Khi tắt hộp kiểm, các phím mũi tên có thể được sử dụng để điều khiển hướng của con trỏ. Ví dụ: nếu bạn đang nhập dữ liệu trong một hàng, hãy nhấn phím mũi tên phải thay vì Enter để di chuyển linh hoạt đến ô tiếp theo trong cùng một hàng

22. Lưu trữ dữ liệu trong Bảng dữ liệu

Bảng tính Excel là lưới các hàng và cột. Trong khi thêm dữ liệu vào Excel, bằng trực giác, người ta không sử dụng Bảng dữ liệu. Tuy nhiên, Bảng dữ liệu Excel rất đơn giản và mạnh mẽ. Chúng giúp dễ dàng thêm nhiều hàng và cột hơn mà không phải lo lắng về việc cập nhật tham chiếu công thức, tùy chọn định dạng, cài đặt bộ lọc, v.v. Bảng dữ liệu chăm sóc mọi thứ

Để thêm Bảng dữ liệu Excel, hãy chọn Phạm vi ô bao gồm dữ liệu và nhấp vào nút Bảng nằm trong Dải băng Chèn

Thủ thuật bảng dữ liệu

• Theo mặc định, Bảng dữ liệu đi kèm với bộ Bộ lọc dữ liệu và Tùy chọn sắp xếp riêng. Không có Bảng dữ liệu, Excel chỉ cho phép một bộ lọc trên mỗi trang tính. Tuy nhiên, nếu một trang tính có nhiều bảng, thì mỗi bảng sẽ có các tùy chọn sắp xếp và bộ lọc riêng

• Công thức bên trong bảng dữ liệu có thể sử dụng tên tiêu đề có ý nghĩa thay vì sử dụng tham chiếu ô. Hơn nữa, khi thêm hoặc bớt hàng, người ta không cần lo lắng về việc cập nhật các tham chiếu

• Với các tham chiếu có cấu trúc, việc tính toán các cột bên trong trở nên rất dễ dàng. Ngoài ra, excel sẽ tự động điền công thức vào bên trong các ô còn lại trong cột

• Bảng dữ liệu có thể được định dạng bằng một cú nhấp chuột bên trong Công cụ bảng

• Dữ liệu trùng lặp có thể bị xóa khỏi Bảng dữ liệu chỉ bằng một cú nhấp chuột bên trong Công cụ bảng

• Bảng dữ liệu có thể được chuyển đổi trở lại Phạm vi chỉ bằng một cú nhấp chuột bên trong Công cụ bảng

• Bảng dữ liệu có thể được xuất sang trang web sharepoint chỉ bằng một cú nhấp chuột bên trong Công cụ bảng

• Bảng dữ liệu có thể được tóm tắt bằng cách thêm một hàng tổng chỉ bằng một cú nhấp chuột bên trong Công cụ bảng

23. Biểu mẫu nhập dữ liệu

Khi các Bảng Dữ liệu được thiết lập trong một trang tính, Biểu mẫu Dữ liệu Excel cung cấp khả năng nhập dữ liệu vào các bảng đó bằng cách sử dụng Hộp thoại. Để có được Nút biểu mẫu trên Thanh công cụ truy cập nhanh, hãy làm theo các bước dưới đây

• Nhấp vào Tùy chỉnh Thanh công cụ truy cập nhanh
• Chọn Thêm lệnh từ danh sách
• Chọn tất cả các lệnh từ danh sách thả xuống
• Bạn sẽ tìm thấy Biểu mẫu trong danh sách Tất cả các lệnh
• Nhấp vào Thêm và nó sẽ được thêm vào Thanh công cụ truy cập nhanh, sau khi bạn nhấp vào OK

Để truy cập hộp thoại Biểu mẫu, chỉ cần chọn Phạm vi Bảng Dữ liệu trong trang tính và Nhấp vào Biểu mẫu từ Thanh công cụ Truy cập Nhanh

Các tùy chọn trên Hộp thoại như sau

• Mới. Thêm một bản ghi mới vào Bảng dữ liệu
• Xóa bỏ. Xóa bản ghi được hiển thị khỏi Bảng dữ liệu
• Khôi phục. Khôi phục các thay đổi được thực hiện trong Mục nhập bảng dữ liệu
• Tìm Trước/Tìm Tiếp. Tìm các bản ghi như được chỉ định trong Trường tiêu chí
• Tiêu chuẩn. Truy xuất dữ liệu đáp ứng một tiêu chí đã đặt
• Đóng. Đóng hộp thoại

24. Danh sách xác thực dữ liệu

Tạo danh sách thả xuống và bắt buộc nhập dữ liệu bằng cách sử dụng menu thả xuống, đảm bảo rằng tính nhất quán của dữ liệu luôn được duy trì trong trang tính của bạn. Để tạo một danh sách thả xuống

• Nhập danh sách các mục trong một phạm vi
• Chọn ô sẽ chứa danh sách thả xuống
• Chọn Dữ liệu -> Công cụ dữ liệu -> Xác thực dữ liệu
• Trong hộp thoại Xác thực dữ liệu, bấm vào tab Cài đặt
• Trong danh sách thả xuống Cho phép, chọn Danh sách
• Trong hộp Nguồn, chỉ định phạm vi chứa các mục
• Đảm bảo rằng tùy chọn thả xuống Trong ô được chọn và nhấp vào OK

Mẹo. Nếu danh sách thả xuống ngắn, thì bạn có thể nhập các giá trị trực tiếp vào Trường nguồn được phân tách bằng dấu phẩy

25. AutoCorrect để nhập dữ liệu tay ngắn

AutoCorrect, theo mặc định, có nghĩa là thực hiện những việc hữu ích như viết hoa chữ cái đầu tiên của câu hoặc sửa việc vô tình sử dụng phím caps lock. Ngoài ra, người ta có thể tùy chỉnh AutoCorrect để tạo các phím tắt cho các từ hoặc cụm từ thường được sử dụng. Ví dụ, dưới đây tôi sử dụng viết tắt. “gro” cho Cửa hàng tạp hóa, “subsc” cho Đăng ký, v.v.

Để đến Tùy chọn Tự động Sửa,
Nhấp vào Tệp -> Tùy chọn Excel -> Soát lỗi -> Tùy chọn Tự động Sửa.
Hoặc, phím tắt trên bàn phím là. ALT+T+A.

Mẹo và thủ thuật tự động sửa lỗi

• Để Thêm Lối tắt Tự động Sửa, hãy nhập văn bản lối tắt vào Trường Thay thế và nhập văn bản mà nó sẽ mở rộng thành Trường Có, sau đó bấm Thêm
• Để xóa phím tắt Tự động sửa lỗi, hãy tìm phím tắt trong danh sách Tự động sửa lỗi và nhấp vào Xóa
• Để ghi đè AutoCorrect Nhấn CTRL+Z trong khi nhập thông tin vào ô
• Để chia sẻ các mục nhập Tự động Sửa với bạn bè của bạn hoặc giữa các máy, chỉ cần xác định vị trí *. acl trên ổ cứng của bạn và sao chép nó vào vị trí tương ứng trên máy khác
• Excel chuyển đổi các mục nhập địa chỉ email hoặc URL web thành siêu liên kết bằng tính năng Tự động sửa lỗi. Để ghi đè một siêu kết nối tự động, chỉ cần nhấp vào Hoàn tác (hoặc nhấn Ctrl+Z) sau khi bạn nhập văn bản. Siêu liên kết biến mất, nhưng văn bản bạn đã nhập vẫn còn nguyên. Để tắt hoàn toàn tính năng này, bạn vào AutoCorrect Options -> Click vào Tab. Tự động định dạng khi bạn nhập -> Tắt hộp kiểm. “Đường dẫn Internet và mạng với siêu liên kết”

26. AutoComplete để tự động nhập dữ liệu

Tính năng AutoComplete của Excel giúp dễ dàng nhập cùng một văn bản vào nhiều ô. Chỉ cần nhập một vài chữ cái đầu tiên của mục nhập văn bản vào một ô và Excel sẽ tự động hoàn thành mục nhập đó, dựa trên các mục nhập khác mà bạn đã thực hiện trong cột. Nó không chỉ giúp giảm việc gõ mà còn duy trì tính nhất quán trong dữ liệu được nhập

Mẹo và thủ thuật tự động hoàn thành

• Tự động hoàn thành chỉ hoạt động trên các cột ô liền kề, vì vậy đừng để trống các hàng để tận dụng hết tiềm năng của Tự động hoàn thành
• Tự động hoàn thành tự động thay đổi trường hợp của các chữ cái
• Nếu cột chứa nhiều mục khớp với một vài ký tự đầu tiên, thì Tự động hoàn thành sẽ không bắt đầu cho đến khi mục nhập của bạn khớp nhanh với một trong số các ký tự đó
• Bạn có thể truy cập phiên bản Tự động hoàn thành do chuột điều khiển bằng cách nhấp chuột phải vào ô và chọn tùy chọn “Chọn từ danh sách thả xuống”
• Bạn có thể truy cập cùng một danh sách thả xuống từ bàn phím bằng cách nhấn. Ca+F10

27. Đọc lại bằng âm thanh

Excel có khả năng chuyển văn bản thành giọng nói tiện dụng, có khả năng đọc nội dung của ô khi bạn nhập nó. Ngoài ra, nó cũng có thể đọc lại một phạm vi ô cụ thể

Để bật tính năng này trong thanh Công cụ truy cập nhanh

• Nhấp vào Tùy chỉnh Thanh công cụ truy cập nhanh
• Chọn Thêm lệnh từ danh sách
• Chọn tất cả các lệnh từ danh sách thả xuống
• Chọn tất cả các Ô bắt đầu bằng Nói Ô và Thêm chúng vào Thanh công cụ Truy cập Nhanh, bằng cách nhấp vào Thêm

Để đọc một phạm vi ô, hãy chọn phạm vi ô và nhấp vào nút Đọc ô

Bạn có thể thay đổi hướng bằng cách nhấp vào “Nói ô trên cột” hoặc “Nói ô trên hàng”

Nếu bạn muốn excel đọc nội dung của ô khi bạn nhập, hãy nhấp vào “Speak Cells on Enter”

Kỹ thuật Excel nâng cao là gì?

Kỹ năng Excel cao cấp nhất là gì?

Một số thủ thuật Microsoft Excel thú vị là gì?

Điều khó khăn nhất trong Excel là gì?