Chẳng phải lúc nào bạn cũng dễ dàng tổng hợp các ô có chứa số và văn bản trong một cột trên Google Sheets. Điều này thực sự là một thách thức, đặc biệt khi bạn phải làm việc với các nhân viên nhập dữ liệu thiếu kinh nghiệm. Nhưng đừng lo lắng, bạn ơi! Hôm nay tôi sẽ chia sẻ một bí mật nhỏ giúp bạn giải quyết nhanh vấn đề này.
Khắc phục vấn đề nội dung kết hợp
Google Sheets xem các ô kết hợp số và văn bản như pure text. Điều này có nghĩa là bạn không thể sử dụng công thức SUM đơn giản để tổng hợp các giá trị trong cột như vậy. Hãy nhìn vào ví dụ sau:
Trong tập dữ liệu ví dụ này, sử dụng công thức =SUM(C2:C6) sẽ trả về 0 vì tất cả các số trong cột C đều kết hợp với đơn vị đo lường, được biểu diễn bằng chuỗi hoặc văn bản. Nhưng đừng lo lắng, bạn ơi, có một giải pháp cho vấn đề này!
Khai phá sức mạnh của các hàm
Để xử lý các cột có nội dung kết hợp trong các phép tính, chúng ta cần trước tiên loại bỏ các phần văn bản từ các con số. Hãy xem cách làm từng bước dưới đây.
Bước 1: Thay thế văn bản bằng dấu chân trâu
Chúng ta sẽ bắt đầu bằng cách sử dụng hàm REGEXREPLACE để thay thế tất cả văn bản trong cột C bằng ký tự đường ống (|). Công thức sau sẽ giúp chúng ta thực hiện điều này:
=ArrayFormula(REGEXREPLACE(C2:C&"", "[^d.]+", "|"))Nhưng tại sao tôi lại thêm &"" sau C2:C? Đó chỉ là để chuyển các số trong phạm vi thành chuỗi văn bản. Điều này quan trọng vì hàm REGEXREPLACE mong đợi các giá trị văn bản trong tham số 1. Nếu bạn bỏ qua &"", bạn có thể nhận được lỗi VALUE!.
Bước 2: Chia kết quả thành các phần riêng biệt
Tiếp theo, chúng ta sẽ sử dụng hàm SPLIT để chia kết quả của bước trước dựa trên ký tự đường ống (|) làm dấu phân cách. Dưới đây là công thức đã được cập nhật:
=ArrayFormula(SPLIT(REGEXREPLACE(C2:C&"", "[^d.]+", "|"), "|"))Sử dụng ký tự đường ống làm dấu phân cách cho phép chúng ta xử lý các trường hợp mà một ô chứa nhiều số, ví dụ như “350 MT, 450 MT”. Bằng cách sử dụng ký tự đường ống, chúng ta đảm bảo rằng các số này sẽ được chia thành các giá trị riêng biệt.
Bước 3: Loại bỏ lỗi và tổng hợp giá trị
Cuối cùng, chúng ta sẽ sử dụng hàm IFERROR để loại bỏ bất kỳ lỗi nào liên quan đến các ô trống trong cột, sau đó sử dụng hàm SUM để tổng hợp tất cả các giá trị. Đây là công thức cuối cùng:
=ArrayFormula(IFERROR(SPLIT(REGEXREPLACE(C2:C&"", "[^d.]+", "|"), "|")))Phép tính này có sức mạnh đấy! Công thức này có thể được sử dụng trong bất kỳ cột nào vì nó là một công thức mảng. Nó có thể tổng hợp toàn bộ cột chứa các ký tự chữ và số. Chỉ cần đảm bảo sửa đổi các tham chiếu ô để phù hợp với nhu cầu của bạn.
Vượt xa việc tổng hợp cột: Tổng hợp hàng và mảng 2D
Nhưng chờ đã, chúng ta còn nhiều hơn thế nữa! Bạn cũng có thể sử dụng công thức này để tổng hợp các ô có chứa số và văn bản trong một hàng, hoặc thậm chí trong một mảng 2D. Hãy xem cách làm dưới đây.
Tổng hợp các ký tự chữ và số trong một hàng
Để tổng hợp các ký tự chữ và số trong một hàng, bạn cần chuyển vị hàng thành cột bằng cách sử dụng hàm TRANSPOSE. Dưới đây là công thức đã được chỉnh sửa:
=ArrayFormula(SUM(IFERROR(SPLIT(REGEXREPLACE(TRANSPOSE(C2:G2&""), "[^d.]+", "|"), "|"))))Chỉ cần thay thế C2:G2&"" bằng phạm vi mong muốn của bạn, và bạn đã sẵn sàng.
Tổng hợp các ký tự chữ và số trong một mảng 2D
Để tổng hợp các ký tự chữ và số trong một mảng 2D, bạn cần điều chỉnh công thức một chút. Thay vì sử dụng C2:C, bạn sẽ sử dụng TOCOL(B2:F3), which transforms the range of cells into a single column, chuyển đổi phạm vi ô thành một cột duy nhất. Dưới đây là công thức đã được chỉnh sửa:
=ArrayFormula(SUM(IFERROR(SPLIT(REGEXREPLACE(TOCOL(B2:F3)&"", "[^d.]+", "|"), "|"))))Và đó là tất cả! Với công thức này, bạn có thể tổng hợp các ô có chứa số và văn bản trong một cột, một hàng hoặc thậm chí một mảng 2D.
Kết luận
Hy vọng rằng bạn đã tìm hiểu được một bí mật quý giá hôm nay. Bây giờ, bạn có thể tự tin xử lý các tình huống khi bạn cần tổng hợp các ô có chứa số và văn bản trong Google Sheets. Hãy nhớ rằng công thức này là vũ khí bí mật của bạn để xử lý và làm sạch dữ liệu. Hãy sử dụng nó một cách khôn ngoan và chinh phục bảng tính của bạn như một chuyên gia SEO thực thụ!
Để biết thêm những mẹo hấp dẫn và thủ thuật khác, đừng quên ghé thăm Crawlan.com. Chúc bạn tổng hợp vui vẻ!
