Excel Tips ในบทความนี้จะนำเสนอวิธีการสร้างรายการข้อมูลที่มีความสัมพันธ์กัน (รายการย่อย) ยกตัวอย่างการใช้งาน เช่น รายชื่ออำเภอ
ที่ต้องสัมพันธ์กับจังหวัดที่เลือก การกำหนดหมวดหมู่ค่าใช้จ่ายเพื่อให้สะดวกในการเลือกรายการ
การสร้างแบบฟอร์มใบแจ้งหนี้ และใบเสนอราคาซึ่งต้องมีความสัมพันธ์ระหว่างหมวดหมู่สินค้า
เป็นต้น การสร้างรายการข้อมูลที่มีความสัมพันธ์เหล่านี้ทำได้ไม่ยากในการใช้ excel
โดยเราจะอาศัยสูตรใน
excel : Indirect ในการอ้างถึงรายการย่อย ส่วนการสร้างรายการข้อมูลใน
excel ยังคงใช้ Data validation เช่นเดิม
ขอยกตัวอย่างการสร้างรายการค่าใช้จ่ายสำหรับลงบัญชีรายรับรายจ่าย ดังแสดงในภาพที่ 1
ภาพที่ 1 ตัวอย่างข้อมูลใช้ใน excel
จากภาพที่ 1 พบว่ารายการหลักคือ ประเภทค่าใช้จ่าย มี 4 รายการคือ
ค่าเดินทาง ค่าอาหาร ค่าการศึกษา และค่าใช้จ่ายอื่นๆ
ในขณะที่ในแต่ละรายการหลักมีรายการย่อย ตัวอย่างเช่น ค่าอาหาร มีรายการย่อยคือ MK
เป็นต้น
ในลำดับถัดไปเราจะทำการสร้างรายการข้อมูล โดยเมื่อผู้ใช้เลือกรายการหลักแล้ว
ในรายการย่อยจะปรากฏรายการย่อยที่มีความสัมพันธ์กัน ทำให้ลดความผิดพลาดของการเลือกและบันทึกข้อมูลได้
การสร้างรายการข้อมูลหลัก
สมมุติให้รายการข้อมูลหลักถูกกำหนดขึ้นในเซลล์ A10 เราจะดำเนินการดังภาพที่
2 โดยในส่วนของ Source จะเขียนสูตร excel ได้เป็น =$B$2:$E$2
ภาพที่ 2 การสร้างรายการข้อมูลหลักใน excel
การสร้างรายการข้อมูลย่อย
เริ่มจากใช้ excel สร้างชื่อข้อมูลแบบคงที่ให้กับรายการข้อมูลย่อยแต่ละรายการ
โดยกำหนดชื่อข้อมูลเป็นชื่อรายการหลัก ตัวอย่างเช่น
สร้างชื่อข้อมูลแบบคงที่ของค่าเดินทาง การกำหนดชื่อข้อมูลใน excel แสดงได้ดังภาพที่ 3
ภาพที่ 3 การสร้างชื่อข้อมูลใน excel
ทำนองเดียวกัน
ค่าอาหาร ก็จะกำหนดชื่อข้อมูลเป็น ค่าอาหาร สูตร excel อ้างถึง $C$3
ค่าการศึกษา ก็จะกำหนดชื่อข้อมูลเป็น ค่าการศึกษา สูตร excel อ้างถึง $D$3:$D$4
ค่าใช้จ่ายอื่นๆ ก็จะกำหนดชื่อข้อมูลเป็น ค่าใช้จ่ายอื่นๆ สูตร excel อ้างถึง $E$3:$E$4
ใช้ excel กำหนดความถูกต้องของข้อมูลใน B10 เพื่อกำหนดรายการข้อมูลย่อย ดังแสดงในภาพที่ 4
โดยกำหนดสูตร excel ในแหล่งข้อมูลเป็น =Indirect($A$10)
ซึ่งหมายถึงให้อ้างชื่อข้อมูลจากรายการหลักนั่นเอง
ภาพที่ 4 การสร้างรายการข้อมูลย่อยใน excel
ผลการรันสูตรใน excel เพื่อช่วยเลือกรายการข้อมูลแสดงได้ดังภาพที่ 5
ภาพที่ 5 ผลการรันสูตร excel เพื่อแสดงการเลือกรายการย่อย
มาถึงตรงนี้ท่านผู้อ่านก็สามารถสร้างรายการข้อมูลที่สัมพันธ์กันได้แล้ว
โดยหากท่านต้องการเพิ่มข้อมูลย่อย
ก็สามารถทำได้โดยการเพิ่มในแถวลำดับถัดไปจากข้อมูลตัวล่าสุด
แต่ต้องจำไว้ว่าทุกครั้งที่มีการเพิ่มข้อมูลท่านผู้อ่านจะต้องทำการแก้ไขสูตร excel การอ้างถึง ในรายการย่อยที่ปรับปรุง ตัวอย่างเช่น เพิ่มรายการย่อยค่าอาหารอีก 2 รายการคือ
ร้านข้าวต้ม และ โคขุน ใน C4 และ C5 ตามลำดับ ท่านจะต้องแก้ไขสูตร excel สำหรับการอ้างถึงในข้อมูลคงที่ชื่อค่าอาหารจาก
$C$3 เป็น $C$3:$C$5 เป็นต้น
สำหรับการปรับปรุงสูตร excel เพื่อให้รายการข้อมูลย่อยมีการอัพเดตข้อมูลแบบไดนามิกส์จะนำเสนอในโอกาสต่อไปครับ
สวัสดีครับ
2 ความคิดเห็น:
ว้าย ทำเป็นแล้ว ขอบคุณมากคะ
ดีใจด้วยครับ
แสดงความคิดเห็น