گروه بندی داده ها در اکسل با استفاده از ابزار Group
آشنایی با گروه بندی داده ها در اکسل
در هنگام کار با اکسل به مرور زمان تعداد سطرها و ستون هایی که داده را در خود جای داده اند زیاد میشود، از این رو کار با آنها مشکلاتی در پی خواهد داشت. داشتن راه حلی برای گروه بندی سطرها و ستون ها به ما کمک میکنند تا بتوانیم سطرها یا ستون هایی را که لازم داریم نمایش دهیم. از طرفی با این کار ساماندهی بیشتری به کارمان داده ایم و مدیریت بهتری بر روی آنها خواهیم داشت. در این مقاله قصد دارم در مورد نحوه گروه بندی داده ها در اکسل (Grouping) داده ها بصورت درختی صحبت کنم. همراه ما باشید تا با این ابزار بسیار مفید اکسل بیشتر آشنا بشید.
این نوع گروه بندی در تهیه WBS پروژه، دسته بندی کردن اطلاعات مالی پروژه، بودجه بندی و یا … کاربرد دارد. می خواهیم ساختاری مشابه شکل۱ ایجاد کنیم.
شکل۱- گروه بندی سطرها و یا ستون ها
برای مثال برای ایجاد زیرمجموعه “محوطه” :
- مطابق شکل ۲ ردیف هایی که باید در یک زیرمجموعه قرار گیرند را انتخاب میکنیم.
- سپس از تب Data بر روی Group کلیک میکنیم.
شکل۲- انتخاب محدوده مورد نظر
دقت داشته باشید که محوطه در ردیف ۵ قرار دارد و ما از ردیف ۶ تا ۹ را انتخاب کرده و بعد از زدن Group، این چهار ردیف به عنوان زیرمجموعه ردیف ۵ یا همان محوطه قرار می گیرند (شکل۳).
همین کار را برای سایر مجموعه ها و زیرمجموعه ها نیز انجام می دهیم.
شکل۳- تبدیل محدوده به یک گروه
نکته: علامت + و – بصورت پیشفرض در پایین زیرمجموعه نمایش داده می شود (شکل۳). برای اینکه این علامت را در بالا و در کنار کلمه محوطه مشاهده کنیم روی زبانه نشان داده شده در شکل ۴ کلیک میکنیم و مطابق شکل ۵ تنظیمات را انجام میدهیم.
شکل۴- تنظیم نحوه نمایش علامت + و –
شکل۵- تنظیمات نمایش مکان + و –
با برداشتن تیک Summary rows below detail علامت + و – هر مجموعه در بالا و در کنار خود مجموعه قرار خواهد گرفت.
برای خارج شدن از این حالت نیز ردیف ها را انتخاب کرده و Ungroup را می زنیم.
پیشنهاد میکنیم حتما مقاله گروه بندی شیت ها در اکسل برای افزایش سرعت و دقت انجام همزمان چند کار بر روی چند شیت رو بخونید.
در یک سلول با Alt+inter دو سطر ایجاد شده
با چه فرمولی میتونم برگردونمش که یک سطر بشه ؟
درود
با ابین فرمول میتونید alt enter رو به فاصله معمولی تبدیل کنید
=SUBSTITUTE(A1,CHAR(10),” “)
سلام
من دیتا ها رو گروه می کنم
در انتها وقتی جمع می کنم یکدسته از اطلاعات پایین از حالت گروه بندی خارج میشن
دلیلش چیه
درود بر شما
فرمول رو در حالت گروه های جمع شده ننویسید، اون اخری رو نمیگیرید در محدوده ها
سلام و عرض خسته نباشید. راهی جهت تایپ دلخواه نام گروه بندی بجای ۱ ۲ ۳ و….. وجود دارد؟
درود بر شما
تا اونجا که میدونم خیر (اگر منظورتون شماره های کنار علامت + و – هست)
سلام . استاد محترم چطوری می تونم عضو گروه شما باشم و از اطلاعات خوب شما استفاده کنم
درود بر شما
لینک در فوتر سایت هست
لینگ سوپرگروه تلگرام
به بهترین شکل ممکن توضیح داده بودید.سایت های زیادی رو گشتم ولی توضیحاتشون واضح که نبود هیچ بدتر گیج کننده هم بود.ممنونم از تدریس عالی شما
به یک گروه بندی نیاز دارم که مانند SUBTOTAL جمع یک ستون را بر حسب دو ستون دیگر بصورت شرطی در پایان جدول ارائه دهد. چون تعداد رکوردها زیاد هست امکان استفاده از فیلتر و عملیات دستی نیست. بعنوان مثال در یک ستون کدهای فروش داریم که تنوع آنها بسیار زیاد و تکرار هم شده در ستون دیگر دو رقم سال را داریم و در ستون سوم مبلغ فروش. حالا در پایین جدول یا زیر هر کد فروش میزان فروش تفکیک شده برای هر سال و هر کد در آن سال را بصورت خلاصه در یک جدول نیاز داریم (ترکیبی از SUBTOTAL و یک شرط هست) خواستم از SUMIF استفاده کنم تعداد رکوردها زیاد بود و باید برای هر کد فروش یک SUMIF مجزا می نوشتم که بسیار زمانگیر و امکانش نبود. به نظرم اگر در SUBTOTAL بشه از شرط استفاده کرد مشکل حل میشه یعنی همه مبالغ فروش که کد فروش آنها یکی است به تفکیک سال فروش
سلام
برای انجام این کار بهتره از Pivot Table استفاده کنید.
سلام
یه فایل اکسل دارم ۳۰۰۰۰سطره میخوام به فایلهای ۱۰۰۰تایی و مجزا تبدیلش کنم لطفاً راهنمایی میفرمایید. با تشکر
درود
اگر میتونید کدنویسی VBA انجام بدید
اگر نه، باید یک ستون کمکی داشته باشید شماره گذاری کنید. هزارتای اول ۱، هزارتای دوم ۲ و … بعد پیوت بگیرید و report filter page
این کار داده ها رو به سی شیت تبدیل میکنه. بعد دونه دونه انتقال بدید به یک فایل جدید و سیو کنید!
سلام
من یه فایل دارم که هرکاری میکنم گروپ ایجاد کنم، ولی متاسفانه ایجاد نمیکنه.
چه دلیلی داره و چجوری باید این مشکل برطرف بشه
سلام . ممنون از توضیح عالیتون. حالا اگر بخوام یک گروه را که چند زیرمجموعه داره و زیرمجموعه هاش هم چند زیرمجوعه دارند را وارد یک گروه دیگر کنم آیا امکانش هست ؟ بدون اینکه دوباره گروه و زیرگروه ها را بخوام تعریف کنم.
یعنی مثلا در مثال بالا بخواهم “ماشین آلات و تکنولوژی” را به همراه زیرمجموعه اش در پایین “میکسر” قرار دهم. به اینصور ت ساختمانهای صنعتی داراری سه زیرمجموعه می شوند که شامل “میکسر” و “سوله ها” و “ماشین آلات و تکنولوژی” است.
با عرض سلام و خسته نباشید
چطور میتونم از یک جدول با ۹۰ سطر و ۲۹ ستون ، داده های مد نظر خودم رو که دارای مثلا ستون ۱۵ و سطر های ۱۱.۲۲،۳۳، هستند رو استخراج کنم ؟
با تشکر
درود بر شما
میتونید از تابع Index استفاده کنید
سلام انتخاب گروهی سلول ها ( به صورت یکی در میان ) جهت برش دادن سلول ها و انتقال به سلول دیگر
ممنون
درود
اگر منظورتون از برش، cut هست
سلول ناپیوسته رو نمیتونید cut کنید
سلام وقت بخیر
در اکسل تعداد زیادی اسم داریم که خیلی هاشون تکراری هستن ولی در روزهای مختلف آمده اند اگر بخواهیم یه لیست از تک تک نفرات داشت باشیم که زیر هم قرار گرفتن چه باید بکنیم
درود بر شما
تکراری ها رو با این ابزار میتونید حذف کنید
https://excelpedia.net/remove-duplicate/
سلام وقت بخیر ممنون از زحمات شما
برای جابجا کردن این لیست کشویی با همه زیرمجموعه چه کار باید کرد؟
با تشکر فراوان
درود بر شما
اگر منظور از لیست کشویی همین گروه بندی ها و subtotal هست، باید با cut و paste جابجا کنید
سلام.
آیا راهی برای پنهان (Hide)کردن نواری که بعد از گروه بندی ایجاد می شود؛ همان نواری که علامت های مثبت و منفی در آن قرار می گیرند، وجود دارد؟
درود بر شما
فکر نمیکنم راهی باشه. ولی خب از طرفی تنها راه باز و بسته کردن گرو هها همون نوار هست. چرا باید پنهان بشه؟
فکر می کنم منظورشون از نظر شکل ظاهری اون هست. من یه مدل از این کار رو دیدم که + – رو روی خود ردیف ها نشون میده. نه اینکه یه نوار بعد از گروه بندی ایجاد بشه. امکانش هست در این مورد راهنمایی بفرمایید؟
خروجی پیوت تیبل به این صورت ظاهر میشه
گروه بندی این کار و نمیتونه بکنه
اگر گروه بندی کنیم میتونیم توی vlookup استفاده کنیم ؟
بله.نباید مشکلی باشه
با سلام سوالی داشتم.
وقتی که زیر مجموعه درست میکنیم این در نمودار خروجی نمایش داده نمیشه. یا همه با هم میان بدون زیر مجموعه یا در طرح زیر مجموعه ای یکی از زیر مجموعه ها در مجموعه اصلی میاد.
چطور باید خروجی نموداری گرفت که جای زیرمجموعه و مجموعه با هم خلط نشه؟
درود بر شما
بصورت کلی این ابزار کارش هاید کردنه. برای همین بصورت پیش فرض داده های هاید در نمودار نمایش داده نمیشن مگر اینکه تنظیمات نمودار رو تغییر بدید
برای اینکه داده ها در نمودار هب درستی نمایش داده بشن باید داده ها فراخوانی بشن و درست چیده بشن. با این ابزار نمیتونید روی نمودار کنترلی داشته باشید
درود بر شما موفق پیروز باشید
سلام من حدود ۲۰۰تا سلول در یک شیت اکسل گروه بندی کردم که میخوام هرکدام به شیت جداگانه تبدیل بشه امکان این کار هست ؟
سلام
دو راه برای انجام این کار وجود داره:
1- از روی داده های اصلی (در صورتیکه ساختار مناسب داشته باشه) Pivot Table بسازید و فیلدی که بر اساس آن گروه بندی کردید رو به قسمت Filter منتقل کنید و از بخش Analyze>Options>Show report filter page این شیت ها رو ایجاد کنید.
2- با استفاده از VBA که ممکنه با توجه به ساختار فایل شما زمان بر باشه.
با سلام
مطلبتون عالی بود
میخواستم ببینم آیا با آرایه هم کار کردید ؟؟ منظورم فرمول نویسی آرایه ای نیست ، منظورم خود آرایه است ، پشته ای از اطلاعات است .
اصلا آیا اکسل چیزی به نام آرایه رو پشتیبانی میکنه یا خیر ؟؟
درود بر شما
در برنامه نویسی VBA ، مبحث آرایه ها و ذخیره اطلاعات رو داریم که مبحث پیشرفته ای هم هست
از قضا vba هم کار کردم .
ولی من میخاستم از آرایه توی شیت ها استفاده کنم .
چطور میتونم فایلی رو براتون بفرستم و توی اون فایل مشکلم رو براتون بگم .
سوالات رو در گروه اکسل پدیا مطرح کنید
لینک در فوتر سایت موجود هست
سلام.من یه فایل دارم که نه لایه هست ولی یکی از لایه ها رو یا تو گروه بندی نمیاره یا با لایه ماقبلش ادغام میکنه.چه راه حلی هست؟
درود
بله محدودیت ۸ لایه ای داره
جایگزین رو نمیدونم
ممکنه افزونه باشه براش
اما من جستجو نکردم
شایدم بهتر باشه ببرید توی پیوت تیبل و همه رو بذارید داخل row
سلام و عرض ادب
ببخشید اگه اطلاعات در یک شیت باشد و در شیت دیگر بخواهیم آیکون های آبشاری قرار بدهیم باید به چه شکل اقدام کرد
ممنون
مشکل بنده رو حل کرد. خیلی ممنون
سلام
تو یه شیت اول یه جدول دارم که چندتا ستون داره ،ستون اول نام استان،ستون دوم نام شهرستان،ستونهای بعدی سودوزیان و فروش و بقیه شاخص ها
در شیت دوم یه لیست کشویی درست کردم بر اساس نام استان
حالا چجوری باید یه جدول درست کنم که وقتی از لیست کشویی نام استان رو انتخاب میکنم جدول مربوط به اون استان برام با زیر مجموعه شهرستانهاش ساخته بشه و وقتی شیت اول تغییر میکنه شیت دوم به روز بشه
ممنون میشم کمکم کنید
درود بر شما
لینک زیر رو مطالعه کنید:
https://excelpedia.net/related-list/
با سلام
خیلی دلم میخواد ببینم شما بیشتر از اکسل سر در می یارید یا من ؟!!
سلام من میخواهم در اکسل ماکرویی بنویسم که در آن با درج یک تاریخ در سلولم بصورت خودکار آن را در شیت دیگرم که تاریخ هارا نوشته ام پیدا کند از فیلتر هم استفاده کرده ام اما جواب نمیدهد راهنماییم میکنید
درود بر شما
چرا فیلتر جواب نداده؟ باید بررسی بشه داده هاتون
سوال رو در گروه تلگرامی بذارید(لینک گروه در فوتر صفحه اصلی سایت موجوده)
فایلتون رو هم بذارید
تا بشه بررسی کرد….
سلام. اگر بخواهم سطرها رو بر اساس نامشون مرتب کنم این گروهبندی من بهم میریزه. چکار باید کنم که در عین حفظ گروهبندی، اطلاعاتم سورت هم بشه؟ سپاس
درود بر شما
ببینید برای این کار باید ترفندی بزنید
باید برای هر دسته، یک ستون ایجاد کنید و کد تعریف کنید. مثلا دسته اول عدد ۱
دسته دوم عدد ۲
بعد اینطوری موقع سورت کردن
اول ستون کدها رو سورت میکنید بعد در سزح بعدی، ستون داده های هر دسته یا اصطلاحا گروه رو
برای ایجاد سطح در سورت پست زیر رو بخونید:
https://excelpedia.net/sort/
سلام
من یک سری اموال دارم که بسته به مکان اموال، داده رو مرتب میکنم وقتی تو قسمت مکان وسیله، فیلتر میکنم واسه هر مکان یک سری اموال دارم.
سوالم اینه که چطور می تونم واسه هر مکان بعد آخرین سطرش یک سطر توضیحات بزارم که واسه همه مکانهایی که فیلتر میشن اعمال بشه و اینکه بعد اون توضیح دیگه جدول خالی چاپ نشه در حالیکه سطرهای خالی بعد توضیح توی فایل موجود هستن.
با تشکر.
سلام
سوال نامفهومه
توضیح برای چه کاری؟احتمالا میشه راههای بهتری برای خواستتون ارائه داد…
توضیح بیشتر لازمه.
اگر مایلید سوال رو درگروه تلگرامی که لینکش در فوتر صفحه موجوده مطرح کنید
سلام خسته نباشید
من یک جدول دارم که تعدادی داده تو سطرهاش هست
میخام موقع چاب تا اخرین سطر رو چاپ کنم و بقیه جدول رو چاپ نکنه واسم.
با چ دستوری می تونم اینکار رو بکنم؟
با تشکر
سلام
یعنی این محدوده متغیر هست؟آخرین سطر تغییر میکنه؟؟؟
با سلام به اساتید محترم
من یه سوال دارم که خیلی براش وقت گذاشتم راههای مختلفی رو هم امتحان کردم ولی نشد.ممنون میشم اگر راهنماییم کنید.
من یه برنامه ERP نوشتم برای شرکتی که در آن کار میکنم. در شیت برنامه ریزی تولید برای محاسبه واریانس در انتهای گزارشات عملکرد روزانه تولید، برای هر OP (عملیات) دلیل انحراف را گذاشتم. حالا برای محاسبه اینکه از هر دلیل انحراف مثلا A، چند درصد انحراف داشتیم نمی تونم کاری بکنم. چون محاسبه در یک ردیف میخوام انجام بشه که مجموعه ای از دلیل ها رو دارم و میخوام اگر مثلا A بود خونه های بعدش رو که درصد اون هست رو باهم جمع کنه. حالا نمیدونم برای اینکه یک مجموعه منفصل یا گسسته رو مساوی یک پارامتر قرار بدم و در صورت برقرار بودن اون شرط، مجموعه دیگری که متناظر با اون هست رو با هم جمع کنم، چیکار کنم. از توابع آرایه ای و sumproduct هم استفاده کردم ولی جواب نداد. اگر لازم هست بفرمایید فایل رو هم براتون میفرستم.
مرررررسی از سایت عالیتون
سلام و درود
به نظر میرسه با Sumif به نتیجه میرسید
لینک زیر رو بخونید
https://excelpedia.net/sumifs-function/
با سلام و ممنون از مطالبتون
ده روستا دارم که بر اساس میانگین میخوام طبقه بندی کنم و نمودارش رو بدست بیارم ممنون میشم راهنماییم کنید.
با سپاس.
سلام
منظورتون مثلا میانگین جمعیت هست؟
و اینکه چطور طبقه بندی کنید؟؟؟
شاید منظورتون نمودار هیستوگرام باشه. پیشنهاد میکنم این پست رو بخونید:
https://excelpedia.net/histogram-chart/
سلام میشه این فایلو اکسپورت کرد تو پریماورا؟
سلام
اگر منظورتون اینه که همین گروه بندی که تو اکسل ایجاد کردید تو پریماورا هم برقرار باشه بعید میدونم شدنی باشه.
سلام خدمت آقا سامان عزیز
چرا سرعت کار اکسل زمانیکه تعداد رکوردهای بالای ۱۰۰۰۰۰ رکورد است کتد میشود و چه راهکاری دارید آیا نرم افزار اکسس قوی تره از اکسله؟
از طرف پسر عموی شما
سلام علیرضا جان
موضوع سرعت تو اکسل به چندین نکته مربوط میشه که اگه رعایت بشه معمولا نیازی به استفاده از نرم افزارهای دیگه نیست.
اینکه شما از هیچگونه فرمت دهی روی رکوردهاتون انجام ندین.
اینکه از چه ابزاری برای ایجاد گزارش استفاده کنید.
اینکه نوع فرمول نویسی شما برای گزارش بهینه هست یا نه.
تعداد اشیا و نام های خیلی زیاد که عمدا یا سهوا در اکسل ایجاد شده باشه.
فرمول نویسی آرایه ای در مقیاس بزرگ در فایل
لینک های زیاد به فایل های خارجی که در هنگام Calculation بسیار کند خواهند شد.
و …
نهایتا اگر با رعایت همه این موارد همچنان فایل کند باشه دو راه وجود داره:
1- زمان محاسبات رو به جای Automatic به Manual تغییر بدید که هر بار با کوچکترین تغییر در فایل، کل فایل محاسبه نشه.
2- اتصال پایگاه های داده (مثل Access و SQL) به اکسل
مخلص پسر عموی عزیز
آقا سامان عزیز خیلی گلی
با تشکر از پاسخ جامع شما ، گزینه Manual در کاهش زمان محاسبات بسیار کمک میکنه
ضمنا بهترین حالت مقایسه تمام داده های دو جدول (کلیه رکوردهایی که id مشترک دارند) بصورتی که داده های غیر همسان مشخص شود(حداقل رنک سلول عوض بشه یا آنها را جداگتنه نمایش بده)
با تشکر فراوان از راهنمایی شما
با سلام
آیا میتوان برای باز شدن گروه رمز گذاشت؟یعنی اگه کسی خواست + را بزنه و گروه رو باز کنه ازش رمز بخواد؟
سلام
زمانیکه شیت پروتکت باشه و ردیف ها گروه بندی باشند امکان باز کردن و یا بستنشون نیست مگر اینکه شیت Unprotect بشه که باید رمز بدن.
در واقع میتونید با پروتکت کردن شیت این کار رو انجام بدید.
این کارو قبلا انجام دادم ولی بنتیجه دلخواه نرسیدم،ما یک فایل اطلاعات داریم که فقط یک گروه رو میخوایم از دید بقیه مخفی کنیم و اگر کسی خواست گروه را باز کنه نتونه و رمز بخواد،اصلا همچین کاری در اکسل مقدوره؟!حتی با برنامه نویسی نمیشه این کارو انجام داد؟
این کار رو با کدنویسی میشه انجام داد
باید در رویداد Selection_Change شیت و با بررسی خاصیت Hidden مربوط سطر موردنظر این کار رو انجام داد.
تشکر میکنم از اینکه وقت میگذارید و جواب میدید،لطف می کنید کدهای مربوطه رو بنویسید؟(من تازه کارم در برنامه نویسی اکسل)
ممنون
خواهش میکنم
از همچین کدی میتونید استفاده کنید:
ولی مشکلی که هست اینه که تو اکسل رویدادی برای تشخیص زمان کلیک کردن روی دکمه های باز و بسته کردن گروه وجود نداره و عملا نتیجه رو بی فایده میکنه
سلام با تشکر از راهنماییتون
من دنبال ویژگی group by اس کیو ال در اکسل هستم .
به صورتیکه تعداد محتویات ستون ها رو بر اساس مقادیر یک یا چند ستون دسته بندی آماری کنم
در صورت امکان راهنمایی فرمایید
سلام
برای اینکه از Queryهای SQL تو اکسل استفاده کنید باید افزونه Power Pivot رو فعال کنید و در قسمت Manage تب Power Pivot وارد محیط مدیریت Power Pivot میشید.
تو این محیط در تب Get External Data میتونید به پایگاه داده SQL وصل بشید و با استفاده از Queryها اطلاعات رو در قالب جدول فراخوانی کنید و تو اکسل استفاده کنید.
ممنون از اینکه وقت گذاشتید و پاسخ دادید.
سلام وقتتون بخیر.
من میخوام در یک ستون فقط یک داده داشته باشم که در ستون دوم ۳ مورد رو در بربگیره. لطفا راهنماییم کنید. با تشکر
سلام
سوالتون نامفهومه…… متوجه منظورتون نشدم
با مثال توضیح بدید
سلام.چه فایده ای داره این همه اکسل کار کردی.
سلام
قطعا شما اگه به نرم افزاری که یکی از اساسی ترین و پرکاربرد ترین ابزارهای مورد نیاز هر کاری باشه تسلط داشته باشید، به راحتی مقادیر خیلی زیادی در زمان کاری صرفه جویی میکنید.
از طرفی این تسلط موجب میشه شما زمان بیشتری برای مدیریت کارهاتون داشته باشید.
این تسلط منجر میشه شما بتونید داده های خام کاریتون رو با ابزارهای قدرتمند اکسل پردازش و تحلیل های بسیار مفیدی برای کسب و کار خودتون و مدیرانتون ایجاد کنید.
و هزاران فایده دیگه در این مجال نمیگنجه
سلام .در اکسل ۲۰۰۷میخوام لیست کشویی درست کنم ولی نمیشه .به این صورت لیست موردنظر درشیت ۱میباشد ودرشیت ۲میخوام از این لیست استفاده کنم .ولی زمانی که میخوام در دیتا ولیدیشن میخوام برم درشیت ۱ این اتفاق نمی افته ولی اگه لیست درهمون شیت ۲باشه انجام میشه .ممنون میشم اگه راهنمایی کنید
برای اینکار میتونید محدوده ای که لیست رو در خودش داره با استفاده از Name نامگذاری کنید و نهایتا هر جا که خواستید اون لیست رو داشته باشید کافیه در Data Validation و در بخش Source، بنویسید:
در اینجا فرض شده که نامی که برای محدوده انتخاب میکنید MyList هست.