excel_blog




Showing posts 1 - 88 of 88. View more »

VLOOKUP ให้เจอตัวที่ 2 เขียนสูตรยังไง

posted Apr 1, 2021, 5:37 AM by Dusit Kob   [ updated Apr 1, 2021, 5:38 AM ]

 VLOOKUP ให้เจอตัวที่ 2 เขียนสูตรยังไง


ปกติ ถ้าใช้ VLOOKUP 

และเจอ ข้อมูล ที่ต้องการ แต่มีมากกว่า 1 รายการ

VLOOKUP จะดึงข้อมูล ของตัวแรก มาแสดง 

โดยเรียงจากบนลงล่าง

แต่ถ้าเราต้องการ VLOOKUP ให้ดึง ตัวที่ 2 ที่เจอ มาแสดง 

จะต้องเขียนสูตรอีกแบบนึง

โดย Function ที่ใช้ช่วย มี


ROW - ได้เลขตำแหน่ง ของ บรรทัด ที่ผูกสูตร

IF - ใช้เลือกแสดงผล

SMALL - เรียงข้อมูล

INDEX - ใช้ดึงข้อมูลออกมา


จากตัวอย่าง ถ้าต้องการดึงข้อมูล ยอดขาย ของพริก รายการที่ 2 ขึ้นมาแสดง






ขั้นตอนที่ 1
ต้องเช็ค ตำแหน่ง ของ "พริก" ทุกรายการ ว่าอยู่บรรทัดไหนบ้าง
โดยใช้ IF + ROW

คลุม G4 ถึง G17 กดปุ่ม F2
พิมพ์สูตร 
เสร็จแล้ว กด Ctrl + Shift + Enter 
(เพราะว่าเป็น สูตรแบบ Array )




ผลลัพธ์ที่ได้ จะแสดง เลข บรรทัด ของ คำว่า "พริก" ในตารางข้อมูล



ขั้นตอนที่ 2
ต้องเช็คว่า ตัวที่ 2 อยู่ บรรทัดที่เท่าไร ใน ตารางข้อมูล
โดยใช้ SMALL 
( หา ตัว ที่น้อย ที่สุด อันดับที่ 2 )
( ซึ่ง ก็คือ ตำแหน่ง ของตัวที่ 2 )



ขั้นตอนที่ 3

ดึงข้อมูลจาก ตำแหน่งที่ 2 ว่าอยู่บรรทัดไหน ที่ได้ จาก SMALL
ด้วย INDEX




ซึ่ง จริงๆ แล้ว เราสามารถผูกสูตร ยำ รวมกันเป็นก้อนเดียวไม่ต้อง ทด แบบด้านบนได้เลย ตามนี้
( ต้องกด Ctrl + Shift + Enter ด้วย สำหรับ Excel ที่เก่ากว่า 365 )





ลอง Download Files ไปเล่นดูได้จาก ด้านล่างครับ


สูตร Excel เช็คทศนิยมว่ามีกี่หลัก

posted Mar 23, 2021, 5:29 AM by Dusit Kob   [ updated Mar 23, 2021, 5:29 AM ]


งานบางประเภท อาจจะต้องเช็ค จำนวนทศนิยม ของข้อมูล ว่ามีกี่หลัก

โดยเราสามารถ สร้างสูตรได้ 

โดย Function ที่ใช้ คือ

LEN -> ใช้นับจำนวนตัวอักษร

FIND -> หาตำแหน่งของตัวอักษร ว่าอยู่ตำแหน่งที่เท่าไร ในข้อความ

โดย จากสูตรในรูป 


LEN(B2) -> นับจำนวนตัวอักษรทั้งหมด ใน B2 ว่ามีกี่ตัว ซึ่งจากรูป จะได้ 6

FIND(".",B2,1) -> ค้นหา "." ใน B2 ว่าอยู่ตำแหน่งที่เท่าไร โดยเริ่มค้นจากตัวที่ 1

ดังนั้น พอ เรารู้ว่า "." อยู่ตัวที่เท่าไร 

เอาจำนวนตัวอักษรทั้งหมดตั้ง - ด้วยตำแหน่งของ "." 

ผลลัพธ์ที่ได้ก็คือ จำนวนทศนิยม ว่ามีกี่หลัก


ดึงราคา บิตคอยน์ ลง Excel

posted Jan 9, 2021, 5:55 AM by Dusit Kob   [ updated Jan 9, 2021, 5:55 AM ]



Excel สามารถดึงราคา Bitcoin ลงมาแบบ Real Time ได้เลย

มีขั้นตอนตามนี้

1.พิมพ์ ชื่อ สัญลักษณ์ ที่ใช้ในการ Trade ตามด้านล่าง



2.สร้าง Table ในตารางข้อมูล ที่พิมพ์เข้าไป 

โดยคลุม ข้อมูล 

ไปที่เมนู Excel -> Insert - > Table



3.คลุม Table ที่สร้าง  ไปที่ เมนู Data - > Stocks





4. Excel จะเปลี่ยน ตารางข้อมูล ธรรมดา เป็น Stock Data

โดยสังเกต ที่มุมขวาบน จะมี สัญลักษณ์ เครื่องหมาย + เล็ก

พอคลิก ก็จะให้เลือกข้อมูลที่มาแสดง ผล

เช่น เลือก Price



5.จะได้ราคาของ Bitcoin มาทันที

ถ้าต้องการ update ไปที่ Data -> คลิก Refresh All



Excel Template สวยๆ

posted Aug 22, 2020, 8:56 PM by Dusit Kob   [ updated Aug 22, 2020, 8:58 PM ]

 Excel Template สวยๆ 



จริง ๆ แล้ว ใน Excel เอง มี Templates สวยๆ  ที่มีประโยชน์ 

เอาไปใช้ได้เลย แก้นิดหน่อย ให้  download ไปได้เลย 

โดยเฉพาะ Template ทางด้าน Finance , Business , Project Tracking

วิธีการเลือก Template สำเร็จรูป 

1. ไปที่ File (ด้านซ้ายบน เมนู Excel )
 



2.ไปที่ New 

ปกติ เราจะเลือก Blank Workbook เลย

ให้สังเกต ด้านล่าง จะมี Templates ให้เลือกตามหัวข้อ เช่น ลองเลือก Business




3.จะมี Templates สวยๆ และมี สูตร สำเร็จเรียบร้อยแล้ว ให้ download ไปใช้ได้เลย




ดึงราคาทองลง Excel พร้อม Update ทุกๆ นาที

posted Jul 30, 2020, 6:15 PM by Dusit Kob   [ updated Jul 30, 2020, 6:16 PM ]

 ดึงราคาทองลอง Excel พร้อม Update ทุกๆ นาที



ใน Excel  เราสามารถ ดึง ข้อมูลจาก Web เช่น ราคา ทอง  เข้ามาได้เลย

และสั่งให้ Update ทุกๆ  1 นาทีได้เลย

มีขึ้นตอน ตามนี้

1.ไปที่เมนู Data -> ซ้ายสุด -> จะมีเมนูเล็กๆ ชื่อ From Web ( อยู่ใน Group ของ Get & Transform )



2.ใส่ชื่อ Web Page โดยเป็นหน้าที่ต้องการจะถึงข้อมูล  และ คลิก ok

จากตัวอย่างเป็น หน้าของ สมาคมค้าทองทำ




3.จะมี รูปแบบ ตารางจาก หน้า Web ที่ต้องการจะดึง

เลือก ตารางที่ต้องการจะดึง Data ( ตารางที่แสดงราคา )

แล้ว คลิก Load



4.Excel จะดึงตาราง ราคา มาลงใน Worksheet ทันที



5.สั่งให้มีการ Refresh ทุกๆ  1 นาที 

ไปที่ เมนู Data - > Refresh All - > Connection Properties



6.ปรับ เวลา Refresh every ให้เเป็น 1 minutes




ตัดตัวซ้ำใน Excel ด้วยสูตรใหม่ UNIQUE

posted Jul 24, 2020, 9:28 PM by Dusit Kob   [ updated Jul 24, 2020, 9:29 PM ]

 ตัดตัวซ้ำใน Excel ด้วยสูตรใหม่ UNIQUE



ช่วงนี้ ( July 2020 ) ใครใช้ Office 365

จะเริ่มมีการ ทยอย update Functions ใหม่ๆ ใน Excel ให้ เป็นมาตรฐาน แล้ว

เช่น XLOOKUP , UNIQUE , SORTFILTER , SORTBY

ซึ่ง ลด การผูกสูตรที่่วุ่นวายไปเยอะมากๆ 


บทความนี้ จะเป็นวิธีการใช้ Function UNIQUE 

ใช้ง่ายมากๆ และ มีโอกาสใช้เยอะมากๆ 

จากตารางด้านล่าง มี Product Group ไล่ทีละรายการ

ถ้าเราต้องการ ถอด ข้อมูล Product Group ออกมา อีกหนึ่ง Column 

ที่ไม่ซ้ำ กัน สมัย ก่อน ต้องใช้ Remove Duplicates

เราสามารถใช้ Function UNIQUE ได้เลย



วิธีการใช้ง่ายๆ  ตามรูป

พิมพ์  =UNIQUE(ลาก column ที่จะตัดตัวซ้ำ )



ผลลัพธ์ที่ได้ จะ ดึงออก มา เฉพาะ รายการ UNIQUE เท่านั้น







เรียงข้อมูลใน Excel ด้วยสูตรใหม่ SORT Function

posted Jul 24, 2020, 9:10 PM by Dusit Kob   [ updated Jul 24, 2020, 9:12 PM ]

 เรียงข้อมูลใน Excel ด้วยสูตรใหม่ SORT Function



ช่วงนี้ ( July 2020 ) ใครใช้ Office 365

จะเริ่มมีการ ทยอย update Functions ใหม่ๆ ใน Excel ให้ เป็นมาตรฐาน แล้ว

เช่น XLOOKUP , UNIQUE , SORT , FILTER , SORTBY

ซึ่ง ลด การผูกสูตรที่่วุ่นวายไปเยอะมากๆ 

บทความนี้จะเป็นการใช้ Function ชื่อ SORT 

วัตถุประสงค์ ตามชื่อ เลย คือ เรียงข้อมูล 


วิธีการใช้แบบ พื้นฐาน ตามรูป เลย




ผลลัพธ์ที่ได้ จะเรียง Data ออกมาให้ โดย Default คือ น้อย -> มาก

ซึ่ง สมัยก่อนถ้าจะสร้างสูตร แบบนี้ วุ่นวายมาก



แต่จริงๆ แล้ว SORT Function สามารถ เลือก column ในการ SORT จากตารางได้ด้วย

รูปแบบ คือ 


=SORT( array , [ sort_index ] , [ sort_order ] , [ by_col ] )


array  -> ก้อนข้อมูล ลากได้เลย

sort_index - > กรณี ลาก เป็น ตาราง ใส่ เลข column ที่ต้องการจะ Sort

sort_order - > ใส่ 1 เรียงจากน้อยไปมาก ( Default ) , ใส่ -1  เรียงจากมากไปน้อย 

by_col -> เลือกว่าจะ sort แนว Row  หรือ Column , ใส่ False คือ แนว Row ( Default ) , ใส่ TRUE คือแนว Column 


ตัวอย่างการลากทั้ง ตาราง



ใส่ 2 - > คือ เลือก Sort  คอลัมน์ที่ 2 ของตาราง คือ รหัส Product

ใส่ -1 -> คือ เรียงจาก มาก แบบ มากไปน้อย

ใส่ FALSE - > คือ เรียงแนวนอน

ผลลัพธ์ที่ได้





วิธีเพิ่ม Power Pivot ใน Excel

posted Jun 15, 2020, 8:14 AM by Dusit Kob   [ updated Jun 15, 2020, 8:25 AM ]

 วิธีเพิ่ม Power Pivot ใน Excel



วิธีการเพิ่ม Power Pivot เข้ามาใช้ใน Excel

1.ไปที่เมนู File 




2.คลิกที่ Options (ด้านล่าง)




3. ไปที่ Add-ins -> ตรงกลางล่างเลือก COM Add-ins-> คลิกที่ Go...




4.ติ๊กเลือก Microsoft  Power Pivot for Excel -> คลิก OK



5.เมนู Power Pivot จะ โผล่ ที่ด้านบน ขวาสุดของ Excel




ดึงข้อมูล เกิน 1 ล้านบรรทัด ทำ PivotTable ใน Excel | Power Pivot

posted Jun 15, 2020, 8:02 AM by Dusit Kob   [ updated Jun 15, 2020, 8:04 AM ]

 วิธีดึงข้อมูล เกิน 1 ล้านบรรทัด ทำ PivotTable ใน Excel | Power Pivot



จาก บทความ ก่อนหน้านี้ 


บทความนี้จะเป็นวิธีการใช้ Power Pivot  ดึงข้อมูลเกิน 1 ล้าน บรรทัด เข้ามาใน Excel

ขั้นตอนมีตามนี้

1. ไปที่เมนู Power Pivot ใน Excel -> คลิกที่ Manage





2. พอเข้ามาที่ เมนูภายใน ของ Power Pivot เราจะต้องไปดึง Data ข้างนอก เข้ามา

ขึ้นอยู่กับว่า เก็บอยู่ในรูป ไหน ตัวอย่างนี้ จะเป็น Text Files

ดังนั้น ไปที่ Home - > From Other Sources  -> เลือก Text Files -> คลิก Next







3. คลิกที่ Browse -> เลือก Text Files ที่ต้องการ -> คลิก Open

ติ๊ก ที่ Use First Row as Column Headers ด้วย









4.Power Pivot จะค่อยๆ Load ข้อมูล เข้ามาจนครบ -> คลิก Close






5. Data จะถูก Load เข้ามาที่ Power Pivot เรียบร้อย

ไปที่ Home - > คลิกที่คำว่า PivotTable

Power Pivot จะกระโดด กลับไปที่หน้าต่าง Excel ปกติ 





6.เลือก New Worksheet




7.ผลลัพธ์ที่ได้ จะได้ Sheet ใหม่พร้อม เมนู PivotTable ใช้สรุป Data ที่เกิน 1 ล้านบรรทัดได้ทันที



ดึงข้อมูล เกิน 1 ล้านบรรทัด ทำ PivotTable ใน Excel | Power Query

posted Jun 12, 2020, 4:54 AM by Dusit Kob   [ updated Jun 12, 2020, 5:01 AM ]

 วิธีดึงข้อมูล เกิน 1 ล้านบรรทัด ทำ PivotTable ใน Excel | Power Query



Excel จะมี จำนวน บรรทัด 1,048,576 บรรทัด เท่านั้นใน แต่ละ Sheet

แต่ข้อมูลดิบ ที่ จะต้องนำมาใช้ใน Excel บางครั้ง มีข้อมูลเกิน 1 ล้านบรรทัด

สมัยก่อนจะไม่สามารถทำได้เลย

ตั้งแต่ Excel 2010 เราสามารถใช้เครื่องมือ 2 ตัว ที่เพิ่มเข้ามาใหม่ แก้ปัญหานี้

1.Power Query

2.Power Pivot

[ แนะนำให้ใช้ Excel 64 bit กรณีข้อมูลใหญ่มากๆ และ ใช้ RAM เยอะๆ หน่อย ]

บทความนี้จะลองใช้ Power Query ดึง ข้อมูลประมาณ 2 ล้าน บรรทัด มาหมุน PivotTable ใน Excel

ข้อมูลที่เกิน 1 ล้านบรรทัด ส่วนใหญ่จะอยู่ในรูป Text Files หรือ Access

ตัวอย่างนี้จะใช้เป็นกรณี Text Files

มีขั้นตอนตามนี้

1.เมนู Excel - > Data - > From Text / CSV



2.เลือก Text Files ที่ต้องการ แล้วคลิก Import



3.พอ Load ขึ้นมา จะ มี Preview รูปแบบ ข้อมูล

คลิกที่ลูก ศร ปุ่ม Load แล้ว เลือก Load To...




4. จะมีหน้าต่างขึ้นมา ถามว่า จะ Load มาวางใน Excel รูปแบบไหน เลือก PivotTable Report แล้ว คลิก OK




5. พอ Load ข้อมูลเสร็จ จะมีหน้า ต่าง Queries & Connection พร้อมจำนวน Rows ที่ Load

และหน้าต่าง PivotTable ให้สรุป เรียบร้อย





1-10 of 88