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

Thứ sáu - 03/01/2014 23: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:
 

NoNo Phòng banTên nhân viênPhòng Ban
11Nguyễn Văn AACC
22Phan văn BACC
33Lê Thị CACC
44Trần văn DACC
51Phạm Văn EHRT
62Cao Minh FHRT
73Ngô Hồng HHRT
81Mai Thục IMAT
92Nguyễn Khôi JMAT
103Phùng Thị MMAT
114Thái Thị Thùy NMAT
125Công Tôn OMAT
136Hoàng Minh PMAT


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:

 

 

NoNo Phòng banTên nhân viênPhòng BanStatus
11Nguyễn Văn AACCCòn làm việc
22Phan văn BACCCòn làm việc
33Lê Thị CACCCòn làm việc
44Trần văn DACCCòn làm việc
51Phạm Văn EHRTCòn làm việc
62Cao Minh FHRTCòn làm việc
73Ngô Hồng HHRTCòn làm việc
80Mai Thục IMATnghỉ việc
90Nguyễn Khôi JMATnghỉ việc
100Phùng Thị MMATnghỉ việc
110Thái Thị Thùy NMATnghỉ việc
121Công Tôn OMATCòn làm việc
132Hoàng Minh PMATCòn làm việc
140Hoàng Minh QMATnghỉ việc
150Hoàng Minh RMATnghỉ việc
163Hoàng Minh SMATCò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ả bài viết: 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à: 10 trong 2 đánh giá

Xếp hạng: 5 - 2 phiếu bầu
Click để đánh giá bài viết

  Ý kiến bạn đọc

Thống kê
  • Đang truy cập40
  • Máy chủ tìm kiếm3
  • Khách viếng thăm37
  • Hôm nay4,232
  • Tháng hiện tại149,524
  • Tổng lượt truy cập3,603,163
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