Snasui Excel Forum

สวัสดีครับ อจ และ ทุกๆท่านในบอร์ด

วันนี้ผมมีคำถามเรื่องการใช้ offset นะครับ ว่าสามารถใช้กับการ sum ข้อมูล คล่อมทีละ 2 ได้มั้ย
จากชีทที่แนบมา ผมมี เวลาแบ่งเป็น Day Night ในแต่ละวัน
ผมต้องทำการ sum ข้อมูล ของแต่ละ กะให้มาเป็น วันเดียวกันก่อน แต่ทุกวันนี้ใช้ =sum(S3:T3) อยู่และต้องคอยปรับเอง เพราะลากไปแล้วมันไม่ขยับทีละ2 คอลัม

ปกติจะใช้ offset เพื่อที่จะดึงข้อมูลมาอย่างเดียวไม่เคยลองเอามาเลือก range แล้ว sum ครับ

scars13 wrote: Wed Mar 21, 2018 9:48 am สวัสดีครับ อจ และ ทุกๆท่านในบอร์ด

วันนี้ผมมีคำถามเรื่องการใช้ offset นะครับ ว่าสามารถใช้กับการ sum ข้อมูล คล่อมทีละ 2 ได้มั้ย
จากชีทที่แนบมา ผมมี เวลาแบ่งเป็น Day Night ในแต่ละวัน
ผมต้องทำการ sum ข้อมูล ของแต่ละ กะให้มาเป็น วันเดียวกันก่อน แต่ทุกวันนี้ใช้ =sum(S3:T3) อยู่และต้องคอยปรับเอง เพราะลากไปแล้วมันไม่ขยับทีละ2 คอลัม

ปกติจะใช้ offset เพื่อที่จะดึงข้อมูลมาอย่างเดียวไม่เคยลองเอามาเลือก range แล้ว sum ครับ

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

ตัวอย่างคือ

F G H I J K L M N O
===================================================
19D 19N 20D 20N 21D 21N 22D 22N 23D 23 N
190 150 160 180 170 200 160 200 190 160


ผลลัพธ์ ผมอยากไปไปออกที่ X Y Z AA AB
X Y Z AA AB
190+150 160+180 170+200 16+200 190+160


ผมใช้สูตร =OFFSET($F7,0,COLUMNS($F$3:F3)-1)+OFFSET($F7,0,COLUMNS($F$3:G3)-1)
ข้อมูล วันที่ 19 ได้อยู่นะครับ แต่พอลากไปมันไม่โดดตาม

ที่x7=SUM(OFFSET($F7,0,COLUMNS($F$3:F3)*2-2,1,2)) copy right and down

puriwutpokin wrote: Wed Mar 21, 2018 2:38 pm ที่x7=SUM(OFFSET($F7,0,COLUMNS($F$3:F3)*2-2,1,2)) copy right and down

ขอบคุณครับ ฟังก์ชั่น offset นี่ยังไงเราก็ต้องทำ Reference ตำแหน่งไปส่วนนึง ไม่สามรถยุบไว้ใน สุตรของเซลล์นั้นๆได้ใช่มั้ยครับ
ผมยัง งงๆกับ ฟังชั่นนี้อยู่เลยเวลาเอามารวมร่างกับการคิดเป็น นับทีละ 2 column แล้วถ้า เราบวกทีละ 3 4 5 n column ตรงส่วนไหนที่เราจะต้องไปปรับครับ

COLUMNS($F$3:F3)*2-2,1,2 ตัวนี้พอจะช่วย อธิบายเพิ่มเติมอีกนิดได้มั้ยครับ



ผมได้มาอีกวิธีนึงแต่ถ้าหัวไม่ใช่ เป็นซีรี่วันที่อาจจะหายาดนิดนึงนะครับ

=SUMIFS($F7:$O7,$F$6:$O$6,DAY(S$6)&" "&"*")

COLUMNS($F$3:F3)*2-2
COLUMNS($F$3:F3) = จะคืนค่าเป็นลำดับของคอลัมภ์ โดยหาก Copy สูตรไปทางขวาลำดับก็จะเพิ่มขึ้นเรื่อยๆเป็น 2,3,4,5,... แล้วนำลำดับที่ได้มาทำการ *2(คูณ2) แล้ว -2(ลบ2)
ผลลัพท์ที่ได้ก็จะเป็น 0,2,4,6,..... หาก Copy สูตรไปทางขวา ซึ่งคือจุดเริ่มที่จะเลือกข้อมูลนั่นเองครับ

ส่วนความกว้าง คือ เลข 2 ที่อยู่ในไวยากรณ์ width ของสูตร offset ครับ
=SUM(OFFSET($F7,0,COLUMNS($F$3:F3)*2-2,1,2))

scars13 wrote: Wed Mar 21, 2018 3:48 pm

puriwutpokin wrote: Wed Mar 21, 2018 2:38 pm ที่x7=SUM(OFFSET($F7,0,COLUMNS($F$3:F3)*2-2,1,2)) copy right and down

ขอบคุณครับ ฟังก์ชั่น offset นี่ยังไงเราก็ต้องทำ Reference ตำแหน่งไปส่วนนึง ไม่สามรถยุบไว้ใน สุตรของเซลล์นั้นๆได้ใช่มั้ยครับ
ผมยัง งงๆกับ ฟังชั่นนี้อยู่เลยเวลาเอามารวมร่างกับการคิดเป็น นับทีละ 2 column แล้วถ้า เราบวกทีละ 3 4 5 n column ตรงส่วนไหนที่เราจะต้องไปปรับครับ

COLUMNS($F$3:F3)*2-2,1,2 ตัวนี้พอจะช่วย อธิบายเพิ่มเติมอีกนิดได้มั้ยครับ



ผมได้มาอีกวิธีนึงแต่ถ้าหัวไม่ใช่ เป็นซีรี่วันที่อาจจะหายาดนิดนึงนะครับ

=SUMIFS($F7:$O7,$F$6:$O$6,DAY(S$6)&" "&"*")

โครงสร้างสูตร=OFFSET(reference, rows, cols, [height], [width])
ซึ่ง COLUMNS($F$3:F3)*2-2,1,2
ซึ่งดูแต่ละสี แต่ละตำแหน่งครับ ซึ่งสีแดง จะเป็นการทำให้คอลัมน์เว้นที่ละ2 คอลัมน์โดยเริ่มที่ 0 ที่ -2 คือเว้นที่ละ n ช่วงในที่นี้คือ2
สรุป จะได้ COLUMNS($F$3:F4)*n-n ซึ่ง n คือช่วงที่ต้องการให้เว้น โดย อันนี้จะเริ่มที่ 0 ก่อนนะครับ

parakorn wrote: Wed Mar 21, 2018 4:26 pm COLUMNS($F$3:F3)*2-2
COLUMNS($F$3:F3) = จะคืนค่าเป็นลำดับของคอลัมภ์ โดยหาก Copy สูตรไปทางขวาลำดับก็จะเพิ่มขึ้นเรื่อยๆเป็น 2,3,4,5,... แล้วนำลำดับที่ได้มาทำการ *2(คูณ2) แล้ว -2(ลบ2)
ผลลัพท์ที่ได้ก็จะเป็น 0,2,4,6,..... หาก Copy สูตรไปทางขวา ซึ่งคือจุดเริ่มที่จะเลือกข้อมูลนั่นเองครับ

ส่วนความกว้าง คือ เลข 2 ที่อยู่ในไวยากรณ์ width ของสูตร offset ครับ
=SUM(OFFSET($F7,0,COLUMNS($F$3:F3)*2-2,1,2))

ขอบคุณครับ พอจะมองเห็นภาพชัดขึ้นเลย เด๊่ยวจะลองเอาไปใช้งานบ่อยๆ ขอบคุณครับ

puriwutpokin wrote: Wed Mar 21, 2018 4:29 pm

scars13 wrote: Wed Mar 21, 2018 3:48 pm

puriwutpokin wrote: Wed Mar 21, 2018 2:38 pm ที่x7=SUM(OFFSET($F7,0,COLUMNS($F$3:F3)*2-2,1,2)) copy right and down

ขอบคุณครับ ฟังก์ชั่น offset นี่ยังไงเราก็ต้องทำ Reference ตำแหน่งไปส่วนนึง ไม่สามรถยุบไว้ใน สุตรของเซลล์นั้นๆได้ใช่มั้ยครับ
ผมยัง งงๆกับ ฟังชั่นนี้อยู่เลยเวลาเอามารวมร่างกับการคิดเป็น นับทีละ 2 column แล้วถ้า เราบวกทีละ 3 4 5 n column ตรงส่วนไหนที่เราจะต้องไปปรับครับ

COLUMNS($F$3:F3)*2-2,1,2 ตัวนี้พอจะช่วย อธิบายเพิ่มเติมอีกนิดได้มั้ยครับ



ผมได้มาอีกวิธีนึงแต่ถ้าหัวไม่ใช่ เป็นซีรี่วันที่อาจจะหายาดนิดนึงนะครับ

=SUMIFS($F7:$O7,$F$6:$O$6,DAY(S$6)&" "&"*")

โครงสร้างสูตร=OFFSET(reference, rows, cols, [height], [width])
ซึ่ง COLUMNS($F$3:F3)*2-2,1,2
ซึ่งดูแต่ละสี แต่ละตำแหน่งครับ ซึ่งสีแดง จะเป็นการทำให้คอลัมน์เว้นที่ละ2 คอลัมน์โดยเริ่มที่ 0 ที่ -2 คือเว้นที่ละ n ช่วงในที่นี้คือ2
สรุป จะได้ COLUMNS($F$3:F4)*n-n ซึ่ง n คือช่วงที่ต้องการให้เว้น โดย อันนี้จะเริ่มที่ 0 ก่อนนะครับ

ขอบคุณครับ ผมขอลองนำสูตรนี้ไป ทำข้อมูลดูก่อนนะครับ