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, ...
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 Excel sẽ báo lỗi nghiêm trọng.
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
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.4Dữ 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 (.)
2.5Dữ 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.
Các công thức Excle cơ bản
STT Hàm Chức Năng & Các Ví dụ Về các Hàm !
1 AVERAGE là hàm cho biết kết quả trung bình cộng của các số
VD: =AVERAGE(x1,x2)
2 ROUND cho kết quả làm số tròn đến m chũ số thập phân
VD = ROUND = (5.1314,2) sẽ cho kết quả là 5.13
VD = ROUND = (5.1354,2) sẽ cho kết quả là 5.14
VD = ROUND = (5.1354,1) sẽ cho kết quả là 5.1
VD = ROUND = (5.1354,0) sẽ cho kết quả là 5
VD = ROUND = (121896,-2)sẽ cho kết quả 121900
3 MIN cho kết quả là số nhỏ nhất trong các số
VD =MIN(2,3,4,10,25) sẽ cho kết quả là 2
4 MAX cho bết kết quả là số lớn nhất trong các số
VD = MAX(2,3,4,10,25) sẽ cho kết quả là 25
5 SUM cho biết két quả của các số trong ngoặc
VD = SUM(2,4,6)sẻ cho kết quả là 9
6 UPPER trả về chỗi chữ hoa của chỗi text
VD = UPPER(“Thanh Da”) sẽ là “THANH DA”
7 PROPER là hàm trả về một chuỗi text và cho ký tự đầu của mỗi từ là chữ hoa
VD = PROPER(“thanh đa”)sẽ cho là Thanh Đa
8 RANK là hàm xếp hạng tăng hoặc giảm dần
VD = (H3,$H$3:$H$45,0) sẽ xếp hạng người có diểm trung bình cao nhất sẽ là 1
VD = (H3,$H$3:$H$45,1) sẽ xếp hạng người có điểm trung bình thấp nhất sẽ là 1
9 HLOOKUP (trị dò ,bảng,hàm tham chiếu,cách dò)
nguyên tắc hoạt động giống như VLOOKUP khác là
VLOOKUP thì dò tìm ở cột trái tham chiếu số liệu ở các cột bên phải
HLOOKUP thì dò tìm ở hàng trên cùng và tham chiếu số liệu ở các hàng bên dưới
10 VLOOKUP là hàm tìm trị dò ,bảng ,cột tham chiếu,cách dò)
VD = VLOOKUP(A001,Bảng,2,0) Dò A001 trong bảng ,lấy giá trị ở cột 2
1 MOD (số bị chia ,số chia )cho kết quả là số dư của phét chia nguyên .kết quảcó cùng dấu với chia
VD = MOD(12,3)cho kết quả là 0
VD =MOD(9,2) cho kết quả là 1
12 COUNT đếm số phân tử kiểu số
VD = COUNT=(B3 :B7)sẽ đếm từ ô B3 đến ô B7 có giá trị số
13 COUNTA đếm số phần tử khác trống (cả số & chuổi)
VD = COUNTA(B3:B7)sẽ đếm từ ô B3 đến ô B7 có giá trị số hoặc chuỗi (trừ các ôcó khoảng trắng)
14 COUNTIF đếm số phân tử trong dãy thỏa điều kiện
VD = COUNTIF(Ả:F3,">=5"sẽ đếm từ Ô Ả đến ô F3 thỏa điều kiện cógiá trị lớn hơn hoăc bằng 5
VD = COUNTIF(C1:C45,"Đậu")sẽ đếm từ Ô C1 đến C45 Thỏa điều kiện"Đậu"
15 IF điều kiện ,trị đúng ,trị sai
VD : IF (2<>3,"đúng","Sai")sẽ cho kết quả là đúng
VD : IF (2=3,"Đúng","Sai")sẽ cho kết quả là sai
16 SUMIF (cột chứa điều kiện,,Đ K,Cột tính tổng)tính tổng trên cột của nhữngdòng thỏa điều kiện
VD : SUMIF($A$3:$A$45,"TIVI",$H$3:$H$45)
17 AND (điều kiện 1,điều kiện 2,điều kiện 3,..)
Chỉ cho kết quả đúng khi tất cả các điều kiện đúng (Cho kết quả sai khi chỉ cầnmột điều kiện sai)
18 OR (điều kiện 1,điều kiện 2,điều kiện 3,..)
cho kết quả sai khi tất cả các điều kiện sai
19 LEFT (Đĩa chỉ ,n)
Lấy n kí tự tù bên trái đĩa chỉ
VD : =LEFT(ThanhDa,5) sẽ là Thanh
VD : =LEFT(Cao11,2)sẽ là Ca
20 RIGHT (Đĩa chỉ ,n)
Lấy n kí tự tù bên phải đĩa chỉ
VD :=RIGHT(ThanhDa,2)sẽ là Da
VD :=RIGHT(CAO11,3)sẽ là 001
21 MID (Đĩa chỉ ,n,m)
Lấy từ ký tự thứ n lấy m ký tự của đĩa chỉ
VD : =MID(ThanhDa,6,2)sẽ là Da
VD : = LEFT(Cao11BT,3,3)sẽ là 001
22 INT (Số X) cho kết quả là phần nguyên của số X
VD := INT(3.1415)sẽ cho kết quả là 3
23 ABS (Số X) cho kết quả là trị tuyệt đối của X
VD: = ABS(-9)sẽ cho kêt quả là 9
24 LEN Trả về chiều dài của chuỗi text
VD:= LEN("THANH DA") sẽ là 8
25 LOWER trả về chuỗi chữ thường của chuỗi text
VD : =LOWER("THANH DA")sẽ là "thanh da"
26 TRIM loại bỏ các khoảng trắng ở trước ,sau và trong chuỗi text
VD : =TRIM(" THANH DA "sẽ là "THANHDA"
27 MATCH (trị dò,bảng 1 chiều,1/0)
hàm trả về giá trị kiểu số và số thứ tự của phần tự này trong mảng,nếu khôngtìm thấy sẽ trả về giá trị #N/A
28 INDEX (Mảng hai chiều ,số dòng,số cột)hàm trả về giá trị của một ô trongbảng đó là giao của dong và cột
VD :=INDEX(Mảng,2,4)kết quả sẽ là 98
29 TEXT (Giá trị số,định dạnh)
chuyển giá trị số thành chuỗi
VD :=TEXT(12345,12345)sẽ biến số 12345 thành chuỗi 12345
VD := TEXT(456,45)sẽ biến số 456 thành chuỗi 45
30 VALUE (ô chứa số dạng chuỗi)
chuyển giá trị số dạng chỗi thành số
VD :=VALUE(12345)biến chuỗi 12345 thành số 12345
31 CHOOSE (biểu thức,Danh sách)
cho kết quả là một vị trí trong danh sách tương ứng với giá trị biểu thức
VD := CHOOSE(2,"trâm",'nhật","phương")sẽ cho kết quảlà nhật
32 NOT (Điều Kiện)
Cho kết quả ngược lại với ĐK
VD = NOT(2<>4)sẽ cho kết quả là FALSE (Sai)
33 SUMIF (rang,đk)Tổng các số thảo Điểu kiện
nếu A1=10,A2=7 VD:=SUMIF(A1:A2,">4")=17
35 SQRT cho kết quả là căn bậc hai của biểuthức số với điều kiện biểu thức số lớn hơn hoặc bằng không
VD = SQRT(16)cho kết quả là 4
36 PI hàm PI không có đối số cho kết quả làgái trị của số PI
VD = PI()cho kết quả là 3,141593
37 TRUNC bỏ các giá trị sau dấu chấm thậtphân
VD = TRUNC(123,56) cho kết quả là 123
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.
Tên hàm : DATE công dụng: Trả về chuỗi số tuần tự của ngày tháng.
DATEVALUE : Trả về chuỗi số đại diện cho 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 tháng từ một giá trị kiểu ngày tháng.
DAYS360 : Tính số ngày giữa 2 mốc ngày tháng dựa trên cơ sở một năm có 360 ngày.
EDATE : Trả về mốc thời gian xảy ra trước hoặc sau mốc chỉ định
EOMONTH: Trả về ngày cuối cùng của 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 thời gian.
MINUTE Trả vế phút của một giá trị kiểu thời gian
MONTH Trả về số tháng của một giá trị kiểu ngày tháng.
NETWORKDAYS Trả về số ngày làm việc trong 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 hệ thống của bạn.
SECOND Trả về số giây của một giá trị thời gian.
TIME Trả về một giá trị thời gian từ chuỗi văn bản
TIMEVALUE Trả về một giá trị thời gian từ một chuỗi kiểu thời gian.
TODAY Trả về ngày hiện tại trong hệ thống của bạn.
WEEKDAY Trả về số thứ tự của ngày trong tuần từ giá trị ngày tháng.
WEEKNUM Trả về số thứ tự của tuần trong năm từ giá trị ngày tháng.
WORKDAY Trả về ngày làm việc xảy ra trước hoặc sau mốc thời gian đưa ra.
YEAR Trả về số năm của một giá trị ngày tháng.
YEARFRAC Trả về tỉ lệ của một khoảng thời gian so với năm.
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.""
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.
HÀM LẤY THÔNG TIN TRONG EXCEL
ðBao gÓm các hàm kiÃm tra kiÃu dï liÇu và l¥y mÙt sÑ thông tin trong b£ng tính. H¥u h¿t các hàm này °ãc cung c¥p à t°¡ng thích và h× trã các éng dång khác khi làm viÇc vÛi Excel.
Tên hàm Công dụng Tên hàm Công dụng CELL Lấy thông tin về dữ liệu trong ô COUNTBLANK Đếm số ô trống ERROR.TYPE Lấy mã lỗi INFO Thông tin về môi trường hoạt động của EXCEL IS FUNCTIONS Các hàm kiểm tra kiểu dữ liệu ISEVEN Kiểm tra số chẵn ISODD Kiểm tra số lẽ N Chuyển đổi giá trị thành số NA Dùng lỗi #N/A! đánh dấu ô TYPE Trả về loại giá trị
HÀM LUẬN LÝ
I
ð Hàm luận lý luôn trả về một trong 2 giá trị TRUE (đúng) hoặc FALSE (sai).
ð Kết quả của hàm luận lý dùng làm đối số trong các hàm có sử dụng điều kiện như IF, SUMIF, COUNTIF,....
Tên hàm Công dụng AND Và OR Hoặc NOT Phủ định FALSE Sai TRUE Đúng IF Trả về kết quả với điều kiện
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ế...
Tên hàm Công dụng Tên hàm Công dụng ADDRESS Tạo địa chỉ dạng chuỗi ký tự. AREAS Đếm số vùng tham chiếu CHOOSE Trả về giá trị trong mảng giá trị tại vị trí được chỉ định. COLUMN Trả về số thứ tự cột của ô đầu tiên trong vùng tham chiếu. COLUMNS Trả về số cột của vùng tham chiếu. HLOOKUP Dò tìm một giá trị trên hàng đầu tiên và trả về ...
HYPERLINK Tạo một siêu liên kết INDEX Trả về một giá trị trong bảng dữ liệu tương ứng với chỉ mục của nó.INDIRECT Trả về giá trị của một tham chiếu LOOKUP Dò tìm một giá trị MATCH Trả về vị trí của một giá trị trong bảng dữ liệu OFFSET Trả về một vùng tham chiếu từ một vùng xuất phát. ROW Trả về số thứ tự dòng của ô đầu tiên trong dãy ô. ROWS Trả về số dòng của dãy tham chiếu. TRANSPOSE Hoán vị hướng một vùng một giá trị. VLOOKUP Dò tìm một giá trị trên cột đầu tiên và trả về ...
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 nhp úp mÙt giá trË kiÃu sÑ b¡n hãy sí dång bàn phím sÑ.
Tên hàm Công dụng Tên hàm Công dụng ABS Tính trị tuyệt đối của một số ACOS Tính nghịch đảo cosinACOSH Tính nghịch đảo cosin hyperbol ASIN Tính nghịch đảo sin ASINH Tính nghịch đảo sin hyperbol ATANTính nghịch đảo tang ATAN2 Tính nghịch đảo tang với tọa độ ATANH Tính nghịch đảo tang hyperbol CEILINGLà tròn đến bội số gần nhất COMBIN Tính tổ hợp từ số phần tử chọn COS Tính cosin của một góc COSH Tính cosin hyperbol DEGREES Đổi radians sang độ EVEN Làm tròn một số đến số nguyên chẵn gần nhất. EXP Tính lũy thừa cơ số e FACT Tính giai thừa của một số FACTDOUBLE Tính lũy thừa cấp 2 FLOOR Làm tròn xuống đến bội số gần nhất do bạn chỉ. GCD Tìm ước số chung lớn nhất INT Làm tròn xuống số nguyên gần nhất LCMTìm bội số chung nhỏ nhất LN Tính logarit cơ số tự nhiên của một số LOG Tính logarit LOG10 Tính logarit cơ số 10 MDETERM Tính định thức của ma trận MINVERSE Tìm ma trận nghịch đảo MMULT Tính tích 2 ma trận MODLấy phần dư của phép chia MROUND Làm tròn một số đến bội số của số khác. MULTINOMIAL Tỷ lệ giai thừa tổng với tích các giai thừa của các số. ODD Làm tròn đến một số nguyên lẽ gần nhất. PI Trả về giá trị piPOWER Tính lũy thừa của một số PRODUCT Tính tích các số QUOTIENT Lấy phần nguyên của phép chiaRADIANS Đổi độ sang radians. RAND Trả về một số ngẫu nhiên trong khoảng 0 và 1 RANDBETWEEN Trả về một số ngẫu nhiên trong khoảng do bạn chỉ định ROMAN Chuyển một số sang số La Mã ROUND Làm tròn một số ROUNDDOWN Làm tròn một số hướng xuống zero ROUNDUP Làm tròn một số hướng ra xa zero.SERIESSUM Tính tổng lũy thừa ... SIGN Trả về dấu của một số SIN Tính sin của một góc SINH Tính sin hyperbol của một số SQRT Tính căn bậc 2 của một số SQRTPI Tính căn bậc 2 của một số nhân với piSUBTOTAL Tính tổng phụ SUM Tính tổng của các số SUMPRODUCT Tính tổng các tích các phần tử tương ứng trong các mảng giá trị SUMSQ Tính tổng bình phương các các SUMX2MY2 Tính tổng của hiệu bình phương các phần tử tương ứng của 2 mảng giá trị SUMX2PY2 Tính tổng của tổng bình phương các phần tử tương ứng của 2 mảng giá trị SUMXMY2 Tính tổng của bình phương hiệu các phần tử tương ứng của 2 mảng giá trị. TAN Tính tang của một góc TANH Tính tang hyperbol của một số TRUNC Cắt bớt phần thập phân của số
HÀM THỐNG KÊ
ð Bao gồm các hàm số giúp bạn giải quyết các bài toán thống kê từ đơn giản đến phức tạp. Tên hàm Công dụng Tên hàm Công dụng AVEDEV Tính bình quân độ phân cực AVERAGE Tính trung bình cộng các số.AVERAGEA Tính trung bình cộng các giá trị COUNT Đếm ô dữ liệu chứa số COUNTA Đếm số ô chứa dữ liệu MAXTìm số lớn nhất MAXA Tìm giá trị lớn nhất MIN Tìm số nhỏ nhất MINA Tìm giá trị nhỏ nhất RANK Tìm vị thứ của một số trong dãy số.
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
CODE Trả về mã số của ký tự đầu tiên
CONCATENATE Nối nhiều chuỗi thành một chuỗi
DOLLAR Chuyển định dạng số thành tiền tệ
EXACT So sánh hai chuỗi văn bản
FIND Tìm kiếm một chuỗi trong chuỗi khác
FIXED Chuyển một số sang định dạng 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 trong chuỗi.
UPPER Chuyển ký tự thường thành hoa.
VALUE Chuyển một chuỗi thành số.
--------------------
Bổ sung bài viết...
--------------------
Cụ thể luôn nè
VLOOKUP
Công dụng
Dò tìm một giá trị ở cột đầu tiên bên trái của một bảng dữ liệu. Nếu tìm thấy sẽ trả về giá trị ở cùng trên dòng với giá trị tìm thấy trên cột mà bạn chỉ định. Hàm VLOOKUP thường dùng để điền thông tin vào bảng dữ liệu từ bảng dữ liệu phụ.
VLOOKUP xuất phát từ vertical lookup : dò tìm theo phương đứng, hay theo cột.
Công thức
=VLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
lookup_value: là tìm một giá trị dùng để tìm kiếm, nó có thể là một giá trị, một tham chiếu hay một chuỗi ký tự.
table_array là vùng chứa dữ liệu cần tìm. Đây là bảng dữ liệu phụ có nội dung thường cố định, bao quát để bạn lấy dữ liệu.
Các giá trị ở cột đầu tiên có thể là giá trị số, chuỗi ký tự, hoặc logic.
Nếu range_lookup là TRUE thì các giá trị ở cột đầu tiên của bảng dữ liệu phụ này phải được sắp xếp tăng dần từ -2,1,0,1,2,...,A-Z,FALSE,TRUE. Nếu không hàm VLOOKUP sẽ trả giá trị không chuẩn xác.
Để sắp xếp các giá trị trong bảng dữ liệu từ trên xuống để hàm cho kết quả phù hợp khi bạn dùng range_lookup là TRUE: Chọn vùng dữ liệu cần sắp xếp, kích vào menu Data, Sort. Nhấn nút Options bên dưới, đánh dấu Soft top to bottom, rồi nhấn OK. Kích chọn cột cần sắp xếp trong danh sách. ChọnAscending, và nhấn OK
row_index_num số thứ tự cột trên bảng dữ liệu phụ mà dữ liệu bạn cần lấy. Giá trị trả về nằm trên cột bạn chỉ định này và ở dòng mà hàm tìm thấy giá trị dò tìm lookup_value.
range_lookup là giá trị logic bạn chỉ định muốn VLOOKUP tìm kiếm chính xác hay là tương đối. Nếu range_lookup là TRUE hàm sẽ trả về kết quả tìm kiếm tương đối. Nếu không tìm thấy kết quả chính xác, nó sẽ trả về một giá trị lớn nhất mà nhỏ hơn giá trị tìm kiếm lookup_value. Nếu range_lookup là FALSE hàm tìm kiếm chính xác, nếu không có trả về lỗi #N/A!
Lưu ý!
Nếu lookup_value nhỏ hơn giá trị nhỏ nhất trong cột đầu tiên của bảng dữ liệu phụ, VLOOKUP trả về lỗi#N/A!.
Khi xuất hiện lỗi #N/A! bạn có thể mắc lỗi nhập dư một khoảng trống ở phía sau giá trị dò tìm hoặc trong bảng dữ liệu, kể cả chính và phụ.
Khi dùng hàm VLOOKUP để điền dữ liệu cho một bảng dữ liệu thì trong công thức cần phải tạo địa chỉ tuyệt đối cho bảng dữ liệu phụ table_array để công thức đúng cho các hàng còn lại khi bạn copy công thức xuống các ô bên dưới.
--------------------
Bổ sung bài viết...
--------------------
AVERAGEA
Công dụng Tính trung bình cộng của các đối số và chấp nhận cả giá trị logic TRUE, FALSE trong dãy số.
Công thức =AVERAGEA(number1,number2,...) number1,number2có thể có từ 1 đến 30 số mà bạn cần tính trung bình cộng.
Lưu ý! Các đối số có thể là số, tên, mảng hoặc vùng tham chiếu
Các thành phần của vùng tham chiếu, mảng giá trị nếu là kiểu chuỗi thì chuyển thành 0. Chuỗi rỗng cũng được chuyển thành 0. Nếu đối số là giá trị logic TRUE thì được hiểu là 1, FALSE hiểu là 0. Ví dụ Để dễ hiểu hơn, bạn hãy nhập công thức bên dưới vào bất kỳ một ô nào trong bảng tính.
Công thức
Giải thích
=AVERAGEA(5,10,TRUE,FALSE) Trả về 4
--------------------
Bổ sung bài viết...
--------------------
AVERAGE
Công dụng Tính trung bình cộng của các đối số. Công thức =AVERAGE(number1,number2,...)number1,number2có thể có từ 1 đến 30 số mà bạn cần tính trung bình cộng. Lưu ý! Nếu đối số là tham số kiểu text, logic, ô rỗng thì giá trị đó được bỏ qua. Nếu là zero thì được tính.
Ví dụ Để dễ hiểu hơn, bạn hãy copy dữ liệu bên trong bảng bên dưới vào một trang bảng tính mới và nhập công thức bên dưới vào.
A B C D E F 1
No comments:
Post a Comment