การนำข้อมูลจากหลาย Sheet มาต่อกันใน Sheet เดียว (2)

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

VDO แสดงการนำข้อมูลหลายชีตมาต่อกันในชีตเดียว

การรวมข้อมูลหลายชีตด้วย Wildcard

ในการรวมข้อมูลจากหลาย ๆ ชีต โดยมีชีตหลักเพื่อไว้สรุปข้อมูลจากชีตย่อย ๆ นั้น

หากชีตที่ต้องการรวมข้อมูลอยู่ติดกัน สามารถทำได้โดย

1. เลือกเซลล์ที่ต้องการใช้แสดงผลรวมในชีตหลัก
2. คีย์สูตร =Sum(
3. คลิกแถบชีตแรกที่ต้องการรวมข้อมูล
4. กดแป้น Shift ค้างไว้
5. คลิกเลือกแถบชีตสุดท้ายที่ต้องการวมข้อมูล
6. ปล่อยแป้น Shift
7. คีย์วงเล็บปิดแล้ว Enter สมมุติว่าต้องการรวมข้อมูล B7 ของ Sheet2 ถึง Sheet30 สูตรจะมีลักษณะเป็นเช่นด้านล่าง
   =Sum(Sheet2:Sheet30!B7)

หากชีตที่ต้องการรวมข้อมูลไม่อยู่ติดกัน สามารถทำได้โดย

1. เลือกเซลล์ที่ต้องการใช้แสดงผลรวมในชีตหลัก
2. คีย์สูตร =Sum(
3. คลิกแถบชีตแรกที่ต้องการรวมข้อมูล
4. คลิกเลือกเซลล์ในชีตแรกที่ต้องการข้อมูล
5. คีย์เครื่องหมายคอมม่า
6. คลิกแถบชีตถัดมาที่ต้องการรวมข้อมูล
7. คลิกเซลล์ในชีตถัดมาที่ต้องการรวมข้อมูล
8. ทำข้อ 6-7 ซ้ำจนครบทุกชีต
9. คีย์วงเล็บปิดแล้ว Enter สมมุติต้องการวามข้อมูลใน B7 ของชีตเหล่านี้คือ Sheet2, Sheet3, Sheet9 และ Sheet30 สูตรจะได้เป็นเช่นด้านล่าง
   =Sum(Sheet2!B7,Sheet3!B7,Sheet9!B7,Sheet30!B7)

ในการทำงานจริง หากมีการกำหนดชีตไว้เป็นกลุ่ม ๆ โดยสามารถแยกแยกระหว่างกลุ่มได้แล้ว เราสามารถนำ Wildcard มาใช้เพื่อความสะดวกในการรวมข้อมูลได้ ยกตัวอย่างเช่นตามภาพด้านล่าง มีการแยกชีตเป็นกลุ่ม PBx, PDx เป็นต้น (x คือเลขต่อท้ายชื่อชีต)

หากเราต้องการรวมข้อมูลของชีตที่ขึ้นต้นด้วย PD ทั้งหมดสามารถทำได้ดังนี้

1. ที่ชีต SumPD คลิกเลือกเซลล์ C2
2. คีย์สูตร
   =Sum('PD*'!C2)
   Enter

โดยเราจะแทนตัวเลขต่าง ๆ ที่ต่อท้ายชื่อชีตด้วย Wildcard (เครื่องหมาย *) สูตรจะกลายเป็น =SUM('PD1:PD3'!C2) ซึ่งจะรวมข้อมูลในเซลล์ C2 ของทุกชีตที่ขึ้นต้นด้วย PD มาให้ตามต้องการ

Revised: January 29, 2017 at 07:04

การเปรียบเทียบข้อมูลจาก 2 ชีทแล้วนำผลต่างไปแสดงในชีทที่ 3

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

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

ภาพข้อมูลใน Sheet1

ภาพข้อมูลใน Sheet2

ภาพการสรุปผลต่างของข้อมูลระหว่าง Sheet1 กับ Sheet2

จากภาพด้านบนจะเป็นการหาผลต่างระหว่าง 2 ชีทโดยยึดค่าในคอลัมน์ A เป็นหลัก เราสามารถเขียนสูตรที่ Sheet3 ได้ดังนี้ครับ

1. ที่เซลล์ G1:G2 คีย์ ไม่มีในชีท1, ไม่มีในชีท2 ตามลำดับ
2. ที่เซลล์ H1 คีย์สูตร
   =SUM(IF(ISNA(MATCH(sheet2!A2:A117,sheet1!A2:A119,0)),1))
   Ctrl+Shift+Enter
3. ที่เซลล์ H2 คีย์สูตร
   =SUM(IF(ISNA(MATCH(sheet1!A2:A119,sheet2!A2:A117,0)),1))
   Ctrl+Shift+Enter
4. ที่เซลล์ H3 คีย์สูตร
   =SUM(H1:H2)
   Enter
5. ที่เซลล์ I1 คีย์สูตร
   =SUM(H$1:H1)-H1+1
   Enter > Copy ไปยัง I2
6. ที่เซลล์ E3 คีย์สูตร
   =IF(ROWS(E$3:E3)>$H$3,"",LOOKUP(ROWS(E$3:E3),$I$1:$I$2,$G$1:$G$2))
   Enter > Copy ลงด้านล่าง
7. ที่เซลล์ B3 คีย์สูตร
   =IF(ROWS(B$3:B3)>$H$3,"",CHOOSE(MATCH($E3,$G$1:$G$2,0),INDEX(sheet1!B$2:B$119,SMALL(IF(ISNA(MATCH(sheet2!$A$2:$A$117,sheet1!$A$2:$A$119,0)),ROW(sheet2!$A$2:$A$117)-ROW(sheet2!$A$2)+1),COUNTIF($E$3:$E3,$E3))),INDEX(sheet1!B$2:B$119,SMALL(IF(ISNA(MATCH(sheet1!$A$2:$A$119,sheet2!$A$2:$A$117,0)),ROW(sheet1!$A$2:$A$119)-ROW(sheet1!$A$2)+1),COUNTIF($E$3:$E3,$E3)))))
   Ctrl+Shift+Enter > Copy ไปทางขวาและลงด้านล่าง

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

การสุ่มแบบแบ่งเป็นกลุ่มและแต่ละกลุ่มสุ่มมาไม่เท่ากัน

กรณีที่ต้องการสุ่มกลุ่มข้อมูล โดยแต่ละกลุ่มต้องการให้สุ่มมาไม่เท่ากันนั้น สามารถใช้สูตรในการจัดการได้เช่นกัน

ยกตัวอย่างเช่น ข้อมูลที่ A1:B19 แบ่งเป็น 3 กลุ่ม คือ A, B, C แต่ละสมาชิกกลุ่มมีมูลค่ากำกับตามภาพด้านล่าง ต้องการสุ่มโดย

1. สุ่ม A มา 1 ค่า
2. สุ่ม B มา 2 ค่า
3. สุ่ม C มา 3 ค่า

แล้วนำค่าที่ได้จากการสุ่มมารวมกัน

ภาพตัวอย่างข้อมูลที่ต้องการสุ่ม

เราสามารถใช้สูตรในการสุ่มได้ดังนี้ครับ

1. ที่ C2 คีย์สูตร
   =RAND()
   Enter > Copy ลงด้านล่าง
2. ที่ E2:E4 คีย์ A, B และ C ตามลำดับ
3. ที่ F2:F4 กรอกจำนวนที่ต้องการสุ่มแต่ละค่า
4. ที่ F5 คีย์สูตรเพื่อรวมจำนวนรายการที่ต้องสุ่ม
   =SUM(F2:F4)
   Enter
5. ที่ G2 คีย์สูตรเพื่อใช้หาบรรทัดที่เริ่มของแต่ละ Group
   =SUM(F$2:F2)-F2+1
   Enter > Copy ไปถึง G4
6. ที่ I2 คีย์สูตรเพื่อ List รายชื่อ Group
   =IF(ROWS(I$2:I2)>$F$5,"",LOOKUP(ROWS(I$2:I2),$G$2:$G$4,$E$2:$E$4))
   Enter > Copy ลงด้านล่าง
7. ที่ J2 คีย์สูตรเพื่อหา Value ที่ได้จากการสุ่ม
   =IF(I2="","",INDEX($B$2:$B$16,MATCH(SMALL(IF($A$2:$A$16=I2,$C$2:$C$16),COUNTIF(I$2:I2,I2)),IF($A$2:$A$16=I2,$C$2:$C$16),0)))
   Ctrl+Shift+Enter > Copy ลงด้านล่าง
8. กดแป้น F9 เพื่อสุ่มค่าตามต้องการ

 Note: Ctrl+Shift+Enter หมายถึงเมื่อคีย์สูตรแล้ว แทนที่จะกด Enter ให้กดแป้น Ctrl+Shift ค้างไว้แล้วกด Enter เพื่อสร้างเป็นสูตร Array หากกดแป้นถูกต้องสูตรนั้นจะมีเครื่องหมายปีกกาครอบ เช่น {=YourFormulas(...)} ปีกกานี้คีย์เข้าไปเองไม่ได้ ถ้ายังไม่เห็นปีกกาครอบสูตรแสดงว่ากดแป้นให้รับสูตรไม่ถูกต้อง

ภาพแสดงผลลัพธ์ที่ต้องการในช่อง I:J

คำแนะนำการใช้งาน Excel สำหรับผู้เริ่มต้น

โปรแกรม Microsoft Excel นับเป็นโปรแกรมที่มีความสามารถสูงและยืดหยุ่นต่อการใช้งานเป็นอย่างมาก ไม่ว่าผู้เริ่มต้นหรือผู้เชี่ยวชาญในระดับใด ๆ ก็ตามก็สามารถใช้ประโยชน์จาก Microsoft Excel ได้ทั้งสิ้น ไม่ว่างานเล็ก ๆ เช่นเก็บข้อมูลส่วนตัว หรืองานใหญ่ ๆ ที่สำคัญ ๆ เช่นการติดต่อกับฐานข้อมูล Data Warehouse การทำ Business Intelligence (BI) โดยมี Add-ins เช่น PowerPivot ก็สามารถนำ Excel ไปใช้ในการแสดงผลและวิเคราะห์ข้อมูลได้ ทำให้การดูข้อมูลและรายงานในมุมมองต่าง ๆ ได้อย่างง่ายดายเพียงแค่คลิกเท่านั้น

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

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

1. เมื่อมีการคีย์ค่าใด ๆ ลงในเซลล์ไม่ว่าจะเป็นค่าคงที่หรือสูตร เมื่อเสร็จสิ้นการคีย์ให้กดแป้น Enter แทนการคลิกเมาส์ที่เซลล์อื่น การ Enter เป็นการบอกโปรแกรมว่าสิ้นสุดการกรอกข้อมูล ผู้ใช้งานหลายท่านมักจะคลิกเมาส์ไปยังเซลล์อื่นเพื่อแสดงการสิ้นสุดการกรอกข้อมูล ซึ่งการทำเช่นนั้นทำให้ค่าที่คีย์ลงไปได้รับผลกระทบหากคีย์เป็นสูตรหรือ Function เพราะโปรแกรมถือว่าการคลิกเซลล์อื่นเป็นการระบุ Argument หรือส่วนประกอบลงในสูตรและการกรอกข้อมูลในครั้งนั้น ๆ ยังไม่สิ้นสุด
2. การ Copy ข้อมูลจากต้นทางไปยังปลายทาง สำหรับ Excel แล้วสามารถเลือก Copy ได้หลายอย่าง ยกตัวอย่างเช่น Copy ไปเฉพาะสูตร, Copy ไปเฉพาะรูปแบบ, Copy ไปเฉพาะความกว้างคอลัมน์, Copy ไปเฉพาะค่า, Copy ไปเฉพาะ Comments, Copy ไปเฉพาะ Validation เป็นต้น ดังนั้น แทนที่จะ Copy แล้ววางทั้งหมดให้ลองนึกดูว่าเราจะวางอะไรลงไปเพื่อจะได้ไม่ต้องมาปรับปรุงในภายหลัง หากไม่ใช่การวางทั้งหมดให้เลือกวางแบบพิเศษ หรือ Paste Special แทน การวางแบบ Paste Special ให้คลิกขวายังเซลล์เป้าหมายแล้วเลือก Options การวางตามต้องการ จะช่วยลดเวลาในการปรับปรุงในภายหลัง
3. พยายามสังเกตทุกการกระทำกับ Worksheet ที่ Status bar ตามภาพด้านล่าง ซึ่งจะเป็นข้อมูลแจ้งให้กับผู้ใช้ทราบว่าให้ทำการใด ๆ เพื่อผลลัพธ์ใด ๆ สำหรับ Status bar นี้มีประโยชน์ในการทำงานกับ Excel มาก
   
4. ให้ระมัดระวังในการลบชีท เนื่องจากเมื่อลบแล้วไม่สามารถ Undo ได้ กรณีได้เผลอลบไปแล้วและยังไม่ได้ Save ให้ปิดไฟล์ไปก่อนแล้วเปิดมาใหม่ แต่การทำเช่นนี้ข้อมูลที่ยังไม่ได้ Save จะสูญหายไปทั้งหมด แต่ก็ยังดีกว่าสูญเสียข้อมูลสำคัญในชีทที่ลบออกไป
5. กรณีต้องการดูผลการคำนวณเร็ว ๆ โดยไม่จำเป็นต้องคีย์ฟังก์ชั่นหรือสูตรการคำนวณขึ้นมาเอง ให้คลุมพื้นที่ที่ต้องการดูผลลัพธ์แล้วสังเกตมุมขวาล่างของหน้าต่าง Excel จะมีผลของการนับ การเฉลี่ยและการรวมมาให้ โดย 3 ค่าดังกล่าวเป็นค่าเริ่มต้นหรือค่า Default สามารถเพิ่มหรือลดโดยการคลิกขวาลงบน Status Bar แล้วทำเครื่องหมายหน้า Option การแสดงผลที่ต้องการ Aggregate Function
6. การ Lock เซลล์ ด้วยเครื่องหมาย $ สำหรับการอ้างอิงใน Excel จะใช้เครื่องหมาย $ เป็นเครื่องชี้ว่าเป็นการอ้างอิงลักษณะใดซึ่งแบ่งออกเป็น
   1. การ Lock คอลัมน์
   2. การ Lock บรรทัด
   3. การ Lock ทั้งคอลัมน์และบรรทัด
   4. ไม่ Lock ทั้งคอลัมน์และบรรทัด

ยกตัวอย่างเช่น

=$A$1 เป็นการ Lock ทั้งคอลัมน์และบรรทัด หาก Copy A1 ไปวางในเซลล์ใด ๆ ค่าผลลัพธ์ที่ได้จะเท่ากับค่าใน A1 เสมอ

=A$1 เป็นการ Lock บรรทัดที่ 1 แต่ไม่ได้ Lock คอลัมน์ หาก Copy A1 ไปวางที่เซลล์ใด ๆ ค่าผลลัพธ์จะอ้างถึงค่าในบรรทัดที่ 1 เสมอแต่คอลัมน์จะผันแปรไปตามเซลล์ปลายทางที่นำไปวาง

=$A1 เป็นการ Lock คอลัมน์แต่ไม่ได้ Lock บรรทัด หาก Copy A1 ไปวางที่เซลล์ใด ๆ ผลลัพธ์จะอ้างถึงค่าในคอลัมน์ A เสมอแต่บรรทัดจะผันแปรไปตามเซลล์ปลายทางทีนำไปวาง

=A1 เป็นการไม่ Lock ทั้งคอลัมน์และบรรทัด หาก Copy A1 ไปวางที่เซลล์ใด ๆ ผลลัพธ์จะอ้างถึงค่าในคอลัมน์ และบรรทัดที่ผันแปรไปตามเซลล์ปลายทางทีนำไปวาง

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

การหา Unique Value จากค่าที่ได้จากการกรอง

สำหรับการหาค่า Unique Value เราสามารถหาได้จากหลาย ๆ วิธีตาม Link นี้ครับ การหารายการเฉพาะที่ไม่ซ้ำ แต่หากเป็นการหาค่าที่ไม่ซ้ำจากรายการที่ได้จากการกรองต้องพึ่งสูตรที่มีความซับซ้อนมากขึ้น

ยกตัวอย่างมีข้อมูลอยู่ที่ A2:B10 หลังจากกรองแล้วให้นับเฉพาะค่าที่ไม่ซ้ำในช่วง A2:A10

เราสามารถคีย์สูตรใน D1 ได้ตามด้านล่างครับ

=SUM(IF(MATCH(IF(SUBTOTAL(3,OFFSET(B2,ROW(B2:B10)-ROW(B2),0)),A2:A10),IF(SUBTOTAL(3,OFFSET( B2,ROW( B2:B10)-ROW(B2),0)),A2:A10),0)=ROW(A2:A10)-ROW(A2)+1,SUBTOTAL(3,OFFSET(B2,ROW(B2:B10)-ROW(B2),0))))

Ctrl+Shift+Enter

ภาพตัวอย่างภาพการใช้สูตรหาค่า Unique จากค่าที่ได้จากการกรอง

การค้นหาตัวเลขทะเบียนรถที่ถูกโฉลก

สำหรับท่านที่ต้องการเลือกเลขทะเบียนรถเองโดยมีเงื่อนไขในการคัดเลือก ยกตัวอย่างเช่นต้องการให้

1. ให้รวมตัวเลขทุกตัวแล้วได้ค่าเป็น 9, 18, 27 หรือ 36
2. สามารถคัดชุดที่มีตัวเลขที่ไม่ต้องการออกไปได้ด้วย ยกตัวอย่างเช่นไม่รวมตัวเลขที่มีค่าต่อไปนี้ 0, 3, 7, 8

การหาค่าดังกล่าวสามารถใช้สูตรหรือ VBA มาคำนวณได้ครับ

กรณีใช้สูตรในการคำนวณมีวิธีการดังนี้

1. เปิดสมุดงานใหม่แล้วเลือกชีทว่าง
2. ที่ A1 คีย์ No. และ ที่ B1 คีย์ Select เพื่อเป็นชื่อ Field
3. ที่ A2 คีย์ Row()-1 > Copy ลงด้านล่างตามต้องการ
4. ที่ B2 คีย์สูตร
   =IF(MIN(LEN(SUBSTITUTE(A2,{0,3,7,8},"")))<LEN(A2),"",IF(ISNUMBER(MATCH(SUM(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)+0),{9,18,27,36},0)),SUM(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)+0),""))
   Ctrl+Shift+Enter > Copy ลงด้านล่าง

กรณีใช้ VBA ในการคำนวณมีวิธีการดังนี้

1. กดแป้น Alt+11 เพื่อเปิดหน้าต่าง VBE
2. คลิกเมนู Insert > Module
3. Copy Code ด้านล่างไปวาง
   
   

   Sub ListNum()
       Dim i As Integer, bln As Boolean
       Dim c As Integer, j As Integer
       Dim a As String, s As Integer
       For i = 1 To 9999
           bln = False
           s = 0
           For j = 1 To Len(CStr(i))
               a = Mid(i, j, 1)
               Select Case a
                   Case 0, 3, 7, 8
                       bln = True
                       Exit For
                   Case Else
                       s = s + a
               End Select
           Next j
           If bln = False Then
               Select Case s
                   Case 9, 18, 27, 36
                       c = c + 1
                       Cells(c + 1, 1) = i
               End Select
           End If
       Next i
   End Sub
   

   
   
   
   
4. กดแป้น Alt+Q เพื่อกลับมายังโปรแกรม Excel
   
5. กดแป้น Alt+F8 > เลือก ListNum > Run

โปรแกรมจะทำการ Run ตัวเลขมาให้ตามเงื่อนไขที่ต้องการ

ภาพตัวอย่างการใช้งาน

การแสดงรายการซ้ำตามจำนวนที่กำหนด

เราสามารถลบรายการซ้ำ ๆ ออกไปให้เหลือเพียงรายการที่ไม่ซ้ำได้หลายวิธีตาม Link นี้ครับ การหาเฉพาะรายการที่ไม่ซ้ำ และเมื่อเราจำเป็นต้องนำรายการที่ไม่ซ้ำกันนั้นมาแจกแจงให้แสดงแบบซ้ำ ๆ โดยระบุว่าแต่ละค่าซ้ำกันกี่ครั้งแล้วทำการสร้างรายการขึ้นมาใหม่ก็สามารถทำได้ด้วยสูตรครับ ซึ่งจะแยกออกเป็นแบบยากกับแบบง่าย

กรณีแบบยากเราจะใช้สูตร Array ในการแสดงรายการซ้ำ ๆ ตามจำนวนที่กำหนด ยกตัวอย่างเช่นตามภาพด้านล่าง ข้อมูลอยู่ที่ A2:A7 จำนวนที่ระบุว่าซ้ำกันกี่ครั้งอยู่ที่ B2:B7 โดยจะนำรายการมาแสดงตั้งแต่ F2 เป็นต้นไป

ภาพแสดงการแสดงรายการซ้ำตามจำนวนที่กำหนดแบบยาก

วิธีการ

1. ที่เซลล์ B8 คีย์สูตรเพื่อหายอดรวมตัวเลขทั้งหมด เพื่อประโยชน์ในการกำหนดจำนวนบรรทัดสูงสุดที่จะแสดงรายการ
   =SUM(B2:B7)
   Enter
2. ที่เซลล์ B9 หาค่าสูงสุดของช่วง B2:B7
   =MAX(B2:B7)
   Enter
3. ที่เซลล์ F2 คีย์สูตรเพื่อแสดงรายการ
   =IF(ROWS($F$2:F2)>$B$8,"",INDEX($A$2:$A$7,SMALL(IF(TRANSPOSE(ROW(INDIRECT("1:"&$B$9)))<=$B$2:$B$7,ROW($B$2:$B$7)-ROW($B$2)+1),ROWS($F$2:F2))))
   Ctrl+Shift+Enter > Copy ลงด้านล่างตามต้องการ สังเกตการกดแป้นจะต้องกด Ctrl+Shift ค้างไว้ก่อนแล้วตามด้วย Enter หากกดแป้นถูกต้องจะเห็นเครื่องหมายปีกกาคร่อมสูตร ปีกกานี้จะคีย์เข้าไปเองไม่ได้ครับ การแก้ไขปรับปรุงสูตรจะต้องกด Ctrl+Shift+Enter ทุกครั้ง

กรณีแบบง่ายจะใช้สูตร Lookup และเพิ่ม C2:C7 มาช่วย โดยจะนำข้อมูลมาแสดงตั้งแต่ G2 เป็นต้นไปตามภาพด้านล่างครับ

ภาพแสดงการแสดงรายการซ้ำตามจำนวนที่กำหนดแบบง่าย

วิธีการ

1. ที่เซลล์ B8 คีย์สูตรเพื่อหายอดรวมตัวเลขทั้งหมด เพื่อประโยชน์ในการกำหนดจำนวนบรรทัดสูงสุดที่จะแสดงรายการ
   =SUM(B2:B7)
   Enter
2. ที่เซลล์ C2 คีย์สูตรเพื่อประโยชน์ในการ Lookup ข้อมูล
   =SUM(B$2:B2)-B2+1
   Enter > Copy ลงด้านล่าง
3. ที่เซลล์ G2 คีย์สูตรเพื่อแสดงรายการ
   =IF(ROWS($G$2:G2)>$B$8,"",LOOKUP(ROWS($G$2:G2),$C$2:$C$7,$A$2:$A$7))
   Enter > Copy ลงด้านล่างตามต้องการ

การให้ลำดับข้อมูลคะแนนโดยค่าเดียวกันได้ลำดับเดียวกันและไม่ข้ามลำดับ

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

	A	B	C
1	คะแนน	Rank	ที่ต้องการ
2	300	1	1
3	200	2	2
4	200	2	2
5	100	4	3

จากตารางจะเห็นว่าด้วยฟังก์ชั่น Rank จะมีการให้ลำดับข้ามไป คือไม่มีลำดับที่ 3 หากต้องการผลลัพธ์ตามคอลัมน์ B เราสามารถคีย์สูตรที่ B2 เป็น

=Rank($A2,$A$2:$A$5)

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

แต่หากต้องการผลลัพธ์ที่มีการให้ลำดับต่อเนื่องกันไปเช่นเดียวกับผลลัพธ์ในคอลัมน์ C ที่ C2 คีย์

=MATCH($A2,LARGE(IF(FREQUENCY(MATCH($A$2:$A$5,$A$2:$A$5,0),ROW($A$2:$A$5)-ROW($A$2)+1),$A$2:$A$5),ROW(INDIRECT("1:"&SUM(1/COUNTIF($A$2:$A$5,$A$2:$A$5))))),0)

Ctrl+Shift+Enter > Copy ลงด้านล่าง

สำหรับสูตรนี้ค่อนข้างจะซับซ้อนครับ เนื่องจากว่าเป็นสูตร Array การกดแป้นให้รับสูตรจะต้องกด 3 แป้น โดยกดแป้น Ctrl+Shift ค้างไว้ก่อนแล้วตามด้วย Enter การแก้ไขเปลี่ยนแปลงสูตรจะต้องกดให้รับสูตรด้วย 3 แป้นนี้เสมอ หากว่ากดแป้นถูกต้องจะเห็นเครื่องหมายปีกกาคร่อมสูตร ปีกกานี้จะคีย์เข้าไปไม่ได้ครับ

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

การรวมข้อมูลโดยอ้างอิงค่าที่เว้นเป็นช่วง ๆ

ปกติรายงานที่ได้มาจากสรุปข้อมูลแล้วมีการเว้นช่วงเอาไว้เช่นรายงานที่ได้จาก PivotTable แล้วมีการ Copy ค่าตามรูปแบบเดิม ๆ มาใช้ จากภาพด้านล่างจะเห็นว่าคอลัมน์ B มีการเว้นข้อมูลไว้เป็นช่วง ๆ การจะให้ง่ายต่อการใช้งานต้องเติม Field ด้านล่างให้เหมือนด้านบน แล้วใช้สูตร Sumif เข้ามาช่วย
แต่หากต้องการจะใช้ความสามารถของสูตรต่าง ๆ เพื่อสรุปข้อมูลโดยไม่ต้องปรับแต่งฐานข้อมูลก็สามารถทำได้แต่สูตรค่อนข้างซับซ้อนและยากต่อการทำความเข้าใจ แต่ก็เป็นประโยชน์ในการนำเสนอแนวความคิด เพื่อนำไปต่อยอดในการใช้งานด้านอื่น ๆ ครับ

ภาพประกอบการรวมข้อมูลโดยอ้างอิงที่เว้นเป็นช่วง ๆ



การที่เราจะรวมข้อมูลในอลัมน์ D จึงต้องประยุกต์สูตรต่าง ๆ มาใช้ โดยคีย์สูตรที่ D5 ดังนี้

=IF(AND(LOOKUP(CHAR(255),$B$2:B5)=LOOKUP(CHAR(255),$B$2:B6),C6=""),SUM(OFFSET($C$1,MATCH(9.99999999999999E+307,C:C),0):OFFSET($C$1,MATCH(9.99999999999999E+307,$D$2:D4)+1,0)),IF(LOOKUP(CHAR(255),$B$2:B5)<>LOOKUP(CHAR(255),$B$2:B6),SUM(OFFSET($C$1,MATCH(CHAR(255),$B$2:B5),0):OFFSET($C$1,MATCH(CHAR(255),$B$2:B6)-1,0)),""))

โดยมีสูตรสำคัญ ๆ ดังนี้

1. LOOKUP(CHAR(255),$B$2:B5) เป็นการหาค่าอักขระสุดท้ายในช่วง B2:B5
2. OFFSET(…):OFFSET(…) เป็นการนำผลลัพธ์ของสูตร OFFSET มาเป็นช่วงข้อมูลใหม่
3. MATCH(CHAR(255),$B$2:B5),0) เป็นการหาตำแหน่งของอักขระสุดท้ายในช่วง B2:B5
4. MATCH(9.99999999999999E+307,C:C) เป็นการหาตำแหน่งสุดท้ายที่พบตัวเลขในช่วง C:C

การรวมข้อมูลหลายเงื่อนไขด้วย Sumif

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

การใช้ Sumif เพื่อรวมข้อมูลหลายเงื่อนไข ทั้งสูตรไม่ยาวเกินไปเราสามารถประยุกต์ใช้ร่วมกับ Sum และ Sumproduct ได้ครับ

ยกตัวอย่างการรวมมูลค่าเฉพาะ 3 Product คือ A, B และ D โดยการประยุกต์ใช้ Sumif

กรณีที่ 1 ประยุกต์ใช้ร่วมกับ Sum

กรณีนี้เงื่อนไขทั้งหลายเราจะเขียนไว้ในเครื่องหมายปีกกาเพื่อให้เป็นค่าคงที่ Array การกดแป้นให้รับสูตรกดเพียง Enter ธรรมดา ดูตัวอย่างที่เซลล์ E2 ในภาพด้านล่างประกอบครับ

กรณีที่ 2 ประยุกต์ใช้ร่วมกับ Sum แบบ Array

กรณีนี้เงื่อนไขทั้งหลายเราจะเตรียมไว้ในที่ใดที่หนึ่ง สำหรับตัวอย่างนี้เราจะเตรียมไว้ที่ D2:D4 การกดแป้นให้รับสูตรกด 3 แป้นคือ Ctrl+Shift+Enter เนื่องจากเป็นสูตร Array ดูตัวอย่างที่เซลล์ E3 ในภาพด้านล่างประกอบครับ

กรณีที่ 3 ประยุกต์ใช้ร่วมกับ Sumproduct

กรณีนี้เงื่อนไขทั้งหลายเราจะเตรียมไว้ในที่ใดที่หนึ่ง สำหรับตัวอย่างนี้เราจะเตรียมไว้ที่ D2:D4 การกดแป้นให้รับสูตรกดเพียง Enter ธรรมดา เนื่องจาก Sumproduct เป็นสูตร Array โดยธรรมชาติดูตัวอย่างที่เซลล์ E4 ในภาพด้านล่างประกอบครับ



ข้อจำกัดของการใช้ฟังก์ชั่น Sumif ก็คือไม่สามารถใช้ฟังก์ชั่น Sumif กับไฟล์ต้นทางที่ถูกปิดไปแล้วได้ครับ กรณีนี้เงื่อนไขทั้งหลายเราจะเขียนไว้ในเครื่องหมายปีกกาเพื่อให้เป็นค่าคงที่ Array การกดแป้นให้รับสูตรกดเพียง Enter ธรรมดา ดูตัวอย่างที่เซลล์ E2 ในภาพด้านล่างประกอบครับ

การนำข้อมูลจากหลาย Sheet มาต่อกันใน Sheet เดียว

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

ภาพ Sheet ที่ใช้รวมข้อมูล 



ภาพ Sheet ที่ 1 



ภาพ Sheet ที่ 2 



ภาพ Sheet ที่ 3 



จากภาพด้านบนเป็นการนำข้อมูล Sheet1, 2, 3 มาต่อกัน ให้ทำตามลำดับดังนี้ครับ

1. เขียนชื่อชีทเรียงกันไว้ตามต้องการที่ G1:G3
2. เซลล์ H1 คีย์สูตรเพื่อนับว่ามีข้อมูลใน Sheet1 เท่าไร
   =COUNTA(INDIRECT("'"&G1&"'!"&"A2:A65536"))
   Enter แล้ว Copy ลงด้านล่าง
3. เซลล์ H4 รวมจำนวนจากด้านบน คีย์
   =SUM(H1:H3)
   Enter
4. เซลล์ I1 ใช้สูตรในการ Run ข้อมูลใหม่เพื่อใช้ในสูตร Lookup
   =SUM($H$1:H1)-H1+1
   Enter แล้ว Copy ลงด้านล่าง
5. เซลล์ E2 คีย์สูตรเพื่อ List รายชื่อ Sheet
   =IF(ROWS($E$2:E2)>$H$4,"",LOOKUP(ROWS($E$2:E2),$I$1:$I$3,$G$1:$G$3))
   Enter แล้ว Copy ไปด้านล่าง
6. เซลล์ A2 คีย์สูตรเพื่อนำข้อมูลใน Sheet ต่าง ๆ มาแสดงต่อกัน
   =IF($E2<>"",INDIRECT("'"&$E2&"'!"&SUBSTITUTE(ADDRESS(1,COLUMN()),1,"")&COUNTIF($E$2:$E2,$E2)+1),"")
   Enter แล้ว Copy ไปทางขวาและลงด้านล่าง

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

เรื่องน่ารู้เกี่ยวกับฟังก์ชั่น SUM

1. มี Short cut เมนูมาให้สามารถคลิกเลือกเพื่อ Sum ข้อมูลได้ ถ้าจะเลือกอย่างอื่นต้องคลิก Dropdown เพื่อเลือกอีกครั้ง
   
2. เมื่อกด Alt+= ก็จะขึ้นฟังก์ชั่น Sum มาให้เลย
   
3. ถ้าเราเลือกเซลล์ว่างใต้กลุ่มข้อมูลที่เป็นตัวเลขแล้วคลิกเครื่องหมาย Sum หรือทำตามข้อ 2 จะเป็นการ Sum ตัวเลขด้านบนมาให้
   
4. ถ้าเราเลือกเซลล์ว่างด้านขวากลุ่มข้อมูลที่เป็นตัวเลขแล้วแล้วคลิกเครื่องหมาย Sum หรือทำตามข้อ 2 จะเป็นการ Sum ตัวเลขด้านซ้ายมาให้
   
5. ถ้าคลุมกล่มข้อมูลที่ประกอบด้วยตัวเลขโปรแกรมจะ Sum ค่าทั้งหมดมาแสดงให้ที่บรรทัด Status bar (ด้านขวาล่างของจอ) ซึ่งเป็นค่าเริ่มต้น ตรงนี้สามารถเลือกเป็น Count, Average, Min, Max, Count Nums
   
6. ถ้าเราคลุมกลุ่มตัวเลขโดยคลุมให้เลยไปทางด้านขวาและด้านล่างจากนั้นกดเครื่องหมาย Sum หรือทำตามข้อ 2 โปรแกรมจะทำการ Sum ข้อมูลมาให้ โดยแสดงผลในเซลล์ด้านล่างและเซลล์ด้านขวาแถวและบรรทัดสุดท้ายที่คลุม
   
7. ฟังก์ชั่น Sum สามารถใช้ร่วมกับ Sumif หรือ Countif กรณีต้องการรวมหรือนับแบบหลายเงือนไขได้ เช่น
   
   =Sum(Sumif(A1:A100,{"<0",">0"}))
   
   โดยไม่ต้องกดแป้น Ctrl+Shift+Enter โดยความหมายสูตรคือให้รวมว่า A1:A100 มีค่าเท่าไร สูตรนี้พิเศษตรงที่สามารถรวมได้แม้ช่วงข้อมูล A1:A100 จะมีค่าผิดพลาดปรากฏอยู่ด้วย เข่น #N/A, DIV/0!
   
8. ฟังก์ชั่น Sum สามารถใช้ในสูตร Array เพื่อนับข้อมูลที่เข้าเงื่อนไขได้ เช่น
   
   =Sum(If(A1:A100="Yes",If(B1:B100="OK",1)))
   
   กดแป้น Ctrl+Shift+Enter เนื่องจากเป็นสูตร Array
   
   ความหมายสูตรคือ ถ้าเซลล์ใดในช่วง A1:A100 มีค่าเท่ากับ Yes และเซลล์ใดในช่วง B1:B100 มีค่าเท่ากับ OK ให้นับเป็น 1 นั่นหมายความว่าต้องอยู่ในบรรทัดเดียวกันด้วย เช่น A10 มีค่าเป็น Yes และ B10 มีค่าเป็น OK ถึงจะนับเป็น 1 ถ้าไม่เช่นนั้นก็มีค่าเป็น False คือไม่เข้าเงื่อนไข จากนั้นรวมค่าที่เข้าเงื่อนไขทั้งหมด
   
9. ฟังก์ชั่น Sum สามารถใช้ในสูตร Array เพื่อ Sum ข้อมูลที่เข้าเงื่อนไขได้ เช่น
   
   =Sum(If(A1:A100="Yes",If(B1:B100="OK",C1:C100)))
   
   กดแป้น Ctrl+Shift+Enter เนื่องจากเป็นสูตร Array
   
   ความหมายสูตรคือ ถ้าเซลล์ใดในช่วง A1:A100 มีค่าเท่ากับ Yes และเซลล์ใดในช่วง B1:B100 มีค่าเท่ากับ OK ให้นำข้อมูลในคอลัมน์ C ที่อยู่ในบรรทัดเดียวกันนั้นมาแสดง นั่นหมายความว่าต้องอยู่ในบรรทัดเดียวกันด้วย เช่น A10 มีค่าเป็น Yes และ B10 มีค่าเป็น OK ถึงจะนำค่า C10 มาแสดง ถ้าไม่เช่นนั้นก็มีค่าเป็น False คือไม่เข้าเงื่อนไข จากนั้นทำการรวมข้อมูลที่เข้าเงื่อนไขทั้งหมด
   
10. ฟังก์ชั่น Sum สามารถมีได้ 30 ส่วนประกอบซึ่งแต่ละส่วนประกอบเป็นเซลล์เดี่ยวก็ได้ เป็นช่วงเซลล์ก็ได้ เช่น
    
    =Sum(A1:A5,2,B10,Max(C1:C200))
    
11. ฟังก์ชั่น Sum สามารถแปลงค่าตัวเลขที่อยู่ในเครื่องหมายคำพูดให้เป็นตัวเลขได้ เช่น
    
    =Sum("10",15)
    
    ผลรวมจะได้เท่ากับ 25
    
12. ฟังก์ชั่น Sum สามารถรวมค่าตรรกะได้ ได้แก่ True จะให้ผลเป็น 1 และ False จะให้ผลเป็น 0 เช่น
    
    =Sum(True,False,4,A1>3)
    
    ผลลัพธ์จะได้ 6 ถ้า A1 มากกว่า 3 หรือ ผลลัพธ์จะได้ 5 ถ้า A1 ไม่มากกว่า 3
    
13. ฟังก์ชั่น Sum สามารถใช้แบบ 3 มิติได้ คือสามารถรวม Sheet หลาย ๆ Sheet ได้ เช่น
    
    =Sum(Sheet1:Sheet5!A1:A10)
    
    ความหมายคือเป็นการรวมค่าในช่วงเซลล์ A1:A10 ของ Sheet1 ถึง Sheet 5

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

การรวมยอดโดยไม่รวมบรรทัดที่ซ่อน

หลาย ๆ ท่านใช้การซ่อนบรรทัด (Hide) ไว้แทนการลบทิ้ง โดยเผื่อไว้ว่าโอกาสหน้าจะกลับมาใช้ จะได้ไม่ต้องคีย์เพิ่มเข้าไปใหม่ แต่หากมีการใช้สูตร Sum เพื่อรวมยอดตัวเลขไว้ ยอดผลรวมนั้นจะรวมบรรทัดที่ซ่อนด้วย

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

สมมุติข้อมูลอยู่ที่ B2:B50 จะขอยกตัวอย่างบางฟังก์ชั่นที่น่าใช้งานดังนี้ครับ

1. หากต้องการรวมยอดที่ B2:B50 โดยไม่รวมบรรทัดที่ซ่อน ที่ B51 คีย์
   =Subtotal(109,B2:B50)
2. หากต้องการนับเฉพาะตัวเลขที่ B2:B50 โดยไม่รวมบรรทัดที่ซ่อน ที่ B51 คีย์
   =Subtotal(102,B2:B50)
3. หากต้องการนับทั้งหมดยกเว้นค่าว่างที่ B2:B50 โดยไม่รวมบรรทัดที่ซ่อน ที่ B51 คีย์
   =Subtotal(103,B2:B50)
4. หากต้องการหาค่าที่มากที่สุดที่ B2:B50 โดยไม่รวมบรรทัดที่ซ่อน ที่ B51 คีย์
   =Subtotal(104,B2:B50)
5. หากต้องการหาค่าที่น้อยที่สุดที่ B2:B50 โดยไม่รวมบรรทัดที่ซ่อน ที่ B51 คีย์
   =Subtotal(105,B2:B50)
6. หากต้องการหาค่าเฉลี่ยที่ B2:B50 โดยไม่รวมบรรทัดที่ซ่อน ที่ B51 คีย์
   =Subtotal(101,B2:B50
   

ถ้ามีค่าผิดพลาดจะรวมยอดอย่างไร?

ปัญหานี้ก็น่าสนใจมาก ในการทำงานของเรามักพบว่า บ่อยครั้งที่ผลลัพธ์ตามฟังก์ชั่นที่เราเขียนขึ้นเป็น #N/A หรือ #DIV/0!

การหาผลรวมโดยใช้ฟังก์ชั่น Sum ธรรมดาก็ไม่สามารถหาผลลัพธ์ได้ เอ... แล้วเราจะทำอย่างไรกันดี

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

หลังจากนึกถึงการประยุกต์ใช้ฟังก์ชั่นจนหน้ามืดได้ที่แล้ว ก็มาดูตัวอย่างการประยุกต์ฟังก์ชั่น Sum กับ Sumif เพื่อเป็นแนวทางดูครับ

สมมุติตัวอย่าง ข้อมูลอยู่ที่ A1:A5 เป็นข้อมูลด้านล่างตามลำดับ อยากได้ผลรวมที่ช่อง A6 เป็น 45 จะต้องใช้สูตรอย่างไร?

10
#N/A
20
#DIV/0!
15

มาดูตัวอย่างการใช้ สูตรที่ A6 ตามนี้ครับ

1. =Sum(Sumif(A1:A5,{"<0",">0"})) หรือ
2. =Sumif(A1:A5,"<=9.99999999999999e307")

ทั้งสองสูตรด้านบน Enter ธรรมดาครับ นอกจากนี้ยังมีสูตร Array ที่พวกเราชอบมากๆ มาช่วยเราได้อีกได้แก่

=Sum(If(Isnumber(A1:A5),A1:A5))

สูตรนี้ต้องกด 3 แป้นครับ เพื่อทำเป็นสูตร Array คือ Ctrl+Shift+Enter

ต่อไปนี้เราก็ไม่ต้องกลัวเรื่องการรวมยอดที่มีข้อผิดพลาดอีกแล้วครับ

การรวมค่าจากการปัดเศษด้วยสูตรเดียว

ปกติหากเราต้องการหาค่ารวมจากการปัดเศษ จะต้องเขียนสูตร Round ก่อนแล้วค่อย Sum ค่าที่ได้ทั้งหมดออกมาอีกที

การใช้วิธีลัดเพื่อรวมค่าที่ปัดเศษทีเดียวทั้งหมดโดยไม่ต้องเขียนสูตร Round ไว้ก่อนจะทำให้ประหยัดเวลา ประหยัดเซลล์ได้มาก ยกตัวอย่างเช่น A2:A6 ประกอบด้วย

A2: 2.354
A3: 3.653
A4: 3.765
A5: 9.228
A6: 4.773

หากปัดให้เหลือทศนิยม 2 หลักทั้งหมดก่อนแล้วค่อยรวม ที่เซลล์ใด ๆ สามารถใช้สูตร Array ดังนี้

=Sum(Round(A2:A6,2))

Ctrl+Shift+Enter

หรือหากไม่ต้องการใช้สูตร Array แบบกำหนดเอง ก็สามารถใช้สูตร Array ธรรมชาติแบบด้านล่างเพื่อไม่ต้องกด Ctrl+Shift+Enter

=Sumproduct(Round(A2:A6,2))

Enter

แทรกบรรทัดผลรวมสูตรไม่ขยายตามจะทำอย่างไร?

ปกติเมื่อเราแทรกบรรทัดที่เป็นผลรวมแล้วมีการกรอกตัวเลขเพิ่ม จะต้องมาแก้ไขสูตรผลรวมใหม่เพื่อให้รวมบรรทัดที่แทรกด้วยเสมอ ยกตัวอย่างเช่น A1:A5 มีข้่อมูลดังนี้

5
2
3
4
8

และที่ A6 เขียนสูตร

=Sum(A1:A5)

หากเราแทรกที่บรรทัด A6 แล้วคีย์ตัวเลขใด ๆ สูตรใน A7 จะไม่รวมตัวเลขที่เพิ่งคีย์ไปล่าสุด และยังคงแสดงเป็น

=Sum(A1:A5)

แต่ทั้งนี้เราต้องการให้แสดงเป็น

=Sum(A1:A6)

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

=Sum(Offset(A$1,0,0,Row()-Row(A$1),1))

จะทำให้สูตรขยายให้เสมอเมื่อมีการแทรกบรรทัดครับ