Page 1 of 2
lookup กับ vlookup
Posted: Sat Jun 20, 2015 4:57 pm
by impim
ใช้ฟังก์ชั่น lookup กับ vlookup แต่ผลลัพท์เป็น #N/A
ลองใช้ Ctrl+F หาข้อมูล ผลที่ได้คือมีข้อมูล
ไม่ทราบว่าเป็นเพราะอะไรค่ะ
(ได้จัดเรียงข้อมูลทั้งในส่วนที่ค้นหา และส่วนที่ต้องการคำตอบให้เรียงลำดับจากน้อยไปหามากแล้วค่ะ)
รบกวนขอคำแนะนำหน่อยค่ะ
Re: lookup กับ vlookup
Posted: Sat Jun 20, 2015 5:03 pm
by snasui
ให้ถามมาพร้อมด้วย File แนบ จะได้เห็นว่าผิดพลาดตรงไหนครับ
Re: lookup กับ vlookup
Posted: Mon Jun 22, 2015 12:28 pm
by impim
เรียน อ.snasui ค่ะ
ตามไฟล์แนบค่ะ
Re: lookup กับ vlookup
Posted: Mon Jun 22, 2015 2:12 pm
by notta_nobi
น่าจะเกิดจากตัวเลขในคอลั่ม C เป็น Text
ส่วนคอลั่ม I เป็น Number
ลอง Double Click ตัวเลขในคอลั่ม C ให้เป็น Number
น่าจะหายครับ
Re: lookup กับ vlookup
Posted: Mon Jun 22, 2015 2:40 pm
by impim
เรียน คุณ notta_nobi ค่ะ
ลองแก้ไขให้เป็น Number ทั้ง 2 คอลัมภ์แล้วค่ะ แต่ยังไม่หายค่ะ
มีสาเหตุอื่น อีกไหมค่ะ
Re: lookup กับ vlookup
Posted: Mon Jun 22, 2015 3:33 pm
by notta_nobi
คุณ impim ครับ
ลอง Copy ข้อมูล ที่คอลัมภ์ C25 ถึง C30
ไปไว้ที่ I13 ถึง I18
แล้วได้ผลอย่างไรครับ
Re: lookup กับ vlookup
Posted: Mon Jun 22, 2015 3:39 pm
by DhitiBank
สาเหตุเพราะข้อมูลตัวเลขในคอลัมน์ C และ คอลัมน์ I บางตัวเป็นตัวเลข บางตัวเป็น text ทำให้ lookup ข้อมูลไม่พบครับ อาจทดสอบโดยใช้สูตร ISNUMBER(cell) หรือ ISTEXT(cell) ก็จะเห็นครับว่าเลขที่เราคีย์เป็นตัวเลขจริงๆ หรือว่าเป็นข้อความ
ทางแก้คือ ควรทำข้อมูลให้มีรูปแบบให้เหมือนกันก่อน (หากเป็นตัวเลขก็ควรทำให้เป็นตัวเลขครับ)
สำหรับกรณีนี้หากไม่อยากแก้ไขข้อมูลดิบ ก็อาจใช้สูตร lookup ได้ครับ เช่น
ที่ J13 คีย์
=LOOKUP(I13&"",$C$25:$C$30&"",$F$25:$F$30)
Enter ครับ
Re: lookup กับ vlookup
Posted: Mon Jun 22, 2015 3:52 pm
by impim
เรียน คุณ notta_nobi ค่ะ
ได้คำตอบค่ะ แต่ว่าข้อมูลจริงเรา COPY แล้วเอาไปวางไม่ได้นี่คะ เพราะข้อมูลหลัก กับข้อมูลที่ต้องการคำตอบได้มาคนละครั้งกันค่ะ
Re: lookup กับ vlookup
Posted: Mon Jun 22, 2015 3:59 pm
by impim
เรียน คุณ DhitiBank ค่ะ
"ทางแก้คือ ควรทำข้อมูลให้มีรูปแบบให้เหมือนกันก่อน (หากเป็นตัวเลขก็ควรทำให้เป็นตัวเลขครับ)"
ต้องแก้อย่างไรคะ ลองทำตอนที่คุณ notta_nobi แนะนำ โดย เลือกคอลัมภ์ แล้วคลิ๊กขวา เปลี่ยน FORMAT เป็นตัวเลข ทศนิยมเป็น 0 แล้วค่ะ แต่ไม่ได้ค่ะ
แล้ว &"" คืออะไรคะ ใช้เพื่ออะไรคะ
ลองใช้สูตร
=LOOKUP(I13&"",$C$25:$C$30&"",$F$25:$F$30)
Enter ครับ
>>ใช้กับข้อมูลจริง แต่คำตอบไม่ถูกค่ะ LOOKUP คิดข้อมูลที่คล้ายด้วยหรือเปล่าคะ
ลองใช้
=VLOOKUP(I13&"",$C$25:$F$30&"",4,0)
กับข้อมูลจริง #VALUE! ค่ะ
Re: lookup กับ vlookup
Posted: Mon Jun 22, 2015 4:04 pm
by DhitiBank
หากข้อมูลต้นทางเอามาจากที่อื่นไม่ได้คีย์เข้าไปเอง การมาแก้ทีหลังจะลำบากครับ คงต้องใช้สูตรปรับเพื่อให้ค้นหาข้อมูลที่ต้องการได้
impim wrote:แล้ว &"" คืออะไรคะ ใช้เพื่ออะไรคะ
เป็นการเชื่อมข้อมูลด้วยค่าว่างครับ หากข้อมูลที่ถูกเชื่อมเป็นตัวเลขที่มีรูปแบบเป็นตัวเลข มันก็จะกลายเป็นข้อความ และหากข้อมูลเดิมเป็นตัวเลขที่มีรูปแบบเป็นข้อความ พอถูกเชื่อมแล้วมันก็จะยังเป็นข้อความเหมือนเดิมครับ
หากยังไม่ได้คำตอบที่ต้องการ รบกวนแนบไฟล์ที่ลองทำมาแล้ว และใส่คำตอบที่ถูกต้องไว้ข้างๆ ได้ไหมครับ จะได้ลองใส่สูตรคำนวณเทียบกันได้
Re: lookup กับ vlookup
Posted: Mon Jun 22, 2015 4:17 pm
by impim
เรียน คุณ DhitiBank ค่ะ
ข้อมูลต้นทางเป็นรายงานที่ดึงมาจากระบบงานที่ใช้ซึ่งระบบมีคำสั่งให้สามารถแปลงไฟล์เป็น EXCEL ได้ค่ะ
ส่วนข้อมูลที่ต้องการคำตอบเป็นข้อมูลได้จากการแสกนบาร์โค้ดค่ะ
หากเข้าใจถูก .. &"" มีผลเท่ากับการปรับข้อมูลให้เป็นข้อความใช้ไหมค่ะ
ตอนนี้ยังไม่ได้คำตอบค่ะ ... ขอปรับไฟล์ให้เป็นไฟล์ตัวอย่างสักครู่นะคะ แล้วจะส่งไฟล์ให้ช่วยดูให้ค่ะ
ขอบคุณมากค่ะ
Re: lookup กับ vlookup
Posted: Mon Jun 22, 2015 4:22 pm
by DhitiBank
impim wrote:หากเข้าใจถูก .. &"" มีผลเท่ากับการปรับข้อมูลให้เป็นข้อความใช้ไหมค่ะ
ใช่ครับ
Re: lookup กับ vlookup
Posted: Mon Jun 22, 2015 4:29 pm
by impim
เรียน คุณ DhitiBank ค่ะ
ขอบคุณค่ะ .. สำหรับคำตอบ
ไฟล์แนบนะคะ
Re: lookup กับ vlookup
Posted: Mon Jun 22, 2015 4:38 pm
by DhitiBank
ที่ไม่ได้คำตอบที่ต้องการเพราะคุณ impim ยังไม่ได้ปรับช่วงในสูตรให้สอดคล้องกับข้อมูลจริงครับ
=LOOKUP(I13&"",$C$25:$C$30&"",$F$25:$F$30)
ช่วงด้านบนอ้างอิงตามไฟล์เก่า แต่ไฟล์ตัวอย่างใหม่ช่วงอ้างอิงคือ C21:C29 และผลลัพธ์อยู่ที่ F21:F29 ครับ
ลองปรับดูก่อนนะครับ ในส่วนที่ระบายสีแดงเอาไว้
Re: lookup กับ vlookup
Posted: Tue Jun 23, 2015 10:58 am
by impim
เรียน คุณ DhitiBank ค่ะ
ตามไฟล์แนบที่ส่งมา ได้แก้ไขตามที่คุณ DhitiBank แนะนำแล้วค่ะ และสามารถได้คำตอบตามที่ต้องการค่ะ
แต่เมื่อเอาสูตรที่คุณ DhitiBank เขียนให้ ไปใช้กับงานจริง...ไม่เชิงว่าไม่ได้คำตอบนะค่ะ คืองานจริงตอนนี้จะเห็นได้ว่า ข้อมูลมี 2 ส่วน คือส่วนที่เป็นข้อมูลดิบ กับส่วนที่ต้องการคำตอบ ซึ่งข้อมูลดิบมีข้อมูลประมาณ 7670 แถวค่ะ
.. แล้วลองทดสอบโดยการ ตัดข้อมูลส่วนที่ต้องการคำตอบให้เหลือเพียง 30 แถว แล้วเอาข้อมูลตัวสุดท้ายที่ต้องการคำตอบ ไปค้นหา (Ctrl+F) แล้วตัดข้อมูลดิบส่วนที่เกินกว่าข้อมูลตัวสุดท้ายที่ต้องการคำตอบออก
>> ผลปรากฎว่าได้คำตอบที่ถูกต้องค่ะ
รบกวนขอคำแนะนำหน่อยค่ะ .. ว่าควรทำอย่างไร
หากไม่ส่งไฟล์ที่ทำงานจริงลงอินเตอร์เน็ตได้ไหมค่ะ
Re: lookup กับ vlookup
Posted: Tue Jun 23, 2015 11:08 am
by DhitiBank
จริงๆ แล้ว ไม่จำเป็นต้องแนบไฟล์จริงมาครับ เพราะอาจมีข้อมูลที่ไม่ต้องการเปิดเผย เพียงแต่ทำไฟล์ตัวอย่างมาก็พอ
แต่ว่า ไฟล์ตัวอย่างที่ทำมา ก็ต้องมีตัวแทนของงานของเราอย่างครบถ้วนครับ เพื่อเวลาเขียนสูตรจะได้ทราบว่าต้องคำนึงถึงเงื่อนไขใดบ้าง อย่างเช่น กรณีของคุณ impim ที่เห็นคือ ข้อมูลบางส่วนที่เห็นเป็นตัวเลขกลับไม่ใช่ตัวเลขจริงๆ ดังนั้น ไฟล์ตัวอย่างที่ทำมาก็ต้องมีตัวแทนของข้อมูลประเภทนี้ด้วยครับ
ส่วนที่ถามมาในโพสต์ล่าสุด บอกตรงๆ ว่างงครับ การทดสอบที่คุณ impim บอกมาเป็นการทดสอบอะไรครับ แล้วในส่วนที่ไม่ได้คำตอบที่ต้องการเนื่องจากอะไรครับ ขึ้นข้อผิดพลาดหรือว่าอย่างไร
Re: lookup กับ vlookup
Posted: Tue Jun 23, 2015 11:29 am
by impim
เรียน คุณ DhitiBank ค่ะ
"ส่วนที่ถามมาในโพสต์ล่าสุด บอกตรงๆ ว่างงครับ การทดสอบที่คุณ impim บอกมาเป็นการทดสอบอะไรครับ แล้วในส่วนที่ไม่ได้คำตอบที่ต้องการเนื่องจากอะไรครับ ขึ้นข้อผิดพลาดหรือว่าอย่างไร" .. ขอโทษด้วยค่ะ
คือจริง ๆ แล้วเป้าหมายที่ต้องการ ไม่ได้ต้องการทดสอบค่ะ แต่ตอนแรกต้องการทำไฟล์ตัวอย่างส่งมาให้ จึงตัดข้อมูลบางส่วนออก ตามที่แจ้งไว้ คือตัดส่วนที่เป็นข้อมูลที่ต้องการคำตอบ และตัดส่วนที่เป็นข้อมูลดิบ แต่เผอิญว่า เมื่อเราตัดข้อมูลให้มีจำนวนแถวที่น้อยลง คำตอบก็แสดงขึ้นมา โดยที่เราไม่ได้แก้ไขใด ๆ ที่สูตรเลยค่ะ
จากสาเหตุดังกล่าวจึงนึกไม่ออกว่าจะทำอย่างไร เพื่อให้สามารถส่งไฟล์ข้อมูลตัวอย่างได้ค่ะ
ขอลองทำไฟล์ข้อมูลตัวอย่างให้ใหม่นะคะ .. ขอเวลาสักครู่ค่ะ
Re: lookup กับ vlookup
Posted: Tue Jun 23, 2015 1:22 pm
by impim
เรียน คุณ DhitiBank ค่ะ
ตามไฟล์แนบค่ะ
เซลสีชมพู คือ ค่าที่ตรงกับข้อมูลที่ต้องการคำตอบค่ะ
เซลสีส้ม คือ เซลที่หากลบออกจะมีผลทำให้คำตอบในคอลัมภ์ J ที่ผูกสูตรไว้เปลี่ยนแปลงค่ะ
คอลัมภ์ L คือ ค่าของคอลัมภ์ J ได้มาโดยการคัดลอก แล้ววางแบบพิเศษ เลือก "ค่า"
คอลัมภ์ M คือ ค่าความแตกต่างระหว่าง คอลัมภ์ K กับ L ค่ะ (หรือคือค่าในคอลัมภ์ J เดิม กับ J ใหม่)
ทำไมเมื่อเราลบข้อมูลอื่นซึ่ง ค่าไม่ตรงกับ คอลัมภ์ H จึงมีผลกับค่าในคอลัมภ์ J ได้คะ
Re: lookup กับ vlookup
Posted: Tue Jun 23, 2015 4:12 pm
by DhitiBank
ที่ J13 ลองคีย์แบบนี้ครับ
=IFERROR(INDEX(E$13:E$100,LOOKUP(2,1/ISNUMBER(SEARCH(H13,C$13:C$100)),ROW(C$13:C$100)-ROW(C$12))),0)
Enter
ลองดูว่าได้ไหมครับ พอดีผมอยู่ข้างนอก ทำในแอปมือถือ ไม่แน่ใจว่าจะได้ตามต้องการหรือเปล่า หากได้ เวลาเอาไปใช้ก็ปรับช่วงอ้างอิงตามข้อมูลจริงครับ
Re: lookup กับ vlookup
Posted: Tue Jun 23, 2015 4:30 pm
by impim