Phân loại chuỗi dựa trên từ khóa trong Excel
Phân loại chuỗi dựa trên một số từ là một trong những nhiệm vụ cơ bản của tôi trong phân tích dữ liệu. Ví dụ: trong một cuộc khảo sát nếu bạn hỏi mọi người xem họ thích gì về một chiếc điện thoại thông minh cụ thể, những câu trả lời giống nhau sẽ có nhiều từ khác nhau. Đối với máy ảnh, họ có thể sử dụng các từ như ảnh, video, ảnh tự chụp, v.v. Tất cả đều ngụ ý máy ảnh. Vì vậy, việc phân loại các câu trước để có được một số thông tin có ý nghĩa là rất quan trọng.
Trong bài viết này, chúng ta sẽ học cách phân loại trong excel bằng từ khóa.
Hãy lấy ví dụ về cuộc khảo sát mà chúng ta đã nói đến.
Ví dụ: Phân loại dữ liệu được thu thập từ khảo sát trong Excel
Vì vậy, chúng tôi đã thực hiện một cuộc khảo sát về xyz điện thoại thông minh mới của mình. Chúng tôi đã hỏi khách hàng của mình những gì họ thích về điện thoại xyz và ghi lại phản hồi của họ trong excel. Bây giờ chúng ta cần biết ai đã thích màn hình LED, loa và camera của chúng ta.
Đối với điều này, chúng tôi đã chuẩn bị một danh sách các từ khóa có thể đề cập đến một danh mục, như bạn có thể thấy trong hình ảnh bên dưới. Để hiểu, tôi đã giữ nó nhỏ.
Phản hồi nằm trong phạm vi A2: A9, từ khóa ở E2: E10 và Danh mục ở F2: F10.
Công thức chung để tạo danh mục sẽ là:
{=INDEX(Category,MATCH(TRUE,ISNUMBER(SEARCH(words,sentence)),0))}
Lưu ý dấu ngoặc nhọn, nó là một công thức mảng. Cần nhập bằng CTRL + SHIFT + ENTER.
Category: Là phạm vi chứa danh sách các danh mục. Các câu hoặc phản hồi sẽ được phân loại theo các giá trị này. Trong trường hợp của chúng tôi, nó là F2: F10.
Từ: là phạm vi chứa danh sách các từ khóa hoặc cụm từ.
Chúng sẽ được tìm kiếm trong các câu. Đây là E2: E10.
Câu: nó là câu sẽ được phân loại. Nó là một ô tương đối duy nhất.
Vì bây giờ chúng ta đã biết từng biến và hàm được sử dụng để phân loại trong excel, hãy triển khai nó trên ví dụ của chúng ta.
Trong ô B2, viết công thức này và nhấn CTRL + SHIFT + ENTER.
{=INDEX($F$2:$F$10,MATCH(TRUE,ISNUMBER(SEARCH($E$2:$E$10,A2)),0))}
Sao chép công thức để xem danh mục của từng phản hồi.
Chúng tôi cần phải cố định danh sách các từ và danh mục, chúng không được thay đổi khi chúng tôi sao chép công thức, do đó tôi đã đưa ra tham chiếu tuyệt đối về các từ khóa và danh mục. Trong khi chúng tôi muốn các câu thay đổi khi chúng tôi sao chép công thức, đó là lý do tại sao tôi đã sử dụng tham chiếu tương đối là A2.
Bây giờ bạn có thể chuẩn bị báo cáo có bao nhiêu người dùng thích màn hình LED, Loa và Máy ảnh.
Nó hoạt động như thế nào?
Hàm TÌM KIẾM tìm kiếm từng giá trị của từ khóa ($ E $ 2: $ E $ 10) trong câu A2. Nó trả về một mảng vị trí tìm thấy của từ hoặc #VALUE (đối với từ không tìm thấy). Cuối cùng, chúng ta sẽ có một mảng gồm 9 phần tử cho ví dụ này.
\ {# VALUE!; 5; #VALUE!; # VALUE!; # VALUE!; # VALUE!; # VALUE!; # VALUE!; # VALUE!}.
Tiếp theo chúng ta sử dụng Hàm ISNUMBER để chuyển mảng này thành dữ liệu hữu ích. Nó chuyển nó thành mảng TRUE và FALSE.
\ {FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}.
hàm MATCH tìm TRUE, trong mảng được kết quả và trả về chỉ số của TRUE được tìm thấy đầu tiên. là 2 cho trường hợp này.
INDEX ($ F $ 2: $ F $ 10, MATCH (TRUE, ISNUMBER (SEARCH ($ E $ 2: $ E $ 10, A2)), 0)): Tiếp theo, hàm INDEX xem xét vị trí thứ 2 trong danh mục ($ F $ 2: $ F $ 10) là Màn hình LED. Cuối cùng công thức này phân loại văn bản hoặc phản hồi này dưới dạng màn hình LED.
Phân biệt chữ hoa chữ thường:
Để làm cho hàm này phân biệt chữ hoa chữ thường, hãy sử dụng hàm TÌM thay vì hàm TÌM KIẾM. Hàm FIND có phân biệt chữ hoa chữ thường theo mặc định.
{=INDEX(Category,MATCH(TRUE,ISNUMBER(FIND(words,sentence)),0))}
Điểm yếu:
1.Nếu hai từ khóa được tìm thấy trong cùng một câu, câu sẽ được phân loại theo từ khóa đầu tiên trong danh sách.
Chụp văn bản trong một từ khác. Giả sử chúng ta đang tìm kiếm LAD trong một phạm vi. Khi đó các từ chứa LAD sẽ được tính. Ví dụ, Ladders sẽ được tính cho LAD vì nó chứa LAD trong đó. Vì vậy, hãy cẩn thận về nó. Cách tốt nhất là chuẩn hóa dữ liệu của bạn nhiều nhất có thể.
Vì vậy, đây là một hướng dẫn nhanh về cách phân loại dữ liệu trong excel. Tôi đã cố gắng giải thích nó càng đơn giản càng tốt. Vui lòng cho tôi biết nếu bạn có bất kỳ nghi ngờ nào về bài viết này hoặc bất kỳ bài viết nào liên quan đến excel.
Tải xuống tệp:
: Đây là một chức năng cần thiết khác của bảng điều khiển. Điều này giúp bạn tổng hợp các giá trị trên các điều kiện cụ thể.