:!: โปรดทราบ Image
    1. กรุณาอ่านกฎการใช้บอร์ด (Forum rules) ในตำแหน่งด้านบนของแต่ละบอร์ดครับ Image
    2. การสมัครสมาชิกเพื่อโพสต์คำถาม ดาวน์โหลดไฟล์แนบไปศึกษา ทำตามขั้นตอนด้านล่างครับ
      1. สมัครสมาชิก ดูขั้นตอนตาม Link นี้ครับ => สมัครสมาชิก กรณีลืมรหัสผ่านสามารถรับรหัสใหม่ได้ที่นี่ครับ => Reset รหัสผ่านImage
      2. Login เข้าระบบโดยคลิก Login ตรงมุมขวาบนของหน้านี้ Image กรณีมีปัญหาในการเข้าใช้งาน คลิก Link นี้เพื่อแจ้งผู้ดูแลระบบครับ => ติดต่อผู้ดูแลระบบ
    3. เมื่อ Login แล้วสามารถกำหนดการตั้งค่าส่วนตัว เช่นตั้งค่าภาษาเป็นไทยหรืออังกฤษได้ที่ Link นี้ครับ => ตั้งค่าส่วนตัว Image
    4. วิธีการตั้งและตอบกระทู้ดูได้ที่ Link นี้ครับ => วิธีการตั้งและตอบกระทู้ Image
    5. การจัดรูปแบบตัวอักษรด้วย bbcode ในช่องแสดงความคิดเห็นดูได้ที่ Link นี้ครับ => จัดรูปแบบตัวอักษร และสามารถกำหนดขนาดตัวอักษรใน Browser ได้ที่นี่ครับ ==> กำหนดขนาดตัวอักษรใน Browser Image

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

ฟอรั่มถาม-ตอบปัญหาการใช้งาน MS Excel and VBA
Forum rules
  1. ไม่อนุญาตให้ใช้ภาษาแชทในการถาม-ตอบปัญหา ไม่ใช้คำว่า "คับ" หรือ "อ่ะครับ" แทนคำว่า "ครับ" ไม่ใช้คำว่า "เด๋ว" แทนคำว่า "เดี๋ยว" เป็นต้น เนื่องจากเมื่อแปลเป็นภาษาต่างประเทศแล้วจะให้ความหมายผิดไปจากที่ควรจะเป็น
  2. ห้ามถามโดยระบุชื่อผู้ตอบ ต้องตั้งชื่อกระทู้ให้สื่อถึงปัญหาที่จะถาม ไม่ตั้งชื่อว่า ช่วยด้วยครับ, มีปัญหามาปรึกษาครับ เป็นต้น
  3. อธิบายปัญหาและระบุคำตอบที่ต้องการมาในกระทู้ด้วยเสมอถึงแม้จะอธิบายไว้ในไฟล์แนบแล้วก็ตาม ทั้งนี้เพื่ออำนวยความสะดวกแก่เพื่อนสมาชิกในการค้นหาข้อมูล
  4. ควรแนบตัวอย่างไฟล์มาที่ฟอรั่มนี้เพื่อเพิ่มความสะดวกในการตอบคำถาม (ขนาดไฟล์ไม่เกิน 500Kb ขนาดภาพไม่เกิน 800*600 Pixel) ไม่แนบเป็น Link มาจากแหล่งอื่น นอกจากนี้ไม่ควรแนบไฟลที่มีข้อมูลสำคัญอันก่อให้เกิดความเสียหายกับตนเองและผู้อื่น
  5. สำหรับคำถามเกี่ยวกับ VBA ให้ลองเขียนมาเองก่อนเสมอ ถามเฉพาะที่ติดปัญหา ระบุ Module, Procedure ที่ติดปัญหาให้ชัดเจน ควรโพสต์ Code ให้แสดงเป็น Code เพื่อสะดวกในการอ่านและทดสอบ (คลิกเพื่อดูตัวอย่าง)
  6. แจ้งผลการใช้งานเมื่อได้รับคำตอบว่าตรงกับความต้องการหรือไม่
akekarakj
Member
Member
Posts: 8
Joined: Sat Aug 26, 2017 8:38 pm

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

#1

Postby akekarakj » Sun Aug 27, 2017 9:58 am

จากไฟล์ที่แนบมา
ไม่ค่อยเข้าใจการเอาจากฐานข้อมูลมาแสดง อยากทราบว่าการดึงข้อมูลจากรายการบันทึกมาแสดงในการอบรมแต่ละซีทเพื่อที่จะมาแสดงวันที่การอบรมในช่วงปี จะเขียนสูตรว่าอย่างไรครับ
1.รายการบันทึกแต่ละการอบรมสามารถเอามารวมกันเวลาเขียนสูตรเพื่อที่จะแยกออกมาต้องเขียนว่าอย่างไร
2.Sheet อบรม ตัวอย่าง GHA ในการเขียนสูตรแยกว่า ID01 นาย A อบรม GHA ใน ช่วงปี 2012 ให้เอาวันที่มาใส่ ตามที่ผมกรอกไว้
You do not have the required permissions to view the files attached to this post.

User avatar
snasui
Site Admin
Site Admin
Posts: 21793
Joined: Sun Jan 24, 2010 12:33 pm
Location: Songkhla, Thailand
Contact:

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

#2

Postby snasui » Sun Aug 27, 2017 2:22 pm

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

ที่ชีต 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 ด้านล่าง
  1. กรณีคีย์สูตรเอง เมื่อคีย์สูตรแล้ว แทนที่จะกด Enter ให้กดแป้น Ctrl และ Shift พร้อมกันค้างไว้แล้วกด Enter เพื่อสร้างเป็นสูตร Array
  2. กรณี Copy สูตรไปวางให้กดแป้น F2 เพื่อทำการ Edit Cell นั้นก่อน จากนั้นกดแป้น Ctrl และ Shift พร้อมกันค้างไว้แล้วตามด้วย Enter เพื่อสร้างเป็นสูตร Array
  3. หากกดแป้นถูกต้องสูตรนั้นจะมีเครื่องหมายปีกกาครอบ เช่น {=YourFormulas(...)} ปีกกานี้ไม่สามารถคีย์เข้าไปเอง จะต้องมาด้วยการกดแป้น Ctrl+Shift+Enter เท่านั้น ถ้ายังไม่เห็นปีกกาครอบสูตรแสดงว่ากดแป้นให้รับสูตรไม่ถูกต้อง
  4. การแก้ไขเปลี่ยนแปลงสูตร Array จะต้องกดแป้นให้รับสูตรด้วย Ctrl+Shift+Enter ทุกครั้ง

akekarakj
Member
Member
Posts: 8
Joined: Sat Aug 26, 2017 8:38 pm

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

#3

Postby akekarakj » Sun Aug 27, 2017 4:39 pm

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

User avatar
snasui
Site Admin
Site Admin
Posts: 21793
Joined: Sun Jan 24, 2010 12:33 pm
Location: Songkhla, Thailand
Contact:

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

#4

Postby snasui » Sun Aug 27, 2017 4:59 pm

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

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
Member
Member
Posts: 8
Joined: Sat Aug 26, 2017 8:38 pm

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

#5

Postby akekarakj » Mon Aug 28, 2017 9:25 pm

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

User avatar
snasui
Site Admin
Site Admin
Posts: 21793
Joined: Sun Jan 24, 2010 12:33 pm
Location: Songkhla, Thailand
Contact:

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

#6

Postby snasui » Mon Aug 28, 2017 9:39 pm

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

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

akekarakj
Member
Member
Posts: 8
Joined: Sat Aug 26, 2017 8:38 pm

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

#7

Postby akekarakj » Tue Aug 29, 2017 1:11 pm

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

akekarakj
Member
Member
Posts: 8
Joined: Sat Aug 26, 2017 8:38 pm

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

#8

Postby akekarakj » Sun Sep 10, 2017 12:57 pm

อาจาร์ยครับพอลองปรับสูตร เพื่อที่จะใช้ 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)),"")
You do not have the required permissions to view the files attached to this post.

User avatar
snasui
Site Admin
Site Admin
Posts: 21793
Joined: Sun Jan 24, 2010 12:33 pm
Location: Songkhla, Thailand
Contact:

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

#9

Postby snasui » Sun Sep 10, 2017 3:01 pm

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

akekarakj
Member
Member
Posts: 8
Joined: Sat Aug 26, 2017 8:38 pm

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

#10

Postby akekarakj » Sun Sep 10, 2017 5:32 pm

ขอโทษด้วยครับอาจาร์ย ที่ไม่ได้อธิบายค่าที่ต้องการ
ค่าที่ต้องการคือ พนักงาน 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 ประวัติข้อมูลพนักงาน
You do not have the required permissions to view the files attached to this post.

User avatar
snasui
Site Admin
Site Admin
Posts: 21793
Joined: Sun Jan 24, 2010 12:33 pm
Location: Songkhla, Thailand
Contact:

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

#11

Postby snasui » Sun Sep 10, 2017 8:15 pm

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

  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
Member
Member
Posts: 8
Joined: Sat Aug 26, 2017 8:38 pm

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

#12

Postby akekarakj » Mon Sep 11, 2017 6:56 pm

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

User avatar
snasui
Site Admin
Site Admin
Posts: 21793
Joined: Sun Jan 24, 2010 12:33 pm
Location: Songkhla, Thailand
Contact:

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

#13

Postby snasui » Mon Sep 11, 2017 7:05 pm

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

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

akekarakj
Member
Member
Posts: 8
Joined: Sat Aug 26, 2017 8:38 pm

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

#14

Postby akekarakj » Tue Sep 12, 2017 7:36 pm

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


Return to “Excel”

Who is online

Users browsing this forum: fujido, tigerwit and 37 guests