Công thức nhìn khủng quá, em xem mà không hiểu, ai có thể "chặt" ra từng khúc giải thích cho em được không?
Như đã hứa, anh ghi sơ dưới đây vài nét chính để em tìm hiểu về các công thức Mảng tại bài trên.
Từ từ rồi em sẽ có sự tổng hợp về các cách giải quyết của công thức mảng theo từng phân đoạn.
Như các bài trên phải xử lý:
- Gộp (hay nối) hay không 1 Mảng từ nhiều mảng.
- Loại bỏ dữ liệu trùng lắp, hay liệt kê toàn bộ dữ liệu không phân biệt có trùng hay không.
- Truy xuất ô, hoặc vị trí của 1 thành phần hiện hữu trong Mảng.
1/ Gộp (hoặc nối đuôi) có hay không từ nhiều mảng về 1 Mảng:
Như bài #2 anh giải, dùng =OFFSET($A$2,MOD(ROW($1:$10)-1,5),INT((ROW($1:$10)-1)/5)*2), tức nối đuôi Vùng C2:C6 vào Vùng A2:A6, tạo thành Mảng 1 chiều dọc gồm 10 phần tử {A;A;B;B;C;C;D;A;A;A}. Từ đó, khi muốn lấy ra phần tử nào thỏa điều kiện thì dùng Match(): trả về kết quả dòng, hoặc Lookup()...tùy biến, sao cho tìm được số dòng của nó là giải quyết bài toán. (Hàm cơ bản MOD() và INT() em đã biết nên không bàn ở đây).
Nói thêm: Tùy theo yêu cầu, khi ta cần tạo Mảng gộp từ 2 hoặc nhiều Vùng, liền kề hay cách khoảng, ta có thể dùng các cách sau:
- OFFSET(...,....,...)
- VLOOKUP/HLOOKUP(...,...,{1,2,3,...,n},...)
- CHOOSE({1,2,...,n}, Vùng 1, Vùng 2,..., Vùng n)
- LOOKUP( 'Mảng 1 chiều dọc/ngang', 'Mảng chọn hướng' , Vùng/Mảng giá trị cùng chiều với 'Mảng chọn hướng')
Nếu không gộp, thì khi xử lý Mảng ta phải định vị trí Hàng (row) và Cột (column) để tìm cách đánh dấu nó, cho nên phải dùng công thức:
=ROW(1:n)*10^m + COLUMN(1:d) -> Vd: ứng với A2:A6 là {2001;3001;4001;5001;6001}, tương tự cho Vùng A2:C6 là {2001,2002,2003;3001,3002,3003;.....;6001,6002,6003}.
Nhìn chung, dù gộp hay không cũng phải có liên quan đến hai hàm mảng ROW() và COLUMN() (hoặc Mảng hằng số như {2,3,4,5,6} hay {5;7;8;9;10}.v.v..), nhờ nó mà từ công thức thường chỉ chứa những thông số có giá trị đơn thành công thức Mảng, hay những công thức chấp nhận có đối số là Mảng.
2/ Loại bỏ dữ liệu trùng lắp, hay liệt kê toàn bộ dữ liệu không phân biệt có trùng hay không.
Cách thường hay dùng của công thức để loại bỏ trùng lắp là:
= INDEX( 'Vùng/Mảng chứa toàn bộ dữ liệu' , MATCH( 0, COUNTIF( 'Vùng chứa kết quả' , 'Vùng/Mảng chứa toàn bộ dữ liệu' ) , 0 ))
Nhờ hàm COUNTIF() như trên khi xét thấy 'Vùng chứa kết quả' có giá trị tồn tại trong Vùng khớp với 'Vùng/Mảng chứa toàn bộ dữ liệu' nó sẽ đếm là 1, không khớp thì bằng 0, nhờ Match(0,...) truy tìm giá trị 0 đầu tiên sẽ ra kết quả dòng nào cần lấy ra. Tức lúc bấy giờ hàm COUNTIF() có chức năng báo hiệu hễ dữ liệu nào trùng thì báo 1, không trùng là 0.
3/ Truy xuất ô, hoặc vị trí của 1 thành phần hiện hữu trong Mảng.
Nếu dùng cách gộp 2 Vùng về thành 1 Mảng thì chỉ cần dùng =INDEX(..,Match(0,..,0)) như trên là tìm ra phần tử thỏa điều kiện.
Nếu dùng cách 'đánh dấu' phần tử Mảng bằng Row()*10^n+Column() thì có nhiều cách để xử lý, nhưng cách ngắn gọn nhất là dùng bộ công thức INDIRECT(...,0) (Đối số 0 nhằm chọn kết quả có dạng RnCn, Vd: như thông thường ta ghi là D7, thì tương đương =R7C4) kết hợp với TEXT(....,"R00xC00y"), cụ thể là:
=INDIRECT(TEXT( 'Mảng kết quả trả về' ,"R00xC00y"),0)
Riêng về 'Mảng kết quả trả về': tùy theo yêu cầu của bài nhưng không ra khỏi dạng tổng quát dùng Small()/Large() hay Aggregate(14(15),6,...,.) mà kết hợp thêm với điều kiện Loại bỏ trùng lắp COUNTIF() ở trên đã nêu, ví dụ:
Để sắp thứ tự từ nhỏ đến lớn thì thêm hàm: COUNTIF(Vùng a,"<"&Vùng b)
Hoặc tính tổng SL rồi sắp từ Lớn đến nhỏ dùng thêm SUMIF(Vùng a, Vùng tiêu chí, Vùng giá trị).
Anh chỉ đưa ra hướng nhìn về công thức Mảng để các em tự lần ra và nghiên cứu thêm thì mới thấy cái hay của nó.
Muốn dò thì phải dùng công cụ để tìm hiểu: em có thể chọn trọn đoạn công thức nào muốn tìm hiểu xong nhấn F9 sẽ thấy kết quả của đoạn đó.
Hoặc chọn cả công thức, dùng công cụ hỗ trợ Formulas\Evaluate Formula, rồi nhấn Evaluate từng lần một sẽ thấy cách hoạt động và trả kết quả của công thức đó. Cũng phải tự dò tìm những bài hay về công thức Mảng mà anh đã cung cấp tên của những nickname ở bài #4 để bổ sung kiến thức Mảng của mình.
Chúc em học tập vui với anh em GPE
Thân
