توابع جستجو و مرجع در اکسل – قسمت دوم

توابع جستجو و مرجع در اکسل در بخش پایگاه دانش در سه مقاله ارایه شده است، در مقاله ی اول به معرفی کلی این دسته از توابع در اکسل پرداخته شده است و برخی توابع مرجع نیز مورد بررسی قرار گرفته اند، در این مقاله باقی توابع مرجع را میشناسیم و در مقاله ی سوم با توابع جستجو آشنا میشویم، لذا پیش از خواندن این مقاله حتماً مقاله ی اول در مورد توابع جستجو و مرجع در اکسل را مطالعه نمایید.

همچنین اکیداً توصیه میشود پیش از مطالعه ی این نوشته، مقاله ی فرمول نویسی در اکسل را به منظور آشنایی با ساختمان و اجزای فرمول ها و همچنین آشنایی با انواع آرگومان ها و عملگرها در اکسل مطالعه نمایید.

به عنوان یادآوری، شکل زیر خلاصه ای از توابع جستجو و مرجع در اکسل را نشان میدهد:

ادامه توابع مرجع در اکسل

این دسته از توابع به منظور ارجاع به محدوده یا مشخصه ای از یک محدوده مورد استفاده قرا میگیرند، در مقاله ی قبلی توابع COLUMN/COLUMNS، ROW/ROWS، ADDRESS، AREAS، CHOOSE و INDEX را معرفی کردیم، در این مقاله قصد داریم بقیه توابع مرجع در اکسل را بشناسیم.

تابع INDIRECT در اکسل

با استفاده از این تابع میتوان به صورت غیر مستقیم سلول های اکسل را وارد فرمول نویسی ها کرد، مثلاً فرض کنید شما میخواهید از محتوای سلول C1 در فرمول استفاده کنید، میتوانید مستقیماً C1 را در فرمول به کار ببرید و یا مثلاً در سلول A1 بنویسید C1، سپس با استفاده از تابع INDIRECT میتوانید به محتوای سلول C1 از طریق اشاره به سلول A1 دست یابید. در ادامه با کاربرد این تابع آشنا خواهیم شد.

سینتکس (Syntax) تابع INDIRECT به صورت زیر است:

INDIRECT(ref_text,a1)

در عبارت بالا، آرگومان اول ارجاع به یک سلول است (نام یک سلول است)، سلولی که در این آرگومان نام آن ذکر شده باید حاوی آدرس یک سلول دیگر (سلولی که قرار است در فرمول مورد استفاده قرار گیرد) با فرمت متداول A1 یا R1C1 باشد، همینطور از مبحث نامگذاری سلول ها میدانیم که علاوه بر فرمت های فوق هر سلول میتواند یک نام اختصاصی هم داشته باشد که میتواند در فرمول ها مورد استفاده قرار گیرد.

خروجی تابع INDIRECT همواره برابر با محتوای سلولی است که در نهایت به آن ارجاع داده شده است، یعنی محتوای سلولی که نام آن در سلول آرگومان اول این تابع نوشته شده است.

آرگومان دوم، یک آرگومان Boolean و اختیاری است. این آرگومان تعیین میکند که چه نوع آدرس دهی در سلول ارجاع داده شده در آرگومان اول مورد استفاده قرار گرفته است. اگر True یا یک باشد یا نادیده گرفته شود یعنی آدرس دهی به فرمت متداول A1 میباشد و اگر False یا صفر باشد یعنی آدرس دهی به فرمت R1C1 است. در آدرس دهی ها به فرمت R1C1 ارجاعات خود را حتماً با دقت چک کنید زیرا فرمول نویسی با این نوع آدرس دهی به دقت بیشتری احتیاج دارد.

اگر محتوای آرگومان اول تابع INDIRECT به یک سلول نامعتبر یا به سلولی در فایل اکسلی دیگر که بر روی سیستم باز نیست ارجاع دهد خروجی تابع برابر با خطای #REF! خواهد بود.

به مثال زیر دقت کنید:

آرگومان اول تابع INDIRECT میتواند یک رشته باشد که به آدرس یک سلول ارجاع میدهد، در مثال های بالا، به فرمول آخر دقت کنید، در این مثال در سلول A5 عدد ۵ قرار گرفته که در کنار کاراکتر B تشکیل B5 را میدهد که در واقع ارجاع به سلول B5 میباشد.

تابع INDIRECT در اکسل در جایی کاربرد دارد که قرار است سلول های استفاده شده در فرمول ها تغییر کنند، در این حالت شما میتوانید بدون اینکه فرمول خود را عوض کنید در هر زمان که خواستید سلول های استفاده شده در فرمول ها را عوض کنید.

تابع TRANSPOSE در اکسل

همانطور که از نام این تابع انتظار دارید، تابع TRANSPOSE یک محدوده ی عمودی را افقی کرده و یا یک محدوده ی افقی را عمودی میکند. سینتکس این تابع که برای آرایه ها و محدوده های اکسل میتواند مورد استفاده قرار گیرد به صورت زیر است:

TRANSPOSE (Array or Range)

تنها آرگومان این تابع آدرس محدوده یا آرایه ای است که میخواهیم Transpose شود.

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

تابع HYPERLINK در اکسل

با استفاده از این تابع میتوان یک لینک (Link)، میانبر (Shortcut) یا پرش (Jump) به یک مدرک (Document) ذخیره شده در سیستم یا شبکه داخلی (Network Server, Intranet) شما یا اینترنت ایجاد کرد که با کلیک بر روی آن لینک مدرک مورد نظر باز شود.

سینتکس تابع HYPERLINK در اکسل به صورت زیر است:

HYPERLINK(link_location,friendly_name)

این تابع دو آرگومان دارد، آرگومان اول در واقع لینک فایل (شامل مسیر و نام فایل) مورد نظر است که به صورت یک رشته برای تابع تعریف میشود این لینک میتواند آدرس یک محدوده از یک فایل اکسل باشد و یا یک Bookmark در یک فایل Word باشد، همچنین این لینک میتواند آدرس یک صفحه اینترنتی یا اینترانتی (Uniform Resource Locator or URL) یا مسیر یک UNP (Universal Naming Convention) بر روی یک Server باشد.

آرگومان اول یا link_Location تابع HYPERLINK میتواند یک رشته قرار گرفته در Quotation Mark (“) یا آدرس یک سلول که مسیر مورد نظر به عنوان یک رشته متنی در آن نوشته شده است، باشد.

آرگومان دوم متن مورد نظر برای نشان داده شدن به کاربر میباشد که در سلول حاوی فرمول نمایش داده میشود و با خواندن این متن کاربر متوجه میشود که با کلیک کردن بر روی لینک چه فایلی باز خواهد شد. به این متن Jump Text میگوییم، در صورتی که کاربر آرگومان دوم را خالی بگذارد، لینک نوشته شده در آرگومان اول به عنوان Jump Text نمایش داده خواهد شد.

جامپ تکست (Jump Text) میتواند یک نام، عدد و یا آدرس یک سلول حاوی Jump Text مورد نظر باشد. حتی اگر آرگومان دوم یک پیام خطا باشد، همان پیام خطا به عنوان Jump Text نمایش داده خواهد شد.

در ادامه به ذکر چند مثال میپردازیم:

• باز کردن یک فایل بر روی اینترنت:

HYPERLINK(“http://example.microsoft.com/report/budget report.xlsx”, “Click for report”)

• رفتن به یک سلول مشخص در یک فایل اکسل:

HYPERLINK(“[http://example.microsoft.com/report/budget report.xlsx]Annual!F10”, D1)

در مثال بالا نام فایل اکسل مورد نظر budget report، نام شیت Annual نام سلول F10 میباشد.

• باز کردن یک فایل در سیستم:

HYPERLINK(“D:\FINANCE\1stqtr.xlsx”, H10)

• لینک به یک سلول در همان فایل اکسل:

HYPERLINK(“[Budget.xlsx]September!E56”, E56)

• اگر شما به صورت زیر به سلول A1 لینک ایجاد کنید، هر چیزی که در A1 نوشته شود در سلول حاو فرمول نشان داده خواهد شد:

HYPERLINK($A$1)

برای انتخاب سلولی که در آن تابع HYPERLINK نوشته شده است بر روی سلول کلیک کرده و تا وقتی علامت نشانگر موس به بعلاوه (+) تبدیل شود کلید موس را پایین نگه دارید، به اینصورت میتوانید بدون اینکه لینک مورد نظر باز شود سلول حاوی فرمول را انتخاب کنید.