Tìm doanh thu tương ứng với chi phí cho trước

[bomberman211]

Chào cả nhà, nhờ mọi người giúp em công thức để xác định chi phí tương ứng với doanh thu cho trước. Doanh thu của 1 dự án có thể do nhiều phòng ban (A,B,C,D) phụ trách. Chi tiết doanh thu của từng phòng ban thể hiện ở cột G-J. Giờ em cần xác định chi phí ở cột N tương ứng với phòng ban nào rồi ghi kết quả ở cột O.
Hiện em đang dùng công thức để xem phòng nào có doanh thi gần nhất với chi phí. Nhưng công thức còn nhiều hạn chế:
1. dòng thứ 2, 1125 chi phí nên đưa vào phòng A chứ không phải phòng C. (mặc dù về mặt số học thì 430 gần với 1125 hơn là 2086)
2. dòng thứ 4, 5000 chi phí là của phòng B chứ không phải A. Cái này là do khoảng cách từ 5000 đến 0 đúng bằng 5000 đến 10000.
Nhờ mọi người tối ưu công thức giúp em. Em cảm ơn nhiều ạ.

 

[bebo021999]

Bạn nên điền tay kết quả muốn có, bên cạnh công thức cũ của bạn để dễ hình dung.
Dùng thử cái này:
O4:

=LOOKUP(2,1/(G4:J4+N4=AGGREGATE(15,6,(G4:J4+N4)/(G4:J4+N4>0),1)),$G$3:$J$3)

Copy xuống

 

[bomberman211]

Bạn nên điền tay kết quả muốn có, bên cạnh công thức cũ của bạn để dễ hình dung.
Dùng thử cái này:
O4:

=LOOKUP(2,1/(G4:J4+N4=AGGREGATE(15,6,(G4:J4+N4)/(G4:J4+N4>0),1)),$G$3:$J$3)

Copy xuống

Cám ơn bác, công thức của bác đã khắc phục được những cái mà em chưa làm được. Có điều đối với những dự án mà chi phí nhỉnh hơn doanh thu của phòng ban thì công thức chưa chạy được. Nhờ bác hiệu chỉnh giúp em nhé.

 

[bebo021999]

Mình đang dùng quy tắc:
Giá trị chênh lệch nhỏ nhất nhưng >0
Nếu bạn muốn chênh lệch nhỏ nhất >0 hoặc <0 thì vì sao dòng thứ 2:
A=2086.27, C=430, với Invoice: - 1125, thì chênh lệch A= 961.27, C = 695
Lẽ ra phải lấy C, sao lại chọn A?

 

[bomberman211]

Mình đang dùng quy tắc:
Giá trị chênh lệch nhỏ nhất nhưng >0
Nếu bạn muốn chênh lệch nhỏ nhất >0 hoặc <0 thì vì sao dòng thứ 2:
A=2086.27, C=430, với Invoice: - 1125, thì chênh lệch A= 961.27, C = 695
Lẽ ra phải lấy C, sao lại chọn A?

Đúng là hơi rối thật. Vậy bác giúp em công thức để khi doanh thu nhỏ hơn chi phí thì lấy doanh thu nhân 120% rồi mới áp dụng quy tắc trên có được không ạ? Như vậy sẽ không bỏ sót các dự án bị lỗ nhẹ nhưng cũng tránh được trường hợp chi phí gấp đôi doanh thu như ở dòng thứ 2 kia.

 

[bebo021999]

Nếu *1.2 thì dòng 5 ra kết quả hơi lạ nhé

=LOOKUP(2,1/(G4:J4=AGGREGATE(15,6,G4:J4/(IF(G4:J4<ABS(N4),G4:J4*1.2,G4:J4)+N4>0),1)),$G$3:$J$3)

 

[bomberman211]

Nếu *1.2 thì dòng 5 ra kết quả hơi lạ nhé

=LOOKUP(2,1/(G4:J4=AGGREGATE(15,6,G4:J4/(IF(G4:J4<ABS(N4),G4:J4*1.2,G4:J4)+N4>0),1)),$G$3:$J$3)

Cảm ơn bác rất nhiều. Công thức chạy đúng ý em rồi ạ.