Trang chủ Tin học căn bản 15 mẹo và thủ thuật Excel hay mà không phải ai cũng biết

15 mẹo và thủ thuật Excel hay mà không phải ai cũng biết

Tại chuyên mục: Tin học căn bản

15 mẹo và thủ thuật hàng đầu cho Excel. Sử dụng các mẹo và thủ thuật này để cải thiện hiệu quả sử dụng Excel của bạn. Tôi chắc chắn rằng bạn sẽ phát hiện ra một số mà bạn chưa từng thấy trước đây, và một số khác mà bạn đã sử dụng không đúng cách. Hy vọng bài này sẽ giúp ích cho các bạn trong các lĩnh vực.

1. Công cụ phân tích nhanh (trl + Q)


Là một công cụ giúp bạn có thể tạo nhanh bảng tính cộng, hay dạng biểu đồ, dạng bảng, đặc biệt là tô màu những ô trùng nhau. Trong Quick Analysis Tool của excel sẽ có các mục sau:
- Formatting bao gồm Data Bars, Color Scale, Icon Set, Greater Than, Top 10% và Clear Format.
- Charts bao gồm Clustered Column, Stacked column, Scatter, Clustered bar, Stacked Bar.
- Totals bao gồm Sum ngang, Average, Count, % Total, Running total, Sum dọc.

Các bạn chỉ cần bôi den vùng chọn và sẽ thấy một biểu tượng như hình dưới và chọn định dạng mà bạn muốn.



Mình sẽ ví dụ một số tính năng cho các bạn xem bằng hình ảnh ở dưới\

Dạng biểu đồ Data Bar

Bôi màu những ô trùng Greater Than

Dạng biểu đồ Chart


2. Công cụ Filter trong Excel


Đây là công cụ có chức năng lọc lấy dòng mà có số ta muốn lấy, để hiệu cụ thể hơn vấn đề này các bạn xem hình ảnh ví dụ sau.

chọn một ô ở khu vực cần hiện nút Filter sau đó ở thành Bar bạn chọn Data -> Filter

Kết quả hiện nút Filter sau khi chọn

Chọn lọc dòng muốn lấy

Ví dụ mình lấy dòng là 3 thì kết quả sẽ như hình sau

Đây là kết quả sau khi mình chọn

Quá dễ phải không nào, mình làm chi tiết vì sẽ có nhiều người chưa biết, nếu bạn nào biết rồi hãy thử kéo xuống dưới xem có cái gì bạn chưa biết không nha.

3. Danh sách Drop-Down List

Tạo danh sách sổ xuống với một chuỗi hoặc 1 số tạo sẵn giúp bạn dễ dàng có được danh sách menu.
sau đây mình hướng dẫn cụ thể bằng ảnh để các bạn thấy

Ở hình trên mình tạo sẵn một danh sách menu sẵn bên phải để tí đối chiếu sang cột 1

bạn chọn list và tiếp theo chọn biểu tượng mũi tên hướng lên để chọn cột menu đã tạo sẵn, hãy nhìn theo hình sau

sau khi chọn xong thì bạn click vào mũi tên 2 ,sau đó nhấn ok và kết quả để ra hình như dưới đây

4. Tự động điều chỉnh chiều rộng của cột

Khi bạn tạo các ô khi quá tay lại không thể bằng nhau, nhiều có quá nhiều dãy cột to nhỏ thì chỉnh thủ công căn kéo bằng tay thì rất lâu, vậy để làm được như vậy các bạn chỉ cần rê chuột vào mép cột click đôi chuột nhanh vào là tự động nó sẽ căn chỉnh độ rộng của cột.

Hình ảnh ví dụ lúc cột quá rộng không đều nhau

 

Và sau khi thao tác thì sẽ như này
 

5. Đổi vị trí (Transpose trong excel)

Khi bạn đang có dữ liệu dạng ba cột, giờ muốn chuyển nó thành 4 hoặc 10 cột thì làm như nào, chỉ đơn giản là các bạn bôi chọn vùng dữ liệu đấy và chỉ chuột sang vùng khác và click chuột phải, chọn paste Special và lúc này 1 bảng hiện ra chọn Transpose là được

Mời các bạn xem hình ảnh sau để dễ hình dung hơn

 

6. Gỡ bỏ dòng trung lặp trong excel

Bạn có một lượng dữ liệu lớn, trong khi đó nó lại trùng nhau khá nhiều, không thể ngồi do từng dòng được, rất may là excel có chức năng Remove Dubplicates, giúp bạn thao tác nhanh chóng.
Cách dùng là bạn chọn vùng dữ liệu cần kiểm tra trùng và gỡ bỏ, rồi chọn cột cần kiểm tra Dubplicates và ok là xong.

Cụ thể hơn các bạn nhiều hình ảnh ví dụ sau nha.

ở đây mình chọn check trùng dòng ở 2 cột đó là họ tên và Ngày sinh

kết quả báo là có 1 giá trị trùng đã được gỡ bỏ

7. Sử dụng Goal Seek để tìm ra mục tiêu cần đạt được.

Ví dụ bạn cần vay ngân hàng số tiền 25 triệu để mua một chiếc máy tính, trả góp trong 5 tháng với lãi suất 7,8%, như vậy mỗi tháng trả 5,097,912đ , nhưng bạn chỉ có thể trả góp 3 triệu 1 tháng, vậy để biết cụ thể mỗi tháng bạn phải trả bao nhiêu và số tháng cần trả hết.
Vậy bạn hãy xem hình ảnh ví dụ sau với cách sử dụng Goal Seek nhé.

khi chưa dùng Goal Seek

Cách dùng Goal Seek  ở thành menu bar chọn Data bạn sẽ thấy What-if Analysis và click vào chọn Goal Seek

Kết quả cho ra là bạn phải trả trong 8,6 tháng và mỗi tháng trả 3 triệu

8. Hàm VLOOKUP trong excel

Cụ thể là VLOOKUP có thể dò tìm dữ liệu ở cột đã định nghĩa để ra đối chiếu với cột đã có sẵn, ví dụ như bạn có danh sách 100 bạn học sinh, mỗi học sinh có điểm trung bình khác nhau, vì thế không thể ngồi gõ tay từng học sinh một.

Bây giờ ta phải định nghĩa bảng xếp hạng dựa vào điểm trung bình của mỗi học sinh, ví dụ như điểm trung bình là 5 là trung bình, 7 là trung bình khá, 8 khá, 9 là giỏi, 10 siêu giỏi chẳng hạn.

Để hiểu rõ hơn các bạn xem hình ảnh dưới đây

lookup_value mình chọn F6 (tức cột F hàng 6)
table_array mình chọn cả mảng ở cột A và B (A20 đến B24)
col_index_num mình để là 2 vì ở cột B là cột thứ 2 đã có định nghĩa; range_lookup mình để là 0 vì là tìm chính sác

Lưu ý: Khi sử dụng hàm VLOOKUP trong Excel để thực hiện tra cứu gần đúng, bảng tra cứu cần được sắp xếp theo thứ tự từ nhỏ tới lớn. Nếu bạn không sắp xếp bảng tra cứu theo thứ tự này, kết quả tra cứu dùng hàm VLOOKUP sẽ bị sai.

9. Paste Special Values

Là thực hiện phép tính ở trong nhó clipboard, cụ thể là khi bạn copy bảng tính và paste ra vùng khác và thực hiện luôn phép tính, ở đấy mình tính thử phần trăm ở hình ảnh sau


10. Tạo biểu đồ có hình cụ thể trong excel

Mặc định khi bạn tạo biểu đồ thấy không được trực quan, để tạo được hình ảnh cụ thể thì các bạn làm như sau

Click chuột phải sẽ ra menu như hình trên và click vào fill

Sau đó chọn Picture.. bạn chọn hình biểu tưởng theo ý thích từ máy tính của bạn là được

11. Hàm if trong excel

Hàng này kiểu là nếu số lớn hơn thì trả về giá trị đúng còn không sẽ trả lại giá trị sai, cụ thể mình sẽ làm như sau

12. Insert Screenshot Chụp ảnh màn hình

Giúp bạn chụp ảnh màn hình chèn vào trong excel nhanh chóng với thao tác như sau
trên menu bạn chọn Insert -> Screenshot -> Screen Clipping sau đó nó ẩn cửa sổ excel xuống và bắt đầu hiện vùng bạn cần chụp màn hình desktop và tự nó insert vào bảng excel.

13. Sửa lỗi tham chiếu ô tuyệt đối

Cụ thể khi bạn muốn tham chiếu cột C1 vào cột B nhưng khi kéo xuống để các dòng ở cột B sẽ tham chiếu từ cột C1 sẽ bị lỗi không xác định vậy ta phải thêm dấu $
Xem hình ảnh ví dụ sau để hiểu rõ

14. Hiển thị công thức đã tính trong excel Show Formulas

khi bạn muốn xem lại các công thức tính của  các dòng các cột khác nhau thì vào File -> Options -> Advanced tìm tới phần Display options for this worksheet và tích vào ô show formulas in cell instead of their calculated results và nhấn Ok là xong

Kết quả đã hiển thị công thức tính

15. Định dạng chuỗi chữ sang thành các cột

Cụ thể là khi bạn đang có dữ liệu chưa được phân thành cột mà chỉ cách nhau bởi dấu phẩy, giờ mà định dạng sang cột thì ta phải làm như sau:

Vd: tôi có dữ liệu như sau:
Stt,họ,tên
1,nguyen,Tuan
2,Nguyen,Huong
3,Tran,Manh
4,Cao,Minh
5,Tuan,Ngoc

Như vậy những thông tin sau sẽ góp ích giúp được các bạn có thêm cách sử dụng excel tốt nhất, nếu có vấn đề xin bình luận ở phía dưới để chúng tôi biết nhé

congpho

Cọng Phở

Mình là một dân CNTT theo mảng dev và marketing mới vào nghề, cho nên kinh nghiệm chưa được nhiều. Nhưng mình luôn muốn chia sẻ với các bạn những gì mình biết, đồng thời các bài viết được các cộng tác viên viết nữa. Vì thế nếu bạn mong muốn hợp tác viết bài lên camnangkienthuc.com thì hãy liên hệ mình, mình sẽ trả nhuận bút cho bạn. Cám ơn các bạn đã ủng hộ