Lấy giá tiêu chuẩn

[solomon2211]

Xin vui lòng giúp công thức lấy giá tiêu chuẩn như yêu cầu trong file đính kèm.

Cám ơn nhiều

 

[cop_kh]

Xin vui lòng giúp công thức lấy giá tiêu chuẩn như yêu cầu trong file đính kèm.

Cám ơn nhiều

Mình thêm cột phụ, bạn xem File OK không nhé.

 

[solomon2211]

Cám ơn bạn nhiều!
Có cách nào không thêm cột phụ không?

 

[ptm0412]

Không dùng cột phụ:
E2 = VLOOKUP(B2;'Bang gia'!$B$2:$C$7;2;1)

 

[cop_kh]

E2 = VLOOKUP(B2;'Bang gia'!$B$2:$C$7;2;1)

Theo yêu cầu của chính chủ thì còn thêm mã hàng nữa Bác ạ.

 

[solomon2211]

Theo yêu cầu của chính chủ thì còn thêm mã hàng nữa Bác ạ.

Bạn cop_kh nói đúng đó, vì ví dụ nên không đưa thêm mã hàng nữa. Nhưng thực tế có nhiều mã hàng lắm.

 

[ptm0412]

Nếu bảng giá nhiều mặt hàng được sort theo mặt hàng, thì có thể giải quyết
Nếu bảng giá nhiều mặt hàng sort theo thứ tự ngày tháng, thì chưa tìm ra cách

 

[cadafi]

Xin vui lòng giúp công thức lấy giá tiêu chuẩn như yêu cầu trong file đính kèm.

Ý anh có phải là khi gõ ngày nhập và mã hàng, thì excel sẽ search trong bảng giá với ngày hiệu lực gần ngày nhập kho nhất (ngày hiệu lực gần nhất<= ngày nhập kho) ứng với mã hàng và tham chiếu cho ra đơn giá không?

Nếu đúng như vậy thì yêu cầu này ta dùng hàm SUM để giải quyết như sau:

1. Hàm này để lấy ra ngày hiệu lực gần nhất với ngày nhập kho (Ctrl+Shift+Enter): (tìm ngày cận dưới ngày nhập kho trong danh sách ngày hiệu lực trong bảng giá)

{=MAX(IF('Bang gia'!$B$2:$B$17<=$B2,'Bang gia'!$B$2:$B$17,""))}

2. Từ đó dùng SUM như sau (Ctrl+Shift+Enter):

E2 {=SUM(('Bang gia'!$A$2:$A$17=$A2)*('Bang gia'!$B$2:$B$17=MAX(IF('Bang gia'!$B$2:$B$17<=$B2,'Bang gia'!$B$2:$B$17,"")))*('Bang gia'!$C$2:$C$17))}

Công thức này không phụ thuộc vào việc dữ liệu tại [Bảng giá] có được Sort hay không!

 

[solomon2211]

Nhìn công thức thì có vẻ là đúng, nhưng thực tế thì không đúng. Xem file đính kèm sẽ rõ (ĐÃ XÓA FILE ĐÍNH KÈM)

 

[cadafi]

Nhìn công thức thì có vẻ là đúng, nhưng thực tế thì không đúng.

Xin lỗi anh, công thức của em sai ở chỗ xác định ngày hiệu lực gần nhất, phải thêm điều kiện mã hàng vào hàm MAX nữa mới đúng! Anh sửa lại hàm Max như sau:

{=MAX(IF(('Standard Cost'!$D$2:$D$6000<=$F2)*('Standard Cost'!$A$2:$A$6000=$A2),'Standard Cost'!$D$2:$D$6000,""))}

Lưu ý: Trong sheet [Purchase] của anh có những mã hàng có [ngày nhập hàng] < [ngày hiệu lực] trong sheet [standard cost], do đó giá của những mặt hàng này phải = 0 mới đúng!
Ví dụ:

Item Code​

|

PO No​

|

PO Date​

|

PO Qty​

|

Req Del​

|

Rec Date​

|

Rec Qty​

|

Unit​

|

U.Price​

|

CCY​

|

MRV​

|

FX​

|

Bảng giá​

|

Kết quả đúng​

|
05LAY004I|

720714​

|

08/07/2008​

|

35200​

|

30/07/2008​

|

04/08/2008​

|

35200​

|sht|

0.455​

|USD|

712011​

|

16494​

|

0​

|

9967.22​

|
05MET001L|

712432​

|

31/10/2008​

|

70​

|

31/10/2008​

|

31/10/2008​

|

70​

|kgs|

60000​

|VND|

712336​

|

1​

|

0​

|

8423.83​

|
02CBR014L|

712085​

|

05/08/2008​

|

2.03​

|

05/08/2008​

|

05/08/2008​

|

2.03​

|kgs|

52.69​

|USD|

712021​

|

16492​

|

0​

|

389716.54​

|
02CYE016L|

712085​

|

05/08/2008​

|

0.4​

|

05/08/2008​

|

05/08/2008​

|

0.4​

|kgs|

41.43​

|USD|

712021​

|

16492​

|

0​

|

384041.81​

|
02CYE017L|

712085​

|

05/08/2008​

|

7.12​

|

05/08/2008​

|

05/08/2008​

|

7.12​

|kgs|

29.11​

|USD|

712021​

|

16492​

|

0​

|

702847.8​

|
02CBU020L|

712230​

|

08/09/2008​

|

1.91​

|

08/09/2008​

|

08/09/2008​

|

1.91​

|kgs|

25.28​

|USD|

712135​

|

16499​

|

0​

|

413271.45​

|
02CGR036L|

712230​

|

08/09/2008​

|

1.09​

|

08/09/2008​

|

08/09/2008​

|

1.09​

|kgs|

13.55​

|USD|

712135​

|

16499​

|

0​

|

373242.57​

|
02CBU017L|

712232​

|

08/09/2008​

|

3.47​

|

08/09/2008​

|

08/09/2008​

|

3.47​

|kgs|

29.05​

|USD|

712137​

|

16499​

|

0​

|

338736.65​

|
02CBU020L|

712233​

|

08/09/2008​

|

0.02​

|

08/09/2008​

|

08/09/2008​

|

0.02​

|kgs|

25.28​

|USD|

712138​

|

16499​

|

0​

|

413271.45​

|
02CMG001L|

712233​

|

08/09/2008​

|

0.02​

|

08/09/2008​

|

08/09/2008​

|

0.02​

|kgs|

33.78​

|USD|

712138​

|

16499​

|

0​

|

307187.61​

|
02CGO010L|

712235​

|

08/09/2008​

|

7.8​

|

08/09/2008​

|

08/09/2008​

|

7.8​

|kgs|

22.79​

|USD|

712140​

|

16499​

|

0​

|

412091.72​

|
02CGR037L|

712235​

|

08/09/2008​

|

0.73​

|

08/09/2008​

|

08/09/2008​

|

0.73​

|kgs|

24.21​

|USD|

712140​

|

16499​

|

0​

|

229890.31​

|
02CPI002L|

712246​

|

12/09/2008​

|

0.47​

|

12/09/2008​

|

12/09/2008​

|

0.47​

|kgs|

16.48​

|USD|

712160​

|

16509​

|

0​

|

252676.16​

|
02CBR015L|

712248​

|

12/09/2008​

|

2.81​

|

12/09/2008​

|

12/09/2008​

|

2.81​

|kgs|

23.79​

|USD|

712162​

|

16509​

|

0​

|

894261.44​

|
02CGR038L|

712269​

|

19/09/2008​

|

0.03​

|

19/09/2008​

|

19/09/2008​

|

0.03​

|kgs|

26.88​

|USD|

712194​

|

16515​

|

0​

|

411055.61​

|
02CGR041L|

712332​

|

09/10/2008​

|

0.03​

|

09/10/2008​

|

09/10/2008​

|

0.03​

|kgs|

16.61​

|USD|

712246​

|

16516​

|

0​

|

421584.24​

|
02CYE018L|

712332​

|

09/10/2008​

|

2.86​

|

09/10/2008​

|

09/10/2008​

|

2.86​

|kgs|

16.8​

|USD|

712246​

|

16516​

|

0​

|

494049.71​

|
02CBU021L|

712333​

|

09/10/2008​

|

15.82​

|

09/10/2008​

|

09/10/2008​

|

15.82​

|kgs|

28.03​

|USD|

712247​

|

16516​

|

0​

|

429275.5​

|
02COR013L|

712333​

|

09/10/2008​

|

4.12​

|

09/10/2008​

|

09/10/2008​

|

4.12​

|kgs|

21.16​

|USD|

712247​

|

16516​

|

0​

|

439422.65​

|
02CBR016L|

712521​

|

21/11/2008​

|

5.19​

|

21/11/2008​

|

22/11/2008​

|

5.19​

|kgs|

23.21​

|USD|

712417​

|

16480​

|

0​

|

403650.58​

|
02COR014L|

712521​

|

21/11/2008​

|

1.5​

|

21/11/2008​

|

22/11/2008​

|

1.5​

|kgs|

28.02​

|USD|

712417​

|

16480​

|

0​

|

359236.55​

|
02CRE023L|

712556​

|

28/11/2008​

|

0.88​

|

28/11/2008​

|

29/11/2008​

|

0.88​

|kgs|

21.34​

|USD|

712451​

|

16483​

|

0​

|

392677.84​

|
05STR002L|

711528​

|

17/03/2008​

|

20​

|

17/03/2008​

|

18/03/2008​

|

20​

|rol|

38.95​

|USD|

711572​

|

16015​

|

0​

|

782250​

|

(sẽ xóa file sau 5 lần download)

 

[ptm0412]

Công thức của Ptm nếu kết hợp với If(IsNA()) sẽ phát hiện lỗi chưa có giá chuẩn tại thời điểm nhập hàng. (tức là lỗi ngày nhập hàng nhỏ hơn ngày có giá hiệu lực nhỏ nhất).
Có thể dùng công thức như sau:
- để báo lỗi:
=IF(ISNA(VLOOKUP(F2;Banggia;5;1));"chưa có giá chuẩn";VLOOKUP(F2;Banggia;5;1))

- Để lấy giá hiện tại:
=IF(ISNA(VLOOKUP(F2;Banggia;5;1));I2*L2;VLOOKUP(F2;Banggia;5;1))

Loại lỗi này dùng cột phụ như Cop_ sẽ không nhận ra, mà lấy giá của mặt hàng ngay trên dòng đầu tiên của mã hàng. Thí dụ tìm 05LAY004I-39689 không ra, sẽ lấy giá của 05LAY003I-39813

 

[Lệnh Hồ Đại Hiệp]

Làm cái VBA cho nó đơn giản các bác ạ.
Nhìn công thức thấy sợ quá.

--CV--