معرفی توابع جستجو و مرجع در اکسل یا توابع Lookup & Reference در بخش پایگاه دانش در سه مقاله ارایه شده است، در مقاله ی اول به معرفی کلی این دسته از توابع در اکسل پرداخته شده است و برخی توابع مرجع نیز مورد بررسی قرار گرفته اند و در مقاله ی دوم با دیگر توابع مرجع آشنا شدیم. در این مقاله با توابع جستجو در اکسل آشنا میشویم، توابع VLOOKUP و HLOOKUP در اکسل از جمله معروفترین توابع اکسل میباشند که همواره مورد توجه و استفاده ی کاربران با تخصص های مختلف قرار گرفته اند لذا خواندن این مقاله بسیار مهم میباشد. تابع VLOOKUP در اکسل علیرغم معروفیت بسیار زیاد آن دارای محدودیت هایی در استفاده میباشد که در این مقاله این محدودیت ها به تفصیل بیان میشود.
اکیداً توصیه میشود پیش از مطالعه ی این نوشته، مقاله ی فرمول نویسی در اکسل را به منظور آشنایی با ساختمان و اجزای فرمول ها و همچنین آشنایی با انواع آرگومان ها و عملگرها در اکسل مطالعه نمایید.
به عنوان یادآوری، شکل زیر خلاصه ای از توابع جستجو و مرجع در اکسل (Lookup & Reference Functions) را نشان میدهد:
توابع Lookup & Reference در اکسل
این دسته از توابع به منظور جستجو یا Lookup در بانک های اطلاعاتی مورد استفاده قرار میگیرند، منظور از جستجو صرفاً پیدا کردن یک عبارت نیست، توابع VLOOKUP و HLOOKUP در اکسل در واقع به کاربران در تکمیل بانک های اطلاعاتی و استفاده از آنها بسیار کمک میکنند، با یاری گرفتن از این توابع انجام بسیاری از کارها در اکسل راحت میشود. تابع VLOOKUP در اکسل به منظور جستجوی یک عبارت در یک جدول و یافتن اطلاعات متناظر با آن به کار برده میشود. تصور کنید فهرستی از پرسنل شرکت خود دارید که شامل مشخصات و شماره تماس است، برای یافتن شماره تماس مربوط به یک شخص با استفاه از نام و نام خانوادگی میتوان از تابع VLOOKUP در اکسل استفاده نمود.
تابع LOOKUP در اکسل
این تابع دو فرم آرایه ای و Vector دارد، فرم Vector این تابع مورد نظر ما میباشد. در اکسل به یک محدوده از سلول ها که شامل تنها یک سطر یا یک ستون باشد Vector میگوییم مثلاً محدوده های A1:A88 یا A1:M1 هر دو Vector هستند. تابع LOOKUP یک عبارت (یک عدد) را در یک Vector جستجو میکند و در صورت پیدا کردن آن عبارت محتوای سلول هم موقعیت با سلول پیدا شده در سطر یا ستون مجاور Vector جستجو شده را به عنوان خروجی به کاربر میدهد. سینتکس تابع LOOKUP به صورت زیر است:
LOOKUP (lookup_value, lookup_vector, result_vector)
- آرگومان اول، که یک آرگومان اجباری میباشد، در واقع عبارت مورد نظر برای جستجو است. این آرگومان میتواند عدد، رشته متنی، Logical Values (شامل صفر و یک یا True و False) یا آدرس یک سلول حاوی عبارت مورد نظر باشد.
به خاطر داشته باشید که از تابع LOOKUP به خاطر روش عملکرد آن تنها برای جستجوی اعداد استفاده نمایید. در واقع آرگومان اول یک عدد باید باشد علیرغکم اینکه سایر انواع داده ها نیز میتوانند انتخاب شوند.
- آرگومان دوم، Vector محل جستجو میباشد که باز یک آرگومان اجباری است. این آرگومان یک محدوده از اکسل شامل یک سطر یا یک ستون میباشد که قرار است عبارت آرگومان اول در آن جستجو شود. سلول های محدوده ی Vector هم میتوانند حاوی اعداد یا رشته های متنی یا Logical Values (شامل صفر و یک یا True و False) باشند. مجدداً تاکید میشود تابع LOOKUP برای اعداد مورد استفاده قرار گیرد زیرا به علت روش عملکرد آن به کابردن این تابع برای داده های غیر عددی پاسخ درست نمیدهد.
نکته ی بسیار مهم اینست که Vector حتماً باید به صورت صعودی مرتب (Sort) شده باشد (صعودی مانند : از اعداد منفی به مثبت یا کوچکتر به بزرگتر یا از حرف A تا حرف Z یا False به True). در غیر اینصورت ممکن است تابع LOOKUP جواب صحیح را به ما ندهد.
- آرگومان سوم، یک Vector مانند آرگومان دوم و به همان اندازه میباشد، مثلاً اگر آرگومان دوم (lookup_vector) یک Vector افقی با ۵۵ سلول باشد، آرگومان سوم (result_vector) هم باید یک Vector افقی با ۵۵ سلول باشد. در واقع پس از یافتن عبارت مورد جستجو در Vector اول (lookup_vector)، تابع LOOKUP محتوای سلول هم تراز با سلول حاوی عبارت جستجو در result_vector را به عنوان خروجی ارائه میدهد.
اگر تابع LOOKUP نتواند عبارت مورد جستجو را در lookup_vector پیدا کند، آخرین (بزرگترین) مقدار در lookup_vector را که برابر یا کوچکتر از عبارت مورد جستجو میباشد را به عنوان نتیجه ی جستجو میپذیرد (به صعودی بودن lookup_vector دقت کنید). و اگر عبارت مورد جستجو، کوچکتر از کوچکترین عضو lookup_vector باشد خروجی تابع خطای N/A# میباشد.
به مثال زیر دقت کنید:
دقت داشته باشید که لزومی ندارد دو محدوده lookup_vector و result_vecto مانند مثال بالا، حتماً در کنار یکدیگر قرار داشته باشند، بلکه صرفاً هم اندازه بودن این دو محدوده (Vector) برای بدست آوردن جواب کافیست.
تابع VLOOKUP در اکسل
تابع VLOOKUP در اکسل یا Vertical LOOKUP مانند تابع LOOKUP عمل میکند، اگر منطق تابع LOOKUP و طریقه ی کار کردن با آن را بدانید درک VLOOKUP برای شما آسان تر خواهد بود، لذا حتماً بخش تابع LOOKUP را قبل از این قسمت بخوانید.
تابع VLOOKUP در اکسل میتواند یک عبارت مورد نظر را در اولین ستون یک محدوده (سمت چپ ترین ستون یک محدوده در نوشتار چپ به راست) جستجو کند و پس از پیدا کردن عبارت مورد نظر محتوای سلول هم تراز (هم ردیف) با سلول پیدا شده در هریک از ستون های مجاور با ستون مورد جستجو را به عنوان خروجی اعلام نماید. در واقع کلمه Verticalدر نام این تابع به جستجو در ستون (یا Vector عمودی) اشاره میکند. دقت داشته باشید تابع VLOOKUP عبارت مورد جستجو را تنها در ستون اول محدوده انتخاب شده جستجو میکند لذا باید در انتخاب محدوده دقت نمایید، از طرفی به “چپ به راست” بودن یا “راست به چپ” بودن نیز دقت کنید، زیرا ستون اول از نظر اکسل در چنش “چپ به راست”، سمت چپ ترین و در چینش “راست به چپ” سمت راست ترین ستون است.
برای درک بهتر این تابع، جدول زیر را در نظر بگیرید، ستون اول شماره شناسایی پرسنل، ستون دوم واحد محل کار هر نفر و ستون سوم نام شخص میباشد، میخواهیم در یک سلول فرمولی بنویسیم که با گرفتن شماره شناسایی هر فرد نام آن فرد را به عنوان خروجی بدهد، برای این کار میتوانیم از VLOOKUP کمک بگیریم.
ساختار این تابع به صورت زیر است:
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
تابع VLOOKUP چهار آرگومان دارد، سه آرگومان اول اجباری و آرگومان آخر اختیاری میباشد.
آرگومان اول عبارتی است که کاربر میخواهد جستجو کند، در مثال بالا این آرگومان شماره پرسنلی شخص میباشد، این آرگومان همانند آنچه در تابع LOOKUP وجود داشت، میتواند عدد، رشته متنی، آدرس سلول و یا یک مقدار منطقی (Logical Value) باشد.
آرگومان دوم یک محدوده از اکسل میباشد، تمام جدول داده ها به عنوان این آرگومان به تابع معرفی میگردد، در مثال بالا محدوده ی A2:C10 به عنوان آرگومان دوم قرار میگیرد، همینطور میتوان نام محدوده را به عنوان آرگومان دوم نوشت (در مبحث نامگذاری محدوده ها با این بحث آشنا شدیم).
همانند تابع LOOKUP، جستجو در تابع VLOOKUP تنها در یک ستون (ستون اول داده ها) یا به عبارتی در Vector اول صورت میگیرد، هر محدوده ای که به عنوان آرگومان دوم (table_array) به تابع معرفی شود، ستون اول (سمت چپ ترین ستون در چینش چپ به راست) به عنوان Vector محل جستجو برای عبارت مورد نظر استفاده میشود.
آرگومان سوم col_index_num، یک عدد میباشد و شماره ستون داده ی مورد نظر برای استخراج از جدول است، ستون شماره ۱ همان ستون یا Vector جستجو شده و ستون شماره ۲ ستون مجاور میباشد و به همین ترتیب. در مثال بالا، این آرگومان عدد ۳ میباشد، زیرا ستون حاوی نام شخص ستون سوم از جدول است.
اگر آرگومان سوم تابع VLOOKUP کمتر از یک باشد خروجی تابع خطای !VALUE# و اگر این عدد بزرگتر از تعداد کل ستون ها باشد خروجی تابع خطای !REF# خواهد بود.
آرگومان چهارم یک آرگومان اختیاری و بسیار مهم است، این آرگومان Boolean میباشد و میتواند True یا False باشد. اگر این آرگومان True یا ۱ باشد یا نادیده گرفته شود، در اینصورت تابع VLOOKUP رفتار زیر را انجام میدهد:
- اولاً، داده های Vector مورد جستجو (ستون اول) باید همانند آنچه در تابع LOOKUP وجود داشت به صورت صعودی مرتب شده باشند تا مطمئن باشیم که خروجی تابع قابل اعتماد است.
- دوماً، تابع در ستون اول به دنبال عبارت مورد جستجو میگردد و در صورت نیافتن آن عبارت، دقیقاً مانند آنچه در تابع LOOKUP دیدیم، بزرگترین مقدار کوچکتر از عبارت مورد جستجو را به عنوان پاسخ میپذیرد.
- سوماً، مجدداً مشابه تابع LOOKUP، اگر تابع VLOOKUP نتواند عبارت مورد جستجو را در ستون اول (Vector جستجو شده) پیدا کند، آخرین (بزرگترین) مقدار در آن Vector که برابر یا کوچکتر از عبارت مورد جستجو میباشد را به عنوان نتیجه ی جستجو میپذیرد (به صعودی بودن داده ها دقت کنید).
حال اگر آرگومان چهارم تابع VLOOKUP در اکسل برابر False باشد، False برای تابع به معنی Exact Match است یعنی کاربر به دنبال داده ای دقیقاً منطبق بر عبارت مورد جستجو میگردد و در این حالت تابع تنها داده ای را به عنوان پاسخ میپذیرد که دقیقاً مانند عبارت مورد جستجو باشد و اگر آن را پیدا نکرد خروجی تابع برابر خطای N/A# خواهد بود.
در صورتی که آرگومان چهارم تابع VLOOKUP در اکسل False باشد نیازی به چینش صعودی داده های ستون مورد جستجو نیست در صورت وجود داشتن چند جواب، اولین مورد پیدا شده به عنوان جواب پذیرفته میشود.
در جستجوی اعداد آرگومان چهارم هرچه که باشد (True یا False)، مانند تابع LOOKUP، اگر عبارت مورد جستجو، کوچکتر از کوچکترین عضو ستون مورد جستجو (Vector) باشد خروجی تابع خطای N/A# است.
تابع HLOOKUP در اکسل
تابع HLOOKUP یا Horizontal LOOKUP، در ساختار و روش عملکرد هیچ تفاوتی با تابع VLOOKUP ندارد و تنها تفاوت آنها در افقی و عمودی بودن داده ها میباشد، تابع HLOOKUP برای جدول های افقی کاربرد دارد و سطر اول داده ها را به عنوان Vectorجستجو میکند. با یادگیری و فهم توابع LOOKUP و VLOOKUP مشکلی در کار با تابع HLOOKUP نخواهید داشت.
سلام واقعا بابت آموزش های با ارزشتون ممنونم فقط میشه لطف کنید آموزش ها رو به صورت pdf بذارید تا بشه راحت دانلود کرد ؟
ممنون
سلام، خواهش میکنم، برای پرینت مقالات به صورت PDF میتوانید از قابلیت Browser خود استفاده نمایید. موفق باشید.
سلام
وقتی از تابع VLOOKUP استفاده میکنیم و در قسمت اخر فرمول، ۰ می گذاریم در این حالت تابع تنها داده ای را به عنوان پاسخ به ما میدهد که دقیقاً مانند عبارت مورد جستجو باشد و اگر آن را پیدا نکرد خروجی تابع برابر خطای N/A# خواهد بود.
حالا اگه بخواهیم به جای نمایش خطا سلول ان را خالی بگذارد باید چکار کنیم !؟
چون نمایش خطا در سلول باعث می شود مابقی محاسبات که از این ستون فرمول می گیرند دچار مشکل شوند.
ممنون
سلام، برای این کار باید از تابعی مانند IFERROR استفاده نمایید. لطفاً توضیحات مربوط به این تابع را در مقاله توابع شرطی مطالعه نمایید. موفق باشید.
ممنون از راهنمایتون
یه سوال هم داشتم ؟
مثلا من میخوام از ستون اول دنبال عدد مورد نظر من بگرده و وقتی پیدا کرد تمام اطلاعات سطر مقابلش رو ببره اون سطری که من میخوام ممنون میشم اگه راهنمایی کنید
خواهش میکنم، شما باید از توابع جستجو و مرجع استفاده کنید. مخصوصاً تابع VLOOKUP برای این کار مناسب است. موفق باشید.
با سلام و احترام
یک جدول اکسل شامل نوع دستگاه و شماره سریال و قیمت موجود است برای تهیه لیست اقلام و مانده گیری مطلوبست نام قطعات بدون تکرار استخراج شود آیا راه حلی وجود دارد ؟
سلام، بله، برای حذف موارد تکراری در جدول میتوانید از ساختار جدول و فیلتر کردن داده ها استفاده نمایید و سطرهای تکراری را با استفاده از ابزار Conditional Formatting مشخص کنید. موفق باشید.
ممنون بابت اطلاعات مفیدی که در پیجتون قرار میدید
اگر در جدول مرجعمون آرگمان اول تکراری داشته باشه چجوری میتونیم با استفاده از vlookup گزارش گیری داشته باشیم که هر یک از تکرارها در سطر مجزا بیاد؟
مثلا اگر جدول مرجعمان این باشد: که جدول انواع درخواستهای پرسنل هست. و گزارش گیری اینجوری باشه که میخوام بدونم مثلا کد پرسنل ۱۰چند بار مراجعه کرده و هر بار چه درخواستی داشته
کد پرسنل(آرگمان اول)- تاریخ درخواست-شرح
سلام، خواهش میکنم، به این نکته حتماً توجه داشته باشید که خروجی هر تابع تنها و تنها یک عبارت است، یعنی خروجی تابعی نمیتواند چند عبارت باشد، مگر اینکه فرمول به صورت ارایه ای و در چند سلول نوشته شود. بنابراین شما باید تابعی بنویسید که تعداد تکرار شدن را استخراج نماید، و تابع VLOOKUP همیشه تنها اولین مورد یافت شده را به عنوان خروجی میپذیرد، از ترکیب توابع INDEX و MATCH میتوانید برای این منظور استفاده کنید. لطفاً امحان کنید. موفق باشید.
سلام
من یک فایل اکسل دارم که میخام بین دو شیت ارتباط از طریق تابع ایجاد کنم به این صورت که در شیت A یک جدول دارم دارای ۵ ستون که هر ستون به نام یک اپراتور خط می باشد هر اپراتور از هر کد تولید یک تعداد نامشخص در طی روز تولید می کند من میخام زمانی که در شیتAداده ای ثبت میکنم در شیت B به صورت فیش حقوقی ثبت بشه و به من بگه از هر کد چه تعدادی هر نفر زده اند.
ممنون میشم کمکم کنید.
سلام، فرمول نویسی برای این کار کمی پیچیده و نیاز به دقت دارد. شخصاً ترجیح میدهم کاری که شما میخواهید انجام دهید را از طریق ماکرو نویسی انجام دهم ولی انجام این کار با فرمول نویسی هم ممکن میباشد. از توابع جستجو و مرجع باید استفاده کنید و شاید مجبور باشید فرمول نویسی ماتریسی را به کار ببرید. موفق باشید.
سر بر شانه خدا بگذار تا قصه عشق را چنان زیبا بخواند
که نه از دوزخ بترسی و نه از بهشت
به رقص درآیی…
قصه عشق،انسان بودن ماست
اگر کسی احساست را نفهمید مهم نیست
سرت را بالا بگیر ولبخند بزن
فهمیدن احسـاس کار هر آدمی نیست!
سلام،بابت مطالب بسیار خوب و کاربردی ممنونم،امیدوارم همیشه پاینده باشید،با آرزوی موفقیت…
سلام، خواهش میکنم، بابت شعر زیبا هم تشکر میکنم. موفق باشید.
سلام ، در مورد vlookup در اکسل . سئوال و مشکل بنده در خصوص این فرمول این است که جوابی که داده میشود آخرین نفر از اسامی می باشد مثلاً در سلول اول کد پرسنلی نفر شماره یک را می نویسم ولی جوابی که بر میگرداند نام آخرین نفر از لیست است. لطفا راهنمایی کنید.ممنون
سلام، مقاله آموزشی را به دقت بخوانید.
باسلام. دو تا فایل اکسل دارم ( فایل اول از خریدار . فایل دوم از فروشنده )میخواهم بر اساس تاریخ و شماره فاکتور و مبلغ ، هر دو فایل اکسل را مغایرت گیری کند و در یه شیت یا ستون به ما بگوید اختلاف کدام ستون ها می شودکه پیگیری کنیم. چطور می شود فرمول داد؟
سلام، از توابع جستجو و مرجع میتوانید برای این کار استفاده کنید. لطفاً مقالات مربوطه را مطالعه نمایید. موفق باشید.
سلام بنده دو جدول دارم که در اولی اعداد در بازه ۰٫۸۶ الی ۱٫۱۵ رو از طریق هایلایت کردن مشخص کردم حالا می خوام اعداد متناظر با اعداد هایلایت شده رو در جدول شماره ۲ (محتوای این جدول متفاوته) هم رنگی کنم آیا راه حلی سراغ دارید؟ (جدول اول و دوم حاوی ۲۰۰۰ سطر و ۱۵ ستون مثلا در جدول ۱ سلول سطر ۱۰۰۰ و ستون ۶ در بازه هایلایتی قرار می گیرد حالا می خوام تو جدول ۲ هم همزمان سلول با مشخصات ۱۰۰۰ و ۶ رنگی بشه) تشکر بی کران
سلام، تنها ابزار برای رنگی کردن سلول ها با شرایط خاص، ابزار Conditional Formatting در اکسل میباشد. لطفاً مقاله مربوطه را مطالعه کنید.
باسلام
اگر در یک ستون داده های اعدادی داشته باشیم به عنوان کد کالا ودر ستون دوم نظیر به نظیرداده های اعدادی به عنوان تعداد کالا حالا اگر در شیت دیگری فروش کالا داشته باشیم آیا میتوان در فرمولی را در اکسل نوشت که وقتی در شیت فروش زمانی که کدکالا را وارد کردیم از تعداد آن کالا یکی را کم کرده و مانده را به مانشان بدهد؟
سلام، بله، اما مشکل این است که شما در یک سلول مشخص کد کالا را وارد نمیکنید. انجام این کار به لحاظ ماهیتی ممکن نیست.
با سلام
چطور می توان vlookup را برای داده هایی که تکرار شده اند استفاده کرد؟ تکراری غیر قابل حذف! ممنون از سایت فوق العاده تون
سلام، VLOOKUP در موارد تکراری، تنها مورد اول را به عنوان خروجی میپذیرد و این ساختار را نمیتوان تغییر داد.
سلام یک شیت داریم با یه سری اطلاعات (نام حانوادگی – کد ملی – مبلغ) در این شیت ممکنه یه کد ملی چند بار تکرار شده باشه . می خوایم ابتدا کد های ملی تکراری رو پیدا کنه سپس مجموع مبلغهاشون به ما اعلام کنه . لطفا راهنمایی نمیید .متشکرم.ضروری
سلام، میتوانید از توابعی مانند SUMIF استفاده کنید. لطفاً مقاله مربوطه را بخوانید. موفق باشید.
سلام و خسته نباشید بابت مطالب مفیدتون
من دو تا جدول أکسل دارم که یک سری مطالب براساس تاریخ تو این جداول هستش میخوام ببینم چطور میتونم این جداول رو ادغام کنم بطوری که در جدول جدید مطالب این دو تا جدول به ترتیب تاریخ پشت هم قرار بگیره خیلیییی گشتم ولی نفهمیدم چی کار کنم ممنون میشم پاسخ بدید
سلام، راه اول و ساده تر این هست که شما دو جدول را در امتداد هم کپی کنید و سپس با استفاده از Sort تاریخ ها را به ترتیب دلخواه مرتب کنید. راه دوم استفاده از Pivot Table هست که میتوانید چندین جدول را همزمان مدیریت و تحلیل کنید.موفق باشید.
با سلام
آیا میشود در v lookup در بخشی که ادرس داده مورد نظر را میدهیم همزمان ادرس بیشتر از یک داده را داد
مثلا بجای اینکه تنها شماره یک ستون داده ی مورد نظر برای استخراج از جدول را بنویسیم . شماره چند ستون مختلف را نوشت؟
سلام، خیر
با سلام
من دو جدول اکسل دارم که در هر دو جدول اعداد تکراری دارم چطور می شود اولین عدد به اولین انتخاب ربط داده شود وعدد تکراری دوم به دومین انتخاب ربط داده شود ویا حداقل اولین انتخاب کرده بقیه را صفر بگذارد تا پس از حذف رکورد های اولیه فرمول تکرار کنم.
سلام، کاری که میخواهید انجام دهید قابلیت فرمول نویسی ندارد. باید برای این کار برنامه نویسی کنید.
اگر کانل تتگرامی دارید معرفی کنی جهت دریافت وآموزش
با تشکر
سلام، خیر
با عرض سلام و احترام،
من یک فایل اکسل دارم که دو ستون دارد، یکی نام و دیگری قیمت که حداقل ۵۰۰ ردیف دارد، هر روز فایل اکسلی به دستم می‌رسد که دو ستونی که من در فایل خودم دارم را به همراه ستون‌های متعدد دیگر در خود دارد(لازم به‌ذکر است که تعداد و ترتیب عناوین فایل جدید و خودم شبیه به هم نیست)، آیا امکانش وجود دارد تا قیمت‌های فایل جدید را با کمترین زمان و نظیر به نظیر به فایل خودم منتقل کنم و در برابر هر عنوان قیمت جدیدش را بروز کنم.
سلام، بله، از تابع جستجو و مرجع استاده کنید. موفق باشید.
یک سپاسگزاری با لبخند از سمت من، بازتاب انرژی مثبت شما
سلام، متشکرم، موفق باشید.
ممنون از آموزش کاربردی
شما فرموده اید:
“VLOOKUP در موارد تکراری، تنها مورد اول را به عنوان خروجی میپذیرد و این ساختار را نمیتوان تغییر داد.”
یک فایل با حدود ۲۰ سال تاریخ دارم و میخواهم نزدیکترین تاریخ به مقدار مورد نظر را به ما بدهد
به جای اولین خروجی نمی تواند نزدیکترین ردیف به ردیف مورد نظر(آخرین خروجی تا ردیف مورد نظر) را به عنوان خروجی برگرداند
سلام، بله، برای این کار در تابع VLOOKUP آرگومان آخر را ۱ قرار دهید و داده ها را بر اساس آنچه در مورد تابع LOOKUP گفته شده مرتب کنید. موفق باشید.
سلام….میخواستم بدونم چطور میشه محتوای دو سلول مشخص یعنی سلولهای ستون ۱ و سطر دهم و همین ستون ۱ و سطر ۲۵ ام از ۲۵۰ فایل اکسل با نامهای ۱ تا ۲۵۰ را استخراج کرد؟
در واقع محتوی این دو سلول در همه این فایلها برام مهمه و میخوام استخراج کنم بدون باز کردن تک تک فایلها
سلام، برای این کار حتماً باید کد نویسی انجام دهید. موفق باشید.
سلام. من یک کاربرگ اکسل دارم که میخوام در این کار برگ دنبال یک محدوده کشخص که شامل ۴ سطر و چهر ستون هست بگردم. در واقع میخوام در یک شیت که دارای ۴ ستون و نزدیک به ۲۰۰۰۰ خط هست یک بازه ۴*۴ مشخص رو سرچ و پیدا کنم. لطفا راهنمایی کنید.
با سلام
من از vlookup می خوام در تهیه فیش حقوقی استفاده کنم به طوری که شماره پرسنلی را بزنم اطلاعات حقوق و دستمزد که در شیت دیگری وجود داره را به من در شیت فیش نشون بده ولی عمل نمیکنه و سل مورد نظر را در همون شیت فیش حقوقی در نظر میگیره.
لطفا راهنمایی فرمایید آیا دارم دستور را اشتباه استفاده میکنم؟
سلام، برای انجام این کار قاعدتاً نباید مشکلی وجود داشته باشد، لطفاً با دقت بیشتر مجدداً تلاش کنید. موفق باشید.
سلام ممنون از مطالب خوبتون ببخشید اگر بخوام از اطلاعات یک فایل اکسل در گزارش گیری در یک فایل اکسل جدید استفاده کنم باید چیکار کنم؟یعنی مثل حالت لینک کردن باشه ..ممنون میشم اگر کمکم کنید
سلام ممنون از مطالب خوبتون ببخشید اگر بخوام از اطلاعات یک فایل اکسل در گزارش گیری در یک فایل اکسل جدید استفاده کنم باید چیکار کنم؟یعنی مثل حالت لینک کردن باشه …ممنون میشم اگر کمکم کنید
سلام، با فرمول نویسی ساده میتوان از اطلاعات فایل های اکسل دیگر در فایل اکسل خود استفاده نماییم و تنها شرط این است که هر دو فایل باز باشند، برای استفاده حرفه تر نیاز است کد نویسی انجام شود. موفق باشید.
کسی می دونه چطور می تونیم برای vlookup
دستور دو شرطی قرار بدهیم؟
سلام.مشکلی در استفاده از تابع lookup پیش میاد وقتی تمام داده هایمان لینک دار باشه دیگه این تابه کار نمیکنه چرا؟
سلا، لطفاً فایلتونو برام ارسال کنید تا بررسی کنم، به نظرم نباید مشکلی و جود داشته باشد.
با سلام برای یادگیری اکسل کدوم یک موسسه آموزشی را معرفی می کنید باتشکر.
سلام، توصیه ای در این خصوص ندارم.
سلام من یه جدول شامل کد حساب و نام طرف حساب دارم ک میخوام تو یه جدول دیگه وقتی نام رو مینویسم کد حساب رو هم بیاره دقیقا کاری ک vlookup انجام میده ولی مشکل اینجاست ک این تابع نمیتونه بر اساس رشته حروف(اسامی طرف حسابها)کد متناظرش رو پیدا کنه اگه راهنمایی کنید ممنون میشم .
سلام، لطفا به آرگومان آخر تابع vlookup توجه کنید، در این مورد حتما باید false باشد. موفق باشید.
با سلام و تشکر از مدیران سایت و همه عزیزانی که با مشارکتشون در پر بار کردن اطلاعات دیگران کمک می کنند.
من یک فایل اکسل دارم حاوی ۲ شیت. یکی یک فایل داده شامل ۱۰ ستون و ۸۰ ردیف که در هر ردیف اطلاعات کارکنان ذخیره شده و در شیت بعدی یک فرم دارم که ۸ ردیف و ۱۰ ستون دارد. حالا می خواهم اطلاعات هر ردیف از شیت اول در فرم شیت دوم ثبت شود و بعد از ان این فرم را بطور کامل به طرف پایین کپی پیست کنم به گونه ای که در فرم دوم اطلاعات ردیف دوم ثبت شود و …..
متاسفانه زمانی که این کار را انجام می دهم چون در هر بار کپی پیست فرم تعداد ۱۰ سطر فاصله می افتد دقیقاً اطلاعات ردیف دهم در فرم دوم ثبت می گردد.
از راهنمایی شما سپاسگزارم
سلام، متشکرم، برای انتقال داده های باید فرمول نویسی شود.
با سلام و عرض ادب
آقای حامد قدیمی واقعا جای سپاس گذاری داره که اینقدر عالی فصیح و کامل توضیح دادین.من آدم کم حوصله ای هستم ولی وقتی نشستم پای مطالب کاملا گرفتم اموزش های شمارو.
موفق و موید باشید
سلام، متشکرم، موفق و سربلند باشید.
سلام ,من ۲ تا جدول دارم , یکی آهن آلات مورد نیاز و دیگری آهن آلات موجود,میخوام که از آهن آلات موجود برای مورد نیاز استفاده کنم طبق شرط هایی که دارم,چیزی که میخوام اینکه شماره ردیف آهن آلات موجود رو بهم بده, اگر راهنمایی کنید ممنون میشم
سلام، از ترکیب توابع Vlookup و Match استفاده کنید، برای راهنمایی بیشتر نیاز به قپفایل شما دارم. موفق باشید.
سلام
تابع ای داریم که تاریخ ورود هر داده رو مشخص کنه؟
من یک جدول پر از اعداد و ارقام دارم که گاهی لازمه بدونم که فلان عدد در چه تاریخی وارد شده
سلام، خیر، در کل تابعی در اکسل وجود ندارد که در صورت رخداد رویدادی خاص تاریخ و ساعت آن رخداد رویداد را ثبت نماید. مگر اینکه شما از محیط ویژوال بیسیک این تابع را کد نویسی و ایجاد نمایید. موفق باشید.
من یه جدول دارم که در ستون کد مشتری هست و در ستون دوم تاریخ خرید و مشتری هایی هستن که چندین تاریخ خرید براشون ثبت شده. میخوام در ستون سوم فاصله بین دو تاریخ خرید برای هر مشتری نوشته شه، از چه فرمولی استفاده کنم؟ که از ستون اول کد مشتری رو سرچ کنه و تاریخای خریدشو از هم کم کنه
سلام، برای انجام این کار ابتدا نیاز به جدولی دارید که بر اساس فهرست مشتریان و خریدهای آنها ایجاد شده باشد، در این صورت میتوانید فرمولی ساده برای رسیدن به مقصود خود ایجاد نمایید. موفق باشید.
با سلام و تشکر از مطالب مفید و آموزنده
میخواستم بپرسم در یک ستون حاوی متن های طول ایا میشود تعیین کرد که اگر یک کلمه مشخص مثل (anemia) در این متن ها بود در ستون مقابلش عدد ۱ بگذارد؟
جهت تحلیل یک فایل به این اطلاعات احتیاج داریم
سلام، خواهش میکنم، بله، کافی توابع SEARCH و IF را با هم ترکیب کنید و در صورت پیدا کردن کلمه مورد نظر خروجی فرمول ۱ باشد.
سلام ببخشید من میخام در یک شیت اطلاعات چند نفررا در یک فاکتورخرید وارد کنم و این اطلاعات در شیت بعدی پشت سر هم ردیف شوند باید از چه فرمولی استفاده راهنمایی کنید ممنون میشم
سلام ببخشید من میخام در یک شیت اطلاعات چند نفررا در یک فاکتورخرید وارد کنم و این اطلاعات در شیت بعدی نام افراد پشت سر هم ردیف شوند باید از چه فرمولی استفاده راهنمایی کنید ممنون میشم
سلام، برای این کار باید کد نویسی انجام دهید و نیتوان در محیط اکسل فرمولی نوشت. موفق باشید.
سلام، تعدادی شرکت دارم که هر شرکت تعداد رکوردهای متفاوتی داره. مثلا شرکت ۱، برای ۳ ماهه اول سال، ۲۰ روز معاملاتی داره…شرکت ۲ در همان بازه، ۳۵ روز معاملاتی…شرکت ۳، ۶۲ روز و….
حالا من میخوام انتهای هر ۳ ماه از یه فرمول یکسان برای همه شرکت ها استفاده کنم که تعداد روزهای معاملاتی هم در این فرمول دخیل است و چون در شرکتهای مختلف برابر نیست من نمیتونم فرمول رو یکبار بنویسم و درگ کنم ….آیا راه حلی داره؟؟؟؟ممنون از شمااا
سلام، بله، برای بدست آوردن تعداد روزهای معاملاتی میتوانید از توابعی مانند COUNT استفاده نمایید. موفق باشید.
باسلام
من تعداد ۱۰ شیت در یک فایل دارم که هر شیت مربوط به اقلام راکد هر سال انبار می باشد،می خواهم تمام شیت ها را در یک شیت بیاورم لطفا راهنمایی کنید،ضمنا تعداد قطعات راکد در سالهای مختلف باهم فرق دارد(سال ۹۳ صد قلم،سال ۹۴ صدوده قلم و…) و ممکن است بعضی اقلام در طی چند سال تکرار شده باشد.
سلام، برای این کار در اکسل تنها میتوانید به صورت دستی کپی نمایید. تنها گزینه دیگر کد نویسی میباشد. موفق باشید.
سلام
ممنونم بابت راهنماییهای خوبتون
در یک سلول برای بدست آوردن مقدار از فرمول استفاده کردم و مقدار سلول مورد نظر با توجه به فرمول تغییر میکند چطوری میتونم سلولی که مقدار رو بر اساس فرمول برمیگرداند رو به عنوان مقدار جستجو در تابع استفاده کنم؟؟
سلام، در تابع مستقیماً به سلول مورد نظر ارجاع دهید. با این شرایط با به روز شدن مقدار سلول تابع نیز به روز رسانی میشود. موفق باشید.
” آرگومان سوم، یک Vector مانند ((آرگومان اول)) و به همان اندازه میباشد، مثلاً اگر آرگومان دوم (lookup_vector) یک Vector افقی با ۵۵ سلول باشد، آرگومان سوم (result_vector) هم باید یک Vector افقی با ۵۵ سلول باشد.”
به نظرم آرگومان اول در توضیح بالا اشتباهه، خود مقاله هم در ادامه همین جمله روی آرگومان دوم تاکید میکنه.
سلام، متشکرم از دقت شما و نظر شما، اصلاح شد. موفق باشید.
با سلام
یک نرم افزار دارم که خروجی اکسل تولید میکند. این خروجی رو به یک فایل اکسل میدیم و یک سری اطلاعات ازش استخراج میکنیم. مشکل اینجاست که ستونی که ما لازم داریم تو این خروجی تغییر میکنه مکانش. مثلا یک باز AL هست تو فایل روز دیگه میشه AS
فرمولی که من زدم اینه.
مشکل تو قسمت AL:AL هست که جابجا میشه
=VALUE(IFERROR(INDEX(‘تراز این ماه’!A:A;MATCH(“401010000”;’تراز این ماه’!AL:AL;0));0))
اگر همینجا AL:AZ رو میگرفت حل بود. اما فقط یک ستون رو میگیره
سوال اینه چطور میشه این ستونی که جابجا میشه رو مدیریت کرد تا اطلاعات اون ستون رو استخراج کرد.
سلام، امکان این کار در فرمول وجود ندارد و تنها یک ستون باید نام برده شود. به عنوان یک راه حل میتوانید از تابع MATCH برای پیدا کردن ستون مورد نظر استفاده کنید که احتمالاً داده های شما عنوان ندارند و این کار نیز ممکن نیست، بنابراین باید ستون را دستی انتخاب نمایید. موفق باشید.
سلام. من یه مشکل دارم کسی میتونه کمک کنه ؟
من یه فایل اکسل دارم حاوی ۳۱ شیت مختلف مربوط به روزهای ماه – که داخل هرکدوم اقلا ۳۰۰ داده ورودی وارد شده و حالا به دست من رسیده .
من میخام بدونم چطوری میتونم تو یه فایل جدید و جداگانه که خودم دارم فرضا داخل فایل قدیمی ۳۱ شیته من جستجو انجام بشه و درصورتی که تاریخی که من تو فایل جدید دادم رو پیداکرد در اونصورت بهم داده موجود در یک سطر و ستون خاص رو بده. ( داخل فایل ۳۱ شیته من یه سلول اختصاصی وجود داره که تاریخ اون رو نوشته شده – اگه بشه به اون رفرنس داد که از فایل جدید اون تاریخ جستجو بشه و درصورت پیداکردنش مقادیر موجود رو بهم بده)
از اونجا که فایل من قراره یه سری خروجی و نمودار تهیه کنه به همین خاطر میخام طوری باشه که مثلا من تو فایل جدید تاریخ رو بزنم ۹۷/۰۳/۳۱ و اون جستجو کنه تو بانک اطلاعاتی ۳۱ شیته من و مثلا سلول d2 رو از همون شیت که مربوط به ۹۷/۰۳/۳۱ میشه برای من استخراج کنه
از چه فرمولهایی میتونم کمک بگیرم/ کمک کنینی ممنون
سلام و درود
دو شیت اکسل دارم میخواستم بدونم چطوری میشه درایه های نظیر به نظیرشون را از هم کم کرد و در شیتی مجزا آورد ؟
ممنونم
سلام، در شیت سوم فرمول نویسی کنید و کافیست دو سلول مورد نظر را انتخاب و از هم کسر نمایید. موفق باشید.
سلام خسته نباشید
من یک فایل اکسل دارم و میخوام که وقتی در صفحه اول و در یک سلول ثابت یک عدد را تایپ کردم اندازه منتاظر با اون عدد از صفحه دیگری بیاد جای سلول دوم این صفحه اولی بشینه ، حدود ۱۳۰۰ عدد هست و فرمول دهی تک تک سخته . لطفا راهنمایی بفرمایید
سلام، انجام این کار تنها با کد نویسی یا ماکرو نویسی در کسل ممکن است و از طریق فرمول نویسی ممکن نمیباشد. موفق باشید.
سلام میشه من رو راهنمایی کنید؟
من ۴ ستون دارم که تو ۲ ستون کد کالا و میزان فروش و تو ۲ ستون کد کالا و میزان خریدشون ثبت شده
میخوام مقایسه کنه کدهایی که با هم یکی هستن رو پیدا کنه و مبلغ خرید که رو به روی اون ردیف نوشته شده رو در عددی ثابت ضرب کنه و تقسیم بر مبلغ فروش همون کد کالا کنه
چون تعداد کدها با هم برابر نیست
سلام، بله
برای انجام این کار از توابع Lookup & reference استفاده نمایید. توصیه میکنم با استفاده از ترکیت توابع INDEX و MATCH جستجو را بر اساس کدها انجام دهید و مبالغ را پیدا نمایید و سپس عملیات ریاضی مورد نظر را به راحتی فرمول نویسی کنید. موفق باشید.
عالی بود.
سلام دوستون اعداد ویک سل عدد دارم میخوام این سل را توستون اول اگه وجود داره از طریق VLOOKUP عدد متناظرش توستون دوم رابگذاره وگرنه با فرمول FORECAST میانیابی از دو ستون انجام بده و بگذاره ممون میشم راهنمایی بفرمایید
سلام، شما باید از ترکتیب توابع IFERROR و توابع INDEX و MATCH و FORECAST استفاده نمایید. با استفاده از تابع IFERROR در صورت که خروجی عملیات جستجو توسط توابع Lookup & Reference خطا باشد #N/A در این حالت از فرمول جدید برای محاسبات استفاده میکند. لطفاً ساختار این توابع را مطالعه کنید. موفق باشید.
با سلام.
من یه فرمول مثل vlookup می خوام که به جای اولین مقدار، آخرین مقدار مشابه را بهم بده. راهش چیه؟؟
با تشکر
سلام، راهی در محیط اکسل برای نوشتن چنین فرمولی بلد نیستم، تنها راه کد نویسی است.
سلام. من یه شیت اکس دارم که کد پرسنلی، نام پروژه های مختلف، سال، ماه و توضیحات توی اون ثبت شده.
توی داشبور می خوام زمانی که اسم پروژه و کد پرسنلی رو وارد کردم، توضیحات وارد شده مربوط به اون کد رو به تفکیک ماه برام نشون بده. برای مثال
پروژه ….
سال ۱۳۹۷
اردیبهشت —- حالا توضیحات نمایش داده بشه
خرداد ——- حالا توضیحات نمایش داده بشه
ممنون میشم راهنمایی بفرمایید. با تشکر
سلام، برای انجام این کار باید از توابع Lookup & Reference استفاده نمایید. به نظر من تابع INDEX به همراه تابع MATCH میتواند نیاز شما را بر طرف نماید. موفق باشید.
سلام
وقتتون بخیر
من دوتا فایل اکسل دارم با اطلاعات متفاوت.تو هردوتاش ستون کد ملی هم هست.میخوام هر دو فایلم یکی بشه و بقیه اطلاعات طبق کد ملی منطبق بشه وبشیته تو فایل جدید.
دقیقا باید چکارکنم؟
سلام، برای انجام این کار میتوانید از توابع جستجو و مرجع استفاده نمایید، به نظر من استفاده از ترکیب توابع MATCH و INDEX انتخاب خوبی است و حتماً میتوانید از آن جواب بگیرید. موفق باشید.
سلام
من یه فایل اکسل دارم توش تعداد زیادی عدد هست میخوام ببینم راهی وجود داره که من یک عدد دیگه ای که مد نظر دارم رو بفهمم کدوم یک از جمع دو عدد یا سه عدد یا چهار عدد به اون عدد من نزدیکه؟
مثال:تو اکسل من اعداد ۵ .۶ .۸٫۱۵٫ ۵۸ و … هست من میخوام بفهمم جمع کدوم چندتا از این اعداد میشه مثلا ۱۵۰ بعد خودش تشخیص بده که جمع این چهارتا عدد مثلا ۱۴۹ نزدیکترین عدد به عدد مورد نظر منه
ممنون میشم پاسخ بدید
سلام، خیر در اکسل چنین کاری نمیتوان انجام داد ولی برای این کار میتوان ماکرو نویسی انجام داد و در محیط ویژوال بیسیک کدی نوشت که برای ما این کار را انجام دهد، البته نوشتن این کد ساده نیست و زمان بر میباشد. موفق باشید.
با سلام و عرض خسته نباشید
میخواستم بدونم چطوری میشه در تابع Vlookup آخرین مورد جستجو برای شرط مورد نظر را برگرداند . در واقع من میخوام آخرین تاریخ خرید مشتری ها رو که بر اساس کد اونها جستجو میشه توی سطر متناظرشون در یک شیت دیگه داشته باشم .
ممنون میشم اگه راهنمایی کنید
سلام، انجام این کار با فرمول نویسی ساده ممکن نیست، روش عملکرد تابع VLOOKUP در اکسل به این صورت است که اولین داده متناظر با مقدار جستجو شده را یافته و برای کاربر نمایش میدهد بنابراین اولین داده یافته شده ممکن است داده مورد نظر شما نباشد و البته این امر به نحوه SORT کردن داده ها نیز بستگی دارد. با استفاده از فرمول نویس ماتریسی میتوانیم تمام داده های متناظر با مقدار جستجو شده را فراخوانی نماییم و در بین آنها تاریخ آخر را با استفاده از تابع MAX بدست آوریم.
موفق باشید.
سلام من یک ستون از کالاها دارم که برای هر کدام از کالاها یک شیت درست کردم یعنی ۲۰۰ کالا ۲۰۰ شیت میشه حالا تو این شیت ها یه قسمت داره به نام شرح کالا میخوام بدونم نام کالا ها رو به چه صورت در این شرح کالا بدون اینکه دستی بنویسم بزارم.
سلام، برای انجام این کار میتوانید اسم شیت های خود را لیست کنید، با استفاده از تابع SHEET در هر شیت میتوانید شماره آن شیت را بر اساس ترتیب شیت ها بدست آورید، بنابراین با استفاده از ترکیب تابع SHEET، لیست شیت ها و توابع جستجو و مرجع میتوانید فرمول نویسی مورد نظر را انجام دهید. موفق باشید.
با سلام عرض ادب
من ۲ شیت دارم که در هر دو شیت ستونی به نام کدملی وجود داره. من میخوام بین این دو ستون کدملی در دو شیت مقایسه انجام بدم و کدملی هایی که در شیت اول وجود دارند اگر در شیت دوم هم همان کدملی به صورت تکراری وجود داره ، کدملی تکراری در شیت دوم حذف شوند
ممنون میشم راهنماییم کنید !
سلام، برای انجام مقایسه از توابع جستجو و مرجع میتوانید استفاده کنید و متوجه شوید ایا کد ملی تکراری هست یا خیر، اما امکان پاک کردن اتوماتیم محتوای یک سلول در صورت رخداد یک شرط در اکسل و با فرمول نویسی وجود ندارد و برای این کار باید کد نویسی انجام دهید. موفق باشید.
با سلام من ئو تا شیت که دو عدد یکسان در پی وت های هرشیتم هست دارم. وقتی که از match استفاده می کنم عدد شیت اولو میخونه ولی عدد شیت دومو خیر . تو هر شیتی که فرمول بنویسم عدد اولی رو میخونهه ولی دومی رو نمیتونه . عدد دومی رو value کردم بازم مشکل حل نشد . ممنون میشم راهنماییم کنید . با وی لوک آپم همین مشکل رو دارم .
سلام، توابع جستجو و مرجه به این صورت عمل میکنند که اولین مورد پیدا شده را به عنوان جواب مپذیرند، بنابراین اگر شما مورد تکراری داشته باشید همیشه اولین مورد پاسخ است و بقیه موارد پیدا نمیشوند. برای انجام این کار استفاده از فرمول نویسی ماتریسی توصیه میشود که البته کمی پیچیده میباشد. موفق باشید.
سلام.من میخوام از بین ۸۰۰۰ تا داده جستجو کنه و اعدادی که بزرگتر از یه مقدار خاصی اند رو پیدا کنه و بجاشون یه عدد دیگه رو جاگذاری کنه
یعنی مثلا عددای بزرگتر از ۵۰ رو پیدا کنه و بجاشون صفر قرار بده
سلام، برای انجام ان کار نیاز به ماکرو نویسی در اکسل دارید. با فرمول نویسی قابل انجام نیست. موفق باشید.
درود
من میخوام فروش ماه های اول و سوم و چهارو ششم سال های ۹۵ و ۹۶ را به من نشان دهد.
هرکاری کردم به نتیجه نرسیدم.
داده ها: ۶ماه اول سال(فروردین تا شهریور)، فروش سال ۹۵ و فروش سال ۹۶ همین ماه ها.
ممنون از پاسخگویی و مطالب مفیدتون
سلام، پاسخ به این سوال به نحوه تعریف داده های شما دارد، اما فکر میکنم شما میبایست از Sumif یا SUMIFS استفاده نمایید. با استفاده از این توابع میتوانید اعداد را با در نظر گرفتن یک یا چند شرط خاص جمع نمایید. موفق باشید.
سلام
من می خواستم مثل ابزار جستجو در اکسل با وارد کردن یک کلمه کل جدول را بگردد و تمام ردیف هایی که کلمه را دارا هست نمایش بده البته با ماکرو نوشتم ولی می خوام کلمه مورد نظر بخشی از عبارت یه سلول بود را پیدا کند ممنون میشم راهنماییم کنید
سلام.من میخوام در یک محدوده مشخص ( سطر و ستون ) دنبال شماره دستگاه بگرده و هر محصولی که با این دستگاه تولید شده رو برام ، به ردیف ، در سطر های پشت سر هم بنویسه.آیا امکانش در اکسل وجود داره؟
سلام، خیر، در اکسل هر فومول یک خروجی دارد که در یک سلول نوشته میشود مگر اینکه فرمول نویسی ماتریسی باشد. با فرمول نویسی ماتریسی میتوان کار مورد نظر شما را انجام داد اما با فرمول نویسی معمولی خیر، و راحت ترین راه نیز کد نویسی در محیط ویژوال بیسیک در اکسل است. موفق باشید.
با سلام
من در یک شیت دو ستون اطلاعات دارم یکی نوشتاری و ستون بعد عدد هست تو شیت دیگه هم همین دو ستون رو میخوام فراخوان بدم بطوریکه ستون عددی براساس بزرگ به کوچک سورت بشن تا اینجا اوکی شده میخوام بدونم فرمولی داریم که بعد از سورت کردن ستون اعداد ستون نوستار رو هم بر اساس سورت اعداد سورت کنه
سلام، اگر هر دو ستون در کنار یکدیگر هستند و یا به صورت Table میباشند، با Sort کردن یک ستون، ستون دیگر نیز Sort میشود. اگر هم میخواهید اول sort کنید و سپس با استفاده از توابع جستجو و مرجع مانند MATCH و index و یا VLOOKUP مقادیر مربوطه را به شیت جدید منتقل نمایید. موفق باشید.
سلام و وقت بخیر
آیا فرمولی هست که درخواست زیر را انجام بدهد
یک ستون داریم که شماره پروژه ها است
یک ستون دیگر هم تاریخ کارهای مختلف انجام شده مربوط به هر شماره پروژه است
بعنوان مثال برای شماره پروژه ۱۰۰ طی مدت ۳۰ روز با تاریخ های متفاوت در ستون تاریخ ورودی دیتا داریم حال میخواهیم در یک سل شماره پروژه را بنویسیم و با توجه به آن شماره زمان شروع آن پروژه را(زودترین زمان ثبت شده) در سل مقابل نشان دهد
با سپاس فراوان
سلام، اگر تارخ اولیه در جدول داده ها بالاترین داده باشد با توابع جستجو و مرجع میتوان آن را پیدا کرد، اما اگر ترتیب داده ها مشخص نیست ابتدا باید داده ها برای ان پروژه خاص فیلتر شوند و سپس مینیمم تاریخ پیدا شود، advanced filter برای انجام این کار میتواند مورد استفاده قرار گیرد، البته فرمول نویسی ماتریسی هم بسیار مفید است که را پیچیده ای است و نهایتا بهترین راه کد نوسی ویژوال بیسیک میباشد.
سلام
روش پیدا کردن عدد دوم یا سوم یک چند رقمی یک سلول در سلول دیگری آورده شود.
مثلا اگر سلولی حاوی عدد۱۲۳۴۵۶ باشد. رقم دوم از راست که ۵ می باشد در سلول دیگری گزارش داده شود.
با تشکر
سلام، با استفاده از فرمول نویسی ساده این کار را انجام دهید. موفق باشید
با تشکر
لطفا راهنمایی بیشتر بفرمایید
سلام
ضمن تشکر از سایت خوبتون و آموزشهای کاربردی آن
من دوتا فایل ایکسل دارم از فایل اول بعنوان مرجع استفاده می کنم و از فایل دوم بعنوان داده های اولیه. در فایل اول در برخی از سولها با استفاده از عبارت مساوی یک سلول از فایل دوم را به فایل اول ربط داده ام . یعنی تا زمانی که هر دو فایل باز هستند هر تغییری که در فایل دوم انجام میدهم در فایل اول نیز اعمال می شود ولی وقتی کامپیوتر را خاموش می کنم و دوباره بر میگردم و در فایل دوم تغییر ایجاد می کنم دیگر در فایل اول اعمال نمی شود شما چه راهکاری پیشنهاد می کنید؟
البته وقتی از یک فایل با چندین شیت استفاده می کنم مشکلی ندارم ولی بنا به دلایلی میخوام تغییر در یک فایل در فایل دیگر اعمال شود ممنون می شم راهنماییم کنید. با تشکر
سلام، هر دو فایل باید همزمان باز باشند.
با سلام
من میخواهم در یک ستون یک متن و کلمه ایی را جستجو کنم و فقط شماره سلول اون متن رو به من بده
مثل من میخواهم در ستون A یک جدول دنبال کلمه “سلام” بگردم
و به من اطلاع بده که کلمه “سلام” در سلول A44 می باشد.
سلام، برای انجام این کار میتوانید از ترکیب توابع ADDRESS و MATCH استفاده نمایید. موفق باشید.
سلام بنده می خواهم با استفاده از تابع VLOOKUP ، محتوای یک سلول که حاوی یک هایپرلینک است برگردانم، ولی وقتی محتوای سلول را پیدا می کند دیگر قابلیت هایپرلینکی خود را از دست می دهد و قابل کلیک کردن نیست و موس به شکل دست، نمی شود. لطفا راهنمایی فرمائید. با تشکر
سلام، بله، برای انجام این کار از یک تابع Hyperlink استفاده کنید و در قسمت آدرس این تابع از تابع VLOOKUP خود برای پیدا کردن آدرس مورد نظر استفاده نمایید. موفق باشید.
توضیحاتتون عالی بود. مرسی
متشکرم، موفق باشید.
سلام
من در اکسل یک جدولی دارم که حاوی دو ستون هست و می خوام داده های متناظر با مواردی که عبارت yes روبروی انها هست رو برام لیست کنه. مشکلم اینجاست که عبارت yes ممکنه که ده بار اورده شده باشه و من لیست اون ده مورد رو می خوام. با دستور های معمول اکسل فقط اولین مورد رو پیدا می کنه و دیگه ادامه نمیده. ممنون میشم راهنمایی بفرمایید
سلام، دو راه دارید، یا اینکه فرمول نویسی ماتریسی انجام دهید و یا اینکه کد نویسی کنید، در فرمول نویسی عادی هر فرمول در یک سلول نوشته میشود و تنها میتواند دارای یک خروجی باشد. موفق باشید.
سلام و خسته نباشی
ستون ۱ ستون ۲
۱ ۵
۱ ۶
۲ ۴
۳ ۷
لطف میکنید راهنمایی کنید اگر عدد ۱ (در ستون۱ ) متناظرش در ستون ۲ که ۵ و ۶ هستش رو بخوام از چه دستوری باید استفاده کنم
تشگر
سلام، در فرمول نویسی در اکسل هر فرمولی در یک سلول نوشته شود یک خروجی بیشتر نمیتواند داشته باشد بنابراین امکان ندارد که بتوان در یک سلول فرمولی نوشت که دو خروجی ایجاد کند. برای این کار باید فرمول نویسی ماتریسا انجام دهید. موفق باشید.
درود بر شما
زنده باشید و تندرست
متشکرم، موفق باشید.
سلام. خسته نباشید
من میخام یک سری اطلاعات متنی در یک سلول وارد کنم که اطلاعاتش در یک جدول موجوده. میخام در نتیجه سلول متناسب با سلول متنی من نمایش بده. لازم به توضیح است داده و نتیجه هردو متنی هستن. روبروی هم هم نیست. از تابع lookup استفاده کردم اما نتیجه درست نمیده. از چه تابعی استفاده کنم ؟؟؟
سلام متشکرم، از ترکیب توابع MATCH و INDEX استفاده کنید. به نظرم بهترین جواب رو میگیرید. موفق باشید.
سلام
سپاس از مطالب ارزشمند شما
سوالی از حضورتون دارم
۳ شیت اکسل دارم که در شیت سوم با وی لوک آپ مقادیری از شیت اول پیدا می کنم و از مقدار شیت دوم کسر می شود.
چطور می توانم مقدار نتیجه به دست آمده را جایگزین رقم قبلی در شیت اول بشود؟
سلام، انجام این کار تنها با ماکرو نویسی ممکن است. موفق باشید.
سلام . می خوام از یک لیست پرداختی یه فرد چند پرداخت داره را تو یک جدول لیست کنم باید از چه فرمولی استفاده کنم ؟
سلام، برای انجام این کار از ابزار Advanced Filter استفاده نمایید. موفق باشید.
سلام خسته نباشید. یه سوال داشتم. می خواستم بدونم اگه برای سلول شرطی بذاریم که درصورت وارد کردن عددی کم تر از A، اجازه ثبت اون رو نده و پیغام خطا بده، باید از چه تابعی استفاده کنم و اصلا یه چنین تابعی بطور پیشفرض در اکسل هست؟ و اینکه در اکسل چجوری بگیم فلان مقدار رو در فلان سلول قرار بده.
ممنون از شما
سلام، برای این کار باید از ابزار Data Validation استفاده نمایید.
در اکسل از طریق فرمول نویسی نمیتوان تعیین کرد که فلان عدد در فلان سلول باشد، در اکسل هر فرمولی که در هر سلولی نوشته میشود میتواند داده همان سلول را تعیین کند و نه سلول های دیگر را.
این کار با ماکرو نویسی در اکسل قابل انجام است.
سلام
من تابعی رای جستجو می خوام که تمام سلول های تکراری رو نشون بده
مثل ترکیبی از تابع large+vlookup که با آرگومانی داشته باشه که بشه دومین عدد بزرگ در بین تکراری ها رو انتخاب کنه
ممنون از سایت مفیدتون
سلام، انجام این کار در محیط اکسل ممکن نیست، هر تابع فقط یک خروجی دارد، نمیتوان فرمولی نوشت که خروجی آن چند آدرس باشد. برای انجام این کار در محیط اکسل تنها راه استفاده از فرمول نویسسی ماتریسی است، که میتوان مشخص کرد کدام سلول ها تکراری هستند و در دومین عدد بزرگ در بین تکراری ها را از خروجی فرمول ماترسی استخراج کرد، فرمول نویسی پیچیده ای دارد.
راه ساده تر استفاده از ماکرو نویسی است که البته نیاز به دانش کد نویسی دارد.
موفق باشید.
با سلام و تشکر برای این همه وقت گذاشتن
من یه فایل دارم که دو ستون داره، ستون اول نام کالا است که تکرار داره و ستون دوم نام کشور سازنده کالا است که اونم تکرار درش هست
برای مثال خودکار ساخت چین دارم ساخت ژاپن و استرالیا هم دارم و کالای های دیگه
حالا باید مشخص کنم خودکار ساخت چه کشور هایی دارم یعنی بهم خروجی بده: چین ژاپن استرالیا
میشه راهنمایی کنید از چه طریق یا فرمولی میشه به این نتیجه رسید
سلام، خواهش میکنم، برای انجام این کار شما باید از ابزار Advanced Filter استفاده کنید، Criteria مورد نظر را تعریف کنید و جدول موجود را فیلتر نمایید، البته ابزار فیلتر هم به شما این امکان را میدهد، اما ابزار Advanced Filter میتواند برای فیلتر و انتقال داده ها به صورت همزمان استفاده شود. موفق باشید.
سلام و درود
من در یک فرم به صورت روزانه سه محصول دارم که در یک شیفت کاری و توسط یک گروه به سرپرستی یک نفر تولید می شوند
مثلا : باتری قلمی ۲۰۰ عدد ، باتری موتوری ۳۰۰ عدد و باتری خودرو ۵۰۰ عدد
که ممکن است هر محصول تولید شده چند عدم انطباق داشته باشد
مثلا : باتری قلمی ۳ عدم انطباق ۱- دفرمگی ۲- جوش ناقص ۳- افت ولتاژ
باتری موتوری ۲ عدم انطباق ۱- افت ولتاژ ۲- سوختگی پوسته
و نهایتا باتری خودرو ۴ عدم انطباق ۱- نشتی از درب ۲- افت ولتاژ ۳- خدمات ۴- سوختگی پوسته
سوال چگونه من گزارش آماری در اکسل تهیه کنم که در یک روز یا شیفت یک نفر ( سرپرست ) چه تعداد باتری تولید کرده است و چه تعداد خرابی در شیفت او رخ داد کرده و خلاصله گزارشات مربوط به او چگونه باید قید شود .
سپاسگذارم
سلام، برای تولید این گزارش شما ابتدا نیاز به ثبت داده های خود در یک پایگاه داده دارید، البته پایگاه داده در اینجا صرفاً یک Table است که دارای ستون های کافی باشد و تمام پارامترهای مورد نظر را در بر بگیرد، در صورت وجود چنین جدولی، استخراج گزارشات مورد نظر به راحتی توسط ابزاری مانند Pivot Table یا Pivot Chart ممکن میباشد. موفق باشید.
سلام خسته نباشید من یه شیت دارم که لیست جنسها با تعداد و قیمتهاش وجود داره و بعد مدتی تعداد و قیمت جدید میاد برای بدست آوردن آخرین قیمت باید چکارکنم ممنون از راهنماییتون
سلام، برای این کار باید داده های خود را به صورت جدول مرتب نمایید، پس از مرتب سازی، میتوانید با استفاده از ابزاهایی مانند Filter یا Advanced Filter آخرین قیمت را برای هر کالای مورد نظر مشاهده نمایید. موفق باشید.
سلام – من یه مشکلی با این دستور دارم – میخوام این دستور همزمان از دو فایل مختلف انجام بشه – یعنی یه فایل مربوط به فرمول نویسی و ارائه گزارش باشه و فایل بعدی داده هام باشه – حالا میخوام از دستور Vlookup استفاده کنم به طوری که جستجوی من بر مبنای عدد هست ولی فایل داده من اعداد آن با فرمت General موجوده و باید هر دفعه که فایل داده رو از سیستم دریافت میکنم به صورت دستی اون و به Number تبدیلش کنم – میخوام بدونم آیا روشی هست که بتونم داخل همین دستور از دستوری استفاده کنم که خودش همونجا دنبال عددی بگرده که تبدیل شده باشه ؟؟ یعنی نیاز نباشه من هر دفعه اون ستون رو تبدیل به عدد کنم و خودش دنبال داده عددی اون ستون بگرده ؟؟ ممنون میشم جواب بدید
سلام، با استفاده از تابع VALUE میتوانید داده ها را تبدیل به عدد نمایید و فرمول خود را بر روی اعداد بنویسید، موفق باشید.
سلام
وقت بخیر
داخل محیط ماکرو نویسی من از تابع vlookup استفاده میکنم ولی مشکلم اینه که وقتی این تابع مقداری رو داخل جدول من پیدا نمیکنه و خروجی تابع ارور میشه برنامه من متوقف میشه
به عنوان مثال
dim saeed_score as integer
saeed_score = worksheetFunction.vlookup()
حالا وقتی تابع مقداری رو پیدا نمیکنه و مقدار ارور رو برمیگردونه چون متغیر من از نوع اینتیجر هست برنامه من متوقف میشه
میخواستم راهنمایی بفرمایید که چطور این مشکل رو حل کنم؟
سلام، برای عبور از خطاها و مدیریت آنها از تکنیک های Error Handling استفاده کنید، به عنوان مثال OnError Resume Next . موفق باشید.
سلام وقت بخیر باتشکر از مطالب مفیدتون لطفا بنده رو راهنمایی بفرمایید من میخوام یک جدول داخل ورد رو بیام توی اگسل و اطلاعات داخل جدول را به صورت رکورد به رکورد فیلد هایی هست داخل هر رکورد که میخولم محاسبات عددی انجام بدم روی یک فرمول چه طور میتونم این کار رو انجام بدم
درود، برای انجام این کار من ابتدا کپی کردن ساده رو امتحان میکنم و معمولا جدول به صورت کامل وارد اکسل میشه، البته ظاهرش متفاوت خواهد بود که مهم نیست، یا اینکه فایل Word رو به صورت Note یا CSV ذخیره کنید و در اکسل به صورت DATA وارد کنید که در این صورت جداولی مرتب خواهید داشت، حتمی میتوانید ابتدا جدول خود را از Word در یک فایل Note کپی نمایید و سپس از Note وارد اکسل کنید که این روش به مراتب بهتر از روش اول خواهد بود.
سلام و تشکر فراوان بابت پاسخگویی های دقیق
یک ستون دارم که “کد کالا” را در آن وارد میکنم و در ستون کناری “تعداد کالا” که بصورت پیشفرض ۱ گذاشتم.
آیا این قابلیت وجود داره که اگه کدی تکراری وارد شد ، بطور خودکار سلول پاک بشه و در مقابلِ اولین تکرار تعداد افزایش پیدا کنه.
یعنی اگه بارکد کالای الف توسط بارکدخوان ۲بار وارد شد ، بجای اینکه ۲ تا سطر یکسان اشغال بشه ، بصورت خودکار تبدیل به یک سطر بشه ولی تعداد افزایش پیدا کنه.
اگر اکسل چنین قابلیتی داره لطفا بگین کدوم سرفصل رو باید بخونم.
مرسی
سلام، خیر، این کار در اکسل به صورت منطقی ممکن نیست، به این دلیل که نمیتوان در اکسل فرمولی نوشت که وردی یه سلول را پاک کند. شما میتوانید از ساختار Table و Pivot Table استفاده نمایید. به این ترتیب هر داده جدید در یک سطر جدید وارد میشود اما در Pivot Table شما همیشه گزارش را به صورت به روز شده و خلاصه شده مشاهد میکنید. همچنین میتوانید از ماکرو نویسی استفاده کنید و کدی بنویسید که هر داده جدید را تحلیل کرده و موجودی را به روز نماید. موفق باشید.
سلام
یک لیستی از نمرات دانش آموزان در یک درس دارم و می خوام مثلا ۲ نمره تشویقی به همه بدهم ولی نمی خوام با احتساب این ۲نمره، مجموع نمرات دانش آموزی بیشتر از ۲۰ نشون داده شود. یعنی اگر ۲۰+۲ کردم باز ۲۰ رو نشون بده
سلام، برای این کار باید فرمول نویسی را با استفاده از دستور شرطی IF انجام دهید، به صورتی که اگر حاصل جمع بیش از ۲۰ شد، عدد ۲۰ را در سلول قرار دهد.
IF(A>20,20,A)
سلام
هنگام استفاده از vlookup همیشه اولین مقداری رو که پیدا میکنه رو برمیگردونه. چطور میشه مقادیر بعدی رو دریافت کرد؟
یعنی یک کدملی دارم که در بانکهای مختلف حساب داره. اما هنگام استفاده ازین دستور فقط اولین بانک رو برمیگردونه.
واضح تر میشه : یک جدول دارم که کد ملی و اطلاعات شخصی رو داره و یک حدول دارم که شامل کد ملی و نام بانک میشه. حالا چطور میتونم لیست بانکهایی که هر کدملی حساب داره رو جلوی اطلاعات شخصی نشون بدم؟
با استفاده از توابع میتونم تعداد حساب ها رو بدست بیارم اما نام بانک هارو میخوام.
ممنون میشم راهنمائی کنید
سلام، این نکته از خواص تابع VLOOKUP است و بنابراین نمیتوان با این تابع مقادیر تکرار شده را پیدا کرد و خروجی این تابع اولین مقدار پیدا شده میباشد. برای این کار باید فرمول نوسی ماتریسی انجام دهید و در وهله اول تمام مقادیر متناظر با کد ملی مورد نظر را پیدا کنید، سپس با ترکیب توابعی مانند CHOOSE، INDEX و VLOOKUP میتوانید در لیست پیدا شده داده مورد نظر را جستجو نمایید.
سلام و وقت بخیر یک سئوالی داشتم برای اینکه بتونم بین دو تا تاریخ مقدار موجودی یک کالا رو بدست بیارم باید از چه فرمولی در اکسل استفاده کنم که بین زمان هایی که من مشخص میکنم ، موجودی را نشان دهد ؟
با سلام خسته نباشید
در یک فایل اکسل تعداد زیادی رکورد دارم که مربوط به بیماران مراجعه کرده به مراکز درمانی است. یک ستون مربوط به نام مرکز درمانی است.
باید در این لیست تعداد بیماران مراجعه کرده به مراکز درمانی دولتی را شمارش کنم
لیست مراکز درمان دولتی نیز موجوده
راهی هست بدون کد نویسی این کار انجام بشه؟
ممنونم