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

BNTT · 15/12/08

Xin mở topic này, dành cho các bạn gửi những công thức khó, nhờ giải thích.

Mở đầu là công thức sau đây:

Sealand đã viết:
Con mình vào GPE và đọc bài hướng dẫn công thức khai name của 1 vùng như sau:

=OFFSET(Sheet1!$B$1,,, 1, MATCH(REPT("z", 255), Sheet1!$1:$1) -1)

Mình không giải thích được cho cháu phần này:

MATCH(REPT("z", 255), Sheet1!$1:$1) -1)

Các bạn giúp mình với, cháu đang ôn thi và trình độ trung bình thôi nên chi tiết 1 chút giùm.

Thân

Sealand.

Nhờ các bạn giải thích dùm Sealand.

hoangvuluan · 15/12/08

MATCH(REPT("z", 255), Sheet1!$1:$1)
Công thức này trả lại tổng số các ô có giá trị trên dòng 1 với điều kiện các giá trị là dãy kiểu chuỗi được sắp thứ tự từ trái sang phải.
Nếu xét hết tòan bộ thì :
=OFFSET(Sheet1!$B$1,,, 1, MATCH(REPT("z", 255), Sheet1!$1:$1) -1)
sẽ trả lại vùng tham chiếu 1 dòng bắt đầu từ B1 có k cột (với các cột là chuỗi được sắp thứ tự tăng dần.
Ví dụ: từ ô B1 đến F1 ta nhập vào các giá trị: a, b, c, d, e thì ta nhận được tham chiếu: [B1:F1].

le tin · 16/12/08

Chưa chính xác bạn ơi .Không phải trả lại tổng mà k là tổng số ô từ B1 đến địa chỉ ô cuối cùng . Ví dụ chỉ cần đánh vào F1 sẽ có kết quả như trên

DOSNET · 16/12/08

Mình có cách giải thích khác với bạn Hvl một chút.
- Không nhất thiết các cột chuỗi phải được sắp xếp tăng dần.

=MATCH(REPT("z", 255), Sheet1!$1:$1)

Sẽ trả về vị trí cột chứa ô cuối cùng nhất là dạng ký tự, và :

=MATCH(REPT("z", 255), Sheet1!$1:$1)-1

sẽ trả về độ rộng mảng (vì bắt đầu từ B1). Vậy

=OFFSET(Sheet1!$B$1,,, 1, MATCH(REPT("z", 255), Sheet1!$1:$1) -1)

Sẽ trả về mảng giá trị thuộc dòng 1 bắt đầu từ B1 đến ô cuối cùng là ô chứa ký tự bất kỳ.

ndu96081631 · 16/12/08

BNTT đã viết:
Xin mở topic này, dành cho các bạn gửi những công thức khó, nhờ giải thích.

Mở đầu là công thức sau đây:

Nhờ các bạn giải thích dùm Sealand.

Nếu biết rằng chuổi cũng có thể so sánh được giống như số thì sẽ hiểu công thức trên rất dể dàng!
- Cell A1 ta gõ chử A
- Cell B1 ta gõ chử B
- Cell C1 ta gõ = A1<B1 nó sẽ cho KQ = True
Từ đó suy ra Z là "bự nhất" trong bảng alphabet ---> 255 chử Z viết liên tục nhau thì còn có chuổi nào "bự hơn" nó nữa chứ ---> MATCH với cách dò không chính xác sẽ tìm từ đầu đến cuối mãng, đương nhiên không em nào có thể = với điều kiện dò thì nó sẽ lấy EM CUỐI CÙNG TRONG MÃNG

hard learner · 16/12/08

Nhờ các bạn giải thích dùm:

=IF(B4="","",INDEX('DS tong hop'!$A$1:$E$18,SMALL(loc,ROW('DS tong hop'!1:1)),))

Đặc biệt 1:1 ở đây là gì jậy, hổng hiểu luôn

le tin · 16/12/08

'DS tong hop'!1:1 : là dòng 1 của sheet 'DS tong hop'
ROW('DS tong hop'!1:1) : cho kết quả là 1
ROW('DS tong hop'!2:2) : cho kết quả là 2
...
SMALL(loc,ROW('DS tong ho'!1:1)) : lấy giá trị nhỏ nhất trong mãng loc
SMALL(loc,ROW('DS tong ho'!2:2)) : lấy giá trị nhỏ thứ nhì trong mãng loc
...
Khi kéo công thức xuống bạn sẽ thấy, như vậy mục đích của 1:1 là đánh số thứ tự tăng dần

DOSNET · 16/12/08

=IF(B4="","",INDEX('DS tong hop'!$A$1:$E$18,SMALL(loc,ROW('DS tong hop'!1:1)),))

Công thức này được bác Anhtuan1066 sử dụng đầu tiên với những bài lọc bằng công thức, Cách sử dụng hàm ROW thật thiên biến vạn hóa, Bác Le tin đã giải thích cho bạn cách dùng ROW(1:1). Mục đích là tăng dần số thứ tự khi bạn kéo công thức.

-Với công thức này, ta nên đi sâu vào nghiên cứu cách dùng hàm ROW trong mảng LOC. Khi đó hàm ROW lại trả về kết quả là mảng giá trị

LOC=IF(NHOM=$I$1,ROW(INDIRECT("1:"&COUNTA(NHOM))),"")

Với I1 là điều kiện lọc, NHOM là mảng động được xác định bởi công thức :

NHOM=OFFSET($A$7,,,COUNTA($A$7:$A$1000))

Name LOC trả về mạng giá trị (vừa chứa giá trị, vừa chứa ký tự trống nếu không phù hợp điều kiện) và lý do tại sao ta lại dùng hàm SMALL để loại bỏ khỏi chọn những ký tự rỗng trong mảng, nhưng điều quan trọng các bạn có thể giải thích tại sao với cách dùng trên name LOC lại trả về mảng giá trị không?

anhtuan1066 · 16/12/08

DOSNET đã viết:
Công thức này được phát minh bởi bác Anhtuan1066, Cách sử dụng hàm ROW thật thiên biến vạn hóa, Bác Letin đã giải thích cho bạn cách dùng ROW(1:1). Mục đích là tăng dần số thứ tự khi bạn kéo công thức.

-Với công thức này, ta nên đi sâu vào nghiên cứu cách dùng hàm ROW trong mảng LOC. Khi đó hàm ROW lại trả về kết quả là mảng giá trị

Với I1 là điều kiện lọc, NHOM là mảng động được xác định bởi công thức :

Các bạn có thể giải thích tại sao với cách dùng trên name LOC lại trả về mảng giá trị không?

1> Tại 1 cell nào đó, gõ vào công thức =ROW(1:10) rồi Ctrl + Shift + Enter
2> Bôi đen công thức vừa gõ trên thanh Formula rồi bấm F9 sẽ thấy nó là 1 mãng giá trị
Thật ra bản thân hàm ROW đã là mãng rồi, nên vẩn có thể Enter mà ko cần phải Ctrl + Shift + Enter

volga · 31/12/08

IF(MID(Data,CHOOSE(MATCH(Loc!$A$1,Loc!$H$1:$J$1,0),1,3,6),CHOOSE(MATCH(Loc!$A$1,Loc!$H$1:$J$1,0),2,3,3))=Loc!$B$1,ROW(INDIRECT("1:"&ROWS(Data))),"")

Những con số phía sau hàm choose là gì vậy các anh .Em không hiểu +-+-+-+

Có thể giải thích dùm em không ?

----------------------------------------
Bài này, được chuyển từ đây sang: http://www.giaiphapexcel.com/forum/showthread.php?p=126577#post126577

ndu96081631 · 31/12/08

volga đã viết:
IF(MID(Data,CHOOSE(MATCH(Loc!$A$1,Loc!$H$1:$J$1,0),1,3,6),CHOOSE(MATCH(Loc!$A$1,Loc!$H$1:$J$1,0),2,3,3))=Loc!$B$1,ROW(INDIRECT("1:"&ROWS(Data))),"")

Những con số phía sau hàm choose là gì vậy các anh .Em không hiểu

Có thể giải thích dùm em không ?

http://www.giaiphapexcel.com/forum/showthread.php?p=126577#post126577

Tách ra 1 đoạn:

PHP:

CHOOSE(MATCH(Loc!$A$1,Loc!$H$1:$J$1,0),1,3,6)

Tức là xem kết quả của MATCH(Loc!$A$1,Loc!$H$1:$J$1,0)
Nếu KQ này = 1 thì hàm CHOOSE cho KQ = 1
Nếu KQ này = 2 thì hàm CHOOSE cho KQ = 3
Nếu KQ này = 3 thì hàm CHOOSE cho KQ = 6
Ví dụ 1 ứng dụng của nó nhé:
- Bạn gõ vào cell A1 ngày tháng năm nào đó
- Cell B1 bạn muốn có kết quả là THỨ của ngày ấy
Ta dùng CHOOSE như sau:

PHP:

=CHOOSE(WEEKDAY(A1),"Chủ nhật","Thứ hai","Thứ ba","Thứ tư","Thứ năm","Thứ sáu","Thứ bảy")

BNTT · 1/1/09

.... Mình hiểu ra vấn đề rồi bạn. Sorry đã làm phiền, nhưng cho hỏi thêm là trong công thức bạn chỉ cho mình : = MIN (F4,G4)*450+(G4-F4)*800*(G4>F4) <<< cái này là gì vậy bạn ? Mình đã thử đổi ...*(G4<F4) thì nó ra kq là 0. Tại sao thế ?
Còn nửa khi mình fill công thức theo cách bạn chỉ, mình gặp vấn đề là ví dụ :H6 =F6*H2+G6*H3 trong đó H2 là đơn giá tuần, H3 là đơn giá ngày. F6 là số tuần, G6 là số ngày. Khi mình fill công thức or copy & paste thì nó sẽ nhảy lun H2 và H3. (Mình kéo xuống H7 thì nó ra là H7=F7*H3+G7*H4 ) Làm sao cho H2 và H3 không bị nhảy hả bạn .

Bạn Aikhanh thân,

Chuyện thứ nhất:

(G4<F4) là một biểu thức so sánh, sẽ trả về giá trị là 1 nếu đúng (TRUE) và trả về giá trị là 0 nếu sai (FALSE). Người ta dùng cái này để làm cho công thức ngắn gọn hơn.

Công thức này:

= MIN (F4,G4) * 450 + (G4-F4) * 800 * (G4>F4)

Tương đương với công thức:

= MIN (F4,G4) * 450 + (G4-F4) * 800 * IF(G4>F4, 1, 0)

Tức là nếu như F4 > G4, thì công thức trên sẽ có kết quả là: = MIN (F4,G4) * 450 + (G4-F4) * 800
Còn nếu như F4 < G4, hay F4 = G4, thì công thức trên có kết quả là: = MIN (F4,G4) * 450

Nếu vẫn chưa hiểu, bạn xem thêm ví dụ này:

Giả sử bạn có một dãy số ở cột A, người ta yêu cầu lập công thức bên cột B, nếu như con số bên cột A mà lớn hơn 10, thì bên cột B tương ứng sẽ là 100, còn nếu như con số bên cột A nhỏ hơn hoặc bằng 10, thì bên cột B tương ứng sẽ là 0.

Bình thường, ta sẽ lập công thức cho cột B như sau (tại ô B1 chẳng hạn):

[B1] =IF(A1>10, 100, 0)

Phải không? Nhưng với loại hàm IF mà chỉ có một điều kiện như vậy, tôi sẽ dùng cái này, ngắn hơn:

[B1] =(A1>100)*100

Khi A1 > 100, biểu thức (A1>100) = 1 (TRUE), do đó B1 = 1*100 = 100
Khi A1 <=10, biểu thức (A1>100) = 0 (FALSE), do đó B1 = 0*100 = 0

Bạn hiểu rồi chứ ?

--------------------------------

Chuyện thứ hai:

Nếu bạn muốn cố định một ô nào đó để nó không bị nhảy khi kéo công thức, thì bạn gõ thêm dấu $ ở trước chỉ số hàng và chỉ số cột của nó.

Ví dụ, bạn muốn giữ nguyên H2 lại, thì bạn dùng $H$2 thay vì chỉ là H2.

Bạn có thể đọc thêm bài viết này của tôi: http://www.giaiphapexcel.com/forum/showthread.php?t=9546, từ bài 12 trở đi: Sao chép và Di chuyển công thức

--------------------------------

Chuyện thứ ba:

Lần sau, bạn nên gửi câu hỏi lên diễn đàn, bạn sẽ được trả lời nhanh hơn.
Vì nếu bạn gửi vào hộp thư của tôi, đâu phải lúc nào tôi cũng online để trả lời cho bạn?

volga · 6/1/09

Các anhchị giải thích giùm em công thức này .
SUMPRODUCT(--NOT(ISERROR(FIND(H3;$C$2:$C$6)));--($E$2:$E$6<>""))

Công thức này em lấy từ File của bạn
huuthang_bd

Cùi bắp tiên sinh

http://www.giaiphapexcel.com/forum/showthread.php?t=18232

Cám ơn mọi người ....!

hoangdanh282vn · 6/1/09

volga đã viết:
Các anhchị giải thích giùm em công thức này .
SUMPRODUCT(--NOT(ISERROR(FIND(H3;$C$2:$C$6)));--($E$2:$E$6<>""))

Hàm Find :

Tìm vị trí của chuỗi con trong trong Chuỗi dò tìm. Nếu tìm thấy thì trả về giá trị số, ngước lại trả về giá trị lỗi.
VD : =FIND("a","danh") => 2, =FIND("b","danh") => #Value

FIND(H3;$C$2:$C$6) :

- Tỉm kiếm vị trí chuỗi có trong ô H3 trong các Chuỗi dò tìm có trong ô C2, trong ô C3...C6.
- Nếu ta tim kiếm một chuỗi trong mỗi chuỗi khác thì kết quả trả về một giá trị duy nhất ( giá trị số hay lỗi). Trong hàm trên ta tìm một chuỗi trong nhiều chuỗi thì kết quả sẽ trả về là một mảng các giá trị, vừa là giá trị số, vừa là giá trị lỗi.
VD : FIND(H3;$C$2:$C$6) <=> {1;5;#value;#value;2;#value}

Hàm ISERROR :

Kiểm tra xem giá trị có lỗi hay không. Trả về True nếu có lỗi, False nếu không có lỗi.
ISERROR(FIND(H3;$C$2:$C$6)) <=> {False;False;True;True;False;True}

NOT(ISERROR(FIND(H3;$C$2:$C$6))) <=> {True;True;False ;False;True;False}
-NOT(ISERROR(FIND(H3;$C$2:$C$6))) <=> {-1;-1;0;0;-1;0}
--NOT(ISERROR(FIND(H3;$C$2:$C$6))) <=> {1;1;0;0;1;0}

Tương tự cho : --($E$2:$E$6<>"")

VD : --($E$2:$E$6<>"") <=> {1;0;0;1;1;1}

SUMPRODUCT(--NOT(ISERROR(FIND(H3;$C$2:$C$6)));--($E$2:$E$6<>""))
<=> SUMPRODUCT({-1;-1;0;0;-1;0},{1;0;0;1;1;1})
<=> -1*1 + -1*0 + 0*0 + 0*1 + -1*1 + 0*1 = -2

Mình giải thích vậy chắc ai cũng hiểu được.

mr_hang · 7/1/09

trong công thức của Vlookup cái` đuôi của công thức thường có phẩy 2 só tự nhiên , vậy ý nghỉa của 2 số đó là sao trong hàm Vlookup???
CẢM ƠN!

volga · 8/1/09

mr_hang đã viết:
trong công thức của Vlookup cái` đuôi của công thức thường có phẩy 2 só tự nhiên , vậy ý nghỉa của 2 số đó là sao trong hàm Vlookup???
CẢM ƠN!

Bạn thử xem ở đây nhe ,mình không giám mạnh dạng giải thích cho bạn sợ sai ...http://www.giaiphapexcel.com/forum/showpost.php?p=48554&postcount=3

Thân

volga · 8/1/09

Các anh chị giải thích hô em công thức này ,
Nhiều lúc em tách công thức ra rồi bôi đen nhấn F9 ( Có cái thì ra kết quả ,có cái thì ra những con số lằn nhằn trong mảng em ko hiểu tí nào +-+-+-+

)với công thức này
IF(dh="HS Tiên tiến",hang*(1000+ROW(hang)),IF(dh="HS Giỏi",hang*(1000+ROW(hang)),""))
Em bôi đenphần hang*(1000+ROW(hang)) rồi nhấn F9 Thì nó ra thế này
{2006;9036;27135;11066;2014;20160;33297;3030;26286;44528;3039}
em trả hiểu gì +-+-+-+

có ai giải thích tỉ mỉ vấn đề này dùm em không ? em dốt thật đó +-+-+-+

Bài này em tham khảo từ link sau http://www.giaiphapexcel.com/forum/showthread.php?t=9158&page=2

BNTT · 8/1/09

volga đã viết:
Các anh chị giải thích hô em công thức này ,
Nhiều lúc em tách công thức ra rồi bôi đen nhấn F9 ( Có cái thì ra kết quả ,có cái thì ra những con số lằn nhằn trong mảng em ko hiểu tí nào )với công thức này
IF(dh="HS Tiên tiến",hang*(1000+ROW(hang)),IF(dh="HS Giỏi",hang*(1000+ROW(hang)),""))
Em bôi đenphần hang*(1000+ROW(hang)) rồi nhấn F9 Thì nó ra thế này
{2006;9036;27135;11066;2014;20160;33297;3030;26286;44528;3039}
em trả hiểu gì có ai giải thích tỉ mỉ vấn đề này dùm em không ? em dốt thật đó
Bài này em tham khảo từ link sau http://www.giaiphapexcel.com/forum/showthread.php?t=9158&page=2

Ngay trong cái link tham khảo của bạn đã có người giải thích rồi mà (bài số 14, của Viendo)

volga · 8/1/09

Em không hiểu những con số này khi (nhấn F9 mới có nó )

{2006;9036;27135;11066;2014;20160;33297;3030;26286 ;44528;3039}

!$@!!

ndu96081631 · 8/1/09

volga đã viết:
Em không hiểu những con số này khi (nhấn F9 mới có nó )

{2006;9036;27135;11066;2014;20160;33297;3030;26286 ;44528;3039}

Công thức mãng thường trả về NHIỀU GIÁ TRỊ chứ không phải 1 giá trị như công thức thường
Ở trên khi bạn bôi đen công thức rồi F9 nó cho biết mãng này có 11 phần tử... Mỗi phần tử là kết quà của quá trình tính toán cộng trừ nhân chia gì đó (dựa theo công thức)
Bạn hãy làm thí nghiệm sau:
- Gõ đại vào 1 cell nào đó công thức =ROW(5:10)
- Bôi đen công thức đó trên thanh Formula rồi F9 để xem nó ra cái gì
Ẹc... Ẹc...
Với công thức của bạn cũng thế, để hiểu nó bạn nên bôi đen từng phần nhỏ và F9 (bôi 1 lèo thì mệt lắm --> Khó hiểu)
----------------
Giờ giãi thích kỹ luôn đây:
Hang = Sheet1!$D$3:$D$13
Vậy Hang = {2;9;27;11;2;20;33;3;26;44;3}
Vậy Row(Hang) = 1 mãng 11 phần tử {3;4;5;6;7;8;9;10;11;12;13} ---> Vị trí dòng
Vậy 1000 + Row(Hang) = 1000 + {3;4;5;6;7;8;9;10;11;12;13} = {1003;1004;1005;1006;1007;1008;1009;1010;1011;1012;1013}
vậy hang*(1000+ROW(hang)) = ({2;9;27;11;2;20;33;3;26;44;3})*({1003;1004;1005;1006;1007;1008;1009;1010;1011;1012;1013})
={2006;9036;27135;11066;2014;20160;33297;3030;26286;44528;3039}
Tức từng em đôi một nhân với nhau
Thế thôi

anhtraitôi · 11/1/09

giúp mình với

mình có bài tập mà mãi khong kam được giúp mình với?
cám ơn nhiều

ndu96081631 · 11/1/09

anhtraitoi đã viết:
mình có bài tập mà mãi khong kam được giúp mình với?
cám ơn nhiều

Gõ vào cell D3 công thức sau:

PHP:

=VLOOKUP(LEFT($B3),$B$12:$E$16,(RIGHT($B3)="B")+3,0)

volga · 12/1/09

ndu96081631 đã viết:
Gõ vào cell D3 công thức sau:

PHP:

=VLOOKUP(LEFT($B3),$B$12:$E$16,(RIGHT($B3)="B")+3,0)

Trong công thức của anh có phần này khó hiểu ,không biết người hỏi có biết không ?sao không thấy hỏi ?em thì +-+-+-+

,
(RIGHT($B3)="B")+3 Phần này thì em bó tay .
như công thức này thì ,vế này nếu trả ra FALSE Thì lấy dử liệu ở cột thứ 3 .
Không lẻ ? FALSE+3 =3 ? Đúng nó bằng 3 thật %#^#$

sau nó ra như thế vậy anh ?
Còn TRUE+3= 4 Còn trường hợp này lại bằng 4 ? lấy dử liệu ở cột số 4 .
Em không hiểu +-+-+-+

?

cadafi · 12/1/09

volga đã viết:
Trong công thức của anh có phần này khó hiểu ,không biết tác giả có biết không ?sao không thấy hỏi ?em thì ,
(RIGHT($B3)="B")+3 Phần này thì em bó tay .
như công thức này thì ,vế này nếu trả ra FALSE Thì lấy dử liệu ở cột thứ 3 .
Không lẻ ? FALSE+3 =3 ? Đúng nó bằng 3 thật sau nó ra như thế vậy anh ?
Còn TRUE+3= 4 Còn trường hợp này lại bằng 4 ? lấy dử liệu ở cột số 4 .
Em không hiểu ?

Cái này giống như là tiên đề, bạn phải đồng ý như vậy thì mới hiểu được:

PHP:

Nếu như đặt False/True vào biểu thức toán học thì:
False= 0 và True = 1

Giống như 1+1=2 trong toán học đại số vậy!

Thanh Nam 2511 · 3/4/09

Lỗi cell khi sử dụng hàm ROUND - Không hiển thị thanh công thức

Thành viên "gà"nhỡ bác hỗ trợ
+ Ở ô trong excel không thực hiện được phép tính hàm ROUND
+ Mất thanh công thức trên Standard

volga · 3/4/09

Thanh Nam 2511 đã viết:
Thành viên "gà"nhỡ bác hỗ trợ
+ Ở ô trong excel không thực hiện được phép tính hàm ROUND
+ Mất thanh công thức trên Standard

Mất thanh công thức ?
+ Bạn vào Tools>>option>>View>>chọn Formula bar >>ok là xong !
+ Sau mình nhập công thức của bạn vào vẫn ok mà !
Bằng 6.043.784.1 ( CT là Round(H22*G23,1) )
Thân

chickencake1 · 7/4/09

Giải thích dùm cái hàm này dùm mình.

...
Đây là câu hỏi cho hàm này: Tính Phải Trả = Số Ngày Thuê * Đơn Giá Thuê, biết rằng: Trong số ngày từ Ngày Thuê xe đến Ngày Trả xe nếu có ngày Chủ Nhật thì Đơn Giá Thuê của ngày đó được tính gấp đôi
*Đây là hàm :=(INT((E5-D5-WEEKDAY(E5)+8)/7)+F5)*G5

---Các bạn giúp giải thích dùm mình hàm này với, mình đã đọc 1 số giải thích nhưng mình ko hiểu lắm. - Cám ơn -
<><><> Còn đây là file bài đó.

DOSNET · 7/4/09

chickencake1 đã viết:
...
Đây là câu hỏi cho hàm này: Tính Phải Trả = Số Ngày Thuê * Đơn Giá Thuê, biết rằng: Trong số ngày từ Ngày Thuê xe đến Ngày Trả xe nếu có ngày Chủ Nhật thì Đơn Giá Thuê của ngày đó được tính gấp đôi
*Đây là hàm :=(INT((E5-D5-WEEKDAY(E5)+8)/7)+F5)*G5

---Các bạn giúp giải thích dùm mình hàm này với, mình đã đọc 1 số giải thích nhưng mình ko hiểu lắm. - Cám ơn -
<><><> Còn đây là file bài đó.

Bạn cần tách công thức ra để hiểu thêm:

=INT((E5-D5-WEEKDAY(E5)+8)/7)

Đây là công thức tính số ngày chủ nhật giữa hai khoảng thời gian, giải thích tại sao lại tính như thế, tôi trích nguyên văn của tác giả:

anhtuan1066 đã viết:
Có nhiều cách đễ biết dc có bao nhiêu ngày CN giữa 2 thời điểm cho trước X, Y (Với X=<Y). Tất cả dựa trên nguyên tắc: "Trong 7 ngày liên tiếp có ít nhất 1 ngày CN". Có thể là 2 ngày CN nếu ngày đầu tiên cũng là ngày CN
Vậy bây giờ ta xét các kết quả sau:
1/- Xét phép chia: INT((A2-A2+1)/7) (Tạm gọi là kết quả (a))
2/- Xét phần dư MOD(A2-A1+1,7) (Tạm gọi là kết quả (b))
3/- Xét xem ngày CN đầu tiên là ngày thứ mấy so với móc thời gian X. Hoặc xét ngày CN cuối cùng cách bao nhiêu ngày so với móc thời gian Y (Tạm gọi đây là kết quả (c)).
Kinh nghiệm của tôi cho thấy nên xét ngày CN cuối cùng so với móc thời gian Y thì sẽ dễ cho việc tính toán hơn (Đễ ý weekday(A2) có liên quan đến ngày CN cuối cùng. Weekday(A2) = bao nhiêu thì ngày CN cuối cùng cách Y bấy nhiêu)
Cuối cùng ta có thể kết luận rằng: Nếu (c)=< (b) thì số ngày CN là (a)+1, ngược lại số ngày CN là (a)
VD: từ X đến Y có 23 ngày. Vậy (a)=3 và (b)=2. Vậy nếu như ngày CN đầu tiên nằm lọt trong khoảng 2 ngày đầu hoặc ngày CN cuối cùng cách Y nhỏ hơn 2 (nghĩa là (c)=<2) thì từ X đến Y sẽ có (a)+1= 4 ngày CN. Ngược lại thì từ X đến Y có 3 ngày CN
Cách suy luận là thế, còn tùy theo cách mỗi người tạo công thức như thế nào đó miễn sao có thể hiểu dc.
Chẳng hạn tôi cũng có thể ra 1 công thức dài hơn nhưng tương đối dễ hiểu hơn như sau:
=IF(MOD(A2-A1+1,7)>=WEEKDAY(A2),INT((A2-A1+1)/7)+1,INT((A2-A1+1)/7))

Như vậy diễn giải công thức của bạn:

INT((E5-D5-WEEKDAY(E5)+8)/7)=Số ngày chủ nhật
F5 : Tổng số ngày (có cả số ngày CN trong đó)

Vậy đã cộng 2 lần số ngày chủ nhật x đơn giá, tương đương với:

"nếu có ngày Chủ Nhật thì Đơn Giá Thuê của ngày đó được tính gấp đôi"

Hy vọng đến đây thì bạn đã hiểu

chickencake1 · 8/4/09

xiN LỖI , Phần + f5 để nhân đôi đơn giá thì mình hiểu.
--Nhưng cái công thức tính ngày chủ nhật , mình ko hiểu , bạn thông cảm có thể nói theo cách bạn hiểu về cái công thức đó ko ... Cám ơn bạn nhiều

huuthang_bd · 17/4/09

chickencake1 đã viết:
xiN LỖI , Phần + f5 để nhân đôi đơn giá thì mình hiểu.
--Nhưng cái công thức tính ngày chủ nhật , mình ko hiểu , bạn thông cảm có thể nói theo cách bạn hiểu về cái công thức đó ko ... Cám ơn bạn nhiều

A1 = 01/04/2009
B1 = 16/04/2009
C1 =INT((B1-A1-WEEKDAY(B1)+8)/7)
C1 =INT(((B1-WEEKDAY(B1)+7)-A1+1)/7)
WEEKDAY(B1): Thứ của ngày cuối cùng (B1) trả về dạng số CN - T7 tương đương 1 - 7.
B1-WEEKDAY(B1): Thứ 7 của tuần kề cuối.
B1-WEEKDAY(B1)+7: Thứ 7 của tuần cuối cùng.
B1-WEEKDAY(B1)+7)-A1+1: Số ngày từ ngày bắt đầu đến thứ 7 của tuần cuối cùng.
INT(((B1-WEEKDAY(B1)+7)-A1+1)/7): Từ thứ 7 của ngày cuối cùng đếm lùi về, mỗi 7 ngày có một ngày chủ nhật. Vì vậy, số ngày chủ nhật là phần nguyên của phép chia (B1-WEEKDAY(B1)+7)-A1+1)/7

Nhân tiện nói thêm:

Tất cả dựa trên nguyên tắc: "Trong 7 ngày liên tiếp có ít nhất 1 ngày CN". Có thể là 2 ngày CN nếu ngày đầu tiên cũng là ngày CN

Trong 7 ngày liên tiếp chỉ có duy nhất 1 ngày chủ nhật cho dù bắt đầu từ bất cứ ngày nào trong tuần.

paulsteigel · 20/4/09

Đố các bạn công thức sau để làm gì?

Có một công thức thế này, đố các bạn biết để làm gì nhé?

=CAPSTATE &": "&IF(ISERROR(FIND(".";TEXT(1000;"#.###,00");3)); TEXT(SUBTOTAL(9;N1STATE);"[Blue][>0] #.###,00_)"); TEXT(SUBTOTAL(9;N1STATE);"[Blue][>0] #,###.00_)"))

Trong đó CAPSTATE và N1STATE chỉ là tên vùng thôi, không có gì đặc biệt!
Câu hỏi chỉ có mục đích chia sẻ, kinh mong bà con cô bác không hiểu lầm mà tội iem!

huuthang_bd · 20/4/09

paulsteigel đã viết:
Có một công thức thế này, đố các bạn biết để làm gì nhé?

=CAPSTATE &": "&IF(ISERROR(FIND(".";TEXT(1000;"#.###,00");3)); TEXT(SUBTOTAL(9;N1STATE);"[Blue][>0] #.###,00_)"); TEXT(SUBTOTAL(9;N1STATE);"[Blue][>0] #,###.00_)"))

Trong đó CAPSTATE và N1STATE chỉ là tên vùng thôi, không có gì đặc biệt!
Câu hỏi chỉ có mục đích chia sẻ, kinh mong bà con cô bác không hiểu lầm mà tội iem!

ISERROR(FIND(".";TEXT(1000;"#.###,00");3)) Kiểm tra định dạng kiểu số theo kiểu dấu phân cách hàng nghìn là (.) và dấu phân cách thập phân là (,) (1)hay hay ngược lại (2).
IF(...) Nếu định dạng mặc định đang là kiểu (1) thì chuyển kết quả tổng sang định dạng text với dấu phân cách kiểu (1), nếu định dạng mặc định đang là kiểu (2) thì chuyển kết quả tổng sang định dạng text với dấu phân cách kiểu (2)
CAPSTATE &": "&IF(...) Hiển thị dòng tổng cộng dạng CAPSTATE: tổng (Với CAPSTATE là một dữ liệu nào đó của bạn, tổng là tổng của vùng N1STATE)
Toàn công thức: Tạo dòng tổng với nội dung CAPSTATE: tổng với tổng được định dạng có dấu phân cách theo kiểu mặc định của máy.
[Blue][>0] Chỗ này sai: Trong công thức, hàm text không thể hiện các định dạng màu; Nếu định dạng kiểu này chỉ có các số dương được định dạng có dấu phân cách, các số âm sẽ hiển thị thành số dương nhưng không có dấu phân cách.
Không biết có đúng với mục đích của bạn khi bạn sử dụng công thức này không?

paulsteigel · 20/4/09

Thanks chuyên gia!
Bác giải thích thế thì còn gì để iem nói nữa!
Đấy là cách em tính tổng và khi người dùng đổi cách định dạng hoặc chuyển tập tin sang máy có cách định dạng thập phân khác thì kết quả định dạng số liệu luôn đúng.
Cái vụ bỏ blue ... vào thì cài vào với mục đích "xấu" thôi ạ, như mà GPE lắm đại ca cao thủ quá nên bị lộ ngay!
Bác có biết còn cách nào để kết quả định dạng số luôn đúng không ạ?

huuthang_bd · 20/4/09

paulsteigel đã viết:
Thanks chuyên gia!
Bác giải thích thế thì còn gì để iem nói nữa!
Đấy là cách em tính tổng và khi người dùng đổi cách định dạng hoặc chuyển tập tin sang máy có cách định dạng thập phân khác thì kết quả định dạng số liệu luôn đúng.
Cái vụ bỏ blue ... vào thì cài vào với mục đích "xấu" thôi ạ, như mà GPE lắm đại ca cao thủ quá nên bị lộ ngay!
Bác có biết còn cách nào để kết quả định dạng số luôn đúng không ạ?

Nếu CAPSTATE của bạn là một chuỗi cố định thì có thể sử dụng Format không cần công thức.
Xem ví dụ trong file đính kèm.

anhtuan1066 · 20/4/09

Đôi khi chúng ta cũng có nhu cầu lấy dấu phân cách ngàn và thập phân để làm công việc gì đó... Chúng ta thường tự nhận định rằng: "Nếu dấu thập phân ko phải là dấu phẩy thì sẽ là dấu chấm... Nếu dấu phân cách ngàn ko phải là dấu chấm thì sẽ là dấu phẩy"
Điều này hoàn toàn sai lầm... Ai biết được có tay nào cắc cớ xài dấu phân cách ngàn là dấu / thì sao? (chuyện này tôi đã từng gặp vài lần)
Vì thế giải pháp toàn diện phải là lấy cho bằng được những Setting hiện hành trong Control Panel.
Đương nhiên với VBA thì việc làm này quá dể. Ở đây chúng ta chỉ nói các giải pháp thuộc về công thức... Và với sự hổ trợ của các hàm macro 4, điều này hoàn toàn có thể thực hiện dc
Chúng ta tiến hành như sau:
1> Đặt 2 name

PHP:

DecSym =INDEX(GET.WORKSPACE(37),,3)

PHP:

DigSym =INDEX(GET.WORKSPACE(37),,4)

2> Định dạng
Với 1 số cho trước, nếu các bạn muốn định dạng số này có dấu phân cách ngàn và 2 số thập phân thì làm như sau:

PHP:

=TEXT(A1,"#"&DigSym&"##0"&DecSym&"00")

3> Thử nghiệm:
Sau khi vào Control Panel và tinh chỉnh, ngay lúc các bạn bấm Apply lần thứ 2 thì lập tức mọi thứ sẽ được cập nhật trên bảng tính
Xem file đính kèm

chickencake1 · 21/4/09

ai giúp mình làm bài này với

BNTT · 21/4/09

chickencake1 đã viết:
ai giúp mình làm bài này với

Bài này đã được giải ở đây:
http://www.giaiphapexcel.com/forum/showthread.php?p=125709#post125709

nickyau · 16/6/09

Mình mới học excel nên cần giúp đỡ....câu 1 sheet 9

salam · 16/6/09

nickyau đã viết:
Mình mới học excel nên cần giúp đỡ....câu 1 sheet 9

Ông bạn vất vả quá.

Mã:

=IF(ISNA(VLOOKUP(B4,$B$14:$D$17,2,0)),"Bóng đèn đặc biệt",VLOOKUP(B4,$B$14:$D$17,2,0))

kellyfe · 16/6/09

tạm thơila2 như vậy nha bạn

ndu96081631 · 16/6/09

kellyfe đã viết:
tạm thơila2 như vậy nha bạn

Trời à! Công thức của bạn:

PHP:

=IF(ISNA(VLOOKUP(B4,$B$14:$D$16,1,0)=TRUE),"Bóng đèn đặc biệt",IF(VLOOKUP(B4,$B$14:$D$16,1,0)=B4,VLOOKUP(B4,$B$14:$D$16,2,0)))

Đã bẩy lổi trường hợp TÌM KHÔNG THẤY thì cho kết quả = "Bóng đèn đặc biệt" ---> Vậy sau đó ta VLOOKUP với cột dò = 2 luôn, cần gì đoạn này:

PHP:

IF(VLOOKUP(B4,$B$14:$D$16,1,0)=B4

Quá thừa phải không?
Làm giống Salam ấy!
Mặc khác, đoạn ISNA cũng không cần =TRUE ---> Bạn bỏ chữ này nó cũng hiểu là =TRUE rồi ---> Không tin cứ xóa =TRUE xem thử
Ngoài ra cũng có thể dùng COUNTIF

PHP:

=IF(COUNTIF($B$14:$B$16,$B4),VLOOKUP(B4,$B$14:$D$16,2,0),"Bóng đèn đặc biệt")

nickyau · 16/6/09

rồi ok,cảm ơn các bác nha,thấy rất vui vì bài vừa post chỉ mới hồi sáng thôi mà được quan tâm giúp đỡ như thế,thật cảm ơn

ndu96081631 đã viết:
Trời à! Công thức của bạn:

PHP:

=IF(ISNA(VLOOKUP(B4,$B$14:$D$16,1,0)=TRUE),"Bóng đèn đặc biệt",IF(VLOOKUP(B4,$B$14:$D$16,1,0)=B4,VLOOKUP(B4,$B$14:$D$16,2,0)))

Đã bẩy lổi trường hợp TÌM KHÔNG THẤY thì cho kết quả = "Bóng đèn đặc biệt" ---> Vậy sau đó ta VLOOKUP với cột dò = 2 luôn, cần gì đoạn này:

PHP:

IF(VLOOKUP(B4,$B$14:$D$16,1,0)=B4

Quá thừa phải không?
Làm giống Salam ấy!
Mặc khác, đoạn ISNA cũng không cần =TRUE ---> Bạn bỏ chữ này nó cũng hiểu là =TRUE rồi ---> Không tin cứ xóa =TRUE xem thử
Ngoài ra cũng có thể dùng COUNTIF

PHP:

=IF(COUNTIF($B$14:$B$16,$B4),VLOOKUP(B4,$B$14:$D$16,2,0),"Bóng đèn đặc biệt")

mình thấy cái công thức countif này có vẻ dễ nhai hơn vì mình cũng chỉ mới học tới countif nhưng không rành áp dụng lắm,còn cái công thức để bẫy lỗi khó hiểu quá nên mình xin khiếu |||||

mình vẫn không hiểu ở công thức này IF(COUNTIF($B$14:$B$16,$B4),VLOOKUP(B4,$B$14:$D$16,2,0),"Bóng đèn đặc biệt"),cái B4 đó fải F4 2 3 lần gì đó,nó có nghĩa là gì vậy ?

hoangdd87 · 23/6/09

Chào các bạn mình đang tự học exel thông qua các ví dụ mình tìm được.
Mình có một bài đính kèm ở đây, trong đó đã có lời giải ở sheet bai giai nhưng mình không hiểu chỗ ô thống kê thành tiền Sony 14' hoặc Sony 21' hoặc Panasonic ... đều có công thức {=TABLE(K25;J26)} (sheet bai giai ô G26, G27, H26, H27) mình không hiểu thế nào. Mình tìm hiểu được biết {} là công thức mảng nhưng TABLE là gì ? Có vẻ nó không phải là hàm. Tại sao các ô thống kê khác nhau lại chung một công thức mà vẫn ra được?

Mong các bạn giải đáp giúp mình.
Thanks

ptm0412 · 24/6/09

Mình tìm hiểu được biết {} là công thức mảng nhưng TABLE là gì ? Có vẻ nó không phải là hàm. Tại sao các ô thống kê khác nhau lại chung một công thức mà vẫn ra được?

Table không phải là 1 hàm, do đó không phải công thức mảng. Đó là chức năng lấy từ Menu - Data - Table.

Bạn xem bài nói về Table(), từ bài 9 trở xuống.

Cái cách giải trong file của bạn khó hơn so với thí dụ của các bài trên, nhưng cùng phương pháp.

nhandong · 3/7/09

tôi gặp rắc rối muốn nhờ các bạn trong diễn đàn giúp: tôi tính tổng mà sau khi gõ công thúc và enter chỉ thấy ô hiện số 0, công thúc vẫn hiển thị trên thanh công cụ. Rất mong trợ giúp...Địa chỉ của tôi:ngan.phanthikim12@gmail.com. Xin đwợc cám ơn trwớc !

tuan73 · 24/7/09

Các bạn cho tôi hỏi, trong công thức: LOC=IF(NHOM=$I$1,ROW(INDIRECT("1:"&COUNTA(NHOM))),"")
thì đoạn "1:"&counta(NHOM), ở đây "1:" mục đích để làm gì vậy? Theo tôi hiểu thì trong hàm indirect thì đòi hỏi trong công thức phải có ký tự thể hiện cột chứ. Mong các bạn giải thich hộ tôi. Cảm ơn.

volga · 25/7/09

Các Anh !

Em tham gia diễn đàn củng khá lâu rồi ,nhưng thật tình khả năng của em còn lè tè thôi .
Nhìn những công thức của bạn HuuThang_bd thật sự em không theo nỗi hu hu.Không thể nào hiểu ý của công thức ,tác dụng công thức .... +-+-+-+

Link bài viết

Mã:

=IF((Sheet1!$C$2:$C$100="")+(Sheet1!$E$2:$E$100=""),"",IF(MATCH(Sheet1!$C$2:$C$100&"x",Sheet1!$C$2:$C$100&Sheet1!$E$2:$E$100,)=ROW(Sheet1!$C$2:$C$100)-ROW(Sheet1!$C$1),ROW(Sheet1!$C$2:$C$100)-ROW(Sheet1!$C$1),""))

Công thức trên em nghỉ dùng để lọc ra danh sách duy nhất ?

Mã:

MATCH(Sheet1!$C$2:$C$100&"x",Sheet1!$C$2:$C$100&Sheet1!$E$2:$E$100,)

Cái này em không hiểu

Mã:

Sheet1!$C$2:$C$100&"x"

Tìm kèm theo dấu "x" . x này ở đâu ?cột E à? Không có địa chỉ sao công thức hiểu ?
Công thức này lại càng không biết . +-+-+-+

Mã:

=(Sheet1!$C$2:$C$100<>Sheet1!$G35)+(Sheet1!$E$2:$E$100="") [COLOR=Red]""[/COLOR] MATCH(Sheet1!$A$2:$A$100&Sheet1!$G35&"x",Sheet1!$A$2:$A$100&Sheet1!$C$2:$C$100&Sheet1!$E$2:$E$100,)=ROW(Sheet1!$C$2:$C$100)-ROW(Sheet1!$C$1)!ROW(Sheet1!$C$2:$C$100)oSheet1!

Liên kết giữa các vế công thức không có dấu (Ngay chỗ bôi đỏ ),những dấu ô vuông ?chử o ?
Nhờ các anh chỉ giúp dùm em với ,khi nào em mới khá nỗi đây????
Nhức đầu quá ...... **~**

Anh đọc ra luôn dùm em công thức bằng chử với có lẻ như vậy em nhanh hiểu hơn .Khả năng đọc công thức của em còn quá yếu ...
Thanks All !!!!!!!!!

nguyen_khai · 3/10/09

Chào các bạn, mình có vấn đề này mong các bạn giúp. Trong công thức excel sau khi tạo xong công thức để tạo dấu { } thì sử dụng tổ hợp phím nào, lâu rồi ko sử dụng nên quên mất, và để mặc định màu sắc cho chữ hay số thì làm thế nào? Ví dụ ta gõ chữ N thì tự động nó hiện màu đỏ hay xanh. Mong được sự giúp do cua cac ban. mong dc reply

salam · 3/10/09

nguyen_khai đã viết:
Chào các bạn, mình có vấn đề này mong các bạn giúp. Trong công thức excel sau khi tạo xong công thức để tạo dấu { } thì sử dụng tổ hợp phím nào, lâu rồi ko sử dụng nên quên mất, và để mặc định màu sắc cho chữ hay số thì làm thế nào? Ví dụ ta gõ chữ N thì tự động nó hiện màu đỏ hay xanh. Mong được sự giúp do cua cac ban. mong dc reply

1.Công thức mảng bạn dùng tổ hợp phím ctrl+shift+ẻnte.
2.Bạn dùng condition format.

chickp · 29/10/09

Mình có công thức này:

PHP:

TRIM(SUBSTITUTE(SUBSTITUTE(MID(I5,SEARCH("ông",SUBSTITUTE(I5,"bà","ông")),SEARCH("Sinh",I5)-SEARCH("Ông",SUBSTITUTE(I5,"bà","ông"))),"ông",""),"bà",""))

bạn nào giải thích cho mình ý nghĩa của nó với nhé.
Nếu mình muốn thêm vào công thức đó một vài tiêu chí nữa như:
1. "cô" vào phần tìm kiếm "ông, "bà"
2. "nghề nghiêp" vào phần tìm kiếm "Sinh"
thì mình cần sửa lại công thức như thế nào?
Rất mong các bạn giúp đỡ.

duongdv · 29/10/09

chickp đã viết:
Mình có công thức này:
TRIM(SUBSTITUTE(SUBSTITUTE(MID(I5,SEARCH("ông",SUBSTITUTE(I5,"bà","ông")),SEARCH("Sinh",I5)-SEARCH("Ông",SUBSTITUTE(I5,"bà","ông"))),"ông",""),"bà",""))
bạn nào giải thích cho mình ý nghĩa của nó với nhé.

Trong công thức này sử dụng toàn các hàm cơ bản mà không có ông thức nào trìu tượng và khó hiểu. Bạn xem lại nhóm hàm về xử lý chuỗi ký tự là biết liền.

nghiavan · 31/10/09

có anh chi nào giúp em với. em đang lam 1 thẻ kho. vì muốn biết tổng xuất của từng người trong 1 tháng, nên em phải cộng cell từ sheet 1 đến sheet 31 bằng cách này :

PHP:

='1'!I7+'2'!I7+'3'!I7+'4'!I7+'5'!I7+'6'!I7+'7'!I7 +'8'!I7+'9'!I7+'10'!I7+'11'!I7+'12'!I7+'13'!I7+'14 '!I7+'15'!I7+'16'!I7+'17'!I7+'18'!I7+'19'!I7+'20'! I7+'21'!I7+'22'!I7+'23'!I7+'24'!I7+'25 bs'!I7+'26'!I7+'27'!I7+'28'!I7+'29'!I7+'30'!I7+'31 '!I7

. em thấy làm như vậy quá mất công, vả lại nếu sang tháng khác thì củng phải như vậy. có hàm nào để xử lý không , giúp em với

http://www.giaiphapexcel.com/forum/showthread.php?do=post_thanks_add&p=198994

ptm0412 · 31/10/09

=Sum('1:31'!I17)

Điều kiện là không có sheet nào xen giữa 31 sheet trên.

bbq1401 · 7/11/09

Sửa em công thức này

Em làm hoài vẫn không ra kết quả
Mong GPE giúp đỡ

Thank

ngocmaipretty · 8/11/09

Em làm hoài vẫn không ra kết quả
Mong GPE giúp đỡ

Lỗi do cột E không phải hoàn toàn số nên không Sum được.

Thứ nhất: vùng dữ liệu chỉ nên lấy từ dòng 4 của sheet, dòng 3 là tiêu đề nên E3 là text
Thứ hai: Các số lớn hơn 1000 hiện đang là text dạng "1 200" (có khoảng trắng)

Sửa xong dùng công thức như sau, chú ý các dấu $ để có thể copy ngang và dọc.

=SUMPRODUCT((Tonkho!$A$4:$A$2832=$B4)*(Tonkho!$C$4:$C$2832=E$3)*(Tonkho!$E$4:$E$2832))

tommybull · 24/11/09

Xin giúp đỡ về hàm

Mình có file đính kèm
mong các anh chị giúp đỡ về hàm trong excel

hoangminhtien · 24/11/09

Tại F10 bạn nhập công thức sau

F10=(B10=0)*200000+(B10=1)*135000+(C10=0)*200+(C10=1)*170000+(C10=2)*120000+(D10=0)*200000+(D10=1)*185000+(D10=2)*170000

yeuthamhangxom · 24/11/09

hoangminhtien đã viết:
Tại F10 bạn nhập công thức sau

không được bạn ạ. Mình thử nhập A=0, B=0, C=0 thì thưởng là 600000 nhưng của bạn là 400200.

ChanhTQ@ · 24/11/09

Hàm CHOOSE(), như sau

=1000*(choose(b10+1,200,135,0)+choose(c10+1,200,185,170,0)+choose(d10+1,200,185,170,0))

Ngắn thêm 1 tí tẹo:

=10^5*(CHOOSE(B10+1,2,1.35,0)+CHOOSE(C10+1,2,1.85,1.7,0)+CHOOSE(D10+1,2,1.85,1.7,0))

hoangminhtien · 24/11/09

yeuthamhangxom đã viết:
không được bạn ạ. Mình thử nhập A=0, B=0, C=0 thì thưởng là 600000 nhưng của bạn là 400200.

Mình gõ nhầm bạn ạ, xin lỗi nhé! bạn chỉnh lại như sau

F10=(B10=0)*200000+(B10=1)*135000+(C10=0)*200000+(C10 =1)*170000+(C10=2)*120000+(D10=0)*200000+(D10=1)*1 85000+(D10=2)*170000

Ba Tê · 24/11/09

ChanhTQ@ đã viết:
=1000*(choose(b10+1,200,135,0)+choose(c10+1,200,185,170,0)+choose(d10+1,200,185,170,0))

Ngắn thêm 1 tí tẹo:

=10^5*(CHOOSE(B10+1,2,1.35,0)+CHOOSE(C10+1,2,1.85,1.7,0)+CHOOSE(D10+1,2,1.85,1.7,0))

Chắc Bạn nhìn lầm các con số, đúng ra phải là vầy:

Mã:

=1000*(choose(b10+1,200,135,0)+choose(c10+1,200,[COLOR=red][B]170,120[/B][/COLOR],0)+choose(d10+1,200,185,170,0))

Ngắn thêm 1 tí tẹo:

Mã:

=10^5*(CHOOSE(B10+1,2,1.35,0)+CHOOSE(C10+1,2,[COLOR=red][B]1.7,1.2[/B][/COLOR],0)+CHOOSE(D10+1,2,1.85,1.7,0))

dat_butmuc · 24/11/09

tommybull đã viết:
Mình có file đính kèm
mong các anh chị giúp đỡ về hàm trong excel

Với bài những bài kiểu này bạn nên theo các cách của các cao thủ như ChanhTQ, Hoangminhtien hay Batê,...
Tuy nhiên nến nhiều điều kiện hơn thì bạn nên làm một bảng phụ của các điều kiện cho dễ xử lý,
Tôi đã tách bài của bạn ra một bảng phụ, cũng có nhiều cách và các cao thủ sử dụng các hàm sumproduct, offset, index, match,...rất chi là hay, nếu thêm điều kiện và dữ liệu vào cũng chẳng sao?
Trình độ tôi có hạn, tạm sử dụng Sumproduct vậy (sau khi có bảng phụ)
Bạn xem file đính kèm nhé!

huuthang_bd · 24/11/09

Công thức này:

F10=(B10=0)*200000+(B10=1)*135000+(C10=0)*200000+(C10 =1)*170000+(C10=2)*120000+(D10=0)*200000+(D10=1)*1 85000+(D10=2)*170000

Có thể rút gọn lại như thế này:

=(COUNTIF(B1010,0)*2+(B10=1)*1.35+(C10=1)*1.7+(C10=2)*1.2+(D10=1)*1.85+(D10=2)*1.7)*10^5

Công thức này:

=10^5*CHOOSE(B10+1,2,1.35,0)+CHOOSE(C10+1,2,1.7,1.2,0)+CHOOSE(D10+1,2,1.85,1.7,0))

Chưa đúng, nếu một số nào đó nằm ngoài số giá trị của hàm Choose() (5 chẳng hạn) sẽ bị lỗi #VALUE! Nếu dùng hàm Choose() thì phải sửa lại thế này:

=1000*CHOOSE(MIN(B10+1,3),200,135,0)+CHOOSE(MIN(C10+1,4),200,170,120,0)+CHOOSE(MIN(D10+1,4),200,185,170,0))

Ngoài ra có thể dùng công thức này:

=(LOOKUP(B10,{0,1,2},{2,1.35,0})+LOOKUP(C10,{0,1,2,3},{2,1.7,1.2,0})+LOOKUP(D10,{0,1,2,3},{2,1.85,1.7,0}))*10^5

Hoặc công thức mảng này:

{=(SUM((B1010=0)*2+(B1010=1)*{1.35,1.7,1.85}+(B1010=2)*{0,1.2,1.7}))*10^5}

dat_butmuc · 24/11/09

huuthang_bd đã viết:
Công thức này:
Có thể rút gọn lại như thế này:
Công thức này:
Chưa đúng, nếu một số nào đó nằm ngoài số giá trị của hàm Choose() (5 chẳng hạn) sẽ bị lỗi #VALUE! Nếu dùng hàm Choose() thì phải sửa lại thế này:
Ngoài ra có thể dùng công thức này:
Hoặc công thức mảng này:

Công thức này; =1000*CHOOSE(MIN(B10+1,3),200,135,0)+CHOOSE(MIN(C10+1,4),200,170,120,0)+CHOOSE(MIN(D10+1,4),200,185,170,0))
thiếu dấu "(" sau 1000
Thêm 1 tý đã nêu trên...=1000*(CHOOSE(MIN(B10+1,3),200,135,0)+CHOOSE(MIN(C10+1,4),200,170,120,0)+CHOOSE(MIN(D10+1,4),200,185,170,0))

SA_DQ · 24/11/09

Nếu anh khùng nào nhập số âm vô thì sao mà khử lỗi được ta?!

from huuthang
công thức này:
=10^5*choose(b10+1,2,1.35,0)+choose(c10+1,2,1.7,1. 2,0)+choose(d10+1,2,1.85,1.7,0))
chưa đúng, nếu một số nào đó nằm ngoài số giá trị của hàm choose() (5 chẳng hạn) sẽ bị lỗi #value! Nếu dùng hàm choose() thì phải sửa lại thế này:
=1000*choose(min(b10+1,3),200,135,0)+choose(min(c10+1,4),200,170,120,0)+choose(min(d10+1,4),200,185,170,0))

? ?

Ba Tê · 25/11/09

HYen17 đã viết:
Nếu anh khùng nào nhập số âm vô thì sao mà khử lỗi được ta?!
? ?

Đề bài tác giả chỉ sử dụng các số 0,1,2,3 trở lên... Các bạn đã làm giúp theo ý tưởng trên. Nếu có anh khùng nào đó nhập số âm thì cứ để lỗi cho anh khùng này biết mình đã bị lỗi gì đó mà xem lại, tôi nghĩ không nên khử lỗi dùm anh khùng này. Khử lỗi chẳng giúp gì cho anh ta.

thaopink · 1/12/09

công thức bị đóng băng

Giúp em với các bác ơi , chẳng là thế này : em nhập công thức từ sổ quỹ tiền mặt = =Data!F63 rồi nhấn Enter mà nó vẫn dữ nguyên công thức như vậy không hiện đáp án.
Đó là lỗi gì vậy , giúp em với , em đang cần gấp lắm .

dat_butmuc · 1/12/09

thaopink đã viết:
Giúp em với các bác ơi , chẳng là thế này : em nhập công thức từ sổ quỹ tiền mặt = =Data!F63 rồi nhấn Enter mà nó vẫn dữ nguyên công thức như vậy không hiện đáp án.
Đó là lỗi gì vậy , giúp em với , em đang cần gấp lắm .

Bạn đang mặc định ô bạn dạng Text
Ctrl+1 -> Chọn General hoặc Number ->Ok
Sau đó thực hiện lại công thức của bạn

DichVuHuyenTrang · 7/12/09

Nhờ các bác help giúp. Tôi đang cần gấp về countif.
theo một điều kiện cho trước theo file đính kèm. Nhờ các bác giúp.
Thanks nhiều

MinhCong · 7/12/09

DichVuHuyenTrang đã viết:
Nhờ các bác help giúp. Tôi đang cần gấp về countif.
theo một điều kiện cho trước theo file đính kèm. Nhờ các bác giúp.
Thanks nhiều

Bạn xem file có đúng ý chưa nhé!

khanh_yeumuon · 7/1/10

Hàm OFFSET có đối số row và colum là các cự ly khoảng cách tham chiếu. Nếu phải tham chiếu đến Sheet khác thì nhập thế nào đây? Mình thử mấy lần mà nó vẫn báo lỗi. Cảm ơn các bạn đã đọc và giúp mình

hoangdanh282vn · 7/1/10

khanh_yeumuon đã viết:
Hàm OFFSET có đối số row và colum là các cự ly khoảng cách tham chiếu. Nếu phải tham chiếu đến Sheet khác thì nhập thế nào đây? Mình thử mấy lần mà nó vẫn báo lỗi. Cảm ơn các bạn đã đọc và giúp mình

VD bạn đang ở sheet1, bạn muốn dùng hàm offset cho sheet2 thì tham số Reference bạn chọn tham chiếu ở sheet2 là được.

VD tại A1 của sheet1 bạn sử dụng hàm Offset tham chiếu đến sheet2 :

=Offset(Sheet2!A1,,,counta(Sheet2!$A$1:$A$100),2)

cuong0202 · 15/5/10

Nhờ các anh chị xem dùm em ,, công thức em sai chỗ nào mà bị báo lỗi ah,,, va hướng dẫn em cách sửa,, xin cảm ơn.

ptm0412 · 15/5/10

Công thức của bạn không sai gì cả. Vấn đề là 2 ô A178 và A179 bên sheet Data bị lỗi #Value! nên làm bất kỳ công thức nào dính đến cột A đều bị lỗi.

Gợi ý: bạn nên đặt các name động cho nhẹ máy.

cuong0202 · 15/5/10

EM không biết như thế nào gọi là name động ah,, nhờ mọi người giúp đỡ vậy .,,thank

BNTT · 15/5/10

cuong0202 đã viết:
EM không biết như thế nào gọi là name động ah,, nhờ mọi người giúp đỡ vậy .,,thank

Name động là Name tự động thay đổi địa chỉ tham chiếu của nó.

Ví dụ, nếu bạn đặt Name = $A$1:$A$10 thì đó là Name tĩnh

Còn nếu bạn đặt Name = OFFSET($A$1,,,COUNTA(A1:A1),) thì bạn sẽ thấy tùy thuộc vào số ô có dữ liệu trong cột A, mà Name này sẽ tự "phình to" theo.

Đây chỉ là một ví dụ nhỏ. Bạn có thể tìm kiếm trên diễn đàn, hoặc bằng Google, với từ khóa "Name động", sẽ có nhiều ví dụ hơn để bạn nghiên cứu.

lequangminh · 15/5/10

cuong0202 đã viết:
EM không biết như thế nào gọi là name động ah,, nhờ mọi người giúp đỡ vậy .,,thank

Name động là tên được đặt cho công thức tại Difine name. Nó được tạo ra như khi bạn đặt tên mảng vậy thôi.
Ví dụ: Như muốn tham chiếu đến hàng hoạc cột nào đó bạn dùng công thức là Vlookup, hoặc Hlookup thì mình sẽ đặt tên cho cả hàm đó là 1 tên như trong file mình gửi kèm. Bạn xem file và ấn Ctrl+F3 nhe!

cuong0202 · 17/5/10

em có file nhap xuất tồn em làm được công thức sumproduct cho cột nhập ,, nhưng không làm được cho cột xuất , nhờ mọi người giúp đỡ ah ,,
điều kiện của cột xuất giống như của cột nhập nhưng thêm một cái là dựa vào 2 ký tự " PX " hoặc " PT "
em co dính kèm file ,, nhờ mọi người ,,,, thank !!!!!!!!

Lê Duy Thương · 17/5/10

cuong0202 đã viết:
em có file nhap xuất tồn em làm được công thức sumproduct cho cột nhập ,, nhưng không làm được cho cột xuất , nhờ mọi người giúp đỡ ah ,,
điều kiện của cột xuất giống như của cột nhập nhưng thêm một cái là dựa vào 2 ký tự " PX " hoặc " PT "
em co dính kèm file ,, nhờ mọi người ,,,, thank !!!!!!!!

đặt sumproduct này thử xem
tuy nhiên nếu dữ liệu trên 2000 rows thì máy chay không được nhanh đâu nhé
G5=SUMPRODUCT((LEFT(data!$A$5:$A$600,2)="px")*(data!$C$5:$C$600=stores!$A5),data!$H$5:$H$600)

cuong0202 · 17/5/10

không được ah,, cột xuất của em phải kèm 2 điều kiện ,, có nghĩa là khi cột chứng từ = PX (phiếu xuất ) hay = PT ( phiếu trả hàng ) thì điều dựa vào điều kiện đó mà trừ số lượng bên cột xuất ,,
và dữ liệu của em thì rất là nhiều.. không biết mấy anh chị có giải pháp nào giúp em không ạh,, co tháng lên tới gần 2000 dòng ah .. thank ,,

BNTT · 17/5/10

cuong0202 đã viết:
không được ah,, cột xuất của em phải kèm 2 điều kiện ,, có nghĩa là khi cột chứng từ = PX (phiếu xuất ) hay = PT ( phiếu trả hàng ) thì điều dựa vào điều kiện đó mà trừ số lượng bên cột xuất ,,
và dữ liệu của em thì rất là nhiều.. không biết mấy anh chị có giải pháp nào giúp em không ạh,, co tháng lên tới gần 2000 dòng ah .. thank ,,

Đây là cái NK của bạn:

NK =SUMPRODUCT((MH=stores!$A5)*(MHCT=stores!$B5)*(LEFT(CT,2)="PN")*SL)

Để tính ra số hàng xuất, mà theo bạn là: cột chứng từ = PX (phiếu xuất) hay = PT (phiếu trả hàng), chỉ cần thay cái màu đỏ ở trên đây, đặt cho nó cái Name là XK chẳng hạn:

XK =SUMPRODUCT((MH=stores!$A5)*(MHCT=stores!$B5)*((LEFT(CT,2)="PX")+(LEFT(CT,2)="PT"))*SL)

Rồi. Bạn thử gõ bên cột xuất: =XK xem nó ra kí rì nha.

dungk51cnmt · 18/5/10

giải thích hộ em ý nghĩa các hàm

em muốn hiểu ý nghĩa các hàm này nó như thế nào? ứng dug nữa
độ lệch chuẩn?
phân bố chuẩn?
standardized residual đồ thị trong hàm hồi quy bội nhiều biến số? em cũng muốn biết thêm thông tin về hàm hồi quy này nữa
em chả hiểu nó là như thế nào đc
anh chị có thể giải thích giùm em được k?

BNTT · 18/5/10

dungk51cnmt đã viết:
em muốn hiểu ý nghĩa các hàm này nó như thế nào? ứng dug nữa
độ lệch chuẩn?
phân bố chuẩn?
standardized residual đồ thị trong hàm hồi quy bội nhiều biến số? em cũng muốn biết thêm thông tin về hàm hồi quy này nữa
em chả hiểu nó là như thế nào đc
anh chị có thể giải thích giùm em được k?

Các hàm này là các hàm nào?
Nếu bạn biết tên hàm, bạn có thể tra cứu trong link ở chữ ký của tôi.

cuong0202 · 19/5/10

may anh chị chỉ dùm em cách làm như thế nào khi em lỡ tay nhấn cái ji đó mà công thức trong excel của em nó hiện lên hết không cho ra giá trị hichic
vd : C1=left(B3,2)="PN" thay vi cho ra AB no lai hiện ra là C1=left(B3,2)="PN" hicichci

ndu96081631 · 19/5/10

cuong0202 đã viết:
may anh chị chỉ dùm em cách làm như thế nào khi em lỡ tay nhấn cái ji đó mà công thức trong excel của em nó hiện lên hết không cho ra giá trị hichic
vd : C1=left(B3,2)="PN" thay vi cho ra AB no lai hiện ra là C1=left(B3,2)="PN" hicichci

- Chọn cell ấy
- Bấm tổ hợp phím Ctrl + Shift + ~
- Chọn cell ấy, bấm F2 rồi Enter

cuong0202 · 19/5/10

Rồi nhưng cũng không được,, nhờ mọi người vậy,, cảm ơn ,,!!!!!

ndu96081631 · 19/5/10

cuong0202 đã viết:
Rồi nhưng cũng không được,, nhờ mọi người vậy,, cảm ơn ,,!!!!!

File của bạn chẳng bị gì, chỉ là trong Tools\Options... tab View bạn đang check vào mục Formulas
Vậy có 2 cách khắc phục:
- Hoặc vào Tools\Options... tab View bỏ check mục Formulas
- Hoặc bấm Ctrl + ~
(Cách 2 nhanh hơn... Chắc lúc trước đã lở tay bấm Ctrl + ~ 1 lần)

dungk51cnmt · 21/5/10

em muốn hỏi 1 chút thông tin về minitab được k ạ? em tìm mãi trên mạng mà thôg tin về sử dụng nó ít quá. tiếng anh đọc mà chả hiểu gì.
http://www.mediafire.com/?3mgnmjbgwtz
đây là file em làm ra trên minitab anh chị có thể giải thích giùm em cái biểu đồ biêu thị nó đc k ạ?

makio123 · 29/6/10

Công thức này [B1] =IF(A1>10, 100, 0) và công thức [B1] =(A1>100)*100 không thay thế được cho nhau vì theo hàm IF nếu ô A1 = 100 thì kết quả ô B1 là 100 nhưng theo CT [B1] =(A1>100)*100 thì kết quả ô B1 = 0

BNTT · 29/6/10

makio123 đã viết:
Công thức này [B1] =IF(A1>10, 100, 0) và công thức [B1] =(A1>100)*100 không thay thế được cho nhau vì theo hàm IF nếu ô A1 = 100 thì kết quả ô B1 là 100 nhưng theo CT [B1] =(A1>100)*100 thì kết quả ô B1 = 0

Không biết bạn chép lại những cái công thức đó từ đâu. Tuy nhiên tôi nghĩ bạn chép sai (hoặc nguyên gốc nó sai), dư hoặc thiếu 1 số 0.

Nếu trong công thức (1) đúng, A1>10, thì ở công thức (2), cũng chỉ là A1>10 chứ không phải là A1>100 đâu.

Còn không, thì ở công thức (1), A1>100 chứ không phải là A1>10, nếu như công thức (2) là đúng.

thuydungbdp · 30/9/10

chào anh, cho em hỏi một chút. Em thấy nhập số vào một cell bất kỳ rồi vừa nhấn ctrl+ giữ chuột kéo xuống thì số cũng tăng dần mà

hanglinh70 · 1/10/10

xin vui lòng chỉ dẫn
- CH 1: Dùng công thức để đếm số người thỏa mãn điều kiện tuổi (sau khi đã lọc bằng Custom Autofilter)
- CH 2: Nếu có nhiều cột, lọc theo nhiều điền kiện, công thức trên vẫn đúng và Luôn ở sát dưới dòng cuối cùng của các giá trị thỏa mã điều kiện

nomater · 6/10/10

SOS: cho mình hỏi hàm sum này với!

mình add file rồi! nhờ các bạn giúp mình, đi bộ đội về, chả làm được việc gì, buồn bã quá! +-+-+-+

MinhCong · 6/10/10

nomater đã viết:
mình add file rồi! nhờ các bạn giúp mình, đi bộ đội về, chả làm được việc gì, buồn bã quá!

Có phải Bạn muốn lọc ra những giá trị trùng sau đó tính tổng nó không? Nếu Bạn muốn tìm Tổng giá trị của 1 mã nào đó thì dùng Hàm SUMIF thôi. Hoặc Bạn dùng Pivot table nhé! Xem file đính kèm.

dat_butmuc · 6/10/10

nomater đã viết:
mình add file rồi! nhờ các bạn giúp mình, đi bộ đội về, chả làm được việc gì, buồn bã quá!

Ngoài cách của MinhCong bạn cũng có thể dùng công cụ Consolidate, hoặc dùng công thức sau tại C1:

PHP:

=IF(COUNTIF($A$1:A1,A1)=1,SUMIF(A:A,A1,B:B),"")

nomater · 6/10/10

uhm,bạn MinhCong làm đúng ý mình đó, nhưng ý mình muốn là 1 công thức cho toàn bảng được ko? chứ làm như vậy thì cũng chỉ nhanh hơn thủ công 1 chút thôi à! còn khối lượng việc mình làm thì nhiều quá!

MinhCong · 6/10/10

nomater đã viết:
uhm,bạn MinhCong làm đúng ý mình đó, nhưng ý mình muốn là 1 công thức cho toàn bảng được ko? chứ làm như vậy thì cũng chỉ nhanh hơn thủ công 1 chút thôi à! còn khối lượng việc mình làm thì nhiều quá!

Bạn xem copy công thức của anh dat_butmuc vào và đặt ở ô C1 sao đó Fill nó xuống dòng cuối cùng là ok. Còn không Bạn đưa số liệu cụ thể hết lên xem sao (Muốn lọc, hay tính công thức...gì gì dó càng chi tiết càng tốt).

nomater · 7/10/10

cảm ơn bạn nhiều! khó hiểu chút, nhưng xài rốt là ok!

phuyen89 · 21/10/10

Mình đang tìm hiểu Offset, có chút thắc mắc sau.
Có phải khi mà Refrence mà nó là vùng.Ví dụ những A1:A5 chẳng hạn, thì mình muốn lấy một ô nào đó để trả về thì phải dùng đến Height và width, còn không thì nó trả về lỗi Value ah.Mong các bạn nói thếm.

dat_butmuc · 21/10/10

phuyen89 đã viết:
Mình đang tìm hiểu Offset, có chút thắc mắc sau.
Có phải khi mà Refrence mà nó là vùng.Ví dụ những A1:A5 chẳng hạn, thì mình muốn lấy một ô nào đó để trả về thì phải dùng đến Height và width, còn không thì nó trả về lỗi Value ah.Mong các bạn nói thếm.

1. Cái này của bạn:

=OFFSET(B2:B5,5,3,1,1)

Nếu đã chỉ định Height và width thì nên >1 (vì đó là chiều cao & rộng của mảng)
trưởng hợp trên sẽ là: =OFFSET(B2,5,3,)

2. Cái này:

=SUM(OFFSET(B2:B4,5,3,E7:E36))

E7:E36 Về cơ bản là sai, vì chiều cao của mảng là một số mà bạn định lấy bao nhiêu dòng chứ không bê nguyên mảng E7:E36 như vậy, nếu bạn thay tại E7 giá trị = 1 hay 2 xem?
Phải là vầy: =SUM(OFFSET(B2:B10,5,3,)) hoặc =SUM(OFFSET(B2,5,3,9,))

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

Người dùng đang xem chủ đề này

Bùi Nguyễn Triệu Tường

Thành viên tích cực

Học mãi

Thành viên gắn bó

Huyền thoại GPE

Thành viên chính thức

Học mãi

Thành viên gắn bó

Thành viên gạo cội

Thành viên tiêu biểu

Huyền thoại GPE

Bùi Nguyễn Triệu Tường

Thành viên tiêu biểu

Nguyễn Cảnh Hoàng Danh

Thành viên mới

Thành viên tiêu biểu

Thành viên tiêu biểu

File đính kèm

Bùi Nguyễn Triệu Tường

Thành viên tiêu biểu

Huyền thoại GPE

Thành viên mới

File đính kèm

Huyền thoại GPE

Thành viên tiêu biểu

Hành động từ trái tim

Thành viên mới

File đính kèm

Thành viên tiêu biểu

Thành viên mới

File đính kèm

Thành viên gắn bó

Thành viên mới

Chuyên gia GPE

Nhi bất hoặc!

Chuyên gia GPE

Nhi bất hoặc!

Chuyên gia GPE

File đính kèm

Thành viên gạo cội

File đính kèm

Thành viên mới

File đính kèm

Bùi Nguyễn Triệu Tường

Thành viên mới

File đính kèm

Thành viên gắn bó

Thành viên chính thức

File đính kèm

Huyền thoại GPE

Thành viên mới

Thành viên mới

File đính kèm

Bad Excel Member

Thành viên mới

Thành viên mới

Thành viên tiêu biểu

Thành viên mới

Thành viên gắn bó

Thành viên mới

Thành viên tích cực

Thành viên mới

Bad Excel Member

Thành viên chính thức

File đính kèm

Thành viên tiêu biểu

Thành viên hoạt động

File đính kèm

Thành viên gắn bó

Thành viên tiêu biểu

0901452không62

Thành viên gắn bó

Cạo Rồi Khỏi Gội

weitə r ə'pɔn prɔvidəns

File đính kèm

Chuyên gia GPE

weitə r ə'pɔn prɔvidəns

/(hông là gì!