Tag: pivot

  • วิธีดับเบิ้ลคลิ๊กแสดงข้อมูลรายะเอียดใน Pivot แบบ Data Model ให้เกิน 1000 บรรทัด

    วิธีดับเบิ้ลคลิ๊กแสดงข้อมูลรายะเอียดใน Pivot แบบ Data Model ให้เกิน 1000 บรรทัด

    บทความนี้เป็น Trick สั้นๆ ครับ ปกติแล้ว ถ้าเราใช้ Pivot Table แบบปกติ เราจะสามารถ Double Click ที่ผลลัพธ์แต่ละช่องเพื่อจะแสดงข้อมูลที่มาของเลขนั้นๆ ได้ครบเลย แต่ถ้าเป็น Pivot Table โหมด Data Model มันจะแสดงข้อมูลได้แค่ 1000 บรรทัดแรกของการ Filter นั้นๆ ซึ่งเป็นเรื่องที่น่าหงุดหงิดมาก

    ลองลงมือทำให้เกิดปัญหา Pivot ได้แค่ 1000

    สมมติเรามีข้อมูลสมมติโดยสร้างเป็นเลข 1-200,000 ไว้ (ผมใช้ Home->Fill-> Series ช่วยสร้างจะได้เร็วๆ)

    วิธีดับเบิ้ลคลิ๊กแสดงข้อมูลรายะเอียดใน Pivot แบบ Data Model ให้เกิน 1000 บรรทัด 1

    แล้วสร้างคอลัมน์ Group อีกซักอันให้มีกลุ่มละ 90,000 ตัว ด้วยการเขียนสูตรว่า

    =ROUNDUP(A2/90000,0)
    วิธีดับเบิ้ลคลิ๊กแสดงข้อมูลรายะเอียดใน Pivot แบบ Data Model ให้เกิน 1000 บรรทัด 2

    จากนั้นเรา convert เป็น Table ซะ แล้วเอาเข้า Pivot แบบ Data Model

    วิธีดับเบิ้ลคลิ๊กแสดงข้อมูลรายะเอียดใน Pivot แบบ Data Model ให้เกิน 1000 บรรทัด 3

    เรา Pivot ข้อมูลอะไรก็แล้วแต่ สมมติได้ดังนี้

    วิธีดับเบิ้ลคลิ๊กแสดงข้อมูลรายะเอียดใน Pivot แบบ Data Model ให้เกิน 1000 บรรทัด 4

    แล้วเราอยากรู้ว่าเลข 135000.5 มาจากข้อมูลบรรทัดไหนบ้าง เราก็กด ดับเบิ้ลคลิ๊กได้เลย มันจะสร้าง sheet ใหม่ แล้ว แสดงข้อมูลบรรทัดที่เป็นแหล่งที่มาของเลข 135000.5 นั้น (แสดงผลการ Drill Through จาก Filter Context นั่นเอง)

    ปัญหาคือ ถ้าเป็น Pivot ปกติจะแสดงครับ แต่ Pivot แบบ Data Model จะแสดงตัวอย่างแค่ 1000 บรรทัดแรก!

    วิธีดับเบิ้ลคลิ๊กแสดงข้อมูลรายะเอียดใน Pivot แบบ Data Model ให้เกิน 1000 บรรทัด 5

    ถ้าเราอยากเห็นมากกว่า 1000 แถวต้องทำไง?

    ให้เราไปแก้ Setting ของ connection ตามรูปนี้ โดยคลิ๊กขวาที่ ThisWorkbookDataModel แล้วเลือก Properties…

    วิธีดับเบิ้ลคลิ๊กแสดงข้อมูลรายะเอียดใน Pivot แบบ Data Model ให้เกิน 1000 บรรทัด 6

    จากนั้นแก้ตัวเลขใน OLAP Drill Through ให้เยอะๆ ไปเลย เช่น เท่ากับจำนวนแถว Excel

    วิธีดับเบิ้ลคลิ๊กแสดงข้อมูลรายะเอียดใน Pivot แบบ Data Model ให้เกิน 1000 บรรทัด 7

    คราวนี้ลบผลการ Drill Through อันเดิมที่ได้ 1000 แถวทิ้งไปซะ (ลบทั้งชีทเลยก็ได้) แล้วลองดับเบิ้ลคลิ๊กใหม่ครับ

    นี่ไง คราวนี้ออกมาครบเลย เย้!

    วิธีดับเบิ้ลคลิ๊กแสดงข้อมูลรายะเอียดใน Pivot แบบ Data Model ให้เกิน 1000 บรรทัด 8
  • การใช้ Excel Power Pivot ตอนที่ 1 : การใช้งานพื้นฐาน

    การใช้ Excel Power Pivot ตอนที่ 1 : การใช้งานพื้นฐาน

    คงปฏิเสธไม่ได้ว่า Pivot Table นั้นเป็นเครื่องมือสรุปข้อมูลที่ใช้ง่ายที่สุดของ Excel (จริงๆ ผมว่าถ้าเทียบกับโปรแกรมอื่น Pivot ก็ยังง่ายและเจ๋งกว่าอยู่ดี) ซึ่งการใช้ Pivot Table แบบทั่วๆ ไปก็สามารถตอบโจทย์การทำงานได้มหาศาลแล้ว อย่างไรก็ตาม ก็ยังมีงานบางอย่าง ที่ Pivot Table ธรรมดายังตอบโจทย์ไม่ได้ แต่ต้องใช้ Power Pivot แทน เช่น

    1. การนับข้อมูลแบบไม่ซ้ำ เช่น นับจำนวนลูกค้า, จำนวนวันที่ที่ขายของ, จำนวน sales, จำนวนประเภทสินค้า
    2. การทำ Calculated Field ที่ไม่ใช่การ SUM
    3. การแสดงข้อมูลสรุปในช่อง Value ออกมาเป็น Text เช่น แสดงชื่อลูกค้าคนล่าสุด แสดงรายการสินค้าออกมาคั่นด้วย comma แสดงสินค้าขายดี Top3
    4. การวิเคราะห์ข้อมูลเทียบกับช่วงเวลาก่อนหน้า
    5. การวิเคราะห์ข้อมูลจากหลายตาราง เช่น การเอาค่าจากอีกตารางมาโดยไม่ต้อง VLOOKUP, การคำนวณ Actual vs Target

    นี่คือตัวอย่างของสิ่งที่Pivot ธรรมดาๆ ทำไม่ได้…

    แต่ว่าไม่ต้องเสียใจไป เพราะจริงๆ แล้วถ้าเราใช้ Pivot Table อีกโหมดนึงที่เรียกว่าโหมด Data Model ซึ่งจะทำให้ Pivot Table ธรรมดากลายเป็น Power Pivot ซึ่งจะมีความสามารถเพิ่มขึ้นมหาศาลใกล้เคียงกับความสามารถของ Power BI เลยล่ะ

    อย่างไรก็ตาม Excel ที่จะใช้ Data Model และ Power Pivot ได้จะต้องเป็น Excel 2010 ขึ้นไปเท่านั้นนะครับ เก่ากว่านี้หมดสิทธิ์ ซึ่งถ้าเป็น 2013 ขึ้นไปจะมีโหมด Data Model ให้ใช้ในตัว แต่ถ้าเป็น Excel 2010 จำเป็นต้องโหลด Add-in Power Pivot มาใช้ซะก่อน

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

    เตรียมความพร้อม Data Model / Power Pivot

    คนที่ใช้ Excel 2010 ให้ไปโหลด Add-in Power Pivot นี่แล้ว Install ซะ มันจะมี Ribbon Power Pivot โผล่ออกมาให้ใช้ครับ

    การใช้ Excel Power Pivot ตอนที่ 1 : การใช้งานพื้นฐาน 9

    คนที่ Version ใหม่กว่า 2010 ก็ให้ไป Enable Power Pivot add-in ซะก่อน โดยไปที่ File -> Options -> Add-in -> Com Add-ins -> Go

    การใช้ Excel Power Pivot ตอนที่ 1 : การใช้งานพื้นฐาน 10

    จากนั้นก็เลือก Power Pivot ซะ

    การใช้ Excel Power Pivot ตอนที่ 1 : การใช้งานพื้นฐาน 11

    หลังจาก ok ไปจนครบ ก็จะมี Ribbon Power Pivot มาให้ใช้แล้ว

    การใช้ Excel Power Pivot ตอนที่ 1 : การใช้งานพื้นฐาน 12

    โหลดไฟล์ประกอบได้ที่นี่

    โหลดผ่าน GitHub

    ก่อนอื่นเรามาดูวิธีเรียกใช้ Pivot Table โหมด Data Model กันครับ

    วิธีเรียกใช้ Pivot Table โหมด Data Model

    การเอาข้อมูลเข้าสู่โหมด Data Model นั้นทำได้ 3 วิธี นั่นคือ

    1. เอาเข้าด้วย Pivot Table (ผ่านการติ๊กเลือก Option ล่างสุดตอนสร้าง Pivot)
    2. นำเข้าผ่านเครื่องมือ Power Pivot ในคำสั่ง Add to Data Model
    3. นำเข้าผ่าน Power Query โดย Load To.. Connection Only แล้วติ๊ก Add to Data Model
    การใช้ Excel Power Pivot ตอนที่ 1 : การใช้งานพื้นฐาน 13

    หากใช้ข้อมูลเพียงตารางเดียวจากชีท TXData การเอาเข้าผ่าน Pivot Table ไปเลยจะง่ายที่สุดเลย

    Tips : ก่อนจะเอาข้อมูลเข้าไปวิเคราะห์ใน Pivot Table เพื่อให้ในอนาคตชีวิตสบายขึ้น ควรแปลงข้อมูลนั้นให้เป็น Table ก่อน ด้วยการกด Insert -> Table เพื่อให้ตัว PivotTable สามารถอ้างอิงข้อมูลจาก Data Source ที่ขยายอาณาเขตตัวเองตามข้อมูลใหม่ที่ใส่เพิ่มได้โดยอัตโนมัติ ซึ่งผมตั้งชื่อ Table นี้ว่า TXData ละกัน (แต่ขั้นตอนนี้ไม่ได้จำเป็นกับการทำ Data Model นะ)

    การใช้ Excel Power Pivot ตอนที่ 1 : การใช้งานพื้นฐาน 14

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

    การใช้ Excel Power Pivot ตอนที่ 1 : การใช้งานพื้นฐาน 15

    พอแปลงเป็น Table แล้วให้เลือกข้อมูลที่จะเอาเข้า Pivot Table แล้วกด Insert -> Pivot Table ตามปกติ แต่ให้ติ๊ก Add this Data to the Data Model ด้วย (สำคัญมากกกกกก)

    การใช้ Excel Power Pivot ตอนที่ 1 : การใช้งานพื้นฐาน 16

    พอกด ok เราจะได้ PivotTable โหมดพิเศษที่เป็น Mode Data Model ขึ้นมาแล้ว ซึ่งหน้าตาแทบจะเหมือน Pivot Table ธรรมดาๆ เลย แต่มีความต่างตรงที่ผมตีกรอบให้ดู

    การใช้ Excel Power Pivot ตอนที่ 1 : การใช้งานพื้นฐาน 17

    เอาล่ะเรามาเริ่มทำอะไรเจ๋งๆ ที่ Pivot Table ปกติทำไม่ได้กัน

    ความเจ๋ง 1 : นับข้อมูลแบบไม่ซ้ำ

    ปกติแล้วเวลาเราลากข้อมูลเข้ามา Count ใน Pivot มันก็จะรับแค่ว่าคอลัมน์ที่เราเลือกมีข้อมูลอยู่กี่ตัว ไม่ได้สนว่าซ้ำรึเปล่า (ซึ่งส่วนใหญ่จะซ้ำกระจาย) เช่น ลากลูกค้าลงมาดูคู่กับสินค้าและผู้ขาย เพื่อดูว่ามีลูกค้ากี่คนที่ซื้อสินค้านั้นๆ โดยผู้ขายคนนั้นๆ (ผมกดเปลี่ยน Report Layout เป็น Tabular Form จะได้เห็นชื่อ Field ชัดๆ)

    การใช้ Excel Power Pivot ตอนที่ 1 : การใช้งานพื้นฐาน 18

    จะเห็นว่าตรงช่องที่ผมทาสีเหลือง มัน Count ลูกค้าได้ 27 แบบนี้ หลายคนอาจตีความว่า มีลูกค้า 27 คนที่ซื้อของเล่นที่ขายโดย sales ง รึเปล่า?

    ถ้าเราดับเบิ้ลคลิ๊กที่เลข 27 จะเห็นชัดเลย ว่าลูกค้าที่มันเอามานับนั้นซ้ำกระจาย (แค่เบอร์ 14 ก็ซ้ำไป 3 รอบแล้ว)

    การใช้ Excel Power Pivot ตอนที่ 1 : การใช้งานพื้นฐาน 19

    และถ้าเราอยากจะนับแบบไม่ซ้ำจะทำยังไงล่ะ?

    คำตอบง่ายมาก แค่กลับไปที่ Pivot แล้วเปลี่ยนวิธีสรุปข้อมูลจาก Count ธรรมดาๆ เป็น Distinct Count ก็จบเลย (แต่มันอยู่ใน More Options… นะ)

    การใช้ Excel Power Pivot ตอนที่ 1 : การใช้งานพื้นฐาน 20
    การใช้ Excel Power Pivot ตอนที่ 1 : การใช้งานพื้นฐาน 21

    ซึ่งพอกด ok เราก็จะได้จำนวนลูกค้าแบบไม่ซ้ำกันแล้ว ซึ่ง PivotTable โหมดปกติจะทำอะไรแบบนี้ไม่ได้เลยนะ

    การใช้ Excel Power Pivot ตอนที่ 1 : การใช้งานพื้นฐาน 22

    ซึ่งถ้าเราอยากจะนับคอลัมน์ไหนแบบไม่ซ้ำ ก็ไปปรับที่ตัวนั้นๆ ได้เลย เช่นจะนับว่ามีการขายของนั้นๆ ทั้งหมดกี่วัน ก็เอาวันที่มานับแบบ Distinct Count ได้ หรือจะดูว่ามีการขายครบทุกเดือนมั้ย ก็เอาเดือนมา Distinct Count ได้ ซึ่งเราสามารถใช้ร่วมกับการ Filter หรือ Row/Column ต่างๆ ได้อย่างอิสระเลย

    เช่นในปี 2020 หนังสือมีการขายแค่ 11 เดือน ในขณะที่ตัวอื่นขายครบ 12 เดือน

    การใช้ Excel Power Pivot ตอนที่ 1 : การใช้งานพื้นฐาน 23

    ความเจ๋ง 2 : การทำ Calculated Field ที่ไม่ใช่การ SUM ด้วย Measure

    ปกติแล้วเวลาเราใช้ Calculated Field ใน Pivot Table นั้น สูตรที่เราเขียน มันจะใช้การสรุปผลด้วยการ Sum เท่านั้น ไม่สามารถเปลี่ยนเป็น Count, Max, Min อะไรได้เลย

    แต่ถ้าเราใช้ Mode Data Model แล้ว Calculated Field จะง่อยกว่าเดิม! เพราะมันใช้ไม่ได้เลยเนื่องจากหลายเป็นสีเทาไปแล้ว…

    การใช้ Excel Power Pivot ตอนที่ 1 : การใช้งานพื้นฐาน 24

    แต่ไม่ต้องเสียใจไป ที่มันเป็นสีเทาเพราะมันมีตัวที่เจ๋งกว่าให้ใช้ นั่นก็คือ Measure นั่นเอง (ซึ่งคือตัวเดียวกับ Power BI)

    วิธีการเรียกใช้ Measure ให้คลิ๊กขวาที่ชื่อตารางใน Pivot Field List แล้วกด +Add Measure… (ถ้าคลิ๊กขวาแล้วไม่มีก็ไปเลือกใน Ribbon Power Pivot ตามวิธีถัดไป)

    การใช้ Excel Power Pivot ตอนที่ 1 : การใช้งานพื้นฐาน 25

    นอกจากการกด +Add Measure เราจะไปกดใน Ribbon Power Pivot ก็ได้นะครับ (เมนูจะเจ๋งกว่าด้วย เพราะมีสีสวยงามตอนเขียนสูตร)

    การใช้ Excel Power Pivot ตอนที่ 1 : การใช้งานพื้นฐาน 26

    ในที่นี้ผมจะกดสร้าง Measure ผ่านเมนูของ Power Pivot ละกันนะครับ เพราะมีตัวช่วยเยอะกว่า

    พอกดสร้าง Measure ปุ๊ป จะเห็นช่องให้ใส่สูตร ถ้าเรากดปุ่ม fx มันจะ List ฟังก์ชันที่ใช้ได้ขึ้นมาเพียบเลย ซึ่งไม่ได้มีแค่ SUM แล้วเนอะ

    การใช้ Excel Power Pivot ตอนที่ 1 : การใช้งานพื้นฐาน 27

    ฟังก์ชันที่ใช้ได้นี้เป็นสูตรเฉพาะที่เรียกว่า DAX (Data Analysis eXpression) ซึ่งเป็นภาษาที่ใช้ใน PowerPivot กับ Power BI และพวก Analysis Service ต่างๆ ซึ่งมีความคล้ายกับฟังก์ชันใน Excel ของเรามากเลย หลายๆ ฟังก์ชันที่เรารู้จักใน Excel ก็สามารถนำมาใช้ในนี้ได้ และก็มีหลายๆ ฟังก์ชันถูกใส่เข้ามามากกว่าใน Excel ปกติ เพื่อใช้ในการวิเคราะห์ข้อมูลโดยเฉพาะ

    ยกตัวอย่างเช่น การนับลูกค้าแบบไม่ซ้ำ นอกจากใช้คำสั่ง Distinct Count ใน Summarize Value By… เราก็ยังสามารถใช้ฟังก์ชัน DISTINCTCOUNT ใน DAX มาช่วยได้

    วิธีเขียนสูตรคือ ให้พิมพ์ชื่อฟังก์ชันแล้วกด Tab เลือกเอา มันจะมีตัวช่วยขึ้นมาว่าจะ DISTINCTCOUNT คอลัมน์ไหน

    การใช้ Excel Power Pivot ตอนที่ 1 : การใช้งานพื้นฐาน 28

    เราก็เลือกว่า DISTINCTCOUNT(TXData[ลูกค้า] แล้วอย่าลืมพิมพ์วงเล็บปิดด้วย สรุปได้สูตรนี้

    =DISTINCTCOUNT(TXData[ลูกค้า])

    พอกด ok มันก็จะสร้าง Measure ขึ้นมาใหม่ และ add เข้า Pivot ให้เราเลย (ถ้ามันยังไม่ add ให้ก็ลากมาใส่เองได้)

    การใช้ Excel Power Pivot ตอนที่ 1 : การใช้งานพื้นฐาน 29

    นอกจากการ DistinctCount แล้ว มันยังทำเรื่องต่างๆ ได้อีกมากมาย เช่น เราจะสร้างยอดขายรวมขึ้นมาโดยไม่ต้องมีคอลัมน์ยอดขายในตารางจริง แต่จะใช้คอลัมน์จำนวนชิ้น * ราคาต่อชิ้น แล้ว SUM ด้วย SUMX

    เรียนรู้ SUMX

    SUMX(<table>, <expression>)

    SUMX จะใช้สูตร <expression> ที่ระบุลงไปในแต่ละแถวของตาราง <table> (เรียกว่า Iterate) แล้วสุดท้ายค่อย SUM ซึ่งจะคล้ายๆกับสูตร SUMPRODUCT ใน Excel แต่ยืดหยุ่นกว่ามากๆ

    เราจะให้ <table> เป็นตาราง TXData ของเรานี่แหละ ส่วน <expression> เราจะเอา จำนวนชิ้น * ราคาต่อชิ้น

    ดังนั้นสูตรออกมาจะเป็นดังนี้

    =SUMX(TXData,TXData[ราคาต่อชิ้น]*TXData[จำนวนชิ้น])

    ซึ่งเราสามารถเลือก Number Format ให้ Measure นั้นๆ ได้เลยด้วย

    Power Pivot

    แค่นี้เราก็จะได้ยอดขายรวมแล้ว

    การใช้ Excel Power Pivot ตอนที่ 1 : การใช้งานพื้นฐาน 30

    หายอดขายเฉลี่ยต่อลูกค้า

    ยกตัวอย่างเช่น หากเราต้องการจะแสดงยอดขายเฉลี่ยต่อลูกค้า 1 คน เราก็สามารถเขียนสูตรโดยเอาเลขสรุป 2 ค่ามาหารกัน

    • ให้ตัวเศษ = ยอดขายรวม
    • ตัวส่วน = จำนวนลูกค้า(แบบไม่ซ้ำ)

    ซึ่งความเจ๋งของ Measure คือ มันอ้างอิง Measure ที่มีอยู่แล้วได้ ด้วยการใส่ [ชื่อMeasure]

    ดังนั้นเราจะเขียนสูตรแบบนี้ได้เลย

    =[TotalSales] / [จำนวนลูกค้าแบบไม่ซ้ำ]
    การใช้ Excel Power Pivot ตอนที่ 1 : การใช้งานพื้นฐาน 31
    การใช้ Excel Power Pivot ตอนที่ 1 : การใช้งานพื้นฐาน 32

    และนี่ก็คือตัวอย่างของการเริ่มหัดใช้ Power Pivot แบบง่ายๆ เดี๋ยวตอนต่อไปจะเริ่มซับซ้อนขึ้นแล้ว

    ถ้าอยากปูพื้นฐานก่อน…

    ถ้าใครอ่านบทความนี้แล้วรู้สึกว่าอยากจะมีความรู้ PivotTable ให้ดีกว่านี้ เพราะยังไม่เข้าใจบางจุด ก็สามารถไปศึกษาคอร์สออนไลน์ของผมได้นะครับ ซึ่งดูตอนไหนก็ได้ กี่รอบก็ได้ ไม่มีหมดอายุ แถมยังอัปเดทเนื้อหาให้เรื่อยๆ อีก (ขอโฆษณาซะหน่อย 555)

    คอร์สออนไลน์จากเทพเอ็กเซล : Online Course

    ตอนต่อไป

    จะมาดูวิธีทำให้ช่อง Value ของ Pivot สามารถแสดงข้อความได้กันครับ

    สารบัญซีรีส์ Power Pivot

  • แนะนำคอร์สออนไลน์สำหรับผู้ที่อยาก Level Up ทักษะ Excel

    แนะนำคอร์สออนไลน์สำหรับผู้ที่อยาก Level Up ทักษะ Excel

    เนื่องจากช่วงนี้ COVID-19 ยังคงระบาดอยู่ คอร์สเรียน Excel แบบ Online จึงเป็นวิธีที่น่าสนใจมากขึ้น แต่ผมเองก็มีคอร์สออนไลน์อยู่ในเว็บ SkillLane ด้วยหลายตัว (และจะเพิ่มขึ้นเรื่อยๆ) จึงอยากจะมาแนะนำว่าแต่ละอันสอนอะไร? และเหมาะกับใคร?

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

    คอร์สออนไลน์ Excel Level Up : Update 2020

    จุดเด่น

    • รวมเนื้อหาพื้นฐานที่จำเป็นที่สุดของ Excel เข้าไว้ด้วยกันในคอร์สเดียว (12 ชั่วโมงครึ่ง) เช่น
      • ปูพื้นฐานแต่ต้น รวมถึงการจัดการข้อมูลวันที่และเวลา / การจัดการข้อความ
      • เครื่องมือต่างๆ เช่น Sort/Filter, Flash Fill, Conditional Format, Data Validation, Goal Seek
      • สอนแนวคิด แนวทางการเขียนสูตรซ้อนกัน ทำยังไงไม่ให้งง
      • ฟังก์ชันยอดนิยม เช่น IF / VLOOKUP / INDEX / MATCH / SUMIFS
      • การใช้ Table และ Pivot Table เพื่อสร้าง Dashboard
    • จ่ายเงินครั้งเดียวได้คอร์สที่เนื้อหาอัปเดทตลอดชีวิต (ปรับปรุงอย่างน้อยปีละ 1 ครั้ง)
      • คนที่เคยซื้อคอร์ส Excel Level Up Revised ไปแล้ว จะสามารถเข้าไปดูคอร์สใหม่นี้ได้ฟรีเช่นกัน
    • ราคา 1,490 บาท รับรองว่าได้เนื้อหาเน้นๆ ไม่มีน้ำ ตลอด 12 ชั่วโมงครึ่งอย่างแน่นอน

    คอร์สออนไลน์ เทพเรื่องข้อมูล ต้องเก่ง Pivot Table

    จุดเด่น

    • เน้นเรื่องการใช้ Table และ Pivot Table โดยเฉพาะ (เนื้อหาเหมือนกับในคอร์ส Excel Level Up)
    • ราคาย่อมเยาเพียง 490 บาท
    • เรียนจบเร็ว ใช้เวลาน้อยมาก (2 ชั่วโมงครึ่ง) แต่รับรองว่าเก่งขึ้นมาก
    • เหมาะกับการเตรียมตัวเพื่อต่อยอดไปใช้ Power Query และ Power BI ในอนาคตได้

    คอร์สออนไลน์ Excel Power Up 2021 : พลังแห่งข้อมูล สร้างได้ด้วย Power Query

    จุดเด่น

    • คอร์สนี้เป็น version อัปเดทเนื้อหาให้เข้าใจง่ายขึ้นครับ (คนที่เคยลงอันเก่าแล้ว สามารถเข้าเรียนอันนี้ได้ฟรี)
    • เพื่อนๆ ก็จะได้เรียนรู้การใช้เครื่องมือ Power Query ซึ่งมีประโยชน์ในการรวบรวมและดัดแปลงข้อมูลให้อยู่ในรูปแบบที่เหมาะสม ซึ่งมีความสามารถหลักดังนี้
      • ช่วยรวบรวมข้อมูลได้หลายรูปแบบ ทั้ง จากหลายๆ ชีท หลายๆ ไฟล์ หลายๆ format
      • ดัดแปลงข้อมูลให้มีหน้าตาเหมาะต่อการนำไปใช้งานต่อ เช่น เอาไปวิเคราะห์ต่อด้วย PivotTable ปกติหรือ Data Model
      • เวลาจะทำซ้ำ สามารถกด Refresh ปุ่มเดียว แปลว่าเหมาะกับงาน Routine มากๆ
    • Power Query เป็นเครื่องมือที่เป็นส่วนหนึ่งของ Excel Version 2016 ขึ้นไป และมีใน Power BI ด้วย ดังนั้นเรียนรู้ทีเดียวจะใช้ได้ 2 โปรแกรมเลย (ถ้า Excel Version 2013 ขึ้นไปต้องไปโหลด Add-in เพิ่ม)
    • ในคอร์ส Power Query นี้จะเน้นเรื่อง Power Query โดยเฉพาะ ซึ่งจะมีเนื้อหาที่ลงลึกกว่า Section Power Query Editor ที่อยู่ในคอร์ส Power BI นะครับ
    • Scope เนื้อหาในคอร์สนี้จะคล้ายกับหนังสือ Excel Power Up แต่จะมีการยกตัวอย่างการประยุกต์ใช้จริงมากกว่าหนังสือครับ
    • พิเศษ! ช่วงนี้ลดราคาจาก 1590 เหลือ 1,190 เท่านั้น (หมดเขต 19 มีค. 64)

    คอร์สออนไลน์ Powerful Data with Power BI

    จุดเด่น

    • คุณจะได้เรียนรู้เครื่องมือ Power BI ซึ่งเป็นเครื่องมือ Business Intelligent ชั้นยอดจากค่าย Microsoft
    • ใช้ทำ Interactive Dashboard ที่มี Visual หรือกราฟต่างๆ ที่เจ๋งกว่า Excel หลายเท่า
    • มีความสามารถในการแชร์ Dashboard ไปให้คนอื่นใช้ได้ง่าย และมีความปลอดภัยสูง
    • ในคอร์สนี้คุณจะได้เรียนรู้ทั้ง 3 แกนหลักของโปรแกรมนี้ ทั้ง
      • การ Get/Transform Data ด้วย Query Editor (เนื้อหาเทียบเท่าบางส่วนของคอร์ส Power Query) โดยจะเน้นแค่การดัดแปลงข้อมูลเบื้องต้นให้เข้าใจ Concept และแก้ปัญหาทั่วๆ ไปเท่านั้น
      • การทำ Data Model และเขียนสูตร DAX ในระดับที่ใช้งานทั่วไปได้
        • เรียนรู้ Data Model แบบพื้นฐาน และแบบที่รองรับการทำ Data Actual vs Budget ได้
        • สูตร DAX จะสอนนี้ก็จะลงลึกถึง CALCULATE และ Time Intelligent เช่น การคำนวณเทียบยอดในช่วงเดียวกันของปีที่แล้ว เป็นต้น
        • DAX ขั้นสูงจะทำแยกเป็นคอร์สต่างหากอีกอัน ไม่ได้อยู่ในคอร์สนี้ เพราะจะซับซ้อนเกินไป
      • การสร้าง Report ด้วยการสร้างกราฟและปรับแต่ง Visual ต่างๆ รวมถึงการทำให้เกิดความ Interactive มากขึ้น เช่น การปรับ Interaction, การ Drill แบบต่างๆ, Report Tool Tipsเป็นต้น
    • ราคา 2,290 บาท (อัปเดทเนื้อหาให้ฟรีทุกปี)

    คอร์สออนไลน์ : สร้างสูตรมหัศจรรย์ขั้นเทพ ด้วย Array Formula

    จุดเด่น

    • เน้นการเขียนสูตรขั้นสูง และการใช้ Array Formula เหมาะกับคนที่อยากแก้ปัญหาด้วยการใช้สูตร ซึ่งดีตรงที่อัปเดทอัตโนมัติ ไม่ต้อง Refresh แบบ Pivot
    • Scope เนื้อหาของคอร์สนี้เดิมจะคล้ายกับหนังสือจอมเวทเทพเอ็กเซลนะครับ
    • คุณจะเข้าใจการทำงานของ Array Formula อย่างลึกซึ้ง
    • เรียนรู้ Feature ใหม่ของ Excel 365 ที่เรียกว่า Dynamic Array
    • สร้างการคำนวณตามเงื่อนไขได้ตามต้องการ เช่น การคำนวณ PERCENTILE + IF
    • การ Filter ข้อมูล / Lookup ข้อมูลหลายค่า
    • การใช้ Match แบบ Advance เช่น หาค่าตัวสุดท้าย
    • การใช้ SUMPRODUCT, AGGREGATE
    • การสร้าง Dynamic Range ด้วย INDEX
    • การใช้ INDIRECT OFFSET
    • การดัดแปลงหน้าตาโครงสร้างของข้อมูลด้วยสูตร
    • หาว่ามีวันที่ตรงตามเงื่อนไขจำนวนกี่วัน ระหว่างวันเริ่ม-วันจบที่กำหนด
    • การใช้ฟังก์ชัน FREQUENCY
    • การแก้สมการหลายตัวแปรด้วย Matrix
    • การเลียนแบบ Pivot Table ด้วยสูตรแบบ Dynamic Array
    • ราคา 990 บาท

    หากต้องการซื้อหลายคอร์สพร้อมกัน ซื้อแบบคอร์ส Package จะประหยัดกว่า

    สรุปเลือกคอร์สไหนดี?

    ถ้าคุณคิดว่าเป็นคนที่พื้นฐาน Excel ok แล้ว อาจจะขาดแค่เรื่อง Pivot Table หรือคนที่อยากจะมุ่งเตรียมตัวไปใช้ Power Query / Power BI แบบด่วนๆ ผมก็แนะนำคอร์ส นะครับ เพราะเรียนจบเร็วดี (2 ชั่วโมงครึ่ง) แถมประหยัดด้วยแค่ 490 บาทเท่านั้น

    แต่ถ้าคุณอยากได้เนื้อหา Excel แบบครบจริงๆ และอยากรู้เรื่องที่สำคัญให้ครบ ผมก็แนะนำคอร์ส นี่แหละครับ ยิ่งตอนนี้ลดราคาอยู่เหลือแค่ 1,090 เท่านั้นเอง (จาก 1,490) เรียกได้ว่าจ่ายแพงขึ้นอีกนิด แต่ได้เนื้อหาเยอะขึ้นมากเลย (2 ชั่วโมงครึ่งของ Pivot เทียบกับ 12 ชั่วโมงครึ่งของ Excel Level Up)

    • ส่วนนี่แนะนำมากๆ เหมาะกับคนที่ต้องรวบรวมข้อมูลมาวิเคราะห์ และใช้ได้กับทั้ง Excel และ Power BI เลย
    • และถ้าใครอยากทำ Interactive Dashboard แบบเจ๋งๆ ก็แนะนำให้เรียน ครับ รับรองว่าเจ๋งกว่า Excel หลายเท่า
    • ส่วนใครยังหลงรักการเขียนสูตร Excel อยู่ และอยากเทพเรื่องการเขียนสูตร พลาดไม่ได้กับ

    ใครรอคอร์สไหนอยู่ก็ Comment บอกได้นะครับ จะไม่มีในนี้ก็ได้นะ เผื่อผมจะได้ลองทำมาให้เพื่อนๆ ได้เรียนกัน

  • เลิกใช้ Merge Cell กันเถอะ (แล้วจะให้ทำยังไงแทน?)

    เลิกใช้ Merge Cell กันเถอะ (แล้วจะให้ทำยังไงแทน?)

    “มาเลิกใช้ Merge Cell กันเถอะ!” นี่คือความในใจที่อยากจะตะโกนบอกคนใช้ Excel ทุกคนเลย 555

    แต่ผมไม่ได้มาห้ามเฉยๆ นะ เพราะในบทความผมมีวิธีหลีกเลี่ยงที่พอจะช่วยลดปัญหาของ Merge Cell มาบอกด้วยล่ะ

    หากผมจะบอกว่า “การ Merge Cell คือต้นกำเนิดของความเลวร้ายทั้งปวงใน Excel” มันอาจจะดูแรงเกินไปนิด แต่มันก็ใกล้เคียงล่ะ มาดูกันว่าทำไม…

    ปัญหาของการ Merge Cell

    หากเรามีการใช้ Merge Cell ที่ตัวข้อมูลดิบที่เป็น Input จะพบว่ามันก่อให้เกิดปัญหามากมาย

    Merge Cell
    • Merge แบบหัวตาราง 2 ชั้น (กรอบชมพูในรูปบน)
      • ทำให้เลือก Sort/Filter หัวตารางลำบาก ต้องเลือกทั้งแถวที่สองแล้วกด Filter เอาถึงจะเลือกหัวตารางชั้นล่างได้ ซึ่งยุ่งยากมาก
        เลิกใช้ Merge Cell กันเถอะ (แล้วจะให้ทำยังไงแทน?) 33
      • เอาข้อมูลเข้าไปวิเคราะห์ใน Pivot Table ก็ไม่ได้อีก (เพราะถือว่ามีชื่อ Field ว่าง)
        เลิกใช้ Merge Cell กันเถอะ (แล้วจะให้ทำยังไงแทน?) 34
    • การ Merge ที่ Data ข้างใน (กรอบแดงในรูปบน) จะทำให้
      • ไม่สามารถ Sort/Filter ข้อมูลที่มีการ Merge Cell ได้เลิกใช้ Merge Cell กันเถอะ (แล้วจะให้ทำยังไงแทน?) 35
    • Copy Paste ข้อมูลลงบน Merge Cell โดยที่ขนาดไม่เท่าเดิมก็ไม่ได้
      เลิกใช้ Merge Cell กันเถอะ (แล้วจะให้ทำยังไงแทน?) 36
    • แถมยังเป็นการเว้นช่องว่างเอาไว้ในฐานที่มนุษย์เข้าใจแต่คอมพิวเตอร์ไม่เข้าใจ (Computer เข้าใจว่าข้อมูลอยู่ที่ช่องซ้ายบนเท่านั้น นอกนั้นเป็นช่องว่าง)
    • เลิกใช้ Merge Cell กันเถอะ (แล้วจะให้ทำยังไงแทน?) 37
    • การเขียนสูตรเพื่อเลือกข้อมูลช่องที่ถูก Merge แต่ไม่ใช่ช่องซ้ายบนก็ยากอีก

    ทำไมคนยังใช้ Merge Cell?

    จะเห็นว่าปัญหามากมายนั้นเกิดจากการใช้ Merge Cells แต่ทำไมคนถึงใช้มันอีกล่ะ??

    คำตอบที่ผมพบจากการสอบถามเพื่อนๆ ในเพจ สามารถสรุปเหตุผลได้สั้นๆ ก็คือ เป็น “เพราะมันสวยดี”

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

    ผมเชื่อว่า คนที่ใช้ Merge Cell ส่วนใหญ่ไม่รู้ด้วยว่ามันจะส่งผลให้มีปัญหาในอนาคตแค่ไหน เพราะอาจจะไม่ค่อยเข้าใจการใช้ Sort/Filter/Table/Pivot Table

    ทำไม Excel ถึงไม่ยอมให้เรา Sort/Filter/Pivot ข้อมูลที่ Merge?

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

    เวลาพูดว่าอย่า Merge Cell ที่ห้ามจริงๆ หมายถึงตัว Data Input ไม่ใช่ Report Output

    หลักการที่ถูกต้องคือ แยกข้อมูลส่วน Input กับ Output ออกจากกันซะ ส่วน Input ที่เป็นข้อมูลดิบ ให้ทำให้อยู่ในลักษณะ Database ให้มากที่สุด (แต่ถ้ากรอกไม่ไหวจริงๆ ก็ใช้ Power Query ช่วย Fill Down/Unpivot แปลงหน้าตาได้ ถ้าทำเป็น) ส่วน Output ถ้าอยากจะ Merge ก็แล้วแต่ ไม่มีใครว่า (ยกเว้นวันจะต้องไปเป็น input ของชาวบ้านอีกที)

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

    วิธีที่พอจะช่วยหลีกเลี่ยง Merge Cell (ถ้ายังอยากทำ)

    กรณีจะ Merge แนวนอน ให้ใช้ Center Across Selection แทน

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

    ดังนั้นหากคุณต้องการจะ Merge Cell แค่แถวเดียว ให้เปลี่ยนมาใช้ Center Across Selection จะดีกว่ากันมากๆ เลย

    วิธีทำก็แค่เลือกข้อมูล แบบที่ยังไม่ต้องMerge

    เลิกใช้ Merge Cell กันเถอะ (แล้วจะให้ทำยังไงแทน?) 38

    จากนั้นก็คลิ๊กขวา Format Cell หรือกด Ctrl+1 ก็ได้

    จากนั้นเลือก Alignment -> Horizontal : Center Across Selection

    เลิกใช้ Merge Cell กันเถอะ (แล้วจะให้ทำยังไงแทน?) 39

    ผลที่ได้จะเหมือนกับการ Merge Cell เลย แต่ Cell ไม่ได้ Merge (เห็นมะว่ายังคลิ๊กเลือก cell แยกกันได้) ส่วนข้อมูลจริงจะยังคงอยู่ที่ช่องซ้ายสุดของพื้นที่ช่องเดียวเหมือนเดิม

    เลิกใช้ Merge Cell กันเถอะ (แล้วจะให้ทำยังไงแทน?) 40

    ดังนั้นสรุปว่าถ้าจะ Merge Cell แนวนอนจริงๆ ก็ให้ใช้ Center Across Selection แทนนะ แต่ถ้าเลือกได้จริงๆ ก็อย่าไปทำมันเลยทั้งคู่นั่นแหละ ให้พิมพ์ข้อมูลให้ครบทุกช่องดีกว่า เพราะอย่างที่บอกไปว่าการ Merge ข้อมูลมันไม่ได้อยู่ครบทุกช่อง ไอ้เจ้า Center Across Selection ก็มีข้อมูลอยู่แต่ช่องซ้ายสุดเหมือนกัน เดี๋ยวก็มีปัญหาอีก

    กรณีจะ Merge แนวตั้ง ให้พิมพ์ให้ครบทุกช่องเถอะ ไม่มีทางเลือก

    การ Merge แนวตั้ง ใช้ Center Across Selection ไม่ได้นะ และไม่น่าจะมีวิธีที่ดีไปกว่านี้แล้วล่ะ เสียใจด้วย

    กรณี Merge หัวตาราง พอมีทางช่วยได้

    กรณีที่เรามีหัวตาราง 2 ชั้น มันทำให้ใช้ PivotTable ไม่ได้โดยสิ้นเชิง ดังนั้นวิธีแก้ที่ดีที่สุดคือ ทำหัวตารางให้มีแค่ชั้นเดียวพอ

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

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

    เลิกใช้ Merge Cell กันเถอะ (แล้วจะให้ทำยังไงแทน?) 41

    จากนั้น Copy Paste Value ซะแล้วแก้บางคอลัมน์ที่อ่านไม่รู้เรื่องให้ถูกต้อง (ตัวที่ Merge ทั้งหลาย)

    เลิกใช้ Merge Cell กันเถอะ (แล้วจะให้ทำยังไงแทน?) 42

    จากนั้นพอจะใช้งานจริง แล้วค่อยเลือกที่ช่อง C3 แล้วสั่ง Freeze Pane ซะแบบนี้

    เลิกใช้ Merge Cell กันเถอะ (แล้วจะให้ทำยังไงแทน?) 43

    พอ Scroll Mouse ลงมา ไอ้ส่วนหัวตารางสองชั้นสวยๆ ของเราก็จะยังอยู่

    เลิกใช้ Merge Cell กันเถอะ (แล้วจะให้ทำยังไงแทน?) 44

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

    เลิกใช้ Merge Cell กันเถอะ (แล้วจะให้ทำยังไงแทน?) 45

    เอายังไงกับตัวสรุปรวมท้ายตาราง

    หากเราต้องการสรุปข้อมูลจริงๆ ควรจะใช้ Pivot Table ทำมากกว่าการมาเขียนสูตรเอาท้ายตาราง แต่ถ้าต้องการให้มีการสรุปข้อมูลอยู่ที่ท้ายตารางจริงๆ ผมแนะนำให้ใช้ความสามารถของ Table ครับ

    ก่อนอื่นให้เลือกข้อมูลช่องใดช่องหนึ่งแล้วกด Insert->Table หรือ Ctrl+T เพื่อแปลงข้อมูลเป็น Table ซะ ซึ่งตอนแรกมันจะยังไม่มีตัวสรุปท้ายตารางให้

    เลิกใช้ Merge Cell กันเถอะ (แล้วจะให้ทำยังไงแทน?) 46

    ถ้าอยากให้มีสรุปท้ายตาราง แค่ติ๊กเลือก Total Row ตามรูป ก็จะมีบรรทัดสรุปโผล่มา

    เลิกใช้ Merge Cell กันเถอะ (แล้วจะให้ทำยังไงแทน?) 47

    จากนั้นเราสามารถเลือกแต่ละคอลัมน์ได้ว่าจะให้สรุปด้วยวิธีไหน เช่น SUM COUNT AVERAGE MAX MIN

    เลิกใช้ Merge Cell กันเถอะ (แล้วจะให้ทำยังไงแทน?) 48

    เราได้ตารางที่หน้าตาไม่ได้แย่เลย มีหัวตาราง 2 ชั้นเหมือนเดิม มีบรรทัดสรุป แค่เนื้อในต้องพิมพ์ให้ครบทุกช่องเท่านั้นเอง เพื่อที่จะได้ Sort/Filter/Pivot ได้ตามปกติครับ

    เลิกใช้ Merge Cell กันเถอะ (แล้วจะให้ทำยังไงแทน?) 49

    และทั้งหมดนี้ก็คือเทคนิค ที่พอจะช่วยหลีกเลี่ยงการทำ Merge Cell ได้นะครับ ถ้าทำตามนี้รับรองว่าชีวิตดีขึ้นแน่นอน ^^

  • Power BI ตอนที่ 02: พื้นฐาน Excel ที่สำคัญก่อนจะเรียนรู้ Power BI

    Power BI ตอนที่ 02: พื้นฐาน Excel ที่สำคัญก่อนจะเรียนรู้ Power BI

    จะเข้าใจ Power BI ได้ พื้นฐาน Excel ที่สำคัญก่อนจะเรียนรู้ Power BI ในความคิดเห็นส่วนตัวของผมก็คือ เราควรจะมีความเข้าใจเรื่อง PivotTable ใน Excel ซะก่อน

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

    ข้อมูลตัวอย่าง

    สมมติผมมีข้อมูลดิบเป็น Table อันนี้ แล้วเอาเข้าไปสรุปใน PivotTable โดยคลิ๊กที่ Table แล้วกด Insert Pivot Table แล้วกด ok

    ข้อมูล 1 บรรทัดคือการขายของ 1 Transaction ซึ่งมีสินค้าได้ 1 ประเภท แต่สามารถขายได้หลายชิ้น ดังนั้นยอดขายต่อ Transaction จึงเท่ากับ ราคาต่อชิ้น * จำนวนชิ้น

    Power BI ตอนที่ 02: พื้นฐาน Excel ที่สำคัญก่อนจะเรียนรู้ Power BI 50

    เรื่อง Basic ที่เราควรรู้คือ

    • หัวตารางทุกอันจะกลายมาเป็น Field List ให้เราเลือก
    • ข้างใต้ Field List จะมีพื้นที่ 4 Block อยู่ หน้าที่เราคือลาก Field ลงมาที่ Block เหล่านั้น
    • หากลาก Field ที่มาที่ Value
      • กรณีเป็นตัวเลขจะเกิดการ Sum โดยอัตโนมัติ
      • กรณีเป็นข้อความจะเกิดการ Count โดยอัตโนมัติ
      • สามารถเปลี่ยนวิธีสรุปการคำนวณจาก Sum เป็นอย่างอื่นได้ ด้วยการคลิ๊กขวา Summarized Values By => Count, Average, Max, Min…
    • หากลาก Field ไปไว้ที่ Row มันจะมีการแจกแจงข้อมูลใน Field นั้นแบบไม่ซ้ำกันโดยแยกกันอยู่คนละแถว
    • หากลาก Field ไปไว้ที่ Column มันจะมีการแจกแจงข้อมูลใน Field นั้นแบบไม่ซ้ำกันโดยแยกกันอยู่คนละคอลัมน์
    • หากลาก Field ไปที่ Filter ก็จะสามารถ Filter ตามสิ่งที่ต้องการได้ ซึ่งจะส่งผลทั้งตาราง (อย่างไรก็ตามการลาก Field ไว้ที่ Row/Column ก็ Filter ได้เช่นกัน)

    ตัวอย่างการสรุปข้อมูลง่ายๆ

    สมมติว่าต้องการสรุปว่า Sales แต่ละคน ขายสินค้าแต่ละประเภท ได้ยอดขายรวมเฉพาะการชำระเงินแบบเงินสดรวมกี่บาท? สิ่งที่เราทำได้ คือ

    • ลากยอดขายไป Value (จะ Sum อัตโนมัติ)
    • ลากผู้ขายไปที่ Rows (จะแยกผู้ขายแบบไม่ซ้ำกันออกเป็นคนละแถว)
    • ลากสินค้าไปที่ Columns (จะแยกสินค้าแบบไม่ซ้ำกันเป็นคนละคอลัมน์)
    • ลากวิธีชำระเงินไป Filter แล้วเลือก เงินสด (จะสนใจเฉพาะข้อมูลแบบเงินสด)
    Power BI ตอนที่ 02: พื้นฐาน Excel ที่สำคัญก่อนจะเรียนรู้ Power BI 51

    การตีความตัวเลขที่แสดงมา

    ตรง cell ที่ผมเลือกไว้ แปลว่า sales ค ขายของเล่น ด้วยวิธีชำระเงินแบบเงินสด ได้ยอดขายรวม 1100 บาท นี่คือการตีความเชิง Business

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

    จะเห็นว่ามันสร้าง sheet ใหม่โดย Filter ข้อมูลตามที่ผมบอกไปเลย แล้วค่อยทำการ Sum ยอดขายที่เหลือจากการ Filter ซึ่งจะได้ 1100 บาทพอดี

    Power BI ตอนที่ 02: พื้นฐาน Excel ที่สำคัญก่อนจะเรียนรู้ Power BI 52

    พูดง่ายๆ คือมัน Filter ให้ครบทุกมิติก่อน จากนั้นค่อยคำนวณตามวิธีที่ระบุใน Values จากนั้นจึงค่อยแสดงค่าออกมานั่นเอง ซึ่ง Concept นี้สำคัญสุดๆๆๆๆๆๆ !!!

    Filter Context (บริบทการ Filter)

    ดังนั้นแปลว่า ค่าแต่ละช่องของตาราง Pivot ที่แสดงออกมา จริงๆ แล้วการคำนวณมีเงื่อนไขการ Filter ไม่เหมือนกันเลยซักช่อง เช่น

    Power BI ตอนที่ 02: พื้นฐาน Excel ที่สำคัญก่อนจะเรียนรู้ Power BI 53
    • ตำแหน่งเลข 1 คือ ผู้ขาย=sales ค, สินค้า=ของเล่น, วิธีชำระเงิน=เงินสด
    • ตำแหน่งเลข 2 คือ สินค้า=ของเล่น, วิธีชำระเงิน=เงินสด
    • ตำแหน่งเลข 3 คือ ผู้ขาย=sales ค, วิธีชำระเงิน=เงินสด
    • ตำแหน่งเลข 4 คือ วิธีชำระเงิน=เงินสด

    การที่แต่ละช่องอยู่ภายใต้เงื่อนไขการ Filter ที่ต่างกัน เรียกว่ามี Filter Context ที่ต่างกัน ซึ่งคำว่า Filter Context นั้นเป็น Concept ที่สำคัญสุดๆของ Power BI แล้ว

    การเข้าใจ Filter Context จะทำให้เข้าใจวิธีการคำนวณดีขึ้น

    สมมติผมคำนวณยอดขายเฉลี่ย ของสินค้าแต่ละประเภท โดยเลือกยอดขายแล้วสั่งเปลี่ยนวิธีสรุป Summarized Values by Average แทนการ Sum จะได้ดังนี้

    Power BI ตอนที่ 02: พื้นฐาน Excel ที่สำคัญก่อนจะเรียนรู้ Power BI 54

    ถามว่าค่า Average ยอดขายของของเล่น 367.4528 แปลว่าอะไร? มันก็คือยอดขายเฉลี่ยต่อ 1 Transaction กรณีที่สินค้าคือของเล่นนั่นเอง (หรือพูดว่า Filter สินค้าเป็นของเล่น แล้วค่อยหาค่าเฉลี่ยยอดขาย) อันนี้ทุกคนน่าจะตอบได้

    แต่ถ้าถามว่ายอดเฉลี่ยที่ Grand Total ที่แสดงออกมาเป็น 428.186 แปลว่าอะไร? หลายคนอาจจะตอบผิด!!

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

    ความหมายของ ยอดเฉลี่ยที่ Grand Total 428.1866 แปลว่ายอดขายเฉลี่ยต่อ 1 Transaction กรณีที่ไม่ได้มีการ Filter สินค้าอะไรเลยต่างหากล่ะ!

    เพราะการคำนวณที่ Grand Total (กรณีที่ไม่ได้มี Filter มาจากหัว Filter หรือ Slicer อื่น) แปลว่ามันไม่มี Filter อะไรอยู่เลย ดังนั้นเมื่อ Average ยอดขายจึงจะได้ 428.186

    Power BI ตอนที่ 02: พื้นฐาน Excel ที่สำคัญก่อนจะเรียนรู้ Power BI 55

    ก่อนจะจากลา

    และนี่คือ Concept สำคัญที่จะทำให้เราเข้าใจการทำงานของการคำนวณใน Power BI มากขึ้น สำหรับบทความนี้ขอพอแค่นี้ก่อนดีกว่า เดี๋ยวไว้ต่อตอนหน้ากันครับ

    เหมือนเดิม ใครมีคำถามอะไร ถามทิ้งไว้ได้เลย!!

    สารบัญ Series Power BI

    ใครสนใจอยากเรียนเป็นคลิปวีดีโอ ผมมี

  • วิธีใช้ Slicer แทน Drop down หลายชั้น

    วิธีใช้ Slicer แทน Drop down หลายชั้น

    ผมเคยได้อธิบายวิธีทำ Drop down หลายชั้นไปแล้ว คราวนี้เราจะมาลองใช้ Slicer ทำแบบนั้นกันบ้างครับ ซึ่งตอน post แบบน้ำจิ้มไว้ใน Facebook ผมแสดงเทคนิคที่ต้องใช้ Excel 2013 ขึ้นไปดังนี้ แล้วบอกว่าจะเขียนวิธีทำแบบละเอียดให้

    slicer drop down

    แต่เผอิญวันนี้ผมมาต่างจังหวัด ในคอมมันมีแต่ Excel 2010 …

    ดังนั้นเดี๋ยวผมจะเปลี่ยนวิธีมาใช้แบบที่ Excel 2010 ขึ้นไปทำได้ครับ (แต่มันไม่เจ๋งเท่า Excel 2013)

    วิธีใช้ Slicer แบบที่ Excel 2010 ทำได้

    ก่อนอื่นให้เตรียมตารางความสัมพันธ์ของข้อมูลเอาไว้ก่อน เช่น

    วิธีใช้ Slicer แทน Drop down หลายชั้น 56

    จากนั้นกด Convert เป็น Table (Ctrl+T) เดี๋ยวเราจะเอาไป Pivot ครับ

    วิธีใช้ Slicer แทน Drop down หลายชั้น 57

    แล้วเราก็เอาไป Pivot แล้วเอา ทุก Field ไปไว้ที่ Row Label

    วิธีใช้ Slicer แทน Drop down หลายชั้น 58

    เปลี่ยน Layout เป็น Tabular Form (อยู่ใน Ribbon Design)

    วิธีใช้ Slicer แทน Drop down หลายชั้น 59

    เลือก Do not show subtotal

    วิธีใช้ Slicer แทน Drop down หลายชั้น 60

    คราวนี้ใส่ Slicer ทั้ง 3 อัน

    วิธีใช้ Slicer แทน Drop down หลายชั้น 61

    ลองเลือกดู ก็จะเห็นว่ามันทำงานได้แล้ว แต่มันดันแสดง Item ที่ไม่เกี่ยวข้องอยู่ แต่จะเป็นสีเทาๆ แล้วจะแสดงอยู่ท้ายๆ (ก็ไม่แย่มากนะผมว่า ^^ )

    วิธีใช้ Slicer แทน Drop down หลายชั้น 62
    วิธีใช้ Slicer แทน Drop down หลายชั้น 63

    ซึ่ง Excel 2013 ขึ้นไป จะสามารถไปที่ Slicer Setting แล้วซ่อน Item ที่ไม่เกี่ยวข้องได้

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

    โดยผมแนะนำให้ Duplicate  Style เดิมที่คุณชอบออกมา แล้ว Modify แก้ Style ของ Selected Item with no Data กับ Unselected item with no data ให้กลายเป็นสีขาวไปเลยก็ได้ 555

    วิธีใช้ Slicer แทน Drop down หลายชั้น 64

    จากนั้นลองเล่นดู แค่นี้ก็ดูเนียนแล้ว 5555

    วิธีใช้ Slicer แทน Drop down หลายชั้น 65
    วิธีใช้ Slicer แทน Drop down หลายชั้น 66

    อย่าลืมนะครับ ถ้าถูกใจช่วยบอกต่อด้วยนะ ^^

  • วิธีทลายข้อจำกัดหน้าตาของ PivotTable ด้วย OLAP CUBE Formula

    วิธีทลายข้อจำกัดหน้าตาของ PivotTable ด้วย OLAP CUBE Formula

    จากที่ผมได้ Post ถามหยั่งเชิงใน Facebook ไปว่าใครสนใจวิธีทำลายข้อจำกัดเรื่องหน้าตาของ PivotTable บ้าง? ปราฏว่ามีคนให้ความสนใจเยอะมากๆ ดังนั้นผมเลยขอมาเขียนบทความอธิบายให้เล็กน้อยครับ

    PivotTable นั้นสร้างง่ายมาก แต่จัด Layout ไม่ได้ดั่งใจ

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

    แต่สิ่งที่มักจะเกิดขึ้นตามมาคือ คำพูดของหัวหน้าที่บอกว่า “รายงานนี้มันหน้าตาไม่สวยเลย ช่วยจัด xxx เว้นบรรทัด yyy แทรกคอลัมน์ zzz หน่อยสิ”

    แล้วคุณก็คงตอบกลับไปแบบสุภาพว่า “อ๋อ ทำไม่ได้ครับ…มันเป็นข้อจำกัดของ PivotTable ครับหัวหน้า” ทั้งๆ ที่จริงๆ อาจอยากตะโกนตอบกลับไปว่า “PivotTable มันแทรกบรรทัดไม่ได้เฟ้ย!”

    วิธีทลายข้อจำกัดหน้าตาของ PivotTable ด้วย OLAP CUBE Formula 67

    วิธีจัด Layout ที่อาจพอรู้กันอยู่แล้ว

    แต่ถ้าคุณอยากจะทำให้รายงานมันสวยได้ดั่งใจจริงๆ ก็มีวิธีที่หลายคนอาจจะรู้อยู่แล้ว ดังนี้

    1. Copy Pivot แล้ว Paste Value เพื่อเอาไปทำรายงาน : วิธีนี้ไม่แนะนำเป็นอย่างยิ่ง เพราะเสียความสามารถในการ Refresh ข้อมูลไปเลย ควรทำเฉพาะกรณีที่คิดว่าทำครั้งเดียวในชีวิต ไม่ต้องทำอีกแล้ว 555
    2. ใช้ GETPIVOTDATA : ปกติเวลากด = แล้วคลิ๊กไปในพื้นที่ Pivot แต่ละ Cell มันจะดึงข้อมูล Cell นั้นมา โดยสร้างสูตรยาวๆ ที่ขึ้นต้นด้วยคำว่า GETPIVOTDATA มาให้ด้วย
      วิธีทลายข้อจำกัดหน้าตาของ PivotTable ด้วย OLAP CUBE Formula 68
      • ซึ่งข้อดีคือ ไม่ว่า Pivot Table ต้นทางจะย้ายไปไหน หรือมีการ Filter Row/Column Label บางอย่างจนข้อมูลเลื่อนไปที่อื่น เจ้า GETPIVOTDATA จะยังเอาข้อมูลที่ถูกต้องมาให้
      • ข้อเสียคือ จะต้องกด = จิ้มทีละ Cell หรือเขียนสูตรทีละ Cell ซึ่งลำบากมากกว่าจะได้ครับ
    3. กด = แล้วจิ้ม แบบ Cell Reference ปกติ : บางคนไม่ชอบ GETPIVOTDATA เลยไปเลือก Option ให้ Excel ไม่ต้องสร้างเจ้าฟังก์ชันนั้นขึ้นมาให้ (ติ๊ก Generate GetPivotData ออก) โดยอยากให้จิ้มแล้วเป็น Cell Reference ธรรมดาแทน จะได้เขียน/copy สูตรง่ายๆ
      วิธีทลายข้อจำกัดหน้าตาของ PivotTable ด้วย OLAP CUBE Formula 69 
      • ข้อดี : เขียนง่ายมาก แค่ = แล้วจิ้ม จากนั้น Copy มาได้ตามปกติ
        วิธีทลายข้อจำกัดหน้าตาของ PivotTable ด้วย OLAP CUBE Formula 70
      • ข้อเสีย : เสี่ยงต่อการที่ PivotTable เลื่อนจากที่เดิม ทำให้ได้ข้อมูลที่ผิดกลับไปโดยไม่รู้ตัว
    4. เลิกใช้ Pivot หันมาเขียนสูตรแทน : วิธีนี้เรียกว่าเปลี่ยนแนวโดยสิ้นเชิง เขียนสูตรเอาเองดีกว่า ไม่ง้อ Pivot ก็ได้ ไม่ว่าจะเป็นสูตรง่ายๆ อย่าง SUMIFS หรือจะใช้ Data Table มาช่วย หรืออาจจะใช้ Array Formula กรณีที่การคำนวณซับซ้อน
      • ข้อดี : มีความยืดหยุ่นสูงกว่า Pivot
      • ข้อเสีย : เขียนสูตรยาก (บางคนเจอตรงนี้ก็ยอมแพ้แล้ว) และอาจคำนวณช้ากว่า Pivot

    แต่วันนี้ผมมีอีกวิธีมาแนะนำครับ นั่นคือ ใช้ความสามารถที่เรียกว่า OLAP CUBE Formula

    วิธี OLAP CUBE Formula

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

    ผมจะขอแบ่งออกเป็น 2 วิธี คือ 1. ใช้ Data Model (Excel 2013 ขึ้นไป) 2. ใช้ PowerPivot (ซึ่งใช้ได้ตั้งแต่ Excel 2010 แต่ต้องลง Add-in) ซึ่งวิธีแรกน่าจะง่ายกว่าสำหรับคนส่วนใหญ่ครับ

    ใช้ Data Model (Excel 2013 ขึ้นไป)

    มี Step ประมาณนี้

    1. ตอนจะสร้าง PivotTable ให้ติ๊กช่อง Add to Data Model ด้วย
      วิธีทลายข้อจำกัดหน้าตาของ PivotTable ด้วย OLAP CUBE Formula 71
    2. สร้าง Pivot ให้สรุปข้อมูลตามปกติ เอาให้คล้ายรายงานที่ต้องการมากที่สุดก่อน
      วิธีทลายข้อจำกัดหน้าตาของ PivotTable ด้วย OLAP CUBE Formula 72
    3. ไปคลิ๊ก OLAP Tools -> Convert to Formulas…
      วิธีทลายข้อจำกัดหน้าตาของ PivotTable ด้วย OLAP CUBE Formula 73
       
      • เพียงเท่านี้ PivotTable ก็จะถูกแปลงเป็น Cell ที่เต็มไปด้วยสูตร CUBE ซึ่งข้อมูลแต่ละช่องจะแยกออกจากกัน
        วิธีทลายข้อจำกัดหน้าตาของ PivotTable ด้วย OLAP CUBE Formula 74
    4. จัด Layout ตามใจชอบ – คุณจะแทรกคอลัมน์ยังไง โยกข้อมูลยังไง ก็สามารถทำได้แล้ว
      วิธีทลายข้อจำกัดหน้าตาของ PivotTable ด้วย OLAP CUBE Formula 75

    จะเห็นว่า ผมจะปรับแต้งหน้าตายังไงก็ได้เลย ซึ่งอิสระขึ้นมากๆ ครับ ^^

    วิธีทลายข้อจำกัดหน้าตาของ PivotTable ด้วย OLAP CUBE Formula 76

    ใช้ PowerPivot (Excel 2010 ขึ้นไป)

    มี Step ประมาณนี้

    1. ถ้ายังไม่มี PowerPivot ให้ไปโหลดมา install ก่อน => Download ที่นี่
    2. Add Data เข้า Power Pivot โดยกดปุ่ม Create Linked Table
      วิธีทลายข้อจำกัดหน้าตาของ PivotTable ด้วย OLAP CUBE Formula 77
    3. สร้าง Pivot จากหน้าต่าง PowerPivot
      วิธีทลายข้อจำกัดหน้าตาของ PivotTable ด้วย OLAP CUBE Formula 78
    4. จากนั้นก็เหมือนแบบวิธี Data Model แล้วล่ะ จัดระเบียบแล้วกด Convert to Formulas โลด!
      วิธีทลายข้อจำกัดหน้าตาของ PivotTable ด้วย OLAP CUBE Formula 79
    5. Pivot จะถุกกระจายกลายเป็นสูตรที่แยกกันคนละช่องเช่นเดียวกับวิธีข้างบนครับ
      วิธีทลายข้อจำกัดหน้าตาของ PivotTable ด้วย OLAP CUBE Formula 80

    ผลลัพธ์ยังสามารถผูกกับ Slicer ได้เช่นเดิม

    หากตอนแรก Pivot ของเรามีการผูก Slicer ไว้ หลังจากแปลงเป็น CUBE Formula แล้ว Slicer ก็ยังทำงานได้นะครับ

    วิธีทลายข้อจำกัดหน้าตาของ PivotTable ด้วย OLAP CUBE Formula 81
    วิธีทลายข้อจำกัดหน้าตาของ PivotTable ด้วย OLAP CUBE Formula 82

    หลังกด Slicer ผลลัพธ์ก็จะถูกตัดกรองเช่นเดียวกับ PivotTable ปกติเลย!!

    วิธีทลายข้อจำกัดหน้าตาของ PivotTable ด้วย OLAP CUBE Formula 83

    แปลว่า เราสามารถเอาวิธีการนี้ ไปสร้าง Dashboard เท่ๆ ได้เลยนะ!!

    CUBE เป็นสูตร แต่ยังต้องกด Refresh เช่นเดียวกับ Pivot

    เนื่องจากสูตรแบบ CUBE เป็นการเชื่อมต่อกับฐานข้อมูล ดังนั้นเวลาทำงานจริง หากข้อมูลต้นทางเปลี่ยนไป จะต้องมีการกด Refresh ก่อน ผลลัพธ์จึงจะเปลี่ยน เช่นเดียวกับ Pivot Table ซึ่งเรา Refresh ได้ 2 ที่ คือ

    Refresh ที่ Data Connections

    ซึ่งสามารถ Refresh ได้ที่เครื่องมือ Data -> Refresh All ครับ

    วิธีทลายข้อจำกัดหน้าตาของ PivotTable ด้วย OLAP CUBE Formula 84

    อย่าลืม!! ถ้าเราไม่ Refresh ผลลัพธ์จะไม่เปลี่ยนนะ

    Refresh ที่ PivotTables

    แต่ถ้าใครไม่ชอบวิธีนี้ จะกด Refresh ที่ PivotTables ก็ได้นะ แต่ต้องทำการเก็บ Pivot ไว้อย่างน้อยอันนึงก่อนที่จะแปลงเป็น OLAP Formula นะครับ (ถ้าแปลงไปแล้ว จะกดที่กด Refresh แบบ Pivot ไม่เจอ)

    วิธีทลายข้อจำกัดหน้าตาของ PivotTable ด้วย OLAP CUBE Formula 85

    คราวนี้เราจะ Refresh ที่ Pivot ได้ตามปกติแล้ว

    วิธีทลายข้อจำกัดหน้าตาของ PivotTable ด้วย OLAP CUBE Formula 86

    จบแล้ว

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

  • 4 วิธีสร้าง PivotTable จากข้อมูลหลายตาราง

    4 วิธีสร้าง PivotTable จากข้อมูลหลายตาราง

    รู้หรือไม่ว่าเราสามารถสร้าง PivotTable จากข้อมูลหลายตารางได้! ในบทความนี้เราจะมาดูกันว่าทำยังไง?

    การทำ PivotTable จากหลายตารางนั้น ทำได้หลายวิธี แต่ละวิธีก็มีข้อดีข้อเสีย-ข้อจำกัดต่างกันดังนี้ครับ มาดูรายละเอียดกันดีกว่า

    วิธีที่ 1 : ใช้ Data Model & PowerPivot

    วิธีนี้เป็นการสร้าง PivotTable จากข้อมูลหลายตารางที่มีความสัมพันธ์กัน (เรียกว่า Data Model) ซึ่งเป็น Concept เดียวกับโปรแกรม Power BI เลยครับ หากใช้วิธีนี้เราก็จะสามารถสร้าง Pivot Table แล้วเลือก Field ข้ามตารางได้ โดยที่ไม่จำเป็นต้องเอาข้อมูลมารวมเป็นตารางเดียวกันเลยด้วยซ้ำ

    อย่างไรก็ตามวิธีนี้จะไม่สามารถเอาตารางมาต่อแถวกันได้นะครับ

    นอกจานี้ วิธีนี้ต้องใช้ Excel version 2013 ขึ้นไปครับ และถ้าจะให้ดีควรจะ Enable Add-in Power Pivot ซะก่อนจึงจะใช้งานได้สะดวก ซึ่งทำตามดังนี้

    4 วิธีสร้าง PivotTable จากข้อมูลหลายตาราง 87

    แล้วเลือก PowerPivot Add-in ซะตามรูป

    4 วิธีสร้าง PivotTable จากข้อมูลหลายตาราง 88

    จากนั้นเราจะต้องเอาตารางแต่ละอัน Add เข้า Data Model ซะก่อน เช่น มีทั้งหมด 3 ตาราง เช่น
    1. ตารางการขาย 2. ตารางรหัสพนักงาน 3. ตารางลูกค้า

    เราต้องเอาตารางเข้า Data Model โดยเลือกข้อมูล แล้วแปลงแต่ละตารางให้เป็น Table โดย Insert -> Table ซะก่อน แล้วค่อยกด Add To Data Model

    4 วิธีสร้าง PivotTable จากข้อมูลหลายตาราง 89

    จากนั้นมันจะเอาข้อมูลเข้าสู่หน้าต่างของ PowerPivot

    4 วิธีสร้าง PivotTable จากข้อมูลหลายตาราง 90

    จากนั้นให้กด Switch to workbook เพื่อกลับออกมาใน Excel แล้วกด Add To Data Model ให้ครบทุกตารางที่มีความสัมพันธ์กัน

    พอ add เข้าไปหลายตาราง ในหน้า Power Pivot จะมีหลายชีทด้วย

    4 วิธีสร้าง PivotTable จากข้อมูลหลายตาราง 91

    จากนั้นให้กดดู Diagram View เพื่อผูกความสัมพันธ์ตารางต่างๆ เข้าด้วยกัน

    4 วิธีสร้าง PivotTable จากข้อมูลหลายตาราง 92

    เราจะเห็นตารางแยกกันอยู่ ให้เรากำหนด Relationship โดยลากเส้นเชื่อมว่า field ไหนคือตัวที่เชื่อมความสัมพันธ์ระหว่างแต่ละตารางเข้าด้วยกัน (จะลากจากไหนไปไหนก็ได้)

    4 วิธีสร้าง PivotTable จากข้อมูลหลายตาราง 93

    ทำให้ครบจะได้แบบนี้ จากนั้นก็กดสร้าง PivotTable ได้แล้ว

    สร้าง PivotTable จากข้อมูลหลายตาราง

    เราก็จะสามารถใช้ PivotTable Model Data Model แล้วยังสามารถลาก Field ข้ามตารางได้เลย โดยไม่ต้องเอาข้อมูลมารวมกันเป็นตารางเดียวกันอีก

    4 วิธีสร้าง PivotTable จากข้อมูลหลายตาราง 94

    สำหรับรายละเอียดว่า Concept ของ Data Model ทำงานยังไง ให้ไปอ่านใน Series Power BI ได้ครับ

    หมายเหตุ : นอกจากวิธีนี้ เรายังกดสร้าง PivotTable โดยใช้ Data Model แบบไม่เปิด PowerPivot ก็ได้ โดยกดสร้าง PivotTable จากตารางโดยตรง แล้วติ๊ก Add to Data Model

    4 วิธีสร้าง PivotTable จากข้อมูลหลายตาราง 95

    แต่ข้อเสียของวิธีนี้ก็คือการกำหนด Relationship จะทำยากกว่า (เพราะไม่เห็นภาพ) โดยต้องใช้เมนูนี้

    4 วิธีสร้าง PivotTable จากข้อมูลหลายตาราง 96

    วิธีที่ 2 : ใช้ Power Query (เหมาะกับ Excel 2013 ขึ้นไป)

    Pivot Table จากหลายตาราง

    การใช้ Power Query นั้นสามารถทำได้ 2 ลักษณะ คือ

    2.1 เตรียมตารางแยกหลายๆ ตารางที่มีความสัมพันธ์กัน แล้วส่งเข้าสู่ Data Model

    ถ้าใช้แบบนี้ ก็เป็นการใช้ Power Query แค่จัดหน้าตาข้อมูลให้เหมาะสมเท่านั้น เพิ่มเติมแค่ตอนกด Close & Load To… ให้เลือกเป็น Connection Only (เพราะเราจะไม่ Load ผลลัพธ์ออกมาใน Excel ปกติ) แต่ให้ติ๊กเลือกว่า Add to Data Model เพื่อให้มันส่งผลลัพธ์เข้า Data Model เท่านั้นเอง

    4 วิธีสร้าง PivotTable จากข้อมูลหลายตาราง 97

    2.2 รวมข้อมูลทั้งหมดให้อยู่ในตารางเดียวกันก่อน แล้วค่อยส่งเข้า Pivot Table แบบปกติ

    คำว่ารวมให้อยู่ในตารางเดียว จริงๆ แล้วก็มี 2 แบบใหญ่ๆ นั่นคือ รวมคอลัมน์ กับ รวมแถว

    • การรวมข้อมูลหลายๆ ตารางมาต่อแถวกัน (คล้ายการที่เรา Copy Paste ) เรียกว่า Append
    • การรวมข้อมูลหลายๆ ตารางมารวมคอลัมน์กัน (คล้ายๆ กับใช้ VLOOKUP) เรียกว่า Merge

    จากนั้นค่อยส่งผลลัพธ์ที่รวมตารางเสร็จแล้วเข้าสู่ Pivot Table อีกทีครับ

    Append เพื่อรวมตารางแบบเพิ่มแถว

    วิธี Append ก็ให้สร้าง Query 2 ตารางขึ้นมาก่อน ตารางไหนยังไม่เอาผลลัพธ์ออกมาก็ทำแบบ Connection Only ไว้ เช่น อาจทำให้เป็น Connection Only ทั้งคู่เลย แล้วกดคลิ๊กขวาที่ Query ตัวแรก แล้วเลือก Append กับ Query ตัวอื่นๆ

    4 วิธีสร้าง PivotTable จากข้อมูลหลายตาราง 98
    4 วิธีสร้าง PivotTable จากข้อมูลหลายตาราง 99

    แล้วเราก็จะได้ Query ใหม่ ที่เป็นผลลัพธ์จากการ Append 2 ตารางเข้าด้วยกัน (เอามาต่อแถวกัน)

    4 วิธีสร้าง PivotTable จากข้อมูลหลายตาราง 100

    จากนั้นก็เอาผลลัพธ์ออกไปใช้ใน Pivot Table ได้

    4 วิธีสร้าง PivotTable จากข้อมูลหลายตาราง 101

    ถ้ามีหลายตารางที่มีความสัมพันธ์กันจะติ๊ก Add this Data to the Data Model ด้วยก็ได้

    Merge เพื่อรวมตารางแบบเพิ่มคอลัมน์

    การจะ Merge ก็ให้สร้าง Query 2 ตารางขึ้นมาก่อน ตารางไหนยังไม่เอาผลลัพธ์ออกมาก็ทำแบบ Connection Only ไว้

    จากนั้นกดที่ Query แรกแล้วคลิ๊กขวาเลือก Merge กับ Query ตัวที่สอง ที่สำคัญคือต้องเลือกด้วยว่าคอลัมน์ไหนคือตัวเชื่อมกัน และเลือก JoinKind ด้วย ซึ่งปกติก็จะเป็น Left Outer Join แบบนี้แหละ ok ได้เลย

    4 วิธีสร้าง PivotTable จากข้อมูลหลายตาราง 102

    ผลลัพธ์จะออกมาเป็น Table ก่อน

    4 วิธีสร้าง PivotTable จากข้อมูลหลายตาราง 103

    ให้เราคลิ๊กที่มุมขวาบนของคอลัมน์เพื่อ Expand เอาข้อมูลออกมา

    4 วิธีสร้าง PivotTable จากข้อมูลหลายตาราง 104

    แล้วเราก็จะได้ผลลัพธ์คล้ายๆ กับ VLOOKUP เลย (แต่ถ้าตารางอ้างอิงมีหลายบรรทัด มันจะมาทุกบรรทัดนะ ต่างจาก VLOOKUP ที่จะมาแค่อันบนสุด)

    4 วิธีสร้าง PivotTable จากข้อมูลหลายตาราง 105

    จากนั้นก็ Load เอาผลลัพธ์เข้า Pivot Table ได้เลย

    4 วิธีสร้าง PivotTable จากข้อมูลหลายตาราง 106

    วิธีที่ 3 : ใช้ Microsoft Query

    วิธีสร้าง PivotTable จากข้อมูลหลายตารางด้วย Microsoft Query เหมาะกับคนที่ไม่มี Power Query ให้ใช้ เช่นคนที่มี Excel Version เก่ามากๆ นั่นเองครับ

    วิธีการทำมีดังนี้

    1. เรียกใช้ Microsoft Query โดยไปที่ Data => Get External Data => From Other Source => From Microsoft Query (Excel Version ใหม่จะไม่มีเมนูนี้แล้ว)
    2. ดับเบิ้ลคลิ๊กที่ Excel File* แล้วเลือกไฟล์ Excel ที่ต้องการ
      • ปกติเลือกไฟล์ที่ทำงานอยู่ก็ได้ แต่ถ้าใครกดไม่ได้ให้เปิดจากไฟล์อื่น)
      • ถ้ามี Error บอกว่าหา Table ไม่เจอ ให้เลือก Option แล้วติ๊ก System Table ด้วย
        table-option
    3. เลือก Table ซักอัน แล้วกด > เพื่อ Add Field (ในที่นี้ของผมมีปัญหากับ field ภาษาไทย ผมเลยต้องเปลี่ยนชื่อ Field เป็นภาษาอังกฤษ)
      Microsoft Query-01
    4. กด Next ไปจนหน้าสุดท้าย ให้เปลี่ยนเป็นเลือกดู Query ก่อน
      Microsoft Query-02
    5. กด SQL แล้วแก้ Code ให้เป็นดังนี้
      SELECT *
      FROM ‘pathของไฟล์’.’ชื่อชีทแรก(ตามที่มันแสดง)’ ‘ชื่อชีทแรก(ตามที่มันแสดง)’
      UNION ALL
      SELECT *
      FROM ‘pathของไฟล์’.’ชื่อชีทสอง(ตามที่มันแสดง)’ ‘ชื่อชีทสอง(ตามที่มันแสดง)’
      Microsoft Query-03
    6. ของผมจะได้เป็นแบบนี้
      SELECT *
      FROM ‘D:\INWEXCEL\PIVOT\multiple-pivot-data2.xlsx’.’Month1$’ ‘Month1$’
      UNION ALL
      SELECT *
      FROM ‘D:\INWEXCEL\PIVOT\multiple-pivot-data2.xlsx’.’Month2$’ ‘Month2$’
    7. แล้วกด OK แล้วมันจะบอกว่าไม่สามารถแสดงผลได้นะ ให้ OK อีกที
    8. จากนั้นไปที่ file => return data to excel
      Microsoft Query-04
    9. เลือกให้ส่งเข้า PivotTable ไปเลยก็ได้ (ถ้าเลือกเป็น Table มันจะออกมาเป็นตารางก่อน)
      Microsoft Query-05
    10. จากนั้นก็หมุน PivotTable ทุกอย่างได้ตามปกติ
      Microsoft Query-06

    วิธีการใช้ Microsoft Query นี้ สามารถนำมาประยุกต์ได้เยอะแยะ เช่น มี 2 ตารางแยกกันอยู่ อยากเอามาผูกกันโดยไม่ต้องใช้ VLOOKUP ก็ยังได้เลยครับ โดยมีทั้งแบบลากเส้นเชื่อมเองใน Editor หรือจะเขียน SQL เอง ก็ได้ (ภาษา SQL ทำได้แทบทุกอย่าง แต่อาจต้องศึกษาเยอะหน่อย)

    วิธีที่ 4 : ใช้ Multiple Consolidation Ranges (PivotTable Wizard เหมาะกับ Excel เก่า)

    วิธีสร้าง PivotTable จากข้อมูลหลายตารางด้วยตัวเลือก Multiple Consolidation Ranges ในคำสั่ง PivotTable Wizard เป็นวิธีที่มีข้อจำกัดมากที่สุด ผมจึงไม่แนะนำครับ แค่จะเอาให้ดูเฉยๆ ว่าในอดีตมันทำแบบนี้ได้

    วิธีทำแบบสั้นๆ

    กด Alt, D, P เพื่อเรียก PivotTable Wizard แล้วเลือก Multiple Consolidation Ranges
    consolidate-range

    เพื่อความง่ายเลือก Create a single page field for me ไปเลยก็ได้ครับ

    เลือก Range ที่ต้องการ แล้วกด Add โดยทำทีละ source พอเสร็จแล้วก็กด Next จนจบกระบวนการ
    select-range

    คุณจะได้ PivotTable หน้าตาประหลาดมา 1 อัน ดังนี้
    first-result2

    ผลลัพธ์ที่ได้ มีข้อจำกัดดังนี้ 

    • Field แรกใน Data Source ต้องมาเป็น Row Item Label เสมอ
    • Field ที่เหลืออันอื่นจะมาอยู่ใน Column Label เสมอ (มาเป็นชุดถ้าเอาออกต้องเอาออกหมดเลย)
    • การสรุปผล เช่น SUM, COUNT เปลี่ยนได้ แต่เปลี่ยนแล้วเท่ากับเปลี่ยนให้กับทุก Field เลย ไม่สามารถทำให้แต่ละ Field ไม่เหมือนกันได้
    • Field Page 1 แค่เป็นตัว Filter เลือกแต่ละ Data Source เฉยๆ เอาออกก็ได้

    วิธีปรับแต่ง Data Source เพื่อทำให้แสดงผลดีขึ้นภายใต้ข้อจำกัดที่มี

    • เอา Item ที่ต้องการไว้เป็น Row Item Label ไว้ Field แรกเสมอ
    • ย้าย Field ที่ไม่ต้องการไปทางขวาๆ แล้วตอนเลือก Range ไม่ต้องเลือกมัน

    ผมลองทำแล้ว ก็พอจะได้ผลลัพธ์ที่ ok ขึ้น ดังนี้ (ผมแยกแต่ละชีทเป็นคนละเดือน ดังนั้นเลยเอา Page1 มาใส่เป็นเดือนได้)
    first-result3

    สรุปแล้วจะเห็นว่าวิธี Multiple Consolidation Ranges นั้นมีข้อจำกัดมากเลยนะครับ เห็นมะ อย่าใช้เลย 555

    เพื่อนๆ ชอบแบบไหน หรือผสมแบบไหนดี?

    ส่วนตัวผมชอบใช้ Power Query จัดข้อมูลให้เรียบร้อยก่อน แล้วค่อยส่งเข้า Data Model ไปทำต่อครับ พูดง่ายๆ คือ Step เหมือน Power BI เป๊ะเลย

    เพื่อนๆ ชอบแบบไหนกันบ้างครับ อย่าลืม Comment บอกด้วยนะ

  • สรุปทุกสิ่งที่คุณควรรู้เกี่ยวกับ Pivot Table : ภาค 2 – ปรับแต่งตารางให้ได้ดั่งใจ

    สรุปทุกสิ่งที่คุณควรรู้เกี่ยวกับ Pivot Table : ภาค 2 – ปรับแต่งตารางให้ได้ดั่งใจ

    ปรับรูปแบบการแสดงผล

    ปกติแล้ว Pivot Table จะแสดงผลในรูปแบบที่เรียกว่า Compact Form ตามรูปในบทที่แล้ว
    ซึ่งความชอบส่วนตัวผมเองชอบให้แสดงในรูปแบบ Tabular Form หรือ Outline Form มากกว่า

    ผมแนะนำให้ลองทำเป็น Tabular Form ดูโดยให้ทำดังนี้

    ให้คลิ๊กที่ PivotTable แล้วไปที่ (PivotTable)[Design]–>Layout –> Report Layout –> Show in Tabular Form

    Pivot-Basic-Tabular

    จะเห็นว่าข้อมูลแต่ละ Row และ Column จะแยกกันชัดเจน อยู่คนละช่องกันไปเลย
    และมีชื่อ Field กำกับอยู่ตลอด ทำให้ไม่สับสนเหมือนดู Tabular Form

    ใครดูแล้วชอบแบบผมก็แนะนำให้ปรับตามได้เลยครับ

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

    ลูกเล่นที่ Report Filter

    Pivot-Filter

    ตรงนี้มีเครื่องมือ Filter ให้ใช้เป็นหลัก ซึ่งยอมให้เราเลือก Choice ได้หลายอันพร้อมกันได้ด้วย

    วิธีการคือให้ติ๊กปุ่ม Select Multiple Items ก่อน นอกจากนี้เราสามารถทำการ Search ได้เช่นเดียวกันเครื่องมือ Filter ปกติเลย

    เมื่อเราทำการ Filter แล้วจะเหลือผลลัพธ์แค่สิ่งที่เราสนใจเท่านั้น เช่น ถ้า Filter ตามรูป

    Pivot-Filter-after

    จะเห็นว่าข้อมูลบางช่องลดลงไปจากเดิม (เช่น F14 ลดจาก 540 เหลือ 160) เนื่องจากถูกกรองข้อมูลออกไปนั่นเอง ซึ่งเราสามารถสังเกตเครื่องหมายกรวยกรองได้จาก Section Filter บนตาราง Pivot และบริเวณ Field List ด้านขวา

    สร้าง Report แยกตาม Filter แต่ละ Item ด้วย Generate Report Filter Page

    บ่อยครั้งคุณอาจอยากจะสร้าง PivotTable หลายๆ ตาราง แยกตามสิ่งที่คุณสนใจ เช่น แยกตามประเภทสินค้า

    reportfilter-1

    ปกติแล้วต้องเลือกสินค้าดูทีละชิ้นกว่าจะเลือกครบก็เสียเวลามาก… ผมมีเทคนิคแนะนำ โดยให้ Excel ทำในสิ่งที่คุณต้องการโดยอัตโนมัติ ให้ไปที่ [[Options]] –> PivotTable –> Options –> Show Report Filter Page… –> เลือก Filter ที่ต้องการ

    reportfilter-3

    Excel จะทำการสร้าง PivotTable ออกมาแยกเป็น Sheet ละ 1 Item ของ Filter นั้นๆ โดยอัตโนมัติ!!

    reportfilter-4

    ลูกเล่นที่ Row Label และ Column Label

    1. สามารถกดปุ่มลูกศร แล้วเลือก Sort ข้อมูล หรือ Filter ข้อมูลบน Label ได้

    การ Sort กับการ Filter ที่เราเรียนรู้มาก่อนหน้านี้ ก็มีให้เลือกใช้ใน PivotTable เช่นกัน

    Pivot-RowLabel
    Pivot-More-sort
    การ Sort ข้อมูล

    การ Sort มีวัตถุประสงค์เพื่อให้ผู้อ่านข้อมูล เข้าใจข้อมูลได้ง่ายขึ้น เช่น ถ้าเรากำหนดให้ Row label หรือ Column Label เรียงหัวข้อตามตัวอักษร หรือ เรียงตามที่มีข้อมูลสรุป เช่น ค่า SUM หรือ COUNT จากน้อยไปมาก มากไปน้อย ย่อมดีกว่าการเรียงข้อมูลแบบ “สะเปะสะปะ” แน่นอน

    • A -> Z (น้อยไปมาก) หรือ Z -> A (มากไปน้อย)
    • More Sort Options
      • Manual คือ ยอมให้เราลากสลับตำแหน่งเองได้ตามต้องการ
      • Ascending by: หรือ Descending by: Field xxx คือ ให้เรียง Field ตามข้อมูลใน Field Values อื่น ตรงนี้มีประโยชน์มาก เช่น อาจเรียงประเภทสินค้าตามยอดขายจากมากไปน้อยเป็นต้น
    การ Filter ข้อมูล

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

    2. สามารถเปลี่ยนชื่อของ Label ได้

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

    3. สามารถจัดกลุ่มข้อมูลได้ (Group & Ungroup)

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

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

    ประเภทจัดกลุ่มแบบ Manualจัดกลุ่มแบบ Auto
    วิธีการทำการเลือกว่าจะเอา item ไหนจัดอยู่ด้วยกัน โดยการเลือก Selection เองจากนั้นค่อยกด GroupExcel จะขึ้นมาให้เลือกว่าจะ Group ช่วงทีละเท่าไหร่
    ประเภทข้อมูลที่ใช้ได้ทุกประเภทใช้ได้กับข้อมูลที่เป็นตัวเลขหรือพวกวันที่เท่านั้น และจะต้องไม่มีค่าว่างอยู่ใน Data Source เลย
    ข้อมูลหลังจากการ Groupจะมี Field ใหม่เพิ่มขึ้นมาให้โดยอัตโนมัติ
    ในชื่อ Field เดิม
    แต่มีเลขต่อท้าย    		กรณีข้อมูลเป็นตัวเลข : ไม่มี Field ใหม่เพิ่มขึ้นมาให้ แต่มันจะเปลี่ยนข้อมูลที่ Field ต้นฉบับเลย

    กรณีข้อมูลเป็นวันที่ : ประเภทช่วงวันที่ที่ย่อยที่สุดจะมาทับที่ Field เดิม และจะมีการสร้าง Field ใหม่ในประเภทช่วงวันที่ที่ใหญ่กว่าด้วย เช่น เลือกให้ Group ตาม Year, Quarter, Month ผลก็คือ Field วันที่ Date เดิม จะกลายเป็น Month และจะมี Field ชื่อ Quarters และ Years เพิ่มขึ้น

    วิธีการจัดกลุ่มแบบ Manual

    • ให้ทำ selection เลือก Label ที่ต้องการจัดให้อยู่ในกลุ่มเดียวกันพร้อมกันก่อน
    • จากนั้นกด (PivotTable) [[Options]] –> Group –> Group Selections
      หรือ กดคลิ๊กขวา –> Group… ก็ได้
      pivot-group-menu
    • เมื่อ Group แล้วจะมี Field ใหม่เพิ่มขึ้นมาให้โดยอัตโนมัติ ในชื่อ Field เดิมแต่มีเลขต่อท้าย
    • จากนั้นเราก็สามารถเอา Field เก่าออกไปได้เลย (กดคลิ๊กขวาที่ ผู้ขาย แล้ว Remove ก็ได้) สุดท้ายก็จะเหลือแค่สิ่งที่เราจัด Group ใหม่เท่านั้น (ถ้าต้องการก็ Rename คำว่า Group1 ได้)
      Pivot-Group3  

    วิธีการจัดกลุ่มแบบ Auto : ข้อมูลเป็นตัวเลข

    • ให้เลือกที่ Label ช่องใดช่องหนึ่งแล้วกด Group Selections หรือ กดคลิ๊กขวา –> Group… จะมีหน้าต่าง Grouping ให้กำหนดจุดเริ่มต้น จุดสิ้นสุด และความกว้างของช่วง ซึ่งเราลองเปลี่ยนค่าเล่นได้ว่าจะให้เริ่ม/ จบ/แบ่งช่วง ทีละเท่าไหร่?
      pivot-grouping
    • เมื่อกด Ok สิ่งที่ขึ้นมาจะขึ้นเป็นช่วงตัวเลขมาให้โดยอัตโนมัติ โดยที่ค่าที่น้อยว่าจุด Start และ ค่าที่มากกว่าจุด End ก็จะกลายเป็นอีกกลุ่มหนึ่ง

    ผลของการ Grouping จะแยกเป็น 2 กรณี ดังนี้

    กรณีที่ต้นฉบับเป็นเลขจำนวนเต็มกรณีที่ต้นฉบับมีเลขทศนิยม
    Pivot-Group-Numberpivot-group-decimal
     เมื่อจัดกลุ่มแล้วเลขจะไม่ซ้ำกัน ซึ่งไม่น่าจะทำให้เกิดความสับสนอะไรpivot-grouping2เวลาจัดกลุ่มแล้วเลขจะขึ้นซ้ำกันpivot-group-decimal2
    วิธีการตีความคือเลขตัวเริ่มจะอยู่ใน Group นั้นเสมอ เช่น 150 จะอยู่ในกลุ่ม 150-250ไม่ได้อยู่ในกลุ่ม 50-150 เป็นต้น

    วิธีการจัดกลุ่มแบบ Auto : ข้อมูลเป็นวันที่

    • ให้เลือกที่ Label ช่องใดช่องหนึ่งแล้วกด Group Selections มันจะขึ้นให้เลือกว่าจะจัดกลุ่มวันที่ เริ่มที่เท่าไหร่ จบที่เท่าไหร่? แบ่งตามอะไร (เลือกได้หลายอันพร้อมกัน) เช่น วัน (ระบุได้ว่าเอาช่วงกี่วัน) , เดือน, Quarter, ปี เป็นต้น
      pivot-groupinig-date

    สังเกตว่า ประเภทช่วงวันที่ที่ย่อยที่สุดที่เราเลือกตอน Grouping (Months) จะมาทับที่ Field เดิม (วันที่)

    pivot-groupinig-date3

    ระวัง! อย่างที่บอกไปตอนต้นว่าการจัดกลุ่มแบบ Auto จะเป็นการทับข้อมูล Field ต้นฉบับเลย ผมขอแนะนำว่า คุณควรจะเลือกตัวย่อยสุดตอนที่ทำการ Grouping ให้เป็น ระดับ Days (หรือย่อยกว่านั้น) ถ้าคุณไม่ใช้มัน ค่อยลากออกไปจาก Pivot Area ทีหลังก็ได้ ไม่งั้น Field ที่ชื่อว่า Days จะถูก Grouping เป็น Months สับสนตายเลย! 

    4. สามารถเลือกวิธีการสรุปข้อมูล (Subtotal) ก่อนจะขึ้น Label ใหม่ได้

    ปกติแล้ว หากเราใส่ Row Label ไป 2 Field ซ้อนกัน เช่น ผมลองเอา สินค้ามาซ้อนกับผู้ซื้อ มันจะมีการสรุปข้อมูลเป็น Subtotal ให้เราก่อน เช่น SUM AVERAGE MAX MIN เป็นต้น โดยที่เราสามารถเลือกตัวสรุปข้อมูลที่ต้องการได้ หรือจะใส่พร้อมๆกันหลายๆ อันก็ยังได้

    วิธีการทำดังนี้

    • ให้คลิ๊กขวาที่ Label ที่ต้องการจะทำการ Subtotal (เช่น นาย a) แล้วเลือก Field Settings
    • None = ไม่แสดงการสรุปข้อมูล
    • Custom สามารถเลือกได้หลายตัวพร้อมกัน
    Pivot-SubTotal
    Pivot-SubTotal2
    Pivot-SubTotal3

    ลูกเล่นที่ Values

    ส่วนใหญ่แล้ว สิ่งที่ซับซ้อนของ PivotTable จะอยู่ที่วิธีการคำนวณตรง Values นี่แหละครับ เพราะมันมีให้ปรับได้หลากหลายมาก เช่น ในตัวอย่างข้างบนใช้วิธี SUM แต่ Pivot ยังมีวิธีอื่นๆอีกเยอะแยะครับ เช่น COUNT, AVERAGE, MAX, MIN เป็นต้น

    วิธีการปรับแต่งคือให้ คลิ๊กขวาที่ Field ข้อมูลที่ต้องการจะปรับแล้วกด Value Field Setting ครับโดยหลักๆ จะมีให้ปรับ 2 เรื่อง คือ 1) Summarized by และ 2) Show Value As

    Summarized by

    เป็นการสั่งว่าจะให้ Excel ทำการสรุปทางสถิติด้วย Function อะไร โดยมีฟังก์ชั่นให้เลือกดังรูป โดยที่มีฟังก์ชั่นสรุปผลที่หลากหลาย เช่น  Sum / Count (นับทุกอย่างที่ไม่ว่าง เหมือนฟังก์ชั่น COUNTA) / Count Number (นับเฉพาะเลข เหมือนฟังก์ชั่น COUNT) / Average / Max / Min / Product / StdDev / StdDevp / Var / Varp

    Pivot-Summarized-by
    Pivot-Summarized-by-example

     Show Value As

    เป็นการเอาค่าที่คำนวณได้จาก Summarized by มาคิดต่อ ว่าจะให้แสดงสัมพันธ์กับช่องอื่นของ Pivot ยังไง ปกติจะเป็น No Calculation แปลว่าจะแสดงค่าออกมาตรงๆ ไม่มีการคำนวณต่อ

    Pivot-Show-value-as

    แต่ถ้าเราลองไปเลือกตัวอื่น เช่น % of row มันก็จะเทียบ Grand Total ของแต่ละแถวให้เป็น 100% แล้วดูว่าค่าในช่องนั้นๆ เป็นกี่ % ของค่ารวมระดับแถว เป็นต้น

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

    Show-value-as

    การเปลี่ยนทิศทางการสรุปข้อมูล

    กรณีที่มี VALUE ตั้งแต่ 2 Field ขึ้นไป เราสามารถกำหนดได้ว่าจะให้วางข้อมูลแต่ละ Field เรียงกันในแนวนอน หรือ แนวตั้ง โดยการลากคำว่า Ʃ Values ว่าจะให้แสดงอยู่ที่ Column หรือ Row Labels

    อยู่ที่ Column Labels

    Pivot-Value-Position2
    Pivot-Value-Position

    อยู่ที่ Row Labels

    Pivot-Value-Position3
    Pivot-Value-Position4

    Tips : เทคนิคการลบข้อมูลเก่าที่ค้างอยู่ใน Pivot Table

    เวลาที่เราทำ PivotTable เสร็จแล้ว แต่ภายหลังมีการเปลี่ยนแปลงข้อมูลใน Source Data อาจทำให้มีเหตุการณ์ที่มี Item ที่เคยอยู่ใน Source Data เก่าตกค้างใน Drop Down List ใน Row Label, Column Label, หรือ Filter ได้

    pivot-old-item

    ใน Excel ตั้งแต่ Version 2007 ขึ้นไป สามารถแก้ไขเหตุการณ์ดังกล่าวได้ง่ายมากๆ โดย

    • คลิ๊กขวาที่ตาราง Pivot
    • เลือก PivotTable options à เลือก Data tab
    • ในหมวด Retain Itemsเลือก None
    • OK แล้วกด Refresh ตาราง Pivot อีกครั้ง
      pivot-old-item2

    ใครอยากเก่ง Pivot Table มากขึ้นไปอีก ขอเชิญอ่านตอนที่ 3 ครับ

  • แฉ 10 ความลับของ Excel ภาค 2 : เรื่องง่ายๆ ที่หลายคนไม่รู้

    แฉ 10 ความลับของ Excel ภาค 2 : เรื่องง่ายๆ ที่หลายคนไม่รู้

    หากคิดว่าเราได้รู้ความลับของ Excel ไปหมดแล้วใน แฉ 10 ความลับของ Excel ที่คุณอาจยังไม่เคยรู้มาก่อน! (ภาคแรก) คุณอาจจะต้องคิดใหม่ เพราะ Excel นั้นมีรายละเอียดเยอะ  แม้แต่เรื่องที่ดูเหมือนเป็นเรื่องพื้นฐานหลายๆอย่าง ก็ยังมีที่หลายคนอาจยังไม่เคยรู้มาก่อน วันนี้ผมจึงขอรวบรวมเคล็ดลับที่จริงๆ แล้วเป็นเรื่องที่ง่ายมากๆ มีประโยชน์มาก แต่หลายคนอาจยังไม่รู้มาก่อน

    เมื่อพร้อมแล้วไปลุยกันเลย!

    1. Copy Paste ง่ายๆ ด้วยการกดคลิ๊กขวาแล้วลากเม้าส์

    right-click-menu

    รู้หรือไม่ว่า เราสามารถเลือก Range ของข้อมูลที่ต้องการ จากนั้นเลื่อนเม้าไปที่ขอบของ Range ที่เราเลือกไว้ (มันจะกลายเป็นรูปลูกศรชี้ 4 ทิศ พร้อมกัน ที่ปกติเราเอาไว้ Move ข้อมูล) แล้วกดคลิ๊กขวาค้างไว้่ แล้วลากไปยังเป้าหมายที่ต้องการ จากนั้นจะมีเมนูขึ้นมาให้เพียบเลย (คล้ายๆตอนกด Paste Special) ซึ่งวิธีนี้มีข้อดีคือ จะเห็นชัดเจนก่อนทำการ Paste ว่า ช่องเป้าหมายจะครอบคลุมถึงแค่ไหน

    2. Copy แล้ว Paste Value Only แบบเร็วสุดๆ ด้วย Ctrl+C => Ctrl+V => Ctrl =>V

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

    ctrl-menu
    1. เลือก Range ต้นฉบับแล้วกด Ctrl+C เพื่อ Copy
    2. เลือกช่องปลายทางแล้วกด Ctrl+V เพื่อ Paste
    3. กด Ctrl เพื่อเข้าสู่ เมนู Paste Options
    4. กด V เพื่อ Paste Values (กดตัวอื่นๆก็ได้ ลองเลื่อนดู Shortcut เอาครับ)

    ที่นำเสนอแบบนี้เพราะว่าจำง่ายมาก คือ Ctrl+V => Ctrl => V แถวเร็วสุดๆด้วย ยังไงลองใช้ดูนะครับ

    3. สร้างตาราง Pivot Table ที่ Filter ตัวเลือกแตกต่างกันโดยอัตโนมัติ

    report-filter
    report-filter2

    เวลาเรา Pivot ข้อมูล แล้วมีการใช้ Filter ข้อมูลโดย อยากให้ 1 ตาราง Pivot เป็นของแต่ละ Product

    ปกติแล้วเราก็อาจจะใช้วิธี Pivot ข้อมูลแล้วเลือก Filter ทีละอัน แล้ว Copy เป็นตารางใหม่ เช่น เลือก Product ของเล่น 1 ที แล้ว Copy Pivot ออกมาเป็นอีกตาราง แล้วค่อยเปลี่ยน Filter เป็น Product เครื่องใช้ไฟฟ้า.. ทำแบบนี้ไปเรื่อยๆ เป็นต้น (ถ้ามี item เป็นสิบอันนี่เหนื่อยเลยครับ)

    ผมจะบอกว่า Excel มีเครื่องมือทำรายงานแบบนี้ให้โดยอัตโนมัติ โดยทำตามนี้

    1. ไปกดที่ Ribbon Options ของ Pivot Table (ต้องกดเลือกบริเวณที่เป็น Pivot ก่อน)
    2. ติ๊กปุ่มสามเหลี่ยมใต้ที่ตั้งชื่อตาราง Pivot
    3. เลือก Show Report Filter Page
    4. เลือก Field ที่ต้องการ Filter
    5. Report จะถูกสร้างขึ้นโดยอัตโนมัติ โดยแยก 1 ตาราง ต่อ 1 sheet (มีการตั้งชื่อ Sheet ให้เป็นแต่ละ item ของสิ่งที่เรา Filter ให้ด้วย)

    4. ปิดคำสั่ง Generate GetPivotData

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

    getpivotdata

    ปกติเวลาเราเขียนสูตร แล้วเข้าไปคลิ๊กในบริเวณที่เป็นตาราง Pivot Table เราอาจจะเห็นสูตรพิลึกๆ ขึ้นมาอย่างเช่น
    =GETPIVOTDATA(“Sum of xxx”,$A$3,”yyy”,zzz,”aaa”,”bbb”) ซึ่งทำให้เกิดปัญหาว่าเรา Copy สูตรไปที่อื่น มันก็ไม่เลื่อนช่องให้แบบเวลาใช้ Cell Reference ปกติ ซึ่งมีวิธีแก้เพื่อให้เวลาใช้สูตรแล้วคลิ๊กเข้าไปใน Pivot Table แล้วยังเป็น Cell ปกติได้ โดยให้ทำดังนี้

    1. ไปกดที่ Ribbon Options ของ Pivot Table (ต้องกดเลือกบริเวณที่เป็น Pivot ก่อน)
    2. ติ๊กปุ่มสามเหลี่ยมใต้ที่ตั้งชื่อตาราง Pivot
    3. เอาติ๊กคำว่า Generate Getpivotdata ออก

    อาจมีคนสงสัย แล้ว GETPIVOTDATA มันมีข้อดียังไง? จริงๆแล้วมันก็มีข้อดีของมันอยู่ครับ นั่นก็คือ เวลาเรา Link ข้อมูลมาแล้ว ไม่ว่าตาราง Pivot Table จะถูกพลิกบิดมุมมองไปมาแค่ไหน ค่าที่เรา Link ไว้ก็จะยังเป็นค่าเดิมเสมอ ต่างจากการใช้ Cell Reference ปกติ ที่อาจกลายเป็นค่าใหม่ที่เราไม่ต้องการไปโดยไม่รู้ตัวเลยก็ได้

    5. จริงๆ แล้ว Keyboard Shortcut มันจำง่ายกว่าที่คิดนะ

    เคยสังเกตมั๊ยว่า Keyboard Shortcut ใน Excel หลายๆตัวนั้นจำง่ายกว่าที่คิด

    ถ้าเป็นคำสั่ง Basic เช่น Copy แทนด้วย Ctrl+C เพราะ คำว่า Copy แทนด้วย C หรือเช่นทำให้ตัวอักษรเป็นตัวหนา (Bold) ด้วย Ctrl+B นั้นก็ ไม่น่าแปลงใจมากนัก แต่ว่าจริงๆแล้ว แม้จะเป็นเครื่องหมายแปลกๆ ก็จำง่ายกว่าที่คิดนะครับ เพราะ Excel ตั้งใจในการเลือกใช้ตัวแทนคีย์ลัดได้น่าสนใจทีเดียว เช่น

    keyboard
    • Ctrl+% เพื่อแปลงเป็นหน่วย % (จริงๆ ต้องกด Ctrl+Shift+5 เพราะ Shift +5 คือตัว % แต่ถ้าต้องจำว่า Ctrl+Shift+5 จะไม่มีทางจำได้เลย)
    • Ctrl+^ ก็เพื่อแปลงเป็นเลข Scientific E ยกกำลัง (เพราะเป็นเครื่องหมายยกกำลัง)
    • Ctrl+$ ก็เพื่อแปลงเป็นรูปแบบสกุลเงิน
    • Ctrl+# ก็เพื่อแปลงเป็นวันที่ (เพราะในโปรแกรม Access ก็ใส่วันที่ในเครื่องหมาย #)
    • Ctrl+@ ก็เพื่อแปลงเป็นเวลา เพราะ เครื่องหมาย@ ก็ดูเจาะจง คล้ายว่าจะระบุว่า ณ กี่โมง
    • Ctrl+: ใส่เวลาปัจจุบัน เพราะเหมือนเครื่องหมายคั่น ชม:นาที
    • Ctrl+* เลือก Range ทั้งหมด เพราะ * แทนความหมายว่าทั้งหมด ในภาษาฐานข้อมูล
    • จริงๆ มีอีกเยอะเลย ถ้าเพื่อนๆ ถ้าเจอตัวอื่นที่ดู Make Sense แบบตัวอย่างข้างบนนี้ก็ Comment ไว้ได้นะครับ

    6. รู้หรือไม่ว่า Excel เวอร์ชั่นแรกนั้นออกให้กับ Apple ไม่ใช่ Microsoft

    excel1

    อันนี้เป็นเกร็ดเล็กเกร็ดน้อยครับ หลายๆคนอาจรู้ว่า Excel ไม่ใช่โปรแกรม Spreadsheet อันแรกของโลก เพราะก่อนหน้าที่จะมี Excel นั้นก็มีโปรแกรม Spreadsheet อื่นๆ เกิดขึ้นมาก่อนมากมาย โดยโปรแกรมแรกของโลกนั้น คือโปรแกรมที่มีชื่อว่า VisiCalc ซึ่งออกมาในปี ค.ศ. 1978 โน่นแน่ะ ต่อมาก็มีอีกหลายโปรแกรม แต่ตัวทีมีชื่อเสียงมากที่พวกเราน่าจะพอรู้จักก็คือ Lotus 1-2-3 ซึ่งออกมาในปี ค.ศ. 1983 และในที่สุด Excel เวอร์ชั่นแรกก็ออกมาในปี คศ.1985 แต่ที่แปลกคือ Excel 1.0 ซึ่งเป็น Version แรกนั้น กลับออกมาให้กับสำหรับเครื่อง Apple Macintosh ก่อนที่จะออก Version 2.0 ใน  Windows ซะอีกนะครับ ทั้งที่สร้างโดย Microsoft เองนี่แหละ! สาเหตุอาจเป็นเพราะความสามารถของการทำงานด้วยกราฟิก กับการใช้เม้าส์จิ้มเมนูต่างๆได้นี่แหละ ที่ Microsoft จำเป็นต้องเลือกลงให้ Macintosh ก่อน PC

    7. ฟังก์ชั่น TRIM ไม่ได้ตัดแค่ Space ที่หัวกับท้ายเท่านั้นนะ

    trim-example

    หลายคนอาจคิดว่าฟังก์ชั่น TRIM มันแค่เอาไว้ตัด space หัวท้ายของคำเท่านั้น (เพราะชื่อมันดูเหมือนเอาไว้เล็มๆ ….)
    แต่จริงๆ มันตัด space ทั้งหมด ยกเว้นจะเหลือ space ระหว่างคำไว้ให้แค่สูงสุด 1 เคาะเท่านั้น space (ตัวอื่นๆจะถูกตัดทิ้งทั้งหมดครับ) ตามรูป

    8. ในคำสั่ง FIND/REPLACE เราสามารถกำหนดรูปแบบที่จะค้นหาได้ด้วยนะ

    find-replace

    ปกติเวลาเราค้นหาคำ เราก็จะกด Ctrl+F เพื่อค้นหา แล้วก็พิมพ์คำที่ต้องการลงไป แต่จริงๆแล้วมันสามารถ กด Option แล้วเลือก Format เพื่อหาสิ่งที่ต้องการได้แบบเจาะจงยิ่งขึ้น เช่น หาช่องที่ถมสีเหลือง เป็นตัวหนา มี Number format ที่กำหนด หรือว่ามีการ Lock / Unlock cell เอาไว้ก็ยังได้

    นอกจากนี้ บางทีหลายๆคนเวลากดหาข้อมูลก็อาจหาไม่เจอ ทั้งๆที่เห็นอยู่ตำตา สาเหตุอาจเป็นเพราะเพื่อนๆ เลือกโหมดการหาผิด
    เช่น ใน Look in จะมีให้เลือก 3 อัน คือ Formula , Value, Comment โดยปกติจะเลือกไว้ที่ Formula ซึ่งมันจะหาสิ่งที่พิมพ์ลงไปใน Formula Bar จริงๆ ไม่ใช่ผลลัพธ์จากการคำนวณ หากเราต้องการหาคำที่อยู่ในผลลัพธ์การคำนวณจะต้องกดหาใน Values นะครับ

    9. เราสามารถ Search หาฟังก์ชั่นได้ด้วยนะ

    search-function

    ในเมื่อ Excel นั้นมีฟังก์ชั่นให้เลือกอยู่มากมาย จะหาตัวที่น่าจะใช่ก็ยากนัก (เพราะเลือกใช้ไม่ถูก) ผมจะบอกว่าจริงๆแล้ว Excel สามารถค้นหาฟังก์ชั่นได้เหมือนกันนะครับ (มันจะหาจากคำบรรยายที่เกี่ยวข้อง) โดยกดปุ่ม fx จากนั้นพิมพ์ลักษณะของฟังก์ชั่นที่ต้องการได้เลย พอกด Enter มันจะ List ฟังก์ชั่นทั้งหมดที่เกี่ยวกับคำนั้นมาให้ จากนั้นค่อยอ่านคำบรรยายด้านล่างเอาว่าใช้สิ่งทีต้องการหรือไม่ เคล็ดลับง่ายๆแบบนี้ หลายๆคนอาจยังไม่รู้ก็ได้นะ!

    10. รู้จักใช้เครื่องหมาย Wildcard ใน Function ค้นหาข้อมูล

    wildcard_joker_main2

    เวลาเราใช้ Function หลายๆ อย่างเพื่อค้นหาข้อมูล เช่น SEARCH หรือ VLOOKUP หรือ MATCH จริงๆเราสามารถค้นหาคำที่มีลักษณะคล้ายกับคำที่กำหนดได้ ด้วยการใช้เครื่องหมาย Wildcard ได้

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

    • เครื่องหมายคำถาม (?) แทนตัวอักษรใดๆก็ได้จำนวน 1 ตัวอักษร
      • เช่น “b?t” จะเจอคำที่มี 3 ตัวอักษร ที่ขึ้นต้นด้วย b และลงท้ายด้วย t เช่น bat และ but แต่ไม่จอ bt (เพราะตรงกลางไม่มีตัวอักษร)
    • เครื่องหมายดอกจัน (*) แทนตัวอักษรใดๆก็ได้จำนวนกี่ตัวอักษรก็ได้
      • เช่น “b*t” จะเจอทุกคำที่ขึ้นต้นด้วย b และ ลงท้ายด้วย t เช่น bat, but, bt, beast, boot
      • เช่น “*ing” จะเจอทุกคำที่ลงท้ายด้วย ing เช่น interesting, sing, ping
      • เช่น “a*” จะเจอทุกคำที่ขึ้นต้นด้วย a รวมถึง a ตัวเดียวด้วย
    • ลองใช้ผสมๆ กัน
      • เช่น “a?*” จะเจอทุกคำที่ขึ้นต้นด้วย a แต่ไม่รวมถึงคำที่มี a แค่ตัวเดียว
    • ถ้าอยากจะหาคำที่มีเครื่องหมายคำถาม หรือ ดอกจันจริงๆ ในข้อความ ให้ใส่เครื่องหมาย ~ นำหน้าอักษรนั้นๆ เช่น ต้องการหาคำว่า star*wars ให้ใส่ใน criteria ว่า “star~*wars”

    ตัวอย่าง:

    =MATCH(“*excel*”,A1:B10,0) จะเจอแถวแรกที่มีคำว่า  excel ผสมอยู่เป็นส่วนประกอบ
    เช่นเป็นคำว่า inwexcel หรือแม้แต่ i love Excel very much

    ถ้าอยากให้เป็น Cell Reference อาจใช้แบบนี้ก็ได้ครับ =MATCH(“”*”&C1&”*”,A1:B10,0)

    ฟังก์ชั่นที่สามารถใช้ Wildcard ได้มีดังนี้

    • AVERAGEIF
    • AVERAGEIFS
    • COUNTIF
    • COUNTIFS
    • DPRODUCT
    • DSTDEVP
    • DSUM
    • DVARP
    • HLOOKUP
    • MATCH
    • SEARCH
    • SEARCHB
    • SUMIF
    • SUMIFS
    • VLOOKUP

    ประเด็นที่สำคัญอีกอันคือ ฟังก์ชั่นที่รองรับ Wildcard มันจะไม่สนใจตัวพิมพ์เล็กพิมพ์ใหญ่นะครับ เช่น VLOOOKUP หาคำว่า INWEXCEL ธรรมดาก็เเจอคำว่า inwexcel ได้เหมือนกัน อันนี้อาจต้องระวัง

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

  • เทคนิคการลบข้อมูลเก่าที่ค้างอยู่ใน Pivot Table

    เทคนิคการลบข้อมูลเก่าที่ค้างอยู่ใน Pivot Table

    เวลาที่เราทำ Pivot Table เสร็จแล้วมีการเปลี่ยนแปลงข้อมูลใน Source Data อาจทำให้มีเหตุการณ์ที่มี Item ที่เคยอยู่ใน Source Data เก่าตกค้างใน Drop Down List ใน Row Label, Column Label, หรือ Filter ได้

    pivot-old-item

    หากเพื่อนๆใช้ Excel ตั้งแต่ Version 2007 ขึ้นไป สามารถแก้ไขเหตุการณ์ดังกล่าวได้ง่ายมากๆ โดย

    1. คลิ๊กขวาที่ตาราง Pivot
    2. เลือก PivotTable options
    3. เลือก Data tab
    4. ในหมวด Retain Items เลือก None
    5. OK แล้วกด Refresh ตาราง Pivot อีกครั้ง
    pivot-old-item2

    สำหรับ Excel Version เก่า สามารถไปโหลด Script VBA เพื่อแก้ไขได้ที่ http://www.contextures.com/xlPivot04.html

  • สรุปทุกสิ่งที่คุณควรรู้เกี่ยวกับ Pivot Table : ภาค 1 – การสรุปผลข้อมูลขั้นพื้นฐาน

    สรุปทุกสิ่งที่คุณควรรู้เกี่ยวกับ Pivot Table : ภาค 1 – การสรุปผลข้อมูลขั้นพื้นฐาน

    PivotTable คืออะไร?

    PivotTable (ฝรั่งอ่านว่า พิ-วอท-เท-เบิ้ล) เป็นเครื่องมือบน Excel ที่สามารถสรุปผลข้อมูลตามเงื่อนไขที่กำหนดได้อย่างง่ายดายและรวดเร็ว เช่น สามารถสรุปได้ว่าข้อมูลแต่ละประเภท มีผลสรุปที่เราสนใจเป็นเท่าไหร่ เช่น ผลรวม/จำนวนนับ/ค่าเฉลี่ย/ค่ามาก/น้อยสุด เป็นต้น

    สรุปทุกสิ่งที่คุณควรรู้เกี่ยวกับ Pivot Table : ภาค 1 – การสรุปผลข้อมูลขั้นพื้นฐาน 107

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

    ตัวอย่างการสรุปผล

    จากข้อมูลยุบยับที่ถูกเตรียมในลักษณะ Database เราสามารถนำมาสรุปผลใน PivotTable แบบง่ายๆ เช่นตารางนี้ได้เลย

    โหลดไฟล์ตัวอย่างได้ที่นี่ =>  inwexcel-pivot-practice.xlsx (แก้แล้ว)

    pivot-sample-01

    หรือสามารถเพิ่มมุมมองของข้อมูลที่เจาะรายละเอียดมากขึ้นได้อย่างง่ายดาย

    pivot-sample-02

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

    นี่ไงจากข้อมูลที่ Pivot ออกมาแล้ว ได้คำตอบ คือ 7 ชิ้น 3250 บาทนั่นเอง อยากรู้ของใครอีกบอกมาได้เลย มันสรุปออกมาให้หมดแล้ว!!

    PivotTable ไม่ยากอย่างที่คิด

    PivotTable มักถูกเข้าใจผิดจากคนทั่วๆไป ว่าเป็นเครื่องมือที่ใช้ยาก ทั้งๆที่จริงแล้ว เป็นเครื่องมือที่ใช้งานง่าย และรวดเร็วกว่าการใช้สูตรเป็นอย่างมาก แต่มีข้อเสียที่ด้อยกว่าการใช้สูตรเล็กน้อยคือ หากข้อมูลที่ต้นทางเปลี่ยนแปลงไป เราต้องกดปุ่ม Refresh ใน PivotTable ก่อน ผลในตาราง Pivot จึงจะ Update ตาม

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

    Step การใช้ Pivot Table

    • โหลดไฟล์ตัวอย่างได้ที่นี่ =>  inwexcel-pivot-practice.xlsx
    • ให้คลิ๊กที่ส่วนใดส่วนหนึ่งของตาราง Database ที่เตรียมไว้
      แล้วกด [Insert] –> Tables –> PivotTable
    • ตรวจดูก่อนว่ามันคลุมตารางครบหรือไม่ ?
      • ถ้าเตรียมข้อมูล Database ดีในแบบที่ผมแนะนำไปมันก็จะครบอยู่แล้ว
      • หากมีการใช้ Tableเป็นSource Dataมันจะขึ้นชื่อTableมาให้เลย ซึ่งไม่ต้องมานั่งตรวจให้เสียเวลาอีกเช่นกัน
    • เลือกได้ว่าจะให้ข้อมูลที่ Pivot แล้วไปสร้างเป็น Sheet ใหม่ หรือไว้ใน Sheet ที่มี
    Pivot-Source

    Field List

    เวลาสร้างตาราง Pivot ขึ้นมาแล้ว หัวตารางของข้อมูลต้นฉบับของเรา จะกลายเป็น Field List ของ PivotTable ซึ่งจะวางอยู่ด้านขวาบน เปรียบเหมือน Item ที่ให้เราหยิบไปใช้ทำอะไรได้มากมาย

    เราสามารถลาก Field List แต่ละอันลงไปใน Slot ทั้งสี่อัน ที่อยู่บริเวณข้างล่างขวามือ แต่ละอันมีความหมายดังนี้

    Pivot-FieldList-add
    • Report Filterทำหน้าที่คัดกรองข้อมูล ให้แสดงเฉพาะข้อมูลบรรทัดเดียวกับที่ Filter ไว้
    • Column Labelนำข้อมูลมาไว้ที่หัวตาราง Pivot (ด้านบน) เพื่อจัดกลุ่มแบบคอลัมน์ โดยมันจะแสดง item ทั้งหมดที่ไม่ซ้ำกันใน Field ที่คุณเลือกมาไว้ที่หัวตาราง Pivot (ด้านบน)
    • Row Labelนำข้อมูลมาไว้ที่ด้านซ้ายของตาราง Pivot เพื่อจัดกลุ่มแบบแถว โดยมันจะแสดง item ทั้งหมดที่ไม่ซ้ำกันใน Field ที่คุณเลือกมาไว้ที่ด้านซ้ายของตาราง Pivot
    • Valuesเป็นการคำนวณสรุปผล ทำได้หลายรูปแบบ เช่น SUM, Count, Average, Max, Min โดยอาจแสดงได้หลายรูปแบบ เช่น รูปแบบปกติ, %ของทั้งหมด, %ของแถว,%ของคอลัมน์, การรวมแบบสะสมค่า เป็นต้น

    โดยField List 1 อัน สามารถลากลงไปใช้ในช่อง Values ซ้ำได้มากกว่า 1 ครั้ง เช่น อันแรกเราเอาไปหาค่า SUM อันที่สองเราสามารถเอา Field เดิมไปหาค่า MAX หรือจะเปลี่ยนรูปแบบการแสดงผลให้ต่างกันได้ เป็นต้น

    ลองลาก Field List หลายๆ แบบ

    ลาก Field จำนวนเงินที่จ่าย มาที่ช่อง Value 1 อัน : มันจะทำการคำนวณสรุปผลข้อมูลให้ แบบนี้คือเหมือนการ SUM ข้อมูลทั้งหมดแบบไม่มีเงื่อนไข หรือการแยกประเภทใดๆ ทั้งสิ้น

    layout-1

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

    layout-1-2

    ลาก Field ผู้ซื้อ มาที่ Row Label : มันจะแสดง item ทั้งหมดที่ไม่ซ้ำกัน

    layout-2

    ลองลาก Field จำนวน มาที่ช่อง Value เพิ่มอีก 1 อัน :

    layout-3

    ลองลาก Field จำนวน มาที่ช่อง Value ซ้ำเป็นอันที่ 2 ก็ยังได้ : ในบทต่อไป ผมจะสอนวิธีเปลี่ยนจาก SUM เป็นการสรุปอย่างอื่นได้อีกครับ เช่น Count, Average

    layout-3-2

    ลองย้าย ∑ Values  : จาก Column Labels มาที่ Row Label

    layout-4

    ลากอีก Field สินค้า มาไว้ที่ Report Filter : แล้วลองเลือก Dropdown คัดกรองดู

    layout-5

    ลากอีก Field ผู้ขาย มาที่ Column Label : มันจะแสดง item ทั้งหมดที่ไม่ซ้ำกัน

    layout-6

    ลากอีก Field ผู้ขาย มาที่ RowLabel ให้ซ้อนกับผู้ซื้อ : มันจะแสดง item ทั้งหมดที่ไม่ซ้ำกัน

    layout-7

    ลองย้าย ∑ Values : ไปยังหลายๆ ตำแหน่ง เช่น สลับลำดับใน Row หรือ ย้ายกลับไป Column

    layout-7-2
    layout-7-3

    วิธีการแปลความหมายตาราง Pivot Table

    ตัวอย่าง ผมลองลาก Field ต่างๆ มาลง 4 Slot ข้างล่างตามรูป

    Pivot-Basic

    วิธีตีความคือให้ดูว่า ข้อมูลที่กำลังสรุปผลเป็นการ Summary จาก Field ไหนบ้าง
    โดยต้องดูว่าข้อมูลมัน Cross กันแบบไหน ทั้ง 4 slot เลย

    ตัวอย่าง ในช่อง C12 (1000) หมายถึง

    นาย c (row) / ซื้อสินค้าใดๆก็ตาม (เลือก All แปลว่า ไม่ได้ filter เจาะจงสินค้า) / กับ sales ค (column) / ด้วยเครดิตการ์ด (row) / เป็นจำนวนรวม 1000 บาท (Values – สรุปด้วย SUM)

    เห็นมั๊ยครับว่า PivotTable นี่สามารถอ่านผลลัพธ์ได้ง่ายและรวดเร็วจริงๆ!!

    ใครอยากรู้ว่า Pivot Table ทำอะไรได้อีก อย่าลืมอ่านต่อได้ที่ตอนที่ 2 ครับ

    Video ประจำตอน

    มี 3 ไฟล์ด้วยกัน ตั้งใจดูให้ดีล่ะครับ ยาวหน่อย แต่อัดแน่นด้วยเนื้อหาครับ
    สรุปทุกสิ่งที่คุณควรรู้เกี่ยวกับ Pivot Table บน Excel ตอนที่ 1/3 :
    http://www.youtube.com/watch?v=_KB4LAMQbWU

    สรุปทุกสิ่งที่คุณควรรู้เกี่ยวกับ Pivot Table บน Excel ตอนที่ 2/3 :
    http://www.youtube.com/watch?v=rrUerQbuAJ8

    สรุปทุกสิ่งที่คุณควรรู้เกี่ยวกับ Pivot Table บน Excel ตอนที่ 3/3 :
    http://www.youtube.com/watch?v=2VJ7eTKmzK4