ابزار Auditing (وارسی) و افزونه Inquire (بررسی)

آخرین به‌روزرسانی: 27 دی 1401

در مقاله قبلی به بررسی فرمول‌های سه‌بعدی و هایپر لینک‌ها پرداختیم، در این فصل به سؤال‌های زیر پاسخ می دهیم.

سؤال‌های مطرح شده در این فصل

• کاربرگی با 5000 ردیف ارائه شده تا با استفاده از آنها ارزش خالص فعلی یک اتومبیل جدید محاسبه شود. تحلیلگر مالی ما در باره درصد سالانه رشد قیمت محصول فرضی ارائه داده است. کدام سلول از این کاربرگ تحت تأثیر این فرضیه قرارمی گیرد؟
• به نظر می‌رسد که تحلیلگر مالی در باره محاسبه سود پیش از مالیات سال اول اشتباه کرده است. کدام سلول در کاربرگ نمونه برای این محاسبه مورداستفاده قرار گرفته است؟
• ابزار Auditing چگونه وقتی‌که روی داده‌هایی در بیش از یک کاربرگ یا فایل کار می‌کنیم ما را یاری می‌دهد؟
• افزونه Inquire چیست و چگونه می باید آن را نصب کرد؟
• چگونه می‌توان از افزونه Inquire برای مقایسه کردن فایل‌های اکسل استفاده کرد؟
• چگونه می‌توان از افزونه Inquire برای تحلیل ساختار یک فایل اکسل استفاده کرد؟
• چگونه می‌توان از افزونه Inquire برای تحلیل رابطه بین کاربرگ‌ها و فایل‌ها استفاده کرد؟
• چگونه می‌توان از افزونه Inquire برای تحلیل پیش آیند و وابستگی یک سلول خاص استفاده کرد؟
• چگونه می‌توان از افزونهInquire برای پاک‌کردن فرمت سلول استفاده کرد؟

اغلب وقتی به کلمه ساختار برمی‌خوریم قبل از هر چیزی پیکره یک ساختمان به نظرمان می‌رسد. اما ساختار یک کاربرگ نمونه در واقع به نحوه‌ای که مفروضات درون آن قرار داده شده (داده‌هایی مثل فروش واحدها، قیمت و هزینه واحد) تا خروجی‌هایی مورد نظر مثل ارزش خالص فعلی، سود یا هزینه محاسبه شوند، ارجاع دارد. ابزار وارسی مایکروسافت اکسل 2019 روشی آسان برای ثبت ساختار یک کاربرگ ارائه می‌دهد که درک منطق موجود در کاربرگ‌های بسیار پیچیده را راحت‌تر می‌نماید.

برای دیدن گزینه‌های انتخابی وارسی در اکسل ابتدا سربرگ Formulas را در روی ریبون فعال کنید و سپس گروه گزینه‌های Formula Auditing را که در تصویر 1-16 نمایش‌داده‌شده است، بررسی کنید. در این فصل بیشتر این فرمان‌های موجود را بررسی خواهیم کرد. البته فرمان Evaluate Formula را در فصل 22 به نام تابع OFFSET بررسی خواهیم کرد.

تصویر 1-16 گروه Formula Auditing بر روی نوار ریبون اکسل

کلیک کردن روی گزینه Show Formulas (نمایش فرمول‌ها) درست مثل کلیدی عمل می‌کند که با هر بار کلیک روی آن فرمول‌های درون سلول‌ها و یا مقادیر حاصل از فرمول‌ها به طور متناوب نمایش داده می‌شوند. شما همچنین می‌توانید کلیدهای Ctrl+~ را فشار دهید تا به طور متناوب فرمول‌های درون سلول یا مقادیر حاصله را ببینید. فرمول‌ها را می‌توان با استفاده از تابع FORMULATEXT که در اکسل 2013 معرفی شده ، در کاربرگ نمایش داد. برای دیدن اینکه آیا سلول حاوی فرمول است می توان از تابع ISFORMULA  استفاده کرد. فایلی به نام ISFORMULATEXT.xlsx (تصویر 2-16 را ببینید) استفاده از این توابع را به شما نشان می‌دهد.

تصویر 2-16 استفاده از توابع ISFORMULA وFORMULATEXT

در ستون A عددهای 1 و 2 و 3 را وارد کرده‌ و در ستون B این عددها را در عدد 5 ضرب کرده‌ایم. با کپی‌کردن فرمول =FORMULATEX(B4) از سلول E4 به محدوده E5:E6  می‌بینید که فرمول‌های استفاده شده در ستون B در ستون E ظاهر می‌شوند.

ستون‌های C و D نحوه استفاده از تابع دیگری در اکسل را بنام ISFORMULA (که در اکسل 2013 اضافه شد) را نشان می‌دهند. این تابع چنانچه سلول حاوی فرمولی باشد عبارت True و در غیر این صورت عبارت False  را برمی‌گرداند. با کپی‌کردن فرمول =ISFORMULA(A4) از سلول C4 به محدوده C4:D6 خواهیم دید که ستون B دارای فرمول و ستون A‌بدون فرمول است.

گزینه Error Cheking (بررسی خطا) در گروه گزینه‌های Formula Auditing  به شما اجازه می‌دهد کاربرگ خود را برای یافتن خطاها مورد بررسی قرار داده و آنها را تصحیح کنید. برای اینکه ببینیم گزینه Error Cheking   در اکسل چگونه کار می‌کند بیایید نگاهی دوباره به فایلی بنام Errortrap.xlsx بیندازیم (تصویر 3-16 را ببینید) که در ابتدا در فصل 12 بنام توابع  IF، IFERROR، IFS،CHOOSE و SWITCH با آن روبرو شدید. (فایلی بنام Errortrap.xlsx نیز در پوشه فایل‌های تمرینی فصل 16 قرار دارد اما این دو فایل یکی هستند)

تصویر 3-16 بررسی خطا

پس از اینکه در بخش Auditing group گزینه Error Cheking را انتخاب کردید با قراردادن نشانگر ماوس روی سلول E13 به اولین سلولی که در آن خطا یافته شده راهنمایی می‌شوید (در اینجا سلول E13) می‌توانید در سلول E13 روی علامت مربوط به نشان خطا کلیک کنید تا منوی کشویی مربوط به بررسی خطا باز شود و سه گزینه در اختیار شما قرار داده شود: Error Cheking (بررسی خطا) Trace Error(ردیابی خطا) و Circular refrences (ارجاع‌های چرخشی). (فایل ما حاوی هیچ ارجاع چرخشی نیست، بنابراین گزینه ارجاع چرخشی در دسترس نمی‌باشد. اگر این فایل دارای ارجاع چرخشی بود اکسل آنها را متمایز می‌کرد.) اگر گزینه Error Cheking  را انتخاب کنید، اکسل به اولین سلولی که دارای خطا باشد می‌رود (اولین خطای یافته شده در سلول E13 است) و کادر محاوره‌ای که در تصویر 4-16 نمایش‌داده‌شده، ظاهر می‌شود.

کلیک بر Help در این گزینه به شما اطلاعات بیشتری درباره خطا می‌دهد. گزینه Show Calcutaion Steps (نشان‌دادن مراحل محاسبه) به شما اجازه می‌دهد که مراحل محاسبه فرمول را قدم به قدم ببینید. گزینه Ignore Error  این کادر محاوره‌ای را می‌بندد. گزینه Edit In Formula Bar (ویرایش در نوار فرمول) به شما اجازه می‌دهد تا فرمول خود را ویرایش کنید. انتخاب دکمه Previous شما را به خطای بازیافته قبلی می‌برد و گزینه Next شما را به کادر محاوره‌ای خطای بعدی می‌فرستد.

تصویر 4-16 کادر محاوره‌ای Error Cheking

وقتی گزینه Trace Error را از انتخاب‌های Error Cheking  برمی‌گزینید اگر سلول فعال دارای خطایی باشد، آن‌وقت سلول‌هایی که برای  محاسبه سلول فعال نیاز است، با فلش های آبی رنگ یا مربع آبی رنگ‌ متمایز می‌شوند. مثلاً وقتی دوباره تصویر 3-16 را بررسی کنیم می‌بینیم که سلول D13 و محدوده سلول‌های D3:E7 برای محاسبه سلول E13  مورد نیاز می‌باشند. (این سلول یک پیام خطای #N/A صادر کرده است.)

با کلیک روی گزینه Watch Window در گروه گزینه‌های Formula Auditing می‌توانید یک سلول یا سلول‌هایی را انتخاب کنید تا در هرجایی از کاربرگ باشید بتوانید تغییر مقادیر سلول‌های انتخاب شده را ببینید. مثلاً بعد از انتخاب گزینه watch Window می‌توانید روی گزینه Add Watch کلیک کنید و یک فرمول (مثلاً سلول C3 در Sheet 1) برای بررسی یک سلول یا بیشتر به آن اضافه کنید. حالا اگر شما در حال کارکردن در فایل دیگری باشید پنجره Watch window به شما تغییرات سلول C3  در کاربرگ Sheet1 را نشان خواهد داد.

دکمه‌های Trace Prrecedents و Trace Dependents موارد پیش آیند و وابسته سلول‌های کاربرگ یا فرمول‌ها را نشان می‌دهد. یک Precedent  هر سلولی است که مقدار آن برای محاسبه مقدار یک فرمول خاص لازم است. مثلاً اگر قرار است کمپین ارسال مستقیم نامه‌های پستی‌ را تحلیل کنید می‌بایست تعداد نامه‌های پست شده و نسبت پاسخ‌گویی به نامه‌ها را حدس بزنید سپس تعداد پاسخ به نامه‌ها را از طریق تعداد پاسخ‌ها ضربدر نامه‌های پست شده (response rate*letters Mailed) محاسبه کنید.

در این مورد تعداد پاسخ‌ها و کل نامه‌های پست شده پیش آیند (Precedent) سلولی است که حاوی فرمول محاسبه تعداد پاسخ‌ها می‌باشد. یک مورد  وابسته (Dependent) سلولی است که حاوی فرمولی باشد که مقادیرش نمی‌توانند بدون اطلاعات از سلول انتخاب شده محاسبه شوند. در مثال قبلی سلولی که حاوی تعداد کل پاسخ‌ها است یک سلول پیش آیند سلولی است که حاوی تعداد پاسخ‌ها می‌باشد. وقتی که از ابزار formula-auditing استفاده می‌کنید، اکسل سلول‌های پیش آیند و وابسته را با فلشهای آبی مشخص می‌کند. برای دیدن گزینه‌های Trace Precedents و Trace Dependents کل ریبون باید نمایش داده شود. می‌توانید از کلیدهای Ctrl+F1 برای تغییر حالت نمایش ریبون از حالت کمینه به بیشینه استفاده کنید.

حالا بیایید ابزارهای formula-auditing را برای مسئله‌هایی عملی بکار بریم.

پاسخ به سؤالات این فصل:

کاربرگی با 5000 ردیف ارائه شده تا با استفاده از آنها ارزش خالص فعلی یک اتومبیل جدید محاسبه شود. تحلیلگر مالی ما درباره درصد سالانه رشد قیمت محصول فرضی ارائه داده است. کدام سلول از این کاربرگ تحت تأثیر این فرضیه قرارمی گیرد؟

کاربرگ Original Model در فایلی به نام NPVaudit.xlsx حاوی محاسباتی است که مقدار NPV سود پس از مالیات یک اتومبیل را  که قرار است پنج سال دیگر از کارخانه خریداری شود را محاسبه می‌کند. (تصویر 5-15 را ببینید) قیمت و هزینه‌ها به هزار دلار درج شده‌اند. مقادیر معلوم فرض شده برای تحلیل در سلول‌های C1:C8 درج شده‌اند. (با نام‌های تخصیص‌داده‌شده در سلول‌های B1:B8). فرض کرده‌ایم که قیمت محصولات هرسال سه درصد افزایش پیدا کرده است. کدام‌یک از سلول‌های کاربرگ سلول‌های وابسته این فرضیات محسوب می‌شوند؟

تصویر 5-16 می‌توانید از ابزارهای formula-auditing برای ردیابی فرمول‌ها در کاربرگ‌های پیچیده استفاده کنید.

 پاسخ به این سؤال انتخاب سلول C8 (سلول حاوی میزان فرضی سه درصد رشد قیمت) و سپس کلیک روی دکمه Trace dependents در گروهFormula Auditing در سربرگ Formula می‌باشد. با انجام این کار نرم‌افزار اکسل مجموعه‌ای از فلشها را (در تصویر 6-16 نشان داده شده) نمایان می‌سازد که به موارد وابسته مستقیم سلولC8 اشاره دارند.

تصویر 6-16 ردیابی مستقیم سلول‌های وابسته

 وقتی‌که روی دکمه Trace Dependents ‌کلیک می‌کنید، اکسل به سلول‌هایی که مستقیماً به گمانه‌های مرتبط با افزایش قیمت وابسته‌اند اشاره می‌کند. در تصویر 6-16 می‌بینید که تنها قیمت واحد  در سال‌های 2 الی 5 است که مستقیماً با این گمانه مرتبط است. همان طور که در تصویر 7-16 نشان داده شده، کلیک کردن بر دکمه Trace Depenents به شکل مکرر، تمامی فرمول‌هایی را که محاسبات آنها به مقدار لازم برای رشد قیمت نیازمند است را نشان می‌دهد.

تصویر 7-16 کلیک بر گزینه Trace Dependents به شکل مکرر تمامی موارد وابسته به گمانه‌ رشد قیمت را نشان می‌دهد.

 می‌بینید که گمانه افزایش قیمت علاوه بر ارزش واحد در سال‌های 2 الی 5 بر درآمد سال‌های 2 الی 5، سود پیش از مالیات، مالیات پرداخته شده، سود پس از مالیات و ارزش خالص فعلی نیز تأثیر گذاشته است. می‌توانید با کلیک بر دکمه Remove Arrows فلش های ظاهر شده را حذف کنید.

ترکیب کلیدهای Ctrl+] تمامی موارد وابسته مستقیم سلول‌های فعال را متمایز می‌کند، درحالی‌که ترکیب کلیدهای Ctrl+ Shift+] تمام موارد وابسته سلول‌های فعال (چه مستقیم و چه غیرمستقیم) را متمایز می‌کند.

به نظر می‌رسد که تحلیلگر مالی در باره محاسبه سود پیش از مالیات سال اول اشتباه کرده است. کدام سلول در کاربرگ نمونه برای این محاسبه مورداستفاده قرار گرفته است؟

در اینجا می‌خواهید موارد پیش آیند سلول B15 ‌را پیدا کنید. موارد پیش آیند در واقع سلول‌هایی هستند که برای محاسبه سود پیش از مالیات سال اول لازم می‌باشند. حالا سلول B15  را انتخاب کنید و بعد روی دکمه Trace Precedents یک‌بار کلیک کنید. فلش هایی را که در تصویر شماره 8-16 نشان داده شده خواهید دید.

تصویر 8-16 موارد پیش آیند مستقیم سود پیش از مالیات سال اول

همان‌طور که می‌بینید سلول‌هایی که مستقیماً برای محاسبه سود پیش از مالیات سال اول نیاز است، سلول‌های حاوی درآمدها و هزینه‌های سال اول هستند. (سود پیش از مالیات سال اول برابر است با درآمد سال اول منهای هزینه‌های سال اول) همان‌طور که در تصویر 9-16 می‌بینید، کلیک مدام روی دکمه Trace Precedents  تمامی موارد پیش آیند مرتبط با سود پیش از مالیات سال اول را نمایان می‌کند. در اینجا تنها گمانه وارد شده که بر سود پیش از مالیات سال اول تأثیر می‌گذارد فروش سال اول، قیمت سال اول و هزینه سال اول است.

تصویر 9-16 کلیک بر دکمه Trace Precedents به طور مکرر به شما تمامی موارد پیش آیند مرتبط با سود پیش از مالیات سال اول را نشان می‌دهد.

ابزار Auditing چگونه وقتی‌که روی داده‌هایی در بیش از یک کاربرگ یا فایل کار می‌کنیم ما را یاری می‌دهد؟

کاربرگ نمونه ساده‌ای در فایل Audittowosheets.xlsx را که در تصویر 10-16 نمایش‌داده‌شده را در نظر بگیرید. فرمول (unit sales*(price–variable cost)–fixed cost)  واقع در کاربرگ Profit سود شرکت را با استفاده از اطلاعات موجود در کاربرگ Data محاسبه می‌کند.

تصویر 10-16 داده‌های مورداستفاده ابزار formula –auditing در چندین کاربرگ

 فرض کنید می‌خوانید موارد پیش آیند فرمول مربوط به سود را بدانید. روی سلول D17 در کاربرگ profit کلیک کرده و بعد روی دکمه Trace Precedentsدر گروه Formula Auditing کلیک می‌کنیم. با انجام این کار یک خط نقطه‌چین، یک فلش و آیکون کاربرگ را می‌بینیم که در تصویر 11-16 هم به شما نشان داده شده‌اند.

تصویر 11-16 نتیجه استفاده از Trace Precedents  با داده‌هایی در چندین کاربرگ

آیکون کاربرگ نشان می‌دهد که موارد پیش آیند مرتبط برای فرمول محاسبه سود در کاربرگ یا فایل دیگری قرار گرفته‌اند. با دو بار کلیک کردن روی خطوط نقطه‌چین، کادر محاوره‌ای Go to نمایش داده می‌شود که آن را در تصویر 12-16 می‌بینید.

تصویر 12-16 با استفاده از کادر محاوره‌ای Go To می‌توانید داده‌های چند کاربرگ را مورد وارسی قرار دهید.

می‌توان بر روی هریک از موارد پیش آیند لیست شده کلیک کرد (سلول‌های D4:D7 در کاربرگData) تا اکسل شما را به پیش آیند انتخاب شده منتقل نماید.

افزونه Inquire چیست و چگونه می باید آن را نصب کرد؟

افزونه Inquire را می‌توان با روش‌های گوناگونی برای بررسی و پیمایش  ساختار کاربرگ‌ها و فایل‌ها به کار برد. همچنین  از این افزونه می‌توان برای مقایسه کاربرگ‌ها و فایل‌ها و بررسی ارتباط‌های آن ها استفاده کرد. افزونه Inquire تنها در نرم‌افزار Office professional Plus  و0020office 365 plus در دسترس می‌باشد. برای نصب کردن افزونه Inquire در سربرگFile روی گزینه Options کلیک کرده و بعد گزینه Add-Ins‌ را انتخاب کنید. سپس در بخش Manage Box گزینه COM.Add-Ins را انتخاب کرده و بعد از کلیک روی گزینه Go در کادر محاوره‌ای ظاهر شده گزینه Inquire Add-In را انتخاب کنید. اگر این افزونه را نمی‌بینید پس نسخه برنامه اکسل شما این قابلیت Inquire ‌را پشتیبانی نمی‌کند.

چگونه می‌توان از افزونه Inquire برای مقایسه کردن فایل‌های اکسل استفاده کرد؟

فرض کنید جیل فایلی بنام Copy-of-Prodmi.xlsx ایجاد کرده و جیمز فایل را تغییر داده و نام آن را به Copy-of-prodmix2.xlsx عوض کرده است. رئیس هیئت‌مدیره شرکت یعنی جوآن نیاز دارد بداند جیمز چه تغییراتی روی فایل اصلی انجام داده است. گزینه  Inquire’s Compare Files همان چیزی است که جوآن بدان نیاز دارد. وقتی‌که روی نوارابزار Inquire‌کلیک کنید (تصویر 13-16 را ببینید) گزینه‌های قابل‌دسترس را مشاهده خواهید کرد. برای استفاده از گزینه Compare files دو فایلی را که می‌خواهید باهم مقایسه کنید را باز نمایید.

تصویر 13-16 نوارابزارInquire

تصویر 14-16 کادر محاوره‌ای مخصوص انتخاب فایل‌ها برای مقایسه

تصویر 15-16 نتایج کلیدی مقایسه فایل‌ها

در اینجا درمی‌یابیم که ستون J به فایل دوم اضافه شده است، داده‌ها در سلولD4 تغییر کرده‌اند و فرمول‌های ستون D14‌ و ِD15 نیز تغییر داده شده‌اند.

چگونه می‌توان از افزونه Inquire برای تحلیل ساختار یک فایل اکسل استفاده کرد؟

فایلی به نام Salessummary.xlsx از داده‌های فایل Salesdata.xlsx برای محاسبه فروش تجمعی هر فصل استفاده می‌نماید. شاید بعد از بازکردن فایل Salessummary.xlsx بخواهید اطلاعات زیادی درباره ساختار این فایل داشته باشید. برای دستیابی به این هدف در نوارابزار Inquire گزینه Workbook Analusis Report را انتخاب کنید. می‌بینید که کادر محاوره‌ای Workbook Analysis Report‌ ظاهر می‌شود و می‌توانید اطلاعاتی را که به آنها علاقه‌مند هستید را انتخاب نمایید. این داده‌ها شامل فایل‌های مرتبط، تمامی فرمول‌ها، ردیف‌ها و ستون‌های پنهان، موارد نام‌گذاری شده و موارد نام‌گذاری شده دارای خطا می‌باشد.

به‌عنوان‌مثال تصویر 6-16 تمامی اطلاعات فایل Salessummary.xlsx را به شما نشان می‌دهد. چنانچه تمایل داشته باشد می‌توانید این اطلاعات را به‌صورت فایل اکسلی صادر نمایید (فایل Salesananalysissummary.xlsx را ببینید)

16-16 خلاصه‌ای از فرمول‌ها در فایل Salesdata.xlsx

 چگونه می‌توان از افزونه Inquire برای تحلیل رابطه بین کاربرگ‌ها و فایل‌ها استفاده کرد؟

بیایید دوباره نگاهی به فایل Audittwosheets.xlsx بیندازیم. اگر بخواهیم بدانیم کاربرگ‌ها چگونه با هم ارتباط دارند می‌توان به‌سادگی فایل را بازکرده و در هر سلولی از نوارابزار Inquire گزینه Worksheet Relationship را انتخاب کرد (تصویر 17-16 را ببینید) این نمودار زیبا به ما نشان می‌دهد که کاربرگ Data کاربرگ Profit را تغذیه می‌کند. اگر چندین کاربرگ را بازکرده باشید و آن‌وقت گزینه Workbook Relationship Summary را انتخاب کنید، می‌توانید خلاصه‌ای از زنجیره‌های ارتباطی بین کاربرگ‌ها را به دست آورید.

تصویر 17-16 خلاصه‌ای از روابط میان کاربرگ‌ها

 چگونه می‌توان از افزونه Inquire برای تحلیل موارد پیش آیند و موارد وابسته یک سلول خاص استفاده کرد؟

فرض کنید می‌خواهید بدانید چه اطلاعاتی برای محاسبه سود در کاربرگ profit در فایل Audittwosheets.xlsx لازم است. برای دستیابی به این اطلاعات کافی است نشانگر ماوس را در سلول D7 از کاربرگ profit قرار داده و از نوارابزار Inquire گزینه Cell Relationship را انتخاب کنید. سپس گزینه Trace Precedents را برگزیده و حالا می‌توانید این نمودار زیبا را که در تصویر 18-16 نشان داده شده را ببینید.

تصویر 18-16 در کاربرگdata برای محاسبه سود نیازمند سه سلول می‌باشیم

 چگونه می‌توان از افزونه Inquire برای پاک‌کردن فرمت سلول استفاده کرد؟

در اکسل 2013 یا نسخه‌های بعد از آن تعداد فرمت‌های انجام شده بر سلول به تعداد 64000 عدد محدود شده است. اگر گزینه Clean Excel Cell Formatting‌ را از نوار ابزار Inquire انتخاب کنید، ابزار Inquire فرمت سلول‌هایی که در کاربرگ قبل از آخرین سلولی که خالی نباشد را حذف می‌کند. مثلاً اگر ردیف 10000 آخرین ردیف در یک کاربرگ باشد فرمت تمام سلول‌های زیر ردیف 10000 حذف خواهد شد و این مسئله به شکل بی خطری تعداد فرمت‌های موجود در فایل شما را کاهش می‌دهد.

مسئله‌های این فصل:

در مثال مربوط به تعیین ارزش خالص فعلی اتومبیل، موارد زیر را تعیین کنید:

1. موارد وابسته مستقیم و تمام موارد وابسته نرخ بهره.
2. موارد وابسته مستقیم و تمامی موارد وابسته نرخ مالیات.
3. موارد پیش آیند مستقیم و تمامی موارد پیش آیند فروش واحدهای سال چهارم.
4. موارد پیش آیند مستقیم و تمامی موارد پیش آیند هزینه‌های سال سوم.

در فایلی به نام Productmix.xlsx موارد زیر را تعیین کنید:

1. موارد پیش آیند مربوط به سود.
2. موارد وابسته مربوط به میزان تولید محصول شماره 1.