TÌM HIỂU MỘT SỐ HÀM THÔNG DỤNG CỦA EXCEL

Chia sẻ: Vuvan Tu | Ngày: | Loại File: XLS | Số trang:112

Thêm vào BST

Báo xấu

1.100
lượt xem 305
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Tài liệu tham khảo và tìm hiểu một số hàm thông dụng của excel giúp các bạn nắm được các kiến thức về hàm trong excel và vận dụng nó vào trong việc giải bài tập

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: TÌM HIỂU MỘT SỐ HÀM THÔNG DỤNG CỦA EXCEL

TÌM HIỂU MỘT SỐ HÀM THÔNG DỤNG CỦA EXCEL Nội dung Hướng dẫn No. Ngày Ghi chú Thảo 1 LEFT, MID, RIGHT, CONCATENATE 19-Nov Finished Thảo 2 MIN, MAX, AVERAGE 22-Nov Finished Thảo 3 ROUND, ROUNDUP, ROUNDDOWN 26-Nov Finished Thảo 4 SORT, FILTER, ADVANCED FILTER, PASTE SPECIAL 29-Nov Finished Thảo 5 VALUE, TEXT 3-Dec Finished Hà Thảo 6 AND, OR, IF 6-Dec Finished BIÊN Thảo SƠN 7 AND, OR, IF (cont.) 8-Dec Finished Thảo 8 COUNT, COUNTA, COUNTBLANK, COUNTIF 10-Dec Finished LONG Thảo 9 VLOOKUP 13-Dec Finished Thảo 10 PRODUCT, SUMPRODUCT 15-Dec Finished Thảo luận Thảo 11 22-Dec Finished Thảo 12 SUM, SUMIF(basic) 24-Dec Finished Thảo luận Thảo 13 27-Dec Finished Thảo luận Thảo 14 29-Dec Canceled GIAO ĐỀ THI (THEO NHÓM) Thảo 15 31-Dec Finished Thảo 16 TRÌNH BÀY (THEO NHÓM) 7-Jan
TIÊN SƯƠNG ANH LIÊN NHUNG QUYÊN, HẰNG
LEFT(B3,1) LEFT(B3,3) LEFT(B3,5) LEFT(B3,7) LEFT(B3,9) 1234567890 1 123 12345 1234567 123456789 Cell B3 LEFT Cell B8 LEFT(B8,1) LEFT(B8,2) LEFT(B8,6) LEFT(B8,7) LEFT(B8,10) Nguyen Van A N Ng Nguyen Nguyen Nguyen Van Hàm LEFT(X,k): - đưa ra một giá trị mới từ giá trị X ban đầu - giá trị mới này bắt đầu từ ký tự đầu tiên tận cùng bên trái của giá trị X ban đầu - giá trị mới này gồm k ký tự, k là bao nhiêu thì tùy theo nhu cầu giải thích hàm sau: =LEFT(B8,7) Vd: - giá trị ban đầu B8 (X) là Nguyen Van A - ký tự đầu tiên tận cùng bên trái của giá trị X ban đầu là N - giá trị mới sẽ gồm 7 (k) ký tự kể từ ký tự N đếm từ trái tiến sang phải --> như vậy giá trị mới là Nguyen bao gồm cả 1 khoảng trắng ngay sau đó RIGHT(B40,1) RIGHT(B40,2) RIGHT(B40,3) RIGHT(B40,4) RIGHT(B40,5)
1234567890 0 90 890 7890 67890 RIGHT Cell B40 Cell B45 RIGHT(B45,1) RIGHT(B45,2) RIGHT(B45,5) RIGHT(B45,6) RIGHT(B45,12) Nguyen Van A A A Van A Van A Nguyen Van A Hàm RIGHT(X,k): - đưa ra một giá trị mới từ giá trị X ban đầu - giá trị mới này bắt đầu từ ký tự đầu tiên tận cùng bên phải của giá trị X ban đầu - giá trị mới này gồm k ký tự, k là bao nhiêu thì tùy theo nhu cầu giải thích hàm sau: =RIGHT(B40,5) Vd: - giá trị ban đầu B40 (X) là 1234567890 - ký tự đầu tiên tận cùng bên phải của giá trị X ban đầu là chử số 0 - giá trị mới sẽ gồm 5 (k) ký tự kể từ chử số 0 đếm từ phải lùi sang trái --> như vậy giá trị mới là 67890 MID(B78,3,1) MID(B78,3,2) MID(B78,3,3) MID(B78,5,4) MID(B78,5,6) 1234567890 3 34 345 5678 567890 MID
MID Cell B78 Cell B83 MID(B83,1,1) MID(B83,1,6) MID(B83,7,4) MID(B83,8,3) MID(B83,8,4) Nguyen Van A N Nguyen Van Van Van Hàm MID(X,k1,k2): - đưa ra một giá trị mới từ giá trị X ban đầu - giá trị mới này bắt đầu từ ký tự thứ k1 kể từ ký tự đầu tiên của giá trị X ban đầu, k1 là bao nhiêu thì tùy theo nhu cầu - giá trị mới này có tất cả k2 ký tự, k2 là bao nhiêu thì tùy theo nhu cầu giải thích hàm sau: =MID(B83,8,3) Vd: - giá trị ban đầu B83 (X) là Nguyen Van A - ký tự thứ 8 (k1) là V, kể từ ký tự đầu tiên N - giá trị mới này có tất cả 3 (k2) ký tự --> như vậy giá trị mới là Van CONCATENATE(B116,C116) CONCATENATE(B116," ",C116) NCATENATE Anh Hùng AnhHùng Anh Hùng
CONCATENATE Cell B116 Cell C116 CONCATENATE(B120,C120) CONCATENATE(C120,B120) 232001 0 2320010 0232001 Hàm CONCATENATE(X1,X2,..,Xn): - Nối các giá trị X1, X2,…,Xn ban đầu thành một giá trị mới - Các giá trị ban đầu phải luôn cách nhau dấu phẩy (,) và có thứ tự tùy ý theo nhu cầu nối - Nếu có giá trị ban đầu nào được nhập thêm từ bàn phím thì phải đặt nó trong 2 dấu ngoặc kép " " giải thích hàm sau: =CONCATENATE(B116," ",C116) Vd: - giá trị B116 (X1) là Anh - giá trị nhập từ bàn phím (X2) là khoảng trắng - giá trị C116 (X3) là Hùng --> như vậy giá trị mới là Anh Hùng B154&C154 B154&" "&C154 TOÁN TỬ & Anh Hùng AnhHùng Anh Hùng Cell B154 Cell C154
TOÁN TỬ & Cell B154 Cell C154 B158&C158 C158&B158 232001 0 2320010 0232001 Toán tử &: - Nối các giá trị X1, X2,…,Xn ban đầu thành một giá trị mới - Các giá trị ban đầu phải luôn cách nhau dấu phẩy (,) và có thứ tự tùy ý theo nhu cầu nối - Nếu có giá trị ban đầu nào được nhập thêm từ bàn phím thì phải đặt nó trong 2 dấu ngoặc kép " "
toan 8 0 1 7 2 6 4 5 ly 4 7 2 MIN hoa 9 1 0 1 = MIN(C2:C10) = MIN(E2:E10) 0 = MIN(C2:E10) Hàm MIN(Am:Xn) - Tìm và đưa ra giá trị nhỏ nhất trong tất cả các giá trị có trong phạm vi được chọn từ vị trí Am đến Xn (tức là trong phạm vi từ dòng m của cột A đến dòng n của cột X) - Phạm vi này có thể trong một hay nhiều cột, một hay nhiều hàng - Trong phạm vi được chọn nếu có các giá trị là text, khoảng trắng thì các giá trị này sẽ bị hàm bỏ qua giải thích hàm sau: =MIN(C2:E10) Vd: - phạm vi được chọn là từ dòng 2 đến dòng 10 và từ cột C đến cột E - trong cột C có nhiều giá trị là TEXT, cột D toàn là khoảng trắng, cột E có hai khoảng trắng --> như vậy giá trị của hàm trên sẽ là 0 toan 8 0 1 7 2 6 4 5 ly 4 7 2 MAX hoa 9 1 9 8 = MAX(C28:C36) = MAX(E28:E36) 9 = MAX(C28:E36) Hàm MAX(Am:Xn) - Tìm và đưa ra giá trị lớn nhất trong tất cả các giá trị có trong phạm vi được chọn từ vị trí Am đến Xn (tức là trong phạm vi từ dòng m của cột A đến dòng n của cột X) - Phạm vi này có thể trong một hay nhiều cột, một hay nhiều hàng - Trong phạm vi được chọn nếu có các giá trị là text, khoảng trắng thì các giá trị này sẽ bị hàm bỏ qua giải thích hàm sau: =MAX(C28:E36) Vd: - phạm vi được chọn là từ dòng 28 đến dòng 36 và từ cột C đến cột E - trong cột C có nhiều giá trị là TEXT, cột D toàn là khoảng trắng, cột E có hai khoảng trắng --> như vậy giá trị của hàm trên sẽ là 9
toan 8 0 1 7 2 6 4 5 ly 4 7 2 AVERAGE hoa 9 1 3.8 4.7 = AVERAGE(C53:C61) = AVERAGE(E53:E61) 4.3 = AVERAGE(C53:E61) Hàm AVERAGE(Am:Xn) - Tính và đưa ra giá trị trung bình của tất cả các giá trị có trong phạm vi được chọn từ vị trí Am đến Xn (tức là trong phạm vi từ dòng m của cột A đến dòng n của cột X) - Phạm vi này có thể trong một hay nhiều cột, một hay nhiều hàng - Trong phạm vi được chọn nếu có các giá trị là text, khoảng trắng thì các giá trị này sẽ bị hàm bỏ qua giải thích hàm sau: =AVERAGE(C53:C61) Vd: - phạm vi được chọn là từ dòng 53 đến dòng 61 và cột C - trong cột C có 3 giá trị là TEXT, còn lại là các số 0, 1, 2, 4, 7 và 9 --> như vậy giá trị của hàm trên sẽ là 3.8 (là trung bình cộng của 0, 1, 2, 4, 7 và 9)
=ROUND(B3,0) =ROUND(B3,1) =ROUND(B3,2) 6.36 6 6.4 6.36 Cell B3 ROUND Cell B8 =ROUND(B8,-1) =ROUND(B8,-2) 164.7 160 200 Hàm ROUND(X,k): - làm tròn và đưa ra một giá trị mới từ giá trị X ban đầu - Làm tròn phần số lẻ: + giá trị mới này luôn có k số lẻ, k bằng bao nhiêu là tùy theo nhu cầu + giá trị của số lẻ thứ k có thể được giữ nguyên (nếu giá trị của số lẻ thứ k+1 nhỏ hơn 5) hay tăng lên 1 đơn vị (nếu giá trị của số lẻ thứ k+1 từ 5 trở l - Làm tròn phần số nguyên: + k = -1 tương ứng làm tròn hàng đơn vị nghĩa là hàng này luôn được làm tròn về 0 và nếu giá trị của của hàng này nhỏ hơn 5 thì giá trị của hàng chụ ko đổi, ngược lại thì giá trị của hàng chục sẽ được tăng thêm 1 + k = -2 tương ứng làm tròn hàng chục nghĩa là từ hàng này về sau luôn được làm tròn về 0 và nếu giá trị của của hàng này nhỏ hơn 5 thì giá trị của trăm ko đổi, ngược lại thì giá trị của hàng trăm sẽ được tăng thêm 1 + k = -3 tương ứng làm tròn hàng trăm nghĩa là từ hàng này về sau luôn được làm tròn về 0 và nếu giá trị của của hàng này nhỏ hơn 5 thì giá trị của h ngàn ko đổi, ngược lại thì giá trị của hàng ngàn sẽ được tăng thêm 1 giải thích hàm sau: =ROUND(B3,3) Vd1: - giá trị ban đầu B3 (X) là 6.3625 - giá trị mới sẽ có 3 (k) số lẻ - giá trị của số lẻ thứ 4 (k+1) là 5, như vậy giá trị của số lẻ thứ ba (k) sẽ được tăng thêm 1 đơn vị --> như vậy giá trị mới là 6.363 giải thích hàm sau: =ROUND(B8,-2) Vd2: - giá trị ban đầu B8 (X) là 164.7 - giá trị mới sẽ được làm tròn hàng chục (k = -2), nghĩa là giá trị của số hàng chục(6) và hàng đơn vị(4) sẽ được làm tròn về 0 - giá trị của số hàng chục là 6, như vậy giá trị của số hàng trăm(1) sẽ được tăng thêm 1 đơn vị --> như vậy giá trị mới là 200 =ROUNDUP(B40,0) =ROUNDUP(B40,1) =ROUNDUP(B40,2) UP
4.65 5 4.7 4.66 19.12 20 19.2 19.13 ROUNDUP =ROUNDUP(B44,-1) =ROUNDUP(B44,-2) 1.7 10 100 19.7 20 100 480 480 500 3622 3630 3700 Hàm ROUNDUP(X,k): - làm tròn lên và đưa ra một giá trị mới luôn bằng hay lớn hơn giá trị X ban đầu - Làm tròn phần số lẻ: + giá trị mới này luôn có k số lẻ, k bằng bao nhiêu là tùy theo nhu cầu + giá trị của số lẻ thứ k có thể được giữ nguyên (nếu giá trị của số lẻ thứ k+1 bằng 0) hay tăng lên 1 đơn vị (nếu giá trị của số lẻ thứ k+1 lớn hơn 0) - Làm tròn phần số nguyên: + k = -1 tương ứng làm tròn hàng đơn vị nghĩa là hàng này luôn được làm tròn về 0 và nếu giá trị từ hàng này trở về sau lớn hơn 0 thì giá trị của hàng chục sẽ được tăng thêm 1, ngược lại thì giá trị của hàng chục sẽ ko đổi + k = -2 tương ứng làm tròn hàng chục nghĩa là từ hàng này trở về sau luôn được làm tròn về 0 và nếu giá trị từ hàng này trở về sau lớn hơn 0 thì giá của hàng trăm sẽ được tăng thêm 1, ngược lại thì giá trị của hàng trăm sẽ ko đổi + k = -3 tương ứng làm tròn hàng trăm nghĩa là từ hàng này trở về sau luôn được làm tròn về 0 và nếu giá trị từ hàng này trở về sau lớn hơn 0 thì giá của hàng ngàn sẽ được tăng thêm 1, ngược lại thì giá trị của hàng ngàn sẽ ko đi giải thích hàm sau: =ROUNDUP(B40,2) Vd1: - giá trị ban đầu B40 (X) là 4.651 - giá trị mới sẽ có 2 (k) số lẻ - giá trị của số lẻ thứ 3 (k+1) là 1>0, như vậy giá trị của số lẻ thứ hai (k) sẽ được tăng thêm 1 đơn vị --> như vậy giá trị mới là 4.66 giải thích hàm sau: =ROUNDUP(B44,-3) Vd2: - giá trị ban đầu B44 (X) là 1.7 (hay 0001.7) - giá trị mới sẽ được làm tròn tới hàng trăm (k = -3), nghĩa là giá trị của số hàng trăm(0), chục(0), hàng đơn vị(1) và phần số lẻ( - giá trị từ hàng trăm trở về sau là 1.7>0, như vậy giá trị của số hàng ngàn(hiện là 0) sẽ được tăng thêm 1 đơn vị --> như vậy giá trị mới là 1000 =ROUNDDOWN(B78,0) =ROUNDDOWN(B78,1) =ROUNDDOWN(B78,2) OWN
4.65 4 4.6 4.65 ROUNDDOWN 19.12 19 19.1 19.12 =ROUNDDOWN(B85,-1) =ROUNDDOWN(B85,-2) 1.7 0 0 19.7 10 0 480 480 400 3622 3620 3600 Hàm ROUNDDOWN(X,k): - làm tròn xuống và đưa ra một giá trị mới luôn bằng hay nhỏ hơn giá trị X ban đầu - Làm tròn phần số lẻ: + giá trị mới này luôn có k số lẻ, k bằng bao nhiêu là tùy theo nhu cầu + giá trị của số lẻ thứ k+1 trở về sau luôn luôn được làm tròn về 0 - Làm tròn phần số nguyên: + k = -1 tương ứng làm tròn hàng đơn vị nghĩa là từ hàng này về sau luôn luôn được làm tròn về 0 + k = -2 tương ứng làm tròn hàng chục nghĩa là từ hàng này về sau luôn luôn được làm tròn về 0 + k = -3 tương ứng làm tròn hàng trăm nghĩa là từ hàng này về sau luôn luôn được làm tròn về 0 giải thích hàm sau: =ROUNDDOWN(B78,1) Vd1: - giá trị ban đầu B78 (X) là 4.651 - giá trị mới sẽ có 1 (k) số lẻ - giá trị của các số lẻ từ thứ 2 (k+1) trở về sau sẽ bị làm tròn về 0 --> như vậy giá trị mới là 4.6 giải thích hàm sau: =ROUNDDOWN(B85,-3) Vd2: - giá trị ban đầu B85 (X) là 3622 - giá trị mới sẽ được làm tròn tới hàng trăm (k = -3), nghĩa là giá trị của số hàng trăm(6), chục(2) và hàng đơn vị(2) sẽ được làm tròn về 0 --> như vậy giá trị mới là 3000
=ROUND(B3,3) 6.36 =ROUND(B8,-3) 0 n (nếu giá trị của số lẻ thứ k+1 nhỏ hơn 5) hay tăng lên 1 đơn vị (nếu giá trị của số lẻ thứ k+1 từ 5 trở lên) là hàng này luôn được làm tròn về 0 và nếu giá trị của của hàng này nhỏ hơn 5 thì giá trị của hàng chục sẽ từ hàng này về sau luôn được làm tròn về 0 và nếu giá trị của của hàng này nhỏ hơn 5 thì giá trị của hàng từ hàng này về sau luôn được làm tròn về 0 và nếu giá trị của của hàng này nhỏ hơn 5 thì giá trị của hàng ), nghĩa là giá trị của số hàng chục(6) và hàng đơn vị(4) sẽ được làm tròn về 0 =ROUNDUP(B40,3)
4.65 19.12 =ROUNDUP(B44,-3) 1000 1000 1000 4000 n (nếu giá trị của số lẻ thứ k+1 bằng 0) hay tăng lên 1 đơn vị (nếu giá trị của số lẻ thứ k+1 lớn hơn 0) là hàng này luôn được làm tròn về 0 và nếu giá trị từ hàng này trở về sau lớn hơn 0 thì giá trị của hàng từ hàng này trở về sau luôn được làm tròn về 0 và nếu giá trị từ hàng này trở về sau lớn hơn 0 thì giá trị từ hàng này trở về sau luôn được làm tròn về 0 và nếu giá trị từ hàng này trở về sau lớn hơn 0 thì giá trị -3), nghĩa là giá trị của số hàng trăm(0), chục(0), hàng đơn vị(1) và phần số lẻ(7) sẽ được làm tròn về 0 =ROUNDDOWN(B78,3)
4.65 19.12 =ROUNDDOWN(B85,-3) 0 0 0 3000 -3), nghĩa là giá trị của số hàng trăm(6), chục(2) và hàng đơn vị(2) sẽ được làm tròn về 0
PASTE SPECIAL - SORT - FILTER 1. Paste Special\Add: cộng chung giá trị cần copy vào giá trị ban đầu Vị trí B6 có giá trị ban đầu là X1. Giá trị cần copy là X2 nằm tại vị trí C6. Nếu Copy C6 và sử dụng chức năng Paste Special \ Add để dán chồng lên B6 thì giá trị mới của B6 sẽ là Y và Y = X2 + X1 X1 X2 Ví dụ: Giá trị ban đầu B11=12, C11=3 Bước 1: Copy C11
Bước 2: Paste Special lên B11 Bước 3: Phần Operation click chọn Add rồi bấm OK Kết quả cuối cùng: B11=15, C11=3
2. Paste Special\Subtract: trừ giá trị cần copy vào giá trị ban đầu Vị trí B71 có giá trị ban đầu là X1. Giá trị cần copy là X2 nằm tại vị trí C71. Nếu Copy C71 và sử dụng chức năng Paste Special \ Subtract để dán chồng lên B71 thì gi trị mới của B71 sẽ là Y và Y = X1 - X2 X1 X2 Ví dụ: Giá trị ban đầu B75=12, C75=3 12 3 Bước 1: Copy C75 Bước 2: Paste Special lên B75 Bước 3: Phần Operation click chọn Subtract rồi bấm OK Kết quả cuối cùng: B75=9, C75=3
3. Paste Special\Multiply: nhân giá trị cần copy với giá trị ban đầu Vị trí B105 có giá trị ban đầu là X1. Giá trị cần copy là X2 nằm tại vị trí C105. Nếu Copy C105 và sử dụng chức năng Paste Special \ Multiply để dán chồng lên B105 t giá trị mới của B105 sẽ là Y và Y = X1 * X2 X1 X2 Ví dụ: Giá trị ban đầu B109=12, C109=3 12 3 Bước 1: Copy C109 Bước 2: Paste Special lên B109 Bước 3: Phần Operation click chọn Multiply rồi bấm OK Kết quả cuối cùng: B109=36, C109=3
4. Paste Special\Divide: chia giá trị ban đầu cho giá trị cần copy Vị trí B139 có giá trị ban đầu là X1. Giá trị cần copy là X2 nằm tại vị trí C139. Nếu Copy C139 và sử dụng chức năng Paste Special \ Divide để dán chồng lên B139 th giá trị mới của B139 sẽ là Y và Y = X1 / X2 X1 X2 Ví dụ: Giá trị ban đầu B143=12, C143=3 12 3 Bước 1: Copy C143 Bước 2: Paste Special lên B143 Bước 3: Phần Operation click chọn Divide rồi bấm OK Kết quả cuối cùng: B143=4, C143=3