Giải thích thêm về cách vận dụng các công thức trong File FIFO

Giả sử ta có bảng dữ liệu sau :Đầu tiên, ta đặt tên cho mảng $B$4:$B$30 là Q, và mảng $C$4:$C$30 là P, cách đặt tên như thế nào hoặc các bạn có thể đặt tên cho vùng dữ liệu động, chúng ta sẽ không bàn tại đâyYêu cầu cấu trúc của File này là : - Số lượng Nhấp và Xuất đặt cùng 1 cột và quy ước SLNhap 0, SLXuat 0, tương ứng với số lượng tồn cuối kỳ, hoặc nhập hàng vào rồi mới xuất bán ...
Nội dung trích xuất từ tài liệu:
Giải thích thêm về cách vận dụng các công thức trong File FIFO Giải thích thêm về cách vận dụng các công thức trong File FIFOGiả sử ta có bảng dữ liệu sau :Đầu tiên, ta đặt tên cho mảng $B$4:$B$30 là Q, và mảng $C$4:$C$30 là P, cáchđặt tên như thế nào hoặc các bạn có thể đặt tên cho vùng dữ liệu động, chúng ta sẽkhông bàn tại đâyYêu cầu cấu trúc của File này là :- Số lượng Nhấp và Xuất đặt cùng 1 cột và quy ước SLNhap >0, SLXuat 0,tương ứng với số lượng tồn cuối kỳ, hoặc nhập hàng vào rồi mới xuất bánĐể có thể hiểu được bài này, chúng ta cần nói về một số công thức mảng kết hợpbởi các hàm sau :1/ Hàm ROW(Q) : sẽ trả về giá trị là một mảng gồm các số hạng là vị trí các hàngcủa các phần tử của mảng Q, tính từ phần tử đầu tiên. Công thức mảng ROW(Q)sẽ cho như hình dưới đây2/ Hàm TRANSPOSE(ROW(Q)) : Sẽ cho ta một mảng ngang khác bằng kíchthước với mảng ROW(Q) như hình vẽ sau :3/ Lệnh ROW(SL)>=TRANSPOSE(ROW(SL)) sẽ xuất ra mảng gồm những phầntử trong mảng có giá trị là TRUE :Ta chuyển giá trị TRUE, False sang giá trị 1, 0 bằng cách thêm vào 2 dấu - - nhưsau :{=(--(ROW(SL)>=TRANSPOSE(ROW(SL))}, ta sẽ có một mảng gồm giá trị :Ta gọi mảng trên đây là mảng 1Bạn hỏi vì sao 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 là tìm thời điểm (dòng số mấy) của cái gọi là “First in”. Muốn làmnhư 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, sau đó, trừđi xuất kho, ta sẽ tìm được thằng “First in” mới.Bạn hãy xem mảng (Q>0)*Q sẽ cho ra các phần tử nào nhé :55020200420133100305300000Đây chính là mảng số lượng B4:B30 của bảng dữ liệu, chỉ có điều số lượng xuất <0, sẽ được Convert thành số 0, chỉ còn các phần tử là số lượng nhập trong kỳ màthôi. Ta gọi mảng này là mảng 24) 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 ra kết quả khá tốt. Đây chính là hàm ma trận, nhân các phần tử trongmảng này với các phần tử trong mảng kia. Kết hợp hàm MMULT để nhân mảng 1và mảng 2 ta có kết quả như sau :Đây chính là mảng chúng ta cần tìm. Mảng này thể hiện tổng số hàng nhập kho, tachỉ cần tìm mảng thể hiện số xuất kho, lấy mảng này trừ đi, ta sẽ tìm được vị tríFirst In mớiBạn sẽ bật cười vì để tạo mảng này, ta có thể chỉ cần hàm SUMIF(B$4:B4,”>0”)rồi kéo xuống, hơi đâu mà phài làm công thức dài loằng ngoằng khó hiểu như vậy? Nhưng thực tế, nếu dung hàm SUMIF, bạn sẽ phải thêm các cột phụ, do đó, cáchgiải này vẫn là Best SolutionBạn thử xem hình sau :Thoạt nhìn, bạn sẽ thấy 2 công thức :- =MMULT(--(ROW(Q)>=TRANSPOSE(ROW(Q))),(Q>0)*Q)- =SUMIF($B$4:B4,”>0”) hay- =SUMIF(OFFSET(Q,0,0,ROW()-4,1),>0)Là cho kết quả tương đương nhau khi bạn nhập chúng vào một cột như hình dưới.Nhưng thực tế, công thức 1 là công thức mảng, trong khi 2 công thức dưới khôngphải công thức mảng.Kết quả trên của hàm trận {= MMULT(-- ma(ROW(Q)>=TRANSPOSE(ROW(Q))),(Q>0)*Q)} thực ra được diễn giải gồmmảng như sau :5 0 0 0 0 0 0 ---> 1*5 + 0*5 + 0* 0 + 2*0 + ….= 55 5 0 0 0 0 0 ----> 1*5 + 1*5 + 0*0 + 2*0 + …. = 105 5 0 0 0 0 0 ----> 1*5 + 1*5 + 1*0 + 2*0 + ….. = 105 5 0 2 0 0 0 ----> 1*5 + 1*5 + 1*0 + 1*2 + 0*0 + …. = 12………Chúng ta sẽ xác định tổng số lượng xuất kho bằng công thức sau := SUMIF(OFFSET(Q,0,0,ROW()-4,1),”0)*Q)+SUMIF(OFFSET(Q,0,0,ROW()-4,1),”Bạn sẽ thấy mảng này thể hiện số lượng hàng tồn sau mỗi lần nhập và xuất kho.Mảng đầy đủ sẽ làMọi việc bắt đầu đơn giản rồi. Điều kiện MMULT + SUMIF >0 sẽ cho ra mộtmảng sau :Mảng đầy đủ phải được diễn giải như sau :Trong mảng trên, TRUE sẽ đại diện cho hàng còn tồn kho, và False đại diện chohàng đã xuất hết kho.5/ Cuối cùng, ta dung hàm MATCH để xác định vị trí “TRUE” đầu tiên của mảngtrên. Ý nghĩa của nó là dò tìm trong khối Q, đâu là dòng “FIRST In” đầu tiên.Công thức này là :{= MATCH(TRUE,(--(ROW(Q)>=TRANSPOSE(ROW(Q))),(Q>0)*Q)+SUMIF(OFFSET(Q,0,0,ROW()-4,1),”0,0)}Trở về bảng dữ liệu, ta sẽ dung 2 cột phụ là cột D và cột E. Cell đầu tiên của bảngbắt đầu từ hàng thứ 4, do đó, tại D4 ta nhập 1Từ D5 đến D30, ta nhập công thức trên, ta sẽ được kết quả là :11111222666999991013131414141818181818181818Sau khi đã xác định được đâu là vị trí của dòng First in, ta sang cột ETại E4 = B4, số lượng tồn đầu kỳE5 = IF(D5=D4,E4+MIN(0,B4),SUMIF(OFFSET(Q,0,0,D5,1),>0 )+SUMIF(OFFSET(Q,0,0,ROW()-4,1), ...