เพื่อมุ่งให้เกิดคุณภาพจากการอบรมสูงสุด Excel Expert Training ให้การอบรม Excel กลุ่มเล็กๆ ไม่เกิน 6 คนทุกคนสามารถเรียนรู้ Excel อย่างใกล้ชิด จะมาคนเดียวหรือมาเป็นกลุ่มแล้วนัดวันอบรมแบบส่วนตัวก็ได้ ผู้เข้าอบรมทุกคนสามารถติดตามเนื้อหาที่อบรมได้อย่างชัดเจนจากจอภาพด้านหน้าของตัวเอง
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: จะดึงค่าจากตำแหน่งอ้างอิง 3 มิติได้อย่างไร

  1. #1
    สมเกียรติ
    Guest

    Unhappy จะดึงค่าจากตำแหน่งอ้างอิง 3 มิติได้อย่างไร

    สมมติว่า เรามีค่าที่บันทึกไว้ในเซลล์ B2 ของทุกชีต แล้วต้องการหายอดรวมของเซลล์ B2 ของทุกชีต สามารถหาได้จากสูตร =SUM(Sheet1:Sheet3!B2) ใช่ไหมครับ

    แสดงว่าสูตรในส่วนของ Sheet1:Sheet3!B2 ดึงค่าจากเซลล์ B2 ของชีตที่อยู่ระหว่างชีต 1 - 3 มาให้หายอดรวมในสูตร Sum

    ผมจึงคิดหาทางต่อไปว่า จะดึงค่าจากสูตร =Sheet1:Sheet3!B2 แต่ละชีตมาได้อย่างไร เพราะถ้าทำได้ เราจะสามารถดึงค่าจากชีตใดก็ได้มาใช้ได้สบายเลยโดยไม่ต้องใช้ Indirect อีกต่อไป และไม่ต้องคอยพะวงถึงชื่อชีตอีกด้วย

    ผมลองใช้สูตร {=Sheet1:Sheet3!B2} กระจายค่าก็ไม่ได้ผล ออกมาเป็น #REF!

    ลองใช้สูตร =Index(Sheet1:Sheet3!B2,1,1,1) เพื่อดึงค่าแรกมาก็ไม่ได้

    ตามตัวอย่างที่ผมแนบมา ถ้าสามารถกระจายค่าจากสูตร =Sheet1:Sheet3!B2 ต้องได้ค่ากระจายออกมาเป็น 111, 222, 333 ตามลำดับ

    ขอบคุณครับ

  2. #2
    สมเกียรติ
    Guest
    รายละเอียดเกี่ยวกับตำแหน่งอ้างอิง 3 มิติ ดูได้จาก
    http://www.exceluserconference.com/C...%20formula.txt

  3. #3
    อรวีร์
    Guest

    Wink

    ทดลองดูในแฟ้มแนบค่ะ
    อรวีร์สร้างสูตร 4 สูตรแบบ Macro4
    สูตรไม่ผูกพันกับชื่อชีต แต่ใช้ลำดับที่ของชีตแทนค่ะ
    ไม่ทราบตรงกับที่อาจารย์ต้องการหรือไม่คะ

  4. #4
    สมเกียรติ
    Guest
    ผมเห็นตัวอย่างใน link ที่ผมให้ไว้ซึ่งใช้สูตรแบบเดียวกันกับที่คุณอรวีร์แนะนำมาอยู่ก่อนแล้วครับ ยังคิดว่ายากไปอยู่ดี ซึ่งผมเห็นว่าน่าจะใช้ VBA สร้างเป็น Add-in แทน Macro 4 จะสะดวกกว่า
    ขอฝากคุณอรวีร์กับสมาชิกช่วยสร้างสูตร Function VBA ให้ได้ไหมครับ อยากได้สูตรแบบนี้
    =Cell3D( ตำแหน่ง ref ของเซลล์, เลขที่ชีตหรือชื่อชีต )
    เช่น
    =Cell3D(A1,1) ใช้หาค่าจากเซลล์ A1 จากชีตที่ 1 ในแฟ้มนั้น
    =Cell3D(A1,2) ใช้หาค่าจากเซลล์ A1 จากชีตที่ 2 ในแฟ้มนั้น
    =Cell3D(B1,1) ใช้หาค่าจากเซลล์ B1 จากชีตที่ 1 ในแฟ้มนั้น
    =Cell3D(B1,2) ใช้หาค่าจากเซลล์ B1 จากชีตที่ 2 ในแฟ้มนั้น
    =Cell3D(B1,"Sheet1") ใช้หาค่าจากเซลล์ B1 จากชื่อ Sheet1 ในแฟ้มนั้น
    =Cell3D(B1,"Sheet2") ใช้หาค่าจากเซลล์ B1 จากชื่อ Sheet2 ในแฟ้มนั้น
    ขอบคุณครับ

  5. #5
    อรวีร์
    Guest

    Smile

    Code:
    Function Cell3D(Ref As Range, ShNo)
    Cell3D = Sheets(ShNo).Range(Ref.Address).Value
    End Function
    ทดลองดูค่ะ
    เดี๋ยวอรวีร์จะเขียน Function ในกลุ่มนี้มาเพิ่มให้ค่ะ

  6. #6
    อรวีร์
    Guest

    Smile Function อ้างอิงข้าม Sheet

    Code:
    Function Cell3D(Ref As Range, ShNo)
    Cell3D = Sheets(ShNo).Range(Ref.Address).Value
    End Function
     
    Function SheetIndex(Optional ShName) As Integer
    If IsMissing(ShName) Then
        SheetIndex = [A1].Parent.Index
    Else
        Select Case TypeName(ShName)
        Case "Range"
            SheetIndex = ShName.Parent.Index
        Case "String", "Double"
            SheetIndex = Sheets(ShName).Index
        End Select
    End If
    End Function
     
    Function SheetCount() As Integer
    SheetCount = Worksheets.Count
    End Function
     
    Function SheetName(Optional ShName) As String
    If IsMissing(ShName) Then
        SheetName = [A1].Parent.Name
    Else
        Select Case TypeName(ShName)
        Case "Range"
            SheetName = ShName.Parent.Name
        Case "String", "Double"
            SheetName = Sheets(ShName).Name
        End Select
    End If
    End Function
    นอกจากการใช้ Cell3D( ) ตามที่อาจารย์แจ้งแล้วนั้น Function อื่นใช้แบบนี้ค่ะ

    SheetIndex( )
    =SheetIndex( ) แสดงตัวเลขลำดับที่ชีตที่สูตรนี้อยู่
    =SheetIndex("ShName") แสดงตัวเลขลำดับที่ชีตที่ชื่อ ShName
    =SheetIndex(SheetName!A1) แสดงตัวเลขลำดับที่ชีตที่ Cell ที่อ้างถึงอยู่(ลำดับที่ของชีต SheetName)

    SheetName( )
    =SheetName( ) แสดงชื่อชีตที่สูตรนี้อยู่
    =SheetName(2) แสดงชื่อชีตลำดับที่ 2
    =SheetName("shname") แสดงชื่อชีต เช่น ShName
    =SheetName(sheetname!A1) แสดงชื่อชีตที่ Cell ที่อ้างถึงอยู่ เช่น SheetName

    SheetCount( )
    =SheetCount( ) แสดงจำนวนชีตในแฟ้ม

    ตัวอย่างการผสมสูตร
    =Cell3D(A1, SheetIndex( )-1)
    ค่าของ Cell A1 ของชีตที่อยู่ลำดับก่อนชีตที่สูตรนี้อยู่

    =Cell3D(B1, SheetCount( ) )
    ค่าของ Cell B1 ของชีตลำดับสุดท้าย

    =SUM(Cell3d(B2:C4, SheetCount( )-1 ))
    ผลรวมของช่วงบริเวณ B2:C4 ของชีตลำดับรองสุดท้าย

  7. #7
    สมเกียรติ
    Guest
    เยี่ยมครับ ขอบคุณคุณอรวีร์มาก
    ได้สูตรที่แยกไปใช้ได้หลายงานด้วย

    สูตรนี้
    =SheetIndex(sheetname!A1) แสดงชื่อชีตที่ Cell ที่อ้างถึงอยู่ เช่น SheetName
    แสดงชื่อชีตหรือครับ

  8. #8
    อรวีร์
    Guest

    Smile

    อรวีร์อยากเน้นนิดค่ะ เรื่อง Cell3D( ) ได้ผลเป็น Array ด้วยกรณีใส่ Range ลงในวงเล็บ เช่น
    =MATCH(B3, Cell3D( A1:A10000 ,2 ) ,0)
    นำค่าใน B3 ของชีตปัจจุบันไปค้นในช่วงบริเวณ A1:A10000 ในชีตลำดับที่ 2

    ขออภัย พิมพ์ผิดไปหน่อยค่ะ SheetIndex( ) แสดงลำดับที่ค่ะ
    =SheetIndex(sheetname!A1) แสดงลำดับที่ของชีตที่ Cell ที่อ้างถึงอยู่ เช่น SheetName

  9. #9
    อรวีร์
    Guest

    Smile

    พอดีคิดขึ้นได้ว่ายังไม่มี Function แบบที่คืนค่าเป็น Array จากหลายชีตตามตัวอย่างที่อาจารย์ยกมา เลยเขียนมาแบบนี้ค่ะ
    Code:
    Function Cell_3D(Ref As Range, ParamArray ShNo()) As Variant()
    Dim Arr(), I As Integer, J As Integer
    If UBound(ShNo) > 0 Then
        If TypeName(ShNo(0)) = "String" Then ShNo(0) = Sheets(ShNo(0)).Index
        If TypeName(ShNo(1)) = "String" Then ShNo(1) = Sheets(ShNo(1)).Index
        J = ShNo(1) - ShNo(0)
        Arr = Array(Sheets(ShNo(0)).Range(Ref.Address).Value)
        ReDim Preserve Arr(J)
        For I = 1 To J
            Arr(I) = Sheets(ShNo(0) + I).Range(Ref.Address).Value
        Next
        Cell_3D = Arr
    Else
        Cell_3D = Sheets(ShNo(0)).Range(Ref.Address).Value
    End If
    End Function
    การใช้ก็แบบนี้ค่ะ (สมมุติชื่อชีตในแฟ้มเรียงกันแบบนี้ Sh1 , Sh2 , Sh3 , Sh4)
    =Cell_3D(A1,1,3) จะได้ผลเป็น Array ของ 3 ค่าคือ
    { Sh1!A1 , Sh2!A1 , Sh3!A1 }

    =Index( Cell_3D(B1,2,4) ,1,2,1 ) จะดึงค่าที่ 2 ของ Array
    { Sh2!B1 , Sh3!B1 , Sh4!B1 } คือได้ค่าของ B1 ของชีต Sh3

    =Cell_3D(A1,"Sh2", SheetCount( ) ) จะได้ผลเป็น Array ของ Cell A1 ตั้งแต่ชีตชื่อ Sh2 จนถึงชีตสุดท้าย

    ข้อจำกัดคือ ไม่สามารถใส่ Range เป็นช่วงหลายเซลล์ ได้ เช่น
    =Cell_3D( A1:A3 , 1, 3 )

  10. #10
    สมเกียรติ
    Guest
    คราวนี้ได้ใช้สูตรดึงค่าจากชีตมาเปรียบเทียบกันได้สบาย ขอบคุณครับ

    ผมจะขอย้ายกระทู้นี้ไปเก็บที่ Excel Expert Volunteer's Articles & News แทนนะครับ จะได้ค้นหากันง่ายขึ้น

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •