Month: May 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

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.

Month: May 2019

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1.1.1 วิธีใช้ FILTER + FILTER

1.1.2 วิธี FILTER+CHOOSE

1.2 ใช้ VLOOKUP + Helper Column

1.3 ใช้ ROW กับ SMALL มาช่วย แบบ Array Formula

กลุ่ม 2 : ใช้เครื่องมือเพื่อให้เกิดผลลัพธ์หลายค่า

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

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

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

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

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

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

Excel 2003

Excel 2007

Excel 2010

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

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

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

เว็บไซต์นี้ใช้คุกกี้ (Cookies)

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

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

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

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

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

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