vlookup پیشرفته؟؟؟؟

[saber2q]

سلام
من دوباره گیر کردم
این فایل پیوستی رو ببینید:
یک شیت "چک" دارم و یک شیت "ثبت سند", در ستون "نوع پرداخت" شیت "ثبت سند" از طریق دتاولیدشن انتخاب سلول ها محدود شده نقد یا چک یا....
از طرفی در شیت "چک" قراره لیست اطلاعات کلیه چک های دریافتی قرار بگیره و این اطلاعات باید از شیت "ثبت سند" که کلیه سندها از جمله چک های دریافتی در اون وارد شده استخراج بشه و در شیت "چک" پشت سر هم قرار بگیره.
پس اولین شرط ورود اطلاعات به شیت "چک" اینه که در ستون "نوع پرداخت" شیت "ثبت سند" کلمه چک انتخاب یا نوشته شده باشه, دومین عمل اینه که در شیت چک آخرین ردیف (اولین ردیف خالی) مشخص بشه و مشخصات شماره چک مورد نظر در اون قرار بگیره و در کل هرچی مشخصات چک در "ثبت سند" وارد شد همزمان در شیت "چک" هم به صورت خودکار وارد بشه و البته هیچ شماره چکی هم نباید دوبار تکرار بشه و این کنترل در همون لحظه ای که شماره چک در شیت "ثبت سند" وارد میشه باید انجام بگیره.
در اصل نیاز به یه تابعی شبیه vlookup دارم به صورتیکه در کل رنج و به تعداد رکوردها تکرار بشه و نتایج را پشت سر هم در یک رنج دیگه بچینه. (تابع vlookup فقط یکبار اجرا میشه و به محض رسیدن به نتیجه دیگه تکرار نمیشه.
میشه اساتید راهنمایی کنید؟
فایل نمونه:
http://s3.picofile.com/file/7371611177/hesabdari_test2.xlsx.html

[khakzad]

RE: vlookup پیشرفته؟؟؟؟

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

[saber2q]

RE: vlookup پیشرفته؟؟؟؟

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

مرسی
کاملا درست کار کرد, فقط میشه در مورد توابع iserror و match و کلا این فرمولی که نوشتید واسم توضیح بدید؟

در مورد عمدم تکرار, منظور منم در همون زمانیه که تو "ثبت سند" میخوایم وارد کنیم, اونم ممنون میشم راهنمایی کنید فرمولشو.

[khakzad]

RE: vlookup پیشرفته؟؟؟؟

بله حتما
ببینید اولین کاری که می کنیم باید اطلاعات مشابه رو ی جوری منحصر بفرد کنیم که تابع vlookup بتونه همشونو پیدا کنه.
پس میایم در اولین ستون هون فرمول if رو می نویسیم.همونطور که پیداست، میاد میگه اگر سلول من برابر بود با چک، یک شماره براش بزن.همینطور به ترتیب هر حا چک بود، شماره قبلی رو + 1 می کنه....
حالا ما باید بجای کلمه چک ،اعداد 1،2،3.....رو پیدا کنیم.ی لیست از اعداد درست می کنیم.مثلا 500 تایی.اعداد 1 تا 500.که من در ستون u نوشتم.میایم اعداد رو در شیت ثبت سند vlookup می کنیم.(که اینهارو میدونید)
حالا می ریم سر iserror.این فرمول برای گرفتن خطاست.چون در صورتی که vlookup مورد مورد نظر رو پیدا نکنه.ارور میده.من میخام درصورتی که ارور داد، خالی بذاره.در غیر اینصورت همون داده پیدا شده رو بذاره.(که با if ترکیبش کردم)
در نهایت هم match : برای Index col که باید شماره ستون های مورد نظر رو بدیم بهش.برای اینکه دستی تغییر ندیم و با درگ کردن اعداد خودشون تغییر کنن.از این استفاده کردیم.
این فرمول میاد مکان یک سل ذو در یک مخدوده مشخص می کنه.مثلا شماره سل تاریخ رو در محدوده ردیف اول در شیت چک تعیین میکنه و به ما میده 1.چون اولین سل در اون محدوده هس.
اگر مبهم توضیح دادم بپرسید.در خدمتم


قسمت دوم رو هم می فرستم تا نیمساعت دیگه.

[saber2q]

RE: vlookup پیشرفته؟؟؟؟

راستی این علائم یعنی چی؟

&A&1
G$1,$A$1:$G$1

کلا این $ یعنی چی ؟

یه سوال دیگه:
ستون U رو چیکار کردید که حاوی اعداد هست ولی نشونشون نمیده؟

[khakzad]

RE: vlookup پیشرفته؟؟؟؟

سوال اول: اینها مربوط به ادرس دهی هست.مطلق و ازاد بودن ادرس ها هست.توضیحش مفصله.
سوال دوم: از مسیر زیر فرمت سل رواز generel به ;;; تغییر بدین.محتویات داخل سل دیده نمیشه.
format cell/ number/custom[hr]



اینم از سوال اخر
الان در شیت ثبت سند.در ستون جزئیات نمیتونین 2تا شماره چک مشابه وارد کنید.
برای اطلاع از نحوه انجام کار، روی هر سل کلیک کنید و data validation رو بزنید.به فرمولش دقت کنید
سوالی بود در خدمتم

[saber2q]

RE: vlookup پیشرفته؟؟؟؟

ممنون
match مگه آدرس ستون رو استخراج نمیکنه؟
فرمول شما مطمئنا درسته اما من هرچی میخونمش, اینجوری به نظر میرسه که عدد خروجی متچ به اضافه یک میشه و...
میشه در مورد Match بیشتر توضیح بدید؟

[khakzad]

RE: vlookup پیشرفته؟؟؟؟

ببینید خروجی این فرمول عدد هست.نه ادرس.مثلا شماره سلول a1 در رنج a1:e1 چنده؟ مسلما 1 هست.به همین ترتیب شماره b1 در این محدوده 2 هست و الی اخر.....
حالا من برای فرمول vlookup که نوشتم.برای هر ستون یک شماره ستون لازم دارم. در شیت ثبت سند، table array ما از ستون a شروع میشه تا اخرش.
حالا مثلا ما می خوایم برای فرمول vlookup شماره ستون تاریخ رو بدیم که در واقع دومین ستون از table array ما هست.یعنی باید د col index بذاریم 2.(در واقع این کارو دستی میشه انجام داد.بجای همه فرمول match عددهای مورد نظر رو بذاریم)
.من برای راحتی از match استفاده کردم.برای بدست اوردن عدد 2 میایم تاریخ رو در اون رنج مورد نظر match می کنیم.مسلما به ما میده 1.حالا چون عدد 2 می خوایم +1 هم می کنیم.
امیدوارم واضح گفته باشم.
در ضمن برای مشاهده چگونگی عملکرد یک فرمول بصورت پله پله به formula/evaluate formula برید و مرحله به مرحله ببینید[hr]
[hr]
در ضمن بجای اینکه اعداد کمکی در ستون U داشته باشیم.میتونیم از شماره ردیف در داخل خود فرمول استفاده کنیم.که جهت سادگی و مفهوم بودن بیشتر اولی رو ترجیح دادم بذارم[hr]

[saber2q]

RE: vlookup پیشرفته؟؟؟؟

ببینید ---وجی این فرمول عدد هست.نه ادرس.مثلا شماره سلول a1 در رنج a1:e1 چنده؟ مسلما 1 هست.به همین ترتیب شماره b1 در این محدوده 2 هست و الی ا---.....
حالا من برای فرمول vlookup که نوشتم.برای هر ستون یک شماره ستون لازم دارم. در شیت ثبت سند، table array ما از ستون a شروع میشه تا ا---ش.
حالا مثلا ما می خوایم برای فرمول vlookup شماره ستون تاریخ رو بدیم که در واقع دومین ستون از table array ما هست.یعنی باید د col index بذاریم 2.(در واقع این کارو دستی میشه انجام داد.بجای همه فرمول match عددهای مورد نظر رو بذاریم)
.من برای راحتی از match استفاده کردم.برای بدست اوردن عدد 2 میایم تاریخ رو در اون رنج مورد نظر match می کنیم.مسلما به ما میده 1.حالا چون عدد 2 می خوایم +1 هم می کنیم.
امیدوارم واضح گفته باشم.
در ضمن برای مشاهده چگونگی عملکرد یک فرمول بصورت پله پله به formula/evaluate formula برید و مرحله به مرحله ببینید[hr]
[hr]
در ضمن بجای اینکه اعداد کمکی در ستون U داشته باشیم.میتونیم از شماره ردیف در داخل خود فرمول استفاده کنیم.که جهت سادگی و مفهوم بودن بیشتر اولی رو ترجیح دادم بذارم[hr]

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

[khakzad]

RE: vlookup پیشرفته؟؟؟؟

خواهش میکنم
به نظرم میرسه با وی بی شدنی باشه(یعنی مطمئنم).داخل خود اکسل نمیدونم .شاید شدنی هست و من نمیدونم
توی انحمن وی بی مطرح کنید.دوستان حتما جوابی براش دارن

[khakzad]

RE: vlookup پیشرفته؟؟؟؟

خواهش میکنم
به نظرم میرسه با وی بی شدنی باشه(یعنی مطمئنم).داخل خود اکسل نمیدونم .شاید شدنی هست و من نمیدونم:huh:
توی انحمن وی بی مطرح کنید.دوستان حتما جوابی براش دارن
البته سوال دومتون با conditional formating جواب میده.اما فقط شما رو اگاه می کنه که مثلا 1 سل جا انداختین(با تغییر رنگ).اما جلوگیری نمی کنه که اینکارو نکنید.[hr]

[Javid Mokhtari]

RE: vlookup پیشرفته؟؟؟؟


بارم ممنون
خیلی زخمت دادم، فقط اینکه آیا میشه با سلول ها کاری کرد که تنها یک بار بشه مقدار رو وارد کرد و بعد از اون امکان تغییر و پاک کردنش نباشه؟
--------------------------------------------------------------------------------
خب با توجه به نوع نیاز شما میشه از راه های زیادی استفاده کرد اما روش ساده که می توان گفت:عدم تغییر سلول های پرشده
---------------------------------------------------------------------------------

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

--------------------------------------------------------------------------------
این هم بستگی به طریقه ورود اطلاعات شما در سلول ها دارد و اینکه چه سلولی معیار شما برای مقایسه است.

[saber2q]

RE: vlookup پیشرفته؟؟؟؟

بارم ممنون
خیلی زخمت دادم، فقط اینکه آیا میشه با سلول ها کاری کرد که تنها یک بار بشه مقدار رو وارد کرد و بعد از اون امکان تغییر و پاک کردنش نباشه؟
--------------------------------------------------------------------------------
خب با توجه به نوع نیاز شما میشه از راه های زیادی استفاده کرد اما روش ساده که می توان گفت:عدم تغییر سلول های پرشده
---------------------------------------------------------------------------------

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

--------------------------------------------------------------------------------
این هم بستگی به طریقه ورود اطلاعات شما در سلول ها دارد و اینکه چه سلولی معیار شما برای مقایسه است.

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

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

راستی این دستور کارش چیه؟ و منظور از ByVal تو تعریف متغییرها چیه؟

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
rang = Target.Address
End Sub

[Javid Mokhtari]

RE: vlookup پیشرفته؟؟؟؟

فکر کنم این روش بهتره:عدم تغییر سلول های پر شده
ByValue[hr]
Worksheet_SelectionChange:
یک رویداد است که وقتی ادرس سلول انتخاب شده تغییر کنه ، کد های موجود در این رویداد اجرا می شوند.
مثلاً فرض کنید سلول A1 انتخاب شده است حال اگر به هر طریقی سلول دیگر را انتخاب کنید کد های این رویداد اجرا می شود.

rang:یک متغیر از نوع عمومی است.یعنی حوزه ی دسترسی به مقدار این متغیر در تمام قسمت های رویه ی رویداد Worksheet_SelectionChange می باشد.

target:
اشاره به سلولی دارد که ما آن سلول رو از طریق انتخاب مورد خطاب قرار می دهیم.
target.address:آدرس سلول مورد خطاب قرار داده شده را به ما می دهد.

[saber2q]

RE: vlookup پیشرفته؟؟؟؟

فکر کنم این روش بهتره:عدم تغییر سلول های پر شده
ByValue[hr]
Worksheet_SelectionChange:
یک رویداد است که وقتی ادرس سلول انتخاب شده تغییر کنه ، کد های موجود در این رویداد اجرا می شوند.
مثلاً فرض کنید سلول A1 انتخاب شده است حال اگر به هر طریقی سلول دیگر را انتخاب کنید کد های این رویداد اجرا می شود.

range:یک متغیر از نوع عمومی است.یعنی حوزه ی دسترسی به مقدار این متغیر در تمام قسمت های رویه ی رویداد Worksheet_SelectionChange می باشد.

target:
اشاره به سلولی دارد که ما آن سلول رو از طریق انتخاب مورد خطاب قرار می دهیم.
target.address:آدرس سلول مورد خطاب قرار داده شده را به ما می دهد.

مرسی از توضیحاتتون،
نمیشه کاری کنیم که سلولها بعد از اینکه خواستیم بریم خط بعد (ردیف بعد) قفل شن؟ (حالا در لحظه ورود به خط بعد یه سوال تائید هم پرسیده بشه و بره خط بعد و سلول های خط قبل و خط های قبل رو قفل کنه)