Tôi chỉ giải thích đoạn công thức:
MATCH(1, INDEX(N(SUMIF($E$3: E3,ROW($1:$100),$C$3: C3)+C4<2000),) , )
Phần công thức đơn giản như IF(C4>0, 'Đúng', 'Sai') thì chắc bạn biết rồi, không cần giải thích thêm.
1/
MATCH(1, 'Vùng/Mảng so khớp' , ) hay tương đương: MATCH(1, 'Vùng so khớp' ,
0 ):
tức tìm giá trị 1 trong 'Vùng/Mảng so khớp', nếu tìm thấy thì
trả về số dòng đang chứa giá trị khớp đúng đầu tiên đó. Hàm này tôi chắc bạn cũng đã rành rồi, nhưng tôi muốn ví dụ thêm để bạn dễ hình dung cho phần giải thích công thức kế tiếp.
- Ví dụ:
- MATCH(1, {1;1;1;1;1;1;1} , 0 ) = 1 --> tìm thấy giá trị 1 trong hàng đầu tiên của 'Vùng so khớp', là giá trị khớp đầu tiên, nên giá trị trả về là 1. Các hàng sau tuy có giá trị 1 nhưng không kể đến.
- MATCH(1, {0;0;0;1;1;1;1} , 0 ) = 4 --> tìm thấy giá trị 1 trong hàng thứ 4 của 'Vùng so khớp', là giá trị khớp đầu tiên, nên giá trị trả về là 4. Các hàng sau tuy có giá trị 1 nhưng không kể đến.
- MATCH(1, {0;1;0;0;0;1;1} , 0 ) = 2 --> tìm thấy giá trị 1 trong hàng thứ 2 của 'Vùng so khớp', là giá trị khớp đầu tiên, nên giá trị trả về là 2. Các hàng sau tuy có giá trị 1 nhưng không kể đến.
2/
INDEX(N(SUMIF($E$3: E3,ROW($1:$100),$C$3: C3)+C4<2000),) là công thức tạo ra kết quả là 1 'Mảng' để làm 'Vùng/Mảng so khớp' trong hàm MATCH(1,...,0) như đã nói ở trên.
- Hàm: SUMIF($E$3: E3,ROW($1:$100),$C$3: C3) dùng "Tiêu chí lọc" là 1 'Mảng' tạo bởi hàm ROW(1:100), tức mảng gồm chứa các giá trị từ 1 đến 100, hay chính là số của các nhóm từ 1 đến 100. Câu lệnh này chạy và biến thiên theo dòng do 'Vùng dò' $E$3: E3 không có cố định biên dưới, nếu xuống dòng 4 thì sẽ thành $E$3: E4, tương tự cho các dòng dưới. Mục đích chính là nhằm cộng lũy kế theo từng nhóm của Vùng trước dòng lập công thức, và kết quả trả về là '1 Mảng' chứa toàn bộ số cộng lũy kế theo từng nhóm (từ 1 đến 100) đó.
- Ví dụ: công thức tại dòng 29, thì SUMIF() sẽ cho ra kết quả cộng lũy kế của Vùng C3: C28. Bạn sẽ thấy Vùng E3: E28 chỉ có các nhóm 1, 2 và 3, nên kết quả trả về sẽ có giá trị chính là tổng số đã lũy kế theo nhóm 1, 2, và 3, còn các dòng khác bằng 0 (xem cột P file đính kèm).
- SUMIF($E$3: E3,ROW($1:$100),$C$3: C3) +C4 <2000: Lấy 'Mảng' chứa số lũy kế theo từng nhóm cộng thêm giá trị 'Length' tại dòng đang đứng (xem cột Q và S), rồi so toàn bộ Mảng đó với 2000. Nếu nhỏ hơn trả ra kết quả TRUE, nếu lớn hơn là FALSE.
- Dùng hàm N() để đổi TRUE thành 1 và FALSE thành 0 (xem cột R và T).
- Do dùng Mảng ROW(1:100) trong 'Tiêu chí' của SUMIF(), nên để báo và hiện đủ mảng kết quả nên dùng thêm hàm Index(... , ).
- Tóm lại: kết quả của đoạn công thức INDEX(....,) là 1 mảng chỉ chứa các giá trị gồm 2 số 0 và 1, tức những nhóm nào có số cộng lũy kế <2000 thì hiện 1, ngược lại là 0.
Khi thực hiện so khớp bằng Match(1, 'Vùng/Mảng so khớp', 0), nó sẽ tìm đến dòng, cũng là số thứ tự của nhóm thỏa điều kiện <2000 đầu tiên tìm thấy, như cách giải thích ở mục "1/" trên.
Thân
