ดึงข้อมูลจากรายการบันทึก Training Record

[akekarakj]

จากไฟล์ที่แนบมา
ไม่ค่อยเข้าใจการเอาจากฐานข้อมูลมาแสดง อยากทราบว่าการดึงข้อมูลจากรายการบันทึกมาแสดงในการอบรมแต่ละซีทเพื่อที่จะมาแสดงวันที่การอบรมในช่วงปี จะเขียนสูตรว่าอย่างไรครับ
1.รายการบันทึกแต่ละการอบรมสามารถเอามารวมกันเวลาเขียนสูตรเพื่อที่จะแยกออกมาต้องเขียนว่าอย่างไร
2.Sheet อบรม ตัวอย่าง GHA ในการเขียนสูตรแยกว่า ID01 นาย A อบรม GHA ใน ช่วงปี 2012 ให้เอาวันที่มาใส่ ตามที่ผมกรอกไว้

[snasui]

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

ที่ชีต GHA เซลล์ G4 คีย์สูตร

=IFERROR(INDEX(รายการบันทึก[Date],MATCH(1,IF(รายการบันทึก[Course]=$D4,IF(รายการบันทึก[NAME]=$C4,IF(YEAR(G$3)=YEAR(รายการบันทึก[Date]),1))),0)),"")

Ctrl+Shift+Enter> Copy ไปทางขวาและลงด้านล่าง > คัดลอกสูตรไปใช้กับชีตอื่น ๆ ตามต้องการ กรุณาอ่านวิธีกดแป้นใน Note ด้านล่างอย่างละเอียด ไม่เช่นนั้นจะไม่ได้คำตอบตรับ

!

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 ทุกครั้ง

[akekarakj]

อาจารย์ครับพอจะอธิบายความหมายเพิ่มเติ่มได้ไหมครับ เพื่อจะจะนำดัดแปลงหรือพอจะมีอันไหนเพื่อผมจะได้ไปศึกษาต่อได้ ขอบคุณมากครับ

[snasui]

ความหมายคือให้หาข้อมูลในช่วงข้อมูลที่กำหนด

Index จะกำหนดว่าให้นำข้อมูลจากที่ใดมาแสดง
Match จะค้นหาว่าจากข้อมูลใน Index จะนำลำดับใดมาแสดง

แต่เนื่องจากมีหลายเงื่อนไข Match จึงมีความซับซ้อน จากสูตร

MATCH(1,IF(รายการบันทึก[Course]=$D4,IF(รายการบันทึก[NAME]=$C4,IF(YEAR(G$3)=YEAR(รายการบันทึก[Date]),1))),0)

เป็นการค้นหาเลข 1 จาก IF(รายการบันทึก[Course]=$D4,IF(รายการบันทึก[NAME]=$C4,IF(YEAR(G$3)=YEAR(รายการบันทึก[Date]),1)))

หมายถึงว่าหากเข้าทั้ง 3 เงื่อนไขพร้อมกันให้ผลลัพธ็เป็นเลข 1 คือ รายการบันทึก[Course]=$D4, รายการบันทึก[NAME]=$C4 และ YEAR(G$3)=YEAR(รายการบันทึก[Date]) ผลลัพธ์จะได้เป็นชุดข้อมูล ยกตัวอย่างเช่น {False, 1, False, False, False,...}

จากตัวอย่างข้างต้นสังเกตว่า 1 อยู่ในลำดับที่ 2 ของชุดข้อมูล ภาพรวมของสูตรจะกลายเป็น
=IFERROR(INDEX(รายการบันทึก[Date],2),"")

หมายถึง จากช่วง รายการบันทึก[Date] ให้นำลำดับที่ 2 มาแสดง

แต่ถ้าหาก Match แล้วไม่พบผลลัพธ์จะเป็น #N/A ซึ่งจะส่งผลให้ Index ได้ผลลัพธ์เป็น #N/A เช่นกัน และกรณีนี้จะถูก Iferror ที่ครอบอยู่นอกสุดเปลี่ยนค่าผิดพลาดให้เป็นค่าว่าง แต่หากไม่เป็นค่าผิดพลาดก็ให้แสดงผลลัพธ์ตามปกติ

ฟังก์ชั่นนี้เป็นฟังก์ชั่น Array จะมีความซับซ้อนสูงและเข้าใจยาก จะต้องค่อย ๆ ทำความเข้าใจครับ

[akekarakj]

ขอบคุณมากเลยครับ ถ้าผมจะเพิ่มสูตรค่าที่มากสุดของแต่ล่ะปีเราจะเพิ่มในช่วงตรงไหนครับ
เนื่องจากในการอบรมมีบ้างครั้งที่ปี1 อบรม2ครั้ง
ครั้งที่1 ID01 นาย A อบรม GHA 2 FEB 2003
ครั้งที่2 ID01 นาย A อบรม GHA 3 DEC 2003
ในSheet GHA ปี 2003 Cell G4 ให้แสดงครั้งล่าสุดของปี คือ แสดง 3 DEC 2003

[snasui]

ปรับสูตรเป็นด้านล่างครับ

=IFERROR(INDEX(รายการบันทึก[Date],MATCH(1,IF(รายการบันทึก[Course]=$D4,IF(รายการบันทึก[NAME]=$C4,IF(YEAR(G$3)=YEAR(รายการบันทึก[Date]),1))))),"")

[akekarakj]

ขอบคุณอาจารย์มากเลยครับ ผมเอาไปปรับได้หลายแบบได้เลยครับ

[akekarakj]

อาจาร์ยครับพอลองปรับสูตร เพื่อที่จะใช้ Small,Large กับไม่แสดงผลข้อมูลครับหรือว่าผมเขียนผิด
ในช่อง D4
=IFERROR(INDEX(รายการบันทึก[Date],MATCH(1,IF(รายการบันทึก[Course]=$D2,IF(รายการบันทึก[NAME]=$C4,SMALL(รายการบันทึก[[ID]:[Date]],1))),0)),"")


ในช่อง E4
=IFERROR(INDEX(รายการบันทึก[Date],MATCH(1,IF(รายการบันทึก[Course]=$D2,IF(รายการบันทึก[NAME]=$C4,Large(รายการบันทึก[[ID]:[Date]],2))),0)),"")

ในช่อง F4
=IFERROR(INDEX(รายการบันทึก[Date],MATCH(1,IF(รายการบันทึก[Course]=$D2,IF(รายการบันทึก[NAME]=$C4,Large(รายการบันทึก[[ID]:[Date]],1))),0)),"")

[snasui]

ควรแจ้งมาว่าต้องการค่าใด ด้วยเงื่อนไขใด กรอกตัวอย่างคำตอบด้วยมือมาด้วยจะได้สะดวกในการทำความเข้าใจครับ

[akekarakj]

ขอโทษด้วยครับอาจาร์ย ที่ไม่ได้อธิบายค่าที่ต้องการ
ค่าที่ต้องการคือ พนักงาน ID 01 นาย A อบรมCourse DGA ครั้งแรกโดยเอาข้อมูลจากรายการที่บันทึก เช่น ID 01 นาย A อบรมวันที่ 1 JAN 16,1 DEC 16, 1 NOV 17 แต่เนื่องด้วยบ้างครั้ง1ปีอาจอบรมCourse เดียวกันถึงสองครั้ง จึงลองแทรกสูตร Small กับ Large เพื่อจะได้เลือกลำดับวันที่ก่อนและหลัง หรือรองสุดท้าย
คอลัมน์ D4 ต้องการแสดงวันที่ครั้งแรกที่นาย Aอบรบ 1 JAN 16
คอลัมน์ E4 ต้องการให้แสดงวันที่ก่อนครั้งสุดท้ายที่นาย A อบรม 1 DEC 16 ครับ แสดงค่าว่างไว้
คอลัมน์ F4 ต้องการให้แสดงวันที่สุดท้าย 1 NOV 17 ครับ
- ท่าไม่มีข้อมูลให้แสดงค่าว่างไว้ครับ
เพื่อที่จะไปทำ ใบป่ะหน้า Sheet ประวัติข้อมูลพนักงาน

[snasui]

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

1. เซลล์ D4 คีย์
   =IFERROR(INDEX(รายการบันทึก[Date],MATCH(1,IF(รายการบันทึก[ID]=$B4,IF(รายการบันทึก[NAME]=$C4,IF(รายการบันทึก[Course]=LOOKUP(CHAR(255),$D$2:D$2),1))),0)),"")
   Ctrl+Shift+Enter > Copy ลงด้านล่าง > Copy ไปยังคอลัมน์ที่เกี่ยวข้อง
2. เซลล์ E4 คีย์
   =IFERROR(LARGE(IF(รายการบันทึก[ID]=$B4,IF(รายการบันทึก[NAME]=$C4,IF(รายการบันทึก[Course]=LOOKUP(CHAR(255),$D$2:E$2),รายการบันทึก[Date]))),2),"")
   Ctrl+Shift+Enter > Copy ลงด้านล่าง > Copy ไปยังคอลัมน์ที่เกี่ยวข้อง
3. เซลล์ F4 คีย์
   =IFERROR(LARGE(IF(รายการบันทึก[ID]=$B4,IF(รายการบันทึก[NAME]=$C4,IF(รายการบันทึก[Course]=LOOKUP(CHAR(255),$D$2:F$2),รายการบันทึก[Date]))),1),"")
   Ctrl+Shift+Enter > Copy ลงด้านล่าง > Copy ไปยังคอลัมน์ที่เกี่ยวข้อง

[akekarakj]

ขอบคุณอาจาร์ยมากเลยครับ จากสูตรที่ให้มาสามารถใช้งานได้ตามตรงตามที่ต้องการให้แสดงเลยครับ
คำถามครับ
1.เชลล์ D4 ใช้INDEX และก็ MATCH แต่ เซลล์ E4,F4 ตัดออก โดยการใช้ Large เพื่อหาลำดับใช้ไหมครับ
2.IF(รายการบันทึก[Course]=LOOKUP(CHAR(255),$D$2:E$2) ผมไม่ค่อยเข้าใจครับ LookupกับChar225

[snasui]

Large เป็นการค่าค่าที่มากที่สุดในลำดับที่ต้องการ เช่น มากที่สุดเป็นลำดับที่ 2 เป็นลำดับที่ 3 ลำดับที่ 1000 เป็นต้น จึงไม่จำเป็นต้องพึ่งพา Index และ Match ที่มีความมุ่งหมายในการใช้งานต่างกันครับ

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

[akekarakj]

ขอบคุณมากเลยครับ

[akekarakj]

ขออนุญาติถามต่อน่ะครับ
เนื่องจากข้อมูลที่ผมได้มามีสิ่งผิดปกติคือ วันที่ที่ไม่ใช้วันที่ในวันอบรบ จึงมีวันที่แบบText (3-4 Jan 17) และวันที่ปกติ 3 Jan 17 ซึ่งวันที่ปกติเราสามารถจัดลำดับก่อนหลังได้ จะพอมีวิธีการโดยสิ่งที่ผมทำคือในตารางผมจะให้หาจากวันที่ปกติก่อน เพื่อจัดลำดับแล้วถ้าจะเขียนสูตรเพิ่มโดย เมื่อเจอวันที่ปกติ แล้วไปเอาคอลัมน์ DateText มาแสดง

ในช่องD4 ID01 นาย A Course DGA ครั้งแรกในชีทReport ได้วันที่ 5 AUG 03 และไปเอาข้อความในรายการบันทึก DateText จะได้5-6 Aug 03 มาแสดง
สูตรเก่าD4 =IFERROR(INDEX(รายการบันทึก[Date],MATCH(1,IF(รายการบันทึก[ID]=$B4,IF(รายการบันทึก[NAME]=$C4,IF(รายการบันทึก[Course]=LOOKUP(CHAR(255),$D$2:D$2),1))),0)),"")

ในช่องF4 ID01 นาย A Course DGA ท้าย Dateได้วันที่ 4 AUG 03 และไปเอาข้อความในรายการบันทึก DateText จะได้ 4 Aug 07 มาแสดง
สูตรเก่า F4=IFERROR(LARGE(IF(รายการบันทึก[ID]=$B4,IF(รายการบันทึก[NAME]=$C4,IF(รายการบันทึก[Course]=LOOKUP(CHAR(255),$D$2:F$2),รายการบันทึก[Date]))),1),"")

[snasui]

เปลี่ยนสูตรในส่วนของ Index เสียใหม่ในส่วนอื่น ๆ ยังคงเดิมครับ

จากเดิม

INDEX(รายการบันทึก[Date]

เปลี่ยนเป็นด้านล่างครับ

INDEX(รายการบันทึก[DateText]

[akekarakj]

ขอบคุณมากครับ

[akekarakj]

อาจาร์ยครับจากคราวได้ทำการสอบถามปรากฎว่า เมื่อทำการเปลี่ยนตามที่อาจาร์ยบอกข้อมูลไม่ตรงตามที่ต้องการเลยครับ หรือว่ผมเข้าใจผิด ต้องรบกวนอาจาร์ยอีกครั้งน่ะครับติดเรื่องนี้เรื่องเดียวเลยครับ (ขอแก้ไขข้อมูลบ้างส่วนที่ถามไปตอนแรกน่ะครับ)
ส่วนของช่อง D4 กลับไม่ค้นหาจากครั้งแรกครับเจออันไหนครั้งก็แสดงเลย
ในช่องD4 ID01 นาย A Course DGA ครั้งแรกในรายการบันทึก ได้วันที่ 3 Jan 02 และไปเอาข้อความในรายการบันทึก DateText จะได้ 2-3 Jan 02 มาแสดง แต่กลับเจอ ได้ 5-6 Aug 03 แทนครับ
สูตรเก่าD4 =IFERROR(INDEX(รายการบันทึก[Date],MATCH(1,IF(รายการบันทึก[ID]=$B4,IF(รายการบันทึก[NAME]=$C4,IF(รายการบันทึก[Course]=LOOKUP(CHAR(255),$D$2:D$2),1))),0)),"")
แก้ตามที่อาจาร์ยบอกเป็น
D4 =IFERROR(INDEX(รายการบันทึก[DateText],MATCH(1,IF(รายการบันทึก[ID]=$B4,IF(รายการบันทึก[NAME]=$C4,IF(รายการบันทึก[Course]=LOOKUP(CHAR(255),$D$2:D$2),1))),0)),"")

ส่วนของช่องF4 กลับไม่แสดงครั้งสุดท้ายของวันที่กลับไปแสดงวันที่ครั้งแรกมาแสดงครับ
ผลที่ต้องการคือช่อง F4 ID01 นาย A Course DGA มี่การอบรบหลายครั้ง ต้องการให้ค้นหาจากวันที่ครั้งสุดท้าย ซึ่งวันสุดท้ายคือ 4 Aug 10 และไปเอาข้อความในรายการบันทึก DateText จะได้ 4-5 Aug 10 มาแสดง ผลปรากฎว่าไม่แสดงผล หรือว่าผมเขียนผิดครับ เพราะเมื่อเปลี่ยน รายการบันทึก[Date] เป็น Date[Text] กลับไม่เรียงลำดับวันที่เลยครับ

F4=IFERROR(LARGE(IF(รายการบันทึก[ID]=$B4,IF(รายการบันทึก[NAME]=$C4,IF(รายการบันทึก[Course]=LOOKUP(CHAR(255),$D$2:F$2),รายการบันทึก[DateText]))),1),"")

จากการแก้เป็น DateText กลับแสดงเป็น 5-6 AUG 03 แทน ครับ
F4=IFERROR(INDEX(รายการบันทึก[DateText],MATCH(1,IF(รายการบันทึก[ID]=$B4,IF(รายการบันทึก[NAME]=$C4,IF(รายการบันทึก[Course]=LOOKUP(CHAR(255),$D$2:D$2),1))),0)),"")

[snasui]

ส่วนของช่อง D4 กลับไม่ค้นหาจากครั้งแรกครับเจออันไหนครั้งก็แสดงเลย
ในช่องD4 ID01 นาย A Course DGA ครั้งแรกในรายการบันทึก ได้วันที่ 3 Jan 02 และไปเอาข้อความในรายการบันทึก DateText จะได้ 2-3 Jan 02 มาแสดง แต่กลับเจอ ได้ 5-6 Aug 03 แทนครับ

จากไฟล์แนบ เซลล์ D4 ให้แสดงครั้งแรก โดยมีเงื่อนไข 3 เงื่อนไขคือ

1. ID: ID01
2. Name: A
3. Course: DGA

เจอที่เซลล์ G7 ค่าเป็น 5-6 Aug 03 ลองทบทวนดูอีกรอบว่าใช่หรือไม่ครับ

[akekarakj]

ส่วนของช่อง D4 กลับไม่ค้นหาจากครั้งแรกครับเจออันไหนครั้งก็แสดงเลย
ในช่องD4 ID01 นาย A Course DGA ครั้งแรกในรายการบันทึก ได้วันที่ 3 Jan 02 และไปเอาข้อความในรายการบันทึก DateText จะได้ 2-3 Jan 02 มาแสดง แต่กลับเจอ ได้ 5-6 Aug 03 แทนครับ

จากไฟล์แนบ เซลล์ D4 ให้แสดงครั้งแรก โดยมีเงื่อนไข 3 เงื่อนไขคือ

1. ID: ID01
2. Name: A
3. Course: DGA

เจอที่เซลล์ G7 ค่าเป็น 5-6 Aug 03 ลองทบทวนดูอีกรอบว่าใช่หรือไม่ครับ

จากไฟล์แนบ เซลล์ D4 ให้แสดงวันที่ครั้งแรก โดยมีเงื่อนไข 3 เงื่อนไขคือ
ID: ID01
Name: A
Course: DGA
Date: 3 Jan 02
แสดงค่า คือ 2-3 Jan 02 ครับ

จากไฟล์แนบ เซลล์ F4 ให้แสดงวันที่ครั้งสุดท้าย โดยมีเงื่อนไข 3 เงื่อนไขคือ
ID: ID01
Name: A
Course: DGA
Date: 4-5 Aug 10
แสดงค่า คือ 4-5 Aug 10 ครับ