Chart ที่อ่านง่ายกว่า ด้วย 2-Level Axis Labels

การแสดงข้อมูลผ่าน Chart ใน Excel นั้น เป็นเรื่องปกติที่จะมี Labels ของ Timeline เข้ามาเกี่ยวข้อง ซึ่งบ่อยครั้งผมพบว่ามักจะอ่านได้ยาก ยิ่งหากในกรณีที่มีจำนวน Labels มากๆ ก็จะขึ้นมาเป็นพรืด ทำเอาตาลายได้เลยทีเดียว

วิธีง่ายๆ ที่จะช่วยให้ Chart ลักษณะนี้ ดูดีและอ่านได้ง่ายขึ้นคือ ทำ Grouping ของ Labels เหล่านั้น แล้วแสดงบน Chart เป็น 2 levels (หรือมากกว่านั้นก็ยังได้)

ดูตัวอย่างวิธีทำจากวิดีโอได้เลยครับ

ไฟล์ตัวอย่าง: 2-level_axis_sample

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

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

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

 

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

 

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

DownloadPlanningTemplate_Simple

 

ข้ามข้อจำกัดการ 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

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

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

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

PictureLookup_01

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

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

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

PictureLookup_Unfinished

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

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

PictureLookup_Finished

เลือกทั้งตารางข้อมูลในพริบตา

Trick นี้น่าจะมีประโยชน์กับคนที่ต้องทำงานกับตารางข้อมูลบ่อยๆ

เวลาทำงานกับตารางข้อมูลใน มีหลายครั้งที่เราต้องการจะเลือกทั้งตาราง (ในกรณีของผม มักจะเป็นการ Copy ข้อมูลไปปู้ยี่ปู้ยำโดยไม่ให้กระทบกับตารางข้อมูลต้นฉบับ)

ที่ผมสังเกตก็คือ แต่ละคนจะมีวิธีการในการเลือกทั้งตารางแตกต่างกัน

บางคนอาจจะใช้ Mouse ลากเอา (ซึ่งถ้าตารางมันมีจำนวน Rows เยอะๆ ก็จะยากหน่อย)

บางคนใช้ปุ่ม Ctrl + Shift แล้วก็ลูกศร ขึ้นๆ ลงๆ ซ้ายขวาซ้ายขวา..

แต่วิธีที่ (ผมคิดว่า) ง่ายที่สุดก็คือการกด Ctrl + A ครับ

เพียงแค่ให้มี Active Cell เป็นส่วนหนึ่งของตารางข้อมูลที่เราต้องการ จากนั้นก็กด Ctrl + A

ทั้งตารางข้อมูล (ที่อยู่ในอาณาเขตเดียวกัน) ก็จะถูกเลือกแล้วครับ

01

หมายเหตุ : จากตรงนี้ หากเรากดอีก Ctrl+A อีกครั้ง ก็จะเป็นการเลือกทุก Cells ในชีทแทน

….

แถม #1

  • ถ้าต้องการเลือกทั้ง Row : กด Shift + Space
    02
  • ถ้าต้องการเลือกทั้ง Column : กด Ctrl + Space
    03

แถม #2

หลังจากเลือกทั้ง Row/Column แล้ว…

  • หากต้องการลบทั้ง Row/Column : กด Ctrl และปุ่มเครื่องหมายลบ (-)
  • หากต้องการแทรก Row/Column : กด Ctrl และปุ่มเครื่องหมายบวก (+)
    หมายเหตุ : หากเราไม่ได้ใช้ปุ่ม + จาก NumPad วิธีการกดก็จะคล้ายๆ ลบทั้ง Row/Column … แต่มีปุ่ม Shift เพิ่มมาด้วยครับ เพื่อให้เป็นเครื่องหมายบวกแทนที่จะเป็นเครื่องหมายลบ