NHÓM HÀM THỜI GIAN VÀ CÔNG THỨC MẢNG

I. Nhóm hàm thời gian
1. Hàm DATEDIF( )
Chức năng: Hàm DATEDIF trả về giá trị là số ngày, số tháng hay số năm giữa khoảng thời gian theo tùy chọn.
Cú pháp: DATEDIF(firstdate,enddate,option)
Các tham số:

• Firstdate: Là ngày bắt đầu của khoảng thời gian cần tính toán.
• Enddate: Là ngày kết thúc của khoảng thời gian cần tính toán.
• Option: Là tùy chọn, xác định kết quả tính toán sẽ trả về trong công thức. Các tùy chọn theo sau:
• “d”: Hàm sẽ trả về số ngày giữa hai khoảng thời gian.
• “m”: Hàm sẽ trả về số tháng (chỉ là phần nguyên) giữa hai khoảng thời gian.
• “y”: Hàm sẽ trả về số năm (chỉ là phần nguyên) giữa hai khoảng thời gian.
• “yd”: Hàm sẽ trả về số ngày lẻ của năm (số ngày chưa tròn năm) giữa hai khoảng thời gian.
• “ym”: Hàm sẽ trả về số tháng lẻ của năm (số tháng chưa tròn năm) giữa hai khoảng thời gian.
• “md”: Hàm sẽ trả về số ngày lẻ của tháng (số ngày chưa tròn tháng) giữa hai khoảng thời gian.

Vd:
 

2. Hàm EDATE( )
Chức năng: Hàm EDATE trả về một ngày nào đó tính từ mốc thời gian cho trước và cách mốc thời gian này một số tháng nhất định.
Cú pháp: EDATE(startdate,months)
Các tham số:

• Startdate: Là ngày được chọn làm mốc thời gian để tính toán. Startdate có thể là Date_text hay tham chiếu đến ô có giá trị ngày tháng.
• Months: Là số tháng các mốc tời gian cho trước. Nếu ngày cần tìm trước ngày làm mốc thì Months sẽ được ghi số âm “-” ngược lại ghi số dương “+” hay không dấu. Nếu Months là một số lẻ thì Excel sẽ lấy phần nguyên, phần lẻ sẽ bỏ đi.

Ghi chú:

• Nếu kết quả trả về một ngày không hợp lệ (VD: 31/04/08) thì Excel sẽ trả về ngày cuối cùng của tháng đó (30/04/08).

Vd:
 

3. Hàm EOMONTH( )
Chức năng: Hàm EOMONTH trả về ngày cuối tháng của tháng nào đó cách mốc thời gian cho trước một số ngày nhất định.
Cú pháp: EOMONTH(startdate,months)
Các tham số:

• Startdate: Là ngày được chọn làm mốc thời gian để tính toán. Startdate có thể là Date_text hay tham chiếu đến ô có giá trị ngày tháng.
• Months: Là số tháng cách mốc thời gian cho trước. Nếu ngày cần tìm trước ngày làm mốc thì Months được ghi số âm “-” ngược lại ghi số dương “+” hay không dấu. Nếu Months là một số lẻ thì Excel sẽ lấy phần nguyên, phần lẻ sẽ bỏ đi.

Vd:

• =EOMONTH(“01/01/2010”,1) giá trị trả về: 28/02/2010
• =EOMONTH(“01/05/2010”,-1) giá trị trả về: 30/04/2010

Áp dụng: Công thức tìm thấy ngày cuối tháng của một giá trị ngày bất kỳ.

• =DAY(EOMONTH(“01/02/2010”,0)) giá trị trả về: 28

4. Hàm WEEKDAY( )
Chức năng: Hàm WEEKDAY trả về một giá trị, là số thứ tự của ngày trong tuần.
Cú pháp: WEEKDAY(serial_number, return_type)
Các tham số:

• Serial_number: Là một biểu thức thời gian, Serial_number có thể là Number hay Value_date.
• Number là số có giá trị tương ứng với giá trị của ngày nào đó. VD ngày 27/04/08 ứng với số 39565.
• Value_date có thể là tham chiếu đến ô nào đó có giá trị ngày tháng, hoặc từ kết quả của một số hàm như Today, Date…
• Return_type: Là tùy chọn để xác định kiểu giá trị sẽ trả về trong công thức, Option có giá trị từ 1 đến 3:
• Option = 1 hoặc để trống: Ngày chủ nhật được xem là 1, ngày thứ bảy là 7.
• Option = 2: Ngày thứ hai được xem là 1, ngày chủ nhật là 7.
• Option = 3: Ngày thứ hai được xem là 0, ngày chủ nhật là 6.

Ghi chú: Nếu trong công thức có Option = 1 và ô có kiểu định dạng là dddd thì kết quả trả về là tên của ngày trong tuần.
Vd:

• =WEEKDAY(DATE(2010,11,27))  giá trị trả về là 7.
• =WEEKDAY(TODAY()) Giá trị trả về là Sunday với kiểu định dạng dddd.
• =WEEKDAY(DATE(2010,4,27),2) giá  trị trả về là 2.
• =WEEKDAY(DATE(2010,4,27),3) giá trị trả về là 1.

II. CÔNG THỨC MẢNG

• Sử dụng công thức mảng thay cho công thức đơn có một số ưu điểm như:
• Đảm bảo sự chính xác về kết quả.
• Tránh trường hợp người sử dụng vô tình xóa công thức.
• Trong một số trường hợp phải dùng công thức mảng mới giải quyết được.

1. Mảng một chiều Vd:

• Để tạo mảng ngang {1, 2, 3, 4, 5} ta chọn 5 ô ngang và gõ ={1,2,3,4,5} nhấn Ctrl+Shift+Enter.

   

• Để tạo mảng dọc {10;20;30;40;50} ta chọn 5 ô dọc và gõ ={10;20;30;40;50} nhấn Ctrl+Shift+Enter.

 

• Hoặc {“Sun”, “Mon”, “Tue”, “Web”, “Thu”, “Fri”, “Sat”} ta chọn 7 ô ngang hoặc dọc và gõ ={“Sun”, “Mon”, “Tue”, “Web”, “Thu”, “Fri”, “Sat”} nhấn Ctrl+Shift+Enter.

2. Mảng hai chiều Vd:

• Tạo mảng 2 chiều sau {1,2,3,4;5,6,7,8;9,10,11,12} ta nhập: ={1,2,3,4;5,6,7,8;9,10,11,12} và nhấn Ctrl+Shift+Enter.

 

3. Đặt tên cho mảng

• Formulas\ Define Name\ Define Name… đặt tên.

 

• Cửa sổ New Name hiện ra

   

• Để hiện các phần tử trong mảng đã đặt tên ta chọn 4 ô ngang và 3 dọc liên tục và gõ =mang và nhấn Ctrl+Shift+Enter

 
.
Để truy xuất các phần tử trong mảng dùng công thức =INDEX(tên mảng, vị trí).
Vd1: =INDEX(mang,3,2) kết quả: 10
 
Vd2: Kiểm tra chuổi có trong mảng hay không?

• Cho mảng tên A4:D8 (đặt tên là data) như hình bên dưới, tại ô A1 nhập tên cần tìm, nếu tên có trong mảng thì tại ô C1 hiện lên chữ (tìm thấy), còn không thì C1 hiện lên Không có (không tìm thấy).
• Công thức tại ô C1 là {=IF(OR(A1=data), “Có”, “Không có”)}
• Hoặc =IF(COUNTIF(data,A1)>0, “Có”, “Không có”)

 

Vd3: Trả về chuỗi dài nhất

• Muốn tìm chuỗi ký tự dài nhất trong các chuỗi thuộc dãy dùng công thức mảng sau:
• {=INDEX(dulieu,MATCH(MAX(LEN(dulieu)),LEN(dulieu),0),1)}
• Hàm này tạo ra hai mảng chứa độ dài của mỗi chuỗi trong dãy được tạo ra bằng hai hàm LEN. Sau đó dùng hàm MAX để xác định giá trị lớn nhất, và hàm MATCH sẽ tìm ra vị trí trong dãy số liệu chuỗi dài nhất này. Cuối cùng hàm INDEX sẽ trả về nội dung của ô chứa chuỗi dài nhất.

 

4. Các phép toán lý luận trên mảng

• Điều kiện AND : Sử dụng phép nhân trong biểu thức so sánh mảng.

Vd: Có bao nhiêu người nữ ở HCM.
   

• {=SUM((B2:B21="nữ")*(C2:C21="HCM"))}
• {=SUM(IF(B2:B21="Nữ",1,0)*IF(C2:C21="HCM",1,0))}

Điều kiện OR: Sử dụng phép cộng
Vd: Có bao nhiêu người ở Long An và Hà Nội.

• {=SUM((C2:C21="Hà Nội")+(C2:C21="Long An"))}