Search

วันเสาร์ที่ 22 ธันวาคม พ.ศ. 2555

ทำ Subtotal ใน Excel Worksheet

Subtotal

คือผลรวมย่อย ซึ่งฟังก์ชัน SUBTOTALจะส่งกลับผลรวมย่อยของรายการหรือฐานข้อมูล

SUBTOTAL(function_num, ref1, ref2, ...)

  • function_num คือตัวเลข 1 ถึง 11 ที่ใช้เพื่อให้ Subtotal คืนค่าที่คำนวณในฐานข้อมูล


    1. ค่าเฉลี่ย
    2. นับจำนวน
    3. COUNTA นับจำนวนเซลล์
    4. ค่ามากที่สุด
    5. ค่าน้อยที่สุด
    6. ผลคูณ
    7. ส่วนเบี่ยงเบนมาตรฐาน
    8. ส่วนเบี่ยงเบนมาตรฐานของประชากร
    9. ผลรวม
    10. ค่าความแปรปรวน
    11. ค่าความแปรปรวนของประชากร

  • ref1, ref2 ... คือ 1-254 การอ้างอิงเพื่อให้ฟังก์ชันหาผลรวมย่อยในข้อมูล
ถ้ากำหนดค่า function_num เท่ากับ 1, Subtotal จะหาค่าเฉลี่ยของค่าอ้างอิงคือ ref1- ref สุดท้ายที่ถูกกำหนดไว้ และถ้า function_num เป็น 9 ก็จะเป็นการหาผลรวมของเงื่อนไข ref ทั้งหมด ซึ่ง function_num ที่ใช้กันบ่อยคือ การหาผลรวม (9), หาค่าเฉลี่ย(1), นับจำนวน(2), หาค่ามากที่สุด (4), หาค่าน้อยที่สุด(5)

ตัวอย่างการใช้งาน Subtotal
  1. สมมุติให้มีข้อมูล 3 ส่วน D1-D3 แต่ละส่วนมีข้อมูล 5 รายการ 
  2. และใช้ Subtotal ให้คืนค่าดังนี้
    • ผลรวม -subtotal(9,(A2:A5))
    • ค่าเฉลี่ย -subtotal(1,(A2:B5))
    • ค่ามากที่สุด - subtotal(4,(A2:C5))
    • และค่าน้อยที่สุด - subtotal(5,(A2:C5))
  3. เพื่อให้ใช้งานได้ยืดหยุ่นขึ้นโดยกำหนดให้ตำแหน่ง function_num เป็นเซลล์ใดๆ ตามตัวอย่างของแถวที่ 7 ซึ่งกำหนดให้ B7 เป็นเลข 9 แล้วให้สูตรดึงค่าจาก B7 มากำหนดค่า function_num
ABCDD
1 D1D2D3 การใช้ Function SUBTOTAL ผลลัพท์
21003090 =subtotal(9,(A2:A5)) 350.00
31013050 =subtotal(1,(A2:B5)) 76.25
420020110 =subtotal(4,(A2:C5)) 250.00
54080250 =subtotal(5,(A2:C5)) 10.00
6
7 9 =subtotal(B7,(B2:C5)) 760.00

นอกจากการใช้ฟังก์ชันหาผลรวมย่อย ใน Excel ยังมีการใช้ Subtotal รูปแบบอัตโนมัติที่แทรกผลรวมย่อยลงในรายการได้เลย

Data Tab in Excel Menu
Subtotal in Outline Menu under Data Tab
  1. เริ่มต้นใช้ Subtotal โดยไปที่เมนูแทป Data เลือก Outline (เค้าร่าง)
  2. Excel จะแสดงกล่องโต้ตอบ Subtotal ในแต่ละหัวข้อต้องกำหนดเงื่อนไขเพื่อให้ Subtotal คืนค่าตามแบบที่ต้องการ
    • At each change in: เลือกคอลัมท์ที่ต้องการจัดกลุ่ม ในที่นี้เป็น Category
    • Use function: เลือกใช้ฟังชันก์ใดฟังก์ชันหนึ่งในกลุ่ม Subtotal
    • Add subtotal to: ต้องการแทรกผลรวมย่อยที่คอลัมท์ใดบ้าง
    • Replace current subtotals แทนที่ผลรวมย่อยเดิม (ถ้ามี)
    • Page break between groups แทรกตัวแบ่งหน้าของแต่ละกลุ่มซึ่งในที่นี้ถ้ากำหนดไว้ เส้นแบ่งหน้าจะแบ่งทุก Category
    • Summary below data ผลรวมของข้อมูลทั้งหมด หรือ Grand total

  3. Choose "Category" at each change in
    Choose "Sum" function to calculate
  4. จากนั้น ok
  5. หน้าตาของเวิร์คชีทจะเปลี่ยนไปโดยเพิ่มพื้นที่ด้านซ้ายขึ้นแยกเป็นสามส่วนโดยที่สามารถคลิกเพื่อดู detail ของรายงานในรูปแบบต่างๆ คือ
    • 1 : เมื่อคลิกที่ 1 ฟังก์ชันจะรวบรายงานเหลือผลของรายงานทั้งหมด Grand total, Grand Average ฯลฯ
    • 2 : รายงานจะแสดงเพียงกลุ่มที่กำหนดไว้ที่ At each change in:
    • 3 : แสดงรายการทั้งหมดที่แสดงผลรวมที่แทรกไว้ด้วย
Data sheet will show only subtotal summary when click 2

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

after using Subtotal, Data sheet will show all data and summary by click 3

Remove all Subtotal in data sheet ข้อดีของการทำ Subtotal คือสามารถย้อนสู่สภาพเดิมได้ โดยไปที่ Data แทป เลือก Outline ไปที่ Subtotal เหมือนเดิม จากนั้นให้เลือก Remove all


อีกนิดนึงก่อนจบ Excel Worksheet สามารถตั้งให้บอกค่าเฉลี่ย, จำนวนเซลล์, ยอดรวมได้  ทุกครั้งที่เราเลือกพื้นที่เซลล์ โดยการตั้งค่าที่ Customize Status Bar (คลิกเม้าส์ขวาที่ Status Bar)
Useful of Excel Status Bar

อย่างภาพตัวอย่างนี้ เลือกคลุมพื้นที่ของคอลัมท์ Stock level แถวที่ 15 ถึงแถวที่ 21 เมื่อดูที่ Status Bar แสดงค่าเฉลี่ย (average) เท่ากับ 11.85714286 ซึ่งได้จากผลรวม (sum) ของช่วงเซลล์ที่เลือกซึ่งคือ 83 หารด้วยจำนวนเซลล์ (count) ที่มีข้อมูลคือ 7 เซลล์

วีดีโอการใช้ Subtotal ใน Excel Worksheet จะทำให้เข้าใจง่ายขึ้น

วันพุธที่ 19 ธันวาคม พ.ศ. 2555

Shortcuts ที่น่าใช้ใน Excel


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

แต่คีย์ลัดก็คือคีย์ลัด ทำให้งานเร็วขึ้น และลดขั้นตอนการเรียกคำสั่งใช้งานได้เยอะเลย

โดยส่วนตัวชอบใช้ Shortcut ชุดหนึ่งเป็นชุดที่ใช้ได้บ่อยๆ และเป็นชุดที่ต้องพันกับไฟล์งานอยู่แทบทุกไฟล์

คีย์ลัด Shortcut สำหรับไฟล์งาน

Shortcut
การแสดงผลคีย์ลัด(Shortcut)เทียบกับเมนูหลัก
Ctrl
O
เปิดไฟล์งานอื่น, File > Open
Ctrl
N
เปิดสมุดงานใหม่, File > New
Ctrl
S
บันทึกไฟล์งานปัจจุบัน, File > Save
F12
บันทึกไฟล์งานเป็น, File > Save as
Ctrl
F2
ตัวอย่างก่อนพิมพ์, File > Print>Print Preview
Ctrl
F4
ปิดไฟล์งานปัจจุบัน, File > Close
Alt
F4
ปิดไฟล์งานเหมือน Ctrl+F4
Ctrl
W
ปิดไฟล์งานเหมือน Ctrl+F4

keyboard

คีย์ลัด Shortcut สำหรับแผ่นงาน

Shortcut
ผลของคำสั่งคีย์ลัด (Shortcut)
Ctrl
C
คัดลอก(Copy) เมื่อ Enter ที่เซลล์ใดๆ จะเป็นการวางค่า (paste)ที่คัดลอกมา
Ctrl
X
ตัด(Cut) เมื่อ Enter ที่เซลล์ใดๆ จะเป็นการวางค่า (paste) ที่ตัดมา
Ctrl
V
การวาง สามารถวางได้มากกว่า 1ครั้ง (Paste, Multiple Paste)
Ctrl
F
ค้นหา Find ค้นหาค่าในเซลล์ของ Worksheet ปัจจุบัน
Ctrl
H
ค้นหาและแทนที่ Find&Replace ค้นหาค่าใดๆในเซลล์และแทนทีค่าใหม่
Ctrl
A
เลือกพื้นที่เซลล์ปัจจุบันทั้งหมด Select all กด ครั้งที่สองจะเลือกทั้งแผ่นงาน
Ctrl
Shift
* 8
เหมือน Ctrl+A แต่ต้องกด Ctrl, Shift, * พร้อมกัน
Ctrl
Shift
Spacebar
เหมือน Ctrl+A
Ctrl
Shift
End
เลือกเซลล์ปัจจุบันถึงขอบเขตเซลล์ที่ใช้งานอยู่
F8
ล็อกเซลล์หรือพื้นที่เซลล์
Ctrl
ลูกศร
ไปขอบเขตข้อมูลก่อนเซล์ว่าง ใช้รวมกับ F8 จะคล้าย Ctrl+Shift+End
F9
สั่งคำนวณแผ่นงาน ใช้เมื่อกำหนดให้การคำนวณเป็น manual
Ctrl
Home
ไปจุดเริ่มต้นของแผ่นงานหรืออาจจะไปที่ A1
Ctrl
End
ไปมุมล่างขวาสุดของแผ่นงาน Worksheet
Ctrl
Y
ทำซ้ำคำสั่งสุดท้าย Redo
Ctrl
Z
ยกเลิกคำสั่งสุดท้าย, เลิกทำหรือย้อนกลับ Undo
Ctrl
! 1
แสดงกล่องโต้ตอบ การจัดรูปแบบเซลล์ Format cell