Công thức với hàm if bị lỗi và có cách nào làm gọn lại công thức quá dài

[quynhace]

1/- Mình đang gặp phải vấn đề về công thức với hàm if, công thức của mình như sau:
=IF(OR(F4=F3,F4="",AO4="",AY4<1),"",
if(and(AP4>AO4,AR4="",AT4=""),((AV4*(AP4-AO4)+(-AX4*AY4))*$AZ$3,
if(and(AR4>AO4,AT4=""),((AS4*(AR4-AO4))+(-AX4*AY4))*$AZ$3,
if(and(AP4>AO4,AR4>AO4,AT4=""),((AQ4*(AP4-AO4)+(AS4*(AR4-AO4))+(-AX4*AY4))*$AZ$3,
if(AT4>AO4,AU4*(AT4-AO4)*$AZ$3,
if(and(AR4>AO4,AT4>AO4),((AS4*(AR4-AO4))+(AU4*(AT4-AO4)))*$AZ$3,
if(and(AP4>AO4,AR4>AO4,AT4>AO4),((AQ4*(AP4-AO4))+(AS4*(AR4-AO4))+(AU4*(AT4-AO4)))*$AZ$3,-AX4*AY4*$AZ$3)))))))))
kết quả bị trả về là #VALUE!

Mọi người chỉ giùm mình chỗ sai với.

2/- Mình thấy công thức này của mình quá dài, vậy có hàm nào có thể ngắn gọn và đơn giản hơn không???

Mong các bạn trong diễn đàn giúp mình gỡ rối 2 vấn đề này.
Chúc các bạn cuối tuần vui vẻ và hạnh phúc!

 

[leonguyenz]

1/- Mình đang gặp phải vấn đề về công thức với hàm if, công thức của mình như sau:
kết quả bị trả về là #VALUE!
Mọi người chỉ giùm mình chỗ sai với.
2/- Mình thấy công thức này của mình quá dài, vậy có hàm nào có thể ngắn gọn và đơn giản hơn không???
Mong các bạn trong diễn đàn giúp mình gỡ rối 2 vấn đề này.
Chúc các bạn cuối tuần vui vẻ và hạnh phúc!

Cấu trúc hàm trong công thức không sai nhưng bạn chưa đặt hết điều kiện cho các ô rỗng, các toán tử với ô rỗng sẽ bị lỗi #VALUE.
Các điều kiện trong IF nếu có điều kiện nào chung thì nên gom lại, đặt điều kiện hợp lý sẽ rút ngắn công thức và tăng tốc độ tính toán. Bạn muốn nhờ mọi người rút ngắn thì gửi file lên (chỉ cần để vài ba dòng ví dụ thôi).

 

[quynhace]

Mình vừa bị ốm 1 thời gian dài nên không vào diễn đàn được, mong Leonguyenz và mọi người thông cảm cho mình nhé.
Mình xin gửi file của mình lên đây nhờ mọi người vào chỉ dẫn cho mình:
1/- Công thức của mình bị sai ở chỗ nào?
2/- Cách rút gọn lại công thức.
3/- Tại cột N: nếu như mình đặt công thức tại 1 cell mà có ra kết quả dạng Text và số (VD: Thiếu 27250) thì làm như thế nào để có thể ra kết quả là Thiếu 27,250 được không?4/- Tại cell N1 (chứa kết quả subtotal từ dòng N4 : N200) sẽ không cộng những cell có chứa Thiếu ... nữa
Cảm ơn các bạn nhiều.

 

[leonguyenz]

Mình vừa bị ốm 1 thời gian dài nên không vào diễn đàn được, mong Leonguyenz và mọi người thông cảm cho mình nhé.
Mình xin gửi file của mình lên đây nhờ mọi người vào chỉ dẫn cho mình:
1/- Công thức của mình bị sai ở chỗ nào?
2/- Cách rút gọn lại công thức.
3/- Tại cột N: nếu như mình đặt công thức tại 1 cell mà có ra kết quả dạng Text và số (VD: Thiếu 27250) thì làm như thế nào để có thể ra kết quả là Thiếu 27,250 được không?4/- Tại cell N1 (chứa kết quả subtotal từ dòng N4 : N200) sẽ không cộng những cell có chứa Thiếu ... nữa
Cảm ơn các bạn nhiều.

Vấn đề rút gọn công thức: vẫn đang ngâm cứu cách tính, bạn có thể cho kết quả đúng tại vài ô được không? Còn vấn đề định dạng tính sau.
Thử sửa đúng cấu trúc hàm IF, kết quả P4 là: 4,514,611 (chắc là chưa đúng).

=IF(OR(A4=A3,A4="",E4="",O4<1),"",IF(AND(F4>E4,H4="",J4=""),(L4*(F4-E4)+(-N4*O4))*$P$3,IF(AND(H4>E4,J4=""),((I4*(H4-E4))+(-N4*O4))*$P$3,IF(AND(F4>E4,H4>E4,J4=""),(G4*(F4-E4)+(I4*(H4-E4))+(-N4*O4))*$P$3,IF(J4>E4,K4*(J4-E4)*$P$3,IF(AND(F4<E4,H4>E4,J4>E4),((I4*(H4-E4))+(K4*(J4-E4)))*$P$3,IF(AND(F4>E4,H4>E4,J4>E4),((G4*(F4-E4))+(I4*(H4-E4))+(K4*(J4-E4)))*$P$3,-N4*O4*$P$3)))))))

Đặt trường hợp công thức trên đúng, thì phần tính còn lại rút gọn như sau:

=IF(AND(F4>E4,H4="",J4=""),L4*(F4-E4)-N4*O4,IF(AND(H4>E4,J4=""),I4*(H4-E4)-N4*O4,IF(AND(F4>E4,H4>E4,J4=""),G4*(F4-E4)+I4*(H4-E4)-N4*O4,IF(J4>E4,K4*(J4-E4),IF(AND(F4<E4,H4>E4,J4>E4),I4*(H4-E4)+K4*(J4-E4),IF(AND(F4>E4,H4>E4,J4>E4),G4*(F4-E4)+I4*(H4-E4)+K4*(J4-E4),-N4*O4))))))*$P$3

Vẫn chưa hiểu cách tính ...

 

[quynhace]

Bạn Leonguyenz ơi, mình gửi lại file có kết quả đúng bên cạnh (với điều kiện là giá trị của ngày hôm nay, sang ngày mai là con số lại khác rồi).
Trong này mình cũng add công thức của bạn đưa ra, nhưng nó sẽ không có kết quả đúng.
Bạn xem lại giúp mình nhé.
Chúc bạn và các thành viên của diễn đàn ngày cuối tuần vui vẻ và hạnh phúc!

 

[leonguyenz]

Bạn Leonguyenz ơi, mình gửi lại file có kết quả đúng bên cạnh (với điều kiện là giá trị của ngày hôm nay, sang ngày mai là con số lại khác rồi).
Trong này mình cũng add công thức của bạn đưa ra, nhưng nó sẽ không có kết quả đúng.
Bạn xem lại giúp mình nhé.
Chúc bạn và các thành viên của diễn đàn ngày cuối tuần vui vẻ và hạnh phúc!

Các đơn khác bạn đặt công thức trong cột H, J (=IF(AND(F14<>0,M14<99%),0,"")) là mục đích gì vậy bạn?
P4 đúng kết quả gõ tay của bạn, từ đơn thứ 2 không đúng, một công thức nhiều IF như vậy tôi chỉ sửa được cấu trúc từng hàm trong công thức của bạn thôi.
Kết quả tôi sẽ sửa lại TODAY() thành 20/09/2013 (vì file bạn gửi ngày này), bạn nên ghi rõ cách tính.
Không biết nó sai ở chỗ nào, tôi sửa luôn công thức cả 3 cột.

 

[quynhace]

Các đơn khác bạn đặt công thức trong cột H, J (=IF(AND(F14<>0,M14<99%),0,"")) là mục đích gì vậy bạn?
P4 đúng kết quả gõ tay của bạn, từ đơn thứ 2 không đúng, một công thức nhiều IF như vậy tôi chỉ sửa được cấu trúc từng hàm trong công thức của bạn thôi.
Kết quả tôi sẽ sửa lại TODAY() thành 20/09/2013 (vì file bạn gửi ngày này), bạn nên ghi rõ cách tính.
Không biết nó sai ở chỗ nào, tôi sửa luôn công thức cả 3 cột.

Bạn ơi, mình đặt công thức tại cột F, H và J chỉ là để nhắc nhở khi khách hàng chưa thanh toán đủ tiền hàng thì sẽ có màu vàng thôi mà (tại các cột này mình đặt Conditional Formatting).
Mình gửi lai file của mình, trong đó mình đã làm các ví dụ với 3 đơn hàng có 3 kiểu thanh toán khác nhau để bạn hiểu rõ hơn.
Từ mấy hôm nay, mình cứ "vò đầu bứt tai" với cột P (tiền phải trả cho việc thanh toán chậm) này mà không ra nổi. Bạn giúp mình với nhé.
Cảm ơn bạn đã nhiệt tình giúp đỡ mình.

 

[quynhace]

Mọi người ơi vào giúp mình với!

 

[leonguyenz]

Bạn ơi, mình đặt công thức tại cột F, H và J chỉ là để nhắc nhở khi khách hàng chưa thanh toán đủ tiền hàng thì sẽ có màu vàng thôi mà (tại các cột này mình đặt Conditional Formatting).
Mình gửi lai file của mình, trong đó mình đã làm các ví dụ với 3 đơn hàng có 3 kiểu thanh toán khác nhau để bạn hiểu rõ hơn.
Từ mấy hôm nay, mình cứ "vò đầu bứt tai" với cột P (tiền phải trả cho việc thanh toán chậm) này mà không ra nổi. Bạn giúp mình với nhé.
Cảm ơn bạn đã nhiệt tình giúp đỡ mình.

Bạn bỏ công thức tại các cột F, H, J đi, đặt nhắc nhở bằng cách khác, còn công thức đặt tại P4 như sau:

=IF(COUNTIF(A$4:A4,A4)=1,((F4>E4)*G4*(F4-E4)+(H4>E4)*I4*(H4-E4)+(J4>E4)*K4*(J4-E4)+N4*(-O4))*$P$3,"")

Đúng với 3 ví dụ bạn giải thích, nếu còn sai trường hợp nào thì bạn giải thích trường hợp đó lại.

 

[leonguyenz]

SUMIF tại cột L có vấn đề.
Cột A các mã đơn hàng cũng vậy, khi COUNTIF có vấn đề giữa mã 02-13 và 01-02 (dòng 16,17,18). Chưa biết tại sao.
Vấn đề ở chỗ đó.

 

[quynhace]

SUMIF tại cột L có vấn đề.
Cột A các mã đơn hàng cũng vậy, khi COUNTIF có vấn đề giữa mã 02-13 và 01-02 (dòng 16,17,18). Chưa biết tại sao.
Vấn đề ở chỗ đó.

Bạn Leonguyenz ơi, mình đã hiểu lỗi ở chỗ nào rồi.
Sau khi áp công thức bạn cho vào bảng thì tất cả các dòng đều cho ra kết quả đúng như mong muốn, chỉ trừ duy nhất dòng 14 là ra kết quả #VALUE! ==> Lỗi là ở chỗ:
1/- Nếu như KH mới hoặc chỉ thanh toán 1 lần (cột H và cột J để trống) thì sẽ ra kết quả là #VALUE!.
2/- Nếu như khách hàng chỉ thanh toán đến lần thứ 2 là hết (thanh toán đủ 100%) thì cũng bị ra kết quả là #VALUE!
Mình đã thử với các dòng khác thì thấy cũng bị kết quả như vậy.
Bạn giúp mình tiếp với nhé. Mình cảm ơn bạn nhiều nhiều lắm.

 

[quynhace]

Bạn Leonguyenz ơi, bạn bớt chút thời gian vào giúp mình nốt với. Đa tạ bạn nhiều.
Mọi người cùng giúp mình với nhé.

 

[leonguyenz]

Bạn Leonguyenz ơi, bạn bớt chút thời gian vào giúp mình nốt với. Đa tạ bạn nhiều.
Mọi người cùng giúp mình với nhé.

Công thức lỗi tại P14 do H14 ="". Các cột F, H, J không nên đặt công thức để báo hiệu gì đó. Có thể dùng Conditional Formatting để báo hiệu (tô màu) trực tiếp, công thức trong CF: =AND($F4<>"",E4<>"",F4="")
Bạn chú ý mã hàng 02-13 nha, tôi sửa nó lại thành 02-13a để xem kết quả có đúng hay không.

 

[quynhace]

Kết quả đúng tuyệt đối luôn rồi Leonguyenz à, cảm ơn bạn nhiều nhé.
Còn vấn đề thì mình đang nghiên cứu vì mình thấy nó sẽ không đúng khi hàng đã giao:
1/- Chưa thanh toán lần nào thì ở cột F không thấy có báo màu, hơn nữa ô ở cột H hoặc cột J lại có màu vàng;
2/- Chỉ thanh toán 1 lần duy nhất (100% giá trị cả đơn hàng) thì vẫn hiện màu ở cột H hoặc cột J;
3/- những dòng có cùng số đơn hàng thì có những dòng tự hiện màu cũng ở cột H hoặc cột J;
4/- ...

Tại các cột F - H - J mình chỉ muốn hiện màu khi:
1/- cột H sẽ có màu ==> cột E <>"" và cột M=0%
2/- cột H sẽ có màu ==> cột F <>"" và cột M < 99%
3/- cột J sẽ có màu ==> cột F & cột H <>"" và cột M < 99%

Bạn chỉ cho mình cách làm trong CF được không?
Mình cũng đã thứ mấy cách rồi nhưng không được nhưng nhớ là chưa vào báo cho bạn biết kết quả ở cột P nên quay vào đây báo cho bạn biết trước đã.
Cảm ơn bạn nhiều lắm, công thức của bạn rất ngắn, gọn nữa.

 

[leonguyenz]

Đặt công thức trong CF: Chịn vùng cần tô màu, Tab Home > Conditional Formatting > New Rule > Use a formula to ... Phần đặt công thức: =AND(E4<>"",F4="",$M4<99%) Sau đó nhấn Format... chọn Fill > chọn màu cần Format > OK.

 

[quynhace]

Chuẩn luôn bạn ơi! Tuyệt thật!
Chỉ thay đổi 1 ô điều kiện thôi mà ....
Đúng là càng đi sâu vào tìm hiểu và thực hành vào công việc thì mình càng thấy Excel đúng là thiên biến vạn hóa thật!!! Mình còn phải học dài dài, nghiên cứu dài dài thì mới theo kịp các bạn bây giờ được...
Xin chân thành cảm ơn bạn Leonguyenz nhé.