Sử Dụng Chức Năng Data Validation Trong Excel

--- Bài mới hơn ---

  • Chèn Text Box & Drop Cap Trong Microsoft Word
  • Làm Chữ Cái To Ở Đầu Dòng Trong Word 2022, 2013, 2010, 2007, 2003, Tạo
  • Chia Cột Và Tạo Drop Cap
  • Tiêu Hóa Ở Dạ Dày Đơn
  • Cấu Tạo Và Chức Năng Của Dạ Dày Trâu Bò
  • Data Validation trong Excel Tác giả: PhanTuHuong ()

    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 ()

    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 ()

    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 ()

    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)

    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 ()

    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 ()

    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 ()

    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

    --- Bài cũ hơn ---

  • 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)
  • Cách Sử Dụng Offset Và Data Validation Để Tạo Liên Kết List Động Trong Excel
  • Liệt Dây Thần Kinh Số 7 Có Nguy Hiểm Không, Có Chữa Được Không?
  • Viêm (Liệt) Dây Thần Kinh Số 7, Những Điều Cần Biết
  • Đau Dây Thần Kinh Số V (Số 5): Triệu Chứng, Nguyên Nhân Và Cách Điều Trị
  • Giới Thiệu Cách Sử Dụng Data Validation List Với Nguồn Là Table

    --- Bài mới hơn ---

  • Hướng Dẫn Cách Sử Dụng Thiết Lập Custom Trong Data Validation
  • Vai Trò Của Vi Khuẩn Dạ Cỏ
  • Sử Dụng Nitrate Cho Gia Súc Nhai Lại
  • Giảm Viêm Đau Dạ Dày, Diệt Khuẩn Hp
  • Thuốc Dạ Dày Nhất Nhất Có Tốt Không? Giá Bao Nhiêu?
  • Trong bài viết này, chúng tôi sẽ hướng dẫn cách sử dụng Data Validation List với nguồn lấy từ 1 Table để có thể tạo được danh sách chọn động một cách nhanh chóng và hiệu quả giúp cho quá trình nhập liệu trở nên dễ dàng và đảm bảo dữ liệu được chính xác hơn.

    Sử dụng nguồn cho danh sách chọn là 1 Table hay 1 cột trong 1 Table nào đó sẽ giúp cho Data Validation trở nên linh hoạt hơn rất nhiều thay vì sử dụng 1 vùng cố định (không phải Table) hay sử dụng công thức phức tạp để làm danh sách chọn động cho Data Validation.

    Những lý do bạn nên sử dụng table trong excel

    Khi bạn thêm dữ liệu vào trong Table thì dữ liệu cũng sẽ được nhận vào trong danh sách chọn cho bạn và bạn không phải sửa lại nguồn cho Data Validation List.

    Trường hợp Table lấy làm nguồn chỉ có 1 cột

    Trong đó:

    =INDIRECT(“Table1”)

    Data Validation

    Trường hợp Table lấy làm nguồn có nhiều hơn 1 cột

    Ví dụ Table1 của mình có 2 cột là HH và DG như sau. Mình muốn làm danh sách chọn cho ô E2 là những đơn giá trong Table1 đó. Và vì Table này có hơn 1 cột nên khi làm danh sách chọn với Data Validation List ta sẽ phải tham chiếu tới cụ thể cột nào đó.

    Ta sẽ có công thức chung như sau:

    =INDIRECT(“Table1[DG]”)

    mục Data Validation List

    Giờ đây, khi bạn thêm dữ liệu vào Table nguồn đó thì đồng thời dữ liệu trong danh sách chọn Data Validation List cũng được cập nhật cho bạn và bạn sẽ không cần phải thay đổi lại nguồn.

    Những lý do bạn nên sử dụng table trong excel

    Tạo data validation với name động trong excel với hàm offset và counta

    Báo lỗi trùng nội dung trong 1 vùng bằng custom data validation

    Đánh giá bài viết này

    --- Bài cũ hơn ---

  • U Dây Thần Kinh Thính Giác (U Dây Thần Kinh Số 8)
  • Đánh Giá Thần Kinh Sọ Não Như Thế Nào
  • Các Dây Thần Kinh Ảnh Hưởng Chức Năng Vận Động Của Mắt
  • Tổng Quan Về Bệnh Lý Thần Kinh Thị Giác Và Dây Thần Kinh Sọ
  • Các Phương Pháp Cơ Bản Trong Chẩn Đoán Vi Khuẩn
  • Hướng Dẫn Cách Sử Dụng Thiết Lập Custom Trong Data Validation

    --- Bài mới hơn ---

  • Vai Trò Của Vi Khuẩn Dạ Cỏ
  • Sử Dụng Nitrate Cho Gia Súc Nhai Lại
  • Giảm Viêm Đau Dạ Dày, Diệt Khuẩn Hp
  • Thuốc Dạ Dày Nhất Nhất Có Tốt Không? Giá Bao Nhiêu?
  • 9+ Thuốc Chữa Đau Dạ Dày (Bao Tử) Hiệu Quả Tốt Nhất 2022
  • Data Validation là một ứng dụng vô cùng cần thiết trong Excel. Ở những phần trước, bạn đã được Gitiho hướng dẫn cách sử dụng căn bản của ứng dụng này. Tuy nhiên có thể bạn cảm thấy những chức năng căn bản vẫn chưa đủ để đáp ứng các yêu cầu của bạn. Và ở bài viết này, Gitiho sẽ giới thiệu thêm cho bạn cách sử dụng chức năng nâng cao của Data Validation.

    Cách sử dụng thiết lập Custom trong Data Validation

    Trong cửa sổ Data Validation, chúng ta chọn điều kiện thiết lập (Validation criteria) là dạng Custom như sau:

    Tuy nhiên ở phần Formula (Công thức) thì chúng ta phải sử dụng cách viết hàm hoặc công thức để diễn đạt ý nghĩa bạn muốn thiết lập.

    Với những trường hợp nhập công thức như vậy, tôi khuyến khích các bạn nhập ra Sheet hay một Cell nào đó rồi copy và paste sau. Như vậy, bạn có thể kiểm tra cũng như giảm sai sót trong khâu tính toán.

    Cách viết hàm trong thanh Formula

    Với ví dụ đầu tiên, bạn được yêu cầu giới hạn cho Cell Company Code (Mã số công ty) là Mã số đó phải gồm 1 chữ cái và 4 con số.

    Tuy

    nhiên để giới hạn yêu cầu này, còn những điều kiện nào khác nữa. Chúng ta hãy

    Điều kiện đầu tiên là tổng số các chữ cái hoặc số trong Mã số là 5. Vậy công thức cho điều kiện này là gì? Để đếm tổng số chữ cái hay ký hiệu trong một Cell nào đó, chúng ta có thể sử dụng hàm Len với công thức như sau:

    =

    Len (A5)

    Tuy nhiên để hoàn thành điều kiện đầu tiên chúng ta không cần là tổng số chữ cái hay ký hiệu mà liệu rằng tổng số có đúng phải là 5 hay không. Và để làm như vậy, chúng ta chỉ cần thêm vào công thức:

    =

    Len(A5)= 5

    Và đây là kết quả của bạn:

    Và nếu có thay đổi thì kết quả sẽ chuyển thành False như sau:

    Ngoài ra, bạn có thể truy cập ngay chúng tôi để tìm hiểu thêm các khóa học hấp dẫn về tin học văn phòng: Word, Excel, Power Point. Có rất nhiều ưu đãi hấp dẫn đang chờ bạn.

    Đánh giá bài viết này

    --- Bài cũ hơn ---

  • Giới Thiệu Cách Sử Dụng Data Validation List Với Nguồn Là Table
  • U Dây Thần Kinh Thính Giác (U Dây Thần Kinh Số 8)
  • Đánh Giá Thần Kinh Sọ Não Như Thế Nào
  • Các Dây Thần Kinh Ảnh Hưởng Chức Năng Vận Động Của Mắt
  • Tổng Quan Về Bệnh Lý Thần Kinh Thị Giác Và Dây Thần Kinh Sọ
  • Kiểm Soát Dữ Liệu Nhập Với Data Validation Trong Excel

    --- Bài mới hơn ---

  • Sử Dụng Data Validation Để Tạo List Và Kiểm Tra Dữ Liệu Nhập
  • Cách Thiết Lập Hộp Thoại Xổ Xuống Đẹp Mắt Cho Chức Năng Kiểm Duyệt Dữ Liệu Data Validation Trong Excel
  • Định Dạng Chữ Rơi, Hạ Dòng (Drop Cap)
  • Cách Chia Cột, Tạo Dropcap Trong Word 2003, 2007, 2010, 2013
  • Cách Sử Dụng Data Validation Trong Microsoft Excel 2013
  • Để dữ liệu đó có thể được giới hạn trong một phạm vi nào đó, có thể là số nguyên, số thập phân, ngày, giờ, trong danh sách sẵn có hoặc chuỗi có độ dài xác định. Khi đó chức năng Data Validation sẽ giúp nhập liệu một cách chính xác theo yêu cầu, hạn chế sai sót. Các bước thực hiện:

    • Chọn vùng dữ liệu dữ liệu cần kiểm soát.
      Tab : cho phép thiết lập về điều kiện nhập liệu trong Validation criteria. Tuỳ vào yêu cầu kiểm soát mà 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 vào trong ô (Any value). Để thay đổi theo ý muốn, phải chọn trong danh sách thả xuống của Validation criteria, có các lựa chọn: Whole number, Decimal, List, Date, Time, Text lenght, Custom.
      Whole number: chức năng này chỉ cho phép nhập dữ liệu là số nguyên. Nếu nhập số thập phân, chuỗi,… sẽ bị báo lỗi. Khi chọn Whole number, chức năng Data xuất hiện cho phép thiết lập phạm vi giá trị nhập.
      Between (not between): chỉ cho phép nhập giá trị trong một phạm vi xác định (hoặc ngoài phạm vi xác định). Ví dụ điểm thi phải nằm trong phạm vi từ 0 đến 10, tháng trong năm từ 1 đến 12… trong đó:

      • Minimum: giá trị giới hạn nhỏ nhất khi nhập dữ liệu.
      • Maximum: giá trị giới hạn lớn nhất khi nhập dữ liệu.
    • Ignore blank: bỏ qua ô trống (không xét điều kiện nhập dữ liệu khi ô trống).
    • Clear All: huỷ bỏ tất cả.
    • 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 và có thể là giá trị 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.
      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 chỉ định. Giá trị giới hạn đó được khai báo trong Minimum và có thể là giá trị 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. Ngược lại với trường hợp này là less than (less than or equal to).
    • Decimal: chỉ cho phép nhập liệu là số nguyên hoặc số thập phân. Cách thực hiện tương tự đối với Whole number.
    • List: chỉ cho phép nhập dữ liệu từ một danh sách sẵn có. 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.

        Danh sách nhập trực tiếp: gõ danh sách trực tiếp trong Source, mỗi phần tử được ngăn cách bởi dấu phẩy.
      Danh sách nhập từ một vùng trên bảng tính: chọn vùng danh sách trên bảng tính, địa chỉ danh sách sẽ hiện ra trong Source. Với cách này, vùng danh sách phải cùng nằm cùng sheet với những ô cần nhập. Nếu muốn sử dụng vùng danh sách ở sheet khác thì phải đặt tên cho vùng đó, khi đó Source sẽ hiển thị tên vùng.

    Hạn chế của chức năng List là không tự động tìm đế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 tìm sẽ mất nhiều thời gian.

      Date: chức năng này chỉ cho phép nhập giá trị ngày, việc điều khiển miền giá trị nhập giống như đối với Whole number.

    • Text length: Cho phép nhập dữ liệu là chuỗi có độ dài xác định, việc thiết lập chiều dài chuỗi nhập giống như đối với Whole number.
    • Tab Input Message: cho phép hiển thị thông tin nhập dữ liệu khi di chuyển chuột vào ô, từ đó định hướng cho công việc nhập dữ liệu.
    • Show input message when cell is selected: bật /tắt chế độ hiển thị thông báo khi ô được chọn.
    • Title: tiêu đề của thông báo.
    • Input message: nội dung thông báo.
    • Tab Error Alert: khi ô đã được đặt chế độ Data validation, nếu người dùng nhập liệu không đúng quy định thì Excel sẽ bị thông báo.
    • 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 ô.
    • Style: kiểu cảnh báo, gồm Stop (dừng lại), Warrning (cảnh báo), Information (thông tin).
    • Title: tiêu đề hộp thông báo.
    • Error message: nội dung thông báo.

    Các thao tác với Worksheet

    • Chèn thêm worksheet vào workbook, có thể thực hiện các cách sau:
    • Xóa worksheet khỏi workbook, có thể thực hiện các cách sau:
    • Nhấp phải chuột lên tên sheet muốn xóa trên thanh sheet tab, chọn Delete, xác nhận xóa chọn Delete.
    • Sắp xếp worksheet trên workbook, có thể thực hiện các cách sau:
    • Chọn tên sheet cần sắp xếp, drag đến vị trí mới.

    --- Bài cũ hơn ---

  • Quản Lý Hàng Hóa Bằng Data Validation Trong Excel Cực Hay
  • 12 Đôi Dây Thần Kinh Sọ
  • Liệt Dây Thần Kinh Số 3 Có Nguy Hiểm Không? Điều Trị Ra Sao?
  • Dây Thần Kinh Là Gì? Vị Trí Của 12 Dây Thần Kinh Sọ Não Số 7, 5, 10, 3, 6
  • Bệnh Viêm Dây Thần Kinh Số 3
  • Quản Lý Hàng Hóa Bằng Data Validation Trong Excel Cực Hay

    --- Bài mới hơn ---

  • Kiểm Soát Dữ Liệu Nhập Với Data Validation Trong Excel
  • Sử Dụng Data Validation Để Tạo List Và Kiểm Tra Dữ Liệu Nhập
  • Cách Thiết Lập Hộp Thoại Xổ Xuống Đẹp Mắt Cho Chức Năng Kiểm Duyệt Dữ Liệu Data Validation Trong Excel
  • Định Dạng Chữ Rơi, Hạ Dòng (Drop Cap)
  • Cách Chia Cột, Tạo Dropcap Trong Word 2003, 2007, 2010, 2013
  • Hôm nay Tin học Đức Minh sẽ hướng dẫn cho các bạn một thủ thuật excel cực hay đó là quản lý hàng hóa bằng cách ứng dụng chức năng Data Validation trong excel

    Link video: https://www.youtube.com/watch?v=qFqD3G-wayE

    Giới thiệu về thủ thuật quản lý hàng hóa bằng Data Validation trong Excel

    Xin chào các bạn, xin tự giới thiệu, tôi là khoa – giáo viên bên Viện đào tạo kế toán và Tin học Đức Minh. Hôm nay tôi sẽ hướng dẫn cho các bạn một thủ thuật quản lý hàng hóa bằng cách ứng dụng chức năng Data Validation trong excel cực hay! Đầu tiên tôi xin đưa ra ví dụ các bạn có một kho hàng hóa cần quản lý theo kiểu như sau:

    – Ở cột “Tình trạng sản phẩm” chỉ cần có 2 loại “có hàng” và “hết hàng” cho tất cả các sản phẩm khác nhau.

    Để làm được những điều trên, đầu tiên các bạn cần tạo một bảng như trong ví dụ trên và làm theo các bước sau để áp dụng chức năng Data Validation trong excel.

    1. Đặt tên cho các vùng dữ liệu cần đưa vào Data Validation trong excel để quản lý

    Tùy trường hợp mà bạn sẽ có bao nhiêu vùng cần đặt tên cho dữ liệu, ở đây ta có các vùng như “Loại sản phẩm”, “Tình trạng sản phẩm”, “iPhone”, “Sony”,… Các bạn nên tạo ra ở một sheet riêng gồm nhiều list khác nhau cho tiện quản lý chức năng Data Validation trong excel

    – Bước 1: Tạo một vùng dữ liệu mang tên “loaisanpham” để biểu thị cho các loại sản phẩm khác nhau chẳng hạn. Mục đích của việc này là tí đưa vùng “loaisanpham” đó vào cột “Loại sản phẩm” để chọn chức năng Data Validation trong ex cel

    Các bạn quét vùng loại sản phẩm vừa tạo và chuột phải, chọn “Name a Range”, đặt tên là “loaisanpham”

    – Bước 2: Tạo một vùng dữ liệu mang tên “ip” để biểu thị cho các đời sản phẩm – đời iphone khác nhau, “sn” để biểu thị cho các đời sony khác nhau, và còn lại thì tùy các bạn. Mục đích của việc này là tí đưa vùng “ip”, “sn” đó vào cột “Đời sản phẩm” để chọn chức năng Data Validation trong ex cel

    – Bước 3: Tạo một vùng dữ liệu mang tên “tinhtrang” để biểu thị cho tình trạng sản phẩm còn hàng hay hết hàng. Mục đích của việc này là tí đưa vùng “còn hàng”, “hết hàng” đó vào cột “Tình trạng sản phẩm” để chọn chức năng Data Validation trong ex cel

    – Bước 2: Ở cột “Đời sản phẩm” các bạn bôi đen hết và chọn Data – Data Validation. Trong phần Allow chọn ” List“, phần source ở đây các bạn phải viết một hàm if để đạt điều kiện thỏa mãn điều kiện “phù hợp với cột “Loại sản phẩm”. Ví dụ ở cột loại sản phẩm là iphone thì bên cột đời sản phẩm cái menu xổ xuống chỉ được xuất hiện những đời sản phẩm về iphone tương ứng ví dụ iphone 100, iphone 1000,…”

    – Dựa theo những gì các bạn làm ở phần 1, bước 2 mà các bạn viết hàm, ở đây tôi sẽ dùng hàm if với cú pháp như sau:

    =if(b6=”iphone”,ip,if(b6=”sony”,sn,chualam))

    – Bước 3: Ở cột “Tình trạng sản phẩm” thực hiện khá đơn giản, giống bước 1: ta bôi đen hết và chọn Data – Data Validation. Trong phần Allow chọn “List”, phần source ta viết chính tên của vùng dữ liệu ta đặt ở phần 1. Ở đây ta sẽ viết là “=tinhtrang”

    Với mục tiêu “Sự thành công của học viên là niềm tự hào của Đức Minh”, Công ty đào tạo kế toán và tin học Đức Minh là nơi đào tạo kế toán thực tế và tin học văn phòng uy tín và chuyên nghiệp nhất Hà Nội hiện nay. Đức Minh luôn sẵn sàng hỗ trợ hết mình vì học viên, luôn đồng hành cùng học viên trên bước đường đi tới thành công.

    Lịch học dạy kèm linh động từ thứ 2 đến thứ 7 hàng tuần cho tất cả các học viên:

    Bảng giá khóa học

    TỔ CHỨC THI VÀ CẤP CHỨNG CHỈ CỦA VIỆN KẾ TOÁN ĐỨC MINH

    Mọi chi tiết vui lòng liên hệ:

    HỌC VIỆN ĐÀO TẠO KẾ TOÁN – TIN HỌC ĐỨC MINH

    CN Đống Đa: Phòng 815, tòa 15 tầng – B14 đường Phạm Ngọc Thạch, Đống Đa, Hn. (tầng 1 là Techcombank và KFC- gửi xe đi vào ngõ 65 Phạm Ngọc Thạch)

    CN Cầu Giấy: Phòng B2T10 (Phòng B2 Tầng 10) – Tòa nhà 335 Cầu Giấy – HN (Tầng 1 là Siêu thị thời trang Fashion Mall)

    CN Linh Đàm: Phòng 404 – Chung cư CT4A1 – Đường nguyễn Hữu Thọ – Linh Đàm – Hoàng Mai Hà Nội. (Ngay đèn xanh đỏ cổng chào Linh Đàm, Tầng 1 siêu thị Bài Thơ, Highlands Cofee)

    CN Hà Đông: Phòng 1001 tầng 10, CT2 tòa nhà Fodacon (tầng 1 là siêu thị Coopmart, đối diện Học Viện An Ninh) – Trần Phú – Hà Đông

    --- Bài cũ hơn ---

  • 12 Đôi Dây Thần Kinh Sọ
  • Liệt Dây Thần Kinh Số 3 Có Nguy Hiểm Không? Điều Trị Ra Sao?
  • Dây Thần Kinh Là Gì? Vị Trí Của 12 Dây Thần Kinh Sọ Não Số 7, 5, 10, 3, 6
  • Bệnh Viêm Dây Thần Kinh Số 3
  • Chức Năng Của Dây Thần Kinh Số 5
  • Cách Sử Dụng Data Validation Trong Microsoft Excel 2013

    --- Bài mới hơn ---

  • Cấu Tạo Và Chức Năng Của Dạ Dày
  • Cấu Tạo Và Chức Năng Của Dạ Dày Trong Quá Trình Tiêu Hóa
  • Dạ Dày Là Gì? Cấu Tạo, Chức Năng Của Dạ Dày Người
  • Dạ Dày Của Người Có Cấu Tạo Thế Nào?
  • Dạ Dày Là Gì? Vai Trò Và Chức Năng Của Dạ Dày Trong Cơ Thể
  • Data Validation là tính năng hiệu quả trong Microsoft Excel khi bạn cần chia sẻ bảng tính với một nhiều người khác nhưng lại muốn giữ cho nội dung bảng tính thống nhất.

      1.Bạn có thể sử dụng tính năng Data Validation cho toàn bộ một hàng, một cột hoặc chỉ cho các ô riêng lẻ. Bài viết minh hoạ bằng việc áp dụng cho tất cả dữ liệu được nhập vào cột B.

      2 . Sau khi chọn cột B, bạn nhấp vào thẻ Data trong menu Ribbon rồi nhấn nút Data Validation trong nhóm Data Tools

      3 . Cửa sổ Data Validation hiện ra, bạn có thể thiết lập để Excel giới hạn dữ liệu trong các loại ngày tháng, số, số thập phân, thời gian hoặc có chiều dài nhất định bằng cách chọn trong thẻ Settings. Nếu bạn muốn tạo danh sách chọn dưới dạng trình đơn thả xuống, hãy chọn List.

      4 . Các lựa chọn cho trình đơn được nhập vào ô Source bằng một trong hai cách sau. Cách 1: gõ trực tiếp các lựa chọn riêng biệt và phân cách với nhau bằng dấu phẩy. Cách này khá cơ bản và hữu ích khi số lượng lựa chọn ít. Tuy nhiên, nếu bạn có nhiều lựa chọn và muốn quản lý chúng dễ dàng hơn, bạn nên sử dụng cách 2: gõ trước các lựa chọn vào một số ô gần nhau rồi dùng nút chọn vùng dữ liệu bên cạnh hộp Source để tham chiếu đến những ô này.

      5 . Tiếp theo, ta chuyển đến thẻ Input Message. Đây là tuỳ chọn để hiển thị một thông báo hướng dẫn gửi đến người dùng mỗi khi ô dữ liệu Data Validation được chọn.

      6 . Tương tự như thế, thẻ Error Alert sẽ hiển thị thông báo lỗi khi người người dùng nhập dữ liệu không phù hợp với yêu cầu của Data Validation. Bạn có thể nhập bật cứ nội dung nào cho các thông báo này nhưng tốt nhất là nên ngắn gọn và đầy đủ thông tin.

      Tất cả đã sẵn sàng, bây giờ một mũi nhỏ sẽ xuất hiện bên phải mỗi ô Data Validation khi ô này được chọn. Nếu bạn nhấp vào mũi tên, một trình đơn thả xuống sẽ mở ra để bạn chọn các chi tiết từ danh sách trong ô Source của thẻ Settings.

    --- Bài cũ hơn ---

  • Cách Chia Cột, Tạo Dropcap Trong Word 2003, 2007, 2010, 2013
  • Định Dạng Chữ Rơi, Hạ Dòng (Drop Cap)
  • Cách Thiết Lập Hộp Thoại Xổ Xuống Đẹp Mắt Cho Chức Năng Kiểm Duyệt Dữ Liệu Data Validation Trong Excel
  • Sử Dụng Data Validation Để Tạo List Và Kiểm Tra Dữ Liệu Nhập
  • Kiểm Soát Dữ Liệu Nhập Với Data Validation Trong Excel
  • Cách Thiết Lập Hộp Thoại Xổ Xuống Đẹp Mắt Cho Chức Năng Kiểm Duyệt Dữ Liệu Data Validation Trong Excel

    --- Bài mới hơn ---

  • Định Dạng Chữ Rơi, Hạ Dòng (Drop Cap)
  • Cách Chia Cột, Tạo Dropcap Trong Word 2003, 2007, 2010, 2013
  • Cách Sử Dụng Data Validation Trong Microsoft Excel 2013
  • Cấu Tạo Và Chức Năng Của Dạ Dày
  • Cấu Tạo Và Chức Năng Của Dạ Dày Trong Quá Trình Tiêu Hóa
  • Trong bài viết này, Blog Học Excel Online sẽ giới thiệu với bạn cách để tạo 1 hôp thoại xổ xuống cho chức năng Data Validation trong Excel. Ta cũng sẽ tìm hiểu về một số kỹ thuật nâng cao để hiển thị danh sách nhiều lựa chọn Data Validation thông qua hàm Indirect.

    Cách để tạo hộp thoại Data Validation xổ xuống trong Excel

    Bước 1: Bôi đen một ô tính hoặc vùng dữ liệu cần tạo hộp thoại Data Validation xổ xuống

    Bước 3: Trong tab Settings, chọn List

    Bước 4: Tiếp đến bạn điền danh sách các dữ liệu cần kiểm duyệt, ngăn cách nhau bởi dấu phẩy vào sau đó bấm OK

      Đối với thông báo dữ liệu cần nhập:

    Điền vào phần Title nội dung khái quát của thông báo

    Điền vào phần Input message nội dung thông báo đầy đủ về loại dữ liệu mà người dùng sẽ lựa chọn để nhập vào

    Chọn vào biểu tượng cảnh báo và điền nội dung khái quát của lỗi cũng như nội dung lỗi đầy đủ vào các mục Title và Short Description.

    Sau đó bấm OK

    Cách để tạo hộp thoại Data Validation xổ xuống cho vùng dữ liệu/vùng dữ liệu đã được đặt tên

    Đôi khi không phải lúc nào bạn cũng có thể tạo thủ công hộp thoại dữ liệu xổ xuống trong ô dữ liệu nguồn. Nếu trong quá trình nhập liệu sau này mà bạn cần phải thay đổi hay cập nhập thì việc này sẽ trở nên tương đối khó chịu.

    Thay vì đó, bạn nên liên kết đến 1 dải ô mới để dễ dàng cập nhật những thay đổi về nội dung dữ liệu

    Bước 1: Chọn 1 ô tính cần tạo hộp kiểm Data Validation

    Bước 2: Điền các giá trị của hộp kiểm vào 1 cột khác

    Bước 1: Chọn dải ô dữ liệu cần tham chiếu

    Cách tạo hộp thoại xổ xuống Data Validation có điều kiện ràng buộc

    Phần này ta sẽ tìm hiểu cách để tạo 1 hộp thoại linh động với hàm Indirect

    Ví dụ tiếp theo ta có dữ liệu được chia thành 2 loại: Đồ điện tử và Sách

    Bố trí bảng tham chiếu:

    Bước 1: Bố trí bảng tham chiếu dưới dạng như sau. Tiêu đề các mục nhỏ hơn cần được bố trí sang phải dưới dạng tiêu đề chính

    Bước 3: Làm tương tự với mục Electronics và Books

    Bạn vừa hoàn tất việc tạo hộp thoại xổ xuống đầu tiên, tiếp theo ta cần tạo thêm 1 hộp thoại nữa

    Làm tương tự đối với Books

    Ghi chú cần lưu ý

    Phương pháp trên có một số hạn chế như sau:

    • Tên vùng dữ liệu mà bạn đặt không được chứa ký tự khoảng trắng ở trong. Chẳng hạn, bạn không nên đặt tên dưới dạng “Mobile Phones” mà thay vào đó phải là “Mobile_Phones”
    • Bạn cũng không nên đặt tên dưới dạng FY15, FY16, Q116, Q216, v.v. bởi vì Excel sẽ mặc định rằng chúng là dải ô tính thông thường.

    Tìm hiểu thêm: Tổng hợp thủ thuật Excel hay

    Khi đó bạn có thể thấy được toàn bộ những ô tính Excel chứa hộp thoại xổ xuống Data Validation.

    Cách xóa hộp kiểm Data Validation trong Excel

    Bước 2: Thêm dữ liệu theo hàng dung để tham chiếu

    Do tính chất của một hộp thoại độc lập nên ta cần phải đi vòng để thực hiện được yêu cầu này:

    Bước 1: Sau khi thiết lập xong cho hộp thoại hỗn hợp Combo box, chọn 1 giá trị bất kì trong danh sách xổ xuống

    Bước 2: Bạn sẽ thấy có 1 dãy số trong ô liên kết

    Bước 3: Quay lại ô tính chứa dữ liệu tham chiếu và thêm dãy số trên vào đối tượng cần tham chiếu

    Bước 4: Chọn 1 ô gần với form và thực hiện việc tham chiếu bằng Vlookup thông qua dãy số trên

    Bước 5: Sử dụng công thức Vlookup trong thanh công thức.

    Học lập trình VBA trong Excel ở đâu?

    --- Bài cũ hơn ---

  • Sử Dụng Data Validation Để Tạo List Và Kiểm Tra Dữ Liệu Nhập
  • Kiểm Soát Dữ Liệu Nhập Với Data Validation Trong Excel
  • Quản Lý Hàng Hóa Bằng Data Validation Trong Excel Cực Hay
  • 12 Đôi Dây Thần Kinh Sọ
  • Liệt Dây Thần Kinh Số 3 Có Nguy Hiểm Không? Điều Trị Ra Sao?
  • 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)

    --- Bài mới hơn ---

  • Sử Dụng Chức Năng Data Validation Trong Excel
  • Chèn Text Box & Drop Cap Trong Microsoft Word
  • Làm Chữ Cái To Ở Đầu Dòng Trong Word 2022, 2013, 2010, 2007, 2003, Tạo
  • Chia Cột Và Tạo Drop Cap
  • Tiêu Hóa Ở Dạ Dày Đơn
  • 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:

    --- Bài cũ hơn ---

  • Cách Sử Dụng Offset Và Data Validation Để Tạo Liên Kết List Động Trong Excel
  • Liệt Dây Thần Kinh Số 7 Có Nguy Hiểm Không, Có Chữa Được Không?
  • Viêm (Liệt) Dây Thần Kinh Số 7, Những Điều Cần Biết
  • Đau Dây Thần Kinh Số V (Số 5): Triệu Chứng, Nguyên Nhân Và Cách Điều Trị
  • Bệnh Đau Dây Thần Kinh Số 5
  • Sử Dụng Data Validation Để Tạo List Và Kiểm Tra Dữ Liệu Nhập

    --- Bài mới hơn ---

  • Cách Thiết Lập Hộp Thoại Xổ Xuống Đẹp Mắt Cho Chức Năng Kiểm Duyệt Dữ Liệu Data Validation Trong Excel
  • Định Dạng Chữ Rơi, Hạ Dòng (Drop Cap)
  • Cách Chia Cột, Tạo Dropcap Trong Word 2003, 2007, 2010, 2013
  • Cách Sử Dụng Data Validation Trong Microsoft Excel 2013
  • Cấu Tạo Và Chức Năng Của Dạ Dày
  • Trong bài viết này, tôi giới thiệu tới các bạn cách sử dụng Data Validation để tạo List và Kiểm tra dữ liệu nhập (quy định dữ liệu nhập) trong bảng tính Excel. Để các bạn có thể nắm bắt được cách sử dụng chúng ta sẽ áp dụng ngay vào ví dụ cụ thể.

    Ví dụ chúng ta cần lập bảng thống kê như hình sau:

    Video hướng dẫn [Video Guide]

    Yêu cầu 1: Điền tên công ty chỉ được chọn 1 trong 5 công ty đã cho

    – Chúng ta sẽ tạo List với chức năng Data Validation

    Đến đây chúng ta đã thực hiện xong việc tạo List, nhấn chuột vào ô C3 và chọn tên xe từ button

    Yêu cầu 2: Sản phẩm chỉ chọn trong những sản phẩm tương ứng với công ty SX

    Bước 2: Tiếp tục thực hiện quét chọn cột tên xe của HONDA, BMWG, AUDI, FORD và đặt tên tương tự trên

    Đến đây đã thực hiện xong yêu cầu 2.

    Yêu cầu 3: Nhập cột số lượng là số nguyên dương

    Thực hiện như sau:

    – Quét vùng dữ liệu E3:E8 chọn thẻ Data, Data Validation, Thẻ Settings, mục Allow chọn Whole Number, mục Data chọn Greater than, mục Minimum nhập 0, nhấn OK

    – Tiến hành nhập số liệu

    Yêu cầu 4: Tính đơn giá

    – Chúng ta đã quen với hàm vlookup để dò tìm theo cột trong 1 bảng dữ liệu, với bài toán này chúng ta có 5 bảng tên xe, như vậy chúng ta cần kết hợp hàm indirect, như đã biết indirect trả về dữ liệu vùng tham chiếu, nên chúng ta thực hiện như sau:

    Bước 2: Đặt tên vùng tương tự cho bảng giá xe HONDA, BMWG, AUDI, FORD là (honda1, bmwg1,audi1,ford1)

    – Giải thích, hàm indirect là đối số thứ 2 trong hàm vlookup, hàm trả về bảng tham chiếu

    Yêu cầu 5: Tính Thành tiền

    – Thành tiền = Số lượng * Đơn giá

    Như vậy, yêu cầu đã được hoàn thành.

    Để quản lý các tên vùng dữ liệu (xóa, thêm, sửa. . .) các bạn có thể nhấn Ctr + F3 hộp thoại quản lý tên vùng hiển thị như hình sau:

    Có 1 lưu ý nhỏ các bạn cần nhớ, việc đặt tên vùng tránh trùng với 1 trong các ô địa chỉ có trong excel, trong bài này hãng bmw tôi để thêm chữ g là: bmwg để khi đặt tên vùng là bmwg và bmwg1 không bị trùng với cột bmw1 của excel.

    Các bạn tải file dữ liệu để thực hành TẠI ĐÂY

    Có thể bạn muốn xem (more videos) :

    --- Bài cũ hơn ---

  • Kiểm Soát Dữ Liệu Nhập Với Data Validation Trong Excel
  • Quản Lý Hàng Hóa Bằng Data Validation Trong Excel Cực Hay
  • 12 Đôi Dây Thần Kinh Sọ
  • Liệt Dây Thần Kinh Số 3 Có Nguy Hiểm Không? Điều Trị Ra Sao?
  • Dây Thần Kinh Là Gì? Vị Trí Của 12 Dây Thần Kinh Sọ Não Số 7, 5, 10, 3, 6
  • Cách Sử Dụng Offset Và Data Validation Để Tạo Liên Kết List Động Trong Excel

    --- Bài mới hơn ---

  • 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)
  • Sử Dụng Chức Năng Data Validation Trong Excel
  • Chèn Text Box & Drop Cap Trong Microsoft Word
  • Làm Chữ Cái To Ở Đầu Dòng Trong Word 2022, 2013, 2010, 2007, 2003, Tạo
  • Chia Cột Và Tạo Drop Cap
  • Ở bài trước chúng ta đã học cách tạo drop-down list có giá trị phụ thuộc một list khác bằng INDIRECT trong Data Validation, trong bài này chúng ta có một lựa chọn khác để tạo liên kết List động trong Excel bằng hàm OFFSET thay cho hàm INDIRECT. Trước tiên chúng ta hãy tìm hiểu về cách sử dụng hàm OFFSET

    Lưu ý: Để thực hiện theo hướng dẫn bài viết các bạn có thể tải file mẫu TẠI ĐÂY

    1. Cách sử dụng hàm OFFSET trong Excel

    Hàm Offset có công dụng: Trả về tham chiếu đến một vùng nào đó, cách một ô hoặc một dãy ô một khoảng cách với số dòng hoặc số cột được chỉ định trước. Chúng ta có thể chỉ định số dòng, số cột của vùng tham chiếu trả về.

    Cú pháp (syntax): =OFFSET(reference, rows, cols, height, width)

    • reference: đối số bắt buộc, là vùng tham chiếu làm cơ sở cho hàm (làm điểm xuất phát) để tạo vùng tham chiếu mới. reference phải chỉ đến một ô hoặc một dãy ô liên tục, nếu không hàm sẽ trả về lỗi #VALUE!.
    • rows:đối số bắt buộc, là số dòng bên trên hoặc bên dưới reference, tính từ ô đầu tiên (ô ở góc trên bên trái) của reference.
    • cols: đối số bắt buộc, là số cột bên trái hoặc bên phải reference, tính từ ô đầu tiên (ô ở góc trên bên trái) của reference.
    • height: đối số tự chọn, là số dòng của vùng tham chiếu cần trả về. Height phải là số dương.
    • width: đối số tự chọn, là số cột của vùng tham chiếu cần trả về. Width phải là số dương.
    • Nếu rows và cols làm cho tham chiếu trả về vượt ra ngoài phạm vi của một worksheet, hàm Offset sẽ báo lỗi #REF!
    • Nếu bỏ qua height và width, thì height và width sẽ có kích thước mặc định là height và width của reference.

    2. Kết hợp OFFSET trong Data validation để tạo List động

    Sau khi đã hiểu rõ cách sử dụng hàm OFFSET, bây giờ chúng ta sẽ sử dụng OFFSET thay cho INDIRECT trong Data validation để tạo List động trong Excel.

    Ví dụ cho bảng danh sách 1 loạt hãng xeloại xe như sau

    Bước 3: Chọn ô F4 trong thẻ Data chọn Data validation, trong thẻ settings mục Allow chọn List, mục Fomular nhập công thức như sau: =OFFSET($A$3,MATCH(E4,$A:$A,0)-3,1,COUNTIF($A:$A,E4),1)

    và nhấn OK, như hướng dẫn ở hình sau:

    Giải thích hàm: =OFFSET($A$3,MATCH(E4,$A:$A,0)-3,1,COUNTIF($A:$A,E4),1)

    Reference: điểm bắt đầu với tham chiếu là ô A3.

    Rows: Sử dụng hàm MATCH để tìm vị trí dòng đầu tiên của tên hãng xe (ô E4) trong danh sách hãng xe ở cột A của sheet, trừ đi 3 cho 2 ô trống A1,A2 và tiêu đề A3.

    N hập giá trị là “1” để di chuyển qua bên phải 1 cột (trong ví dụ là cột B).

    Height: Sử dụng COUNTIF để đếm số lần xuất hiện của tên hãng xe ở cột A, ví dụ như hãng Mercedes trả về 12, tức là vùng dữ liệu sẽ có chiều cao là 12 dòng.

    Width: Vùng dữ liệu rộng 1 cột (cột B)

    Sau thao tác này ta được kết quả như sau:

    Kết thúc bước 3, ta đã hoàn thành việc tạo danh sách LIST động sử dụng OFFSET trong Data validation và thu được kết quả như hình trên. Đây sẽ là một lựa chọn khác để tạo danh sách động ngoài cách sử dụng INDIRECT trong Data validation.Thực tế, nếu dữ liệu lớn thì việc sử dụng indirect để tạo list trong data validation sẽ tốt hơn cho việc xử lý dữ liệu.

    --- Bài cũ hơn ---

  • Liệt Dây Thần Kinh Số 7 Có Nguy Hiểm Không, Có Chữa Được Không?
  • Viêm (Liệt) Dây Thần Kinh Số 7, Những Điều Cần Biết
  • Đau Dây Thần Kinh Số V (Số 5): Triệu Chứng, Nguyên Nhân Và Cách Điều Trị
  • Bệnh Đau Dây Thần Kinh Số 5
  • Chức Năng Của Dây Thần Kinh Số 5 Là Gì ?
  • Web hay
  • Links hay
  • Push
  • Chủ đề top 10
  • Chủ đề top 20
  • Chủ đề top 30
  • Chủ đề top 40
  • Chủ đề top 50
  • Chủ đề top 60
  • Chủ đề top 70
  • Chủ đề top 80
  • Chủ đề top 90
  • Chủ đề top 100
  • Bài viết top 10
  • Bài viết top 20
  • Bài viết top 30
  • Bài viết top 40
  • Bài viết top 50
  • Bài viết top 60
  • Bài viết top 70
  • Bài viết top 80
  • Bài viết top 90
  • Bài viết top 100
  • CẦM ĐỒ TẠI F88
    15 PHÚT DUYỆT
    NHẬN TIỀN NGAY

    VAY TIỀN NHANH
    LÊN ĐẾN 10 TRIỆU
    CHỈ CẦN CMND

    ×