excel_blog


วิธี Lock กราฟ Excel ไม่ให้ ขยับ หรือ ถูกแก้ไข | Lock Excel Chart

posted Feb 14, 2018, 9:50 PM by Dusit Kob   [ updated Feb 14, 2018, 9:51 PM ]


วิธี Lock กราฟใน Excel ไม่ให้ขยับ หรือ ถูกแก้ไข



บางครั้งอาจจะมีการทำ report ด้วย กราฟ ต่างๆ ใน Excel และ เมื่อส่งให้ คนอื่นดู ไม่อยากให้ คนดูสามารถขยับ กราฟได้

หรือแก้ไข กราฟได้ เพราะถ้า ขยับ หรือ แก้ไข อาจจะทำให้ format ต่างๆ เปลี่ยนไป

แต่ยังสามารถ แก้ไขข้อมูลใน Worksheet ได้ปกติ

วิธีการสามารถทำได้ตามนี้คัรบ

1.คลิก เลือกทั้ง Sheet - >  คลิกขวา Format Cells - > Protection ที่คำว่า Locked  ติ๊กออก และคลิก OK

(เพื่อให้ทุก Cells ยังสามารถทำงานได้ปกติ )



2.Review - > Protect Sheet



3.ติ๊กเลือกทุกตัว ยกเว้น Edit Objects (ให้ทำได้ทุกอย่าง ยกเว้น แก้ไข หรือ ขยับ Objects ซึ่งกราฟ เป็นหนึ่งใน Objects )

   และ ใส่ Password  แล้ว  OK


   หลังจากนี้ จะไม่สามารถขยับ กราฟได้ แต่ยังทำงานกับทุก Cell ได้ปกติ 




วิธี Sort ข้อมูลใน Excel แนวนอน | Excel Sort Hozitontal

posted Feb 11, 2018, 2:14 AM by Dusit Kob   [ updated Feb 11, 2018, 2:16 AM ]


วิธี Sort ข้อมูลใน Excel แนวนอน  




ถ้ามีการเก็บข้อมูลแนวนอน ใน Excel และ อยากจะมีการ Sort เราสามารถสั่งให้ Excel Sort ข้อมูลแนวนอนได้

วิธีการสามารถทำได้ตามนี้ครับ 

1. เอาเม้าส์ คลุม กลุ่มข้อมูล ทั้งหมด  - > Data - > Sort



2.คลิกที่ Options 



3.เลือก  Sort left to right




4.เลือก Row ที่ตัองการจะ Sort ตามลำดับที่ต้องการ และ click OK








นำเสนอ ข้อมูล ให้ดู โปร ใน Excel ด้วย Data Bars

posted Feb 1, 2018, 10:12 PM by Dusit Kob   [ updated Feb 1, 2018, 10:30 PM ]


นำเสนอข้อมูล ให้ดู โปร ใน Excel ด้วย Data Bars | Conditional Formatting    



ถ้าต้องการ นำเสนอ ข้อมูล ใน Excel ทางเลือก นอกเหนือ จาก การสร้าง Chart 

อีกอย่างหนึ่งคือ Conditional Formatting ด้วย Data Bars

ตัวอย่างเช่น  ถ้าเรามีข้อมูลตามตารางด้านล่าง และ ต้องการ นำเสนอ เปรียบเทียบ ยอดขาย ของแต่และ Customer 

เพื่อดูความแตกต่าง เราสามารถใช้ Data Bars ได้เลย




วิธีการคือ 

1.เอา เม้าส์ คลุมตัวเลขที่ต้องการ ใส่ Data Bars







2. Home - > Conditional Formatting -> Data Bars และเลือกสีได้เลย



ผลลัพธ์ ที่ได้ จะมี  Data Bars สี ตาม ตัวเลข โดย Default คือ แท่งสีที่เต็ม Cell จะเป็นตัวเลขที่ Maximum 


4.แต่ถ้าต้องการปรับ ขนาดของแท่ง Bars สามารถปรับได้ โดย คลุม กลุ่ม Cells - > Conditional Formatting - > Manage Rules



5.คลิกที่ Edit Rules




6. เปลี่ยนจาก Automatic - > Number ทั้ง Max และ Min โดยกำหนดตัวเลขเข้าไปเอง ตัวอย่างคือเปลี่ยน 

Min = 0 

Max = 8,000,000








7.ผลลัพธ์ที่ได้ ขนาดของ Data bars จะเล็กลง โดยถ้าสมมติ ตัวเลข 8,000,000 ขนาด Data Bars ถึงจะเต็ม Cell



วิธี LOOKUP ข้ามไฟล์ ไม่ต้องผูกสูตร โดยใช้ Power Query

posted Jan 21, 2018, 11:40 PM by Dusit Kob   [ updated Jan 22, 2018, 6:01 AM ]


วิธี LOOKUP ข้ามไฟล์ ไม่ต้องผูกสูตร โดยใช้ Power Query     


Power Query-Merge1




Power Query สามารถ สั่งให้ทำงานเหมือน  VLOOKUP ข้ามไฟล์ โดยไม่ต้องผูกสูตร  ซึ่งอยู่ในเครื่องมือ ที่ชื่อว่า Merge


รูปด้านล่าง ใช้จาก Excel Version 2016  โดย Power Query สามารถใช้ได้ตั้งแต่ Excel 2010 ขึ้นไป

ใน Excel 2013 หรือ 2010 จะมี Tab ด้านบนเมนู แยกต่างหาก ชื่อ Power Query 

รูป เมนู Power Query ใน Excel 2013


แต่ใน Excel 2016 เมนู Power Query จะรวมอยู่ใน Data -> New Query 




จากตัวอย่างมีข้อมูล 2 Files 

Customer Data  - > รายละเอียดข้อมูลของลูกค้า

Invoice Data -> รายละเอียด Invoice พร้อม รหัส ลูกค้า

สามารถลอง Download File ไปลองใช้จาก ด้านล่างได้ครับ



โดยทั่วไป เราสามารถดึงข้อมูล ลูกค้า มาสรุป ใน Invoice Data ได้ โดยใช้ VLOOKUP แบบข้ามไฟล์

ใน Power Query มีเครื่องมือ ขื่อ ว่า Merge สามารถทำงานลักษณะดังกล่าว โดยไม่ต้องผูกสูตรเลย 

มีวิธี การทำ ตามนี้ครับ 

1. เมนู Data -> From File - >  From Workbook



2.เลือก File แรก คือ  Customer Data แล้ว คลิก Import


3.เลือก ชื่อ Sheet แล้ว คลิก Load




4.Excel จะสร้าง Sheet ใหม่ โดยดึงข้อมูลจาก File Customer Data มา 





5. เลือก ข้อมูล จาก File Invoice_data 

เมนู Data -> From File - >  From Workbook



6. เลือก File แล้วคลิก  Import




7.เลือก sheet ที่ต้องการดึงข้อมูล แล้ว คลิก Load 



8.Excel จะสร้าง Sheet ใหม่ โดยดึงข้อมูลจาก ไฟล์ Invoice Data มาใส่ 



9.เอา mouse ในข้อมูล ไปที่เมนู Query เลือก Merge




10.เลือก ตาราง ที่ต้องการ Merge โดย ตารางแรก คือ INV_Details ตารางที่สองคือ Customer

และ เลือก Column ที่ต้องการ Lookup จากตัวอย่างนี้คือ Column ชื่อ CustomerID

โดยใน  INV_Details คลิกเลือกที่ Column  ชื่อ CustomerID

และ ใน Customer  คลิก เลือก Column ชื่อ CustomerID

แล้วกด OK 



11.Excel จะสร้างหน้าต่างใหม่ เป็นหน้าต่าง ของ Power Query ขึ้นมา และ มี Column  ชื่อ Customer ขึ้นมา

 โดย Column นี้ เกิดจากจาก Lookup ข้อมูลเรียบร้อยแล้ว



12.คลิกที่หัวคอลัมน์ ตรงลูก ข้างขวามือของคำว่า Customer จะมีเมนูเล็กๆ ให้เลือก ข้อมูลจาก Customer ทีต้องการดึงมา



จากรูป จะลือก Customer Name , Address , Country 

ผลลัพธ์ที่ได้จะดึงข้อมูลมาโชว์ เพิ่มขึ้นใน column ด้านข้าง



13. คลิกที่คำว่า Close & Load ที่เมนู ด้านซ้าย บน ใน Tab Home


14. Excel จะสร้าง Sheet ใหม่ และ นำข้อมูล ที่ถูก Merge มาแสดง




วิธีรวมข้อมูล Excel File จากทั้ง Folder มาเรียงต่อกันอัตโนมัติ ด้วย Power Query

posted Jan 21, 2018, 7:13 AM by Dusit Kob   [ updated Jan 21, 2018, 5:42 PM ]


วิธีรวมข้อมูล Excel File จากทั้ง Folder มาเรียงต่อกัน ด้วย Power Query     



หลายๆครั้งที่เราต้องการจะรวมข้อมูลจาก Excel File ทั้ง Folder มาอยู่ใน file เดียวกัน โดย
รวมแบบลักษณะ มาเรียงต่อกัน ซึ่งถ้าจำนวน file เยอะ จะทำให้เสียเวลามาก 

งานลักษณะแบบนี้ เราสามารถใช้ Power Query ใน Excel ได้เลยครับ

รูปด้านล่าง ใช้จาก Excel Version 2016  โดย Power Query สามารถใช้ได้ตั้งแต่ Excel 2010 ขึ้นไป

ใน Excel 2013 หรือ 2010 จะมี Tab ด้านบนเมนู แยกต่างหาก ชื่อ Power Query 

รูป เมนู Power Query ใน Excel 2013


แต่ใน Excel 2016 เมนู Power Query จะรวมอยู่ใน Data -> New Query 



วิธีการสามารถทำตามนี้ได้เลย

1.ไปที่เมนู Data -> New Query -> From File - > From Folder



2.Browse ไปยัง Folder ทีต้องการ แล้วกด OK





3. จะมีหน้าต่าง แสดง ชื่อ File ทั้งหมด ใน Folder ที่เลือก 

    ที่ด้านล่างหน้าต่าง เลือก Combine & Load



4. จะมีหน้าต่างแสดง Preview ของข้อมูล คลิก Ok ที่ด้านล่าง





5. ผลลัพธ์ที่ได้ ข้อมูจากทุก File จะถูกนำมารวมกัน ในลักษณะ เรียงต่อกัน ไปเรื่อยๆ 





วิธีใช้ SUMIF แนวนอน | SUM เฉพาะ Plan

posted Jan 16, 2018, 7:11 AM by Dusit Kob   [ updated Jan 16, 2018, 7:20 AM ]


วิธีใช้ SUMIF แนวนอน | SUM เฉพาะ Plan


จากรูป ถ้าเรามี Template ด้านบน แล้วต้องการ sum ตัวเลขเฉพาะ Plan ในแต่ละเดือน ของแต่ละ Product 

เราสามารถใช้ Function ชื่อ SUMIF ได้ครับ 

โครงสร้างการใช้ SUMIF คือ

SUMIF( Range ,Criteria ,SUM_Range)

Range คือ กลุ่ม range ที่เก็บเงื่อนไข

Criteria คือ เงื่อนไข สำหรับ เลือก ที่จะ sum

SUM_Range กลุ่ม range ที่ต้องการ SUM

วิธีที่ 1

ผูกสูตร ด้วย อ้างเงื่อนไข ตรงๆ ด้วยคำว่า "Plan"



วิธีที่ 2

ผูกสูตร และ อ้างจากช่องที่เก็บ คำว่า Plan 



สามารถ download file ไปลองเล่นดูได้ครับ 

วิธีเขียน VBA สั่ง Lock Worksheet

posted Jan 13, 2018, 6:52 AM by Dusit Kob   [ updated Jan 13, 2018, 6:53 AM ]

วิธีเขียน VBA สั่ง Lock Worksheet


เราสามารถเขียน VBA สั่งให้ Excel Lock worksheet และ Unlock Worksheet ได้

โดยใช้ Method ชื่อ Protect และ Unprotect 

มีรูปแบบการเขียนตามรูป

สามารถลอง download file ไปลองเล่นดูได้ครับ


วิธีลบ Sheet และ เพิ่ม Sheet แบบเร็วที่สุด ไม่ต้องใช้ เม้าส์

posted Jan 4, 2018, 6:50 PM by Dusit Kob   [ updated Jan 4, 2018, 6:52 PM ]

วิธีลบ Sheet และ เพิ่ม Sheet แบบเร็วที่สุด ไม่ต้องใช้ เม้าส์ 

เวลาทำงานบน Excel มีงานที่หนีไม่พ้น คือ เพิ่ม และ ลบ Sheet 

ซึ่งโดยพื้นฐานทั่วไปต้องใช้ เม้าส์ 

จริงๆ แล้ว Excel มี Shortcut สำหรับ เพิ่มและ ลด sheet ครับ 

Add Sheet :  Shift -> F11

Delete Sheet :  Alt -> E ->  L 



วิธีเขียน VBA เช็ค Filter

posted Dec 20, 2017, 4:01 PM by Dusit Kob   [ updated Dec 24, 2017, 3:42 PM ]

วิธีเขียน VBA เช็คว่า Filter อยู่หรือเปล่า

เราสามารถเช็ค ว่า ข้อมูลมมีการ Filter อยู่หรือไม่ ด้วยการใช้ Property ของ Worksheet ชื่อ AutoFilterMode

โดยถ้าข้อมูลมีการ Filter ผลลัพธ์ จะเป็น True

ถ้าข้อมูลไม่มีการ Filter ผลลัพธ์ จะเป็น False

ลองพิมพ์ Code ตามในรูปได้เลยครับ

หรือ ลอง Download File ด้านล่างไปลองเล่นดูได้ครับ




วิธี จัดข้อความให้อยู่ตรงกลาง โดยไม่ Merge

posted Dec 10, 2017, 7:40 PM by Dusit Kob   [ updated Dec 10, 2017, 7:41 PM ]

วิธี จัดข้อความให้อยู่ตรงกลาง โดยไม่ Merge

ใน Excel ปกติ เราสามารถ จัด ข้อความให้อยู่ตรงกลาง ได้โดย การ Merge 

แต่การ Merge ข้อเสียค่อนข้างเยอะ เช่น การนำข้อมูลไป Filter , PivotTable หรือ การผูกสูตร 

วิธีการจัดข้อความ ให้อยู่ตรงกลางโดย ไม่ Merge เราสามารถทำได้ตามนี้ 

1.คลุม ข้อความ ที่ต้องการให้อยู่ตรงกลาง

2.คลิกขวา Format Cells

3.Alignment - > Horizontal - > Center Across Selection 

ข้อความจะอยู่ตรงกลาง โดยที่ไม่ด้อง Merge


1-10 of 41