misammisam
2014/10/08, 11:16
1 : نحوه كار ساده با تابع HLOOKUP
1- 1 : بانك اطلاعاتي مورد نظر
http://www.exceltrick.com/wp-content/uploads/2013/06/How-to-Use-Excel-H-Lookup.png (http://www.exceltrick.com/wp-content/uploads/2013/06/How-to-Use-Excel-H-Lookup.png) 1- 2 : شروع كار با تابع HLOOKUP و مشخص كردن ستون مورد جستجو
http://www.exceltrick.com/wp-content/uploads/2013/06/How-to-Use-Excel-H-Lookup-02.png (http://www.exceltrick.com/wp-content/uploads/2013/06/How-to-Use-Excel-H-Lookup-02.png)
1- 3 : مشخص كردن محدوده بانك اطلاعاتي در HLOOKUP http://www.exceltrick.com/wp-content/uploads/2013/06/How-to-Use-Excel-H-Lookup-03.png (http://www.exceltrick.com/wp-content/uploads/2013/06/How-to-Use-Excel-H-Lookup-03.png)
1- 4 : مشخص كردن رديف مورد جستجو http://www.exceltrick.com/wp-content/uploads/2013/06/How-to-Use-Excel-H-Lookup-04.png (http://www.exceltrick.com/wp-content/uploads/2013/06/How-to-Use-Excel-H-Lookup-04.png)
1- 5 : در صورت FALSE بودن گزينه پنجم ، مقدار دقيق برگردانده ميشود http://www.exceltrick.com/wp-content/uploads/2013/06/How-to-Use-Excel-H-Lookup-05.png (http://www.exceltrick.com/wp-content/uploads/2013/06/How-to-Use-Excel-H-Lookup-05.png)
1- 6 : نتيجه جستجو با HLOOKUP http://www.exceltrick.com/wp-content/uploads/2013/06/How-to-Use-Excel-H-Lookup-06.png (http://www.exceltrick.com/wp-content/uploads/2013/06/How-to-Use-Excel-H-Lookup-06.png)
2 : چند مثال كاربردي در HLOOKUP
2 - 1 : مثال اول - جستجوي ساده
http://www.exceltrick.com/wp-content/uploads/2013/06/How-to-Use-Excel-H-Lookup.png (http://www.exceltrick.com/wp-content/uploads/2013/06/How-to-Use-Excel-H-Lookup.png)
2 - 2 :
http://www.exceltrick.com/wp-content/uploads/2013/06/HLookup-Example01-07.png (http://www.exceltrick.com/wp-content/uploads/2013/06/HLookup-Example01-07.png)
2 - 3 : مثال دوم - جستجوي ستون خاص با كلمه اول ستون به كمك كاراكتر *
http://www.exceltrick.com/wp-content/uploads/2013/06/HLookup-Example02-08.png (http://www.exceltrick.com/wp-content/uploads/2013/06/HLookup-Example02-08.png)
3 : HLOOKUP در ويژوال بيسيك3 - 1 : بانك اطلاعاتي مورد نظر
http://www.exceltrick.com/wp-content/uploads/2013/06/hlookup-in-VBA-14.png
(http://www.exceltrick.com/wp-content/uploads/2013/06/hlookup-in-VBA-14.png)
3 - 2 : بطور مثال كد زير را در VB به يك دكمه اختصاص بدهيد
Sub H_LOOKUP() On Error GoTo ErrorHandler Dim student As String Dim Result As String student = InputBox("Enter the student Name:") If Len(student) > 0 Then Result = "Science - " & Application.WorksheetFunction.HLookup(student, ActiveSheet.Range("A1:I5"), 2, False) Result = Result & vbNewLine & "Maths - " & Application.WorksheetFunction.HLookup(student, ActiveSheet.Range("A1:I5"), 3, False) Result = Result & vbNewLine & "English - " & Application.WorksheetFunction.HLookup(student, ActiveSheet.Range("A1:I5"), 4, False) Result = Result & vbNewLine & "History - " & Application.WorksheetFunction.HLookup(student, ActiveSheet.Range("A1:I5"), 5, False) MsgBox student & " has got following Marks:" & vbNewLine & Result End If Exit Sub ErrorHandler: If Err.Number = 1004 Then MsgBox "Student Not found in the records!" Else MsgBox "Some Error Occurred" End If End Sub
3 - 3 : نتيجه در يك MsgBox به نمايش در ميايد
http://www.exceltrick.com/wp-content/uploads/2013/06/hlookup-in-VBA-15.png (http://www.exceltrick.com/wp-content/uploads/2013/06/hlookup-in-VBA-15.png)
4 :* جستجوي ناموفق در HLOOKUP و كاربرد تابع ISNA در HLOOKUP
4 - 1 : در صورت عدم موفقيت در HLOOKUP با پيغام N/A# مواجه خواهيد شد
http://www.exceltrick.com/wp-content/uploads/2013/06/hlookup-with-ISNA-16.png (http://www.exceltrick.com/wp-content/uploads/2013/06/hlookup-with-ISNA-16.png)
4 - 2 : براي جلو گيري از نمايش اين پيغام و دادن پيغام مورد نظر خودمان از تابع ISNA ميتوان به صورت زير استفاده كرد
http://www.exceltrick.com/wp-content/uploads/2013/06/rsz_hlookup-with-isna-17.png (http://www.exceltrick.com/wp-content/uploads/2013/06/rsz_hlookup-with-isna-17.png)
5 : استفاده از فرمولهاي آرايه اي HLOOKUP در اكسل
http://www.exceltrick.com/wp-content/uploads/2013/06/HLOOKUP-As-Array-Function.gif (http://www.exceltrick.com/wp-content/uploads/2013/06/HLOOKUP-As-Array-Function.gif)
منبع سايت : Tricking Excel The Smarter Way ! (http://www.exceltrick.com)
1- 1 : بانك اطلاعاتي مورد نظر
http://www.exceltrick.com/wp-content/uploads/2013/06/How-to-Use-Excel-H-Lookup.png (http://www.exceltrick.com/wp-content/uploads/2013/06/How-to-Use-Excel-H-Lookup.png) 1- 2 : شروع كار با تابع HLOOKUP و مشخص كردن ستون مورد جستجو
http://www.exceltrick.com/wp-content/uploads/2013/06/How-to-Use-Excel-H-Lookup-02.png (http://www.exceltrick.com/wp-content/uploads/2013/06/How-to-Use-Excel-H-Lookup-02.png)
1- 3 : مشخص كردن محدوده بانك اطلاعاتي در HLOOKUP http://www.exceltrick.com/wp-content/uploads/2013/06/How-to-Use-Excel-H-Lookup-03.png (http://www.exceltrick.com/wp-content/uploads/2013/06/How-to-Use-Excel-H-Lookup-03.png)
1- 4 : مشخص كردن رديف مورد جستجو http://www.exceltrick.com/wp-content/uploads/2013/06/How-to-Use-Excel-H-Lookup-04.png (http://www.exceltrick.com/wp-content/uploads/2013/06/How-to-Use-Excel-H-Lookup-04.png)
1- 5 : در صورت FALSE بودن گزينه پنجم ، مقدار دقيق برگردانده ميشود http://www.exceltrick.com/wp-content/uploads/2013/06/How-to-Use-Excel-H-Lookup-05.png (http://www.exceltrick.com/wp-content/uploads/2013/06/How-to-Use-Excel-H-Lookup-05.png)
1- 6 : نتيجه جستجو با HLOOKUP http://www.exceltrick.com/wp-content/uploads/2013/06/How-to-Use-Excel-H-Lookup-06.png (http://www.exceltrick.com/wp-content/uploads/2013/06/How-to-Use-Excel-H-Lookup-06.png)
2 : چند مثال كاربردي در HLOOKUP
2 - 1 : مثال اول - جستجوي ساده
http://www.exceltrick.com/wp-content/uploads/2013/06/How-to-Use-Excel-H-Lookup.png (http://www.exceltrick.com/wp-content/uploads/2013/06/How-to-Use-Excel-H-Lookup.png)
2 - 2 :
http://www.exceltrick.com/wp-content/uploads/2013/06/HLookup-Example01-07.png (http://www.exceltrick.com/wp-content/uploads/2013/06/HLookup-Example01-07.png)
2 - 3 : مثال دوم - جستجوي ستون خاص با كلمه اول ستون به كمك كاراكتر *
http://www.exceltrick.com/wp-content/uploads/2013/06/HLookup-Example02-08.png (http://www.exceltrick.com/wp-content/uploads/2013/06/HLookup-Example02-08.png)
3 : HLOOKUP در ويژوال بيسيك3 - 1 : بانك اطلاعاتي مورد نظر
http://www.exceltrick.com/wp-content/uploads/2013/06/hlookup-in-VBA-14.png
(http://www.exceltrick.com/wp-content/uploads/2013/06/hlookup-in-VBA-14.png)
3 - 2 : بطور مثال كد زير را در VB به يك دكمه اختصاص بدهيد
Sub H_LOOKUP() On Error GoTo ErrorHandler Dim student As String Dim Result As String student = InputBox("Enter the student Name:") If Len(student) > 0 Then Result = "Science - " & Application.WorksheetFunction.HLookup(student, ActiveSheet.Range("A1:I5"), 2, False) Result = Result & vbNewLine & "Maths - " & Application.WorksheetFunction.HLookup(student, ActiveSheet.Range("A1:I5"), 3, False) Result = Result & vbNewLine & "English - " & Application.WorksheetFunction.HLookup(student, ActiveSheet.Range("A1:I5"), 4, False) Result = Result & vbNewLine & "History - " & Application.WorksheetFunction.HLookup(student, ActiveSheet.Range("A1:I5"), 5, False) MsgBox student & " has got following Marks:" & vbNewLine & Result End If Exit Sub ErrorHandler: If Err.Number = 1004 Then MsgBox "Student Not found in the records!" Else MsgBox "Some Error Occurred" End If End Sub
3 - 3 : نتيجه در يك MsgBox به نمايش در ميايد
http://www.exceltrick.com/wp-content/uploads/2013/06/hlookup-in-VBA-15.png (http://www.exceltrick.com/wp-content/uploads/2013/06/hlookup-in-VBA-15.png)
4 :* جستجوي ناموفق در HLOOKUP و كاربرد تابع ISNA در HLOOKUP
4 - 1 : در صورت عدم موفقيت در HLOOKUP با پيغام N/A# مواجه خواهيد شد
http://www.exceltrick.com/wp-content/uploads/2013/06/hlookup-with-ISNA-16.png (http://www.exceltrick.com/wp-content/uploads/2013/06/hlookup-with-ISNA-16.png)
4 - 2 : براي جلو گيري از نمايش اين پيغام و دادن پيغام مورد نظر خودمان از تابع ISNA ميتوان به صورت زير استفاده كرد
http://www.exceltrick.com/wp-content/uploads/2013/06/rsz_hlookup-with-isna-17.png (http://www.exceltrick.com/wp-content/uploads/2013/06/rsz_hlookup-with-isna-17.png)
5 : استفاده از فرمولهاي آرايه اي HLOOKUP در اكسل
http://www.exceltrick.com/wp-content/uploads/2013/06/HLOOKUP-As-Array-Function.gif (http://www.exceltrick.com/wp-content/uploads/2013/06/HLOOKUP-As-Array-Function.gif)
منبع سايت : Tricking Excel The Smarter Way ! (http://www.exceltrick.com)