Snasui Excel Forum

คือ ติดปัญหาการใช้ vlookup ใน excel แบบข้ามชีตและมีเงื่อนไข อย่างนี้คะ
มี 3 ชีต ประกอบด้วย ชีต "sale","slot","rlot"
ในชีต "sale" ให้ คอลัมน์ "lot no." เทียบกับ คอลัมน์ "lot no." ในชีต "slot" กับ "rlot" ถ้าตรงกันดึงข้อมูลที่ถูกต้องมาแสดงผลลง คอลัมน์ "a1" และ "a2" ในชีต "sale"
ได้ใช้ =VLOOKUP($A2,slot!$A$2:$C$4,2)
แต่ผลที่ได้ถ้าไม่ใช่ค่า rlot มันก็ไม่แสดง


ได้แนบไฟล์มาเพื่อขยายคำอธิบายมากขึ้น
ขอบคุณคะ
แอม

ลองตามนี้ครับ

ที่ชีท Sale เซลล์ B2 คีย์ =IFERROR(VLOOKUP($A2,slot!$A$2:$C$12,COLUMNS($A$2:B$2)),VLOOKUP($A2,rlot!$A$2:$C$12,COLUMNS($A$2:B$2)))
คัดลอกไปทางขวามือ แล้วลงล่างพร้อมกันครับ

ขอบคุณ คุณ bank9597 มากคะ สำหรับ ความช่วยเหลือ และได้ผลตรงตามที่ต้องการ

แก้ไขอีกครั้งครับ
ที่ชีท Sale เซลล์ B2 คีย์ =IFERROR(VLOOKUP($A2,IF(LEFT($A2,1)="s",slot!$A$2:$C$12,rlot!$A$2:$C$12),COLUMNS($A$2:B$2)),"")
คัดลอกไปทางขวามือ แล้วลงล่างพร้อมกันครับ

ขอบคุณ คุณ bank9597 อีกครั้งคะ อันล่าสุดนี้ต่างกับอันแรกยังไงเพราะผลลัพธ์ที่ได้ก็ให้ผลเหมือนกันคะ

ต่างกันดังนี้ครับ

สมมุติ มีรหัสชื่อ A101 โผล่มา ค่าที่สูตรแสดงออกมาคือ ค่าว่าง ซึ่งไม่ต้องโชว์ค่าเออร์เรอร์ออกมา

ทดสอบใส่รหัสแปลกๆลงไปดูครับ ผลที่สูตรแสดงออกมาคือ "ค่าว่าง"

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

ทดสอบตามที่บอกแล้วไม่เห็น error จริงด้วยคะ อย่างไรแล้วข้อมุลที่ใช้จริงมีคอลัมน์มากและไม่ติดกัน แต่จะนำไป apply ต่อคะ (คือบ.มีกฏห้ามนำข้อมูลบ.เผยแพร่เด็ดขาด) ก็เลยสมมุติข้อมูลให้ตรงกับปัญหาที่เจอ ขอบคุณ คุณ bank9597 ที่ให้ความรู้เพิ่มเติมด้าน excel และเป็นตัวอย่างที่ดีของสังคมแบ่งปันต่อไปคะ

ข้อมุลที่ใช้จริงมีคอลัมน์มากและไม่ติดกัน แต่จะนำไป apply ต่อคะ

หากนำไปใช้กับข้อมูลจริงนั้น ผมเกรงว่าต้องปรับสูตรใหม่อีก ให้ตรงตามข้อมูลที่มี

1. ข้อมูลจริงไม่มีแค่รหัสที่ขึ้นต้นด้วย S และ R แต่มีมากกว่านั้น จำเป็นต้องปรับสูตรอีก
2. ข้อมูลจริงไม่มีแค่ชีท Slot และ RSlot แต่มีมากกว่านั้น จำเป็นต้องปรับสูตรอีก

ทั้งหมดนี้ขึ้นอยู่กับพื้นฐานการใช้สูตรของผู้ถามเองครับ ถ้าเข้าใจในสูตรที่ผมตอบไปให้ ก็สามารถปรับให้เข้ากับเงื่อนไขได้เองเลยครับ

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

bank9597 wrote:ส่วนอีกวิธีคือ รอให้อาจารย์คนควน เข้ามาตอบดูใ้ห้อีกครั้งครับ เพราะสูตรของอาจารย์ จะครอบคลุมทั้งหมด แต่มีข้อจำกัดว่า สูตรจะยากกว่าเดิมมากครับ

โปรดงดการเจาะจงผู้ตอบหรือระบุให้รอผู้ใดผู้หนึ่งครับ เพราะจะเป็นการปิดโอกาสผู้ที่ทราบและส่งผลให้ผู้ถามต้องเสียโอกาสไปด้วยครับ

snasui wrote:

bank9597 wrote:ส่วนอีกวิธีคือ รอให้อาจารย์คนควน เข้ามาตอบดูใ้ห้อีกครั้งครับ เพราะสูตรของอาจารย์ จะครอบคลุมทั้งหมด แต่มีข้อจำกัดว่า สูตรจะยากกว่าเดิมมากครับ

โปรดงดการเจาะจงผู้ตอบหรือระบุให้รอผู้ใดผู้หนึ่งครับ เพราะจะเป็นการปิดโอกาสผู้ที่ทราบและส่งผลให้ผู้ถามต้องเสียโอกาสไปด้วยครับ

ขออภัยด้วยครับ พอดีเห็นว่าอาจารย์มีคุณวุฒิมากที่สุด อีกอย่างคือหากผู้ตอบคนใดเข้ามาตอบนั้น เป็นเรื่องที่ผมยินดีอยู่แล้วครับ คิดว่าน่าจะทราบดีอยู่แล้ว ผมจึงไม่กล่าวลงไป ยังขออภัยด้วยครับ

มีคุณวุฒิสูงหรือต่ำก็ไม่ได้หมายความว่าจำเป็นจะต้องรอผู้นั้น ใครทราบก็ตอบได้เลยครับ

ปัญหาเดิมแต่สงสัยว่าถ้าหน้าชีต sale มีตารางคอลัมน์ที่วางสลับไม่ตรงกับ หน้าชีต Slot กับ Rlot ถ้าทำการ vlookup หรือ columns จะไม่แสดงผล จะต้องทำอย่างไรได้บ้างคะ

ได้แนบตัวอย่างมาด้วยคะ

ampertise wrote:ถ้าหน้าชีต sale มีตารางคอลัมน์ที่วางสลับไม่ตรงกับ หน้าชีต Slot กับ Rlot

จากไฟล์แนบก็ตรงกันอยู่ครับ ช่วยทำตัวอย่างมาใหม่พร้อมอธิบายประกอบว่าไม่ตรงกันอย่างไรด้วยครับ

ขอโทษด้วยคะอาจารย์ ได้แนบไฟล์ใหม่มาให้แล้วคะ

ลองตามนี้ครับ

ที่ชีท sale เซลล์ E2 คีย์สูตรด้านล่างหรือ Copy ไปวาง

=INDEX(INDIRECT(LOOKUP(LEFT($C2),{"r","s"},{"rlot","slot"})&"!"&"A:XFD"),MATCH($C2,INDIRECT(LOOKUP(LEFT($C2),{"r","s"},{"rlot","slot"})&"!"&"C:C"),0),MATCH(E$1,INDIRECT(LOOKUP(LEFT($C2),{"r","s"},{"rlot","slot"})&"!"&"1:1"),0))

Enter > Copy ไปยังเซลล์ต่าง ๆ ที่เกี่ยวข้อง

ขอบคุณอาจารย์มากคะ ได้ผลอย่างที่อยากได้มาก แต่ขอถามเพื่อเพิ่มความรู้ เกี่ยวกับที่มาของสูตร ตรงคำว่า "1:1" ในวงเล็บสุดท้ายคะ คือค่าอะไรคะ

คือบรรทัดที่ 1 ครับ

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

ขออธิบายเพิ่มเติมเพื่อท่านอื่น ๆ ที่ต้องการทำความเข้าใจกับสูตรลักษณะนี้ว่า ต้องเข้าใจพื้นฐานฟังก์ชั่น Index เสียก่อนจึงจะทำความเข้าใจสูตรนี้ได้ และแม้จะเข้าใจพื้นฐานมาพอควรแต่ทำความเข้าใจสูตรนี้ไม่ง่ายนักครับ

จากโจทย์นี้ ที่ต้องลำบากในการทำความเข้าใจเพราะแยกชีทเก็บข้อมูล เพื่อให้ง่ายต่อการทำรายงาน ข้อมูลหรือ Database ควรจะอยู่ในชีทเดียวกัน บันทึกต่อกันไปด้านล่างเรื่อย ๆ สามารถใช้สูตรเดียวหาค่าออกมาได้เลย นอกจากนี้สามารถสรุปเป็นรายงานด้วยความสามารถอื่น ๆ เช่น PivotTable ได้ด้วย จากตัวอย่างที่ถามมานี้ หากเพิ่มชีทออกไปอีกก็จะต้องแก้สูตรกันใหม่เรื่อยไป

กลับมาที่ Function Index จะมีไวยากรณ์คือ

=Index(ช่วงข้อมูล,บรรทัดที่ต้องการนำมาแสดง,คอลัมน์ที่ต้องการนำมาแสดง)

จะเห็นว่ามี 3 Arguments หรือ 3 ส่วนประกอบ ซึ่งสูตรที่ผมตอบไปก็มีลักษณะนี้เช่นกัน

จากสูตร =INDEX(INDIRECT(LOOKUP(LEFT($C2),{"r","s"},{"rlot","slot"})&"!"&"A:XFD"),MATCH($C2,INDIRECT(LOOKUP(LEFT($C2),{"r","s"},{"rlot","slot"})&"!"&"C:C"),0),MATCH(E$1,INDIRECT(LOOKUP(LEFT($C2),{"r","s"},{"rlot","slot"})&"!"&"1:1"),0))

ช่วงข้อมูลคือผลลัพธ์ที่ได้จากสูตร INDIRECT(LOOKUP(LEFT($C2),{"r","s"},{"rlot","slot"})&"!"&"A:XFD")
บรรทัดที่ต้องการนำมาแสดงคือผลลัพธ์ที่ได้จากสูตร MATCH($C2,INDIRECT(LOOKUP(LEFT($C2),{"r","s"},{"rlot","slot"})&"!"&"C:C"),0)
คอลัมน์ที่ต้องการนำมาแสดงคือผลลัพธ์ที่ได้จากสูตร MATCH(E$1,INDIRECT(LOOKUP(LEFT($C2),{"r","s"},{"rlot","slot"})&"!"&"1:1"),0)

เครื่องหมาย ! ในฟังก์ชั่น Indirect เป็นตัวชี้ว่า ก่อนหน้าเครื่องหมายนี้คือชื่อชีท หลังเครื่องหมายนี้คือชื่อเซลล์ คำว่า Indirect คือ เป็นทางอ้อม หรือ ไม่ใช่ทางตรง หากเขียนสูตรเป็น =Indirect("Sheet1!A5") หมายความว่าให้แสดงค่าในเซลล์ A5 ของ Sheet1

ดังนั้น จากสูตร INDIRECT(LOOKUP(LEFT($C2),{"r","s"},{"rlot","slot"})&"!"&"A:XFD") จึงสามารถแจกแจงเป็นดังนี้

1. ผลลัพธ์จากสูตร LOOKUP(LEFT($C2),{"r","s"},{"rlot","slot"}) คือชื่อชีท ซึ่งสูตร LOOKUP(LEFT($C2),{"r","s"},{"rlot","slot"}) จะให้ผลลัพธ์เป็น rlot หรือ slot ก็ขึ้นอยู่กับอักขระแรกของ C2 ถ้าเป็น r ก็จะได้ชีท rlot ถ้าเป็น s ก็จะได้ชีท slot ถ้าไม่ใช่ทั้งสองอย่างก็จะเป็น #N/A ซึ่งต้องทราบความหมายของฟังก์ชั่น Lookup และ Left มาก่อนว่าทำงานอย่างไร และสามารถให้ผลลัพธ์อย่างไร
2. ! คือเครื่องหมายที่บอกว่าก่อนหน้านี้คือชื่อชีทและหลังเครื่องหมายนี้คือตำแหน่งเซลล์
3. A:XFD คือตำแหน่งเซลล์ ซึ่งไม่ได้ระบุบรรทัด เป็นช่วงเซลล์ของทั้ง Worksheet หากระบุเป็น 1:1 คือเฉพาะบรรทัดที่ 1 ของ Worksheet

นอกจากตามด้านบนแล้ว ต้องทำความเข้าใจว่าฟังก์ชั่น Match มีความหมายว่าอย่างไรและสามารถให้ผลลัพธ์อย่างไรอีกด้วย ซึ่งสามารถศึกษาทุกฟังก์ชั่นได้ที่ http://office.microsoft.com/th-th/excel ... aspx?CTT=1

อ่านที่อาจารย์อธิบายแล้วเข้าใจมากขึ้นคะ
แต่มีปัญหาใหม่อีกกรณี ที่ขอถามอาจารย์หรือผู้รู้ท่านอื่น

เกี่ยวกับ vlookup ข้ามชีต โดยมีไฟล์ใหม่มาแนบประกอบคำถาม ดังนี้
ชีต "sale" ในคอลัมน์ "com" มีข้อมูล "siam" และ "rang" สลับกันไปแต่ละบรรทัดไม่เท่ากัน และต้องการให้ข้อมูลทั้งแถวของ "siam" แสดงในชีต "s" ถ้าของ "rang" มาแสดงในชีต "r" (คอลัมน์ในชีต "siam" และ "rang" มีคอลัมน์เรียงเหมือนกันกับชีต "sale")
ได้ลองทำเองโดยเลือกคำสั่ง "if" แต่ผลที่ออกมาคือ บรรทัดไหนที่ไม่ใช่ siam มันก็จะเว้นบรรทัดไม่ต่อบรรทัดกัน
จะต้องใช้คำสั่งเพิ่มเติมอะไรถึงจะตัดบรรทัดที่เว้นออกไปเพื่อให้บรรทัดติดกันได้คะ