Ví dụ FIFO-Vận dụng công thức mảng

  • Thread starter workman
  • Ngày gửi
W

workman

Guest
22/7/05
372
2
0
51
Ho Chi Minh
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).

Đâ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.
 
Khóa học Quản trị dòng tiền
H

handung107

Cao cấp
28/8/04
576
15
0
VN
www.giaiphapexcel.com
Đây là File của ví dụ trên. File này và File FIFO1 la 2 File xuất sắc nhất, theo tôi về xây dựng những ý tuởng về các cột phụ và công thức mảng. Cám ơn bài giải của Workman nhiều
 
W

workman

Guest
22/7/05
372
2
0
51
Ho Chi Minh
Nhờ chị handung107 sửa hộ mấy số âm (hàng xuất) với. Do tôi lấy ví dụ là ô D6 nên có mấy số âm ở giữa sẽ giúp các bạn theo dõi dễ hơn.
 
dragon76

dragon76

Cao cấp
12/3/04
257
2
18
Để hiểu cách tính và công dụng của các công thức trong ví dụ của Handung107 do workman trình bày mình nghĩ handung107 nên nói thêm về cách tính cụ thể hơn , chẳng hạn như tại sao lại sử dụng (ROW(Q)>=TRANSPOSE(ROW(Q)) để tạo ra 1 mảng có số dòng bằng với số cột, trong đó giá trị toàn TRUE ?? Để người đọc có thể hình dung được các công thức được lập ra để tạo ra những kết quả như thế nào. Handung107 vui lòng giúp nhé!
 
W

workman

Guest
22/7/05
372
2
0
51
Ho Chi Minh
Cám ơn bạn dragon76 có nhận xét. Tôi xem lại mới giât mình. Hóa ra giải thích của mình không chính xác. Thực ra lệnh ROW(Q)>=TRANSPOSE(ROW(Q)) xuất ra mảng như thế này.

1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1 1 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0
1 1 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0
1 1 1 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0
1 1 1 1 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0 0
1 1 1 1 1 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0
1 1 1 1 1 1 1 1 1 1 1 1 0 0 0 0 0 0 0 0
1 1 1 1 1 1 1 1 1 1 1 1 1 0 0 0 0 0 0 0
1 1 1 1 1 1 1 1 1 1 1 1 1 1 0 0 0 0 0 0
1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 0 0 0 0 0
1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 0 0 0 0
1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 0 0 0
1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 0 0
1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 0
1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1

Bạn hỏi mục đích tại sao lại phải tạo ra mảng này? Thực ra phải đi từ mục đích cuối cùng ra. Mục đích chính của bài toán là tìm thời điểm (dòng số mấy) của cái gọi là "First In" stock. Muốn làm như thế bắt buộc phải tạo ra một mảng thể hiện tổng số hàng nhập kho (giống như sổ kho vậy đó), sau đó trừ số xuất kho sẽ tìm được thằng "First In" mới. Mảng cần tìm chính là mảng số 2.

Có bạn sẽ bật cười vì để tạo mảng số 2 ta có thể làm công thức sum(B$1:B2) rồi kéo xuống, tại sao lại phải làm 1 công thức loằng ngoằng cho đời thêm phức tạp. Nhưng thực tế nếu dùng hàm SUM ta phải làm thêm một cột phụ. Do đó, cách làm của tác giả tuy khó hiểu 1 chút nhưng vấn là best solution.
 
H

handung107

Cao cấp
28/8/04
576
15
0
VN
www.giaiphapexcel.com
Bài giải của Workman quá hay, tôi thấy không có gì để bổ sung nữa, chỉ đề nghị Workman phát triển tiếp File này thành một File hoàn chỉnh, tức có dòng tiêu đề cột, thay vì bắt đầu từ Cell A1, thì có thể bắt đầu bằng bất cứ vị trí nào, và kèm thêm Sheet nhập xuất hàng hoá, Sheet HH tồn đầu kỳ, và rút dữ liệu vào Sheet FIFO để tính toán giá vốn cho từng mặt hàng. Cám ơn Workman nhé
 
W

workman

Guest
22/7/05
372
2
0
51
Ho Chi Minh
handung107 nói:
Bài giải của Workman quá hay, tôi thấy không có gì để bổ sung nữa, chỉ đề nghị Workman phát triển tiếp File này thành một File hoàn chỉnh, tức có dòng tiêu đề cột, thay vì bắt đầu từ Cell A1, thì có thể bắt đầu bằng bất cứ vị trí nào, và kèm thêm Sheet nhập xuất hàng hoá, Sheet HH tồn đầu kỳ, và rút dữ liệu vào Sheet FIFO để tính toán giá vốn cho từng mặt hàng. Cám ơn Workman nhé
Chi handung tha cho tôi với. Việc này e quá sức tôi. Thú thực trước nay tôi không làm sổ kế toán bao giờ cả, cũng không lập form trên excel bao giờ.

Chắc hôm nào phải nhờ chị chỉ bảo về cách lập form trên Excel quá.
 
L

Lieuquocdat

Guest
28/9/05
17
0
0
49
Soc Trang Province
MÌnh không hiểu, mục đích của Mình là muốn xác định số lượng hàng tồn có cả số lượng và đơn giá theo FIFO
 
P

ptdzung

Guest
26/3/05
56
0
0
Hà Nội
Chủ đề này đã quá lâu, nay tôi mới tìm được. Nếu tôi cho dòng cuối cùng là xuất hết thì FIFO lại ra kết quả <0. Tôi thử với vùng dữ kiện của P và Q là 50 chẳng hạn nhưng dữ kiện nhập vào mới chỉ có đến 30 dòng thì kết quả cũng không được (Chỉ sai dòng cuối cùng thôi). Các bạn chỉ hộ cách khắc phục. Xin cám ơn
 
K

ketoantructuyen

Guest
9/7/05
8
0
0
43
Qui Nhon City
Chào chị handung107
Đọc bài chị thấy cách tính FIFO rất hay. Nhưng mình có thể viết 1 hàm nào đó để tính FIFO cho mỗi lần xuất. Và có cách nào viết macro để thể hiện trị giá xuất của lô nhập nào. Giả sử mình có 01 bảng chứa dữ liệu nhật ký nhập-xuất liên tục trình tự theo thời gian phát sinh. Giống như sổ chi tiết HH làm bằng thủ công.
Mình đã lục rất nhiều trang web kể cả ở nước ngoài nhưng chưa thấy ai làm được.
Vì làm như vậy thì theo dõi được tình hình nhập-xuất thường xuyên. Còn để tính tổng trị giá xuất theo FIFO thì dễ. Chỉ cần tính trị giá tồn cuối theo FIFO là tínnh ngược lại được TG xuất FIFO.

Mong nhận được cách thực hiện sớm nhất. Hậu tạ sau.

ketoantructuyen@yahoo.com
 

Xem nhiều

Webketoan Zalo OA