การทำความสะอาดข้อมูลด้วยฟังก์ชันข้อความ

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

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

• การค้นหารูปแบบข้อความที่ต้องการด้วยฟังก์ชันข้อความ
• การแยกค่าข้อมูลจากข้อความ
• การจัดรูปแบบข้อมูลด้วยฟังก์ชันข้อความ
• การดำเนินการกับข้อมูลด้วยฟังก์ชันข้อความ

การลบอักขระที่ไม่ต้องการออกจากข้อความ

เมื่อคุณนำเข้าข้อมูลจากแอปพลิเคชันอื่นข้อมูลอาจมีอักขระที่พิมพ์ไม่ได้และ / หรือช่องว่างส่วนเกิน ช่องว่างส่วนเกินสามารถ -

• ช่องว่างชั้นนำและ / หรือ
• ช่องว่างพิเศษระหว่างคำ

หากคุณจัดเรียงหรือวิเคราะห์ข้อมูลดังกล่าวคุณจะได้ผลลัพธ์ที่ผิดพลาด

ลองพิจารณาตัวอย่างต่อไปนี้ -

นี่คือข้อมูลดิบที่คุณได้รับจากข้อมูลผลิตภัณฑ์ที่มีรหัสผลิตภัณฑ์คำอธิบายผลิตภัณฑ์และราคา อักขระ“ |” แยกเขตข้อมูลในแต่ละแถว

เมื่อคุณนำเข้าข้อมูลนี้ลงในแผ่นงาน Excel จะมีลักษณะดังนี้ -

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

คุณต้องลบอักขระที่พิมพ์ไม่ได้และช่องว่างส่วนเกินที่อาจมีอยู่ในข้อมูล คุณสามารถใช้ฟังก์ชัน CLEAN และฟังก์ชัน TRIM เพื่อจุดประสงค์นี้

ส.	ฟังก์ชั่นและคำอธิบาย
1.	CLEAN ลบอักขระที่พิมพ์ไม่ได้ทั้งหมดออกจากข้อความ
2.	TRIM ลบช่องว่างจากข้อความ

• เลือกเซลล์ C3 - C11
• พิมพ์ = TRIM (CLEAN (B3)) แล้วกด CTRL + Enter

สูตรจะเต็มไปในเซลล์ C3 - C11

ผลลัพธ์จะเป็นดังภาพด้านล่าง -

การค้นหารูปแบบข้อความที่ต้องการด้วยฟังก์ชันข้อความ

ในการจัดโครงสร้างข้อมูลของคุณคุณอาจต้องทำการจับคู่รูปแบบข้อความบางอย่างตามที่คุณสามารถดึงค่าข้อมูลออกมาได้ ฟังก์ชันข้อความบางส่วนที่มีประโยชน์สำหรับวัตถุประสงค์นี้ ได้แก่ -

ส.	ฟังก์ชั่นและคำอธิบาย
1.	EXACT ตรวจสอบว่าค่าข้อความสองค่าเหมือนกันหรือไม่
2.	FIND ค้นหาค่าข้อความหนึ่งค่าภายในอีกค่าหนึ่ง (พิจารณาตัวพิมพ์เล็กและใหญ่)
3.	SEARCH ค้นหาค่าข้อความหนึ่งภายในค่าอื่น (ไม่คำนึงถึงตัวพิมพ์เล็กและใหญ่)

การแยกค่าข้อมูลออกจากข้อความ

คุณต้องดึงข้อมูลที่ต้องการออกจากข้อความเพื่อให้มีโครงสร้างเหมือนกัน ในตัวอย่างข้างต้นกล่าวว่าคุณต้องวางข้อมูลในสามคอลัมน์ - ProductID, Product_Description และ Price

คุณสามารถดึงข้อมูลด้วยวิธีใดวิธีหนึ่งดังต่อไปนี้ -

• การแยกค่าข้อมูลด้วยตัวช่วยสร้างการแปลงข้อความเป็นคอลัมน์
• การแยกค่าข้อมูลด้วยฟังก์ชันข้อความ
• การแยกค่าข้อมูลด้วย Flash Fill

การแยกค่าข้อมูลด้วยตัวช่วยสร้างการแปลงข้อความเป็นคอลัมน์

คุณสามารถใช้ไฟล์ Convert Text to Columns Wizard เพื่อแยกค่าข้อมูลลงในคอลัมน์ Excel หากฟิลด์ของคุณ -

• คั่นด้วยอักขระหรือ
• จัดเรียงในคอลัมน์โดยมีช่องว่างระหว่างแต่ละฟิลด์

ในตัวอย่างข้างต้นช่องจะถูกคั่นด้วยอักขระ“ |” ดังนั้นคุณสามารถใช้ไฟล์Convert Text to Columns ตัวช่วย

• เลือกข้อมูล

• คัดลอกและวางค่าในที่เดียวกัน มิฉะนั้น,Convert Text to Columns ใช้ฟังก์ชันแทนที่จะใช้ข้อมูลเป็นอินพุต

• เลือกข้อมูล

• คลิกที่ Text to Columns ใน Data Tools กลุ่มภายใต้ Data แท็บบน Ribbon

Step 1 - แปลงข้อความเป็นตัวช่วยสร้างคอลัมน์ - ขั้นตอนที่ 1 จาก 3 จะปรากฏขึ้น

• เลือกตัวคั่น
• คลิกถัดไป

Step 2 - แปลงข้อความเป็นตัวช่วยสร้างคอลัมน์ - ขั้นตอนที่ 2 จาก 3 จะปรากฏขึ้น

• ภายใต้ Delimitersเลือก Other.

• ในช่องถัดจาก Otherพิมพ์อักขระ |

• คลิก Next.

Step 3 - แปลงข้อความเป็นตัวช่วยสร้างคอลัมน์ - ขั้นตอนที่ 3 จาก 3 จะปรากฏขึ้น

ในหน้าจอนี้คุณสามารถเลือกแต่ละคอลัมน์ของข้อมูลของคุณในวิซาร์ดและกำหนดรูปแบบสำหรับคอลัมน์นั้นได้

• สำหรับ Destinationเลือกเซลล์ D3

• คุณสามารถคลิก Advancedและตั้งค่า Decimal Separator และ Thousands Separator ใน Advanced Text Import Settings กล่องโต้ตอบที่ปรากฏขึ้น

• คลิก Finish.

ข้อมูลของคุณซึ่งถูกแปลงเป็นคอลัมน์จะปรากฏในสามคอลัมน์ - D, E และ F

• ตั้งชื่อส่วนหัวคอลัมน์เป็น ProductID, Product_Description และ Price

การแยกค่าข้อมูลด้วยฟังก์ชันข้อความ

สมมติว่าฟิลด์ในข้อมูลของคุณไม่ได้ถูกคั่นด้วยอักขระหรือไม่ถูกจัดแนวในคอลัมน์ที่มีช่องว่างระหว่างแต่ละฟิลด์คุณสามารถใช้ฟังก์ชันข้อความเพื่อแยกค่าข้อมูลได้ แม้ในกรณีที่ช่องถูกคั่นคุณยังสามารถใช้ฟังก์ชันข้อความเพื่อดึงข้อมูลได้

ฟังก์ชันข้อความบางส่วนที่มีประโยชน์สำหรับวัตถุประสงค์นี้ ได้แก่ -

ส.	ฟังก์ชั่นและคำอธิบาย
1.	LEFT ส่งคืนอักขระซ้ายสุดจากค่าข้อความ
2.	RIGHT ส่งคืนอักขระขวาสุดจากค่าข้อความ
3.	MID ส่งคืนจำนวนอักขระเฉพาะจากสตริงข้อความที่เริ่มต้นที่ตำแหน่งที่คุณระบุ
4.	LEN ส่งคืนจำนวนอักขระในสตริงข้อความ

คุณยังสามารถรวมฟังก์ชันข้อความเหล่านี้อย่างน้อยสองฟังก์ชันตามข้อมูลที่คุณมีอยู่ในมือเพื่อแยกค่าข้อมูลที่ต้องการ ตัวอย่างเช่นการใช้ฟังก์ชัน LEFT, RIGHT และ VALUE ร่วมกันหรือใช้ฟังก์ชัน FIND, LEFT, LEN และ MID ร่วมกัน

ในตัวอย่างข้างต้น

• อักขระทั้งหมดเหลือตัวแรก | ตั้งชื่อ ProductID

• อักขระทั้งหมดไปจนถึงวินาที | ให้ชื่อราคา.

• อักขระทั้งหมดที่อยู่ระหว่างตัวแรก | และสอง | ตั้งชื่อ Product_Description

• แต่ละ | มีช่องว่างก่อนและหลัง

จากการสังเกตข้อมูลนี้คุณสามารถแยกค่าข้อมูลโดยทำตามขั้นตอนต่อไปนี้ -

• ค้นหาตำแหน่งของอันดับแรก | -First | Position

  • คุณสามารถใช้ฟังก์ชัน FIND

• ค้นหาตำแหน่งที่สอง | -Second | Position

  • คุณสามารถใช้ฟังก์ชัน FIND ได้อีกครั้ง

• เริ่มต้นที่ (First | Position - 2) อักขระของข้อความให้รหัสผลิตภัณฑ์

  • คุณสามารถใช้ฟังก์ชัน LEFT

• (First | Position + 2) ถึง (Second | Position - 2) อักขระของข้อความให้ Product_Description

  • คุณสามารถใช้ฟังก์ชัน MID

• (Second | Position + 2) ถึงอักขระสิ้นสุดของข้อความให้ราคา

  • คุณสามารถใช้ฟังก์ชัน RIGHT

ผลลัพธ์จะเป็นดังภาพด้านล่าง -

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

การแยกค่าข้อมูลด้วย Flash Fill

ใช้ Excel Flash Fillเป็นอีกวิธีหนึ่งในการดึงค่าข้อมูลจากข้อความ อย่างไรก็ตามจะใช้ได้เฉพาะเมื่อ Excel สามารถค้นหารูปแบบในข้อมูลได้

Step 1 - สร้างคอลัมน์สามคอลัมน์สำหรับ ProductID, Product_Description และ Price ถัดจากข้อมูล

Step 2 - คัดลอกและวางค่าสำหรับ C3, D3 และ E3 จาก B3

Step 3 - เลือกเซลล์ C3 แล้วคลิก Flash Fill ใน Data Tools กลุ่มบน Dataแท็บ ค่าทั้งหมดสำหรับ ProductID จะถูกเติม

Step 4- ทำซ้ำขั้นตอนที่ระบุข้างต้นสำหรับ Product_Description และ Price กรอกข้อมูลแล้ว

การจัดรูปแบบข้อมูลด้วยฟังก์ชันข้อความ

Excel มีฟังก์ชันข้อความในตัวหลายอย่างที่คุณสามารถใช้สำหรับการจัดรูปแบบข้อมูลที่มีข้อความ ซึ่ง ได้แก่ -

Functions that format the Text as per your need -

ส.	ฟังก์ชั่นและคำอธิบาย
1.	LOWER แปลงข้อความเป็นตัวพิมพ์เล็ก

ส.	ฟังก์ชั่นและคำอธิบาย
1.	UPPER แปลงข้อความเป็นตัวพิมพ์ใหญ่
2.	PROPER อักษรตัวแรกเป็นตัวพิมพ์ใหญ่ในแต่ละคำของค่าข้อความ

Functions that convert and/or format the Numbers as Text -

ส.	ฟังก์ชั่นและคำอธิบาย
1.	DOLLAR แปลงตัวเลขเป็นข้อความโดยใช้รูปแบบสกุลเงิน $ (ดอลลาร์)
2.	FIXED จัดรูปแบบตัวเลขเป็นข้อความโดยมีทศนิยมจำนวนคงที่
3.	TEXT จัดรูปแบบตัวเลขและแปลงเป็นข้อความ

Functions that convert the Text to Numbers -

ส.	ฟังก์ชั่นและคำอธิบาย
1.	VALUE แปลงอาร์กิวเมนต์ข้อความเป็นตัวเลข

Executing Data Operations with the Text Functions

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

ฟังก์ชั่นข้อความต่อไปนี้ช่วยคุณในการดำเนินการข้อความกับข้อมูลของคุณที่มีข้อความ -

ส.	ฟังก์ชั่นและคำอธิบาย
1.	REPLACE แทนที่อักขระภายในข้อความ
2.	SUBSTITUTE แทนที่ข้อความใหม่สำหรับข้อความเก่าในสตริงข้อความ
3.	CONCATENATE รวมรายการข้อความหลายรายการเป็นรายการข้อความเดียว
4.	CONCAT รวมข้อความจากหลายช่วงและ / หรือสตริง แต่ไม่มีอาร์กิวเมนต์ตัวคั่นหรือ IgnoreEmpty
5.	TEXTJOIN รวมข้อความจากหลายช่วงและ / หรือสตริงและรวมตัวคั่นที่คุณระบุระหว่างค่าข้อความแต่ละค่าที่จะรวมกัน ถ้าตัวคั่นเป็นสตริงข้อความว่างฟังก์ชันนี้จะเชื่อมต่อช่วงได้อย่างมีประสิทธิภาพ
6.	REPT ทำซ้ำข้อความตามจำนวนครั้งที่กำหนด