Posted in Excel

Ctrl + Arrow : another underutilized hotkey

คุณเป็นคนนึงที่ยังใช้เม้าส์ในการเลือกข้อมูลในตารางอยู่รึเปล่า?

ถ้าใช่ ผมเดาว่าคุณต้องเคยเจอปัญหาการการที่ต้องรอการลากและเลื่อนลงไปถึงก้นตาราง ซ้ำมันมักจะเลยจุดที่เราต้องการไปได้ง่ายๆ

ลองเปลี่ยนมาใช้วิธีกดปุ่ม Ctrl ค้างไว้ และปุ่มลูกศร (ขึ้น ลง ซ้าย ขวา) ดูแทนสิครับ

การกด Ctrl แล้วตามด้วยลูกศร จะเป็นการเลื่อนการเลือกไปยังตำแหน่ง “ขอบ” ของข้อมูล

ทีนี้ หากเราต้องการจะเลือกข้อมูลจากต้นจรดปลายตาราง ก็แค่ให้เลือกขอบด้านใดด้านหนึ่งก่อน จากนั้นกด Ctrl + Shift แล้วกดลูกศรที่ชี้ไปทิศทางตรงกันข้าม

…ง่ายกว่าใช้เม้าส์ร้อยเท่า!…

ดูวิดีโอด้านล่างน่าจะเข้าใจได้ง่ายขึ้นครับ

Posted in Excel

Change Partial Text Color/Size in Cells – เปลี่ยนฟอร์แมตของ Keyword

[English version also available in the latter half of this page]

เคยต้องมานั่งไล่ฟอร์แมตคำบางคำในหลายๆ Cells ไหม?

เคยต้องทำงานกับเซลส์ที่มีคำเป็นร้อยๆ แล้วต้องเพ่งหาคำที่ต้องการจนตาแทบแตกมั้ย?

ถ้าต้องเจอปัญหาแบบนี้ อยากให้ลองใช้ add-in ตัวนี้ดูครับ มีปุ่มหนึ่งในนี้ที่จะช่วยแก้ปัญหาได้

output_YL86MP.gif

 

มาดูกันว่าทำยังไง:

1. อันดับแรก ให้มั่นใจก่อนว่าเรายอมให้ Macro มันทำงานได้ โดยไปที่ Excel’s option > Trust Center > Trust Center Settings… > Macro Settings. แล้วเลือก Disable all macros with notification

2. โหลดไฟล์ add-in ตามลิ้งค์ข้างล่างนี้ไปวางไว้ที่เครื่องก่อน จากนั้นก็จัดการ โหลด Add-in ใน Excel (หรือถ้าคิดว่าคงไม่ได้ใช้งานบ่อยๆ ก็เปิดไฟล์เอาตรงๆ เลยก็ได้ ถ้าใช้วิธีนี้ ในครั้งถัดๆ ไป Excel มันก็จะไม่โหลด add-in ตัวนี้ขึ้นมาให้โดยอัตโนมัติ) หากมีคำถามว่าจะอนุญาตให้ Macro ทำงานได้หรือไม่ ก็ตอบไปว่า Enable Macro
ก็จะได้ Ribbon เพิ่มขึ้นมาหนึ่งตัว ชื่อ KM_TOOLBAR

 

Add-in FileKM_Toolbar.xlam

EXCEL_2016-03-05_18-18-07.png

 

 

 

3. คลิ้กที่ปุ่ม Format Keyword, จะเห็นฟอร์ม Keyword Format Form ถูกเปิดขึ้นมา

EXCEL_2016-03-05_18-19-47.png

 

4. ทีนี้ ให้เปิด book ที่เราต้องการจะทดสอบขึ้นมา หรืออาจจะโหลดไฟล์ข้างล่างนี้ไปทดลองก่อนก็ได้ หลังจากเปิดไฟล์แล้ว ให้เลือก Cells ที่มีตัวหนังสือ

SampleBook

5. จากนั้นก็ให้ใส่คำที่ต้องการลงไป แล้วทดลอง:

  • กดปุ่ม B/ U
  • กดปุ่มสีต่างๆ
  • กดปุ่มลูกศรขึ้นหรือลง เพื่อปรับขนาดตัวอักษร หรือหากอยากให้ได้ขนาดเป๊ะๆ ก็ใส่ตัวเลขในช่องสีดำแล้วคลิ้กที่ปุ่ม Set Size

แค่นี้เอง!

2016-03-05_18-34-56.gif

ข้อนึงที่ควรต้องระวังคือ การทำงานในส่วนนี้ถูกทำโดย VBA ซึ่งจะ Undo ไม่ได้เหมือนการทำงานปรกติ ดังนั้นถ้ายังไม่แน่ใจจริงๆ ก็อย่าไป Save ทับไฟล์งานเดิมนะครับ หรือใช้ Save As.. แทนเพื่อความชัวร์ก็ได้

Credit: ขอบคุณคุณ pskelly99 ที่ได้โพสต์โค้ดกระทู้เกี่ยวกับวิธีการจัดการเปลี่ยนฟอร์แมตตัวอักษรเฉพาะบางส่วนไว้ที่นี่

 

#####English Version Starts from Here#####

 

Ever found yourself trying to format specific word in a number of cells?

Ever found yourself trying to spot occurrences of keyword in cells full of text

One of the features in this add-in will do the trick.

 

Here’s how it works: 

1. Make sure you allow macro to run by going to Excel’s option > Trust Center > Trust Center Settings… > Macro Settings. Here you choose either the 2nd option: Disable all macros with notification

2. Download the add-in file from the link below to your computer, then load the add-in in Excel  (or you can just double click to open the file in Excel but the add-in will not be automatically loaded the next time you open Excel), if prompted by a security notice, choose Enable Macro. There will be a new Ribbon: KM_TOOLBAR

 

Add-in FileKM_Toolbar.xlam

EXCEL_2016-03-05_18-18-07.png

 

3. Click at the Format Keyword button, you will see a Keyword Format Form
EXCEL_2016-03-05_18-19-47.png

 

4. Now, open a workbook, or you can try it with this sample file below. Select the cells with text. 

 

SampleBook

 

5. Enter your keyword in the Keyword Format Form, and try each of the following:

  • click at the B/ U button
  • click at one of the buttons with red/blue/green/etc. color
  • click at the up/down arrow to increase/decrease font size (or if you want to do this more precisely, enter a number in the black input box and click Set Size button).

It’s that simple!

2016-03-05_18-34-56.gif

 

Note that, this action is done with VBA and you cannot undo it, so if you are not 100% sure then always make sure to backup the file first! (Or you can use Save As.., just to be on the safe side)

 

Credit:

Thanks to pskelly99 who posted the code for partial text formatting here, most of the code for this function is based on his post.

Posted in Excel

Plan งานทั้งปีแบบง่ายๆ แถมยัง Filter ได้

พอดีเมื่อวานนี้แฟนผมให้ช่วยทำชีทสำหรับวางแผนงาน โดยมี Requirements มาว่า

  1. แสดงผลเป็น Gantt Chart
  2. สามารถ Filter ดูงานในแต่ละไตรมาสได้
  3. เอาแบบใช้ง่ายๆ ไม่ต้องยุ่งยาก
  4. สามารถเห็นภาพรวมของงานทั้งปี
  5. ไม่ได้ต้องการความละเอียดอะไรมากมาย เอาแค่เห็นว่าในแต่ละเดือน หรือครึ่งเดือน ต้องทำอะไรบ้าง

ทำเสร็จ ก็เลยถือโอกาสเอามาแชร์ซะเลย เผื่อมีใครจะเอาไปใช้ประโยชน์ได้ครับ

โหลดไฟล์ และดูวิดีโอตัวอย่างการใช้งานได้จากข้างล่างนี้เลยครับ

Update! เนื่องจากมีคนขอแบบใส่ข้อมูลเป็นรายสัปดาห์มาด้วย เลยทำเพิ่มแบบ Weekly Interval มาอีกเวอร์ชั่นนึงให้เลือกใช้ตามความสะดวกได้เลยครับ

Posted in Excel, None

ข้ามข้อจำกัดการ Paste Link + Transpose

ผู้ใช้ Excel ทุกคนน่าจะได้มีโอกาสต้องใช้การ Transpose ข้อมูลกันมาบ้างแล้วไม่มากก็น้อย เช่น การเปลี่ยน Layout ระหว่างตารางข้อมูลแนวตั้ง กับแนวนอน

ผู้ใช้อีกจำนวนหนึ่งก็อาจจะเคยทำการ Paste Link … คือการเอา Copy & Paste โดยไม่ได้เอาข้อมูลไปตรงๆ แต่ใช้วิธี Link หรือ Refer กลับมาที่ต้นฉบับแทน ดังเช่นในภาพตัวอย่างด้านล่างนี้

2015-11-30_13-46-43.gif

 

แต่ถ้าเราอยากจะทำการ Paste Link และ Transpose Data ไปพร้อมๆ กันล่ะก็ จะพบว่าเราใช้วิธีการ Paste Special ที่ Excel ให้มาไม่ได้ เพราะเมื่อเราเลือกติ๊ก Transpose ปุ๊บ ปุ่ม Paste Link ก็จะใช้ไม่ได้ปั๊บ

2015-11-30_13-49-11.gif

 

…บางที Microsoft ก็ใจร้ายเกินไป…

 

แต่ไม่เป็นไร ชีวิตเรายังพอมีหนทางให้เดินต่อ

ถ้าอยากลองทำตาม ให้โหลด Sample File นี้มาเปิดก่อนครับ

SampleFile

วิธีแก้ปัญหาวิธีที่ 1 : ใช้ Find and Replace ช่วย

  1. ขั้นแรกให้เราใช้วิธี Copy และมา Paste Special ตามปรกติก่อน
    2015-11-30_13-55-50.png
    .
  2. ณ จุดนี้ จะพบว่าเราได้ตารางข้อมูลอีกอันหนึ่งทีข้อมูลเหมือนกัน แต่จริงๆ แล้วเป็นสูตรที่อ้างอิงไปที่ตารางเดิม โดยใช้เครื่องหมายเท่ากับ (‘=’)
    2015-11-30_13-56-53.png
    .
  3. จากนั้นให้เราเลือกคลุมตารางใหม่ทั้งตาราง แล้วกด Ctrl+H เพื่อเรียกหน้าต่าง Find and Replace ขึ้นมา
    2015-11-30_13-58-02.png
    .
  4. แล้วใส่เครื่องหมาย = ตรงช่อง Find what
    ใส่ x= ตรงช่อง Replace with
    แสร็จแล้วกดปุ่ม Replace All จากนั้นก็ปิดหน้าต่าง Find and Replace นี้ไป
    2015-11-30_14-01-13.png
    .
  5. ตารางใหม่ของเราก็จะหน้าตาเป็นแบบนี้ คือมีตัว x เพิ่มขึ้นมาข้างหน้าสูตร 2015-11-30_14-02-25.png
    .
  6. จากนั้นเราก็ Copy ตารางที่ 2 นี้ มาวางแบบ Transpose อีกทีหนึ่ง
    2015-11-30_14-03-48.gif
    .
  7. จากตรงนี้ เราก็แค่ทำสวนทางกับขั้นตอนที่ 4 คือ เลือกตารางล่าสุด กด Ctrl+H และ ทำการ Replace x= ด้วย = เพื่อเอาสูตรของเรากลับมาดังเดิม 
    2015-11-30_14-06-51.gif
    .

เพียงแค่เท่านี้ เราก็ได้ตารางที่ Link กลับไปตารางเดิม พร้อมทั้ง Transpose ไปพร้อมๆ กันอีกด้วย

วิธีแก้ปัญหาวิธีที่ 2 : ใช้สูตร Transpose

วิธีนี้จะทำได้ง่ายกว่าวิธีแรกนิดหน่อย แต่จะมีความยืดหยุ่นน้อยกว่า เพราะสูตรที่ใส่จะเป็นแบบ Array Formula ดังนั้น เวลาจะแก้ไขสูตรก็จะต้องทำทั้งตาราง

  1. ให้เรานับขนาดของตารางก่อน ว่ากี่ Rows และ กี่ Columns เช่นในตัวอย่างนี้ ตารางของผมมีขนาด 8 Rows X 4 Columns ก็จดไว้ในใจก่อน2015-11-30_14-12-01.png
    .
  2. ลากคลุม Cells อีกที่หนึ่งที่เราอยากจะทำการวางสูตรที่จะลิ้งค์กลับมา่ที่ตารางข้อมูลต้นฉบับ โดยคราวนี้ให้ขนาดกลับด้านกันกับตารางต้นฉบับ กล่าวคือ ของเดิม 8×4 ข้องใหม่นี้ก็ให้เลือกเป็น 4×8
    2015-11-30_14-14-44.png
    .
  3. ใส่สูตร =Transpose( 
    จากนั้นทำการเลือกตารางต้นฉบับที่เราต้องการ
    แล้วกด Ctrl+Shift+Enter เพื่อวางสูตรลงไป
    2015-11-30_14-16-40.gif

 

อ้างอิง: ผมไปเจอ Trick แรกจาก Chandoo.org และอันที่สองจาก Excel.Tips.Net

Posted in Excel

ค้นหาภาพจากตาราง ด้วย Index, Match, และ Named Range

Technique นี้ เพิ่งไปเห็นมาจากวิดีโอ Picture Lookup Technique in Excel โดย Trump Excel

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

PictureLookup_01

เทคนิคที่จะต้องใช้ในที่นี้ก็คือ Index & Match และ Named Range

มาลองทำกันดูเลยดีกว่า!

ขั้นแรกให้ดาวน์โหลดไฟล์ตัวอย่างนี้มาก่อน เป็นไฟล์ที่ผมเตรียมตารางข้อมูลที่เป็นชื่อประเทศและธงชาติไว้ให้แล้ว แต่ไม่ได้ใส่สูตรอะไรไว้เลย

PictureLookup_Unfinished

จากนั้นก็ลองทำตามวิดีโอด้านล่างนี้เลยครับ!

ส่วนข้างล่างนี้คือไฟล์ที่ทำเสร็จแล้วครับ

PictureLookup_Finished