Author: Sira Ekabut

สอนทำไฟล์ Excel บริหาร Stock สินค้าคงคลัง : Version 1 ง่ายสุดๆ
เรื่องการบริหาร Stock เป็นปัญหายอดฮิตอันนึงที่หลายคนต้องเจอ เพราะธุรกิจส่วนใหญ่จะมีการซื้อขายสินค้า ดังนั้นการบริหาร Stock จึงเป็นเรื่องที่หลีกเลี่ยงไม่ได้เลย โดยเฉพาะกับธุรกิจ SME ที่ยังไม่มีระบบบริหารจัดการที่ดี ก็อาจยังต้องใช้ Excel มาช่วยอยู่มากพอสมควร

ความฮิตของเรื่องนี้สะท้อนมาให้เห็นในผลโหวตในเพจ ที่ผมถามไปว่า “ระหว่าง บริหาร Stock สินค้า กับ การเทียบยอดขายกับ Target อยากให้ทำเรื่องไหน?”ตัวที่ได้ผลโหวตมากกว่า คือเรื่องการบริหาร Stock สินค้านั่นเอง แสดงว่ามีคนมีปัญหานี้เยอะจริงๆ

ก่อนหน้านี้ (นานมาแล้ว…) ผมเคยเขียนเรื่องการบริหาร stock ไว้นิดหน่อยแล้วล่ะ แต่คราวนี้จะเขียนให้ครบทุกมิติมากขึ้น และละเอียดขึ้น ยังไงลองติดตาม Series นี้ได้เลยครับ

Series เรื่องการจัดการ Stock นี้มี 3 ตอน
ความสัมพันธ์ของ Stock สินค้า
```
Stock ตั้งต้น + การเปลี่ยนแปลง = Stock คงเหลือ
```
หรือจะเขียนให้ละเอียดอีกนิดได้ว่า
```
Stock ตั้งต้น + การซื้อเพิ่ม - การขายออก = Stock คงเหลือ 
```
จริงๆ ความสัมพันธ์แบบนี้ก็ใช้ได้กับทุกอย่างอ่ะนะ เช่น เงิน Balance ในบัญชีธนาคารก็ด้วย 555

มาเริ่มทำไฟล์ Excel บริหาร Stock กัน

ความสัมพันธ์พื้นฐานของการบริหาร Stock

ลองใส่ข้อมูล sample ลงไปตามนี้ก่อน
(ในความเป็นจริงแต่ละช่องอาจะเขียนเป็น =30+10+10 ไรแบบนี้ก็ได้นะครับ)

ต่อไปเราเปลี่ยนข้อมูลให้เป็น Table เพื่อให้ตารางมันงอกเพิ่มเองได้เวลามีการเพิ่มข้อมูล โดยเลือกข้อมูลช่องนึง (ช่องไหนก็ได้) แล้วกด Insert -> Table หรือกด Ctrl+T ก็ได้

จากนั้นเราเขียนสูตรในช่องคงเหลือได้ ดังนี้
```
คงเหลือ =[@ตั้งต้น]+[@ซื้อเพิ่ม]-[@ขายออก]
```
ซึ่งไอ้สัญลักษณ์พวก @ นี่ไม่ได้พิมพ์เอง แต่ใช้วิธีคลิ๊กแต่ละช่องในบรรทัดเดียวกัน มันจะขึ้นมาเองนะครับ ( [@xxx] เป็นวิธีการอ้างอิงข้อมูลใน Table หมายถึงเอาข้อมูลในคอลัมน์ xxx นั้นๆ ในบรรทัดเดียวกันกับช่องที่เขียนสูตรอยู่ ซึ่งเราใส่สูตรแค่บรรทัดเดียว มันจะ Fill สูตรเดียวกันลงไปเองทุกบรรทัด)

จุดสั่งของเพิ่ม ( Reorder Point)

สมมติว่ามีการกำหนดว่า ถ้าของเหลือน้อยกว่า จุดสั่งของเพิ่ม (Reorder Point) จะต้องขึ้นมาเตือนด้วย เราก็ทำได้ง่ายๆ ครับ โดยการกำหนดตัวเลข Reorder Point ของสินค้าแต่ละตัวลงไป

จากนั้นก็ทำการเพิ่มคอลัมน์เพื่อแสดงขึ้นมาว่า ต้องสั่งเพิ่มหรือไม่ โดยเขียนสูตรว่า
```
ต้องสั่งเพิ่ม =[@คงเหลือ]<[@จุดสั่งของเพิ่ม]
```
ถ้าสิ่งที่คงเหลือมีน้อยกว่าจุดที่ต้องสั่งเพิ่ม ผลจะออกมาเป็น TRUE ก็แปลว่าต้องสั่งเพิ่ม

สังเกตว่า ถ้าแค่อยากได้ผลลัพธ์เป็น TRUE/FALSE แค่นี้เราแค่ใช้เครื่องหมายเปรียบเทียบ มาช่วยเปรียบเทียบข้อมูล 2 ก้อน แค่นั้นก็เพียงพอแล้ว ไม่ต้องใช้ฟังก์ชัน IF ก็ได้นะ

ใส่ Conditional Format ซะหน่อย

เพื่อให้เห็นคำว่า TRUE ชัดขึ้น เราจะใส่ Conditional Format แบบ Highlight cells Rule –> Equal to เข้าไปดังนี้

ขั้นตอนแรกให้เลือกข้อมูลในคอลัมน์ ต้องสั่งเพิ่มซะก่อน โดยให้เอา cursor ไปไว้ที่ขอบตารางจนเป็นลูกศรทึบสีดำแล้วค่อยกดคลิ๊กเพื่อเลือกข้อมูลนะ

จากนั้นใส่เงื่อนไขว่าให้ใส่ Format เมื่อค่าในช่องเป็น TRUE (ก็พิมพ์เข้าไปเลย) แล้วด้านขวาคือจะให้ Format ด้วยสีอะไรยังไง ก็เลือกได้ตามใจชอบ (ถ้าไม่พอใจก็กด Custom Format ได้)

ถ้าเราอยากดูเฉพาะตัวที่ต้องสั่งเพิ่ม ก็ Filter คอลัมน์ต้องสั่งเพิ่มเป็น TRUE ได้เลยเนอะ อันนี้น่าจะทำเป็นกันนะครับ

แยกความต่าง ระหว่างช่อง Input กับช่อง Output

เราควรใส่สีให้ต่างกันระหว่างช่องที่ต้องมีการกรอกเอง กับช่องที่คำนวณอัตโนมัติด้วย คนกรอกจะได้ไม่งงว่าต้องกรอกช่องไหนบ้าง (ถ้าให้ advance กว่านี้เรา Lock ได้ว่าช่องไหนยอมให้กรอก ไม่ให้กรอก ซึ่งเดี๋ยวไว้ค่อยทำทีหลังนะ)

ถ้าจะเพิ่มสินค้าล่ะ?

ก็พิมพ์เพิ่มต่อไปในบรรทัดถัดไปได้เลย เนื่องจากเราสั่งให้เป็น Table แล้ว ทุกอย่างจะขยายลงมาที่บรรทัดล่างเองโดยอัตโนมัติครับ ทั้งสูตร ทั้ง Format ทั้ง Conditional Format มาหมดเลย

จะทำอะไรต่อดี?

ตอนนี้ได้ไฟล์บริหาร Stock แบบง่าย (มากๆ) มาอันนึงแล้วล่ะ แต่ยังขาดความสามารถอีกหลายอย่างเลย เช่น
- ข้อมูลการซื้อ-ขายสินค้า อาจมีได้หลายที (จะให้เขียนสูตรว่า =30+10+10 ไรงี้ก็ลำบาก) ควรจะทำตารางการซื้อ การขายแยกออกไปต่างหากเลยดีกว่า
- ถ้าจะมีข้อมูลการซื้อขายสินค้าก็ควรมีพวกนี้ตามมาอีก
  - Drop-down List เลือกสินค้าให้มันง่ายหน่อยจริงมะ?
  - ต้องมีการคำนวณสรุปว่า ตกลงสินค้าที่สนใจซื้อมารวมกี่ชิ้น ขายไปรวมกี่ชิ้น
- สินค้าซื้อมาจาก Vendor เจ้าไหน?
- ลูกค้าที่ซื้อเป็นใคร?
- ราคาต่อหน่วยเท่าไหร่ ทั้งตอนซื้อ และตอนขาย? แล้วราคาเปลี่ยนไปแต่ละช่วงเวลาไม่เท่ากันอีก
- ส่วนลดพิเศษมีอีกมั้ย?
- สุดท้ายกำไรเป็นกี่บาท?
- กำไรนับแบบ FIFO LIFO อะไรคิดยังไง?
- จะ scope ดูเฉพาะช่วงเวลาที่สนใจยังไง?
เรื่องอะไรทำนองนี้ เดี๋ยวจะมาทำในบทความถัดๆ ไปนะครับ ซึ่งจะเห็นว่ามีเรื่องต้องทำอีกเยอะมาก ใครอยากให้มันทำอะไรได้นอกเหนือจากที่ List ไว้อีก ก็ Comment ไว้ได้เลย เดี๋ยวจะค่อยๆ อัปเกรดเจ้าไฟล์นี้ไปเรื่อยๆ และสอนไปด้วยนะ

อ่านตอนต่อไป คลิ๊กที่นี่
August 23, 2019
สอนวิธีทำ Drop Down List แบบ Search ได้ใน Excel (ไม่ต้องใช้ VBA)
คุณเคยมีปัญหามีรายการที่จะต้องแสดงใน Drop Down List จำนวนมากจนแสดงไม่ไหวหรือไม่?

วิธีแก้อันนึงที่ผมนำเสนอไปแล้วคือ การทำ Drop Down หลายชั้น แต่หากเราสามารถที่จะทำให้คนใช้งานพิมพ์ข้อความบางส่วนแล้วแสดงรายการที่มีคำนั้นๆ ได้เลยจะดีขนาดไหน?

หลายคนคิดว่าเรื่องแบบนี้ต้องใช้ VBA ทำเท่านั้น แต่ในความเป็นจริง เราก็สามารถเขียนสูตรเพื่อสร้าง “Drop down List แบบค้นหาได้” โดยจะใช้ Excel Version ไหนก็สามารถทำได้ครับ สุดยอดไปเลยมั้ยล่ะ!!

คลิปวีดีโอ

ในคลิปนี้ผมมีการอธิบายโดยละเอียดถึงวิธีทำตั้งแต่ต้นจนจบ ลองดูได้เลยครับ

ไฟล์ประกอบการทำตาม

ไฟล์เริ่มต้น :

https://drive.google.com/open?id=1qiWOJjoB9dGyPDt9HjByaCLUwky8_jqX

ไฟล์จบ :

https://drive.google.com/open?id=17QXXR5xVtHqxPgU1ay5biyg4Vcd1ypod

หลักการทำงานของ Drop Down List แบบค้นหาได้
- ใช้ ISNUMBER + SEARCH เพื่อเช็คว่ารายการไหน มีคำค้นหาอยู่บ้าง
- พยายามเอารายการที่เจอ มากองรวมกันข้างบนด้วย INDEX
  - ในนี้ผมมีใช้ฟังก์ชัน ROW กับ RANK เพื่อช่วยจัดอันดับตัวที่เจออันดับ 1,2,3 ด้วยครับ
- จากนั้นใช้ OFFSET เพื่อเลือก Range ของรายการที่เจอทั้งหมด
- เอาสูตร OFFSET ที่ได้ไปตั้งชื่อ
- เอาชื่อที่ตั้งไปใส่ใน Data Validation List
- เลือก Option ว่าไม่ต้องแสดง Error Alert
- ใช้ IF ดักว่าถ้าหาไม่เจอ ให้แสดงคำว่า “ไม่เจอข้อมูล”
สรุปสูตร
```
IsFound =ISNUMBER(SEARCH($A$2,[Province]))
GetRowNum =IF([@IsFound],ROW([@Province]),"")
Rank =RANK.EQ([@GetRowNum],[GetRowNum],1)
Rearrange =INDEX([Province],MATCH(ROW([@Province])-1,[Rank],0))
count=--NOT(ISERROR([@Rearrange]))

=IF(SUM(Table1[count])=0,$K$4,OFFSET(Table1[[#Headers],[Rearrange]],1,0,SUM(Table1[count]),1))

ใน A2 คือช่องที่จะทำ Dropdown
ใน K4 มีคำว่า ไม่เจอข้อมูล
```
อยากใช้ Drop Down หลายที่ทำไง?

ถ้าอยากให้ Dropdown List ที่ทำมาสามารถ Copy ไปใช้ได้หลายๆ ที่ ต้องเปลี่ยนสูตรตรง IsFound เล็กน้อยนะครับ
```
แก้จาก
  IsFound =ISNUMBER(SEARCH($A$2,[Province]))
เป็น
 IsFound =ISNUMBER(SEARCH(CELL("contents"),[Province]))
```
หลักการคือ ฟังก์ชัน CELL หากไม่ระบุ Cell Reference จะอ้างอิงถึงช่องล่าสุดที่มีการแก้ไขได้ ดังนั้น CELL(“contents”) จะดึงข้อมูลจากช่องที่มีการแก้ไขล่าสุดไปใช้ใน SEARCH ได้ครับ

แหล่งอ้างอิง
- ถ้าเกิดใช้ VBA ก็จะทำคล้ายๆ เว็บนี้ได้ครับ https://trumpexcel.com/excel-drop-down-list-with-search-suggestions/
- ถ้ามี Excel 365 Insider ที่มี Dynamic Array ก็จะเขียนสูตรง่ายขึ้นแบบคลิปนี้ได้ด้วยเช่นกัน https://youtu.be/Z-h2UER3b_0
August 20, 2019
แจก Template กราฟเทียบ Skill Chart ปัจจุบัน vs Target (มีเน้นจุดพัฒนาหลัก)

เพื่อนๆ เคยเล่นเกมที่ตัวละครมีความสามารถหลากหลาย อาจมีเก่งบ้าง ห่วยบ้าง แล้วมันแสดงออกมาเป็นกราฟแบบ Skill Chart มั้ยครับ? กราฟนั้นใน Excel เรียกว่า Radar Chart ซึ่งไม่ค่อยมีใครใช้กันเท่าไหร่

แต่ผมลองเอามาปรับให้มันมีลูกเล่นมากขึ้นเล็กน้อย คือ ถ้า Skill ไหนที่ค่าปัจจุบันกับ Target ห่างกันมากที่สุดเป็นอันดับ 1 ผมจะทำให้มันเน้นออกมาเป็นตัวหนาสีแดงด้วย เราจะได้ดูได้ชัดขึ้นว่าจะต้องเน้นตัวไหน

สามารถเอาไปประยุกต์กับเรื่องอื่นๆ เช่น งาน HR หรือ Spec สินค้าได้ ลองดูได้นะ ^^

Download Template Skill Chart

ถ้าใครอยากลองเล่นบ้าง ผมทำ Template ให้แล้วครับ

Excel 2013 ขึ้นไป

โหลดได้ที่นี่ (รองรับการเพิ่ม Skill ได้เลย ต้องใช้ Excel 2013 ขึ้นไปเพื่อใช้ Feature ของ Data Label ที่ชื่อว่า Value from Cell)

Excel เก่ากว่า 2013

ใครมี Excel ต่ำกว่า 2013 ให้ โหลดตัวนี้ แต่ถ้าเพิ่ม Skill ก็ต้องไป Manual Text Box เองนะ

ลองทำของตัวคุณเองดูสิ

คนไหนลองโหลดไปทำของตัวเองแล้ว Comment รูปกราฟให้ผมดูหน่อยนะ มันไม่มีผิดไม่มีถูกอยู่แล้ว มีแต่ความตั้งใจล้วนๆ ^^

August 19, 2019
วิธีคำนวณระยะเวลาทำงาน แบบมีช่วงเวลาหยุดพัก

ปัญหาหนึ่งที่มีคนถามมาบ่อยๆ เกี่ยวกับเรื่องของการคำนวณเวลา นั่นก็คือ การคำนวณระยะเวลาทำงาน เมื่อเรารู้เวลาเริ่มงาน เวลาเลิกงาน และมีการกำหนดช่วงเวลาพักไว้ เช่น Break อาจมีหลายช่วงด้วย เช่น พัก1 ตอน 12:00-13:00 และ พัก2 ตอน 17:00-18:00 เป็นต้น

ในบทความนี้ผมจะขอเขียนอธิบายแนวทางในการคิด ซึ่งหากเข้าใจแล้วจะมีช่วงพักกี่ช่วงเราก็ไม่กลัวครับ

ในบทความนี้ผมเตรียมข้อมูลอยู่ในรูปแบบ Table เพื่อให้เห็นชื่อ Field ชัดๆ ตอนเขียนสูตรดังนี้ (โหลดไฟล์ได้ที่นี่)

แนวคิด

เนื่องจากมีการพักสองช่วงเวลา ดังนั้นผมจะขอแบ่งการทำงานออกเป็น 3 Part ดังนี้ เพื่อคำนวณเวลาการทำงานในแต่ละ Part จากนั้นค่อยเอามารวมกันอีกที

การคำนวณระยะเวลาใน Part 1

เวลาจบ : MIN([@เริ่มพัก1],[@เลิกงาน])

เวลาเริ่ม : MAX([@เริ่มงาน],0)

ที่เทียบกับเลข 0 เพราะว่าเวลา 0:00 คือการเริ่มต้นวัน ซึ่งมีค่าที่แท้จริงคือเลข 0 นั่นเอง

Part 1 ทำให้ไม่ติดลบด้วย Max กับ 0 :
=MAX(MIN([@เริ่มพัก1],[@เลิกงาน])-MAX([@เริ่มงาน],0),0)

การคำนวณระยะเวลาใน Part 2

เวลาจบ : MIN([@เริ่มพัก2],[@เลิกงาน])

เวลาเริ่ม : MAX([@เริ่มงาน],[@จบพัก1])

Part 2 ทำให้ไม่ติดลบด้วย Max กับ 0 :
=MAX(MIN([@เริ่มพัก2],[@เลิกงาน])-MAX([@เริ่มงาน],[@จบพัก1]),0)

การคำนวณระยะเวลาใน Part 3

เวลาจบ : MIN(1,[@เลิกงาน])

ที่เทียบกับเลข 1 เพราะว่าเวลา 24:00 ก็คือจบวันพอดี ซึ่ง 1 วันใน Excel มีค่าคือ 1 ครับ

เวลาเริ่ม : MAX([@เริ่มงาน],[@จบพัก2])

Part 3 ทำให้ไม่ติดลบด้วย Max กับ 0 :
=MAX(MIN(1,[@เลิกงาน])-MAX([@เริ่มงาน],[@จบพัก2]),0)

รวมระยะเวลา

รวมระยะเวลา =[@part1]+[@part2]+[@part3]

สรุปสูตรทั้งหมด

part1 =MAX(MIN([@เริ่มพัก1],[@เลิกงาน])-MAX([@เริ่มงาน],0),0)
part2 =MAX(MIN([@เริ่มพัก2],[@เลิกงาน])-MAX([@เริ่มงาน],[@จบพัก1]),0)
part3 =MAX(MIN(1,[@เลิกงาน])-MAX([@เริ่มงาน],[@จบพัก2]),0)
รวม =[@part1]+[@part2]+[@part3]

ถ้ามีพักแค่ทีเดียว เราอาจทำให้ เวลาพัก 1 กับ 2 เหมือนกันไปก็ได้นะครับ เช่น

หรือถ้ามีพัก 3 อีกเราก็ใช้หลักการนี้ในการวิเคราะห์ได้ครับ ว่า Part4 ควรจะเริ่มต้นที่ไหน ยังไงลองดูนะครับ ถ้าติดก็ Comment ได้เลย

คำนวณค่าแรง

สมมติว่าการทำงานแต่ละชั่วโมง มีค่าจ้างด้วย ชั่วโมงละ 100 บาท เราจะคิดค่าจ้างอย่างไร?

หากคิดว่าเราจะเอามาคูณกันตรงๆแบบนี้ ผลลัพธ์จะผิด เพราะว่าจริงๆ มันคนละหน่วยกัน
เช่น =[@รวมเวลาทำงานในวัน]*[@ค่าจ้างต่อชม]

หากเราปรับ format ของช่องรวมเวลาเป็น general ให้เห็นค่าที่แท้จริงจะชัดเจนขึ้น

ค่าที่ออกมาจริงๆ แล้วหน่วยเป็นวัน ไม่ใช่ชั่วโมง (10:10 คือ 0.42 วัน) ดังนั้นการคำนวณเราต้องแปลงหน่วยเป็นชั่วโมงโดยการคูณ 24 ด้วย จึงจะถูกต้อง

สรุป สูตร คือ : =[@รวมเวลาทำงานในวัน][@ค่าจ้างต่อชม]*24

ดังนั้น จำไว้เลยว่า เมื่อไหร่ที่มีการคำนวณเรื่องวันที่/เวลา… อย่าลืมคิดถึงเรื่องของค่าที่แท้จริงของมันนะครับ จะได้ไม่พลาด ^^

August 8, 2019
วิธีกำหนดให้ Pivot Table แสดงเฉพาะแถว/คอลัมน์ที่ต้องการ
คงปฏิเสธไม่ได้ว่า Pivot Table คือเครื่องมือสรุปผลตัวเลขที่ใช้งานง่ายและมีประโยชน์ที่สุดเครื่องมือนึงใน Excel และคงปฏิเสธไม่ได้เช่นกันว่ามันก็มีข้อจำกัดหลายอย่างที่ทำให้หลายคนรำคาญใจ

ข้อจำกัดหนึ่งที่หลายคนรำคาญมากคือ ในบางครั้งเราอยากจะเอาบางแถวหรือบางคอลัมน์ออกไปจาก Pivot Table ซะ ซึ่งหนึ่งในวิธีที่ทำได้คือทำการซ่อนแถวหรือคอลัมน์นั้นๆ ไปทั้งอันเลย ซึ่งอาจจะดูลูกทุ่งไปหน่อย วันนี้ผมเลยจะขอนำเสนออีกวิธีหนึ่งซึ่งดูโปรกว่า แต่จะทำได้เฉพาะ Excel 2010 ขึ้นไปเท่านั้นครับ เพราะมันต้องใช้ Power Pivot นะ!!

มาเริ่มใช้ Pivot Table Named Set กัน

วิธีนี้จะใช้เครื่องมือที่ชื่อว่า Named Set ซึ่งเป็น Feature ของ Power Pivot ที่ใช้ Data Model ดังนั้นการจะใช้คำสั่งนี้ได้ เราต้องมี Excel 2010 ที่มี Power Pivot Add-in หรือมี Excel 2013 ขึ้นไปถึงจะทำได้ครับ

วิธีการใช้งานคือต้องสร้าง Pivot Table แบบ Add เข้า Data Model ซะก่อน

จากนั้นหมุน Pivot Table ว่าต้องการ Field อะไรไว้ตรงไหนตามใจชอบ (ในรูปผมปรับ Pivot Layout เป็น Outline Form เพื่อให้เห็น Field แยกออกมาชัดๆ)

จากนี้ก็จะถึงตอนที่เราจะเอาบางแถวหรือบางคอลัมน์ที่ต้องการออกแล้ว (สมมติผมต้องการเอาออกดังรูปข้างล่างนี้)

ให้ไปที่ Field, Item & Set แล้ว Create Set Based on Row/Column แล้วแต่ว่าจะเอา Row ออก หรือเอา Column ออก (ในรูปผมจะเอาบางแถวออก ดังนั้นผมต้องเลือก Based on Row Items…)

จากนั้นเลือกคอลัมน์ที่ไม่ต้องการ แล้ว Delete ออกได้เลย

สังเกตว่าผลที่ออกมาในตัว Total รวมยังคงมีค่าเท่าเดิมก่อนจะเอา Row/Column ออกด้วยนะ

นอกจากนี้ยังมี Set ที่เราตั้งชื่อไว้ ไปโผล่แทนที่ Row/Column เดิม ด้วย

อย่างไรก็ตาม วิธีนี้ไม่สามารถเอา Field ที่เกี่ยวกับการทำ Set นั้นๆ ไปใส่ที่อื่นได้อีก เช่น ผมไม่สามารถลากสินค้าไปที่ Filter อีก เพื่อกรองสินค้าให้เหลือแค่ Dvd หนัง กับ ของเล่น เป็นต้น

แต่ถ้าผมเอา Field ที่ไม่เกี่ยวข้องกับ Set ไปใส่ มันก็ยังยอมให้เล่นอยู่ครับ เช่น ผมเอาลูกค้าไปใส่ที่ Filter ก็ยังได้ครับ

ยังแก้อะไรได้อีกมั้ย?

สำหรับคนที่อยากแก้ไขให้มัน Advance กว่านี้ ในส่วนของ Set เราสามารถเข้าไป Edit MDX เพื่อแก้ไขอะไรที่มัน Advance กว่านี้ได้ครับ

แต่บอกตามตรงว่าผมเองก็ยังไม่ได้ศึกษาเจ้า MDX นี่ให้ลึกลงไป ดังนั้นจะยังไม่กล้าสอนมากกว่านี้ครับ 555 ไว้ไปแอบศึกษามาก่อนแล้วจะเล่าให้ฟังนะ

เบื้องต้นถ้าใครสนใจลองไปอ่านได้ที่นี่ครับ https://support.office.com/en-us/article/create-a-named-set-in-an-olap-pivottable-8148265e-f843-4300-98f5-37c74f96d770

รู้แล้วจะเอาไปทำอะไรดี?

เป็นยังไงบ้างครับกับเทคนิคการใช้ Named Set อันนี้ เคยรู้กันบ้างมั้ยครับ? แล้วถ้าตอนนี้รู้แล้วจะเอาไปใช้ทำอะไรกัน มาบอกกันบ้างนะครับ ^^

สารบัญซีรีส์ Power Pivot
August 6, 2019
สารพัดวิธีดึงข้อความหลังตัวคั่นตัวที่สอง
หลายวันก่อนผมมีการ Post คำถามนี้ ไปใน เพจเทพเอ็กเซล ว่า จะดึงข้อความหลังเครื่องหมาย – ตัวที่สองได้อย่างไร?

ปรากฏว่ามีคนสนใจมาตอบเป็นจำนวนมากกกกก กว่าที่ผมคิดไว้เยอะเลย และมีหลายท่านได้นำเสนอวิธีที่น่าสนใจและควรค่าต่อการนำมาอธิบายต่อมาก

ดังนั้นผมจะขออนุญาตนำวิธีของแต่ละท่านที่ตอบมาอธิบายให้ละเอียดมากขึ้น เผื่อที่คนมาอ่านจะได้นำไปต่อยอดความรู้ของตัวเองได้ครับ (ลองไปดูใน Post ได้นะครับว่าใครตอบแบบไหนมา หลายคนก็ตอบวิธีเดียวกันครับ)

ขอเริ่มจากวิธีที่ผมคิดว่าง่ายที่สุดก่อนละกันนะครับ

วิธีที่ 1 : ใช้ Flash Fill

หลักการ : Excel จะพยายามหา Pattern ของสิ่งที่เราใส่เป็นตัวอย่าง แล้วเลียนแบบสิ่งนั้นให้โดยอัตโนมัติ (แต่ไม่ใช่สูตร หากข้อมูลต้นทางเปลี่ยนต้องกดคำสั่ง Flash Fill ใหม่)

ข้อจำกัด : ใช้ได้ตั้งแต่ Excel 2013 ขึ้นไป

แนะนำมาโดย : Bob Pytnst, Noppadol Rattanawisadrat, Taekuza Meathayavat

วิธีทำ : พิมพ์ตัวอย่างสิ่งที่อยากได้ เช่น UITR ลงไปในช่อง B2 กด Enter

พอเราอยู่ที่ช่อง B3 แล้ว จากนั้นกด Flash Fill
ที่ Data –> (Data Tool) Flash Fill ที่เป็นรูปสายฟ้า
หรือกด Ctrl+E ก็ได้ครับ (ผมชอบกดแบบนี้ ง่ายกว่าเยอะ)

วิธีที่ 2 : ใช้ Text to Column แบบ Delimited

หลักการ : แบ่งข้อมูลจากคอลัมน์เดียว กลายเป็นหลายๆ คอลัมน์ โดยมีตัวคั่นที่ชัดเจน คือ เครื่องหมาย – นั่นเอง ( แต่ไม่ใช่สูตร หากข้อมูลต้นทางเปลี่ยนต้องกดคำสั่ง Text to Column ใหม่)

แนะนำมาโดย : Chatchai Sanguanwong, Tee Jamjam, Gus Pholsap, Natdanai Choksakulsub, Bob S. Wibulseth

วิธีทำ Copy ข้อมูลออกมาเป็นอีกคอลัมน์นึงก่อน จากนั้นไปที่

Data –> (Data Tools) Text To Column

จากนั้นเลือก Delimiter (ตัวแบ่ง) เป็น Other แล้วพิมพ์ – ลงไปในช่องว่าง แล้ว Finish ได้เลย

จากนั้น ให้ลบ Cell ที่ไม่เกี่ยวข้องออก ถ้าต้องการ

วิธีที่ 3 : ใช้ Power Query Extract Data

หลักการ : ใช้ Power Query ดึงเอาข้อมูลจากตัวคั่น – ตัวขวาสุุด (หากข้อมูลต้นทางเปลี่ยนแปลงสามารถกด Refresh ได้)

ข้อจำกัด : ต้องมี Power Query รุ่นใหม่ (Excel 365) หรือ Power BI Desktop

แนะนำมาโดย : เทพเอ็กเซล

วิธีทำ โหลดข้อมูลที่ต้องการเข้า Power Query ก่อน (เช่น Get Data from Table/Range)

จากนั้น Add Column –> Extract –> Text After Delimiter ตามรูปได้เลย

กด ok แล้วจบเลย

สรุป M-Code
```
let
     Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
     #"Inserted Text After Delimiter" = Table.AddColumn(Source, "Text After Delimiter", each Text.AfterDelimiter([code], "-", {0, RelativePosition.FromEnd}), type text)
 in
     #"Inserted Text After Delimiter"
```
วิธีที่ 4 : ใช้ Power Query Split

หลักการ : สำหรับคนที่มี Power Query version เก่า จะไม่มีเครื่องมือ Extract After Delimiter แบบตัวอย่างที่แล้ว

ดังนั้นเราจะมาใช้ Power Query Split แทน ซึ่งสามารถ Split ข้อมูลจากตัวคั่น – ตัวขวาสุุดได้ครับ

แนะนำมาโดย : เทพเอ็กเซล

ข้อจำกัด : ต้องมี Power Query (Excel 2010 ขึ้นไป) หรือ Power BI Desktop

วิธีทำ : โหลดข้อมูลที่ต้องการเข้า Power Query ก่อน (เช่น Get Data from Table/Range)

จากนั้น Duplicate Column เอาไว้ก่อน

ต่อไปเราก็จะทำการ Split Column ออกมา โดยใช้ Delimiter แล้วเอาจากด้านขวาสุด

จะได้ส่วนที่ต้องการออกมาเลย

กด Remove Column ที่ไม่ต้องการเป็นอันจบ

สรุป M-Code
```
let
     Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
     #"Duplicated Column" = Table.DuplicateColumn(Source, "code", "code - Copy"),
     #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "code - Copy", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, true), {"code - Copy.1", "code - Copy.2"}),
     #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"code - Copy.1"})
 in
     #"Removed Columns"
```
วิธีที่ 4 : ใช้สูตร FIND เพื่อหาตำแหน่ง – ตัวที่สอง

หลักการ : FIND สามารถหาตำแหน่งของคำที่ต้องการได้ และระบุได้ว่าจะให้เริ่มหาตั้งแต่ตัวไหน

ดังนั้นถ้าเราเริ่มหาตั้งแต่หลังจากที่เจอตัวแรกไป (ด้วยการเอาตำแหน่งที่เจอตัวแรกไป +1) ก็จะได้ตำแหน่งของ – ตัวที่สองครับ

แนะนำมาโดย : Apichot Hongkham, Aussanee Sripirom, Jason Beer, Vorrachai Rojanaporntip, Nop Noppon, Guy SN, Parakorn Tantapon, Wittaya Chainim, Worachai Pathumapa

วิธีทำ : ในช่อง B3 เขียนสูตรว่า
```
=RIGHT(A3,LEN(A3)-FIND("-",A3,FIND("-",A3)+1))
```
มาดูทีละ Part แบบทีละ Step จากซ้ายไปขวาครับ

วิธีที่ 5 : ใช้สูตร SUBSTITUTE ช่วยหาพิกัดของ – ตัวที่สอง

หลักการ : หาตำแหน่ง – ตัวที่สองให้ได้ แล้วใช้ RIGHT ดึงข้อความออกมา
ซึ่งสูตรนี้จะใช้ SUBSTITUTE เปลี่ยน – ตัวที่สองให้กลายเป็นเครื่องหมายแปลกๆ ก่อน เช่น | แล้วค่อยใช้ FIND หาตำแหน่งของ | อีกที ที่เหลือก็ไม่ใช่เรื่องยากแล้ว

ทั้งนี้เพราะ =SUBSTITUTE(text,old_text,new_text,instance_num) ยอมให้เราระบุได้ว่าจะแทนข้อความเดิมคำที่เท่าไหร่ในส่วนของ instance_num นั่นเองครับ

ซึ่งวิธีนี้จะสะดวกในกรณีที่เครื่องหมาย – มีเยอะๆ เช่น อยากจะได้ – ตัวที่ 5 เราก็สามารถระบุได้เลยด้วย =SUBSTITUTE(text,”-“,”|”,5) โดยไม่ต้องเขียน FIND 5 รอบครับ

แนะนำมาโดย : เทพเอ็กเซล

วิธีทำ : ในช่อง B3 เขียนสูตรว่า
```
=RIGHT(A3,LEN(A3)-FIND("|",SUBSTITUTE(A3,"-","|",2)))
```
มาดูทีละ Part แบบทีละ Step จากซ้ายไปขวาครับ

วิธีที่ 6 : ใช้ REPLACE ในการตัดข้อความส่วนหน้าออกไปซะ เหลือแต่ข้างหลัง

หลักการ : หาตำแหน่ง – ตัวที่สองให้ได้ก่อน จากนั้นใช้ REPLACE ตัดข้อความที่ไม่ต้องการออก โดยเอาออกตั้งแต่ตำแหน่งแรก เป็นจำนวนตัวอักษรเท่ากับตำแหน่งของ – ตัวที่สอง แล้วแทนด้วย “”

แนะนำมาโดย : Pichai Tee

วิธีทำ : ในช่อง B3 เขียนสูตรว่า
```
=REPLACE(A3,1,FIND("-",A3,FIND("-",A3)+1),"")
```
มาดูทีละ Part แบบทีละ Step จากซ้ายไปขวาครับ

วิธีที่ 7 : ใช้ MID เพื่อเลือกข้อความส่วนที่ต้องการ

หลักการ : หาตำแหน่ง – ตัวที่สองออกมาแล้วใช้ MID ดึงข้อความหลังจากนั้นมาซะ ซึ่งหากเราใส่จำนวนตัวอักษรที่ต้องการจะดึงด้วย MID ยาวมากจนเกินตัวอักษรที่มีก็ไม่เป็นไร ดังนั้นในที่นี้เลยดึงออกมาด้วย LEN ซะเลย (แม้จะเริ่มต้นที่ตำแหน่งหลัง – ตัวที่สองแล้วก็ตาม)

แนะนำมาโดย : Manop Udom

วิธีทำ : ในช่อง B3 เขียนสูตรว่า
```
=MID(A3,FIND("-",A3,FIND("-",A3)+1)+1,LEN(A3))
```
มาดูทีละ Part แบบทีละ Step จากซ้ายไปขวาครับ

วิธีที่ 8 : ใช้ TRIM + MID ตัดเอาส่วนที่ต้องการ

หลักการ : วิธีนี้ค่อนข้างแปลกมากๆ ครับ คือพยายามใช้ช่องว่างจำนวนมหาศาลมาใส่แทน – แต่ละตัว แล้วใช้ MID เลือกส่วนที่ต้องการมา (คัดให้เริ่มและจบที่ช่องว่างระหว่างคำ) จากนั้นใช้ TRIM เพื่อตัดช่องว่างส่วนเกินออก

แนะนำมาโดย : Bo Rydobon

วิธีทำ : ในช่อง B3 เขียนสูตรว่า
```
=TRIM(MID(SUBSTITUTE(A3,"-",REPT(" ",40)),80,40))
```
มาดูทีละ Part แบบทีละ Step จากซ้ายไปขวาครับ

อย่างไรก็ตาม วิธีนี้ต้องแทนจำนวนช่องว่างให้เยอะเมื่อเทียบกับจำนวนข้อความแต่ละ part นะครับ ไม่งั้นผลอาจผิดได้ เช่น ถ้าข้อความยาวมาก เราอาจใส่เลขเยอะหน่อยแบบนี้ก็ได้
```
=TRIM(MID(SUBSTITUTE(A3,"-",REPT(" ",200)),400,200))
```
ทั้งนี้เพื่อให้ MID สามารถเลือกคำระหว่างช่องว่างออกมาให้ได้นั่นเองครับ

วิธีที่ 9 : ใช้ VBA สร้างฟังก์ชัน Split ขึ้นมาเอง

หลักการ : Excel ในปัจจุบันยังไม่มีฟังก์ชัน SPLIT แบบเดียวกับ Google Sheets แต่ว่าเราก็พอหาทางแก้ไขได้โดยการเขียนฟังก์ชันใน VBA มาใช้เองครับ

แนะนำมาโดย : Bo Rydobon

วิธีทำ : กด Alt+F11 เพื่อเปิด VBA Editor ขึ้นมาครับ

จากนั้น คลิ๊กขวาเพื่อ Insert Module ใหม่ดังรูป

จากนั้นใส่ code ดังนี้
```
Function GetSplit(text As String, dlm As String, pos As Integer)
 GetSplit = split(text, dlm)(pos - 1)
 End Function
```
**ผมขอดัดแปลง Code เล็กน้อย จากที่เดิม comment มาจริงๆ ว่า
```
Function split2(t)
split2 = Split(t, "-")(2)
End Function
```
เพื่อให้สะดวกขึ้นว่าจะเอาตัวคั่นตัวไหน และเอาข้อมูลตัวที่เท่าไหร่นะครับ

จากนั้นสามารถเรียกใช้ฟังก์ชันได้เลย เช่น =GetSplit(A2,”-“,3) จะแบ่งข้อมูลด้วย – จากนั้นดึงเอาส่วนที่ 3 มาครับ

ถ้าใช้วิธีนี้แล้วจะ Save ไฟล์ไว้ อย่าลืม Save เป็น .xlsm หรือไม่ก็ .xls นะครับ (ไม่งั้น VBA จะหายหมดนะ)

สรุปวิธีดึงข้อความ

บทความนี้ยาวมาก มีใครอ่านมาจนจบบ้างมั้ย 555

ใครที่อ่านมาถึงตรงนี้น่าจะเห็นแล้วว่า ปัญหาหนึ่งๆใน Excel มันมีทางแก้เยอะมากๆ นี่ขนาดแค่เรื่องดึงข้อความจากส่วนที่ต้องการยังมีวิธีหลากหลายขนาดนี้เลย

อย่างไรก็ตามหลักการแก้ปัญหานี้มีอยู่ 2 ส่วนหลักๆ คือ
1. หาตำแหน่งที่เป็นจุดแบ่งที่เหมาะสม (เช่น FIND ซ้อน FIND, SUBSTITUTE ตัวที่กำหนด แล้ว FIND)
2. ดึงส่วนของข้อความที่ต้องการ (จะใช่ RIGHT, MID หรืออะไรก็แล้วแต่)
  ซึ่งจริงๆ แล้วฟังก์ชัน MID ก็สามารถใช้แทน LEFT และ RIGHT ได้อยู่แล้วอ่ะนะ เพราะมันเป็นตัวที่มีความ Flexible มากกว่า เนื่องจากกำหนดได้ทั้งจุดเริ่มต้น และจำนวนตัวอักษรที่ต้องการ
ซึ่งแต่ละส่วนก็แก้ปัญหาได้หลายแบบ ดังนั้นหากเราเปิดใจให้กับวิธีใหม่ๆ เราก็จะเรียนรู้และพลิกแพลงสิ่งต่างๆ ได้อีกเยอะเลยครับ

นอกเหนือจากวิธีดังกล่าว ยังมีอีกหลายท่านที่มาแนะนำการใช้เครื่องมืออื่นๆ ซึ่งต่างก็เจ๋งๆ กันทั้งนั้น เราเองก็น่าจะลองเปิดใจ ศึกษาเครื่องมือเหล่านี้ด้วยเช่นกันครับ

เช่น Google Sheets (Qiuxuan Ank, Jonathan Ablanida)

R, Python (Kasidis Satangmongkol)

สุดท้ายนี้ขอขอบคุณทุกคนที่เข้ามา Comment ใน Post ที่ผมถามคำถามไปด้วยนะครับ ถือว่าเป็นการได้แลกเปลี่ยนความรู้กันได้ดีมากๆ และคนอ่านก็น่าจะเกิดประโยชน์มากด้วยเช่นกัน ^^

เดี๋ยวบทความถัดไป จะเป็นการอธิบายเรื่องที่ยากกว่านี้ นั่นคือ การดึงตัวเลขออกมาจากข้อความนั่นเอง จะเป็นยังไง รอติดตามได้เลยครับ!!
August 1, 2019
วันนี้ วันนั้น วันไหน? : เรื่องลับๆเกี่ยวกับวันที่ใน Excel
เรื่องวันที่ใน Excel นั้นมีความลึกซึ้งยิ่งนัก หากเราเข้าใจแบบผิวเผิน ก็อาจจะทำงานผิดพลาดโดยไม่รู้ตัวก็ได้ ลองมาดูกันครับ ว่าคุณรู้เรื่องพวกนี้แล้วหรือยัง?

ประเด็นที่ 1 : การกรอกวันที่ปัจจุบัน

วิธีกรอกวันที่ปัจจุบันลงไปใน Excel มีง่ายๆ 2 วิธีด้วยกัน
1. ใช้ฟังก์ชัน =TODAY()
2. กดคีย์ลัด Ctrl+; (ปุ่ม ว)
ซึ่งมันออกมาเป็นวันที่ปัจจุบันทั้งคู่… แล้วทั้ง 2 วิธีต่างกันตรงไหน? หากใช้ผิด ผลที่ได้อาจไม่ใช่อย่างที่คิดนะครับ…

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

แต่ถ้าหากใช้คีย์ลัด Ctrl+; (ปุ่ม ว) มันจะเป็นการ Stamp ค่าวันที่ลงไปใน Cell เลย เหมือนกับการที่เราพิมพ์ลงไปเอง แค่สะดวกขึ้น ดังนั้นวิธีนี้ถึงจะเอาไฟล์ไปเปิดในวันถัดไป ค่าก็จะไม่เปลี่ยนครับ

ประเด็นที่ 2 : วิธีกรอกวันที่ใน Excel

สมมติว่าวันที่เราต้องการกรอกลงไปใน Excel คือวันอังคาร ที่ 25 มิถุนายน พ.ศ. 2562 นะ

คุณจะกรอกวันที่ดังกล่าวลงไปใน Excel ยังไงครับ? (เราใส่วันที่เป็น / หรือ – ได้ทั้งคู่นะ)

ก) 25/6/2562 หรือ 25-6-2562

ข) 25/6/2019 หรือ 25-6-2019

หากใครตอบ ก. ก็ผิดทันทีครับ เพราะวิธีกรอกที่ถูกต้องต้องกรอกเป็น ค.ศ. เสมอ (ถ้าเราไปกรอกเป็น 25/6/2562 Excel จะเข้าใจว่าเป็น ค.ศ. 2562 ซึ่งเป็นวันในอนาคตอีก 543 ปีข้างหน้า ซึ่งวันจันทร์-อาทิตย์ก็ไม่ตรงกับปัจจุบัน แถมบางปีอาจมี 29 กพ. ไม่ตรงกันด้วย (ปี ค.ศ. ที่หาร 4 ลงตัวจะมี 29 กพ. เกือบ 100%)

ส่วนใครตอบว่า ข ก็ยังต้องลุ้นต่อว่าจะถูกหรือผิดอยู่ดี…

วิธีกรอกวันที่แบบถูกต้องให้ง่ายที่สุด คือ ให้กรอกเป็น ปี/เดือน/วัน (ไม่ว่าคอมพ์จะตั้งค่าแบบไหนก็จะรู้จักวันที่รูปแบบนี้)
หรือไม่ก็กรอกตามตัวอย่างรูปแบบวันที่ปัจจุบันที่แสดงขึ้นมาจาก =TODAY() หรือ Ctrl+; นั่นแหละครับ ว่าเป็น วัน/เดือน/ปี หรือ เดือน/วัน/ปี

สิ่งที่ผมจะบอกก็คือ การกรอก 25/6/2019 เครื่องคอมพ์ของคนไทยส่วนใหญ่จะวันที่ออกมาชิดขวาของ Cell … แต่บางเครื่องกลับชิดซ้าย…

ถ้ากรอกแล้ววันที่ออกมาชิดซ้าย Cell แสดงว่าผิดชัวร์ เพราะแสดงว่า Excel ไม่ได้มองว่าข้อมูลนั้นเป็นวันที่แต่มองเป็นข้อความ (หากข้อมูลเป็นวันที่จริง Excel จะตีความเป็นตัวเลข ซึ่งจะชิดขวา)

แล้วทำไมพิมพ์แบบเดียวกันแท้ๆ บางเครื่องได้ บางเครื่องไม่ได้??

ก็เพราะเรื่องวันที่ มันขึ้นอยู่กับการตั้งค่าใน Control Panel ในส่วนของ Regional Setting (รูปลูกโลก) ว่าตั้งค่าเป็น Format ของประเทศอะไร ?

ถ้าตั้งค่าผิดจากที่คิด เช่น ตั้งเป็น English (United States) มันจะเป็น เดือน/วัน/ปี
ซึ่งถ้าเราใส่ 25/6/2019 มันจะนึกว่าเป็นเดือนที่ 25 วันที่ 6 ซึ่งไม่มี… (มันเลยไม่รู้จักไง)

ซึ่งผมขอแนะนำให้ตั้งเป็น Thai (Thailand) ไม่ก็ English (United Kingdom) ซึ่งจะทำให้วันที่ออกมาเป็น วัน/เดือน/ปี ซึ่งเป็นแบบที่คนไทยถนัดครับผม

ประเด็นที่ 3 : กรอกวันที่เป็น ค.ศ. แต่อยากแสดงปีเป็น พ.ศ. ทำไง?

ถ้ากรอกวันที่เป็น ค.ศ. ไปแล้ว เช่น 25/06/2019 แต่อยากให้แสดงออกมาเป็น พ.ศ. มันก็ง่ายมากๆ เลยครับ นั่นคือ ให้ไปปรับที่ Number Format นั่นเอง

พอปรับ Number Format แล้ว รูปแบบที่เรามองเห็นจะเปลี่ยนไป แต่ค่าจริงๆ ยังเหมือนเดิมครับ

ประเด็นที่ 4 : ค่าที่แท้จริงของวันที่ใน Excel

สมมติเรากรอกวันที่ 25/6/2019 ลงไปใน Cell ถูกต้องแล้ว…

สมมติว่าผมอยากได้เลขวัน ว่าเป็นวันที่เท่าไหร่ (ในที่นี้ต้องการเลข 25) หรือ อยากได้ปีว่าเป็นปีอะไร (ต้องการเลข 2019)

คนที่ไม่เข้าใจเรื่องวันที่อาจลองใช้ฟังก์ชัน LEFT(วันที่,2) เพื่อเอาข้อมูล 2 ตัวแรก หรืออาจจะใช้ RIGHT(วันที่,4) เพื่อเอาข้อมูล 4 ตัวท้าย ซึ่งมันผิดนะครับ

เราไม่สามารถใช้ LEFT/RIGHT เพื่อดึงส่วนวันหรือปีออกมาจากข้อมูลที่เป็นวันที่อย่างที่คิดได้ เนื่องจากค่าที่แท้จริงของวันที่เป็นเพียงตัวเลขธรรมดา
- ซึ่งจะเริ่มตั้งแต่วันแรกที่ Excel รู้จัก คือ วันที่ 1/1/1900 แทนด้วยเลข 1
- วันถัดมา คือ คือ วันที่ 2/1/1900 แทนด้วยเลข 2
- โดยเลขจะเพิ่มขึ้นเรื่อยๆ วันที่ในยุคปัจจุบัน จะถึงเลข 40000 กว่าๆ แล้ว
ถ้าไม่เชื่อลองปรับ Format ของข้อมูลวันที่เป็น General ดู จะเห็นว่าวันที่ 25/06/2019 เป็นเลข 43641 ซึ่งก็คือ ค่าที่แท้จริงครับ

แต่ถ้าคุณกรอกวันที่เป็น ปี พ.ศ. เช่น 25/06/2562 แล้วปรับเป็น General จะได้เลขออกมา 2 แสนกว่าๆ ซึ่งไม่ใช่ 4 หมื่นกว่าๆ แสดงว่าผิดชัวร์ (2 แสนกว่าคือวันที่ในอนาคต ในอีก 543 ปีข้างหน้า)

และนี่ก็คือสาเหตุว่าทำไม LEFT(วันที่,2) กลับได้ 43 จาก 43641 แทนจะได้ 25 และ RIGHT(วันที่,4) กลับได้ 3641 จาก 43641 แทน 2019

และการที่ Excel เก็บค่าที่แท้จริงของวันที่เป็นตัวเลขธรรมดา มันทำให้เราสามารถส่งไฟล์ Excel ของเราไปให้เพื่อนต่างประเทศ (ที่ตั้งค่า Region ไม่เหมือนกับเรา) แล้วข้อมูลไม่เพี้ยนนั่นเอง (แค่อาจแสดงค่าออกมาคนละ Format แต่มีค่าที่แท้จริงตรงกัน)

ประเด็นที่ 5 : การคำนวณเกี่ยวกับวันที่และเวลา
- วันที่ คือ จำนวนเต็ม
- เวลา คือ ทศนิยม (ที่เป็นสัดส่วนของวัน เช่น 12:00 คือ 0.5 วัน เนื่องจาก 24:00 คือ 1 วัน)
หากต้องการเลื่อนวันที่ไป 7 วัน ก็สามารถเอาค่าวันที่ไป +7 ได้เลย

และหากมีข้อมูลแยกกันคนละช่อง เราสามารถเอามารวมกันง่ายๆ ได้ด้วยการบวก (ไม่ใช่เอามา & เชื่อมกันนะ)

ถ้าเรามีข้อมูลรวมวันที่และเวลาอยู่ในช่องเดียวกัน เราก็แยกวันที่ออกมาด้วยวิธีการหาจำนวนเต็ม เช่น ใช้ INT มาช่วย และ สามารถใช้ฟังก์ชันต่างๆ เพื่อดึงจากข้อมูลที่เป็นวันที่และ/หรือเวลาได้เลยดังนี้

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

ถ้าข้อมูลมีแต่เวลา ก็สามารถเอามาลบกันได้เช่นกัน แต่ต้องมีความเข้าใจด้วยว่า เลขที่ออกมามีค่าที่แท้จริงคืออะไร?

เช่นในรูปข้างบนนี้ เราเห็นผลหน้าตาออกมาเป็น 3 ชั่วโมง แต่หากลองปรับ Format เป็น general จะเห็นว่าค่าที่แท้จริงคือ 0.125 ซึ่งหน่วยเป็นวันต่างหาก

ดังนั้นจะเอาไปคำนวณอะไรต่อก็ระวังเรื่องหน่วยด้วยนะครับ เช่น จะเอาไปคูณค่าใช้จ่ายต่อชั่วโมง ก็ต้องแปลงหน่วยให้ตรงกันซะก่อน

นอกจากนี้แล้ว ก็มีฟังก์ชันเกี่ยวกับวันที่ที่ควรรู้อีกมากมาย ลองดูฟังก์ชันพื้นฐานได้ที่นี่

ประเด็นที่ 6 : วิธีการคำนวณวันของสัปดาห์(วันจันทร์-อาทิตย์)

ปกติแล้วถ้าเรามีข้อมูลวันที่อยู่ แล้วอยากรู้ว่าเป็นวันอะไร (จ-อา) ใน Excel ก็จะมีฟังก์ชันที่ทำหนี้ที่นี้อยู่ นั่นก็คือ WEEKDAY

แต่เจ้า WEEKDAY ออกแบบมาเอาใจคนทั่วโลกที่มีความหลากหลายมากๆ ก็เลยดันมี Option ใน Input ที่ชื่อว่า return_type โดยให้เลือกว่าจะให้วันจันทร์-อาทิตย์ออกมาเป็นตัวเลขรหัสแบบไหน เช่น
- โหมด 1 : ให้วันอาทิตย์เป็น 1 และ เสาร์เป็น 7
- โหมด 2 : ให้วันจันทร์เป็น 1 และ อาทิตย์เป็น 7
ซึ่งมันอาจทำให้เกิดปัญหาเรื่องความชอบหรือความเข้าใจแต่ละคนไม่ตรงกันก็ได้ เพราะมันดันออกมาเป็นตัวเลข ไม่ใช่วันที่เป็นข้อความเช่น Monday หรือ จันทร์ เป็นต้น

วิธีแก้ที่สามารถทำได้แบบหนึ่งคือ ทำตารางอ้างอิงว่า Code ตัวเลข 1-7 ของเราคือวันอะไร จากนั้นก็ใช้พวก VLOOKUP ดึงข้อมูลชื่อวันมาแสดงก็ได้

แต่ผมจะขอแนะนำอีกวิธีหนึ่งง่ายๆ นั่นก็คือการเปลี่ยน Custom Number Format เป็น dddd ครับ

พอเราปรับ Number Format เป็น dddd ก็จะแสดงข้อความออกมาเป็นชื่อวันเลย แต่ว่ามันก็เป็นแค่ Format หรือการแสดงผลนะครับ ค่าที่แท้จริงยังคงเป็นตัวเลขธรรมดาๆ อยู่ (สังเกตว่าคำว่าอังคารดันชิดขวา บ่งบอกว่าจริงๆ คือ ตัวเลขนะ)

Tips: เราสามารถเอาค่าดังกล่าวไปบวก 1 แล้วจะได้เป็นคำว่า พุธ ด้วยซ้ำ

ถ้าเราลองใช้ LEFT ดูจะเห็นว่าได้ออกมาเป็นเลข 4 เพราะค่าที่แท้จริง คือ 4 หมื่นกว่าๆ ซึ่งทำให้เกิดปัญหาว่าเราไม่สามารถเช็คข้อมูลได้สะดวกนักว่า Cell นั้นมีค่าตามที่เราต้องการรึเปล่า?

วิธีแก้ไข ถ้าอยากให้ชื่อวันเป็นข้อความจริงๆ ให้ใช้ฟังก์ชัน TEXT แล้วใส่ format เป็น dddd มาช่วยแทนครับ เช่น

แบบนี้จะเห็นว่าคำว่าอังคารจะกลายเป็น Text จริงๆ ไปแล้ว และอยู่ชิดซ้ายด้วย (ถ้า LEFT 1 ตัวอักษร ก็จะได้ อ แล้วครับ)

นอกจาก dddd แล้ว ก็ยังสามารถใส่ได้อีกหลายแบบเช่น
- ddd = ชื่อย่อของวัน
- dddd = ชื่อเต็มของวัน
- mmm = ชื่อย่อขอเดือน
- mmmm = ชื่อเต็มของเดือน
ประเด็นที่ 7 : การกำหนดภาษาของวันที่

ทีนี้ยังมีประเด็นจุกจิกอีกเล็กน้อย นั่นคือ บางเครื่องใส่ dddd แล้วออกมาเป็น “อังคาร” แต่บางเครื่องก็ออกมาเป็น “Tuesday”

ที่เป็นแบบนี้เพราะมันจะยึดตามภาษาที่ตั้งไว้ใน Control Panel ที่ผมบอกตอนแรกนั่นแหละครับ เครื่องผมตั้งค่าเป็น Thai (Thailand) ก็เลยออกมาเป็น “อังคาร” ซึ่งถ้าเอาไฟล์ไปเปิดในเครื่องคนอื่นก็อาจขึ้นเป็นภาษาอื่น ทำให้ไม่มีมาตรฐาน ซึ่งอาจยากต่อการเขียนสูตรอีกแล้ว

ทางแก้ไขคือ จะต้องมีการระบุภาษาให้เจาะจงลงไปใน code ของ Custom Number Format เลย เช่น จะเอาภาษาอังกฤษนะ ไม่ว่าจะเปิดในเครื่องไหน ก็จะเป็นภาษาอังกฤษเหมือนกันหมดครับ

วิธีในการดู Code คือ ให้ไปเลือก Format วันที่มาซักอันนึงทีมีข้อความ เช่นอันนี้ผมเลือกจาก Locale ภาษาอังกฤษ (English UK)

จากนั้นค่อยไปกดที่ Custom คราวนี้เราจะเห็น Code รหัสภาษาออกมาด้วย

คราวนี้ถ้าผมแก้เป็น [$-en-GB] นำหน้า ก็จะได้เป็น Tuesday แล้วครับ

ลองไปทำภาษาไทยดูก็จะเป็นแบบนี้ [$-th-TH,107] นำหน้า

ประเด็นที่ 8 : ถ้าข้อมูลไม่ใช่วันที่แบบถูกต้อง จะแก้ยังไง?

แทนที่จะได้ข้อมูลวันที่มาเป็นวันที่จริงๆ (ที่มีค่าที่แท้จริงเป็นเลข 4 หมื่นกว่าๆ) กลับได้ข้อมูลมาผิดๆ เลย เช่น มาเป็นตัวเลข หรือ ข้อความ เราจะทำไงดี?

ผมแนะนำว่าโดยทั่วไปคือ ให้แยกข้อมูลออกมาทีละส่วนเป็น ปี เดือน วัน ด้วย LEFT MID RIGHT ตามความเหมาะสม (คราวนี้ทำได้เพราะข้อมูลเป็นเลขหรือข้อความธรรรมดา) จากนั้นค่อยเอามารวมร่างกันด้วยฟังก์ชัน DATE ครับ

นอกจากนี้ อาจใช้เทคนิคเปลี่ยน Custom Number Format ให้ใส่ขีดคั่น แล้วบังคับแปลงเป็นตัวเลขด้วยการคูณ 1 แล้วค่อยเปลี่ยน Format เป็นวันที่ แบบนี้ก็ได้ครับ

สรุป

เรื่องวันที่เป้นเรื่องที่ควรทำให้ถูกแต่แรก ไม่งั้นจะปวดหัวตอนแก้ไข โดยเฉพาะถ้าต้องรวบรวมไฟล์จากคนหลายคน แล้วแต่ละคนดันกรอกวันที่ถูกบ้างผิดบ้าง…

ถ้าเจอปัญหาที่วันที่ปนกันหลายรูปแบบลองทำแบบนี้ดูครับ

หากทุกคนทำความเข้าใจบทความนี้ได้ทุกประเด็นแล้ว ผมเชื่อว่าคุณจะทำงานกับวันที่ได้ดีขึ้นมากครับ ใครมีปัญหาสงสัยอะไรก็ Comment สอบถามไว้ได้เลยนะครับ ยินดีตอบทุกคำถามครับ
June 27, 2019

รวมคีย์ลัด Excel ที่ใช้บ่อย พร้อมเทคนิคการจำ

มีหลายเว็บที่ได้รวบรวมคีย์ลัด Excel เอาไว้มากมาย แต่น้อยที่นักจะบอกเทคนิคการจำด้วย ในบทความนี้ผมจึงขอนำเสนอ คีย์ลัด + เทคนิคการจำ ที่จะช่วยให้คุณจำคีย์ลัดใน Excel ไปได้อีกนานแสนนาน

ก่อนอื่นต้องบอกว่าบทความนี้ยาวนะครับ แต่รับรองว่าคุณจะได้อะไรดีๆ ไปเยอะมากๆ แน่นอน

ทำไมต้องใช้คีย์ลัด Excel?

รู้หรือไม่ว่า คุณสามารถทำงานใน Excel ได้เร็วขึ้นอีก ถ้าคุณรู้จักการใช้คีย์ลัดใน Excel และนี่ก็คือสิ่งที่มือโปรทั้งหลายใช้กัน ซึ่งนอกจากจะทำให้ทำงานเร็วขึ้นแล้ว ยังเท่มากๆ อีกด้วย!

แน่นอนว่าการใช้ Mouse คลิ๊กก็สามารถทำงานให้สำเร็จได้เช่นกัน แต่ว่าการลาก Cursor ไปมา ก็มีต้นทุนของเรื่องเวลามากกว่าการใช้ Keyboard ไม่ว่าจะเป็นแค่ครั้งละ 1-2 วินาทีก็ตาม ซึ่งถ้าทำอย่างนี้วันละหลายๆ รอบ รวมกันก็หลายนาทีอยู่นะ

อย่างไรก็ตาม การใช้คีย์ลัดบน Keyboard นั้น เราต้องจำคำสั่งนั้นได้ เราถึงจะใช้งานได้เร็ว ดังนั้นมันจึงต้องมีความพยายามในการจำพอสมควร และนี่คือหน้าที่ของบทความนี้

หมายเหตุ : ในบทความนี้สอนใช้คีย์ลัดของ Windows PC นะครับ ถ้าใช้ Mac แนะนำให้ไปดูที่เว็บ ExcelJet แทน

คลิปสอนคีย์ลัดเบื้องต้น

ทำไมต้องมีเทคนิคการจำ?

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

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

ซึ่งเคล็ดลับการจำที่ดีมากๆ คือ ต้องสร้างความเชื่อมโยงกับสิ่งที่เรารู้อยู่แล้ว หรือไม่ก็ต้องเป็นเรื่องแปลก ประหลาด จนทำให้เกิดอารมณ์ที่รุนแรง เช่น ตกใจ Surprise มากๆ เป็นต้น

Arctic vs Antarctic ?

ตัวอย่างวิธีจำอันนึงที่ผมอ่านเจอแล้วชอบมาก คือ ชื่อทวีป Arctic กับ Antarctic ซึ่งผมมักจะงงเสมอว่าอันไหนมันขั้วโลกเหนือ อันไหนมันขั้วโลกใต้? … อยู่มาวันหนึ่งผมไปอ่านเจอว่า

Antarctic = Ant มันคือมดที่เดินบนพื้นดิน เราต้องก้มมองต่ำ = ใต้
Arctic = Arc คือส่วนโค้งของเพดาน/ประตู ที่เราต้องแหงนหน้ามอง = เหนือ

รวมคีย์ลัด Excel ที่ใช้บ่อย พร้อมเทคนิคการจำ 83 — Ant มันคือมดที่เดินบนพื้นดิน เราต้องก้มมองต่ำ
= ขั้วโลกใต้ **Ant**arctic

ตั้งแต่ผมรู้หลักการจำนี้ ที่อาศัยการเชื่อมโยงกับคำว่า Ant และ Arc ผมก็ไม่เคยสับสนกับคำว่า Antarctic กับ Arctic อีกเลยครับ

นอกจากนี้ก็มีหลายเทคนิคที่ผมคิดค้นวิธีจำของผมเองด้วย เช่น

เครื่องหมาย > กับ <

> มากกว่า เพราะฝั่งซ้ายมีสองกิ่ง ฝั่งขวามีจุดเดียว แปลว่า ซ้ายมากกว่าขวา
< น้อยกว่า เพราะ ฝั่งซ้ายมีจุดเดียว ฝั่งขวามีสองกิ่ง แปลว่า ซ้ายน้อยกว่าขวา

วิธีการนำไปใช้

บทความนี้ผมจะพยายามให้คุณจำ Keyboard Shortcut หรือ คีย์ลัดต่างๆ ให้ได้ง่ายที่สุด แต่ทว่าการจำทางสมองก็เป็นเพียงส่วนหนึ่ง…

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

คีย์ลัดในเว็บของผมจะเป็นของ PC เท่านั้น ถ้าใครใช้ Mac ลองไปดูจากเว็บ ExcelJet แทนนะครับ

เอาล่ะ เพื่อไม่ให้เสียเวลา เราไปเข้าเรื่อง Excel กันได้แล้วล่ะ

คีย์ลัด ใน Excel (Keyboard Shortcut)

สิ่งสำคัญมากๆ คือ ก่อนจะเข้าโปรแกรม Excel ครั้งแรก ต้องปรับคีย์บอร์ดให้เป็นภาษาอังกฤษก่อนนะครับ ไม่งั้นจะกดคีย์ลัดบางตัวไม่ติด และคีย์ลัดที่ต้องกดปุ่ม Fต่างๆ เช่น F4 บางเครื่องต้องกดเป็น Fn+F4 แทนนะครับ

คีย์ลัดที่สำคัญที่สุด ก็คือ Action อะไรก็ได้ที่เราทำบ่อยที่สุด ซึ่งมันก็แล้วแต่งานของแต่ละคนว่ามันคืออะไร ดังนั้นผมจะแบ่งคีย์ลัดตามกลุ่มการใช้งานที่คล้ายๆ กันอยู่ด้วยกันนะครับ

ซึ่งคีย์ลัดตัวไหนที่ผมใช้บ่อยมาก จะทำตัวหนาสีแดงไว้ให้ครับ

การทำงานของไฟล์ (มักใช้ได้กับหลายๆ โปรแกรม)

คีย์ลัด	ความหมาย	วิธีจำ
Ctrl+N	New Workbook	N=New
Alt+F4	ปิดโปรแกรม (ใช้ได้กับทุกโปรแกรม)	F4 (Four) = Forever คงอยู่ตลอด Alt =สลับ เปลี่ยนเป็นอีกแบบ รวมกัน=เปลี่ยนจาก Forever =เป็นการปิดการทำงาน
Ctrl+F4	ปิด Workbook ปัจจุบัน	คล้ายๆ ข้างบน แต่แค่ Ctrl เลยให้ผลแค่ Workbook
Ctrl+O	Open ไฟล์	O=Open
Ctrl+P	Print	P=Print
Ctrl+S	Save ไฟล์ (ถ้าไม่เคย Save จะเป็น Save As)	S=Save
F12	Save As

คำสั่งทั่วไป (ใช้ได้กับหลายๆ โปรแกรม)

คีย์ลัด	ความหมาย	วิธีจำ
Ctrl+C	Copy ข้อมูล	C=Copy
Ctrl+V Enter	Paste ข้อมูล ถ้าเรา copy ข้อมูลมาแล้ว เราสามารถกด Enter เพื่อ Paste ได้นะ	V=วาง (ติดกับ c กดง่าย)
Ctrl+Alt+V หรือคลิ๊กขวาหลัง Copy	Paste Special	V=วาง Alt = ทางเลือก Paste แบบมีทางเลือก
Ctrl+X	Cut ข้อมูล	X = รูปกรรไกร (ติดกับ c กดง่าย)
Ctrl+Z	undo
F1	Help

เรียกใช้เครื่องมือ

คีย์ลัด	ความหมาย	วิธีจำ
F4	Redo (กรณีเคย Undo มาก่อน) Repeat action สุดท้ายซ้ำ (กรณี Undo จนสุดแล้ว/ไม่เคย undo)	F4 (Four) = Forever ทำซ้ำไปเรื่อยๆ
Alt	เลือกเมนูบน Ribbon ด้วยคีย์บอร์ด	Alt=ทางเลือก สลับ เปลี่ยน เปลี่ยนจากปกติใช้ Mouse มาใช้ Keyboard แทน
Alt, ตามด้วย ตัวเลข	เป็นการใช้เมนูใน Quick Access Toolbar ปุ่มอะไรไม่มีคีย์ลัด หรือเลือกยาก กดยาก ก็มายัดไว้ในนี้นะ
Alt+F1	สร้างกราฟอย่างรวดเร็ว ใน Sheet เดิม
Alt+F11	เปิด VBA Editor

ตัวอย่าง Quick Access Toolbar

เอาเครื่องมือที่ใช้บ่อย แต่กดปุ่มยากมาใส่

รวมคีย์ลัด Excel ที่ใช้บ่อย พร้อมเทคนิคการจำ 85 — ใส่เครื่องมือเพิ่มจาก Ribbon โดยคลิ๊กขวา -> Add to Quick Access Toolbar

รวมคีย์ลัด Excel ที่ใช้บ่อย พร้อมเทคนิคการจำ 86 — พอกด Alt ก็จะมีเลขขึ้น ให้กดใช้ได้เลย

รวมคีย์ลัด Excel ที่ใช้บ่อย พร้อมเทคนิคการจำ 87 — Customize การเรียง/เพิ่ม/ลบ เครื่องมือได้
สามารถใส่เครื่องมือที่ไม่อยู่ใน Ribbon ได้

ช่วยกรอกข้อมูลเร็วๆ

คีย์ลัด	ความหมาย	วิธีจำ
Ctrl+;	Stamp วันที่ปัจจุบันลง Cell	Ctrl+ว = วันที่ (อยู่ปุ่มเดียวกับเวลา)
Ctrl+: Ctrl+Shift+;	Stamp เวลาปัจจุบันลง Cell (ละเอียดแค่ระดับนาที)	: เป็นสัญลักษณ์ของเวลา (อยู่ปุ่มเดียวกับวันที่ Ctrl+ว)
Alt+ลูกศรลง	เลือก Drop down List (ถ้าไม่เคยสร้าง มันจะเดาจากข้อมูลด้านบน)	ALT = ทางเลือก ลง = Drop Down
Ctrl+D	Fill Down Copy จากช่องบนสุดลงมา	D=Down
Ctrl+R	Fill Right Copy จากช่องซ้ายสุดไปขวา	R=Right
Ctrl+E	Flash Fill เลียนแบบผลลัพธ์ตัวอย่างที่เราใส่ลงไป ใครไม่เคยใช้ลองอ่านอันนี้ได้	E=Excellent Flash Fill กรอกข้อมูลแบบ Excellent

คีย์ลัด ผสม Mouse ลาก

กรณีใช้กับ Range

รวมคีย์ลัด Excel ที่ใช้บ่อย พร้อมเทคนิคการจำ 88 — เอา Cursor มาที่ขอบ Range
จะเป็นลูกศร 4 แฉก ถึงจะลากได้

คีย์ลัด	ความหมาย	วิธีจำ
ลากขอบ Range	Move Range	ลาก=ลากตัวไป =จับย้าย
Ctrl+ลากขอบ Range	Copy Range	Ctrl=Copy
Shift+ ลากขอบ Range	Insert Range	Shift แปลว่า เลื่อน =แทรกจนคนอื่นเลื่อน
Shift+Ctrl+ลากขอบ Range	Insert Copy Range	Shift แปลว่า เลื่อน Ctrl=Copy
Alt+ ลาก	ถ้ากด Alt ไว้ จะ ลาก Range เพื่อข้าม Sheet/Workbook ได้	Alt=เปลี่ยน Sheet/Workbook

กรณีใช้กับ Object อื่น

รวมคีย์ลัด Excel ที่ใช้บ่อย พร้อมเทคนิคการจำ 89 — Ctrl+ลาก Sheet
สามารถ Copy Sheet ได้แบบง่ายๆ
ไม่ต้องคลิ๊กขวา Move or Copy ให้ยุ่งเลย!

คีย์ลัด	ความหมาย	วิธีจำ
ลาก Sheet/ Object / Shape	Move Sheet/ Object / Shape	ลาก=ลากตัวไป =จับย้าย
Ctrl+ลาก Sheet/ Object / Shape	Copy Sheet/ Object / Shape	Ctrl=Copy
Shift+ ลากขอบ Shape	เลื่อนแบบให้ตรงแนวตั้งแนวนอน	Shift=เลื่อนได้ดี

การเลือกข้อมูล

ใช้ Mouse ผสม Keyboard

คีย์ลัด	ความหมาย	วิธีจำ
Shift + Click	เลือกข้อมูลที่ต่อเนื่องกัน	Shift=Selection เลือกทีเดียวเยอะๆ
Ctrl + Click	เลือกข้อมูลที่ไม่ต่อเนื่องกัน	Ctrl = ควบคุม = ค่อยๆ เลือกทีละกัน

ใช้ Keyboard ปุ่มหลัก

คีย์ลัด	ความหมาย	วิธีจำ
ปุ่มลูกศร (ขึ้นลงซ้ายขวา)	เลื่อน Active Cell ทีละ Cell	ลูกศร=ทิศทาง
Shift+ ปุ่มลูกศร	เลือกข้อมูลเพิ่ม/ลด ทีและแถว ทีละคอลัมน์	Shift= Selection =เลือก ลูกศร=ทิศทาง
Ctrl+ ปุ่มลูกศร	กระโดดไปสุดทาง	Ctrl=กระโดด ลูกศร=ทิศทาง
Shift+Ctrl+ ปุ่มลูกศร (กด Shift ก่อน Ctrl จะเลือกในขณะเขียนสูตร แล้วไม่หลุด)	เลือกข้อมูลและกระโดด ไปสุดทาง	Shift=เลือก Ctrl=กระโดด ลูกศร=ทิศทาง
Ctrl+A	เลือกข้อมูลทั้งหมด (ที่ต่อเนื่องกัน)	A=All
Ctrl+.	เปลี่ยนจุดมุม Active Cell (cell ที่ highlight) ในพื้นที่ที่เลือก ในทิศตามเข็มนาฬิกา มักใช้คู่กับ Shift+ลูกศร	. = จุดมุม

ใช้ Keyboard ปุ่มเสริม

คีย์ลัด	ความหมาย	วิธีจำ
Ctrl+Home	กระโดดไปช่องแรกสุด	Ctrl=กระโดด Home=บ้าน จุดเริ่มต้น
Shift+Ctrl+Home	เลือก+กระโดดไปช่องแรกสุด	Shift=เลือก Ctrl=กระโดด Home=บ้าน จุดเริ่มต้น
Ctrl+End	กระโดดไปช่องสุดท้าย ที่เคยใช้งาน	Ctrl=กระโดด End=จุดสิ้นสุด
Shift+Ctrl+End	เลือก+ กระโดดไปช่องสุดท้าย ที่เคยใช้งาน	Shift=เลือก Ctrl=กระโดด End=จุดสิ้นสุด
PgUp	ขึ้นทีละหน้า	Page Up ขึ้นบน = ย้อนกลับ
PgDn	ลงทีละหน้า	Page Down ลงล่าง = ไปต่อ
Alt+PgUp	ไปซ้ายทีละหน้า	Alt ช่วยเปลี่ยนทิศ ซ้าย = ย้อนกลับ
Alt+PgDn	ไปขวาทีละหน้า	Alt ช่วยเปลี่ยนทิศ ขวา = ไปต่อ
Ctrl+Space bar	เลือกทั้งคอลัมน์ (ถ้าใช้ใน Table จะเลือกแค่ใน Table นั้น)	Space bar = Range ยาวๆ Ctrl = Column
Shift+Space bar	เลือกทั้งแถว (ถ้าใช้ใน Table จะเลือกแค่ใน Table นั้น)	Space bar = Range ยาวๆ Shift = ปุ่มยาวๆ แนวนอน= แถว

การแทรก ลบ แถว/คอลัมน์/cell

คีย์ลัด	ความหมาย	วิธีจำ
Ctrl+ บวก Ctrl+Shift+=	Insert Row/Col/Cell แล้วแต่ว่าเลือกอะไรอยู่	เครื่องหมาย + คือ เพิ่ม/แทรก
Ctrl+ ลบ	Delete Row/Col/Cell แล้วแต่ว่าเลือกอะไรอยู่	เครื่องหมาย – คือ ลบ
Ctrl+0	Hide Column	0 ศูนย์ คือ สูญ ทำให้หายไป 0 ดูเป็นแนวตั้งมากกว่า (สูงๆ) จึงเป็นการซ่อนคอลัมน์
Ctrl+9	Hide Row	9 เป็นตัวคู่กับ 0 (ต้องจำ Ctrl+0 ให้ได้ก่อน)
Ctrl+Shift+0 Ctrl+)	Unhide Column	เพราะ Ctrl+0 คือ Hide Column
Ctrl+Shift+9 Ctrl+(	Unhide Row	เพราะ Ctrl+9 คือ Hide Row

การค้นหาข้อมูล

คีย์ลัด	ความหมาย	วิธีจำ
Ctrl+F	เครื่องมือ Find/Replace	F=Find
Ctrl+G F5	เครื่องมือ Go to	G=Go to F5=5 (ห้า) =ไปหา
Ctrl+{ Ctrl+Shift+[	ไปช่องทั้งหมด ที่เกี่ยวข้องกับที่มาของสูตร คล้าย Trace Precedent ไปจนสุด	{ } = สัญลักษณ์ ของสูตร Array เห็นข้อมูลเป็นก้อน { มาก่อน=ไปก่อนหน้า
Ctrl+} Ctrl+Shift+]	ไปช่องที่เกี่ยวข้องทั้งหมด ที่เอาข้อมูลไปใช้ คล้าย Trace Dependent ไปจนสุด	{ } = สัญลักษณ์ ของสูตร Array เห็๋นข้อมูลเป็นก้อน } มาหลัง=ไปถัดไป
Ctrl+[	ไปช่องที่มาของสูตร Trace Precedent ทีเดียว	ปุ่มเดียวกับข้างบน แต่ไม่กด Shift
Ctrl+]	ไปช่องต่อไปที่เอาข้อมูลไปใช้ Trace Dependent ทีเดียว	ปุ่มเดียวกับข้างบน แต่ไม่กด Shift

การปรับ Format ข้อมูล

รวมคีย์ลัด Excel ที่ใช้บ่อย พร้อมเทคนิคการจำ 90

คีย์ลัด	ความหมาย	วิธีจำ
Ctrl+1	Format Cell	Format ทุกอย่างแบบ All in One (1) เป็น Combo เข้าชุด Ctrl+ 1,2,3,4,5
Ctrl+2 Ctrl+B	ตัวหนา	ลำดับเรียงตาม Ribbon= ฺB, I, U b=bold
Ctrl+3 Ctrl+I	ตัวเอียง	ลำดับเรียงตาม Ribbon= ฺB, I, U i=italic
Ctrl+4 Ctrl+U	ขีดเส้นใต้	ลำดับเรียงตาม Ribbon= ฺB, I, U u=underline
Ctrl+5	ขีดฆ่า (strike through)	5=โดน(ขีด)ฆ่าตายห่า เป็น Combo เข้าชุด Ctrl+ 1,2,3,4,5

การตีกรอบ

ต้องเข้า Menu Format Border ก่อน โดย กด Ctrl+1 แล้วไป Tab Border

คีย์ลัด	ความหมาย	วิธีจำ
Alt+O	ตีกรอบรอบนอก	O=Outside
Alt+I	ตีกรอบใน	I=Inside / Interior
Alt+R	ตีกรอบขวา	R=Right
Alt+L	ตีกรอบซ้าย	L=Left
Alt+T	ตีกรอบบน	T=Top
Alt+B	ตีกรอบล่าง	B=Bottom
Alt+H	ตีกรอบแนวนอน	H=Horizontal (H มีเส้นนอนราบตรงกลาง)
Alt+V	ตีกรอบแนวตั้ง	V=Vertical (V ชี้ลงแนวตั้ง)
Alt+D	ตีกรอบแนวเฉียง	D=Diagonal
Alt+N	ไม่ตีกรอบเลย	N=None

การปรับ Format ตัวเลข

รวมคีย์ลัด Excel ที่ใช้บ่อย พร้อมเทคนิคการจำ 91

คีย์ลัด	ความหมาย	วิธีจำ
Ctrl+~ (ถ้าเราเปลี่ยนภาษา ด้วยปุ่มนี้จะใช้ไม่ได้)	ใส่ General Format	Combo เรียงลำดับปุ่มคีย์บอร์ด ตามเมนู Number Format
Ctrl+! (Ctrl+Shift+1)	ใส่ Number Format มี, หลักพัน ทศนิยม 2 ตำแหน่ง	ตัวเลขเป็น Format ทีนิยมที่สุด! (ตกใจ) เป็นอันดับ1 (เลข1)
Ctrl+@ (Ctrl+Shift+2)	ใส่ Format เวลา	@ at= เจาะจง เหมือนการเจาะจงเวลา เช่น เจอกัน @8:30 นะ
Ctrl+# (Ctrl+Shift+3)	ใส่ Format วันที่	# คือ รูปแบบข้อมูลวันที่ ในโปรแกรม Database
Ctrl+$ (Ctrl+Shift+4)	ใส่ Format สกุลเงิน	$ คือ สกุลเงิน
Ctrl+% (Ctrl+Shift+5)	ใส่ Format %	%…ก็คือ %
Ctrl+^ (Ctrl+Shift+6)	ใส่ Format Scientific	E คือ 10 ยกกำลัง ^ คือ เครื่องหมายยกกำลัง

มุมมอง

คีย์ลัด	ความหมาย	วิธีจำ
Ctrl+Alt+ บวก Ctrl+Alt+ =	Zoom in	Alt คือเปลี่ยน(ขนาด) + คือเพิ่มขนาด
Ctrl+Alt+ ลบ	Zoom out	Alt คือเปลี่ยน(ขนาด) – คือ ลดขนาด
Ctrl+Pgup	ไป Sheet ก่อนหน้า	มองให้เหมือนกระดาษสมุด PgUp คือหน้าบน ที่มาก่อน
Ctrl+PgDn	ไป Sheet ถัดไป	มองให้เหมือนกระดาษสมุด PgDnคือหน้าต่อไป
Ctrl+Tab	ไป Workbook ถัดไป
Ctrl+Shift+Tab	ไป Workbook ที่แล้ว

การเขียนสูตร

คีย์ลัด	ความหมาย	วิธีจำ
Tab	เลือกใช้ Function หรือ Name ตอนที่มี Auto Complete มาให้เลือก
F2	เข้าสู่ Edit Mode
F3	เลือก Name เพื่อ Paste ลงสูตร	F3 เป็นเรื่อง Name
F4	[ใน Edit Mode] ใส่เครื่องหมาย $ เพื่อตรึง Cell Reference วนกัน 4 แบบ $A$1, A$1, $A1, A1	F4 = 4ever วัน 4 แบบ
F9	[ใน Edit Mode] คำนวณแปลงสูตรที่ Highlight ให้เป็น Value [นอก Edit Mode] สั่ง Calculate ทั้ง Workbook	F9 เป็นการคำนวณ
Shift+F9	สั่ง Calculate แค่ Sheet ที่ทำงาน	Shift=Sheet เดียว F9 เป็นการคำนวณ
Ctrl+Alt+F9	บังคับคำนวณทุกอย่างใหม่หมด แม้ว่าข้อมูลจะไม่เปลี่ยน
Esc	ใช้ยกเลิกการกด F9 / ยกเลิกการเขียนสูตร	Esc = หนี
Ctrl+Enter	ใส่สูตรพร้อมกันหลายช่อง	Ctrl=ควบคุม ควบคุมหลายช่อง พร้อมกัน
Ctrl+Shift+Enter	ใส่สูตรแบบ Array Formula
Alt+=	ใส่ Autosum	อยากรู้ผลรวมว่า เท่ากับเท่าไหร่

เรื่องเกี่ยวกับ Name

คีย์ลัด	ความหมาย	วิธีจำ
F3	เลือก Name เพื่อ Paste ลงสูตร	F3= Name
Ctrl+F3	เปิด Name Manager	Ctrl=ควบคุม F3= Name ควบคุม Name
Ctrl+Shift+F3	สร้าง Name จาก Selection	Ctrl=ควบคุม Shift = Selection F3= Name

Table / Filter

คีย์ลัด	ความหมาย	วิธีจำ
Ctrl+T	แปลงให้เป็น Table	T=Table
Ctrl+Shift+L	เปิดปิด Filter	แต่ก่อน Filter เคยถูกเรียกว่า List เลยใช้ตัว L
Ctrl+Shift+T	เปิด/ปิด Table Total Row	T=Total Row Shift เพื่อให้ไม่ซ้ำ กับ Ctrl+T
Alt+ลูกศรลง	เลือก Drop down List (ถ้าไม่เคยสร้าง มันจะเดาจากข้อมูลด้านบน)	ALT = ทางเลือก ลง = Drop Down
Shift+Alt+ลูกศรลง	เปิดเมนูบน Filter Drop down จาก Cell ที่เลือก (กรณี Active Cell อยู่ที่ Data)	Shift เพิ่ม =เลื่อนไป เลือก Dropdown ได้ แม้ไม่อยู่ที่หัวตาราง

หลังเข้า Filter Dropdown แล้วกดปุ่มที่ขีดเส้นใต้ได้

รวมคีย์ลัด Excel ที่ใช้บ่อย พร้อมเทคนิคการจำ 92 — ถ้าจะ Search กดปุ่ม E ได้

PivotTable

คีย์ลัด	ความหมาย	วิธีจำ
Alt, N, V	Insert–> Pivot Table	Alt=เรียก Ribbon N=iNsert V=piVot
Ctrl+เครื่องหมายลบ	Filter ข้อมูลนั้นทิ้ง	ลบ = เอาออก
Shift+Alt+ลูกศรขวา	Group (ใช้กับ Range ปกติได้ด้วย)	Shift=Selection Alt=เปลี่ยน รวม=เปลี่นกลุ่มข้อมูลที่เลือก ยิ่ง group จะยิ่งซ้อนไปทางขวา
Shift+Alt+ลูกศรซ้าย	Ungroup (ใช้กับ Range ปกติได้ด้วย)	Shift=Selection Alt=เปลี่ยน รวม=เปลี่นกลุ่มข้อมูลที่เลือก Ungroup จะปลดออกไปทางซ้าย
Alt+F5	Refresh	F5 เป็นปุ่ม Refresh ในโปรแกรมทั่วไป
Ctrl+Alt+F5	Refresh All	Ctrl ควบคุมทั้งหมด F5 เป็นปุ่ม Refresh ในโปรแกรมทั่วไป

สรุป แล้วยังไงต่อไป?

สำหรับบทความสุดยาวอันนี้ก็จบลงแล้วครับ หวังว่าจะได้เทคนิคดีๆ ไปปรับใช้กันนะครับ และอย่าลืมลงมือทำบ่อยๆ ให้ร่างกายจดจำนะ

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

ในอนาคต ผมจะพยายามอัปเดทหน้านี้เรื่อยๆ นะครับ มันอาจจมีเทคนิคที่ดีขึ้น รูปประกอบที่ดีขึ้น เพื่อให้บทความนี่สมบูรณ์มากขึ้นเรื่อยๆ ครับ

June 16, 2019

ใช้ Excel คำนวณมูลค่าเงินในอนาคตจากการลงทุน
“ถ้าเราลงทุน x บาท ด้วยผลตอบแทน z% เป็นเวลา y ปี สุดท้ายจะมีเงินเท่าไหร่ครับ?”

นี่คือคำถามที่ผมได้รับ หลังจากที่ผม Post เรื่องการคำนวณยอดผ่อนเงินกู้ไป ซึ่งได้รับผลตอบรับที่ดีมากๆ (มีคนสนใจเรื่องกู้เงินเยอะขนาดนี้เลยเหรอเนี่ย!! น่ากลัวนิดๆ นะ 555 )

เลยคิดว่า เมื่อมีคนสนใจเรื่องการลงทุนด้วย ยิ่งเป็นสิ่งที่ควรสนับสนุนเลย เพราะยิ่งลงทุนเร็วยิ่งได้เปรียบมากๆ นะครับ (อ่านจบแล้วจะเห็นว่าระยะเวลาการลงทุน สำคัญขนาดไหน)

ดังนั้นผมจะสอนวิธีใช้ Excel คำนวณมูลค่าเงินในอนาคตแบบง่ายๆ ได้ด้วยฟังก์ชัน FV ใน Excel เองครับ

ใช้ Excel คำนวณการลงทุน FV

เราสามารถคำนวณมูลค่าเงินในอนาคตได้ด้วยฟังก์ชัน FV (Future Value) ได้ครับ สูตรเดียวจบ แถมรองรับการใส่เงินเพิ่มในแต่ละเดือนได้ด้วยนะ ซึ่งมีสูตรดังนี้

=FV(rate,nper,pmt,[pv],[type])

จะเห็นว่าสูตรขึ้นอยู่กับ Input หลายๆ ตัว คือ
- rate=อัตราผลตอบแทนต่องวด (ถ้าเราคิดจะมีการลงทุนเพิ่มในแต่ละเดือน แปลว่าระยะวลาของแต่ละงวดคือเดือนครับ)
- nper = จำนวนงวด
- pmt = เงินที่จะใส่เพิ่มแต่ละงวด
- pv = เงินตั้งต้น
- type = เป็นการเลือกว่ากระแสเงินสดเกินขึ้นที่ต้นงวดหรือปลายงวด ถ้าไม่กรอกแปลว่าเกิดที่ปลายงวดแต่ละงวด ซึ่งเราจจะไม่กรอก
หลักการเหมือนเดิม คือ เงินเข้ากับเงินออกต้องใส่เครื่องหมายคนละทิศกัน
- ซึ่งในที่นี้ PV คือเงินลงทุนตั้งต้น เงินออกจากตัวเรา ใส่เครื่องหมายลบ
- pmt คือ เงินลงทุนเพิ่มแต่ละเดือน เงินออกจากตัวเรา ใส่เครื่องหมายลบ
- สุดท้าย เงินที่ได้กลับมาตอนจบ คือ FV เงินเข้าตัวเรา จะออกมาเป็นบวกครับ
จะได้สูตรดังนี้

=FV(ผลตอบแทนต่องวด,จำนวนงวด,-เงินลงทุนเพิ่มในแต่ละงวด,-เงินลงทุนตั้งต้น)

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

วิเคราะห์การลงทุนด้วย Sensitivity Analysis

การถามว่า “ถ้าเปลี่ยนปัจจัยต่างๆ ไป ผลลัพธ์จะเป็นยังไง?” อันนี้เค้ามีชื่อเรียกอย่างเป็นทางการว่า Sensitivity Analysis ครับ

ซึ่งผมจะใช้เครื่องมือ Data Table ช่วยทำ เพราะง่ายดี (จะใช้การใส่ $ lock cell ก็ทำได้นะ) ซึ่งแสดงวิธีทำไว้บทความอื่นๆ แล้ว จึงไม่ขอบอกวิธีละเอียดในบทความนี้แล้วนะ

เปลี่ยนระยะเวลาการลงทุน เปลี่ยนอัตราผลตอบแทน

ให้ Row input cell เป็น อัตราผลตอบแทน , Column input cell เป็นระยะเวลาการลงทุน

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

เราจะเห็นได้ว่าด้วยอัตราผลตอบแทน 6% (เช่น ลงทุนในกองทุนรวมหุ้น ควรจะต้องได้ 6-10% นะ) เป็นเวลา 15-20 ปี นี่ได้ผลตอบแทน มากกว่าการไม่ลงทุนอะไรเลยเกือบ 2 เท่าเลยนะครับ!!

ดังนั้นถ้าเรายิ่งเริ่มลงทุนเร็ว เราจะมีระยะเวลาในการลงทุนได้นานขึ้น เช่น ถ้าคุณลงทุนตั้งแต่อายุ 30 กว่าจะเกษียรก็ 60 แปลงว่ามีระยะเวลาการลงทุนได้ 20-30 ปีเลยนะครับ แต่ถ้าคุณมาเริ่มตอนอายุ 40-50 นี่่อาจจะไม่เหลือเวลามากแล้ว

ถ้าไม่มีเวลาลงทุนนานขนาดนั้นล่ะ?

สมมติว่าคุณมีเวลาลงทุน Fix ไว้แค่ 10 ปี (ช่อง B3 แก้เป็น 10 ไปเลย โดยเราจะไม่ได้เปลี่ยนในตารางเล่น)

ดังนั้นอาจต้องลงทุนแต่ละเดือนเพิ่มขึ้นแทน เราลองมาดูกันว่าเป็นยังไง?

ให้ Row input cell เป็น อัตราผลตอบแทน , Column input cell เป็นจำนวนเงินที่ลงเพิ่มในแต่ละเดือนครับ

จะเห็นว่าการลงทุนเพิ่มเยอะขึ้น ก็ยังพอช่วยให้เงินในอนาคตงอกเงยได้พอสมควร แต่ถ้าดูว่าเงินเป็นกี่เท่าของกรณีไม่ได้เอาไปลงทุน มันจะไม่ได้เยอะมากนะครับ

ไฟล์ประกอบบทความ

โหลดไฟล์ประกอบบทความได้ที่นี่

สรุป

ถ้าคุณไม่ค่อยมีเงินลงทุนมาก คุณต้องพึ่งพาเวลาให้มาก… ดังนั้นลงทุนเร็ว ได้เปรียบโคตรๆ (มีเวลาให้เรียนรู้เยอะด้วย ล้มได้ ลุกใหม่ไหว)

ถ้าคุณไม่ค่อยมีเวลา คุณต้องลงทุนเพิ่มให้มากขึ้น… (แต่ก็สู้ใช้เวลามากๆ ไม่ได้อยู่ดี)

อย่างไรก็ตาม ทั้งคู่ ต้องลงทุนในที่ที่ผลตอบแทนดีในระยะยาว (ซึ่งจะทำได้ดีกว่า 6% ต้องใช้ความรู้ให้มากขึ้น คือการลงทุนในตัวเอง) ไม่ใช่ปล่อยเอาไว้ใน บัญชีออมทรัพย์ หรือ ฝากประจำ ที่ดอกเบี้ยเตี้ยต่ำติดดินจนเหมือนเอาเงินไว้ในตุ่ม 555
June 7, 2019
สอนใช้ Excel คำนวณยอดผ่อนเงินกู้แบบง่ายๆ
ในฐานะที่ทำงานเกี่ยวกับสินเชื่อในธนาคารมาก่อน วันนี้ผมจะมาสอนใช้ Excel คำนวณยอดผ่อนเงินกู้แบบง่ายๆ ใครๆ ก็ทำได้ครับ ทำแป๊ปเดียว ไม่ต้องสร้างตารางผ่อนอะไรให้ยุ่งยากด้วย

การผ่อนเงินกู้โดยทั่วไปสำหรับคนทั่วไป จะแบ่งเป็น 2 ลักษณะ คือ
1. แบบเงินต้นคงที่ (ดอกเบี้ย Flat Rate เช่น การผ่อนรถยนต์)
2. แบบลดต้นลดดอก (ดอกเบี้ย Effective Rate แบบผ่อนบ้าน หรือ สินเชื่อธุรกิจ)
ซึ่งผมจะสอนวิธีคิดทั้งคู่เลยครับ

เอาล่ะ สมมติเราจะกู้เงิน 1 ล้านบาท ดอกเบี้ย 6% ต่อปี แล้วอยากรู้ว่าจะต้องผ่อนเดือนละกี่บาท (เดือนละเท่าๆ กัน) จึงจะผ่อนหมดพอดี ในระยะเวลา 5 ปี เราก็กรอกข้อมูลดังรูปเลย

คำนวณยอดผ่อนเงินกู้แบบเงินต้นคงที่ (ดอกเบี้ย Flat Rate)

ทีนี้จะเริ่มจากตัวที่เข้าใจง่ายก่อน ก็คือการผ่อนรถ ซึ่งโดยทั่วไปจะคิดดอกเบี้ยแบบ Flat Rate คือ แบบไม่ลดต้นลดดอก ซึ่งแปลว่า ไม่ว่าเราจะผ่อนเงินต้นไปเท่าไหร่ ดอกเบี้ยที่จ่ายก็จะไม่ลดลงเลย (ช่วงแรกหรือช่วงหลัง ก็ต้องจ่ายดอกเบี้ยเท่ากันหมด)

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

= (เงินต้น+ดอกเบี้ยทุกปี) / (จำนวนงวดทั้งหมด)

=(B1+ B1*B2*B3) / (B3*12)

คำนวณยอดผ่อนเงินกู้แบบลดต้นลดดอก (ดอกเบี้ย Effective Rate)

การคิดยอดผ่อน แบบลดต้นลดดอก ถ้าใช้วิชาคณิตศาสตร์ทั่วไปจะยากมากๆ แต่เราโชคดีที่ Excel มีฟังก์ชัน PMT ที่จะตอบโจทย์เรื่องนี้ได้ครับ

=PMT(rate,nper,pv,[fv],[type])

สูตรพวกการเงินใน Excel เช่น PV, FV, PMT, RATE มีหลักการเหมือนกัน คือ ต้องดูทิศทางของ Cash Flow ด้วย เช่น ถ้าเงินเข้าให้ใส่เลขเป็นบวก ถ้าเงินออกให้ใส่เลขเป็นลบ (หรือจะกลับกันก็ได้ ขึ้นอยู่กับมองในมุมมองคนกู้ หรือ มุมมองธนาคาร)

ดังนั้นในสูตรนี้ผมจึงใส่เลขเงินต้นให้ติดลบ (ธนาคารเสียเงิน) เพราะทิศทางมันคนละอันกับยอดผ่อนแต่ละเดือน (ธนาคารได้เงิน)

เป็นยังไงบ้างครับ การคิดยอดผ่อนเงินกู้แบบง่ายๆ ไม่ต้องสร้างตารางอะไรให้วุ่นวาย ก็สามารถคำนวณยอดผ่อนคร่าวๆ ได้แล้วครับ

ซึ่งเรื่องพวกนี้เราสามารถนำมาต่อยอดทำเป็น Sensitivity Analysis ได้สบายๆ ครับ เช่น ผมสามารถหายอดผ่อนต่อเดือนที่จะต้องผ่อน ถ้าจำนวนปีเปลี่ยน หรือ อัตราดอกเบี้ยเปลี่ยนไปได้

วิธีทำคือเตรียมอัตราดอกเบี้ย ปีที่กู้ เอาไว้ก่อนดังนี้

จากนั้นไปช่อง B11 ที่เป็นจุดตัดหัวตาราง แล้วเขียนสูตร Link กับช่องยอดผ่อนที่เราคิดไว้แล้ว คือ =B5

แล้วลากครอบพื้นที่ แล้วกด Data -> What if Analysis -> Data Table
- แล้วเลือก Row Input Cell ไปที่ช่องอัตราดอกเบี้ย คือ B2
- แล้วเลือก Column Input Cell ไปที่ช่องจำนวนปี คือ B3
แล้วกด ok ก็จะได้ดังรูปครับ

ที่นี้ถ้าไม่อยากให้ผ่อนเป็นเศษ ก็ใช้พวก ROUND, ROUNDUP, ROUNDDOWN มาช่วยในช่องที่เราคำนวณยอดผ่อนได้ครับ (ในที่นี้ผมใช้ ROUNDUP เพราะถ้าผ่อนน้อยไปเดี๋ยวผ่อนไม่หมด)

โดยเทคนิคคือ การใส่ Digit ให้ติดลบ จะช่วยให้เป็นเลขกลมๆ ได้ เช่น -2 คือ ทำให้เลขหาร 100 ลงตัว (มี 0 อยู่ 2 ตัว), -3 คือ ทำให้เลขหาร 1000 ลงตัว (มี 0 อยู่ 3 ตัว)

จากนั้นค่าในตารางของ Data Table ก็จะเปลี่ยนไปเองโดยอัตโนมัติเลย

ถ้าเพื่อนๆ อยากรู้เรื่องอะไรอีกก็ Comment ทิ้งไว้ได้เลยครับ จะได้เอามาทำเป็นบทความให้ครับ

ถ้าอยากรู้ว่าจะกู้ผ่านรึเปล่าล่ะ?

มีคนถามผมอีกว่า จะคำนวณความสามารถในการชำระหนี้ยังไง?

จริงๆ แล้ววิธีคำนวณมันง่ายมากเลยครับ มันจะมีคำศัพท์อยู่ 2 แบบ คือ
1. Debt Burden นั่นคือ เอาภาระผ่อนหนี้ / รายได้ (กรณีเงินเดือน) หรือกำไรธุรกิจ(กรณีทำธุรกิจ) แล้วคำนวณว่าเป็นกี่%
2. DSCR นั่นคือ รายได้หรือ กำไรธุรกิจ / ภาระผ่อนหนี้
ปัญหาที่คนทั่วไปไม่รู้คือ ภาระผ่อนหนี้ ธนาคารจะคิดหนี้ทั้งหมด ทั้งหนี้ที่มีอยู่แล้ว และหนี้ในครั้งนี้ รวมถึงอาจคิดสินเชื่อที่เป็นเงินหมุนเวียนที่ไม่ได้มีการผ่อนจริงๆ ด้วย (เช่น O/D หรือ บัตรเครดิต) ซึ่งสูตรแต่ละธนาคารไม่เหมือนกัน และผมคงบอกไม่ได้ว่าธนาคารคิดยังไง เพราะเป็นความลับของธนาคารครับ

เอาเป็นว่า เผื่อๆ ไว้หน่อยละกัน ว่าตัวเองมีสินเชื่ออะไรบ้าง ให้เอามารวมๆ ให้หมด อย่างน้อยพวกวงเงินหมุนเวียนควรจะคิดดอกเบี้ยว่าต้องจ่ายเท่าไหร่มาเป็นยอดผ่อนด้วยน่ะครับ

ในรูปข้างล่าง ผมสมมติว่ามีหนี้เดิมทั้งหมดที่ต้องจ่ายอยู่ 40,000 บาท (ในช่อง G4)

ตัวอย่างการคำนวณทำดังนี้

จากรูปจะเห็นว่า Debt Burden ได้ 74.3% แปลว่า
ต้องผ่อนหนี้ด้วยจำนวน 74.3% ของเงินที่สามารถมาผ่อนหนี้ได้

ส่วน DSCR 1.35 เท่า ก็คือ มี เงินที่สามารถมาผ่อนหนี้ได้ เป็น 1.35 เท่าของหนี้ที่ต้องผ่อน

แต่เกณฑ์ระดับไหนที่เรียกว่าผ่าน หรือ ไม่ผ่าน แต่ละธนาคารคิดไม่เหมือนกันครับ ส่วนตัวคิดว่าเลขประมาณนี้ก็ปริ่มๆ แล้วล่ะ (เป็นคุณจะเอามั้๊ย? ต้องผ่อนหนี้ 75% ของรายได้ ซึ่งก็เยอะอยู่นะ ยกเว้นรายได้คุณสูงมากจนทำให้เงินที่เหลืออีก 25% ยังพอดำรงชัวิตได้)

คิดว่าน่าจะพอเห็นภาพบ้างนะครับ ว่าธนาคารมีวิธีเรื่องคำนวณยอดผ่อนเงินกู้ยังไง เอาเป็นว่าถ้าสงสัยอะไรก็ comment ถามมาได้เลยครับ
May 30, 2019
วิธีจัดการข้อมูลซ้ำใน Excel (แบบลึกซึ้ง)

หากเราไม่แน่ใจว่ามีข้อมูลซ้ำอยู่ในรายการข้อมูล Excel หรือไม่? แล้วเราอยากจะรู้หรืออยากจะทำให้ข้อมูลมันไม่ซ้ำกัน เราจะทำยังไงดี? มาดูกันครับ

สถานการณ์ คือ เรามีข้อมูล เป็น Customer ID และวันที่ของการบันทึกข้อมูลลูกค้า
สมมติว่า เราอยากจะได้เฉพาะข้อมูลครั้งล่าสุดเท่านั้น เราจะทำยังไงดี?

แนวทาง #1 : ลบข้อมูลซ้ำด้วย Remove Duplicates

เครื่องมือ Remove Duplicates มันจะเก็บข้อมูลไว้เฉพาะบรรทัดบนสุดเท่านั้น ดังนั้นก่อนจะใช้เครื่องมือนี้ ผมแนะนำให้เรียงข้อมูลให้อยู่ในลักษณะที่ หากข้อมูลซ้ำกัน ให้เอาแถวที่เราอยากได้ไว้บนสุดซะก่อนครับ โดยใช้ Data –> Sort ก่อน

ในที่นี้เราจะเรียงตาม CustomerID ก่อน แล้วเรียงตามวันที่ โดยเอาวันที่ใหม่สุดไว้บน

จากนั้นเราจะได้ข้อมูลที่เรียงกันในแบบที่อยากได้แล้ว

ให้เราเลือกข้อมูลช่องใดช่องหนึ่ง แล้วไปที่ Data -> Remove Duplicates

แล้วให้ติ๊กเฉพาะ CustomerID เท่านั้น (ติ๊กเอา Date ออกซะ) เพราะถ้าติ๊กทั้งคู่มันจะมองทั้งสองคอลัมน์ ซึ่งจะทำให้มันไม่เอา Cust 004 วันที่ 15/3/2019 ออกไป

พอกด ok จะเห็นว่า ในคอลัมน์ CustomerID ที่ซ้ำๆ กัน ผลลัพธ์จะเหลือเฉพาะบรรทัดบนสุดของแต่ละตัวเท่านั้น (นี่แหละการเรียงถึงสำคัญ)

และสังเกตว่า Remove Duplicates มัยไม่สนใจเรื่องตัวพิมพ์เล็กพิมพ์ใหญ่ด้วย (มองว่าเหมือนกัน ลองดูที่ CUST-007 สิ)

แนวทาง #2 : Sort แล้วเขียนสูตรเทียบกับตัวบน

วิธีนี้ผมจะ Sort เช่นเดียวกับตอนทำ Remove Duplicates เพื่อให้ตัวที่อยากได้อยู่บน

จากนั้นจะเขียนสูตรเช็ค Duplicates ง่ายๆ ดังนี้ ว่า CustomerID บรรทัดตัวเอง ซ้ำกับ CustomerID บรรทัดเหนือมัน 1 อันรึเปล่า? ถ้าซ้ำก็เรียกว่า Dup ตัวบนนี่แหละ

ซึ่งถ้าเป็น TRUE ก็จะเป็นตัวที่เราไม่ต้องการนั่นเอง

เขียนสูตรนี้เสร็จแล้ว ถ้าจะลบข้อมูลซ้ำทิ้งเลย จะ Filter TRUE แล้วลบ Row ทิ้งตรงๆ ก็ได้

มันจะเหลือเฉพาะแถวที่ต้องการแล้ว
ส่วน Column C ที่เอาไว้ Check Duplicates ไม่ใช้แล้วก็ลบทิ้งได้ครับ

Tips : ถ้ายังไม่ลบทิ้ง แล้วจะเอาไปใช้อะไรต่อ อย่าลืม Paste Value ตัว TRUE / FALSE ด้วย ไม่งั้นพอ Sort ใหม่แล้วเดี๋ยวสูตรผิดนะครับ

แนวทาง #3 : ดึงเอาเฉพาะข้อมูลที่ไม่ซ้ำด้วย PivotTable

วิธีนี้เราจะใช้ PivotTable สรุปข้อมูล ซึ่งทำไม่ยากเลย ดังนี้

เลือกข้อมูลช่องใดช่องหนึ่ง แล้ว Insert -> PivotTable -> ok

ลาก CustomerID ไว้ที่ Row และ Date ไว้ที่ Value (สังเกตว่า PivotTable ก็ไม่สนตัวพิมพ์เล็กพิมพ์ใหญ่เหมือนกัน)

จากนั้นคลิ๊กขวาตรง Count of Date แล้วเปลี่ยน Summarized Value by เป็น Max

ที่เห็นเป็นเลข 4 หมื่นกว่าๆ จริงๆ ก็คือวันที่นี่แหละ ถูกแล้ว แต่ว่าแค่ยังไม่ได้เปลี่ยน Format เป็นวันที่เท่านั้นเอง ดังนั้นเราจะคลิ๊กขวา เลือกเมนู Number Format… (อย่าเลือก เมนู Format Cell นะ) แล้วเปลี่ยนให้เป็นวันที่ซะ

แนวทาง #4 : ใช้ Power Query

(เดิมเขียนผิด อันนี้แก้แล้ว)

Power Query นี่ทำงานพวกนี้ได้สบายมากๆ ครับ เรายังไม่จำเป็นต้องเรียงข้อมูลตอนแรกด้วยซ้ำ (เพราะไปเรียงใน Power Query ได้)

ให้เลือกข้อมูลช่องใดช่องหนึ่งแล้วเอาเข้า Power Query ตรง import from Table/Range แล้วกด Ok

เรื่องจาก Power Query จะให้ความสำคัญกับตัวพิมพ์ใหญ่ พิมพ์เล็กด้วยเสมอ ดังนั้นเราจะทำการแปลงข้อมูลให้มีลักษณะเหมือนกันก่อน เช่น ใช้ Transform Capitalize Each Word เพื่อแก้ปัญหาให้ CUST-007 ก่อน

จากนั้น ผม Sort ตาม customer A to Z และ กด Sort ตามวันที่ ใหม่ไปเก่า จะได้ดังรูป

จากนั้นเลือก คอลัมน์แรก แล้ว เลือก Remove Rows -> Remove Duplicates

มันจะเหลือเฉพาะตัวที่ไม่ซ้ำแล้ว แต่ปรากฏว่า Cust-004 ดันเหลือ 15/3/2562 ซะงั้น!! ทั้งๆ ที่ควรจะเหลือวันที่ 8/4/2562 ซึ่งเราอุตส่าห์เอามาไว้ข้างบนแล้วแท้ๆ

ซึ่งปรากฏการณ์ที่เกิดขึ้นนี้ ผมมองว่าเป็น Bug ของ Power Query ครับ ซึ่งมีวิธีแก้ไขคือ ให้กด Add Index Number แทรกเอาไว้หลังจาก sort (ก่อนจะสั่ง Remove Duplicates ) ครับ

โดยกดย้อนกลับไปที่ Step การ Sort

แล้วกด Add Index Column -> From 1
แล้วมันจะเตือนว่าเป็นการ Insert step ก็ ok ไปครับ

กดไปที่ Step สุดท้าย

จะเห็นว่าผลลัพธ์ถูกต้องแล้วครับ

จากนั้น ลบ คอลัมน์ index ออกไป เพราะไม่ใช้แล้ว จากนั้นกด Close & Load เป็นอันจบ

แถม 1 # : ระบุข้อมูลซ้ำด้วย Conditional Formatting

ถ้าเราอยากจะแค่รู้ว่ามีข้อมูลซ้ำรึเปล่า? ก็ใช้ Conditional Format -> Highlights Duplicates มาช่วยได้

ผมเลือกแค่คอลัมน์แรกก่อน ค่อยกด Highlight ดังนี้ แล้ว ok

จะเห็นว่ามัน Highlight ส่วนที่ซ้ำกันให้

เราสามาถ Filter เฉพาะตัวที่ Highlights ได้ถ้าต้องการ โดยคลิ๊กขวา Filter by Selected Cell’s Color

มันก็จะเหลือเฉพาะบรรทัดที่มีประเด็น แต่ว่าเราจะจัดการยังไงต่อ ก็เรื่องของเราแล้วล่ะ ซึ่งแนวทางใช้ Conditional Format นี้ ไม่สามารถกำจัดตัวซ้ำได้ด้วยตัวมันเองนะครับ

แถม #2 : ใช้สูตรคำนวณหา Max Date ของลูกค้าแต่ละคน

ใครมี Excel 2016 ขึ้นไป จะมี MAXIFS ให้ใช้ ก็ง่ายหน่อย
=MAXIFS(max_range,criteria_range,criteria,…)
=MAXIFS([Date],[CustomerID],[@CustomerID]) หรือ
=MAXIFS($B$3:$B$15,$A$3:$A$15,A3)

ถ้าไม่มี MAXIFS ก็ต้องใช้ Array Formula
=MAX(([CustomerID]=[@CustomerID])*[Date]) แล้วกด Ctrl+Shift+Enter หรือ
=MAX(($A$3:$A$15=A3)*$B$3:$B$15) แล้วกด Ctrl+Shift+Enter

จะเห็นว่าถ้าทำเป็น Table ก่อน สูตรจะอ่านง่ายกว่านะผมว่า

แบบ Range ต้องมาเล็งว่า A3 คืออะไรอีก…

หมายเหตุ : ภาพ Cover เป็นตัวละครจากการ์ตูนเรื่อง Naruto ใช้คาถาแยกเงาพันร่าง

May 29, 2019
วิธีลดขนาดไฟล์ Excel ใหญ่ๆ ให้เล็กลง
ไฟล์ Excel ขนาดใหญ่นั้นมีได้หลายสาเหตุ และมีวิธีแก้ได้หลายวิธีเช่นกัน ในบทความนี้ผมจะขอนำเสนอหลายๆ วิธีที่จะช่วยลดขนาดไฟล์ Excel ใหญ่ๆ เหล่านั้นได้ครับ

แนวทางลดขนาดไฟล์ Excel#1 :
ลบสิ่งที่ไม่จำเป็นออกซะ โดยเฉพาะ Used Range ที่ไม่ใช้แล้ว

ถ้าบอกว่าจะลดขนาดไฟล์ได้ ให้ “ลบสิ่งที่ไม่จำเป็นออกซะ” อันนี้จะเรียกว่าเทคนิคได้มั้ยนะ 55 ดูจะขวานผ่าซากมากๆ แต่ก็เป็นเรื่องที่ต้องตรวจสอบก่อนอยู่ดี ดังนั้นขอให้ตรวจสอบดังนี้
- ข้อมูลใน Hidden Sheet มีรึเปล่า? ถ้ามี แล้วไม่ใช้ก็ลบซะ
- การใส่ Format ต่างๆ รวมถึง Conditional Format ที่ไม่จำเป็น พวกนี้ก็เปลี่องเนื้อที่นะ ถ้าอยากให้ไฟล์เล็กลงก็ลบออกเถอะ
- การใส่สูตรที่ไม่จำเป็น พวกนี้ถ้าแปลงเป็น Value ก็จะทำให้ไฟล์เล็กลงได้นะ
เทคนิคข้างบนนี่ก็เป็นเรื่องพื้นๆ แต่ตัวที่หลายคนไม่รู้ ก็คือเรื่องของ Used Range ต่างหาก! ซึ่งเกิดขึ้นจากใน Sheet เราอาจเคยมีการใช้งาน Cell นั้นมาก่อน แต่ตอนนี้ไม่ใช้แล้ว ซึ่งเจ้า Used Range นี้อาจทำให้ขนาดของไฟล์ใหญ่โดยไม่จำเป็นได้นะ

ตัวอย่างเรื่อง Used Range

ไฟล์ดั้งเดิมผมมีข้อมูลอยู่เยอะเลย 68615 แถว ดังนี้

ต่อมาผมลองลบข้อมูลทิ้ง ให้มันเหลืออยู่แค่ 10 แถว โดยการกด del บน Keyboard เพื่อลบเอา content เหล่านั้นทิ้งไปซะ

ปรากฎว่าไฟล์ขนาดลดลง แต่ว่ามันก็ยังดูเยอะแปลกๆ

พอกลับไปดูในไฟล์ แล้วกด Ctrl+End (หรือบางเครื่องกด Ctrl+Fn+End) เพื่อให้มันเด้งไป Cell สุดท้ายที่มีการใช้งาน ปรากฎว่า มันดันกลายเป็น Cell K68615 ทั้งๆ ที่มันไม่มีข้อมูลแล้ว (มันควรจะไปประมาณแถวที่สิบไม่ใช่เรอะ!!)

ไอนี่แหละ ที่ทำให้ไฟล์มีขนาดใหญ่ครับ

ถ้าเกิดเราเปลี่ยนวิธี เป็นลบ Cell นั้นทิ้งไปเลย เช่น เลือกแล้ว คลิ๊กขวา Delete Row แล้วลองกด Save ไฟล์ดู แล้วเปิดไฟล์ลงไป จะเห็นว่าขนาดไฟล์เหลือน้อยลงมากๆ

เรื่องนี้จึงเป็นเรื่องสำคัญที่เราจะต้องตระหนักไว้นะครับ เพราะมองด้วยตาเปล่าไม่เห็นซะด้วย

แนวทางลดขนาดไฟล์ Excel#2 :
Save ไฟล์เป็น .xlsb (Excel Binary Workbook)

วิธีนี้เหมาะอย่างยิ่งกับคนที่อยากจะลดขนาดไฟล์แบบรวดเร็ว และไม่อยากจะคิดอะไรมากนัก เพราะวิธีนี้ทำได้ง่ายแสนง่าย นั่นก็คือ ให้ ไปที่ File => Save As แล้วเลือก Save as Type เป็นนามสกุล Excel Binary Workbook (*.xlsb) นั่นเอง

พอลองเปรียบเทียบขนาดไฟล์แล้ว พบว่า ไฟล์เล็กลงเกินครึ่งนึงเลยด้วยซ้ำ!!

แล้ว xlsb มีข้อเสียอะไรมั้ย?

หลายคนคงเริ่มคิดในใจว่า เอ๊ะ! ถ้าไฟล์ xlsb มันขนาดเล็กดีแบบนี้ แล้วค่า default มันจะให้เรา save เป็น .xlsx ไปทำไม? สู้ save เป็น .xlsb ไปให้เลยไม่ดีกว่าเหรอ?

ข้อเสียของ .xlsb คือ
1. ไฟล์ xlsx เก็บข้อมูลแบบ xml เป็นวิธีการเก็บแบบมาตรฐานสากล ทำให้ใช้งานร่วมกับเครื่องมืออื่นๆ ได้ดีกว่า xlsb ที่เก็บไฟล์เป็น bin (binary)
  - ต้องลอง test ดูว่า xlsb ของเราใช้กับพวก 3rd-party software ได้รึเปล่า
  - เคยอ่านเจอในเว็บฝรั่งว่า Power Query ดึงไฟล์ .xlsb ไม่ได้ แต่ไม่จริงนะครับ ผมลองแล้วมันดึงได้นะ
2. ดูจากภายนอกจะไม่รู้ว่ามี Macro แฝงอยู่หรือไม่
ถ้าอ่านแล้วรู้สึกว่า เรารับกับข้อเสียเหล่านี้ได้ ก็ save เป็น .xlsb โลดเลยครับ!

Tips : อย่า Save ไฟล์เป็นนามสกุล .xls (2003)

ถ้า save ไฟล์เป็นนามสกุลแบบ xlsx (2007 ขึ้นไป) ไฟล์จะเล็กกว่าแบบ xls (2003) เพราะไฟล์แบบ 2007 จะเหมือนมีการ Zip ไฟล์มาแล้วในตัวครับ

Tips : รู้หรือไม่ว่า ไฟล์ CSV กินที่กว่า ไฟล์ Excel นะ

อันนี้ไม่เกี่ยวกับไฟล์ Excel โดยตรง แต่บางทีเราก็อาจมี Save ไฟล์โดยที่นามสกุลเป็นไฟล์ CSV ซึ่งจะทำให้ขนาดไฟล์ใหญ่กว่าไฟล์ Excel ปกตินะครับ

ไฟล์ csv มันดีกว่าตรงที่ข้อมูลมันเกิน 1 ล้านแถวได้นี่แหละ แต่ข้อเสียคือ ไฟล์ใหญ่ และไม่สามารถมีสูตรหรือ format อะไรได้เลย

แนวทางลดขนาดไฟล์ Excel #3 :
Compress Pictures บีบอัดรูปซะ

บางทีเรามีการเอารูปเข้ามาไว้ใน File Excel ซึ่งรูปนั้นเดิมทีอาจจะเป็นรูปที่มีความละเอียดสูง ทำให้ไฟล์มีขนาดใหญ่

แต่หากว่าเราไม่ได้ต้องการรูปที่มีความละเอียดสูงขนาดนั้น หรือ เรามีการ Crop รูปให้เห็นแค่บางส่วนของรูปก็เพียงพอแล้ว เราก็สามารถใช้วิธี Compress รูปได้ครับ

โดยให้ Double Click ที่รูป หรือเลือกรูปแล้วไปที่ Picture Tool => Compress Picture แล้วอย่าลืมติ๊ก Apply only to this picture ออกด้วย เพื่อที่จะลดขนาดทุกรูปในไฟล์เลย

จากนั้นเลือกความละเอียดที่ต้องการ ถ้าเลือก Email (96 ppi) ก็จะขนาดเล็กสุด แต่ภาพก็จะห่วยสุดด้วย ซึ่งถ้าคิดว่าภาพหยาบเกินไป ก็เลือกซัก 150 ppi ก็ ok ครับ

บางทีเราก็มีการ Crop รูป เอาเฉพาะส่วนที่ต้องการด้วย ซึ่งถ้า Crop แล้ว Compress ขนาดไฟล์จะเล็กลงมาก

ผลลัพธ์ที่ได้ พบว่า ระหว่างภาพปกติดั้งเดิม กับภาพที่ Crop แล้ว แต่ยังไม่ได้มีการ Compress Picture เลย จะมีขนาดไฟล์ใหญ่เท่ากัน ที่ 947 KB

แต่ถ้ามีการ Compress แล้ว ขนาดไฟล์จะลดลงจนเหลือ 99 KB และ 23 KB ตามลำดับ

ข้อเสียของการ Compress Pictures

ข้อเสียชัดๆ เลย คือ ภาพที่ได้จะคุณภาพห่วยลง เพราะเครื่องมือการ Compress ของ Excel มันไม่ได้เทพขนาดที่จะลดขนาดรูปในขณะที่ภาพยังคงดูสวยเช่นเดิมได้ แต่ถ้าเกิดเราใช้เครื่องมืออื่น เช่น Photoshop หรือ เว็บ TinyPNG ในการบีบอัดรูปให้ขนาดเล็กลงก่อนเอาเข้ามาใน Excel มันก็จะได้ภาพที่ขนาดเล็กแต่ยังสวยอยู่ได้ครับ

แนวทางลดขนาดไฟล์ Excel #4 :
แก้ปัญหาไฟล์ใหญ่เพราะ Pivot Table

ก่อนอื่น ต้องอธิบายก่อนว่า ปกติเวลาเราสร้าง PivotTable ขึ้นมาจาก Source Data ใดๆ ก็ตาม Excel จะมีการสร้างสิ่งที่เรียกว่า Pivot Cache ขึ้นมาด้วยโดยอัตโนมัติ

และเวลาที่ PivotTable เอาข้อมูลไปประมวลผล มันจะไม่ได้เอา Source Data ไปใช้ตรงๆ แต่จะใช้สิ่งที่มีใน Pivot Cache ไปคำนวณต่างหาก และนี่คือสาเหตุที่ว่าทำไม Source data เปลี่ยนไปแล้วเราจะต้อง Refresh PivotTable ด้วยเสมอ

ผมพิสูจน์ให้ดูด้วยการสร้าง PivotTable เปล่าๆ ขึ้นมาแล้ว Save as ทันที โดยที่ยังไม่ได้มีการลากข้อมูลอะไรมา Pivot ทั้งสิ้น

ปรากฎว่าขนาดไฟล์ใหญ่ขึ้นมาเกือบ 2 เท่าเลยทีเดียว

วิธีแก้ไขที่ผมจะแนะนำคือ เราจะสั่งให้ Excel ไม่ต้อง Save Pivot Cache เอาไว้ในไฟล์ แต่ให้มัน Refresh ดึงข้อมูลมาใหม่ ตอนเปิดไฟล์ Excel แทน ซึ่งมีวิธีทำดังนี้ครับ
- ให้เราคลิ๊กขวาที่ Pivot Table แล้ว เลือก Pivot Table Options แล้วกดตามรูปเลย
- ที่สำคัญคือให้ติ๊ก Save Source data with file ออกไปซะ (อันนี้แหละ คือ PivotCache)
- แต่ทีนี้ผมขอแนะนำให้ติ๊ก Refresh data when opening the file ด้วย เผื่อเราลืม Refresh นี่ซวยเลยครับ
พอ Save ไฟล์อีกทีก็จะเห็นว่าขนาดลดลงไปเยอะเลยครับ (ทำไมมันเล็กกว่า Original อีกเนี่ย งงจริงๆ 555)

แนวทางลดขนาดไฟล์ Excel # 5 :
ไม่ต้องเอาข้อมูลมาไว้ใน Excel โดยสิ้นเชิง

เวลาเราจะวิเคราะห์ข้อมูล ใน PivotTable จริงๆ แล้วเราสามารถ Pivot ข้อมูลจาก Source Data อื่นที่ไม่อยู่ใน Excel ได้ด้วยนะครับ (จะดึงผ่าน PivotTable เอง หรือจะผ่าน Power Query ก็ได้)

อันนี้เป็นตัวอย่างการใช้ Pivot Table เชื่อมเอง

และไฟล์นั้นสามารถใหญ่กว่า 1 ล้านแถวของ Excel ได้ด้วย (เช่น เชื่อมกับไฟล์ Access) แถมข้อดีอีกอย่างคือ ไฟล์ Excel จะมีขนาดเล็ก เพราะไม่ต้องเก็บ Data จริงๆ ไว้ด้วย
(ถ้า Pivot แล้วอย่าลืมเอา Pivot Cache ออกล่ะ ไม่งั้นก็ใหญ่อีกนะ)

แนวทางลดขนาดไฟล์ Excel # 6 :
ถ้าจะเก็บข้อมูลไว้ใน Excel ลองเอาเข้า Data Model ดูสิ

ถ้าแนวทางที่ 5 ดู Hard core เกินไป ลองพิจารณาลองเอาข้อมูลไว้ใน Data Model ของ Excel ดูสิ เพราะใน Data Model จะมีวิธีเก็บข้อมูลพิเศษ ที่จะเก็บเฉพาะข้อมูลที่ไม่ซ้ำๆ กันเท่านั้น ทำให้ไฟล์มีขนาดเล็กมากครับ

เราสามารถดึงข้อมูลเข้า Power Query ก่อน จากนั้นค่อยเลือก Load to…
แล้วเลือก Pivot Table Report หรือ จะ Only Create Connection ก็ได้ จากนั้นก็ให้ติ๊ก Add to Data Model ด้วย (ถ้าอยากเก็บไฟล์ไว้ใน Excel นะ)

จะเห็นว่าเราสามารถ Pivot Data ได้ และมีการ Load ไฟล์เข้า Data Model ไปแล้ว

จะเห็นว่าไฟล์มีขนาดเล็กลงพอสมควร แต่ไม่ได้เล็กเท่าเอา Data ไว้ข้างนอกแน่นอนอ่ะนะ…

แนวทางอื่นๆ ในการลดขนาดไฟล์ Excel

ใครมีวิธีอื่นๆ ที่น่าสนใจในการลดขนาดไฟล์อีก ก็สามารถ Comment บอกมาได้ครับ คนอื่นๆ จะได้นำเทคนิคเหล่านั้นไปใช้ได้ด้วย สำหรับบทความนี้ผมขอจบเท่านี้ครับ ขอบคุณที่อ่านจนจบนะ ^^
May 27, 2019
วิธีเปรียบเทียบข้อมูล 2 List ว่ามี item ไหนที่ต่างกัน?
“ไม่ทราบว่ามีวิธีเปรียบเทียบข้อมูลใน Excel จากข้อมูล 2 รายการหรือไม่ ว่ามี item ไหนต่างกันบ้างครับ?”

คำถามนี้มีคนถามบ่อยพอสมควร เลยเขียนเป็นบทความให้อ่านกัน คนอื่นๆ จะได้รับประโยชน์ไปด้วยเนอะ

เอาล่ะ! สมมติผมมีข้อมูลอยู่ 2 List ซึ่งอยู่คนละ Sheet ดังนี้
- SheetA
- SheetB
จะเห็นว่ามีทั้งรายการที่มีใน A แต่ไม่มีใน B รวมถึง มีใน B แต่ไม่มีใน A ด้วย

ผมจะเสนอวิธี Compare List นี้ 2 วิธีด้วยกัน คือ
1. วิธีใช้ VLOOKUP
2. วิธีใช้ Power Query

ซึ่งความพิเศษของบทความนี้ คือ ผมจะแสดง Step การแก้ปัญหาเหมือนกันเป๊ะๆ เลย แต่ใช้แค่เครื่องมือต่างกันเท่านั้นเอง

เพื่อนๆ จะได้เข้าใจว่า Power Query สามารถทำงานในลักษณะเดียวกับที่เราใช้ Excel ปกติได้ยังไง? ซึ่งเพื่อนๆ ก็จะรู้จักทั้งการ Apppend/ Remove Duplicates / Merge/ เขียนสูตร/Filter ใน Power Query ด้วยครับ

วิธีที่ 1 : ใช้ VLOOKUP เปรียบเทียบข้อมูล

Step 1 : เอาข้อมูลมารวมกันใน Sheet เดียว

ก่อนที่เราจะใช้สูตร VLOOKUP ค้นหาข้อมูล ผมขอนำ List จากทั้งสอง Sheet มารวมกัันก่อนใน Sheet ใหม่ ( ตั้งชื่อว่า Sheet All ) ด้วยการ Copy Paste ปกติ

จะเห็นว่ามันมีข้อมูลซ้ำๆ กันอยู่ ดังนั้นเราจะกด Remove Duplicates ก่อนจะทำงานต่อด้วยครับ โดยไปที่ [Data] => Data Tools => Remove Duplicates

Step 2 : หาข้อมูลว่าเจอใน List ที่สนใจหรือไม่

จากนั้นเราจะใช้ VLOOKUP หาว่ามีข้อมูลที่เราสนใจใน Sheet A หรือไม่?

ดังนั้นเราจะเขียนสูตรประมาณนี้ก่อนครับ
หาใน Sheet A =VLOOKUP(A2,SheetA!A:A,1,FALSE)

เราจะสามารถตีความผลลัพธ์ได้ว่า
- ถ้าขึ้น #N/A แปลว่า ไม่เจอข้อมูลใน Sheet A
- ถ้าไม่ใช่ #N/A แปลว่า เจอ ข้อมูลใน Sheet A
ในทำนองเดียวกัน ในอีก Sheet เราก็ทำเหมือนกัน ได้ว่า

หาใน Sheet B =VLOOKUP(A2,SheetB!A:A,1,FALSE)

Step 3 : ทำ Column สรุปผล เพื่อเลือกเฉพาะสิ่งที่เราสนใจ

ถ้าเราสนใจอยากจะ List ทุก item ที่ไม่เจอครบทั้ง 2 List ก็ใช้วิธีการสร้างคอลัมน์ใหม่ แล้วใส่เงื่อนไขแบบ OR ลงไปก็ได้ครับ

หลักการคือ เราจะตรวจสอบว่ามีตัวใดตัวหนึ่ง Error หรือไม่ แปลว่า อย่างน้อยหา PartNum ที่กำลังสนใจไม่พบใน Sheet ใด Sheet หนึ่ง

ดังนี้ =OR(ISNA(B2),ISNA(C2))

จากนั้นเราก็ Filter เฉพาะตัวที่เป็น TRUE ได้เลยครับ (แปลว่า อย่างน้อยหาไม่พบใน Sheet ใด Sheet หนึ่ง)

วิธีที่ 2 : ใช้ Power Query เปรียบเทียบข้อมูล

คราวนี้วิธีที่ 2 จะใช้ Power Query แทน ซึ่งมีข้อดีตรงที่สามารถกด Refresh เมื่อข้อมูลเปลี่ยนไปได้เลย (ไม่ต้องนั่ง copy paste /remove duplicates ใหม่อีกที) แต่หลักการคิดจะเหมือนกับวิธี VLOOKUP เลยครับ

Step 1 : เอาข้อมูลมารวมกันใน Sheet เดียว

ก่อนอื่นให้เอาทั้ง 2 List เข้าเป็น Query ก่อน (ทั้ง 2 อันเลย)

โดยตั้งชื่อ Query แต่ละอันเป็น TableA กับ TableB ด้วย (ทำทีละอัน) และให้ทำเป็น Load to…Only create Connection อย่างเดียว เราจะได้ไม่ต้องมีตารางออกมาเยอะแยะ เดี๋ยวงง

Tips : หลักการ คือ เราจะให้ผลลัพธ์สุดท้ายเท่านั้น ที่ออกมาเป็น Table จริงๆ นอกนั้นให้ create Connection อย่างเดียวนะครับ

พอได้ครบทั้ง 2 Query แล้ว ให้สร้าง Query ใหม่ แบบ Append ก่อน จะได้เห็นภาพครบทุก Item (การ Append คือ เอาข้อมูลมาต่อตูดกัน จำนวนแถวจะเพิ่มขึ้น เหมือนกัยการที่เรา Copy ข้อมูลทั้ง 2 Sheet มาต่อตูดกันแบบวิธีแรกนั่นแหละครับ)

เลือก Append Table A และ B เข้าด้วยกัน

ตอนแรกข้อมูลอาจจะออกมาซ้ำกัน (เพราะมันเอาสองตารางมาต่อตูดกันจริงๆ) ดังนั้นเราจะ Remove Duplicates ออกก่อน

Step 2 : หาข้อมูลว่าเจอใน List ที่สนใจหรือไม่

พอเราได้ List ที่สมบูรณ์และไม่ซ้ำกันแล้ว จากนั้นเราจะเอาไป Merge กับ Table A (เหมือน VLOOKUP หาข้อมูลใน Table A นั่นแหละ )

โดยที่ Step ในการ Merge เค้าจะให้เลือกว่า column ไหนใน 2 ตาราง ที่มีความสัมพันธ์กัน เราก็เลือกไปเลยว่าเป็น PartNum ทั้งคู่นั่นแหละ

พอกด Ok ตอนแรกมันจะออกมาเป็น Table ก่อน

ให้กด Expand เพื่อแตกเอาผลลัพธ์ออกมา (ตรงนี้จริงๆ เลือกได้ว่าจะเอาคอลัมน์ไหนกลับมาบ้าง แต่ในที่นี้เรามีคอลัมน์เดียว 555)

จะได้ผลลัพธ์ว่ามีการ Map เจอกับ Table A ตัวไหนบ้าง เหมือนกับตอนที่เรา VLOOKUP เลยมะ?

ทำอย่างนี้อีกทีกับ Table B เพื่อดูว่า Map เจอตัวไหนบ้าง

Step 3 : ทำ Column สรุปผล เพื่อเลือกเฉพาะสิ่งที่เราสนใจ

ถ้าเราอยากได้ตัวที่ตัวใดตัวหนึ่งเป็น Null ดังนั้นเราจะสร้างคอลัมน์ใหม่ โดย Add Column => Custom Column โดยเขียนสูตรแบบ or ดังนี้ (ตรงชื่อคอลัมน์ สามารถ double click จาก List ด้านขวาได้นะ ไม่ต้องพิมพ์เอง)

พอเรา Filter เลือกเฉพาะที่เป็น TRUE ก็จะได้ตัวที่เราสนใจครับ

จากนั้นถ้าจะโหลดออกมาเป็น Table ก็ Close & Load ได้เลยจบ เช่นเดียวกับตอนที่ใช้ VLOOKUP

แต่มีข้อดีกว่าคือ ถ้าเกิดมีข้อมูลเพิ่มขึ้นอีก เรากด Refresh ทีเดียวจบเลย!!
May 24, 2019
3 วิธีใช้ Excel ลบแถวว่าง (Remove Empty Row)
เราจะใช้ Excel ลบแถวว่าง ได้ยังไงกันนะ? วันนี้ผมขอนำเสนอ 3 วิธีเลยละกัน ซึ่งมีดังนี้
1. กด Sort ไปเลย แล้วแถวว่างจะไปกองด้านล่าง
  - ก่อน Sort ต้องคลุมพื้นที่ก่อนแล้วค่อยเลือก Filter นะครับ
  - เจ้าแถวว่างจะไปกองอยู่ด้านล่างของตารางเอง
  - วิธีนี้ต้องระวังว่าข้อมูลจะเรียงไม่เหมือนเดิม ยกเว้นทำเลข running เอาไว้ก่อนครับ
2. Filter Blank แล้วลบแถวว่างนั้นออก
  - วิธีนี้มีข้อเสียคือถ้าข้อมูลเยอะมากๆ เครื่องอาจจะ Hang ได้ครับ
3. ใช้ Power Query ในการ Remove Blank Row เพื่อลบแถวว่าง
  - วิธีนี้สบายใจสุดๆ ข้อมูลเปลี่ยนไปเราก็แค่กด Refresh เอง ข้อเสียคือต้องมี Power Query ถึงจะทำได้ครับ
May 23, 2019
รู้หรือไม่ว่า Slicer ก็ใช้กับ Table ได้นะ

หลายๆ คนยังไม่รู้จัก Slicer …

หลายๆ คนรู้จัก Slicer แต่นึกว่ามันใช้ได้กับ PivotTable อย่างเดียว….

จริงๆ แล้วมันใช้กับ Table ได้ด้วยนะครับ ลองดูคลิปนี้แล้วจะรู้ว่าทำยังไงครับ

Tips : ถ้าอยากจะแสดงบรรทัดสรุป ก็แค่ติ๊กคำว่า Total Row ใน Ribbon ของ Table เพิ่มแค่นั้นเองครับ

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

May 23, 2019
สารพัดเทคนิคสำหรับคนอยาก VLOOKUP ผลลัพธ์หลายค่า จากคำค้นหาเดียว
จะทำยังไงถึงจะทำ VLOOKUP ผลลัพธ์หลายค่า จาก Lookup_Value เดียวได้นะ?? นี่คือคำถามที่เกิดขึ้นบ่อย เพราะปกติแล้ว เวลาเราใช้ VLOOKUP หรือจะ INDEX MATCH ก็แล้วแต่ หาก Lookup_Value ในตารางอ้างอิงมันซ้ำกันหลายตัว กรณีแบบ Exact Match มันจะได้ผลลัพธ์เป็นตัวบนสุดตัวเดียวเสมอ…

บทความนี้ผมจะมาบอกวิธีเพื่อแสดงผลลัพธ์หลายบรรทัดจากคำค้นหาเดียวให้เอง และจะทำให้ดูหลายแบบด้วยครับ!! ซึ่งมี VLOOKUP อยู่แค่แบบเดียวเท่านั้น นอกนั้นผมใช้วิธีอื่นหมดเลย 555 (ก็จริงๆ แล้ว VLOOKUP มันไม่ได้เหมาะกับเคสแบบนี้นี่นา)

โจทย์คือแบบนี้ครับ เราต้องการเลือกสินค้า แล้วให้แสดง Order ID ทั้งหมดของสินค้านั้นออกมา (ถ้าเราเอา Order ID ออกมาได้แล้ว อย่างอื่นก็คงไม่ยากแล้วจริงมั้ยครับ?)

บอกไว้ก่อนว่า วิธีกลุ่มที่ 1 ซึ่งคือการเขียนสูตรนั้นยากกว่ากลุ่มที่ 2 ที่ใช้เครื่องมือพอสมควรนะครับ ใครเน้นง่ายก็ไปดูวิธีกลุ่มการใช้เครื่องมือได้เลย แต่ถ้าใครอยากรู้วิธีเขียนสูตรก็อ่านต่อไปได้เลย

กลุ่ม 1 : ใช้การเขียนสูตรเพื่อให้เกิดผลลัพธ์หลายค่า

1.1 ใช้ FILTER (ต้องมี Excel 365 หรือ Excel for web )

ถ้าเรามี Excel365 วิธีที่ง่ายสุดๆ สำหรับการเขียนสูตรคือใช้ฟังก์ชัน FILTER นั่นเองครับ ไม่ต้องไปใช้ VLOOKUP นะ (ก็ VLOOKUP มันได้ทีละค่าไง…)
```
=FILTER(array,include,[if_empty])
=FILTER(พื้นที่ผลลัพธ์,เงื่อนไขที่ต้องการ,[ถ้าไม่มีผลลัพธ์เลยให้แสดงอะไร])
```
ตรงเงื่อนไขที่ต้องการ เราใส่เป็น TRUE/FALSE หรือ 1,0 ก็ได้ นะครับ และไม่จำเป็นต้องอยู่ใน array ผลลัพธ์ก็ได้นะครับ

เช่น ผมต้องการ order_id ที่สินค้าเป็นแอปเปิ้ล ผมก็เขียนแบบนี้ได้เลย ใน H6
```
=FILTER(B3:B14,C3:C14=G3)
```
จะเห็นว่าเงื่อนไข C3:C14 ไม่ได้อยู่ใน B3:B14 นะครับ แค่ต้องจับคู่กันให้ได้เท่านั้นเอง

สังเกตว่าผลลัพธ์จะงอกออกมาหลายตัวเองเลยอัตโนมัติ ซึ่งเรียกว่า Spill ซึ่งเป็นความสามารถของ Excel 365 ที่เรียกว่า Dynamic Array ครับ ใครสนใจลองอ่านบทความนี้เพิ่มเติมได้

ถ้าอยากได้ผลลัพธ์หลายคอลัมน์แบบต่อเนื่องกัน ก็แค่ทำให้ array ผลลัพธ์มีหลายคอลัมน์แค่นั้นเอง
```
=FILTER(B3:E14,C3:C14=G3)
```
แต่ถ้ากรณีอยากได้ผลลัพธ์หลายคอลัมน์ที่ไม่ต่อกัน ก็จะต้องพลิกแพลงมากขึ้น ดังนี้

1.1.1 วิธีใช้ FILTER + FILTER

เราสามารถใช้ FILTER 2 รอบ เพื่อเลือกเฉพาะบางคอลัมน์ที่ต้องการได้ด้วยครับ (ขอบคุณ Excel Wizard สำหรับเทคนิคนี้)
```
=FILTER(FILTER(input สูตร Filter แถว),{เลือกว่าเอาคอลัมน์ไหนบ้าง เป็น 1,0 หรือ TRUE,FALSE})
```
ตรง Array ผลลัพธ์ผมเปลี่นเป็น B3:E14 ให้คลุมพื้นที่ทั้งหมด ซึ่งมี 4 คอลัมน์
แต่ผมจะเอาแค่ Order id วันที่ ผู้ขาย ซึ่งอยู่คอลัมนืที่ 1,3,4 หรือไม่เอาคอลัมน์ที่ 2 จึงเขียนว่า {1,0,1,1}

ถ้าไม่อยากมานั่งเลือก 1,0 เอง ก็ใช้ MATCH มาช่วยหาว่ามีในเป้าหมายหรือไม่ แล้วใช้ ISNUMBER แปลงเป็น TRUE, FALSE ก็ได้ ดังนี้
```
=FILTER(FILTER(B3:E14,C3:C14=G3),ISNUMBER(MATCH(B2:E2,H5:J5,0)))
ซึ่งตรง ISNUMBER นั้นถ้าลองกด F9 ดูจะเห็นเป็น {TRUE,FALSE,TRUE,TRUE} ซึ่งก็คือ {1,0,1,1} นั่นเอง
```
วิธีหลังจะดีกว่าตรงที่มัน Dynamic เปลี่ยนคอลัมน์ที่ต้องการได้ (แต่ลำดับยังเรียงเหมือนเดิมนะครับ)
```
=FILTER(FILTER(B3:E14,C3:C14=G3),{1,0,1,1})
```
1.1.2 วิธี FILTER+CHOOSE

สมมติผมอยากจะได้ Order id, ผู้ขาย, วันที่ (สลับเอาวันที่มาอยู่หลัง) ผมคิดว่าใช้ FILTER+CHOOSE น่าจะง่ายสุด ดังนี้

หลักการคือใช้ CHOOSE สร้าตารางจำลองขึ้นมาก่อน แล้วค่อย FILTER ตารางนั้น ซึ่งการสร้างตารางจำลองด้วย CHOOSE สามารถใช้ Array Formula มาช่วยได้ โดยใส่ว่าจะเอากี่คอลัมน์ เช่น เอา 3 คอลัมน์ก็ใส่ {1,2,3} แล้วก็เลือกเลยว่าจะเอาคอลัมน์ไหนเป็นคอลัมน์ 1,2,3

เช่น
```
=CHOOSE({1,2,3},B3:B14,E3:E14,D3:D14)
```
แบบนี้จะเป็นการสร้างตารางจำลองขึ้นมาใหม่ได้แล้ว

จากนั้นก็เอา FILTER ไปครอบ ดังนี้
```
=FILTER(CHOOSE({1,2,3},B3:B14,E3:E14,D3:D14),C3:C14=G3)
```
เท่านี้เราก็ได้ผลลัพธ์ตามต้องการแล้ว ซึ่งการใช้ FILTER น่าจะเหมาะกับคนที่มี Excel365 เท่านั้น หากใครมี Version เก่ากว่านี้ ก็ไปดูข้อถัดไปได้เลย

1.2 ใช้ VLOOKUP + Helper Column

ในเมื่อ ปัญหาคือมี lookup_value ซ้ำกันหลายตัว ใน table_array ดังนั้นหลักการในการแก้ไขก็คือ ทำให้ข้อมูลไม่ซ้ำกันซะก่อน เช่น แอปเปิ้ล มีซ้ำกัน 4 ตัว ดังนั้นเราจะทำให้มันไม่ซ้ำกัน เช่น ทำให้เป็น แอปเปิ้ล1 แอปเปิ้ล2 แอปเปิ้ล3 แอปเปิ้ล 4 ซะ

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

สังเกตในสูตรช่อง A7 ในรูปข้างล่าง :
```
=COUNTIFS($C$3:C7,$G$3)
```
ส่วน criteria_range1 ผมใส่เป็น $C$3:C7 ซึ่งมีการ Fix ตำแหน่งของ C3 ไว้ แต่ไม่ Fix ที่ C7 ทำให้เวลา Copy ลงมาข้างล่าง Range จะขยายเพิ่มขึ้นเรื่อยๆ

ปล. วิธีนี้จะเข้าใจง่ายกว่า 1.3 แต่ว่าจะเปลืองคอลัมน์มากกว่า ยังไงลองเลือกดูนะครับ

ต่อไป เราเอาชื่อสินค้าในบรรทัดนั้นๆ ไปเชื่อมกับเลขลำดับ ด้วยการใช้ &
สูตรในช่อง A3 :
```
=C3&"-"&COUNTIFS($C$3:C3,$G$3)
```
(ที่ใช้ – คั่นเพื่อความปลอดภัย เผื่อชื่อสินค้าเป็นตัวเลขแล้วจะงง)

เราก็จะได้ผลลัพธ์แบบนี้

ทีนี้เราก็ทำการ VLOOKUP ได้แล้ว เพราะค่าในคอลัมน์ A ไม่ซ้ำกันเลย (ในผลไม้ที่เราสนใจ) ดังนั้นสูตรใน H6 จะเป็นดังนี้ :
```
=VLOOKUP($G$3&"-"&G6,A:B,2,FALSE)
```
ซึ่งในส่วน lookup_value เราเขียนสูตรเอาสินค้าที่สนใจ ไปเชื่อมกับเลขลำดับ ว่า
$G$3&”-“&G6 เพื่อให้ผลลัพธ์ออกมาเหมือนกับในคอลัมน์ A นั่นเอง

ถ้าไม่อยากให้ Error ก็อาจใส่ IFERROR ดักไปก็ได้ เช่น
```
=IFERROR(VLOOKUP($G$3&"-"&G6,A:B,2,FALSE),"-")
```
1.3 ใช้ ROW กับ SMALL มาช่วย แบบ Array Formula

ถ้าเราใช้วิธีนี้ ก็จะไม่ต้องสร้างคอลัมน์เพิ่มเลย แต่ก็ต้องมีความเข้าใจเกี่ยวกับ Array Formula พอสมควร

หลักการคือ เราจะใช้ IF + ROW เพื่อ แสดงแถวของค่าที่ตรงกับ lookup value เท่านั้น และใช้ SMALL เพื่อเอาแถวที่น้อยสุดเป็นอันดับที่ 1,2,3 ขึ้นมาแสดง

ก่อนแื่น เราใช้ IF + ROW เพื่อ แสดงแถวของค่าที่ตรงกับ lookup value ก่อน ดังนี้
```
=IF(C3:C14=G3,ROW(C3:C14))
```
จะเห็นว่ามันแสดงค่าแถวออกมาเฉพาะลำดับที่ตรงกับแอปเปิ้ล แต่มันยังแสดงแบบเว้นๆ กันอยู่

ต่อไปเราจะใช้ SMALL มาช่วย เพื่อให้มันแสดงค่าที่น้อยสุดเป็นอันดับที่ 1,2,3 และอย่าลืม fix cell reference ด้วย เพราะเดี่ยวจะ copy ลงล่าง
```
=SMALL(IF($C$3:$C$14=$G$3,ROW($C$3:$C$14)),G6)
```
เท่านี้ผมก็ได้ลำดับแถวที่ตรงกับแอปเปิ้ลแล้ว คือ 3,4,11,14

จากนั้นผมก็ใช้ INDEX ครอบเข้าไปเพื่อ ดึง Order id จากลำดับแถวที่รู้จาก SMALL+ROW ได้เลย
```
=INDEX(B:B,SMALL(IF($C$3:$C$14=$G$3,ROW($C$3:$C$14)),G6))
```
กลุ่ม 2 : ใช้เครื่องมือเพื่อให้เกิดผลลัพธ์หลายค่า

2.1 ใช้ Slicer (Excel 2013 ขึ้นไป)

วิธีนี้จะเรียกว่าโกงก็ได้ มันคือการแปลง Data ให้เป็น Table แล้วใส่ Slicer จบเลย 555

เลือกข้อมูล 1 ช่อง กด Insert –> Table หรือ Ctrl+T

กด Insert Slicer แล้วเลือกสินค้า

จากนั้นเลือกสินค้าที่สนใจได้อย่างง่ายดาย

เนื่องจากวิธีนี้ดูขี้โกงไปหน่อย งั้นแถมวิธีใช้ Power Query ให้ละกันครับ

2.2 ใช้ Power Query (Excel 2013 ขึ้นไป)

ก่อนอื่น เราต้องเอาข้อมูลสินค้าที่เราสนใจเข้า PowerQuery ด้วย ก่อน
ด้วยการ Get Data from Table/Range ดังรูป

ผมตั้งชื่อตารางแรกว่า OrderDatabase แล้ว กด Close & Load to… Only Create Connection เพื่อให้ยังไม่ต้องสร้างตารางผลลัพธ์ออกมา

จากนั้น เอาข้อมูลสินค้าที่เราสนใจเข้า PowerQuery ด้วย Get Data From Table อีกที

แล้วตั้งชื่อว่า SelectedProduct แล้ว Close&Load to… only create connection อีกที

จากนั้นเราจะใช้ Merge Query ดึงข้อมูลมาเฉพาะสิ่งที่สนใจ

ให้เราไปสร้าง Merge Query ขึ้นมาใหม่

จากนั้นก็เลือกทั้งสองตาราง แล้วเลือกคอลัมน์ที่เป็นตัวเชื่อมซะ ซึ่งก็คือสินค้า แล้วเลือก Join Kind เป็น Inner (แปลว่าต้องเจอค่าในทั้ง 2 ตาราง) แล้ว กด ok

กดปุ่มขวาบนตามรูป และติ๊กว่าไม่เอา prefix (เพราะมันอ่านยาก)

ลบคอลัมน์ที่ไม่ต้องการซะ เช่น สินค้า แล้วถ้าต้องการเลข running ก็ Add Index Column From 1 ได้

กด Close & Load to … Table แล้วเลือกให้วางยัง Existing Worksheet ตรงที่ต้องการ แล้วกด ok จบ

จากนั้นเราก็จะได้ผลลัพธ์ออกมาครับ

แต่วิธีนี้มีข้อเสียคือ ถ้าเปลี่ยนสินค้าแล้ว อย่าลืมกดคลิ๊กขวาที่ตารางแล้ว Refresh นะ (ยกเว้นจะผูกกับ VBA ก็อาจจะช่วยได้)

เพียงเท่านี้ก็เสร็จแล้วครับ

สรุปสารพัดวิธี VLOOKUP ผลลัพธ์หลายค่า

เพื่อนๆ ชอบวิธีไหนในการได้ผลลัพธ์หลายค่ามากกว่ากันเอ่ย? หรือใครมีวิธีที่ดีกว่านี้ก็แชร์กันได้นะครับ ^^ ส่วนตัวแล้วผมจะต้องจัดการทุกอย่างใน Power Query ก่อนจะส่งไป Pivot อยู่แล้ว ดังนั้นผมจึงชอบวิธี Power Query มากที่สุดแล้วครับ เพราะทั้งง่ายและทรงพลัง
May 9, 2019
วิธีรวบรวมข้อมูลจากหลายคอลัมน์มารวมเป็นคอลัมน์เดียว

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

จะทำเรื่องพวกนี้ด้วยวิธีเขียนสูตรมันก็พอทำได้ แต่ว่ามันจะง่ายกว่ามากเลยถ้าจะใช้ Power Query ทำงานนี้ ซึ่งใครๆ ก็ทำได้ครับ

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

1.ก่อนอื่นสมมติว่ามีมีข้อมูลดังนี้ ผมคลิ๊กที่ช่อง A1 เอาไว้ก่อน

2. จากนั้นผมกด [Data] –> Get Data From Table/Range

3. เนื่องจากข้อมูลผมยังไม่มีหัวตาราง ก็ไม่ต้องไปติ๊กว่ามี Header นะ แล้วกด Ok โลด!

4. พอเข้า Power Query ก็กด Add Column -> Index Column ซะ เพื่อสร้างเลข running

5. ขณะที่กำลังเลือก Index Column อยู่นั้น ให้เลือก Unpivot Other Column

6. จากนั้นข้อมูลทุกอัน จะมาเรียงอยู่ในคอลัมน์เดียวกันอย่างง่ายดาย

7. ทีนี้เราก็ลบคอลัมน์ที่ไม่ต้องการทิ้งได้เลย โดยเลือกแล้วกด Del ธรรมดา

8. ทีนี้ถ้าอยากได้คำว่าแมว ก็ Filter แล้วเลือก Contain คำว่า แมว (อย่าใช้วิธี Search นะ!!)

9. แค่นี้ก็จะได้เฉพาะข้อมูลที่ต้องการแล้ว ถ้าอยากจะ Sort ด้วยก็แล้วแต่เลย

จากนั้นกด Close & Load ได้เลย

ข้อมูลจะโผล่มาที่อีกชีทนึง ซึ่งจะแสดงแค่ผลลัพธ์ที่เราต้องการ

ถ้าข้อมูล Input มีเพิ่มอีกล่ะ???

ถ้าข้อมูล Input มีเพิ่มอีก เช่น ในหน้า Input ผมเพิ่มช่อง D5,D6 เข้าไป

เราก็ไปที่ตารางผลลัพธ์ แล้วกด Click ขวา Refresh แค่นั้นก็จบครับ !!

นี่ไง มาแล้ววววว!!!

เป็นไงบ้างครับ กับ Power Query จะเห็นว่ามันง่ายมากๆ ใครๆ ก็สามารถเรียนรู้ได้ครับ แถมถ้าใช้ Power Query พื้นฐาน ก็ไม่ต้องยุ่งกับการเขียนสูตรเลยด้วย !!

May 5, 2019
วิธีตรวจสอบรุ่นของโปรแกรม Excel (Excel Version) ของคุณ

เวลาผมถามว่าใช้ Excel Version ไหนอยู่? หลายๆ คนก็ไม่รู้จริงๆ ว่ามันตรวจสอบยังไงกันนะ? ดังนั้นในบทความนี้ผมจะบอกวิธีตรวจ Version ของ Excel ให้คุณเองครับ

วิธีที่ 1 : ดูที่หน้าตาคร่าวๆ ก่อน

ถ้าเป็น Excel version เก่าๆ หน่อย ซึ่งหน้าตาจะต่างกันค่อนข้างชัดเจนครับ ดูด้วยตาเปล่าได้ง่ายๆ เลย ดังนี้

Excel 2003

Excel 2003

Excel 2007

Excel 2007

Excel 2010

Excel 2010

Excel 2013 ขึ้นไป (หน้าตาปุ่มจะแบนๆ คล้ายๆ กัน )

แนะนำให้ใช้อีกวิธีตามข้างล่างในการตรวจสอบ

Excel 2013 ขึ้นไป

วิธีที่ 2 : ดูที่ข้อมูลเลข Version

เหมาะกับการดู Excel 2013 ขึ้นไป ซึ่งหน้าตาค่อนข้างคล้ายกัน
ดังนั้นดูที่ File -> Account -> ดูด้านขวา จะง่ายกว่า
(version 2010 อยู่ที่ File -> Help –> ดูด้านขวา)

เราจะดูได้ว่าเป็น Excel version ไหน เช่น 2013, 2016, Office 365 ??
และตรง About Excel ยังดูได้ด้วยว่าเป็น 32bit หรือ 64bit

สำหรับ Office 365 จะเป็นแบบที่มีการอัปเดทใหม่อยู่เรื่อยๆ ครับ แต่ว่าเราเสียตังเป็นค่าเช่าไปเรื่อยๆ แทน ไม่ใช่การซื้อขาด

เพียงเท่านี้ เพื่อนๆ ก็จะรู้ได้แล้วว่าเราม่ี Excel Version ไหนนะครับ

รู้ Version แล้วไงต่อ?

หลังจากรู้ Version แล้ว เราก็จะสามารถเลือกลง Add in ต่างๆ ได้ถูกต้องมากขึ้น เช่น Excel 2016 ขึ้นไปจะมี Power Pivot และ Power Query มาในตัวอยู่แล้ว

ถ้า Version เก่ากว่านั้น ต้องไป Download add-in มาลงเพิ่มเอง ดังนี้

Power Query (Excel 2010 ขึ้นไป)
https://www.microsoft.com/en-US/download/details.aspx?id=39379

Power Pivot (Excel 2010 ขึ้นไป)
https://www.microsoft.com/en-us/download/details.aspx?id=7609

May 5, 2019
สอน Excel ผ่าน Avengers : พลัง Infinity Stone ทั้ง 6 ใน Excel (ไม่สปอยล์ End Game)
วันนี้ผมจะมาสอนเทคนิค Excel โดยใช้เทคนิคต่างๆ ที่ ใช้ Gimmick เรื่อง Infinity Stone ทั้ง 6 ที่แต่ละอันมีความสามารถพิเศษแตกต่างกัน จากหนังเรื่อง Avengers กำลังมาแรงมากๆ และกำลังกวาดรายได้อย่างถล่มทลาย

แต่เพื่อไม่ให้เป็นการสปอยล์คนที่ยังไม่ได้ดู ผมจะยังไม่พูดถึงเนื้อหาในภาค End Game แต่จะขอนำ มาผสมผสานเพื่อนำเสนอความรู้ Excel แบบสนุกสนาน เพื่อให้คนที่ยังไม่ได้ดู Avengers ภาค End Game ก็สามารถอ่านได้ครับ

เพื่อไม่ให้เสียเวลา เราไปดูกันเลย!!

Space Stone = Go to

ความสามารถในหนัง : พาผู้ใช้งานวาร์ปไปโผล่ที่ไหนก็ได้

ใน Excel เราก็มีเครื่องมือที่คล้ายกับเจ้า Space Stone นี้มากๆ เลย นั่นก็คือ เครื่องมือ Go to… ซึ่งมีความสามารถในการไปที่ Cell ไหนก็ได้นั่นเองครับ โดยเครื่องมือ Go to นี้สามารถเรียกใช้งานง่ายๆ ได้ด้วยการกดปุ่ม F5

พอกด F5 แล้ว เราสามารถพิมพ์ Cell reference ที่ต้องการจะวาร์ปไปได้ หรือจะเลือก Defined Name ที่ตั้งไว้แล้วก็ย่อมได้

แต่ความสามารถที่แท้จริงของ Go to นี้อยู่ในปุ่ม Special… ซึ่งมีลูกเล่นอีกมหาศาล เช่น เลือก Cell ที่มีค่า Blank, เลือก Cell ที่เป็น Text, เลือก Cell ที่มีค่าต่างจากช่องอื่น และอีกมากมาย

โดยเราสามารถนำไปประยุกต์ได้หลายเรื่อง เช่น เติมเต็มข้อมูลในช่องว่างให้เหมือนค่าข้างบน เป็นต้น

ใครอยากรู้เพิ่มเติม อ่านรายละเอียดได้ที่นี่

Time Stone = Undo Redo

ความสามารถในหนัง : ควบคุมเวลา สามารถย้อนเวลาไปอดีต หรือ ไปอนาคตได้

ใน Excel เราก็มีเครื่องมือที่ทำตัวคล้ายๆ กับ Time Stone อยู่เหมือนกันนะ นั่นก็คือ เครื่องมือ Undo, Redo นั่นเอง

เวลาเราทำอะไรผิดพลาดแล้วอยากจะย้อนกลับไปแก้ไข เราสามารถกด Undo หรือ Ctrl+z ได้ และถ้าหากเราย้อนเกิน เราก็กด Redo (Ctrl+y หรือ F4) เพื่อทำอีกทีได้ และถ้า Redo ไปเรื่อยๆ เราก็สามารถทำ Action นั้นซ้ำ แม้ว่าไม่เคย undo มาก่อน ซึ่งเรียกว่า Repeat นั่นเอง

Tips : ปกติแล้ว Excel จะตั้งค่าให้ Undo ได้ถึง 100 ครั้ง และเราสามารถเพิ่มค่านี้ได้ด้วยการไปแก้ Registry ซึ่งใครสนใจลองไป Google ดูได้ แต่ผมว่าไม่ได้จำเป็นมากนะ

สิ่งสำคัญกว่าคือ การต้องรู้ว่า เราไม่สามารถ Undo ย้อนเวลาไปได้ทุกเรื่องนะครับ เช่น Action ที่เกิดจากคำสั่ง File เช่น การ Save เนี่ย Save แล้วเราจะ Undo ไปก่อน Save ไม่ได้ นอกจากนี้คำสั่งจากพวก Macro หรือ VBA ก็ไม่สามารถ Undo ได้ ดังนั้นจะทำอะไรก็อย่าลืม Save ไฟล์ Backup ไว้ดีๆ นะครับ

Reality Stone = เปลี่ยน Format

ความสามารถในหนัง : เปลี่ยนแปลงความจริงได้

สิ่งที่ดูคล้ายกับเจ้า Reality Stone ใน Excel ก็คือ เรื่องของการเปลี่ยน Format ให้กับ Cell ต่างๆ นั่นเอง

ใน Cell หนึ่งๆ ของ Excel นั้น จริงๆ แล้วมีส่วนประกอบอยู่สองส่วนด้วยกัน นั่นคือ Content (เนื้อหา) กับ Format (รูปแบบการแสดงผล) ซึ่งสำหรับผมแล้ว Content คือ ความจริง ส่วน Format เป็นสิ่งที่สามารถทำให้เราเห็นสิ่งต่างๆ เปลี่ยนไปจากความจริงได้

ตัวอย่าง

ถ้าเราใส่ค่าจริงๆ ลงไปใน Cell ว่า 3.5 แต่ลองเปลี่ยน Format ให้ไม่มีทศนิยม เราก็จะเห็นเป็นเลข 4 ทั้งๆ ที่จริงๆ ค่ามันยังเป็น 3.5 อยู่ (รายละเอียดลองดูคลิปข้างล่าง)

ค่าวันที่ที่เราเห็นเป็น 28/4/2019 จริงๆ แล้วถูกเก็บค่าเป็นตัวเลขธรรมดา เช่น 43583 (Excel จะเก็บค่า 1/1/1900 เป็น เลข 1 รายละเอียดอ่านได้ที่นี่)

Power Stone = Power Tools

ความสามารถในหนัง : เพิ่มพลังให้ผู้ใช้งานแข็งแกร่งมากขึ้น

ใน Excel เราก็มีเจ้า Power Stone เหมือนกัน และชื่อของมันก็คล้ายกันด้วย นั่นก็คือ เครื่องมือ Power Tools ต่างๆ ไม่ว่าจะเป็น
- Power Query : มีความสามารถในการรวบรวมข้อมูลจาก Data Source หลายๆ รูปแบบได้ อีกทั้งยังดัดแปลงข้อมูลจากข้อมูลที่อยู่ในรูปแบบเน่าๆ ให้กลายเป็นรูปแบบที่เหมาะสมต่อการเอาไปทำงานต่อ เช่น PivotTable ได้
- Power Pivot : มีความสามารถในการวิเคราะห์ข้อมูลได้เหนือกว่า PivotTable ธรรมดา เนื่องจากมันสามารถใช้ Data Model มาสร้าง Relationship เพื่อผูกความสัมพันธ์ให้ตารางข้อมูลของเราได้ อีกทั้งยังมีภาษา DAX Formula มาสร้าง Calculated Column และ Measure ซึ่งมีฟังก์ชันที่ Excel ปกติไม่มี แต่ใช้ง่ายกว่า Excel มาก เช่น ใน Excel การเขียน VLOOKUP ที่ต้องใช้ Input 4 ตัวกว่าจะเขียนจบ แต่ใน DAX มีคำสั่ง RELATED ที่ทำงานเหมือน VLOOKUP เลย แต่ใช้ Input แค่ตัวเดียว
- Power BI : เป็นโปรแกรม Business Intelligent (BI) ซึ่งแยกออกมาจาก Excel โดยสามารถโหลดใช้ฟรีได้เลยสำหรับ version desktop ซึ่งมีความสามารถของ Power Query และ Power Pivot อยู่ด้วยกัน แถมยังสามารถสร้างกราฟและ Dashboard แบบ Interactive ได้ง่ายกว่า Excel มาก อีกด้วย
ใครที่สนใจ สามารถอ่านรายละเอียดของเจ้า Power Tools ทั้งหลายนี้ได้ที่นี่

ในบทความจะมีคลิปวีดีโอสอนด้วยครับ เช่น

Mind Stone = VBA

ความสามารถในหนัง : ควบคุมจิตใจของผู้อื่นให้ทำตามสิ่งที่เราต้องการได้

ใน Excel ผมมองว่า เครื่องมือที่ทำสิ่งที่คล้ายกับ Mind Stone ทำได้ ก็คือการเขียนโปรแกรม VBA นั่นเอง

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

คนที่เขียนโปรแกรมไม่เป็นเลย ก็ไม่ต้องกลัว ทุกคนสามารถเริ่มต้นเรียนรู้ได้ครับ และโชคดีมากๆ ที่ Excel มีเครื่องมือ Macro Recorder ที่สามารถ Record หรือบันทึกการกระทำของเราแล้วแปลงให้กลายเป็น VBA Code ได้โดยอัตโนม้ติ และถ้าเราเรียนรู้ VBA อีกเล็กน้อย เราก็สามารถดัดแปลง Code ที่อัดไว้แล้วให้กลายเป็นโปรแกรมที่ฉลาดขึ้นได้

ซึ่งใครที่สนใจรายละเอียดเพิ่มเติม สามารถอ่านบทความสองอันนี้ได้ครับ
- 10 CONCEPTS เริ่มต้นเรียนรู้การเขียนโปรแกรมด้วย EXCEL VBA
- EXCEL VBA ฺBasic : แผนดีมีชัยไปกว่าครึ่ง
ในบทความจะมีคลิปวีดีโอสอนด้วย เช่น

Soul Stone = Array Formula

ความสามารถในหนัง : ควบคุมและกักเก็บวิญญาณได้

มณีเม็ดนี้ในหนังมีความพิเศษมากกว่าก้อนอื่นมาก และความสามารถก็ไม่ค่อยชัดเจนมากนัก แต่เท่าที่อ่านดูในเว็บเหมือนจะม่ีความสามารถในการสร้างโลกจำลองแล้วกักเก็บวิญญาณไว้ในนั้นได้

สำหรับผมแล้ว ใน Excel เราก็สามารถสร้างโลกจำลองขึ้นมาได้เช่นกัน และในโลกจำลองนั้นเราสามารถเก็บข้อมูลหลายๆ อันลงไปได้ ซึ่ง การเก็บข้อมูลเป็นชุดลงไปในสิ่งเดียว ใน Excel เรียกว่า Array นั่นเอง (แถสุดๆ 555)

เวลาที่เราเขียนสูตรที่รองรับข้อมูลแบบ Array หรือที่เรียกว่า Array Formula นั้น ปกติเราจะต้องกดปุ่มพิเศษ คือ Ctrl+Shift+Enter แทนที่จะกด Enter ธรรมดาเหมือนสูตรปกติ

สมมติว่าเราต้องการหาราคาที่มากที่สุดของสินค้าที่ชื่อขึ้นต้นด้วยตัว T จากข้อมูลต่อไปนี้ เราก็สามารถทำได้โดยเขียนสูตรทุกอย่างอยู่ในช่องเดียว (เหมือนสร้างโลกจำลองขึ้นมาในช่องเดียว)

เราก็สามารถเขียน LEFT เอาตัวแรกออกมาเทียบว่าเป็นตัว T รึเปล่า? แล้วใช้ IF เช็คว่าถ้าใช่ ก็ให้ดูค่าราคา (ถ้าไม่ใช่ ก็ปล่อยเป็นเป็น FALSE)
จากนั้นก็เอา MAX มาครอบเพื่อหาค่าสูงสุดได้เลยในช่องเดียวด้วย Array Formula โดยเขียนสูตรว่า
=MAX(IF(LEFT(A2:A7,1)=”T”,B2:B7)) แล้วกด Ctrl+Shift+Enter ครับ

ซึ่งถ้าค่อยๆ ลอง Highlight สิ่งที่สนใจแล้วลองกด F9 ดูจะเห็นว่ามันเก็บข้อมูลไว้มากมายในโลกของ Array ครับ
รวบรวม Infinity Stone ครบ 6 ชิ้นแล้ว ก็ดีดนิ้วได้!!

ความสามารถในหนัง : สามารถทำให้สิ่งมีชีวิตครึ่งจักรวาลหายไปในแบบสุ่ม

สำหรับ Excel แล้ว ความสามารถในการสุ่มข้อมูลสามารถทำได้ด้วยฟังก์ชัน RAND หรือ RANDBETWEEN ก็ได้ครับ ซึ่งทั้งสองฟังก์ชันนี้จะสุ่มตัวเลขออกมาด้วยการแจกแจกแบบ Uniform Distribution หรือว่าทุกค่ามีโอกาสเท่ากันหมดนั่นเอง แต่สองตัวนี้จะต่างกันดังนี้
- =RAND() : จะทำการสุ่มค่าตั้งแต่ 0 ถึง 1 ออกมาเป็นทศนิยม
- =RANDBETWEEN(bottom,top) : จะสุ่มตัวเลขจำนวนเต็มระหว่างค่า Bottom กับ Top ที่กำหนด
วิธีที่ 1

ดังนั้นถ้าเรามีข้อมูลอยู่ชุดหนึ่ง แล้วอยากจะสุ่มว่าข้อมูลไหนจะได้อยู่ต่อ ข้อมูลไหนจะต้องหายไป บางทีเราก็สามารถเขียนสูตรง่ายๆ ได้ดังนี้

=CHOOSE(RANDBETWEEN(1,2),”อยู่ต่อ”,”หายไป”)

ซึ่งสูตรดังกล่าวจะสุ่มเลขระหว่าง 1 กับ 2 ออกมาด้วย RANDBETWEEN (ความน่าจะเป็นครึ่งๆ ระหว่าง 1 กับ 2) แล้วใช้ CHOOSE เลือกว่าถ้า 1 ให้อยู่ต่อ ถ้า 2 ให้หายไป

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

วิธีที่ 2

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

=IF(RANK.AVG([@[เลขสุ่มด้วย RAND]],[เลขสุ่มด้วย RAND])<=(COUNTA([สิ่งมีชีวิต])/2),”อยู่ต่อ”,”หายไป”)

และถ้าใครอยากให้ข้อมูลมันดูเหมือนหายไปจริงๆ อาจใช้ Conditional Format เขียนเพิ่ม โดยใส่ Custom Number Format เป็น ;;; (; 3 ตัว) ก็ได้ครับ เพื่อให้มองไม่เห็นค่านั้นๆ

พลังของ Infinity Stone แห่ง Excel อยู่ในมือคุณแล้ว

ยิ่งเราเชี่ยวชาญแต่ละหัวข้อมากเท่าไหร่ เราก็จะยิ่งสามารถนำไปพลิกแพลง ต่อยอดเรื่องต่างๆ ได้มากเท่านั้นครับ หวังว่าเพื่อนๆ เองจะรวบรวมความรู้เกี่ยวกับ Infinity Stone ใน Excel แต่ละเม็ด เพื่อปลดปล่อยพลังสูงสุดของ Excel เพื่อนๆ ออกมาให้ได้นะครับ

ไว้ Avengers : End Game ออกจากโรงเมื่อไหร่ ไว้ผมจะมาเขียนบทความถึง End Game อีกทีครับ ซึ่งรับรองว่ามีเรื่องเอามาประยุกต์ได้อีกเพียบ!!
April 28, 2019
เจาะลึกเครื่องมือ Go to ใน Excel
ใน Excel เรามีเครื่องมือ Go to… ซึ่งมีความสามารถในการไปที่ Cell ไหนก็ได้นั่นเองครับ โดยเครื่องมือ Go to นี้สามารถเรียกใช้งานง่ายๆ ได้ด้วยการกดปุ่ม F5 (หรือ Ctrl+G)

พอกด F5 แล้ว เราสามารถพิมพ์ Cell reference ที่ต้องการจะวาร์ปไปได้ หรือจะเลือก Defined Name ที่ตั้งไว้แล้วก็ย่อมได้

แต่ความสามารถที่แท้จริงอยู่ในปุ่ม Special… ที่สามารถมีลูกเล่นได้อีกมหาศาล เช่น

ในเมนู Go to Special มี option ให้เลือกมากมาย

เครื่องมือใน Go to … Special
- Notes : เลือก Cell ที่มี Notes (ใน Excel version ใหม่ เปลี่ยน Comment เดิมไปเรียกว่า Notes แทน)
- Comments : เลือก Cell ที่มี Comment (ใน Excel Version ใหม่ Comment จะพิมพ์โต้ตอบกันได้)
- Constant : เลือก Cell ที่เป็นค่าคงที่ ไม่ใช่สูตร (พูดง่ายๆ คือ ไม่ได้ขึ้นต้นด้วยเครื่องหมาย = นั่นเอง)
- Formula : เลือก Cell ที่เป็นสูตร (พูดง่ายๆ คือ ขึ้นต้นด้วยเครื่องหมาย = นั่นเอง)
  - โดยทั้ง Constant และ Formula นี้ เราสามารถกำหนดได้ว่าจะเลือกเฉพาะ Cell ที่มี Data types ที่กำหนด เช่น Number, Text, Logical, Error
- Blank : เลือก Cell ที่ว่างเปล่า ( ต้องว่างจริงๆ ไม่ใช่ text ที่เป็น “” นะ) ซึ่งตัวนี้นำมาประยุกต์แบบนี้ได้ครับ
- Current Region : เลือก Range ทั้งพื้นที่ ซึ่งผมว่ากด Ctrl+A ง่ายกว่า
- Current Array : เลือกพื้นที่ทั้งหมดของสูตร Array แบบหลาย Cell ที่เขียนไว้ในช่องที่กำหนด เหมาะกับการหาว่าเขียนสูตร Array ครอบไว้แค่ไหน
- Objects : คือ เลือกวัตถุที่เพิ่มเข้ามาทั้งหมด เช่น รูปภาพ shape icon ต่างๆ รวมถึงกราฟด้วย
- Row Differences : หาว่าในแถวเดียวกัน มีค่าไหนไม่เหมือนตัวแรก?
  ดังนั้นวิธีใช้ต้องเลือก Range หลายๆ คอลัมน์ก่อน พอกดแล้วมันจะดูว่า ในช่องไหนมีค่าไม่เหมือนคอลัมน์แรกในแถวเดียวกันที่เลือกไว้บ้าง โดยถ้าตัวนึงเป็น Constant อีกตัวเป็น Formula มันดันดูว่าไม่เหมือนกันซะงั้น
- Column Differences : หาว่าในคอลัมน์เดียวกัน มีค่าไหนไม่เหมือนกับตัวแรก? เหมือน Row Differences แต่คราวนี้ต้องเลือกหลายๆ แถวก่อนค่อยกด ซึ่งตัวนี้เองสามารถนำมาพลิกแพลงหาข้อมูลบรรทัดที่ข้อมูลเปลี่ยนแปลงได้ใน Database โดยการกดควบคู่กับปุ่ม F4
- Precedents : เลือก Cell ที่เป็น Input ของสูตรที่กำลังสนใจ
- Dependents : เลือก Cell ที่เอาผลจากสูตรที่สนใจไปใช้ต่อ
- Last Cells : เลือก Cell สุดท้ายที่ใช้งานใน Sheet
- Visible Cell Only : เลือก Cell ที่มองเห็นเท่านั้น ไม่เอาที่ซ่อนหรือ Filter อยู่
- Conditional Formats : เลือก Cell ที่ใส่ Conditional Formats เอาไว้
- Data Validation : เลือก Cell ที่กำหนด Data Validation เอาไว้
  - โดยทั้ง Conditional Format และ Data Validation จะเลือกได้ว่าจะเลือกทุกช่องเลย (All) หรือเลือกช่องที่กำหนดค่าเหมือนกับช่องที่สนใจเท่านั้น
April 28, 2019
แนะนำภาพรวมการใช้ Power BI Desktop
Power BI = BI Tools ยอดนิยม

Power BI Desktop เป็นโปรแกรมด้าน Business Intelligent (BI Tools) ตัวหนึ่งที่ได้รับความนิยมมากในยุคปัจจุบัน เช่นเดียวกับโปรแกรมอื่นๆ เช่น Tableau และ Qlik Sense ซึ่งออกแบบมาให้คนทั่วไปสามารถนำข้อมูลขนาดมหึมาอย่าง Big Data มาวิเคราะห์ได้ง่ายขึ้น

Power BI มีความสามารถสูงแบบ 3 in 1

โปรแกรมนี้มีความสามารถมากมาย ซึ่งรวมความสามารถของ 3 โปรแกรมย่อยๆ เข้าด้วยกันในเครื่องมือเดียว นั่นคือ
- Power Query : จัดการข้อมูลที่มาจากหลายแหล่ง
- Power Pivot : สร้างสูตรการคำนวณและผูกความสัมพันธ์ของตาราง Database
- Power View : สร้าง Interactive Dashboard/Visualization แบบมือโปรได้ง่ายๆ
Power BI Desktop โหลดได้ฟรี!!

ที่สำคัญคือ คุณสามารถ Download โปรแกรมมาใช้ฟรีๆ ได้เลย โดยไม่จำเป็นต้องมี Microsoft Office ในเครื่องคุณเลยด้วยซ้ำ

โดยคุณสามารถเข้าไป โหลดโปรแกรม Power BI Desktop ได้ที่นี่

สำหรับคนที่โหลดมาแล้ว ลองดูภาพรวมการใช้งานได้ที่วีดีโอคลิปนี้ที่ผมทำไว้ให้ได้เลยครับ (ใครชื่นชอบ ฝากกด Subscribe Channel ให้ด้วยนะครับ)

ตัวอย่างการใช้งาน Power BI Desktop | Thep Excel

ไฟล์ตัวอย่าง
April 21, 2019
บทความแนะนำ เครื่องมือ Power Tools
Power Tools

เครื่องมือ Power Tools เหล่านี้ เป็นเครื่องมือที่ถือว่าเป็น Trend ใหม่ ที่น่าสนใจมากๆๆๆ แต่คนไทยส่วนใหญ่ยังไม่รู้จัก และใช้กันไม่ค่อยเป็น แต่นี่จะเป็นสิ่งที่ผมจะเน้นในการนำเสนอจากนี้เป็นต้นไปครับ
- Power Query = ช่วยเชื่อมต่อ และ จัดการข้อมูลให้อยู่ในรูปแบบที่เหมาะสม (แทน Steps การจัดการข้อมูลได้เลย)
  - Power Query ตอนที่ 1: ปลุกพลัง Power Query ให้ตื่นขึ้น!!
  - Power Query ตอนที่ 2 : มหากาพย์การใช้งาน Power Query ตั้งแต่ต้นจนจบ
- Power Pivot = วิเคราะห์ข้อมูลขั้นสูง เขียนสูตร DAX Formula ได้ยืดหยุ่นกว่า Calculated Field ของ PivotTable ธรรมดามากมายนัก
- Power View = นำเสนอข้อมูลด้วย Visualization เจ๋งๆ ที่ใคร เห็นแล้วก็ต้องทึ่ง
ซึ่ง 3 เครื่องมือข้างบนนี้ถูกรวมอยู่ในเครื่องมือเดียว ที่ชื่อว่า Power BI ซึ่งโหลดมาใช้ได้ฟรี!!

Power BI Series
- ตัวอย่างการใช้งาน Power BI Desktop ตอนที่ 1
April 21, 2019
บทความแนะนำ การเรียนรู้ขั้นสูง (Advance Level)
เนื้อหาหลักใน Excel
- การจัดการข้อมูล
- การทำ Dashboard / Visualization
  - การทำกราฟหลอกตา (Mixed Chart)
  - การทำ Conditional Format แบบ Formula สำหรับเงื่อนไขขั้นสูง
- เรื่องเฉพาะทาง และ Add-in ต่างๆ
  - ฟังก์ชันทางการเงิน : PV, FV, PMT, RATE, IRR, NPV
    
    เจาะลึกฟังก์ชั่นการเงินใน Excel : ตอน 1 ปูพื้นฐานวิชาการเงิน
    
    เจาะลึกฟังก์ชั่นการเงินใน Excel : ตอน 2 วิเคราะห์แผนประกันชีวิต
    
    เจาะลึกฟังก์ชั่นการเงินใน Excel : ตอน 3 คำนวณการผ่อนเงินกู้
  - Data Analysis Toolpak : เพื่อวิเคราะห์ทางสถิติ เช่น ANOVA, Regression
- การประยุกต์เจ๋งๆ
  - การทำ VLOOKUP แบบเร็ว 100 เท่า! (*****)
  - การทำ Dependent Dropdown กี่ชั้นก็ได้ (OFFSET+MATCH+Data Validation) (****)
  - การทำ Dynamic Picture (เปลี่ยน Dropdown แล้วรูปเปลี่ยนไป)
  - การใช้ Solver เพื่อหาคำตอบที่ดีที่สุด เช่น หาว่าผลิตสินค้าแบบไหนกี่ชิ้นดี จัดสรรทรัพยากรยังไงดี
- การเขียนสูตรขั้นสูงแบบ Array Formula
  - แนะนำการใช้ Array Formula เบื้องต้น : เข้าสู่โลกใหม่ของการเขียนสูตร Excel ด้วย Array Formula
  - หนังสือเล่ม จอมเวทเทพ Excel ของผมเน้นอันนี้ ใครสนใจลองไปอ่านรายละเอียดได้
- การเขียนโปรแกรม VBA
  “การเขียน VBA ได้” เป็นสิ่งที่ทำให้เราเป็นเหมือนเทพประจำที่ทำงานเลยก็ว่าได้ แต่ด้วย Trend ในอนาคต ผมคิดว่าการใช้ VBA อาจเริ่มจำเป็นน้อยลง แต่การเข้าใจ VBA ขั้นพื้นฐานก็ยังเป็นสิ่งที่ดีมากๆ สำหรับคุณอยู่ดี ดังนั้นรู้พื้นฐานไว้ไม่เสียหายครับ
บทความแนะนำใน Power Tools

สำหรับบทความแนะนำใน Power Tools เช่น Power Query, Power Pivot, DAX, Data Model, Power Bi => คลิ๊กได้ที่นี่
April 21, 2019
บทความแนะนำ การเรียนรู้ขั้นกลาง (Intermediate Level)
- การใช้ฟังก์ชั่นที่น่าสนใจ
- การจัดการข้อมูล
  - การ Import / เตรียมข้อมูลให้อยู่ในรูปแบบ Database ที่เหมาะสม
  - ฟังก์ชั่นกลุ่ม Text พื้นฐาน เช่น LEN, TRIM, LEFT, MID, RIGHT, FIND, SEARCH, SUBSTITUTE, REPLACE, TEXT
  - การใช้ Table เพื่อสร้างแหล่งข้อมูลแบบ Dynamic Range
- การทำงานของ Excel กับวันที่และเวลา
  - ค่าที่แท้จริงของวันที่และเวลา : วันที่คือจำนวนเต็ม, เวลาคือทศนิยม (ที่เปลี่ยน Format ไป)
  - ฟังก์ชันเกี่ยวกับวันที่และเวลา : DATE, DAY, MONTH, YEAR, EDATE, NETWORKDAYS, WORKDAYS
- การทำ PivotTable เพื่อสรุปข้อมูล
  - สรุปทุกสิ่งที่คุณควรรู้เกี่ยวกับ Pivot Table : ภาค 1 – การสรุปผลข้อมูลขั้นพื้นฐาน
  - สรุปทุกสิ่งที่คุณควรรู้เกี่ยวกับ Pivot Table : ภาค 2 – ปรับแต่งตารางให้ได้ดั่งใจ (Summarized Value by / Show Value As)
- การทำ Dashboard / Visualization
  - การทำ Pivot Chart เพื่อนำเสนอข้อมูลในรูปแบบกราฟ
  - การใช้ Slicer แทน Filter เพื่อให้ควบคุม PivotTable/PivotChart ได้พร้อมๆ กันหลายตัว
  - การทำ Conditional Format แบบ Standard เพื่อใส่ Format แบบอัตโนมัติ เช่น ใส่ไฟเขียวไฟแดง การ Highlight สีตามค่าของแต่ละ Cell
- การประยุกต์เจ๋งๆ (แนะนำมากๆ)
April 21, 2019
บทความแนะนำ Excel พื้นฐาน (Excel Basic Level)
ในหน้านี้จะเป็นการรวบรวมบทความที่จะช่วยให้ความรู้ Excel พื้นฐานของคุณแน่นขึ้น ซึ่งเป็นสิ่งที่สำคัญมากในการต่อยอดความรู้ Excel ในระดับสูงต่อไปในอนาคต เพราะหากพื้นฐานไม่ดี ก็ย่อมไม่สามารถสร้างตึกสูงได้

หาแรงจูงใจ
- เก่ง Excel แล้วดียังไง? : เล่าที่มาว่าผมมาใช้ Excel จนเก่งขึ้นได้ยังไง
- หาโปรเจคทำที่อยากทำ เพื่อให้เจอปัญหา แต่มีแรงสู้ต่อ
เริ่มต้นเรียนรู้ Excel พื้นฐาน
- องค์ประกอบพื้นฐานของ Excel
  - สิ่งที่เราเห็นใน Cell มาจากไหน ? (ความต่างของ Content vs Format)
  - ประเภทของข้อมูล เช่น Number, Text, Logic, Error
  - การปรับ Format เช่น Number Format, ปรับ Wrap Text, Merge Cell, Shrink to Fit, Freeze Pane
- การเขียนสูตร
  - การเขียนสูตร และ องค์ประกอบของสูตร เช่น Cell Reference, Constant, Operator, Defined Name, Function
  - การคำนวณสัดส่วน/ Percentage ใน Excel ควรทำแบบใด
  - การตรวจสอบความผิดพลาดของสูตร ด้วยการใช้เครื่องมือ Formula Auditing รวมถึงการกด F9
- ฟังก์ชั่น-เครื่องมือพื้นฐาน
  - ฟังก์ชันสรุปข้อมูลพื้นฐาน : SUM, COUNT, COUNTA, AVERAGE, MAX, MIN
  - ฟังก์ชันปัดตัวเลข ปัดเศษทศนิยม : ROUND, ROUNDUP, ROUNDDOWN, INT, MOD
  - การใช้ Goal Seek เพื่อแก้สมการแบบง่ายๆ
  - การใช้คีย์ลัดต่างๆ เพื่อให้ทำงานเร็วขึ้น
- การ Copy/Paste ข้อมูล
Excel พื้นฐาน มุ่งสู่การต่อยอด
- Data Validation : การกำหนดเงื่อนไขการกรอกข้อมูล ว่ายอมให้กรอกอะไรใน Cell ได้บ้าง
  - องค์ประกอบ Data Validation และ การทำ Drop down List (Data Validation ที่นิยมและใช้บ่อยที่สุด)
- การใช้ฟังก์ชั่นกลุ่ม Logic เงื่อนไข
  - ฟังก์ชันกลุ่ม IS…. เช่น ISERROR, ISTEXT, ISNUMBER, ISBLANK
  - การใช้ฟังก์ชัน IF / IF ซ้อนกับ AND OR NOT เพื่อรองรับเคสที่มีหลายเงื่อนไข / IF ซ้อนกับ IF
- การจัดการข้อมูล
  - การ Sort/Filter เพื่อจัดเรียงและคัดกรองข้อมูล รวมถึงรู้ผลเมื่อเรา Copy หรือ แก้ไขข้อมูลที่ Filter อยู่
  - การใช้ Flash Fill เพื่อให้ Excel เลียนแบบการจัดการข้อมูลของเรา
April 21, 2019
แจกไฟล์รวมวันหยุด 10 ปี ย้อนหลัง ในรูปแบบไฟล์ Excel

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

ซึ่ง Function หรือ สูตรทั้ง 2 กลุ่มดังกล่าว จะใช้งานจริงแทบไม่ได้เลย หากปราศจากข้อมูลวันหยุดพิเศษ ที่เราต้องบอก Excel เองว่าบริษัทเราหุดวันไหนบ้าง? (ระบุเป็น Range ใน Input ที่ชื่อว่า Holidays) ตามข้างล่างนี้

ตระกูล NETWORKDAYS

NETWORKDAYS / NETWORKDAYS.INTL เอาไว้หาจำนวนวันทำการในระหว่างช่วง 2 วันที่กำหนด (นับวันเริ่ม)

***ฟังก์ชันแบบมี .INTL จะเจ๋งกว่าเพราะสามารถเลือกได้ว่าหยุดวันไหนบ้าง (จ-อา) แต่ถ้าแบบ ปกติจะถือว่า หยุดทั้งวันเสาร์และวันอาทิตย์

=NETWORKDAYS.INTL(start_date,end_date,[weekend],[holidays])
หรือ
=NETWORKDAYS.INTL(วันเริ่ม,วันจบ,[เลือกโหมดว่าหยุดวันไหนบ้าง จ-อา],[เลือกวันหยุดพิเศษในไฟล์ที่แจก])

ตระกูล WORKDAY

WORKDAY/WORKDAY.INTL เอาไว้หาวันสิ้นสุด โดยรู้วันเริ่ม และจำนวนวันทำการที่ต้องใช้ทำงาน (ไม่นับวันเริ่ม)

=WORKDAY.INTL(start_date,days,[weekend],[holidays])

หรือ
=WORKDAY.INTL(วันเริ่ม,จำนวนวันทำการ,[เลือกโหมดว่าหยุดวันไหนบ้าง จ-อา],[เลือกวันหยุดพิเศษในไฟล์ที่แจก] )

จะหา List วันหยุดจากไหน???

ผมลองไปหาใน internet ดูว่ามีใครรวบรวมไฟล์วันหยุดไว้หรือไม่? ซึ่งดีที่สุดเท่าที่เจอ คืิอ มีการแยกไฟล์วันหยุดเป็นปีๆ เอาไว้ เช่น เว็บ MyHora อันนี้

ทีนี้ผมก็เลยโหลดไฟล์ CSV 10 ไฟล์ (ไฟล์ละ 1 ปี) มารวมไว้ใน Folder เดียวกัน แล้วใช้ Power Query ช่วยรวมทุกไฟล์เป็นไฟล์เดียวกัน ตามขั้นตอนดังนี้

*** ใครยังไม่แน่มจว่าตัวเองมีี PowerQuery หรือไม่ ลองอ่านบทความนี้ก่อน

Step 1 : เลือก Get Data จาก Folder ที่ต้องการตามรูป
จากนั้น Browse แล้ว Ok

เลือกดึงข้อมูลจาก Folder ตาม step

Step 2 : กด Transform Data หรือ Edit (ถ้าใครมีปุ่ม Combined ก็กดได้เลย จะง่ายกว่า)

Step 3: กดปุ่ม Combined แล้วเลือก Encoding ให้เหมาะสม (ในที่นี้ของผมตอนแรกอ่านไม่ออก ต้องปรับเป็น UTF-8 ถึงจะอ่านออก) จากนั้นกด OK

Step 4 : เลือกคอลัมน์ทีต้องการ (กด ctrl ค้างแล้วเลือก) แล้วกด Remove Other Column เพื่อเอาคอลัมน์ที่ไม่ต้องการออก

Step 5 : กด Close & Load มันก็จะออกมาเป็น Table ใน Excel แล้วครับ

ถ้าจะใส่ไฟล์วันหยุดเพิ่มล่ะ?

ทีนี้ ถ้าหากเราจะเพิ่มวันหยุดเข้าไปอีก เช่น เอาปี 2008 มาด้วย เราก็แค่โหลดไฟล์ CSV แล้วโยนไปไว้ใน Folder เดิมที่เรามี 10 ไฟล์แรกอยู่ (รวมเป็น 11 ไฟล์)

จากนั้นกด Refresh All แค่นี้ก็จบเลยครับ

Download ไฟล์รวมวันหยุด

ไฟล์วันหยุดที่ทำเสร็จแล้ว โหลดได้ที่ link นี้ครับ

การประยุกต์ใช้

สำหรับคนที่มีไฟล์นี้แล้ว สามารถลองศึกษาคลิปวีดีโอนี้ เพื่อเรียนรู้วิธีการเขียนสูตรเพื่อตรวจสอบว่าวันที่ที่เราสนใจ เป็นวันหยุดหรือวันทำงานได้

ใครชอบบทความนี้และเห็นว่ามีประโยชน์ ผมรบกวนช่วยบอกต่อเพื่อนๆ ให้หน่อยนะครับ ขอบคุณมากครับ

April 11, 2019
วิธีใช้ Excel รันเลข สร้างเลข Running 1 ถึง 1000 ง่ายๆ

บางครั้งเราต้องการสร้างเลข Running หรือพูดง่ายๆ ว่าจะใช้ Excel รันเลขให้เพิ่มขึ้นเรื่อยๆ เช่น จากเลข 1 ไปจนถึง 1000 (หรือจะ 1 ล้านก็ตาม)

วิธีที่ทำได้ง่ายมากๆ วิธีหนึ่ง นั่นก็คือ การใช้เครื่องมือ Fill Series นั่นเอง ซึ่งมีวิธีการทำดังนี้

Step 1 : เลือก Cell ที่ต้องการเป็นจุดเริ่มต้น แล้วพิมพ์เลขเริ่มต้น เช่น เลข 1

Step 2 : ไปที่ Ribbon Home => Fill => Series…

Step 3 : เลือกว่าจะ Fill ข้อมูลในแนวไหน ซึ่งปกติจะเลือก column ซึ่งเป็นแนวตั้ง แล้วก็กรอกไปว่าจะให้สิ้นสุดที่เลขอะไร เช่น 1000 จากนั้นกด ok

ได้ผลลัพธ์ เป็นอันจบครับ! ง่ายมะ?

แค่งี้เองครับ เทคนิคนี้ง่ายมากๆ ใครชอบก็อย่าลืมบอกต่อเพื่อนๆ ด้วยนะครับ ขอบคุณมากๆ

April 5, 2019
7 เทคนิค Power Query

คงปฏิเสธไม่ได้ว่าเครื่องมือที่ร้อนแรงที่สุดของ Excel ยุคใหม่ ก็คือ Power Query ซึ่งเดิมเคยเป็น Add-in ตัวนึงที่ใช้ได้ตั้งแต่ Excel 2010 เป็นต้นไป แต่สำหรับ Excel Version ใหม่ๆ มันได้ถูกฝังตัวมาอยู่ใน Ribbon Data เลย

สำหรับคนที่ไม่เคยใช้ Power Query มาก่อนเลย ให้ลองอ่านบทความที่ผมเคยเขียนไว้ก่อน เพื่อที่จะได้เข้าใจภาพรวมมากขึ้นครับ

Power Query ตอนที่ 1: ปลุกพลัง Power Query ให้ตื่นขึ้น!!
Power Query ตอนที่ 2 : มหากาพย์การใช้งาน Power Query ตั้งแต่ต้นจนจบ

ในบทความนี้ ผมจะมาแนะนำเทคนิคเจ๋งๆ ในการใช้ Power Query ให้รู้จักกันมากขึ้นครับ โดยจะขอแนะนำซัก 7 อันก่อนแล้วกันครับ

สำหรับไฟล์ประกอบที่ผมใช้ในบทความนี้ สามารถโหลดได้ที่นี่ครับ

Tips 1 : เราไม่จำเป็นต้องเอา Data มาไว้ในไฟล์ Excel จริงๆ

ปกติแล้วเวลาเรา Import ข้อมูลจากข้างนอกมาก เราก็มักจะเอา Data มาไว้ในไฟล์ Excel จริงๆ ด้วย ซึ่งข้อเสียคือ จะทำให้ไฟล์ Excel มีขนาดใหญ่โดยไม่จำเป็น และยังมีข้อจำกัดที่ว่า Sheet ของ Excel มีได้แค่ 1 ล้านกว่าๆ แถวด้วย

ซึ่งข้อจำกัดนี้สามารถแก้ไขได้โดยการที่เราสามารถกด Import File แล้วเลือก Load to… ==> Only Create Connection ได้ครับ (แถมเราเอาเข้า PivotTable จาก Query ที่มีแต่ Connection ก็ได้นะ)

กดลูกศรข้างๆ ปุ่ม Load

Only Create Connection จะได้ไม่เปลืองเนื้อที่เก็บข้อมูล

จะออกมาเป็น Connection Only

ถ้าจะเอาไป Pivot ก็ได้ โดยเลือก Insert -> PivotTable
แล้วเลือก External Data Source -> Choose Connection

เลือก Query ที่ต้องการ แล้วกด Open เพื่อทำ PIvot ตามปกติได้เลย

Tips 2 : ลำดับการคลิ๊ก Column มีผลต่อการจัดเรียง เวลากด Remove Other Columns

เมื่อเรา Edit Query แล้วเข้าสู่ Query Editor หากเราต้องการเอาคอลัมน์ในตารางออกยกเว้นแค่บางคอลัมน์ ปกติก็จะเลือก Column ที่ต้องการ แล้วกด Remove Other Columns ได้เลย แต่รู้หรือไม่ว่า ลำดับก่อนหลังที่เราคลิ๊ก Column นั้นมีผลต่อการจัดเรียงคอลัมน์ด้วยนะ

เลือกคอลัมน์ที่ต้องการให้เหลือ ตามลำดับที่ต้องการให้เรียง (กด Ctrl ค้างไว้เพื่อเลือกหลายๆ อัน)
จากนั้นกด click ขวา -> Remove Other Columns

สุดท้ายจะเลือกแต่คอลัมน์ที่เราเลือกไว้ โดยเรียงตามลำดับการเลือก

Tips 3 : ใน Power Query อักษรตัวพิมพ์เล็กพิมพ์ใหญ่ ถือว่าเป็นคนละคำกัน

เวลาเราเลือก Filter ข้อมูลใน Power Query จะต้องระมัดระวังเรื่องของข้อมูลที่มีตัวพิมพ์เล็กพิมพ์ใหญ่ด้วย เช่น Filter ว่าเอาเฉพาะคำว่า Female แปลว่ามันจะตัดคำว่า female ทิ้งด้วยนะ ระวังให้ดี!!

ลองกดปุ่ม Filter ดู

เห็นมั้ย? ว่ามันมองเป็น 4 ตัวที่ไม่เหมือนกัน เพราะติดเรื่องตัวพิมพ์เล็กพิมพ์ใหญ่

ถ้าไม่อยากมีปัญหาเรื่องนี้ ผมแนะนำให้แปลงข้อมูลเป็นตัวพิมพ์เล็กทั้งหมดด้วย Transform–>Format–>Lower Case ก่อนทำการ Filter นะครับ

กด lowercase ให้แปลงข้อมูลเป็นตัวพิมพ์เล็กทั้งหมด

เหลือแค่ 2 แบบแล้ว

Tips 4 : แยกข้อความง่ายๆ ด้วย Split

หากในข้อมูลดิบ มีข้อความหลายตัวที่รวมกันอยู่ในคอลัมน์เดียว แต่คั่นด้วยตัวอักษรบางอย่างเช่น . / – หรือ ตัวอื่นๆ เราสามารถแยกคำเหล่านั้นให้ออกมาให้อยู่คนละคอลัมน์ได้อย่างง่ายดาย

จะเห็นว่าจริงๆ มัน split ด้วย Pattern อย่างอื่นได้อีก เช่น แบ่งตัวหนังสือกับตัวเลข เป็นต้น

เลือกได้ว่าจะว่าจะ Split ทุกตัว หรือเอาเฉพาะตัวซ้ายสุด ขวาสุด (ในที่นี้ split ที่ตัวซ้ายสุด)
ถ้าตัวคั่นเป็นอักษรอื่น ก็เลือก dropdown -> custom ได้

จากคอลัมน์ Name อันเดียว คราวนี้กลายเป็น Name.1 กับ Name.2 แล้ว
ซึ่งใครจะเปลี่ยนชื่อ column ก็กด Double Click ที่ชื่อ column ได้เลย

Tips 5 : Unpivot พลิกข้อมูลหลายคอลัมน์ให้มาอยู่ในคอลัมน์เดียว

งานนี้เป็นงานที่ยากสุดๆ เมื่อใช้ Excel แต่กลับง่ายแบบปอกกล้วยเข้าปากเมื่อใช้ Power Query ซะงั้น

สมมติเรามีข้อมูลตามตาราง ซึ่งมีคอลัมน์ Survived กับ Not Survived แยกกันอยู่

เลือก 2 คอลัมน์ (อย่าลืมกด Ctrl) ที่ต้องการพลิกให้มาอยู่ในคอลัมน์เดียวกัน
แล้ว transform -> Unpivot Columns

จะเห็นว่า Survived กับ Not Survived ถูกพลิกมาอยู่ในคอลัมน์เดียวกันแล้ว (ส่งผลให้จำนวนแถวมีมากขึ้น)

Tips 6 : เราสามารถดึงข้อมูลจาก ทุก Text File ใน Folder ได้เลย

ถ้าเรามี Text File หรือ CSV หลายๆ ไฟล์อยู่ใน Folder เดียวกัน เราสามารถสั่งให้ Excel ดึงข้อมูลทุกไฟล์ที่อยู่ใน Folder นั้นมา Append หรือเอาข้อมูลมาต่อตูดกันได้เลย

เลือก Get Data -> From File -> From Folder

เลือก Folder ที่ต้องการ

กด Edit หรือ Transform Data (แล้วแต่ version)

จะเห็นว่ามีไฟล์นามสกุลอื่นปนอยู่ (นอกจาก csv ที่เราอยากได้) ดังนั้นก็ Filter ทิ้งไปซะ

กด select all ทิ้งก่อน แล้วค่อยเลือก csv

กดแตกลูกศรตรง Content ออกมา แล้วกด ok จากหน้าต่างใหม่ที่โผล่มา

คราวนี้มันจะเอาทุกไฟล์มาต่อกัน โดยบอกด้วยว่าเอามาจากไฟล์ไหน

Tips 7 : เทคนิคพิเศษดึงข้อมูลจากไฟล์ Excel ทุก File ใน Folder

ถ้าเราต้องการ รวมไฟล์ Excel ใน Folder มันจะใช้วิธีปกติไม่ได้ครับ ทั้งนี้เพราะโครงสร้าง Excel มันสามารถมีได้หลาย Sheet ในไฟล์เดียว

สมมติเราทำแบบเดิม แต่ Filter ให้เหลือแต่ไฟล์ excel (แต่ละไฟล์มีหลาย sheet)

พอกดลูกศรตรง Content คราวนี้มันจะให้เลือกทีละ Sheet
โดยไม่มี Option ให้เอาทั้งหมดทุก Sheet มารวมกัน

ดังนั้นเราต้องใช้เทคนิคพิเศษ (ที่ค่อนข้างยุ่งหน่อย) ดังนี้แทนครับ

สร้าง Custom Column

ใส่สูตรว่า =Excel.Workbook([Content],true)

จะมี Field ใหม่โผล่มา ซึ่งจะกด Expand ได้แล้ว

กด ok

มันจะ expand ข้อมูลออกมาอีกก้อนนึง คราวนี้เราต้องกด Expand อีกทีที่ DataExpand.Data

กด ok

คราวนี้มันก็จะแตกทุก Field ที่มีใน File Excel ได้แล้ว
แล้วเอาแต่ละ Sheet มาต่อกันด้วย
ทำอย่างนี้ทุก File ใน Folder เลย เจ๋งสุดๆ

เป็นยังไงบางครับกับเทคนิคการใช้ Power Query ทำงานในการดัดแปลงข้อมูล จะเห็นว่าถ้าหากเราต้องทำเรื่องแบบนี้ใน Excel นี่จะต้องปวดหัวแน่นอน แต่ถ้าใช้ PowerQuery ก็จะสบายขึ้นเยอะ

ยังไงก็ลองหัดทำดูนะครับ ถ้าสงสัยตรงไหนก็ Comment ถามได้เลยครับ
ใครอ่านแล้วชอบ ก็ช่วยบอกต่อเพื่อนๆ ด้วยนะครับ
ยิ่งคนใช้เป็นเยอะก็ยิ่งดีใจครับ ^^

March 21, 2019
9 เทคนิค Excel มือใหม่ก็เก่งได้อย่างรวดเร็ว
Excel มือใหม่หลายๆ คน อาจไม่รู้เทคนิค Excel เหล่านี้มาก่อน ซึ่งเป็นเรื่องที่น่าเสียดายมาก เพราะ Microsoft Excel เป็นโปรแกรมสามัญประจำทุก Office และยังเป็นโปรแกรมที่คนใช้เป็นสร้างผลลัพธ์ได้ต่างกับคนใช้ไม่เป็น มากๆ เรียกได้ว่าคนใช้ Excel เก่งๆ จะทำงานได้เสร็จเร็วกว่าแถมยังถูกต้องมากกว่า

สาเหตุหนึ่งเป็นเพราะ Excel เป็นโปรแกรมที่มีความสามารถหลากหลาย และคนใช้งานต้องมีความเข้าใจที่ค่อนข้างลึกซึ้งจึงจะพลิกแพลงการทำงานได้ดี

แต่มันก็ไม่ใช่ทุกเรื่องของ Excel ที่จะต้องใช้ความรู้ที่ลึกซึ้งอะไรมากมาย หลายๆ เรื่องเป็นแค่เรื่องของ “รู้” กับ “ไม่รู้” มากกว่า

ซึงบทความนี้ผมจะขอพูดถึง 9 เทคนิค Excel ที่จะทำให้คุณใช้ Excel ได้เก่งขึ้นแบบมือใหม่ก็ใช้ได้เลย

เทคนิค Excel #1 : กรอกวันที่/เวลาปัจจุบันอย่างไวด้วย Ctrl + ;

เราสามารถกรอกข้อมูลวันที่ปัจจุบัน ณ ขณะนั้นลงไปใน Cell ด้วยการกดปุ่มลัดบน Keyboard Ctrl+; (ปุ่ม ว) ซึ่งมีวิธีจำง่ายๆ คือ ปุ่ม ว. = วันที่และเวลา
วิธีจำอีกแบบคือ เวลา จะมีสัญลักษณ์ คือ : ดังนั้นก็เลยมีปุ่มลัดเป็น Ctrl+: ครับ
- จะใส่วันที่ปัจจุบัน กด Ctrl+;
- จะใส่เวลาปัจจุบัน กด Ctrl+: หรือ Ctrl+Shift+; นั่นเอง
Tips1: หากกด Ctrl+; แล้วไม่มีอะไรเกิดขึ้น แสดงว่ามี Bug … ให้ลองปิด Excel แล้วเปิดใหม่ แล้วลองใหม่นะครับ

Tips2: พอลองกรอกปีด้วย Ctrl+; แล้ว ดูดีๆ นะว่าคอมพ์เราต้องกรอก วัน/เดือน/ปี ค.ศ. หรือ เดือน/วัน/ปี ถ้าใส่ผิดก็จะมั่วเลย (แต่ละเครื่องอาจตั้งค่าไม่เหมือนกัน ขึ้นอยู่กับ Regional Setting ใน Control Panel

Tips3: โดยแม้ดูเผินๆ การกด Ctrl+; จะคล้ายๆ กับการใส่ฟังก์ชันที่ชื่อว่า =TODAY() แต่จะมีความต่างกันตรงที่ว่า การกด Ctrl+; จะเหมือนกับการที่เรากรอกวันที่ลงไปเอง ซึ่งค่าจะไม่เปลี่ยนอีกเลย แม้จะนอนหลับจนถึงวันถัดไปแล้วเปิดไฟล์เดิมมาดูอีกครั้ง ซึ่งต่างจาก =TODAY() ที่วันที่จะเปลี่ยนไปหากเอาไฟล์ไปเปิดในอนาคต

เทคนิค Excel #2 : ค้นหา Sheet ที่ต้องการอย่างรวดเร็ว

Excel แต่ละไฟล์มี Sheet ได้หลายอัน และบางไฟล์ก็อาจมี Sheet เยอะมากจนหาไม่เจอเลยก็เป็นไปได้ถ้า
จะดีกว่ามั้ย หากมีวิธีเลือก Sheet ที่ต้องการอย่างรวดเร็ว?

ผมมีวิธีง่ายๆ มานำเสนอ นั่นก็คือ การกดปุ่ม Click ขวา ที่ลูกศรด้านซ้ายสุดของ Tab ชื่อ Sheet

เมื่อกดแล้วจะมีชื่อ Sheet เรียงกันอยู่ เราสามารถพุ่งไปหา Sheet ที่ต้องการได้ เพียงแค่กดตัวอักษรแรกสุดของชื่อ Sheet นั้นๆ เช่น จะไป Sheet ชื่อว่า Data Validation ก็ให้กดตัว d บนคีย์บอร์ดไปเรื่อยๆ จนกว่าจะเจอ Sheet นั้น

เทคนิค Excel #3 : ทดลองค่าจนกว่าจะได้ตามเป้าหมายด้วย Goal Seek

หากเราเขียนสูตรใน Excel เพื่อผูกความสัมพันธ์แต่ละ Cell เอาไว้ เช่น ใส่ราคาสินค้าก่อน Vat และราคาสินค้าหลัง Vat เอาไว้
เช่น ก่อน Vat ในช่อง B1 เป็น 100 บาท และหลัง Vat เป็น =B1+(7%*B1) ซึ่งจะได้ 107

ที่นี้สมมติปัญหาคือ เราอยากให้ราคาหลัง Vat เป็น 120 บาทพอดี จะต้องมีราคาก่อน Vat เท่าไหร่??

โจทย์แบบนี้ปกติแล้วถ้าเรามีความรู้คณิตศาสตร์เราก็จะแก้สมการได้ แต่ถ้าบางทีสมการแก้ยากมาก เราก็สามารถใช้วิธีลองเปลี่ยนราคาก่อน Vat ไปเรื่อยๆ จนกว่าราคาหลัง Vat จะได้ 120 บาทพอดีได้

จะให้เราเปลี่ยนค่าเองไปเรื่อยๆ คงเสียเวลามากกว่าจะเจอ โชคดีที่ Excel สามารถช่วยลองเปลี่ยนค่าแทนเราได้ด้วยเครื่องมือ Data => What-if Analysis => Goal Seek ซึ่งจะให้เราเลือกว่า
จะให้ Cell ไหน (Set Cell) เปลี่ยนค่าเป็นอะไร (To Value) โดยลองเปลี่ยนค่า Cell ไหน (By Changing Cell) พอกด ok แล้ว Excel ก็จะจัดการให้เราทุกอย่างจนจบครับ

เทคนิค Excel #4 : การกด F4 เพื่อทำ Action ล่าสุดซ้ำ

เทคนิคนี้ง่ายแสนง่าย แค่เราทำอะไรบางอย่างใน Excel เช่น แทรกแถว ลบแถว หรือ ใส่สี แล้วลองกดปุ่ม F4 ดู จะเห็นว่า Excel ทำสิ่งที่เราทำซ้ำอีกรอบนึงอย่างน่ามหัศจรรย์ครับ

Tips: สำหรับคนที่กดปุ่ม F4 แล้วไม่มีอะไรเกิดขึ้น อาจเป็นเพราะเครื่องคอมพิวเตอร์ของคุณตั้งค่าเอาไว้ว่า ถ้าจะใช้ปุ่ม F1-F12 จะต้องกดปุ่ม Fn (อยู่บริเวณด้านซ้ายของปุ่ม Windows) ควบคู่ไปด้วย จากนั้นให้ลองดูว่ากด Fn+F4 แล้วได้ผลมั้ย

เทคนิค Excel #5 : Copy ข้อมูลอย่างรวดเร็วด้วย Fill Handle

หากเรามีข้อมูลอยู่ แล้วเราอยากจะ Copy ไป Paste ลงที่อื่น เราสามารถทำง่ายๆ โดยการ Click เม้าส์ซ้ายที่สี่เหลี่ยมเล็กๆ ที่มุมขวาล่างของ Cell (เรียกว่า Fill Handle) แล้วลากลงมา มันจะทำเหมือนว่าเรากด Copy –> Paste ข้อมูลทุกประการ

และถ้าข้างๆ คอลัมน์นั้นมีข้อมูลอยู่แล้ว เราสามารถกดปุ่ม Double Click ที่ Fill Handle ได้เลย มันจะ Copy ข้อมูลลงมาจนสุดเท่าที่ข้างๆ มีข้อมูลอยู่ (เหมาะมากกับกรณีที่มีข้อมูลเยอะๆ จนลากไม่ไหว)

เทคนิค Excel #6 : การ Copy รูปแบบด้วย Format Painter

จากเทคนิคที่แล้ว เราทำการ Copy ข้อมูลด้วย Fill Handle ถ้าสังเกตจะเห็นว่ามันเป็นการ Copy ทั้งข้อมูลเนื้อหา (Content) และรูปแบบ (Format)

แต่ถ้าเราอยากจะ Copy แต่ Format เราสามารถทำได้ง่ายๆ โดย เลือก Cell ต้นฉบับก่อน จากนั้นใช้เครื่องมือ Format Painter หรือแปรงทาสี ที่อยู่ในบริเวณซ้ายมือของ Ribbon Home แล้วป้ายไปยังพื้นที่ปลายทางที่ต้องการนั่นเอง

และถ้าอยากจะป้าย Format ลงพื้นที่ปลายทางที่อยู่ไม่ต่อเนื่องกัน ก็สามารถกด Double Click ที่แปรงทาสีได้ เพื่อ lock การใช้แปรงทาสีเอาไว้ก่อนครับ

เทคนิค Excel #7 : การให้ Excel เลียนแบบเราด้วย Flash Fill

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

แต่ถ้าเรามี Excel 2013 ขึ้นไป จะมีเครื่องมือที่จะช่วยชีวิตเราได้แบบสบายๆ นั่นก็คือ Flash Fill ที่จะมีความสามารถในการเลียนแบบสิ่งที่เราทำได้เลย

วิธีทำคือ พิมพ์ผลลัพธ์ที่ต้องการให้ Excel ดูก่อน จากนั้นเลือก Cell ต้นฉบับ (หรือ Cell ถัดไปข้างล่าง 1 ช่อง) จากนั้นกดปุ่ม Ctrl+E เพื่อใช้ Flash Fill ได้เลยครับ

Tips: ถ้า Flash Fill ทำไม่ได้ หรือทำผิด ให้ทำตัวอย่างเพิ่ม เดี๋ยวมันจะฉลาดเอง

เทคนิค Excel #8 : กำจัดข้อมูลที่ซ้ำด้วย Remove Duplicates

บางทีเรามีข้อมูลในแต่ละคอลัมน์ซ้ำๆ กันเต็มไปหมด เราสามารถกำจัดตัวซ้ำได้ง่ายๆ ด้วยการเลือกข้อมูลคอลัมน์ที่ต้องการ แล้วไปที่ Ribbon Data => Remove Duplicates เพียงเท่านี้ตัวซ้ำก็จะหายไปอย่างง่ายดาย

Tips : คำสั่ง Remove Duplicates จะมองว่าข้อมูลที่ Content เหมือนกัน แต่ต่าง Format กัน ถือว่าเป็นข้อมูลที่ไม่ซ้ำกัน แต่เราอยากจะให้เหลือที่ Content ที่ไม่ซ้ำกัน ก็สามารถ Clear Format ทิ้งไปก่อนจะ Remove Duplicates ก็ได้ หรือจะเปลี่ยนวิธีไปใช้เครื่องมือ PivotTable แทนก็ได้

เทคนิค Excel #9 : จัดการเฉพาะข้อมูลที่เราสนใจด้วย Filter

การทำงานกับข้อมูลที่มีหลายๆ รายการในฐานข้อมูล ก็มีหลายจังหวะที่เราก็อยากจะจัดการข้อมูลเฉพาะรายการที่เราสนใจเท่านั้น เช่น สนใจเฉพาะ สินค้า A หรือ เฉพาะเดือนที่เลือก

ซึ่งเราสามารถทำเรื่องดังกล่าวได้โดยง่าย โดยการใช้เครื่องมือ Filter
วิธีการคือ เลือกซัก Cell หนึ่งในข้อมูลของเรา จากนั้นไปที่ Data=> Filter

จากนั้นก็เลือก Filter ว่าจะดูเฉพาะรายการไหน ซึ่งพอ Filter ปุ๊ป เลขแถวบริเวณด้้านซ้ายมือจะกลายเป็นสีฟ้า

หลังจากที่ Filter แล้วไม่ว่าเราจะทำอะไรกับข้อมูลก็ตาม มันจะส่งผลเฉพาะรายการที่เรามองเห็นอยู่เท่านั้นครับ ไม่ว่าจะแก้ไข Content หรือ Format เช่น เขียนสูตร ลบข้อมูล ใส่สี ตีกรอบ หรือจะ Copy ข้อมูลจากตารางที่ Filter อยู่ออกไป Paste ลงที่อื่นก็ตาม

เมื่อ filter แล้วจะสังเกตได้ว่าตัวเลขด้านซ้ายจะเป็นสีฟ้า

เมื่อเราทำอะไรก็ตามกับข้อมูลที่มีการ Filter มันจะส่งผลเฉพาะข้อมูลที่มองเห็นอยู่เท่านั้น

Tips : การ Copy ข้อมูลจากที่อื่น มา Paste ยังข้อมูลที่ Filter อยู่ จะมีการ Paste ลงไปยังรายการที่มองไม่เห็นด้วย (ต่างจากการ Copy ข้อมูลจากตารางที่ Filter อยู่ไป Paste ลงที่อื่่น)
March 13, 2019
VLOOKUP Approximate Match
อย่างที่เคยบอกไปแล้วว่า VLOOKUP มี 2 แบบ คือ Exact Match และ VLOOKUP Approximate Match

ซึ่งแบบ Exact Match จะ Map ข้อมูลได้ จะต้องเจอคำค้นหาอยู่ในคอลัมน์แรกของตารางอ้างอิงแบบเป๊ะๆ เท่านั้น

แต่ในหลายๆ สถานการณ์ VLOOKUP แบบ Exact Match นั้นไม่สามารถตอบโจทย์ได้ เช่น กรณีที่มีเงื่อนไขในการใช้ตารางอ้างอิงเป็นช่วง เช่น การจัดเกรด การให้ commission เป็นต้น ซึ่งจะต้องใช้ VLOOKUP Approximate Match แทน

ตัวอย่างเช่น การจัดเกรดจากคะแนนแบบนี้

จะเห็นว่า ถ้าจะทำตารางเพื่อรองรับการหาแบบ Exact Match จะต้องสร้างตารางให้มี 100 ค่าเลย เพื่อรองรับแต่ละคะแนน (จริงๆ ต้องทำ 101 ค่าด้วย เพราะต้องรองรับตั้งแต่ 0-100 คะแนน) ยิ่งที่ค่าตัวเลขคะแนนเป็นทศนิืิยมได้นี่ ไม่มีทางสร้างตารางแบบ Exact Match รองรับไว้ล่วงหน้าได้เลย

แต่ถ้าเราเปลี่ยนวิธีมาใช้ VLOOKUP แบบ Approximate Match จะแก้ปัญหานี้ได้ง่ายๆ เลย

วิธีเตรียมข้อมูลเพื่อรองรับการทำ VLOOKUP แบบ Approximate Match

วิธีเตรียมที่ง่ายที่สุด คือ ให้สร้างคอลัมน์ใหม่ไว้ทางซ้ายของข้อมูล แล้วใส่ค่าที่น้อยที่สุดในช่วงนั้นเข้าไป (ต้องเรียงจากน้อยไปมากด้วยนะ)

การใช้สูตร VLOOKUP แบบ Approximate Match

จากนั้นก็ใช้ VLOOKUP ได้ตามปกติเลย จากสูตร

=VLOOKUP(lookup_value,table_array,col_index_num, [range_lookup])
- table_array ให้คอลัมน์แรกสุดเรียงจากน้อยไปมาก ดังนั้นควรลากแค่ตัวข้อมูล ไม่ต้องลากหัวตาราง
- ให้เปลี่ยน Range Lookup เป็น TRUE
เช่น ใน C10 เขียนว่า =VLOOKUP(B10,A2:C6,3,TRUE) เพื่อ Lookup คะแนน 63 ซึ่งจะได้ผลเป็น C นั่นเอง

การทำงานที่แท้จริงของ VLOOKUP Approximate Match

การใช้งาน VLOOKUP Approximate Match ที่ผมอธิบายไปด้านบนนั้น เป็นแบบที่ทำให้ใช้งานจริงได้ง่าย แต่ในความเป็นจริงแล้ว VLOOKUP แบบนี้มีการทำงานที่ซับซ้อนกว่านั้น คือ มันจะใช้การค้นหาข้อมูลแบบ Binary Search ซึ่งมีความรวดเร็วมาก แต่จะมีข้อจำกัดคือ ต้องเรียงค่าที่เราจะค้นหาในตารางอ้างอิงจากน้อยไปมากเสียก่อน นั่นคือ ข้อมูลในคอลัมน์ซ้ายสุดของตารางอ้างอิงจะต้องเรียงจากน้อยไปมากเท่านั้น (ไม่จำเป็นต้องเป็นตัวเลข จะเป็นตัวหนังสือก็ได้) ไม่งั้นผลการ VLOOKUP อาจจะ Error

และหลังจาก หาคำที่ต้องการจากคอลัมน์ซ้ายสุดของตารางที่กำหนด (Table Array) โดยทำการหาจากบนลงล่าง (Vertical Lookup) จนเจอตัวที่มากกว่าค่าที่กำหนดแล้วจะเด้งกลับขึ้นข้างบน 1 บรรทัด

ผลลัพธ์จากการใช้ฟังก์ชัน
1. ถ้าเจอค่าที่ต้องการเป๊ะๆ : ถ้ามีค่าเดียว มันจะยึดค่าบรรทัดนั้น ถ้ามี หลายค่า มันจะยึดที่เจออันล่างสุด(เจอค่าที่มากกว่าแล้วเด้งขึ้น 1 บรรทัด)
2. ถ้าไม่เจอค่าที่ต้องการเป๊ะๆ :ขึ้นอยู่กับค่าคำค้นหาเทียบกับค่าในตารางอ้างอิง
  - อยู่ระหว่างค่าในตาราง : จะหาตัวที่มากกว่าคำค้นหาแล้วเด้งกลับ 1 ช่อง
  - มากกว่าทุกค่าในตาราง : เจอตัวล่างสุด เพราะเด้งมา 1 ช่องจากนอกตาราง
  - น้อยกว่าทุกค่าในตาราง : จะแสดงเป็น #N/A (เป็นกรณีเดียวที่จะ Error)
และถ้าข้อมูลเรียงมั่วจะเกิดอะไรขึ้น?

ผมได้ทำคลิปเดาการทำงานของ VLOOKUP Approximate Match แบบเป๊ะๆ เอาไว้ ใครสนใจลองไล่ดูได้ครับ
February 13, 2018

Cookie	Duration	Description
cookielawinfo-checkbox-analytics	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checkbox-functional	11 months	The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checkbox-necessary	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-others	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-performance	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
viewed_cookie_policy	11 months	The cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.

Author: Sira Ekabut

Series เรื่องการจัดการ Stock นี้มี 3 ตอน

ความสัมพันธ์ของ Stock สินค้า

มาเริ่มทำไฟล์ Excel บริหาร Stock กัน

ความสัมพันธ์พื้นฐานของการบริหาร Stock

จุดสั่งของเพิ่ม ( Reorder Point)

ใส่ Conditional Format ซะหน่อย

แยกความต่าง ระหว่างช่อง Input กับช่อง Output

ถ้าจะเพิ่มสินค้าล่ะ?

จะทำอะไรต่อดี?

คลิปวีดีโอ

ไฟล์ประกอบการทำตาม

หลักการทำงานของ Drop Down List แบบค้นหาได้

สรุปสูตร

อยากใช้ Drop Down หลายที่ทำไง?

แหล่งอ้างอิง

Download Template Skill Chart

Excel 2013 ขึ้นไป

Excel เก่ากว่า 2013

ลองทำของตัวคุณเองดูสิ

แนวคิด

การคำนวณระยะเวลาใน Part 1

การคำนวณระยะเวลาใน Part 2

การคำนวณระยะเวลาใน Part 3

รวมระยะเวลา

สรุปสูตรทั้งหมด

คำนวณค่าแรง

มาเริ่มใช้ Pivot Table Named Set กัน

ยังแก้อะไรได้อีกมั้ย?

รู้แล้วจะเอาไปทำอะไรดี?

สารบัญซีรีส์ Power Pivot

วิธีที่ 1 : ใช้ Flash Fill

วิธีที่ 2 : ใช้ Text to Column แบบ Delimited

วิธีที่ 3 : ใช้ Power Query Extract Data

วิธีที่ 4 : ใช้ Power Query Split

วิธีที่ 4 : ใช้สูตร FIND เพื่อหาตำแหน่ง – ตัวที่สอง

วิธีที่ 5 : ใช้สูตร SUBSTITUTE ช่วยหาพิกัดของ – ตัวที่สอง

วิธีที่ 6 : ใช้ REPLACE ในการตัดข้อความส่วนหน้าออกไปซะ เหลือแต่ข้างหลัง

วิธีที่ 7 : ใช้ MID เพื่อเลือกข้อความส่วนที่ต้องการ

วิธีที่ 8 : ใช้ TRIM + MID ตัดเอาส่วนที่ต้องการ

วิธีที่ 9 : ใช้ VBA สร้างฟังก์ชัน Split ขึ้นมาเอง

สรุปวิธีดึงข้อความ

ประเด็นที่ 1 : การกรอกวันที่ปัจจุบัน

ประเด็นที่ 2 : วิธีกรอกวันที่ใน Excel

ประเด็นที่ 3 : กรอกวันที่เป็น ค.ศ. แต่อยากแสดงปีเป็น พ.ศ. ทำไง?

ประเด็นที่ 4 : ค่าที่แท้จริงของวันที่ใน Excel

ประเด็นที่ 5 : การคำนวณเกี่ยวกับวันที่และเวลา

ประเด็นที่ 6 : วิธีการคำนวณวันของสัปดาห์(วันจันทร์-อาทิตย์)

ประเด็นที่ 7 : การกำหนดภาษาของวันที่

ประเด็นที่ 8 : ถ้าข้อมูลไม่ใช่วันที่แบบถูกต้อง จะแก้ยังไง?

สรุป