ตัวอย่างสูตรการตัดข้อมูลไปแสดงผลคอลัมน์ละ 200 แถว
Samroeng
วันนี้มีคำถามหนึ่งน่าสนใจ ผู้ถามมีโจทย์ว่า
มีรายชื่ออีเมล์อยู่ในคอลัมน์ A (ตั้งแต่ A1 เป็นต้นไป) มีจำนวนนับพันบรรทัด
ผู้ถามต้องการจะเอาอีเมล์เหล่านั้น ไปแยกวางไว้ในแต่ละคอลัมน์ๆ ละ 200 บรรทัด
จะเขียนสูตรอย่างไร
สูตรที่ผมแนะนำคือ ที่ B1 พิมพ์สูตร
=INDEX($A:$A, ROW()+(COLUMNS($B:B)*200)-200 )
เสร็จแล้วก็คัดลอกลงมา 200 แถว
และคัดลอกไปทางขวา จนกว่าจะไม่ขึ้นอีเมล์
โดยหลักการคือ ใช้ฟังก์ชัน INDEX ซึ่งในคอลัมน์แรกให้แสดงลำดับที่ 1 ถึง 200
พอคอลัมน์ที่สอง ก็จะแสดงลำดับที่ 201 ถึง 400 ไปเรื่อยๆ จนกว่าจะหมดข้อมูล
ดังนั้น เพื่อให้สูตรเป็นอัตโนมัติ เขียนสูตรเดียวแล้วคัดลอกไปใช้ได้ จึงใช้ฟังก์ชัน
ROW และ COLUMNS เข้ามาช่วย
ลองดูในไฟล์แนบครับ
อ.สำเริง ยิ่งถาวรสุข
Mr.Samroeng Yingthawornsuk
e-HR Information Technology Co.,Ltd.
Tel. (66)2-347-1066, (66)81-423-9828 Fax.(66)2-347-1067
มีการศึกษา (Education) ไม่ได้แปลว่า มีความรู้ (Knowledge)
"หนังสือ และ CDรวมไฟล์ตัวอย่างเอ็กเซล" ดูรายละเอียดที่
aek.samroeng 
Seksit Koonsri
--
******************
|= คุณได้รับเมล์นี้ เพราะคุณเป็นสมาชิกกลุ่ม "Excel_for_HR" ของ อ.สำเริง ยิ่งถาวรสุข
|= ส่งคำถามเข้ากลุ่มนี้ โดยส่งอีเมลไปที่ excel_...@googlegroups.com
|= หากต้องการยกเลิกการเป็นสมาชิก ส่งเมล์หา อ.สำเริง โดยตรงที่ ysam...@gmail.com
|= ดูตัวเลือกเพิ่มเติมที่
http://groups.google.co.th/group/excel_for_hr?hl=th
--
Regards,
Seksit Koonsri
Mobile: (+66) 892 118855
Email: sek...@gmail.com
Blog: http://seksitk.blogspot.com
Samroeng
ขอขอบคุณที่แชร์ไอเดียในเรื่องนี้ อย่างน้อยก็เป็นการยืนยันให้สมาชิกท่านอื่นๆ ได้ทราบว่า
การได้ผลลัพธ์อย่างหนึ่งอย่างใดใน Excel นั้น มีวิธีการที่หลากหลาย และไม่ตายตัวเสมอไป
สำหรับคนที่ไม่คุ้นเคยกับการใช้ INDEX หรือการซ้อนฟังก์ชันหลายๆ ฟังก์ชัน
อาจจะมองว่า การเขียนสูตรแบบที่ผมแนะนำไปนั้น "ยาก" เช่น แกะสูตรยาก, สูตรยาว
และอาจจะเขียนสูตรผิดได้ง่าย เนื่องจากมีเครื่องหมายหลายตัว แต่ก็มีข้อดีข้อเสียต่างกันครับ
ในตัวอย่างชุดนี้ การใช้ VLOOKUP ก็นับว่าเป็นวิธีที่ง่าย และผู้ใช้จะคุ้นเคยมากกว่า
แต่ก็มีขั้นตอนที่มากกว่า และไม่อัตโนมัติเมื่อมีข้อมูลเพิ่มขึ้นในอนาคต ตัวอย่างเช่น
- มีขั้นตอนในการแทรกคอลัมน์ และการใส่หมายเลขกำกับ
- มีขั้นตอนในการใส่ตัวเลขในคอลัมน์ D ถึง K
- การอ้างอิงช่วงข้อมูลในสูตร =VLOOKUP(D1,$B$1:$C$1546,2,FALSE) เป็นการระบุถึงตำแหน่งที่แน่นอน ดังนั้น ถ้ามีข้อมูลเพิ่มขึ้น เกินจากแถวที่ 1546 สูตรดังกล่าวก็จะใช้ไม่ได้
- เมื่อมีข้อมูลเพิ่มขึ้น ก็ต้องมาแทรกคอลัมน์ถัดจาก K เพื่อใส่ตัวเลขก่อน
แต่สำหรับสูตร INDEX ที่ผมแนะนำนั้น มีเพียงขั้นตอนเดียว
คือพิมพ์สูตร =INDEX($A:$A,ROW()+(COLUMNS($B:B)*200)-200) ลงไปเลยที่ B1 แล้วก็คัดลอกไปได้ทันที
เพราะตำแหน่งอ้างอิงจะเปลี่ยนไปโดยอัตโนมัติ โดยอาศัยฟังก์ชัน ROW และ COLUMNS เมื่อมีข้อมูลเพิ่มขึ้น
ก็สามารถลากสูตรออกไปคอลัมน์ทางขวาได้ทันที โดยไม่ต้องแก้ไขสูตร หรือต้องเพิ่มขั้นตอน
เนื่องจาก INDEX ต้องการการระบุตำแหน่งของข้อมูลที่ต้องการ ความอัตโนมัตของสูตรที่แนะนำนั้น
จะอยู่ที่ฟังก์ชัน ROW และ COLUMNS ที่จะส่งค่าตำแหน่งอัตโนมัติออกมาให้กับ INDEX นั่นเอง
จากสูตร =INDEX($A:$A,ROW()+(COLUMNS($B:B)*200)-200)
เมื่อสูตรอยู่ที่ B1 จะแปลความหมายได้ว่า =INDEX($A:$A,1+(1*200)-200)
เมื่อสูตรอยู่ที่ C1 จะแปลความหมายได้ว่า =INDEX($A:$A,1+(2*200)-200)
เมื่อสูตรอยู่ที่ D10 จะแปลความหมายได้ว่า =INDEX($A:$A,10+(3*200)-200)
ขอบคุณครับ
สำเริง
Mr.Samroeng Yingthawornsuk
e-HR Information Technology Co.,Ltd.
Tel. (66)2-347-1066, (66)81-423-9828 Fax.(66)2-347-1067
มีการศึกษา (Education) ไม่ได้แปลว่า มีความรู้ (Knowledge)
"หนังสือ และ CDรวมไฟล์ตัวอย่างเอ็กเซล" ดูรายละเอียดที่
aek.samroeng 
ysam...@hotmail.com
Seksit Koonsri
Santipong Nasui
สำหรับสูตรที่แข็งแรงและมั่นคงไม่ถูกกระทบหากต้องแทรกบรรทัด เช่น แทรกบรรทัดที่ 1
สามารถปรัับมาเป็นสูตรตามด้านล่าง เพื่อจะได้ไม่ต้องเขียนสูตรใหม่หากมีการเขียนไปแล้วครับ
B1 คีย์
=INDEX($A:$A,ROWS(B$1:B1)+200*(COLUMNS($B:B)-1))
Enter > Copy ไปด้านขวาและลงด้านล่าง
คนควน (snasui)
MOS Expert, Microsoft Excel
http://www.snasui.com/
http://snasui.blogspot.com/
http://snasui.bloggang.com/
Chaiwat Kullawong
เรียนอาจารย์สำเริงและอาจารย์ทุกๆท่าน
ขอบคุณที่สละเวลาในการทำสูตรและยังสละเวลาอธิบายอีกที่มาที่ไปของสูตร ทำให้มีความเข้าใจเพิ่มขึ้นครับ
Best regards
ChaiwatK.
2010/9/25 Samroeng <ysam...@gmail.com>
Contact Me Error! Filename not specified.Error! Filename not specified.
Chat Error! Filename not specified.aek.samroeng Error! Filename not specified.ysam...@hotmail.comError! Filename not specified.
2010/9/25 Seksit Koonsri <sek...@gmail.com>
2010/9/24 Samroeng <ysam...@gmail.com>
Chat Error! Filename not specified.aek.samroeng Error! Filename not specified.ysam...@hotmail.com
This e-mail and any attachments transmitted with it contain information which is confidential, intended solely for the addressee(s) and which may also be privileged or exempt from disclosure under applicable law. If you are not the addressee(s), or have received this e-mail in error, please notify the sender immediately, delete it from your system and do not copy, print, disclose or use any part of it or its attachments. Internet communications are not guaranteed to be secure or free of any virus. The GKN Group does not accept liability for any loss or damage arising in any way from changes to this e-mail or its attachments which may occur in transmission due to network, machine or software failure or manufacturer or operator error, or from unauthorised access or interference with internet communications by any third party or from the transmission of any viruses. Any opinion or other information in this e-mail or its attachments that does not relate to the business of the GKN Group or any part of it is personal to the sender and is not given or in any way endorsed by the GKN Group or any part of it.