แสดงบทความที่มีป้ายกำกับ Excel 2007 แสดงบทความทั้งหมด

วันพุธที่ 24 เมษายน พ.ศ. 2556

ตัดเศษด้วยฟังก์ชัน TRUNC กับ INT

การทำงานของฟังก์ชัน TRUNC นั้น คล้ายกับการทำงานของฟังก์ชัน INT คือ ส่งกลับค่าที่เป็นจำนวนเต็ม

ต่างกันแค่วิธีตัดเศษ ซึ่งการเลือกใช้ ก็ขึ้นอยู่ที่ว่าต้องการ รูปแบบของผลลัพท์ อย่างไร

ข้อแตกต่างของ TRUNCกับ INT

ฟังก์ชัน TRUNC จะลบจำนวน ที่เป็นเศษส่วนออกและถ้าใช้ค่าที่เป็นลบ เช่น TRUNC (-2.5) ฟังก์ชัน จะส่งกลับค่า -2
ฟังก์ชัน INT จะปัดเศษขึ้น เป็นจำนวนเต็มที่ใกล้ที่สุด โดยยึดค่าของเศษส่วน เป็นหลัก เมื่อมีการใช้ตัวเลขลบ เช่น INT( -2.5) จะส่งกลับค่า -3 เพราะเป็นตัวเลข ที่น้อยกว่า
ฟังก์ชัน TRUNC กำหนดจำนวนทศนิยมได้ ในขณะที่ ฟังก์ชัน INT ทำไม่ได้

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

=TRUNC (number,num_digits)

number คือตัวเลขที่คุณต้องการตัดเศษทิ้ง
num_digits คือจำนวนที่ใช้ระบุความแม่นยำของการตัดเศษ ซึ่งมีค่าเริ่มต้นคือ 0 (ศูนย์)

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

=INT (number)

number คือจำนวนจริงที่คุณต้องการปัดเศษเพื่อให้เหลือเป็นจำนวนเต็ม

ตารางตัวอย่าง

Row	Column
	A	B	C	D	E
1	ข้อมูล	1.2	4.8	-5.3	-8.8
2	แทนค่า INT	=INT(B1)	=INT(B1+C1)	=INT(D1/B1)	=INT(E1)
3	ผลลัพท์ INT	1	6	-5	-9
4	แทนค่า TRUNC	=TRUNC(B1-C1)	=TRUNC(C1,2)	=TRUNC(D1-C1)	=TRUNC(E1*B1)
5	ผลลัพท์ TRUNC	-3	4.80	-10	-7

อธิบายการใช้ฟังก์ชัน INT กับ ฟังก์ชัน TRUNC จากตารางข้างบน

เมื่อตัวเลขเป็นด้านบวก ทั้งสองฟังก์ชันจะตัดเศษออก และให้ผลลัพท์เหมือนกัน
ถ้าตัวเลขเป็นด้านลบ ทั้งสองฟังก์ชันจะแสดงผลที่ต่างกัน INT ปัดเศษ แต่ TRUNC ตัดเศษ
B1+C1 = 6 เมื่อผลลัพท์เป็นจำนวนเต็มก็ไม่มีการตัดเศษ ซึ่งทั้งสองฟังก์ชันจะให้ผลลัพท์เท่ากัน
INT(E1) = -9 เพราะ E1 = 8.8, ฟังก์ชัน INT ปัดเศษเป็น -8 แต่ถ้าเป็น TRUNC(E1) จะได้ผลลัพท์เป็น -9
D1/B1 = -4.41666666666667 ฟังก์ชัน INT ปัดเศษเป็นเลขจำนวนเต็มที่ใกล้ที่สุด คือ -5
B1-C1 = -3.6 ฟังก์ชัน TRUNC ตัดเศษ ให้เหลือเลขจำนวนเต็ม คือ -3
D1-C1 = -10.1 ฟังก์ชัน TRUNC ตัดเศษ ให้เหลือเลขจำนวนเต็ม คือ -10
TRUNC(C1,2) = 4.80 กำหนดทศนิยม 2 ตำแหน่งให้กับฟังก์ชัน TRUNC ถ้าไม่กำหนดผลลัพท์จะได้ 4

วันพฤหัสบดีที่ 4 เมษายน พ.ศ. 2556

การแปลงแถวเป็นคอลัมน์ Excel ด้วยฟังก์ชัน TRANSPOSE

ฟังก์ชัน TRANSPOSE

คือคำสั่งให้ส่งกลับช่วงของเซลล์จากแนวตั้งเป็นแนวนอน หรือ จาก แนวนอนเป็นแนวตั้ง และการใช้ฟังก์ชัน TRANSPOSE นั้นต้องป้อนเป็นสูตรอาเรย์(Array)

หมายถึงต้องปิดสูตรด้วย
CTRL+SHIFT+ENTER
เช่น ช่วง A1 ถึง B10 ใน formula bar จะแสดงเป็น {=TRANSPOSE(A1:B10)}
และสิ่งสำคัญคือ จำนวนแถวกับคอลัมน์ ของตารางใหม่ ต้องเท่ากับตารางแรกเสมอ

ไวยากรณ์

=TRANSPOSE (array)

array หรือช่วงของเซลล์ บนแผ่นงาน(worksheet) ที่ต้องการสลับเปลี่ยนแถว (row)กับคอลัมน์ (column)

วิธีใช้ฟังก์ชัน TRANSPOSE

เปิด Ms Excel ในตัวอย่างนี้ต้องการทำ TRANSPOSE จาก Data sheet ไปยัง Output sheet

คลุมพื้นที่ทั้งหมดของตารางในชีท Data (คือช่วง A2 ถึง H10)

คัดลอก หรือกด CTRL+C แล้วไปที่ชีท Output ในตัวอย่างนี้วาง cursor ที่ A2

ใช้ shortcut ALT+E+S

เพื่อเรียกกล่องโต้ตอบ Paste Special

เลือกวางเฉพาะ Format กับ Transpose

กดคีย์ลัดT+E และ okจะเห็นเพียง

สีของกรอบตารางเดิมถูกคัดลอกมา

Using Paste Special Formats and transpose to set range of new table

พิมพ์ =TRANSPOSE(Data!A2:H10) แล้วปิดสูตรด้วย CTRL+SHIFT+ENTER หรือทำให้ง่ายขึ้นก็คือ เมื่อพิมพ์ถึงเครื่องหมายวงเล็บเปิด "(" แล้วคลิกที่ไปที่แทปชีท Data แล้วคลิกเลือกพื้นที่ช่วง A2 ถึง H10 จากนั้นกดคีย์ CTRL+SHIFT+ENTER ปิดสูตรได้เหมือนกัน

ข้อสังเกตุการใช้ฟังก์ชัน TRANSPOSE

ตารางใหม่ที่แทนค่าด้วยฟังก์ชัน TRANSPOSE จะคืนค่าตามการเปลี่ยนแปลง ของตารางแรกเสมอ มีลิงก์กับตารางแรก

เมื่อ TRANSPOSE ทำหน้าที่สลับเปลี่ยนแถวกับคอลัมท์ สิ่งทึ่ต้องคำนึงถึงก่อนสิ่งอื่น นั่นคือ ขนาดของพื้นที่เซลล์ที่ใช้ฟังก์ชัน ต้องผกผันตามตารางแรกด้วย เช่น ตารางแรกมี 4 แถว 5 คอลัมท์ พื้นที่ที่ต้องใช้สำหรับวางฟังก์ชัน TRANSPOSE ก็ควรมี 5 แถว กับ 4 คอลัมท์

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

ใช้คีย์ลัด ALT+E+S เพื่อเรียกกล่องโต้ตอบ Paste Special จากนั้นเลือกกดคีย์ T+E และok (คีย์ลัดการวางเฉพาะรูปแบบ+สลับแถวกับคอลัมท์ ของ Microsoft Excel 2003 ซึ่งยังคงใช้ได้ใน 2007 )

คีย์ลัดเรียกคำสั่ง Paste Special ของ 2007 ก็มีแต่จะยาวกว่า 2003 คือ ALT+H+V+S จากนั้นก็กด T+E+ok

การจะใช้คีย์ลัด ALT+E+S หรือ ALT+H+V+S เพื่อเรียกคำสั่งวางแบบพิเศษ (Paste Special) นั้นต้องมีการเลือก การคัดลอก หรือ CTRL+C ก่อนเสมอ

วีดีโอแสดงการใช้ฟังก์ชัน TRANSPOSE เพื่อความเข้าใจมากขึ้น

วันอังคารที่ 26 มีนาคม พ.ศ. 2556

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

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

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

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

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

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

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

เมื่อกลับมาที่หน้า 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

บันทึก

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

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

เข้าหัวข้อ 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

วันศุกร์ที่ 8 มีนาคม พ.ศ. 2556

การคูณตัวเลขใน Excel ด้วยสูตร PRODUCT กับ SUMPRODUCT

หลากหลายวิธีการคูณใน Excel

การคูณใน Excel จะใช้เครื่องหมายดอกจัน (*) หรือ asterisk ในการหาผลคูณ เช่น 2*3 = 6
นอกจากการใช้เครื่องหมาย * แล้วสามารถใช้ฟังก์ชัน PRODUCT หรือ SUMPRODUCT ให้คืนค่าผลคูณได้

ทั้งสองฟังก์ชัน มีลักษณะการทำงานที่ต่างกัน แต่มีฟีเจอร์ที่ครอบคลุมเรื่องการคูณได้กว้างกว่า การใช้ asterisk หรือ ดอกจัน(*)

ไวยากรณ์ของฟังก์ชัน PRODUCT

PRODUCT(number1,number2,...)

number1, number2 .. เป็นตัวเลข 1 ถึง 255 จำนวน ที่ต้องการคูณ

เปรียบเทียบการคูณใน Excel ด้วยการใช้ * (asterisk) กับ ฟังก์ชัน PRODUCT

	A		B	C	D	E	F	G	H
1	Line 1		1	2	3	4	3	2	1
2	Line 2		2	3	4	3	2	1	2
3	Line 3		2	3	4	3	2	1	2
4	เปรียบเทียบการคูณ Line 1กับ Line 2 ด้วย (*) กับ PRODUCT
5	ใช้ * (asterisk)เป็นตัวคำนวณ		=B1B2C1C2D1D2E1E2F1F2G1G2H1*H2
6	ฟังก์ชัน PRODUCT	number อยู่ติดกัน	=PRODUCT(B1:H2)
6	ฟังก์ชัน PRODUCT	number ต่างชีท+ไม่ติดกัน	=PRODUCT(Sheet11!B1:H1,Sheet11!B3:H3)
8	ผลลัพท์คำนวณทั้งสองวิธี		=41472

ข้อสังเกตุช่วยให้เข้าใจการใช้ฟังก์ชัน PRODUCT ได้ง่ายขึ้น

ฟังก์ชัน PRODUCT ลดความยาวยืดเยื้อการหาผลคูณของ (*) แต่อย่างไรก็ตาม ก็อยู่ที่รูปแบบการใช้ว่ามีความเหมาะสมหรือไม่

ถ้าข้อมูลอยู่ต่าง Sheet เมื่อพิมพ์ =PRODUCT( แล้วให้เลือกคลิกไปที่ sheet ที่ต้องการคำนวณแล้วลากเม้าส์คลุมพื้นที่ทั้งหมดที่ต้องการผลคูณ

หากข้อมูลที่ต้องการคูณไม่ใช่เซลล์ที่อยู่ติดกัน ให้กด CTRL ขณะที่เลือกพื้นที่เซลล์

ไวยากรณ์ของฟังก์ชัน SUMPRODUCT

SUMPRODUCT(array1,array2,array3, ...)

array1, array2, array3 ... คืออาร์เรย์ 2 ถึง 255 ที่มีคอมโพเนนต์ที่ต้องการคูณแล้วบวก

เช่น การคำนวณราคาสินค้าต่อหน่วยของสินค้าหลายๆ ชนิดในใบเสร็จใบเดียว โดยคูณราคาสินค้ากับจำนวนที่ซื้อ แล้วนำราคารวมของสินค้าแต่ละชนิดมารวมกัน ซึ่ง SUMPRODUCT จะมีความเหมาะสมกับมากกว่าการใช้สูตร SUM แต่อาร์กิวเมนต์อาร์เรย์ที่ใช้ในฟังก์ชัน SUMPRODUCT ต้องมีขนาดเท่ากัน ถ้าไม่เท่ากัน ฟังก์ชันจะส่งกลับค่าความผิดพลาด #VALUE!

เปรียบเทียบการคูณใน Excel ด้วย * กับฟังก์ชัน SUMPRODUCT

	A	B	C	D	E	F	G	H
1	Unit Price-A	1	2	3	4	5	6	7
2	Unit Price-B	5	6	7	8	7	6	5
3	Qty-A	8	9	1	2	3	4	5
4	Qty-B	10	10	10	10	10	10	10
5	เปรียบเทียบการคูณ Unit Price กับ Qty ด้วย (*) กับ SUMPRODUCT
6	ใช้ * หาราคาสุทธิ A	=(B1B3)+(C1C3)+(D1D3)+(E1E3)+(F1F3)+(G1G3)+(H1*H3)
7	ฟังก์ชัน SUMPRODUCT	array อยู่ติดกัน		=SUMPRODUCT(B1:H1,B3:H3)
8		array ต่างชีท		=SUMPRODUCT(Data!B2:H2,Data!B4:H4)
9		รวมทั้ง A,B		=SUMPRODUCT(Data!B1:H2,Data!B3:H4)
10	ผลลัพท์การคำนวณทั้งสองวิธี	PRODUCT A=111, PRODUCT B=440, รวม=551

เทคนิคช่วยให้ใช้ฟังก์ชัน SUMPRODUCT ง่ายขึ้น

ฟังก์ชัน SUMPRODUCT ช่วยคำนวณราคาสุทธิ (Unit Price*Qty) ง่ายกว่าการใช้ (*) นอกจากนี้ยังสามารถใส่ได้ถึง 255 อาร์เรย์

จำนวน arrary ในฟังก์ชันต้องมีขนาดเท่ากัน ไม่เช่นนั้นสูตรจะคืนค่า #VALUE!

จากตาราง รวมทั้ง A,B ฟังก์ชันจะทำงานโดย จับคู่คูณต้้งแต่แถว B1*B3, C1*C3, D1*D3, E1*E3, F1*F3, G1*G3, H1*H3 และเป็นแบบเดียวกันระหว่าง B2-H2 กับ B4-H4

ข้อมูลต่าง Sheet เมื่อพิมพ์สูตร =SUMPRODUCT( แล้วให้เลือกคลิกไปที่ sheet ที่ต้องการคำนวณ แล้วลากเม้าส์คลุมพื้นที่ทั้งหมดที่ต้องการ

หากข้อมูลที่ต้องการคูณไม่ใช่เซลล์ที่อยู่ติดกัน ให้กด CTRL ขณะที่เลือกพื้นที่เซลล์

วันจันทร์ที่ 4 มีนาคม พ.ศ. 2556

การแปลงมาตราวัดด้วยฟังก์ชัน CONVERT ใน Excel

ฟังก์ชัน CONVERT

เป็นฟังก์ชันที่ใช้สำหรับแปลงหน่วยวัด จากระบบการวัดแบบหนึ่งให้กลายเป็นอีกระบบหนึ่ง

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

ไวยากรณ์ของฟังก์ชัน CONVERT

=CONVERT(number,from_unit,to_unit)

Number : คือค่าใน from_units ที่จะแปลง
From_unit : คือหน่วยของตัวเลขที่ต้องการให้แปลง
To_unit คือหน่วยที่ต้องการแปลง และฟังก์ชัน CONVERT จะยอมรับค่าที่ตรงกับรูปแบบที่ Excel กำหนดไว้เท่านั้น (ในเครื่องหมายคำพูด "xx") สำหรับ from_unit และ to_unit

	A	B	C	D	E	F
2	เปลี่ยนจาก	Number	From_unit	To_unit	แสดงการใช้ฟังก์ชัน	ผลลัพท์
3	กิโลกรัม(kg) เป็น กรัม(g)	1	kg	g	=CONVERT(B3,C3,D3)	1000
4	เซนติเมตร(cm) เป็น ฟุต(ft)	169	cm	ft	=CONVERT(B4,C4,D4)	5.544619
5	แกลลอน(gal)เป็น ออนซ์(oz)	1	gal	oz	=CONVERT(B5,C5,D5)	128
6	ชั่วโมง(hr) เป็น วัน(day)	100	hr	day	=CONVERT(B6,C6,D6)	4.166667
7	ออนซ์(oz)เป็นปอนด์(lbm)	16	oz	lbm	=CONVERT(B7,C7,D7)	#N/A
8	ตร.ฟุต(ft)เป็น ตร.เมตร(m)	4	ft	m	=CONVERT(B8,C8,D8),C8,D8	1.2192

ข้อสังเกตเงื่อนไขการใช้ฟังก์ชัน

ถ้า Number ไม่ใช่ตัวเลข ฟังก์ชัน CONVERT จะส่งกลับ #VALUE! เป็นค่าความผิดพลาด
ฟังก์ชัน CONVERT จะส่งกลับ #N/A เป็นค่าความผิดพลาด ด้วยเหตุผลต่างๆ ดังนี้
1. ชื่อหน่วยที่แทนค่าในฟังก์ชันไม่ตรงกับหน่วยของฟังก์ชัน Convert
2. หน่วยที่แทนค่าในฟังก์ชันไม่มี
3. หน่วยที่ใช้แปลงอยู่ต่างกลุ่มกัน (F7)
4. ตัวพิมพ์ใหญ่-เล็กของชื่อย่อหน่วยมีผลต่อการส่งกลับค่าของฟังก์ชัน Convert ด้วย

รหัสที่ใช้แทนค่า from_unit หรือ to_unit ในฟังก์ชัน Convert

ประเภทหน่วยวัด	ชื่อหน่วยวัด	รหัสในฟังก์ชัน
มวลหรือน้ำหนัก	กรัม (gram)	"g"
	กิโลกรัม (kilogram)	"kg"
	มวลออนซ์ (ounce)	"ozm"
	ปอนด์ (pound=16 ounces)	"lbm"
ระยะทาง	เมตร (meter)	"m"
	ไมล์ (mile)	"mi"
	ไมล์ทะเล (mileage)	"Nmi"
	นิ้ว (inch)	"in"
	เซนติเมตร (centimeter)	"cm"
	ฟุต (foot)	"ft"
	หลา (yard)	"yd"
เวลา	ปี (year)	"yr"
	วัน (day)	"day"
	ชั่วโมง (hour)	"hr"
	นาที (minute)	"mn"
	วินาที (second)	"sec"
อุณหภูมิ	องศาเซลเซียส	"C" หรือ "cel"
	องศาฟาเรนไฮต์	"F" หรือ "fah"
	Kelvin	"K" หรือ "kel"
มาตราวัดของเหลว	ช้อนชา	"tsp"
	ช้อนโต๊ะ	"tbs"
	ออนซ์	"oz"
	ถ้วย	"cup"
	ไพนท์ Us. pint	"pt" หรือ "us_pt"
	ไพนท์ Uk. pint	"uk_pt"
	ควอร์ท (Quart)	"qt"
	แกลลอน	"gal"
	ลิตร	"l" หรือ "lt"

วันพฤหัสบดีที่ 7 กุมภาพันธ์ พ.ศ. 2556

ทำให้ Excel เร็วขึ้นด้วย Quick access toolbar

Customize Quick Access Tool Bar ใช้ Excel ได้เร็วขึ้น จากเครื่องมือด่วนบนทูลบาร์

Quick Access Tool Bar หรือแถบเครื่องมือด่วน ในหน้า Excel's Worksheet นั้นจะแสดงอยู่ ด้านล่างหรือด้านบน ของเมนูแทป

และด้วยฟีเจอร์นี้ของ Excel ช่วยให้เราสามารถเลือกคำสั่งที่ใช้บ่อยๆ มารวมกันไว้ใน Quick Access Tool Bar หรือแถบเครื่องมือด่วน เพื่อให้ใช้งานได้เร็วขึ้นและง่ายขึ้น ลดขั้นตอนการเรียกคำสั่งที่ซับซ้อนให้เหลือเพียงแค่คลิกเดียว

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

Excel's menu tap and Quick access toolbar

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

การใช้คีย์ลัดสำหรับ แถบเครื่องมือด่วน (Quick Access Tool Bar) ก็จะเปลี่ยนแปลงไปตามตำแหน่งที่เราวางคำสั่งนั้นไว้ซึ่งถ้าหากจะใช้ตามมาตรฐาน Shortcut ของ Excel ก็ใช้ได้หรือ จะใช้เรียกผ่านแถบเครื่องมือด่วนก็ได้เช่นกัน

Press Alt - to use Quick access toolbar code

คำสั่ง "ตัวอย่างก่อนพิมพ์" หรือ Print Preview ใช้ หากจะใช้ Ctrl+F2 ก็ได้เหมือนกัน	Alt	1
คำสั่ง "Auto Sum" ซึ่งอยู่ลำดับที่ 11 ตำแหน่งที่ 08 ใช้	Alt	08
คำสั่ง "Erase border" ลำดับที่ 22 เป็น 0d (ตัวพิมพ์ใหญ่เล็กไม่มีผล)	Alt	0D

การปรับแต่งแถบเครื่องมือทูลบาร์

ให้คลิกเม้าส์ขวาที่เมนูแทป
เลือก Customize Quick Access Toolbar
จะเข้าสู่หน้า Excel Options

เลือก Customize ซึ่งอยู่ในแถบด้านซ้ายสุด
ใต้ Choose Commands from: สามารถเลือกคำสั่งจากกลุ่มต่างๆนอกจาก Popular Commands ได้ เช่น Home tab, All Commands
เมื่อเลือกคำสั่งที่ต้องการได้แล้วให้กดปุ่ม Add เพื่อเพิ่มรายการคำสั่งลงในลิสต์ Customize Quick Access Toolbar ด้านขวา
และใต้หัวข้อ Customize Quick Access Toolbar ก็สามารถกำหนดให้ใช้แถบเครื่องมือกับไฟล์งาน Excel ทั้งหมด หรือเฉพาะไฟล์งานปัจจุบัน
ถ้าต้องการเปลี่ยนแปลงคำสั่งใน Customize Quick Access Toolbar
- ปุ่ม Remove ใช้ลบคำสั่งที่ไม่ต้องการออก
- ปุ่ม Move up กับ Move down ใช้เลื่อนคำสั่งให้อยู่ในตำแหน่งที่ต้องการ
จากนั้น ok

เมื่อกำหนด Quick Access Toolbar เสร็จแล้วก็สามารถแก้ไขคำสั่งเพิ่มเติมได้

เพิ่มคำสั่งพื้นฐาน หรือ เอาบางรายการออกได้ เช่นคำสั่ง New, Open ฯลฯ
กำหนด Show Above the Ribbon จะแสดง Quick Access Toolbar บนเมนูแทป (จากรูปได้เลือกให้แสดงใต้เมนูแทปไว้)
More Commands เป็นตัวเลือกเพื่อเข้าไปแก้ไข/เพิ่ม/ลดคำสั่งอื่นๆ ในหน้า Excel Options

วันพุธที่ 14 พฤศจิกายน พ.ศ. 2555

การใช้ IF กับ INT หาเงินทอนแบบแยกแบงค์แยกเหรียญ

การใช้ฟังก์ชัน IF กับ INT คำนวณเงินทอนหรือแลกเงินด้วยจำนวนธนบัตรและเหรียญเท่าที่จำเป็น

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

จากตัวอย่างรูป Figure A เป็นการ
หาเงินทอนแยกแบงค์แยกเหรียญ ด้วยโจทย์ดังนี้

ยอดรับเงิน 2,000 (เซลล์ C2)
หักค่าสินค้า 1,067.50 (เซลล์ D2)
เหลือเงินที่ต้องทอนให้ลูกค้า 932.75 (เซลล์ E2)

Figure A

จุดประสงค์ของการคำนวณเงินทอนก็เพื่อใช้คัดแยกเหรียญและธนบัตรให้เหลือจำนวนที่น้อยที่สุดในการทอนเงินแต่ละครั้ง ดังนั้นสูตรที่ใช้จึงเกี่ยวพันกันทุกประเภทเหรียญ/ธนบัตร และเนื่องจากหน่วยเงิน 1000 บาท เป็นรายการสุดท้ายของตาราง ดังนั้นสูตรชุดนี้จึงเหมือนกับรันย้อนขึ้นจากด้านล่าง

ฟังก์ชันที่ใช้กับตารางนี้มี 2 ฟังก์ชัน

ฟังก์ชัน IF กำหนดเงื่อนไข
ฟังก์ชัน INT ปัดเศษทศนิยมออก ไวยากรณ์ INT คือ INT(number)

Logic การใช้ IF ของสูตรชุดนี้ คือ ถ้าเงินที่ต้องทอนหักหน่วยเงินแล้วมากกว่า, เป็นจริง-ใช้ INT ปัดเศษผลลัพท์ จากเงินทอนรวม หักหน่วยเงินที่ใหญ่กว่า แล้วจึงคูณหน่วยเงิน ,ไม่จริง-เป็นศูนย์

Row	Column
	B	C	D	E	F
1		รับเงิน	ค่าสินค้า	เงินทอน
2		2,000.00	1,067.25	932.75

ส่วนต่างๆ ของตารางแสดงสูตร โดยตำแหน่งคอลัมท์และแถวที่ใช้ (กำหนดจาก FigureA)

หน่วยเงิน (column C)แยกประเภทของเหรียญและธนบัตร เช่น เหรียญ 25 สตางค์, เหรียญ 5 บาท, ธนบัตรใบละ 50 บาท, ธนบัตรใบละ 100 บาท เป็นต้น
สูตรในคอลัมท์เงินทอน แสดงการใช้ฟังก์ชัน ของ column E
เงินทอน/บาท(column E) แสดงผลลัพท์ที่คำนวณได้

	B	C		E	F
3	หน่วยเงิน		สูตรในคอลัมท์เงินทอน	เงินทอน/ บาท	จำนวน
4	สต.	0.25	=IF($E$2-$E5>$C4,INT(($E$2-SUM($E5:$E$14))/C4)*$C4,0)	0.25	1
5	สต.	0.50	=IF($E$2-$E6>$C5,INT(($E$2-SUM($E6:$E$14))/C5)*$C5,0)	0.50	1
6	บาท	1	=IF($E$2-$E7>$C6,INT(($E$2-SUM($E7:$E$13))/C6)*$C6,0)	0
7	บาท	2	=IF($E$2-$E8>$C7,INT(($E$2-SUM($E7:$E$14))/C7*$C7,0)	2	1
8	บาท	5	=IF($E$2-$E9>$C8,INT(($E$2-SUM($E9:$E$14))/C8)*$C8,0)	0
9	บาท	10	=IF($E$2-$E10>$C9,INT(($E$2-SUM($E10:$E$14))/C9)*$C9,0)	10	1
10	บาท	20	=IF($E$2-$E11>$C10,INT(($E$2-SUM($E11:$E$14))/C10)*$C10,0)	20	1
11	บาท	50	=IF($E$2-$E12>$C11,INT(($E$2-SUM($E12:$E$14))/C11)*$C11,0)	0
12	บาท	100	=IF($E$2-$E13>$C12,INT(($E$2-SUM($E13:$E$14))/C12)*$C12,0)	400	4
13	บาท	500	=IF($E$2-$E14>$C13,INT(($E$2-SUM($E14:$E$14))/C13)*$C13,0)	500	1
14	บาท	1000	=IF($E$2>$C14,INT($E$2/$C14)*$C14,0)	0
15		รวมเงินทอน		932.75	10

จากภาพ Figure A และตารางตัวอย่าง
column F เป็นผลคำนวณหาจำนวนเหรียญ/ธนบัตร ที่ต้องทอนให้กับลูกค้า ในขณะที่ column E เป็นการคำนวณหาเงินทอนตามมูลค่าของเหรียญ/ธนบัตร ความแตกต่างของสองคอลัมท์นี้ มีเพียงแค่ตัวคูณของหน่วยเงินเท่านั้น คือ ตัวคูณมูลค่าของเหรียญหรือ ธนบัตร (ในที่นี้คือ คอลัมท์ C)
หมายเหตุ

ในเซลล์ E4 : =IF(E2-E5>C4,INT((E2-SUM(E5:E14))/C4)*C4,0)
ในเซลล์ F4 : =IF(E2-E5>C4,INT((E2-SUM(E5:E14))/C4),0)
ในคอลัมท์ C ที่เป็นค่าของเหรียญหรือธนบัตร หากใช้ custom format cells เราสามารถใส่หน่วย "สต." หรือ "บาท" ได้พร้อมทั้งแทนค่าในสูตรด้วยได้เลย โดยไม่ต้องแยกหน่วยที่คอลัมท์ B
ตารางที่แสดงด้านล่างได้เพิ่มหน่วยเงิน 2 บาทเข้าไป

วันจันทร์ที่ 29 ตุลาคม พ.ศ. 2555

ทำตัวพิมพ์ใหญ่-พิมพ์เล็กด้วยฟังก์ชัน UPPER, PROPER, LOWER ใน Excel 2007

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

UPPER, PROPER, LOWER

คือชุดฟังก์ชันก์ที่พูดถึง หน้าที่คือเปลี่ยน Text ให้เป็นตัวพิมพ์ใหญ่ หรือ ตัวพิมพ์เล็ก แต่ละตัวทำงานคล้ายกัน แต่คืนค่าต่างกัน

รูปแบบฟังก์ชัน

UPPER(text)	: แปลงข้อความใน (text) ไปเป็นตัวพิมพ์ใหญ่
PROPER(text)	: เปลี่ยนตัวอักษรตัวแรกในแต่ละคำใน (text) เป็นตัวพิมพ์ใหญ่ และอื่นเป็นตัวพิมพ์เล็ก
LOWER(text)	: แปลงข้อความใน (text) เป็นตัวพิมพ์เล็ก

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

ส่วนแรกใช้ฟังก์ชันกับ Text
ส่วนถัดไป สมมุติให้ A6 เป็นประโยค you must be the change You wish to see in the World. และจะใช้ประโยคนี้กับฟังก์ชันทั้งสาม

Row	Column
	A	B	C
1	ฟังก์ชันที่ใช้	แทนค่า	ผลลัพท์
2	UPPER(text)	UPPER ("Mahatama Gandhi")	MAHATAMA GANDHI
3	PROPER(text)	PROPER ("good manufacturing practice")	Good Manufacturing Practice
4	LOWER(text)	LOWER ("Swimsuit-Shopping Demons")	swimsuit-shopping demons
5
6	you must be the change You wish to see in the World.
7	UPPER(A6)	YOU MUST BE THE CHANGE YOU WISH TO SEE IN THE WORLD.
8	PROPER(A6)	You Must Be The Change. You Wish To See In The World.
9	LOWER(A6)	you must be the change. you wish to see in the world.

การใช้งานฟังก์ชันชุดนี้ก็ขึึ้นอยู่กับว่าเหมาะที่จะใช้กับข้อความแบบไหน

วันศุกร์ที่ 26 ตุลาคม พ.ศ. 2555

การใช้ Formatting Styles ใน Excel 2007

การจัดรูปแบบ หรือ การ Formatting ใน Excel คือการใช้รูปแบบที่แตกต่าง เช่น สี, เวลา, สัญลักษณ์ช่วยกำหนด เพื่อให้เราสามารถแยกแยะค่าในเซลล์ให้ง่ายขึ้น และใน Excel 2007 ทำให้การจัดรูปแบบ หรือ Format ง่ายขึ้นด้วยเครื่องมือที่กึ่งสำเร็จรูป

วิธีเรียกใช้ Format แบบกึ่งสำเร็จรูป ใน Excel 2007

Formatting Style ใน Excel 2003 อยู่ในเมนู Format แต่ใน Excel 2007 กลับถูกย้ายไปอยู่ที่เมนู Home ในเมนูย่อย Styles เมื่อคลิกเข้าไปก็จะห็นว่ามีรูปแบบให้เลือกอยู่ 3 รูปแบบ มาดูกันต่อว่าเราจะทำอะไรได้บ้าง

Conditional Formatting การจัดรูปแบบด้วยการระบุเงื่อนไขแต่ก็มี 5 แบบแรกที่มีลักษณะคล้ายกับการจัดรูปแบบอัตโนมัติ

A) Highlight Cells Rules ใช้เพื่อไฮท์ไลท์เซลล์ใน Table ที่เลือกเป็นไปตามเงื่อนไขที่กำหนด เช่น มากหรือน้อยกว่า,ค่าซ้ำ ฯลฯ
	B) Top/Bottom Rules ใช้เพื่อเลือกค่า Top 10 หรือ Bottom 10 ใน Table
C) Data Bars แยกแยะค่าในเซลล์ ความยาวหรือสั้นของ Color Bars เป็นไปตามค่าที่อยู่ในเซลล์ สีที่ยาวกว่าจะแทนค่าที่สูงกว่า

D) Color Scales จะแสดงสองหรือสามสี เพื่อแทนค่าในตาราง

E) Icon Sets มีลักษณะคล้ายกับ ตัวเลือกข้างต้นเพียงแต่เป็นรูป Icon ต่างๆ

ถัดไปคือ New Ruleใช้เพื่อให้เราตั้งเงื่อนไขเอง ซึ่งเงื่อนไขที่ใช้อาจเป็นค่าคงที่หรือฟังก์ชันก็ได้
Clear Rules เป็นการยกเลิกรูปแบบของ Conditional Formatting ทั้งหมด
Manage Rules มีไว้เพื่อจัดการกับ Condition ทั้งหมด ทั้งใหม่และเก่า เช่น แก้ไข เพิ่ม หรือ ลบ

Video แสดงวิธีใช้ Conditional Formatting ด้วย New Rule ที่กำหนด Rule Type ด้วย Format only cells that contain หรือใช้ค่าในเซลล์เป็นตัวกำหนด

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

หรือ เพิ่ม Format ใหม่ได้ ซึ่งสามารถทำได้ 2 วิธี
- เพิ่มรูปแบบ Table ด้วย New Table Style
- เพิ่มรูปแบบเพื่อใช้กับ PivotTable Style

Cell Styles - จัดรูปแบบเซลล์อัตโนมัติ ใช้งานง่ายมาก เพียงแค่เลือกคลุมพื้นที่เซลล์ที่ต้องการแล้วเลือกรูปแบบที่ Microsoft Excel ให้มา

หรือ เพิ่มแบบใหม่ซึ่งทำได้ 2 วิธี
- เพิ่มแบบใหม่ๆ ที่ไม่มีในลิสต์ด้วยเมนู New Cell Style
- ใช้รูปแบบจาก Workbook อื่นๆ ด้วย Merge Styles (ต้องเปิดไฟล์ที่จะใช้ merge ไว้ด้วย)

Video แสดงการใช้ Cell Styles กับ Format as Table

วันเสาร์ที่ 13 ตุลาคม พ.ศ. 2555

การใช้ Data Validation ทำ 2 Drop down lists ด้วย INDIRECT

ลักษณะการทำงานของ INDIRECT มันคล้ายกับการแข่งแรลลี่ หาอาร์ซีที่ซ่อนอยู่ตามจุด เพื่อแกะรอยเดินทางต่อ มันคล้าย INDIRECT ซ้อน INDIRECT ซ้อนกันจนถึงจุดฟินิช

ฟังก์ชัน INDIRECT ทำหน้าที่ส่งกลับค่าอ้างอิงที่อยู่ในเซลล์ ด้วยการระบุตำแหน่งของเซลล์ (Column Letter,Row Number)

และยังสามารถอ้างอิงไปยังภายนอก หรือสมุดงานอื่นได้ ทำให้ปรับใช้ได้กับฟังก์ชันอื่นๆ ได้อีกหลายฟังก์ชัน

เช่น ใช้ร่วมกับ Data Validation ในการสร้าง Drop down lists 2 คอลัมน์

ไวยากรณ์และความหมาย

=INDIRECT(ref_text, a1)

ref_text คือเซลล์อ้างอิง ซึ่งถ้าการอ้างอิงไม่ใช่เซลล์ที่ถูกต้อง ฟังก์ชัน INDIRECT จะส่งกลับค่าความผิดพลาด #REF! และ หากมีการอ้างอิงไปยังสมุดงานอื่น(การอ้างอิงภายนอก) สมุดงานที่เชื่อมโยงกันต้องเปิดอยู่ ไม่เช่นนั้นฟังก์ชันก็จะส่งค่าความผิดพลาดเช่นกัน
1. ถ้า a1 เป็น TRUE หรือถูกละเว้น ref_text จะถูกแปลว่าเป็นการอ้างอิงลักษณะ A1
2. ถ้า a1 เป็น FALSE แล้ว ref_text จะถูกแปลว่าเป็นการอ้างอิงลักษณะ R1C1

ข้อจำกัดของ INDIRECT
1. เซลล์ที่เป็น ref_text สูตรจะไม่อ้างอิงที่เซลล์เดิมถ้ามีการ ตัด(cut), ลบ(Delete) หรือแทรกแถว หรือคอลัมน์ (Insert) ซึ่งทำให้เซลล์เปลี่ยนตำแหน่ง
2. ถ้าต้องการอ้างอิงที่เซลล์เดิม ตลอดไปไม่ว่าแถวที่อยู่บนเซลล์จะถูกลบหรือเซลล์นั้นจะถูกย้ายไป ให้ใช้ " " เพิ่มในตัวสูตร (=INDIRECT("ref_text"))
3. สมมุติในเซลล์ A9 เป็นค่าว่าง และเมื่อแทนค่า =INDIRECT("A9") สูตรจะคืนค่า 0 แทน #REF!

ตาราง1 คอลัมท์ A2-C6 เป็นโจทย์ และ D1-F7 จะแสดงฟังก์ชันเพื่อแสดงคุณสมบัติพื้นฐานของ INDIRECT

Row	Column
	A	B	C	x	D	E	F
1	DataA	DataB	DataC		ข้อ	แสดงการใช้ Function	ผลลัพท์
2	B4	200	B3		1	=INDIRECT(A2)	A2
3	C4	1.82	99		2	=INDIRECT(B2)	#REF!
4	C3	A2	700		3	=INDIRECT(A3)	700
5	2	3000	A4		4	=INDIRECT(C5)	C3
6					5	=INDIRECT(INDIRECT(C5))	99
7					6	=INDIRECT("B"&ROWS(A2:C5)+1)	3000

อธิบายการใช้ฟังก์ชัน INDIRECT ของตางรางข้างบน( Column F2-F7)

Column

ข้อ	ผลลัพท์	คำอธิบายผลลัพท์การใช้ฟังก์ชัน INDIRECT
1	A2	ในเซลล์ A2 มีตัวอักษร B4 อยู่, สูตรจะไปค้นหาค่าในเซลล์ B4 จึงได้คำตอบเท่ากับ A2
2	#REF	B2 มีตัวเลข 200, สูตรไม่สามารถระบุตำแหน่งเซลล์อ้างอิงได้ ผลลัพท์เป็นค่าความผิดพลาด
3	700	A3 มีตัวอักษร C4, สูตรจะค้นหาค่าในเซลล์ C4 คำตอบคือ 700
4	C3	ที่เซลล์ C5 มีตัวอักษร A4 อยู่ สูตรจะคืนค่าในเซลล์ A4 ซึ่งคำตอบคือ C3
5	99	ซ้อน INDIRECT อีกชั้นจากข้อ 4, เท่ากับ INDIRECT(A4) สูตรคืนค่าในเซลล์ C3 คำตอบที่ได้คือ 99
6	3000	ใช้ "&" เพื่อล็อคตัว B เชื่อมกับฟังก์ชัน Rows(A2:C5) เท่ากับ 4 คำตอบที่ได้เท่ากับ "B"& 4+1 หรือ B5 นั่นเอง

ภาพด้านข้างแสดงการค้นหา
ค่าในเซลล์สไตล์ INDIRECT

Using Indirect to find reference text in Excel

ต่อไปจะเป็นการปรับใช้ INDIRECT ใน Drop down list ที่สร้างจาก Data Validation

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

เราจะสร้าง 2 Drop down lists ร่วมกับฟังก์ชัน INDIRECT กำหนดเงื่อนไขการบันทึก คือ ก่อนการเลือกชื่อของกลุ่มย่อย (บริษัท) ต้องเลือกชื่อกลุ่มหลัก (ประเภทธุรกิจ) ก่อน มาเริ่มกันเลยดีกว่า

การใช้ Indirect ทำ Drop down lists 2 คอลัมน์

เปิดไฟล์งานไปที่ชีท indirect ซึ่งเป็นที่เก็บข้อมูลรายชื่อบริษัทในธุรกิจอุตสาหกรรม
ไปที่ Formula > Name Manger คลิก New เพื่อกำหนดชื่อให้กับพื้นที่เซลล์
ที่ Name: พิมพ์ว่า INDUSTRY และส่วนของ Refer to : ใช้เม้าส์คลุมพื้นที่ช่วงเซลล์ B2:G2 คลิก ok (ชุดนี้คือประเภทธุรกิจซึ่งจะเป็นข้อมูลหลัก)

Define Name of Industry Business to create two drop down lists

คลิก New เพื่อสร้างชื่อกลุ่มใหม่ (ชุดนี้จะเป็นรายชื่อบริษัทในแต่ละประเภทธุรกิจนั่นคือเป็นกลุ่มย่อย)
ที่ Name: พิมพ์ว่า ยานยนต์ และส่วนของ Refer to : ใช้เม้าส์คลุมช่วงเซลล์ B4:B13 คลิก ok
คลิก New เพื่อเพิ่มชื่อกลุ่มใหม่
ที่ Name: พิมพ์ว่า วัสดุอุตสาหกรรมและเครื่องจักร และส่วนของ Refer to :ใช้เม้าส์คลุมช่วงเซลล์ C4:C10 คลิก ok
ทำแบบเดียวกันกับ ช่วงเซลล์ D4:D5 (กระดาษและวัสดุการพิมพ์), ช่วงเซลล์ E4:E13 (ปิโตรเคมีและเคมีภัณฑ์), ช่วงเซลล์ F4:F13 (บรรจุภัณฑ์), ช่วงเซลล์ G4:G13 (เหล็ก)

Setting Name Manager to create 2 drop down lists

เมื่อเสร็จจากชีท Indirect ให้คลิกแทปชีท Indirect1 ซึ่งเป็นชีทที่เราใช้บันทึกข้อโดยดึงข้อมูลหลักจาก Indirect

สร้าง Drop down list ที่เซลล์ B3 (ใต้ประเภทธุรกิจ) แล้วคลิกที่ Data > Validation
- หัวข้อ Allow: เลือกเป็น List
- หัวข้อ Source: พิมพ์ =Industry
- แล้ว copy สูตรลงเซลล์ที่จะใช้งาน
สร้าง drop down list ที่เซลล์ C3 (ใต้บริษัท)แล้วคลิกที่ Data > Validation
- หัวข้อ Allow: เลือกเป็น List
- หัวข้อ Source: พิมพ์ =INDIRECT(B3)
- แล้ว copy สูตรลงเซลล์ที่จะใช้งาน

ตอนที่สร้าง Drop down list ในข้อ 11. Excel เมื่อคลิก ok จะแสดงกล่องเตือน error ขึ้้นมา สาเหตุเพราะว่า ข้อมูลหลักยังเป็นค่าว่าง ให้คลิก Yes

วันพฤหัสบดีที่ 11 ตุลาคม พ.ศ. 2555

การใช้ฟังก์ชัน IFERROR กับ VLOOKUP

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

IFERROR ทำหน้าที่คล้าย IF เพียงแต่เพิ่มศักยภาพการจัดการกับข้อมูลได้กว้างขึ้นและตรวจสอบความถูกต้องไปพร้อมๆ กัน

ไวยากรณ์และความหมาย

IFERROR(value,value_if_error)

value คืออาร์กิวเมนต์ที่ใช้ตรวจสอบเพื่อหาข้อผิดพลาด
value_if_error คือค่าที่จะส่งกลับถ้า value ได้ผลลัพท์เป็นค่าความผิดพลาด(Error_val) และชนิดของค่าความผิดพลาดที่จะทำให้ฟังก์ชันส่งคืนค่า value_if_error มีอยู่ 7 ชนิด
#N/A

#VALUE!

#REF!

#DIV/0!

#NUM!

#NAME?

#NULL!

โจทย์ตัวอย่างทดสอบการใช้ IFERROR

Row	Column
2	A	B	C	D	E
3	Product	Price	Dis.	Dis.Price	Formula in Dis.Price
4	Ice-Cream Maker C30	5,400	3%	5,238	=IFERROR(B4-(B4*C4),B4)
5	Ice-Cream Maker C45	9,100	5%	8,645	=IFERROR(B5-(B5*C5),B5)
6	Ice-Cream Maker C50	17,900	7%	16,647	=IFERROR(B6-(B6*C6),B6)
7	Mini Prep Processor	3,600		3,600	=IFERROR(B7-(B7*C7),B7)
8	Soup Blender SB30	10,000	3%	9,700	=IFERROR(B8-(B8*C8),B8)
9	Stick hand Blender	3,000		3,000	=IFERROR(B9-(B9*C9),B9)
10	Waffle Maker WF20	5,800	2%	5,684	=IFERROR(B10-(B10*C10),B10)

จากตัวอย่างกำหนดให้ Dis.Price เป็นราคาสุทธิที่หักส่วนลด (Dis.) ตามเปอร์เซนต์ที่กำหนดไว้โดยให้ Price เป็นราคาฐานที่่ใช้คำนวณส่วนลด และถ้าสินค้าชนิดใดไม่มีส่วนลด (Dis.) ให้ใช้ราคาปกติ (Price) ซึ่งจะแสดงสูตรที่ใช้ในคอลัมท์ Formula in Dis.Price

นอกจากนี้ถ้าเราใช้ IFFEROR ร่วมกับ Vlookup เพื่อให้สูตรคืนค่าผลลัพท์จากสองฐานข้อมูลได้ในเวลาเดียวกัน ซึ่งจะเป็นข้อมูลจาก Worksheet เดียวกัน หรือ ต่าง Worksheet ก็ได้

ตัวอย่างถัดไปเป็นการทดสอบ IFERROR ร่วมกับ VLOOKUP
เราจะใช้ข้อมูลจากรูปเป็นฐานข้อมูล ซึ่งข้อมูลด้านซ้ายเป็นจำนวนสต็อคสินค้าของ LED TV และด้านขวาเป็นจำนวนสต็อคสินค้าของ Kitchenware

Row	Column
2	A	B	C
3		LED TV	QTY
4	1	LG	300
5	2	SAMSUNG	250
6	3	PANASONIC	120
7	4	BRAVIA	90
8	5	TOSHIBA	560
9	6	SUNYO	50
10
11
12


E	F	G
	KICHENWARE	QTY
1	OTTO	1,110
2	IMARFLEX	500
3	HOUSE WORTH	459
4	MISAWA	50
5	VICTOR	89
6	SHARP	300
7	PHILIPS	70
8	DEBRANDT	79
9	CUISINART	250

เราจะใช้การค้นหาจำนวนสต็อคของสินค้า 2 หมวดนี้ โดยใช้ฟังก์ชัน IFERROR ที่ โดยใช้เซลล์ A13 เป็นตัวกำหนดเงื่อนไข ให้หาจำนวนสินค้าในสต็อคแบรนด์ Cuisinart ว่ามีจำนวนกี่หน่วย ซึ่งผลลัพท์เท่ากับ 250 ที่เซลล์ B13 และ C13 เป็นรูปแบบฟังก์ชัน IFERROR ที่ใช้ใน B13
และ ที่ เซลล์ A14 เป็นการทดสอบสูตรแบบเดียวกันเพียงแต่เปลี่ยนเป็น แบรนด์ LG ได้คำตอบ 300 หน่วย (B14), แสดงสูตรที่ (C14)

Row	Column
11	A	B	C
12	Brand	Qty.	Formula in Qty. (Column B)
13	CUISINART	250	=IFERROR(VLOOKUP(A13,B4:C9,2,FALSE),(VLOOKUP(A13,F4:G12,2,FALSE)))
14	LG	300	=IFERROR(VLOOKUP(A14,B4:C9,2,FALSE),(VLOOKUP(A13,F4:G12,2,FALSE)))

Search

วันพุธที่ 24 เมษายน พ.ศ. 2556

ตัดเศษด้วยฟังก์ชัน TRUNC กับ INT

=TRUNC (number,num_digits)

=INT (number)

วันพฤหัสบดีที่ 4 เมษายน พ.ศ. 2556

ฟังก์ชัน TRANSPOSE

=TRANSPOSE (array)

วิธีใช้ฟังก์ชัน TRANSPOSE

วันอังคารที่ 26 มีนาคม พ.ศ. 2556

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

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

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

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

วันศุกร์ที่ 8 มีนาคม พ.ศ. 2556

หลากหลายวิธีการคูณใน Excel

PRODUCT(number1,number2,...)

เปรียบเทียบการคูณใน Excel ด้วยการใช้ * (asterisk) กับ ฟังก์ชัน PRODUCT

ข้อสังเกตุช่วยให้เข้าใจการใช้ฟังก์ชัน PRODUCT ได้ง่ายขึ้น

SUMPRODUCT(array1,array2,array3, ...)

เปรียบเทียบการคูณใน Excel ด้วย * กับฟังก์ชัน SUMPRODUCT

เทคนิคช่วยให้ใช้ฟังก์ชัน SUMPRODUCT ง่ายขึ้น

วันจันทร์ที่ 4 มีนาคม พ.ศ. 2556

ฟังก์ชัน CONVERT

=CONVERT(number,from_unit,to_unit)

วันพฤหัสบดีที่ 7 กุมภาพันธ์ พ.ศ. 2556

Customize Quick Access Tool Bar ใช้ Excel ได้เร็วขึ้น จากเครื่องมือด่วนบนทูลบาร์

การปรับแต่งแถบเครื่องมือทูลบาร์

วันพุธที่ 14 พฤศจิกายน พ.ศ. 2555

การใช้ฟังก์ชัน IF กับ INT คำนวณเงินทอนหรือแลกเงินด้วยจำนวนธนบัตรและเหรียญเท่าที่จำเป็น

วันจันทร์ที่ 29 ตุลาคม พ.ศ. 2555

UPPER, PROPER, LOWER

วันศุกร์ที่ 26 ตุลาคม พ.ศ. 2555

วิธีเรียกใช้ Format แบบกึ่งสำเร็จรูป ใน Excel 2007

วันเสาร์ที่ 13 ตุลาคม พ.ศ. 2555

=INDIRECT(ref_text, a1)

การใช้ Indirect ทำ Drop down lists 2 คอลัมน์

วันพฤหัสบดีที่ 11 ตุลาคม พ.ศ. 2555

IFERROR(value,value_if_error)

Popular Pots

Tips

Tag