Welcome Guest [Log In] [Register]
Add Reply
Chuyên mục GIẢI THÍCH CÔNG THỨC; giải thích công thức excelll
Topic Started: Nov 10 2010, 01:03 PM (2,528 Views)
hoaanhsang
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)



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


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


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
No Avatar
Administrator
Quote Post Goto Top Offline Profile
hoaanhsang
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
No Avatar
Administrator
Quote Post Goto Top Offline Profile
hoaanhsang


'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

No Avatar
Administrator
Quote Post Goto Top Offline Profile
hoaanhsang
=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?
No Avatar
Administrator
Quote Post Goto Top Offline Profile
hoaanhsang
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
No Avatar
Administrator
Quote Post Goto Top Offline Profile
hoaanhsang
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 ?
Tách ra 1 đoạn:
PHP Code:
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 Code:
=CHOOSE(WEEKDAY(A1),"Chủ nhật","Thứ hai","Thứ ba","Thứ tư","Thứ năm","Thứ sáu","Thứ bảy")
No Avatar
Administrator
Quote Post Goto Top Offline Profile
hoaanhsang
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



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
No Avatar
Administrator
Quote Post Goto Top Offline Profile
hoaanhsang
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;2628 6;44528;3039}
Tức từng em đôi một nhân với nhau

No Avatar
Administrator
Quote Post Goto Top Offline Profile
hoaanhsang
Đâ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ả:

Trích Nguyên văn bởi anhtuan1066
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
No Avatar
Administrator
Quote Post Goto Top Offline Profile
hoaanhsang
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.
No Avatar
Administrator
Quote Post Goto Top Offline Profile
hoaanhsang


Đô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 Code:
DecSym =INDEX(GET.WORKSPACE(37),,3)

PHP Code:
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 Code:
=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

Tập tin đính kèm

* Loại tập tin: xls GetDec&DigSymbol.xls (13.5 KB, 17 lần tải)
No Avatar
Administrator
Quote Post Goto Top Offline Profile
1 user reading this topic (1 Guest and 0 Anonymous)
« Previous Topic · Giải pháp Excell · Next Topic »
Add Reply