ดึงข้อมูลหาค่าซ้ำ และเรียงข้อมูล

[March201711]

คำถามที่ 1. ถ้าจะดึงข้อมูลหน้า Trd2 มาแสดงที่ Column D312 และ E312 ต้องใช้สูตรดึงอย่างไรคะ เพราะข้อมูลเยอะต้องมาแก้มือเองทุกครั้งค่ะ (high light สีส้ม)

คำถามที่ 2.ถ้าจะดึงข้อมูล Column A-F โดยดูที่ Vol (column F) ว่ามีค่าไหมแล้วให้เรียงข้อมูลมาแสดงที่ cell A322 โดยเรียงข้อมูล ID จากน้อยไปหามาก

[logic]

ลองดูครับ

D312 =INDEX('Trd2'!E$6:E$65,AGGREGATE(15,6,(ROW('Trd2'!$B$6:$B$65)-ROW('Trd2'!$B$6)+1)/('Trd2'!$B$6:$B$65<>"")/('Trd2'!$B$6:$B$65='Trd2'!$B$7:$B$66),1)) คัดลอกไป E312

A322 =AGGREGATE(15,6,1/($F$3:$F$300<>0)*$A$3:$A$300,ROWS(A$322:A322)) คัดลอกสูตรลง

[March201711]

อีกนิดนึงคะ ทำไม vlookup column A322 ข้อมูลไม่ดึงตามที่เลือกคะ ขึ้น #N/A ทั้งหมดเลย

ส่วนใหญ่ข้อมูลจะได้เป็น Text แล้วมาทำเป็นPivot เวลาดึงข้อมูลมาเป็น Text ไปด้วย ต้องปรับแก้อย่างไรคะให้เป็นตัวเลขโดยใช้สูตรค่ะ

[snasui]

ตัวอย่างการปรับสูตรที่ A322 ครับ

=AGGREGATE(15,6,1/($F$3:$F$300<>0)*$A$3:$A$300,ROWS(A$322:A322))&""

Enter > Copy ลงด้านล่าง

[March201711]

ช่วยอธิบายสูตรได้ไหมคะ เผื่อไปประยุกต์ใช้งานอื่นต่อค่ะ

[snasui]

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

[March201711]

สูตรที่สามารถหาค่าซ้ำได้ตรงนี้ค่ะ ดูยังไงก็ไม่เข้าใจค่ะ รู้สึกยากจัง

D312 =INDEX('Trd2'!E$6:E$65,AGGREGATE(15,6,(ROW('Trd2'!$B$6:$B$65)-ROW('Trd2'!$B$6)+1)/('Trd2'!$B$6:$B$65<>"")/('Trd2'!$B$6:$B$65='Trd2'!$B$7:$B$66),1)) คัดลอกไป E312

และสูตรนี้สามารถเรียงจากน้อยไปมาก อย่างไร ถ้าคิดกลับกันจะเรียงจากมากไปน้อยได้ไหมค่ะ

A322 =AGGREGATE(15,6,1/($F$3:$F$300<>0)*$A$3:$A$300,ROWS(A$322:A322)) คัดลอกสูตรลง

[snasui]

เพื่อสะดวกในการทำความเข้าใจทุกสูตรของ Excel ให้ใช้หลักการนี้เสมอไปครับคือ

1. ปรับข้อมูลให้เหลือเพียงไม่กี่บรรทัดและให้เหลือข้อมูลที่เข้าเงื่อนไข
2. ใช้เครื่องมือเช่น F9, Evaluate Formula, Function Argument เข้ามาช่วย
3. การแปลสูตรให้ง่ายต่อการทำความเข้าใจ จะแปลจากสูตรที่ครอบอยู่ด้านนอกไปยังสูตรที่อยู่ด้านใน

ยกตัวอย่างสูตรเดิมผมปรับให้เหลือ 3 บรรทัด และจากสูตรในรูป ได้เปลี่ยนเครื่องหมาย / มาเป็นเครื่องหมาย * เพื่อจะได้สะดวกแก่การทำความเข้าใจเมื่อตอนแปลสูตรด้วย F9 นั่นคือ

Desc.png

=INDEX('Trd2'!D$2:D$4,AGGREGATE(15,6,(ROW('Trd2'!$A$2:$A$4)-ROW('Trd2'!$A$2)+1)/(('Trd2'!$A$2:$A$4<>"")*('Trd2'!$A$2:$A$4='Trd2'!$A$3:$A$5)),1))

การแปลที่สะดวกต่อการทำความเข้าใจคือให้แปลจากสูตรนอกสุดไปด้านใน

ฟังก์ชั่นนอกสุดคือ Index ไวยากรณ์คือ
=index(array,row_num)
แปลว่า จากช่วง array ให้นำลำดับที่ row_num มาแสดง

เทียบสูตรกับไวยากรณ์ด้านบนจะได้เป็น

array คือ 'Trd2'!D$2:D$4
row_num คือ AGGREGATE(15,6,(ROW('Trd2'!$A$2:$A$4)-ROW('Trd2'!$A$2)+1)/(('Trd2'!$A$2:$A$4<>"")*('Trd2'!$A$2:$A$4='Trd2'!$A$3:$A$5)),1)

จาก row_num ของ Index ใช้ฟังก์ชั่น Aggregate เข้ามาช่วย ไวยากรณ์ของ Aggregate ที่นำมาใช้คือ
=aggregate(function_num,options,array,k)

แปลว่า ให้หาผลลัพธ์ของ array ตาม function_num, options และ k ขึ้นอยู่กับว่าทั้งสามส่วนประกอบนี้เป็นอะไร ที่นำมาใช้ในสูตรนี้คือ 15, 6 และ 1 ตามลำดับ โดย
15 และ 1 หมายถึงให้หาค่าที่น้อยที่สุดในลำดับที่ 1
6 หมายถึง ไม่คำนึงค่าผิดพลาด

เมื่อเทียบสูตรกับไวยากรณ์ด้านบนจะเห็นว่าส่วนประกอบอื่นมีการใช้แบบตรงไปตรงมา โดยในส่วนของ array ได้เป็น
array คือ (ROW('Trd2'!$A$2:$A$4)-ROW('Trd2'!$A$2)+1)/(('Trd2'!$A$2:$A$4<>"")*('Trd2'!$A$2:$A$4='Trd2'!$A$3:$A$5))

เมื่อกระจายออกมาแล้วสูตรพวกนี้ไม่ใช่ของยาก หากต้องการทราบว่าแต่ละส่วนประกอบคืออะไรให้ลากเมาส์คลุมแล้วกดแป้น F9 ยกตัวอย่างเช่น

1. ลากเมาส์คลุม ROW('Trd2'!$A$2:$A$4) > กดแป้น F9 สังเกตผลลัพธ์ > กดแป้น ESC เพื่อยกเลิกการแปลผล
2. ลากเมาส์คลุม ROW('Trd2'!$A$2:$A$4)-ROW('Trd2'!$A$2) > กดแป้น F9 สังเกตผลลัพธ์ > กดแป้น ESC เพื่อยกเลิกการแปลผล
3. ลากเมาส์คลุม ROW('Trd2'!$A$2:$A$4)-ROW('Trd2'!$A$2)+1 > กดแป้น F9 สังเกตผลลัพธ์ > กดแป้น ESC เพื่อยกเลิกการแปลผล
4. ลากเมาส์คลุม 'Trd2'!$A$2:$A$4<>"" > กดแป้น F9 สังเกตผลลัพธ์ > กดแป้น ESC เพื่อยกเลิกการแปลผล
5. ลากเมาส์คลุม 'Trd2'!$A$2:$A$4='Trd2'!$A$3:$A$5 > กดแป้น F9 สังเกตผลลัพธ์ > กดแป้น ESC เพื่อยกเลิกการแปลผล
6. ลากเมาส์คลุม ('Trd2'!$A$2:$A$4<>"")*('Trd2'!$A$2:$A$4='Trd2'!$A$3:$A$5) > กดแป้น F9 สังเกตผลลัพธ์ > กดแป้น ESC เพื่อยกเลิกการแปลผล
7. ลากเมาส์คลุม (ROW('Trd2'!$A$2:$A$4)-ROW('Trd2'!$A$2)+1)/(('Trd2'!$A$2:$A$4<>"")*('Trd2'!$A$2:$A$4='Trd2'!$A$3:$A$5))) > กดแป้น F9 สังเกตผลลัพธ์ > กดแป้น ESC เพื่อยกเลิกการแปลผล
8. ฯลฯ

สิ่งสำคัญเหนือสิ่งอื่นใดคือ ให้ทำความเข้าใจแต่ละฟังก์ชั่นอย่างละเอียด ในการถามและตอบปัญหาที่ผ่านมา คุณ March201711 ได้รับคำตอบจากฟังก์ชั่นพวกนี้ไปจำนวนมากแล้ว พยายามใช้เวลาแกะสูตรให้มากกว่าเดิมครับ

[March201711]

ค่ะ อาจารย์ จะพยายามทำความเข้าใจ ต้องอ่านหลายๆรอบ ยากจัง ไม่เก่งเรื่องเลขเลยค่ะ นอบคุณนะคะที่อาจารบ์อย่างละเอียดค่ะ