Đề tài Các hàm toán học trong Excel -Thu thuat Excel

CÁC HÀM TOÁN H C TRONG EXCELỌ THU THUAT EXCEL ABS (number): Tính tr tuy t đ i c a m t s (giá tr tuy t đ i c a m t s là s luôn luônị ệ ố ủ ộ ố ị ệ ố ủ ộ ố ố d ng).ươ CEILING (number, significance) : Làm tròn lên m t s (cách xa kh i s 0) đ n b i s g nộ ố ỏ ố ế ộ ố ầ nh t c a significance.ấ ủ COMBIN (number, number_chosen) : Tr v s t h p đ c ch n t m t s các ph n t .ả ề ố ổ ợ ượ ọ ừ ộ ố ầ ử Th ng dùng đ xác đ nh t ng s nhóm có đ c t m t s các ph n t .ườ ể ị ổ ố ượ ừ ộ ố ầ ử EVEN (number) : Làm tròn m t s đ n s nguyên ch n g n nh tộ ố ế ố ẵ ầ ấ EXP (number) : Tính lũy th a c s e c a m t sừ ơ ố ủ ộ ố FACT (number) : Tính giai th a c a m t sừ ủ ộ ố FACTDOUBLE (number) : Tính giai th a c p hai c a m t sừ ấ ủ ộ ố FLOOR (number, significance) : Làm tròn xu ng m t s đ n b i s g n nh tố ộ ố ế ộ ố ầ ấ GCD (number1, number2, ...) : Tìm c s chung l n nh t c a các sướ ố ớ ấ ủ ố INT (number) : Làm tròn m t s xu ng s nguyên g n nh tộ ố ố ố ầ ấ LCM (number1, number2, ...) : Tìm b i s chung nh nh t c a các sộ ố ỏ ấ ủ ố LN (number) : Tính logarit t nhiên c a m t sự ủ ộ ố LOG (number) : Tính logarit c a m t sủ ộ ố LOG10 (number) : Tính logarit c s 10 c a m t sơ ố ủ ộ ố MDETERM (array) : Tính đ nh th c c a m t ma tr nị ứ ủ ộ ậ MINVERSE (array) : Tìm ma tr n ngh ch đ o c a m t ma tr nậ ị ả ủ ộ ậ MMULT (array1, array2) : Tính tích hai ma tr nậ MOD (number, divisor) : L y ph n d c a m t phép chia.ấ ầ ư ủ ộ MROUND (number, multiple) : Làm tròn m t s đ n b i s c a m t s khácộ ố ế ộ ố ủ ộ ố MULTINOMIAL (number1, number2, ...) : Tính t l gi a giai th a t ng và tích giai th a cácỷ ệ ữ ừ ổ ừ 1 số ODD (number): Làm tròn m t s đ n s nguyên l g n nh tộ ố ế ố ẻ ầ ấ PI () : Tr v giá tr con s Piả ề ị ố POWER (number, power) : Tính lũy th a c a m t sừ ủ ộ ố PRODUCT(number1, number2, ...) : Tính tích các số QUOTIENT (numberator, denominator) : L y ph n nguyên c a m t phép chiaấ ầ ủ ộ RAND () : Tr v m t s ng u nhiên gi a 0 và 1ả ề ộ ố ẫ ữ RANDBETWEEN (bottom, top) : Tr v m t s ng u nhiên gi a m t kho ng tùy ch nả ề ộ ố ẫ ữ ộ ả ọ ROMAN (number, form) : Chuy n m t s ( -r p) v d ng s La-mã theo đ nh d ng tùy ch nể ộ ố Ả ậ ề ạ ố ị ạ ọ ROUND (number, num_digits) : Làm tròn m t s theo s ch đ nhộ ố ự ỉ ị ROUNDDOWN (number, num_digits) : Làm tròn xu ng m t s theo s ch đ nhố ộ ố ự ỉ ị ROUNDUP (number, num_digits) : Làm tròn lên m t s theo s ch đ nhộ ố ự ỉ ị SERIESSUM (x, n, m, coefficients) : Tính t ng lũy th a c a m t chu i sổ ừ ủ ộ ỗ ố SIGN (number) : Tr v d u (đ i s ) c a m t sả ề ấ ạ ố ủ ộ ố SQRT (number) : Tính căn b c hai c a m t sậ ủ ộ ố SQRTPI (number) : Tính căn b c hai c a m t s đ c nhân v i Piậ ủ ộ ố ượ ớ SUBTOTAL (function_num, ref1, ref2, ...) : Tính toán cho m t nhóm con trong m t danh sáchộ ộ tùy theo phép tính đ c ch đ nhượ ỉ ị SUM (number1, number2, ...) : Tính t ng các sổ ố SUMIF (range, criteria, sum_range) : Tính t ng các ô th a m t đi u ki n ch đ nhổ ỏ ộ ề ệ ỉ ị SUMIFS (sum_range, criteria_range1, criteria1, criteria_range2, criteria2, …) : Tính t ng các ôổ th a nhi u đi u ki n ch đ nhỏ ề ề ệ ỉ ị SUMPRODUCT (array1, array2, ...) : Tính t ng các tích c a các ph n t trong các m ng dổ ủ ầ ử ả ữ li uệ SUMSQ (number1, number2, ...) : Tính t ng bình ph ng c a các sổ ươ ủ ố 2 SUMX2MY2 (array_x, array_y) : Tính t ng c a hi u bình ph ng c a các ph n t trong haiổ ủ ệ ươ ủ ầ ử m ng giá trả ị SUMX2PY2 (array_x, array_y) : Tính t ng c a t ng bình ph ng c a các ph n t trong haiổ ủ ổ ươ ủ ầ ử m ng giá trả ị SUMXMY2 (array_x, array_y) : Tính t ng c a bình ph ng c a hi u các ph n t trong haiổ ủ ươ ủ ệ ầ ử m ng giá trả ị TRUNC (number, num_digits) : L y ph n nguyên c a m t s (mà không làm tròn)ấ ầ ủ ộ ố CÁC TH THU T TRONG EXCELỦ Ậ THU THUAT EXCEL Các th thu t hay nh t trong Excelủ ậ ấ Đ giúp các b n khai thác các tính năng u vi t trong công tác x lý b ng tính đ i v i ph nể ạ ư ệ ử ả ố ớ ầ m m Microsoft Excel, xin gi i thi u m t s th thu t đ c coi là hay nh t c a ph n m mề ớ ệ ộ ố ủ ậ ượ ấ ủ ầ ề này: Hi n th đ i s c a các hàmể ị ố ố ủ Đ xem đ i s trong m t công th c, hãy nh n Ctrl- Shift- A. Ví d , n u b n gõ =RATE vàể ố ố ộ ứ ấ ụ ế ạ nh n Ctrl- Shift -A, b n có th nhìn th y t t c các đ i s cho hàm này (ví d =RATE (nper,ấ ạ ể ấ ấ ả ố ố ụ pmt, pv, fv, type, guess)). N u b n mu n bi t chi ti t h n, gõ theo: =RATEế ạ ố ế ế ơ Và nh n Ctrl+A đ hi n th theo Function Wizard.ấ ể ể ị Tham chi u các hàmế Đ tham chi u các hàm ng d ng trong Excel, b n hãy gi phím Shift-F3, Excel s đ a ra h pể ế ứ ụ ạ ữ ẽ ư ộ tho i ch a t t c các hàm ng d ng và h ng d n các cú pháp c th cho t ng hàm khi b nạ ứ ấ ả ứ ụ ướ ẫ ụ ể ừ ạ di chuy n con tr đi m sáng đ n hàm mu n tham chi u. Đây cũng là cách nh p công th cể ỏ ể ế ố ế ậ ứ nhanh mà không c n gõ toàn b cú pháp.ầ ộ Dùng F9 đ tính t m th iể ạ ờ N u b n t o m t b n tính có ch a công th c quá dài nên không th bi t ngay k t qu , b n cóế ạ ạ ộ ả ứ ứ ể ế ế ả ạ th kéo con tr đ ch n m t ph n c a công th c trong thanh công th c, và n phím F9. L pể ỏ ể ọ ộ ầ ủ ứ ứ ấ ậ t c, k t qu c a m t công th c con trong dãy công th c c a b n s hi n trên màn hình. Quanứ ế ả ủ ộ ứ ứ ủ ạ ẽ ệ tr ng h n, là b n không đ c n Enter, m t ph n c a công th c đó s b m t, nên đ ch cọ ơ ạ ượ ấ ộ ầ ủ ứ ẽ ị ấ ể ắ ch n b n ph i n phím ESC. Tuy nhiên n u b n nh n Enter, thì hãy th n t h p phímắ ạ ả ấ ế ạ ỡ ấ ử ấ ổ ợ Ctrl- Z đ ph c h i l i các thay đ i.ể ụ ồ ạ ổ Liên k t text box t i d li u trong ôế ớ ữ ệ B n có th liên k t m t text box t i d li u trong m t ô c a b n tính b ng cách t o ra m tạ ể ế ộ ớ ữ ệ ộ ủ ả ằ ạ ộ text box và liên k t công th c tr l i k t qu c a ô đó t i text box.ế ứ ả ạ ế ả ủ ớ 3 1. Nh p vào bi u t ng t o m t text box trên thanh công c Drawing. Nh p vào b ng tính vàắ ể ượ ạ ộ ụ ắ ả kéo con tr đ t o m t text box.ỏ ể ạ ộ 2. Đ a con tr t i thanh công th c, gõ công th c đã cho k t qu t i ô c n liên k t vào textư ỏ ớ ứ ứ ế ả ớ ầ ế box. (Ví du: trong ô A1 b n có s li u là 2. ạ ố ệ Trên thanh công th c, ban gõ =A1). Và n Enter.ứ ấ 3. Text hay s li u b n gõ trong ô đã liên k t (ví d A1) s xu t hi n trong text box. Trong víố ệ ạ ế ụ ẽ ấ ệ d trên thì text box s có giá tr trong là 2.ụ ẽ ị ở B n có th di chuy n text box t i m t vài b n tính khác trong t p b ng tính n u b n mu n.ạ ể ể ớ ộ ả ậ ả ế ạ ố Liên k t m t hình nh t i m t dãy ôế ộ ả ớ ộ B n có th copy m t dãy ô và dán nhanh chúng nh m t hình nh trong m t b n tính. Đâyạ ể ộ ư ộ ả ộ ả cũng là m t cách tinh x o đ d dàng nhìn th y ô nào đó t i m t vài n i trong b ng tính c aộ ả ể ễ ấ ạ ộ ơ ả ủ b n. B n có th s d ng ph ng pháp này đ in các ô trong m t trang. Khi các ô liên k t thayạ ạ ể ử ụ ươ ể ộ ế đ i thì các đ nh d ng này cũng đ c áp d ng cho các ô đ c liên k t. Đ t o m t hình nhổ ị ạ ượ ụ ượ ế ể ạ ộ ả đ c liên k t, b n theo các b c:ượ ế ạ ướ 1. Ch n các ô có ch a d li u g c.ọ ứ ữ ệ ố 2. Nh p vào Copy trên menu Edit.ắ 3. Ch n ô mà b n mu n dán hình nh v a ch n xu t hi n.ọ ạ ố ả ừ ọ ấ ệ 4. Trong khi gi phím Shift, nh p vào Paste Picture Link trên menu Edit. K t qu s cho nhanhữ ắ ế ả ẽ chóng. S d ng Advanced Filterử ụ N u b n t o m t danh sách Shift trong Microsoft Excel và mu n ch n t i d li u đó và copyế ạ ạ ộ ố ọ ạ ữ ệ chúng t i b ng tính khác, hãy s d ng lênh Advanced Filter. Đ b t đ u s d ng l nh này,ớ ả ử ụ ể ắ ầ ử ụ ệ nh p vào Filter trong menu Dat, nh p vào Advanced Filter và làm theo các ch d n.ắ ắ ỉ ẫ S d ng hàm Sum+ If đ tính t ng d li u Advanced Filterử ụ ể ổ ữ ệ Gi s b n t o m t danh sách d li u trong ô t A1 đ n A10 và mu n tính t ng t t c các giáả ử ạ ạ ộ ữ ệ ừ ế ố ổ ấ ả tr l n h n 50 và nh h n 200. Đ làm đ c vi c này, s d ng theo dòng công th c d i đây:ị ớ ơ ỏ ơ ể ượ ệ ử ụ ứ ướ =SUM( IF( A1:A10 >=50, IF( A1: A10 <=200, A1:A10,0),0)) Đ ch c ch n b n nh p công th c nh là m t dãy, b n hãy n Ctrl- Shift- Enter. Sau đó b nể ắ ắ ạ ậ ứ ư ộ ạ ấ ạ s nhìn th y d u ngo c {} trong công th c. Nh ng không đ c n Enter khi đang gõ côngẽ ấ ấ ặ ứ ư ượ ấ th c.ứ S d ng hàm Sum+ If đ đ m d li uử ụ ể ế ữ ệ 4 B n đã có m t danh sách d li u trong các ô A1: A10, và mu n đ m t t c các giá tr l n h nạ ộ ữ ệ ố ế ấ ả ị ớ ơ 50 và nh h n 200. B n s d ng công th c sau:ỏ ơ ạ ử ụ ứ =SUM( IF( A1:A10 >=50, IF( A1: A10 <=200, 1,0),0)) Đi n nhanh d li u b ng các ô gi ng nhauề ữ ệ ằ ố B ng cách nh p đúp chu t vào góc ph i d i c a m t ô đ làm xu t hi n con tr hình d uằ ắ ộ ả ướ ủ ộ ể ấ ệ ỏ ấ c ng s t o ra m t chu i d li u gi ng h t ô g c trong các ô ti p theo c a c t. Ví d , n uộ ẽ ạ ộ ỗ ữ ệ ố ệ ố ế ủ ộ ụ ế b n gõ d li u trong các ô A1: A20, gõ m t công th c hay text vào trong ô B1. Nh p hai l nạ ữ ệ ộ ứ ắ ầ chu t vào góc d i c a ô B1. Microsoft Excel s đi n d li u xu ng phía d i c t t ô B1ộ ướ ủ ẽ ề ữ ệ ố ướ ộ ừ t i ô B20.ớ S p x p m t danh sách đ đ c l cắ ế ộ ẵ ượ ọ Đ s p x p m t danh sách đã đ c l c, ch n Data\ Sort, và ch n c t thích h p t h p đi uể ắ ế ộ ượ ọ ọ ọ ộ ợ ừ ộ ề khi n Sort by. Đ c bi t b n c n l u ý là s p x p theo th t tăng d n (Ascending) và gi mể ặ ệ ạ ầ ư ắ ế ứ ự ầ ả d n (Descending) và nh p vào OK.ầ ắ L y các b l c khi công vi c đã hoàn thànhấ ộ ọ ệ N u b n s d ng AutoFilter đ l c các b n ghi, b n đã k t thúc vi c hi n th các record đó,ế ạ ử ụ ể ọ ả ạ ế ệ ể ị nh ng sau đó b n l i mu n xem l i t t c các b n ghi c a b n m t l n n a. Đ nh n đ cư ạ ạ ố ạ ấ ả ả ủ ạ ộ ầ ữ ể ậ ượ các b n ghi đó, đ n gi n b n ch n All t danh sách th xu ng c a b l c hi n t i. N u b nả ơ ả ạ ọ ừ ả ố ủ ộ ọ ệ ạ ế ạ mu n t t ch c năng AutoFilter, ch n Data\ Filter và xoá ch n trong AutoFilter.ố ắ ứ ọ ọ Làm v a d li u trong m t trangừ ữ ệ ộ Excel đã r t "c g ng" đ đ a th t nhi u d li u cho v a m t trang, nh ng b n có th gi mấ ố ắ ể ư ậ ề ữ ệ ừ ộ ư ạ ể ả b t hay làm tăng thêm cho các d li u b ng tính c a b n b ng cách thay đ i l a ch n Adjustớ ữ ệ ả ủ ạ ằ ổ ự ọ To % Normal Size. Hay b n có th s d ng l a ch n Fit To Pages đ nén d li u cho đ y sạ ể ử ụ ự ọ ể ữ ệ ầ ố trang riêng bi t. Đ n gi n, b n ch n File\ Page Setup và th nghi m v i hai l a ch n đó b ngệ ơ ả ạ ọ ử ệ ớ ự ọ ằ cách thay đ i các thi t đ t c a chúng. Cu i cùng, nh p vào nút Print Preview đ xem k t qu .ổ ế ặ ủ ố ắ ể ế ả Hi n th các ô hay dùngể ị Đ tìm ra b t c ô nào mà b n hay dùng, ch n Auditing t menu Tools và ch n Show Auditingể ấ ứ ạ ọ ừ ọ Toolbar. Nh p vào m t ô cùng v i m t công th c, khi đó ch n Trace Precedents (nút đ u tiênắ ộ ớ ộ ứ ọ ầ trên thanh công c Auditing), và đ a mũi tên t i các ô mà b n đã s d ng đ tính toán các giáụ ư ớ ạ ử ụ ể tr c a riêng ô đó, lúc này hình mũi tên màu xanh s xu t hi n có liên k t t các ô d li u t i ôị ủ ẽ ấ ệ ế ừ ữ ệ ớ k t qu . Đ xoá các mũi tên, nh p vào nút Remove All Arrows.ế ả ể ắ Tìm nhanh các ô có ch a công th cứ ứ Đ tìm ra n i các ô ch a công th c m t cách nhanh chóng trong b ng tính, ch n Go To tể ơ ứ ứ ộ ả ọ ừ menu Edit. Trong h p tho i xu t hi n, ch n Special\ Formulas, và nh p vào OK. Khi đó, m iộ ạ ấ ệ ọ ắ ỗ ô có ch a m t công th c s đ c l a ch n.ứ ộ ứ ẽ ượ ự ọ Đánh d u vào Formulas trong h p tho i Go To đ ch n ô có công th c.ấ ộ ạ ể ọ ứ 5 B sung Shift n n web vào b ng tínhổ ề ả Đ b sung d li u "s ng" t các b ng tính n n web t i b ng tính hi n t i c a b n: m b ngể ổ ữ ệ ố ừ ả ề ớ ả ệ ạ ủ ạ ở ả tính Web, gõ URL vào trong h p tho i File Open sau đó ch n và copy các ô b n mu n. Trongộ ạ ọ ạ ố b ng tính c a ban, ch n Paste Special t menu Edit và nh p vào nút Paste Link.ả ủ ọ ừ ắ S d ng ô tham chi u và nhãn text trong các công th cử ụ ế ứ Đ s d ng các tham chi u ô cùng v i nhăn text trong m t công th c, b n ch vi c gõ m t kýể ử ụ ế ớ ộ ứ ạ ỉ ệ ộ hi u (&Wink gi a tham chi u và text. Ví d , đ hi n th dòng d li u là "25 Departments",ệ ở ữ ế ụ ể ể ị ữ ệ gõ (=A1 & "Departments"), trong đó A1 ch a s 25.ứ ố Làm th nào đ n đ nh m t macro t i m t nút?ế ể ấ ị ộ ớ ộ B n mu n ch y macro hay hàm ch b ng m t thao tác nh p vào m t nút? B ng cách gán m tạ ố ạ ỉ ằ ộ ắ ộ ằ ộ hàm hay m t macro t i m t nút tuỳ bi n b n có th th c hi n r t nhanh chóng mà không ph iộ ớ ộ ế ạ ể ự ệ ấ ả ch n Tools\Macro. Đ n đ nh m t macro t i m t nút, ch n View\ Toolbars\ Customize. Nh pọ ể ấ ị ộ ớ ộ ọ ắ vào tab Commands và ch n Macros trong đi u khi n Categories. Kọ ề ể �ustom Button trong Commands t i m t thanh công c . Khi b n đã b sung xong nút macro, nh p vào nút có bi uớ ộ ụ ạ ổ ắ ể t ng hình m t c i màu vàng và nh p vào nút Modify Selection trong h p tho i Customize.ượ ặ ườ ắ ộ ạ Ch n Assign Macro t menu shortcut m i xu t hi n. Cu i cùng, đ đ ng nh t macro b nọ ừ ớ ấ ệ ố ể ồ ấ ạ mu n t i g n vào nút macro, nh p vào OK, và nh p vào Close.ố ớ ắ ắ ắ Hi n th công th c m t cách an toànể ị ứ ộ B n nh ph i luôn luôn ghi b g tính tr c khi chuy n t i côg th c hi n th b ng cách n Ctrlạ ớ ả ả ướ ể ớ ứ ể ị ằ ấ - ~. Khi b n quay l i b ng tính sau khi hi n th các công th c, b n có th tìm th y m t vàiạ ạ ả ể ị ứ ạ ể ấ ộ đ nh d ng khác nhau. Ví d , Excel có th đã thay đ i đ r ng c t cùng m t vài c t khác c aị ạ ụ ể ổ ộ ộ ộ ộ ộ ủ b n. N u đi u này x y ra, đ n gi n m l i b ng tính đ quay l i đ nh d ng đã m t. Ph ngạ ế ề ả ơ ả ở ạ ả ể ạ ị ạ ấ ươ pháp khác là chuy n b ng tính theo cách trên và t t b ng cách vào menu Tools\Options ch nể ả ở ắ ằ ọ Views và đánh d u ch n vào ô Formulas. Nh p và OK đ k t thúc.ấ ọ ắ ể ế S p x p các ký t in hoa và in th ngắ ế ự ườ Excel không quan tâm đ n tr ng h p các ký t ch cái khi s p x p, tr khi b n thông báo t iế ườ ợ ự ữ ắ ế ừ ạ ớ nó. N u b n mu n phân bi t gi a ch in hoa và in th ng trong quá trình s p x p, ch n dế ạ ố ệ ữ ữ ườ ắ ế ọ ữ li u b n mu n s p x p, ch n Data\Sort. Nh p vào Options trong h p tho i Sort và ch n l aệ ạ ố ắ ế ọ ắ ộ ạ ọ ự ch n Case Sensitive. Nh p vào OK hai l n. B n s ph i nh đ ch n l a ch n này m i l n vìọ ắ ầ ạ ẽ ả ớ ể ọ ự ọ ỗ ầ Excel s không gi l i thi t đ t này.ẽ ữ ạ ế ặ Chuy n đ i các nhăn t i các sể ổ ớ ố Text d ng s là m t ki u d li u h p l . Ví d m t s phone không hoàn toàn là m t s . Khiạ ố ộ ể ữ ệ ợ ệ ụ ộ ố ộ ố l u tr ki u d li u này, b n có th thêm vào d li u nh p vào b ng ký t ('). Tuy nhiên,ư ữ ể ữ ệ ạ ể ữ ệ ậ ằ ự th nh tho ng các giá tr s đ c nh p vào đ c nh p nh là text s và đi u này có th x y raỉ ả ị ố ượ ậ ượ ậ ư ố ề ể ả r c r i. May thay, có th d dàng chuy n đ i text s t i d ng s . B n theo các b c sau:ắ ố ể ễ ể ổ ố ớ ạ ố ạ ướ 6 ch n các ô d li u b n mu n chuy n đ i. Ch n Data\ Text To Column. Nh p vào Next haiọ ữ ệ ạ ố ể ổ ọ ắ l n. Ch n General format và nh p vào Finish. Excel s chuy n đ i text s thành d li u s .ầ ọ ắ ẽ ể ổ ố ữ ệ ố 7