การหาค่า Sum โดยการเลือกเงื่อนไข

[trirongcop]

ผมอยากคีย์เงื่อนไขแล้วให้ผล Sum ตามเงื่อนไขครับ

Capture1.JPG

[DhitiBank]

ลองแบบนี้ครับ

N3 คีย์

=SUM(SUMIF($J$3:$J$8,MID(M3,ROW(INDIRECT("1:"&LEN(M3))),1),$K$3:$K$8))

กด Ctrl+Shift ค้างแล้ว Enter >> คัดลอกลงล่างครับ

[trirongcop]

ได้ตรงตามต้องการครับ ขอบคุณพี่มากครับ

=SUM(SUMIF($J$3:$J$8,MID(M3,ROW(INDIRECT("1:"&LEN(M3))),1),$K$3:$K$8))

พี่ครับไม่เข้าใจตรง ไฮไลท์สีแดง มันมีการคำนวณยังไงครับ

[DhitiBank]

ลองดูสูตรนี้ในเซลล์ 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 ครับ

[trirongcop]

ขอบคุณครับคุณพี่มีความเข้าในการSum อีกมากเลยครับ

[trirongcop]

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

Capture.JPG

[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

[DhitiBank]

ช่วยจับภาพให้เห็นคอลัมน์และแถวด้วยได้ไหมครับ หรือหากแนบไฟล์ที่ติดปัญหามาด้วยจะดีมากเลยครับ

แล้วก็ ถามเพิ่มครับ
เงื่อนไข1: B, A
เงื่อนไข2: 1, 3

หมายถึงให้หาทั้ง A-1, A-3, B-1, B-3 ใช่หรือเปล่าครับ

[trirongcop]

ครับ ผลลัพธ์มันน้อยกว่าความเป็นจริงครับ

[DhitiBank]

เงื่อนไข1: A, B
เงื่อนไข2: 1, 2

คือให้หาผลรวมทั้ง A-1, A-2, B-1 และ B-2 ใช่หรือเปล่าครับ

[trirongcop]

ใช้ครับผม ผลลัพธ์ = 25 Krub.

[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

[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 นะครับ

[trirongcop]

ขอบคุณมากครับ
สูตรพี่นี้เอาแบบแกะไม่ออกเลยครับ เอาสะ งง
สองฟังก์ชั้นนี้ มันทำงานยังไงครับ (MMULT(--($I$3:$I$8=TRANSPOSE

[DhitiBank]

เอาแบบย่อๆ นะครับ เพราะหากจะอธิบาย MMULT จริงๆ ผมว่าหน้ากระดาษ A4 ไม่พอแน่ๆ เพราะต้องเอาเรื่องเมตริกซ์ในวิชาคณิตศาสตร์มาพูดด้วย สยองครับ (หากอยากหาข้อมูลเพิ่ม เปิด google แล้วคีย์ค้นหา "การคูณเมตริกซ์" ครับ )

การเปรียบเทียบในกรณีนี้เป็นการเปรียบเทียบกับเงื่อนไขหลายตัว ขอยกตัวอย่างแค่เงื่อนไขแรกนะครับคือการเทียบว่า A หรือ B (เซลล์ M3) และขอยกตัวอย่างเพียงการเปรียบเทียบข้อมูลอย่างเดียว ซึ่งเป็นสาเหตุที่ผมนึกได้แค่สูตร MMULT ครับ

กรณีที่ 1 คีย์เงื่อนไขตัวเดียว สมมติว่าเป็น A
การเปรียบเทียบจะง่ายครับ จะได้ว่า

Code: Select all

=I3:I8="A"
={"TRUE";
  "TRUE";
  "TRUE";
  "FALSE";
  "FALSE";
  "FALSE"}

(ผมเขียนแบบนี้เพราะการเปรียบเทียบในแนวแถว ผลลัพธ์ที่ได้ก็จะยังเป็นแนวแถวครับ ปกติเราเห็นแบ่งด้วย ";" ที่ต้องเขียนแบบนี้เพราะเดี๋ยวกรณีที่ 2 จะได้เห็นภาพชัด)
ผลลัพธ์แค่แนวเดียว คือมีคอลัมน์เดียว ในทางเมตริกซ์เรียกข้อมูลนี้ว่ามีขนาดหรือมิติ 6 x 1 (แถว x คอลัมน์) ข้อมูลลักษณะนี้เราเอาไปใช้ต่อในสูตรง่ายครับ

กรณีที่ 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}

[trirongcop]

ยัง งง ครับ เดียวต้องอ่านทำความเข้าใจคงอีกหลายๆรอบ
ต้อนนี้มึนครับ
ขอบคุณพี่อีกครั้งนะครับ

[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))),
บรรทัดนี้เข้าไป เราต้องปรับ ตรงไฮไลท์ไหมครับ
แล้ว เงื่อนไขที่เป็นตัวเลขกับตัวหนังสือ ต่างกันไมครับ

[DhitiBank]

1. หากเพิ่มเงื่อนไข ไม่ได้ปรับตรงที่ระบายสีแดงแน่นอนครับ ผมว่าแนบไฟล์ตัวอย่างมาจะดีที่สุดครับ
2. เนื่องจากการตัดข้อความด้วย Mid ผลที่ได้จะมีรูปแบบเป็น text หมด แม้ว่าจะมองเห็นเป็นตัวเลขครับ ดังนั้น จากสูตรด้านบนผมแปลงตัวเลขในตางรางเงื่อนไขหลัก (คอลัมน์ J) ด้วยการเชื่อมกับค่าว่าง ("") เพื่อให้มีรูปแบบเป็น text ครับ ไม่เช่นนั้นมันจะเอามาเทียบกันไม่ได้

IF(MMULT(--($J$3:$J$8&""

[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))))))

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

[snasui]

อีกตัวอย่างสูตรครับ

เซลล์ 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 ลงด้านล่าง