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

[BNTT]

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:

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]

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]

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]

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]

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]

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]

'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]

=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]

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]

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]

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:

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:

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

 

[BNTT]

.... 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]

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]

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]

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]

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]

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]

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]

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]

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