วันศุกร์ที่ 28 กันยายน พ.ศ. 2555

สร้าง Drop Down List ด้วย Data Validation ใน Excel


การใช้ Data Validation สร้าง Drop Down Control List ใน MS Excel เป็นการสร้างลิสต์บ๊อกที่ง่าย และโดยทั่วไปมักใช้กับข้อมูลที่ไม่มีการเพิ่มเติมหรือเปลี่ยนแปลงมากนัก แต่อันที่จริงก็มีวิธีที่ทำให้เราสามารถเพิ่มข้อมูลในลิสต์ได้สะดวกด้วยเช่นกัน

ข้อดีของการใช้ drop down list ที่สร้างจาก data validation คือ
  • เป็นรูปแบบที่สร้างได้อย่างง่าย โดยกำหนดให้ user เพียงใช้เม้าส์คลิกเลือกข้อมูลในลิสต์

  • ลดความผิดพลาดจากการคีย์อินข้อมูล

  • สามารถใช้ควบคุมขอบเขตของข้อมูลที่เราต้องการใช้บันทึกจริง

  • ไม่จำเป็นที่จะต้องสร้างลิสต์จากข้อมูลที่อยู่บน Work sheet เดี่ยวกัน

  • สามารถเพิ่มลิสต์ใน Drop down ได้
จะขอแบ่งวิธีเพิ่ม List Control Box ออกเป็น 3 ส่วน


Part 1 สร้าง Drop Down List บน Worksheet เดียวกัน
  1. เลือก เซลส์ที่ต้องการวางลิสต์บ็อก แล้วไปที่ Menu เลือก Data Validation

  2. Data Validation Menu

  3. Excel จะแสดงกล่องโต้ตอบ ที่แทป Setting เป็นตัวกำหนดการใช้ Validation criteria ให้กำหนดตามนี้
    1. หัวข้อ Allow:- เลือกเป็น List
    2. หัวข้อ Source:- ใส่ cell area ที่ต้องการให้อยู่ใน List ในรูป คือ =$D$5:$D$13 (หมายถึง เขตพื้นที่ตั้งแต่ D5 ถึง D13 และการใส่ $ เพื่อล็อคคอลัมภ์และแถวของพื้นที่) หรืออาจใช้เม้าส์คลิกเลือกพื้นที่ที่เราต้องการก็ได้ จากนั้น ok

  4. sett validation criteria box

  5. จากภาพจะเห็นว่าข้อมูลที่เราเลือกทั้งหมดจะอยู่ในลิสต์บ็อกทั้งหมด เราสามารถเลือกข้อมูลจากลิสต์ได้และสามารถ copy ลงแถวอื่นๆ ได้เลย

  6. add data from drop down list


Part 2 สร้าง Drop Down List ต่างหรือข้าม Worksheet
  1. ก่อนอื่นเราต้องตั้งชื่อให้กับ Cell range ที่ต้องการใช้เป็นลิสต์ใน drop down ไปที่เมนู Formula เลือก Name Manager จากนั้นคลิกเลือก New ที่กล่องโต้ตอบ
  2. Name Manager Menu
    Define New Name

  3. ที่กล่องโต้ตอบ New Name ทำตามนี้
    1. หัวข้อ Name: ใส่ชื่อสำหรับพื้นที่เซลส์ที่เราต้องการ จากรูปใช้ชื่อ Product
    2. หัวข้อ Refer to: คือพื้นที่เซลส์ จากรูป เป็น ='Data validation1'!$D$5:$D$13 (หมายถึงเขตพื้นที่ตั้งแต่ D5 ถึง D13 ซึ่งอยู่อีกเวริ์คชีทหนึ่งที่ชื่อว่า Data validation1

  4. Set New Name in Name Manager

  5. หลังจากนั้นเราใช้วิธีสร้าง Data Validation เหมือน Part 1 เพียงแต่ในหัวข้อ Source: ใส่ชื่อพื้นที่เซลส์ที่เราตั้งไว้ คือ =Product และเมื่อต้องการเพิ่มข้อมูลสินค้าให้ใช้วิธี Insert/แทรกรายการลงในตารางข้อมูลที่เราได้ตั้งชื่อไว้ หรือ Edit ที่ Name Manager

  6. Input =Product in source of Data validation
    Drop down list


Part 3 Drop Down List ที่สามารถเพิ่มข้อมูลหลักได้โดยไม่ต้อง Insert
ที่ Refer to: ใน Name Manager ใช้ฟังก์ชั่น OFFSET  แทนค่าเดิมจากตัวอย่างใน Part 2 ด้วย
=OFFSET('Data validation1'!$D$5,0,0,COUNTA('Data validation1'!$D:$D),1)

Define Name with Offset Function




Reactions:

4 ความคิดเห็น:

  1. สอบถามหน่อยค่ะ
    พอทำเเล้วข้อมูล price ไม่ยอมมา มาเเค่ product เเวเดียวอ่าค่ะ

    ตอบลบ
    คำตอบ
    1. proice มีสูตรรองรับอยู่ค่ะ ใช้ vlookup ราคาจาก product

      ลบ
    2. พอจะมีตัวอย่าง การกำหนดสูตร vlookup จาก Droupdow list ไหมค่ะ
      ขอบคุณนะคะ

      ลบ
    3. ดูที่นี่ ค่ะ
      https://jumkesinee.blogspot.com/2012/08/How-to-Using-Vlookup-with-different-worksheets.html

      ลบ