วันพุธที่ 18 กรกฎาคม พ.ศ. 2555

การนำบรรทัดแรกของข้อมูลที่ได้จากการ Filter มาแสดงไม่สามารถใช้สูตร Link มาแบบธรรมดาได้ ทั้งนี้เนื่องจากบรรทัดของข้อมูลที่ได้จากการ Filter นั้นจะไม่คงที่ ขึ้นอยู่กับว่ารายการที่ได้จากการ Filter นั้นอยู่ในบรรทัดใดบ้าง การนำข้อมูลที่พบตัวแรกของรายการที่ได้จากการ Filter จึงต้องใช้สูตรที่ซับซ้อนกว่าปกติ
ยกตัวอย่างเช่นข้อมูล Product ตามภาพด้านล่าง ต้องการให้ Filter ราย Product แล้วนำ Prodcut บรรทัดแรกที่ได้จากการ Filter มาแสดง

ภาพข้อมูล Product

FilterData

สามารถทำได้ดังนี้

ที่เซลล์ A2 คีย์สูตร

=INDEX(A$4:A$20,MATCH(1,SUBTOTAL(3,OFFSET($A$4,ROW($A$4:$A$20)-ROW($A$4),)),0))

Ctrl+Shift+Enter
โดยสูตร SUBTOTAL(3,OFFSET($A$4,ROW($A$4:$A$20)-ROW($A$4),)) เป็นสูตร Array ซึ่งจะแสดงผลลัพธ์เป็นชุดข้อมูล โดยจะแสดงผลลัพธ์เป็น 0 หากบรรทัดนั้นถูกซ่อนจากการ Filter และจะแสดง 1 หากบรรทัดนั้นได้จากการ Filter ซึ่งตัวอย่างสูตรจะแสดงตามด้านล่าง
=INDEX(A$4:A$20,MATCH(1,{0,0,1,0,0,0,0,0,1,1,0,0,1,0,0,1,0},0))
จากตัวอย่างด้านบนจะนำ A6 มาแสดง เนื่องจากเลข 1 ตัวแรกตรงกับ A6 ( เลข 0 ตัวแรกคือ A4) ซึ่งการจะเข้าใจสูตรนี้ต้องเข้าใจสูตร Index และ Match ก่อนครับ
ตัวอย่างการแสดง Prouduct จากการ Filter
FirstItem
  1. ให้รวมตัวเลขทุกตัวแล้วได้ค่าเป็น 9, 18, 27 หรือ 36
  2. สามารถคัดชุดที่มีตัวเลขที่ไม่ต้องการออกไปได้ด้วย ยกตัวอย่างเช่นไม่รวมตัวเลขที่มีค่าต่อไปนี้ 0, 3, 7, 8
การหาค่าดังกล่าวสามารถใช้สูตรหรือ VBA มาคำนวณได้ครับ

กรณีใช้สูตรในการคำนวณมีวิธีการดังนี้


  1. เปิดสมุดงานใหม่แล้วเลือกชีทว่าง
  2. ที่ A1 คีย์ No. และ ที่ B1 คีย์ Select เพื่อเป็นชื่อ Field
  3. ที่ A2 คีย์ Row()-1 > Copy ลงด้านล่างตามต้องการ
  4. ที่ B2 คีย์สูตร
    =IF(MIN(LEN(SUBSTITUTE(A2,{0,3,7,8},"")))<LEN(A2),"",IF(ISNUMBER(MATCH(SUM(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)+0),{9,18,27,36},0)),SUM(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)+0),""))Ctrl+Shift+Enter > Copy ลงด้านล่าง

กรณีใช้ VBA ในการคำนวณมีวิธีการดังนี้

  1. กดแป้น Alt+11 เพื่อเปิดหน้าต่าง VBE
  2. คลิกเมนู Insert > Module
  3. Copy Code ด้านล่างไปวาง

    Sub ListNum()
    Dim i As Integer, bln As Boolean
    Dim c As Integer, j As Integer
    Dim a As String, s As Integer
    For i = 1 To 9999
    bln = False
    s = 0
    For j = 1 To Len(CStr(i))
    a = Mid(i, j, 1)
    Select Case a
    Case 0, 3, 7, 8
    bln = True
    Exit For
    Case Else
    s = s + a
    End Select
    Next j
    If bln = False Then
    Select Case s
    Case 9, 18, 27, 36
    c = c + 1
    Cells(c + 1, 1) = i
    End Select
    End If
    Next i
    End Sub
  4. กดแป้น Alt+Q เพื่อกลับมายังโปรแกรม Excel 
  5. กดแป้น Alt+F8 > เลือก ListNum > Run

โปรแกรมจะทำการ Run ตัวเลขมาให้ตามเงื่อนไขที่ต้องการ

ภาพตัวอย่างการใช้งาน


ListNum
Posted by netdesign On 07:36 No comments

0 ความคิดเห็น:

แสดงความคิดเห็น

Onsite computer repair Ramkhamhaeng.














รับซ่อมคอมพิวเตอร์นอกสถานที่ รามคำแหง บางกะปิ นวมินทร์ เสรีไทย


ค่าแรง+ค่ารถ+ลงwinodws+โปรแกรมดูหนังฟังเพลง+office+antivirus 400 บาท
ปล. ให้บริการ เฉพาะเขตพื้นที่ รามคำแหง บางกะปิ นวมินทร์ เสรีไทย ลาดพร้าวเฉพาะ บริเวณ จากเดอะมอลบางกะปิถึงโชคไชย4


สนใจติต่อ : Tel. 083-792-5426





ดูแผนที่ขนาดใหญ่ขึ้น


  • RSS
  • Delicious
  • Digg
  • Facebook
  • Twitter
  • Linkedin
  • Youtube

ป้ายกำกับ

คลังบทความของบล็อก

Advertisement

Unordered List