หาจำนวนสินค้าคงเหลือราย Invoice

[NEO889]

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

จากไฟล์ตัวอย่างปกติผมใช้ Vlookup ดึุงข้อมูลของสินค้าจากไฟล์ฐานข้อมูลขึ้นมาแสดง คอลัมน์ Qty จะเป็นยอดสินค้าที่เปิด Invoice ส่งเข้าไป และพอสิ้นเดือนจะมาตัดขาย โดยจะลงยอดติดลบ และหากมีลดหนี้รับคืนก็จะลงยอดติดลบเหมือนกัน ซึ่งวิธีที่ผมใช้ในปัจจุบันคือใช้ Pivote Table หายอดคงเหลือครับ ส่วนคอลัมน์ No. คือเลขที่ Invoice หากตัดขาย (Qty ติดลบ) จะลงเลขที่ Invoice ของตัวสินค้าที่ต้ด และคอลัมน์ Type จะลงเลขที่ Invoice ซึ่งจะเหมือนกันกับคอลัมน์ No. หากมีการเปิด Invoice ส่งสินค้าเข้าไป และจะลงเลขที่ของใบเสร็จและใบลดหนี้ด้วย


รบกวนขอคำแนะนำด้วยครับ

[snasui]

ช่วยกรอกตัวอย่างคำตอบที่ต้องการมาด้วยเพื่อจะได้เข้าใจตรงกันครับ

[NEO889]

ขอบคุณสำหรับคำตอบนะครับ

แนบไฟล์เพิ่มเติมครับ

[snasui]

ลองตามนี้ครับ
ที่ชีท QTY

1. เซลล์ B4 คีย์
   =SUMIF('Data Entry'!$A$2:$A$8,B2,'Data Entry'!$D$2:$D$8)
   Enter
2. เซลล์ B5 คีย์
   =SUM(IF(FREQUENCY(IF('Data Entry'!$A$2:$A$8=$B$2,MATCH('Data Entry'!$G$2:$G$8,'Data Entry'!$G$2:$G$8,0)),ROW('Data Entry'!$I$2:$I$8)-ROW('Data Entry'!$G$2)+1),1))
   Ctrl+Shift+Enter
3. เซลล์ D5 คีย์
   =IF(ROWS(D$5:D5)=$B$5+1,"Total",IF(ROWS(D$5:D5)>$B$5+1,"",INDEX('Data Entry'!$G$2:$G$8,SMALL(IF(FREQUENCY(IF('Data Entry'!$A$2:$A$8=$B$2,MATCH('Data Entry'!$G$2:$G$8,'Data Entry'!$G$2:$G$8,0)),ROW('Data Entry'!$I$2:$I$8)-ROW('Data Entry'!$G$2)+1),ROW('Data Entry'!$I$2:$I$8)-ROW('Data Entry'!$G$2)+1),ROWS(D$5:D5)))))
   Ctrl+Shift+Enter > Copy ลงด้านล่าง
4. เซลล์ E5 คีย์
   =IF(D5="","",IF(D5="Total",SUM(OFFSET($E$4,0,0,ROW()-ROW($E$4))),SUMIF('Data Entry'!$G$2:$G$8,D5,'Data Entry'!$D$2:$D$8)))
   Enter > Copy ลงด้านล่าง

[NEO889]

ขอบคุณมากๆ เลยครับ..คงจะต้องใช้เวลาแกะสูตรอีกนานจึงจะนำไปใช้จริง

ผมลองดูแล้วครับ...แต่ในช่อง Total มีแสดงเป็น #REF! ครับ


รบกวนดูให้หน่อยครับ



ขอบคุณที่ยอมสละเวลาอันมีค่าของท่าน

[snasui]

ผมแก้ไขสูตรในข้อ 4 ให้ใหม่แล้วเนื่องจากการอ้างอิงไม่ถูกต้อง ดูตามด้านบนครับ การ Enter ให้รับสูตรเพียงแต่ Enter ธรรมดา ไม่ต้อง Ctrl+Shift+Enter ครับ

[NEO889]

ผมลองแล้วลองอีกครับ...แต่พอข้อมูลเยอะๆ เข้ารู้สึกว่าสูตรมันทำงานผิดพลาดไปหรือเปล่าครับ

รบกวนดูให้หน่อยครับ

ไฟล์แนบ

[snasui]

จากไฟล์ล่าสุดปรับสูตรในเซลล์ E7 เป็น

=IF(B7="","",IF(B7="Total",SUM(OFFSET($E$6,0,0,ROW()-ROW($E$6))),SUMIFS(TRC!$D$2:$D$10000,TRC!$A$2:$A$10000,$B$4,TRC!$G$2:$G$10000,B7)))

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

[NEO889]

ขอบคุณมากๆ เลยครับทำได้แล้ว หัวฟูตั้งนาน