วิธีวิเคราะห์สเปรดชีตที่คนอื่นสร้างขึ้น (โดยไม่ต้องทำวิศวกรรมย้อนกลับ)

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

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

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

ทำไมสเปรดชีตที่คนอื่นสร้างขึ้นถึงอ่านยาก

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

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

โครงสร้างก็สามารถซ่อนอยู่ได้เช่นกัน ชีตอาจถูกซ่อนไว้, แถวอาจถูกจัดกลุ่มและยุบไว้, และช่วงที่ตั้งชื่อ (named range) อาจชี้ไปยังจุดที่แตกต่างจากชื่อของมันอย่างสิ้นเชิง นอกจากนี้ ลิงก์ภายนอกที่เชื่อมไปยังไฟล์ที่คุณไม่มี ก็จะยังคงแสดงผลลัพธ์ที่แคชไว้ล่าสุดต่อไปโดยไม่มีการแจ้งเตือนข้อผิดพลาดใดๆ

นอกจากนี้ยังมีปัญหาเรื่องเวอร์ชัน เมื่อในโฟลเดอร์มีไฟล์ชื่อ model_v3, model_final และ model_final_USE_THIS ชื่อไฟล์จึงไม่ใช่หลักฐานที่เชื่อถือได้เลย

สิ่งที่คุณต้องเสียไปจากเรื่องนี้

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

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

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

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

วิธีการแก้ปัญหาเฉพาะหน้าที่ผู้คนมักจะลองใช้

ทางเลือกที่ 1: แยกตัวเลขที่พิมพ์ป้อนเข้าไปออกจากตัวเลขที่คำนวณด้วยสูตร

ก่อนที่จะอ่านตรรกะใดๆ ให้หาคำตอบก่อนว่าเซลล์ไหนเป็นข้อมูลนำเข้า (input) ฟังก์ชัน ISFORMULA จะส่งกลับค่า TRUE สำหรับเซลล์ใดๆ ที่มีสูตร ดังนั้นการใช้คอลัมน์ช่วยคำนวณ (helper column) ทั่วทั้งชีตจะช่วยเปิดเผยค่าที่เขียนทับไว้ (hardcoded values) ได้ทันที

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

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

ทางเลือกที่ 2: ติดตามการเชื่อมโยงของสูตร (Dependencies)

เครื่องมือตรวจสอบสูตร (Formula Auditing) ของ Excel จะช่วยวาดแผนผังความสัมพันธ์ Microsoft มีเอกสารอธิบายเกี่ยวกับ การแสดงความสัมพันธ์ระหว่างสูตรและเซลล์ โดยฟังก์ชัน Trace Precedents จะแสดงว่ามีเซลล์ใดบ้างที่ส่งค่ามายังเซลล์นี้ และ Trace Dependents จะแสดงว่าเซลล์นี้ส่งค่าไปยังเซลล์ใดบ้าง

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

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

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

ทางเลือกที่ 3: ทำรายการตรวจสอบ (Inventory) ในระดับเวิร์กบุ๊ก

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

Microsoft มีเอกสารแนะนำ Add-in สำหรับเรื่องนี้โดยเฉพาะ นั่นคือ Spreadsheet Inquire ซึ่งจะวิเคราะห์โครงสร้างและความสัมพันธ์ของเวิร์กบุ๊ก การเปิดใช้งานจะขึ้นอยู่กับเวอร์ชัน Office ของคุณ ดังนั้นโปรดตรวจสอบข้อมูลในหน้าเว็บดังกล่าวก่อนวางแผนใช้งาน ส่วนการอ้างอิงแบบวงกลม (Circular references) ก็ควรได้รับการตรวจสอบแยกต่างหาก ซึ่ง Microsoft ได้อธิบายวิธี ค้นหาและจัดการกับการอ้างอิงแบบวงกลม ไว้ในอีกส่วนหนึ่ง

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

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

วิธีวิเคราะห์เวิร์กบุ๊กที่รับช่วงต่อมาด้วย Powerdrill Bloom

ขั้นตอนที่ 1: อัปโหลดเวิร์กบุ๊ก

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

ขั้นตอนที่ 2: ถามคำถามเกี่ยวกับโครงสร้างด้วยภาษาธรรมชาติ

เริ่มต้นด้วยการดูแผนผังโครงสร้างแทนที่จะดูตัวเลข ถามว่าชีตไหนที่ดูเหมือนเป็นข้อมูลนำเข้าดิบ (raw inputs) และชีตไหนที่ดูเหมือนเป็นบทสรุปที่คำนวณมา (derived summaries) รวมถึงจุดที่ฟิลด์เดียวกันแสดงค่าที่แตกต่างกันในแต่ละชีต

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

ขั้นตอนที่ 3: ส่งออกแผนภูมิ รายงาน หรือสไลด์นำเสนอ (Deck)

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

ทำไมวิธีนี้ถึงดีกว่าการอ่านสูตรทีละเซลล์

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

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

ข้อผิดพลาดที่พบบ่อย

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

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

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

การละเลยชีตที่ซ่อนอยู่ ชีตที่ซ่อนอยู่มักจะเป็นที่เก็บตารางค้นหาข้อมูล (lookup table) ที่ทุกอย่างต้องพึ่งพา ควรยกเลิกการซ่อนชีตทั้งหมดก่อนที่จะด่วนสรุปว่าไฟล์นี้ไม่มีอะไรซับซ้อน

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

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

การทำความสะอาดข้อมูลก่อนทำความเข้าใจ การลบเซลล์ที่ผสานกัน (merged cells) และแถวว่างออกไปอาจทำให้ไฟล์อ่านง่ายขึ้น แต่ก็เป็นการทำลายหลักฐานที่แสดงว่าไฟล์นี้ถูกสร้างขึ้นมาอย่างไร ควรสำเนาไฟล์เก็บไว้ก่อนเสมอ

บทสรุป

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

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

สิ่งที่ทำให้มันมีราคาแพงคือการต้องทำซ้ำแบบเดิมทุกครั้งที่มีการแก้ไขปรับปรุง หากนั่นคือสิ่งที่ทำให้คุณต้องเสียเวลาไปทั้งสัปดาห์ ลองใช้ Powerdrill Bloom กับไฟล์ตามสภาพที่คุณได้รับมาได้เลย นอกจากนี้ โปรดดูคู่มือของเราเกี่ยวกับ วิธีวิเคราะห์ Excel ด้วย AI และ การทำความสะอาดและลบข้อมูลซ้ำซ้อน รวมถึงหน้า Excel AI assistant และ AI data cleaning ของเราด้วย

คำถามที่พบบ่อย

ฉันจะค้นหาค่าที่เขียนทับไว้ (Hardcoded values) ในสเปรดชีตที่คนอื่นสร้างขึ้นได้อย่างไร?

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

ฉันจะดูสูตรที่อยู่เบื้องหลังเซลล์ในรูปแบบข้อความได้อย่างไร?

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

ฉันจะรู้ได้อย่างไรว่าเซลล์หนึ่งๆ ขึ้นอยู่กับเซลล์ใดบ้าง?

ใช้ฟังก์ชัน Trace Precedents บนแท็บ Formulas เพื่อดูว่ามีเซลล์ใดบ้างที่ส่งค่ามายังเซลล์นี้ และใช้ Trace Dependents เพื่อดูว่าเซลล์นี้ส่งค่าไปยังเซลล์ใดบ้าง ลูกศรสีดำที่ชี้ไปยังไอคอนเวิร์กชีตหมายความว่าการอ้างอิงนั้นอยู่นอกชีตปัจจุบัน

ฉันควรทำความสะอาดเวิร์กบุ๊กที่รับช่วงต่อมาก่อนที่จะวิเคราะห์หรือไม่?

ไม่ควรทำก่อนที่จะทำแผนผังโครงสร้าง การทำความสะอาดข้อมูลจะเป็นการลบหลักฐานที่แสดงว่าไฟล์นี้ถูกสร้างขึ้นมาอย่างไร รวมถึงเซลล์ที่ผสานกันและบล็อกว่างที่ระบุโครงสร้าง ไม่ว่าจะอย่างไรก็ตาม ควรเก็บไฟล์ต้นฉบับที่ยังไม่ได้แก้ไขไว้หนึ่งสำเนาเสมอ

วิธีที่เร็วที่สุดในการตรวจสอบว่ายอดรวมนั้นน่าเชื่อถือหรือไม่คืออะไร?

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