کلیک‌ساز - هایپرلینکی از نام شیت‌ها درون اکسل

هایپرلینکی از نام شیت‌ها درون اکسل

موضوعات : آفیس

در این مقاله سعی بر آن است چند ترفند و راه‌کار برای کار با فایل‌های اکسل آموزش داده‌شود، شاید برخی را بدانید و شاید برای برخی دیگر راه بهتری سراغ داشته باشید(که اگر آن را برای سایت بفرستید تا با نام خودتان در سایت اضافه شود ممنون خواهم شد).

یک فایل اکسل دارید، شامل تعداد بسیار بسیار زیادی شیت. یافتن شیت مورد نظرتان در میان آن‌همه شیت مشکل‌ساز است. تعداد شیت‌ها بحدی زیاد است که با چشم بدنبال آن گشتن سخت است و بخصوص اسکرول کردن تب نام شیت‌ها کسالت‌بار، اگر امکانی باشد که لیست تمام شیت‌ها دردسترس باشد و با زدن کلیک روی آن‌ها به شیت موردنظر رفت بسیار عالی‌ست. روش‌هایمختلفی در این‌باره وجود دارد یکی استفاده از ماکرو(که نیاز است فایل اکسل از نوع Macro Enable باشد) و یا از دستورات و فرمول‌های درونی اکسل که ساده‌تر است.

برای اینکه بتوانید هایپرلینکی از نام شیت‌ها داشته باشید که با زدن روی هر یک به آن شیت بروید نخست باید نام شیت‌ها را بدست آورید اما برای این‌ کار راه‌های مختلفی وجود‌دارد، می‌توانید از یک ماکرو استفاده‌کنید و یا از یک متغیر Name تعریف‌شده. استفاده از ماکرو مزایا و معایب خاص خودش را دارد، از مزایای ماکرو عبارتنداز:

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

اما معایب آن:

باید فایل اکسل شما قابلیت اجرای ماکرو را داشته باشد(Excel Macro Enabled).
درصورت بلد نبودن نحوه دستورنویسی در VBA امکان استفاده نیست.
هر بار که فایل و تعداد شیت‌ها تغییر می‌کند باید برنامه دوباره اجراشود.

در فایل پیوست ماکرو نیز قرارداده شده، اما در این‌جا فقط دستورات داخلی خود اکسل را توضیح می‌دهیم.

یک فایل اکسل ایجادکنید، سپس تعدادی شیت با نام‌های گوناگون به آن بیفزایید. به تب FORMULAS رفته، گزینه Name Manager را انتخاب کنید(با زدن CTRL+F3 هم می‌توانید این گزینه را فعال کنید).

یک نام جدید ایجاد کنید(ListSheets) و در بخش Refers To مقدار زیر را قرار دهید:

=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")

دستور GET.WORKBOOK(1) نام تمام شیت‌ها(نتیجه از نوع آرایه می‌باشد) را به فرمت [workbookname]sheetname زیر برمی‌گرداند، و دستور REPLACE بخش نام ورک‌بوک را حذف می‌کند، دکمه OK را بزنید. حال درون یکی از شیت‌ها رفته در سلول A1 مقدار زیر را قراردهید:

=INDEX(ListSheets,ROWS($ZZ$1:$ZZ1))

این سلول را به تعداد شیت‌ها گرفته و بکشید. اکنون نام هر شیت در یک ردیف دیده‌می‌شود. حال هایپرلینک، به سلول B1 رفته و مقدار زیر را در آن قرار دهید:

=HYPERLINK("#" & A1 & "!A1",A1)

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

استفاده از ماکرو:

نخست باید بدانید فایل مورد نیاز باید از نوع Macro Enable باشد یعنی پسوند آن Xlsm باشد.

(بدلیل استفاده از دارک‌مد در ویندوز عکس بالایی بصورت تاریک است) باید تب Developer هم برای‌تان فعال باشد اگر نبود به محل زیر بروید:

Options > Customize Ribbon

در بخش Main Tabs گزینه Developer تیک بزنید تا تب Developer ظاهر شود.

حال با زدن دکمه Ctrl+F11 وارد بخش برنامه‌نویسی می‌شوید و از قسمت Project - VBAProject روی ThisWorkbook دو بار کلیک کنید تا بخش برنامه نویسی باز شود

قطعه کد زیر را وارد کنید:

Sub ListSheets()
    Dim ws As Worksheet
    Dim i As Integer
    Sheets("Sheet1").Range("A:A").Clear
    i = 1
    For Each ws In Worksheets
         Sheets("Sheet1").Cells(i, 1) = ws.Name
         i = i + 1
    Next ws
End Sub

با زدن F5 برنامه را اجرا کنید. در Sheet1 لیستی از نام شیت‌ها مشاهده می‌کنید. اگر بخواهید با بازشدن برنامه نام شیت‌ها دوباره بررسی و ایجاد شود چه بایدکرد؟

بروزرسانی:چهارشنبه 4 مرداد ماه 1402

دستور GET.WORKBOOK دارای دو پارامتر مختلف می‌باشد:

GET.WORKBOOK(type_num, name_text)

:type_num عددی است که مشخص میکند استخراج چه داده‌ای را از تابع نیاز داریم تا دریافت کنیم،

:name_text نام یک فایل اکسل باز در حافظه(اگر مقداری ارسال نگردد پیش فرض آن همان اکسل فایل باز می‌باشد که در حال کار با آن هستیم)

در زیر به برخی از پارامترها و مقادیری که برمی‌گردانند اشاره می‌شود:

GET.WORKBOOK(1)	نام همه شیت های اکسل فایل جاری را برمیگرداند(به صورت یک آرایه از نام شیت ها)
GET.WORKBOOK(1,"Sale.XLSX")	نام همه شیت های اکسل فایل Sale.XLSX را برمیگرداند(در صورت باز بودن این فایل)
GET.WORKBOOK(3,"Sale.XLSX")	نام شیت‌های انتخاب شده از فایل باز Sale.XLSX برمیگرداند(به صورت یک آرایه از نام شیت ها).
GET.WORKBOOK(4)	تعداد شیت های فایل جاری را نشان میدهد.
GET.WORKBOOK(14)	مقدار برگشتی اگر true باشد یعنی ساختار ورک‌بوک(workbook structure) حفاظت شده(protected) هست در غیر آن مقدار false برمی‌گرداند(به عکس زیر توجه کنید)
GET.WORKBOOK(16,"Sale.XLSX")	نام فایل اکسل Sale.XLSX را برمی‌گرداند یعنی "Sale.XLSX"، اگر نام فایل خالی باشد نام فایل در حال کار را بر‌میگرداند(توجه کنید نام فایل بدون فولدر قرار گیری آن می‌باشد).
GET.WORKBOOK(17)	ریداونلی(readonly) بودن فایل جاری را مشخص می‌کند.
GET.WORKBOOK(17,"Sale.XLSX")	ریداونلی(readonly) بودن فایل Sale.XLSX را مشخص می‌کند.
GET.WORKBOOK(35)	نام مالک و سازنده(owner) فایل را نشان می‌دهد.
GET.WORKBOOK(38)	نام شیت فعال را نشان می‌دهد.