ปัญหา Excel ทำงานช้าครับ

[aniwat2011]

เรียน อาจารย์และผู้รู้ทุกท่านครับ
ผมได้สร้างไฟล์ Excel สำหรับควบคุมวัสดุ ที่รองรับรายการประมาณ 5,000 รายการ มีทั้งหมด 12 ชีท เมื่อสร้างเสร็จแล้วปัญหาที่พบคือ เครื่องจะประมวลผลช้า จะประมวลทุกครั้งเมื่อคีย์ข้อมูลรู้สึกไม่สะดวกและเป็นอุปสรรคครับ สูตรที่ใช้ ส่วนใหญ่ INDEX และ MATCH และอื่น ๆ เช่น SUMIF / IF / AGGREGATE / ROW / ROWS / OFFSET ครับ ไม่ทราบว่าจะต้องแก้ไขอย่างไรครับ

2022-02-26_180650.jpg

อยากจะแนบไฟล์ก็ไม่รู้ว่าจะดัดแปลงอย่างไรดีครับ ถ้าแนบก็ต้องแนบไฟล์จริงเลยครับ

ผมใช้ Windows10 / Excel2019
แต่สูตรที่ทำอยากให้ใช้ได้กับ Excel2010 ขึ้นไปครับ
ขอบคุณครับ

[aniwat2011]

เนื่องจากว่าไฟล์ใหญ่จึงไม่สามารถแนบไฟล์ได้ครับ

2022-02-26_183340.jpg

[snasui]

Zip แล้วเหลือขนาดไฟล์เท่าไรครับ หากไม่เกิน 500KB สามารถแนบไฟล์ Zip มาได้ครับ

[aniwat2011]

Zip แล้วมีขนาด 5,076 kb ครับอาจารย์

[snasui]

หากขนาดไฟล์เท่านั้นไม่ส่ามารถแนบผ่านฟอรัมได้ครับ

ลองส่งไปที่ snasui@gmail.com อ้างอิงถึงกระทู้นี้ไปด้วยครับ

[aniwat2011]

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

[snasui]

แก้ไข 3 เรื่องใหญ่ครับ

1. ปรับพื้นที่การใช้งานในชีต สรุปยอดคงเหลือ ให้เป็นไปตามการใช้จริง ปัจจุบันใช้ทั้งคอลัมน์ตั้งแต่ A:W จึงมีการสำรองพื้นที่ใช้งานไว้จำนวนมากดังภาพด้านล่าง วิธีแก้คือคลุมหัวบรรทัดว่างว่างตั้งแต่บรรทัดที่ 5005:1048576 > คลิกขวา > Delete > Ctrl + Home หรือเลือก A1 แล้ว Save จะเป็นการ Reset last cell ใหม่
2. สูตร Aggregate ต่าง ๆ ที่ใช้หาค่า Unique เช่น
   =IFERROR(INDEX(I:I,AGGREGATE(15,6,ROW($I$6:$I$5005)/(MATCH($I$6:$I$5005,$I$6:$I$5005,0)=ROW($I$1:$I$5005)),ROWS($R$6:R10))),"")
   สูตรนี้จะทำงานหนักมาก ให้เลือกใช้ Frequency แทน เช่นปรับเป็น
   =IFERROR(INDEX(I:I,AGGREGATE(15,6,ROW($I$6:$I$5005)/(0<FREQUENCY(MATCH("~"&$I$6:$I$5005,$I$6:$I$5005&"",0),ROW($I$6:$I$5005)-ROW($I$6)+1)),ROWS($R$6:R10))),"")
3. สูตร Aggregate แบบหาค่าหลายคอลัมน์ เช่น
   =IFERROR(INDEX('5.บันทึกจ่าย'!$C$4:$N$5004,AGGREGATE(15,6,(ROW('5.บันทึกจ่าย'!$D$5:$D$5004)-ROW('5.บันทึกจ่าย'!$D$5)+1)/('5.บันทึกจ่าย'!$D$4:$D$5004=$F$2),ROWS(B$9:B9)),MATCH(B$8,'5.บันทึกจ่าย'!$C$4:$N$4,0))," ")
   ให้สร้างคอลัมน์ช่วยเพื่อแสดงค่าบรรทัดที่เข้าเงื่อนไข นั่นคือแยก
   =AGGREGATE(15,6,(ROW('5.บันทึกจ่าย'!$D$5:$D$5004)-ROW('5.บันทึกจ่าย'!$D$5)+1)/('5.บันทึกจ่าย'!$D$4:$D$5004=$F$2),ROWS(B$9:B9))
   ออกมาแสดงต่างหาก 1 คอลัมน์ จากนั้นค่อยใช้ Index และ Match มาอ้างอิงคอลัมน์นี้ไปใช้ในการแสดงค่าคอลัมน์ใด ๆ ตามต้องการ

[aniwat2011]

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

[aniwat2011]

เรียน อาจารย์ที่เคารพ ครับ
ผมได้ดำเนินการแก้ไขตามข้อ 1 และ ข้อ 2 แล้วครับ ผมสามารถทำได้ แต่ข้อ 3. ผมยังไม่เข้าใจครับ
แต่ผมลองดูสูตรแล้วเป็นสูตรที่ใช้ในชีท "ใบเบิก" ซึ่งในชีทนี้การทำงานก็เร็วปกตินะครับ ส่วนชีทที่การทำงานช้า มีดังนี้ครับ
ชีท "2.ฐานข้อมูลวัสดุ"
"3.บันทึกยอดยกมา"
"4.บันทึกรับ"
"5.บันทึกจ่าย" และ
"บัญชีวัสดุ พด.4"
ซึ่ง 4 ชีทแรก ช้าในคอลัมน์ "รายการวัสดุ" และ ในชีทสุดท้ายช้าที่ "รหัสวัสดุ" ครับ
ซึ่งข้อมูลจะมาจากชีท "2.ฐานข้อมูลวัสดุ" ครับอาจารย์

[snasui]

3. ผมยังไม่เข้าใจครับ
แต่ผมลองดูสูตรแล้วเป็นสูตรที่ใช้ในชีท "ใบเบิก" ซึ่งในชีทนี้การทำงานก็เร็วปกตินะครับ

เนื่องจากใช้ยังไม่มากจึงอาจจะไม่เห็นความแตกต่าง และหากใช้เพื่อนำข้อมูลลง Form ยังพอใช้ได้ แต่หากนำไปสร้างเป็นตารางข้อมูลขนาดใหญ่ ควรปรับสูตรส่วนนี้ครับ

ผมทำเป็นตัวอย่างให้ใหม่และส่งไปทางอีเมล จากเดิมมีขนาด 17 MB ลดเหลือ 6 MB และเพื่อเป็นประโยชน์กับผู้ที่เข้ามาศึกษาในกรณีการทำไฟล์ที่ใช้สูตรจำนวนมากและแยกเป็นหลาย ๆ ชีต โดยเฉพาะงานนี้ใช้สูตรรวมกันเกือบ 5 แสนเซลล์ โดยหลัก ๆ จะมีการปรับดังนี้

1. กรณีต้องการนำค่าที่เข้าเงื่อนไขมาแสดง ให้สร้างเซลล์สำหรับนับว่ามีกี่รายการขึ้นมาใช้อ้างอิง
2. สูตรสำหรับแสดงรายการที่เข้าเงื่อนไขให้ตรวจสอบเสียก่อนว่าจำนวนบรรทัด ของการแสดงรายการเกินกว่าผลลัพธ์ตามข้อ 1 แล้วหรือไม่ (ปกติตรวจสอบด้วย IF(Rows(...) > ค่าตามข้อ 1 ,"",สูตรที่เกี่ยวข้อง)) หากเกินให้แสดงเป็นเซลล์ว่างทันทีจะไม่คำนวณต่ออีก ซึ่งต้องแสดงรายการเท่ากับจำนวนที่นับได้ในข้อ 1 เท่านั้น
3. ลดการใช้ฟังก์ชัน IFERROR เพราะฟังก์ชันนี้จะทำงานด้วยการนำผลลัพธ์ของสูตรที่เป็นส่วนประกอบของตัวมันเองมาแปลผลแล้วค่อยให้ผลลัพธ์ ควรจัดการการสูตรเดิมให้ทำงานตามที่ควรจะเป็นดังเช่นข้อ 1 และ 2 ที่กล่าวมา
4. ลำดับบรรทัดของรายการในคอลัมน์ใด ๆ ที่ได้จากฟังก์ชัน Match และนำไปใช้ร่วมกับ Index หลาย ๆ คอลัมน์ ให้แยก Match ออกมาแสดงต่างหาก แล้วค่อยอ้างอิงเข้าไปใช้ใน Index วิธีนี้จะเป็นการเขียน Match เพียงรอบเดียว แล้วเอาไปใช้หลายคอลัมน์แทนการ Match ค่าเดิม ๆ ทุก ๆ คอลัมน์

ช้าในคอลัมน์ "รายการวัสดุ" และ ในชีทสุดท้ายช้าที่ "รหัสวัสดุ" ครับ
ซึ่งข้อมูลจะมาจากชีท "2.ฐานข้อมูลวัสดุ" ครับอาจารย์

คอลัมน์ใดที่ไม่ได้เกิดจากสูตรคำนวณ ไม่ได้ช้าในคอลัมน์นั้นครับ

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

[aniwat2011]

ขอบคุณมาก ๆ ครับอาจารย์

[aniwat2011]

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

“ความต้องการ คือ นำข้อมูลจาก ชีท "เรียงรับจ่าย" คอลัมน์ D คอลัมน์ F,G,H มาทำ Dropdown List
แบบมีความสัมพันธ์กัน ที่ชีท "บัญชีวัสดุพ.ด.4" เซลล์ O8 ถึง O11
แล้วให้ข้อมูลในตารางเปลี่ยนแปลงตามที่เลือก Dropdown”

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

ขอขอบคุณอาจารย์เป็นอย่างสูงมา ณ โอกาสนี้ครับ

2022-03-05_04.jpg

[aniwat2011]

แต่สูตรที่ทำ อยากให้ใช้ได้กับ Excel2010 ขึ้นไปเหมือนเดิมครับ

[snasui]

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

[aniwat2011]

เรียน อาจารย์ที่เคารพ

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

บัญชีคุมวัสดุDropdown.xlsb

[snasui]

ตัวอย่างสูตรตามด้านล่างครับ

1. ที่ S9 คีย์
   =COUNT(AGGREGATE(15,6,ROW(เรียงรับจ่าย!F5:F36)/((0<FREQUENCY(IF(เรียงรับจ่าย!D5:D36=O8,MATCH(เรียงรับจ่าย!F5:F36,เรียงรับจ่าย!F5:F36,0)),ROW(เรียงรับจ่าย!F5:F36)-ROW(เรียงรับจ่าย!F5)+1))),ROW(1:50)))
   Ctrl+Shift+Enter
2. ที่ T9 คีย์
   =COUNT(AGGREGATE(15,6,ROW(เรียงรับจ่าย!F5:F36)/(0<FREQUENCY(IF(เรียงรับจ่าย!D5:D36=O8,IF(เรียงรับจ่าย!F5:F36=O9,MATCH(เรียงรับจ่าย!H5:H36,เรียงรับจ่าย!H5:H36,0))),ROW(เรียงรับจ่าย!F5:F36)-ROW(เรียงรับจ่าย!F5)+1)),ROW(1:50)))
   Ctrl+Shift+Enter
3. ที่ S11 คีย์
   =IF(ROWS(S$11:S11)>$S$9,"",INDEX(เรียงรับจ่าย!F:F,AGGREGATE(15,6,ROW(เรียงรับจ่าย!$F$5:$F$36)/(0<FREQUENCY(IF(เรียงรับจ่าย!$D$5:$D$36=$O$8,MATCH(เรียงรับจ่าย!$F$5:$F$36,เรียงรับจ่าย!$F$5:$F$36,0)),ROW(เรียงรับจ่าย!$F$5:$F$36)-ROW(เรียงรับจ่าย!$F$5)+1)),ROWS(S$5:S5))))
   Ctrl+Shift+Enter > Copy ลงด้านล่าง
4. ที่ T11 คีย์
   =IF(ROWS(T$11:T11)>$T$9,"",INDEX(เรียงรับจ่าย!H:H,AGGREGATE(15,6,ROW(เรียงรับจ่าย!$F$5:$F$36)/(0<FREQUENCY(IF(เรียงรับจ่าย!$D$5:$D$36=$O$8,IF(เรียงรับจ่าย!$F$5:$F$36=$O$9,MATCH(เรียงรับจ่าย!$H$5:$H$36,เรียงรับจ่าย!$H$5:$H$36,0))),ROW(เรียงรับจ่าย!$F$5:$F$36)-ROW(เรียงรับจ่าย!$F$5)+1)),ROWS(S$5:S5))))
   Ctrl+Shift+Enter > Copy ลงด้านล่าง
5. ที่ O11 คีย์
   =IFERROR(INDEX(เรียงรับจ่าย!G5:G36,MATCH(1,IF(เรียงรับจ่าย!$D$5:$D$36=$O$8,IF(เรียงรับจ่าย!$F$5:$F$36=$O$9,IF(เรียงรับจ่าย!H5:H36=O10,1))),0)),"")
   Ctrl+Shift+Enter

[aniwat2011]

ขอกราบอาจารย์แบบงาม ๆ 3 ครั้งเลยครับ ที่กรุณา

[aniwat2011]

บัญชีคุมวัสดุDropdownอาจารย์ตอบ.xlsb

เรียน อาจารย์ที่เคารพ
ผมได้ทดลองใช้ไฟล์งานแล้วครับ ติดปัญหาตรงที่ว่าข้อมูลจะลิ้งค์มาทั้งหมดที่เป็นรหัสเดียวกัน รวมทุกส่วนราชการ/กอง ซึ่งความต้องการคืออยากให้มันมาเฉพาะ ของกองนั้น ๆ ครับ
จากภาพ ถ้าเลือก หน่วยงานผู้เบิก "กองคลัง" ประเภท "วัสดุสำนักงาน" ชื่อหรือชนิดวัสดุ "ลวดเย็บกระดาษ No.10" รหัสวัสดุ "A006"
ก็ให้ข้อมูลในตารางลิ้งค์เฉพาะของกองคลังเท่านั้น ของกองอื่นไม่ต้องนำมาครับ
เดิมการลิ้งค์ข้อมูล มาจากชีท "สรุปคงเหลือ" ซึ่งไม่ได้แยกส่วนราชการ/กอง ไว้ครับ เปลี่ยนเป็นชีท "3.บันทึกยอดยกมา" ได้หรือไม่ครับ และข้อมูลการรับ - จ่าย ก็ลิ้งค์มาจากชีท "เรียงรับจ่าย" เปลี่ยนเป็นลิ้งค์มาจากชีท "4.บันทึกรับ" และชีท "5.บันทึกจ่าย" ได้หรือไม่ครับอาจารย์
กราบขออภัยอาจารย์เป็นอย่างสูงครับที่ผมถามหรือสื่อสารไม่เป็น และขอขอบคุณในความเมตตาที่อาจารย์มีต่อศิษย์ครับ
ขอบคุณครับ
อนิวรรต

2022-03-06_Dropdown.jpg

[snasui]

กรุณาทำตัวอย่างคำตอบที่ต้องการมาด้วยจะได้เข้าใจตรงกันครับ

[Bo_ry]

เรียงรับจ่าย U5:Uxx
=IF((D5=บัญชีวัสดุพ.ด.4!$O$8)*(F5=บัญชีวัสดุพ.ด.4!$O$9)*(G5=บัญชีวัสดุพ.ด.4!$O$11),ROWS(U$5:U5),"")

บัญชีวัสดุพ.ด.4 Q11
=COUNT(เรียงรับจ่าย!U5:U36)

บัญชีวัสดุพ.ด.4 Q15:Qxx
=IF(ROWS(Q$15:Q15)>$Q$11,"",SMALL(เรียงรับจ่าย!$U$5:$U$36,ROWS(Q$15:Q15)))