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

جستجو برای مقادیر در جدول اکسل

برای حل این مشکل، ما یک مثال را بر روی یک جدول طرح بندی نشان می دهیم که در بالا شرایط توصیف شده مطابقت دارد.

ورق با یک جدول برای جستجو برای مقادیر عمودی و افقی:

بالاتر از جدول خود یک رشته با نتایج است. در سلول B1 درایو یک معیار برای پرس و جو جستجو، یعنی، هدر ستون یا نام رشته. و در سلول D1، فرمول جستجو باید نتیجه محاسبه مقدار مربوطه را بازگرداند. پس از آن، فرمول دوم در سلول F1 کار خواهد کرد، که در حال حاضر از مقادیر سلول های B1 و D1 به عنوان معیارهای جستجو برای ماه مربوطه استفاده می کند.

مقادیر جستجو در رشته اکسل

در حال حاضر ما یاد می گیریم که حداکثر حجم و در آن ماه حداکثر فروش کالا 4 بود.

برای جستجو برای ستون ها به شرح زیر است:

در آن ماه یافت شد و بزرگترین فروش کالاها 4 برای دو چهارم بود.

اصل مقدار فرمول جستجوی اقدام در خط اکسل:

در اولین استدلال، عملکرد PRD (Vertical View) نشان دهنده یک پیوند به یک سلول است که در آن معیارهای جستجو است. استدلال دوم نشان می دهد که طیف وسیعی از سلول ها برای مشاهده در طول فرآیند جستجو است. در استدلال سوم، عملکرد PRD باید توسط تعداد ستون نشان داده شود که از آن مقدار باید در برابر ردیف با نام کالا انجام شود. 4. اما از آنجایی که ما این شماره را پیش از آن نمی دانیم. ما از تابع ستون استفاده می کنیم. مجموعه ای از اعداد ستون را برای محدوده B4 ایجاد کنید: G15.

این اجازه می دهد تا توابع VDC جمع آوری کل مجموعه ای از مقادیر. به عنوان یک نتیجه، تمام مقادیر مربوط به هر ستون در مورد خط 4 در حافظه ذخیره می شود (یعنی: 360؛ 958؛ 201؛ 605؛ 462؛ 832). پس از آن، توابع حداکثر تنها برای گرفتن حداکثر تعداد از این آرایه باقی می مانند و به عنوان یک مقدار برای یک سلول D1، به عنوان نتیجه محاسبه فرمول بازگشت.

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

چگونه می توان ستون های ستون را بر روی یک سلول یک سلولی دریافت کرد؟

به عنوان مثال، به طور چشمگیری ما یک ماه نمایش داده شد که در آن حداکثر فروش با استفاده از فرمول دوم وجود داشت. دشوار نیست که در فرمول دوم ما از اسکلت فرمول اول بدون عملکرد حداکثر استفاده کردیم. ساختار اصلی فرمول: PRD (B1؛ A5: G14؛ ستون (B5: G14)؛ 0). ما تابع حداکثر را در جستجو جایگزین کردیم، که در اولین استدلال از مقدار بدست آمده توسط فرمول قبلی استفاده می شود. در حال حاضر به عنوان معیار برای جستجو یک ماه عمل می کند. و در نتیجه، تابع جستجو توسط ستون شماره 2 به ما بازگردانده می شود که حداکثر مقدار فروش کالا برای کالاها واقع شده است. پس از آن، تابع شامل یک تابع شاخص است که مقدار را با شماره و ستون از آن نشان می دهد محدوده تعریف شده در استدلال های آن. از آنجایی که ما یک ستون شماره 2 داریم و شماره ردیف در محدوده ای که اسامی ماه ها در هر مورد ذخیره می شود، 1. پس از آن یک تابع از شاخص برای به دست آوردن مقدار مربوطه از محدوده B4 باقی می ماند: G4 - فوریه (ماه دوم).



مقادیر جستجو در ستون اکسل

وظیفه دوم این است که جدول را با استفاده از نام ماه به عنوان یک معیار جستجو کنید. در چنین مواردی، ما باید اسکلت فرمول ما را تغییر دهیم: عملکرد UPR با GRP جایگزین می شود و عملکرد ستون با یک رشته جایگزین می شود.

این به ما اجازه خواهد داد که چه حجمی را پیدا کنیم و کدام محصول حداکثر فروش در یک ماه خاص بود.

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

اصل فرمول جستجوی عمل در ستون اکسل:

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

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

توجه! هنگام استفاده از یک اسکلت فرمول برای سایر وظایف، همیشه به بحث دوم و سوم تابع جستجو GRP توجه کنید. تعداد ردیف های تحت پوشش در محدوده استدلال مشخص شده در این استدلال باید با تعداد ردیف ها در جدول هماهنگ شود. همچنین، شماره گیری باید با ردیف دوم شروع شود!

قالب بندی مشروط (5)
لیست ها و محدوده ها (5)
ماکرو (روش VBA) (63)
متفرقه (39)
اشکالات اکسل و زلزله (3)

نحوه پیدا کردن یک مقدار در جدول دیگری یا قدرت PRP

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

و از جدول اول باید به تاریخ دوم برای هر نام جایگزین شود. برای سه ضبط، این یک مشکل نیست و دست ها را ایجاد می کند - همه چیز واضح است. اما در زندگی، این ها در هزاران ورودی جداول هستند و جستجو با جایگزینی داده ها به صورت دستی ممکن است یک ساعت طول بکشد. به علاوه یک جفت دیگر از پرواز در هدف: نه تنها این واقعیت است که نام به طور کامل در هر دو جداول واقع شده است و تعداد سوابق در جداول متفاوت است، بنابراین جداول بیشتری در ورق های مختلف قرار دارند. من فکر می کنم که من شما را متقاعد کردم که جایگزینی این دست ها گزینه ای نیست. ولی vlookup) این در اینجا ضروری خواهد بود. در عین حال، عملا هیچ چیز لازم نیست - فقط در اولین سلول ستون از جدول دوم بنویسید (جایی که لازم است تاریخ را از جدول اول جایگزین کنید) چنین فرمول:
\u003d UPR ($ a2؛ list1! $ $ 2: $ c $ 4؛ 3؛ 0)
شما می توانید فرمول را به طور مستقیم به سلول بنویسید یا با استفاده از Dispatcher تابع با انتخاب در رده لینک ها و آرایه ها PRD و به طور جداگانه مشخص کردن معیارهای لازم. در حال حاضر کپی ( سیپوش+C.) سلول با یک فرمول که تمام سلول های ستون را به انتهای داده ها اختصاص داده و وارد کنید ( سیپوش+V.).

اول، اصل اصلی عملیات: UPR به دنبال مقدار جدول مشخص شده توسط استدلال در ستون اول استدلال است skeeping_new . هنگامی که مقدار مورد نظر را پیدا می کنید، تابع مقدار را در مقابل مقدار یافت می شود، اما از ستون مشخص شده توسط این استدلال number_stolbets . با مشاهده فاصله، ما کمی بعد آن را می بینیم. VDP می تواند تنها یک مقدار را بازگرداند - اولین مناسب برای معیار. اگر مقدار مورد نظر یافت نشد (گم شده در جدول)سپس نتیجه تابع خواهد بود # n / d . از این نترسید - حتی مفید است. شما دقیقا می دانید چه سوابق نیست و بنابراین شما می توانید دو جداول را با یکدیگر مقایسه کنید. گاهی اوقات معلوم می شود که می بینید: داده ها در هر دو جداول است، اما مسائل VD # n / d. بنابراین داده ها در جداول شما یکسان نیستند. در برخی از آنها شکاف های اضافی غیر قابل تشخیص وجود دارد (معمولا قبل از مقدار یا بعد از آن)، یا نشانه های سیریلیک با علائم لاتین مخلوط می شوند. یکسان # n / d اگر معیارهای شماره و در جدول مورد نظر آنها به عنوان متن ثبت شود، خواهد بود (به عنوان یک قاعده، مثلث سبز در گوشه سمت چپ بالای این سلول ظاهر می شود)، و در نهایی - به عنوان اعداد. یا برعکس.

شرح استدلال استدلال
$ A2 - استدلال skeeping_new (بیایید آن را بفرستیم معیار کوتاه بودن) این چیزی است که ما دنبال آن هستیم. کسانی که. برای اولین رکورد جدول دوم، پتروف S.A خواهد بود در اینجا شما می توانید متن معیار را مشخص کنید (در این مورد، باید در نقل قول ها باشد - \u003d VD ("Petrov SA"؛ List1! $ $ 2: $ $ 4؛ 3؛ 0) یا یک لینک به سلول، با این متن (همانطور که در مثال تابع). یک نکته کوچک وجود دارد: شما همچنین می توانید نمادهای جایگزینی را اعمال کنید: "*" و "؟". این بسیار راحت است اگر شما نیاز به پیدا کردن مقادیر تنها بخشی از خط. به عنوان مثال، شما نمی توانید به طور کامل "Petrov S.A." وارد شوید، اما فقط نام خانوادگی و علامت ستاره را معرفی کنید - "Petrov *". سپس هر ورودی از بین می رود، که از پتروف شروع می شود. اگر شما نیاز به پیدا کردن یک ورودی که در آن نام "Petrov" در هر نقطه از خط یافت می شود، پس شما می توانید این را مشخص کنید: "* Petrov *." اگر می خواهید نام خانوادگی Petrov را پیدا کنید و مهم نیست که کدام حروف به نام نام شخصی (اگر نام در فرم Ivanov I.I ثبت شده باشد)، پس از آن در اینجا بهترین است: "ایوانوف؟." . اغلب لازم است مقدار آن را برای هر خط (در ستون و نام خانوادگی مشخص کنید و شما باید همه آنها را پیدا کنید). در این مورد، مراجع به سلول های ستون A. به عنوان مثال، در سلول A1 ثبت شده: ایوانوف. همچنین شناخته شده است که ایوانوف در جدول دیگری قرار دارد، اما پس از نام خانوادگی، نام و نامتال ممکن است ثبت شود (یا چیز دیگری). اما ما باید تنها یک رشته ای را که در نام خانوادگی شروع می شود پیدا کنیم. سپس شما باید به صورت زیر ثبت کنید: A1 & "*". این مطلب برابر با "ایوانوف *" خواهد بود. در A1، ایوانوف ضبط شده است، Ampersand (&) برای ادغام به یک خط از دو مقادیر متن استفاده می شود. ستاره ای در نقل قول ها (همانطور که باید متن درون فرمول باشد). بنابراین ما دریافت می کنیم:
A1 & "*" \u003d\u003e
"Ivanov" & "*" \u003d\u003e
"ایوانوف *"
بسیار راحت اگر شخصیت های زیادی برای جستجو وجود دارد.
اگر شما نیاز به تعیین اینکه آیا یک کلمه در جایی وجود دارد وجود دارد، سپس ستاره ها در هر دو طرف قرار می گیرند: "*" & A1 & "*"

ورق 1 $ $ 2: $ C $ 4 - استدلال جدول. محدوده سلول ها را مشخص می کند. فقط محدوده باید شامل داده ها از سلول اول با داده ها به MOSTST باشد. این نباید در محدوده مثال مشخص شود. اگر رشته ها 100، سپس list1! $ $ 2: $ c $ 100. مهم است که سه چیز را به یاد داشته باشید: اول، آن جدول باید همیشه با آن ستون شروع شود که در آن ما به دنبال آن هستیم معیار . و دیگر هیچ. در غیر این صورت، هیچ چیز یافت نشد یا نتیجه دقیقا همان چیزی نیست که انتظار می رود. دوم: استدلال جدول باید "ثابت" . چه مفهومی داره. علائم دلار را ببینید - $؟ این اصلاح است (دقیق تر، این یک پیوند مطلق به محدوده نامیده می شود). چگونه انجام می شود. متن پیوند را تخصیص دهید (فقط یک محدوده یک معیار است) و zhmete F4 تا زمانی که ببینیم که دلار قبل از نام تعیین نام ستون و قبل از شماره خط نمایش داده نشود. اگر این کار انجام نشود، هنگام کپی کردن فرمول، جدول استدلال "حرکت می کند" و نتیجه دوباره نادرست خواهد بود. و آخرین - جدول باید شامل ستون ها از اول (که در آن ما به دنبال آن) به دوم (که از آن ضروری است برای بازگشت ارزش ها). در مثال list1! $ $ 2: $ C $ 4 - بنابراین ممکن نیست که مقدار را از ستون D (4) بازگرداند، زیرا جدول تنها سه ستون است.

3 - number_stolbets . در اینجا به سادگی شماره ستون را در استدلال مشخص کنید جدول، ارزش هایی که ما باید به عنوان یک نتیجه جایگزین کنیم. در مثال، این تاریخ تصویب است - I.E. شماره ستون 3. اگر من به یک بخش نیاز داشتم، من 2 را نشان دادم، و اگر ما نیاز به مقایسه کنیم، اگر یک نام جدول در دیگری وجود داشته باشد، ممکن است نشان دهد و 1. مهم است: بحث number_stolbets نباید تعداد ستون ها را در این استدلال تجاوز کند جدول . در غیر این صورت نتیجه فرمول یک خطا خواهد بود #ارتباط دادن!. به عنوان مثال، اگر طیف وسیعی از $ B $ 2: $ C $ 4 مشخص شده است و لازم است که داده ها را از ستون C بازگرداند، سپس به درستی نشان می دهد 2. از آنجا که بحث و جدل جدول($ B $ 2: $ C $ 4) شامل تنها دو ستون - در و C. اگر شما سعی می کنید تعدادی از ستون 3 را مشخص کنید (چه حساب در ورق است)، ما اشتباه می کنیم #ارتباط دادن!زیرا ستون سوم در محدوده مشخص شده به سادگی نیست.

نکته عملی: اگر جدول استدلال تعداد زیادی از ستون ها داشته باشد و شما باید نتیجه را از ستون آخر بازگردانید، لازم نیست آنها را با شمارش محاسبه کنید. این را می توان به عنوان این مشخص کرد: \u003d VD ($ a2؛ list1! $ $ 2: $ C $ 4؛ عددی (sheet1! $ $ 2: $ c $ 4)؛ 0). به هر حال در این مورد ورق 1! شما همچنین می توانید به عنوان غیر ضروری حذف کنید: \u003d VD ($ A2؛ list1! $ $ 2: $ C $ 4؛ NuterStolba ($ $ 2: $ 4 $ 4)؛ 0).

0 - Interval_prising - استدلال بسیار جالب ممکن است برابر با درست یا نادرست باشد. بلافاصله این سوال مطرح می شود: چرا در فرمول من 0 وجود دارد؟ همه چیز بسیار ساده است - اکسل در فرمول ها می تواند 0 را به عنوان دروغ و 1 به عنوان حقیقت درک کند. اگر در UPR برای مشخص کردن این پارامتر، 0 یا یک دروغ باشد، جستجو برای پیروی دقیق با معیار مشخص شده رخ می دهد. هیچ ارتباطی با نشانه های جایگزینی ندارد ("*" و "؟"). اگر از 1 یا حقیقت استفاده می کنید (یا آخرین بحث را مشخص نکنید، زیرا به طور پیش فرض این حقیقت است)، سپس ... یک داستان بسیار طولانی. به طور خلاصه - EPR به دنبال ارزش مشابهی مناسب برای معیار . گاهی اوقات بسیار مفید است. درست است، اگر از این پارامتر استفاده می کنید، لازم است که لیست در جدول استدلال مرتب شود صعودی. من توجه به این واقعیت را جلب می کنم که مرتب سازی لازم است تنها در صورتی که این استدلال، شما با حقیقت برابر است یا 1. اگر 0 یا یک دروغ باشد، مرتب سازی لازم نیست.

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

چگونه برای جلوگیری از اشتباهات # n / d (# n / a) در UPR؟
هنوز مشکل مکرر - بسیاری نمی خواهند نتیجه # n / e را ببینند، اگر تصادف یافت نشد. آسان است برای رسیدن به اطراف:
\u003d اگر (und (EGR ($ a2؛ list1! $ $ 2: $ c $ 4، 3؛ 0))؛ ""؛ بازو ($ a2؛ list1! $ $ 2: $ c $ 4 $ c $ 4؛ 3؛ 0))
در حال حاضر اگر یک داور یک تصادف را پیدا نکنید، سلول خالی خواهد بود.
و کاربران نسخه اکسل 2007 را می توان مورد استفاده قرار داد اگر شما می توانید استفاده کنید اگر:
\u003d اگر ابزار (ARM A2؛ list1! $ $ 2: $ c $ 4؛ 3؛ 0)؛ "")

هیئت مدیره وعده داده شده

این ویژگی به دنبال مقدار مشخص شده توسط پارامتر است skeeping_new در استدلال visible_massive . و نتیجه عملکرد، تعداد موقعیتی از مقدار یافت شده در مشاهده شده. این شماره موقعیت است، نه ارزش خود. در اصل، من آن را به همان شیوه نقاشی نمی کنم، زیرا نکات اصلی دقیقا یکسان است. اگر ما می خواستیم آن را برای جدول بالا اعمال کنیم، این شبیه به این خواهد بود:
\u003d شرکت جستجو ($ a2؛ list1! $ $ 2: $ $ 4؛ 0)
$ A2 مورد نظر مورد نظر است. در اینجا همه چیز دقیقا همانند با UPR است. نمادهای جایگزینی و دقیقا در همان عملکرد نیز مجاز است.

برگ 1! $ $ 2: $ $ 4 - ردیابی. تفاوت اصلی از UPR مجاز به مشخص کردن یک آرایه تنها با یک ستون است. باید این ستون باشد که در آن ما نگاه می کنیم skeeping_new . اگر سعی کنید بیش از یک ستون را مشخص کنید، تابع خطا را به شما می دهد.

type_spections (0) - همانند در UPR Interval_prising . با همان ویژگی ها. این فرصت را برای جستجوی کوچکترین از دلخواه یا بزرگترین مشخص شده است. اما در این مقاله من در این مقاله متوقف نخواهم شد.

اساسا با آن برخورد کرد اما ما باید تعداد موقعیت را بازگردانیم، اما ارزش خود را. بنابراین، تلاش جستجو برای ما مناسب نیست. حداقل یک، به خودی خود. اما اگر آن را با شاخص تابع استفاده می شود - پس این چیزی است که ما نیاز داریم و حتی بیشتر.
\u003d فهرست (Sheet1! $ $ 2: $ C $ 4؛ هیئت مدیره جستجو ($ a2؛ list1! $ $ 2: $ $ 4؛ 0)؛ 2)؛ 2)؛ 2)؛
چنین فرمول همانند ARD را باز می کند.

شاخص تابع استدلال
برگ 1! $ $ 2: $ C $ 4 - یک آرایه. به عنوان این استدلال، ما محدوده ای را که ما می خواهیم ارزش ها را دریافت کنیم، نشان می دهیم. این می تواند هر دو یک ستون و چند باشد. اگر ستون یکی باشد، آخرین استدلال نیازی به مشخص کردن عملکرد ندارد. به هر حال، این استدلال کاملا می تواند با کسی که ما اشاره می کنیم در استدلال مشاهده شده توسط_Massive تابع هیئت مدیره جستجو.

بعدی می آید number_stroke و number_stall. این به عنوان یک عدد است که ما جایگزین هیئت مدیره را می دهیم، که به شماره موقعیت ایالات متحده در آرایه باز می گردد. این بر روی آن ساخته شده است. این شاخص مقدار را از آرایه ای که در خط مشخص شده (شماره سرور) آرایه و ستون مشخص شده است، باز می گرداند (number_number) اگر ستون ها بیش از یک است. مهم این است که بدانید که در این بسته نرم افزاری تعداد ردیف ها در این استدلال آرایه ای از شاخص تابع و تعداد رشته ها در استدلال مشاهده شده توسط_massive توابع هیئت مدیره جستجو باید هماهنگ باشد. و با همان خط شروع کنید. این موارد در موارد عادی است، اگر اهداف مختلفی را دنبال نکنید.
همچنین، همانطور که در مورد HDP، شاخص در صورت عدم پیدا کردن ارزش مورد نظر بازگشت # H / D. و اطراف خطاهای مشابه شما همچنین می توانید:
برای تمام نسخه های اکسل (از جمله 2003 قبل از):
\u003d اگر (und ($ a2؛ list1! $ $ 2: $ $ 4؛ 0))؛ ""؛ شاخص (ورق 1 $ $ 2: $ C $ 4؛ شرکت جستجو ($ a2؛ list1! $ $ 2: $ $ 4؛ 0)؛ 2))
برای نسخه های 2007 و بالاتر:
\u003d اگر ابزار (شاخص (Sheet1! $ $ 2: $ C $ 4؛ هیئت مدیره جستجو ($ a2؛ list1! $ $ 2: $ $ 4؛ 0)؛ 2)؛

کار با معیارها بیش از 255 کاراکتر است
یک شاخص جستجو و یک مزیت دیگر قبل از PRD وجود دارد. واقعیت این است که EPR نمیتواند به معنی جستجو کند طول رشته ای که شامل بیش از 255 کاراکتر است. این اتفاق می افتد به ندرت، اما این اتفاق می افتد. شما می توانید، البته، داوری را فریب دهید و معیار را برش دهید:
\u003d PRP (PSR ($ A2؛ 1؛ 255)؛ PSR (Sheet1! $ $ 2: $ C $ 4؛ 1؛ 255)؛ 3؛ 0)
اما این یک فرمول آرایه است. و علاوه بر این، همیشه چنین فرمول نتیجه مطلوب را به دست خواهد آورد. اگر اولین 255 کاراکتر با 255 کاراکتر اول در جدول یکسان باشد، پس از آن نشانه ها متفاوت هستند - فرمول این را نمی بیند. بله، و فرمول ارزش های فوق العاده متنی، که در مواردی که اعداد باید بازگردانده شود، بسیار راحت نیست.

بنابراین، بهتر است از چنین فرمول حیله گری استفاده کنید:
\u003d index (sheet1 $ $ 2: $ c $ 4؛ supered (truth؛ list1! $ $ 2: $ 4 \u003d $ a2؛ 0)؛ 2)؛ 2))؛ 2)
در اینجا من از همان محدوده برای خوانایی استفاده کردم، اما در مثال برای دانلود، آنها از آنچه که در اینجا نشان داده شده متفاوت است.
فرمول خود را بر روی قابلیت های عملکرد Sumpera تبدیل به محاسبات عظیم برخی از توابع در آن ساخته شده است. در این مورد، مالکیت جستجو به دنبال یک موقعیت ردیف است که در آن معیار برابر با مقدار ردیف است. کاراکترهای wildcard در اینجا نمی توان اعمال کرد.

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

مثال دانلود

(26.0 KIB، 14 191 دریافت)

مقاله کمک کرد؟ یک لینک با دوستان به اشتراک بگذارید! درس های ویدئویی

("نوار پایین" :( "TextStyle": "Static"، "TextoToyStatic": "Textautohide": درست است، "textosionmarginstatic": 0، "textosiondynamic": "bottomleft"، "textosionmarginleft": 24، " textosionmarginright ": 24،" textposionmargintop ": 24،" textposionmarginbottom ": 24،" texteffect ":" اسلاید "،" texteffectinging ":" easeoutcubic "،" texteffectduration ": 600،" texteffectecturing ":" چپ "،" texteffectslideistance " : 30، "texteffectdelay": 500، "texteffectseparate": false، "texteffect1": "slide"، "texteffectslidirection1": "right"، "texteffectslidisce1": 120، "texteffectinging1": "easeoutcubic"، "texteffectduration1": 600 ، "texteffectdelay1": 1000، "texteffect2": "slide"، "texteffectslideive2": "راست"، "texteffectslideistance2": 120، "texteffectinging2": "easeoutcubic"، "texteffectdury2": 600، "texteffectdelay2": 1500، " TEXTCSS ":" صفحه نمایش: بلوک؛ PADDING: 12PX؛ TEXT-ALIGN: LEFT؛ "،" TEXTBGCSS ":" نمایش: بلوک؛ موقعیت: مطلق؛ بالا: 0px؛ چپ: 0px؛ عرض: 100٪؛ ارتفاع: 100٪ ؛ پس زمینه رنگ: # 333333؛ Opacity: 0.6؛ فیلتر: a LPHA (Opacity \u003d 60)؛ "،" Titlecss ":" نمایش: بلوک؛ موقعیت: نسبی؛ فونت: Bold 14px \\ "Lucida Sans Unicode \\"، \\ "Lucida Grande \\"، Sans-Serif، Arial؛ رنگ: #fff؛ "، descriptioncss": "نمایش: بلوک؛ موقعیت: نسبی؛ فونت: 12px \\ "Lucida Sans Unicode \\"، \\ "Lucida Grande \\"، Sans-Serif، Arial؛ رنگ: #fff؛ Margin-Top: 8px؛ "،" Buttoncss ":" نمایش: بلوک؛ موقعیت: نسبی؛ Margin-top: 8px؛ "،" texteffectresponsive ": درست است،" texteffectresponsize ": 640،" titlecsssresponsive ":" font-size: 12px؛ "،" descriptioncsssponsive ":" نمایش: هیچ! مهم؛ "،" buttoncsssresponsive ": ""، "addgooglefonts": false، "googlefonts": ""، "textleftrightpercentConforstatic": 40))

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

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

به عنوان مثال، لیستی از اتومبیل از ناوگان شرکت متوسط، همانطور که در زیر نشان داده شده است،

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

اگر ما به فورد از بخش بازاریابی علاقه مند هستیم چه باید بکنیم؟ علاوه بر این، ما می خواهیم تنها از عملکرد Poispost استفاده کنیم، بدون استفاده از فرمول ها با ترکیبی از شاخص های دیگر توابع، و غیره راه خروج از این وضعیت در تعیین تنظیمات استدلال و اجرای عملکرد در آرایه است. برای این:

1. در سلول B16، مقدار فورد را وارد کنید، و در سلول C16 نام گروه مورد علاقه بازاریابی است.
2. در سلول C17، ویژگی را با استدلال های زیر وارد کنید:
3. پس از ورود به تایید تابع، Ctrl + Shift + را فشار دهید + ترکیب کلید داغ را وارد کنید، زیرا باید در آرایه انجام شود. اگر همه چیز به درستی انجام شود در فرمول خط ظاهر خواهد شد پرانتز.

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

شرح مثال، چگونه عملکرد جستجو کار می کند:

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

همچنین خواندن: توابع شاخص و جستجوی اکسل و نمونه هایی از استفاده از آنها

مقادیر جستجو در محدوده جدول اکسل بر روی ستون ها و ردیف ها

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

جستجو برای موقعیت آیتم در لیست با هیئت مدیره جستجو (مسابقه)

بله، البته، شما می توانید از ابزار استفاده کنید: "صفحه اصلی" - "ویرایش" - "پیدا کردن" Ctrl + F برای فراخوانی جعبه جستجو بر روی ورق اکسل. یا فرمت مشروط را برای جدول ایجاد کنید. اما پس از آن، با نتایج به دست آمده، محاسبه نخواهد شد. بنابراین، لازم است که فرمول مربوطه را ایجاد و به درستی اعمال کنیم.

مقادیر جستجو در آرایه اکسل

طرح راه حل کار به این ترتیب به نظر می رسد:

• در سلول B1 ما داده های مورد علاقه خود را وارد خواهیم کرد؛
• در سلول B2، هدر ستون نمایش داده می شود، که حاوی مقدار سلول B1 است
• در سلول B3، نام خط نمایش داده خواهد شد، که حاوی مقدار سلول B1 است.

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

ما به طور مداوم گزینه های حل پیچیدگی های مختلف را در نظر می گیریم و در پایان مقاله - نتیجه نهایی.

مقادیر جستجو در ستون اکسل

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

1. در سلول B1، مقدار گرفته شده از جدول 5277 را وارد کنید و پس زمینه آن را در آبی رنگ برای خوانایی فیلد ورودی برجسته کنید (بعد به شماره های دیگر سلول B1 معرفی می شود تا با ارزش های جدید آزمایش شود).
2. در سلول C2، ما فرمول را برای به دست آوردن یک هدر ستون که حاوی این مقدار است، وارد می کنیم:
3. پس از ورود به فرمول برای تایید، ترکیبی از کلیدهای داغ Ctrl + Shift + ENTER را فشار دهید، زیرا فرمول باید در آرایه انجام شود. اگر همه چیز به درستی در خط فرمول ها در اطراف لبه ها انجام شود، پرانتز های فرفری ظاهر می شوند ().

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

مقادیر جستجو در رشته اکسل

حالا شماره ردیف را برای همان مقدار به دست می آوریم (5277). برای انجام این کار، فرمول زیر را در سلول C3 وارد کنید:

پس از وارد شدن به فرمول برای تأیید دوباره، CTRL + Shift + را فشار دهید + ترکیب کلید را وارد کنید و نتیجه را دریافت کنید:

فرمول شماره 9 را بازگرداند - هدر رشته ورق را با توجه به مقدار مربوطه جدول یافت. در نتیجه، ما داریم آدرس کامل ارزش D9.

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

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

• برای ستون ستون - مارس؛
• برای رشته - محصول 4.

برطرف كردن این وظیفه ما از فرمول با مقادیر موجود در سلول های C2 و C3 استفاده خواهیم کرد. برای انجام این کار، این کار را انجام دهید:

در نتیجه، مختصات داخلی جدول به دست آمده توسط پول - مارس؛ محصول 4:

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

جستجو برای مقادیر یکسان در محدوده اکسل

برای کنترل حضور تکراری در میان مقادیر جدول، یک فرمول ایجاد کنید که می تواند ما را در مورد دسترسی تکراری اطلاع دهد و مقدار آنها را شمارش کند. برای انجام این کار، فرمول را به سلول E2 معرفی می کنیم:

علاوه بر این، برای محدوده بخش جدولی، ما یک قانون قالب بندی شرطی ایجاد خواهیم کرد:

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

در این مورد، فرمول ها یا یک یا چند را تغییر می دهیم، اما نه دو بار در یک زمان. لازم به یادآوری است که فرمول قدیمی باید در سلول C3 باقی بماند:

در اینجا مختصات اولین عمودی تکراری به درستی نمایش داده می شود (از بالا به پایین) - i7 برای ورق و آگوست؛ محصول 2 برای جدول. بگذارید این گزینه را برای مثال بعدی بعدی بگذاریم.

جستجو برای نزدیکترین مقدار در محدوده اکسل

این جدول هنوز کامل نیست

پس از همه، هنگام تجزیه و تحلیل شما باید دقیقا تمام ارزش های خود را بدانید. اگر تعداد تولید شده در سلول B1 در جدول یافت نشد، سپس خطا باز می شود - # معنی! ایده آل این خواهد بود که فرمول در غیاب در جدول منبع منبع خود را انتخاب کنید نزدیکترین مقدار که حاوی جدول است. برای ایجاد چنین برنامه ای برای تجزیه و تحلیل جداول در سلول F1، یک فرمول جدید را وارد کنید:

پس از آن، در تمام فرمول های دیگر، تغییر لینک به جای B1 باید F1 باشد! شما همچنین باید مرجع را در قالب بندی شرطی تغییر دهید. انتخاب کنید: "خانه" - "سبک" - "قالب بندی شرطی" - "قوانین مدیریت" - "تغییر قانون". و در اینجا در پارامترها، F1 را به جای B1 مشخص کنید. برای بررسی عملیات برنامه، تعداد آن را وارد کنید که در جدول به سلول B1 نیست، به عنوان مثال: 8000. این نتیجه نهایی خواهد شد:

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

یک مثال از پیدا کردن یک مقدار در گروه اکسل را دانلود کنید

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

وظیفه 1. ایجاد یک لیست کشویی

1. فایل را باز کنید ex03_1.xlsx.

2. بر روی ورق قیمت یک جدول از کالاها با قیمت وجود دارد.

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

تصویر 1

در ورق فروش در ستون نام محصول یک لیست کشویی ایجاد کنید و آن را با داده ها پر کنید.

برای ایجاد یک لیست کشویی، سلول ها را انتخاب کنید (شما می توانید بلافاصله چند را انتخاب کنید) که در آن شما می خواهید لیست کشویی را انتخاب کنید و روی برگه انتخاب کنید داده ها دکمه چک کردن داده ها (شکل 2).

شکل 2.

در برگه اول مولفه های از لیست کشویی نوع داده گزینه را انتخاب کنید فهرست و وارد خط شوید منبع علامت برابر با نام محدوده با داده های مورد نیاز است (یعنی، \u003d محصول) (شکل 3). کلیک خوب.

نتیجه ایجاد یک لیست کشویی در شکل 4 نشان داده شده است.

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

شکل 3

شکل 4

آشنایی با توابع هیئت مدیره جستجو و شاخص

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

توابع نحو

ضمیمه (دلخواه، آرایه، type_densation)

آرایه- این یک بلوک متشکل از یک ستون یا یک رشته است.

type_specification- این شماره -1، 0 یا 1 است.

اگر یک type_specificationبرابر با 1، سپس تابع هیئت مدیرهبزرگترین ارزش را پیدا می کند که کمتر از آن است skeeping_newیا به همان اندازه به او. واقع شده آرایهباید صعودی سفارش شود

اگر یک type_specificationکلاغ سیاه 0, که عملکرد هیئت مدیره جستجواولین مقدار را که دقیقا برابر استدلال است، پیدا می کند اسکلهواقع شده آرایهممکن است اختلال باشد

اگر یک type_specificationبرابر با -1، سپس تابع هیئت مدیرهکوچکترین ارزش را پیدا می کند که بیشتر از آن است دلخواهیا به همان اندازه به او. visible_massiveباید به نزول دستور داد.

اگر یک type_specificationحذف شده است، پس فرض می شود که برابر است 1 .

تابع هیئت مدیرهموقعیت ارزش مورد نظر را در آرایه باز می گرداند، و نه ارزش خود را.

تابع فهرست مطالب او دارای دو فرم است. ما فقط یکی را در نظر خواهیم گرفت.

فهرست (جدول؛ number_name؛ number_number)

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

بیایید به کار این توابع در نمونه های خاص نگاه کنیم.

وظیفه 2. آشنایی با عملکرد جستجو

با استفاده از تابع جستجو ()، موقعیت کالا را با نام "Jogurt" در محدوده تعیین کنید تولید - محصول. برای این:

1. ب کتاب الکترونیکی ex03_1.xlsx به ورق بروید قیمت و مکان نما برگ را به هر سلول آزاد نصب کنید

2. فرمول را به این سلول وارد کنید \u003d هیئت مدیره جستجو ("ماست"؛ محصول؛ 0)، I.E. ما با استفاده از این ویژگی مشخص می کنیم که ما باید یک عنصر "ماست" را پیدا کنیم (مقدار متن در ویژگی ها همیشه در نقل قول ها نشان داده شده است) در محدوده تولید - محصول، نوع مقایسه 0 - تصادف دقیق؛

eNTER را فشار دهید در نتیجه، شما باید شماره 7 را دریافت کنید (شکل 5).

شکل 5

در واقع، اگر به محدوده نگاه کنید تولید - محصولموقعیت ماست مربوط به این رقم است. باید به یاد داشته باشید که موقعیت ارزش در آرایه، I.E. محدوده مشخص شده از ابتدای محدوده شمارش شده است. معلوم می شود که اگر یک رشته جدید را در ابتدای جدول اضافه کنیم، مقدار "ماست" در خط با شماره 8 تغییر می کند، اما محدوده تولید - محصول با ارزش "شیر" شروع می شود، بنابراین از ابتدای محدوده موقعیت ماست در هر مورد مربوط به شکل 7 خواهد بود (شکل 6).

شکل 6

توجه داشته باشید که به عنوان یک مقدار برای تابع جستجو () تابع، شما می توانید هر دو مقدار خود را مشخص کنید و نام سلول که در آن این مقدار است. به عنوان مثال، ضبط \u003d هیئت مدیره جستجو (A7؛ محصول؛ 0) اجازه می دهد یک نتیجه مشابه را به عنوان هنگام استفاده از ضبط \u003d هیئت مدیره جستجو ("ماست"؛ محصول؛ 0).

وظیفه 3. مطابقت تابع شاخص

به یاد بیاورید که تابع index () یک عنصر مشخص شده توسط عدد ردیف و ستون را از بلوک مستطیلی (جدول) مشخص می کند و این اعداد از عنصر بلوک سمت چپ شمارش می شوند.

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

1. در کتاب الکترونیکی ex03_1.xlsx بر روی ورق قیمت مکان نما برگ را به هر سلول آزاد نصب کنید

2. تابع زیر را به این سلول وارد کنید \u003d شاخص (محصول؛ 5). ENTER را فشار دهید نتیجه در شکل 7 نشان داده شده است.

شکل 7

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

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

در سلول جدید، فرمول \u003d شاخص را وارد کنید (جدول، 8؛ 2). در نتیجه، شما هزینه ژامبون را پیدا خواهید کرد (شکل 8).

شکل 8.

تعویض داده های خودکار

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

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

وظیفه 4. ترکیب توابع هیئت مدیره جستجو و شاخص

در کتاب الکترونیکی ex03_1.xlsx بر روی ورق حراجی در یک چهارم قیمتبا استفاده از توابع MS اکسل، جایگزینی خودکار هزینه کالا از ورق را فراهم می کند قیمت، بسته به مقدار بر روی ورق انتخاب شده از لیست کشویی حراجی.

1. مکان نما برگ را به سلول C2 نصب کنید و فرمول زیر را وارد کنید:

2. ENTER را فشار دهید. در نتیجه، شما در سلول C2 دریافت می کنید هزینه آن محصول، نام آن در ستون مجاور نشان داده شده است (شکل 9). هنگام انتخاب از لیست کشویی یکی دیگر از نام محصول، مقدار قیمت به ترتیب تغییر خواهد کرد.

شکل 9

3. با استفاده از مارکر اتوکسیفوسی، این فرمول را به انتهای جدول تقسیم کنید (شکل 10).

شکل 10

این فرمول را تعیین کنید این فرمول شروع به کار با عملکرد هیئت مدیره جستجو می کند، که به ما اجازه می دهد مقدار ارزش را از سلول B2 بر روی ورق پیدا کنیم حراجی در محدوده تولید - محصول با ورق قیمت، و نوع دقت 0. به عنوان مثال، اگر از لیست کالاها استفاده می کنید، که در شکل 10 ارائه شده است، عملکرد هیئت مدیره جستجو به دنبال یک موقعیت لاف است، نام آن در سلول B2 نشان داده شده است از ستون نام محصول در محدوده تولید - محصول بر روی ورق قیمت. این موقعیت برابر خواهد بود 3. در نتیجه، فرمول معرفی شده توسط ما در حافظه کامپیوتر

\u003d فهرست (جدول؛ هیئت جستجو (B2؛ محصول؛ 0)؛ 2)؛ 2)

نگاه کردن

\u003d فهرست (جدول؛ 3؛ 2)

پارامتر "3" نتیجه عملکرد کار هیئت مدیره جستجو است.

بعد، تابع وارد تابع می شود، که در محدوده جستجو می شود جدول بر روی ورق قیمت مقدار در تقاطع ردیف سوم و ستون دوم. (ما به شما یادآوری می کنیم که محدوده جدول متشکل از دو ستون است). این مقدار هزینه کالاها خواهد بود، در این مورد نان \u003d 27.

وظیفه 5. ثبت نام جدول نهایی

بر روی ورق حراجی در یک چهارم عدد مقادیر دلخواه را وارد کنید

مقادیر ستون را محاسبه کنید جمع.

وظیفه 6. وظیفه مستقل

پرونده باز frames.xls. . لازم است تغییر تغییر در حقوق و دستمزد بر روی ورق. فریم .

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

یک ورق جدید ایجاد کنید و آن را بنویسید. دولت .

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

کارکنان تکراری را حذف کنید.

برای این:

1. ستون های پست و حقوق را برجسته کنید.

2. در برگه داده ها روی دکمه کلیک کنید حذف تکراری .

شاخص توابع و قرار گرفتن در معرض در اکسل - بهترین جایگزین برای VDP

تیک ستون را بردارید حقوق.

4. بر روی دکمه کلیک کنید خوب..

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

دستورالعمل های متداول

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

2. برجسته بر روی ورق دولتمحدوده حقوق و دستمزد نام دامنه انتخاب شده را نام ببرید حقوق.

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

4. پس از ورود به فرمول، اطمینان حاصل کنید که حقوق و دستمزد به دست آمده واقعا مربوط به حقوق کارمند کارمند از ورق است دولت.

5. حقوق و دستمزد را از CH تغییر دهید. حسابدار در ورق دولت. بررسی کنید که تغییر در حقوق مربوطه بر روی ورق اتفاق افتاد کارکنان.

جستجو در سایت:

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

تابع VRP (Vertical View) برای یک جدول با داده ها جستجو می شود و بر اساس معیارهای پرس و جو جستجو، مقدار مربوطه را از یک ستون خاص باز می گرداند. اغلب، لازم است از چندین شرایط در یک بار در پرس و جو جستجو استفاده کنید. اما به طور پیش فرض، این عملکرد نمی تواند بیش از یک شرط را پردازش کند. بنابراین، لازم است از یک فرمول بسیار ساده استفاده کنید که به شما این امکان را می دهد که قابلیت های عملکرد FPR را در چندین ستون در همان زمان گسترش دهید.

کار عملکرد اسلحه برای چندین معیار

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

این گزارش نیاز به پیدا کردن نرخ درآمد برای یک نماینده فروش خاص در یک تاریخ خاص دارد. با توجه به شرایط جستجو، درخواست ما باید شامل 2 شرایط باشد:

1. - تاریخ تحویل درآمد به صندوقدار.
2. - نام خانوادگی نماینده فروش.

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

1. در سلول C1، مقدار اول را برای اولین معیار پرس و جو جستجو وارد کنید. به عنوان مثال، تاریخ: 03/22/2017.
2. در سلول C2، نام نماینده فروش را وارد کنید (به عنوان مثال Novikov). این مقدار به عنوان استدلال پرس و جو جستجو دوم استفاده می شود.
3. در سلول C3، ما نتیجه جستجو را دریافت خواهیم کرد، زیرا باید آن را معرفی کرد:
4. پس از ورود به فرمول برای تأیید، ترکیبی از کلیدهای داغ Ctrl + Shift + ENTER را فشار دهید، زیرا فرمول باید در آرایه انجام شود.

نتیجه جستجو در جدول برای دو شرایط:

مقدار درآمد یک نماینده فروش خاص را در یک تاریخ خاص پیدا کرد.

تجزیه و تحلیل اصل فرمول برای عملکرد FMP با شرایط متعدد:

اولین استدلال تابع \u003d VD () اولین شرط برای پیدا کردن ارزش در گزارش درآمد حاصل از نمایندگان فروش است. در بحث دوم یک جدول مجازی ایجاد شده به عنوان یک نتیجه از یک محاسبه عظیم از عملکرد منطقی \u003d اگر () وجود دارد. هر نام خانوادگی در محدوده سلول های B6: B12 با مقدار در سلول C2 مقایسه می شود. بنابراین، یک آرایه داده های متعارف از داده ها با عناصر مقادیر حقیقت و دروغ در حافظه ایجاد می شود.

سپس به دلیل فرمول، در حافظه برنامه، هر عنصر واقعی با یک مجموعه داده 3 عنصر جایگزین می شود:

1. عنصر - تاریخ
2. عنصر - نام خانوادگی.
3. عنصر - درآمد.

و هر عنصر کاذب در حافظه با یک مجموعه 3 عنصر از مقادیر متن خالی ("") جایگزین می شود.

در نتیجه یک جدول جدید در حافظه برنامه ایجاد می شود، که عملکرد PRD در حال حاضر کار خواهد کرد. این همه مجموعه های خالی عناصر داده را نادیده می گیرد.

جستجوی تابع برای مایکروسافت اکسل

و عناصر غیر خالی با مقدار سلول C1 مورد استفاده قرار می گیرند که به عنوان اولین معیار پرس و جو جستجو (تاریخ) مورد استفاده قرار می گیرد. در یک کلمه، جدول در حافظه توسط عملکرد FPR با یک وضعیت جستجو بررسی می شود. با یک نتیجه مثبت مقایسه، تابع مقدار عنصر را از ستون سوم (درآمد) جدول معمولی باز می گرداند. این به این دلیل است که استدلال سوم نشان می دهد ستون شماره 3 که مقادیر گرفته شده است. شایان ذکر است که برای مشاهده در استدلال های عملکرد، یک جدول کامل (در بحث دوم) مشخص شده است، اما جستجوی خود همیشه در ستون اول در جدول مشخص شده است.

یک مثال از عملکرد تابع را با شرایط متعدد در اکسل دانلود کنید

و از کدام ستون مقدار بازگشتی را در نظر گرفته شده در بحث سوم نشان داده شده است.

شماره 0 در آخرین استدلال تابع نشان می دهد که تصادف باید کاملا دقیق باشد.

آرایه های دستکاری در MS Excel

هدف از کار: مهارت های خرید برای کار با آرایه ها؛ برای مطالعه ویژگی های آرایه فرمول ورودی.

دستورالعمل های متداول

این آرایه یک محدوده مستطیلی مجاور فرمول هایی است که MS اکسل به طور کلی فراینده می شود.

نتیجه محاسبات می تواند هر دو آرایه و یک عدد باشد. استفاده از فرمول آرایه امکان دستیابی به راه حل های جمع و جور را به وظایف کافی پیچیده می کند و در برخی موارد بدون آنها غیر ممکن است. جایگزینی فرمول های مکرر فرمول های آرایه اجازه می دهد تا شما را به ذخیره حافظه، از آنجا که MS Excel مجموعه ای از فرمول ها را به عنوان یک فرمول واحد نگه می دارد.

مثال 1 لازم است هزینه هر نوع محصول را محاسبه کنید (شکل 5.1).

شکل. 5.1. داده های منبع به عنوان مثال 1

تصمیم گیریبرای محاسبات با استفاده از فرمول های آرایه:

1. دامنه ای را که به یک فرمول آرایه وارد می شود، اختصاص دهید.

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

3. ورودی فرمول را با فشار دادن همزمان کامل کنید Shift-Ctrl-Enter. در این مورد، براکت های کاری به طور خودکار ظاهر می شوند، فرمول را فریم می کنند. فرمول Massif در تمام سلول های محدوده اختصاص داده شده ثبت می شود.

پس از یک آرایه حاوی فرمول ایجاد می شود، شما نمی توانید سلول ها را در محدوده آرایه وارد کنید، بخشی از محدوده را حذف کنید یا یک سلول جداگانه را در داخل محدوده ویرایش کنید. یک آرایه تنها می تواند به عنوان یک کل تغییر کند. بنابراین، برای مثال، برای تغییر فرمول حل، طیف وسیعی از آرایه را انتخاب کنید، فرمول را ویرایش کنید و تغییرات را با فشار دادن کامل کنید Shift-Ctrl-Enter.

برای اصلاح فرمول آرایه به سمت کاهش یا افزایش اندازه واحد، شما همچنین می توانید الگوریتم زیر را ارائه دهید.

1. محدوده را با فرمول Massif اختصاص دهید و نماد Apostrophe را اضافه کنید " ’ " فرمول به متن تبدیل خواهد شد.

2. این متن را به تمام سلول های محدوده اختصاصی وارد کنید ( Ctrl-Enter).

3. یک محدوده جدید برای فرمول آرایه را انتخاب کنید، آدرس های لازم را تنظیم کنید، نماد آپوستروف را حذف کنید و تغییرات را با فشار دادن تکمیل کنید Shift-Ctrl-Enter.

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

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

در MS Excel، تعدادی از توابع وجود دارد که نتیجه را به شکل مجموعه ای از مقادیر بازگرداند. به طور طبیعی، فرمول های حاوی ویژگی ها (به عنوان مثال، و غیره) باید به عنوان فرمول های آرایه ها و ایجاد طیف وسیعی از سلول های اندازه مورد نظر انجام شود.

در MS اکسل، مجاز به استفاده از آرایه های ثابت است. بنابراین، برای مثال، ضبط یک خط بردار را نشان می دهد؛ - ستون بردار، - ماتریس.

عملکرد هیئت مدیره جستجو

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

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

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

هزینه یک واحد مواد خام هر نوع توسط یک ماتریس تنظیم شده است. هزینه های کل یک شرکت در تولید 100 واحد تولید نوع اول و 150 واحد از نوع دوم چیست؟

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

شکل. 5.2. نمونه پیدا کردن هزینه مواد اولیه برای تولید

واحد محصولات هر نوع

حجم تولید به ما اجازه می دهد ستون ماتریس را تنظیم کنیم، سپس کل هزینه تولید محصولات برابر با محصول ماتریس رشته است از جانب در یک ماتریس ستون Q.(شکل 5.3):

شکل. 5.3. محاسبه نمونه از کل هزینه شرکت

مثال 4یک سیستم معادلات خطی را حل کنید، جایی که ،.

تصمیم گیریراه حل این سیستم بردار است . برای پیدا کردن بردار، عناصر ماتریس را در محدوده سلول ها و عناصر بردار در محدوده سلول ها معرفی می کنیم.

سپس شما باید محدوده ای را انتخاب کنید که در آن عناصر بردار محلول قرار گرفته اند. در این محدوده، فرمول را معرفی می کنیم Shift-Ctrl-Enter. به عنوان یک نتیجه از اقدامات اقدامات در محدوده سلول ها، سیستم معادلات ظاهر می شود (شکل 5.4).

شکل. 5.4. نمونه نمونه از مثال 4

مثال 5

شکل درجه دوم را محاسبه کنید، در حالی که.

تصمیم گیریبرای پیدا کردن ارزش این فرم درجه دوم، عناصر ماتریس را در محدوده سلول ها و عناصر بردار در محدوده سلول ها معرفی می کنیم. سپس شما باید یک سلول را انتخاب کنید که در آن مقدار مقدار درجه دوم را قرار دهید. در این سلول ما فرمول را معرفی می کنیم \u003d Mumznom (MOBUM (A2: B3؛ A2: B3))؛ D2: D3) و با فشار دادن ترکیب کلید، ورودی فرمول را تکمیل کرد Shift-Ctrl-Enter. به عنوان یک نتیجه از اقدامات اقدامات در محدوده سلول ها، سیستم معادلات ظاهر می شود (شکل 5.5).

شکل. 5.5. نمونه نمونه ای از مثال 5

⇐ قبلی 45678910111213 بعدی

اطلاعات مشابه:

جستجو در سایت:

پیوندهای اکسل و عملکرد آرایه ها

این مقاله برخی از توابع کار با مراجع و آرایه ها را مورد بحث قرار می دهد:

1. خط؛
2. ستون؛
3. نشانی؛
4. کوتوله؛
5. تاج؛
6. عددی؛
7. شیفت؛
8. مهمانی - جشن.

تابع PRP

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

Syntax: \u003d. pr(کلید؛ محدوده؛ number_stall؛ [interval_view])، کجا

• کلید یک استدلال اجباری است. مقدار مورد نظر که می خواهید ارزش را بازگردانید.
• محدوده یک استدلال اجباری است. یک جدول که در آن لازم است برای پیدا کردن یک مقدار برای کلید. ستون اول جدول (محدوده) باید حاوی یک مقدار باشد که با کلید همخوانی دارد، در غیر این صورت خطا # n / d بازگردانده می شود.
• number_libes - استدلال اجباری تعداد توالی ستون در محدوده مشخص شده از آن ضروری است که ارزش را در مورد یک مسابقه کلیدی بازگردانید.
• interval_Prising - استدلال اختیاری. ارزش منطقی نشان دهنده نوع نمایش:
  • FALSE - تابع به دنبال یک تصادف دقیق در ستون اول جدول است. در صورت امکان ممکن است چندین همزمان، سپس اولین بار بازگشت خواهد شد. اگر تصادف یافت نشد، تابع خطای # n / d را باز می کند.
  • حقیقت - این تابع به دنبال یک تصادف تقریبی است. مقدار پیش فرض است. مطابقت تقریبی به این معنی است که هیچ تصادفی یافت نشد، تابع مقدار کلید قبلی را باز می گرداند. در همان زمان، یکی از موارد قبلی، کلیدی است که پیش از این مورد نظر با توجه به مرتب سازی کمتر به بیشتر یا از A تا Ya می شود. بنابراین، قبل از اعمال یک تابع با مشاهده این فاصله، پیش از مرتب کردن ستون اول جدول صعودی، زیرا اگر انجام نشده باشد، تابع می تواند نتیجه اشتباه را بازگرداند. هنگامی که چندین حادثه یافت می شود، آخرین آنها باز می گردند.

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

مثال استفاده:

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

برای قیمت، شما باید از عملکرد FPR با تصادف دقیق (فاصله فاصله) استفاده کنید، زیرا این پارامتر برای همه کالاها تعریف شده است و برای استفاده از قیمت محصول دیگر، اگر هنوز توسط شانس تعریف نشده است، ارائه نمی شود.

برای تعیین محل کالا، HDP با یک تصادف تقریبی استفاده می شود (دیدگاه فاصله از حقیقت)، از آنجا که توزیع کالا توسط دسته بندی انجام می شود. با توجه به این واقعیت که نام کالاها شامل نام دسته به علاوه متن اضافی، با توجه به شرایط مرتب سازی از A به I، نام کالاها بلافاصله پس از نام دسته، پس از آن، جدول کلید کلید را به کلید تبدیل نمی کند اولین مقدار از بالا را تشدید می کند.

در اصل، این رویکرد ترتیب می دهد، اگر برای کالاها که برای آن هیچ دسته ای وجود ندارد، محل آن سخت نیست. توجه به محصول "پیاز منطقه مسکو". برای او، محل "Stelleg No. 2" تعریف شده است، اگر چه "پیاز" در جدول اول وجود ندارد. این اتفاق می افتد همه چیز به دلایل مشابه زمانی که برابر با عملکرد تشخیص داده می شود، پس از آن مقدار ارزش یک کلید کوچکتر را به مرتب سازی کلید تعیین می کند، و برای "Loke of Region Moscow" طبقه "سیب زمینی" است ".

این را می توان با تعیین رده از نام محصول با استفاده از توابع متنی LevSMV (C11؛ جستجو ("؛ C11) -1) حذف کرد، که به تمام کاراکترها به فضای اول بازگردانده می شود و همچنین فاصله را تغییر خواهد داد مشاهده به دقیق ترین.

علاوه بر این، عملکرد PRD اجازه می دهد تا برای مقادیر متن از کلمات کلیدی - * (ستاره - هر تعداد از هر شخصیت) و؟ (یک شخصیت). به عنوان مثال، برای ارزش مورد نظر "*" و "ایوان" و "*" می تواند توسط ایوان، ایوانوف، خطوط مبل، و غیره نزدیک شود.

همچنین، این تابع می تواند مقادیر را در آرایه ها جستجو کند - \u003d VD (1؛ (2؛ "دو": 1؛ "یک")؛ 2؛ نادرست) - نتیجه اجرای خط "دو".

عملکرد GPR

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

Syntax: \u003d GPR (کلید؛ محدوده؛ number_name؛ [interval_prising]).

از آنجا که عملکرد مشابه عملکرد PRD است، به جز اینکه از جداول افقی به جای عمودی استفاده می کند، سپس توصیف ها و نمونه های استفاده نیز برای GPR مناسب است، با توجه به تفاوت های ذکر شده.

رشته تابع

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

مثال استفاده:

=خط(D4) - نتیجه 4.
=خط() - تابع تعداد رشته ای را که در آن قرار دارد، بازگرداند.

تابع ستون

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

مثال استفاده:

=ستون(C4) - فرمول ارزش 3 را بازگرداند.
=ستون() - تابع شماره ستون را که در آن قرار دارد، باز می گرداند.

آدرس تابع

متن را نشان می دهد که آدرس سلول مشخص شده توسط شماره خط و ستون مشخص شده است.

Syntax: \u003d. نشانی(String؛ ستون؛ [نوع نوع]؛ [style_links]؛ [list_name])، کجا:

• رشته یک استدلال اجباری است. شماره نشان دهنده شماره خط است که لازم است آدرس را بازگرداند؛
• ستون - استدلال اجباری. شماره نشان دهنده شماره ستون سلولی هدف است.
• نوع مورد نیاز - استدلال اختیاری. شماره از 1 تا 4، نشان دهنده شاخص های مرجع:
  • 1 - مقدار پیش فرض زمانی که تمام شاخص ها ثابت می شوند؛
  • 2 - رفع شاخص خط؛
  • 3 - ثابت کردن شاخص ستون؛
  • 4 - آدرس بدون اصلاح
• سبک لینک - استدلال اختیاری. ارزش منطقی:
  • حقیقت، فرمت پیوند "A1" است؛
  • FALSE فرمت لینک "R1C1" است.
• link_name یک استدلال اختیاری است. یک رشته نشان دهنده نام ورق برای افزودن به متن آدرس سلول است.

مثال استفاده:

=نشانی(1؛ 1) - $ 1 $ 1 را باز می گرداند.
=نشانی(1؛ 1؛ 4) - A1 را باز می گرداند.
=نشانی(1؛ 1؛ 4؛ false) - نتیجه RC.
=نشانی(1؛ 1؛ 4؛ false؛ list1 ") - نتیجه عملکرد لیست 1! RC.

عملکرد DVSSL

• address_links - استدلال اجباری یک رشته نشان دهنده آدرس پیوند به سلول یا محدوده است. به عنوان مثال، "C3"، "R3C3" یا "D8: D9".
• سبک لینک - استدلال اختیاری. ارزش منطقی تعریف سبک لینک:
  • حقیقت - سبک A1. مقدار پیش فرض است؛
  • دروغ - سبک R1C1.

مثال استفاده:

=DVSSL("A3") - مرجع به سلول A3 را باز می گرداند.
=DVSSL("R3C3") - خطای # لینک را بازگرداند، از آنجا که متن برای مراجع در فرمت R1C1، و استدلال دوم پیش فرض است.
=DVSSL("R3C3"؛ FALSE) - مرجع به سلول C3 را باز می گرداند.
=DVSSL(آدرس (String (C3)؛ ستون (C3))) - تابع نتیجه مشابه نمونه قبلی را بازگرداند.
پیوست عملکرد DVSSL با اشاره به محدوده:

تابع cems

تعداد ردیف ها را در محدوده یا آرایه مشخص می کند.

مثال استفاده:
=cems(D1: D8) - تابع نتیجه 8 را باز می کند.
=cems(1: 2: 3: 4: 5)) - تابع آن را تعیین می کند که در یک آرایه از 5 خط.

تابع nizhnyolb

تعداد ستون ها را در محدوده مشخص شده یا آرایه باز می گرداند.
Syntax: \u003d. عددی(لینک)، جایی که "مرجع" یک استدلال اجباری است که یک پیوند به سلول، محدوده یا آرایه است.
مثال استفاده:
=عددی(A5: D5) - نتیجه عملکرد 4.
=عددی((1؛ 2؛ 3؛ 4؛ 5)) - تابع آن را تعیین می کند که در یک آرایه از 5 ستون.

جابجایی تابع

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

اگر، به عنوان یک نتیجه از جبران، محدوده شروع به رفتن به محدودیت های برگ، تابع بازگشت خطا # لینک!

مثال استفاده:

به عنوان مثال، ما از پیوست پیوست به Sums تابع استفاده خواهیم کرد.
در ابتدا، به محدوده 10 ردیف و 1 ستون مراجعه کنید، جایی که تمام سلول ها دارای مقدار 2. به این ترتیب نتیجه فرمول - 20 را به دست می آورند.

حالا محدوده را به یک ستون سمت چپ ذخیره کنید، به عنوان مثال در -1

عملکرد هیئت مدیره جستجو

موقعیت موردی مشخص شده توسط مقدار را در محدوده یا آرایه نشان می دهد.

Syntax: \u003d. هیئت مدیره(skeeping_name؛ آرایه؛ [type_densation])، جایی که:

• نظم دلخواه یک استدلال اجباری است. مقدار عنصر در آرایه یافت می شود.
• آرایه یک استدلال اجباری است. محدوده یک بعدی یا آرایه برای جستجو یک مورد.
• type_name یک استدلال اختیاری است. شماره 1، 0 یا -1، که روش جستجو را تعریف می کند:
  • 1 - مقدار پیش فرض اگر همزمان ها یافت نشوند، موقعیت نزدیکترین مقدار کوچکتر به عنصر مورد نظر بازگردانده می شود. یک آرایه یا محدوده باید از کوچکتر تا بیشتر یا از A تا Ya مرتب شود.
  • 0 - تابع به دنبال یک تصادف دقیق است. اگر یافت نشد، خطا # n / d بازگشته است.
  • -1 - اگر همزمان ها یافت نشود، موقعیت نزدیکترین مقدار بیشتر به عنصر مورد نظر بازگردانده می شود. یک آرایه یا محدوده باید توسط نزولی مرتب شود.

مثال استفاده:
=هیئت مدیره("g"؛ ("a"؛ "b"؛ "در"؛ "g"؛ "d")) - تابع نتیجه نتیجه 4 را باز می کند.

تابع جستجوی اکسل

در عین حال، ثبت نام در نظر گرفته نشده است.
=هیئت مدیره("E"؛ ("a"؛ "b"؛ "در"؛ "g"؛ "d")؛ 1) - نتیجه 5، زیرا عنصر یافت نشد، بنابراین نزدیکترین عنصر کوچکتر بازگردانده می شود. عناصر آرایه صعودی ثبت می شوند.
=هیئت مدیره("E"؛ ("a"؛ "b"؛ "در"؛ "g"؛ "d")؛ 0) - خطا بازگشتی است، زیرا عنصر یافت نشد، و نوع مقایسه بر روی تصادف دقیق نشان داده شده است.
=هیئت مدیره("b"؛ ("d"؛ "g"؛ "b"؛ "b"؛ "a")؛ -1) - نتیجه 3.
=هیئت مدیره("D"؛ ("A"؛ "B"؛ "B"؛ "G"؛ "D")؛ -1) - عنصر یافت نشد، اگر چه در آرایه موجود نیست. این تابع یک نتیجه نادرست را باز می کند، زیرا آخرین استدلال مقدار -1 را می گیرد، و عناصر به عنوان نزولی مرتب نشده اند.

برای مقادیر متن، عملکرد اجازه می دهد تا استفاده از شخصیت های wildcard "*" و "؟".

بعد از ظهر، Hobrovers عزیز!

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

جستجو به من فقط یک مقاله در مورد هاب را بر اساس موضوعات مشابه - "Talmud توسط فرمول در صفحه گسترده Google" داد. این توصیف خوبی از چیزهای اساسی برای کار در اکسل می دهد (اگر چه 100٪ در مورد خود اکسل نیست).

بنابراین، جمع آوری یک استخر خاص از درخواست ها / وظایف، ایده آنها را به نوع و پیشنهاد راه حل های ممکن (اجازه ندهید همه ممکن نباشد، اما به سرعت نتیجه را می دهد).

این امر در مورد حل وظایف رایج ترین کاربران است.

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

Case_1: توابع منطقی و توابع تصادف
"من مجموعه ای از مقادیر در صفحه را دارم و لازم است که هنگام انجام یک وضعیت خاص / مجموعه ای از شرایط، یک مقدار معینی نمایش داده شد" (C) کاربر

داده ها معمولا در فرم جدولی ارائه می شود:

وضعیت:

• اگر مقدار در ستون "کمیت" بیشتر از 5 باشد،
• سپس شما نیاز به عقب نشینی در ستون "نتیجه" ارزش "سفارش لازم نیست"،

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

نحو فرمول به شرح زیر است:
اگر (log_production، [value_initina]، [value_if_nu]))

• log_eximacy - بیان که منجر به ارزش حقیقت یا نادرست می شود.
• value_initina - مقدار نمایش داده می شود اگر بیان منطقی درست باشد
• value_fire مقدار است که نمایش داده می شود اگر بیان منطقی نادرست باشد

نحو فرمول برای حل:

\u003d اگر (C5\u003e 5؛ "سفارش لازم نیست"؛ "نیاز به سفارش")

در خروجی ما نتیجه را دریافت می کنیم:

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

• اگر مقدار در ستون "کمیت" بیشتر از 5 باشد، و مقدار در ستون نوع برابر با "A" است

در این مورد، ما دیگر نمی توانیم خودمان را به استفاده از یک فرمول "اگر" محدود کنیم، لازم است فرمول دیگری را به نحو آن اضافه کنیم. و این یک فرمول منطقی دیگر "و" (و) خواهد بود.
نحو فرمول به شرح زیر است:
و (loginal_dation1، [logual_dation2]، ...)

• loginal_dation1-2، و غیره - شرایط معتبر، محاسبه که ارزش حقیقت یا نادرست را می دهد

نتیجه را در سلول D2 نمایش می دهد:

\u003d اگر (و (c2\u003e 5؛ b2 \u003d "a")؛ 1؛ 0)

بنابراین، با استفاده از ترکیبی از 2 فرمول، ما راه حل کار ما را پیدا می کنیم و نتیجه را دریافت می کنیم:

بیایید سعی کنیم این کار را پیچیده کنیم - یک شرایط جدید:

• اگر مقدار در ستون "کمیت" 10 است، و مقدار در ستون نوع برابر با "A"
• یا مقدار در ستون "شماره" بزرگتر یا برابر 5 است و ارزش "نوع" "B" است
• شما باید در ستون "نتیجه" مقدار "1"، در مورد معکوس "0" عقب نشینی کنید.

راه حل نحو به شرح زیر خواهد بود:
نتیجه را در سلول D2 نمایش می دهد:

\u003d اگر (یا (و (C2 \u003d 10؛ b2 \u003d "a")؛ و (c2\u003e \u003d 5؛ b2 \u003d "b")؛ 1؛ 0)

همانطور که می توان از ضبط، در فرمول "اگر" یک شرط "یا" (یا) و دو شرایط با استفاده از فرمول "و" موجود در آن شامل می شود، دیده می شود. اگر حداقل یکی از شرایط سطح دوم "حقیقت" باشد، نتیجه "1" در ستون "نتیجه" نمایش داده می شود، در غیر این صورت "0" خواهد بود.
نتیجه:

ما اکنون به وضعیت بعدی می رویم:
تصور کنید که، بسته به مقدار در ستون "وضعیت"، یک وضعیت خاص در ستون "نتیجه" باید نمایش داده شود، مقادیر و نتایج مطابق است.
وضعیت:

• 1 \u003d A.
• 2 \u003d ب
• 3 \u003d ب
• 4 \u003d G.

هنگام حل یک کار با استفاده از عملکرد "اگر"، نحو به شرح زیر است:

\u003d اگر (a2 \u003d 1؛ "a"؛ اگر (a2 \u003d 2؛ "b"؛ اگر (a2 \u003d 3؛ "b"؛ اگر (a2 \u003d 4؛ "g"؛ 0)))))

نتیجه:

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

راه حل جایگزین_1:
با استفاده از فرمول "انتخاب" (انتخاب)،
ویژگی های نحو:
انتخاب (number_intex، value1، [value2]، ...)

• شماره شاخص تعداد ارزش استدلال انتخاب شده است. شماره شاخص باید یک عدد از 1 تا 254، یک فرمول یا مرجع به یک سلول حاوی یک عدد در محدوده از 1 تا 254 باشد.
• value1، value2، ... - ارزش از 1 تا 254 استدلال ارزش ها، که عملکرد "انتخاب" را با استفاده از شماره شاخص، مقدار یا عمل انجام شده را انتخاب می کند. استدلال می تواند اعداد اشاره به سلول ها، نام های خاص، فرمول ها، توابع یا متن باشد.

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

• 1 \u003d A.
• 2 \u003d ب
• 3 \u003d ب
• 4 \u003d G.

نحو فرمول:

\u003d انتخاب (A2؛ "A"؛ "B"؛ "B"؛ "G")

نتیجه مشابه راه حل با زنجیره توابع "اگر" بالا باشد.
هنگام استفاده از این فرمول، محدودیت های زیر وجود دارد:
فقط اعداد را می توان در سلول A2 (شماره شاخص) نشان داد، و مقادیر نتیجه به ترتیب صعودی از 1 تا 254 ارزش خروجی خواهد بود.
به عبارت دیگر، تابع تنها کار خواهد کرد اگر در شماره های "A2" سلول از 1 تا 254 به ترتیب صعودی نشان داده شود و محدودیت های خاصی را هنگام استفاده از این فرمول اعمال می کند.
کسانی که. اگر ما می خواهیم ارزش "G" را در هنگام مشخص کردن شماره 5 نمایش داده شود،

• 1 \u003d A.
• 2 \u003d ب
• 3 \u003d ب
• 5 \u003d G.

این فرمول نحوی زیر را دارد:
نتیجه را در سلول B2 نمایش می دهد:

\u003d انتخاب (A31؛ "A"؛ "B"؛ "B" ؛؛ "G")

همانطور که دیده می شود، ارزش "4" در فرمول ما باید خالی را ترک کنیم و نتیجه "G" را به شماره دنباله "5" انتقال دهیم.

راه حل جایگزین_2:
بنابراین ما به یکی از محبوب ترین توابع اکسل نزدیک شدیم، که تسلط آن به طور خودکار هر کارگر اداری را در اکسل "اکسل تجربه" / sarcasm / تبدیل می کند.
نحو فرمول:
PRD (Desirable_dation، Table، Number_Libes، [Interval_View])

• یکی مورد دلخواه ارزش است، جستجو که برای آن توسط تابع انجام می شود.
• جدول - محدوده سلول های حاوی داده ها. این در این سلول ها است که جستجو رخ می دهد. ارزش ها می توانند متن، عددی یا منطقی باشند.
• شماره آهنگ یک عدد ستون در استدلال "جدول" است که از آن مقدار در صورت تصادف نمایش داده می شود. مهم است بدانیم که شمارش معکوس ستون ها بر روی شبکه کلی ورق (A.B، C، D، و غیره) و در داخل آرایه مشخص شده در استدلال "جدول" رخ نمی دهد.
• Interval_Prising - تعیین می کند که کدام یک از همزمان باید یک تابع را پیدا کند - دقیق یا تقریبی.

مهم: تابع "PRD" فقط در اولین ورودی منحصر به فرد به دنبال یک تصادف است، اگر Deasure_Dation در Argument "جدول" چندین بار وجود داشته باشد و دارای مقادیر متفاوت باشد، پس تابع "VDP" تنها اولین تصادف را پیدا خواهد کرد نتایج برای همه موارد دیگر برای فرمول UPR در دسترس نیست. (Vlookup) با رویکرد دیگری در کار با داده ها مرتبط است، یعنی تشکیل "کتاب های مرجع".
ماهیت رویکرد در ایجاد یک کتاب مرجع از انطباق با استدلال "Deasure_Name" از یک نتیجه خاص، به طور جداگانه از آرایه اصلی، که در آن شرایط و مقادیر مربوطه تجویز می شود:

سپس فرمول قبلا در قسمت کار جدول جدول با اشاره به دایرکتوری پر شده قبل از آن تجویز شده است. کسانی که. در دایرکتوری در ستون "D"، مقدار مقدار از ستون "A"، و هنگامی که مطابق با ستون "E" در ستون B مشتق شده است.
نحو فرمول:
نتیجه را در سلول B2 نمایش می دهد:

نتیجه:

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

می توان دید که خطوط در ستون های "محصول" هر دو جداول هماهنگ نیستند، اما این مانع استفاده از عملکرد "VDP" نیست.
نتیجه را در سلول B2 نمایش می دهد:

اما هنگامی که حل، ما با یک مشکل جدید مواجه هستیم - با "کشش" فرمول که ما به سمت راست ما از ستون "در" به ستون "E" نوشته ایم، باید به صورت دستی این استدلال را به صورت دستی "شماره_stolbet" جایگزین کنیم. این مورد یک وقت گیر و غیرقابل انکار است، زیرا به کمک دیگر کمک می کند - "ستون" (ستون).
ویژگی های نحو:
ستون ([لینک])

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

اگر از یک رکورد نوع استفاده می کنید:

تابع تعداد ستون فعلی را نشان می دهد (که در آن فرمول در سلول نوشته شده است).
به عنوان یک نتیجه، یک عدد را که می تواند در عملکرد "PRD" استفاده شود، از ما استفاده می شود و ثبت نام زیر فرمول را دریافت می کنیم:
نتیجه را در سلول B2 نمایش می دهد:

\u003d VD ($ a3؛ $ h $ 3: $ m $ 6؛ colument ()؛ 0)

تابع "ستون" تعدادی ستون فعلی را تعیین می کند که توسط استدلال "شماره DRIVE-DRIVE" برای تعیین تعداد ستون جستجو در دایرکتوری استفاده می شود.
علاوه بر این، شما می توانید از طراحی استفاده کنید:

به جای شماره "1"، شما می توانید از هر شماره (و همچنین نه تنها برای کسر آن، بلکه همچنین برای اضافه کردن به مقدار نتیجه) استفاده کنید، برای به دست آوردن نتیجه دلخواه، اگر تمایل به اشاره به یک سلول خاص وجود ندارد در ستون با تعداد ما نیاز داریم
نتیجه نتیجه:

ما همچنان موضوع را توسعه می دهیم و شرایط را پیچیده می کنیم: تصور کنید که ما دو کتاب مرجع با داده های داده های مختلف داریم و لازم است ارزش را در جدول نشان دهیم بسته به نوع کتاب مرجع در ستون "دایرکتوری" مشخص شده است
وضعیت:

• اگر شماره 1 در ستون "Handbook" مشخص شود، داده ها باید از جدول "دایرکتوری_1"، اگر شماره 2، سپس از جدول "directory_2" مطابق با ماه مشخص شده باشد

گزینه راه حل که بلافاصله به ذهن می آید، زیر موارد زیر است:

\u003d اگر ($ B3 \u003d 1؛ VD ($ a3؛ $ g $ 3: $ i 6، colument () - 1؛ 0)؛ VD ($ a3؛ $ k $ 3: $ m $ 6؛ colument () - 1؛ 0))

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

\u003d vd ($ a3؛ انتخاب ($ b3؛ $ g $ $ 3: $ i $ 6؛ $ k $ 3: $ m $ 6)؛ ستون () - 1؛ 0)

طرفداران: فرمول ممکن است شامل تا 254 نام کتاب های مرجع، معکوس باشد - نام آنها باید به شدت عددی باشد.
نتیجه فرمول با استفاده از عملکرد "انتخاب":

پاداش: UPR بر روی دو یا چند نشانه در استدلال "The Desiede_station".
وضعیت:

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

هر دو جداول زیر هستند:

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

با استفاده از نماد "&"، سه نشانه را به یکی ترکیب می کنیم (جداساز بین کلمات می تواند هر کدام، به عنوان کامل نیست، اصلی ترین چیزی است که از یک قانون مشابه و برای جستجو استفاده کنید)
آنالوگ فرمول می تواند عملکرد "سکه" باشد (concatenate)، در این مورد به نظر می رسد این است:

\u003d گرفتن (h3؛ "_"؛ i3؛ "_"؛ J3)

پس از یک ویژگی اضافی برای هر ورودی در جدول با داده ها ایجاد می شود، به نوشتن تابع جستجو بر روی این ویژگی ادامه دهید:
نتیجه سلول D3 را نمایش می دهد:

\u003d if iTility (APP (A2 & "_" & B2 & "_" $ $ 2: $ k $ 6؛ 5؛ 0)؛ 0)؛ 0)؛ 0)؛ 0)؛ 0)؛ 0)؛ 0)؛ 0)؛ 0)؛ 0)؛ 0)؛ 0)؛ 0)؛ 0)؛

در عملکرد "VDP" به عنوان استدلال "the desiede_station"، ما از همان اوراق قرضه سه نشانه (name_class_faska) استفاده می کنیم، اما ما آن را در حال حاضر در جدول برای پر کردن و وارد کردن به طور مستقیم به استدلال (به عنوان یک گزینه، آن را ممکن است ارزش را برای استدلال به ستون اضافی در جدول برای پر کردن برجسته کنید، اما این اقدام اضافی خواهد بود).
من به شما یادآوری میکنم که اگر مقدار مورد نظر یافت نشد، با استفاده از تابع "ifore" ضروری است، اگر مقدار مورد نظر یافت نشد، و عملکرد "PRD" ما را "# h / d" (در مورد آن در زیر) به ارمغان می آورد.
نتیجه در تصویر زیر است:

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

Keys_3 جستجو برای ارزش در آرایه، و یا زمانی که یک آرایه قادر به کمک به ما نیست

وضعیت را در نظر بگیرید زمانی که لازم است بدانید که آیا در آرایه ای از سلول ها ما ارزش ها را نیاز داریم.
یک وظیفه:

• در ستون "وضعیت جستجو"، مقدار مشخص شده است و لازم است تعیین کنید که آیا در ستون "آرایه جستجو" وجود دارد

به صورت بصری، همه چیز به صورت زیر ظاهر می شود:

همانطور که می بینیم، عملکرد "PRD" در اینجا بی قدرت است، زیرا ما به دنبال یک تصادف دقیق هستیم، یعنی حضور مقادیر در سلول.
برای حل مشکل، لازم است از ترکیبی از چندین توابع استفاده کنید، یعنی:
"اگر یک"
"اگر ابزار"
"مستحکم"
"برای پیدا کردن"

به ترتیب از همه، "اگر" ما قبلا قبلا جدا شده ایم، بنابراین ما به "اینترنت" (IFEror) عمل می کنیم

اگر خطا (مقدار، value_no_r.)

• ارزش یک استدلال برای خطاها بررسی شده است.
• value_no_name - ارزش در صورت محاسبه فرمول بازگشت. انواع زیر خطاها امکان پذیر است: # n / d، # معنی! # link!، # del / 0، # شماره!، # نام؟ و # خالی است!

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

"پایین" (پایین تر)

• متن - متن قابل تبدیل به ثبت نام پایین تر.

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

در حال حاضر بیشتر در مورد عملکرد نحو "پیدا کردن" (پیدا کردن).

پیدا کردن (deasure_text، مشاهده شده_text، [nach_position])

• the request_text متن شما می خواهید پیدا کنید.
• به دنبال from_text - متن که در آن شما نیاز به پیدا کردن متن مورد نظر.
• nach_position - نشانه ای از آن شما می خواهید یک جستجو را شروع کنید. اولین علامت در متن "Watching_Text" دارای شماره 1. اگر شماره مشخص نشده باشد، آن را برابر با 1 قرار می دهد.

نحو راه حل فرمول به نظر می رسد:
نتیجه را در سلول B2 نمایش می دهد:

\u003d اگر (اگر ابزار (پیدا کردن (Litch (A2)؛ Strochene (E2)؛ 1)؛ 0) \u003d 0؛ "شکست"؛ "Bingo!")

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

1. Stricken (A2) - این استدلال را "The Deasure_Text" در سلول در A2 به متن با ثبت نام پایین تر تبدیل می کند
2. تابع "پیدا کردن" شروع به جستجو برای آرگومان تبدیل شده "Deasure_Text" در آرایه "Listing_Text"، که توسط تابع "strach" (E2)، همچنین به متن با ثبت نام پایین تر تبدیل می شود، جستجو می شود.
3. در مورد، تابع یک تصادف را پیدا می کند، به عنوان مثال شماره دنباله ای از اولین نماد کلمه / ارزش تطبیق را به دست می آورد، شرط حقیقت در فرمول "اگر" باعث شود. مقدار نتیجه صفر نیست. به عنوان یک نتیجه، ارزش "Bingo!" در ستون "نتیجه" نمایش داده می شود.
4. اگر، تابع یک تصادف را پیدا نمی کند. تعداد دنباله ای از اولین نماد کلمه / مقدار تطبیق مشخص نشده است و خطا به جای مقدار بازگردانده می شود، وضعیت در فرمول "اگر ابزار" ایجاد شود و مقدار برابر "0" را که مربوط به آن است، بازگرداند دروغ در فرمول "اگر"، به دلیل مقدار به دست آمده "0" است. به عنوان یک نتیجه، ارزش "شکست" در ستون "نتیجه" نمایش داده می شود.

همانطور که می توان از شکل بالا دیده می شود، به لطف توابع "strach" و "پیدا کردن" ما مقادیر دلخواه را بدون در نظر گرفتن ثبت نام نماد و محل در سلول پیدا می کنیم، اما شما باید به رشته توجه کنید 5
وضعیت جستجو به عنوان "111" تنظیم شده است، اما در آرایه جستجو با ارزش "1111111 کوکی" نشان داده شده است، با این حال، فرمول نتیجه "BINGO!" را مطرح می کند. این به این دلیل است که ارزش "111" در تعدادی از مقادیر "1111111" گنجانده شده است، به عنوان یک نتیجه، یک تصادف است. در مورد مخالف، این وضعیت کار نخواهد کرد.

Case_4 جستجو برای ارزش در آرایه در شرایط مختلف، و یا زمانی که یک آرایه از زمان قادر به کمک به ما نیست

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

وضعیت:

• در جدول با نتیجه، شما باید داده ها را مطابق با هماهنگی شرایط "نام" و "ماه" تضعیف کنید.

برای حل چنین کاری، ترکیبی از توابع "index" و "شرکت جستجو" مناسب خواهد بود

تابع نحو "index" (index)

فهرست (آرایه، number_stroke، [number_stolbits])

• یک آرایه، طیف سلول هایی است که از آن ارزش ها در صورت وقوع یک تصادف جستجو آنها نمایش داده می شود.
• اگر یک آرایه تنها شامل یک رشته یا یک ستون باشد، استدلال "number_stroke" یا "number_stolbet" به ترتیب اجباری نیست.
• اگر آرایه طول می کشد بیش از یک خط و یک ستون، و تنها یکی از استدلال "number_stroke" تنظیم شده است، پس تابع "index" یک آرایه را تشکیل می دهد که شامل یک کل خط یا کل ستون "آرایه" است.
• number_stroke - شماره ردیف در آرایه ای که می خواهید ارزش را بازگردانید.
• شماره آهنگ شماره ستون در آرایه ای است که می خواهید ارزش را بازگردانید.

به عبارت دیگر، تابع از آرایه مشخص شده در ارزش Argument "Array" که در تقاطع مختصات مشخص شده در استدلال های مشخص شده در Arguments "Number_stroke" و "شماره_stolbet" است، باز می گردد.

تابع Syntax "شرکت جستجو" (مسابقه)

شرکت جستجو (search_name، مشاهده شده توسط_massive، [نوع نرم افزار]))

• Desirable_Station یک مقدار است که با مقادیر در استدلال مطابقت داده شده توسط_Massive مطابقت دارد. استدلال مورد نظر ممکن است یک مقدار (تعداد، متن یا مقدار منطقی) یا یک پیوند به یک سلول حاوی چنین ارزش باشد.
• visible_massive - محدوده سلول هایی که در آن جستجو انجام می شود.
• type_name یک استدلال اختیاری است. شماره -1، 0 یا 1.

عملکرد سرویس جستجو برای عنصر مشخص شده در محدوده سلول جستجو می شود و موقعیت نسبی این عنصر را در گروه باز می گرداند.
ماهیت استفاده از ترکیبی از توابع "index" و "موتور جستجو" این است که ما هماهنگی های مقادیر را برای نام آنها بر اساس "محورهای مختصات" تولید می کنیم.
محور Y ستون "نام" خواهد بود، و AXIS X خط "ماه" است.

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

شرکت جستجو ($ a4؛ $ i $ 4: $ i $ 7؛ 0)

شماره را در امتداد محور Y باز می گرداند، در این مورد برابر با 1 برابر خواهد بود، زیرا مقدار "A" در محدوده مورد نظر وجود دارد و دارای موقعیت نسبی "1" در این محدوده است.
بخشی از فرمول:

شرکت جستجو ($ 3، $ j $ 3: $ l $ 3؛ 0)

مقدار # n / d را باز می گرداند، زیرا ارزش "1" در نمای باند گم شده است.

بنابراین، ما مختصات نقطه (1؛ # n / d) را به دست آوردیم که عملکرد "index" از "آرایه" برای جستجو در استدلال استفاده می کند.
تابع کاملا نوشته شده برای سلول B4 فرم زیر را دارد:

\u003d index ($ j $ 4: $ l $ 7؛ هیئت مدیره جستجو ($ a4؛ $ I 4: $ I 7 $؛ 0)؛ هیئت مدیره جستجو ($ 3؛ $ j $ 3: $ l $ 3؛ 0 )

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

\u003d شاخص ($ j $ 4: $ l $ 7؛ 1؛ # n / d))

از آنجا که استدلال "number_stolb" "# h / d" است، نتیجه برای سلول B4 مناسب خواهد بود.
همانطور که از نتیجه حاصل دیده می شود، تمام مقادیر موجود در جدول با کتاب مرجع همخوانی ندارند و در نتیجه ما می بینیم که بخشی از مقادیر در جدول به عنوان "# H / D" نمایش داده می شود، که استفاده از داده ها برای محاسبات بیشتر دشوار است.
نتیجه:

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

\u003d اگر Utylum (index ($ j $ 4: $ l $ 7؛ هیئت مدیره جستجو ($ a4؛ $ i $ 4: $ I 7 $؛ 0)؛ هیئت مدیره جستجو ($ 3؛ $ j $ 3: $ l $ 3؛ 0))؛ 0)

تظاهرات نتیجه:

همانطور که در تصویر دیده می شود، مقادیر "# h / d" در محاسبات بعدی با استفاده از مقادیر موجود در جدول با ما دخالت نمی کنند.

مقادیر جستجو Case_5 در محدوده تعداد

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

• از 0 تا 1000 \u003d a
• از 1001 تا 1500 \u003d ب
• از 1501 تا 2000 \u003d در
• از 2001 تا 2500 \u003d g
• بیش از 2501 \u003d D

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

نمایش (deasure_station؛ ردیابی؛ [vector_teults])

• سفارش دلخواه ارزش این است که عملکرد مشاهده در اولین بردار به دنبال آن است. سفارش دلخواه ممکن است یک عدد، متن، ارزش منطقی، نام یا مرجع به مقدار باشد.
• visible_extector - محدوده متشکل از یک ردیف یا یک ستون است. مقادیر در استدلال قابل مشاهده_tector می تواند متن، اعداد یا مقادیر منطقی باشد.
• مقادیر در Argument مشاهده شده باید به ترتیب صعودی مرتب شوند: ...، -2، -1، 0، 1، 2، ...، a-z، دروغ، حقیقت؛ در غیر این صورت، عملکرد مشاهده می تواند نتیجه اشتباه را بازگرداند. متن در ثبت های پایین و بالا معادل آن است.
• vector_teults - محدوده متشکل از یک ردیف یا ستون. vector_tegults باید همان اندازه را به عنوان ردیابی مشاهده کنید.

\u003d نمایش (E3؛ $ $ 3: $ $ 7؛ $ B $ 3: $ B $ 7)

استدلال "watching_tector" و "vector_tector" را می توان به صورت یک آرایه ثبت کرد - در این مورد شما مجبور نیستید آنها را به یک جدول جداگانه در صفحه اکسل برداشت کنید.
در این مورد، تابع به نظر می رسد:
نتیجه سلول B3 را نمایش می دهد:
\u003d نمایش (E3؛ (0؛ 1001؛ 1501؛ 2001؛ 2501)؛ ("a"؛ "b"؛ "b"؛ "g"؛ "d"))

Case_6 خلاصه اعداد در ویژگی های

برای خلاصه کردن اعداد، سه توابع مختلف را می توان برای ویژگی های خاص استفاده کرد:
SUMIF - تنها یک نشانه را خلاصه می کند
Sumilimn (SUMIFS) - نشانه های بسیاری را خلاصه می کند
SumProduct - خلاصه مجموعه نشانه ها
همچنین گزینه ای با استفاده از "Sums" (SUM) و توابع فرمول آرایه ها وجود دارد، زمانی که فرمول "Sums" به یک آرایه ساخته شده است:
((\u003d sums (() * ()))
اما این رویکرد کاملا ناخوشایند است و به طور کامل در عملکرد فرمول "Summipot"
در حال حاضر بیشتر در نحو "خلاصه -":

Summipatic (Array1، [array2]، [array3]، ...)

• Array1 اولین آرایه ای است که اجزای آن نیاز به چند برابر دارند و سپس نتایج را کاهش می دهند.
• Array2، آرایه 3 ... - از 2 تا 255 آرایه هایی که اجزای آنها نیاز به چند برابر دارند و سپس نتایج را تجزیه می کنند.

وضعیت:

• مقدار کل را در هزینه حمل و نقل برای هر یک از محصولات برای یک دوره خاص پیدا کنید:

همانطور که می توان از جدول با داده ها برای محاسبه هزینه مشاهده کرد، شما باید قیمت را با مقدار، و مقدار به دست آمده با استفاده از شرایط انتخاب برای انتقال به جدول با نتیجه ضرب کنید.
با این حال، فرمول "Summpariz" اجازه می دهد تا چنین محاسبات داخل فرمول.
نمایش نتیجه در سلول B4:

\u003d Sumpacy ($ A4 \u003d $ H $ 3: $ H $ 11) * ($ K $ 3: $ K $ 11\u003e \u003d B $ 3) * ($ K $ 3: $ K $ 11
ما فرمول را در قطعات تجزیه خواهیم کرد:
- شرایط انتخاب را در ستون "نام" جدول با داده ها در ستون "نام" در جدول با نتیجه تنظیم کنید

($ k $ 3: $ k $ 11\u003e \u003d b $ 3) * ($ k $ 3: $ k $ 11 - ما از شرایط طبق زمان فریم می خواهیم، \u200b\u200bتاریخ بزرگتر یا برابر با تعداد اول ماه جاری است، اما کمتر از تعداد اول ماه زیر است. به طور مشابه، یک شرط در یک جدول با نتیجه، یک آرایه - در جدول با داده ها.
- کاهش ستون "شماره" و "قیمت" در جدول با داده ها.
علاوه بر این، به علاوه این ویژگی، روش آزاد برای ضبط شرایط است، آنها می توانند در هر جهت ثبت شوند، بر نتیجه تاثیر نمی گذارد.
نتیجه:

در حال حاضر شرایط را پیچیده و الزام اضافه کنید که انتخاب توسط نام "کوکی ها" تنها در کلاس های "کوچک" و "بزرگ"، و به نام "کاسه" رخ داده است، به جز کلاس "با مربا":

نمایش نتیجه در سلول B4:

\u003d Sugucy ($ A4 \u003d $ H $ 3: $ H $ 11) * ($ j $ 3: $ j $ 11\u003e \u003d b $ 3) * ($ j $ 3: $ j $ 11
فرمول انتخاب برای کوکی ها یک شرایط جدید را اضافه می کند:

(($ i $ 3: $ i $ 11 \u003d "کوچک") + ($ 1 £ 3: $ 11 $ 11 \u003d "بزرگ"))

- همانطور که دیده می شود، دو یا چند شرایط در یک ستون به یک گروه جداگانه با استفاده از نماد "+" و نتیجه گیری شرایط به براکت های اضافی اختصاص داده می شود.
فرمول انتخاب برای پاها نیز یک وضعیت جدید اضافه شد:

\u003d Sumpapers (($ a5 \u003d $ h $ 3: $ H $ 11) * ($ j $ 3: $ j $ 11\u003e $ $ 3) * ($ j $ 3: $ j $ 11 "با جم") ($ l $ 3: $ 11 $ 11) * ($ k $ 3: $ k $ 11))

این هست:

($ i $ 3: $ i 11 $<>"با مربا")

- در واقع، در این فرمول، شرایط انتخاب و همچنین زمانی که در کوکی ها انتخاب شده بود، بنویسید، اما پس از آن باید سه شرایط را در فرمول فهرست کنید، در این مورد، ساده تر است که یک استثناء را بنویسید - نه دقیقا " با jam "برای این ما از ارزش استفاده می کنیم"<>».
به طور کلی، اگر علائم / کلاس ها پیش از آن شناخته شده باشد، بهتر است آنها را به این گروه ها ترکیب کنید، کتاب های مرجع را ایجاد کنید تا تمام شرایط را در عملکرد ثبت کنید، آن را دفع کنید.
نتیجه:

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

ممنون بخاطر وقتی که گذاشتید!

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

برای جستجوی داده ها در جدول اکسل، باید از آیتم منو استفاده کنید "پیدا کردن و برجسته کردن" در برگه "اصلی"که در آن شما باید گزینه را انتخاب کنید "برای پیدا کردن" یا برای تماس با یک ترکیب کلیدی استفاده کنید "Ctrl + F".

به عنوان مثال، سعی کنید تعداد مورد نیاز را در میان داده های جدول ما پیدا کنید، زیرا هنگام جستجو برای اعداد لازم است برخی از ظرافت های جستجو را در نظر بگیرید. ما در شماره جدول اکسل جستجو خواهیم کرد "10".

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

بیایید سعی کنیم تمام مقادیر را در یک بار پیدا کنیم، با تمام آنچه که در آن یافت می شود در پنجره تحت تنظیمات جستجو ذکر شده است. اگر تمام تنظیمات پیش فرض را ترک کنید، نتیجه جستجو دقیقا همان راهی نیست که ما انتظار داشتیم.

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

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

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