
- Hueaitixaydung-Công nghệ thông tin-Tin học xây dựng đại -ĐHXD Hà Nội
- →
- Diễn đàn office 2003
- →
- Giải pháp Excell
- →
- Chuyên mục GIẢI THÍCH CÔNG THỨC
|
• Thủ thuật Blog • Bài Viết Chú Ý • Hướng Dẫn Sử Dụng Diễn Đàn • Danh Sách Các Forum Zetaboards |
|
| Chuyên mục GIẢI THÍCH CÔNG THỨC; giải thích công thức excelll | |
|---|---|
| Tweet Topic Started: Nov 10 2010, 01:03 PM (2,528 Views) | |
| Post #1 Nov 10 2010, 01:03 PM | 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 |
Administrator
|
|
![]() |
| Post #2 Nov 10 2010, 01:04 PM | 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 |
Administrator
|
|
![]() |
| Post #3 Nov 10 2010, 01:05 PM | 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 |
Administrator
|
|
![]() |
| Post #4 Nov 10 2010, 01:05 PM | 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? |
Administrator
|
|
![]() |
| Post #5 Nov 10 2010, 01:06 PM | 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 |
Administrator
|
|
![]() |
| Post #6 Nov 10 2010, 01:07 PM | 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") |
Administrator
|
|
![]() |
| Post #7 Nov 10 2010, 01:08 PM | 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 |
Administrator
|
|
![]() |
| Post #8 Nov 10 2010, 01:09 PM | 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 |
Administrator
|
|
![]() |
| Post #9 Nov 10 2010, 01:11 PM | 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 |
Administrator
|
|
![]() |
| Post #10 Nov 10 2010, 01:12 PM | 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. |
Administrator
|
|
![]() |
| Post #11 Nov 10 2010, 01:13 PM | 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) |
Administrator
|
|
![]() |
| 1 user reading this topic (1 Guest and 0 Anonymous) | |
| « Previous Topic · Giải pháp Excell · Next Topic » |
| Theme: Zeta Original | Track Topic · E-mail Topic |
3:49 AM Jul 11
|
Hosted for free by ZetaBoards · Privacy Policy




3:49 AM Jul 11