Sub TestTime4()
Mytime = Now + TimeSerial(0, 0, 5) 'อีก 5 วินาทีจะรัน
Application.OnTime Mytime, "TestMacro1"
MsgBox ("Test แทรก")
Application.OnTime Mytime, "TestMacro1", , False 'นี่จะ cancel การ schedule
End Sub
การสั่งรันหลายๆ ครั้งต่อเนื่อง ทุกๆ xx นาที
ถ้าเราสั่งให้ Sub 2 ตัวมันเรียกกันเอง มันก็จะรันต่อเนื่องไปเรื่อยๆ ได้ เช่น
Sub TestMacroRecur()
MsgBox ("Test Run Macro Number 2")
StartTimeRecur 'เรียกตัว schedule อีกรอบ
End Sub
Sub StartTimeRecur()
StartTime = Now + TimeSerial(0, 0, 5) 'อีก 5 วินาทีจะรัน
Application.OnTime StartTime, "TestMacroRecur", , True
End Sub
แต่ที่นี้มันจะรันไปเรื่อยๆ ไม่หยุดเลย เราก็ต้องสร้างปุ่มมาสั่งหยุดการเตือนอีกที เช่น สร้าง sub นี้แล้ว Assign Macro ให้กับปุ่มเอาไว้
Sub CancelTimeRecur()
' เอาอันนี้ไป assign กับปุ่มก็ได้
Application.OnTime StartTime, "TestMacroRecur", , False
End Sub
Sub RefreshQueryBatch()
'จะ Refresh Query ไหนบ้างก็ใส่ไป
Call RefreshQuery("CombinedTable")
Call RefreshQuery("xxxลำดับถัด2")
Call RefreshQuery("xxxลำดับถัด3")
End Sub
เก็บตก 1 : วิธีส่งข้อความแจ้งเตือน Line Notification เข้า Group Chat
ก่อนหน้านี้ที่ผมสอนไปเป็นการส่ง Line ไปหา Line Notify ซึ่งเราจะเห็นอยู่คนเดียว ซึ่งไอแบบนี้มันเอาไปใช้งานจริงได้ลำบาก เพราะจะเป็นการเตือนตัวเองได้อย่างเดียว (จริงมะ)
Sub LineNotify(msg As String, Optional stickID As Integer, Optional stickPack As Integer, Optional imgFullPath As String = "none", Optional imgThumbPath As String = "none")
Dim LineToken As String
Dim lineMessage As String
Dim objectXML As Object
Dim URL As String
'========================================
'Line Notify Token ที่ Generate มา
'group noti final
LineToken = "ใส่ Token ของคุณ"
'========================================
'Line Message
yourMessage = msg
lineMessage = "message=" & yourMessage
'========================================
'เพิ่มเงื่อนไข Sticker
If stickPack = 0 Or stickID = 0 Then
Else
lineMessage = lineMessage & "&stickerPackageId=" & stickPack & "&stickerId=" & stickID
End If
'เพิ่มเงื่อนไข image
If imgThumbPath = "none" Then
imgThumbPath = imgFullPath
End If
If imgFullPath <> "none" Then
lineMessage = lineMessage & "&imageThumbnail=" & imgThumbPath & "&imageFullsize=" & imgFullPath
End If
'========================================
Set objectXML = CreateObject("Microsoft.XMLHTTP")
URL = "https://notify-api.line.me/api/notify"
With objectXML
.Open "POST", URL, 0
.SetRequestHeader "Content-Type", "application/x-www-form-urlencoded"
.SetRequestHeader "Authorization", "Bearer " & LineToken
.send (lineMessage)
Debug.Print objectXML.responseText
End With
Set objectXML = Nothing
End Sub
นั่นคือสามารถส่งค่า URL ที่เก็บรูปเข้าไปในคำสั่ง Line Notify ได้ ด้วยรูปแบบคำสั่งที่ผมออกแบบไว้ดังนี้
Sub testImg2()
Call LineNotify("ทดสอบส่งรูป", 0, 0, "https://www.thepexcel.com/wp-content/uploads/2019/11/excel-power-up-page.jpg", "https://www.thepexcel.com/wp-content/uploads/2019/11/excel-power-up-book.jpg")
End Sub
และเท่าที่เช็คดูใน Internet เหมือนกับว่าการส่งข้อมูลที่เป็นไฟล์ด้วย VBA นั้นจะต้องใช้ multipart/form-data แทน application/x-www-form-urlencoded ซึ่งจะซับซ้อนขึ้นเยอะและมีรูปแบบแปลกๆ เช่น
Content-Type: multipart/form-data; boundary=12345
--12345
Content-Disposition: form-data; name="sometext"
some text that you wrote in your html form ...
--12345
Content-Disposition: form-data; name="name_of_post_request" filename="filename.xyz"
content of filename.xyz that you upload in your form with input[type=file]
--12345
Content-Disposition: form-data; name="image" filename="picture_of_sunset.jpg"
content of picture_of_sunset.jpg ...
--12345--
ดังนั้นผมจะสร้าง Sub ใหม่ขึ้นมา เพื่อส่งรูปโดยเฉพาะ (แต่ถ้าเกิดส่งอย่างอื่นก็เรียกฟังก์ชันเดิมได้)
ให้เราสร้าง sub ใหม่และ function ใหม่ เพิ่มจากของเดิม โดยใส่ Code ดังนี้
Sub LineNotifyUploadPic(msg As String, Optional UploadFilePath As String = "none")
Dim sFilepath As String
Dim nFile As Integer
Dim baBuffer() As Byte
Dim ssPostData1 As String
Dim ssPostData2 As String
Dim ssPostData3 As String
Dim Messagelength As Integer
Dim objectXML As Object
Dim arr1() As Byte
Dim arr2() As Byte
Dim arr3() As Byte
Dim arr4() As Byte
Const STR_BOUNDARY As String = "xxx---thepexcel---thepexcel---thepexcel---xxx"
'แก้ Token ตรงนี้
LineToken = "ใส่ Token ของตัวเอง"
sFilepath = UploadFilePath
sFileName = GetFilenameFromPath(sFilepath)
ssPostData1 = vbCrLf & _
"--" & STR_BOUNDARY & vbCrLf & _
"Content-Disposition: form-data; name=" & """message""" & vbCrLf & vbCrLf
arr1 = StrConv(ssPostData1, vbFromUnicode)
arr2 = (msg)
ssPostData2 = vbCrLf & _
"--" & STR_BOUNDARY & vbCrLf & _
"Content-Disposition: form-data; name=" & """imageFile""" & "; filename=" & sFileName & vbCrLf & _
"Content-Type: image/jpng" & vbCrLf & vbCrLf
arr3 = StrConv(ssPostData2, vbFromUnicode)
nFile = FreeFile
Open sFilepath For Binary Access Read As nFile
If LOF(nFile) > 0 Then
ReDim baBuffer(0 To LOF(nFile) - 1) As Byte
Get nFile, , baBuffer
imagear = baBuffer
End If
Close nFile
arr4 = StrConv(vbCrLf & "--" & STR_BOUNDARY & "--" & vbCrLf, vbFromUnicode)
Dim arraytotal As Long
Dim sendarray() As Byte
arraytotal = UBound(arr1) + UBound(arr2) + UBound(arr3) + UBound(imagear) + UBound(arr4) + 4
ReDim sendarray(arraytotal)
For i = 0 To UBound(arr1)
sendarray(i) = arr1(i)
Next
For i = 0 To UBound(arr2)
sendarray(UBound(arr1) + i + 1) = arr2(i)
Next
For i = 0 To UBound(arr3)
sendarray(UBound(arr1) + UBound(arr2) + i + 2) = arr3(i)
Next
For i = 0 To UBound(imagear)
sendarray(UBound(arr1) + UBound(arr2) + UBound(arr3) + i + 3) = imagear(i)
Next
For i = 0 To UBound(arr4)
sendarray(UBound(arr1) + UBound(arr2) + UBound(arr3) + UBound(imagear) + i + 4) = arr4(i)
Next
Set objectXML = CreateObject("Microsoft.XMLHTTP")
URL = "https://notify-api.line.me/api/notify"
With objectXML
.Open "POST", URL, 0
.SetRequestHeader "Content-Type", "multipart/form-data; boundary=" & STR_BOUNDARY
.SetRequestHeader "Authorization", "Bearer " & LineToken
.send sndar(sendarray)
Debug.Print .responseText
End With
Set objectXML = Nothing
End Sub
Public Function sndar(sendarray As Variant) As Byte()
sndar = sendarray
End Function
Public Function GetFilenameFromPath(ByVal strPath As String) As String
If Right$(strPath, 1) <> "\" And Len(strPath) > 0 Then
GetFilenameFromPath = GetFilenameFromPath(Left$(strPath, Len(strPath) - 1)) + Right$(strPath, 1)
End If
End Function
Sub TestUploadPic()
Dim filepath As String
'แก้เป็น path ของรูปในเครื่องตัวเอง
filepath = "d:\thepexcel\sira-excel-powerup.jpg"
Call LineNotifyUploadPic(".", filepath)
End Sub
Sub LoopTable()
Dim tbl As ListObject
'อ้างอิงจาก ชื่อ table
Set tbl = ActiveSheet.ListObjects("ProjectTable")
'ลองดึงข้อมูลในตำแหน่งที่ต้องการ
'หลักๆ อ้างโดย Cells(ลำดับแถว,ลำดับคอลัมน์) โดยมันอ้างใน range ที่กำหนด ในที่นี้คือ Data ใน Table
'ใช้ tbl.ListColumns("ชื่อคอลัมน์").Index เพื่อหาว่าอยู่คอลัมน์ที่เท่าไหร่
MsgBox tbl.DataBodyRange.Cells(2, tbl.ListColumns("Desc").Index)
End Sub
Public tbl As ListObject
Function GetTableData(projRow)
projName = tbl.DataBodyRange.Cells(projRow, tbl.ListColumns("ชื่อ Project").Index)
projDesc = tbl.DataBodyRange.Cells(projRow, tbl.ListColumns("Desc").Index)
projDeadline = tbl.DataBodyRange.Cells(projRow, tbl.ListColumns("Deadline").Index)
projFullMsg = projName & " | " & projDesc & " | Dead Line : " & projDeadline
GetTableData = projFullMsg
End Function
Sub LoopTable()
Set tbl = ActiveSheet.ListObjects("ProjectTable")
'get current date
dateToday = Date
'check number of rows
NumRows = tbl.DataBodyRange.Rows.Count
For i = 1 To NumRows
projDeadline = tbl.DataBodyRange.Cells(i, tbl.ListColumns("Deadline").Index)
projFinish = tbl.DataBodyRange.Cells(i, tbl.ListColumns("Finish").Index)
If projFinish <> "Y" And projDeadline < dateToday Then
MsgBox GetTableData(i)
End If
Next i
End Sub
พอกด F5 มันก็จะเด้ง MsgBox มา 2 รอบ คือ Project B และ Project H
เริ่มส่งข้อความเข้า Line จริงๆ ละ
จากนั้นเราจะเปลี่ยนจาก MsgBox ไปใช้การส่งข้อความเข้า Line แทน ซึ่งเรามี Sub ตัวนึงที่ทำงานนั้นอยู่แล้ว (สร้างไว้ในบทแรก) นั่นคือ
Sub LineNotify(msg As String, Optional stickID As Integer, Optional stickPack As Integer)
โดยผมลองกลับมาใช้ MsgBox เพื่อทดสอบก่อนว่าได้ผลหรือไม่ เดี๋ยวไว้แก้ทุกอย่างเสร็จเรียบร้อยจะเปลี่ยนกลับไปส่ง Line อีกที
Public tbl As ListObject
Function GetTableData(projRow)
projName = tbl.DataBodyRange.Cells(projRow, tbl.ListColumns("ชื่อ Project").Index)
projDesc = tbl.DataBodyRange.Cells(projRow, tbl.ListColumns("Desc").Index)
projDeadline = tbl.DataBodyRange.Cells(projRow, tbl.ListColumns("Deadline").Index)
projFullMsg = projName & " | " & projDesc & " | Dead Line : " & projDeadline
GetTableData = projFullMsg
End Function
Sub LoopTable()
Set tbl = ActiveSheet.ListObjects("ProjectTable")
'get current date
dateToday = Date
'check number of rows
NumRows = tbl.DataBodyRange.Rows.Count
'Due Already
For i = 1 To NumRows
projDeadline = tbl.DataBodyRange.Cells(i, tbl.ListColumns("Deadline").Index)
projFinish = tbl.DataBodyRange.Cells(i, tbl.ListColumns("Finish").Index)
If projFinish <> "Y" And projDeadline < dateToday Then
MsgBox "เลยกำหนดแล้ว : " & GetTableData(i)
End If
Next i
'Pre Noti
'Get day to noti
NumDayNoti = Range("dayNoti")
For i = 1 To NumRows
projDeadline = tbl.DataBodyRange.Cells(i, tbl.ListColumns("Deadline").Index)
projFinish = tbl.DataBodyRange.Cells(i, tbl.ListColumns("Finish").Index)
If projFinish <> "Y" And (projDeadline - NumDayNoti) < dateToday And projDeadline >= dateToday Then
MsgBox "กำลังจะครบกำหนด : " & GetTableData(i)
End If
Next i
End Sub
Public tbl As ListObject
Function GetTableData(projRow)
projName = tbl.DataBodyRange.Cells(projRow, tbl.ListColumns("ชื่อ Project").Index)
projDesc = tbl.DataBodyRange.Cells(projRow, tbl.ListColumns("Desc").Index)
projDeadline = tbl.DataBodyRange.Cells(projRow, tbl.ListColumns("Deadline").Index)
projFullMsg = projName & " | " & projDesc & " | Dead Line : " & projDeadline
GetTableData = projFullMsg
End Function
Sub LoopTable()
Set tbl = ActiveSheet.ListObjects("ProjectTable")
'get current date
dateToday = Date
'check number of rows
NumRows = tbl.DataBodyRange.Rows.Count
'Due Already
CountDue = 0
For i = 1 To NumRows
projDeadline = tbl.DataBodyRange.Cells(i, tbl.ListColumns("Deadline").Index)
projFinish = tbl.DataBodyRange.Cells(i, tbl.ListColumns("Finish").Index)
If projFinish <> "Y" And projDeadline < dateToday Then
DueMsg = DueMsg & GetTableData(i) & Chr(13) & Chr(10)
CountDue = CountDue + 1
End If
Next i
LineNotify "เลยกำหนดแล้ว : " & CountDue & " รายการ" & Chr(13) & Chr(10) & DueMsg
'Pre Noti
'Get day to noti
NumDayNoti = Range("dayNoti")
CountPreNoti = 0
For i = 1 To NumRows
projDeadline = tbl.DataBodyRange.Cells(i, tbl.ListColumns("Deadline").Index)
projFinish = tbl.DataBodyRange.Cells(i, tbl.ListColumns("Finish").Index)
If projFinish <> "Y" And (projDeadline - NumDayNoti) < dateToday And projDeadline >= dateToday Then
PreNotiMsg = PreNotiMsg & GetTableData(i) & Chr(13) & Chr(10)
CountPreNoti = CountPreNoti + 1
End If
Next i
LineNotify "กำลังจะครบกำหนด : " & CountPreNoti & " รายการ" & Chr(13) & Chr(10) & PreNotiMsg
If CountDue > 0 Then
Call LineNotify("ทำไมถึงปล่อยให้มีงานเกินกำหนดส่ง คราวหน้าปรับปรุงด้วยนะ!!", 118, 1)
End If
End Sub
ปล. สำหรับการดึงข้อความจาก Line มาเข้า Excel นั้น ส่วนตัวคิดว่ามันใช้ Line Notify ไม่ได้ และไม่แน่ใจว่าใช้ Line Messaging API ได้หรือไม่? ผมยังไม่ได้ลอง แต่ถ้าได้ ผมคิดว่าทำเป็น Line BOT ไปเลยอาจจะง่ายกว่าใช้ Excel ครับ
Sub SendOutlook()
' SET Outlook APPLICATION OBJECT.
Dim objOutlook As Object
Set objOutlook = CreateObject("Outlook.Application")
' CREATE EMAIL OBJECT.
Dim objEmail As Object
Set objEmail = objOutlook.CreateItem(olMailItem)
With objEmail
.to = "ที่อยู่เมลปลายทาง"
.Subject = "ส่งจาก Excel VBA"
.Body = "ข้อความจาก Excel VBA"
.Display ' DISPLAY MESSAGE.
End With
' CLEAR.
Set objEmail = Nothing: Set objOutlook = Nothing
End Sub
ถ้ารันสำเร็จก็จะเปิดหน้าต่าง Outlook ออกมา
Tips : ถ้าใครกดแล้วไม่ติด ลองไปที่ Option ใน VBA -> References… -> ติ๊ก Microsoft Outlook xx.x Object Library เพิ่มดูนะครับ (แต่ผมไม่ได้ติ๊กยังใช้ได้เลย)
วิธีเพิ่มไฟล์แนบลงไป
ในระหว่าง With objEmail … กับ End With สามารถเพิ่มคำสั่งนี้ได้ครับ
Sub SendOutlook()
' SET Outlook APPLICATION OBJECT.
Dim objOutlook As Object
Set objOutlook = CreateObject("Outlook.Application")
' CREATE EMAIL OBJECT.
Dim objEmail As Object
Set objEmail = objOutlook.CreateItem(olMailItem)
With objEmail
.to = "ที่อยู่เมลปลายทาง"
.Subject = "ส่งจาก Excel VBA"
.Body = "ข้อความจาก Excel VBA แบบมีไฟล์แนบ"
.Attachments.Add ("ที่อยู่ไฟล์แนบ")
.Display ' DISPLAY MESSAGE.
End With
' CLEAR.
Set objEmail = Nothing: Set objOutlook = Nothing
End Sub
Sub SendOutlook()
' SET Outlook APPLICATION OBJECT.
Dim objOutlook As Object
Set objOutlook = CreateObject("Outlook.Application")
' CREATE EMAIL OBJECT.
Dim objEmail As Object
Set objEmail = objOutlook.CreateItem(olMailItem)
With objEmail
.to = "ที่อยู่เมลปลายทาง"
.Subject = "ส่งจาก Excel VBA AUTO"
.Body = "ข้อความจาก Excel VBA แบบมีไฟล์แนบ แบบ Auto"
.Attachments.Add ("ที่อยู่ไฟล์แนบ")
.Send ' SEND MESSAGE AUTO.
End With
' CLEAR.
Set objEmail = Nothing: Set objOutlook = Nothing
End Sub
จากนั้นกด F5 เพื่อ Run Code จะพบว่ามี email ส่งไปยังปลายทางได้ทันที !!
ใช้ VBA ส่งผ่านช่องทางอื่นๆ เช่น Gmail
เอาล่ะ มาถึงวิธีสุดท้ายแล้ว นั่นคือ เราจะส่ง Email แบบ Auto ด้วยคนส่งที่ใช้ Gmail กันล่ะ!!
ปล. ถ้า Account Google ของเราเปิดโหมด 2-Step Verification เอาไว้ จะทำให้ใช้ Allow Less Secure Apps ไม่ได้นะ ต้องไปปิดโหมดนี้ก่อน ซึ่งก็จะอันตรายขึ้นนะ
ใส่ Code VBA
สร้าง Module ใหม่แล้ว Copy Code นี้ลงไป
Sub SendGmail()
Dim Mail As CDO.Message
Dim strSubject As String
Dim strFrom As String
Dim strTo As String
Dim strCc As String
Dim strBcc As String
Dim strBody As String
Dim strAttach As String
Dim GmailAccount As String
Dim GmailPassword As String
Dim msURL As String
'เปลี่ยน code ที่นี่'============
GmailAccount = "ที่อยู่gmailของคุณ"
GmailPassword = "password gmail ของคุณ"
strSubject = "ทดสอบยิง Gmail จาก Excel VBA"
strFrom = GmailAccount
strTo = "emailปลายทาง"
strCc = ""
strBcc = ""
strBody = "เนื้อหาการทดสอบ"
strAttach = "ที่อยู่ไฟล์แนบ"
'เปลี่ยน code ที่นี่'============
'creating a CDO object
Set Mail = New CDO.Message
msURL = "http://schemas.microsoft.com/cdo/configuration"
With Mail.Configuration.Fields
'Enable SSL Authentication
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
'Make SMTP authentication Enabled=true (1)
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
'Set the SMTP server and port Details
'Get these details from the Settings Page of your Gmail Account
.Item(msURL & "/smtpserver") = "smtp.gmail.com"
.Item(msURL & "/smtpserverport") = 465
.Item(msURL & "/sendusing") = 2
'Set your credentials of your Gmail Account
.Item(msURL & "/sendusername") = GmailAccount
.Item(msURL & "/sendpassword") = GmailPassword
'Update the configuration fields
.Update
End With
'Set All Email Properties
With Mail
.Subject = strSubject
.From = strFrom
.To = strTo
.CC = strCc
.BCC = strBcc
.TextBody = strBody
.AddAttachment (strAttach) 'To attach Documents in mail
End With
'to send the mail
Mail.Send
End Sub
เอาล่ะ เพื่อไม่ให้เสียเวลาเรามาเริ่มกันเลยที่การแจ้งเข้า Line กันก่อนละกัน (เพราะมันน่าสนใจกว่าไงล่ะ 555)
วิธีแจ้งเตือนเข้า Line
การจะแจ้งเตือนเข้า Line ได้นั้น เราจะใช้บริการที่ชื่อว่า Line Notify ซึ่งเป็นบริการที่เปิดให้เราสามารถส่งคำสั่งไปหา Line ได้ แต่ก็ต้องส่งในรูปแบบที่ Line กำหนดเป๊ะๆ นะ มันถึงจะเข้าใจ *ทางเทคนิค คือ มันเป็นการส่งผ่าน API (Application Programming Interface) ที่ Line ออกแบบเอาไว้
แล้วก็ทำการ Paste Code นี้ลงไป (Copy ตั้งแต่แถว 1 ที่เป็นคำว่า sub นะ…)
Sub LineNotify()
Dim LineToken As String
Dim lineMessage As String
Dim objectXML As Object
Dim URL As String
'========================================
'Line Notify Token ที่ Generate มา
LineToken = "ใส่tokenของตัวเอง"
'Line Message
yourMessage = "ใส่ข้อความของตัวเอง"
'========================================
lineMessage = "message=" & yourMessage
Set objectXML = CreateObject("Microsoft.XMLHTTP")
URL = "https://notify-api.line.me/api/notify"
With objectXML
.Open "POST", URL, 0
.SetRequestHeader "Content-Type", "application/x-www-form-urlencoded"
.SetRequestHeader "Authorization", "Bearer " & LineToken
.send (lineMessage)
Debug.Print objectXML.responseText
End With
Set objectXML = Nothing
End Sub
Sub test_noti()
Call LineNotify("ใส่ข้อความของตัวเอง", stickerID, stickPackID)
End Sub
Sub LineNotify(msg As String, Optional stickID As Integer, Optional stickPack As Integer)
Dim LineToken As String
Dim lineMessage As String
Dim objectXML As Object
Dim URL As String
'========================================
'Line Notify Token ที่ Generate มา
LineToken = "ใส่tokenของตัวเอง"
'========================================
'Line Message
yourMessage = msg
lineMessage = "message=" & yourMessage
'========================================
'เพิ่มเงื่อนไข Sticker
If stickPack = 0 Or stickID = 0 Then
Else
lineMessage = lineMessage & "&stickerPackageId=" & stickPack & "&stickerId=" & stickID
End If
'========================================
Set objectXML = CreateObject("Microsoft.XMLHTTP")
URL = "https://notify-api.line.me/api/notify"
With objectXML
.Open "POST", URL, 0
.SetRequestHeader "Content-Type", "application/x-www-form-urlencoded"
.SetRequestHeader "Authorization", "Bearer " & LineToken
.send (lineMessage)
Debug.Print objectXML.responseText
End With
Set objectXML = Nothing
End Sub
คราวนี้เราจะแก้ข้อความที่ข้างบนสุดเอาได้เลย และสามารถใส่ Sticker ได้ด้วย โดยต้องระบุ Sticker ID และ Sticker Package ID ที่ต้องการ โดยดูรายชื่อ Sticker ที่นี่
จากนั้นคลิ๊กตรงส่วนบน ใน Sub test_noti() แล้วกดปุ่ม F5 เพื่อ Run Code
จะเห็นว่ามีทั้งข้อความและ Sticker เลย
การส่งยืดหยุ่นขึ้นมาก
แต่ถ้าอยากส่งแต่ข้อความ ก็ใส่แต่ข้อความได้เลย
นอกจากนี้ Sub test_noti() จะใส่ Call LineNotify หลายรอบก็ได้ เช่น
Sub test_noti()
Call LineNotify("ตอนนี้ยาวมากแล้ว ขอจบเท่านี้ดีกว่า ไว้ติดตามต่อตอนหน้านะ")
Call LineNotify("ขอบคุณทุกคนมากที่ติดตามอ่าน สวัสดีครับ", 125, 1)
End Sub
ตามปกติแล้ว เวลาเราใช้ Power Query เพื่อGet Data จากไฟล์ Excel เดียวกันกับตัว Power Query ที่กำลังสร้าง เราจะต้อง Get Data from Table/Range เท่านั้น ซึ่งหากใช้วิธีนี้ Excel จะบังคับให้แปลงข้อมูลเป็น Table ก่อนเสมอ และนั่นก็เป็นวิธีมาตรฐานที่ทาง Microsoft ออกแบบเอาไว้
ใช้ Get Data From Excel Workbook แล้วมา Browse หาไฟล์ปัจจุบันที่ทำงานอยู่ วิธีนี้ทำง่าย แต่ผมไม่แนะนำ เพราะว่าวิธีนี้มีข้อเสียสำคัญคือ ต้อง save ไฟล์ Excel ก่อน Power Query จึงจะสามารถ Refresh ข้อมูลล่าสุดมาได้
ตั้งชื่อ (Define Name) ให้กับ Range ที่ต้องการเพื่อสร้าง Named Range ก่อน
อ่อ! ขอบอกก่อนว่าบทความนี้อาจจะเหมาะกับคนที่อ่านหนังสือ Excel Power Up! หรือเคยใช้ Power Query มาบ้างแล้วนะครับ ไม่งั้นอาจจะไม่เข้าใจว่าทำไมถึงต้องมาทำอะไรยุ่งยากแบบนี้ด้วย
จากนั้นพิมพ์ใน Formula Bar ของ Power Query Editor ว่า =Excel.CurrentWorkbook() แล้วกด Enter
จะเห็นว่ามีทั้งข้อมูลที่เป็น Table จริงๆ และข้อมูลที่มาจาก Name ด้วย ซึ่งมันเข้าทางเราล่ะ!!เพราะเราจะใช้ Name นี่แหละในการอ้างอิงข้อมูลเข้า Power Query
Tips : ถ้ายังไม่มี Formula Bar ให้ไปกดติ๊กที่ View->Formula Bar ก่อน
จากนั้นคลิ๊กคำว่า Table สีเขียวๆ ที่ตรงกับ Name ที่ต้องการ คือ MyDataName เราก็จะได้ Table นั้นออกมาใน Power Query แล้วล่ะ
อย่างไรก็ตาม การสร้าง Dynamic Named Range ไว้ก่อนจะไม่สามารถเอาเข้า Power Query ได้โดยตรงด้วยวิธีที่ 1 (กด Get Data From Table/Range) แต่ยังสามารถทำด้วยวิธีที่ 2 ( สูตร = Excel.CurrentWorkbook() ) ได้อยู่ครับ
และนี่ก็คือเทคนิคที่ผมอยากจะนำเสนอครับ หากใช้เทคนิคนี้จะทลายข้อจำกัดหลายๆ อย่างของการใช้ Power Query ไปได้เยอะเลย ดังนั้น มาใช้ Power Query กันเยอะๆ นะครับ ^^
แต่ว่าคิดว่ามีคนเขียนถึงฟังก์ชันพวกนี้เยอะแล้วและมันดันเป็นฟังก์ชันที่ต้องมี Excel version ที่ใหม่สุดๆ งั้นมาเล่าฟังก์ชันที่มีนานแล้วแต่หลายคนอาจจะไม่รู้ดีกว่า
ปกติแล้วคำสั่ง Get Data from Web ใน Power Query จะใช้ดึงข้อมูลจากหน้าเว็บไซต์ได้ แต่ว่าจริงๆ แล้วมันใช้ดึงไฟล์ที่วางไว้ในเว็บได้โดยตรงโดยไม่ต้อง save ไฟล์ออกมาก่อนก็ได้นะ
ยกตัวอย่าง เช่น เราสามารถใช้ Get Data From Web ดึงข้อมูลไฟล์นั้นมาได้เลย โดยไม่ต้อง save ออกมาด้วยซ้ำ
ก่อนอื่นเราไปเตรียมตารางวันหยุดไว้ก่อน ซึ่งผมใช้ Power Query ทำไว้ให้ใน Excel ปฏิทินแล้ว โหลดได้ที่นี่เลย
พอได้ไฟล์แล้วตอนแรกหน้าตาจะเป็นแบบนี้
ปรับ Field Value ให้เป็นวันที่จริงๆ
เดี๋ยวเราจะเปลี่ยน Field ที่ใช้สรุปใน Pivot Table จาก Sum of เลขวัน ให้กลายเป็น SUM of Date แทนครับ ซึ่งแต่ละช่องจะหลายเป็นเลข 4 หมื่นกว่าๆ (ซึ่งคือค่าที่แท้จริงของข้อมูลวันที่ใน Excel ) ดังนี้
ปรับ Format ให้เห็นแค่วัน
จากนั้นให้เราคลิ๊กขวาที่เลข 4 หมื่นกว่าซักช่องนึง ให้เลือก Number Format… แล้วใส่ Custom format เป็นตัว d (ย่อมาจาก day)
พอ ok จะทำให้ Format จากเลข 4 หมื่นกว่าๆ กลายเป็นแค่เลขวัน เหมือนตอนแรกสุด (แต่ค่าที่แท้จริงคือวันที่ ซึ่งคือ 4 หมื่นกว่าๆ)
ใส่ Conditional Format ให้แสดงวันหยุดเป็นสีแดง
คราวนี้แหละ เราจะสามารถใช้ Conditional Formatting เพื่อ mark วันหยุดได้แล้ว โดยเลือกวันที่ในปฏิทินช่อง B6 (ซ้ายบนสุด) แล้วไปที่ Home –> Conditional Formatting –> New Rule แล้วเลือกดังรูป
ซึ่งถามว่าผลลัพธ์วิธีข้างบนถูกมั้ย? มันก็ถูกนะ แต่จะเจ๋งกว่าถ้าช่วงเวลามันเป็น Field ที่เอามาพลิกเล่นไปมาได้ แทนที่จะเป็น Sum of 24 อันแบบนี้
ดังนั้นเพื่อเราจะใช้ Power Query ช่วยในการตบคอลัมน์เวลา 24 อัน ให้มารวมเป็นคอลัมน์เดียวแต่มีหลายบรรทัดแทน
ใช้ Power Query ตบข้อมูลให้เป็นลักษณะ Database มากขึ้น
ตอนแรกที่ผมลองทำ Get Data from Table/Range จากข้อมูลที่เราทำไว้ ปรากฏว่า ใน Power Query เครื่องผมมันน่าจะมี Bug ทำให้เห็นเลขทุกอย่างเป็น 0 แทนที่จะเป็น 1 กับ 0 (หากเราใช้วิธี Get Data from Table/Range ตรงๆ)
ดังนั้นผมเลยเปลี่ยนวิธี Get Data เล็กน้อย เป็นการ Get Data จากไฟล์ Excel ตัวเองแทน ซึ่งหากจะใช้วิธีนี้ต้อง Save ไฟล์ Excel ของเราไว้ซะก่อนครับ
จากนั้นไปที่ Get Data -> From File -> From Workbook แล้วเลือก File ตัวเอง -> เลือก Sheet1 ที่มีตารางที่เราทำ -> กด Transform Data
มันจะมีหน้าต่างขึ้นมาถามว่าจะให้พยายามดูไฟล์ไหนเป็นตัวอย่าง (เหมาะกับกรณีที่บางไฟล์มีหน้าตาดูดีกว่าไฟล์อื่น) ถ้าไม่คิดอะไรมากก็ปล่อยเป็น First File แล้วปรับค่าให้เหมาะสมแล้ว Ok
ซึ่งเมื่อ OK เจ้า Power Query จะทำการรวมทุกไฟล์เข้าด้วยกัน ซึ่งมันบอกด้วยว่า Data แต่ละแถวมาจากไฟล์ชื่อว่าอะไร
แล้วเราค่อย Expand คอลัมน์ Data ออกมาอีกที ซึ่งคราวนี้จะมี Data จริงๆ แล้ว
จะเห็นว่าชื่อหัวตารางยังคงอยู่ใน Row ของ Data ปกติอยู่ (ซึ่งมีหลายแถวด้วย) ดังนั้นเราต้องมานั่งแก้ไขเรื่องนี้อีก เช่น เอาบรรทัดแรกเป็นหัวตารางซะด้วย Home → Use First Row As Header
บทความนี้จะอธิบายวิธีการใช้ Power Query เชื่อมกับ Google Form/Google Sheets โดยที่เราสามารถกด Refresh ที่ Excel/Power BI เพื่อดูดข้อมูลจาก Google Form/Google Sheets ณ ตอนนั้นได้เลย
นี่แหละครับ ความสุดยอดของ Power Query ซึ่งใช้ได้ทั้งใน Excel และ Power BI เลยนะ ใครอยากลองกรอกฟอร์มของผมเล่นดู ก็ไปกรอกได้ที่ https://forms.gle/2wAbkYeJdP6oHwEHA นะครับ
ใครมีคำถามหรือติดอะไรตรงไหนก็สามารถ Comment ไว้ได้นะครับ ส่วนคนที่อยากเรียนรู้เรื่อง Power Query เพิ่มเติม สามารถอ่านได้ที่ Category Power Query
ประเด็น 1 : ใน Power Query ตัวพิมพ์ใหญ่/ตัวพิมพ์เล็ก มีผลต่างกัน (Case-Sensitive) เสมอ
หากเราลองกด Filter ว่าต้องการเอาเฉพาะบรรทัดที่มีค่า aa ก็จะได้แต่ aa จริงๆ (ไม่ได้บรรทัดที่มี AA ด้วย หรือ Aa หรือ aA ด้วย) เพราะมันมองว่าแต่ละค่าต่างกันโดยสิ้นเชิง
ดังนั้นหากผลลัพธ์ออกมาไม่ใช่อย่างที่ต้องการ (โดยเฉพาะเวลา List มันน้อยๆ) ก็มีแนวทางแก้ไข คือ ควรระบุเงื่อนไขแบบชัดเจนไปเลย เช่น More Than, Less Than, Contain, Not Contain, Equal, Not Equal
ซึ่งเมนูพวกนี้จะโผล่มาก็ต่อเมื่อเรามีการกำหนด Data Type ของหัวตารางคอลัมน์นั้นๆ แล้วเท่านั้น ดังนี้
คราวนี้ผมจะแก้ชื่อคอลัมน์จากใน Formula Bar เลย จะได้ไม่ต้องมี Step เพิ่ม
= Table.AddColumn(#”Renamed Columns”, “Multiplication”, each [ยอดขาย] * 0.1, type
= Table.AddColumn(#”Renamed Columns”, “Commission”, each [ยอดขาย] * 0.1, type number)
ซึ่งวิธีแก้ชื่อคอลัมน์ที่ Formula Bar หรือ ที่ M Code ไปเลยจะดีกว่าครับ เพราะโดยหลักการแล้วยิ่งขั้นตอนน้อย ยิ่งคำนวณเร็วกว่า และการเพิ่ม Step โดยไม่จำเป็นจำทำให้อ่านแล้วงงด้วย
เรื่องพื้นฐานที่ควรจะรู้เลยคือ การจัดการหัวตาราง ไม่ว่างจะเป็นการ Promote บรรทัดแรกให้กลายเป็นหัวตาราง หรือการปรับหัวตารางให้กลับมาเป็น Data บรรทัดแรก รวมถึงการจัดการบรรทัดข้อมูลที่ไม่ต้องการออกไปก่อน กรณีหัวตารางไม่ได้อยู่บรรทัดบนสุด
บทนี้ให้ Get Data → From Table/Range จากไฟล์ตัวอย่าง (โหลดที่นี่) เพื่อเอาข้อมูลเข้าไปใน Power Query แต่คราวนี้เราไม่ต้องติ๊ก My table has headers เนื่องจากเพราะบรรทัดแรกมันไม่ใช่หัวตาราง
จากนั้นสิ่งที่เราควรทำคือ Promote ให้ข้อมูลแถวแรกกลายเป็นหัวตารางซะ โดยไปที่ Home → Use First Row as Headers จะได้ดังรูป ซึ่งพบว่ามีการ Detect ประเภทข้อมูลให้อัตโนมัติด้วย (ดูที่ icon ของหัวตาราง)
ซึ่งจะเห็นว่าเราควรจะ Fill Right เพื่อเอาข้อมูลผลไม้ไปถมช่อง null ด้านขวา แต่มันไม่มีให้เลือก
ดังนั้นเราจะพลิกตารางก่อน โดย ไปที่ Transform → Transpose เราจะได้ผลลัพธ์หน้าตาคล้ายๆ ตัวอย่างก่อนหน้านี้ ซึ่งเราก็จะ Fill Down ได้แล้ว และเราควรจะ Promote Header ด้วยโดยไปที่ Home → Use First Row as Headers
ถ้าเราพอใจกับผลลัพธ์นี้จริงๆ ก็จบได้เลยนะ แต่ถ้ายังอยากจะพลิกให้ผลไม้ไปอยู่ที่คอลัมน์เหมือน Data Source เราก็ควรทำให้หัวตารางไม่ซ้ำกัน เราจะเอาข้อมูลในคอลัมน์ผลไม้กับเกรด มา Merge รวมกัน เช่น แอปเปิ้ล A มันจะได้ไม่ซ้ำกัน
จากนั้นเราค่อยเลือกทั้งสองคอลัมน์ แล้วไปที่ Transform → Merge Columns แล้วเราสามารถเลือกตัวคั่นได้ ในที่นี้ผมเลือกเป็น Space
Using Locale เอาไว้จัดการเวลาเจอข้อมูลที่มี Format แปลกๆ ที่ขึ้นอยู่กับแต่ละประเทศ เช่น วันที่ หรือ สกุลเงิน ซึ่งจะอธิบายละเอียดในบทหลังๆ ไปครับ
กรณีที่ Step ก่อนหน้าเคยเปลี่ยน Data Type ไปแล้ว และเรากดเปลี่ยน Data Type อีก (เช่น เปลี่ยน ID สินค้าเป็น Text หลังจาก Changed Type แบบ Auto เป็นตัวเลขไปแล้ว) มันจะขึ้นมาถามว่า จะสร้าง Step ใหม่ หรือ แก้ไข Step เดิม
ถ้าเราตอบว่า Add New Step มันจะมีการแปลงประเภทข้อมูลโดย Add Step ใหม่เพิ่มเข้ามา
แต่ถ้ากด Replace Current มันจะแก้สูตรใน Step เดิม ให้ ID สินค้าเป็น Text โดยไม่เพิ่ม Step ใหม่
= Table.TransformColumnTypes(Source,{{“ID สินค้า”, type text}…
ส่วนตัวผมขอแนะนำให้ Replace Current จะดีกว่าครับ เพราะบางครั้งการแก้ Data Type ไปแล้วมันอาจสูญเสียข้อมูลบางอย่างไปเรียบร้อย เช่น แก้ให้เป็นจำนวนเต็มก่อน แล้วปรับเป็นทศนิยมทีหลังก็จะไม่มีผล เพราะค่าทศนิยมมันก็หายกลายเป็น 0 ไปหมดแล้ว
บทความนี้มีที่มายังไง?
บทความนี้เป็นส่วนหนึ่งของตัวอย่างจาก หนังสือ Excel Power Up! เพิ่มพลังการใช้ Excel ของคุณด้วย Power Query โดยผมเอาเนื้อหาบทแรกๆ ซัก 25-30% มาลงในเว็บให้อ่านกันฟรีๆ เลย คนอ่านจะได้ตัดสินใจได้ว่าอยากจะรู้เรื่องราวหลังจากนั้นอีกมั้ย? ซึ่งแค่นี้ก็น่าจะช่วยงานคุณได้เยอะพอสมควรแล้วล่ะ
Step1 : Get Data เอาไว้เลือกว่าเอาแหล่งข้อมูลจากไหน?
Get Data เป็นขั้นตอนแรกที่เราต้องทำ ซึ่งเป็นการเลือกว่าอยากให้ Power Query เอาข้อมูลจากไหน? เราสามารถเลือกข้อมูลได้หลากหลายรูปแบบมากๆ เช่น ข้อมูลจากในไฟล์ Excel เดียวกับที่เปิดอยู่ หรือเอาจากแหล่งอื่น?
ถ้าเอาจากแหล่งอื่นก็จะต้องกดปุ่ม Get Data (หรือ บางคนจะเห็นเป็นปุ่ม New Query) ก็จะดึงข้อมูลเช่น จาก Excel ไฟล์อื่น, Text File, CSV, Access, Database ต่างๆ, Website, Facebook
ตรงนี้ยังไม่ต้องซีเรียสหากวันที่ขึ้นไม่เหมือนกับผม เพราะหากเรายังใช้การ Get Data จากวันที่ใน Excel (ซึ่งบันทึกข้อมูลวันที่แบบถูกต้องแล้วจริงๆ) Power Query จะไม่มีปัญหาเรื่องวันที่ครับ แต่ถ้าดึงข้อมูลจาก CSV/Text File หรือวันที่ที่เป็น Text จะต้องมีการจัดการวันที่อย่างเหมาะสม ซึ่งผมจะมีการอธิบายโดยละเอียดในบทที่เราดึงข้อมูลจาก Text/CSV ในช่วงหลังครับ
สำรวจ Toolbar
ถ้ายังไม่เห็น Formula Bar ให้เราเข้าไปติ๊ก option ใน View → Formula Bar ซะก่อน
Tips : กรณีที่สูตรยาวมากๆ ให้กดลูกศรด้านขวาของ Formula Bar เพื่อขยายพื้นที่ให้มันใหญ่ขึ้นครับ
หากเรากดดูที่แต่ละ Step ที่เราทำไป เช่น ดู Step ที่ชื่อว่า Filtered Rows จะพบว่ามีสูตรเกิดขึ้นใน Formula Bar ที่แตกต่างกันในแต่ละ Step ด้วย
ดังนั้นการที่เรากด Filter โดยแท้จริงก็คือการเลือกว่าจะเอาบรรทัดไหนไว้ สิ่งที่เกิดขึ้นก็คือ Power Query จะทำการสร้างสูตร M Code ที่ชื่อว่า Table.SelectRows ขึ้นมาโดยอัตโนมัติ นั่นแปลว่า การกดคำคั่งเป็นเมนู เป็นแค่ตัวช่วยสร้าง M Code ขึ้นมานั่นเอง
เจ้า M Code หรือ M Language นี่เองเป็นภาษาที่ Power Query ใช้ทำงานทั้งหมด ทุกอย่างที่เกิดขึ้นใน Power Query สามารถเขียนหรือถูกแปลงออกมาเป็น M Code ได้ทั้งนั้น
จะเห็นว่าคอลัมน์ที่เราเลือกก็จะหายไป และสูตรใน Formula Bar ก็จะเปลี่ยนไปด้วย กลายเป็นสูตรของ Step ที่เราลบคอลัมน์ออก
ดู M Code ทั้งหมดได้ใน Advanced Editor
การดู M Code ที่ Formula Bar นั้นเป็นการดู Code ทีละ Step แต่ถ้าหากเราอยากดู M Code ทั้งหมดของทั้ง Query นี้เลย ให้กดดูที่ View → Advanced Editor ได้เลย เจ้า M Code นี่เองคือเบื้องหลังการทำงานทั้งหมดของ Query นี้ (ถ้ายังดูไม่รู้เรื่องเลยก็ไม่ต้องแปลกใจครับ ใครๆ เห็นครั้งแรกก็ช๊อคทั้งนั้น 555)
เราสามารถ Copy M Code นี้ไปถามเพื่อนหรือผู้เชี่ยวชาญการทำ Power Query ใน Internet เพื่อปรึกษาปัญหาต่างๆ ได้ โดยไม่ต้องนั่ง Save Screenshot ทุก Step ว่าเราทำอะไรไปบ้าง เนื่องจากหากคนอ่าน M Code เป็น ก็จะเข้าใจเรื่องราวทั้งหมดในทันที (แต่ต้องเห็นหน้าตา Data Source ตั้งต้นด้วยนะ ไม่งั้นจะรู้ได้ไงว่าแรกสุดเป็นยังไง 55)
เราต้องเข้าใจ M Code มั้ย?
โดยทั่วไปเจ้าเครื่องมือ User Interfaceมาตรฐาน ที่กดง่ายๆ ของ Power Query น่าจะช่วย Cover งานของพวกเราได้ประมาณ 70%-80% แล้วล่ะ แปลว่าถึงไม่เข้าใจ M Code เราก็ใช้ Power Query ได้ครับ
แต่มันก็ยังมีงานบางอย่างที่จำเป็นต้องมีการแก้ไข M Code ให้ Power Query เราทำงานได้ดียิ่งขึ้นไปอีกครับ ซึ่งเราจะลงรายละเอียดกันทีหลังครับ
Step 3 : Load To เพื่อสั่งว่าจะให้เอาผลลัพธ์ไว้ที่ไหน?
ถ้าเราไปที่ Home → กดที่ icon Close & Load ไปเลย ปกติจะส่งข้อมูลออกไปเป็น Table ใหม่ทันที
แต่ถ้ากดลูกศรแล้วเลือก Close & Load to… จะสามารถเลือก option ได้ว่าอยากให้ผลลัพธ์ไปโผล่ที่ไหน
Only Create Connection : สร้าง Query ไว้เฉยๆ โดยยังไม่เอาข้อมูลออกมาจริงๆ
และยังมี Option ให้เลือก Add this data to the Data Model ต่างหากอีกอันนึง ซึ่งตัว Data Model จะเอาไว้ใช้ทำ Power Pivot หรือ PivotTable แบบ Advance ต่อไปซึ่งจะรองรับเรื่องการผูก Relationship ระหว่างหลายๆ ตารางด้วยครับ
หากจะสร้าง Power Pivot จะต้องเลือก Add this data to Data Model ด้วย และเมื่อเราสร้าง Pivot Table โดยใช้ Source Data จาก Data Model ก็จะสามารถ Refresh ข้อมูลที่ Pivot Table ทีเดียวได้เช่นกัน
บทความนี้เป็นตัวอย่างตอนที่ 3 ของ Series เนื้อหา Power Query ซึ่งเป็นตัวอย่างจากหนังสือ Excel Power Up! (ใครสนใจตอนก่อนหน้านี้ให้ไปดูสารบัญด้านล่างนะครับ)
ดูแบบคลิปวีดีโอได้ที่นี่
Power Query คืออะไรกันแน่?
Power Query ก็คือ เครื่องมือที่สามารถ “ดึงข้อมูลอย่างทรงพลัง” นั่นคือมีความสามารถในการดึงข้อมูลได้จากแหล่งข้อมูลที่หลากหลายและสามารถปรับเปลี่ยนรูปแบบหน้าตาข้อมูลผลลัพธ์ได้ตามต้องการ
จุดประสงค์หลักเพื่อทำให้ข้อมูลเน่าๆ (ที่อาจจะมาจากหลายแหล่ง หลาย Format หลายหน้าตา) ให้มาอยู่ในรูปแบบที่ Database เพื่อให้สามารถนำไปวิเคราะห์ต่อใน PivotTable, Power Pivot, Power BI ได้
ที่เจ๋งมากๆ คือ เจ้า Power BI Desktop เนี่ยมีการอัปเดทความสามารถใหม่ๆ ทุกเดือนด้วย (โคตรเจ๋ง!)
ดังนั้นผมขอแนะนำให้ผู้อ่านทุกท่านเลือกเอาว่าจะใช้ Power Query ใน Excel หรือจะใช้ใน Power BI Desktop ก็ได้ครับ แต่ถ้าใช้ใน Power BI ก็จะมั่นใจว่าได้เวอร์ชันใหม่ล่าสุดแน่นอน แต่ตัวอย่างในหนังสือเล่มนี้จะใช้ Power Query จากใน Excel365 ครับ
Computer ที่ออฟฟิศลงโปรแกรมเองไม่ได้?
ผมแนะนำว่า หาก Computer ที่คุณใช้ไม่สามารถลงโปรแกรมดังกล่าวเองได้ ให้ลองไปเจรจากับทาง IT ให้ลงโปรแกรมให้ครับ ถ้าทาง IT ไม่ยอม ผมแนะนำให้ลองขอหัวหน้าของเราให้ไปช่วยคุยอีกทีครับ 555
สาเหตุเพราะโปรแกรม Power Query มันมีประโยชน์มากและช่วยลดเวลาทำงานได้มหาศาลเลยล่ะ หากคำนึงถึงเรื่องราคาก็ไม่ต้องห่วงเพราะมันฟรีครับ และไม่ผิดลิขสิทธิ์ด้วย คุ้มกว่านี้ไม่มีแล้วครับ
ให้เราคลิ๊กตรงไหนก็ได้ในตาราง เช่น A3 แล้วไปที่ Data → From Table/Range (บางคนอาจจะเห็นเป็น From Table เฉยๆ) แล้วกด Ok แล้วมันจะเปิดหน้าต่าง Power Query Editor ขึ้นมา
จะเห็นว่าข้อมูลในคอลัมน์สินค้าช่องที่ว่างๆ อยู่จะขึ้นว่า null ซึ่งเราไม่ต้องการให้มันว่าง ให้เราเลือกคอลัมน์สินค้าแล้วไปที่ Transform → Fill → Down จะพบว่า Power Query จะถมช่องว่างได้อย่างง่ายดาย!
ตรงนี้เหมือนว่าการใช้ Pivot Table จะเสียเปรียบพอสมควร ซึ่งทาง Microsoft เองเลยทำเครื่องมือใหม่มาใช้เพื่อทำกราฟหรือ Visualization เจ๋งๆ โดยเฉพาะ นั่นก็คือ Power BI นั่นเอง
นี่เป็นตัวอย่างของการใช้เครื่องมือยุคใหม่ว่ามันมีความฉลาดมากขึ้นขนาดไหน ซึ่งในหนังสือเล่มนี้ผมจะแนะนำให้รู้จักอีกเครื่องมือนึงที่เจ๋งกว่า Flash Fill อีก มันมีชื่อว่า Power Query ซึ่งใช้ได้ตั้งแต่ Excel 2010 ขึ้นไป (แต่ถ้าไม่มี Excel version ที่ใช้ได้จริงๆ ก็ยังดาวน์โหลดโปรแกรม Power BI Desktop ซึ่งมี Power Query มาใช้ได้ฟรีๆ ไม่ผิดลิขสิทธิ์นะ)
ในไฟล์ version ก่อนหน้านี้เราได้มีการแยกตารางซื้อขายออกมาเป็นคนละตาราง เพื่อเพิ่มความยืดหยุ่นให้มากขึ้นไปแล้ว มาวันนี้เราจะสามารถกำหนดช่วงเวลาเพื่อที่จะสามารถดูข้อมูลเฉพาะในช่วงที่ระบุได้ด้วย
This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary cookies are absolutely essential for the website to function properly. These cookies ensure basic functionalities and security features of the website, anonymously.
Cookie
Duration
Description
cookielawinfo-checkbox-analytics
11 months
This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checkbox-functional
11 months
The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checkbox-necessary
11 months
This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-others
11 months
This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-performance
11 months
This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
viewed_cookie_policy
11 months
The cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.
Functional cookies help to perform certain functionalities like sharing the content of the website on social media platforms, collect feedbacks, and other third-party features.
Performance cookies are used to understand and analyze the key performance indexes of the website which helps in delivering a better user experience for the visitors.
Analytical cookies are used to understand how visitors interact with the website. These cookies help provide information on metrics the number of visitors, bounce rate, traffic source, etc.
Advertisement cookies are used to provide visitors with relevant ads and marketing campaigns. These cookies track visitors across websites and collect information to provide customized ads.
