Snasui Excel Forum

สวัสดีครับ

ผมมีคำถามเกี่ยวกับการให้ excel แสดงข้อมูลแบบเรียงลำดับโดยอ้างอิงข้อมูลจาก sheet ที่ 1 ซึ่งจะเป็นการทำ report แสดงอัตราว่างของพนักงาน (-1 คือ ขาดจำนวน 1 อัตรา) โดยมีมีโจทย์ในการทำดังนี้
1. อยากให้ข้อมูลที่เป็น -1 แสดงออกมาแบบเรียบลำดับว่า -1 มีสาขาอะไรบ้างที่เป็น -1 ใน sheet ที่2

หมายเหตุ : ตัวอย่างตาม file แนบครับ

ขอบคุณสำหรับคำแนะนำด้วยครับ

ตัวอย่างสูตรครับ

1. ชีต Vacancy เซลล์ G15 คีย์สูตรเพื่อเป็นเซลล์ช่วยในการเขียนสูตรข้อต่อ ๆ ไป
   =ABS(SUM($C2:C14))-ABS(SUM(C2:C14))+1
   Enter > Copy ไปด้านขวาจนถึง V15
2. ชีตตัวอย่างที่ต้องการให้แสดง เซลล์ A2 คีย์สูตร
   =IF(ROWS(A$2:A2)>Vacancy!$V$15,"",LOOKUP(ROWS(A$2:A2),Vacancy!$C$15:$V$15,Vacancy!$C$1:$V$1))
   Enter > Copy ลงด้านล่างจนพบค่าว่าง
3. ชีตตัวอย่างที่ต้องการให้แสดง เซลล์ C2 คีย์สูตร
   =IF(A2<>"",1,"")
   Enter > Copy ลงด้านล่าง
4. ชีตตัวอย่างที่ต้องการให้แสดง เซลล์ B2 คีย์สูตร
   =IFERROR(INDEX(Vacancy!$B$2:$B$14,SMALL(IF(INDEX(Vacancy!$C$2:$V$14,0,MATCH($A2,Vacancy!$C$1:$V$1,0)),ROW(Vacancy!$B$2:$B$14)-ROW(Vacancy!$B$2)+1),COUNTIF(A$2:A2,A2))),"")
   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 ทุกครั้ง

ขอบคุณมากครับ จะลองนำไปใช้งานดูได้ผลยังไงจะเข้ามาแจ้งให้ทราบอีกครั้งครับ

ขอแสดงความนับถือ

snasui wrote: ตัวอย่างสูตรครับ

1. ชีต Vacancy เซลล์ G15 คีย์สูตรเพื่อเป็นเซลล์ช่วยในการเขียนสูตรข้อต่อ ๆ ไป
   =ABS(SUM($C2:C14))-ABS(SUM(C2:C14))+1
   Enter > Copy ไปด้านขวาจนถึง V15
2. ชีตตัวอย่างที่ต้องการให้แสดง เซลล์ A2 คีย์สูตร
   =IF(ROWS(A$2:A2)>Vacancy!$V$15,"",LOOKUP(ROWS(A$2:A2),Vacancy!$C$15:$V$15,Vacancy!$C$1:$V$1))
   Enter > Copy ลงด้านล่างจนพบค่าว่าง
3. ชีตตัวอย่างที่ต้องการให้แสดง เซลล์ C2 คีย์สูตร
   =IF(A2<>"",1,"")
   Enter > Copy ลงด้านล่าง
4. ชีตตัวอย่างที่ต้องการให้แสดง เซลล์ B2 คีย์สูตร
   =IFERROR(INDEX(Vacancy!$B$2:$B$14,SMALL(IF(INDEX(Vacancy!$C$2:$V$14,0,MATCH($A2,Vacancy!$C$1:$V$1,0)),ROW(Vacancy!$B$2:$B$14)-ROW(Vacancy!$B$2)+1),COUNTIF(A$2:A2,A2))),"")
   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 ทุกครั้ง

สวัสดีครับวันนี้ผมนำสูตรที่ได้รับการชี้แนะเอาไปลองทำใน report แรกดูแล้วผลลัพธ์ได้ตามที่ต้องการ แต่พอจะนำไปปรับใช้จริงกับ report ที่ต้องใช้งานมีปัญหาเพิ่มเติมให้ช่วยชี้แนะดังนี้ครับ
1. ใน sheet Report มันดึงเอาตัวเลขที่เป็นจำนวน -1 ในแถวรวมจาก sheet Vacancy (เช่น ตามแถวที่ 22,31,45,57,68,80) ทำให้ข้อมูลที่ถูกดึงไปใน sheet Report ข้อมูลมีการคลาดเคลื่อน ยกตัวอย่าง ตำแหน่ง SGM ที่จริงข้อมูลที่เป็น -1 จะมีแค่สาขาเดียว แต่ใน sheet Vacancy มีข้อมูลที่เป็น -1 ในช่องรวมทำให้มีการดึงข้อมูล -1 มาเป็น 3 ตำแหน่ง
2. ใน sheet Report ตรงช่อง D2 เมื่อใส่สูตรแล้วข้อมูลไม่ขึ้น (อันนี้ผมอาจใส่ผิดเงื่อนไขครับ)
3. อยากให้มีการดึงข้อมูลจาก sheet Vacancy มาใส่ใน sheet Report ในช่อง c2

ขอแสดงความนับถือ

ตัวอย่างการปรับสูตรครับ

1. ชีต Vacancy
   1. ที่ M139 คีย์
      =SUMPRODUCT(--($E$9:$E$125<>""),--(M$9:M$125=-1))
      Enter > Copy ไปด้านขวา
   2. ที่ M127 คีย์
      =SUM($M139:M139)-M139+1
      Enter Copy ไปทางขวา
2. ชีต Report
   ที่ D2 คีย์ =IFERROR(INDEX(Vacancy!$D$9:$D$125,SMALL(IF(Vacancy!$D$9:$D$125<>"",IF(INDEX(Vacancy!$M$9:$AF$125,0,MATCH($A2,Vacancy!$M$8:$AF$8,0))=-1,ROW(Vacancy!$D$9:$D$125)-ROW(Vacancy!$D$9)+1)),COUNTIF(A$2:A2,A2))),"")
   Ctrl+Shift+Enter > Copy ลงด้านล่าง

ขอบคุณสำหรับคำชี้แนะครับ ตอนนี้ปรับแก้ไขได้แล้ว

ขอแสดงความนับถือ