Cập nhật nội dung chi tiết về Hướng Dẫn Giới Hạn Dữ Liệu Trong Excel (Hướng Dẫn Sử Dụng Chức Năng Data Validation Trong Excel) mới nhất trên website Photomarathonasia.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.
Chức năng Data Validation của Excel cho phép bạn thiết lập các quy tắc nhất định để giới hạn những gì được nhập vào một ô. Ví dụ: nhập vào ô có giá trị từ 0 đến 10, chỉ nhập vào ô dạng số, chỉ nhập vào ô dạng ngày, chỉ được phép nhập 10 ký tự… Khi thiết lập những quy tắc này, nếu người sử dụng nhập vào giá trị nằm ngoài giới hạn, sẽ hiển thị tin nhắn báo lỗi do bạn tạo ra.
Bước 1: Chọn ô hoặc cột/ hàng mong muốn
Bước 2: Vào thẻ Data → chọn Data Validation
Bước 3: Hộp thoại Data Validation xuất hiện
Bước 4: Thiết lập kiểu dữ liệu nhập
Any Value: Kiểu dữ liệu bất kỳ
Whole Number: Dữ liệu dạng số tự nhiên
Decimal: Dữ liệu dạng số thập phân
List: Dữ liệu được lựa chọn theo danh sách cho sẵn (bạn có thể tham khảo chức năng này ở bài viết Hướng dẫn tạo danh sách lựa chọn trong Excel (tạo drop down list))
Date: Dữ liệu dạng ngày tháng
Time: Dữ liệu dạng thời gian
Text Length: Giới hạn độ dài ký tự nhập vào (số các ký tự có thể nhập)
Custom: Người dùng tự tạo giới hạn bằng cách nhập công thức (việc tự tạo giới hạn rất phức tạp, bạn chỉ cần chọn 1 trong các lựa chọn trên là có thể các nhu cầu cơ bản rồi)
Bước 5: Thiết lập kiểu giới hạn
Sau khi lựa chọn kiểu dữ liệu, bạn có thể giới hạn giá trị nhập theo các lựa chọn:
between: khoảng giá trị (từ bao nhiêu đến bao nhiêu)
not between: không thuộc từ bao nhiêu đến bao nhiêu
greater than or equal to: lớn hơn hoặc bằng
less than or equal to: nhỏ hơn hoặc bằng
Bước 6: Thiết lập giá trị biên
Ví dụ, mình chọn kiểu dữ liệu Whole number, kiểu giới hạn between → hộp thoại sẽ cho phép bạn nhập giá trị biên
Minimum: giá trị nhỏ nhất
Maximum: giá trị lớn nhất
Với ví dụ như hình, mình chọn Allow kiểu Whole number → Data kiểu between → Min là 1, Max là 10 → ô/hàng/cột Excel được thiết lập sẽ cho phép nhập các giá trị số tự nhiên, với điều kiện lớn hơn 1 và nhỏ hơn 10.
Bước 7: Thiết lập thông báo hướng dẫn
Vẫn ở hộp thoại Data Validation, bạn chọn thẻ Input Message → nhập thông tin về tiêu đề hướng dẫn, tin nhắn hướng dẫn.
Kết quả bảng tính Excel của bạn sẽ show tin nhắn ở ô dữ liệu bạn vừa thiết lập
Bước 8: Thiết lập thông báo lỗi
Chọn thẻ Error Alert → chọn Style (ở đây mình chọn Stop ra biểu tượng x đỏ, nếu bạn chọn style Warning sẽ ra biểu tượng ! màu vàng, còn style Information sẽ ra biểu tượng chữ i màu xanh) → điền tiêu đề lỗi và thông báo lỗi.
Kết quả, nếu người sử dụng nhập sai dữ liệu, hệ thống sẽ hiện thông báo lỗi như bạn đã thiết lập:
Sử Dụng Chức Năng Data Validation Trong Excel
Data Validation trong Excel Tác giả: PhanTuHuong ([email protected])
Bài viết mừng sinh nhật diễn đàn chúng tôi tròn 4 tuổi (02/7/2010) Trang 1
Table of Contents 1. Đặt vấn đề 2 1.1. Chức năng Settings 3 1.1.1. Whole number: 3 1.1.2. Decimal: 6 1.1.3. List: 7 1.1.4. Date: 9 1.1.5. Text length: 10 1.2. Chức năng Input Message 10 1.3. Chức năng Error Alert 11 1.4. Các ví dụ về Data validation 13 1.4.1. Nhập số thập phân tăng dần theo 1 cột: 13 1.4.2. Tạo danh sách tham chiếu phụ thuộc: 14 1.4.3. Giới hạn tổng giá trị nhập liệu trong vùng: 15 2. Kết luận 16 Data Validation trong Excel Tác giả: PhanTuHuong ([email protected])
Bài viết mừng sinh nhật diễn đàn chúng tôi tròn 4 tuổi (02/7/2010) Trang 2
Hình 1: Chọn vùng cần kiểm soát và thực hiện chức năng Data validation Data Validation trong Excel Tác giả: PhanTuHuong ([email protected])
Bài viết mừng sinh nhật diễn đàn chúng tôi tròn 4 tuổi (02/7/2010) Trang 3
1.1. Chức năng Settings Cửa sổ Settings cho phép thiết lập cài đặt về điều kiện nhập liệu trong Validation criteria. Tuỳ vào đối tượng kiểm soát mà ta chọn trong danh sách bên dưới Allow. Mặc định ban đầu cho phép nhập bất cứ kiểu dữ liệu nào trong ô (Any value). Để thay đổi theo ý muốn, đầu tiên ta chọn vùng dữ liệu cần thiết lập chức năng Validation.
Hình 2: Cửa sổ Data validation
Hình 3: Các chức năng về điều kiện nhập liệu Trong danh sách thả xuống của Validation criteria (hình 3), có các lựa chọn Whole number, Decimal, List, Date, Time, Text lenght, Custom. 1.1.1. Whole number: Chức năng này chỉ cho phép nhập liệu là số nguyên. Nếu nhập số thập phân, chuỗi, sẽ bị báo lỗi. Chức năng này hữu ích khi dữ liệu nhập là tuổi, số lượng mặt hàng, số sản phẩm, điểm
thi, Khi chọn Whole number, chức năng Data xuất hiện cho phép khống chế phạm vi giá trị nhập (hình 5). Data Validation trong Excel Tác giả: PhanTuHuong ([email protected])
Bài viết mừng sinh nhật diễn đàn chúng tôi tròn 4 tuổi (02/7/2010) Trang 4 Hình 4: Chọn kiểu phạm vi nhập liệu a. Between (not between): Chỉ cho phép nhập giá trị trong một vùng xác định (hoặc ngoài một vùng xác định). Ví dụ điểm thi phải nằm trong phạm vi từ 0 đến 10, ngày trong tháng từ 1 đến 31, các thứ trong tuần, – Minimum: giá trị giới hạn nhỏ nhất khi nhập liệu. – Maximum: giá trị giới hạn lớn nhất khi nhập liệu. – Ingnore blank: bỏ qua ô trống, tức là không xét điều kiện nhập liệu khi ô trống. – Clear All: huỷ bỏ Giá trị giới hạn có thể cố định (không thay đổi được) hoặc không cố định (có thể thay đổi được) tuỳ vào mục đích sử dụng.
Hình 5: Phạm vi nhập liệu – Giới hạn cố định: là số khi nhập giá trị trong ô giới hạn. Trong hình 5, giá trị nhập nhỏ nhất bằng 0 và lớn nhất là 100. Nếu bạn nhập giá trị ngoài khoảng, Excel sẽ báo lỗi như hình 6 (thông báo lỗi mặc định của Excel). Chúng ta có thể thay đổi nội dung thông báo đó theo mục đích sử dụng cụ thể ở mục 1.3. Data Validation trong Excel Tác giả: PhanTuHuong ([email protected])
Bài viết mừng sinh nhật diễn đàn chúng tôi tròn 4 tuổi (02/7/2010) Trang 5 Hình 6: Thông báo lỗi nhập liệu mặc định trong Excel
Hình 7: Chọn giới hạn động trong nhập liệu
Hình 8: Giá trị xác định khi nhập liệu – Giới hạn không cố định: giá trị trong ô giới hạn phụ thuộc quá trình tính toán từ địa chỉ ô nào đó. Điều đó có nghĩa là giá trị giới hạn có thể thay đổi, phụ thuộc quá trình tính toán, cập nhật của công thức. Trong hình 7, giá trị nhập nhỏ nhất bằng giá trị ô Q8 (bằng 200) và lớn nhất bằng ô Q9 (bằng 1500). Nếu bạn nhập giá trị ngoài khoảng, Excel sẽ báo lỗi như hình 7 (thông báo lỗi mặc định của Excel). Phạm vi ứng dụng này phụ thuộc nhiều vào khả năng khai thác Data Validation trong Excel Tác giả: PhanTuHuong ([email protected])
Bài viết mừng sinh nhật diễn đàn chúng tôi tròn 4 tuổi (02/7/2010) Trang 6
công thức và sử dụng hàm của bạn. Nếu bạn khai thác công thức và sử dụng hàm tốt, khả năng kiểm soát số liệu sẽ tốt hơn, linh hoạt hơn. Điều đó thể hiện ở phần ví dụ. b. Equal to (not equal to): Chỉ cho phép nhập một giá trị xác định (hoặc ngoài giá trị xác định). Giá trị xác định đó được khai báo trong Value. Giá trị xác định có thể cố định bằng cách nhập trực tiếp hoặc có thể thay đổi được bằng cách sử dụng hàm hoặc công thức (cách thực hiện tương tự như ở mục a ở trên). c. Greater than (Greater than or equal to): Chỉ cho phép nhập giá trị lớn hơn (hoặc lớn hơn hay bằng) giá trị giới hạn nào đó. Giá trị giới hạn đó được khai báo trong Minimum. Giá trị giới hạn có thể cố định bằng cách nhập trực tiếp hoặc có thể thay đổi được bằng cách sử dụng hàm hoặc công thức (cách thực hiện tương tự như ở mục a ở trên). Ngược với trường hợp này là less than (less than or equal to).
Hình 9: Giá trị giới hạn nhỏ nhất khi nhập liệu d. Less than (less than or equal to): Chỉ cho phép nhập giá trị nhỏ hơn (hoặc nhỏ hơn hay bằng) giá trị giới hạn nào đó. Trường hợp này ngược lại ở mục c ở trên. 1.1.2. Decimal: Chức năng này chỉ cho phép nhập liệu là số nguyên hoặc số thập phân (hình 3). Cách thực hiện tương tự đối với Whole number. Sử dụng kiểu giá trị Decimal cho phép tránh được những sai sót do quy định về số thập phân Quốc tế và Việt Nam (dấu chấm và phẩy). Data Validation trong Excel Tác giả: PhanTuHuong ([email protected])
Bài viết mừng sinh nhật diễn đàn chúng tôi tròn 4 tuổi (02/7/2010) Trang 7 Hình 10: Giá trị giới hạn lớn nhất khi nhập liệu 1.1.3. List: Chức năng này chỉ cho phép nhập liệu từ một danh sách sẵn có (hình 3). Danh sách này có thể được nhập trực tiếp, từ một vùng trong sheet, từ tên (Name) vùng sẵn có hoặc từ file khác. Nên sử dụng chức năng này khi bạn cần nhập liệu được lấy từ một cơ sở dữ liệu (CSDL) sẵn có nằm hạn chế sai sót, rút ngắn thời gian. Ví dụ như nhập 1 phần tử (linh kiện, môn học, người, ) trong danh sách phụ tùng xe máy, ô tô, máy tính, môn học, danh sách thành viên, đã có sẵn. Điều này cho phép hạn chế tối đa do nhập liệu không đúng dẫn đến kết quả tính toán sai, đặc biệt là đối tượng nhập là chuỗi.
Hình 11: Tạo danh sách dữ liệu trực tiếp trong Source a. Danh sách nhập trực tiếp: Bạn gõ danh trực tiếp trong Source (hình 11), mỗi phần tử được ngăn cách bởi dấu phẩy. Với danh sách cố định (không sửa đổi) nên sử dụng trường hợp này. Với trường hợp nhập trực tiếp, bạn phải dùng kiểu gõ Unicode để hiển thị tiếng Việt. Nếu sử dụng kiểu gõ TCVN thì sẽ bị lỗi hiển thị chữ tiếng Việt, nhưng nội dung hiển thị trên ô không bị sai (tức là chỉ khó nhìn thôi). Data Validation trong Excel Tác giả: PhanTuHuong ([email protected])
Bài viết mừng sinh nhật diễn đàn chúng tôi tròn 4 tuổi (02/7/2010) Trang 8
Trong cửa sổ Data validation (hình 11), chức năng In-cell dropdown cho phép khi chọn vào ô sẽ hiện nút danh sách thả xuống để lựa chọn phần tử trong đó (hình 12).
Hình 12: Danh sách được thả xuống để chọn khi bấm vào nút dropdown b. Danh sách nhập từ một vùng trên bảng tính: Bạn có thể nhập danh từ một vùng sẵn có trong bảng tính bằng cách bấm vào nút (hình 13). Sau đó chọn vùng danh sách, địa chỉ danh sách sẽ hiện ra trong Source. Việc lựa chọn như trường hợp đầu tiên. Trong kiểu khai báo này, vùng danh sách phải cùng nằm trong cùng một sheet với những ô cần thể hiện. Điều đó là hạn chế nếu ta khai thác vùng danh sách này ở sheet khác. Vậy cách khắc phục hạn chế này như thế nào? Bằng cách tạo Name cho vùng (K2:K15) là chúng ta có thể khắc phục nhược điểm này. Khi đó trong Source sẽ thể hiện tên vùng (hình 14).
Hình 13: Khai báo danh sách từ vùng trong bảng tính Data Validation trong Excel Tác giả: PhanTuHuong (tuhuong[email protected])
Bài viết mừng sinh nhật diễn đàn chúng tôi tròn 4 tuổi (02/7/2010) Trang 9 Hình 14: Khai báo danh sách từ tên một vùng Bên cạnh những ưu điểm trên, hạn chế của chức năng List là không tự động dò đến phần tử cần tìm trong danh sách khi gõ ký tự đầu tiên. Vì vậy, với danh sách nhiều đối tượng việc dò tìm sẽ mất nhiều thời gian. 1.1.4. Date: Chức năng này chỉ cho phép nhập liệu là ngày. Việc điều khiển về khoảng ngày nhập giống như đối với Whole number. Điều đó có nghĩa là chọn ngày trong 1 khoảng xác định, ngày bắt đầu, ngày kết thúc,
Hình 15: Khai báo phạm vi ngày nhập Ví dụ như trong hình 15, chỉ cho phép nhập ngày trong khoảng từ ngày hôm nay (Today) và cộng thêm 3 ngày nữa. Cần lưu ý về định dạng ngày tháng và cách nhập liệu ngày tháng cho đúng, nếu không Excel luôn báo lỗi nhập liệu. Data Validation trong Excel Tác giả: PhanTuHuong ([email protected])
Bài viết mừng sinh nhật diễn đàn chúng tôi tròn 4 tuổi (02/7/2010) Trang 10
1.1.5. Text length: Chức năng này chỉ cho phép nhập liệu là chuỗi có độ dài xác định (tính bằng số ký tự, kể cả khoảng trắng, dấu, ). Việc điều khiển về chiều dài chuỗi nhập giống như đối với Whole number. Điều đó có nghĩa là có thể kiểm soát được chiều dài chuỗi nhập giới hạn trong 1 khoảng xác định, chiều dài chuỗi nhập nhỏ nhất, chiều dài chuỗi nhập lớn nhất, Ví dụ như trong hình 16, chỉ cho phép nhập mã hàng có chiều dài 6 ký tự.
Hình 16: Khai báo mã hàng có chiều dài 6 ký tự
Hình 17: Khai báo mã hàng có chiều dài 6 ký tự 1.2. Chức năng Input Message Chức năng Input Message cho phép hiển thị thông tin nhập liệu khi di chuyển chuột vào ô đó, từ đó định hướng cho công việc nhập liệu. Tất nhiên khi thực hiện chức năng này thì bạn đã thiết lập chế độ Settings cho các ô đó. Để hiển thị thông tin nhập liệu cho khối ô nào đó, đầu tiên ta bôi đen khối ô đó (C6:C15 trong hình 17). Bước thực hiện như hình 1, 2 và chuyển sang Input Message (hình 17). Cửa sổ Input Message gồm các nội dung sau: Data Validation trong Excel Tác giả: PhanTuHuong ([email protected])
Bài viết mừng sinh nhật diễn đàn chúng tôi tròn 4 tuổi (02/7/2010) Trang 11
– Show input message when cell is selected: Bật (tắt) chế độ hiển thị thông báo khi ô được chọn. Trường hợp này chọn Bật. – Title: Nội dung tiêu đề hiển thị (hình 18), dùng kiểu gõ Unicode. – Input message: Nội dung thông báo, dùng kiểu gõ Unicode.
Hình 18: Thông báo khi chọn ô khi dùng Input message 1.3. Chức năng Error Alert Như chúng ta đã biết, khi ô đã được đặt chế độ Data validation, nếu nhập liệu không đúng quy định sẽ bị thông báo ở hình 6. Ta có thể thay đổi nội dung thông báo mặc định đó theo các hoàn cảnh khác nhau và hiển thị nội dung thông báo đó bằng tiếng Việt cho dễ hiểu và sửa đổi. Từ hình 2 chuyển sang Error Alert (hình 19), cửa sổ Error Alert gồm các nội dung sau: – Show error alert after invalid data is selected: Bật (tắt) chế độ hiển thị cảnh báo sau khi dữ liệu được nhập vào ô. Trường hợp này chọn Bật. – Style: Kiểu cảnh báo, gồm Stop (dừng lại), Warrning (cảnh báo), Information (thông tin). Tuỳ mức độ cảnh báo mà có cách xử lý phù hợp. + Stop : Thông báo lỗi nhập liệu nghiêm trọng, Excel không chấp nhận giá trị nhập liệu này và yêu cầu phải nhập đúng mới được chấp nhận (hình 20). + Warning : Thông báo lỗi nhập liệu mang tính cảnh báo, tùy trường hợp có thể chấp nhập hoặc không chấp nhận giá trị nhập liệu (hình 21). + Stop : Thông báo lỗi nhập liệu mang tính thông tin, bạn có thể bỏ qua trường hợp nhập liệu không đúng quy định (hình 22). – Title: Nội dung tiêu đề thông báo, dùng kiểu gõ Unicode. – Input message: Nội dung thông báo (chú ý nội dung theo những hoàn cảnh cụ thể), dùng kiểu gõ Unicode. Data Validation trong Excel Tác giả: PhanTuHuong ([email protected])
Bài viết mừng sinh nhật diễn đàn chúng tôi tròn 4 tuổi (02/7/2010) Trang 12 Hình 19: Cửa sổ Error Alert
Hình 20: Thông báo Stop khi nhập liệu không đúng
Hình 21: Thông báo Warrning khi nhập liệu không đúng Như vậy, chúng ta đã tìm hiểu một cách khá tổng quát về chức năng Data validation. Chức năng cho phép kiểm soát tốt những dữ liệu nhập, đặc biệt là khi thực hiện với CSDL. Chức năng này giúp Excel trở nên gần gũi hơn với phần mềm Access (chuyên về CSDL). Để hiểu rõ hơn khả năng áp dụng Data validation, chúng ta sẽ đi vào các ví dụ cụ thể ở mục tiếp theo. Data Validation trong Excel Tác giả: PhanTuHuong ([email protected])
Bài viết mừng sinh nhật diễn đàn chúng tôi tròn 4 tuổi (02/7/2010) Trang 13 Hình 22: Thông báo Information khi nhập liệu không đúng 1.4. Các ví dụ về Data validation 1.4.1. Nhập số thập phân tăng dần theo 1 cột: Trong nhiều bài toán về kỹ thuật, yêu cầu nhập liệu là số (số thập phân) theo 1 cột với giá trị tăng dần. Nếu sai do không kiểm soát tốt (chẳng hạn giá trị sau nhỏ hơn giá trị trước), quá trình tính toán sẽ bị sai lệch. Có thể sử dụng cách lập trình sự kiện đối với ô nhập (VBA) để kiểm soát nhưng sẽ phức tạp, nhất là khi xử lý với nhiều ô, nhiều khối ô. Khi sử dụng Data validation thì công việc trở nên khá đơn giản. Trong hình 23, vùng D7:D29 yêu cầu sắp xếp theo giá trị tăng dần, giá trị nhỏ nhất tại ô D7. Chú ý khi khai báo công thức trong Validation sử dụng địa chỉ tuyệt đối.
Hình 23: Thiết lập nhập dữ liệu là số tăng dần Data Validation trong Excel Tác giả: PhanTuHuong ([email protected])
Bài viết mừng sinh nhật diễn đàn chúng tôi tròn 4 tuổi (02/7/2010) Trang 14
Hình 24: Tạo danh sách tham chiếu phụ thuộc Data Validation trong Excel Tác giả: PhanTuHuong ([email protected])
Bài viết mừng sinh nhật diễn đàn chúng tôi tròn 4 tuổi (02/7/2010) Trang 15 Hình 25: Tạo danh sách các huyện (quận) tương ứng với tỉnh (thành phố)
Hình 26: Danh sách nhập liệu các huyện (quận) tương ứng với tỉnh (thành phố) 1.4.3. Giới hạn tổng giá trị nhập liệu trong vùng: Trong một số trường hợp, yêu cầu tổng giá trị nhập trong vùng chỉ tới 1 giới hạn nào đó. Nếu tổng giá trị nhập vượt vùng giới hạn đó thì Excel sẽ báo lỗi. Ví dụ tổng thu nhập của 1 người là 15 triệu/ tháng. Khoản tiền đó sẽ được chi tiêu trong 1 tháng với nhiều loại chi phí (hình 27). Tại trường hợp này, sử dụng Custom trong danh sách của Allow, công thức nhập ở dưới: =SUM($C$5:$C$12)<$C$2 Data Validation trong Excel Tác giả: PhanTuHuong ([email protected])
Bài viết mừng sinh nhật diễn đàn chúng tôi tròn 4 tuổi (02/7/2010) Trang 16 Hình 27: Sử dụng Custom để quản lý giới hạn nhập liệu Sau đó thiết lập thông báo lỗi trong Error Alert, kiểu Warning. Khi nhập quá giới hạn trên, thông báo sẽ hiện ra và bạn tuỳ chọn cách xử lý (hình 28).
Hình 28: Thông báo lỗi khi chi tiêu vượt quá giới hạn 2. Kết luận – Data validation là chức năng rất hay và linh hoạt trong việc xử lý dữ liệu nhập trong bảng tính. Khả năng kiểm soát dữ liệu kèm theo thông báo hỗ trợ giúp người sử dụng đưa ra biện pháp xử lý thích hợp. – Kết hợp sự định dạng, trang trí bảng tính, thiết kế giao diện, khai thác và sử dụng hàm cùng với Data validation sẽ giúp chúng ta có một bảng tính hoàn chỉnh, đẹp, khả năng ứng dụng cao. Đó cũng là xu hướng tất yếu với những ai xây dựng các sản phẩm ứng dụng trong Excel. – Với khả năng này, Excel đã trở nên gần gũi hơn với CSDL vì có khả năng kiểm soát dữ liệu nhập. Việc khai thác CSDL trong Excel bằng hàm, công thức và các chức năng nâng cao khác đã giúp nhiều người sử dụng Excel làm CSDL. Data Validation trong Excel Tác giả: PhanTuHuong ([email protected])
Tài liệu tham khảo chính và một số nội dung lấy từ: http://www.contextures.com
Hướng Dẫn Sử Dụng Chức Năng Subtotal Thống Kê Dữ Liệu Theo Điều Kiện Trong Excel
Đôi khi ta cần thể hiện doanh số bán hàng theo từng nhân viên, hoặc số lượng thực phẩm theo vùng miền, hay nhiệt độ trung bình của từng tháng… Trong bài viết này, Học Excel Online sẽ hướng dẫn bạn làm điều trên thông qua chức năng SUBTOTAL thống kê dữ liệu theo điều kiện.
Chức năng SUBTOTAL thống kê dữ liệu theo điều kiện
Trong ví dụ lần này, ta sẽ mô phỏng đơn giản nhất bảng thống kê nhiệt độ và độ ẩm của Hà Nội 3 tháng đầu năm 2017.
Công việc của ta bây giờ là thống kê nhiệt độ và độ ẩm trung bình từng tháng. Và ta sẽ thực hiện bằng công cụ SUBTOTAL.
Công cụ SUBTOTAL
Thêm các đối tượng thích hợp để sử dụng công cụ SUBTOTAL
Với một bảng dài ngoằng như này, hẳn bạn sẽ không muốn thêm những dòng tính trung bình vào dưới mỗi khi kết thúc tháng đâu nhỉ? Hãy đến với SUBTOTAL.
Chức năng SUBTOTAL sẽ giúp bạn tính được kết quả trung bình theo từng tháng một cách nhanh chóng.
Bước 1: Chọn toàn bộ bảng tính (trong trường hợp bạn chưa biết, chỉ cần chọn một ô bất kì trong bảng tính rồi chọn Subtotal, Excel sẽ tự tính toán và chọn tất cả những ô còn lại).
Bước 2: Chọn Subtotal. Một hộp thoại sẽ hiện ra:
Use function: Hàm được sử dụng để tính. Với hàm SUM, Excel sẽ trả về tổng, hàm AVERAGE trả về trung bình,… trả lời cho câu hỏi: Phương thức thống kê?
Add subtotal to: Mục được áp dụng hàm ở trên. Chọn các cột cần thiết và Excel sẽ tính toán giá trị ở đó. Trả lời câu hỏi: Thống kê dữ liệu gì?
Ngoài ra ta có:
Replace current subtotal: Nếu đã tồn tại trước đó một subtotal, nó sẽ thay thế bằng subtotal mới.
Page break between groups: Bạn biết page break (ngắt trang) chứ? Tùy chọn này giúp cho mỗi nhóm của at each change in được nằm trong một trang khác nhau.
Summary below data: Đưa function xuống dưới các nhóm. Nếu bỏ chọn ô này, kết quả sẽ không mất, mà đưa lên đầu.
Ok, ấn đi các bạn!
At each change in thời gian, use function AVERAGE, Add subtotal to Nhiệt độ ngày, Nhiệt độ đêm, Độ ẩm.
Mình biết điều đang xảy ra với trang tính của các bạn.
Để khắc phục, đầu tiên là Undo đi nào (Ctrl+Z).
Tại cột Tháng, nhập công thức vào như sau:
Sau đó định dạng lại về General nếu kết quả đang ở dạng Date.
Sử dụng autofill áp dụng kết quả cho các ô còn lại. Trong trường hợp bạn chưa biết autofill, hãy xem bài viết này.
Kết quả:
– Ứng dụng SUBTOTAL kết hợp COUNTIF thiết lập số thứ tự thành phần.
– Tổng quan về công cụ SUBTOTAL
Hướng Dẫn Sử Dụng Excel Autofill Trong Excel
Lượt Xem:3589
Hướng dẫn sử dụng EXCEL AUTOFILL trong excel
Tính năng Tự động điền Excel ( EXCEL AUTOFILL ) có thể được sử dụng để điền vào một dải ô có giá trị lặp lại, một chuỗi giá trị hoặc chỉ một định dạng ô.
Để sử dụng tính năng Tự động điền Excel đơn giản:
Hộp thoại tùy chọn xử lý điền ô Excel và hộp EXCEL AUTOFILL
Nhập một giá trị vào ô bắt đầu;
Sử dụng chuột để kéo ‘ fill handle‘ (hình vuông nhỏ màu đen ở góc dưới cùng bên phải của ô bắt đầu) trên phạm vi ô được lấp đầy;
Khi bạn kéo ‘fill handle’ trên dãy ô cần điền, Excel sẽ điền vào các ô đã chọn, bằng cách lặp lại giá trị trong ô đầu tiên hoặc bằng cách chèn một chuỗi từ giá trị ô đầu tiên (ví dụ 1, 2, 3 , …);
Nhấp vào hộp ‘ Tuỳ chọn EXCEL AUTOFILL‘, sẽ xuất hiện ở cuối phạm vi ô đã chọn của bạn. Điều này sẽ cung cấp cho bạn các tùy chọn khác nhau sau:
Sao chép ô – sao chép ô ban đầu trên phạm vi đã chọn;
Fill Series – điền vào dãy được chọn với một loạt các giá trị (thường tăng thêm 1), bắt đầu với giá trị ô ban đầu;
Chỉ định dạng điền – điền vào dải ô đã chọn với định dạng, chứ không phải các giá trị của ô ban đầu;
Điền Không có Định dạng – điền vào phạm vi đã chọn với các giá trị, nhưng không sao chép định dạng từ ô ban đầu.
Chọn tùy chọn mà bạn yêu cầu cho các ô được điền.
Tự động điền sử dụng nhiều giá trị di động bắt đầu
Nếu bạn muốn Excel nhận ra một chuỗi không phải là một gia số đơn giản bằng 1, điều này có thể được thực hiện bằng cách nhập hai giá trị đầu tiên của chuỗi của bạn vào ô đầu tiên và thứ hai của một dải ô. Chọn cả hai ô này và một lần nữa, kéo thanh điều khiển điền vào trong phạm vi cần điền. Excel sẽ tự động nhận dạng mẫu từ hai ô ban đầu và tiếp tục điều này qua phạm vi đã chọn. Sử dụng phương pháp này, bạn có thể lấy Excel để điền vào ô theo gia số hoặc số lượng của bất kỳ số nào (ví dụ 2, 4, 6, 8, …).
Ngoài ra, nếu bạn muốn Excel điền vào các ô có giá trị xen kẽ lặp lại ( ví dụ: 1, 2, 1, 2, 1, 2, ...), bạn có thể bắt đầu mẫu trong hai (hoặc nhiều) ô đầu tiên, sau đó, với các ô đầu tiên được tô sáng, kéo thanh công cụ fill và sau đó nhấp vào hộp ”. Trong hộp này, chọn tùy chọn ‘ Sao chép ô‘ để lặp lại các giá trị ô ban đầu trong phạm vi đã chọn.
Ngày tự động điền Excel
Vì ngày tháng và thời gian được lưu trữ trong Excel dưới dạng số, chúng cũng có thể được sử dụng với Excel Autofill.
Theo mặc định, nếu bạn chỉ cần nhập một ngày hoặc thời gian và kéo xử lý điền, ngày và thời gian sẽ hoàn thành trong một chuỗi, bằng cách thêm một ngày (cho ngày) hoặc một giờ (đối với thời gian). Tuy nhiên, cũng như với các số đơn giản, bạn có tùy chọn nhấp vào hộp ‘T uỳ chọn Tự động Điền‘, để chọn một loại tự động điền khác.
Thời gian có bốn tùy chọn EXCEL AUTOFILL tương tự như được hiển thị ở trên, đối với các số đơn giản (ví dụ: Sao chép Ô, Điền Dòng, Chỉ Định dạng Đầy, Điền Không có Định dạng). Tuy nhiên, đối với ngày tháng, có các tùy chọn Tự động điền bổ sung. Cũng như bốn tùy chọn cho số đơn giản và thời gian, cũng có những điều sau đây:
Fill Days – Tìm một mẫu trong ngày khi điền các ô đã chọn;
Điền vào các ngày trong tuần – Tìm một mẫu trong ngày khi điền các ô đã chọn, nhưng không bao gồm thứ Bảy hoặc Chủ Nhật trong chuỗi;
Điền các tháng – Tìm một mẫu trong tháng khi điền các ô đã chọn;
Fill Years – Tìm một mẫu trong năm khi điền các ô đã chọn.
Tự động điền Giá trị Văn bản
Tự động điền Excel thường sẽ điền vào một cột có giá trị văn bản bằng cách lặp lại (các) giá trị trong (các) ô đầu tiên. Tuy nhiên, có một số giá trị văn bản Excel nhận ra là một phần của chuỗi. Đó là:
Các ngày trong tuần (viết tắt hoặc tên đầy đủ)
Tháng (tên viết tắt hoặc tên đầy đủ)
Chức năng tự động điền và công thức
Tính năng EXCEL AUTOFILL cũng hoạt động với các hàm và công thức trong Excel. Tuy nhiên, với loại Tự động điền này, không có tùy chọn ‘điền chuỗi’. Thay vào đó, Excel quan sát các quy tắc của Tham chiếu tuyệt đối và tương đối (tức là nếu tham chiếu hàng hoặc cột được bắt đầu bằng dấu $, excel sẽ giữ nguyên tham chiếu khi công thức được sao chép sang các ô khác; được điều chỉnh khi công thức được sao chép sang các ô khác.
Tự động điền ngang và dọc
Tự động điền Excel qua một hàng
Cũng như làm việc xuống một cột, tính năng Tự động điền cũng hoạt động theo chiều ngang, trên các hàng.
Chỉ cần kéo tay cầm điền vào các ô mà bạn muốn điền.
Tự động điền nhiều hàng hoặc cột đồng thời
Nhấp đúp vào Fill Handle
Đối với tốc độ, bạn có thể Tự động điền cột bằng cách nhấp đúp vào nút điều khiển điền của ô được đánh dấu (hoặc phạm vi ô). Nếu các ô bên dưới hoặc liền kề với ô được đánh dấu (hoặc dải ô) chứa giá trị, nhấp đúp vào nút điều khiển điền sẽ làm cho Tự động điền điền vào cột hiện tại cho đến khi nó đến cuối dải dữ liệu hiện tại.
Các bài viết mới
Các tin cũ hơn
Bạn đang đọc nội dung bài viết Hướng Dẫn Giới Hạn Dữ Liệu Trong Excel (Hướng Dẫn Sử Dụng Chức Năng Data Validation Trong Excel) trên website Photomarathonasia.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!