เราจะcopyสูตรแบบกำหนดระยะห่างของคอลัมน์ได้อย่างไรครับ

[moolandxp]

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

ลองหาวิธีแก้ใน google แล้วไม่เจอครับ

ขอบคุณครับ

[snasui]

การดึงข้อมูลมาตามที่ต้องการนั้นมีความซับซ้อน จะเขียนสูตรเช่นที่เขียนมานั้นไม่ได้ครับ

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

1. ที่ C1, K1, S1 คีย์เป็นวันเริ่มสัปดาห์คือ 31/12/2012, 7/1/2013, 14/1/2013 ตามลำดับ
2. ที่เซลล์ D4 คีย์สูตร
   =SUMPRODUCT(--(OFFSET('Sale by Wk'!$A$3,MATCH($B4,'Sale by Wk'!$A$3:$A$28,0),1,8)=D$3)*OFFSET('Sale by Wk'!$A$3,MATCH($B4,'Sale by Wk'!$A$3:$A$28,0),MATCH(LOOKUP(9.99999999999999E+307,$C$1:D$1),'Sale by Wk'!$B$3:$AD$3,0),8,7))
   Enter > Copy ไปยังเซลล์อื่น ๆ ที่เกี่ยวข้อง

[moolandxp]

ขอบคุณอาจารย์มากๆเลยครับ
อาจารย์ครับผมลองใส่สูตรตามที่อาจารย์บอกแล้วมันเป็นตามรูปครับ

[snasui]

ให้แนบเป็นไฟล์ Excel แทนแนบเป็นภาพจะได้ช่วยตรวจสอบได้ครับ

[moolandxp]

ขอบคุณครับอาจารย์ผมแนบไฟล์ที่แก้มาให้ช่วยดูแล้วครับ

[bank9597]

เพิ่มเติมอีกวิธีครับ
ชีท "Sale by Wk" เซลล์ AE4 คีย์ =IF(AND(A4="",B4=""),"",IF(AND(A4<>"",B4=""),"",LOOKUP(CHAR(255),$A$3:A4))) คัดลอกลงมา
ชีท "Sale by Wk" เซลล์ AF4 คีย์ =IF(AND(A4="",B4=""),"",IF(AND(A4<>"",B4=""),"",LOOKUP(CHAR(255),$B$3:B4))) คัดลอกลงมา

ชีท "Sale by Rev" เซลล์ D1 คีย์ 31/12/2012
ชีท "Sale by Rev" เซลล์ L1 คีย์ 7/1/2013
ชีท "Sale by Rev" เซลล์ T1 คีย์ 14/1/2013

ชีท "Sale by Rev" เซลล์ D4 คีย์ =SUM(IF($B4='Sale by Wk'!$AE$4:$AE$28,IF(D$3='Sale by Wk'!$AF$4:$AF$28,IF('Sale by Wk'!$C$3:$AD$3>=LOOKUP(9.99999999999999E+307,$D$1:D1),'Sale by Wk'!$C$3:$AD$3<=LOOKUP(9.99999999999999E+307,$D$1:D1)+6)*'Sale by Wk'!$C$4:$AD$28))) กด Ctrl+Shift+Enter แล้วคัดลอกสูตรไปทางขวามือ แล้วคัดลอกสูตรลงล่างพร้อมกัน

[snasui]

ขอบคุณครับอาจารย์ผมแนบไฟล์ที่แก้มาให้ช่วยดูแล้วครับ

ผมเข้าใจว่าเครื่องที่คุณ moolandxp ใช้ กำหนด Regional and Language ไว้เป็นแบบอื่นที่ไม่ใช่ Thai เมื่อคีย์วันที่ในรูปแบบ วัน/เดือน/ปี จึงแสดงผลเป็น Text แทนที่จะแสดงผลเป็น Number

Text จะชิดซ้ายของเซลล์ ส่วน Number จะชิดขวาของเซลล์เสมอ สำหรับ วัน เวลา คือตัวเลข หรือ Number เมื่อคีย์ลงในเซลล์ที่ยังไม่จัด Format เป็นอย่างอื่นจะต้องชิดขวาเท่านั้นครับ

การทดสอบว่าเครื่องของเรากำหนด Regional and Language เป็นแบบ วัน/เดือน/ปี หรือ เดือน/วัน/ปี ให้คีย์ลงในเซลล์ใด ๆ ด้วยฟังก์ชั่น

=Today()

Enter > สังเกตดูว่า วันขึ้นก่อนหรือเดือนขึ้นก่อน ถ้าเดือนขึ้นก่อนแสดงว่าไม่ได้จัด Regional and Language เป็นไทย

สำหรับไฟล์ที่แนบมาผมแค่กดแป้น F2 ที่เซลล์ C1 แล้ว Enter ก็แสดงผลลัพธ์ได้ปกติ ดูไฟล์แนบประกอบครับ

[moolandxp]

ขอบพระคุณทุกท่านมากๆครับได้ความรู้ใหม่อีกแล้ว

[moolandxp]

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

=SUMPRODUCT(--(OFFSET('Sale by Wk'!$A$3,MATCH($B4,'Sale by Wk'!$A$3:$A$28,0),1,8)=D$3)*OFFSET('Sale by Wk'!$A$3,MATCH($B4,'Sale by Wk'!$A$3:$A$28,0),MATCH(LOOKUP(9.99999999999999E+307,$C$1:D$1),'Sale by Wk'!$B$3:$AD$3,0),8,7))


จะได้เอาไปใช้กับงานอื่นๆด้วยครับ

ขอบคุณครับ

[snasui]

OFFSET('Sale by Wk'!$A$3,MATCH($B4,'Sale by Wk'!$A$3:$A$28,0),1,8)

ฟังก์ชั่น Offset เป็นการกำหนดขอบเขตพื้นที่เซลล์ที่เราสนใจ จากสูตรด้านบนความหมายคือ

1. จากตำแหน่ง 'Sale by Wk'!$A$3
2. ให้ลงมาจำนวน MATCH($B4,'Sale by Wk'!$A$3:$A$28,0) บรรทัด
3. เลื่อนไปทางขวา 1 คอลัมน์
4. ความสูงของพื้นที่ 8 บรรทัด
5. ไม่ได้กำหนดความกว้างแสดงว่าความกว้างเป็น 1 คอลัมน์

อ่านเพิ่มเติมเกียวกับฟังก์ชั่น Offset ที่นี่ครับ http://snasui.blogspot.com/2009/03/offset.html