snasui wrote: ↑Sat Mar 17, 2018 9:11 am
ตัวอย่างสูตรที่ F7 ครับ
=LARGE(INDEX(Data!$C$7:$D$17,0,MATCH(TRIM(LOOKUP(CHAR(255),$F$5:F5)),Data!$C$6:$D$6,0)),RIGHT(F$6)+0)
Enter > Copy ไปด้านขวา
ส่วน Top Pond ให้กรอกคำตอบเป็นตัวอย่างพร้อมอธิบายว่าคิดอย่างไรจึงได้ค่าเท่านั้นมาด้วยจะได้สะดวกในการทำความเข้าใจครับ
ลองศึกษาตามด้านล่าง การอ่านสูตรให้อ่านจากด้านนอกเข้าด้านใน การแปลสูตรให้แปลจากด้านในออกด้านนอก วิธีการอ่านให้อ่านจากบนลงล่างไปก่อนแล้วอ่านย้อนกลับเพื่อสร้างความเข้าใจที่เพิ่มขึ้น
LARGE(INDEX(Data!$C$7:$D$17,0,MATCH(TRIM(LOOKUP(CHAR(255),$F$5:F5)),Data!$C$6:$D$6,0)),RIGHT(F$6)+0) แปลว่าให้ค่าที่ใหญ่ที่สุดในช่วง INDEX(Data!$C$7:$D$17,0,MATCH(TRIM(LOOKUP(CHAR(255),$F$5:F5)),Data!$C$6:$D$6,0)) โดยนำลำดับที่จากผลลัพธ์ของ RIGHT(F$6)+0 มาแสดง
Large ใช้แสดงค่าที่ใหญ่ที่สุดในลำดับที่ที่กำหนด เช่น ใหญ่ที่สุดในลำดับที่ 1, 2, 3 เป็นต้น หากผลลัพธ์ของ RIGHT(F$6)+0 คือ 1 จะแปลว่า ให้หาค่าที่ใหญ่ที่สุดในลำดับที่ 1 ของชุดข้อมูลที่เป็นผลลัพธ์ของ INDEX(Data!$C$7:$D$17,0,MATCH(TRIM(LOOKUP(CHAR(255),$F$5:F5)),Data!$C$6:$D$6,0))
RIGHT(F$6)+0 คือการตัดอักขระด้านท้ายของ F6 มา 1 อักขระแล้วบวกด้วย 0 ที่ต้องบวกด้วย 0 เพื่อแปลงกลับให้เป็นตัวเลข เนื่องจากอักขระที่ได้จากการตัดข้อความจะมีสภาพเป็น Text เสมอ
จากสูตร INDEX(Data!$C$7:$D$17,0,MATCH(TRIM(LOOKUP(CHAR(255),$F$5:F5)),Data!$C$6:$D$6,0)) แปลว่า จากช่วง Data!$C$7:$D$17 ให้นำคอลัมน์ที่เป็นผลลัพธ์ของ MATCH(TRIM(LOOKUP(CHAR(255),$F$5:F5)),Data!$C$6:$D$6,0) มาแสดง
Index คือสูตรที่แสดงค่าในพื้นที่ใด ๆ โดยมีการกำหนดแนวบรรทัด แนวคอลัมน์เอาไว้ให้ หากกำหนดค่าบรรทัดและค่าคอลัมน์ สูตรนี้จะไปดึงค่าตรงจุดตัดระหว่างบรรทัดและคอลัมน์ที่กำหนด เช่น Index(A1:C3,2,2) จะเป็นการนำค่าในบรรทัดที่ 2 คอลัมน์ที่ 2 ของช่วง A1:C3 มาแสดงคำตอบคือค่าใน B2
แต่หากไม่กำหนดค่าบรรทัดแสดงว่าให้นำมาแสดงทั้งคอลัมน์ เช่น Index(A1:C3,0,2) จะเป็นการนำคอลัมน์ที่ 2 มาใช้ทั้งคอลัมน์ สูตรแบบนี้มักจะต้องมีสูตรอื่นมาครอบเพื่อหาค่าที่ต้องการอีกทอดหนึ่ง เช่น Large ตามกระทู้นี้
จากสูตร MATCH(TRIM(LOOKUP(CHAR(255),$F$5:F5)),Data!$C$6:$D$6,0)) เป็นการหาว่า TRIM(LOOKUP(CHAR(255),$F$5:F5)) อยู่ในลำดับที่เท่าไรของช่วง Data!$C$6:$D$6
จากสูตร LOOKUP(CHAR(255),$F$5:F5) เป็นการหาอักขระสุดท้ายในช่วง $F$5:F5 ที่ต้องหาอักขระสุดท้ายเพราะว่ามีการคีย์เว้นเซลล์ หากคัดลอกสูตรไปด้านขวาช่วง $F$5:F5 จะเปลี่ยนแปลงขนาดไปเรื่อย ๆ เช่น $F$5:Z5 หมายถึงหาอักขระสุดท้ายที่พบในช่วง $F$5:Z5
Trim ที่ครอบอยู่ด้านนอกเป็นการตัดอักขระของผลลัพธ์ที่ได้ที่ไม่จำเป็นทิ้งไป อักขระดังกล่าวคือ วรรคหน้าข้อความ วรรคหลังข้อความ วรรคตรงกลางข้อความที่กิน 1 วรรค
การคีย์ข้อความเดียวกันจึงต้องคีย์ให้เหมือนกัน อย่าวรรคหน้า วรรคกลาง วรรคหลังให้แตกต่างกันเพราะต้องใช้สูตรซับซ้อนยิ่งขึ้น