Cập nhật thông tin chi tiết về Cách Xác Định Đơn Giá Theo Điều Kiện Trên Excel mới nhất trên website Trucbachconcert.com. 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.
Trong bài viết này, Gitiho mang đến cho các bạn cách dùng các hàm LOOKUP nhiều điều kiện để xác định đơn giá theo nhiều điều kiện trên Excel mới nhất và hiệu quả nhất.
Đăng ký ngay khóa học Tuyệt đỉnh Excel – Trở thành bậc thầy Excel trong 16 giờ
Xác định đơn giá theo điều kiện với INDEX và MATCH thay thế hàm LOOKUP nhiều điều kiện
Thay vì phải nhớ hai loại hàm VLOOKUP và HLOOKUP ( hàm LOOKUP nhiều điều kiện) thì INDEX và MATCH cũng là hai hàm vô cùng hữu hiệu để xác định đơn giá theo điều kiện.
Những điều chúng ta cần lưu ý khi sử dụng INDEX và MATCH là:
Xác định dòng dựa theo hàm Match theo “Mã công ty”
Xác định cột dưa theo hàm INDEX theo “Mã hàng”
Để xác định vùng dữ liệu thì ta cần tính đơn giá, bởi vậy mà ta cần xét vùng B13:F17 là Bảng đơn giá.
Ta sẽ sử dụng cấu trúc cụ thể qua hàm sau:
INDEX(Bảng dữ liệu, MATCH(Mã công ty, Cột Mã công ty,0),MATCH(Mã hàng, dòng Mã hàng,0))
Qua đó ta sẽ áp dụng toạ độ tham chiếu:
G3=INDEX($B$13:$F$17,MATCH(C3,$B$13:$B$17,0),MATCH(B3,$B$13:$F$13,0))
Kết hợp hàm LOOKUP nhiều điều kiện: Hàm VLOOKUP và MATCH
Chúng ta đã biết rằng hàm VLOOKUP là hàm có chức năng tham chiếu dữ liệu theo nhiều điều kiện. Vậy sẽ thế nào nếu như ta dùng VLOOKUP và hàm MATCH để thực hiện chung một nghiệm vụ.
Theo như nguyên lý của VLOOKUP thì giá trị cần tìm sẽ phải nằm ở cột đầu tiên, bởi vậy ta cần xếp Mã công ty ngay cột đầu tiên trong bảng đơn giá.
Tiếp theo, để xác định mã hàng nằm ở cột bao nhiêu thì ta dùng hàm MATCH để xét giá trị Mã hàng.
Cấu trúc hàm sẽ là:
VLOOKUP(Mã công ty, Bảng đơn giá, MATCH(mã hàng, dòng mã hàng, 0), 0)
Thay toạ độ tham chiếu vào, ta có.
Kết hợp hàm LOOKUP nhiều điều kiện: Hàm HLOOKUP và MATCH
Tương tự như VLOOKUP thì HLOOKUP cũng là hàm để tham chiếu dữ liệu nhưng chỉ khác về hướng tham chiếu. Bên cạnh đó ta cũng cần lưu ý một số điều kiện khi chuyển sang HLOOKUP.
Hàm HLOOKUP sẽ tìm kiếm ở dòng đầu tiên của bảng tìm kiếm, bởi thế mà dòng Mã hàng ở dòng 13 sẽ là dòng đầu tiên của vùng bảng thuộc đơn giá hàng hoá.
Tới đây ta sẽ cần căn cứ vào mã công ty để xác định dạng, qua đó ta dùng hàm MATCH để tìm theo Mã công ty.
Cấu trúc hàm sẽ là:
HLOOKUP(Mã hàng, Bảng đơn giá, MATCH(Mã công ty, cột Mã công ty, 0), 0)
Thay vào nội dung tham chiếu sẽ là:
G3=VLOOKUP(C3,$B$14:$F$17,MATCH(B3,$B$13:$F$13,0),0)
Sử dụng hàm SUMPRODUCT
Có thể nói hàm SUMPRODUCT là hàm cực kỳ linh động khi có thể dùng cho rất nhiều trường hợp khác nhau, trường hợp xác định đơn giá theo điều kiện cũng không phải ngoại lệ.
Điều ta cần để ý ở đây là ta dùng hàm SUMPRODUCT khi bảng đơn giá không có các mã trùng nhau, Bên cạnh đó là cần xét 2 điêu kiện mảng 2 chiều. Như ở bảng tính này là Mã hàng thuộc vùng C13:F13 và Mã công ty thuộc vùng B14:B17, tương tự như vậy ở vùng C14:F17.
Áp dụng công thức ta sẽ có.
G3=SUMPRODUCT(($B$14:$B$17=C3)*($C$13:$F$13=B3)*$C$14:$F$17)
Với một điều kiện thực hiện yêu cầu mà chúng ta có tới 4 cách để giải quyết vấn đề khác nhau, tuỳ thuộc vào sự hiểu biết và trình độ của người dùng Excel. Công dụng của hàm LOOKUP nhiều điều kiện để tính đơn giá theo nhiều điều kiện là quan trọng và hiệu quả. Ngoài ra, bạn đọc cũng có thể thấy được các loại hàm trong Excel luôn có thể biến đổi và thậm chí là kết hợp cùng nhau để hoá giải một vấn đề, thật kỳ diệu phải không nào!
Hướng dẫn cách dùng hàm index trong Excel chi tiết nhất
Hướng dẫn cách copy chỉ những giá trị hiển thị sau khi lọc trong Excel
Hướng dẫn cách sắp xếp ngày trong Excel
Hướng dẫn cách dùng hàm VLOOKUP qua các ví dụ từ cơ bản đến nâng cao
Hướng dẫn cách sử dụng hàm IF với nhiều điều kiện: AND, OR, hàm IF lồng nhau và hơn thế
Với sứ mệnh: ” Mang cơ hội phát triển kỹ năng, phát triển nghề nghiệp tới hàng triệu người “, đội ngũ phát triển đã và đang làm việc với những học viện, trung tâm đào tạo, các chuyên gia đầu ngành để nghiên cứu và xây dựng lên các chương trình đào tạo từ cơ bản đến chuyên sâu xung quanh các lĩnh vực: Tin học văn phòng, Phân tích dữ liệu, Thiết kế, Công nghệ thông tin, Kinh doanh, Marketing, Quản lý dự án…
50+ khách hàng doanh nghiệp lớn trong nhiều lĩnh vực như: Vietinbank, Vietcombank, BIDV, VP Bank, TH True Milk, VNPT, FPT Software, Samsung SDIV, Ajinomoto Việt Nam, Messer,…
Gitiho tự hào khi được đồng hành cùng:
Hướng Dẫn 4 Cách Xác Định Đơn Giá Theo Nhiều Điều Kiện Trong Excel
Ví dụ chúng ta có bảng dữ liệu bán hàng như sau:
Đơn giá của mỗi sản phẩm được xác định dựa theo 2 yếu tố: Mã hàng và Mã công ty.
Yêu cầu là hãy xác định đơn giá dựa vào mã công ty kết hợp với mã hàng tra trong bảng đơn giá để có giá trị tương ứng tại vùng G3:G9.
Cách thứ 1: Dùng hàm VLOOKUP kết hợp hàm MATCH
Khi nghĩ tới việc tham chiếu 1 giá trị (cụ thể ở đây là đơn giá) theo nhiều điều kiện (từ 2 điều kiện trở lên), chúng ta nghĩ tới việc sử dụng hàm VLOOKUP kết hợp hàm MATCH.
Trọng tâm của cách làm này là các bạn phải xác định VLOOKUP theo giá trị nào, MATCH theo giá trị nào. Để làm điều đó, chúng ta chú ý cấu trúc của bảng đơn giá:
Kết quả cần tìm của hàm VLOOKUP là đơn giá của các mã hàng, tương ứng theo cột thứ mấy trong bảng. Vì vậy để xác định cột thứ mấy, chúng ta dùng hàm MATCH để xét giá trị Mã hàng.
Cấu trúc như sau: VLOOKUP(Mã công ty, Bảng đơn giá, MATCH(mã hàng, dòng mã hàng, 0), 0)
Khi thay các nội dung thành tọa độ tham chiếu ta có:
G3=VLOOKUP(C3,$B$14:$F$17,MATCH(B3,$B$13:$F$13,0),0)
Hàm HLOOKUP cũng là 1 hàm tìm kiếm tương tự với VLOOKUP, chỉ thay đổi về phương hướng, chiều tìm kiếm. Trong trường hợp này chúng ta biện luận như sau:
Đối tượng tìm kiếm của hàm HLOOKUP phải nằm ở dòng đầu tiên của bảng tìm kiếm. Vì vậy chúng ta thấy dòng Mã hàng (dòng 13) là dòng đầu tiên của vùng bảng đơn giá hàng hóa. Do đó đối tượng tìm kiếm của hàm HLOOKUP trong trường hợp này là Mã hàng.
Khi đó kết quả của hàm HLOOKUP sẽ lấy tương ứng xuống bao nhiêu dòng? Căn cứ vào mã công ty để xác định dòng. Vì vậy chúng ta dùng hàm MATCH tìm theo Mã công ty.
Cấu trúc như sau: H LOOKUP(Mã hàng, Bảng đơn giá, MATCH(Mã công ty, cột Mã công ty, 0), 0)
Khi thay các nội dung thành tọa độ tham chiếu ta có:
G3=VLOOKUP(C3,$B$14:$F$17,MATCH(B3,$B$13:$F$13,0),0)
Bạn có biết rằng dùng Index + Match thì tốt hơn dùng Vlookup hay Hlookup? Nếu không tin thì bạn hãy xem lại bài viết:
Khi nhắc tới INDEX MATCH, các bạn hãy ghi nhớ 3 yếu tố:
Vùng dữ liệu nào? Chúng ta cần tính đơn giá, do đó xét vùng B13:F17 là Bảng đơn giá.
Kết quả ở dòng nào? Xác định dòng dựa theo hàm MATCH, tìm theo Mã công ty.
Kết quả ở cột nào? Xác định cột dựa theo hàm MATCH, tìm theo Mã hàng.
Cấu trúc cụ thể: INDEX(Bảng dữ liệu, MATCH(Mã công ty, Cột Mã công ty,0), MATCH(Mã hàng, dòng Mã hàng,0))
Khi thay các nội dung thành tọa độ tham chiếu ta có:
G3=INDEX($B$13:$F$17,MATCH(C3,$B$13:$B$17,0),MATCH(B3,$B$13:$F$13,0))
Hàm SUMPRODUCT là một hàm sử dụng được trong rất nhiều trường hợp. Và chúng ta hoàn toàn có thể ứng dụng hàm này trong việc xác định đơn giá theo nhiều điều kiện. Cụ thể như sau:
Khi nào áp dụng được? Khi bảng đơn giá không có các mã trùng nhau.
Cách dùng: Xét 2 điều kiện dạng mảng 2 chiều: Mã hàng (vùng C13:F13) và Mã công ty (vùng B14:B17), kết quả tương ứng lấy trong vùng C14:F17
Khi thay các nội dung thành tọa độ tham chiếu ta có:
G3=SUMPRODUCT(($B$14:$B$17=C3)*($C$13:$F$13=B3)*$C$14:$F$17)
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!
Bạn đang xem bài viết Cách Xác Định Đơn Giá Theo Điều Kiện Trên Excel trên website Trucbachconcert.com. 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. Chúc bạn một ngày tốt lành!