Trong bài viết này, tôi sẽ giới thiệu một “báu vật” ẩn trong Google Sheets – đó là hàm FLATTEN. Bạn có thể không tìm thấy hàm này trong tài liệu chính thức, nhưng nó có thể làm phẳng các phạm vi và tạo ra dữ liệu phẳng bằng cách sử dụng mệnh đề SKIPPING trong QUERY. Vậy hãy cùng khám phá sức mạnh của hàm FLATTEN nhé!
Khám phá hàm FLATTEN trong Google Sheets
Trước khi bắt đầu, hãy để tôi chia sẻ một mẹo nhỏ để xác định xem một hàm có tồn tại trong Google Sheets hay không. Đơn giản chỉ cần gõ hàm đó vào một ô và kiểm tra xem nó có gây ra lỗi hay không. Nếu bạn thấy xuất hiện lỗi #NAME? hoặc #N/A, điều đó có nghĩa là hàm không có sẵn hoặc viết sai. Hiểu rồi chứ?
Để sử dụng hàm FLATTEN, hãy nhập =flatten() vào bất kỳ ô nào. Đợi đã! Đừng ngạc nhiên nếu bạn nhìn thấy lỗi #N/A. Điều này là điều dễ hiểu! Gợi ý sẽ cho biết vấn đề, đó là bạn cần cung cấp ít nhất một đối số.
Cú pháp và đối số của hàm FLATTEN
Vì hàm FLATTEN không được tài liệu chính thức ghi lại, hãy để tôi giải thích cú pháp và đối số của nó:
Cú pháp: flatten(value1, [value2, ...])
Đối số:
value1– Giá trị hoặc phạm vi đầu tiên cần xem xét để làm phẳng.value2, ...– Các giá trị hoặc phạm vi bổ sung tùy chọn để xem xét để làm phẳng.
Mục đích của hàm FLATTEN là làm phẳng các giá trị trong một mảng thành các hàng riêng lẻ trong một cột. Điều này rất hữu ích khi làm việc với các bộ dữ liệu phức tạp.
Một ví dụ để minh họa hàm FLATTEN
Để làm cho mọi thứ cụ thể hơn, hãy xem xét một ví dụ. Trong hình ảnh dưới đây, bạn có thể thấy làm thế nào mảng A1:D được làm phẳng thành các hàng trong cột F1:F.
Trong trường hợp cụ thể này, hàm FLATTEN giúp chúng ta tổ chức các giá trị phân tán, từ đó dễ dàng điều chỉnh và phân tích dữ liệu. Thật tuyệt vời, phải không?
Sử dụng thực tế: Xử lý các giá trị phân tán
Một ứng dụng thực tế của hàm FLATTEN là xử lý các giá trị phân tán trong một bảng tính. Hãy xem xét ví dụ về việc phân tích sở thích giường ngủ của hành khách. Để tìm ra các sở thích duy nhất và tóm tắt dữ liệu, chúng ta không thể chỉ dựa vào công thức UNIQUE. Thay vào đó, chúng ta có thể sử dụng =unique(flatten(B2:D)) trong một ô, như được hiển thị dưới đây.
Để tóm tắt dữ liệu, bạn có thể sử dụng hàm QUERY như sau:
=query(flatten(B2:D), "Select Col1, count(Col1) where Col1 is not null group by Col1 label count(Col1)''")
Có vô số cách sử dụng khi bạn làm chủ hàm FLATTEN!
Cách làm phẳng một phạm vi đã được làm phẳng trong Google Sheets
Bây giờ bạn đã biết cách làm phẳng một phạm vi, hãy khám phá quá trình đảo ngược – làm phẳng lại. Chúng ta có thể thực hiện điều này bằng cách sử dụng hàm QUERY.
Trong hình ảnh thứ nhất (kể lại ví dụ trước đó), bạn có thể thấy các giá trị đã được làm phẳng trong phạm vi F1:F. Để làm phẳng nó, chúng ta sẽ tận dụng sức mạnh của QUERY. Nhưng đợi đã! Vì có cả số và chuỗi văn bản trong cột F, nó trở thành một cột có loại dữ liệu pha trộn. Trong trường hợp như vậy, chúng ta cần sử dụng hàm TO_TEXT cùng với QUERY.
Các bước để làm phẳng một mảng đã được làm phẳng
Đây là hướng dẫn từng bước để làm phẳng một phạm vi trong Google Sheets:
- Nhìn vào tập dữ liệu, chẳng hạn như A1:D (như được hiển thị trong hình ảnh thứ nhất), để hiểu số cột nó chứa.
- Nếu bạn chỉ có phạm vi đã được làm phẳng (như F1:F) và muốn làm phẳng nó với số cột tương tự, bạn cần phân tích nó và xác định số cột mong muốn.
- Giả sử có 4 cột. Trong công thức QUERY, bạn nên bỏ qua 4 cột để làm phẳng nó hiệu quả. Dưới đây là một ví dụ:
=ArrayFormula(query(to_text(F1:F),"Skipping 4"))
- Vì phạm vi chứa dữ liệu loại trộn, chúng ta bọc nó bằng hàm TO_TEXT không phải mảng. Để duy trì phạm vi mảng, chúng ta sử dụng hàm ARRAYFORMULA.
Đối với mỗi cột bổ sung, chẳng hạn I1, J1 và K1, bạn có thể sử dụng công thức sau:
=ArrayFormula(query(to_text(indirect("F"&column(A1)&":F")),"Skipping 4"))
Bây giờ bạn đã sẵn sàng để làm phẳng phạm vi đã được làm phẳng của mình!
Giải thích phần Indirect
Bạn có thể đang tự hỏi về phần “Indirect” trong công thức. Hãy để tôi giải thích. Trong công thức INDIRECT, chữ “F” đại diện cho cột F. Tham chiếu ô A1 đại diện cho số dòng. Nếu các giá trị đã được làm phẳng nằm trong phạm vi F5:F thay vì F1:F, bạn cần thay thế A1 bằng E1, vì cột(E1) sẽ trả về 5.
Đó là tất cả những gì bạn cần biết về hàm FLATTEN trong Google Sheets. Hãy tận hưởng sức mạnh của nó và nâng cao trình độ chơi bảng tính của bạn!
Credit: Crawlan.com
