Snasui Excel Forum

ต้องการสรุปข้อมูลจากซีท Database มาที่ซีท "สรุป" โดยแสดงจำนวนของแต่ละรายการลงในแต่ละช่องของวัน เช่น รายการ A ช่องของวันที่2 มีจำนวนเท่ากับ 2 ช่วยดูสูตรให้หน่อยครับ

ลองตามนี้ครับ

ที่ C9 คีย์
=SUMPRODUCT(--(DAY(Database!$B$4:$B$160)=C$7),--(TEXT(Database!$B$4:$B$160,"ดดดด")=$T$4),--(Database!$A$4:$A$160=$V$4),--(Database!$C$4:$C$160=$B9),Database!$D$4:$D$160)
คัดลอกไปทางขวามือจนสุดวันที่ 31 แล้วคัดลอกลงมาพร้อมกันครับ

สามารถเปลี่ยนชื่อ เดือน และ ปี ได้ตามต้องการครับ

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

โปรแกรมคำนวนช้าเป็นเรื่องธรรมดาครับ เพราะเป็นสูตรอาร์เรย์ อีกทั้งเงื่อนไขก็เยอะด้วย

ลองดูตามไฟล์แนบครับ

ลองแบบสูตรธรรมดาครับ
1.หาช่วงวันที่ของเดือน ที่ชีท Database
j2 พิมพ์สูตร
= DATEVALUE("1/"&สรุป!$T$4&"/"&สรุป!$V$4-543)
k2 พิมพ์สูตร
=DATE(YEAR($J$2),MONTH($J$2)+1,0)

2.หา index ไว้ดึงข้อมูล
k4 พิมพ์สูตร
=IF(AND($B4>=$J$2,$B4<=$K$2),$C4&DAY($B4),"")
enter> copy ลงด้านล่าง

3.ที่ชีท สรุป c9 พิมพ์สูตร
=IF(COUNTIF(Database!$K$4:$K$151,$B9&C$7)=0,"",INDEX(Database!$D$4:$D$151,MATCH($B9&C$7,Database!$K$4:$K$151,0)))
enter> copy ไปทางขวา และ ลงด้านล่าง

tupthai wrote:ลองแบบสูตรธรรมดาครับ
1.หาช่วงวันที่ของเดือน ที่ชีท Database
j2 พิมพ์สูตร
= DATEVALUE("1/"&สรุป!$T$4&"/"&สรุป!$V$4-543)
k2 พิมพ์สูตร
=DATE(YEAR($J$2),MONTH($J$2)+1,0)

2.หา index ไว้ดึงข้อมูล
k4 พิมพ์สูตร
=IF(AND($B4>=$J$2,$B4<=$K$2),$C4&DAY($B4),"")
enter> copy ลงด้านล่าง

3.ที่ชีท สรุป c9 พิมพ์สูตร
=IF(COUNTIF(Database!$K$4:$K$151,$B9&C$7)=0,"",INDEX(Database!$D$4:$D$151,MATCH($B9&C$7,Database!$K$4:$K$151,0)))
enter> copy ไปทางขวา และ ลงด้านล่าง

ผมมาดูเงื่อนไขใหม่อีกครั้ง พบว่าในหนี่งวัน เช่น กิจกรรม A ในวันที่ 9 มีมากกว่าหนึ่งรายการจึงปรับสูตรใหม่เป็น

c9 พิมพ์สูตร
=IF(COUNTIF(Database!$K$4:$K$151,$B9&C$7)=0,"",SUMIF(Database!$K$4:$K$151,$B9&C$7,Database!$D$4:$D$151))
enter> copy ไปทางขวา และ ลงด้านล่าง

ขอบคุณครับ คุณTupthai และ คุณ bank9597 มากๆเลยครับได้ทดลองกับข้อมูลมากๆดูแล้วเร็วขึ้นกว่าเดิมเลยครับ แต่ก็ไม่ค่อยสะดวกมากเท่าไรที่ต้องคีย์สูตรไว้ที่ฐานข้อมูลให้ครอบคลุมล่วงหน้าไว้ก่อน ถ้าได้แบบเดิมก็จะดีไม่น้อยเลยครับ

joo wrote:ขอบคุณครับ คุณTupthai และ คุณ bank9597 มากๆเลยครับได้ทดลองกับข้อมูลมากๆดูแล้วเร็วขึ้นกว่าเดิมเลยครับ แต่ก็ไม่ค่อยสะดวกมากเท่าไรที่ต้องคีย์สูตรไว้ที่ฐานข้อมูลให้ครอบคลุมล่วงหน้าไว้ก่อน ถ้าได้แบบเดิมก็จะดีไม่น้อยเลยครับ

ให้แยกเก็บข้อมูลวันที่ออกมาก่อนครับ เช่น 1/03/2012 ก็ให้แยกเก็บข้อมูล วันที่ เดือน ปี ออกจากกัน เวลานำข้อมูลไปใช้ ก็จะทำให้เร็วขึ้น เนื่องจากสูตรไม่ต้องแปลงค่าจากวันที่ก่อน ก่อนจะนำไปเปรียบเทียบกับเงื่อนไข ซึ่งมีผลทำให้การทำงานช้าลงอย่างหลีกเลี่ยงไม่ได้ แต่ในทางปฎิบัติแล้วไม่มีค่อยมีใครแยกเก็บข้อมูลแบบนั้น เพราะมันจะเปลืองคอลัมน์โดยเปล่าประโยชน์ เว้นแต่ว่าเราต้องการใช้เฉพาะกิจจริงๆ เราถึงจะทำแบบนั้นได้

ในชีท database มีข้อมูลวันที่อยู่ 1 คอลัมน์ การจะใช้สูตรให้ได้ผลลัพธ์ที่เร็วขึ้นก็ควรแยกวันที่ เดือน ปี ออกมาอยู่คนล่ะคอลัมน์กัน โดยการใช้สูตร Day,Month,Year แล้วคัดลอกค่าที่ได้ วางทับที่เดิมอีกครั้งโดยการ "วางค่า" ที่ทำเช่นนี้เพราะว่า ฐานข้อมูลที่ดีไม่ควรมีสูตรอยู่ในชีทเก็บข้อมูล มันจะทำให้การทำงานของโปรแกรมช้านั่นเองครับ

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

bank9597 wrote:ในชีท database มีข้อมูลวันที่อยู่ 1 คอลัมน์ การจะใช้สูตรให้ได้ผลลัพธ์ที่เร็วขึ้นก็ควรแยกวันที่ เดือน ปี ออกมาอยู่คนล่ะคอลัมน์กัน โดยการใช้สูตร Day,Month,Year แล้วคัดลอกค่าที่ได้ วางทับที่เดิมอีกครั้งโดยการ "วางค่า" ที่ทำเช่นนี้เพราะว่า ฐานข้อมูลที่ดีไม่ควรมีสูตรอยู่ในชีทเก็บข้อมูล มันจะทำให้การทำงานของโปรแกรมช้านั่นเองครับ

ปกติแล้วใน Database ไม่จำเป็นต้องแยกวัน เดือน ปี ออกเป็นคนละคอลัมน์ครับ เพราะจะยุ่งยากลำบากในการสรุปข้อมูลด้วยวิธีอื่น ๆ เช่น PivotTable และไม่ควรจะมี วันเดือนปี แล้วแยกเป็น วันที่ เดือน ปี รวมเป็น 4 คอลัมน์เช่นนี้ครับ

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

สรุปแล้วควรใช้วิธีไหนดีครับท่านอาจารย์...จึงจะได้ผลการคำนวณที่รวดเร็ว

ที่ชีทสรุปเซลล์ C9 ลองปรับสูตรเป็นตามด้านล่างครับ

=SUMPRODUCT(--((C$7&$T$4&$V$4-543)+0=Database!$B$4:$B$151),--($B9=Database!$C$4:$C$151),Database!$D$4:$D$151)

Enter > Copy ไปทางขวาและลงด้านล่าง

ขอบคุณครับท่านอาจารย์...ทดลองกับข้อมูลปริมาณมากๆแล้วเร็วขึ้นกว่าเดิมครับ
แต่ว่าถ้าเลือกเป็นเดือนอื่นๆที่ไม่ครบ 31 วัน เช่น เดือน กุมภาพันธ์ ที่เซลล์ AF,AG จะแสดง #VALUE! ทำให้การคำนวณที่เซลล์ AH แสดง #VALUE! ตามครับ

ที่ C9 ลองปรับสูตรตามนี้
=IF(ISERROR(SUMPRODUCT(--((C$7&$T$4&$V$4-543)+0=Database!$B$4:$B$151),--($B9=Database!$C$4:$C$151),Database!$D$4:$D$151))=TRUE,"",SUMPRODUCT(--((C$7&$T$4&$V$4-543)+0=Database!$B$4:$B$151),--($B9=Database!$C$4:$C$151),Database!$D$4:$D$151))

คัดลอกไปทางขวามือ

ขอบคุณครับทดลองแล้วสามารถแก้ปัญหา #VALUE! ได้ดี....แต่ผลข้างเคียงที่ตามมาคือคำนวณช้าลงกว่าเดิมอีกครับ

เมื่อใช้ Iserror, Iserr, Isnumber ฯลฯ ซึ่งเป็นการตรวจสอบค่าว่าจริงหรือเท็จแล้ว เราไม่จำเป็นต้องเขียนเปรียบเทียบว่าเท่ากับ True หรือ False อีกรอบครับ เพราะมันให้ผลลัพธ์เป็น True หรือ False อยู่แล้ว

จาก

bank9597 wrote: ที่ C9 ลองปรับสูตรตามนี้
=IF(ISERROR(SUMPRODUCT(--((C$7&$T$4&$V$4-543)+0=Database!$B$4:$B$151),--($B9=Database!$C$4:$C$151),Database!$D$4:$D$151))=TRUE,"",SUMPRODUCT(--((C$7&$T$4&$V$4-543)+0=Database!$B$4:$B$151),--($B9=Database!$C$4:$C$151),Database!$D$4:$D$151))

ควรจะเป็น
=IF(ISERROR(SUMPRODUCT(--((C$7&$T$4&$V$4-543)+0=Database!$B$4:$B$151),--($B9=Database!$C$4:$C$151),Database!$D$4:$D$151)),"",SUMPRODUCT(--((C$7&$T$4&$V$4-543)+0=Database!$B$4:$B$151),--($B9=Database!$C$4:$C$151),Database!$D$4:$D$151))

joo wrote:ขอบคุณครับท่านอาจารย์...ทดลองกับข้อมูลปริมาณมากๆแล้วเร็วขึ้นกว่าเดิมครับ
แต่ว่าถ้าเลือกเป็นเดือนอื่นๆที่ไม่ครบ 31 วัน เช่น เดือน กุมภาพันธ์ ที่เซลล์ AF,AG จะแสดง #VALUE! ทำให้การคำนวณที่เซลล์ AH แสดง #VALUE! ตามครับ

หากข้อมูลปริมาณมากและไม่ต้องการเพิ่มความซับซ้อนของสูตรให้ปรับสูตรในคอลัมน์ AH เป็น Sumif แบบไม่รวมค่าผิดพลาด และใช้ Conditional Formatting เพื่อซ่อนค่าผิดพลาดเอาไว้ก็ได้ครับ ดูตัวอย่าง Sumif แบบไม่รวมค่าผิดพลาดที่นี่ครับ http://snasui.blogspot.com/2008/11/blog-post_9829.html

เมื่อใช้ Iserror, Iserr, Isnumber ฯลฯ ซึ่งเป็นการตรวจสอบค่าว่าจริงหรือเท็จแล้ว เราไม่จำเป็นต้องเขียนเปรียบเทียบว่าเท่ากับ True หรือ False อีกรอบครับ เพราะมันให้ผลลัพธ์เป็น True หรือ False อยู่แล้ว

ขอบคุณครับสำหรับคำแนะนำ

หากข้อมูลปริมาณมากและไม่ต้องการเพิ่มความซับซ้อนของสูตรให้ปรับสูตรในคอลัมน์ AH เป็น Sumif แบบไม่รวมค่าผิดพลาด และใช้ Conditional Formatting เพื่อซ่อนค่าผิดพลาดเอาไว้ก็ได้ครับ

ตรง Conditional Formatting เพื่อซ่อนค่าผิดพลาด กำหนดการซ่อนค่าอย่างไรครับ

ลองตามนี้ครับ
สมมุติต้องการซ่อนค่าผิดพลาดที่เซลล์ A1:A10

1. คลุม A1:A10
2. เข้าเมนู Format > Conditional Formatting
3. ที่ Condition 1 เลือก Formula Is
4. ที่ช่องถัดมาคีย์ =Iserr(a1)
5. คลิกปุ่ม Format > กำหนดสีของตัวอักษรเป็นสีพื้น > OK > OK

ขอบคุณครับท่านอาจารย์ลองตั้งนานที่แท้ใช้สูตรนี้นี่เอง