در مقاله ی فرمول نویسی در اکسل، با اصول نگارش فرمول ها و ساختمان فرمول و اجزای فرمول در اکسل آشنا شدیم، همچنین در این مقاله توابع موجود در اکسل را دسته بندی کردیم. توابع جستجو و مرجع در اکسل یا Lookup & Reference Functions مانند توابع ریاضی و توابع متنی که پیشتر با آنها آشنا شدیم، از جمله توابع عمومی اکسل محسوب میشوند که تمام کاربران اکسل به آنها احتیاج دارند، توابع جستجو و مرجع در فایل های اکسلی که محتوی بانک های اطلاعاتی میباشند بسیار کاربرد دارد، برخی از توابع جستجو و مرجع در اکسل مانند توابع VLOOKUP و HLOOKUP بسیار معروف و پرکاربرد هستند. به علت طولانی شدن مقالات توابع جستجو و مرجع در سه مقاله مورد بررسی قرار میگیرد.
اکیداً توصیه میشود پیش از مطالعه ی این نوشته، مقاله ی فرمول نویسی در اکسل را به منظور آشنایی با ساختمان و اجزای فرمول ها و همچنین آشنایی با انواع آرگومان ها و عملگرها در اکسل مطالعه نمایید.
همواره در فرمول ها و توابع اکسل میتوان از سلول های دیگر به عنوان آرگومان استفاده نمود، در مقاله ی آدرس دهی در اکسل با روش های اشاره به سلول ها در فرمول نویسی ها و توابع اکسل آشنا میشویم و در مقاله ی نامگذاری محدوده در اکسل یاد میگیریم که نام دلخواه و متناسب با محتوای هر سلول را برای اشاره به آن سلول در فرمول ها و توابع در اکسل استفاده نماییم، لذا خواندن این مقالات نیز پیش نیاز یادگیری توابع و درک فرمول نویسی در اکسل میباشد.
معرفی توابع جستجو و مرجع در اکسل
توابع جستجو و مرجع در اکسل یا Lookup & Reference Functions توابعی هستند که در بانک های اطلاعات در اکسل به صورت گسترده مورد استفاده قرار میگیرند، کارهایی مانند جستجو در بانک های اطلاعاتی، ارجاع دادن و بسیاری موارد دیگر از جمله کاربردهای توابع جستجو و مرجع در اکسل میباشند. در این مقاله برخی از توابع “مرجع” مورد بررسی قرار میگیرند و در مقاله ی دوم باقی توابع “مرجع” را بررسی میکنیم. با توابع “جستجو” شامل VLOOKUP، HLOOKUP و LOOKUP در مقاله ی سوم آشنا خواهیم شد.
شکل زیر خلاصه ای از توابع جستجو و مرجع در اکسل را نشان میدهد:
توابع مرجع در اکسل
این دسته از توابع به منظور ارجاع به محدوده یا مشخصه ای از یک محدوده مورد استفاده قرا میگیرند، در ادامه با این توابع آشنا میشویم.
توابع COLUMN و COLUMNS در اکسل
از تابع COLUMN در اکسل میتوان شماره ستون یک سلول را به دست آورد، سینتکس این تابع به صورت زیر است:
COLUMN (reference)
تنها آرگومان این تابع نام سلول است، به عنوان مثال اگر در عبارت فوق بجای reference بنویسیم D10 خروجی تابع عدد چهار خواهد بود یعنی شماره ستون سلول D10.
اگر مجموعه ای از سلول ها (Range) را بجای reference قرار دهیم خروجی تابع شماره سمت چپ ترین ستون خواهد بود. و اگر جای reference را خالی بگذاریم خروجی تابع برابر شماره ستون سلولی که تابع در آن نوشته شده است میباشد.
تابع COLUMNS مخصوص آرایه ها میباشد، با استفاده از این تابع میتوان تعداد ستون های یک آرایه را بدست آورد.
توابع ROW و ROWS در اکسل
دقیقاً مانند تابع COLUMN عمل میکند ولی این تابع اطلاعات ردیف (Row) را به ما میدهد. از تابع ROW در اکسل میتوان شماره سطر یک سلول را به دست آورد، سینتکس این تابع به صورت زیر است:
ROW (reference)
تنها آرگومان این تابع نام سلول است، به عنوان مثال اگر در عبارت فوق بجای reference بنویسیم D10 خروجی تابع عدد ده خواهد بود یعنی شماره سطر سلول D10.
اگر مجموعه ای از سلول ها (Range) را بجای reference قرار دهیم خروجی تابع شماره بالا ترین سطر خواهد بود. و اگر جای reference را خالی بگذاریم خروجی تابع برابر شماره سطر سلولی که تابع در آن نوشته شده است میباشد.
تابع ROWS مخصوص آرایه ها میباشد، با استفاده از این تابع میتوان تعداد سطر های یک آرایه را بدست آورد.
تابع ADDRESS در اکسل
از تابع ADDRESS در اکسل برای بدست آورد آدرس یک سلول در Worksheet استفاده میشود، Syntax این تابع به صورت زیر است:
ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])
- در ساختار این تابع، آرگومان اول و دوم آرگومان های اجباری و عدد هستند و به ترتیب شماره سطر و ستون سلولی هستند که میخواهیم آدرس آن را بدست بیاوریم، آرگومان سوم، چهارم و پنجم آرگومان های اختیاری هستند.
- آرگومان سوم در مورد نسبی یا مطلق بودن آدرس سلول تعیین تکلیف میکند. این آرگومان میتواند خالی بماند و یا یکی از اعداد ۱، ۲، ۳ یا ۴ باشد، اگر خالی یا عدد یک باشد آدرس دهی به صورت مطلق خواهد بود و اگر عدد ۴ باشد آدرس دهی به صورت نسبی میباشد، اگر میخواهیم آدرس سلول به نحوی بیان شود که سطر به صورت مطلق و ستون نسبی باشد، عدد ۲ و برای برعکس این حالت عدد ۳ را بجای آرگومان سوم قرار دهید.
- آرگومان چهارم هم یک آرگومان اختیاری و یک مقدار منطقی (Logical Value) میباشد، اگر این آرگومان خالی بماند و یا True باشد، آدرس سلول به فرمت آشنای A1 یعنی شماره سطر عدد و شماره ستون حرف بیان میشود و اگر این آرگومان False باشد فرمت بیان آدرس سلول به صورت R1C1 میشود که سطر و ستون هر دو عدد هستند.
- و نهایتاً، آرگومان پنجم نام شیت در برگیرنده ی سلول را در صورتی که تمایل داشته باشیم در آدرس سلول ذکر شود را مشخص میکند.
به مثال زیر توجه کنید:
ADDRESS(8;13;3;FALSE;”Sheet1″)= Sheet1!R[8]C13
ADDRESS(8;13;3;TRUE;”Sheet1″)= Sheet1!$M8
برای نشان دادن مطلق بودن آدرس ها در فرمت R1C1 از قرار دادن عدد سطر یا ستون در براکت استفاده میکنیم.
تابع AREAS در اکسل
در اکسل، Area به یک محدوده ی پیوسته از سلول ها شامل یک یا چندین سلول که در حالت انتخاب قرار دارد گفته میشود، مثلاً سلول A1 به تنهایی میتواند یک Area باشد، همینطور محدوده ی A1:C5 هم اگر توسط کاربر انتخاب شود یک Area است. تابع AREAS تعداد Area های یک محدوده (reference) را مشخص میکند. سینتکس این تابع به صورت زیر است:
AREAS (reference)
در عبارت بالا reference تنها میتواند یک Area باشد، برای انتخاب چند Area باید از پرانتز استفاده کنیم، به مثال های زیر توجه نمایید:
AREAS(B2:D4) =1
AREAS((B2:D4,E5,F6:I9)) = 3
AREAS(B2:D4B2) = 1
در مثال سوم دو Area وجود دارند، اولی B2:D4 و دومی B2 است، در واقع منظور از این نوع نوشتن Area ها بدست آوردن Area فصل مشترک دو Area اصلی میباشد که یک Area است و خروجی این تابع عدد یک میباشد، اگر دو Area قرار گرفته به عنوان reference با هم فصل مشترک نداشته باشند، خروجی تابع خطای !NULL# خواهد بود.
تابع CHOOSE در اکسل
سینتکس تابع CHOOSE به صورت زیر است:
CHOOSE(index_num,value1,value2,…)
در عبارت بالا، آرگومان اول عددی است بین ۱ و ۲۵۴ که در واقع شماره مشخصه (Index Number) مربوط به یکی از ۲۵۴ مقدار داده شده به تابع در آرگومان های دوم تا دویست و پنجاه و پنجم میباشد.
آرگومان های Value1، Value2،… تا Value254 میتواند یک فهرست دلخواه کاربر باشد (مانند نام پرسنل، شماره اقلام، آدرس سلول و…) که به ترتیب شماره مشخصه (Index Number) یک تا ۲۵۴ را به خود اختصاص میدهند، خروجی تابع CHOOSE آیتم مربوط به Index_num مورد نظر کاربر میباشد.
اگر عدد قرار گرفته در آرگومان اول کمتر از یک یا بیشتر از تعداد Value ها باشد، خروجی تابع خطای !VALUE# خواهد بود. و اگر عدد Index_num یک عدد اعشاری باشد تنها ارقام صحیح آن عدد در نظر گرفته میشود.
به مثال های زیر توجه کنید:
SUM (CHOOSE (2,A1:A10,B1:B10,C1:C10)) = SUM(B1:B10)
CHOOSE (2,A1:A10,”Excelpro”,۱۳)) = Excelpro
تابع INDEX در اکسل
این تابع دو حالت آرایه ای (Array) و Reference دارد، حالت اول مربوط به آرایه ها میباشد که در مورد آن بعداً بحث خواهیم نمود، سینتکس تابع INDEX به صورت زیر است:
INDEX(reference,row_num,column_num,area_num)
- این تابع چهار آرگومان دارد، reference که آرگومان اول میباشد آدرس یک یا چند محدوده یا Area میباشد، توجه داشته باشید مانند آنچه در تابع AREAS یاد گرفتیم در اینجا هم اگر بخواهیم به چند محدوده یا Area به صورت همزمان اشاره کنیم نیاز به استفاده از پرانتز داریم.
- آرگومان دوم و سوم به ترتیب یک شماره سطر و ستون مربوط به reference میباشد که کاربر میخواهد به آن اشاره کند، مثلاً فرض کنید شما محدوده ای B2:E8 را به عنوان reference به تابع معرفی کرده اید، حال میخواهید مقدار سلول C5 را بدانید، بجای آرگومان دوم عدد ۴ و بجای آرگومان سوم عدد ۲ را قرار دهید (زیرا در محدوده ی انتخاب شده B2:E8 سلول C5 در سطر چهارم و در ستون دوم محدوده قرار دارد)، در ضمن اگر reference معرفی شده تنها دارای یک سطر یا ستون باشد میتوانید آرگومان دوم یا سوم را خالی بگذارید.
- همانطور که گفته شد، میتوانید چند Area را در محل reference به تابع معرفی کنید، در این صورت آرگومان چهارم تعیین میکند کدام Area مورد نظر شما است، به عنوان مثال اگر سه Area به تابع معرفی کنید و آرگومان چهارم عدد دو باشد، تابع در Area دوم به دنبال سطر و ستون مورد نظر شما میگردد.
اگر شماره سطر و سلول قرار گرفته به عنوان آرگومان دوم و سوم در محدوده ی Area مورد نظر نباشد خروجی تابع برابر خطای !REF# خواهد بود.
سلام، در یک شیت اکسل می خواهم به عنوان مثال ببینم نام احمد در کدام سطر وجود دارد. یک فرمولی که وقتی نام را بدهیم در جواب شماره سطر را به مابدهد. با تشکر لطفا جواب را برایم ایمیل نمایید با تشکرmoezzi55@gmail.com
سلام، از تابع ROWS برای مشخص شدن شماره سطر هر سلول استفاده نمایید و تابع VLOOKUP را برای جستجوی عبارت مورد نظر به کار ببرید، به این ترتیب میتوانید با پیدا شدن عبارت مورد نظر ستونی را که محتوی شماره سطر آن عبارت میباشد قرائت کنید.
با عرض سلام و تشکر از مطالب مفیدتون
اگر بخواهیم داده های یک ستون در چند شیت درگر جستجو شود و آدرس سلول و نام شیت را نشان دهد از چه فرمولی می تونیم استفاده کنیم.
لطفاً با جزئیات توضیح دهید.
با تشکر فراوان
برای جستجو در فایل اکسل به صورتی که شما مد نظر دارید، دو تابع VLOOKUP و HLOOKUP و یا LOOKUP میتواند مورد استفاده قرار گیرد، البته توابع CHOOSE و INDEX هم میتواند مفید باشد، برای اطلاع از نام شیت و آدرس سلول میتوانید از توابعی مانند ADDRESS یا CELL استفاده کنید، البته اینطور که شما نوشتید فکر میکنم ماکرو نویسی بتواند مناسب ترین راه برای شما باشد. در بخش پایگاه دانش سایت اسم توابع را جستجو کنید و مقلات مرتبط را مطالعه نمایید، موفق باشید.
سلام مرسی از وب سایت خوبتون.
میخوام در اکسل هر گاه مبلغ حقوق نفرات را به عدد وارد کردم معادل ان را برایم به فارسی بنویسید. مثلا: ۲/۳۰۰/۰۰۰ در روبرو بنویسد دو میلیون و سیصد هزار ریال
ممنون میشم راهنماییم کنید
سلام، خواهش میکنم، برای انجام این کار تابعی در اکسل وجود ندارد، شما باید در قسمت کد نویسی ویژوال بیسیک تابعی بنویسید که این کار را انجام دهد. میتوانید در قسمت پرسش و پاسخ این سایت برای نوشتن تابع مورد نظر درخواست کمک کنید. دوستان علاقمند میتوانند کد مورد نظر را برای شما بنویسند. موفق باشید.
فانکشن مربوطه (persian function for excel)رو دانلود کرده addنمائید واز تابع abh استفاده کنید
سلام
من چند دسته داده در یک فایل دارم که هر دسته با یک ردیف فاصله از هم جدا شده است. میخواهم از بین هر دسته تعدادی داده را انتخاب کنم و در یک صفحه جدید کپی کنم که دوباره باید قسمت های انتخاب شده از هر دسته یک ردیف با هم فاصله داشته باشد. تعداد این دسته ها ۱۱۴۰ عدد است و توانایی کپی کردن تک تک آنها را ندارم. آیا شما می توانید به من راهی پیشنهاد کنید؟
با تشکر
سلام، قطعاً کاری که میخواهید انجام دهید با استفاده از کد نویسی قابل اجرا هست. اما اینکه کد نویسی مربوط تا چه حد پیچیده باشد بستگی به داده ها و عملیات مورد نظر دارد. اگر نخواهید از کد نویسی استفاده کنید شاید بتوان در اکسل هم این کار را انجام داد که البته نیاز به بررسی دارد.
موفق باشید.
سلام
آیا تابعی وجود ندارد که با مشخص کردن شماره سطر و ستون دو گوشه از محدوده بتوان آدرس آن محدوده را بصورت مثلاً A1:E6 استخراج نمود؟
ممنون
سلام، خیر وجود ندارد. میتوانید این تابع را از طریق کد نویسی در محیط ویژوال بیسیک در اکسل ایجاد نمایید.
سوال = اگر در اکسل نمراتی داشته باشیم که بخواهیم با رنگ خاص یا علامتی مشخص شود مثلا نمرات کمتراز ۱۰ را برایمان مشخص نماید باید چه کار کنیم
سلام، باید از ابزار Conditional Formatting استفاده کنید. مقاله آموزشی در سایت وجود دارد. مطالعه نمایید.
با سلام
میخواستم در یک شیت سرچ سه مرحله ای انجام دهم به این صورت که اگر از بین کدهای بطور مثال a یکی از انها که دارای تاریخ b می باشد (تاریخ مورد نظر) انگاه قیمت ان کد را (c) به من نشان دهد
میشه راهنمایی کنید
سلام، برای اینکار میتوانید از فرمول نویسی ماتریسی استفاده کنید. که در واقع در چند مرحله داده ها را فیلتر میکند. انجام اینکار در اکسل کمی پیچیده است. موفق باشید.
سلام.آیا در اکسل تابعی وجود دارد که یک مقدار را در یک بازه جستجو نماید و در صورت وجود پیغام TRUE و در صورت عدم وجود پیغام FALSE بدهد.؟
سلام، خیر، باید از ترکیب توابعی مثل IFERROR و VLOOKUP استفاده نمایید. امتحان کنید.
با سلام
میخواهم با سرچ شماره یک فاکتور که مبلغ آن در طی مدت مثلا یک سال دریک کاربرگ اکسل ثبت شده(بطور مجزا) تمام مبالغ مربوط به آن شماره فاکتور را بدست بیارم .مثل یک لیست.خیلی سپاسگذارم
سلام، شما باید از ابزار Filter استفاده کنید. مقاله مربوطه را بخوانید.
سلام و وقت بخیر
بنده یک ستون از اعداد در اکسل دارم که میخوام بدونم جمع کدوماشون میشه مثلا ۲۱۰۰۰
در واقع میخوام مجموعه اعدادی رو پیدا کنم که جمعشون بشه ۲۱۰۰۰
ممنون میشم کمک کنید
با تشکر
سلام، برای این کار باید کد نویسی کنید.
با سلام
امکانش هست تابع رفرنس رو آموزش بدید.. مثلا ی شیت مرجع داریم ک توش کد نوشتیم و شیت دیگمون پر از اطلاعات که وقتی اون کد هارو میزنیم ی کلمه رو نشون بده☺️
سلام، آیا در مورد ماکرو نویسی سوال را پرسیدید؟ در اینصورت مقالات مربوطه را بخوانید و مطمئن باشید که انجام هرکاری با کد نویسی ممکن است، اما در محیط اکسل محدودیت داریم ومحدود به توابع هستیم.
با سلام و تشکر از مطالب خوبتون
یه سوال داشتم
فرض کنید یک بانک اطلاعاتی شامل تعداد زیادی شماره پلاک ماشین در اختیار داریم
می خوایم در یک سلول قسمتی از شماره پلاک را وارد کنیم و اکسل اون شماره مورد نظر رو نمایش بده. مثلا بنویسیم ۶۱۱ و تمام پلاک هایی که شامل عدد ۶۱۱ هستند که حداکثر ۲ یا ۳ مورد هست نمایش داده بشه و ما شماره پلاک مورد نظر رو انتخاب کنیم. آیا همچین امکانی در اکسل هست؟
سلام، متشکرم
در محیط اکسل انجام این کار ممکن است اما کمی پیچیده و زمان بر است، باید از فرمول های جستجو و مرجع به صورت ماتریسی استفاده کنید، برای انجام این کار میتوان کد نویسی نیز انجام داد که این راه هم نیاز به دانش کدنویسی دارد. موفق باشید.
با سلام
اگر اطلاعات چند شرکت در دو ستون آورده شده باشد مثلا کد حسابداری و مبلغ. کد شرکت اول از ردیف ۱ تا ۱۰ مثلا ۳۰ باشد از ردیف ۱۰ تا ۲۰ زیر ستون کد، کد شرکت دوم ۴۰ و کد بقیه شرکتها به همین صورت در ردیفهای زیر ستون کد حسابداری نوشته شده باشد و مبالغ در ستون کنار آن و بخواهیم اطلاعات هر شرکت را به شیتهای مجزا انتقال دهیم مثلا اطلاعات شرکت اول که از ردیف ۱ تا ۱۰ بود به شیت ۲ وارد شود و … باید چکار کنیم. لطفا راهنمایی کنید.
با تشکر
سلام، به صورت اتوماتیک نمیتوان اطلاعات شرکت های مختلف را به شیت های مختلف اناقال داد حتما باید کد نویسی انجام شود
باید در شیت های مختلف فرمول های متفوت برای شرکت های نتفاوت ایجاد نماید . موفق باشید.
سلام وقت بخیر
جدولی دارم و میخواهم وقتی نام فرد را در سلول آن وارد کردم از یک اکسل دیگر نام فرد و شماره ملی اورا جستجو کرده و در سلول مورد نظر وارد کند. ممنون
سلام، از توابع جستجو و مرجع (مانند تابع VLOOKUP، INDEX و…) استفاده نمایید. موفق باشید.
سلام
مطلبتون خیلی خوب بود
راستش من با vlookup کار کردم
یه مشکل دارم الان
اگه بخوایم تو چند دسته ستون سرچ کنیم چیکار باید بکنیم؟
مثلا نام، تلفن ، آدرس ۲۰۰ نفر رو داریم که میشه ۲۰۰ ردیف ۳ ستونه
با Vlookup راحت میشه با سرچ نام مثلا تلفنشون رو بیاریم
حالا اگر مجبور باشیم به ۴ تا دسته ۵۰ تایی تقسیم کنیم که میشه ۵۰ ردیف ۱۲ ستونه
حالا چجوری میتونیم سرچ کنیم؟
تو ستون اول انجام میده ولی بقیشو نه
میشه راهنمایی کنید
ممنون
سلام، از توابع INDEX و MATCH استفاده کنید، البته باید فایل شما رو ببینم براتون انجامش بدم، برام ارسال کنید، موفف باشید.
با سلام و خسته نباشید
من در یک فایل اکسل تعدای ستون با عناوین مختلف دارم . آیا میشه بر اساس مقدار یک ستون در خروجی مقدار یک ستون دیگه رو دریافت کرد ؟ مثلا ستونی داریم با عنوان “نام پیمانکار” و ستون دیگری داریم با عنوان “نام مشتری” , حالا می خواهیم تمام مشتری هایی که پیمانکارشون مثلا آقای قدیمی هست رو بدست بیاوریم ؟ آیا شدنی هست ؟
با تشکر فراوان
سلام، بله، میتوانید از ابزار Advanced Filter استفاده کنید. همچنین میتوانید از فرمول نویی ماتریسی هم استفاده کنید که البته Advanced Filtering راحت تر است. لطفاً مقاله مربوطه را مطالعه کنید. موفق باشید.
سلام وقتتان بخیر
من یک جدول تو اکسل دارم ک شامل اطلاعاتی در مورد بیماری هاست این جدول شامل ۵ تا ستون هس که نام دوتا از انها “نام بیماری” و “علائم بالینی ” است که بعضی از این بیماری ها در علائم بالینی مشترک هستند من میخوام وقتی ی علائم بالینیو سرچ کردم بیماری هایی ک اون علائم را دارند نشون بده
ممونم میشم اگ کمک کنین خیلی ضروریه
سلام، در استفاده از توابع جستجو و مرجع شما این مشکل رو دارید که الولین مورد یاد شده به عنوان خروجی به شما نشان داده میشود. برای رفع این مشکل باید با استفاده از توابع جستجو و مرجه فرمول نویی ماتریسی انجام دهید. راه دوم استفاده از ابزار Advanced Filter است که میتوانید جداول را به دلخواه فیلتر کنید و راه سوم کد نویسی میباشد. موفق باشید.
سلام
سایت شما خیلی زیبا و مفید هست . دستتون درد نکنه ومتشکرم که جوانان عزیز کشورمان را در فراگیری مطالب یاری میکنید .
من برای نوشتن یک ماکرو به دستوری نیاز دارم که وقتی یک ردیف را انتخاب و ماکرو را اجرا میکنم شماره ردیف را در ماکرو دریک متغیر ذخیره کنم . قبلا استفاده کرده ام ولی الان یادم رفته خیلی ممنون میشم اگه راهنمایی کنید . تقریبا به شکل زیر بود .
AS=activrow namber
سلامف متشکرم، موفق باشید
MyVariable=Cells(i,j).Rows
of
MyVariable=Range(“A1”).Rows
سلام. من یه مشکل دارم کسی میتونه کمک کنه ؟
من یه فایل اکسل دارم حاوی ۳۱ شیت مختلف مربوط به روزهای ماه – که داخل هرکدوم اقلا ۳۰۰ داده ورودی وارد شده و حالا به دست من رسیده .
من میخام بدونم چطوری میتونم تو یه فایل جدید و جداگانه که خودم دارم فرضا داخل فایل قدیمی ۳۱ شیته من جستجو انجام بشه و درصورتی که تاریخی که من تو فایل جدید دادم رو پیداکرد در اونصورت بهم داده موجود در یک سطر و ستون خاص رو بده. ( داخل فایل ۳۱ شیته من یه سلول اختصاصی وجود داره که تاریخ اون رو نوشته شده – اگه بشه به اون رفرنس داد که از فایل جدید اون تاریخ جستجو بشه و درصورت پیداکردنش مقادیر موجود رو بهم بده)
از اونجا که فایل من قراره یه سری خروجی و نمودار تهیه کنه به همین خاطر میخام طوری باشه که مثلا من تو فایل جدید تاریخ رو بزنم ۹۷/۰۳/۳۱ و اون جستجو کنه تو بانک اطلاعاتی ۳۱ شیته من و مثلا سلول d2 رو از همون شیت که مربوط به ۹۷/۰۳/۳۱ میشه برای من استخراج کنه
از چه فرمولهایی میتونم کمک بگیرم/ کمک کنینی ممنون
سلام، انجام این کار و در واقع نوشتن فرمول انجام این کار سخت نیست اما در نظر داشته باشید برای فرمول نویسی باید تمام شیت ها در فرمول خود درج کنید بنابراین فرمول شما بسیار بسیار طولانی و احتمالاً نا کارآمد خواهد بود. لذا پیشنهاد من استفاده از ماکرو نویسی است و به نظرم در اکسل نمیتوانید این مشکل را با فرمول نویسی ساده مرتفع نمایید. موفق باشید.
با سلام.من یک لیست از اسامی دارم با مقدار مشخص برای هر کدام.در این لیست چند اسم بارها تکرار شده.حال میخوام فرمولی بنویسم و از داخل این جدول انهایی که یک نام داره رو جدا کنه با مقدار مشخص روبروش و در جدولی در شیت دیگر وارد کنه.با چه فرمولی میتونم این کار رو بکنم.در ضمن ایا لیسبی وجود داره برای اینکه اگر در جدولی من عدد در ردیف وارد کردم خودکار یک خط مابین اخرین ردیف و جمع کل در زیر اضافه کنه؟
سلام، در پاسخ به سوال اول، از توابع VLOOKUP و بهتر از آن از ترکیب توابع INDEX، MATCH و OFFSET میتوانید استفاده کنید. اما در موارد تکراری فقط اولین مورد یافت میشود و برای پیدا کردن موارد دیگر راه دیگری جز کد نویسی (مامرو نویسی) به ذهن من نمیرسد. در پاسخ به سوال دوم، خیر، این مکان در اکسل نیست مگر اینکه کد نویسی انجام دهید. موفق باشید.
سوال دیگر این که ایا روشی وجود داره که نه مثل vlook & lookup &hlook فقط یک سطر و یا یک ستون رو جستجو کنه بلکه یک عدد رو در یک جدول جستجو کنه.و درضمن وقتی اون عدد رو جستجو کرد در سلول کناری آدرس اون سلول نوشته بشه
سلام، بله، برای این کار شما باید توابع جستجو و مرجع INDEX، OFFSET ، ADDRESS و MATCH را با هم ترکیب کنید. این کار ممکن است. موفق باشید.
سلام ، وقت به خیر
من می خواهم تابع vlookup مقدار همان سطر یا ستون مورد جستجو را به عنوان خروجی برگرداند نه سطرهای روبه رو را
برای این کار باید از چه توابعی استفاده کنم و چگونه؟
سلام، متشکرم، تابع VLOOKUP دقیقاً مقدار سلول را بر میگردان، بنابراین منظور خود از سوال را بیشتر توضیح دهید لطفاً، متشکرم
سلام ممنون از اطلاعات خوبتون
من فایلی دارم حاوی مشخصات افراد در یک شیت. در شیت بعدی فرمی تنظیم کردم جهت پرینت . میخام فرمولی تعریف کنم که روی هر ردیف شیت اول که کلیک کردم اطلاعات ان وارد فرم پرینت شیت دوم بشه. لطفا راهنمایی کنید.
سلام، انجام این کار در محیط اکسل ممکن نیست و باید ماکرو نویسی انجام دهید. موفق باشید.
باسلام
من میخوام در اکسل که مقادیر تکراری زیاد ، جست و جو کنم و اخرین مقدار رو برام پیدا کنه
کسی میدونه چجوری میشه؟با دستور if فقط نباشه یه چیز مثل vlookup باشه
سلام، تابعی برای جستجوی مقادیر تکراری در اکسل وجود ندارد. برای این کار ابزاری مانند Conditional Formatting وجود دارد. که مقادیر تکراری را رنگی میکند، بنابراین تنها راه استفاده از کد نویسی میباشد که البته بسیار حرفه ای و در صورتی که یادبگیرید بسیار کاربردی است. موفق باشید.
سلام دوست عزیز
میشه در این مورد منو راهنمایی کنید
در این ادرس یک مثال هست ولی من یک سئوال دیگه دارم با توجه به مثال اگر بعد از پیدا کردن شخص مورد نظر دو درامد جداگانه باشه چه جور میشه به دومین یا آخرین درامد دستسرس داشت؟
سلام، این کار ممکن نیست و اولین یافته به عنوان خروجی اعلام میشود. موفق باشید.
سلام آقای قدیمی اگر بخوایم در یک ستون که پویا هست بخوایم یک مقدارمتنی رو جستجو کنیم از چه فرمولی استفاده میشه
سلام
من با استفاده از تابع if یک فرمولل نوشتم که خروجی اون “بالاتر از حر مراقبت و کمتر از حد مجاز” هست. حالا میخوام عبارت “بالاتر از حد مراقبت” رو قرمز بنویسه و نصف دیگر عبارت “کمتر از حد مجاز” رو سبز بنویسه.
از conditional formating که استفاده میکنم فقط میتونم رنگ کل عبارت رو عوض کنم اما من میخوام نصف عبارت سبز و نصف دیگه قرمز باشه.
لطفا راهنماییم کنید.
ممنون میشم امکانش باشه که پاسخ رو برام ایمیل کنید.
باتشکر
سلام، تنها راه استفاده از Conditional Formatting هست که به آن اشاره فرمودید. اما با این ابزار امکان اعمال دو فرمت به صورت همزان بر روی یک سلول با داشتن یک شرط به خصوص وجود ندارد. لیکن شما میتوانید تعیین کنید رنگ فونت سلول مورد نظر در ظرایطی سبز و در شرایطی قرمز باشد و امکان اینکه در یک حالت نصف فونت سبز و نصف دیگر قرمز باشد فراهم نیست. موفق باشید.
میخوام در یک ردیف تعداد سلول هایی که شامل یک اسم نمی باشند را مشخص کنم؟
مثلا لیست تعدادافراد بجز علی را در یک سطر مشخص کنم؟
سلام، برای این کار تنها ابزار استفاده از Conditional Formatting است. با اسفتفاده از این ابزار و توابع Lookup & Reference مانند SEARCH میتوانید سلول های مورد نظر را مشخص نمایید. موفق باشید.
با سلام.
سوالی از خدمتتون داشتم یک شیت حاوی اطلاعات فروش در مهر ماه دارم میخوام در یک شیت دیگر بر اساس شماره فاکتور تمامی اطلاعات مربوط به آن فاکتور درشیت دوم بیاید ینی با استفاده از یک مقدار تمامی سطرهای متناظر با اون مقدار بیاد.
سلام، برای انجام این کار از توابع Lookup & Reference میتوانید استفاده نمایید و پیشنهاد من استفاده از ترکیب توابع INDEX و MATCH است. موفق باشید.
سلام ممنون از سایت خوبتون
ممنون میشم به منم راهنمایی کنید
من یه فایل دارم شامل ادرس خونه ها و قیمت فروششون
میخواستم ببینم چطور میتونم سرچ کنم ادرس خونه هایی رو بهم نشون بده که رنج قیمتش از مثلا صد تا دویست هستش؟
از چه تابعی باید استفاده کنم ؟
سلام، خواهش میکنم، برای این کار شما نیاز به استفاده از ابزارهای فیلتر و Advanced Filter دارید، انجام دادن این کار با استفاده از فرمول نویسی نیاز به استفاده از فرمول نویسی ماتریسی و آرایه ای دارد و امری نسبتاً پیچیده است.
موفق باشید.
سلام وقت بخیر
ممنون از آموزش های سایت
من جدولی دارم شامل نام – نام خانوادگی – آدرس – شماره تلفن و…
می خواهم وقتی نامی یا یکی از عنوان های جدول را تایپ مکنم مثلا ۳ کاراکتر از نامی را تایپ کردم هر نامی از تو این جدول که به این ۳ کاراکتر تایپ شده ربط داشته باشد را بصورت فیلتر به ما نشان دهد
مثلا نام خانوادگی ابراهیمی اگر ۳ کاراکتر ابر را تایپ کردیم هر نام خانوادگی که این کلمه ابر را داشت جدول ما را فیلتر کند
راهی هست لطفا راهنمایم کنید
با تشکر
سلام برای انجام این کار میتوانید از advanced filter استفاده نمایید، مقاله مربوطه را مطالعه بفرمایید. موفق باشید.
سلام
میخوام تابع لوک آپ رو بین دوتا اکسل اجرا کنم. باید چیکار کنم؟
یعنی یک مقدار رو بخونه و بره توی فایل اکسل دیگر، عبارت قابلش رو بخونه و برگردونه.
سلام، هر دو فایل اکسل باز باشند، در یان حالت مانند همیشه فرمول نویسی کنید. موفق باشید.
با سلام به اقا حامد گل وتشکر از اطلاعاته بسیار مفیدتون
این سوالو یبار پرسیدم فک کنم تایید نشده
فرض کنیم با مثبت بودن یه شرط درسلولی(مثلا b4) بخواهیم روی ستون کناری و ده سطر بعدش(a4:a13)یک عملیات مقایسه ای انجام دهیم ک این ادرس دهی نسبی است یعنی هر جای این دیتا بیس در ستون (B) میتواند بکار رود
حال سوال اینجاس ک اگه بخواهیم طول این ناحیه (ده سطر) متغییر باشد وتوسط سلول ثالثی تایین گردد از چ راهی باید رفت
البته اگر ترجیحا فقط در سلول b4 این حرکت انجام شود عالیه چون این مساله رو از راه دیگه حل کردم ولی فک کنم راه میان بر و کوتاه تر باشه
باتشکر فراوان
سلام اساتید
در شیت اول یک فاکتور کل دارم که کد کالاها رو وارد میکنم و مشخصات رو میاره
حالا در شیت دوم یه فاکتور جزء دارم که میخوام اگر در شیت اول کد کالاهای ۳۱۷۸و۳۱۷۹و۳۱۸۰و…. وجود داشت اونها رو در ستون کد کالاهام در شیت دوم واردکنه.دقیقتر بگم اگه فلان کدکالاها در شیت اول بود اونها رو برام پیدا کنه و تو ستون کدکالای شیت دوم وارد کنه
چطور میتونم این کار رو انجام بدم؟
سلام، برای انجام این کار نیاز به کد نویسی دارید. موفق باشید.
سلام خسته نباشید
ببخشید استاد من چندتا سوال دارم
۱. اگه بخوایم تو یک شیت از داده هامون ، که توشون داده های تکراری داریم یعنی مثلا کد ۱۰۰ سه بار تکرار شده ولی با مقدار فیلد های مختلف مثلا کد ۱۰۰ فیلد رنگش سه نوع مقدار گرفته و در نتیجه سه تا رکورد دارم ولی همشون با کد ۱۰۰ هستند، حالا میخوام تو یه صفحه جدید یک رکورد داشته باشم با کد ۱۰۰ ولی فقط فیلد های غیر تکراریش جستجو بشه و بیاد تو صفحه جدید کپی بشه امکانش هست؟
باید چکار کنم ؟لطفا با جزئیات برام توصیح بدید من یکم تو اکسل تازه کارم هنوز
ممنون
سلام، برای این کار میتوانید فرمول نویسی ماتریسی انجام دهید که به اندازه کافی پیچیده است که به سراغش نرویم، راه دوم استفاده از ماکرو نویسی و کد نویسی در اکسل است که عملاً راه حل بسیاری از مسائل حل نشدنی در اکسل میباشد که نیاز به یادگیرفتن کد نویسی و تمرین دارد و راه سوم استفاده از دستور Remove Duplicate در تب Data است و در نهایت راه چهارم که به نظرم بهترین راه برای شما است استفاده از Advance Filter میباشد که برای یادگیری ورش استفاده از این ابزار مقاله مربوطه در قسمت مقالات آموشی بخوانید.
سلام. ممنون از راهنمایی هاتون.
من یه شیت دارم حاوی آمار و اسامی و تعدادی نمره.
در شیت دیگر میخوام یه کارنامه طراحی کنم که اون اطلاعات رو برام نمایش بده.
میخوام یه لیست کشویی داشته باشم شامل اسامی شیت اولی که هر کس رو انتخاب کردم کارنامه همون دانش آموز رو نمایش بده. لطفا راهنمایی کنید.
دورد، برای انجام این کار شما نیاز به یادگیری تعدادی از توابج جستجو و مرجع مانند INDEX، MATCH دارید و همینطور باید روش کار با Combo Box رو یاد بگیرید. در کل کار پیچیده ای نیست و تنها نیاز به صرف زمان و دقت دارد. حتما موفق میشوید.