วิธีกระทบยอดรายการธุรกรรมในสเปรดชีต (โดยไม่ต้องจับคู่แถวด้วยตนเอง)

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

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

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

ทำไมการกระทบยอดในสเปรดชีตถึงยากกว่าที่คิด

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

การชำระเงินผ่านบัตรอาจปรากฏเพียงครั้งเดียวในบัญชีแยกประเภทของคุณ แต่ปรากฏสองครั้งในไฟล์ที่ส่งออกจากระบบประมวลผลการชำระเงิน โดยแยกเป็นยอดชำระและค่าธรรมเนียม ใบแจ้งหนี้ของซัพพลายเออร์อาจใช้รหัสอ้างอิง INV-0042 ในระบบหนึ่ง และใช้ INV42 ในอีกระบบหนึ่ง การโอนเงินที่เริ่มทำในวันที่ 31 แต่อาจไปเสร็จสิ้นในวันที่ 1 ซึ่งทำให้ตัวเลขของทั้งเดือนคลาดเคลื่อนได้

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

นอกจากนี้ยังมีกับดักเรื่องการปัดเศษที่ผู้คนมักจะเจอในทุกๆ เดือน ค่าสกุลเงินที่บันทึกด้วยทศนิยมแบบลอยตัวเต็มรูปแบบอาจมีความแตกต่างกันในทศนิยมตำแหน่งที่สี่ ดังนั้น ยอดเงินสองยอดที่แสดงผลเป็น 1,204.50 เหมือนกัน จึงอาจไม่ผ่านการทดสอบความเท่ากันทุกประการ

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

สิ่งที่คุณต้องเสียไป

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

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

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

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

วิธีแก้ปัญหาชั่วคราวที่ผู้คนนิยมใช้

ทางเลือกที่ 1: เปรียบเทียบยอดรวมก่อนที่จะเริ่มจับคู่รายการใดๆ

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

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

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

ทางเลือกที่ 2: สร้างคีย์สำหรับจับคู่ (Match Key) แล้วใช้สูตรค้นหา

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

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

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

ทางเลือกที่ 3: จัดการกับความแตกต่างทั้ง 4 ประเภทอย่างเป็นระบบ

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

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

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

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

วิธีการกระทบยอดรายการธุรกรรมด้วย Powerdrill Bloom

ขั้นตอนที่ 1: อัปโหลดไฟล์ทั้งสองไฟล์

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

ขั้นตอนที่ 2: อธิบายการกระทบยอดด้วยภาษาธรรมชาติ

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

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

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

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

ทำไมวิธีนี้ถึงดีกว่าการสร้างระบบจับคู่ใหม่ทุกเดือน

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

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

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

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

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

การละเลยความแตกต่างจากการปัดเศษ ค่าที่แสดงผลเหมือนกันทุกประการอาจไม่ผ่านการทดสอบความเท่ากัน ให้ปัดเศษทั้งสองฝั่งให้มีความละเอียดเท่ากันก่อนที่จะนำมาเปรียบเทียบกัน

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

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

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

บทสรุป

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

สิ่งที่ทำให้เสียต้นทุนมากที่สุดคือการต้องมาสร้างระบบใหม่ในทุกๆ เดือน โดยเฉพาะอย่างยิ่งเมื่อข้อมูลอ้างอิงไม่ตรงกัน หรือการชำระเงินหนึ่งรายการเชื่อมโยงกับหลายแถว หากนั่นคือสิ่งที่คุณต้องเผชิญในขั้นตอนการปิดบัญชี ลองใช้ Powerdrill Bloom กับไฟล์ส่งออกทั้งสองไฟล์ดู นอกจากนี้ คุณยังสามารถดูคู่มือของเราเกี่ยวกับการรวมไฟล์ Excel สองไฟล์โดยไม่ใช้ VLOOKUP และการสร้างรายงานเปรียบเทียบงบประมาณกับค่าใช้จ่ายจริง (budget vs. actual) ส่วนหน้ารายงานค่าใช้จ่ายและการวิเคราะห์กระแสเงินสดจะครอบคลุมเวิร์กโฟลว์ที่เกี่ยวข้องเพิ่มเติม

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

การกระทบยอดรายการธุรกรรมหมายถึงอะไร?

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

Excel สามารถกระทบยอดสองรายการโดยอัตโนมัติได้หรือไม่?

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

ทำไมยอดเงินสองยอดที่ดูเหมือนกันถึงจับคู่กันไม่สำเร็จ?

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

ฉันจะจัดการกับการชำระเงินหนึ่งรายการที่ปรากฏเป็นหลายแถวได้อย่างไร?

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

ควรลบแถวที่จับคู่ไม่ได้ทิ้งหรือไม่?

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