هک زندگی اکسل برای کسانی که درگیر گزارش و پردازش داده هستند

2024 نویسنده: Malcolm Clapton | [email protected]. آخرین اصلاح شده: 2024-01-13 01:23

در این پست، Renat Shagabutdinov، دستیار مدیر کل انتشارات Mann, Ivanov and Ferber، برخی از هک های جالب زندگی Excel را به اشتراک می گذارد. این نکات برای هر کسی که درگیر در گزارش های مختلف، پردازش داده ها و ایجاد ارائه ها است مفید خواهد بود.

این مقاله شامل تکنیک های ساده برای ساده سازی کار شما در اکسل است. آنها به ویژه برای کسانی که درگیر گزارش های مدیریت هستند مفید هستند، گزارش های تحلیلی مختلفی را بر اساس بارگیری از 1C و گزارش های دیگر تهیه می کنند، ارائه ها و نمودارهایی را از آنها برای مدیریت تشکیل می دهند. من تظاهر به تازگی مطلق نمی کنم - به هر شکلی، این تکنیک ها احتمالاً در انجمن ها بحث شده یا در مقالات ذکر شده است.

جایگزین های ساده برای VLOOKUP و HLOOKUP، اگر مقادیر مورد نظر در ستون اول جدول نباشد: LOOKUP، INDEX + SEARCH

توابع VLOOKUP و HLOOKUP فقط در صورتی کار می کنند که مقادیر مورد نظر در اولین ستون یا ردیف جدولی که قصد دریافت داده از آن را دارید، وجود داشته باشد.

در غیر این صورت دو گزینه وجود دارد:

1. از تابع LOOKUP استفاده کنید.

   نحو زیر را دارد: LOOKUP (lookup_value; lookup_vector; result_vector). اما برای اینکه به درستی کار کند، مقادیر محدوده view_vector باید به ترتیب صعودی مرتب شوند:

   

2. از ترکیبی از توابع MATCH و INDEX استفاده کنید.

   تابع MATCH عدد ترتیبی یک عنصر در آرایه را برمی‌گرداند (با کمک آن می‌توانید پیدا کنید که عنصر جستجو شده در کدام ردیف از جدول قرار دارد)، و تابع INDEX یک عنصر آرایه را با یک عدد معین برمی‌گرداند (که ما متوجه خواهیم شد). با استفاده از تابع MATCH).

   

   نحو توابع:

   • SEARCH (مقدار_جستجو؛ آرایه_جستجو؛ نوع_تطبیق) - برای مورد ما به یک نوع تطبیق "تطابق دقیق" نیاز داریم که با عدد 0 مطابقت دارد.

   • INDEX (آرایه؛ خط_شماره؛ [شماره_ستون]). در این مورد، شما نیازی به تعیین شماره ستون ندارید، زیرا آرایه از یک ردیف تشکیل شده است.

نحوه پر کردن سریع سلول های خالی در لیست

وظیفه این است که سلول های ستون را با مقادیر بالا پر کنید (به طوری که موضوع در هر ردیف جدول باشد و نه تنها در ردیف اول بلوک کتاب در مورد موضوع):

ستون "موضوع" را انتخاب کنید، روی نوار در گروه "صفحه اصلی" کلیک کنید، دکمه "یافتن و انتخاب" → "انتخاب یک گروه از سلول ها" → "سلول های خالی" و شروع به وارد کردن فرمول (یعنی یک برابر قرار دهید) را علامت بزنید) و به سلول بالا مراجعه کنید، به سادگی با کلیک بر روی فلش بالا روی صفحه کلید خود. پس از آن Ctrl + Enter را فشار دهید. پس از آن، می توانید داده های دریافتی را به عنوان مقادیر ذخیره کنید، زیرا دیگر به فرمول ها نیاز نیست:

نحوه پیدا کردن خطاها در فرمول

محاسبه یک بخش جداگانه از یک فرمول

برای درک یک فرمول پیچیده (که در آن از توابع دیگر به عنوان آرگومان های تابع استفاده می شود، یعنی برخی از توابع در برخی دیگر تودرتو هستند) یا برای یافتن منبع خطاها در آن، اغلب باید بخشی از آن را محاسبه کنید. دو راه آسان وجود دارد:

1. برای محاسبه بخشی از فرمول درست در نوار فرمول، آن قسمت را انتخاب کرده و F9 را فشار دهید:

   

   در این مثال، مشکلی با تابع SEARCH وجود داشت - آرگومان ها در آن جابجا شدند. لازم به یادآوری است که اگر محاسبه بخشی از تابع را لغو نکنید و Enter را فشار دهید، قسمت محاسبه شده یک عدد باقی می ماند.

2. روی دکمه Calculate Formula در گروه Formulas روی نوار کلیک کنید:

   

   در پنجره ای که ظاهر می شود، می توانید فرمول را گام به گام محاسبه کنید و تعیین کنید که خطا در چه مرحله و در کدام تابع رخ می دهد (در صورت وجود):

   

چگونه تعیین کنیم که یک فرمول به چه چیزی بستگی دارد یا به آن اشاره دارد

برای تعیین اینکه یک فرمول به کدام سلول ها بستگی دارد، در گروه Formulas روی روبان، روی دکمه Affecting Cells کلیک کنید:

فلش ها نشان می دهد که نتیجه محاسبه به چه چیزی بستگی دارد.

اگر نماد مشخص شده در تصویر به رنگ قرمز نمایش داده شود، فرمول به سلول های برگه های دیگر یا کتاب های دیگر بستگی دارد:

با کلیک بر روی آن، می‌توانیم دقیقاً ببینیم که سلول‌ها یا محدوده‌های تأثیرگذار در کجا قرار دارند:

در کنار دکمه "تأثیر بر سلول ها" دکمه "سلول های وابسته" قرار دارد که به همین ترتیب کار می کند: فلش هایی از سلول فعال را با فرمولی به سلول هایی که به آن وابسته هستند نمایش می دهد.

دکمه "حذف فلش ها" که در همان بلوک قرار دارد، به شما امکان می دهد فلش ها را به سلول های تأثیرگذار، فلش ها را به سلول های وابسته یا هر دو نوع فلش را به طور همزمان حذف کنید:

نحوه یافتن مجموع (تعداد، میانگین) مقادیر سلول از چندین برگه

فرض کنید چندین برگه از یک نوع با داده‌هایی دارید که می‌خواهید آن‌ها را به روش دیگری اضافه کنید، بشمارید یا پردازش کنید:

برای انجام این کار، در سلولی که می خواهید نتیجه را مشاهده کنید، یک فرمول استاندارد، به عنوان مثال SUM (SUM) وارد کنید و نام اولین و آخرین برگه را از لیست برگه هایی که باید در آن پردازش کنید، مشخص کنید. آرگومان که با دو نقطه جدا شده است:

مجموع سلول ها را با آدرس B3 از برگه های "Data1"، "Data2"، "Data3" دریافت خواهید کرد:

این آدرس دهی برای شیت های واقع شده کار می کند همواره … نحو به شرح زیر است: = FUNCTION (first_list: last_list! مرجع محدوده).

نحوه ساخت خودکار عبارات قالب

با استفاده از اصول اولیه کار با متن در اکسل و چند کارکرد ساده می توانید عبارات قالب را برای گزارش تهیه کنید. چند اصل کار با متن:

• ما متن را با استفاده از علامت & به هم متصل می کنیم (شما می توانید آن را با تابع CONCATENATE جایگزین کنید، اما این کار چندان منطقی نیست).
• متن همیشه در گیومه نوشته می شود، ارجاع به سلول های دارای متن همیشه بدون اشاره است.
• برای به دست آوردن کاراکتر سرویس "کوتیشن مارک" از تابع CHAR با آرگومان 32 استفاده کنید.

نمونه ای از ایجاد یک عبارت الگو با استفاده از فرمول ها:

نتیجه:

در این مورد، علاوه بر تابع CHAR (برای نمایش نقل قول ها)، از تابع IF استفاده می شود که به شما امکان می دهد بسته به روند فروش مثبت، متن را تغییر دهید و تابع TEXT که به شما امکان نمایش شماره در هر قالب نحو آن در زیر توضیح داده شده است:

TEXT (مقدار؛ قالب)

فرمت در علامت نقل قول مشخص می شود، دقیقاً مانند اینکه شما یک قالب سفارشی را در پنجره Format Cells وارد می کنید.

متون پیچیده تر نیز می توانند خودکار شوند. در عمل من، اتوماسیون نظرات طولانی اما معمولی برای گزارش مدیریت در قالب «INDICATOR نسبت به برنامه XX کاهش / افزایش یافت، عمدتاً به دلیل رشد / کاهش FACTOR1 توسط XX، رشد / کاهش FACTOR2 توسط YY …» با فهرست متغیری از عوامل. اگر اغلب چنین نظراتی را می نویسید و فرآیند نوشتن آنها را می توان الگوریتم کرد، ارزش دارد که یک فرمول یا ماکرو ایجاد کنید که حداقل بخشی از کار را برای شما نجات دهد.

نحوه ذخیره داده ها در هر سلول پس از الحاق

هنگامی که سلول ها را ادغام می کنید، تنها یک مقدار حفظ می شود. اکسل هنگام تلاش برای ادغام سلول ها در مورد این هشدار می دهد:

بر این اساس، اگر بسته به هر سلول فرمولی داشتید، پس از ترکیب آنها کار نمی کند (# N / A خطا در خطوط 3-4 مثال):

برای ادغام سلول‌ها و حفظ داده‌ها در هر یک از آنها (شاید فرمولی مانند این مثال انتزاعی داشته باشید؛ شاید بخواهید سلول‌ها را ادغام کنید، اما تمام داده‌ها را برای آینده نگه دارید یا عمداً آن‌ها را پنهان کنید)، سلول‌های موجود در برگه را ادغام کنید. ، آنها را انتخاب کنید و سپس از دستور Format Painter برای انتقال قالب بندی به سلول هایی که باید ترکیب کنید استفاده کنید:

نحوه ساختن یک محور از چندین منبع داده

اگر نیاز به ایجاد یک محور از چندین منبع داده به طور همزمان دارید، باید "PivotTable and Chart Wizard" را به نوار یا پنل دسترسی سریع اضافه کنید که چنین گزینه ای دارد.

می توانید این کار را به صورت زیر انجام دهید: "File" → "Options" → "Quick Access Toolbar" → "All Commands" → "PivotTable and Chart Wizard" → "Add":

پس از آن، یک نماد مربوطه روی نوار ظاهر می شود که روی آن کلیک کنید که همان جادوگر را فرا می خواند:

وقتی روی آن کلیک می کنید، یک کادر محاوره ای ظاهر می شود:

در آن، شما باید مورد "در چندین محدوده ادغام" را انتخاب کنید و روی "بعدی" کلیک کنید. در مرحله بعد می توانید «Create one page field» یا «Create page fields» را انتخاب کنید. اگر می خواهید به طور مستقل برای هر یک از منابع داده نامی بیاورید، مورد دوم را انتخاب کنید:

در پنجره بعدی، تمام محدوده هایی را که pivot بر اساس آنها ساخته می شود، اضافه کنید و نام آنها را بگذارید:

پس از آن، در آخرین کادر محاوره ای، مشخص کنید که گزارش جدول محوری در کجا قرار می گیرد - در یک صفحه موجود یا جدید:

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

نحوه محاسبه تعداد دفعات متن A در متن B ("تعرفه MTS SuperMTS" - دو مورد از مخفف MTS)

در این مثال، ستون A حاوی چندین خط متن است و وظیفه ما این است که بفهمیم هر یک از آنها چند بار حاوی متن جستجوی واقع در سلول E1 است:

برای حل این مشکل می توانید از یک فرمول پیچیده متشکل از توابع زیر استفاده کنید:

1. DLSTR (LEN) - طول متن را محاسبه می کند، تنها آرگومان متن است. مثال: DLSTR ("ماشین") = 6.
2. SUBSTITUTE - متن خاصی را در یک رشته متنی با متن دیگری جایگزین می کند. نحو: SUBSTITUTE (متن؛ متن_قدیمی؛ متن_جدید). مثال: SUBSTITUTE ("ماشین"؛ "خودکار"؛ "") = "موبایل".
3. UPPER - همه کاراکترهای یک رشته را با حروف بزرگ جایگزین می کند. تنها استدلال متن است. مثال: UPPER ("ماشین") = "CAR". ما به این تابع برای انجام جستجوهای غیر حساس به حروف بزرگ نیاز داریم. پس از همه، UPPER ("ماشین") = UPPER ("ماشین")

برای یافتن وقوع یک رشته متن خاص در یک رشته دیگر، باید تمام موارد وقوع آن را در رشته اصلی حذف کنید و طول رشته حاصل را با رشته اصلی مقایسه کنید:

DLSTR ("Tariff MTS Super MTS") - DLSTR ("Tariff Super") = 6

و سپس این تفاوت را بر طول رشته ای که به دنبال آن بودیم تقسیم کنید:

6 / DLSTR ("MTS") = 2

دقیقاً دو بار است که خط "MTS" در خط اصلی گنجانده شده است.

باقی مانده است که این الگوریتم را به زبان فرمول ها بنویسیم (بیایید متنی را که در آن به دنبال رخدادها هستیم و با "جستجو" - متنی که به تعداد رخدادهای آن علاقه مند هستیم را با "متن" مشخص کنیم):

= (DLSTR (متن) -LSTR (SUBSTITUTE (UPPER (متن)؛ UPPER (جستجو)، ""))) / DLSTR (جستجو)

در مثال ما، فرمول به صورت زیر است:

= (DLSTR (A2) -LSTR (SUBSTITUTE (UPPER (A2)، UPPER ($ E $ 1)، ""))) / DLSTR ($ E $ 1)