ดึงข้อมูลข้ามชี้ตทั้ง คอลัมพ์ ผมใช้สูตร SUM Product

[godman]

ผมใช้สูตร sumproduct เพื่อให้แสดงค่าใน sheet WW37 โดยต้อง update ตามข้อมูลใน AD FORE ครับ ผมอยากให้แสดงใน ww37 38 39 หรืออื่นๆ ผมทดลองทำแล้วโดยใช้ความรู้ใน web นี้แต่ติดปัญหาตรงใหน ช่วยอธิบายด้วยครับ อยากก้าวหน้ามากกว่านี้

[snasui]

สำหรับที่ผิดผมทำสีไว้ให้ด้านล่างครับ หากต้องการ Copy ไปใช้ได้จะต้องมี $ ที่ A ด้วย

ที่ Sheet ww37 เซลล์ D5 คีย์

=SUMPRODUCT(--($A5='AD FORE'!$A$5:$A$35),--(D$4='AD FORE'!$BX$5:$BX$35),'AD FORE'!$AO$5:$AO$35)

Enter ไม่ต้อง Ctrl+Shift+Enter เนื่องจาก Sumproduct เป็นสูตร Array ตามธรรมชาติ จากนั้ืน Copy ไปด้านขวาและลงด้านล่าง

[godman]

=SUMPRODUCT(--($A5='AD FORE'!$A6:$A$35),--(D$4='AD FORE'!$BX$4:$BX$33),'AD FORE'!$AO$6:$AO$35)
ถ้าสองตารางนั้น บรรทัดไม่ตรงกันมันก็มีปัญหานะครับ ผมลองแล้ว มัน ไม่ได้ครับ

[snasui]

ที่ไม่ได้ก็เพราะช่วงข้อมูลไม่เท่ากันครับ ไม่ใช่เฉพาะแต่ Sumproduct การใช้สูตรหรือฟังก์ชั่นในลักษณะนี้ต้องระวังอย่างยิ่งยวด

ช่วงข้อมูลใน Sumproduct ต้องเท่ากันเสมอ เริ่มและสิ้นสุดที่บรรทัดเดียวกัน และช่วงข้อมูลที่เป็นผลลัพธ์ห้ามมีค่าผิดพลาดเด็ดขาดครับ โอ้ว...น่ากลัว

[godman]

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

[godman]

ผมเอางานที่ผมทำมาให้ อาจารย์ดู แต่ผมว่ามันยัง ไม่สามารถ auto ได้ ผมอยาให้เพียงแค่ ใส่ ในช่องสีน้ำเงิน ww37 38 โดยผมรู้ว่า Part และ Model Name มันคงที่เหมือนกับ Dheet data คือ AD FORE ครับ ผมอยากให้มันสามารถ auto ได้เพียงกรอก ww number ลงไปแล้วดึงข้อมูลที่ตรงกันซี่งอยู่ใน sheet AD FORE (sheet นี้จะถูก update ทุก ชั่วโมง ครับ)
สูตรที่ผมส่งมาให้เป็นความพยายามที่จะใช้สูตร Index แต่ว่าต้องตามเปลี่ยน name rang ทุกครั้งที่มีข้อมูลใหม่มา ครับ ผมอยากได้วิธีของอาจารย์ ผมเคยใช้สูตร sumproduct แต่มันไม่ work หรือว่าอาจารย์ช่วยทำให้ดูสัก 2 สัปดาห์ได้ไหมครับ

อ้อ สิ่งที่ทำอยู่ใน Sheet Report ครับ

[snasui]

รบกวนช่วยแปลงให้เป็น 2003 มาให้อีกรอบครับ Edit ความเห็นเดิม แล้ววางแทนไฟล์เดิมก็ได้ หรือจะแนบมาในความเห็นใหม่ในกระทู้นี้ก็ได้ เนื่องจากตอนนี้เล่น Netbook ไม่มี Excel Version อื่น ต้องขออภัยในความไม่สะดวกสำหรับครั้งนี้ครับ ปกติแล้วทุกท่านสามารถแนบ Excel Version ไหนก็ได้เนื่องจากผมใช้ Notebook ในการตอบปัญหาเป็นหลักซึ่งมี Excel ทุก Version

[godman]

ส่งครับ

[snasui]

ลองตามนี้ครับ ที่ D6 คีย์

=INDEX(xx,MATCH($A6,'AD FORE'!$A$4:$A$35,0),MATCH(D$5,'AD FORE'!$A$4:$BU$4,0))

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

[godman]

ขอบพระคุณเป็นอย่างสูง ผมเพิ่งเคยเห็นสูตร index(array),match(),match())) เป็นครั้งแรกครับ ไม่นึกว่าจะมี match แบบสองชั้น รู้สึกดีกับชีวิตมากเลยครับ ที่งานของผมมีที่ปรึกษาที่เก่งที่สุด
ขอบพระคุณอีกครังครับ index match match นี่มันยึดหยุ่นและจำง่ายกว่ากว่า sumproduct นะเนี่ย

[godman]

ผมลองให้ชื่อ Rangename ของผมคือ ww ผมอยากให้มันใช้ไปเรื่อยๆไม่ว่าจะกี่คอลัมพ์ที่ผู้ใช้ตองการ ปกติผมตั้งไว้ที่ คอลัมพ์ BU ผมใช้สูตร offset(A1,4,0,COUNTA(A4:A)) โดยประมาณนะครับ แต่ว่าพอผมเอาไปใช้ มัน ผลคือ ไม่ได้ครับ
1.ผมสามารถใช้สูตร offset ได้ใช่ไหมครับ
2.ผมอยากให้มันเริ่มพื้นที่ที่ใต้แถวลงมา 4 บรรทัดใช่ไหมครับ และ ผมไม่ใส่สูตรว่า -1 ด้วยมันจึงไม่ขึ้นใช่ไหมครับ -1 นี่คืออะไรครับ
3.รบกวนอาจารย์ช่วยหน่อยครับ ว่าจะเขียนสูตรนี้ให้ถูกต้องได้อย่างไรครับ ไฟล์เดิมนะครับ ในชอง ww37 ครับ

[snasui]

ผมลองให้ชื่อ Rangename ของผมคือ ww ผมอยากให้มันใช้ไปเรื่อยๆไม่ว่าจะกี่คอลัมพ์ที่ผู้ใช้ตองการ ปกติผมตั้งไว้ที่ คอลัมพ์ BU ผมใช้สูตร offset(A1,4,0,COUNTA(A4:A)) โดยประมาณนะครับ แต่ว่าพอผมเอาไปใช้ มัน ผลคือ ไม่ได้ครับ
1.ผมสามารถใช้สูตร offset ได้ใช่ไหมครับ
2.ผมอยากให้มันเริ่มพื้นที่ที่ใต้แถวลงมา 4 บรรทัดใช่ไหมครับ และ ผมไม่ใส่สูตรว่า -1 ด้วยมันจึงไม่ขึ้นใช่ไหมครับ -1 นี่คืออะไรครับ
3.รบกวนอาจารย์ช่วยหน่อยครับ ว่าจะเขียนสูตรนี้ให้ถูกต้องได้อย่างไรครับ ไฟล์เดิมนะครับ ในชอง ww37 ครับ

ตอบ
1. ได้แน่นอนครับ
2. ฟังก์ชั่น Offset ดูที่ผมอธิบายไว้ค่อนข้างละเอียดที่นี่ครับ http://snasui.blogspot.com/2009/03/offset.html
3. ตอบแบบยังไม่ดูข้อมูลในไฟล์นะครับ

=Offset($A$4,0,0,Counta($A$4:$A$65536),Counta($4:$4))

ความหมายคือ
1. ให้เริ่มที่ A4
2. 0 ตัวแรกหมายถึงว่าให้ห่างจาก A4 ลงไป 0 บรรทัด นั่นหมายความว่าอยู่ที่ A4 เ่ช่นเดิม
3. 0 ตัวที่สองหมายถึงว่าให้ห่างจาก A4 ไปทางขวา 0 คอลัมน์ นั่นหมายความว่าให้อยู่ที่ A4 เหมือนเดิม
4. Counta($A$4:$A$65536) หมายถึงให้ความสูงของข้อมูลมีจำนวนเท่ากับค่าที่นับได้ในช่วง $A$4:$A$65536
5. Counta($4:$4) หมายถึงความกว้างของข้อมูลมีจำนวนเท่ากับค่าที่นับได้ในช่วง $4:$4 หมายถึงค่าที่นับได้ในบรรทัดที่ 4 ทั้งบรรทัด

ส่วน -1 หรือค่าอื่นใดที่ใส่เข้าไปบวกหรือลบในช่วงใด ๆ เพียงเพื่อปรับค่านั้น ๆ ให้ตรงกับความต้องการของผู้เขียนสูตรครับ

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

[godman]

file ครับ

[snasui]

ผมไม่เจอ Range Name ที่ชื่อว่า ww ครับ

[godman]

=OFFSET('AD FORE'!$A$4,0,0,COUNTA('AD FORE'!$A$4:$A$65536),COUNTA(Report!$4:$4))
อยู่ใน Name range ที่ผมใส่ว่า ww หนะครับ

[godman]

เผื่ออาจารย์จะไม่เจอครับ

[snasui]

เยี่ยมครับ ถูกต้อง 99 เปอร์เซ็นต์ ผิดนิดเดียวตามที่ทำสีไว้ด้านล่างครับ

=OFFSET('AD FORE'!$A$4,0,0,COUNTA('AD FORE'!$A$4:$A$65536),COUNTA('AD FORE'!$4:$4))

[godman]

ทำไม Pivot table มันถึงไม่ยอมให้เรา sort เองหละครับ เมื่อเราเอาข้อมูลจาก sheet data มันจะจัดเรียงอีกแบบ มิวธีใหน
1.ให้มัน เรียงลำดับ ตามต้นฉบับ
2.เวลาจะหาคน เช่นมี En อยู่เป็น พัน อยากให้เราเพียง เพียงพิมพ์ en ลงไปในช่อง filter แต่มันกลับไม่มีให้เราเลือก มันลำบากมากในการไล่หา en ที่ต้องการจากคนเป็ฯพันครับ
en หมายถึง รหัสพนักงานครับ

[snasui]

ลองส่งตัวอย่างไฟล์ ระบุปัญหา ระบุเป้าหมายที่ต้องการ ใส่สีตีเส้นมาได้เต็มที่ครับ