ساختار حرفهای اکسل؛ چگونه یک شیت مخفی برای فرمولها و دادههای پشتیبان بسازیم؟
اگر فایلهای اکسل شما چندین جدول مرجع، فرمول، داده خام یا اطلاعات کمکی دارند، لازم نیست همه این موارد را در معرض دید کاربر قرار دهید. یک روش کاربردی این است که یک شیت پشتیبان یا Backend ایجاد کنید و دادههایی را که برای عملکرد فایل لازم هستند اما نیازی نیست همیشه دیده شوند، در آن نگه دارید.
این شیت میتواند محل نگهداری جدولهای جستوجو، دادههای مورد استفاده در PivotTable، مقادیر ثابت فرمولها، یادداشتهای داخلی و حتی دادههای قدیمی باشد. نکته مهم این است که مخفی کردن شیت با محافظت امنیتی یکسان نیست؛ شیت معمولی مخفیشده را میتوان بهسادگی دوباره نمایش داد. مایکروسافت نیز تأکید میکند که دادههای شیت مخفی همچنان از سایر شیتها قابل ارجاع هستند.
چرا بهتر است در اکسل یک شیت پشتیبان داشته باشیم؟
در فایلهای ساده معمولاً همه دادهها در چند شیت قابل مشاهده قرار میگیرند. با بزرگتر شدن فایل اما این روش باعث شلوغی محیط کار و افزایش احتمال تغییر اشتباه اطلاعات مرجع میشود.
شیت پشتیبان این امکان را میدهد که دادههای فنی را از اطلاعاتی که کاربر باید ببیند جدا کنید. برای نمونه میتوانید جدولهای مورد استفاده در XLOOKUP را در این شیت قرار دهید و در شیت اصلی فقط نتیجه نهایی را نمایش دهید. XLOOKUP برای جستوجوی یک مقدار در یک محدوده و برگرداندن مقدار متناظر از محدوده دیگر طراحی شده است.
شیت پشتیبان چه تفاوتی با شیت معمولی دارد؟
شیت پشتیبان از نظر فنی همان Worksheet معمولی اکسل است اما هدف آن نمایش مستقیم اطلاعات به کاربر نیست. میتوانید آن را با روش عادی Hide کنید یا در نسخه دسکتاپ اکسل با VBA به حالت xlSheetVeryHidden ببرید.
در حالت عادی کاربر میتواند از گزینه Unhide برای نمایش دوباره شیت استفاده کند. در حالت Very Hidden شیت در فهرست Unhide ظاهر نمیشود و برای نمایش مجدد آن باید ویژگی Visible شیت از طریق محیط VBA تغییر کند.
چگونه یک شیت را در اکسل مخفی کنیم؟
سادهترین روش برای مخفی کردن شیت این است که روی تب آن در پایین پنجره اکسل کلیک راست کنید و گزینه Hide را انتخاب کنید. شیت بلافاصله از فهرست تبها حذف میشود اما اطلاعات آن همچنان در فایل باقی میماند.
روش دیگر از طریق نوار ابزار اکسل انجام میشود و برای زمانی مناسب است که بخواهید دستور Hide را از مسیر رسمی برنامه اجرا کنید. مایکروسافت این مسیر را در نسخههای جدید اکسل نیز ارائه میکند.
Home → Format → Hide & Unhide → Hide Sheet
چگونه شیت مخفی را دوباره نمایش دهیم؟
برای نمایش دوباره شیت معمولی روی یکی از تبهای قابل مشاهده کلیک راست کنید و Unhide را بزنید. سپس شیت مورد نظر را از فهرست انتخاب کنید و OK را بزنید.
اگر شیتی با VBA به حالت Very Hidden رفته باشد در پنجره Unhide نمایش داده نمیشود. در چنین شرایطی باید از محیط VBA ویژگی Visible آن را به xlSheetVisible تغییر دهید.
چگونه شیت Very Hidden در اکسل بسازیم؟
اگر فایل را با افراد دیگر به اشتراک میگذارید و نمیخواهید شیت پشتیبان در فهرست معمولی Unhide دیده شود میتوانید از قابلیت Very Hidden استفاده کنید.
این قابلیت از طریق محیط Visual Basic Editor در نسخه دسکتاپ اکسل قابل تنظیم است. شیتی که ویژگی xlSheetVeryHidden داشته باشد از دستور معمولی Unhide قابل نمایش نیست.
پس از باز شدن محیط VBA میتوانید از Project Explorer شیت مورد نظر را انتخاب کنید و در پنجره Properties مقدار Visible را روی 2 - xlSheetVeryHidden قرار دهید.
چگونه شیت Very Hidden را دوباره قابل مشاهده کنیم؟
برای برگرداندن شیت باید دوباره وارد Visual Basic Editor شوید و شیت مورد نظر را در Project Explorer انتخاب کنید. سپس در Properties مقدار Visible را از xlSheetVeryHidden به xlSheetVisible تغییر دهید.
اگر از VBA در فایل استفاده میکنید باید هنگام ذخیرهسازی فرمت مناسب فایل را نیز در نظر بگیرید. فایلهایی که حاوی کد VBA هستند معمولاً باید در قالبی مانند .xlsm ذخیره شوند تا کدهای آنها حفظ شوند.
جدولهای جستوجو را در شیت پشتیبان نگه دارید
یکی از کاربردهای مهم شیت پشتیبان ذخیره جدولهایی است که فرمولهای جستوجو به آنها وابسته هستند. به عنوان مثال میتوانید فهرست کد محصولات، نامها، قیمتها یا دستهبندیها را در شیت Backend قرار دهید و در شیت اصلی فقط نتیجه را نمایش دهید.
این کار باعث میشود جدول مرجع فضای قابل مشاهده شیت اصلی را اشغال نکند و احتمال تغییر تصادفی آن نیز کمتر شود. در صورت استفاده از XLOOKUP حتی میتوانید جدولهای مرجع را از محل محاسبات جدا نگه دارید. مایکروسافت XLOOKUP را برای جستوجو در یک محدوده و بازگرداندن مقدار متناظر معرفی میکند.
استفاده از XLOOKUP با جدول مرجع مخفی
فرض کنید در شیت Backend جدولی دارید که کد هر محصول را به نام و قیمت آن مرتبط میکند. شیت اصلی میتواند کد محصول را دریافت کند و با XLOOKUP نام یا قیمت مربوط به آن را نمایش دهد.
برای پروژههای بزرگتر بهتر است داده مرجع را به شکل Excel Table نیز سازماندهی کنید. Structured Referenceها باعث میشوند فرمولها به جای وابستگی مستقیم به آدرسهای ثابت سلول از نام جدول و ستون استفاده کنند و با تغییر اندازه جدول مدیریت داده آسانتر شود.
دادههای PivotTable را از شیت اصلی جدا کنید
اگر فایل شما برای تحلیل داده از PivotTable استفاده میکند بهتر است داده خام همیشه در معرض دید کاربر نباشد. یک شیت پشتیبان میتواند محل نگهداری دادههای خام باشد و شیت اصلی فقط PivotTable و نمودارهای نهایی را نمایش دهد.
این ساختار مخصوصاً زمانی مفید است که داده خام صدها یا هزاران ردیف داشته باشد. با جدا کردن منبع داده از صفحه گزارش میتوانید فضای بیشتری برای PivotChart، فیلترها و سایر عناصر تحلیلی داشته باشید.
چرا مخفی کردن داده خام PivotTable کاربردی است؟
مخفی کردن داده خام به معنی حذف آن نیست. PivotTable همچنان میتواند از داده موجود در شیت دیگر استفاده کند و شما میتوانید هنگام نیاز داده منبع را ویرایش یا بهروزرسانی کنید.
اگر دادهها ساختار مشخصی داشته باشند میتوان از Excel Table نیز به عنوان منبع استفاده کرد تا اضافه شدن رکوردهای جدید مدیریت سادهتری داشته باشد. همچنین میتوانید منبع PivotTable را از مسیر مربوط به تغییر Data Source بررسی کنید.
مقادیر ثابت فرمولها را در شیت مخفی قرار دهید
گاهی فرمولهای یک فایل به اعدادی وابسته هستند که مرتب تغییر نمیکنند. نرخ مالیات، درصد کارمزد، ضریب تبدیل یا یک مقدار مرجع سالانه نمونههایی از این نوع دادهها هستند.
به جای اینکه چنین اعدادی را مستقیماً داخل فرمول بنویسید بهتر است مقدار آنها را در سلول مشخصی از شیت Backend نگه دارید. این روش باعث میشود بعداً بتوانید مقدار را تغییر دهید بدون اینکه مجبور باشید تکتک فرمولها را ویرایش کنید.
استفاده از نامگذاری سلولها برای فرمولهای خواناتر
برای مدیریت بهتر چنین مقادیری میتوانید به سلول یک نام مشخص اختصاص دهید. مثلاً اگر یک سلول حاوی نرخ مالیات است میتوانید نام آن را Tax بگذارید و سپس در فرمول از همین نام استفاده کنید.
این روش خوانایی فرمول را افزایش میدهد و پیدا کردن مقدار مرجع را نیز سادهتر میکند. اکسل امکان مدیریت و پیدا کردن Named Rangeها را از طریق ابزارهای مربوط به Name Manager و Go To فراهم میکند.
از شیت Backend برای فرمولهای چند شیتی استفاده کنید
در فایلهایی که برای هر ماه، سال یا بخش یک شیت جداگانه دارند ممکن است لازم باشد یک سلول مشابه از چند شیت جمع یا تحلیل شود. قابلیت 3D Reference اکسل برای همین نوع ساختارها کاربرد دارد.
برای نمونه فرمول زیر میتواند مقدار سلول D2 را از تمام شیتهای قرارگرفته بین دو شیت مشخص جمع کند:
=SUM('فروردین:Backend'!D2)
در 3D Reference محدوده شیتها بر اساس موقعیت آنها در فایل تعیین میشود. بنابراین اگر شیتهای جدیدی در محدوده مشخصشده قرار بگیرند میتوانند در محاسبه نیز لحاظ شوند. مایکروسافت این قابلیت را برای محاسبه یک سلول یا محدوده یکسان در چند Worksheet مستند کرده است.
جایگاه شیت Backend در 3D Reference اهمیت دارد
در 3D Reference ابتدا و انتهای محدوده شیتها اهمیت زیادی دارد. اگر شیت جدیدی بین دو شیت تعیینشده قرار بگیرد اکسل میتواند آن را در محاسبات وارد کند. در مقابل اگر شیتی از این محدوده خارج شود دیگر در نتیجه فرمول لحاظ نمیشود.
به همین دلیل اگر از این روش استفاده میکنید بهتر است ساختار شیتها را با دقت مدیریت کنید و نامگذاری و ترتیب تبها را بیدلیل تغییر ندهید.
اطلاعات قدیمی و یادداشتهای داخلی را جدا نگه دارید
شیت پشتیبان فقط برای فرمولها نیست. میتوانید اطلاعات قدیمی که فعلاً در محاسبات استفاده نمیشوند اما احتمال دارد در آینده به آنها نیاز پیدا کنید نیز در آن نگه دارید.
برای مثال دادههای سالهای گذشته، توضیح فرمولها، مقادیر مرجع یا دستورالعملهای داخلی میتوانند در یک یا چند شیت Backend قرار بگیرند. البته بهتر است دادههای غیرضروری را بیدلیل به فایل اضافه نکنید تا حجم و پیچیدگی Workbook افزایش پیدا نکند.
یادداشتهای مهم را در شیت پشتیبان سازماندهی کنید
اگر فایل توسط چند نفر استفاده میشود میتوانید بخشی از شیت پشتیبان را به توضیح فرمولها، معنی ستونها، مقادیر مرجع و نکات نگهداری اختصاص دهید.
با این حال بهتر است اطلاعاتی که کاربر عادی باید هنگام کار با فایل ببیند در یک صفحه اصلی یا راهنمای قابل مشاهده قرار گیرد. مخفی کردن تمام دستورالعملها باعث میشود کاربران برای فهمیدن نحوه استفاده از فایل مجبور به جستوجوی شیتهای مخفی شوند.
شیت مخفی امنیت واقعی ایجاد نمیکند
یکی از نکات مهم این است که Hide یا حتی Very Hidden را نباید جایگزین روشهای امنیتی واقعی دانست. شیت معمولی مخفیشده از طریق دستور Unhide قابل نمایش است و Very Hidden نیز با دسترسی مناسب به محیط VBA قابل تغییر است.
بنابراین اطلاعاتی مانند رمز عبور، کلیدهای دسترسی یا دادههای فوقمحرمانه را صرفاً به دلیل مخفی بودن شیت در آن ذخیره نکنید. هدف اصلی Backend بیشتر سازماندهی فایل، کاهش شلوغی و جلوگیری از ویرایش تصادفی دادههای مرجع است. مایکروسافت نیز تصریح میکند که داده شیت مخفی همچنان میتواند در فرمولها و ارجاعها استفاده شود.
صفحه اصلی را برای کاربران قابل مشاهده نگه دارید
یک ساختار مناسب میتواند شامل یک شیت Home یا Dashboard در ابتدای فایل باشد. در این صفحه میتوانید توضیح کوتاهی درباره فایل، نحوه استفاده، وضعیت دادهها و مسیر دسترسی به بخشهای مختلف را قرار دهید.
در چنین ساختاری کاربر با باز کردن فایل ابتدا با یک صفحه مرتب مواجه میشود و لازم نیست با جدولهای خام، فرمولهای کمکی یا دادههای فنی روبهرو شود. در مقابل شیت Backend در پشت صحنه وظیفه تأمین داده و اجرای بخشی از منطق فایل را بر عهده دارد.
جمعبندی
ساختن یک شیت Backend مخفی یکی از روشهای ساده برای حرفهایتر کردن فایلهای اکسل است. جدولهای جستوجو، دادههای خام PivotTable، مقادیر ثابت فرمولها، Named Rangeها، دادههای قدیمی و یادداشتهای فنی را میتوان در این بخش نگهداری کرد تا شیتهای اصلی خلوتتر و قابل استفادهتر باشند.
برای فایلهای شخصی Hide معمولی معمولاً کافی است. اگر فایل را با دیگران به اشتراک میگذارید میتوانید برای شیتهای فنی از Very Hidden استفاده کنید. با این حال هیچکدام از این روشها نباید به عنوان راهکار امنیتی برای نگهداری اطلاعات محرمانه در نظر گرفته شوند.
در نهایت ترکیب شیت اصلی برای کاربر، شیتهای گزارش و یک Backend منظم میتواند ساختار فایلهای بزرگ اکسل را بسیار مرتبتر کند. استفاده از Table، XLOOKUP، Named Range و 3D Reference نیز کمک میکند این ساختار در پروژههای پیچیدهتر قابل مدیریت باقی بماند.
اینتوتک