Trong bối cảnh quản lý và phân tích dữ liệu ngày càng phức tạp, việc trích xuất thông tin cụ thể từ các bảng tính lớn có thể tốn rất nhiều thời gian và công sức. Các hàm truyền thống thường yêu cầu lồng ghép nhiều công thức hoặc thao tác thủ công phức tạp. Tuy nhiên, với sự ra đời của các hàm CHOOSECOLS và CHOOSEROWS trong Microsoft Excel, quy trình này đã trở nên dễ dàng và linh hoạt hơn bao giờ hết.
Hai hàm này là một “cuộc cách mạng” thực sự, cho phép bạn nhanh chóng trích xuất các cột hoặc hàng mong muốn từ tập dữ liệu mà không cần phải sử dụng nhiều hàm lồng ghép. Hơn thế nữa, chúng là những hàm động, có khả năng tự động điều chỉnh kết quả khi dữ liệu nguồn thay đổi, mang lại hiệu quả vượt trội trong việc xây dựng các báo cáo và bảng điều khiển động.
Lưu ý: Tại thời điểm hiện tại, các hàm CHOOSECOLS và CHOOSEROWS chỉ khả dụng cho người dùng Excel phiên bản Microsoft 365 trên Windows, Mac hoặc Excel cho nền tảng web.
Cú pháp CHOOSECOLS và CHOOSEROWS: Hiểu Rõ Để Sử Dụng Hiệu Quả
Hàm CHOOSECOLS và CHOOSEROWS trong Excel có cấu trúc rất tương đồng, như “anh em song sinh” nhưng có những điểm khác biệt tinh tế trong ứng dụng. Cú pháp của chúng cũng phản ánh sự tương đồng này.
Dưới đây là cú pháp của hàm CHOOSECOLS:
=CHOOSECOLS(a,b,c,…)Và đây là cú pháp của hàm CHOOSEROWS:
=CHOOSEROWS(a,b,c,…)Trong cả hai trường hợp:
a(bắt buộc) là mảng gốc chứa các cột (đối với CHOOSECOLS) hoặc hàng (đối với CHOOSEROWS) bạn muốn trích xuất.b(bắt buộc) là số chỉ mục của cột (đối với CHOOSECOLS) hoặc hàng (đối với CHOOSEROWS) đầu tiên cần trích xuất.c(tùy chọn) đại diện cho các số 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 chỉ mục phải được phân tách bằng dấu phẩy.
Lưu ý quan trọng: Nếu một số chỉ mục đại diện cho một cột hoặc hàng ở giữa mảng (nghĩa là không phải cột hoặc hàng đầu tiên hoặc cuối cùng), việc thêm hoặc xóa cột/hàng vào giữa mảng sẽ làm thay đổi chỉ mục của cột hoặc hàng đó.
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, điểm khác biệt chính là hàm TAKE cho phép bạn trích xuất x cột hoặc hàng đầu tiên/cuối cùng, hoặc một cột/hàng duy nhất từ một vị trí bất kỳ trong bảng được đặt tên. Trong khi đó, CHOOSECOLS và CHOOSEROWS mang lại sự linh hoạt cao hơn, 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.
Biểu tượng Excel và bóng đèn tượng trưng cho ý tưởng, trên nền bảng tính Excel
Ví dụ 1: Trích Xuất Cột hoặc Hàng Đầu Tiên và Cuối Cùng Từ Bảng Dữ Liệu
Việc trích xuất cột hoặc hàng đầu tiên và cuối cùng từ một bảng là một nhu cầu rất phổ biến. Điều này đặc biệt tiện lợi 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.
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 đang tạo một báo cáo tóm tắt điểm số của năm đội qua năm trận đấu.
Bảng Excel tổng hợp điểm của 5 đội qua 5 trận đấu, bao gồm hàng tiêu đề và cột tổng
Tập dữ liệu đầu tiên bạn muốn tạo sẽ cho bạn biết tổng số điểm mà mỗi đội đã ghi được. Để làm điều này, trong một ô trống bất kỳ, 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” và “Total”.T_Gameslà tên của bảng nơi dữ liệu được lưu trữ, và[#All]báo cho Excel rằng bạn muốn bao gồm cả hàng tiêu đề và hàng tổng trong kết quả.1là chỉ số của cột đầu tiên (trong trường hợp này là cột “Team”).-1là chỉ số 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.
Mẹo SEO: 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 trừ (-) phía trước các số chỉ mục liên quan.
Đây là kết quả bạn nhận được sau 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 một workbook (ví dụ: một tab dashboard) 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.
Kết quả sau khi áp dụng công thức CHOOSECOLS để trích xuất cột Team và Total từ bảng Excel
Điều tuyệt vời là ngay cả khi bạn thêm dữ liệu mới vào bảng gốc, kết quả – bao gồm cả hàng Total – sẽ tự động cập nhật tương ứng.
Bảng Excel với dữ liệu được cập nhật và kết quả CHOOSECOLS tự động thay đổi
Bạn cũng có thể sử dụng phương pháp tương tự với các bảng không đị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 hoặc cột vào bên phải/dưới của mảng, công thức sẽ không tự động nhận diện trừ khi bạn thay đổi tham chiếu ô thủ công. Nhìn chung, các bảng Excel cung cấp công cụ và khả năng thích ứng tốt hơn so với các ô không cấu trúc.
Báo cáo tiếp theo bạn muốn tạo sẽ hiển thị tổng số điểm ghi được trong mỗi trận đấu (hàng 1 và hàng 8 trong ví dụ).
Vì vậy, trong một ô trống, hãy nhập công thức sau:
=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 buộc Excel phải tính cả hàng tiêu đề và hàng tổng khi xử lý các số chỉ mục.
Kết quả của công thức CHOOSEROWS trong Excel, hiển thị hàng tiêu đề và hàng tổng từ bảng dữ liệu
Ví dụ 2: Trích Xuất Cột Từ Nhiều Phạm Vi Bảng (Kết Hợp VSTACK và SORTBY)
Giả sử bạn có một bảng tính với điểm tổng của mỗi đội từ ba giải đấu khác nhau: League 1 (xanh lá), League 2 (xanh dương) và League 3 (xám). Mục tiêu của bạn là tạo một danh sách tổng hợp điểm số của các đội từ tất cả các giải đấu này.
Bảng tính Excel chứa điểm số của các đội qua ba giải đấu riêng biệt (League 1, 2, 3)
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 trong công thức CHOOSECOLS để xếp chồng ba bảng trực tiếp lên nhau theo chiều dọc. Vì vậy, trong các ô I1 và J1, hãy tạo tiêu đề cột thủ công.
Tiêu đề cột được nhập thủ công vào bảng tính Excel, chuẩn bị cho kết quả của hàm CHOOSECOLS và VSTACK
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ẽ dùng để trích xuất các 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 các bảng lên nhau.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 trong kết quả.1báo cho Excel trích xuất cột đầu tiên (“Team”) từ mỗi mảng.-1báo cho Excel trích xuất cột cuối cùng từ mỗi mảng (“Total”).
Nhấn Enter để xem kết quả.
Kết quả của công thức CHOOSECOLS kết hợp với VSTACK, trích xuất cột đầu tiên và cuối cùng từ ba bảng khác nhau
Tại thời điểm này, bạn có thể tiến thêm một bước nữa 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. Dấu # (Spill Range Operator) sau I2 cho biết bạn đang tham chiếu toàn bộ mảng kết quả của công thức trước đó.
Bảng tính Excel hiển thị kết quả CHOOSECOLS và VSTACK, sau đó được sắp xếp bằng hàm SORTBY
Ví dụ 3: Ứng Dụng CHOOSECOLS Với Data Validation và Conditional Formatting
Cho đến nay, chúng ta đã khám phá 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/cuối cùng. Tuy nhiên, trong ví dụ này, bạn sẽ thấy cách sử dụng CHOOSECOLS để trích xuất các cột khác, kết hợp 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 điểm ghi được trong mỗi trận ở hàng tổng. Mục tiêu của bạn là tạo ra một kết quả hiển thị điểm số của mỗi đội và tổng điểm chung trong các ô A11 đến B15 cho một 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 các đội qua năm trận đấu và khu vực trống để hiển thị kết quả 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:
=CHOOSECOLS(T_Scores[[#Data],[#Totals]],1,B9+1)Trong đó:
CHOOSECOLSlà hàm dùng để trích xuất các cột.T_Scoreslà tên của bảng, và[[#Data],[#Totals]]báo cho Excel rằng bạn muốn bao gồm cả 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+1báo cho Excel 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 thêm+1ở đây là vì các số trận đấu bắt đầu từ cột thứ hai của bảng. Kết quả là, 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 3.
Công thức CHOOSECOLS trong Excel sử dụng giá trị từ một ô khác (B9) để xác định chỉ số cột cần trích xuất
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 ai đó vô tình nhập một số không hợp lệ, hàm CHOOSECOLS sẽ trả về lỗi #VALUE!.
Mẹo xử lý lỗi: Excel trả về lỗi #VALUE! nếu bất kỳ số chỉ mục nào là 0 hoặc vượt quá số lượng cột hoặc hàng trong mảng.
Đây là trường hợp lý tưởng để sử dụng tính năng Data Validation (Xác thực dữ liệu) trong ô B9 để tạo một danh sách thả xuống các số trận đấu có sẵn.
Cách ưu tiên để làm điều này cho một hàng tiêu đề trong bảng Excel là trước tiên chọn tất cả các ô liên quan, cộng thêm một vài ô phụ để dự phòng cho việc mở rộng, và đặt tên cho phạm vi trong hộp tên ở góc trên bên trái cửa sổ Excel. Lưu ý rằng tôi đã không chọn tiêu đề cột “Team”, vì tôi không muốn nó xuất hiện trong danh sách thả xuống game ở ô B9.
Các tiêu đề cột trong bảng Excel được chọn và đặt tên là 'Games' trong hộp tên, chuẩn bị cho Data Validation
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 thanh ribbon.
Ô B9 trong Excel được chọn, và nút Data Validation trong tab Data đang được nhấn
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 là tên bạn vừa đặt cho phạm vi 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, Ignore Blank được chọn và '=Games' được nhập vào mục Source
Giờ đây, 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ệ, 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à phạm vi được đặt tên trong Data Validation sẽ hoạt động cùng nhau để cập nhật các tùy chọn có sẵn tương ứng.
Thanh thả xuống Data Validation trong Excel hoạt động cùng với hàm CHOOSECOLS để tạo ra tập dữ liệu trích xuất động
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 thanh ribbon, nhấp vào “Conditional Formatting”, di chuột qua “Data Bars”, và chọn một màu nền đồng nhất.
Các ô trong Excel được chọn, và tùy chọn Data Bars trong menu thả xuống Conditional Formatting đang được làm nổi bậ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à định dạng dữ liệu để tăng cường sự rõ ràng về mặt trực quan.
Bảng tính Excel hiển thị kết quả CHOOSECOLS dựa trên danh sách thả xuống, được định dạng có điều kiện bằng Data Bars
Những Lưu Ý Quan Trọng Khi Sử Dụng CHOOSECOLS và CHOOSEROWS
Các hàm CHOOSECOLS và CHOOSEROWS tạo ra các 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 trống trong bảng tính của bạn – nếu không, bạn sẽ gặp phải lỗi #SPILL! đáng sợ. Ngoài ra, vì mảng động và các bảng Excel được định dạng không kết hợp tốt 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 sử dụng Conditional Formatting (định dạng có điều kiện).
Kết Luận
Các hàm CHOOSECOLS và CHOOSEROWS là những bổ sung vô cùng giá trị cho bộ công cụ Excel, giúp người dùng tiết kiệm đáng kể thời gian và công sức trong việc quản lý, phân tích dữ liệu. Khả năng trích xuất dữ liệu động, linh hoạt, kết hợp với các tính năng khác như Data Validation và Conditional Formatting, mở ra nhiều cơ hội để tạo ra các báo cáo và bảng điều khiển trực quan, chuyên nghiệp và hiệu quả hơn.
Với những kiến thức và ví dụ minh họa trên, hy vọng bạn đã nắm vững cách sử dụng các hàm này và sẵn sàng áp dụng chúng vào công việc hàng ngày của mình. Hãy bắt đầu khám phá và chia sẻ trải nghiệm của bạn về CHOOSECOLS và CHOOSEROWS để tận dụng tối đa sức mạnh của Excel!
