CÔNG THỨC MẢNG INDEX, LỌC RA NHIỀU GIÁ TRỊ NHƯNG CHỈ LẤY 1 GIÁ TRỊ ĐÚNG ĐIỀU KIỆN

[quocgiacan]

dạ để em xem lại, chúc anh ngày vui

Công thức của em muốn ra kết quả phải sửa như sau:
Chọn D13: K17 nhập công thức:

D13=INDEX(OFFSET('Tong hop'!$D$8,SMALL(IF(('Tong hop'!$C$9:$C$22=$K$1)*('Tong hop'!$D$9:$D$22=$K$2),(ROW(1:14))),ROW(1:5)),COLUMN(A1:H1)),)

Ctrl+Shift+Enter (CSE).

Tức với ROW(1:5) (5 giá trị đầu của hàng thỏa điều kiện) và COLUMN(A1:H1) báo cho OFFSET() trả về 1 mảng 2 chiều: 5x8 ứng với Vùng D13:K17.

Và INDEX(...,) sử dụng ở đây không phải "Né" nhấn CSE, mà là hiển thị cho ra giá trị của mảng này.

Chúc em ngày vui.

 

[dazkangel]

Công thức của em muốn ra kết quả phải sửa như sau:
Chọn D13: K17 nhập công thức:

D13=INDEX(OFFSET('Tong hop'!$D$8,SMALL(IF(('Tong hop'!$C$9:$C$22=$K$1)*('Tong hop'!$D$9:$D$22=$K$2),(ROW(1:14))),ROW(1:5)),COLUMN(A1:H1)),)

Ctrl+Shift+Enter.

Tức với ROW(1:5) (5 giá trị đầu của hàng thỏa điều kiện) và COLUMN(A1:H1) báo cho OFFSET() trả về 1 mảng 2 chiều: 5x8 ứng với Vùng D13:K17.

Chúc em ngày vui.

cảm ơn anh, em sẽ kiểm tra sau, em hiện chơi luôn cái index cho lành ^^ và trả về một mảng 2 chiều luôn.
chúc anh buổi tối vui vẻ

 

[Drghost]

H3=IFERROR(OFFSET(OFFSET('BIÊN LAI'!$A$1,,INDEX({0,8,1},,MOD(COLUMN(A1)-1,3)+1)),MATCH(INT((COLUMN(A1)-1)/3)+1,INDEX(COUNTIF(OFFSET('BIÊN LAI'!$C$2,,,ROW($1:$15)),$B3),),0),),"")
Nhờ anh Quocgiacan giải thích rõ hơn từng thành phần trong công thúc giúp em được không. Em cũng có một biểu mẫu gần giống thế này mà áp dụng không được.
Em chưa rõ chỗ {0,8,1} lấy ở đâu và cả chỗ (COLUMN(A1)-1)/3 này nữa
Cảm ơn anh

 

[quocgiacan]

H3=IFERROR(OFFSET(OFFSET('BIÊN LAI'!$A$1,,INDEX({0,8,1},,MOD(COLUMN(A1)-1,3)+1)),MATCH(INT((COLUMN(A1)-1)/3)+1,INDEX(COUNTIF(OFFSET('BIÊN LAI'!$C$2,,,ROW($1:$15)),$B3),),0),),"")
Nhờ anh Quocgiacan giải thích rõ hơn từng thành phần trong công thúc giúp em được không. Em cũng có một biểu mẫu gần giống thế này mà áp dụng không được.
Em chưa rõ chỗ {0,8,1} lấy ở đâu và cả chỗ (COLUMN(A1)-1)/3 này nữa
Cảm ơn anh

Hỏi: Em chưa rõ chỗ {0,8,1} lấy ở đâu

• Đó là số tương ứng với các cột "MBL"-"SỐ TIỀN"-"NGÀY BL" của Sheet "BienLai". Các số này là kết quả trả về trong đoạn công thức: INDEX({0,8,1},,MOD(COLUMN(A1)-1,3)+1) bên sheet "TỔNG HỢP", tức cột đầu (Cột H) trả về 0, khi kéo công thức qua Cột I trả về 8, kéo qua Cột J trả về số 1. Các số này báo về cho hàm OFFSET('BIÊN LAI'!$A$1,,INDEX({0,8,1},,MOD(COLUMN(A1)-1,3)+1)) để lấy các vị trí bên sheet "BienLai" các ô A1 (ứng cột 1), ô I1 (ứng cột 2), ô B1 (ứng cột 3). Rồi tuần tự khi qua cột K (thuộc Lần 2) thì trả về cột 1,2,3 tức lại trả về kết quả 0-8-1, việc được vậy là nhờ MOD(COLUMN(A1)-1,3)+1, hàm này cứ mỗi lần kéo qua các cột: bắt đầu từ cột H nó ra 1, qua cột I nó ra 2, qua cột J nó ra 3, qua cột K nó trả về đầu tức ra 1, qua cột L nó ra 2, qua cột M nó ra 3, cứ trở đi trở lại.

Hỏi: chỗ (COLUMN(A1)-1)/3 này nữa

• Riêng INT((COLUMN(A1)-1)/3)+1 nó trả về tuần tự 3 cột ra chung 1 số: 3 cột đầu (H-I-J) ra số 1 (ứng với Lần 1), 3 cột kế (K-L-M) ra số 2 (ứng với Lần 2)...., và tuần tự như vậy tương ứng với các Lần: 1,2,3,4,5,6.....Nó cung cấp 'giá trị cần tìm' cho hàm: MATCH( INT((COLUMN(A1)-1)/3)+1 , INDEX(COUNTIF(OFFSET('BIÊN LAI'!$C$2,,,ROW($1:$15)),$B3),) , 0). Bạn có thể hình dung công thức khi nó thực hiện là: MATCH ( 1 , {1;1;1;1;1;1;2;2;2;2;3;3;3;3;3;4;4} ,0), nó so khớp giá trị 1 (tương ứng Lần 1) hiện có dòng bao nhiêu thì sẽ nhảy đến ô đó tính từ hoặc A1, hoặc I1, hoặc B1 (Do kết quả như lời giải thích trên trả về), ví dụ: cho mã đầu tiên NC1701 thì sheet BienLai có dữ liệu của mã này tại các dòng 1 (Lần 1), dòng 7 (Lần 2).

Hơi khó hình dung nhưng hy vọng bạn đọc chậm và trích từng đoạn công thức ra thử sẽ hiểu.

Chúc bạn ngày vui.

 

[Drghost]

Hỏi: Em chưa rõ chỗ {0,8,1} lấy ở đâu

• Đó là số tương ứng với các cột "MBL"-"SỐ TIỀN"-"NGÀY BL" của Sheet "BienLai". Các số này là kết quả trả về trong đoạn công thức: INDEX({0,8,1},,MOD(COLUMN(A1)-1,3)+1) bên sheet "TỔNG HỢP", tức cột đầu (Cột H) trả về 0, khi kéo công thức qua Cột I trả về 8, kéo qua Cột J trả về số 1. Các số này báo về cho hàm OFFSET('BIÊN LAI'!$A$1,,INDEX({0,8,1},,MOD(COLUMN(A1)-1,3)+1)) để lấy các vị trí bên sheet "BienLai" các ô A1 (ứng cột 1), ô I1 (ứng cột 2), ô B1 (ứng cột 3). Rồi tuần tự khi qua cột K (thuộc Lần 2) thì trả về cột 1,2,3 tức lại trả về kết quả 0-8-1, việc được vậy là nhờ MOD(COLUMN(A1)-1,3)+1, hàm này cứ mỗi lần kéo qua các cột: bắt đầu từ cột H nó ra 1, qua cột I nó ra 2, qua cột J nó ra 3, qua cột K nó trả về đầu tức ra 1, qua cột L nó ra 2, qua cột M nó ra 3, cứ trở đi trở lại.

Hỏi: chỗ (COLUMN(A1)-1)/3 này nữa

• Riêng INT((COLUMN(A1)-1)/3)+1 nó trả về tuần tự 3 cột ra chung 1 số: 3 cột đầu (H-I-J) ra số 1 (ứng với Lần 1), 3 cột kế (K-L-M) ra số 2 (ứng với Lần 2)...., và tuần tự như vậy tương ứng với các Lần: 1,2,3,4,5,6.....Nó cung cấp 'giá trị cần tìm' cho hàm: MATCH( INT((COLUMN(A1)-1)/3)+1 , INDEX(COUNTIF(OFFSET('BIÊN LAI'!$C$2,,,ROW($1:$15)),$B3),) , 0). Bạn có thể hình dung công thức khi nó thực hiện là: MATCH ( 1 , {1;1;1;1;1;1;2;2;2;2;3;3;3;3;3;4;4} ,0), nó so khớp giá trị 1 (tương ứng Lần 1) hiện có dòng bao nhiêu thì sẽ nhảy đến ô đó tính từ hoặc A1, hoặc I1, hoặc B1 (Do kết quả như lời giải thích trên trả về), ví dụ: cho mã đầu tiên NC1701 thì sheet BienLai có dữ liệu của mã này tại các dòng 1 (Lần 1), dòng 7 (Lần 2).

Hơi khó hình dung nhưng hy vọng bạn đọc chậm và trích từng đoạn công thức ra thử sẽ hiểu.

Chúc bạn ngày vui.

Cảm ơn anh đã phân tích giúp em.
Em đưa file mẫu của em lên nhờ anh và mọi người giúp đỡ em làm hoài không được

 

[quocgiacan]

Cảm ơn anh đã phân tích giúp em.
Em đưa file mẫu của em lên nhờ anh và mọi người giúp đỡ em làm hoài không được

Bạn xem file đính kèm.

Chúc bạn ngày vui.

 

[HieuCD]

Cảm ơn anh đã phân tích giúp em.
Em đưa file mẫu của em lên nhờ anh và mọi người giúp đỡ em làm hoài không được

Bạn xem file
Dùng Table chi cho nặng file

 

[Drghost]

Bạn xem file
Dùng Table chi cho nặng file

Em cảm ơn 2 anh Quocgiacan và HieuCD đã giúp đỡ em