Xu Hướng 2/2023 # Cách Sử Dụng Định Dạng Có Điều Kiện Trong Microsoft Excel # Top 6 View | Hoisinhvienqnam.edu.vn

Xu Hướng 2/2023 # Cách Sử Dụng Định Dạng Có Điều Kiện Trong Microsoft Excel # Top 6 View

Bạn đang xem bài viết Cách Sử Dụng Định Dạng Có Điều Kiện Trong Microsoft 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.

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.

Conditional Formatting Trong Excel, Cách Định Dạng Có Điều Kiện Từ Pro

Conditional Formatting là định dạng có điều kiện trong excel. Áp dụng cho excel 2003, 2007, 2010, 2013, 2016.

Bạn dùng CF để tô màu theo 1/ nhiều điều kiện. Ví dụ: Bôi màu ô trống, ô chứa lỗi, Tìm giá trị trùng, duy nhất, tìm TOP 3/5/10… sản phẩm có doanh số lớn nhất,…

Bài viết này dành cho: Nhân sự, Kế toán, Sale admin, Kinh doanh, Quản lý sản xuất,…

Trường đã dành ra 2 ngày lễ để viết bài này gửi tặng các bạn và sẽ dành nhiều ngày khác nữa để viết tiếp. Khối lượng kiến thức rất lớn và thường xuyên được cập nhật.

1. Cơ bản 2. Nâng cao bằng Ví dụ thực tế 3. Lập báo cáo chuyên nghiệp khi sử dụng excel 2007 trở lên

Sử dụng Databar: Chèn Biểu đồ ngay trong ô Excel

Color scales: Bôi màu các ô chứa giá trị theo các nhóm

Icon sets: Chèn biểu tượng tăng giảm ngay cạnh số trong excel giúp lập báo cáo chuyên nghiệp

Lưu ý:

Có một số mục đã được viết, hàng tuần Trường sẽ bổ sung các nội dung còn lại.

Now, let’s move your ass!

1. Định dạng có điều kiện: Cơ bản

Conditional formatting là Định dạng có điều kiện trong excel.

Định dạng có điều kiện là việc áp dụng 1 hoặc nhiều loại định dạng cho 1/ 1 vùng ô theo 1/ nhiều điều kiện.

Sau khi xóa quy luật định dạng của CF thì định dạng mặc định của ô sẽ được khôi phục.

Ví dụ:

Tôi muốn bôi nền vàng tên của các sinh viên có điểm thi đạt loại GIỎI. Màu của các ô excel được xác định bởi:

(Trường xin dùng từ viết tắt CF thay thế cho conditional formatting trong một số chỗ của bài viết này)

Đối với người lâu năm trong nghề phân tích tài chính như Trường thì CF là một công cụ tuyệt vời. CF là không thể thiếu để làm các báo cáo phân tích khoa học, trực quan và đẹp.

CF trong excel 2003:

Bị giới hạn số lượng điều kiện: Tối đa là 3

CF trong excel 2007 trở lên (2010, 2013, 2016):

Cho phép dùng tới 64 điều kiện

Trong bài viết này ad sẽ hướng dẫn các bạn cách sử dụng CF trên 2007 trở lên. Về cơ bản các tính năng trong phần 2 sẽ áp dụng được cho cả excel 2003.

2.2. Giới thiệu về các điều kiện có sẵn của conditional formatting

2.2.1. Cách Mở bảng điều khiển Định dạng có điều kiện

Để mở bảng điều khiển của Định dạng có điều kiện bạn chọn như sau:

(1) Select a rule type – chọn loại điều kiện

(2) Edit the rule description – chỉnh sửa điều kiện và định dạng đi kèm

2.2.2. Giải thích Chi tiết các nhóm:

Dựa vào giá trị của chính các ô trong vùng được chọn, excel sẽ tự động bôi màu. Ví dụ A1:E1, tôi chọn bôi màu giá trị nhỏ nhất (đậm nhất) và nhạt dần tới giá trị lớn nhất. Tham khảo hình dưới.

Trường muốn bôi vàng các ô có giá trị năm trong khoảng 2 – 3.

Bạn không cần làm gì cả, chỉ cần bôi đen vùng ô chứa giá trị và thiết lập điều kiện mong muốn là được.

Bước 1 là bôi đen

Bước 2 là thiết lập các điều kiện như ở hình dưới.

Values that are above or below average

Đơn giản bạn chỉ cần nói cho excel biết:

Tôi muốn bôi màu các ô có giá trị cao hơn giá trị trung bình của tất cả các ô có trong vùng được chọn.

Không phải làm nhiều & rất dễ thực hiện.

Dễ như ăn kem!

Bạn chọn Duplicates hoặc Unique trong phần điều kiện rồi chọn định dạng cần cho các giá trị tương ứng là được.

Use a formula to determine which cells to format

2.1. Ẩn lỗi trong báo cáo tự động bằng định dạng có điều kiện trong excel

Ở cột A hình dưới, Trường có một danh sách các ô chứa giá trị số và giá trị là lỗi.

Khi trình bày báo cáo ta không thể để các lỗi đó xuất hiện được. Do đó cần chuyển hết các ô có lỗi thành màu trắng sẽ làm báo cáo đẹp và chuyên nghiệp hơn.

B1: Bôi đen vùng cần định dạng

B3: Trong bảng định dạng, bạn chọn loại định dạng: Use a formula to determine which cells to format

B4: Trong phần soạn thảo định dạng chi tiết bạn chọn:

Nhập vào Format value where this formula is true: = ISERROR($A3)

Sau khi chọn xong ta nhấn OK để hoàn tất quá trình ẩn lỗi trong excel.

Mở rộng:

=Iserror (value): Kiểm tra tất cả các lỗi

=Isna (value): Kiểm tra các ô chứa lỗi #N/A

=Iserr (value): Kiểm tra tất cả các lỗi trừ lỗi #N/A

Rất khó nhận biết ô nào trống khi vùng dữ liệu lên tới hàng nghìn ô.

Trường sẽ bôi màu nền (“xanh”) cho tất cả các ô trống trong vùng dữ liệu để dễ dàng nhận biết như sau:

B1: Chọn vùng dữ liệu

B2: Thiết lập điều kiện trong bảng CF như sau: Isblank (A1)

B3: Chọn màu nền tại Format rồi nhấn OK

Sau khi hoàn tất các bước trên, ta sẽ một bảng dữ liệu với các ô trống được bôi nền màu xanh. Thực sự khác biệt phải không nào các bạn.

Điều này gây khó khăn trong việc nhận biết các phiếu nhập/ xuất.

Vì Vậy!

Hình sau sẽ minh họa cho điều chúng ta sẽ làm.

B1: Bôi đen A2:A11

B3: Nhập công thức sau vào phần formula: =A1=A2

B4: Chọn màu chữ “Trắng” trong Format

B5: Nhấn OK để hoàn tất quá trình định dạng có điều kiện

Quả thực làm rất nhanh và dễ, đúng không nào các bạn.

Nhưng kết quả thu được đúng là quả ngọt 😀

2.4. Xác định ngày đến hạn (tô màu chỉ định cho các ô này)

Phần này đặc biệt hữu ích cho những ai làm kế toán công nợ, thanh toán. Ngoài ra thì những ai đang quản lý phần chăm sóc khách hàng cũng cần phải biết.

Mục đích: Dễ dàng biết được hôm nay phải thu, trả tiền ai? Phải gọi điện chăm sóc ai,…

Cách làm như sau:

B1: Bôi đen vùng dữ liệu cần bôi màu

Do đó, các ngày thỏa mãn điều kiện là ngày nằm trong khoảng: [ Ngày hôm nay] và [ Ngày hôm nay + 7]

Và khi đó, ta phải dùng hàm And để xác định điều kiện 2 chiều này.

Lưu ý:

Định dạng: Việc chọn màu và tìm tới vùng nhập công thức (điều kiện) định dạng thì rất dễ rồi. Ai cũng phải biết được khi đã học tới bước này.

Công thức: Nhưng việc sử dụng công thức như thế nào và tại sao lại dùng như vậy? Thì với nhiều bạn cần phải nghiên cứu đi nghiên cứu lại, thực hành đi thực hành lại.

Cái này hay nè!

Bạn có một bảng dữ liệu với chỉ toàn một màu trắng – hơi khó nhìn dữ liệu phải không nào?

Bảng bên phải hình dưới là báo cáo sau khi định dạng cách dòng.

Về cảm quan, Trường thấy đọc báo cáo bảng bên phải sẽ dễ nắm thông tin hơn nhiều.

Và Trường sẽ dạy các bạn bôi màu cách dòng cả nghìn dòng trong nháy mắt

Dễ ợt!

B1: Chọn vùng ô cần định dạng (đương nhiên rồi)

B2: Mở bảng điều khiển Định dạng có điều kiện để nhập công thức định dạng

B3: Nhập công thức để xác định điều kiện, chọn màu nền và nhấn OK

Mở rộng:

Công thức sử dụng trong việc tô màu nền cách dòng:

1. Tô màu các ô thuộc dòng lẻ: =Mod(Row(),2)

2. Tô màu các ô thuộc dòng chẵn: =Mod(Row(),2)=0

3. Tô màu nhiều ô: =Mod(Row(),n) – trong đó n là mẫu số 3 dòng, 4 dòng,…

2.6. Bôi màu những nhân viên có ngày sinh nhật thuộc tháng hiện tại

Với những người làm nhân sự thì việc hàng tháng thống kê người có ngày sinh nhật trong tháng là chuyện thường ở phố huyện.

Nếu công ty chỉ có vài người thì dễ rồi. Nhưng nếu có hàng trăm/ nghìn nhân viên mà ngồi đếm tay thì hơi vất.

Tháng này là tháng 01 năm 2017, Trường sẽ dùng CF để bôi màu xanh tất những ai có sinh nhật tháng này.

Công thức: =Month($B4)=Month(Now())

Cụ thể bạn xem hình dưới.

1. Hàm Month sẽ tự động tính ra tháng của ngày được chọn

2. Hàm Now() sẽ lấy ngày hiện tại của hệ thống

Hàm Today() – là sự thay thế cho hàm Now(). Có tác dụng gần tương đương nhau.

2.7. Tô màu các ngày thứ 7 và chủ nhật/ ngày lễ

Nhiều khi ta phải lập báo cáo với nhiều cột, mỗi cột là dữ liệu của một ngày. Và bạn phải bôi màu các cột ngày thứ 7 và chủ nhật để làm nổi bật các ngày này.

Đặc biệt khi bạn là kế toán lương, nhân sự thì bảng chấm công là công việc hàng ngày của bạn.

Cách làm như sau:

B1: Bôi đen các cột muốn định dạng có điều kiện

B2: Mở bảng định dạng conditional formatting

B3: Chọn Use a formula to determine which cells to format

B4: Nhập công thức để xác định ngày nào thứ 7 hoặc chủ nhật bằng hàm Weekday. Bạn xem hình dưới để hiểu rõ hơn cách sử dụng hàm.

B5: Chọn màu và định dạng mong muốn của các ô thỏa mãn điều kiện rồi nhấn OK để hoàn tất.

Kết quả ta có được bảng như sau:

Cách làm như sau:

B1: Chọn vùng dữ liệu cần định dạng TOP 3 (chính là cột số lượng)

TOP/ BOTTOM: Nếu là Top là các giá trị lớn nhất; còn Bottom là các giá trị nhỏ nhất. Tùy vào bạn cần gì để chọn cái tương ứng

Format: Là nơi bạn bôi màu, bôi đậm, gạch chân,…

Bước cuối cùng là nhấn OK để hoàn tất.

Trong ví dụ trên, Trường sẽ bôi đậm TOP 3 giao dịch có số lượng bán nhỏ nhất. Và kết quả như sau:

Sẽ có ngày 29/11/2017

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 Sub

3. 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ách Sử Dụng Hàm Countif Và Countifs Có Điều Kiện Trong Excel

Hàm countif trong excel

Hàm countif thuộc nhóm hàm count sử dụng để đếm, trong đó hàm countif là hàm đếm có điều kiện và là sự kết hợp của hàm count và hàm if trong excel.

Hàm countif trên Excel có cú pháp là =COUNTIF(range;criteria)

Trong đó:

Range là phần vùng đếm dữ liệu (bắt buộc). Có thể là số, tham chiếu hoặc mảng chiếu chứa số. Bỏ qua các giá trị trống.

Criteria là phần điều kiện (bắt buộc) thực hiện đếm các giá trị bên trong range, số, biểu thức, tham chiếu ô hoặc văn bản.

Lưu ý:

Hàm countif trả về kết quả có điều kiện với các chuỗi ký tự hơn 255 ký tự.

Đối số criteria để trong ngoặc kép không phân biệt chữ thường hay in hoa.

Có thể sử dụng ký tự là dấu chấm hỏi (?) trong điều kiện criteria. Tuy nhiên, tùy vào từng cài đặt trên máy để sử dụng dấu trong hàm là (,) hoặc (;) để sử dụng.

Ví dụ:

Hàm countif trong excel

Để đếm được số học sinh nam cần áp dụng công thức cho ô D15 như sau: =COUNTIF(C5:C14,”Nam”)

Kết quả áp dụng hàm countif trong excel

► XEM THÊM: Các hàm cơ bản trong Excel mà “dân” văn phòng phải biết

Hàm countifs

Khái niệm hàm countifs trong excel

Hàm countifs trong excel là một hàm nâng cao của hàm đếm count. Nhưng có một điểm khác biệt là hàm này chứa nhiều điều kiện để xét. Trong trường hợp có nhiều điều kiện khi xét các giá trị thì nên áp dụng hàm countifs.

Trên lý thuyết, hàm count, hàm countif, hàm countifs đều là hàm đếm trong excel, chủ yếu khác nhau về yêu cầu khi sử dụng dựa vào điều kiện cụ thể.

Lệnh countifs trong excel

Cú pháp lệnh hàm countifs: =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2],…).

Trong đó:

Criteria_range1: Vùng chọn đầu tiên cần thống kê (bắt buộc).

Criteria1: Đặt điều kiện áp dụng cho định dạng có thể là số, ô tham chiếu, chuỗi văn bản, một mảng hoặc một hàm Excel khác, (bắt buộc).

[criteria_range2, criteria2]: Cặp vùng chọn và điều kiện bổ sung tối đa cho phép 127 cặp vùng chọn và điều kiện.

Lưu ý:

Để đếm các ô trong một phạm vi đơn lẻ với một điều kiện duy nhất hay trong nhiều vùng dữ liệu với nhiều điều kiện bạn có thể áp dụng hàm countifs.

Vùng chọn bổ sung cùng số hàng và cột như vùng dữ liệu đầu tiên (Đối số criteria_range1i).

Có thể đếm vùng dữ liệu liền kề và không liền kề.

Hàm countifs xử lý như một giá trị bằng không (0) trong tiêu chí là một tham chiếu đến một ô trống.

Bạn có thể áp dụng các wildcard characters trong các tiêu chí như:

Cách dùng hàm countifs

Với bảng thống kê thu nhập, để chế độ tự động thống kê số lượng nhân viên có thu nhập từ 10 triệu trở lên áp dụng cấu trúc như sau:

Trong đó:

C2:C10 là cột chức vụ trong bảng, điều kiện chính là “nhân viên”. Máy sẽ đọc dữ liệu điều kiện đầu tiên là đếm số lượng nhân viên trong công ty.

Tại ví dụ này, hàm countifs trong excel sẽ thỏa mãn hai điều kiện bên trên, tự động bỏ qua những người không có chức vụ nhân viên và thu nhập dưới 10 triệu.

Hàm countifs trong excel

Cách sử dụng hàm countif và hàm countifs nhiều điều kiện

Cách đếm ô tính với nhiều tiêu chí (and logic)

Công thức 1: Công thức countifs với các tiêu chí khác

Kết quả hiển thị là 2 (“Cherries” và “Lemons”):

Ví dụ cách đếm ô tính nhiều tiêu chí khác nhau

Công thức 2. Công thức countifs với các tiêu chí giống nhau

Khi bạn muốn đếm các mục có tiêu chí giống nhau bạn phải cung cấp mỗi cặp criteria_range / criteria riêng lẻ. Trong bảng dữ liệu phía dưới để đếm các mục chứa 0 trong cột B và cột C cần áp dụng công thức như sau:

= COUNTIFS ($B$2: $B$7, “= 0”, $C$2: $C$7, “= 0”)

Kết quả hiển thị trả về là 1 (vì chỉ có “Grapes” có giá trị “0” trong cả hai cột)

hàm countifs với các tiêu chí giống nhau

Áp dụng công thức đơn giản với một criteria_range duy nhất như COUNTIFS(B2: C7, “= 0”) sẽ trả về kết quả khác, tổng số các ô tính trong khoảng từ B2:C7 đều chưa 0.

Cách đến số ô tính với nhiều tiêu chí (or logic)

Công thức thêm hai hoặc nhiều công thức countif hoặc countifs

Trong bảng dữ liệu bên dưới, nếu bạn muốn tính các đơn hàng đang ở trạng thái “Cancelled” và “Pending” bạn áp dụng công thức hàm countif bình thường và kết quả hiển thị.

Công thức lệnh: =COUNTIF($C$2:$C$11,”Cancelled”)+COUNTIF($C$2:$C$11,”Pending”)

Cách đếm số ô tính nhiều tiêu chí

Nếu mỗi hàm chứa nhiều hơn 1 điều kiện thì áp dụng hàm countifs thay vì sử dụng hàm countif. Cụ thể, khi đếm số đơn đặt hàng “Cancelled” và “Pending” đối với “Apples”, sử dụng công thức sau:

Công thức lệnh như sau:

=COUNTIFS($A$2:$A$11,”Apples”,$C$2:$C$11,”Cancelled”)+COUNTIFS($A$2:$A$11,”Apples”,$C$2:$C$11,”Pending”)

Kết quả cách đếm số ô tính nhiều tiêu chí

Cách sử dụng countifs với các ký tự đại diện

Trong công thức excel, một số những ký tự đại diện cơ bản bạn có thể sử dụng sau đây:

Dấu hỏi (?): Kết hợp được với bất kỳ ký tự đơn nào, sử dụng đếm các ô bắt đầu hoặc kết thúc bằng một ký tự nhất định.

Dấu hoa thị (*): Kết hợp được với bất kỳ dãy ký tự nào, sử dụng để đếm ô chứa một từ cụ thể hoặc một ký tự trong ô.

Nếu muốn đếm ô chứa dấu hỏi hoặc dấu hoa thị, gõ dấu ngã (~) trước dấu chấm hỏi hoặc hoa thị. Ví dụ bạn có một danh sách các dự án trong cột A. Bạn muốn biết có bao nhiêu dự án đã ký kết (chứa tên người trong cột B). Hãy thêm một điều kiện thứ 2 (End date trong cột D).

Lưu ý:

Cập nhật thông tin chi tiết về Cách Sử Dụng Định Dạng Có Điều Kiện Trong Microsoft 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!