ดึงข้อมูลจาก pivot เป็นแสดงข้อมูลเป็นกลุ่ม

[March201711]

ต้องการดึงข้อมูลจากตาราง pivot โดยให้จับข้อมูลเป็นกลุ่มมาแสดงคำตอบ เช่น
เช่น กลุ่ม INST (สีเหลือง) จะมีตัวย่อเป็น VINS VIFD VZZZ VIEI VIPI VIPJ VICC VECM ที่ cell H89:H97 ให้ไปหาใน Row 4 จะเจอ VECM VINS และให้จับคู่ที่ Product column E กับ ที่ cell G81 คือ 80165 จะได้ยอด 3,998,209.85.00 ในแต่ละบรรทัด
เช่น กลุ่ม PWM (สีเขียว) จะมีตัวย่อเป็น VPH1 VPH3 VPH4 VPH5 VPH6 VPH7 VPFI VPWM ที่ cell I89:I97 ให้ไปหาใน Row 4 จะเจอ VPH1 VPH3 VPH4 VPH7 และให้จับคู่ที่ Product column E กับ ที่ cell G82 คือ 80165 จะได้ยอด 741,333.34ในแต่ละบรรทัด
เช่น กลุ่ม AFFILINE (สีฟ้า) จะมีตัวย่อเป็น VPT1 VPT2 VPT3 VPT4 VPCB VPUB VPBB VPSB VPRB VPCC VPTR VPRT VIER VIPP VITR VPPR VPMS VONL VDER ที่ cell J89:J109 ให้ไปหาใน Row 4 จะเจอ VPBB BPCB VPCC VPPB VPRB VPSB VPT1 VPT2 VPT3 VPT4 VPUB VONL VDER VPRTและให้จับคู่ที่ Product column E กับ ที่ cell G82 คือ 80165 จะได้ยอด 741,333.34ในแต่ละบรรทัด

อยากให้สูตรมีความยึดหยุ่นจากที่ link ข้อมูลมาจาก sheet data ถ้าการเพิ่มบัญชี หรือ มีการเพิ่ม product หรือ เพิ่ม แผนก พอ refresh pivot ที่ตารางนี้ จะทำให้สูตรที่ดึงมาเพี้ยนไปหมดเลยค่ะ ต้องปรับสุตรยังไงคะ ขอบคุณค่ะ

[norkaz]

...

ที่ชีท Data สร้าง ตารางจับคู่ตามจริง

Q3
=IFERROR(VLOOKUP(E3,$T$3:$U$99,2,0),"")

ชีท Pivot
สร้าง Pivot ไปตามนั้น

** สนับสนุนฟอรั่ม กรุณา คลิกโฆษณาที่หน้าเพจ***

แมวใหญ่ Bigcat9
RCA พระราม9 กรุงเทพฯ

...

[March201711]

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

[norkaz]

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

ท่านเจ้าของคำถาม ช่วย แนบไฟล์ Excel ทำตัวอย่างที่เกี่ยวข้องกับ คำถามมาดูครับ

** สนับสนุนฟอรั่ม กรุณา คลิกโฆษณาที่หน้าเพจ***

แมวใหญ่ Bigcat9
RCA พระราม9 กรุงเทพฯ

...

[norkaz]

...

แบบนี้ไหม ถ้าไม่ใช่ ทำตัวอย่าง แนบไฟล์ Excel มาดูครับ

คอลัมน์ T,U สร้างตารางจับคู่

Q3
=IFERROR(VLOOKUP(E3,$T$3:$U$999,2,0),"")
Copy ลงไป

W3
=IFERROR(INDEX($F$1:$F$999,AGGREGATE(15,6,ROW($F$3:$F$999)/(MATCH($F$3:$F$999,$F$3:$F$999,0)=ROW($F$3:$F$999)-ROW($F$3)+1),ROWS($F$3:F3))),"")
Copy ลงไป

X3
=SUMIFS($N$3:$N$999,$F$3:$F$999,$W3,$Q$3:$Q$999,X$2)
Copy ไปขวา และลงล่าง


** สนับสนุนฟอรั่ม กรุณา คลิกโฆษณาที่หน้าเพจ***

แมวใหญ่ Bigcat9
RCA พระราม9 กรุงเทพฯ

...

[March201711]

แบบนี้น่ะค่ะ ตามไฟล์ที่แนบ

[norkaz]

...

หมายถึง ให้ดึงข้อมูล มาจาก ชีท Data เลย หรือว่า ยังต้องดึงผลลสรุป มาจาก Pivot Table ครับ..


** สนับสนุนฟอรั่ม กรุณา คลิกโฆษณาที่หน้าเพจ***

แมวใหญ่ Bigcat9
RCA พระราม9 กรุงเทพฯ

...

[norkaz]

...

ถ้าไม่ต้องการดึงมาจาก Pivot Table แล้ว

ชีท Data ทำแบบเดิม

ชีท Pivot

H83
=SUMIFS(Data!$N$3:$N$999,Data!$F$3:$F$999,Pivot!$G83,Data!$Q$3:$Q$999,H$82)

Copy ไปเซลล์ที่เกี่ยวข้อง

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

** สนับสนุนฟอรั่ม กรุณา คลิกโฆษณาที่หน้าเพจ***

แมวใหญ่ Bigcat9
RCA พระราม9 กรุงเทพฯ

...

[March201711]

ถ้าดึงข้อมูลจาก ตาราง pivotที่มี sheet Pivot เลย โดยไม่มี sheet data แต่มีกลุ่ม INST, PWM, AFFLINE ว่ามี code อะไรอยู่ใน กลุ่มนี้บ้าง ในตาราง H100: J120เช่น INST มี code VINS VIFD VZZZ VIEI VIPI VIPJ VICC VECM ในกลุ่ม PWM มี code VPH1 VPH3 VPH4 VPH5 VPH6 VPH7 VPFI VPWM ค่ะ ในอนาคต อาจมี code เพิ่มลงมาอีกเรื่อยๆ ถ้าจะให้จัดกลุ่มต้องปรับสูตรอย่างไรคะ ขอบคุณค่ะ

[norkaz]

...

G2
=IFERROR(IF(G4="","",INDEX($H$100:$J$100,AGGREGATE(15,6,{1,2,3}/(G4=$H$101:$J$200),1))),"")
Copy ไปขวา เผื่อๆไป ถึง AZ4

H83
=SUMPRODUCT((H$82=$G$2:$AZ$2)*($G83=$E$5:$E$70)*($G$5:$AZ$70))
Copy ไปเซลล์ที่เกี่ยวข้อง

** สนับสนุนฟอรั่ม กรุณา คลิกโฆษณาที่หน้าเพจ***

แมวใหญ่ Bigcat9
RCA พระราม9 กรุงเทพฯ

...

[March201711]

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

[norkaz]

...

D102
=LEFT(B102,AGGREGATE(15,6,SEARCH({0,1,2,3,4,5,6,7,8,9},B102),1)-1)

E102
=MID(B102,LEN(D102)+1,5)+0

F102
=SUBSTITUTE(B102,D102&E102,"")+0

** สนับสนุนฟอรั่ม กรุณา คลิกโฆษณาที่หน้าเพจ***

แมวใหญ่ Bigcat9
RCA พระราม9 กรุงเทพฯ

...

[norkaz]

...

อีกแบบ

F102
=RIGHT(B102,8)+0

E102
=LEFT(RIGHT(B102,13),5)+0

D102
=SUBSTITUTE(B102,E102&F102,"")

** สนับสนุนฟอรั่ม กรุณา คลิกโฆษณาที่หน้าเพจ***

แมวใหญ่ Bigcat9
RCA พระราม9 กรุงเทพฯ

...

[March201711]

ได้แล้วค่ะ ขอบคุณมากค่ะ

[March201711]

มีปัญหาเพิ่มนิดนีงค่ะ ถ้าต้องการให้ ดึงข้อมูลเฉพาะ account code นี้ และ Product code นี้ มาแสดงข้อมูลที่ vlookup ต้องปรับสูตรอย่างไรค่ะ
เช่น account code 72000000-99001 ควรแสดงข้อมูลเป็น Placement แต่กลับแสดง Equity
เช่น account code 72001006-80350 ควรแสดงข้อมูลเป็น DXEw แต่กลับแสดง Equity
เช่น account code 72001019-80191 ควรแสดงข้อมูลเป็น EIPIE แต่กลับแสดง Exchange diff

PD code Type
80102 Equity
v v
80401 Overseas trading
99001 Equity
99356 Equity
Row 70 72000000-99001 Placement
Row 220 72001006-80350 DXEw
Row 239 72001019-80191 EIPIE

[norkaz]

...

สูตร VLOOKUP ให้ผลลัพธ์ ถูกต้องแล้วครับ

หากต้องการให้ผลลัพธ์เปลี่ยนไป ให้ไปแก้ไขที่ คอลัมน์ U

เช่น 99001 Equity
แก้ Equity เป็น xxxx ตามที่ต้องการให้เป็น

** สนับสนุนฟอรั่ม กรุณา คลิกโฆษณาที่หน้าเพจ***

แมวใหญ่ Bigcat9
RCA พระราม9 กรุงเทพฯ

...

[March201711]

vlookup จับคู่ เฉพาะ product code ไม่ได้จับ 2 column (account code และ product code) ค่ะ ต้องปรับสูตรอย่างไร ให้จับแบบ 2 เงื่อนไข คือ 1 เงื่อนไขที่ 1 จับ product code เท่านั้น
2 เงื่อนไขที่ 2 จับ เฉพาะ account code และ product code

[norkaz]

...

ในไฟล์ สูตร VLOOKUP ที่ท่านเจ้าของคำถามเขียนไว้ ไปที่คอลัมน์ F ซึ่งเป็น Product code อย่างเดียว

ถ้าต้องการให้สะพาน ที่ คอลัมน์ T มีทั้ง Account code และสามารถอ่านค่า Product Code ด้วย มันจะขัดแย้งกัน

เพราะ VLOOKUP แถวอื่น Excel จะรู้ได้อย่างไร ว่า ต้อง อ่านค่าที่ Product code อย่างเดียว หรือ แถวนั้นต้องอ่านค่า ทั้ง Product code และ Account code พร้อมกันครับ

ถ้าจะทำแบบที่สอบถามมาได้ ต้องสร้างรหัส Product Code มาอีกคอลัมน์ ที่คอลัมน์ S โดยจับคู๋กับ Account Code

เช่น คอลัมน์ S Product Code 71000010 จับคู่กับ คอลัมน์ T Account Code 80166 ให้แสดงค่า ที่ คอลัมน์ U เป็น Overseas trading
เป็นต้น

และต้องทำแบบนี้ทุกๆคู่ จึงจะสามารถ หาผลลัพธ์ จาก การ LOOKUP ด้วย 2 เงื่อนไข ทั้ง Product code + Account code ได้ครับ

** สนับสนุนฟอรั่ม กรุณา คลิกโฆษณาที่หน้าเพจ***

แมวใหญ่ Bigcat9
RCA พระราม9 กรุงเทพฯ

...

[norkaz]

...

ลองปรับที่ Q3
=IFERROR(IF(AND(C3=72000000,F3=99001),"Placement",IF(AND(C3=72001006,F3=80350),"DXEw",IF(AND(C3=72001019,F3=80191),"EIPIE",VLOOKUP(F3,$T$3:$U$16,2,FALSE)))),"-")

วิธีนี้ไม่แนะนำ เนื่องจาก ต้องคอยมาเช็คว่า ต้องระบุเงื่อนไข คู่ใดลงไป ในสูตร ถ้า มีจำนวนคู่พิเศษมากขึ้น การเช็คและตรวจสอบจะยากขึ้นครับ

** สนับสนุนฟอรั่ม กรุณา คลิกโฆษณาที่หน้าเพจ***

แมวใหญ่ Bigcat9
RCA พระราม9 กรุงเทพฯ

...

[March201711]

ถ้าทำการแยกออกเป็น 2 ตาราง
ตารางที่ 1 มีทั้ง Account code และ PD code
ตารางที่ 2 มีเฉพาะ PD code
ถ้า column Q ใช้สูตรหาจากตารางที่ 1 ไม่ได้ ให้สูตรไปหาตารางที่ 2 แทน ถ้าหาทั้ง 2 ตารางไม่ได้ ให้ขึ้นข้อความว่า " Not match data " ค่ะ
เผื่อตาราง 1 ต้องเพิ่ม Account code และ PD code มา จีงจำเป็นต้องเสร้างตารางที่ 1 เพิ่มค่ะ