CÁC HÀM THÔNG DỤNG TRONG EXCEL

Chia sẻ: Lit Ga | Ngày: | Loại File: PDF | Số trang:12

Thêm vào BST

Báo xấu

2.332
lượt xem 579
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Tài liệu này sẽ hướng dẫn bạn sử dụng các hàm Excel để giải một bài toán từ đơn giản đến phức tạp như ma trận, thống kê,... một cách dễ dàng và nhanh chóng.Những hàm mà bạn tiếp xúc trong Excel cũng là những hàm số mà bạn thường xuyên gặp

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: CÁC HÀM THÔNG DỤNG TRONG EXCEL

Trang chinh Page 1 of 4 CÁC HÀM SỐ THÔNG DỤNG TRONG EXCEL Data & Time Math Logical Text & Data Information Lookup Statistical Error I. Gi ới thiệu Tài liệu này sẽ hướng d ẫn bạn sử dụng các hàm Excel để giải một bài toán từ đơn giản đế n ph ức tạp như ma tr ận, thống kê,... m ột cách dễ dàng và nhanh chóng. Những hàm mà b ạn tiếp xúc trong Excel cũng là những hàm s ố mà bạn thường xuyên g ặp trong các ứng d ụng khác như Access, SQL,... và trong kỹ thu ật lập trình PASCAL, C++, C#, VB.NET,.... Sử dụng và hiểu các hàm Excel sẽ giúp bạn có nền tảng c ơ bản khi làm việc với các phần mềm tính toán khác, tiến xa hơn trong kỹ thuật lập trình, ... II. C ơn b ản v ề hàm số trong Excel: Một số kiên thức cơ bản về hàm số và cách tính toán trong Excel mà bạn cần nắm rõ trước khi làm việc với bảng tính Excel. 2.1 Toán t ử: + , -, *, /, ^ Microsoft Excel s ử dụng các toán tử toán học (lũy thừa). >, >=,
Trang chinh Page 2 of 4 Excel s ẽ báo lỗi nghiêm trọng. Dấu phân cách gi ữ các đối số là dấu phẩy (,) hoặc dấu ch ấm phẩy (;) tùy vào thiết lập  trong hệ thống của bạn. C ần xem kỹ ph ần hướng d ẫn bên d ưới. 2.3 Địa chỉ: Hàm số Excel s ử dụng các đị a chỉ ô để đại diện cho các giá tr ị bên trong ô và gọi là tham chiếu. Vì vậy có thể sử dụng cô thức cho nhiều ô có cùng dạng công thức bằng copy công thức. Địa chỉ ô có 3 loại:  Địa ch ỉ tươ ng đố i . V í dụ AA10.  Địa ch ỉ tuyệt đố i $$. V í dụ $ IV$65536  Địa ch ỉ hỗ n hợp $ ho ặc $. Ví d ụ $A10 Tùy loại công thức, mục đích sử dụng mà có thể sử dụng các lo ại địa chỉ khác nhau cho phù hợp. Ví dụ để tính toán cho tất cả các ô đều tham chiếu đế n một ô thì đị a chỉ ô cố đị nh đó trong công th ức phải là địa chỉ tuy ệt đối. Để chuyển đổi giữa các loại địa chỉ trong công th ức, sau khi chọn vùng tham chiếu (địa chỉ ô) nhấn phím F4. Để đưa các đị a chỉ ô (tham chiếu) vào trong công th ức không nên nhập trực tiếp từ bàn phím mà chỉ cần dùng chu ột ch ọn hoặc dùng các phím m ũi tên (ho ặc kết hợp với phím Shift để chọn nhiều ô). 2.4 D ữ liệu kiểu số trong Excel  Mặc định d ữ liệu kiểu số của Excel là h ệ số của USA: Dùng dấu ch ấm để phân ph ần thập phân và phân nguyên (Decimal symbol : .), Dùng dấu ph ẩy để nhóm 3 ký số đố i với những s ố hàng nghìn tr ở lên (Digit grouping symbol: ,). Quy cách hiển thị kiểu số của Excel liên quan tr ực tiếp đế n công thức và được thiết lập  trong Regional Options của Control Panel. Nếu h ệ số của USA thì công thức s ử dụng dấu phẩy để phân cách các đối số. N ếu h ệ số của VN thì công th ức sử dụng dấu chấm phẩy (;) để phân cách các đối số. Khi nhập một dữ liệu kiểu số vào Excel n ếu ô chưa định d ạng thì d ữ liệu tự động được  canh lề bên phải. N ếu số bạn nhập vào nh ảy qua bên ph ải là một số không hợp lệ. Có thể do b ạn nhầm với ki ểu số của Việt Nam. Khi đó công thức thường trả về lỗi #VALUE! Nên dùng bàn phím số để nhập m ột số vào Excel.  Để đổi hệ số thành hệ số của VN: Vào Regional Options trong Control Panel. Ch ọn thẻ  Number. Sửa Decimal symbol (ký tự phân cách ph ần nguyên và ph ần thập phân) là dấu ph ẩy (,). Sửa Digit grouping symbol (ký tự dùng để nhóm số đối với số > 1000) là dấu chấm (.) mk:@MSITStore:C:\Documents%20and%20Settings\Dinh%20Hiep\Desktop\Cac_ham_so ... 5/17/2011
Trang chinh Page 3 of 4 2.5 D ữ liệu kiểu ngày tháng trong Excel  Dữ liệu kiểu ngày tháng trong Excel ph ụ thu ộ c vào thi ết lập trong Regional Options của Control Panel và m ặc định dùng quy cách ngày tháng của USA: M/d/YYYY.  Khi nh ập m ộ t giá trị ngày tháng vào Excel nó t ự động canh trái thì Excel hi ểu đó là một giá trị kiểu text, dùng công th ức cho giá tr ị ngày tháng đó s ẽ trả về lỗi #VALUE!  Để chuyển đổ i sang quy cách hi ển th ị ngày tháng của VN, vào Control Panel, Regional Options. Chọn thẻ Date. Nh ập đị nh dạngn dd/mm/yyyy vào m ục Short Date Format. mk:@MSITStore:C:\Documents%20and%20Settings\Dinh%20Hiep\Desktop\Cac_ham_so ... 5/17/2011
Trang chinh Page 4 of 4 Copyright © Trần Quang H ải 2005 - qhaivn@gmail.com mk:@MSITStore:C:\Documents%20and%20Settings\Dinh%20Hiep\Desktop\Cac_ham_so ... 5/17/2011
NOW Page 1 of 2 Home Date & Time HÀM NGÀY THÁNG VÀ THỜI GIAN Một số lưu ý khi sử dụng ngày tháng và thời gian trong Excel: Excel h ỗi trợ tính toán ngày tháng cho Windows và Macintosh. Windows dùng h ệ ngày bắt  đầ u từ 1900. Macitosh dùng h ệ ngày bắt đầ u từ 1904. Tài liệu này được diễn giải theo hệ ngày 1900 dành cho Windows.  H ệ th ống ngày gi ờ Excel ph ụ thu ộc vào thi ết lập trong Regional Options c ủa Control Panel. M ặc định là hệ thống c ủa M ỹ "Tháng/Ngày/Năm" (M/d/yyyy). B ạn có thể sửa lại thành h ệ thống ngày của VN "Ngày/Tháng/N ăm" (dd/MM/yyyy).  Khi b ạn nh ập m ột giá trị ngày tháng không h ợp lệ nó sẽ trở thành m ột chu ỗi văn bản. Công thức tham chiếu tới giá tr ị đó sẽ trả về lỗi. Công dụng Công dụng Tên hàm Tên hàm DATE DATEVALUE Trả về chuỗi số đạ i diện cho Trả về chuỗi số tuần tự của ngày tháng. ngày từ chuỗi văn bản đại diện cho ngày tháng. DAY Trả về thứ tự của ngày trong DAYS360 Tính số ngày giữa 2 mốc ngày tháng từ một giá trị kiểu ngày tháng d ựa trên c ơ sở một tháng. năm có 360 ngày. EDATE EOMONTH Trả về ngày cuối cùng của Trả về mốc thời gian xảy ra trước hoặc sau mốc chỉ định tháng xảy ra trước hoặc sau mốc chỉ định HOUR Trả về giờ của m ột giá trị kiểu MINUTE Trả vế phút c ủa một giá trị thời gian. kiểu thời gian MONTH NETWORKDAYS Trả về số ngày làm việc trong Trả về số tháng của một giá trị kiểu ngày tháng. mốc thời gian đưa ra sau khi trừ đi ngày nghĩ và ngày lễ. NOW Trả về ngày giờ hiện tại trong SECOND Trả về số giây của một giá tr ị hệ thống của bạn. thời gian. TIME Trả về một giá trị thời gian từ TIMEVALUE Trả về một giá trị thời gian từ chuỗi văn bản một chuỗi kiểu thời gian. TODAY WEEKDAY Trả về số thứ tự của ngày Trả về ngày hiện tại trong hệ trong tuần từ giá trị ngày thống của bạ n. tháng. mk:@MSITStore:C:\Documents%20and%20Settings\Dinh%20Hiep\Desktop\Cac_ham_so ... 5/17/2011
NOW Page 2 of 2 WEEKNUM Trả về số thứ tự của tuần WORKDAY Trả về ngày làm việc xảy ra trong năm từ giá tr ị ngày trước hoặc sau mốc thời gian tháng. đưa ra. YEAR Trả về số năm của m ột giá trị YEARFRAC Trả về tỉ lệ của một khoảng ngày tháng. thời gian so với năm. Copyright © Trần Quang H ải 2005 - qhaivn@gmail.com mk:@MSITStore:C:\Documents%20and%20Settings\Dinh%20Hiep\Desktop\Cac_ham_so ... 5/17/2011
Mot so loi thuong gap Page 1 of 2 Home Error MỘT SỐ LỖI THƯỜNG G ẶP TRONG EXCEL ##### Lỗi độ rộng Khi cột thiếu độ rộng. Dùng chu ột kéo độ rộng c ột ra cho phù h ợp Khi bạ n nhập giá trị ngày tháng ho ặc th ời gian là số âm. #VALUE! Lỗi giá tr ị Bạn nhập vào công th ức một chuỗi trong khi hàm yêu c ầu một số hoặc một giá trị logic. Đ ang nhập một hoặc chỉnh sửa các thành ph ần của công thức mà nh ấn Enter. Nhập quá nhiều tham số cho m ột toán tử hoặc một hàm trong khi chúng ch ỉ dùng m ột tham số. Ví d ụ =LEFT(A2:A5) Thi hành m ột marco (lệnh tự động) liên quan đế n một hàm mà hàm đó tr ả về lỗi #VALUE! #DIV/0! Lỗi chia cho 0 Nhập vào công th ức số chia là 0. Ví dụ = MOD(10,0). Số chia trong công thức là một tham chiếu đế n ô trống. #NAME! Sai tên Bạn dùng những hàm không th ường trực trong Excel nh ư EDATE, EOMONTH, NETWORKDAYS , WORKDAYS,... Khi đó cần ph ải vào menu Tools - Add-in. Đánh dấu vào tiện ích Analysis ToolPak. Nhập sai tên m ột hàm s ố. Trường hợp này xảy tra khi bạn dùng bộ gõ tiếng Việt ở chế độ Telex vô tình làm sai tên hàm nh ư IF thành Ì, VLOOKUP thành VLÔKUP. Dùng những ký tự không được phép trong công th ức. Nhập một chuỗi trong công th ức mà không có đóng và m ở dấu nháy đôi."" mk:@MSITStore:C:\Documents%20and%20Settings\Dinh%20Hiep\Desktop\Cac_ham_so ... 5/17/2011
Mot so loi thuong gap Page 2 of 2 Không có dấu 2 ch ấm : trong dãy đị a chỉ ô trong công th ức. #N/A Lỗi d ữ liệu Giá tr ị trả về không tương thích t ừ các hàm dò tìm như VLOOKUP, HLOOKUP, LOOKUP hoặc MATCH. Dùng hàm HLOOKUP, VLOOKUP, MATCH để trả về một giá trị trong b ảng chưa được sắp xếp. Không đồng nhất dữ liệu khi bạn sử dụng địa chỉ mảng trong Excel. Quên m ột hoặc nhiều đối số trong các hàm tự tạo. Dùng m ột hàm t ự tạo không hợp lý. #REF! Sai vùng tham chiếu Xóa những ô đang được tham chiếu bởi công thức. Hoặc dán những giá trị được tạo ra từ công thức lên chính vùng tham chiếu của công thức đó. Liên kết hoặc tham chiếu đến một ứng d ụng không th ể chạy được. #NUM! Lỗi d ữ liệu kiểu s ố Dùng m ột đối số không phù h ợp trong công thức sử dụng đối số là dữ liệu kiểu số. Ví dụ bạn đư a vào công th ức số âm trong khi nó ch ỉ tính số dương. Dùng hàm lặp đi lặp lại dẫn đế n hàm không tìm được kết quả tr ả về. Dùng m ột hàm trả về một số quá lớn hoặc quá nhỏ so v ới khả năng tính toán c ủa Excel. #NULL! Lỗi d ữ liệu r ỗng Dùng m ột dãy tóan tử không phù h ợp Dùng m ột mảng không có phân cách. Copyright © Trần Quang H ải 2005 - qhaivn@gmail.com mk:@MSITStore:C:\Documents%20and%20Settings\Dinh%20Hiep\Desktop\Cac_ham_so ... 5/17/2011
Ham toan va luong giac Page 1 of 2 Home Math HÀM TOÁN HỌC VÀ LƯỢNG GIÁC  Bao gồm các hàm v ề toán h ọc và lượng giác giúp bạn có thể giải một bài toán đạ i số, giải tích, hoặc lượng giác từ tiểu học đế n đạ i học...  Lưu ý đến quy cách hiển thị số của VN và của US. Để luôn nhập đúp m ột giá trị kiểu số bạn hãy sử dụng bàn phím số. Công dụng Công dụng Tên hàm Tên hàm Tính tr ị tuy ệt đối của một số Tính nghịch đảo cosin ABS ACOS Tính ngh ịch đảo cosin Tính nghịch đảo sin ACOSH ASIN hyperbol Tính ngh ịch đảo sin hyperbol Tính nghịch đảo tang ASINH ATAN Tính ngh ịch đảo tang với tọa Tính nghịch đảo tang ATAN2 ATANH độ hyperbol Là tròn đế n bội số gần nhất Tính tổ hợp từ số phần tử CEILING COMBIN chọn Tính cosin c ủa một góc Tính cosin hyperbol COS COSH Đổi radians sang độ Làm tròn một số đế n số DEGREES EVEN nguyên chẵn gần nhất. Tính lũy thừa c ơ số e Tính giai thừa của một số EXP FACT FACTDOUBLE Tính lũy thừa c ấp 2 Làm tròn xuống đế n bội số FLOOR gần nhất do b ạn ch ỉ. Tìm ước số chung lớn nhất Làm tròn xuống s ố nguyên GCD INT gần nhất Tìm b ội số chung nhỏ nhất Tính logarit cơ số tự nhiên LCM LN của một số Tính logarit Tính logarit cơ số 10 LOG LOG10 Tính định th ức của ma trận Tìm ma trận nghịch đảo MDETERM MINVERSE mk:@MSITStore:C:\Documents%20and%20Settings\Dinh%20Hiep\Desktop\Cac_ham_so ... 5/17/2011
Ham toan va luong giac Page 2 of 2 Tính tích 2 ma tr ận Lấy phần dư của phép chia MMULT MOD Làm tròn m ột số đế n bội số MULTINOMIAL Tỷ lệ giai thừa t ổng với tích MROUND của số khác. các giai thừa của các s ố. Làm tròn đế n một số nguyên Trả về giá trị pi ODD PI lẽ gần nhất. Tính lũy thừa c ủa một số Tính tích các số POWER PRODUCT Lấy phần nguyên của phép Đổi độ sang radians. QUOTIENT RADIANS chia Tr ả về một số ng ẫu nhiên RANDBETWEEN Trả về một số ngẫu nhiên RAND trong kho ảng 0 và 1 trong khoả ng do bạn ch ỉ định Chuy ển một số sang s ố La Làm tròn một số ROMAN ROUND Mã ROUNDDOWN Làm tròn m ột số hướng Làm tròn một số hướng ra xa ROUNDUP xu ống zero zero. Tính tổng lũy th ừa ... Trả về dấu của một số SERIESSUM SIGN Tính sin c ủa một góc Tính sin hyperbol của m ột số SIN SINH Tính c ăn bậc 2 của m ột số Tính căn bậc 2 của một số SQRT SQRTPI nhân với pi Tính tổng phụ Tính tổng c ủa các số SUBTOTAL SUM SUMPRODUCT Tính tổng các tích các ph ần Tính tổng bình phương các SUMSQ tử tương ứng trong các m ảng các giá tr ị Tính tổng của hiệu bình Tính tổng c ủa tổng bình SUMX2MY2 SUMX2PY2 ph ương các phần tử tương phương các phần tử tương ứng c ủa 2 mảng giá tr ị ứng c ủa 2 m ảng giá trị Tính tổng của bình phương Tính tang của một góc SUMXMY2 TAN hiệu các phần tử tương ứng của 2 mảng giá trị. Tính tang hyperbol c ủa m ột Cắt bớt phần thập phân c ủa TANH TRUNC số số Copyright © Trần Quang H ải 2005 - qhaivn@gmail.com mk:@MSITStore:C:\Documents%20and%20Settings\Dinh%20Hiep\Desktop\Cac_ham_so ... 5/17/2011
Ham tim kiem va tham chieu Page 1 of 1 Home Lookup & Reference HÀM TÌM KIẾM VÀ THAM CHIẾU Bao g ồm các hàm tìm kiếm và tham chiếu r ất hữu ích khi b ạn làm việc với CSDL lớn trong EXCEL như kế toán, tính lương, thuế... Công dụng Công dụng Tên hàm Tên hàm Tạo địa chỉ dạng chuỗi ký tự. Đếm số vùng tham chiếu ADDRESS AREAS Tr ả về giá tr ị trong mảng giá Trả về số thứ tự cột của ô CHOOSE COLUMN trị tại vị trí được chỉ định. đầ u tiên trong vùng tham chiếu. Tr ả về số cột của vùng tham Dò tìm một giá trị trên hàng COLUMNS HLOOKUP chiếu. đầ u tiên và trả về ... HYPERLINK Tạo một siêu liên kết Trả về một giá trị trong INDEX bảng d ữ liệu tương ứng v ới chỉ mục của nó. Tr ả về giá tr ị của một tham Dò tìm một giá trị INDIRECT LOOKUP chiếu Tr ả về vị trí của một giá trị Trả về một vùng tham chiếu MATCH OFFSET trong b ảng d ữ liệu từ một vùng xuất phát. Tr ả về số thứ tự dòng của ô Trả về số dòng của dãy ROW ROWS đầ u tiên trong dãy ô. tham chiếu. Hoán vị hướng một vùng một Dò tìm một giá trị trên cột TRANSPOSE VLOOKUP giá trị. đầ u tiên và trả về ... Copyright © Trần Quang H ải 2005 - qhaivn@gmail.com mk:@MSITStore:C:\Documents%20and%20Settings\Dinh%20Hiep\Desktop\Cac_ham_so ... 5/17/2011
REPLACE Page 1 of 1 Home Text HÀM XỬ LÝ VĂN BẢN VÀ DỮ LIỆU  Bao gồm các hàm xử lý chuỗi văn bản như trích lọc, tìm ki ếm, thay thế, chuyển đổi chu ỗi văn bản trong Excel. Hàm Công d ụng Hàm Công d ụng CHAR Chuy ển số thành ký tự CLEAN Xóa ký tự không phù h ợp Nối nhiều chuỗi thành m ột CODE Tr ả về mã số của ký tự đầ u tiên CONCATENATE chuỗi Chuy ển định d ạng số thành tiền DOLLAR EXACT So sánh hai chuỗi văn bản tệ Tìm kiếm một chuỗi trong chuỗi FIND FIXED Chuyển một số sang đị nh dạng khác văn bản LEFT Trích bên trái m ột chuỗi LEN Tính độ dài m ột chuỗi LOWER Chuy ển thành ch ữ thường. PROPER Chuyển ký tự đầu m ỗi từ thành chữ hoa MID Trích chuỗi con từ một chuỗi REPLACE Thay thế một phần của chuỗi. RIGHT Trích bên ph ải một chuỗi REPT Lặp lại một chuỗi SUBSTITUTE Thay thế một chuỗi xác định SEARCH Tìm kiếm một chuỗi TEXT Chuy ển một số sang text. T Kiểm tra dữ liệu kiểu text TRIM Xóa những ký tự tr ắng bên UPPER Chuyển ký tự thường thành trong chu ỗi. hoa. VALUE Chuy ển một chuỗi thành số. Copyright © Trần Quang H ải 2005 - qhaivn@gmail.com mk:@MSITStore:C:\Documents%20and%20Settings\Dinh%20Hiep\Desktop\Cac_ham_so ... 5/17/2011