گزارش گیری ریز اطلاعات در یک شیت دیگر

[a.dal65]

سلام دوستان
فرض کنید من 100 تا واحد مسکونی رو به صورت اقساطی به 100 نفر فروختم
حالا افراد قسطاشونو میاد میدن که من در شیت Data وارد کردم (حدود 4000 سطر)
حالا میخوام یه صورت وضعیت از ریز واریزی های هر شخص رو توی شیت (DetailReport) در قالب یه فرم بیارم که مشخص باشه در چه تاریخ های بابت چه ماهایی واریز کرده . چقدر دیگه بدهکار هستش.
تا یک جایشو تونستم درست کنم
که اگه اطلاعات اون واحد مسکونی رو وارد کنید نام شخص رو بیار
دیگه بقیشو موندم چیکار کنم خودش اطلاعات اون شخص رو بیاره:min19:
(نمیخوام از Povit Table استفاده کنم)
ممنون میشم کمکم کنید.

[mohammad_ashooryan]

سلام
بپیوست جواب رو ارسال کردم.
سوالی بود در خدمتم.

[a.dal65]

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

[amir_ts]

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

با سلام
این فایل رو مشاهده کنید .

[a.dal65]

ممنون از هر دو بزرگوار
فقط آقا امیر میشه در مورد کارای تابع row توی فرمول یه توضیح کوچیک بدی که اینجا جیکار میکنه و اینکه
سلول a1 و b1 چی هستن که هی تکرار شدن و این 1- چی هستش
ممنون از توجه هت.

[amir_ts]

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

(IF((DATA!$I$2:$I$55=$D$3)*(DATA!$J$2:$J$55=$F$3)* (DATA!$K$2:$K$55=$H$3)
اگر برقرار بود ردیفش رو بده:

(ROW(DATA!$E$2:$E$55
حالا 10 ردیف هست که شرط برقراره:
باتوجه به فرمول در تابع small قرار داره.
حالا باید پارامتر دوم تابع small رو بدیم که تک تک ردیف ها رو برگردونه:
(ROW(a1 یعنی 1 اولین ردیف پیدا شده
از اونجایی که آدرس رو مطلق نکردم با درگ به پایین میشه (ROW(a2 یعنی دومین ردیف پیدا شده و به همین ترتیب (ROW(a3 یعنی سومین ردیف پیدا شده و...
حالا تک تک ردیف ها با درگ به پایین پیدا میشن و تو تابع index وقتی ردیف قرار گرفت با توجه به ستون وارد شده که در اینجا بصورت دستی بوده سلول مشخص شده اطلاعاتش قرار میگیره.
این که چطور 3 شرایط رو میسنجیم مربوط میشه به آرایه ها
باز سوالی بود بفرمایید.

[a.dal65]

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

[amir_ts]

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

[a.dal65]

ممنون
دیروز 2 ساعت داشتم با این ور میرفتم
بالاخره خسته شدم بیخیالش شدم

الان من حدود 800 سطر اطلاعات دارم که وقتی شماره واحد فردی رو وارد میکنم حدود 10 تا 15 ثانیه طول میشکه تا جواب بده
آیا راهی داره سرعت شو بالاتر ببرم ؟(مثلا از TAble استفاده کنم یا NameManger تعریف کنم برای ستون هام)
وقتی هم که با UserForm کار میکنم این تایم حداقل 6-7 برابر میشه و همش در حال Calculatingهست

[amir_ts]

با سلام
تا اونجایی که من اطلاع دارم برای 800 ردیف تقریبا برای آزمایش برقراری فقط سه شرط یک ردیف 512,000,000 ضرب صورت میگیره تا شرایط درست سنجیده و انتخاب بشن طبیعتا این حجم پردازش زمان میبره.
table یا name manager تاثیری تو اصل پردازش نداره و فقط نامگذاری برای درک بهتر فرمول هست.
من فکر نکنم با تغیر فرمول بشه سرعت رو افزایش داد چون تحت هر شرایط باید 800 ردیف شما پردازش بشه .حالا اگر سیستم سخت افزاری کامپیوتر شما قوی تر باشه یک مقدار سرعت افزایش پیدا میکنه ولی این که لحظه ای بشه ی کم دور از ذهنه.
البته شاید اساتید راهکاری داشته باشند که اگر توضیح دادند من هم استفاده میکنم.

[a.dal65]

ممنون بابت راهنماییت.
الان مثلا توی کد ها از این خیلی استفاده شده:
DATA!$J$2:$J$65000
حالا اگه این 65000 حذف بشه وبجاش از ردیف نام جدول استفاده کنم =Table2[واحد]
شاید تاثیر داشته باشه
حالا تست میکنم همه راه های رو و جوابو همینجا میزارم

[a.dal65]

امیر جان با تعریف Table و استفاده ستون جدول ها سرعت خیلی افزایش پیدا کرد الان حدود 2 تا 3 ثانیه
البته فکر کنم بیشترش به این مربوظ میشه که قبلا تو 65000 تا سطر جستجو میکرد الان توی جدولی که فعلا 800 تا سطر داره.

(فقط با UserForm که کار میکنم واقعا عذاب آور شده )
یه راه پیدا کردم
اون هم اینه که وقتی اون شیتی که باعث هنگ کردن میشه در هنگامی که باز نیست calcation رو manaual کردم
الان درست کار میکنه

Private Sub Worksheet_Activate()
Application.Calculation = xlCalculationAutomatic
End Sub

Private Sub Worksheet_Deactivate()
Application.Calculation = xlCalculationManual
End Sub

[amir_ts]

با سلام
دوست عزیز
من الان متوجه شدم شما 800 ردیف اطلاعات داشتید بعد تو فرمول 65000 ردیف آدرس داده بودید این یعنی 65000*65000*65000 میشه 274,625,000,000,000 عملیات ضرب که قطعا" زمانبر و گاهی مشکل ساز میشه.
من فکر کردم 800 ردیف پردازش شما رو کلافه کرده ممنون که اطلاع رسانی کردید.

[mobinsaffarpour]

جناب آقای دلیران

باسلام

امکانش هست فایل نهائی پروژه رو برای دوستان قرار دهید.

[a.dal65]

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

[a.dal65]

یه تغییر کوچیک دارم ممنون میشم انجام بدید :
من میخوام اضافه پرداختی که توی شیت NameManager در ستون L اومده با تاریخش که در ستون M اومده در شیت Reporte بیاد
شیت "نمونه" اون چیزی هست که مد نظر من هست.و با فونت قرمز مشخص کردم.

[amir_ts]

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

[a.dal65]

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

[amir_ts]

با سلام
ببینید دوست عزیز جناب atadaliran (http://forum.exceliran.com/member.php/1342-atadaliran) همان طور که خودتون در ردیف های آخر تو شیت DataUser ردیف های قرمز رو اضافه کردید دقیقا در شیت Report در آخرین ردیف ها قرار میگیره.علتش هم اینه که فرمول بر اساس ردیف ها به ترتیب از بالا به پایین اطلاعات رو لیست میکنه اگه شما ردیف قرمز رنگ رو جابه جا کنید و به وسط لیست DataUser ببرید در شیت Report هم جابه جا میشه.علتش هم اینه که دقیقا ردیف های قرار گیری Report عینا به ترتیب قرار گیری در شیت UserData است.



تنها خواسته من اینکه اون متن قرمز درست سرجاش (بر اساس تاریخ )قرار بگیره

خوب حالا به نظر شما چطور میشه این کار رو کرد؟آیا در شیت DataUser دقیقا بر اساس تاریخ لیست مرتب شده؟ خیر، پس باید فکر دیگه ای کرد.
من با یک روش به نزدیک اون چیزی که شما میخواهید رسیدم البته یک ایراد داره که نمیدونم شما میتونید برای اون فکری کنید.
من ابتدا تمام فرمت تاریخ ها رو یکسان کردم(برای مرتب کردن اونها مشکل پیش نیاد) بعد با یک فرمول دیگه در شیت Report1 (به رنگ زرد) اطلاعات اضافه متراژ رو آوردم و بعد در شیت Report2 بر اساس تاریخ تمام ردیف ها رو مرتب کردم.
اشکال کار تو تاریخ هایی هست که یکی هستند نمیدونم تو لیست اصلی تون چقدر تاریخ های مشابه دارید .اگه تاریخ مشابه داشته باشید در شیت Report2 در ستون تاریخ (B)همه تاریخ ها قرار میگره ولی چون ستون های C تا F شیت Report2 بر اساس همین تاریخ ها (ستونB ) جستجو رو انجام میدن در صورت وجود تاریخ های مشابه جستوی این قسمت با مشکل مواجه میشه .

[امين اسماعيلي]

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

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

دست اخر

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