Excel

15 hàm

1. Hàm SUM: Hàm tính tổng các đối số

– Công dụng của hàm Sum: Hàm SUM trong Excel thường được sử dụng để tính tổng các đối số trên bảng tính. Sử dụng hàm Sum trong Excel giúp bạn tiết kiệm khá nhiều thời gian và công sức so với cách tính thủ công thông thường
– Cú pháp: =SUM (number 1, number 2, …)
– Chức Năng: Tính tổng number 1, number 2, …
  • Trong đó: number 1, number 2, … là các đối số mà bạn muốn tính tổng
– Chú ý:
  • Ô tính có giá trị logic TRUE được xem là 1, FALSE được xem là 0.
  • Nếu đối số là mảng hay tham chiếu thì chỉ các giá trị số trong mảng hay tham chiếu đó mới được tính. Các giá trị khác trong mảng hoặc tham chiếu bị bỏ qua.
VD1:
  • =SUM (2, 3, 4) có giá trị bằng 9.
  • =SUM (2, 3, TRUE) có giá trị bằng 6.
  • =SUM (“2”,”3”,1) có giá trị bằng 6.
10 hàm cơ bản trong Excel
Công thức sử dụng: C2=SUM (A2:B2) Ta có kết quả như hình

2. Hàm AVERAGE: Hàm tính trung bình cộng

– Công dụng hàm Average: Hàm AVERAGE là hàm cơ bản trong Excel hỗ trợ tính trung bình cộng của một dãy số trong bảng tính Excel, hỗ trợ người dùng tính toán nhanh hơn nếu số lượng phần tử trong dãy lớn và dài.
– Cú Pháp: AVERAGE(Number1, [Number2], [Number3],…)
  • Trong Đó:
    • Number1: bắt buộc
    • Number2, Number3,…: tùy chọn.
Hàm AVERAGE có tối đa 256 đối số, có thể là số, tên, phạm vi hoặc tham chiếu ô có chứa số. Một đối số tham chiếu ô hoặc phạm vi có chứa giá trị logic, văn bản hay ô rỗng thì những giá trị đó sẽ bị bỏ qua, trừ giá trị 0 hoặc được nhập trực tiếp vào danh sách đối số.
Ví dụ: Cho bảng điểm của một số học sinh, tính điểm trung bình cộng các môn học của từng học sinh. Hãy tính trung bình điểm các môn của học sinh
Hàm average cơ bản trong Excel
Hàm average hàm cơ bản trong Excel
Hàm average cơ bản trong Excel
Công thức ô G6:=AVERAGE(D6,E6,F2)

3. Hàm MIN/MAX: Hàm cho giá trị lớn nhất, nhỏ nhất

– Công dụng hàm Min/Max: Cách sử dụng hàm MAX và hàm MIN để tìm giá trị lớn nhất, nhỏ nhất của các đối số hay vùng dữ liệu.
– Cú Pháp:
  • MAX (number 1, number 2, …)
  • MIN (number 1, number 2, …)
    • Trong Đó:
      • Number 1, number 2 là các đối số hoặc vùng dữ liệu.
 – Chức Năng: Trả về giá trị lớn nhất, nhỏ nhất của các đối số hoặc vùng dữ liệu có chứa số
Ví dụ:
– Đưa về giá trị lớn nhất trong dãy các số 10, -20, 3, 4, 11
  • Công thức: MAX (10, -20, 3, 4, 11) = 11.
– Đưa về giá trị nhỏ nhất trong các số: 20, 100, 30, 45.
  • Công thức: MIN (20, 100, 30, 45) = 20.

4. Hàm TRIM: Hàm loại bỏ khoảng trống trong văn bản

– Công dụng hàm Trim: Hàm TRIM là một trong các hàm cơ bản Excel không thể bỏ qua giúp bạn xử lý các khoảng trắng nằm rải rác trong bảng tính khi bạn nhập hay dán văn bản từ một cơ sở dữ liệu, một website, một chương trình văn bản khác,… Giả sử, bạn muốn loại bỏ các khoảng trắng trong cột A2, bạn chỉ cần sử dụng hàm TRIM ở ô B2 với công thức: =TRIM(A2). Bạn copy công thức xuống cuối danh sách để có kết quả loại bỏ các khoảng trắng tương tự ở B2.
– Cú Pháp: TRIM(Text)
  • Trong Đó: Text là đoan văn bản chứa khoảng trắng cần loại bỏ
Ví Dụ: Bạn có một bảng tính có cột Họ và Tên chứa các ký tự trắng, bây giờ cần loại bỏ các khoảng trắng này
Hàm xóa khoảng trắng dư thừa bỏ khoảng trắng trong Excel
Hàm xóa khoảng trắng dư thừa bỏ khoảng trắng trong Excel, hàm cơ bản trong excel
Để loại bỏ các khoảng trắng, tại ô D6 nhập công thức =TRIM(A6) ta được kết quả như hình.

5. Hàm COUNT/COUNTA: Hàm đếm dữ liệu/đếm số ô không trống

– Công dụng hàm Count/Counta: Thay vì đếm thủ công các ô chứa số trong một bảng dữ liệu khổng lồ, bạn có thể sử dụng hàm COUNT. Ví dụ, bạn gõ =COUNT(A1:A20) sẽ cho kết quả có bao nhiêu ô chứa số từ ô A1 đến ô A20 trong bảng tính. Tương tự, hàm COUNTA cũng dùng hỗ trợ đếm ô. Tuy nhiên, thay vì chỉ đếm các ô chứa số, COUNTA sẽ hỗ trợ bạn đếm bất kỳ ô nào có chứa nội dung (cả số và cả chữ cái). Cú pháp hàm COUNTA bạn thực hiện tương tự như COUNT.
  • Hàm Count để đếm dữ liệu, các đối tượng trong chuỗi trong một bảng tính nhất định
  • Hàm Counta trong Excel là hàm cho phép bạn đếm những ô có dữ liệu trong một phạm vi nào đấy.
– Cú pháp hàm Count/Counta
  • Counta (Value1, [value2], [value3],…)
    • Trong Đó:
      • Value1 là đối số bắt buộc, là vùng dữ liệu cần đếm.
      • Value2 và Value3 là các tùy chọn vì được đặt trong dấu [], không bắt buộc có.
  • COUNT(vùng chọn để đếm)
    • Ý Nghĩa: Dùng để đếm số ô chứa dữ liệu Số trong vùng được chọn.
Ví dụ:
– Đếm số Học Sinh đỗ trong đợt thi này, học sinh trượt được ký hiệu là “tr”.
Cú pháp hàm Count
Để đếm được số học sinh đỗ ta sử dụng công thứcô C15: =COUNT(C6:C14)
Ví dụ: Đếm số ô không rỗng trong bảng dữ liệu sau:
Hàm counta trong Excel
Hàm counta trong Excel, hàm cơ bản trong excel
Ta sử dụng công thức tại ô D10 như sau: =COUNTA(A4:D9)

6. Hàm LEN: Hàm đo độ dài của chuỗi ký tự

– Công dụng hàm Len: Để đo độ dài của chuỗi ký tự bất kỳ bạn có thể sử dụng hàm LEN trong Excel, hàm này trả về độ dài chính xác của chuỗi ký tự từ đó bạn có thể thực hiện những yêu cầu bạn muốn.
– Cú Pháp:LEN (text)
– Trong Đó:
  • Text: là chuỗi ký tự
– Chức Năng: Trả về giá trị là độ dài của chuỗi ký tự, kể cả ký tự khoảng trống.
Ví dụ :
Đo độ dài của chuỗi “University of Da Nang”.
Công thức tính sẽ là: =LEN (“University of Da Nang”) = 21.
Xem thêm: Khóa học Excel 2010 trong 10 giờ giúp bạn ứng dụng và thành thạo các hàm cơ bản trong Excel

7. Hàm CONCATENATE: Hàm nối các chuỗi ký tự

– Công dụng hàm CONCATENATE: Hàm CONCATENATE giúp kết hợp nội dung của các ô với nhau. Ví dụ, bạn muốn kết hợp các ô tên, tên đệm, họ thành một ô thể hiện tên gọi hoàn chỉnh, bạn sử dụng hàm CONCATENATE và di chuyển đếm các ô cần kết hợp là có kết quả như mong muốn. Để tạo khoảng cách hoặc dấu chấm, dấu phẩy,… giữa các ô kết hợp, bạn chỉ việc đưa dấu chấm, dấu phẩy, để trống,… vào dấu ngoặc kép.
– Cú Pháp: CONCATENATE (text 1, text 2, …)
– Trong Đó:
  • Text 1: là chuỗi thứ 1. Bắt buộc.
  • Text 2 …: tùy chọn. Có thể lên đến tối đa 255 chuỗi. Các chuỗi phải được phân tách nhau bởi dấu phẩy.
– Chức Năng: Dùng để ghép nối các chuỗi văn bản thành một chuỗi duy nhất. Các chuỗi được nối có thể là số, văn bản, ô tham chiếu.
Hàm CONCATENATE trong excel
Hàm CONCATENATE trong excel, hàm cơ bản trong excel
= CONCATENATE (B1,” “,C1): Mark Twain
= CONCATENATE (A2,” “,A1,” is ”,A3,” USD”): Kawasaki Z250 price is 3365 USD.
= CONCATENATE (B2,” & “,C2): Phineas & Ferb.

8. Hàm DAYS: Hàm trả về số ngày giữa 2 ngày trong Excel

– Công dụng hàm Days: Hàm trả về số ngày giữa 2 ngày trong Excel
– Cú pháp: DAYS(end_date, start_date).
  • Trong đó:
    • end_date: Là ngày kết thúc muốn xác định số ngày, là tham số bắt buộc.
    • start_date: Là ngày đầu tiên muốn xác định số ngày, là tham số bắt buộc.
– Chú ý:
  • Nếu end_date và start_date đều có dạng số -> hàm dùng EndDate – StartDate để tính số ngày giữa 2 ngày này.
  • Nếu end_date và start_date ở dạng văn bản hàm tự động chuyển sang định dạng ngày bằng cách sử dụng hàm DATEVALUE(date_text) rồi tính toán.
  • Nếu đối số vượt ngoài phạm vi của ngày tháng năm -> hàm trả về giá trị lỗi #NUM!
  • Nếu đối số dạng văn bản mà không thể chuyển sang định dạng ngày hợp lệ -> hàm trả về giá trị lỗi #VALUE!
Ví dụ: Tính số năm công tác của các nhân viên dựa vào hàm Days().
Tính số năm công tác của các nhân viên dựa vào hàm Days().

9. Hàm NETWORKDAYS: Hàm tính số lượng ngày làm việc

– Công dụng Hàm NETWORKDAYS: Thay vì dùng cách thủ công như đếm tay để tính số ngày làm việc. Excel hỗ trợ cho bạn một hàm NETWORKDAYS tính số lượng ngày làm việc toàn thời gian giữa hai ngày một cách chính xác, nhanh gọn.
– Cú Pháp: =NETWORKDAYS(START_DATE,END_DATE,[HOLIDAYS])
– Chức Năng: Trả về số lượng ngày làm việc trừ những ngày lễ và ngày cuối tuần bắt đầu từ ngày start_date và kết thúc bằng ngày end_date.
Ví dụ: Ta có một bảng chấm công nhân viên, bao gồm cả số ngày nghỉ như sau:
Hàm NETWORKDAYS
– Sử dụng hàm networkdays tính số ngày làm việc với ngày bắt đầu là 23-01-2013, ngày kết thúc là 18-02-2013. Công thức là =NETWORKDAYS(B3,C3).

10. Hàm NOW: Hàm trả về ngày và giờ hiện tại

– Công dụng của hàm Now: Nếu bạn muốn hiển thị ngày và thời gian hiện tại trên trang tính hoặc muốn tính toán dựa trên thời gian hiện tại thì hàm NOW là giải pháp dành cho bạn.
– Cú Pháp: = NOW ()
  • Cú pháp hàm NOW không sử dụng đối số.
  • Kết quả của hàm NOW được cập nhật khi bạn thực hiện một công thức hoặc edit một ô giá trị chứ không cập nhật liên tục.
– Chức Năng: Trả về giá trị ngày tháng năm và giờ phút hiện hành.
Ví dụ: Bây giờ là 10 giờ 30 phút ngày 14/08/2014. Khi viết cú pháp hàm =NOW() cho ô B6 sẽ trả về giá trị chính xác giờ phút ngày tháng năm hiện hành.
Hàm Now trong Excel

11. Hàm CHOOSE tìm chuỗi kí tự

Dạng thức: =CHOOSE(vị trị chuỗi kí tự, chuỗi thứ nhất, chuỗi thứ hai,…)
ví dụ: Cho dữ liệu các ô: B1=”Toan”; B2=”Van”; B3=”Ngoai Ngu”; B4=”Tin hoc”
=CHOOSE(3,B1,B2,B3,B4) = Ngoai Ngu

12. Dạng thức MATCH(giá trị dò, danh sách, cách dò)

Dạng thức MATCH(giá trị dò, danh sách, cách dò)
Hàm MATCH sẽ đem “giá trị dò” dò tìm trong bảng (bảng là dãy ô nằm trên một dòng hoặc trên một cột). Nếu tìm thấy sẽ trả về thứ tự của ô được tìm thấy nằm trong bảng. Nếu bảng là cột thì sẽ trả về số dòng, nếu bảng là dòng sẽ trả về số cột.
Hàm MATCH có 3 cách dò thể hiện bởi 3 giá trị sau:
0: bảng không cần được sắp xếp, hàm MATCH sẽ có kết quả nếu tìm được đúng giá trị đem dò, ngược lại trả về #N/A.
1: bảng phải được sắp xếp tăng. Nếu không tìm thấy đúng “giá trị dò” hàm MATCH sẽ trả về thứ tự của ô có giá trị lớn nhất trong bảng và nhỏ hơn “giá trị dò”.
-1: bảng phải được sắp xếp giảm. Nếu không tìm thấy đúng “giá trị dò” hàm MATCH sẽ trả về thứ tự của ô có giá trị nhỏ nhất trong bảng và lớn hơn “giá trị dò”.
ví dụ: =MATCH(5,{3,7,5,8},0) = 3 hoặc =MATCH(5,{3,5,7,8},1) = 2
hoặc =MATCH(5,{8,7,5,3},-1) = 3

13. Dạng thức INDEX(bảng, dòng số, cột số)

Dạng thức INDEX(bảng, dòng số, cột số)
Hàm INDEX trả về giá trị của ô trong bảng được xác định bởi giao giữa dòng và cột. Dòng đầu tiên trong bảng là dòng số 1, cột đầu tiên trong bảng là cột số 1.
Ví dụ: INDEX(A1:C5,2,3) sẽ trả về giá trị là giá trị của ô được xác định bởi giao của dòng số 2 và cột số 3 trong bảng tức là giá trị của ô C3.

14. Hàm VLOOKUP hay còn gọi là hàm tham chiếu cột

– Khác với hàm if, hàm vlookup hay còn gọi là hàm tham chiếu cột, nó lấy thông tin từ bảng phụ với điều kiện dò tìm ở bảng chính để điền vào cột nào đó của bảng chính nếu thỏa mãn điều kiện dò tìm.
Chú ý: Bài toán về hàm vlookup thường cho ra khi bắt ta tham chiếu cột từ một bảng phụ cho trước.
Cú pháp hàm excel nâng cao – vlookup:
=VLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
Giải thích cú pháp:
lookup_value: là giá trị để tham chiếu có thể là một ký tự, chuỗi ký tự, tham chiếu hay giá trị nào đó của bảng chính.
table_array: vùng chứa dữ liệu của bảng phụ (lấy vùng bằng cách dùng chuột quét cả bảng phụ). Luôn phải để ở dạng địa chỉ tuyệt đối bằng cách quét xong rồi nhấn F4 để có dấu $ đằng trước nhé
row_index_num: giá trị cần lấy ra thuộc cột mấy trong bảng phụ
range_lookup: muốn tìm chính xác hay tương đối (0 là chính xác, 1 là tương đối) thường giá trị này là 0 nhé.

Ví dụ hàm vlookup trong excel

– Dưới đây chỉ là những ví dụ cơ bản hàm vlookup thôi nhé bạn nên nhớ cần học thuộc hàm và biết vận dụng trong những trường hợp cụ thể nhé!
Ví dụ 1: Hãy điền tên vào cột tên hàng dựa vào ký tự của cột mã hàng lấy tham chiếu từ bảng phụ.
Hàm CHOOSE tìm chuỗi kí tự
Hàm CHOOSE tìm chuỗi kí tự
Tóm tắt ngắn ví dụ: nghĩa là bạn nhìn cột mã hàng ở bảng chính nó sẽ trùng với mã hàng ở cột bảng phụ bây giờ mình phải làm thế nào để từ dữ liệu cột mã hàng bảng chính tham chiếu xuống bảng phụ cho nó hiểu G =”Gà”, V=”Vịt”, C=”Chó” để điền vào cột tên hàng ở bảng chính.
Từ ví dụ ta có công thức =VLOOKUP(C6,$B$11:$C$14,2,0)
Bạn nhìn màu từ công thức trong hình và xem mình lấy hay quét dữ liệu như thế nào.
C6 (lookup_value): giá trị để tham chiếu nó là 1 ký tự trùng với ký tự từ bảng phụ
$B$11:$C$14 (table_array): vùng chứa dữ liệu bảng phụ quét cả bảng phụ và ấn F4 để ở dạng địa chỉ tuyệt đối nhé
2 (row_index_num): do lấy dữ liệu cột thứ 2 của bảng phụ lên là ,2. Nếu lấy cột thứ 3 thì , 3 hoặc ,4 ,5 ,6…
0 (range_lookup): dò tìm chính xác bạn cứ ,0 cho mình nhé.
Lưu ý: Bài toán ít khi cho ở dạng ví dụ 1 mà thường cho ở dạng ví dụ 2 sau đây thường kết hợp với một hàm khác nữa như hàm left, hàm right, hàm mid… thì mới tham chiếu được ký tự và điền vào bảng chính được.

15. Hàm dò tìm theo hàng: HLOOKUP (giá trị tìm, mảng giá trị, chỉ số dòng) (kết quả sẽ trả về giá trị của một ô)

Hàm HLOOKUP là hàm excel nâng cao dùng dò tìm một giá trị ở dòng đầu tiên của một bảng dữ liệu. Nó sẽ trả về giá trị ở cùng trên cột với giá trị tìm thấy trên hàng mà chúng ta chỉ định. Hàm HLOOPUP thường dùng để điền thông tin vào bảng dữ liệu lấy từ bảng dữ liệu phụ.

Cú Pháp: Hlookup (giá trị đem dò, bảng giá trị dò, số thứ tự hàng cần lấy, phạm vi tìm kiếm)
Giải thích:
Giá trị tìm kiếm: giá trị dùng để tìm kiếm, có thể là một chuỗi ký tự, một tham chiếu, một giá trị nào đó của bảng chính. Giá trị này phải có tên trong vùng dữ liệu tìm kiếm.
Vùng dữ liệu tìm kiếm: Vùng chứa dữ liệu cần tìm. Vùng chứa dữ liệu tìm kiếm này thường nằm ở bảng phụ. Lưu ý: trong vùng dữ kiệu tìm kiếm này phải chứa giá trị tìm kiếm
Hàng trả về giá trị tìm kiếm: giá trị lấy ở hàng thứ mấy trong bảng phụ
Tham số: muốn tìm chính xác hay tương đối. Tham số này điền “0” nếu bạn muốn dò tìm chính xác, điền số “1” nếu muốn dò tìm giá trị tương đối.
Hàm dò tìm theo hàng: HLOOKUP
Hàm dò tìm theo hàng: HLOOKUP.
Nắm vững các hàm cơ bản trong Excel này sẽ giúp các bạn tự tin, thành thạo hơn trong các công việc hành chính, kế toán,… Nếu bạn là một người bận rộn, công việc của bạn gắng liền với Excel nhưng lại không có thời gian đi học thì bạn có thể tham khảo 3 khóa học online chuyên sâu về Excel, tính ứng dụng thực tế cao và đang có chương trình ưu đãi học phí tại Kyna.
  • Quản lý dữ liệu có hệ thống
  • Luyện tập nhuần nhuyễn các hàm từ cơ bản đến nâng cao
  • Cách làm biểu đồ, báo cáo thống kê chuyên nghiệp
  • Nhân đôi hiệu suất công hiệu bằng các thủ thuật
  • Bảng tính lương cho công ty, lương cuối tháng
  • Bảng tính chi phí thuê khách sạn, tiền thuê kho, cước phí vận tải,…
  • Bảng thuế nhập khẩu
  • Bảng theo dõi tình hình bán hàng
  • Bảng báo cáo bán hàng, tình hình bán hàng, chi tiết hàng bán
  • Báo cáo doanh số nhập hàng, hóa đơn mua hàng, quản lý nhà sản xuất,…
  • Báo cáo doanh thu sản phẩm bán hàng, quản lý sổ bán hàng
  • Học chuyên sâu về công việc quản lý tùy theo ngành
  • Bảo mật file Excel
  • Tạo liên kết dữ liệu trong và ngoài file Excel
  • Ràng buộc dữ liệu Data Validation
  • Các loại bảng thống kê nâng cao
Chương trình học excel

4 bí kíp Excel dân văn phòng không thể bỏ qua

0 comments
Tổng hợp lại những bí kíp Excel thông dụng và hiệu quả bao gồm cách tách họ tên, hàm VLOOKUP, và các mẹo nhỏ hay giúp bạn làm việc hiệu quả hơn

Tách họ và tên

Có lẽ đây là vấn đề thường gặp nhất của dân văn phòng. Vậy cho nên chúng ta đừng để phải mất quá nhiều thời gian với nó.
4 bí kíp Excel dân văn phòng không thể bỏ qua
Giả sử chúng ta phải tách họ và tên của một danh sách 5 người sau đây:
Hãy:
  • Copy lại cột A sang cột C.
  • Khoanh vùng các tên cần tách họ và tên ở cột C
  • Nhấn tổ hợp phím Ctrl + F để mở hộp thoại tìm kiếm và thay thế
  • Ở hộp thoại Find what trong cửa sổ Replace, hãy nhập: *_ (dấu sao và khoảng trắng)
  • Chọn Replace All và chờ đợi điều kỳ diệu
Excel_2
Excel_3
Vậy còn phần còn lại của tên thì phải làm sao? Đơn giản thôi, hãy:
  • Trỏ chuột qua ô B1
  • Nhập công thức sau: =LEFT(A1,LEN(A1)-LEN(C1))
  • Copy xuống các ô còn lại
Vậy là bạn đã có 1 danh sách họ riêng, tên riêng hoàn chỉnh:
Excel_4

Hàm SUMIF

Ttổng kết số lượng Like của các bài đã viết. Đề bài đơn giản như sau:
Excel_5
Để ví dụ lấy đơn giản vậy thôi, chứ thật ra nhiều khi các bạn phải đối mặt với rất nhiều tình huống phức tạp khác có thể ứng dụng SUMIF đó.
Với bài này, hãy làm sau đây nha:
  • Để trỏ chuột tới ô H2
  • Nhập công thức: =SUMIF(A2:A5,”Tháng 9″,D2:D5) (Với A2:A5 là ô để bạn lấy điều kiện, “Tháng 9” là điều kiện, còn ô D2:D5 là ô dữ liệu mà excel sẽ cộng lại dựa trên điều kiện bạn đưa ra)
Kết quả là:
Excel_6

Hàm VLOOKUP

Nếu như mấy hàm trên kia chỉ là bí kíp nhỏ lẻ, hàm VLOOKUP là đỉnh cao của Excel. Là dân văn phòng, không ít trường hợp bạn sẽ phải dùng đến nó đó.
Excel_7
Sếp đưa cho danh sách 3 khách hàng trung thành là Hải, Vi và Lâm và hãy tìm ra số lượng hàng mà 3 người này đã mua.
Excel_8

Các bạn làm như sau nhé:
  • Để trỏ chuột vào ô H2
  • Nhập vào công thức: =VLOOKUP(G2,A:C,3,FALSE) (với G2 là ô đại diện tên khách hàng trung thành (ở đây là Hải), A:C là cột bao gồm bảng thông tin cần trích xuất (ở đây là bảng 10 khách hàng), 3 là vị trí cột cần lấy thông tin (với bảng 10 khách hàng, cột Số lượng đã mua nằm ở vị trí số 3)
Vậy là xong, bạn sẽ có ngay kết quả như bên dưới:
Excel_9

Conditional formatting

Conditional formatting được dùng để phân loại số liệu cho dễ.
Chẳng hạn như bạn có một loạt các email và bạn muốn biết xem Open Rate (tỉ lệ mở email) của email nào là tệ nhất. Giả sử bạn cho là Open rate dưới 5% là tệ.
Excel_10
Hãy sử dụng conditional formatting để tìm ra email đó nha. Tìm chức năng conditional formatting như hình sau:
Excel_11
Ở đây có rất nhiều tùy chọn để bạn sử dụng. Trong trường hợp này ta chọn thẻLess than. Một hộp thoại hiện ra, bạn chỉ cần chọn theo nhu cầu của mình thôi. Ta sẽ chọn là 5, và ô dưới 5 sẽ được tô đỏ.
Excel_12
Vậy là bạn đã dễ dàng nhận ra email nào có open rate thấp dưới 5 rồi:
Excel_13

Home
Hướng dẫn cách lập bảng chấm công theo ca và làm thêm giờ trên Excel hay nhất

HƯỚNG DẪN CÁCH LẬP BẢNG CHẤM CÔNG THEO CA VÀ LÀM THÊM GIỜ TRÊN EXCEL HAY NHẤT

Excel cơ bản Hành chính nhân sự    , , 

Chia sẻ bài viết này:

Trong nhiều công ty lớn hiện nay thường yêu cầu bộ phận kế toán, nhân sự phải thực hiện chấm công theo ca và làm thêm giờ trên cùng 1 bảng tính Excel. Vậy chúng ta có thể tổ chức bảng tính Excel như thế nào để đáp ứng được yêu cầu này? Hãy cùng Học Excel Online tìm hiểu cách lập bảng chấm công theo ca và làm thêm giờ trên Excel đang được áp dụng nhiều nhất nhé.

YÊU CẦU CỦA VIỆC CHẤM CÔNG THEO CA VÀ LÀM THÊM GIỜ

Trước hết chúng ta cùng tìm hiểu xem việc chấm công theo ca và làm thêm giờ cụ thể là làm gì nhé.
Những doanh nghiệp áp dụng làm việc theo ca thường là doanh nghiệp làm trong ngành sản xuất, dịch vụ. Đặc điểm là:
  • Trong 1 ngày có thể chia làm 2 – 3 ca làm việc. Mỗi ca làm 8 tiếng.
  • Các nhân viên có thể luân chuyển các ca làm thoải mái theo tuần, tháng.
  • Trong mỗi ca lại có thể làm thêm giờ ngay trong ca đó.
  • Một ngày chỉ làm chính thức 1 ca, còn lại thời gian vượt quá sẽ tính làm thêm giờ
Do vậy mỗi người trong bảng chấm công sẽ phải ghi nhận được:
  • Làm việc vào ngày nào trong tháng
  • Trong ngày đó làm ca nào, làm việc bao nhiêu giờ trong 1 ca
  • Trong ca đó làm thêm bao nhiêu giờ
  • Tổng số giờ làm việc chính thức
  • Tổng số giờ làm thêm (tăng ca)
  • Ngoài yếu tố giờ làm, còn phải xác định được các yếu tố: nghỉ phép, nghỉ không phép, nghỉ chế độ…
Dựa trên những yêu cầu trên chúng ta cùng tìm hiểu cách thực hiện bảng chấm công trên Excel nhé.

BẢNG CHẤM CÔNG THEO CA VÀ LÀM THÊM GIỜ TRÊN EXCEL

XÂY DỰNG BỐ CỤC BẢNG CHẤM CÔNG

Các bạn có thể xây dựng bố cục bảng chấm công như sau:
Mỗi nhân viên sẽ có 4 dòng:
  • 3 dòng ứng với 3 ca làm việc để theo dõi số công theo từng ca
  • 1 dòng ứng với thời gian tăng ca (có thể ký hiệu tắt là TC hoặc OT)
Mỗi tháng sẽ gồm 31 ngày, tương ứng với 31 cột, được đánh số từ 1 tới 31
Để xác định ngày đó là thứ mấy, có thể làm như sau:
  • Công thức xác định ngày tại dòng 4: Dùng hàm Date dựa theo Năm là năm hiện tại, tháng là số tại ô B2, ngày là số tại ô D3
  • Định dạng Ngày về dạng thứ trong tiếng anh: Sử dụng Custome Format: Tại dòng 4, sử dụng Format cells > Custome > Nhập vào mục Type là ba chữ d viết liền (như hình bên dưới)
Như vậy ta sẽ có kết quả là giá trị Ngày tại dòng 4, nhưng được hiển thị dưới dạng Thứ trong tiếng anh.
Việc còn lại là trang trí kẻ khung, tô màu, căn chỉnh độ rộng cho bảng chấm công sao cho đẹp mắt và dễ nhìn.
Bằng việc thấu hiểu rằng hầu hết nhân viên văn phòng thường mất nhiều thời gian tra cứu cách sử dụng các hàm, các tính năng trong Excel, gặp khó khăn trong lồng ghép hàm, xử lý dữ liệu, công việc bận rộn không có thời gian học. Nên Học Excel Online đã thiết kế ra chương trình Excel từ cơ bản tới nâng cao dành cho người đi làm. Đây là chương trình học online, học viên có thể chủ động học mọi lúc mọi nơi, trao đổi trực tiếp với giảng viên tại bài giảng trong suốt quá trình học.
Với khoá học này học viên sẽ nắm được: :
  • Trọn bộ công cụ định dạng báo cáo chuyên nghiệp: Format, Cell, Number, Style,...không mất thời gian cả ngày ngồi sửa báo cáo
  • Toàn bộ kỹ năng lọc dữ liệu, lọc dữ liệu nâng cao trong Excel phục vụ cho mục đích trích xuất dữ liệu
  • Data Validation kiểm soát dữ liệu khi nhập vào một vùng trong bảng tính
  • TẤT TẦN TẬT hơn 100 hàm thông dụng, công thức mảng trong Excel, học tới đâu nhớ tới đó
  • Bộ 36 PHÍM TẮT giúp thao tác nhanh gấp đôi
  • BÁO CÁO SIÊU TỐC trong 1 phút với Pivot Table
  • Hỏi đáp trực tiếp tại bài giảng với giảng viên trong suốt quá trình học
  • CHUYÊN NGHIỆP trong báo cáo bằng Biểu đồ, đồ thị,....
  • 142+ bài giảng, 13+ giờ học, 200+ ví dụ thực tế cùng chuyên gia
  • 100+ Kỹ năng, thủ thuật Excel thông dụng với các hàm: SUMIF, HLOOKUP, VLOOKUP, DATEDIF…

CÁCH CHẤM CÔNG VÀO BẢNG

Với chấm công trong từng ca, có thể sử dụng chấm công theo ký hiệu hoặc theo số giờ.
Với chấm công làm thêm giờ, tăng ca, nên chỉ chấm bằng số giờ
Khi chấm công làm thêm giờ, lưu ý chấm đúng theo ngày để xác định được làm thêm giờ trong ca nào
Ví dụ như sau:
Ký hiệu X là đi làm trong ca
Ký hiệu P là nghỉ phép
Ký hiệu K là nghỉ không phép
Trong 1 ngày chỉ có duy nhất 1 ca được chấm.
Số giờ làm tăng ca trong ngày được chấm kèm theo ngày đó.
Ví dụ:
  • NV001 ngày 04/01/2019 đi làm ca 1 và tăng ca 2 giờ
  • NV001 ngày 07/01/2019 đi làm ca 2 và tăng ca 3 giờ

CÁCH TÍNH CÔNG CUỐI THÁNG

Sử dụng các hàm COUNTIF để đếm số công trong các ca:
  • Số ca làm (ký hiệu X): =COUNTIF([Vùng chấm công], “X”)
  • Số ngày nghỉ phép (ký hiệu P) =COUNTIF([Vùng chấm công], “P”)
  • Số ngày nghỉ không phép (ký hiệu K) =COUNTIF([Vùng chấm công], “K”)
Sử dụng hàm SUM để tính tổng số giờ làm thêm: =SUM([Vùng chấm công])
Kết quả như sau:

 
Home
Tính công khi chấm công bằng số xen lẫn ký hiệu

TÍNH CÔNG KHI CHẤM CÔNG BẰNG SỐ XEN LẪN KÝ HIỆU

Excel cơ bản Excel nâng cao Hành chính nhân sự Kế toán Kế toán tiền lương Thủ thuật Excel    , , , , , , , , , 
Chia sẻ bài viết này:
  • 6

Hầu hết khi thiết lập bảng chấm công trên Excel, người kế toán tiền lương thường không xây dựng theo một tiêu thức, tiêu chuẩn cụ thể nào. Mỗi nơi chấm công một kiểu. Hay thậm chí cùng 1 người lại chấm công mỗi lúc một kiểu.
Có một số quy ước chấm công mà chúng ta vẫn thường làm theo như:
  • Ký hiệu X : chấm cho 1 công làm việc đủ 8 tiếng.
  • Nếu ít hơn 8 giờ công đó, thì ghi số giờ.
  • Làm nửa ngày thì ký hiệu N, tức là 4 tiếng một ngày.
Vậy chúng ta tính tổng số giờ công như thế nào, khi mà bảng chấm công sử dụng đồng thời cả ký tự X, N và cả số giờ công?
Cách làm như sau:
Đếm số giờ công lẻ bằng hàm
SUM(E6:N6)
Đếm số ngày chấm công bằng ký hiệu X (hàm Countif), sau đó quy đổi ra giờ bằng cách nhân với 8
COUNTIF(E6:N6,$A$13)*8
Đếm số ngày chấm công bằng ký hiệu N (hàm Countif), sau đó quy đổi ra giờ bằng cách nhân với 4
COUNTIF(E6:N6,$A$14)*4
Kết hợp cả 3 hàm này vào 1 ô xác định kết quả:
O6 = SUM(E6:N6)+COUNTIF(E6:N6,$A$13)*8+COUNTIF(E6:N6,$A$14)*4
Xem thêm: lớp học Excel kế toán với các chuyên gia
Thật đơn giản phải không nào.
By at

Không có nhận xét nào:

Đăng nhận xét