Đây là bài toán trích xuất danh sách con từ 1 danh sách theo điều kiện:
Điều kiện 1: số 22 (đk cột)
Điều kiện 2: các dòng có số liệu ( <>"")
Sau đó dùng INDEX(bảng viền vàng ,đk2, đk1) để lấy từng giá trị khác 0 tại cột N, và copy xuống liên tiếp.
Điều kiện 1 thì dễ rồi, dùng MATCH:
MATCH($D$7,Dulieu!$L$1:$AM$1,0) (với D7 chứa "số: 22") trả về cột thứ 3
Điều kiện 2 thì khó hơn, vì phải tạo mảng liên tiếp {1,3,...} để excel hiểu dòng 1 thì lấy dòng thứ 1 (giá trị 500), copy xuống dòng dưới thì lấy dòng số 3 (giá trị 2000)
View attachment 248780
Giải pháp: Từ excel 2010 về trước, người ta hay dùng tổ hợp SMALL(IF(...row(indirect(...)) để lấy từng giá trị từ nhỏ tới lớn khi copy xuống. Tuy nhiên, công thức lúc này thành công thức mảng, phải dùng Ctrl-shift-enter
Excel mới sau này, người ta dùng hàm AGGREGATE để lấy từng giá trị từ nhỏ tới lớn, tự động bỏ qua giá trị #ERROR qua các tham số, nên chỉ cần Enter là ra kết quả.
Đây là hàm bạn dùng để xác định tham số dòng:
AGGREGATE(15,6,ROW($1:$99)/(INDEX(Dulieu!$L$3:$AM$60,,MATCH($D$7,Dulieu!$L$1:$AM$1,0))<>""),ROW(D1))
Cú pháp: AGGREGATE(x,y,range,n) với x=15 ( lấy từ nhỏ đến lớn), y=6: bỏ qua giá trị lỗi, n=row(D1) =nhỏ nhất cho dòng đầu, nhỏ thứ 2 cho dòng sau,...
Vậy mấu chốt là tạo range bên trong như thế này: {1,3,6,...} tương ứng với vị trí <>"" trong cột N
Ta bắt đầu từ INDEX: vùng bắt đầu từ cột L, dịch chuyển sang phải 3 cột
INDEX(Dulieu!$L$3:$AM$60,,MATCH($D$7,Dulieu!$L$1:$AM$1,0)) tham chiếu đến cột N: {500;0;2000;0;0;300;...}
Đặt điều kiện cho nó <>"", sẽ thành: {true;false;true;false;false;true;...} và excel hiểu true=1, fasle=0
Dùng dãy số liên tiếp từ 1 tới 99 chia cho dãy 1/0, sẽ trả về dãy {1;#N/A;3;#N/A",#N/A",6,..} để lấy thứ tự dòng >0 trong cột N
Lúc này, tại dòng đầu tiên, tham số dòng sẽ là: AGGREGATE(15,6,{1;#N/A;3;#N/A",#N/A",6,..},1) = 1
dòng số 2 sẽ là: AGGREGATE(15,6,{1;#N/A;3;#N/A",#N/A",6,..},2) = 3
Như vậy dòng đầu sẽ là: INDEX(Dulieu!$L$3:$AM$60,1,3)=500
Copy xuống dòng thứ 2 sẽ là: INDEX(Dulieu!$L$3:$AM$60,3,3)=2000
Tương tự, dòng thứ 3 là 300
Tương tự để lấy tên thuốc, lúc này dùng INDEX(Dulieu!B$3:B$60... và không cần điều kiện cột nữa, vì cột đã được xác định. Điều kiện dòng y như công thức lấy SL ở trên.
Đểu hiểu rõ công thức, bạn lên thanh công thức và bôi đen từng phần đầy đủ của các công thức con bên trong, nhấn F9 để xem kết quả tức thời.
