Author: Sira Ekabut

การสร้างกราฟแผนภูมิใน Excel เบื้องต้น
กราฟหรือแผนภูมิที่ดีนั้นเป็นสิ่งที่ช่วยให้เราสามารถแสดงผลลัพธ์ของข้อมูลในรูปแบบที่เข้าใจง่ายขึ้น แบบว่า คนดูมองเแล้วเห็นภาพ และรู้ได้ทันทีว่าข้อมูลที่เรากำลังแสดงอยู่หมายถึงอะไร ดังคำกล่าวที่ว่า “รูปภาพ 1 รูปแทนคำนับพัน” นั่นเอง
ดังนั้นกราฟหรือแผนภูมิจึงเป็นสิ่งที่จำเป็นต่อการนำเสนองานเป็นอย่างยิ่ง เพราะช่วยประหยัดเวลาในการอธิบาย และทำให้การนำเสนอน่าสนใจ และเป็นที่จดจำมากขึ้น นอกจากนี้จากการวิจัย และประสบการณ์ส่วนตัวของหลายๆคน เห็นว่า คนเราจำภาพได้ดีกว่าตัวอักษรแน่นอน จริงมั๊ยครับ
วันนี้ผมจะมาแนะนำการทำกราฟเบื้องต้นก่อน ไล่ตั้งแต่เทคนิคการเลือกกราฟที่เหมาะสมเลย เพราะเป็นพื้นฐานที่สำคัญมาก แต่ในบทความถัดๆ ไปเราจะมาเจาะลึกเทคนิคขั้นสูงขึ้น เช่น การผสมกราฟ การใส่แกน X หรือ Y 2 อัน เป็นต้น
Step ของการทำกราฟ มีดังนี้
1. Think : คิดวัตถุประสงค์ของการนำเสนอกราฟ
2. Prepare : เตรียมข้อมูล (เช่น สรุปข้อมูลจาก Data ดิบ)
3. Create : สร้างกราฟ
4. Customize : ปรับแต่งกราฟ
5. Summarize : สรุปผลของกราฟ
เรามาดูที่ Step แรกกันก่อนครับ
1. Think : คิดวัตถุประสงค์ของการนำเสนอกราฟ
วัตถุประสงค์ของการทำกราฟนั้นมีอยู่ค่อนข้างหลากหลาย ส่วนตัวของผมเองมองว่าที่สำคัญคือเราต้องรู้ว่าเราอยากจะสื่อสารอะไร และถ้าเป็นไปได้ ควรวาดภาพในใจก่อนว่ากราฟควรจะออกมาหน้าตาแบบไหน (ทำในจิตนาการก่อน ค่อยทำใน Excel)
วัตถุประสงค์ที่เจอบ่อยๆ เช่น
- การเปรียบเทียบข้อมูล ไม่ว่าจะเป็น เทียบระหว่างแต่ละกลุ่ม, เทียบกับมาตรฐาน หรือ เป้าหมายบางอย่าง
- บอกการแจกแจงความถี่ เพื่อให้เห็นการกระจายตัวของข้อมูล
- บอกสัดส่วนขององค์ประกอบ : เพื่อบอกองค์ประกอบว่าแต่ละส่วนมีมากน้อยแค่ไหน
- การแสดงความสัมพันธ์ระหว่างตัวแปร เช่น การแสดงความสัมพันธ์ระหว่างตัวแปร 2 ตัว
- บอกแนวโน้ม คาดการณ์อนาคต
- แสดงให้เห็นลำดับเหตุการณ์ หรือ Timeline โครงการ
- อื่นๆ
Tips ในการเลือก Chart ที่เหมาะสม
1. กราฟที่เหมาะสมกับวัตถุประสงค์แต่ละแบบ ดูได้ที่นี่ http://labs.juiceanalytics.com/chartchooser/index.html
2. เลือกกราฟ Basic ที่สุดถ้าเป็นไปได้ : ผมไม่แนะนำให้ใช้กราฟในรูปแบบที่ประหลาดๆ โดยเฉพาะพวกกราฟ 3 มิตินะครับ เพราะนอกจากจะดูรกแล้ว ยังหลอกตาสุดๆ อีกด้วย
3. ถ้าจะใช้เปรียบเทียบข้อมูลระหว่างกลุ่ม : ผมแนะนำให้ใช้พวกการฟแท่ง ดีกว่ากราฟวงกลมนะครับ เพราะกรางวงกลมดูยากมากว่ามันมากน้อยแค่ไหน (คนเราเทียบความสูงต่ำ ง่ายกว่าเทียบมุมแคบกว้าง)
4. ถ้าจะแสดงแนวโน้ม : ผมแนะนำกราฟเส้น หรือ Scatter Plot แล้วใส่ Trend line จะเหมาะที่สุดครับ
2. Prepare : เตรียมข้อมูล
การเตรียมข้อมูลเพื่อการทำกราฟ หลายๆครั้ง เราอาจต้องพึ่งพาความรู้พื้นฐานเหล่านี้ก่อน
- Function พวกที่ใช้เพื่อการสรุปข้อมูล เช่น SUM, COUNT, AVERAGE, MAX, MIN, SUMIF, COUNTIF
- การใช้ Sort & Filter หรือ Advanced Filter => อ่านได้ที่นี่ การกรองข้อมูลใน Excel ด้วย Filter & Sort และ Advanced Filter
- การใช้ Pivot Table (เป็นวิธีที่สะดวกมาก แนะนำเลย) ใครสนใจการสรุปข้อมูลด้วย Pivot Table เชิญอ่านได้ที่ สรุปทุกสิ่งที่คุณควรรู้เกี่ยวกับ Pivot Table
- ปกติแล้ว เรามักจะเตรียมข้อมูลให้เหมือนกับเตรียมฐานข้อมูลตามปกติ คือ
  - ใส่ข้อมูลแต่ละ Record (แต่ละราย แต่ละกลุ่ม) แยกไว้คนละแถวกัน
  - ใส่การวัดข้อมูลในแต่ละมิติ ไว้แยกคอลัมน์กัน (คล้ายๆกับ Field ข้อมูลใน Database)
  - ตรงนี้ไม่ต้องซีเรียส เพราะสามารถปรับแต่งภายหลังได้ง่ายๆ มากๆ ครับ
  - ที่สำคัญควรใส่ Label ของข้อมูลให้ครบถ้วยด้วยถ้าเป็นไปได้
3. Create : การสร้างกราฟ
มาถึงขั้นตอนนี้ก็เข้าหน้าที่หลักของ Excel แล้วล่ะครับ
- เมื่อเรามีข้อมูลเตรียมไว้ในตารางแล้ว ให้เราลาก Selection ครอบคลุมพื้นที่ของข้อมูลทั้งหมด จากนั้นไปที่ Ribbon Insert => Chart => เลือกรูปแบบที่ต้องการตามที่ได้แนะนำไปข้างบน
- เมื่อเราเลือกรูปแบบกราฟไปแล้ว ถ้ากราฟมันสร้างออกมาสลับแกน x แกน y กับสิ่งที่เราคิดไว้แต่แรก เราสามารถกดปุ่ม Switch Row/Column ที่อยู่บน Ribbon Chart Tools => Design ได้เลยครับ (ต้องคลิ๊กที่ Chart ก่อน Ribbon นี้ถึงจะโผล่มาให้เห็น)
- บางครั้งเมื่อเราสร้างกราฟแล้ว ต่อไปอยากจะใส่ข้อมูลเพิ่มลงไปอีก มีวิธีง่ายๆ ที่ไม่ต้องเข้าไปนั่งแก้ที่ Select Data เลย นั่นก็คือ สามารถกด Copy ข้อมูลจากตาราง (Ctrl+C) แล้วเลือกที่กราฟ จากนั้นกด Paste (Ctrl+V) ลงไปเลยตรงๆครับ
  - ในตัวอย่างนี้ ผมเพิ่มทั้งแถวทั้งคอลัมน์ จึงต้อง copy 2 รอบครับ
4. Customize : ปรับแต่งกราฟ
ต่อไปเป็นขั้นตอนสุดท้าย นั่นคือการปรับแต่งกราฟ ซึ่งการที่เราจะปรับแต่งกราฟได้นั้น เราต้องรู้จักส่วนประกอบของกราฟซะก่อนครับ
ส่วนประกอบของกราฟ หลักๆ มีดังนี้
- Chart Area : พื้นที่ของกราฟทั้งหมด
- Plot Area : พื้นที่ที่มีการพลอตข้อมูลลงไปจริงๆ
- Label ป้ายกำกับ
  - Chart Title : ชื่อกราฟ
  - Axis Title : ชื่อแกน
    
    Horizontal : แกนนอน
    
    Vertical : แกนตั้ง
  - Legend : เพื่อบอกว่าซีรีส์อะไรคืออะไร
  - Data Label : บอกค่าของ Data
  - Data Table : ตารางข้อมูลต้นฉบับ
- Axes แกน
  - Axes
    
    Vertical Axis : แกนตั้ง
    
    Horizontal Axis : แกนนอน
  - Gridlines : เส้นกริด ทั้งทั้งแกนนอนแกนตั้ง และ Major, minor
- Series คือตัวข้อมูลจริงๆ
  - Series Name : ชื่อของข้อมูล
  - Series Value : ค่าของข้อมูล
- Category Label : ชื่อประเภทข้อมูล (มักอยู่ที่แกนนอน)
วิธีที่ง่ายที่สุดที่จะเข้าใจส่วนประกอบของกราฟ ก็คือ ให้ไปที่ Ribbon Layout ที่อยู่ภายใต้ Chart Tools เลยครับ (ต้องเลือกที่กราฟก่อน) ในนั้นจะแบ่งออกเป็นหลายๆ หมวด ดังนี้
- Current Selection : เอาไว้เลือกส่วนประกอบของกราฟ มีประโยชน์มากเวลามีส่วนประกอบหลายๆ อันบังกันอยู่ ลองคลิ๊กดูแล้วจะรู้ว่าแต่ละส่วนเรียกว่าอะไร
  - พอเลือกแล้วสามารถกด Format Selection เพื่อปรับแต่งแต่ละส่วนได้อีก
- เราสามาถ เพิ่ม/ลด ส่วนประกอบของกราฟได้ โดยไปที่เมนู Ribbon แต่ละอัน เช่น Chart Title, Axis Title, Legend, Data Label แล้วเลือก option จาก Drop down เป็นต้น
เป็นอย่างไรครับกับพื้นฐานการทำกราฟ การปรับแต่งกราฟไม่ได้จบแค่นี้ เดี๋ยวตอนหน้าเราจะมาเจาะลึกลงรายละเอียดมากกว่านี้ครับ ทั้งการผสมกราฟ การทำกราฟแกน y 2 อัน หรือแกน x 2อัน การประยุกต์สร้างกราฟที่ไม่มีให้เลือก และอื่นๆ อีกมากมาย อย่าลืมติดตามกันต่อไปนะครับ
February 16, 2014
เมื่อเกม RPG ถูกสร้างด้วย Excel

เชื่อหรือไม่ว่าเกมส์ RPG ถูกสร้างได้ด้วย Microsoft Excel + VBA ล้วนๆ

ด้วยฝีมือนักบัญชีชาวแคนาดา ชื่อว่า CARY WALKIN เค้าสามารถทำได้ครับ

เกมส์นี้ชื่อว่า Arena.Xlsm

หลายๆอย่างที่เกม RPG ชั้นนำมี เจ้าเกมนี้ก็มีหมดครับ ทั้งค่าพลังต่างๆ ไอเท็ม อาวุธ เวทย์มนต์ ลูกกระจ๊อก ไปจนถึง บอสใหญ่

ใครอยากลองเล่นไปโหลดได้ที่ http://carywalkin.ca/download-arena-xlsm/

ลองเล่นแล้วเป็นยังไงอย่าลืมมาเล่าให้ฟังกันบ้างล่ะครับ

February 10, 2014
รวม 5 เทคนิคการเขียนสูตรใน Excel ที่คุณอาจยังไม่รู้มาก่อน!
ครั้งก่อนผมได้แนะนำ รวม 10 เทคนิค Excel ง่ายๆ ที่ทำให้คุณทำงานเร็วขึ้น ไปแล้ว และได้การตอบรับดีมากๆ (ขอบคุณมากเลยครับ) วันนี้ผมเลยจะขอนำเสนอ รวม 5 เทคนิคการเขียนสูตรใน Excel ที่คุณอาจยังไม่รู้มาก่อน! ซึ่งเป็นเทคนิคการทำงานเกี่ยวกับการเขียนสูตรใน Excel ที่จะช่วยให้เพื่อนๆ เขียนสูตรได้เร็ว ถูกต้อง และตรวจสอบสูตรที่คนอื่นเขียนได้อย่างรวดเร็วอีกด้วย

1. หากเจอสูตรที่ใช่ ให้กด Tab เลย เพื่อเลือกใช้

เวลาเขียนสูตรใน cell ด้วยเครื่องหมาย = แล้วเริ่มพิมพ์ชื่อสูตร เช่น จะพิมพ์สูตร VLOOKUP
เมื่อเราเริ่มพิมพ์ว่า =V ไปเพียงตัวเดียว มันจะขึ้นรายชื่อสูตรทุกตัวที่ขึ้นต้นด้วยตัว V มาให้เลือก…
- ตรงนี้จริงๆเราสามารถดับเบิ้ลคลิ๊กเลือกที่ VLOOKUP ได้ หรือจะกดลูกศรขึ้นลงไปหาคำว่า VLOOKUP แล้วก็สามารถ กด Tab เพื่อเลือกได้เลย โดยไม่ต้องพิมพ์ให้จบ
- แต่ถ้าพิมพ์ L เพิ่มลงไปอีกตัวด้วยหลังจากตัว V มันก็จะกรองให้เลือกแต่สูตรที่มีคำว่า VL นำหน้า สูตรที่แสดงก็อาจจะเหลือแค่ VLOOKUP ตัวเดียว ก็สามารถกด Tab เลือกได้เลยเช่นกัน
- การกดเลือกแบบนี้มันจะใส่วงเล็บเปิดให้เองด้วย ดีกว่าพิมพ์เองเป็นไหนๆ
- อ่อ เกือยลืม ก่อนจะพิมพ์สูตรเสร็จ ตัววงเล็บปิดตัวสุดท้ายของสูตรไม่จำเป็นต้องพิมพ์ใส่ก็ได้นะ เสียเวลาครับ! (more…)
February 9, 2014
สอนทำเกม Sudoku ( Excel Sudoku)
วันนี้ผมจะมาแนะนำวิธีใช้ Excel สร้างเกม Sudoku (Excel Sudoku) อย่างง่ายขึ้นมา โดยที่จะมำให้ Excel ไฟล์นี้สามารถตรวจสอบได้ว่าเลขที่เราใส่ลงไปผิดกติกาของเกม Sudoku หรือไม่ แต่อันนี้จะไม่ฉลาดพอที่สามารถเฉลยเกม Sudoku ได้นะครับ ถ้าอยากได้แบบนั้นเดี๋ยวมี Link ให้อีกทีนึงท้ายบทความครับ
กติกาเบื้องต้นเกม Sudoku
- มีตาราง 9 x 9
- ใส่ได้แค่เลข 1-9
- ใส่เลขห้ามซ้ำกัน
  - ในแต่ละแถว
  - ในแต่ละคอลัมน์
  - ในแต่ละกรอบตาราง Block ใหญ่ 3 x 3 (เหมือนที่ตีกรอบหนาไว้)
- จะมีเลขใส่มาให้เบื้องต้นบางส่วนก่อน ถ้าเกมง่ายๆก็จะใส่มาให้เยอะหน่อย ถ้าเกมยาก ก็จะใส่มาให้น้อย
- เราจะเล่นเกม โดยมีเป้าหมายคือ ใส่เลขต่อให้ครบทุกช่อง โดยไม่ทำผิดกติกาข้างบน ถ้าทำได้ก็ถือว่าจบเกม
วิธีสร้างตัวตรวจกติกาเกม Sudoku
โหลดไฟล์ตัวอย่างได้ที่นี่
- ไฟล์ก่อนกรอกเลข (แสดงสูตรให้เห็น) sudoku-inwexcel(.xlsx)
- ไฟล์หลังกรอกเลข (ซ่อนสูตร + lock cell พร้อมให้ user เล่น) sudoku-inwexcel2(.xlsx)
Concept : เช็คว่ามีการทำผิดกติกาหรือไม่ ไล่ตามกติกาที่เขียนข้างบน แล้วนับว่ามีการทำผิดกติกากี่อัน/กี่ครั้ง จากนั้นนำผลการนับที่ได้มารวมกัน ถ้าสรุปแล้วมากกว่า 0 แสดงว่ามีการทำผิดกติกา
สร้างตารางขนาด 9×9
โดยปรับให้ขนาดของแถวและคอลัมน์ให้กว้างพอๆ กันตามรูป โดยให้เว้นช่องรอบนอกไว้เล็กน้อย เราจะเอาไว้เขียนสูตรเพื่อตรวจสอบความถูกต้อง ดังนั้นผมจึงเริ่มมุมซ้ายบนที่ช่อง C3 ครับ และอาจจัด Format ตัวหนังสือให้อยู่ตรงกลางทั้งแนวนอนและแนวตั้ง จะได้สวยๆ

กำหนดเงื่อนไข ว่าให้ใส่ได้แค่เลข 1-9 เท่านั้น
เราจะทำโดยใช้ Data Validation โดยเลือกพื้นที่ตาราง 9×9 แล้วไปที่ Ribbon Data=> Data Validation =>Data Validation…
ใน Allow ให้เลือก Whole Number (จำนวนเต็ม) Between Minimum=1 Maximum=9

สร้างตัวตรวจสอบว่าแต่ละแถวมีเลข 1-9 กี่ตัว
ใช้ COUNTIF มาช่วย =COUNTIF(แต่ละแถว,แต่ละเลข1-9)
ในที่นี้ผมเขียนว่า
```
=COUNTIF($C3:$K3,M$2)
```
เพราะ C3:K3 ผมคือแถวแรก ส่วน M2 ผมคือเลข 1
พอ copy ไปยังช่องอื่น สูตรจะใช้ได้โดยอันโนมัติ เพราะผม Lock คอลัมน์ไว้ที่คอลัมน์ C กับ K (เพื่อไล่ไปแต่ละแถวลงไปข้างล่าง) ส่วนช่อง M2 ล๊อคแถวไว้ เวลา Copy ไปจะได้ไล่ไปยังเลข 2-9 ต่อไปได้

สร้างตัวตรวจสอบว่าแต่ละคอลัมน์มีเลข 1-9 กี่ตัว
ใช้ COUNTIF มาช่วย =COUNTIF(แต่ละคอลัมน์,แต่ละเลข1-9)
ในที่นี้ผมเขียนว่า
```
=COUNTIF(C$3:C$11,$B13)
```
เพราะ C3:C11 ผมคือคอลัมน์แรก ส่วน B13 ผมคือเลข 1
พอ copy ไปยังช่องอื่น สูตรจะใช้ได้โดยอันโนมัติ เพราะผม Lockแถวไว้ที่แถว 3 กับ 11 (เพื่อไล่ไปแต่ละคอลัมน์ทางขวา) ส่วนช่อง B13 ล๊อคคอลัมน์ไว้ เวลา Copy ไปจะได้ไล่ไปยังเลข 2-9 ต่อไปได้

สร้างตารางตรวจเช็คการใส่ข้อมูลซ้ำ ในแต่ละ Zone 3×3 อันใหญ่
โดยใช้เทคนิค คล้ายๆ ที่เขียนไว้ใน http://www.thepexcel.com/prevent-duplicate-data-entry/
แต่ว่าเราจะไม่ใช้ Data Validation แล้ว เพราะเราได้ใช้ไปแล้วในชั้นตอนแรก และผู้เล่นอาจต้องการลองใส่เลขเข้าไปก่อน ถ้าใช้ Data Validation จะไม่ยอมให้ใส่เลย อันนั้นแรงเกินไป ดังนั้นเราจะเขียนทดไว้อีกทีนึงแทนตามรูป (ขวาล่าง)
โดยที่เราต้องเขียนสูตร 9 ครั้ง แยกแต่ละ Block ใหญ่ที่เป็นขนาด 3×3

ใน Block แรก (M13) เขียนว่า =COUNTIF($C$3:$E$5,C3) แล้ว copy paste แค่ zone ตัวเอง คือ ช่อง M13 ถึง O15

ใน Block ถัดไปทางขวา (P13) เขียนว่า
```
=COUNTIF($F$3:$H$5,F3)
```
แล้ว copy paste แค่ zone ตัวเอง คือ ช่อง P13 ถึง R15
ทำแบบนี้จบครบ 9 Block
ต่อไป เราจะเช็คว่าทั้ง 3 ตารางตัวช่วยที่เราสร้างขึ้นมานั้น มีเลขอะไรที่มากกว่า 1 หรือไม่ ถ้าหากมี แสดงว่าทำผิดกติกา
โดยใช้ COUNTIF มานับว่าช่วงที่เราหามีเลขอะไรมากกว่า 1 หรือไม่นั่นเอง โดยเขียนว่า
```
=COUNTIF(C13:K21,">1")
```
```
=COUNTIF(M3:U11,">1")
```
```
=COUNTIF(M13:U21,">1")
```
จากนั้นนำผลนับที่ได้มากรวมกัน ถ้ามากกว่า 0 แปลว่ามีการทำผิดกติกาครับ

ทำการ Link ไปแสดงผลด้านบน พร้อมใส่ Conditional Format ให้สวยงาม
เช่น ถ้า 3 ช่องนั้น SUM ได้มากกว่า 0 ให้เขียนว่า ไม่ ok เป็นต้น
ในที่นี้ผมเขียนว่า
```
=IF(M23>0,"ไม่ok","ok") 
```
โดย M23 ผมเป็นช่องที่เช็คผลรวมความผิดปกติ

สุดท้ายก็เช็คว่ากรอกครบทุกช่องแล้วหรือยัง ถ้าครบ 81 ช่องแล้วและยัง Ok อยู่ก็ถือว่าจบเกมครับ ^^
ในที่นี้ ผมเขียนเพิ่มไปว่า
```
=IF(M23>0,"ไม่ok",IF(COUNT(C3:K11)=9*9,"จบเกม","ok"))
```
การเตรียมการก่อนให้ผู้เล่นเริ่มเล่นเกมจริง
ทีนี้ ก็สามารถใส่เลขเริ่มต้น ตามแต่ละ Puzzle ที่ได้มาได้เลย
เพื่อความสะดวก และไม่ให้สับสนว่าเลขไหนเป็นเลขเริ่มต้น เลขไหนเป็นเลขที่ให้ผู้เล่นเพิ่มทีหลัง อาจใช้วิธี Fill สี หรือ ทำสีอักษรให้ต่างจากปกติก็ได้
เทคนิคการเลือกพื้นที่เฉพาะที่เป็นเลขเริ่มต้นของเรา
ให้เลือกพื้นที่ทั้งหมดในตาราง => Ctrl+G => Special => Constant => มันจะเลือกเฉพาะที่มีการกรอกข้อมูลเริ่มต้นไปแล้ว
จากนั้นค่อยใส่สี หรืออะไรให้ต่างจากปกติ แล้วค่อยซ่อนแถวและคอลัมน์ที่เป็นตัวทดของเราไปซะ เพื่อความสะอาดตาด้วยการเลือก แถว หรือ column แล้วคลิ๊กขวา => Hide

เพื่อความสะดวกในการลบเลขที่ให้ผู้เล่นเพิ่มเข้าไปเพื่อเริ่มเกมใหม่ เราอาจจะ Lock Cell เฉพาะที่เป็นเลขเริ่มต้นไว้ โดยจะไม่ Lock ช่องที่ให้ผู้เล่นกรอกเอง โดยทำดังนี้
เลือกพื้นที่ทั้งหมดในตาราง => Ribbon Home =>Cells =>Format =>ติ๊กเอา Lock Cell ออก (เพื่อปลดการ Lock Cell ทั้งหมดก่อน)
เลือกพื้นที่เฉพาะที่เป็นเลขเริ่มต้นของเรา มันจะเลือกเฉพาะที่มีการกรอกข้อมูลเริ่มต้นไปแล้ว จากนั้นกด
Ribbon Home =>Cells =>Format => ติ๊กให้ Lock Cell ทำงาน (เพื่อให้ Lock Cell เฉพาะที่มีการใส่เลขลงไปแล้ว) จากนั้นกด Ribbon Review => Protect Sheet
ถ้าติ๊กเอา Select Locked Cell ออก จะเป็นการห้ามให้ผู้เล่นมาเลือกช่องที่กรอกไว้แล้ว
จะใส่ Password หรือไม่ก็ได้ แล้วกด OK
เท่านี้ เวลาเล่น ก็จะไม่สามารถไม่ต้องกังวลว่าจะเผลอลบช่องที่เป็นตัวเลขเริ่มต้นแล้ว และเราก็สามารถเอาไปให้คนอื่นเล่นเกม Sudoku ของเราได้แล้วครับ
ที่นี้ถ้าใครอยากได้ตัวโปรแกรมแก้ปัญหา Sudoku แบบบอกเฉลยได้เลย ผมจะแนะนำ Link ให้ครับ
Sudoku Solver
แบบที่ 1 : แก้โดยไม่ใช้ VBA แต่ใช้ Iteration แทน
ดาวน์โหลดไฟล์
https://templates.office.com/en-us/Sudoku-puzzle-solver-TM10080972
สอนวิธีเขียน
- Building a Basic, Understandable Sudoku Solver Using Excel Iterative Calculation – Part 1/2
- Building a Basic, Understandable Sudoku Solver Using Excel Iterative Calculation – Part 2/2
แบบที่ 2 แก้โดยใช้ VBA
ลอง search Google ว่า Excel Sudoku Solver VBA ดูครับ
February 5, 2014
การห้ามใส่ข้อมูลซ้ำในพื้นที่ของ Excel
มีคนถามผมมาว่า เราจะห้ามใส่ข้อมูลซ้ำกันใน Excel ได้อย่างไร??
ผมคิดว่าเป็นคำถามที่มีประโยชน์มาก เลยจะมาแชร์เทคนิคกันครับ เทคนิคนี้จะใช้ฟังก์ชั่น COUNTIF กับ Data Validation มาช่วยครับ
ทำตามขั้นตอนนี้เลยนะครับ
ลาก Select Range กำหนดพื้นที่ตามต้องการ
โดยใน Range นั้นจะเป็น Zone ที่ห้ามมีข้อมูลซ้ำกัน จะใหญ่แค่ไหนก็ได้ จะเป็นคอลัมน์เดียว หรือแถวเดียว หรือหลายคอลัมน์ หลายแถวก็ได้ครับ สมมติผมกำหนดเป็น B2:G10
ไปที่ Ribbon Data => Data Validation => Data Validation…
กำหนดเงื่อนไขใน Setting
ในแถบ Setting ส่วนของ Allow เลือก Custom แล้ว ใส่ในแถบ Formula ดังนี้
```
=COUNTIF(พื้นที่ที่ห้ามซ้ำ โดยให้ lock cell ด้วย ,ช่องซ้ายบนของพื้นที่ห้ามซ้ำ)=1
```
นั่นคือ
```
=COUNTIF($B$2:$G$10,B2)=1
```
สิ่งที่มันทำ คือ จะนับว่า ในช่วงที่กำหนด มีคำที่ตรงกับช่อง B2 กี่อัน ซึ่งเราบังคับว่ามีได้แค่อันเดียว (ไม่นับช่องว่าง) ถ้ามันมีหลายอันมันก็จะขึ้นเตือนทันที
สังเกตว่าช่วง B2:G10 ที่กำหนดจะ Lock cell ไว้เพื่อไม่ให้เลื่อน แต่ตรง B2 ห้าม lock cell นะครับ เพราะเราต้องการให้มันเลื่อนไปครอบคลุมทั้งพื้นที่ (ใส้สูตรใน Active Cell แล้ว Copy ไปใช้ทั้งพื้นที่ รายละเอียดตามนี้)

กำหนด Error Message
ในแถบ Error Alert ใส่ Title และ Error Message ตามต้องการ
ในที่นี้ ผมใส่ Title ว่า ห้ามใส่ข้อมูลซ้ำ และ Error Message ว่า ห้ามใส่ข้อมูลซ้ำในพื้นที่ที่กำหนด
กด Ok เป็นอันจบ
ต่อไปหากลองใส่ข้อมูลให้ซ้ำกัน มันก็จะขึ้นเตือนแล้วครับ !!
February 4, 2014
การ Lookup ข้อมูล แบบ Case-Sensitive (สนตัวพิมพ์เล็กพิมพ์ใหญ่)
Excel Lookup ไม่ลืมหูลืมตา ไม่ดูตัวพิมพ์เล็กพิมพ์ใหญ่!?

ผมเดาว่าหลายๆ คนอาจยังไม่รู้ว่า ปกติ Excel จะ Lookup ข้อมูลแบบไม่สนตัวพิมพ์เล็กพิมพ์ใหญ่
นั่นคือ หากเราจะหาคำว่า B1 จากตารางที่มีทั้งคำว่า b1 และคำว่า B1
Excel จะเอาผลลัพท์จากคำที่เจอก่อน (วิ่งจากบนลงล่าง) โดยไม่สนว่ามันจะเป็นตัวพิมพ์เดียวกับคำที่เราต้องการหรือไม่ เพราะ Excel มันทำงาน Lookup โดยมองทั้งสองคำนั้นเหมือนๆ กันนั่นเอง

ทางแก้ไข

วิธีที่จะ Lookup ข้อมูล แบบ Case-Sensitive (สนตัวพิมพ์เล็กพิมพ์ใหญ่) ได้ จะต้องใช้ ฟังก์ชั่น EXACT และการทำงานของ Array Formula มาช่วย

Concept การใส่สูตร

{=INDEX(B2:B7,MATCH(1,–EXACT(A2:A7,A10),0))}

หาว่าคำที่ต้องการอยู่แถวที่เท่าไหร่ก่อน ด้วย MATCH กับ EXACT จากนั้นค่อยใช้ INDEX ดึงค่าที่สอดคล้องออกมา

หาว่าคำที่ต้องการอยู่แถวที่เท่าไหร่
- พื้นฐานคือ ฟังก์ชั่น EXACT ไว้เทียบว่าคำสองคำเหมือนกันทุกประการหรือไม่ โดยแสดงผลกลับมาเป็น TRUE/FALSE
  - หากเราใส่ว่า =EXACT(“B1″,”b1”) จะได้ FALSE เพราะต่างกันตรงตัวพิมพ์เล็กพิมพ์ใหญ่
  - ส่วน =EXACT(“B1″,”B1”) จะได้ TRUE เพราะเหมือนกันทุกประการ
  - หากลองกด F9 ดู จะได้ว่า EXACT(A2:A7,A10) เป็น {FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}
- ที่นี้หากเราเอา TRUE/FALSE มาดำเดินการทางตัวเลขใดๆก็ตาม (เช่น *1 หรือ ใส่ติดลบเข้าไปสองที — ) มันจะกลายเป็นเลข 1 กับ 0
  - หากลองกด F9 ดู จะได้ –EXACT(A2:A7,A10) ออกมาเป็น {0;0;0;1;0;0}
- จากนั้นเราจึงค่อย MATCH เลข 1 กับผลที่ได้ ว่าอยู่ที่ตำแหน่งแถวที่เท่าไหร่ หากมัน Match กันเจอ แสดงว่า คือคำที่เราต้องการนั่นเอง
  - จะได้ว่า MATCH(1,–EXACT(A2:A7,A10),0) คือ 4 (เพราะ 1 อยู่ลำดับที่ 4)
- ดังนั้นเมื่อใช้ INDEX มาผสม ก็จะได้ค่าที่ต้องการ
  - =INDEX(B2:B7,MATCH(1,–EXACT(A2:A7,A10),0))
  - = #VALUE! ได้ Error เพราะลืมบอกว่าเป็น Array Formula
- เราต้องกด CTRL+SHIFT+ENTER ด้วย (มันจะงอก ปีกกามาให้เอง) เพื่อให้มันทำงานแบบ Array Formula นั่นเองครับ
  - = 40 ถูกต้อง!!
February 3, 2014
เทคนิคการแปลงวันที่จาก พ.ศ. เป็น ค.ศ. แบบง่ายๆ ใน Excel
เราจะสามารถแปลงวันที่ที่กรอกมาเป็น พ.ศ. (วิธีที่ผิด) ให้กลายเป็น ค.ศ. (แบบที่ถูกต้อง) ได้อย่างไร มาดูกันครับ
บางครั้งเวลาเรากรอกข้อมูลใน Excel โดยตั้งใจกรอกเป็นวันที่ 31 มกราคม ปี พ.ศ. 2557 เราก็เลยกรอกลงไปว่า 31/01/2557 แต่สิงที่ Excel เข้าใจ คือ มันจะมองว่าเป็น ค.ศ. 2557 (หรือ พศ. 3100 )ต่างหาก!! ไม่ใช่ พ.ศ. 2557 อย่างที่เราอยากได้ (อันนี้ผิดในแง่ข้อมูลเลยนะครับ ไม่ใช่เรื่องของ Format)
ซึ่งบางคนคิดว่าไม่เห็นเป็นไร เราเข้าใจว่าเป็น พศ. 2557 เองก็ได้ อันนี้เป็นความคิดที่ไม่ถูกต้องครับ เพราะทุกอย่างจะผิดเพี้ยนไปหมด ทั้งวันที่นี้คือวันจันทร์ อังคาร พุธ หรืออะไรก็จะผิดหมด แถมบางปีมี 29 กพ. โผล่มาอีกทั้งๆที่ปีนั้นถ้าอยู่ถูกปฏิทินจะมีแค่ 28 กพ. ดังนั้นการแก้วันที่ให้ถูกปฏิทินจึงเป็นเรื่องสำคัญมาก
บางทีเราอาจมี Input ทำนองนี้อยู่มากมาย (เช่น Import มาจาก Database อื่นแล้วผิด Format มาเลย) จะไล่แก้ทีละอันก็ใช่เรื่อง… วันนี้ผมเลยมีวิธีแปลงข้อมูลพวกนี้ให้ถูกต้อง โดยแก้ปัญหา พศ. กับ คศ. แบบง่ายๆ มาให้ครับ
จริงๆทำได้หลายวิธีมาก ผมขอแนะนำซัก 2 แบบละกัน
แปลงวันที่วิธีแรก ใช้ฟังก์ชั่น EDATE มาช่วย
สมมติ ในช่อง A1 เป็น 31/01/2557 (excel เข้าใจว่าเป็น คศ. 2557 ) แล้วเราจะแปลงให้เป็น 31/01/2014 โดยใช้สูตรมาช่วย มาดูกันครับว่าทำยังไง
วิธีคือ =EDATE(ช่องวันที่,-543*12)
เดี๋ยวผมจะอธิบายว่าทำไมเขียนแบบนี้ครับ
ก่อนอื่นต้องรู้จักฟังก์ชั่น EDATE ซะก่อนครับ… คร่าวๆคือมันสามารถเพิ่ม/ลด วันเข้าไปในวันที่ที่เรากำหนด โดยมีหน่วยเป็นเดือน โดยไม่สนว่าแต่ละเดือนจะมีกี่วัน เช่นจะมี 28, 29, 30 หรือ 31 วันก็ไม่สน
วิธีการเขียนสูตร EDATE
```
= EDATE(start_date,months)
```
ตัวอย่างการทำงานของฟังก์ชั่น EDATE
ให้ A1 = 31/01/2013
- ถ้าใส่เลข months เป็นบวก มันจะเพิ่มเวลาไปในอนาคต เท่ากับจำนวน months
  - EDATE(A1,1) => 28/2/2013 (ไม่มีวันที่ 31/2/2013 จึงปรับเป็นวันสิ้นเดือนให้)
  - EDATE(A1,2) => 31/3/2013
  - EDATE(A1,3) => 30/4/2013 (ไม่มีวันที่ 31/4/2013 จึงปรับเป็นวันสิ้นเดือนให้)
  - EDATE(A1,4) => 31/5/2013
- ถ้าใส่เลข months ติดลบ มันจะย้อนอดีตเท่ากับจำนวน months
  - EDATE(A1,-1) => 31/12/2012
  - EDATE(A1,-2) => 30/11/2012 (ไม่มี 31/11/2012)
ใช้ EDATE แปลงจาก พ.ศ. เป็น ค.ศ.
ที่นี้ถ้าเลขต้นฉบับ สมมติช่อง C1 เป็น 31/01/2557 แล้วเราจะทำให้เป็น 31/01/2014
แปลว่ามันต้องย้อนอดีตไป 543 ปี => นั่นคือ 543*12 เดือน
ดังนั้นเราจะเขียนสูตรว่า
= EDATE(C1,-543*12) นั่นเองครับ (ติดลบ เพราะเลขต้องน้อยลง)
เท่านี้ก็จะได้เป็นวันที่ 31/01/2014 แล้ว (ถ้าเห็นเป็นเลขหลักหมื่นให้ลองเปลี่ยน Format เป็น Date ดูครับ)
ข้อสังเกต!! วิธีนี้กรณีของวันที่ 29 กพ. เช่น เดิมใส่เป็น 29/02/2556 ถ้าใช้สูตรนี้แปลงจะได้เป็น 28/02/2013 ซึ่งต่างจากวิธีล่างที่่จะได้วันที่ 01/03/2013 แทน ซึ่งผมมองว่าวิธีบนถูกต้องมากกว่าครับ เพราะเป็นเดือนเดียวกันด้วย แต่ก็แล้วแต่สถานการณ์นะครับ
แปลงวันที่วิธีที่ 2 ใช้ฟังก์ชั่น DATE, YEAR, MONTH, DAY
วิธีนี้เป็นวิธีที่เห็นได้ทั่วไป ซึ่งผมมองว่าค่อนข้างยุ่ง เพราะต้องเขียนเยอะ คือ แตกปี เดือน วันออกมา แล้วลบที่ปีไปด้วยเลข 543
นั่นคือ
- ปี = YEAR(ช่องวันที่)-543
- เดือน = MONTH(ช่องวันที่)
- วัน = DAY(ช่องวันที่)
- แล้วจับรวมกันด้วยฟังก์ชั่น DATE(year,month,day) จะได้ว่า
  - =DATE(YEAR(ช่องวันที่)-543,MONTH(ช่องวันที่),DAY(ช่องวันที่))
  - ถ้าเห็นเป็นเลขหลักหมื่นให้ลองเปลี่ยน Format เป็น Date ดูครับ
ข้อสังเกต!! วิธีนี้กรณีของวันที่ 29 กพ. เช่น เดิมใส่เป็น 29/02/2556 ถ้าใช้สูตรนี้แปลงจะได้เป็น 01/03/2013 แทน ซึ่งต่างจากวิธีบน ที่่จะยังได้วันที่ 28/02/2013 ซึ่งผมมองว่าวิธีบนถูกต้องมากกว่าครับ เพราะเป็นเดือนเดียวกันด้วย แต่ก็แล้วแต่สถานการณ์นะครับ
February 2, 2014
เล่นแร่แปรสูตร : การแปลงวันที่ Text ให้เป็นวันที่ Date
ปกติแล้ว Excel จะมีฟังก์ชั่นที่ชื่อว่า DATEVALUE ในการเปลี่ยนวันที่ในรูปแบบ Text ให้กลายเป็นรูปแบบ Date จริงๆ ที่เป็นตัวเลขอยู่แล้ว… แต่ฟังก์ชั่นนี้มีข้อจำกัดอยู่มาก คือ มันจะ Convert Text ได้แค่ในรูปแบบที่มันรู้จักเท่านั้น (ซึ่งมีไม่กี่แบบ คล้ายๆตอนที่เราพิมพ์ลงไปใน cell ปกติ แหละครับ ว่า 31/1/2014 หรือ 31-Jan-2014 หรือ 31-01-2014 แล้ว excel มันจะฉลาดแปลงเป็นวันที่ได้เอง)

ดังนั้น ถ้าหากเรามี Date ในรูปแบบแปลกไปจากที่มันรู้จัก เช่น 31012014 หรือ 20140131 อะไรแบบนี้ ฟังก์ชั่นนี้ก็จะเอ๋อไปเลย

วันนี้ผมมีวิธีแก้มาแนะนำหลากหลายวิธีด้วยกันครับ ลองติดตามดูได้

วิธี 1 ตัด Text ออกเป็นส่วนๆ แล้วเชื่อม (ยาก)

Concept : ใช้พวกฟังก์ชั่น LEFT RIGHT MID หรือ Text to Column ช่วยตัดวันเดือนปีแยกออกจากกัน แล้วค่อยมาเชื่อมกันอีกทีด้วยฟังก์ชั่น DATE

สมมติว่า ต้นฉบับ อยู่ในช่อง A1 คือ “20140131” (ปีเดือนวัน)
- ตัดปี = LEFT(A1,4)
- ตัดเดือน = MID(A1,5,2)
- ตัดวัน =RIGHT(A1,2)
- จับรวมด้วย DATE (year,month,day)
  - =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))
สมมติว่า ต้นฉบับ อยู่ในช่อง A1 คือ “31012014” (วันเดือนปี)
- ตัดวัน =LEFT(A1,2)
- ตัดเดือน = MID(A1,3,2)
- ตัดปี = RIGHT(A1,4)
- จับรวมด้วย DATE (year,month,day)
  - =DATE(RIGHT(A1,4),MID(A1,3,2),LEFT(A1,2))
วิธี 2 แปลง Format ของ Text ให้ DATEVALUE รู้จัก (ง่าย)

ในเมื่อฟังก์ชั่น DATEVALUE มันรู้จักรูปแบบแค่บางอย่าง เราก็ช่วยมันหน่อย โดยใช้ฟังก์ชั่น TEXT ช่วยแปลง FORMAT ให้

สมมติว่า ต้นฉบับ อยู่ในช่อง A1 คือ “20140131” (ปีเดือนวัน)
- ใช้ TEXT แปลง =TEXT(A1,”0000-00-00″) <= ปี 4 หลัก เดือน 2 หลัก วันที่ 2 หลัก…
- ใช้ DATEVALUE แปลงค่าให้เป็น Date จริงๆ =DATEVALUE(TEXT(A1,”0000-00-00″))
- อาจได้ค่าออกมาเป็นตัวเลขธรรมดา ให้เปลี่ยน Format เป็น Date ก็จะเห็นเป็นวันที่ครับ
สมมติว่า ต้นฉบับ อยู่ในช่อง A1 คือ “31012014” (วันเดือนปี)
- ใช้ TEXT แปลง =TEXT(A1,”00-00-0000″) <= วันที่ 2 หลัก เดือน 2 หลัก ปี 4 หลัก
- ใช้ DATEVALUE แปลงค่าให้เป็น Date จริงๆ =DATEVALUE(TEXT(A1,”00-00-0000″))
- อาจได้ค่าออกมาเป็นตัวเลขธรรมดา ให้เปลี่ยน Format เป็น Date ก็จะเห็นเป็นวันที่ครับ
February 1, 2014
รวม 10 เทคนิค Excel ง่ายๆ ที่ทำให้คุณทำงานเร็วขึ้น
เทคนิค Excel ในบทความนี้ จะมาช่วยรักษาสิ่งที่มีค่ามากๆ ในยุคปัจจุบัน และจะมีค่ามากขึ้นเรื่อยๆ นั่นก็คือ “เวลา” ดังนั้นการทำงานให้เร็ว (และถูกต้อง) ย่อมจะช่วยให้เรามีเวลาไปทำอะไรที่เราอยากทำมากขึ้น
ผมเลยถือโอกาสนี้รวบรวม 10 เทคนิค Excel ง่ายๆ ที่ทำให้คุณทำงานเร็วขึ้น มาให้เพื่อนๆ ได้อ่านกันครับ
1. ดูข้อมูลสรุปทางสถิติจากจาก Status Bar ด้านขวาล่าง
บางครั้งเราต้องการบวกเลข นับเลข หรือหาค่าเฉลี่ยเลขคณิต แบบเร็วๆ (โดยไม่ได้จะเอาไปคำนวณต่อ) เราไม่ต้องเขียนสูตรให้เมื่อย แค่ให้ลาก Select ครอบคลุมข้อมูลที่ต้องการ แล้วไปดูที่ Status Bar ที่อยู่ขวาล่าง มันก็แสดงข้อมูลให้เพียบแล้ว
Tips : เราเลือกได้ว่าใน Status Bar จะแสดงการสรุปอะไรบ้างโดยให้ลองคลิ๊กขวาที่ Status Bar ด้านล่าง แล้วติ๊กเลือกเอาได้ครับ ว่าจะเอาอะไร เช่น Average, Count, Numerical Count, Maximum, Minimum, Sum
2. หัดใช้ Keyboard คีย์ลัดบ้าง
การใช้คีย์ลัดช่วยให้เราทำงานได้เร็วมากขึ้นจริงๆ ครับ (และดูเท่มากด้วย!) โดยเฉพาะตัวไหนที่เราใช้บ่อยๆ ให้จำให้ได้เลย
สำหรับผมเอง เช่น
- Ctrl+C : Copy
- Ctrl+X : Cut
- Ctrl+V : Paste
- Ctrl + Z : Undo
- Alt+ = : Auto Sum
- F2 : เข้าสู่ Edit Mode
- F3 : Paste Name (ชื่อที่ตั้งเอาไว้แล้ว)
- F4 : ล๊อค Cell (ใน Edit Mode ) / Repeat ทำซ้ำ
- F5 : ให้ลากครอบ Cell Reference ใน Formula Bar แล้วกด F5 แล้ว Enter เพื่อไปเลือก Cell/Range นั้นๆ ได้เลย
- F9 : แปลงสูตรเป็น Value (ใน Edit Mode) / Recalculate
สามารถไปดูสรุปคีย์ลัดที่สำคัญ พร้อมวิธีการจำได้ที่ =>

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

3. ใช้ Quick Access Toolbar
นอกจากเราจะมี Ribbon มาตรฐานให้ใช้แล้ว เรายังสามารถ Add คำสั่งที่เราใช้บ่อยๆ ไว้ที Quick Access Toolbar ที่อยู่มุมบนซ้ายได้ด้วย รวมถึงสามารถใส่คำสั่งที่ไม่มีใน Ribbon ปกติได้อีกต่างหาก และที่สำคัญยังใช้ร่วมกับคีย์ลัด ปุ่ม Alt+1, Alt+2 …. (ไปเรื่อยๆ) ได้อีกด้วย
สามารถดูวิธีเพิ่มปุ่มลงใน Quick Access Toolbar ได้ที่ => http://www.thepexcel.com/quick-access-toolbar/
4. ใช้ Defined Name ในการอ้างอิงสูตร
การตั้งชื่อให้ Cell หรือ Range มีข้อดีหลายอย่าง เช่น
- อ่านสูตรเข้าใจได้ง่ายกว่า เพราะสามารถใส่เป็นคำที่มีความหมายได้
- บริหารจัดการง่าย เวลาแก้ไขการอ้างอิง ก็สามารถแก้ได้ที่ Name Manager ที่เดียวเลย เหมาะอย่างยิ่งกับการอ้างอิงไปที่ช่องที่มีการอ้างถึงบ่อยๆ หรือ Range ที่อาจมีการเพิ่มเติม Item ภายหลัง เช่น Reference Table
- เวลาใช้ VBA อ้างอิง หากอ้างไปที่ Defined Name จะสะดวกกว่าอ้างไปที่ชื่อ Cell เพราะเวลาเราแทรก Cell แล้ว การอ้างอิงใน VBA จะไม่เลื่อนตามให้ เหมือนการอ้างอิงใน Sheet ปกติ ซึ่งจะไม่เป็นปัญหากเลย หากเราใช้ Defined Name แทน
- ไม่ต้องกด Ctrl+Shift+Enter เวลาใช้เป็น Array Formula
วิธีตั้งชื่อ Definced Name ให้ Cell หรือ Range
1. เลือก Cell หรือ Range ที่ต้องการตั้งชื่อ
2. ไปที่ Name Box ที่อยู่ซ้ายมือของช่องใส่สูตร
3. พิมพ์ชื่อที่ต้องการแล้วกด Enter
วิธีบริหารจัดการชื่อ ให้ไปที่ Ribbon Formula => Name Manager (หรือกด Ctrl + F3)
5. สร้าง Custom List เพื่อช่วย Auto Fill / Sort
บางทีเราต้องการให้ Excel สามารถ Auto Fill ข้อมูลให้เราโดยอัตโนมัติ เช่น A-Z หรือ ก-ฮ แต่เชื่อหรือไม่ว่า Excel ทำไม่ได้ เพราะมันไม่รู้จัก List พวกนี้ (ไม่เข้าใจมันจริงๆ ว่าทำไมแค่นี้ไม่รู้จัก…)
แต่ไม่ต้องกลัวไป เราสามารถสอนให้ Excel รู้จัก List ที่เรากำหนดเองได้ เพื่อให้มัน Autofill และ Sort ตาม List ที่เรากำหนดเป็นด้วย (เลือกได้ใน Sort Option ว่าจะ Sort Order ตาม อะไร)
วิธีการสร้าง Custom List
- ไปที่ Excel Option => Popular => Edit Custom List
- จากนั้นสามารถใส่คำที่ต้องการลงไปได้เลยในช่อง List entries (แยกคำด้วย Enter) หรือจะ Import จาก Range ที่มีอยู่ก็ได้ครับ (แต่ต้องเป็น Text ที่เป็น Value ไม่ค้างเป็นสูตร)
- อย่างตัว A-Z นี้ผมสร้างจากสูตร =CHAR(65), =CHAR(66) ไปเรื่อยๆ
  - วิธีคือ สร้างเลขเรียงกันในคอลัมน์นึง เช่น A1,A2…
  - แล้วค่อยเขียน =CHAR(A1) ไว้คอลัมน์ข้างๆ แล้ว Copy ลากยาวลงมา…
  - จากนั้นค่อย Paste เป็น Value อีกที
6. เรียนรู้ Function ให้มากขึ้น
Excel มีฟังก์ชั่นเยอะแยะมากมาย ซึ่งบางฟังก์ชั่นออกแบบมาให้เราทำงานได้ง่ายมากๆ ถ้าเรารู้จักใช้มัน
แต่ในทางกลับกัน หากเราไม่รู้ว่ามี Function แบบนั้นแบบนี้อยู่ เราก็อาจทำงานถึกโดยไม่จำเป็น…
ที่ผมเจอบ่อย คือ บางทีเราต้องการจะเอาผลลัพธ์แต่ละช่องมาคูณกัน แต่คูณกันหลายตัวมาก… บางคนไม่รู้จักฟังก์ชั่น PRODUCT ก็จะจับคูณกันตามปกติ แต่ถ้าหากรู้จักฟังก์ชั่นนี้ ก็จะพบว่าสามารถลากทั้งแถบได้เลย เช่น
นี่แค่ฟังก์ชั่นเดียวนะ แล้วลองคิดดูว่าเราอาจเผลอทำอะไรถึกๆ โดยไม่จำเป็นอยู่เยอะแยะแค่ไหนครับ! (เช่น ฟังก์ชั่นคล้ายๆกับแบบนี้ก็มี SUMPRODUCT อีก ที่เอาไว้คูณแต่ละคู่อันดับแล้วจับมาบวกกัน)
ดังนั้นถ้าเริ่มรู้สึกว่าตัวเองถึกแปลกๆ ให้ลอง Google หาคำตอบดูก่อน เผื่อมีคนเจอปัญหาแบบเดียวกับเราแล้วเจอทางแก้ไขแบบง่ายๆ แล้วนะครับ
7. ใช้ Picture Link ช่วยตรวจผลกระทบที่ช่องปลายทาง
เวลาต้องการตรวจผลกระทบต่อการกรอกข้อมูลช่องนึงไปยังอีกช่องนึง (โดยเฉพาะช่องที่อยู่ไกลๆ) เราสามารถใช้ Picture Link มาช่วยได้ (มีตั้งแต่ Excel 2007 ขึ้นไป) โดย
- ให้ Copy Range ปลายทาง ที่ต้องการดูผลลัพธ์
- Paste ที่ใกล้ๆ กับช่องต้นทาง โดยให้เลือก => Paste =>As Picture => Paste Picture Link
- ย่อ/ขยาย/เคลื่อนย้ายรูปได้ตามสะดวก
- ลองเปลี่ยนค่าต้นทางดู เพื่อดู Effect ที่ปลายทาง
8. ใช้ Paste Column Widths แทนการนั่งปรับความกว้างของคอลัมน์เอง
ผมเคยเจอบางท่านนั่งจัดความกว้างของคอลัมน์ใน Excel ให้เท่ากับต้นฉบับ ซึ่งบางคนนั่งจัดได้เป๊ะมาก น่านับถือในความพยายามยิ่งนัก! แต่ผมจะบอกว่ามันไม่จำเป็นเลย ในเมื่อเราสามารถ Copy แม้แต่ความกว้างของคอลัมน์ได้
วิธีการ
- เลือกที่ cell หรือ range ต้นฉบับที่ต้องการ copy ความกว้างคอลัมน์มา
- กด copy
- ไปเลือกที่ช่องปลายทาง
- กด Paste Special => Column Widths
9. Lock Format Painter เอาไว้ เพื่อให้ Paint ช่องปลายทางได้ทีละหลายๆ รอบ
ผมคิดว่าหลายๆ ท่านคงเคยใช้ Format Painter กันบ้างแล้ว แต่ไม่แน่ใจว่าเวลาสมมติอยากจะ copy format จากช่องนึง ไป Paste ยังช่องปลายทาง หลายๆ ช่องที่ไม่ติดกัน เพื่อนๆ ทำยังไง
ถ้าคนไม่รู้เทคนิค อาจใช้วิธี เลือกช่องต้นฉบับ … แล้วกดปุ่ม Format Painter 1 ที… แล้วกดช่องปลายทาง 1 ที… จากนั้น เริ่มใหม่ ไปที่ช่องต้นฉบับ อีกที painter อีกที ปลายทางอีกที…ไปเรื่อยๆ.. ซึ่งเสียเวลามากครับ!!
วิธีที่ดีกว่าคือ
1. ให้เลือกช่องต้นฉบับ
2. ดับเบิ้ลคลิ๊ก ที่ปุ่ม Format Painter (ปุ่มจะถูกเลือกค้างไว้เลย ต่างจากการกดปกติที่กดเพียง 1 ที)
3. เลือกช่องปลายทางกี่ทีก็ได้ตามต้องการ
4. หากจะเลิก Paint ค่อยกลับมากดปุ่ม Format Painter อีกครั้งหนึ่ง
10. ทำงานใน Excel แล้วค่อยใช้ Copy ไป Paste as Link ลงที่อื่น
เมื่อมีการทำข้อมูลเป็นตาราง มีการคำนวณ หรือมีกราฟ ผมแนะนำให้ใช้ Excel ทำงาน แล้วค่อย Copy เป็น Link ไปยัง MS Word หรือ PowerPoint ปลายทางแทน
เหตุผล :
- เนื่องจาก Excel มีความยืดหนุ่นสูง เวลาค่า input บางอย่างในตารางเปลี่ยนไป ค่า output จะถูกคำนวณใหม่ทันที ไม่ต้องมาพิมพ์ซ้ำลงไปอีก (จะเนื่องจากอะไรก็แล้วแต่ เช่น เจ้านายสั่งเพิ่ม เราทำผิดเอง หรือข้อมูลใหม่เพิ่งจะมา)
- การรวมข้อมูลอยู่ในที่เดียวกันคือใน Excel แล้ว Link ไปแสดงในที่ต่างๆ ย่อมมีประสิทธิภาพมากกว่าทำข้อมูลในหลายๆที่และ ทำให้ไม่มี conflict ด้านข้อมูล
วิธี Copy ไป Paste เป็น Link :
1. ให้ Copy ข้อมูลตาราง หรือ กราฟใน Excel
2. เปิด MS Word หรือ PowerPoint ที่เป็นโปรแกรมปลายทาง
3. Paste => Paste Special => เลือก Paste Link
ป็นอย่างไรบ้างครับกับ รวม 10 เทคนิค Excel ง่ายๆ ที่ทำให้คุณทำงานเร็วขึ้น ถ้าเพื่อนๆ มีเทคนิคอื่นๆ จะแชร์ก็สามารถ comment ไว้ได้ หรือจะไปพูดคุยกันใน inwexcel Facebook Page ก็ได้นะครับ
ในตอนต่อไป ผมจะมาแชร์ รวม 5 เทคนิคการเขียนสูตรใน Excel ที่คุณอาจยังไม่รู้มาก่อน! ใครสนใจ คลิ๊กเลยครับ ^^
February 1, 2014
เทคนิคการลบ Hyperlink และป้องกันไม่ให้ Excel ใส่ Hyperlink เองแบบอัตโนมัติ
บ่อยครั้งที่เราไม่อยากให้ Excel ใส่ Hyperlink ให้เราเองโดยอัตโนมัติ จริงๆ เราสามารถไป set ค่าได้ครับ และมีวิธีเอา Hyperlink ที่มีอยู่ออกแบบง่ายๆ ด้วย ทำยังไงมาดูกัน

วิธีการป้องกันไม่ให้มี Auto Hyperlink

สามารถไป set ค่าได้ใน Options => Proofing => Auto Correct Options => Auto Format As You Type

จากนั้นเอาติ๊กถูกหน้าคำว่า Internet and Network path with hyperlinks ออกครับ

วิธีลบ Hyperlink ที่มีอยู่ออกแบบง่ายๆ

Excel 2010 ขึ้นไป

วิธีที่ 1 : คลิ๊กขวา => Remove Hyperlinks

วิธีที่ 2 : ไปที่ Ribbon Home ส่วนของ Editing => Clear => Remove Hyperlinks

Excel ver. ต่ำกว่า 2010

มี step ดังนี้
1. ให้ Copy ค่าจากช่องที่ว่างๆอยู่ แล้วมา Paste Special ลงช่องที่มี Link
2. แล้วให้เลือก Option ของ Paste Special เป็น Multiply
ป.ล. วิธีนี้ผมลองใน Excel 2007 แล้ว Hyperlink หายไปจริง แต่ Format พวกสีตัวอักษรกับการขีดเส้นใต้ยังอยู่ครับ ต้องมาแก้ format อักษรอีกที
January 22, 2014
ภาพวาดงามๆ โดย ศิลปินผู้ใช้ Excel เป็นเครื่องมือ (Excel Artist)

จะมีกี่คนที่คิดว่า Excel สามารถวาดรูปอย่างจริงจังออกมาสวยงามได้ไม่แพ้โปรแกรมแพงๆ อย่าง Illustrator หรือ Photoshop

ผมจะแสดงให้ดูครับว่า Excel วาดรูปได้สวยงามแค่ไหน

วาดรูปด้วย Shape

คุณปู่ชาวญี่ปุ่นที่มีชื่อว่า Tatsuo Horiuchi เค้าใช้ Excel วาด Shape ออกมาทีละชิ้น แล้วเอามาประกอบกันเป็นรูปสุดอลังการได้สวยงามมากจริงๆครับ

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

ไม่ต้องคิดไรมาก ลองดูรูปผลงานของแกได้เลยครับ

ใครไม่เชื่อว่าวาดด้วย Excel จริง โหลดไฟล์นี้ไปเปิดเล่นได้เลย excel-art (.zip)

Video การวาดรูป Gundam ด้วย Excel

ส่วนอันนี้เป็นผลงานของ shukei01 ครับ เป็น Video การวาดรูป Gundam ด้วย Excel ซึ่งก็ inw ไม่แพ้กันเลยครับ…

วาดรูปด้วยการ Fill สีลงช่อง แล้วทำให้ช่องขนาดเล็กๆ ละเอียดๆ

มักใช้เลียนแบบกราฟฟิกของเกมสมัยก่อนที่ภาพยังไม่ค่อยละเอียดมาก แต่ผมไม่นับพวกที่ใช้ plugin ทำนะครับ แบบนั้นมันไม่ใช่ศิลปะ หึหึ

Pokemon โดย thetokomitsu

รวมมิตรตัวละครจากเกม (โดย synbios16)

ยังมีอีกเยอะครับ ไปดูได้ที่ http://www.hongkiat.com/blog/microsoft-excel-artworks/

เห็นอย่างนี้แล้ว อยากวาดรูปด้วย Excel กันบ้างมั๊ยครับ??

January 18, 2014
เทคนิคการลบข้อมูลเก่าที่ค้างอยู่ใน Pivot Table
เวลาที่เราทำ Pivot Table เสร็จแล้วมีการเปลี่ยนแปลงข้อมูลใน Source Data อาจทำให้มีเหตุการณ์ที่มี Item ที่เคยอยู่ใน Source Data เก่าตกค้างใน Drop Down List ใน Row Label, Column Label, หรือ Filter ได้
หากเพื่อนๆใช้ Excel ตั้งแต่ Version 2007 ขึ้นไป สามารถแก้ไขเหตุการณ์ดังกล่าวได้ง่ายมากๆ โดย
1. คลิ๊กขวาที่ตาราง Pivot
2. เลือก PivotTable options
3. เลือก Data tab
4. ในหมวด Retain Items เลือก None
5. OK แล้วกด Refresh ตาราง Pivot อีกครั้ง
สำหรับ Excel Version เก่า สามารถไปโหลด Script VBA เพื่อแก้ไขได้ที่ http://www.contextures.com/xlPivot04.html
January 14, 2014
ลูกเล่นเกี่ยวกับ Gridlines
คำว่า Gridlines ในที่นี้คือ เส้นแบ่งช่อง ไม่ใช่กรอบของช่องแต่ละช่อง หรือ Border นะครับ

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

เราสามารถซ่อนไม่ให้มองเห็นบนหน้าจอคอมพ์ได้ ซึ่งทำได้ 2 วิธี ซึ่งดีกว่าการใช้วิธีถม Fill Color ด้วยสีขาวแน่นอน
1. ไปที่ Ribbon แถบ View แล้วติ๊ก Gridlines ออก
2. ไปที่ Ribbon แถบ Page Layout => Sheet Options แล้วติ๊ก View ตรงแถบ Gridlines ออก
การ Print เส้น Gridlines

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

ไปที่

Ribbon แถบ Page Layout => Sheet Options แล้วติ๊ก Print ตรงแถบ Gridlines ให้ติ๊กค้างเอาไว้
January 8, 2014
เล่นแร่แปรสูตร: หาว่าใน cell นั้นๆ มีตัวอักษรที่เรากำหนดอยู่กี่ตัว
สถานการณ์

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

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

ส่วนประกอบ
- LEN เพื่อนับจำนวนตัวอักษรทั้งหมด
- SUBSTITUTE(text,old_text,new_text,[instance_num]) เพื่อแทนที่ตัวอักษร
Concept การผสมสูตร
- ใช้ LEN เพื่อนับตัวอักษรทั้งหมดว่ามีกี่ตัว
- ใช้ SUBSTITUTE แทนที่ตัวอักษรที่เราต้องการหาด้วย “” (เหมือนลบตัวอักษรนั้นทิ้ง)
- ใช้ LEN นับคำที่ SUBSTITUTE แล้ว จากนั้นเอาไปหักออกจากที่นับไว้ตอนแรก
ขั้นตอนการผสมสูตร

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

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

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

มี 2 วิธี คือ

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

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

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

=DATE(2014,3,1)-1

= 28/02/2014

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

=DATE(2014,3,0)

= 28/02/2014

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

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

January 8, 2014
วิธีสุ่ม item จากรายการที่เราเตรียมไว้
บางครั้งเพื่อนๆ อาจต้องการสุ่ม item ซึ่งอาจจะเป็นคำบางคำ หรือ เลขบางเลข ออกมาจากรายการที่เราเตรียมไว้ แล้วเราจะทำอย่างนั้นด้วย Excel ได้ยังไง มาดูกันครับ
แน่นอนว่าการจะบรรลุวัตถุประสงค์อย่างหนึ่ง ไม่จำเป็นต้องมีการผสมสูตรเพียงแบบเดียว บางทีเพื่อนๆ อาจเจอหรือค้นพบการผสมสูตรที่มีประสิทธิภาพกว่าผมก็ได้ ถ้าเจอแบบนั้นแล้วอย่าลืมมาแชร์กันบ้างนะครับ!
สุ่ม item แบบที่ 1 : เขียนรายการที่จะสุ่มลงไปในสูตรเลย
ส่วนประกอบ
- RANDBETWEEN (bottom,top) = ตัวสุ่มจากช่วงของเลขที่กำหนด
- CHOOSE (index_num,value1,value2,…) = กำหนดว่าจะเอาผลลัพธ์ที่เท่าไหร่มาแสดง
Concept การผสมสูตร
- ถ้าจะใช้วิธีนี้ ก่อนอื่นเราต้องรู้ว่าจะมีรายการที่จะให้สุ่มทั้งหมดกี่รายการ สมมุติว่า 5 อัน
- ใช้ RANDBETWEEN สุ่มเลขระหว่าง 1-5 ออกมาก่อน
- จากนั้นได้ผลลัพธ์อะไร (เลขสุ่มระหว่าง 1-5) ก็เอาไปใส่ใน index_num ของฟังก์ชั่น CHOOSE อีกทีซึ่งเตรียมเขียนรายการที่จะสุ่มทั้ง 5 รายการไว้แล้ว
ขั้นตอนการผสมสูตร
- เขียนสูตรในช่องไหนก็ได้ ว่า
```
=RANDBETWEEN(1,5)
```
ได้เลขสุ่ม ระหว่าง 1-5
```
=CHOOSE (เลขสุ่ม,รายการที่1,รายการที่2,รายการที่3,รายการที่4,รายการที่5)
```
ผสม!!
```
 =CHOOSE (RANDBETWEEN(1,5),รายการที่1,รายการที่2,รายการที่3,รายการที่4,รายการที่5)
```
หมายเหตุ : รายการที่ xxx ถ้าเป็นตัวหนังสือต้องใส่ในเครื่องหมายคำพูด (“”) ด้วย แต่ถ้าเป็นตัวเลข หรือ เป็นสูตรก็ใส่ลงไปตรงๆ ได้เลย
- จากนั้นกด F9 เพื่อ Random ได้เลย
สุ่ม item แบบที่ 2 : มีรายการที่จะสุ่มอยู่ในตาราง Excel เป็น Range คอลัมน์นึง
ส่วนประกอบ
- RANDBETWEEN (bottom,top) = ตัวสุ่มจากช่วงของเลขที่กำหนด
- INDEX (reference,row_num,[column_num]) = ดึงค่ามาแสดงจากพิกัดที่กำหนด
- COUNTA (value1,value2,…) (Optional) = นับว่ามีกี่ช่องที่ไม่ว่างเปล่า
Concept การผสมสูตร
- ถ้าจะใช้วิธีนี้ ต้องเขียนรายการที่จะสุ่มลงไปในคอลัมน์ซักอันนึงก่อน สมมุติเขียนใน Column B (เพื่อให้ง่าย ให้เขียนโดยไม่ต้องมีหัวตารางนะครับ และให้เขียนต่อกันลงไปเรื่อยๆ อย่าเว้นช่อง)
- วิธีนี้ ถ้าจะกำหนดจำนวนรายการแน่นอนจะง่ายมาก แต่ในที่นี้เราจะทำแบบเผื่อให้มาเพิ่มรายการทีหลังก็ได้ (Dynamic Range) โดยไม่ต้องแก้สูตรใหม่ด้วย
- ใช้ COUNTA เพื่อนับว่าที่เราใส่ลงไปใน Column B มีกี่รายการ (ที่ไม่ใช่ค่าว่างเปล่า) สมมติว่านับได้ bbb รายการ
- ใช้ RANDBETWEEN สุ่มเลขระหว่าง 1 ถึง bbb ออกมาก่อน
- ใช้ INDEX ลากคลุม Column B เอาไว้ เป็น Reference จากนั้นให้เลขสุ่มเป็น row_num
ขั้นตอนการผสมสูตร
- เขียนรายการที่จะสุ่มลงไปในคอลัมน์ B จำนวน bbb รายการ
- เขียนสูตรในช่องไหนก็ได้ ที่ไม่ใช่คอลัมน์ B ว่า
```
=COUNTA(B:B)
```
สมมติว่านับได้ bbb รายการ
```
=RANDBETWEEN(1,เลขที่นับได้)
```
ผสมครั้งที่ 1 !
```
= RANDBETWEEN(1,COUNTA(B:B))
```
ได้เลขสุ่ม
จากนั้นใช้ = INDEX (reference,row_num) โดยให้ คอลัมน์ B (หรือ B:B) เป็น reference และค่าที่สุ่มได้เป็น row_num
```
=INDEX(B:B,เลขสุ่ม)
```
ผสมครั้งที่ 2 !!
```
=INDEX(B:B,RANDBETWEEN(1,COUNTA(B:B)))
```
- จากนั้นกด F9 เพื่อ Random ได้เลย
January 1, 2014
สรุปทุกสิ่งที่คุณควรรู้เกี่ยวกับ Pivot Table : ภาค 1 – การสรุปผลข้อมูลขั้นพื้นฐาน
PivotTable คืออะไร?
PivotTable (ฝรั่งอ่านว่า พิ-วอท-เท-เบิ้ล) เป็นเครื่องมือบน Excel ที่สามารถสรุปผลข้อมูลตามเงื่อนไขที่กำหนดได้อย่างง่ายดายและรวดเร็ว เช่น สามารถสรุปได้ว่าข้อมูลแต่ละประเภท มีผลสรุปที่เราสนใจเป็นเท่าไหร่ เช่น ผลรวม/จำนวนนับ/ค่าเฉลี่ย/ค่ามาก/น้อยสุด เป็นต้น
ความสามารถนี้ช่วยให้เราสามารถสรุปผลจากข้อมูลจำนวนมากที่อยู่ในลักษณะตารางฐานข้อมูล มาเป็นข้อมูลสรุปแบบสั้นๆ โดยให้เราสามารถพลิกแพลงมุมมองไปมาเพื่อให้เห็นข้อมูลในมุมมองที่สนใจได้อย่างรวดเร็ว โดยที่เมื่อได้ตารางสรุปตามต้องการแล้วยังสามารถเอาไปสร้างเป็นกราฟต่อได้อีก สะดวกสุดๆ เลยครับ
ตัวอย่างการสรุปผล
จากข้อมูลยุบยับที่ถูกเตรียมในลักษณะ Database เราสามารถนำมาสรุปผลใน PivotTable แบบง่ายๆ เช่นตารางนี้ได้เลย
โหลดไฟล์ตัวอย่างได้ที่นี่ => inwexcel-pivot-practice.xlsx (แก้แล้ว)
หรือสามารถเพิ่มมุมมองของข้อมูลที่เจาะรายละเอียดมากขึ้นได้อย่างง่ายดาย
ลองคิดดูว่าหากคุณต้องเขียนสูตรเพื่อหาว่า Sales ก สามารถขายของเล่น โดยวิธีให้ลูกค้าชำระเงินสด เป็นจำนวนเงินเท่าไหร่? จะยุ่งยากแค่ไหน ในทางกลับกัน หากเราใช้ PivotTable เป็น เราสามารถหาคำตอบนี้ได้ภายเวลาไม่ถึง 1 นาทีด้วยซ้ำ
นี่ไงจากข้อมูลที่ Pivot ออกมาแล้ว ได้คำตอบ คือ 7 ชิ้น 3250 บาทนั่นเอง อยากรู้ของใครอีกบอกมาได้เลย มันสรุปออกมาให้หมดแล้ว!!
PivotTable ไม่ยากอย่างที่คิด
PivotTable มักถูกเข้าใจผิดจากคนทั่วๆไป ว่าเป็นเครื่องมือที่ใช้ยาก ทั้งๆที่จริงแล้ว เป็นเครื่องมือที่ใช้งานง่าย และรวดเร็วกว่าการใช้สูตรเป็นอย่างมาก แต่มีข้อเสียที่ด้อยกว่าการใช้สูตรเล็กน้อยคือ หากข้อมูลที่ต้นทางเปลี่ยนแปลงไป เราต้องกดปุ่ม Refresh ใน PivotTable ก่อน ผลในตาราง Pivot จึงจะ Update ตาม
เมื่อลองชั่งใจข้อดีข้อเสียแล้ว จะพบว่าในสถานการณ์ทั่วไป การใช้ PivotTable สรุปข้อมูลนี่แหละ สะดวกรวดเร็วและง่ายที่สุดแล้ว เอาล่ะ ต่อไปเรามาดูกันว่าจะใช้งาน PivotTable ได้อย่างไร
Step การใช้ Pivot Table
- โหลดไฟล์ตัวอย่างได้ที่นี่ => inwexcel-pivot-practice.xlsx
- ให้คลิ๊กที่ส่วนใดส่วนหนึ่งของตาราง Database ที่เตรียมไว้
  แล้วกด [Insert] –> Tables –> PivotTable
- ตรวจดูก่อนว่ามันคลุมตารางครบหรือไม่ ?
  - ถ้าเตรียมข้อมูล Database ดีในแบบที่ผมแนะนำไปมันก็จะครบอยู่แล้ว
  - หากมีการใช้ Tableเป็นSource Dataมันจะขึ้นชื่อTableมาให้เลย ซึ่งไม่ต้องมานั่งตรวจให้เสียเวลาอีกเช่นกัน
- เลือกได้ว่าจะให้ข้อมูลที่ Pivot แล้วไปสร้างเป็น Sheet ใหม่ หรือไว้ใน Sheet ที่มี
Field List
เวลาสร้างตาราง Pivot ขึ้นมาแล้ว หัวตารางของข้อมูลต้นฉบับของเรา จะกลายเป็น Field List ของ PivotTable ซึ่งจะวางอยู่ด้านขวาบน เปรียบเหมือน Item ที่ให้เราหยิบไปใช้ทำอะไรได้มากมาย
เราสามารถลาก Field List แต่ละอันลงไปใน Slot ทั้งสี่อัน ที่อยู่บริเวณข้างล่างขวามือ แต่ละอันมีความหมายดังนี้
- Report Filterทำหน้าที่คัดกรองข้อมูล ให้แสดงเฉพาะข้อมูลบรรทัดเดียวกับที่ Filter ไว้
- Column Labelนำข้อมูลมาไว้ที่หัวตาราง Pivot (ด้านบน) เพื่อจัดกลุ่มแบบคอลัมน์ โดยมันจะแสดง item ทั้งหมดที่ไม่ซ้ำกันใน Field ที่คุณเลือกมาไว้ที่หัวตาราง Pivot (ด้านบน)
- Row Labelนำข้อมูลมาไว้ที่ด้านซ้ายของตาราง Pivot เพื่อจัดกลุ่มแบบแถว โดยมันจะแสดง item ทั้งหมดที่ไม่ซ้ำกันใน Field ที่คุณเลือกมาไว้ที่ด้านซ้ายของตาราง Pivot
- Valuesเป็นการคำนวณสรุปผล ทำได้หลายรูปแบบ เช่น SUM, Count, Average, Max, Min โดยอาจแสดงได้หลายรูปแบบ เช่น รูปแบบปกติ, %ของทั้งหมด, %ของแถว,%ของคอลัมน์, การรวมแบบสะสมค่า เป็นต้น
โดยField List 1 อัน สามารถลากลงไปใช้ในช่อง Values ซ้ำได้มากกว่า 1 ครั้ง เช่น อันแรกเราเอาไปหาค่า SUM อันที่สองเราสามารถเอา Field เดิมไปหาค่า MAX หรือจะเปลี่ยนรูปแบบการแสดงผลให้ต่างกันได้ เป็นต้น
ลองลาก Field List หลายๆ แบบ
ลาก Field จำนวนเงินที่จ่าย มาที่ช่อง Value 1 อัน : มันจะทำการคำนวณสรุปผลข้อมูลให้ แบบนี้คือเหมือนการ SUM ข้อมูลทั้งหมดแบบไม่มีเงื่อนไข หรือการแยกประเภทใดๆ ทั้งสิ้น
หากลองเปลี่ยนเอา Field จำนวนเงินที่จ่าย มาไว้ที่ Row Label แทน : มันจะแสดง item ทั้งหมดที่ไม่ซ้ำกันซึ่งจะออกมาเยอะมากๆ ปกติแล้วคุณไม่น่าจะต้องการผลลัพธ์แบบนี้ครับ ยกเว้นว่าจะทำการ Grouping ข้อมูลตามช่วงยอดขาย ซึ่งผมจะพูดถึงเรื่อง Grouping ข้อมูลในบทถัดไปครับ
ลาก Field ผู้ซื้อ มาที่ Row Label : มันจะแสดง item ทั้งหมดที่ไม่ซ้ำกัน
ลองลาก Field จำนวน มาที่ช่อง Value เพิ่มอีก 1 อัน :
ลองลาก Field จำนวน มาที่ช่อง Value ซ้ำเป็นอันที่ 2 ก็ยังได้ : ในบทต่อไป ผมจะสอนวิธีเปลี่ยนจาก SUM เป็นการสรุปอย่างอื่นได้อีกครับ เช่น Count, Average
ลองย้าย ∑ Values : จาก Column Labels มาที่ Row Label
ลากอีก Field สินค้า มาไว้ที่ Report Filter : แล้วลองเลือก Dropdown คัดกรองดู
ลากอีก Field ผู้ขาย มาที่ Column Label : มันจะแสดง item ทั้งหมดที่ไม่ซ้ำกัน
ลากอีก Field ผู้ขาย มาที่ RowLabel ให้ซ้อนกับผู้ซื้อ : มันจะแสดง item ทั้งหมดที่ไม่ซ้ำกัน
ลองย้าย ∑ Values : ไปยังหลายๆ ตำแหน่ง เช่น สลับลำดับใน Row หรือ ย้ายกลับไป Column
วิธีการแปลความหมายตาราง Pivot Table
ตัวอย่าง ผมลองลาก Field ต่างๆ มาลง 4 Slot ข้างล่างตามรูป
วิธีตีความคือให้ดูว่า ข้อมูลที่กำลังสรุปผลเป็นการ Summary จาก Field ไหนบ้าง
โดยต้องดูว่าข้อมูลมัน Cross กันแบบไหน ทั้ง 4 slot เลย
ตัวอย่าง ในช่อง C12 (1000) หมายถึง
นาย c (row) / ซื้อสินค้าใดๆก็ตาม (เลือก All แปลว่า ไม่ได้ filter เจาะจงสินค้า) / กับ sales ค (column) / ด้วยเครดิตการ์ด (row) / เป็นจำนวนรวม 1000 บาท (Values – สรุปด้วย SUM)
เห็นมั๊ยครับว่า PivotTable นี่สามารถอ่านผลลัพธ์ได้ง่ายและรวดเร็วจริงๆ!!
ใครอยากรู้ว่า Pivot Table ทำอะไรได้อีก อย่าลืมอ่านต่อได้ที่ตอนที่ 2 ครับ
Video ประจำตอน
มี 3 ไฟล์ด้วยกัน ตั้งใจดูให้ดีล่ะครับ ยาวหน่อย แต่อัดแน่นด้วยเนื้อหาครับ
สรุปทุกสิ่งที่คุณควรรู้เกี่ยวกับ Pivot Table บน Excel ตอนที่ 1/3 :
http://www.youtube.com/watch?v=_KB4LAMQbWU

สรุปทุกสิ่งที่คุณควรรู้เกี่ยวกับ Pivot Table บน Excel ตอนที่ 2/3 :
http://www.youtube.com/watch?v=rrUerQbuAJ8

สรุปทุกสิ่งที่คุณควรรู้เกี่ยวกับ Pivot Table บน Excel ตอนที่ 3/3 :
http://www.youtube.com/watch?v=2VJ7eTKmzK4
December 30, 2013
เลือกอาวุธใน Excel ให้เหมาะกับคุณด้วย Quick Access Toobar
เราอาจใช้ Excel เพื่อจุดประสงค์ที่แตกต่างกัน

การใช้งาน Excel ของแต่ละคนนั้นย่อมไม่เหมือนกันและมีความแตกต่างกันไป ยกตัวอย่างเช่น
- เน้นสูตรคำนวณซับซ้อน
- จัดทำบัญชีรายรับ-รายจ่าย
- เก็บข้อมูลคนที่ต้องร่วมงานด้วยบ่อยๆ
- เพื่อวิเคราะห์ข้อมูล หาความสัมพันธ์บางอย่าง
- อาจเน้นทำกราฟ หรือทำแผนภูมิ
- บางคนใช้ Excel ในการวาดรูปก็มี!
ดังนั้นการที่เราทำงานแต่ละแบบนั้น ย่่อมมีเครื่องมือที่ต้องใช้แตกต่างกันไป การที่เราเลือกเครื่องมือที่เราใช้งานบ่อยๆ มาให้อยู่ในที่ที่เข้าถึงง่าย กดใช้งานง่าย มันย่อมทำให้ราทำงานได้เร็วขึ้นอีกมาก วันนี้ผมเลยจะมาแนะนำให้เพื่อนๆใช้เครื่องมือ Quick Access Toolbar (QAT) ที่สามารถทำให้เราทำงานได้เร็วขึ้นอีกมากมาย

Quick Access Toolbar อยู่ตรงไหน ใช้งานยังไง?

อยู่ตำแหน่งซ้ายบนของหน้าจอ วิธีใช้มี 2 แบบ คือ
1. ใช้เม้าคลิ๊กปุ่มต่างๆที่มีให้เลือก
2. หรือจะกด Keyboard ปุ่ม ALT+ ตัวเลข 1, 2, 3, 4…เอาก็ได้ (แล้วแต่ว่าเรียงลำดับเครื่องมือไว้แบบไหน)
จะเห็นว่าเราเข้าถึงคำสั่งพวกนี้ได้เร็วกว่าปกติมาก เพราะกดทีเดียวก็จะใช้งานได้เลย ไม่ต้องกดเข้าไปในแถบต่างๆ เหมือนปกติ ดังนั้นคำสั่งไหนที่เราใช้บ่อยๆ เราสามารถเอามาเพิ่มลงใน QAT นี้ได้ด้วย (more…)
December 8, 2013

แนะนำฟังก์ชั่น DATEDIF

DATEDIF ไว้ใช้ทำอะไร?

DATEDIF เป็นฟังก์ชั่นเกี่ยวกับวันที่ ใช้หาว่าวันสองวันที่กำหนด ห่างกันกี่วัน กี่เดือน หรือ กี่ปี (เราเลือกได้)

วิธีการใช้ฟังก์ชั่น

DATEDIF( start_date, end_date, interval )

DATEDIF( วันเริ่ม, วันจบ, รูปแบบการวัดช่วงเวลา )
โดย interval (รูปแบบการวัดช่วงเวลา) คือ

Interval	Explanation
Y	ปี (แบบครบปี)
M	เดือน (แบบครบเดือน)
D	วัน
MD	วัน (ไม่สนใจ เดือน ปี)
YM	เดือน (ไม่สนใจวัน ปี)
YD	วัน (ไม่สนใจปี)

อย่างไรก็ตาม ฟังก์ชันนี้มีความแปลกและพฤติกรรมประหลาด ให้ไปอ่านรายละเอียดได้ในบทความใหม่ของผมอันนี้ครับ

อธิบายการทำงาน DATEDIF ใน Excel และแนวทางแก้ไขให้ได้ผลลัพธ์ดั่งใจ

November 24, 2013

การทำงานเกี่ยวกับวันและเวลา (Date & Time) ใน Excel
เรื่องของวันและเวลา

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

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

รูปแบบวันที่ ที่ Excel รู้จัก

กรณีที่ตั้งค่า Regional Setting ใน Control Panel เป็น Thai จะทำให้กรอกเป็น วัน/เดือน/ปี ได้แบบนี้ (ถ้าตั้งค่าเป็นอย่างอื่น อาจต้องกรอกเป็น เดือน/วัน/ปี ซึ่งอาจทำให้เรากรอกผิด เพราะคนไทยไม่คุ้นกับการกรอกแบบนี้)

Excel มองวันและเวลาเป็นตัวเลขธรรมดาๆ

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

วันที่คือจำนวนเต็ม

Excel จะแทนวันที่ 1/1/1900 (1 มกราคม คศ. 1900) ด้วยเลข 1
แล้วมันก็แทนวันที่ 2/1/1900 ( 2 มกราคม คศ. 1900) ด้วยเลข 2..

ทำอย่างนี้ไปเรื่อยๆ (วันเพิ่ม 1 วัน เลขเพิ่ม 1 หน่วย)

จนถึงวันในยุคปัจจุบัน จะได้ตัวเลขประมาณ 4 หมื่นกว่าๆ

เวลา คือ ทศนิยม

ทำนองเดียวกัน ถ้าเลขนั้นลงละเอียดถึงระดับของเวลา การแทนเลขจะแทนด้วยทศนิยมแทน
ถ้าหากเวลาผ่านไป 1 วัน เลขจะเพิ่มขึ้น 1 หน่วย
เมื่อเวลาผ่านไป ครึ่งวัน (เช่น ตอนเที่ยงตรง) เลขก็จะเพิ่มขึ้น 0.5 หน่วย นั่นเอง

ดังนั้น หากเราเห็น เลข 1.5 มันคือวันที่ 1/1/1900 เวลา 12:00:00

พอเริ่มเห็นภาพแล้วใช่มั๊ยครับ เรามาลุยต่อกันเลยครับ

อยากรู้ว่าวันที่/เวลา มีค่าที่แท้จริงคือเลขอะไร?

ให้ลองเปลี่ยน Format จาก Date => General หรือ Number ดูก็จะรู้ครับ

การคำนวณเกี่ยวกับวันที่

เมื่อเรารู้อย่างนี้แล้ว หากเราเอาวันที่ 2 ช่องลบกัน เราก็จะรู้ระยะห่างของวันทั้งสองได้เลย โดยที่หน่วยออกมาเป็น วัน เช่น

การคำนวณเวลา และการแปลงหน่วย

สมมติเรามีข้อมูลว่า…

เริ่มทำงาน ด้วยวันที่/เวลาในช่อง B1 เสร็จงาน ด้วยวันที่/เวลาในช่อง B2

อยากรู้ว่า ใช้เวลาทั้งหมดกี่นาที ในช่อง B3 ยังไง?

เมื่อ Excel แทนวันที่ด้วยจำนวนเต็ม ดังนั้น เวลา (ซึ่งคือส่วนหนึ่งของวัน) ก็จะถูกแทนด้วยเลขทศนิยมนั่นเอง เช่น
- วันที่ 16/10/2016 เวลา 0:00 คือเลข 42659
ทีนี้ถ้าเรากรอกเป็น 16/10/2016 13:25:00 แล้วลองเปลี่ยนเป็น Number จะได้เป็นเลข 42659.5590277778 (ลองเพิ่มทศนิยมดู) ซึ่งจริงๆ แล้วมันก็คือการแปลงวัน+เวลา ชั่วโมงและนาที ให้มีหน่วยเป็น “วัน” นั่นเอง

ดังนั้นถ้าเรามีเวลาเริ่มและจบแล้ว เราจะหาเวลาที่ผ่านไปได้ง่ายมากๆ โดยเอาค่าทั้งสองมาลบกัน

เช่น เอา B2-B1 จะได้ 1.784722222 แบบนี้ ซึ่งจะมีหน่วยเป็นวัน

ถ้าหากโจทย์ต้องการคำตอบที่หน่วยเป็นนาที เราก็แค่ต้องแปลงหน่วยจากวันเป็นนาที ซึ่งง่ายมากๆ

แค่เพียงเรารู้หลักการของการแปลงหน่วยเท่านั้น นั่นคือ การทำให้หน่วยตัดกันจนให้ได้ผลลัพธ์เป็นหน่วยสุดท้ายที่ต้องการ โดยการแทนเลข *1 ด้วยอัตราส่วนที่เป็นความจริง เช่น
- 1 วัน = 24 ชม. หรือ 1 = 24 ชม./วัน
- 1 ชม. = 60 นาที หรือ 1 = 60 นาที/ชม.
ดังนั้น (B2-B1) วัน คือ
- = (B2-B1) วัน * 1 * 1 (คูณ 1 ค่าไม่เปลี่ยน ถือว่าทำแล้วค่ายังคงถูกต้อง)
- = (B2-B1) วัน * 24 ชม./วัน * 60 นาที/ชม.
  โดยที่จะเห็นว่าหน่วยสามารถตัดกันได้ จนเหลือแค่หน่วยนาที
- สุดท้ายจะได้ว่า เวลาที่ผ่านไปเป็นหน่วยนาที คำนวณได้จาก
- = (B2-B1)*24*60 นาที
เห็นมั้ยครับ? การคำนวณเรื่องวันที่และเวลาไม่ได้ยากอย่างที่คิดเลย แค่ต้องเข้าใจ Concept ของมันเท่านั้นเอง

ใครสนใจเรื่องวันที่และเวลา อย่าลืมอ่านต่อในบทความเรื่อง ฟังก์ชันเกี่ยวกับวันที่และเวลา นะครับ
November 24, 2013
เจาะลึกฟังก์ชั่น OFFSET
ฟังก์ชั่น OFFSET เอาไว้แสดงผลลัพธ์เป็นการอ้างอิงช่อง/ช่วง (ผลเป็น Reference) โดยใช้วิธีเลื่อนตำแหน่งจากช่องที่เราอ้างอิงไปในทิศทางต่างๆ เช่น เราสามารถอ้างอิงให้เริ่มที่ช่อง G3 แล้วให้เลื่อนลงล่าง 2 แถว ไปทางซ้ายอีก 4 แถว ซึ่งจะได้ผลลัพธ์เป็นค่าในช่อง C5 เป็นต้น
พูดง่ายๆ ว่า OFFSET มีความสามารถในการทำ Dynamic Range นั่นเองครับ
วิธีการใช้งาน
```
=OFFSET(reference,rows,cols,[height],[width])
```
วิธีการใช้งาน ภาษาชาวบ้าน
= OFFSET(จุดอ้างอิง, เลื่อนลงล่างกี่แถว, เลื่อนขวากี่แถว, [ความสูงของ Range ผลลัพธ์], [ความกว้างของ Range ผลลัพธ์])
ประเภทของ Argument ที่ใส่ลงไป
= OFFSET(Cell/Range Reference, ตัวเลข, ตัวเลข, ตัวเลข, ตัวเลข)
ประเภทของผลลัพธ์ที่ได้จาก Function
ได้เป็น Cell/Range Reference
ประเด็นที่น่าสนใจ
- จุดอ้างอิงจะเป็น Cell หรือ Range ก็ได้
- Rows ถ้าใส่เลขบวกจะเลื่อนลง ถ้าใส่ติดลบจะเลื่อนขึ้น
- Columns ถ้าใส่เลขบวกจะเลื่อนไปขวา ถ้าใส่ติดลบจะเลื่อนไปทางซ้าย
- ถ้าไม่ใส่ Height หรือ width จะถือว่าให้สูงหรือกว้างเท่าช่องอ้างอิงต้นฉบับ
- Height และ Width ติดลบไม่ได้ เป็น 0 ไม่ได้
เช่น
- =OFFSET(C3,2,3,1,1)
  - จะส่งผลกลับมาเป็นการอ้างอิงช่อง F5 เพราะเลื่อนจาก C3 ไปล่าง 2 ช่อง ขวา 3 ช่อง
- =SUM(OFFSET(C3:E5,-1,0,3,3))
  - จะทำการ Sum ช่วง C2:E4 เพราะเลื่อนจาก C3:E5 ขึ้นไป 1 ช่อง
- =OFFSET(C3:E5,0,-3,3,3)
  - จะ Error (#REF!) เพราะการอ้างอิงหลุดกรอบการทำงานของ Worksheet (เลื่อน C ไปซ้าย 3 ช่อง จะเลย A ออกไป)
ตัวอย่างการประยุกต์ใช้ OFFSET เพื่อสร้าง Dependent Dropdown List
November 19, 2013
เจาะลึกสูตร VLOOKUP ดูจบใช้เป็นเลย
VLOOKUP (Vertical Lookup) เป็นฟังก์ชั่นใน Excel ที่จัดอยู่ในประเภทการค้นหาและอ้างอิงข้อมูล เช่น เอาไว้หาว่ารหัสสินค้าที่กำลังค้นหาคือสินค้าชื่ออะไร (ดูจากตารางอ้างอิงที่เรามีอยู่) โดยถือว่าเป็นฟังก์ชั่นที่ผู้หัดใช้ Excel แรกๆ อาจรู้สึกว่ามันใช้ยาก ซึ่งก็จริง แต่ถ้าหากว่าเราใช้ฟังก์ชั่นนี้เป็นแล้ว เราจะใช้งาน Excel ทำอะไรได้อีกมากเลยทีเดียว ดังนั้นเรามาหัดใช้กันให้คล่องเถอะครับ

วิธีการใช้งาน
การใช้ VLOOKUP นั้นจะมีสิ่งที่ต้องกรอกหลายช่องหน่อยนะครับ อย่าเพิ่งตกใจ ลองค่อยๆอ่านตามไปเรื่อยๆ นะครับ จะมีตัวอย่างให้ดูที่ทำให้เห็นภาพมากขึ้นแน่นอน
```
= VLOOKUP(lookup_value, table_array, col_index_num,[range_lookup])
```
=VLOOKUP(ค้นหาคำนี้,จากคอลัมน์แรกในตารางนี้,เมื่อเจอแล้วให้เอาค่าในคอลัมน์ที่ xx ของตารางกลับมา (นับซ้ายไปขวา), [ใช้โหมด Lookup แบบ Approximate Match หรือ Exact Match])
VLOOKUP มี 2 โหมดด้วยกัน
1. Approximate Match (ตั้งค่า Range Lookup เป็น TRUE หรือ 1 หรือ เว้นไว้ไม่กรอก) แปลว่า แม้ไม่เจอค่าที่ต้องการหาเป๊ะๆ ก็ยังส่งค่าบางอย่างกลับมาได้ (แล้วมันส่งค่าอะไรกลับมาเดี๋ยวจะอธิบายอีกที)
2. Exact Match (ตั้งค่า Range Lookup เป็น FALSE หรือ 0) แปลว่า ต้องเจอค่า/คำที่ต้องการเป๊ะๆ จึงจะส่งค่ากลับมา
ซึ่งในบทความนี้ผมจะขออธิบายจากตัวที่ง่ายกว่าก่อน นั่นก็คือ VLOOKUP โหมด Exact Match ครับ
VLOOKUP โหมด Exact Match
จุดประสงค์หลัก : เหมาะกับการ Map ข้อมูล (ดึงค่าจาก Reference ที่กำหนด)
มันจะทำงานดังนี้
1. หาคำที่ต้องการจากคอลัมน์ซ้ายสุดของตารางที่กำหนด (Table Array) และทำการหาจากบนลงล่าง (Vertical Lookup)
2. ผลมี 2 กรณี
  1. ถ้าเจอคำที่ต้องการ(ต้องเหมือนเป๊ะๆ) มันจะหยุดที่บรรทัดนั้น
    ถ้ามีค่าที่ตรงเงื่อนไขหลายค่าในคอลัมน์ที่ค้นหา มันจะยึดที่เจออันแรกสุด (เฉพาะแบบ Exact Match นะ)
  2. ไม่เจอคำที่ต้องการเป๊ะๆ จะแสดงค่า Error กลับมาเป็น #N/A (ย่อมาจาก Not Available แปลว่าหาไม่เจอ)
3. กรณีเจอคำที่ต้องการเป๊ะๆ จะวิ่งไปทางขวา (ในบรรทัดเดียวกับตัวที่เจอ) ตาม Col Index ที่กำหนด เริ่มนับที่คอลัมน์ซ้ายสุดของ Table_Array เป็น 1, ถัดไปทางขวา =2, 3, 4… เพื่อเอาค่าใน Col Index ที่กำหนดกลับมาแสดงค่า
  - ตัวอย่างในรูป ช่อง G3 เกิดจากการใช้ VLOOKUP ค้นหาทะเบียน นม6666 ว่าอยู่บรรทัดไหน ถ้าเจอให้เอาคอลัมน์ที่ 2 ของบรรทัดนั้นกลับมา (นับเฉพาะขอบเขตตารางที่เลือกไว้ ไม่เกี่ยวว่าคอลัมน์ที่ 2 คือ B) ซึ่งจะได้ค่า BMW
ทำไมใช้ VLOOKUP แล้วขึ้น #N/A หาไม่เจอ (ทั้งๆ ที่น่าจะเจอ)?
คุณอาจเคยเจอปัญหา ว่าเห็น lookup_value ในตารางอ้างอิงอยู่เต็มๆ ตา ทำไม VLOOKUP แล้วกลับขึ้นมาว่าเป็น #N/A ซึ่งอาการที่พบบ่อยมีอยู่ 3 แบบ ดังนี้
ข้อมูลไม่ตรงกันจริงๆ เช่น ติดช่องว่างมา
บางครั้งสิ่งที่เราเห็นว่าตรงกัน อาจะไม่ตรงกันจริงๆ ก็ได้ เช่นตัวนึงเป็น AAA อีกอันเป็น AAA mี่มีช่องว่างอยู่ด้านหลัง (เรามองด้วยตาเปล่าไม่เห็นหรอก) วิธีแก้คือใช้ฟังก์ชัน TRIM มาครอบเพื่อตัดช่องว่างส่วนเกินออกซะ จะได้ใช้ VLOOKUP ได้อย่างไม่มีปัญหา
Data Type ไม่ตรงกัน
อาการนี้พบบ่อยที่สุด นั่นคือ ค่า lookup_value ที่เราระบุไว้ในฟังก์ชั่น VLOOKUP กับค่าที่อยู่ในตารางอ้างอิง ( table_array) เป็นข้อมูลคนละประเภทกันนั่นเอง ที่เจอบ่อยๆ คือ ใน Lookup_value เป็นตัวเลข ส่วนตารางอ้างอิงเป็น Text ที่มีหน้าตาเป็นตัวเลข ซึ่งมองด้วยตาเปล่าอาจมองไม่ออก ต้องใช้ฟังก์ชั่น TYPE มาช่วยตรวจถึงรู้ว่าเป็นข้อมูลประเภทไหนกันแน่ เช่น
=VLOOKUP(30,ตารางอ้างอิง,2,0) จะขึ้น #N/A หากว่าในตารางอ้างอิงเป็นข้อมูลประเภท Text
ซึ่งทางแก้ คือ เราก็ต้องทำให้ Lookup_value กับตารางอ้างอิงเป็นข้อมูลประเภทเดียวกัน เช่น
- ทำให้ Lookup_value เป็น Text ไปด้วย เช่น =VLOOKUP(“30”,ตารางอ้างอิง,2,0) หรือ
- จะ ใช้ cell reference ที่มีข้อมูลเป็น Text เลข 30 ก็ได้ เช่น ถ้าเอา Text เลข 30 ไปไว้ใน B8 ถ้าเขียน =VLOOKUP(B8,ตารางอ้างอิง,2,0) ก็จะเจอ เช่นกัน หรือ
- ทำให้ตารางอ้างอิงเป็นเลขไปเลย ซึ่งทำได้หลายวิธี เช่น ใช้วิธีเอาเลข 1 ไปคูณให้หมด
  คือ copy เลข 1 –> เลือกตารางอ้างอิงคอลัมน์แรก –> Paste Special –> Multiply
Cell Reference เลื่อน
อาการนี้มักเกิดขึ้นเวลาจะ Lookup ข้อมูลแล้วมีการ Copy สูตรลงมาข้างล่างยาวๆ ไม่ก็ Copy เปลี่ยนคอลัมน์ไปทางแนวนอน แล้วลืมกด Lock ตารางอ้างอิงไว้ ทำให้ตัวที่ถูก Copy ท้ายๆ เลื่อนการอ้างอิงตารางไปผิดตำแหน่ง ทำให้หาข้อมูลไม่เจอ เป็นต้น
ทางแก้ คือ เราควรทำการ ตรึงตำแหน่งสูตร Cell Reference ในส่วนของ ตาราง Table__array ทุกครั้ง โดยการกดปุ่ม F4 ให้มีเครื่องหมาย $ ขึ้นมาคลุม
หรือไม่ก็ใช้วิธีตั้งชื่อ Defined Name ก็ได้เช่นกัน อันนั้นมันจะ ตรึงตำแหน่งสูตร Cell Reference ด้วยการใช้ชื่อเอง เราไม่ต้องมานั่ง Lock อีกครั้ง สะดวกมาก
แล้วยังไงต่อ?
และนี่ก็จบแล้วสำหรับการใช้ VLOOKUP พื้นฐานแบบ Exact Match ถ้าใครอยากศึกษา VLOOKUP โหมด Approximate Match ต่อไป สามารถคลิ๊กอ่านได้ที่นี่
November 17, 2013
เจาะลึกสูตร IF ใน Excel
สูตร IF คืออะไร?
สูตร IF (หรือจะเรียกให้ถูกคือฟังก์ชัน IF) คือ ฟังก์ชั่น (Function) ซึ่งทำหน้าที่เลือกใช้สูตรคำนวณได้ 2 แบบ ขึ้นอยู่กับเงื่อนไขที่เราระบุลงไปว่าจริงหรือเท็จ?
ถ้าเงื่อนไขเป็นจริง (TRUE) จะทำการคำนวณด้วยสูตรแบบหนึ่ง ถ้าเงื่อนไขเป็นเท็จ (FALSE) จะคำนวณด้วยสูตรอีกแบบหนึ่ง
** ผมใช้คำว่า “สูตร” เพราะใส่สูตรยาวๆ ซับซ้อนแค่ไหนก็ได้
ผมแนะนำให้มองว่า IF 1 ตัว สามารถแตกกิ่งก้านสาขาการตัดสินใจ (Decision Tree) ออกไปได้ 2 กิ่ง
คือ ผลลัพธ์จากกรณีเงื่อนไขเป็น TRUE และ ผลลัพธ์จากกรณีเงื่อนไขเป็น FALSE
สูตร IF เป็นฟังก์ชั่นพื้นฐานที่ใช้บ่อยมากถึงมากที่สุดอันหนึ่งของ Excel เลยทีเดียว ดังนั้นทุกคนที่ต้องการจะเก่ง Excel จำเป็นอย่างยิ่งที่จะต้องใช้สูตรนี้ให้เป็นนะครับ
ปล. ผมมีเขียนบทความใหม่เกี่ยวกับ IF รวมถึงตัวที่ใกล้เคียงกับมัน เช่น IFS และ SWITCH เอาไว้ด้วย สามารถไปอ่านได้ที่นี่
วิธีเขียนสูตร
```
=IF(logical_test,[value_if_true],[value_if_false])
=IF(เงื่อนไขที่เราโยนเข้าไปให้ทดสอบ, [ถ้าจริงจะทำอันนี้], [ถ้าเท็จจะทำอันนี้])
```
เช่น หากเทียบกับภาษาพูด จะได้ว่า
- ถ้า ฉันสอบตก ฉันจะเลี้ยงข้าว ไม่งั้น เธอเป็นคนเลี้ยงข้าว
  - ฉันสอบตก = เงื่อนไข
  - ฉันเลี้ยงข้าว = การกระทำหากเงื่อนไขเป็นจริง
  - เธอเป็นคนเลี้ยงข้าว = การกระทำหากเงื่อนไขเป็นเท็จ
ซึ่งเงื่อนไขที่เราโยนเข้าไปให้ทดสอบ นั้นจะต้องมี ตัวเปรียบเทียบ (COMPARISON OPERATOR) อยู่ด้วย เพื่อให้ค่าออกมาเป็นจริง (TRUE) หรือเท็จ (FALSE) เช่น = เท่ากับ, < น้อยกว่า, > มากกว่า, <> ไม่เท่ากับ, >= มากกว่าหรือเท่ากับ, <= น้อยกว่าหรือเท่ากับ
ตัวอย่าง 1
หากช่อง B4 เราเขียนว่า
```
=IF(B2>B3,"ของไม่พอ","ของพอ")
```
สิ่งที่มันจะทำคือดูว่าค่าใน B2 มากกว่า B3 หรือไม่?
ซึ่งในที่นี้มากกว่า แปลว่า Logical Test (เงื่อนไข) ได้ผลลัพธ์เป็น TRUE
ซึ่งเปรียบได้กับ =IF(TRUE,”ของไม่พอ”,”ของพอ”)
และเมื่อ Logical Test เป็น TRUE ก็จะแสดงผลลัพธ์จาก value_if_true ซึ่งก็คือ “ของไม่พอ” นั่นเองครับ
หากมีหลายเงื่อนไขจะทำอย่างไร?
โดยหากมีหลายเงื่อนไข เราสามารถเอาแต่ละเงื่อนไขมาเชื่อมกับฟังก์ชั่นทางตรรกศาสตร์ ได้อีก เช่น
- AND(เงื่อนไข1,เงื่อนไข2,เงื่อนไข3,…) => และ : เงื่อนไขต้องเป็นจริงทุกอัน ผลลัพธ์ถึงจะออกมาเป็นจริง
- OR(เงื่อนไข1,เงื่อนไข2,เงื่อนไข3,…) => หรือ : หากเงื่อนไขอันใดอันหนึ่งจริงถือว่าผลลัพธ์จริง
ตัวอย่าง 2
การเขียน IF ซ้อน IF
เราสามารถเขียน IF ซ้อนกันไปเรื่อยๆ ได้ (จริงๆแล้วจะเอาฟังก์ชั่นอื่นมาซ้อนด้วยก็ยังได้)
โดย IF แต่ละตัวก็จะมีการเช็คเงื่อนไขของตัวเอง และทำค่าจริง/เท็จ ของตัวเองแล้วแต่ผลลัพธ์ที่ประมวลได้ เช่น
```
=IF(เงื่อนไข 1, IF( เงื่อนไข2,จริง2,เท็จ2),IF(เงื่อนไข3,จริง3,เท็จ3))
```
เทคนิคการเขียน IF ซ้อนกันหลายตัว
- จำนวนวงเล็บเปิดต้องเท่ากับวงเล็บปิด (ในที่นี้มีอย่างละ 4 อันเท่ากัน)
- ในแต่ละกิ่งที่ จริง /เท็จ เราสามารถเลือกที่จะใส่ IF ซ้อนลงไปอีกหรือไม่ก็ได้ ลองดูได้จากตัวอย่างที่จะเห็นต่อไป
ตัวอย่าง 3
หากเราต้องการจะจัดเกรดจากคะแนนดิบของนักเรียน โดยมีเกณฑ์ดังนี้
- คะแนน <50 : F
- 50 <= คะแนน<60 : D
- 60 <= คะแนน<70 : C
- 70 <= คะแนน<80 : B
- คะแนน >= 80 : A
สมมติคะแนนดิบอยู่ในช่อง A2 และเราจะใส่เกรดในช่อง B2 / ในช่อง B2 เราต้องเขียนดังนี้
```
=IF(A2<50,"F",IF(A2<60,"D",IF(A2<70,"C",IF(A2<80,"B","A"))))
```
อธิบายแนวคิด
ให้ใส่เงื่อนไขทีละ Step อันแรกเช็คว่าน้อยกว่า 50 หรือไม่? ถ้าน้อยกว่าให้แสดงเกรด F ถ้าไม่น้อยกว่า ต้องเช็คต่อว่า < 60 หรือไม่?… ทำแบบนี้ไปเรื่อยๆ
สรุปแบบ Slide

Excel 9 หน้า : IF ถ้ารู้งี้ ก็เก่งไปนานแล้ว! from Sira Ekabut

หวังว่าเพื่อนๆ อ่านแล้วจะเข้าใจมากขึ้นนะครับใครอ่านแล้วสงสัยตรงไหน หรือมีข้อแนะนำอะไร อย่าลืม Comment ในนี้ หรือมาพูดคุยกันได้ใน Facebook นะครับ
November 10, 2013
การแปลงค่าจาก ตัวหนังสือ ตัวเลข
ทำไมต้องแปลงค่า?

หากเราใช้ข้อมูลที่มีรูปแบบไม่ตรงตามที่ต้องการ อาจเปิดปัญหาในการใช้สูตร เช่น Lookup ค่าไม่เจอ ก็เป็นไปได้ครับ

วิธีแปลงค่า จากตัวหนังสือ => ตัวเลข ใน Excel

วิธีที่ง่าย และได้ผลที่สุด คือ เอาไปคูณ 1 ครับ เช่น
- ในช่อง A1 มีตัวหนังสือว่า 000540
- หากจะแปลงไปเป็นตัวเลข และแสดงผลในช่อง B1
- ในช่อง B1 ให้เขียนว่า =A1*1
- ซึ่งจะได้ค่าเป็น 540 แบบเป็น Number นั่นเอง
วิธีแปลงค่า จากตัวเลข => ตัวหนังสือ ใน Excel

วิธีที่ง่าย และได้ผลที่สุด คือ เอาไป & ช่องว่าง ครับ เช่น
- ในช่อง A1 มีตัวเลข 540
- หากจะแปลงไปเป็นตัวหนังสือ และแสดงผลในช่อง B1
- ในช่อง B1 ให้เขียนว่า =A1&””
- ซึ่งจะได้ค่าเป็น 540 แต่เป็น Text นั่นเอง
วิธีตรวจสอบประเภทของข้อมูลใน Excel

อ่านได้ที่ http://www.thepexcel.com/check-data-type/
November 10, 2013
การขึ้นบรรทัดใหม่ใน Cell เดิม

หากคุณใช้โปรแกรม Excel แล้วจะพิมพ์ข้อความให้ขึ้นบรรทัดใหม่ แต่อยู่ในช่องเดิม คุณไม่สามารถกดปุ่ม Enter เฉยๆ เหมือนกับโปรแกรมอื่นๆ ทั่วไปอย่าง Microsoft Word ได้ (หาดกด Enter ใน Excel มันจะเด้งลงไป Cell ถัดไปที่อยู่ข้างล่างแทน !!)

ใน Microsoft Excel การจะบังคับขึ้นบรรทัดใหม่ได้นั้น ต้องกดปุ่ม Alt+Enter เท่านั้นครับ (กด Alt ค้างไว้ แล้วค่อยกด Enter) เมื่อต้องการจะขึ้นบรรทัดใหม่ตรงไหนให้กดตรงนั้นครับ

November 10, 2013

ประเภทข้อมูล (Data Type) ในโปรแกรม Excel

ประเภทข้อมูล 4 แบบ

ใน Excel ไม่ว่าเราจะเขียนสูตร หรือ พิมพ์ข้อมูลลงไปใน Cell …. ผลลัพธ์ค่าที่แท้จริง (Value) สามารถแบ่งออกเป็น 4 ประเภทข้อมูลใหญ่ๆ ด้วยกัน คือ

Number ตัวเลข สามารถเอามาคำนวณทางคณิตศาสตร์ได้
- ตัวเลขปกติทั่วไป 10, 2.3, 1/2, 1.234E+03
- ตัวที่อาจไม่เหมือนตัวเลข แต่จริงๆเป็นตัวเลขอย่าง เช่น วันที่และเวลา คือ ตัวเลขที่เปลี่ยน Format ไป
  - วันที่ เช่น 31 Jan 2013
    - Excel จะมองวันที่ เป็นจำนวนเต็ม เช่น
    - เลข 1 คือ วันที่ 1 เดือน 1 ปี คศ. 1900
    - เลข 2 คือ วันที่ 2 เดือน 1 ปี คศ. 1900
  - เวลา เช่น 16:30
    - Excel จะมองเวลา เป็นจุดทศนิยม โดย เที่ยงวันคือ 0.5 เป็นต้น
  - รายละเอียด อ่านได้ที่ การทำงานเกี่ยวกับวันและเวลา (Date & Time) ใน Excel
- ธรรมชาติจะอยู่ ชิดขวาของ Cell โดยอัตโนมัติ
Text ตัวอักษร เอาไว้แสดงผลข้อความ ไม่ได้เอาไว้มาคำนวณ
- ตัวหนังสือจริงๆ เช่น ช้าง, ม้า, cow, sid110, I love my pen
- ตัวหนังสือที่หน้าตาเหมือน Type อื่น เช่น 123 จริงๆ สามารถเป็นตัวหนังสือก็ได้ ถ้าพิมพ์ว่า ‘123
- ธรรมชาติจะอยู่ ชิดซ้ายของ Cell โดยอัตโนมัติ
- แปลว่า บางช่องเราอาจเห็นว่าเป็นตัวเลข แต่จริงๆ เป็น Text ก็ได้ วิธีดูคร่าวๆ คือ หากมันถูกจัดชิดซ้าย โดยที่เราไม่ได้ไปเป็นคนกำหนดจัดซ้ายเอง มันจะเป็น Text, ถ้าชิดขวาจะเป็นตัวเลข)
- เราสามารถกำหนดให้เวลาพิมพ์ข้อมูลลงไป แล้วบังคับให้ผลลัพธ์เป็น Text ได้ โดย
  - วิธีที่ 1 : ใส่เครื่องหมาย ‘ นำหน้า เช่น ‘001234 มันจะออกมาเป็น 001234 ที่เป็น Text
  - วิธีที่ 2 : เปลี่ยน format ของ Cell เป็น Text ก่อน แล้วค่อยพิมพ์ข้อมูล
Logic ตรรกะ มีอยู่ 2 อย่างคือ
- TRUE เกิิดเมื่อมีการเปรียบเทียบค่าแล้วเป็นจริง เช่น ใส่สูตรว่า =10>3
- FALSE เกิิดเมื่อมีการเปรียบเทียบค่าแล้วเป็นเท็จ เช่น ใส่สูตรว่า =5<2
- ธรรมชาติจะอยู่ กึ่งกลางของ Cell โดยอัตโนมัติ
Error ข้อผิดพลาด
- ธรรมชาติจะอยู่ กึ่งกลางของ Cell โดยอัตโนมัติ
- ความผิดพลาดมีหลายสาเหตุด้วยกัน การที่เรารู้ความแตกต่างของมัน จะทำให้เราเข้าใจ และแก้ไขข้อบกพร่องได้อย่างถูกต้องมากขึ้นครับ

ประเภท Error	ความหมาย	ค่าทดสอบที่แสดงออกมาจากการใช้ฟังก์ชัน ERROR.TYPE
#NULL!	เกิดจากการใช้ Intersection Operator (ช่องว่าง) แล้วปรากฎว่าการ Intersect นั้นออกมาเป็น Set ว่าง เช่น =SUM(A10:A20 C10:C20) (ช่วง 2 อันไม่มีช่องซ้ำกันเลย)	1
#DIV/0!	เกิดจากการคำนวณที่มีการหารด้วย 0	2
#VALUE!	เกิดจากการใส่ค่า Input ลงไปในสูตรผิดประเภทข้อมูล เช่น =IF(“แมว”,1,0) จะผิด เพราะ ตรง “แมว” จริงๆ ต้องเป็นตรรกกะ ที่ถูกคือ = IF(A3=”แมว”,1,0) จึงจะไม่ Error	3
#REF!	เกิดจากการที่ไม่สามารถอ้างถึง Cell ได้ ซึ่งอาจเกิดจากการ Delete Cell, Column หรือ Row ไปจนช่องนั้นหายไป	4
#NAME?	มีการอ้างถึงชื่อ Cell หรือ Function ที่ไม่มีอยู่จริง (ปกติมักจะเกิดจากการจะใส่ข้อความ แต่ลืมใส่ ” ” ครอบ)	5
#NUM!	มีการใส่ค่าตัวเลขที่มากเกินกว่า Excel จะรับไหว หรือ อาจเกิดจากการที่ Excel ทำการคำนวณ Trial & Error ค่า (Iterative) แล้วไม่ได้ผลลัพธ์ที่ต้องการ เช่น ตอนใช้สูตร IRR	6
#N/A	เป็น Error ที่เจอบ่อยมาก เช่น หาข้อมูลด้วยการ Lookup ไม่เจอ, อาจเกิดจากใส่ Input ลงสูตรผิดหรือเกิน (ซึ่งการ Lookup ไม่เจอ ไม่ใช่แปลว่าเขียนสูตรผิด)	7
#GETTING_DATA		8

วิธีตรวจสอบประเภทข้อมูล

โดยเราสามารถตรวจสอบว่าข้อมูลที่เราสนใจอยู่ในประเภทไหนได้ง่ายๆ มี 3 วิธี คือ

ใช้ยางลบ Clear Format ทิ้งแล้วดูด้วยตา

หากเป็น Number จะอยู่ชิดขวาของช่อง
หากเป็น Text ปกติจะอยู่ชิดซ้ายของช่อง
หากเป็น Logic จะเป็นคำว่า TRUE FALSE อยู่กึ่งกลางช่อง
หากเป็น Error มักมีเครื่องหมาย # หรือ ! และ อยู่กึ่งกลางช่อง

ใช้ Function =TYPE(ช่องที่ต้องการตรวจสอบ)

ผลลัพธ์ที่ออกมาจะเป็นรหัสตัวเลข ซึ่งมีความหมายดังนี้

1 = Number (ตัวเลข)
2 = Text (ตัวอักษร)
4 = Logic (ตรรกกะ) :
16 = Error (ผิดพลาด)
64 = Array (อาเรย์) **** เป็นการใส่ข้อมูลหลายๆ ค่าใน Cell เดียว ซึ่งขอไม่พูดถึึง ณ ตอนนี้ครับ

ใช้ฟังก์ชั่นกลุ่ม IS….

เป็นการถามคำถามกับ Excel ว่าใช้ประเภทข้อมูลที่สนใจมั้ย? ซึ่งจะให้ผลลัพธ์เป็น TRUE/FALSE

ISNUMBER เช็คว่า ใช่ตัวเลขหรือไม่?
ISTEXT เช็คว่า ใช่ตัวเลขหรือไม่?
ISNONTEXT เช็คว่า ไม่ใช่ตัวหนังสือหรือไม่?
ISLOGICAL เช็คว่า ใช่ตรรกกะหรือไม่?
ISERROR เช็คว่า error หรือไม่?
ISFORMULA เช็คว่า ใช่สูตรหรือไม่?

ทำไมเราต้องเรียนรู้ประเภทของข้อมูลใน Excel?

หากเรารู้ว่าข้อมูลที่เรากำลังใส่เป็นข้อมูลแบบไหน เราจะสามารถใช้สูตรได้พลิกแพลงมากขึ้น โดยเรารู้ว่าเราต้องใส่ Input ลงไปในสูตรในแต่ละ Argument (แต่ละช่อง Input) ให้ถูกประเภท ตามที่แต่ละสูตรต้องการ เช่น
- สูตร =LEFT(ข้อความ,จำนวนตัวอักษรที่ต้องการ) จะเห็นว่า จำนวนตัวอักษรที่ต้องการ จะต้องเป็นข้อมูลที่เป็น Number (ตัวเลข) เท่านั้น แปลว่า หากเรามีสูตรที่ได้ผลลัพธ์เป็นตัวเลข เราก็สามารถผสมสูตรนั้นลงไปในช่อง “จำนวนตัวอักษรที่ต้องการ” ได้เช่นกัน
เราจะเข้าใจการทำงานของ Excel มากขึ้น เช่น เราจะเข้าใจว่า ทำไมเวลาเอาวันที่มาลบกันแล้วจะได้ออกมาเป็นช่วงเวลาระยะห่างของสองวันได้ (เพราะ Excel มองว่าวันที่คือตัวเลขตัวหนึ่งนั่นเอง)

เมื่อคุณอ่านบทความนี้จบแล้ว ก็น่าจะมีพื้นฐานเกี่ยวกับประเภทข้อมูลใน Excel กันแล้วล่ะ ในตอนต่อๆ ไปเราจะได้มาเรียนรู้วิธีการใช้สูตร (Formula) กันครับ

November 7, 2013

รวมคีย์ลัดโปรแกรม Microsoft Excel ให้คุณทำงานเร็วขึ้น

อะไรคือ คีย์ลัด?

คีย์ลัด (Shortcut หรือ Hotkey) ก็คือ การกดปุ่มบน Keyboard เพื่อสั่งให้ Excel ทำงานต่างๆ (เช่น กด Ctrl+Z เพื่อ Undo ) แทนที่จะใช้เม้าส์เลื่อนไปกดเมนูต่างๆบนหน้าจอ

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

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

แล้วคีย์ลัดของ Excel มีอะไรบ้าง?

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

Download Excel File => Excel-shortcut (2003) / Excel-shortcut (2007)
Download PDF version => Excel-shortcut

ปุ่มประเภท Function
Key	กดเดี่ยวๆ	กดร่วมกับ
		Shift	CTRL	Shift+CTRL	ALT
F1	Help				New Chart Sheet(‘03) Insert Chart Object(‘07+)
F2	Edit Mode	Insert/EditComment			Save As
F3	Paste Name Formula (Edit Mode)	Insert Function	Deﬁne Name	Create Names (Labels)
F4	Repeat / Anchor (Edit Mode)	Find Again	Close Window		Quit Excel
F5	Goto	Find	Restore Window Size
F6	สลับโหมด Worksheet/ Ribbon/Task pane/Zoom		Next Workbook	Previous Workbook
F7	Spell Check		Move Window
F8	Extend Selection	Add to Selection			Macro
F9	Re-Calculate (All Sheet)	Calculate Selected Worksheet	Minimize Workbook
	Extra	ctrl+alt+f9 : Full Calculate (All sheet)
	Extra	ctrl+alt+shift+f9 : Full Calculate (All sheet) + Rebuild Dependency Tree
F10	Activate Menu	Context Menu	Restore Workbook
F11	New Chart Sheet	Insert Worksheet			VBA Editor
F12	Save As	Save	Open	Print

ปุ่มประเภท Navigation
Key	กดเดี่ยวๆ	กดร่วมกับ
		Shift	CTRL	Shift+CTRL	ALT
/			Select Array
\			Select Row Differences	Select Column Differences
Insert	Insert Mode		Copy
Delete	Clear		Delete to End of Line [fusion_builder_container hundred_percent=”yes” overflow=”visible”][fusion_builder_row][fusion_builder_column type=”1_1″ background_position=”left top” background_color=”” border_size=”” border_color=”” border_style=”solid” spacing=”yes” background_image=”” background_repeat=”no-repeat” padding=”” margin_top=”0px” margin_bottom=”0px” class=”” id=”” animation_type=”” animation_speed=”0.3″ animation_direction=”left” hide_on_mobile=”no” center_content=”no” min_height=”none”][Edit Mode]
Home	Begin Row		Start of Worksheet
End	End Mode		End of Worksheet
Page Up	Page Up		Previous Worksheet		Left 1 Screen
Page Down	Page Down		Next Worksheet		Right 1 Screen
Left Arrow	Move Left	Select Left	Move Left Area	Select & Move Left Area
Right Arrow	Move Right	Select Right	Move Right Area	Select & Move Right Area
Up Arrow	Move Up	Select Up	Move Up Area	Select & Move Up Area
Down Arrow	Move Down	Select Down	Move Down Area	Select & Move Down Area	Drop down list
Space Bar	Space	Select Row	Select Column	Select Area/All	Control Box
Tab	Enter data & Move Right	Enter data & Move Left	Next Workbook	Previous Workbook	Next Application
Enter	Enter data & Move Down	Enter data & Move Up	Fill Selected range	Array Formula (Edit Mode)	Formula Line Break

ปุ่มประเภท ตัวอักษรปกติ
Key	กดร่วมกับ			Key	กดร่วมกับ
Key	CTRL	Shift+CTRL	ALT	Key	CTRL	Shift+CTRL	ALT
A	Select Area/All	Formula Arguments	Data (03/10)	`(~)	Toggle Formula View	General Format
B	Bold			1	Cell Format	Number Format	QAT Button 1
C	Copy			2	Toggle Bold	Time Format	QAT Button 2
D	Fill Down		Data Menu	3	Toggle Italics	Date Format	QAT Button 3
E			Edit Menu	4	Toggle Underline	Currency Format	QAT Button 4
F	Find	Font Name (‘03) Format Menu (‘07+)	File Menu	5	Toggle Strikethrough	Percent Format	QAT Button 5
G	Goto			6		Scientiﬁc Format	QAT Button 6
H	Replace		Help Menu, Home (‘07/‘10)	7	Standard Toolbar (‘03)	Apply Border	QAT Button 7
I	Italics		Insert Menu	8	Hide/Show Outline	Select Region	QAT Button 8
K	Insert Hyperlink			9	Hide Rows	Unhide Rows	QAT Button 9
M			Formula (‘07+)	0	Hide Columns	Unhide Columns	QAT Button More…
N	New Workbook		Insert (‘07+)	–	Delete Cell	No border	Control Menu
O	Open Workbook	Selects all commented cells	Format Menu	= (+)		Insert cells/rows/column	Auto Sum
P	Print		Page Layout (‘07/‘10)	]	Direct Dependents	Select All Dependents
R	Fill Right		Review (‘07+)	[	Direct Precedents	Select All Precendents
S	Save			;	Insert Date	Insert Time	Select VisibleCells
T			Tools Menu	‘ (apostrophe)	Copy Formula จากช่องข้างบน	Copy Cell Value จากช่องข้างบน	Style (‘03)
U	Underline	Expand/Collapse Formula Bar
V	Paste
W	Close Workbook		Window Menu
X	Cut
Y	Redo
Z	Undo

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

แหล่งอ้างอิง

[/fusion_builder_column][/fusion_builder_row][/fusion_builder_container]

October 31, 2013

มาทำความรู้จักโปรแกรม Microsoft Excel กันเถอะ
Microsoft Excel คืออะไร?
มันคือโปรแกรมตัวนึงในชุดโปรแกรม Microsoft Office ซึ่ง Excel นั้นเป็นโปรแกรมยอดฮิต มีความสามารถรอบด้าน แต่เก่งมากด้านการวิเคราะห์ คำนวณ และการจัดการข้อมูลในรูปแบบตารางที่เรียกว่า Spreadsheet รวมถึงนำข้อมูลดิบในตารางมาแสดงผลในรูปแบบที่ทำให้เราเข้าใจข้อมูลนั้นลึกซึ้งมากยิ่งขึ้น เช่น สร้างกราฟ หรือตารางสรุปที่เรียกว่า PivotTable ซึ่งสามารถให้เราลองเปลี่ยนมุมมองการสรุปข้อมูลไปมาได้อย่างง่ายดายก็ยังได้

หน้าตาของ Microsoft Excel จะเห็นว่าเป็นตารางๆ นี่และครับเรียกว่า Spreadsheet
โดย Excel นั้นมีประวัติมายาวนาน ในยุคก่อนที่จะมี Excel นั้นมีโปรแกรม Spreadsheet อื่นมาก่อน เช่น Visicalc, MultiPlan แต่ที่นิยมมากก่อนจะมี Excel ก็คือ Lotus 1-2-3 แต่สุดท้าย Excel ก็สามารถกลายเป็น Spreadsheet Software ที่เป็นที่นิยมที่สุดในโลกได้
Excel Version แรก หรือ Excel 1.0 ออกมาตั้งแต่ปี 1985 และก็ออก Version ต่างๆ เรื่อยมา เช่น Excel 2, 3, 4, 5, Excel 95,Excel 97,Excel 2000, Excel Office XP, Excel 2003, Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021 และ Version ล่าสุดที่เป็นแบบ Subscription ที่จะคอยอัปเดทความสามารถของตัวเองเรื่อยๆ และมี Feature ที่เจ๋งที่สุด ก็คือ Excel 365 (ซึ่งรองรับ Dynamic Array ที่เจ๋งมากๆ)
แม้จะมีมาแล้วหลากหลาย Version แต่ทุกอันก็ใช้หลักการเดียวกันหมด เพียงแต่มีนู่นนี่เพิ่มมานิดหน่อยเท่านั้น (ถ้ายังไม่ลงลึกถึงเครื่องมือขั้นสูงอ่ะนะ) หากเราหัด Excel เป็นซัก Version นึงเดี๋ยวก็ใช้เป็นทุก Version ครับ
แต่ถ้าใครอยากรู้ว่า Excel แต่ละ Version มีฟังก์ชันอะไรต่างกันบ้างก็มาดูได้ที่นี่เลย

List ฟังก์ชันทั้งหมดของ Excel ในทุก Version

กลุ่มเป้าหมายของผู้ใช้งาน
อย่างที่ชื่อของ Microsoft Office บอกไว้ว่า Office กลุ่มเป้าหมายที่เหมาะกับการใช้โปรแกรมนี้ที่สุดคือคนที่ต้องทำงานออฟฟิศ เรียกได้ว่ายุคหลังๆ เวลาสมัครงาน/สัมภาษณ์งาน ต้องมีการถามเลยว่าใช้ Excel เป็นรึเปล่า? ถ้าใครทำเป็นทำเก่งก็จะได้เปรียบขึ้นมากครับ!! แต่มันไม่ได้หมายความว่าอาชีพอื่นจะใช้ประโยชน์จาก Excel ไม่ได้ อย่างเช่น คนที่เป็นเจ้าของกิจการ ก็สามารถใช้ Excel วางแผนโครงการ ทำงานบัญชี เก็บข้อมูลการซื้อขายสินค้า หรือแม้แต่คุณแม่บ้าน ก็ใช้บันทึกรายรับรายจ่ายได้ เช่นกัน ดังนั้นมันคุ้มค่ามากที่จะลงทุนศึกษาเจ้า Excel นี้ให้ชำนาญมากขึ้น
Microsoft Excel ทำอะไรได้? ต่างจากโปรแกรม Office อื่นๆยังไง?
จริงๆ แล้ว จะต้องรู้ก่อนว่า โปรแกรม Office แต่ละตัวมีความสามารถ และมีความถนัดต่างกัน อธิบายสั้นๆ ได้ดังนี้
- Microsoft Word – เหมาะกับการทำเอกสาร ทำรายงาน (ใช้ผนวกกับ Excel เพื่อทำ Label/จดหมายจำนวนมากได้ เรียกว่า MailMerge)
- Microsoft PowerPoint – เหมาะกับการนำเสนอ Presentation ใส่ Effect ได้มากมาย (ผนวกให้ Excel ช่วยทำกราฟได้ดีกว่าตัวเองทำ)
- Microsoft Access – เหมาะกับการทำฐานข้อมูลโดยเฉพาะ สามารถผูกความสัมพันธ์ข้อมูลได้ซับซ้อน (ส่งต่อข้อมูลให้ Excel วิเคราะห์ได้สบายๆ)
- Microsoft Outlook – เหมาะกับการจัดการอีเมลล์ นัดประชุม วางแผนการทำงาน
- Microsoft Visio – เหมาะกับการทำ Flow Chart และ Diagram
- Microsoft Team – เอาไว้ประสานงานระหว่างคนในทีม
- Microsoft Project – เหมาะกับการวางแผนงานในรูปแบบโครงการ วางแผนลำดับงานและทรัพยากร
- Microsoft Excel – เก่งหลายอย่างมาก (Excel เป็น Super เป็ด) แต่เด่นด้านการคำนวณที่สุด เช่น ทำตารางคำนวณ ทำกราฟ วิเคราะห์ข้อมูล รองรับการเขียนสูตรคำนวณที่ซับซ้อน ทำตาราง Pivot Table (นำข้อมูลมาพลิกมุมมอง)
  - ทำงานเกี่ยวกับการวิเคราะห์ข้อมูลได้ดี เช่น
    บันทึกข้อมูลลงใน Cell ต่างๆ ได้เลย
    รวบรวมจากที่อื่นและดัดแปลงข้อมูลด้วย Power Query ก็ได้
    อีกทั้งสามารถสร้างความสัมพันธ์ Data Model และวิเคราะห์ข้อมูลขึ้นสูงด้วย Power Pivot ได้อีก
    นำเสนอข้อมูลเป็นกราฟ หรือ Dashboard ผ่าน Pivot Chart ก็ได้ (แต่สู้ Power BI Dashboard ไม่ได้)
  - ทำ Simulation ต่างๆ แก้ปัญหา Optimization ด้วย Solver ได้
  - ทำ Diagram / Flow Chart ได้ดี (แต่ไม่เก่งเท่า Visio)
  - วางแผนโครงการได้ดี เช่น สร้าง Gannt Chart (แต่ไม่เก่งเท่า Project)
  - วิเคราะห์ทางสถิติได้ดี เช่น ค่าสถิติทั่วๆไป ทำ Regression (แต่ไม่เก่งเท่าโปรแกรมทางสถิติโดยเฉพาะ)
อยากลองเทียบ Excel กับโปรแกรมอื่นๆ ลองอ่านอันนี้

Excel เป็น Super เป็ด : Excel vs เครื่องมือเฉพาะทาง

Excel ใช้งานยากมั๊ย??
โปรแกรม Excel ออกแบบมาได้ดีมาก จนมือใหม่ก็สามารถเรียนรู้การใช้งานได้ไม่ยากในเวลาอันสั้น แต่ Excel ก็ทำอะไรได้อีกเยอะมากๆ จนแม้แต่คนเก๋าๆ ก็ต้องเรียนรู้กันทั้งชีวิตเช่นกัน!
ถ้าหากลอง Search หาไฟล์งาน Excel ใน Google ดูจะพบว่ามีไฟล์หลากหลายมากครับ ทั้งแบบพื้นๆ ที่คนทั่วไปใช้กัน ไปจนถึงแบบ Advance ที่ระดับเทพ Excel ทั้งหลายเป็นคนพัฒนาขึ้นมา เช่น ลองหาคำว่า Excel Template, Excel Dashboard, Excel Sample ดูก็ได้ครับ
ซึ่งผมก็มีการทำคลิปสอนมือใหม่เอาไว้แล้ว ใครอยากใช้ Excel เป็น มาดูคลิปนี้ต่อได้เลยครับ

แล้วต่อด้วยอันนี้ก็ดีครับ

หรือถ้าใครสนใจอยากศึกษาจริงจัง ผมก็แนะนำคอร์สออนไลน์อันนี้ครับ ซึ่งจะสอน Excel จนคุณทำงานจัดการข้อมูลได้อย่างแน่นอน
Excel Level Up
เป็นคอร์สที่ฮิตที่สุดของเทพเอ็กเซล ซึ่งรวมเนื้อหาพื้นฐานที่จำเป็นที่สุดของ Excel เข้าไว้ด้วยกันในคอร์สเดียว (12 ชั่วโมงครึ่ง)
- ปูพื้นฐานแต่ต้น ทั้งประเภทข้อมูล จัดการข้อมูลวันที่และเวลา / การจัดการข้อความ
- เครื่องมือต่างๆ เช่น Sort/Filter, Flash Fill, Conditional Format, Data Validation, Goal Seek
- สอนแนวคิด แนวทางการเขียนสูตรซ้อนกัน ทำยังไงไม่ให้งง
- ฟังก์ชันยอดนิยม เช่น IF / VLOOKUP / INDEX / MATCH / SUMIFS
- เน้นมากๆ เรื่องการใช้ Table เก็บข้อมูล และสรุปด้วย Pivot Table จากนั้นจึงสร้าง Dashboard
- จ่ายเงินครั้งเดียวได้คอร์สที่เนื้อหาอัปเดทตลอดชีวิต (ปรับปรุงอย่างน้อยปีละ 1 ครั้ง)
  คนที่เคยซื้อคอร์ส Excel Level Up Revised ไปแล้ว จะสามารถเข้าไปดูคอร์สใหม่นี้ได้ฟรีเช่นกัน
- ราคา 1,490 บาท
- รับรองว่าได้เนื้อหาเน้นๆ ไม่มีน้ำ ตลอด 12 ชั่วโมงครึ่งอย่างแน่นอน
October 25, 2013

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

Step ของการทำกราฟ มีดังนี้

1. Think : คิดวัตถุประสงค์ของการนำเสนอกราฟ

Tips ในการเลือก Chart ที่เหมาะสม

2. Prepare : เตรียมข้อมูล

3. Create : การสร้างกราฟ

4. Customize : ปรับแต่งกราฟ

ส่วนประกอบของกราฟ หลักๆ มีดังนี้

1. หากเจอสูตรที่ใช่ ให้กด Tab เลย เพื่อเลือกใช้

กติกาเบื้องต้นเกม Sudoku

วิธีสร้างตัวตรวจกติกาเกม Sudoku

สร้างตารางขนาด 9×9

กำหนดเงื่อนไข ว่าให้ใส่ได้แค่เลข 1-9 เท่านั้น

สร้างตัวตรวจสอบว่าแต่ละแถวมีเลข 1-9 กี่ตัว

สร้างตัวตรวจสอบว่าแต่ละคอลัมน์มีเลข 1-9 กี่ตัว

สร้างตารางตรวจเช็คการใส่ข้อมูลซ้ำ ในแต่ละ Zone 3×3 อันใหญ่

การเตรียมการก่อนให้ผู้เล่นเริ่มเล่นเกมจริง

Sudoku Solver

แบบที่ 1 : แก้โดยไม่ใช้ VBA แต่ใช้ Iteration แทน

แบบที่ 2 แก้โดยใช้ VBA

ทำตามขั้นตอนนี้เลยนะครับ

ลาก Select Range กำหนดพื้นที่ตามต้องการ

กำหนดเงื่อนไขใน Setting

กำหนด Error Message

ต่อไปหากลองใส่ข้อมูลให้ซ้ำกัน มันก็จะขึ้นเตือนแล้วครับ !!

Excel Lookup ไม่ลืมหูลืมตา ไม่ดูตัวพิมพ์เล็กพิมพ์ใหญ่!?

ทางแก้ไข

Concept การใส่สูตร

หาว่าคำที่ต้องการอยู่แถวที่เท่าไหร่

แปลงวันที่วิธีแรก ใช้ฟังก์ชั่น EDATE มาช่วย

วิธีการเขียนสูตร EDATE

ตัวอย่างการทำงานของฟังก์ชั่น EDATE

ใช้ EDATE แปลงจาก พ.ศ. เป็น ค.ศ.

แปลงวันที่วิธีที่ 2 ใช้ฟังก์ชั่น DATE, YEAR, MONTH, DAY

วิธี 1 ตัด Text ออกเป็นส่วนๆ แล้วเชื่อม (ยาก)

วิธี 2 แปลง Format ของ Text ให้ DATEVALUE รู้จัก (ง่าย)

1. ดูข้อมูลสรุปทางสถิติจากจาก Status Bar ด้านขวาล่าง

2. หัดใช้ Keyboard คีย์ลัดบ้าง

3. ใช้ Quick Access Toolbar

4. ใช้ Defined Name ในการอ้างอิงสูตร

วิธีตั้งชื่อ Definced Name ให้ Cell หรือ Range

5. สร้าง Custom List เพื่อช่วย Auto Fill / Sort

วิธีการสร้าง Custom List

6. เรียนรู้ Function ให้มากขึ้น

7. ใช้ Picture Link ช่วยตรวจผลกระทบที่ช่องปลายทาง

8. ใช้ Paste Column Widths แทนการนั่งปรับความกว้างของคอลัมน์เอง

9. Lock Format Painter เอาไว้ เพื่อให้ Paint ช่องปลายทางได้ทีละหลายๆ รอบ

10. ทำงานใน Excel แล้วค่อยใช้ Copy ไป Paste as Link ลงที่อื่น

วิธีการป้องกันไม่ให้มี Auto Hyperlink

วิธีลบ Hyperlink ที่มีอยู่ออกแบบง่ายๆ

Excel 2010 ขึ้นไป

Excel ver. ต่ำกว่า 2010

วาดรูปด้วย Shape

วาดรูปด้วยการ Fill สีลงช่อง แล้วทำให้ช่องขนาดเล็กๆ ละเอียดๆ

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

การ Print เส้น Gridlines

สถานการณ์

ส่วนประกอบ

Concept การผสมสูตร

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

สุ่ม item แบบที่ 1 : เขียนรายการที่จะสุ่มลงไปในสูตรเลย

ส่วนประกอบ

Concept การผสมสูตร

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

สุ่ม item แบบที่ 2 : มีรายการที่จะสุ่มอยู่ในตาราง Excel เป็น Range คอลัมน์นึง

ส่วนประกอบ

Concept การผสมสูตร

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

PivotTable คืออะไร?

ตัวอย่างการสรุปผล

PivotTable ไม่ยากอย่างที่คิด

Step การใช้ Pivot Table

Field List

ลองลาก Field List หลายๆ แบบ

วิธีการแปลความหมายตาราง Pivot Table

Video ประจำตอน

เราอาจใช้ Excel เพื่อจุดประสงค์ที่แตกต่างกัน

Quick Access Toolbar อยู่ตรงไหน ใช้งานยังไง?

DATEDIF ไว้ใช้ทำอะไร?

วิธีการใช้ฟังก์ชั่น