วิธีการคำนวณเปอร์เซ็นต์การเปลี่ยนแปลงใน Excel [สูตร]

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

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

การคำนวณเปอร์เซ็นต์ที่พบบ่อยที่สุดอย่างหนึ่งที่ผู้ใช้ทำใน Excel คือการคำนวณเปอร์เซ็นต์การเปลี่ยนแปลงระหว่างสองค่าในช่วงเวลาหนึ่ง Percentage Change หรือ Percentage Variance ใช้เพื่อแสดงอัตราการเปลี่ยนแปลง (การเติบโตหรือลดลง) ระหว่างสองค่าจากช่วงเวลาหนึ่งไปอีกช่วงหนึ่ง สามารถใช้เพื่อวัตถุประสงค์ด้านการเงิน สถิติ และวัตถุประสงค์อื่นๆ ได้มากมาย

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

การคำนวณเปอร์เซ็นต์การเปลี่ยนแปลงใน Excel

การคำนวณเปอร์เซ็นต์การเปลี่ยนแปลงระหว่างสองค่านั้นเป็นเรื่องง่าย คุณเพียงแค่ต้องค้นหาความแตกต่างระหว่างตัวเลขสองตัวและหารผลลัพธ์ด้วยตัวเลขเดิม

มีสองสูตรที่แตกต่างกันซึ่งคุณสามารถใช้คำนวณเปอร์เซ็นต์การเปลี่ยนแปลงได้ ไวยากรณ์ของพวกเขามีดังนี้:

=(new_value – original_value) / original_value

หรือ

=(ค่าใหม่ / มูลค่าเดิม) – 1

• new_value – คือค่าปัจจุบัน/สุดท้ายของตัวเลขสองตัวที่คุณกำลังเปรียบเทียบ
• Previous_value – คือค่าเริ่มต้นของตัวเลขสองตัวที่คุณกำลังเปรียบเทียบเพื่อคำนวณเปอร์เซ็นต์การเปลี่ยนแปลง

ตัวอย่าง:

มาดูรายการคำสั่งซื้อผลไม้สมมุติโดยคอลัมน์ B ที่มีจำนวนผลไม้ที่สั่งซื้อในเดือนที่แล้ว และคอลัมน์ C มีจำนวนผลไม้ที่สั่งซื้อในเดือนนี้:

ตัวอย่างเช่น คุณสั่งผลไม้จำนวนหนึ่งเมื่อเดือนที่แล้วและเดือนนี้คุณสั่งมากกว่าที่สั่งเมื่อเดือนที่แล้ว คุณสามารถป้อนสูตรด้านล่างเพื่อค้นหาเปอร์เซ็นต์ความแตกต่างระหว่างสองคำสั่งซื้อ:

=(C2-B2)/B2

สูตรข้างต้นจะถูกป้อนในเซลล์ D2 เพื่อค้นหาเปอร์เซ็นต์การเปลี่ยนแปลงระหว่าง C2 (new_value) และ B2 (original_value) หลังจากพิมพ์สูตรในเซลล์แล้ว ให้กด Enter เพื่อดำเนินการตามสูตร คุณจะได้รับเปอร์เซ็นต์การเปลี่ยนแปลงในค่าทศนิยมดังที่แสดงด้านล่าง

ผลลัพธ์ยังไม่ได้จัดรูปแบบเป็นเปอร์เซ็นต์ หากต้องการนำรูปแบบเปอร์เซ็นต์ไปใช้กับเซลล์ (หรือช่วงของเซลล์) ให้เลือกเซลล์ จากนั้นคลิกปุ่ม "รูปแบบเปอร์เซ็นต์" ในกลุ่มตัวเลขของแท็บ "หน้าแรก"

ค่าทศนิยมจะถูกแปลงเป็นเปอร์เซ็นต์

คำนวณเปอร์เซ็นต์การเปลี่ยนแปลงระหว่างสองคอลัมน์ของตัวเลข

ตอนนี้ เราทราบเปอร์เซ็นต์การเปลี่ยนแปลงระหว่างคำสั่งซื้อสองรายการสำหรับ Apple แล้ว มาดูเปอร์เซ็นต์การเปลี่ยนแปลงของรายการทั้งหมดกัน

ในการคำนวณเปอร์เซ็นต์การเปลี่ยนแปลงระหว่างสองคอลัมน์ คุณต้องป้อนสูตรในเซลล์แรกของคอลัมน์ผลลัพธ์ และป้อนสูตรอัตโนมัติลงในคอลัมน์ทั้งหมด

ในการทำเช่นนั้น ให้คลิกที่จับเติม (สี่เหลี่ยมสีเขียวเล็กๆ ที่มุมล่างขวาของเซลล์) ของเซลล์สูตร แล้วลากลงไปที่เซลล์ที่เหลือ

ตอนนี้คุณได้ค่าเปอร์เซ็นต์การเปลี่ยนแปลงสำหรับสองคอลัมน์แล้ว

เมื่อค่าใหม่สูงกว่าค่าเดิม เปอร์เซ็นต์ผลลัพธ์จะเป็นบวก หากค่าใหม่มีค่าต่ำกว่าค่าเดิม ผลลัพธ์จะเป็นค่าลบ

อย่างที่คุณเห็น เปอร์เซ็นต์การเปลี่ยนแปลงของ 'Apples' เพิ่มขึ้น 50% ดังนั้นค่านี้จึงเป็นค่าบวก เปอร์เซ็นต์ของ 'อะโวโคโด' ลดลง 14% ดังนั้นผลลัพธ์จึงเป็นลบ (-14%)

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

ในการจัดรูปแบบเซลล์ ให้เลือกเซลล์ที่คุณต้องการจัดรูปแบบ คลิกขวาแล้วเลือกตัวเลือก 'จัดรูปแบบเซลล์'

ในหน้าต่าง Format Cells ที่ปรากฏขึ้น ให้คลิกที่ 'Custom' ที่ด้านล่างของเมนูด้านซ้าย และพิมพ์โค้ดด้านล่างในกล่องข้อความ 'Type:':

0.00%;[สีแดง]-0.00%

จากนั้นคลิกปุ่ม 'ตกลง' เพื่อใช้การจัดรูปแบบ

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

คุณยังสามารถใช้สูตรอื่นเพื่อคำนวณเปอร์เซ็นต์การเปลี่ยนแปลงระหว่างสองค่า (คำสั่งซื้อของเดือนที่แล้วและเดือนนี้):

=(C2/B2)-1

สูตรนี้แบ่ง new_value (C2) ด้วยค่าเดิม (B2) และลบ '1' ออกจากผลลัพธ์เพื่อค้นหาเปอร์เซ็นต์การเปลี่ยนแปลง

การคำนวณเปอร์เซ็นต์การเปลี่ยนแปลงเมื่อเวลาผ่านไป

คุณสามารถคำนวณเปอร์เซ็นต์การเปลี่ยนแปลงจากช่วงเวลาหนึ่งไปอีกช่วงหนึ่งได้ (การเปลี่ยนแปลงเดือนต่อเดือน) เพื่อทำความเข้าใจอัตราการเติบโตหรือลดลงจากช่วงหนึ่งไปยังอีกช่วงหนึ่ง (สำหรับทุกๆ เดือน) สิ่งนี้มีประโยชน์มากเมื่อคุณต้องการทราบเปอร์เซ็นต์การเปลี่ยนแปลงของทุกเดือนหรือปีในช่วงเวลาที่กำหนด

ในตัวอย่างด้านล่าง เรามีราคาผลไม้สำหรับเดือนมีนาคมในคอลัมน์ B และราคาสำหรับเดือนกรกฎาคม 5 เดือนต่อมา

ใช้สูตรทั่วไปนี้เพื่อค้นหาเปอร์เซ็นต์การเปลี่ยนแปลงเมื่อเวลาผ่านไป:

=((Current_value/Original_value)/Original_value)*N

ในที่นี้ N หมายถึงจำนวนงวด (ปี เดือน เป็นต้น) ระหว่างสองค่าเริ่มต้นและปัจจุบัน

ตัวอย่างเช่น หากคุณต้องการเข้าใจอัตราเงินเฟ้อหรือภาวะเงินฝืดของราคาทุกเดือนเป็นเวลานานกว่า 5 เดือนในตัวอย่างข้างต้น คุณสามารถใช้สูตรต่อไปนี้ได้

=((C2-B2)/B2)/5

สูตรนี้คล้ายกับสูตรที่เราเคยใช้มาก่อน ยกเว้น เราต้องหารค่าเปอร์เซ็นต์การเปลี่ยนแปลงด้วยจำนวนเดือนระหว่างสองเดือน (5)

การคำนวณเปอร์เซ็นต์การเติบโต/การเปลี่ยนแปลงระหว่างแถว

สมมติว่าคุณมีหนึ่งคอลัมน์ของตัวเลขที่แสดงราคาน้ำมันรายเดือนเป็นเวลานานกว่าหนึ่งปี

ตอนนี้ ถ้าคุณต้องการคำนวณเปอร์เซ็นต์ของการเติบโตหรือการลดลงระหว่างแถวต่างๆ เพื่อให้คุณเข้าใจการเปลี่ยนแปลงของราคาแบบเดือนต่อเดือน ให้ลองใช้สูตรด้านล่าง:

=(B3-B2)/B2

เนื่องจากเราต้องหาเปอร์เซ็นต์การเติบโตในเดือนกุมภาพันธ์ (B3) จากราคาของเดือนมกราคม (B2) เราจึงต้องเว้นแถวแรกว่างไว้เพราะไม่ได้เปรียบเทียบเดือนมกราคมกับเดือนก่อนหน้า ป้อนสูตรในเซลล์ C3 แล้วกด Enter

จากนั้นคัดลอกสูตรไปยังเซลล์ที่เหลือเพื่อกำหนดเปอร์เซ็นต์ความแตกต่างระหว่างแต่ละเดือน

คุณยังสามารถคำนวณเปอร์เซ็นต์การเปลี่ยนแปลงในแต่ละเดือนเมื่อเทียบกับเดือนมกราคม (B2) ในการดำเนินการนี้ คุณต้องทำให้เซลล์นั้นเป็นข้อมูลอ้างอิงแบบสัมบูรณ์โดยเพิ่มเครื่องหมาย $ ในการอ้างอิงเซลล์ เช่น $C$2. ดังนั้นสูตรจะมีลักษณะดังนี้:

=(B3-$B$2)/$B$2

เช่นเดียวกับเมื่อก่อน เราข้ามเซลล์แรกและป้อนสูตรในเซลล์ C3 เมื่อคุณคัดลอกสูตรไปยังเซลล์ที่เหลือ การอ้างอิงแบบสัมบูรณ์ ($B$2) จะไม่เปลี่ยนแปลง ในขณะที่การอ้างอิงแบบสัมพัทธ์ (B3) จะเปลี่ยนเป็น B4, B5 และอื่นๆ

คุณจะได้รับอัตราร้อยละของอัตราเงินเฟ้อหรือภาวะเงินฝืดในแต่ละเดือนเมื่อเทียบกับเดือนมกราคม

การคำนวณเปอร์เซ็นต์ที่เพิ่มขึ้นใน Excel

การคำนวณการเพิ่มเปอร์เซ็นต์จะคล้ายกับการคำนวณเปอร์เซ็นต์การเปลี่ยนแปลงใน Excel เปอร์เซ็นต์เพิ่มขึ้นคืออัตราการเพิ่มขึ้นเป็นค่าเริ่มต้น คุณจะต้องใช้ตัวเลขสองตัวในการคำนวณเปอร์เซ็นต์การเพิ่มขึ้น หมายเลขเดิม และหมายเลข New_number

ไวยากรณ์:

เปอร์เซ็นต์ที่เพิ่มขึ้น = (New_number - Original_number) / Original_number

สิ่งที่คุณต้องทำคือลบตัวเลขเดิม (ตัวแรก) ออกจากตัวเลขใหม่ (ที่สอง) แล้วหารผลลัพธ์ด้วยตัวเลขเดิม

ตัวอย่างเช่น คุณมีรายการบิลและจำนวนเงินสำหรับสองเดือน (เมษายนและพฤษภาคม) หากบิลของคุณสำหรับเดือนเมษายนเพิ่มขึ้นในเดือนพฤษภาคม แล้วเดือนเมษายนถึงพฤษภาคมจะเพิ่มขึ้นเป็นเท่าใด คุณสามารถใช้สูตรด้านล่างเพื่อค้นหา:

=(C2-B2)/B2

ในที่นี้ เราลบบิลค่าไฟฟ้าเดือนเดือนพฤษภาคม (C2) ออกจากบิลเดือนเมษา (B2) แล้วหารผลลัพธ์ด้วยบิลเดือนเมษา จากนั้นคัดลอกสูตรไปยังเซลล์อื่นโดยใช้ที่จับเติม

หากคุณได้ผลลัพธ์ที่เป็นบวก (24.00%) เปอร์เซ็นต์จะเพิ่มขึ้นระหว่างสองเดือน และหากคุณได้ผลลัพธ์ที่เป็นลบ (เช่น -$13.33%) เปอร์เซ็นต์นั้นจะลดลงจริง ๆ แทนที่จะเพิ่มขึ้น

การคำนวณเปอร์เซ็นต์การลดลงใน Excel

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

สูตรนี้ใกล้เคียงกันกับการคำนวณเปอร์เซ็นต์ที่เพิ่มขึ้น ยกเว้นในกรณีนี้ คุณจะลบค่าเดิม (ตัวเลขแรก) ออกจากค่าใหม่ (ตัวเลขที่สอง) ก่อนที่จะหารผลลัพธ์ด้วยค่าเดิม

ไวยากรณ์:

เปอร์เซ็นต์การลดลง = (Original_number - New_number) / Original_number

สมมติว่าคุณมีรายการอุปกรณ์เก็บข้อมูลและราคาสำหรับสองปี (2018 และ 2020)

ตัวอย่างเช่น ราคาอุปกรณ์จัดเก็บข้อมูลสูงขึ้นในปี 2561 และราคาลดลงในปี 2563 ราคาลดลงในปี 2563 เทียบกับปี 2561 คิดเป็นร้อยละเท่าใด คุณสามารถใช้สูตรนี้เพื่อค้นหา:

=(B2-C2)/B2

ที่นี่ เราลบราคาปี 2018 (B2) ออกจากราคาปี 2020 (C2) และหารผลรวมด้วยราคาปี 2018 จากนั้น เราคัดลอกสูตรลงไปที่เซลล์อื่นเพื่อค้นหาเปอร์เซ็นต์ที่ลดลงสำหรับอุปกรณ์อื่นๆ

หากคุณได้ผลลัพธ์ที่เป็นบวก ($20.00%) เปอร์เซ็นต์จะลดลงระหว่างสองปี และหากคุณได้ผลลัพธ์ที่เป็นลบ (เช่น -$13.33%) เปอร์เซ็นต์นั้นจะเพิ่มขึ้นจริง ๆ แทนที่จะลดลง

ข้อผิดพลาดทั่วไปที่คุณได้รับเมื่อใช้สูตรข้างต้น

เมื่อใช้สูตรข้างต้น คุณอาจพบรายการข้อผิดพลาดทั่วไปเป็นครั้งคราว:

• #DIV/0!: ข้อผิดพลาดนี้เกิดขึ้นเมื่อคุณพยายามหารตัวเลขด้วยศูนย์ (0) หรือด้วยเซลล์ที่ว่างเปล่า เช่น. ค่า B6 ในสูตร '=(B6-C6)/B6' เท่ากับศูนย์ ดังนั้น #DIV/0! ข้อผิดพลาด.

เมื่อคุณป้อน 'ศูนย์' ในรูปแบบสกุลเงิน จะถูกแทนที่ด้วยขีดกลาง (-) ดังที่แสดงด้านบน

• #ค่า: Excel จะแสดงข้อผิดพลาดนี้เมื่อคุณป้อนค่าที่ไม่ใช่ประเภทที่ได้รับการสนับสนุนหรือเมื่อปล่อยเซลล์ว่างไว้ ซึ่งมักจะเกิดขึ้นเมื่อค่าที่ป้อนมีช่องว่าง อักขระ หรือข้อความแทนที่จะเป็นตัวเลข

• NUM!: ข้อผิดพลาดนี้เกิดขึ้นเมื่อสูตรมีตัวเลขที่ไม่ถูกต้องซึ่งส่งผลให้ตัวเลขที่มากเกินไปหรือเล็กเกินไปที่จะแสดงใน Excel

แปลงข้อผิดพลาดเป็นศูนย์

แต่มีวิธีที่เราสามารถกำจัดข้อผิดพลาดเหล่านี้ และแสดง '0%' แทนเมื่อเกิดข้อผิดพลาด ในการทำเช่นนั้น คุณต้องใช้ฟังก์ชัน 'IFERROR' ซึ่งส่งกลับผลลัพธ์แบบกำหนดเองเมื่อสูตรสร้างข้อผิดพลาด

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

=IFERROR(ค่า, value_if_error)

ที่ไหน,

• ค่า คือค่าอ้างอิงหรือสูตรที่จะตรวจสอบ
• value_if_error คือค่าที่เราต้องการแสดงหากสูตรคืนค่าผิดพลาด

ลองใช้สูตรนี้ในหนึ่งในตัวอย่างข้อผิดพลาด (ข้อผิดพลาด #DIV/0!):

=IFERROR((B6-C6)/B6,0%)

ในสูตรนี้ อาร์กิวเมนต์ 'value' คือสูตรการเปลี่ยนแปลงเปอร์เซ็นต์ และอาร์กิวเมนต์ 'value_if_error' คือ '0%' เมื่อสูตรการเปลี่ยนแปลงเปอร์เซ็นต์พบข้อผิดพลาด (#DIV/0! error) ฟังก์ชัน IFERROR จะแสดง '0%' ดังที่แสดงด้านล่าง

นั่นคือทุกสิ่งที่คุณจำเป็นต้องรู้เกี่ยวกับการคำนวณเปอร์เซ็นต์การเปลี่ยนแปลงใน Excel