Những vấn đề về hàm IF (6 người xem)

[ngaykhongmua]

Cho mình hỏi bạn BNTT:
=--SUBSTITUTE(VLOOKUP(LEFT(B3,3), $L$13:$O$17, 4, 0), " USD", "")
Vì mình cũng mới học excel đây thôi nên cũng có những chỗ không hiểu ví dụ như trong công thức trên của bạn dấu --có ý nghĩa gì, tại sao phải dùng nó, và có thể nói thêm cho mình biết về hàm SUBSTITUTE được không? Cám ơn bạn nhé!
À quên nữa câu cuối cùng của bài 05 nó bắt định dạng các cột Đơn giá, thuế, thành tiền theo kiểu có dấu phân cách hàng ngàn và có đơn vị tiền tệ là USD ở phía trước.​

 

[doilangthang]

Hỏi đáp về INDIRECT

Mình dùng công thức như vầy! Khỏi cần bảng Vlookup luôn. Và bạn muốn nó dài bao nhiêu thì tùy ý!

=INDIRECT("C" & 14-((B2<=$D$2)+(B2<=$D$3)+(B2<=$D$4)+(B2<=$D$5)+(B2<=$D$6)+(B2<=$D$7)+(B2<=$D$8)+(B2<=$D$9)+(B2<=$D$10)+(B2<=$D$11)+(B2<=$D$12)+(B2<=$D$13)))

Thân.

Bạn có thể giải thích cho mình đoạn này được không "((B2<=$D$2)+(B2<=$D$3)+(B2<=$D$4)+(B2<=$D$5)+(B2<=$D$6)+(B2<=$D$7)+(B2<=$D$8)+(B2<=$D$9)+(B2<=$D$10)+(B2<=$D$11)+(B2<=$D$12)+(B2<=$D$13))" vì mình chưa hiểu lắm.

 

[BNTT]

Cho mình hỏi bạn BNTT:
=--SUBSTITUTE(VLOOKUP(LEFT(B3,3), $L$13:$O$17, 4, 0), " USD", "")
Vì mình cũng mới học excel đây thôi nên cũng có những chỗ không hiểu ví dụ như trong công thức trên của bạn dấu --có ý nghĩa gì, tại sao phải dùng nó, và có thể nói thêm cho mình biết về hàm SUBSTITUTE được không? Cám ơn bạn nhé!
À quên nữa câu cuối cùng của bài 05 nó bắt định dạng các cột Đơn giá, thuế, thành tiền theo kiểu có dấu phân cách hàng ngàn và có đơn vị tiền tệ là USD ở phía trước.​

Chuyện thứ nhất:

SUBSTITUTE là hàm dùng để thay thế. Nó sẽ tìm trong kết quả của VLOOKUP, hễ thấy cái nào có chuỗi " USD" thì thay thế bẳng ký tự rỗng (""), hay nói cách khác, nó bỏ đi chuỗi " USD", chỉ lấy con số thôi, và đồng thời 2 cái dấu -- ở trước công thức là để chuyển cái con số (đã được tách ra từ chuỗi có chữ USD) trở thành con số thực để có thể tính toán được.

Xem thêm: Hàm SUBSTITUTE()​

Chuyện thứ hai:

Nếu muốn được như yêu cầu, thì bạn phải tính đơn giá theo cách thứ hai mà tôi đã nói ở bài trước, nghĩa là tính đơn giá theo công thức này:

=--SUBSTITUTE(VLOOKUP(LEFT(B3,3), $L$13:$O$17, 4, 0), " USD", "")​

Sau đó bạn chọn hết 3 cột Đơn giá, Thuế, Thành Tiền, nhấn Ctrl+1 để gọi hộp thoại Format Cells, chọn Custom, đại khái giống y như cái hình tôi đã gửi ở bài trước, nhưng trong khung type (chỗ có mũi tên màu đỏ tôi vẽ thêm vào), thì nhập cái này:

"USD" #,##0 ​

Nếu trong 3 cái cột đó đã có những con số rồi, bạn có thể kiểm tra ngay kết quả khi bạn nhập, ở ngay phía trên khung Type, phần Sample (trong hình ở bài trước, bạn thấy ở khung Type tôi nhập #0 "USD" thì trong phần Sample nó hiện ra 20 USD).​

 

[BNTT]

Bạn có thể giải thích cho mình đoạn này được không "((B2<=$D$2)+(B2<=$D$3)+(B2<=$D$4)+(B2<=$D$5)+(B2<=$D$6)+(B2<=$D$7)+(B2<=$D$8)+(B2<=$D$9)+(B2<=$D$10)+(B2<=$D$11)+(B2<=$D$12)+(B2<=$D$13))" vì mình chưa hiểu lắm.

Cái đó chẳng qua là một loạt biểu thức so sánh thôi, đem giá trị trong B2 đi so sánh lần lượt với D2, D3, .... D12, xem có nhỏ hơn hoặc bằng (<=) không, nếu đúng thì trả về TRUE (tương đương số 1), nếu sai thì trả về FALSE (tương đương số 0). Và sau khi làm xong thì cộng hết mấy kết quả TRUE và FALSE (1 và 0) này lại, ra một con số nào đó.

Thôi để tôi lấy 1 trường hợp cụ thể cho bạn xem thì dễ hiểu hơn:

Công thức ở ô E8 trong bảng tính ở trên đang là:

=INDIRECT("C" & 14-((B8<=$D$2) + (B8<=$D$3) + (B8<=$D$4) + (B8<=$D$5) + (B8<=$D$6) + (B8<=$D$7) + (B8<=$D$8) + (B8<=$D$9) + (B8<=$D$10) + (B8<=$D$11) + (B8<=$D$12) + (B8<=$D$13)))​

Tôi thay hết mấy con số thực của các ô B8 (=25) và từ D2 đến D13 vào công thức nhé:

=INDIRECT("C" & 14-((25<=10) + (25<=20) + (25<=30) + (25<=40) + (25<=50) + (25<=60) + (25<=70) + (25<=80) + (25<=90) + (25<=100) + (25<=110) + (25<=120)))​

Bạn thấy đấy, chỉ có mấy cái màu xanh là đúng, còn 2 cái màu đỏ là sai. Bi giờ tôi thay tiếp đúng (TRUE) là 1, còn sai (FALSE) là 0 vào đó nhé:

=INDIRECT("C" & 14-(0 + 0 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1))​

Bạn nhẩm thử cái dãy 0+0+1+1.... nó ra bao nhiêu, có phải là 10 không? Công thức lúc này sẽ là:

=INDIRECT("C" & 14-(10)) =INDIRECT("C" & 4)​

Hàm INDIRECT có chức năng biến đổi những cái gì trong ruột của nó thành một tham chiếu, do đó kết quả chúng ta có sẽ là C4, hay nói cách khác, công thức này sẽ trả về giá trị tại ô C4, tức là:

=INDIRECT("C" & 14-((B8<=$D$2) + (B8<=$D$3) + (B8<=$D$4) + (B8<=$D$5) + (B8<=$D$6) + (B8<=$D$7) + (B8<=$D$8) + (B8<=$D$9) + (B8<=$D$10) + (B8<=$D$11) + (B8<=$D$12) + (B8<=$D$13))) = 4x16​

Bạn xem ô C4 có phải là 4x16 không?

 

[BNTT]

Có một cách nữa, dùng để tự xem công thức nó chạy như thế nào, đó là công cụ Evaluate Formula, nó nằm trong menu Tools > Formula Auditing:

Nghĩa là, nếu bạn đọc bài trên đây của tôi mà thấy dài dòng văn tự quá, chả hiểu gì... thì bạn có thể tự nghiên cứu: Nhấn chọn ô chứa công thức dài dòng (ví dụ ô E8 mà tôi trích ra ở bài trước), rồi gọi công cụ Evaluate Formula, rồi nhấn vào nút Evaluate.... xem nó chạy từ từ như thế nào:

Mà ở bài trước, tôi cũng dựa vào cái này để "dịch sang tiếng Việt" đấy thôi.

 

[ngaykhongmua]

bài 07 câu 3 bạn cop kh làm là =IF(TYPE(F4*D4)=16,"Tính sau",F4*D4) mình muốn hỏi 16 là gì vậy bạn? Mình không hiểu có bạn nào giải thích giùm mình được không?
Sao không có bạn nào xem dùm mình câu 10 của bài 05 hết vậy?

 

[cadafi]

bài 07 câu 3 bạn cop kh làm là =IF(TYPE(F4*D4)=16,"Tính sau",F4*D4) mình muốn hỏi 16 là gì vậy bạn? Mình không hiểu có bạn nào giải thích giùm mình được không?

Bạn xem bài trả lời tại đây nhé! Bài của anh BNTT
http://www.giaiphapexcel.com/forum/showpost.php?p=47937&postcount=7

Và đây nữa
http://www.giaiphapexcel.com/forum/showpost.php?p=88990&postcount=3

 

[thiennx]

Rất cám ơn các bác đã quan tâm và đưa ra các giải pháp giúp em.
Công thức của bác BNTT cũng đã đáp ứng được yêu cầu ấy. Rất mong các bác cho thêm vài cách khác nhau để em tham khảo.
Hôm trước em chưa nói kỹ, và thực ra nó như thế này.

 

[Po_Pikachu]

Ví dụ : Dòng tính toán ở cột B3 = 50,51<68 và >49 thì phải chọn cáp 4x25 có dòng cho phép là 68

Nếu vậy phải lấy 4x16 là đúng rồi sao lại nhãy lên đến 4x25 được. Điều này nghe không được Logic lắm.
Vậy ra 4x16 không có giá trị nào để lấy à?
Bạn xét lại xem có trường hợp nào để xuất ra giá trị 4x16 không? Nếu không thì ta bỏ trị này đi là xong.
Thân.

 

[cop_kh]

Thú thật thì em không hiểu tại sao các Bác cứ làm phức tạp lên vậy, theo em thì Bác thiennx đưa lên 1 bảng kê đầy đủ các trường hợp để em làm thử cho. VD Bác liệt kê như sau:

Dòng 0 đến 10: dùng cáp 4x6.
Dòng >10 đến 20: dùng cáp 4x8...

Có phải vậy không Bác?

 

[DOSNET]

Nếu vậy phải lấy 4x16 là đúng rồi sao lại nhãy lên đến 4x25 được. Điều này nghe không được Logic lắm.
Vậy ra 4x16 không có giá trị nào để lấy à?
Bạn xét lại xem có trường hợp nào để xuất ra giá trị 4x16 không? Nếu không thì ta bỏ trị này đi là xong.
Thân.

Po_Pikachu không làm trong ngành điện nên chưa hiểu, nguyên tắc chọn cable phải chọn >= dòng tính toán. Chọn 4X25 trong trường hợp này là hợp lý. Tuy nhiên công thức để chọn cable có lẽ cần làm lại cho gọn hơn !

 

[Po_Pikachu]

Vâng rất đúng! Em không làm trong nghành điện. Phần gọn hơn thì ta dùng Vlookup.
Vậy bác có thể chỉ cho em được rõ là cái 4x16 kia dùng vào cái chỗ nào không? Tại vì từ 49 -> 68 không có số ở giữa để phân cách số liệu ở B3 (50.51). Vậy nó sẽ lấy thông số kế tiếp hơn 49 (tức là 4x16).
Vậy là vấn đề không phải tại em. Vấn đề bác không nêu rõ cận trên và cận dưới của phần so sánh số liệu.
Nếu vậy bác nên lập ra 1 bảng so sánh cận trên và cận dưới thật chi tiết rõ ràng. Rồi dùng hàm Vlookup. Vậy có phải đơn giản hơn không?
Thân.

 

[DOSNET]

File này cũng không phải của tôi, cũng chỉ góp ý thôi. Việc làm của chúng ta là:

Với dòng điện tính toán xác định, ta chọn loại cable có kích thước như nào trong bảng.

Với những bảng tra dữ liệu đồ sộ, việc sắp xếp lại bảng tra là việc làm không thể. Cable 4X16 không dùng vào việc gì trong trường hợp này đơn giản vì người ta không dùng đến loại cable đó thôi. thân!

 

[Po_Pikachu]

Nếu không dùng thì bỏ đi là xong.
Bác xem thử file nha!
Thân.

 

[thiennx]

Ý của em là dùng hàm indirect như trước là cũng đáp ứng được rồi, có điều cũng hơi phức tạp chút vì cũng phải có tương đối kiến thức mới hiểu và áp dụng được.
Em khẳng định là trong trường hợp này cũng khó có thể dùng vlookup.
Em dùng hàm if thì sẽ đơn giản, nhưng chỉ giải quyết được 7 lần if trong excel 2003.trong trường hợp này cần tới 12,13 lần if mới hết.

 

[ndu96081631]

Ý của em là dùng hàm indirect như trước là cũng đáp ứng được rồi, có điều cũng hơi phức tạp chút vì cũng phải có tương đối kiến thức mới hiểu và áp dụng được.
Em khẳng định là trong trường hợp này cũng khó có thể dùng vlookup.
Em dùng hàm if thì sẽ đơn giản, nhưng chỉ giải quyết được 7 lần if trong excel 2003.trong trường hợp này cần tới 12,13 lần if mới hết.

Bạn gõ công thức này vào F2 nhé:

=LOOKUP(MIN(IF($B2<=$D$2:$D$13,$D$2:$D$13,"")),$D$2:$D$13,$C$2:$C$13)

Gõ xong bấm tổ hợp phím Ctrl + Shift + Enter

 

[thiennx]

Công thức lookup của bác ndu thì kéo xuống để lấy ô B3 để so sánh thì báo lỗi value!

 

[ndu96081631]

Công thức lookup của bác ndu thì kéo xuống để lấy ô B3 để so sánh thì báo lỗi value!

Bạn đọc kỹ hướng dẩn chưa? Có bấm Ctrl + Shift + Enter không?
Tôi nghĩ báo lổi là vì bạn chỉ Enter bình thường mà không làm giống như tôi đã nói!
Thử lại xem!

 

[cop_kh]

Như vậy thì dùng hàm VLOOKUP với cách tra gần đúng là OK nhất rồi phải không các Bác nhỉ. Vậy mà các Bác khó thằng em không hà hihihi.

 

[gautruchd]

Tôi có một trường hợp như file đính kèm. Tôi cũng đã cố gắng thử bằng 1 vài hàm khác nhau trong excel 2003 nhưng không làm được. Nhờ các cao thủ giúp đỡ. Nếu là excel 2007 thì có thể dùng hàm ifs được, nhưng chỗ tôi lại chưa dùng excel 2007!!!

Bạn Có thể dùng hàm &If ma.Bạn muốn có bao nhiêu điều kiện cũng được mà.
Bạn Thử nhé:=if(B2=<=D2,E2=C2,"")&if(B2>D2,B2=D3,"")&IF(AND(B2>=D2,B2<=D3),E2=C3,"")&IF(AND(B2>D3,B2=D4),E2=C4,"")......