کاربرد تابع INDIRECT

[samina]

با سلام خدمت شما دوستان عزیز لطفاً با مثال تابع INDIRECT را توضیح دهید

[khakzad]

سلام
بطور غیر مستقیم به اشاره میکنه
یعنی ارگومان رو ادرس یک محدوده قرار بدید.میره عملیات رو روی اون ادرس انجام میده
مثلا:
[undefined=undefined]=sum(indirect(a1:a10)
الان میاد اطلاعات محدوده ده تایی رو جمع میزنه

[Amin_D]

سلام
به نظر میرسه این تابع کاربردهای زیادی میتونه از ترکیب با فرمول های دیگه برامون داشته باشه
اگر ممکنه توضیحات بیشتری بفرمائید همچنین با ذکر مثال
با تشکر

[homai]

با سلام
میشه این فرمول رو ترجمه بفرمایید

=SUM(INDIRECT("a"&G2&":c"&I2))

[Alireza]

با سلام
تابع ترکیبی sum و indirect در مواقعی استفاده میشود که ما نیاز به تعریف رنج sum در سلول دیگه ای داشته باشیم یعنی بطور مثال در سلول a1 یک رنج تایپ میکنیم b1:b50 و در فرمول نویسی این سلول رو بعنوان مرجع استفاده میکنیم اگر از تابع indirect استفاده نشود سلول مرجع ما توسط تابع sumبعنوان text در نظر گرفته میشود و فرمول قابل اجرا نمی باشد.

[Behnam]

محدوده ستون های a و b و c رو هر عددی باشه جمع میزنه،اما g2 و i2 به نظر من غلطه و قاعدتا باید خطا بده اگه اون سلول ها پر باشه

[Amirsayyafi]

ترجمه فرمول



=SUM(INDIRECT("a"&G2&":c"&I2))








با فرض اینکه G2 برابر با 3 و سلول I2 برابر با 8 باشه میشه



=SUM(A3:C8)


فرمول INDIRECT یکی از فرمولهای جادویی اکسل هست و خب باید براش خیلی احترام قائل بشیم!





کاربرد 1 :


یکی از کاربردهای اون در کمبوباکس کردن Datavalidation هستش که قبلا در فروم مثالشو داشتیم.





فرض کنید جدول زیر را داریم که در ستون 1 اون عناوین سه گروه قرار داره و در ستون دوم اون سه منطقه یک ستونی (مثلاً S2:S18) با نامگذاری محدوده:


گروه مهندس Data1


گروه فنی Data2


گروه ستادی Data3





همانطور که گفتیم Data1، Data2 و Data3 نام سه منطقه یک ستونی هستش که در هر یک از اونها اسامی کارکنان گروه مربوطه قید شده. در دیتاولیدیشن اول نام گروهها فراخوانی میشود و در دیتاولیدیشن دوم با استفاده از فرمول INDIRECT جدول مربوطه فراخوانی میشه و خب اگه در ستون اول نام گروهی انتخاب نشه دیتاولیدیشن دوم خالی خواهد بود. (در فروم یک پست به طور کامل در این خصوص وجود داره.)





کاربرد 2:


مثال دیگه فرمول فایل پیوست CHAL5 هستش که در اون مجبور هستیم از این فرمول استفاده کنیم.





کاربرد 3:


در فایل درج عنوان مطابق با فیلتر در دومین راه حل برای پاسخمون از فرمول INDIRECT استفاده شده





یادتون باشه در حل بعضی از مسائل ممکن نیست که بدون این فرمول بتونید به پاسخ مناسب برسید.

[Amirsayyafi]

به حضور indirect در فرمولهای زیر توجه کنید:






index(indirect(a1);1;1)



vlookup(a8;indirect(a1);2;0)






که دراونها سلول a1 مقدار یک محدوده مثلاً b1:c4 هستش. که میتونه خودش حاصل یک فرمول باشه و بنابراین متغیره.

[mostafap4]

سلام
يك سوالي براي من پيش اومد
آيا ميشه با استفاده از تابع ايندايركت از يك فايل اكسل ديگه اطلاعات را فراخواني كرد؟
راستش من هرچي سعي كردم همش اشكال REf!*ميگيره
ممنون ميشم راهنماييم كنيد

[Ali Parsaei]

سلام
يك سوالي براي من پيش اومد
آيا ميشه با استفاده از تابع ايندايركت از يك فايل اكسل ديگه اطلاعات را فراخواني كرد؟
راستش من هرچي سعي كردم همش اشكال ref!*ميگيره
ممنون ميشم راهنماييم كنيد


سلام،
در متن تابع indirect ارجاع به يک آدرس مي دهيم و آرگومان آن حتما" بايد text باشد، و کار اين تابع آن است که محتواي درون آدرسي که در متن آن درج کرده ايم را به ما باز مي گرداند. که البته آن آدرس مي تواند يک سل يا يک محدوده باشد.

بنابراين به فرض براي ارجاع به خانه a1 در شيت 1 اگر شما بنويسيد:

=indirect(sheet1!a1)
تابع خطاي ref خواهد داد و بايد بنويسيد:

=indirect("sheet1!a1")
براي فراخوان از يک فايل ديگر هم به همين صورت است.

[mostafap4]

سلام و تشكر
جناب آقاي پارسا ميشه يك نمونه برام بفرستيد چون من اين موارد را رعايت كردم ولي بازم اين اشكال را ميگيره، براي فراخواني از داخل همون فايلي كه دارم داخلش فرمول ايندايركت را مينويسم مشكلي ندارم و كار ميكنه ولي ميخوام از يك فايل ديگه اطلاعات را فراخواني كنم كه اشكال ميگيره
ممنونم

[mostafap4]

باز هم سلام
براي مثال يك فايل زيپ شامل دو فايل اكسل تقديم ميشود
كه ميخوام در فايل اكسل 1 اطلاعات را از فايل اكسل pp فراخواني كند
ممنونم

[Ali Parsaei]

باز هم سلام
براي مثال يك فايل زيپ شامل دو فايل اكسل تقديم ميشود
كه ميخوام در فايل اكسل 1 اطلاعات را از فايل اكسل pp فراخواني كند
ممنونم
ببينيد F3 به عنوان آرگومان تابع INDIRECT غلط است، گفتم، بايد آرگومان تابع حتما" از نوع TEXT باشد.
بايد از تابع ADDRESS در داخل تابع INDIRECT استفاده کنيد، داخل خانه F4 فرمول زير را بنويسيد:

=INDIRECT(ADDRESS(1;1;1;1;"[pp.xlsx]"&D2))
جواب خواهد داد و پاسخ مربوطه با تغيير خانه D2 تغيير خواهد کرد. (منتهي هر دو فايل بايد در يک دايرکتوري باشند مانند فايل زيپي که فرستاده ايد)
کارتان با اين راه مي افتد يا حتما" بايد به خانه F3 هم مربوط گردد؟

[mostafap4]

ممنونم استاد
نه نميخواد به F3 مربوط باشه فقط ميخواستم كه نام شيت را از D2 بخونه كه درسته فقط چند تا سوال ديگه:
اول اينكه فكر كنم اين فقط وقتي كار ميكنه كه فايل PP*هم باز باشه چون وقتي ميبندم همون ERROR*را ميده بازم
سوال بعديم را بايد مثالش را آماده كنم بعدا ميپرسم
فقط ميشه يك توضيحي راجب به تابع Address*كه نوشتيد بهم بديد؟
ممنونم

[Ali Parsaei]

و اما تابع address :
اين تابع ارجاع به يک آدرس مي دهد.
آرگومان اول سطر مربوطه است، آرگومان دوم ستون مربوطه است،
آرگومان سوم مربوط به تعيين نوع آدرس دهي (مطلق و نسبي) است، (به چهار حالت قابل تنظيم است)
آرگومان چهارم مربوط به الگويي است که تابع خروجي خود را بر مبناي آن ارائه مي دهد (مثل a1 يا r1c1 ) اگر آرگومان true يا بدون مقدار باشد از الگوي rc استفاده نخواهد کرد
آرگومان آخر نام شيت را بر مي گرداند و اگر نوشته نشود تابع شيت جاري را مبنا قرار خواهد داد.
پس به عنوان مثال در فرمولي که برايتان نوشتم اگر به جاي خانه a1 بخواهيد خانه b1 برگردانده شود بايد دو آرگومان اول تابع آدرس که 1 و 1 مي باشند را به 1 و 2 تغيير دهيد (يعني سطر اول و ستون دوم شيت مورد نظر )
اين تابع معمولا" با تابع indirect به کار مي رود

[mostafap4]

ممنونم جناب پارسا
فقط سوال اولم را جواب ندادید؟ آیا این تابع indirect فقط وقتی کار میکنه که اون یکی فایل هم باز باشه؟

[Ali Parsaei]

ممنونم جناب پارسا
فقط سوال اولم را جواب ندادید؟ آیا این تابع indirect فقط وقتی کار میکنه که اون یکی فایل هم باز باشه؟
بله ظاهرا" اينطور است

[mostafap4]

تابع دیگری سراغ دارید که این کاری که نمونه اش را فرستادم را بتونه انجام بدهد؟
خیلی سپاسگزارم استاد ممنونم از راهنماییتون

[Ali Parsaei]

تابع دیگری سراغ دارید که این کاری که نمونه اش را فرستادم را بتونه انجام بدهد؟
خیلی سپاسگزارم استاد ممنونم از راهنماییتون
اگر شيتي که در آن اطلاعات را فراخوان مي کنيد با بقيه شيت ها همه در يک فايل بود کار خيلي راحت بود، مثال آن در آدرس:
نمایش محتوای یک شیت در شیت دیگر (http://forum.exceliran.com/showthread.php?t=3748)
بحث شده، ولي به اين طريق فعلا" چيزي به ذهنم نمي رسد مگر اينکه از ماکروها بخواهيد استفاده کنيد.
متاسفانه ظاهرا" تابع INDIRECT در صورت بسته بودن فايل دوم کار نمي کند، البته بعضي توابع ديگر مانند VLOOKUP در موارد ارجاع به فايل ديگر در صورت بسته بودن فايل دوم هم به خوبي کار مي کنند (نهايت اينکه سوالي در مورد به روز رساني بکنند). شايد بتوان تابع يا راه حلي پيدا کرد ولي من فعلا" چيزي به ذهنم نمي رسد.
ولي حدث مي زنم بقيه دوستان راه حل هاي مناسبي سراغ خواهند داشت.

[لیلا11]

سلام.من یک فایل اکسلی دارم که چندتا شیت داره و توی هرکدوم یک ستون شماره تلفنها و یه سری اطلاعات دیگست البته تعداد شماره هام خیلی زیاده.این شیتها تو بعضی شماره ها مشترکند.من میخوام همه شماره ها تو یک ستون یک شیت جمع بشه بدون اینکه تکراری باشند و اطلاعات موجود در جلوی هریک از شماره ها در هر کدام از شیتها تو شیت اصلی فراخوانی بشه.لطفا کمکم کنید.

[generalsamad]

با سلام
یک فایل نمونه قرار دادم
البته کدش رو دوستان نوشتن و من بعضی جاهاش رو ویرایش کردم و چند خط کد اضافه کرد


Private Sub CommandButton1_Click()
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
If Sheet1.Range("A2").Value <> "" Then
Dim LR As Long
LR = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Sheet1.Range("A2:D" & LR).ClearContents
End If


Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.CodeName <> "Sheet1" Then
With ws
.Range("A2:D" & .Cells(Rows.Count, 1).End(xlUp).Row).Copy Sheet1.Cells(Rows.Count, "A").End(xlUp).Offset(1)
End With
End If
Next ws

Sheet1.Activate
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
Columns("A:D").Select
ActiveSheet.Range("$A$1:$D$20000").RemoveDuplicates Columns:=Array(1, 2, 3, 4), _
Header:=xlNo
Range("A1").Select
End Sub

فایل ضمیمه کردید

[لیلا11]

سلام .میشه یکی به سوال من جواب بده.

[generalsamad]

مگه جواب رو نگرفتید؟

[لیلا11]

اگه میشه یکم در مورد جواب توضیح بدین.حجم فتایل من در حدود 25meg امکانش هست براتون بفرستم؟

[generalsamad]

فایلتون رو با winrar فشرده کنید اگه حجمش کم شد و امکانش بود همینجا آپلود کنید
یا نیازی نیست فایل اصلیتون رو بفرست
اگه تعداد شیت هاتون زیاده از شیت هاتون حذف کنید
از سطرها هم همینطور که حجم فایلتون کم بشه
که ساختار جدولتون رو ببینیم
بعد بر اساس اون توی کد تغییرات بدیم

[لیلا11]

براتون یه فایل فرستادم با دوتا شیت و یه تعداد کمی از سلول ها .میخوام همه ی شماره ها شیتها در یک شیت بدون تکرار بیاد و مقادیر مقابلشونم از همون فراخوانی بشه

[generalsamad]

با سلام
این فایل رو ببینید

جواب رو توی هر شیتی که میخواید قرار بدید نام اون شیت رو توی کد باید تغییر بدید
نسبت به فایل ارسالی این قسمت از کد


If ws.CodeName <> "Sheet1" Then


به این کد تغییر کرد


If ws.CodeName <> "Sheet3" Then


اگه جواب رو نگرفتید بفرمائید
فایل ضمیمه گردید

[لیلا11]

خیلی ممنون .لطف کردین

[لیلا11]

فقط یه سوال دیگه اینکه حالا اگه به جواب رسیدمو برای هر شماره ای مقدارشو بدست آوردم لازمه که برای هر شماره ای به تعداد مقادیرش که عدد صحیح مثبتی هست کدی تعریف کنم مثلا برای کدی اومده 5 باید 5 تا کد به اون شماره اختصاص بدم.راه حلی براش دارین؟؟

[generalsamad]

متوجه منظورتو نشدم
اگه خواستتون رو به همراه فایل نمونه بیان کنید بهتر راهنمائی میشید

[لیلا11]

با سلام.مشکل قبلی فایلم حل شد الان میخام برای عددهای بدست آمده برای هرشماره کدهای مخصوصی تعریف کنم مثلا امتیاز شماره ای 15 شده باید 15 کد مشخص به شماره مورد نظر نسبت داده بشه .یه قسمتی از فایلو میفرستم براتون .لطفا راهنمایی کنین

[generalsamad]

با سلام

مثلا امتیاز شماره ای 15 شده باید 15 کد مشخص به شماره مورد نظر نسبت داده بشه

منظورتون چی هست
این کدها بر چه اساسی باید بدست بیان؟
کد تکراری داریم؟
واضح تر بیان کنید

[لیلا11]

این کدها به تعداد امتیاز هر شماره ای باید باشه .اعداد جلوی هر شماره همون امتیازها هستن.نه تکراری نمیتونن باشن.
این کدها واسه قرعه کشی لازمن.واسه همین وقتی کدی بدست اومد باید راحت شماره مورد نظرم مشخص بشه.

[generalsamad]

با تابع randbetween کارتون راه میوفته؟
که یه بازه مشخص میکنیم که اعداد تصادفی برای هر شماره ایجاد کنه البته بدون تکرار

[لیلا11]

کاربردشو توضیح میدین ؟تابعی باشه که مثلا برای امتیاز 10 ، ده تا کد متوالی بده.

[generalsamad]

این تابع دو آرگومان داره
که حد پائین و حد بالا رو وارد میکنیم و توی اون بازه عدد صحیح بر میگردونه
مثلا اگه بخوایم عدد تصادفی بین اعداد 1-10 تولید کنیم از این تابع میتونیم استفاده کنیم
بعد اگه بخوایم تکراری نباشه فرمولش رو باید با چند تابع ترکیب کرد


فرمودید متوالی باشه؟

[لیلا11]

بله کدها متوالی باشند و بدون تکرار

[generalsamad]

منظورتون از کدهای متوالی اینطوره؟
3
4
5
6
7
8
؟

[لیلا11]

بله.فقط مثلا فرض کنیم کد از 1000000شروع بشه بعد متوالی ادامه پیدا کنه
1000001
1000002
1000003
1000004
.
.
.

[generalsamad]

این توضیحاتی که دادید و من برداشت کردم نیازی به فرمول نویسی نداره
شما ابتدا میتونید اولین و دومین محدوده رو وارد کنید
مثلا توی خونه b2 وارد میکنید 1000
بعد توی خونه b3 وارد میکنید 1001
بعد سلول b2 و b3 رو انتخاب و لبه پائین سلول b3 رو گرفته و به پائین درگ میکنیم

یا از این روش که شاید هر بار بخواهید محدوده رو تغییر بدید
توی خونه b2 عدد مورد نظرتون رو مینویسید
توی خونه b3 اینو مینویسید


=b2+1


بعد لبه پائینی سلول b3 رو گرفته و به پائین درگ میکنید

برداشت من از توضیحاتتون این بود
فایل ضمیمه گردید

[لیلا11]

منظور من اینه که برای هر شماره ای به اندازه امتیازیاش کدهایی اختصاص بده نه اینکه برای هر شماره یه کدی اختصاص بده.تو فایلی که فرستادن برای هر شماره یه کدی اورده

[generalsamad]

خوب اون امتیازاتی که برای هر شماره دادید کدهای متوالی هم باید از بیشترین امتیاز شروع بشه یا کمترین امتیاز؟

[لیلا11]

نه.ترتیت شماره ها ثابته به همین خاطر دیگه لازم نیست ترتیب امتیازا عوض بشه برای هر امتیازی هرچقدم باشه اون تعداد کد تعریف بشه

[generalsamad]

متوجه منظورم نشدید
هر شماره یه امتیاز داره
میخواید اون شماره هائی که امتیازشون نسبت به بقیه بالاتره از نظر کد گذاری جز کدگذاری های اول باشه یا آخر

[لیلا11]

میشه تربیت اصلیشون حفظ بشه؟در کل فرقی نداره

[generalsamad]

آره
با تابع میشه
بدون تابع هم میشه

[لیلا11]

خب راهنمایی کنین

[generalsamad]

اول بدون تابع میگم
شما یه ستون کمکی ایجاد کنید و شماره گذاریش کنید (هدف: ترتیب شماره ها رعایت بشه)
بعد ستون امتیازها رو مرتب سازی کنید
بعد از مرتب سازی حالا توی یه ستون دیگه اون کدها رو وارد کنید که از هر عددی که میخواید شروع بشه به همون شکل بالائی که گفتم درگ کنید
بعد از این کار ستون کمکی که ایجاد کرده بودیم رو مرتب میکنیم که ترتیب قبلی برگرده
بعد میتونید این ستون کمکی رو حذف کرد

فایل ضمیمه گردید

[لیلا11]

مثل اینکه منظورمو نتونستم برسونم.برای هر شماره به تعداد امتیازش کد لازم دارم یعنی امتیاز شماره ای باشه 10 ،ده تا کد به اون شماره باید اختصاص داده بشه

[khakzad]

من همچین برداشتی داشتم:
ببینید همینه؟ (سل زرد مبنای کد شماست. هر عددی بزنید سایر کدها از اون به بعد تولید میشن . که من 1000 نوشتم.)

پ.ن: واقعا صبر جناب generalsamad ستودنی است. من که اگر باشم اصولا بعد از یکی دو پست شخص نتونه منظورشو برسونه دیگه پیگیری نمی کنم!

دوست عزیز. لطفا برای رسیدن به جواب و صرفه جویی در وقت، حتما مثال بزنید. اگر یک مثال عددی داخل فایل می زدید وروی اون توضیح می دادید خیل سریع تر از اینها انجام میشد.
امیدوارم منظورتون رو متوجه شده باشم

[generalsamad]

تشکر
طبق آخرین خواسته همین مد نظر بوده
که سرکار خانم خاکزاد زدوتر پاسخ رو قرار دادند
ممنون

[لیلا11]

سلام.خیلی ممنون بابت راهنماییتون.
لطف کردین