Chuyên đề giải đáp các bài tập

[BNTT]

Bạn dùng hàm VLOOKUP và SUMPRODUCT để làm bài tập số 2 nhé. Bài tập số 3 bạn nêu thêm dữ kiện cụ thể đi, các Anh chị trên GPE sẽ giúp bạn làm được thôi. Thân

Góp ý một tí. Công thức tính chi phí:

=IF(RIGHT(D3, 1)="A", VLOOKUP(LEFT(D3, 2), $A$19:$E$21, 4, 0), VLOOKUP(LEFT(D3, 2), $A$19:$E$21, 5, 0))​

Có phải ý bạn là nếu ký tự ngoài cùng ở D3 là A thì VLOOKUP lấy giá trị ở cột 4, còn không thì lấy giá trị ở cột 5? Nên đặt cái IF vào chỗ lấy cột, công thức sẽ ngắn hơn, vì khỏi dùng VLOOKUP đến 2 lần. Đồng thời nếu dùng hàm RIGHT để chỉ lấy 1 ký tự, thì khỏi cần gõ số 1:

=VLOOKUP(LEFT(D3, 2), $A$19:$E$21, IF(RIGHT(D3)="A", 4, 5), 0)
​

 

[BNTT]

Nếu đủ số liệu thì kết quả sẽ như thế này, xem file đính kèm

Cũng xin góp ý với bạn một tí. Công thức của cột F:

= IF(MONTH(D3)=10, HLOOKUP(B3,$B$14:$D$17,2,0), IF(MONTH(D3)=11, HLOOKUP(B3,$B$14:$D$17,3,0), HLOOKUP(B3,$B$14:$D$17,4,0))) * E3​

Công thức dò đơn giá để tính trị phí của bạn dài quá, thêm nữa, lỡ mà số liệu trong cột D không phải là các tháng 10, 11, và 12 thì sao? Hoặc nhỡ có đủ số cho 12 tháng, thì bạn sẽ làm 12 cái IF? Nhỡ thêm tí nữa, số liệu từ năm này sang năm khác thì IF bao nhiêu cho vừa?

Cách làm của tôi: Dùng HLOOKUP, đem Mã hàng (cột B) đi dò với Bảng 1. HLOOKUP này lấy giá trị ở hàng thứ mấy, thì dùng hàm MATCH, đem Ngày Bán (cột D) đi dò với cột A của Bảng 1. Lợi dụng việc dữ liệu trong cột A của Bảng 1 đang được xếp thứ tự từ nhỏ đến lớn, tôi sẽ bỏ qua tham số thứ 3 của hàm MATCH (hoặc cho nó là 1). Khi đó, MATCH sẽ tìm giá trị lớn nhất mà nhỏ hơn hoặc bằng giá trị Ngày Bán, và tôi có thể yên tâm rằng bán ra tháng mấy sẽ có giá của tháng đó... Và bi giờ thì dữ liệu Ngày Bán nhỡ như có từ năm này sang tháng nọ thì cũng chả sợ!

Công thức đây:

= HLOOKUP(B3, $B$14:$D$17, MATCH(D3, $A$14:$A$17), 0) * E3
​

Còn cái này:

Lập công thức điền vào cột HOA HỒNG tính như sau: Nếu hàng bán trong tháng 10 thì HOA HỒNG=1%*TRỊ GIÁ,tháng 11 thì HOA HỒNG=2%*TRỊ GIÁ,tháng 12 thì HOA HỒNG=3%*TRỊ GIÁ

Nếu điều kiện "nguyên xi" như thế, không có gì thay đổi, thì tôi sẽ dùng công thức này để tính Hoa Hồng, ngắn ngủn thôi, chẳng cần IF gì hết:

= F3 * (MONTH(D3)-9)%​

 

[thivantan]

Góp ý một tí. Công thức tính chi phí:

=IF(RIGHT(D3, 1)="A", VLOOKUP(LEFT(D3, 2), $A$19:$E$21, 4, 0), VLOOKUP(LEFT(D3, 2), $A$19:$E$21, 5, 0))​

Có phải ý bạn là nếu ký tự ngoài cùng ở D3 là A thì VLOOKUP lấy giá trị ở cột 4, còn không thì lấy giá trị ở cột 5? Nên đặt cái IF vào chỗ lấy cột, công thức sẽ ngắn hơn, vì khỏi dùng VLOOKUP đến 2 lần. Đồng thời nếu dùng hàm RIGHT để chỉ lấy 1 ký tự, thì khỏi cần gõ số 1:

=VLOOKUP(LEFT(D3, 2), $A$19:$E$21, IF(RIGHT(D3)="A", 4, 5), 0)
​

Đúng ý tôi là như vậy, Cách giải của bạn ngắn, chắt lọc hơn và rất phù hợp với những bạn đã hơi biết về EXCEL, nhưng nếu người mới đến với EXCEL thì quả là cũng hơi vất vả. Cảm ơn bạn, trên GPE tôi đã học được rất nhiều từ bạn, vì bạn diễn giải một vấn đề rất cụ thể; chi tiết và dễ hiểu. Thân

 

[vantrong83]

Giúp mình về hàm if

Tính cột số SP kế hoạc biết:
Nếu thuộc PX 2 thì số SP KH=100
Nếu thuộc PX 1 thì số SP KH=150
Nếu thuộc PX 3 thì số SP KH=120
dùng hàm if để tính, help me

 

[BNTT]

Tính cột số SP kế hoạc biết:
Nếu thuộc PX 2 thì số SP KH=100
Nếu thuộc PX 1 thì số SP KH=150
Nếu thuộc PX 3 thì số SP KH=100
dùng hàm if để tính, help me

Bạn chỉ cần đặt mỗi điều kiện cho PX1 thôi mà? PX2 và PX3 bằng nhau rồi? Công thức cho cột D nè:

=IF(C2="PX1", 150, 100)​

 

[BNTT]

Tính cột số SP kế hoạc biết:
Nếu thuộc PX 2 thì số SP KH=100
Nếu thuộc PX 1 thì số SP KH=150
Nếu thuộc PX 3 thì số SP KH=120
dùng hàm if để tính, help me

Bạn sửa lại câu hỏi rồi à? Vậy thì thêm một cái IF nữa:

=IF(C2="PX1", 150, IF(C2="PX2", 100, 120))​

 

[sao_cuong]

hoi ve ham if(anh(or.....

mến chào bạn, bạn có thể giupmình với,
mình gửi kèm file đính kèm,bạn xem giúp mình sheet 03-GTGT, sao mình làm nó k ra số liệu, k biết công thức có bị sai k.mong nhận được sự giúp đỡ của bạnn.
CÁm ơn bạn rất nhiều.Mến chào bạn.
sao_cuong

 

[vantrong83]

Cái này thì dùng hàm gì vậy các bạn

Đếm xem có bao nhiêu người quê ở Hà Tây và xếp loại A. Kết quả để ở ô A9

 

[hoangdanh282vn]

Đếm xem có bao nhiêu người quê ở Hà Tây và xếp loại A. Kết quả để ở ô A9

=SUMPRODUCT(($D$2:$D$6="Hµ T©y")*($G$2:$G$6="A"))

 

[truongduchoa]

mình đang vướng 1 vần đề cần nhờ các cao thủ chỉ giáo như sau :

sheet1 liệt kê các hóa đơn phát sinh trong ngày theo hàng
VD :
ngày sohd masp soluong so tien
1 01 a 10 20
1 01 b 10 20
1 02 a 20 40
1 03 b 10 20
1 03 c 50 100

trong sheets 2 mình muốn làm như sau
NGAY : 1 (chỉ cần chọn ở đây là số hóa đơn sẽ xuất hiện ở dưới)

so hd so tien
01 40
02 40
03 120

về so tien thì mình biết cách rồi, nhưng vấn đề ở đây là làm sao biết được trong ngày đó có bao nhiêu hóa đơn và liệt kê nó ra như trong sheets 2

Mong các bạn chỉ giáo.. mình cần gấp lắm

 

[quoctin122]

giúp mình với

mới tập tành học excel nên lúng túng wá. bài này mình ko làm được câu 2
bạn nào trả lời xong gui wa mail cho minh nha:quoctin122@gmail.com
thank

 

[thivantan]

mới tập tành học excel nên lúng túng wá. bài này mình ko làm được câu 2
bạn nào trả lời xong gui wa mail cho minh nha:quoctin122@gmail.com
thank

Nếu bạn muốn tính ngày trả đầu tiên sau 30 ngày kể từ ngày vay, bạn đặt công thức tại cột ngày trả đầu tiên (H7) = Ngày vay (C7) + 30, sau đó Fill xuống là được. Thân

 

[thanhxt2009]

Gởi bạn bài giải "đường sữa gạo.xls"

Trong bài phần nhập đơn giá loại A va loại B bạn nhập luôn chữ "đ" ở cuối con số thì làm sao tính được!

 

[thanhxt2009]

Thân chào các bạn! Nào hãy thử sức với bài tập Excel này nhé!

Bạn đã biết về MS Excel thì hãy thử sức nhé! Mình gởi bài này để học hỏi những công thức giải bài hay của các bạn. Thân ái! Bạn nào giải xong thì gởi mail cho mình nhé! Mail của mình nè! thanhxt2009@gmail.com

 

[thanhxt2009]

Đừng khóc! Xem đây nè!

Đây bài giải của bạn đây! Chúc học tốt

 

[miss_ltk]

thân chào các anh chị!! em vừa mới thi xong ngày hôm nay .... có bài E này muốn nhờ anh chị giải hộ để em dò lại bài . cám ơn các anh chị nhiều^^
Thân,
Miss_ltk.
P/S: nếu câu nào dùng nhiều cách thì càng tốt ạ >"<
@ thanhtx2009: tối nay mệt rồi mai mình sẽ thử sức nhá

 

[BNTT]

thân chào các anh chị!! em vừa mới thi xong ngày hôm nay .... có bài E này muốn nhờ anh chị giải hộ để em dò lại bài . cám ơn các anh chị nhiều^^

Tôi làm một công thức ở hàng 4, bạn copy xuống đến hàng 12 nhé.
Công thức màu nâu đỏ là công thức bài bản. Còn công thức màu xanh (nếu có) là công thức rút gọn.

Câu 1 (tuyến du lịch):

=VLOOKUP(LEFT(A4,1), $A$19:$B$22, 2, 0)

=VLOOKUP(LEFT(A4), $A$19:$B$22, 2, 0)​

Câu 2 (phương tiện):

=IF(MID(A4,2,1)="1", "Xe", IF(MID(A4,2,1)="2", "Tàu", "Máy bay"))​

Câu 3 (tiền PT):

=IF(F4="Xe", VLOOKUP(LEFT(A4,1), $A$19:$C$22, 3, 0), HLOOKUP(LEFT(A4,1), $C$26:$F$28, MATCH(F4, $B$27:$B$28, 0)+1, 0))

=IF(F4="Xe", VLOOKUP(LEFT(A4), $A$19:$C$22, 3, 0), HLOOKUP(LEFT(A4), $C$26:$F$28, MATCH(F4, $B$27:$B$28, 0)+1, 0))​

Câu 4 (số ngày ở):

=D4-C4-IF(WEEKDAY(C4)=1, 1, 0)

=D4-C4-(WEEKDAY(C4)=1)​

Câu 5 (tiền ăn ở):

=E4*(VLOOKUP(LEFT(A4,1), $A$19:$E$22, RIGHT(A4,1)+3, 0))

=E4*(VLOOKUP(LEFT(A4), $A$19:$E$22, RIGHT(A4)+3, 0))​

Câu 6 (miễn giảm):

=H4*IF(AND(OR(LEFT(A4,1)="H", LEFT(A4,1)="D"), AND(MONTH(C4)=5, MONTH(D4)=6)), 2%, 0)

=H4*((LEFT(A4)="H")+(LEFT(A4)="D"))*(MONTH(C4)=5)*(MONTH(D4)=6)*2%​

Câu 7 (tổng cộng):

=(G4+H4-I4)*(1 - IF(AND(OR(LEFT(A4,1)="H", LEFT(A4,1)="D"), E4>10), 10%, 0))

=(G4+H4-I4)*(1-((LEFT(A4)="H")+(LEFT(A4)="D"))*(E4>10)*10%)​

Câu 8 (đếm những chuyến đi ĐN bằng máy bay):

=SUMPRODUCT((LEFT(A4:A12)="D")*(MID(A4:A12, 2, 1)="3"))​

Câu 9 (tính tổng tiền những chuyến đi đến ĐN được miễn giảm):

=SUMIF(B4:B12, "Đà Nẵng", I4:I12)

=SUMIF(B4:B12, "Đà Nẵng", I4)​

 

[BNTT]

Đây bài giải của bạn đây! Chúc học tốt

Bài o-to.xls bạn làm như thế là đạt rồi. Tuy nhiên, có thể làm cho công thức ngắn hơn. Đây là bảng Hệ thống phân phối xe năm 2007, nên đương nhiên là tính cho năm 2007, do đó không cần dùng đến hàm DATE nữa, mà xét thẳng con số ngày, số tháng trong mã ký hiệu luôn, sẽ gọn công thức hơn.

Tôi sẽ làm để bạn so sánh, công thức màu đỏ nâu là của bạn, còn màu xanh là của tôi (trong các công thức dưới đây, tôi chèn thêm khoảng trắng vào để nó không bị nhảy lung tung, còn trong thực tế bảng tính, bạn có thể để yên vậy hoặc gõ dính hết lại với nhau):

Công thức tính thành tiền:

=IF(AND(RIGHT(B3,2)="BM",DATE(2007,MID(B3,5,2),MID(B3,3,2))>=DATE(2007,3,1)),90%,1)*D3*E3

Có thể rút ngắn lại:

=IF((RIGHT(B3,2)="BM")*(MID(B3,3,2)*1>1)*(MID(B3,5,2)*1>=3),90%,1)*D3*E3

Công thức cho cột Ghi chú:

=IF(AND(AND(LEFT(B3,2)="MD",OR(DATE(2007,MID(B3,5,2),MID(B3,3,2))=DATE(2007,2,12),DATE(2007,MID(B3,5,2),MID(B3,3,2))=DATE(2007,5,20))),D3>=5),"tặng phiếu ưu đãi","")

Có thể rút ngắn lại:

=IF((LEFT(B3,2)="MD")*((MID(B3,3,2)="12")*(MID(B3,5,2)="02")+MID(B3,3,2)="20")*MID(B3,5,2)="05"))*(D3>=5),"tặng phiếu ưu đãi","")

 

[miss_ltk]

cảm ơn bác BNTT nhiều lắm nhưg tôi còn một số thắc mắc sau :
câu 6 công thức đầu thì còn hiểu chứ công thức mà bác rút gọn thì tôi không hiểu cho lắm , hình như dấu "+" có ý là hoặc còn * là và . nhưng hình như vẫn chưa ổn lắm, cảm phiền bác có thể chỉ giáo thêm được không ạ??
câu 7 CT rút gọn cũng giống câu 6 nên .... hic><
câu 9 : tính tổng tiền những chuyến đi Đn đc miễn giảm ta phải dùng hàm sumproduct chứ ạ??
Chân thành cảm ơn bác,
Miss_ltk.

 

[thanhxt2009]

Gởi bài giải cho bạn nè! Tham khảo nhé!

Bài tập tinhoc.xls của bạn cũng khá hay đấy chứ! Nhưng mà cũng chưa ớ mức độ khó lắm đâu!