Cập nhật nội dung chi tiết về Nhân Sự Hay Dùng Những Hàm Excel Gì – Excel Và Công Tác Quản Trị Nhân Sự? mới nhất trên website Beiqthatgioi.com. Hy vọng thông tin trong bài viết sẽ đáp ứng được nhu cầu ngoài mong đợi của bạn, chúng tôi sẽ làm việc thường xuyên để cập nhật nội dung mới nhằm giúp bạn nhận được thông tin nhanh chóng và chính xác nhất.
Phần 1: Chúng ta cùng tìm hiểu Các hàm công thức thường dùng trong bảng chấm công. 1/ Công thức thứ ngày tháng: ở đây tôi chưa hướng dẫn phần format và trình bày bảng biểu mà mục tiêu là các công thức thường xuyên dùng trong bảng chấm công.
Đầu tiên, tạo bảng biểu dữ liệu với đầy đủ các cột phù hợp với cách chấm công mỗi công ty. Trong bảng công, nhất định chúng ta nên có 1 dòng thể hiện thứ ngày tháng, 1 dòng thể hiện ngày trong tháng. Nếu trường hợp là công ty sản xuất với ngày nghỉ sẽ không rơi vào ngày chủ nhật thì nên thêm dòng thể hiện ngày thường hoặc ngày nghỉ như mẫu của tôi.
Trong bảng biểu dữ liệu, hạn chế tối đa việc hợp ô, hợp dòng (tôi nói là hạn chế thôi chứ không phải là không sử dụng). Chúng ta nhập ngày đầy đủ 01/01/2018 vào ô và kéo từ ngày 1 đến ngày cuối tháng.
Điều chỉnh hiển thị bằng cách, vào Format, chọn Custom, trong ô Type kéo xuống cuối cùng, gõ chữ dd (chỉ hiện ngày), mm (chỉ hiện tháng), yyyy (chỉ hiện năm), ở đây chúng ta chọn chỉ hiện ngày.
Lập công thức thứ trong tuần từ dòng ngày trong tháng. Có nhiều cách để có thể tạo thứ bằng tiếng Việt hoặc tiếng Anh, nhưng thông thường chúng ta dùng tiếng Anh, sử dụng công thức: =TEXT(F7,”ddd”). F7 là địa chỉ ô ngày 01 đầu tháng. Kéo kết quả đến ngày cuối tháng
=IF(F6=””,””,IF(F6=”Sun”,”N”,”T”)). F6 là địa chỉ ô chứa thứ thuộc ngày đầu tháng. Nếu F6 = Sun thì ra kết quả ngày N, còn lại là ngày thường T. Trường hợp này tôi ví dụ khi ngày nghỉ chủ nhật là ngày nghỉ tuần của người lao động
Trong 1 số trường hợp công ty quy định ngày nghỉ của NLĐ là ngày khác thì điều kiện công thức của chúng ta phải thay đổi.
Vì theo luật, Điều 110. Nghỉ hằng tuần: 1. Mỗi tuần, người lao động được nghỉ ít nhất 24 giờ liên tục. Trong trường hợp đặc biệt do chu kỳ lao động không thể nghỉ hằng tuần, thì người sử dụng lao động có trách nhiệm bảo đảm cho người lao động được nghỉ tính bình quân 01 tháng ít nhất 04 ngày. 2. Người sử dụng lao động có quyền quyết định sắp xếp ngày nghỉ hằng tuần vào ngày chủ nhật hoặc một ngày cố định khác trong tuần nhưng phải ghi vào nội quy lao động.
Như vậy chúng ta hoàn thành việc thiết lập thứ ngày tháng trong bảng công, đây là phần quan trọng khi thiết lập bảng công để dễ tính toán. Khi tạo bảng biểu dữ liệu, phải suy nghĩ cách thể hiện nào thuận tiện cho việc tính toán và quản lý của chúng ta một cách tốt nhất. Vì nó là công cụ để chúng ta hoàn thành công việc.
PHẦN TIẾP THEO, CHÚNG TA TẠO LẬP CÔNG THỨC CHI TIẾT BẮT ĐẦU TỪ SỐ THỨ TỰ 2/ Công thức số thứ tự: Bình thường thì ai cũng nghĩ đơn giản là đánh số TT chỉ cần kéo trỏ chuột thủ công bằng những cách khác nhau để tạo ra DS có STT.
Cách 1: Đánh STT 1 , bấm phím Ctrl và rê chuột đến dòng bạn cần.
Cách thứ nhất: Với những bảng dữ liệu liên tục không phân biệt phòng ban đánh số liên tục từ 1-cuối cùng chẳng hạn thì chúng ta dùng công thức đơn giản: = Row()-số dòng kế trên . Ví dụ chúng ta đang bắt đầu đếm Số 1 từ dòng 9 thì trong công thức là =Row()-8
Công thức này chỉ áp dụng khi đơn giản và không có điều kiện, không phân biệt phòng ban hay khoảng cách. Vậy trong công việc nhân sự chúng ta luôn có danh sách với đủ các phòng ban, thì chúng ta nên dùng công thức nào?
Cách thứ 2: Công thức đáp ứng điều kiện cho bảng công và lương với nhiều phòng ban =IF(ISTEXT(B10),MAX(A$9:A9)+1,””)
– Nếu cột mã nhân viên là text thì lấy STT lớn nhất bên trên cộng với 1, ngược lại khong có text thì để trống. Lưu ý cột Mã NV, nếu là phòng ban thì để trống hoặc điền số, nếu là nhân viên thì điền mã dạng text. – Nếu chúng ta đang dùng dữ liệu dạng bảng thì công thức tự động lấp đầy bảng – Nếu không thì chúng ta kéo kết quả xuống dòng cuối cùng
Như vậy các bạn sẽ thấy công thức bỏ qua ô trống để điền số TT. Nhưng 1 điều bất cập ở công thức này là, nó không phân biệt được mã trùng, giả sử mã nhân viên của chúng ta là mỗi người chỉ một đúng không ạ? Như vậy việc trùng mã nghĩa là 2 mã trùng nhau chỉ là 1 người (đếm chỉ 1) thì công thức này chưa đáp ứng được yêu cầu. Vậy nên cách thứ 3 chúng ta sử dụng công thức: =IF(AND(ISTEXT(B10),COUNTIF(B$10:B10,B10)=1),MAX(A$9:A9)+1,””)
Vậy công thức này ra kết quả chính xác số lượng nhân sự (lưu ý đảm bảo mỗi nhân viên là 1 mã khác nhau). =IF(AND(ISTEXT(B10),COUNTIF(B$10:B10,B10)=1),MAX(A$9:A9)+1,””)
Countif =1 có nghĩa là chỉ đánh STT cho dòng đầu tiên chứa mã thôi, từ dòng thứ 2 trở đi có cùng mã như vậy thì để trống, không đánh STT Vì dòng thứ 2 chứa mã đó thì kết quả COUNTIF sẽ là số 2.
Tóm lại: Nếu cột B có chứa mã và lần đầu tiên xuất hiện mã đó thì đánh STT, còn lần thứ 2 trở đi thì không đánh STT nữa, đảm bảo mỗi mã chỉ được đánh STT một lần. Có thể hiểu là trong trường hợp có nhiều mã giống nhau (1 nhân viên có trên 1 dòng dữ liệu chấm công) thì việc đánh STT chỉ thực hiện cho dòng đầu tiên thôi, các dòng còn lại không đánh STT.
Phần 2: Hướng dẫn tô màu ngày chủ nhật Để thuận tiện cho công việc, trong bảng công chúng ta nên dùng tô màu tự động cho các loại ngày nghỉ chủ nhật, lễ tết…nó cũng là 1 cách ghi nhớ để chúng ta tính toán các loại ngày công theo quy định và không bị sai sót.
Từ công thức như video 1 tôi đã hướng dẫn tạo thứ, ngày tháng, chúng ta lập công thức như sau: =WEEKDAY(F7)=1
Mô tả: Hàm trả về thứ trong tuần của 1 ngày tương ứng. Giá trị trả về là số nguyên từ 1 (chủ nhật) đến 7 (thứ 7). 1 ở đây là chúng ta xác định chủ nhật.
Lập công thức =WEEKDAY(F7)=1 đặt tại 1 ô nào đó trong bảng công.
Chọn vùng dữ liệu cần tô màu (ở đây là bao gồm từ ngày 1- cuối tháng).
Vào Formating- new ruler – trong mục Select ruler (lựa chọn) chúng ta chọn dòng cuối cùng
Sử dụng công thức cho ô, trong muc Format values…. Nhập công thức chúng ta vừa tạo– tiếp theo vào Format chọn màu phù hợp- ok. Ok – như vậy kết quả đã tô màu cho những ngày chủ nhật.
Những công thức này thông thường chúng ta chỉ cần tạo 1 lần từ tháng sau chúng ta chỉ cần thay đổi ngày tháng, thì bảng màu tự động di chuyển. Rất tiện ích phải không ạ?
Công thức tô màu ngày lễ trong năm Tạo list các ngày lễ như mẫu. Nhập công thức kiểm tra vùng dữ liệu tại 1 ô bất kỳ =F$7=IFERROR(VLOOKUP(F$7,BU1:BU10,1,0),””)
Chọn vùng dữ liệu cần tô màu (ở đây là bao gồm từ ngày 1 đến ngày cuối tháng).
Vào Formating- new ruler – trong mục Select ruler (lựa chọn) chúng ta chọn dòng cuối cùng
— tiếp theo vào Format chọn màu phù hợp- ok. Ok – như vậy kết quả đã tô màu cho những ngày chủ nhật.
Vậy là chúng ta đã hoàn thành công việc tô màu cho ngày chủ nhật và ngày lễ
Phần 3: Cách chấm công và kiểm soát các ký tự bảng chấm công Thông thường, đối với mỗi công ty phụ thuộc vào mô hình kinh doanh chúng ta sẽ thiết lập bảng chấm công phù hợp. Đối với công ty thương mại, kinh doanh thì thường chỉ có giờ hành chính nên chúng ta thường sử dụng cách chấm công bằng ký tự chữ vì nó không phát sinh các loại ca làm việc và giờ tăng ca theo ca làm việc.
Còn với sản xuất thì ký tự chữ không đủ đáp ứng khi chấm các ca đêm tăng ca….nên chúng ta nên dùng chấm công bằng số. Mỗi cách chấm công có điểm ưu và nhược riêng.
1. Chấm công dạng ký tự: Thì các bạn sẽ phải định nghĩa cho các ký tự là chấm cho ca nào, phân nhóm các loại ngày công. Với dạng này sẽ có nhiều cột tổng hợp vì mỗi loại ký tự là 1 cột tổng công. 2. Chấm công dạng số: Nếu có nhiều ca làm việc thì sẽ cần ít nhất 1 nhân viên có 3 dòng, thể hiện các loại ca khác nhau, còn nếu chỉ có giờ hành chính thì ít phức tạp hơn.
Tổng hợp công của các ký tự, chúng ta dùng hàm Countif thông thường, cộng các ký tự =COUNTIFS(Table2[@ [Column6]:[Column35]],”HC”)+COUNTIFS(Table2[@[Column6]:[Column35]],”HC/P”)/2+COUNTIFS(Table2[@[Column6]:[Column35]],”HC/K”)/2
Ở đây chúng ta hiểu là những ký tự HC tính là 1, hàm đếm này không phân biệt có bao nhiêu ký tự trong ô, kể cả có 10 ký tự trong 1 ô nó vẫn đếm là 1. Do vậy các ký tự HC/P, hay HC/k thì chúng ta hiểu nó đang là nửa ngày công vậy không thể đếm là 1- tổng hợp công bị sai đúng khong ạ?
Vậy công thức hàm countif sẽ là COUNTIFS(Table2[@ [Column6]:[Column35]],”HC/P”)/2 chia 2 sẽ ra nửa ngày công. Với cách chấm ký tự này, cho phép chúng ta dễ dàng kiểm soát các ngày công
Tôi giả sử có bạn chỉ còn nửa ngày phép, nhưng bạn nghỉ cả ngày thì đương nhiên thứ tự ưu tiên của chúng ta sẽ là: Nghỉ phép, nghỉ bù, nếu hết phép hết ngày nghỉ bù sẽ tính là không lương.
Thực hành trên bảng chấm công này, chúng ta sẽ được kết quả như file mẫu.
Đối với trường hợp chấm bằng số, nếu 1 công ty thương mại hay kinh doanh chủ yếu là ca hành chính thì chỉ cần chấm 1 dòng và kèm thêm bảng theo dõi tăng ca nếu có.
Nhưng nếu có nhiều ca làm việc thì mỗi người nên chấm công 3 dòng để thể hiện các loại ca khác nhau. Với mỗi cách có ưu và nhược điểm khác nhau và phù hợp với các công ty khác nhau.
Đối với công ty có chấm ký tự số và nhiều ca khác nhau: =IF([@Column5]=”C1,C2″,SUMIF($F$9:$AJ$9,”T”,Table24[@ [Column6]:[Column352]]),0)/8
Nếu giá trị E15= ký tự C1,C2 thì tính tổng với điều kiện, Hàng F9, có chữ T, thì cộng dồn kết quả của hàng 15 vào tổng.
Công thức tính giờ công tăng ca: =IF(E15=”TC”,SUMIF($F$9:$AI$9,”T”,F15:AI15),0)
Nếu E25=”TC” thì tính tổng với điều kiện Hàng 9 có chữ T và tính tổng hàng 15 cộng dồn vào tổng.
Như vậy chúng ta hiểu rằng, với mỗi loại ký tự chấm công thì có các công thức tính khác nhau, nhưng dù là cách nào thì các bạn vẫn phải tổng hợp đầy đủ các loại công và ca làm việc.
Ngoài hàm Count thì chúng ta sử dụng hàm if nhiều điều kiện để đáp ứng yêu cầu cho bảng tính. • Để lấy dữ liệu từ bảng công sang bảng lương, ngoài việc sử dụng hàm Vlookup thì chúng ta có thể sử dụng hàm Sumif. • Hàm Vlookup và Sumif là 2 nhóm hàm khác nhau.Một hàm tìm kiếm, một hàm thống kê. Vlookup có thể đem so sánh với Index, hoặc Hlookup, hoặc Lookup, chứ không so sánh với Sumif được. Ngược lại, Sumif thì so sánh với Countif, Sumproduct, Sumifs, Countifs.
Thao tác hàm Sumif như sau:
Lập công thức hàm Sumif chuẩn, copy sang 1 ô khác. Xóa công thức tại ô vừa tạo.
Chọn vùng dữ liệu cần điền, phải đảm bảo dữ liệu hai bảng là giống nhau các cột, số lượng cột tiêu đề cột. Nhập công thức trên thanh công cụ, nhấn Ctrl + Enter.
Chỉ nhấn Ctrl+Enter thôi, không có Shift. Có Shift chỉ dành riêng cho công thức mảng.Nhấn thêm Shift thì ở đầu, cuối công thức sẽ có thêm cặp dấu {}, đây là đặc trưng của công thức mảng.Chẳng hạn công thức dạng =SUM(IF()) thì phải kết thúc bằng Ctrl+Shift+Enter.
Như vậy là chúng ta đã lấy được dữ liệu theo yêu cầu. Hàm tìm kiếm và tham chiếu (Vlookup)
=VLOOKUP(Giá trị bạn muốn tra cứu, Vùng chứa giá trị cần tìm kiếm, số cột trong vùng chứa giá trị trả về, Kết quả khớp Chính xác hoặc Kết quả khớp Tương đối – được biểu thị là 0/FALSE hoặc 1/TRUE).
Hoặc cú pháp đơn giản: =VLOOKUP(Trị dò, Bảng dò, Cột kết quả, Kiểu dò)
Nếu sử dụng hàm Vlookup đơn thuần thông thường các kết quả không khớp mã sẽ ra #NA dẫn đến địa chỉ giá trị tổng bị lỗi #NA.
1/ Hàm VLOOKUP nếu không tim thấy thì sẽ trả về lỗi #N/A!. Hàm IFERROR được lồng vào nhằm mục đích trong trường hợp đó thì trả về kết quả theo ý mình. =IFERROR(VLOOKUP(…),Kết quả cần trả về)
Hàm IFERROR được lồng ở bên ngoài các hàm khác, không chỉ VLOOKUP, nói chung là các hàm mà có thể phát sinh lỗi, nhằm mục đích giúp người dùng xử lý tình huống lúc hàm ở trong đó phát sinh lỗi.
Ví dụ: =IFERROR(VLOOKUP(B16,B6:C10,2,TRUE))– sai Phải là: =IFERROR(VLOOKUP(B16,B6:C10,2,TRUE),”I love you!”) Iloveyou có thể là 0.
Nếu hàm VLOOKUP phát sinh lỗi (không tìm thấy giá trị ô B16 trong cột đầu của vùng B6:C10) thì công thức sẽ trả về chuỗi “I love you!”
Ngược lại thì nó trả về giá trị ở cột thứ 2 trong vùng B6:C10 (tức là cột C) tương ứng với vị trí xuất hiện của B16 trong vùng B6:B10
Chỗ TRUE ở VLOOKUP phải dùng FALSE hoặc 0 là chính xác, chứ TRUE hoặc 1 là sai
Tham số thứ 4 của công thức: Nếu tham số này là FALSE hoặc 0 thì trong trường hợp không tìm thấy giá trị nào chính xác bằng giá trị ở tham số thứ nhất thì hàm sẽ phát sinh lỗi #N/A!
Nếu nó là TRUE hoặc 1 thì trong trường hợp không tìm thấy giá trị nào chính xác bằng giá trị ở tham số thứ nhất thì hàm sẽ trả về giá trị tương ứng với giá trị “lớn nhất nhưng bé hơn” giá trị ở tham số thứ nhất (Ví dụ: Mình có bảng phụ cấp theo số năm công tác, nhưng bị cách quãng, chẳng hạn theo thang 1 năm, 3 năm, 5 năm, 10 năm,… Giờ giả sử có 1 nhân viên công tác được 4 năm thì sẽ lấy phụ cấp ở mức nào? Hiển nhiên số 4 không có trong bảng dò nên nó sẽ dò từ trên xuống dưới, thấy có số 3 là số “lớn nhất nhưng bé hơn 4”, vậy là người này sẽ được hưởng mức phụ cấp tương ứng 3 năm công tác. Cũng chính vì cách dò tuần tự từ trên xuống này nên bảng dò buộc phải sắp xếp tăng dần, nếu không sẽ sai kết quả.)
Nếu để FALSE hoặc 0 thì sẽ phát sinh lỗi #N/A vì không có số 4 trong bảng dò.
Trong trường hợp là TRUE (hoặc 1) thì bắt buộc cột thứ nhất của bảng dò phải được sắp xếp tăng, ngược lại thì hàm sẽ sai. Nếu để FALSE hoặc 0 thì sẽ phát sinh lỗi #N/A vì không có số 4 trong bảng dò.
Chỉ dùng TRUE hoặc 1 trong trường hợp mình không dám chắc trị dò có trong bảng dò, chẳng hạn tính phụ cấp theo thâm niên. (Ví dụ: Mình có bảng phụ cấp theo số năm công tác, nhưng bị cách quãng, chẳng hạn theo thang 1 năm, 3 năm, 5 năm, 10 năm,… Giờ giả sử có 1 nhân viên công tác được 4 năm thì sẽ lấy phụ cấp ở mức nào? Hiển nhiên số 4 không có trong bảng dò nên nó sẽ dò từ trên xuống dưới, thấy có số 3 là số “lớn nhất nhưng bé hơn 4”, vậy là người này sẽ được hưởng mức phụ cấp tương ứng 3 năm công tác. Cũng chính vì cách dò tuần tự từ trên xuống này nên bảng dò buộc phải sắp xếp tăng dần, nếu không sẽ sai kết quả.)
Nó là cái thám số thứ 2 của hàm IFERROR chứ không phải là tham số của hàm VLOOKUP, nó nằm ngoài dấu ngoặc .
Hàm Sumproduct tính tổng có điều kiện
Cú pháp: =SUMPRODUCT(array1,array2,array3….)
Cách thức hoạt động: Hàm sẽ nhân lần lượt các phần tử của các mảng array1, array2,… theo thứ tự, sau đó cộng các tích đó lại với nhau để ra kết quả.Tuy nhiên, thông thường người ta sử dụng dưới dạng =SUMPRODUCT((ĐK1)*(ĐK2)*(ĐK3)*…)
Với cách thức trên, hàm sẽ kiểm tra lần lượt các điều kiện ĐK1, ĐK2,… và trả về mỗi điều kiện thành một mảng các giá trị TRUE, FALSE hay 1, 0 rồi nhân các mảng này lại với nhau và cộng các tích thu được.
Xét điều kiện (A1 : A3<12), sẽ trả về mảng {True,True,False} hay {1,1,0} 3. Tính: {1,1,0}*{0,1,1}*{1,2,3}=1*0*1+1*1*2+0*1*3, được kết quả là 2. =SUMIF(Vùng1,Điều kiện,Vùng2) Tính tổng theo điều kiện
Hàm sẽ kiểm tra trong Vùng1, nếu giá trị nào thỏa mãn Điều kiện thì sẽ cộng dồn giá trị tương ứng ở Vùng 2 vào tổng. Trong trường hợp Vùng 2 và Vùng 1 trùng nhau thì mình có thể bỏ qua tham số thứ 3, nó chỉ còn =SUMIF(Vùng,Điều kiện) =SUMIF(MaNV_BM,$B53,Com_BM), hàm sẽ kiểm tra trng vùng MaNV_BM, nếu ô nào có giá trị bằng ô $B53 thì sẽ lấy giá trị tương ứng ở vùng Com_BM để cộng dồn vào tổng.
. Trong trường hợp Vùng 2 và Vùng 1 trùng nhau
Có nghĩa là vùng để tính tổng cũng chính là vùng để kiểm tra điều kiện
Tính tổng theo nhiều điều kiện =SUMIFS(Vùng_tính_tổng,Vùng_1,ĐK_1,Vùng_2,ĐK_2,…)
Ý nghĩa của nó là kiểm tra trên các vùng, nếu anh nào có giá trị ở Vùng_1 thòa mãn ĐK_1, có giá trị ở Vùng_2 thỏa mãn ĐK_2, thỏa mãn đồng thời, thì lấy giá trị tương ứng trong Vùng_tính_tổng để cộng dồn vào kết quả.
Nếu có 2 điều kiện thì phải cả 2 đứa cùng thỏa mãn, có 3 điều kiện thì cả 3 đứa cùng thỏa mãn mới tính tổng.
Cách thức thực hiện: Chọn vùng cần dán dữ liệu Nhập công thức trong thanh công cụ Kết thúc bằng Ctrl Shift Enter Quét chọn vùng S8: AT15 bên sheet lương, sử dụng công thức sau: =SUMIF(BCC!$B$10:$B$18,$B8,BCC!AK$10:AK$18) và nhấn Ctrl+Enter. Để dùng cho cả mảng: Quét chọn vùng, copy công thức vào thanh công thức, nhấn Ctrl+enter. Chẳng hạn “Quét chọn vùng S8: AT15” thì quét từ ô S8 đến ô AT15, để đảm bảo ô S8 đang là ô sẽ nhập công thức vào.
Dùng Sumif để lấy dữ liệu chấm công từ bảng công
Nguồn: fb/phan.ht.771
Dài quá phải không anh chỉ em? Đây là clip minh họa cho bài:
Phải nói là đọc hơi khó hiểu. Nhưng chúng ta cứ đọc cho hết. Phần 1 của bài ở đây: Nhân sự hay dùng những hàm excel gì ? ( http://blognhansu.net.vn/?p=7954 )
Bài viết được Google đề xuất đọc kèm:
Bài viết được Google đề xuất đọc kèm:
Nhân Sự Hay Dùng Những Hàm Excel Gì
Đầu tiên, tạo bảng biểu dữ liệu với đầy đủ các cột phù hợp với cách chấm công mỗi công ty. Trong bảng công, nhất định chúng ta nên có 1 dòng thể hiện thứ ngày tháng, 1 dòng thể hiện ngày trong tháng. Nếu trường hợp là công ty sản xuất với ngày nghỉ sẽ không rơi vào ngày chủ nhật thì nên thêm dòng thể hiện ngày thường hoặc ngày nghỉ như mẫu của tôi.
Trong bảng biểu dữ liệu, hạn chế tối đa việc hợp ô, hợp dòng (tôi nói là hạn chế thôi chứ không phải là không sử dụng). Chúng ta nhập ngày đầy đủ 01/01/2018 vào ô và kéo từ ngày 1 đến ngày cuối tháng.
Điều chỉnh hiển thị bằng cách, vào Format, chọn Custom, trong ô Type kéo xuống cuối cùng, gõ chữ dd (chỉ hiện ngày), mm (chỉ hiện tháng), yyyy (chỉ hiện năm), ở đây chúng ta chọn chỉ hiện ngày.
Lập công thức thứ trong tuần từ dòng ngày trong tháng. Có nhiều cách để có thể tạo thứ bằng tiếng Việt hoặc tiếng Anh, nhưng thông thường chúng ta dùng tiếng Anh, sử dụng công thức: =TEXT(F7,”ddd”). F7 là địa chỉ ô ngày 01 đầu tháng. Kéo kết quả đến ngày cuối tháng
Trong 1 số trường hợp công ty quy định ngày nghỉ của NLĐ là ngày khác thì điều kiện công thức của chúng ta phải thay đổi.
Vì theo luật, Điều 110. Nghỉ hằng tuần: 1. Mỗi tuần, người lao động được nghỉ ít nhất 24 giờ liên tục. Trong trường hợp đặc biệt do chu kỳ lao động không thể nghỉ hằng tuần, thì người sử dụng lao động có trách nhiệm bảo đảm cho người lao động được nghỉ tính bình quân 01 tháng ít nhất 04 ngày. 2. Người sử dụng lao động có quyền quyết định sắp xếp ngày nghỉ hằng tuần vào ngày chủ nhật hoặc một ngày cố định khác trong tuần nhưng phải ghi vào nội quy lao động.
Như vậy chúng ta hoàn thành việc thiết lập thứ ngày tháng trong bảng công, đây là phần quan trọng khi thiết lập bảng công để dễ tính toán. Khi tạo bảng biểu dữ liệu, phải suy nghĩ cách thể hiện nào thuận tiện cho việc tính toán và quản lý của chúng ta một cách tốt nhất. Vì nó là công cụ để chúng ta hoàn thành công việc.
PHẦN TIẾP THEO, CHÚNG TA TẠO LẬP CÔNG THỨC CHI TIẾTBẮT ĐẦU TỪ SỐ THỨ TỰ2/ Công thức số thứ tự: Bình thường thì ai cũng nghĩ đơn giản là đánh số TT chỉ cần kéo trỏ chuột thủ công bằng những cách khác nhau để tạo ra DS có STT.
Cách thứ nhất: Với những bảng dữ liệu liên tục không phân biệt phòng ban đánh số liên tục từ 1-cuối cùng chẳng hạn thì chúng ta dùng công thức đơn giản: = Row()-số dòng kế trên . Ví dụ chúng ta đang bắt đầu đếm Số 1 từ dòng 9 thì trong công thức là =Row()-8
Công thức này chỉ áp dụng khi đơn giản và không có điều kiện, không phân biệt phòng ban hay khoảng cách. Vậy trong công việc nhân sự chúng ta luôn có danh sách với đủ các phòng ban, thì chúng ta nên dùng công thức nào?
Cách thứ 2: Công thức đáp ứng điều kiện cho bảng công và lương với nhiều phòng ban =IF(ISTEXT(B10),MAX(A$9:A9)+1,””)
– Nếu cột mã nhân viên là text thì lấy STT lớn nhất bên trên cộng với 1, ngược lại khong có text thì để trống. Lưu ý cột Mã NV, nếu là phòng ban thì để trống hoặc điền số, nếu là nhân viên thì điền mã dạng text. – Nếu chúng ta đang dùng dữ liệu dạng bảng thì công thức tự động lấp đầy bảng – Nếu không thì chúng ta kéo kết quả xuống dòng cuối cùng
Như vậy các bạn sẽ thấy công thức bỏ qua ô trống để điền số TT. Nhưng 1 điều bất cập ở công thức này là, nó không phân biệt được mã trùng, giả sử mã nhân viên của chúng ta là mỗi người chỉ một đúng không ạ? Như vậy việc trùng mã nghĩa là 2 mã trùng nhau chỉ là 1 người (đếm chỉ 1) thì công thức này chưa đáp ứng được yêu cầu. Vậy nên cách thứ 3 chúng ta sử dụng công thức: =IF(AND(ISTEXT(B10),COUNTIF(B$10:B10,B10)=1),MAX(A$9:A9)+1,””)
Vậy công thức này ra kết quả chính xác số lượng nhân sự (lưu ý đảm bảo mỗi nhân viên là 1 mã khác nhau).=IF(AND(ISTEXT(B10),COUNTIF(B$10:B10,B10)=1),MAX(A$9:A9)+1,””)
Countif =1 có nghĩa là chỉ đánh STT cho dòng đầu tiên chứa mã thôi, từ dòng thứ 2 trở đi có cùng mã như vậy thì để trống, không đánh STT Vì dòng thứ 2 chứa mã đó thì kết quả COUNTIF sẽ là số 2.
Tóm lại: Nếu cột B có chứa mã và lần đầu tiên xuất hiện mã đó thì đánh STT, còn lần thứ 2 trở đi thì không đánh STT nữa, đảm bảo mỗi mã chỉ được đánh STT một lần. Có thể hiểu là trong trường hợp có nhiều mã giống nhau (1 nhân viên có trên 1 dòng dữ liệu chấm công) thì việc đánh STT chỉ thực hiện cho dòng đầu tiên thôi, các dòng còn lại không đánh STT.
Phần 2: Hướng dẫn tô màu ngày chủ nhậtĐể thuận tiện cho công việc, trong bảng công chúng ta nên dùng tô màu tự động cho các loại ngày nghỉ chủ nhật, lễ tết… nó cũng là 1 cách ghi nhớ để chúng ta tính toán các loại ngày công theo quy định và không bị sai sót.
Từ công thức như video 1 tôi đã hướng dẫn tạo thứ, ngày tháng, chúng ta lập công thức như sau: =WEEKDAY(F7)=1
Mô tả: Hàm trả về thứ trong tuần của 1 ngày tương ứng. Giá trị trả về là số nguyên từ 1 (chủ nhật) đến 7 (thứ 7). 1 ở đây là chúng ta xác định chủ nhật.
Lập công thức =WEEKDAY(F7)=1 đặt tại 1 ô nào đó trong bảng công.
Chọn vùng dữ liệu cần tô màu (ở đây là bao gồm từ ngày 1- cuối tháng).
Vào Formating- new ruler – trong mục Select ruler (lựa chọn) chúng ta chọn dòng cuối cùng
Sử dụng công thức cho ô, trong muc Format values…. Nhập công thức chúng ta vừa tạo– tiếp theo vào Format chọn màu phù hợp- ok. Ok – như vậy kết quả đã tô màu cho những ngày chủ nhật.
Những công thức này thông thường chúng ta chỉ cần tạo 1 lần từ tháng sau chúng ta chỉ cần thay đổi ngày tháng, thì bảng màu tự động di chuyển. Rất tiện ích phải không ạ?
Công thức tô màu ngày lễ trong năm Tạo list các ngày lễ như mẫu. Nhập công thức kiểm tra vùng dữ liệu tại 1 ô bất kỳ =F$7=IFERROR(VLOOKUP(F$7,BU1:BU10,1,0),””)
Chọn vùng dữ liệu cần tô màu (ở đây là bao gồm từ ngày 1 đến ngày cuối tháng).
Vào Formating- new ruler – trong mục Select ruler (lựa chọn) chúng ta chọn dòng cuối cùng
Vậy là chúng ta đã hoàn thành công việc tô màu cho ngày chủ nhật và ngày lễ
Phần 3: Cách chấm công và kiểm soát các ký tự bảng chấm công Thông thường, đối với mỗi công ty phụ thuộc vào mô hình kinh doanh chúng ta sẽ thiết lập bảng chấm công phù hợp. Đối với công ty thương mại, kinh doanh thì thường chỉ có giờ hành chính nên chúng ta thường sử dụng cách chấm công bằng ký tự chữ vì nó không phát sinh các loại ca làm việc và giờ tăng ca theo ca làm việc.
Còn với sản xuất thì ký tự chữ không đủ đáp ứng khi chấm các ca đêm tăng ca….nên chúng ta nên dùng chấm công bằng số. Mỗi cách chấm công có điểm ưu và nhược riêng.
Tổng hợp công của các ký tự, chúng ta dùng hàm Countif thông thường, cộng các ký tự =COUNTIFS(Table2[@ [Column6]:[Column35]],”HC”)+COUNTIFS(Table2[@[Column6]:[Column35]],”HC/P”)/2+COUNTIFS(Table2[@[Column6]:[Column35]],”HC/K”)/2
Ở đây chúng ta hiểu là những ký tự HC tính là 1, hàm đếm này không phân biệt có bao nhiêu ký tự trong ô, kể cả có 10 ký tự trong 1 ô nó vẫn đếm là 1. Do vậy các ký tự HC/P, hay HC/k thì chúng ta hiểu nó đang là nửa ngày công vậy không thể đếm là 1- tổng hợp công bị sai đúng khong ạ?
Vậy công thức hàm countif sẽ là COUNTIFS(Table2[@ [Column6]:[Column35]],”HC/P”)/2 chia 2 sẽ ra nửa ngày công. Với cách chấm ký tự này, cho phép chúng ta dễ dàng kiểm soát các ngày công
Tôi giả sử có bạn chỉ còn nửa ngày phép, nhưng bạn nghỉ cả ngày thì đương nhiên thứ tự ưu tiên của chúng ta sẽ là: Nghỉ phép, nghỉ bù, nếu hết phép hết ngày nghỉ bù sẽ tính là không lương.
Thực hành trên bảng chấm công này, chúng ta sẽ được kết quả như file mẫu.
Đối với trường hợp chấm bằng số, nếu 1 công ty thương mại hay kinh doanh chủ yếu là ca hành chính thì chỉ cần chấm 1 dòng và kèm thêm bảng theo dõi tăng ca nếu có.
Nhưng nếu có nhiều ca làm việc thì mỗi người nên chấm công 3 dòng để thể hiện các loại ca khác nhau. Với mỗi cách có ưu và nhược điểm khác nhau và phù hợp với các công ty khác nhau.
Đối với công ty có chấm ký tự số và nhiều ca khác nhau: =IF([@Column5]=”C1,C2″,SUMIF($F$9:$AJ$9,”T”,Table24[@ [Column6]:[Column352]]),0)/8
Nếu giá trị E15= ký tự C1,C2 thì tính tổng với điều kiện, Hàng F9, có chữ T, thì cộng dồn kết quả của hàng 15 vào tổng.
Công thức tính giờ công tăng ca: =IF(E15=”TC”,SUMIF($F$9:$AI$9,”T”,F15:AI15),0)
Nếu E25=”TC” thì tính tổng với điều kiện Hàng 9 có chữ T và tính tổng hàng 15 cộng dồn vào tổng.
Như vậy chúng ta hiểu rằng, với mỗi loại ký tự chấm công thì có các công thức tính khác nhau, nhưng dù là cách nào thì các bạn vẫn phải tổng hợp đầy đủ các loại công và ca làm việc.
Ngoài hàm Count thì chúng ta sử dụng hàm if nhiều điều kiện để đáp ứng yêu cầu cho bảng tính. * Để lấy dữ liệu từ bảng công sang bảng lương, ngoài việc sử dụng hàm Vlookup thì chúng ta có thể sử dụng hàm Sumif. * Hàm Vlookup và Sumif là 2 nhóm hàm khác nhau.Một hàm tìm kiếm, một hàm thống kê. Vlookup có thể đem so sánh với Index, hoặc Hlookup, hoặc Lookup, chứ không so sánh với Sumif được. Ngược lại, Sumif thì so sánh với Countif, Sumproduct, Sumifs, Countifs.
Thao tác hàm Sumif như sau:
Lập công thức hàm Sumif chuẩn, copy sang 1 ô khác. Xóa công thức tại ô vừa tạo.
Chọn vùng dữ liệu cần điền, phải đảm bảo dữ liệu hai bảng là giống nhau các cột, số lượng cột tiêu đề cột. Nhập công thức trên thanh công cụ, nhấn Ctrl + Enter.
Chỉ nhấn Ctrl+Enter thôi, không có Shift. Có Shift chỉ dành riêng cho công thức mảng.Nhấn thêm Shift thì ở đầu, cuối công thức sẽ có thêm cặp dấu {}, đây là đặc trưng của công thức mảng.Chẳng hạn công thức dạng =SUM(IF()) thì phải kết thúc bằng Ctrl+Shift+Enter.
=VLOOKUP(Giá trị bạn muốn tra cứu, Vùng chứa giá trị cần tìm kiếm, số cột trong vùng chứa giá trị trả về, Kết quả khớp Chính xác hoặc Kết quả khớp Tương đối – được biểu thị là 0/FALSE hoặc 1/TRUE).
Hoặc cú pháp đơn giản: =VLOOKUP(Trị dò, Bảng dò, Cột kết quả, Kiểu dò)
Nếu sử dụng hàm Vlookup đơn thuần thông thường các kết quả không khớp mã sẽ ra #NA dẫn đến địa chỉ giá trị tổng bị lỗi #NA.
1/ Hàm VLOOKUP nếu không tim thấy thì sẽ trả về lỗi #N/A!. Hàm IFERROR được lồng vào nhằm mục đích trong trường hợp đó thì trả về kết quả theo ý mình. =IFERROR(VLOOKUP(…),Kết quả cần trả về)
Hàm IFERROR được lồng ở bên ngoài các hàm khác, không chỉ VLOOKUP, nói chung là các hàm mà có thể phát sinh lỗi, nhằm mục đích giúp người dùng xử lý tình huống lúc hàm ở trong đó phát sinh lỗi.
Ví dụ: =IFERROR(VLOOKUP(B16,B6:C10,2,TRUE))– sai Phải là: =IFERROR(VLOOKUP(B16,B6:C10,2,TRUE),”I love you!”) Iloveyou có thể là 0.
Nếu hàm VLOOKUP phát sinh lỗi (không tìm thấy giá trị ô B16 trong cột đầu của vùng B6:C10) thì công thức sẽ trả về chuỗi “I love you!”
Ngược lại thì nó trả về giá trị ở cột thứ 2 trong vùng B6:C10 (tức là cột C) tương ứng với vị trí xuất hiện của B16 trong vùng B6:B10
Chỗ TRUE ở VLOOKUP phải dùng FALSE hoặc 0 là chính xác, chứ TRUE hoặc 1 là sai
Tham số thứ 4 của công thức: Nếu tham số này là FALSE hoặc 0 thì trong trường hợp không tìm thấy giá trị nào chính xác bằng giá trị ở tham số thứ nhất thì hàm sẽ phát sinh lỗi #N/A!
Nếu nó là TRUE hoặc 1 thì trong trường hợp không tìm thấy giá trị nào chính xác bằng giá trị ở tham số thứ nhất thì hàm sẽ trả về giá trị tương ứng với giá trị “lớn nhất nhưng bé hơn” giá trị ở tham số thứ nhất (Ví dụ: Mình có bảng phụ cấp theo số năm công tác, nhưng bị cách quãng, chẳng hạn theo thang 1 năm, 3 năm, 5 năm, 10 năm,… Giờ giả sử có 1 nhân viên công tác được 4 năm thì sẽ lấy phụ cấp ở mức nào? Hiển nhiên số 4 không có trong bảng dò nên nó sẽ dò từ trên xuống dưới, thấy có số 3 là số “lớn nhất nhưng bé hơn 4”, vậy là người này sẽ được hưởng mức phụ cấp tương ứng 3 năm công tác. Cũng chính vì cách dò tuần tự từ trên xuống này nên bảng dò buộc phải sắp xếp tăng dần, nếu không sẽ sai kết quả.)
Nếu để FALSE hoặc 0 thì sẽ phát sinh lỗi #N/A vì không có số 4 trong bảng dò.
Trong trường hợp là TRUE (hoặc 1) thì bắt buộc cột thứ nhất của bảng dò phải được sắp xếp tăng, ngược lại thì hàm sẽ sai. Nếu để FALSE hoặc 0 thì sẽ phát sinh lỗi #N/A vì không có số 4 trong bảng dò.
Chỉ dùng TRUE hoặc 1 trong trường hợp mình không dám chắc trị dò có trong bảng dò, chẳng hạn tính phụ cấp theo thâm niên. (Ví dụ: Mình có bảng phụ cấp theo số năm công tác, nhưng bị cách quãng, chẳng hạn theo thang 1 năm, 3 năm, 5 năm, 10 năm,… Giờ giả sử có 1 nhân viên công tác được 4 năm thì sẽ lấy phụ cấp ở mức nào? Hiển nhiên số 4 không có trong bảng dò nên nó sẽ dò từ trên xuống dưới, thấy có số 3 là số “lớn nhất nhưng bé hơn 4”, vậy là người này sẽ được hưởng mức phụ cấp tương ứng 3 năm công tác. Cũng chính vì cách dò tuần tự từ trên xuống này nên bảng dò buộc phải sắp xếp tăng dần, nếu không sẽ sai kết quả.)
Nó là cái thám số thứ 2 của hàm IFERROR chứ không phải là tham số của hàm VLOOKUP, nó nằm ngoài dấu ngoặc .
Cú pháp: =SUMPRODUCT(array1,array2,array3….)
Cách thức hoạt động: Hàm sẽ nhân lần lượt các phần tử của các mảng array1, array2,… theo thứ tự, sau đó cộng các tích đó lại với nhau để ra kết quả.Tuy nhiên, thông thường người ta sử dụng dưới dạng =SUMPRODUCT((ĐK1)*(ĐK2)*(ĐK3)*…)
Với cách thức trên, hàm sẽ kiểm tra lần lượt các điều kiện ĐK1, ĐK2,… và trả về mỗi điều kiện thành một mảng các giá trị TRUE, FALSE hay 1, 0 rồi nhân các mảng này lại với nhau và cộng các tích thu được.
Hàm sẽ kiểm tra trong Vùng1, nếu giá trị nào thỏa mãn Điều kiện thì sẽ cộng dồn giá trị tương ứng ở Vùng 2 vào tổng. Trong trường hợp Vùng 2 và Vùng 1 trùng nhau thì mình có thể bỏ qua tham số thứ 3, nó chỉ còn =SUMIF(Vùng,Điều kiện) =SUMIF(MaNV_BM,$B53,Com_BM), hàm sẽ kiểm tra trng vùng MaNV_BM, nếu ô nào có giá trị bằng ô $B53 thì sẽ lấy giá trị tương ứng ở vùng Com_BM để cộng dồn vào tổng.
. Trong trường hợp Vùng 2 và Vùng 1 trùng nhau
Có nghĩa là vùng để tính tổng cũng chính là vùng để kiểm tra điều kiện
Tính tổng theo nhiều điều kiện =SUMIFS(Vùng_tính_tổng,Vùng_1,ĐK_1,Vùng_2,ĐK_2,…)
Ý nghĩa của nó là kiểm tra trên các vùng, nếu anh nào có giá trị ở Vùng_1 thòa mãn ĐK_1, có giá trị ở Vùng_2 thỏa mãn ĐK_2, thỏa mãn đồng thời, thì lấy giá trị tương ứng trong Vùng_tính_tổng để cộng dồn vào kết quả.
Nếu có 2 điều kiện thì phải cả 2 đứa cùng thỏa mãn, có 3 điều kiện thì cả 3 đứa cùng thỏa mãn mới tính tổng.
Cách thức thực hiện: Chọn vùng cần dán dữ liệu Nhập công thức trong thanh công cụ Kết thúc bằng Ctrl Shift Enter Quét chọn vùng S8: AT15 bên sheet lương, sử dụng công thức sau: =SUMIF(BCC!$B$10:$B$18,$B8,BCC!AK$10:AK$18) và nhấn Ctrl+Enter. Để dùng cho cả mảng: Quét chọn vùng, copy công thức vào thanh công thức, nhấn Ctrl+enter. Chẳng hạn “Quét chọn vùng S8: AT15” thì quét từ ô S8 đến ô AT15, để đảm bảo ô S8 đang là ô sẽ nhập công thức vào.
Dùng Sumif để lấy dữ liệu chấm công từ bảng công
Nguồn: fb/phan.ht.771
Dài quá phải không anh chỉ em? Đây là clip minh họa cho bài:
Phải nói là đọc hơi khó hiểu. Nhưng chúng ta cứ đọc cho hết. Phần 1 của bài ở đây: Nhân sự hay dùng những hàm excel gì ? ( http://blognhansu.net.vn/?p=7954 )
Bài viết được Google đề xuất đọc kèm:
Hàm, Công Thức Excel Sử Dụng Trong Kế Toán Tiền Lương, Nhân Sự
Bạn còn đang là sinh viên, hay bạn đã ra trường và đang đi xin việc, hay bạn đang làm nhân viên kế toán tiền lương, khi gặp phải câu hỏi: Khi làm kế toán tiền lương trên excel thường sử dụng công thức nào? , hẳn nhiều bạn sẽ rất bối rối.
Trong bài viết này, chúng tôi sẽ hướng dẫn cho các bạn cách học excel tại nhà và có thể nắm được cách sử dụng các hàm thường dùng trong công việc kế toán tiền lương.
1. Khi làm kế toán tiền lương, bạn phải nắm được danh sách nhân viên của công ty, thông tin về hợp đồng lao động của mỗi người. Vì vậy các hàm truy vấn, tìm kiếm sẽ thường sử dụng để tra cứu thông tin nhân viên có trong bảng danh sách nhân viên.
Hàm VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]): hàm truy vấn, tìm kiếm thông dụng nhất. Tuy nhiên đối tượng tìm kiếm (lookup_value = Mã nhân viên / Tên nhân viên) phải nằm trong cột đầu tiên bên trái vùng bảng tìm kiếm (table_array = Bảng Danh sách nhân viên)
Hàm INDEX+MATCH: kết hợp 2 hàm này với nhau để truy vấn, tìm kiếm thông tin. Về tính năng thì 2 hàm này có hiệu quả cao hơn hàm vlookup, không bị giới hạn cấu trúc bảng tìm kiếm như đã nói ở trên, để tìm hiểu cách dùng hàm index+match thế nào mời các bạn tham khảo bài viết sau:
Tìm hiểu ngay: lớp học Excel kế toán 1 thầy 1 trò
Hàm DATE(year,month,day): tạo ra 1 giá trị ngày tháng xác định rõ bởi năm, tháng, ngày
Tham khảo bài viết: Ứng dụng của hàm DATE: khi muốn xác định ngày đầu và ngày cuối của các tháng trong năm.
Hàm YEAR(serial_number) : theo dõi số năm của 1 giá trị ngày tháng
Hàm MONTH(serial_number): theo dõi số tháng của 1 giá trị ngày tháng
Hàm DAY(serial_number): theo dõi số ngày của 1 giá trị ngày tháng
Hàm HOUR(serial_number): theo dõi số giờ của 1 giá trị thời gian
Hàm MIN(serial_number): theo dõi số phút của 1 giá trị thời gian
COUNTIF : Đếm số ký hiệu công trong bảng chấm công (theo 1 điều kiện duy nhất)
Tham khảo bài viết: Hàm, công thức Excel sử dụng trong kế toán tiền lương, nhân sự: COUNTIF
COUNTIFS : Đếm số ký hiệu công trong bảng chấm công (theo nhiều điều kiện cùng lúc)
SUMIF : Tính tổng số giờ công trong bảng chấm công (theo 1 điều kiện duy nhất)
SUMIFS : Tính tổng số giờ công trong bảng chấm công (theo nhiều điều kiện cùng lúc)
4. Để tính lương cho nhân viên, thường sử dụng các công thức sau:
IF : Cần biện luận logic trong việc tính toán trong 1 số trường hợp (Nếu theo yêu cầu 1 thì kết quả là gì, nếu không thỏa mãn yêu cầu 1 thì kết quả là gì)
Hàm AND / OR : Khi cần biện luận nhiều điều kiện kết hợp nhau thì sẽ sử dụng hàm And/Or để ghép nối các điều kiện trong hàm IF
Hàm LOOKUP / VLOOKUP : Truy vấn tới các nội dung: Thông tin tính lương, thông tin nhân viên, kết quả chấm công…
Hàm SUM / SUMIF / SUMIFS : Tính tổng (có điều kiện hoặc không) các nội dung tiền lương.
Để nắm được những kiến thức và cách ứng dụng Excel trong công việc hành chính-nhân sự, mời bạn tham gia khóa học Hành chính nhân sự từ A-Z của Học Excel Online. Khóa học này sẽ hướng dẫn bạn cách sử dụng những kỹ năng tin học văn phòng căn bản, cách áp dụng word, excel vào từng phần việc cụ thể của Hành chính nhân sự. Ngoài ra khi tham dự khóa học này, bạn còn được tặng những chương trình, tài liệu hấp dẫn, hữu ích để sử dụng ngay trong công việc.
Hàm Nhân Trong Excel. Cách Nhân Các Số Và Ô Trong Excel
Hàm nhân trong Excel (PRODUCT)
Nếu bạn cần nhân nhiều ô hoặc phạm vi, phương pháp nhanh nhất sẽ là sử dụng hàm PRODUCT:
PRODUCT (number1, [number2],…)
Trong đó number1 , number2 , v.v. là các số, ô hoặc dải ô mà bạn muốn nhân.
Ví dụ: Để nhân các giá trị trong các ô A2, B2 và C2, hãy sử dụng công thức này:
=PRODUCT(A2:C2)
Để nhân các số trong các ô từ A2 đến C2, sau đó nhân kết quả với 3, sử dụng hàm excel này:
=PRODUCT(A2:C2,3)
Trong trường hợp bạn cần nhân hai cột hoặc hàng rồi cộng kết quả của các phép tính riêng lẻ. Hãy sử dụng hàm SUMPRODVEL để nhân các ô và tính tổng sản phẩm.
Bạn có giá ở cột B, số lượng ở cột C, bạn muốn tính tổng giá trị bán hàng. Trong Excel, tất cả các phép tính này được thực hiện với một công thức hàm nhân duy nhất:
=SUMPRODUCT(B2:B5,C2:C5)
Nếu bạn muốn, bạn có thể kiểm tra kết quả với phép tính này:
=(B2*C2)+(B3*C3)+(B4*C4)+(B5*C5)
Trong trường hợp bạn muốn nhân hai cột số, sau đó thực hiện các phép tính tiếp theo với kết quả, hãy thực hiện phép nhân trong công thức mảng.
Trong tập dữ liệu trên, một cách khác để tính tổng giá trị bán hàng là:
=SUM(B2:B5*C2:C5)
Hàm nhân tổng số trong Excel này tương đương với SUMPRODVEL và trả về chính xác cùng một kết quả.
Lấy ví dụ xa hơn, hãy tìm mức trung bình của doanh số bán hàng. Đối với điều này, chỉ cần sử dụng hàm AVERAGE thay vì SUM:
=AVERAGE(B2:B5*C2:C5)
Để tìm doanh số bán hàng lớn nhất và nhỏ nhất, hãy sử dụng hàm MAX và hàm MIN tương ứng:
=MAX(B2:B5*C2:C5)
=MIN(B2:B5*C2:C5)
Để hoàn thành công thức mảng đúng cách, hãy nhớ nhấn Ctrl + Shift + Enter kết hợp thay vì chỉ Enter. Ngay sau khi bạn thực hiện hàm nhân này, Excel sẽ đặt công thức trong {dấu ngoặc nhọn}, cho biết đó là công thức mảng.
Các kết quả có thể trông giống như sau:
Ngoài hàm nhân cách dễ nhất để thực hiện phép nhân trong Excel là sử dụng biểu tượng (*). Với cách tiếp cận này, bạn có thể nhanh chóng nhân các số, ô, toàn bộ cột và hàng.
Cách nhân số trong Excel
Để tạo công thức nhân đơn giản nhất trong Excel, hãy nhập dấu bằng (=) vào một ô, sau đó nhập số đầu tiên bạn muốn nhân, tiếp theo là dấu (*), tiếp theo là số thứ hai và nhấn phím Enter để tính công thức.
Ví dụ: Để nhân 2 với 5, bạn nhập biểu thức này vào một ô (không có khoảng trắng): =2*5
Excel cho phép thực hiện các phép toán số học khác nhau trong một công thức mà không cần đến hàm nhân. Chỉ cần nhớ thứ tự của các phép tính.
Để nhân hai ô trong Excel, hãy sử dụng công thức nhân như trong ví dụ trên nhưng cung cấp tham chiếu ô thay vì số.
Ví dụ: Để nhân giá trị trong ô A2 với giá trị trong ô B2, hãy nhập biểu thức: =A2*B2
Để nhân nhiều ô, hãy bao gồm nhiều tham chiếu ô hơn trong công thức, được phân tách bằng dấu nhân. Ví dụ: =A2*B2*C2
Để nhân hai cột trong Excel, hãy viết công thức hàm nhân cho ô trên cùng, ví dụ:
=A2*B2
Sau khi bạn đã đặt công thức vào ô đầu tiên (trong ví dụ này là C2). Hãy bấm đúp vào ô vuông nhỏ màu xanh lục ở góc dưới bên phải của ô để sao chép công thức xuống cột, lên đến ô cuối cùng có dữ liệu:
Nhân các hàng trong Excel ít phổ biến hơn, ngoài hàm nhân cũng có một giải pháp đơn giản cho nó. Để nhân hai hàng trong Excel, chỉ cần thực hiện như sau:
Chèn công thức nhân vào ô đầu tiên (ngoài cùng bên trái).
Trong ví dụ này, chúng ta nhân các giá trị trong hàng 1 với các giá trị trong hàng 2 bắt đầu bằng cột B. Vì vậy công thức như sau: =B1*B2
Chọn ô công thức và di con trỏ chuột lên một hình vuông nhỏ ở góc dưới bên phải cho đến khi nó thay đổi thành hình chữ thập màu đen dày.
Kéo chữ thập màu đen đó sang phải qua các ô mà bạn muốn sao chép công thức.
Cách nhân với phần trăm trong Excel
Để nhân tỷ lệ phần trăm trong Excel, hãy thực hiện hàm công thức nhân theo cách sau. Nhập dấu bằng, theo sau là số hoặc ô, tiếp theo là dấu nhân (*), sau đó là tỷ lệ phần trăm.
Nói cách khác, hãy tạo một công thức tương tự như sau:
Để nhân một số với phần trăm: =50*10%Để nhân một ô với tỷ lệ phần trăm: =A1*10%
Thay vì phần trăm, bạn có thể nhân với một số thập phân tương ứng.
Để nhân một cột số với cùng một số, hãy tiến hành các bước sau:
Nhập số cần nhân vào một số ô, chẳng hạn như trong A2. Viết công thức hàm nhân cho ô trên cùng trong cột.
Giả sử các số được nhân vào cột C, bắt đầu ở hàng 2, bạn đặt công thức sau vào D2:
=C2*$A$2
Điều quan trọng là bạn phải khóa tọa độ cột và hàng của ô với số cần nhân để ngăn tham chiếu thay đổi khi bạn sao chép công thức sang các ô khác. Đối với điều này, hãy nhập biểu tượng $ trước ký tự cột và số hàng để tạo tham chiếu tuyệt đối ($ A $ 2). Hoặc, nhấn vào tham chiếu và nhấn phím F4 để thay đổi nó thành tuyệt đối.
Bấm đúp vào ô điều khiển điền vào ô công thức (D2) để sao chép công thức xuống cột.
C2 (tham chiếu tương đối) thay đổi thành C3 khi công thức được sao chép sang hàng 3. Trong khi đó $ A $ 2 (tham chiếu tuyệt đối) không hề thay đổi
Bạn đang đọc nội dung bài viết Nhân Sự Hay Dùng Những Hàm Excel Gì – Excel Và Công Tác Quản Trị Nhân Sự? trên website Beiqthatgioi.com. Hy vọng một phần nào đó những thông tin mà chúng tôi đã cung cấp là rất hữu ích với bạn. Nếu nội dung bài viết 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. Chúc bạn một ngày tốt lành!