Bạn đang xem bài viết Làm Cách Nào Để Loại Bỏ Nhanh Định Dạng Có Điều Kiện Trong Excel? được cập nhật mới nhất trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất.
Làm cách nào để loại bỏ nhanh định dạng có điều kiện trong Excel?
Định dạng có điều kiện là một công cụ rất hữu ích trong Excel, nó có thể dễ dàng giúp bạn định dạng và tô bóng ô theo tiêu chí điều kiện cụ thể, nhưng đôi khi bạn có thể muốn xóa hoặc xóa định dạng có điều kiện trong Excel. Làm cách nào để xóa định dạng có điều kiện trong Excel?
Loại bỏ định dạng có điều kiện với chức năng Clear Rules Xóa định dạng có điều kiện bằng mã VBA Dễ dàng loại bỏ định dạng có điều kiện và đổ bóng nền với Kutools cho Excel
Loại bỏ định dạng có điều kiện với chức năng Clear Rules
Chức năng Clear Rules có thể giúp bạn nhanh chóng và dễ dàng loại bỏ định dạng có điều kiện trong phạm vi đã chọn và toàn bộ trang tính.
Để xóa định dạng có điều kiện cho dải ô đã chọn, vui lòng thực hiện như sau:
1. Chọn phạm vi mà bạn muốn xóa định dạng có điều kiện.
3. Và định dạng có điều kiện đã chọn đã bị loại bỏ. Xem ảnh chụp màn hình:
Để xóa toàn bộ định dạng có điều kiện của trang tính, vui lòng thực hiện như sau:
Xóa định dạng có điều kiện bằng mã VBA
Mã VBA sau đây cũng có thể giúp bạn loại bỏ định dạng có điều kiện.
VBA: loại bỏ định dạng có điều kiện
Sub DeleteConditionalFormats() 'Update 20130912 Dim WorkRng As Range On Error Resume Next xTitleId = "KutoolsforExcel" Set WorkRng = Application.Selection Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8) WorkRng.FormatConditions.Delete End Sub2. Sau đó nhấn vào nút hoặc bấm F5 để chạy mã và hộp nhắc sẽ nhắc bạn chọn một dải ô mà bạn muốn sử dụng. Xem ảnh chụp màn hình:
3. Sau đó nhấn vào OK và tất cả định dạng có điều kiện trong phạm vi đã chọn đã bị xóa.
Dễ dàng loại bỏ định dạng có điều kiện và đổ bóng nền với Kutools cho Excel
Mô hình Kutools cho Excel‘S Đổ bóng hàng / cột thay thế tiện ích có thể giúp bạn loại bỏ cả định dạng có điều kiện và đổ bóng nền cùng một lúc.
Trước khi áp dụng Kutools cho Excel, Xin vui lòng tải xuống và cài đặt nó trước tiên.
2. Trong Đổ bóng hàng / cột thay thế hộp thoại, chỉ cần chọn Xóa bóng hàng thay thế hiện có tùy chọn trong Phương pháp che bóng và sau đó bấm vào OK cái nút. Xem ảnh chụp màn hình:
Sau đó, tất cả các định dạng điều kiện và màu tô thủ công bị loại bỏ ngay lập tức.
Nếu bạn muốn dùng thử miễn phí (30 ngày) tiện ích này, vui lòng nhấp để tải xuống, và sau đó đi đến áp dụng hoạt động theo các bước trên.
Dễ dàng loại bỏ định dạng có điều kiện và đổ bóng nền với Kutools cho Excel
Tái sử dụng: Chèn nhanh công thức phức tạp, biểu đồ và bất cứ thứ gì bạn đã sử dụng trước đây; Mã hóa ô với mật khẩu; Tạo danh sách gửi thư và gửi email …
Hơn 300 tính năng mạnh mẽ. Hỗ trợ Office / Excel 2007-2019 và 365. Hỗ trợ tất cả các ngôn ngữ. Dễ dàng triển khai trong doanh nghiệp hoặc tổ chức của bạn. Đầy đủ các tính năng dùng thử miễn phí 30 ngày. Đảm bảo hoàn tiền trong 60 ngày.
Cho phép chỉnh sửa và đọc theo thẻ trong Word, Excel, PowerPoint, Publisher, Access, Visio và Project.
Mở và tạo nhiều tài liệu trong các tab mới của cùng một cửa sổ, thay vì trong các cửa sổ mới.
Tăng 50% năng suất của bạn và giảm hàng trăm cú nhấp chuột cho bạn mỗi ngày!
Cách Sử Dụng Định Dạng Có Điều Kiện Trong Microsoft Excel
Những bảng tính tồi được cho là khó đọc. Việc tạo cho bảng tính cách sử dụng thân thiện là thêm một chút về định dạng-thực hiện các bước cơ bản giống như cách thêm tiêu đề cột, canh chỉnh và sử dụng màu sắc phù hợp trong bảng tính của bạn.
Định dạng là cần thiết, nhưng định dạng có điều kiện lại còn mạnh mẽ hơn. Định dạng có điều kiện thay đổi cách một ô xuất hiện, dựa trên bên trong các ô.
Về cơ bản định dạng có điều kiện có thể thay đổi màu nền của một ô dựa trên giá trị của ô đó.
Trong ảnh chụp màn hình ở trên, bạn có thể thấy sức mạnh của định dạng có điều kiện. Tôi đã thiết lập một quy tắc rất cơ bản có thể biến background của một ô với một màu xanh lá cây cho giá trị tích cực , trong khi đó, giá trị tiêu cực sẽ chuyển sang màu đỏ. Điều thay đổi là màu nền dựa trên giá trị.
Các ví dụ khác của định dạng có điều kiện có thể bao gồm:
Đưa tất cả con số tiêu cực trong một danh sách dữ liệu với màu đỏ.
Tạo thanh dữ liệu data bars để có một cái nhìn trực quan về các mục lớn nhất và nhỏ nhất trong bộ dữ liệu của bạn.
Sử dụng các icons để chỉ ra các giá trị cao nhất và thấp nhất trong danh sách các dữ liệu.
Dễ dàng đọc ra những lá cờ này hữu ích để đánh dấu các giá trị cao nhất với màu xanh lá cây, giá trị trung bình cho màu vàng, và số tiền thấp cho màu đỏ.Khi bạn thực hiện định dạng có điều kiện, việc định dạng điều chỉnh trong ô. Trong hướng dẫn này, bạn sẽ tìm ra cách dễ dàng sử dụng định dạng có điều kiện trong Excel.
Cách áp dụng định dạng có điều kiện trong Excel (xem & Tìm hiểu)
Trong video này, tôi sẽ hướng dẫn bạn qua một số cách dễ nhất để áp dụng định dạng có điều kiện. Bạn sẽ xem lai cách mà tôi sử dụng định dạng có điều kiện trong Excel để làm nổi bật các dữ liệu quan trọng.
Phần còn lại của hướng dẫn này sẽ tập trung vào việc thêm các định dạng có điều kiện vào bảng tính của bạn. Đọc để tìm hiểu cách bạn có thể sử dụng chúng trong bảng tính của riêng bạn.
1. Sử dụng định dạng có điều kiện đã thiết lập
Chúng ta sẽ có một cái nhìn về định dạng có điều kiện được xây dựng trong Excel. Đối với mỗi kiểu, bạn có thể áp dụng chúng bằng cách tô sáng danh sách dữ liệu mà bạn muốn định dạng, và sau đó tìm nút Conditional Formatting trên tab Home trên ribbon.
Áp dụng định dạng có điều kiện bằng cách tô sáng một danh sách dữ liệu, và sau đó chọn Conditional Formatting từ tab Home trên ribbon của Excel.
Một khi bạn đã mở trình đơn conditional formatting trên Excel, chọn một trong các tùy chọn để bắt đầu áp dụng định dạng có điều kiện.
Tô sáng các ô.
Việc chọn lựa những tùy chọn tô sáng các ô cho phép bạn định dạng có điều kiện các ô trên nhiều yếu tố khác nhau. Nhấp vào Conditional Formatting và chọn một trong các tùy chọn từ trình đơn Highlight Cells Rules để bắt đầu với định dạng có điều kiện.
Tô sáng các ô để áp dụng cài đặt trước.
Greater Than – Định dạng ô lớn hơn giá trị mà bạn thiết lập.
Less Than – Định dạng ô thấp hơn một giá trị mà bạn thiết lập.
Between – Thực hiện định dạng có điều kiện để sắp xếp các các giá trị, và làm nổi bật các ô giữa các giá trị đó.
Between – Định dạng một ô khi nó là chính xác bằng một giá trị bạn thiết lập.
Text that Contains – Định dạng ô có chứa chuỗi văn bản.
A Date Occurring – Định dạng có điều kiện dựa trên ngày trong một ô.
Duplicate Values – Nhanh chóng định dạng có điều kiện cho một danh sách dữ liệu với các bản sao.
Hãy xem một ví dụ. Tôi sẽ chọn Greater Than cho ví dụ của tôi. Sau đó, tôi sẽ đặt một giá trị trong cửa sổ pop up. Trong ảnh chụp màn hình bên dưới, tôi thiết lập giá trị là 22.000$ và sử dụng tùy chọn “Green fill…”.
Định dạng ô Greater Than trong Excel.
Như bạn có thể thấy, Excel áp dụng định dạng có điều kiện để xem trước và tất cả giá trị lớn hơn 22.000$ đã được đánh dấu màu xanh lá cây.
Trình đơn này là một cách tuyệt vời để tuỳ chỉnh quy tắc định dạng có điều kiện. Bạn có thể dễ dàng xây dựng các quy tắc để định dạng các ô bằng cách bắt đầu với tùy chọn “Highlight Cells”.
Áp dụng quy tắc Top/Bottom
Một cách tuyệt vời khác để thiết lập định dạng có điều kiện trên Excel là tùy chọn Top/Bottom Rules. Bạn có thể dễ dàng chọn lựa để làm nổi bật phía trên và dưới 10 mục trong một danh sách, hoặc áp dụng một tùy chọn khác.
Nhấp vào tùy chọn Conditional Formatting, chọn Top/Bottom Rules và chọn một định dạng hiển thị.
Top/Bottom Rules trong Excel.
Trong trường hợp của tôi, tôi sẽ chọn tùy chọn Above Average để làm nổi bật tất cả số tiền bán hàng trên trung bình trong phần thiết lập.
Cửa sổ pop up rất giống như các ví dụ trước, và chúng ta sẽ chọn Green Fill… từ trình đơn thả xuống.
Tôi đã chọn tùy chọn Green Fill để làm nổi bật màu xanh lá cây trong ô bất kỳ trên mức trung bình trong danh sách.
Thông báo trong hình ở trên, Excel sẽ tự động làm nổi bật màu xanh lá cây của các ô màu xanh lá cây bất kỳ.
2. Sử dụng Styles cài sẵn trong Excel
Làm việc với Data Bars
Một trong những cách nhanh nhất để thêm công cụ vào bảng tính là chọn tùy chọn data bar s. Data bars sẽ đặt ở thanh bên trong các ô hiển thị “kích thước” dữ liệu trong danh sách của bạn.
Chọn gradient fill hoặc solid fill từ data bars.
Để áp dụng định dạng có điều kiện trên data bar, tô sáng dữ liệu của bạn trước tiên. Sau đó, tìm tùy chọn Data Bars và chọn một trong các tùy chọn gradient fill hoặc solid fill.
Tùy chọn định dạng có điều kiện trên data bar thực sự mô tả dữ liệu của bạn, ngay bên trong các ô.
Data bars là một minh hoạ tuyệt vời về sức mạnh của định dạng có điều kiện trong Excel. Dễ dàng hơn là để tìm kiếm số tiền lớn nhất hơn cột tháng một, chỉ bằng cách quét độ dài của các thanh.
Áp dụng Color Scales
Color Scales thực sự gây chú ý đến giá trị cao nhất và thấp nhất trong danh sách dữ liệu. Sau khi bạn đã đánh dấu dữ liệu của bạn, quay về tùy chọn Conditional Formatting, và chọn một trong các tùy chọn từ trình đơn Color Scales.
Áp dụng Color Scales với định dạng có điều kiện trong Excel.
Định dạng có điều kiện với màu sắc làm nổi bật các ô với màu background dựa trên giá trị của chúng.
Trong ví dụ trên, kết quả bán hàng tốt nhất có màu xanh lá cây trên nền tối, trong khi giá trị thấp nhất là màu đỏ. Tất cả các giá trị khác màu vàng và màu xanh lá cây để đại diện cho hiệu suất của nhân viên bán hàng.
Tùy chọn color scales có nhiều quy tắc định dạng có điều kiện trên Excel. Hãy thử một số thumbnails khác để áp dụng màu sắc khác cho dữ liệu của bạn.
Thêm Icon Sets
Icon Sets là một cách tuyệt vời để thêm một số style vào bảng tính của bạn. Trong ví dụ của tôi, tôi sẽ đánh dấu dữ liệu của tôi, và chọn một vài mũi tên từ các tùy chọn định dạng có điều kiện.
Icon sets bao gồm các mũi tên để hiển thị các thay đổi trong dữ liệu, cũng như các biểu tượng khác như cờ và thanh để hiển thị dữ liệu ngay bên trong các ô.
Icon Set với định dạng có điều kiện trong Excel.
Các biểu tượng percent change giúp bạn xem lại dữ liệu của bạn một cách nhanh chóng, và điều chỉnh tự động khi dữ liệu thay đổi.
Mỗi styles biểu tượng cài đặt được thiết kế để giúp bạn đánh giá dữ liệu của bạn một cách nhanh chóng khi nhìn vào.
Tất cả các định dạng có điều kiện thích nghi khi dữ liệu thay đổi. Nếu con số bán hàng đã thay đổi, Excel sẽ tự động thay đổi biểu tượng cho phù hợp.
Thông thường, tôi bắt đầu với một trong những style được cài đặt sẵn và sửa đổi nó theo ý thích của tôi. Nó dễ dàng hơn để sử dụng tùy chọn định dạng có điều kiện và tinh chỉnh nó, thay vì bằng văn bản của riêng bạn lúc đầu.
Tóm lại
Hướng dẫn này bao hàm về định dạng có điều kiện trong Excel. Bạn đã học cách để thêm các style vào các ô của bạn thay đổi tự động dựa trên những gì bên trong các ô.
Đây là một vài điều đạt được: sử dụng định dạng làm cho bảng tính của bạn đọc dễ dàng hơn. Tuy nhiên, bằng cách sử dụng định dạng có điều kiện trong Excel giúp bạn hiểu trực tiếp các ô của bạn. Nó cho phép bạn hiểu nhanh chóng khi nhìn lướt qua dữ liệu.
Công Thức Excel Để Định Dạng Có Điều Kiện Dựa Trên Giá Trị Ô Khác
Trong bài viết này, Học Excel Online sẽ hướng dẫn cách định dạng có điều kiện trong Excel, cách sử dụng các công thức Excel để định dạng từng ô và toàn bộ hàng dựa trên các giá trị bạn chỉ định hoặc dựa vào giá trị của một ô khác.
Công thức định dạng có điều kiện dựa trên giá trị một ô
Các quy tắc định dạng có điều kiện được xác định trước của Excel chủ yếu nhằm định dạng các ô dựa trên giá trị của chúng hoặc các giá trị mà bạn chỉ định.
Nếu bạn muốn áp dụng định dạng có điều kiện dựa trên một ô khác hoặc định dạng toàn bộ hàng dựa trên giá trị của một ô, thì bạn sẽ cần sử dụng công thức Excel.
Cách tạo quy tắc định dạng có điều kiện sử dụng công thức
Vì vậy, bạn thiết lập một quy tắc định dạng có điều kiện dựa trên một công thức theo cách này:
Chọn các ô mà bạn muốn định dạng. Bạn có thể chọn một cột, một vài cột hoặc toàn bộ bảng nếu bạn muốn áp dụng định dạng có điều kiện của mình cho nhiều hàng.
Mẹo: Nếu bạn dự định sẽ nhập thêm nhiều dữ liệu trong tương lai và bạn muốn quy tắc định dạng có điều kiện tự động áp dụng cho các mục nhập mới, bạn có thể:
Chọn một số hàng trống bên dưới dữ liệu của bạn, ví dụ 100 hàng trống.
Tìm hiểu ngay: Học Excel nâng cao ở đâu?
Trong New Formatting Rule window, chọn Use a formula to determine which cells to format.
Nhập công thức vào hộp tương ứng.
Nhấp vào nút Format… để chọn định dạng tùy chỉnh của bạn.
Chuyển đổi giữa các tab Font, Border và Fill và sử dụng với các tùy chọn khác nhau như kiểu phông chữ, màu mẫu và hiệu ứng đổ để thiết lập định dạng phù hợp nhất cho bạn. Nếu bảng màu chuẩn không đủ, hãy nhấp vào More colors… và chọn bất kỳ màu RGB hoặc HSL nào theo ý thích của bạn. Khi hoàn tất, nhấp vào nút OK.
Đảm bảo phần Preview hiển thị định dạng mà bạn muốn và nếu đồng ý, hãy nhấp vào nút OK để lưu quy tắc. Nếu bạn không hài lòng với bản xem trước định dạng, hãy nhấp vào nút Format … một lần nữa và thực hiện chỉnh sửa.
Các ví dụ công thức định dạng theo định dạng có điều kiện của Excel
Bây giờ bạn đã biết cách tạo và áp dụng định dạng có điều kiện của Excel dựa trên một ô khác, chúng ta hãy tiếp tục và xem cách sử dụng các công thức Excel khác nhau trong thực tế:
Công thức để so sánh các giá trị (số và văn bản)
=$A2<$B2 – Định dạng các ô hoặc các hàng nếu một giá trị trong cột A nhỏ hơn giá trị tương ứng trong cột B.
=$A2=$B2 – định dạng ô hoặc các hàng nếu các giá trị trong các cột A và B là giống nhau.
Nếu cả hai điều kiện đều được đáp ứng
=AND($B2<$C2, $C2<$D2)
Định dạng các ô nếu giá trị trong cột B nhỏ hơn cột C, và nếu giá trị trong cột C nhỏ hơn trong cột D.
Nếu một trong các điều kiện được đáp ứng
=OR($B2<$C2, $C2<$D2)
Định dạng các ô nếu giá trị trong cột B nhỏ hơn cột C, hoặc nếu giá trị trong cột C nhỏ hơn trong cột D.
Đây là những công thức định dạng có điều kiện cơ bản bạn sử dụng trong Excel. Bây giờ chúng ta hãy xem xét một ví dụ phức tạp hơn nhưng thú vị hơn nhiều.
Định dạng có điều kiện cho ô trống và không trống
Nhưng điều gì sẽ xảy ra nếu bạn muốn định dạng các ô trong một cột nhất định nếu một ô tương ứng trong một cột khác trống hoặc không trống? Trong trường hợp này, bạn sẽ cần phải sử dụng các công thức Excel một lần nữa:
Công thức cho khoảng trống : =$B2=”” – định dạng các ô / hàng đã chọn nếu ô tương ứng trong Cột B trống.
Chú thích. Các công thức trên sẽ làm việc cho các ô “hiện ra” trống hoặc không trống. Nếu bạn sử dụng một số hàm Excel trả về một chuỗi rỗng, ví dụ =if(false,”OK”, “”), và bạn không muốn các ô như vậy được coi là khoảng trắng, hãy sử dụng các công thức sau đây =isblank(A1)=truehoặc =isblank(A1)=false để định dạng ô trống và không trống, tương ứng.
Và đây là một ví dụ về cách bạn có thể sử dụng các công thức trên trong thực tế. Giả sử bạn có một cột (B) là ” Date of Sale ” và một cột khác (C) ” Delivery” “. Hai cột này chỉ có giá trị khi hàng đã được bán và giao nhận. Vì vậy, bạn muốn toàn bộ hàng chuyển sang màu da cam khi bạn đã bán hàng; Và khi một vật phẩm được giao, một hàng tương ứng sẽ chuyển sang màu xanh lá cây. Để đạt được điều này, bạn cần phải tạo ra 2 quy tắc định dạng có điều kiện với các công thức sau:
Trong trường hợp cụ thể này, tùy chọn “Stop if true ” thực sự không cần thiết, và quy tắc sẽ làm việc bất kể có hay không có nó. Bạn có thể đánh dấu vào hộp này chỉ như một biện pháp phòng ngừa bổ sung, trong trường hợp bạn thêm một vài quy tắc khác trong tương lai có thể xung đột với bất kỳ trường hợp nào hiện có.
Công thức Excel làm việc với các giá trị văn bản
Nếu bạn muốn áp dụng định dạng có điều kiện cho các cột được chọn khi một ô khác trong cùng một hàng chứa một từ nhất định, bạn có thể sử dụng một công thức đơn giản như =$D2=”Worldwide”(chúng ta đã sử dụng một công thức tương tự trong một trong các ví dụ trước đó). Tuy nhiên, công thức này sẽ làm việc cho từ chính xác .
Đối với từ chỉ đúng một phần , bạn sẽ cần một hàm Excel khác: SEARCH () Bạn sử dụng nó theo cách này:
Tuy nhiên, trong một số trường hợp, dữ liệu sẽ tốt hơn nếu bạn tô màu các cột đã chọn hoặc toàn bộ hàng khi có giá trị trùng lặp xảy ra trong một cột khác. Trong trường hợp này, bạn sẽ cần phải sử dụng lại công thức định dạng điều kiện Excel, và lần này chúng ta sẽ sử dụng hàm COUNTIF . Như bạn đã biết, hàm Excel này đếm số ô trong phạm vi chỉ định đáp ứng một tiêu chí duy nhất.
Làm nổi bật dữ liệu trùng lặp bao gồm lần đầu xuất hiện
Nếu bạn chỉ muốn làm nổi bật các giá trị trùng lặp trên các hàng liên tiếp, bạn có thể thực hiện việc này theo cách sau. Phương pháp này hoạt động cho bất kỳ loại dữ liệu nào: số, giá trị văn bản và ngày tháng.
Chọn cột mà bạn muốn làm nổi bật các bản sao, không chọn tiêu đề cột .
Tạo một quy tắc định dạng có điều kiện bằng các công thức đơn giản sau: Quy tắc 1 (xanh dương):=$A1=$A2– nêu bật lần hai và tất cả các lần xuất hiện tiếp theo, nếu có.
Quy tắc 2 (xanh lá cây): =$A2=$A3– làm nổi bật lần xuất hiện lần đầu tiên .
Trong các công thức trên, A là cột mà bạn muốn kiểm tra, $ A1 là tiêu đề cột, $ A2 là ô đầu tiên với dữ liệu.
Quan trọng! Để các công thức hoạt động chính xác, điều quan trọng là Quy tắc 1 làm nổi bật lần lặp lại thứ 2 và tất cả các lần lặp lại tiếp theo nên là quy tắc đầu tiên trong danh sách, đặc biệt nếu bạn đang sử dụng hai màu khác nhau.
Trong ví dụ này, để làm nổi bật các hàng trùng lặp với lần xuất hiện đầu tiên , hãy tạo ra một quy tắc với công thức sau:
Đối với Cột A: =ISERROR(MATCH(A1,$B$1:$B$10000,0))=FALSE
Đối với Cột B: =ISERROR(MATCH(B1,$A$1:$A$10000,0))=FALSE
Chú thích. Để các công thức có điều kiện như vậy hoạt động chính xác, điều rất quan trọng là bạn áp dụng các quy tắc cho toàn bộ các cột, ví dụ =$A:$Avà =$B:$B.
Bạn có thể xem ví dụ về cách sử dụng thực tế trong hình sau đây để làm nổi bật các cột trùng lặp trong Cột E và F.
Khi bạn làm việc với một số dữ liệu số, hàm AVERAGE () có thể có ích để định dạng các ô có giá trị thấp hơn hoặc cao hơn mức trung bình trong một cột.
Ví dụ 1. Tìm giá trị gần nhất, bao gồm kết hợp chính xác
Trong ví dụ của chúng ta, chúng ta sẽ tìm và đánh dấu số gần nhất với số không. Nếu tập dữ liệu chứa một hoặc nhiều số không, tất cả chúng sẽ được đánh dấu. Nếu không có 0, thì giá trị gần nhất với nó, dương hoặc âm, sẽ được đánh dấu.
Trước hết, bạn cần phải nhập công thức sau vào bất kỳ ô trống nào trong bảng tính của bạn, bạn sẽ có thể ẩn ô đó sau, nếu cần. Công thức tìm thấy số trong một dãy nhất định gần nhất với số bạn chỉ định và trả về giá trị tuyệt đối của số đó (giá trị tuyệt đối là số không có dấu âm):
=MIN(ABS(B2:D13-(0)))
Trong công thức trên, B2: D13 là dải ô của bạn và 0 là số mà bạn muốn tìm kiếm kết hợp gần nhất. Ví dụ: nếu bạn đang tìm kiếm một giá trị gần nhất với 5, công thức sẽ thay đổi thành:=MIN(ABS(B2:D13-(5)))
Chú thích. Đây là một công thức mảng , vì vậy bạn cần phải nhấn Ctrl + Shift + Enter thay vì gõ Enter đơn giản để hoàn thành nó.
Và bây giờ, bạn tạo một quy tắc định dạng có điều kiện với công thức sau, trong đó B3 là ô trên cùng bên phải trong phạm vi của bạn và $ C $ 2 trong ô bằng công thức mảng trên:
=OR(B3=0-$C$2,B3=0+$C$2)
Hãy chú ý đến việc sử dụng tham chiếu tuyệt đối trong địa chỉ của ô có chứa công thức mảng ($C$2), bởi vì ô này là hằng số. Ngoài ra, bạn cần phải thay thế 0 bằng số mà bạn muốn làm nổi bật kết hợp gần nhất. Ví dụ: nếu chúng ta muốn làm nổi bật giá trị gần nhất đến 5, công thức sẽ thay đổi thành:=OR(B3=5-$C$2,B3=5+$C$2)
Trong trường hợp bạn không muốn làm nổi bật một kết hợp chính xác, bạn cần một công thức mảng khác nhau sẽ tìm giá trị gần nhất nhưng bỏ qua kết hợp chính xác.
Ví dụ: công thức mảng sau tìm giá trị gần với 0 nhất trong phạm vi được chỉ định, nhưng bỏ qua các giá trị không, nếu có:
=MIN(ABS(B3:C13-(0))+(10^0*(B3:C13=0)))
Hãy nhớ nhấn Ctrl + Shift + Enter sau khi bạn gõ xong công thức mảng.
Công thức định dạng có điều kiện giống như trong ví dụ trên:
=OR(B3=0-$C$2,B3=0+$C$2)
Tuy nhiên, vì công thức mảng của chúng ta trong ô C2 bỏ qua kết hợp chính xác, quy tắc định dạng có điều kiện sẽ bỏ qua các giá trị không và làm nổi bật giá trị 0.003 là kết hợp gần nhất.
Tại sao định dạng có điều kiện Excel của tôi không hoạt động đúng?
Hãy nhớ rằng các công thức =A1=1, =$A$1=1và =A$1=1 sẽ cho kết quả khác nhau. Nếu bạn không chắc chắn cái nào là đúng trong trường hợp của bạn, bạn có thể thử tất cả.
Xác minh phạm vi áp dụng. Kiểm tra xem quy tắc định dạng có điều kiện của bạn có áp dụng cho đúng phạm vi của ô. Một quy tắc là – chọn tất cả các ô / hàng bạn muốn định dạng nhưng không bao gồm các tiêu đề cột.
Ví dụ: nếu dữ liệu của bạn bắt đầu ở hàng 2, bạn gõ =A$2=10 sẽ đánh dấu các ô có giá trị bằng 10 trong tất cả các hàng . Một sai lầm phổ biến là luôn luôn sử dụng một tham chiếu đến hàng đầu tiên (ví dụ =A$1=10). Hãy nhớ rằng, bạn tham khảo hàng 1 trong công thức chỉ khi bảng của bạn không có tiêu đề và dữ liệu của bạn thực sự bắt đầu trong hàng 1. Dấu hiệu rõ ràng nhất của trường hợp này là khi công thức hoạt động, nhưng các định dạng giá trị không xuất hiện đúng nơi cần thiết.
Điều chỉnh các tham chiếu ô khi sao chép quy tắc. Nếu bạn sao chép định dạng có điều kiện Excel bằng Format Painter, đừng quên điều chỉnh tất cả các tham chiếu ô trong công thức.
Chia công thức phức tạp thành các phần tử đơn giản. Nếu bạn sử dụng một công thức Excel phức tạp bao gồm một số hàm khác nhau, chia thành các phần tử đơn giản và xác minh từng hàm một.
Và cuối cùng, nếu bạn đã thử tất cả các bước nhưng quy tắc định dạng có điều kiện của bạn vẫn không hoạt động chính xác, hãy viết cho tôi một dòng trong nhận xét và chúng ta sẽ cố gắng tìm hiểu nó cùng nhau 🙂
Tổng Các Ô Theo Màu Sắc Với Định Dạng Có Điều Kiện Trong Excel?
Làm cách nào để đếm / tổng các ô theo màu sắc với định dạng có điều kiện trong Excel?
Trong Excel, bạn có thể tô màu một số ô bằng cách sử dụng định dạng có điều kiện. Bây giờ, hướng dẫn này sẽ cho bạn biết một số phương pháp tiện dụng và dễ dàng để nhanh chóng đếm hoặc tính tổng các ô theo màu với định dạng có điều kiện trong Excel.
Đếm ô theo màu phông chữ và nền bằng VBA
Đếm / tổng các ô theo màu phông chữ với định dạng có điều kiện bởi Kutools cho Excel
Đếm / tổng các ô theo màu nền với định dạng có điều kiện bởi Kutools cho Excel
Đếm ô theo phông chữ và màu nền bằng VBA
Trong Excel, chỉ mã VBA mới có thể nhanh chóng đếm các ô dựa trên phông chữ và màu nền cụ thể.
1. nhấn Alt + F11 chìa khóa để mở Microsoft Visual Basic cho các ứng dụng cửa sổ.
VBA: Đếm ô dựa trên phông chữ và màu nền.
Sub DisplayFormatCount() 'Updateby20150305 Dim Rng As Range Dim CountRange As Range Dim ColorRange As Range Dim xBackColor As Long Dim xFontColor As Long On Error Resume Next xTitleId = "KutoolsforExcel" Set CountRange = Application.Selection Set CountRange = Application.InputBox("Count Range :", xTitleId, CountRange.Address, Type: = 8) Set ColorRange = Application.InputBox("Color Range(single cell):", xTitleId, Type: = 8) Set ColorRange = ColorRange.Range("A1") xReturn = 0 For Each Rng In CountRange qqq = Rng.Value xxx = Rng.DisplayFormat.Interior.Color If Rng.DisplayFormat.Interior.Color = ColorRange.DisplayFormat.Interior.Color Then xBackColor = xBackColor + 1 End If If Rng.DisplayFormat.Font.Color = ColorRange.DisplayFormat.Font.Color Then xFontColor = xFontColor + 1 End If Next MsgBox "BackColor is " & xBackColor & Chr(10) & "FontColor is " & xFontColor End Sub3. nhấp chuột chạy nút hoặc bấm F5 để chạy mã, sau đó chọn phạm vi bạn cần trong hộp thoại bật lên. Xem ảnh chụp màn hình:
4. nhấp chuột OK, sau đó chọn một ô có phông chữ và màu nền mà bạn muốn đếm các ô dựa trên. Xem ảnh chụp màn hình:
5. nhấp chuột OK. Sau đó, một hộp thoại bật ra để cho bạn biết có bao nhiêu ô có cùng màu nền và màu phông chữ với ô đã chọn trong phạm vi.
Đếm / tổng các ô theo màu phông chữ với định dạng có điều kiện bởi Kutools cho Excel
Trong Excel, bạn không thể đếm hoặc tính tổng các ô theo màu phông chữ chỉ với định dạng có điều kiện. Tuy nhiên, có một phần bổ trợ tiện dụng được gọi là Kutools cho Excel có thể giúp bạn nhanh chóng giải quyết vấn đề này với Đếm theo màu tính năng này.
Sau cài đặt miễn phí Kutools cho Excel, vui lòng làm như sau:
2. Sau đó trong Đếm theo màu hộp thoại, chọn Định dạng có điều kiện Dưới Phương pháp màu danh sách và Font Dưới Loại màu danh sách. Sau đó, bạn có thể xem kết quả của phép đếm và tổng trong hộp thoại. Xem ảnh chụp màn hình:
Chú thích: Nếu bạn muốn xuất kết quả tính toán dưới dạng báo cáo, bạn có thể nhấp vào Tạo báo cáo in Đếm theo màu thì một báo cáo sẽ được tạo trong một sổ làm việc mới. Xem ảnh chụp màn hình:
Đếm / tổng các ô theo màu nền với định dạng có điều kiện bởi Kutools cho Excel
Nếu bạn muốn đếm hoặc tính tổng các ô theo màu nền với định dạng có điều kiện, bạn cũng có thể áp dụng Đếm theo màu tiện ích của Kutools cho Excel.
Sau cài đặt miễn phí Kutools cho Excel, vui lòng làm như sau:
2. Sau đó trong Đếm theo màu hộp thoại, chọn Định dạng có điều kiện Dưới Phương pháp màu danh sách và Bối cảnh Dưới Loại màu danh sách. Sau đó, bạn có thể xem kết quả của phép đếm và tổng trong hộp thoại. Xem ảnh chụp màn hình:
Chú thích: Nếu bạn muốn xuất kết quả tính toán dưới dạng báo cáo, bạn có thể nhấp vào Tạo báo cáo trong Đếm theo màu thì một báo cáo sẽ được tạo trong một sổ làm việc mới. Xem ảnh chụp màn hình:
Bấm vào đây để biết thêm về Đếm theo màu.
Tiền boa.Với Đếm theo màu tiện ích, bạn cũng có thể đếm / tổng / trung bình các ô theo màu nền hoặc màu phông chữ chỉ với định dạng tiêu chuẩn hoặc cả hai định dạng tiêu chuẩn và có điều kiện. Nếu bạn muốn áp dụng Đếm theo màu không giới hạn trong 30 ngày, vui lòng tải xuống và dùng thử miễn phí ngay bây giờ.
Tính toán các ô theo màu phông chữ hoặc màu nền
Các công cụ năng suất văn phòng tốt nhất
Kutools cho Excel giải quyết hầu hết các vấn đề của bạn và tăng 80% năng suất của bạn
Tái sử dụng:
Chèn nhanh
công thức phức tạp, biểu đồ
và bất cứ thứ gì bạn đã sử dụng trước đây;
Mã hóa ô
với mật khẩu;
Tạo danh sách gửi thư
và gửi email …
Super Formula Bar
(dễ dàng chỉnh sửa nhiều dòng văn bản và công thức);
Bố cục đọc
(dễ dàng đọc và chỉnh sửa số lượng ô lớn);
Dán vào Dải ô đã Lọc
hữu ích. Cảm ơn !
Hợp nhất các ô / hàng / cột
mà không làm mất dữ liệu; Nội dung phân chia ô;
Kết hợp các hàng / cột trùng lặp
… Ngăn chặn các ô trùng lặp;
So sánh các dãy
hữu ích. Cảm ơn !
Chọn trùng lặp hoặc duy nhất
Hàng;
Chọn hàng trống
(tất cả các ô đều trống);
Tìm siêu và Tìm mờ
trong Nhiều Sổ làm việc; Chọn ngẫu nhiên …
Bản sao chính xác
Nhiều ô mà không thay đổi tham chiếu công thức;
Tự động tạo tài liệu tham khảo
sang Nhiều Trang tính;
Chèn Bullets
, Hộp kiểm và hơn thế nữa …
Trích xuất văn bản
, Thêm Văn bản, Xóa theo Vị trí,
Xóa không gian
; Tạo và In Tổng số phân trang;
Chuyển đổi giữa nội dung ô và nhận xét
hữu ích. Cảm ơn !
Siêu lọc
(lưu và áp dụng các lược đồ lọc cho các trang tính khác);
Sắp xếp nâng cao
theo tháng / tuần / ngày, tần suất và hơn thế nữa;
Bộ lọc đặc biệt
bằng cách in đậm, in nghiêng …
Kết hợp Workbook và WorkSheets
; Hợp nhất các bảng dựa trên các cột chính;
Chia dữ liệu thành nhiều trang tính
;
Chuyển đổi hàng loạt xls, xlsx và PDF
hữu ích. Cảm ơn !
Hơn 300 tính năng mạnh mẽ
. Hỗ trợ Office / Excel 2007-2019 và 365. Hỗ trợ tất cả các ngôn ngữ. Dễ dàng triển khai trong doanh nghiệp hoặc tổ chức của bạn. Đầy đủ các tính năng dùng thử miễn phí 30 ngày. Đảm bảo hoàn tiền trong 60 ngày.
Tab Office mang lại giao diện Tab cho Office và giúp công việc của bạn trở nên dễ dàng hơn nhiều
Cho phép chỉnh sửa và đọc theo thẻ trong Word, Excel, PowerPoint
, Publisher, Access, Visio và Project.
Mở và tạo nhiều tài liệu trong các tab mới của cùng một cửa sổ, thay vì trong các cửa sổ mới.
Tăng 50% năng suất của bạn và giảm hàng trăm cú nhấp chuột cho bạn mỗi ngày!
Cập nhật thông tin chi tiết về Làm Cách Nào Để Loại Bỏ Nhanh Định Dạng Có Điều Kiện Trong Excel? trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!