Chuyên mục GIẢI THÍCH CÔNG THỨC (3 người xem)

[mhieuit]

Dear GPE,
Mình có CT bên dưới nhưng nó hơi dài dòng, có cách nào rút gọn lại không? Thanks

=IF(I5=1,SUMIF(J:J,J5,M:M)+SUMIF(J:J,J5,P)+SUMIF(J:J,J5,S:S),0)

 

[Hoàng Trọng Nghĩa]

Dear GPE,
Mình có CT bên dưới nhưng nó hơi dài dòng, có cách nào rút gọn lại không? Thanks

=IF(I5=1,SUMIF(J:J,J5,M:M)+SUMIF(J:J,J5,P)+SUMIF(J:J,J5,S:S),0)

Tôi nghĩ như thế là ngắn gọn lắm rồi đó bạn, tuy nhiên dùng cả cột thế kia làm công thức tính chậm một chút.

 

[leonguyenz]

Dear GPE,
Mình có CT bên dưới nhưng nó hơi dài dòng, có cách nào rút gọn lại không? Thanks

=IF(I5=1,SUMIF(J:J,J5,M:M)+SUMIF(J:J,J5,P)+SUMIF(J:J,J5,S:S),0)

Nếu dữ liệu không lớn thì SUMPRODUCT cũng là 1 giải pháp:
=SUMPRODUCT((vùng cột I = 1)*(vùng cột J = j5)*(vùng cột M + vùng cột P + vùng cột S))
SUMIF hoặc SUMIFS có tốc độ tính toán nhanh hơn SUMPRODUCT.

 

[Hoàng Trọng Nghĩa]

Nếu dữ liệu không lớn thì SUMPRODUCT cũng là 1 giải pháp:
=SUMPRODUCT((vùng cột I = 1)*(vùng cột J = j5)*(vùng cột M + vùng cột P + vùng cột S))
SUMIF hoặc SUMIFS có tốc độ tính toán nhanh hơn SUMPRODUCT.

Dữ liệu dù không lớn, nhưng chơi cả cột thì cũng thành quá lớn rồi em trai. Chơi SumIf cho nó lành.

 

[mhieuit]

Tôi nghĩ như thế là ngắn gọn lắm rồi đó bạn, tuy nhiên dùng cả cột thế kia làm công thức tính chậm một chút.

Hi anh,
Khi em áp dụng thực tế em sẽ giới hạn địa chỉ ô, nhưng vì dùng 3 sumif, em dự định làm 1 sumifs nhưng chưa biết đặt dk thế nào nữa.

 

[khuongvietphong]

Hi anh,
Khi em áp dụng thực tế em sẽ giới hạn địa chỉ ô, nhưng vì dùng 3 sumif, em dự định làm 1 sumifs nhưng chưa biết đặt dk thế nào nữa.

Bạn có thể up cà File của bạn lên để mọi người cùng xem

 

[Hoàng Trọng Nghĩa]

Hi anh,
Khi em áp dụng thực tế em sẽ giới hạn địa chỉ ô, nhưng vì dùng 3 sumif, em dự định làm 1 sumifs nhưng chưa biết đặt dk thế nào nữa.

Sao được bạn, ở đây SUMIFS cho một cột SUM, còn lại là nhiều điều kiện trong các vùng điều kiện, còn bạn thì điều kiện chung, nhưng các cột SUM khác nhau. Bạn không nên thay đổi thêm nữa.

 

[THANHHT83]

Chào các bạn!
Mình có một bảng tính muốn nhờ các bạn chỉ giáo giúp làm sao rút gọn công thức lại hoặc có thể sử dụng công thức khác mà vẫn cho kết quả như yêu cầu đặt ra. Kiến thức về Excel của mình còn non kém nên mong nhận được sự chỉ bảo của các bạn.
Bài toán của mình là quản lý vật tư của một tổ khoan dựa trên định mức, khu vực khoan và số mét khoan làm được. Trong đó E3 là khu vực khoan, E5 là số mét khoan thi công được và E6 là số mét khoan qua lò (định mức khoan qua lò = định mức khoan thường * 1.3). Trong công thức mình đang làm nó phải lồng nhiều hàm if quá (vì kiến thức eo hẹp ) Vậy rất mong nhận được sự trợ giúp từ các ban. Cảm ơn nhiều!

 

[thangbithu]

Chào các bạn!
Mình có một bảng tính muốn nhờ các bạn chỉ giáo giúp làm sao rút gọn công thức lại hoặc có thể sử dụng công thức khác mà vẫn cho kết quả như yêu cầu đặt ra. Kiến thức về Excel của mình còn non kém nên mong nhận được sự chỉ bảo của các bạn.
Bài toán của mình là quản lý vật tư của một tổ khoan dựa trên định mức, khu vực khoan và số mét khoan làm được. Trong đó E3 là khu vực khoan, E5 là số mét khoan thi công được và E6 là số mét khoan qua lò (định mức khoan qua lò = định mức khoan thường * 1.3). Trong công thức mình đang làm nó phải lồng nhiều hàm if quá (vì kiến thức eo hẹp ) Vậy rất mong nhận được sự trợ giúp từ các ban. Cảm ơn nhiều!

Mình chỉnh công thức lại cho bạn rồi, bạn xem nhé, 1 số chỗ kết quả của bạn bị sai đấy.

 

[THANHHT83]

Cảm ơn bạn đã gúp đỡ! Mình đang nghiên cứu công thức bạn viết vì mình cũng chưa thuộc nhiều hàm. Tuy nhiên có đoạn này "--LEFT(ĐMVTK!$D$5:$L$5)" mình vẫn chưa tìm ra "--" trước hàm left có ý nghĩa gì. Mong bạn chỉ giáo!

 

[manuchungtinh]

Cảm ơn bạn đã gúp đỡ! Mình đang nghiên cứu công thức bạn viết vì mình cũng chưa thuộc nhiều hàm. Tuy nhiên có đoạn này "--LEFT(ĐMVTK!$D$5:$L$5)" mình vẫn chưa tìm ra "--" trước hàm left có ý nghĩa gì. Mong bạn chỉ giáo!

Dấu -- nó thay cho hàm Value() trong excel đó bạn ạ.

 

[thangbithu]

Cảm ơn bạn đã gúp đỡ! Mình đang nghiên cứu công thức bạn viết vì mình cũng chưa thuộc nhiều hàm. Tuy nhiên có đoạn này "--LEFT(ĐMVTK!$D$5:$L$5)" mình vẫn chưa tìm ra "--" trước hàm left có ý nghĩa gì. Mong bạn chỉ giáo!

khi lấy bên trái 1 ký tự trong dãy ô $D$5:$L$5 thì nó ra các ký tự 1, 2, ..., 9 nhưng đây chỉ là text thôi, đặt dấu "--" đằng trước để các ký tự này biến thành số, cộng trừ nhân chia được. "--LEFT(ĐMVTK!$D$5:$L$5)" nằm trong tham số cột của hàng index, nó sẽ chỉ ra cột thứ mấy tương ứng.

 

[THANHHT83]

khi lấy bên trái 1 ký tự trong dãy ô $D$5:$L$5 thì nó ra các ký tự 1, 2, ..., 9 nhưng đây chỉ là text thôi, đặt dấu "--" đằng trước để các ký tự này biến thành số, cộng trừ nhân chia được. "--LEFT(ĐMVTK!$D$5:$L$5)" nằm trong tham số cột của hàng index, nó sẽ chỉ ra cột thứ mấy tương ứng.

Chào bạn!
Hình như có một chút lỗi nào đó trong công thức bạn viết giúp mình. Khi mình thêm phần khối lượng khoan qua lò (E6) các kết quả đều báo lỗi.

 

[thangbithu]

Chào bạn!
Hình như có một chút lỗi nào đó trong công thức bạn viết giúp mình. Khi mình thêm phần khối lượng khoan qua lò (E6) các kết quả đều báo lỗi.

Ok, mình biết tại sao rồi, từ từ mỉnh chỉnh lại công thức, hiện mình đang bận

 

[thangbithu]

Chào bạn!
Hình như có một chút lỗi nào đó trong công thức bạn viết giúp mình. Khi mình thêm phần khối lượng khoan qua lò (E6) các kết quả đều báo lỗi.

Bạn kiểm tra lại giúp mình xem đúng chưa nhé.

 

[THANHHT83]

Bạn kiểm tra lại giúp mình xem đúng chưa nhé.

Chào bạn!
Cảm ơn bạn đã giúp mình giải quyết bài toán. Tuy nhiên mình có 2 vấn đề muốn bạn giúp đỡ.
1. Hình như có chút hiểu lẩm trong công thức bạn viết khi cột cần lấy phải nằm ở bên phải cấp chiều sâu. Ý mình ở đây là phải dùng hàm RIGHT thay vì hàm LEFT vì như mình ví dụ luôn trong bảng (phần bôi vàng) mình để chiều sâu khoan được là 450 khi đó định mức cần lấy là cấp chiều sâu 0-500 cho kết quả là 2835 chứ ko phải là 2475 như kết quả đưa ra.
2. Về hàm LEFT mình chưa hiểu lắm mình đã lấy ví dụ bên dưới bạn có thể giải thích giúp mình tại sao cho kết quả bằng 5 và tại sao lại báo lỗi được không?
Mong bạn giúp đỡ mình! Cảm ơn bạn nhiều!

 

[thangbithu]

Chào bạn!
Cảm ơn bạn đã giúp mình giải quyết bài toán. Tuy nhiên mình có 2 vấn đề muốn bạn giúp đỡ.
1. Hình như có chút hiểu lẩm trong công thức bạn viết khi cột cần lấy phải nằm ở bên phải cấp chiều sâu. Ý mình ở đây là phải dùng hàm RIGHT thay vì hàm LEFT vì như mình ví dụ luôn trong bảng (phần bôi vàng) mình để chiều sâu khoan được là 450 khi đó định mức cần lấy là cấp chiều sâu 0-500 cho kết quả là 2835 chứ ko phải là 2475 như kết quả đưa ra.

Là do bạn mô tả không kỹ, mình hiểu như thế này có đúng không nhé:
Nếu TK01!E5 = 450 thì sẽ lấy ở cột I
Nếu TK01!E5 = 499 thì sẽ lấy ở cột I
Nếu TK01!E5 = 501 thì sẽ lấy ở cột J
Nếu TK01!E5 = 599 thì sẽ lấy ở cột J
????

2. Về hàm LEFT mình chưa hiểu lắm mình đã lấy ví dụ bên dưới bạn có thể giải thích giúp mình tại sao cho kết quả bằng 5 và tại sao lại báo lỗi được không?
Mong bạn giúp đỡ mình! Cảm ơn bạn nhiều![/QUOTE]

- Bình thường thì hàm left sẽ ở dạng như sau: left(ĐMVTK!E5;1)=1 or left(ĐMVTK!E5)=1 nếu lấy 1 ký tự thì không cần tham số 1 phía sau, nếu lấy 2 ký tự trở lên thì bắt buộc phải có tham số phía sau, ví dụ: left(ĐMVTK!E5;2)=15

- Còn trong trường hợp cụ thể bài này: hàm LEFT(ĐMVTK!$D$5:$M$5) được lồng trong hàm Lookup nên nó trở thành một dạng hàng mảng.
Để xem kết quả của hàm left nêu trên, bạn tô đoạn hàm left đó và nhấn F9, nó sẽ hiên lên kết quả như sau: {0\1\2\3\4\5\6\7\8\9}

Hay để rõ hơn, bạn qua sheet ĐMVTK, chọn khối D2:M2, gõ vào công thức =LEFT(ĐMVTK!$D$5:$M$5), xong nhấn Ctrl-Shifft-Enter, bạn sẽ thấy kết quả.

 

[THANHHT83]

Là do bạn mô tả không kỹ, mình hiểu như thế này có đúng không nhé:
Nếu TK01!E5 = 450 thì sẽ lấy ở cột I
Nếu TK01!E5 = 499 thì sẽ lấy ở cột I
Nếu TK01!E5 = 501 thì sẽ lấy ở cột J
Nếu TK01!E5 = 599 thì sẽ lấy ở cột J
????

2. Về hàm LEFT mình chưa hiểu lắm mình đã lấy ví dụ bên dưới bạn có thể giải thích giúp mình tại sao cho kết quả bằng 5 và tại sao lại báo lỗi được không?
Mong bạn giúp đỡ mình! Cảm ơn bạn nhiều!

- Bình thường thì hàm left sẽ ở dạng như sau: left(ĐMVTK!E5;1)=1 or left(ĐMVTK!E5)=1 nếu lấy 1 ký tự thì không cần tham số 1 phía sau, nếu lấy 2 ký tự trở lên thì bắt buộc phải có tham số phía sau, ví dụ: left(ĐMVTK!E5;2)=15

- Còn trong trường hợp cụ thể bài này: hàm LEFT(ĐMVTK!$D$5:$M$5) được lồng trong hàm Lookup nên nó trở thành một dạng hàng mảng.
Để xem kết quả của hàm left nêu trên, bạn tô đoạn hàm left đó và nhấn F9, nó sẽ hiên lên kết quả như sau: {0\1\2\3\4\5\6\7\8\9}

Hay để rõ hơn, bạn qua sheet ĐMVTK, chọn khối D2:M2, gõ vào công thức =LEFT(ĐMVTK!$D$5:$M$5), xong nhấn Ctrl-Shifft-Enter, bạn sẽ thấy kết quả.[/QUOTE]
Hay quá! Giờ thì mình hiều vì sao nó cho kq là 5 và báo lỗi rồi. Mình nghĩ thế này bạn xem có đúng ko nhé. Vì ví dụ mình lập công thức tại cột I nên hàm tham chiếu bên bảng ĐMVTK và trả về cột I của bảng này. Khi đó cột lấy ký tự đầu của I5=5. Nhưng đến khi mình vẫn đặt công thức tại ô I22=left(ĐMVTK!$J$5:$M$5) thì khi đó hàm sẽ trả sai chỉ đến khi mình đặt công thức tại ô J22 thì hàm mới hiểu và trả về giá trị đúng được đúng ko? Vậy là nó trả về vị trí tương ứng với cột bên bảng tìm kiếm?
Còn về vấn đề 1 thì đúng như bạn mô tả. Ta có thể hiểu là nếu chiều sâu lỗ khoan là X, 300<X<=400 thì X lấy định mức ở 0-400, nếu 400<X<=500 thì X lấy định mức ở 0-500 bạn ah. Bạn sửa lại giúp mình nhé. Cảm ơn bạn nhiều!

 

[thangbithu]

Hay quá! Giờ thì mình hiều vì sao nó cho kq là 5 và báo lỗi rồi. Mình nghĩ thế này bạn xem có đúng ko nhé. Vì ví dụ mình lập công thức tại cột I nên hàm tham chiếu bên bảng ĐMVTK và trả về cột I của bảng này. Khi đó cột lấy ký tự đầu của I5=5. Nhưng đến khi mình vẫn đặt công thức tại ô I22=left(ĐMVTK!$J$5:$M$5) thì khi đó hàm sẽ trả sai chỉ đến khi mình đặt công thức tại ô J22 thì hàm mới hiểu và trả về giá trị đúng được đúng ko? Vậy là nó trả về vị trí tương ứng với cột bên bảng tìm kiếm?

Bạn hiểu vậy là không đúng rồi.
Nó là 1 dạng hàm mảng khi nằm trong hàm Lookup, còn tại sao khi bạn đánh công thức trả về kết quả =5 và báo lỗi thì mình chịu, không biết, nhưng cả 2 kết quả này đều tào lao hết. Bạn chịu khó tìm hiểu thêm về hàm Lookup vậy, chứ giải thích ở đây thì rất dài dòng
Còn công thức thì mình chỉnh rồi, bạn thử nhiều trường hợp xem có đúng không nhé, mình không có thời gian để test

 

[THANHHT83]

Cảm ơn bạn đã chỉ giáo! Bảng tính mình chỉnh lại một chút là OK rồi. Chỉ có điều hàm Left thì mình tìm hiểu mãi vẫn ko ra tại sao nó lại như vậy . Còn một điều nữa mình muốn hỏi có thể thay hàm Lookup bằng hàm Match luôn ko? Hay nói cách khác hai hàm này có gì khác nhau? Cảm ơn bạn nhiều!