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

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

0
1.524
lượt xem
514
download

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

Mô tả tài liệu
  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ủ đề:
Lưu

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

  1. 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).     >, >=, <, =<, <>. Microsoft Excel s ử dụng các toán tử  so sánh     2.2 Hàm s ố: = Mọi công thức, hàm số trong Excel đề u bắt đầ u với dấu bằng     Cấu trúc hàm Excel:     =<Tên hàm>([<Đối số 1>,<Đối số 2>,..]) Trong đó:  <Tên hàm> do Excel cung c ấp. N ếu bạ n nhập sai sẽ  báo lỗ i #NAME!  <Đố i số 1>, <Đố i số 2> có th ể  là tham chiếu đế n ô, dãy ô, địa ch ỉ mảng, hay k ết quả  trả về của m ột công thức hoặc hàm khác.  Excel cho phép t ối đa 30 đố i số và t ổng chiều dài của công th ức tối đa là 255 ký t ự.  Các đố i số ph ải đượ c đặ t trong d ấu ngoặc đơ n ( ).  Đố i với nh ững đố i số kiểu ký t ự, chuỗ i nhập vào công th ứ c thì phả i được đặ t trong dấu ngoặc kép " ". Tuyệt đối không dùng 2 d ấu ngoặc đơn để  tạo d ấu ngoặc kép. Lúc này mk:@MSITStore:C:\Documents%20and%20Settings\Dinh%20Hiep\Desktop\Cac_ham_so ... 5/17/2011
  2. 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 <Tên c ộ t><Tên dòng>. V í dụ AA10.  Địa ch ỉ tuyệt đố i $<Tên c ột>$<Tên dòng>. V í dụ $ IV$65536  Địa ch ỉ hỗ n hợp $<Tên c ộ t><Tên dòng> ho ặc <Tên c ột>$<Tên dòng>. 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
  3. 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
  4. 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
  5. 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
  6. 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
  7. 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
  8. 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
  9. 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
  10. 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
  11. 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
  12. 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
Đồng bộ tài khoản