138 thủ thuật excel nâng cao cực kỳ hữu ích không thể bỏ qua
138 thủ thuật excel nâng cao cực kỳ hữu ích không thể bỏ qua
Dưới đây là 38 thủ thuật Excel nâng cao hữu ích:
- Tính toán điều kiện với hàm SUMIFS, COUNTIFS, AVERAGEIFS.
- Sử dụng hàm INDEX và MATCH để tìm kiếm giá trị trong bảng.
- Biến đổi dữ liệu với Power Query để nhập và xử lý dữ liệu từ nhiều nguồn.
- Tạo báo cáo động với PivotTable để tổng hợp dữ liệu một cách linh hoạt.
- Sử dụng công thức mảng để tính toán phức tạp.
- Sử dụng Data Validation để hạn chế dữ liệu nhập vào ô.
- Sử dụng Named Ranges để quản lý và sử dụng các phạm vi dễ dàng hơn.
- Tạo biểu đồ phức tạp với các loại biểu đồ không phổ biến.
- Tạo báo cáo sổ cái phức tạp với hàm SUMPRODUCT.
- Tính toán dựa trên ngày tháng với các hàm DATE và YEAR.
- Tạo các báo cáo mẫu với các chức năng hàm IF và VLOOKUP.
- Sử dụng kịch bản để tự động hóa các công việc lặp lại.
- Sử dụng điều kiện định dạng để làm nổi bật dữ liệu theo ý muốn.
- Tạo các biểu đồ động với các thanh trượt và phím chuyển đổi.
- Sử dụng hàm INDIRECT để tham chiếu đến các ô và phạm vi động.
- Sử dụng Macro Recorder để ghi lại các thao tác và tự động hóa chúng.
- Kết hợp với hàm TEXT để định dạng dữ liệu theo ý muốn.
- Sử dụng các công cụ phân tích dữ liệu, như Goal Seek và Solver.
- Sử dụng hàm TRANSPOSE để chuyển đổi dữ liệu dọc thành ngang và ngược lại.
- Tích hợp với Microsoft Power BI để tạo các báo cáo tùy chỉnh và chuyên nghiệp.
- Tạo các biểu đồ phức tạp với PivotChart và Slicers.
- Sử dụng hàm CONCATENATE để kết hợp các giá trị và văn bản.
- Tạo biểu đồ Gantt để quản lý dự án.
- Sử dụng bảng tính Data Tables để thực hiện phân tích “What-If”.
- Sử dụng các công cụ của Ribbon để tùy chỉnh giao diện Excel.
- Sử dụng các hàm hợp nhất văn bản, như CONCATENATE và TEXTJOIN.
- Tính toán trên các ô được ẩn với hàm SUBTOTAL.
- Tích hợp dữ liệu từ các nguồn bên ngoài với hàm WEBSERVICE và FILTERXML.
- Tạo các biểu đồ tần số với hàm FREQUENCY.
- Sử dụng hàm CHOOSE để lựa chọn giá trị từ một danh sách.
- Tạo bảng chọn bằng cách sử dụng hàm OFFSET và Data Validation.
- Tích hợp với hàm DATEDIF để tính số ngày giữa hai ngày.
- Sử dụng hàm RANDBETWEEN để tạo số ngẫu nhiên trong một khoảng.
- Tạo các biểu đồ Sparkline để hiển thị dữ liệu trên một ô.
- Sử dụng các hàm Lookup như HLOOKUP và VLOOKUP để tra cứu dữ liệu.
- Tạo các biểu đồ địa lý với hàm GEOGRAPHY.
- Tạo các hình dạng tùy chỉnh và biểu tượng trong Excel.
- Sử dụng các hàm thông minh như IFERROR, IFNA để xử lý lỗi dễ dàng hơn.
Tin học Đức Minh xin giới thiệu tới các bạn 13 thủ thuật Excel nâng cao trong Excel cực kỳ hữu ích mà các các bạn kế toán viên hay bất kỳ ai cũng có thể tham khảo như sau:
1. Thủ thuật Excel nâng cao – Điền nhanh dữ liệu bằng các ô giống nhau
Bằng cách nhấp chuột đúp chuột vào góc phải phía dưới của một ô để làm Open con trỏ chuột hình dấu cộng, sẽ tạo ra một chuỗi tài liệu giống hệt ô gốc trong những ô tiếp theo của cột. Ví dụ, nếu những bạn nhập tài liệu trong những ô A2 : A20, nhập một công thức hay text vào trong ô B2. Nhấp chuột hai lần chuột vào góc dưới của ô B2. Microsoft Excel sẽ điền tài liệu xuống phía dưới cột từ ô B2 tới ô B200 .
2. Thủ thuật Excel nâng cao – Làm vừa dữ liệu trong một trang
Excel đã cực kỳ cố gắng nỗ lực đưa thật nhiều tài liệu cho vừa một trang, nhưng những bạn trọn vẹn hoàn toàn có thể giảm bớt hay làm tăng thêm cho những tài liệu của bảng tính của những bạn bằng cách đổi khác kiểu lựa chọn Adjust To % Normal Size. Hay những bạn hoàn toàn có thể sử dụng lựa chọn tính năng Fit To Pages để nén tài liệu cho đầy những số trang riêng không liên quan gì đến nhau. Đơn giản, những bạn chỉ vệc chọn File \ Page Setup và test với hai lựa chọn đó bằng cách biến hóa những thiết lập của chúng. Cuối cùng, những bạn nhấp chuột vào nút Print Preview để xem tác dụng .
3. Thủ thuật Excel nâng cao – Hiển thị các ô hay dùng
Để tìm ra được bất kể ô nào mà những bạn hay dùng, chọn công dụng Auditing từ menu Tools sau đó chọn Show Auditing Toolbar. Nhấp chuột vào một ô cùng với một công thức, khi đó chọn command Trace Precedents ( nút tiên phong trên thanh công cụ Auditing toolbar ), và đưa mũi tên tới những ô mà những bạn đã sử dụng để thống kê giám sát những giá trị của riêng ô đó, lúc này hình mũi tên màu xanh xanh sẽ Open có link từ những ô tài liệu tới ô hiệu quả. Để xoá những mũi tên bất kể, nhấp chuột vào nút Remove All Arrows .
4. Thủ thuật Excel nâng cao – Tìm nhanh những ô có chứa công thức
Để tìm ra nơi những ô có chứa công thức một cách cực kỳ nhanh gọn trong bảng tính, chọn công dụng Go To từ menu Edit. Trong hộp thoại Open, chọn menu Special \ Formulas sau đó nhấp chuột vào OK. Khi đó, mỗi ô có chứa một công thức sẽ được lựa chọn. Sau đó ghi lại vào Formulas trong hộp thoại Go To để chọn một ô có công thức .
5. Thủ thuật Excel nâng cao – Sử dụng ô tham chiếu + nhãn text trong công thức
Để sử dụng những tham chiếu ô nào đó cùng với với nhãn text trong một công thức bất kể, những bạn chỉ việc nhập một ký hiệu “ và ” ở nhấn giữa tham chiếu và phần text. Ví dụ, để hiển thị dòng tài liệu là “ 100 tips ”, nhập ( = A2 và “ Tips ” ), trong đó A2 chứa số 100 .
6. Thủ thuật Excel nâng cao – Hiển thị đối số của những công thức
Để xem đối số trong một công thức Microsoft Excel, những những bạn hãy nhnhấn phím tổng hợp phím Ctrl – Shift – A.Ví dụ, nếu những những bạn nhập = RATE và nhấn phím tổng hợp phím Ctrl – Shift – A, những những bạn hoàn toàn có thể nhìn thấy mọi đối số cho công thức này ( ví dụ = RATE ( nper, pmt, pv, fv, type, guess ) ). Nếu những những bạn muốn biết rõ ràng hơn, nhập theo : = RATE và nhnhấn phím tổng hợp phím Ctrl + A để hiển thị công thức theo Function Wizard .
7. Thủ thuật Excel nâng cao – Tham chiếu những công thức
Để tham chiếu những công thức ứng dụng trong Excel, những bạn hãy nhnhấn phím tổng hợp phím nhấn giữ tổng hợp phím Shift-F3, Excel sẽ đưa ra cho những bạn một hộp thoại dialog chứa mọi công thức ứng dụng. Thậm chí còn có hướng dẫn những cú pháp đơn cử cho từng công thức một khi những bạn chuyển dời con trỏ chuột điểm sáng đến bất kỳ công thức nào muốn tham chiếu. Đây cũng là một cách nhập công thức cực nhanh mà không cần nhập hàng loạt cú pháp. Những điều này cực kỳ quan trọng cho người học kế toán hay bất kể ai
8. Thủ thuật Excel nâng cao – Dùng phím F9 để tính trong thời điểm tạm thời
Nếu những bạn tạo ra một bảng tính có chứa công thức quá dài nên không hề biết ngay tác dụng, những bạn hoàn toàn có thể kéo con trỏ chuột chuột để chọn một phần của công thức trong thanh công thức, sau đó nhnhấn phím tổng hợp phím F9. Lập tức, hiệu quả của một công thức con trong dãy công thức của những bạn sẽ hiện lên trên màn hình hiển thị Excel. Quan trọng hơn là những bạn tuyệt đối không được nhấn phím Enter, một phần của công thức đó sẽ bị mất đi, nên để chắc như đinh thì những bạn phải nhấn tiếp phím ESC. Tuy nhiên nếu những bạn đã lỡ nhấn phím Enter, thì hãy thử nhấn phím tổng hợp tổng hợp phím Ctrl – Z để hồi sinh lại những đổi khác. Nếu những bạn là một kế toán thuế, những bạn nên hiểu rõ tầm quan trọng của phím F9 này
9. Thủ thuật Excel nâng cao – Liên kết textbox tới tài liệu trong ô
Các bạn hoàn toàn có thể link một textbox tới tài liệu trong một ô của bảng tính bằng cách tạo ra một textbox và link công thức để trả lại hiệu quả của ô đó tới textbox .
– Nhấp vào icon tạo một textbox trên Drawing Toolbar. Nhấp chuột vào bảng tính và kéo con trỏ chuột để tạo ra một textbox .
– Đưa con trỏ chuột tới thanh công thức, nhập công thức đã cho hiệu quả tới ô cần link vào textbox. ( Ví du : trong ô A2 những bạn có số liệu là 2. Trên thanh công thức, ban nhập = A2 ). Sau đó nhấn phím Enter .
– Text hay số liệu những bạn nhập trong ô đã link ( ví dụ A2 ) sẽ Open trong textbox. Trong ví dụ trên thì textbox sẽ có giá trị ở trong là 2 .
Các bạn hoàn toàn có thể vận động và di chuyển textbox tới một vài bảng tính khác trong tập những bảng tính nếu những bạn muốn .
10. Thủ thuật Excel nâng cao – Liên kết một hình ảnh tới một dãy ô
Các bạn hoàn toàn có thể copy một dãy ô trong Microsoft Excel và dán nhanh chúng như một hình ảnh trong một bảng tính. Đây cũng là một cách cực kỳ tinh xảo để hoàn toàn có thể thuận tiện nhìn thấy ô nào đó tại một vài nơi trong bảng tính của những bạn. Các bạn cũng hoàn toàn có thể sử dụng chiêu thức này để in những ô trong một trang Microsoft Excel. Khi những ô link đổi khác thì những định dạng này cũng được vận dụng cho những ô được link. Để tạo một hình ảnh được link, những bạn liên tục làm theo những bước :
– Chọn những ô cells có chứa tài liệu gốc .
– Nhấp chuột vào Copy trên menu Edit .
– Chọn ô mà những bạn muốn dán hình ảnh vừa chọn Open .
– Trong khi nhấn giữ phím Shift, nhấp chuột vào Paste Picture Link trên menu Edit. Kết quả sẽ được thực hiện nhanh chóng.
11. Thủ thuật Excel nâng cao – Sử dụng Advanced Filter
Nếu những bạn tạo ra một list Shift trong Excel và muốn chọn tại tài liệu đó sau đó copy chúng tới bảng tính khác, hãy sử dụng tính năng Advanced Filter. Để khởi đầu sử dụng tính năng này, nhấp chuột vào Filter trong menu Data, nhấp chuột vào Advanced Filter và làm theo những hướng dẫn .
– Sử dụng công thức Sum+ If tính tổng dữ liệu Advanced Filter:
Giả sử những bạn tạo ra một list tài liệu trong ô từ A2 đến A20 và muốn tính tổng mọi giá trị lớn hơn 100 và nhỏ hơn 1000. Để làm được việc này, sử dụng theo dòng công thức như dưới đây :
= SUM ( IF ( A2 : A20 > = 100, IF ( A2 : A20 < = 1000, A2 : A20, 0 ), 0 ) )
Để chắc như đinh những bạn nhập công thức trên như thể một dãy, những bạn hãy nhấn tổng hợp phím Ctrl – Shift – Enter. Sau đó những bạn sẽ nhìn thấy dấu ngoặc { } trong công thức. Nhưng không được nhấn phím Enter khi đang nhập công thức .
– Sử dụng công thức Count + if để đếm dữ liệu:
Các bạn đã có một list tài liệu trong những ô A2 : A20, và muốn đếm mọi giá trị lớn hơn 100 và nhỏ hơn 1000. Các bạn sử dụng công thức sau :
= Count ( IF ( A2 : A20 > = 100, IF ( A2 : A20 < = 1000, 1,0 ), 0 ) )
12. Thủ thuật Excel nâng cao – Sắp xếp một list đã được lọc
Để sắp xếp một list tài liệu đã được lọc, chọn Data \ Sort, sau đó chọn cột thích hợp từ hộp điều khiển và tinh chỉnh control box – Sort by. Đặc biệt những bạn cần quan tâm là sắp xếp theo thứ tự tăng dần ( Ascending ) hoặc giảm dần ( Descending ) sau đó nhấp chuột vào OK .
13. Thủ thuật Excel nâng cao – Lấy những bộ lọc khi việc làm đã triển khai xong
Nếu những bạn sử dụng công dụng AutoFilter để lọc những bản ghi, những bạn đã kết thúc việc hiển thị những record đó, nhưng sau đó những bạn lại muốn xem lại mọi bản ghi của những bạn một lần nữa. Để nhận lại được những bản ghi đó, đơn thuần những bạn chỉ việc chọn All từ list thả xuống của bộ lọc hiện tại. Nếu những bạn muốn tắt công dụng AutoFilter, chọn menu Data \ Filter và xoá chọn trong AutoFilter .
Ngoài ra các các bạn có thể tham khảo thêm một bài viết cực kỳ hay là 100 THỦ THUẬT EXCEL cực kỳ hấp dẫn tại:
http://ketoanducminh.edu.vn/tin-tuc/102/2729/100-thu-thuat-Excel-cuc-ky-huu-ich-bat-buoc-phai-nho-cap-nhat-lien-tuc.html
Mời các bạn xem thêm các bài viết cực kỳ hay và tâm huyết tại:
>>> Chuyển đổi số thành chữ trong excel
>>> Quản lý hàng hóa bằng data validation trong excel
>> > Cách học excel
Với tiềm năng “ Sự thành công xuất sắc của học viên là niềm tự hào của Đức Minh ”, Công ty giảng dạy kế toán và tin học Đức Minh là nơi đào tạo và giảng dạy kế toán trong thực tiễn và tin học văn phòng uy tín và chuyên nghiệp nhất TP. Hà Nội lúc bấy giờ. Đức Minh luôn sẵn sàng chuẩn bị tương hỗ hết mình vì học viên, luôn sát cánh cùng học viên trên bước đường đi tới thành công xuất sắc .
Lịch học dạy kèm linh động từ thứ 2 đến thứ 7 hàng tuần cho toàn bộ những học viên :
Ca 1: Từ 8h -> 11h30 * Ca 2: Từ 13h30 -> 17h * Ca 3: Từ 18h -> 20h
Bảng giá khóa học
TỔ CHỨC THI VÀ CẤP CHỨNG CHỈ CỦA VIỆN KẾ TOÁN ĐỨC MINH
Mọi cụ thể vui mắt liên hệ :
HỌC VIỆN ĐÀO TẠO KẾ TOÁN – TIN HỌC ĐỨC MINH
Cơ Sở 1: Tầng 2 – Tòa nhà B6A Nam Trung Yên – đường Nguyễn Chánh – Cầu Giấy HN – 0339.156.806
Cơ Sở 2: Phòng 610 – Chung cư CT4A2 Ngã tư Nguyễn Hữu Thọ, Nguyễn Xiển – Linh Đàm – Hoàng Mai – Hà Nội. – 0342.254.883
Cơ Sở 3: Tầng 2, Nhà số 2, Ngõ 4 – Đường Nguyễn Khuyến – Văn Quán – Hà Đông – Hà Nội – 0339.421.606
Source: https://dvn.com.vn
Category: Thủ Thuật
