Page 1 of 1
เราจะcopyสูตรแบบกำหนดระยะห่างของคอลัมน์ได้อย่างไรครับ
Posted: Thu Dec 20, 2012 12:31 pm
by moolandxp
คือผมอยากจะทำการคัดลอกสูตรโดยการลากคลุมจากอาทิตย์หนึ่งไปยังอีกอาทิตย์หนึ่งแต่ชีทที่อ้างอิงข้อมูลของคอลัมน์ไม่เท่ากัน
ทำให้เวลาคลุมลากสูตรไปแล้วสูตรมันไม่พอดีครับผมส่งไฟล์ตัวอย่างมาให้ช่วยดูด้วยครับ
ลองหาวิธีแก้ใน google แล้วไม่เจอครับ
ขอบคุณครับ
Re: เราจะcopyสูตรแบบกำหนดระยะห่างของคอลัมน์ได้อย่างไรครับ
Posted: Thu Dec 20, 2012 1:20 pm
by snasui
การดึงข้อมูลมาตามที่ต้องการนั้นมีความซับซ้อน จะเขียนสูตรเช่นที่เขียนมานั้นไม่ได้ครับ
ที่ชีท Sale by Rev ลองตามนี้ครับ
- ที่ C1, K1, S1 คีย์เป็นวันเริ่มสัปดาห์คือ 31/12/2012, 7/1/2013, 14/1/2013 ตามลำดับ
- ที่เซลล์ 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 ไปยังเซลล์อื่น ๆ ที่เกี่ยวข้อง
Re: เราจะcopyสูตรแบบกำหนดระยะห่างของคอลัมน์ได้อย่างไรครับ
Posted: Thu Dec 20, 2012 5:54 pm
by moolandxp
ขอบคุณอาจารย์มากๆเลยครับ
อาจารย์ครับผมลองใส่สูตรตามที่อาจารย์บอกแล้วมันเป็นตามรูปครับ
Re: เราจะcopyสูตรแบบกำหนดระยะห่างของคอลัมน์ได้อย่างไรครับ
Posted: Thu Dec 20, 2012 9:01 pm
by snasui
ให้แนบเป็นไฟล์ Excel แทนแนบเป็นภาพจะได้ช่วยตรวจสอบได้ครับ
Re: เราจะcopyสูตรแบบกำหนดระยะห่างของคอลัมน์ได้อย่างไรครับ
Posted: Thu Dec 20, 2012 11:26 pm
by moolandxp
ขอบคุณครับอาจารย์ผมแนบไฟล์ที่แก้มาให้ช่วยดูแล้วครับ
Re: เราจะcopyสูตรแบบกำหนดระยะห่างของคอลัมน์ได้อย่างไรครับ
Posted: Fri Dec 21, 2012 2:26 am
by 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 แล้วคัดลอกสูตรไปทางขวามือ แล้วคัดลอกสูตรลงล่างพร้อมกัน
Re: เราจะcopyสูตรแบบกำหนดระยะห่างของคอลัมน์ได้อย่างไรครับ
Posted: Fri Dec 21, 2012 7:00 am
by snasui
moolandxp wrote:ขอบคุณครับอาจารย์ผมแนบไฟล์ที่แก้มาให้ช่วยดูแล้วครับ
ผมเข้าใจว่าเครื่องที่คุณ moolandxp ใช้ กำหนด Regional and Language ไว้เป็นแบบอื่นที่ไม่ใช่ Thai เมื่อคีย์วันที่ในรูปแบบ วัน/เดือน/ปี จึงแสดงผลเป็น Text แทนที่จะแสดงผลเป็น Number
Text จะชิดซ้ายของเซลล์ ส่วน Number จะชิดขวาของเซลล์เสมอ สำหรับ วัน เวลา คือตัวเลข หรือ Number เมื่อคีย์ลงในเซลล์ที่ยังไม่จัด Format เป็นอย่างอื่นจะต้องชิดขวาเท่านั้นครับ
การทดสอบว่าเครื่องของเรากำหนด Regional and Language เป็นแบบ วัน/เดือน/ปี หรือ เดือน/วัน/ปี ให้คีย์ลงในเซลล์ใด ๆ ด้วยฟังก์ชั่น
=Today()
Enter > สังเกตดูว่า วันขึ้นก่อนหรือเดือนขึ้นก่อน ถ้าเดือนขึ้นก่อนแสดงว่าไม่ได้จัด Regional and Language เป็นไทย
สำหรับไฟล์ที่แนบมาผมแค่กดแป้น F2 ที่เซลล์ C1 แล้ว Enter ก็แสดงผลลัพธ์ได้ปกติ ดูไฟล์แนบประกอบครับ
Re: เราจะcopyสูตรแบบกำหนดระยะห่างของคอลัมน์ได้อย่างไรครับ
Posted: Fri Dec 21, 2012 3:19 pm
by moolandxp
ขอบพระคุณทุกท่านมากๆครับได้ความรู้ใหม่อีกแล้ว
Re: เราจะcopyสูตรแบบกำหนดระยะห่างของคอลัมน์ได้อย่างไรครับ
Posted: Fri Dec 21, 2012 10:52 pm
by 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))
จะได้เอาไปใช้กับงานอื่นๆด้วยครับ
ขอบคุณครับ
Re: เราจะcopyสูตรแบบกำหนดระยะห่างของคอลัมน์ได้อย่างไรครับ
Posted: Sat Dec 22, 2012 7:49 am
by snasui
moolandxp wrote:OFFSET('Sale by Wk'!$A$3,MATCH($B4,'Sale by Wk'!$A$3:$A$28,0),1,8)
ฟังก์ชั่น Offset เป็นการกำหนดขอบเขตพื้นที่เซลล์ที่เราสนใจ จากสูตรด้านบนความหมายคือ
- จากตำแหน่ง 'Sale by Wk'!$A$3
- ให้ลงมาจำนวน MATCH($B4,'Sale by Wk'!$A$3:$A$28,0) บรรทัด
- เลื่อนไปทางขวา 1 คอลัมน์
- ความสูงของพื้นที่ 8 บรรทัด
- ไม่ได้กำหนดความกว้างแสดงว่าความกว้างเป็น 1 คอลัมน์
อ่านเพิ่มเติมเกียวกับฟังก์ชั่น Offset ที่นี่ครับ
http://snasui.blogspot.com/2009/03/offset.html