به عنوان یک قاعده، اثربخشی عملیات پیچیده و در مقیاس بزرگ را نمی توان با استفاده از یک نشانگر W مشخص کرد، بلکه باید توسط W 1، W 2،...، W اضافی به آن کمک کرد. برخی از آنها مطلوب است که بیشتر انجام دهند، برخی دیگر - کمتر. به عنوان مثال، هنگام ارزیابی عملکرد یک شرکت، تعدادی شاخص باید در نظر گرفته شود:

• حجم کامل محصولات

  قیمت تمام شده و غیره

هنگام تجزیه و تحلیل یک عملیات رزمی، علاوه بر شاخص اصلی، انتظار ریاضی آسیب وارد شده به دشمن، لازم است تعدادی از موارد اضافی را نیز در نظر بگیرید:

ضررهای خود

زمان اجرای عملیات،

مصرف مهمات و غیره

این تعدد شاخص‌های عملکرد، که برخی از آن‌ها مطلوب به حداکثر رساندن و برخی دیگر برای به حداقل رساندن مطلوب هستند، مشخصه هر مسئله تحقیق در عملیات پیچیده است. در این مورد، فرمول صحیح «دستیابی به حداکثر اثر با هزینه‌های معین» یا «دستیابی به اثر معین با حداقل هزینه» است. در حالت کلی، هیچ راه حلی وجود ندارد که یک نشانگر W 1 را به حداکثر و در عین حال شاخص دیگر W 2 را به حداکثر (یا حداقل) تبدیل کند. علاوه بر این، چنین راه حلی برای چندین شاخص وجود ندارد. با این حال، تجزیه و تحلیل کمی اثربخشی نیز می تواند در مورد چندین شاخص مفید باشد، زیرا این به شما امکان می دهد از قبل دور بیندازید به وضوح غیرمنطقیگزینه های راه حلی که از همه نظر نسبت به بهترین گزینه ها پایین تر هستند.

بیایید به یک مثال نگاه کنیم. اجازه دهید عملیات رزمی Q را که توسط دو شاخص ارزیابی می شود، تجزیه و تحلیل کنیم:

دبلیو- احتمال انجام یک ماموریت رزمی؛

اس- هزینه وجوه صرف شده

توصیه می شود اولین نشانگر را به حداکثر و دومی را به حداقل برسانید.

فرض کنید 20 گزینه راه حل مختلف x 1، x 2،…، x 20 وجود دارد. برای هر یک از آنها، مقادیر هر دو شاخص W و S مشخص است (شکل 1.1 را ببینید).

شکل نشان می دهد که برخی از گزینه های راه حل را می توان بلافاصله کنار گذاشت. هنگام ارزیابی اثربخشی با دو شاخص، کدام گزینه باید ترجیح داده شود. بدیهی است آنهایی که به طور همزمان در هر دو مرز راست و پایین منطقه قرار دارند (در شکل 1.1 - خط نقطه چین). که چهار گزینه باقی مانده است: X 16، X 17، X 19، X 20. از این میان، X 16 موثرترین، اما نسبتاً گران است. X 20 ارزان ترین است، اما نه به همان اندازه موثر. وظیفه تصمیم گیرنده این است که بفهمد با چه قیمتی می توانیم برای افزایش مشخصی در کارایی بپردازیم یا برعکس، مایلیم چه کارایی را قربانی کنیم تا متحمل خسارات مادی خیلی بزرگ نشویم.

S x

ایکس

ایکس ایکس ایکس

ایکس ایکس ایکس

ایکس ایکس

ایکس ایکس ایکس

ایکس ایکس

ایکس ایکس

ایکس

ایکس ایکس

با توجه به اینکه ارزیابی جامع یک عملیات با استفاده از چندین شاخص به طور همزمان دشوار است، در عمل چندین شاخص در یک شاخص تعمیم یافته ترکیب می شوند. غالباً کسری به عنوان چنین معیاری در نظر گرفته می شود. در شمارشگر آن شاخص های W 1 ,…, W را قرار دهید ، که مطلوب است افزایش یابد و در مخرج - مواردی که مطلوب است کاهش یابد:

U=
(4)

یک عیب کلی معیار نوع (4) این است که عدم اثربخشی در یک شاخص همیشه می تواند توسط شاخص دیگر جبران شود (مثلاً احتمال کم انجام یک ماموریت جنگی به دلیل مصرف کم مهمات و غیره).

اغلب، معیارهای ترکیبی «مجموع موزون» شاخص‌های عملکرد فردی فرض می‌شوند:

U=α
+α
+…+α
(5)

که در آن α ضرایب مثبت یا منفی هستند.

موارد مثبت برای آن شاخص هایی تنظیم شده است که مطلوب است حداکثر شود. منفی - برای کسانی که مطلوب است به حداقل برسد. مقادیر مطلق ضرایب با درجه اهمیت شاخص ها مطابقت دارد. معیار نوع (5) دارای همان اشکال است (امکان جبران متقابل شاخص های ناهمگن) و می تواند منجر به توصیه های نادرست شود. اما در مواردی که α i خودسرانه انتخاب نمی شود، بلکه طوری انتخاب می شود که معیار ترکیبی بهترین باشد. عملکرد خود را انجام داد، می توان با کمک آن به نتایجی با ارزش محدود دست یافت.

در برخی موارد، اگر یکی از شاخص های کارایی W 1 (اصلی) را مشخص کند و در تلاش برای به حداکثر رساندن آن باشد، می تواند به مشکل با یک شاخص تقلیل یابد و فقط برخی محدودیت های شکل را بر روی شاخص های کمکی باقی مانده W اعمال کند. 2، W 3، ...

دبلیو
; …دبلیو
; دبلیو
; ... W

سپس این محدودیت ها در مجموعه شرایط مشخص شده a 1, a 2,… گنجانده می شود.

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

راه ممکن دیگری برای ایجاد یک راه حل مصالحه وجود دارد که می توان آن را " روش امتیازات پی در پی" بگذارید شاخص های عملکرد به ترتیب کاهش اهمیت مرتب شوند: ابتدا W 1 اصلی، سپس سایر موارد کمکی: W 2، W 3، .... برای سادگی، فرض می کنیم که هر یک از آنها باید به حداکثر تبدیل شوند (اگر اینطور نیست، کافی است علامت توان را تغییر دهید). روش ساخت یک راه حل مصالحه به شرح زیر است. ابتدا به دنبال راه حلی هستیم که شاخص کارایی اصلی W 1 را به حداکثر برساند. سپس، بر اساس ملاحظات عملی و دقتی که داده های اولیه با آن شناخته می شوند (اغلب کوچک)، یک "امتیاز" مشخص ΔW 1 اختصاص داده می شود که ما موافقت می کنیم برای به حداکثر رساندن شاخص دوم W 2 اجازه دهیم. ما محدودیتی را روی نشانگر W 1 اعمال می کنیم تا کمتر از W 1 * - ∆W 1 نباشد (W 1 * حداکثر مقدار ممکن W 1 است) و با این محدودیت به دنبال راه حلی هستیم که W 2 را تبدیل کند. به حداکثر.

در مرحله بعد ، "امتیاز" دوباره در شاخص W 2 اختصاص داده می شود که با هزینه آن می توان W 3 و غیره را به حداکثر رساند. این روش خوب است زیرا بلافاصله مشخص می شود که "امتیاز" در یک شاخص در شاخص دیگر به چه قیمتی به دست می آید. در عین حال، آزادی انتخاب راه حل، که به قیمت امتیازات جزئی به دست آمده است، می تواند قابل توجه باشد، زیرا در ناحیه حداکثر، کارایی محلول معمولاً تغییر بسیار کمی دارد.

به هر طریقی، با هر روش رسمی سازی، وظیفه اثبات کمی یک تصمیم بر اساس چندین شاخص به طور کامل تعریف نشده است و انتخاب نهایی راه حل با عمل ارادی "فرمانده" تعیین می شود. وظیفه محقق این است که مقدار کافی از داده ها را در اختیار «فرمانده» قرار دهد و به او اجازه دهد تا مزایا و معایب هر گزینه راه حل را به طور جامع ارزیابی کند و بر اساس آنها، انتخاب نهایی را انجام دهد.

در قسمت دوم آموزش تابع ما VLOOKUP(VLOOKUP) در اکسل به چندین نمونه نگاه خواهیم کرد که به شما کمک می کند تمام توان خود را هدایت کنید VLOOKUPبرای حل بلندپروازانه ترین مشکلات اکسل. مثال‌ها فرض می‌کنند که شما قبلاً دانش پایه‌ای در مورد نحوه عملکرد این تابع دارید. اگر نه، ممکن است علاقه مند باشید که با قسمت اول این آموزش شروع کنید، که نحو و استفاده اولیه را توضیح می دهد. VLOOKUP. خوب، بیایید شروع کنیم.

جستجو در اکسل با استفاده از چندین معیار

تابع VLOOKUPاکسل یک ابزار واقعا قدرتمند برای جستجوی یک مقدار خاص در پایگاه داده است. با این حال، یک محدودیت قابل توجه وجود دارد - نحو آن به شما امکان می دهد فقط یک مقدار را جستجو کنید. اگر لازم باشد با استفاده از چندین شرط جستجو کنید، چه؟ راه حل را در زیر پیدا خواهید کرد.

مثال 1: جستجو با استفاده از 2 معیار مختلف

فرض کنید لیستی از سفارشات داریم و می خواهیم پیدا کنیم مقدار کالا(تعداد)، بر اساس دو معیار - نام مشتری(مشتری) و نام محصول(تولید - محصول). موضوع از آنجایی پیچیده می شود که هر یک از خریداران چندین نوع کالا را سفارش داده اند، همانطور که در جدول زیر مشاهده می شود:

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

VLOOKUP(B1,$A$5:$C$14,3,FALSE)
=VLOOKUP(B1,$A$5:$C$14,3,FALSE)

- این فرمول نتیجه را برمی گرداند 15 ، مربوط به محصول است سیب، زیرا اولین مقداری است که مطابقت دارد.

یک راه حل ساده وجود دارد - یک ستون اضافی ایجاد کنید که در آن تمام معیارهای لازم را ترکیب کنید. در مثال ما اینها ستون ها هستند نام مشتری(مشتری) و نام محصول(تولید - محصول). به یاد داشته باشید که ستون ادغام شده باید همیشه سمت چپ ترین ستون در محدوده جستجو باشد، زیرا این ستون سمت چپ است که تابع VLOOKUPهنگام جستجوی یک مقدار به نظر می رسد.

بنابراین، شما یک ستون کمکی به جدول اضافه می کنید و فرمولی مانند این را در تمام سلول های آن کپی می کنید: =B2&C2. اگر می خواهید رشته خواناتر باشد، می توانید مقادیر به هم پیوسته را با فاصله جدا کنید: =B2&" "&C2. پس از این می توانید از فرمول زیر استفاده کنید:

VLOOKUP ("شیرینی های جرمی هیل"، $A$7:$D$18,4، FALSE)
=VLOOKUP("شیرینی های جرمی هیل";$A$7:$D$18,4;FALSE)

VLOOKUP(B1,$A$7:$D$18,4,FALSE)
=VLOOKUP(B1,$A$7:$D$18,4,FALSE)

سلول کجاست B1حاوی مقدار الحاقی آرگومان است lookup_value(جستجوی_مقدار)، و 4 - بحث و جدل col_index_num(شماره_ستون)، یعنی. شماره ستون حاوی داده هایی که باید بازیابی شوند.

مثال 2: VLOOKUP بر اساس دو معیار با جدول در صفحه دیگری مشاهده می شود

اگر می‌خواهید جدول اصلی را با افزودن داده‌ها از یک جدول دوم (جدول جستجو) که در برگه دیگری یا در کتاب کار اکسل دیگری قرار دارد، به‌روزرسانی کنید، می‌توانید مقدار مورد نیاز را مستقیماً در فرمولی که در جدول اصلی وارد می‌کنید جمع‌آوری کنید. .

مانند مثال قبلی، به یک ستون کمکی در جدول Lookup با مقادیر ترکیب شده نیاز دارید. این ستون باید سمت چپ ترین ستون در محدوده مشخص شده برای جستجو باشد.

بنابراین، فرمول با VLOOKUPمی تواند اینگونه باشد:

VLOOKUP(B2&" "&C2,سفارشات!$A&$2:$D$2,4,FALSE)
=VLOOKUP(B2&" "سفارشات!$A&$2:$D$2;4;FALSE)

در اینجا، ستون های B و C به ترتیب شامل نام مشتری و نام محصول و پیوند هستند سفارشات!$A&$2:$D$2جدولی را برای جستجو در کاربرگ دیگر تعریف می کند.

برای خوانایی بیشتر فرمول، می‌توانید محدوده‌ای را که مشاهده می‌کنید یک نام بدهید، که فرمول را بسیار ساده‌تر نشان می‌دهد:

VLOOKUP(B2&" "&C2,Orders,4,FALSE)
=VLOOKUP(B2&" "Orders;4;FALSE)

برای اینکه فرمول کار کند، مقادیر در سمت چپ ترین ستون جدولی که مشاهده می کنید باید به همان روشی که در معیارهای جستجو ترکیب شده است، ترکیب شوند. در شکل بالا، مقادیر را ترکیب کرده و بین آنها فاصله گذاشتیم، در آرگومان اول تابع (B2&" "&C2") باید همین کار را انجام داد.

یاد آوردن!تابع VLOOKUPمحدود به 255 کاراکتر، نمی تواند مقداری را که بیش از 255 کاراکتر طول دارد جستجو کند. این را در نظر داشته باشید و مطمئن شوید که طول مقدار مورد نظر شما از این حد تجاوز نکند.

موافقم که افزودن ستون کمکی ظریف ترین و همیشه قابل قبول ترین راه حل نیست. شما می توانید همین کار را بدون ستون کمکی انجام دهید، اما به فرمول بسیار پیچیده تر با ترکیبی از توابع نیاز دارد فهرست مطالب(INDEX) و همخوانی داشتن(همخوانی داشتن).

2، 3 و غیره را استخراج می کنیم. مقادیر با استفاده از VLOOKUP

شما قبلاً این را می دانید VLOOKUPمی تواند تنها یک مقدار منطبق، یا بهتر است بگوییم اولین مورد یافت شده را برگرداند. اما اگر این مقدار چندین بار در آرایه ای که مشاهده می کنید تکرار شود و بخواهید دومی یا سومی آن ها را استخراج کنید، چه؟ چه می شود اگر همه ارزش ها؟ مشکل به نظر پیچیده است، اما راه حلی وجود دارد!

فرض کنید یک ستون از جدول شامل نام مشتریان (نام مشتری) و دیگری شامل محصولات (محصول) است که آنها خریداری کرده اند. بیایید سعی کنیم محصولات 2، 3 و 4 خریداری شده توسط یک مشتری خاص را پیدا کنیم.

ساده ترین راه اضافه کردن یک ستون کمکی قبل از ستون است نام مشتریو آن را با نام مشتریان با تعداد تکرار هر نام پر کنید، به عنوان مثال. جان دو 1, جان دو 2و غیره. بیایید ترفند شماره گذاری را با استفاده از تابع انجام دهیم COUNTIF(COUNTIF)، با توجه به اینکه نام مشتریان در ستون B قرار دارد:

B2&COUNTIF($B$2:B2,B2)
=B2&COUNTIF($B$2:B2,B2)

پس از این می توانید از عملکرد عادی استفاده کنید VLOOKUPبرای پیدا کردن سفارش مورد نیاز مثلا:

• ما پیدا می کنیم 2 دن براون:

  VLOOKUP ("Dan Brown2"، $A$2:$C$16,3، FALSE)
  =VLOOKUP("Dan Brown2"، $A$2:$C$16,3، FALSE)

• ما پیدا می کنیم 3کالاهای سفارش شده توسط خریدار دن براون:

  VLOOKUP ("Dan Brown3"، $A$2:$C$16,3، FALSE)
  =VLOOKUP("Dan Brown3"، $A$2:$C$16,3، FALSE)

اگر فقط به دنبال آن هستید 2تکرار، می توانید این کار را بدون ستون کمکی با ایجاد یک فرمول پیچیده تر انجام دهید:

IFERROR(VLOOKUP($F$2،INDIRECT("$B$"&(MATCH($F$2،Table4,0)+2)&":$C16")،2،FALSE)،"")
=IFERROR(VLOOKUP($F$2،INDIRECT("$B$"&(MATCH($F$2،Table4,0)+2)&":$C16")،2،صحیح)"")

در این فرمول:

• 2 دلار فارنهایت- یک سلول حاوی نام خریدار (بدون تغییر است، لطفا توجه داشته باشید - پیوند مطلق است)؛
• $B$– ستون نام مشتری;
• جدول 4- میز شما (همچنین ممکن است محدوده منظمی در این مکان وجود داشته باشد)؛
• C16 دلار- سلول انتهای جدول یا محدوده شما.

این فرمول تنها مقدار تطبیق دوم را پیدا می کند. اگر نیاز به استخراج تکرارهای باقی مانده دارید، از راه حل قبلی استفاده کنید.

اگر به فهرستی از همه موارد منطبق نیاز دارید، از تابع استفاده کنید VLOOKUPدر اینجا هیچ کمکی وجود ندارد، زیرا هر بار فقط یک مقدار را برمی گرداند - همین. اما اکسل یک عملکرد دارد فهرست مطالب(INDEX) که به راحتی می تواند با این کار کنار بیاید. در مثال زیر متوجه خواهید شد که چنین فرمولی چگونه خواهد بود.

بازیابی تمام تکرارهای مقدار مورد نظر

همانطور که اشاره شد، VLOOKUPنمی تواند تمام مقادیر تکراری را از محدوده مورد مشاهده استخراج کند. برای انجام این کار، به فرمول کمی پیچیده‌تر نیاز دارید که از چندین تابع اکسل تشکیل شده است، مانند فهرست مطالب(فهرست مطالب)، کم اهمیت(کوچک) و ROW(خط)

به عنوان مثال، فرمول زیر تمام رخدادهای یک مقدار از سلول F2 را در محدوده B2:B16 پیدا می کند و نتیجه را از همان ردیف های ستون C برمی گرداند.

(=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1"")،ROW()-3))"") )
(=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1;"");ROW()-3));"") )

این فرمول آرایه را در چندین سلول مجاور وارد کنید، به عنوان مثال. F4:F8، همانطور که در شکل زیر نشان داده شده است. تعداد سلول ها باید برابر یا بیشتر از حداکثر تعداد تکرارهای ممکن مقدار جستجو باشد. فراموش نکنید که کلیک کنید Ctrl+Shift+Enterتا فرمول آرایه را به درستی وارد کنید.

اگر علاقه مند به درک نحوه عملکرد آن هستید، اجازه دهید کمی عمیق تر به جزئیات فرمول بپردازیم:

قسمت 1:

IF($F$2=B2:B16,ROW(C2:C16)-1"")
IF($F$2=B2:B16;ROW(C2:C16)-1;"")

$F$2=B2:B16- مقدار سلول F2 را با هر یک از مقادیر در محدوده B2:B16 مقایسه کنید. اگر مطابقت پیدا شد، عبارت ROW(C2:C16)-1شماره سطر مربوطه (مقدار -1 به شما امکان می دهد خط هدر را حذف کنید). اگر هیچ منطبقی وجود نداشته باشد، تابع اگر(IF) یک رشته خالی را برمی گرداند.

نتیجه عملکرد اگر(اگر) یک آرایه افقی مانند این وجود خواهد داشت: (1""""""""""""""""""،""،""،"""،"""""12" """"" )

قسمت 2:

ROW()-3
ROW()-3

در اینجا تابع است ROW(ROW) به عنوان یک شمارنده اضافی عمل می کند. از آنجایی که فرمول در سلول های F4:F9 کپی می شود، عدد را کم می کنیم 3 از نتیجه تابع برای بدست آوردن مقدار 1 در یک سلول F4(خط 4، 3 را کم کنید) تا به دست آید 2 در یک سلول F5(خط 5، تفریق 3) و غیره.

قسمت 3:

SMALL(IF($F$2=B2:B16،ROW(C2:C16)-1"")،ROW()-3))
SMALL(IF($F$2=B2:B16،ROW(C2:C16)-1"")،ROW()-3))

تابع کم اهمیت(SMALL) برمی گردد نهمینکوچکترین مقدار در آرایه داده در مورد ما، کدام موقعیت (از کوچکترین) برای بازگشت توسط تابع تعیین می شود ROW(خط) (بخش 2 را ببینید). بنابراین، برای سلول F4تابع SMALL((آرایه);1)برمی گرداند 1(کوچکترین) عنصر آرایه، یعنی 1 . برای سلول F5برمی گرداند 2کوچکترین عنصر آرایه، یعنی 3 ، و غیره.

قسمت 4:

INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1"")ROW()-3))
INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1;"")،ROW()-3))

تابع فهرست مطالب(INDEX) به سادگی مقدار یک سلول خاص در یک آرایه را برمی گرداند C2: C16. برای سلول F4تابع INDEX($C$2:$C$16,1)بر خواهد گشت سیب، برای F5تابع INDEX($C$2:$C$16,3)بر خواهد گشت شیرینیو غیره

قسمت 5:

IFERROR()
IFERROR()

در نهایت فرمول را داخل تابع قرار می دهیم IFERROR(IFERROR)، زیرا بعید است از پیام خطا راضی باشید #عدم(#N/A) اگر تعداد سلول هایی که فرمول در آنها کپی شده کمتر از تعداد مقادیر تکراری در محدوده مشاهده شده باشد.

جستجوی دو بعدی با سطر و ستون شناخته شده

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

پس بیایید به جدول خود برویم و فرمول را با تابع یادداشت کنیم VLOOKUP، که اطلاعاتی در مورد هزینه فروش لیمو در ماه مارس پیدا خواهد کرد.

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

توابع VLOOKUP و MATCH

می توانید از یک سری توابع استفاده کنید VLOOKUP(VLOOKUP) و جستجو کردن(MATCH) برای یافتن مقدار در تقاطع فیلدها نام محصول(رشته) و ماه(ستون) آرایه مورد نظر:

VLOOKUP("لیمو"، $A$2:$I$9، MATCH("مار"،$A$1:$I$1,0)، FALSE)
=VLOOKUP("لیموها"،$A$2:$I$9،MATCH("مار"،$A$1:$I$1,0)،FALSE)

فرمول بالا یک تابع منظم است VLOOKUP، که به دنبال تطابق دقیق مقدار "Lemons" در سلول های A2 تا A9 است. اما از آنجایی که نمی دانید فروش ماه مارس در کدام ستون است، نمی توانید شماره ستون را برای آرگومان سوم تابع مشخص کنید. VLOOKUP. در عوض از تابع استفاده می شود جستجو کردنبرای تعریف این ستون

MATCH ("مار"، $A$1:$I$1.0)
MATCH ("مار"، $A$1:$I$1.0)

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

• ما به دنبال نمادهای "Mar" - آرگومان هستیم lookup_value(جستجوی_مقدار)؛
• ما در سلول های A1 تا I1 برای آرگومان جستجو می کنیم lookup_array(نمایش_آرایه)؛
• برگرداندن یک تطابق دقیق - آرگومان مطابقت_نوع(مطابق_نوع).

استفاده كردن 0 در آرگومان سوم، تابع را بگویید جستجو کردنبه دنبال اولین مقداری باشید که دقیقاً با مقدار جستجو مطابقت دارد. این معادل مقدار است نادرست(FALSE) برای استدلال چهارم VLOOKUP.

به این ترتیب می توانید فرمولی برای جستجوی دوطرفه در اکسل ایجاد کنید که به جستجوی دو بعدی یا جستجوی دوطرفه نیز معروف است.

عملکرد SUMPRODUCT

تابع SUMPRODUCT(SUMPRODUCT) مجموع حاصل از آرایه های انتخاب شده را برمی گرداند:

SUMPRODUCT(($A$2:$A$9="لیموها")*($A$1:$I$1="مار")،$A$2:$I$9)
=SUMPRODUCT(($A$2:$A$9="لیموها")*($A$1:$I$1="مار");$A$2:$I$9)

توابع INDEX و MATCH

من این توابع را در مقاله بعدی با جزئیات کامل توضیح خواهم داد، بنابراین در حال حاضر فقط می توانید این فرمول را کپی کنید:

INDEX($A$2:$I$9،MATCH(«لیموها»،$A$2:$A$9.0)،MATCH(«مارس»،$A$1:$I$1.0))
=INDEX($A$2:$I$9,MATCH("لیمو"،$A$2:$A$9,0)،MATCH("مار"،$A$1:$I$1,0))

به نام Ranges and the Intersection Operator

اگر دیوانه همه آن فرمول های پیچیده اکسل نیستید، ممکن است این روش بصری و به یاد ماندنی را دوست داشته باشید:

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

1. کلیک واردو نتیجه را بررسی کنید

به طور کلی، هر کدام از روش های بالا را انتخاب کنید، نتیجه جستجوی دو بعدی یکسان خواهد بود:

ما از چندین VLOOKUP در یک فرمول استفاده می کنیم

این اتفاق می افتد که جدول اصلی و جدول جستجو یک ستون مشترک ندارند و این مانع استفاده از تابع معمول می شود. VLOOKUP. با این حال، جدول دیگری وجود دارد که حاوی اطلاعات مورد نظر ما نیست، اما دارای یک ستون مشترک با جدول اصلی و جدول جستجو است.

بیایید به مثال زیر نگاه کنیم. ما یک جدول اصلی با یک ستون داریم SKU (جدید)، جایی که باید ستونی با قیمت های مربوطه از جدول دیگری اضافه کنید. علاوه بر این، ما 2 جدول جستجو داریم. اولین (جدول جستجوی 1) شامل اعداد به روز شده است SKU (جدید)و نام محصولات، و دوم (جدول جستجوی 2) - نام محصولات و شماره های قدیمی SKU (قدیمی).

برای اضافه کردن قیمت ها از جدول جستجوی دوم به جدول اصلی، باید عملی را انجام دهید که به عنوان double شناخته می شود VLOOKUPیا تو در تو VLOOKUP.

1. تابع را بنویسید VLOOKUP، که نام محصول را در جدول پیدا می کند جدول جستجو 1استفاده كردن SKU، به عنوان مقدار مورد نظر:

   VLOOKUP(A2،New_SKU،2،FALSE)
   =VLOOKUP(A2،New_SKU،2،FALSE)

   اینجا New_SKU- محدوده نامگذاری شده $A:$Bدر جدول جدول جستجو 1، آ 2 – این ستون B است که حاوی نام محصولات است (تصویر بالا را ببینید)

2. فرمولی برای درج قیمت ها از جدول بنویسید جدول جستجوی 2بر اساس نام های شناخته شده محصولات برای انجام این کار، فرمولی را که قبلا ایجاد کردید به عنوان مقدار جستجوی تابع جدید وارد کنید VLOOKUP:

   VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),قیمت,3,FALSE)
   =VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),قیمت,3,FALSE)

   اینجا قیمت- محدوده نامگذاری شده $A:$Cدر جدول جدول جستجوی 2، آ 3 ستون C حاوی قیمت است.

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

جایگزینی دینامیک داده ها از جداول مختلف با استفاده از VLOOKUP و INDIRECT

ابتدا، بیایید منظورمان از عبارت «جایگزینی پویا از داده‌ها از جداول مختلف» چیست تا مطمئن شویم که یکدیگر را به درستی درک می‌کنیم.

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

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

اگر فقط دو گزارش از این قبیل دارید، می توانید از یک فرمول بسیار ساده با توابع استفاده کنید VLOOKUPو اگر(IF) برای انتخاب گزارش مورد نظر برای جستجو:

VLOOKUP($D$2,IF($D3="FL,FL_Sales,CA_Sales),2,FALSE)
=VLOOKUP($D$2,IF($D3="FL,FL_Sales,CA_Sales),2,FALSE)

• 2 دلار D$- این یک سلول حاوی نام محصول است. لطفاً توجه داشته باشید که ما در اینجا از ارجاعات مطلق استفاده می کنیم تا از تغییر مقدار جستجو هنگام کپی کردن فرمول در سلول های دیگر جلوگیری کنیم.
• D3 دلار– این یک سلول با نام منطقه است. ما از یک مرجع مطلق برای ستون و یک مرجع نسبی برای سطر استفاده می کنیم زیرا قصد داریم فرمول را در سلول های دیگر همان ستون کپی کنیم.
• FL_Salesو CA_Sales- نام جداول (یا محدوده های نامگذاری شده) که حاوی گزارش های فروش مربوطه است. البته می‌توانید از نام‌های برگه معمولی و مراجع محدوده سلولی استفاده کنید، به عنوان مثال. «ورق FL»! $A$3:$B$10، اما محدوده های نامگذاری شده بسیار راحت تر هستند.

با این حال، زمانی که چنین جداول زیادی وجود دارد، تابع اگر- این بهترین راه حل نیست. در عوض، می توانید از تابع استفاده کنید غیر مستقیم(INDIRECT) تا محدوده جستجوی مورد نظر را برگردانید.

همانطور که احتمالا می دانید، تابع غیر مستقیمبرای برگرداندن یک پیوند داده شده یک رشته متن استفاده می شود، که دقیقاً همان چیزی است که اکنون به آن نیاز داریم. بنابراین، با خیال راحت، عبارت را با تابع موجود در فرمول بالا جایگزین کنید اگربه پیوند با تابع غیر مستقیم. این ترکیب است VLOOKUPو غیر مستقیمبه صورت جفت عالی کار می کند:

VLOOKUP($D$2،INDIRECT($D3&"_Sales")،2،FALSE)
=VLOOKUP($D$2،INDIRECT($D3&"_Sales")،2،FALSE)

• 2 دلار D$– این یک سلول با نام محصول است، به دلیل پیوند مطلق بدون تغییر است.
• D3 دلار– این سلول حاوی اولین قسمت از نام منطقه است. در مثال ما این است FL.
• _حراجی- قسمت مشترک نام همه محدوده ها یا جداول نامگذاری شده. ترکیب با مقدار موجود در سلول D3، نام کامل محدوده مورد نظر را تشکیل می دهد. در زیر برخی از جزئیات برای کسانی که تجربه ای با عملکرد ندارند آورده شده است غیر مستقیم.

INDIRECT و VLOOKUP چگونه کار می کنند

ابتدا، اجازه دهید دستور تابع را به شما یادآوری کنم غیر مستقیم(غیر مستقیم):

• A1، اگر استدلال برابر باشد درست است، واقعی(درست) یا مشخص نشده است.
• R1C1، اگر افALSE(دروغ).

پس بیایید به گزارش های فروش خود برگردیم. اگر به خاطر داشته باشید، هر گزارش یک جدول جداگانه است که در یک برگه جداگانه قرار دارد. برای اینکه فرمول به درستی کار کند، باید به جداول (یا محدوده) خود نام بدهید و همه نام ها باید یک قسمت مشترک داشته باشند. به عنوان مثال، مانند این: CA_Sales, FL_Sales, TX_Salesو غیره همانطور که می بینید، همه نام ها حاوی "_Sales" هستند.

تابع غیر مستقیممقدار در ستون D و رشته متن "_Sales" را به هم وصل می کند و بدین ترتیب گزارش می دهد VLOOKUPکدام جدول را نگاه کنیم اگر مقدار "FL" در سلول D3 باشد، فرمول جدول را جستجو می کند FL_Sales، اگر "CA" در جدول باشد CA_Salesو غیره

نتیجه توابع VLOOKUPو غیر مستقیمبه شرح زیر خواهد بود:

اگر داده ها در کتاب های کار اکسل مختلف قرار دارند، باید نام کتاب کار را قبل از محدوده نامگذاری شده اضافه کنید، به عنوان مثال:

VLOOKUP($D$2،INDIRECT($D3&"Workbook1!_Sales")،2،FALSE)
=VLOOKUP($D$2،INDIRECT($D3&"Workbook1!_Sales")،2،FALSE)

اگر تابع غیر مستقیمبه کتاب کار دیگری اشاره دارد، پس آن کتاب کار باید باز باشد. اگر بسته باشد، تابع یک خطا گزارش می دهد #مرجع!(#ارتباط دادن!).

KPI یک شاخص عملکرد است که به شما امکان می دهد اثربخشی اقدامات انجام شده را به طور عینی ارزیابی کنید. این سیستم برای ارزیابی شاخص های مختلف (فعالیت های کل شرکت، ساختارهای فردی، متخصصان خاص) استفاده می شود. این نه تنها عملکردهای کنترلی را انجام می دهد، بلکه فعالیت کار را نیز تحریک می کند. اغلب، یک سیستم پاداش بر اساس KPI ها ساخته می شود. این روشی برای تشکیل بخش متغیر حقوق است.

شاخص های عملکرد کلیدی KPI: نمونه هایی در اکسل

عامل محرک در سیستم انگیزش KPI، پاداش پولی است. می تواند توسط کارمندی که وظیفه محول شده به او را انجام داده است دریافت کند. مقدار پاداش/پاداش به عملکرد یک کارمند خاص در دوره گزارش بستگی دارد. میزان حق الزحمه می تواند ثابت یا به صورت درصدی از حقوق بیان شود.

هر شرکتی شاخص های کلیدی عملکرد و وزن هر یک را به صورت جداگانه تعیین می کند. داده ها به اهداف شرکت بستگی دارد. مثلا:

1. هدف دستیابی به یک برنامه فروش محصول 500000 روبل در ماه است. شاخص کلیدی برنامه فروش است. سیستم اندازه گیری: مقدار واقعی فروش / مبلغ فروش برنامه ریزی شده.
2. هدف افزایش 20 درصدی میزان محموله ها در این دوره است. شاخص کلیدی میانگین مقدار حمل و نقل است. سیستم اندازه گیری: میانگین حمل و نقل واقعی / حمل و نقل متوسط ​​برنامه ریزی شده.
3. هدف افزایش 15 درصدی تعداد مشتریان در یک منطقه خاص است. شاخص کلیدی تعداد مشتریان در پایگاه داده سازمانی است. سیستم اندازه گیری: تعداد واقعی مشتریان / تعداد برنامه ریزی شده مشتریان.

شرکت همچنین به طور مستقل گسترش ضریب (وزن) را تعیین می کند. مثلا:

1. اجرای طرح کمتر از 80 درصد غیرقابل قبول است.
2. اجرای طرح 100% - ضریب 0.45.
3. اجرای طرح 100-115٪ - ضریب 0.005 برای هر 5٪.
4. بدون خطا - ضریب 0.15.
5. در طول دوره گزارش هیچ نظری وجود نداشت - ضریب 0.15.

این تنها یک گزینه ممکن برای تعیین ضرایب انگیزشی است.

نکته کلیدی در اندازه گیری KPI، نسبت شاخص واقعی به شاخص برنامه ریزی شده است. تقریباً همیشه، حقوق یک کارمند شامل حقوق (قسمت ثابت) و پاداش (بخش متغیر / متغیر) است. ضریب انگیزه بر شکل گیری متغیر تأثیر می گذارد.

بیایید فرض کنیم که نسبت بخش های ثابت و متغیر در حقوق 50 × 50 باشد. شاخص های کلیدی عملکرد و وزن هر یک از آنها:

اجازه دهید مقادیر ضرایب زیر را بپذیریم (برای اندیکاتور 1 و شاخص 2 یکسان است):

جدول KPI در اکسل:

توضیحات:

این یک نمونه جدول KPI در اکسل است. هر شرکتی خود را می سازد (با در نظر گرفتن ویژگی های کار و سیستم پاداش).

ماتریس KPI و مثال در اکسل

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

1. شاخص های کلیدی معیارهایی هستند که بر اساس آنها کار پرسنل ارزیابی می شود. آنها برای هر موقعیت متفاوت هستند.
2. وزن ها اعدادی در محدوده 0 تا 1 هستند که مجموع آنها 1 است. آنها اولویت های هر شاخص کلیدی را با در نظر گرفتن اهداف شرکت منعکس می کنند.
3. پایه - حداقل مقدار قابل قبول شاخص. زیر سطح پایه - بدون نتیجه.
4. هنجار - سطح برنامه ریزی شده کاری که یک کارمند باید انجام دهد. در زیر - کارمند نتوانست با وظایف خود کنار بیاید.
5. هدف ارزشی است که باید برای آن تلاش کرد. یک شاخص بالاتر از استاندارد که به شما امکان می دهد نتایج را بهبود بخشید.
6. واقعیت - نتایج واقعی کار.
7. شاخص KPI سطح نتایج را در رابطه با هنجار نشان می دهد.

فرمول محاسبه kpi:

شاخص KPI = ((واقعی - پایه) / (هنجار - پایه)) * 100%.

نمونه ای از پر کردن ماتریس برای مدیر دفتر:

ضریب عملکرد مجموع حاصل از شاخص ها و وزن ها است. رتبه بندی عملکرد کارکنان به وضوح با استفاده از قالب بندی شرطی نشان داده می شود.

فرض کنید می خواهید مقادیر را با بیش از یک شرط جمع کنید، مانند مجموع فروش یک محصول در یک منطقه خاص. این یک استفاده خوب از تابع SUMIFS در یک فرمول است.

به این مثال توجه کنید که در آن دو شرط داریم: مقدار فروش مورد نیاز گوشت(از ستون C) به جنوبمناطق (از ستون a).

در اینجا فرمولی وجود دارد که می توان از آن برای انجام آن استفاده کرد:

= SUMIFS (D2: D11؛ a2: A11؛ "جنوب"؛ C2: C11؛ "گوشت")

نتیجه مقدار 14719 است.

بیایید به یک نمایش دقیق تر از هر بخش از فرمول نگاه کنیم.

SUMIFS یک فرمول حسابی است. اعدادی را محاسبه می کند که در این مورد در ستون D قرار دارند. اولین مرحله تعیین محل اعداد است.

=SUMIFS(D2:D11،

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

سپس باید داده ای را پیدا کنید که دارای دو شرط باشد و شرط اول را وارد کنید و برای تابع محل داده را مشخص کنید (a2: A11) و همچنین اینکه شرط "South" باشد. به کاما بین هر آرگومان توجه کنید.

=SUMIFS(D2:D11,A2:A11"South"؛

علامت نقل قول در اطراف کلمه "South" نشان می دهد که این داده متنی است.

در نهایت، آرگومان‌های شرط دوم را وارد می‌کنید - محدوده سلول‌ها (C2:C11) که حاوی کلمه "Meat" و همچنین خود کلمه (محصور در نقل قول) هستند تا اکسل بتواند آنها را مطابقت دهد. با بستن پرانتز بسته فرمول را کامل کنید ) و سپس Enter را فشار دهید. نتیجه دوباره 14719 است.

=SUMIFS(D2:D11,A2:A11,South,C2:C11,,Meat')

همانطور که تابع SUMIFS را در اکسل تایپ می کنید، اگر این آرگومان ها را به خاطر ندارید، Help آماده است. پس از وارد کردن فرمول = SUMIFS (فرمول های تکمیل خودکار در زیر فرمول ظاهر می شوند و لیست آرگومان ها به ترتیب مورد نیاز فهرست می شود.

به تصویر فرمول تکمیل خودکار و لیست آرگومان در مثال ما نگاه کنید sum_range D2:D11 است، ستون اعدادی که باید جمع شوند. محدوده_معیار1- a2. A11 - ستون داده حاوی شرط 1"جنوبی".

همانطور که فرمول را وارد می کنید، آرگومان های باقی مانده (در اینجا نشان داده نشده اند) در Formula AutoComplete ظاهر می شوند. محدوده_شرایط2محدوده C2:C11 است که ستونی از داده ها است که شامل می شود شرط 2- "گوشت".

اگر کلیک کنید SUMIFSهنگامی که فرمول را به صورت خودکار تکمیل می کنید، مقاله ای باز می شود که در آن می توانید اطلاعات بیشتری دریافت کنید.

سعی کنید تمرین کنید

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

در این اکسل برای کتاب کار وب، می توانید با داده ها و فرمول های نمونه درست در اینجا کار کنید. مقادیر و فرمول ها را تغییر دهید یا مقدار خود را اضافه کنید تا نتایج فوری تغییر کند.

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

VPR بر اساس دو یا چند معیار

مطمئناً همه کسانی که با تابع VLOOKUP آشنا هستند می دانند که مقادیر مشخص شده را منحصراً در ستون سمت چپ جدول مشخص شده جستجو می کند (شما می توانید اطلاعات بیشتری در مورد VLOOKUP در مقاله بخوانید: نحوه پیدا کردن یک مقدار در جدول دیگر یا قدرت VLOOKUP) . همچنین، بسیاری از مردم می دانند که VLOOKUP فقط بر اساس یک مقدار جستجو می کند.

آیا مقاله کمکی کرد؟ لینک را با دوستان خود به اشتراک بگذارید دروس تصویری

("نوار پایین":("textstyle":"ایستا"،"textpositionstatic":"پایین"،"textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"پایین سمت چپ"،"textpositionmarginleft":24," textpositionmarginright":24"textpositionmargintop":24"textpositionmarginbottom":24"texteffect":"slide"، "texteffecteasing":"easeOutCubic"، "texteffectduration":600، "texteffectslidedirection": "leftlid"، "texteffects" :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"اسلاید"،"texteffectslidedirection1":"راست"،"texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic:601"ext "texteffectdelay1":1000,"texteffect2":"slide", "texteffectslidedirection2":"راست"،"texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic"،"texteffectduration2":600""ext textcss":"display:block; padding:12px; text-align:left;""textbgcss":"display:block; position:absolute; top:0px; left:0px; عرض:100%؛ ارتفاع:100% ; background-color:#333333; opacity:0.6; filter:alpha(opacity=60);""titlecss":"display:block; موقعیت:نسبی; فونت:پررنگ 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; موقعیت:نسبی; فونت: 12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; موقعیت:نسبی; margin-top:8px;""texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;,""descriptioncssresponsive":"display:none !important;""buttoncssresponsive" """"addgooglefonts":false,""googlefonts":""، "textleftrightpercentforstatic":40))