Search

วันพฤหัสบดีที่ 17 มกราคม พ.ศ. 2556

ฟังก์ชันแทนที่และตัดข้อความใน Excel SUBSTITUTE, CLEAN และ TRIM

การเอาช่องว่างและอักขระที่ไม่พิมพ์ออกจากข้อความ

บางครั้งการคัดลอกข้อมูลมาวางบน Excel worksheet หรือข้อมูลที่ได้จากการบันทึกอาจมีช่องว่างนำหน้าหรือต่อท้าย ข้อความ, วันที่ หรือ จำนวนตัวเลข ฯลฯ


ซึ่งไม่สามารถนำมาใช้งานได้ทันที ต้องมาแก้ไขเอาช่องว่างที่ไม่ต้องการออก และการที่จะแก้ไขทีละรายการกับข้อมูลที่มีปริมาณมากๆ จะเป็นการเสียเวลามาก


ซึ่งงานลักษณะนี้เราสามารถใช้บริการฟังก์ชันชุด TRIM, CLEAN และ SUBSTITUTE ช่วยตัดอักขระหรือช่องว่างเหล่านี้ออก จะใช้เดี่ยวๆ หรือใช้รวมกันได้ โดยที่อาจพ่วงการใช้งานร่วมกับตัวอักขระ ASCII ซึ่งมี 255 อักขระ
Functionไวยากรณ์และการทำงานของฟังก์ชัน TRIM, CLEAN

TRIM

=TRIM(text), text คือ ข้อความที่ต้องการเอาช่องว่างออก
using TRIM function in Excel
ฟังก์ชัน Trim จะเอาช่องว่างออกจากข้อความ ยกเว้นช่องว่างระหว่างคำ 1 เคาะ

CLEAN

=CLEAN(text), text คือ ข้อความที่ต้องการเอาอักขระที่พิมพ์ไม่ได้ออก
using CLEAN function in Excel
ฟังก์ชัน Clean จะตัดออกตัวอักขระ ASCII ซึ่งถูกกำหนดให้ใช้กับฟังก์ชั่น CHAR(0)-CHAR(31)

ตัวอย่างการใช้ TRIM และ CLEAN
ABCD
1ข้อมูล การใช้ฟังก์ชันผลลัพท์คำอธิบายการใช้ฟังก์ชัน TRIM, CLEAN
2 Year  2013  =TRIM(A2)Year 2013 ช่องว่างก่อนและหลัง Year ลดลง 1 ช่อง
3 HelloASCII character codes (Char2 and Char7) =CLEAN(A3) Hello ASCII character codes (Char2 and Char7) เป็นอักขระที่ไม่พิมพ์หายไป (ค่า ASCll = 2, 7)
Functionไวยากรณ์และการทำงานของฟังก์ชัน SUBSTITUTE

SUBSTITUTE

=SUBSTITUTE (text,old_text,new_text,instance_num)
using SUBSTITUTE function in excel
ฟังก์ชัน SUBSTITUTE ใช้แทนที่ข้อความเดิม old text ด้วยข้อความใหม่ new text ส่วน instance_num ใช้เพื่อระบุตำแหน่งการแทนที่ ซึ่งถ้าไม่ระบุ old text จะถูกเปลี่ยนด้วย new text ทั้งหมด

ตารางตัวอย่างการใช้ SUBSTITUTE
ABCD
1 Old Text New Textการใช้ฟังก์ชัน SUBSTITUTE ผลลัพท์
2Retail Sales 2013 =SUBSTITUTE(A2,A2,(A2&" "&B2)) Retail Sales 2013
3 Jan 2013  January =SUBSTITUTE(A3,"Jan",B3) January 2013
4 1/1/2012 =SUBSTITUTE(A4,2,3,1) 1/1/3012
5 1/1/2012   =SUBSTITUTE(A4,2,3,2) 1/1/2013


อธิบายตารางตัวอย่างการใช้ SUBSTITUTE
  1. ต่อท้าย Retail Sales ด้วย 2013 โดยใช้ &" "& เชื่อมคำและเว้นระหว่าง "" ก็เพื่อให้มี เคาะ 1 เคาะ ก่อน 2013
  2. แทนที่ Jan ด้วย January
  3. เมื่อระบุ instant_num เป็น 1 ฟังก์ชันจะแทนที่เลข 2 ตัวแรก ผลลัพท์จึงเป็น 1/1/3012
  4. เปลี่ยน instant_num เป็น 2 ฟังก์ชันแทนที่เลข 2 ตัวที่สอง ผลลัพท์กลายเป็น 1/1/2013

วันอังคารที่ 15 มกราคม พ.ศ. 2556

Cut, Copy และ Paste ตัด ก๊อปปี้ และวาง ใน Excel

เทคนิค Cut, Copy, Paste ในExcel  



การตัด, การคัดลอกหรือการก๊อปปี้ใน Excel มีความยืดหยุ่นและทรงประสิทธิภาพมากพอสมควรตรงที่เราเลือกว่าจะ ก๊อปปี้เฉพาะรูปแบบ, ค่า, สูตร ซึ่งทั้งหมดทำได้ด้วยเครื่องมือที่ Excel สร้างไว้รองรับงานต่างๆ อย่างเหลือเฟือ

เริ่มต้นการก๊อปปี้อย่างง่ายที่สุดโดย ใช้คำสั่ง Clipboard ใต้เมนู Home ในการ Copy โดยเลือกเซลล์ที่ต้องการแล้วคลิกคำสั่งจากคลิปบอร์ด

คำสั่งหลักๆใน Clipboard คือ ตัด (Cut), คัดลอก(Copy) และ คัดลอกรูปแบบ Format Painter

Excel's clipboard Cut command1. ย้ายเซลล์ด้วยการตัดหรือแป้นพิมพ์ลัด Ctrl+X แล้ว Enter เซลล์ที่ต้องการวาง
Copy2. คัดลอก อาจใช้แป้นพิมพ์ลัด Ctrl+C แล้ว Enter เซลล์ที่ต้องการวาง
Paste command3. คัดลอกเฉพาะรูปแบบ แล้ววางบนเซลล์ที่ต้องการรูปแบบเดียวกัน

Paste คือส่วนที่มีความพิเศษ ตรงที่แบ่งการใช้งานไว้เพื่อรองรับการใช้งานของ Users

Paste Command in Excel'sClipboard
  • Paste การวาง เป็นการวางเนื้อหาและการจัดรูปแบบของเซลล์ ที่คัดลอกทั้งหมด (Ctrl+V ได้เช่นกัน) มาทั้งรูปแบบ,สี,เส้น, ส่วนสูตรจะถูกเปลี่ยนตำแหน่งอ้างอิงตามเซลล์ปลายทาง
  • Formulas วางเฉพาะสูตรของเซลล์ต้นทาง ไปยังเซลล์ปลายทาง
  • Paste Values วางเฉพาะค่าของเซลล์ต้นฉบับที่ก๊อปปี้มา
  • No Borders เหมือน Paste แต่ไม่เอาเส้นขอบ
  • Transpose สลับเปลี่ยนแถวกับคอลัมน์ เปลี่ยนคอลัมน์ของข้อมูลที่คัดลอกเป็นแถว และเปลี่ยนแถวเป็นคอลัมน์ เช่น ถ้าก๊อปปี้ช่วงเซลล์ A1 ถึง A5 แล้วเลือก Transpose ที่เซลล์ D1 ผลที่คัดลอกมาจะถูกวางที่เซลล์ D1 ถึง H1 แทน
  • Paste Link วางการเชื่อมโยง ซึ่งข้อมูลในเซลล์ปลางทางจะกลายเป็นสูตรลิงก์ไปยังต้นทางที่ก๊อปปี้มา เช่น ถ้าสั่งคัดลอกที่เซลล์ A4 แล้วใช้คำสั่ง Paste Link ที่เซลล์ D10 ผลที่เซลล์ D10 จะเท่ากับ =$A$4
    และถ้าคัดลอกช่วงเซลล์ A1 ถึง B2 วางที่ D1 ผลคือในเซลล์ D1 เป็นสูตร =A1  และในเซลล์ E2 เป็น =B2 เป็นต้น

Clipboard เป็นส่วนที่แสดงการคัดลอกเก่าๆ ไว้ และสามารถนำกลับมาใช้ paste ที่เซลล์อื่นๆ ได้อีก การแสดง Clipboard ใน worksheet นั้นก็ขึ้นอยู่กับการที่เราจะตั้ง options ไว้อย่างไร

setting Excel's Clipboard option
How to Clear item in Clipboard

ซึ่งเราสามารถเลือกเก็บหรือลบข้อมูลต่างๆ ใน Clipboard ได้ด้วยการคลิกที่ลูกศรตามรูปแล้ว Delete (ดูภาพ-วิธี Clear ข้อมูลใน Clipboard)

Paste Special การวางแบบพิเศษ

เป็นการวางคำสั่งคัดลอกที่มีความยืดหยุ่นมากขึ้น โดยแบ่งเป็นเรื่องๆ

Paste การวางข้อมูลที่คัดลอกมา

  • All ทั้งหมด วางเนื้อหาและการจัดรูปแบบของเซลล์ต้นทางที่คัดลอกทั้งหมด
  • Formulars สูตร วางเฉพาะสูตร
  • Values ค่า วางเฉพาะค่าของข้อมูลในเซลล์ที่คัดลอก
  • Formats รูปแบบ วางเฉพาะการจัดรูปแบบเซลล์ของข้อมูลที่คัดลอก เช่น สี รูปแบบตัวอักษร,เส้นขอบ ฯลฯ
  • Comments ข้อคิดเห็น วางเฉพาะข้อคิดเห็นที่แนบมากับเซลล์ที่คัดลอก
  • Validation การตรวจสอบความถูกต้อง วางกฎการตรวจสอบความถูกต้องของข้อมูลในเซลล์ที่คัดลอก ไปยังพื้นที่เซลลใหม่
  • All using Source theme วางชุดรูปแบบตามต้นฉบับ วางเนื้อหาเซลล์ทั้งหมด
  • All except borders ทั้งหมดยกเว้นเส้นขอบ วางเนื้อหาในเซลล์และการจัดรูปแบบทั้งหมด ในเซลล์ปลายทาง ยกเว้นเส้นขอบ
  • Column widths ความกว้างคอลัมน์ วางความกว้างของคอลัมน์หนึ่งคอลัมน์หรือช่วงของคอลัมน์หนึ่งช่วงลงในคอลัมน์หรือช่วงของคอลัมน์อื่น
  • Formulas and number formats สูตรและรูปแบบตัวเลข วางเฉพาะสูตรและตัวเลือกการจัดรูปแบบตัวเลขทั้งหมดจากเซลล์ที่คัดลอกเท่านั้น
  • Values and number formats ค่าและรูปแบบตัวเลข วางเฉพาะค่าและตัวเลือกการจัดรูปแบบตัวเลขทั้งหมดจากเซลล์ที่คัดลอกเท่านั้น
Paste Special of Excel
Operation เป็นคำสั่งเสริมให้ดำเนินการทางคณิตศาสตร์ระหว่างเซลล์ต้นทางกับเซลล์ปลายทาง
  • None ไม่มีการดำเนินการใดๆ
  • Add บวก ข้อมูลที่คัดลอกจะบวกกับข้อมูลในเซลล์ปลายทาง
  • Subtract ลบ ข้อมูลที่คัดลอกจะถูกลบจากข้อมูลในเซลล์ปลายทาง
  • Multiply คูณ ข้อมูลที่คัดลอกจะถูกคูณด้วยกับข้อมูลในเซลล์ปลายทาง
  • Divide หาร ข้อมูลที่คัดลอกจะถูกหารด้วยข้อมูลในเซลล์ปลายทาง
  • Skip blanks ข้ามเซลล์ที่ว่าง เช่น ถ้าก๊อปปี้ช่วงเซลล์ต้นทางที่มีบางเซลล์เป็นค่าว่าง เมื่อใช้ Skip blanks เซลล์ว่างนั้นจะไม่ทับข้อมูลเดิมของเซลล์ปลายทาง
  • Transpose สลับเปลี่ยนแถวกับคอลัมน์ เปลี่ยนคอลัมน์ของข้อมูลที่คัดลอกเป็นแถว และเปลี่ยนแถวเป็นคอลัมน์
  • Paste Link วางการเชื่อมโยง เชื่อมโยงข้อมูลเซลล์ปลายทางไปยังข้อมูลต้นทางที่คัดลอก

การเรียกใช้ Paste Special นอกจากผ่านเมนู Home> Clipboard แล้วยังใช้ Secondary menu ได้อีก(คลิกเม้าส์ขวา)

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