Page 1 of 1
สอบถามวิธี vlookup ดึงข้อมูลในฐานข้อมูลด้วยเงื่อนไขเดียวแต่ข้อมูลมีหลายบรรทัด
Posted: Fri Jul 05, 2019 4:48 pm
by phrutwitt
สวัสดีครับ
ผมขอสอบถามการดึงข้อมูลในsheetหนึ่ง ไปยังอีกsheetหนึ่ง ตัวอย่างเช่นคำสั่งซื้อที่กำกับด้วย codeใบสั่งซื้อเดียว แต่รายการสินค้ามีหลายตัว เพื่อมาแสดงในsheet แบบฟอร์ม
:):)
เบื้องต้นใช้ vlookup และให้ข้อมูลเเสดงตามตัวกำหนดใน drop down ดังตัวอย่างครับ
ขอบคุณครับ
Re: สอบถามวิธี vlookup ดึงข้อมูลในฐานข้อมูลด้วยเงื่อนไขเดียวแต่ข้อมูลมีหลายบรรทัด
Posted: Fri Jul 05, 2019 5:06 pm
by Supachok
B17
เอา merge cell ออก
=INDEX(ฐานข้อมูล!$E$1:$E$500,SMALL(IF($J$4=ฐานข้อมูล!$C$1:$C$500,ROW($D$1:$D$500),""),ROW(A1)))
Array formula {}
Copy down
Re: สอบถามวิธี vlookup ดึงข้อมูลในฐานข้อมูลด้วยเงื่อนไขเดียวแต่ข้อมูลมีหลายบรรทัด
Posted: Fri Jul 05, 2019 5:15 pm
by phrutwitt
Supachok wrote: Fri Jul 05, 2019 5:06 pm
B17
เอา merge cell ออก
=INDEX(ฐานข้อมูล!$E$1:$E$500,SMALL(IF($J$4=ฐานข้อมูล!$C$1:$C$500,ROW($D$1:$D$500),""),ROW(A1)))
Array formula {}
Copy down
ขอโทษทีครับ ไม่ค่อยเข้าใจครับคือพอแก้ไขแล้วcopyไปวางแลัวมันไม่แสดงค่า แล้วสมมติพอเรากด next เลขใบคำสั่งซื้อถัดไปข้อมูลมันจะเป็นยังไงครับ...ขออภัยครับมือใหม่
Re: สอบถามวิธี vlookup ดึงข้อมูลในฐานข้อมูลด้วยเงื่อนไขเดียวแต่ข้อมูลมีหลายบรรทัด
Posted: Sat Jul 06, 2019 7:50 am
by snasui
คีย์สูตรที่มีเครื่องหมาย { } ครอบคือสูตรแบบ Array ดูวิธีการกดแป้นที่นี่ครับ
viewtopic.php?f=6&t=13228
หากทำแล้วยังไม่ได้คำตอบกรุณาแนบไฟล์ล่าสุดมาด้วยจะได้ตอบต่อไปจากนั้นครับ
Re: สอบถามวิธี vlookup ดึงข้อมูลในฐานข้อมูลด้วยเงื่อนไขเดียวแต่ข้อมูลมีหลายบรรทัด
Posted: Sat Jul 06, 2019 8:53 am
by phrutwitt
ไม่ทราบว่า {} มันครอบอย่างไรครับ
=INDEX(ฐานข้อมูล!$E$1:$E$500,SMALL(IF($J$4=ฐานข้อมูล!$C$1:$C$500,ROW($D$1:$D$500),""),ROW(A1)))
Array formula {=VLOOKUP(J4,ฐานข้อมูล!C:Q,3,0)} แบบนี้เหรอครับ
ขอช่วยแก้ให้ทราบหน่อยครับ ขอบคุณครับ
Re: สอบถามวิธี vlookup ดึงข้อมูลในฐานข้อมูลด้วยเงื่อนไขเดียวแต่ข้อมูลมีหลายบรรทัด
Posted: Sat Jul 06, 2019 9:32 am
by snasui
ตัวอย่างสูตรตามด้านล่างครับ
- ที่ B17 คีย์
=IFERROR(INDEX(ฐานข้อมูล!E$2:E$500,SMALL(IF($J$4=ฐานข้อมูล!$C$2:$C$500,ROW(ฐานข้อมูล!$E$2:$E$500)-ROW(ฐานข้อมูล!$E$2)+1),ROWS(B$17:B17))),"")
Ctrl+Shift+Enter > Copy ลงด้านล่าง
- ที่ F17 คีย์
=IFERROR(INDEX(ฐานข้อมูล!F$2:F$500,SMALL(IF($J$4=ฐานข้อมูล!$C$2:$C$500,ROW(ฐานข้อมูล!$E$2:$E$500)-ROW(ฐานข้อมูล!$E$2)+1),ROWS(B$17:B17))),0)
Ctrl+Shift+Enter > Copy ลงด้านล่าง
- ที่ G17 คีย์
=IFERROR(INDEX(ฐานข้อมูล!G$2:G$500,SMALL(IF($J$4=ฐานข้อมูล!$C$2:$C$500,ROW(ฐานข้อมูล!$E$2:$E$500)-ROW(ฐานข้อมูล!$E$2)+1),ROWS(B$17:B17))),0)
Ctrl+Shift+Enter > Copy ลงด้านล่าง
- ที่ H17 คีย์
=IFERROR(INDEX(ฐานข้อมูล!H$2:H$500,SMALL(IF($J$4=ฐานข้อมูล!$C$2:$C$500,ROW(ฐานข้อมูล!$E$2:$E$500)-ROW(ฐานข้อมูล!$E$2)+1),ROWS(B$17:B17))),0)
Ctrl+Shift+Enter > Copy ลงด้านล่าง
- ที่ I17 คีย์
=IFERROR(INDEX(ฐานข้อมูล!I$2:I$500,SMALL(IF($J$4=ฐานข้อมูล!$C$2:$C$500,ROW(ฐานข้อมูล!$E$2:$E$500)-ROW(ฐานข้อมูล!$E$2)+1),ROWS(B$17:B17))),0)
Ctrl+Shift+Enter > Copy ลงด้านล่าง
กรุณาสังเกตว่าเซลล์ที่เขียนสูตรมีการเปลี่ยนการอ้างอิงข้อมูลต้นทางที่นำมาแสดงที่ฟังก์ชั่น Index หากจะปรับใช้สามารถเปลี่ยนค่าในตำแหน่งนี้ได้ตามต้องการ
การกดแป้นให้รับสูตรผมแจ้งไปแล้วว่ากดอย่างไร กรุณาทบทวนในโพสต์ #4
ฐานข้อมูลต้นทางที่มีการ Merge เซลล์ ให้แบ่งเป็นรายบรรทัด
ไม่ควร Merge เซลล์ หรือ คีย์ค่าในเซลล์เดียว เพราะจะสูญเสียความเป็น Database ทำให้เกิดความยุ่งยากในนำไปทำรายงาน
เซลล์ปลายทางที่ต้องการคีย์สูตร Array จำเป็นต้องยกเลิกการ Merge เซลล์เช่นเดียวกันครับ
Re: สอบถามวิธี vlookup ดึงข้อมูลในฐานข้อมูลด้วยเงื่อนไขเดียวแต่ข้อมูลมีหลายบรรทัด
Posted: Mon Jul 08, 2019 4:24 pm
by menem
ผมชอบใช้เขียนสูตรเพื่อดึงไปแล้วใช้ Indirect อ้างอิงกลับมาตามในไฟล์แนบ
แต่สิ่งที่ต้องระวัง เหมือนว่าฐานข้อมูลจะมีความไม่สมบูรณ์อยู่ (และการใช้ไฟล์
ที่เขียน จะต้องระบุวันที่ ก่อนจึงจะเลือก PO ที่ต้องการได้ครับ)
Re: สอบถามวิธี vlookup ดึงข้อมูลในฐานข้อมูลด้วยเงื่อนไขเดียวแต่ข้อมูลมีหลายบรรทัด
Posted: Tue Jul 09, 2019 11:10 am
by phrutwitt
menem wrote: Mon Jul 08, 2019 4:24 pm
ผมชอบใช้เขียนสูตรเพื่อดึงไปแล้วใช้ Indirect อ้างอิงกลับมาตามในไฟล์แนบ
แต่สิ่งที่ต้องระวัง เหมือนว่าฐานข้อมูลจะมีความไม่สมบูรณ์อยู่ (และการใช้ไฟล์
ที่เขียน จะต้องระบุวันที่ ก่อนจึงจะเลือก PO ที่ต้องการได้ครับ)
ขอบคุณมากครับ แต่ผมมีความไม่เข้าใจนิดนึงครับคือ พอจะรบกวนอธิบายหลักการคิดได้ไหมครับ เช่น
1 ต้องหาเลข PO ทั้งหมดว่ามีกี่ใบ ,วันที่อะไรบ้าง
2 หาD.Count,D.1st.Match.Row,D_RangeE,P.Count
3 กรุ๊ปข้อมูลโดยวันที่ปีพ.ศ.
4.....
5.....
6.....
ช่วยขยายความหน่อยได้ไหมครับ
Re: สอบถามวิธี vlookup ดึงข้อมูลในฐานข้อมูลด้วยเงื่อนไขเดียวแต่ข้อมูลมีหลายบรรทัด
Posted: Tue Jul 09, 2019 8:48 pm
by menem
เนื่องจากชุดข้อมูล มีลักษณะที่ไม่สมบูรณ์ (เลข PO ไม่ Uniq)
ดังนั้นในมุมมองผม การดึงข้อมูลจาก PO อย่างเดียวอาจจะไม่เหมาะสม
เลยใช้วิธีการกำหนดช่วงข้อมูล โดยใช้วันที่เป็นหลัก (ดังนั้นโดยพื้นฐาน
ข้อมูลจะต้องมีการเรียงลำดับ วันที่ และ เลขที่ PO ด้วยจึงจะใช้งานได้)
D.Count หมายถึงการนับว่า วันที่ที่ระบุมีกี่รายการ
D.1st.Match หมายถึง วันที่ที่ระบุ เริ่มต้นที่บรรทัด (ฐานข้อมูล) ที่เท่าไหร่
เพื่อส่งต่อให้กับ INDIRECT สำหรับกำหนดช่วงข้อมูล เพื่อที่จะทำ Uniq PO
อีกทีหนึ่ง (และกันไม่ให้เกิดปัญหาการดึง PO ข้ามกลุ่มวันที่ เช่นระบุ PO
เป็น 'เงินสด' เป็นต้น)
เมื่อได้ช่วงข้อมูล (จากวันที่) จะดึงข้อมูลเหล่านั้น แล้วหาลำดับ PO ที่ไม่ซ้ำ
กับคนอื่น (ในช่วงวันที่ดังกล่าว) โดยการใช้ Countif แล้วป้อนลำดับให้ เพื่อ
สร้างชุด List ข้อมูล สำหรับส่งต่อให้กับ Data Validate อีกต่อหนึ่ง
และหลังจากเลือก PO แล้ว จึงจะใช้วิธีหาบรรทัดแรกของ PO นั้น ๆ
(ในช่วงวันที่ที่กำหนด) รวมถึงจำนวน PO นั้น ๆ แล้วเอามาแสดงในรายการ
** ข้อจำกัดคือต้องมีการเรียงวันที่ และ PO ก่อนเสมอ กรณีที่ข้อมูลวันที่เป็น ว่าง
ถือว่าไม่ใช่ข้อมูลที่อ้างอิงได้
ประมาณนี้น่ะครับ
Re: สอบถามวิธี vlookup ดึงข้อมูลในฐานข้อมูลด้วยเงื่อนไขเดียวแต่ข้อมูลมีหลายบรรทัด
Posted: Wed Jul 10, 2019 9:33 am
by phrutwitt
menem wrote: Tue Jul 09, 2019 8:48 pm
เนื่องจากชุดข้อมูล มีลักษณะที่ไม่สมบูรณ์ (เลข PO ไม่ Uniq)
ดังนั้นในมุมมองผม การดึงข้อมูลจาก PO อย่างเดียวอาจจะไม่เหมาะสม
เลยใช้วิธีการกำหนดช่วงข้อมูล โดยใช้วันที่เป็นหลัก (ดังนั้นโดยพื้นฐาน
ข้อมูลจะต้องมีการเรียงลำดับ วันที่ และ เลขที่ PO ด้วยจึงจะใช้งานได้)
D.Count หมายถึงการนับว่า วันที่ที่ระบุมีกี่รายการ
D.1st.Match หมายถึง วันที่ที่ระบุ เริ่มต้นที่บรรทัด (ฐานข้อมูล) ที่เท่าไหร่
เพื่อส่งต่อให้กับ INDIRECT สำหรับกำหนดช่วงข้อมูล เพื่อที่จะทำ Uniq PO
อีกทีหนึ่ง (และกันไม่ให้เกิดปัญหาการดึง PO ข้ามกลุ่มวันที่ เช่นระบุ PO
เป็น 'เงินสด' เป็นต้น)
เมื่อได้ช่วงข้อมูล (จากวันที่) จะดึงข้อมูลเหล่านั้น แล้วหาลำดับ PO ที่ไม่ซ้ำ
กับคนอื่น (ในช่วงวันที่ดังกล่าว) โดยการใช้ Countif แล้วป้อนลำดับให้ เพื่อ
สร้างชุด List ข้อมูล สำหรับส่งต่อให้กับ Data Validate อีกต่อหนึ่ง
และหลังจากเลือก PO แล้ว จึงจะใช้วิธีหาบรรทัดแรกของ PO นั้น ๆ
(ในช่วงวันที่ที่กำหนด) รวมถึงจำนวน PO นั้น ๆ แล้วเอามาแสดงในรายการ
** ข้อจำกัดคือต้องมีการเรียงวันที่ และ PO ก่อนเสมอ กรณีที่ข้อมูลวันที่เป็น ว่าง
ถือว่าไม่ใช่ข้อมูลที่อ้างอิงได้
ประมาณนี้น่ะครับ
ขอบคุณมากครับบบบบบบบ
Re: สอบถามวิธี vlookup ดึงข้อมูลในฐานข้อมูลด้วยเงื่อนไขเดียวแต่ข้อมูลมีหลายบรรทัด
Posted: Thu Jul 11, 2019 5:06 pm
by phrutwitt
menem wrote: Tue Jul 09, 2019 8:48 pm
เนื่องจากชุดข้อมูล มีลักษณะที่ไม่สมบูรณ์ (เลข PO ไม่ Uniq)
ดังนั้นในมุมมองผม การดึงข้อมูลจาก PO อย่างเดียวอาจจะไม่เหมาะสม
เลยใช้วิธีการกำหนดช่วงข้อมูล โดยใช้วันที่เป็นหลัก (ดังนั้นโดยพื้นฐาน
ข้อมูลจะต้องมีการเรียงลำดับ วันที่ และ เลขที่ PO ด้วยจึงจะใช้งานได้)
D.Count หมายถึงการนับว่า วันที่ที่ระบุมีกี่รายการ
D.1st.Match หมายถึง วันที่ที่ระบุ เริ่มต้นที่บรรทัด (ฐานข้อมูล) ที่เท่าไหร่
เพื่อส่งต่อให้กับ INDIRECT สำหรับกำหนดช่วงข้อมูล เพื่อที่จะทำ Uniq PO
อีกทีหนึ่ง (และกันไม่ให้เกิดปัญหาการดึง PO ข้ามกลุ่มวันที่ เช่นระบุ PO
เป็น 'เงินสด' เป็นต้น)
เมื่อได้ช่วงข้อมูล (จากวันที่) จะดึงข้อมูลเหล่านั้น แล้วหาลำดับ PO ที่ไม่ซ้ำ
กับคนอื่น (ในช่วงวันที่ดังกล่าว) โดยการใช้ Countif แล้วป้อนลำดับให้ เพื่อ
สร้างชุด List ข้อมูล สำหรับส่งต่อให้กับ Data Validate อีกต่อหนึ่ง
และหลังจากเลือก PO แล้ว จึงจะใช้วิธีหาบรรทัดแรกของ PO นั้น ๆ
(ในช่วงวันที่ที่กำหนด) รวมถึงจำนวน PO นั้น ๆ แล้วเอามาแสดงในรายการ
** ข้อจำกัดคือต้องมีการเรียงวันที่ และ PO ก่อนเสมอ กรณีที่ข้อมูลวันที่เป็น ว่าง
ถือว่าไม่ใช่ข้อมูลที่อ้างอิงได้
ประมาณนี้น่ะครับ
ขอโทษด้วยครับ พอดีตอนนี้มีประเด็นคือถ้าหากอยากเพิ่มฐานข้อมูลต่อไปเรื่อยๆจะต้องทำยังไงครับ เพราะเมื่อเพิ่มในหน้า uniq จะแสดง #REF เลย
รบกวนด้วยครับ
Re: สอบถามวิธี vlookup ดึงข้อมูลในฐานข้อมูลด้วยเงื่อนไขเดียวแต่ข้อมูลมีหลายบรรทัด
Posted: Thu Jul 11, 2019 5:16 pm
by puriwutpokin
ตัวอย่างที่ #REF เกิดที่จุดไหนครับ ลองแนบตัวที่เกิด #REF นั้นมาด้วยครับจะได้เข้าใจตรงกันครับ
Re: สอบถามวิธี vlookup ดึงข้อมูลในฐานข้อมูลด้วยเงื่อนไขเดียวแต่ข้อมูลมีหลายบรรทัด
Posted: Thu Jul 11, 2019 5:29 pm
by phrutwitt
puriwutpokin wrote: Thu Jul 11, 2019 5:16 pm
ตัวอย่างที่ #REF เกิดที่จุดไหนครับ ลองแนบตัวที่เกิด #REF นั้นมาด้วยครับจะได้เข้าใจตรงกันครับ
อันนี้ครับผม ขออภัยครับแนบผิดไฟล์
Re: สอบถามวิธี vlookup ดึงข้อมูลในฐานข้อมูลด้วยเงื่อนไขเดียวแต่ข้อมูลมีหลายบรรทัด
Posted: Thu Jul 11, 2019 6:31 pm
by snasui
เซลล์ F8 ชีต UNIQ เปลี่ยนค่าเป็น
ฐานข้อมูลFX ครับ
Re: สอบถามวิธี vlookup ดึงข้อมูลในฐานข้อมูลด้วยเงื่อนไขเดียวแต่ข้อมูลมีหลายบรรทัด
Posted: Fri Jul 12, 2019 10:14 am
by phrutwitt
snasui wrote: Thu Jul 11, 2019 6:31 pm
เซลล์ F8 ชีต UNIQ เปลี่ยนค่าเป็น
ฐานข้อมูลFX ครับ
ขอบคุณครับ