Snasui Excel Forum

1. ต้องการผลรวมค่าเฉลี่ยของเซลล์ที่อยู่คอลัมภ์ H แยกตามรหัสของคอลัมภ์ B *ตัวอย่างคำตอบคือเซลล์สีเหลืองค่ะ
(รบกวนแนะนำการใส่ค่าคำตอบ ไม่ทราบว่าควรวางไว้ที่ใด
เนื่องจากจะต้องลบแถวที่เป็นรหัสซ้ำออกไป เมื่อจัดเรียงข้อมูลเรียบร้อย ตามเงื่อนไขต่อไปนี้ด้านล่างค่ะ)

2. ต้องการดึงค่าที่อยู่ด้านล่างขึ้นมาแสดงที่เซลล์ว่างแถวบน ตามตัวอย่างตัวเลขสีแดงค่ะ

3. กรณีแถวแรกในแต่ละรหัส ช่องที่เป็นค่าว่าง ตัวอย่างเซลล์สีเขียว ต้องการให้ใส่ เลข 0 ค่ะ

4. ข้อมูลที่คอลัมภ์ P , Q , R เนื่องจากได้ใช้สูตร vlookup เพราะเหตุใดถึงไม่แสดงค่าที่ถูกต้องคะ
ตัวอย่างเช่น ที่เซลล์ R4 ต้องแสดงค่า 13 , ที่เซลล์ Q9 ต้องแสดงค่า 26 , ที่เซลล์ R9 ต้องแสดงค่า 34 เป็นต้นค่ะ

5. ข้อมูลที่เซลล์ P15 , Q15 , R15 แสดง N/A หมายถึงไม่มีข้อมูล แต่ต้องการให้แสดงค่าเป็น 0 ค่ะ

6. ขอทราบสูตรการตัดแถวที่เป็นรหัสซ้ำ หลังจากที่ใส่ข้อมูล ตามข้อ 2,3,4,5 เรียบร้อยแล้วค่ะ

ขอบคุณมากค่ะ

ก่อนอื่น ขอถามก่อนครับว่าในชีท sort400 ในที่สุดแล้วจะเหลือแค่รหัสที่ไม่ซ้ำเท่านั้นใช่ไหมครับ ดังนั้น หากใส่แค่รหัสที่ไม่ซ้ำเลยตั้งแต่เริ่มแรกได้ไหมครับ

หากได้ ลองแบบนี้ครับ
เอาแบบคร่าวๆ คือ ขั้นแรก สร้าง Named Range แบบไดนามิคเพราะคิดว่าข้อมูลในชีท ยอดซื้อ และ ยอดใช้ คงมีการเพิ่มเข้าไปเรื่อยๆ สามารถดูตัวอย่างที่ทำไว้โดยการกด Ctrl+F3 ครับ จากนั้นก็ใส่สูตรลงไปในชีท sort400 ลองดูในไฟล์แนบครับ

DhitiBank wrote:หากได้ ลองแบบนี้ครับ
เอาแบบคร่าวๆ คือ ขั้นแรก สร้าง Named Range แบบไดนามิคเพราะคิดว่าข้อมูลในชีท ยอดซื้อ และ ยอดใช้ คงมีการเพิ่มเข้าไปเรื่อยๆ สามารถดูตัวอย่างที่ทำไว้โดยการกด Ctrl+F3 ครับ จากนั้นก็ใส่สูตรลงไปในชีท sort400 ลองดูในไฟล์แนบครับ

รบกวนสอบถามค่ะ Named Range แบบไดนามิค หมายถึงอะไรคะ
และข้อมูลในชีทจะไม่เพิ่มอีกค่ะ เพราะดึงยอดมาจากฐานข้อมูลเดิมค่ะ

ขอบคุณค่ะ

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

จากไฟล์ตัวอย่าง หากไม่มีการเพิ่มลดข้อมูลแล้วก็ไม่เป็นไรครับ ว่าแต่... ได้คำตอบตามต้องการหรือเปล่าครับ

ได้ตรงตามที่ต้องการเลยค่ะ
กำลังทำความเข้าใจเรื่องสูตรค่ะ ยากและงงมากเลย เนื่องจากต้องทำข้อมูลต่อค่ะ
ขอเรียงวิธีการดังนี้ค่ะ
1. นำยอดซื้อและยอดใช้มารวมเป็นชีทเดียวกัน แล้วกรองเฉพาะรหัสที่ไม่ซ้ำ เนื่องจาก รหัสอาจมีในยอดซื้อแต่ไม่มีในยอดใช้ หรือมีในยอดใช้แต่ไม่มียอดซื้อ ขั้นตอนนี้เข้าใจถูกไหมคะ
2. จากนั้นใส่สูตรแล้วลาก ตามที่คุณ DhitiBank แนะนำได้เลยไหมคะ

ขอบคุณค่ะ

งงตรงไหนถามได้เลยครับ

ยอดซื้อและยอดใช้แยกตามเดิมครับ คือใน 2 ชีทนี้ใส่ข้อมูลตามปกติอย่างที่เคยทำครับ สูตรจะดึงรหัสที่ไม่ซ้ำมาจาก "ยอดซื้อ" เองครับ

ปกติ รหัสในยอดซื้อจะมีครบไม่ใช่เหรอครับ หรือว่าจะมีโอกาสที่ใน "ยอดซื้อ" จะไม่มีรหัสที่ปรากฏใน "ยอดใช้"

ใช่ค่ะ มีโอกาสที่ยอดซื้อจะไม่มีรหัสปรากฏในยอดใช้ค่ะ
แบบนี้ต้องแก้สูตรไหมคะ

ขอบคุณค่ะ

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

แต่หากไม่สะดวก ก็คงต้องนำยอดซื้อกับยอดใช้มารวมในชีทเดียวกันครับ

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

ขอบคุณค่ะ

amziiify wrote:จากสูตรที่ทำเป็นตัวอย่างไว้ คือกรองค่าที่ "ไม่ซ้ำ" ไว้แล้วใช่ไหมคะ

ใช่ครับ

amziiify wrote:อย่างนี้คือสามารถเพิ่มหรือลดค่าในชีทเดียว ตามที่กล่าวไว้ด้านบน ถูกต้องไหมคะ

ถูกต้องครับ หากสะดวก ก็รวมยอดซื้อกับยอดใช้ไว้ชีทเดียวกันก็ได้ครับ

amziiify wrote:แล้วแบบนี้ต้องแก้สูตรอย่างไรบ้างคะ

ผมต้องปรับส่วน named range ใหม่ แต่ลองปรับหน้าตาไฟล์มาก่อนแล้วกันครับ ส่วนเรื่องรายละเอียดสูตร หากต้องการคำอธิบาย ผมจะทำให้แต่ต้องหลังจากนี้ 2-3 วันครับเพราะตอนนี้ไม่มีคอมอยู่กับตัว

ปรับหน้าตาไฟล์ใหม่แล้วค่ะ

ขอทบทวนความต้องการดังนี้ค่ะ
1. ดึงข้อมูลจากชีท BUYUSED ที่คอลัมภ์ G-M , Q-S มาแสดงที่ชีท M400 โดยตัดรหัสซ้ำให้เหลือเพียงรหัสเดียว

2. อยากทราบสูตร ราคาเฉลี่ย 3 ปี จากผลรวมราคาต่อหน่วยของปี 2012-2014
โดยเงื่อนไข หากมีข้อมูล 3 ปี หาร 3 , หากมีข้อมูล 2 ปี หาร 2 , หากมีข้อมูล 1 ปี หาร 1 หรือแสดงค่าเลยค่ะ

3. ขอสูตรการดึงข้อมูลที่ชีท Vendor400 คอลัมภ์ C มาแสดงที่ชีท M400 ที่คอลัมภ์ W , X , Y ค่ะ
อาจารย์ snasui เคยแนะนำสูตร =IF($A3="","",OFFSET($B4,2*(COLUMNS($D2:D2)-1),0)&"")
ซึ่งลองแล้วก็ใช้ได้เหมือนกันค่ะ แต่พอดึงค่ามาแสดงที่คอลัมภ์ W , X , Y แล้ว มีเซลล์ว่างคั่นก่อนจะแสดงค่าที่เซลล์ถัดไปค่ะ

4. กรณีช่องที่เป็นค่าว่าง ต้องการให้ใส่ เลข 0 ยกเว้น คอลัมภ์ W , X , Y ค่ะ

ขอบคุณค่ะ

amziiify wrote:1. ดึงข้อมูลจากชีท BUYUSED ที่คอลัมภ์ G-M , Q-S มาแสดงที่ชีท M400 โดยตัดรหัสซ้ำให้เหลือเพียงรหัสเดียว2. อยากทราบสูตร ราคาเฉลี่ย 3 ปี จากผลรวมราคาต่อหน่วยของปี 2012-2014 โดยเงื่อนไข หากมีข้อมูล 3 ปี หาร 3 , หากมีข้อมูล 2 ปี หาร 2 , หากมีข้อมูล 1 ปี หาร 1 หรือแสดงค่าเลยค่ะ

ทำตามด้านล่างครับ
ที่ชีท M400

1. เซลล์ G4 คีย์สูตร
   =SUMIF(BUYUSED!$B$4:$B$525,$B4,BUYUSED!G$4:G$525)
   Enter > Copy ไปถึง I4 > Copy ลงด้านล่าง
2. เซลล์ J4 คีย์
   =(G4+H4+I4)/COUNTIF(G4:I4,">0")
   Enter > Copy ลงด้านล่าง
3. ปรับใช้กับคอลัมน์ Q:S

amziiify wrote:3. ขอสูตรการดึงข้อมูลที่ชีท Vendor400 คอลัมภ์ C มาแสดงที่ชีท M400 ที่คอลัมภ์ W , X , Y ค่ะอาจารย์ snasui เคยแนะนำสูตร =IF($A3="","",OFFSET($B4,2*(COLUMNS($D2:D2)-1),0)&"") ซึ่งลองแล้วก็ใช้ได้เหมือนกันค่ะ แต่พอดึงค่ามาแสดงที่คอลัมภ์ W , X , Y แล้ว มีเซลล์ว่างคั่นก่อนจะแสดงค่าที่เซลล์ถัดไปค่ะ

ทำตามด้านล่างครับ

1. ที่ชีท Vendor400 เซลล์ E1 คีย์ รหัสวัสดุ2
2. ที่ชีท Vendor400 เซลล์ E2 คีย์สูตร
   =LOOKUP(9.99999999999999E+307,B$2:B2)
   Enter > Copy ลงด้านล่างเท่าที่มีข้อมูล
3. ที่ชีท M400 เซลล์ W4 คีย์สูตร
   =IFERROR(INDEX(Vendor400!$C$2:$C$1516,SMALL(IF(Vendor400!$E$2:$E$1516=$B4,IF(Vendor400!$C$2:$C$1516<>"",ROW(Vendor400!$E$2:$E$1516)-ROW(Vendor400!$E$2)+1)),COLUMNS($W4:W4))),"")
   Ctrl+Shift+Enter > Copy ไปทางขวาถึง Y4 แล้ว Copy ลงด้านล่าง

!

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

ขอสอบถามเพิ่มเติมค่ะ
ถ้าต้องการดึงรหัสวัสดุและชื่อจากชีท BUYUSED มาแสดงที่ชีท M400 โดยมีรหัสไม่ซ้ำกัน ควรใช้ไดนามิคไหมคะ กรณีนี้จะมีการเพิ่มหรือลดรหัสเข้าไปที่ชีท BUYUSED ค่ะ

ที่ชีท M400 เซลล์ B4 คีย์สูตร

=IFERROR(INDEX(BUYUSED!B$4:B$525,SMALL(IF(FREQUENCY(IF(BUYUSED!$B$4:$B$525<>"",MATCH(BUYUSED!$B$4:$B$525,BUYUSED!$B$4:$B$525,0)),ROW(BUYUSED!$B$4:$B$525)-ROW(BUYUSED!$B$4)+1),ROW(BUYUSED!$B$4:$B$525)-ROW(BUYUSED!$B$4)+1),ROWS(B$4:B4))),"")

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

สอบถามค่ะ กรณีถ้ามีการเพิ่มข้อมูลแถว 100 แถว
จะต้องเปลี่ยนสูตร จาก

=IFERROR(INDEX(BUYUSED!B$4:B$525,SMALL(IF(FREQUENCY(IF(BUYUSED!$B$4:$B$525<>"",MATCH(BUYUSED!$B$4:$B$525,BUYUSED!$B$4:$B$525,0)),ROW(BUYUSED!$B$4:$B$525)-ROW(BUYUSED!$B$4)+1),ROW(BUYUSED!$B$4:$B$525)-ROW(BUYUSED!$B$4)+1),ROWS(B$4:B4))),"")

เป็น

=IFERROR(INDEX(BUYUSED!B$4:B$625,SMALL(IF(FREQUENCY(IF(BUYUSED!$B$4:$B$625<>"",MATCH(BUYUSED!$B$4:$B$625,BUYUSED!$B$4:$B$625,0)),ROW(BUYUSED!$B$4:$B$625)-ROW(BUYUSED!$B$4)+1),ROW(BUYUSED!$B$4:$B$625)-ROW(BUYUSED!$B$4)+1),ROWS(B$4:B4))),"") ใช่ไหมคะ

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

ขอบคุณค่ะ

มีหลายวิธีครับ เช่น

1. ปรับสูตรเผื่อไว้เท่าที่คิดว่าข้อมูลจะขยายไปถึง
2. ทำต้นทางให้เป็น Table สูตรที่เขียนถึงพื้นที่ใน Table จะขยายเพิ่มลดตามปริมาณข้อมูลได้
3. ใช้ Dynamic Range Name แล้วนำมาใช้ในสูตร
4. ใช้ VBA

ขอบคุณสำหรับคำแนะนำ ของอาจารย์ Snasui และคุณ DhitiBank มากค่ะ