Search

วันศุกร์ที่ 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"