PDA

توجه ! این یک نسخه آرشیو شده میباشد و در این حالت شما عکسی را مشاهده نمیکنید برای مشاهده کامل متن و عکسها بر روی لینک مقابل کلیک کنید : vlookup پیشرفته؟؟؟؟



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

khakzad
2012/05/03, 15:02
سلام
این فایل رو ببینید
الان در صورتی که هر انتخاب رو به چک تغییر بدین.میاد توی شیت چک میشینه.
اما چیزی که هست من الان شماره چک ندیدم! همون جزئیات رو به عنوان شماره چک در نظر گرفتم.که خودتون می تونید تغییر بدین.
در مورد عدم ورود شماره چک تکراری بهترین کار اینه که توی همون شیت ثبت سند، از ورود اطلاعات تکراری جلوگیری کنید.از طریق دیتا ولیدیشن.که 1 فرمول ساده داره.اگر موافق هستین ادامه بدیم.اگر هم نه دوستان از طریق وی بی .....
در ضمن در مورد نحوه کار هر سوالی بود در خدمتم

saber2q
2012/05/03, 15:10
سلام
این فایل رو ببینید
الان در صورتی که هر انتخاب رو به چک تغییر بدین.میاد توی شیت چک میشینه.
اما چیزی که هست من الان شماره چک ندیدم! همون جزئیات رو به عنوان شماره چک در نظر گرفتم.که خودتون می تونید تغییر بدین.
در مورد عدم ورود شماره چک تکراری بهترین کار اینه که توی همون شیت ثبت سند، از ورود اطلاعات تکراری جلوگیری کنید.از طریق دیتا ولیدیشن.که 1 فرمول ساده داره.اگر موافق هستین ادامه بدیم.اگر هم نه دوستان از طریق وی بی .....
در ضمن در مورد نحوه کار هر سوالی بود در خدمتم


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

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

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


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

saber2q
2012/05/03, 15:35
راستی این علائم یعنی چی؟


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


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

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

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



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

saber2q
2012/05/03, 15:57
ممنون
match مگه آدرس ستون رو استخراج نمیکنه؟
فرمول شما مطمئنا درسته اما من هرچی میخونمش, اینجوری به نظر میرسه که عدد خروجی متچ به اضافه یک میشه و...
میشه در مورد Match بیشتر توضیح بدید؟

khakzad
2012/05/03, 16:10
ببینید خروجی این فرمول عدد هست.نه ادرس.مثلا شماره سلول 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
2012/05/03, 22:16
ببینید ---وجی این فرمول عدد هست.نه ادرس.مثلا شماره سلول 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
2012/05/04, 00:15
خواهش میکنم
به نظرم میرسه با وی بی شدنی باشه(یعنی مطمئنم).داخل خود اکسل نمیدونم .شاید شدنی هست و من نمیدونم
توی انحمن وی بی مطرح کنید.دوستان حتما جوابی براش دارن

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

Javid Mokhtari
2012/05/04, 12:11
بارم ممنون
خیلی زخمت دادم، فقط اینکه آیا میشه با سلول ها کاری کرد که تنها یک بار بشه مقدار رو وارد کرد و بعد از اون امکان تغییر و پاک کردنش نباشه؟
--------------------------------------------------------------------------------
خب با توجه به نوع نیاز شما میشه از راه های زیادی استفاده کرد اما روش ساده که می توان گفت:عدم تغییر سلول های پرشده (http://uplod.ir/1tzciuftgu39/no_change.xlsm.htm)
---------------------------------------------------------------------------------

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

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

saber2q
2012/05/04, 15:07
بارم ممنون
خیلی زخمت دادم، فقط اینکه آیا میشه با سلول ها کاری کرد که تنها یک بار بشه مقدار رو وارد کرد و بعد از اون امکان تغییر و پاک کردنش نباشه؟
--------------------------------------------------------------------------------
خب با توجه به نوع نیاز شما میشه از راه های زیادی استفاده کرد اما روش ساده که می توان گفت:عدم تغییر سلول های پرشده (http://uplod.ir/1tzciuftgu39/no_change.xlsm.htm)
---------------------------------------------------------------------------------

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

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


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

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

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

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

Javid Mokhtari
2012/05/04, 17:44
فکر کنم این روش بهتره:عدم تغییر سلول های پر شده (http://uplod.ir/xzsgjima9zrw/no_change.xlsm.htm)
ByValue (http://forum.exceliran.com/thread-1283-post-6294.html#pid6294)[hr]
Worksheet_SelectionChange:
یک رویداد است که وقتی ادرس سلول انتخاب شده تغییر کنه ، کد های موجود در این رویداد اجرا می شوند.
مثلاً فرض کنید سلول A1 انتخاب شده است حال اگر به هر طریقی سلول دیگر را انتخاب کنید کد های این رویداد اجرا می شود.

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

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

saber2q
2012/05/05, 00:27
فکر کنم این روش بهتره:عدم تغییر سلول های پر شده (http://uplod.ir/xzsgjima9zrw/no_change.xlsm.htm)
ByValue (http://forum.exceliran.com/thread-1283-post-6294.html#pid6294)[hr]
Worksheet_SelectionChange:
یک رویداد است که وقتی ادرس سلول انتخاب شده تغییر کنه ، کد های موجود در این رویداد اجرا می شوند.
مثلاً فرض کنید سلول A1 انتخاب شده است حال اگر به هر طریقی سلول دیگر را انتخاب کنید کد های این رویداد اجرا می شود.

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

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



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

Javid Mokhtari
2012/05/05, 18:26
مرسی از توضیحاتتون،
نمیشه کاری کنیم که سلولها بعد از اینکه خواستیم بریم خط بعد (ردیف بعد) قفل شن؟ (حالا در لحظه ورود به خط بعد یه سوال تائید هم پرسیده بشه و بره خط بعد و سلول های خط قبل و خط های قبل رو قفل کنه)


با سلام.
http://uplod.ir/w7jwl1o4ddx7/VLookUp.xlsm.htm

saber2q
2012/05/05, 19:26
[quote=jansiz hayat]





با سلام.


با سلام مجدد و زحمات مجدد بنده:
همون فايلي که اول آماده کرديد برام:
http://uplod.ir/1tzciuftgu39/no_change.xlsm.htm

ميشه طوري برنامه ريزيش کنيم که به جاي قفل شدن سلول ها در لحظه خروج از هر سلول، با هر بار زدن کليد ctr+s (هنگام ذخيره کردن سند) و همچنين هنگام خروج از سند جاري کليه سلول‌هاي غير خالي قفل بشن. يعني کاربر فقط تا زماني که سند رو ذخيره نکرده بتونه مقدار هايي که همون موقع وارد کرده تغيير بده اما به محضي که سند رو ذخيره کرد ديگه قابل تغيير نباشه...
ميشه اينکارو بکنيد برام؟

khakzad
2012/05/08, 13:02
فکر کنم این روش بهتره:عدم تغییر سلول های پر شده (http://uplod.ir/xzsgjima9zrw/no_change.xlsm.htm)
ByValue (http://forum.exceliran.com/thread-1283-post-6294.html#pid6294)[hr]
Worksheet_SelectionChange:
یک رویداد است که وقتی ادرس سلول انتخاب شده تغییر کنه ، کد های موجود در این رویداد اجرا می شوند.
مثلاً فرض کنید سلول A1 انتخاب شده است حال اگر به هر طریقی سلول دیگر را انتخاب کنید کد های این رویداد اجرا می شود.

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

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


سلام
ممنون
این قفل کردن با زدن unprotect sheet باز میشه.از طریق کد چطور میشه بهش پسورد داد که همون روال protect sheet که پسورد داره رو طی کنه؟

MEYTI
2012/05/09, 12:21
فایل پیوست خدمت شما

http://uplod.ir/20bgn1ishqmo/protect.xlsm.htm

Javid Mokhtari
2012/05/11, 00:54
ميشه طوري برنامه ريزيش کنيم که به جاي قفل شدن سلول ها در لحظه ---وج از هر سلول، با هر بار زدن کليد ctr+s (هنگام ذخيره کردن سند) و همچنين هنگام ---وج از سند جاري کليه سلول‌هاي غير خالي قفل بشن. يعني کاربر فقط تا زماني که سند رو ذخيره نکرده بتونه مقدار هايي که همون موقع وارد کرده تغيير بده اما به محضي که سند رو ذخيره کرد ديگه قابل تغيير نباشه...
ميشه اينکارو بکنيد برام؟
__________________________________________________ _____________________________

با سلام.
قفل کردن سلول بعد از ذخیره کردن فایل (http://persiandrive.com/691885)

saber2q
2012/05/13, 12:08
ميشه طوري برنامه ريزيش کنيم که به جاي قفل شدن سلول ها در لحظه ---وج از هر سلول، با هر بار زدن کليد ctr+s (هنگام ذخيره کردن سند) و همچنين هنگام ---وج از سند جاري کليه سلول‌هاي غير خالي قفل بشن. يعني کاربر فقط تا زماني که سند رو ذخيره نکرده بتونه مقدار هايي که همون موقع وارد کرده تغيير بده اما به محضي که سند رو ذخيره کرد ديگه قابل تغيير نباشه...
ميشه اينکارو بکنيد برام؟
__________________________________________________ _____________________________

با سلام.
قفل کردن سلول بعد از ذخیره کردن فایل (http://persiandrive.com/691885)


ممنون از پاسختون,
بسیار عالی و کارساز بود.

reza2003
2012/05/19, 01:00
سلام به همه دوستان
من یه سلول دارم که توش تاریخ را ثبت کردم و در ستون های بعدی ورود و خروج + محاسبات کسر کار و اضافه کار حالا میخوام تو یه شیت دیگه داده های بین دو تاریخ را تو یه شیت دیگه بصورت گزارش در بیارم
لطفا راهنمایی کنید

Javid Mokhtari
2012/05/19, 01:18
سلام به همه دوستان
من یه سلول دارم که توش تاریخ را ثبت کردم و در ستون های بعدی ورود و ---وج + محاسبات کسر کار و اضافه کار حالا میخوام تو یه شیت دیگه داده های بین دو تاریخ را تو یه شیت دیگه بصورت گزارش در بیارم
لطفا راهنمایی کنید


با سلام.
فایل ضمیمه لطفاً...

reza2003
2012/05/19, 15:58
http://s3.picofile.com/file/7385524729/test.xlsx.html

این فایل خامی هست که من درست کردم

kindcrasher
2013/05/14, 16:49
فايل ناقص هست[hr]
اگر بخواهيم يك value و نه يك فرمول را export كنييم بايد چه كرد .
در ضمن فايل راهنمايي براي من( جهت جمع بستن اعداد در ستون با 2 شرط تاريخ و رديف و با در نظر گرفتن تكرار ) ناقص بود لطفآ فايل ديگري آپلود بفرماييد