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

تمامی داد‌ه‌ها در دنیای کامپیوتر و البته اکسل در حافظه اصلی (یا همان RAM) باید باشند تا بتوانیم با آن‌ها کار کنیم و ما قادریم تا هر چیزی را که می‌خواهیم در این حافظه ذخیره کنیم و بعدا بر روی آن کارهایی را انجام دهیم.

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

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

برای تصور بهتر شکل زیر را در نظر بگیرید که ما ۲ جعبه داریم به نام‌های atal و matal و مقدار 17 و 57 را در آن‌ها ذخیره کرده‌ایم:

  atal          matal
+------+      +------+
|  17  |      |  57  |
+------+      +------+

باید بدانید که :
+ این دو جعبه در حافظه اصلی کامپیوتر هستند.
+ محتوای این دو جعبه قابل تغییر هستند.
+ ما می‌توانیم در داخل این دو جعبه هر چیزی را ذخیره کنیم که فعلا ۲ عدد ساده است.

و البته هر گاه از کامپیوتر بخواهیم که برای ما چیزی مانند atal + matal را محاسبه کند،  کامپیوتر به حافظه نگاه می‌کند و مقادیری که در این جعبه‌ها ذخیره شده است را برای ما خواهد آورد و البته اگر آنها را نیابد خطایی به ما می‌دهد که این نام‌هایی که گفتی در قسمتی از حافظه که در اختیار من است، یافت نشد و د ر اکسل خطای !Name# و یا در زبان پایتون خطای زیر به ما نمایش داده می‌شود:

NameError: name 'atal' is not defined

ساختن و ذخیره این نام‌ها در اکسل با Nameها انجام می‌شود.

ساخت و مقدار دهی یک نام در اکسل 

در دنیای اکسل می‌توانیم یک نام جدید تعریف کنیم و مقداری را به آن بدهیم که این کار از تب Formula‌ و گزینه Define Names قابل انجام است:

 توجه داشته باشیم که یک Name باید ویژگی‌های زیر را داشته باشید:

+ باید آن نام یکتا باشد و تکراری قبول نیست.
+ قواعدی مانند عدم وجود فاصله در نام را باید رعایت کنید وگرنه خطا می‌گیرید.

و مقدار آن نام در دنیای اکسل می‌تواند:

+ یک فرمول باشد.
+ یک مقدار ثابت (عدد یا متن) باشد.
+ آدرس یک سلول و یا محدوده‌ای از سلول‌ها باشد.
+ یک نام دیگر باشد.

در پنجره زیر یک Name به نام my_birth_date تعریف شده است که مقدار آن برابر 14020517 است:

حال در هر سلول اکسل می‌توانیم از این نام استفاده کنیم تا روی مقدارش محاسبه‌ای را انجام دهیم مثلا بنویسیم:

= my_birth_date - 5

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

🧿 شیت جادویی:
فایل پیوست این مقاله را دانلود کنید و به دستور العمل آن در شیت جادویی عمل کنید.

 روش سریع ایجاد یک نام در اکسل

اگر می‌خواهید برای سلول‌های اکسل نام جدیدی را بگذارید، کافی است که آن‌ها را انتخاب کنید و سپس در قسمت Name Box‌ که در کنار نوار فرمول اکسل است، نام دلخواهتان را تایپ کنید و Enter را بزنید. بلافاصله این نام ایجاد می‌شود و مقدارش برابر آدرس آن سلول‌خواهد بود:

در تصویر بالا یک Name به نام capacity ایجاد می‌شود که مقدارش برابر است با B2:B4 و در هر فرمول اکسل می‌توانیم از این نام استفاده کنیم مثلا می‌خواهیم جمع کل ظرفیت کشتی‌های شرکتمان را محاسبه کنیم:

=SUM(capacity)

جالب است بدانید که:
می‌توانیم برای سلول A1 یک نام دلخواه بگذاریم. مثلا نام آن را vat_rate بگذاریم و از این پس این سلول هم A1 است و هم یک نام «مستعار» به نام vat_rate دارد. به این نام متسعار در دنیای برنامه نویسی alias‌ می‌گویند.

پرسش ۱) چرا از Nameها در اکسل استفاده می‌شود؟

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

خوانایی فرمول‌ها در دنیای برنامه نویسی بسیار مهم است و به همین دلیل در «ذن زبان پایتون» بر روی خوانایی فرمول تاکید شده است و در اصل هفتم داریم Readability counts.

پرسش ۲) چرا همگان از Nameها در اکسل استفاده نمی‌کنند؟

متاسفانه کاربران اکسل به این امر (یعنی خوانایی فرمول) توجه ندارند و بر حسب عادت همیشه همان آدرس سلول‌ها را در سلول می‌نویسند.
یکی از ریشه‌های این امر آن است که در دبیرستان و یا احتمالا در دانشگاه با هیچ زبان برنامه نویسی آشنا نشده‌اند و نمی‌دانند که Nameهای اکسل همان چیزی است که در دنیای برنامه نویسی به آن Variables می‌گویند!

استفاده از Table‌ و Nameهایی که خود اکسل می‌سازد

در اکسل 2007 امکانی به وجود آمد به نام Table و هر Table به صورت پیش فرض یک Name دارد که این نام قابل تغییر است و در فرمول نویسی می‌توانیم از آنها استفاده کنیم. اگر با Tableها آشنا نیستید «ویدئوی آموزش ساخت Table در اکسل» را مشاهده کنید.

در تصویر زیر یک Table که نام آن ship است را می‌بینید و می‌توانیم از این Name و البته زیر مجموعه آن که نام ستون‌های این جدول است مطابق با این تصویر در فرمول نویسی استفاده کنیم:

مدیریت Nameها در پنجره Name Manager

برای مشاهده، حذف و یا تغییر Nameهایی که در اکسل تعریف شده است در تب Formulas ، گزینه Name Manager را داریم که با زدن آن پنجره‌ای مانند شکل زیر باز می‌شود:

در این پنجره این چیزها را می‌بینید:

+ سه دکمه New, Edit, Delete که به ترتیب می‌توانید «یک نام جدید بسازید»، «ویرایش یک نام موجود» و «حذف یک نام»

+ لیست هم نام‌های موجود در این فایل را می‌بینید که پنج ستون دارد:
++ Name: نام را مشاهده می‌کنید.
++ Value: مقدار آن نام است که اگر یک محدوده باشد، فقط چند مقدار سلول‌های اول آن محدوده را نمایش می‌دهد.
++  Refers To: اگر آن نام به سلول‌ها و محدوده‌ها اشاره داشته باشد، آدرس آن‌ها را مشاهده می‌کنید.
++ Scope: میدان دید آن نام است و اگر workbook باشد یعنی آن نام در تمامی شیت‌های فایل قابل استفاده است و در غیر اینصورت (نام یک شیت خاص باشد) باشد یعنی آن نام فقط در آن شیت قابل استفاده است و در سایر شیت‌ها دیده نمی‌شود.
++ Comment: توضیحاتی است که در مورد آن نام برای خودتان و یا سایر استفاده کنندگان نوشته‌اید.

+ در قسمت پایین این پنجره Refers To را داریم که در اینجا می‌توانیم مقدار یا آدرسی که آن نام دارد را تغییر دهیم.

هشدارهای مهم:

💀 نام یک Table‌ قابل حذف و یا ویرایش از اینجا نیست.
💀 نام‌هایی که خطای !REF# دارند، یعنی مرجع آن حذف شده است و دیگر در حافظه دستگاه نیست. این نام‌ها را حتما حذف کنید زیرا فایل را کند می‌کنند!

چه مواقعی استفاده از Nameها در اکسل اجباری است؟

تا اینجا احتمالا برداشت شما در مورد Nameها آن است که چیز خوبی است اما استفاده از آن‌ها در اکسل اجباری نیست. در این بخش از این مقاله می‌خواهم به شما بگویم که در برخی از مواقع ما مجبوریم از Nameها استفاده کنیم و هیچ راه دیگری برای حل آن مسئله نداریم! و یا اگر هست من نمی‌دانم!

تا این لحظه بر اساس تجربه شخصی به سناریوی‌های زیر برخورد کرده‌ام که باید از Nameها استفاده کنیم:

الف) استفاده از Nameها در هنگام خواندن داده‌های سلول‌های یک شیت توسط Power Query
ب ) استفاده از Nameها برای تعریف محدوده چارت‌های که با تابع OFFSET داینامیک شده‌اند.
ج) برای تعریف ؟؟؟‌ در اکسل الزامی است.

واقعیت آن است که هدف اصلی من از نگارش این مقاله دقیقا مورد الف بوده است 😎 و در غیر اینصورت این مقاله را احتمالا نمی‌نوشتم. 

در خصوص مورد استفاده ب،  «آموزش ساخت یک داینامیک چارت در اکسل» را ببینید.

و توضیحاتم را در مورد ج در انتهای این مقاله و بخش «جو فروشان» بخوانید . 

پاورکوئری فقط داد‌ه‌ها را از Nameها می‌خواند 🚀

عجله نکنید! توضیح می‌دهم منظورم چیست ⚡ و البته هدف اصلی این مقاله هم گفتن این نکته است.
توجه کنید که پاورکوئری می‌تواند داده‌ها را از جاهای مختلفی بخواند از جمله:
۱) از همین فایل که کوئری را در آن ساخته‌ایم
۲) هر یک فایل اکسل دیگر

در حالت ۱ یعنی اگر پاورکوئری بخواهد داده‌هایی را از همین فایلی که در آن کوئری را می‌سازیم را بخواند، باید❗  آن محدوده یک Name داشته باشد و در اکثر موارد (یعنی بیش از ٪۹۰) ما محدوده‌ داده‌ها را Table می‌کنیم و پاورکوئری آن Table‌ را می‌خواند و نیازی به تعریف Nameها نیست.

حتی اگر محدوده داده‌ها را Table‌ نکنیم و با گزینه From Table/Range‌ پاورکوئری بخواهیم سلولهایی را بخوانیم که Table نیستند، خود پاورکوئری❕ به صورت اتوماتیک❗ بدون اجازه از شما❕  آن منطقه را Table‌خواهد کرد❗ تمام 😤

حال در برخی از موارد شما نمی‌خواهید که محدوده داده‌هایتان را Table‌ کنید زیرا Table‌ کردن باعث می‌شود که سلول‌هایی که در فرمت Merge‌ شده قرار دارند، از این حالت خارج شوند و ظاهر شیت شما با Table کردن بدشکل و بی نظم می‌شود. به همین دلیل در اینجا راهکار جایگزین استفاده از یک Name است.

شما آن محدوده را با یک Name تعریف می‌کنید و پاورکوئری می‌توانید هم اکنون آن محدوده را بدون آنکه Table شده باشد، را از «همین فایل» بخواند.

روش اجرا:
۱) ابتدا سلول‌های مورد نظرتان را انتخاب کنید.
۲) در Name Box یک اسم دلخواه (مجاز) را تایپ کنید و Enter‌ را بزنید.
۳) آن محدوده را باید انتخاب کنید (در Name Box باید نام دلخواهی را که گذاشته‌اید را مشاهده کنید) و سپس گزینه From Table/Range را بزنید.

پاورکوئری داده‌های شما را می‌خواهند و اگر به فرمول قدم Source‌ در Applied Step‌ پاورکوئری دقت کنید، فرمولی شبیه زیر را خواهید دید که نام محدوده شما (در فرمول زیر my_data) در آن دیده می‌شود:

توجه 💥) هنگامی که قرار است پاورکوئری داده‌ها را از یک فایل دیگر (نه همین فایل)‌ بخواهند، الزامی به تعریف Name‌ و یا Table کردن داده‌ها نیست زیرا پاورکوئری در این حالت (مجدد تاکید می‌کنم یعنی فایل دیگری) می‌تواند بر اساس «شیت‌های» داده‌ها از آن فایل بخواند.

اگر با پاورکوئری آشنا نیستید، « ۱۱ ویدئوی آموزش پاور کوئری (Power Query) در اکسل فرساران» را ببینید و سایر ویدئوهای را از سایت خریداری نمایید.

داینامیک کردن یک Name 

تا اینجا دانستیم که می‌توانیم برای سلول‌ها یک Name بگذاریم و سپس به پاورکوئری بگوییم که آن محدوده را بخواند. اما یک مشکل وجود دارد ! زیرا نامی که تعریف کرده‌اید دقیقا به محدوده‌ای مانند =data!$A$1:$B$4 اشاره دارد و این آدرس به صورت داینامیک (پویا)، با اضافه شدن سطر و ستون جدیدی به دیتابیس شما تغییر نمی‌کند. (در این فرمول data نام شیت است.)

بدیهی است که این مشکل کوچکی نیست زیرا تقریبا همه چیز در دنیای اکسل متغیر است. برای این کار می‌توانیم از تابع OFFSET و یا INDEX برای ساخت محدوده داینامیک استفاده کنیم. برای آشنایی با تابع OFFSET «آموزش ساخت یک داینامیک چارت در اکسل» را ببینید.

اگر فرض کنید که ما محدوده‌ای از داده‌ها داریم که از سلول A1 شروع می‌شوند و شامل چندین سطر و ستون هست و سطرها و ستون‌های جدیدی به آن اضافه می‌شود. با فرض اینکه همواره سلول‌های ستون A و سطر 1 اکسل آن محدوده همیشه دارای مقادیری است (یعنی اگر سطری اضافه شود، حتما در ستون A دارای مقداری است و اگر ستونی اضافه شود، حتما مقداری در سطر ۱ اکسل تایپ خواهد شد)، با استفاده از تابع OFFSET محدوده داینامیک ما اینگونه تعریف می‌شود:

= OFFSET($A$1, 0, 0, COUNTA($A:$A), COUNTA($1:$1))

حال این فرمول را به عنوان یک Name‌ در اکسل تعریف می‌کنیم و به پاورکوئری خواهیم گفت که این Name را بخواند:

هشدار ۱) ابتدا فرمول را در سلول اکسل بنویسید و آزمایش کنید و سپس اگر صحیح بود آن را در پنجره Name Manager کپی پیست کنید! از نوشتن فرمول در کادر Refers to خودداری کنید چون کلافه خواهید شد!

نکته ۱) در یک Name ، آدرس شیت هم به صورت اتوماتیک اضافه می‌شود. در تصویر قبل، data نام شیت است که در فرمول اضافه شده است و همچنین تمامی آدرس‌های به صورت مطلق (یعنی با $) در خواهند آمد.

🍒 نکته ۲) Tableها به صورت اتوماتیک محدوده‌هایی پویا هستند و نیازی نیست که آن‌ها را با Nameها و OFFSET ، به صورت پویا درآوریم.

نکته ۲) جالب است بدانید که ما می‌توانیم با تابع INDEX و یا INDIRECT هم یک محدوده پویا بسازیم که معمولا کسی این کار را نمی‌کند و از همان OFFSET استفاده می‌شود.

داینامیک کردن یک کوئری با Nameها

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

مقدار سلولی که نام آن min_capacity است را اینگونه در پاورکوئری می‌خوانیم:

= Excel.CurrentWorkbook(){[Name="min_capacity"]}[Content]{0}[Column1]

و سپس از این مقدار در فیلتر کردن سطرهای یک کوئری دیگر استفاده می‌کنیم:

= Table.SelectRows(#"Changed Type", 
each [capacity] > Excel.CurrentWorkbook(){[Name="min_capacity"]}[Content]{0}[Column1] )

و همچنین تکنیک دیگر بسیار متداول و مفید خواندن مسیر فایلمان در پاورکوئری است. در این حالت باید از «تابع CELLS» اکسل استفاده کنیم تا مسیر فایل را بخوانیم و سپس این مسیر را در یک Name به پاورکوئری پاس دهیم.

استفاده از Nameهای تعریف شده در VBA

کاملا متداول است که در VBA از Nameها استفاده کنیم زیرا کدهای ما بسیار خواناتر و قابل کنترل‌تر خواهند شد. در کد زیر از invoice_range که نامی است که به سلولی در اکسل اشاره می‌کند استفاده شده است و مقدار و رنگ آن سلول را تغییر می‌دهیم:

Sub set_invoice_range()
	Range("invoice_id").Value = 1234
End Sub

برای اجرای کد به شکل دیگری، فایل پیوست را دانلود کنید.

قواعد انتخاب نامی برای Nameها

+ به صورت کلی یک نام باید خلاصه اما گویا باشد. مثلا inv_num می‌تواند مخفی مناسبی برای invoice number در نظر گرفته شود و یا file_path نام مناسبی برای مسیر فایل ما است و نام یک Table می‌تواند چیزهایی مانند forosh و sales و ... باشد.

+ گذاشتن فاصله ممنوع است و به جای فاصله از کاراکتر  و یا . استفاده کنید مثلا inv_num و یا inv.num

+ نام نمی‌تواند نام یک سلول اکسل باشد مثلا TAX2022 نام یکی از سلو‌ل‌های اکسل است.

+ بدیهی است که نام‌ها نمی‌توانند تکراری باشند.

+ اگر چه یک نام می‌تواند فارسی باشد، اما اصلا توصیه نمی‌کنم که این کار را انجام دهید. به صورت کلی نام هرچیز را بهتر است که انگلیسی یا پینگلیس/فینگلیش بگذارید تا در فرمول نویسی و ... گرفتار نشوید.

جو فروشان

در «بخش کاربرد Nameها» سه مورد را ذکر کردم اما در مورد سوم هیچ توضیحی ندادم و هدفم آن است که دست خالی و تهی «جو فروشان» را نشان دهم. منظور از جو فروشان، سایت‌ها و افرادی است که کار اصلی آنها کپی محتوا، مقاله و یا ترجمه از سایت‌های خارجی است و محتوایی را که تولید می‌کنند بدون ارجاع و ذکر نام منبع، به نام خودشان منتشر می‌کنند. این‌ افراد همان کسانی هستند که در قابوس نامه آنها را «گندم نمای جو فروش» می‌نامد.

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

سایر نکته‌های Nameها 

الف) کلیدهای میانبر:

کلید F3 : نمایش Nameهایی تعریف شده در فایل و استفاده از آنها در فرمول

کلید Ctrl + F3 : نمایش پنجره Name Manger در اکسل

ب) اکسل به صورت خودکار نام‌هایی را تولید می‌کند مثلا با گزینه Row to repeat at top (در تنظیمات پرینت) نام Print_Titles و با Print Area نام Print_Area تولید خواهد شد.

ج) پرسش) آیا Nameها می‌توانند باعث کند شدن اکسل شوند؟
پاسخ) تجربه من نشان دهنده چنین چیزی نیست و در منبع و کتاب‌های معتبر ندیده‌ام که چنین چیزی قید شده باشد. اما توصیه می‌کنم که حتما باید Nameهایی که خطا شده‌اند را پاک کنید. 

د) در فرمول نویسی اکسل می‌توان به یک Name که در فایل دیگری تعریف شده است،‌ ارجاع داد.