Microsoft Excel

คุณอาจไม่ทราบวิธีใช้ฟังก์ชัน OFFSET ใน Excel

คุณรู้หรือไม่ว่าฟังก์ชัน OFFSET ใน Microsoft Excel สามารถช่วยคุณทำสิ่งอื่นๆ ได้มากมาย ไม่ใช่แค่การอ้างอิงเซลล์เท่านั้น ด้านล่างนี้เป็นวิธีการใช้ OFFSET ที่ไม่เหมือนใครซึ่งคุณอาจไม่รู้

คุณรู้หรือไม่ว่าฟังก์ชัน OFFSET ใน Microsoft Excelสามารถช่วยคุณทำสิ่งอื่นๆ ได้มากมาย ไม่ใช่แค่การอ้างอิงเซลล์เท่านั้น ด้านล่างนี้เป็น วิธี เฉพาะในการใช้ OFFSET ใน Excel ที่คุณอาจไม่รู้

คุณอาจไม่ทราบวิธีใช้ฟังก์ชัน OFFSET ใน Excel

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

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

สร้างชุดข้อมูลขนาดที่กำหนดเอง

หนึ่งในการใช้ฟังก์ชัน OFFSET ที่พบบ่อยที่สุดใน Excel คือการรวบรวมชุดข้อมูลที่ปรับขนาดได้ ด้วยชุดข้อมูลขนาดใหญ่ เราอาจพิจารณาเพียงส่วนเล็กๆ ของข้อมูลในช่วงเวลาใดก็ตาม

คุณอาจไม่ทราบวิธีใช้ฟังก์ชัน OFFSET ใน Excel

ตัวอย่างเช่น หากต้องการจัดทำรายงานผลรวมยอดขายของพนักงานโดยอัตโนมัติ คุณต้องได้รับผลรวมสำหรับสัปดาห์หรือสัปดาห์ที่กำหนด

การเขียนฟังก์ชัน SUM ที่แตกต่างกันสำหรับผลรวมแต่ละรายการนั้นค่อนข้างง่าย อย่างไรก็ตาม การใช้ฟังก์ชัน OFFSET จะใช้งานได้ง่ายกว่าและนำกลับมาใช้ใหม่ได้เร็วกว่า

ขั้นแรก ให้ใส่วันที่ที่คุณต้องการเริ่มต้นในเซลล์ B6 จากนั้น ป้อนจำนวนวันที่คุณต้องการเรียกใช้รายงานใน B7 จากนั้น ให้ใช้ฟังก์ชัน SUM และ AVERAGE แบบธรรมดาเพื่อรับข้อมูลเพิ่มเติมสำหรับพนักงานแต่ละคนในช่วงเวลาที่เลือก:

=SUM(OFFSET(B2, 0, B6 - 1, 1, B7))
=AVERAGE(OFFSET(B2, 0, B6 - 1, 1, B7)

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

คุณอาจไม่ทราบวิธีใช้ฟังก์ชัน OFFSET ใน Excel

อนุญาตให้สร้างรายงานที่กำหนดเอง

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

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

คุณอาจไม่ทราบวิธีใช้ฟังก์ชัน OFFSET ใน Excel

ฟังก์ชันออฟเซ็ตต่อไปนี้ที่อยู่ในเซลล์ B5 จะดึงข้อมูลจำนวนวันที่ระบุใน B2 โดยจะเริ่มต้นในสัปดาห์ที่เลือกใน B1 และแสดงเฉพาะข้อมูลในช่วงเวลาที่เกี่ยวข้องเท่านั้น:

=OFFSET(Sheet1!B2, 0, (B1 - 1) * 7, 1, B2 * 7)

คุณสามารถจำกัดรายการใน B1 และ B2 และสร้างรายการแบบเลื่อนลงใน Excel โดยมีสัปดาห์ที่มีอยู่โดยใช้การตรวจสอบข้อมูล

การแบ่งหน้าสเปรดชีต

ฟังก์ชัน OFFSET ยังทำให้การแบ่งหน้าสเปรดชีตแบบธรรมดาเป็นไปได้ ซึ่งเป็นงานที่มักเกี่ยวข้องกับการใช้แท็บ Excel Developer สิ่งนี้สามารถทำให้การประมวลผลข้อมูลกลุ่มใหญ่ง่ายขึ้นและมีประสิทธิภาพมากขึ้น

เมื่อทำงานกับสเปรดชีตที่มีข้อมูลมากถึง 30,000 แถว การค้นหาข้อมูลทั้งหมดในครั้งเดียวอาจเป็นเรื่องยาก สร้างเมนูแบบเลื่อนลงที่ยอมรับช่วงตัวเลขตั้งแต่ 1 ถึง 300 ช่วยให้คุณสามารถแยกข้อมูลตามหน้าได้อย่างรวดเร็วด้วยผลลัพธ์ 100 รายการต่อหน้า

ฟังก์ชัน OFFSET จะมีจุดยึดอยู่ในเซลล์ข้อมูลด้านบนเสมอ อย่างไรก็ตาม อาร์กิวเมนต์ที่สองคือออฟเซ็ตแนวตั้ง ค่านี้เท่ากับหมายเลขหน้าที่เลือกลบ 100 ออฟเซ็ตเป็นแนวนอน อาร์กิวเมนต์ที่สามสามารถตั้งค่าเป็น 0

อาร์กิวเมนต์ที่ 4 ถูกตั้งค่าเป็น 100 อาร์กิวเมนต์ที่ 5 คือจำนวนคอลัมน์ในชุดข้อมูล วิธีนี้ทำให้การแยกและการจัดการตารางข้อมูลเป็นเรื่องง่ายมาก

ด้านล่างนี้เป็นตัวอย่างของข้อมูลที่แบ่งหน้า ประกอบด้วยข้อมูล 500 ชิ้นที่บันทึกจากเซ็นเซอร์ในแผ่นเดียวและรายงานแบบแบ่งหน้าในอีกแผ่นหนึ่ง

คุณอาจไม่ทราบวิธีใช้ฟังก์ชัน OFFSET ใน Excel

รายงานใช้การตรวจสอบข้อมูลในเซลล์ B1 เพื่อสร้างเมนูแบบเลื่อนลงที่มีหน้าเว็บอยู่ข้างใน นิพจน์ต่อไปนี้ในเซลล์ A3 จะสร้างเนื้อหาของรายงาน:

=OFFSET(Sheet1!A2, (B1 - 1) * 100, 0, 100, 21)

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

หวังว่าบทความนี้จะเป็นประโยชน์กับคุณ

More in Microsoft Excel