เทคนิคการลบข้อมูลเก่าที่ค้างอยู่ใน Pivot Table

เวลาที่เราทำ Pivot Table เสร็จแล้วมีการเปลี่ยนแปลงข้อมูลใน Source Data อาจทำให้มีเหตุการณ์ที่มี Item ที่เคยอยู่ใน Source Data เก่าตกค้างใน Drop Down List ใน Row Label, Column Label, หรือ Filter ได้

pivot-old-item

หากเพื่อนๆใช้ Excel ตั้งแต่ Version 2007 ขึ้นไป สามารถแก้ไขเหตุการณ์ดังกล่าวได้ง่ายมากๆ โดย

  1. คลิ๊กขวาที่ตาราง Pivot
  2. เลือก PivotTable options
  3. เลือก Data tab
  4. ในหมวด Retain Items เลือก None
  5. OK แล้วกด Refresh ตาราง Pivot อีกครั้ง

pivot-old-item2

สำหรับ Excel Version เก่า สามารถไปโหลด Script VBA เพื่อแก้ไขได้ที่ http://www.contextures.com/xlPivot04.html

Lookup ข้อมูลใน Excel ไม่ยากเลย

วีดีโอชุดนี้จะสอนคุณใช้ฟังก์ชั่นในการ Lookup ข้อมูลตั้งแต่พื้นฐาน โดยสอนเทคนิคการ Lookup ข้อมูลใน Excel ด้วย VLOOKUP, MATCH, INDEX ด้วยตัวอย่างที่เข้าใจง่าย ดูแล้วทำตามได้เลยครับ

ไฟล์ประกอบ : Lookup-Tutorial-Inwexcel2(.xlsx)

ตอนที่1/4

ตอนที่2/4

ตอนที่3/4

ตอนที่4/4

บทความเสริมความรู้ที่เกี่ยวข้อง

ลูกเล่นเกี่ยวกับ Gridlines

คำว่า Gridlines ในที่นี้คือ เส้นแบ่งช่อง ไม่ใช่กรอบของช่องแต่ละช่อง หรือ Border นะครับ

การซ่อน Gridlines ให้มองไม่เห็นบนหน้าจอ

เราสามารถซ่อนไม่ให้มองเห็นบนหน้าจอคอมพ์ได้ ซึ่งทำได้ 2 วิธี ซึ่งดีกว่าการใช้วิธีถม Fill Color ด้วยสีขาวแน่นอน

excel grid lines

  1. ไปที่ Ribbon แถบ View แล้วติ๊ก Gridlines ออก
  2. ไปที่ Ribbon แถบ Page Layout => Sheet Options แล้วติ๊ก View ตรงแถบ Gridlines ออก

การ Print เส้น Gridlines

ซึ่งปกติ Gridline จะ มองไม่เห็นเวลา Print ลงกระดาษอยู่แล้วนะครับ แต่เราสามารถตั้งค่าให้มันพิมพ์แล้วมองเห็นได้ด้วย โดย

ไปที่

Ribbon แถบ Page Layout => Sheet Options แล้วติ๊ก Print ตรงแถบ Gridlines ให้ติ๊กค้างเอาไว้

เล่นแร่แปรสูตร: หาว่าใน cell นั้นๆ มีตัวอักษรที่เรากำหนดอยู่กี่ตัว

สถานการณ์

ในช่อง A1 มีคำว่า THE*DARK*KNIGHT*IS*COOL

ผมต้องการหาว่ามีตัว * กี่ตัว จะทำยังไง??

ส่วนประกอบ

  • LEN เพื่อนับจำนวนตัวอักษรทั้งหมด
  • SUBSTITUTE(text,old_text,new_text,[instance_num]) เพื่อแทนที่ตัวอักษร

Concept การผสมสูตร

  • ใช้ LEN เพื่อนับตัวอักษรทั้งหมดว่ามีกี่ตัว
  • ใช้ SUBSTITUTE แทนที่ตัวอักษรที่เราต้องการหาด้วย “” (เหมือนลบตัวอักษรนั้นทิ้ง)
  • ใช้ LEN นับคำที่ SUBSTITUTE แล้ว จากนั้นเอาไปหักออกจากที่นับไว้ตอนแรก

ขั้นตอนการผสมสูตร

ในช่อง A1 เขียนว่า THE*DARK*KNIGHT*IS*COOL

ในช่องอื่น ช่องไหนก็ได้

  • =LEN(A1)  ได้ 23
  • =SUBSTITUTE(A1,”*”,””)  ได้ THEDARKKNIGHTISCOOL
    • เราตัดตัว * ทิ้งทั้งหมด ด้วยการไม่กำหนด [instance_num] ว่าจะแทนที่ตัวไหนเป็นพิเศษ
  • นับ THEDARKKNIGHTISCOOL
    • =LEN(SUBSTITUTE(A1,”*”,””)) ได้ 19
  • เอามาลบกัน
    • =LEN(A1) - LEN(SUBSTITUTE(A1,”*”,””)) ได้ 4 ครับ

หาวันสุดท้ายของเดือนว่าเป็นวันที่เท่าไหร่

calendar_icon1

เราสามารถหาวันสุดท้ายของเดือน ว่าเป็นวันที่เท่าไหร่ได้ง่ายๆ

มี 2 วิธี คือ

1. การหาวันแรกของเดือนถัดไป แล้วลบออก 1 
เช่น จะหาวันสุดท้ายของเดือนกุมภาพันธ์ของปี 2014

= (วันที่ 1 ของเดือนมีนาคม 2014 )- 1

=DATE(ปี,เดือน,วัน)-1

=DATE(2014,3,1)-1

= 28/02/2014

2. ใส่วันของเดือนถัดไปเป็น 0 ในฟังก์ชั่น Date เลย  

=DATE(2014,3,0)

= 28/02/2014

Tips : Day ในฟังก์ชั่น DATE เราใส่วันติดลบ หรือเกิน 31 ก็ได้นะครับ !! (มันจะเลื่อนวันไปเดือนถัดไปเอง) และอย่าลืมว่าใส่ 0 ก็ได้ผลอย่างที่บอก คือจะได้วันสุดท้ายของเดือนก่อนมานั่นเอง

ใครงงว่าทำแบบนี้ได้ยังไง ไปอ่านความรู้พื้นฐานได้ที่ การทำงานเกี่ยวกับวันและเวลา (Date & Time) ใน Excel