Trong kỷ nguyên dữ liệu bùng nổ, việc xử lý và trích xuất thông tin chính xác, nhanh chóng từ các tập dữ liệu lớn là kỹ năng then chốt đối với bất kỳ ai làm việc với Excel. Microsoft Excel đã liên tục cải tiến để đáp ứng nhu cầu này, và sự ra đời của các hàm CHOOSECOLS và CHOOSEROWS là một minh chứng rõ ràng. Đây là những “người thay đổi cuộc chơi” thực sự, giúp bạn nhanh chóng trích xuất các cột hoặc hàng cụ thể từ dữ liệu của mình mà không cần phải lồng ghép nhiều hàm phức tạp. Hơn nữa, chúng là các hàm động, nghĩa là chúng sẽ tự động điều chỉnh theo những thay đổi trong bộ dữ liệu của bạn, mang lại hiệu quả vượt trội.
Với những ai đang sử dụng Excel cho Microsoft 365 trên Windows hoặc Mac, hoặc Excel cho web, các hàm này đã sẵn sàng để bạn khám phá và ứng dụng vào công việc hàng ngày, từ việc tạo báo cáo chuyên nghiệp đến việc phân tích dữ liệu chuyên sâu. Hãy cùng tincongnghemoi.com đi sâu vào tìm hiểu cách các hàm này hoạt động và làm thế nào bạn có thể tận dụng chúng để tối ưu hóa quy trình làm việc của mình.
Giới Thiệu Chung về CHOOSECOLS và CHOOSEROWS
Các hàm CHOOSECOLS và CHOOSEROWS của Excel có thể được ví như cặp song sinh: chúng có cấu trúc rất giống nhau nhưng lại được phân biệt bởi những khác biệt tinh tế. Điều này thể hiện rõ nhất trong cú pháp của chúng.
Cú pháp của hàm CHOOSECOLS:
=CHOOSECOLS(mảng, chỉ_số_cột_1, [chỉ_số_cột_2], ...)Và đây là cú pháp của hàm CHOOSEROWS:
=CHOOSEROWS(mảng, chỉ_số_hàng_1, [chỉ_số_hàng_2], ...)Trong cả hai trường hợp:
mảng(bắt buộc) là vùng dữ liệu gốc chứa các cột (CHOOSECOLS) hoặc hàng (CHOOSEROWS) mà bạn muốn trích xuất.chỉ_số_cột_1(bắt buộc) hoặcchỉ_số_hàng_1(bắt buộc) là số thứ tự (chỉ mục) của cột hoặc hàng đầu tiên cần trích xuất.chỉ_số_cột_2,chỉ_số_hàng_2(tùy chọn) biểu thị các chỉ mục của bất kỳ cột hoặc hàng bổ sung nào cần trích xuất, mỗi chỉ mục phải được phân tách bằng dấu phẩy.
Lưu ý quan trọng: Nếu bạn sử dụng chỉ mục cho một cột hoặc hàng nằm ở giữa mảng (nghĩa là không phải cột/hàng đầu tiên hoặc cuối cùng), việc thêm các cột hoặc hàng mới vào giữa mảng sẽ làm thay đổi chỉ mục của cột hoặc hàng đó. Điều này có nghĩa là công thức cần được kiểm tra lại hoặc điều chỉnh.
Bạn có thể nghĩ rằng các hàm này nghe có vẻ rất giống với hàm TAKE. Tuy nhiên, trong khi TAKE cho phép bạn trích xuất x cột hoặc hàng đầu tiên hoặc cuối cùng, hoặc một cột/hàng duy nhất từ một vị trí cụ thể trong bảng, thì CHOOSECOLS và CHOOSEROWS lại linh hoạt hơn nhiều. Chúng cho phép bạn trích xuất bất kỳ số lượng cột hoặc hàng nào, theo bất kỳ thứ tự nào, từ bất kỳ vị trí nào trong dữ liệu của bạn. Đây chính là điểm tạo nên lợi thế đáng kể cho hai hàm này trong việc xử lý và tùy chỉnh dữ liệu.
Bàn tay cầm biểu tượng Excel và bóng đèn ý tưởng bên cạnh bảng tính, minh họa cho các hàm CHOOSECOLS và CHOOSEROWS động trong Excel.
Ví Dụ 1: Trích Xuất Cột hoặc Hàng Đầu Tiên và Cuối Cùng Từ Một Bảng
Trong quá trình làm việc với Excel, bạn có thể thường xuyên cần trích xuất cột hoặc hàng đầu tiên và cuối cùng từ một bảng. Điều này đặ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 tổng số.
Để dễ dàng thực hành, bạn có thể tải xuống tập tin Excel ví dụ được sử dụng trong hướng dẫn này. Sau khi nhấp vào liên kết, bạn sẽ tìm thấy nút tải xuống ở góc trên bên phải màn hình.
Hãy tưởng tượng bạn là quản trị viên cho một giải đấu thể thao địa phương và bạn cần tạo một báo cáo tóm tắt điểm số của năm đội trong năm trận đấu.
Bạn muốn tạo một tập dữ liệu đầu tiên cho biết tổng số điểm mà mỗi đội đã ghi được. Để làm điều này, trong một ô trống, hãy nhập công thức sau:
=CHOOSECOLS(T_Games[#All],1,-1)Trong đó:
CHOOSECOLSlà hàm bạn muốn sử dụng, vì bạn đang trích xuất dữ liệu từ các cột “Team” (Đội) và “Total” (Tổng).T_Gameslà tên của bảng nơi mảng dữ liệu được lưu trữ và[#All]cho Excel biết rằng bạn muốn bao gồm cả hàng tiêu đề và hàng tổng cộng trong kết quả.1là chỉ mục của cột đầu tiên (trong trường hợp này là cột “Team”).-1là chỉ mục của cột cuối cùng (trong trường hợp này là cột “Total”).
Sau khi nhập công thức, nhấn Enter.
Theo mặc định, CHOOSECOLS đếm từ trái sang phải, và CHOOSEROWS đếm từ trên xuống dưới. Để đảo ngược thứ tự này, bạn chỉ cần đặt ký hiệu dấu trừ (-) phía trước các số chỉ mục tương ứng.
Đây là kết quả bạn nhận được khi nhấn Enter. Dữ liệu này có thể được sao chép sang một trang tính khác trong cùng sổ làm việc (ví dụ: một tab bảng điều khiển), hoặc sao chép và dán dưới dạng văn bản vào email hoặc tài liệu Word.
Bảng tính Excel hiển thị dữ liệu gốc và kết quả trích xuất cột đầu tiên (Team) và cuối cùng (Total) bằng hàm CHOOSECOLS.
Đáng chú ý, ngay cả khi có thêm dữ liệu vào bảng, kết quả – bao gồm cả hàng Tổng – sẽ tự động cập nhật tương ứng. Điều này thể hiện tính động tuyệt vời của hàm.
Kết quả của hàm CHOOSECOLS tự động cập nhật khi có thêm dữ liệu vào bảng Excel gốc, minh họa tính năng động của hàm.
Bạn cũng có thể sử dụng phương pháp tương tự với các bảng chưa định dạng bằng cách sử dụng tham chiếu ô trực tiếp thay vì tham chiếu cấu trúc. Tuy nhiên, nếu bạn thêm nhiều hàng vào bên phải hoặc cột vào bên dưới mảng của mình, công thức sẽ không nhận diện được chúng trừ khi bạn thay đổi tham chiếu ô theo cách thủ công. Nhìn chung, bảng trong Excel cung cấp các công cụ và khả năng thích ứng tốt hơn so với các ô không có cấu trúc.
Tiếp theo, bạn muốn tạo báo cáo hiển thị số điểm ghi được trong mỗi trận đấu (hàng 1 và 8).
Vì vậy, trong một ô trống, hãy nhập:
=CHOOSEROWS(T_Games[#All],1,-1)và nhấn Enter. Hãy nhớ rằng việc thêm [#ALL] sau tên bảng trong công thức sẽ buộc Excel tính cả hàng tiêu đề và hàng tổng cộng khi xử lý các số chỉ mục.
Ví Dụ 2: Trích Xuất Cột Từ Nhiều Phạm Vi Dữ Liệu
Bây giờ, giả sử bạn có bảng tính này và mục tiêu của bạn là tạo một danh sách chứa tổng điểm của mỗi đội trong Giải đấu 1 (xanh lá), 2 (xanh dương) và 3 (xám).
Bảng tính Excel với điểm số của các đội qua ba giải đấu (League 1, League 2, League 3), chuẩn bị để kết hợp dữ liệu.
Lần này, bạn không muốn Excel trích xuất tiêu đề cột và tổng hàng, vì bạn sẽ lồng hàm VSTACK vào bên trong công thức CHOOSECOLS để xếp chồng ba bảng trực tiếp lên nhau. Vì vậy, trong các ô I1 và J1, hãy tạo tiêu đề cột theo cách thủ công.
Sau đó, trong ô I2, nhập công thức sau:
=CHOOSECOLS(VSTACK(League_1,League_2,League_3),1,-1)Trong đó:
CHOOSECOLSlà hàm bạn sẽ sử dụng để trích xuất cột.VSTACKcho phép bạn kết hợp các kết quả theo chiều dọc (xếp chồng).League_1, League_2, League_3là tên của các bảng đại diện cho các mảng dữ liệu. Việc không có[#ALL]sau tên bảng cho Excel biết không bao gồm hàng tiêu đề và hàng tổng cộng trong kết quả.1cho Excel biết để trích xuất cột đầu tiên (“Team”) từ mỗi mảng.-1cho Excel biết để trích xuất cột cuối cùng (“Total”) từ mỗi mảng.
Và nhấn Enter.
Kết quả của công thức CHOOSECOLS lồng ghép VSTACK để trích xuất cột Team và Total từ ba bảng dữ liệu League trong Excel.
Tại thời điểm này, bạn có thể đi xa hơn một bước và sắp xếp kết quả theo thứ tự giảm dần bằng cách nhập công thức sau vào ô L2:
=SORTBY(I2#,J2:J16,-1)Và nhấn Enter.
Bảng tính Excel hiển thị kết quả tổng hợp từ CHOOSECOLS và VSTACK, sau đó được sắp xếp theo thứ tự giảm dần điểm số bằng hàm SORTBY.
Ví Dụ 3: Sử Dụng CHOOSECOLS Với Xác Thực Dữ Liệu (Data Validation) và Định Dạng có Điều Kiện (Conditional Formatting)
Cho đến nay, chúng ta đã thấy lợi ích của việc 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 mảng. Tuy nhiên, lần này, chúng ta sẽ khám phá cách sử dụng CHOOSECOLS để trích xuất các cột khác và kết hợp nó với các công cụ Excel bổ sung để làm cho bảng tính của bạn nổi bật hơn.
Bảng tính này hiển thị điểm số của năm đội qua năm trận đấu, bao gồm tổng số điểm ghi được trong mỗi trận đấu ở hàng tổng cộng. Mục tiêu của bạn ở đây là tạo ra một kết quả hiển thị điểm số của mỗi đội và tổng số điểm tổng thể trong các ô A11 đến B15 cho số trận đấu mà bạn sẽ nhập vào ô B9.
Bảng tính Excel với điểm số của năm đội qua năm trận đấu, cùng khu vực bên dưới để hiển thị kết quả trích xuất cột động với CHOOSECOLS.
Đầu tiên, hãy nhập một số trận đấu vào ô B9, để bạn có dữ liệu để làm việc khi tạo công thức CHOOSECOLS. Sau đó, trong ô A11, nhập công thức sau:
=CHOOSECOLS(T_Scores[[#Data],[#Totals]],1,B9+1)Trong đó:
CHOOSECOLSlà công thức được sử dụng để trích xuất cột.T_Scoreslà tên của bảng và[[#Data],[#Totals]]cho Excel biết bao gồm dữ liệu và tổng cộng trong kết quả, nhưng không bao gồm hàng tiêu đề.1đại diện cho cột đầu tiên (“Team”).B9+1cho Excel biết rằng đối số chỉ mục thứ hai được đại diện bởi giá trị trong ô B9, cộng thêm một. Lý do bạn cần bao gồm+1ở đây là vì các số trận đấu bắt đầu từ cột thứ hai của bảng. Do đó, việc nhập 3 vào ô B9 sẽ trích xuất dữ liệu từ cột thứ tư của bảng, đó là trận đấu 3.
Công thức CHOOSECOLS trong Excel sử dụng giá trị từ ô B9 để xác định chỉ số cột cần trích xuất, tạo ra tính năng động cho báo cáo.
Mặc dù việc nhập số trận đấu vào ô B9 hoạt động tốt, nhưng nếu bạn hoặc người khác vô tình nhập một số không hợp lệ, hàm CHOOSECOLS sẽ trả về lỗi #VALUE!.
Lỗi #VALUE! xuất hiện nếu bất kỳ số chỉ mục nào bằng 0 hoặc vượt quá số cột hoặc hàng trong mảng.
Đây là một trường hợp tốt để sử dụng xác thực dữ liệu (Data Validation) trong ô B9 để tạo một danh sách thả xuống các số trận đấu có sẵn.
Cách tốt nhất để làm điều này đối với hàng tiêu đề trong bảng Excel là đầu tiên chọn tất cả các ô liên quan, cộng thêm một vài ô bổ sung để cho phép mở rộng, và đặt tên cho vùng đó trong hộp tên ở góc trên bên trái cửa sổ Excel. Lưu ý rằng chúng ta không chọn tiêu đề cột “Team” vì không muốn nó xuất hiện trong danh sách thả xuống trận đấu trong ô B9.
Ô B9 được chọn trong Excel, sau đó nhấn nút "Data Validation" trong tab Data để bắt đầu cài đặt xác thực dữ liệu.
Tiếp theo, chọn ô B9 và nhấp vào nửa trên của nút “Data Validation” trong tab Data trên Ribbon.
Bây giờ, trong hộp thoại “Data Validation”, chọn “List” trong trường Allow, đảm bảo “Ignore Blank” được chọn, nhập dấu bằng (=) theo sau tên mà bạn vừa đặt cho vùng tiêu đề cột (ví dụ: =Games), và nhấp vào “OK”.
Hộp thoại Data Validation trong Excel, với tùy chọn "List" được chọn và tên vùng "=Games" được nhập làm nguồn dữ liệu cho danh sách thả xuống.
Bây giờ bạn có thể nhấp vào mũi tên thả xuống trong ô B9 để chọn một số trận đấu hợp lệ. Bạn hoàn toàn yên tâm rằng ngay cả khi bạn thêm các cột bổ sung vào bảng của mình, CHOOSECOLS và vùng được đặt tên trong xác thực dữ liệu sẽ hoạt động cùng nhau để cập nhật các tùy chọn có sẵn một cách tự động.
Danh sách thả xuống Data Validation trong Excel giúp chọn số trận đấu hợp lệ, kết hợp với hàm CHOOSECOLS để tự động cập nhật dữ liệu trích xuất.
Cuối cùng, để trực quan hóa dữ liệu của bạn hơn nữa, hãy chọn các ô B11 đến B15, và trong tab Home trên Ribbon, nhấp vào “Conditional Formatting”, di chuột qua “Data Bars” và chọn một màu tô đầy.
Chọn các ô dữ liệu trong Excel và tùy chọn "Data Bars" trong menu "Conditional Formatting" để trực quan hóa kết quả trích xuất.
Kết quả là, bạn không chỉ trích xuất các cột cụ thể từ dữ liệu của mình bằng CHOOSECOLS, mà còn làm cho kết quả trở nên động bằng cách thêm danh sách thả xuống, và bạn đã định dạng dữ liệu để tăng cường sự rõ ràng về mặt trực quan.
Kết quả của hàm CHOOSECOLS động kết hợp với danh sách thả xuống Data Validation và định dạng có điều kiện Data Bars để trực quan hóa dữ liệu hiệu quả.
Lưu Ý Quan Trọng Khi Sử Dụng CHOOSECOLS và CHOOSEROWS
Các hàm CHOOSECOLS và CHOOSEROWS tạo ra mảng động (dynamic arrays), có nghĩa là kết quả sẽ “tràn” ra từ ô mà bạn nhập công thức. Do đó, trước khi nhập công thức, hãy đảm bảo rằng có đủ không gian trong bảng tính của bạn – nếu không, bạn sẽ gặp lỗi #SPILL!. Ngoài ra, vì mảng động và các bảng Excel đã định dạng không tương thích với nhau, nếu bạn muốn định dạng kết quả, bạn sẽ phải thực hiện thủ công hoặc bằng cách sử dụng định dạng có điều kiện (conditional formatting).
Với khả năng trích xuất dữ liệu linh hoạt, tính năng động và khả năng kết hợp với các công cụ Excel khác, CHOOSECOLS và CHOOSEROWS chắc chắn là những hàm không thể thiếu trong bộ công cụ của bạn. Hãy bắt đầu áp dụng chúng ngay hôm nay để nâng cao hiệu suất làm việc và tạo ra những báo cáo dữ liệu ấn tượng hơn!
Bạn có bất kỳ thủ thuật hay mẹo nào khác khi sử dụng CHOOSECOLS và CHOOSEROWS không? Hãy chia sẻ ý kiến và kinh nghiệm của bạn trong phần bình luận bên dưới hoặc tìm hiểu thêm các thủ thuật Excel khác tại tincongnghemoi.com!
