บทความทั้งหมด
-
List ฟังก์ชันทั้งหมดของ Excel ในทุก Version
คุณสงสัยหรือไม่ว่า Excel แต่ละ Version มีฟังก์ชันอะไรให้ใช้แตกต่างกันบ้าง? แม้ว่าทาง Microsoft จะมีเว็บบอกไว้ แต่มันช่างใช้งานยากเหลือเกินว่าตกลงแล้ว Excel แต่ละ Version มี Function อะไรกันแน่ ผมก็เลยทำรายงาน Power BI และไฟล์ Excel ขึ้นมารวบรวมไว้ให้เผื่อคนที่อยากจะรู้นะครับ แล้วเครื่องมืออื่นๆ ล่ะ? สำหรับเรื่องที่ Excel แต่ละ Version มีฟังก์ชันไม่เท่ากันนั้น จริงๆ แล้วนอกจาก Version แล้วก็ยังมีเรื่องของเครื่องมืออื่นๆ ด้วย ดังนั้นผมจึงสรุปออกมาเป็น Diagram ให้ด้วยดังนี้ครับ เพราะหลายคนก็สับสนอยู่ว่าอะไรใช้กับ Version ไหนได้บ้าง?
-
เปรียบเทียบ MAX vs LASTDATE ในภาษา DAX
เมื่อเราศึกษา DAX ใน Power BI/Excel Power Pivot ไปซักพัก บางทีก็จะเจอหลายฟังก์ชันที่ทำงานคล้ายกันมากๆ เช่น MAX กับ LASTDATE แต่การที่เราจะเข้าใจมันได้อย่างแท้จริงก็คือต้องลองหามุมคิดที่ว่า แต่ละฟังก์ชันที่คล้ายกันนั้น มีจุดเหมือนหรือจุดต่างกันอย่างไร ป.ล. เว็บ SQLBI เคยเขียนสรุปไว้ในนี้ ผมขอนำมาตีความเพิ่มเติม รวมถึงใส่ภาพประกอบและตัวอย่างให้เพื่อนๆ เข้าใจง่ายขึ้น โดยที่ช่วงแรกผมจะขอพูดการใช้งานในฐานะ Measure ซะก่อน แล้วอธิบายเวลาเอาไปใช้ใน Calculated Column ทีหลัง เพราะมีพฤติกรรมที่ซับซ้อนเกิดขึ้นกับ LASTDATE ในกรณี Column นะครับ เอาล่ะมาเริ่มจากฟังก์ชันพื้นฐานอย่างMAX ซะก่อน MAX แบบพื้นๆ MAX จะหาค่าสูงสุด โดยให้ผลลัพธ์ออกมาเป็นค่าแบบ Scalar ก็คือเป็น Value ตัวเลขธรรมดาๆ ไปเลย ซึ่งใช้ได้กับ Data Type…
-
Power BI ตอนที่ 22 : การวิเคราะห์ Event ที่มีช่วงเวลาเริ่มต้นกับสิ้นสุดคนละวัน
ในตัวอย่างที่ผ่านมาทั้งหมด มักจะเป็นเหตุการณ์ของวันนั้นๆ ที่เราสนใจเลย เช่น ถ้าเป็นการขายของ ก็จะสามารถวิเคราะห์ได้ว่า วันนั้นๆ มีการขายของอะไรไปเท่าไหร่บ้าง? แต่ถ้าหากเหตุการณ์ของเรามันเป็นช่วงเวลา ที่มีวันเริ่มต้นกับสิ้นสุดไม่ตรงกันล่ะ เราจะทำยังไงดี? เช่น บอกเป็นช่วงเวลาของเรื่องเหล่านี้ว่าเริ่มวันไหนจบวันไหน การซื้อสินค้าที่มีเรื่องของวันสั่งซื้อกับวันส่งของที่ไม่ตรงกัน Campaign การตลาด ช่วงเวลาการซ่อมแซมเครื่องจักร สัญญา/กรมธรรม์ แบบนี้เราจะทำการวิเคราะห์ข้อมูลได้อย่างไร? คำถามนี้ผมเชื่อว่าหลายคนคงเจอกับการทำงานอยู่ และก็ไม่รู้จะทำยังไงกับมันดี ดังนั้นผมเลยเขียนบทความนี้ขึ้นมาช่วยแก้ไขปัญหาให้ครับ แนวทางแก้ปัญหา และ ไฟล์ประกอบ โชคดีที่ท่านอาจารย์ Alberto Ferrari แห่ง SQLBI ได้เขียนบทความแนวนี้ไว้แล้วด้วย ซึ่งเป็นทางแก้ที่น่าสนใจ ดังนั้นผมก็จะขออธิบายโดยใช้แนวทางของเค้า และเพื่อให้ง่ายกับทุกคน ผมก็จะใช้ไฟล์ประกอบของเค้าด้วยครับ (ซึ่งเป็นเรื่องของวันสั่งซื้อของกับวันส่งของ) แต่ผมก็จะมีการอธิบายเสริมด้วยความรู้ที่ผมมี เผื่อเพื่อนๆ จะเข้าใจมากขึ้นนะครับ ถ้าผูก Relationship แบบทื่อๆ หากเราใช้การผูก Relationship ตามปกติ เช่น ผูกแค่วันเริ่ม Event แบบปกติ การผูกแบบเส้นเดียวกับแค่วันเริ่มต้น…
-
Power BI ตอนที่ 21 : แสดงข้อมูลสรุปแบบ Top N + Others (ฉบับเทพเอ็กเซล)
วันก่อนผมได้เจอบทความของพี่บิว วิศวกรรีพอร์ต ที่อธิบายเรื่องวิธีการสรุปข้อมูลแบบ TopN+Others ใน ใน Power BI ซึ่งก็คือการแสดง Top N ที่สนใจ แต่สามารถแสดงข้อมูลอื่นๆ ที่อันดับมากกว่า Top N ให้รวมกันเป็น Others ได้ด้วย ในบทความนั้นพี่บิวบอกว่าได้ดัดแปลงสูตร DAX จากบทความของฝรั่งชื่อ Gerhard Brueckl ที่เขียนแนวทางการทำการสรุปแบบนี้ไว้ ให้มีประสิทธิภาพมากขึ้น ซึ่งบทความของคุณ Gerhard และ พี่บิวก็มีเทคนิค DAX มากมายที่น่าสนใจ แนะนำให้ลองเข้าไปศึกษาดูครับ หลังจากได้อ่านบทความพี่บิว ผมก็เลยคิดว่า ถ้าผมจะลองทำวิธีอื่น ที่ไม่ซ้ำกับทั้งสองท่านเลยจะทำได้มั้ยนะ? (ซึ่งก็แทบไม่เหลือวิธีอะไรมากแล้วล่ะ… 555) หลังจากลองอยู่ซักพัก ผลลัพธ์ของมันก็คือบทความนี้นั่นเองครับ หวังว่า Concept ของผมจะทำให้เพื่อนๆ สามารถทำการสรุปแบบ Top N + Others ได้เช่นกันครับ…
-
วิธี Export ข้อมูลจาก Power Query ที่เยอะเกินล้านบรรทัดออกมาเป็น Text File
ช่วงนี้เพื่อนๆ หลายคนเริ่มใช้ Power Query กันเยอะมากขึ้นแล้ว ทำให้เริ่มเจอปัญหาการใช้งานมากขึ้น (ซึ่งเป็นเรื่องที่ดี) มีคำถามจาก Inbox ที่ช่วงนี้ผมได้รับมาบ่อยนั่นคือ จะสามารถ Export ข้อมูลที่สร้างจาก Power Query แล้วเยอะเกินล้านบรรทัดได้ยังไง? เพราะจะเอาออกมาเป็น Table ใน Sheet ของ Excel ก็ไม่ได้ เพราะมันรับได้แค่ 1,048,576 บรรทัด หรือ ล้านกว่าบรรทัด (ต่อ 1 Sheet) เท่านั้น ผมคิดว่าคงติดปัญหานี้กันหลายคนพอสมควร การเขียนบทความอธิบายน่าจะเกิดประโยชน์สูงสุดครับ แนวทางแก้ไขปัญหา หลักการโดยรวมคือ เราจะเอาผลลัพธ์จาก Power Query Load เข้าสู่ Data Model ก่อน (เพราะมันรับข้อมูลได้ไม่จำกัด ต่างจาก Sheet Excel) แล้วค่อย Export…
-
วิธี Append ต่อแถวจากตาราง 2 อัน แบบสลับแถว
มีคน inbox มาถามว่า “โจทย์คือ มีข้อมูล 2 ชุด ชุด A and B จะทำการรวมข้อมูลโดยการที่ให้ ข้อมูล A 1 แถว แล้วต่อด้วยข้อมูล B 5 แถว แล้วทำสลับไปแบบนี้เรื่อยๆ สามารถทำยังไงได้มั่งครับ?” ผมเห็นว่าน่าสนใจดีเลย เขียนเป็นบทความนี้เพื่อตอบให้เลยครับ วิธีการทำที่ผมเห็นว่าง่ายที่สุด ก็คือใช้ Power Query ทำ เพราะมันคือการ Transform/Reshape Data ซึ่งเป็นสิ่งที่ Power Query เก่งที่สุด ซึ่งเดี๋ยวผมจะสร้างข้อมูล Dummy ขึ้นมา 2 ชุด เป็นชุด A กับ B ดังนี้นะครับ แนวคิด หากเราทำแบบตรงไปตรงมา คือเอาตาราง A…
-
13 กับดักสุดเกรียนใน Excel ที่ใครโดนต้องกุมขมับ (พร้อมวิธีแก้ไข)
หลายครั้ง Excel แสดงอาการแปลกๆ ออกมาทำให้เรางง รู้หรือไม่เป็นเพราะว่าเรากำลังเจอกับกับดักอยู่โดยที่ไม่รู้ตัว ไม่ว่ากับดักนั้นจะถูกสร้างมาอย่างตั้งใจหรือไม่ตั้งใจก็ตาม (บางทีอาจเป็นคุณเองนั่นแหละที่เป็นคนวางกับดักตัวเอง…) ในบทความนี้คุณจะได้เรียนรู้อาการแปลกๆ หลายอย่างพร้อมทั้งวิธีแก้ไขด้วยอย่างแน่นอนครับ ปล. จริงๆ แล้วผมลังเลอยู่นานว่าจะเขียนบทความนี้ออกมาดีมั้ยเพราะอาจมีคนเกรียนๆ เอาวิธีสร้างกับดักเหล่านี้ใช้ทำอะไรแผลงๆ แต่คิดไปคิดมา มันก็เป็นอีกวิธีที่ทำให้ผู้อ่านได้ความรู้ Excel เพิ่มขึ้นได้เช่นกันเลยคิดว่าเขียนดีกว่า เอาล่ะ เพื่อไม่ให้เสียเวลา มาดูกันเลยดีกว่าว่ามีกับดักอะไรบ้าง? กับดัก 1 : ทำให้เพื่อนเขียนสูตรไม่ติด ปกติแล้วเราสามารถปรับ Number Format ให้แต่ละ Cell สามารถแสดงข้อมูลออกมาในรูปแบบที่แตกต่างกันได้ เช่น จะมีทศนิยมกี่ตำแหน่ง จะแสดงเป็น % หรือไม่ ซึ่งหนึ่งในรูปแบบมาตรฐานที่สามารถนำมาแกล้งเพื่อนได้คือ รูปแบบ Text ซึ่งหลังจากปรับ Format เป็น Text แล้วจะทำให้เมื่อพิมพ์อะไรลงไปใน Cell ก็ตาม Excel จะมองว่าข้อมูลนั้นเป็น Text ทั้งหมดเลย โดยไม่ต้องใส่เครื่องหมาย…
-
วิธีเช็คว่าเลขแต่ละหลักเรียงจากน้อยไปมากหรือยัง
อาทิตย์ก่อนหน้ามีแฟนเพจ inbox ถามมาว่า มีวิธีตรวจสอบอย่างไรว่าตัวเลข 5 หลักที่กรอกใน Cell นั้นเรียงจากน้อยไปมากแล้วหรือยัง? ผมคิดว่าเป็นเรื่องที่น่าสนใจ ดังนั้นมาดูวิธีทำกันครับ สมมติตัวอย่างเป็นแบบนี้ วิธีทำมีได้หลายแบบ เช่น ถ้าใช้ Excel 365 ก็จะมีฟังก์ชันพิสดารให้ใช้เยอะแยะ แต่ถ้าใช้ Excel ที่เก่ากว่า 365 จะทำอะไรแปลกๆ ได้ยาก จึงเหมาะกับการเขียนเช็คทีละช่องให้ชัดเจนไปเลยเช่น ถ้าใช้ Excel 365 ขั้นตอนแรกคือดึงข้อมูลแต่ละตัวมาพิจารณาด้วย MID เช่น =MID(A2,SEQUENCE(LEN(A2)),1) จากนั้นก็ลอง Sort ข้อมูลใหม่ ด้วย SORT =SORT(MID(A2,SEQUENCE(LEN(A2)),1)) แล้วใช้ CONCAT หรือ TEXTJOIN รวมข้อความอีกที =CONCAT(SORT(MID(A2,SEQUENCE(LEN(A2)),1))) CONCAT แล้วจะเป็น Text ยังไงก็ไม่เท่าเลขเดิม ดังนั้นเราบังคับ Text ให้เป็น…
-
Excel Array Formula ตอนที่ 2 : การใช้ Array Formula แบบมีเงื่อนไขด้วย IF
ในตอนที่แล้ว เราได้เรียนพื้นฐานของการใช้ Array Formula กันไปแล้ว ในตอนนี้ผมจะใช้ IF และ Boolean Logic มาช่วยสร้างสูตร Array Formula แบบมีเงื่อนไขกันครับ ใช้ความเป็น FALSE ของ IF วิธีนี้เราจะใช้ IF เช็คเงื่อนไข ถ้ากรณีเป็นจริงจะทำตามที่กำหนด กรณีเป็นเท็จจะปล่อยให้เป็น FALSE ไปเพื่อ Ignore การคำนวณนั้นๆ เช่น กรณีสนใจแค่เพศหญิง ก็ใช้สูตรแบบนี้ได้ (สังเกตว่าเราจะไม่ใส่ value_if_false ) ถ้าหากอยากได้ค่าน้อยสุดก็เอา MIN ไปครอบซะ เช่น แค่นี้เราก็สามารถเลียนแบบฟังก์ชัน MINIFS ได้แล้ว แต่มันเจ๋งกว่านั้น เพราะเราใช้ฟังก์ชันอะไรก็ได้ เราจะใช้ MEDIAN ก็ยังได้ เช่น กรณีสนใจค่า MEDIAN กรณีเป็นผู้ชายก็เขียนสูตรดังนี้ได้ กรณีมีหลายเงื่อนไขแบบ…
-
การใช้ Excel Power Pivot ตอนที่ 5 : การทำรายงาน Actual vs Target
การใช้ Pivot Table แบบปกตินั้นเวลาเราจะทำรายงานยอดขาย Actual vs Target เราจะทำต้องทำตาราง Pivot แยกกัน 2 อัน แล้วค่อยเอามาเทียบกันด้วยวิธีอะไรบางอย่าง ไม่ว่าจะ Copy Paste หรือ VLOOKUP มาอยู่ข้างๆ กัน แต่ถ้าหากเราใช้ Concept ของ Data Model เราจะสามารถทำรายงาน Actual vs Target ในตาราง Pivot เดียวได้เลย โหลดไฟล์ Target โหลดไฟล์ Target ได้ที่นี่ วิธีการทำรายงาน Actual vs Target จากหลักการของ Data Model ที่เราเรียนมาในตอนที่แล้ว จะช่วยให้เราสามารถทำรายงาน Actual vs Target ได้โดยง่าย…
-
การใช้ Excel Power Pivot ตอนที่ 4 : Data Model ทำงานอย่างไร
ก่อนที่เราจะทำรายงาน Actual vs Target ได้นั้นเราจะต้องมีความเข้าใจเรื่องของ Data Model ซะก่อนว่ามันทำงานยังไง ซึ่งในตอนที่แล้วผมแค่ทำการสร้าง Relationship แล้วทำรายงานให้ดูเฉยๆ แต่ยังไม่ได้อธิบายการทำงานของมันเลย จึงคิดว่าเพื่อเป็นพื้นฐานที่ดี เราควรจะเข้าใจการทำงานของมันอย่างลึกซึ้งยิ่งขึ้นซะก่อน สาเหตุที่วิเคราะห์ข้อมูลข้ามตารางได้ การที่เราสามารถลากข้อมูลจากคนละตารางมาวิเคราะห์ร่วมกันได้ เพราะมันมีการ Filter ข้อมูลข้ามตาราง ผ่านทิศทางของเส้น Relationship ซึ่งสังเกตว่ามันจะวิ่งจาก Dimension Table (ตารางอ้างอิง) ไป Fact Table (ตารางหลัก) เช่น ที่เราลากประเทศลงมา แล้วตัวเลขต่างๆ ใน Pivot สามารถแสดงออกมาได้ถูกต้องตามรูปนี้ มันมีหลักการดังนี้ สมมติเราดูที่ประเทศลาว : การที่ได้เลข TotalSales 24,231 และ Count of TXID 49 มันมาจากอะไร? เราต้องอย่าลืม Concept ที่สำคัญที่สุดของการคำนวณทุกอย่างใน…
-
ซีรีส์สอนวิธีใช้ Pivot Table ใน 5 นาที
คลิปนี้ผมจะมาสอนการใช้เครื่องมือสรุปผลข้อมูลที่เจ๋งที่สุดใน Excel อย่าง Pivot Table ให้เพื่อนๆ ที่ยังไม่เคยใช้มาก่อนได้รู้จักและใช้มันเป็นภายใน 5 นาทีครับ (ซึ่งน่าจะมีหลายตอนเลยล่ะ ติดตามกันไปยาวๆ นะ) สามารถโหลดไฟล์ประกอบได้ที่นี่ : https://github.com/ThepExcel/download… แล้วคลิ๊กคำว่า Download นะ
-
การใช้ Excel Power Pivot ตอนที่ 3 : ลาก่อน VLOOKUP สวัสดี Data Model
ในตอนนี้เราจะมาเรียนรู้เรื่องของการผูก Data Model ซึ่งก็คือการทำให้ข้อมูลหลายๆ ตารางมาผูกความสัมพันธ์กัน (Relationship) รวมกันเป็นสิ่งที่เรียกว่า Data Model ซึ่งจะช่วยให้เราสามารถดึงข้อมูลจากตารางอื่นมาใช้ Pivot ร่วมกับตารางหลักได้อย่างสบายๆ ไม่ต้องใช้ VLOOKUP อีกต่อไป เอาข้อมูลแต่ละตารางเข้าสู่ Data Model แนวทางที่ผมอยากแนะนำคือ ให้แปลงข้อมูลเป็น Table ก่อน แล้วตั้งชื่อ Table ซะ แล้วค่อย Add เข้า Data Model ครับ เราจะมาทำทีละตารางกัน (ตารางหลักเราทำไปแล้ว) เริ่มจากการ Convert เป็น Table (Ctrl+T) และ ตั้งชื่อ Table ซึ่งผมตั้งว่า SalesBio Convert และตั้งชื่อให้ครบทุกตารางเลยนะครับ (ยกเว้นตาราง Commission ซึ่งยังยากไปขอข้ามไปก่อน) ตารางอื่นๆ ผมตั้งชื่อดังนี้…
-
การใช้ Excel Power Pivot ตอนที่ 2 : ทำผลสรุป Value ให้เป็นข้อความด้วย DAX
ตามปกติแล้ว ในช่อง Values ของ Pivot Table จะแสดงข้อมูลออกมาได้แค่ตัวเลขเท่านั้นไม่สามารถทำเป็นข้อความได้ ที่เป็นแบบนั้นเพราะมันถูกสรุปด้วยการ Sum, Count, Average, Max, Min ปกติไงล่ะ… แต่ใน Power Pivot เราสามารถเขียน Measure ได้ด้วยฟังก์ชัน DAX อะไรก็ได้ ดังนั้นเราก็สามารถสรุปผลลัพธ์ที่เป็น Text ได้แล้วล่ะ และในบทความนี้ก็จะสอนวิธีทำให้ครับ Warning : บทความนี้เริ่มมีความซับซ้อน ในบทความนี้อาจมีสูตรหลายตัวที่ค่อนข้างมีความซับซ้อน เพราะเกี่ยวข้องกับ Concept หลายๆ อย่างของ DAX ที่ใน Excel ไม่มี เช่นเรื่องของ Table Function หรือสูตรที่ให้ผลลัพธ์ออกมาเป็นตาราง รวมถึง Concept ของการคำนวณ ซ้ำๆ แต่ละแถวของตารางด้วย Expression ที่กำหนด (เรียกว่า…
-
การใช้ Excel Power Pivot ตอนที่ 1 : การใช้งานพื้นฐาน
คงปฏิเสธไม่ได้ว่า Pivot Table นั้นเป็นเครื่องมือสรุปข้อมูลที่ใช้ง่ายที่สุดของ Excel (จริงๆ ผมว่าถ้าเทียบกับโปรแกรมอื่น Pivot ก็ยังง่ายและเจ๋งกว่าอยู่ดี) ซึ่งการใช้ Pivot Table แบบทั่วๆ ไปก็สามารถตอบโจทย์การทำงานได้มหาศาลแล้ว อย่างไรก็ตาม ก็ยังมีงานบางอย่าง ที่ Pivot Table ธรรมดายังตอบโจทย์ไม่ได้ แต่ต้องใช้ Power Pivot แทน เช่น นี่คือตัวอย่างของสิ่งที่Pivot ธรรมดาๆ ทำไม่ได้… แต่ว่าไม่ต้องเสียใจไป เพราะจริงๆ แล้วถ้าเราใช้ Pivot Table อีกโหมดนึงที่เรียกว่าโหมด Data Model ซึ่งจะทำให้ Pivot Table ธรรมดากลายเป็น Power Pivot ซึ่งจะมีความสามารถเพิ่มขึ้นมหาศาลใกล้เคียงกับความสามารถของ Power BI เลยล่ะ อย่างไรก็ตาม Excel ที่จะใช้ Data…
-
เรียน Calculus ด้วย Excel ตอนที่ 3 : Optimization หาจุดสูงสุด จุดต่ำสุด
ตอนก่อนหน้านี้เราได้เรียนรู้ทั้งเรื่องการหาอนุพันธ์และการอินทิเกรทไปแล้ว ในตอนนี้เราจะมาดูวิธีประยุกต์ใช้ความรู้เรื่องอนุพันธ์เพื่อทำ Optimization โดยการหาจุดสูงสุดต่ำสุดกันซักหน่อยครับ การทำ Optimization ด้วยการหาอนุพันธ์ เวลาเรามีฟังก์ชันอะไรซักอย่าง แล้วอยากจะหาว่าจุดสูงสุดหรือจุดต่ำสุดในช่วงที่สนใจอยู่ที่ตำแหน่งไหน? เราจะทำยังไงกับมันได้บ้าง? ก่อนจะไปหาค่าที่ทำให้เกิด “จุดสูงสุด” หรือ “จุดต่ำสุด” เราไปรู้จักอีกคำนึงก่อน นั่นก็คือ Critical Value หรือ ค่า x ที่ทำให้เกิดจุดวกกลับ (Extreme Point) ของเส้นกราฟซะก่อน ซึ่งจุดวกกลับก็จะมี 2 ลักษณะอีกคือ ถ้าความชันของกราฟเปลี่ยนจากบวก ไป0 แล้ว ไปลบ แสดงว่าจุดวกกลับนั้นเป็นแบบตัว A ซึ่งก็น่าจะทำให้เกิดจุดต่ำสูงสุด ถ้าความชันของกราฟเปลี่ยนจากลบ ไป0 แล้ว ไปบวก แสดงว่าจุดวกกลับนั้นเป็นแบบตัว U ซึ่งก็น่าจะทำให้เกิดจุดต่ำสุด แปลว่าถ้าเราหาสมการของอนุพันธ์ได้ แล้วเอามาเทียบกับค่า 0 ก็จะสามารถแก้สมการหาได้ว่า x ค่าใดทำให้เกิดความชันเป็น 0 บ้าง…
-
สารพัดวิธี Lookup หาค่าตัวสุดท้าย
หลายๆ คนคงรู้จักการใช้พวก VLOOKUP หรือ MATCH เพื่อหาข้อมูลที่ต้องการแบบ Exact Match กันอยู่แล้ว ซึ่งมันจะได้ค่าของ lookup_value ตัวแรกที่เจอ (นับจากข้างบนลงล่าง) แต่ก็มีหลายสถานการณ์ที่เราอยาก Lookup หาค่าตัวสุดท้ายมากกว่า โดยเฉพาะเวลาที่ฐานข้อมูลเรียงตามวันที่/เวลาจากเก่าไปใหม่ เราย่อมอยากได้ข้อมูลที่อัปเดทล่าสุดมากกว่าอยากได้บรรทัดแรกจริงมั้ย? เช่นข้อมูลเป็นแบบนี้ ในบทความนี้ผมจะสอนวิธีการ Lookup หาค่าตัวสุดท้าย มาดูกันว่าทำยังไง เอาตั้งแต่แบบเข้าใจง่ายไปยาก (นิดๆ)เลย กรณีที่ 1 : กรณียอมให้เรียง Data ใหม่ได้ทั้งหมด ถ้าแบบนี้ก็ง่ายสุด ให้เรากด sort ใหม่ ให้เอาวันเวลาใหม่สุดมาอยู่ข้างบน แบบี้เราจะ VLOOKUP แบบ Exact Match ได้เลย =VLOOKUP(G2,C:D,2,FALSE) กรณีที่ 2 : กรณียอมให้เรียง Product ใหม่ได้ แต่ใน…
-
เรียน Calculus ด้วย Excel ตอนที่ 2 : Integrate กับพื้นที่ใต้กราฟพิศวง
ในตอนที่แล้วเราได้เรียนเรื่องพื้นฐาน Calculus และเรื่องการหา Derivative กันไปแล้ว ในตอนนี้เราจะมาเรียนอีกเรื่องนึงที่สำคัญมากๆ นั่นก็คือการ Integrate นั่นเองครับ ซึ่งเจ้า Integration หรือ การอินทิเกรต คือ กระบวนการที่ตรงข้ามกับการหาอนุพันธ์ หรือ Derivative นั่นเอง Integrate นั้นตรงข้ามกับ Differentiate รูปแบบทั่วไปคือ ถ้าสมมติ diff ฟังก์กัน F(x) แล้วได้ฟังก์ชัน f(x) หาเรา integrate f(x) จะได้ F(x) + c โดยที่ เครื่องหมาย ∫ คือเครื่องหมาย Integral dx คือ ตัวที่บอกว่ามันย้อนการ diff ที่เทียบกับ dx มา c คือค่าคงที่ (Constant)…
-
เรียน Calculus ด้วย Excel ตอนที่ 1 : พื้นฐานแคลคูลัส
วันก่อนผมได้ถามแฟนเพจว่าอยากให้สอนเรื่องอะไรเป็นพิเศษ มีคำตอบนึงทำให้ผมสนใจมาก นั่นคือ อยากให้สอนพื้นฐาน Calculus แบบใช้ Excel มาช่วยคำนวณ ตั้งแต่ Limit, Differential, Integral etc. ดังนั้น นี่คือบทความที่จะตอบเรื่องนั้นครับ ไม่แน่ใจเหมือนกันว่าจะมีซักกี่ตอน (เพราะเรื่องนี้อาจไม่มีคนสนใจมากนัก ถ้าไม่ใช่สายวิทย์) แต่คิดว่าคงไม่ค่อยมีใครเขียนถึงอะไรแบบนี้เท่าไหร่ และถือเป็นการทบทวนความรู้ Math ผมไปด้วยในตัว ก็เลยทำซะเลย 555 เอาล่ะ เรามาเริ่มกันเลยดีกว่าว่า Calculus คืออะไร ความหมายของการ Diff จริงๆ คืออะไร แล้วใช้ใน Excel ยังไง? Calculus คืออะไร? มันคือหลักการทางคณิตศาสตร์ที่ใช้ศึกษา “อัตราการเปลี่ยนแปลง” ซึ่ง ไอแซค นิวตัน ได้คิดต้นขึ้นเพื่อใช้อธิบายปรากฏการณ์ทางฟิสิกส์ต่างๆ เช่น กฎการเคลื่อนที่ กฎแห่งแรงโน้มถ่วง ที่คณิตศาสตร์สมัยนั้นอธิบายไม่ได้ (จริงๆ มีอีอกคนนึงก็คิดค้น Calculus…
-
Power BI ตอนที่ 20 : การคำนวณต้นทุนแบบ FIFO ด้วย DAX
ตอนนี้จะเป็นเนื้อหาที่ประยุกต์ใช้ความสามารถของ DAX ที่เราเรียนมา ในการคำนวณสิ่งที่ค่อนข้างซับซ้อน นั่นก็คือ ต้นทุนแบบ FIFO นั่นเองครับ ซึ่งวิธีในบทความนี้จะสามารถทำให้คุณสามารถคำนวณสิ่งนี้ได้แบบอัตโนมัติ ไม่ต้องทน Manual อีกต่อไป 555 แต่ก่อนอื่นเรามาทบทวนหลักการของ FIFO กันนิดนึงก่อนจะไปดูวิธีทำใน DAX ( Edit 30/6/2020 14:20 : ผมมีแก้ Code ที่ Error เรื่องเครื่องหมาย & ให้แล้วนะครับ รบกวนลอง refresh หน้าเว็บด้วย) FIFO คืออะไร? FIFO หรือ First In, First Out คือวิธีการทางบัญชี ที่จะใช้ต้นทุนสินค้าชิ้นที่ซื้อเอาไว้เก่าที่สุดก่อนแล้วค่อยไล่ไปชิ้นใหม่ขึ้นเรื่อยๆ ซึ่งผลของมันจะเห็นได้ชัดเลยกับสินค้าที่มีราคาซื้อเปลี่ยนแปลงไปเยอะๆ เช่น เราซื้อสินค้าไป 3 รอบ จากเก่าไปใหม่ คือ 2…
-
Power BI ตอนที่ 19 : การปรับ Cross Filter Direction เพื่อคำนวณค่าในตาราง Dimension
บทความนี้จะสอนเรื่องการปรับค่า Cross Filter เพื่อให้สามารถคำนวณข้อมูลที่อยู่ในตาราง Dimension จากตารางอื่นได้ แต่ก่อนจะไปดูเรื่องนั้น มาทวนเรื่องการคำนวณกรณีที่ Measure หลักอยู่ใน Fact Table กันซะก่อนครับ เมื่อ Measure หลัก คำนวณจากข้อมูลใน Fact Table ในตอนที่ 14 เรื่องของ Context Transition ผมมีเขียนสูตรอธิบายการใช้ CALCULATE ผสมกับพวก SUMX AVERAGEX MAXX (เรียกว่า Iterator) ซึ่งจะสามารถเพิ่มการคำนวณมุมมองใหม่ๆ ให้กับ Measure หลัก ซึ่งคำนวณจากข้อมูลในตาราง Fact ผ่านมุมมองต่างๆ ของตาราง Dimension เช่น จะเห็นว่า TotalRevenue คำนวณจากตาราง fSales ดังนั้นจึงสามารถ Filter จากตารางอื่นได้เมื่อเราใช้ พวก…
-
Power Query Speed Up Tips : รวมเทคนิคแก้ปัญหา Power Query ช้า ให้ทำงานเร็วขึ้น
ในบทความนี้จะเป็นการรวบรวมเทคนิคและความรู้ความเข้าใจในการที่จะแก้ปัญหา Power Query ช้า ให้ทำงานเร็วขึ้น เพราะหากคุณเริ่มใช้ Power Query เป็น คุณจะเริ่มใช้มันกับทุกสถานการณ์ หากคุณเริ่มทำมันเยอะพอ คุณคงต้องเคยเจอปัญหาว่าบางทีมันทำงานช้ามากตอนที่กด Refresh All เพื่ออัปเดทข้อมูล ไม่ว่าจะเป็นใน Excel หรือ Power BI ก็ตาม เพื่อไม่ให้เสียเวลา เรามาเริ่มกันเลยครับ ตั้งค่า Data Load ให้เหมาะสม ปกติแล้วเวลาเราทำงานกับ Power Query เรามักจะสร้าง Query Step ย่อยๆ ขึ้นมาระหว่างทาง แล้วสุดท้ายก็เอามาใช้จริงแค่บาง Query เท่านั้น นอกนั้นเป็นตัวทด ซึ่งตัวทดควรจะ Load ออกมาแบบ Connection Only จริงมั้ยครับ? ทีนี้ถ้าเราต้องมานั่งกด Close & Load to……
-
คัมภีร์สรุป M Code ใน Power Query ตอนที่ 4: เทคนิค M Code ที่ใช้บ่อย
บทความนี้จะเป็นการรวมเทคนิค M Code แบบประยุกต์กับการใช้งานจริงที่เราต้องพบเจอบ่อยๆ มาไว้ด้วยกัน จะได้เป็นแหล่งอ้างอิงให้เพื่อนๆ เวลาทำงานด้วยนะ ซึ่งจะเข้าใจการทำงานมันได้อย่าลืมไปอ่านตอนที่แล้วซึ่งเป็นการสรุปวิธีทำงานของฟังก์ชันเกี่ยวกับ List ซะก่อนล่ะ เทคนิค M Code #1: อยากเก็บ Text ไว้เฉพาะอักขระที่กำหนด วิธีนึงที่ใช้ได้ดีคือใช้ฟังก์ชัน Text.Select มาช่วย ซึ่งมีวิธีการใช้งานดังนี้ text คือ ข้อความ Original selectChars สามารถใส่เป็น text ธรรมดาได้ แต่ปกติเราจะใส่เป็น List ที่มีอักขระที่อยากจะเอาไว้ซะมากกว่า ยกตัวอย่างเช่น ข้อความของผมเป็นรายชื่อหนังดังนี้ แล้วผมใช้ Text.Select ให้เก็บเฉพาะตัว “a” ไว้ ผมสามารถกด Add Custom Column แล้วใส่สูตรดังนี้ อย่าลืมว่าทุกอย่างใน Power Query เป็น Case-Sensitive ทั้งหมด…
-
คัมภีร์สรุป M Code ใน Power Query ตอนที่ 3: เจาะลึกเรื่องของ List
ในตอนนี้เราจะมาเรียนรู้เรื่องเกี่ยวกับข้อมูลประเภท List กันให้ลึกซึ้งมากขึ้นครับ ซึ่งเดี๋ยวเพื่อนๆ จะได้เห็นว่า พอเราเริ่มเข้าใจการทำงานของฟังก์ชัน List ต่างๆ ในตอนนี้แล้ว ฟังก์ชันกลุ่มอื่นหลายๆ ตัวก็จะมีชื่อคล้ายๆ กันนี่แหละ ทำให้เราจะพอเดาการใช้งานได้ไปด้วย ยิงปืนนัดเดียวได้นกสองตัวเลย! ถ้าเราไปค้นคว้าข้อมูลเอกสารเกี่ยวกับ M Code เองจะพบว่า M Language มันมีฟังก์ชันให้เลือกเยอะมากๆๆ คือเยอะจนเลือกไม่ถูก ผมคิดว่าฟังก์ชันพื้นฐานกลุ่มที่ควรรู้จักให้ดียิ่งขึ้นนั่นก็คือ List เป็นเพราะว่ามันมักถูกใช้เป็นองค์ประกอบสำคัญในฟังก์ชันอื่นๆ ด้วย ดังนั้นใครที่สามารถจัดการเรื่อง List ได้ดี ก็จะพลิกแพลงทำเรื่องต่างๆ ได้เยอะขึ้น List เป็นองค์ประกอบสำคัญในหลายๆ ฟังก์ชัน เวลาเราไปดูฟังก์ชันที่มีความสามารถเจ๋งๆ ทำเรื่องที่เราต้องการทีไร มักจะมี input หรือ output เป็น List ทุกทีสิน่า! เวลาใช้ฟังก์ชัน Text.Split ก็ได้ผลเป็น List เวลาจะรวม Text หลายๆ…
-
คัมภีร์สรุป M Code ใน Power Query ตอนที่ 2 : Function และ each
ในตอนที่แล้วเราได้เรียนรู้พื้นฐานของการใช้ M Code กันไปแล้ว คราวนี้เราจะมาเรียนรู้ถึงสิ่งที่ทรงพลังที่สุดของ M Code นั่นก็คือเรื่องของหลักการของการใช้ Function นั่นเอง เมื่อคุณใช้งาน Power Query ไปเรื่อยๆ จุดที่บ่งบอกว่า คุณกำลังจะก้าวไปสู่ความสามารถอีกขึ้นหนึ่งของ Power Query ก็คือ ความสามารถในการใช้งาน Custom Function นี่แหละครับ ซึ่งหากใช้เป็น คุณจะสามารถแก้ไขปัญหาที่ซับซ้อนได้มากขึ้นกว่าการใช้เครื่องมือมาตรฐานมากมายหลายเท่าเลย Function คือ ขุมพลังที่แท้จริงของ M Code Function (ฟังก์ชัน) คือ สิ่งที่สามารถรับค่า input เข้ามาคำนวณประมวลผล แล้วส่งผลลัพธ์ output ออกมาได้ รูปแบบ เช่น จะสร้างฟังก์ชันของตัวเองขึ้นมา (เรียกว่า Custom Function) เอาไว้หาพื้นที่สามเหลี่ยม การจะเข้าใจเรื่องนี้ได้ดีขึ้น ผมอยากให้นึกถึงฟังก์ชันของ Excel เป็นตัวเปรียบเทียบครับ…
-
คัมภีร์สรุป M Code ใน Power Query ตอนที่ 1
ซีรีส์นี้จะเป็นการเอาเนื้อหา M Code Language ของ Power Query บางส่วนในหนังสือ Excel Power Up และการเรียนรู้เพิ่มเติมของผมหลังจากนั้นมาผสมผสานกัน เนื้อหาจึงมีทั้งส่วนที่เหมือนและแตกต่างกับในหนังสือด้วยนะครับ อีกอย่างในหนังสืออาจ Copy Code ลำบาก ผมเลยเขียนเป็นบทความไว้ให้เลยดีกว่า จะได้ลอง Copy Paste สูตรไปเล่นได้ง่ายๆ ด้วย เอาเป็นว่าใครอยากจะลงลึกเรื่อง M Code ก็อ่านซีรีส์นี้ได้เลย เพราะผมอยากให้มีคนไทยใช้เครื่องมือนี้เป็นเยอะๆ ครับ อีกอย่างมันใช้ได้ทั้งใน Excel และ Power BI เลย จึงมีประโยชน์มากๆ และที่สำคัญ ในซีรีส์นี้ผมจะมาอัปเดทเนื้อหาเพิ่มให้เรื่อยๆ ด้วยนะ ติดตามไปกันได้ยาวๆ เลยครับ M Language คืออะไร? M Language เป็นภาษาในรูปแบบฟังก์ชัน (Functional Language ตรงข้ามกับอีก…
-
Statistics with Excel ตอนที่ 6 : Hypothesis Testing
Hypothesis Testing หรือการทดสอบสมมติฐาน คือกระบวนการที่เราใช้ข้อมูลจาก Sample มาตัดสินเกี่ยวกับ Population โดยจะตัดสินเลือกสมมติฐาน (Hypothesis) ที่มีข้อมูลสนับสนุนว่าน่าจะถูกต้องมากกว่า โดยเราจะตั้งสมมติฐาน 2 อัน คือ… Null Hypothesis vs Alternative Hypothesis ตัวอย่างเช่น บริษัทแห่งหนึ่งผลิตยาพิษซึ่งในขวดควรจะมีปริมาตรเฉลี่ย 250 cc ขึ้นไป ทางลูกค้าที่อยากซื้อยาพิษไปใช้เกิดความสงสัยว่ายาพิษในขวดอาจมีการใส่น้อยเกินไป (กลัวคนกินแล้วไม่ตาย) จึงมีสมมติฐานดังนี้ ที่นี้เราจะรู้ได้ยังไงว่าสมมติฐานอันไหนที่เป็นความจริง? หลักการคิดก็คือ แล้วเราจะดูยังไงว่าหลักฐานจากการทดสอบของเรามีความน่าจะเป็นต่ำ? ค่า p-Value และคำว่า Significant วิธีคิดคือ ถ้าสมมติว่า H0 เป็นจริง ความน่าจะเป็นที่จะพบข้อมูลที่สำรวจได้ในปัจจุบันหรือไปในทิศทางเดียวกับ Ha จะเป็นเท่าไหร่? ซึ่งเราจะเรียกความน่าจะเป็นนี้ว่า p-value จะเห็นว่า P-Value ก็คือความน่าจะเป็นอย่างนึง ดังนั้นค่ามันจะต้องออกมาอย่ระหว่าง 0-1 แน่นอน ซึ่งมันวัดความน่าจะเป็นของสิ่งนี้ เช่น ในตัวอย่างยาพิษ สมมติว่าเราสุ่มแล้วได้ค่าเฉลี่ยยาพิษอยู่ที่ 230 cc…
-
Statistics with Excel ตอนที่ 5 : Central Limit Theorem
ทฤษฎีสุดเจ๋งอย่าง Central Limit Theorem นั้นเป็นสิ่งที่มีประโยชน์มากในการประมาณค่า Parameter ของ Population ได้จากการคำนวณค่า Statistic ของ Sample ครับ ดังนั้นเพื่อไม่ให้เสียเวลา เราไปดูกันเลย! ตัวอย่างเช่น ถ้าผมต้องการทำการสำรวจส่วนสูงของคนในบริษัท แต่ผมไปสำรวจเก็บข้อมูลทุกคนมาไม่ไหว ก็เลยใช้วิธีเลือกสุ่มมาจำนวน 100 คนแล้วหาค่าเฉลี่ยของส่วนสูง ได้ 165 cm Population คือ คนในบริษัททั้งหมด Sample คือ คนในบริษัทที่ผมสุ่มมา 100 คน Parameter = เช่น ค่าเฉลี่ยของส่วนสูงของคนในบริษัททั้งหมด ( µ = ไม่รู้) Statistic = เช่น ค่าเฉลี่ยของส่วนสูงของคนในบริษัทที่ผมสุ่มมา 100 คน = xบาร์ = ได้…
-
Statistics with Excel ตอนที่ 4 : Normal Distribution
ในตอนนี้เราจะเน้นเรื่องของ Normal Distribution ซึ่งเป็น Distribution ประเภท Continuous Probability Distribution ที่พบมากที่สุดในธรรมชาติเลย แต่เราจะขอปูพื้นฐานเกี่ยวกับ Continuous Probability Distribution เบื้องต้นกันก่อนเล็กน้อยก่อนจะไปลงเรื่อง Normal Distribution กันจริงๆ ในครึ่งหลังของบทความ Continuous Probability Distribution คือ การแจกแจงความน่าจะเป็นแบบที่เหตุการณ์ที่สนใจนั้นไม่สามารถนับเป็นชิ้นๆ ได้ เพราะเลขมีค่าต่อเนื่องกัน เช่น การแจกแจงของน้ำหนักของคนในบริษัท เพราะน้ำหนักของแต่ละคนมันอาจเป็น 63.43 kg ที่เป็นเศษแบบนี้ได้ ซึ่งเป็นค่าต่อเนื่อง ทำให้การอ่านความน่าจะเป็นของกราฟที่ Plot ออกมาต้องอ่านจาก “พื้นที่ใต้กราฟ” แทน ซึ่งมีอยู่หลายแบบด้วยกัน เช่น Uniform Distribution, Normal Distribution, T-Distribution, Chi-Square Distribution และอีกมากมาย แต่ผมจะขอพูดถึง 2…
-
Statistics with Excel ตอนที่ 3 : Discrete Probability Distribution
ในตอนที่แล้วเราได้เรียนเกี่ยวกับเรื่องความน่าจะเป็นไปแล้ว ในตอนนี้เราจะมาพูดถึงเรื่องของ Probability Distribution หรือการแจกแจงความน่าจะเป็นกันครับ Probability Distribution เป็นการทำให้เราเห็นภาพรวมถึงค่าของตัวแปรสุ่มที่เป็นไปได้ทั้งหมด และสามารถหาค่าความน่าจะเป็นของเหตุการณ์ที่เราสนใจได้อีกด้วย จึงถือได้ว่ามันเป็นหัวใจสำคัญของเรื่องสถิติเลยล่ะ Probability Distribution สามารถแบ่งออกเป็น 2 ประเภทใหญ่ๆ คือ Discrete Probability Distributions : การแจกแจงความน่าจะเป็นแบบที่เหตุการณ์ที่สนใจนั้นสามารถนับแยกเป็นชิ้นๆ ได้(ไม่ได้มีค่าต่อเนื่องกัน) จึงสามารถ Plot กราฟเป็นแท่งๆ ได้เลย เช่น ใช้แจกแจงความน่าจะเป็นที่ที่ข้อสอบ 10 ข้อแล้วถูกต้อง 0,1,2,3,… 10 ข้อ ซึ่งจะได้กราฟ 10 แท่ง เป็นต้น ดังนั้นเราจะสามารถอ่านความน่าจะเป็นของเหตุการณ์ที่สนใจจากค่าแกน Y ได้เลยง่ายๆ Continuous Probability Distributions : การแจกแจงความน่าจะเป็นแบบที่เหตุการณ์ที่สนใจนั้นไม่สามารถนับเป็นชิ้นๆ ได้ เพราะเลขมีค่าต่อเนื่องกัน เช่น การแจกแจงของน้ำหนักของคนในบริษัท น้ำหนักมันอาจเป็น…
🧑🏻💻อบรมกับเทพเอ็กเซล
📂หมวดหมู่
- Highlights : บทความแนะนำ (94)
- Excel (274)
- Excel ทั่วไป (165)
- Pivot Table (15)
- Excel Chart & Visualization (15)
- Excel Advanced Formula (42)
- Excel VBA (16)
- Python in Excel (7)
- Solver & Simulation (4)
- Excel for Business (16)
- Statistics and Maths (11)
- Excel and Game (6)
- Power Query (72)
- Power BI (15)
- AI & Automation (39)
- Automation (9)
- AI Image & Video (23)
- AI Chatbot (6)
- Programming (8)
- Personal (7)
- Resources (18)
- Uncategorized (2)
🏷️Tags
actual address aggregate ai api chart combination data entry dynamic array Excel Python frequency function query header iterative let machine learning map marketing math matplotlib matrix m code MDETERM mode parameter percentile pi probability quartile regex rfm segmentation simulation small statistics sumifs sumproduct switch target textjoin transpose unique web scraping xirr xnpv