پرسش انجام کار پیوت با فرمول جهت گزارش سازی

[armey]

با سلام خدمت دوستان عزیز و اعضای محترم

میخواهم همان کار پیوت را با فرمول انجام دهم


1 - محاسبه شود همایون چند مرتبه از ما هارد خریده
2- چه تعداد خریده
3- اسم تمام خریداران لیست شود

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

فایل پیوست میشود
با سپاس و تجدید احترامموجودی تعدادی(90).xlsx

[armey]

عرض ادب خدمت دوستان محترم و اعضای عزیز
اگر راهنمایی بفرمایید ممنونتان می شوم

[M_ExceL]

با سلام خدمت دوستان عزیز و اعضای محترم

میخواهم همان کار پیوت را با فرمول انجام دهم


1 - محاسبه شود همایون چند مرتبه از ما هارد خریده
2- چه تعداد خریده
3- اسم تمام خریداران لیست شود

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

فایل پیوست میشود
با سپاس و تجدید احترام[ATTACH]21769[/ATTACH]

با سلام،

sub report_data()
dim a1 as variant
dim a2 as variant
dim a3 as variant
dim a4 as variant

lstr = cells(rows.count, 1).end(3).row
range("m5:o" & lstr).clearcontents
a1 = uvalues(range("b2:b" & lstr))
a2 = uvalues(range("f2:f" & lstr))
cnt = ubound(a1) * ubound(a2)
redim a3(1 to cnt, 1 to 3)
r = 1
for each i in a1
    for each s in a2
        a3(r, 1) = i
        a3(r, 2) = s
        r = r + 1
    next
next
a4 = range("b2:k" & lstr).value

for n = lbound(a3) to ubound(a3)
    a3(n, 3) = 0
    for i = lbound(a4) to ubound(a4)
        if a3(n, 1) = a4(i, 1) and a3(n, 2) = a4(i, 5) then
            a3(n, 3) = a3(n, 3) + a4(i, 10)
        end if
    next
next

range("m5:o" & cnt + 4).value = a3

end sub

function uvalues(rng as range) as variant
dim coll as new collection
dim arr as variant
dim arru() as variant
arr = rng.value
on error resume next
for each itm in arr
    if itm <> empty then
        coll.add cstr(itm), cstr(itm)
    end if
next
on error goto 0
redim arru(1 to coll.count)
for i = 1 to coll.count
    arru(i) = coll.item(i)
next
uvalues = arru
end function

توضیح :
در فایل پیوست ابتدا ماکرو را فعال کرده سپس روی باتنی که گذاشتم کلیک کنید و نتیجه را بررسی کنید.

[armey]

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


موضوع اینه:
سه ستون داریم
نام خریدار
نام کالا
تعداد

هدف :
هر خریدار از هر کالا چه تعداد خرید داشته

[saed.rasa]

سلام
با اجازه دوستان
تست کنید لطفا!
پیوت تیبل باز هم پیشنهاد می شود

[armey]

سلام
با اجازه دوستان
تست کنید لطفا!
پیوت تیبل باز هم پیشنهاد می شود

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

مشکل این بود مثلا در مثال مذکور اگه بخواییم یه جدول داشته باشیم از نام خریدار ها اگه موردی خرید نداشته باشه در پیوت نشون نمیده یعنی موارد صفر دیده نمیشه
با فرض وجود 40 خریدار اگه یک خریدار از کالای مورد نظر خرید نکرده باشه در لیست نمایش داده نمیشه و 39 ردیف دیده میشه مضاف بر اینکه ترتیبشون هم ثابت نیست

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

[saed.rasa]

سلام

فکر می کنم که تمامی مشکلات اشاره شده ازجمله ترتیب - نشان دادن داده های صفر - مرجع قرار دادن سلول و حتی رفرش خودکار... همه در Pivot Table براحتی قابل اجرا می باشد که خودتان بهتر است در این موردها تحقیق فرمایید.

[armey]

سلام

فکر می کنم که تمامی مشکلات اشاره شده ازجمله ترتیب - نشان دادن داده های صفر - مرجع قرار دادن سلول و حتی رفرش خودکار... همه در Pivot Table براحتی قابل اجرا می باشد که خودتان بهتر است در این موردها تحقیق فرمایید.

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