Snasui Excel Forum

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

ตัวอย่างตามนี้ค่ะ

ลำดับ หมายเลข ชื่อ ปภ.เอกสาร จำนวนเงินคงค้างสุทธิ

1 20001488359 นาย นิเวศน์ ZH 3,702.34
1 20001488359 นาย นิเวศน์ ZH 657.62
1 20001488359 นาย นิเวศน์ IN 47.4
* 1 20001488359 4,407.36
2 20001553907 นาย เสน่ห์ AQ 393.8
* 2 20001553907 393.8
3 20001656205 น.ส. พรรณี AQ 1,419.05
3 20001656205 น.ส. พรรณี ZH 1,665.24
3 20001656205 น.ส. พรรณี CC 1,350.00
* 3 20001656205 4,434.29
4 20001763396 นาย สมชาย CC 2,219.49
4 20001763396 นาย สมชาย CC 1,235.59
* 4 20001763396 3,455.08
5 20001814700 นาย สังเวียน IN 3,915.63
* 5 20001814700 3,915.63
6 20001830254 นาย ยงยุทธ IN 1,065.54


ขอบคุณทุกท่านที่ให้คำแนะนำค่ะ



ป.ล. ใช้ countifs แล้วยังไม่ได้ผลลัพธ์ตามที่ต้องการค่ะ เพราะบรรทัาดมันซ้ำกันด้วยค่ะ

ลองตามนี้ครับ

ชีทผลลัพธ์ทีต้องการ

1. เซลล์ A3 คีย์สูตรเพื่อให้หมายเลขลำดับ
   =IF(B3<>"",N(A2)+1,"")
   Enter > Copy ลงด้านล่าง
2. เซลล์ B3 คีย์สูตรเพื่อแสดงหมายเลข
   =IFERROR(INDEX(ข้อมูลดิบ!B$3:B$107,SMALL(IF(FREQUENCY(IF(ISNUMBER(MATCH(ข้อมูลดิบ!$D$3:$D$107,{"ZH","IN","AQ"},0)),MATCH(ข้อมูลดิบ!$B$3:$B$107,ข้อมูลดิบ!$B$3:$B$107,0)),ROW(ข้อมูลดิบ!$B$3:$B$107)-ROW(ข้อมูลดิบ!$B$3)+1),ROW(ข้อมูลดิบ!$B$3:$B$107)-ROW(ข้อมูลดิบ!$B$3)+1),ROWS(B$3:B3))),"")
   Ctrl+Shift+Enter > Copy ไป C3 เพื่อแสดงชื่อ จากนั้น Copy B2:C3 ลงด้านล่าง
3. เซลล์ D3 คีย์สูตรเพื่อนับรายการ
   =SUMPRODUCT(--(ISNUMBER(MATCH(ข้อมูลดิบ!$D$3:$D$107,{"ZH","IN","AQ"},0))),--(ข้อมูลดิบ!$B$3:$B$107=$B3))
   Enter > Copy ลงด้านล่าง
4. เซลล์ E3 คีย์สูตรเพื่อรวมจำนวนเงิน
   =SUMPRODUCT(--(ISNUMBER(MATCH(ข้อมูลดิบ!$D$3:$D$107,{"ZH","IN","AQ"},0))),--(ข้อมูลดิบ!$B$3:$B$107=$B3),ข้อมูลดิบ!$E$3:$E$107)
   Enter > Copy ลงด้านล่าง

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

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

ขอบคุณอีกครั้ง เข้ามาที่นี่ไม่เคยผิดหวังเลยค่ะ เอาไปใช้ได้ดีมากๆเลย

pori wrote:ถ้าอยากให้ลูกค้าที่ไม่มีประเภทเอกสารตามเงื่อนไข ให้โชว์ 0 ด้วย จะได้รู้ว่าไม่มีรายการน่ะค่ะจะทำได้ไหม เพราะสูตรอันนี้เหมือนจะตัดออกไปเลย

ที่สูตรตัดออกไปเพราะไม่เข้าเงื่อนไขที่แจ้งมาครับ

กรณีต้องการรายการเหล่านั้นด้วยให้ปรับสูตรในข้อ 2 เป็นด้านล่าง สำหรับสูตรอื่น ๆ เหมือนเดิม

=IFERROR(INDEX(ข้อมูลดิบ!B$3:B$107,SMALL(IF(FREQUENCY(IF(ข้อมูลดิบ!$C$3:$C$107<>"",IF(ISNUMBER(LEFT(ข้อมูลดิบ!$B$3:$B$107)+0),MATCH("~"&ข้อมูลดิบ!$B$3:$B$107,ข้อมูลดิบ!$B$3:$B$107&"",0))),ROW(ข้อมูลดิบ!$B$3:$B$107)-ROW(ข้อมูลดิบ!$B$3)+1),ROW(ข้อมูลดิบ!$B$3:$B$107)-ROW(ข้อมูลดิบ!$B$3)+1),ROWS(B$3:B3))),"")

Ctrl+Shift+Enter > Copy ไป C3 และ Copy ลงด้านล่าง

ใช้ได้เลยค่ะ แบบนี้เลยที่ต้องการ

แต่อยากทราบว่าเครื่องหมาย ~ ที่อยู่ใน match มีความหมายอะไรเหรอคะ มันทำหน้าที่อะไรในสูตร

อีกอน่างนึง คือ จะกดตัวนี้อย่างไรในคีย์บอร์ด เพราะพอกดแป้นมันก็เหมือนการเปลี่ยนภาษาไปเลย ไม่ขึ้นตัวนี้ซักที (คือว่าไม่รู้จริงๆน่ะค่ะ )

ขอบคุณมากจริงๆค่ะ สำหรับคำแนะนำ

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

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

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

ดีใจที่มี web ตอบปัญหาอย่างนี้ค่ะ แอบมาหาความรู้บ่อยๆๆ เป็นอะไรที่สุดยอดไปเลยค่ะ ขอบคุณมากๆๆที่ทำให้มี web ดีๆอย่างนี้ค่ะ

ว่าแต่ขอถามนอกเรื่องอีกอย่างนึงค่ะ คือ ไปอ่านหัวข้อ ฟังก์ชั่น If กับการตัดเกรด wordpress/if-and-grade/

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

pori wrote: ว่าแต่ต้องเขียนตัวนี้ทุกครั้งที่มีเซลล์ว่างใช่หรือไม่คะ แล้ว &"" ที่อยู่ส่วนหลัง นี่ก็เพราะเหตุผลเดียวกันคือ มีเซลล์ว่าง ใช่ไหม (ทำไมถึงไม่ใช้ให้เหมือนๆกันละคะ แอบงงเล็กน้อย

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

สำหรับ If ที่ถาม ต้องดูเงื่อนไขครับ การที่มีเครื่องหมาย <= หรือ >= โจทย์จะต้องบอกว่า น้อยกว่าหรือเท่ากับ มากกว่าหรือเท่ากับ ไม่ใช่แค่น้อยกว่าหรือมากกว่าครับ

อ้อ เข้าใจแล้ว ขอบคุณมากมายค่ะ จะเอาไว้ประยุกต์ใช้ต่อค่ะ