Snasui Excel Forum

ต้องการสูตรผลรวมแบบมีเงื่อนไขหรือสูตรใดใดครับ จากรูปต้องการผลรวมในคอลัม G
เงื่อนไขคือ
Location
Start Time
End Time

ครับ

ลองใช้ sumif แล้วใช้ไม่ได้ครับ
อ้างอิงเงื่อนไขไม่ได้ครับ

ตามที่ทำตัวอย่างคำตอบมา ค่าใน G8 ช่วงเซลล์คือ O3543:O3578

ในช่วง L3543:L3578 และ M3543:M3578 ไม่มีวันที่และเวลาที่เป็นไปตามเงื่อนไขที่ E8 และ F8 คำตอบควรเป็น 0 ครับ

ขอโทษครับ คีย์ตัวเลขผิด
ของใหม่ดังนี้

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

G5 คีย์

=SUMIFS(INDEX($N$4:$T$4904,0,MATCH($D5,$N$3:$T$3,0)),$L$4:$L$4904,">="&INT($E5),$L$4:$L$4904,"<="&INT($F5),$M$4:$M$4904,">="&MOD($E5,1),$M$4:$M$4904,"<="&MOD($F5,1))

Enter > Copy ลงด้านล่าง

อีกตัวอย่างครับ
G5=SUMPRODUCT((TEXT(L$4:L$4904+M$4:M$4904,"d/mm/yyyy hh:mm:ss")+0>=E5)*(TEXT(L$4:L$4904+M$4:M$4904,"d/mm/yyyy hh:mm:ss")+0<=F5)*(D5=N$3:T$3),N$4:T$4904)

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

ผลจากการคำนวณดังรูปด้านล่าง

สูตรตาม #4 ไม่น่าที่จะตรงบ้างไม่ตรงบ้างครับ

ลองแนบไฟล์นั้นมาจะได้ช่วยดูให้ได้ ทางที่ดีให้ทดสอบกับข้อมูลจำนวนหนึ่งที่ไม่มากนักจะได้ตรวจสอบได้ง่าย ๆ

วิธีการตรวจสอบสามารถใช้การ Filter มาช่วยได้ครับ

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

แนวคิดของผม(ไม่ดี)
คือ นำข้อมูลที่ได้จาก การรวมไฟล์ ของ power qurey มาทำ pivot table
ดังรูปด้านล่าง การใช้ pivot table นี้ จะให้โชวได้เป็นรายเดือนเท่านั้น เพราะรายเดือนจะมีบรรทัด ถึง 50,000 บรรทัด
ถ้าให้โชว์ราย ปี เครื่องจะฟ้องว่าทรัพยากรไม่พอ
นี่เป็นปัญหาข้อหนึ่ง ถ้าจะหาผลลัพธ์ข้อมูลทั้งปี ผมก็ต้องคลิก "ที่ละเดือน" แล้วนำผลลัพธ์ที่ได้ไปเก็บในตาราง

การหาผลลัพธ์นั้นดังรูปด้านล่าง การหาผลลัพธ์ เวลาของแต่ละสถานที่จะไม่เหมือนกัน
ผมมีประมาณ 50 สถานที่
จึงทำ 50 sheet เพื่อคำนวณหาแต่ละสถานที่ และใช้สูตรที่ได้มาคำนวณหาค่าในแต่ละช่วงเวลา
โดยให้มาอ้างอิงค่า ในตาราง pivot table มาคำนวณค่าค่าผลรวมในแต่ละบรรทัด
ปัญหาที่ผลในการคัดลอกสูตรนี้ลงไปด้ายล่าง ประมวลผลช้ามากมาก 1ชั่วโมงก็ยังไม่เสร็จ
และถ้า ประมวณผลแล้วเสร็จ เวลาจะทำอะไรเพิ่มก็จะคำนวณซ้ำอีก ทำให้ทำอะไรไม่ได้เลย

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

จึงใคร่ขอแนวคิดว่าจะทำแบบไหนดี

ได้ทำผ่าน Data Model เข้า PowerPivot หรือไม่ ที่ถามเพราะไม่เห็นกล่าวถึง และถ้าไม่ เหตุใดไม่ทำผ่าน PowerPivot ครับ

เครื่องที่บ้านเป็นโนตบุค ไม่มี PowerPivot ครับ แต่ในโนตบุค สามารถ รีเพรสข้อมูลจาก add Data Model
ได้ปกติถ้าข้อมูลมีการเพิ่มมาใน foder

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

จึงสรุปได้ว่าเครื่องที่ทำงานไม่ว่าจะใช้ PowerPivot หรือ Power Qurey เมื่อ มีการเพิ่มข้อมูล แล้ว
ทำการ รีเพรส all เครื่องจะฟ้องว่าทรัพยากรมีจำกัด น่าจะเป็นที่ความสามารถของเครื่องรึเปล่าครับ
ข้อสงสัย
1.ในตอนที่ใช้เมื่อข้อมูลมีระดับหนึ่งไม่มากไปเครื่องจะทำงานได้ครับ
แต่พอมีข้อมูลเพิ่มขึ้นเครื่องจะคำนวณไม่ได้
2.ไฟล์ที่อยู่ในโนตบุคใช้ Power Qurey รีเพรสข้อมูล ได้ครับ แต่เมื่อนำไฟล์นี้ไปใช้ในคอมที่ทำงานคอมตั้งโต๊ะ จะรีเพรสไม่ได้ฟ้องว่าทรัพยากรมีจำกัด

เพราะสาเหตุเหล่านี้จึงไม่ได้ใช้คอมที่ทำงานจึงไมสามารถทำ PowerPivot ได้ครับ เพราะไม่สามรถเพิ่มข้อมูลได้

PowerPivot สามารถจัดการข้อมูลระดับ 100 ล้านบรรรทัดได้ ไม่แน่ใจว่า Excel ที่ใช้เป็นกี่ Bit ถ้าเป็น 64bit น่าจะทำงานได้ครับ

วันจันทรจะรายงานให้ทราบครับ
เรียนอาจารย์ครับ
ตอนนี้แค่ ลิงคธรรมดา เช่น b4 ลิงคไป aa4 เป็นต้น บางคอลัมมี if ด้วย
แต่มี 50000 บรรทัดต่อหน้า หน้าละ 7 คอลัม มีทั้งหมด 26 หน้า
สั่งให้คำนวณตอน seve
เวลาเริ่ม seve 13.00 น.
บัดนี้ยังไม่เสร็จเลย
ต่อไปภายหน้า ถ้าจะเพิ่มแถวหรือแค่คลิกเปลี่ยนข้อมูล
มันก็ต้องใช้เวลาในการแสดงค่านานแบบนี้แน่เลย เพราะในตอนนี้คำนวณค่าได้ 50% เอง
ไม่รู้จะใช้วิธีไหนดี

นี่ขนาดยังไม่ใส่สูตรที่ซับซ้อนของอาจารย์เลย

ผมคิดว่าจะลองไปทำเครื่องที่ทำงานอีกที่หนึ่งก่อนครับ
ถ้าได้ผลอย่างไรจะเรียนให้ทราบอีกที่หนึ่ง

snasui wrote: Sat Aug 08, 2020 11:49 am ได้ทำผ่าน Data Model เข้า PowerPivot หรือไม่ ที่ถามเพราะไม่เห็นกล่าวถึง และถ้าไม่ เหตุใดไม่ทำผ่าน PowerPivot ครับ

ได้ทำผ่าน Data Model เข้า PowerPivot แล้วดังรูป

____________________________________________________________________________________________________


จากรูป
ต้องการให้แสดงค่าในคอลัม L
โดยเงื่อนไขอยูุ่ที่ start time, end time
และข้อมูลอยู่ที่ตาราง data_m ค่าที่นำมาคำนวณในคอลัม kw ครับ
ดังรูปด้านล่าง

ไฟล์แนบครับ
ไฟลสมุดงาน1 เป็นไฟล์ทีทใช้ทำงานโดยทำผ่าน Data Model เข้า PowerPivot
ส่วนอีก 2 ไฟล์ที่เหลือเป็ไฟลที่นำมาทำตาราง add date model

ไม่ทราบว่าต้องการทำอย่างไรต่อครับในการหาค่าผลรวมแบบเป็นช่วงๆ

ในไฟล์ที่แนบมายังไม่มีการสร้าง PivotTable ไม่มีการเขียน DAX Formula ยอดเซลล์ผลลัพธ์ในไฟล์แนบไม่ตรงกับยอดตามภาพที่แนบมาด้านบน

ยอดเซลล์ผลลัพธ์ในไฟล์แนบไม่ตรงกับยอดตามภาพที่แนบมาด้านบน ?

ตอนนี้ได้ปรับให้ถูกต้องแล้วครับ
ต้องขอโทษครับพอดีลบข้อมูลแล้วไม่ได้ตรวจสอบให้ดี

ในไฟล์ที่แนบมายังไม่มีการสร้าง PivotTable ?

ผมไม่รู้จริงๆครับต้องสร้าง PivotTable จากตารางไหน

ไม่มีการเขียน DAX Formula ?

สูตร ที่ว่าผมไม่เข้าใจครับ
จากรูป พยายมจะใส่สูตรในข้อลัม
แต่ก็error ครับ

ตัวอย่าง DAX ครับ

=CALCULATE(SUM(data_e[Energy(kWh)]),FILTER('data_e','data_e'[Start Time]=EARLIER('data_e'[Start Time]) && 'data_e'[End Time] = EARLIER('data_e'[End Time])))

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

=CALCULATE(SUM(data_m[kW]),FILTER(data_m,data_m[Date/Time]=EARLIER(data_e[Start Time]) && data_m[Date/Time] = EARLIER(data_e[End Time])))

คำตอบ ไม่แสดงครับ มันแสดงค่าว่างดังรูปดานล่าง (สูตรที่ใช้ด้านบนครับ) .


อธิบายดังนี้
ดูจาก power pivot

ค่าที่นำมาใช้นำมาคำนวณ หาคำตอบ คือ คอลัม kW ใน table "data_m"ดังรูป .


.
ส่วนข้อแม้ มี 2 ข้อแม้ คือจะเริ่มต้นที่คอลัม Start Time จนถึง คอลัม End Time ในแต่ละ record
(ข้อแม้ คอลัม Start Time และ คอลัม End Time ใน table "data_e")
โดยข้อแม้ทั้งสองจะอ้างอิงกับ คอลัม Date/Time ใน table "data_m"
เพื่อไปหาค่าใน คอลัม kW ใน table "data_m"

จึงใช้สูตรดังนี้

=CALCULATE(SUM(data_m[kW]),FILTER(data_m,data_m[Date/Time]=EARLIER(data_e[Start Time]) && data_m[Date/Time] = EARLIER(data_e[End Time])))

คำตอบ ไม่แสดงครับ มันแสดงค่าว่าง

ไฟล์แนบ
เนื้อหาในไฟล์ไม่ใช้

ในขั้นนี้ใช้กับ

power pivot ครับ