19 เทคนิคการล้างข้อมูลที่เป็นประโยชน์ใน Excel

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

คุณสามารถดาวน์โหลดเวิร์กบุคที่ใช้สำหรับการสาธิตได้จากลิงค์ด้านล่าง

19 เทคนิคการล้างข้อมูลใน Excel ที่จะมีประโยชน์

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

1. ลบแถวที่ซ้ำกัน

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

มาจำลองคอลัมน์ข้างๆ แล้วลบรายการที่ซ้ำกันเพื่อเปรียบเทียบในภายหลัง

ทำตามขั้นตอนเหล่านี้เพื่อลบรายการที่ซ้ำกัน

ขั้นตอน:

• ขั้นแรก เลือกคอลัมน์ที่คุณต้องการลบรายการที่ซ้ำกันออก
• ไปที่ ข้อมูล บนริบบิ้นของคุณ
• จากนั้นเลือก ลบรายการที่ซ้ำกัน จาก เครื่องมือข้อมูล กลุ่มที่นั่น

• หลังจากนั้น เลือก ดำเนินการต่อด้วยการเลือกปัจจุบัน ในช่องป๊อปอัปแล้วคลิก ลบรายการที่ซ้ำกัน .

• ถัดไป คลิกที่ ตกลง .

การดำเนินการนี้จะลบรายการที่ซ้ำกันทั้งหมดออกจากการเลือก

2. เน้นค่าที่ซ้ำกัน

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

การใช้การจัดรูปแบบตามเงื่อนไข

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

ทำตามขั้นตอนเหล่านี้เพื่อดูว่าเราจะทำได้อย่างไร

ขั้นตอน:

• อันดับแรก เลือกเซลล์ที่เราต้องการทำซ้ำ ในกรณีนี้คือ D5:D18
• ประการที่สอง ไปที่ หน้าแรก> เลือก การจัดรูปแบบตามเงื่อนไข> ไปที่ ไฮไลต์กฎของเซลล์> เลือก ค่าที่ซ้ำกัน .

• ในที่สุด ค่าที่ซ้ำกัน หน้าต่างจะปรากฏขึ้น
• ประการที่สาม เลือก ซ้ำ .
• ประการที่สี่ เลือกตัวเลือกสีใดก็ได้ใน ค่าด้วย ในกรณีนี้ เราได้เลือก เติมสีแดงอ่อนพร้อมข้อความสีแดงเข้ม .

• ดังนั้น เราจะเห็นว่าค่าที่ซ้ำกันใน D คอลัมน์ถูกเน้น

ไฮไลต์รายการซ้ำรวมถึงการเกิดขึ้นครั้งแรก

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

ขั้นตอน:

• ประการแรก ไปที่กฎการจัดรูปแบบใหม่ หน้าต่างโดยไปที่ การจัดรูปแบบตามเงื่อนไข

• ประการที่สอง เลือก ใช้สูตรเพื่อกำหนดเซลล์ที่จะจัดรูปแบบ .
• ประการที่สาม เขียนสูตรต่อไปนี้ในกล่องสูตร

=COUNTIF($D$5:$E$16,$D5)>1

• ดังนั้น เราจะสังเกตว่า D คอลัมน์จะถูกเน้นสำหรับรายการที่ซ้ำกัน รวมถึงครั้งที่ 1 ด้วย

การไฮไลต์รายการซ้ำ ไม่รวมการเกิดขึ้นครั้งแรก

ตอนนี้เราจะพูดถึงเทคนิคที่มีประโยชน์ที่สุดอีกวิธีหนึ่งในการระบุค่าที่ซ้ำกัน ไม่เน้นพวกเขายกเว้นการเกิดขึ้นครั้งแรก สิ่งนี้มีประโยชน์หากเราต้องการสังเกตเฉพาะค่าที่เกิดซ้ำเท่านั้น แทนที่จะเป็นค่าที่ซ้ำกันทั้งหมด เช่นเดียวกับก่อนหน้านี้ เราจะใช้ฟังก์ชัน COUNTIF ที่นี่.

ทำตามขั้นตอนเหล่านี้เพื่อดูว่าเราจะทำได้อย่างไร

ขั้นตอน:

• ในการดำเนินการนี้ ก่อนอื่น ให้ไปที่กฎการจัดรูปแบบใหม่ ผ่าน การจัดรูปแบบตามเงื่อนไข อย่างที่เราได้กล่าวไปแล้วก่อนหน้านี้

• ประการที่สอง เลือก ใช้สูตรเพื่อกำหนดเซลล์ที่จะจัดรูปแบบ เหมือนเดิม
• ประการที่สาม เขียนสูตรต่อไปนี้ในกล่องสูตร

=COUNTIF($D$5:$D5,$D5)>1

• หลังจากคลิกที่ ตกลง โดยจะไฮไลต์รายการที่ซ้ำกันทั้งหมด ยกเว้นรายการแรก

3. แบ่งข้อความออกเป็นหลายเซลล์

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

มี ข้อความเป็นคอลัมน์ . ที่เป็นประโยชน์ คุณลักษณะที่เราสามารถใช้ได้ซึ่งจะช่วยให้เราแยกข้อความเหล่านี้ได้

ขั้นตอน:

• ขั้นแรก เลือกช่วงที่คุณต้องการแยกข้อความ
• จากนั้นไปที่ ข้อมูล บนริบบิ้นของคุณ
• หลังจากนั้น จาก เครื่องมือข้อมูล กลุ่ม เลือก ข้อความเป็นคอลัมน์ .

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

• หลังจากคลิกที่ ถัดไป , กล่องอื่นจะปรากฏขึ้น
• เลือก เว้นวรรค ที่นี่ภายใต้ ตัวคั่น ตัวเลือกเนื่องจากข้อความถูกแบ่งโดยเว้นวรรค
• จากนั้นคลิกที่ ถัดไป .

• หลังจากนั้น ให้คลิกที่ เสร็จสิ้น .

• สุดท้าย ข้อความจะถูกแบ่ง

• ตอนนี้ มาทำการปรับเปลี่ยนบางอย่างเพื่อให้ดูเรียบร้อยยิ่งขึ้น

นี่คือวิธีที่เราสามารถแบ่งข้อความออกเป็นเซลล์ต่างๆ พร้อมกับเทคนิคการล้างข้อมูลอื่นๆ ใน Excel

4. การแปลงข้อมูลด้วยสูตร

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

บน :สูตรนี้แปลงข้อความเป็นตัวพิมพ์ใหญ่ทั้งหมด

ล่าง :สูตรนี้แปลงข้อความเป็นตัวพิมพ์เล็กทั้งหมด

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

เราจะใช้ชุดข้อมูลต่อไปนี้สำหรับการนำเสนอทั้งหมด

เปลี่ยนเป็นตัวพิมพ์ใหญ่

ดังที่ได้กล่าวไว้ก่อนหน้านี้ เราจะใช้ ฟังก์ชัน UPPER เพื่อจุดประสงค์นี้. ทำตามขั้นตอนเหล่านี้เพื่อดูว่าเราจะทำได้อย่างไร

ขั้นตอน:

• ขั้นแรก เลือกเซลล์ C5 .
• จากนั้นเขียนสูตรต่อไปนี้ลงไป

=UPPER(B5)

• หลังจากนั้น กด Enter .

• เลือกเซลล์อีกครั้งแล้วคลิกและลากไอคอนเติมแฮนเดิลเพื่อเติมสูตรในเซลล์ที่เหลือ

เปลี่ยนเป็นตัวพิมพ์เล็ก

ต่อไป เราจะสาธิตวิธีการแปลงข้อความทั้งหมดเป็นตัวพิมพ์เล็ก

เราจะทำตามขั้นตอนต่อไปนี้สำหรับสิ่งนี้

ขั้นตอน:

• ขั้นแรก ให้เลือกเซลล์ D5 .
• จากนั้นเขียนสูตรต่อไปนี้ลงไป

=LOWER(B5)

• หลังจากนั้น กด Enter .

• เลือกเซลล์อีกครั้งแล้วคลิกและลากไอคอนเติมแฮนเดิลเพื่อเติมสูตรในเซลล์ที่เหลือ

เปลี่ยนเป็นกรณีที่เหมาะสม

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

ขั้นตอน:

• ขั้นแรก ให้เลือกเซลล์ E5 .
• จากนั้นเขียนสูตรต่อไปนี้ลงไป

=PROPER(B5)

• หลังจากนั้น กด Enter .

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

5. ลบช่องว่างพิเศษ

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

เราสามารถปฏิบัติตามเทคนิคการล้างข้อมูลทั้งสองนี้ใน Excel เพื่อลบช่องว่างเพิ่มเติม

เราจะใช้ทั้งคู่ในชุดข้อมูลต่อไปนี้

การใช้ฟังก์ชัน TRIM

เราใช้ ฟังก์ชัน TRIM เพื่อลบช่องว่างนำหน้าและต่อท้ายทั้งหมดออกจากข้อความ การตัดแต่ง ฟังก์ชันยังแทนที่ช่องว่างหลายช่องด้วยช่องว่างเดียว

ขั้นตอน:

• ขั้นแรก เพิ่มคอลัมน์ชื่อ ข้อมูลที่อัปเดต เพื่อแสดงผล
• จากนั้นคลิกที่เซลล์ D5 .
• พิมพ์ =TRIM แล้วเลือกเซลล์ C5 ในอาร์กิวเมนต์แรก ปิดวงเล็บ
• ดังนั้น สูตรจึงกลายเป็น:

=TRIM(C5)

• ตอนนี้ กด Enter .

• ตอนนี้ให้ลากไอคอน Fill Handel ไปยังเซลล์สุดท้าย

เราจะเห็นได้ว่าช่องว่างที่ไม่จำเป็นจะถูกลบออก มีเพียงช่องว่างเดียวเท่านั้นหลังจากแต่ละคำ

การใช้คุณลักษณะค้นหาและแทนที่:

ในส่วนนี้ เราจะพูดถึงวิธีใช้คำสั่ง Find &Replace เพื่อลบช่องว่างทั้งหมด

ขั้นตอน:

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

• เมื่อเราเลือก แทนที่ , เราจะได้รับกล่องโต้ตอบ
• พิมพ์ช่องว่างในช่อง ค้นหาอะไร  ฟิลด์.
• เก็บ แทนที่ด้วย กล่องเปล่า
• จากนั้น คลิก แทนที่ทั้งหมด .

• หลังจากคลิก แทนที่ทั้งหมด เราจะได้รับป๊อปอัปแสดงจำนวนการแทนที่
• ตอนนี้ คลิก ตกลง บนป๊อปอัป
• จากนั้นคลิก ปิด ของกล่องโต้ตอบ
• ในที่สุด เราก็ได้ผลลัพธ์

6. ลบอักขระแปลก ๆ

บ่อยครั้ง หลังจากนำเข้าข้อมูลลงในเวิร์กชีต Excel คุณจะพบว่ามีอักขระแปลก ๆ (บางครั้งไม่สามารถพิมพ์ได้) คุณสามารถใช้ฟังก์ชัน CLEAN เพื่อลบอักขระที่ไม่ได้พิมพ์ทั้งหมดออกจากข้อความ หากข้อมูลอยู่ในเซลล์ A2 คุณสามารถใช้ในการทำงานของคุณ:

=CLEAN(A2)

สะอาด ฟังก์ชันบางครั้งอาจพลาดอักขระ Unicode ที่ไม่ได้พิมพ์บางตัว มีการตั้งโปรแกรมให้ลบอักขระที่ไม่ได้พิมพ์ 32 ตัวแรกในโค้ด ASCII 7 บิต เรียกดูระบบวิธีใช้ Excel เพื่อดูข้อมูลเกี่ยวกับวิธีการเอาอักขระ Unicode ที่ไม่ได้พิมพ์ออก

7. แปลงค่า

สิ่งสำคัญอีกประการหนึ่งในเทคนิคการล้างข้อมูลคือการแปลงค่าหนึ่งในชุดข้อมูลเป็นอีกค่าหนึ่ง บางครั้ง คุณอาจต้องแปลงค่าจากระบบเมตริกหนึ่งไปเป็นระบบเมตริกอื่น ตัวอย่างเช่น คุณอาจนำเข้าไฟล์ที่มีค่าเป็นออนซ์ของเหลว (fl oz) และต้องแสดงเป็นมิลลิลิตร ฟังก์ชันการแปลงของ Excel สามารถดำเนินการประเภทนี้และการแปลงประเภทอื่น ๆ ได้อีกมากมาย

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

สำหรับการสาธิต ให้ดูที่ชุดข้อมูลและนำไปใช้กับชุดข้อมูลนี้ นี่คือชุดข้อมูล

ทำตามขั้นตอนเหล่านี้เพื่อเปลี่ยนอุณหภูมิเป็นฟาเรนไฮต์

ขั้นตอน:

• ขั้นแรก เลือกเซลล์ผลลัพธ์ D5
• จากนั้นพิมพ์สูตรต่อไปนี้

=CONVERT(C5,"C","F")

• หลังจากนั้น กด Enter .

• ตอนนี้ คลิกและลากไอคอนเติมแฮนเดิลที่ท้ายคอลัมน์เพื่อแปลงค่าทั้งหมด

นี่คือวิธีที่เราสามารถแปลงค่าเป็นหน่วยอื่นได้ สำหรับการแปลงหน่วยอื่นๆ ดูบทความนี้ .

8. เน้นข้อผิดพลาด

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

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

มาดูสเปรดชีตต่อไปนี้กัน

ทำตามขั้นตอนเหล่านี้เพื่อเน้นข้อผิดพลาดที่นี่

ขั้นตอน:

• เหนือสิ่งอื่นใด เลือกชุดข้อมูลทั้งหมดแล้วคลิก กฎใหม่ จาก การจัดรูปแบบตามเงื่อนไข  ตัวเลือก

• จากนั้น ในประเภทกฎ ให้เลือก จัดรูปแบบเฉพาะเซลล์ที่มี จากนั้นเลือก ข้อผิดพลาด จากรายการดรอปดาวน์ด้านล่าง

• จากนั้นคลิกที่ ตกลง .

นี่คือผลลัพธ์สุดท้ายของชุดข้อมูล อย่างที่คุณเห็น Excel เน้นค่าความผิดพลาด

นี่คือวิธีที่คุณสามารถเน้นข้อผิดพลาดเป็นหนึ่งในเทคนิคการล้างข้อมูลใน Excel

9. เข้าร่วมคอลัมน์

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

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

ขั้นตอน:

• ขั้นแรก ให้เลือกเซลล์ E5 .
• จากนั้นเขียนสูตรต่อไปนี้

=B5&" "&D5&" "&C5

• หลังจากนั้น กด Enter .

• ตอนนี้ คลิกและลากไอคอนเติมแฮนเดิลที่ส่วนท้ายของคอลัมน์เพื่อจำลองสูตรสำหรับเซลล์ที่เหลือ

นี่คือวิธีที่เราสามารถรวมสองคอลัมน์ขึ้นไปใน Excel ได้

10. จัดเรียงคอลัมน์ใหม่

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

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

ขั้นตอน:

• ขั้นแรก เลือกคอลัมน์ที่คุณต้องการย้ายโดยคลิกที่ส่วนหัวของคอลัมน์
• จากนั้นตัดคอลัมน์โดยใช้เมนูบริบทหรือกด Ctrl+X . คุณจะเห็นเส้นประที่ขอบของส่วนที่เลือก ณ จุดนี้

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

• นี่จะแทรกคอลัมน์ก่อนหน้าที่นี่

นี่คือวิธีที่เราสามารถจัดเรียงคอลัมน์ใหม่โดยเป็นส่วนหนึ่งของเทคนิคการล้างข้อมูลใน Excel

11. สุ่มแถว

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

เราจะดำเนินการดังกล่าวสำหรับชุดข้อมูลต่อไปนี้

ทำตามขั้นตอนเหล่านี้เพื่อสุ่มแถวและเปลี่ยนลำดับใน Excel

ขั้นตอน:

• ก่อนอื่น เราต้องสร้างคอลัมน์ใหม่หลังคอลัมน์ B . สำหรับสิ่งนี้ ให้คลิกตัวอักษรส่วนหัวของคอลัมน์ C และ C . ทั้งหมด คอลัมน์จะถูกเลือก
• จากนั้น คลิกขวาและเลือกคำสั่ง Insert และคอลัมน์ใหม่จะถูกสร้างขึ้น

• ตอนนี้ กำหนดชื่อให้กับคอลัมน์ที่สร้างขึ้นใหม่ (เช่น Random Number) เลือกคอลัมน์แรก (เช่น C5) และพิมพ์สูตรต่อไปนี้ลงในเซลล์

=RAND()

• หลังจากนั้น กด ENTER และตัวเลขสุ่มจะแสดงในเซลล์ C5 . จำนวนน้อยกว่า 1

• จากนั้น ให้เลือกเซลล์อีกครั้งแล้วคลิกและลากไอคอนเติมแฮนเดิลที่ท้ายคอลัมน์เพื่อจำลองสูตรสำหรับเซลล์ที่เหลือ

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

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

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

12. แยกชื่อไฟล์จาก URL

บางครั้งคุณอาจมีรายการ URL และจำเป็นต้องแยกเฉพาะชื่อไฟล์ ด้วยวิธีต่อไปนี้ คุณสามารถแยกชื่อไฟล์ออกจาก URL สมมติว่าเซลล์ B5 มี URL นี้:https://example.com/assets/images/horse.jpg .

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

ทำตามขั้นตอนเหล่านี้เพื่อแยกชื่อไฟล์ (horse.jpg) ออกจากลิงก์

ขั้นตอน:

• ขั้นแรก เลือกเซลล์ที่คุณต้องการใส่ชื่อไฟล์ ในกรณีนี้คือเซลล์ C5 .
• จากนั้นเขียนสูตรต่อไปนี้ลงไป

=RIGHT(B5,LEN(B5)-FIND("*",SUBSTITUTE(B5,"/","*",LEN(B5)-LEN(SUBSTITUTE(B5,"/","")))))

• หลังจากนั้น กด Enter .

🔎 รายละเอียดของสูตร

👉 SUBSTITUTE(B5,”/”,””) ลบ “/” . ทั้งหมด จาก URL มันส่งกลับ http:www.example.comassetsimageshorse.jpg

👉 LEN(SUBSTITUTE(B5,”/”,””)) คืนค่าความยาวของสตริงก่อนหน้า

👉 เลน(B5) คืนค่าความยาวของสตริงในเซลล์ B4 ซึ่งก็คือ 46 ที่นี่

👉 LEN(B5)-LEN(SUBSTITUTE(B5,”/”,””))) ระบุความแตกต่างระหว่างค่าความยาวสองค่า ซึ่งในทางกลับกันแสดงว่าจำนวน “/” ถูกลบออก

👉 SUBSTITUTE(B5,”/”,”*”,LEN(B5)-LEN(SUBSTITUTE(B5,”/”,””))) แทนที่เครื่องหมายทับทั้งหมด (/) คือเซลล์ B5 ด้วยเครื่องหมายดาว (*) พร้อมหมายเลขอินสแตนซ์จากผลลัพธ์ของฟังก์ชันก่อนหน้า

👉 FIND(“*”,SUBSTITUTE(B5,”/”,”*”,LEN(B5)-LEN(SUBSTITUTE(B5,”/””)))) พบว่าตำแหน่ง “*” อยู่ในสตริงจนถึงปัจจุบัน

👉 LEN(B5)-FIND(“*”,SUBSTITUTE(B5,”/”,”*”,LEN(B5)-LEN(SUBSTITUTE(B5,”/””)))) > คือความแตกต่างระหว่างความยาวสตริงเดิมและตำแหน่งก่อนหน้า

👉 สุดท้าย RIGHT(B5,LEN(B5)-FIND(“*”,SUBSTITUTE(B5,”/”,”*”,LEN(B5)-LEN(SUBSTITUTE(B5,”/”,””) )))) แยกจำนวนอักขระนั้นออกจากด้านขวาของเซลล์

13. จับคู่ข้อความในรายการ

ลองดูที่รูปต่อไปนี้ เราจะไปหาคนที่ลาออกจากด้านซ้ายของตัวอย่างของเรา มีรายชื่ออยู่ทางด้านขวาของหมายเลขที่ลาออก

ด้านบนแสดงตัวอย่างง่ายๆ ข้อมูลอยู่ในช่วง B5:D133 . เป้าหมายคือการระบุแถวในเขตข้อมูลที่ปรากฏในรายชื่อสมาชิกลาออกในคอลัมน์ G . คุณสามารถลบแถวที่ไม่จำเป็นเหล่านี้ได้ในภายหลัง เราต้องการการรวมกันของ IF และ COUNTIF ใช้งานได้ตามวัตถุประสงค์

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

ขั้นตอน:

• ขั้นแรก ให้เลือกเซลล์ E5 .
• จากนั้นเขียนสูตรต่อไปนี้

=IF(COUNTIF($G$5:$G$25,C5),"Resigned","")

• หลังจากนั้น กด Enter .

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

นี่คือวิธีที่เราสามารถจับคู่ข้อความเป็นส่วนหนึ่งของเทคนิคการวิเคราะห์ข้อมูลใน Excel

14. เปลี่ยนข้อมูลแนวตั้งเป็นแนวนอน

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

เราจะใช้ชุดข้อมูลต่อไปนี้

การใช้ตัวเลือกการวางแบบพิเศษ

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

• อันดับแรก เลือกช่วงที่คุณต้องการเปลี่ยนเป็นแถวแนวนอน ที่นี่เราได้เลือกช่วง B4:C10 .
• ประการที่สอง กด Ctrl+C เพื่อคัดลอกช่วง
• ประการที่สาม เลือกเซลล์ที่คุณต้องการวางช่วงในแนวนอน ในกรณีของเรา เราได้เลือก Cell B12 .

This will change vertical columns into horizontal rows in the Excel spreadsheet.

Using TRANSPOSE Function:

We can use some Excel Functions to convert a vertical column to a horizontal row. Here, we will use the TRANSPOSE function for that purpose. The main advantage of using functions is that you will get dynamic updates in the horizontal rows if you change anything in the main dataset. But, the horizontal rows will not have the same formatting as the vertical columns. You need to add formatting after applying the method.

Let’s follow the steps below to learn more.

ขั้นตอน:

• First, select the cell where you want to place the formula.
• Then write down the following formula in it.

=TRANSPOSE(B4:C10)

• Now press Enter .

This is how we can change vertical data to horizontal using the TRANSPOSE ฟังก์ชัน

15. Fill Blank Cells

One of the most important data cleaning techniques in any form of data analysis is filling blank values, either with average in numeric cases or with values with the previous or later rows.

Sometimes we keep some blank cells to understand that they will be the first filled ones, have a look at our dataset to understand it. But sometimes it creates problems like while we are filtering or sorting data. So it’s better to fill them and there are easy ways to o it.

This is our dataset.

We can fill them up using these two simple methods.

Using Fill Command

If your data is small, you can enter the missing cell values from above manually or by using this command. Follow these steps to see how you can do that.

• Select Cell B6 .
• Then go to the Home
• From the Editing group, click on Fill and then Down .

• The dataset will look like this now.

• Now repeat the process for all the blank cells and fill out all the cells.

This is one way to fill up blank cells in a dataset.

Using Keyboard Shortcut:

We can achieve the same result using a keyboard shortcut. The shortcut is Ctrl+D . You just have to select the blank cell and press the shortcut. It will fill the cell with the value from the previous cell in the same column. Follow these steps for a more detailed guide.

• First, select cell B6 .
• Then press Ctrl+D on your keyboard.

• Now selecting cell B9 and pressing Ctrl+D will yield the following result.

• Now fill out all the blank values by selecting them and pressing the shortcut.

16. Check Spelling

When you use Microsoft Word Document or another word processor, you use its spell checker feature most of the time. Spelling mistakes in a text document are embarrassing. In Excel, spelling mistakes can also occur serious problems. Say, for example, if you tabulate data by month, a misspelled month name will make that a year has 13 months.

Here we have made a dataset for the demonstration.

We are going to correct the range C5:C7 . Follow these steps to see how we can do that.

ขั้นตอน:

• First, select the range C5:C7 .
• Then go to the Review tab and select Spelling from the Proofing You can also skip this step and press F7 on your keyboard.

• Now select the correct suggestion and click on Change to change the value of a particular cell. The errors come in the selection order here.

• This will change the first error in the dataset.

• Now repeat this process for all the errors and you will have correct spellings this way.

17. Replace or Remove Text

Removing and replacing data, especially outliers is another important task in any data cleaning process. So as a part of our data cleaning techniques compilation, we are going to demonstrate replacing and removing text in Excel. We can do that using three methods.

For all of the methods, we are using the following dataset.

Using Find &Replace Feature:

Excel has a direct feature that can help find, replace and remove certain values from another. Follow these steps to see how we can use this feature to remove or replace in Excel.

• First, select the range which you want to modify. In this case, it is C5:C13 .
• Then go to the Home
• After that, select Find &Replace from the Editing group section.
• Next, select Replace from the drop-down.

• Now, go to the Replace tab in the pop-up box.
• Then insert the value you want to replace in the Find what field and the value you want to replace it with in the Replace with

• Finally, click Replace All to replace all of them in the dataset.

This will be the final product after removing all of the hyphens from the dataset.

Utilizing Flash Fill:

Another feature we can use to replace or remove values while using data cleaning techniques is to utilize the flash fill feature. For the same dataset, follow these steps to see how that works.

• First, write down the final product after removing or replacing the desired value in the first cell. We are replacing the second hyphen in the string with a colon.

• Now start writing the second one manually. As soon as you start writing Excel will recognize the pattern and start suggesting output for the rest.

Note

If the suggestion doesn’t occur in the second entry, try it out manually. It sometimes suggests the output at the third or fourth entry if the pattern isn’t that obvious to Excel.

• Now press Enter .

Thus, you can replace or remove values using this handy feature of Excel while data cleaning.

Using SUBSTITUTE Function:

Another way we can replace a value, or remove them in Excel is to use the SUBSTITUTE function . This function takes three values- a string, the value to replace, and the value to replace it with. Follow these steps to see how we can use this function.

• First, select cell C5 .
• Then write down the following formula in it.

=SUBSTITUTE(B5,"-",":",2)

• After that, press Enter .

• Finally, select the cell again and click and drag the fill handle icon to the end of the column to replicate the formula for the rest of the cells.

18. Add Text to Cells

Adding text is not only common as one of the data cleaning techniques but also in general Excel operations. We can add text to cells in various ways. For demonstrations of those, we will use the following dataset.

Now you can use one of the following methods to add text to cells.

Using Ampersand (&) Symbol:

The ampersand (&) symbol in a formula works as the string connector. It connects the previous and the following string it is in between. Follow these steps to see how we can easily add text to cells using this.

• First of all, select cell C5 .
• Secondly, put down the formula below.

=B5&".mp3"

• Next, press Enter .

• Finally, click and drag the fill handle icon to the end of the column to replicate the formula for the rest of the cells.

Applying Flash Fill Feature:

Follow these steps to add text using the flash fill feature easily in Excel.

• First, write down the first intended value manually in cell C5 .

• Then start filling out the second one in the dataset. Once you start filling, Excel will suggest the pattern.

• As soon as the suggestion appears, press Enter .

This is another way to add text in Excel.

Using CONCATENATE Function:

The CONCATENATE function in Excel, as the name suggests, concatenates two or more strings together. It takes those strings as its arguments. It works the same way as the ampersand sign method described earlier. Follow these steps to see how we can use this function.

• First, select cell C5 .
• Then write down the following formula in it.

=CONCATENATE(B5,".mp3")

• After that, press Enter .

• Finally, select the cell again and click and drag the fill handle icon to the end of the column to replicate the formula for the rest of the cells.

19. Fix Trailing Minus Sign

Sometimes while taking data from other sources, the negative values do have a trailing minus sign. Take a look at the following figure.

Excel doesn’t consider the values with trailing negative signs as numbers. Instead, it treats them as texts. This may cause problems, not only in the data cleaning techniques but also in general operations like using formulas in Excel. So we need to address the problem and convert them into negative numbers.

Follow these simple steps to do that.

ขั้นตอน:

• First, select the range you want to convert. We have copied it to compare it with the previous one.

• Then go to the Data tab on your ribbon.
• Then select Text to Columns from the Data Tools group section.

• Finally, directly click on Finish in the wizard.

This will automatically change all the negative values with trailing minus signs to Excel’s negative sign format.

This procedure works because there is a default setting in the Advanced Text Import Settings กล่องโต้ตอบ

บทสรุป

So these were some of the most useful data cleaning techniques in Excel used for data analysis. Hopefully, you can use these techniques while using data cleaning in Excel with ease. I hope you found this guide helpful and informative. If you have any questions or suggestions, let us know in the comments below.

For more guides like this, visit Exceldemy.com .