Giải thích giúp công thức mảng hoạt động như thế nào ?? (1 người xem)

[LOGISTICS]

các bạn có thể giải thích giúp mình công thức trong file hoạt động như thế nào được không ?
về khoản công thức mảng mình chảng hiểu gì cả . ai có thể giải thích chi tiết giúp mình với . Thanks !

 

[bebo021999]

các bạn có thể giải thích giúp mình công thức trong file hoạt động như thế nào được không ?
về khoản công thức mảng mình chảng hiểu gì cả . ai có thể giải thích chi tiết giúp mình với . Thanks !

Muốn hiểu công thức mảng, bạn phải nắm chắc các kiến thức sau:
* Mảng 1 chiều: tâp hợp các ô trên cùng 1 cột hoặc 1 dòng; mảng 2 chiều: khối ô nằm trên nhiều cột và nhiều dòng
* Cách xem phần tử của mảng: bôi đen mảng, nhấn F9. Xem xong nhấn ESC.
* Kết quả so sánh TRUE, FALSE: Khi so sánh một mảng với một giá trị, thường trả về 1 mảng với các giá trị TRUE hoặc FALSE (Tương đương giá trị 1 hoặc 0)
VD: (A1:A3) chứa giá trị {1;2;3}.
(A1:A3) = 2 => bôi đen xem bằng F9 => trả về mảng {FALSE;TRUE;FALSE} tương đương mảng {0;1;0}
(A1:A3) <= 2 => bôi đen xem bằng F9 => trả về mảng {TRUE;TRUE;FALSE} tương đương mảng {1;1;0}
* Các phép tính +-*/ giữa các mảng:
VD: (A1:A3) chứa giá trị {1;2;3}; (B1:B3) chứa giá trị {4;5;6}.
Khi +-*/ giữa 2 mảng này với nhau, excel sẽ lấy từng phần tử tương ứng tính toán với nhau, tạo 1 mảng mới:
VD: (A1:A3)*(B1:B3) = {1*4; 2*5; 3*6} = {4;10;18}
VD: ((A1:A3)<=2)*((B1:B3)>=5) = {1;1;0}*{0;1;1} = {1*0;1*1;0*1} = {0;1;0}
* Một số hàm cơ bản:
Hàm ROW(1:1)=1
ROW(1:3)={1;2;3}=>Dùng để tạo 1 mảng số nguyên liên tiếp
VD: IF(((A1:A3)<=2)*((B1:B3)>=5),ROW(1:3),"")
= IF({0;1;0},{1;2;3},"") (Gán số thứ tự cho phần tử 1 của mảng 1)
= {"";2;""} ("" : phần tữ rỗng)
Hàm SMALL
Hàm INDIRECT
Hàm INDEX
Hàm MATCH

 

[bebo021999]

Yêu cầu trong bài tập của bạn: Trích lọc danh sách MODEL (cột B) với điều kiện DATE (cột E) = 09/05/05 (ô J3) và Lot No (cột C) = 1

Công thức của bạn:
{=INDEX($B$4:$B$346,SMALL(IF(($E$4:$E$346=$J$3)*($C$4:$C$346=1),ROW($B$4:$B$346),""),ROW(1:1))-3,)}(sửa 1 chút màu đỏ)
Dấu {} không phải gõ bằng tay, mà excel tự điền bằng cách nhấn Ctrl-Shift-Enter sau khi nhập xong công thức.

Để dễ phân tích công thức, ta đặt:
($E$4:$E$346=$J$3) = ĐK1 (Mảng điều kiện 1)
($C$4:$C$346=1) = ĐK2 (Mảng điều kiện 2)

Theo hướng dẫn ở bài #2, dùng F9, sẽ thấy kết quả:

ĐK1 = {FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;...} trong đó TRUE tương ứng với các phần tử thỏa điều kiện ngày 09/05/05

ĐK2 = {FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;...} trong đó TRUE tương ứng với các phần tử thỏa điều kiện Lot No = 1

(A)=ĐK1*ĐK2 = {0;0;0;1;0;0;0;0;0;0;0;1;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;...} trong đó số 1 tương ứng với các phần tử thỏa điều kiện ngày 09/05/05 và Lot No = 1

Nhìn lại mảng Model:
(B)=$B$4:$E$346 = {"AS-689";"W9-195";"JP-195";"MA-53";"AS-689";"MA-53";"AS-689";"ZN-148";"AS-689";"MA-53";"AS-689";"FV-120";...}
Thấy tương ứng với các vị trí số 1 trong mảng (A), là các Model thỏa yêu cầu cần trích lọc.

Để trích lọc ta dùng INDEX:
Model thứ nhất:tại ô L4 = INDEX($B$4:$E$346,4) = "MA-53z"
Model thứ hai:tại ô L5 = INDEX($B$4:$E$346,12) = "FV-120"

Để truy xuất tham số 4,12,...trong hàm INDEX, ứng với L4,L5,...dùng hàm ROW
ROW($B$4:$B$346) = {4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24...}
và IF:
IF(ĐK1*ĐK2,ROW($B$4:$B$346),"") =
{"";"";"";7;"";"";"";"";"";"";"";15;"";"";"";"";"";"";22;"";"";"";"";"";"";"";"";"";"",...}
Dùng SMALL để lấy giá trị :
Nhỏ thứ nhất (tại L4)= SMALL(IF(ĐK1*ĐK2,ROW($B$4:$B$346),""),ROW(1:1))-3 = 4
Nhỏ thứ hai (tại L5) = SMALL(IF(ĐK1*ĐK2,ROW($B$4:$B$346),""),ROW(2:2))-3 = 12
Nhỏ thứ ba: ............

Trên đây là phần phân tích công thức gốc của bạn. Tuy nhiên, công thức trên chưa hợp lý do bị phụ thuộc vào vị trí tuyệt đối của bảng dữ liệu trong sheet. VD: Nếu insert thêm 1 dòng phía trên bảng thì kết quả sẽ sai. Do đó người ta thường dùng thêm hàm INDIRECT. Công thức như sau:

=INDEX($B$4:$B$346,SMALL(IF(($E$4:$E$346=$J$3)*($C$4:$C$346=1),ROW(INDIRECT("1:"&ROWS($B$4:$B$346))),""),ROW(1:1)))