Day: February 6, 2023

  • วิธีใช้ Power Query ดึงข้อมูลจาก PSU Web API

    benjawan.n

    PSU Web API เป็นเว็บที่ให้บริการข้อมูลในรูปแบบ JSON ทุกท่านที่มี PSU Passport สามารถสมัครเพื่อขอใช้บริการได้ที่เว็บไซต์ api.psu.ac.th โดยปัจจุบันข้อมูลที่เปิดให้บริการแล้วคือ ข้อมูลส่วนกลาง ข้อมูลระบบสารสนเทศนักศึกษา และข้อมูลระบบสารสนเทศบุคลากร สำหรับทุกท่านที่สนใจใช้บริการข้อมูลสามารถศึกษาวิธีการได้จากคู่มือของระบบค่ะ

    วันนี้จะขอนำเสนอวิธีการดึงข้อมูลจาก PSU Web API ที่ให้บริการออกมาเป็นรูปแบบตารางด้วย Power Query ใน Microsoft Excel 365 กันค่ะ ซึ่งการดึงข้อมูลด้วย Power Query นี้น่าจะเหมาะสำหรับคนที่ไม่ถนัดในการเขียนโปรแกรมเพื่อดึงข้อมูลมาใช้งานกันค่ะ

    STEP 1 : เชื่อมต่อกับแหล่งข้อมูล PSU Web API

    • คลิกเมนู Data –> Get Data –> From Other Sources –> From Web
    • ตัวอย่าง แสดงการดึงข้อมูลชื่อปริญญาที่ให้บริการจากระบบ PSU Web API
    • ป้อน URL ตามรูปแบบที่กำหนด ศึกษาได้จากคู่มือการใช้งานของระบบ (ตามตัวอย่างให้แสดงข้อมูล 100 รายการ)
    • คลิกปุ่ม OK

    STEP 2 : ตั้งค่า Power Query Editor

    • ระบบแสดงผลลัพธ์ข้อมูล 2 ส่วน คือส่วน paging ลักษณะข้อมูลเป็น Record และส่วนของ data ซึ่งจะมีลักษณะข้อมูลเป็น List ซึ่งภายใน List จะมี Record ซ้อนอยู่ข้างในอีกที
    • ตั้งชื่อ Query Name : Degree
    • คลิก Convert Into Table เพื่อแปลงผลลัพธ์ 2 ส่วนออกมาเป็นตาราง

    STEP 3 : ทำการ Transpose และแตก List ข้อมูล

    • คลิกเมนู Transpose –> Reverse Rows เพื่อสลับให้ paging เป็น First Row
    • คลิก Use First Row as Header เพื่อเตรียมแตก List ในคอลัมน์ data ออกมา
    • คลิกปุ่ม Expand ที่หัวตารางของคอลัมน์ Column2 แล้วคลิก Expand to New Rows เราก็จะได้ Record ออกมาในแต่ละบรรทัด
    • คลิกปุ่ม Expand ที่หัวตารางของคอลัมน์ Column2 อีกครั้ง
    • สามารถคลิกเลือกหรือไม่เลือกคอลัมน์ที่ต้องการหรือไม่ต้องการได้
    • คลิกไม่เลือก Use original column name as prefix
    • คลิกปุ่ม OK ระบบแสดงข้อมูลจำนวน 100 รายการตามที่กำหนดจาก PSU Web API
    • คลิกเมนู Home –> Close & Load เพื่อเสร็จสิ้น

    STEP 4 : Power Query ดึงข้อมูลจาก PSU Web API มาแสดงเป็นตารางเรียบร้อยแล้วค่ะ

    ^_^ หวังว่าโพสนี้จะเป็นประโยชน์ต่อผู้อ่านไม่มากก็น้อยนะคะ แล้วพบกันใหม่ค่ะ

  • วิธีรวมข้อมูล Excel จากหลายชีทเข้าด้วยกันเป็นตารางเดียวด้วย Power Query

    benjawan.n

    Power Query เครื่องมือสำคัญของ Power BI และ Microsoft Excel 2016 | 2019 | 365 ที่จะช่วยจัดการข้อมูลให้ได้ตามต้องการ ไม่ว่าเป็นการรวบรวมข้อมูลจากหลายแหล่งเข้าด้วยกัน การเปลี่ยนประเภทข้อมูล การจัดตารางหรือคิวรี การจัดคอลัมน์ การจัดการข้อมูลที่บกพร่อง การรวมข้อมูล การแยกข้อมูล การปรับแต่งข้อมูล และการสร้างรูปแบบข้อมูล

    จุดเด่นของ Power Query

    • รวบรวมข้อมูลได้จากหลายแหล่ง หลากหลายรูปแบบ
    • ใช้งานง่าย มีปุ่มเมนูให้ใช้งาน
    • ทุกการกระทำจะถูกบันทึกไว้เป็น Steps สามารถแก้ไข สลับลำดับ หรือลบ Steps ที่ทำไว้ได้อย่างง่ายดาย
    • รองรับการทำงานที่ Advance มากขึ้นด้วย M Code ซึ่งเป็นภาษาพิเศษของ Power Query

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

    STEP 1 : ทำการแปลงข้อมูลในแต่ละชีทให้เป็นตาราง

    • เนื่องจาก Power Query จะทำงานกับข้อมูลในรูปแบบที่เป็นตาราง ดังนั้นต้องดำเนินการแปลงข้อมูลแต่ละชีทให้เป็นตาราง
    • ภายในชีท HATYAI คลิกเซลล์ A1
    • คลิกเมนู Insert –> Table
    • ระบบเลือกช่วงข้อมูลที่ต้องการให้อัตโนมัติ
    • เลือก My table has headers
    • คลิกปุ่ม OK
    • กำหนด Table Name : T_HATYAI
    • ดำเนินการเช่นเดียวกันในชีทที่เหลือโดยตั้งชื่อตาราง T_PATTANI, T_PHUKET, T_SURAT, T_TRANG ตามลำดับ

    STEP 2 : รวมข้อมูลเป็นตารางเดียวด้วย Power Query

    • คลิกเมนู Data –> Get Data –> From Other Sources –>Blank Query
    • ระบบแสดงหน้าต่าง Power Query Editor

    STEP 3 : กำหนดรายละเอียดของ Query

    • ตั้งชื่อ Query Name : ALL_CAMPUS
    • พิมพ์ฟังก์ชัน “=Excel.CurrentWorkbook()” ซึ่งเป็นภาษา M ที่อยู่เบื้องหลังของ Power Query เพื่อแสดงตารางข้อมูลทั้งหมดในไฟล์ Excel ของเรา
    • เราสามารถ Filter เพื่อเลือกรวมเฉพาะตารางข้อมูลที่ต้องการเท่านั้นได้
    • สมมุติเราต้องการรวมเฉพาะตารางเฉพาะที่ขึ้นต้นด้วยตัวอักษร ‘T’ เราสามารถทำได้โดยคลิกที่ Text Filters –> Begins With….
    • กรอกตัวอักษร T ลงไป คลิกปุ่ม OK

    STEP 4 : กำหนดรายละเอียดของคอลัมน์

    • สามารถเลือกหรือไม่เลือกคอลัมน์ที่ต้องการหรือไม่ต้องการได้
    • กำหนดเงื่อนไขไม่เลือก Use original column name as prefix กรณีเลือกจะมีชื่อ Content นำหน้าชื่อคอลัมน์เช่น Content.Subject ID Content.Subject Code เป็นต้น
    • คลิกปุ่ม OK ระบบแสดงข้อมูลที่เกิดจากการรวมของตารางข้อมูลตามเงื่อนไข
    • คลิกปุ่ม Close & Load ระบบจะทำการรวมข้อมูลทั้ง 5 ชีทไว้ที่ชีท ALL_CAMPUS

    STEP 4 : Power Query รวมข้อมูล Excel จากหลายชีทเข้าด้วยกันเป็นตารางเดียวเรียบร้อยแล้วค่ะ

    ^_^ หวังว่าโพสนี้จะเป็นประโยชน์ต่อผู้อ่านไม่มากก็น้อยนะคะ แล้วพบกันใหม่ค่ะ

  • วิธีการแยกคำนำหน้าชื่อ ชื่อและนามสกุล ที่รวมอยู่ในช่องเดียวกันใน Microsoft Excel

    benjawan.n

    ในการใช้งาน Microsoft Excel เราคงจะเคยเจอปัญหาที่ข้อมูลมีทั้งคำนำหน้าชื่อ ชื่อและนามสกุล รวมอยู่ในช่องเดียวกัน แต่การนำไปใช้งานของเราต้องการที่จะแยกคำนำหน้าชื่อ ชื่อและนามสกุลออกจากกันเป็นคนละช่อง

    วันนี้จะขอนำเสนอสูตรเพื่อแก้ปัญหาดังกล่าวข้างต้น มาดูกันเลยคะว่าทำยังไง ในการแก้ปัญหานี้จะต้องใช้ฟังก์ชันหลายฟังก์ชัน รวมทั้งเทคนิคการคำนวณแบบ Array ของ Excel ผสมผสานกันโดยเริ่มจาก

    STEP 1 : สร้างคอลัมน์สำหรับการแสดงผล และสร้าง List รายการคำนำหน้าชื่อที่คอลัมน์ I

    STEP 2 : ทำการแปลง TITLE_LIST เป็นตาราง เพื่อความสะดวกในการจัดการข้อมูลคำนำหน้าชื่อและการอ้างอิงข้อมูล

    • คลิกเซลล์ I1
    • คลิกเมนู Insert –> Table
    • ระบบเลือกช่วงข้อมูลที่ต้องการให้อัตโนมัติ
    • เลือก My table has headers
    • คลิกปุ่ม OK
    • กำหนด Table Name : TITLE

    STEP 3 : ทำการแยกเฉพาะคำนำหน้าชื่อมาแสดงที่คอลัมน์ D ด้วยฟังก์ชัน SEARCH , MATCH และ INDEX

    =INDEX(TITLE[TITLE_LIST],MATCH(1,SEARCH(TITLE[TITLE_LIST],B2),0))

    ฟังก์ชัน SEARCH : ค้นหาสตริงข้อความหนึ่งภายในสตริงข้อความที่สอง และส่งกลับตัวเลขที่เป็นตำแหน่งเริ่มต้นของสตริงข้อความแรกจากอักขระแรกของสตริงข้อความที่สอง

    ไวยากรณ์ : SEARCH(find_text,within_text,[start_num])

    กรณี

    • ถ้าไม่พบค่าของ find_text จะส่งกลับ #VALUE! เป็นค่าความผิดพลาด
    • กรณีพบค่าของ find_text ส่งกับตัวเลขที่เป็นตำแหน่งเริ่มต้นของสตริงข้อความแรกจากอักขระแรกของสตริงข้อความที่สอง
      • กรณี find_text เป็นช่วงข้อมูล จะส่งค่ากลับเป็นเลข 1

    ฟังก์ชัน MATCH : ค้นหารายการที่ระบุช่วงในเซลล์ แล้วส่งกลับตําแหน่งสัมพันธ์ของรายการนั้นในช่วง ตัวอย่างเช่น ถ้าช่วง A1:A3 มีค่า 5, 25 และ 38 สูตร =MATCH(25,A1:A3,0) จะส่งกลับตัวเลข 2 เนื่องจาก 25 เป็นรายการที่สองในช่วง

    ไวยากรณ์ : MATCH(lookup_value, lookup_array, [match_type])

    ฟังก์ชัน INDEX : ส่งกลับค่าจากภายในตารางหรือช่วงข้อมูล

    ไวยากรณ์ : INDEX(array, row_num, [column_num])

    STEP 4 : ทำการแยกชื่อและนามสกุลออกจากคำนำหน้าชื่อมาแสดงที่คอลัมน์ C ด้วยฟังก์ชัน SUBSTITUTE

    =SUBSTITUTE(B2,D2,””)

    ฟังก์ชัน SUBSTITUTE : ใช้แทนที่ข้อความเดิม old text ด้วยข้อความใหม่ new text ส่วน instance_num ใช้เพื่อระบุตำแหน่งการแทนที่ ซึ่งถ้าไม่ระบุ old text จะถูกเปลี่ยนด้วย new text ทั้งหมด

    ไวยากรณ์ : SUBSTITUTE(text, old_text, new_text, [instance_num])

    STEP 5 : ทำการแยกชื่อมาแสดงที่คอลัมน์ E ด้วยฟังก์ชัน LEFT และ SEARCH

    =LEFT(C2,SEARCH(” “,C2,1)-1)

    ฟังก์ชัน LEFT : ส่งกลับอักขระจำนวนหนึ่งที่อยู่ในลำดับแรกๆ ในสตริงข้อความ ตามจำนวนอักขระที่ระบุ

    ไวยากรณ์ : LEFT(text, [num_chars])

    STEP 6 : ทำการแยกนามสกุลมาแสดงที่คอลัมน์ F ด้วยฟังก์ชัน RIGHT , LEN และ SEARCH

    =RIGHT(C2,LEN(C2)-SEARCH(” “,C2,1))

    ฟังก์ชัน RIGHT : ส่งกลับอักขระสุดท้ายจำนวนหนึ่งในสตริงข้อความ ตามจำนวนอักขระที่ระบุ

    ไวยากรณ์ : RIGHT(text,[num_chars])

    ฟังก์ชัน LEN :  ส่งกลับเป็นจำนวนอักขระในสตริง

    ไวยากรณ์ : LEN(text)

    STEP 7 : แยกคำนำหน้าชื่อ ชื่อและนามสกุล ที่รวมอยู่ในช่องเดียวกันใน Microsoft Excel เรียบร้อยแล้วค่ะ

    ^_^ หวังว่าโพสนี้จะเป็นประโยชน์ต่อผู้อ่านไม่มากก็น้อยนะคะ แล้วพบกันใหม่ค่ะ