10 mẹo và thủ thuật Excel

Bảng tính Excel là một cách tuyệt vời để so sánh và vận hành dữ liệu và các biến thể của nó trên các hàng và cột. Tuy nhiên, công cụ bộ Office này cũng có các tính năng tuyệt vời để chuyển đổi các đơn vị, phân tích các tình huống kinh tế hoặc lọc, chọn và chỉnh sửa nhiều mục. Các chức năng này, không được nhiều người dùng cơ bản của chương trình biết đến, có thể giúp cuộc sống dễ dàng hơn cho những người sử dụng bảng tính tại nơi làm việc hoặc trên cơ sở hàng ngày. Kiểm tra mười hướng dẫn thủ thuật nhỏ và mẹo Microsoft Excel nâng cao.

Bảy hàm Excel mà ít người biết đến

Cách tạo biểu đồ trong Microsoft Excel

Bạn muốn mua điện thoại di động, TV và các sản phẩm giảm giá khác? Biết so sánh

1. Lựa chọn nâng cao

Với tính năng "Tìm" tiện dụng, Microsoft Excel, giống như tất cả các chương trình Office khác, có thể nhanh chóng tìm thấy các mục và thuật ngữ trong tài liệu. Tuy nhiên, để chỉnh sửa đồng thời nhiều dữ liệu, ứng dụng cho phép bạn chọn tất cả chúng.

Bước 1. Đánh dấu toàn bộ bảng với dữ liệu bạn muốn tìm nạp và chọn. Sau đó, trong tab "Trang chủ", chọn "Tìm và chọn" và chọn "Tìm ...";

Với dữ liệu đã chọn, nhấp vào các lệnh trong tab "Trang chủ" được tô sáng

Bước 2. Trong cửa sổ "Tìm và thay thế", nhập cụm từ bạn muốn tìm và nhấn "Tìm tất cả";

Nhập mục bạn muốn tìm kiếm và nhấn "Tìm tất cả"

Bước 3. Chọn một trong các mục được hiển thị và nhấn bàn phím "Ctrl" + "A" để chọn tất cả các kết quả cùng một lúc. Sau đó nhấn đóng - các ô đã chọn sẽ vẫn được chọn;

Nhấp vào một mục từ kết quả và "Ctrl" + "A" để chọn tất cả

Bước 4. Thông qua lựa chọn này, bạn có thể xóa, tô sáng hoặc sao chép tất cả các mục đã chọn cùng một lúc.

Với lựa chọn nâng cao, bạn có thể chỉnh sửa hoặc xóa tất cả các mục được tìm thấy

2. Chuyển vị nâng cao

Tính năng chuyển vị Excel cho phép bạn chuyển đổi các cột thành các hàng và ngược lại. Tuy nhiên, với chuyển vị nâng cao, người dùng sử dụng một công thức, ràng buộc dữ liệu từ bảng chuyển đổi và gây ra bất kỳ sửa đổi nào của các mục được sao chép.

Bước 1. Chọn số lượng ô và cột tương đương với số lượng cột và ô bạn muốn chuyển đổi - ví dụ: nếu một bảng là 2 x 5, hãy chọn các ô ở định dạng 5X2;

Đánh dấu một số ô tương đương với bảng tính sẽ được hoán vị

Bước 2. Vẫn với các ô được đánh dấu, hãy viết hàm "TRANSPOR (X: Y)" - trong đó "X" là ô đầu tiên ở góc trên bên trái của bảng và "Y" là ô cuối cùng ở góc dưới bên phải;

Sau đó nhập "TRANSPOR (X: Y)", với "X" và "Y" cho biết phần đầu và phần cuối của bảng sẽ được hoán vị

Bước 3. Bảng sẽ được chuyển đổi bởi hàm, thiết lập các giá trị ban đầu. Tuy nhiên, bất kỳ thay đổi nào trong các giá trị của bảng gốc sẽ ngay lập tức thay đổi các mục được chuyển.

Bảng chuyển đổi sẽ có các giá trị cố định và sẽ phản ánh bất kỳ thay đổi nào trong bảng gốc

3. Phân đoạn dữ liệu

Excel có một bộ lọc đặc biệt cho các bảng được xác định trong bảng tính của bạn. Phân đoạn dữ liệu trình bày các macro đặc biệt với các mục bảng, mang lại kết quả trong một bố cục trực quan tuyệt vời.

Bước 1. Chọn các ô sẽ được xác định là bảng và trong tab "Chèn", chọn tùy chọn "Bảng";

Đánh dấu các ô và bấm vào các lệnh được tô sáng để chèn bảng

Bước 2. Trong cửa sổ sẽ mở, kiểm tra lựa chọn các ô sẽ tạo thành bảng, kiểm tra tùy chọn "Bảng của tôi có các tiêu đề" và xác nhận "OK";

Trong cửa sổ "Tạo bảng", chọn "Bảng của tôi có các tiêu đề" và nhấp vào "OK"

Bước 3. Tiếp theo, trong tab "Thiết kế", nhấp vào tùy chọn "Chèn phân đoạn dữ liệu" để kích hoạt hệ thống lọc động của bảng đã tạo;

Khi bảng đã được tạo, hãy nhấp vào nút "Chèn phân đoạn dữ liệu" trong "Thiết kế"

Bước 4. Một cửa sổ sẽ mở ra với các tùy chọn cho các tiêu đề của bảng đã tạo. Chọn những cái bạn cần và nhấn "OK";

Chọn các tiêu đề cột bạn muốn sử dụng

Bước 5. Sau khi phân đoạn dữ liệu được tạo, mỗi tùy chọn được đánh dấu trong cửa sổ tiêu đề sẽ loại bỏ các lựa chọn không tương thích với các lựa chọn khác. Tương tự, các mục đã chọn sẽ được hiển thị trong bảng, trong khi các mục không được đánh dấu sẽ vẫn bị ẩn.

Mỗi mục được đánh dấu sẽ được hiển thị trong bảng, trong khi các mục khác sẽ bị ẩn

4. Quản lý kịch bản

Trình bày dữ liệu trong bảng tính Excel giúp dễ dàng xem các kết quả khác nhau. Tuy nhiên, với tính năng Trình quản lý kịch bản, bạn có thể thay đổi các biến và báo cáo theo thang điểm từ kết quả tốt nhất đến kết quả xấu nhất có thể.

Bước 1. Trong bảng tính, chọn các giá trị biến và trong tab "Dữ liệu", nhấp vào nút "Kiểm tra giả thuyết" và sau đó chọn "Trình quản lý kịch bản ...";

Kiểm tra các giá trị biến trong bảng và nhấp vào các lệnh được tô sáng trong hình ảnh

Bước 2. Cửa sổ "Chỉnh sửa kịch bản" sẽ mở ra. Nhập tên vào trường "Tên kịch bản" và nhấp vào "OK" để tiếp tục định cấu hình một trong các tình huống có thể xảy ra;

Đặt tên cho kịch bản và nhấn "OK"

Bước 3. Tiếp theo, thay đổi giá trị của từng biến, theo các ô được chọn trước đó và kết luận trong "OK";

Tiếp tục chỉnh sửa kịch bản bằng cách chọn các giá trị cho từng biến và nhấp vào "OK"

Bước 4. Lặp lại thao tác để thêm các kịch bản khác và khi bạn hài lòng, chọn một trong các kịch bản này trong cửa sổ "Trình quản lý kịch bản", nhấp vào "Hiển thị" để xem kịch bản. Các công thức trong bảng tính sẽ ngay lập tức kết hợp và vận hành các giá trị kịch bản;

Chọn một kịch bản và nhấp vào "Hiển thị" để xem nó, cũng như kết quả với các công thức

Bước 5. Vẫn trong cửa sổ "Trình quản lý cảnh", nhấn nút "Tóm tắt" để tạo một bảng tính đặc biệt với mỗi kịch bản;

Thay thế "Tóm tắt" tạo ra một bảng tính với kết quả của tất cả các kịch bản

Bước 6. "Tóm tắt kịch bản" sẽ được tạo trong một bảng tính mới và sẽ trình bày các kịch bản được tạo theo cấu hình mong muốn.

Excel sẽ tạo "Kết quả kịch bản" trong một bảng tính mới

5. Chức năng "CHUYỂN ĐỔI"

Excel có một cơ sở dữ liệu chuyển đổi khổng lồ cho các giá trị và thước đo nhiệt độ, trọng lượng, kích thước, v.v. Để truy cập và sử dụng tính năng này, chỉ cần chèn chức năng "CHUYỂN ĐỔI", tự động chuyển đổi các giá trị.

Bước 1. Nhập công thức "= CHUYỂN ĐỔI (X;") mà không có dấu ngoặc kép để hiển thị bảng giá trị chuyển đổi Excel - trong đó "X" là ô có giá trị ban đầu được chọn. sau khi xác định ô để mở bảng giá trị (ví dụ trong ảnh, được chọn "số tính bằng lít");

Viết hàm "= CONVERTER (X;") và chọn loại đo sẽ được chuyển đổi

Bước 2. Sau đó, với công thức "= CONVERTER (X;" Y ";" - trong đó "Y" là số đo được chọn - chọn trong bảng thứ hai, tương thích với bảng thứ nhất, công thức mà giá trị sẽ được chuyển đổi;

Sau khi nhập ";" của "= CHUYỂN ĐỔI (X;" Y ";" thứ hai, hãy chọn mức độ mà giá trị sẽ được chuyển đổi

Bước 3. Ô có công thức sẽ tự động chuyển đổi bất kỳ giá trị đã cho nào trong trường đầu tiên, có thể được sao chép và có các biện pháp thay đổi trong các ô khác.

Trường có công thức sẽ tự động thay đổi các giá trị của ô đã chọn trước đó

6. Các tế bào có nội dung vô hình

Với các tính năng để ẩn toàn bộ cột khỏi bảng tính, Microsoft Excel cũng cung cấp một mẹo để ẩn các giá trị khỏi các ô nhất định.

Bước 1. Nhấp chuột phải vào ô có chế độ xem ẩn, sau đó chọn thay thế "Định dạng ô ...";

Nhấp chuột phải vào ô và chọn "Định dạng ô ..."

Bước 2. Trong cửa sổ "Định dạng ô", trong tab "Số", chọn tùy chọn "Tùy chỉnh" và trong trường văn bản, nhập ";;;" (không có dấu ngoặc kép);

Chọn "Tùy chỉnh" và nhập ";;;" trong trường văn bản

Bước 3. Nội dung của ô sẽ được ẩn khỏi chế độ xem bình thường của trang tính, hiển thị nó dưới dạng một trường trống. Tuy nhiên, bạn có thể kiểm tra giá trị của nó bằng cách tham khảo thanh công thức Excel.

Giá trị ô sẽ chỉ được hiển thị trong thanh công thức

7. Phân tích dữ liệu

Microsoft Excel có một bộ tính năng để phân tích dữ liệu thống kê cụ thể và tinh tế hơn. Tuy nhiên, công cụ này có sẵn trong cấu hình ban đầu của chương trình.

Bước 1. Để kích hoạt gói Phân tích dữ liệu, nhấp vào tab "Tệp" và sau đó vào tùy chọn "Tùy chọn";

Trong tab "Tệp", nhấp vào "Tùy chọn"

Bước 2. Một cửa sổ với các tùy chọn cấu hình Excel khác nhau sẽ mở ra. Trong phần "Bổ trợ", chọn tùy chọn "Bổ trợ Excel" trong hộp kiểm ở cuối giao diện và kết luận "Đi ...";

Trong "Tiện ích bổ sung", chọn "Bổ trợ Excel" và nhấp vào "Đi ..."

Bước 3. Trong cửa sổ "Tiện ích bổ sung", kiểm tra thay thế "Công cụ phân tích" và nhấn "OK";

Sau đó kiểm tra "Công cụ phân tích" và nhấp vào "OK"

Bước 4. Các tùy chọn Phân tích dữ liệu có thể được truy cập thông qua một nút trong tab "Dữ liệu", chọn một trong nhiều lựa chọn thay thế để bắt đầu cấu hình tài nguyên thống kê.

Trong tab "Dữ liệu" sẽ là nút "Phân tích dữ liệu" với các tính năng thống kê tinh vi của Excel

8. Loại bỏ các ô trống

Một mẹo thực tế và dễ dàng để chỉnh sửa và chuẩn hóa bảng tính Excel là loại bỏ các ô trống. Mặc dù không có cách nào để làm điều này, nhưng sự thay thế cho sự phù hợp là rất hữu ích và linh hoạt.

Bước 1. Trong một bảng tính có các ô trống, chọn các cột của bạn và nhập lệnh "Ctrl" + "G" để mở tính năng "Chuyển đến" và nhấp vào "Đặc biệt ...";

Chọn các cột của bảng tính, mở "Chuyển đến" bằng "Ctrl" + "G" và nhấp vào "Đặc biệt ..."

Bước 2. Trong cửa sổ "Chuyển đến đặc biệt", chọn tùy chọn "Trống" và nhấn "OK" để chọn tất cả các ô trống trong phần chọn của bảng tính;

Chọn "Trống" và nhấp vào "OK" để chọn tất cả các ô trống

Bước 3. Tiếp theo, trên tab "Trang chủ", nhấp vào lệnh "Xóa" và chọn tùy chọn "Loại trừ các ô ...". Trong hộp chọn sẽ mở, chọn tùy chọn "Làm nổi bật các ô" để giữ đơn vị bảng tính với các trường liên tục.

Trên tab "Trang chủ", nhấp vào "Xóa", "Xóa các ô ..." và "Làm nổi bật các ô"

9. Bộ lọc nâng cao

Với hệ thống lọc đã tinh vi cho bảng tính, Excel cũng có tính năng Bộ lọc nâng cao cho phép bạn tạo một bảng mới với các mục được trích xuất từ ​​bảng tính gốc.

Bước 1. Trong tab "Dữ liệu", chọn "Nâng cao" để kích hoạt cửa sổ cấu hình Bộ lọc nâng cao. Nó cũng quan trọng để sao chép tiêu đề của bảng tính để không trộn các ô và đã xác định thuật ngữ được lọc;

Sao chép tiêu đề bảng tính với thuật ngữ bộ lọc và trong "Tab dữ liệu, nhấp vào" Nâng cao "

Bước 2. Trong cửa sổ Bộ lọc nâng cao, xác định là "Khoảng thời gian liệt kê" bất kỳ bảng tính nào sẽ được sử dụng;

Đánh dấu toàn bộ bảng tính là "Khoảng thời gian liệt kê"

Bước 3. Tiếp theo, chọn tiêu đề và thuật ngữ bộ lọc cho trường "Phạm vi tiêu chí";

Đánh dấu tiêu đề và thuật ngữ bộ lọc là "Khoảng tiêu chí"

Bước 4. Để hoàn thành Bộ lọc nâng cao, hãy chọn tùy chọn "Sao chép vào vị trí mới" và bên dưới, chọn trường trong "Sao chép vào:" và nhấn "OK";

Kiểm tra "Sao chép vào một vị trí mới" và chọn ô trong "Sao chép vào"

Bước 5. Bộ lọc nâng cao sẽ tạo thành một bảng mới chỉ với các dòng có chứa thuật ngữ của bộ lọc

Một bảng tính mới sẽ được hiển thị trong trường được xác định bởi Bộ lọc nâng cao

Bước 6. Tương tự như vậy, bạn có thể kết hợp nhiều hơn hai thuật ngữ và sử dụng các giá trị và chỉ số bằng số để cải thiện quá trình lọc.

Bộ lọc nâng cao cho phép bạn kết hợp các thuật ngữ và sử dụng các giá trị với các chỉ báo số

10. Chức năng "DIATRABALHOTOTAL"

Excel có một số tính năng để đếm ngày và giờ làm việc, nhưng với công thức "DIATRABALHOTOTAL", bạn cũng có thể đặt ngày nghỉ và nghỉ.

Bước 1. Công thức "= DIATRABALHOTOTAL (A; B)" (không có dấu ngoặc kép) tự động đếm tổng số ngày làm việc - "A" là ô có ngày của ngày đầu tiên và "B" trường có dấu ngày của ngày cuối cùng;

Nhập "= DIATRABALHOTOTAL (A; B)", với "A" và "B" tương ứng là ngày làm việc đầu tiên và cuối cùng

Bước 2. Tuy nhiên, chức năng này có một biến thể thứ ba, ngoài việc kiểm tra xem ngày rơi vào cuối tuần, đã trừ vào tổng số ngày làm việc. Để tiếp tục công thức, hãy nhập "= DIATRABALHOTOTAL (A; B; C: D)" - với C và D chỉ ra các ô đầu tiên và cuối cùng của ngày lễ hoặc khoảng trống;

Với "= DIATRABALHOTOTAL (A; B; C: D)", khoảng thời gian "C: D" trừ các ngày nghỉ trong tổng số

Bước 3. Chức năng sẽ tự động hiển thị kết quả bất cứ khi nào bất kỳ biến thể nào của nó được thay đổi.

Công thức sẽ ngay lập tức

Cách tốt nhất để chuyển đổi tệp PDF sang Excel là gì? Đặt câu hỏi trong Diễn đàn.