Nhờ rút gọn công thức tính chi phí tài chính bán hàng (1 người xem)

[Salemok]

Mình đã làm xong file này
Nhưng công thức dài quá khoảng 20 dòng
Nhờ các cao thủ rút gọn giúp
Nguyên tắc:
- CPTC= CPTC nhập hàng - CPTC bán hàng
CPTC nhập hàng=(Ngày kết thúc-ngày nhập L1)*Tiền mua lần 1*lãi suất năm/360+(Ngày kết thúc-ngày nhập L2)*Tiền mua lần 2*lãi suất năm/360

CPTC bán hàng=(Ngày kết thúc-ngày thu L1)*Tiền thu lần 1*lãi suất năm/360+(Ngày kết thúc-ngày thu L2)*Tiền thu lần 2*lãi suất năm/360
Nếu chưa kết thúc thì =0
Nếu âm thì =0
mình xin gửi kèm file

 

[bebo021999]

Tưởng khủng long tuyệt chủng rôi chứ:
Yên tâm đi, chịu khó chờ, thế nào mà chẳng có cách giải quyết.

=IF(N("//Chi phí TC+")+IF(U5<>"",IF(W$4=1,IF($H5>=$X$1,IF(AND(W$4<=MONTH($U5),W$4>=MONTH($H5)),IF(W$4=MONTH($H5),W$3-DAY($H5),IF(W$4=MONTH($U5),DAY($U5),W$3)),0),$X$1-$H5),IF(AND(W$4<=MONTH($U5),W$4>=MONTH($H5)),IF(W$4=MONTH($H5),W$3-DAY($H5),IF(W$4=MONTH($U5),DAY($U5),W$3)),0))*$K5*$Z$1/360+IF(W$4=1,IF($I5>=$X$1,IF(AND(W$4<=MONTH($U5),W$4>=MONTH($I5)),IF(W$4=MONTH($I5),W$3-DAY($I5),IF(W$4=MONTH($U5),DAY($U5),W$3)),0),$X$1-$I5),IF(AND(W$4<=MONTH($U5),W$4>=MONTH($I5)),IF(W$4=MONTH($I5),W$3-DAY($I5),IF(W$4=MONTH($U5),DAY($U5),W$3)),0))*$L5*$Z$1/360,0)+N("//Chi phí TC-")-N("//Chi phí TC-")-IF(U5<>"",IF(W$4=1,IF($O5>=$X$1,IF(AND(W$4<=MONTH($U5),W$4>=MONTH($O5)),IF(W$4=MONTH($O5),W$3-DAY($O5),IF(W$4=MONTH($U5),DAY($U5),W$3)),0),$X$1-$O5),IF(AND(W$4<=MONTH($U5),W$4>=MONTH($O5)),IF(W$4=MONTH($O5),W$3-DAY($O5),IF(W$4=MONTH($U5),DAY($U5),W$3)),0))*$R5*$Z$1/360+IF(W$4=1,IF($P5>=$X$1,IF(AND(W$4<=MONTH($U5),W$4>=MONTH($P5)),IF(W$4=MONTH($P5),W$3-DAY($P5),IF(W$4=MONTH($U5),DAY($U5),W$3)),0),$X$1-$P5),IF(AND(W$4<=MONTH($U5),W$4>=MONTH($P5)),IF(W$4=MONTH($P5),W$3-DAY($P5),IF(W$4=MONTH($U5),DAY($U5),W$3)),0))*$S5*$Z$1/360,0)>0,N("//Chi phí TC+")+IF(U5<>"",IF(W$4=1,IF($H5>=$X$1,IF(AND(W$4<=MONTH($U5),W$4>=MONTH($H5)),IF(W$4=MONTH($H5),W$3-DAY($H5),IF(W$4=MONTH($U5),DAY($U5),W$3)),0),$X$1-$H5),IF(AND(W$4<=MONTH($U5),W$4>=MONTH($H5)),IF(W$4=MONTH($H5),W$3-DAY($H5),IF(W$4=MONTH($U5),DAY($U5),W$3)),0))*$K5*$Z$1/360+IF(W$4=1,IF($I5>=$X$1,IF(AND(W$4<=MONTH($U5),W$4>=MONTH($I5)),IF(W$4=MONTH($I5),W$3-DAY($I5),IF(W$4=MONTH($U5),DAY($U5),W$3)),0),$X$1-$I5),IF(AND(W$4<=MONTH($U5),W$4>=MONTH($I5)),IF(W$4=MONTH($I5),W$3-DAY($I5),IF(W$4=MONTH($U5),DAY($U5),W$3)),0))*$L5*$Z$1/360,0)+N("//Chi phí TC-")-N("//Chi phí TC-")-IF(U5<>"",IF(W$4=1,IF($O5>=$X$1,IF(AND(W$4<=MONTH($U5),W$4>=MONTH($O5)),IF(W$4=MONTH($O5),W$3-DAY($O5),IF(W$4=MONTH($U5),DAY($U5),W$3)),0),$X$1-$O5),IF(AND(W$4<=MONTH($U5),W$4>=MONTH($O5)),IF(W$4=MONTH($O5),W$3-DAY($O5),IF(W$4=MONTH($U5),DAY($U5),W$3)),0))*$R5*$Z$1/360+IF(W$4=1,IF($P5>=$X$1,IF(AND(W$4<=MONTH($U5),W$4>=MONTH($P5)),IF(W$4=MONTH($P5),W$3-DAY($P5),IF(W$4=MONTH($U5),DAY($U5),W$3)),0),$X$1-$P5),IF(AND(W$4<=MONTH($U5),W$4>=MONTH($P5)),IF(W$4=MONTH($P5),W$3-DAY($P5),IF(W$4=MONTH($U5),DAY($U5),W$3)),0))*$S5*$Z$1/360,0),0)

 

[bebo021999]

Thấy trong Ct còn nhiều điều kiện liên quan đến từng tháng trong năm 2016, tương ứng với từng cột trong vùng W:AH?

 

[Salemok]

Trong đó mốc ngày đầu năm 01/01/2016 và các ngày cuối tháng 31-29-31-30.... nếu có thể thì các bạn đưa luôn vào công thức giúp.
Nhiều điều kiện liên quan từng thắng chủ yếu để xử lý việc vắt từ năm trước sang
Mình hoa cả mắt vì công thức

 

[quocgiacan]

Tưởng khủng long tuyệt chủng rôi chứ:
Yên tâm đi, chịu khó chờ, thế nào mà chẳng có cách giải quyết.

Nỗi khốn đốn khi gặp "khủng long bạo chúa"! khà khà khà.

Trong đó mốc ngày đầu năm 01/01/2016 và các ngày cuối tháng 31-29-31-30.... nếu có thể thì các bạn đưa luôn vào công thức giúp.
Nhiều điều kiện liên quan từng thắng chủ yếu để xử lý việc vắt từ năm trước sang
Mình hoa cả mắt vì công thức

Nhìn công thức của bạn thì làm anh em động lòng muốn giúp bạn, nhưng rõ khổ đi mò từng điều kiện của bạn thiết lập..... thiệt đúng là "trần ai khoai củ".
Để có nguồn động lực bạn thấy anh em có thể giúp bạn, tôi đã làm thử và đưa 1 công thức ngắn gọn cho ô W15 (và W16) như sau:
W15=SUMPRODUCT(($X$1-H15:I15)*K15:L15*$Z$1/360)
Đây là công thức dựa vào những gì bạn ghi tại bài #1 trên.

Tuy nhiên nó không bao quát hết mọi trường hợp của bạn. Vì vậy, để hoàn thành yêu cầu của bạn, bạn chịu khó làm 1 file Excel ghi "bằng chữ" (không ghi công thức , nhưng nếu chỉ ra ô kèm ý nghĩa ô đó: vd: ô chứa ngày bắt đầu, ô chứa giá trị mua lần 1.... thì được) các trường hợp có thể xảy ra. Ý là, thay vì bạn đưa công thức để anh em dò dẫm tìm hiểu, thì bạn ghi gạch đầu dòng:
- trường hợp 1: là ..... thì xử:....;
- trường hợp 2: là ..... thì xử:....;
- trường hợp n: là ..... thì xử:....;

Có làm vậy thì hy vọng anh em GPE dễ bám theo mà hiệu chỉnh dùm bạn công thức "đặc biệt" trên.

Chúc bạn ngày vui.

 

[Salemok]

Mình vừa up lại file mới.
Do file cũ mình quên Ko ghim $U5 nên khi kéo sang các ô khác bị chạy.

 

[bebo021999]

Trong đó mốc ngày đầu năm 01/01/2016 và các ngày cuối tháng 31-29-31-30.... nếu có thể thì các bạn đưa luôn vào công thức giúp.
Nhiều điều kiện liên quan từng thắng chủ yếu để xử lý việc vắt từ năm trước sang
Mình hoa cả mắt vì công thức

quocgiacan tiềm năng nhất trong vấn đề ngày cuối tháng, kiên nhẫn chờ đợi để "hóng" anh đây

 

[bebo021999]

Dài như vậy nhưng ít ra trước mắt cũng lôi được thằng lãi suất/360 ra ngoài làm thừa số chung.

 

[quocgiacan]

Mình vừa up lại file mới.
Do file cũ mình quên Ko ghim $U5 nên khi kéo sang các ô khác bị chạy.

Bạn làm theo gợi ý bài #5 đi.

 

[Salemok]

Sợ quá , Hoa mắt chóng mặt rồi. Ko dịch đc nữa

 

[quocgiacan]

Sợ quá , Hoa mắt chóng mặt rồi. Ko dịch đc nữa

Cứ từ từ đi bạn không gấp lắm đâu.

Bạn còn chịu không thấu thì anh em làm sao hiểu nỗi bạn muốn xử trường hợp như thế nào.

Hay bạn dịch đến đâu thì đưa đến đó, để anh em nắm ý sơ sơ rồi sẽ hỏi tiếp bạn.

 

[chisinhvnn]

cho mình hỏi, nếu công thức dài quá thì dặt name cho nó gọn có được không nhỉ

 

[eke_rula]

Mình đã làm xong file này
Nhưng công thức dài quá khoảng 20 dòng
Nhờ các cao thủ rút gọn giúp
Nguyên tắc:
- CPTC= CPTC nhập hàng - CPTC bán hàng
CPTC nhập hàng=(Ngày kết thúc-ngày nhập L1)*Tiền mua lần 1*lãi suất năm/360+(Ngày kết thúc-ngày nhập L2)*Tiền mua lần 2*lãi suất năm/360

CPTC bán hàng=(Ngày kết thúc-ngày thu L1)*Tiền thu lần 1*lãi suất năm/360+(Ngày kết thúc-ngày thu L2)*Tiền thu lần 2*lãi suất năm/360
Nếu chưa kết thúc thì =0
Nếu âm thì =0
mình xin gửi kèm file

Nhìn theo công thức bạn không hiểu gì, nhưng xem file bạn mình có thể hiểu được cách tính , mình làm theo nguyên tắc là khi thu được tiền bán hàng sẽ trả tiền mua hàng luôn, bạn dùng CT này ở W5:

W5=MAX(IFERROR(SUMPRODUCT((LOOKUP(ROW(INDIRECT($H5&":"&$P5)),SMALL(N(OFFSET($A5,,{7;8;14;15})),ROW($1:$4)),MMULT(N(ROW($1:$4)>=COLUMN($A:$D)),LOOKUP(N(OFFSET($A5,,{7;8;14;15})),SMALL(N(OFFSET($A5,,{7;8;14;15})),ROW($1:$4)),{1;1;-1;-1}*N(OFFSET($A5,,{10;11;17;18})))))*$Z$1/360)*(MONTH(ROW(INDIRECT($H5&":"&$P5)))=W$4)*(YEAR(ROW(INDIRECT($H5&":"&$P5)))=YEAR($X$1))),0),)

Fill xuống, fill sang phải!!!
Mình có làm một bảng phụ mô tả cách tính của công thức trên ở AJ3:AM83 lấy dữ liệu của dòng 7 (ngày bắt đầu mua hàng là 01/01/2016- và kết thúc 18/03/2016) , bạn xem thử xem hiểu không!!!

 

[Salemok]

Phương thức tính như thế này:

Nguyên tắc:


1- CPTC= CPTC nhập hàng - CPTC bán hàng
2- CPTC nhập hàng=(Ngày kết thúc-ngày nhập L1)*Tiền mua lần 1*lãi suất năm/360+(Ngày kết thúc-ngày nhập L2)*Tiền mua lần 2*lãi suất năm/360

3- CPTC bán hàng=(Ngày kết thúc-ngày thu L1)*Tiền thu lần 1*lãi suất năm/360+(Ngày kết thúc-ngày thu L2)*Tiền thu lần 2*lãi suất năm/360
4- Nếu chưa kết thúc tức ngày kết thúc trống chưa xác định vì vẫn đang thực hiện thì tính đủ cả tháng

5- Nếu ngày nhập hàng của năm trước thì tính là ngày 01/01 của năm (file cũ mình tính cả về trước nữa như vậy thi năm trước đã tính rồi năm sau sẽ bị trùng nên sẽ chỉ tính tiếp từ 01/01 thôi. Vì vậy lưu ý khi tính trong tháng 1.)

6- Năm tính là 2016 (nhưng tính trước để lập file này dùng cho các năm 2017, 2018… sau này



Hoặc các bạn lập công thức mới giúp nhé

 

[Salemok]

Nhìn theo công thức bạn không hiểu gì, nhưng xem file bạn mình có thể hiểu được cách tính , mình làm theo nguyên tắc là khi thu được tiền bán hàng sẽ trả tiền mua hàng luôn, bạn dùng CT này ở W5:

W5=MAX(IFERROR(SUMPRODUCT((LOOKUP(ROW(INDIRECT($H5&":"&$P5)),SMALL(N(OFFSET($A5,,{7;8;14;15})),ROW($1:$4)),MMULT(N(ROW($1:$4)>=COLUMN($A:$D)),LOOKUP(N(OFFSET($A5,,{7;8;14;15})),SMALL(N(OFFSET($A5,,{7;8;14;15})),ROW($1:$4)),{1;1;-1;-1}*N(OFFSET($A5,,{10;11;17;18})))))*$Z$1/360)*(MONTH(ROW(INDIRECT($H5&":"&$P5)))=W$4)*(YEAR(ROW(INDIRECT($H5&":"&$P5)))=YEAR($X$1))),0),)

Fill xuống, fill sang phải!!!
Mình có làm một bảng phụ mô tả cách tính của công thức trên ở AJ3:AM83 lấy dữ liệu của dòng 7 (ngày bắt đầu mua hàng là 01/01/2016- và kết thúc 18/03/2016) , bạn xem thử xem hiểu không!!!

khi thu được tiền bán hàng sẽ trả tiền mua hàng luôn

Không hẳn vậy bạn ơi

 

[eke_rula]

khi thu được tiền bán hàng sẽ trả tiền mua hàng luôn

Không hẳn vậy bạn ơi

Bạn không để mốc trả tiền khi nào sao mà tính lãi được, hay là để đến ngày kết thúc rồi mới trả tiền, vậy thì đơn giản hơn!!!

 

[Salemok]

2- CPTC nhập hàng=(Ngày kết thúc-ngày nhập L1)*Tiền mua lần 1*lãi suất năm/360+(Ngày kết thúc-ngày nhập L2)*Tiền mua lần 2*lãi suất năm/360

Ngày trả tiền mua lần 1----------------------------------> ngày kết thúc Cột U(=ngày thu tiền lần cuối tức lần 2)
. Ngày trả tiền mua lần 2----------------------> ngày kết thúc Cột U(=ngày thu tiền lần cuối tức lần 2)
Nếu có lần 3 , 4 tính tương tự
3- CPTC bán hàng=(Ngày kết thúc-ngày thu L1)*Tiền thu lần 1*lãi suất năm/360+(Ngày kết thúc-ngày thu L2)*Tiền thu lần 2*lãi suất năm/360

Ngày thu tiền bán lần 1----------------------------------> ngày kết thúc Cột U(=ngày thu tiền lần cuối tức lần 2)
. Ngày thu tiền bán lần 2--------------> ngày kết thúc Cột U(=ngày thu tiền lần cuối tức lần 2)
Nếu có lần 3 , 4 tính tương tự

 

[quocgiacan]

cho mình hỏi, nếu công thức dài quá thì dặt name cho nó gọn có được không nhỉ

"Name" chỉ dùng để làm gọn 1 đoạn công thức nào đó được lặp đi lặp lại nhiều lần trong công thức chính.

Bạn cứ tiếp tục bổ sung cho hết những ý gì trong công thức của bạn như các bài bạn vừa viết. (tôi cũng chưa có thời gian nhìn qua, tối về nghiền ngẫm)

Bạn thấy đó, sẽ có nhiều anh em hỗ trợ bạn bằng cách này hay cách khác: như @bebo021999 , @eke_rula.... và còn nhiều anh em khác nữa đang quan tâm đến bạn.

Vậy, cứ yên chí là vấn đề của bạn sẽ được giải quyết, trên tinh thần có đầy đủ sự hợp tác như bạn đã và đang làm nha.

Chúc bạn ngày vui.

 

[eke_rula]

2- CPTC nhập hàng=(Ngày kết thúc-ngày nhập L1)*Tiền mua lần 1*lãi suất năm/360+(Ngày kết thúc-ngày nhập L2)*Tiền mua lần 2*lãi suất năm/360

Ngày trả tiền mua lần 1----------------------------------> ngày kết thúc Cột U(=ngày thu tiền lần cuối tức lần 2)
. Ngày trả tiền mua lần 2----------------------> ngày kết thúc Cột U(=ngày thu tiền lần cuối tức lần 2)
Nếu có lần 3 , 4 tính tương tự
3- CPTC bán hàng=(Ngày kết thúc-ngày thu L1)*Tiền thu lần 1*lãi suất năm/360+(Ngày kết thúc-ngày thu L2)*Tiền thu lần 2*lãi suất năm/360

Ngày thu tiền bán lần 1----------------------------------> ngày kết thúc Cột U(=ngày thu tiền lần cuối tức lần 2)
. Ngày thu tiền bán lần 2--------------> ngày kết thúc Cột U(=ngày thu tiền lần cuối tức lần 2)
Nếu có lần 3 , 4 tính tương tự

Cách tính này của bạn tức là thu được tiền bán hàng sẽ trả tiền mua hàng luôn chứ gì nữa!!!

 

[Salemok]

Cách tính này của bạn tức là thu được tiền bán hàng sẽ trả tiền mua hàng luôn chứ gì nữa!!!

Thực tế bên mình thanh toán khi nhập hàng 10 hoặc 20% từng đơnhàng. Sau 60 đến 90 ngày sẽ thanh toán nốt cho nhà cung cấp để nhận hồ sơ.
Còn khi bán hàng có khi có hàng trong kho rồi mới đàm phán bán hàng. Có khi nhận đặt cọc của khách hàng rồi mới đặt cọc cho nhà cung cấp. Nên không phải là thu được tiền bán hàng rồi sẽ trả tiền mua hàng luôn. Vấn đề là để tính chi phí tài chính cho từng đơn hàng minh flaays ngày thu tiền cuối cùng của khách mua là ngày kết thúc của một thương vụ mua ban. Còn việc tính chi phí tài chính tính từ các lần bỏ tiền nhập hàng trừ đi lợi ích từ việc thu tiền khách hàng đến thời điểm cuối (lợi ích này đương nhiên lần cuối trùng mốc nên Ko tính)

 

[eke_rula]

Thực tế bên mình thanh toán khi nhập hàng 10 hoặc 20% từng đơnhàng. Sau 60 đến 90 ngày sẽ thanh toán nốt cho nhà cung cấp để nhận hồ sơ.
Còn khi bán hàng có khi có hàng trong kho rồi mới đàm phán bán hàng. Có khi nhận đặt cọc của khách hàng rồi mới đặt cọc cho nhà cung cấp. Nên không phải là thu được tiền bán hàng rồi sẽ trả tiền mua hàng luôn. Vấn đề là để tính chi phí tài chính cho từng đơn hàng minh flaays ngày thu tiền cuối cùng của khách mua là ngày kết thúc của một thương vụ mua ban. Còn việc tính chi phí tài chính tính từ các lần bỏ tiền nhập hàng trừ đi lợi ích từ việc thu tiền khách hàng đến thời điểm cuối (lợi ích này đương nhiên lần cuối trùng mốc nên Ko tính)

Bạn triển khai công thức trên của bạn xem, nó như là khi nào có tiền sẽ trả NCC, mà cái này bạn đang ước tính cái khoản thu nhập tài chính của bạn chứ đâu phải chi phí tài chính, có thể thực tế khác như bạn nói là trả theo hợp đồng là 30,60,90 gì đấy..., nếu theo nguyên tắc của bạn file này, khi nào bán hàng xong bạn mới trả tiền cho nhà cung cấp (mà khoảng thời gian đó không xác định) tức là bạn đang chiếm dụng vốn của NCC hay nói cách khác là chi phí cơ hôi của nhà cung cấp hay thu nhập khác của bạn, nói ví dụ đơn giản bạn mua 100tr , nhưng bạn không trả tiền liền mà bạn đi gửi ngân hàng sẽ thu được tiền lãi và nó là của bạn càng lâu thì lãi càng nhiều , nếu như bạn trả từ đầu thì đó là phần lãi vay của NCC. Nếu ngay từ đầu bạn không có tiền trả bạn phải vay ngân hàng để trả tiền thì lãi bạn đóng cho ngân hàng thì đó mới là chi phí tài chính(lãi vay) như vậy nó mới phù hợp với file này.

 

[Salemok]

Bên mình phải vay ngân hàng để thanh toán cho nhà cung cấp. Kể từ mỗi lần mình trả nhà cung cấp là mình phải trả lãi. Vì vậy mình tính từ mỗi lần giải ngân đến khi thu đủ tiền bán là chi phí tài chính về việc mua hàng
Khi thu được tiền KH từng lần thì mình tính là thu nhập để giảm trừ chi phí tài chính mua hàng và mình cũng tính bằng lãi vay ngân hàng đến thười điểm thu đủ tiền
Vì việc thanh toán mua cũng như thu tiền khách hàng có thể nhiều lần nên mình phải tính vậy cho giản đơn
Sau đó lấy hiệu CPTC mua- CPTC bán=CPTC phải bỏ ra trong kỳ. Nếu dương thì là chí phí. Âm thì thôi (thực ra có thể lấy âm là chi phí tài chính chiếm dụng)

* Mục tiêu của mình là tính chi phí tài chính. Mình xin gửi kèm sơ đồ mô tả.
Nếu các bạn có cách tính hay có thể giúp mình. Không cứ phải tính như cách của mình.

 

[eke_rula]

Bên mình phải vay ngân hàng để thanh toán cho nhà cung cấp. Kể từ mỗi lần mình trả nhà cung cấp là mình phải trả lãi. Vì vậy mình tính từ mỗi lần giải ngân đến khi thu đủ tiền bán là chi phí tài chính về việc mua hàng
Khi thu được tiền KH từng lần thì mình tính là thu nhập để giảm trừ chi phí tài chính mua hàng và mình cũng tính bằng lãi vay ngân hàng đến thười điểm thu đủ tiền
Vì việc thanh toán mua cũng như thu tiền khách hàng có thể nhiều lần nên mình phải tính vậy cho giản đơn
Sau đó lấy hiệu CPTC mua- CPTC bán=CPTC phải bỏ ra trong kỳ. Nếu dương thì là chí phí. Âm thì thôi (thực ra có thể lấy âm là chi phí tài chính chiếm dụng)

* Mục tiêu của mình là tính chi phí tài chính. Mình xin gửi kèm sơ đồ mô tả.
Nếu các bạn có cách tính hay có thể giúp mình. Không cứ phải tính như cách của mình.

Vậy là bạn đang tính là mỗi lần mua hàng là vay ngân hàng trả cho NCC luôn, khi nào bán hàng xong thì sẽ trả lại cho ngân hàng, vậy là quan hệ của bạn với ngân hàng đúng không bạn. Nếu vậy thì lãi sẽ bị tính từ lúc mua hàng, vậy đến khi thu tiền lần 1,2,3 gì đấy bạn có chuyển thanh toán nợ với ngân hàng luôn không, vì mình thấy bạn có tính lãi lúc thu được tiền!!!

 

[Salemok]

Vậy là bạn đang tính là mỗi lần mua hàng là vay ngân hàng trả cho NCC luôn, khi nào bán hàng xong thì sẽ trả lại cho ngân hàng, vậy là quan hệ của bạn với ngân hàng đúng không bạn. Nếu vậy thì lãi sẽ bị tính từ lúc mua hàng, vậy đến khi thu tiền lần 1,2,3 gì đấy bạn có chuyển thanh toán nợ với ngân hàng luôn không, vì mình thấy bạn có tính lãi lúc thu được tiền!!!

Nếu đúng ra thì khi thu tiền lần 1 sẽ đối trừ giá trị đã trả nhà cung cấp trước đó... do thanh toán nhiêu lần lẫn lộn rất phức tạp nên mính quy về điểm chốt là kết thúc hợp đồng. Các khaonr đã chi ra tính đến mốc đó trừ đi các khaonr đã thu về tính đến mốc đó. Còn thu là thu về TK của mình. Nhưng phải tính tối ưu việc sử dụng vốn nên mình coi như khoản đó đem trả ngân hàng (có thể trả hay ko thì tùy)
Mục tiêu là mình tính ra chi phí tài chính cho từng vụ việc bán hàng một từ khâu nhập hàng đến khâu thu tiền đến khi thu hết thì thôi. Bạn có cách tính khác càng dễ hiểu càng chính xác càng tốt. Vì thực tế đòi hỏi thì doanh nghiệp nào cũng cần việc tính toán này. Nhất là những ngành hàng có giá trị lớn. (ở đây kể cả ko vay ngân hàng nhưng người ta vẫn cần phải có con tính này.)

 

[eke_rula]

Nếu đúng ra thì khi thu tiền lần 1 sẽ đối trừ giá trị đã trả nhà cung cấp trước đó... do thanh toán nhiêu lần lẫn lộn rất phức tạp nên mính quy về điểm chốt là kết thúc hợp đồng. Các khaonr đã chi ra tính đến mốc đó trừ đi các khaonr đã thu về tính đến mốc đó. Còn thu là thu về TK của mình. Nhưng phải tính tối ưu việc sử dụng vốn nên mình coi như khoản đó đem trả ngân hàng (có thể trả hay ko thì tùy)
Mục tiêu là mình tính ra chi phí tài chính cho từng vụ việc bán hàng một từ khâu nhập hàng đến khâu thu tiền đến khi thu hết thì thôi. Bạn có cách tính khác càng dễ hiểu càng chính xác càng tốt. Vì thực tế đòi hỏi thì doanh nghiệp nào cũng cần việc tính toán này. Nhất là những ngành hàng có giá trị lớn. (ở đây kể cả ko vay ngân hàng nhưng người ta vẫn cần phải có con tính này.)

Vì bạn đang là người nợ nên phương án tối ưu nhất là có tiền sẽ trả liền cho ngân hàng để giảm chi phí lãi vay đến mức thấp nhất (với điều kiện là các dự án khác có tỷ suất sinh lời thấp hơn lãi vay hay có độ rủi cao mà bạn không dám đầu tư nên phải gửi ngân hàng để hưởng lãi suất tiết kiệm ). Bạn đang tính trong trường hợp là lãi suất cho vay = lãi suất gửi tiết kiệm thì phương án của bạn nói và phương án trả tiền liên cho ngân hàng là như nhau. Nhưng trong thực tế lãi suất cho vay luôn lớn hơn lãi suất tiết kiệm nên cách bạn nói là không phù hợp trong thực tế và không có ai làm cả, doanh nghiệp chỉ giữ lại tiền và chịu chi phí tài chính (lãi vay) khi biết chắc rằng khoản giữ lại đó sẽ tạo ra được thu nhập lớn hơn rất nhiều lần chi phí lãi vay đó.
Cách tính thì không khó, nhưng bạn yêu cầu phải tính lãi vay từng tháng và không tính phần lãi vay của năm trước (vì có thể đã tính trong năm tài chính trước đó) nên công thức hơi phức tạp chút. Mình chỉnh công thức của lại theo cách của bạn nói, bạn dùng công thức này ở W5:

=MAX(IFERROR(SUMPRODUCT((ROW(INDIRECT($H5&":"&$P5))>=N(OFFSET($A5,,{7,8,14,15})))*N(OFFSET($A5,,{10,11,17,18}))*{1,1,-1,-1}*(TEXT(ROW(INDIRECT($H5&":"&$P5)),"myyyy")=W$4&YEAR($X$1))*$Z$1/360),),)

Fill xuống, fill sang phải!!
Bạn kiểm tra lại xem đúng không!!!

 

[bebo021999]

Xét về phương diện đánh giá hiệu quả tài chính, cứ phát sinh mua hàng hoặc phát sinh khoản tiền tồn đọng, là bắt đầu phát sinh chi phí gọi là "chi phí cơ hội"
Hình dung xem khi bạn có khoản tiền 10 triệu, nếu bạn gửi NH lấy lãi, bạn sẽ có số tiền, VD: 100 ngàn/tháng
Nếu bạn mua 2 cái Tivi , mỗi cái 5 triệu, hoặc bạn cất trong két, nghĩa là bạn mất đi 100 ngàn / tháng, đây chính là chi phí cơ hội.
Giả sử sau 1 tháng, bạn bán bớt 1 cái TV giá 5 triệu và gửi NH, Chi phí của bạn sẽ là : CPTC 100 ngàn/tháng - DTTC (doanh thu TC) 50 ngàn/tháng = 50 ngàn/tháng
Một cách tổng quát, cách tính CPTC khi mua hàng là: CPTC từ ngày mua đến ngày kết thúc - Doanh Thu TC từ ngày thu đến ngày KT
Xét trong từng tháng, CPTC = CPTC từ ngày mua đến cuối tháng - DTTC từ ngày thu đến ngày cuối tháng
(Ngày cuối tháng hoặc ngày kết thúc, lấy ngày nhỏ hơn, trong TH ngày KT nằm trong tháng)
Đến đây, bài toán quy về tính số ngày đến ngày cuối tháng.
Nói thì dễ, nhưng khi bắt tay vào thì hơi rắc rối, lười quá, để dành cho các chuyên gia công thức khai thác)

 

[bebo021999]

post trùng, mod xóa giúp.

 

[Salemok]

Vì bạn đang là người nợ nên phương án tối ưu nhất là có tiền sẽ trả liền cho ngân hàng để giảm chi phí lãi vay đến mức thấp nhất (với điều kiện là các dự án khác có tỷ suất sinh lời thấp hơn lãi vay hay có độ rủi cao mà bạn không dám đầu tư nên phải gửi ngân hàng để hưởng lãi suất tiết kiệm ). Bạn đang tính trong trường hợp là lãi suất cho vay = lãi suất gửi tiết kiệm thì phương án của bạn nói và phương án trả tiền liên cho ngân hàng là như nhau. Nhưng trong thực tế lãi suất cho vay luôn lớn hơn lãi suất tiết kiệm nên cách bạn nói là không phù hợp trong thực tế và không có ai làm cả, doanh nghiệp chỉ giữ lại tiền và chịu chi phí tài chính (lãi vay) khi biết chắc rằng khoản giữ lại đó sẽ tạo ra được thu nhập lớn hơn rất nhiều lần chi phí lãi vay đó.
Cách tính thì không khó, nhưng bạn yêu cầu phải tính lãi vay từng tháng và không tính phần lãi vay của năm trước (vì có thể đã tính trong năm tài chính trước đó) nên công thức hơi phức tạp chút. Mình chỉnh công thức của lại theo cách của bạn nói, bạn dùng công thức này ở W5:

=MAX(IFERROR(SUMPRODUCT((ROW(INDIRECT($H5&":"&$P5))>=N(OFFSET($A5,,{7,8,14,15})))*N(OFFSET($A5,,{10,11,17,18}))*{1,1,-1,-1}*(MONTH(ROW(INDIRECT($H5&":"&$P5)))=W$4)*(YEAR(ROW(INDIRECT($H5&":"&$P5)))=YEAR($X$1))*$Z$1/360),),)

Fill xuống, fill sang phải!!
Bạn kiểm tra lại xem đúng không!!!

Thực ra đây là mình tính chi phí cho 1 phòng bán hàng sát nhất. Nên quy đồng về 1 tỷ lệ lãi suất chư đi vay.
Mình sẽ thử công tức của bạn xem sao.

 

[Salemok]

Vì bạn đang là người nợ nên phương án tối ưu nhất là có tiền sẽ trả liền cho ngân hàng để giảm chi phí lãi vay đến mức thấp nhất (với điều kiện là các dự án khác có tỷ suất sinh lời thấp hơn lãi vay hay có độ rủi cao mà bạn không dám đầu tư nên phải gửi ngân hàng để hưởng lãi suất tiết kiệm ). Bạn đang tính trong trường hợp là lãi suất cho vay = lãi suất gửi tiết kiệm thì phương án của bạn nói và phương án trả tiền liên cho ngân hàng là như nhau. Nhưng trong thực tế lãi suất cho vay luôn lớn hơn lãi suất tiết kiệm nên cách bạn nói là không phù hợp trong thực tế và không có ai làm cả, doanh nghiệp chỉ giữ lại tiền và chịu chi phí tài chính (lãi vay) khi biết chắc rằng khoản giữ lại đó sẽ tạo ra được thu nhập lớn hơn rất nhiều lần chi phí lãi vay đó.
Cách tính thì không khó, nhưng bạn yêu cầu phải tính lãi vay từng tháng và không tính phần lãi vay của năm trước (vì có thể đã tính trong năm tài chính trước đó) nên công thức hơi phức tạp chút. Mình chỉnh công thức của lại theo cách của bạn nói, bạn dùng công thức này ở W5:

=MAX(IFERROR(SUMPRODUCT((ROW(INDIRECT($H5&":"&$P5))>=N(OFFSET($A5,,{7,8,14,15})))*N(OFFSET($A5,,{10,11,17,18}))*{1,1,-1,-1}*(MONTH(ROW(INDIRECT($H5&":"&$P5)))=W$4)*(YEAR(ROW(INDIRECT($H5&":"&$P5)))=YEAR($X$1))*$Z$1/360),),)

Fill xuống, fill sang phải!!
Bạn kiểm tra lại xem đúng không!!!

Mình dán vào W5 nó = 0 hết
Bạn ktra lại nhé

 

[eke_rula]

Mình dán vào W5 nó = 0 hết
Bạn ktra lại nhé

Bạn xem cái file mình gửi ấy!!!

 

[Salemok]

Bạn xem cái file mình gửi ấy!!!

Đoạn này
N(OFFSET($A5;;{10\11\17\18}))*{1\1\-1\-1}
Là gì bạn ơi

 

[eke_rula]

Đoạn này
N(OFFSET($A5;;{10\11\17\18}))*{1\1\-1\-1}
Là gì bạn ơi

Bạn đừng đi sâu vào công thức, xem kết quả chạy ra đúng không cái đã!!!

 

[dazkangel]

Bạn đừng đi sâu vào công thức, xem kết quả chạy ra đúng không cái đã!!!

bạn chỉ mình hàm này với:
=ROW(INDIRECT($H5&":"&$P5))
nếu nhấn F9 thì chỉ ra lỗi thôi, trước giờ xài ROW mục đích chỉ là lấy các số liên tiếp chưa nghĩ tới cái này bao giờ.

 

[eke_rula]

bạn chỉ mình hàm này với:
=ROW(INDIRECT($H5&":"&$P5))
nếu nhấn F9 thì chỉ ra lỗi thôi, trước giờ xài ROW mục đích chỉ là lấy các số liên tiếp chưa nghĩ tới cái này bao giờ.

Bị lỗi đúng rồi P5 là rỗng mà
xem dòng số 7 ấy =ROW(INDIRECT($H7&":"&$P7))

 

[dazkangel]

Bị lỗi đúng rồi P5 là rỗng mà
xem dòng số 7 ấy =ROW(INDIRECT($H7&":"&$P7))

hi tìm hiểu mãi mới ra, bạn toàn chiêu độc

 

[eke_rula]

hi tìm hiểu mãi mới ra, bạn toàn chiêu độc

Độc gì đâu, trên diễn đàn nhóc người sài, tại bạn không để ý đấy!!!

 

[dazkangel]

Độc gì đâu, trên diễn đàn nhóc người sài, tại bạn không để ý đấy!!!

híc tại bạn không biết đấy mình mới vào, hàm row mới xài kiểu A1:A10 thôi

 

[Salemok]

Bạn đừng đi sâu vào công thức, xem kết quả chạy ra đúng không cái đã!!!

Kết quả đúng ròi bạn ơi. Công thức ngắn thật. Bạn giải thích giúp cong thức nhé. Mình đang học hỏi
'=MAX(IFERROR(SUMPRODUCT((ROW(INDIRECT($H5&":"&$P5))>=N(OFFSET($A5;;{7\8\14\15})))*N(OFFSET($A5;;{10\11\17\18}))*{1\1\-1\-1}*(MONTH(ROW(INDIRECT($H5&":"&$P5)))=W$4)*(YEAR(ROW(INDIRECT($H5&":"&$P5)))=YEAR($X$1))*$Z$1/360)

 

[huuthang_bd]

Trong trường hợp phát sinh lãi tài chính (Thu nhập lớn hơn chi phí) thì có tính không?
Trong trường hợp này dùng Sumproduct tuy gọn nhưng sẽ chậm hơn so với cách tính thông thường.

 

[eke_rula]

Trong trường hợp phát sinh lãi tài chính (Thu nhập lớn hơn chi phí) thì có tính không?
Trong trường hợp này dùng Sumproduct tuy gọn nhưng sẽ chậm hơn so với cách tính thông thường.

Chỉ tính chi phí tài chính thôi anh ạ, anh viết công thức khác cho em học với, cách của em hơi phức tạp!!!

 

[eke_rula]

Kết quả đúng ròi bạn ơi. Công thức ngắn thật. Bạn giải thích giúp cong thức nhé. Mình đang học hỏi

, mình dùng CT ở dòng 7 nhe:
MAX(IFERROR(SUMPRODUCT((ROW(INDIRECT($H7&":"&$P7))>=N(OFFSET($A7;;{7\8\14\15})))*N(OFFSET($A7;;{10\11\17\18}))*{1\1\-1\-1}*(MONTH(ROW(INDIRECT($H7&":"&$P7)))=W$4)*(YEAR(ROW(INDIRECT($H7&":"&$P7)))=YEAR($X$1))*$Z$1/360)
Bài này mình dùng CT mảng 2 chiểu để giải bạn ạ, tính theo đúng như bạn nói , là tất cả các đều so sánh với ngày kết thúc hết và chi phí =lãi mua hàng-lãi bán hàng.
Mình đi từng đoạn trong công thức:
1. N(OFFSET($A7;;{7\8\14\15}))) chính là các ngày phát sinh (ngày đặt hàng 1, ngày đặt hàng 2, ngày thu tiền lần 1, ngày thu tiền lần 2..) (H7,I7, O7,P7)
2. N(OFFSET($A7;;{10\11\17\18}))*{1\1\-1\-1} chính là các khoản phát sinh tiền (tiền đặt hàng 1, tiền đặt hàng 2, tiền thu lần 1, tiền thu lần 2). Tại sao lại có đoạn {1\1\-1\-1}, vì bạn nói lãi của mua hàng -lãi bán hàng tức là lãi mua hàng là dương và lãi bán hàng sẽ là âm. (K7,L7,R7,S7)
3. ROW(INDIRECT($H7&":"&$P7)) là một tập hợp ngày từ ngày mua hàng lần 1 đến ngày kết thúc (tức là ngày thu 2) , trong file dòng 7 thì từ ngày: 01/01/02016 đến ngày 18/03/2016 (H7->P7)
4. ROW(INDIRECT($H7&":"&$P7))>=N(OFFSET($A7;;{7\8\14\15})) đoạn này sẽ tính ra những ngày chịu lãi của những ngày phát sinh, ví dụ trong dòng 7:
Ngày đặt hàng 1 sẽ phát sinh lãi mua hàng từ ngày 01/01/02016-> 18/03/2016 (78 ngày)
Ngày đặt hàng 2 sẽ phát sinh lãi mua hàng từ ngày 02/02/02016-> 18/03/2016 (46 ngày)
Ngày thu 1 sẽ phát sinh lãi mua hàng từ ngày 20/01/02016-> 18/03/2016 (59 ngày)
Ngày thu 2 sẽ phát sinh lãi mua hàng từ ngày 18/03/02016-> 18/03/2016 (1 ngày)
5. (ROW(INDIRECT($H7&":"&$P7))>=N(OFFSET($A7;;{7\8\14\15})))*N(OFFSET($A7;;{10\11\17\18}))*{1\1\-1\-1} sau khi được ngày chịu lãi của từng gia đoạn thì chỉ nhân số ngày chĩu lãi đó với từng khoản tiên mà thôi
Ngày đặt hàng 1 sẽ phát sinh lãi mua hàng từ ngày 01/01/02016-> 18/03/2016: 77* 150920000*1
Ngày đặt hàng 2 sẽ phát sinh lãi mua hàng từ ngày 02/02/02016-> 18/03/2016: 45*603680000*1
Ngày thu 1 sẽ phát sinh lãi mua hàng từ ngày 20/01/02016-> 18/03/2016: 58*238500000*-1
Ngày thu 2 sẽ phát sinh lãi mua hàng từ ngày 18/03/02016-> 18/03/2016: 1*556500000*-1
6. (MONTH(ROW(INDIRECT($H7&":"&$P7)))=W$4)*(YEAR(ROW(INDIRECT($H7&":"&$P7)))=YEAR($X$1)) đoạn này thì chỉ để xác định tháng nào năm để phân bổ vào file của bạn cho hợp lý mà thôi
7. $Z$1/360 Chỗ này thì chắc bạn hiểu.
Mình không đi sâu vào phân tích chi tiết công thức vì là mảng 2 chiều nên rất phức tạp và mơ hồ nếu bạn chưa nắm rõ về mảng.
Chỉ cần bạn hiểu những đoạn trên mình nói thì bạn có thể làm tương tự cho các lần khác!!
Hi vọng giúp được bạn!!

 

[huuthang_bd]

Chỉ tính chi phí tài chính thôi anh ạ, anh viết công thức khác cho em học với, cách của em hơi phức tạp!!!

Công thức mảng nhé.

=MAX(SUM(IFERROR((DATEDIF(EOMONTH($X$1,W$4-2),MIN($P5,EOMONTH($X$1,W$4-1)),"d")-IFERROR(DATEDIF(EOMONTH($X$1,W$4-2),N(OFFSET($A5,,{7\8\14\15})),"d")-1,))^0.5,)^2*N(OFFSET($A5,,{10\11\17\18}))*{1\1\-1\-1})*$Z$1/360,)

Các dòng 35, 45, 46, 47 kết quả của bạn chưa đúng. Cái này là do lỗi dữ liệu thôi (Ngày đặt hàng 1 > ngày đặt hàng 2)

 

[huuthang_bd]

, mình dùng CT ở dòng 7 nhe:
MAX(IFERROR(SUMPRODUCT((ROW(INDIRECT($H7&":"&$P7))>=N(OFFSET($A7;;{7\8\14\15})))*N(OFFSET($A7;;{10\11\17\18}))*{1\1\-1\-1}*(MONTH(ROW(INDIRECT($H7&":"&$P7)))=W$4)*(YEAR(ROW(INDIRECT($H7&":"&$P7)))=YEAR($X$1))*$Z$1/360)
Bài này mình dùng CT mảng 2 chiểu để giải bạn ạ, tính theo đúng như bạn nói , là tất cả các đều so sánh với ngày kết thúc hết và chi phí =lãi mua hàng-lãi bán hàng.
Mình đi từng đoạn trong công thức:
1. N(OFFSET($A7;;{7\8\14\15}))) chính là các ngày phát sinh (ngày đặt hàng 1, ngày đặt hàng 2, ngày thu tiền lần 1, ngày thu tiền lần 2..) (H7,I7, O7,P7)
2. N(OFFSET($A7;;{10\11\17\18}))*{1\1\-1\-1} chính là các khoản phát sinh tiền (tiền đặt hàng 1, tiền đặt hàng 2, tiền thu lần 1, tiền thu lần 2). Tại sao lại có đoạn {1\1\-1\-1}, vì bạn nói lãi của mua hàng -lãi bán hàng tức là lãi mua hàng là dương và lãi bán hàng sẽ là âm. (K7,L7,R7,S7)
3. ROW(INDIRECT($H7&":"&$P7)) là một tập hợp ngày từ ngày mua hàng lần 1 đến ngày kết thúc (tức là ngày thu 2) , trong file dòng 7 thì từ ngày: 01/01/02016 đến ngày 18/03/2016 (H7->P7)
4. ROW(INDIRECT($H7&":"&$P7))>=N(OFFSET($A7;;{7\8\14\15})) đoạn này sẽ tính ra những ngày chịu lãi của những ngày phát sinh, ví dụ trong dòng 7:
Ngày đặt hàng 1 sẽ phát sinh lãi mua hàng từ ngày 01/01/02016-> 18/03/2016 (77 ngày)
Ngày đặt hàng 2 sẽ phát sinh lãi mua hàng từ ngày 02/02/02016-> 18/03/2016 (45 ngày)
Ngày thu 1 sẽ phát sinh lãi mua hàng từ ngày 20/01/02016-> 18/03/2016 (58 ngày)
Ngày thu 2 sẽ phát sinh lãi mua hàng từ ngày 18/03/02016-> 18/03/2016 (1 ngày)
5. (ROW(INDIRECT($H7&":"&$P7))>=N(OFFSET($A7;;{7\8\14\15})))*N(OFFSET($A7;;{10\11\17\18}))*{1\1\-1\-1} sau khi được ngày chịu lãi của từng gia đoạn thì chỉ nhân số ngày chĩu lãi đó với từng khoản tiên mà thôi
Ngày đặt hàng 1 sẽ phát sinh lãi mua hàng từ ngày 01/01/02016-> 18/03/2016: 77* 150920000*1
Ngày đặt hàng 2 sẽ phát sinh lãi mua hàng từ ngày 02/02/02016-> 18/03/2016: 45*603680000*1
Ngày thu 1 sẽ phát sinh lãi mua hàng từ ngày 20/01/02016-> 18/03/2016: 58*238500000*-1
Ngày thu 2 sẽ phát sinh lãi mua hàng từ ngày 18/03/02016-> 18/03/2016: 1*556500000*-1
6. (MONTH(ROW(INDIRECT($H7&":"&$P7)))=W$4)*(YEAR(ROW(INDIRECT($H7&":"&$P7)))=YEAR($X$1)) đoạn này thì chỉ để xác định tháng nào năm để phân bổ vào file của bạn cho hợp lý mà thôi
7. $Z$1/360 Chỗ này thì chắc bạn hiểu.
Mình không đi sâu vào phân tích chi tiết công thức vì là mảng 2 chiều nên rất phức tạp và mơ hồ nếu bạn chưa nắm rõ về mảng.
Chỉ cần bạn hiểu những đoạn trên mình nói thì bạn có thể làm tương tự cho các lần khác!!
Hi vọng giúp được bạn!!

Bạn siêng thật. Tôi là tôi lười mấy cái vụ này lắm
Đoạn 6 có thể thay bằng đoạn sau cho gọn.

(TEXT(ROW(INDIRECT($H5&":"&$P5)),"myyyy")=W$4&YEAR($X$1))

 

[HieuCD]

, mình dùng CT ở dòng 7 nhe:
MAX(IFERROR(SUMPRODUCT((ROW(INDIRECT($H7&":"&$P7))>=N(OFFSET($A7;;{7\8\14\15})))*N(OFFSET($A7;;{10\11\17\18}))*{1\1\-1\-1}*(MONTH(ROW(INDIRECT($H7&":"&$P7)))=W$4)*(YEAR(ROW(INDIRECT($H7&":"&$P7)))=YEAR($X$1))*$Z$1/360)
Bài này mình dùng CT mảng 2 chiểu để giải bạn ạ, tính theo đúng như bạn nói , là tất cả các đều so sánh với ngày kết thúc hết và chi phí =lãi mua hàng-lãi bán hàng.
Mình đi từng đoạn trong công thức:
1. N(OFFSET($A7;;{7\8\14\15}))) chính là các ngày phát sinh (ngày đặt hàng 1, ngày đặt hàng 2, ngày thu tiền lần 1, ngày thu tiền lần 2..) (H7,I7, O7,P7)
2. N(OFFSET($A7;;{10\11\17\18}))*{1\1\-1\-1} chính là các khoản phát sinh tiền (tiền đặt hàng 1, tiền đặt hàng 2, tiền thu lần 1, tiền thu lần 2). Tại sao lại có đoạn {1\1\-1\-1}, vì bạn nói lãi của mua hàng -lãi bán hàng tức là lãi mua hàng là dương và lãi bán hàng sẽ là âm. (K7,L7,R7,S7)
3. ROW(INDIRECT($H7&":"&$P7)) là một tập hợp ngày từ ngày mua hàng lần 1 đến ngày kết thúc (tức là ngày thu 2) , trong file dòng 7 thì từ ngày: 01/01/02016 đến ngày 18/03/2016 (H7->P7)
4. ROW(INDIRECT($H7&":"&$P7))>=N(OFFSET($A7;;{7\8\14\15})) đoạn này sẽ tính ra những ngày chịu lãi của những ngày phát sinh, ví dụ trong dòng 7:
Ngày đặt hàng 1 sẽ phát sinh lãi mua hàng từ ngày 01/01/02016-> 18/03/2016 (77 ngày)
Ngày đặt hàng 2 sẽ phát sinh lãi mua hàng từ ngày 02/02/02016-> 18/03/2016 (45 ngày)
Ngày thu 1 sẽ phát sinh lãi mua hàng từ ngày 20/01/02016-> 18/03/2016 (58 ngày)
Ngày thu 2 sẽ phát sinh lãi mua hàng từ ngày 18/03/02016-> 18/03/2016 (1 ngày)
5. (ROW(INDIRECT($H7&":"&$P7))>=N(OFFSET($A7;;{7\8\14\15})))*N(OFFSET($A7;;{10\11\17\18}))*{1\1\-1\-1} sau khi được ngày chịu lãi của từng gia đoạn thì chỉ nhân số ngày chĩu lãi đó với từng khoản tiên mà thôi
Ngày đặt hàng 1 sẽ phát sinh lãi mua hàng từ ngày 01/01/02016-> 18/03/2016: 77* 150920000*1
Ngày đặt hàng 2 sẽ phát sinh lãi mua hàng từ ngày 02/02/02016-> 18/03/2016: 45*603680000*1
Ngày thu 1 sẽ phát sinh lãi mua hàng từ ngày 20/01/02016-> 18/03/2016: 58*238500000*-1
Ngày thu 2 sẽ phát sinh lãi mua hàng từ ngày 18/03/02016-> 18/03/2016: 1*556500000*-1
6. (MONTH(ROW(INDIRECT($H7&":"&$P7)))=W$4)*(YEAR(ROW(INDIRECT($H7&":"&$P7)))=YEAR($X$1)) đoạn này thì chỉ để xác định tháng nào năm để phân bổ vào file của bạn cho hợp lý mà thôi
7. $Z$1/360 Chỗ này thì chắc bạn hiểu.
Mình không đi sâu vào phân tích chi tiết công thức vì là mảng 2 chiều nên rất phức tạp và mơ hồ nếu bạn chưa nắm rõ về mảng.
Chỉ cần bạn hiểu những đoạn trên mình nói thì bạn có thể làm tương tự cho các lần khác!!
Hi vọng giúp được bạn!!

vẫn chưa hiểu:
- kết quả W7=302753, từ các số trên làm sao ra được kết quả
- 18/03/02016-> 18/03/2016: số ngày là 1=18/03/2016-18/03/02016+1
20/01/02016-> 18/03/2016: số ngày là 58 sao không là 18/03/2016-20/01/02016+1=59

 

[huuthang_bd]

vẫn chưa hiểu:
- kết quả W7=302753, từ các số trên làm sao ra được kết quả
- 18/03/02016-> 18/03/2016: số ngày là 1=18/03/2016-18/03/02016+1
20/01/02016-> 18/03/2016: số ngày là 58 sao không là 18/03/2016-20/01/02016+1=59

- Đoạn 5 phải kết hợp với đoạn 6 thì mới ra kết quả. Ví dụ 20/01/02016-> 18/03/2016 là 59 ngày nhưng trong tháng 1 chỉ có 12 ngày (20->31). Giải thích như vậy là quá chi tiết rồi.
- Chắc tính nhầm thôi. Là 59.

 

[eke_rula]

Bạn siêng thật. Tôi là tôi lười mấy cái vụ này lắm
Đoạn 6 có thể thay bằng đoạn sau cho gọn.

(TEXT(ROW(INDIRECT($H5&":"&$P5)),"myyyy")=W$4&YEAR($X$1))

Cảm ơn anh, gọn hơn nhiều lắm, để em sửa lại CT!!!

 

[eke_rula]

vẫn chưa hiểu:
- kết quả W7=302753, từ các số trên làm sao ra được kết quả
- 18/03/02016-> 18/03/2016: số ngày là 1=18/03/2016-18/03/02016+1
20/01/02016-> 18/03/2016: số ngày là 58 sao không là 18/03/2016-20/01/02016+1=59

Em ghi nhầm thôi anh ạ là 78, 46,59 và 1 ạ.Mấy số này em ghi ra vậy cho bạn chủ topic dễ hình dung thôi, chứ thực tế vẫn còn nằm trên mảng , vì phải năm trên mảng mới phân loại từng tháng được, nếu tính tổng một cục thì không phân loại được . Anh nhận file, xem cách em tính ở dòng 7, các cột màu xanh, vùng màu vàng là t01 sum vùng đấy sẽ ra 302.753

 

[dazkangel]

Bạn siêng thật. Tôi là tôi lười mấy cái vụ này lắm
Đoạn 6 có thể thay bằng đoạn sau cho gọn.

(TEXT(ROW(INDIRECT($H5&":"&$P5)),"myyyy")=W$4&YEAR($X$1))

vậy mới giỏi diễn đạt anh à, híc híc

 

[huuthang_bd]

vậy mới giỏi diễn đạt anh à, híc híc

Nhiều người thấy công thức phức tạp quá nên nhờ giải thích nhưng giải thích xong họ cũng không nghiên cứu thậm chí không đọc (tôi nói vậy vì có trường hợp giải thích xong không thấy phản hồi). Trong trường hợp này có phải là người trả lời đã bỏ thời gian vô ích không?
Một câu "Bạn giải thích giúp mình công thức" quá đơn giản (đôi khi thuận tay gõ vậy chứ cũng chả quan tâm) nhưng để giải thích cho rõ ràng cũng mất khá nhiều công sức.
Bởi vậy tôi thường không giải thích những yêu cầu giải thích cả công thức. Tôi chỉ giải thích cho bạn nào đã nghiên cứu nhưng có chỗ không hiểu (chỉ hỏi từng phần trong công thức).

Em ghi nhầm thôi anh ạ là 78, 46,59 và 1 ạ.Mấy số này em ghi ra vậy cho bạn chủ topic dễ hình dung thôi, chứ thực tế vẫn còn nằm trên mảng , vì phải năm trên mảng mới phân loại từng tháng được, nếu tính tổng một cục thì không phân loại được . Anh nhận file, xem cách em tính ở dòng 7, các cột màu xanh, vùng màu vàng là t01 sum vùng đấy sẽ ra 302.753

Bạn nghiên cứu sửa lỗi bài #42 luôn cho hoàn chỉnh . Biết là dữ liệu không logic nhưng nếu thực tế dữ liệu có trường hợp như vậy thì cũng phải xử lý.

 

[dazkangel]

Nhiều người thấy công thức phức tạp quá nên nhờ giải thích nhưng giải thích xong họ cũng không nghiên cứu thậm chí không đọc (tôi nói vậy vì có trường hợp giải thích xong không thấy phản hồi). Trong trường hợp này có phải là người trả lời đã bỏ thời gian vô ích không?
Một câu "Bạn giải thích giúp mình công thức" quá đơn giản (đôi khi thuận tay gõ vậy chứ cũng chả quan tâm) nhưng để giải thích cho rõ ràng cũng mất khá nhiều công sức.
Bởi vậy tôi thường không giải thích những yêu cầu giải thích cả công thức. Tôi chỉ giải thích cho bạn nào đã nghiên cứu nhưng có chỗ không hiểu (chỉ hỏi từng phần trong công thức).

Bạn nghiên cứu sửa lỗi bài #42 luôn cho hoàn chỉnh . Biết là dữ liệu không logic nhưng nếu thực tế dữ liệu có trường hợp như vậy thì cũng phải xử lý.

Khà khà, em mà hỏi phải hỏi nguyên phần
Nói vậy thực ra họ không đọc, chứ có giải thích sợ cũng không hiểu anh à haizz

 

[HieuCD]

Em ghi nhầm thôi anh ạ là 78, 46,59 và 1 ạ.Mấy số này em ghi ra vậy cho bạn chủ topic dễ hình dung thôi, chứ thực tế vẫn còn nằm trên mảng , vì phải năm trên mảng mới phân loại từng tháng được, nếu tính tổng một cục thì không phân loại được . Anh nhận file, xem cách em tính ở dòng 7, các cột màu xanh, vùng màu vàng là t01 sum vùng đấy sẽ ra 302.753

Công thức nầy quá dữ, dùng mảng 1 chiều, 2 chiều, lấy cột lung tung, còn lợi dụng thứ tự dòng để xét tháng và năm thì không thể tưởng tượng làm sao nghĩ ra được nếu mình tính thì chỉ có max và min làm tới thôi
chúc các bạn vui

 

[eke_rula]

Nhiều người thấy công thức phức tạp quá nên nhờ giải thích nhưng giải thích xong họ cũng không nghiên cứu thậm chí không đọc (tôi nói vậy vì có trường hợp giải thích xong không thấy phản hồi). Trong trường hợp này có phải là người trả lời đã bỏ thời gian vô ích không?
Một câu "Bạn giải thích giúp mình công thức" quá đơn giản (đôi khi thuận tay gõ vậy chứ cũng chả quan tâm) nhưng để giải thích cho rõ ràng cũng mất khá nhiều công sức.
Bởi vậy tôi thường không giải thích những yêu cầu giải thích cả công thức. Tôi chỉ giải thích cho bạn nào đã nghiên cứu nhưng có chỗ không hiểu (chỉ hỏi từng phần trong công thức).

Bạn nghiên cứu sửa lỗi bài #42 luôn cho hoàn chỉnh . Biết là dữ liệu không logic nhưng nếu thực tế dữ liệu có trường hợp như vậy thì cũng phải xử lý.

Nếu vậy thì thêm MIN nữa là được anh ạ:

W5=MAX(IFERROR(SUMPRODUCT((ROW(INDIRECT(MIN($H5:$I5)&":"&$P5))>=N(OFFSET($A5,,{7,8,14,15})))*N(OFFSET($A5,,{10,11,17,18}))*{1,1,-1,-1}*(TEXT(ROW(INDIRECT(MIN($H5:$I5)&":"&$P5)),"myyyy")=W$4&YEAR($X$1))*$Z$1/360),),)

Còn nếu lộn xộn ngày thu và ngày đặt hàng nữa thì dùng max đuôi nữa là ok. Cám ơn anh!!!

 

[Salemok]

Nếu vậy thì thêm MIN nữa là được anh ạ:

W5=MAX(IFERROR(SUMPRODUCT((ROW(INDIRECT(MIN($H5:$I5)&":"&$P5))>=N(OFFSET($A5,,{7,8,14,15})))*N(OFFSET($A5,,{10,11,17,18}))*{1,1,-1,-1}*(TEXT(ROW(INDIRECT(MIN($H5:$I5)&":"&$P5)),"myyyy")=W$4&YEAR($X$1))*$Z$1/360),),)

Còn nếu lộn xộn ngày thu và ngày đặt hàng nữa thì dùng max đuôi nữa là ok. Cám ơn anh!!!

Mình bận đi công tác mấy hôm nay mới ngồi máy tính. Rất cám ơn các bạn đã chỉ giáo và giúp đỡ.
Vì có nhiều công thức mới mình giờ mới biết nên đang tìm hiểu từng bước. Mình đang tìm hiểu và học công thức của các bạn đây.

 

[Salemok]

Cám ơn các bạn đã chỉ giáo rất chi tiết. Mình đã hiểu và nắm được phần nào . Từ Khủng long giwof thành công thức quá đ
ẹp. Ngắn gọn dễ hiểu (đặc biệt nhờ bạn eke_rula đã bớt thời gian chỉ giáo chi tiết)
Tuy nhiên tình huống mua từ năm trước thì chưa được tính đến thì phải.
Mình thay ô X1 (01/01/2016) bằng W1 (năm 2016) cho gọn.
Bạn giúp mình giải quyết trường hợp ở dòng 15 bôi vàng nhé. Vì nó chạy liên năm
Mình up lại file nhé

 

[eke_rula]

Cám ơn các bạn đã chỉ giáo rất chi tiết. Mình đã hiểu và nắm được phần nào . Từ Khủng long giwof thành công thức quá đ
ẹp. Ngắn gọn dễ hiểu (đặc biệt nhờ bạn eke_rula đã bớt thời gian chỉ giáo chi tiết)
Tuy nhiên tình huống mua từ năm trước thì chưa được tính đến thì phải.
Mình thay ô X1 (01/01/2016) bằng W1 (năm 2016) cho gọn.
Bạn giúp mình giải quyết trường hợp ở dòng 15 bôi vàng nhé. Vì nó chạy liên năm
Mình up lại file nhé

Công thức này chưa tính phần lãi của năm trước, bạn kêu không tính lãi của năm trước mà.
Nếu tính lãi của năm trước thì cộng dồn vào tháng 01 luôn hả bạn??, nếu vậy thì chỉnh công thức lại như vầy:

W5=MAX(IFERROR(SUMPRODUCT((ROW(INDIRECT(MIN($H5:$I5)&":"&$P5))>=N(OFFSET($A5,,{7,8,14,15})))*N(OFFSET($A5,,{10,11,17,18}))*{1,1,-1,-1}*(ROW(INDIRECT(MIN($H5:$I5)&":"&$P5))<=DATE($W$1,W$4+1,))*$Z$1/360),)-SUM($V5:V5),)

Fill sang phải, rồi fill xuống!!

 

[Salemok]

Oh. khong tính phần lãi của năm trước là đúng rồi đó.
Như vậy coogn thức của bạn chỉ tính từ 01/01/2016 thôi đúng không.
Bạn chỉ giúp chỗ nào tính vậy nhé. Mình đọc coogn thức của bạn tưởng nó tính cả nên hỏi vậy.

 

[eke_rula]

Oh. khong tính phần lãi của năm trước là đúng rồi đó.
Như vậy coogn thức của bạn chỉ tính từ 01/01/2016 thôi đúng không.
Bạn chỉ giúp chỗ nào tính vậy nhé. Mình đọc coogn thức của bạn tưởng nó tính cả nên hỏi vậy.

Nó chạy theo năm mặc định của bạn mà chỗ W1, nếu là 2016 thì sẽ bắt đầu từ 1/1/2016, 2017 thì sẽ bắt đầu từ 1/1/2017...

 

[Salemok]

Giúp mình chỗ nào chỉ ra điều đó đc ko.
Vẫn còn non quá

 

[Salemok]

Trong công thức cũ hiện chúng ta tính ngày kế thúc là ngay fthu tiền lần 2.
Tuy nhiên ngyaf kết thúc có thể là lần 1 (thu đủ 100% sau lần 1) hay lần 2 (thu đủ 100% sau lần 2), hay lần 3.. hoặc chưa kế thúc do mới thi được x% sau lần 1 hay lần 2 hoặc chưa thu chút nào.
Mình đổi công thức của bạn tính mốc đến cột P. thành cột V (ngày kế thúc)
- Trong công thức V5=IF($U5<1000;MAX($O5:$Q5);"") ở đây mình xét U5<1000 là do nhiều trường hợp chênh lệch vai fđồng lẻ do khách hàng làm tròn hay chuyển khoản gì đó. Trường hợp này mình coi là họ đã thu đủ.
- Tuy nhiên trường hợp dòng 47 thì ta đã bỏ tiền nhập hàng nhuwngchuwa thu được đủ tiền khách hàng thì nó khoogn tính.

Các bạn chỉ giúp nhé
Cám ơn