ตรวจสอบและปรับปรุงไฟล์ Excel ให้เร็วและเสถียรด้วย File Anatomy Add-in
เครื่องมือนี้ช่วยวิเคราะห์โครงสร้างไฟล์อย่างละเอียด เช่น สูตร, ลิงก์, PivotTable, Styles และ Objects
พร้อมฟังก์ชันล้างสิ่งเกินจำเป็นที่ทำให้ไฟล์ช้า หรือเสียหายได้ในคลิกเดียว
เหมาะสำหรับผู้ใช้ Excel ที่ต้องการควบคุมคุณภาพไฟล์อย่างมืออาชีพ
ทดลองใช้ Add-in
snasui.com ยินดีต้อนรับ 
สมาชิกท่านใดที่ยังไม่ได้ระบุ Version ของ Excel ที่ใช้งานจริง สามารถทำตาม Link นี้เพื่อจะได้รับคำตอบที่ตรงกับ Version ของท่านครับ 
ระบุ Version ของ Excel 
โปรดทราบ สมัครสมาชิก
Login ผ่าน Facebook Reset รหัสผ่าน ติดต่อผู้ดูแลระบบ ตั้งค่าส่วนตัว วิธีการตั้งและตอบกระทู้ จัดรูปแบบตัวอักษร กำหนดขนาดตัวอักษรใน Browser
คำแสดงเจตนา[code] และปิดด้วย [/code] ตัวอย่างเช่น [code]dim r as range[/code] เพื่อให้แตกต่างจากข้อความทั่วไป สะดวกในการอ่านและทดสอบ (คลิกเพื่อดูตัวอย่าง)
ขอบคุณครับสำหรับการช่วยทดสอบปรับแก้ไขสูตรให้ มองข้ามสิ่งเล็กๆน้อยๆไปได้
ที่ซีท Report นั้นต้องการใช้สูตรดึงข้อมูลให้มาแสดงโดยตรงเลย โดยไม่ต้องทำคอลัมภ์ช่วยที่ซีท Database มันก็จะสะดวกไปอีกแบบหนึ่งที่ไม่ต้องไปยุ่งกับฐานข้อมูล3. ไม่ว่าจะในชีท look, look1 หรือ report ส่วนที่แสดงข้อมูลด้านล่างนั้น ใช้สูตรลักษณะเดียวกันทั้งหมดครับ เพียงแต่ไปอ้างอิงจากคอลัมน์ช่วยในชีท database คนละคอลัมน์กันเท่านั้น สาระสำคัญคือ สูตรในแต่ละคอลัมน์ช่วยนั้นต่างหาก (ในชีท report ทำไมต้องเขียนอะเรย์ ทั้งๆ ที่ใช้สูตรเหมือนชีทก่อนหน้าก็ได้แล้ว)
สูตรนี้ใช้ดึงข้อมูลจากตารางแบบ 2 ทาง คือ ทางบรรทัดและทางคอลัมน์ แต่จากโจทย์นี้ต้องการดึงข้อมูลทางบรรทัดเท่านั้น เพียงแต่ต้องเข้า 2 เงื่อนไข คือ รหัสพนักงานและปี จึงสามารถเปลี่ยนสูตรเป็นjoo wrote:ได้ลองปรับสูตรที่ซีท Report ใหม่ที่ C12
=INDEX(Database!A$4:A$10,MATCH($F5,Database!$B$4:$B$10,0),MATCH($F6,Database!$A$4:$A$10,0))
ก็แสดงข้อมูลได้ครับแต่ข้อมูลมันจะซ้ำๆกันและถ้าเงื่อนไขไม่ตรงจะขึ้น #N/A ครับ
แนบตัวอย่างมาใหม่ดีกว่าครับ แจ้งว่าเปลี่ยนค่าเงื่อนไขในเซลล์ใดบ้าง และช่วยแจ้งให้ชัดเจนว่าต้องการผลลัพธ์เป็นอย่างไร เพื่อจะได้ตอบให้ตรงประเด็น แนบสูตรที่ได้ทดลองเขียนเองมาแล้วด้วยครับ ที่เขียนมาใช้เพื่อทำการ List รายการที่เข้าเงื่อนไขทั้งหมดออกมาแสดง หากมี 10 รายการก็จะ List ออกมาทั้งหมด (หากมีการ Copy สูตรลงไปทางด้านล่าง) จากโจทย์ด้านล่าง
และตัวอย่างสูตรทีใช้ดึงข้อมูลที่ C12 คือค้นหาข้อมูลโดยใช้ รหัสบุคคลและปีงบประมาณเป็นเงื่อนไขในการค้นหา เมื่อเปลี่ยนปีงบประมาณหรือรหัสบุคคลอย่างใดอย่างหนึ่งให้ข้อมูลที่แสดงมีการเปลี่ยนแปลงตามเงื่อนไข ถ้าไม่มีก็ให้แสดงค่าว่าง แต่ปัจจุบันทำได้เพียงเงื่อนไขเดียว คำตอบที่ต้องการคือเมื่อเลือกรหัสบุคคลและปีก็ให้แสดงค่าตามตัวอย่างนี้ครับ
จากไฟล์แนบอันล่าสุด ชีท look1 เซลล์ C11 มีสูตรนี้ =IF(N($B11),LOOKUP($B11,Database!$M:$M,Database!A:A),"") joo wrote:ขอบคุณครับสำหรับคำแนะนำในการปรับปรุงแก้ไขสูตร ทดลองแล้วสามารถใช้งานได้ดีครับ
มีคำถามเพิ่มครับที่ซีท look1 เซลล์ C11 ผมใช้สูตรค้นหาข้อมูลโดยมีเงื่อนไขคือเดือนกับปี เมื่อนำไปใช้กับข้อมูลปริมาณมากๆ พบว่าคำนวณช้ามากหรือว่ามันเป็นธรรมดาของสูตรแบบอเรย์ครับ พอมีวิธีแก้ไขให้เร็วขึ้นบ้างไหมครับ สูตรที่ใช้แบบนี้ครับ
=IF(ROWS($C$11:C11)>$F$6,"",INDEX(Database!A$4:A$11,SMALL(IF(TEXT(Database!$F$4:$F$10,"mmmm")=$F$4,IF(Database!$A$4:$A$10=$F$5,ROW(Database!$A$4:$A$10)-ROW(Database!$B$4)+1)),ROWS($C$11:C11))))
กรณีคำนวณช้าเป็นธรรมดาของสูตร Array ครับ 
ตอบไม่เคยทันอาจารย์เลยจริงๆ ลองตามนี้ครับCode: Select all
Sub ShowDataTraining()
On Error Resume Next
Application.EnableEvents = False
Sheets("Database").Range("A3:K100").AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=Sheets("Search").Range("M3:N4")
Sheets("Database").Range("A4:K100").SpecialCells(xlCellTypeVisible).Copy
Sheets("Search").Range("C12").PasteSpecial xlPasteValues
Sheets("Database").ShowAllData
Range("F4").Activate
Application.CutCopyMode = False
Application.EnableEvents = True
End Sub
Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("F4:F6")) Is Nothing Then
Call ShowDataTraining
End If
End Sub
Code: Select all
Option Explicit
Option Base 1
Sub ShowDataTraining()
Dim a() As Variant, lng As Long
Dim r As Range, rAll As Range
Dim rt As Range, rl As Long
Application.EnableEvents = False
Application.ScreenUpdating = False
rl = Rows.Count
With Worksheets("Database")
Set rAll = .Range("B4", .Range("B" & rl).End(xlUp))
End With
For Each r In rAll
If r = Worksheets("Search").Range("F5") Then
lng = lng + 1
ReDim Preserve a(12, lng)
a(1, lng) = lng
a(2, lng) = r.Offset(0, -1)
a(3, lng) = r.Offset(0, 0)
a(4, lng) = r.Offset(0, 1)
a(5, lng) = r.Offset(0, 2)
a(6, lng) = r.Offset(0, 3)
a(7, lng) = r.Offset(0, 4)
a(8, lng) = r.Offset(0, 5)
a(9, lng) = r.Offset(0, 6)
a(10, lng) = r.Offset(0, 7)
a(11, lng) = r.Offset(0, 8)
a(12, lng) = r.Offset(0, 9)
End If
Next r
If lng > 0 Then
With Worksheets("Search")
Set rt = .Range("B12", .Range("Q" & lng - 1 + 12))
If .Range("B12") <> "" Then 'Check if isblank
.Range("B12", .Range("B" & rl).End(xlUp).Offset(0, 4)).ClearContents
End If
.Range("B12:M12").Copy
rt.PasteSpecial xlPasteFormats
rt = Application.Transpose(a)
.Range("D12", .Range("D" & rl).End(xlUp)).NumberFormat = "00000"
.Range(.Range("B11").End(xlDown).Offset(1, 0), .Range("Q" & rl)).Clear 'Change new start cell
.Range("F4").Activate
End With
Else
MsgBox "Data not found."
End If
'Range("A5:E5").AutoFilter
Application.EnableEvents = True
Application.ScreenUpdating = True
End Subjoo wrote:ถ้าข้อมูลใน Database มีปริมาณมากก็จะต้องคอยปรับแก้ไข
เรนจ์ที่อ้างอิงในโค๊ดอยู่เรื่อยๆซึ่งดูแล้วไม่ค่อยยืดหยุ่นเท่าไร
เราสามารถปรับ Code ให้ยืดหยุ่นได้ครับ เช่น จาก
Code: Select all
Sheets("Database").Range("A3:K100").AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=Sheets("Search").Range("M3:N4")Code: Select all
Sheets("Database").Range("A3",Range("K" & Rows.Count).End(xlUp)).AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=Sheets("Search").Range("M3:N4")Code: Select all
For Each r In rAll
If r = Worksheets("Search").Range("F5") ThenCode: Select all
For Each r In rAll
If r = Worksheets("Search").Range("F5") And r.Offset(0,1) = Worksheet("Search").Range("F6") Thenทดสอบแล้วพบว่าข้อมูลแสดงออกมาหมดเลยไม่ตรงตามเงื่อนไขCode: Select all
Sheets("Database").Range("A3",Range("K" & Rows.Count).End(xlUp)).AdvancedFilter Action:=xlFilterInPlace, _ CriteriaRange:=Sheets("Search").Range("M3:N4")
ทดลองดูแล้วข้อมูลไม่ยอมแสดง ขึ้นคำว่า Data No found ตลอดครับCode: Select all
For Each r In rAll If r = Worksheets("Search").Range("F5") And r.Offset(0,1) = Worksheets("Search").Range("F6") Then