6
Nhấp vào "Tìm tất cả" để tìm từ trong tất cả các ô. Tất cả các trường hợp của từ được hiển thị dưới nút "Tìm tất cả" trong một phần mới
Hoạt động từ trong ra ngoài, hàm MATCH khớp phạm vi với chính nó. Nghĩa là, chúng ta cung cấp cho hàm MATCH cùng một phạm vi cho giá trị tra cứu và mảng tra cứu (B5. F5)
Vì giá trị tra cứu chứa nhiều hơn một giá trị (một mảng), hàm MATCH trả về một mảng kết quả, trong đó mỗi số đại diện cho một vị trí. Trong ví dụ hiển thị, mảng trông như thế này
{1,2,1,2,2}
Bất cứ nơi nào "con chó" xuất hiện, chúng tôi thấy 2 và Bất cứ nơi nào "con mèo" xuất hiện, chúng tôi thấy 1. Đó là bởi vì hàm MATCH luôn trả về kết quả khớp đầu tiên, nghĩa là các lần xuất hiện tiếp theo của một giá trị đã cho sẽ trả về cùng một vị trí (đầu tiên)
Tiếp theo, mảng này được đưa vào hàm MODE. MODE trả về số xuất hiện thường xuyên nhất, trong trường hợp này là 2. Số 2 đại diện cho vị trí mà tại đó chúng ta sẽ tìm thấy giá trị xuất hiện thường xuyên nhất trong phạm vi
Cuối cùng, chúng ta cần trích xuất giá trị. Đối với điều này, chúng tôi sử dụng chức năng INDEX. Đối với mảng, chúng ta sử dụng dải giá trị (B5. F5). Số hàng được cung cấp bởi MODE
INDEX trả về giá trị ở vị trí 2, đó là "con chó"
ô trống
Để xử lý các ô trống, bạn có thể sử dụng công thức mảng sau đây, công thức này có thêm câu lệnh IF để kiểm tra các ô trống
Trong Microsoft Excel, chúng tôi làm việc với một lượng lớn dữ liệu. Trong một bảng dữ liệu lớn, thông thường, chúng ta thấy một số từ lặp đi lặp lại. Thật mệt mỏi khi tìm ra những từ lặp đi lặp lại đó một cách thủ công. Trong hướng dẫn này, chúng ta sẽ thảo luận về 11 phương pháp đếm từ lặp lại trong Excel với hình minh họa phù hợp
Mục lục
Tải sách bài tập thực hành
Tải xuống sách bài tập thực hành này để thực hành trong khi bạn đang đọc bài viết này
Đếm từ lặp đi lặp lại. xlsm
11 Phương Pháp Đếm Số Từ Lặp Lại Trong Excel
Chúng tôi có một bộ dữ liệu về một số người với loại trái cây yêu thích của họ. Chúng tôi sẽ sử dụng nó làm tập dữ liệu mẫu trong suốt bài viết
1. Đếm Số Từ Được Lặp Lại Sử Dụng Hàm COUNTIF
Hàm COUNTIF đếm thứ gì đó dựa trên tham chiếu đã cho
Chúng tôi sẽ thêm hai hàng vào tập dữ liệu cho phương pháp này. Bây giờ, chúng ta sẽ đếm xem Banana được lặp lại bao nhiêu lần trong tập dữ liệu
Bước 1
- Chuyển đến Ô C17 và nhập công thức bên dưới
=COUNTIF(C5:C14,C16)
Bước 2
Kết quả là 3, có nghĩa là Chuối có mặt hoặc lặp lại hai lần trong phạm vi đã chọn. Hàm COUNTIF này không phân biệt chữ hoa chữ thường
Đọc thêm. Cách đếm số trùng lặp trong cột trong Excel (3 cách)
2. Hàm COUNTIFS để đếm các bản sao trong Excel
Hàm COUNTIFS được sử dụng để áp dụng nhiều điều kiện thông qua nhiều phạm vi
Chúng tôi sẽ chỉ áp dụng một điều kiện duy nhất bằng hàm COUNTIFS
Bước 1
- Chuyển đến ô C17
- Viết công thức sau
=COUNTIFS(C5:C14,C16)
Bước 2
Giống như hàm COUNTIF, hàm này cũng không phân biệt chữ hoa chữ thường. Cả hai đều không thể phát hiện dữ liệu chữ thường của Ô C11
Nội dung liên quan. Cách đếm các giá trị trùng lặp chỉ một lần trong Excel (3 cách)
3. Hàm SUMPRODUCT của Excel để đếm số hạng lặp đi lặp lại
Hàm SUMPRODVEL cung cấp tổng các sản phẩm của một phạm vi nhất định
Chúng tôi sẽ sử dụng chức năng TÓM TẮT này ở đây để đếm các số hạng lặp lại của phạm vi đã chọn
Bước 1
- Chuyển đến ô C17
- Đặt công thức dưới đây
=SUMPRODUCT(--(C16=C5:C14))
Bước 2
Hàm TÓM TẮT này không thể phát hiện sự khác biệt về chữ hoa chữ thường như các phương pháp trước đó
Đọc thêm. Cách đếm số hàng trùng lặp trong Excel (4 phương pháp)
4. Đếm các bản sao phân biệt chữ hoa chữ thường trong Excel
Hàm SUM cộng các giá trị từ một dãy số đã cho trong công thức
Hàm EXACT so sánh các chuỗi văn bản với một tham chiếu. Trả về TRUE nếu cả hai đều giống nhau, FALSE nếu không
Chúng ta sẽ sử dụng kết hợp hàm SUM và EXACT để đếm số trùng lặp trong phần này
Bước 1
- Nhập ô C17
- Sao chép công thức bên dưới và dán vào ô đó
=SUM(--EXACT(C5:C14,C16))
Bước 2
Kết quả là 2. Nhưng trong các phương pháp trước, chúng tôi nhận được 3. Điều này là do hàm EXACT phân biệt chữ hoa chữ thường. Nhìn vào Ô C11, ký tự đầu tiên là chữ thường thay vì chữ hoa
Bước 3
- Chuyển đến Ô C11 và sửa đổi ký tự đầu tiên
Bây giờ, kết quả là 3
Nội dung liên quan. Cách đếm các bản sao dựa trên nhiều tiêu chí trong Excel
5. Sử dụng hàm SUM, LEN, SUBSTITUTE để đếm số lượng chuỗi văn bản
Hàm LEN đếm số ký tự của một từ
Hàm SUBSTITUTE thay thế một từ hiện tại bằng một từ mới
Chúng ta sẽ sử dụng kết hợp các hàm SUM, LEN và SUBSTITUTE để tìm và đếm các từ lặp lại
Bước 1
- Nhập công thức sau vào Ô C17
=SUM(LEN(C5:C14)-LEN(SUBSTITUTE(C5:C14,C16,"")))/LEN(C16)
Bước 2
Phương pháp này cũng phân biệt chữ hoa chữ thường
Chúng ta cũng có thể sử dụng hàm SUMPRODUCT thay vì hàm SUM và sẽ nhận được kết quả tương tự. Công thức sẽ là
=SUMPRODUCT(LEN(C5:C14)-LEN(SUBSTITUTE(C5:C14,C16,"")))/LEN(C16)
Phá vỡ công thứcxuống
- THAY THẾ (C5. C14,C16,””)
Nó sẽ thay thế giá trị của C16 bằng “” từ phạm vi của C5. C14
Kết quả. [ ,Táo, ,Dứa, Nho, Ổi, chuối, Ổi, Táo, Táo]
- =LEN(SUBSTITUTE(C5. C14,C16,””))
Điều này sẽ đếm độ dài của từng dữ liệu sau thao tác SUBSTITUTE
Kết quả. [0, 5, 0, 9, 6, 5, 6, 5, 5, 5]
Điều này sẽ tính độ dài của từng dữ liệu từ phạm vi C5. C14
Kết quả. [6, 5,6, 9, 6, 5, 6, 5, 5, 5]
- LEN(C5. C14)-LEN(THAY THẾ(C5. C14,C16,””))
Phép toán trừ được thực hiện ở đây
Kết quả. [6, 0, 6, 0, 0, 0, 0, 0, 0, 0]
- TỔNG(LEN(C5. C14)-LEN(THAY THẾ(C5. C14,C16,””)))
Tính tổng kết quả bị trừ
Kết quả. [12]
Tìm chiều dài của Ô C16
Kết quả. [6]
- TỔNG(LEN(C5. C14)-LEN(THAY THẾ(C5. C14,C16,””)))/LEN(C16)
Chia kết quả tổng cho độ dài của ô C16
Kết quả. [2]
Đọc thêm. Cách đếm số từ trong Excel bằng công thức (2 ví dụ hữu ích)
6. Đếm thứ tự xuất hiện của các từ lặp lại trong Excel
Chúng ta có thể đếm thứ tự của các từ được lặp lại bằng hàm COUNTIF. Một sự kết hợp của các tham chiếu ô tương đối, tuyệt đối và hỗn hợp được sử dụng trong công thức cho phương pháp này
Chúng tôi đã thêm một cột có tên Đặt hàng cho mục đích này
Bước 1
- Chuyển đến ô D5
- Viết công thức sau
________số 8_______
Bước 2
- Bây giờ, nhấn nút Enter và kéo biểu tượng Fill Handle
Thứ tự được hiển thị cho mỗi loại trái cây. Số lượng đơn đặt hàng tối đa là số lần lặp lại
Đọc thêm. Đếm thứ tự xuất hiện các bản sao trong Excel (4 phương pháp)
bài đọc tương tự
- Cách đếm các từ cụ thể trong một cột trong Excel (2 phương pháp)
- Đếm các bản sao trong Excel Pivot Table (2 cách dễ dàng)
- Cách đếm các giá trị trùng lặp trong nhiều cột trong Excel (6 cách)
7. Đếm các bản sao không có lần xuất hiện đầu tiên
Đôi khi chúng ta cần biết một từ được lặp lại bao nhiêu lần trừ lần xuất hiện đầu tiên. Thực hiện theo phương pháp này cho việc này
Bước 1
- Chuyển đến Ô C17 và đặt công thức sau
=COUNTIF(C5:C14,C16)-1
Bước 2
Nội dung liên quan. Cách đếm số lần xuất hiện mỗi ngày trong Excel (4 cách nhanh chóng)
8. Đếm tất cả các từ lặp lại trong một cột mà không có lần xuất hiện đầu tiên
Hàm IF là một trong những hàm được sử dụng nhiều nhất của Microsoft Excel. Chức năng này cung cấp để so sánh giữa các giá trị khác nhau theo mong đợi của chúng tôi
Chúng ta sẽ kết hợp hàm IF và COUNTIF trong phần này
Chúng tôi thêm một cột và một hàng mới vào tập dữ liệu cho mục đích này
Bước 1
- Đầu tiên, đi đến Ô D5
- Đặt công thức sau
=IF(COUNTIF($C$5:$C5,C5)>1,"Repeated"," ")
Bước 2
- Bây giờ, nhấn Enter và kéo biểu tượng Fill Handle
Công thức này kiểm tra xem có bất kỳ giá trị lặp lại nào trong phạm vi từ C5 đến C14 không. Và nếu tìm thấy bất kỳ giá trị lặp lại nào, thì hãy chỉ ra rằng từ lần xuất hiện thứ 2
Bước 3
- Bây giờ, hãy đặt một công thức tại Ô D16 để đếm số lần lặp lại
=COUNTIF(C5:C14,C16)0
Bước 4
Bây giờ, lấy tổng số lần lặp lại mà không có lần xuất hiện đầu tiên trong phạm vi chọn lọc
Đọc thêm. Excel Đếm số lần xuất hiện của mỗi giá trị trong một cột
9. Đếm tổng số trùng lặp trong một cột trong Excel
Hàm ROWS cung cấp số hàng từ tham chiếu hoặc mảng đã cho
Chúng ta sẽ kết hợp các hàm ROWS, IF và COUNTIF trong phần này
Bước 1
- Viết công thức sau vào Ô C16
=COUNTIF(C5:C14,C16)1
Bước 2
- Nhấn nút Enter ngay bây giờ
Chúng tôi nhận được tổng số từ được lặp lại từ phạm vi đã chọn với lần xuất hiện đầu tiên
Phân tích công thức
- COUNTIF(C5. $C$14,C5. $C$14)
Điều này sẽ tính C5. C14 từ phạm vi C5. C14
Kết quả. [3,3,3,1,1,2,3,2,3,3]
- NẾU(COUNTIF(C5. $C$14,C5. $C$14)=1,1,0)
Nó sẽ đại diện cho kết quả của COUNTIF đến 1 và 0. Đó là 1 trong hàm COUNTIF sẽ là 1 và phần còn lại sẽ là 0
Kết quả. [0,0,0,1,1,0,0,0,0,0]
- TỔNG(NẾU(ĐẾM(C5. $C$14,C5. $C$14)=1,1,0))
Nó sẽ tính tổng các giá trị của hàm IF
Kết quả. 2
Nó sẽ hiển thị số hàng từ phạm vi C5. C14
Kết quả. 10
- HÀNG($C$5. $C$14)-SUM(IF(COUNTIF(C5. $C$14,C5. $C$14)=1,1,0))
Điều này sẽ trừ giá trị của hàm SUM khỏi hàm ROWS
Kết quả. số 8
Đọc thêm. Cách bỏ qua khoảng trống và đếm số trùng lặp trong Excel (3 cách)
10. Kết hợp hàm SUMPRODVEL và COUNTIF để lấy tổng số từ lặp lại
Chúng ta sẽ chỉ cần kết hợp các hàm SUMPRODUCT và COUNTIF ở đây để có được tổng số từ lặp lại ở đây
Bước 1
- Chuyển đến Ô C16
- Sao chép và dán công thức dưới đây
=COUNTIF(C5:C14,C16)2
Bước 2
Chúng tôi nhận được tổng số từ lặp đi lặp lại
Đọc thêm. Công thức Excel để đếm các từ cụ thể trong một ô (3 ví dụ)
11. VBA để đếm tổng số từ lặp lại
Chúng tôi sẽ áp dụng mã VBA để lấy tổng số từ lặp lại
Bước 1
- Chuyển đến tab Nhà phát triển trước
- Nhấp vào Record Macro và đặt tên cho macro và nhấn OK
Bước 2
- Bây giờ, nhấn tùy chọn Macros
- Chọn macro mong muốn và Bước vào nó
Bước 3
- Bây giờ, đặt đoạn mã sau vào mô-đun lệnh
Sub Count_Repeated_Words()
Range("C16").Formula = "=SUMPRODUCT(--(COUNTIF(C5:C14,C5:C14)>1))"
End Sub
Bước 4
- Cuối cùng nhấn F5 để chạy code
Bây giờ, công thức đã đề cập được đặt trên Ô C16. Và chúng tôi nhận được tổng số từ lặp lại
Đọc thêm. VBA Excel để đếm các bản sao trong một cột (Phân tích đầy đủ)
Phần kết luận
Trong bài viết này, chúng tôi đã thảo luận về cách đếm các từ lặp lại trong Excel. Tôi hy vọng điều này sẽ đáp ứng nhu cầu của bạn. Vui lòng xem trang web của chúng tôi Exceldemy. com và đưa ra đề xuất của bạn trong hộp bình luận
