ถามเรื่องการอ้างเซลล์ใน Vlookup

[korarkar]

รบกวนถามการอ้างเซลล์หน่อยครับ คือจากไฟล์ที่แนบมาผมทำ vlookup เพื่อหาชื่อ แต่พอก๊อปสูตรไปวางมันอ้างเซลล์ไม่ถูกต้องครับ มันข้ามไปหลายเซลล์เลย คือ sheet ที่ใช้อ้างอิงข้อมูลจะเรียงกันไป a1 a2 a3 ตามลำดับ แต่sheet ที่ทำสูตรหาชื่อ โดยคนแรกก็อ้างได้ถูกต้อง แต่พอก๊อปไปวางของคนที่สอง มันดันไปอ้างชื่อของคนที่เจ็ด รบกวนช่วยหาวิธีแก้ให้อ้างเซลล์ให้มันเรียงกันด้วยครับ ขอบคุณครับ

[kmb]

ถ้าต้องการใช้วิธีนี้จริง ๆ ลองเปลี่ยนจากสูตรเดิมในชีต "Sheet2" เซลล์ A2
="ชื่อ"&" "&VLOOKUP(Sheet1!A2,Sheet1!A1:C14,2,0)&" "&VLOOKUP(Sheet1!A2,Sheet1!A1:C14,3,0)&" เลขที่ 1 – 35 (ต่อห้อง)"

ไปเป็นตามนี้ครับ
="ชื่อ"&" "&VLOOKUP(INDIRECT(ADDRESS(COUNTIF(A$1:A1,"ระดับชั้น ม.2")+1,1,,,"Sheet1")),Sheet1!$A$1:$C$14,2,0)&" "&VLOOKUP(INDIRECT(ADDRESS(COUNTIF(A$1:A1,"ระดับชั้น ม.2")+1,1,,,"Sheet1")),Sheet1!$A$1:$C$14,3,0)&" เลขที่ 1 – 35 (ต่อห้อง)"

ทีนี้ก็ copy ไปใช้ได้เลย

แต่ผมว่าน่าจะดีกว่าถ้าใช้วิธี mail merge ลองดูตามกระืทู้นี้ครับ
viewtopic.php?f=3&t=542&p=3207&hilit=mail+merge

[snasui]

มาช่วยเสริมอีกวิธีครับ

A2 คีย์สูตร

="ชื่อ "&INDEX(Sheet1!$B$2:$B$14,INT((ROWS(A$2:A2)-1)/5)+1)&" "&INDEX(Sheet1!$C$2:$C$14,INT((ROWS(A$2:A2)-1)/5)+1)&" เลขที่ 1 - 35 (ต่อห้อง)"

Enter

[sdmania]

ขอพ่วงคำถามไปด้วยครับ

ผมใช้vlookupหารหัสลูกค้า ยกตัวอย่าง

คอลัมภ์a1
22-001-2
22-002-2
22-003-2

ทีนี้ผมลองแกล้งใส่ รหัสไปว่า 22-004-8(ซึ่งไม่มีอยู่ในข้อมูล) มันไปดึงข้อมูลของแถวล่างสุด(22-003-2)ขึ้นมาให้ครับ
เลยอยากทราบวิธีถ้าพิมพ์ผิดไปจากข้อมูลที่เรามีให้ขึ้นเตือน หรืออะไรก็ได้ที่บ่งบอกว่าเราพิมพ์นอกเหนือข้อมูลครับ

[kmb]

ผมคิดว่าสูตรที่คุณ sdmania ใช้คงต่างกันที่ไวยากรณ์ส่วนสุดท้ายของ Vlookup ครับ ตามตัวอย่างด้านล่างที่เซลล์ E3

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

=VLOOKUP($D3,$A$2:$B$4,2,0) >> เงื่อนไขนี้ใช้สำหรับหาตัวที่เท่ากับค่าที่ต้องการเท่านั้น
ถ้าต้องการให้แสดงค่าือื่นก็ใช้ IF ช่วยดัก error ได้ครับ เช่น =IF(ISNA(VLOOKUP($D3,$A$2:$B$4,2,0)),"ค่าผิดพลาด",VLOOKUP($D3,$A$2:$B$4,2,0))

=VLOOKUP($D3,$A$2:$B$4,2)
ส่วนกรณีนี้ ส่วนนี้ไม่ใส่ค่าอะไรไว้ คำอธิบายคือ
ถ้าเป็น TRUE หรือไม่ใส่ค่าอะไรไว้ จะส่งการจับคู่ที่ตรงกันหรือที่เหมาะสมกลับมา ถ้าไม่พบการจับคู่ที่ตรงกัน ค่าที่ใกล้เคียงที่สุดที่น้อยกว่าค่า lookup_value จะถูกส่งกลับมา

ลองทำตัวอย่างให้ดูตามภาพครับ

vlookup.JPG

[korarkar]

มาช่วยเสริมอีกวิธีครับ

A2 คีย์สูตร

="ชื่อ "&INDEX(Sheet1!$B$2:$B$14,INT((ROWS(A$2:A2)-1)/5)+1)&" "&INDEX(Sheet1!$C$2:$C$14,INT((ROWS(A$2:A2)-1)/5)+1)&" เลขที่ 1 - 35 (ต่อห้อง)"

Enter

ถ้าเราทำเป็น 2 คอลัมน์เพิ่มคอลัมน์ B ขึ้นมาอีก (ตามรูปที่แนบมา) เพื่อที่จะให้มันเต็มหน้ากระดาษเวลาปริ้นออกมา จะต้องแก้ชื่อเซลล์อะไรคับ

28-2-2554 11-46-18.png

[snasui]

ในคอลัมน์ A ต้องการเรียงลงด้านล่างสูงสุดกี่คนถึงค่อยมาเรียงใหม่ในคอลัมน์ B ครับ

[korarkar]

มีข้อมูล 100 record คับก็แบ่งเป็นหน้า หน้าละประมาณ 12 record ครับ

[snasui]

ลองดูสูตรตามด้านล่างและไฟล์แนบประกอบครับ

ที่ A2 คีย์

="ชื่อ "&INDEX(Sheet1!$B$2:$B$14,6*(COLUMNS($A2:A2)-1)+INT((ROWS(A$2:A2)-1)/5)+1)&" "&INDEX(Sheet1!$C$2:$C$14,6*(COLUMNS($A2:B2)-1)+INT((ROWS(A$2:A2)-1)/5)+1)&" เลขที่ 1 - 35 (ต่อห้อง)"

Enter

คำว่า 12 Record ผมถือว่าคอลัมน์ A มี 6 รายชื่อและคอลัมน์ B มี 6 รายชื่อโดยต้องการเรียงข้อมูลไปทางด้านขวาเรื่อย ๆ

[korarkar]

ผมได้รายละเอียดน้อยไปหน่อย เพิ่งได้รายละเอียดของเงื่อนไขมาครับ คือต้องการจะทำป้ายชื่อติดโต๊ะสอบ โดยจะมีรายชื่อมา 100 รายชื่อ มีห้องสอบ 3 ห้องสอบ ห้องละ 35 คน โดยแสดงชื่อ เลขที่(โดยตัวอย่างที่ผมให้ไปแล้วจะเปลี่ยนจาก เลขที่ 1 – 35 (ต่อห้อง) เป็นให้รันเลขที่จาก 1 ถึง 35 แล้วก็ตัดขึ้นห้องใหม่)จนครบ 100 คน 3 ห้อง โดยเลขประชาชนผิมคิดว่าจะใช้สูตรเดียวกันได้ ดูรูปแบบตามรูปที่แนนบมาได้ครับ

28-2-2554 14-36-15.png

[snasui]

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