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ỉ...
Nội dung trích xuất từ tài liệu:
Vận dụng công thức mảng để tính FIFO Vận dụng công thức mảng để tính FIFOThấy ví dụ về cách tính FIFO của chị handung107 quá hay nên post lại cho cácbạ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ácbạ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ạnphé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ạicho 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ớilà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ướcmộ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, tuynhiê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.0A..... 5..... 1.1A..... -3..... 1.3A..... 2...... 1.3A...... -2.....1.4A..... 2.....1.2A..... 4..... 1.3A..... -3..... 1.6A..... 4..... 1.4A..... 2..... 1.4A..... -2..... 1.6A..... 1..... 1.2A..... 3..... 1.7A..... 3..... 1.2A..... 1..... 1.4A..... -5..... 1.3A..... -4..... 1.8A..... 3..... 1.8A..... -3..... 1.9A..... 5..... 1.4chúng ta có những công thức như sau:Tại ô D1: nhập số 1E1:=B1Chú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ếtsố 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),2. Khối SUMIF(OFFSET(Q,0,0,ROW()-1,1),200420133100305Tôi gọi là mảng 1Thứ 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ảngkhá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éptính sau:510101212141414182020212427282828313136Ví dụ tại ô D6, phép tính sẽ là: 1*5+1*5+1*0+1*2+1*0+1*2=14Tôi gọi là mảng 23. Tổng số của MMULT và SUMIF: như đã nói trên, SUMIF đóng vai trò 1 hằngsố. Kết hợp mảng MMULT và hàm SUMIF (tại ô D6), ta có05577999131515161922232323262631Tôi gọi là mảng 3Ví dụ: tại ô D6, phép tính sẽ là 14 (của MMULT) - 5 (hằng số từ SUMIF) = 94. Mọi việc đơn giản rồi!! Điều kiện MMULT+SUMIF>0 sẽ cho ra một mảng nh ưsauFALSETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETRUETrong 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 45. 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ĩakinh 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:D30Sau khi xác định được đâu là vị trí của dòng First In, ta sang cột EE2:=IF(D2=D1,E1+MIN(0,B1),SUMIF(OFFSET(Q,0,0,D2,1),> 0)+SUMIF(OFFSET(Q,0,0,ROW()-1,1),=IF(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 ìnhthường. Chỉ xin lưu ý cách sử dụng hàm SUMPRODUCT cho mảng thôi. Bạn lạithấy hai dấu trừ (để convert giá trị logic về 0 hoặc 1), mục đích để loại bỏ nhữngsố âm trong mảng. Như vậy cách sử dụng hàm SUMPRODUCT nhằm mục đíchtí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àothấy bối rối thì ới lên một tiếng nghe.