Page 1 of 2
การหาค่า Sum โดยการเลือกเงื่อนไข
Posted: Wed Sep 14, 2016 8:23 am
by trirongcop
![Razz :P](./images/smilies/icon_razz.gif)
ผมอยากคีย์เงื่อนไขแล้วให้ผล Sum ตามเงื่อนไขครับ
Capture1.JPG
Re: การหาค่า Sum โดยการเลือกเงื่อนไข
Posted: Wed Sep 14, 2016 9:28 am
by DhitiBank
ลองแบบนี้ครับ
N3 คีย์
=SUM(SUMIF($J$3:$J$8,MID(M3,ROW(INDIRECT("1:"&LEN(M3))),1),$K$3:$K$8))
กด Ctrl+Shift ค้างแล้ว Enter >> คัดลอกลงล่างครับ
Re: การหาค่า Sum โดยการเลือกเงื่อนไข
Posted: Wed Sep 14, 2016 9:41 am
by trirongcop
ได้ตรงตามต้องการครับ ขอบคุณพี่มากครับ
=SUM(SUMIF($J$3:$J$8,MID(M3,ROW(INDIRECT("1:"&LEN(M3))),1),$K$3:$K$8))
พี่ครับไม่เข้าใจตรง ไฮไลท์สีแดง มันมีการคำนวณยังไงครับ
Re: การหาค่า Sum โดยการเลือกเงื่อนไข
Posted: Wed Sep 14, 2016 10:45 am
by DhitiBank
![Smile :)](./images/smilies/icon_e_smile.gif)
ลองดูสูตรนี้ในเซลล์ N4 นะครับ
(ก่อนอื่น ขอแนะวิธีแกะสูตรดูตามไปนะครับ ไปที่ formula bar ลากคลุมสูตรแล้วกด F9 เพื่อดูว่าสูตรนั้นๆ คำนวณได้ผลอย่างไร ดูเสร็จกด ESC ครับ)
แนวคิด:
- เงื่อนไขใน M4 คือ 1 และ 2 ผมใช้ sumif เพื่อหาผลรวมในคอลัมน์ K แยกแต่ละเงื่อนไข แต่ sumif ต้องมี criteria
- criteria นี่ได้มาจากการตัดข้อความใน M4 ด้วยสูตร Mid
- สูตร Mid มีโครงสร้างคือ
MID(ข้อความที่จะตัด,ตำแหน่งที่จะตัด,จำนวนอักษรที่จะตัด)
ซึ่ง "ตำแหน่งที่จะตัด" ก็คือตั้งแต่ตำแหน่งที่ 1 จนถึงตำแหน่งอักษรตัวสุดท้ายใน M4 โดยตัดทีละ 1 อักษร
--- เนื่องจากต้องการตัดอักษรทุกตัวใน M4 กรณีนี้คือตั้งแต่ตำแหน่งที่ 1 ถึง 3 ผมใช้สูตร Row(ช่วงข้อมูล) เช่น ROW(1:3) ซึ่งผลลัพธ์คือจะเป็นตัวเลขลำดับแถวตาม "ช่วงข้อมูล"
--- แต่ช่วงข้อมูลในที่นี้ไม่เท่ากัน (เพราะอย่าง M7 มี 5 อักษร) ก็ใช้สูตร LEN(...) เพื่อนับจำนวนอักขระ
--- หากอ้างอิงช่วงแบบนี้ (คือ 1:Len(...)) ไม่สามารถคีย์ตรงๆ ในสูตร ROW(...) ได้ ต้องใช้ Indirect มาช่วยครับ
=SUM(SUMIF($J$3:$J$8,MID(M4,
ROW(INDIRECT("1:"&LEN(M4))),1),$K$3:$K$8))
=SUM(SUMIF($J$3:$J$8,MID(M4,
{1;2;3},1),$K$3:$K$8))
=SUM(SUMIF($J$3:$J$8,
{"1";",";"2"},$K$3:$K$8))
=SUM(
{5;0;6})
สูตรด้านบนนี้ ผมลากคลุมสูตรแล้วกด F9 ครับ
Re: การหาค่า Sum โดยการเลือกเงื่อนไข
Posted: Wed Sep 14, 2016 11:06 am
by trirongcop
Re: การหาค่า Sum โดยการเลือกเงื่อนไข
Posted: Wed Sep 14, 2016 2:54 pm
by trirongcop
คุณพี่ครับถ้าผมจะเพิ่มเงื่อนไข จะต้องปรับยังไงครับ
ผลลองแก้แล้วก้อไม่ได้ครับ
Capture.JPG
Re: การหาค่า Sum โดยการเลือกเงื่อนไข
Posted: Wed Sep 14, 2016 3:04 pm
by trirongcop
ผมปรับเป็น
=SUM(SUMIFS($K$3:$K$8,$I$3:$I$8,MID(M3,ROW(INDIRECT("1:"&LEN(M3))),1),$J$3:$J$8,MID(N3,ROW(INDIRECT("1:"&LEN(N3))),1)))
ผลลัพธ์ได้ไม่ตรงครับ
Capture.JPG
Re: การหาค่า Sum โดยการเลือกเงื่อนไข
Posted: Wed Sep 14, 2016 3:12 pm
by DhitiBank
ช่วยจับภาพให้เห็นคอลัมน์และแถวด้วยได้ไหมครับ หรือหากแนบไฟล์ที่ติดปัญหามาด้วยจะดีมากเลยครับ
แล้วก็ ถามเพิ่มครับ
เงื่อนไข1: B, A
เงื่อนไข2: 1, 3
หมายถึงให้หาทั้ง A-1, A-3, B-1, B-3 ใช่หรือเปล่าครับ
Re: การหาค่า Sum โดยการเลือกเงื่อนไข
Posted: Wed Sep 14, 2016 3:15 pm
by trirongcop
![Razz :P](./images/smilies/icon_razz.gif)
ครับ ผลลัพธ์มันน้อยกว่าความเป็นจริงครับ
Re: การหาค่า Sum โดยการเลือกเงื่อนไข
Posted: Wed Sep 14, 2016 3:26 pm
by DhitiBank
เงื่อนไข1: A, B
เงื่อนไข2: 1, 2
คือให้หาผลรวมทั้ง A-1, A-2, B-1 และ B-2 ใช่หรือเปล่าครับ
Re: การหาค่า Sum โดยการเลือกเงื่อนไข
Posted: Wed Sep 14, 2016 3:30 pm
by trirongcop
ใช้ครับผม ผลลัพธ์ = 25 Krub.
Re: การหาค่า Sum โดยการเลือกเงื่อนไข
Posted: Wed Sep 14, 2016 3:43 pm
by DhitiBank
ลองแบบนี้ครับ ผมใช้คอลัมน์ช่วยนะครับ เพราะหากเขียนให้จบในสูตรเดียว ผมคิดว่าสูตรคงยาวมาก (แต่ตอนนี้ผมก็ยังเขียนให้จบในสูตรเดียวไม่ได้)
P3 คีย์
=IFERROR(SUM(SUMIFS($K$3:$K$8,$I$3:$I$8,MID($M3,COLUMNS($P3:P3),1),$J$3:$J$8,MID($N3,ROW(INDIRECT("a1:a"&LEN($N3))),1))),0)
Ctrl+Shift+Enter >> คัดลอกไปทางขวาเผื่อเอาไว้ครับ (เอาเงื่อนไขทั้ง 2 กรณีที่มีมากที่สุดเอามาคูณกัน เช่น เงื่อนไขแรกมีโอากาสมากสุดคือ 3 เงื่อนไข ก็ให้คัดลอกเผื่อสัก 5 คอลัมน์ครับ -- ที่เกินมาอีก 2 คือรวมเครื่องหมาย ",")
O3 คีย์
=SUM(P3:T3)
Enter
จากนั้นคัดลอกสูตรทั้งหมดลงล่าง
แต่ผมว่าอาจไม่เหมาะสักเท่าไรหากข้อมูลมีมากๆ
2016-09-14 15_39_11-Microsoft Excel - sumif1.xlsb.png
Re: การหาค่า Sum โดยการเลือกเงื่อนไข
Posted: Wed Sep 14, 2016 3:55 pm
by DhitiBank
เพิ่มเติมครับ แบบจบในสูตรเดียว
O3 คีย์
=SUM(IF(MMULT(--($I$3:$I$8=TRANSPOSE(MID(M3,ROW(INDIRECT("a1:a"&LEN(M3))),1))),LEN(MID(M3,ROW(INDIRECT("a1:a"&LEN(M3))),1))),
IF(MMULT(--($J$3:$J$8&""=TRANSPOSE(MID(N3,ROW(INDIRECT("a1:a"&LEN(N3))),1))),LEN(MID(N3,ROW(INDIRECT("a1:a"&LEN(N3))),1))),$K$3:$K$8)))
Ctrl+Shift+Enter >> คัดลอกลงล่างครับ
แถวไหนไม่ได้คีย์เงื่อนไขไว้จะขึ้นข้อผิดพลาด #REF นะครับ
Re: การหาค่า Sum โดยการเลือกเงื่อนไข
Posted: Wed Sep 14, 2016 3:59 pm
by trirongcop
![Razz :P](./images/smilies/icon_razz.gif)
ขอบคุณมากครับ
สูตรพี่นี้เอาแบบแกะไม่ออกเลยครับ เอาสะ งง
สองฟังก์ชั้นนี้ มันทำงานยังไงครับ (MMULT(--($I$3:$I$8=TRANSPOSE
Re: การหาค่า Sum โดยการเลือกเงื่อนไข
Posted: Wed Sep 14, 2016 4:59 pm
by DhitiBank
เอาแบบย่อๆ นะครับ เพราะหากจะอธิบาย MMULT จริงๆ ผมว่าหน้ากระดาษ A4 ไม่พอแน่ๆ เพราะต้องเอาเรื่องเมตริกซ์ในวิชาคณิตศาสตร์มาพูดด้วย สยองครับ (หากอยากหาข้อมูลเพิ่ม เปิด google แล้วคีย์ค้นหา "การคูณเมตริกซ์" ครับ
![Smile :)](./images/smilies/icon_e_smile.gif)
)
การเปรียบเทียบในกรณีนี้เป็นการเปรียบเทียบกับเงื่อนไขหลายตัว ขอยกตัวอย่างแค่เงื่อนไขแรกนะครับคือการเทียบว่า A หรือ B (เซลล์ M3) และขอยกตัวอย่างเพียงการเปรียบเทียบข้อมูลอย่างเดียว ซึ่งเป็นสาเหตุที่ผมนึกได้แค่สูตร MMULT ครับ
![Arrow :arrow:](./images/smilies/icon_arrow.gif)
กรณีที่ 1 คีย์เงื่อนไขตัวเดียว สมมติว่าเป็น A
การเปรียบเทียบจะง่ายครับ จะได้ว่า
Code: Select all
=I3:I8="A"
={"TRUE";
"TRUE";
"TRUE";
"FALSE";
"FALSE";
"FALSE"}
(ผมเขียนแบบนี้เพราะการเปรียบเทียบในแนวแถว ผลลัพธ์ที่ได้ก็จะยังเป็นแนวแถวครับ ปกติเราเห็นแบ่งด้วย ";" ที่ต้องเขียนแบบนี้เพราะเดี๋ยวกรณีที่ 2 จะได้เห็นภาพชัด)
ผลลัพธ์แค่แนวเดียว คือมีคอลัมน์เดียว ในทางเมตริกซ์เรียกข้อมูลนี้ว่ามีขนาดหรือมิติ 6 x 1 (แถว x คอลัมน์) ข้อมูลลักษณะนี้เราเอาไปใช้ต่อในสูตรง่ายครับ
![Arrow :arrow:](./images/smilies/icon_arrow.gif)
กรณีที่ 2 คีย์เงื่อนไขหลายตัว สมมติว่าคีย์ A,B
- การเปรียบเทียบจะยากขึ้น เพราะสูตร MID จะตัดได้ว่า
"A"
;
","
;
"B"
(สังเกตว่าผลลัพธ์มีขนาด 3x1)
- สูตร Transpose จะเปลี่ยนแกนของผลลัพธ์นี้ครับ จาก 3x1 เป็น 1x3 คือ
"A"
,
","
,
"B"
(เครื่องหมาย , จะคั่นข้อมูลที่อยู่คนละคอลัมน์)
Code: Select all
=I3:I8={"A",",","B"}
={"T","F","F";
"T","F","F";
"T","F","F";
"F","F","T";
"F","F","T";
"F","F","T"}
(ขอย่อ T=True, F=False)
- งงไหมครับ คอลัมน์แรกมาจากการเทียบ I3:I8 กับ A คอลัมน์ที่สองเทียบกับ , คอลัมน์สุดท้ายเทียบกับ B
- จะเห็นว่าข้อมูลมีมิติ 6x3 แบบนี้เราเอามาใช้ในสูตรยากครับ เราต้องการให้เหลือเพียงคอลัมน์เดียว จะได้เลือกถูกว่าจะเอาค่าไหนในคอลัมน์ K มาบวก ผมเลยเอาสูตร MMULT มาใช้เพราะสูตรนี้เป็นการคูณเมตริกซ์กับเมตริกซ์ ผลที่ได้ยังเป็นเมตริกซ์
ที่มีมิติเปลี่ยนไปครับ มีเทคนิคคือ
เมตริกซ์ตัวตั้ง x เมตริกซ์ตัวคูณ = เมตริกซ์ผลลัพธ์
[
m x n] x [a x
b] = [
m x
b]
--- สังเกตว่า ผลลัพธ์จะมีแถวเท่าตัวตั้ง และคอลัมน์เท่าตัวเอามาคูณ
--- กรณีนี้ตัวตั้งของเรามีมิติ 6x3 หากผมอยากให้เหลือ 6x1 ก็ต้องหาข้อมูลที่มีมิติ 3x1 มาเป็นตัวคูณครับ (สูตร Len ครับ)
--- และหากเราหาตัวคูณที่มีสมาชิกเป็น 1 ทั้งหมด ผลลัพธ์จะมีความพิเศษตรงที่ ดูตัวตั้งนะครับ หากแถวไหนมี T ผลลัพธ์ก็จะมี T ที่แถวนั้นครับ (ขอเปลี่ยน T, F เป็นเลขนะครับ จะได้ดูง่ายหน่อย)
Code: Select all
2, 0, 0 2
3, 0, 0 3
4, 0, 0 1 4
0, 0, 5 x 1 = 5
0, 0, 6 1 6
0, 0, 7 7
คราวนี้เราก็เอาไปใช้ในสูตร IF ได้ง่ายขึ้นครับ (ยกตัวอย่างสูตรที่ O3)
=IF(
MMULT(--($I$3:$I$8=TRANSPOSE(MID(M3,ROW(INDIRECT("a1:a"&LEN(M3))),1))),LEN(MID(M3,ROW(INDIRECT("a1:a"&LEN(M3))),1))),IF(
MMULT(--($J$3:$J$8&""=TRANSPOSE(MID(N3,ROW(INDIRECT("a1:a"&LEN(N3))),1))),LEN(MID(N3,ROW(INDIRECT("a1:a"&LEN(N3))),1))),$K$3:$K$8))
=IF(
{1;1;1;1;1;1},IF(
{1;1;0;1;1;0},$K$3:$K$8))
={5;6;FALSE;8;6;FALSE}
Re: การหาค่า Sum โดยการเลือกเงื่อนไข
Posted: Wed Sep 14, 2016 5:10 pm
by trirongcop
ยัง งง ครับ เดียวต้องอ่านทำความเข้าใจคงอีกหลายๆรอบ
ต้อนนี้มึนครับ
ขอบคุณพี่อีกครั้งนะครับ
![Clap :cp:](./images/smilies/icon_clap.gif)
Re: การหาค่า Sum โดยการเลือกเงื่อนไข
Posted: Wed Sep 14, 2016 6:25 pm
by trirongcop
คุณพี่ครับถ้าจะใส่เงื่อนไขอีก
เพิ่ม IF(MMULT(--($J$3:$J$14&""=TRANSPOSE(MID(O3,ROW(INDIRECT("a1:a"&LEN(O3))),1))),LEN(MID(O3,ROW(INDIRECT("a11:a"&LEN(O3))),1))),
บรรทัดนี้เข้าไป เราต้องปรับ ตรงไฮไลท์ไหมครับ
แล้ว เงื่อนไขที่เป็นตัวเลขกับตัวหนังสือ ต่างกันไมครับ
Re: การหาค่า Sum โดยการเลือกเงื่อนไข
Posted: Wed Sep 14, 2016 10:08 pm
by DhitiBank
1. หากเพิ่มเงื่อนไข ไม่ได้ปรับตรงที่ระบายสีแดงแน่นอนครับ ผมว่าแนบไฟล์ตัวอย่างมาจะดีที่สุดครับ
2. เนื่องจากการตัดข้อความด้วย Mid ผลที่ได้จะมีรูปแบบเป็น text หมด แม้ว่าจะมองเห็นเป็นตัวเลขครับ ดังนั้น จากสูตรด้านบนผมแปลงตัวเลขในตางรางเงื่อนไขหลัก (คอลัมน์ J) ด้วยการเชื่อมกับค่าว่าง ("") เพื่อให้มีรูปแบบเป็น text ครับ ไม่เช่นนั้นมันจะเอามาเทียบกันไม่ได้
IF(MMULT(--($J$3:$J$8
&""
Re: การหาค่า Sum โดยการเลือกเงื่อนไข
Posted: Thu Sep 15, 2016 10:31 am
by trirongcop
=(SUM(IF(MMULT(--($O$7:$O$14432=TRANSPOSE(MID(AN$7,ROW(INDIRECT("a1:a"&LEN(AN$7))),2))),LEN(MID(AN$7,ROW(INDIRECT("a1:a"&LEN(AN$7))),2))),
IF(MMULT(--($F$7:$F$14432&""=TRANSPOSE(MID($AL$2,ROW(INDIRECT("a1:a"&LEN($AL$2))),1))),LEN(MID($AL$2,ROW(INDIRECT("a1:a"&LEN($AL$2))),1))),
IF(MMULT(--($Q$7:$Q$14432&""=TRANSPOSE(MID($AL$3,ROW(INDIRECT("a1:a"&LEN($AL$3))),2))),LEN(MID($AL$3,ROW(INDIRECT("a1:a"&LEN($AL$3))),2))),
IF(MMULT(--($R$7:$R$14432&""=TRANSPOSE(MID($AM8,ROW(INDIRECT("a1:a"&LEN($AM8))),3))),LEN(MID($AM8,ROW(INDIRECT("a1:a"&LEN($AM8))),3))),$I$7:$I$14432))))))
ขอบคุณครับพี่ ผมลองปรับแล้วได้ผลลัพธ์ถูกต้องอยู่ครับ
แต่ถ้ามีข้อมูลมากๆๆมันจะอืดนิหนึ่งครับ
ถ้าติดปัญหาเดียวผมขอคำแนนนำอีกครั้งครับ
Re: การหาค่า Sum โดยการเลือกเงื่อนไข
Posted: Sat Sep 17, 2016 2:24 pm
by snasui
![Very Happy :D](./images/smilies/icon_e_biggrin.gif)
อีกตัวอย่างสูตรครับ
เซลล์ O3 คีย์
=SUMPRODUCT(SUMIFS($K$3:$K$8,$I$3:$I$8,MID($M3,ROW(INDIRECT("1:"&LEN($M3))),1),$J$3:$J$8,MID($N3,ROW(INDIRECT("1:"&LEN($N3))),1)))
Enter > Copy ลงด้านล่าง