ทำความสะอาดข้อความในสเปรดชีตด้วยมาโคร ONLYOFFICE ที่สามารถนำกลับมาใช้ใหม่ได้

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

มาโคร ONLYOFFICE ทำงานภายในตัวแก้ไขและใช้ Office JavaScript API เอกสารอย่างเป็นทางการยังคงอธิบายขั้นตอนการทำงานว่า เปิดView > Macrosเขียนหรือวางโค้ด และเรียกใช้ในตัวแก้ไข ไม่มีข้อเปลี่ยนแปลงใดๆ ในเอกสารปัจจุบันที่เปลี่ยนแปลงกระบวนการพื้นฐานนี้ มาโครนี้เป็นเพียงสคริปต์ตัวอย่างที่สามารถนำกลับมาใช้ใหม่ได้ ไม่ใช่รายงานการทดสอบซอฟต์แวร์ โปรดดูคู่มือเริ่มต้นใช้งานมาโคร ONLYOFFICEและข้อมูลอ้างอิงแท็บมุมมองตัวแก้ไขสเปรดชีต

เตรียมสำเนาที่ปลอดภัยและกำหนดขั้นตอนการล้างข้อมูล

สมมติว่าเวิร์กบุ๊กของคุณมีรายชื่อผู้ติดต่อ โดยมีส่วนหัวอยู่ในแถวที่ 1 และที่อยู่อีเมลอยู่ในคอลัมน์ B ตั้งแต่เซลล์ B2 ถึง B500 ค่าที่นำเข้าบางค่าอาจมีตัวแบ่งบรรทัด แท็บ หรือช่องว่างนำหน้าและต่อท้าย ตัวอย่างนี้จะทำความสะอาดเฉพาะคอลัมน์ข้อความนั้นคอลัมน์เดียวเท่านั้น ปรับช่วงให้ตรงกับชีตของคุณก่อนเรียกใช้โค้ด

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

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

เปิดตัวแก้ไขมาโครและเพิ่มสคริปต์

เปิดเวิร์กบุ๊กในโปรแกรมแก้ไขสเปรดชีต ONLYOFFICE เลือกมุมมอง > มาโครเพื่อเปิดตัวแก้ไขมาโคร จากนั้นสร้างมาโครหรือแทนที่โค้ดเริ่มต้นในตัวแก้ไข มาโครของ ONLYOFFICE ใช้ไวยากรณ์ JavaScript และ Office API ไม่ใช่มาโคร VBA เวิร์กโฟลว์มาโครของตัวแก้ไขและวิธีการ API ที่รองรับมีอธิบายไว้ในคู่มือเริ่มต้นใช้งานมาโครอย่าง เป็นทางการ

วางตัวอย่างนี้ลงไป จากนั้นเปลี่ยนB2:B500เป็นช่วงข้อความที่คุณต้องการใช้งานจริง:

(function () {
    var sheet = Api.GetActiveSheet();
    var range = sheet.GetRange("B2:B500");
    var rows = range.GetValue();
    var functions = Api.WorksheetFunction;

    for (var i = 0; i < rows.length; i++) {
        var value = rows[i][0];
        if (typeof value === "string") {
            value = functions.CLEAN(value);
            rows[i][0] = value.replace(/^\s+|\s+$/g, "")
                              .replace(/\s{2,}/g, " ");
        }
    }

    range.SetValue(rows);
})();

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

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

ใช้งานในขอบเขตที่ควบคุมได้

1. ตรวจสอบช่วงค่าอีกครั้ง หากรายการสิ้นสุดที่แถวที่ 240 ตัวอย่างเช่น ให้ใช้ B2:B240 แทนที่จะประมวลผลแถวที่ไม่ได้ใช้งานหลายร้อยแถว
2. คลิกปุ่ม Runในโปรแกรมแก้ไขมาโคร หากโปรแกรมแก้ไขแจ้งข้อผิดพลาดทางไวยากรณ์ โปรดตรวจสอบเครื่องหมายคำพูด วงเล็บ และวงเล็บปีกกา ก่อนที่จะเปลี่ยนแปลงช่วงหรือเพิ่มตรรกะเพิ่มเติม
3. กลับไปที่เวิร์กชีตและตรวจสอบเซลล์หลายๆ เซลล์ รวมถึงค่าที่ต้องแก้ไข ค่าที่อยู่ในรูปแบบที่ถูกต้องแล้ว และเซลล์ว่าง

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

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

ตรวจสอบผลลัพธ์ก่อนนำมาโครไปใช้ซ้ำ

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

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

ทำให้การทำความสะอาดสามารถทำซ้ำได้

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

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