Công thức thường dùng trong Ms Excel 2007, 2010 (Phần 2)

Thứ hai, 24/08/2015  |  MS Excel  |  Lượt xem: 1483

5. Hàm nhóm chuỗi

a. Hàm LEFT

Cú pháp:  LEFT(Text,Num_chars)

– Text: Chuỗi văn bản.

– Num_Chars: Số ký tự muốn trích.

Mục đích:  Trích các ký tự bên trái của chuỗi nhập vào.

b. Hàm RIGHT

Cú pháp: RIGHT(Text,Num_chars)

– Text: Chuỗi văn bản.

– Num_Chars: Số ký tự muốn trích.

Mục đích: Trích các ký tự bên phải của chuỗi nhập vào.

c. Hàm MID

Cú pháp:  MID(Text,Start_num, Num_chars)

– Text: chuỗi văn bản.

– Start_num: Số thứ tự của ký tự bắt đầu được trích.

– Num_chars: Số ký tự cần trích.

Mục Đích: Trích các ký tự từ số bắt đầu trong chuỗi được nhập vào.

d. Hàm UPPER

Cú pháp:  UPPER(Text)

- Text: chuỗi cần thay đổi

Mục Đích:  Đổi chuỗi nhập vào thành chữ hoa.

e.  Hàm LOWER

Cú Pháp: Lower( text)

- Text: chuỗi cần thay đổi

Mục đích: Đổi chuỗi nhập vào thành chữ thường.

f. Hàm PROPER

Cú Pháp:  PROPER(Text)

Mục đích: Đổi ký từ đầu của từ trong chuỗi thành chữ hoa

h. Hàm TRIM

Cú Pháp:  TRIM(Text) 

- Text: Chuỗi cần thay đổi

Mục đích:  Cắt bỏ các ký tự trắng ở đầu chuỗi và cuối chuỗi.

 

6. Hàm ngày tháng

a. Hàm DAY

Cú pháp: DAY(Serial_num)

- Serial_num: Là dữ liệu kiểu Date, có thể là một hàm DATE hoặc kết quả của một hàm hay công thức khác

Mục đích: Trả về ngày tương ứng với chuỗi ngày đưa vào. Giá trị trả về là một số kiểu Integer ở trong khoảng từ 1 đến 31.

b. Hàm MONTH

Cú pháp:  MONTH(Series_num)

- Serial_num: Là dữ liệu kiểu Date, có thể là một hàm DATE hoặc kết quả của một hàm hay công thức khác

Mục đích: Trả về tháng của chuỗi ngày được mô tả. Giá trị trả về là một số ở trong khoảng 1 đến 12

c. Hàm YEAR

Cú pháp: YEAR(Serial_num)

- Serial_num: Là dữ liệu kiểu Date, có thể là một hàm DATE hoặc kết quả của một hàm hay công thức khác

Mục đích: Trả về năm tương ứng với chuỗi ngày đưa vào. Year được trả về là một kiểu Integer trong khoảng 1900-9999.

e.  Hàm TODAY

Cú pháp: TODAY()

- Hàm này không có các đối số.

Mục đích: Trả về ngày hiện thời của hệ thống.

f.  Hàm WEEKDAY

Cú pháp:  WEEKDAY(Serial, Return_type)

- Serial_number: là một biểu thức thời gian, có thể là số hoặc có thể là ngày.

- Return_type: tùy chọn để xác định kiểu giá trị trả về cho hàm (để trống, 1, 2, 3)

Mục đích: Trả về số chỉ thứ trong tuần

Ví dụ: Giả sử tại ô A1 có giá trị “20/12/2010” và ô A1 được định dạng là “dd/mm/yyyy”. Lần lược gọi hàm WEEKDAY() với các tùy chọn:

=WEEKDAY(A1) ->  giá trị trả về là 2

=WEEKDAY(A1,2) ->  giá trị trả về là 1

=WEEKDAY(A1,3) ->  giá trị trả về là 0

=WEEKDAY(A1) ->  Giá trị trả về là Monday (khi ô gọi hàm được định dạng dddd)

g. Hàm EOMONTH

Cú pháp:  EOMONTH(start_date, months)

- Start_date: Ngày biểu thị ngày bắt đầu (*)

- Months: Số tháng trước hoặc sau start_date (*)

h. Hàm EDATE

Cú pháp: EDATE(start_date,months)

- Start_Date: Là ngày bắt đầu , ngày này phải tồn tại trong hệ thống của excel

- Months: Là số tháng trước hoặc sau ngày bắt đầu.
+ Nếu Months > 0 thì hàm sẽ trả về ngày có sau ngày bắt đầu
+ Nếu Months < 0 thì hàm sẽ trả về ngày có trước ngày bắt đầu

Mục đích: Hàm EDate trong excel dùng để trả về một con số đại diện cho một ngày trong hệ thống ngày mặc định của Excel, ngày trả về có thể trước hoặc sau một ngày bắt đầu cho trước.

Ví dụ:

=EDATE("15-Oct-2013",4): 4 tháng sau ngày 15 tháng 10  năm 2013 là ngày bao nhiêu?

=EDATE("15-Oct-2013",-4): 4 tháng trước ngày 15 tháng 10 năm 2013 là ngày bao nhiêu?

=EDATE("31-Oct-2013",4) ==> 28-Feb-2014

=EDATE("31-Oct-2015",4) ==> 29-Feb-2016 (Năm nhuận)

=EDATE(“31-Oct-2013",-1) thì kết quả hàm sẽ trả về là ngày 30 tháng 9 chứ không phải là ngày 31 (Ngày 31 không tồn tại trong tháng 9.

Tương tự khi hàm trả về vào tháng 2, nếu ngày bắt đầu là 29, 30 hoặc 31 thì ngày EDATE vẫn là 28 tháng 2 hoặc 29 tháng 2 nếu là năm nhuận

 

7. Hàm thời gian

a. Hàm TIME

Cú pháp: TIME(Hour,Minute,Second)

– Hour: miêu tả giờ, là một số từ 0 đến 32767. 

– Minute: miêu tả phút, là một số từ 0 đến 32767.

– Second: miêu tả giây, là một số từ 0 đến 32767.

Mục đích: Trả về một chuỗi trình bày một kiểu thời gian đặc thù. Giá trị trả về là một số trong khoảng từ 0 đến 0.99999999, miêu tả thời gian từ 0:00:00 đến 23:59:59.

b. Hàm HOUR

Cú pháp:  HOUR(Serial_num)

– Một chuỗi kí tự nằm trong dấu nháy

– Một số thập phân

– Kết quả của một công thức hay một hàm khác.

Mục đích: Trả về giờ trong ngày của dữ liệu kiểu giờ đưa vào. Giá trị trả về là một kiểu Integer trong khoảng từ 0 (12:00A.M) đến 23 (11:00P.M).

c. Hàm MINUTE

Cú pháp: MINUTE(Serial_num)

– Một chuỗi kí tự nằm trong dấu nháy

– Một số thập phân

– Kết quả của một công thức hay một hàm khác.

Mục đích: Trả về phút của dữ liệu kiểu Time đưa vào. Giá trị trả về là một kiểu Integer trong khoảng từ 0 đến 59.

d. Hàm SECOND

Cú pháp:  SECOND(Serial_num)

– Một chuỗi kí tự nằm trong dấu nháy

– Một số thập phân

 

– Kết quả của một công thức hay một hàm khác.

Mục đích:  Trả về giây của dữ liệu kiểu Time đưa vào. Giá trị trả về là một kiểu Integer trong khoảng từ 0 đến 59.

e. Hàm NOW

Cú pháp:  NOW()

- Hàm này không có các đối số. 

Mục đích: Trả về ngày giờ hiện thời của hệ thống.

 

8. Hàm dò tìm dữ liệu

a. HÀM VLOOKUP

Cú pháp: VLOOKUP(Lookup Value, Table array, Col idx num, [range lookup])

- Lookup Value: Giá trị cần đem ra so sánh để tìm kiếm.(*)

- Table array: Bảng chứa thông tin mà dữ liệu trong bảng là dữ liệu để so sánh. Vùng dữ liệu này phải là tham chiếu tuyệt đối.

- Nếu giá trị Range lookup là TRUE hoặc được bỏ qua, thì các giá trị trong cột dùng để so sánh phải được sắp xếp tăng dần

- Col idx num: số chỉ cột dữ liệu mà bạn muốn lấy trong phép so sánh

Range lookup: Là một giá trị luận lý để chỉ định cho hàm VLOOKUP tìm giá trị chính xác hoặc tìm giá trị gần đúng. + Nếu Range lookup là TRUE hoặc bỏ qua, thì giá trị gần đúng được trả về

Mục đích: Hàm trả về số thứ tự của một ngày nào đó trong tuần

Ví dụ:   =VLOOKUP(F11,$C$20:$D$22,2,0)

- Tìm một giá trị bằng giá trị ở ô F11 ở cột thứ nhất, và lấy giá trị tương ứng ở cột thứ 2

b. HÀM HLOOKUP

Cú pháp: HLOOKUP(Lookup Value, Table array, Col idx num, [range lookup])

- Các tham số tương tự như hàm VLOOKUP.

Mục đích: Tìm kiếm tương tự như hàm VLOOKUP nhưng bằng cách so sánh nó với các giá trị trong hàng đầu tiên của bảng nhập vào.

c. HÀM INDEX

c1)  Hàm INDEX dạng mảng:

Cú pháp:  (Array,Row_num,[Column_num])

- Array: Phạm vi ô hoặc một hằng số mảng (*)
- Row_num: Chọn hàng trong mảng mà từ đó trả về một giá trị
- Column_num: Chọn cột trong mảng mà từ đó trả về một giá trị.

Mục đích: Hàm INDEX dạng mảng trả về một giá trị hay một tham chiếu đến một giá trị trong phạm vi bảng hay vùng dữ liệu.

c2) Hàm INDEX dạng tham chiếu

Cú pháp:  INDEX(Reference,Row_num,[Column_num], [Area_num])

- Reference: Vùng tham chiếu (*)

- Row_num: Chỉ số hàng từ đó trả về một tham chiếu(*)

- Column_num: Chỉ số cột từ đó trả về một tham chiếu

- Area_num: Số của vùng ô sẽ trả về giá trị trong reference. Nếu Area_num được bỏ qua thì hàm INDEX dùng vùng 1

d. HÀM MATCH

Cú pháp:  MATCH(Lookup_value,Lookup_array,[Match_type])

- Lookup_value: Giá trị tìm kiếm logic (*)
- Lookup_array: Mảng để tìm kiếm (*)
- Match_type: Kiểu tìm kiếm (default: 1)

Có 3 kiểu tìm kiếm là:

+  (-)1: Less than

+ 0: Exact match

+  1: Greater than

 

9. Hàm tính tổng

a. Hàm SUM

Cú phápSUM(Number1, Number2…)
- Các tham số: Number1, Number2… là các số cần tính tổng.

b. Hàm SUMIF

Cú pháp:  SUMIF(Range, Criteria, Sum_range)

- Range: Là dãy mà bạn muốn xác định.

- Criteria: Các tiêu chuẩn muốn tính tổng. Đối số này có thể là số, biểu thức hoặc chuỗi.

- Sum_range: Là các ô thực sự cần tính tổng.

Mục đích: Tính tổng của các ô được chỉ định bởi những tiêu chuẩn đưa vào.

 

10. Hàm tính giá trị trung bình

a. Hàm AVERAGE

Cú pháp:  AVERAGE(number1, number2…)

- Các tham số: number1, number2 … là các số cần tính giá trị trung bình.

Mục đích: Trả về gi trị trung bình của các đối số.

b. Hàm SUMPRODUCT

Cú pháp:  SUMPRODUCT(Array1, Array2, Array3…)

- Các tham số: Array1, Array2, Array3… là các dãy ô mà bạn muốn nhân sau đó tính tổng các tích.

Mục đích: Lấy tích của các dãy đưa vào, sau đó tính tổng của các tích đó

Mục đích:  Cộng tất cả các số trong một vùng dữ liệu được chọn.

Minh Triệu

2 Bình luận

Ánh Tuyết
Thứ sáu, 11/09/2015 | 8:20 GMT+7

Những công thức thường dùng này Tuyết rất cần. Tuy nhiên nếu có thêm hình ảnh minh họa nữa thì ok hơn. Cảm ơn chủ thớt.

Minh Quân
Thứ tư, 26/08/2015 | 0:23 GMT+7

Bài viết này rất hữu ích cho dân văn phòng, những người làm sale không rành nhiều về máy tính như mình. Xin cảm ơn !

Viết bình luận

  1.  
  2.  
  3.  
  4.  

Từ khóa tìm kiếm:   excel 2010, excel 2007, cong thuc excel, thu thuat, thu thuat excel, cong thuc thuong dung trong excel