วันเสาร์ที่ 30 มีนาคม พ.ศ. 2556

คำนวณจำนวนตัวเลขที่มีตัวอักษร(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 กำหนดให้เป็นตามรูป input type of date formatting

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

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

การแปลงตัวเลขเป็นค่าเงินภาษาอังกฤษ 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 ซึ่งเป็นรูปสามเหลี่ยมสีเหลืองตามภาพ
    choose macro security

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

  4. เมื่อกลับมาที่หน้า worksheet ให้กด ALT+F11 เพื่อเปิดใช้งาน Visual Basic Editor หรือ เลือก Developer Tab แล้วเลือก Visual Basic
    Insert module in VB
    1. เข้าเมนู แทรก(Insert)
    2. คลิกโมดูล (Module)
    3. แล้วพิมพ์รหัสวางในโมดูล (ใช้คัดลอกได้)
    paste code in module
  5. รหัสที่ใช้คัดลอกไปวางที่โมดูลที่เพิ่มใหม่
    
      
    
  6. บันทึก
    Save button

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

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

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

    Click Add-In menu from Excel options
    Then click Go button at manage Add-In to find Add-In list
    Choose Spellnumber from Add-In list

วิธีใช้ฟังก์ชัน 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
Sample of using Spellnumber Function
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)เป็นตัวคำนวณ =B1*B2*C1*C2*D1*D2*E1*E2*F1*F2*G1*G2*H1*H2
6 ฟังก์ชัน PRODUCT number อยู่ติดกัน =PRODUCT(B1:H2)
6 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 =(B1*B3)+(C1*C3)+(D1*D3)+(E1*E3)+(F1*F3)+(G1*G3)+(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"