Excel Forum

จากไฟล์ sumif ผมจะรวมข้อมูล คอลัมน์ I5:I2000 โดยให้เป็นเงื่อนไขตามวัน ใน ไฟล์ sumif-cont เพื่อสรุปเป็นยอดของแต่ละวัน

แต่จากที่ทดลองทำดู พบว่าต้องเปลี่ยน เลขตามนี้ตลอดครับ =SUMIF($B$4:$B$2000,"30/10/11",$I$4:$I$2000) จะมีทางแก้ไขไหมครับที่จะอ้างอิงแบบเป็น เซลล์แทน เพราะว่า เมื่อเปลี่ยนเป็นเดือนใหม่แล้วผมต้องมานั่งแก้ไขวันที่ดังกล่าวอีก

ขอบคุณมากครับ

ไม่เข้าใจครับ

โดยปกติการเขียนสูตรสามารถอ้างอิงไปยังเซลล์ได้ทุกส่วนประกอบของสูตรครับ

จากสูตรที่เขียนมา เราสามารถเขียนได้เป็น เช่น

=SUMIF($B$4:$B$2000,A12,$I$4:$I$2000)

คือว่าตามปกติแล้วในแต่ละเดือน
ผมต้องรวมยอดของเข้าในแต่ละวัน แต่ว่า ถ้าเป็นการอ้างอิงแบบเซล์ มันจะรวมตามวันไม่ได้ครับเพราะว่า

่เช่น ไฟล์ sumif-cont เป็นยอด balance ในแต่ละเดือนที่ต้องรวมมาจาก ไฟลล์ sumif
- เช่น ตั้งแต่ เซลล์ B5:B2000 รวมยอดของวันที่ 30/10/11 มาทั้งหมด (I5:I2000) เพื่อให้นำมาเป็นผลรวมใน ไฟล์ sumif-cont ของวันที่ 30/10/11


ผมลืมบอกครับ ว่าเป็นการทำงานข้าม workbook ครับ ระหว่า workbook " sumif-cont และ sumif " ครับ

kamahltew wrote:คือว่าตามปกติแล้วในแต่ละเดือน
ผมต้องรวมยอดของเข้าในแต่ละวัน แต่ว่า ถ้าเป็นการอ้างอิงแบบเซล์ มันจะรวมตามวันไม่ได้ครับเพราะว่า

่เช่น ไฟล์ sumif-cont เป็นยอด balance ในแต่ละเดือนที่ต้องรวมมาจาก ไฟลล์ sumif
- เช่น ตั้งแต่ เซลล์ B5:B2000 รวมยอดของวันที่ 30/10/11 มาทั้งหมด (I5:I2000) เพื่อให้นำมาเป็นผลรวมใน ไฟล์ sumif-cont ของวันที่ 30/10/11

ที่ B9 ชีท Sumif-Count คีย์ =SUMIF([sumif.xls]NOVEMBER!$B$5:$B$580,$A9,[sumif.xls]NOVEMBER!$I$5:$I$580) คัดลอกลงมา

bank9597 wrote:

kamahltew wrote:คือว่าตามปกติแล้วในแต่ละเดือน
ผมต้องรวมยอดของเข้าในแต่ละวัน แต่ว่า ถ้าเป็นการอ้างอิงแบบเซล์ มันจะรวมตามวันไม่ได้ครับเพราะว่า

่เช่น ไฟล์ sumif-cont เป็นยอด balance ในแต่ละเดือนที่ต้องรวมมาจาก ไฟลล์ sumif
- เช่น ตั้งแต่ เซลล์ B5:B2000 รวมยอดของวันที่ 30/10/11 มาทั้งหมด (I5:I2000) เพื่อให้นำมาเป็นผลรวมใน ไฟล์ sumif-cont ของวันที่ 30/10/11

ที่ B9 ชีท Sumif-Count คีย์ =SUMIF([sumif.xls]NOVEMBER!$B$5:$B$580,$A9,[sumif.xls]NOVEMBER!$I$5:$I$580) คัดลอกลงมา

ลองแล้วครับ อย่างที่ต้องการเลย แต่
งงตรงนี้ครับ $A19 ช่วยอธิบายได้ไหมครับว่าทำไมต้องใช้แบบนี้

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

การล๊อคมีอยู่ 3 ประเภทคือ

$A$1 แบบนี้คือล๊อคอยู่กับที่ไม่ไปไหนเลย ไม่ว่าจะคัดลอกสูตรไปซ้าย-ขวา หรือ ลงล่าง
A$1 แบบนี้คือล๊อคให้อยู่กับที่ในกรณีที่คัดลอกสูลงลงล่าง แต่หากคัดลอกสูตรไปซ้าย หรือ ขวา ชื่อคอลัมน์ก็จะเปลี่ยนตามไปด้วย
$A1 แบบนี้อธิบายไปแล้วคือ ล๊อคให้อยู่กับคลัมน์นั้นๆ ไม่ว่าจะคัดลอกไปคอลัมน์ไหนก็ตาม มันก็ไม่เปลี่ยน จะเเปลี่ยนแปลงแค่แถวที่คัดลอกลงล่างเท่านั้น

ขอบคุณมากครับ รบกวนถามเพิ่มนะครับ

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

แนบที่ลองแล้วมาด้วยครับ

แนบเอกสารมาด้วยแล้วครับ
ไฟล์ sumif คอลัมน์ J อ้างอิง จาก ไฟล์ data

ชีท Sumif เซลล์ J9 คีย์ =SUMPRODUCT(--([data.xls]NOVEMBER!$Q$5:$Q$955="E"),--([data.xls]NOVEMBER!$B$5:$B$955=$A9),[data.xls]NOVEMBER!$I$5:$I$955) คัดลอกลงมา

เปิดไฟล์ Data ขึ้นมาก่อน ตามด้วยไฟล์ Sumif ครับ แล้วที่เซลล์ J9 คีย์
=SUMPRODUCT(--([data.xls]NOVEMBER!$Q$5:$Q$955="E"),--([data.xls]NOVEMBER!$B$5:$B$955=$A9),[data.xls]NOVEMBER!$I$5:$I$955) คัดลอกลงมา
ผมลองแล้วไม่มีข้อผิดพลาดแต่อย่างใด

ส่วนเครื่องหมาย -- ที่อยู่ในสูตรไม่ใช่สูตร if ครับ แต่เป็นการแปลงข้อมูลที่อยู่ในรูปแบบ Text ให้เป็น Number เพื่อที่สูตรสามารถจะคำนวนผลออกมาได้ เราสามารถเลือกที่จะไม่ใช้ครื่องหมาย -- ได้ โดยการใส่เครื่องหมาย * เชื่อมเงื่อนไขเข้าด้วยกันดังนี้
ไฟล์ Sumif เซลล์ J9 คีย์ =SUMPRODUCT(([data.xls]NOVEMBER!$Q$5:$Q$955="E")*([data.xls]NOVEMBER!$B$5:$B$955=$A9)*[data.xls]NOVEMBER!$I$5:$I$955) คัดลอกลงมา

หรือใช้สูตร sum(if) ก้ได้เช่นกัน ดังนี้
ไฟล์ Sumif เซลล์ J9 คีย์ =SUM(IF([data.xls]NOVEMBER!$Q$5:$Q$955="E",IF([data.xls]NOVEMBER!$B$5:$B$955=$A9,[data.xls]NOVEMBER!$I$5:$I$955))) กด Ctrl+Shift+Enter คัดลอกลงมา

ลองแล้วครับ ไม่ี่เกิดการเปลี่ยนแปลง

ถ้าใช้สูตร IF ที่ผมเคยลองทำไป เป็นไปได้ไหมครับ ต้องแก้ตรงไหนบ้าง

kamahltew wrote:ลองแล้วครับ ไม่ี่เกิดการเปลี่ยนแปลง

ถ้าใช้สูตร IF ที่ผมเคยลองทำไป เป็นไปได้ไหมครับ ต้องแก้ตรงไหนบ้าง

หากเป็นเช่นนั้น ลองอธิบายเงื่อนไขมาอีกครั้งครับ ว่าต้องมีเงื่อนไขกี่ข้อ อะไรบ้าง

และลองโหลดไฟล์ที่ผมแนบไปให้ในกระทู้ล่าสุด เอาไปดูก่อนครับ โดยเปิดไฟล์ data ขึ้นก่อนเสมอ ครับ

ผมลองยกข้อมูลในไฟล์ sumif มาไว้ในไฟล์ Data ครับ เผื่อที่จะสามารถดูเงื่อนไขให้ง่ายขึ้น หากเงื่อนไขที่ผมใส่ไว้ ไม่ถูกต้อง ลองแจ้งมาอีกครั้งครับ

bank9597 wrote:

kamahltew wrote:ลองแล้วครับ ไม่ี่เกิดการเปลี่ยนแปลง

ถ้าใช้สูตร IF ที่ผมเคยลองทำไป เป็นไปได้ไหมครับ ต้องแก้ตรงไหนบ้าง

หากเป็นเช่นนั้น ลองอธิบายเงื่อนไขมาอีกครั้งครับ ว่าต้องมีเงื่อนไขกี่ข้อ อะไรบ้าง

และลองโหลดไฟล์ที่ผมแนบไปให้ในกระทู้ล่าสุด เอาไปดูก่อนครับ โดยเปิดไฟล์ data ขึ้นก่อนเสมอ ครับ

รายละเอียดผมได้แนบมาให้ดูแล้วครับ

ตอนแรกไปวนอยู่กับ IF(AND แต่งงมากเพราะสูตรมันจะยาวเลย

อันนี้แจ้งเพื่อเพื่อนสมาชิกจะได้ทราบไปด้วยกันครับ

bank9597 wrote:หรือใช้สูตร sum(if) ก้ได้เช่นกัน ดังนี้
ไฟล์ Sumif เซลล์ J9 คีย์ =SUM(IF([data.xls]NOVEMBER!$Q$5:$Q$955="E",IF([data.xls]NOVEMBER!$B$5:$B$955=$A9,[data.xls]NOVEMBER!$I$5:$I$955))) กด Ctrl+Shift+Enter คัดลอกลงมา

ถ้าใช้ Sumproduct ได้เราจะไม่เลือกใช้ Sum แบบ Array เพื่อประสิทธิภาพในการคำนวณครับ

snasui wrote: อันนี้แจ้งเพื่อเพื่อนสมาชิกจะได้ทราบไปด้วยกันครับ

bank9597 wrote:หรือใช้สูตร sum(if) ก้ได้เช่นกัน ดังนี้
ไฟล์ Sumif เซลล์ J9 คีย์ =SUM(IF([data.xls]NOVEMBER!$Q$5:$Q$955="E",IF([data.xls]NOVEMBER!$B$5:$B$955=$A9,[data.xls]NOVEMBER!$I$5:$I$955))) กด Ctrl+Shift+Enter คัดลอกลงมา

ถ้าใช้ Sumproduct ได้เราจะไม่เลือกใช้ Sum แบบ Array เพื่อประสิทธิภาพในการคำนวณครับ

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

รายละเอียดผมได้แนบมาให้ดูแล้วครับ

ตอนแรกไปวนอยู่กับ IF(AND แต่งงมากเพราะสูตรมันจะยาวเลย

ผมยังไม่เข้าใจเงื่อนไขครับ ลองสูตรนี้ดูครับ หากยังไม่ใช่ลองอธิบายเงื่อนไขมาอีกครับ จะช่วยดูจนกว่าจะถูกต้อง
ที่ J9 คีย์ =SUMPRODUCT(--([data.xls]NOVEMBER!$N$5:$N$955=$A9),--([data.xls]NOVEMBER!$Q$5:$Q$955="E"),[data.xls]NOVEMBER!$I$5:$I$955) คัดลอกลงมา

ผมยังไม่เข้าใจเงื่อนไขครับ ลองสูตรนี้ดูครับ หากยังไม่ใช่ลองอธิบายเงื่อนไขมาอีกครับ จะช่วยดูจนกว่าจะถูกต้อง
ที่ J9 คีย์ =SUMPRODUCT(--([data.xls]NOVEMBER!$N$5:$N$955=$A9),--([data.xls]NOVEMBER!$Q$5:$Q$955="E"),[data.xls]NOVEMBER!$I$5:$I$955) คัดลอกลงมา[/quote]

ขอบคุณมากนะครับ ตอนนี้ยอดตรงแล้ว แต่อยากทราบการทำงานของ สูตร SUMPRODUCT ครับว่า ในตัวอย่างที่ให้มา มันทำงานอย่างไร และใช้แทน SUMIF ในไฟล์งานนี้ได้ ทุกคอลัมน์ไหม

เพราะจะลองนำไปใช้กับ คอลัมน์อื่นๆ และถ้าติด อย่างไรอนุญาติถามต่อในกระทู้เดิมนะครับ

การทำงานของ sumproduct ดูที่นี่ครับ http://snasui.blogspot.com/2009/12/sumproduct.html

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

kamahltew wrote: ผมยังไม่เข้าใจเงื่อนไขครับ ลองสูตรนี้ดูครับ หากยังไม่ใช่ลองอธิบายเงื่อนไขมาอีกครับ จะช่วยดูจนกว่าจะถูกต้อง
ที่ J9 คีย์ =SUMPRODUCT(--([data.xls]NOVEMBER!$N$5:$N$955=$A9),--([data.xls]NOVEMBER!$Q$5:$Q$955="E"),[data.xls]NOVEMBER!$I$5:$I$955) คัดลอกลงมา

ขอบคุณมากนะครับ ตอนนี้ยอดตรงแล้ว แต่อยากทราบการทำงานของ สูตร SUMPRODUCT ครับว่า ในตัวอย่างที่ให้มา มันทำงานอย่างไร และใช้แทน SUMIF ในไฟล์งานนี้ได้ ทุกคอลัมน์ไหม

เพราะจะลองนำไปใช้กับ คอลัมน์อื่นๆ และถ้าติด อย่างไรอนุญาติถามต่อในกระทู้เดิมนะครับ

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

ผมขอตอบในเรื่องการใช้ Sumproduct กับ Sumif ครับว่าใช้ในสถาณการณ์ใด
1. sumproduct จะใช้ในกรณีที่มีเงื่อนไขมากกว่า 1 เงื่อนไข และซับซ้อน8iy[
2. sumif จะใช้ในกรณีที่มีเงื่อนไขเพียงเงื่อนไขเดียว ซึ่งจะทำงานได้เร็วมากกว่าสูตรอื่น
3. sumifs สามารถใช้แทน sumproduct ได้ในบางกรณี เช่น เงื่อนไขตรงตัว ไม่ซับซ้อน แต่อย่างไรก้ดีก็ขึ้นอยู่กับความสามารถของผู้ใช้ด้วยครับ หากเก่งมากก็ใช้ sumifs ได้เหมือนเทียบเท่า sumproduct ได้เหมือนกัน และ sumifs สามารถใช้ได้ในเอ็กเซลล์ เวอร์ชั่น 2007 ขึ้นไปครับ

สรุป หากเงื่อนเดียวใช้ sumif หากหลายเงื่อนไข ใช้ sumproduct ซึ่งจะเร็วและดีที่สุดในกรณีนั้นๆครับ