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

Chia sẻ: Nguyen Quy | Ngày: | Loại File: PDF | Số trang:27

Thêm vào BST

Báo xấu

121
lượt xem 17
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

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 đây Yê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 ...

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: 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 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 đây Yê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ợp bở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àng củ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 đây
2/ Hàm TRANSPOSE(ROW(Q)) : Sẽ cho ta một mảng ngang khác bằng kích thướ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ần tử 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 1 Bạ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à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, 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é : 5
5 0 2 0 2 0 0 4 2 0 1 3 3 1 0
0 3 0 5 3 0 0 0 0 0
Đâ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 2 4) 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 ra kết quả khá tốt. Đây chính là hàm ma trận, nhân các phần tử trong mả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 1 và 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, ta chỉ 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ới Bạ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ách giải này vẫn là Best Solution Bạ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ông phả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ồm mảng như sau : 5 0 0 0 0 0 0 ---> 1*5 + 0*5 + 0* 0 + 2*0 + ….= 5 5 5 0 0 0 0 0 ----> 1*5 + 1*5 + 0*0 + 2*0 + …. = 10 5 5 0 0 0 0 0 ----> 1*5 + 1*5 + 1*0 + 2*0 + ….. = 10 5 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ột mả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 cho hà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ảng trê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ảng bắt đầu từ hàng thứ 4, do đó, tại D4 ta nhập 1
Từ D5 đến D30, ta nhập công thức trên, ta sẽ được kết quả là : 1 1 1 1 1 2 2 2 6 6 6 9 9 9
9 9 10 13 13 14 14 14 18 18 18 18 18 18 18
18 Sau khi đã xác định được đâu là vị trí của dòng "First in", ta sang cột E Tạ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),"