Excel Forum

joo wrote:อาจารย์ครับ...ช่วยอธิบาย 3 สูตรนี้ให้หน่อยครับมันทำงานยังไงครับจะได้นำไปประยุกต์ใช้ต่อได้ถูก
1. =LOOKUP(9.99999999999999E+307,B$7:B7)

หมายถึงหาตัวเลขสุดท้ายในช่วงข้อมูล B$7:B7 ซึ่งจะเห็นว่า B7 ตัวหลังไม่ได้มีการ Lock หมายเลขบรรทัด หาก Copy ไปด้านล่างตัวเลขจะ Run ไปเรื่อย ๆ นั่นหมายถึงเพิ่มช่วงข้อมูลไปเรื่อย ๆ และเรากำลังหาตัวเลขสุดท้ายจากช่วงข้อมูลนั้นครับ

2. =IF(C6="","",LOOKUP(2,1/(($B6=Report!$I$7:$I$36)*($C6=Report!$C$7:$C$36)),Report!$F$7:$F$36))

หมายถึงหาค่าที่น้อยกว่าหรือเท่ากับ 2 จากช่วง 1/(($B6=Report!$I$7:$I$36)*($C6=Report!$C$7:$C$36)) แล้วนำค่าที่ตรงกันในช่วง Report!$F$7:$F$36 มาแสดง

จาก (($B6=Report!$I$7:$I$36)*($C6=Report!$C$7:$C$36)) จะให้ผลลัพธ์เพียง 2 ค่าครับ คือ True หรือ False และเมื่อใช้หนึ่งเป็นตัวตั้งเป็น

1/(($B6=Report!$I$7:$I$36)*($C6=Report!$C$7:$C$36)) ผลลัพธ์จะได้ 1 หรือ #Div/0!

ดังนั้นการหาค่าที่น้อยกว่าหรือเท่ากับ 2 จากช่วง 1/(($B6=Report!$I$7:$I$36)*($C6=Report!$C$7:$C$36)) จึงเท่ากับว่าเป็นการหาค่าเลข 1 ที่ปรากฎเป็นตัวสุดท้ายอยู่ที่ใด ให้นำค่าที่ตรงกันใน Report!$F$7:$F$36 มาแสดง

3.=INDEX(Report1!$D$6:$D$30,MATCH(1,IF(Report1!$C$6:$C$30=C23,IF(Report1!$B$6:$B$30=B23,1)),0))

สูตรนี้เป็นสูตร Array ความหมายคือจากช่วง Report1!$D$6:$D$30 ให้นำตำแหน่งที่ได้จากผลลัพธ์ของ MATCH(1,IF(Report1!$C$6:$C$30=C23,IF(Report1!$B$6:$B$30=B23,1)),0) มาแสดง

จาก MATCH(1,IF(Report1!$C$6:$C$30=C23,IF(Report1!$B$6:$B$30=B23,1)),0) หมายถึง ให้หาเลข 1 โดยที่กำหนดไว้ว่าถ้า Report1!$C$6:$C$30=C23 และ Report1!$B$6:$B$30=B23 แล้วให้แสดงเลข 1 ถ้าไม่เข้าเงื่อนไขให้แสดงค่า False

เมื่อเจอเลข 1 ในตำแหน่งใดก็ให้นำตำแหน่งเดียวกันของ Report1!$D$6:$D$30 มาแสดง

แล้วการใช้สูตรที่ดึงข้อมูลได้หลายเงื่อนไข เช่น Index, Match ซึ่งใช้แบบ Array หรือ Lookup มันแตกต่างกันอย่างไรตัวไหนทำงานได้เร็วและประหยัดทรัพยากรกว่ากันเมื่อข้อมูลมีปริมาณเพิ่มมากขึ้น
ขอบคุณครับ

การใช้สูตรแบบ Array โดยทั่วไปย่อมประมวลผลช้ากว่าสูตรแบบไม่เป็น Array แต่ตัวอย่างสูตร Lookup ที่ผมใช้แม้จะไม่ได้กดแป้นให้รับสูตรด้วย Ctrl+Shift+Enter แต่ Lookup เองมีสภาพเป็นสูตร Array โดยธรรมชาติ การใช้ตามที่ผมให้เป็นตัวอย่างไปนั้นก็ไม่ได้คำนวณเร็วในแบบสูตรทั่ว ๆ ไปครับ

ขอบคุณครับ พอเข้าใจนิดหน่อยครับมีข้อสงสัย 1 ข้อและคำถามเพิ่ม 1 ข้อครับ
1.จากสูตรข้างล่างนี้ที่อธิบายไว้ ผมสงสัยเลข 2 ตัวนี้ครับกำหนดมาจากไหนผมลองเปลี่ยนเป็นเลขอื่นตั้งแต่1-10 ค่าที่ลิงค์มาก็แสดงเหมือนกับที่เป็นเลข2
=IF(C6="","",LOOKUP(2,1/(($B6=Report!$I$7:$I$36)*($C6=Report!$C$7:$C$36)),Report!$F$7:$F$36))
2. จากซีท Main สูตรที่ D14 =IF(D12="","",IF(D13="","",IF(D15="ครึ่งวัน",0.5,SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(D12&":"&D13)),2)<6)))))
เมื่อมีการยกเลิกวันลาอยากให้จำนวนวันที่แสดงมีค่าติดลบต้องทำยังไงครับ เช่น เดิมทีลาไว้วันที่ 25/01/2011 ถึงวันที่ 27/01/2011 ทีนี้ต้องการยกเลิกวันลาวันที่27/01/2011 ก็จะคีย์วันที่ 27/01/2011 ลงในเซลล์ D12,D13 แล้วต้องการให้ค่าที่แสดงในเซลล์ D14 มีค่าติดลบตามจำนวนวันที่ยกเลิก เช่น -1

joo wrote:ขอบคุณครับ พอเข้าใจนิดหน่อยครับมีข้อสงสัย 1 ข้อและคำถามเพิ่ม 1 ข้อครับ
1.จากสูตรข้างล่างนี้ที่อธิบายไว้ ผมสงสัยเลข 2 ตัวนี้ครับกำหนดมาจากไหนผมลองเปลี่ยนเป็นเลขอื่นตั้งแต่1-10 ค่าที่ลิงค์มาก็แสดงเหมือนกับที่เป็นเลข2
=IF(C6="","",LOOKUP(2,1/(($B6=Report!$I$7:$I$36)*($C6=Report!$C$7:$C$36)),Report!$F$7:$F$36))

snasui wrote:

joo wrote:อาจารย์ครับ...ช่วยอธิบาย 3 สูตรนี้ให้หน่อยครับมันทำงานยังไงครับจะได้นำไปประยุกต์ใช้ต่อได้ถูก
1. =LOOKUP(9.99999999999999E+307,B$7:B7)

หมายถึงหาตัวเลขสุดท้ายในช่วงข้อมูล B$7:B7 ซึ่งจะเป็นว่า B7 ตัวหลังไม่ได้มีการ Lock หมายเลขบรรทัด หาก Copy ไปด้านล่างตัวเลขจะ Run ไปเรื่อย ๆ นั่นหมายถึงเพิ่มช่วงข้อมูลไปเรื่อย ๆ และเรากำลังหาตัวเลขสุดท้ายจากช่วงข้อมูลนั้นครับ

2. =IF(C6="","",LOOKUP(2,1/(($B6=Report!$I$7:$I$36)*($C6=Report!$C$7:$C$36)),Report!$F$7:$F$36))

หมายถึงหาค่าที่น้อยกว่าหรือเท่ากับ 2 จากช่วง 1/(($B6=Report!$I$7:$I$36)*($C6=Report!$C$7:$C$36)) แล้วนำค่าที่ตรงกันในช่วง Report!$F$7:$F$36 มาแสดง

จาก (($B6=Report!$I$7:$I$36)*($C6=Report!$C$7:$C$36)) จะให้ผลลัพธ์เพียง 2 ค่าครับ คือ True หรือ False และเมื่อใช้หนึ่งเป็นตัวตั้งเป็น

1/(($B6=Report!$I$7:$I$36)*($C6=Report!$C$7:$C$36)) ผลลัพธ์จะได้ 1 หรือ #Div/0!

ดังนั้นการหาค่าที่น้อยกว่าหรือเท่ากับ 2 จากช่วง 1/(($B6=Report!$I$7:$I$36)*($C6=Report!$C$7:$C$36)) จึงเท่ากับว่าเป็นการหาค่าเลข 1 ที่ปรากฎเป็นตัวสุดท้ายอยู่ที่ใด ให้นำค่าที่ตรงกันใน Report!$F$7:$F$36 มาแสดง

แลกเปลี่ยนกันตามที่ผมเข้าใจโดยอ้างถึงคำอธิบายสีแดงของคุณ snasui นะครับ ที่คุณ snasui เลือก 2 เพราะว่า ผลลัพธ์จะได้ 1 หรือ #Div/0! เท่านั้น และ LOOKUP จะคืนค่าที่มากที่สุดหรือใหญ่ีที่สุด โดยที่ค่าที่หาน้อยกว่าหรือเท่ากับค่าที่เรากำหนดให้หา ซึ่งจะทำให้เราได้ค่าตัวสุดท้าย ค่า 2 จึงถูกตามเงื่อนไขเสมอ ผมเข้าใจว่าใช้ 1.01 หรือ 1.1 ก็ได้แต่ 2 อาจจะสังเกตง่ายกว่าหรือเปล่า อันนี้ก็ไม่ทราบเหมือนกันครับ

joo wrote:ขอบคุณครับ พอเข้าใจนิดหน่อยครับมีข้อสงสัย 1 ข้อและคำถามเพิ่ม 1 ข้อครับ
1.จากสูตรข้างล่างนี้ที่อธิบายไว้ ผมสงสัยเลข 2 ตัวนี้ครับกำหนดมาจากไหนผมลองเปลี่ยนเป็นเลขอื่นตั้งแต่1-10 ค่าที่ลิงค์มาก็แสดงเหมือนกับที่เป็นเลข2
=IF(C6="","",LOOKUP(2,1/(($B6=Report!$I$7:$I$36)*($C6=Report!$C$7:$C$36)),Report!$F$7:$F$36))

ดูจากที่ผมอธิบายไว้ตรงนี้ครับ ชัดเจนแล้วครับ

หมายถึงหาค่าที่น้อยกว่าหรือเท่ากับ 2 จากช่วง 1/(($B6=Report!$I$7:$I$36)*($C6=Report!$C$7:$C$36)) แล้วนำค่าที่ตรงกันในช่วง Report!$F$7:$F$36 มาแสดง

เป็นการหาค่าที่น้อยกว่าหรือเท่ากับค่าที่ต้องการ ตราบใดเงื่อนไขที่ต้องการทดสอบมีค่ามากกว่าหรือเท่ากับ 1/(($B6=Report!$I$7:$I$36)*($C6=Report!$C$7:$C$36)) ก็จะได้คำตอบเสมอ ที่ไม่ใส่เลขอื่นเพราะต้องการให้ง่ายและเป็นตัวเร่งให้หาเร็วขึ้น เพราะเป็นเลขที่มากกว่าค่าของ 1/(($B6=Report!$I$7:$I$36)*($C6=Report!$C$7:$C$36)) ครับ

2. จากซีท Main สูตรที่ D14 =IF(D12="","",IF(D13="","",IF(D15="ครึ่ง
วัน",0.5,SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(D12&":"&D13)),2)<6)))))
เมื่อมีการยกเลิกวันลาอยากให้จำนวนวันที่แสดงมีค่าติดลบต้องทำยังไงครับ เช่น เดิมทีลาไว้วันที่ 25/01/2011 ถึงวันที่ 27/01/2011 ทีนี้ต้องการยกเลิกวันลาวันที่27/01/2011 ก็จะคีย์วันที่ 27/01/2011 ลงในเซลล์ D12,D13 แล้วต้องการให้ค่าที่แสดงในเซลล์ D14 มีค่าติดลบตามจำนวนวันที่ยกเลิก เช่น -1

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

ใครช่วยทำสูตรในการคิดค่าพื้นที่ให้ผมได้บางครับจะใช้สูตร IF หรือ Lookup ก็ได้ครับช่วยที่ผมได้แนบตัวอย่างมาให้นะครับ

กรณีนี้เห็นว่าเป็นคำถามที่ไม่เกี่ยวเนื่องกันกับกระทู้นี้ รบกวนคุณ c_parenya ช่วยตั้งกระทู้ใหม่ครับ

เข้าใจกระจ่างแล้วครับอาจารย์
ที่ซีท Main ผมกำหนดเงื่อนไขให้ F15="ยกเลิก" ส่วนที่ D14 ใส่สูตรแบบนี้ครับ
=IF(D12="","",IF(D13="","",IF(D15="ครึ่งวัน",0.5,IF(F15=
"ยกเลิก","-",SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(D12&":"&D13)),2)<6))))))
ค่าที่แสดงออกมากลับเป็นค่า "-" เฉยๆไม่มีจำนวนวันต่อท้ายเลยครับ อยากให้แสดงเครื่องหมายลบตามด้วยตัวเลขของจำนวนวัน เช่น -1 ต้องปรับแก้ตรงไหนครับ

แนวทางการเขียนสูตรจะเป็นตามตัวอย่างนี้ครับ

=If(D15="ยกเลิก",-1,1)*If(<--สูตรเดิม-->)

ทดลองแล้วใช้ได้ตามที่ต้องการครับแต่ถ้า D12,D13 เป็นค่าว่างอยู่ ที่ D14 มันจะแสดง #Value! ครับ เช็คขั้นตอนการประเมินดูพบว่าเป็นค่า1*"" จึงทำให้เกิดค่า #VALUE! ตรงนี้ปรับแก้ได้ไหมครับ

ใช้ IF ซ้อนเข้าไปอีกชั้นครับ เป็นเช่น

=If(And(D12="",D13=""),"",If(D15="ยกเลิก",-1,1)*If(<--สูตรเดิม-->))

ปรับจากสูตรเดิมจะได้เป็น

=IF(AND(D12="",D13=""),"",IF(D15="ยกเลิก",-1,1)*IF(D15="ครึ่งวัน",0.5,SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(D12&":"&D13)),2)<6))))

ที่ D14 ค่ายังคงแสดง #VALUE! ถ้า D12 หรือ D13 เป็นค่าว่างอยู่ แต่ถ้า D12,D13 เป็นค่าว่างทั้งคู่ D14 ก็แสดงค่าว่างให้ครับ

เป็น And เป็น Or ครับ

จาก =IF(AND(D12="",D13=""),"",... เป็น =IF(OR(D12="",D13=""),"",...

ขอบคุณครับ...ใช้งานได้ตามที่ต้องการแล้วครับ นึกไม่ถึงว่ามี Or ด้วยหลงไปใช้วิธีอื่นตั้งนาน

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

การสรุปข้อมูลลักษณะนี้ควรใช้ PivotTable เข้ามาช่วยแทนการเขียนสูตรจะสะดวกกว่าครับ

ผมลองสร้าง PivotTable ไว้ที่ซีท Report3 ค่าที่ได้สรุปมาเฉพาะยอดของแต่ละวันและของแต่ละปี แต่ค่าที่ได้ยังไม่ค่อยตรงคือมันต้องสรุปตามปีที่เราบันทึกไม่ใช้สรุปตามปีงบประมาณ ส่วนของแต่ละเดือนยังไม่ได้ครับ

กรณีต้องการแสดงรายการตามวันในเืดือน ให้เพิ่มข้อมูลดังกล่าวลงใน Database ด้วยครับ จากนั้น Refresh PivotTable แล้ว Group ข้อมูลตามวันที่กรอกข้อมูล ลองดูตัวอย่างตามภาพด้านล่างครับ

ขอบคุณครับทำได้แล้วครับ แต่ถ้าต้องการให้สรุปตามซีท Report2 โดยเลือกปี พ.ศ.ที่เซลล์ T2 ก็ให้ข้อมูลแสดงออกมาเลยทำได้ไหมครับ

ที่ถามนี่ต้องการใ้ช้สูตรดึงค่ามาแสดงตามตารางที่กำหนดไว้ล่วงหน้าใช่ไหมครับ

ถ้าใช่ ตอบว่าทำได้ด้วยสูตร Sumproduct เป็นต้นครับ ส่วนจะทำอย่างไรนั้นลองศึกษาสูตร Sumproduct ในฟอรั่มนี้ซึ่งมีจำนวนมาก ลองเขียนมาดูก่อน ติดขัดตรงไหนก็ถามกันเรื่อย ๆ ครับ

ใช่เลยครับอาจารย์ ต้องการให้แสดงในตารางที่ Report2
ที่ Report2 ตรง C5 ผมคีย์สูตรแบบนี้ครับ =IF(T2="","",SUMPRODUCT(--($B5=Report3!$C$8:$C$15),--(LOOKUP(CHAR(255),$C$4:C$4)=Report3!$D$7:$K$7))) ค่าที่ได้มันแสดง #N/A ครับ
ที่ Report3 ตรง M8:M16 ผมคีย์สูตรแบบนี้ =LOOKUP(9.99999999999999E+307,B$8:B8+0) ทำไมค่าปีจึงไม่เรียงกันลงมาครับ
ดู Sumproduct หลายตัวอย่างยังงงๆ อยู่ครับ