Snasui Excel Forum

เรียนอาจาร์ยและทุกท่านครับ
ผมต้องการแก้ไขสูตรsumifเพื่อให้ประมวลผลเร็วขึ้น ดังนี้ครับ
-ข้อมูลของผมมีประมาณ 7000บรรทัด
-ในsheet DataWk01 ผมใช้สูตร=SUMIF(ReferWk01!A:A,DataWk01!A:A,ReferWk01!C:C)
-ก็สามารถทำงานได้นะครับแต่ว่าช้ามาก เลย พอจะมีสูตรที่รวดเร็วกว่านี้ใหมครับ
ขอบพระคุณมากครับ
อาร์ต

วิธีการแก้คือ ดึงข้อมูลมาแสดงทีละรหัสครับ จะใช้วิธีการลากสูตรตามจำนวนข้อมูลที่มี ไม่ว่าสูตรไหนก็ชาครับ

อนึ่ง สูตรที่คุณแสดงมา =SUMIF(ReferWk01!A:A,DataWk01!A:A,ReferWk01!C:C) ก็เป็นสาเหตุหลักที่ทำให้การคำนวนช้า เราควรใส่ช่วงเซลล์ตามปริมาณข้อมูลที่มีอยู่จริง ไม่ใช่ A:A , C:C ซึ่งเท่ากับสั่งให้สูตรหาทั้งคอลัมน์ ย่อมช้าอย่างแน่นอน ควรเปลี่ยนเป็น =SUMIF(Sheet1!$A$2:$A$6,$A2,Sheet1!C$2:C$6) ประมาณนี้จึงจะเหมาะ หรือ ใช้ RangeName มาช่วยครับ

ผมอยากให้คุณBankช่วยอธิบาย Ranknameด้วยครับ ใช้อย่างไรผมไช้ไม่เป็นครับ
ขอโทษที่ตอบช้าครับเพราะว่างานเยอะครับ
ขอบคุณมากครับ
อาร์ต

lotto009 wrote:ผมอยากให้คุณBankช่วยอธิบาย Ranknameด้วยครับ ใช้อย่างไรผมไช้ไม่เป็นครับ
ขอโทษที่ตอบช้าครับเพราะว่างานเยอะครับ
ขอบคุณมากครับ
อาร์ต

ดาวน์โหลดไฟล์ Data ไปก่อนครับ แล้วค่อยทำตามคำตอบโพสต์ถัดไป

อธิบายพอเข้าใจน่ะครับ RangeName คือ การกำหนดชื่อของช่วงข้อมูล เช่น เรากำหนดชื่อช่วงข้อมูล A1:A10 ว่า "MyRange" โปรแกรมก็จะจดจำช่วงข้อมูลนี้ไว้ เราสามารถใช้ชื่อที่เราตั้งไว้นี้ แทนค่าลงไปในสูตรได้ เช่น
=Sumif(MyRange,A2,MyValue) เป็นต้น
RangeName สามารถสร้างได้หลายแบบ ตามความสามารถของผู้ใช้ ดังนี้
1. RangeName ที่มีขอบเขตแน่นอนชัดเจน ไม่เปลี่ยนแปลง
2. RangeName ที่เปลี่ยนแปลงโดยการเพิ่มหรือลด ตามจำนวนข้อมูลจริง ที่เรียกว่า Dynamic

RangeName ยังสมารถทำให้มันมีความสามารถได้อีก หากเรารู้หลักการของมันจริงๆ
ส่วนที่ผมจะตอบคือการทำ RangeName แบบ Dynamic ซึ่งจะเหมาะสำหรับการใช้กับฐานข้อมูลที่มีการเพิ่มของข้อมูลตลอดเวลา โดยให้มันกำหนดช่วงของข้อมูลตามจริง เช่น ข้อมูลมี 1000 บรรทัด RangeName ชนิดนี้ ก็จะกำหนดช่วงไว้ 1000 บรรทัดเช่นกัน

ตอบตามโจทย์ในโพสต์ด้านบน
โหลดไฟล์ Data แล้วเปิดขึ้นมา
1. ไปที่แท็บ Formulas เลือก NameManager กด New จะปรากฏหน้าต่างใหม่ ให้ตั้งชื่อว่า "num" ในช่อง Refer To คีย์ =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1) แล้วกด Ok
2. ทำตามข้อ 1 โดยตั้งชื่อว่า "amt" คีย์สตร =OFFSET(Sheet1!$B$2,0,0,COUNTA(num)) แล้วกด OK แล้วออกจากหน้าต่าง NameManager

3. การทำ DataValidation (DropDown) เพื่อเลือกรหัสมาแสดงข้อมูล โดยคลิ๊กที่ D2 แล้วไปที่แท็บ Data เลือก DataValidation > DataValidation จะปรากฏหน้าต่าง DataValidation ในช่อง Allow เลือกเป็น List ในช่อง Source คีย์ =num กด OK จะได้ DropDown

4. แสดงค่าของรหัสที่เราเลือก ที่ E2 คีย์ =SUMIF(num,D2,amt)

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

สงสัยสูตรไหนอย่างไร ค่อยตั้งคำถามมาอีกทีครับ

ขอบคุณคุณแบงค์มากเลยครับ กระจ่างตามที่คุณแบงค์สอนเลย ขอให้สุขภาพแข็งแรงครับ
อาร์ต

lotto009 wrote:ขอบคุณคุณแบงค์มากเลยครับ กระจ่างตามที่คุณแบงค์สอนเลย ขอให้สุขภาพแข็งแรงครับ
อาร์ต

ขอบคุณครับ ขอให้สุขภาพแข็งแรงเช่นกันครับ