การแทรกแถวเพิ่มจำนวนพนักงานแล้วสูตรเพี้ยนจะแก้ได้อย่างไร

[tejasid]

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

[snasui]

แนบไฟล์ข้อมูลตัวอย่าง ลบข้อมูลสำคัญทิ้งไป อธิบายมาว่า ก่อนลบ ก่อนแทรก เป็นอย่างไร หลังลบ หลังแทรก เป็นอย่างไร จะได้เข้าใจตรงกันครับ

[tejasid]

แนบไฟล์ข้อมูลตัวอย่าง ลบข้อมูลสำคัญทิ้งไป อธิบายมาว่า ก่อนลบ ก่อนแทรก เป็นอย่างไร หลังลบ หลังแทรก เป็นอย่างไร จะได้เข้าใจตรงกันครับ

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

[snasui]

ตัวอย่างสูตรที่ H9 ครับ

=SUMIF(INDIRECT("R7C6:R[-1]C6",FALSE),LOOKUP(CHAR(255),INDIRECT("R7C6:R[-1]C6",FALSE)),INDIRECT("R7C:R[-1]C",FALSE))

Enter > Copy ไปยังเซลล์ในคอลัมน์อื่นและบรรทัดอื่นที่เกี่ยวข้อง สูตรนี้จะใช้คอลัมน์ F ซึ่งเป็นแผนกในการรวม หากเพิ่มบรรทัดและคีย์ตัวเลขแล้วจะต้องไม่ลืมคีย์ชื่อแผนกด้วยจึงจะได้คำตอบได้ถูกต้องครับ

[tejasid]

อาจารย์ครับรบกวนแปลสูตรให้ด้วยได้ไมครับผมไม่เข้าใจสูตรเลยเอาไปใช้ต่อไม่ได้ครับ

[snasui]

ยกส่วนที่ไม่เข้าใจมาถามครับ

ถึงผมแปลไปก็ไม่ได้รับประกันว่าจะเข้าใจได้ สูตรนั้นไม่ได้ยาก คือการใช้ Sumif ซึ่งเป็นการรวมข้อมูลแบบมีเงื่อนไข

ในส่วนของ Indirect เป็นการแปลงช่วงข้อมูลแบบสายอักขระให้กลับมาเป็นช่วงข้อมูลที่ Excel รู้จัก R คือบรรทัด C คือคอลัมน์ หากมีตัวเลขกำกับ R หรือกำกับ C แสดงว่าเป็นการระบุบรรทัด ระบุคอลัมน์ หากตัวเลขอยู่ในเครื่องหมาย [] แปลว่าเป็นการอ้างอิงแบบ Relative คือสัมพันธ์กับการเปลี่ยนแปลงของบรรทัดหรือคอลัมน์ที่เขียนสูตร ถ้าตัวเลขไม่อยู่ในเครื่องหมาย [] แสดงว่าให้บรรทัดหรือคอลัมน์คงที่แล้วแต่ว่าตัวเลขนั้นอยู่หลัง R หรือ C หรือทั้งคู่

[tejasid]

ผมแนบตัวอย่างที่ 2 มีปัญหาถ้าในแผนกไม่ได้มีแค่แผนกเดียวแต่รวมแผนกอื่นด้วยจะแก้ปัญหาได้อย่างไรครับ

[snasui]

แนบไฟล์ที่ได้คีย์สูตรตามที่ผมตอบไปแล้วจะได้ตอบต่อไปจากนั้นครับ

[tejasid]

แนบไฟล์ที่ได้คีย์สูตรมาแล้วครับอาจารย์

[snasui]

ตัวอย่างการปรับสูตรครับ

็H22 คีย์

=SUM(SUMIF(INDIRECT("R7C6:R[-1]C6",FALSE),{"Skin & Spa","Skin & Spa NA"},INDIRECT("R7C:R[-1]C",FALSE)))

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

[tejasid]

เข้าใจแล้วครับขอบคุณอาจารย์มากครับ