Giáo trình Excel 2021 toàn tập [phần cơ bản-2]

Bài 18: Tự động đánh số hàng

Không giống như các chương trình Microsoft Office khác, Excel không cung cấp một nút đánh số dữ liệu tự động. Thế nhưng, bạn dễ dàng thêm số hàng dữ liệu theo thứ tự bằng cách dùng hàm ROW trong Microsoft Excel.

Mẹo: Nếu đang tìm hệ thống tự động đánh số nâng cao hơn cho dữ liệu và có cài Access trên máy tính, bạn có thể nhập dữ liệu Excel vào database Access. Trong cơ sở dữ liệu Access, bạn có thể tạo một trường tự động đánh số duy nhất khi nhập một báo cao mới vào bảng.

Điền một chuỗi số vào cột

1. Chọn ô đầu tiên trong phạm vi bạn muốn điền.
2. Gõ giá trị bắt đầu chuỗi đó.
3. Gõ giá trị trong ô tiếp theo để thiết lập mẫu.

Mẹo: Ví dụ, nếu muốn chuỗi số 1, 2, 3, 4, 5…, gõ 1 và 2 vào hai ô đầu

tiên. Nếu muốn seri 2, 4, 6, 8…, gõ 2 và 4.

• Chọn ô chứa giá trị bắt đầu.

Lưu ý: Trong Excel 2013 trở lên, nút Quick Analysis mặc định được hiển thị khi bạn chọn nhiều hơn một ô chứa dữ liệu. Bạn có thể bỏ qua nút bấm để hoàn tất quá trình này.

• Kéo icon điền dữ liệu qua phạm vi bạn muốn.

Lưu ý: Khi kéo thanh điền dữ liệu qua từng ô. Excel hiện cửa sổ xem trước dữ liệu. Nếu muốn một mẫu khác, kéo thanh điền bằng cách giữ nút click chuột phải, rồi chọn một mẫu.

Để điền số theo thứ tự tăng dần, kéo xuống dưới hoặc sang phải. Để điền số theo thứ tự giảm dần, kéo lên hoặc sang trái.

Mẹo: Nếu không thấy thanh điền dữ liệu, bạn cần hiển thị nó trước tiên.

Lưu ý: Những số này không được update tự động khi bạn thêm, di chuyển hay xóa hàng. Bạn có thể tự tay cập nhật chuỗi số bằng cách chọn 2 số theo đúng thứ tự, rồi kéo thanh điền vào cuối phạm vi cần đánh số.

Dùng hàm ROW để đánh số hàng

1. Trong ô đầu tiên của phạm vi bạn muốn đánh số, gõ =ROW(A1).

Hàm ROW trả về số hàng bạn tham chiếu tới. Ví dụ, =ROW(A1) trả về số 1.

• Kéo tới phạm vi bạn muốn đánh số.
  • Những số này được update khi bạn phân loại chúng theo dữ liệu. Chuỗi số có thể bị gián đoạn nếu bạn thêm, di chuyển hoặc xóa hàng. Bạn có thể update số bằng cách chọn 2 số theo đúng thứ tự, rồi kéo thanh điền số tới cuối phạm vi mong muốn.

• Nếu đang dùng hàm ROW và muốn chèn số tự động khi thêm hàng dữ liệu mới, hãy đưa phạm vi dữ liệu đó vào bảng Excel. Phần mềm sẽ đánh số theo thứ tự toàn bộ hàng được thêm vào cuối bảng.

Để nhập mã số theo thứ tự riêng, như số đơn mua hàng, bạn có thể dùng hàm ROW cùng với hàm TEXT. Ví dụ, để bắt đầu một danh sách được đánh số theo 000-001, bạn nhập công

thức =TEXT(ROW(A1),”000-000″) trong ô đầu tiên của phạm vi muốn đánh số, rồi kéo thanh điền dữ liệu vào cuối phạm vi.

Hiển thị hoặc ẩn icon điền dữ liệu

Mặc định, icon sẽ hiển thị nhưng bạn có thể tắt/bật nó.

1. Trong Excel 2010 trở lên, click tab File, rồi click Options.

Trong Excel 2007, click nút Microsoft Office  > click Excel Options.

Trong danh mục Advanced > Editing options, chọn hoặc bỏ tích

ô Enable fill handle and cell drag-and-drop để hiện/ẩn icon điền dữ liệu.

Lưu ý: Để ngăn chặn việc thay thế dữ liệu hiện tại khi bạn kéo điều khiển tương ứng, đảm bảo đã tích ô Alert before overwriting cells. Nếu không muốn Excel hiển thị thông báo ghi đè ô, bạn có thể bỏ tích ô trên.

Bài 19: Tính toán sự chênh lệch giữa 2 ngày

Dùng hàm DATEDIF khi bạn muốn tính sự chênh lệch giữa 2 ngày. Trước tiên, đặt ngày bắt đầu vào một ô, ngày kết thúc vào ô khác. Sau đó, nhập công thức giống như một trong số tác vụ dưới đây.

Chênh lệch trong ngày

Ví dụ, ngày bắt đầu trong ô D9, ngày kết thúc trong E9. Công thức nằm ở ô F9. d trả về tổng số ngày giữa hai ngày này.

Chênh lệch trong tuần

Ở ví dụ này, ngày bắt đầu nằm trong ô D13, ngày kết thúc ở E13. d trả về số ngày. Thế nhưng, lưu ý /7 ở cuối. Nó chia số ngày cho 7 bởi một tuần có 7 ngày. Lưu ý kết quả này cũng cần được định dạng là một số. Nhấn CTRL+1. Sau đó, click Number > Decimal places: 2.

Chênh lệch trong tháng

Ví dụ, ngày bắt đầu nằm ở ô D5, ngày kết thúc ở ô E5. Trong công thức này, m trả về số tháng đầy đủ giữa 2 ngày.

Chênh lệch trong năm

Ở ví dụ này, ngày bắt đầu nằm ở ô D2, ngày kết thúc ở ô E2. y trả về tổng số năm giữa 2 ngày.

Tính toán thâm niên trong năm, tháng và ngày tích lũy

Bạn cũng có thể tính thâm niên hay thời gian sử dụng dịch vụ của ai đó. Kết quả có thể là 2 năm, 4 tháng, 5 ngày.

1.   Dùng DATEDIF để tìm tổng số năm

Trong ví dụ này, ngày bắt đầu nằm ở ô D17, ngày kết thúc ở E17. y trả về tổng số năm giữa 2 ngày.

2.   Dùng DATEDIF cùng ym để tìm tháng

Trong ô khác, dùng công thức DATEDIF với tham số ym. Ym trả về số tháng còn lại ở năm vừa qua.

3.   Dùng công thức khác để tìm ngày

Giờ chúng ta cần tìm số ngày còn lại bằng cách viết một kiểu công thức khác như hình minh họa trên. Công thức này trừ ngày đầu tiên của tháng cuối (5/1/2016) từ ngày kết thúc ban đầu trong ô E17 (5/6/2016). Đây là cách nó thực hiện: Đầu tiên, hàm DATE tạo ngày, 5/1/2016. Nó dùng năm và tháng trong ô E17. Tiếp theo, 1 đại diện cho ngày đầu tiên của tháng. Kết quả cho hàm DATE là 5/1/2016. Sau đó, công thức trừ đi ngày cuối ban đầu ở ô E17. Nghĩa là 5/6/2016 trừ 5/1/2016 là 5 ngày.

4.   Tùy chọn: Kết hợp 3 công thức thành một

Bạn có thể đặt toàn bộ 3 phép tính trong một ô như ví dụ trên bằng cách dùng dấu &, dấu trích dẫn và text. Công thức này khá dài. Mẹo: Nhấn ALT+ENTER để ngắt dòng trong công thức. Điều này giúp nó dễ đọc hơn. Ngoài ra, nhấn CTRL+SHIFT+U nếu bạn không thể thấy toàn bộ công thức.

Cảnh báo: Bạn không nên dùng đối số “md” của DATEDIF bởi nó có thể cho kết quả tính toán không chính xác.

Phép tính ngày và thời gian khác

Tính từ ngày hôm nay tới ngày khác

Như đã thấy ở trên, hàm DATEDIF tính sự khác biệt giữa ngày bắt đầu và ngày kết thúc. Tuy nhiên, thay vì nhập ngày cụ thể, bạn cũng có thể dùng hàm TODAY() bên trong công thức này. Khi dùng hàm

TODAY(), Excel sử dụng ngày hiện tại của máy tính cho ngày. Nhớ rằng, điều này sẽ thay đổi khi bạn mở lại file ở lần tới.

Tính toán ngày làm việc có hoặc không có ngày lễ

Dùng hàm NETWORKDAYS.INTL khi bạn muốn tính toàn số ngày làm việc giữa hai ngày. Bạn cũng có thể loại bỏ ngày cuối tuần và ngày lễ.

Trước khi bắt đầu: Chọn ngày lễ muốn bỏ qua bằng cách nhập danh sách ngày đó vào vùng hay bảng tính riêng. Đặt từng ngày lễ vào ô riêng. Sau đó, chọn những ô đó, chọn Formulas > Define Name. Đặt tên phạm vi MyHolidays > click OK. Sau đó tạo công thức theo những bước sau:

1. Gõ ngày bắt đầu và ngày kết thúc

Trong ví dụ này, ngày bắt đầu nằm ở ô D53, ngày kết thúc ở ô E53.

• Trong ô khác, nhập công thức như sau:

Gõ công thức như ví dụ trên. 1 trong công thức này được thiết lập là ngày cuối tuần (thứ 7 & Chủ Nhật), và loại bỏ chúng khỏi kết quả tính tổng.

Lưu ý: Excel 2007 không có hàm NETWORKDAYS.INTL. Tuy nhiên, nó có NETWORKDAYS. Ví dụ ở trên nếu thực hiện trong Excel 2007, công thức sẽ như sau: =NETWORKDAYS(D53,E53). Bạn không chỉ định 1 bởi NETWORKDAYS đã giả định ngày cuối tuần vào Thứ 7 và Chủ Nhật.

• Nếu cần, thay đổi 1

Nếu thứ 7 và Chủ Nhật không phải ngày cuối tuần của bạn, hãy thay đổi 1 sang số khác từ danh sách IntelliSense. Ví dụ, 2 thiết lập Chủ Nhật & thứ Hai làm ngày cuối tuần.

Nếu đang dùng Excel 2007, bỏ qua bước này.

Hàm NETWORKDAYS của Excel 2007 luôn giả định cuối tuần vào thứ 7 & Chủ Nhật.

• Nhập tên phạm vi ngày lễ

Nếu đã tạo một tên phạm vi ngày lễ trong phần “Trước khi bắt đầu” ở trên, hãy nhập nó vào phần cuối cùng. Nếu không có ngày lễ, bạn có thể bỏ dấu phẩy và MyHolidays. Nếu dùng Excel 2007, ví dụ trên sẽ như sau:

=NETWORKDAYS(D53,E53,MyHolidays).

Mẹo: Nếu không muốn tham chiếu tới tên phạm vi ngày lễ, bạn có thể nhập một phạm vi thay thế, như D35:E:39. Hoặc bạn có thể nhập từng ngày lễ vào trong công thức. Ví dụ, nếu ngày lễ rơi vào 1/1 và 2/1 năm 2016, bạn sẽ nhập chúng như sau:

=NETWORKDAYS.INTL(D53,E53,1,{“1/1/2016″,”1/2/2016”}).

Trong Excel 2007, nó sẽ trông như thế

này =NETWORKDAYS(D53,E53,{“1/1/2016″,”1/2/2016”})

Tính thời gian đã trôi qua

Bạn có thể tính thời gian đã trôi qua trên Excel giống như một phép tính trừ đơn giản. Đầu tiên, nhập thời gian bắt đầu vào một ô và ngày kết thúc vào ô khác. Đảm bảo nhập thời gian đầy đủ, bao gồm giờ, phút và khoảng cách trước AM hoặc PM. Các bước thực hiện như sau:

1. Nhập ngày bắt đầu và kết thúc

Ở ví dụ này, thời gian bắt đầu ở ô D80, thời gian kết thúc ở E80.

• Thiết lập thời gian theo định dạng giờ/phút sáng/chiều

Chọn cả hai ngày và nhấn CTRL + 1 hoặc     + 1 trên Mac. Đảm bảo đã chọn Custom > h:mm AM/PM.

• Thực hiện phép trừ thời gian

Trong một ô khác, lấy dữ liệu ở ô thời gian kết thúc trừ đi ô thời gian bắt đầu.

• Thiết lập định dạng giờ:phút

Nhấn CTRL+1 hoặc     +1 trên Mac. Chọn Custom > h:mm để kết quả không bao gồm AM và PM.

Tính thời gian đã qua giữa hai ngày và giờ

Bạn cũng chỉ cần thực hiện phép trừ tương tự như trên. Tuy nhiên, bạn phải áp dụng định dạng cho từng ô để đảm bảo Excel trả về kết quả mong muốn.

1. Nhập đầy đủ 2 ngày và giờ

Nhập đầy đủ ngày/giờ bắt đầu vào một ô. Tương tự ở ô khác, nhập ngày/giờ kết thúc. Mỗi ô cần có đủ tháng, ngày, năm, giờ, phút và khoảng cách trước AM hoặc PM.

• Đặt định dạng 3/14/12 1:30 PM

Chọn cả hai ô, rồi nhấn CTRL + 1 hoặc    + 1 trên Mac. Sau đó,

chọn Date > 3/14/12 1:30 PM. Đây không phải ngày bạn chọn, nó chỉ là một ví dụ về cách định dạng. Lưu ý rằng ở phiên bản trước Excel 2016, định dạng này có thể khác mẫu, ví dụ: 3/14/01 1:30 PM.

• Trừ hai ngày

Ở ô khác, trừ ngày/thời gian bắt đầu từ ngày/thời gian kết thúc. Kết quả có thể là số số thập phân. Bạn sẽ sửa lỗi này ở bước tiếp theo.

• Thiết lập định dạng [h]:mm

Nhấn CTRL+1 hoặc     +1 trên Mac. Chọn Custom. Trong Type, nhập [h]:mm.

Qua bài viết: Giáo trình Excel 2021 toàn tập [phần cơ bản-2] nếu vẫn còn thắc mắc hoặc cần tư vấn, hỗ trợ. Quý khách vui lòng liên hệ theo thông tin dưới đây: