چگونه فرمول نویسی کنیم

[Amirsayyafi]

داشتم فکر میکردم الگویی برای فرمول نویسی با کمک همدیگه به دست بیاریم. تا در این راه برامون کمکی باشه.
با اجازه بزرگترها اولین چراغ رو خودم روشن میکنم. سعی میکنم هر بخش حداقل یک مثال به همراه داشته باشد تا موضوعی بودن اون روشنتر باشه.


گام اول: تحلیل مسئله

برای فرمول نویسی به نظر من اولین کار تحلیل مسئله است. ببینید که چه اطلاعاتی دارید و چه چیزی را میخواهید به دست بیاورید. برای این کار باید سؤالاتی رو از خودتون بپرسید. سؤالات در دو دسته تقسیم میشن:

سری اول سؤالات پیرامون اطلاعاتی است که در اختیار دارید. بهرحال این اطلاعات محدود هستند و حتماً قابل دسته بندی. این پرسشها رو از خودتون داشته باشید:

آیا اطلاعات فعلی برای رسیدن به این خواسته کافی هستند یا به اطلاعات دیگری نیاز دارید؟
برای رسیدن به نتیجه کدام بخش از این اطلاعات به کار می آیند؟
آیا به جدولهای کمکی نیاز دارید؟


داخل پرانتز 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 هم علیرغم مظلومیتش، گاهی خیلی به درد بخور میشه.



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



خب کامل کردن این بحث رو به عهده دوستان میگذارم. اگه بشه در آینده چند فرمول رو تحلیل میکنیم تا روند رسیدن به جواب رو در اونها بررسی کنیم.

[mahsa shayesteh]

RE: چگونه فرمول نویسی کنیم

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

[Amirsayyafi]

RE: چگونه فرمول نویسی کنیم

تکنیکی که بهش اشاره کردید خوبه فقط باید خیلی مراقب باشیم چرا که این روش در بعضی جاها بشدت ما رو گول میزنه تا روشهای ساده تر رو نبینیم. بطوری که گاهی فرمولی که تنها با 11-12 کاراکتر میشه نوشت رو با بیش از 300 کاراکتر مینویسیم.

[ستایش احدی]

سلام. من یه سئوال داشتم راجع به فرمول نویسی. بعد از اینکه فرمول رو می دم جواب رو فقط توی یک درایه به من میده ولی من می خوام تو همه اون ستون بده. البته می دونم با کشیدن درایه می شه این کارو کرد اما ستون من حدود 20000 داده توش هستش کلی طول میکشه کشیدنش. چی کار باید کنم؟

[maziardm]

سلام. من یه سئوال داشتم راجع به فرمول نویسی. بعد از اینکه فرمول رو می دم جواب رو فقط توی یک درایه به من میده ولی من می خوام تو همه اون ستون بده. البته می دونم با کشیدن درایه می شه این کارو کرد اما ستون من حدود 20000 داده توش هستش کلی طول میکشه کشیدنش. چی کار باید کنم؟

سوال جالبیه. دوستان مثل همیشه راهنمایی کنند. با تشکر

[Alireza]

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

[Alireza]

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

[~M*E*H*D*I~]

سلام. من یه سئوال داشتم راجع به فرمول نویسی. بعد از اینکه فرمول رو می دم جواب رو فقط توی یک درایه به من میده ولی من می خوام تو همه اون ستون بده. البته می دونم با کشیدن درایه می شه این کارو کرد اما ستون من حدود 20000 داده توش هستش کلی طول میکشه کشیدنش. چی کار باید کنم؟

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

[ستایش احدی]

یه سئوال دیگه هم داشتم حالا اگه فرمول نداشته باشه ولی بخوام مثلا همون 20000 درایه رو انتخاب کنم چکار باید کنم.بازم به غیر کشیدن درایه ها.

[khakzad]

برای انتخاب محدوده از سل های پیوسته م یتوانید از کلیدهای ترکیبی زیر استفاده کنید:
ctrl+shift و یکی از کلیدهای جهات چهارگانه

[aminafz]

با سلام خدمت دوستان عزیز،
یه کاری میخوام بکنم که تاحالا نرکده بودم به همین جهت راهنمایی میخوام.
من توی ردیفام رکوردی دارم که به وسیله کامبوباکس مقدار دهی میشه مثلا مقادیر کاربر 1 و کاربر 2 رو داره
رکورد دیگری هم داریم که یک عدد رو قبول میکنه، مثلا مبلغی که پرداخت کرده.
من مخواستم توی یک سلول جمع مقادیر پرداختی کاربر1 و تو سلول دیگری جمع مبالغ پرداختی کاربر2 رو داشته باشم.
لطفا راهنمایی کنید چطور میشه این کار رو انجام داد.
ممنون.

[khakzad]

سلام
از sumif باید استفاده کنید
sum range رو محدوده مبالغ قرار بدید
criteria رو یک سل خالی بذارید
criteria range هم همون محدوده ای که داده های 1 و 2 قرار دارند
بعداد داخل سل خالی بنویسید 1===> محموع مبالغ مربوط به 1 رو میده
بزنید 2 هم مقادیر مربوط به 2

[ieumts]

با سلام خدمت دوستان
مطلبتون تقریبا کامل بود. خیلی ممنون
یه نکته ای که اشاره کردین و گفتین که ممکنه هی مجبور بشین فرمول رو عوض کنین، در موردش باید این رو عرض کنم که بنظرم اگه شما مساله تونو بشکنید، و بقول یکی از دوستان که در مطالب بالا تلویحا اشاره کردن که تیکه تیکه توی سلولهای مختلف آزمایش میکنن، خیلی زودتر به جواب میرسین.
مثلا فرض کنید میخاین مجموع محدوده ای رو بدست بیارید که این محدوده خودش خروجی یک فرموله
مسلما میرین سراغ توابعی مثل indirect یا offset که یک محدوده برگردونن تا اینکه مثلا از vlookup استفاده کنین.
بنابراین همونجوری که evaluate formula از داخلی ترین فرمول شروع میکنه به تحلیل، شما هم باید فکر کنین که تهش قراره به چی برسین و بعد هی فرمول رو بسطش بدید و به قولی یک مگافرمول بنویسید
این شکستن، توی فرمول نویسیهایی که از توابع متنی و جستجو استفاده میکنه و بعضا به صورت آرایه ای هم هست، خیلی جواب میده

[Yanehsar]

با سلام
احتراماً خواهشمند است بفرمائید جهت ورود اعداد با تعداد ارقام مشخص مثلاً کد ملی فقط با 10 رقم و امثال این باید چکار کنم تا از ورود کد ملی با تعداد ارقام کمتر یا بیشتر از 10 جلوگیری شود
با تشکر

[Behnam]

با سلام
احتراماً خواهشمند است بفرمائید جهت ورود اعداد با تعداد ارقام مشخص مثلاً کد ملی فقط با 10 رقم و امثال این باید چکار کنم تا از ورود کد ملی با تعداد ارقام کمتر یا بیشتر از 10 جلوگیری شود
با تشکر

از ابزار data validation در تب data میتوانید استفاده کنید
از فروریز allow گزینه text length رو انتخاب کنید و سپس از فروریز data گزینه equal to و بعد در قسمت length مثلا برای کد ملی عدد 10 را وارد کنید