การ Copy แบบ Formula

[meloids]

รบกวนสอบถามอาจารย์ครับว่า

จากไฟล์แนบ

ต้องการ Copy Formula ไปยัง B7:B12 (โดยค่า Formula จะเป็นการเพิ่มที่ละ 2 Cell จาก Sheet 2) อยากถามว่า จะสามารถ Copy โดย Formula เพิ่มที่ละ 2 ตามแบบ B3:B6 ได้อย่างไรครับ

ขอบคุณครับ

[bank9597]

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

ที่ B3 คีย์ =INDIRECT("sheet2!A"&2*ROWS($H$2:H2)-1) คัดลอกลงมา

[meloids]

รบกวนสอบถามคุณ BANK เพิ่มหน่อยครับว่าพอจะอธิบายการใช้สูตรนี้ได้ไหม

ถ้าเกิดว่าผมต้องการไปประยุกต์กับไฟล์อื่นๆน่ะครับ พอดีดู Formula แล้วงงๆ

ขอบคุณล่วงหน้าครับคุณ BANK

[snasui]

อีกสูตรครับ

เซลล์ B3 คีย์

=INDEX(Sheet2!A$1:A$19,2*(ROWS(B$3:B3)-1)+1)

Enter > Copy ไปด้านขวาและลงด้านล่าง

[meloids]

จาก Formula ของอาจารย์ SNASUI อยากสอบถามว่า

=INDEX(Sheet2!A$1:A$19,2*(ROWS(B$3:B3)-1)+1) -1 และ +1 จะมีผลต่อสูตรอย่างไรครับ พอดีกำลังทำความเข้าใจสูตรของอาจารย์ แต่ติดตรง -1 และ +1 ว่ามีผลอย่างไรครับ

ขอบคุณครับ

[snasui]

วิธีการแกะสูตรให้คลิกเซลล์ที่สนใจแล้วเข้าเมนู Formulas > Evaluate Formula > ค่อย ๆ คลิก Evaluate ไปเรื่อย ๆ จะเห็นผลลัพธ์ของแต่ละส่วนประกอบว่าเป็นค่าใด การจะแปลสูตรได้นอกจากทราบว่าได้ค่าใดแล้วต้องทราบว่าฟังก์ชั่นนั้น ๆ มีความหมายว่าอย่างไร จึงจะเข้าใจได้ครับ

จากสูตรนี้ =INDEX(Sheet2!A$1:A$19,2*(ROWS(B$3:B3)-1)+1) หมายถึง ในช่วงข้อมูล Sheet2!A$1:A$19 ให้นำลำดับที่เป็นผลลัพธ์ของสูตร 2*(ROWS(B$3:B3)-1)+1 มาแสดง

หากผลลัพธ์เป็น 1 หมายความว่า จากช่วงข้อมูล Sheet2!A$1:A$19 ให้นำลำดับที่ 1 มาแสดง เช่นนี้เป็นต้น

จากสูตร 2*(ROWS(B$3:B3)-1)+1 เป็นการนำ 2 มาตั้งแล้วคูณด้วย (ROWS(B$3:B3)-1) แล้วบวกด้วย 1 ส่วนที่ต้องทำความเข้าใจคือ ROWS(B$3:B3)-1

จาก ROWS(B$3:B3)-1 หมายถึง จำนวนบรรทัดในช่วง B$3:B3 กรณีนี้มีค่าเป็น 1 เมื่อหักออกไป 1 จะเป็น 0 สูตรจะได้เป็น
2*0+1 และจะได้เป็น 0+1 สุดท้ายผลลัพธ์คือ 1

หากนำไปแทนค่าในฟังก์ชั่น Index ก็จะได้เป็น =INDEX(Sheet2!A$1:A$19,1) ความหมายตามที่กล่าวแล้วด้านบน

ลองฝึกแปล =INDEX(Sheet2!A$1:A$19,2*(ROWS(B$3:B5)-1)+1) ว่าได้ผลลัพธ์เป็นค่าใด จะได้เข้าใจมากขึ้นครับ

[bank9597]

รบกวนสอบถามคุณ BANK เพิ่มหน่อยครับว่าพอจะอธิบายการใช้สูตรนี้ได้ไหม

ถ้าเกิดว่าผมต้องการไปประยุกต์กับไฟล์อื่นๆน่ะครับ พอดีดู Formula แล้วงงๆ

ขอบคุณล่วงหน้าครับคุณ BANK

INDIRECT("sheet2!A ก็คือระบุปลายทางของเซลล์ ส่วน ROWS($H$2:H2) คือแสดงลำดับบรรทัด แล้วคูณด้วย 2 เสมอ ลบ 1 คือให้เริ่มจากบันทัดที่ 1 ก่อนนั่นเอง

[snasui]

เพิ่มเติมสำหรับ Indirect ครับ

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

ข้อจำกัดอีกอย่างหนึ่งคือ หากต้องการเขียนให้ Dynamic เทียบเท่ากับ Index หรือ Offset จะต้องใช้แบบ R1C1 Style ยกตัวอย่างเช่นจากสูตร

=INDEX(Sheet2!A$1:A$19,2*(ROWS(B$3:B3)-1)+1)

หากเขียนด้วย Indirect แล้วให้มีความเป็น Dynamic เท่ากันจะเขียนเป็นด้านล่างครับ

=INDIRECT("Sheet2!R"&2*(ROWS(B$3:B3)-1)+1&"C[-1]",0)

[bank9597]

เพิ่มเติมสำหรับ Indirect ครับ

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

ข้อจำกัดอีกอย่างหนึ่งคือ หากต้องการเขียนให้ Dynamic เทียบเท่ากับ Index หรือ Offset จะต้องใช้แบบ R1C1 Style ยกตัวอย่างเช่นจากสูตร

=INDEX(Sheet2!A$1:A$19,2*(ROWS(B$3:B3)-1)+1)

หากเขียนด้วย Indirect แล้วให้มีความเป็น Dynamic เท่ากันจะเขียนเป็นด้านล่างครับ

=INDIRECT("Sheet2!R"&2*(ROWS(B$3:B3)-1)+1&"C[-1]",0)

[meloids]

ขอบคุณอาจารย์ทั้งสองมากๆครับ

[meloids]

ขออณุญาติข้ามมาถามสูตร SUMIFS นะครับ


=SUMIFS('[BO Decorative 290414.xlsx]Sheet1'!$I$2:$I$190284,'[BO Decorative 290414.xlsx]Sheet1'!$A$2:$A$190284,"E1501")

พอดีว่าผมต้องการระบุข้อมูลเพิ่ม E1501DD เข้าไปใน '[BO Decorative 290414.xlsx]Sheet1'!$A$2:$A$190284 ด้วยจะต้องใช้สูตรอย่างไรครับ

โดยต้องการให้สูตรระบุทั้ง "E1501 และ E1501DD" น่ะครับซึ่ง 2 ข้อมูลนี้จะอยู่ใน Column เดียวกัน

[snasui]

กรณีเรื่องที่ถามไม่ได้เกี่ยวเนื่องจากคำถามเดิมให้เปิดกระทู้ใหม่ครับ