Snasui Excel Forum

มีปัญหามาปรึกษาครับ ถ้าจะค้นหาแบบมีเงื่อนไขใน vlookup จะกำหนดยังไง
ผมต้องเอาค่า น้ำหนัก และ index ไปค้นหาน้ำหนักตามอายุ
ส่วนสูง และ index ไปค้นหา ส่วนสูงตามอายุ ครับ




ต้องนำค่า น้ำหนัก และ index ในแต่ละคนไปค้นหาน้ำหนักตามอายุ เช่นอย่างคนแรกก็จะไปค้นหาที่ m13.04 จะได้ค่าน้ำหนักตามเกณฑ์ในช่องน้ำหนักตามเกณฑ์อายุ

ขอความร่วมมือแสดงตัวอย่างคำตอบในแฟ้มด้วยครับว่าจะเป็นอย่างไร อย่าแนบแต่แฟ้มเพียงอย่างเดียว

เขียนคำถามในแฟ้มด้วยครับ

เห็นตารางของคุณแล้ว ต้องแก้ไขหลายจุดครับ

จุดแรกคือ วันเดือนปีเกิดครับ

คุณป้อนวันเดือนปีโดยใช้ปีพุุทธศักราช ซึ่งไม่ถูกต้อง ทำให้อายุของข้อมูลนักเรียนคนแรกมีค่าเป็น 13 ปี ทั้ง ๆ ที่มีคำนำหน้าเป็น นาย ครับ มันขัดแย้งกัน

การกรอกวันเดือนปี ต้องใช้ปีคริสตศักราชเท่านั้น

การแก้ไขกระทำดังนี้
1 คลิกเซลล์ F5
2 กดแป้น Ctrl+Shift+แป้นเลื่อนเคอร์เซอร์ลง
3 กดแป้น Ctrl+H
4 ช่อง Find What พิมพ์ 2543
5 ช่อง Replace with พิมพ์ b2543
6 คลิกปุ่ม Replace All
กระทำซ้ำข้อ 4 - 6 ไปจนกว่าจะหมด

เวลากรอกวันเดือนปี ให้กรอกด้วยปีคริสตศักราช หรือ กรอกในรูป d/m/bbbbb เช่น 5/2/b2554 เพื่อให้ไมโครซอฟต์เอ็กเซลแปลงปีพุทธศักราชไปเป็นปีคริสตศักราชให้ครับ

เห็นคุณมีการใช้สูตร TODAY ซ้ำกันหลายจุด รวมถึงเซลล์ F2 ด้วย ดังนัั้น แก้ไขสูตรในเซลล์ที่มีสูตร Today ไปดึงค่าจากเซลล์ F2 มาใช้งานครับ เพื่อเพิ่มความเร็วในการคำนวณ

จุดที่ 2 คือหัวตาราง
หัวตารางมีการเมิร์จเซลล์ (ผสานเซลล์) ทำให้ไม่สามารถใช้คำสั่ง Filter ได้ ดังนั้นยกเลิกการเมิร์จเซลล์ก่อน แล้ว แทรกบรรทัดเปล่า จากนั้นจึงซ่อน แล้วใช้คำสั่ง AutoFilter

จุดที่ 3 ข้อมูลชื่อ นามสกุล ปะปนกัน
ควรแยกชื่อ นามสกุล ออกเป็นฟิลด์ คำนำหน้านาม ชื่อ นามสกุล เพื่อความสะดวกต่อการแก้ไข และเพื่อความสะดวกต่อการจำแนกเพศครับ
ที่ฟิลด์ คำนำหน้านามให้ใช้ Data Validation แบบ ลิสต์ เพื่อใช้สำหรับคลิกเลือกและกำหนดค่าเองครับ

ขอบคุณที่ให้ความร่วมมือ

แก้ไขไฟล์ต้นฉบับในแฟ้มแล้วนะครับ

nattasiray wrote:ขอความร่วมมือแสดงตัวอย่างคำตอบในแฟ้มด้วยครับว่าจะเป็นอย่างไร อย่าแนบแต่แฟ้มเพียงอย่างเดียว

เขียนคำถามในแฟ้มด้วยครับ

ขอบคุณที่ให้ความร่วมมือ

ครับขอโทษครับ

อย่างข้อมูลคนที่ 1 ต้องนำค่า น้ำหนัก และ index ไปค้นหาน้ำหนักตามอายุ เช่นอย่างคนแรกก็จะไปค้นหาที่ m13.04 จะได้ค่าน้ำหนักตามเกณฑ์ในช่องน้ำหนักตามเกณฑ์อายุอะครับ

ผมแก้ไขตารางให้แล้ว พร้อมกับใช้สูตร OFFSET MATCH TEXT ในลักษณะสูตรอาร์เรย์ เพราะต้องตรวจสอบข้อมูลจากการเชื่อมข้อมูลเพื่อทำเป็นคีย์ผสมสำหรับสืบค้น จึงไม่สามารถใช้สูตร VLOOKUP ได้

ในตารางแสดงผลลัพธ์ผมใช้ Conditional Formatting เพื่อเน้นผลลัพธ์ให้ตรงกับตารางข้อกำหนดผลลัพธ์ หากผลลัพธ์เปลี่ยน สีเซลล์ก็เปลี่ยนตามด้วย สะดวกต่อการตรวจสอบ

ตารางแปลผลลัพธ์ควรนำข้อมูลมาพิมพ์ให้ต่อเนื่องกัน ไม่ใช่แยกกัน มิฉะนั้นสูตรจะยุ่งยากกว่านี้

วันเดือนปีใช้ Data Validation ตรวจสอบว่าผู้ใช้กรอกวันเดือนปีโดยใช้ปีพุทธศักราชหรือไม่ ส่วนที่ฟิลด์ชื่อ นามสกุล ก็ใช้ Data Validation แบบลิสต์ เพื่อบังคับให้ผู้ใช้คลิกเลือกรายการคำนำหน้าแทนการพิมพ์เข้าไป

เคยใช้ Conditional Formatting หรือไม่ครับ ถ้าไม่เคย ต้องหัดใช้นะครับ

งานของคุณเป็นฐานข้อมูลบนไมโครซอฟต์เอ็กเซล ลองหาหนังสือจัดการฐานข้อมูลด้วยไมโครซอฟต์เอ็กเซล 2010 มาอ่านนะครับ

ใช้ได้หรือไม่ครับแจ้งกลับมาด้วยครับ

nattasiray wrote:ใช้ได้หรือไม่ครับแจ้งกลับมาด้วยครับ

ขอบคุณมากครับ เดี๋ยวลองเปิดดูก่อนนะครับ

nattasiray wrote:ใช้ได้หรือไม่ครับแจ้งกลับมาด้วยครับ

ใช้ได้ครับ แต่ผมยังง งงอยู่อะครับ เกี่ยวกับความหมายสูตรที่ใช้
=IFERROR(OFFSET($W$4,MATCH($E5&"-"&TEXT($H5,"000.00")&"-"&TEXT($I5,"000.00"),$T$5:$T$374&TEXT($U$5:$U$374,"000.00")&TEXT($V$5:$V$374,"000.00"),1),0),"แปลผลไม่ได้")


มันมีความหมายว่ายังไงอะครับ ผมลองดัดแปลงแก้ไขในช่องส่วนสูงแล้วแต่มันแปลผลไม่ได้อะครับ ขอความกรุณาช่วยอธิบายหน่อยนะครับ อาจารย์

จากสูตร

=IFERROR(OFFSET($W$4,MATCH($E5&"-"&TEXT($H5,"000.00")&"-"&TEXT($I5,"000.00"),$T$5:$T$374&TEXT($U$5:$U$374,"000.00")&TEXT($V$5:$V$374,"000.00"),1),0),"แปลผลไม่ได้")

แปลว่า ถ้า OFFSET($W$4,MATCH($E5&"-"&TEXT($H5,"000.00")&"-"&TEXT($I5,"000.00"),$T$5:$T$374&TEXT($U$5:$U$374,"000.00")&TEXT($V$5:$V$374,"000.00"),1),0) เป็นค่าผิดพลาดให้แสดงคำว่า แปลผลไม่ได้

ถ้าไม่ผิดพลาดให้แสดงผลลัพธ์ของ OFFSET($W$4,MATCH($E5&"-"&TEXT($H5,"000.00")&"-"&TEXT($I5,"000.00"),$T$5:$T$374&TEXT($U$5:$U$374,"000.00")&TEXT($V$5:$V$374,"000.00"),1),0)

จากสูตร

OFFSET($W$4,MATCH($E5&"-"&TEXT($H5,"000.00")&"-"&TEXT($I5,"000.00"),$T$5:$T$374&TEXT($U$5:$U$374,"000.00")&TEXT($V$5:$V$374,"000.00"),1),0)

แปลว่า จากตำแหน่ง $W$4 ให้ลงไปอีกกี่บรรทัดขึ้นอยู่กับผลลัพธ์ของ MATCH($E5&"-"&TEXT($H5,"000.00")&"-"&TEXT($I5,"000.00"),$T$5:$T$374&TEXT($U$5:$U$374,"000.00")&TEXT($V$5:$V$374,"000.00"),1) เช่นหากผลลัพธ์เป็น 3 ก็จะแปลว่าจาก $W$4 ให้ลงไป 3 บรรทัด ซึ่งก็คือ W7

จากสูตร

MATCH($E5&"-"&TEXT($H5,"000.00")&"-"&TEXT($I5,"000.00"),$T$5:$T$374&TEXT($U$5:$U$374,"000.00")&TEXT($V$5:$V$374,"000.00"),1)

แปลว่า ให้หาว่า $E5&"-"&TEXT($H5,"000.00")&"-"&TEXT($I5,"000.00") อยู่ในลำดับที่เท่าไรของ $T$5:$T$374&TEXT($U$5:$U$374,"000.00")&TEXT($V$5:$V$374,"000.00")

สูตรดังกล่าวเป็นสูตร Array ค่อนข้างยากต่อการทำความเข้าใจสำหรับมือใหม่ครับ