Trong thế giới làm việc với bảng tính, việc trích xuất các cột hoặc hàng cụ thể từ một tập dữ liệu lớn là một tác vụ thường xuyên và đôi khi khá phức tạp. Google Sheets, với sự phát triển liên tục, đã cung cấp hai hàm mạnh mẽ và linh hoạt là CHOOSECOLS và CHOOSEROWS, giúp đơn giản hóa quy trình này một cách đáng kể. Các hàm này cho phép bạn nhanh chóng lựa chọn và hiển thị các phần dữ liệu cần thiết mà không cần đến những công thức lồng ghép phức tạp hay việc sao chép thủ công. Hơn thế nữa, vì đây là những hàm động, chúng có khả năng tự động cập nhật kết quả khi dữ liệu nguồn thay đổi, mang lại sự linh hoạt và hiệu quả cao trong quản lý và báo cáo dữ liệu. Bài viết này sẽ hướng dẫn chi tiết cách sử dụng hai hàm này, từ cú pháp cơ bản đến các ví dụ ứng dụng nâng cao, giúp bạn khai thác tối đa tiềm năng của chúng trong công việc.
Để thực hành theo hướng dẫn, bạn có thể truy cập và tạo bản sao của mẫu Google Sheets được cung cấp. 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à chọn “Tạo bản sao” để thêm tệp vào Google Drive của mình. Tiếp theo, bạn có thể theo dõi các tab ở cuối cửa sổ Google Sheets khi khám phá các ví dụ bên dưới.
I. Cú Pháp Cơ Bản của Hàm CHOOSECOLS và CHOOSEROWS
Khi bạn đã hiểu cách sử dụng một trong hai hàm CHOOSECOLS hoặc CHOOSEROWS trong Google Sheets, bạn sẽ dễ dàng nắm bắt cách hoạt động của hàm còn lại, vì cú pháp của chúng rất tương đồng.
Cú pháp cho hàm CHOOSECOLS như sau:
=CHOOSECOLS(a,b,c)Và đây là cú pháp cho hàm CHOOSEROWS:
=CHOOSEROWS(a,b,c)Dưới đây là ý nghĩa của từng đối số:
| Đối số | Bắt buộc? | Ý nghĩa | Lưu ý |
|---|---|---|---|
| a | Có | Phạm vi dữ liệu chứa các cột (CHOOSECOLS) hoặc hàng (CHOOSEROWS) bạn muốn trích xuất. |
Đây có thể là một phạm vi ô (ví dụ: A1:Z100), hoặc tên của một bảng/phạm vi dữ liệu đã được định dạng. |
| b | Có | Số thứ tự (chỉ mục) của cột (CHOOSECOLS) hoặc hàng (CHOOSEROWS) đầu tiên cần trích xuất. |
Số dương đếm từ bên trái (đối với cột) hoặc từ trên xuống (đối với hàng) của phạm vi, trong khi số âm đếm từ bên phải (đối với cột) hoặc từ dưới lên (đối với hàng). |
| c | Không | Các số thứ tự (chỉ mục) của bất kỳ cột (CHOOSECOLS) hoặc hàng (CHOOSEROWS) bổ sung nào cần trích xuất. |
Mỗi số thứ tự phải được phân tách bằng dấu phẩy. |
Nếu bất kỳ số thứ tự nào là 0 hoặc vượt quá số lượng cột hay hàng trong phạm vi, Google Sheets sẽ trả về lỗi #VALUE!. Đặc biệt, nếu số thứ tự trích xuất một cột hoặc hàng ở giữa phạm vi, việc chèn thêm cột hoặc hàng vào dữ liệu nguồn có thể làm thay đổi cột hoặc hàng được trích xuất. Điều này nhấn mạnh tầm quan trọng của việc hiểu cách các hàm này phản ứng với sự thay đổi trong cấu trúc dữ liệu.
II. Ví Dụ Ứng Dụng 1: Trích Xuất Cột/Hàng Đầu Tiên và Cuối Cùng
Kể từ khi tôi hiểu rõ cách hoạt động của các hàm CHOOSECOLS và CHOOSEROWS, tôi đã sử dụng chúng vô cùng nhiều để trích xuất các cột hoặc hàng đầu tiên và cuối cùng từ dữ liệu của mình. Đây là một mẹo đặc biệt hữu ích 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 các giá trị tổng cộng.
1. Trích Xuất Cột Đầu Tiên và Cuối Cùng
Trong ví dụ này, hãy tưởng tượng bạn là một quản trị viên kinh doanh và bạn muốn tạo một báo cáo tóm tắt số lượng sản phẩm đã bán của mỗi cửa hàng trong hai quý đầu năm.
Bảng dữ liệu doanh số cửa hàng trong Google Sheets bao gồm 10 cửa hàng trong sáu tháng.
Để thực hiện điều này, trong một ô trống, bạn nhập công thức sau:
=CHOOSECOLS(Sales[#ALL],1,-1)Trong đó:
CHOOSECOLSlà hàm bạn cần sử dụng để trích xuất dữ liệu từ cột “Shop” và cột “Tot”.Saleslà tên của bảng chứa dữ liệu, và[#ALL]cho Google Sheets biết rằng bạn muốn hàng tiêu đề cũng được bao gồm trong kết quả.1chỉ 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”).-1chỉ 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ì nhập phạm vi dữ liệu theo cách thủ công vào đối số a, bạn có thể sử dụng chuột để chọn các ô bạn muốn tham chiếu, giúp đảm bảo tính chính xác và hiệu quả.
Khi bạn nhấn Enter, đây là kết quả bạn nhận được:
Kết quả của công thức CHOOSECOLS trong Google Sheets trích xuất cột đầu tiên và cột cuối cùng của tập dữ liệu.
Hãy để ý rằng, mặc dù bạn chỉ gõ công thức vào ô J1, kết quả đã tự động tràn ra từ ô đó đến ô K12 để chứa toàn bộ số cột và hàng trong kết quả.
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, kết quả của hàm CHOOSECOLS cũng sẽ tự động thay đổi theo. Điều này cũng đúng nếu có thêm cột hoặc hàng được bổ sung vào tập dữ liệu.
Kết quả của công thức CHOOSECOLS trong Google Sheets đã được cập nhật để bao gồm một hàng dữ liệu bổ sung trong dữ liệu nguồn.
Nếu bạn sử dụng tham chiếu ô trực tiếp trong đối số a thay vì tham chiếu dựa trên bảng, công thức sẽ không tự động nhận diện bất kỳ dữ liệu mới nào trừ khi bạn thay đổi các tham chiếu ô một cách 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ị hơn là các ô không có cấu trúc, vì chúng cung cấp các công cụ và khả năng thích ứng tốt hơn.
Bây giờ, bạn có thể dễ dàng sao chép dữ liệu tóm tắt này sang một trang 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.
2. Trích Xuất Hàng Đầu Tiên và Cuối Cùng
Tương tự, việc trích xuất các hàng đầu tiên và cuối cùng là rất tiện lợi để tạo các bản tóm tắt ngang (horizontal summaries) cho dữ liệu của bạn.
Sử dụng cùng ví dụ như trên, bao gồm cả “Shop K” được thêm vào, giả sử bây giờ bạn muốn tóm tắt tổng số sản phẩm đã bán mỗi tháng.
Trong một ô trống, bạn nhập công thức:
=CHOOSEROWS(Sales[#ALL],1,-1)Và nhấn Enter. Tương tự như 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 tra cứu chỉ mục.
Kết quả của công thức CHOOSEROWS trong Google Sheets trích xuất hàng đầu tiên và hàng cuối cùng của tập dữ liệu.
III. Ví Dụ Ứng Dụng 2: Kết Hợp CHOOSECOLS Với Hàm VSTACK để Trích Xuất Từ Nhiều Phạm Vi
Trong Ví dụ 1, chúng ta đã trích xuất các cột và hàng cụ thể từ các phạm vi dữ liệu riêng lẻ. Tuy nhiên, bạn có thể kết hợp hàm CHOOSECOLS với hàm VSTACK để kết hợp các kết quả trích xuất từ nhiều phạm vi cùng một lúc, tạo ra một bảng dữ liệu hợp nhất và mạnh mẽ hơn.
Ở đây, bạn có kế hoạch 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).
Bảng tính Google Sheets chứa điểm số của 10 đội từ hai giải đấu trong sáu trận đấu.
Điều đầ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 cảm giác bạn đang xem một tập dữ liệu kết hợp duy nhất.
Vì vậy, trong các ô J1 và K1, bạn nhập các tiêu đề cột một cách thủ công:
Tiêu đề hai cột dự kiến được trích xuất dữ liệu được nhập vào ô J1 và K1 trong Google Sheets.
Sau đó, trong ô J2, bạn nhập công thức sau:
=CHOOSECOLS(VSTACK(League1,League2),1,-1)Trong đó:
CHOOSECOLSchỉ dẫn Google Sheets rằng bạn sẽ trích xuất các cột nhất định.VSTACKkết hợp kết quả theo chiều dọc (do đó có chữ “v” trong tên hàm).League1,League2là các phạm vi chứa các cột, và việc không bao gồm[#ALL]trong công thức cho Google Sheets biết không bao gồm các hàng tiêu đề.1trích xuất cột đầu tiên (“Team”) từ mỗi phạm vi.-1trích xuất cột cuối cùng (“Tot”) từ mỗi phạm vi.
Khi bạn nhấn Enter, đây là kết quả bạn nhận được:
Kết quả của công thức CHOOSECOLS trong Google Sheets, kết hợp với hàm VSTACK, để trích xuất dữ liệu từ nhiều bảng.
Nâng Cao: Kết Hợp Với Hàm SORT để Sắp Xếp Dữ Liệu
Bạn có thể tiến thêm một bước nữa và lồng toàn bộ công thức trên vào 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, bạn nhấp đúp vào ô mà bạn đã nhập 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 đó:
SORTlà hàm dùng để sắp xếp một phạm vi dựa trên một cột được chỉ định.2cho Google Sheets biết sắp xếp phạm vi theo cột thứ hai (cột tổng điểm).FALSEchỉ 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).
Kết quả của công thức CHOOSECOLS trong Google Sheets, kết hợp với các hàm SORT và VSTACK, để trích xuất dữ liệu từ các bảng khác nhau.
Đ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ẽ tự động điều chỉnh tương ứng. Ví dụ, việc thay đổi điểm Game 6 của Đội H từ 51 thành 89 sẽ đưa họ từ vị trí thứ chín lên vị trí thứ tư.
Dữ liệu được thay đổi trong bảng tính Google Sheets, và kết quả công thức được cập nhật tương ứng.
IV. Ví Dụ Ứng Dụng 3: Làm Cho Hàm CHOOSECOLS và CHOOSEROWS Động Hơn
Cho đến nay, tôi đã chỉ cho bạn cách sử dụng CHOOSECOLS và CHOOSEROWS để trích xuất các cột và hàng đầu tiên và cuối cùng từ một phạm vi trong Google Sheets. Tuy nhiên, trong ví dụ cuối cùng này, tôi sẽ chỉ cho bạ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 các ô – thay vì sử dụng các số thứ tự cố định – trong các đối số.
Tôi sẽ minh họa cách sử dụng CHOOSECOLS một cách động, mặc dù bạ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 kết hợp cho mỗi trận. Mục tiêu của bạn là tạo một bản tóm tắt dựa trên 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 để bạn có dữ liệu để làm việc khi tạo công thức CHOOSECOLS.
Bảng dữ liệu trong Google Sheets có tên TeamScores, và một bảng trích xuất dữ liệu nơi công thức sẽ được thực thi.
Sau đó, trong ô A11, bạn nhập công thức:
=CHOOSECOLS(TeamScores,1,B9+1)Trong đó:
CHOOSECOLSchỉ dẫn Google Sheets rằng bạn sẽ trích xuất các cột nhất định.TeamScoreslà tên của bảng chứa dữ liệu bạn muốn trích xuất.1là số thứ tự cho cột “Team”.B9+1cho 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, chứ không phải cột đầu tiên. Do đó, việc nhập 4 vào ô B9 sẽ trích xuất dữ liệu từ cột thứ năm của bảng, chính là trận đấu 4.
Đây là kết quả bạn nhận được khi nhấn Enter:
Điểm số từ Trận đấu 4 được trích xuất từ một bảng trong Google Sheets bằng cách sử dụng CHOOSECOLS.
Bây giờ, hãy nhập một số trận đấu khác vào ô B9 để xem kết quả tự động cập nhật tương ứng.
Điểm số của mỗi đội từ Trận đấu 2 được trích xuất từ một bảng trong Google Sheets bằng cách sử dụng CHOOSECOLS.
Mẹo Chuyên Gia: 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 ô.
Để thực hiện điều này, hã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 trong Google Sheets, và Quy tắc xác thực dữ liệu được chọn từ menu thả xuống tab Dữ liệu.
Sau đó, trong bảng “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).
Thêm Quy tắc được chọn trong bảng 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 vào khả thi đầu tiên 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 quy trình này cho đến khi tất cả các tùy chọn đầ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 “Xong” (Done).
Các mục đang được nhập vào bảng Quy tắc xác thực dữ liệu cho danh sách thả xuống trong Google Sheets.
Bạn cũng có thể chọn “Danh sách thả xuống từ một phạm vi” (Dropdown From A Range) trong trường “Tiêu chí”, nơi bạn có thể chọn các ô chứa các mục nhập 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 phạm vi. Thay vào đó, bạn nên thiết lập một trang tính riêng biệt nơi bạn có thể lưu trữ tất cả các phạm vi 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ả tự động cập nhật tương ứng.
Mũi tên thả xuống trong một ô trong Google Sheets có các quy tắc xác thực dữ liệu được đính kèm.
Các hàm CHOOSECOLS và CHOOSEROWS trong Google Sheets tạo ra các mảng động, nghĩa là kết quả sẽ tràn ra từ ô mà bạn đã nhập công thức. Do đó, bạn phải đảm bảo có đủ không gian trong bảng tính của mình trước khi nhập công thức. Nếu không, bạn sẽ thấy lỗi #REF!, báo hiệu rằng có sự xung đột về không gian hiển thị dữ liệu.
Lời kết:
Việc nắm vững cách sử dụng các hàm CHOOSECOLS và CHOOSEROWS là một kỹ năng vô cùng giá trị, giúp bạn tối ưu hóa quy trình làm việc với dữ liệu trong Google Sheets. Từ việc trích xuất các cột/hàng cơ bản đến kết hợp với các hàm nâng cao như VSTACK và SORT, khả năng xử lý dữ liệu của bạn sẽ được nâng lên một tầm cao mới. Hãy áp dụng những kiến thức này vào công việc hàng ngày để biến những tác vụ phức tạp thành những thao tác đơn giản, linh hoạt và hiệu quả hơn. Nếu bạn có bất kỳ thắc mắc hoặc muốn chia sẻ kinh nghiệm sử dụng các hàm này, hãy để lại bình luận bên dưới.
