การเปลี่ยนนามสกุล File ด้วย Macro

............................................................................

สูตรใน Macro
 
    Dim strSourceFile As String
    Dim strSourceDirectory As String
    Dim counter As Long
    Dim FolderPath As String
    FolderPath = Application.ActiveWorkbook.path

    strSourceDirectory = FolderPath & "\New folder\"
    strSourceFile = Dir(strSourceDirectory & "new" & ".xlsx")
    Do While strSourceFile <> ""
        Name strSourceDirectory & strSourceFile As strSourceDirectory & Replace$(strSourceFile, ".xlsx", ".csv")
        counter = counter + 1
        strSourceFile = Dir()
    Loop

............................................................................

Save File ด้วย Macro

............................................................................

สูตรใน Macro

    Dim FolderPath As String
    FolderPath = Application.ActiveWorkbook.Path

    ActiveWorkbook.SaveAs Filename:= _
        FolderPath & "\2.xlsm", FileFormat:=52, CreateBackup:=False
               
    ActiveWorkbook.SaveAs Filename:= _
        FolderPath & "\2.xlsx", FileFormat:=51, CreateBackup:=False

    ActiveWorkbook.SaveAs Filename:= _
        FolderPath & "\2", FileFormat:=xlCSV, CreateBackup:=False

............................................................................

กำหนดตำแหน่งใน Sheet Excel ด้วย Macro

............................................................................

สูตรใน Macro แบบที่ 1

Sheets("Sheet3").Range("A1").Select

ActiveCell.Value = "1"

 ............................................................................

แปล

เริ่มต้นเขียน Macro

............................................................................

 1. หลังจาก กด Alt+F11 เพื่อเข้าหน้าจอสำหรับเขียน Macro ให้เลือกตามรูป

สร้างปุ่มไว้ Run Macro , Assign Macro และ เรียกใช้งาน

............................................................................

วิธีสร้างปุ่มไว้ Run Macro
1. เลือกตามรูป  สามารถสร้างปุ่มรูปแบบใดก็ได้

เขียนสูตร Macro Excel ด้วยการ Record Macro

............................................................................

การเขียนสูตร Macro Excel ง่ายๆ ด้วยการใช้การ Record Macro

ตัวอย่าง  ต้องการ พิมพ์ a ใน Cell A1 , พิมพ์ b ใน Cell B1 , พิมพ์ c ใน Cell C1

1. เริ่มเข้าใช้งาน Record Macro

เลือก View --> Macros --> Record Macro

Macro Excel คืออะไร

............................................................................

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

โดยขั้นตอนการใช้งาน Macro Excel จะเริ่มจาก
1. เขียนสูตร...การเขียนสูตร Marco สามารถทำได้ 2 แบบ คือ เขียนในรูปแบบ Visual Basic หรือ ใช้ปุ่ม Record ของ Macro
2. สร้างปุ่มไว้ Run Macro
3. Assign Macro ลงในปุ่มที่เราสร้างขึ้น
4. เรียกใช้งาน Macro โดยการกดปุ่มที่เราสร้างขึ้น
ปล. File ที่มี Macro Excel สำหรับ Excel 2007 ขึ้นไป ต้อง Save File เป็นแบบ .xlsm

การเรียกใช้งานหน้าต่างสำหรับเขียนสูตร Macro ทำได้โดยกด Alt+F11 จะปรากฎหน้าต่างตามรูป

หน้าต่างที่ไว้สำหรับเขียนสูตร คือ หน้าต่างที่ 3

 ............................................................................

การสร้างเงื่อนไขให้แสดงผล

............................................................................

สิ่งที่ต้องการ คือ ให้ Cell ตัวแปร a เป็นสีส้ม ตัวหนา  ถ้ามีค่ามากกว่า ตัวแปร b

ขั้นตอนการสร้างเงื่อนไข
1. มีข้อมูล ตัวแปร a , b

2. เลือกตำแหน่งตามรูป

การใช้ Pivot Table

............................................................................

การใช้ Pivot Table สำหรับการวิเคราะห์ข้อมูล
ตัวอย่าง มีฐานข้อมูล  a  ,  b  ,  c  ตามรูป

เรียกใช้ Pivot Table ตามขั้นตอน  1  ,  2
ขั้นตอนที่  3  เป็นการกำหนดช่วงของข้อมูล (ปกติ Excel จะเลือกให้เราเองอัตโนมัติ)
ขั้นตอนที่  4  เป็นการกำหนดการแสดงผลของ Pivot ว่าจะให้แสดงใน Sheet เดิม หรือ Sheet ใหม่  ตามตัวอย่างเป็นการแสดง Pivot ใน Sheet เดิม ที่ Cell F1

การดึงข้อมูลจาก Website

............................................................................

ขั้นตอนการดึงข้อมูลจาก Website
1. เริ่มจากมี URL ของ Website ที่ต้องการดึงข้อมูล  ยกตัวอย่าง
http://ananauo.blogspot.com/

2. เลือกตำแหน่งตามรูป

ตัดตัวอักษรด้วย Function RIGHT , LEFT , MID

............................................................................

Function RIGHT , LEFT , MID เป็นการตัดตัวอักษร ใช้ได้กับ  Text  ,  Number  และค่าที่อยู่ในรูป  Formula

สูตรใน Excel คือ  =RIGHT(a,b)

แปล  เอาตัวอักษรทางขวาของตัวแปร  a  เป็นจำนวน  b  ตัว

สูตรใน Excel คือ  =LEFT(a,b)

แปล  เอาตัวอักษรทางซ้ายของตัวแปร  a  เป็นจำนวน  b  ตัว

สูตรใน Excel คือ  =MID(a,b,c)

แปล  มีตัวแปร  a  เอาตัวอักษรลำดับที่  b  และตัวถัดไปอีกเป็นจำนวน  c  ตัว

ตัวอย่างที่ 1
ข้อมูลตัวแปร  a  อยู่ในรูปแบบ  Text  เอาตัวอักษรทางขวา จำนวน  3  ตัว

นับจำนวนตัวอักษรใน Cell ด้วย Function…LEN

............................................................................

การนับจำนวนตัวอักษรใน Cell ด้วย Function LEN สามารถใช้ได้ทั้งกับข้อมูล Text  ,  Number  และ  ค่าที่อยู่ในรูป Formula

สูตรใน Excel คือ  =LEN(a)

แปล  นับจำนวนตัวอักษรของตัวแปร  a

ตัวอย่างที่ 1
นับจำนวนตัวอักษร  โดยที่ตัวแปร  a  อยู่ในรูปแบบ  Text

แทนที่ตัวอักษรเดิมด้วย Function…SUBSTITUTE

............................................................................

 การใช้ Function SUBSTITUTE เพื่อแทนที่ตัวแปร ด้วยตัวแปรอีกตัวหนึ่ง  สามารถใช้ได้ทั้งกับข้อมูล Text  ,  Number  และ  ค่าที่อยู่ในรูป Formula

สูตรใน Excel คือ  =SUBSTITUTE(a,b,c,d)

แปล  มีตัวแปร  a  ให้แทนค่าตัวอักษร  b  ที่อยู่ใน  a  ด้วย  c  โดยมีเงื่อนไข  d
         d  ไม่ใส่ค่า  คือ  การแทนที่ตัวอักษรทั้งหมด
         d  ใส่ค่า Number  คือ  การแทนที่ตัวอักษรตัวที่เท่าไหร่

ตัวอย่างที่ 1
มีตัวแปร  a  ที่อยู่ในรูปแบบ Text   ต้องการแทนที่ตัวแปร  b  ซึ่งอยู่ใน  a  ด้วยตัวแปร  c

ค้นหาตำแหน่งด้วย Function…MATCH

............................................................................

Function MATCH เป็นการค้นหาตำแหน่งของตัวแปร ซึ่งการใช้ Function นี้จะต้องประกอบไปด้วยชุดของตัวแปรที่จะเข้าไปค้นหา และตัวแปรที่จะค้นหา

สูตรใน Excel คือ  =MATCH(a,b:b,c)

แปล  หาตัวแปร  a  ในชุดข้อมูล  b  โดยจะมีเงื่อนไข  c  ซึ่งเงื่อนไขจะมี  3  แบบ
          c  =  0   เป็นการหาตำแหน่งที่ในชุดข้อมูล  b  ต้องมีตัวแปร  a  อยู่
          c  =  1   เป็นการหาตำแหน่งที่ในชุดข้อมูล  b  ไม่จำเป็นต้องมีตัวแปร  a  อยู่
                       ข้อมูลต้องเรียงลำดับแบบน้อยไปมาก
          c  =  -1  เป็นการหาตำแหน่งที่ในชุดข้อมูล  b  ไม่จำเป็นต้องมีตัวแปร  a  อยู่
                       ข้อมูลต้องเรียงลำดับแบบมากไปน้อย

 ตัวอย่างที่ 1
ค้นหาตำแหน่ง ในชุดข้อมูล  b  โดยมีเงื่อนไข  =  0

สร้างเงื่อนไขด้วย Function…IFERROR

............................................................................

 Function IFERROR เป็นการสร้างเงื่อนไข พร้อมกับตรวจสอบ Error จากการคำนวณ

สูตรใน Excel คือ  =IFERROR(a,b)

แปล  ถ้า  a  ไม่มี Error ให้แสดงค่า  a  แต่ถ้า  a  มี Error ให้แสดงค่า  b

การใช้ Function IFERROR จะมีค่าเท่ากับการใช้ Function IF ที่มีสูตรการตรวจสอบ Error ดังนี้...

=IFERROR(a,b)     เท่ากับ     =IF(ISERROR(a),b,a)

ตัวอย่างที่ 1 , 2
เอาตัวแปร a หารด้วยตัวแปร b แล้วเช็คผล Error ถ้า Error ให้ใส่ค่า = 10

ตัดข้อมูลที่เป็นช่องว่างด้วย Function…TRIM

............................................................................

การใช้ TRIM เพื่อตัดช่องว่างทั้งหมดที่อยู่หน้า  กับ  ท้ายของข้อมูลออก  แต่ไม่สามารถตัดช่องว่างที่อยู่ตรงกลางข้อมูลได้

สูตรใน Excel คือ  =TRIM(a)

แปล  การตัดช่องว่าง บริเวณหน้า กับ ท้าย  ข้อความ a ออกทั้งหมด

ตัวอย่างที่ 1
มีช่องว่างอยู่ด้านหน้าของข้อมูล

ตัวอย่างที่ 2
มีช่องว่างอยู่ด้านท้ายของข้อมูล

ปัดเศษตัวเลขด้วย Function…ROUND , ROUNDUP , ROUNDDOWN

............................................................................

การปัดเศษตัวเลขมีอยู่ 3 แบบ คือ
1.  ROUND เป็นการปัดเศษได้ทั้งปัดขึ้น และปัดลง
     โดยมีเงื่อนไข คือ ถ้าค่า >= 5 จะปัดขึ้น  และจะปัดลง เมื่อค่า < 5
2.  ROUNDUP เป็นการปัดเศษขึ้นทุกกรณี
3.  ROUNDDOWN เป็นการปัดเศษลงทุกกรณี

สูตรใน Excel คือ  =ROUND(a,b)

        =ROUNDUP(a,b)

         =ROUNDDOWN(a,b)

แปล  ทั้ง 3 แบบ เป็นการปัดเศษตัวเลข  a  ด้วยจำนวนหลักตัว  b
         กรณีหลักหน่วย b =  0 , หลักสิบ b =  1 , ทศนิยมตำแหน่งที่ 1 ค่า b =  -1
        

ตัวอย่างที่ 1 , 2
ใช้ ROUND ปัดเศษหลักหน่วย โดยที่ตัวอย่างที่ 1 เป็นการปัดเศษขึ้น  และตัวอย่างที่ 2 เป็นการปัดเศษลง

ค้นหาข้อมูลด้วย Function…VLOOKUP , HLOOKUP

............................................................................

การใช้ Function VLOOKUP , HLOOKUP  เป็นการหาข้อมูล โดยจะมีองค์ประกอบ 2 อย่าง คือ   1. ชุดของข้อมูลที่จะค้นหา   2. ตัวแปรที่ต้องการค้นหา
ข้อแตกต่างระหว่าง VLOOKUP และ HLOOKUP คือ VLOOKUP ใช้ค้นหาข้อมูลในแนว Column ส่วน HLOOKUP ใช้ค้นหาข้อมูลในแนว Row

สูตรใน Excel คือ  =VLOOKUP(a,b:b,c,d)

                                       =HLOOKUP(a,b:b,c,d)

แปล  การค้นหาค่า  a  ในช่วงข้อมูล  b:b
         ตัว  c  เป็นตัวกำหนดว่าจะเอาข้อมูล  Row  หรือ  Column  ที่เท่าไหร่
         ตัว  d  จะมีค่าเป็น TRUE , FALSE  เป็นตัวกำหนดความคลาดเคลื่อนของข้อมูล
 

*  เนื่องจาก VLOOKUP กับ HLOOKUP มีความใกล้เคียงกันในการใช้งาน ในที่นี้จึงยกตัวอย่างมาเพียงวิธีใช้ VLOOKUP เท่านั้น

นับจำนวนด้วย Function…COUNTIFS

............................................................................

การใช้ Function COUNTIFS เป็นการนับจำนวน โดยมีเงื่อนไข    ซึ่งจะดีกว่า COUNTIF ตรงที่สามารถกำหนดเงื่อนไขในการนับจำนวนได้มากกว่า 1 เงื่อนไข

สูตรใน Excel คือ  =COUNTIFS(a:a,b,c:c,d)

แปล  นับจำนวนโดยกำหนดเงื่อนไขในช่วง  a:a  ด้วย  b  และกำหนดเงื่อนไขในช่วง  c:c ด้วย  d  โดยจะการนับจำนวนจะเกิดขึ้นต้องเข้าเงื่อนไขทั้ง 2 เงื่อนไข

ตัวอย่าง 1
กรณีตัวแปรชุดแรกเป็น  Text  มีชุดตัวแปร  a  ,  b  ให้นับจำนวน เมื่อเข้าเงื่อนไข  1  ,  2

หาผลรวมด้วย Function…SUMIFS

............................................................................

การใช้ Function SUMIFS เป็นการหาผลรวม โดยมีเงื่อนไข    ซึ่งจะดีกว่า SUMIF ตรงที่สามารถกำหนดเงื่อนไขในการหาผลรวมได้มากกว่า 1 เงื่อนไข

สูตรใน Excel คือ  =SUMIFS(a:a,b:b,c,d:d,e)

แปล  หาผลรวมในช่วง  a:a  โดยกำหนดเงื่อนไขการรวม  c  ในช่วง  b:b  และเงื่อนไขการรวม  e  ในช่วง  d:d

ตัวอย่าง 1
กรณีตัวแปรชุดแรกเป็น  Text  มีชุดตัวแปร  a  ,  b  ,  c  ให้หาผลรวมในชุดตัวแปร  c  โดยกำหนดเงื่อนไขในชุดตัวแปร  a  และ  b

สร้างเงื่อนไขด้วย Function…IF

............................................................................

การใช้ Function IF เป็นการสร้างเงื่อนไขแบบตรรกะ TRUE , FALSE

สูตรใน Excel คือ  =if(a>=b,1,0)

แปล  ถ้า a>=b เป็นจริง (TRUE) ให้ใส่ค่า 1     ถ้าไม่เป็นจริง (FALSE) ให้ใส่ค่า 0

ตัวอย่าง 1
มีตัวแปร  a  ,  b
ถ้า         a >= b        ให้แสดงค่า  1
ถ้า         b > a          ให้แสดงค่า  0

การใช้เครื่องหมายต่างๆ

............................................................................

 เครื่องหมายใน Excel ที่ใช้บ่อย ได้แก่

+  ,  -  ,  *  ,  /  ,  ^  ,  > ,  <  ,  <>  ,  =  ,  >=  ,  <=  ,  and  ,  or  ,  &  ,  " "

 ............................................................................

การ Refer ถึง Cell อื่น

............................................................................
เป็นการอ้างอิงถึงข้อมูลใน Cell อื่น
ตัวอย่าง 1...Cell D1 อ้างอิงถึงข้อมูลใน Cell A1 ใน Sheet เดียวกัน

เลือก Cell D1 แล้วพิมพ์  =a1  หรือ  =A1  ก็ได้

วิธีใช้ Copy , Paste , Find , Replace

............................................................................

วิธีใช้ Copy และ Paste ทำได้ 2 วิธี

วิธีที่ 1
1. ใช้ Mouse Click ขวา เลือก Copy

ประเภทของ File Excel

............................................................................

วิธีการ Save ทำตามรูป

............................................................................