گروه بندی داده ها در اکسل با استفاده از ابزار Group
آشنایی با گروه بندی داده ها در اکسل
در هنگام کار با اکسل به مرور زمان تعداد سطرها و ستون هایی که داده را در خود جای داده اند زیاد میشود، از این رو کار با آنها مشکلاتی در پی خواهد داشت. داشتن راه حلی برای گروه بندی سطرها و ستون ها به ما کمک میکنند تا بتوانیم سطرها یا ستون هایی را که لازم داریم نمایش دهیم. از طرفی با این کار ساماندهی بیشتری به کارمان داده ایم و مدیریت بهتری بر روی آنها خواهیم داشت. در این مقاله قصد دارم در مورد نحوه گروه بندی داده ها در اکسل (Grouping) داده ها بصورت درختی صحبت کنم. همراه ما باشید تا با این ابزار بسیار مفید اکسل بیشتر آشنا بشید.
این نوع گروه بندی در تهیه WBS پروژه، دسته بندی کردن اطلاعات مالی پروژه، بودجه بندی و یا … کاربرد دارد. می خواهیم ساختاری مشابه شکل۱ ایجاد کنیم.
شکل۱- گروه بندی سطرها و یا ستون ها
برای مثال برای ایجاد زیرمجموعه “محوطه” :
- مطابق شکل ۲ ردیف هایی که باید در یک زیرمجموعه قرار گیرند را انتخاب میکنیم.
- سپس از تب Data بر روی Group کلیک میکنیم.
شکل۲- انتخاب محدوده مورد نظر
دقت داشته باشید که محوطه در ردیف ۵ قرار دارد و ما از ردیف ۶ تا ۹ را انتخاب کرده و بعد از زدن Group، این چهار ردیف به عنوان زیرمجموعه ردیف ۵ یا همان محوطه قرار می گیرند (شکل۳).
همین کار را برای سایر مجموعه ها و زیرمجموعه ها نیز انجام می دهیم.
شکل۳- تبدیل محدوده به یک گروه
نکته: علامت + و – بصورت پیشفرض در پایین زیرمجموعه نمایش داده می شود (شکل۳). برای اینکه این علامت را در بالا و در کنار کلمه محوطه مشاهده کنیم روی زبانه نشان داده شده در شکل ۴ کلیک میکنیم و مطابق شکل ۵ تنظیمات را انجام میدهیم.
شکل۴- تنظیم نحوه نمایش علامت + و –
شکل۵- تنظیمات نمایش مکان + و –
با برداشتن تیک Summary rows below detail علامت + و – هر مجموعه در بالا و در کنار خود مجموعه قرار خواهد گرفت.
برای خارج شدن از این حالت نیز ردیف ها را انتخاب کرده و Ungroup را می زنیم.
پیشنهاد میکنیم حتما مقاله گروه بندی شیت ها در اکسل برای افزایش سرعت و دقت انجام همزمان چند کار بر روی چند شیت رو بخونید.
سلام
من یک سری اموال دارم که بسته به مکان اموال، داده رو مرتب میکنم وقتی تو قسمت مکان وسیله، فیلتر میکنم واسه هر مکان یک سری اموال دارم.
سوالم اینه که چطور می تونم واسه هر مکان بعد آخرین سطرش یک سطر توضیحات بزارم که واسه همه مکانهایی که فیلتر میشن اعمال بشه و اینکه بعد اون توضیح دیگه جدول خالی چاپ نشه در حالیکه سطرهای خالی بعد توضیح توی فایل موجود هستن.
با تشکر.
سلام
سوال نامفهومه
توضیح برای چه کاری؟احتمالا میشه راههای بهتری برای خواستتون ارائه داد…
توضیح بیشتر لازمه.
اگر مایلید سوال رو درگروه تلگرامی که لینکش در فوتر صفحه موجوده مطرح کنید
سلام خسته نباشید
من یک جدول دارم که تعدادی داده تو سطرهاش هست
میخام موقع چاب تا اخرین سطر رو چاپ کنم و بقیه جدول رو چاپ نکنه واسم.
با چ دستوری می تونم اینکار رو بکنم؟
با تشکر
سلام
یعنی این محدوده متغیر هست؟آخرین سطر تغییر میکنه؟؟؟
با سلام به اساتید محترم
من یه سوال دارم که خیلی براش وقت گذاشتم راههای مختلفی رو هم امتحان کردم ولی نشد.ممنون میشم اگر راهنماییم کنید.
من یه برنامه ERP نوشتم برای شرکتی که در آن کار میکنم. در شیت برنامه ریزی تولید برای محاسبه واریانس در انتهای گزارشات عملکرد روزانه تولید، برای هر OP (عملیات) دلیل انحراف را گذاشتم. حالا برای محاسبه اینکه از هر دلیل انحراف مثلا A، چند درصد انحراف داشتیم نمی تونم کاری بکنم. چون محاسبه در یک ردیف میخوام انجام بشه که مجموعه ای از دلیل ها رو دارم و میخوام اگر مثلا A بود خونه های بعدش رو که درصد اون هست رو باهم جمع کنه. حالا نمیدونم برای اینکه یک مجموعه منفصل یا گسسته رو مساوی یک پارامتر قرار بدم و در صورت برقرار بودن اون شرط، مجموعه دیگری که متناظر با اون هست رو با هم جمع کنم، چیکار کنم. از توابع آرایه ای و sumproduct هم استفاده کردم ولی جواب نداد. اگر لازم هست بفرمایید فایل رو هم براتون میفرستم.
مرررررسی از سایت عالیتون
سلام و درود
به نظر میرسه با Sumif به نتیجه میرسید
لینک زیر رو بخونید
https://excelpedia.net/sumifs-function/
با سلام و ممنون از مطالبتون
ده روستا دارم که بر اساس میانگین میخوام طبقه بندی کنم و نمودارش رو بدست بیارم ممنون میشم راهنماییم کنید.
با سپاس.
سلام
منظورتون مثلا میانگین جمعیت هست؟
و اینکه چطور طبقه بندی کنید؟؟؟
شاید منظورتون نمودار هیستوگرام باشه. پیشنهاد میکنم این پست رو بخونید:
https://excelpedia.net/histogram-chart/
سلام میشه این فایلو اکسپورت کرد تو پریماورا؟
سلام
اگر منظورتون اینه که همین گروه بندی که تو اکسل ایجاد کردید تو پریماورا هم برقرار باشه بعید میدونم شدنی باشه.
سلام خدمت آقا سامان عزیز
چرا سرعت کار اکسل زمانیکه تعداد رکوردهای بالای ۱۰۰۰۰۰ رکورد است کتد میشود و چه راهکاری دارید آیا نرم افزار اکسس قوی تره از اکسله؟
از طرف پسر عموی شما
سلام علیرضا جان
موضوع سرعت تو اکسل به چندین نکته مربوط میشه که اگه رعایت بشه معمولا نیازی به استفاده از نرم افزارهای دیگه نیست.
اینکه شما از هیچگونه فرمت دهی روی رکوردهاتون انجام ندین.
اینکه از چه ابزاری برای ایجاد گزارش استفاده کنید.
اینکه نوع فرمول نویسی شما برای گزارش بهینه هست یا نه.
تعداد اشیا و نام های خیلی زیاد که عمدا یا سهوا در اکسل ایجاد شده باشه.
فرمول نویسی آرایه ای در مقیاس بزرگ در فایل
لینک های زیاد به فایل های خارجی که در هنگام Calculation بسیار کند خواهند شد.
و …
نهایتا اگر با رعایت همه این موارد همچنان فایل کند باشه دو راه وجود داره:
۱- زمان محاسبات رو به جای Automatic به Manual تغییر بدید که هر بار با کوچکترین تغییر در فایل، کل فایل محاسبه نشه.
۲- اتصال پایگاه های داده (مثل Access و SQL) به اکسل
مخلص پسر عموی عزیز
آقا سامان عزیز خیلی گلی
با تشکر از پاسخ جامع شما ، گزینه Manual در کاهش زمان محاسبات بسیار کمک میکنه
ضمنا بهترین حالت مقایسه تمام داده های دو جدول (کلیه رکوردهایی که id مشترک دارند) بصورتی که داده های غیر همسان مشخص شود(حداقل رنک سلول عوض بشه یا آنها را جداگتنه نمایش بده)
با تشکر فراوان از راهنمایی شما
با سلام
آیا میتوان برای باز شدن گروه رمز گذاشت؟یعنی اگه کسی خواست + را بزنه و گروه رو باز کنه ازش رمز بخواد؟
سلام
زمانیکه شیت پروتکت باشه و ردیف ها گروه بندی باشند امکان باز کردن و یا بستنشون نیست مگر اینکه شیت Unprotect بشه که باید رمز بدن.
در واقع میتونید با پروتکت کردن شیت این کار رو انجام بدید.
این کارو قبلا انجام دادم ولی بنتیجه دلخواه نرسیدم،ما یک فایل اطلاعات داریم که فقط یک گروه رو میخوایم از دید بقیه مخفی کنیم و اگر کسی خواست گروه را باز کنه نتونه و رمز بخواد،اصلا همچین کاری در اکسل مقدوره؟!حتی با برنامه نویسی نمیشه این کارو انجام داد؟
این کار رو با کدنویسی میشه انجام داد
باید در رویداد Selection_Change شیت و با بررسی خاصیت Hidden مربوط سطر موردنظر این کار رو انجام داد.
تشکر میکنم از اینکه وقت میگذارید و جواب میدید،لطف می کنید کدهای مربوطه رو بنویسید؟(من تازه کارم در برنامه نویسی اکسل)
ممنون
خواهش میکنم
از همچین کدی میتونید استفاده کنید:
If Not Sheet1.Rows(9).Hidden Then Sheet1.Rows(9).ShowDetail = False If InputBox("Enter Pass to Unhide") = "Pass" Then Sheet1.Rows("9").ShowDetail = True End If End Ifولی مشکلی که هست اینه که تو اکسل رویدادی برای تشخیص زمان کلیک کردن روی دکمه های باز و بسته کردن گروه وجود نداره و عملا نتیجه رو بی فایده میکنه
سلام با تشکر از راهنماییتون
من دنبال ویژگی group by اس کیو ال در اکسل هستم .
به صورتیکه تعداد محتویات ستون ها رو بر اساس مقادیر یک یا چند ستون دسته بندی آماری کنم
در صورت امکان راهنمایی فرمایید
سلام
برای اینکه از Queryهای SQL تو اکسل استفاده کنید باید افزونه Power Pivot رو فعال کنید و در قسمت Manage تب Power Pivot وارد محیط مدیریت Power Pivot میشید.
تو این محیط در تب Get External Data میتونید به پایگاه داده SQL وصل بشید و با استفاده از Queryها اطلاعات رو در قالب جدول فراخوانی کنید و تو اکسل استفاده کنید.
ممنون از اینکه وقت گذاشتید و پاسخ دادید.
سلام وقتتون بخیر.
من میخوام در یک ستون فقط یک داده داشته باشم که در ستون دوم ۳ مورد رو در بربگیره. لطفا راهنماییم کنید. با تشکر
سلام
سوالتون نامفهومه…… متوجه منظورتون نشدم
با مثال توضیح بدید
سلام.چه فایده ای داره این همه اکسل کار کردی.
سلام
قطعا شما اگه به نرم افزاری که یکی از اساسی ترین و پرکاربرد ترین ابزارهای مورد نیاز هر کاری باشه تسلط داشته باشید، به راحتی مقادیر خیلی زیادی در زمان کاری صرفه جویی میکنید.
از طرفی این تسلط موجب میشه شما زمان بیشتری برای مدیریت کارهاتون داشته باشید.
این تسلط منجر میشه شما بتونید داده های خام کاریتون رو با ابزارهای قدرتمند اکسل پردازش و تحلیل های بسیار مفیدی برای کسب و کار خودتون و مدیرانتون ایجاد کنید.
و هزاران فایده دیگه در این مجال نمیگنجه
سلام .در اکسل ۲۰۰۷میخوام لیست کشویی درست کنم ولی نمیشه .به این صورت لیست موردنظر درشیت ۱میباشد ودرشیت ۲میخوام از این لیست استفاده کنم .ولی زمانی که میخوام در دیتا ولیدیشن میخوام برم درشیت ۱ این اتفاق نمی افته ولی اگه لیست درهمون شیت ۲باشه انجام میشه .ممنون میشم اگه راهنمایی کنید
برای اینکار میتونید محدوده ای که لیست رو در خودش داره با استفاده از Name نامگذاری کنید و نهایتا هر جا که خواستید اون لیست رو داشته باشید کافیه در Data Validation و در بخش Source، بنویسید:
در اینجا فرض شده که نامی که برای محدوده انتخاب میکنید MyList هست.