Excel Tips : การใช้ Data Validation เพื่อป้องกันการกรอกข้อมูลผิดรูปแบบ-1

สวัสดีครับท่านผู้อ่าน เรื่องของ Data Validation ผมได้นำเสนอมาหลากหลายทั้ง การป้องกันการบันทึกรายการข้อมูลซ้ำกันเหมาะสำหรับพวกการเก็บ Invoice หรือข้อมูลที่ต้องไม่ซ้ำกัน หรือวิธีการสร้างรายการข้อมูล(List) และการสร้างรายการที่มีรายการย่อย เป็นต้น ในบทนี้จะขอนำเสนอการใช้ Data Validation อีกตัวอย่างหนึ่งใน Excel นั่นคือ การป้องกันการกรอกข้อมูลผิดรูปแบบ ตัวอย่างคือ Code ต่างๆที่มีรูปแบบแน่นอน  สมมุติว่า เป็น Code วัตถุดิบที่ต้องนำเข้า โดยรูปแบบของ Code จะต้องขึ้นต้นด้วยตัวอักษร R และต่อท้ายด้วยตัวเลข  4 หลัก เช่น R0125 เป็นต้น ดังนั้น เราต้องใช้ Data Validation ใน Excel ช่วยป้องกันการกรอก Code ผิดรูปแบบ มาดูแนวคิดกันครับ การทำงานของ Excel ต้องตรวจสอบเงื่อนไข ดังนี้ครับ

อักขระตัวแรกต้องเป็นตัว R และ ต้องมีความยาวของโค้ดรวม 5 ตัว และ 4 ตัวหลังต้องเป็นตัวเลข

สมมุติว่า Code วัตถุดิบถูกกรอกที่ Cell B2 

ให้ท่านทดลองเขียนสูตร Excel ในCell D3 ดังนี้ครับ AND(LEFT(B2) = "R",LEN(B2) = 5 , ISNUMBER(VALUE(RIGHT(B2,4)))

ขออธิบายสูตร Excel นี้นะครับ

ในสูตร AND จะมีเงื่อนไขให้ตรวจสอบ 3 เงื่อนไข โดยคืนค่า TRUE หรือ 1 ก็ต่อเมื่อ ทั้งสามเงื่อนไขเป็นจริง นอกนั้นจะคืนค่า FALSE หรือ 0 ครับ

เงื่อนไขแรก จะตรวจสอบอักขระตัวแรกว่าเป็น R หรือไม่ โดยใช้สูตร LEFT(B2) = "R" เป็นการบังคับให้ผู้กรอกต้องพิมพ์ R เท่านั้น

เงื่อนไขต่อมา จะตรวจสอบว่าอักขระที่กรอกลงไปมีทั้งหมด 5 ตัวหรือไม่ โดยใช้สูตร LEN(B2) = 5 เป็นการบังคับให้ต้องกรอกอักขระ 5 ตัวเท่านั้น

เงื่อนไขสุดท้าย จะเริ่มจากตัดอักขระ 4 ตัวจากด้านขวา โดยใช้สูตร RIGHT(B2,4) จากนั้นจะแปลงอักขระเป็นตัวเลขโดยใช้ สูตร VALUE สุดท้ายจะตรวจสอบว่าอักขระที่แปลงเป็นตัวใช้หรือไม่โดยใช้สูตร ISNUMBER 

ผลแสดงได้ดังภาพที่ 1

ภาพที่ 1 ผลการตรวจสอบเงื่อนไข

เอาหล่ะครับ ถึงตอนนี้ท่านผู้อ่านทดลองกรอก Code ในช่อง B2 และสังเกตุผลลัพธ์ใน Cell D2 ดูนะครับ จากนั้นเราจะ Copy สูตร Excel ใน Cell D2 ไปใช้ใน Data Validation มาต่อกันเลยครับ 

1. คลิกเมาส์เลือก Cell B2:B10 เลือก เมนู Data และเลือกทูลบาร์ Data Validation จะปรากฎไดอะล็อก การตรวจสอบความถูกต้องของข้อมูล 

2. คลิก Tab การตั้งค่า

3. ในหัวข้อ อนุญาตให้ เลือก กำหนดเอง

4. ในหัวข้อ Formula ให้วางสูตร Excel ที่ Copy จาก D2 ลงไป ดังแสดงในภาพที่ 2

ภาพที่ 2 การกำหนดเงื่อนไขใน Formula

5. คลิก Tab ข้อความที่ใส่ เพื่อกำหนดการแสดงข้อความเมื่อ Cell B2:B10 ถูกเลือก เป็นการช่วยลดความผิดพลาดลงไปได้อีก ดังแสดงในภาพที่ 3 

ภาพที่ 3 การกำหนดข้อความแจ้งเตือนขณะกรอก Code

6. คลิก Tab การแจ้งเตือนข้อผิดพลาด เพื่อกำหนดข้อความแจ้งเตือนเมื่อมีการกรอก Code ผิดรูปแบบที่กำหนด ดังแสดงในภาพที่ 4

7. คลิกปุ่ม ตกลง เพื่อปิดไดอะล็อก การตรวจสอบความถูกต้องของข้อมูล 

ภาพที่ 4 การกำหนดการแจ้งเตือนข้อผิดพลาดที่เกิดขึ้น

ภาพที่ 5 ผลลัพธ์จากการกรอก Code วัตถุดิบผิดรูปแบบ

เอาหล่ะครับ มาถึงตรงนี้เราก็กำหนดเงื่อนไขเพื่อตรวจสอบความถูกต้องของ Code วัตถุดิบ Cell B2 ถึง B10 เสร็จเรียบร้อย หากท่านกรอก Code วัตถุดิบผิดรูปแบบ Excel ก็จะแจ้งเตือนความผิดพลาดท่านด้วย Dialog ที่เราได้กำหนดไว้ ดังแสดงในภาพที่ 5 เท่านี้เราก็มั่นใจได้แล้วว่าข้อมูลที่เราบันทึกเข้ามาเก็บใน Cell ดังกล่าวมีความถูกต้องแน่นอน หวังว่าจะนำไปประยุกต์ใช้กันต่อได้นะครับ 

บทความที่ใกล้เคียงกัน

การใช้ Data Validation ป้องกันการบันทึกวันเวลาผิดพลาด

Excel Tips: การแสดงผลรวมเวลาที่มากกว่า 24 ชั่วโมง

Excel Tips วันนี้ขอนำเสนอ วิธีการแสดงผลเวลาที่มากกว่า 24 ชั่วโมง Excel Tips นี้จะเป็นประโยชน์กับผู้ที่กำลังใช้ Excel ในงานสำนักงานหลายๆด้านๆ เช่น ผู้ที่กำลังจะประมวลผล KPI ผู้ที่กำลังใช้ excel คำนวณชั่วโมงรวม OT ของพนักงาน หรือ ชั่วโมงรวมการเข้าอบรมของพนักงานเป็นต้น ในบทความที่ผ่านๆมาของผม ได้นำเสนอ ตัวอย่าง KPI บางหัวข้อที่เกี่ยวข้องกับเวลา เช่น lead KPI ค่า MTTR เป็นต้น ตามปกติหากต้องการหาผลรวมของค่าชั่วโมงใน excel จะพบว่า หากผลรวมเวลามีค่าเกิน 24 ชั่วโมง excel จะละเว้นไม่นำค่าที่เกินมาแสดงเนื่องด้วย excel ทราบว่า 1 วันมี 24 ชั่วโมง ดังนั้นความผิดพลาดจากการคำนวณก็จะเกิดขึ้นซึ่งมีผลต่อความถูกต้องของผลลัพธ์ในลำดับถัดไปได้ เรามาดูตัวอย่างการหาผลรวมจำนวนชั่วโมงซึ่งใช้สูตร excel : SUM ให้ผลลัพธ์ที่ผิดออกมา ดังภาพที่ 1  โดยผลลัพธ์ที่ถูกต้องควรเป็น 30 ชั่วโมง

ภาพที่ 1 สูตร excel : SUM ให้ผลรวมเวลาผิด

ผลรวมเวลาที่ถูกต้องสามารถหาได้โดยการแก้ไขรูปแบบการแสดงผลของ CELL C11 ใหม่จากเดิม h:mm เป็น [h]:mm หรือหากต้องการแสดงค่าวินาทีด้วยก็สามารถแก้รูปแบบแสดงผลเป็น [h]:mm:ss ผลลัพธ์จากสูตร excel แสดงดังภาพที่ 2

ภาพที่ 2 สูตร excel สำหรับรวมเวลาหลังแก้ไขรูปแบบการแสดงผล

Excel Tips : ตอนการลบข้อมูลที่ซ้ำกันออกจากรายการ

บทความการใช้ excel บทความสุดท้ายของปี 2555 นี้ผมจะขอนำเสนอ excel tips เรื่องการลบข้อมูลที่ซ้ำกันออกจากรายการ ซึ่งผมเชื่อว่าเป็นปัญหาหนึ่งของผู้ใช้งาน excel ที่ต้องพบอยู่เป็นประจำครับ ยกตัวอย่างเช่น ในคอลัมน์ B แสดงรายชื่อลูกค้าของบริษัทหนึ่งดังแสดงในภาพที่ 1 โดยพบว่าในรายการชื่อลูกค้าดังกล่าวมีชื่อลูกค้าที่ซ้ำกันอยู่ หากต้องการลบชื่อลูกค้าที่ซ้ำกันออกไปเพื่อให้เหลือข้อมูลที่เป็นหนึ่งเดียวเราจะเริ่มดำเนินการดังนี้

ภาพที่ 1 รายการข้อมูล รายชื่อลูกค้า

1 ให้ท่านทำการเรียงข้อมูลในหลัก Bใหม่โดยเลือก CELL B2 - B16 และคลิกเมาส์ขวาเลือกคำสั่ง เรียงลำดับ เลือก เรียงลำดับจาก ก ถึง ฮ จะได้ผลดังแสดงในภาพที่ 2

ภาพที่ 2 ผลการจัดเรียงรายการข้อมูล

2. พิมพ์สูตร excel ใน cell C3 ดังนี้ =B2=B3 ดังแสดในภาพที่ 3 เพื่อตรวจสอบข้อมูลซ้ำกับข้อมูลถัดไป (ผลลัพธ์เป็น TRUE หรือ FALSE) โดยหากข้อมูลซ้ำกันจะแสดงผลเป็น TRUE 

ภาพที่ 3 สูตร excel สำหรับตรวจสอบการซ้ำกัน

3. สำเนาสูตร excel ลงไปใน cell C3 - C16 จะได้ผลดังแสดงในภาพที่ 4

ภาพที่ 4 ผลการคำนวณด้วยสูตร excel

4. ทำการกรองข้อมูลในรายการโดยพิจารณาข้อมูลในหลัก C โดยเลือกแสดงข้อมูลที่เป็น TRUE ดังนี้

      4.1 เลือกเซลล์ C1 เลือกเมนู ข้อมูล เลือกคำสั่ง ตัวกรอง...

      4.2 เลือกแสดงข้อมูลที่เป็น TRUE ดังแสดงในภาพที่ 5 

      4.3 คลิกปุ่มตกลง excel จะแสดงข้อมูลที่หลัก C มีค่าเป็น TRUE ดังแสดงในภาพที่ 6

ภาพที่ 5 การใช้ ตัวกรอง ใน excel

ภาพที่ 6 ผลการใช้ตัวกรอง ใน excel

5. ทำการลบข้อมูลในแถวที่แสดงดังกล่าว โดยใช้คำสั่ง Delete Row

6. ทำการยกเลิกคำสั่งตัวกรองและเคลียร์สูตร excel ในหลัก C จะได้รายการข้อมูลที่ไม่ซ้ำกันดังแสดงในภาพที่ 7

ภาพที่ 7 ผลการลบข้อมูลที่ซ้ำกันใน excel

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

Excel Tips : การประยุกต์ใช้ List เข้ากับ VLOOKUP

สวัสดีครับ Excel tips บทนี้จะนำเสนอสั้นๆนะครับเป็นการนำความรู้จาก บทความเรื่อง การสร้างตารางข้อมูลแบบไดนามิกส์สำหรับใช้ในสูตร excel : VLOOKUP ซึ่งในบทความดังกล่าวได้กล่าวถึงการสร้างตารางข้อมูลที่มีความสามารถอัพเดตข้อมูลแบบอัตโนมัติรวมถึงยังป้องกันไม่ให้รายการหลักหรือคีย์หลักมีการซ้ำกันเกิดขึ้นได้ ในบทความนี้ผมจะขอนำความรู้จากบทความเรื่อง Excel tips การสร้างรายการข้อมูลด้วย Data validation เข้ามาร่วมด้วย โดยจะกำหนดให้ CELL B2 มีสมบัติเป็น List รายการ เพื่อให้ผู้ใช้สามารถเลือกรายการหรือคีย์หลักที่ต้องการได้โดยง่าย โดยสามารถกำหนดได้ดังนี้

1. เลือก CELL B2

2. ไปที่เมนู ข้อมูล เลือก การตรวจสอบความถูกต้องของข้อมูล... จะปรากฎหน้าต่าง การตรวจสอบความถูกต้องของข้อมูล

3. เลือกแท๊บ การตั้งค่า ในหัวข้อ อนุญาตให้ เลือก รายการ

4. พิมพ์สูตร Excel เพื่อสร้างรายการแบบไดนามิกส์ ในหัวข้อ แหล่งข้อมูล ดังนี้

สูตร Excel : OFFSET($B$2,0,0,COUNTA($B$2:$B$100),1)

5. กดปุ่ม ตกลง เพื่อปิดหน้าต่าง 

ภาพที่ 1 การกำหนดข้อมูลแบบไดนามิกส์ใน excel

ผลการประยุกต์ใช้  List เข้ากับสูตร excel : VLOOKUP แสดงได้ดังภาพที่ 2

ภาพที่ 2 ผลการประยุกต์ใช้  List เข้ากับสูตร excel : VLOOKUP

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

Excel Tips : การสร้างตารางข้อมูลแบบไดนามิกส์สำหรับใช้ใน สูตร excel VLOOKUP

Excel Tips บทความนี้ขอนำเสนอการสร้างตารางข้อมูลแบบไดนามิกส์ครับ โดยสมมุติว่าท่านผู้อ่านมีความรู้ความเข้าใจเกี่ยวกับสูตร excel : VLOOKUP มาอย่างดีแล้ว เรามาดูที่มาของ Tips ที่ผมจะนำเสนอกันก่อนครับ การใช้สูตร excel :VLOOKUP สิ่งที่สำคัญคือ table_array ซึ่งเป็นตารางข้อมูลสำหรับใช้ค้นหาและเก็บข้อมูลไว้สำหรับค้นหาครับ รูปแบบของตารางข้อมูลก็จะเรียงกันเป็นแถวแต่ละแถวจะเรียกว่า record ซึ่งในการใช้งานจริงข้อมูลในตารางสามารถเพิ่มหรือลดได้ ดังนั้นหากเรากำหนดให้ table_array ของเราเป็นข้อมูลแบบสถิต จะทำให้เราต้องเสียเวลากำหนดช่วงข้อมูลใหม่ทุกครั้งที่มีการเปลี่ยนแปลงตารางข้อมูล ดังนั้นเราจึงทำการสร้างตารางข้อมูลแบบไดนามิกส์สำหรับใช้ในสูตร excel :VLOOKUP โดยมีข้อกำหนดดังนี้

1.รายการข้อมูลในคีย์หลักจะต้องไม่ซ้ำกัน ซึ่งเราสามารถกำหนดได้ รายละเอียดดูได้ใน excel การป้องกันรายการข้อมูลซ้ำกัน

2. สร้างตารางข้อมูลแบบไดนามิกส์โดยใช้สูตร excel : OFFSET ผ่านวิธีการกำหนดข้อมูลแบบพลวัตร

มาดูตัวอย่างกันเลยครับ

สมมุติรูปแบบของ table_array เป็นดังภาพที่ 1

ภาพที่ 1 รูปแบบของ table_array ใน excel

จากรูปแบบของ table_array จะกล่าวได้ว่า table_array มี 5 ฟิลด์ และกำหนดให้จำนวน record สูงสุดเท่ากับ 1,000 แถว

ดังนั้นเราสามารถกำหนด Data validation แบบ Custom เพื่อป้องกันรายการข้อมูลซ้ำกันใน B4 ได้ดังนี้
สูตร excel : COUNTIF($B$4:$B$1004,B4)=1
Copy สูตรดังกล่าวและวางลงใน B5 – B1003

สร้าง table_array แบบไดนามิกส์ โดยไปที่เมนู สูตร เลือก กำหนดชื่อ จะปรากฏหน้าต่าง ชื่อใหม่ ให้กำหนดชื่อข้อมูลเป็น tb_cutomer ขอบเขตเป็น สมุดงาน และกำหนดสูตร excel ใน หัวข้ออ้างอิงไปยัง โดยใช้ สูตร excel ดังนี้
OFFSET(Sheet1!$B$4,0,0,COUNTA(Sheet1!$B$4:Sheet1!$B$1003),5)

 

ภาพที่ 2 การกำหนดข้อมูลแบบไดนามิกส์ใน excel

ทดลองสร้างสูตร excel: VLOOKUP เพื่อแสดงผลการค้นหาข้อมูลตามรหัสพนักงาน โดยกำหนดให้ผลการค้นหาแสดงในแถวที่ 2 โดยกำหนดให้ผู้ใช้ พิมพ์รหัสลูกค้าใน B2 และแสดงรายชื่อ ที่อยู่ เบอร์โทรศัพท์ และ ผู้ติดต่อ ใน C2, D2, E2 และ F2 ตามลำดับ โดยใช้สูตร excel ดังนี้


C2 : IFERROR(VLOOKUP(B2,tb_customer,2,False),”Data does not found”)

D2 : IFERROR(VLOOKUP(B2,tb_customer,3,False),”Data does not found”)

E2 : IFERROR(VLOOKUP(B2,tb_customer,4,False),”Data does not found”)

F2 : IFERROR(VLOOKUP(B2,tb_customer,5,False),”Data does not found”)

ทดลองพิมพ์รหัสลูกค้า A003 ใน B2 Excel จะแจ้งผลการค้นหาว่า Data does not found ดังแสดงในภาพที่ 3

ภาพที่ 3 ผลการค้นหาในกรณีที่ไม่พบข้อมูล

ทดลองเพิ่มข้อมูล A003 ในแถวที่ 6 และเมื่อทดสอบพิมพ์รหัสลูกค้า A003 ลงใน B2 จะได้ผลลัพธ์ดังแสดงในภาพที่ 4

ภาพที่ 4 ผลการค้นหาในกรณีที่ข้อมูล

สรุปในบทความนี้ เราสามารถสร้างตารางข้อมูลแบบไดนามิกส์สำหรับใช้งานในสูตร excel  VLOOKUP ได้ โดยตารางข้อมูลดังกล่าวมีประสิทธิภาพสูง ในบทความต่อไปเราจะประยุกต์ วิธีการนำรายการข้อมูลจาก Data validation มาใช้ร่วมกับสูตร excel : VLOOKUP ซึ่งจะช่วยเพิ่มประสิทธิภาพการค้นหาข้อมูลด้วย excel ให้มากขึ้น สวัสดีครับ

Excel Tips : การสร้างรายการข้อมูลด้วย Data Validation

Excel Tips ในบทความนี้ขอนำเสนอการสร้างรายการข้อมูล (List Data) ใน Excel ด้วยการใช้ Data validation ครับ ต่อเนื่องจาก Excel tips ในเรื่องการป้องกันการซ้ากันของรายการข้อมูล มาดูแนวทางการนำไปใช้ เช่น สร้างเป็นรายการข้อมูลให้ผู้ใช้เลือก หรือรายการ คีย์หลัก สำหรับค้นหาในฐานข้อมูลเป็นต้น ดังนั้นสมบัติของรายการข้อมูลที่ต้องมีคือข้อมูลในรายการต้องไม่ซ้ำกัน และจะต้องอัพเดตรายการข้อมูลอยู่เสมอหากมีการเพิ่มหรือลดจำนวนรายการข้อมูล จากข้อกำหนดดังกล่าว เราจะใช้ความรู้จากเรื่อง การป้องกันการซ้ากันของรายการข้อมูลและการสร้างรายการข้อมูลแบบอัตโนมัติเข้าช่วยครับ มาดูขั้นตอนการสร้างรายการข้อมูลกันเลยครับ

สมมุติให้ ข้อมูลหลักหรือ คีย์หลัก ถูกบันทึกลงใน คอลัมภ์ B โดยเริ่มตั้งแต่ B2 เป็นต้นไป และสมมุติให้บันทึกได้ถึง B100 

เราจะเริ่มเขียนสูตร excel ใน Data validation เพื่อป้องกันการซ้ำกันของข้อมูล รายละเอียดสามารถดูได้ในบทความ Excel tips เรื่อง การป้องกันการซ้ากันของรายการข้อมูล โดยสูตร excel สำหรับ B2 คือ

= COUNTIF($B$2:$B$100,B2)=1

สำหรับ B3 ,..., B100 ก็สามารถเขียนได้ในทำนองเดียวกัน

กำหนดให้ D2 เป็น CELL แสดงรายการข้อมูล เราสามารถใช้  Data validation และเลือกการตั้งค่าเป็น รายการ หรือ List และเขียนสูตร excel ได้เป็น 

=OFFSET($B$2,0,0,COUNTA($B$2:$B$100),1)

ภาพที่ 1 การตั้งค่าใน Data validation โดยใช้สูตร excel

ผลการสร้างรายการข้อมูลใน excel แสดงได้ดังภาพที่ 2

ภาพที่ 2 ผลการสร้างรายการข้อมูลใน excel ด้วย Data validation

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

Excel Tips : การป้องกันรายการข้อมูลซ้ำกัน (Excel Data validation)

 Excel Tips ในบทความนี้ขอนำเสนอการป้องกันรายการข้อมูลซ้ำกัน ซึ่งเป็นประโยชน์มากกับท่านผู้อ่านที่ต้องการบันทึกข้อมูลลงใน excel เพื่อนำไปใช้ในการทำรายการ  List แบบอัตโนมัติ หรือเป็นตารางข้อมูลในสูตร excel :  vlookup หรือสูตร excel : Index และ สูตร excel : MATCH ซึ่งโดยส่วนใหญ่ข้อมูลที่เราไม่ต้องการให้ซ้ำกันจะเรียกว่าคีย์หลัก (Primary key) การป้องกันการซ้ำกันเราจะอาศัยวิธีการ Data Validation (การตรวจสอบความถูกต้องของข้อมูล) ครับ โดยจะกำหนดสูตร excel เพื่อตรวจสอบข้อมูลก่อนจะบันทึกลงใน CELL มาดูวิธีการกันเลยครับ สมมุติเราต้องการให้รายการข้อมูลตั้งแต่ CELL B2 จนถึง B10 ต้องไม่มีข้อมูลซ้ำกัน ทำได้ดังนี้

1   คลิกเลือก B2

2 ไปที่เมนูข้อมูลเลือก เครื่องมือ Data Validation... จะปรากฏหน้าต่าง การตรวจสอบความถูกต้องของข้อมูล ให้คลิกเลือกแท็บ การตั้งค่า 

3 ใน List อนุญาตให้เลือก กำหนดเอง (Custom)

4 พิมพ์สูตร excel   = COUNTIF($B$2:$B10,B2) = 1 ดังแสดงในภาพที่ 1 เพื่อตรวจสอบความถูกต้องของข้อมูล

5 เลือก B2 และทำการลากคลุมถึง B10 เพื่อ copy สูตร excel ใน B2 

6 ทดลองพิมพ์ข้อมูลใน B2 เป็น A001

7 ทดลองพิมพ์ข้อมูลใน B3 เป็น A001 ท่านจะพบหน้าต่างแสดงความผิดพลาดดังแสดงในภาพที่ 2 ซึ่งหมายถึงว่าตอนนี้ข้อมูลในเซลล์ B2 จนถึง B10 ถูกตรวจสอบการซ้ำกันของข้อมูลเรียบร้อยแล้ว

ภาพที่ 1 หน้าต่างสำหรับกำหนดสูตร excel เพื่อตรวจสอบความถูกต้องของข้อมูล

ภาพที่ 2 หน้าต่างแสดงความผิดพลาดจากการบันทึกข้อมูลที่ซ้ำกันลงใน B3

จาก Excel Tips นี้เราก็จะนำไปเป็นขั้นตอนการตรวจสอบการซ้ำกันของข้อมูลก่อนนำไปใช้งานอื่นๆซึ่งจะนำเสนอต่อไป สวัสดีครับ

Excel Tips: การกำจัดอักขระที่ไม่เป็นตัวเลขในข้อความ

วันนี้ Excel Tips แบบง่ายๆที่จะนำเสนอคือ การกำจัดอักขระที่ไม่เป็นตัวเลขในข้อความ ซึ่งท่านผู้อ่านจะพบได้มากในกรณีการนำเข้าข้อมูลรูปแบบอื่นๆมายัง Excel ดังนั้นก่อนจะดำเนินการวิเคราะห์หรือนำข้อมูลไปใช้เราต้องจัดการกำจัดอักขระเหล่านี้ออกไปก่อนครับ เป็นที่ทราบอยู่แล้วว่าอักขระทั้งหมดมี 255 ตัว เราสามารถแสดงอักขระเหล่านั้นใน CELL ได้โดยใช้สูตร excel : Char(number) โดย number จะเป็นตัวเลขระหว่าง 1 - 255 ในตัวอย่างนี้จะแสดงตัวอักขระแต่ละตัวในคอมลัมภ์ D โดยจะเรียงตั้งแต่อักขระตัวที่ 1 ถึง ตัวที่ 255 ในแถวถัดไป ซึ่ง number จะได้จากสูตร excel : ROW() ซึ่งจะคืนค่าหมายเลขแถวของ CELL ดังกล่าว ดังนั้นเราจะเขียนสูตร excel เพื่อแสดงอักขระแต่ละตัวได้ดังนี้

สมมุติให้ D1 เป็นอักขระตัวที่ 1 จะเขียนสูตร excel ใน D1 ได้เป็น 

= CHAR(ROW())

และเมื่อ copy สูตร excel ลงไปตามแถวจนถึงแถวที่ 255 ในหลัก D เราจะได้อักขระแสดงได้ดังภาพที่ 1

ภาพที่ 1 อักขระที่ได้จากสูตร excel : CHAR

ผลการใช้สูตร Excel : CHAR เราจะพบว่าอักขระที่เป็นตัวเลขอารบิก 0-9 จะเป็นอักขระตัวที่ 48-57 ขณะที่ตัวอักขระภาษาอังกฤษตัวพิมพ์ใหญ่จะเป็นอักขระตัวที่ 65-90 และ ตัวพิมพ์เล็กจะเป็นอักขระตัวที่ 97-122

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

Substitute(text,old text,new text) โดย

text คือข้อความต้นฉบับ

old text คือข้อความหรืออักขระภายในข้อความต้นฉบับที่ต้องการกำจัด

new text คือข้อความที่ต้องการแทนที่ old text ซึ่งในที่นี้คือค่าว่าง ""

เราจะแทนที่อักขระที่ไม่ต้องการด้วยค่าว่างและจะทำการตรวจสอบข้อความเพื่อกำจัดอักขระที่ไม่ต้องการและแทนที่ด้วยค่าว่างตั้งแต่อักขระตัวที่ 1 -255 โดยยกเว้นอักขระตัวที่ 48-57 ดังนั้นเราจะต้องลบสูตร Excel : CHAR ในแถวที่ 48 - 57 ออกก่อน สมมุติให้ข้อความที่ต้องการเฉพาะตัวเลขอยู่ใน CELL B3 ดังแสดงในภาพที่ 2 เราจะดำเนินการกำจัดอักขระดังกล่าวตามขั้นตอนดังนี้

1. เขียนสูตร Excel :  Substitute ใน CELL E1 เป็น  =SUBSTITUTE(B3,D1,"") เพื่อกำจัดอักขระตัวที่ 1

2. เขียนสูตร Excel :  Substitute ใน CELL E2 เป็น  =SUBSTITUTE(E1,D2,"")  เพื่อกำจัดอักขระตัวที่ 2

3. Copy สูตร Excel ใน CELL E2 ลงไป CELL E3 จนถึง CELL E255 เพื่อกำจัดอักขระตัวที่ 3 - 255 (ยกเว้นตัวที่ 48 -57 ซึ่งเราได้ลบสูตร Excel : CHAR ไปแล้วจะไม่ถูกกำจัด)

แสดงผลลัพธ์การกำจัดอักขระที่ไม่ต้องออกจากข้อความหมดแล้วใน CELL C3 โดยกำหนดให้ค่าใน CELL C3 = E255 ดังแสดงในภาพที่ 2

ภาพที่  2 แสดงผลการกำจัดอักขระที่ไม่ใช่ตัวเลขหมดแล้ว

จากตัวอย่างท่านผู้อ่านสามารถประยุกต์ได้อีกมากครับ เช่นหากต้องการทั้งตัวเลข ตัวอักษร ภาษาไทยภาษาอังกฤษ ก็สามารถทำได้ โดยสิ่งสำคัญต้องทราบก่อนคืออักขระที่เราต้องการเป็นอักขระลำดับที่เท่าไหร่จากนั้นให้ไปลบสูตร excel : CHAR(ROW()) ในแถวนั้นก่อนดังตัวอย่างที่นำเสนอไป เท่านี้เราก็ได้ข้อความที่สามารถนำไปใช้งานได้แล้ว 

Excel trip : การกำหนดข้อมูลแบบพลวัตร (Dynamics data)

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

สูตร excel ที่ใช้คือ สูตร excel : OFFSET ซึ่งเป็นสูตรที่จะคืนค่าตำแหน่งของช่วงข้อมูลกลับมาให้กับผู้ใช้ โดยมีรูปแบบดังนี้

OFFSET(REF CELL, ROW,COL,HEIGHT,WIDTH)
โดย REF CELL คือ เซลล์ที่ใช้สำหรับอ้างอิงการกำหนดช่วงข้อมูล
ROW คือค่าตำแหน่งแถวเริ่มต้นของช่วงข้อมูล โดยอ้างอิงจาก REF CELL
COL คือ ค่าตำแหน่งหลักเริ่มต้นของช่วงข้อมูล โดยอ้างอิงจาก REF CELL
HEIGHT คือจำนวนแถวของช่วงข้อมูล
WIDTH คือจำนวนหลักของช่วงข้อมูล
ตัวอย่างเช่น
OFFSET($A$1,0,0,3,2) จะคืนค่า A1:B3
OFFSET($A$1,1,0,3,2) จะคืนค่า A2:B4

ลักษณะของข้อมูลแบบพลวัตรจำนวนแถวหรือจำนวนหลักของช่วงข้อมูลมักจะมีการเปลี่ยนแปลง อาจจะเพิ่มขึ้นหรือลดลง ดังนั้นสูตร excel ของเราต้องมีการคำนวณหาจำนวนแถวและจำนวนหลักอยู่ตลอดเวลาเพื่ออัพเดตข้อมูล การนับจำนวนแถวหรือจำนวนหลักจะใช้สูตร excel : COUNTA เพื่อหาจำนวนแถวหรือหลักที่มีข้อมูล มาดูตัวอย่างสูตร excel ที่ใช้หาตำแหน่งของช่วงข้อมูลกันเลยครับ สมมุติเรามีข้อมูลรายชื่อลูกค้าอยู่ในหลัก B ดังภาพที่ 1 

ภาพที่ 1 ตัวอย่างข้อมูล

เราสามารถกำหนดช่วงข้อมูลรายชื่อลูกค้าได้ดังนี้ครับ
OFFSET($B$3,1,0,COUNTA($B:$B)-1,1)
ข้อสังเกต จำนวนหลักเป็นค่าคงที่ 1 เนื่องจากข้อมูลรายชื่อลูกค้าเป็นแบบเวกเตอร์ (ต้องการข้อมูลเพียงหลักเดียว)
และเราสามารถกำหนดตารางข้อมูลลูกค้าได้ดังนี้
OFFSET($B$3,1,0,COUNTA($B:$B)-1,3)
ข้อสังเกต จำนวนหลักเป็นค่าคงที่ 3 เนื่องจากตารางข้อมูลลูกค้ามีจำนวนหลักคงที่
หากจำนวนหลักของข้อมูลมีโอกาสเพิ่มหรือลดลงได้ท่านผู้อ่านต้องใช้สูตร excel : COUNTA นับจำนวนหลักด้วย
ข้อสังเกต จำนวนแถวของช่วงข้อมูลที่ได้จากสูตร excel : COUNTA จะต้องถูกลบด้วย 1 เนื่องด้วยแถวที่ 3 เป็นชื่อของข้อมูล
ท่านผู้อ่านสามารถกำหนดชื่อของช่วงข้อมูลตามขั้นตอนการกำหนดชื่อดังแสงในบทความการกำหนดชื่อข้อมูลแบบคงที่ โดยในขั้นตอน อ้างอิงไปยังให้พิมพ์สูตร excel : OFFSET โดยสมมุติให้ชื่อข้อมูลรายชื่อลูกค้าเป็น CUSTOMER ขณะที่ชื่อตารางข้อมูลลูกค้าเป็น tb_cus ดังแสดงในภาพที่ 2

ภาพที่ 2 การกำหนดชื่อข้อมูลใน excel

ตัวอย่างการใช้งาน

นำไปใช้ในการค้นหาข้อมูลรายละเอียดลูกค้าได้ดังแสดงในภาพที่ 3 โดยในช่องรายชื่อ จะใช้การแสดงรายการโดยกำหนดข้อมูลเป็นแบบ Data validation และเลือกชนิดเป็นแบบ List และแหล่งข้อมูล(Source) จะเป็นข้อมูลชื่อ CUSTOMER ดังแสดงในภาพที่ 5 ส่วนของ ที่อยู่ และ เครดิต เราจะใช้สูตร Excel : vlookup เพื่อช่วยค้นหารายละเอียดลูกค้าโดยอ้างอิงจากรายชื่อลูกค้า โดยสูตร Excel : vlookup มีดังนี้

vlookup(G9,tb_cus,2,false) สำหรับ ที่อยู่
vlookup(G9,tb_cus,3,false) สำหรับ เครดิต

ภาพที่ 3 การใช้ excel ทำระบบการค้นหา

หากข้อมูลลูกค้าดังภาพที่ 1 มีการเปลี่ยนแปลง List รายการในการค้นหาก็จะมีการอัพเดตตามดังแสดงในภาพที่ 4



ภาพที่ 4 ตัวอย่างเมื่อข้อมูลมีการอัพเดต

จากคำอธิบายและตัวอย่างการกำหนดข้อมูลใน excel ที่ได้นำเสนอมาทั้งสองบทความ ก็หวังว่าท่านผู้อ่านจะนำไปประยุกต์ใช้กับการใช้ excel ของท่านได้อย่างมีประสิทธิภาพครับ เพราะการกำหนดข้อมูลถือว่าเป็นพื้นฐานการใช้งาน Excel ขั้นสูงต่อไป

ภาพที่ 5 การกำหนดรายการลูกค้า



Excel tips : การกำหนดชื่อข้อมูลแบบคงที่ใน excel (Static data)

วันนี้ขอนำเสนอบทความ tips การใช้ excel เพื่อเป็นพื้นฐานในการใช้ excel หรือใช้สูตร excel ในงานอื่นๆต่อไปครับ การกำหนดชื่อข้อมูลใน excel มีประโยชน์อย่างมาก ในกรณีที่เราต้องเรียกใช้งานข้อมูลนั้นบ่อยๆ การพิมพ์ช่วงของข้อมูลหรือตำแหน่งเซลล์ของข้อมูลมีโอกาสเกิดข้อผิดพลาดได้เช่น สูตร excel สำหรับการคำนวณภาษีมูลค่าเพิ่ม หรือ การกำหนดตารางข้อมูลเพื่อใช้ในสูตร excel : vlookup เป็นต้น ผมจะแบ่งชื่อข้อมูลแบบคงที่ใน excel ออกเป็น 3 รูปแบบดังนี้ครับ

1. ข้อมูลประเภท ค่าคงที่ เช่น ภาษีมูลค่าเพิ่ม อัตราแลกเปลี่ยน
2. ข้อมูลประเภทเป็นช่วงข้อมูลแบบรายการ (อาจจะเป็นแบบแถวเดียวหรือหลักเดียว) ผมจะเรียกว่าข้อมูลแบบเวกเตอร์ เราจะพบการใช้งานแสดง List รายการต่างๆให้ผู้ใช้เลือก
3. ข้อมูลประเภทตาราง จะเรียกว่าข้อมูลแบบ Matrix เราจะพบในการใช้งานสูตร excel : vlookup เป็นส่วนใหญ่

ข้อดีของการกำหนดชื่อข้อมูล

1. ลดความผิดพลาดในการใช้สูตร excel
2. ง่ายต่อการเรียกใช้ข้อมูลเนื่องจากชื่อข้อมูลที่กำหนดมีความหมายในตัวเองเช่น vat , customer_name , Table เป็นต้น
3. ง่ายต่อการพิมพ์สูตร excel

ข้อเสีย

1. ใช้ได้อย่างถูกต้องกับข้อมูลที่ไม่มีการเปลี่ยนแปลงขณะใช้งาน

วิธีการกำหนดชื่อใน excel 2007 ขึ้นไป

1. เลือกเมนู สูตร
2. เลือกทูลบาร์ กำหนดชื่อ จะปรากฎหน้าต่าง ชื่อใหม่ ขึ้นมาดังรูปที่ 1
3. กำหนดชื่อข้อมูลในช่อง ชื่อ
4. เลือกขอบเขตการใช้ข้อมูล ในหัวข้อนี้เราสามารถกำหนดให้ข้อมูลที่เรากำหนดสามารถเรียกใช้ได้ตามขอบเขตที่กำหนด อาจจะทั้งหมด (สมุดงาน) หรือว่า เจาะจงใช้ได้เฉพาะ WorkSheet
5. กำหนดตำแหน่งของ cells หรือช่วงของ Cells ที่ต้องการกำหนดชื่อ ในช่องอ้างอิงไปยัง
1. ตัวอย่างเช่น =Sheet1!$F$12 จะเป็นข้อมูลแบบ ค่าคงที่
2. ตัวอย่างเช่น =Sheet1!$F$12:$F$15 จะเป็นข้อมูลแบบเวกเตอร์
3. ตัวอย่างเช่น =Sheet1!$F$12:$G$15 จะเป็นข้อมูลแบบ Matrix
6. คลิกปุ่มตกลง

ภาพที่ 1 ตัวอย่างการกำหนดชื่อข้อมูลใน excel

ตัวอย่างการใช้งาน excel
ข้อมูลแบบค่าคงที่
พิมพ์ค่า 7% ใน Cell A1 กำหนดชื่อข้อมูลแบบค่าคงที่ ชื่อ vat โดยอ้างถึง Cell A1 หากต้องการคำนวณภาษีมูลค่าเพิ่มจากราคาสินค้าใน cells B3จะเขียนสูตร excel ใน Cell C3 ได้ดังนี้
สูตร excel :  C3= vat*B3
ข้อมูลแบบเวกเตอร์
พิมพ์ข้อมูลชื่อลูกค้าลงใน Cells A1 – A5
กำหนดชื่อข้อมูลแบบเวกเตอร์ ชื่อ Customer โดยอ้างถึง $A$1:$S$5
สามารถนำมาใช้เป็น List data ในการตรวจสอบความถูกต้องของข้อมูล โดยกำหนดแหล่งข้อมูล = Customer
ข้อมูลแบบ Matrix
พิมพ์ข้อมูลลงใน Cells A1 – C5
กำหนดชื่อข้อมูลแบบ Matrix ชื่อ data โดยอ้างถึง $A$1:$C$5
สามารถนำมาใช้ในสูตร excel : vlookup เช่น
=vlookup(B4,data,2,false) เป็นต้น

ก่อนจบบทความนี้อยากให้ท่านผู้อ่านคิดต่อนะครับว่า หากต้องการเพิ่มข้อมูลในแบบเวกเตอร์หรือแบบ Matrix เช่น มีลูกค้าเพิ่มขึ้นมาอีก 2 ราย เราจะทำอย่างไร หากเป็นการกำหนดข้อมูลแบบเดิมก็ต้องกลับไปแก้ไขข้อมูลในช่องอ้างอิงไปยังเพื่อให้ครอบคุลมช่วงของข้อมูลที่เพิ่มขึ้นมา  ซึ่งการกระทำดังกล่าวจะทำให้ไม่สะดวกในการใช้งาน ซึ่งเป็นจุดด้อยของการกำหนดชื่อข้อมูลแบบคงที่ ดังนั้นในบทความต่อไปผมจะนำเสนอวิธีการกำหนดชื่อข้อมูลแบบพลวัตร (Dynamic Data) เพื่อให้ข้อมูลมีการอัพเดตและครอบคลุมข้อมูลที่เพิ่มขึ้นมาได้อย่างอัตโนมัติ สวัสดีครับ

Excel tips: pdf creator สำหรับแปลงเป็น pdf

pdf creator เป็น free program ที่ใช้สำหรับแปลงไฟล์ต่างๆให้เป็น pdf ท่านผู้อ่านสามารถเข้าไป download pdf creator ได้ที่นี่ บางครั้งเราก็จำเป็นต้องส่งข้อมูลที่ได้จากการดำเนินการด้วยสูตร excel ต่างๆ เช่น การดำเนินการด้วยสูตรexcel : vlookup การใช้ pivottable ช่วยทำรายงานกับข้อมูลต่างๆใน excel ในรูปแบบไฟล์ pdf เพื่อป้องกันความผิดพลากจากการอ้างอิงสูตร excel หรือป้องกันการถูกแก้ไขสูตร excel วิธีการแปลงเป็น pdf ก็ไม่ยากครับ หากท่าน download pdf creator เสร็จแล้ว ให้ท่านทำการติดตั้ง pdf creator ตามปกติครับ จากนั้นหากต้องการแปลงข้อมูลใน excel เราก็สามารถทำได้โดยไปที่ File->print… จะปรากฎหน้าต่าง พิมพ์ โดยในหน้างต่างพิมพ์ ในช่องชื่อเครื่องพิมพ์ ให้เราเลือก PDFCreator ดังแสดงในภาพ นอกจากนี้เราสามารถปรับแต่งรูปแบบการพิมพ์ได้เหมือนการพิมพ์ออกเครื่องพิมพ์ครับ เพียงแต่การพิมพ์ด้วย pdf creator เป็นการแปลงออกเป็นไฟล์ pdf ดังแสดงในภาพที่ 1

ภาพที่ 1 หน้าต่างกำหนดการพิมพ์ (pdf creator)

เมื่อกำหนดรูปแบบการพิมพ์เสร็จสมบูรณ์แล้วก็ให้กดปุ่มตกลงเพื่อให้ pdf creator แปลงข้อมูลที่เราเลือกออกเป็นไฟล์ pdf โดยจะแสดงหน้าต่างดังภาพที่ 2 ให้เรากดปุ่ม Save จะปรากฎหน้าต่าง Save As ให้เรากำหนดชื่อไฟล์ผลลัพธ์การแปลง ตำแหน่งที่เก็บไฟล์ผลลัพธ์ และชนิดของไฟล์ที่ต้องแปลง ดังแสดงในภาพที่ 3

ภาพที่ 2 หน้าต่างการกำหนดค่าของ pdf creator

ภาพที่ 3 หน้าต่าง pdf creator Save As

กดปุ่ม Save เสร็จแล้ว pdf creator จะดำเนินการแปลงข้อมูลใน excel ที่เรากำหนดให้เป็นรูปแบบไฟล์ pdf ที่เรากำหนดครับ

เสร็จแล้วครับขั้นตอนการใช้งาน pdf creator จะเห็นว่าสามารถแปลงเป็น pdf ได้ง่ายมากที่สำคัญเป็นของฟรีด้วยครับ แถมท้ายมาดูกันว่านอกจากการแปลงเป็น pdf แล้ว pdf creator สามารถแปลงข้อมูลเป็นไฟล์ชนิดไหนได้อีก ไปดูกันเลยครับ

1. ไฟล์ภาพ .png ,.jpg, .bmp , .pcx , .tif ,
2. postscrip file .ps , .eps
3. text file .txt
4. Adobe Photoshop .psd

excel tips

1. การสลับแถวสลับหลักของข้อมูล
2. การทำซ้ำด้านบน (Repeat Row)
3. การแปลงเป็น pdf จากไฟล์หลายรูปแบบ
   

Excel Tips: การสลับแถวสลับหลักของข้อมูล

Excel Tips ที่จะเสนอในบทความนี้คือวิธีการสลับแถวสลับหลักของข้อมูลในเซลล์ที่เลือกไว้ครับ ในการใช้งาน excel หลายครั้งที่ต้องมีการสำเนาข้อมูลจาก excel file หนึ่งไปยัง excel file หนึ่ง และมีอีกสิ่งหนึ่งที่มีโอกาสพบเจอนั่นคือรูปแบบการจัดเรียงข้อมูลใน excel file ปลายทางไม่ตรงกับการจัดเรียงข้อมูลใน excel file ต้นทาง มาดูตัวอย่างปัญหากันเลยครับ

100.99

20

30.45

50.85

ลักษณะการวางข้อมูล excel file ต้นทางมีลักษณะเป็นเมตริกซ์ขนาด 1x4

24
24.6
1000
105

ลักษณะรูปแบบข้อมูล excel file ปลายทาง

ท่านผู้อ่านสามารถทำได้โดยใช้คำสั่งใน excel ดังนี้ครับ

ให้ทำการ copy ข้อมูลใน excel ต้นทาง จากนั้นให้ท่านผู้อ่านคลิกเมาส์ปุ่มขวาใน excel ปลายทาง จะปรากฎ pop-up menu ให้เลือกคำสั่ง วางแบบพิเศษ… excel จะปรากฎหน้าต่างการวางแบบพิเศษ ดังแสดงในภาพที่ 1

ภาพที่ 1 หน้าต่างการวางแบบพิเศษ

ในหน้าต่างการวางแบบพิเศษให้ผู้ใช้เลือก Check box หัวข้อ สลับเปลี่ยนแถวกับคอลัมน์ ดังแสดงในภาพที่ 1 จากนั้นให้คลิกปุ่ม ตกลง จะทำให้ข้อมูลที่ผู้ใช้สำเนามามีการสลับตำแหน่งของแถวและคอมลัมภ์ดังแสดงในภาพที่ 2

24	100.99
24.6	20
1000	30.45
105	50.85

ภาพที่ 2 ลักษณะของข้อมูลใน excel file ปลายทาง

ท่านผู้อ่านจะเห็นว่าคำสั่งนี้มีประโยชน์มากสำหรับใช้ในการโอนย้ายข้อมูลทีมีเป็นจำนวนมากๆ ก็หวังว่า excel tips ข้อนี้คงเป็นประโยชน์กับผู้ใช้ excel ทุกท่านนะครับ

ทิ้งท้ายไว้ด้วยจากหน้าต่างการวางแบบพิเศษ ในหัวข้อการวาง เราสามารถเลือกการวางแบบ ค่า ได้ในกรณีที่ผู้ใช้ไม่ต้องการสำเนาสูตรการคำนวณจาก excel file ต้นทางซึ่งมีโอกาสเกิดความผิดพลาดได้และข้อมูลที่ผู้ใช้สำเนามาไม่มีจำเป็นที่ต้องเชื่อมโยงกับข้อมูลใน excel file อื่นๆ ก็ฝากไว้สอง tips นะครับ สวัสดีครับ

Excel Tips

การทำซ้ำด้านบน (Repeat a Row)

pdf creator สำหรับแปลงไฟล์ excel

การแปลงเป็น pdf จากไฟล์หลายรูปแบบ