Amirsayyafi
2012/11/23, 16:08
داشتم فکر میکردم الگویی برای فرمول نویسی با کمک همدیگه به دست بیاریم. تا در این راه برامون کمکی باشه.
با اجازه بزرگترها اولین چراغ رو خودم روشن میکنم. سعی میکنم هر بخش حداقل یک مثال به همراه داشته باشد تا موضوعی بودن اون روشنتر باشه.
گام اول: تحلیل مسئله
برای فرمول نویسی به نظر من اولین کار تحلیل مسئله است. ببینید که چه اطلاعاتی دارید و چه چیزی را میخواهید به دست بیاورید. برای این کار باید سؤالاتی رو از خودتون بپرسید. سؤالات در دو دسته تقسیم میشن:
سری اول سؤالات پیرامون اطلاعاتی است که در اختیار دارید. بهرحال این اطلاعات محدود هستند و حتماً قابل دسته بندی. این پرسشها رو از خودتون داشته باشید:
آیا اطلاعات فعلی برای رسیدن به این خواسته کافی هستند یا به اطلاعات دیگری نیاز دارید؟
برای رسیدن به نتیجه کدام بخش از این اطلاعات به کار می آیند؟
آیا به جدولهای کمکی نیاز دارید؟
داخل پرانتز 1 : جدولهای کمکی (کوچک اما پرکاربرد)
گاهی برای حل مسئله به جدولهای کمکی نیاز پیدا میکنیم. این جدولها میتونن یکی از حالتهای زیر رو داشته باشند:
جدولهایی که اطلاعاتشان را از خود اطلاعات اولیه کسب کرده اند. بیشتر استفاده از این جدولها با فرمول Vlookup است. فرض کنید شما از یک بانک 30 هزار رکوردی که حاوی ستونی با اطلاعات فرضاً 10 آیتم پرشده و میخواهید به هر آیتم کدی اختصاص بدید. بنابراین جدول کمکی شما شامل 10 سطر با دو ستون میشود.
جدولهایی که توسط فرمول از اطلاعات اولیه به دست آمده اند. بهترین مثال برای این بخش دفترچه های تلفنی است که آقای کمیل و من در سایت قرار دادیم. در اونجا ما مجبور شدیم تا ستونی ایجاد کنیم تا در مرحله بعد این ستون کمکی باشه برای حل مسئله مون.
http://forum.exceliran.com/thread-2150.html
جدولهایی که محتویاتشان در اطلاعات اولیه نیست ولی در اینجا میتوانند کاربرد داشته باشند. یکی از مثالهای عملی این جدولها تبدیل واحدهاست. گاهی شما مجبورید برای حل مسئله واحدهاتون رو به هم تبدیل کنید.
سری دوم سؤالاتتون باز هم به اطلاعاتتون برمیگرده. در مرحله قبل شما شناخت کافی از اطلاعات کسب کردید. اما کار ما با این اطلاعات هنوز تمام نشده (و راستشو بخواهید تا پایان کار هم تمام نخواهد شد. اطلاعات بیچاره!). در اینجا باید رابطه و ماهیت اطلاعات رو مورد بررسی قرار بدیم. پرسشهایی از این دست میتونه برای حل مسئله کمک خوبی باشه:
آیا برای فرمول نویسی در یک سلول تمام سلولهای اطلاعات درگیر مسئله هستند؟ (پس حتماً از فرمولهای آرایه ای باید استفاده کنید.)
آیا سلولهایی که درگیر مسئله هستند با هم رابطه منطقی دارند؟ مثلاً همگی در یک ستون قرار دارند؟ یا مثلاً آیا برخی از طریق محاسبه از برخی دیگر حاصل شده اند؟
آیا سلولهایی که درگیر مسئله هستند همگی از ویژگی خاصی برخوردارند (مثلاً همگی متن هستند، یا همگی اعداد 6 رقمی هستند یا همگی تاریخ هستند، ...) یا اینکه مثلاً برخی سلولها خالی و برخی پر هستند؟
جنس اطلاعات چیست؟ عدد و رقم؟ تاریخ؟ ساعت؟ متن؟ یا ترکیبی از دو یا چند جنس؟
آیا فرمول واحدی بر کل ستون یا سطری از اطلاعات حاکم است؟
آیا میتوان با ایجاد سلولهای کمکی مسئله را ساده تر حل کرد؟
طرز چینش اطلاعات به چه صورتی است؟ ستونی؟ سطری؟ یا ماتریسی؟
آیا اطلاعات در شیتهای مختلف پراکنده هستند؟
گام دوم: ابزارها
خب حالا ما شناخت کافی پیرامون مسئله پیدا کردیم و کارمون در این گام اینه که مشخص کنیم به چه ابزاری احتیاج داریم. به برخی از این احتیاجات اشاره میکنیم:
ممکنه در یک مسئله ما به ماکزیمم و می نمیمم احتیاج داشته باشیم.
ممکنه به IF احتیاج داشته باشیم. (این یکی رو تقریباً همیشه لازم داریم.)
ممکنه فرمولهایی رو لازم داشته باشیم که جستجو کنند.
ممکنه فرمولهایی رو لازم داشته باشیم که مرتب کنند.
ممکنه فرمولهایی رو لازم داشته باشیم که تبدیل کنند.
ممکنه فرمولهایی رو لازم داشته باشیم که مقایسه کنند.
ممکنه فرمولهایی رو لازم داشته باشیم که محاسبه کنند.
...
دقت داشته باشید که ابزارها تنها شامل فرمولها نمیشن. رنگ کردن اتوماتیک (Conditional Formatting)، انتخاب شیوه (Custom-Format Cell)، استفاده از Data Validation، استفاده از Name Manager، و ... ابزارهایی هستند که میتونن در حل مسئله یا بهینه کردن یک پروژه، نقش مهمی ایفا کنند.
در کنار فرمولها و ابزارهای اکسل باید با فرمولهایی که کمک میکنند تا از یک سنجه اطلاعات به سنجه دیگری از همان اطلاعات برسیم، آشنا باشیم.
از قویترین این فرمولها میشه از Indirect، Row، COLUMN، Address یاد کرد. این فرمولها کمک میکنند تا شما ویژگی از یک سلول را به دست بیارید که برای مسئله تون کاربردی هستش.
مثلاً شما برای ترتیب میتونید از شماره سطر سلولتون استفاده کنید. یا اگر از طریق شماره سطر به سلولی دست پیدا کردید با فرمول Indirect محتویات اون سلول را فراخوانی کنید.
گام سوم: ترکیب فرمولها
در اینجا باید بتونید با ترکیب موادی که در بالا دارید آش خوبی بپزید. مسئله رو مرور کنید ببینید برای رسیدن به پاسخ چه مراحلی را باید طی کنید. در اینجا علاوه بر دانش فرمولها باید از حوصله و شهود خوبی برخوردار باشید.
نمیگم ممکنه بلکه میگم حتماً شما چندین بار در حل یک مسئله مجبورید که فرمولهاتون رو عوض کنید. چون در مراحل اولیه وقتی اونچه رو که در ذهنتون ساختید وارد اکسل میکنید میبیند فرمولتون به تناقضات منطقی برخورد میکنه. در اینجا باید خیلی صبور باشید و دوباره امتحان کنید.
گاهی با جابجا کردن فرمولها به نتیجه دلخواه میرسید. مثلاً اول Max را نوشته اید بعد Indirect را، جواب نگرفتید بعد میبینید که با جابجا کردن این دو فرمول نتیجه مطلوب به دست میاد. بهرحال بدونید که بارها و بارها با کلماتی چون #N/A ، یا #VALUE! ، یا پیغامهای ناامیدکننده دیگه ای از این دست روبرو میشید. هر بار که با امید کلید Enter رو فشار میدید در نظر داشته باشید که ممکنه نکته ای رو در نظر نگرفته باشید و این فرمول باوفاتونه که اونو براتون پیدا میکنه و بهتون یادآور میشه. پس به هر شکست به دیده یک تجربه نگاه کنید و مطمئن باشید که این تجربیات در گذر زمان چون گوهر براتون ارزشمند میشه. تاجایی که خیلی از خطاهای احتمالی رو پیش از فشردن کلید نهایی Enter پیش بینی میکنید و در اون موقع کسی که کنارتون نشسته با تعجب ازتون میپرسه: «چطور تونستی این کار رو بکنی؟!» خب بهش بگید که این دانش، حاصل نبردهای تن به تن شما با خطاهای اکسله.با انواع این خطاها میتونین تو لینک زیر که استاد محترم جناب آقای علیمحمدی زحمتشو کشیدن آشنا بشین:
http://forum.exceliran.com/thread-586.html?highlight=%D8%AE%D8%B7%D8%A7%D9%87%D8%A7
داخل پرانتز 2 : امدادگران اکسل
در اکسل ابزارهایی هست که در مسیر حل مسئله میتونه کمک خوبی براتون باشه که با اجازه اساتید اسمشونو میذاریم امدادگران اکسل.
یکی از این امدادگران کلید Evaluate Formula هست که فرمولتون رو گام به گام براتون نمایش میده. (البته بماند که گاهی از روی راه حلها پرش میکنه! اما خب بازم دستش درد نکنه ما به همین هم راضی هستیم.)
دیگه میتونیم از Conditional Formatting یاد کنیم.
کلید Show Formulas هم علیرغم مظلومیتش، گاهی خیلی به درد بخور میشه.
در نهایت نکته مهمی رو یادآور میشم. سعی کنید یک مجموعه از فرمولهایی که ساخته اید یا از جایی کسب کرده اید، فراهم کنید. در بسیار از مواقع یک فرمول میتونه به عنوان شروع کار بسیار مناسب باشه. داشتن این مجموعه علاوه بر اینکه باعث میشه تا در خیلی از مسائلتون کلی جلو بیفتید، کمکتون میکنه به دستاوردها و دیدگاههای جدیدی برسید.
خب کامل کردن این بحث رو به عهده دوستان میگذارم. اگه بشه در آینده چند فرمول رو تحلیل میکنیم تا روند رسیدن به جواب رو در اونها بررسی کنیم.
با اجازه بزرگترها اولین چراغ رو خودم روشن میکنم. سعی میکنم هر بخش حداقل یک مثال به همراه داشته باشد تا موضوعی بودن اون روشنتر باشه.
گام اول: تحلیل مسئله
برای فرمول نویسی به نظر من اولین کار تحلیل مسئله است. ببینید که چه اطلاعاتی دارید و چه چیزی را میخواهید به دست بیاورید. برای این کار باید سؤالاتی رو از خودتون بپرسید. سؤالات در دو دسته تقسیم میشن:
سری اول سؤالات پیرامون اطلاعاتی است که در اختیار دارید. بهرحال این اطلاعات محدود هستند و حتماً قابل دسته بندی. این پرسشها رو از خودتون داشته باشید:
آیا اطلاعات فعلی برای رسیدن به این خواسته کافی هستند یا به اطلاعات دیگری نیاز دارید؟
برای رسیدن به نتیجه کدام بخش از این اطلاعات به کار می آیند؟
آیا به جدولهای کمکی نیاز دارید؟
داخل پرانتز 1 : جدولهای کمکی (کوچک اما پرکاربرد)
گاهی برای حل مسئله به جدولهای کمکی نیاز پیدا میکنیم. این جدولها میتونن یکی از حالتهای زیر رو داشته باشند:
جدولهایی که اطلاعاتشان را از خود اطلاعات اولیه کسب کرده اند. بیشتر استفاده از این جدولها با فرمول Vlookup است. فرض کنید شما از یک بانک 30 هزار رکوردی که حاوی ستونی با اطلاعات فرضاً 10 آیتم پرشده و میخواهید به هر آیتم کدی اختصاص بدید. بنابراین جدول کمکی شما شامل 10 سطر با دو ستون میشود.
جدولهایی که توسط فرمول از اطلاعات اولیه به دست آمده اند. بهترین مثال برای این بخش دفترچه های تلفنی است که آقای کمیل و من در سایت قرار دادیم. در اونجا ما مجبور شدیم تا ستونی ایجاد کنیم تا در مرحله بعد این ستون کمکی باشه برای حل مسئله مون.
http://forum.exceliran.com/thread-2150.html
جدولهایی که محتویاتشان در اطلاعات اولیه نیست ولی در اینجا میتوانند کاربرد داشته باشند. یکی از مثالهای عملی این جدولها تبدیل واحدهاست. گاهی شما مجبورید برای حل مسئله واحدهاتون رو به هم تبدیل کنید.
سری دوم سؤالاتتون باز هم به اطلاعاتتون برمیگرده. در مرحله قبل شما شناخت کافی از اطلاعات کسب کردید. اما کار ما با این اطلاعات هنوز تمام نشده (و راستشو بخواهید تا پایان کار هم تمام نخواهد شد. اطلاعات بیچاره!). در اینجا باید رابطه و ماهیت اطلاعات رو مورد بررسی قرار بدیم. پرسشهایی از این دست میتونه برای حل مسئله کمک خوبی باشه:
آیا برای فرمول نویسی در یک سلول تمام سلولهای اطلاعات درگیر مسئله هستند؟ (پس حتماً از فرمولهای آرایه ای باید استفاده کنید.)
آیا سلولهایی که درگیر مسئله هستند با هم رابطه منطقی دارند؟ مثلاً همگی در یک ستون قرار دارند؟ یا مثلاً آیا برخی از طریق محاسبه از برخی دیگر حاصل شده اند؟
آیا سلولهایی که درگیر مسئله هستند همگی از ویژگی خاصی برخوردارند (مثلاً همگی متن هستند، یا همگی اعداد 6 رقمی هستند یا همگی تاریخ هستند، ...) یا اینکه مثلاً برخی سلولها خالی و برخی پر هستند؟
جنس اطلاعات چیست؟ عدد و رقم؟ تاریخ؟ ساعت؟ متن؟ یا ترکیبی از دو یا چند جنس؟
آیا فرمول واحدی بر کل ستون یا سطری از اطلاعات حاکم است؟
آیا میتوان با ایجاد سلولهای کمکی مسئله را ساده تر حل کرد؟
طرز چینش اطلاعات به چه صورتی است؟ ستونی؟ سطری؟ یا ماتریسی؟
آیا اطلاعات در شیتهای مختلف پراکنده هستند؟
گام دوم: ابزارها
خب حالا ما شناخت کافی پیرامون مسئله پیدا کردیم و کارمون در این گام اینه که مشخص کنیم به چه ابزاری احتیاج داریم. به برخی از این احتیاجات اشاره میکنیم:
ممکنه در یک مسئله ما به ماکزیمم و می نمیمم احتیاج داشته باشیم.
ممکنه به IF احتیاج داشته باشیم. (این یکی رو تقریباً همیشه لازم داریم.)
ممکنه فرمولهایی رو لازم داشته باشیم که جستجو کنند.
ممکنه فرمولهایی رو لازم داشته باشیم که مرتب کنند.
ممکنه فرمولهایی رو لازم داشته باشیم که تبدیل کنند.
ممکنه فرمولهایی رو لازم داشته باشیم که مقایسه کنند.
ممکنه فرمولهایی رو لازم داشته باشیم که محاسبه کنند.
...
دقت داشته باشید که ابزارها تنها شامل فرمولها نمیشن. رنگ کردن اتوماتیک (Conditional Formatting)، انتخاب شیوه (Custom-Format Cell)، استفاده از Data Validation، استفاده از Name Manager، و ... ابزارهایی هستند که میتونن در حل مسئله یا بهینه کردن یک پروژه، نقش مهمی ایفا کنند.
در کنار فرمولها و ابزارهای اکسل باید با فرمولهایی که کمک میکنند تا از یک سنجه اطلاعات به سنجه دیگری از همان اطلاعات برسیم، آشنا باشیم.
از قویترین این فرمولها میشه از Indirect، Row، COLUMN، Address یاد کرد. این فرمولها کمک میکنند تا شما ویژگی از یک سلول را به دست بیارید که برای مسئله تون کاربردی هستش.
مثلاً شما برای ترتیب میتونید از شماره سطر سلولتون استفاده کنید. یا اگر از طریق شماره سطر به سلولی دست پیدا کردید با فرمول Indirect محتویات اون سلول را فراخوانی کنید.
گام سوم: ترکیب فرمولها
در اینجا باید بتونید با ترکیب موادی که در بالا دارید آش خوبی بپزید. مسئله رو مرور کنید ببینید برای رسیدن به پاسخ چه مراحلی را باید طی کنید. در اینجا علاوه بر دانش فرمولها باید از حوصله و شهود خوبی برخوردار باشید.
نمیگم ممکنه بلکه میگم حتماً شما چندین بار در حل یک مسئله مجبورید که فرمولهاتون رو عوض کنید. چون در مراحل اولیه وقتی اونچه رو که در ذهنتون ساختید وارد اکسل میکنید میبیند فرمولتون به تناقضات منطقی برخورد میکنه. در اینجا باید خیلی صبور باشید و دوباره امتحان کنید.
گاهی با جابجا کردن فرمولها به نتیجه دلخواه میرسید. مثلاً اول Max را نوشته اید بعد Indirect را، جواب نگرفتید بعد میبینید که با جابجا کردن این دو فرمول نتیجه مطلوب به دست میاد. بهرحال بدونید که بارها و بارها با کلماتی چون #N/A ، یا #VALUE! ، یا پیغامهای ناامیدکننده دیگه ای از این دست روبرو میشید. هر بار که با امید کلید Enter رو فشار میدید در نظر داشته باشید که ممکنه نکته ای رو در نظر نگرفته باشید و این فرمول باوفاتونه که اونو براتون پیدا میکنه و بهتون یادآور میشه. پس به هر شکست به دیده یک تجربه نگاه کنید و مطمئن باشید که این تجربیات در گذر زمان چون گوهر براتون ارزشمند میشه. تاجایی که خیلی از خطاهای احتمالی رو پیش از فشردن کلید نهایی Enter پیش بینی میکنید و در اون موقع کسی که کنارتون نشسته با تعجب ازتون میپرسه: «چطور تونستی این کار رو بکنی؟!» خب بهش بگید که این دانش، حاصل نبردهای تن به تن شما با خطاهای اکسله.با انواع این خطاها میتونین تو لینک زیر که استاد محترم جناب آقای علیمحمدی زحمتشو کشیدن آشنا بشین:
http://forum.exceliran.com/thread-586.html?highlight=%D8%AE%D8%B7%D8%A7%D9%87%D8%A7
داخل پرانتز 2 : امدادگران اکسل
در اکسل ابزارهایی هست که در مسیر حل مسئله میتونه کمک خوبی براتون باشه که با اجازه اساتید اسمشونو میذاریم امدادگران اکسل.
یکی از این امدادگران کلید Evaluate Formula هست که فرمولتون رو گام به گام براتون نمایش میده. (البته بماند که گاهی از روی راه حلها پرش میکنه! اما خب بازم دستش درد نکنه ما به همین هم راضی هستیم.)
دیگه میتونیم از Conditional Formatting یاد کنیم.
کلید Show Formulas هم علیرغم مظلومیتش، گاهی خیلی به درد بخور میشه.
در نهایت نکته مهمی رو یادآور میشم. سعی کنید یک مجموعه از فرمولهایی که ساخته اید یا از جایی کسب کرده اید، فراهم کنید. در بسیار از مواقع یک فرمول میتونه به عنوان شروع کار بسیار مناسب باشه. داشتن این مجموعه علاوه بر اینکه باعث میشه تا در خیلی از مسائلتون کلی جلو بیفتید، کمکتون میکنه به دستاوردها و دیدگاههای جدیدی برسید.
خب کامل کردن این بحث رو به عهده دوستان میگذارم. اگه بشه در آینده چند فرمول رو تحلیل میکنیم تا روند رسیدن به جواب رو در اونها بررسی کنیم.