›10:20:04 10-11-2023 | Lượt xem: 317
Microsoft Excel là một công cụ tuyệt vời để làm việc với các con số, nhưng nó có thể trở nên khó khăn khi đếm các từ cụ thể trong bảng tính của bạn. Nếu bạn từng gặp khó khăn với các công thức phức tạp của Excel cho nhiệm vụ này thì có một giải pháp hiệu quả - tạo một hàm tùy chỉnh giúp việc phân tích tần suất văn bản trở nên dễ dàng.
Excel cung cấp nhiều cách tiếp cận khác nhau để truy xuất danh sách các giá trị duy nhất và lấy số lượng văn bản cụ thể trong phạm vi mục tiêu. Tuy nhiên, những phương pháp này thường yêu cầu sử dụng các hàm Excel phức tạp, có thể không trực quan và thân thiện với mọi người.
Với chức năng tùy chỉnh của chúng tôi, bạn có thể hoàn thành cả hai nhiệm vụ trong một lần:
Nhận danh sách tất cả các giá trị riêng biệt (từ và số) xuất hiện trong một phạm vi hoặc ô nhất định.
Tìm số lần mỗi giá trị xảy ra.
Để lấy hàm trong Excel của bạn, chỉ cần thêm mã của nó vào Trình soạn thảo VBA như được giải thích trong Cách chèn mã VBA trong Excel .
Chức năng tùy chỉnh để thực hiện phân tích văn bản theo tần suất từ
Hàm FreqWords(Data_range As Range, Tùy chọn Delimiter As String ) As Variant () Dim rCell As Range Dim text As String , sWord As String , PuncChars() As Variant Dim arr() As String , arr2() As Variant , arr3() As Variant , i As Long , j As Variant , y As Variant PuncChars = Array( "." , "," , ";" , ` ` , "'" , " ! " , "#" , _ "$" , "%" , "&" , "(" , ")" , "-" , "_" , "--" , "+" , _ "=" , "~" , "/" , "\" , "{" , "}" , "[" , "]" , "" " " , "?" , "*" ) ' dấu phân cách mặc định là khoảng trắng Nếu Dấu phân cách = "" Hoặc Dấu phân cách = " " Thì Dấu phân cách = " " ' nối phạm vi thành một chuỗi văn bản For Each rCell In Data_range ' xóa khoảng trắng thừa text = text & " " & WorksheetFunction.Trim(rCell) Next rCell ' xóa dấu câu For y = 0 To UBound (PuncChars) text = Thay thế( text, PuncChars(y), "" ) Tiếp theo y Khác cho mỗi rCell trong Data_range text = text & Delimiter & WorksheetFunction.Trim(rCell) Tiếp theo rCell End If ' tạo một mảng các giá trị văn bản arr() = Split(text, Delimiter) ' tạo một mảng không trùng lặp arr2 = ArrayUnique(arr) ' tạo một mảng 2D để đếm ReDim arr3( UBound(arr2) - 1, 1) For i = 1 Đến UBound (arr2) arr3(i - 1, 0) = arr2(i) Tiếp theo i For j = 0 Đến UBound (arr3) arr3(j, 1) = Application. Count(Application.Match(arr, Array(arr3(j, 0)), 0)) Next j FreqWords = ArraySort(arr3, 1) End Function Function ArrayUnique( ByVal aArrayIn As Variant ) As Variant ' Hàm này loại bỏ các giá trị trùng lặp khỏi một mảng một chiều Dim aArrayOut() As Variant Dim bFlag As Boolean Dim vIn As Variant Dim vOut As Variant Dim i%, j%, k% ReDim aArrayOut( LBound (aArrayIn) To UBound (aArrayIn)) i = LBound (aArrayIn) j = i Với mỗi vIn Trong aArrayIn For k = j To i - 1 Nếu LCase(vIn) = LCase(aArrayOut(k)) Then bFlag = True : Thoát Cho Tiếp theo Nếu không phải bFlag Thì aArrayOut(i) = vIn: i = i + 1 bFlag = False Tiếp theo Nếu i <> UBound (aArrayIn) Then ReDim Giữ nguyên aArrayOut( LBound (aArrayIn) To i - 1) ArrayUnique = aArrayOut End Hàm Function ArraySort(SourceArr As Variant , ByVal n As Integer ) As Variant ' sắp xếp một mảng hai chiều theo cột N ' Số cột bắt đầu từ 0 Nếu n > UBound (SourceArr, 2) Hoặc n < LBound (SourceArr, 2) Then _ MsgBox "Không có cột nào như vậy trong mảng!" , vbCritical: Thoát hàm Dim Check As Boolean , iCount As Integer , jCount As Integer , nCount As Integer ReDim tmpArr( UBound (SourceArr, 2)) As Variant Do Until Check Check = True For iCount = LBound (SourceArr, 1) To UBound (SourceArr, 1) - 1 ' Dấu "<" nghĩa là sắp xếp theo thứ tự giảm dần Nếu val(SourceArr(iCount, n)) < val( SourceArr(iCount + 1, n)) Sau đó , Với jCount = LBound (SourceArr, 2) Đến UBound (SourceArr, 2) tmpArr(jCount) = SourceArr(iCount, jCount) SourceArr(iCount, jCount) = SourceArr(iCount + 1, jCount) SourceArr(iCount + 1, jCount) = tmpArr(jCount) Kiểm tra = Sai Kết thúc tiếp theo Nếu vòng lặp tiếp theo ArraySort = Hàm kết thúc SourceArr
Trước khi đi sâu vào cách sử dụng hàm FreqWords tùy chỉnh , trước tiên chúng ta hãy xem cú pháp của nó:
Tần số từ(dải_dữ liệu, [dấu phân cách])
Data_range (bắt buộc) - một ô hoặc một dải ô để tìm kiếm từ.
Dấu phân cách (tùy chọn) - dấu phân cách từ. Nếu bị bỏ qua, dấu phân cách mặc định là khoảng trắng.
Và đây là một số điều quan trọng cần lưu ý:
Trường hợp không nhạy cảm. Hàm FreqWords không phân biệt chữ hoa chữ thường, nghĩa là nó bỏ qua chữ cái khi đếm tần số từ. Tuy nhiên, nó không thay đổi kiểu chữ của các từ trong phạm vi nguồn. Ví dụ: nếu các từ "Phố", "phố" và "ĐƯỜNG" xuất hiện theo trình tự trong phạm vi dữ liệu đã chỉ định thì hàm sẽ coi chúng là cùng một từ và bao gồm từ được tìm thấy đầu tiên ("Phố") trong đầu ra với tần số 3.
Đầu ra được sắp xếp theo tần số. Danh sách tần suất từ do hàm tạo ra sẽ được sắp xếp theo thứ tự giảm dần theo số từ. Điều này cho phép bạn xác định nhanh những từ xuất hiện thường xuyên nhất.
Sau khi bạn đã thêm hàm FreqWords vào Excel, quá trình sử dụng hàm này rất đơn giản. Đây là hướng dẫn từng bước:
Chọn một ô. Bắt đầu bằng cách chọn ô ngoài cùng bên trái nơi bạn muốn danh sách tần số từ xuất hiện.
Nhập tên hàm. Trong ô đã chọn, nhập tên hàm sau dấu đẳng thức (=). Khi bạn bắt đầu nhập, Excel sẽ hiển thị danh sách các hàm và tên có sẵn. Tìm hàm FreqWords trong danh sách và chọn nó. Excel sẽ tự động chèn hàm vào ô.
Đưa ra những lý lẽ. Sau khi chèn hàm, bạn cần cung cấp các đối số được yêu cầu. Chỉ định data_range nơi bạn muốn đếm tần số từ. Nếu các từ của bạn được phân tách bằng các ký tự không phải dấu cách, hãy chỉ định đối số dấu phân cách tùy chọn .
Hoàn thành công thức. Khi bạn đã nhập hàm và các đối số của nó, hãy nhấn nútĐi vàochìa khóa hoặcCtrl + Shift + Enter phím tắt tùy theo phiên bản Excel của bạn (xem chi tiết bên dưới). Sau đó Excel sẽ tính toán tần suất văn bản và hiển thị kết quả.
Ngoài ra, bạn có thể sử dụng Trình hướng dẫn hàm để định cấu hình hàm FreqWords , như được giải thích trong hướng dẫn được liên kết ở trên.
Trong Dynamic Array Excel, FreqWord hoạt động giống như một hàm mảng động, tức là bạn nhập công thức vào một ô và nó trả về một mảng giá trị vào các ô lân cận.
Chỉ cần đảm bảo có đủ ô trống ở dưới và ít nhất một cột trống ở bên phải để xuất kết quả, nếu không bạn có thể gặp lỗi #SPILL .
Trong các phiên bản tiền động của Excel, bạn cần sử dụng hàm FreqWords làm công thức mảng , yêu cầu cách tiếp cận hơi khác một chút:
Chọn phạm vi đích. Chọn một dãy ô trống nơi bạn muốn kết quả xuất hiện. Các ô này sẽ được điền với tần số từ.
Nhập công thức. Nhập công thức FreqWords vào thanh công thức.
Biến nó thành một công thức mảng. Đây là bước quan trọng - thay vì chỉ nhấn Enter, nhấn nút Ctrl + Shift + Enter tổ hợp phím. Điều này yêu cầu Excel xử lý công thức dưới dạng công thức mảng và tính toán cho phù hợp.
Để biết thêm thông tin, hãy xem Cách nhập công thức mảng trong Excel.
Bây giờ bạn đã hiểu cách sử dụng hàm FreqWords, hãy xem xét một số tình huống thực tế nơi hàm này có thể đơn giản hóa công việc xử lý dữ liệu Excel của bạn.
Giả sử bạn có danh sách địa chỉ trong A2:A25. Để tìm ra những từ nào thường xuất hiện nhất trong danh sách này, hãy nhập công thức này vào C3:
=FreqWords(A2:A25)
Sau khi đánh Enter, bạn sẽ nhận được danh sách tất cả các từ duy nhất và số lần mỗi từ xuất hiện trong phạm vi nguồn. Các từ sẽ được hiển thị ở cột C và tần suất tương ứng của chúng sẽ ở cột D.
Trong Excel 365 và Excel 2021, công thức trả về một mảng giá trị trong nhiều ô. Tuy nhiên, trong Excel 2019 và các phiên bản cũ hơn, công thức thông thường sẽ chỉ trả về một giá trị duy nhất. Để có được danh sách các giá trị, hãy đảm bảo nhập danh sách đó dưới dạng công thức mảng như đã giải thích ở trên.
Thông thường, khi nhập dữ liệu vào Excel từ các chương trình hoặc tệp CSV khác, các giá trị không được phân tách bằng dấu cách mà bằng các ký tự đặc biệt như dấu phẩy, dấu chấm phẩy, dấu hai chấm, dấu gạch chéo,...
Trong những trường hợp như vậy, bạn cần chỉ định đối số thứ hai [ dấu phân cách ] của hàm FreqWords.
Ví dụ: để tính tần suất các từ trong danh sách được phân tách bằng dấu phẩy ở A3:A16, hãy sử dụng công thức sau:
=FreqWords(A3:A16, ",")
Nếu các giá trị của bạn được phân tách bằng ký tự khác, hãy thay thế "," bằng dấu phân cách cụ thể nếu cần.
Kết quả của phép tính này là bảng tần suất các từ dựa trên dấu phân cách được chỉ định của bạn:
Trong trường hợp bạn cần đếm tần số văn bản trong một ô, chỉ cần cung cấp địa chỉ ô đó cho đối số đầu tiên của hàm FreqWords:
=FreqWords(A3, ",")
Kết quả là bạn sẽ nhận được danh sách tần số từ sau:
Microsoft Excel cung cấp nhiều phương pháp khác nhau để so sánh các cột và xác định các giá trị trùng lặp và duy nhất trong hai danh sách. Tuy nhiên, có những trường hợp bạn cần so sánh không phải toàn bộ giá trị ô mà là tần số của các từ trong mỗi cột, bất kể vị trí hoặc thứ tự của chúng trong ô. Đây là lúc chức năng FreqWords trở nên vô giá!
Hãy xem xét tình huống trong đó bạn có hai danh sách có các mục giống nhau nhưng theo thứ tự khác nhau. Hơn nữa, các mục trong danh sách này có thể có cách sắp xếp từ khác nhau, chẳng hạn như "sô cô la sữa" và "sô cô la sữa". Câu hỏi đặt ra là: những danh sách này giống nhau hay khác nhau?
Giải pháp bao gồm hai bước đơn giản.
Bước 1: Tìm tần suất của các từ trong mỗi danh sách
Để xác định số lần mỗi từ xuất hiện trong mỗi danh sách, hãy sử dụng hàm FreqWords ở dạng cơ bản.
Tần số từ trong Danh sách 1:
=FreqWords(A3:A18)
Tần số từ trong Danh sách 2:
=FreqWords(C3:C18)
Bằng cách áp dụng các công thức này, bạn sẽ có được số từ cho mỗi danh sách. Kiểm tra kết quả, như trong ảnh chụp màn hình bên dưới, bạn có thể xác định chắc chắn rằng hai danh sách này khác nhau.
Bước 2: So sánh tần số từ trong hai danh sách
Sau khi bạn đã xác định rằng các danh sách được so sánh là khác nhau, bạn có thể muốn tìm hiểu sâu hơn và thu thập thêm thông tin chi tiết, chẳng hạn như:
Những từ nào xuất hiện trong cả hai danh sách và từ nào là duy nhất cho một danh sách?
Những từ nào có số lần xuất hiện giống nhau trong cả hai danh sách và từ nào có số lần xuất hiện khác nhau?
Để hoàn thành nhiệm vụ này, bạn cần đặt tần số của các từ cạnh nhau. Trong ví dụ, vì Danh sách 2 chứa nhiều từ độc đáo hơn nên sẽ coi đây là danh sách chính và kéo tần số từ Danh sách 1 sang cột liền kề bằng cách sử dụng hàm XLOOKUP.
Công thức bên dưới sẽ chuyển đến ô J3, sau đó bạn sao chép nó xuống cột:
=XLOOKUP(H3, $E$3:$E$10, $F$3:$F$10, "not found")
Bằng cách so sánh số từ trong cột I (Danh sách 1) và J (Danh sách 2), bạn có thể thấy rõ mỗi từ xuất hiện trong cả hai danh sách bao nhiêu lần. Và văn bản “không tìm thấy” trong cột J cho biết rằng một từ không có trong Danh sách 2.
Để làm cho sự khác biệt trở nên rõ ràng hơn, bạn có thể thêm các nhãn như "khớp" và "không khớp" vào một cột riêng biệt. Điều này có thể đạt được bằng cách sử dụng câu lệnh IF sau để so sánh số từ trong cột I và J:
=IF(I3=J3, "Match", "Don’t match")
“Khớp” có nghĩa là một từ nhất định có cùng số lần xuất hiện trong cả hai danh sách. “Không khớp” có nghĩa là một từ chỉ xuất hiện trong một danh sách hoặc có số lần xuất hiện khác nhau.
Đây là một cách tiếp cận hiệu quả khi xử lý dữ liệu văn bản có các từ theo thứ tự hoặc sự kết hợp khác nhau. Nó cũng hoạt động rất tốt khi so sánh tần suất văn bản trong hai danh sách với nhiều từ trong mỗi ô, được phân tách bằng ký tự bất kỳ. Ngoài ra, tính năng này rất hữu ích khi so sánh các ô thực sự lớn, ngay cả những ô cực lớn chứa hàng chục hoặc hàng trăm từ khác nhau.
Bằng cách sử dụng các công thức được thảo luận ở trên, bạn có thể so sánh tần suất văn bản trong hai cột một cách hiệu quả. Để có được những hiểu biết sâu sắc hơn nữa có giá trị về những điểm tương đồng và khác biệt giữa hai danh sách, bạn có thể làm nổi bật những điểm khác biệt này bằng màu sắc. Điều này có thể được thực hiện dễ dàng bằng công cụ Compare Tables, một phần của Ultimate Suite for Excel.
Ví dụ: hãy so sánh danh sách tần số từ thu được bằng cách áp dụng hàm FreqWords: Bảng 1 (A3:B10) và Bảng 2 (D3:E13).
Bạn có thể tìm thấy hướng dẫn chi tiết về cách sử dụng công cụ Compare Tables trong tài liệu trực tuyến. Ở đây, sẽ cung cấp tổng quan ngắn gọn về các bước:
Chọn bảng đầu tiên (A3:B10).
Chọn bảng thứ hai để so sánh (D3:E13).
Chỉ định xem bạn muốn tìm Bản sao (các mục xuất hiện trong cả hai danh sách) hay Giá trị duy nhất (sự khác biệt giữa hai danh sách). Chọn các bản sao, tức là tìm số từ giống nhau trong cả hai bảng.
Chọn các cột để so sánh Đây là bước quan trọng nên hãy cẩn thận khi lựa chọn. Trong ví dụ này, chọn hai cặp cột, bao gồm các từ và tần số tương ứng của chúng.
Cuối cùng, chọn hành động bạn muốn thực hiện – ví dụ: đánh dấu bằng một màu cụ thể. Sau đó, nhấp vào nút Kết thúc .
Lặp lại các bước tương tự để so sánh Bảng 2 với Bảng 1.
Kết quả là, bạn sẽ nhận được tần suất từ giống nhau được đánh dấu trong cả hai danh sách, cho phép bạn phân biệt trực quan các kết quả trùng khớp và khác biệt.
Tóm lại, việc phân tích tần suất văn bản trong Excel không còn khó khăn nữa. Với chức năng FreqWords tùy chỉnh và công cụ So sánh bảng, mọi việc trở nên cực kỳ đơn giản! Cho dù bạn đang làm việc về phân tích văn bản, khai thác dữ liệu hay chỉ đơn giản là cố gắng tìm ra các mẫu trong bảng tính của mình, những công cụ này kết hợp với nhau sẽ giúp công việc của bạn mượt mà và dễ hiểu hơn. Chỉ cần cho họ thử và xem sự khác biệt mà họ có thể tạo ra.
