Page 1 of 1
สอบถามวิธีการ Vlookup หรือ Index Match จาก Row ที่ Merge
Posted: Tue Oct 15, 2019 5:54 pm
by gamesavant
สอบถามวิธีการ Vlookup หรือ Index Match หรือวิธีที่การอื่นใดในการคัดลอกข้อมูลจาก Row ที่ Merge
โดยตัวอย่างไฟล์คือต้องการข้อมูลจากชีท SumOT มาใส่ใน ชีท Detail
โดยอ้างอิงจาก รหัสพนักงานใน Column B
ซึ่งข้อมูลที่ต้องการนำมาใส่คือ OT1, OT1_5, OT2, OT3 ทั้งหมด 30 วันดังไฟล์ตัวอย่างครับ
รบกวนสอบถามวิธีในการ Vlookup หรือ Index Match หรือวิธีการอื่นใด
ให้สามารถนำข้อมูลจาก ชีท SumOT ซึ่งทำการ Merge มาใส่ใน ชีท Detail ด้วยครับ
ขอบคุณครับ 
Re: สอบถามวิธีการ Vlookup หรือ Index Match จาก Row ที่ Merge
Posted: Tue Oct 15, 2019 7:36 pm
by puriwutpokin
ที่ Sheet SumOT ที่ AJ5=LOOKUP(9^9,B$5:B5) เป็นคอลัมน์ช่วย คัดลอกจนสุดตาราง
ที่ ชีท Detail E2=IFERROR(INDEX(SumOT!$E$5:$AH$4000,MATCH(1,INDEX((SumOT!$AJ$5:$AJ$4000=LOOKUP(9^9,$B$2:$B2))*(SumOT!$D$5:$D$4000=$D2),0),0),MATCH(E$1,SumOT!$E$4:$AH$4,0)),0) คัดลอกทั่วตารางครับ
Re: สอบถามวิธีการ Vlookup หรือ Index Match จาก Row ที่ Merge
Posted: Wed Oct 16, 2019 12:48 pm
by gamesavant
ใช้สูตรตามที่คุณ puriwutpokin แนะนำ ทำได้แล้วครับ
ขอบพระคุณครับ
Re: สอบถามวิธีการ Vlookup หรือ Index Match จาก Row ที่ Merge
Posted: Mon Oct 28, 2019 9:29 pm
by pro602
puriwutpokin wrote: Tue Oct 15, 2019 7:36 pm
ที่ Sheet SumOT ที่ AJ5=LOOKUP(9^9,B$5:B5) เป็นคอลัมน์ช่วย คัดลอกจนสุดตาราง
ที่ ชีท Detail E2=IFERROR(INDEX(SumOT!$E$5:$AH$4000,MATCH(1,INDEX((SumOT!$AJ$5:$AJ$4000=LOOKUP(9^9,$B$2:$B2))*(SumOT!$D$5:$D$4000=$D2),0),0),MATCH(E$1,SumOT!$E$4:$AH$4,0)),0) คัดลอกทั่วตารางครับ
สวัสดีครับคุณpuriwutpokin ช่วยแนะนำผมหน่อยครับ ตามเอกสารที่ผมแนบมาใน Sheet Rawdata เป็นข้อมูลดึงมาจากโปรแกรม พอจะเขียนสูตรแบบไหนได้บ้างครับที่จะสรุปข้อมูลออกมาได้เหมือนตัวอย่างใน Sheet report (ตามที่แนบผมตัดเอาข้อมูลมาแสดงแค่บางส่วนครับ)หรือถ้าสามารถทำสรุปออกมาได้เหมือนของคุณgamesavant ดีเลยครับ
Re: สอบถามวิธีการ Vlookup หรือ Index Match จาก Row ที่ Merge
Posted: Mon Oct 28, 2019 10:11 pm
by puriwutpokin
ที่ A7=IFERROR(INDEX(Rawdata!$A$14:$AJ$200,AGGREGATE(15,6,(ROW(Rawdata!$A$14:$A$200)-ROW(Rawdata!$A$14)+1)/(Rawdata!$A$14:$A$200<>""),ROWS(A$7:A7)),CHOOSE(COLUMNS($A7:A7),1,5)),"") คัดลอกไปถึง B7 และลงล่างครับ
ที่ C7=IFERROR(INDEX(Rawdata!$A$15:$AJ$201,MATCH($A7,Rawdata!$A$14:$A$200,0),CHOOSE(COLUMNS($C7:C7),18,20,22,24,26,28,30,32,36)),"") คัดลอกไปทางขวา และลงล่างครับ
Re: สอบถามวิธีการ Vlookup หรือ Index Match จาก Row ที่ Merge
Posted: Mon Oct 28, 2019 10:52 pm
by Bo_ry
A7
=IFERROR(INDEX(Rawdata!$A:$A,AGGREGATE(15,6,ROW(Rawdata!$A$14:$A$179)/(Rawdata!$A$14:$A$179>0),ROWS(A$7:A7))),"")
B7:K7
=IF($A7="","",INDEX(Rawdata!$A:$AK,AGGREGATE(15,6,ROW(Rawdata!$A$14:$A$179)/(Rawdata!$A$14:$A$179>0),ROWS(B$7:B7))+(COLUMNS($B7:B7)>1)*($A7<>"รวมแผนก"),MATCH(INDEX(B$4:B$6,MATCH("*",B$4:B$6,)),Rawdata!$A$7:$AJ$7,)-(A7="รวมแผนก")*3-(A7="แผนก")*2))
Re: สอบถามวิธีการ Vlookup หรือ Index Match จาก Row ที่ Merge
Posted: Tue Oct 29, 2019 5:09 am
by pro602
puriwutpokin wrote: Mon Oct 28, 2019 10:11 pm
ที่ A7=IFERROR(INDEX(Rawdata!$A$14:$AJ$200,AGGREGATE(15,6,(ROW(Rawdata!$A$14:$A$200)-ROW(Rawdata!$A$14)+1)/(Rawdata!$A$14:$A$200<>""),ROWS(A$7:A7)),CHOOSE(COLUMNS($A7:A7),1,5)),"") คัดลอกไปถึง B7 และลงล่างครับ
ที่ C7=IFERROR(INDEX(Rawdata!$A$15:$AJ$201,MATCH($A7,Rawdata!$A$14:$A$200,0),CHOOSE(COLUMNS($C7:C7),18,20,22,24,26,28,30,32,36)),"") คัดลอกไปทางขวา และลงล่างครับ
-"puriwutpokin"ขอบคุณมากๆครับครับสำหรับคำแนะนำ
Re: สอบถามวิธีการ Vlookup หรือ Index Match จาก Row ที่ Merge
Posted: Tue Oct 29, 2019 5:10 am
by pro602
Bo_ry wrote: Mon Oct 28, 2019 10:52 pm
A7
=IFERROR(INDEX(Rawdata!$A:$A,AGGREGATE(15,6,ROW(Rawdata!$A$14:$A$179)/(Rawdata!$A$14:$A$179>0),ROWS(A$7:A7))),"")
B7:K7
=IF($A7="","",INDEX(Rawdata!$A:$AK,AGGREGATE(15,6,ROW(Rawdata!$A$14:$A$179)/(Rawdata!$A$14:$A$179>0),ROWS(B$7:B7))+(COLUMNS($B7:B7)>1)*($A7<>"รวมแผนก"),MATCH(INDEX(B$4:B$6,MATCH("*",B$4:B$6,)),Rawdata!$A$7:$AJ$7,)-(A7="รวมแผนก")*3-(A7="แผนก")*2))
-"Bo_ry"ขอบคุณมากๆครับครับสำหรับคำแนะนำ
Re: สอบถามวิธีการ Vlookup หรือ Index Match จาก Row ที่ Merge
Posted: Tue Oct 29, 2019 1:49 pm
by pro602
Bo_ry wrote: Mon Oct 28, 2019 10:52 pm
A7
=IFERROR(INDEX(Rawdata!$A:$A,AGGREGATE(15,6,ROW(Rawdata!$A$14:$A$179)/(Rawdata!$A$14:$A$179>0),ROWS(A$7:A7))),"")
B7:K7
=IF($A7="","",INDEX(Rawdata!$A:$AK,AGGREGATE(15,6,ROW(Rawdata!$A$14:$A$179)/(Rawdata!$A$14:$A$179>0),ROWS(B$7:B7))+(COLUMNS($B7:B7)>1)*($A7<>"รวมแผนก"),MATCH(INDEX(B$4:B$6,MATCH("*",B$4:B$6,)),Rawdata!$A$7:$AJ$7,)-(A7="รวมแผนก")*3-(A7="แผนก")*2))
ผมลองเอาสูตรที่แนะนำมาให้ไปทำตามแล้วผลไม่ได้เหมือนตัวอย่างครับ ไม่แสดงค่าอะไรเลย ที่ Office ใช้ Excel 2007 ครับ
Re: สอบถามวิธีการ Vlookup หรือ Index Match จาก Row ที่ Merge
Posted: Tue Oct 29, 2019 2:19 pm
by Bo_ry
A7
=IFERROR(INDEX(Rawdata!$A:$A,SMALL(IF(Rawdata!$A$14:$A$179>0,ROW(Rawdata!$A$14:$A$179)),ROWS(A$7:A7))),"")
Ctrl+Shift+Enter
B7
=IF($A7="","",INDEX(Rawdata!$A:$AK,SMALL(IF(Rawdata!$A$14:$A$179>0,ROW(Rawdata!$A$14:$A$179)),ROWS(B$7:B7))+(COLUMNS($B7:B7)>1)*($A7<>"รวมแผนก"),MATCH(INDEX(B$4:B$6,MATCH("*",B$4:B$6,)),Rawdata!$A$7:$AJ$7,)-(A7="รวมแผนก")*3-(A7="แผนก")*2))
Ctrl+Shift+Enter
Re: สอบถามวิธีการ Vlookup หรือ Index Match จาก Row ที่ Merge
Posted: Tue Oct 29, 2019 9:16 pm
by pro602
Bo_ry wrote: Tue Oct 29, 2019 2:19 pm
A7
=IFERROR(INDEX(Rawdata!$A:$A,SMALL(IF(Rawdata!$A$14:$A$179>0,ROW(Rawdata!$A$14:$A$179)),ROWS(A$7:A7))),"")
Ctrl+Shift+Enter
B7
=IF($A7="","",INDEX(Rawdata!$A:$AK,SMALL(IF(Rawdata!$A$14:$A$179>0,ROW(Rawdata!$A$14:$A$179)),ROWS(B$7:B7))+(COLUMNS($B7:B7)>1)*($A7<>"รวมแผนก"),MATCH(INDEX(B$4:B$6,MATCH("*",B$4:B$6,)),Rawdata!$A$7:$AJ$7,)-(A7="รวมแผนก")*3-(A7="แผนก")*2))
Ctrl+Shift+Enter
ขอบคุณครับคุณ"Bo_ry" ที่ช่วยแก้ไขปรับสูตรให้ผมได้นำไปใช้งานได้ ถ้าไม่เป็นการรบกวนช่วยอธิบายสูตรให้ผมพอเข้าใจเพื่อเป็นวิทยาทานให้ผมได้นำไปศึกษาต่อยอดได้ไหมครั
Re: สอบถามวิธีการ Vlookup หรือ Index Match จาก Row ที่ Merge
Posted: Wed Oct 30, 2019 11:01 am
by Bo_ry
a. IF(Rawdata!$A$14:$A$179>0,ROW(Rawdata!$A$14:$A$179)) ได้ทุก Row ที่มีค่า
b. SMALL( a. ,ROWS(A$7:A7)) ดึงค่า Row จากน้อย ไปมาก ทีละค่า
c. INDEX(Rawdata!$A:$A, b) ดึงค่าจาก Column A แถวที่มีค่า
d. +(COLUMNS($B7:B7)>1)*($A7<>"รวมแผนก") ปรับตำแหน่ง row ถ้าไม่ใช่ "รวมแผนก" ให้ + อีก 1
e. Rawdata!$A$7:$AJ$7,)-(A7="รวมแผนก")*3-(A7="แผนก")*2)) ปรับตำแหน่ง column เพื่อดึงเลขแผนก
Re: สอบถามวิธีการ Vlookup หรือ Index Match จาก Row ที่ Merge
Posted: Wed Oct 30, 2019 11:40 am
by pro602
Bo_ry wrote: Wed Oct 30, 2019 11:01 am
a. IF(Rawdata!$A$14:$A$179>0,ROW(Rawdata!$A$14:$A$179)) ได้ทุก Row ที่มีค่า
b. SMALL( a. ,ROWS(A$7:A7)) ดึงค่า Row จากน้อย ไปมาก ทีละค่า
c. INDEX(Rawdata!$A:$A, b) ดึงค่าจาก Column A แถวที่มีค่า
d. +(COLUMNS($B7:B7)>1)*($A7<>"รวมแผนก") ปรับตำแหน่ง row ถ้าไม่ใช่ "รวมแผนก" ให้ + อีก 1
e. Rawdata!$A$7:$AJ$7,)-(A7="รวมแผนก")*3-(A7="แผนก")*2)) ปรับตำแหน่ง column เพื่อดึงเลขแผนก
-ขอบคุณครับสำหรับข้อมูลผมจะนำไปศึกษาต่อครับ