เทคนิคการเลือกเซลล์,ช่วงเซลล์,คอลัมท์,แถว,ข้อมูลใน Excel Worksheet

เทคนิคการเลือกเซลล์,เซลล์เรนจ์,คอลัมท์,แถว หรือข้อมูลใน Excel

การ copy คอลัมท์หรือแถว เป็นวิธีที่คุ้นเคยกัน เพียงแค่ใช้คำสั่งคัดลอก (copy) แล้ววาง (paste) บนช่วงเซลล์ใหม่

ซึ่งจะได้ผลลัพท์ เป็นตารางที่เหมือนเดิม ซึ่งสูตรที่อ้างอิงอาจ เปลี่ยนตำแหน่งไป

แต่ถ้าต้องการ copy หรือ insert บางคอลัมท์ หรือ บางแถวเหมือนภาพด้านล่าง คือ ต้องการคัดลอกคอลัมท์ ที่ 1, 3 และ 6 โดยที่ยังต้องการให้ผลของการ paste บนช่วงเซลล์ใหม่นั้น คอลัมท์ทั้ง 3 (คอลัมท์ 1,3,6) อยู่ติดกัน และยังคงรูปแบบเดิมไว้ ด้วยเทคนิคการเลือกพื้นที่เซลล์

ก่อนอื่นเรามาดูความแตกต่างของการเลือกพื้นที่เซลล์ใน Excel ระหว่าง Shift กับ Ctrl

• การกด Shift ในขณะที่เลือกพื้นที่เซลล์จะเป็นการเลือกส่วนของพื้นที่ติดกันต่อเนื่องกันไป
• แต่ถ้ากดแป้นคีย์ Ctrl จะเป็นการเลือกเฉพาะเซลล์ที่เราคลิกเลือกเท่านั้น

ซึ่งการ copy หรือ insert ก็สามารถใช้พื้นฐานเดียวกันนี้

วีดีโอแสดงการเลือกเซลล์,ช่วงเซลล์,คอลัมท์,แถวด้วย Ctrl

คำนวณจำนวนตัวเลขที่มีตัวอักษร(Text) ใน Excel

การคำนวณจำนวนตัวเลขที่มี Text ใน Excel

จากบทความเดิมที่แสดงการใช้ Custom Format Cells ส่วนในบทความนี้เป็นภาคต่อที่จะบอกว่า Custom Format Cell ทำอะไรได้บ้างและมีประโยชน์อย่างไร

เมื่อไรที่ควรใช้ Custom Format Cells

• เมื่อต้องการใส่ Text ในเซลล์ที่มี number โดยที่ยังคงคำนวณ บวก ลบ หาร หรือคูณจำนวนในเซลล์นี้กับเซลล์อื่นๆ ได้เหมือนเดิม
• กรณีที่มีคำหรือตัวเลขที่ต้องคีย์อยู่ทุกรายการ การใช้ Custom format cells นี้ช่วยให้งานง่ายขึ้น
• ข้อมูลที่ทำอยู่มีขนาดใหญ่ และต้องการใช้ช่วงเซลล์ในเวิร์คชีทให้น้อยที่สุดเท่าที่เป็นไหได้ เพื่อให้ Excel ประมวลผลได้เร็วขึ้น
• ต้องการบันทึกวันที่ให้เป็นรูปแบบเฉพาะ เช่น เมื่อคีย์วันที่ 22/3/2013 แล้วใช้ custom format cell กำหนดให้เป็นตามรูป

วีดีโอตัวอย่างแสดงการคำนวณจากเซลล์ที่มีทั้ง number และ text

การแปลงตัวเลขเป็นค่าเงินภาษาอังกฤษ English Text

วิธีการแปลงยอดรวมตัวเลขเป็นค่าเงินภาษาอังกฤษ (English Text)ใน Excel

ด้วยการสร้างฟังก์ชัน Called SpellNumber และนำมาใช้เป็น Add-In ใน Microsoft Excel ซึ่ง Add-In - Called SpellNumber ไม่ใช่ฟังก์ชันดั้งเดิมของ MS Excel จึงทำให้สามารถใช้ได้ต่อเมื่อได้เพิ่มฟังก์ขัน Spellnumber แล้วเท่านั้น

วิธีสร้างฟังก์ชัน SpellNumber

1. เปิด Microsoft Excel สำหรับ user ที่ไม่เคยใช้ Developer ต้องเปิด Developer ก่อนเพื่อ Enable Macro เพราะไม่เช่นนั้นจะเกิดปัญหาบันทึกโมดูลไม่ได้ ลิงก์เพื่อดูการเปิด Developer Tab


2. เข้าที่แทป Developer เลือก Macro Security ซึ่งเป็นรูปสามเหลี่ยมสีเหลืองตามภาพ
   


3. เข้า Trust Center(ด้านซ้าย) เลือกหัวข้อ Enable all macro (not recommended; potentially dangerous code can run) แล้ว ok
   


4. เมื่อกลับมาที่หน้า worksheet ให้กด ALT+F11 เพื่อเปิดใช้งาน Visual Basic Editor หรือ เลือก Developer Tab แล้วเลือก Visual Basic
   
   
   

   1. เข้าเมนู แทรก(Insert)
   2. คลิกโมดูล (Module)
   3. แล้วพิมพ์รหัสวางในโมดูล (ใช้คัดลอกได้)
   

รหัสที่ใช้คัดลอกไปวางที่โมดูลที่เพิ่มใหม่

Option Explicit
'Main Function
Function SpellNumber(ByVal MyNumber)
    Dim Dollars, Cents, Temp
    Dim DecimalPlace, Count
    ReDim Place(9) As String
    Place(2) = " Thousand "
    Place(3) = " Million "
    Place(4) = " Billion "
    Place(5) = " Trillion "
    ' String representation of amount.
    MyNumber = Trim(Str(MyNumber))
    ' Position of decimal place 0 if none.
    DecimalPlace = InStr(MyNumber, ".")
    ' Convert cents and set MyNumber to dollar amount.
    If DecimalPlace > 0 Then
        Cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _
                  "00", 2))
        MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
   End If
   Count = 1
   Do While MyNumber <> ""
        Temp = GetHundreds(Right(MyNumber, 3))
        If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars
        If Len(MyNumber) > 3 Then
            MyNumber = Left(MyNumber, Len(MyNumber) - 3)
        Else
            MyNumber = ""
        End If
        Count = Count + 1
    Loop
    Select Case Dollars
        Case ""
            Dollars = "No Dollars"
        Case "One"
            Dollars = "One Dollar"
         Case Else
            Dollars = Dollars & " Dollars"
    End Select
    Select Case Cents
        Case ""
            Cents = " and No Cents"
        Case "One"
            Cents = " and One Cent"
              Case Else
            Cents = " and " & Cents & " Cents"
    End Select
    SpellNumber = Dollars & Cents
End Function      
' Converts a number from 100-999 into text 
Function GetHundreds(ByVal MyNumber)
    Dim Result As String
    If Val(MyNumber) = 0 Then Exit Function
    MyNumber = Right("000" & MyNumber, 3)
    ' Convert the hundreds place.
    If Mid(MyNumber, 1, 1) <> "0" Then
        Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
    End If
    ' Convert the tens and ones place.
    If Mid(MyNumber, 2, 1) <> "0" Then
        Result = Result & GetTens(Mid(MyNumber, 2))
    Else
        Result = Result & GetDigit(Mid(MyNumber, 3))
    End If
    GetHundreds = Result
End Function      
' Converts a number from 10 to 99 into text. 
Function GetTens(TensText)
    Dim Result As String
    Result = ""           ' Null out the temporary function value.
    If Val(Left(TensText, 1)) = 1 Then   ' If value between 10-19...
        Select Case Val(TensText)
            Case 10: Result = "Ten"
            Case 11: Result = "Eleven"
            Case 12: Result = "Twelve"
            Case 13: Result = "Thirteen"
            Case 14: Result = "Fourteen"
            Case 15: Result = "Fifteen"
            Case 16: Result = "Sixteen"
            Case 17: Result = "Seventeen"
            Case 18: Result = "Eighteen"
            Case 19: Result = "Nineteen"
            Case Else
        End Select
    Else                                 ' If value between 20-99...
        Select Case Val(Left(TensText, 1))
            Case 2: Result = "Twenty "
            Case 3: Result = "Thirty "
            Case 4: Result = "Forty "
            Case 5: Result = "Fifty "
            Case 6: Result = "Sixty "
            Case 7: Result = "Seventy "
            Case 8: Result = "Eighty "
            Case 9: Result = "Ninety "
            Case Else
        End Select
        Result = Result & GetDigit _
            (Right(TensText, 1))  ' Retrieve ones place.
    End If
    GetTens = Result
End Function     
' Converts a number from 1 to 9 into text. 
Function GetDigit(Digit)
    Select Case Val(Digit)
        Case 1: GetDigit = "One"
        Case 2: GetDigit = "Two"
        Case 3: GetDigit = "Three"
        Case 4: GetDigit = "Four"
        Case 5: GetDigit = "Five"
        Case 6: GetDigit = "Six"
        Case 7: GetDigit = "Seven"
        Case 8: GetDigit = "Eight"
        Case 9: GetDigit = "Nine"
        Case Else: GetDigit = ""
    End Select
End Function 
  

5. บันทึก
   


6. File name พิมพ์ชื่อไฟล์เป็น Spellnumber และ ลือกรูปแบบ (Save as types) เป็น Excel Add-IN ซึ่ง Excel จะเลือกไปบันทึกยังโฟลเดอร์ Add-In อัตโนมัติ จากนั้น กด Save
   


7. เมื่อกลับมาที่ worksheet ให้ไปที่ office button อีกครั้งแล้วเข้า Excel options


8. เข้าหัวข้อ Add-In ทางด้านล่างของหน้า จะมีตัวเลือก Manage: Excel Add-ins ให้คลิก 
   Go....
   
   
   
   

   

---

วิธีใช้ฟังก์ชัน Spellnumber เหมือนการใช้ฟังก์ชันอื่นๆ ใน Excel

รูปแบบฟังก์ชัน = Spellnumber(number or cell)

ฟังก์ชัน Spellnumber สามารถใช้ได้ 2 รูปแบบคือ 1. ป้อนตัวเลขในฟังก์ชันเลย กับ 2. อ้างอิงไปยังเซลล์ที่ต้องการ

Row	Column
	A	B	C
1	ตัวเลข	แสดงการใช้ฟังก์ชัน	ผลลัพท์จาก Spellnumber
2	12.75	= Spellnumber(12.75)	Twelve Dollars and Seventy Five Cents
3	402.50	= Spellnumber(A3)	Four Hundred Two Dollars and Fifty Cents

credit:

http://support.microsoft.com

แยกตัวเลข,ข้อความในคอลัมท์เดียวให้เป็นสองคอลัมท์ Excel

เทคนิคการแยกตัวเลขและข้อความที่อยู่ในเซลล์เดียวกัน ให้เป็นสองคอลัมท์ใน Excel ด้วยฟังก์ชัน Find

จากรูป ต้องการแยกจำนวน 100 ออกจากหน่วย sachet ที่ถูกบันทึกไว้ในเซลล์เดียวกัน
ด้วยโจทย์นี้สามารถนำฟังก์ชันข้อความสองชุดมาประยุกต์ใช้ด้วยกัน คือฟังก์ชัน Find และกลุ่มฟังก์ชัน Left, Right, Len (ดูการใช้ Left, Right, Len)

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

ไวยากรณ์ฟังก์ชัน FIND

FIND(find_text,within_text,start_num)

• find_text :ข้อความหรือตัวเลขที่ต้องการค้นหา ถ้าเป็นข้อความให้ใส่ "xx "
• within_text : คือข้อความที่มีสิ่งที่่คุณต้องการค้นหา (find_text)
• start_num : ใช้ระบุหมายเลขตำแหน่งใน within_text ที่ต้องการให้เริ่มค้นหา โดยอักขระตัวแรกใน within_text เป็นอักขระ
• หมายเหตุ : ถ้าไม่ได้ใส่ค่าให้กับ start_num ฟังก์ชันจะหมายถึงเริ่มตั้งแต่ 1 หรืออักขระตัวแรก

	D	H	J	K	K
		ผลลัพท์		แสดงการแทนค่าฟังก์ชัน
2	Contain	จำนวน	หน่วย	จำนวน	หน่วย
3	100 sachet	100	sachet	=LEFT(D3,FIND(" ",D3))*1	=RIGHT(D3,LEN(D3)-FIND(" ",D3))
4	1 kg	1	kg	=LEFT(D4,FIND(" ",D4))*1	=RIGHT(D4,LEN(D4)-FIND(" ",D4))
5	80 gram	80	gram	=LEFT(D5,FIND(" ",D5))*1	=RIGHT(D5,LEN(D5)-FIND(" ",D5))

ข้อสังเกตุ จากตัวอย่าง:

• ฟังก์ชัน FIND ใช้ระบุตำแหน่งของวรรคระหว่าง จำนวน กับ หน่วยวัด (FIND(" ",D3) ผลลัพท์เท่ากับ 4
• LEFT(D4,FIND(" ",D4))*1 คือให้สูตรคืนค่าข้อความจากด้านซ้ายจนถึงวรรค และเพื่อให้เป็นตัวเลขจึง คูณด้วย 1
• RIGHT(D5,LEN(D5)-FIND(" ",D5)) คือให้คืนค่าข้อความจากด้านขวาจนถึงวรรค (" ")

การจัดลำดับด้วยฟังก์ชัน Rank ใน Excel

ฟังก์ชัน Rank

การทำงานของฟังก์ชัน Rank เหมือนกับการจัดลำดับผู้ที่เข้าสอบแข่งขัน หรือจัดอันดับทีมที่เข้าประกวดการแสดง


โดยใช้คะแนนของผู้เข้าแข่งขันทุกหน่วยเป็นค่าอ้างอิงในการจัดอันดับ
และในฟังก์ชัน Rank ก็มีเงื่อนไขที่ใช้กำหนดวีธีเรียงอันดับ 2 แบบ คือการเรียงจากมากไปน้อย หรือ เรียงจากน้อยไปมาก

ไวยากรณ์ :

RANK (number,ref,order)

• Number คือตัวเลขที่ต้องการค้นหา และเป็นหนึ่งในรายการทั้งหมด


• Ref คืออาร์เรย์หรือการอ้างอิง รายการตัวเลข และค่าที่ไม่ใช่ตัวเลขใน ref จะถูกละเว้น


• Order ตัวเลขที่ใช้กำหนดวิธีจัดเรียงถ้าไม่ระบุไว้หรือเป็น 0 จะเรียงจากมากไปน้อย(Z->A) และถ้าเป็น 1 หรือ มากกว่าจะเรียงจากน้อยไปมาก (A->Z)

สมมุติว่าค่าที่ให้จัด Rank มีค่าซ้ำกัน ฟังก์ชัน RANK จะให้ลำดับที่ซ้ำด้วยเช่นกัน และตัวเลขซ้ำจะมีผลกระทบถึงลำดับที่ของตัวเลขตัวถัดมา เช่น ถ้าในรายการที่เรียงลำดับ มีลำดับที่ 2 สองรายการ ลำดับถัดไปต้องเป็นลำดับที่ 4 คือจะข้ามลำดับที่ 3 ไป

ตัวอย่างการใช้ ฟังก์ชัน RANK
ให้จัดอันดับพนักงานขายโดยใช้ยอดขายที่ทำได้ในเดือนก่อนโดยอ้างอิงจากยอดขายเดือนที่ผ่านมา

Row	Column
	A	B	C	D	E	F
1	Sales/ยอดขาย		แทนค่าฟังก์ชัน(Z->A)	ผลลัพท์ Z->A	แทนค่าฟังก์ชัน(A->Z)	ผลลัพท์ A->Z
2	กรณ์	105	=RANK($B2,B$2:B$8)	1	=RANK($B2,B$2:B$8,1)	6
3	ไข่มุก	97	=RANK($B3,B$2:B$8)	3	=RANK($B3,B$2:B$8,2)	5
4	จุตินันท์	88	=RANK($B4,B$2:B$8,0)	5	=RANK($B4,B$2:B$8,3)	3
5	ชลิดา	105	=RANK($B5,B$2:B$8,0)	1	=RANK($B5,B$2:B$8,1)	6
6	ปีใหม่	92	=RANK($B6,B$2:B$8)	4	=RANK($B6,B$2:B$8,1)	4
7	พิชิต	79	=RANK($B7,B$2:B$8,0)	7	=RANK($B7,B$2:B$8,2)	1
8	อินทนน	81	=RANK($B8,B$2:B$8)	6	=RANK($B8,B$2:B$8,1)	2

หมายเหตุ

• การเรียงจากมากไปน้อยหรือน้อยไปมาก จะยึดจากตัวเลขยอดขายเป็นหลัก ไม่ใช่ตัวเลขของผลลัพท์ที่ใช้ฟังก์ชันแล้ว


• คอลัมท์ C ให้ Order เป็น 0 และ เว้นว่างไว้แสดงผลลัพท์ในคอลัมท์ D
  
  • กรณ์กับชลิดา ซึ่งมียอดขายเท่ากับ 105 กลายเป็นอันดับที่ 1 ทั้งสองคน
  • อันดับถัดไปคือ ไข่มุก ยอดขายเท่ากับ 97 ข้ามไปเป็นอันดับ 3 เลย


• คอลัมท์ E ให้ Order เป็น 1 หรือ มากกว่า จะเรียงอันดับจากยอดขายน้อยที่สุด ไปหายอดขายที่มากกว่า

แปลง Microsoft Office เป็นไฟล์ pdf ใน Excel 2007

Portable Document Format หรือ PDF ซึ่งเป็นรูปไฟล์อิเล็กทรอนิกส์ชนิดที่ไม่เปลี่ยนแปลงโครงสร้างเดิม จึงทำให้มั่นใจได้ว่าไฟล์ที่เป็นฟอร์แมต PDF นั้นจะยังรักษารูปแบบเดิมได้ ถึงแม้จะถูกนำเสนอผ่านออนไลน์หรือการพิมพ์  อีกทั้ง การที่จะเปลี่ยนดาต้าในไฟล์ PDF ให้กลับมาเป็นรูปแบบเดิม ต้องใช้โปรแกรมแปลงกลับไม่สามารถเปลี่ยนในเอกสารได้ทันที

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

ซึ่งการจะใช้ฟีเจอร์ของ MS Office บันทึกเป็น PDF ได้นั้นในคอมพิวเตอร์นั้นต้องติดตั้งโปรแกรมอ่านอย่าง Adobe Acrobat หรือ Foxit Reader ซึ่งส่วนใหญ่เป็น ฟรีแวร์ หาดาวน์โหลดมาใช้กันได้ทั่วไป

---

วิธีการบันทึกไฟล์เป็นรูปแบบ PDF

• เปิดไฟล์งานที่ต้องการบันทึก


• ไปที่ Office button


• เลือก Save as


• ใต้ save a copy of the docmuent เป็น PDF or XPS


• ไฟล์งานจะถูกสั่ง Publish และไฟล์ PDF จะถูกเปิดด้วยโปรแกรมอ่าน PDF หลังจาก Publishing เสร็จสิ้น

---

นอกจากนี้ยังมีโปรแกรมที่น่าใช้สำหรับแปลงไฟล์เป็น PDF และยังรองรับการแปลงไฟล์งานแทบทุกประเภท อย่าง Email, Web page หรือ ไฟล์งานประเภทอื่นๆ

โปรแกรม CutePDF writer ไม่ใช่โปรแกรมขนาดใหญ่แต่มีประสิทธิภาพดีใช้งานง่ายและที่สำคัญเป็นฟรีแวร์

การทำงานของ CutePDF Writer ต่างจาก  Ms Office ที่ใช้วิธีบันทึกเป็น PDF

เพราะ CutePDF Writer จะสร้างพริ้นเตอร์ตัวใหม่ขึ้นมา ดังนั้นลักษณะการทำงานของ CutePDF จึงเป็นการทำงานผ่านการพิมพ์

(ดูว่าติดตั้งสมบูรณ์หรือไม่ ดูที่ Control Panel สำหรับ Ms Office ก่อน 2007 และ สำหรับเวอร์ชั่น 2007 ดูใน Device and Printers )

เมื่อต้องการแปลงไฟล์เป็น PDF จึงใช้วิธีสั่งผ่านเครื่องพิมพ์ CutePDF Writer เหมือนการสั่งพริ้นต์เอกสารทั่วไป แต่โปรแกรมจะรันหน้า Save as ขึ้นมาภายหลังจากที่สั่งพิมพ์ผ่าน CutePDF Writer เราเพียงใส่ชื่อที่ต้องการบันทึกเท่านั้น และเมื่อบันทึกเสร็จ โปรแกรมอ่าน PDF ที่เราติดตั้งในเครื่องคอมพิวเตอร์ จะเปิดไฟล์ที่เราพึ่งบันทึกไปขึ้นมาอีกครั้ง ถ้าเห็นว่ายังไม่สวยงามพอ ก็แค่สั่งพิมพ์ใหม่เท่านั้น

CutePDF Writer เป็นฟรีแวร์ดาวน์โหลดจาก website ตามลิ้งค์ด้านล่าง
http://www.cutepdf.com/products/cutepdf/writer.asp

ฟังก์ชันแทนที่และตัดข้อความใน Excel SUBSTITUTE, CLEAN และ TRIM

การเอาช่องว่างและอักขระที่ไม่พิมพ์ออกจากข้อความ

บางครั้งการคัดลอกข้อมูลมาวางบน Excel worksheet หรือข้อมูลที่ได้จากการบันทึกอาจมีช่องว่างนำหน้าหรือต่อท้าย ข้อความ, วันที่ หรือ จำนวนตัวเลข ฯลฯ

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


ซึ่งงานลักษณะนี้เราสามารถใช้บริการฟังก์ชันชุด TRIM, CLEAN และ SUBSTITUTE ช่วยตัดอักขระหรือช่องว่างเหล่านี้ออก จะใช้เดี่ยวๆ หรือใช้รวมกันได้ โดยที่อาจพ่วงการใช้งานร่วมกับตัวอักขระ ASCII ซึ่งมี 255 อักขระ

Function	ไวยากรณ์และการทำงานของฟังก์ชัน TRIM, CLEAN
TRIM	=TRIM(text), text คือ ข้อความที่ต้องการเอาช่องว่างออก
	ฟังก์ชัน Trim จะเอาช่องว่างออกจากข้อความ ยกเว้นช่องว่างระหว่างคำ 1 เคาะ
CLEAN	=CLEAN(text), text คือ ข้อความที่ต้องการเอาอักขระที่พิมพ์ไม่ได้ออก
	ฟังก์ชัน Clean จะตัดออกตัวอักขระ ASCII ซึ่งถูกกำหนดให้ใช้กับฟังก์ชั่น CHAR(0)-CHAR(31)

ตัวอย่างการใช้ TRIM และ CLEAN

	A	B	C	D
1	ข้อมูล	การใช้ฟังก์ชัน	ผลลัพท์	คำอธิบายการใช้ฟังก์ชัน TRIM, CLEAN
2	Year  2013	=TRIM(A2)	Year 2013	ช่องว่างก่อนและหลัง Year ลดลง 1 ช่อง
3	Hello	=CLEAN(A3)	Hello	เป็นอักขระที่ไม่พิมพ์หายไป (ค่า ASCll = 2, 7)

Function	ไวยากรณ์และการทำงานของฟังก์ชัน SUBSTITUTE
SUBSTITUTE	=SUBSTITUTE (text,old_text,new_text,instance_num)
	ฟังก์ชัน SUBSTITUTE ใช้แทนที่ข้อความเดิม old text ด้วยข้อความใหม่ new text ส่วน instance_num ใช้เพื่อระบุตำแหน่งการแทนที่ ซึ่งถ้าไม่ระบุ old text จะถูกเปลี่ยนด้วย new text ทั้งหมด

ตารางตัวอย่างการใช้ SUBSTITUTE

	A	B	C	D
1	Old Text	New Text	การใช้ฟังก์ชัน SUBSTITUTE	ผลลัพท์
2	Retail Sales	2013	=SUBSTITUTE(A2,A2,(A2&" "&B2))	Retail Sales 2013
3	Jan 2013	January	=SUBSTITUTE(A3,"Jan",B3)	January 2013
4	1/1/2012		=SUBSTITUTE(A4,2,3,1)	1/1/3012
5	1/1/2012		=SUBSTITUTE(A4,2,3,2)	1/1/2013

อธิบายตารางตัวอย่างการใช้ SUBSTITUTE

1. ต่อท้าย Retail Sales ด้วย 2013 โดยใช้ &" "& เชื่อมคำและเว้นระหว่าง "" ก็เพื่อให้มี เคาะ 1 เคาะ ก่อน 2013
2. แทนที่ Jan ด้วย January
3. เมื่อระบุ instant_num เป็น 1 ฟังก์ชันจะแทนที่เลข 2 ตัวแรก ผลลัพท์จึงเป็น 1/1/3012
4. เปลี่ยน instant_num เป็น 2 ฟังก์ชันแทนที่เลข 2 ตัวที่สอง ผลลัพท์กลายเป็น 1/1/2013

Cut, Copy และ Paste ตัด ก๊อปปี้ และวาง ใน Excel

เทคนิค Cut, Copy, Paste ในExcel  

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

เริ่มต้นการก๊อปปี้อย่างง่ายที่สุดโดย ใช้คำสั่ง Clipboard ใต้เมนู Home ในการ Copy โดยเลือกเซลล์ที่ต้องการแล้วคลิกคำสั่งจากคลิปบอร์ด

คำสั่งหลักๆใน Clipboard คือ ตัด (Cut), คัดลอก(Copy) และ คัดลอกรูปแบบ Format Painter

		1. ย้ายเซลล์ด้วยการตัดหรือแป้นพิมพ์ลัด Ctrl+X แล้ว Enter เซลล์ที่ต้องการวาง
		2. คัดลอก อาจใช้แป้นพิมพ์ลัด Ctrl+C แล้ว Enter เซลล์ที่ต้องการวาง
		3. คัดลอกเฉพาะรูปแบบ แล้ววางบนเซลล์ที่ต้องการรูปแบบเดียวกัน

Paste คือส่วนที่มีความพิเศษ ตรงที่แบ่งการใช้งานไว้เพื่อรองรับการใช้งานของ Users

• Paste การวาง เป็นการวางเนื้อหาและการจัดรูปแบบของเซลล์ ที่คัดลอกทั้งหมด (Ctrl+V ได้เช่นกัน) มาทั้งรูปแบบ,สี,เส้น, ส่วนสูตรจะถูกเปลี่ยนตำแหน่งอ้างอิงตามเซลล์ปลายทาง
• Formulas วางเฉพาะสูตรของเซลล์ต้นทาง ไปยังเซลล์ปลายทาง
• Paste Values วางเฉพาะค่าของเซลล์ต้นฉบับที่ก๊อปปี้มา
• No Borders เหมือน Paste แต่ไม่เอาเส้นขอบ
• Transpose สลับเปลี่ยนแถวกับคอลัมน์ เปลี่ยนคอลัมน์ของข้อมูลที่คัดลอกเป็นแถว และเปลี่ยนแถวเป็นคอลัมน์ เช่น ถ้าก๊อปปี้ช่วงเซลล์ A1 ถึง A5 แล้วเลือก Transpose ที่เซลล์ D1 ผลที่คัดลอกมาจะถูกวางที่เซลล์ D1 ถึง H1 แทน
• Paste Link วางการเชื่อมโยง ซึ่งข้อมูลในเซลล์ปลางทางจะกลายเป็นสูตรลิงก์ไปยังต้นทางที่ก๊อปปี้มา เช่น ถ้าสั่งคัดลอกที่เซลล์ A4 แล้วใช้คำสั่ง Paste Link ที่เซลล์ D10 ผลที่เซลล์ D10 จะเท่ากับ =$A$4
  และถ้าคัดลอกช่วงเซลล์ A1 ถึง B2 วางที่ D1 ผลคือในเซลล์ D1 เป็นสูตร =A1  และในเซลล์ E2 เป็น =B2 เป็นต้น

Clipboard เป็นส่วนที่แสดงการคัดลอกเก่าๆ ไว้ และสามารถนำกลับมาใช้ paste ที่เซลล์อื่นๆ ได้อีก การแสดง Clipboard ใน worksheet นั้นก็ขึ้นอยู่กับการที่เราจะตั้ง options ไว้อย่างไร

ซึ่งเราสามารถเลือกเก็บหรือลบข้อมูลต่างๆ ใน Clipboard ได้ด้วยการคลิกที่ลูกศรตามรูปแล้ว Delete (ดูภาพ-วิธี Clear ข้อมูลใน Clipboard)

---

Paste Special การวางแบบพิเศษ

เป็นการวางคำสั่งคัดลอกที่มีความยืดหยุ่นมากขึ้น โดยแบ่งเป็นเรื่องๆ

Paste การวางข้อมูลที่คัดลอกมา

• All ทั้งหมด วางเนื้อหาและการจัดรูปแบบของเซลล์ต้นทางที่คัดลอกทั้งหมด
• Formulars สูตร วางเฉพาะสูตร
• Values ค่า วางเฉพาะค่าของข้อมูลในเซลล์ที่คัดลอก
• Formats รูปแบบ วางเฉพาะการจัดรูปแบบเซลล์ของข้อมูลที่คัดลอก เช่น สี รูปแบบตัวอักษร,เส้นขอบ ฯลฯ
• Comments ข้อคิดเห็น วางเฉพาะข้อคิดเห็นที่แนบมากับเซลล์ที่คัดลอก
• Validation การตรวจสอบความถูกต้อง วางกฎการตรวจสอบความถูกต้องของข้อมูลในเซลล์ที่คัดลอก ไปยังพื้นที่เซลลใหม่
• All using Source theme วางชุดรูปแบบตามต้นฉบับ วางเนื้อหาเซลล์ทั้งหมด
• All except borders ทั้งหมดยกเว้นเส้นขอบ วางเนื้อหาในเซลล์และการจัดรูปแบบทั้งหมด ในเซลล์ปลายทาง ยกเว้นเส้นขอบ
• Column widths ความกว้างคอลัมน์ วางความกว้างของคอลัมน์หนึ่งคอลัมน์หรือช่วงของคอลัมน์หนึ่งช่วงลงในคอลัมน์หรือช่วงของคอลัมน์อื่น
• Formulas and number formats สูตรและรูปแบบตัวเลข วางเฉพาะสูตรและตัวเลือกการจัดรูปแบบตัวเลขทั้งหมดจากเซลล์ที่คัดลอกเท่านั้น
• Values and number formats ค่าและรูปแบบตัวเลข วางเฉพาะค่าและตัวเลือกการจัดรูปแบบตัวเลขทั้งหมดจากเซลล์ที่คัดลอกเท่านั้น

Operation เป็นคำสั่งเสริมให้ดำเนินการทางคณิตศาสตร์ระหว่างเซลล์ต้นทางกับเซลล์ปลายทาง

• None ไม่มีการดำเนินการใดๆ
• Add บวก ข้อมูลที่คัดลอกจะบวกกับข้อมูลในเซลล์ปลายทาง
• Subtract ลบ ข้อมูลที่คัดลอกจะถูกลบจากข้อมูลในเซลล์ปลายทาง
• Multiply คูณ ข้อมูลที่คัดลอกจะถูกคูณด้วยกับข้อมูลในเซลล์ปลายทาง
• Divide หาร ข้อมูลที่คัดลอกจะถูกหารด้วยข้อมูลในเซลล์ปลายทาง
• Skip blanks ข้ามเซลล์ที่ว่าง เช่น ถ้าก๊อปปี้ช่วงเซลล์ต้นทางที่มีบางเซลล์เป็นค่าว่าง เมื่อใช้ Skip blanks เซลล์ว่างนั้นจะไม่ทับข้อมูลเดิมของเซลล์ปลายทาง
• Transpose สลับเปลี่ยนแถวกับคอลัมน์ เปลี่ยนคอลัมน์ของข้อมูลที่คัดลอกเป็นแถว และเปลี่ยนแถวเป็นคอลัมน์
• Paste Link วางการเชื่อมโยง เชื่อมโยงข้อมูลเซลล์ปลายทางไปยังข้อมูลต้นทางที่คัดลอก

การเรียกใช้ Paste Special นอกจากผ่านเมนู Home> Clipboard แล้วยังใช้ Secondary menu ได้อีก(คลิกเม้าส์ขวา)

เครื่องมือต่างๆ ใน Excel ถูกสร้างไว้รองรับให้ใช้งานให้ง่ายมากขึ้นกว่าแต่ก่อนมากด้วยฟีเจอร์ต่างๆ ที่ค่อนข้างครบถ้วน โดยเฉพาะสำหรับงานที่ต้องใช้การคำนวณ 

การใช้ Fill Command และ Auto Fill ใน Excel

การใช้ Fill Command & Auto Fill ใน Excel

ใน Editing menu ภายใต้ เมนู Home มีคำสั่งอัตโนมัติต่างๆ เช่น Auto Sum, Fill command, Clear และคำสั่งอื่นๆที่จะกล่าวถึงก็คือ คำสั่ง Fill command เป็นรูปลูกศร และ Clear ซึ่งเป็นรูปยางลบ

---

การทำงานของคำสั่งแต่ละตัวใน Fill Command

Down -	Fill down คัดลอกข้อมูลลงด้านล่าง(Ctrl+D)
Right -	Fill right คัดลอกข้อมูลไปทางขวา(Ctrl+R)
Up -	Fill up คัดลอกข้อมูลไปทางเซลล์ด้านบน
Left -	Fill left เติมข้อมูลที่เหมือนกันไปทางขวา

การทำงานของ Fill Command มีความหลากหลายโดยจะแยกเป็นเรื่องๆ ดังนี้

Fill number, ใส่ตัวเลขจาก 1 ถึง 2000

1. เลือกเซลล์ใดๆ แล้วใส่เลข 1  
2. เข้าเมนู Editing เลือก Series (รูปข้างบน) 
3. ใต้หัวข้อ Series in เลือก columns, Type เลือก Linear
4. Step value ใส่ 1, Stop value ใส่ 2000 จากนั้น ok
5. ถ้าเลือก Series in เป็น Rows ตัวเลขจะถูกเพิ่ม (Fill) ไปทางขวาตามแถว

Fill Date เติมวันที่ด้วย Fill Command

1. เลือกเซลล์ใดๆ ใส่วันที่เริ่มต้น ตามรูป 
2. คลุมพื้นที่ที่ต้องการเพิ่ม(แนวคอลัมท์หรือแถวก็ได้ แล้วเข้า Fill Command
3. คลิกเครื่องหมายถูกที่ Trend จากนั้น ok
4. การทำงานส่วนหัวข้อ Date unit
   • Day - เรียงวันที่
   • Weekday - เรียงเฉพาะจันทร์-ศุกร์
   • Month - เรียงเดือน
   • Year - เรียงปี
5. ถ้าจะเปลี่ยน Date unit ทำโดยคลุมพื้นที่ของวันที่ทั้งหมดแล้วเข้า Fill Command อีกครั้ง จากนั้นเลือก Date unit ที่ต้องการ

Auto Fill เติมข้อมูลอัตโนมัติ

1. คลุมพื้นที่เซลล์ใดๆ ตั้งแต่เซลล์ที่มีข้อมูลตัวแรกในการเพิ่มข้อมูล (แนวคอลัมท์หรือแถวก็ได้แล้วเข้าคำสั่ง Fill Command ตามรูป 
2. ใต้ Type เลือก Auto Fill จากนั้น ok
3. Auto Fill ใช้ได้ทั้งสูตร, ตัวเลข, ตัวอักษร
4. คุณสมบัติเหมือนการคัดลอก

---

นอกจากนี้ใน Excel ก็สามารถใช้ auto fill ด้วยการลากเม้าส์ ตามแนวคอลัมท์(columns) หรือแถว(rows) ในแผ่นงาน(worksheet) Excel จะแสดงเครื่องหมาย Auto fill options (ในสี่เหลี่ยมสีแดง) ที่เซลล์ปลายทางเสมอ ซึ่งเราสามารถกำหนด options ต่างๆ ได้จากตรงนี้เช่นกัน

วีดีโอแสดงการใช้ Fill command เพื่อให้เข้าใจง่ายขึ้น

ทำ Subtotal ใน Excel Worksheet

Subtotal

คือผลรวมย่อย ซึ่งฟังก์ชัน SUBTOTALจะส่งกลับผลรวมย่อยของรายการหรือฐานข้อมูล

SUBTOTAL(function_num, ref1, ref2, ...)

• function_num คือตัวเลข 1 ถึง 11 ที่ใช้เพื่อให้ Subtotal คืนค่าที่คำนวณในฐานข้อมูล
  
  
  
  1. ค่าเฉลี่ย
  2. นับจำนวน
  3. COUNTA นับจำนวนเซลล์
  4. ค่ามากที่สุด
  5. ค่าน้อยที่สุด
  6. ผลคูณ
  7. ส่วนเบี่ยงเบนมาตรฐาน
  8. ส่วนเบี่ยงเบนมาตรฐานของประชากร
  9. ผลรวม
  10. ค่าความแปรปรวน
  11. ค่าความแปรปรวนของประชากร


• ref1, ref2 ... คือ 1-254 การอ้างอิงเพื่อให้ฟังก์ชันหาผลรวมย่อยในข้อมูล

ถ้ากำหนดค่า function_num เท่ากับ 1, Subtotal จะหาค่าเฉลี่ยของค่าอ้างอิงคือ ref1- ref สุดท้ายที่ถูกกำหนดไว้ และถ้า function_num เป็น 9 ก็จะเป็นการหาผลรวมของเงื่อนไข ref ทั้งหมด ซึ่ง function_num ที่ใช้กันบ่อยคือ การหาผลรวม (9), หาค่าเฉลี่ย(1), นับจำนวน(2), หาค่ามากที่สุด (4), หาค่าน้อยที่สุด(5)

---

ตัวอย่างการใช้งาน Subtotal

1. สมมุติให้มีข้อมูล 3 ส่วน D1-D3 แต่ละส่วนมีข้อมูล 5 รายการ 
2. และใช้ Subtotal ให้คืนค่าดังนี้
   • ผลรวม -subtotal(9,(A2:A5))
   • ค่าเฉลี่ย -subtotal(1,(A2:B5))
   • ค่ามากที่สุด - subtotal(4,(A2:C5))
   • และค่าน้อยที่สุด - subtotal(5,(A2:C5))
3. เพื่อให้ใช้งานได้ยืดหยุ่นขึ้นโดยกำหนดให้ตำแหน่ง function_num เป็นเซลล์ใดๆ ตามตัวอย่างของแถวที่ 7 ซึ่งกำหนดให้ B7 เป็นเลข 9 แล้วให้สูตรดึงค่าจาก B7 มากำหนดค่า function_num

	A	B	C	D	D
1	D1	D2	D3	การใช้ Function SUBTOTAL	ผลลัพท์
2	100	30	90	=subtotal(9,(A2:A5))	350.00
3	10	130	50	=subtotal(1,(A2:B5))	76.25
4	200	20	110	=subtotal(4,(A2:C5))	250.00
5	40	80	250	=subtotal(5,(A2:C5))	10.00
6
7		9		=subtotal(B7,(B2:C5))	760.00

นอกจากการใช้ฟังก์ชันหาผลรวมย่อย ใน Excel ยังมีการใช้ Subtotal รูปแบบอัตโนมัติที่แทรกผลรวมย่อยลงในรายการได้เลย

1. เริ่มต้นใช้ Subtotal โดยไปที่เมนูแทป Data เลือก Outline (เค้าร่าง)
2. Excel จะแสดงกล่องโต้ตอบ Subtotal ในแต่ละหัวข้อต้องกำหนดเงื่อนไขเพื่อให้ Subtotal คืนค่าตามแบบที่ต้องการ
   • At each change in: เลือกคอลัมท์ที่ต้องการจัดกลุ่ม ในที่นี้เป็น Category
   • Use function: เลือกใช้ฟังชันก์ใดฟังก์ชันหนึ่งในกลุ่ม Subtotal
   • Add subtotal to: ต้องการแทรกผลรวมย่อยที่คอลัมท์ใดบ้าง
   • Replace current subtotals แทนที่ผลรวมย่อยเดิม (ถ้ามี)
   • Page break between groups แทรกตัวแบ่งหน้าของแต่ละกลุ่มซึ่งในที่นี้ถ้ากำหนดไว้ เส้นแบ่งหน้าจะแบ่งทุก Category
   • Summary below data ผลรวมของข้อมูลทั้งหมด หรือ Grand total




3. จากนั้น ok
4. หน้าตาของเวิร์คชีทจะเปลี่ยนไปโดยเพิ่มพื้นที่ด้านซ้ายขึ้นแยกเป็นสามส่วนโดยที่สามารถคลิกเพื่อดู detail ของรายงานในรูปแบบต่างๆ คือ
   • 1 : เมื่อคลิกที่ 1 ฟังก์ชันจะรวบรายงานเหลือผลของรายงานทั้งหมด Grand total, Grand Average ฯลฯ
   • 2 : รายงานจะแสดงเพียงกลุ่มที่กำหนดไว้ที่ At each change in:
   • 3 : แสดงรายการทั้งหมดที่แสดงผลรวมที่แทรกไว้ด้วย

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

ข้อดีของการทำ Subtotal คือสามารถย้อนสู่สภาพเดิมได้ โดยไปที่ Data แทป เลือก Outline ไปที่ Subtotal เหมือนเดิม จากนั้นให้เลือก Remove all

---

อีกนิดนึงก่อนจบ Excel Worksheet สามารถตั้งให้บอกค่าเฉลี่ย, จำนวนเซลล์, ยอดรวมได้  ทุกครั้งที่เราเลือกพื้นที่เซลล์ โดยการตั้งค่าที่ Customize Status Bar (คลิกเม้าส์ขวาที่ Status Bar)

อย่างภาพตัวอย่างนี้ เลือกคลุมพื้นที่ของคอลัมท์ Stock level แถวที่ 15 ถึงแถวที่ 21 เมื่อดูที่ Status Bar แสดงค่าเฉลี่ย (average) เท่ากับ 11.85714286 ซึ่งได้จากผลรวม (sum) ของช่วงเซลล์ที่เลือกซึ่งคือ 83 หารด้วยจำนวนเซลล์ (count) ที่มีข้อมูลคือ 7 เซลล์

วีดีโอการใช้ Subtotal ใน Excel Worksheet จะทำให้เข้าใจง่ายขึ้น