Trong thế giới kỹ thuật số ngày nay, Google Sheets đã trở thành một công cụ không thể thiếu cho việc tổ chức, phân tích và quản lý dữ liệu. Tuy nhiên, khi làm việc với các tập dữ liệu lớn và phức tạp, việc trích xuất thông tin cụ thể có thể trở nên rườm rà và tốn thời gian, đặc biệt là khi phải sử dụng các công thức lồng ghép phức tạp hoặc thao tác thủ công. Đây chính là lúc hai hàm mạnh mẽ của Google Sheets – CHOOSECOLS và CHOOSEROWS – phát huy tối đa hiệu quả.
CHOOSECOLS và CHOOSEROWS là các hàm động, được thiết kế để đơn giản hóa quá trình trích xuất các cột hoặc hàng cụ thể từ bảng tính của bạn. Khác với các phương pháp truyền thống, các hàm này không chỉ giúp bạn lấy dữ liệu nhanh chóng mà còn tự động cập nhật kết quả khi dữ liệu nguồn thay đổi, đảm bảo tính chính xác và kịp thời cho mọi báo cáo hay phân tích. Sự linh hoạt và khả năng thích ứng này biến chúng thành công cụ lý tưởng cho bất kỳ ai muốn nâng cao kỹ năng xử lý dữ liệu trên Google Sheets. Bài viết này sẽ đi sâu vào cách sử dụng CHOOSECOLS và CHOOSEROWS, cung cấp cú pháp, giải thích chi tiết các đối số và minh họa bằng các ví dụ thực tế, giúp bạn dễ dàng áp dụng vào công việc của mình.
Để thực hành theo hướng dẫn, bạn có thể tạo bản sao riêng của mẫu Google Sheets. Sau khi nhấp vào liên kết, hãy đăng nhập vào tài khoản Google của bạn và nhấp vào “Tạo bản sao” để thêm bản sao tệp vào Google Drive của bạn. Sau đó, hãy làm theo các tab ở phía dưới cửa sổ Google Sheets khi bạn thực hiện các ví dụ dưới đây.
Hàm CHOOSECOLS và CHOOSEROWS Hoạt Động Như Thế Nào?
Một khi bạn đã nắm được cách sử dụng một trong hai hàm này trong Google Sheets, việc hiểu và áp dụng hàm còn lại sẽ trở nên rất dễ dàng vì chúng có cú pháp và cách thức hoạt động tương tự.
Cú pháp Hàm CHOOSECOLS
Hàm CHOOSECOLS được dùng để trích xuất các cột từ một dải ô hoặc bảng dữ liệu. Cú pháp cơ bản của nó như sau:
=CHOOSECOLS(dải_ô, số_chỉ_mục_cột1, [số_chỉ_mục_cột2, ...])Cú pháp Hàm CHOOSEROWS
Tương tự, hàm CHOOSEROWS được dùng để trích xuất các hàng từ một dải ô hoặc bảng dữ liệu, với cú pháp:
=CHOOSEROWS(dải_ô, số_chỉ_mục_hàng1, [số_chỉ_mục_hàng2, ...])Giải thích các Đối số
Cả hai hàm đều sử dụng các đối số tương tự nhau, cụ thể như sau:
| Đối số | Bắt buộc? | Ý nghĩa | Lưu ý |
|---|---|---|---|
| dải_ô | Có | Dải ô hoặc tên bảng chứa các cột (CHOOSECOLS) hoặc hàng (CHOOSEROWS) bạn muốn trích xuất. | Có thể là một dải ô thông thường (ví dụ: A1:G10) hoặc tên của một bảng được định dạng (ví dụ: Sales). |
| số_chỉ_mục_cột/hàng1 | Có | Chỉ số của cột (CHOOSECOLS) hoặc hàng (CHOOSEROWS) đầu tiên cần trích xuất. | Số dương tính từ trái (cột) hoặc trên (hàng), số âm tính từ phải (cột) hoặc dưới (hàng) của dải ô. |
| [số_chỉ_mục_cột/hàng2, …] | Không | Chỉ số của các cột (CHOOSECOLS) hoặc hàng (CHOOSEROWS) bổ sung cần trích xuất. | Mỗi chỉ số phải được phân tách bằng dấu phẩy. |
Giao diện Google Sheets trên máy tính xách tay với biểu đồ và sách hướng dẫn, minh họa các hàm xử lý dữ liệu.
Nếu bất kỳ chỉ số nào là số không (0) hoặc vượt quá số lượng cột/hàng trong dải ô, Google Sheets sẽ trả về lỗi #VALUE!. Điều quan trọng cần lưu ý là vì các hàm này tạo ra các mảng động, kết quả sẽ tràn ra các ô liền kề. Do đó, bạn cần đảm bảo có đủ không gian trống trong bảng tính. Nếu không, bạn sẽ gặp lỗi #REF!.
Hơn nữa, nếu bạn trích xuất một cột hoặc hàng từ giữa dải ô và sau đó chèn thêm cột hoặc hàng vào dữ liệu gốc, chỉ số bạn đã cung cấp có thể không còn trỏ đến cùng một cột/hàng ban đầu nữa. Để khắc phục điều này và làm cho công thức của bạn linh hoạt hơn, hãy sử dụng bảng có cấu trúc (formatted table) hoặc tham chiếu động (sẽ được đề cập ở ví dụ sau).
Ví Dụ 1: Trích Xuất Cột/Hàng Đầu Tiên và Cuối Cùng
CHOOSECOLS và CHOOSEROWS đặc biệt hữu ích khi bạn muốn nhanh chóng lấy các cột hoặc hàng ở đầu và cuối của tập dữ liệu. Đây là một mẹo rất tiện dụng nếu cột hoặc hàng đầu tiên là tiêu đề và cột hoặc hàng cuối cùng chứa tổng cộng.
Trích Xuất Cột Đầu Tiên và Cuối Cùng
Hãy tưởng tượng bạn là một quản trị viên kinh doanh và cần tạo một báo cáo tổng hợp số lượng sản phẩm đã bán của mỗi cửa hàng trong hai quý đầu năm. Dữ liệu bán hàng của bạn được tổ chức trong một bảng như hình dưới:
Bảng tính Google Sheets hiển thị dữ liệu doanh số bán hàng của các cửa hàng trong sáu tháng, minh họa dữ liệu nguồn.
Để trích xuất cột “Shop” (cột đầu tiên) và cột “Tot” (cột cuối cùng chứa tổng doanh số), trong một ô trống, bạn nhập công thức sau:
=CHOOSECOLS(Sales[#ALL],1,-1)Trong đó:
- CHOOSECOLS là hàm bạn dùng để trích xuất dữ liệu từ các cột “Shop” và “Tot”.
- Sales là tên của bảng chứa dữ liệu, và [#ALL] cho Google Sheets biết rằng bạn muốn bao gồm cả hàng tiêu đề trong kết quả.
- 1 chỉ ra rằng bạn muốn trích xuất cột đầu tiên (trong trường hợp này là cột “Shop”).
- -1 chỉ ra rằng bạn muốn trích xuất cột cuối cùng (trong trường hợp này là cột “Tot”).
Thay vì gõ thủ công dải ô trong đối số dải_ô, bạn có thể dùng chuột để chọn các ô bạn muốn tham chiếu. Điều này giúp tránh lỗi và tăng tốc độ làm việc.
Khi bạn nhấn Enter, đây là kết quả bạn nhận được:
Kết quả hàm CHOOSECOLS trong Google Sheets trích xuất cột đầu tiên và cuối cùng từ dữ liệu doanh số.
Hãy để ý rằng, mặc dù bạn chỉ gõ công thức vào ô J1, kết quả đã tràn từ ô đó đến ô K12 để chứa tất cả các cột và hàng được trích xuất. Đây là đặc điểm của các hàm mảng động.
Hơn nữa, vì dữ liệu nguồn nằm trong một bảng có cấu trúc, nếu dữ liệu thay đổi (ví dụ: một cửa hàng mới được thêm vào hoặc doanh số cập nhật), kết quả của hàm CHOOSECOLS cũng sẽ tự động thay đổi theo. Điều này cũng đúng khi thêm các cột hoặc hàng mới vào tập dữ liệu.
Kết quả hàm CHOOSECOLS được cập nhật tự động khi thêm hàng dữ liệu mới vào bảng tính Google Sheets.
Nếu bạn sử dụng tham chiếu ô trực tiếp trong đối số dải_ô thay vì tham chiếu dựa trên bảng, công thức sẽ không tự động nhận diện dữ liệu mới trừ khi bạn thay đổi tham chiếu ô thủ công. Dù bạn đang sử dụng Google Sheets hay Microsoft Excel, việc sử dụng các bảng có cấu trúc luôn được khuyến nghị vì chúng cung cấp các công cụ và khả năng thích ứng tốt hơn.
Giờ đây, bạn có thể dễ dàng sao chép dữ liệu tóm tắt này sang một bảng tính Google Sheets khác (chẳng hạn như một tab bảng điều khiển) hoặc một tài liệu Google Docs.
Trích Xuất Hàng Đầu Tiên và Cuối Cùng
Tương tự, việc trích xuất hàng đầu tiên và cuối cùng rất tiện lợi để tạo các bản tóm tắt dữ liệu theo chiều ngang.
Sử dụng cùng ví dụ như trên, bao gồm cả “Shop K” đã được thêm vào, giả sử bạn muốn tóm tắt tổng số sản phẩm bán được mỗi tháng.
Trong một ô trống, bạn nhập công thức sau:
=CHOOSEROWS(Sales[#ALL],1,-1)và nhấn Enter. Như trong ví dụ trước, việc thêm [#ALL] sau tên bảng cho Google Sheets biết rằng bạn muốn hàng tiêu đề được tính là hàng 1 trong quá trình tìm kiếm chỉ số.
Kết quả hàm CHOOSEROWS trong Google Sheets trích xuất hàng đầu tiên và cuối cùng từ dữ liệu doanh số.
Ví Dụ 2: Trích Xuất Cột Từ Nhiều Dải Ô (Kết Hợp VSTACK và SORT)
Trong Ví dụ 1, chúng ta đã trích xuất các cột và hàng cụ thể từ các dải ô riêng lẻ. Tuy nhiên, bạn có thể kết hợp CHOOSECOLS với hàm VSTACK để kết hợp các trích xuất từ nhiều dải ô cùng một lúc.
Giả sử bạn muốn tạo một danh sách tổng điểm của mỗi đội trong Giải đấu 1 (màu đỏ) và Giải đấu 2 (màu xanh lá cây) từ dữ liệu sau:
Bảng tính Google Sheets hiển thị điểm số của mười đội từ hai giải đấu qua sáu trận đấu.
Điểm quan trọng đầu tiên cần lưu ý là bạn không muốn trích xuất các tiêu đề cột, vì bạn muốn xếp chồng dữ liệu từ mỗi bảng lên nhau. Nếu bạn bao gồm các tiêu đề cột, chúng sẽ xuất hiện ở đầu cả hai tập dữ liệu trong kết quả, làm mất đi ý nghĩa của việc bạn đang xem một tập dữ liệu kết hợp.
Do đó, trong các ô J1 và K1, bạn hãy nhập thủ công các tiêu đề cột mà bạn mong muốn (ví dụ: “Team” và “Total Score”).
Tiêu đề cột được nhập thủ công trong Google Sheets trước khi trích xuất dữ liệu kết hợp.
Tiếp theo, trong ô J2, bạn nhập công thức sau:
=CHOOSECOLS(VSTACK(League1,League2),1,-1)Trong đó:
- CHOOSECOLS cho Google Sheets biết rằng bạn muốn trích xuất các cột nhất định.
- VSTACK kết hợp kết quả theo chiều dọc (do đó có chữ “V” trong tên hàm).
- League1,League2 là các dải ô chứa các cột (không bao gồm [#ALL] trong công thức cho Google Sheets biết không bao gồm hàng tiêu đề).
- 1 trích xuất cột đầu tiên (“Team”) từ mỗi mảng.
- -1 trích xuất cột cuối cùng (“Tot”) từ mỗi mảng.
Khi bạn nhấn Enter, đây là kết quả bạn nhận được:
Kết quả hàm CHOOSECOLS kết hợp VSTACK trong Google Sheets, trích xuất và gộp dữ liệu từ nhiều bảng.
Bạn có thể tiến thêm một bước nữa và lồng toàn bộ công thức bên trong hàm SORT, để bảng tóm tắt hiển thị các đội theo thứ tự tổng điểm của họ.
Để làm điều này, nhấp đúp vào ô bạn đã nhập công thức kết hợp CHOOSECOLS-VSTACK trước đó, và thêm các đối số SORT sau:
=SORT(CHOOSECOLS(VSTACK(League1,League2),1,-1),2,FALSE)Trong đó:
- SORT là hàm sắp xếp một dải ô dựa trên một cột được chỉ định.
- 2 cho Google Sheets biết sắp xếp dải ô theo cột thứ hai (cột tổng điểm).
- FALSE chỉ ra rằng dữ liệu nên được sắp xếp theo thứ tự giảm dần (từ cao xuống thấp). Nếu muốn sắp xếp tăng dần, bạn có thể dùng TRUE hoặc bỏ qua đối số này.
Kết quả hàm CHOOSECOLS kết hợp SORT và VSTACK trong Google Sheets, hiển thị dữ liệu đã sắp xếp từ nhiều bảng.
Điều ấn tượng là, nếu bất kỳ giá trị nào trong tập dữ liệu gốc thay đổi, bảng tóm tắt sẽ điều chỉnh tương ứng. Ví dụ, nếu thay đổi điểm số Game 6 của Team H từ 51 lên 89 sẽ đưa họ từ vị trí thứ chín lên vị trí thứ tư.
Dữ liệu được cập nhật tự động trong Google Sheets sau khi thay đổi điểm số, minh họa tính động của công thức.
Ví Dụ 3: Làm Cho Hàm CHOOSECOLS và CHOOSEROWS Trở Nên Động Với Tham Chiếu Ô
Cho đến nay, chúng ta đã tìm hiểu cách sử dụng CHOOSECOLS và CHOOSEROWS để trích xuất các cột và hàng đầu tiên/cuối cùng. Tuy nhiên, trong ví dụ cuối cùng này, bạn sẽ được hướng dẫn cách trích xuất các cột hoặc hàng khác một cách động bằng cách tham chiếu đến các ô chứa chỉ số, thay vì sử dụng các số chỉ mục cố định (hard-coded index numbers) trong các đối số.
Ví dụ này sẽ minh họa cách sử dụng CHOOSECOLS một cách động, nhưng bạn hoàn toàn có thể áp dụng nguyên tắc tương tự với hàm CHOOSEROWS.
Bảng Google Sheets này hiển thị điểm số của năm đội qua sáu trận đấu, cũng như tổng điểm cho mỗi trận. Mục tiêu của bạn là tạo một bảng tóm tắt theo số trận đấu mà bạn chỉ định trong ô B9. Đầu tiên, hãy nhập một số trận đấu vào ô này để có dữ liệu để làm việc khi bạn tạo công thức CHOOSECOLS của mình.
Bảng dữ liệu TeamScores và bảng trích xuất kết quả trong Google Sheets, chuẩn bị cho hàm CHOOSECOLS động.
Sau đó, trong ô A11, bạn nhập công thức:
=CHOOSECOLS(TeamScores,1,B9+1)Trong đó:
- CHOOSECOLS cho Google Sheets biết bạn sẽ trích xuất các cột nhất định.
- TeamScores là tên của bảng chứa dữ liệu bạn muốn trích xuất.
- 1 là chỉ số cho cột “Team”.
- B9+1 cho Google Sheets biết rằng đối số chỉ mục thứ hai được tính bằng cách cộng 1 vào giá trị trong ô B9. Lý do bạn cần thêm +1 ở đây là vì các số trận đấu bắt đầu từ cột thứ hai của hàng 1, không phải cột đầu tiên. Kết quả là, nhập 4 vào ô B9 sẽ trích xuất dữ liệu từ cột thứ năm của bảng, đó là Game 4.
Đây là kết quả bạn nhận được khi nhấn Enter:
Điểm số từ Game 4 được trích xuất từ bảng TeamScores bằng hàm CHOOSECOLS động trong Google Sheets.
Bây giờ, hãy nhập một số trận đấu khác vào ô B9 để xem kết quả cập nhật tương ứng.
Kết quả trích xuất dữ liệu Google Sheets cập nhật tự động khi thay đổi số trận đấu được chọn.
Mẹo Chuyên Nghiệp: Thêm Quy Tắc Xác Thực Dữ Liệu (Data Validation)
Mặc dù việc nhập số trận đấu vào ô B9 hoạt động chính xác như mong đợi trong việc làm cho hàm CHOOSECOLS trở nên động, nhưng nếu một số không hợp lệ được nhập, bạn sẽ ngay lập tức gặp lỗi #VALUE!. Để ngăn điều này xảy ra, bạn có thể sử dụng tính năng xác thực dữ liệu (Data Validation) để kiểm soát các tham số đầu vào của ô.
Để làm điều này, chọn ô B9, và trong tab Dữ liệu (Data), nhấp vào Xác thực dữ liệu (Data Validation).
Ô B9 được chọn và tùy chọn Xác thực dữ liệu trong tab Dữ liệu của Google Sheets.
Sau đó, trong khung Quy tắc xác thực dữ liệu (Data Validation Rules) ở phía bên phải, nhấp vào Thêm quy tắc (Add Rule).
Chọn 'Thêm quy tắc' trong khung Quy tắc xác thực dữ liệu của Google Sheets.
Tiếp theo, trong trường Tiêu chí (Criteria), chọn Danh sách thả xuống (Dropdown), và nhập giá trị đầu tiên có thể có cho ô này vào trường văn bản. Sau đó, nhấp vào Thêm mục khác (Add Another Item) và lặp lại quá trình này cho đến khi tất cả các giá trị đầu vào được liệt kê. Khi tất cả các tùy chọn đã được liệt kê, nhấp vào Hoàn tất (Done).
Nhập các mục cho danh sách thả xuống trong Quy tắc xác thực dữ liệu của Google Sheets.
Bạn cũng có thể chọn “Danh sách thả xuống từ một dải ô” trong trường Tiêu chí, nơi bạn có thể chọn các ô chứa các mục hợp lệ. Tuy nhiên, điều này không phải lúc nào cũng hoạt động như mong đợi với các tiêu đề cột, ngay cả khi bạn đặt tên cho dải ô. Thay vào đó, hãy thiết lập một bảng tính riêng nơi bạn có thể lưu giữ tất cả các dải ô xác thực dữ liệu của mình.
Bây giờ, quay lại ô B9, nhấp vào mũi tên thả xuống và chọn một số trận đấu khác để xem kết quả cập nhật tương ứng.
Mũi tên thả xuống trong ô Google Sheets với quy tắc xác thực dữ liệu đã áp dụng, cho phép chọn giá trị.
Kết Luận
Hàm CHOOSECOLS và CHOOSEROWS trong Google Sheets là những công cụ vô cùng mạnh mẽ, mang lại khả năng trích xuất và quản lý dữ liệu một cách động và hiệu quả. Chúng không chỉ giúp bạn tránh khỏi sự phức tạp của các công thức lồng ghép mà còn đảm bảo dữ liệu của bạn luôn được cập nhật tự động theo những thay đổi của nguồn. Từ việc trích xuất các cột/hàng cơ bản đến việc kết hợp với các hàm khác như VSTACK và SORT để tạo báo cáo phức tạp, hay thậm chí là xây dựng các công thức động dựa trên tham chiếu ô, tiềm năng ứng dụng của CHOOSECOLS và CHOOSEROWS là rất lớn.
Điều quan trọng cần nhớ khi làm việc với các hàm mảng động là đảm bảo có đủ không gian trống trong bảng tính để chứa kết quả. Việc này sẽ giúp bạn tránh các lỗi #REF! không đáng có. Bằng cách kết hợp các hàm này với các tính năng như Data Validation, bạn có thể tạo ra các bảng tính không chỉ mạnh mẽ về mặt chức năng mà còn thân thiện với người dùng và ít lỗi hơn.
Hy vọng qua bài viết này, bạn đã có cái nhìn rõ ràng hơn về cách tận dụng tối đa CHOOSECOLS và CHOOSEROWS để tối ưu hóa quy trình làm việc với dữ liệu trên Google Sheets. Đừng ngần ngại thử nghiệm với các ví dụ đã cho và sáng tạo ra những ứng dụng mới phù hợp với nhu cầu công việc của bạn. Hãy tiếp tục theo dõi thoibaocongnghe.net để khám phá thêm nhiều mẹo và hướng dẫn công nghệ hữu ích khác!
