สอบถามเกี่ยวกับการ link ข้อมูลจากตาราง pivot แล้ว Erro เป็น #REF!

[asawin77]

สอบถามเกี่ยวกับการ link ข้อมูลมาจากตาราง pivot แล้วเกิด Error เป็น #REF! ทุกครั้งที่มีการ Update Pivot ต้นทาง ต้องไปแก้ link ให้ตารางสรุปปลายทางตลอด ไม่ทราบว่ามีวิธีการแก้ไขไมครับ โดยแค่กด refresh ก็ให้ update ข้อมูลได้เลยโดยไม่ให้เกิด Error เป็น #REF!

[snasui]

เข้าใจว่าเป็นการ Link แบบตรง ๆ จึงเกิดปัญหาเช่นนั้นครับ

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

[asawin77]

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

[snasui]

ไฟล์ที่แนบมาเป็นการ Link มาจากไฟล์ต้นทางที่ไม่ได้แนบมาด้วย

ตัวอย่างสูตร Vlookup ข้ามไฟล์ โดยเปิดไฟล์ต้นทางเอาไว้กอ่นครับ

ที่ชีต Report เซลล์ C8 มีรูปแบบสูตรเป็น

=Vlookup($A8,'[05-FC-จ่าย AC-PIVOT-05-07-21.xlsx]PIVOT-FC-PR'!$A$6:$Z$1000,10,0)

Enter

สูตรด้านบนเป็นแค่รูปแบบต้องปรับส่วนประกอบที่ระยายสีเองให้ตรงกับความต้องการครับ

[asawin77]

ขอบคุณครับ ยังสงสัยถ้ามีการ Update pivot แล้วcolumn เปลี่ยนไป สูตรยังจะตามหาเจอตรงเดือนไมครับ เพราะเดือนจะเพิ่มลดทุกครั้งที่ Update ครับ

[snasui]

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

[asawin77]

รบกวนแนะนำสูตร Match ได้ไมครับ เพราะถ้าต้องมาเปลี่ยนก็อาจจะคล้ายๆกับสูตรเดิมครับ รบกวนด้วยครับ

[snasui]

ตัวอย่าง Match ครับ

=Match(C$7,'[05-FC-จ่าย AC-PIVOT-05-07-21.xlsx]PIVOT-FC-PR'!$A$1:$Z$1,0)

ปรับส่วนประกอบในสูตร Match ให้ตรงกับที่ใช้จริง

[asawin77]

สูตรนี้สามารถนำจำนวนเงินมาแสดงตามเดือนได้ใช่ไมครับ ผมไม่ถนัด Match กำลังเรียนรู้ไปด้วยครับ

[snasui]

Match จะให้ค่าเป็นลำดับที่ ใช้ประกอบกับสูตรอื่นเช่น Vlookup เพื่อหาคอลัมน์ที่ ใช้ประกอบกับ Index เพื่อหาบรรทัดที่ คอลัมน์ที่ เป็นต้น

สรุปโดยรวม การหาค่าใด ๆ ที่บรรทัดไม่คงที่ คอลัมน์ไม่คงที่จึงใช้ Match เข้าไปช่วยได้ครับ

[asawin77]

ขอสูตรได้ไมครับใช้กับไฟล์นี้ ใช้ Vlookup ได้แล้วครับแต่อยากได้ Match ช่วยด้วยเนื่องจาก column rows ไม่คงที่ครับ

[snasui]

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

การตอบปัญหาที่ไม่เห็นไฟล์ จะเขียนสูตรให้ตรงกับตำแหน่งจริงย่อมยากที่จะเป็นไปได้ครับ

[asawin77]

ผมลอง ใช้ vlookup +Match บรรทัดแรก A8 ถูกต้อง แต่เมื่อลากสูตรลงล่างสูตรไปอ่านค่า column อื่นมาครับ ต้องการให้อ่านค่าลงล่างใน column เดียวกันครับ รบกวนช่วยแนะนำด้วยครับ ตามไฟล์แนบ

[logic]

แบบนี้หรือเปล่าครับ

B8 =VLOOKUP(A8,pivot!$A$4:$K$12,MATCH(pivot!$I$4,pivot!$A$4:$Z$4,0),0) คัดลอกสูตรลง

ดูตัวอย่างแล้วก็นึกไม่ออกว่าใช้ match ไปหาอะไร คีย์เลข 9 ลงไปแทน match ก็ได้

แต่ถ้าเป็นแบบรูปคือคีย์คอลัมน์ที่จะดึงตัวเลขไว้ใน D6 แล้วให้สูตรไปหา สูตรคือ

B8 =VLOOKUP(A8,pivot!$A$4:$K$12,MATCH($D$6,pivot!$A$4:$Z$4,0),0)

ใส่เครื่องหมาย $ อะไรไว้ตรงไหนก็เป็นเรื่องที่ต้องเข้าใจ ไม่งั้นทำงานกับสูตรยากครับ

[asawin77]

ไม่ใช่ครับ คุณเข้าใจโจทย์ผมผิดครับ ( เป็นการหาค่าใด ๆ ที่บรรทัดไม่คงที่ คอลัมน์ไม่คงที่จึงใช้ Match เข้าไปช่วยได้ครับ)

: ดูตัวอย่างแล้วก็นึกไม่ออกว่าใช้ match ไปหาอะไร คีย์เลข 9 ลงไปแทน match ก็ได้
=> ไม่ได้ครับ เพราะ คอลัมน์ไม่คงที่ จึงใช้ Match ช่วยแต่ผมยังไม่เก่ง ที่ C8 สูตรดึงค่า 01 ของเดือน พ.ค มาถูกต้องแล้ว และเมื่อลากสูตรลงล่างสูตรควรดึงค่า 02.....08 ของเดือน พ.ค มาแสดง แต่สูตรไปดึงของเดือน มี.ค มาแทนครับ
ที่ผมต้องการคือเมื่อลากลงล่างให้สูตรดึงค่า 01 ,02,03,04.....08 ของเดือน พ.ค ใน sheet pivot มาแสดง ถึงแม้บรรทัด หรือ column เปลี่ยนไปครับ

[logic]

B8 =VLOOKUP(A8,pivot!$A$4:$K$12,MATCH(pivot!$I$4,pivot!$A$4:$Z$4,0),0) คัดลอกสูตรลง

แก้สูตรในไฟล์ดูเฉพาะเรื่องการใช้ $ ที่ผมใส่สี เช็คว่าได้คำตอบตรงไหมครับ

[asawin77]

ok ได้แล้วครับ ขอบคุณมากครับ

[asawin77]

มี Update ครับ Copy สูตรลงล่างได้ค่ามาตรงตามต้องการ คือของเดือน พ.ค แต่เมื่อเราย้าย column ใน Sheet pivot สูตรเปลี่ยนไปดึงของ column อื่นมาแทนครับ ตามไฟล์ตัวอย่างผมย้าย column ของเดือน พ.ค ที่มาแสดงไปอยู่ท้ายสุดสูตรไม่ตามไปด้วยแต่กลับไปดึง column อื่นแทนครับ เกิดจากอะไรครับ

[snasui]

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

จากสูตร

=VLOOKUP(A8,pivot!$A$4:$K$12,MATCH(pivot!$I$4,pivot!$A$4:$Z$4,0),0)

เกิดจากค่าที่ระบายสีเอาไว้ครับ

การย้ายเดือนพ.ค.ไปที่ใดก็ไม่มีผลให้สูตรต้องตามไปหาค่านั้น ทั้งนี้เพราะเขียนให้ไป Match ที่ I4 ซึ่ง I4 เป็นค่าใดก็จะไปหาค่านั้นอยู่ตลอดไปครับ

ถ้าจะให้ Match เดือน พ.ค. ดังที่กล่าวมา สูตรควรจะเป็นตามด้านล่าง หรือคีย์ค่า พ.ค.-21 เอาไว้ในเซลล์ใด ๆ แล้วไปอ้างอิงมาใช้เพื่อให้สูตรมีความยืดหยุ่น ไม่ต้องคอยแก้ไขสูตรเมื่อต้องการจะดูเดือนอื่น ๆ

=VLOOKUP(A8,pivot!$A$4:$K$12,MATCH("พ.ค.-21",pivot!$A$4:$Z$4,0),0)

[asawin77]

เริ่มเข้าใจมากขึ้นแล้ว ขอบคุณมากครับอาจารย์