Vận dụng công thức mảng để tính FIFO pot
Bạn đang xem bản rút gọn của tài liệu. Xem và tải ngay bản đầy đủ của tài liệu tại đây (110.97 KB, 13 trang )
Vận dụng công thức mảng để tính FIFO
Thấy ví dụ về cách tính FIFO của chị handung107 quá hay nên post lại cho các
bạn tham khảo về cách sử dụng mảng (array).
(P/S : Xin lỗi các bạn, vì tôi không tìm thấy File minh họa này ở đâu nữa, nên các
bạn cố gắng theo dõi bài viết này - Handung107)
Đây là 1 ví dụ xuất sắc về cách sử dụng mảng một cách linh hoạt. Tôi xin mạn
phép tác giả post lại đoạn mô tả cách tính FIFO (không phải của tôi, chỉ dịch lại
cho các bạn tham khảo thôi. Tôi chỉ thêm phần giải thích cách tính cho thêm khí
thế).
Tôi biết phần lớn các bạn ở đây đều rất rành về mảng, nhưng sợ một vài bạn mới
làm quen với excel chưa biết hết. Vì vậy tôi xin được phép đi cụ thể từng bước
một.
Giả sử ta có bảng sau: cột 1 là tên Sản phẩm (A), cột 2 là số lượng hàng mua/bán
(số âm là bán, số dương là mua). Tạm đặt thên cột này là Q (tương ứng với khối
$B$1:$B$30). Cột 3 là giá mua/bán, đặt tên là P (tương ứng với khối
$C$1:$C$30).
(Tôi không được phép post file nên không có file cho các bạn tham khảo, tuy
nhiên các bạn có thể copy và paste vào trong excel).
Dòng đầu tiên bao giờ cũng là số dương (số mua mới hoặc số đầu kỳ).
A 5 1.0
A 5 1.1
A -3 1.3
A 2 1.3
A -2 1.4
A 2 1.2
A 4 1.3
A -3 1.6
A 4 1.4
A 2 1.4
A -2 1.6
A 1 1.2
A 3 1.7
A 3 1.2
A 1 1.4
A -5 1.3
A -4 1.8
A 3 1.8
A -3 1.9
A 5 1.4
chúng ta có những công thức như sau:
Tại ô D1: nhập số 1
E1:
=B1
Chúng ta làm 2 cột trung gian: cột E: Số lượng hàng tồn kho của đợt hàng lâu nhất
(cái thằng First in ấy).
Cột D: vị trí của số hàng tồn kho lâu nhất, ví dụ: tại dòng số 6, do đã tiêu thụ hết
số hàng nhập về lần đầu tiên (5 cái), nên số hàng tồn kho lâu nhất sẽ là dòng số 2.
Để tính các cột D, E, ta nhập các công thức sau:
Tôi giả sử đang ở dòng số 6, ta nhập:
Cột D (trừ ô D1 đã nhập số 1): nhớ đây là công thức mảng nhé. Bạn nhớ nhấn tổ
hợp Ctrl+Shift+Enter.
{=MATCH(TRUE,MMULT( (ROW(Q)>=TRANSPOSE(ROW(Q))),(Q>0)*Q)
+SUMIF(OFFSET( Q,0,0,ROW()-1,1),"<0")>0,0)}
Các bạn chưa quen với công thức mảng chú ý một chút, hơi tốn công nhưng bù lại
có nhiều lợi ích về sau
Tôi tách cái công thức rối mù ở trên ra làm nhiều mảnh để dễ theo dõi.
1. Khối hàm Offset: trong công thức trên, hàm offset đóng vai trò 1 trích một khối
con trong khối Q. Ví dụ nếu lúc này bạn đang ở ô D6, thì khối được trích sẽ là
B1:B5
2. Khối SUMIF(OFFSET(Q,0,0,ROW()-1,1),"<0"): Hàm SumIf sẽ lấy tổng của
các số âm trong khối B1:B5 (tức là tổng số hàng xuất ra). Trong công thức, khối
SUMIF đóng vai trò 1 hằng số (sẽ thấy rõ hơn vài trò này ở phần sau).
3. Khối MMULT( (ROW(Q)>=TRANSPOSE(ROW(Q))),(Q>0)*Q): Cái này hay
nhất đây. Tác giả sử dụng rất nhiều "mánh lới" trong công thức trên
Đầu tiên: (ROW(Q)>=TRANSPOSE(ROW(Q)): Đây là mánh để tạo ra 1 mảng có
số dòng bằng với số cột, trong đó giá trị toàn TRUE
Thứ hai: Hai dấu trừ liên tiếp nhau ( ). cũng là 1 "mánh" thông dụng để chuyển
giá trị logic (true/false) thành số (True=1, false=0). Đến đây bạn có mảng toàn số
1.
Thứ ba: (Q>0)*Q: giống "cái gì đây" quá nhỉ. Cái này trích ra mảng toàn số
dương, các số âm bị convert thành số 0. Cái mảng này là:
5
5
0
2
0
2
0
0
4
2
0
1
3
3
1
0
0
3
0
5
Tôi gọi là mảng 1
Thứ tư: Hàm MMULT: hàm này ít ai xài, nhưng nếu kết hợp với các hàm về mảng
khác lại cho kết quả rất tốt. Đây là hàm tính tổng của hàng nhân với cột.
Ráp vào MMULT( (ROW(Q)>=TRANSPOSE(ROW(Q))),(Q>0)*Q), ta có phép
tính sau:
5
10
10
12
12
14
14
14
18
20
20
21
24
27
28
28
28
31
31
36
Ví dụ tại ô D6, phép tính sẽ là: 1*5+1*5+1*0+1*2+1*0+1*2=14
Tôi gọi là mảng 2
3. Tổng số của MMULT và SUMIF: như đã nói trên, SUMIF đóng vai trò 1 hằng
số. Kết hợp mảng MMULT và hàm SUMIF (tại ô D6), ta có
0
5
5
7
7
9
9
9
13
15
15
16
19
22
23
23
23
26
26
31
Tôi gọi là mảng 3
Ví dụ: tại ô D6, phép tính sẽ là 14 (của MMULT) - 5 (hằng số từ SUMIF) = 9
4. Mọi việc đơn giản rồi!! Điều kiện MMULT+SUMIF>0 sẽ cho ra một mảng như
sau
FALSE
TRUE
TRUE
TRUE
TRUE
TRUE
TRUE
TRUE
TRUE
TRUE
TRUE
TRUE
TRUE
TRUE
TRUE
TRUE
TRUE
TRUE
TRUE
TRUE
Trong mảng trên, False đại diện cho các dòng đã xuất hết kho, "True" nghĩa là
hàng hóa hãy còn tồn kho.
Tôi gọi là mảng 4
5. Cuối cùng ta dùng hàm match (các bạn chắc rành hàm này lắm rồi, nói kỹ quá
người ta cười rụng răng): dùng để xác định vị trí "TRUE" đầu tiên trong mảng 4.
Ở đây, ta thấy vị trí của TRUE trong mảng 4 là vị trí số 2 (dòng số 2). Về ý nghĩa
kinh tế, đây là bước dò tìm trong khối Q xem đâu là dòng "First In" đầu tiên.
Bạn copy công thức mảng trên vào toàn bộ khối từ D2:D30
Sau khi xác định được đâu là vị trí của dòng "First In", ta sang cột E
E2:
=IF(D2=D1,E1+MIN(0,B1),SUMIF(OFFSET(Q,0,0,D2,1),"> 0")
+SUMIF(OFFSET(Q,0,0,ROW()-1,1),"<0"))
Cái này chắc rõ rồi. Chỉ xin diễn nôm một chút: Nếu số lượng hàng cũ còn đủ xài,
thì số lượng hàng (cũ) tồn kho bằng với E1 trừ đi số lượng hàng xuất ra (Xin lưu ý
hàm min(0,B1) chính là dấu trừ); còn nếu số lượng hàng cũ không đủ xài, thì lấy
hàng mới mà xuất, số tồn kho (đợt hàng First In mới) sẽ là tổng số nhập trừ tổng
số xuất cho đến thời điểm tính toán. (nghe ghê quá, chắc chẳng ai hiểu được )
Cột F được dành để tính FIFO. Công thức FIFO như sau:
F2:
=IF(B2<0,IF(E2+B2>=0,-B2*INDEX(P,D2),E2*INDEX(P,D2)+(INDEX(Q,D3)-
E3)
*INDEX(P,D3)+IF(D3-D2>1,SUMPRODUCT( (OFFSET(Q,D2,0,D3-D2-
1,1)>0)
,OFFSET(Q,D2,0,D3-D2-1,1),OFFSET(P,D2,0,D3-D2-1,1)),0)),"")
Ở đây tôi không muốn mất thời gian các bạn để giải thích những công thức bình
thường. Chỉ xin lưu ý cách sử dụng hàm SUMPRODUCT cho mảng thôi. Bạn lại
thấy hai dấu trừ (để convert giá trị logic về 0 hoặc 1), mục đích để loại bỏ những
số âm trong mảng. Như vậy cách sử dụng hàm SUMPRODUCT nhằm mục đích
tính tổng giá của lô hàng mua GIỮA hai giá trị D2 và D3.
Giải thích nhiều rườm tai các bạn, nên tôi xin không đi sâu vào chi tiết. Bạn nào
thấy "bối rối" thì ới lên một tiếng nghe.
