46 Hàm excel ôn thi Chuẩn CNTT cơ bản

I.      NHÓM HÀM TOÁN HỌC

1.      ABS: trả về trị tuyệt đối của đối số.

2.      INT: trả về phần nguyên nhỏ hơn đối số.

3.      MOD(a;b): trả về phần dư của phép chia nguyên.

4.      SQRT: căn bậc hai.

5.      SUM: tính tổng.

6.      SUMIF(vùng dữ liệu; điều kiện; vùng tính tổng): tính tổng các giá trị tương ứng thỏa điều kiện.

7.      ROUND(abc,xyz;n): làm tròn số theo chỉ số n.

Ví dụ 1: =ABS(-2)         => 2

Ví dụ 2: =INT(1,5)         => 1 

Ví dụ 3: =MODS(7;2)    => 1

Ví dụ 4: =SQRT(49)      => 7

Ví dụ 5: =SUM(1;2;3)    => 6

Ví dụ 6:

= SUMIF(C2:D6;">6";D2:D6)          => 18000

Ví dụ 2: =Round(1,5;0)         => 2

II.      NHÓM HÀM THỐNG KÊ

1.      Count: đếm số ô là số.

2.      Counta: đếm số ô có dữ liệu.

3.      Coutblank: đếm số ô trống.

4.      Countif(vùng dữ liệu;điều kiện): đếm số ô thỏa điều kiện

5.      Max: trả về trị lớn nhất.

6.      Min: trả về trị nhỏ nhất.

7.      Average: tính trung bình cộng.

Ví dụ 1: =COUNT(B2:C8)    => 5

Ví dụ 2: =COUNTA(B2:C8)  => 10

Ví dụ 3: =COUNTBLANK(B2:C8)                 => 4

Ví dụ 4: =COUNTIF(B2:C8;">=6")               => 3

Ví dụ 5: =MAX(C2:C8)                                     => 10

Ví dụ 6: =MIN(C2:C8)                                      => 2

Ví dụ 7: =AVERAGE(C2:C8)                          => 6

III.      NHÓM HÀM XỬ LÝ CHUỖI

1.      Len: trả về chiều dài chuỗi.

2.      Left(chuỗi,n): cắt lấy bên trái chuỗi n kí tự.

3.      Right(Chuỗi;n): cắt lấy bên phải chuỗi n kí tự.

4.      Mid(chuỗi;k;n): từ vị trí thứ k cắt lấy n kí tự.

5.      Lower: chuyển chuỗi sang chữ thường.

6.      Proper: chuyển kí tự đầu của mỗi từ sang chữ in hoa.

7.      Upper: chuyển chuỗi sang chữ in hoa.

Ví dụ 1: =LEN(C1)                           => 12

Ví dụ 2: =LEFT(C1;2)                      => Số

Ví dụ 3: =RIGHT(C1;3)                    => bán

Ví dụ 4: =MID(C1;4;5)                     => lượng

Ví dụ 5: =LOWER(C1)                     => số lượng bán

Ví dụ 6: =Proper(C1)                        => Số Lượng Bán

Ví dụ 7: =UPPER(C1)                      => SỐ LƯỢNG BÁN

IV.      NHÓM HÀM NGÀY, GIỜ

1.      Today: trả về ngày/tháng/năm hiện tại.

2.      Now: trả về ngày, giờ hiện tại.

3.      Day: trả về ngày.

4.      Month: trả về tháng.

5.      Year: trả về năm.

6.      Hour: trả về giờ.

7.      Minute: trả về phút.

8.      Second: trả về giây.

Ví dụ 1: =TODAY( )                         => 6/22/2014

Ví dụ 2: =NOW                                => 6/22/2014 15:56

Ví dụ 3: =DAY(TODAY( ))              => 22

Ví dụ 4: =MONTH(TODAY( ))        => 6

Ví dụ 5: =YEAR(TODAY( ))            => 2014

Ví dụ 6: =HOUR(NOW( )                => 15

Ví dụ 7: =MINUTE(NOW( ))            => 56

Ví dụ 8: = =SECOND("15:56:03")   => 03

V.      NHÓM HÀM ĐIỀU KIỆN

1.      And: chỉ trả về giá trị true, khi tất cả biểu thức có giá trị true.

2.      Or: chỉ trả về giá trị false, khi tất cả biểu thức có giá trị false.

3.      Not: trả về giá trị flase, khi biểu thức có kết quả true và ngược lại.

4.      If(biểu thức; giá trị 1; giá trị 2):

     Nếu biểu thức đúng, thì trả về giá trị 1; ngược lại trả về giá trị 2.

     Giá trị 2 có thể là hàm if.

     Số lựa chọn -1 = số hàm if.

Ví dụ:

=IF(AND(C2<C3;C4<C5);LEN(C1);LEN(D1))             => 12

5.      Find(từ cần tìm; chuỗi; vị trí bắt đầu tìm)

=Find("A";B2) -> 1

VI.      NHÓM HÀM DÒ TÌM

1.      Hlookup(trị dò; bảng dò; hàng trả về; cách dò)

=HLOOKUP(2;$A$2:$D$6;3;0)               => 6

2.      Vlookup(trị dò; bảng dò; cột trả về; cách dò)

=VLOOKUP(3;$A$2:$D$6;3;0)               => 6

* Lưu ý: phải cố định bảng dò bằng phím F4.

VII.      NHÓM HÀM CƠ SỞ DỮ LIỆU

1.      Daverage(vùng dữ liệu, cột tính trung bình cộng, điều kiện)

2.      Dsum(vùng dữ liệu, cột tính tổng, điều kiện)

3.      Dcount(vùng dữ liệu, cột cần đếm, điều kiện)

4.      Dmax(vùng dữ liệu;cột trả về giá trị lớn nhất, điều kiện)

5.      Dmin(vùng dữ liệu; cột trả về giá trị nhỏ nhất, điều kiện)

Ví dụ 1:  =DAVERAGE(B1:D6;C1;D7:D8)         => 8

Ví dụ 2: =DSUM(B1:D6;C1;D7:D8)                     => 24

Ví dụ 3: =DCOUNT(B1:D6;C1;D7:D8)               => 3

Ví dụ 4: =DMAX(B1:D6;C1;D7:D8)                     => 10

Ví dụ 5: =DMIN(B1:D6;C1;D7:D8)                       => 6

VIII.      NHÓM HÀM KHÁC

1.      Rank(số, vùng dữ liệu, cách xếp hạng): trả về thứ hạng của số trong vùng dữ liệu. Phụ thuộc vào cách xếp hạng 0 hay 1.

Ví dụ:

=RANK(C2;C2:C6;0)              =>5

2.      ISNA: kiểm trả biểu thức có phải là #N/A hay không, nếu đúng trả về TRUE

=ISNA(HLOOKUP(3;$A$2:$D$6;3;0))     => TRUE

3.      MATCH(trị dò;bảng dò; cách dò): tìm trị dò trong bảng dò, nếu tìm thấy trả về vị trí của trị dò. 
0: dò chính xác; 1: nhỏ hơn giá trị đem dò; -1: lớn hơn giá trị đem dò (trừ giá trị lớn nhất).
=MATCH(6;C2:C6;0)                           => 3
=MATCH(5;C2:C6;1)                           => 2

4.      INDEX(mảng;X;Y): tìm trị dò giao điểm của hàng X và cột Y trong mảng 

=INDEX($C$2:$C$6;3;1)                   => 6

5.      Choose(n;a1;a2;....): n=1 trả về a1; n=2; trả về a2;.....
=CHOOSE(3;2;4;6)                            => 6