การออกแบบรายงานประวัติการอบรมของเจ้าหน้าที่

[andyliu]

รบกวนผู้รู้ช่วยด้วยนะครับ เพราะไม่รู้จะออกแบบรายงานอย่างไรดี แต่ต้องการให้ Excel ดึงประวัติการฝึกอบรมของเจ้าหน้าที่แต่ละคนมาแสดงดังไฟล์ที่แนบมาครับ
ผมเองไม่ค่อยมีพื้นฐานการใช้ฟังก์ชั่น ใน Excel เลยครับ (ใช้เป็นแต่ Sumและ ฟังก์ชั่นง่ายๆ) รบกวนกูรูทั้งหลายให้คำแนะนำด้วยนะครับ ปวดหัวกับเรื่องนี้มาหลายเดือนแล้วครับ
ขอบคุณมากครับ

[joo]

ลองแบบนี้ดูครับที่ซีท Report เซลล์ B4 คีย์ =INDEX(DATA!B$2:B$40,MATCH($A$4,DATA!$A$2:$A$40,0)) Enter > Copy ไปทางขวา
ที่ E4 คีย์เพื่อหารายการอบรมทั้งหมด =COUNTIF(DATA!$A$2:$A$39,peport!A4)
ที่ A14 คีย์ =IF(ROWS(A$14:A14)>$E$4,"",INDEX(DATA!K$2:K$40,SMALL(IF($A$4=DATA!$A$2:$A$40,ROW(DATA!K$2:K$40)-ROW(DATA!K$2)+1),ROWS(E$19:E19)))) กดแป้น Ctrl+Shift+Enter พร้อมกัน แล้วคัดลอกลงด้านล่าง
ที่ B14 คีย์ =IF(ROWS(B$14:B14)>$E$4,"",INDEX(DATA!M$2:M$40,SMALL(IF($A$4=DATA!$A$2:$A$40,ROW(DATA!M$2:M$40)-ROW(DATA!M$2)+1),ROWS(B$14:B14))))
กดแป้น Ctrl+Shift+Enter พร้อมกัน แล้วคัดลอกไปทางขวาถึงคอลัมน์ E แล้วลงด้านล่าง
ที่F14 คีย์ =IF(ROWS(F$14:F14)>$E$4,"",INDEX(DATA!R$2:R$40,SMALL(IF($A$4=DATA!$A$2:$A$40,ROW(DATA!R$2:R$40)-ROW(DATA!Q$2)+1),ROWS(F$14:F14))))
กดแป้น Ctrl+Shift+Enter พร้อมกัน แล้วคัดลอกลงด้านล่าง
ที่เหลือก็ประยุกต์ต่อ แล้วจัด format เซลล์ตามต้องการ ถ้าซีท Report เรียงข้อมูลที่ต้องการมาแสดงก็จะทำให้เขียนสูตรเพียงครั้งเดียวแล้วแล้วคัดลอกไปวางได้ง่าย ลองดูตามไฟล์แนบในแบบที่ต้องการครับ

[NEO889]

เพิ่มอีกอันละกัน.....ลองดูไฟล์แนบ..ศึกษาดูครับ

สูตรในตารางด้านล่างหากแสดงผลผิดเพี้ยนให้ คลิกที่เซลล์แสดงผลผิดเพี้ยนแล้วกด Ctrl+Shift+Enter พร้ิอมกันครับเพื่อสร้างเป็นสูตร array

[tupthai]

ชื่อหัวคอลัมน์หรือหัวรายงานต้องตรงกันระหว่างรายงานกับ ชีทDATA เนื่องจากสูตรใช้เป็นเงื่อนไขด้วย และให้เพิ่มคอลัมน์ Index No. รันเลขที่แต่1เป็นต้นไป

1) B14 พิมพ์สูตร
=INDEX(DATA!$A$1:$R$39,SMALL(IF(DATA!$A$2:$A$39=$B$4,ROW(DATA!$A$2:$A$39)),$A14),MATCH(B$13,DATA!$A$1:$R$1,0))
กด Ctrl+Shift+Enter พร้อมกัน แล้ว copy ไปทางขวาและลงด้านล่าง

2) C2 พิมพ์สูตร
=VLOOKUP($B$4,DATA!$A$2:$R$39,MATCH(C$3,DATA!$A$1:$R$1,0),0)
กด Enter > copy ไปทางขวา

3) B7 พิมพ์สูตร
=VLOOKUP($B$4,DATA!$A$2:$R$39,MATCH(B$6,DATA!$A$1:$R$1,0),0)
กด Enter > copy ไปทางขวา

[andyliu]

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

[andyliu]

ไม่ทราบว่าถ้านำเสนอผลงานของพี่ๆ ที่แนะนำมาอีกครั้ง หัวหน้าผมจะมีอะไรมาให้เล่นอีกก็ไม่รู้นะครับ หวังว่าคงไม่เบื่อนะครับ (คิดซะว่าโดนลองภูมิก็แล้วกัน)

[joo]

ลองแบบนี้ดูครับว่าตรงกับที่ต้องการหรือไม่ในซีท Report2 ที่ D1 คีย์ =COUNTIF(DATA!$K$2:$K$39,$C$1)
ที่ C2 คีย์ =INDEX(DATA!$M$2:$M$39,MATCH($C$1,DATA!$K$2:$K$39,0))
ที่ C3 คีย์ =INDEX(DATA!$N$2:$N$39,MATCH($C$1,DATA!$K$2:$K$39,0))
ที่ C4 คีย์ =INDEX(DATA!$O$2:$O$39,MATCH($C$1,DATA!$K$2:$K$39,0))
ที่ C5 คีย์ =INDEX(DATA!$P$2:$P$39,MATCH($C$1,DATA!$K$2:$K$39,0))

ที่ C6 คีย์ =SUM(D10:D14)
ที่ A10 คีย์ =IF(ROWS($A$10:A10)>$D$1,"",ROW(A10)-9)
ที่ B10 คีย์ =IF(ROWS(B$10:B10)>$D$1,"",INDEX(DATA!C$2:C$40,SMALL(IF($C$1=DATA!$K$2:$K$40,ROW(DATA!K$2:K$40)-ROW(DATA!K$2)+1),ROWS(B10:B10)))) กดแป้น Ctrl+Shift+Enter พร้อมกัน จากนั้นก็ Copy ไปทางขวาลงมาด้านล่าง
ที่ D10 คีย์ =IF(ROWS(D$10:D10)>$D$1,"",INDEX(DATA!R$2:R$40,SMALL(IF($C$1=DATA!$K$2:$K$40,ROW(DATA!M$2:M$40)-ROW(DATA!M$2)+1),ROWS(D10:D10)))) พร้อมกัน จากนั้นก็ Copy ลงมาด้านล่าง

[joo]

ขออภัยพอดีใส่สูตรผิดไปหน่อยครับลองดูตามไฟล์แนบตัวใหม่ครับ

[nattasiray]

อันที่จริงงานของคุณเป็นฐานข้้อมูล ที่ควรสร้างด้วยไมโครซอฟต์แอคเซสครับ เพราะสามารถคัดกรองข้อมูลและพิมพ์รายงานได้ตรงตามความต้องการ

ข้อมูลที่คุณมีอยู่ต้องไปปรับแก้ให้ถูกต้องตามหลักการฐานออกแบบข้อมูลก่อน แล้วจึงโอนเข้าไมโครซอฟต์แอคเซสได้

ต้องสร้าง 6 ตารางได้แก่

• ตารางเก็บรายชื่อพนักงาน

• ตารางกลุมพนักงาน

• ตารางตำแหน่ง

• ตารางเก็บรายชื่อหลักสูตร

• ตารางประเภทการอบรม

• ตารางกำหนดการอบรม

• ตารางชื่อผู้เข้าการอบรม

แล้วสร้างความสัมพันธ์ให้เรียบร้อย

จากนั้นสร้างแบบฟอร์มสำหรับกรอกข้อมูล และรายงานที่ต้องการ

[andyliu]

กูรูทั้งหลายครับ ผมทดลองทำดูแล้วนะครับ เมื่อเพิ่มรายการประวัติเจ้าหน้าที่เข้าไป (จากตัวอย่างมีถึง row 39 ครับ หลังจาก row 40 เป็นต้นไป การประมวลผลมันผิดพลาดครับ ตรวจสอบไม่เป็นเสียด้วย อย่างไรรบกวนกูรูทั้งหลาย คุณ joo คุณ NEO899 และคุณ tupthai ช่วยอีกทีนะครับ ผมใช้ ชุด office 2007 ครับ

กรณี ที่คุณ joo คุณ NEO899 แนะนำ ถ้าเพิ่มข้อมูลไปภายหลัง row ที่ 39 แล้วการประมวลผลจะผิดไปครับ รบกวนทดสอบให้ด้วยนะครับ

ส่วนกรณีของคุณ tupthai พอทดสอบโดยเปลี่ยนรหัสพนักงาน จะขึ้น #N/A และ #NUM!

ส่วนที่คุณ nattasiray รบกวนช่่วยทำให่้ดูเป็นตัวอย่างด้วยนะครับ ผมไม่เคยใช้ ms access เลยครับ ทำไม่เป็นครับ

อย่างไรก็ต้องขอบคุณคำแนะนำทุกคำแนะนำด้วยนะครับ

[NEO889]

ผมลองเพิ่มดูข้อมูลใน Sheet DATA ถึง Row 45 ก็ไม่พบข้อผิดพลาดอะไรนะครับ...ลองแนบไฟล์ให้ดูหน่อยครัีบ

[andyliu]

รายละเอียดตามด้านล่างครับ พร้อมรูปที่แนบมาด้วยนะครับ

[NEO889]

ผมขอใช้สูตรผมที่แนะนำไปแล้วกัน...ดูไฟล์แนบ

ถ้าข้อมูลบางคอลัมน์ของประวัติมีเหมือนกันและมีการป้อนซ้ำๆ กันใน Sheet DATA น่าจะมีการทำเป็นแบบ database ไว้นะครับจะได้ง่ายขึ้น และใน Sheet Report หาก

ข้อมูลแยอะขึ้นเรื่อยๆ ควรจะต้องมีการเพิ่มจะนวนแถวในการแสดงข้อมูล

[andyliu]

คุณ joo ครับ ในส่วนของ report 2 ยังผิดในส่วนของยอดค่าใช้จ่ายแต่ละท่านครับ ถ้าจำไม่ผิด การสัมมนาปี 2553 ค่าใช้จ่ายเป็นคนละ 2,942 ครับ

[tupthai]

1)คอลัมน์ B4 รหัสควรเป็๋น ชนิดข้อมูลเดียวกัน Text หรือ Number
2) เนื่องจากข้อมูลมีมากต้องกำหนดช่วงข้อมูลเป็นแบบ Dynamic ถ้ามีการเพิ่มของข้อมูลสูตรจะปรับเอง
ให้สร้าง range name
data=OFFSET(DATA!$A$2,0,0,COUNTA(DATA!$A:$A)-1,MATCH(CHAR(255),DATA!$1:$1))
Col_header=OFFSET(DATA!$A$1,0,0,1,MATCH(CHAR(255),DATA!$1:$1))

3) ปรับสูตรใหม่
C4 พิมพ์สูตร =VLOOKUP($B$4,data,MATCH(C$3,Col_header,0),0)
Enter>copy ไปทางขวา

B7 พิมพ์สูตร =VLOOKUP($B$4,data,MATCH(B$6,Col_header,0),0)
Enter>copy ไปทางขวา

B14 พิมพ์สูตร =IFERROR(INDEX(data,SMALL(IF(data=$B$4,ROW(data)),$A14)-1,MATCH(B$13,Col_header,0)),"")
กด Ctrl+Shift+Enter copy ไปทางขวาและลงด้านล่าง

[joo]

คุณ joo ครับ ในส่วนของ report 2 ยังผิดในส่วนของยอดค่าใช้จ่ายแต่ละท่านครับ ถ้าจำไม่ผิด การสัมมนาปี 2553 ค่าใช้จ่ายเป็นคนละ 2,942 ครับ

ต้องขออภัยอีครั้งครับลองดูตามไฟล์แนบตัวนี้อีกครั้งครับ

[andyliu]

ขอบคุณคุณ joo และคุณ tupthai ครับ ต้องขอบคุณมากๆ เลยครับ
อย่างไรเสียรบกวนคุณ tupthai ช่วยเหลือในส่วนของ Report ที่ 2 ตามไฟล์ที่แนบไว้แล้วด้วยนะครับ
จะได้นำมาเปรียบเทียบกับของคุณ joo ดู แต่ทั้งนี้ทั้งนั้น ไม่ได้หมายความว่าของคุณ joo ไม่ดีนะครับ
ต้องบอกว่า แต่ละคนเขียนสูตรไม่เหมือนกันทุกคน แต่คำตอบที่ได้เหมือนกันครับ ทุกคนที่เขียนสูตรก็มี
หลักในการคิดของแต่ละคนครับ ต้องขอบคุณอีกครั้งครับ

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

สรุปว่า ชอบหมดทุกอย่างครับที่เสนอมา ซูฮกให้เป็นอาจารย์ทั้ง 2 ท่าน เลยครับ ส่วนคนอื่นๆ
ที่ให้คำแนะนำก็นับเป็นอาจารย์เหมือนกันนะครับ

[andyliu]

รบกวนใหม่ครับ
report 2 ต้องการออกมาหน้าตามตามไฟล์แนบครับ (หัวหน้าดีไซน์ให้ใหม่ครับ)

ต้องขอโทษด้วยนะครับ ที่ต้องรบกวนกูรูอีกครั้ง

[snasui]

ลองตามนี้ครับ

1. เซลล์ A5 คีย์
   =IF(ROWS(A$5:A5)>COUNTIF(DATA!$K$2:$K$39,$C$1),"",ROWS(A$5:A5))
   Enter > Copy ลงด้านล่าง
2. เซลล์ B5 คีย์
   =IF(N($A5),INDEX(INDEX(DATA!$A$2:$R$39,0,MATCH(B$4,DATA!$A$1:$R$1,0)),SMALL(IF(DATA!$K$2:$K$39=$C$1,ROW(DATA!$A$2:$A$39)-ROW(DATA!$A$2)+1),$A5)),"")
   Ctrl+Shift+Enter > Copy ไปทางขวาและลงด้านล่าง
3. เปลี่ยน E4:F4 ให้เหมือนกับชีท DATA เพื่อให้มันใจว่าเหมือนกัน ให้ใช้การ Copy มาวาง ไม่เช่นนั้นจะติด #N/A

[andyliu]

ขอบคุณครับ แต่มันเกิด error ตามรูปครับ (ผมใช้ไฟล์ report2 ที่แนบมานะครับ) บางช่องก็ได้ แต่บางช่องยังสรุปผิดอยู่ครับ รบกวนเช็คให้ผมอีกทีนะครับ

ผมมีพื้นฐานการใช้สูตร excel น้อยมากครับ ก็หวังว่าจะได้ทางพี่ๆ กูรูทั้งหลายช่วยแนะนำครับ