Excel

Excel Xloopup

by

Function HIT Excel มาต่อกันกับ Xlookup

ต่อกันจาก post Excel Vlookup – Level 2 กับทริคในการดัดแปลข้อมูลเพื่อให้ใช้สูตร Vlookup ได้

ถ้าคุณได้ใช้ Vlookup ไปสักพักจะรู้สึกว่า Vlookup ยังมีข้อจำกัดในการค้นหาข้อมูลเพื่อส่งค่ากลับมาที่รายการที่เราต้องการ คือ Vlookup สามารถค้นหาข้อมูลจากด้านขวาได้เท่านั้น แต่ไม่สามารถค้นหาข้อมูล หรือส่งกลับข้อมูลที่อยู่ทางด้านซ้ายได้


Microsoft Excel ได้พัฒนาฟังก์ชั่น Xloopup ที่สามารถค้นหาข้อมูลจากคอลัมน์ช้ายหรือขวาก็ได้

อธิบายสูตร

=XLOOKUP (หาอะไร,  จากไหน,  ผลลัพธ์, หากไม่พบให้โชว์อะไร, ต้องเหมือนกันเป๊ะ, ลำดับการค้นหา)

หาอะไร = C5 : C9
จากไหน = H5 : H15
ผลลัพธ์ = G5 : G9
หากไม่พบจะให้โชว์อะไร = ไม่พบ
เลข 0 = ต้องเหมือนกันเป๊ะ 100%
———ไม่ต้องใส่ก็ได้ ————
ลำดับการค้นหา
1 = จากรายกาแรก
-1 = จากรายการสุดท้าย

เราจะเห็นได้ว่า Xlookup มีความยึดหยุ่นกว่า Vlookup ซึ่ง Xlookup จะมีอยู่ใน excel Version 2021 และ Microsoft 365 สำหรับ excel Version ที่ต่ำกว่าก็สามารถใช้สูตร Match และ Index เพื่อดึงข้อมูลจากด้านขวาได้เช่นกัน

Excel Vlookup – level 2

by

Function HIT มาต่อกันกับ Vlookup Level 2

มาต่อจาก post ที่แล้ว Excel Vlookup – Level 1 ที่สอนการใช้ Vlookup เบื้องต้น สำหรับ Post นี้ จะเป็นทริคที่ใช้กันบ่อยๆ เพื่อแก้ปัญหาเวลาใช้สูตร Vlookup มีจุดสำคัญของการใช้สูตรประเภท lookup อยู่ที่ รายการที่ต้องการค้นหา จะต้องมีรูปแบบเหมือนกัน กับแหล่งข้อมูลที่จะไปหาแบบเป๊ะๆ 100% สูตร Vlookup จึงจะทำงาน แต่บางครั้ง ข้อมูลจาก 2 ที่ก็มีรูปแบบที่แตกต่างกัน

โดยโจทย์ เราต้องการนำรหัสการนำเข้าผลไม้ของประเทศต่างๆ จากตารางข้อมูล มาใส่ในตารางรายการของเรา ซึ่งในตารางข้อมูล ชื่อของผลไม้และชื่อประเทศ นั้นอยู่รวมในช่องเดียวกันและมีเครื่องหมายขีดคั่นตรงกลาง แต่ตารางรายการที่เราจะใส่ข้อมูล ชื่อผลไม้ แยกกันอยู่คนละช่อง

อธิบายสูตร

VLOOKUP (หาอะไร = โดยรวมคอลัมน์ C5:C9 กับ D5:D9, จากไหน = G5:H15, ผลลัพธ์ที่ต้องการ = คอลัมน์ที่ 2, เงื่อนไขในการเปรียบเทียบ = 0)
  1. C5 : C9 คือ คอลัมน์ ชื่อผลไม้
  2. &” – “& คือ เครื่องหมายเชื่อม คอลัมน์ ผลไม้ กับ ประเทศ
  3. D5 :D9 คือ คอลัมน์ ชื่อประเทศ
  4. G5 : H15 คือ คอลัมน์ จากตารางที่มีข้อมูลที่เราต้องการ
  5. เลข 2 คือ คอลัมน์ที่ 2 จากตารางที่มีข้อมูลที่เราต้องการ
    * ตัวสุดท้ายใส่เลข 0 = เปรียบเทีบบความถูกต้อง 100%

จุดสำคัญ

  1. Excel Version ที่ต่ำกว่า Office 2021 ควรใส่เครื่องหมาย $ เพื่อล็อค cell ไม่ให้เลื่อนออกเวลาใช้สูตร สำหรับ Office 2021 และ Office365 ขึ้นไปไม่ต้องใส่ $ ก็ได้
  2. การใช้เครื่องหมาย & (แอนด์) เป็นการเชื่อม Cell 2 cell ให้ต่อกัน แต่ถ้าระหว่าง 2 cell มีเครื่องหมาย หรือ อักษรคั่น ให้ใส่เครื่องหมายคำพูด ” ” หรือ Duble code แทรก กลางระหว่าง 2 cell และเครื่องหมาย & ที่หัวและท้ายเครื่องหมายคำพูด จึงจะสามารถพิมพ์ข้อความที่คั่นกลางได้

ใน Post ต่อไปเราจะมาลองใช้ xloopup ฟังก์ชั่นการค้นหาข้อมูลที่ประสิทธิ์ภาพสูงกว่า Vloopup และ hloopup

Excel PivotTable

by

Function HIT PivotTable

วิเคราะห์ข้อมูลอย่างรวดเร็วด้วย Pivotable เพียง 3 ขั้นตอน

บทความนี้ขอแนะนำการใช้ PivotTable สำหรับการสร้างตรางวิเคราะห์ข้อมูล ที่ง่ายและเร็วที่สุดในกลุ่ม software การสร้าง Report ด้วยความง่ายและสามารถใช้ได้ทั้ง Excel และ Google Sheet

ก่อนที่เราจะไปสร้าง PivotTable เรามาดูวิธีการเตรียมข้อมูลกันก่อนโดยมีหลัก 2-3 ข้อ จากตัวอย่าง เป็นข้อมูลที่เกี่ยวกับ จำนวนนักเรียน แบ่งตามชั้นการศึกษา อายุ และโรงเรียน

*ขอบคุณข้อมูลจาก https://data.go.th/ ศูนย์กลางข้อมูลเปิดภาครัฐ ( Open Government Data Download file ตัวอย่าง )

1. ข้อมูลที่จะนำมา Pivot ไม่ควรมีช่องว่างในตรางข้อมูล เพื่อการแสดงผลรวมได้อย่างถูกต้อง
2. Format ตัวเลข ตัวอักษร เช่น คอลัมน์ “Total” ถ้า Fomat เป็นตัวอักษร เมื่อ Pivot แล้วจะไม่สามารถแสดงผลรวมของตัวเลขช่อง “Total” ได้
3. ค่า ในตราง ควรสั้นกระชับและมีความหมายเดียว เช่น คอลัมน์ “Age” ไม่จำเป็นต้องมีคำว่า “ปี”

การสร้าง PivotTable 3 ขั้นตอน


1. เปลี่ยนรูปแบบ cell ให้เป็นแบบ Table ก่อน
1. ลากเมาส์คลุมข้อมูลที่ต้องการ Pivot
2. เลือกเมนู Insert > Table
3. OK
4. ตรางจะเปลี่ยนเป็นสีและได้ชื่อ “Table1”


2. สร้างตราง PivotTable
1. คลิ๊กที่ตราง 1 ครั้ง
2. เลือกเมนู Insert
3. เลือกเมนู PivotTable
4. ติ๊กที่ “Add this data to the Data Model”
5. OK

เราจะได้ layout PivotTable จากนั้นมาดูรูปตามลำดับตัวเลขกัน

*รูปตัวอย่าง จะสลับ ซ้าย-ขวา กับหน้าจอโปรแกรม excel เพื่อแสดงให้เห็นถึงที่มาของ Fild ต่างๆ ได้ง่ายขึ้น

3. ออกแบบตราง PivotTable (ลากวางตามรูปตัวอย่าง)
1. SchoolName กับ YearTerm วางช่อง Filters
2. Age วางช่อง Columns
3. Level วางช่อง Row
4. Total วางช่อง Values

การปรับแต่งตราง

– เราสามารถสลับค่าต่างๆ ในช่อง Filters, Columns, Row, Values ได้ การลบค่าที่ใส่ไปแล้วให้คลิ๊กค้างแล้วลากออก เคอเซอร์จะขึ้นรูปกากบาท แล้วปล่อยเมาส์
– ช่อง Value สามารถเปลี่ยนรูปแบบการแสดงผลเป็น SUM, Count, Average และอื่นๆ ได้ โดยคลิ๊กรูปสามเหลี่ยม เลือก Value Filed Settings
– เมื่อ Update ข้อมูลในตรางฐานข้อมูล ใน PivotTable ก็จะ update ทันที

สำหรับเพื่อนๆ ที่สงสัยหรืออยากให้แนะนำ Funtion อะไรไหนที่ต้องใช้งานกันบ่อย
สามารถ Comment ไว้ที่ใต้บทความนี้นะครับ Ad จะพยายามตอบให้ทุกคำถามน้า

Excel Vlookup – level 1

by

Function HIT วันนี้แนะนำ VLOOKUP

ฟังก์ชั่นยอดนิยม สำหรับค้นหาข้อมูลจำนวนมากได้ในครั้งเดียว VLOOKUP สามารถใช้ได้บน excel และ Google sheet ใช้งานง่ายเพียง 3 ขั้นตอน

3 สิ่งที่ต้องเตรียม

1. รายการที่ต้องการใส่ข้อมูล
2. แหล่งข้อมูล ที่จะไปหา
3. สูตร Vlookup


อธิบายสูตร VLOOKUP ตามลำดับหมายเลขในวงเล็บ

VLOOKUP (หาอะไร = C5:C9 ,จากไหน = G5:G15 ,ผลลัพธ์ที่ต้องการ = คอลัมน์ที่ 2, เงื่อนไขการเปรียบเทียบ = 0)

1. C5 : C9 = ตารางที่ต้องการหา (ใส่ $ เพื่อ Lock Cell ไม่ให้เลือน)
2. G5 : G15 = ตารางที่ค้นหาข้อมูล
3. เลข 2 = คือ ลำดับคอลัมน์ในตารางข้อมูล
* เลข 0 ใส่แทนค่าในสูตรเพื่อเปรียบเทียบค่าค้นหาต้องตรงกัน 100%

จุดสำคัญการใช้ Vlookup

1. คอลัมน์แรก ในตารางรายการ ต้องเป็นข้อมูลประเภทเดียวกันกับ ตารางข้อมูล
2. การใส่ $ เพื่อป้องกันไม่ให้สูตรเลื่อน ใช้กับ Excel Version 2019 – 2007

Vlookup สามารถประยุกต์ใช้ค้นหาข้อมูล ได้หลายรูปแบบ ติดตามได้ที่ Excel Vloopup – Level 2
สำหรับเพื่อนๆ ที่สงสัยหรืออยากให้แนะนำ Function อะไร Comment กันเข้ามาได้