Chuyên mục GIẢI THÍCH CÔNG THỨC

[tueyennhi]

Offset sai. Đọc lại hàm Offset().

=OFFSET(Part,0,4)

Mình mới đọc về Offset ở chủ đề http://www.giaiphapexcel.com/diendan/threads/hàm-offset-và-validation.7188/
Trong đó cú pháp có nêu như sau:

Cú pháp:
=OFFSET(reference, rows, cols, height, width)

Với reference là vùng tham chiếu, rows là vị trí dòng bắt đầu so với vùng tham chiếu, cols là vị trí cột bắt đầu so với vùng tham chiếu, height là số dòng của vùng tham chiếu trả về, và width là số cột của vùng tham chiếu trả về.

Vậy befaint có thể giải thích rõ hơn là sai ở đâu được không?

Mình áp dụng với tính trung bình:

Công thức 3: =IFERROR(AVERAGEIF(Part,AS21,OFFSET(Part,0,1,5000,1)),0)

Thì ra kết quả chính xác nhưng không hiểu sao với hàm Coutifs thì trả về Value.

 

[huuthang_bd]

Mình sử dụng công thức sau:

Công thức 1: =COUNTIFS(Part,AS21,OFFSET(Part,0,4,5000,1),">=52") hàm trả về Value. Đếm có điều kiện: giá trị AS21 tại với vùng name Part kết hợp vế điều kiện 2 đếm có điều kiện giá trị >=52 với vùng dữ liệu có số dòng bằng name Part (5000 dòng), lui sang bên phải 4 cột và số cột là 1.

Công thức 2: =COUNTIF(OFFSET(Part,1,4,5000,1),">=52") thì trả kết quả về một con số đúng.

Vậy tại sao công thức 1 không ra kết quả

Tôi nghĩ vùng dữ liệu được đặt tên là Part không phải có 5000 dòng và 1 cột.

 

[quocgiacan]

Mình mới đọc về Offset ở chủ đề http://www.giaiphapexcel.com/diendan/threads/hàm-offset-và-validation.7188/
Trong đó cú pháp có nêu như sau:

Cú pháp:
=OFFSET(reference, rows, cols, height, width)

Với reference là vùng tham chiếu, rows là vị trí dòng bắt đầu so với vùng tham chiếu, cols là vị trí cột bắt đầu so với vùng tham chiếu, height là số dòng của vùng tham chiếu trả về, và width là số cột của vùng tham chiếu trả về.

Vậy befaint có thể giải thích rõ hơn là sai ở đâu được không?

COUNTIFS(Part,AS21,OFFSET(Part,0,4,5000,1),">=52")
'Part' và Vùng trả về của OFFSET(Part,0,4,5000,1) phải đồng nhau về số cột và hàng.
Chắc chắn rằng name Part của bạn <> Vùng trả về có {5000hàng, 1 cột}

 

[tueyennhi]

Tôi nghĩ vùng dữ liệu được đặt tên là Part không phải có 5000 dòng và 1 cột.

Đúng như bạn nói

Mình sửa lại thành như vậy:
Công thức 4: =COUNTIFS(Part,AS21,OFFSET(Part,0,4,4981,1),">=52")

Lý do là vì name part: ='Reporst all'!$E$20:$E$5000, thực tế về số dòng thì nó chỉ còn là 4981

Cảm ơn tất cả mọi người!

 

[huuthang_bd]

Đúng như bạn nói

Mình sửa lại thành như vậy:
Công thức 4: =COUNTIFS(Part,AS21,OFFSET(Part,0,4,4981,1),">=52")

Lý do là vì name part: ='Reporst all'!$E$20:$E$5000, thực tế về số dòng thì nó chỉ còn là 4981

Cảm ơn tất cả mọi người!

Tham số height và width của hàm OFFSET có thể bỏ qua. Nếu được bỏ qua thì vùng kết quả sẽ có cùng số dòng và/hoặc số cột với vùng reference.
Bạn nên làm theo bài #682. Làm như bạn lỡ chèn/xóa dòng là lỗi ngay.

 

[tueyennhi]

Tham số height và width của hàm OFFSET có thể bỏ qua. Nếu được bỏ qua thì vùng kết quả sẽ có cùng số dòng và/hoặc số cột với vùng reference.
Bạn nên làm theo bài #682. Làm như bạn lỡ chèn/xóa dòng là lỗi ngay.

Ồ vậy à, cảm ơn bạn để mình thử.

 

[pnbaotran0406]

Chào mọi người em đang có bài này có lẻ ko khó với mọi người nhưng đối với em thì sâu não vô cùng
đề: nhận xét: nếu thời gian lưu kho dưới 30 thì ghi Thực phẩm; nếu từ 30 ngày đến 200 ngày thì ghi Vải còn lại ghi Sắt-thép
em ghi là =IF(D3>200;"Sắt-Thép";if(and(D3<=200;D3>=3);"Vải";"Thực phẩm))
Cái đầu nó ra Sắt-Thép đúng nhưng mấy cái sau cũng Sắt-thép lun thì em thấy sai thảm hại rồi
mong mọi người giúp em

 

[dhn46]

Chào mọi người em đang có bài này có lẻ ko khó với mọi người nhưng đối với em thì sâu não vô cùng
đề: nhận xét: nếu thời gian lưu kho dưới 30 thì ghi Thực phẩm; nếu từ 30 ngày đến 200 ngày thì ghi Vải còn lại ghi Sắt-thép
em ghi là =IF(D3>200;"Sắt-Thép";if(and(D3<=200;D3>=3);"Vải";"Thực phẩm))
Cái đầu nó ra Sắt-Thép đúng nhưng mấy cái sau cũng Sắt-thép lun thì em thấy sai thảm hại rồi
mong mọi người giúp em

If nó thực hiện phép so sánh từ trai qua phải so với công thức If.

=> Nếu đặt các điều kiện số được sắp xếp tuần tự thì bạn sẽ tự nhận ra điểm vấn đề

----------------------200------------------30------------------
      Sắt thép        |          Vải        |        Thực phẩm

 

[lehuong1409]

Chào mọi người em đang có bài này có lẻ ko khó với mọi người nhưng đối với em thì sâu não vô cùng
đề: nhận xét: nếu thời gian lưu kho dưới 30 thì ghi Thực phẩm; nếu từ 30 ngày đến 200 ngày thì ghi Vải còn lại ghi Sắt-thép
em ghi là =IF(D3>200;"Sắt-Thép";if(and(D3<=200;D3>=3);"Vải";"Thực phẩm))
Cái đầu nó ra Sắt-Thép đúng nhưng mấy cái sau cũng Sắt-thép lun thì em thấy sai thảm hại rồi
mong mọi người giúp em

mình thấy nó có sai gì đâu nhỉ?

 

[dhn46]

mình thấy nó có sai gì đâu nhỉ?

Sai ở cái 3 và 30 đó bạn.

Còn cái chưa hợp lý tại hàm If nữa

 

[TheThienChu]

Chào mọi người em đang có bài này có lẻ ko khó với mọi người nhưng đối với em thì sâu não vô cùng
đề: nhận xét: nếu thời gian lưu kho dưới 30 thì ghi Thực phẩm; nếu từ 30 ngày đến 200 ngày thì ghi Vải còn lại ghi Sắt-thép
em ghi là =IF(D3>200;"Sắt-Thép";if(and(D3<=200;D3>=3);"Vải";"Thực phẩm))
Cái đầu nó ra Sắt-Thép đúng nhưng mấy cái sau cũng Sắt-thép lun thì em thấy sai thảm hại rồi
mong mọi người giúp em

Thử sửa thành thế này bạn xem thử kết quả ra sao
=IF(D3>200;"Sắt-Thép";IF(D3>=30;"Vải";"Thực phẩm"))

 

[lehuong1409]

Sai ở cái 3 và 30 đó bạn.

Còn cái chưa hợp lý tại hàm If nữa

do em chưa để ý đến con số nhưng về logic em thấy không có vấn đề gì ạ có chăng là thừa ở hàm And thôi ạ. Ý em thắc mắc là sao kết quả của bạn ấy ra hết là sắt thép ý ạ. Có thể bạn ấy làm vào bài thì công thức bị lỗi ở đâu đó chứ công thức viết ra em không thấy vấn đề đó

 

[pnbaotran0406]

Thử sửa thành thế này bạn xem thử kết quả ra sao
=IF(D3>200;"Sắt-Thép";IF(D3>=30;"Vải";"Thực phẩm"))

em thử cái đó rồi nhưng vẫn ko đc ạ

 

[pnbaotran0406]

Sai ở cái 3 và 30 đó bạn.

Còn cái chưa hợp lý tại hàm If nữa

hàm if sai gì vậy ạ

 

[pnbaotran0406]

em phải ghi như thế nào mới đúng đây ((((

 

[dhn46]

hàm if sai gì vậy ạ

Bạn sai tưng đây thứ:
1/ Không đọc kỹ từng bài hướng dẫn
2/ Khi áp dụng công thức góp ý không tự mình so sánh với công thức cũ xem khác gì, tại sao sai
3/ Khi áp dụng hướng dẫn vẫn sai thay vì việc gào lên thì sao không gửi những gì bạn đã làm qua file thực thế để mọi người tư vấn

Sử dụng excel là sử dụng tính logic thế nên hỏi excel cũng cần logic trong tư duy!

 

[TheThienChu]

em thử cái đó rồi nhưng vẫn ko đc ạ

Gửi file có chỗ "ko đc" lên để giải quyết đi bạn

 

[pnbaotran0406]

dạ do em mới lập nick nên cũng còn chưa biết nhiều điều nên đừng nói những lời nặng nề thế
với lại em cũng thử tùm lum công thức rồi mới kêu la vậy đó ạ
Đây là file, mong mọi người giúp đỡ em

 

[Gió Đông]

dạ do em mới lập nick nên cũng còn chưa biết nhiều điều nên đừng nói những lời nặng nề thế
với lại em cũng thử tùm lum công thức rồi mới kêu la vậy đó ạ
Đây là file, mong mọi người giúp đỡ em

Đề thì nên tự làm và hỏi thầy cô dậy?
Tạm thử thế này đi
Cột D (Lưu kho) hiện giờ sd công thức
=RIGHT(B3,3)
là trở thành KÝ TỰ --> nên kết quả công thức IF là sắt thép hết

Đổi công thức đó thành
=1*RIGHT(B3,3)
-----------------------
sau đó thay các công thức IF trên để thấy hợp lý, cần học đọc lại (1) hàm IF cơ bản, (2) phân biệt dữ liệu số / ký tự số trong dữ liệu Excel

 

[TheThienChu]

dạ do em mới lập nick nên cũng còn chưa biết nhiều điều nên đừng nói những lời nặng nề thế
với lại em cũng thử tùm lum công thức rồi mới kêu la vậy đó ạ
Đây là file, mong mọi người giúp đỡ em

Công tại bài 693: Sửa D3 -> D3*1 là được.
Công thức lỗi là do giá trị tại cột D là chuỗi, điều kiện so sánh là số, dẫn đến kết quả so sánh bị sai