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

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

ฟังก์ชัน OFFSET ของ Microsoft Excel เป็นเครื่องมือที่ยืดหยุ่นและมีประสิทธิภาพ เนื่องจากความซับซ้อนและจำนวนฟังก์ชันที่คล้ายกัน ฟังก์ชัน 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 อ้างอิงชีตอื่นได้ สิ่งนี้ทำให้คุณสามารถแยกการป้อนข้อมูลและการรายงานได้

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

ฟังก์ชันออฟเซ็ตต่อไปนี้ที่อยู่ในเซลล์ 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 ชิ้นที่บันทึกจากเซ็นเซอร์ในแผ่นเดียวและรายงานแบบแบ่งหน้าในอีกแผ่นหนึ่ง

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

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

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

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