วิธีจัดหน้าตารายงาน Excel ให้พร้อมส่งผู้บริหาร

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

โพสต์นี้ใช้ PhpSpreadsheet เป็นตัวอย่าง ถ้าเขียนผ่าน Laravel Excel โค้ดเดียวกันนี้อยู่ใน WithStyles หรือ event AfterSheet (ดูวิธีใช้ concern เหล่านี้)

ส่วนประกอบของรายงานที่อ่านง่าย#

รายงานหนึ่งชีทที่ส่งให้ผู้บริหารได้ ทุกส่วนตั้งเป็นช่วงทีเดียว

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

ตั้งสไตล์เป็นช่วงทีเดียวทั้งแถว#

PhpSpreadsheet รับสไตล์เป็น array แล้วใช้กับทั้งช่วงได้ในคำสั่งเดียว หัวรายงานกับหัวตารางจึงเขียนได้สั้น ๆ

$sheet->mergeCells('A1:C1');
$sheet->getStyle('A1')->applyFromArray([
    'font' => ['bold' => true, 'size' => 14],
    'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER],
]);

$sheet->getStyle('A3:C3')->applyFromArray([
    'font' => ['bold' => true],
    'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => 'E4E8FB']],
    'borders' => ['bottom' => ['borderStyle' => Border::BORDER_THIN]],
]);

$sheet->getDefaultRowDimension()->setRowHeight(15);

บรรทัดสุดท้ายสำคัญกว่าที่เห็น ถ้าวนตั้ง setRowHeight() ทีละแถว PhpSpreadsheet ต้องเก็บข้อมูลของทุกแถวแยกกัน ในรายงานจาก SAP ที่เราทำ รายงาน 45,000 แถว ใช้เวลากับลูปนี้อย่างเดียวราวหนึ่งนาที การตั้งค่าเริ่มต้นของชีทครั้งเดียวได้ผลเหมือนกันโดยแทบไม่เสียเวลา

เส้นขอบก็เหมือนกัน กรอบนอกกับเส้นใต้หัวตารางพอสำหรับรายงานส่วนใหญ่ การตีเส้นในทุกช่อง (inside ทั้งตาราง) ทำให้ไฟล์ใหญ่เขียนช้าลงชัดเจน ถ้าอยากให้อ่านตามแถวง่ายขึ้น ใช้เส้นสีอ่อนหรือตั้งตัวกรองให้ผู้ใช้เลือกดูเองก็ได้

ไฮไลต์แถวด้วย conditional formatting#

ทางแรกคือให้ Excel ตัดสินเองด้วย conditional formatting กฎจะอยู่ในไฟล์ ถ้าผู้ใช้แก้ตัวเลขหรือวางข้อมูลใหม่ สีจะเปลี่ยนตาม ตัวอย่างนี้ไฮไลต์ทั้งแถวเมื่อยอดขายในคอลัมน์ B ติดลบ

$negative = new Conditional;
$negative->setConditionType(Conditional::CONDITION_EXPRESSION)->addCondition('$B4<0');
$negative->getStyle()->getFont()->setBold(true)->getColor()->setRGB('C2185B');

$sheet->getStyle('A4:C7')->setConditionalStyles([$negative]);

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

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

merge เฉพาะหัวรายงาน และอย่าให้ช่วงทับกัน#

merge ช่วยให้หัวรายงานหรือหัวกลุ่มคอลัมน์ ("ไตรมาส 1" คลุมสามเดือน) ดูเป็นระเบียบ แต่มีเรื่องที่ต้องรู้สามข้อ

• ช่วงที่ merge ทับกัน เช่น A1:C1 กับ B1:D1 Excel จะแจ้งว่าไฟล์เสียและขอซ่อม แล้วทิ้ง merge ทั้งหมดในชีทนั้น ไม่ใช่แค่ช่วงที่ทับ
• ค่าที่เหลือหลัง merge คือค่าของช่องซ้ายบนเท่านั้น ถ้าเขียนป้ายไว้ช่องล่างแล้ว merge แนวตั้ง ป้ายจะหายไป ให้ย้ายค่าขึ้นไปช่องบนก่อน merge
• merge ในพื้นที่ข้อมูล ทำให้ผู้ใช้เรียงข้อมูลไม่ได้ Excel จะบอกว่าช่องที่ merge ต้องมีขนาดเท่ากันก่อน ข้อมูลที่ต้องเรียงหรือกรองจึงควรเป็นตารางเรียบ ๆ ไม่มี merge

ตรึงหัวตารางไว้และเปิดตัวกรอง#

รายงานที่ยาวเกินหนึ่งจอควรตรึงแถวหัวตารางไว้ เลื่อนลงไปเท่าไหร่ก็ยังรู้ว่าคอลัมน์ไหนคืออะไร freezePane() รับช่องแรกที่ "ไม่ถูกตรึง" ถ้าหัวตารางอยู่แถว 3 ให้ใส่ A4

$sheet->freezePane('A4');
$sheet->setAutoFilter('A3:C'.$sheet->getHighestRow());

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

แยกชีทตามเรื่องและใส่สีแท็บ#

รายงานที่มีทั้งสรุปและรายละเอียด แยกเป็นชีทอ่านง่ายกว่าวางต่อกันในชีทเดียว ใส่ชีทสรุปไว้แรกสุด เพราะเป็นชีทที่ Excel เปิดขึ้นมาก่อน สีแท็บ ($sheet->getTabColor()->setRGB('2E9E6B')) ช่วยจัดกลุ่มชีทที่เป็นเรื่องเดียวกัน ส่วนกฎการตั้งชื่อชีทภาษาไทยอยู่ในโพสต์เรื่องความกว้างคอลัมน์และฟอนต์

ยอดรวมคิดใน PHP หรือเขียนเป็นสูตร#

ทั้งสองแบบใช้ได้ เลือกตามว่าคนอ่านจะทำอะไรกับไฟล์

	คิดใน PHP แล้วเขียนเป็นตัวเลข	เขียนเป็นสูตร =SUM() หรือ =SUBTOTAL()
ค่าที่ได้	ตรงกับในระบบแน่นอน	Excel คำนวณจากตัวเลขในชีท
ผู้ใช้แก้ตัวเลขหรือกรอง	ยอดรวมไม่เปลี่ยน	ยอดเปลี่ยนตาม SUBTOTAL(9, …) รวมเฉพาะแถวที่ตัวกรองแสดง
เปิดในโปรแกรมอื่น	เห็นตัวเลขเสมอ	ขึ้นกับว่าโปรแกรมนั้นคำนวณสูตรหรือไม่
เหมาะกับ	รายงานที่ใช้เป็นหลักฐาน ยอดต้องตรงกับระบบ	ไฟล์ที่ผู้ใช้จะกรอง แก้ หรือวิเคราะห์ต่อ

SUBTOTAL รหัส 9 คือผลรวมที่ข้ามแถวที่ตัวกรองซ่อนไว้ แถวรวมที่ใช้สูตรนี้จึงวางไว้ใต้ตารางได้โดยไม่เพี้ยนตอนกรอง

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

รูปแบบตัวเลขติดลบก็ควรเลือกให้ตรงกับคนอ่าน ฝ่ายบัญชีคุ้นกับวงเล็บ #,##0.00;(#,##0.00) และขีดแทนศูนย์ ส่วนรายงานทั่วไปใช้เครื่องหมายลบตรง ๆ รายละเอียดของ format code อยู่ในโพสต์เรื่องตัวเลขยาวและรูปแบบตัวเลข

สไตล์ที่เลื่อนหรือขาดหายเมื่อรายงานเปลี่ยน#

บางรายงานต้องหน้าตาเหมือนต้นฉบับเดิมที่ผู้บริหารคุ้นเคย อย่างตอนที่เราทำระบบรายงานจาก SAP ที่ส่ง Excel ให้แต่ละฝ่ายตามเวลา บางรายงานย้ายมาจาก Power BI ก็ต้องคิดตั้งแต่สีเส้นขอบไปจนถึงตำแหน่งยอดรวมให้ตรงกับของเดิม เราจึงรวมสไตล์ที่ใช้ซ้ำ (หัวตาราง ยอดรวม เส้นขอบ) ไว้ที่เดียว แล้วให้ทุกรายงานเรียกใช้

อีกสองเรื่องที่เจอบ่อยเมื่อรายงานเริ่มเปลี่ยน

• แถวเว้นว่างหายไป Laravel Excel ตัดแถวที่เป็น array ว่างทิ้ง สไตล์ที่ตั้งตามเลขแถวจะเลื่อนขึ้นหนึ่งแถว ให้ใส่แถวเว้นว่างเป็น [''] แทน []
• เพิ่มคอลัมน์แล้วสีแถวรวมไม่สุดตาราง เพราะช่วงสีเขียนเป็น A10:H10 ไว้ตายตัว ให้คำนวณคอลัมน์สุดท้ายจาก $sheet->getHighestColumn() แทน