หากคุณทำงานกับตัวเลขใน Microsoft Excel เป็นไปได้สูงที่คุณจะต้องคำนวณเปอร์เซ็นต์การเปลี่ยนแปลงเป็นประจำ โชคดีที่ Excel มีสูตรและเทคนิคมากมายเพื่อช่วยให้คุณคำนวณเปอร์เซ็นต์การเปลี่ยนแปลงหรือความแตกต่างระหว่างตัวเลขหรือเซลล์สองตัวได้อย่างง่ายดาย และเราจะนำเสนอในบทช่วยสอนนี้
เปอร์เซ็นต์การเปลี่ยนแปลง หรือที่เรียกว่าการเปลี่ยนแปลงเปอร์เซ็นต์หรือความแตกต่าง ใช้เพื่อแสดงความแตกต่างระหว่างค่าสองค่า กล่าวอีกนัยหนึ่ง เปอร์เซ็นต์การเปลี่ยนแปลงเป็นวิธีการบอกว่าบางสิ่งเพิ่มขึ้นหรือลดลงในรูปของเปอร์เซ็นต์เมื่อเทียบกับค่าก่อนหน้า
ตัวอย่างเช่น คุณสามารถใช้เพื่อกำหนดเปอร์เซ็นต์ที่เพิ่มขึ้นหรือลดลงของจำนวนนักเรียนในโรงเรียนระหว่างปีที่แล้วถึงปีนี้ หรือ ความแตกต่างของราคาของผลิตภัณฑ์เมื่อเทียบกับเดือนที่แล้ว หรือจำนวนผู้เยี่ยมชมเว็บไซต์ก่อนและหลังการใช้แคมเปญการตลาด เป็นต้น
ในบทความนี้ ฉันจะแสดงวิธีคำนวณเปอร์เซ็นต์การเปลี่ยนแปลงระหว่างตัวเลขสองตัวใน Excel และวิธีนับการเปลี่ยนแปลงเปอร์เซ็นต์เมื่อคุณมีค่าเป็นลบหรือเป็นศูนย์
-
ค้นหาเปอร์เซ็นต์การเปลี่ยนแปลงใน Excel
- สูตร 1.
- สูตร 2
- การจัดรูปแบบผลลัพธ์เป็นเปอร์เซ็นต์ (ตำแหน่งทศนิยม, สี)
- ค้นหาเปอร์เซ็นต์การเปลี่ยนแปลงเมื่อมีค่าเป็นลบ
- การทำงานกับค่าเปอร์เซ็นต์การเปลี่ยนแปลงและค่าศูนย์
- นับเปอร์เซ็นต์การเปลี่ยนแปลงระหว่างเซลล์ในคอลัมน์เดียวกัน
- ค้นหาเปอร์เซ็นต์การเปลี่ยนแปลงเป็นหมายเลขอ้างอิง/ค่า
วิธีนับเปอร์เซ็นต์ที่เพิ่มขึ้นหรือลดลงใน Microsoft Excel
การค้นหาเปอร์เซ็นต์การเปลี่ยนแปลงระหว่างตัวเลขสองตัวใน Excel ไม่ใช่เรื่องยาก และสามารถทำได้โดยใช้หนึ่งในสอง (2) สูตรต่อไปนี้:
- =(new_value–old_value)/old_value
- =(new_value/old_value)–1
ที่ไหน:
● new_value: หมายถึงค่าล่าสุดหรือค่าสุดท้ายของตัวเลขสองตัวที่กำลังเปรียบเทียบ
● old_value: เป็นค่าเริ่มต้นระหว่างตัวเลขสองตัวเปรียบเทียบเพื่อคำนวณเปอร์เซ็นต์การเปลี่ยนแปลง
ตัวอย่าง: สมมติว่าคุณต้องการทราบเปอร์เซ็นต์ (เป็นเปอร์เซ็นต์) ที่ราคาของรายการผลิตภัณฑ์เพิ่มขึ้นหรือลดลงตั้งแต่เดือนก่อนหน้า เพื่อให้งานนี้เสร็จสมบูรณ์ คุณต้องคำนวณเปอร์เซ็นต์การเปลี่ยนแปลงของผลิตภัณฑ์แต่ละรายการระหว่างราคาปัจจุบัน (new_value) และราคาก่อนหน้า (old_value)
1. เปิดสเปรดชีตใหม่ใน Excel
2. พิมพ์ส่วนหัวของคอลัมน์ในเซลล์แถวแรกดังนี้*
- ในคอลัมน์แรกในเซลล์ A1 พิมพ์ "รายการ"สำหรับรายละเอียดสินค้า.
- ในคอลัมน์ที่สองในเซลล์ บี1 พิมพ์ "ราคาเดือนที่แล้ว" ("ค่าเก่า").
- ในคอลัมน์ที่สามในเซลล์ C1 พิมพ์ "ราคาเดือนนี้" (new_value")
3. ตอนนี้ ในแต่ละหัวข้อ ให้ป้อนข้อมูลที่เกี่ยวข้อง (เช่น คำอธิบายของสินค้า ราคาของเดือนก่อนหน้า และราคาของเดือนปัจจุบัน)
4.ทางเลือก: หลังจากกรอกราคาสินค้าแล้ว คุณสามารถระบุประเภทของสกุลเงิน (เช่น ดอลลาร์ ($) ยูโร (€) เป็นต้น) ได้หากต้องการดังต่อไปนี้:
คลิกที่ตัวอักษรตัวใดตัวหนึ่งของคอลัมน์ที่มีราคา (เช่น "B") จากนั้นใน บ้าน แท็บคลิก ลูกศร ถัดจากดอลลาร์ $ แล้วเลือกรูปแบบสกุลเงิน [เช่น "$ อังกฤษ (สหรัฐอเมริกา)].
5. จากนั้น ที่เซลล์แรกของคอลัมน์ว่างแรก (เช่น "D1" ในตัวอย่างนี้) พิมพ์ "เปอร์เซ็นต์การเปลี่ยนแปลง".
6. ตอนนี้ใช้หนึ่งในสอง (2) วิธี/สูตรด้านล่างเพื่อค้นหาเปอร์เซ็นต์การเปลี่ยนแปลง
วิธี ก. คำนวณเปอร์เซ็นต์การเปลี่ยนแปลงด้วย: =(new_value–old_value)/old_value"
ในการนับเปอร์เซ็นต์ในตัวอย่างของเราโดยใช้สูตร:
=(new_value–old_value)/old_value
ก. เลือกเซลล์ว่างเซลล์แรกภายใต้ "เปอร์เซ็นต์การเปลี่ยนแปลง" (เช่น เซลล์ "D2), พิมพ์ ด้านล่าง สูตร และกด เข้า (หรือคลิกในเซลล์ว่างหนึ่งเซลล์) *
=(C2-B2)/B2
* บันทึก: ที่นี่ค่าเก่า (บี2) ถูกลบออกจากค่าใหม่ (C2) และผลลัพธ์จะถูกหารด้วยค่าเก่า (บี2).
ข. หลังจากกด Enter เปอร์เซ็นต์การเปลี่ยนแปลงจะเป็นค่าทศนิยมตามที่แสดงด้านล่าง หากต้องการเปลี่ยนผลลัพธ์เป็นรูปแบบเปอร์เซ็นต์ ให้เลือกเซลล์ D2 และคลิก รูปแบบร้อยละ ปุ่ม (%) ใน ตัวเลข กลุ่ม.
* บันทึก: คุณยังสามารถสมัคร เปอร์เซ็นต์ จัดรูปแบบเป็นเซลล์ผลลัพธ์ก่อนป้อนสูตร
ค. ตอนนี้ คุณมีเปอร์เซ็นต์การเปลี่ยนแปลงระหว่างสองค่าในเซลล์ บี2 & ซีทู ในเซลล์ D2 ซึ่งเป็น 10%.
6. หลังจากคำนวณเปอร์เซ็นต์การเปลี่ยนแปลงสำหรับสินค้าชิ้นแรก (เช่น "สมาร์ทโฟน" ในตัวอย่างนี้) เราสามารถใช้สูตรเดียวกันเพื่อดูเปอร์เซ็นต์การเปลี่ยนแปลงสำหรับสินค้าอื่นๆ ทั้งหมดได้ ในการทำเช่นนั้น:
คลิก และ ลาก เดอะ เติมที่จับ (สี่เหลี่ยมสีเขียวเล็กๆ ที่มุมล่างขวาของเซลล์สูตร) ลงไปด้านล่างเพื่อคัดลอกสูตรไปยังเซลล์ที่คุณต้องการเติม
วิธี ข. การคำนวณเปอร์เซ็นต์การเปลี่ยนแปลงด้วย: =(new_value/old_value)–1"
วิธีที่สองในการค้นหาเปอร์เซ็นต์การเปลี่ยนแปลงใน Excel คือการใช้สูตร:
=(new_value/old_value)–1
เช่น. เมื่อต้องการค้นหาเปอร์เซ็นต์การเปลี่ยนแปลงระหว่างสองค่าในเซลล์ บี2 และ ซีทู พิมพ์สูตรต่อไปนี้:
=(C2/B2)-1
อย่างที่คุณเห็น สูตรที่สองสามารถให้ผลลัพธ์เหมือนกับสูตรแรก คัดลอกสูตรไปยังเซลล์ที่เหลือด้านล่างสำหรับผลิตภัณฑ์ทั้งหมด เสร็จแล้ว!
วิธีจัดรูปแบบตำแหน่งทศนิยมและสีของเปอร์เซ็นต์ผลลัพธ์
ดังที่คุณจะเห็นในตัวอย่างของเรา ผลลัพธ์เป็นเปอร์เซ็นต์จะแสดงโดยไม่มีตำแหน่งทศนิยมและมีเครื่องหมายลบเมื่อมีการลดลงของเปอร์เซ็นต์
หากคุณต้องการแสดงเปอร์เซ็นต์ที่เพิ่มขึ้นหรือลดลง (เช่น มีจุดทศนิยม) และรูปแบบ เช่น เดอะ เปอร์เซ็นต์ติดลบในสีอื่น (เช่น สีแดง) เพื่อให้คุณเข้าใจความแตกต่างได้ง่าย ให้ทำ กำลังติดตาม:
1. จากนั้นเลือกเซลล์ทั้งหมดที่มีเปอร์เซ็นต์การเปลี่ยนแปลง คลิกขวา และเลือก จัดรูปแบบเซลล์ จากเมนูบริบท
2. ในหน้าต่าง "จัดรูปแบบเซลล์" คลิกที่ กำหนดเอง ตัวเลือกบนเมนูด้านซ้าย พิมพ์โค้ดด้านล่างในช่อง พิมพ์: ช่องข้อความแล้วคลิก ตกลง. *
0.00%;[สีแดง]-0.00%
* หมายเหตุ:
1. หากคุณใช้ เครื่องหมายจุลภาค (,) เป็นสัญลักษณ์ทศนิยม แทนที่ เดอะ จุด (.) กับ เครื่องหมายจุลภาค (,). เช่น.: "0,00%;[แดง]-0,00%"
2. หากต้องการจัดรูปแบบการเปลี่ยนแปลงเปอร์เซ็นต์ที่เป็นบวก เช่น "สีเขียว"สีและลบกับ"สีแดง" พิมพ์: "[เขียว]0.00%;[แดง]-0.00%"
3. ซึ่งจะจัดรูปแบบการเปลี่ยนแปลงเปอร์เซ็นต์ที่ลดลงทั้งหมดเป็นสีแดง และจะแสดงทศนิยมสอง (2) ตำแหน่งสำหรับเปอร์เซ็นต์ที่แน่นอน
วิธีคำนวณเปอร์เซ็นต์การเปลี่ยนแปลงเมื่อมีค่าติดลบ
ถ้าคุณมีตัวเลขติดลบอยู่ในค่าของคุณ สูตรเปอร์เซ็นต์ผลต่างปกติอาจไม่ได้ผลลัพธ์ที่ถูกต้อง โดยเฉพาะอย่างยิ่ง:
● ถ้าทั้ง เก่า และ ค่าใหม่ เป็น เชิงลบ หรือ เชิงบวกคุณสามารถใช้สูตรเปอร์เซ็นต์ได้โดยไม่มีปัญหาใดๆ
● ถ้า ค่าเก่าเป็นบวก และ ค่าใหม่เป็นค่าลบคุณยังสามารถใช้สูตรเปอร์เซ็นต์ที่กำหนดเพื่อให้ได้คำตอบที่ถูกต้อง
● แต่ถ้า ค่าเก่าเป็นลบ และ ค่าใหม่เป็นค่าบวกสูตรเปอร์เซ็นต์จะให้ผลลัพธ์ที่ไม่ถูกต้อง ดังที่คุณเห็นในเซลล์ "C4" & "C7" ในภาพหน้าจอด้านล่าง
ในการแก้ไขปัญหานี้ ให้ใส่ "เอบีเอส" ฟังก์ชันเพื่อทำให้ตัวส่วนเป็นจำนวนบวก โดยใช้และใช้สูตรนี้กับเซลล์เปอร์เซ็นต์ทั้งหมด:
=(new_value – old_value) / ABS(old_value)
* บันทึก: เพื่อให้แน่ใจว่าสูตรทำงานได้อย่างถูกต้อง ไม่ว่าค่าใดจะเป็นจำนวนลบก็ตาม)
เช่น. ในตัวอย่างของเรา สูตรจะเป็น: "=(B1-A1)/ABS(A1)"
วิธีค้นหาเปอร์เซ็นต์การเปลี่ยนแปลงเมื่อมีค่าเป็นศูนย์ (s)
ถ้าเซลล์ของคุณมีค่าเป็นศูนย์ทั้งในค่าเก่าหรือใหม่ คุณอาจพบปัญหา #DIV/0! ข้อความผิดพลาด. สิ่งนี้เกิดขึ้นเนื่องจากคุณไม่สามารถหารจำนวนใด ๆ ด้วยศูนย์ในทางคณิตศาสตร์
ในการแก้ไขปัญหานี้ คุณสามารถแทรก "IFERROR" ในสูตร Excel ของคุณ ดังนั้นหากสูตรการเปลี่ยนแปลงเปอร์เซ็นต์พบข้อผิดพลาดใดๆ (เช่น ข้อผิดพลาด "#DIV/0!") จะส่งกลับ "0%" ผลที่ตามมา:*
=IFERROR((ค่าใหม่ – ค่าเก่า) / ABS (ค่าเก่า),0)
* บันทึก: หากคุณใช้เครื่องหมายจุลภาค (,) เป็นสัญลักษณ์ทศนิยม แทนที่ เครื่องหมายจุลภาค ","ในสูตรที่มีเครื่องหมายอัฒภาค";" และใช้สูตรด้านล่าง:
=IFERROR((new_value – old_value) / ABS(old_value);0)
เช่น. ในตัวอย่างของเรา (โดยที่เราใช้เครื่องหมายจุลภาค (,) เป็นสัญลักษณ์ทศนิยม) สูตรจะเป็น: "=IFERROR((B1-A1)/ABS(A1);0)"
วิธีคำนวณเปอร์เซ็นต์การเปลี่ยนแปลงระหว่างเซลล์ของคอลัมน์เดียวกัน
วิธีวัดเปอร์เซ็นต์การเปลี่ยนแปลงระหว่างเซลล์ในคอลัมน์เดียวกันเกือบจะเหมือนกับที่อธิบายไว้ข้างต้นสำหรับการวัดเปอร์เซ็นต์การเปลี่ยนแปลงระหว่างเซลล์ในแถวเดียวกัน
ตัวอย่าง: เพื่อคำนวณเปอร์เซ็นต์ที่เพิ่มขึ้นหรือลดลงของกำไรของคุณในแต่ละเดือน
1. เปิดเอกสาร Excel เปล่าแล้วป้อน เช่น เดือนและผลกำไรของพวกเขา
2. จากนั้นเลือกเซลล์ว่างที่คุณต้องการแสดงเปอร์เซ็นต์การเปลี่ยนแปลงระหว่างสองเดือนแรก (เช่น "C3" ในตัวอย่างนี้) แล้วพิมพ์สูตรนี้เพื่อนับเปอร์เซ็นต์การเปลี่ยนแปลงระหว่างสองเดือนแรก (มกราคม & กุมภาพันธ์): *
=(B4-B3)/B3
* ที่ไหน:B4 = รายได้ของเดือนที่สอง, และ B3 = รายได้เดือนแรก
3. ถัดไป ใช้สูตรกับคอลัมน์ที่เหลือ (C3 ถึง C14) จากนั้น ถ้าคุณต้องการ ให้ใช้การจัดรูปแบบสีแบบกำหนดเองด้วย (ตามที่อธิบายไว้ด้านบน) สำหรับเปอร์เซ็นต์ค่าลบ
วิธีค้นหาเปอร์เซ็นต์การเปลี่ยนแปลงเป็นค่าอ้างอิง
หากคุณต้องการคำนวณเปอร์เซ็นต์การเปลี่ยนแปลงเทียบกับเซลล์เฉพาะ (ค่าอ้างอิง) ให้เพิ่ม "$" ลงชื่อก่อนทั้งตัวอักษรประจำคอลัมน์และหมายเลขแถวของเซลล์อ้างอิง
ตัวอย่าง: ในการคำนวณเปอร์เซ็นต์การเปลี่ยนแปลงของกำไรสำหรับเดือนกุมภาพันธ์ (บี4) เทียบกับเดือนมกราคม (บี3) เป็นเดือนอ้างอิง สูตรคือ:
=(B4-$B$3)/$B$3
จากนั้นใช้สูตรกับเซลล์ที่เหลือเพื่อรับเปอร์เซ็นต์การเปลี่ยนแปลงสำหรับแต่ละเดือนเมื่อเทียบกับเดือนมกราคม
แค่นั้นแหละ! แจ้งให้เราทราบหากคำแนะนำนี้ช่วยคุณได้โดยการแสดงความคิดเห็นเกี่ยวกับประสบการณ์ของคุณ โปรดชอบและแชร์คำแนะนำนี้เพื่อช่วยเหลือผู้อื่น