Bạn đang xem bài viết 43 Hàm Microsoft Excel Cơ Bản Và Nâng Cao Cho Kế Toán được cập nhật mới nhất trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất.
Microsoft Excel là 1 công cụ tính toán rất cần thiết đối với những người làm việc trong văn phòng. Hiện tại, thì Microsoft quá các phiên bản 2003, 2007, 2010, 2013, 2023, 2023 đã có rất nhiều hàm công thức giúp cho công việc tính toán trở nên nhẹ nhàng và dễ dàng hơn bao giờ hết.
Sự đa dạng về hàm và công thức là 1 lợi thế nhưng cũng là 1 nhược điểm vì không ai có thể nhớ hết 1 lúc mấy trăm hàm cùng lúc và cách sử dụng. Chính vì vậy, mình sẽ phân chia các hàm Excel cho từng đối tượng khác nhau. Trong bài viết này mình sẽ tổng hợp 8 hàm cơ bản và 43 hàm nâng cao dành cho các anh chị em kế toán.
Hàm Excel cơ bản dành cho kế toán Hàm Excel nâng cao dành cho kế toán1. Hàm AND – Cú pháp: AND (Logical1, Logical2,….) – Các đối số: Logical1, Logical2… là các biểu thức điều kiện. – Hàm trả về giá trị TRUE (1) nếu tất cả các đối số của nó là đúng, trả về giá trị FALSE (0) nếu một hay nhiều đối số của nó là sai. Lưu ý: – Các đối số phải là giá trị logic hoặc mảng hay tham chiếu có chứa giá trị logic. – Nếu đối số tham chiếu là giá trị text hoặc Null (rỗng) thì những giá trị đó bị bỏ qua. – Nếu vùng tham chiếu không chứa giá trị logic thì hàm trả về lỗi #VALUE! – Ví dụ: =AND(D7>0,D7<5000) 2. Hàm OR – Cú pháp: OR (Logical1, Logical2…) – Các đối số: Logical1, Logical2… là các biểu thức điều kiện. – Hàm trả về giá trị TRUE (1) nếu bất cứ một đối số nào của nó là đúng, trả về giá trị FALSE (0) nếu tất cả các đối số của nó là sai. – Ví dụ: =OR(F7>03/02/74,F7>01/01/2002) 3. Hàm NOT – Cú pháp: NOT(Logical) – Đối số: Logical là một giá trị hay một biểu thức logic. – Hàm đảo ngược giá trị của đối số. Sử dụng NOT khi bạn muốn phủ định giá trị của đối số trong phép toán này.
II. NHÓM HÀM TOÁN HỌC4. Hàm ABS – Lấy giá trị tuyệt đối của một số – Cú pháp: ABS(Number) – Đối số: Number là một giá trị số, một tham chiếu hay một biểu thức. – Ví dụ: =ABS(A5 + 5) 5. POWER – Hàm trả về lũy thừa của một số. – Cú pháp: POWER(Number, Power) – Các tham số: Number: Là một số thực mà bạn muốn lấy lũy thừa. – Power: Là số mũ. – Ví dụ = POWER(5,2) = 25 6. Hàm PRODUCT – Bạn có thể sử dụng hàm PRODUCT thay cho toán tử nhân * để tính tích của một dãy. – Cú pháp: PRODUCT(Number1, Number2…) – Các tham số: Number1, Number2… là dãy số mà bạn muốn nhân. 7. Hàm MOD – Lấy giá trị dư của phép chia. – Cú pháp: MOD(Number, pisor) – Các đối số: Number: Số bị chia. – pisor: Số chia. 8. Hàm ROUNDUP – Làm tròn một số. – Cú pháp: ROUNDUP(Number, Num_digits)
– Các tham số: Number: Là một số thực mà bạn muốn làm tròn lên. – Number_digits: là bậc số thập phân mà bạn muốn làm tròn. – Chú ý: – Nếu Num_digits > 0 sẽ làm tròn phần thập phân. – Nếu Num_digits = 0 sẽ làm tròn lên số tự nhiên gần nhất. – Nếu Num_digits < 0 sẽ làm tròn phần nguyên sau dấu thập phân. 9. Hàm EVEN – Làm tròn lên thành số nguyên chẵn gần nhất. – Cú pháp: EVEN(Number) – tham số: Number là số mà bạn muốn làm tròn. – Chú ý: – Nếu Number không phải là kiểu số thì hàm trả về lỗi #VALUE! 10. Hàm ODD – Làm tròn lên thành số nguyên lẻ gần nhất. – Cú pháp: ODD(Number) – Tham số: Number là số mà bạn muốn làm tròn. 11. Hàm ROUNDDOWN – Làm tròn xuống một số. – Cú pháp: ROUNDDOWN(Number, Num_digits) – Các tham số: tương tự như hàm ROUNDUP.
III. NHÓM HÀM THỐNG KÊA. Nhóm hàm tính tổng 12. Hàm SUM – Cộng tất cả các số trong một vùng dữ liệu được chọn.
– Cú pháp: SUM(Number1, Number2…) – Các tham số: Number1, Number2… là các số cần tính tổng. 13. Hàm SUMIF – Tính tổng của các ô được chỉ định bởi những tiêu chuẩn đưa vào. – Cú pháp: SUMIF(Range, Criteria, Sum_range) – Các tham số: Range: Là dãy mà bạn muốn xác định. – Criteria: các tiêu chuẩn mà muốn tính tổng. Tiêu chuẩn 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. – Ví dụ: = SUMIF(B3:B8,”<=10″) – Tính tổng của các giá trị trong vùng từ B2 đến B5 với điều kiện là các giá trị nhỏ hơn hoặc bằng 10.B. Nhóm hàm tính giá trị trung bình 14. Hàm AVERAGE – Trả về gi trị trung bình của các đối số. – 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. 15. Hàm SUMPRODUCT – Lấy tích của các dãy đưa vào, sau đó tính tổng của các tích đó. – 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. – Chú ý: – Các đối số trong các dãy phải cùng chiều. Nếu không hàm sẽ trả về giá trị lỗi #VALUE.
C. Nhóm hàm tìm giá trị lớn nhất và nhỏ nhất 16. Hàm MAX – Trả về số lớn nhất trong dãy được nhập. – Cú pháp: MAX(Number1, Number2…) – Các tham số: Number1, Number2… là dãy mà bạn muốn tìm giá trị lớn nhất ở trong đó. 17. Hàm LAGRE – Tìm số lớn thứ k trong một dãy được nhập. – Cú pháp: LARGE(Array, k) – Các tham số: Array: Là một mảng hoặc một vùng dữ liệu. – k: Là thứ hạng của số bạn muốn tìm kể từ số lớn nhất trong dãy. 18. Hàm MIN – Trả về số nhỏ nhất trong dãy được nhập vào. – Cú pháp: MIN(Number1, Number2…) – Các tham số: Number1, Number2… là dãy mà bạn muốn tìm giá trị nhỏ nhất ở trong đó. 19. Hàm SMALL – Tìm số nhỏ thứ k trong một dãy được nhập vào. – Cú pháp: SMALL(Array, k) – Các tham số: Array: Là một mảng hoặc một vùng của dữ liệu. – k: Là thứ hạng của số mà bạn muốn tìm kể từ số nhỏ nhất trong dãy. D. Nhóm hàm đếm dữ liệu 20. Hàm COUNT – Hàm COUNT đếm các ô chứa dữ liệu kiểu số trong dãy. – Cú pháp: COUNT(Value1, Value2, …)
– Các tham số: Value1, Value2… là mảng hay dãy dữ liệu. 21. Hàm COUNTA – Đếm tất cả các ô chứa dữ liệu. – Cú pháp: COUNTA(Value1, Value2, …) – Các tham số: Value1, Value2… là mảng hay dãy dữ liệu. 22. Hàm COUNTIF – Hàm COUNTIF đếm các ô chứa giá trị số theo một điều kiện cho trước. – Cú pháp: COUNTIF(Range, Criteria) – Các tham số: Range: Dãy dữ liệu mà bạn muốn đếm. – Criteria: Là tiêu chuẩn cho các ô được đếm. – Ví dụ: = COUNTIF(B3:B11,”>100″): (Đếm tất cả các ô trong dãy B3:B11 có chứa số lớn hơn 100)
IV. NHÓM HÀM CHUỖI23. Hàm LEFT – Trích các ký tự bên trái của chuỗi nhập vào. – Cú pháp: LEFT(Text,Num_chars) – Các đối số: Text: Chuỗi văn bản. – Num_Chars: Số ký tự muốn trích. – Ví dụ: =LEFT(Tôi tên là,3) = “Tôi” 24. Hàm RIGHT – Trích các ký tự bên phải của chuỗi nhập vào. – Cú pháp: RIGHT(Text,Num_chars) – Các đối số: tương tự hàm LEFT. – Ví dụ: =RIGHT(Tôi tên là,2) = “là”
25. Hàm MID – Trích các ký tự từ số bắt đầu trong chuỗi được nhập vào. – Cú pháp: MID(Text,Start_num, Num_chars) – Các đối số: 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. 26. Hàm UPPER – Đổi chuỗi nhập vào thành chữ hoa. – Cú pháp: UPPER(Text) 27. Hàm LOWER – Đổi chuỗi nhập vào thành chữ thường. – Cú pháp: LOWER(Text) 28. Hàm PROPER – Đổi ký từ đầu của từ trong chuỗi thành chữ hoa. – Cú pháp: PROPER(Text) – Ví dụ: =PROPER(phan van a) = “Phan Van A” 29. Hàm TRIM – Cắt bỏ các ký tự trắng ở đầu chuỗi và cuối chuỗi. – Cú pháp: TRIM(Text)
V. NHÓM HÀM NGÀY THÁNG30. Hàm DATE – Hàm Date trả về một chuỗi trình bày một kiểu ngày đặc thù. – Cú pháp: DATE(year,month,day) – Các tham số:
– Year: miêu tả năm, có thể từ 1 đến 4 chữ số. Nếu bạn nhập 2 chữ số, theo mặc định Excel sẽ lấy năm bắt đầu là: 1900.(Ví dụ) – Month: miêu tả tháng trong năm. Nếu month lớn hơn 12 thì Excel sẽ tự động tính thêm các tháng cho số miêu tả năm. – Day: miêu tả ngày trong tháng. Nếu Day lớn hơn số ngày trong tháng chỉ định, thì Excel sẽ tự động tính thêm ngày cho số miêu tả tháng. Lưu ý: – Excel lưu trữ kiểu ngày như một chuỗi số liên tục, vì vậy có thể sử dụng các phép toán cộng (+), trừ (-) cho kiểu ngày.(Ví dụ) 31. Hàm DAY – 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. – Cú pháp: DAY(Serial_num) – Tham số: – 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. 32. Hàm MONTH – 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ú pháp: MONTH(Series_num) – Tham số: – Series_num: Là một chuỗi ngày, 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. 33. Hàm YEAR – 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.
– Cú pháp: YEAR(Serial_num) – Tham số: – Serial_num: Là một dữ liệu kiểu ngày, 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 34. Hàm TODAY – Trả về ngày hiện thời của hệ thống. – Cú pháp: TODAY() – Hàm này không có các đối số. 35. Hàm WEEKDAY – Trả về số chỉ thứ trong tuần. – Cú pháp: WEEKDAY(Serial, Return_type) – Các đối số: – Serial: một số hay giá trị kiểu ngày. – Return_type: chỉ định kiểu dữ liệu trả về.
VI. HÀM VỀ THỜI GIAN36. Hàm TIME – 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. – Cú pháp: TIME(Hour,Minute,Second) – Các tham số: Được tính tương tự ở hàm DATE. – 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. 37. Hàm HOUR – 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ú pháp: HOUR(Serial_num) – Tham số: – Serial_num: Là dữ liệu kiểu Time. Thời gian có thể được nhập như: – Một chuỗi kí tự nằm trong dấu nháy (ví dụ “5:30 PM”) – Một số thập phân (ví dụ 0,2145 mô tả 5:08 AM) – Kết quả của một công thức hay một hàm khác. 38. Hàm MINUTE – 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. – Cú pháp: MINUTE(Serial_num) – Tham số: Serial_num: Tương tự như trong công thức HOUR. 39. Hàm SECOND – 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. – Cú pháp: SECOND(Serial_num) – Tham số: Serial_num: Tương tự như trong công thức HOUR. 40. Hàm NOW – Trả về ngày giờ hiện thời của hệ thống. – Cú pháp: NOW() – Hàm này không có các đối số.
VII. NHÓM HÀM DÒ TÌM DỮ LIỆU41. Hàm VLOOKUP – Tìm ra một giá trị khác trong một hàng bằng cách so sánh nó với các giá trị trong cột đầu tiên của bảng nhập vào. – Cú pháp:
– VLOOKUP(Lookup Value, Table array, Col idx num, [range lookup]) – Các tham số: – 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ề. Chú ý: – Nếu giá trị Lookup value nhỏ hơn giá trị nhỏ nhất trong cột đầu tiên của bảng Table array, nó sẽ thông báo lỗi #N/A. – Ví dụ: =VLOOKUP(F11,$C$20:$D$22,2,0) – Tìm một giá trị bằng giá trị ở ô F11 trong cột thứ nhất, và lấy giá trị tương ứng ở cột thứ 2.
42. Hàm HLOOKUP – 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ú pháp: HLOOKUP(Lookup Value, Table array, Col idx num, [range lookup]) – Các tham số tương tự như hàm VLOOKUP. 43. Hàm INDEX – 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. – Cú pháp:
– INDEX(Array,Row_num,Col_num) – Các tham số: – Array: Là một vùng chứa các ô hoặc một mảng bất biến. – Nếu Array chỉ chứa một hàng và một cột, tham số Row_num hoặc Col_num tương ứng là tùy ý. – Nếu Array có nhiều hơn một hàng hoặc một cột thì chỉ một Row_num hoặc Col_num được sử dụng. – Row_num: Chọn lựa hàng trong Array. Nếu Row_num được bỏ qua thì Col_num là bắt buộc. – Col_num: Chọn lựa cột trong Array. Nếu Col_num được bỏ qua thì Row_num là bắt buộc.
Các Hàm Cơ Bản Trong Excel Kế Toán Thường Dùng
Các hàm cơ bản trong excel kế toán thường dùng nhất!
Hàm IF Công dụng: hàm sử dụng để kiểm tra điều kiện và trả lại một trong hai giá trị được chỉ ra trong công thức, Nếu điều kiện kiểm tra đúng thì trả lại giá trị 1, ngược lại thì trả lại giá trị 2. Cú pháp =If(Điều kiện, giá trị 1, giá trị 2)
Hàm SUMIF Công dụng: Hàm tính tổng các giá trị trong một vùng dữ liệu hoặc một dãy số theo một điều kiện nào đó. =SUMIF( Vùng giá trị tìm kiếm, giá trị tìm kiếm, vùng giá trị cần cộng) → F4 :3lần → F4:1 lần – Vùng giá trị tìm kiếm: là vùng chứa giá trị trị tìm kiếm – Giá trị tìm kiếm: Phải có tên trong vùng giá trị tìm kiếm (Giá trị tìm kiếm chỉ là một ô). – Vùng giá trị cần cộng: là vùng chứa giá trị cần cộng Lưu ý: Vùng giá trị tìm kiếm và cùng giá trị cần cộng phải tương ứng với nhau, tức là điểm đầu và điểm cuối phải tương ứng với nhau. – F4: 1 lần để có giá trị tuyệt đối ( tuyệt đối được hiểu là cố định cột và cố định dòng) Ví dụ: $E$10: được hiểu là cố định cột E và cố định dòng 10 – F4: 2 lần để có giá trị tương đối cột và tuyệt đối dòng (được hiểu là cố định dòng và thay đổi cột) Ví dụ: E$10 cố định cột E thay đổi dòng 10 – F4$: 3 lần để có giá trị tương đối dòng và cố định cột (cố định cột thay đổ dòng) Ví dụ: $E10 cố định cột E và thay đổi dòng 10
Hàm SUBTOTAL Công dụng dùng để cộng tổng có điều kiện, khác với hàm sum là hàm cộng tổng tránh trùng, tránh lặp. Cú pháp = Subtotal(9,vùng cần cộng) Mỗi đối số có một ý nghĩa khác nhau, đối số 9 thay cho hàm sum.
Hàm MAX – Trả về số lớn nhất trong dãy được nhập. – Cú pháp: MAX(Number1, Number2…) – Các tham số: Number1, Number2… là dãy mà bạn muốn tìm giá trị lớn nhất ở trong đó.
Hàm MIN – Trả về số nhỏ nhất trong dãy được nhập vào. – Cú pháp: MIN(Number1, Number2…) – Các tham số: Number1, Number2… là dãy mà bạn muốn tìm giá trị nhỏ nhất ở trong đó.
Hàm VLOOKUP Công dụng: hàm tìm kiếm dữ liệu theo điều kiện và trả lại kết quả là giá trị tương ứng được tìm thấy trong bảng phụ. Bảng phụ được tạo theo dạng cột dọc Cú pháp = VLOOKUP(giá trị tìm kiếm, vùng dữ liệu tìm kiếm, thứ tự cột trả về giá trị tìm kiếm, kiểu tìm kiếm) → F4 :3lần → F4:1 lần Trong đó: – Giá trị tìm kiếm: giá trị để tìm kiếm chỉ là một ô và phải có tên trong vùng dữ liệu tìm kiếm. – Vùng dữ liệu tìm kiếm: phải chứa tên của giá trị tìm kiếm và phải chứa giá trị cần tìm, điểm bắt đầu của vùng được tính từ dãy ô có chứa giá trị để tìm kiếm. – Cột trả về giá trị tìm kiếm: đếm thứ tự cột từ bên trái qua bên phải. – Kiểu tìm kiếm nhận một trong hai giá trị 0,1. Nếu tìm kiếm về 0 thì điều kiện dựa vào tìm kiếm được lấy ở bảng chính phải giống giá trị trong cột đầu tiên. Nếu là 1 hoặc để trống thì giá trị tìm kiếm trên bảng phụ phải được sắp xếp theo thứ tự tăng dần. – Bảng tìm kiếm phải sử dụng địa chỉ tuyệt đối. Cột đầu tiên trong bảng tìm kiếm phải chứa giá trị để tìm kiếm. – F4: 1 lần để có giá trị tuyệt đối (tuyệt đối được hiểu là cố định cột và cố định dòng). Ví dụ: $D$10: được hiểu là cố định cột D và cố định dòng 10 – F4: 2 lần để có giá trị tương đối cột và tuyệt đối dòng (được hiểu là cố định dòng và thay đổi cột). Ví dụ: D$10 cố định cột D thay đổi dòng 10 – F4$: 3 lần để có giá trị tương đối dòng và cố định cột (cố định cột thay đổ dòng). Ví dụ: $D10 cố định cột D và thay đổi dòng 10
Hàm LEFT: – Trích các ký tự bên trái của chuỗi nhập vào. – Cú pháp: LEFT(Text,Num_chars) – Các đối số: Text: Chuỗi văn bản. – Num_Chars: Số ký tự muốn trích. – Ví dụ: =LEFT(Tôi tên là,3) = “Tôi”
Hàm RIGHT – Trích các ký tự bên phải của chuỗi nhập vào. – Cú pháp: RIGHT(Text,Num_chars) – Các đối số: tương tự hàm LEFT. – Ví dụ: =RIGHT(Tôi tên là,2) = “là”
Tự Học Kế Toán Excel Cơ Bản
Trong bài viết này Kế toán Thiên Ưng sẽ hướng dẫn các bạn tự học kế toán Excel với những kiến thức từ cơ bản nhất về Excel đến những kiến thức nâng cao về chuyên môn kế toán Excel.
Mục đích: Dành cho các bạn đã đăng ký khóa học kế toán trên Excel, khóa học kế toán tổng hợp tại Công ty kế toán Thiên Ưng nhưng phần tin học Excel chưa thực sự thành thạo.
Trước khi đi học các bạn cần học và thực hành trước những kiến thức cơ bản này để quá trình học chuyên sâu về kế toán Excel trên lớp như: học cách hạch toán, học lên sổ sách, lập BCTC… được thuận lợi hơn.
1. Giới thiệu qua về giao diện của Microsoffe Excel 2007
Data chứa các lệnh làm việc với vùng CSDL sắp xếp (Sort), lọc (Filter), tổng hợp dữ liệu (Subtotal).
Nút office: chứa các lệnh làm việc với tệp văn bản New, save, Print, Close
Thanh công thức (Formulas Bar) dùng để nhập, sửa chữa dữ liệu hoặc công thức.
Các thanh công cụ (tools); chứa các lệnh thực hiện nhanh. Hai thanh công cụ thông dụng nhất là Standard vaf formatting.
Thanh cuộn ngang và dọc ( horizontal Scroll Bar và Vertical Scroll Bar) cho phép cuộn màn hình theo chiều ngang và dọc.
– CTRL + A: chọn toàn bộ– CTRL + C: copy– CTRL+ V: dán ký tự vừa copy– CTRL+ X: cắt đoạn lý tự– CTRL+F: tìm kiếm dữ liệu– CTRL+P: in– CTRL+D: copy nội dung những dòng giống nhau– CTRL+H: Dùng để thay thế dữ liệu– CTRL+B: dùng để bôi đậm dữ liệu– CTRL +U: dùng để gạch chân văn bản– CTRL+I: phím tắt dùng để chọn chữ nghiêng – CTRL+Z: quay lại thao tác vừa thực hiện– CTRL+0: Phím tắt dùng để ẩn cột hoặc nhóm cột được chọn– CTRL+9: Phím tắt dùng để ẩn dòng hoặc nhóm dòng được chọn– ALT+ Enter: ngắt dòng– Enter: chuyển xuống ô dưới– ESC: hủy bỏ dữ liệu vừa thao tác trong ô– F4 hoặc CTRL + Y: lặp lại thao tác– ALT+ Enter: thêm dòng trong ô– Backspace: Xóa dòng ký tự từ bên phải– Home về đầu dòng– Tab: chuyển sang ô bên phải– F2: Hiệu chỉnh dữ liệu trong một ô– F3: Dán một tên đã đặt trong công thức– F12: Tùy chọn lưu– CTRL+D: Chép dữ liệu từ ô phía trên xuống ô phía dưới– Shift + Enter: chuyển lên ô phía trên– Shift + Tab: chuyển sang ô bên trái
3.Xử lý các ký hiệu lạ xuất hiện trên bảng tính Excel:Khi nhập dữ liệu dạng công thức hoặc sao chép dữ liệu được tạo ra từ công thức sang các vùng khác đôi khí thấy xuất hiện các ký hiệu lạ như:
Là do cột không đủ lớn để chứa dữ liệu, nên mơ rộng cột lớn đủ chứa dữ liệu. Một giá trong công thức không hợp lệ do dữ liệu bị xóa hoặc bị dán đè dữ liệu khác lên. Nên kiểm tra lại các địa chỉ trong công thức và sửa chữa cho phù hợp. Tên hàm hoặc biểu thức trong công thức bị sai và excel không hiểu công thức đó. Cần thay đổi lại tên hàm hoặc các phép tính cho đúng. Một trong các giá trị không nằm trong vùng dữ liệu quy định của hàm. Nên kiểm tra lại các địa chỉ trong công thức và sửa chữa cho phù hợp. Một trong các giá trị của công thức không phải là kiểu dữ liệu số, nên kiểm tra lại các địa chỉ trong công thức và sửa chữa cho phù hợp.
Công dụng dùng để tính tổng trong một vùng dữ liệu hoặc một dãy số nào đó. Cú pháp = SUM (vùng cần cộng).Bài tập thực hành: Cộng tổng bộ phận quản lý và bộ phận bán hàng tại Sheet bảng lương.
Công dụng dùng để cộng tổng có điều kiện, khác với hàm sum là hàm cộng tổng tránh trùng, tránh lặp. Cú pháp = Subtotal(9,vùng cần cộng) Mỗi đối số có một ý nghĩa khác nhau, đối số 9 thay cho hàm sum.Bài tập thực hành: Cộng tổng bộ phận quản lý và bộ phận bán hàng tại Sheet bảng lương.
Công dụng tìm giá trị lớn nhất trong một vùng dữ liệu hoặc một dãy số nào đó. Cú pháp= max(vùng dữ liệu cần tìm giá trị lớn nhất)Bài tập thực hành: Sử dụng hàm max để lấy số dư cuối kỳ cho bảng tổng hợp 131 và 331
Công dụng: hàm tìm kiếm dữ liệu theo điều kiện và trả lại kết quả là giá trị tương ứng được tìm thấy trong bảng phụ. Bảng phụ được tạo theo dạng cột dọc
Cú pháp = VLOOKUP(giá trị tìm kiếm, vùng dữ liệu tìm kiếm, thứ tự cột trả về giá trị tìm kiếm, kiểu tìm kiếm)
→ F4 :3lần → F4:1 lần
Trong đó: – Giá trị tìm kiếm: giá trị để tìm kiếm chỉ là một ô và phải có tên trong vùng dữ liệu tìm kiếm. – Vùng dữ liệu tìm kiếm: phải chứa tên của giá trị tìm kiếm và phải chứa giá trị cần tìm, điểm bắt đầu của vùng được tính từ dãy ô có chứa giá trị để tìm kiếm. – Cột trả về giá trị tìm kiếm: đếm thứ tự cột từ bên trái qua bên phải. – Kiểu tìm kiếm nhận một trong hai giá trị 0,1. Nếu tìm kiếm về 0 thì điều kiện dựa vào tìm kiếm được lấy ở bảng chính phải giống giá trị trong cột đầu tiên. Nếu là 1 hoặc để trống thì giá trị tìm kiếm trên bảng phụ phải được sắp xếp theo thứ tự tăng dần. – Bảng tìm kiếm phải sử dụng địa chỉ tuyệt đối. Cột đầu tiên trong bảng tìm kiếm phải chứa giá trị để tìm kiếm. – F4: 1 lần để có giá trị tuyệt đối (tuyệt đối được hiểu là cố định cột và cố định dòng). Ví dụ: $D$10: được hiểu là cố định cột D và cố định dòng 10 – F4: 2 lần để có giá trị tương đối cột và tuyệt đối dòng (được hiểu là cố định dòng và thay đổi cột). Ví dụ: D$10 thay đổi cố định dòng 10
– F4$: 3 lần để có giá trị tương đối dòng và cố định cột (cố định cột thay đổ dòng).Ví dụ: $D10 cố định cột D và thay đổi dòng 10 Bài tập thực hành: sử dụng hàm vlookup để tìm kiếm tên hàng và đơn vị tại Sheet bảng kê Nhập Xuất Kho.
Công dụng: Hàm tính tổng các giá trị trong một vùng dữ liệu hoặc một dãy số theo một điều kiện nào đó.
=SUMIF( Vùng giá trị tìm kiếm, giá trị tìm kiếm, vùng giá trị cần cộng)
→ F4 :3lần → F4:1 lần
– Vùng giá trị tìm kiếm: là vùng chứa giá trị trị tìm kiếm – Giá trị tìm kiếm: Phải có tên trong vùng giá trị tìm kiếm (Giá trị tìm kiếm chỉ là một ô). – Vùng giá trị cần cộng: là vùng chứa giá trị cần cộng
: Vùng giá trị tìm kiếm và cùng giá trị cần cộng phải tương ứng với nhau, tức là điểm đầu và điểm cuối phải tương ứng với nhau. – F4: 1 lần để có giá trị tuyệt đối ( tuyệt đối được hiểu là cố định cột và cố định dòng) Ví dụ:$E$10: được hiểu là cố định cột E và cố định dòng 10 – F4: 2 lần để có giá trị tương đối cột và tuyệt đối dòng (được hiểu là cố định dòng và thay đổi cột) Ví dụ: E$10 thay đổi cột E cố định dòng 10
– F4$: 3 lần để có giá trị tương đối dòng và cố định cột (cố định cột thay đổ dòng) Ví dụ:$E10 cố định cột E và thay đổi dòng 10Bài tập thực hành: Sử dụng hàm sumif để lấy số lượng nhập, thành tiền nhập và số lượng xuất từ bảng Kê nhập xuất kho sang bảng tổng hợp nhập xuất tồn.
Công dụng: hàm sử dụng để kiểm tra điều kiện và trả lại một trong hai giá trị được chỉ ra trong công thức, Nếu điều kiện kiểm tra đúng thì trả lại giá trị 1, ngược lại thì trả lại giá trị 2.
7.1. Chỉnh sửa dữ liệu đã nhập – Chọn vùng hoặc ô muốn thay đổi kiểu dữ liệu – Chọn Home/chọn nút mở rộng Alignment: chọn vùng hoặc ô muốn thay đổi dữ liệu – Hoặc chọn home/chọn nút format painter
Khi cần xóa vùng dữ liệu cần thưc hiện các thao tác sau:
– Chọn vùng dữ liệu cần xoá – Vào home/chọn nút Clear mở rộng– Clear All: xóa tất cả dữ liệu– Clear formats: xóa định dạng(phông, cỡ, mầu chữ, căn lề)– Clear Contents: Xóa nội dung giữ nguyên định dạng– Clear Comments: Xóa chú thích
Muốn lọc dữ liệu phải bôi đen từ dòng muốn lọc tới hết cùng lọc, sau đó vào Data/chọn filter. Xuất hiện các mũi tên nhỏ màu trắng trên tiêu đề các cột trong vùng dữ liệu. Muốn lọc dữ liệu cột nào nhấn chuột tại mũi tên của cột đó.
– Sort A to Z: sắp xếp dữ liệu tăng dần– Sort Z to A: sắp xếp dữ liệu giảm dần– Sort by color: sắp xếp dữ liệu theo màu sắc– Text Filter: cho phép chọn theo điều kiện cần lọc– Equals: so sánh bằng– Does not Equals: So sánh không bằng– Begin With: bắt đầu với– Does not begin with: không bắt đầu với– End with: kết thúc với– Content: bao gồm– Muốn hủy lọc: vào data/chọn Filter.– Muốn nhả lọc để lọc điều kiện khác vào Select All hoặc Clear Filter From.
a. Chèn hàng, cột: Chọn home/chọn Inser – Chọn Insert Cells: để thêm ô – Chọn Insert Sheet Rows: thêm hàng – Chọn Insert Sheet Cloumns: thêm cột – Chọn Insert Sheet: thêm bảng tính
b. Xóa ô, hàng, cột– Chọn vùng dữ liệu cần xóa – Chọn home/Chọn Delete – Delete Cell: Xóa ô – Delete sheet Rows: xóa dòng – Delete sheet cloumns: xóa cột – Delete sheet: xóa toàn bộ bảng tính
c. Thay đổi độ rộng của cột: đưa con chuột tới đường viền phải của cột hoặc đường viền dưới của hàng cần thay đổi.d. Ẩn hiện hàng, cột, bảng tínhẨn– Chọn Home/format/chọn hide hoặc unhide – Hide cloumns: ẩn cột – Hide Rows: hàng – Hide Sheet: ẩn bảng tínhHiện– Chọn home/chọn format/Chọn hide hoặc unhide – Unhide cloumns: hiện cột – Unhide Rows:hiện hàng – Unhide Sheet: hiệnbảng tính
e. Định dạng ô:Chọn thay đổi phông chữ – Chọn vùng dữ liệu cần thay đổi phông chữ – Chọn home/chọn phông chữ, cỡ chữ, kiểu chữ. Màu chữ trực tiếp trên thanh menu nút lệnh. – Hoặc chọn home chọn font mở hộp thoại Format Cells. – Hộp thoại format Cells, thẻ fonts gồm: – Font: chọn các phông chữ – Font style: chọn kiểu chữ(nghiêng :Italic. Đậm Bold, gạch chân:Under line) – Size: chọn cỡ chữ – Color:màu chữ – OK.Tạo đường viền Chọn home/chọn thẻ Border Hoặc hộp thoại FormatCells, thẻ Border gồm: – Line Style: chọn kiểu đường viền ( nét đơn, đôi, đứt) – Color: chọn màu đường viền – None: xóa các đường viền trong vùng dữ liệu – Outline: tạo đường viền ngoài vùng dữ liệu – Inside: tạo đường viền trong vùng dữ liệu – Border: thêm, bỏ hoặc thay đổi các đường viền trên, dưới, trái, phải, chéo trái, chéo phải bằng các nút tương ứng.
g. Hợp ô tách ô Chọn home/Chọn merge – Merge & Center: hợp các ô được chọ thành một ô duy nhất và định dạng dữ liệu vào giữa ô. – Merge Across: Hợp các ô theo dòng – Unmerge Cells: tách ô đã đượ hợp
h. Đổi hướng chữ, căn lề dữ liệu trong ô Chọn Home/chọn các nút căn lề theo mong muốn trên thẻ Alignment/chọn thẻ Alignment Hộp thoại format Cells, thẻ Alignment gồm: Horizontal: căn lề theo chiều ngang General: giữ nguyên như khi nhập dữ liệu Left: căn lề theo mép trái Center: căn lề giữa Right: căn lề mép phải Fill: căn toàn ô bằng các ký tự trong ô đó Justifly: căn lề đều hai bên mép lề Center across selection: căn lề giữa qua một dãy ô – Vertical: căn lề theo chiều dọc (top;căn lề phía trên, Center căn giữa, Bottom: căn lề xuống phía dưới) – Wrap text; khi tích dấu check dữ liệu nhập vào nếu dài hơn độ rộng ô sẽ tự độn dàn thành nhiều dòng. – Merge Cells:trộn nhiều ô thành một ô duy nhất. – Orientation: chọn hướng xoay dữ liệu trong ô bằng cách dùng chuột kéo nút đỏ tới các góc mong muốn.
I. Chèn các ký tự đặc biệt – Chọn insert/chọn Symbol. Xuất hiện hộp thoại Symbol. – Hộp thoaị Symbol gồm các mục chức năng – Chọn phông chữ trong mục Font, trong mỗi bảng ký tự đặc biệt có nhiều ký tự khác nhau. Chọn ký tự cần dùng – Nếu cần chèn thêm nhiều ký tự khác nhau lần lượt thực hiện các thao tác: chọn ký tự cần sử dụng. Chọn nút insert cho đến khi chèn đủ các ký tự cần. Chọn nút Close đóng hộp thoại Symbol.
a. Chèn thêm bảng tính Nhấn chuột phải tại thanh Sheet Bar trên thẻ ghi tên bảng tính Chọn Insert/chọn Worksheet/chọn Okb. Xóa bảng tính Nhấn chuột phải tại thẻ ghi tên bảng tính cần xóa Chọn lệnh Delete/Chọn OK, bảng tính sẽ bị xóa khỏi tệp dữ liệu
c. Đổi Tên Bảng Tính Nhấn chuột phải vào thẻ ghi tên bảng tính, chọn lênh rename/ghi tên mới cho bảng tính.d. Di chuyển sao chép bảng tính– Nhấn chuột phải tại thẻ ghi tên bảng tính cần sao chép hoạc di chuyển/chọn lệnh Move or Copy – To Book: chọn tệp dữ liệu đích sẽ chứa kết quả sau khi di chuyển hoặc sao chép bảng tính. – Before sheet: chọn vị trí mới cho bảng tính trong tệp hiện thời đang làm việc Create a copy: nếu chọn là sao chép bảng tính, ngược lại là di chuyển bảng tính – Chọn Ok
e. Đổi màu tên bảng tính– Nhấn chuột phải tại thẻ ghi tên bảng tính/chọn Tab color/chọn màu sắc. Tên bảng tính sẽ được đổi màu như mong muốn.
g. Ẩn hiện bảng tính Ẩn bảng tính: Nhấn chuột phải tại thẻ ghi tên bảng tính cần ẩn/chọn Hide Hiện bảng tính: Nhấn chuột phải tại thẻ ghi tên bảng tính bất kỳ/chon Unhide/chọn tên bảng tính muốn hiện
– Chọn Page Layout/chọn Print Titles- Nhấn CTRL+F2 mở hộp thoại Print Preview – Print titles: nhập đại chỉ vùng cần in. Sử dụng khi cần in một vùng trong dữ liệu bảng tính – Print titles: Đối với các bảng dữ liệu lớn khi in trên nhiều trang, từ trang thứ hai trở đi dữ liệu được in sẽ không có tiêu đề bảng làm việc quản lý dữ liệu gặp khó khăn. Muôn tiêu đề lặp trên các trang in – Rows to repeat at top: chọn hàng cần lặp trên các trang – Chọn Office Button/ chọn Print/chọn Print Preview – Print: thực hiện lệnh in – Page Setup: thiết lập trang in – Zoom: chức năng phong to bảng dữ liệu ở kích cỡ bình thường hoặc thu nhỏ bange dữ liệu vừa độ rộng cửa sổ. – Next page: chuyển xem trang tiếp theo – Show Margins: điều chỉnh độ rộng lề giấy – Page Break Preview: cho phép đặt dấu ngắt trang, Định trỏ tại vị trí ngắt trang, nhấn chuột tại nút Page Dreak Preview. Dấu ngắt trang được thiết lập – Close Print Preview: đóng chế độ xem và trở về màn hình làm việc thông thường – Help:trợ giúp sử dụng chức năng tương ứng để thay đổi định dạng trang in theo mong muốn. – Chọn nút Close Print Preview hoặc tổ hợp phím CTRL+F2 để đóng chế độ xem trước khi in – Columns to repeat at left: chọn cột cần lặp trên các trang – Print Gridlines: cho phép in các đường lưới trong bảng
– Chọn Page layout, chọn nút Breaks
– Chọn insert page Break nếu muốn đặt dấu ngắt trang – CHọn Remove page Break nếu muốn hủy dấu ngắt trang – Chọn Reset All Page Breaks nếu muốn hủy tất cả các dấu ngắt trang đã đặt
Chọn nút OFFICE BUTTON/Chọn Print để mở hộp thoại PrintKhung Copies: số bản cần in.Sử dụng khi muôn in dữ liệu thành nhiều bản giống nhau.
Khung Printer: name chọn máy in cần sử dụng in Lưu ý: để in giấy A4 cần định dạng giấy như sau Khung Print range: chọn miền dữ liệu cần in All: in tất cả các trang Page from chúng tôi …. Chọn in số trang theo mong muốn. Ghi trang bắt đầu I tại mục from và trang kết thúc toKhung Print what: điều khiển in
Hàm Đếm Kí Tự Và Hàm Đếm Dữ Liệu Trong Excel Từ Cơ Bản Đến Nâng Cao
Hàm LEN sử dụng để đếm chiều dài chuỗi kí tự (gồm: chữ cái, chữ số, kí tự, khoảng trắng).
ĐẾM SỐ KÍ TỰ TRONG MỘT Ô TRỪ KHOẢNG TRẮNG
Hàm LEN dùng để đếm số kí tự nhưng lại bao gồm cả khoảng trắng, muốn đếm số kí tự nhưng trừ khoảng trắng thì bạn cần sử dụng hàm LEN kết hợp với hàm SUBSTITUTE.
Trước tiên mình sẽ giới thiệu qua về hàm SUBSTITUTE:
Mục đích sử dụng: Thay thế kí tự này bằng kí tự khác trong chuỗi kí tự.
Cú pháp: =SUBSTITUTE(text,old_text,new text,[instrance_num])
trong đó:
– text: chuỗi kí tự. bắt buộc
– old_text: kí tự bạn muốn được thay thế (nằm trong text), nằm trong dấu ” “. bắt buộc
– new_text: kí tự bạn muốn thay thế cho old_text, nằm trong dấu ” “. bắt buộc
– instance_num: xác định số lần xuất hiện của old_text mà bạn muốn thay thể bởi new_text. không bắt buộc (nếu bạn xác định instance_num, thì số lần xuất hiện đó của old_text được thay thế; Nếu không xác định, mọi lần xuất hiện của old_text trong văn bản được đổi thành new_text)
Ở đây mình muốn loại bỏ mọi dấu cách, vì vậy mình nhập =SUBSTITUTE(A2,” “,””)
Kết quả dấu cách sẽ bị xóa đi hết:
Vì vậy, để đếm số kí tự trong một ô mà trừ khoảng trắng, bạn sẽ kết hợp hàm LEN và hàm SUBSTITUTE thành: =LEN(SUBSTITUTE(A2,” “,””))
Ta sẽ có kết quả mong muốn:
ĐẾM SỐ LẦN XUẤT HIỆN CỦA SỐ TRONG MIỀN DỮ LIỆU
Cú pháp: =COUNT(value1,value2…)
ĐẾM SỐ LẦN XUẤT HIỆN CỦA NỘI DUNG BẤT KỲ TRONG MIỀN DỮ LIỆU
Nội dung bất kỳ bao gồm: chữ cái, chữ số, kí tự, khoảng trắng…
Cú pháp: =COUNTA(value1,value2…)
ĐẾM DỮ LIỆU THEO ĐIỀU KIỆN
Hàm COUNTIF dùng để đếm nội dung nào đó theo điều kiện nhất định. Bạn có thể đếm số lần xuất hiện của từ bắt đầu bằng nội dung gì; hoặc số lần xuất hiện của từ kết thúc bằng nội dung gì…
Cú pháp: =COUNTIF(range,criteria)
trong đó:
– range: vùng dữ liệu
– criteria: điều kiện cần thỏa mãn (nếu là kí tự thì nằm trong dấu ” “)
Bảng công thức ĐẾM DỮ LIỆU THEO NHIỀU ĐIỀU KIỆN LIÊN KẾT
Tương tự với hàm COUNTIF, bạn có thể sử dụng hàm COUNTIFS để đếm dữ liệu thỏa mãn 2 hay nhiều điều kiện bất kỳ.
Cú pháp: =COUNTIFS(criteria_range1,criteria1,[criteria_range2,criteria2],…)
trong đó:
criteria_range1: vùng dữ liệu thứ nhất. bắt buộc
criteria1: điều kiện thứ nhất. bắt buộc
criteria_range2, criteria2,… : vùng dữ liệu thứ hai và điều kiện thứ hai… không bắt buộc (cho phép nhập tối đa 127 vùng dữ liệu/ điều kiện)
Ví dụ, trong bảng dữ liệu nêu trên:
Yêu cầu: Tìm số lượng giáo viên dạy Toán có lương 4.000.000
Công thức: =COUNTIFS(C3:C7,C4,D3:D7,D4)
Mô tả: Đếm số lượng các ô thỏa mãn 2 điều kiện: điều kiện thứ nhất – bằng ô C4 trong phạm vi C3:C7 và điều kiện thứ hai – bằng ô D4 trong phạm vi D3:D7
Cập nhật thông tin chi tiết về 43 Hàm Microsoft Excel Cơ Bản Và Nâng Cao Cho Kế Toán trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!