Excel Forum

ผมต้องการดึงข้อมูลของสินค้าที่ต้องการให้แสดงอีกชีทนึงครับ
จากไฟล์แนบในชีท Report ช่อง B2 เมื่อผมคีย์ชื่อสินค้าลงไปแล้ว อยากให้ดึงข้อมูลของสินค้าชนิดนั้นจากชีท Transaction มาแสดงครับ

สาเหตุที่ผมไม่ใช้ Filter เนื่องจากว่าในชีท Report เดี๋ยวผมจะนำข้อมูลที่ได้ไปคำนวนผลกับข้อมูลอื่นอีกครับ

การทำเช่นนั้นต้องใช้ VBA ไม่ทราบว่าที่เขียนมาแล้วนั้นชื่อ Procedure อะไร ติดที่บรรทัดใดครับ

ผมยังไม่ได้ลองเขียน VBA เพราะเข้าใจว่าอาจจะใช้สูตรแทนได้ เช่น vlookup หรือพวก Index

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

หากต้องการใช้สูตรลองแบบนี้ดูครับ
ที่ A2 คีย์ =COUNTIF(Transaction!$C$4:$C$11,$B$2)
ที่ A6 คีย์ =IF(ROWS($A$6:$A6)>$A$2,"",INDEX(Transaction!B$4:B$11,SMALL(IF(Transaction!$C$4:$C$11=$B$2,ROW(Transaction!$C$4:$C$11)-ROW(Transaction!$C$4)+1),ROWS($A$6:$A6)))) กดแป้น Ctrl+Shift+Enter พร้อมกันแล้วคัดลอกลงล่าง
ที่ B6 คีย์ =IF(ROWS($A$6:$A6)>$A$2,"",INDEX(Transaction!D$4:D$11,SMALL(IF(Transaction!$C$4:$C$11=$B$2,ROW(Transaction!$C$4:$C$11)-ROW(Transaction!$C$4)+1),ROWS($A$6:$A6)))) กดแป้น Ctrl+Shift+Enter พร้อมกันแล้วคัดลอกไปทางขวาและลงล่างแล้วจัด Format ตามต้องการ

ขอบคุณมากครับ ทำได้แล้วครับ

มีเรื่องรบกวนอีกครับ คือผมได้เพิ่มชีทเพิ่มขึ้นมาอีก 1 ชีท คือ ชีท "stock" เอาไว้สรุปยอดสินค้าคงเหลือทั้งหมด
ผมอยากทราบสูตรการดึงข้อมูลเฉพาะสินค้าคงเหลือนะครับ โดยดึงจากชีท Transaction
และให้คำนวนต้นทุนเฉลี่ยในการซื้อสินค้าและนำต้นทุนเฉลี่ยที่ได้ไปแสดงผลในชีท Stock ครับ

ผมส่งไฟล์แนบมาให้ใหม่ครับ

ลองแบบนี้ดูครับที่ซีท Stock เซลล์ C3 คีย์ =SUMIF(Transaction!$C$4:$C$11,B3,Transaction!$D$4:$D$11)-SUMIF(Transaction!$C$4:$C$11,B3,Transaction!$E$4:$E$11)Enter > Copy ลงด้านล่าง
ที่ D3 คีย์ =SUMIF(Transaction!$C$4:$C$11,B3,Transaction!$F$4:$F$11)/COUNTIF(Transaction!$C$4:$C$11,B3) Enter > Copy ลงด้านล่าง

ขอบคุณครับ ใช้ได้เลยครับ

แต่ผมขออภัยด้วยที่บอกความต้องการไม่หมด พอดีพึ่งนึกออก คือ ในชีท stock อยากให้แสดงสินค้าคงเหลือขึ้นมาทันทีเลย โดยที่เราไม่ต้องไปคีย์ชื่อสินค้าในคอลัมน์ B ก่อน
อย่างตอนนี้ผมต้องมาคีย์ชื่อสินค้า B, C ก่อนถึงจะขึ้นจำนวนคงเหลือ

ถ้าหากอีกหน่อยมีซื้อสินค้า X, Y, Z เข้ามา ก็อยากให้ขึ้นแสดงในชีท stock ทันที แล้วเมื่อมีการขายสินค้าชนิดใดหมดแล้ว ในหน้า stock นั้นก็จะไม่แสดงสินค้าที่ขายไปหมดแล้ว

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

ที่ชีท Stock เซล B3 คีย์

=IFERROR(INDEX(Transaction!$C$4:$C$9,SMALL(IF(FREQUENCY(IF(SUMIF(Transaction!$C$4:$C$9,Transaction!$C$4:$C$9,Transaction!$D$4:$D$9)-SUMIF(Transaction!$C$4:$C$9,Transaction!$C$4:$C$9,Transaction!$E$4:$E$9)>0,MATCH(Transaction!$C$4:$C$9,Transaction!$C$4:$C$9,0)),ROW(Transaction!$C$4:$C$9)-ROW(Transaction!$C$4)+1),ROW(Transaction!$C$4:$C$9)-ROW(Transaction!$C$4)+1),ROWS($B$3:B3))),"")

Ctrl+Shift+Enter > Copy ลงด้านล่าง

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

ขอบคุณมากครับ สะดวกขึ้นเยอะเลยครับ ถ้าจะให้มันเรียงลำดับตัวอักษรของสินค้าจาก A>Z อัตโนมัติ เรียงลงมาทำได้รึเปล่าครับ หรือว่าต้องใช้ sort เอาครับ

ตอนนี้กำลังคิดอยู่ว่าในชีท Stock จะใช้วิธีคำนวนต้นทุนสินค้าแบบ FIFO ไม่ทราบว่าสามารถทำได้รึเปล่าครับ พอจะมีแนวทางรึเปล่าครับ

การ Sort สามารถทำดังนี้ครับ

1. ที่ชีท Transaction เซลล์ H4 คีย์สูตร
   =SUMPRODUCT(--(C4>$C$4:$C$9))+COUNTIF(C$4:C4,C4)
   Enter > Copy ลงด้านล่าง
2. ที่ชีท Transaction เซลล์ I4 คีย์สูตร
   =INDEX($C$4:$C$9,MATCH(ROWS(I$4:I4),$H$4:$H$9,0))
   Enter > Copy ลงด้านล่าง
3. ที่ชีท Stock เซลล์ B3 ปรับสูตรเป็น
   =IFERROR(INDEX(Transaction!$I$4:$I$9,SMALL(IF(FREQUENCY(IF(SUMIF(Transaction!$C$4:$C$9,Transaction!$C$4:$C$9,Transaction!$D$4:$D$9)-SUMIF(Transaction!$C$4:$C$9,Transaction!$C$4:$C$9,Transaction!$E$4:$E$9)>0,MATCH(Transaction!$I$4:$I$9,Transaction!$I$4:$I$9,0)),ROW(Transaction!$C$4:$C$9)-ROW(Transaction!$C$4)+1),ROW(Transaction!$C$4:$C$9)-ROW(Transaction!$C$4)+1),ROWS($B$3:B3))),"")
   Ctrl+Shift+Enter > Copy ลงด้านล่าง

ส่วนการตัด Stock แบบ FIFO ไม่ใช่เรื่องง่ายครับ คงต้องใช้ VBA ในการจัดการ สำหรับการใช้ VBA จำเป็นต้องเขียนมาเองก่อน ติดตรงไหนแล้วค่อยถามกันครับ

ขอบคุณอาจารย์มากครับที่ช่วยเขียนสูตรให้

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

http://snasui.wordpress.com/category/small/

ลิงค์ที่แนบมาก็เป็น Blog ผมอีกที่ครับ

สูตรตาม Link จะค่อนข้างซับซ้อนเนื่องจากต้องการเรียงในสูตรเดียวไม่ต้องการใช้เซลล์อื่นมาช่วย หากใช้กับข้อมูลประเภทเดียวกัน เช่น Text เหมือนกันหมด หรือ Number เหมือนกันหมดน่าจะไม่มีปัญหาใดครับ

ข้อดีคือใช้สูตรเดียว ข้อเสียคือถ้าข้อมูลมาก ๆ จะคำนวณช้าครับ

ขอบคุณมากครับ

ตอนนี้ผมได้ลองใช้สูตร sort จากลิงค์ที่ว่านี้ http://snasui.wordpress.com/category/small/

แล้วผมพบปัญหาครับ คือถ้าหากเรามีชื่อสินค้าที่มีตัวอักษรนำหน้าเหมือนกัน เช่น

เดิมเรามีสินค้าใน stock คือ BOY อยู่ก่อนแล้ว
จากนั้นเราซื้อสินค้า BAG เข้ามา ในชีท stock มันจะดึงสินค้า BOY มาแสดงผล รวมเป็น 2 บรรทัดอะครับ แต่ถ้าไม่ใช้สูตร sort มันจะดึงมาถูก

สินค้าทั้ง 2 มีตัว B ขึ้นต้นเหมือนกันครับ ไม่ทราบว่าผมต้องแก้ไขตรงไหนเพิ่มเติมครับ

จาก Link ทีแนบมาหรือ Link นี้ http://snasui.blogspot.com/2011/04/blog-post_18.html

ลองปรับสูตรที่ C1 เป็น

=IF(ROWS(C$1:C1)>$B$1,"",INDEX($A$1:$A$12,MATCH(SMALL(IF($A$1:$A$12<>"",COUNTIF($A$1:$A$12,"<"&$A$1:$A$12)),ROWS(C$2:C2)),IF($A$1:$A$12<>"",COUNTIF($A$1:$A$12,"<"&$A$1:$A$12)),0)))

หากยังมีปัญหาลองแนบไฟล์ตัวอย่างมาดูกันครับ

ขอบคุณครับ สามารถใช้งานได้แล้วครับ