فهرست
هنگام استفاده از مایکروسافت اکسل، اغلب کاربر از قبل نمی داند که در نتیجه چه مقدار اطلاعات در جدول وجود دارد. بنابراین، ما در همه شرایط متوجه نمی شویم که چه محدوده ای باید پوشش داده شود. از این گذشته، مجموعه ای از سلول ها یک مفهوم متغیر است. برای رهایی از این مشکل، باید تولید محدوده را به صورت خودکار انجام داد تا صرفاً بر اساس میزان داده ای باشد که توسط کاربر وارد شده است.
تغییر خودکار محدوده سلولی در اکسل
مزیت محدوده های خودکار در اکسل این است که استفاده از فرمول ها را بسیار آسان می کند. علاوه بر این، آنها به طور قابل توجهی تجزیه و تحلیل داده های پیچیده را که حاوی تعداد زیادی فرمول است، که شامل بسیاری از توابع است، ساده می کند. می‌توانید به این محدوده یک نام بدهید، و سپس بسته به داده‌هایی که دارد، به‌طور خودکار به‌روزرسانی می‌شود.
نحوه ایجاد تغییر خودکار محدوده در اکسل
فرض کنید شما یک سرمایه گذار هستید که باید روی یک شی سرمایه گذاری کنید. در نتیجه، ما می‌خواهیم اطلاعاتی در مورد میزان درآمدی که می‌توانید در کل برای کل زمانی که پول برای این پروژه کار می‌کند، به دست آوریم. با این وجود، برای به دست آوردن این اطلاعات، باید به طور منظم نظارت کنیم که این شی چقدر سود کل برای ما به ارمغان می آورد. همان گزارشی را که در این اسکرین شات است تهیه کنید.
در نگاه اول، راه حل واضح است: شما فقط باید کل ستون را جمع کنید. اگر ورودی در آن ظاهر شود، مقدار به طور مستقل به روز می شود. اما این روش معایب زیادی دارد:
- اگر مشکل از این طریق حل شود، امکان استفاده از سلول های موجود در ستون B برای اهداف دیگر وجود نخواهد داشت.
- چنین جدولی مقدار زیادی رم مصرف می کند که استفاده از سند را در رایانه های ضعیف غیرممکن می کند.
از این رو، حل این مشکل از طریق نام های پویا ضروری است. برای ایجاد آنها، باید دنباله اقدامات زیر را انجام دهید:
- به تب "Formulas" که در منوی اصلی قرار دارد بروید. بخش "اسامی تعریف شده" وجود خواهد داشت که در آن دکمه "تخصیص نام" وجود دارد که باید روی آن کلیک کنیم.
- سپس یک کادر محاوره ای ظاهر می شود که باید فیلدهایی را که در تصویر نشان داده شده است پر کنید. توجه به این نکته مهم است که باید تابع را اعمال کنیم = جابجایی همراه با تابع بررسیبرای ایجاد محدوده به روز رسانی خودکار
- پس از آن باید از تابع استفاده کنیم مبلغ، که از محدوده دینامیکی ما به عنوان آرگومان استفاده می کند.
پس از تکمیل این مراحل، می‌توانیم ببینیم که چگونه پوشش سلول‌های متعلق به محدوده «درآمد» به‌روزرسانی می‌شود و عناصر جدیدی را در آنجا اضافه می‌کنیم.
تابع OFFSET در اکسل
بیایید به توابعی که قبلاً در قسمت "محدوده" ضبط کرده بودیم نگاه کنیم. با استفاده از تابع دسترس با توجه به تعداد سلول های ستون B می توانیم مقدار محدوده را تعیین کنیم. آرگومان های تابع به شرح زیر است:
- سلول شروع با این آرگومان، کاربر می تواند نشان دهد که کدام سلول در محدوده بالا سمت چپ در نظر گرفته می شود. به پایین و سمت راست گزارش می دهد.
- بازه با ردیف ها جبران می شود. با استفاده از این محدوده، تعداد سلول‌هایی را تعیین می‌کنیم که با آن یک افست باید از سلول سمت چپ بالای محدوده رخ دهد. شما می توانید نه تنها از مقادیر مثبت، بلکه از صفر و منهای استفاده کنید. در این حالت ممکن است جابجایی اصلاً رخ ندهد یا در جهت مخالف انجام شود.
- بازه با ستون ها جبران می شود. این پارامتر مشابه پارامتر قبلی است، فقط به شما امکان می دهد درجه تغییر افقی محدوده را تنظیم کنید. در اینجا می توانید از مقادیر صفر و منفی نیز استفاده کنید.
- مقدار دامنه در ارتفاع. در واقع عنوان این برهان معنای آن را برای ما روشن می کند. این تعداد سلول هایی است که محدوده باید افزایش یابد.
- مقدار محدوده در عرض. آرگومان مشابه مورد قبلی است، فقط به ستون ها مربوط می شود.
اگر نیازی ندارید، نیازی نیست دو آرگومان آخر را مشخص کنید. در این حالت مقدار محدوده تنها یک سلول خواهد بود. به عنوان مثال، اگر فرمول را مشخص کنید =OFFSET(A1;0;0)، این فرمول به همان سلولی در آرگومان اول اشاره خواهد کرد. اگر افست عمودی روی 2 واحد تنظیم شود، در این حالت سلول به سلول A3 اشاره می کند. حالا بیایید به تفصیل توضیح دهیم که تابع چیست بررسی.
تابع COUNT در اکسل
با استفاده از تابع بررسی ما تعیین می کنیم که در کل چند سلول در ستون B را پر کرده ایم. یعنی با استفاده از دو تابع مشخص می کنیم که چند سلول در محدوده پر شده و بر اساس اطلاعات دریافتی اندازه محدوده را مشخص می کنیم. بنابراین فرمول نهایی به صورت زیر خواهد بود: =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1)
بیایید نگاهی به نحوه درک صحیح اصل این فرمول بیندازیم. اولین آرگومان به جایی اشاره می کند که محدوده دینامیکی ما از کجا شروع می شود. در مورد ما، این سلول B2 است. پارامترهای بیشتر دارای مختصات صفر هستند. این نشان می دهد که ما نیازی به آفست نسبت به سلول بالا سمت چپ نداریم. تمام چیزی که ما پر می کنیم اندازه عمودی محدوده است که از تابع به عنوان استفاده کردیم بررسی، که تعداد سلول هایی که حاوی برخی داده ها هستند را تعیین می کند. چهارمین پارامتری که پر کردیم واحد است. بنابراین نشان می دهیم که عرض کل محدوده باید یک ستون باشد.
بنابراین، با استفاده از تابع بررسی کاربر می‌تواند با بارگذاری سلول‌هایی که حاوی مقادیری هستند، تا حد امکان از حافظه استفاده کند. بر این اساس، هیچ خطای اضافی در کار مرتبط با عملکرد ضعیف رایانه ای که صفحه گسترده روی آن کار خواهد کرد، وجود نخواهد داشت.
بر این اساس، برای تعیین اندازه محدوده بسته به تعداد ستون ها، باید دنباله ای از اقدامات مشابه را انجام دهید، فقط در این مورد باید واحد را در پارامتر سوم و فرمول را در پارامتر چهارم مشخص کنید. بررسی.
می بینیم که با کمک فرمول های اکسل، نه تنها می توانید محاسبات ریاضی را خودکار کنید. این فقط یک قطره در اقیانوس است، اما در واقع آنها به شما اجازه می دهند تقریباً هر عملیاتی را که به ذهنتان می رسد خودکار کنید.
نمودارهای پویا در اکسل
بنابراین، در مرحله آخر، ما توانستیم یک محدوده پویا ایجاد کنیم که اندازه آن کاملاً به تعداد سلول های پر شده آن بستگی دارد. اکنون، بر اساس این داده ها، می توانید نمودارهای پویا ایجاد کنید که به محض ایجاد هر گونه تغییر یا اضافه کردن یک ستون یا ردیف اضافی توسط کاربر، به طور خودکار تغییر می کند. توالی اقدامات در این مورد به شرح زیر است:
- محدوده خود را انتخاب می کنیم و پس از آن نموداری از نوع "هیستوگرام با گروه بندی" را وارد می کنیم. شما می توانید این مورد را در بخش "درج" در بخش "نمودار - هیستوگرام" پیدا کنید.
- ما روی ستون تصادفی هیستوگرام یک کلیک چپ ماوس می کنیم و پس از آن تابع =SERIES() در خط تابع نشان داده می شود. در تصویر می توانید فرمول دقیق را مشاهده کنید.
- پس از آن، باید تغییراتی در فرمول ایجاد شود. شما باید محدوده را بعد از "Sheet1!" جایگزین کنید. به نام محدوده این باعث عملکرد زیر می شود: = ردیف (برگه 1! $ b 1 $ ؛؛ sheet1! درآمد ؛ 1)
- اکنون باقی مانده است که یک رکورد جدید به گزارش اضافه کنیم تا بررسی کنیم که نمودار به طور خودکار به روز می شود یا خیر.
بیایید به نمودار خود نگاهی بیندازیم.
بیایید خلاصه کنیم که چگونه این کار را انجام دادیم. در مرحله قبل یک محدوده دینامیکی ایجاد کردیم که اندازه آن بستگی به تعداد عناصر دارد. برای این کار از ترکیبی از توابع استفاده کردیم بررسی и دسترس. ما این محدوده را نامگذاری کردیم و سپس از ارجاع به این نام به عنوان محدوده هیستوگرام خود استفاده کردیم. کدام محدوده خاص را به عنوان منبع داده در مرحله اول انتخاب کنید چندان مهم نیست. نکته اصلی این است که بعداً آن را با نام محدوده جایگزین کنید. به این ترتیب می توانید مقدار زیادی رم ذخیره کنید.
محدوده های نامگذاری شده و کاربرد آنها
بیایید اکنون با جزئیات بیشتری در مورد چگونگی ایجاد صحیح محدوده های نامگذاری شده و استفاده از آنها برای انجام وظایفی که برای کاربر اکسل تنظیم شده است صحبت کنیم.
به طور پیش فرض، ما از آدرس های سلولی معمولی برای صرفه جویی در زمان استفاده می کنیم. این زمانی مفید است که شما نیاز دارید یک محدوده را یک یا چند بار بنویسید. اگر باید همیشه از آن استفاده شود یا باید تطبیق پذیر باشد، باید از محدوده های نامگذاری شده استفاده شود. آنها ایجاد فرمول ها را بسیار آسان تر می کنند و تجزیه و تحلیل فرمول های پیچیده که شامل تعداد زیادی توابع هستند برای کاربر چندان دشوار نخواهد بود. اجازه دهید برخی از مراحل مربوط به ایجاد محدوده های پویا را شرح دهیم.
همه چیز با نامگذاری سلول شروع می شود. برای این کار کافیست آن را انتخاب کنید و سپس نام مورد نیاز خود را در قسمت نام آن بنویسید. مهم این است که به راحتی به خاطر بسپارید. در هنگام نام گذاری محدودیت هایی وجود دارد که باید در نظر گرفته شوند:
- حداکثر طول 255 کاراکتر است. این برای تعیین نامی که دلتان می خواهد کافی است.
- نام نباید دارای فاصله باشد. بنابراین، اگر حاوی چندین کلمه باشد، می توان آنها را با استفاده از کاراکتر زیر خط جدا کرد.
اگر بعداً در برگه های دیگر این فایل نیاز به نمایش این مقدار یا اعمال آن برای انجام محاسبات بیشتر داشته باشیم، دیگر نیازی به تغییر به اولین صفحه نیست. فقط می توانید نام این سلول محدوده را یادداشت کنید.
مرحله بعدی ایجاد یک محدوده با نام است. رویه در اصل یکسان است. ابتدا باید یک محدوده را انتخاب کنید و سپس نام آن را مشخص کنید. پس از آن، این نام می تواند در تمام عملیات داده های دیگر در اکسل استفاده شود. به عنوان مثال، محدوده های نامگذاری شده اغلب برای تعریف مجموع مقادیر استفاده می شود.
علاوه بر این، امکان ایجاد یک محدوده با نام با استفاده از تب Formulas با استفاده از ابزار Set Name وجود دارد. پس از انتخاب آن، پنجره ای ظاهر می شود که در آن باید یک نام برای محدوده خود انتخاب کنیم و همچنین منطقه ای را که به صورت دستی به آن گسترش می یابد را مشخص کنیم. همچنین می‌توانید محل کار این محدوده را مشخص کنید: در یک برگه یا در سراسر کتاب.
اگر یک محدوده نام قبلا ایجاد شده باشد، برای استفاده از آن، یک سرویس ویژه به نام مدیر نام وجود دارد. این نه تنها امکان ویرایش یا افزودن نام‌های جدید را فراهم می‌کند، بلکه در صورت عدم نیاز، آنها را نیز حذف می‌کند.
باید در نظر داشت که هنگام استفاده از محدوده های نامگذاری شده در فرمول ها، پس از حذف آن، فرمول ها به طور خودکار با مقادیر صحیح بازنویسی نمی شوند. بنابراین ممکن است خطاهایی رخ دهد. بنابراین، قبل از حذف یک محدوده نامگذاری شده، باید مطمئن شوید که در هیچ یک از فرمول ها استفاده نشده است.
راه دیگر برای ایجاد یک محدوده با نام، دریافت آن از یک جدول است. برای انجام این کار، یک ابزار ویژه به نام "ایجاد از انتخاب" وجود دارد. همانطور که متوجه شدیم، برای استفاده از آن، ابتدا باید محدوده ای را که ما ویرایش خواهیم کرد، انتخاب کنید و سپس مکانی را که سرفصل ها را در آن قرار داده ایم، تعیین کنید. در نتیجه، بر اساس این داده ها، اکسل به طور خودکار تمام داده ها را پردازش می کند و عناوین به طور خودکار اختصاص می یابد.
اگر عنوان حاوی چندین کلمه باشد، اکسل به طور خودکار آنها را با یک خط زیر از هم جدا می کند.
بنابراین، ما متوجه شدیم که چگونه می‌توان محدوده‌های نامگذاری شده پویا ایجاد کرد و چگونه به شما امکان می‌دهند کار با مقادیر زیاد داده را خودکار کنید. همانطور که می بینید، کافی است از چندین تابع و ابزارهای برنامه تعبیه شده در عملکرد استفاده کنید. هیچ چیز پیچیده ای وجود ندارد، اگرچه ممکن است در نگاه اول برای یک مبتدی چنین به نظر برسد.