Công thức đánh số tự động theo vùng trong excel

Thứ sáu - 03/01/2014 11:00
Ví thử như bạn muốn thống kê số nhân viên cho từng phòng ban thì phải làm như thế nào? bài hôm nay sẽ hướng dẫn bạn đánh số tự động liên tục trong một khoản hoặc một dãy ô nhất định,

Ngoài cách đánh số tự động liên tục cho cả danh sách trong excel mà chúng ta đã học ở bài trước thông qua đó ta sẽ biết được danh sách có bao nhiêu dòng hàng hoặc nếu bạn lập danh sách liệt kê toàn bộ nhân viên trong công ty thì ta sẽ biết được tổng số nhân viên trong công ty đó mà không cần phải dùng hàm count hoặc hàm Countif,

Ví thử như bạn muốn thống kê số nhân viên cho từng phòng ban thì phải làm như thế nào? bài hôm nay sẽ hướng dẫn bạn đánh số tự động liên tục trong một khoản hoặc một dãy ô nhất định,

Ví dụ: ta có danh sách nhân viên trong công ty như sau:
 

No No Phòng ban Tên nhân viên Phòng Ban
1 1 Nguyễn Văn A ACC
2 2 Phan văn B ACC
3 3 Lê Thị C ACC
4 4 Trần văn D ACC
5 1 Phạm Văn E HRT
6 2 Cao Minh F HRT
7 3 Ngô Hồng H HRT
8 1 Mai Thục I MAT
9 2 Nguyễn Khôi J MAT
10 3 Phùng Thị M MAT
11 4 Thái Thị Thùy N MAT
12 5 Công Tôn O MAT
13 6 Hoàng Minh P MAT


Cột B (No phòng ban) là dùng để đánh số nhân viên của từng phòng ban đó. Nếu các nhân viên cùng một phòng ban thì phải đánh số từ 1 cho đến nhân viên cuối cùng của phòng ban đó, khi chuyển sang phòng ban mới thì sẽ đánh số lại từ đầu.

Công cụ sử dụng vẫn là Hàm IFHàm And

=IF(A1="No",1,IF(AND(A1<>"No",D1<>D2),1,B1+1))

Lưu ý, để đảm bảo việc đánh số tự động chuẩn xác, sau khi cập nhật danh sách, chúng ta phải sort cột phòng ban để đảm bảo các nhân viên cùng một phòng ban thì luôn liền kề nhau.

Như vậy là bạn đã học được cách đánh số tự động cục bộ một vùng trong excel rồi, tuy nhiên, nếu như một nhân viên nào đó trong phòng ban bị nghỉ việc mà chúng ta không muốn xóa nó đi, nhưng lại muốn excel đánh số thứ tự cho những nhân viên còn làm việc tại công ty thi bạn làm thế nào?

Để làm được việc này, chúng ta phải chèn thêm một cột nữa gọi là tình trạng (Status) ở cột này chúng ta nhập 2 điều kiện là còn làm việc và nghỉ việc, Nếu nhân viên nào có giá trị là Nghỉ việc thì sẽ không đánh số cho nó nữa.

Ta có danh sách sau:

 

 

No No Phòng ban Tên nhân viên Phòng Ban Status
1 1 Nguyễn Văn A ACC Còn làm việc
2 2 Phan văn B ACC Còn làm việc
3 3 Lê Thị C ACC Còn làm việc
4 4 Trần văn D ACC Còn làm việc
5 1 Phạm Văn E HRT Còn làm việc
6 2 Cao Minh F HRT Còn làm việc
7 3 Ngô Hồng H HRT Còn làm việc
8 0 Mai Thục I MAT nghỉ việc
9 0 Nguyễn Khôi J MAT nghỉ việc
10 0 Phùng Thị M MAT nghỉ việc
11 0 Thái Thị Thùy N MAT nghỉ việc
12 1 Công Tôn O MAT Còn làm việc
13 2 Hoàng Minh P MAT Còn làm việc
14 0 Hoàng Minh Q MAT nghỉ việc
15 0 Hoàng Minh R MAT nghỉ việc
16 3 Hoàng Minh S MAT Còn làm việc


Chú ý: Để tránh trường hợp người nhập liệu tại ô Status không thống nhất dẫn đến công thức chúng ta thiết lập không hoạt động được, chúng ta nên dùng Data Validation để yêu cầu Excel cho nhập 2 điều kiện là" Còn làm việc" và "Nghỉ việc", Tương tự như vậy cột phòng ban cũng cần được Thiết lập Data Validation cho nó nữa.

Ở bài trước, chúng ta dùng Hàm Max để lấy giá trị lớn nhất của những ô trước đó trong cùng một cột, tuy nhiên ở bài này, chúng ta không dùng được hàm Max nữa vì nó lấy giá trị lớn nhất cho cả cột, trong khi ở đây chúng ta yêu cầu nó lấy giá trị lớn nhất cho một vùng dữ liệu mà thôi.

Để giải quyết vấn đề trên ta dùng Hàm Countifsđể đếm những số lượng người trong phòng ban thỏa điều kiện là "No phòng ban" lớn hơn không và nhân viên đó phải thuộc phòng ban đó và sau đó cộng kết quả đếm được với 1 (đơn vị) để lũy tiến cho số thứ tự.

Ta có công thức tổng quát như sau:

=IF(E2="nghỉ việc",0,IF(AND(E1="Status",E2<>"nghỉ việc"),1,IF(AND(E2<>"nghỉ việc",E1<>"status",D2<>D1),1,IF(AND(E2<>"nghỉ việc",E1<>"status",D2=D1),COUNTIFS($B$1:B1,">"&0,$D$1:D1,D2)+1))))

Chúc bạn tìm thấy niềm vui qua trang tính excel

 

 

 

 

Tác giả: Minh Phú

Chú ý: Việc đăng lại bài viết trên ở website hoặc các phương tiện truyền thông khác mà không ghi rõ nguồn http://aneedz.com là vi phạm bản quyền

Tổng số điểm của bài viết là: 20 trong 4 đánh giá

Xếp hạng: 5 - 4 phiếu bầu
Click để đánh giá bài viết
Thống kê
  • Đang truy cập17
  • Hôm nay2,239
  • Tháng hiện tại5,691
  • Tổng lượt truy cập18,402,726
Bạn đã không sử dụng Site, Bấm vào đây để duy trì trạng thái đăng nhập. Thời gian chờ: 60 giây
Gửi phản hồi