در این آموزش 4 روش مختلف را برای داشتن لیست شیتهای اکسل یاد خواهیم گرفت که دسترسی سریع تری به شیتها داشته باشیم .
فهرست محتوا
لیست شیتهای اکسل با فرمول نویسی
روشی که ما پیشنهاد می کنیم ایجاد کرده یک محدوده نام در اکسل می باشد که سپس با یک فرمول ساده بتوانیم نام شیتها را استخراج کنیم و برای این منظور می توانیم مراحل زیر را انجام دهیم :
- با زدن کلیدهای Ctrl + Shift + g متوجه می شویم که چند شیت در فایل ما وجود دارد
- در ستون A از 1 تا تعداد شیتها شماره گذاری می کنیم
- با زدن Ctr+ + F3 پنجره Name Manager را باز میکنیم
- یک محدوده جدید با نام SheetsName ایجاد می کنیم
- فرمول زیر را به آن اختصاص می دهیم
=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")محدوده نام را بسته و در ستون B فرمول زیرا را می نویسیم
=INDEX(SheetNames,A1)
فرمول بالا را تا جایی که در ستون A اطلاعات داریم امتداد می دهیم.
مراحل انجام کار با استفاده از پاورکوئری
این آموزش رایگان از آفیس باز در واقع به دو قسمت اصلی تقسیم می شود ، قسمت اول ایجاد لیست نام شیتهای اکسل به صورت خودکار می باشد. تا لیست شیتهای اکسل را داشته باشیم.
و قسمت دوم ایجاد هایپرلینک در اکسل برای پرش به شیتها و برگشتن به شیت INDEX تنها با یک کلیک.
بخش اول لیست شیتهای اکسل پاورکوئری
برای این منظور ما باید از Power Query استفاده کنیم تا کار را به سریعترین و دقیق ترین شکل انجام دهیم. مزیت این کار هم این است که :
- اگر نام شیتها تغییر کند ، نام آن در لیست هم تغییر می کند.
- اگر شیتی حذف و یا اضافه شود ، تغییرات در لیست هم اعمال می شود .
فرض می کنیم فایلی مشابه تصویر بالا داریم که حاوی 6 شیت مختلف است ، برای اینکه بتوانیم لیستی از نام شیتها در یک شیت جدید داشته باشیم باید مراحل ذیل را طی کنیم .
- به تب DATA بریم و از تب Get Data از گزینه From File گزینه اول را انتخاب کنید.
- در پنجره باز شده ، فایل اکسل مد نظر خود را انتخاب کنید ( فایل جاری )
- در پنجره بعدی نام فایل را انتخاب کرده و گزینه Transform Data را بزنید.
- در محیط پاور کوئری ، ستونهای اضافی را حذف کنید تا فقط نام شیتها باقی بماند.
- گزینه Close & Load را بزنید .
- یک شیت به اکسل شما اضافه میشود.
- روی نام آن دابل کرده و نام آن را به INDEX تغییر دهید .
- محدوده ایجاد شده انتخاب کنید و در تب Query روی Edit کلیک کنید.
- باز هم وارد کوئری خواهید شد و اینجا فیلتر را انجام دهید تا نام INDEX از لیست حذف شود .
- گزینه Close & Load را بزنید .
با انجام این مراحل لیست خودکار از نام شیتها را با موفقیت ایجاد کرده اید حالا باید به سراغ ایجاد هایپرلینک اکسل برویم.
لیست نام شیتها با کدهای VBA
روش دیگری که نام شیتهای اکسل را به ما نمایش می دهد استفاده از کدنویسی می باشد که کدهای آن در مطلب امکانات ویژه اکسل آورده شده است و در ویدیو اول این آموزش هم به آن اشاره شده است .
بخش دوم ایجاد هایپرلینک در اکسل
لیست را ایجاد کرده ایم حالا باید کاری کنیم که با کلیک روی نام شیتها به آن شیتها هدایت شویم که باید از هایپرلینک در اکسل استفاده نمائیم برای این منظور اقدامات زیر را انجام دهید.
- در شیتی که در مراحل قبل ایجاد کرده اید یک ستون کمکی ایجاد کنید.
- فرمول زیر را در اولین سلول آن بنویسید ، مقابل اولین نام از لیست.
=HYPERLINK("#'"&[@Name]&"'!A1",[@Name])این کار باعث می شود با کلیک روی نامها به آن شیتها و سلول A1 آن شیتها منتقل شوید .
