เทพเอ็กเซล ช่วย "ปลุกเทพ Excel ในตัวคุณ"
0
  • No products in the cart.
Highlights : บทความแนะนำPower QueryDAX FormulaExcel Chart & Visualization

แฉ 10 ความลับของ EXCEL ภาค3 : เปิดโลก Excel ใบใหม่ที่หลายคนไม่รู้จัก

แฉ 10 ความลับของ EXCEL ภาค3 : เปิดโลก Excel ใบใหม่ที่หลายคนไม่รู้จัก 1

บทความนี้เป็นภาคต่อจากบทความที่ผมเคย post ในปี 2014 (เกือบ 6 ปีมาแล้วเหรอเนี่ย!! ) ซึ่งเกี่ยวกับเรื่องลับที่หลายคนไม่รู้ใน Excel ซึ่งได้รับความสนใจอย่างสุดๆ (ใครยังไม่เคยอ่านก็ลองเข้าไปดูนะ)

ตอนนี้ก็ขึ้นปี 2020 แล้ว ผมคิดว่าได้เวลาที่จะ post ภาค 3 เป็นของขวัญปีใหม่ให้กับทุกท่านในปี 2020 นี้เลยละกัน

แล้ว 10 เรื่องที่ผมคิดว่าเจ๋ง แต่หลายคนไม่รู้ในช่วงนี้ มีอะไรบ้าง? มาดูกันเลย!! (บอกเลยว่าหลายอันนี่ Advance มากๆ นะ และบางอันอาจมีใน Excel version ใหม่ๆ หน่อยนะครับ)

สารบัญ

Secret 1: Flash Fill คือสุดยอดเครื่องมือขี้โกงที่ใช้ง่ายที่สุด

Flash Fill คือ เครื่องมือที่สามารถเลียนแบบ Pattern ที่เราพิมพ์ให้เป็นตัวอย่างได้อย่างง่ายดาย โดยเครื่องมือนี้มีมาตั้งแต่ Excel 2013 เป็นต้นไป

ยกตัวอย่างเช่น หากผมมีข้อมูลตามตัวสีดำ แล้วผมพิมพ์ตัวอย่างผลลัพธ์ที่ต้องการตามตัวสีแดงแล้วกด Enter

แฉ 10 ความลับของ EXCEL ภาค3 : เปิดโลก Excel ใบใหม่ที่หลายคนไม่รู้จัก 2

จากนั้นเราแค่กดปุ่ม Ctrl+E เพื่อเรียกใช้ Flash Fill แค่นั้น Excel ก็จะจัดการเสกผลลัพธ์ให้เราเลยอย่างง่ายดาย

แฉ 10 ความลับของ EXCEL ภาค3 : เปิดโลก Excel ใบใหม่ที่หลายคนไม่รู้จัก 3

Tips : ผมแนะนำว่าใส่ตัวอย่างบรรทัดที่ค่อนข้างเห็น Pattern ชัดเจนที่สุด (เช่น ไม่ควรไปใส่บรรทัด Braben.b เพราะมันอาจคิดว่า .b คือชื่อตัวแรก)

และเราใส่ตัวอย่างมากกว่า 1 ตัวได้นะครับ เพื่อให้ Flash Fill ไม่สับสนว่าต้องการอะไรกันแน่

Secret 2: แสดงสูตรจาก Cell ที่สนใจด้วย FORMULATEXT

สมัยก่อนเวลาที่เราอยากจะเขียนสูตรลงใน Cell เราจะต้องใช้เครื่องหมาย ‘ นำหน้า เพื่อให้ Excel มองเป็น text เพื่อจะได้ไม่ต้องคำนวณจริงๆ

แต่ใน Excel 2013 เป็นต้นมา มีฟังก์ชันชื่อ FORMULATEXT ช่วยทำหน้าที่นี้ได้อย่างง่ายๆ เช่น =FORMULATEXT(cell ที่ต้องการ) ได้เลย

เช่น ผมใช้ FORMULATEXT ในช่อง E3 เพื่อให้แสดงสูตรจากช่อง B3 ดังนี้

แฉ 10 ความลับของ EXCEL ภาค3 : เปิดโลก Excel ใบใหม่ที่หลายคนไม่รู้จัก 4

Secret 3: CELL ฟังก์ชันสารพัดประโยชน์

ตอนแรกคิดว่าจะเขียนถึงฟังก์ชันใหม่ๆ อย่างเช่น

  • XLOOKUP ที่มาแทน VLOOKUP
  • TEXTJOIN ที่มาช่วยให้เชื่อมข้อความแบบมีตัวคั่นได้ง่ายมาก
  • IFS และ SWITCH ที่มาแทนการใช้ IF ซ้อนกันเยอะๆ

แต่ว่าคิดว่ามีคนเขียนถึงฟังก์ชันพวกนี้เยอะแล้วและมันดันเป็นฟังก์ชันที่ต้องมี Excel version ที่ใหม่สุดๆ งั้นมาเล่าฟังก์ชันที่มีนานแล้วแต่หลายคนอาจจะไม่รู้ดีกว่า

ฟังก์ชันที่จะมาเล่าในบทความนี้ก็คือฟังก์ชัน CELL ซึ่งมันสามารถดึงข้อมูลต่างๆ จาก Reference ที่ระบุได้มากมาย

แฉ 10 ความลับของ EXCEL ภาค3 : เปิดโลก Excel ใบใหม่ที่หลายคนไม่รู้จัก 5

ซึ่งเราสามารถนำมาประยุกต์หาได้ทั้งชื่อไฟล์และชื่อชีทที่เขียนสูตรอยู่ได้ (แต่ต้อง save ไฟล์ก่อนนะ) เช่น

สมติว่าผม save ไฟล์ชื่อ testcell.xlsx ไว้ที่ folder d:\thepexcel จะเป็นแบบนี้

ชื่อชีท

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

ชื่อไฟล์

=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",CELL("filename",A1))-1-FIND("[",CELL("filename",A1)))
แฉ 10 ความลับของ EXCEL ภาค3 : เปิดโลก Excel ใบใหม่ที่หลายคนไม่รู้จัก 6

แต่ที่หลายคนไม่รู้ก็คือ หากเราไม่ระบุช่อง reference มันจะอ้างอิงไปที่ Cell ล่าสุดที่มีการ Enter ข้อมูลลงไปได้โดยอัตโนมัติ เช่น

แฉ 10 ความลับของ EXCEL ภาค3 : เปิดโลก Excel ใบใหม่ที่หลายคนไม่รู้จัก 7

Secret 4: Power Query คือสุดยอดเครื่องมือดัดแปลงข้อมูลให้อยู่ในรูปแบบ Database

หากเราต้องการจะสรุปข้อมูลด้วย Pivot Table หนึ่งในขั้นตอนที่ควรจะต้องทำก่อนคือเตรียมข้อมูลให้อยู่ในรูปแบบ Database ซะก่อน

สมมติว่าผมต้องการใช้ Pivot Table สรุปข้อมูลที่ดึงจากเว็บ ข้อมูลประชากรไทยรายจังหวัดในเว็บสำนักงานสถิติแห่งชาติหน้านี้ http://statbbi.nso.go.th/staticreport/page/sector/th/01.aspx

ในนั้นจะมีไฟล์ จำนวนประชากรจากการทะเบียน จำแนกตามอายุ เพศ และจังหวัด พ.ศ. 2561 ซึ่งเป็นไฟล์ Excel หน้าตาแบบนี้

แฉ 10 ความลับของ EXCEL ภาค3 : เปิดโลก Excel ใบใหม่ที่หลายคนไม่รู้จัก 8
http://statbbi.nso.go.th/staticreport/Page/sector/TH/report/sector_01_11101_TH_.xlsx

ปกติแล้วคำสั่ง Get Data from Web ใน Power Query จะใช้ดึงข้อมูลจากหน้าเว็บไซต์ได้ แต่ว่าจริงๆ แล้วมันใช้ดึงไฟล์ที่วางไว้ในเว็บได้โดยตรงโดยไม่ต้อง save ไฟล์ออกมาก่อนก็ได้นะ

ยกตัวอย่าง เช่น เราสามารถใช้ Get Data From Web ดึงข้อมูลไฟล์นั้นมาได้เลย โดยไม่ต้อง save ออกมาด้วยซ้ำ

แฉ 10 ความลับของ EXCEL ภาค3 : เปิดโลก Excel ใบใหม่ที่หลายคนไม่รู้จัก 9

จากนั้นใส่ URL ของไฟล์ที่ต้องการลงไป เช่น http://statbbi.nso.go.th/staticreport/Page/sector/TH/report/sector_01_11101_TH_.xlsx แล้วกด ok

แล้วเลือกที่ข้อมูล แล้วกด Transform หรือ Edit ได้เลย

แฉ 10 ความลับของ EXCEL ภาค3 : เปิดโลก Excel ใบใหม่ที่หลายคนไม่รู้จัก 10

โดยที่พอดึงข้อมูลแล้วอยากจะได้ข้อมูลประชากรแต่ละช่วงอายุเป็นรายจังหวัดให้ผลลัพธ์มีหน้าตาแบบนี้

แฉ 10 ความลับของ EXCEL ภาค3 : เปิดโลก Excel ใบใหม่ที่หลายคนไม่รู้จัก 11

หากเราใช้สูตรแก้ปัญหานี้จะยุ่งยากมาก แต่ถ้าหากใช้ Power Query แทน เรื่องนี้ก็จะง่ายกว่ามาก ดังนี้

หลังจากกด Transform/Edit แล้วเราสามารถทำตามขั้นตอนดังต่อไปนี้ เพื่อให้ผลลัพธ์ออกมาได้ตามที่เราต้องการได้เลย (รายละเอียดวิธีทำพร้อมรูปประกอบดูได้ที่นี่)

  1. ปรับเรื่องหัวตารางให้เหมาะสม เช่น Remove Top Row ที่ไม่เกี่ยวออก / Use First Row as Header เพื่อปรับหัวตาราง
  2. ตัดคอลัมน์ที่ไม่ต้องการทิ้ง โดยตัดคอลัมน์ ภาค และ รวม ทิ้ง
  3. ถมช่องว่างในคอลัมน์จังหวัด
  4. จากนั้น Filter เอาบรรทัดที่ไม่ต้องการออก
  5. พลิกเอาอายุหลายๆ คอลัมน์ ให้มารวมกันในคอลัมน์เดียวด้วย Unpivot ซึ่งเครื่องมือนี้แหละคือขั้นตอนเด็ดสุดที่เครื่องมืออื่นทำยากมาก แต่ Power Query ง่ายมากๆ
  6. เปลี่ยนชื่อคอลัมน์ให้เหมาะสม โดยการดับเบิ้ลคลิ๊กที่หัวตารางแล้วเปลี่ยนได้เลย
  7. ตัดอายุให้เหลือเป็นแค่ตัวเลข
  8. เปลี่ยนประเภทข้อมูลอายุ ให้เป็นตัวเลข แล้ว Replace Error ด้วยค่าว่าง
    ตอนนี้เราจะได้ข้อมูลที่มีหน้าตาตามต้องการแล้ว
  9. Load ข้อมูลผลลัพธ์ออกไปใช้ ทีนี้เราก็สามารถเอาเข้า Pivot Table ได้แล้วล่ะ!! โดยไปที่ Home -> Close & Load To… ได้เลย
แฉ 10 ความลับของ EXCEL ภาค3 : เปิดโลก Excel ใบใหม่ที่หลายคนไม่รู้จัก 12

แค่นี้เราก็จะมีอิสระในการพลิกข้อมูลใน Pivot มากขึ้น รวมถึงจะ Group ช่วงอายุตัวเลข โดยคลิ๊กขวา -> Group ก็ย่อมทำได้ ซึ่งนี่คือความดีของการทำข้อมูลให้เป็นเชิง Database ซะก่อนนั่นเองครับ

แฉ 10 ความลับของ EXCEL ภาค3 : เปิดโลก Excel ใบใหม่ที่หลายคนไม่รู้จัก 13

Secret 5: พลัง Load Power Query ออกมาเป็น Table แล้ว เราสามารถเขียนสูตร Excel ใน Table นั้นต่อได้เลย

สมมติว่าเราใช้ Power Query สร้างผลลัพธ์อะไรซักอย่างออกมาได้ เช่น ข้อมูลจังหวัดเมื่อกี๊ ถ้าผมเปลี่ยนการ Load ออกมาให้เป็น Table ก่อน โดยคลิ๊กขวา Load to… –> Table

แฉ 10 ความลับของ EXCEL ภาค3 : เปิดโลก Excel ใบใหม่ที่หลายคนไม่รู้จัก 14

จะได้หน้าตาแบบนี้

แฉ 10 ความลับของ EXCEL ภาค3 : เปิดโลก Excel ใบใหม่ที่หลายคนไม่รู้จัก 15

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

แฉ 10 ความลับของ EXCEL ภาค3 : เปิดโลก Excel ใบใหม่ที่หลายคนไม่รู้จัก 16

หรือบางทีอยากใช้ฟังก์ชันใน Excel จัดการมากกว่า เช่น กรณีที่เป็นสูตรทางการเงิน เช่น FV ที่ใช้คำนวณ Future Value ใน Power Query จะไม่มีสำเร็จรูปให้ใช้ ดังนั้นมาใช้ใน Excel อาจจะง่ายกว่า

เช่น จะ Forecast จำนวนประชากรในอีก 10 ปีข้างหน้า โดย assume ว่า growth คือ -0.3% ต่อปี (ผมมั่วนะ จริงๆ growth แต่ละช่วงอายุจะไม่เท่ากันหรอก)

ก็ใส่สูตรได้ว่า =FV(-0.3%,10,0,-[@จำนวนประชากร])

แฉ 10 ความลับของ EXCEL ภาค3 : เปิดโลก Excel ใบใหม่ที่หลายคนไม่รู้จัก 17

ความพิเศษคือ เรายังคงสามารถแก้ Query ที่ทำไว้ แล้วยัง Refresh ผลลัพธ์ได้ โดยคอลัมน์ใหม่ที่เราสร้างข้างนอกไม่หายไป เจ๋งป่ะล่ะ!!

Secret 6: Power Query สามารถอ่านไฟล์ทั้งหมดใน Folder และ Sub Folder ได้นะ

นอกจาก Power Query จะใช้ในการรวมรวม/ดัดแปลงข้อมูลแล้ว มันยังนำมาประยุกต์ใช้ได้หลายอย่าง เช่น การอ่านข้อมูลไฟล์ใน Folder เป็นต้น (โดยไม่จำเป็นต้องเอา content ในไฟล์มาใช้)

เช่น ผมสามารถใช้ Power Query list ไฟล์ pdf ที่ต้องการใน Folder ตามที่กำหนดได้

เริ่มต้นโดยให้ Get Data from Folder

แฉ 10 ความลับของ EXCEL ภาค3 : เปิดโลก Excel ใบใหม่ที่หลายคนไม่รู้จัก 18

Browse เลือก Folder ที่ต้องการ แล้ว ok แล้วกด Transform Data

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

เคสของผมเอง เร็วๆ นี้ก็ใช้วิธีนี้ดึงข้อมูลว่าผมทำใบเสร็จรับเงินสำหรับรายการที่ซื้อ Excel Power Up! แล้วหรือยัง โดยใช้วิธีนี้ดึงใบเสร็จ .pdf ต่างๆ มาเช็คโดย map กับเลข order ในชื่อไฟล์ได้ครับ

แฉ 10 ความลับของ EXCEL ภาค3 : เปิดโลก Excel ใบใหม่ที่หลายคนไม่รู้จัก 19

Secret 7: วิธีทำให้ Pivot Table อัปเดทข้อมูลอัตโนมัติ ทุกๆ xx นาที

คงปฏิเสธไม่ได้ว่า Pivot Table คือเครื่องมือสรุปข้อมูลที่ใช้ง่ายและทรงพลังมากที่สุดใน Excel อย่างไรก็ตามปัญหาหนึ่งที่กวนใจใครหลายๆ คนก็คือ เรื่องของการอัปเดทข้อมูลที่ต้องมาคอยกด Refresh อยู่เรื่อยๆ

ถึงแม้ว่าใน Pivot Table Option จะมีให้เลือก Auto Refresh ตอนเปิดไฟล์ Excel ขึ้นมาแต่ก็ดูเหมือนว่ามันจะยังไม่ได้ดั่งใจใครหลายๆ คน

แฉ 10 ความลับของ EXCEL ภาค3 : เปิดโลก Excel ใบใหม่ที่หลายคนไม่รู้จัก 20

หากเป็นสมัยก่อน เราอาจต้องเขียน VBA เพื่อให้ Excel ทำการ Refresh Pivot Table ด้วยความถี่ที่มากกว่านั้น แต่โชคดีที่ในยุคนี้เราสามารถใช้ Power Query เป็นแหล่งข้อมูลให้กับ Pivot Table ได้ และตัว Query เองมี Option ให้ Refresh ทุกๆ xx นาทีได้ด้วย!

วิธีการทำคือสร้าง Query จาก Table ต้นฉบับ (โหลดไฟล์ประกอบได้ที่นี่)

โดยเลือกข้อมูลต้นฉบับ แล้วกด Get Data From Table/Range (ถ้าข้อมูลยังไม่ได้เป็น Table มันจะแปลงเป็น Table ซะแล้วกด ok )

เมื่อปรับหน้าตาใน Power Query Editor ตามต้องการเสร็จแล้ว ให้กด Home –> Close & Load to… –> Pivot Table แล้วนำไปสร้าง Pivot Table ได้เลย

แฉ 10 ความลับของ EXCEL ภาค3 : เปิดโลก Excel ใบใหม่ที่หลายคนไม่รู้จัก 21

Tips : ถ้าใครกด Close & Load To… แล้วไม่มี Pivot Table ให้เลือก ก็ให้เลือก Only Create Connection ไปก่อน แล้วค่อยสร้าง Pivot Table จาก External Data ที่เป็น Query นั้นๆ ได้ โดยกด Insert->Pivot Table แล้วเลือก Choose Connection ตามรูป

แฉ 10 ความลับของ EXCEL ภาค3 : เปิดโลก Excel ใบใหม่ที่หลายคนไม่รู้จัก 22

ความพิเศษอยู่ที่เราสามารถคลิ๊กขวาที่ Query ที่สร้างขึ้น แล้วเลือก Properties เพื่อตั้งค่าให้มัน Auto Refresh Query ทุกๆ xx นาทีได้

แฉ 10 ความลับของ EXCEL ภาค3 : เปิดโลก Excel ใบใหม่ที่หลายคนไม่รู้จัก 23

และเมื่อ Query Refresh ปุ๊ป Pivot Table ก็จะถูก Refresh ตามไปด้วยโดยอัตโนมัติครับ!! นี่แหละวิธีทำให้ Pivot Refresh อัตโนมัติได้!!

Secret 8: Pivot Table เพื่อนับข้อมูลแบบไม่ซ้ำกัน (Distinct Count)

ปกติแล้วเวลาเราลากข้อมูลที่เป็นข้อความลงตาราง Pivot Table มันจะสรุปข้อมูลให้เราด้วยวิธีการ Count (นับ) โดยอัตโนมัติ เช่นแบบนี้ (ใช้ไฟล์ประกอบเดิม คือ โหลดไฟล์ประกอบได้ที่นี่)

แฉ 10 ความลับของ EXCEL ภาค3 : เปิดโลก Excel ใบใหม่ที่หลายคนไม่รู้จัก 24

ซึ่งเลข 28 ที่ผม Highlight สีเหลืองไว้ หลายคนจะเข้าใจว่า มันคือเลขที่เกิดจาก Sales ข ขายหนังสือให้ลูกค้าได้ 28 คน… ซึ่งในความเป็นจริงมันไม่ใช่อย่างนั้นซะทีเดียว เพราะหากเราดับเบิ้ลคลิ๊กที่เลข 28 เพื่อเข้าไปดูว่าคำนวณมาจากรายการไหนบ้าง จะพบว่ามีลูกค้าหลายรายที่ซ้ำกัน

แฉ 10 ความลับของ EXCEL ภาค3 : เปิดโลก Excel ใบใหม่ที่หลายคนไม่รู้จัก 25

ดังนั้นหากเราต้องการจะนับลูกค้าแบบไม่ซ้ำ ต้องใช้ Pivot Table โหมด Data Model โดยให้ไปที่ Field List ด้านขวา เลื่อนลงล่างสุด แล้วคลิ๊ก More Tables…

แฉ 10 ความลับของ EXCEL ภาค3 : เปิดโลก Excel ใบใหม่ที่หลายคนไม่รู้จัก 26

จากนั้นให้กด Yes แล้วมันจะสร้าง Pivot Table ตัวใหม่ใน Sheet ใหม่ (ที่หน้าตาเหมือนเดิมทุกประการ)

ให้เราคลิ๊กขวาที่ Count of ลูกค้า แล้วเปลี่ยน Summarized Values By -> More Option –> Distinct Count

แฉ 10 ความลับของ EXCEL ภาค3 : เปิดโลก Excel ใบใหม่ที่หลายคนไม่รู้จัก 27

จะเห็นว่าคราวนี้ผลลัพธ์ที่ได้เหลือเพียง 10 คนที่ไม่ซ้ำกันแล้ว!!

แฉ 10 ความลับของ EXCEL ภาค3 : เปิดโลก Excel ใบใหม่ที่หลายคนไม่รู้จัก 28

Secret 9: Pivot Table สามารถแสดง Values เป็นข้อความได้ด้วยนะ

ปกติแล้วใน Pivot Table โหมดปกติ จะสรุปข้อมูลได้แค่ตัวเลขเท่านั้น เช่น การ Sum, Count, Average, Max, Min แต่มันไม่มีแบบไหนเลยที่จะแสดงข้อความออกมาได้

แต่ถ้าเราใช้ Pivot Table โหมด Data Model (แบบเดียวกับตัวอย่างข้างบน) มันจะสามารถเขียนผลสรุปด้วยสิ่งที่เรียกว่า Measure ซึ่งสามารถเขียนสูตรที่หลากหลายมากๆ โดยใช้ภาษา DAX ในการเขียน (ภาษาเดียวกับ Power BI)

ซึ่งภาษา DAX นี้มีข้อดีมากๆ คือ เป็นภาษาที่มีฟังก์ชันเหมือน Excel มากๆ ทำให้ลดเวลาในการเรียนรู้ลงได้มาก อย่างไรก็ตามก็ยังมีอีกหลายเรื่องที่ต้องศึกษาเพิ่ม (อีกเยอะมากๆ)

ภาษานี้มันมีฟังก์ชันที่ Excel ไม่มีด้วย เช่น CONCATENATEX ซึ่งสามารถเอาข้อความมาต่อกันได้ เช่น ผมอยากแสดงว่าลูกค้าซื้อสินค้าอะไรบ้างโดยให้คั่นด้วยเครื่องหมาย / ก็สามารถทำได้ดังนี้ (ใช้ไฟล์ประกอบเดิม คือ โหลดไฟล์ประกอบได้ที่นี่)

ให้คลิ๊กขวาที่ชื่อตารางใน Field List ของ Pivot โหมด Data Model แล้วเลือก Add Measure…

แฉ 10 ความลับของ EXCEL ภาค3 : เปิดโลก Excel ใบใหม่ที่หลายคนไม่รู้จัก 29

ซึ่งในนั้นจะมีหน้าต่าง Measure ขึ้นมาให้เราสามารถตั้งชื่อ และกรอกสูตรคำนวณของ Measure (ด้วยภาษา DAX) ได้เลย ว่า

=CONCATENATEX(DISTINCT(SalesTX[สินค้า]),[สินค้า],"/")
แฉ 10 ความลับของ EXCEL ภาค3 : เปิดโลก Excel ใบใหม่ที่หลายคนไม่รู้จัก 30

ตรงนี้คุณอาจยังอ่านสูตร DAX ไม่เข้าใจ (และบทความนี้ก็จะไม่ได้สอนด้วย) แต่วัตถุประสงค์ที่ผมเอามาโชว์แค่จะบอกว่า Measure นั้นมีความ Flexible สูงมากๆ ซึ่งช่วยให้ Pivot Table สามารถแสดงผลช่อง Values ออกมาเป็นข้อความก็ได้นะครับ

ดังนั้นใครอยากปลดปล่อยประสิทธิภาพของ Pivot Table ออกมาให้เต็มที่ก็อย่าลืมหันมาศึกษาภาษา DAX เพิ่มด้วยนะ เรียกได้ว่าเรียนรู้ทีเดียวใช้ได้ทั้ง Excel และ Power BI เลยนะ

Secret 10: เราสามารถ Plot กราฟแผนที่ใน Excel ได้นะ

นอกจาก Power BI ที่มีกราฟแผนที่เจ๋งๆ แล้ว ใน Excel Version ใหม่ๆ อย่าง Excel 365 หรือ Excel 2019 ก็สามารถทำกราฟแผนที่ได้เช่นกัน โดยใช้ชื่อจังหวัดภาษาไทยได้เลยด้วย

สมมติผมมีข้อมูลประชากรรายจังหวัดในข้อที่ผ่านมา เราสามารถนำไป Plot กราฟประเภท Map ได้ แต่ว่ามันไม่สามารถสร้างจาก Pivot Table ได้โดยตรงนะ (อันนี้คือข้อเสียของ Pivot Table เมื่อเทียบกับ Power BI ที่ Visual มีหลายแบบมากกว่า)

ให้เรา Copy ข้อมูลจาก Pivot ออกมาเป็น Range ธรรมดาก่อน แล้วกด Insert -> Maps –> Filled Maps ก็จะได้ตามรูปเลย

แฉ 10 ความลับของ EXCEL ภาค3 : เปิดโลก Excel ใบใหม่ที่หลายคนไม่รู้จัก 31

สำหรับคนที่มี Excel 365 แต่ไม่มีแผนที่ให้เลือก หรือ Power Query มีเครื่องมือน้อยกว่าของผม ลองเช็คการอัปเดท version ว่ามีการ update ล่าสุดแล้วหรือยัง? อาจดูแนวทางในข้อถัดไปนี้ได้ครับ

ของแถมสุดพิเศษ : วิธีเปลี่ยน Excel 365 ให้มีการอัปเดทถี่ขึ้นเป็นรายเดือน (Monthly Channel)

หากคุณใช้ Office 365 แล้วคิดว่าจะได้รับการอัปเดทล่าสุดเสมอ บางทีมันอาจจะไม่ใช่อย่างที่คุณคิดซะทีเดียว… เนื่องจากบางท่านอาจได้รับการอัปเดทอาจแบบครึ่งปีครั้ง แทนที่จะเป็นเดือนละครั้งก็ได้ (แบบนั้นก็ช้าไปเกือบ 6 เดือนเลยนะ!!)

การอัปเดทช้าอาจทำให้ Power Query มีเครื่องมือหรือมีความสามมารถน้อยกว่าของผมได้นะ

วิธีการดูว่าการอัปเดทของเราเป็นแบบไหนให้ไปดูใน File –> Account ครับ

แฉ 10 ความลับของ EXCEL ภาค3 : เปิดโลก Excel ใบใหม่ที่หลายคนไม่รู้จัก 32

วิธีเปลี่ยนให้สามารถรับการอัปเดททุกเดือน สามารถทำได้ตามคำแนะนำของเว็บนี้เลยครับ https://docs.microsoft.com/en-us/office365/troubleshoot/updates/switch-channel-for-office-365?fbclid=IwAR2GIchDKqOg84bHUnRrNOxDOgqfm6oPfSeBwkPGFPt7sPjQKtHM7cORWGI

หรือตามเว็บนี้ก็ได้ https://www.solver.com/switching-office-365-monthly-update-channel

ถ้าเป็นคอมพ์บริษัท ผมแนะนำให้ทาง IT ทำให้นะครับ เพราะเราทำเองไม่น่าจะได้ และอาจจะไม่ปลอดภัยครับ

ถ้าใครขี้เกียจทำตามในเว็บก็ใช้วิธีลัดอันนี้ได้เลยนะ

1. สามารกโหลดไฟล์นี้ไปได้เลย ไฟล์ชื่อ updateo365.bat ซึ่งเป็น bat ไฟล์ที่ทำมาจากขั้นตอนในเว็บนะครับ

2. คลิ๊กขวาที่ไฟล์ updateo365.bat ที่โหลดไป แล้วเลือก Run as administrator

แฉ 10 ความลับของ EXCEL ภาค3 : เปิดโลก Excel ใบใหม่ที่หลายคนไม่รู้จัก 33

3. เข้า Excel –> File –> Account –> Update option –> Update Now จบ

แฉ 10 ความลับของ EXCEL ภาค3 : เปิดโลก Excel ใบใหม่ที่หลายคนไม่รู้จัก 34

และทั้งหมดนี่ก็คือ 10 ความลับของ Excel ยุคใหม่ๆ ที่ทำอะไรได้เยอะแยะมากกว่าที่คุณเคยรู้ หากอ่านแล้วสงสัยตรงไหนก็ Comment ถามได้เลยนะครับ

***==***==***==***==***==***
เนื่องในโอกาสวันขึ้นปีใหม่ ขอสุขสันต์วันปีใหม่ Happy New Year 2020 ถึงแฟนเพจทุกคนด้วยนะครับ
.
สำหรับปีที่ผ่านมา รวมถึงปีก่อนหน้านั้นด้วย ผมอยากจะขอบคุณทุกท่านที่ช่วยสนับสนุนทางเทพเอ็กเซลมาโดยตลอด ทั้งเข้ามาอ่าน มาช่วยบอกแต่ ช่วยอุดหนุนหนังสือทั้ง 3 เล่มของผม (บางคนมีครบ 3 เลย ดีใจมากๆ)
.
ในปี 2020 นี้ผมสัญญาว่าจะมีอะไรเจ๋งๆ จากเทพเอ็กเซลออกมามากมายแน่นอน เดี๋ยวจะมีการประกาศอีกทีนะครับ
.
สุดท้ายนี้ขอให้ทุกท่านมีความสุข และสามารถพัฒนาความสามารถของตนเองให้เก่งขึ้นได้ตามต้องการครับ ^^

***==***==***==***==***==***

Facebook Group : Power Query Thailand

This image has an empty alt attribute; its file name is cover-1024x534.jpg
Facebook Group : Power Query Thailand

ผู้ที่สนใจ Power Query อย่างคุณที่มาอ่านบทความนี้ ผมขอเชิญชวนเข้ากลุ่มปิด Power Query Thailand ได้ตาม Link นี้ครับ

แชร์ความรู้ให้เพื่อนๆ ของคุณ
  • 693
  •  
  •  
  •  
  •  
  • 693
  •  
  •  
  •  
  •  
Sira Ekabut
the authorSira Ekabut
• Microsoft Office Specialist : Excel ระดับ Expert • ก่อตั้งเว็บไซต์และ แฟนเพจเทพเอ็กเซล คนติดตาม 8 หมื่นคน+, คลิปวีดีโอ 1 ล้าน Views + • หนังสือ Best Seller คอมพิวเตอร์ : Excel Level Up! , จอมเวท เทพ Excel , Excel Power Up!วิทยากรใน SkillLane.com คอร์สออนไลน์ชั้นนำ / อบรม Excel ให้บริษัทชั้นนำ

Leave a Reply