Page 1 of 1
ต้องการนำข้อมูลในแต่ละ sheet มาแสดงในตารางรวม
Posted: Mon Oct 01, 2018 4:08 pm
by azzinoth
ตามไฟล์แนบ ผมสมมติมี 3 ชีต
ต้องการดึงข้อมูล CODE และ description จากทุกชีต มาแสดงในตารางใหม่
โดยข้อมูลอันไหนซ้ำไม่ต้องเอามา ต้องทำอย่างไรบ้างครับ
ความรู้ด้านสูตรเป็นศูนย์เลยครับ
Re: ต้องการนำข้อมูลในแต่ละ sheet มาแสดงในตารางรวม
Posted: Mon Oct 01, 2018 7:53 pm
by snasui
ตัวอย่างสูตรตามด้านล่างครับ
คำตอบที่ต้องการจะอยู่ที่คอลัมน์ E:F ครับ
- เปิด Sheet ใหม่ขึ้นมา 1 ชีต
- เซลล์ K1:M1 คีย์หัวคอลัมน์เป็น Sheet, Count, StartLine ตามลำดับ
- เซลล์ K1:K3 คีย์ชื่อชีตทั้งหมดที่ต้องการนำข้อมูลมาต่อกัน คือ 1, 2 และ 3
- ที่ L2 คีย์
=COUNTIF(INDIRECT("'"&K2&"'!A:A"),"<9.99999999999999e307")
Enter > Copy ลงด้านล่างถึง L4
- ที่ L5 คีย์
=SUM(L2:L4)
Enter > Copy ลงด้านล่าง
- ที่ M2 คีย์
=SUM(L$2:L2)-L2+1
Enter > Copy ลงด้านล่าง
- เซลล์ A1:C1, E1:F1 คีย์หัวคอลัมน์เป็น Code, Description, Sheet, Code, Description ตามลำดับ
- ที่ C2 คีย์
=IF(ROWS(C$2:C2)>$L$5,"",LOOKUP(ROWS(C$2:C2),$M$2:$M$4,$K$2:$K$4))
Enter > Copy ลงด้านล่าง
- ที่ A2 คีย์
=IF($C2="","",INDEX(INDIRECT("'"&$C2&"'!C1:C100"),SMALL(IF(ISNUMBER(INDIRECT("'"&$C2&"'!A1:A100")),ROW(INDIRECT("'"&$C2&"'!A1:A100"))),COUNTIF($C$2:$C2,$C2))))
Ctrl+Shift+Enter > Copy ลงด้านล่าง
- ที่ B2 คีย์
=IF($C2="","",INDEX(INDIRECT("'"&$C2&"'!D1:D100"),SMALL(IF(ISNUMBER(INDIRECT("'"&$C2&"'!A1:A100")),ROW(INDIRECT("'"&$C2&"'!A1:A100"))),COUNTIF($C$2:$C2,$C2))))
Ctrl+Shift+Enter > Copy ลงด้านล่าง
- ที่ D1 คีย์
=SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100,A2:A100&""))
Enter
- ที่ E2 คีย์
=IF(ROWS(E$2:E2)>$D$1,"",INDEX(A$2:A$100,SMALL(IF($A$2:$A$100<>"",ROW($A$2:$A$100)-ROW($A$2)+1),ROWS(E$2:E2))))
Ctrl+Shift+Enter > Copy ลงด้านล่าง
- ที่ F2 คีย์
=IF(ROWS(F$2:F2)>$D$1,"",INDEX(B$2:B$100,SMALL(IF($A$2:$A$100<>"",ROW($A$2:$A$100)-ROW($A$2)+1),ROWS(F$2:F2))))
Ctrl+Shift+Enter > Copy ลงด้านล่าง
!
| Note: Ctrl+Shift+Enter
เป็นการสร้างสูตร Array โดยสามารถเลือกดำเนินการตามข้อ 1 หรือ 2 ด้านล่าง
- กรณีคีย์สูตรเอง เมื่อคีย์สูตรแล้ว แทนที่จะกด Enter ให้กดแป้น
Ctrl และ Shift พร้อมกันค้างไว้แล้วกด Enter เพื่อสร้างเป็นสูตร Array
- กรณี Copy สูตรไปวางให้กดแป้น F2 เพื่อทำการ Edit Cell นั้นก่อน
จากนั้นกดแป้น Ctrl และ Shift พร้อมกันค้างไว้แล้วตามด้วย Enter เพื่อสร้างเป็นสูตร Array
- หากกดแป้นถูกต้องสูตรนั้นจะมีเครื่องหมายปีกกาครอบ เช่น {=YourFormulas(...)}
ปีกกานี้ไม่สามารถคีย์เข้าไปเอง จะต้องมาด้วยการกดแป้น Ctrl+Shift+Enter เท่านั้น
ถ้ายังไม่เห็นปีกกาครอบสูตรแสดงว่ากดแป้นให้รับสูตรไม่ถูกต้อง
- การแก้ไขเปลี่ยนแปลงสูตร Array จะต้องกดแป้นให้รับสูตรด้วย Ctrl+Shift+Enter ทุกครั้ง
|