[فهرست آموزش]
لینک فیلم برای نمایش یا دانلود https://my.uupload.ir/p/yoWVnvkD
روشهای انتقال و تعمیم فرمول در اکسلدر اکسل، سرعت عمل یعنی حرفهای بودن! یکی از پرتکرارترین کارها برای ما مدرسان و تحلیلگران مالی، تعمیم دادن یک فرمول به سایر سلولهاست. به جای استفاده مداوم از موس و درگ کردن (Drag)، با استفاده از ترکیب کلیدهای میانبر زیر، در کمتر از چند ثانیه جداول پیچیده خود را کامل کنید.
کلیدهای میانبر جادویی برای کپی و تعمیمCtrl + Shift + Endکاربرد: انتخاب سریع محدودهتوضیح: از سلول جاری تا آخرین سلول فعال در سمت راست و پایین جدول را به صورت یکجا انتخاب میکند. برای آمادهسازی محدوده جهت اعمال فرمولها عالی است.
Ctrl + D (Fill Down)کاربرد: کپی رو به پایینتوضیح: فرمول یا محتوای اولین سلولِ محدوده انتخابی را در تمام سلولهای پایینتر آن تکثیر میکند.
Ctrl + R (Fill Right)کاربرد: کپی رو به راستتوضیح: فرمول یا محتوای اولین سلول در سمت چپِ محدوده را در تمام سلولهای سمت راست آن کپی میکند. (مثل پر کردن درصدهای رشد در ردیفهای جدول تصویر بالا).
Alt + H + F + I + L (Fill Left)کاربرد: کپی رو به چپتوضیح: این یک میانبر ترکیبی از منوی Ribbon است. زمانی کاربرد دارد که میخواهید فرمول را از سمت راست به سمت چپ تعمیم دهید (بسیار کاربردی برای جداول فارسی که جهتشان راستبهچپ است).
اکسلدان شو | آموزش اکسل ویژه بازار کار
دوره ۴ مرحله ای LPCF مستری اکسل
لینک فیلم برای نمایش یا دانلود https://my.uupload.ir/p/yoWVnvkD روشهای انتقال و تعمیم فرمول در اکسلدر اکسل، سرعت عمل یعنی حرفهای بودن! یکی از پرتکرارترین کارها برای ما مدرسان و تحلیلگران مالی، تعمیم دادن یک فرمول به سایر سلولهاست. به جای استفاده مداوم از موس و درگ کردن (Drag)، با استفاده از ترکیب کلیدهای میانبر زیر، در کمتر از چند ثانیه جداول پیچیده خود را کامل کنید. کلیدهای میانبر جادویی برای کپی و تعمیمCtrl + Shift + Endکاربرد: انتخاب سریع محدودهتوضیح: از سلول جاری تا آخرین سلول فعال در سمت راست و پایین جدول را به صورت یکجا انتخاب میکند. برای آمادهسازی محدوده جهت اعمال فرمولها عالی است.Ctrl + D (Fill Down)کاربرد: کپی رو به پایینتوضیح: فرمول یا محتوای اولین سلولِ محدوده انتخابی را در تمام سلولهای پایینتر آن تکثیر میکند.
Ctrl + R (Fill Right)کاربرد: کپی رو به راستتوضیح: فرمول یا محتوای اولین سلول در سمت چپِ محدوده را در تمام سلولهای سمت راست آن کپی میکند. (مثل پر کردن درصدهای رشد در ردیفهای جدول تصویر بالا).
Alt + H + F + I + L (Fill Left)کاربرد: کپی رو به چپتوضیح: این یک میانبر ترکیبی از منوی Ribbon است. زمانی کاربرد دارد که میخواهید فرمول را از سمت راست به سمت چپ تعمیم دهید (بسیار کاربردی برای جداول فارسی که جهتشان راستبهچپ است).
اکسلدان شو | آموزش اکسل ویژه بازار کار
دوره ۴ مرحله ای LPCF مستری اکسل
۵۲۲اکسلدان شو | آموزش اکسل ویژه بازار کار [فهرست آموزش] لینک فیلم برای نمایش یا دانلود https://my.uupload.ir/p/yoWVnvkD روشهای انتقال و تعمیم فرمول در اکسل در اکسل، سرعت عمل یعنی حرفهای بودن! یکی از پرتکرارترین کارها برای ما مدرسان و تحلیلگران مالی، تعمیم دادن یک فرمول به سایر سلولهاست. به جای استفاده مداوم از موس و درگ کردن (Drag)، با استفاده از ترکیب کلیدهای میانبر زیر، در کمتر از چند ثانیه جداول پیچیده خود را کامل کنید. کلیدهای میانبر جادویی برای کپی و تعمیم Ctrl + Shift + End کاربرد: انتخاب سریع محدوده توضیح: از سلول جاری تا آخرین سلول فعال در سمت راست و پایین جدول را به صورت یکجا انتخاب میکند. برای آمادهسازی محدوده جهت اعمال فرمولها عالی است. Ctrl + D (Fill Down) کاربرد: کپی رو به پایین توضیح: فرمول یا محتوای اولین سلولِ محدوده انتخابی را در تمام سلولهای پایینتر آن تکثیر میکند. Ctrl + R (Fill Right) کاربرد: کپی رو به راست توضیح: فرمول یا محتوای اولین سلول در سمت چپِ محدوده را در تمام سلولهای سمت راست آن کپی میکند. (مثل پر کردن درصدهای رشد در ردیفهای جدول تصویر بالا). Alt + H + F + I + L (Fill Left) کاربرد: کپی رو به چپ توضیح: این یک میانبر ترکیبی از منوی Ribbon است. زمانی کاربرد دارد که میخواهید فرمول را از سمت راست به سمت چپ تعمیم دهید (بسیار کاربردی برای جداول فارسی که جهتشان راستبهچپ است). اکسلدان شو | آموزش اکسل ویژه بازار کار دوره ۴ مرحله ای LPCF مستری اکسل
[فهرست آموزش] لینک فیلم برای نمایش یا دانلود https://my.uupload.ir/p/yoWVnvkD روشهای انتقال و تعمیم فرمول در اکسل در اکسل، سرعت عمل یعنی حرفهای بودن! یکی از پرتکرارترین کارها برای ما مدرسان و تحلیلگران مالی، تعمیم دادن یک فرمول به سایر سلولهاست. به جای استفاده مداوم از موس و درگ کردن (Drag)، با استفاده از ترکیب کلیدهای میانبر زیر، در کمتر از چند ثانیه جداول پیچیده خود را کامل کنید. کلیدهای میانبر جادویی برای کپی و تعمیم Ctrl + Shift + End کاربرد: انتخاب سریع محدوده توضیح: از سلول جاری تا آخرین سلول فعال در سمت راست و پایین جدول را به صورت یکجا انتخاب میکند. برای آمادهسازی محدوده جهت اعمال فرمولها عالی است. Ctrl + D (Fill Down) کاربرد: کپی رو به پایین توضیح: فرمول یا محتوای اولین سلولِ محدوده انتخابی را در تمام سلولهای پایینتر آن تکثیر میکند. Ctrl + R (Fill Right) کاربرد: کپی رو به راست توضیح: فرمول یا محتوای اولین سلول در سمت چپِ محدوده را در تمام سلولهای سمت راست آن کپی میکند. (مثل پر کردن درصدهای رشد در ردیفهای جدول تصویر بالا). Alt + H + F + I + L (Fill Left) کاربرد: کپی رو به چپ توضیح: این یک میانبر ترکیبی از منوی Ribbon است. زمانی کاربرد دارد که میخواهید فرمول را از سمت راست به سمت چپ تعمیم دهید (بسیار کاربردی برای جداول فارسی که جهتشان راستبهچپ است). اکسلدان شو | آموزش اکسل ویژه بازار کار دوره ۴ مرحله ای LPCF مستری اکسلاین ویدئو رو حتما مشاهده کنید. به دلیل محدودیتها پیامرسان بله مجبورم لینک فیلم رو بزارم۶۱
فرمولها پایان مسیر اکسل نیستند؛ شروع مسیر حرفهای شدناندخیلیها وقتی افراد میخواهند اکسل را جدی یاد بگیرند، از فرمولها شروع میکنند و این کاملاً درست است.
چون تا وقتی فرمولها را خوب بلد نباشی، نمیتوانی تحلیل درست انجام بدهی، خطاها را پیدا کنی یا گزارش قابل اعتماد بسازی.
اما نکته مهم اینجاست:
فرمولنویسی، هدف نهایی اکسل نیست؛ پایه ورود به گزارشسازی حرفهای است.در محیط کار، فقط اینکه بتوانی یک فرمول بنویسی کافی نیست. باید بتوانی:-
دادهها را درست تحلیل کنی- از فرمول مناسب در جای درست استفاده کنی- بانک اطلاعاتی منظم بسازی- گزارشهای قابل استفاده طراحی کنی- خروجیای بسازی که برای تصمیمگیری کاربرد داشته باشددر دوره مستری اکسل هم مسیر دقیقاً همین است:اول فرمولها را با مثالهای متنوع و کاربردی یاد میگیریم، بعد وارد ساخت بانک اطلاعاتی، تحلیل داده و گزارشگیری میشویم.
چون اکسل حرفهای یعنی:🧩 فرمول + ساختار داده + گزارشگیری
اگر میخواهی اکسل را از فرمولنویسی کاربردی تا گزارشسازی حرفهای یاد بگیری، کلمه مستری اکسل رو به ID زیر بفرست :@Hoseinnezhad_Mehdi
۳۱
[فهرست آموزش]
جداکردن نام و نام خانوادگی در اکسل، بدون فرمول
فرض کنید نام و نام خانوادگی افراد در یک ستون ثبت شده است و میخواهید آنها را در دو ستون جداگانه قرار دهید.
برای مثال، در سلول A2 نوشته شده است:سیدمهدی حسیننژاد
استخراج نام
در سلول B2 ، نام اولین شخص را تایپ کنید:سیدمهدی
سلول B3 را انتخاب کنید.
کلیدهای Ctrl + E را فشار دهید.
اکسل الگوی واردشده را تشخیص میدهد و نام سایر افراد را بهصورت خودکار استخراج میکند.
استخراج نام خانوادگی
در سلول C2 ، نام خانوادگی اولین شخص را بنویسید:حسیننژاد
سلول C3 را انتخاب کنید.
دوباره کلیدهای Ctrl + E را فشار دهید.
این قابلیت در اکسل Flash Fill یا «پرکردن سریع» نام دارد و از مسیر زیر نیز در دسترس است:
Data → Flash Fill
نکته مهمFlash Fill فرمول نیست؛ بنابراین اگر اطلاعات ستون اصلی تغییر کند، نتیجه بهصورت خودکار بهروزرسانی نمیشود و باید دوباره آن را اجرا کنید.
تمرین امروزاطلاعات زیر را در یک ستون وارد کنید:
- محمدرضا اکبری- فاطمه حسینی- امیر صادقی
سپس با استفاده از Ctrl + E ، نام و نام خانوادگی را در دو ستون جداگانه قرار دهید.━━━━━━━━━━━━━━اگر میخواهی اکسل را از فرمولنویسی کاربردی تا گزارشسازی حرفهای یاد بگیری، کلمه مستری اکسل رو به ID زیر بفرست : @Hoseinnezhad_Mehdi
جداکردن نام و نام خانوادگی در اکسل، بدون فرمولفرض کنید نام و نام خانوادگی افراد در یک ستون ثبت شده است و میخواهید آنها را در دو ستون جداگانه قرار دهید.
برای مثال، در سلول A2 نوشته شده است:سیدمهدی حسیننژاد
استخراج نام در سلول B2 ، نام اولین شخص را تایپ کنید:سیدمهدی سلول B3 را انتخاب کنید. کلیدهای Ctrl + E را فشار دهید.اکسل الگوی واردشده را تشخیص میدهد و نام سایر افراد را بهصورت خودکار استخراج میکند.
استخراج نام خانوادگی در سلول C2 ، نام خانوادگی اولین شخص را بنویسید:حسیننژاد سلول C3 را انتخاب کنید. دوباره کلیدهای Ctrl + E را فشار دهید.این قابلیت در اکسل Flash Fill یا «پرکردن سریع» نام دارد و از مسیر زیر نیز در دسترس است:
Data → Flash Fill
نکته مهمFlash Fill فرمول نیست؛ بنابراین اگر اطلاعات ستون اصلی تغییر کند، نتیجه بهصورت خودکار بهروزرسانی نمیشود و باید دوباره آن را اجرا کنید. تمرین امروزاطلاعات زیر را در یک ستون وارد کنید:- محمدرضا اکبری- فاطمه حسینی- امیر صادقی
سپس با استفاده از Ctrl + E ، نام و نام خانوادگی را در دو ستون جداگانه قرار دهید.━━━━━━━━━━━━━━اگر میخواهی اکسل را از فرمولنویسی کاربردی تا گزارشسازی حرفهای یاد بگیری، کلمه مستری اکسل رو به ID زیر بفرست :
@Hoseinnezhad_Mehdi۷۴
اکسل، دستیار قدرتمند حسابدارانتسلط بر اکسل یعنی انجام سریعتر و دقیقتر محاسبات، کاهش خطاهای انسانی و تهیه گزارشهای مالی حرفهای. این تصویر یک نقشه راه کاربردی برای یادگیری مهارتهای ضروری اکسل در حسابداری است.
خبر خوب اینکه همه این مهارتها را میتوان بدون VBA و ماکرو نیز بهصورت کاملاً کاربردی یاد گرفت.
از بین مهارتهای داخل تصویر، روی کدام مورد تسلط دارید و دوست دارید آموزش کدام موضوع را در کانال منتشر کنم؟
@Hoseinnezhad_Mehdi این مطلب را برای دوستان حسابدار و علاقهمند به اکسل ارسال کنید.۴۳۳۲
[لیست آموزش توابع]
جمعزدن شرطی با تابع SUMIF
چطور فروش یک محصول را بدون جمعزدن دستی محاسبه کنیم؟
فرض کن یک جدول فروش داری که نام محصول و مبلغ فروش هر محصول در آن ثبت شده است. حالا میخواهی بفهمی مجموع فروش هر محصول چقدر بوده است.
قرار نیست ردیفها را یکییکی پیدا کنی و با ماشینحساب جمع بزنی! اکسل با تابع SUMIF این کار را در چند ثانیه انجام میدهد.
ساختار تابع=SUMIF(range,criteria,sum_range)
اجزای تابع: range محدودهای که شرط در آن بررسی میشود. criteria شرطی که به دنبال آن هستیم. sum_rangeمحدوده اعدادی که باید با هم جمع شوند.
مثال کاربردیفرض کن:- ستون A شامل نام محصولات است.- ستون B شامل مبلغ فروش است.- در سلول D2 نام محصول موردنظر، مثلاً «ماوس»، نوشته شده است.
فرمول زیر را وارد میکنیم:=SUMIF(A2:A100,D2,B2:B100)
اکسل تمام ردیفهایی را که در ستون A نام «*ماوس*» دارند پیدا میکند و مبلغهای متناظرشان را از ستون B با هم جمع میکند.
یک نکته مهممحدوده شرط و محدوده جمع باید از نظر تعداد ردیف با هم برابر باشند.
درست:=SUMIF(A2:A100,D2,B2:B100)
نادرست:=SUMIF(A2:A100,D2,B2:B50)
با همین تابع ساده میتوانی مجموع فروش هر محصول، هزینه هر واحد، خرید از هر تأمینکننده یا پرداختی هر مشتری را محاسبه کنی.
اگر دوست داشتی مسیر کامل یادگیری اکسل از فرمولنویسی تا گزارشگیری را ببینی، کلمه «*مستری اکسل*» را به آیدی زیر بفرست: @Hoseinnezhad_Mehdi
"پیشنهاد به مجله"رو بزنین خستگیامونو بشوره ببره! بازم براتون آموزش رایگان خوب بزارم
اکسلدان شو | آموزش اکسل ویژه بازار کار
جمعزدن شرطی با تابع SUMIF چطور فروش یک محصول را بدون جمعزدن دستی محاسبه کنیم؟فرض کن یک جدول فروش داری که نام محصول و مبلغ فروش هر محصول در آن ثبت شده است. حالا میخواهی بفهمی مجموع فروش هر محصول چقدر بوده است.
قرار نیست ردیفها را یکییکی پیدا کنی و با ماشینحساب جمع بزنی! اکسل با تابع SUMIF این کار را در چند ثانیه انجام میدهد.
ساختار تابع=SUMIF(range,criteria,sum_range) اجزای تابع: range محدودهای که شرط در آن بررسی میشود. criteria شرطی که به دنبال آن هستیم. sum_rangeمحدوده اعدادی که باید با هم جمع شوند. مثال کاربردیفرض کن:- ستون A شامل نام محصولات است.- ستون B شامل مبلغ فروش است.- در سلول D2 نام محصول موردنظر، مثلاً «ماوس»، نوشته شده است.فرمول زیر را وارد میکنیم:=SUMIF(A2:A100,D2,B2:B100)
اکسل تمام ردیفهایی را که در ستون A نام «*ماوس*» دارند پیدا میکند و مبلغهای متناظرشان را از ستون B با هم جمع میکند.
یک نکته مهممحدوده شرط و محدوده جمع باید از نظر تعداد ردیف با هم برابر باشند. درست:=SUMIF(A2:A100,D2,B2:B100) نادرست:=SUMIF(A2:A100,D2,B2:B50)با همین تابع ساده میتوانی مجموع فروش هر محصول، هزینه هر واحد، خرید از هر تأمینکننده یا پرداختی هر مشتری را محاسبه کنی.
اگر دوست داشتی مسیر کامل یادگیری اکسل از فرمولنویسی تا گزارشگیری را ببینی، کلمه «*مستری اکسل*» را به آیدی زیر بفرست:
@Hoseinnezhad_Mehdi "پیشنهاد به مجله"رو بزنین خستگیامونو بشوره ببره! بازم براتون آموزش رایگان خوب بزارماکسلدان شو | آموزش اکسل ویژه بازار کار
۶۱
[لیست آموزش توابع]
اشتباهات رایج در VLOOKUP
۵ اشتباه رایج در VLOOKUP که جواب فرمول را نابود میکند!
تابع VLOOKUP یکی از پرکاربردترین توابع اکسل است،اما یک انتخاب اشتباه در آن میتواند باعث نمایش خطا یا حتی بدتر از آن، نمایش یک جواب ظاهراً درست ولی غیرواقعی شود!
فرض کن در سلول E۲ کد کالا را وارد کردهای و میخواهی قیمت آن را از جدول A۲:C۱۰۰ پیدا کنی:=VLOOKUP(E۲,A۲:C۱۰۰,۳,FALSE)
اگر این فرمول درست کار نمیکند، احتمالاً یکی از این ۵ اشتباه را انجام دادهای:
مقدار مورد جستوجو در ستون اول جدول نیست!
تابع VLOOKUP همیشه مقدار موردنظر را در اولین ستون محدوده انتخابشده جستوجو میکند.
برای مثال، در محدوده زیر:A۲:C۱۰۰
کد کالا باید در ستون A قرار داشته باشد. اگر کد کالا در ستون B باشد، تابع نمیتواند آن را پیدا کند.
راهحل: محدوده جدول را از ستونی انتخاب کن که مقدار مورد جستوجو در آن قرار دارد یا از XLOOKUP استفاده کن.
آرگومان آخر را FALSE قرار ندادهای
اگر آرگومان آخر تابع را ننویسی یا مقدار TRUE قرار بدهی، اکسل جستوجوی تقریبی انجام میدهد و ممکن است نتیجه اشتباهی نمایش دهد.
پرریسک:=VLOOKUP(E۲,A۲:C۱۰۰,۳)
جستوجوی دقیق:=VLOOKUP(E۲,A۲:C۱۰۰,۳,FALSE)
برای جستوجوی کد کالا، کد ملی، شماره پرسنلی و شماره فاکتور معمولاً باید از FALSE استفاده کنی.
شماره ستون را اشتباه وارد کردهای!
عدد سوم در تابع مشخص میکند نتیجه از چندمین ستون محدوده برگردانده شود.
در محدوده A۲:C۱۰۰:
- ستون A شماره ۱ است.- ستون B شماره ۲ است.- ستون C شماره ۳ است.
پس فرمول زیر، نتیجه را از ستون C برمیگرداند:=VLOOKUP(E۲,A۲:C۱۰۰,۳,FALSE)
اگر محدوده جدول تغییر کند، ممکن است شماره ستون قبلی دیگر صحیح نباشد.
نوع دادهها با یکدیگر متفاوت استممکن است کد ۱۰۰۱ در جدول بهصورت متن ذخیره شده باشد، اما در سلول جستوجو بهصورت عدد وارد شود.
ظاهر این دو مقدار یکسان است، ولی اکسل آنها را برابر نمیداند و خطای !N/A# نمایش میدهد.
راهحل: نوع داده مقدار جستوجو و ستون کدها را یکسان کن. هر دو باید عدد یا هر دو باید متن باشند.
محدوده جدول را هنگام کپیکردن قفل نکردهای!
اگر فرمول را به ردیفهای پایین کپی کنی، محدوده جدول نیز جابهجا میشود.
محدوده متغیر:=VLOOKUP(E۲,A۲:C۱۰۰,۳,FALSE)
محدوده ثابت:=VLOOKUP(E۲,$A$۲:$C$۱۰۰,۳,FALSE)
با فشردن کلید F۴ میتوانی محدوده جدول را قفل کنی.
قبل از اینکه بگویی «تابع VLOOKUP خراب است»، این پنج مورد را بررسی کن: ستون جستوجو آرگومان FALSE شماره ستون نتیجه یکسان بودن نوع دادهها ثابت بودن محدوده جدول
این آموزش را برای همکاری بفرست که هنوز با خطای !N/A# درگیر است!
اگر دوست داشتی مسیر کامل یادگیری اکسل از فرمولنویسی تا گزارشگیری را ببینی، کلمه «*مستری اکسل*» را به آیدی زیر بفرست: @Hoseinnezhad_Mehdi
"پیشنهاد به مجله"رو بزنین خستگیامونو بشوره ببره! بازم براتون آموزش رایگان خوب بزارم
اکسلدان شو | آموزش اکسل ویژه بازار کار
اشتباهات رایج در VLOOKUP ۵ اشتباه رایج در VLOOKUP که جواب فرمول را نابود میکند!تابع VLOOKUP یکی از پرکاربردترین توابع اکسل است،اما یک انتخاب اشتباه در آن میتواند باعث نمایش خطا یا حتی بدتر از آن، نمایش یک جواب ظاهراً درست ولی غیرواقعی شود!
فرض کن در سلول E۲ کد کالا را وارد کردهای و میخواهی قیمت آن را از جدول A۲:C۱۰۰ پیدا کنی:=VLOOKUP(E۲,A۲:C۱۰۰,۳,FALSE)
اگر این فرمول درست کار نمیکند، احتمالاً یکی از این ۵ اشتباه را انجام دادهای:
مقدار مورد جستوجو در ستون اول جدول نیست!تابع VLOOKUP همیشه مقدار موردنظر را در اولین ستون محدوده انتخابشده جستوجو میکند.
برای مثال، در محدوده زیر:A۲:C۱۰۰
کد کالا باید در ستون A قرار داشته باشد. اگر کد کالا در ستون B باشد، تابع نمیتواند آن را پیدا کند.
راهحل: محدوده جدول را از ستونی انتخاب کن که مقدار مورد جستوجو در آن قرار دارد یا از XLOOKUP استفاده کن. آرگومان آخر را FALSE قرار ندادهایاگر آرگومان آخر تابع را ننویسی یا مقدار TRUE قرار بدهی، اکسل جستوجوی تقریبی انجام میدهد و ممکن است نتیجه اشتباهی نمایش دهد.
پرریسک:=VLOOKUP(E۲,A۲:C۱۰۰,۳) جستوجوی دقیق:=VLOOKUP(E۲,A۲:C۱۰۰,۳,FALSE)برای جستوجوی کد کالا، کد ملی، شماره پرسنلی و شماره فاکتور معمولاً باید از FALSE استفاده کنی.
شماره ستون را اشتباه وارد کردهای!عدد سوم در تابع مشخص میکند نتیجه از چندمین ستون محدوده برگردانده شود.
در محدوده A۲:C۱۰۰:
- ستون A شماره ۱ است.- ستون B شماره ۲ است.- ستون C شماره ۳ است.
پس فرمول زیر، نتیجه را از ستون C برمیگرداند:=VLOOKUP(E۲,A۲:C۱۰۰,۳,FALSE)
اگر محدوده جدول تغییر کند، ممکن است شماره ستون قبلی دیگر صحیح نباشد.
نوع دادهها با یکدیگر متفاوت استممکن است کد ۱۰۰۱ در جدول بهصورت متن ذخیره شده باشد، اما در سلول جستوجو بهصورت عدد وارد شود.ظاهر این دو مقدار یکسان است، ولی اکسل آنها را برابر نمیداند و خطای !N/A# نمایش میدهد.
راهحل: نوع داده مقدار جستوجو و ستون کدها را یکسان کن. هر دو باید عدد یا هر دو باید متن باشند. محدوده جدول را هنگام کپیکردن قفل نکردهای!اگر فرمول را به ردیفهای پایین کپی کنی، محدوده جدول نیز جابهجا میشود.
محدوده متغیر:=VLOOKUP(E۲,A۲:C۱۰۰,۳,FALSE) محدوده ثابت:=VLOOKUP(E۲,$A$۲:$C$۱۰۰,۳,FALSE)با فشردن کلید F۴ میتوانی محدوده جدول را قفل کنی.
قبل از اینکه بگویی «تابع VLOOKUP خراب است»، این پنج مورد را بررسی کن:
ستون جستوجو آرگومان FALSE شماره ستون نتیجه یکسان بودن نوع دادهها ثابت بودن محدوده جدول این آموزش را برای همکاری بفرست که هنوز با خطای !N/A# درگیر است!اگر دوست داشتی مسیر کامل یادگیری اکسل از فرمولنویسی تا گزارشگیری را ببینی، کلمه «*مستری اکسل*» را به آیدی زیر بفرست:
@Hoseinnezhad_Mehdi "پیشنهاد به مجله"رو بزنین خستگیامونو بشوره ببره! بازم براتون آموزش رایگان خوب بزارماکسلدان شو | آموزش اکسل ویژه بازار کار
۳۲۲
[لیست آموزش توابع]
۷ اشتباه خطرناک در تابع IF که گزارش شما را غیرقابلاعتماد میکند!
تابع IF یکی از مهمترین و پرکاربردترین توابع اکسل است. اما یک اشتباه کوچک در نوشتن شرط میتواند نتیجه کل گزارش را تغییر دهد.
ساختار کلی این تابع به شکل زیر است:=IF(logical_test,value_if_true,val ue_if_false)یعنی:اگر شرط برقرار بود، نتیجه اول و اگر برقرار نبود، نتیجه دوم نمایش داده شود.
فرض کن هدف فروش هر کارشناس ۵۰ میلیون تومان است. اگر مبلغ فروش به هدف رسیده باشد، وضعیت Successful و در غیر این صورت Unsuccessful نمایش داده میشود:=IF(B۲>=۵۰.۰۰۰.۰۰۰,"Successful","Unsuccessful")
اگر این تابع نتیجه اشتباهی نمایش میدهد، احتمالاً یکی از هفت خطای زیر را انجام دادهای:
جهت شرط را برعکس نوشتهای!فرض کن میخواهی فروشهای بیشتر یا مساوی ۵۰ میلیون تومان، Successful اعلام شوند.
فرمول اشتباه:=IF(B۲<=۵۰.۰۰۰.۰۰۰,"Successful","Unsuccessful")
در این فرمول، فروشهای کمتر یا مساوی ۵۰ میلیون تومان بهاشتباه Successful نمایش داده میشوند.
فرمول صحیح:=IF(B۲>=۵۰.۰۰۰.۰۰۰,"Successful","Unsuccessful")
مفهوم فرمول صحیح:- اگر فروش بیشتر یا مساوی ۵۰ میلیون باشد: Successful- اگر فروش کمتر از ۵۰ میلیون باشد: Unsuccessful
قبل از نوشتن فرمول، شرط را یک بار با زبان ساده برای خودت بخوان.
متن را داخل کوتیشن قرار ندادهای!خروجیهای متنی در فرمولهای اکسل باید داخل علامت کوتیشن " " قرار بگیرند.
فرمول اشتباه:=IF(B۲>=۵۰.۰۰۰.۰۰۰,Successful,Unsuccessful)
فرمول صحیح:=IF(B۲>=۵۰.۰۰۰.۰۰۰,"Successful","Unsuccessful")
اگر خروجی فرمول متن است، باید آن را داخل کوتیشن قرار بدهی؛ اما برای خروجی عددی معمولاً نیازی به کوتیشن نیست.
عدد را بهصورت متن وارد کردهای!اگر قرار است خروجی تابع در محاسبات بعدی استفاده شود، عدد را داخل کوتیشن قرار نده.
خروجی متنی:=IF(B۲>=۵۰.۰۰۰.۰۰۰,"۱","۰")
خروجی عددی:=IF(B۲>=۵۰.۰۰۰.۰۰۰,۱,۰)
در فرمول اول، ۱ و ۰ بهصورت متن برگردانده میشوند؛ اما در فرمول دوم، خروجیها عدد واقعی هستند و میتوان از آنها در محاسبات بعدی استفاده کرد.
ترتیب شرطها در IF تودرتو اشتباه است!فرض کن میخواهی عملکرد فروش را اینگونه دستهبندی کنی:
- فروش ۱۰۰ میلیون تومان و بیشتر: Excellent- فروش ۵۰ میلیون تومان تا کمتر از ۱۰۰ میلیون: Good- فروش کمتر از ۵۰ میلیون تومان: Poor
فرمول اشتباه:=IF(B۲>=۵۰.۰۰۰.۰۰۰,"Good",IF(B۲>=۱۰۰.۰۰۰.۰۰۰,"Excellent","Poor"))
اگر مبلغ فروش ۱۲۰ میلیون تومان باشد، شرط اول برقرار میشود و اکسل نتیجه Good را نمایش میدهد؛ بنابراین هرگز به شرط مربوط به Excellent نمیرسد.
فرمول صحیح:=IF(B۲>=۱۰۰.۰۰۰.۰۰۰,"Excellent",IF(B۲>=۵۰.۰۰۰.۰۰۰,"Good","Poor"))
اکسل شرطها را از چپ به راست بررسی میکند و پس از رسیدن به اولین شرط درست، ادامه فرمول را بررسی نمیکند.
در دستهبندیهای پلهای، معمولاً باید شرطها را از بزرگترین مقدار به کوچکترین مقدار بنویسی.
پرانتزها را کامل نبستهای!هر تابع IF باید یک پرانتز باز و یک پرانتز بسته داشته باشد. در فرمولهای تودرتو، احتمال فراموشکردن پرانتزها بیشتر است.
فرمول اشتباه:=IF(B۲>=۱۰۰.۰۰۰.۰۰۰,"Excellent",IF(B۲>=۵۰.۰۰۰.۰۰۰,"Good","Poor")
فرمول صحیح:=IF(B۲>=۱۰۰.۰۰۰.۰۰۰,"Excellent",IF(B۲>=۵۰.۰۰۰.۰۰۰,"Good","Poor"))
در این مثال دو تابع IF داریم. بنابراین در انتهای فرمول نیز باید دو پرانتز بسته وجود داشته باشد.
شرطها را بدون توجه به همپوشانی مرتب کردهای!ممکن است یک مقدار همزمان چند شرط را برقرار کند. برای مثال، عدد ۱۲۰ میلیون هم بزرگتر از ۵۰ میلیون و هم بزرگتر از ۱۰۰ میلیون است.
اکسل سردرگم نمیشود؛ بلکه اولین شرط درست را انتخاب میکند. به همین دلیل ترتیب شرطها اهمیت زیادی دارد.
ترتیب نادرست:=IF(B۲>=۵۰.۰۰۰.۰۰۰,"Good",IF(B۲>=۱۰۰.۰۰۰.۰۰۰,"Excellent","Poor"))
ترتیب صحیح:=IF(B۲>=۱۰۰.۰۰۰.۰۰۰,"Excellent",IF(B۲>=۵۰.۰۰۰.۰۰۰,"Good","Poor"))
پس شرط خاصتر یا مقدار بزرگتر را زودتر بررسی کن.
ادامه دارد : مورد ۷ در پست بعدی
۷ اشتباه خطرناک در تابع IF که گزارش شما را غیرقابلاعتماد میکند!تابع IF یکی از مهمترین و پرکاربردترین توابع اکسل است. اما یک اشتباه کوچک در نوشتن شرط میتواند نتیجه کل گزارش را تغییر دهد.
ساختار کلی این تابع به شکل زیر است:=IF(logical_test,value_if_true,val ue_if_false)یعنی:اگر شرط برقرار بود، نتیجه اول و اگر برقرار نبود، نتیجه دوم نمایش داده شود.
فرض کن هدف فروش هر کارشناس ۵۰ میلیون تومان است. اگر مبلغ فروش به هدف رسیده باشد، وضعیت Successful و در غیر این صورت Unsuccessful نمایش داده میشود:=IF(B۲>=۵۰.۰۰۰.۰۰۰,"Successful","Unsuccessful")
اگر این تابع نتیجه اشتباهی نمایش میدهد، احتمالاً یکی از هفت خطای زیر را انجام دادهای:
جهت شرط را برعکس نوشتهای!فرض کن میخواهی فروشهای بیشتر یا مساوی ۵۰ میلیون تومان، Successful اعلام شوند. فرمول اشتباه:=IF(B۲<=۵۰.۰۰۰.۰۰۰,"Successful","Unsuccessful")در این فرمول، فروشهای کمتر یا مساوی ۵۰ میلیون تومان بهاشتباه Successful نمایش داده میشوند.
فرمول صحیح:=IF(B۲>=۵۰.۰۰۰.۰۰۰,"Successful","Unsuccessful")مفهوم فرمول صحیح:- اگر فروش بیشتر یا مساوی ۵۰ میلیون باشد: Successful- اگر فروش کمتر از ۵۰ میلیون باشد: Unsuccessful
قبل از نوشتن فرمول، شرط را یک بار با زبان ساده برای خودت بخوان.
متن را داخل کوتیشن قرار ندادهای!خروجیهای متنی در فرمولهای اکسل باید داخل علامت کوتیشن " " قرار بگیرند. فرمول اشتباه:=IF(B۲>=۵۰.۰۰۰.۰۰۰,Successful,Unsuccessful) فرمول صحیح:=IF(B۲>=۵۰.۰۰۰.۰۰۰,"Successful","Unsuccessful")اگر خروجی فرمول متن است، باید آن را داخل کوتیشن قرار بدهی؛ اما برای خروجی عددی معمولاً نیازی به کوتیشن نیست.
عدد را بهصورت متن وارد کردهای!اگر قرار است خروجی تابع در محاسبات بعدی استفاده شود، عدد را داخل کوتیشن قرار نده. خروجی متنی:=IF(B۲>=۵۰.۰۰۰.۰۰۰,"۱","۰") خروجی عددی:=IF(B۲>=۵۰.۰۰۰.۰۰۰,۱,۰)در فرمول اول، ۱ و ۰ بهصورت متن برگردانده میشوند؛ اما در فرمول دوم، خروجیها عدد واقعی هستند و میتوان از آنها در محاسبات بعدی استفاده کرد.
ترتیب شرطها در IF تودرتو اشتباه است!فرض کن میخواهی عملکرد فروش را اینگونه دستهبندی کنی:- فروش ۱۰۰ میلیون تومان و بیشتر: Excellent- فروش ۵۰ میلیون تومان تا کمتر از ۱۰۰ میلیون: Good- فروش کمتر از ۵۰ میلیون تومان: Poor
فرمول اشتباه:=IF(B۲>=۵۰.۰۰۰.۰۰۰,"Good",IF(B۲>=۱۰۰.۰۰۰.۰۰۰,"Excellent","Poor"))اگر مبلغ فروش ۱۲۰ میلیون تومان باشد، شرط اول برقرار میشود و اکسل نتیجه Good را نمایش میدهد؛ بنابراین هرگز به شرط مربوط به Excellent نمیرسد.
فرمول صحیح:=IF(B۲>=۱۰۰.۰۰۰.۰۰۰,"Excellent",IF(B۲>=۵۰.۰۰۰.۰۰۰,"Good","Poor"))اکسل شرطها را از چپ به راست بررسی میکند و پس از رسیدن به اولین شرط درست، ادامه فرمول را بررسی نمیکند.
در دستهبندیهای پلهای، معمولاً باید شرطها را از بزرگترین مقدار به کوچکترین مقدار بنویسی.
پرانتزها را کامل نبستهای!هر تابع IF باید یک پرانتز باز و یک پرانتز بسته داشته باشد. در فرمولهای تودرتو، احتمال فراموشکردن پرانتزها بیشتر است. فرمول اشتباه:=IF(B۲>=۱۰۰.۰۰۰.۰۰۰,"Excellent",IF(B۲>=۵۰.۰۰۰.۰۰۰,"Good","Poor") فرمول صحیح:=IF(B۲>=۱۰۰.۰۰۰.۰۰۰,"Excellent",IF(B۲>=۵۰.۰۰۰.۰۰۰,"Good","Poor"))در این مثال دو تابع IF داریم. بنابراین در انتهای فرمول نیز باید دو پرانتز بسته وجود داشته باشد.
شرطها را بدون توجه به همپوشانی مرتب کردهای!ممکن است یک مقدار همزمان چند شرط را برقرار کند. برای مثال، عدد ۱۲۰ میلیون هم بزرگتر از ۵۰ میلیون و هم بزرگتر از ۱۰۰ میلیون است.اکسل سردرگم نمیشود؛ بلکه اولین شرط درست را انتخاب میکند. به همین دلیل ترتیب شرطها اهمیت زیادی دارد.
ترتیب نادرست:=IF(B۲>=۵۰.۰۰۰.۰۰۰,"Good",IF(B۲>=۱۰۰.۰۰۰.۰۰۰,"Excellent","Poor")) ترتیب صحیح:=IF(B۲>=۱۰۰.۰۰۰.۰۰۰,"Excellent",IF(B۲>=۵۰.۰۰۰.۰۰۰,"Good","Poor"))پس شرط خاصتر یا مقدار بزرگتر را زودتر بررسی کن.
ادامه دارد : مورد ۷ در پست بعدی ۵۱۱۱ ادامه پست قبلی : مورد ۱ الی ۶ در پست قبلی برای تعداد زیادی شرط، همچنان از IF تودرتو استفاده میکنی!وقتی تعداد شرطها زیاد میشود، استفاده از چند تابع IF تودرتو باعث میشود فرمول طولانی، سختخوان و خطاپذیر شود.مثال:=IF(B۲>=۱۰۰.۰۰۰.۰۰۰,"Excellent",IF(B۲>=۷۰.۰۰۰.۰۰۰,"Good",IF(B۲>=۴۰.۰۰۰.۰۰۰,"Average","Poor")))
اگر نسخه اکسل شما از تابع IFS پشتیبانی میکند، میتوانی همان منطق را خواناتر بنویسی:=IFS(B۲>=۱۰۰.۰۰۰.۰۰۰,"Excellent",B۲>=۷۰.۰۰۰.۰۰۰,"Good",B۲>=۴۰.۰۰۰.۰۰۰,"Average",TRUE,"Poor")
استفاده از TRUE در شرط آخر باعث میشود اگر هیچکدام از شرطهای قبلی برقرار نبود، نتیجه Poor نمایش داده شود.
چکلیست نهایی تابع IFقبل از تأیید فرمول، این موارد را بررسی کن:
جهت عملگرهای مقایسهای مانند >= و <= درست است؟ خروجیهای متنی داخل کوتیشن قرار گرفتهاند؟ عددها بیدلیل به متن تبدیل نشدهاند؟ شرطهای چندمرحلهای به ترتیب صحیح نوشته شدهاند؟ همه پرانتزها کامل بسته شدهاند؟ همپوشانی شرطها و ترتیب بررسی آنها کنترل شده است؟ برای شرطهای زیاد، استفاده از IFS مناسبتر نیست؟ این آموزش را برای همکاری بفرست که هنوز با فرمولهای شرطی در اکسل درگیر است!اگر دوست داشتی مسیر کامل یادگیری اکسل از فرمولنویسی تا گزارشگیری را ببینی، کلمه «مستری اکسل» را به آیدی زیر بفرست:
@Hoseinnezhad_Mehdiاکسلدان شو | آموزش اکسل ویژه بازار کار
۸۱۱
روشهای مغایرتگیری دو لیست و رفع اختلافِ فیمابین با اکسلتطبیقدادن دو لیست مختلف و پیداکردن تفاوتهای آنها، یکی از تکراریترین و حساسترین کارهای روزمره در بخشهای مالی، انبارداری، فروش ، اداری و ... است. برای مغایرتگیری دو لیست در اکسل، چند روش اصلی وجود دارد:
۱. روش چشمی و فیلتر: سادهترین راه است که فقط برای لیستهای بسیار کوچک مناسب است، اما در حجم بالای داده، ضریب خطای بسیار زیادی دارد.
۲. فرمولهای کنترلی (IF و COUNTIF): روشی سریع برای اینکه بدانید آیا یک داده یا عدد مشخص در لیست دوم وجود دارد یا خیر و بالعکس
۳. توابع جستوجوگر (VLOOKUP و XLOOKUP): یکی از استانداردترین روشها برای تطبیق نظیربهنظیر دادهها و استخراج مقادیر مغایرت است.
۴. ابزار بصری (Conditional Formatting): هایلایتکردن سلولهای مغایر با رنگهای مشخص، برای بررسی سریع و چشمیِ گزارش.
۵. ابزار هوشمند (Power Query): حرفهایترین روش برای مغایرتگیریهای دورهای و خودکار در فایلهای بسیار حجیم.
«هرکدام از این روشها برای سطح مشخصی از دادهها طراحی شدهاند. در حالی که برای گزارشهای ساده، توابع جستوجو ابزاری سریع و در دسترس هستند، برای مدیریت فایلهای حجیم و فرآیندهای تکرارپذیر، Power Query به دلیل قابلیت اتوماسیون و دقت بالا، انتخاب حرفهایتری محسوب میشود.»
در پست بعدی ، نحوه کار با تابع XLOOKUP را برای مغایرتگیری دو لیست آموزش خواهم داد تا این فرایند را با دقت بیشتر و زمان کمتر انجام دهید. کانال را رصد کن تا آموزش رو از دست ندی.۹۵۲