การดึงข้อมูลแล้วมาจัดเรียงตามลำดับรหัสพร้อมแยกเพศ

[spuangchang]

สอบถามอาจารย์เกี่ยวกับการดึงข้อมูลมาแล้วสามารถที่จะเรียงลำดับตามรหัสและให้แยกตามเพศชาย หญิงได้อย่างไรครับ ในแผ่นงาน database ประกอบด้วยคอลัมน์ รหัส ชั้น ชื่อ และ เพศ โดยดึงข้อมูลมาแสดงในแผ่นงาน report โดยยึด ชั้น เป็นหลัก อยากจะสอบถามถึงข้อมูลที่ดึงมาแล้วในแผ่นงาน report จะสามารถจัดเรียงตามรหัส และให้แยกตามเพศ ชาย หญิง ได้อย่างไรครับ เพิ่มเติม ถ้าจะสร้างรายการ list ชั้นประถมศึกษาปีที่ ... ให้คลิกเลือกได้แทนการกรอกตัวเลข จะทำได้อย่างไรครับ(ทำ list ได้ แต่ไม่ทราบวิธีการที่จะให้ list เชื่อมโยงไปเรียกข้อมูลได้อย่างไร ขอบคุณครับ)

[bank9597]

ผมได้ลองปรับตารางไปบ้างนิดหน่อย ยังไงลองนำปรับไปใช้เองน่ะครับ

ที่ E5 ทำเป็น DropDown List ให้เลือก เพศ ชาย,หญิง
ที่ B3 ทำเป็น DropDown List ให้เลือก ระดับ อนุบาล,ประถมศึกษา
ที่ B4 ทำเป็น DropDown List ให้เลือก ชั้นที่ 1,2...,6
ที่ C4 ปรับสูตรเป็น =IF(B3="อนุบาล","อนุบาล "&B4,"ประถมศึกษาปีที่ "&B4)

การใช้สูตรเพื่อค้นหาตามเงื่อนไขที่กำหนด
ที่ I2 คีย์ =IF(C4="","C4","Database!B2:B112") แสดงช่วงเซลล์ที่จะนำไปใช้ในสูตรค้นหา
ที่ I3 คีย์ =IF(E5="","E5","Database!D2:D112") แสดงช่วงเซลล์ที่จะนำไปใช้ในสูตรค้นหา
ที่ J2 คีย์ =IF(COUNTA(C4,E5)=0,COUNT(Database!A:A),SUMPRODUCT(--($C$4&$E$5=INDIRECT($I$2)&INDIRECT($I$3))))
สูตรใช้เพื่อนับรายการที่ตรงกับเงื่อนไขที่เราเลือก ว่ามีจำนวนกี่รายการ

ที่ A8 ปรับสูตรจากเดิมเป็น =IF(B8="","",MAX(A$7:A7)+1) เพื่อยืดหยุดต่อการเพิ่ม-ลด บรรทัด ตัวเลขจะไม่ผิดแม้จะลบหรือเพิ่มบรรทัด
ที่ B8 คีย์ =IF(ROWS(B$8:B8)>$J$2,"",INDEX(Database!A$2:A$112,SMALL(IF($C$4&$E$5=INDIRECT($I$2)&INDIRECT($I$3),ROW(Database!A$2:A$112)-ROW(Database!A$2)+1),ROWS(B$8:B8))))
กด Ctrl+Shift+Enter แล้วคัดลอกไปทางขวามือถึงคอลัมน์ E แล้วคัดลอกลงพร้อมกัน

ทดสอบเลือก ระดับชั้น.... ชั้นที่.... เพศ.....

ปล. กรณีต้องการดึงแค่ข้อมูลนักเรียนทั้งระดับชั้นโดยไม่ต้องการแยกเพศ ชาย-หญิง ก็ให้ลบค่าในเซลล์ E5 ออกครับ

Credit By "คนควน"

[spuangchang]

ขอบคุณมากครับ แต่อยากจะทราบว่าถ้าจะให้ข้อมูลนักเรียนแต่ละชั้นปรากฏขึ้นทั้งหมด ไม่แยกเฉพาะชาย หรือ หญิง แต่ให้ run เพศชายไว้ข้างบนแล้วตามด้วย เพศหญิง เนื่องจากsheet เดิม มีทั้งชาย และ หญิง อยู่ปนกัน ทำให้ไม่สะดวกต่อการนำไปใช้ เช่นลำดับที่ 1-10 เป็นเพศชาย และให้ลำดับที่เหลือเป็นเพศหญิงทั้งหมด ไม่ทราบว่าจะทำได้ไหมครับ

[bank9597]

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

หากเป็นเช่นนี้ ผมต้องรบกวนให้ท่านอื่นเข้ามาช่วยดูให้ครับ น่าจะมีวิธีที่ง่ายกว่าเดิม

[spuangchang]

ขอบคุณที่ช่วยชี้แนะครับ และจะนำสูตรที่ท่านให้ไปปรับใช้ต่อไปครับ

[joo]

ผมทำตัวอย่าไว้ที่ซีท Report1 ลองทดสอบดูครับว่าตรงกับที่ต้องการไหมครับ
ที่ A8 คีย์ =IF(ROWS(A$8:A8)>$D$4,"",ROWS(A$8:A8)) Enter แล้ว Copy ลงมาด้านล่าง
ที่ B8 คีย์ =IF(ROWS($A$8:A8)>$D$4,"",INDEX(Database!A$2:A$2000,SMALL(IF(Database!$B$2:$B$2000=$C$4,ROW(Database!$A$2:$A$2000)-ROW(Database!$B$2)+1),ROWS($A$8:A8))))
กด Ctrl+Shift+Enter แล้วคัดลอกไปทางขวามือถึงคอลัมน์ E แล้วคัดลอกลงพร้อมกัน

[spuangchang]

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

[joo]

ลองดูวิธีนี้ดูไหมครับที่ซีท Database คลิกที่เซลล์ D2 แล้วก็ทำการจัดเรียงข้อมูลจากน้อยไปหามาก กลับไปที่ซีท Report1 ที่ทำไว้ดูว่าตรงกับที่ต้องการหรือไม่

[spuangchang]

ขอบพระคุณมากครับ นับว่าเป็นอีกวิธีหนึ่งที่ได้ผลตามต้องการครับ แต่ว่าในฐานข้อมูลทะเบียนนักเรียนที่ link มายัง database นั้นจะเป็นการเรียงตามรหัสเลขประจำตัวก่อนหลังโดยไม่มีการแยกเพศ การทีทำเป็นไฟล์ excel เช่นนี้ก็เลียนแบบมาจากโปรแกรม smis เดิมครับ ซึ่งโปรแกรมดังกล่าวได้หยุดพัฒนาและคงจะเลิกใช้ จึงคิดว่าน่าจะนำโปรแกรม excel เป็นทางเลือกใหม่ในการจัดทำข้อมูลของสถานศึกษาต่อไป เอาเป็นว่าจะนำวิธีการของท่านไปใช้เป็นกรณี ๆ ไป กรณีที่ท่านบอกมานี้สามารถนำไปจัดชั้นเรียนได้ตรงความประสงค์เลยครับ ต้องขอขอบพระคุณท่านอีกครั้งหนึ่งด้วยใจจริงที่ท่านได้กรุณาให้ความสนใจให้ความรู้แนะนำสิ่งที่เป็นประโยชน์ที่จะนำไปพัฒนางานต่อไป ถ้ามีอะไรเพิ่มเติมกรุณาชี้แนะต่อไปด้วยนะครับ

[bank9597]

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

ก่อนอื่น ที่ A8 คีย์ =IF(B8="","",MAX($A$7:A7)+1) คัดลอกลงมา
จากนั้นเริ่มทำการ Advance Filter
ให้คัดลอกหัวข้อคอลัมน์ก่อน โดยคลุมทึบ B7:E7 แล้ว Ctrl+C
ไปที่ชีท Database คลิ๊กเซลล์ A1 แล้วกด Ctrl+V เราจะได้หัวเรื่องที่เหมือนกัน

กด Alt+F11 เพื่อเปิดหน้าต่าง VBE เมื่อเปิดแล้ว คลิ๊ก Insert > Module
จะได้ Module1 จากนั้นก็วางโค๊ดนี้ลงไป

Code: Select all

Sub Filter()
    Sheets("Database").Range("A1:D200").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheets("Report").Range( _
        "I2:I3"), CopyToRange:=Sheets("Report").Range("B7:E36"), Unique:=False
    Range("A7:E7").AutoFilter
    ActiveWorkbook.Worksheets("Report").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Report").AutoFilter.Sort.SortFields.Add Key:=Range _
        ("E7"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Report").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A7:E7").AutoFilter
End Sub

จากนั้น มองแถบซ้ายมือ ดับเบิ๊ลคลิ๊กที่ชีทชื่อ Report แล้ววางโค๊ด

Code: Select all

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("C5")) Is Nothing Then
        Call Filter
    End If
End Sub

ทดสอบคีย์เลขในชีท Report เซลล์ C5 ครับ ก็จะได้ตามต้องการ

[spuangchang]

ขอขอบพระคุณท่านอีกครั้งหนึ่งที่ได้รับความกรุณาจากท่าน และสมาชิกท่านอื่นที่ได้กรุณาให้ความสนใจต่อผู้ที่ยังไม่รู้จะใช้สูตร หรือเขียน โคัต vba อย่างไรท่านทราบไหมครับว่าต้ังแต่สมัครเป็นสมาชิกเว็บไซต์แห่งนี้แล้ว ข้าพเจ้าได้พยายามเปิดอ่านและบันทึกผลงานของแต่ละท่านเกือบ 60 หน้าแล้วได้พยายามคัดลอก แก้ไข สูตรผลงานแต่ละท่านจนนำมาสร้างเป็นแบบเก็บข้อมูล database และให้ดึงข้อมูลมาแสดงที่ report ได้เกิน 50% ของงานทะเบียนนักเรียนแล้ว แต่ลำพังที่จะสร้างแบบฟอร์มด้วยตนเองโดยไม่มีผู้ชี้แนะ ให้คำแนะนำ ช่วยเหลือในด้านการเขียนสูตร และโค้ต vba แล้วย่อมไม่มีทางสำเร็จ โดยเฉพาะโค้ต vba สำหรับผู้เริ่มต้นด้วยแล้วไม่ต้องพูดถึงครับ ในใจแล้วคิดว่าการเรียงข้อมูลแบบนี้คงจะต้องใช้ vba เข้ามาจัดการได้ และท่านก็ได้โปรดกรุณาเพิ่มสูตรและเขียน vba จนสามารทำให้ใช้งานได้ตรงความประสงค์จริงๆ ต้องขอขอบพระคุณท่านและเพื่อนสมาชิกอีกครั้งหนึ่ง และคงจะได้รับคำแนะนำจากท่านอีกในครั้งต่อไป ขอบพระคุณครับ

[spuangchang]

ส่งใหม่ ขออภัยครับที่พิมพ์ผิดบางคำ
ขอขอบพระคุณท่านอีกครั้งหนึ่งที่ได้รับความกรุณาจากท่าน และสมาชิกท่านอื่นที่ได้กรุณาให้ความสนใจต่อผู้ที่ยังไม่รู้จะใช้สูตร หรือเขียน โคัต vba อย่างไรท่านทราบไหมครับว่าต้ังแต่สมัครเป็นสมาชิกเว็บไซต์แห่งนี้แล้ว ข้าพเจ้าได้พยายามเปิดอ่านและบันทึกผลงานของแต่ละท่านเกือบ 60 หน้าแล้วได้พยายามคัดลอก แก้ไข สูตรผลงานแต่ละท่านจนนำมาสร้างเป็นแบบเก็บข้อมูล database และให้ดึงข้อมูลมาแสดงที่ report ได้เกิน 50% ของงานทะเบียนนักเรียนแล้ว แต่ลำพังที่จะสร้างแบบฟอร์มด้วยตนเองโดยไม่มีผู้ชี้แนะ ให้คำแนะนำ ช่วยเหลือในด้านการเขียนสูตร และโค้ต vba แล้วย่อมไม่มีทางสำเร็จ โดยเฉพาะโค้ต vba สำหรับผู้เริ่มต้นด้วยแล้วไม่ต้องพูดถึงครับ ในใจแล้วคิดว่าการเรียงข้อมูลแบบนี้คงจะต้องใช้ vba เข้ามาจัดการได้ และท่านก็ได้โปรดกรุณาเพิ่มสูตรและเขียน vba จนสามารถทำให้ใช้งานได้ตรงความประสงค์จริงๆ ต้องขอขอบพระคุณท่านและเพื่อนสมาชิกอีกครั้งหนึ่ง และคงจะได้รับคำแนะนำจากท่านอีกในครั้งต่อไป ขอบพระคุณครับ

[bank9597]

ส่งใหม่ ขออภัยครับที่พิมพ์ผิดบางคำ
ขอขอบพระคุณท่านอีกครั้งหนึ่งที่ได้รับความกรุณาจากท่าน และสมาชิกท่านอื่นที่ได้กรุณาให้ความสนใจต่อผู้ที่ยังไม่รู้จะใช้สูตร หรือเขียน โคัต vba อย่างไรท่านทราบไหมครับว่าต้ังแต่สมัครเป็นสมาชิกเว็บไซต์แห่งนี้แล้ว ข้าพเจ้าได้พยายามเปิดอ่านและบันทึกผลงานของแต่ละท่านเกือบ 60 หน้าแล้วได้พยายามคัดลอก แก้ไข สูตรผลงานแต่ละท่านจนนำมาสร้างเป็นแบบเก็บข้อมูล database และให้ดึงข้อมูลมาแสดงที่ report ได้เกิน 50% ของงานทะเบียนนักเรียนแล้ว แต่ลำพังที่จะสร้างแบบฟอร์มด้วยตนเองโดยไม่มีผู้ชี้แนะ ให้คำแนะนำ ช่วยเหลือในด้านการเขียนสูตร และโค้ต vba แล้วย่อมไม่มีทางสำเร็จ โดยเฉพาะโค้ต vba สำหรับผู้เริ่มต้นด้วยแล้วไม่ต้องพูดถึงครับ ในใจแล้วคิดว่าการเรียงข้อมูลแบบนี้คงจะต้องใช้ vba เข้ามาจัดการได้ และท่านก็ได้โปรดกรุณาเพิ่มสูตรและเขียน vba จนสามารถทำให้ใช้งานได้ตรงความประสงค์จริงๆ ต้องขอขอบพระคุณท่านและเพื่อนสมาชิกอีกครั้งหนึ่ง และคงจะได้รับคำแนะนำจากท่านอีกในครั้งต่อไป ขอบพระคุณครับ

กรณีที่รู้ว่ามีคำผิด แล้วต้องการแก้ไข ให้มองมุมขวาบนของกระทู้ แล้วกดปุ่ม Edit เพื่อเข้าไปแก้คำผิดได้ครับ ไม่ต้องขึ้นโพสต์ใหม่ครับ

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

สำหรับคำตอบที่ตอบไปให้ก่อนหน้านี้ ต้องบอกว่ายังไม่สมบูรณ์ครับ ยังไม่ได้ 100% เพราะว่าผมบันทึกมาโครอย่างเร็วและปรับโค๊ดเพียงให้ใช้งานได้เท่านั้น

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

จากโค๊ดใน Module1 ให้เปลี่ยนเป็นโค๊ดตามด้านล่างครับ

Code: Select all

Sub Filter()
    On Error Resume Next
    Application.EnableEvents = False
    Sheets("Database").Range("A1:D200").AdvancedFilter Action:=xlFilterInPlace, _
        CriteriaRange:=Sheets("Report").Range("I2:I3")
    Sheets("Database").Range("A2:D200").SpecialCells(xlCellTypeVisible).Copy
    Sheets("Report").Range("B8").PasteSpecial xlPasteValues
    Sheets("Database").ShowAllData
    
    Range("A7:E7").AutoFilter
    ActiveWorkbook.Worksheets("Report").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Report").AutoFilter.Sort.SortFields.Add Key:=Range _
        ("E7"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Report").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A7:E7").AutoFilter

    Application.CutCopyMode = False
    Application.EnableEvents = True
End Sub

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

Credit By "คนควน"

[spuangchang]

รับทราบและขอขอบคุณด้วยใจจริงมาอีกครั้งหนึ่งครับ

[snasui]

การเรียงข้อมูลโดยให้ชายอยู่ติดกันเป็นชุดและหญิงอยู่ติดกันเป็นชุดไม่สลับกันไปมา สามารถเพิ่มสูตรในชีท Database มาช่วยเพื่อให้ลำดับข้อมูลก่อนทีจะดึงมาใช้ในชีท Report

ลองตามไฟล์แนบครับ โดย

1. ที่ชีท Database เซลล์ E2 คีย์
   =SUMPRODUCT(--(D2>$D$2:$D$112))+COUNTIF(D$2:D2,D2)
   Enter > Copy ลงด้านล่าง
2. ที่ชีท Report เซลล์ B8 คีย์
   =IF(A8="","",INDEX(Database!A$2:A$112,MATCH(SMALL(IF(Database!$B$2:$B$112=$C$4,Database!$E$2:$E$112),$A8),Database!$E$2:$E$112,0)))
   Ctrl+Shift+Enter > Copy ลงด้านล่าง
3. ที่ชีท Report เซลล์ C8 คีย์
   =IF($B8="","",INDEX(Database!B$2:B$112,MATCH($B8,Database!$A$2:$A$112,0)))
   Enter > Copy ไปทางขวาและลงด้านล่าง

[spuangchang]

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