فرمول های اساسی اکسل برای محاسبات ساده خوب کار می کنند ، اما وقتی با تجزیه و تحلیل داده های پیچیده سر و کار دارید به سرعت دست و پا گیر می شوند. شما به کارکردهای تو در تو در تو در پایان می رسید که خواندن آن دشوار است ، چندین ستون یاور که صفحه گسترده شما را به هم ریخته و فرمول هایی که هنگام تغییر داده های شما می توانند شکسته شوند. این جایی است که من از فرمول های Array Excel استفاده می کنم.
فرمول های اساسی اکسل برای محاسبات ساده خوب کار می کنند ، اما وقتی با تجزیه و تحلیل داده های پیچیده سر و کار دارید به سرعت دست و پا گیر می شوند. شما به کارکردهای تو در تو در تو در پایان می رسید که خواندن آن دشوار است ، چندین ستون یاور که صفحه گسترده شما را به هم ریخته و فرمول هایی که هنگام تغییر داده های شما می توانند شکسته شوند. این جایی است که من از فرمول های Array Excel استفاده می کنم.
فرمول های آرایه به شما امکان می دهد محاسبات را در کل محدوده داده ها در یک فرمول واحد انجام دهید. از این رو ، شما می توانید به جای نوشتن فرمول های جداگانه برای هر سطر یا ستون ، جستجوی سریع ، فیلتر و مرتب سازی با یک عبارت قدرتمند را انجام دهید. آنها برای برتری جدید نیستند ، اما برخی از افراد با روشهای قدیمی انجام کارها وقتی این کارکردها می توانند کار خود را بسیار ساده تر و کارآمدتر کنند ، می چسبند.
5
Xlookup
هر بار Vlookup را ضرب می کند
Xlookup عملکرد جستجوی است که باید از ابتدا وجود داشته باشد. بر خلاف VLOOKUP ، که شما را مجبور می کند ستون ها را بشمارید و فقط در سمت راست جستجو می کنید ، Xlookup از هر جهت کار می کند و از منابع ستون واقعی استفاده می کند. این نحو زیر را دارد:
= Xlookup (lookup_value ، lookup_array ، return_array ، [if_not_found] ، [match_mode] ، [search_mode])
در اینجا به معنای هر پارامتر است:
- lookup_value: مقدار خاصی که در جستجوی آن هستید. این می تواند یک شماره قطعه ، کد محصول یا هر شناسه در مجموعه داده شما باشد.
- lookup_array: دامنه ای که اکسل برای جستجوی شما جستجو می کند. این معمولاً یک ستون یا ردیف واحد حاوی معیارهای جستجوی شما است.
- Return_array: دامنه حاوی مقادیری که می خواهید بازیابی کنید. این می تواند یک ستون واحد ، چندین ستون یا حتی یک بخش کامل جدول باشد.
- if_not_found (اختیاری): متن سفارشی یا مقدار برای نمایش در صورت عدم وجود مطابقت. این خطاهای آزار دهنده #N/A را از بین می برد و به شما امکان می دهد “یافت نشد” یا “شماره قسمت را بررسی نکنید”.
- match_mode (اختیاری): نوع مسابقه را کنترل می کند. از 0 برای مسابقه دقیق (پیش فرض) ، -1 برای مسابقه دقیق یا کوچکترین بعدی ، 1 برای مسابقه دقیق یا بزرگترین بعدی و 2 برای تطبیق Wildcard استفاده کنید.
- SEARCH_MODE (اختیاری): جهت جستجو را تعیین می کند. من از 1 برای جستجوی اول تا آخر استفاده می کنم (پیش فرض) ، -1 جستجوهای آخرین تا اول و 2 جستجوی باینری را روی داده های مرتب شده انجام می دهد.
بیایید نمونه ای از صفحه گسترده موجودی مکانیکی را بگیریم. فرمول زیر شماره قسمت “BRG-002” را در محدوده قسمت IDS جستجو می کند و داده های مربوطه را برمی گرداند. اگر این قسمت وجود نداشته باشد ، به جای خطا “بخشی یافت نشد” را نشان می دهد.
= Xlookup (“BRG-002” ، الف: A ، A: H ، “قسمت یافت نشد”)
Xlookup به شما امکان می دهد داده ها را از ستون های مختلف بدون سردردهای شمارش ستون VLookup بکشید ، که این امر را به یکی از مهمترین توابع اکسل برای یافتن سریع داده ها تبدیل می کند.
4
کالای جمع
نیروگاه محاسبه مشروط
sumproduct فقط شماره ها را اضافه نمی کند ، بلکه آرایه ها را چند برابر می کند و سپس نتایج را خلاصه می کند. این امر باعث می شود تا محاسبات پیچیده شرطی که در غیر این صورت به ستون های متعدد یاور نیاز دارد ، مفید باشد.
این نحو زیر را دارد:
= sumproduct (array1 ، [array2] ، [array3] ، …)
در اینجا ، Array1 اولین طیف وسیعی از مقادیر است که به طور معمول ستون داده اصلی شما مانند مقادیر یا هزینه ها است. و ، Array2 یک دامنه دوم اختیاری برای ضرب است ، که اغلب با استفاده از اپراتورهای مقایسه حاوی معیارها یا منطق شرطی است.
وقتی از اپراتورهای منطقی در آرایه ها استفاده می کنیم ، مفیدتر می شود. به عنوان مثال ، هنگامی که ما شرایطی را می نویسیم مانند (تأمین کننده = “زیمنس”) ، اکسل نتایج واقعی/غلط را به 1/0 تبدیل می کند و به عملیات ریاضی اجازه می دهد.
به عنوان نمونه ، فرمول زیر مقدار کل موجودی را برای قطعات تهیه شده توسط زیمنس محاسبه می کند. این فرمول مقادیر را با هزینه واحد ضرب می کند ، اما فقط برای ردیف هایی که تأمین کننده با معیارها مطابقت دارد.
= sumproduct (D2: D100*H2: H100*(G2: G100 = “زیمنس”))
به همین ترتیب ، فرمول زیر هزینه کل موجودی تحمل موجودی را پیدا می کند:
= sumproduct ((C2: C100 = “یاتاقان”)*(D2: D100> = 15)*H2: H100)
این دو شرط به طور همزمان اعمال می شود – طبقه بندی باید “یاتاقان” باشد و سطح سهام باید 15 واحد یا بالاتر باشد ، و به ما کمک می کند تا شناسایی کنیم که دسته های تحمل دارای پوشش موجودی کافی هستند.
بر خلاف توابع جمع سنتی با معیارهای متعدد ، Sumproduct به ساختارهای پیچیده تو در تو نیاز ندارد زیرا این شرایط را در یک فرمول واحد و قابل خواندن پردازش می کند. توابع جمع در اکسل ، مانند sumif و sumifs ، برای مبالغ اساسی مشروط خوب کار می کنند ، اما در صورت نیاز به مقادیر قبل از جمع بندی یا رسیدگی به عملیات منطقی پیچیده تر ، محصول جمع می شود.
3
فیلتر کردن
استخراج داده های پویا را ساده می کند
بر اساس شرایطی که مشخص کرده اید ، ردیف ها را از مجموعه داده خود فیلتر کنید. بر خلاف فیلتر دستی ، این عملکرد نتایج پویا ایجاد می کند که هنگام تغییر داده های منبع شما به طور خودکار به روز می شود. فیلتر نحو زیر را دارد:
= فیلتر (آرایه ، شامل ، [if_empty])
در اینجا چیزی که هر پارامتر کنترل می کند آورده شده است:
- آرایه: کل طیف وسیعی از داده هایی که می خواهید فیلتر کنید. این شامل تمام ستون هایی که در نتایج خود می خواهید ، نه فقط ستون معیارها.
- شامل: شرط منطقی که تعیین می کند ردیف ها برای بازگشت – استفاده از اپراتورهای مقایسه برای ایجاد آرایه های واقعی/نادرست برای هر ردیف.
- if_empty (اختیاری): برای نمایش یک پیام سفارشی در صورت عدم رعایت ردیف معیارهای شما. از #Calc جلوگیری می کند! خطاها و متن معنادار مانند “هیچ مسابقه ای پیدا نشده” را نشان می دهد.
این کار با ارزیابی وضعیت شما در برابر هر ردیف در آرایه کار می کند. هنگامی که شرط درست باز می گردد ، آن ردیف در نتایج فیلتر شده ظاهر می شود. موارد زیر نمونه ای از صفحه گسترده موجودی مکانیکی است:
= فیلتر (A2: H101 ، (C2: C101 = “یاتاقان”)*(G2: G101 = “Timken”))
این فرمول تمام ردیف هایی را که تأمین کننده “Timken” است ، استخراج می کند و دسته “یاتاقان” است. ستاره (*) با ضرب آرایه های منطقی با هم ، یک و شرط ایجاد می کند.
هنگامی که داده های جدیدی را به محدوده منبع خود اضافه می کنید ، استفاده از عملکرد فیلتر اکسل نسبت به مرتب سازی دستی و جداول موقت بیشتر می شود زیرا نتایج فیلتر شده به طور خودکار به روز می شود. این باعث می شود که برای ایجاد داشبورد و گزارش های زنده مفید باشد.
2
بی نظیر
مقادیر متمایز را بدون نسخه کپی استخراج کنید
منحصر به فرد مقادیر متمایز را از محدوده داده شما بیرون می کشد و به طور خودکار از تکثیر جلوگیری می کند. اگر می خواهید لیست های کشویی ایجاد کنید ، دسته بندی داده ها را تجزیه و تحلیل کنید و گزارش های خلاصه ایجاد کنید ، این عملکرد مهم است. نحو این است:
= منحصر به فرد (آرایه ، [BY_COL] ، [TATUE_ONCE])
در اینجا نحوه عملکرد هر پارامتر آورده شده است:
- آرایه: دامنه حاوی داده های مورد نظر برای اختصاص دادن – می تواند یک ستون واحد ، ستون های مختلف یا یک بخش کامل باشد.
- BY_COL (اختیاری): FALSE ردیف ها را برای منحصر به فرد بودن (پیش فرض) مقایسه می کند ، در حالی که TRUE ستون ها را مقایسه می کند. با این حال ، بیشتر سناریوها از مقایسه ردیف پیش فرض استفاده می کنند.
- TATUE_ONCE (اختیاری): FALSE تمام مقادیر منحصر به فرد ، از جمله مواردی که چندین بار به نظر می رسند (پیش فرض) باز می گردد ، و True فقط مقادیر را باز می گرداند که دقیقاً یک بار در مجموعه داده ظاهر می شوند.
عملکرد منحصر به فرد هر ردیف یا مقدار را در آرایه شما ارزیابی می کند و فقط اولین وقوع هر مورد مجزا را برمی گرداند. سفارش با دنباله داده اصلی شما مطابقت دارد. مثال زیر است:
= منحصر به فرد (G2: G22)
این فرمول تمام نام های تأمین کننده منحصر به فرد را از ستون تأمین کننده G استخراج می کند و بدون تکرار یک لیست تمیز ایجاد می کند. من از آن برای ساخت منوهای کشویی تأمین کننده یا گزارش های خلاصه استفاده می کنم.
همانطور که در زیر آمده است می توانید از آن در کل جدول استفاده کنید:
= منحصر به فرد (A2: F100)
این ترکیبات منحصر به فرد را در تمام ستون ها (A از طریق F) باز می گرداند ، و سوابق موجودی متمایز را نشان می دهد. اگر دو بخش در هر ستون مقادیر یکسان داشته باشند ، فقط یک در نتایج ظاهر می شود.
هنگام کار با مجموعه داده های بزرگ ، منحصر به فرد روند خسته کننده حذف تکراری دستی را از بین می برد. نتایج پویا هنگام ورود داده های جدید به روز می شود و از آنجا که منحصر به فرد آرایه های نشت ایجاد می کند ، این رویکرد با گسترش خودکار برای قرار دادن تمام مقادیر منحصر به فرد ، درد جداول تغییر شکل را به پایان می رساند. من از آن برای حفظ لیست های مرجع تمیز و ایجاد محدوده اعتبار سنجی داده های قابل اعتماد استفاده می کنم.
1
مرتب سازی و مرتب سازی
داده های خود را بدون لمس کردن اصلی ترتیب دهید
توابع مرتب سازی و مرتب سازی بر روی داده ها به صورت پویا در حالی که منبع را دست نخورده نگه می دارند ، سازماندهی می کنند. مرتب سازی بر اساس مرتب سازی اساسی بر اساس موقعیت ستون ، در حالی که انواع مرتب سازی بر اساس مقادیر در ستون های مختلف – انعطاف پذیری بیشتری را برای ترتیبات پیچیده به شما می دهد.
مرتب سازی از این نحو استفاده می کند:
= مرتب سازی (آرایه ، [sort_index] ، [sort_order] ، [by_col])
در اینجا چیزی که هر پارامتر کنترل می کند آورده شده است:
- آرایه: محدوده داده ای که می خواهید مرتب کنید – شامل تمام ستون هایی است که باید در نتایج مرتب شده ظاهر شوند.
- SORT_INDEX (اختیاری): شماره ستون در آرایه برای مرتب سازی بر اساس. از 1 برای ستون اول ، 2 برای ستون دوم و غیره استفاده کنید (پیش فرض 1).
- SORT_ORDER (اختیاری): از 1 برای سفارش صعودی (پیش فرض) و -1 برای سفارش نزولی استفاده کنید.
- by_col (اختیاری): انواع کاذب توسط ردیف (پیش فرض) ، انواع واقعی توسط ستون ها – بیشتر سناریوها از مرتب سازی ردیف استفاده می کنند.
Sortby نحو زیر را دارد:
= sortby (آرایه ، by_array1 ، [sort_order1] ، [by_array2] ، [sort_order2] ، …)
پارامترهای آن عبارتند از:
- Array: محدوده داده برای مرتب سازی – SOME به عنوان مرتب سازی ، شامل تمام ستون های مورد نظر در نتایج است.
- by_array1: دامنه حاوی مقادیری که ترتیب مرتب سازی را تعیین می کنند – می توانند هر ستون ، حتی خارج از آرایه اصلی شما باشند.
- sort_order1 (اختیاری): 1 برای صعود (پیش فرض) ، -1 برای نزولی.
- by_array2 ، sort_order2 (اختیاری): معیارهای مرتب سازی اضافی برای مرتب سازی چند سطحی.
با توجه به نمونه ای از صفحه گسترده موجودی مکانیکی ، این توابع سناریوهای مرتب سازی واقعی را اداره می کنند:
= مرتب سازی (A2: H22 ، 4 ، -1)
این کل موجودی را بر اساس سطح سهام به ترتیب نزولی مرتب می کند و بالاترین موارد سهام را در ابتدا نشان می دهد. فرمول در ستون 4 (سطح سهام) در حالی که تمام روابط ردیف را حفظ می کند ، مرتب می شود.
من برای کنترل بهتر معیارهای مرتب سازی و سطح مرتب سازی چندگانه از عملکرد Sortby به جای مرتب سازی استفاده می کنم. به عنوان نمونه ، فرمول زیر ابتدا بر اساس طبقه بندی به صورت حروف الفبا ، سپس با سطح سهام از بالاترین تا کمترین در هر گروه مرتب می شود.
= Sortby (A2: H22 ، C2: C22 ، 1 ، D2: D22 ، -1)
صفحات گسترده تمیز ، نتایج باهوش تر
فرمول های آرایه ، درهم و برهمی ستون های یاور و توابع تو در تو را از بین می برند که باعث می شود صفحات گسترده برای نگهداری آنها سخت شود. شما فرمول های منفرد دریافت می کنید که چندین عملیات را اداره می کنند ، کتاب های کار را تمیز تر می کنند و ظاهری حرفه ای تری دارند.
یک مزیت قابل توجه عملکرد پویا است ، زیرا نتایج به طور خودکار هنگام تغییر داده های منبع به روز می شود. بنابراین ، دیگر هیچ گونه تازه سازی دستی یا زنجیره های فرمول شکسته ، و صفحات گسترده شما برای تجزیه و تحلیل مداوم قابل اطمینان تر می شوند.
کتابخانه عملکرد آرایه اکسل فراتر از این ابزارهای اصلی گسترش می یابد. هنگامی که من نیاز به ادغام داده ها از چندین منبع دارم ، برای ادغام دامنه از توابع VSTACK و HSTACK استفاده می کنم. این کارکردها با هم کار می کنند تا گردش کار قدرتمند پردازش داده ها را ایجاد کنند که با فرمول های سنتی غیرممکن باشد.