รวมถ้าเซลล์มีข้อความเฉพาะในคอลัมน์อื่น
บทช่วยสอนนี้จะแสดงวิธีรวมค่าหากเซลล์มีข้อความเฉพาะหรือบางส่วนในคอลัมน์อื่น ใช้ช่วงข้อมูลด้านล่างเป็นตัวอย่าง เพื่อรับจำนวนรวมของผลิตภัณฑ์ที่มีข้อความ "เสื้อยืด" ทั้งฟังก์ชัน SUMIF และฟังก์ชัน SUMPRODUCT สามารถจัดการกับงานนี้ใน Excel
- รวมค่าถ้าเซลล์มีข้อความเฉพาะหรือบางส่วนที่มีฟังก์ชัน SUMIF
- รวมค่าถ้าเซลล์มีข้อความเฉพาะหรือบางส่วนด้วยฟังก์ชัน SUMPRODUCT
รวมค่าถ้าเซลล์มีข้อความเฉพาะหรือบางส่วนที่มีฟังก์ชัน SUMIF
ในการรวมค่าถ้าเซลล์มีข้อความเฉพาะในคอลัมน์อื่น คุณสามารถใช้ฟังก์ชัน SUMIF ร่วมกับสัญลักษณ์แทน (*) ไวยากรณ์ทั่วไปมีดังนี้
สูตรทั่วไปพร้อมข้อความฮาร์ดโค้ด:
=SUMIF(range,"*text*",sum_range)
- range: ช่วงข้อมูลที่คุณต้องการประเมินโดยใช้เกณฑ์
- *text*: เกณฑ์ที่คุณต้องการรวมค่าตาม ในที่นี้ สัญลักษณ์แทน * ถูกใช้เพื่อค้นหาอักขระจำนวนเท่าใดก็ได้ เพื่อให้ตรงกับรายการทั้งหมดที่มีข้อความเฉพาะ ให้ใส่ข้อความระหว่างอักขระ * สองตัว (โปรดทราบว่าคุณต้องใส่ข้อความและสัญลักษณ์แทนด้วยเครื่องหมายคำพูดคู่)
- sum_range: ช่วงของเซลล์ที่มีค่าตัวเลขที่ตรงกันที่คุณต้องการหาผลรวม
สูตรทั่วไปที่มีการอ้างอิงเซลล์:
=SUMIF(range,"*"&cell&"*",sum_range)
- range: ช่วงข้อมูลที่คุณต้องการประเมินโดยใช้เกณฑ์
- "*"&cell&"*": เกณฑ์ที่คุณต้องการรวมค่าตาม;
- *: ไวด์การ์ดที่ค้นหาอักขระจำนวนเท่าใดก็ได้
- เซลล์: เซลล์มีข้อความเฉพาะที่ต้องการค้นหา
- &: ตัวดำเนินการต่อ (&) นี้ใช้เพื่อรวมการอ้างอิงเซลล์ด้วยเครื่องหมายดอกจัน
- sum_range: ช่วงของเซลล์ที่มีค่าตัวเลขที่ตรงกันที่คุณต้องการหาผลรวม
หลังจากทราบการทำงานพื้นฐานของฟังก์ชันแล้ว โปรดใช้สูตรใดสูตรหนึ่งต่อไปนี้ที่คุณต้องการ แล้วกด เข้าสู่ กุญแจสำคัญในการรับผลลัพธ์:
=SUMIF($A$2:$A$12,"*T-shirt*",$B$2:$B$12) (Type the criteria manually)
=SUMIF($A$2:$A$12,"*"&D2&"*",$B$2:$B$12) (Use a cell reference)
=SUMIF($A$2:$A$12,"*"&D2&"*",$B$2:$B$12) (Use a cell reference)
หมายเหตุ: ฟังก์ชัน SUMIF นี้ไม่คำนึงถึงขนาดตัวพิมพ์
รวมค่าถ้าเซลล์มีข้อความเฉพาะหรือบางส่วนด้วยฟังก์ชัน SUMPRODUCT
ฟังก์ชัน SUMPRODUCT ยังสามารถช่วยให้คุณทำงานนี้ให้เสร็จโดยไม่ต้องใช้ไวด์การ์ด ที่นี่ คุณควรรวมฟังก์ชัน ISNUMBER และ SEARCH ไว้ในฟังก์ชัน SUMPRODUCT ไวยากรณ์ทั่วไปคือ:
=SUMPRODUCT(sum_range *(ISNUMBER(SEARCH(criteria,range))))
- sum_range: ช่วงของเซลล์ที่มีค่าตัวเลขที่ตรงกันที่คุณต้องการรวม
- criteria: เกณฑ์ที่คุณต้องการรวมค่าตาม อาจเป็นการอ้างอิงเซลล์หรือข้อความเฉพาะที่คุณกำหนด
- range: ช่วงข้อมูลที่คุณต้องการประเมินโดยใช้เกณฑ์
โปรดใช้สูตรใดสูตรหนึ่งด้านล่างนี้ในเซลล์ว่าง แล้วกด เข้าสู่ คีย์เพื่อส่งคืนผลลัพธ์:
=SUMPRODUCT($B$2:$B$12*(ISNUMBER(SEARCH("T-Shirt",$A$2:$A$12)))) (Type the criteria manually)
=SUMPRODUCT($B$2:$B$12*(ISNUMBER(SEARCH(D2,$A$2:$A$12)))) (Use a cell reference)
=SUMPRODUCT($B$2:$B$12*(ISNUMBER(SEARCH(D2,$A$2:$A$12)))) (Use a cell reference)
คำอธิบายของสูตรนี้:
=SUMPRODUCT($B$2:$B$12*(ISNUMBER(SEARCH("T-Shirt",$A$2:$A$12))))
- SEARCH("เสื้อยืด",$A$2:$A$12): ฟังก์ชัน SEARCH จะคืนค่าตำแหน่งของข้อความเฉพาะ “เสื้อยืด” จากช่วงข้อมูล A2:A12 ดังนั้น คุณจะได้อาร์เรย์ดังนี้: {5;#VALUE!;#VALUE!;7;#VALUE! ;7;#VALUE!;#VALUE!;#VALUE!;#VALUE!;7}.
- ISNUMBER(SEARCH("T-Shirt",$A$2:$A$12))= ISNUMBER({5;#VALUE!;#VALUE!;7;#VALUE!;7;#VALUE!;#VALUE!;#VALUE!;#VALUE!;7}): ฟังก์ชัน ISNUMBER นี้ใช้เพื่อทดสอบค่าตัวเลข และส่งคืนอาร์เรย์ใหม่: {TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE}
- $B$2:$B$12*(ISNUMBER(SEARCH("T-Shirt",$A$2:$A$12)))= {347;428;398;430;228;379;412;461;316;420;449}*{TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE}: ในการคูณอาร์เรย์ทั้งสองนี้ การดำเนินการทางคณิตศาสตร์บังคับให้ค่า TRUE และ FALSE เป็น 1s และ 0s ดังนั้น การคูณของอาร์เรย์ทั้งสองจะเป็นดังนี้: {347;428;398;430;228;379;412;461;316;420;449}*{1;0;0;1;0;1; 0;0;0;0;1}={347;0;0;430;0;379;0;0;0;0;449}.
- SUMPRODUCT($B$2:$B$12*(ISNUMBER(SEARCH("T-Shirt",$A$2:$A$12)))) =SUMPRODUCT({347;0;0;430;0;379;0;0;0;0;449}):สุดท้าย ฟังก์ชัน SUMPRODUCT จะรวมค่าทั้งหมดในอาร์เรย์เพื่อให้ได้ผลลัพธ์:1605
ฟังก์ชันสัมพัทธ์ที่ใช้:
- SUMIF:
- ฟังก์ชัน SUMIF สามารถช่วยในการรวมเซลล์ตามเกณฑ์เดียว
- SUMPRODUCT:
- ฟังก์ชัน SUMPRODUCT สามารถใช้เพื่อคูณคอลัมน์หรืออาร์เรย์ตั้งแต่สองคอลัมน์ขึ้นไปเข้าด้วยกันแล้วรับผลรวมของผลิตภัณฑ์
- ISNUMBER:
- ฟังก์ชัน Excel ISNUMBER จะส่งกลับ TRUE เมื่อเซลล์มีตัวเลข และ FALSE หากไม่มี
- SEARCH:
- ฟังก์ชัน SEARCH สามารถช่วยคุณค้นหาตำแหน่งของอักขระหรือสตริงย่อยเฉพาะจากสตริงข้อความที่กำหนด
บทความเพิ่มเติม:
- ผลรวมถ้าขึ้นต้นด้วยหรือลงท้ายด้วยข้อความหรืออักขระเฉพาะ
- ในการรวมค่าถ้าเซลล์ที่เกี่ยวข้องขึ้นต้นด้วยหรือลงท้ายด้วยค่าที่ระบุ คุณสามารถใช้ฟังก์ชัน SUMIF ร่วมกับสัญลักษณ์แทน (*) เพื่อดึงออกมาได้ บทความนี้จะแนะนำวิธีการใช้สูตรอย่างละเอียด
- รวมค่าที่เล็กที่สุดหรือต่ำสุด N
- ใน Excel เป็นเรื่องง่ายสำหรับเราในการรวมช่วงของเซลล์โดยใช้ฟังก์ชัน SUM บางครั้ง คุณอาจต้องรวมตัวเลขที่เล็กที่สุดหรือต่ำสุด 3, 5 หรือ n ตัวในช่วงข้อมูลดังที่แสดงไว้ด้านล่าง ในกรณีนี้ SUMPRODUCT ร่วมกับฟังก์ชัน SMALL สามารถช่วยคุณแก้ปัญหานี้ใน Excel ได้
- รวมค่าที่เล็กที่สุดหรือต่ำสุด N ตามเกณฑ์
- ในบทช่วยสอนก่อนหน้านี้ เราได้พูดถึงวิธีการรวมค่า n ที่น้อยที่สุดในช่วงข้อมูล ในบทความนี้ เราจะดำเนินการขั้นสูงเพิ่มเติม เพื่อรวมค่า n ที่ต่ำที่สุดตามเกณฑ์อย่างน้อยหนึ่งข้อใน Excel
เครื่องมือเพิ่มประสิทธิภาพการทำงานในสำนักงานที่ดีที่สุด
Kutools สำหรับ Excel - ช่วยให้คุณโดดเด่นจากฝูงชน
Kutools สำหรับ Excel มีคุณสมบัติมากกว่า 300 รายการ รับรองว่าสิ่งที่คุณต้องการเพียงแค่คลิกเดียว...
แท็บ Office - เปิดใช้งานการอ่านแบบแท็บและการแก้ไขใน Microsoft Office (รวม Excel)
- หนึ่งวินาทีเพื่อสลับไปมาระหว่างเอกสารที่เปิดอยู่มากมาย!
- ลดการคลิกเมาส์หลายร้อยครั้งสำหรับคุณทุกวันบอกลามือเมาส์
- เพิ่มประสิทธิภาพการทำงานของคุณได้ถึง 50% เมื่อดูและแก้ไขเอกสารหลายฉบับ
- นำแท็บที่มีประสิทธิภาพมาสู่ Office (รวมถึง Excel) เช่นเดียวกับ Chrome, Edge และ Firefox
