نکته: در این مطلب فرض شده است که شما با نوشتن تابع if ، if(or) ,if(and) در اکسل آشنا هستید. اگر با نحوه نوشتن اینگونه فرمول های آشنایی ندارید میتوانید به  IF formulas و IF(OR and IF(AND formulas و همچنین در اینجا فرض شده است که با load کردن داده در power query و اضافه کردن custom column آشنایی دارید. در غیر اینصورت میتوانید به مطالب زیر مراجعه کنید و آنها را یاد بگیرید:

تابع if در اکسل | مثال ها و کاربردها

تابع IF اکسل همراه با شرط های And/or چندگانه در اکسل، if های تو در تو و غیره

اضافه کردن یک ستون در پاور کوئری

شروع کار با مجموعه get&transform (پاور کوئری) در اکسل ۲۰۱۶

حال، به بررسی مثال میپردازیم:

در زیر داده های مربوط به سفارشات را در power query در اختیار داریم:

میخواهیم یک ستون با نام ” وضعیت تحویل(Delivery Status)” اضافه کنیم تا وضعیت تحویل کالا به مشتری را نشان دهد.

منطق این ستون به این شکل است که ؛ اگر سلول مربوط به تاریخ تحویل خالی نباشد، به این معنی است که کالا تحویل داده شده است.

دستور if در power query

در پنجره Add column بر روی Conditional Column کلیک کنید. دیالوگ باکس که در زیر مشاهده میکنید باز میشود. این باکس از یک منو ساده برخوردار است که به شما در نوشتن دستور if کمک میکند (نکته: در power query، کلمه “null” به معنای خالی است)

توجه کنید که چگونه میتوان سطر if را در دیالوگ باکس خواند و آیا این سطر به انگلیسی صحیح است یا خیر؟

پس از آن که ok را کلیک کردید، یک ستون برای وضعیت تحویل ایجاد میشود.

و اگر کد M  را در Advanced editor بررسی کنید، مشاهده میکنید که فرمول زیر برای شما نوشته شده است (قسمت آبی رنگ)

متاسفانه، برای هر سناریو if نمیتوانید از conditional column(ستون شرطی)  استفاده کنید. در بعضی مواقع میبایست از از دیالوگ باکس Add Custom Column که در مثال بعد توضیح داده خواهد شد استفاده کنید و آن را خودتان بنویسید. در این موارد می بایست از تفاوت های کلیدی بین دستور if در اکسل آگاهی داشته باشید:

• در power query، if به صورت حروف کوچک است.
• به جای استفاده از کاما برای جداسازی value_if_true و value_if_false  از کلمات than  و else استفاده کنیم.
• در power query، null به معنای خالی است.
• فرمول های power query نسبت به حروف بزرگ و کوچک حساس هستند.

دستورات if or در power query

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

بنابراین، در اینجا می بایست از دستور if or استفاده کرد.

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

متاسفانه، GUI (راهنما) مناسب برای کمک به ما در نوشتن دستور if or وجود ندارد، بنابراین در این مورد، از دکمه Add Column > Add Custom Column استفاده میکنیم:

دیالوگ باکس باز میشود و شما می بایست نامی برای ستون خود انتخاب کنید و فرمول خود را در قسمت ‘Custom column formula:’ وارد کنید.

فرمول را به صورت دقیقتر بررسی کنید:

if  [Delivery Date] <> null or  [Order Type] = “In Store Pickup”  then “Completed” else null

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

اگر تاریخ تحویل خالی نبود، یا نوع سفارش ” درون فروشگاهی” است، مقدار ‘Completed’ را برگردان، در غیر اینصورت سلول خالی نگه داشته شود.

هر کدام از آزمون های منطقی بوسیله ‘or’ تفکیک میشود. خیلی آسان و ساده بود.

دستور if and در power query

دستور if and نیز بسیار آسان است. مثال زیر در مورد حقوق و دستمزد کارکنان را در نظر بگیرید:

میخواهیم یک ستون اضافه کنیم که میزان پاداش برای کارکنان اجرایی را محاسبه کند.

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

اگر level، برابر با executive و target met برابر با yes باشد، آنگاه پاداش را به صورت salary*0.05 محاسبه کن در غیر اینصورت پاداش برابر با صفر.

مثل دستور if or، می بایست از Add Column > Add Custom Column برای وارد کردن فرمول استفاده کنیم:

if [Level] = “Executive” and [Target Met] = “Yes” then [Salary] * .05 else 0

حال، میتوانید نتایج را در ستون پاداش(bonus) زیر مشاهده کنید:

صبر کنید، در مورد پاداش مدیران چه باید کرد؟ در حقیقت، مدیران در صورت انجام کار خود 10 درصد پاداش میگیرند.

ما میتوانیم با استفاده از if های تو در تو دو سطح پاداش را در نظر بگیریم.

دستور if تو در تو در power query

با استفاده از همان داده های نمونه، دستور ما به فارسی به این صورت میشود:

اگر، level برابر با executive باشد و target met برابر با yes باشد، آنگاه پاداش را به صورت salary*5% محاسبه کن در غیر اینصورت، اگر level برابر با manager باشد، و target met برابر با yes باشد، آنگاه پاداش به صورت salary*10% محاسبه شود، در غیر اینصورت پاداش برابر با صفر

فرمول به شکل زیر خواهد بود:

if [Level] = “Executive” and [Target Met] = “Yes” then [Salary]*.05 else

if [Level] = “Manager” and [Target Met] = “Yes” then [Salary]*.10 else 0

نکته کلیدی این است که دستور if بعدی را بعد از else قرار دهیم. من فرمول را در دو سطر قرار دادم تا خواندن if های تو در توی آنا آسان باشد. شما میتوانید بعد از هر else ، if های بیشتری قرار دهید.

[/vc_column_text][/vc_column][/vc_row]