Tiểu luận Về các hàm thông dụng trong excel

Đề tài: hàm cơ sở dữ liệu trong Excel I. Giới thiệu: 1. Tìm hiểu về excel và hàm dữ liệu cơ sở trong excel EXCEL là một phần mềm chuyên dung cho công tác kế toán văn phòng trên môi trường Windows, thực hiện được nhiều phép tính từ đơn giản đến phức tạp. EXCEL tổ chức và lưu trữ thhoong tin dưới dạng bảng lương, bảng thanh toán, bảng thống kê, bảng dự toán, và khi có sự thay đổi dữ liệu bảng tính tự động tính toán lại theo số liệu mớ. Thao tác trên bảng tính có thể tạo ra các báo cóa tổng hợp hoặc phân tích có kèm theo các biểu đồ, hình vẽ mih họa. Hàm trong excel là 1 công cụ nhằm giải quyết 1 công việc nhất định. Đó là những hàm được viết sẵn bởi các chương trình được đưa vào trong excel. Khi cần sử dụng chỉ việc lấy ra để sử dụng nhưng phải viết đúng theo dạng thức quy định. Nó có dạng: Tên hàm ( danh sách đối số ) hoặc tên hàm ( ) * Các hàm này được chia thành 9 nhóm: - Nhóm hàm thống kê ( statistical ): hàm average, count, countA,…. - Nhóm hàm ngày và giờ ( date and time ): hàm now, today, date,… - Nhóm hàm logic ( logical ): hàm Not, or, and,…. - Nhóm hàm kí tự ( text ) - Nhóm hàm dò tìm và tham chiếu ( lookup and reference ) - Nhóm hàm thong tin ( information ) - Nhóm hàm tài chính ( financial ) - Nhóm hàm cơ sở liệu ( database ) 2. Dạng hàm dữ liệu cơ sở trong excel * Dạng 1: hàm có kèm theo đối số Tên hàm ( danh sách đối số ) Trọng đó danh sách đối số được ngăn cách nhau bởi dấu phẩy ( , ) hoặc dấu chấm phẩy ( ; ) và chỉ được sử dụng 1 loại dấu này. Việc sử dụng dấu phẩy ( , ) hay dấu chấm phẩy ( ; ) là do khai báo trong control panel. Trong bài học ta quy ước đối số ngăn cách nhau bởi dấu phẩy ( , ). Đối số ở đây có thể là hằng số, địa chỉ cột, địa chỉ vùng, hàm, biểu thức, tên vùng. Nếu là hằng chuỗi phải để trong cắp dấu ngoặc kép ( “ “ ) VD: COUNT( đối số 1, đối số 2,… ) * Dạng 2: hàm không có đối số Tên hàm( ) VD: hàm TODAY( ) 3. Các cách sử dụng hàm: Cách 1: viết trực tiếp 1 hàm Bắt đầu bởi dấu bằng ( = ) sau đó viết theo đúng dạng hàm quy định và kết thúc bằng phím enter Nếu hàm tham gia trong công thức, thì không viết dấu bằng đằng trước VD: dữ liệu trong ô A1 là số -4 Tại ô B1 ta nhập =ABS(A1) và nhấn enter → kết quả là 4 tại ô B1 Cách 2: dung thực đơn Mở thực đơn insert → function → xuất hiện hộp thoại insert function: Tại mục Search for a function: đưa vào tên cần sử dụng, kích chuột tại nút Go. Máy tính sẽ cho ta hàm cần tìm, sau đó ta sẽ khai báo tiếp trong hàm đó để sử dụng. Tại hộp Or select a category: lựa chọn theo nhóm hàm. Hộp select a function: hiện ra các hàm nằm trong nhóm hàm mà ta lựa chọn tại hộp Or select a category Sau khi kích chuột tại nút Ok trên màn hình hiện ra hộp thoại Function Argument để ta đưa vào giá trị cho các đối số. Kết thúc chèn hàm kích chuột tại nút Ok. Cách 3: dùng nút chèn hàm trên thanh công thức Kích chuột vào nút Insert Function f(x) trên thanh công cụ → chọn mục hàm → chọn hàm → Ok → nhập các đối số → Ok II. Một số hàm thông dụng 1. Một số hàm thống kê a. Hàm AVERAGE - Dạng hàm: AVERAGE( number1, number2,… ) Trong đó: number1, number2,… là các trị số, địa chỉ ô hay vùng chứa trị số… - Ý nghĩa: hàm trả về giá trị trung bình của tất cả các đối số number1, number2,… VD: dữ liệu trong các ô A1, A2, A3, A4 là 1,2,3,4. Công thức ở ô A5 là =AVERAGE(A1:A4) giá trị trả về ô A5 là 2.5 b. Hàm COUNT - Dạng hàm: COUNT(value1, value2,…. ) Trọng đó: value1, value2,… là các trị số, địa chỉ ô hay vùng chứa các trị số… - Ý nghĩa: hàm đếm số các dữ liệu có kiểu số có trong danh sách các value1, value2,… VD: =COUNT(-5,7,”DHTM”,8.9) cho kết quả là 4 c. Hàm COUNTA -Dạng hàm: COUNTA(value1, value2,… ) Trong đó: value1, value2,… là các giá trị số, chuỗi, địa chỉ ô hay vùng…. Ý nghĩa: đếm số giá trị chứa dữ liệu bất kì, bao gồm cả giá trị chuỗi rỗng… VD: Công thức: =COUNTA(A1:C2) cho giá trị là 6 d. Hàm COUNTIF - Dạng hàm: COUNTIF(range, criteria ) Trong đó: range: là địa chỉ vùng cần đếm Criteria: là tiêu chuẩn dưới dạng 1 số hay 1 chuỗi để xác định ô nào được đếm tiêu chuẩn phải để trong cặp dấu nháy (“ “). VD: Công thức: =COUNTIF(A1:C2,”>=5”) cho kết quả là 5 e. Hàm RANK - Dạng RANK(x, danh sách ) Trong đó X là các giá trị số, địa chỉ ô chứa giá trị số,… Danh sách là vùng chứa các giá trị số - Ý nghĩa: xác định thứ hạng của giá trị x so với các giá trị trong danh sách ( thứ hạng được xếp theo giá trị giảm dần ) VD: Nhập công thức: =RANK(C2,$C$2:$C$5) kết quả sẽ là 4.Sao chép công thức với các ô D3, D4, D5 được kết quả như trên. 2. Một số hàm toán học a. Hàm ABS - Dạng hàm: ABS(number) - Ý nghĩa: trả về giá trị tuyệt đối của number VD1: =ABS(-4) cho giá trị là 4 Ô A1 chứa giá trị số là -5 thì công thức =ABS(A1) cho giá trị số là 5 VD2: Nhập công thức tại ô D6 là: =ABS(C2) kết quả là 6,sao chép công thức với các ô D3, D4, D5 được kết quả như trên. b. Hàm SQRT - Dạng hàm: SQRT(number) Trong đó: number có giá trị số không âm - Ý nghĩa: cho căn bậc 2 của number VD1: Công thức = SQRT(4) cho kết quả là 2 Ô A2 chứa giá trị số 16 thì công thức =SQRT(A2) cho giá trị số là 4 VD2: c. Hàm INT - Dạng hàm: INT(number) - Ý nghĩa: trả về giá trị là phần nguyên của number VD1: =INT(725.152) cho giá trị là 725 VD2: Nhập vào ô B2 công thức =INT(A2) được kết quả là 5, copy công thức với các ô B3, B4, B5 được kết quả như trên. d. Hàm MOD - Dạng hàm: MOD(number,divisor) Trọng đó: Number là số chia Divisor là số bị chia - Ý nghĩa: trả về kết quả là phần dư của phép chia number cho divisor - Chú ý: nếu divisor=0 thì công thức trả về kết quả là giá trị lỗi #DIV/0! VD1: =MOD(7,2) cho kết quả là 1 VD2: e. Hàm ROUND - Dạng hàm: ROUND(number,num_digits) Trong đó: Number: giá trị số Num_digits: là chữ số ( để chỉ độ chính xác ) làm tròn - Ý nghĩa: trả về giá trị đã đuwọc làm tròn của number với độ chính xác đến num_digits - Chú ý: Nếu num_digits=0 thì số sẽ được làm tròn đến phần nguyên gần nhất Nếu num_digits<0 thì số sẽ được làm tròn đến vị trí – num_digits tính từ trái sang phải ở phần nguyên Nếu num_digits>0 thì số sẽ được làm tròn đến vị trí num_digits tính từ trái sang phải ở phần thập phân VD: =ROUND(3.152,0) cho kết quả là 3 =ROUND(3152,-3) cho kết quả là 3150 =ROUND(3.1526,3) cho kết quả là 3,153 f. Hàm SUM - Dạng hàm: SUM(number1, number2,…) Trong đó: number1, number2,… là các giá trị số, địa chỉ ô hay vùng chứa các giá trị số,…. - Ý nghĩa: trả về tổng giá trị các ô chứa dữ liệu số của value1, value2,… VD: -SUM(1,2,3,4,5) cho kết quả là 15 g. Hàm SUMIF - Dạng hàm: SUMIF(vùng tiêu chuẩn,tiêu chuẩn,vùng lấy tổng) - Ý nghĩa: khi nhập hàm vào thì chương trình sẽ so sánh vùng tiêu chuẩn với tiêu chuẩn, giá trị thỏa mãn sẽ được ghi lại và cuối cùng cho kết quả là tổng các giá trị đó VD; Để tính tổng số tiền bán vé loại A ta nhập công thức: =SUMIF($B$2:$B$6,”A”,$C$2:$C$6) kết quả sẽ là 5.000.000 Copy công thức với các ô E3, E4(thay tiêu chuẩn “A” lần lượt thành “B”, “C”) ta được kết quả như bảng trên. h. Hàm MAX - Dạng hàm: MAX( danh sách các giá trị ) - Ý nghĩa: cho ra giá trị lớn nhất trong danh sách giá trị VD: =MAX(3,4,5,9.10) cho kết quả là 10 i. Hàm MIN - Dạng hàm: MIN( danh sách các giá trị ) - Ý nghĩa: cho ra giá trị nhỏ nhất trong danh sách giá trị VD: =MIN(3,4,5,9,10) cho kết quả là 3 k.Hàm PI -Dạng hàm: PI() -Ý nghĩa: cho giá trị là số pi Vd: Nhập công thức =PI() vào một ô tính trong excel được kết quả là số pi bằng 3.141593. l.Hàm FACT -Dạn hàm: FACT(number) Ý nghĩa: tính giai thừa của số number. Vd: =FACT(4) cho kết quả là 24 3, Một số hàm logic a. Hàm NOT - Dạng hàm: NOT(logical) Trong đó logical là 1 giá trị hoặc 1 biểu thức nhận giá trị TRUE hoặc FALSE - Ý nghĩa: Nếu logical nhận giá trị TRUE thì hàm trả lại giá trị FALSE Nếu logical nhận giá trị FALSE thì hàm trả lại giá trị TRUE VD: =NOT(4>=5) cho kết quả TRUE =NOT(4<5) cho kết quả FALSE =NOT(2.3=7) cho kết quả TRUE b. Hàm OR - Dạng hàm: OR(logical1,logical2,…) Trọng đó logical1, logical2,… là các biểu thức điều kiện nhận giá trị TRUE hoặc FALSE - Ý nghĩa: hàm sẽ trả về kết quả TRUE nếu 1 trong các diều kiện logical1, logical2,… nhận giá trị TRUE Nếu tất cả các giá trị logical1, logical2,… đều nhận giá trị FALSE thì hàm sẽ trả về kết quả là FALSE VD: =OR(3>2,9=8) cho kết quả TRUE =OR(1<2<3) cho kết quả TRUE =OR(1>2>3) cho kết quả FALSE =OR(1+1=1.1+2.3=5) cho kết quả FALSE c. Hàm AND - Dạng hàm: AND(logical1, logical2,…) Trong đó: logical1, logical2,…. Là các biểu thức điều kiện nhận giá trị TRUE hoặc FALSE - Ý nghĩa: hàm sẽ trả về kết quả là TRUE nếu tất cả các điều kiện logical1, logical2,… điều nhận giá trị TRUE, ngược lại trả về kết quả FALSE VD: =AND(3>2,5<8) cho kết quả TRUE =AND(6=2.3=5) cho kết quả FALSE =AND(4>3>2) cho kết quả TRUE d. Hàm IF - Dạng hàm: IF(logical_test, value_if_true_, value_if_false) Trong đó: Logical_test: là 1 giá trị số hoặc 1 biểu thức bắt giá trị là TRUE hoặc FALSE Value_if_true: là giá trị được trả về nếu logical_test có giá trị khác 0 hoặc TRUE Value_if_false: là giá trị được trả về nếu logcal_test có giá trị là 0 hoặc là FALSE Thực tế value_if_true và value_if_false là các biểu thức, các đối số này có thể lại là các hàm IF khác lồng nhau VD1: =IF(5<6,”TRUE”,”FALSE”) → cho kết quả là TRUE VD2: 4. Một số hàm Ngày và Giờ a. Hàm DAY - Dạng hàm: DAY( dữ liệu kiểu ngày ) - Ý nghĩa: cho giá trị ngày của dữ liệu kiểu ngày VD: =DAY(“11/20/11”) cho kết quả là 20 nếu kiểu định dạng ngày tháng năm là mm/dd/yy b. Hàm MONTH - Dạng hàm: MONTH( dữ liệu kiểu ngày ) - Ý nghĩa: cho giá trị tháng của dữ liệu kiểu ngày VD: =MONTH(“11/20/2011”) cho kết quả là 11 c. Hàm NOW - Dạng hàm: NOW( ) - Ý nghĩa: trả về ngày và giờ hiện hành của hệ thống d. Hàm TODAY - Dạng hàm: TODAY( ) - Ý nghĩa: trả về ngày hiện hành của hệ thống e. Hàm YEAR - Dạng hàm: YEAR( dữ liệu kiểu ngày ) - Ý nghĩa: cho giá trị năm của dữ liệu kiểu ngày VD: =YEAR(“22/12/2011”) cho kết quả là 2011 với định dạng ngày của hệ thống là dd/mm/yyyy 5. Một số hàm kí tự a. Hàm LEFT - Dạng hàm: LEFT(TEXT,N) - Ý nghĩa: cho giá trị là chuỗi của chuỗi được tính từ trái sang phải N kí tự VD: =LEFT(“ABCDE”,4) cho chuỗi ABCD b. Hàm RIGHT - Dạng hàm: RIGHT(TEXT,N) - Ý nghĩa: cho giá trị là chuỗi của chuỗi được tính từ phải qua trái N kí tự VD: =RIGHT(“ABCDE”,4) cho chuỗi BCDE c. Hàm MID - Dạng hàm: MID(TEXT,m,n) - Ý nghĩa: lấy n kí tự của chuỗi TEXT bắt đầu từ vị trí thứ m tính từ trái sang phải VD: =MID(“DAI HOC”,5,3” cho giá trị là chuỗi “ HOC” d. Hàm LEN - Dạng hàm: LEN(TEXT) - Ý nghĩa: cho độ dài của chuỗi TEXT VD: =LEN(“DAI HOC THUONG MAI”) cho giá trị là 18 e. Hàm LOWER - Dạng hàm: LOWER(TEXT) - Ý nghĩa: chuyển đổi chuỗi TEXT thành chuỗi thường VD: =LOWER(“Ha Noi”) cho kết quả là “ha noi” f. Hàm UPPER - Dạng hàm: UPPER(TEXT) - Ý nghĩa: chuyển đổi chuỗi TEXT thành chuỗi chữ hoa VD: =UPPER(“ha noi”) cho kết quả là chuỗi “HA NOI” g. Hàm PROPER - Dạng hàm: PROPER(TEXT) - Ý nghĩa: chuyển các ký tự đầu của chuỗi TEXT thành chữ hoa VD: =PROPER(“ha noi”) cho kết quả là chuỗi “Ha noi” h. Hàm TEXT - Dạng hàm: TEXT( giá trị số, định dạng ) - Ý nghĩa: chuyển giá trị số thành chuỗi theo quy định đã định VD: =TEXT(123,0000) cho kết quả 0123 =TEXT(56.826,00.00) cho kết quả 56.83 =TEXT(23.6,0000) cho kết quả 023.600 i. Hàm VALUE - Dạng hàm: VALUE( chuỗi ) - ý nghĩa: chuyển đổi chuỗi có dạng số sang số VD: =VALUE(“56.32”) cho kết quả là giá trị số 56.32 6. Các hàm tìm kiếm a. Hàm VLOOKUP - Dạng hàm: VLOOKUP(x, vùng tham chiếu, cột tham chiếu, cách dò ) Trong đó: Cột tham chiếu là thứ tự của cột trong vùng tham chiếu tính từ trái qua phải X là giá trị cần tìm để so sánh với giá trị tương ứng trong vùng tham chiếu Vùng tham chiếu nên để giá trị tuyệt đối nếu công thức còn được sao chép ở các ô khác Cách dò là số 0 hoặc 1. Nếu là 1 thì vùng tham chiếu phải được sắp xếp theo thứ tự tăng dần theo cột. Nếu cách dò là 0 thì vùng tham chiếu không cần phải sắp xếp Ý nghĩa: sau khi nhập hàm máy tính sẽ thực hiện dò tìm giá trị x trong vùng tham chiếu, nếu thấy x máy tính sẽ cho kết quả là giá trị trong cột tham chiếu tương ứng với x. VD: Công thức ở ô E6 là: =VLOOKUP(“A”,$A$1,$B$3,2,0)*D6 thì kết quả ở ô E6 là 5000. Copy công thức với các ô E7,E8,E9 (và lần lượt thay “A” bằng “B”, “C”, “D”) được kết quả như trên.Ô E9 bị lỗi là do trong hàm không tìm thấy “D” trong vùng tham chiếu. b. Hàm HLOOKUP - Dạng hàm: HLOOKUP(x,bảng, hàm tham chiếu, cách dò) - Ý nghĩa: tương tự hàm VLOOKUP nhưng lúc này hàm HLOOKUP sẽ dò tìm ở hàng trên cùng vùng tham chiếu, tham chiếu số liệu ở các hàng phía dưới. VD: Công thức ở ô E6 là =HLOOKUP(“A”,$A$1:$C$2,2,0)*D6 kết quả sẽ là 5000. Copy công thức cho các ô E7,E8,E9(và lần lượt thay “A” bằng “B”, “C”, “D”) được kết quả như trên. Ô E9 bị lỗi là do trong hàm không tìm thấy “D” trong vùng tham chiếu. KẾT LUẬN. Qua sự tìm hiểu về các hàm cơ sở dữ liệu trong excel ở trên ta thấy phần mềm excel hay hàm dữ liệu trong excel là rất quan trọng trong công việc hiện nay của con người mà đặc biệt là những người làm công tác kế toán, văn phòng.Tin rằng trong tương lai phần mềm excel vẫn là công cụ tốt nhất, mang lại lợi ích cho con người. TÀI LIỆU THAM KHẢO. 1. Giáo trình tin học đại cương Tác giả: Bùi Thế Tâm 2. Giáo trình tin học đại cương của đại học Cần Thơ.