Bạn có thể đã sử dụng mã hóa màu trong dữ liệu Excel của mình hoặc thấy nó trong sổ làm việc bạn phải sử dụng.
Nó là một cách phổ biến để trực quan hóa dữ liệu của bạn!
Mặc dù các ô màu là một cách tuyệt vời để làm nổi bật dữ liệu để nhanh chóng thu hút sự chú ý của ai đó, nhưng chúng không phải là một cách tuyệt vời để lưu trữ dữ liệu.
Thật không may, rất nhiều người dùng sẽ tô màu một ô để chỉ ra một số giá trị thay vì tạo một điểm dữ liệu khác với giá trị.
Ví dụ, họ có thể tô màu một ô màu xanh lá cây để chỉ ra một mục được phê duyệt thay vì tạo một điểm dữ liệu khác với văn bản được phê duyệt.Approved.
Điều này gây ra rất nhiều vấn đề khi bạn thực sự cần tìm hiểu có bao nhiêu mặt hàng đã được phê duyệt. Excel không cung cấp một cách tích hợp để đếm các ô màu.
Trong bài đăng này, tôi sẽ chỉ cho bạn 6 cách để tìm và đếm bất kỳ ô màu nào trong dữ liệu của bạn.6 ways to find and count any colored cells in your data.
Sử dụng lệnh tìm và chọn để đếm các ô màu
Excel có một tính năng tuyệt vời cho phép bạn tìm các ô dựa trên định dạng. Điều này bao gồm bất kỳ ô màu quá!
Bạn có thể tìm thấy tất cả các ô của một màu nhất định, sau đó đếm chúng.
Chuyển đến tab Trang chủ Nhấp vào lệnh Tìm & Chọn Sau đó chọn Tìm từ các tùy chọn.Home tab ➜ click on the Find & Select command ➜ then choose Find from the options.
Ngoài ra còn có một phím tắt bàn phím tuyệt vời cho việc này. Nhấn Ctrl + F để mở menu Tìm và thay thế.Ctrl + F to open the Find and Replace menu.
Nhấp vào mũi tên nhỏ xuống ở nút định dạng và chọn Định dạng từ ô.Format button and select Choose Format From Cell.
Nhấp vào phần chính của nút Định dạng sẽ mở menu Định dạng Tìm nơi bạn có thể chọn bất kỳ kết hợp định dạng nào để tìm kiếm.Format button will open up the Find Format menu where you can select any combination of formatting to search for.
Điều này là hoàn hảo nếu bạn biết chính xác bạn đang tìm kiếm màu nào, nhưng thường thì bạn sẽ được phục vụ tốt nhất bằng cách đặt định dạng bằng ví dụ. Định dạng có thể khác nhau và điều này có thể khiến bạn bỏ lỡ việc tìm đúng dữ liệu!
Khi bạn nhấp vào mũi tên nhỏ bên trong nút định dạng, nó sẽ tiết lộ nhiều tùy chọn hơn bao gồm khả năng đặt định dạng bằng cách chọn ô.Format button, it will reveal more options including the ability to set the format by selecting a cell.
Khi bạn đã chọn định dạng, hãy nhấp vào nút Tìm tất cả.
Phần dưới của hộp thoại Tìm và thay thế sẽ hiển thị tất cả các ô được tìm thấy phù hợp với định dạng và ở phía dưới bên trái, bạn sẽ tìm thấy số đếm.
Nhấn Ctrl + A để chọn tất cả các ô và sau đó nhấn nút Đóng và sau đó bạn có thể thay đổi màu của tất cả các ô này hoặc thay đổi bất kỳ định dạng nào khác.Ctrl + A to select all the cells and then press the Close button and you can then change the color of all these cells or change any other formatting.
Nếu bạn chỉ muốn trả lại các ô trong một cột hoặc phạm vi nhất định, điều này là có thể. Chọn phạm vi trong trang tính trước khi nhấn nút Tìm tất cả để giới hạn tìm kiếm trong lựa chọn.
Ưu điểm
- Dễ sử dụng.
- Bạn có thể sử dụng điều này để tìm kiếm các loại định dạng khác và không chỉ lấp đầy màu sắc.
- Bạn có thể sử dụng điều này để tìm kiếm một phạm vi đã chọn, toàn bộ trang tính hoặc toàn bộ sổ làm việc.
Nhược điểm
- Giải pháp này không năng động và sẽ cần được lặp lại mỗi khi bạn muốn có được số lượng.
Sử dụng các bộ lọc và chức năng phụ để đếm các ô màu
Phương pháp này sẽ dựa vào thực tế là bạn có thể lọc dựa trên màu ô.
Bước đầu tiên, bạn sẽ cần thêm các bộ lọc vào dữ liệu của mình.
Chọn bạn dữ liệu và chuyển đến tab Dữ liệu sau đó chọn lệnh Filter.Data tab then choose the Filter command.
Điều này sẽ thêm biểu tượng sắp xếp và lọc vào từng tiêu đề cột của dữ liệu của bạn và những điều này sẽ cho phép bạn lọc dữ liệu của mình nhiều cách khác nhau.
Ngoài ra còn có một phím tắt bàn phím tiện dụng để thêm hoặc xóa các bộ lọc khỏi dữ liệu của bạn. Chọn dữ liệu của bạn và nhấn Ctrl + Shift + L trên bàn phím của bạn.Ctrl + Shift + L on your keyboard.
Một tùy chọn khác để thêm bộ lọc là biến dữ liệu của bạn thành bảng Excel. Tôi đã viết một bài viết tất cả về bảng excel và các tính năng tuyệt vời mà chúng đi kèm.
Bạn có thể chuyển đổi dữ liệu của mình thành một bảng với một trong hai phương thức này.
- Chọn một ô bên trong dữ liệu của bạn ➜ Chuyển đến tab Chèn ➜ Nhấp vào lệnh bảng.
- Chọn một ô bên trong dữ liệu của bạn ➜ Nhấn Ctrl + T trên bàn phím của bạn.
Bảng của bạn nên đi kèm với các bộ lọc theo mặc định. Nếu không, hãy chuyển đến tab bảng và kiểm tra tùy chọn nút Filter trong phần Tùy chọn kiểu bảng.Table tab and check the Filter Button option in the Table Style Options section.
= SUBTOTAL [ 3, Orders[Order ID] ]Bây giờ bạn có thể thêm công thức phụ ở trên để đếm các ô không trống trong đó ID thứ tự là cột chứa các ô màu bạn muốn đếm.SUBTOTAL formula to count the non empty cells where Order ID is the column which contains the colored cells you would like to count.
Đối số đầu tiên của hàm phụ cho biết nó sẽ trả về một số đếm trong khi đối số thứ hai cho nó biết những gì để đếm.SUBTOTAL function tells it to return a count while the second argument tells it what to count.
Thủ thuật đặc biệt ở đây là hàm phụ sẽ chỉ đếm các ô hiển thị, do đó, nó sẽ cập nhật số lượng dựa trên dữ liệu mà nó được lọc.SUBTOTAL function will only count visible cells, so it will update the count based on what data it is filtered on.
Điều này có nghĩa là bạn có thể lọc trên các ô màu và bạn sẽ nhận được số lượng các ô màu đó!
Bây giờ bạn có thể lọc dữ liệu của bạn theo màu sắc.
- Nhấp vào sắp xếp và lọc chuyển đổi cho cột chứa các ô màu.
- Chọn Bộ lọc theo màu từ các tùy chọn menu.Filter by Color from the menu options.
- Chọn màu bạn muốn lọc trên.
Bây giờ kết quả phụ sẽ cập nhật và bạn có thể nhanh chóng tìm thấy số lượng của các ô màu của mình.SUBTOTAL result will update and you can quickly find the count of your colored cells.
Nếu bạn điều chỉnh màu sắc, thêm hoặc xóa dữ liệu trong bảng. Bạn sẽ cần phải áp dụng lại các bộ lọc khi họ không cập nhật một cách linh hoạt.
Chuyển đến tab Dữ liệu và nhấp vào nút nộp lại trong phần Sắp xếp & Bộ lọc.Data tab and click on the Reapply button in the Sort & Filter section.
Ưu điểm
- Dễ sử dụng.
Nhược điểm
- Yêu cầu dữ liệu lọc thủ công.
- Bản cập nhật của các bộ lọc don don và bạn sẽ cần phải áp dụng lại chúng khi bạn thay đổi dữ liệu của mình.
- Vì số lượng dựa trên bộ lọc, kết quả có thể khác nhau đối với mỗi người dùng khi hợp tác trên sổ làm việc.
Sử dụng chức năng Get.Cell Macro4 để đếm các ô màu
Excel có một chức năng để có được màu lấp đầy của một ô, nhưng nó là hàm macro 4 kế thừa.
Những điều kiện Predate VBA và là ngôn ngữ kịch bản dựa trên công thức Excel.
Mặc dù chúng được coi là không dùng nữa, nhưng vẫn có thể sử dụng chúng bên trong trình quản lý tên.
Có chức năng Get.Cells macro4 sẽ trả về mã màu dựa trên màu lấp đầy của ô.GET.CELLS Macro4 function that will return a color code based on the fill color of the cell.
Bạn có thể tạo một phạm vi tương đối có tên sử dụng điều này bằng cách truy cập tab Công thức và nhấp vào Xác định tên.Formulas tab and clicking on Define Name.
Điều này sẽ mở ra menu Tên mới và bạn có thể xác định tham chiếu.
Đặt tên xác định của bạn một tên như ColorCode. Đây là cách bạn sẽ đề cập đến nó trong sổ làm việc.Name like ColorCode. This is how you will refer to it in the workbook.
= GET.CELL [ 38, Orders[@[Order ID]] ]Thêm công thức trên vào phần đề cập đến phần. Đối với công thức này, dữ liệu của bạn sẽ cần phải ở trong một bảng có tên các đơn đặt hàng có cột có tên ID thứ tự, nhưng bạn có thể thay đổi chúng để phù hợp với dữ liệu của mình.Orders with a column called Order ID, but you can change these to fit your data.
Công thức này sẽ luôn đề cập đến ô ID đặt hàng trong hàng hiện tại mà nó được tham chiếu.
= GET.CELL [ 38, $B3 ]Nếu dữ liệu của bạn không ở trong bảng, thì bạn có thể sử dụng công thức trên thay vào đó, trong đó B là cột chứa màu điền bạn muốn đếm. Điều này sử dụng một cột cố định và tham chiếu hàng tương đối để luôn luôn tham khảo cột B của hàng hiện tại.
= ColorCodeVới tên xác định, giờ đây bạn có thể tạo một cột khác bằng công thức trên trong dữ liệu của mình để tính toán mã màu cho mỗi hàng.
Kết quả sẽ là giá trị số nguyên dựa trên màu điền của ô trong cột ID thứ tự.Order ID column.
= COUNTIFS [ Orders[ColorCode], B14 ]Bây giờ bạn có thể đếm số hoặc ô màu bằng cách sử dụng công thức đếm trên.
Công thức này sẽ đếm các ô trong cột ColorCode nếu chúng có mã phù hợp. Trong ví dụ này, nó đếm tất cả 10 giá trị tương ứng với màu xanh lá cây.10 values which correspond to the green color.
Ưu điểm
- Bạn có thể tính toán màu điền cho từng hàng dữ liệu và nó sẽ cập nhật linh hoạt khi bạn thay đổi dữ liệu hoặc điền màu của dữ liệu.
Nhược điểm
- Yêu cầu dữ liệu lọc thủ công.
- Bản cập nhật của các bộ lọc don don và bạn sẽ cần phải áp dụng lại chúng khi bạn thay đổi dữ liệu của mình.
- Vì số lượng dựa trên bộ lọc, kết quả có thể khác nhau đối với mỗi người dùng khi hợp tác trên sổ làm việc.
- Sử dụng chức năng Get.Cell Macro4 để đếm các ô màu
- Excel có một chức năng để có được màu lấp đầy của một ô, nhưng nó là hàm macro 4 kế thừa.
- Những điều kiện Predate VBA và là ngôn ngữ kịch bản dựa trên công thức Excel.
Mặc dù chúng được coi là không dùng nữa, nhưng vẫn có thể sử dụng chúng bên trong trình quản lý tên.
Có chức năng Get.Cells macro4 sẽ trả về mã màu dựa trên màu lấp đầy của ô.
Bạn có thể tạo một phạm vi tương đối có tên sử dụng điều này bằng cách truy cập tab Công thức và nhấp vào Xác định tên.
Điều này sẽ mở ra menu Tên mới và bạn có thể xác định tham chiếu.
Đặt tên xác định của bạn một tên như ColorCode. Đây là cách bạn sẽ đề cập đến nó trong sổ làm việc.
= LAMBDA [ cell, GET.CELL [ 38, cell ] ]Thêm công thức trên vào phần đề cập đến phần. Đối với công thức này, dữ liệu của bạn sẽ cần phải ở trong một bảng có tên các đơn đặt hàng có cột có tên ID thứ tự, nhưng bạn có thể thay đổi chúng để phù hợp với dữ liệu của mình.Refers to section.
Công thức này sẽ luôn đề cập đến ô ID đặt hàng trong hàng hiện tại mà nó được tham chiếu.
= GETCOLORCODE [ [@[Order ID]] ]Nếu dữ liệu của bạn không ở trong bảng, thì bạn có thể sử dụng công thức trên thay vào đó, trong đó B là cột chứa màu điền bạn muốn đếm. Điều này sử dụng một cột cố định và tham chiếu hàng tương đối để luôn luôn tham khảo cột B của hàng hiện tại.
= COUNTIFS [ Orders[ColorCode], B14 ]Với tên xác định, giờ đây bạn có thể tạo một cột khác bằng công thức trên trong dữ liệu của mình để tính toán mã màu cho mỗi hàng.
Ưu điểm
- Kết quả sẽ là giá trị số nguyên dựa trên màu điền của ô trong cột ID thứ tự.
- Bây giờ bạn có thể đếm số hoặc ô màu bằng cách sử dụng công thức đếm trên.
Nhược điểm
- Công thức này sẽ đếm các ô trong cột ColorCode nếu chúng có mã phù hợp. Trong ví dụ này, nó đếm tất cả 10 giá trị tương ứng với màu xanh lá cây.
- Bản cập nhật của các bộ lọc don don và bạn sẽ cần phải áp dụng lại chúng khi bạn thay đổi dữ liệu của mình.
- Vì số lượng dựa trên bộ lọc, kết quả có thể khác nhau đối với mỗi người dùng khi hợp tác trên sổ làm việc.
- Vì số lượng dựa trên bộ lọc, kết quả có thể khác nhau đối với mỗi người dùng khi hợp tác trên sổ làm việc.
- Sử dụng chức năng Get.Cell Macro4 để đếm các ô màu
Excel có một chức năng để có được màu lấp đầy của một ô, nhưng nó là hàm macro 4 kế thừa.
Function COLORCOUNT[CountRange As Range, FillCell As Range]
Dim FillColor As Integer
Dim Count As Integer
FillColor = FillCell.Interior.ColorIndex
For Each c In CountRange
If c.Interior.ColorIndex = FillColor Then
Count = Count + 1
End If
Next c
COLORCOUNT = Count
End FunctionƯu điểm
- Những điều kiện Predate VBA và là ngôn ngữ kịch bản dựa trên công thức Excel.
- Mặc dù chúng được coi là không dùng nữa, nhưng vẫn có thể sử dụng chúng bên trong trình quản lý tên.
- Có chức năng Get.Cells macro4 sẽ trả về mã màu dựa trên màu lấp đầy của ô.
Nhược điểm
- Sử dụng VBA yêu cầu tệp được lưu ở định dạng XLSM.
- Ít người dùng thân thiện để thiết lập.
Sử dụng các tập lệnh văn phòng để đếm các ô màu
Các kịch bản văn phòng là phương pháp hoàn toàn mới để tự động hóa các tác vụ trong Excel.
Nhưng nó chỉ có sẵn cho Excel Online và chỉ có kế hoạch doanh nghiệp. Nếu bạn có một kế hoạch doanh nghiệp, thì bạn rất tốt để đi với phương pháp này!
Đầu tiên, bạn sẽ cần thiết lập hai ô được đặt tên mà mã sẽ đề cập đến.
Chọn bất kỳ ô và nhập tên như Colorcount vào hộp tên và nhấn Enter. Điều này sẽ tạo ra một phạm vi được đặt tên có thể được đề cập trong mã.ColorCount into the name box and press Enter. This will create a named range which can be referred to in the code.
Điều này có nghĩa là chúng tôi có thể di chuyển ô và mã sẽ đề cập đến vị trí mới của nó.
Bạn cũng sẽ cần tạo ra một phạm vi màu được đặt tên cho đầu vào của màu để đếm.Color named range for the input of the color to count.
Bây giờ bạn có thể tạo một kịch bản văn phòng mới. Chuyển đến tab Tự động hóa trong Excel Online và nhấp vào lệnh tập lệnh mới.Automate tab in Excel online and click on the New Script command.
function main[workbook: ExcelScript.Workbook] {
let selectedSheet = workbook.getWorksheet["Sheet1"];
let myID = selectedSheet.getRange["Orders[Order ID]"];
let myIDCount = selectedSheet.getRange["Orders[Order ID]"].getCellCount[];
let myColorCode = selectedSheet.getRange["Color"].getFormat[].getFill[].getColor[];
let counter = 0;
for [let i = 0; i < myIDCount; i++] {
if [myID.getCell[i, 0].getFormat[].getFill[].getColor[] == myColorCode ] {
counter = counter + 1;
}
}
selectedSheet.getRange["ColorCount"].setValue[counter];
}Điều này sẽ mở ra Trình chỉnh sửa mã và bạn có thể dán vào mã trên và lưu tập lệnh.Code Editor and you can paste in the above code and save the script.
Nhấn nút Run và mã sẽ thực thi và sau đó điền vào phạm vi được đặt tên với số lượng của các ô màu được tìm thấy trong cột ID thứ tự.Run button and the code will execute and then populate the ColorCount named range with the count of the colored cells found in the Order ID column.
Ưu điểm
- Đây là phương pháp mới nhất và sẽ được hỗ trợ trong tương lai.
- Kịch bản có thể được chạy từ Power Automate cho giải pháp không nhấp chuột.
Nhược điểm
- Phương pháp này rất khó để thiết lập.
- Điều này đòi hỏi giấy phép doanh nghiệp và bạn cần sử dụng Excel Online.
Kết luận
Hy vọng một ngày nào đó Microsoft sẽ tạo ra một chức năng Excel tiêu chuẩn có thể trả về các thuộc tính từ một ô như màu lấp đầy của nó.
Cho đến khi điều đó xảy ra, ít nhất bạn có một vài tùy chọn cho phép bạn tìm thấy số lượng tế bào màu mà không cần đếm thủ công chúng.
Bạn có một phương pháp ưa thích không được liệt kê ở đây? Hãy cho tôi biết ở phần bình luận!
Thông tin về các Tác giả
John là một MVP của Microsoft và chuyên gia tính toán đủ điều kiện với hơn 15 năm kinh nghiệm. Ông đã làm việc trong một loạt các ngành công nghiệp, bao gồm bảo hiểm, công nghệ quảng cáo và gần đây nhất là tư vấn nền tảng quyền lực. Anh ấy là một người giải quyết vấn đề sắc sảo và có niềm đam mê sử dụng công nghệ để làm cho các doanh nghiệp hiệu quả hơn.