เล่นแร่แปรสูตร: หาว่าใน 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

เล่นแร่แปรสูตร: สุ่ม item จากรายการที่เราเตรียมไว้

แนะนำคอลัมน์ เล่นแร่แปรสูตร (Formula Combo)

excel-formula-alchemist

ยินดีต้อนรับสู่บทความแรกของคอลัมน์ใหม่ ที่ชื่อว่า “เล่นแร่แปรสูตร ” (Formula Combo) ซึ่งมีวัตถุประสงค์ที่จะนำเสนอการทดลองประยุกต์ใช้ Excel โดยการผสมสูตรขึ้นมาใหม่จากส่วนประกอบมากมาย  (ใครอยากรู้ว่า Formula ประกอบด้วยอะไรบ้าง เชิญอ่านได้ที่นี่ ) โดยเฉพาะอย่างยิ่งการนำ Function หลายๆ ตัวมาใช้ร่วมกันจะส่งผลให้เห็นถึงพลังมากมายแค่ไหนนั้น รับรองว่าสนุกแน่ครับ

แน่นอนว่าการจะบรรลุวัตถุประสงค์อย่างหนึ่ง ไม่จำเป็นต้องมีการผสมสูตรเพียงแบบเดียว บางทีเพื่อนๆ อาจเจอหรือค้นพบการผสมสูตรที่มีประสิทธิภาพกว่าผมก็ได้ ถ้าเจอแบบนั้นแล้วอย่าลืมมาแชร์กันบ้างนะครับ!

เอาล่ะ เรามาเริ่มกันที่เนื้อหาแรกกันเลยดีกว่าครับ ดูเนื้อหาทั้งหมด…

รวมวันหยุดและวันสำคัญของประเทศไทย ในรูปแบบไฟล์ Excel

Thailand holidays วันหยุดไทย

ช่วงนี้เป็นเทศกาลแห่งวันหยุดปีใหม่ ผมเลยถือโอกาสสรุปเอาวันหยุดของประเทศไทยเอาไว้ให้ ในรูปแบบไฟล์ Excel เผื่อว่าใครจะต้องใช้ฟังก์ชั่นพวก NETWORKDAYS หาจำนวนวันทำงาน จะได้ทำได้ง่ายๆ ไม่ต้องไปนั่งหาข้อมูลมาใหม่ครับ

ดาวน์โหลดไฟล์ได้ที่นี่

ปัจจุบันมีข้อมูลปี 2549-2557 (จะแวะมาเพิ่มให้เรื่อยๆ ครับ อย่าลืม Bookmark ไว้นะ)

Excel 2007+inwexcel-thailand-holiday-ver20140221 (.xlsx)

Excel 2003inwexcel-thailand-holiday-ver20140221 (.xls)

Version History

ver. 20140221

  • แก้ไขข้อผิดพลาด วันที่ 30 ธค. 56 ต้องเป็นวันหยุดธนาคารด้วยครับ

แนะนำวิธีใช้งาน

  • ให้พิจารณาเอาว่าที่ทำงานที่คุณต้องการคำนวณว่า วันทำงานนั้นๆ หยุดตามธนาคาร หรือ ราชการ ให้ Filter คอลัมน์ให้ถูกต้อง
  • หลังจาก Filter คำว่า วันหยุด  แล้ว สามารถ Copy ออกไปไว้ชีทใหม่
  • จากนั้นเอา Range ช่วงวันหยุดที่ Filter แล้ว ไปใส่ใน Argument [Holidays] ของ Function NETWORKDAYS ได้เลยครับ
    NETWORKDAYS(start_date, end_date, [holidays]) 
    *ตัว Holidays เราสามารถเลือกเป็นช่วงวันที่ที่เป็นวันหยุดพิเศษ (คอลัมน์ B ในไฟล์)ได้ ถ้าไม่ใส่ ก็จะถือว่าหยุดแต่เสาร์อาทิตย์
    • ตรงนี้สำคัญมาก!! ถ้าใช้วิธี Filter เฉยๆ โดยไม่ได้ Copy ออกไป บางวันที่เราไม่ได้หยุด (แค่มีวันที่อยู่ใน Range) มันจะยังมองว่าเป็นวันหยุดอยู่นะครับ 
  • บางรายการเป็นวันเสาร์-อาทิตย์อยู่แล้ว (ใส่ซ้ำลงไปใน Range ก็ไม่เป็นไรครับ ไม่มีผลอะไร)
  • บางรายการจะหยุดเฉพาะบางพื้นที่ ให้ระวังด้วย
  • บางรายการเป็นแค่วันสำคัญ ไม่ใช่วันหยุด ผมมีเขียน flag เอาไว้ในคอลัมน์ ไม่ใช่วันหยุด ให้แล้ว

แหล่งที่มาของข้อมูล

ดัดแปลงมาจาก http://www.thaiall.com/calendar/ ครับ

ใครเจอข้อผิดพลาดรบกวนช่วยแจ้งผมด้วยนะครับ

สรุปทุกสิ่งที่คุณควรรู้เกี่ยวกับ Pivot Table

บทความ และ video ประกอบการสอนนี้จะสอนคุณทุกอย่างที่จำเป็นเกี่ยวกับการใช้ Pivot Table บน Microsoft Excel  แล้วคุณจะรู้ว่า Pivot Table ทำอะไรๆ ได้มากกว่าที่คุณคิด ผมรับรองว่าเมื่อคุณอ่าน/ดู VDO ประกอบ และลองทำตามด้วยไฟล์ตัวอย่างที่เตรียมไว้ให้แล้ว คุณจะใช้ Pivot Table ทำอะไรได้อีกมากมายครับ

Pivot Table คืออะไร?

Pivot Table เป็นเครื่องมือบน Excel ที่นำข้อมูลที่อยู่ในลักษณะตารางฐานข้อมูล มาให้เราพลิกแพลงไปมาเพื่อดูผลสรุปข้อมูลในรูปแบบ และ มุมมองต่างๆ กัน

โดยเวลาสร้างตาราง Pivot ขึ้นมาแล้ว หัวตารางของข้อมูลต้นฉบับของเรา จะกลายร่างเป็น Pivot Table Field List ด้านขวาบน ซึ่งมันจะเป็นเหมือน Item ที่ให้เราหยิบไปใช้ทำอะไรได้มากมาย

โหลดไฟล์ตัวอย่างได้ที่นี่ => ไฟล์ข้อมูลตัวอย่างสำหรับฝึกทำตาม Video สอน Excel ( inwexcel-pivot-practice.xlsx )

pivot1

หลังจากโหลดไฟล์ตั้งต้นไปแล้ว เรามาเรียนรู้ต่อไปด้วยกันเลยครับ ดูเนื้อหาทั้งหมด…