บันทึกการใช้งาน Google Sheet 104 สำหรับนักวิทยาศาสตร์ข้อมูล Data Science - sprint 01

Image placeholder
แวะมาทักทายกันได้


Pivot Table Formula

Data analyst ใช้ Pivot Table ในการวิเคราะห์ข้อมูลและ ทำรายงานในโปรแกรม Excel / Google Sheet

???? Excel จะ feature ครบกว่า ถ้ายิ่งใช้คู่กับ Power Query + Power Pivot (Data Model)

การสร้าง Report ด้วย Pivot Table จะต้องเข้าใจความแตกต่างของ Dimension และ Measurement ก่อน


Dimension คืออะไร

Dimension คือ คอลัมน์แบบ Categorical Data ที่ใช้แบ่งข้อมูลเป็นกลุ่มๆ เช่น region, category, zipcode , city, country


Measurement คืออะไร

measurement คือ คอลัมน์แบบ numerical data สามารถทำพวก aggregation ได้ เช่น sum, average, median เป็นต้น

สูตรการใช้ pivot table รูปแบบที่ง่ายที่สุด คือ 1 dimension x 1 measurement

การทำรายงานยอดขาย salse โดยแบ่งตาม region

Region อยู่ในช่อง Row

Sales อยู่ในช่อง Values (default aggregation ของคอลัมน์ measurement คือการหาผลรวม sum())



ถ้าเราเปลี่ยน dimension Region เป็นตัวแปรอื่นๆ เช่น City Country Category Segment ก็จะสามารถตอบคำถามต่อไปนี้ได้

???? City , Country, Category, Segment ไหนมียอดขายสูงที่สุด

ในการทำงานจริง Data analyst จะหมุนข้อมูล (เปลี่ยน dimension, measurement) ไปเรื่อยๆ เพื่อหา insights ที่ช่วยให้ธุรกิจเติมโตขึ้น รายได้สูงขึ้น รายจ่ายลดลง หรือ insight ที่ช่วยให้ลูกค้าอยู่กับแบรนด์เรานานขึ้น เป็นต้น


Intro to Pivot Table Google Sheet



นี่คือ Dataset เพื่อจะเอามาทำ Pivot Table

โดยที่ Region คือ ภูมิภาคที่ขายของได้

Rep คือ พนักงานขาย

Item คือ สินค้าที่ขายได้

???? tip : กด Ctrl จะช่วยให้เช็คข้อมูลในตารางของ google sheet ได้

???? tip : กด Ctrl + Shift จะ hilight ข้อมูลที่มีในตาราง

*ทำเพื่อ investigate ข้อมูลคร่าวๆ แบบ เร็วๆได้

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



โดยเรานิยามแต่ละคอลัมน์ไว้ดังนี้

Dateข้อมูลที่เป็นวันที่
Dimensionข้อมูลที่ไม่ใช่ตัวเลข
Measurementข้อมูลที่เป็นต้วเลข




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

ขั้นตอนการเขียน code

  • จะทำการ list รายการทั้งหมดที่มีอยู่ในตารางในส่วนของ dimension ที่เป็น unique ไม่ซ้ำกัน เช่น พื้นที่

ใช้คำสั่ง =UNIQUE(column ที่สนใจทั้งหมด) เพื่อดูค่าที่ไม่ซ้ำกัน



ทำการ Breakdown ค่า Sum ของข้อมูลในตาราง โดยแบ่งตาม Region ในขั้นตอนที่แล้ว โดยใช้คำสั่ง sum() ก่อน



  • เราจะใช้ =SUMIF() เพื่อหาผลรวมแบบมีเงื่อนไข =sumif**(**B:B, I8,G:G**) แปลความหมายได้ว่า ให้เข้าไปหาข้อมูลใน column Region ทั้งหมด โดยมีเงื่อนไขว่าจะต้องเท่ากับ I8 แล้ว Sum ค่าทั้งหมดที่ตรงตามเงื่อนไขที่อยู่ในคอลัมน์ G ทั้งหมด**



  • ลอง recheck อีกครั้งโดยใช้ sum ธรรมดา

???? slide and drive คือการแบ่งข้อมูลตามกลุ่ม


Dimension เป็นตัวตัดแบ่ง Measurement


มาลองหา =UNIQUE() ของพนักงานขายกันบ้าง เพื่อดูจำนวนการขายของแต่ละคน โดยสามารถทำตารางออกมาเป็นแบบนี้



คราวนี้เราจะต้องใช้การนับแบบมีเงื่อนไข โดยจะใช้ =COUNTIF() ในช่อง Column ที่ชื่อ Count



คราวนี้มาหาผลรวมของพนักงานขายของแต่ละคน โดยใช้ =SUMIF() โดยตีความหมายได้ว่าให้ฟังก์ชันวิ่งเข้าไปหา ชื่อพนักงานขายในคอลัมน์ C โดยมีเงื่อนไขว่าตรงกับชื่อ John ตามตัวอย่าง แล้วก็ให้ดึงข้อมูลจากคอลัมน์ G ออกมา SUM กัน



คราวนี้ก็หาค่าเฉลี่ยการขายแต่ละครั้งออกมาเลย ก็นำ ผลรวมหารด้วยจำนวนการขาย ได้เลยก็จะได้ค่าเฉลี่ย



แต่จากการหาค่าผมรวม ค่าเฉลี่ยของแต่ละคอลัมน์ที่ผ่านมา เราสามารถใช้ pivot table โดยไม่ต้องทำหลายขั้นตอนโดยทำดังนี้

  • HILIGHT ตารางโดยการกด Shift + A
  • ไปที่เมนู Insert แล้วเลือก Pivot Table




จะเห็นว่ามันสร้าง Sheet ใหม่ขึ้นมา โดยสังเกตจะมี sidebar ที่อยู่ด้านข้างสามารถใช้เลือกข้อมูลที่ต้องการดูได้



Google Sheet มีความฉลาดอยู่อย่างหนึ่งมันสามารถที่จะ Preview ตารางเพื่อแสดงข้อมูลให้เราดูก่อนได้โดยไปที่เมนูด้านข้างตรง Average of Units for each Rep เพื่อจะดูข้อมูลของคอลัมน์ Rep เหมือนกับทำไปก่อนหน้านี้ กดเครื่องแว่นขยายบวก 



แต่เราจะมาสร้าง Pivot Table ด้วยตัวเองกัน

สมมติว่าเราอยากได้ผลรวมโดยแยกตาม Region ตามตารางที่เราสร้างไปก่อนหน้านี้

เราสามารถไปที่เมนูด้านข้างเหมือนเดิม แล้วจะเห็นคำว่า Row โดยจะเอา Region มาแปะ



เราเลือก Row → Add → Region 



เราก็จะได้ผลลัพท์ออกมาเป็น 



แล้วเราก็ใส่ Value เข้าไปโดยทำแบบเดียวกันกับ Row 



เลือก Total 



เท่านี้เราก็จะได้ ตารางเหมือนเมื่อสักครู่นี้เลย และตรง Value ยังสามารถเปลี่ยนค่าเป็นค่าทางสถิติอื่นๆได้อีกตามที่เราอยากรู้ เช่น ค่าเฉลี่ย



???? เวลาที่เราเลือก Value ตามที่คอลัมน์ที่เป็น Dimension มันเอง ค่า Default ในการเลือกของมันเป็น CountA จำนวนนับ เพราะมันไม่ใช่ตัวเลข ทำได้แค่ Percent ของข้อมูล ปุ่ม Add สามารถที่จะเพิ่มได้เรื่อยตามที่มันสามารถคำนวนได้


Filter Pivot Table


???? ค่า Measurement คือ การหาค่าสถิติเบื้องต้น แล้วถ้าหากว่าเราอยากจะแบ่งตามกลุ่ม ก็จะนำ Dimenstion มาซอยย่อย (Slide And Dive) โดยนำ Dimension ไปใส่ที่ Row



สามารถกด Filter → Add → เลือกกลุ่มที่ต้องการแสดง อย่างในภาพด้านล่างก็จะเป็นการเลือก เฉพาะค่า พนักงานขายส John มาแสดง



คราวนี้มาลองทำ pivot table แบ่งแยกตามยอดขายรายเดือนกันบ้าง เราจะต้องดูจาก OrderDate นั้นเอง โดยมีขั้นตอน ตามที่ได้ทำมา คือ

  1. เลือก ROW แล้วเลือก OrderDate
  2. เลือก Value แล้วเลือก Total
  3. มา Right Click ที่ Table แล้วเลือก Create Pivot Date Group แล้วมันจะมีให้เลือก ว่าอยากได้ Range แบบไหน จะขอเลือกเป็นแบบ Month



เราสามารถเอา Data ที่ทำ Pivot Table มาแล้วไปทำ Chart ได้ด้วย โดยไปที่เมนู Insert → Chart



ตาราง Crosstab

ตาราง crosstab คือ ตารางที่จะ cross กันโดยที่จะเปรียบเทียบว่าสินค้าตัวไหน ขายดีไม่ดี โดยตาราง crosstab เกิดจากการที่ข้อมูล 2 คอลัมน์ เอา 1 คอลัมน์มาทำเป็นแกน Row และ อีกตัวหนึ่งมาเป็น Column

โดยเราสามารถเลือก ข้อมูลที่ pivot table ได้เช่นเดียวกัน โดยจะมี ROW และ COLUMN ให้เลือก



ลองเลือกค่าเปรียบเทียบกัน สมมติว่าอยากรู้ว่า ผลรวมเทียบ Region กับ Item ที่มี Value ด้วย Total โดยอยากรู้ยอดขายเป็น percent เราจะเลือก Value ด้วย % of Column



เราสามารถเอาข้อมูลมาทำ chart ได้



Calculated Field

สมมติว่าในตารางข้อมูล Original อยากจะหากำไร Profit ของการขายแต่ละพื้นที่ด้วย เราสามารถใช้ Function Calculated Field ของ google sheet ได้ด้วยเพื่อคำนวน Profit ขึ้นมา

สมมติเราคิดว่ากำไรจะได้ประมาณ 20% ของยอดขายแต่ละพื้นที่ แต่ข้อมูลเราไม่ได้มีคอลัมน์ Profit เราจะต้องสร้างมันขึ้นด้วย Pivot Table



คราวนี้เราจะมาสร้าง Calculated Field ให้เราเลือก Value → Calculated Field



มันจะมีช่องให้ใส่ สูตรที่เราสามารถเขียนเองได้เลย สังเกตได้ว่าจะมี Column Calculated Field เพิ่มขึ้นมา



ให้เราใส่สูตรใช้ช่อง Formula ว่า =SUM(Total*20%) สังเกตว่า Total จะเป็นคอลัมน์จาก Sheet Original



มาดูอีกตัวอย่าง สมมติว่าเราอยากคำนวนภาษี เราจะไม่คำนวนเองที่ช่องแต่จะใช้ Calculated Field เช่นเดิม



Slicer

มีเครื่องมือตัวหนึ่งที่น่าสนใจสำหรับการกรองข้อมูล ชื่อว่า Slicer มันจะช่วยในการ Filter ข้อมูลที่ต้องอยากเห็นได้โดยใช้ Sidebar ในการจัดการการ Filter ได้ โดยไปที่เมนู Data → Add a Slicer



จะมีเมนูด้านข้าง ให้เลือก Column เพื่อจะกำหนดการกรอง ข้อมูลได้ 



เมื่อเราเลือก Column แล้วเราจะเห็น virtual filter ให้เราเลือก กรองข้อมูลสามารถที่จะลากไปมาได้ด้วย



จะได้



คราวนี้มาดูที่ Pivot Table บ้าง จะถูก filter ด้วยเช่นกัน




คอร์สนี้ดีมากกกก (ไก่ ล้านตัว)  ใครอ่านจบ แนะนำว่าให้ไปสมัครเรียน ติดตามได้ที่ link ด้านล่างนี้เลย 

Course Online DATA ROCKIE Bootcamp

แวะมาทักทายกันได้
donate

Categories: Tutorial Tags: #Data Science , 770