Data Sci Boot Camp Batch#11 : ใช้ Spreadsheet / Excel มา 10 ปี เพิ่งรู้ว่าทำแบบนี้ได้

สำหรับ Blog นี้ ถ้าให้จดทั้งหมดคงยากครับ มาวันนี้ผมจะจดสิ่งที่ไม่รู้ และมันโคตรจะมีประโยชน์เลย ไม่งั้นคงใช้แบบเดิมๆไปอีก 10 ปี อันนี้ Base on Google Spreadsheet เป็นหลักตามที่แอดทอยสอนน้า ถ้าอันไหน Excel ทำได้ ผมจะใส่ไว้ด้วยครับ

Spreadsheet / Excel

เปิด Google Sheet / Doc ใหม่ แบบไว้ๆ sheets.new / docs.new

• เป็น Fundamental ต้องรู้อยู่แล้ว ทำได้ระดับนึง พวก Job Description เลยไม่ได้ใส่ลงไป
• มองเป็น versatile (เอนกประสงค์) / Generalist Software เอาไปทำได้ทุกอย่าง เก็บข้อมูล วิเคราะห์ Visuzalize โดยข้อมูลจะอยู่ในรูปแบบ Structure Data เป็นตาราง11

Structure Data เป็นตารางนะ โดยในแต่ละ Column ต้องมี DataType กำกับ

• วันที่ ตาม ISO8601 ใน Blog มี Tag Coding นะ
• String
• ตัวเลข Integer / Decimal

การที่เราต้องรู้ Data Type เพราจะได้เลือกมุม dimension (จัดกลุ่ม) หรือ measure (ตัวเลข) พอรู้แล้วเลือก operation ต่างๆ เพื่อหา Insight math / stat / date เป้นต้น
แต่ต้องดูบริบทด้วยนะ เช่น Zip Code ตัวเลข 10170 แต่dimension เอา 2 เขต/อำเภอมารวมกันได้ไม่ได้

Common Function ที่ควรรู้สำหรับ เอาตัวรอด

• รู้หลายตัว เอามาใช้งานร่วมกัน Powerful มากๆ

vlookup / basic stat เอาตัวรอดของผมเลยนะ

• การเป็น Data Analyst ที่ดี เราต้องรู้ว่าใช้อะไร จะได้ Filter สิ่งที่ไม่ต้องการออกไป ลดเวลาที่เสียไป

หลังจากนี้ ผมใส่สิ่งที่ไม่รู้ และมันโคตรจะมีประโยชน์เลย ไม่งั้นคงใช้แบบเดิมๆไปอีก 10 ปี

สิ่งที่ผมเพิ่งรู้

- FORMULATEXT

บอกว่า Cell ที่สนใจผูกสูตรอะไรไว้ อันนี้ทั้งใช้ได้ทั้ง Google Spreadsheet / Excel

=FORMULATEXT(Cell ที่ผูกสูตร)

=FORMULATEXT(A5)

- ArrayFormular

เคยใช้ไหม ที่เรามีสูตรที่ต้องใช้กับหลายร้อย หลายพัน Row แล้วใช้วิธีรูดๆ ลงมา แล้วค้างไปสักพัก พอมีใครแก้อะไรระหว่างทางสูตรพังไปหมด พระเอกของเราเลยครับตัว Array Formular โดยขั้นตอนง่ายๆ

1. ทำให้ work ก่อน 1 Row
2. แก้ Range ของสูตรข้อที่ 1 ให้คลุมทั้ง Column
3. กด Ctrl+Shift+Enter เพื่อให้ตัว Google Sheet มัน Apply สูตรลงไป

ข้อดี ลดข้อผิดพลาด กันคนมือบอลไปแก้สูตร มันอยู่ที่จุดเดียวแล้ว / ทำให้ Spreadsheet เราทำงานเร็วขึ้น สูตรมันน้อย
ข้อด้อย บาง Function เช่น AND / OR / SUM / COUNT / AVERAGE พวกรวมเป็นค่าเดียว ยังใช้งานไม่ได้นะ

Sample: ArrayFormular

สำหรับคนสาย M365 ตัว Excel ทำได้มาหลายปีแล้วครับ หลังเรียนจบลองไปหาดูมี https://www.thepexcel.com/new-world-of-array-formula/ เขียนไว้นานแล้ว

สำหรับข้อจำกัดของ ArrayFormular พวก AND / OR มันมีท่าเสี่ยงนะ

• ใช้ * แทน AND
• ใช้ + แทน OR

=ArrayFormula(if((E4:E23 >= 256) * (F4:F23 >=12),1,0))

แบบนี่ ตัวอย่างเต็มๆ อันนี่้เลย

- Filter & Sort

สามารถเลย ว่าปกติกดจาก Pivot / GUI ที่มันให้มาตลอด มันมีสูตรด้วยยยยยยย

• Filter มันเหมือน SQL Where กรองตามเงื่อนไข

=FILTER([ช่วงข้อมูลทั้งหมด], [Column ที่ต้องการตรวจสอบ], [ค่าให้ Filter])
//เงื่อนไขเดียว
=FILTER(A3:E22
      , C3:C22="AMD")
//อันนี้แบบมีหลายๆเงื่อนไข AMD และเป็น Ryzen 5
=FILTER(A3:E22
      , C3:C22="AMD"
      , D3:D22="Ryzen 5")

• Sort มันเหมือน SQL Order by แต่มันต้องมาจำนำว่า 3 / 5 Column อะไร

=Sort([ช่วงข้อมูลทั้งหมด], [Column], [รูปแบบการเรียง True=Asending ตามตัวอักษร ถ้าไม้ใช่ False])
//Field เดียว 
=Sort(A3:E22
    , 3,true)
//หลาย Field เรียงตาม Brand  + ตาม Quality จากมากไปน้อย (False)
=Sort(A3:E22
    , 3,true
    , 5,false)

M365 ตัว Excel มีเหมือนกัน Filter / Sort

Sample: Filter / Sort

- Named Range

จากเดิมต้องต้องอ้างอิงแต่ละ Cell ตาม Column และ Row ใช่ไหมครับ แต่เราสามารถกำหนดเป็นชื่อ ที่จดจำได้ง่ายๆ อารมณ์เหมือนตัวแปร ที่จะเอาไปอ้างอิงสูตร แถมข้อดีอีกอย่าง มันลดการ Copy ทำให้สูตรไม่พังด้วย อ้างเป็นตัวแปร โดยใน Google Sheet Named Range

• ห้ามขึ้นต้นด้วยตัวเลข ห้ามมี Space แต่ใช้ _ มาช่วยแยกได้
• เป็นไปได้ควรตั้งชื่อ Named Range ตัวใหญ่ สื่อถึงข้อมูล มีข้อตกลงชัดเจน

สำหรับใน M365 Excel ทำได้นะ Define and use names in formulas / Names in formulas

ลองดู Sample ได้นะ

- Convert To Table

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

เพราะการอ้างอิงสูตรมันง่าย และสื่อมากกว่ามากกกกกกกก ล้านตัว

• NameRange มันต้องสร้าง NameRange หลายอันก่อน

=FILTER(MOBILELIST, MOBILE_BRAND="Samsung")

• Table - ทำครั้งเดียวจบ เดี๋ยวมัน Hint เอง

=FILTER(SmartPhone, SmartPhone[Brand] = "Samsung")

หรือพวก Sort แบบแรกต้องมานึก 3 คือ อะไร

=Sort(MOBILELIST, 3, false)

ใช้ Table สิ ว้าวซ่ามาก

=Sort(SmartPhone, SmartPhone[Price ($)], FALSE())

- ImportData

เราสามารถเอาข้อมูลจาก web / csv / feed เข้ามาได้ในนี้เลย ปกติผมเขียน C# / Python เอาตลอด พอมาเรียนว้าวเลย โดยมีหลายแบบ เช่น

• IMPORTDATA ดึงจาก csv

=IMPORTDATA([CSV PATH]) 
=IMPORTDATA($I$2)     //$I$2 เป็น Cell ที่เก็บ path ไฟล์ csv ไว้นะ

• IMPORTHTML เอา HTML มาลง Spreadsheet

=IMPORTHTML([HTML PATH],[TAG],[ตัวที่เท่าไหร่ของ TAG นั้นๆ])
=IMPORTHTML(C4,"table",4)

• IMPORTFEED - เอาข้อมูลที่เป็น Structure เช่น HTML / XML เข้ามา

=IMPORTFEED([PATH TO STRUCTURE HTML/XML],[XPATH],[Include Header], [LIMITS]

Sample: IMPORTDATA / IMPORTHTML / IMPORTFEED ดู Sample เลยชัดเจนครับ

สำหรับ M365 Excel ทำได้เหมือนกันคร้าบ Import data from a CSV, HTML, or text file

- Named Function

นอกจาก Named Range แล้ว ถ้าเรามีสูตรที่ต้องใช้บ่อยๆ ก็ทำได้นะ

• Data > Named Function
• parameter
• กำหนดสูตร

ลองทำสูตรสร้าง User Email

• เดิม

=ArrayFormula(LOWER(C4:C28) &"." & LOWER(LEFT(B4:B28,3)) & "@ds.com")	

• ใหม่

=ArrayFormula(CREATEUSERMAIL(C4:C28,B4:B28,$I$2))

Sample: Named Function / Named Function CREATEUSERMAIL

- Query / Dynamic Query

• Query - เอาไว้ข้อมูลข้อมูล Syntax เหมือน SQL เลยแต่ไม่มี FROM Clause นะ ตอนเรียน ผมดันชอบพิมพ์ติด FROM 555 เลยจำแม่นๆ การอ้างอิง Column เป็น Index A B C ...

=QUERY([DATA SOURCE / NAME RANGE / TABLE] , [YOUR SQL], [INCLUDE HEADER])
//เอาหมด
=QUERY(IMDB_SELECT, "SELECT * ")
//เฉพาะ Column + Limit
=QUERY(IMDB_SELECT, "SELECT A , B, G LIMIT 3", true)
//Group By
=QUERY(IMDB, "SELECT D
                   , AVG(E) 
              WHERE D IS NOT NULL 
              GROUP BY D")
//Where + Order By
=QUERY(IMDB, "SELECT A, B, C, D, E
              WHERE D IS NOT NULL 
              ORDER BY D, E DESC")

• Dynamic Query เหมือนกัน Query แต่เราจะปรับสูตรให้เอาค่าจาก Cell อื่น / Combo / Slicer ได้
  จากตัวอย่างจะดึงข้อมูลจาก C9 / D9 ทำให้ข้อมูลเราดูมีลูกเล่น

=QUERY(IMDB, "SELECT * WHERE F LIKE '%" &C9&"%' AND C <"&D9)

Sample Query & Dynamic Query

สำหรับใน M365 ยังไม่มีตัวชนตรงๆ มันจะมีอีกตัว Power Query แต่อันนี้มันจะใหญ่กว่าไปเลย จัดการข้อมูล + Visualize ด้วย

- REGEX

Recap RegEx มีวิธีการหาข้อมูล โดยเค้ามีข้อตกลงประมาณนี้

• ^A ขึ้นต้นด้วย A
• s$ ลงท้าย s
• a.b ขึ้นต้นด้วย a ลงท้ายด้วย b
• * match zero or more
• + match one or more
• ? mathch zero or on
• {5} match ทั้ง 5ตัว
• {3,5} ตำสุด 3 ตัว และไม่เกิน 5 ตัว

โดยสามารถกำหนดเป็นช่วงได้ เช่น

• [ABC] เอา A B C
• [A-Z] A-Z ตัวใหญ่ ถ้าตัวเล็ก [a-z]
• [A-z] เอาทั้งตัวเล็กใหญ่หมด
• [0-9] 0-9 หรือ /d

ตัวอย่าง

• [0-9]{5} อันนี้เอาเลข 00000-99999
• Address\s([\d\s\w]+) \d{5} ดึงข้อมูลที่อยู่ระหว่างคำว่า Address และ Zip Code

เหมือนจะเคยเขียน Blog ไว้ เดวหาเรื่อง RegEx เจอจะเอามาแปะอีกที

Google SpreadSheet เอา REGEX มาช่วย กรณีข้อมูลมันเยอะ ถ้า Where ถือ หรือ IF เอาจะไม่ไหวได้ใน Google Spreadsheet มีของช่วยตามนี้

Sample REGEX

สำหรับใน M365 Excel ชนหมดเลย REGEXTEST / REGEXEXTRACT / REGEXREPLACE