วันเสาร์ที่ 21 กรกฎาคม พ.ศ. 2555

Copy ใน Excel โดยให้สูตรติดมาด้วยสตริง($) และ ประโยชน์ของ Round


ตัวช่วยที่ทำให้การคำนวณใน Excel ง่ายขึ้นและเร็วขึ้น สำหรับปริมาณข้อมูลมากๆ คือการใช้ $ (สตริงเรียกคำสั่งนี้ด้วยการกด F4) เพื่อล็อคตำแหน่งที่อ้างอิงในสูตร เมื่อเรา copy สูตรนั้นไปใช้ที่เซลล์อื่นๆ ค่าที่อยู่หลังเครื่องหมาย $ จะไม่ถูกเปลี่ยนแปลงไปเป็นการ fix สูตร เพราะสามารถ copy ให้สูตรแสดงผลเดียวกับเซลล์เดิม มาที่เซลล์ใหม่ด้วย
โจทย์ตั้งเงื่อนไขให้หาราคาสินค้า Ink-Jet Printer 4 รุ่นที่ต้องการมาทำส่วนลดการขายรุ่นละ 10% ซึ่งผลลัพท์จะแสดงใน Dis. 10%(Column D) และเมื่อนำส่วนลดรวมกับราคาจะได้ราคาใหม่ คือ New Price (Column E) และแสดงสูตรที่ Formula (Column F) ซึ่งเป็นสูตรที่ใช้ใน Dis.10% (Column D)

ตารางตัวอย่างการใช้ฟังก์ชัน Round+$
B
C
D
E
F
1
2
1
2
3
4
5
3Table_array
4Multi Ink-Jet PrinterPriceDis.10%New Priceสูตรใน Column D
5Brother MFC-J825DW
8,990
899
8,091
=Round(C5*$D$4,0)
6Epson Me Office620F
3,990
399
3,591
=Round(C6*$D$4,0)
7Canon E500
2,790
279
2,511
=Round(C7*$D$4,0)
8Canon Pixma MG5370
3,190
319
2,871
=Round(C8*$D$4,0)

ใช้ฟังก์ชัน ROUND เพื่อให้สูตรคืนค่าผลลัพท์ไม่มีเศษทศนิยม(ปัดเศษ)

  1. ถ้าไม่ใช้ ROUND คลุม สูตรที่เซลส์ D5 จะเท่ากับ =C5*$D$4
  2. ไวยากรณ์ฟังก์ชัน ROUND คือ =ROUND(เซลล์หรือค่าที่ต้องการ,ตำแหน่งทศนิยมที่ต้องการ) เมื่อเราเลือกค่า "0" คือจะไม่มีเศษทศนิยม
  3. สมมุติว่าหา 10% ของราคาใหม่ Canon E500 ซึ่งเท่ากับ (2,511*10/100) จะได้ 251.1 เมื่อใส่ round เข้าไปเศษ .1 จะถูกปัดออก ผลลัพท์จะเท่ากับ 251
Using $ and Round in Excel Formula
การเรียกใช้คำสั่ง $ ผ่านคีย์ลัด F4
  • พิมพ์สูตรแล้วให้กด F4 เช่นพิมพ์ =B2 แล้ว กด F4 จะได้ผลลัพท์ =$B$2
  • ถ้าฟังก์ชันนั้นยาวมากก็ให้กด F4 หลังจากเลือกหรือพิมพ์พื้นที่เซลล์ที่ต้องการฟิกซ์ เช่น =vlookup(A4,แล้วกด F4 2 ครั้งหน้าตาสูตรจะเป็น =vlookup(A$4,
  • ถ้าฟังก์ชันมีอยู่ก่อนแล้วและต้องการเพิ่ม $ เพื่อฟิกซ์สูตรก็ให้ไปที่เซลล์ที่มีฟังก์ชันนั้น แล้ว กด F2 แล้วเลือกช่วงที่ต้องการกำหนดแล้วจึงกด F4
ตารางเปรียบเทียบการใช้ $
RowColumn
1ABCD
2137
32
44
5=$B$2111
6111
7=B$2137
8137
9 =$B2 111
10222

ทดสอบใช้ $ ล็อคสูตรในเซลล์

  1. ที่เซลล์ A5 พิมพ์ =B2 แล้วกด F4 1 ครั้ง ตัวสตริง $ อยู่ทั้งหน้าและหลัง Column Letter จะได้ =$B$2 แล้วก๊อปปี้ไปยัง B5 ถึง D6 ผลลัพท์เท่ากับ 1 เสมอ (สีเหลือง) เพราะเป็นการล็อคเซลล์ B2 ไม่ว่าจะก๊อปไปไหนสูตร จะย้อนมาอ้างอิงเซลล์ B2 เท่านั้น

  2. ที่เซลล์ B7 พิมพ์ =B2 แล้วกด F4 2 ครั้ง ตัวสตริง $ อยู่หลัง Column Letter จะได้ =B$2 แล้วก๊อปปี้จาก B7 ถึง D8 ผลลัพท์จะได้ 1,3,7 ทั้งสองแถว(สีเขียว) เพราะ $ ทำหน้าที่ล็อคแถว 2 (Row2) เสมอ สูตรในคอลัมท์ C จะเปลี่ยนเป็น C$2 และคอลัมท์ D เป็น D$2

  3. ที่เซลล์ B9 พิมพ์ =B2 แล้วกด F4 3 ครั้ง ตัวสตริง $ อยู่หน้า Column Letter จะได้ =$B2 แล้วก๊อปปี้จาก B9 ถึง D10 ผลลัพท์จะได้ 1,1,1 ที่แถวแรก และ 2,2,2 ที่แถวสอง (สีชมพู)ตัว $ ล็อคเฉพาะ Column B สูตรในเซล์ B10 ถึง d10 จะเปลี่ยนเป็น $B3

Reactions:

4 ความคิดเห็น:

  1. ขอบคุณในความรู้ที่ได้รับค่ะ

    ตอบลบ
  2. ขอบคุณครับ วันนี้ช่วยผมไว้

    ตอบลบ
  3. กำลังหาอยู่พอดี ขอบคุณครับ

    ตอบลบ
  4. จำเป็นต้องใช้มาก กำลังหาพอดี ขอบคุณค่าาา

    ตอบลบ