Snasui Excel Forum

ถ้าเราจะดึงข้อมูล column B (Data A) และ column F (Data B) โดยเอามาไว้ที่ column J (Sum C) ต้องใช้สูตรอะไรคะ


และ ทำไมใช้ Vlookup แล้วข้อมูลที่ดึงมาไม่ตรง
เช่น cell J4 ID#4444 ค่า Fee ตาราง Data B ไม่มี ID นี้ แต่ใช้ Vlookup มันดึงข้อมูลมาให้เฉยเลยค่ะ

ตัวอย่างสูตรตามด้านล่างครับ

1. ที่ P9:T9 คีย์หัวคอลัมน์เป็น Ref, ID, Ref, Line, Start ตามลำดับ
2. ที่ R10 คีย์
   =CELL("address",B11)&":"&CELL("address",B32)
   Enter
3. ที่ R11 คีย์
   =CELL("address",F11)&":"&CELL("address",F35)
   Enter
4. ที่ S10 คีย์
   =COUNTA(INDIRECT(R10))
   Enter > Copy ลงด้านล่างถึง S11
5. ที่ S12 คีย์
   =SUM(S10:S11)
   Enter
6. ที่ T10 คีย์
   =SUM(S$10:S10)-S10+1
   Enter > Copy ลงด้านล่างถึง T11
7. ที่ P10 คีย์
   =IF(ROWS(P$10:P10)>$S$12,"",LOOKUP(ROWS(P$10:P10),$T$10:$T$11,$R$10:$R$11))
   Enter > Copy ลงด้านล่างจนเห็นเป็นเซลล์ว่าง
8. ที่ Q10 คีย์
   =INDEX(INDIRECT(P10),COUNTIF($P$10:P10,P10))
   Enter > Copy ลงด้านล่าง
9. ที่ J11 คีย์
   =IFERROR(INDEX($Q$10:$Q$56,SMALL(IF(FREQUENCY(MATCH($Q$10:$Q$56,$Q$10:$Q$56,0),ROW($Q$10:$Q$56)-ROW($Q$10)+1),ROW($Q$10:$Q$56)-ROW($Q$10)+1),ROWS(J$11:J11))),"")
   Ctrl+Shift+Enter > Copy ลงด้านล่าง
10. ที่ K11 คีย์
    =IFERRORLOOKUP(CHAR(255),CHOOSE({1,2},VLOOKUP(J11,$B$11:$C$32,2,0),VLOOKUP(J11,$F$11:$G$35,2,0))),"")
    Enter > Copy ลงด้านล่าง
11. ที่ L11 คีย์
    =IFERROR(VLOOKUP(J11,$B$11:$D$32,3,0),0)
    Enter > Copy ลงด้านล่าง
12. ที่ M11 คีย์
    =IFERROR(VLOOKUP(J11,$F$11:$H$35,3,0),0)
    Enter > Copy ลงด้านล่าง

ที่ Vlookup แล้วได้คำตอบไม่ตรงเพราะเป็นการ Lookup แบบใกล้เคียง (ไม่ใส่ส่วนประกอบสุดท้ายของ Vlookup) ในขณะที่คอลัมน์แรกของ Table Array ใน Vlookup ไม่ได้เรียงจากน้อยไปหามากครับ

งานลักษณะนี้ควรนำ ID และ Name มาต่อกันด้วยการ Copy > Paste แล้วค่อย Remove Duplicate ผ่านเมนู Data ส่วนที่เหลือค่อยทำเป็นสูตร อีกวิธีใช้การประยุกต์ใช้ Consolidate และหากสูตรลักษณะนี้ยังทำงานได้ช้าก็ต้องเขียน VBA ครับ

ค่ะอาจารย์ ขอบคุณที่ให้คำแนะนำ จะลองไปปรับใช้ดูก่อนค่ะว่าช้าไปหรือป่าวค่ะ

E11

=COUNTIF($F$11:$F$36,B11)
จะเห็น Text ที่ไม่มีใน List ที่ 2
จากนั้น sort - และ copy

วิธีนี้คิดว่าคำนวนไม่ช้าและขั้นตอนมีอยู่บ้าง

อาจารย์คะ อยากให้ cellสุดท้ายที่หาเป็นสูตรให้หาค่าสุดท้าย เพราะต้องมานั่งแก้ในสูตร ข้อมูลมีเป็นพันๆกว่ารายการเลยค่ะ

ที่ R10 คีย์
=CELL("address",B11)&":"&CELL("address",B32)
Enter
ที่ R11 คีย์
=CELL("address",F11)&":"&CELL("address",F35)
Enter

และ

ที่ J11 คีย์
=IFERROR(INDEX($Q$10:$Q$56,SMALL(IF(FREQUENCY(MATCH($Q$10:$Q$56,$Q$10:$Q$56,0),ROW($Q$10:$Q$56)-ROW($Q$10)+1),ROW($Q$10:$Q$56)-ROW($Q$10)+1),ROWS(J$11:J11))),"")
Ctrl+Shift+Enter > Copy ลงด้านล่าง
ที่ K11 คีย์
=IFERRORLOOKUP(CHAR(255),CHOOSE({1,2},VLOOKUP(J11,$B$11:$C$32,2,0),VLOOKUP(J11,$F$11:$G$35,2,0))),"")
Enter > Copy ลงด้านล่าง

March201711 wrote: Sun Sep 09, 2018 11:57 am อาจารย์คะ อยากให้ cellสุดท้ายที่หาเป็นสูตรให้หาค่าสุดท้าย เพราะต้องมานั่งแก้ในสูตร ข้อมูลมีเป็นพันๆกว่ารายการเลยค่ะ

ที่ R10 คีย์
=CELL("address",B11)&":"&CELL("address",B32)
Enter
ที่ R11 คีย์
=CELL("address",F11)&":"&CELL("address",F35)
Enter

และ

ตัวอย่างสูตรตามด้านล่างครับ

1. ที่ R10 คีย์
   =CELL("address",B11)&":"&CELL("address",INDEX(B:B,MATCH(9.99999999999999E+307,B:B)))
   Enter
2. ที่ R11 คีย์
   =CELL("address",F11)&":"&CELL("address",INDEX(F:F,MATCH(9.99999999999999E+307,F:F)))
   Enter

ค่ะ และที่ cell

ที่ J11 คีย์
=IFERROR(INDEX($Q$10:$Q$56,SMALL(IF(FREQUENCY(MATCH($Q$10:$Q$56,$Q$10:$Q$56,0),ROW($Q$10:$Q$56)-ROW($Q$10)+1),ROW($Q$10:$Q$56)-ROW($Q$10)+1),ROWS(J$11:J11))),"")
Ctrl+Shift+Enter > Copy ลงด้านล่าง
ที่ K11 คีย์
=IFERRORLOOKUP(CHAR(255),CHOOSE({1,2},VLOOKUP(J11,$B$11:$C$32,2,0),VLOOKUP(J11,$F$11:$G$35,2,0))),"")
Enter > Copy ลงด้านล่าง

ต้องปรับสูตรอย่างไรคะ

แนบไฟล์ล่าสุดมาด้วยจะได้ปรับต่อไปจากนั้นครับ

ค่ะ และถ้าจะให้เรียงข้อมูลที่ column J จากน้อยไปมา ต้องปรับสูตรอย่างไรคะ

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

=IFERROR(INDEX($P$10:$P$5000,SMALL(IF(FREQUENCY(IF($P$10:$P$5000<>"",MATCH($P$10:$P$5000,$P$10:$P$5000,0)),ROW($P$10:$P$5000)-ROW($P$10)+1),ROW($P$10:$P$5000)-ROW($P$10)+1),ROWS(J$11:J11))),"")

ส่วนการเรียงให้ดึงข้อมูลนั้นไปแสดงพื้นที่อื่นก่อนแล้วค่อยนำมาเรียงด้วย Small หรือ Large ในคอลัมน์ J ครับ

ค่ะ แล้ว column K ล่ะคะ

March201711 wrote: Sun Sep 09, 2018 1:44 pm ค่ะ แล้ว column K ล่ะคะ

คลุมข้อมูลเผื่อไปเท่าที่คิดว่าข้อมูลจะขยายไปถึงได้เลยครับ

ที่จริงแล้วสูตรใด ๆ สามารถทำลักษณะนี้ได้ มีบางกรณีเท่านั้นที่อาจจะต้องปรับเช่นการทำ Unique List เป็นต้น

ค่ะ ขอบคุณมากค่ะอาจารย์

J11 ลองใส่ตามนี้ ลากลง
=IFERROR(INDEX($P$10:$P$99,MATCH(0,INDEX((COUNTIF($P$10:$P$99,"<"&$P$10:$P$99)-SUMPRODUCT(COUNTIF($J$10:J10,$P$10:$P$99)))/($P$10:$P$99<>""),),0)),"")

Bo_ry wrote: Sun Sep 09, 2018 3:00 pm J11 ลองใส่ตามนี้ ลากลง
=IFERROR(INDEX($P$10:$P$99,MATCH(0,INDEX((COUNTIF($P$10:$P$99,"<"&$P$10:$P$99)-SUMPRODUCT(COUNTIF($J$10:J10,$P$10:$P$99)))/($P$10:$P$99<>""),),0)),"")

สูตรลักษณะ Match(0,Countif...) นี้จะมีข้อด้อยอยู่ 3 ประการครับ

1. ใช้พลังประมวลผลสูง
2. มีปัญหากับตัวเลขที่จัดเก็บเป็น Text
3. กรณีข้อความมีเครื่องหมาย > หรือ < นำหน้าจะแสดงผลลัพธ์ผิดพลาด

แล้วถ้าใช้สูตร array กับ สูตรลักษณะ Match(0,Countif...) อันไหนดีกว่ากันคะ แบบว่าประมวลได้เร็วไม่หน่วงๆช้าๆน่ะค่ะอาจารย์

ลองกับข้อมูลจริงได้เลย จะได้ทราบความแตกต่างครับ

การใช้สูตร Array ผมเคยแจ้งไปแล้วว่าขึ้นอยู่กับสูตรนั้น ๆ ไม่ใช่ว่าเป็นสูตร Array แล้วจะช้าไปทุกสูตรครับ

ลองใช้สูตร Match(0,Countif...) แล้วข้อมูลมาไม่ครบค่ะ

ทำใหม่ที่ column W:AB ค่ะ

ทำแล้วค่ะ แต่มีข้องความ excel ขึ้นเตือนน่ะล่ะ ตามที่แนบ excel ค่ะ และประมวลปลช้าๆหนืดๆค่ะ