سبد خرید
0

هیچ محصولی در سبد خرید نیست.

تمام دسته ها
  • تمام دسته ها
  • Power BI
  • Power Pivot
  • Power Query
  • ابزارها
  • افزونه ها
  • اکسل مدیا
  • توابع اکسل
  • دسته‌بندی نشده
  • دیده بان
  • گوگل شیت
  • مسائل کاربردی
  • معرفی کتاب
  • نمودار ها
  • وی بی - ماکرو

گروه بندی داده ها در اکسل با استفاده از ابزار Group

گروه بندی داده ها در اکسل
۴.۶/۵ - (۱۸ امتیاز)

آشنایی با گروه بندی داده ها در اکسل

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

این نوع گروه بندی در تهیه WBS پروژه، دسته بندی کردن اطلاعات مالی پروژه، بودجه بندی و یا … کاربرد دارد. می خواهیم ساختاری مشابه شکل۱ ایجاد کنیم.

گروه بندی داده ها در اکسل - گروه بندی سطرها و یا ستون ها

شکل۱- گروه بندی سطرها و یا ستون ها

برای مثال برای ایجاد زیرمجموعه “محوطه” :

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

گروه بندی داده ها در اکسل - انتخاب محدوده مورد نظر

شکل۲- انتخاب محدوده مورد نظر

 

دقت داشته باشید که محوطه در ردیف ۵ قرار دارد و ما از ردیف ۶ تا ۹ را انتخاب کرده و بعد از زدن Group، این چهار ردیف به عنوان زیرمجموعه ردیف ۵ یا همان محوطه قرار می گیرند (شکل۳).

همین کار را برای سایر مجموعه ها و زیرمجموعه ها نیز انجام می دهیم.

گروه بندی محدوده انتخاب شده

شکل۳- تبدیل محدوده به یک گروه

نکته: علامت + و – بصورت پیشفرض در پایین زیرمجموعه نمایش داده می شود (شکل۳). برای اینکه این علامت را در بالا و در کنار کلمه محوطه مشاهده کنیم روی زبانه نشان داده شده در شکل ۴ کلیک میکنیم و مطابق شکل ۵ تنظیمات را انجام میدهیم.

 تنظیم نحوه نمایش علامت +و-

شکل۴- تنظیم نحوه نمایش علامت + و –

 تنظیمات نمایش مکان +و-

شکل۵- تنظیمات نمایش مکان + و –

با برداشتن تیک Summary rows below detail علامت + و – هر مجموعه در بالا و در کنار خود مجموعه قرار خواهد گرفت.

برای خارج شدن از این حالت نیز ردیف ها را انتخاب کرده و Ungroup را می زنیم.

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

آواتار
128

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

دیدگاه کاربران
  • علیرضا ۱۷ آذر ۱۴۰۰ / ۲:۵۲ ب٫ظ

    سلام و عرض خسته نباشید. راهی جهت تایپ دلخواه نام گروه بندی بجای ۱ ۲ ۳ و….. وجود دارد؟

    • آواتار
      حسنا خاکزاد ۲۱ آذر ۱۴۰۰ / ۲:۲۶ ب٫ظ

      درود بر شما
      تا اونجا که میدونم خیر (اگر منظورتون شماره های کنار علامت + و – هست)

  • عطائی ۴ فروردین ۱۴۰۰ / ۹:۱۸ ق٫ظ

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

    • آواتار
      حسنا خاکزاد ۵ فروردین ۱۴۰۰ / ۸:۳۸ ب٫ظ

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

  • حسین ۷ دی ۱۳۹۹ / ۱:۴۰ ق٫ظ

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

  • علیرضا ۲۹ شهریور ۱۳۹۹ / ۲:۵۲ ب٫ظ

    به یک گروه بندی نیاز دارم که مانند SUBTOTAL جمع یک ستون را بر حسب دو ستون دیگر بصورت شرطی در پایان جدول ارائه دهد. چون تعداد رکوردها زیاد هست امکان استفاده از فیلتر و عملیات دستی نیست. بعنوان مثال در یک ستون کدهای فروش داریم که تنوع آنها بسیار زیاد و تکرار هم شده در ستون دیگر دو رقم سال را داریم و در ستون سوم مبلغ فروش. حالا در پایین جدول یا زیر هر کد فروش میزان فروش تفکیک شده برای هر سال و هر کد در آن سال را بصورت خلاصه در یک جدول نیاز داریم (ترکیبی از SUBTOTAL و یک شرط هست) خواستم از SUMIF استفاده کنم تعداد رکوردها زیاد بود و باید برای هر کد فروش یک SUMIF مجزا می نوشتم که بسیار زمانگیر و امکانش نبود. به نظرم اگر در SUBTOTAL بشه از شرط استفاده کرد مشکل حل میشه یعنی همه مبالغ فروش که کد فروش آنها یکی است به تفکیک سال فروش

    • سامان چراغی ۲۲ دی ۱۳۹۹ / ۳:۴۸ ب٫ظ

      سلام
      برای انجام این کار بهتره از Pivot Table استفاده کنید.

  • محمد ترکاشوند ۲۶ مرداد ۱۳۹۹ / ۸:۲۸ ق٫ظ

    سلام
    یه فایل اکسل دارم ۳۰۰۰۰سطره میخوام به فایلهای ۱۰۰۰تایی و مجزا تبدیلش کنم لطفاً راهنمایی میفرمایید. با تشکر

    • آواتار
      حسنا خاکزاد ۲۶ مرداد ۱۳۹۹ / ۸:۳۲ ب٫ظ

      درود
      اگر میتونید کدنویسی VBA انجام بدید
      اگر نه، باید یک ستون کمکی داشته باشید شماره گذاری کنید. هزارتای اول ۱، هزارتای دوم ۲ و … بعد پیوت بگیرید و report filter page
      این کار داده ها رو به سی شیت تبدیل میکنه. بعد دونه دونه انتقال بدید به یک فایل جدید و سیو کنید!

  • مسعود ۲۴ خرداد ۱۳۹۹ / ۵:۲۱ ب٫ظ

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

  • آرزو ۲۲ فروردین ۱۳۹۹ / ۲:۴۴ ب٫ظ

    سلام . ممنون از توضیح عالیتون. حالا اگر بخوام یک گروه را که چند زیرمجموعه داره و زیرمجموعه هاش هم چند زیرمجوعه دارند را وارد یک گروه دیگر کنم آیا امکانش هست ؟ بدون اینکه دوباره گروه و زیرگروه ها را بخوام تعریف کنم.
    یعنی مثلا در مثال بالا بخواهم “ماشین آلات و تکنولوژی” را به همراه زیرمجموعه اش در پایین “میکسر” قرار دهم. به اینصور ت ساختمانهای صنعتی داراری سه زیرمجموعه می شوند که شامل “میکسر” و “سوله ها” و “ماشین آلات و تکنولوژی” است.

  • محمد حسین ۱۴ فروردین ۱۳۹۹ / ۱:۵۴ ب٫ظ

    با عرض سلام و خسته نباشید

    چطور میتونم از یک جدول با ۹۰ سطر و ۲۹ ستون ، داده های مد نظر خودم رو که دارای مثلا ستون ۱۵ و سطر های ۱۱.۲۲،۳۳، هستند رو استخراج کنم ؟
    با تشکر

    • آواتار
      حسنا خاکزاد ۱۴ فروردین ۱۳۹۹ / ۴:۱۹ ب٫ظ

      درود بر شما
      میتونید از تابع Index استفاده کنید

  • نیکو ۲ اسفند ۱۳۹۸ / ۲:۵۷ ب٫ظ

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

    • سامان چراغی ۳ اسفند ۱۳۹۸ / ۱۱:۳۲ ق٫ظ

      درود
      اگر منظورتون از برش، cut هست
      سلول ناپیوسته رو نمیتونید cut کنید

  • سیامک ۵ دی ۱۳۹۸ / ۲:۲۱ ب٫ظ

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

  • zahra ۲۲ مرداد ۱۳۹۸ / ۱۲:۱۲ ب٫ظ

    سلام وقت بخیر ممنون از زحمات شما
    برای جابجا کردن این لیست کشویی با همه زیرمجموعه چه کار باید کرد؟
    با تشکر فراوان

    • آواتار
      حسنا خاکزاد ۲۹ مرداد ۱۳۹۸ / ۱۱:۱۱ ق٫ظ

      درود بر شما
      اگر منظور از لیست کشویی همین گروه بندی ها و subtotal هست، باید با cut و paste جابجا کنید

  • سروش ۲ مرداد ۱۳۹۸ / ۸:۱۰ ق٫ظ

    سلام.
    آیا راهی برای پنهان (Hide)کردن نواری که بعد از گروه بندی ایجاد می شود؛ همان نواری که علامت های مثبت و منفی در آن قرار می گیرند، وجود دارد؟

    • آواتار
      حسنا خاکزاد ۶ مرداد ۱۳۹۸ / ۱۲:۲۵ ب٫ظ

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

      • kia ۸ اسفند ۱۳۹۸ / ۱۱:۳۲ ق٫ظ

        فکر می کنم منظورشون از نظر شکل ظاهری اون هست. من یه مدل از این کار رو دیدم که + – رو روی خود ردیف ها نشون میده. نه اینکه یه نوار بعد از گروه بندی ایجاد بشه. امکانش هست در این مورد راهنمایی بفرمایید؟

        • آواتار
          حسنا خاکزاد ۸ اسفند ۱۳۹۸ / ۱۲:۰۹ ب٫ظ

          خروجی پیوت تیبل به این صورت ظاهر میشه
          گروه بندی این کار و نمیتونه بکنه

  • آتی ۳۰ تیر ۱۳۹۸ / ۱۰:۳۸ ق٫ظ

    اگر گروه بندی کنیم میتونیم توی vlookup استفاده کنیم ؟

    • آواتار
      حسنا خاکزاد ۳۰ تیر ۱۳۹۸ / ۸:۵۲ ب٫ظ

      بله.نباید مشکلی باشه

  • titi ۹ خرداد ۱۳۹۸ / ۲:۵۵ ب٫ظ

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

    • آواتار
      حسنا خاکزاد ۹ خرداد ۱۳۹۸ / ۵:۲۴ ب٫ظ

      درود بر شما
      بصورت کلی این ابزار کارش هاید کردنه. برای همین بصورت پیش فرض داده های هاید در نمودار نمایش داده نمیشن مگر اینکه تنظیمات نمودار رو تغییر بدید

      برای اینکه داده ها در نمودار هب درستی نمایش داده بشن باید داده ها فراخوانی بشن و درست چیده بشن. با این ابزار نمیتونید روی نمودار کنترلی داشته باشید

  • سید احمد شمس العلماء ۲۹ فروردین ۱۳۹۸ / ۲:۴۴ ب٫ظ

    درود بر شما موفق پیروز باشید

  • محمدی ۲۳ مهر ۱۳۹۷ / ۱۰:۲۱ ق٫ظ

    سلام من حدود ۲۰۰تا سلول در یک شیت اکسل گروه بندی کردم که میخوام هرکدام به شیت جداگانه تبدیل بشه امکان این کار هست ؟

    • سامان چراغی ۲۴ مهر ۱۳۹۷ / ۹:۴۲ ب٫ظ

      سلام
      دو راه برای انجام این کار وجود داره:
      ۱- از روی داده های اصلی (در صورتیکه ساختار مناسب داشته باشه) Pivot Table بسازید و فیلدی که بر اساس آن گروه بندی کردید رو به قسمت Filter منتقل کنید و از بخش Analyze>Options>Show report filter page این شیت ها رو ایجاد کنید.
      ۲- با استفاده از VBA که ممکنه با توجه به ساختار فایل شما زمان بر باشه.

  • جلیل کریمی ۱۱ مهر ۱۳۹۷ / ۳:۱۹ ب٫ظ

    با سلام

    مطلبتون عالی بود

    میخواستم ببینم آیا با آرایه هم کار کردید ؟؟ منظورم فرمول نویسی آرایه ای نیست ، منظورم خود آرایه است ، پشته ای از اطلاعات است .

    اصلا آیا اکسل چیزی به نام آرایه رو پشتیبانی میکنه یا خیر ؟؟

    • آواتار
      حسنا خاکزاد ۱۱ مهر ۱۳۹۷ / ۳:۵۹ ب٫ظ

      درود بر شما
      در برنامه نویسی VBA ، مبحث آرایه ها و ذخیره اطلاعات رو داریم که مبحث پیشرفته ای هم هست

      • جلیل کریمی ۱۱ مهر ۱۳۹۷ / ۴:۰۸ ب٫ظ

        از قضا vba هم کار کردم .
        ولی من میخاستم از آرایه توی شیت ها استفاده کنم .

        چطور میتونم فایلی رو براتون بفرستم و توی اون فایل مشکلم رو براتون بگم .

        • آواتار
          حسنا خاکزاد ۱۱ مهر ۱۳۹۷ / ۱۰:۳۱ ب٫ظ

          سوالات رو در گروه اکسل پدیا مطرح کنید
          لینک در فوتر سایت موجود هست

  • محسن ۶ مرداد ۱۳۹۷ / ۵:۴۶ ب٫ظ

    سلام و عرض ادب
    ببخشید اگه اطلاعات در یک شیت باشد و در شیت دیگر بخواهیم آیکون های آبشاری قرار بدهیم باید به چه شکل اقدام کرد
    ممنون

  • زهرا ۶ مرداد ۱۳۹۷ / ۱۲:۲۱ ب٫ظ

    مشکل بنده رو حل کرد. خیلی ممنون

  • محمود ۲۳ خرداد ۱۳۹۷ / ۱۱:۰۳ ق٫ظ

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

  • Good ۲۸ فروردین ۱۳۹۷ / ۱۲:۵۰ ب٫ظ

    با سلام
    خیلی دلم میخواد ببینم شما بیشتر از اکسل سر در می یارید یا من ؟!!

  • عباسی ۸ اسفند ۱۳۹۶ / ۱۰:۳۶ ق٫ظ

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

    • آواتار
      حسنا خاکزاد ۸ اسفند ۱۳۹۶ / ۱۱:۵۲ ق٫ظ

      درود بر شما
      چرا فیلتر جواب نداده؟ باید بررسی بشه داده هاتون
      سوال رو در گروه تلگرامی بذارید(لینک گروه در فوتر صفحه اصلی سایت موجوده)
      فایلتون رو هم بذارید
      تا بشه بررسی کرد….

  • مهديه ۷ اسفند ۱۳۹۶ / ۱۲:۰۸ ب٫ظ

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

    • آواتار
      حسنا خاکزاد ۷ اسفند ۱۳۹۶ / ۱:۴۳ ب٫ظ

      درود بر شما
      ببینید برای این کار باید ترفندی بزنید
      باید برای هر دسته، یک ستون ایجاد کنید و کد تعریف کنید. مثلا دسته اول عدد ۱
      دسته دوم عدد ۲
      بعد اینطوری موقع سورت کردن
      اول ستون کدها رو سورت میکنید بعد در سزح بعدی، ستون داده های هر دسته یا اصطلاحا گروه رو

      برای ایجاد سطح در سورت پست زیر رو بخونید:

      https://excelpedia.net/sort/

  • سينا1983 ۲۳ بهمن ۱۳۹۶ / ۷:۲۶ ب٫ظ

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

    • آواتار
      حسنا خاکزاد ۲۴ بهمن ۱۳۹۶ / ۹:۵۳ ق٫ظ

      سلام
      سوال نامفهومه
      توضیح برای چه کاری؟احتمالا میشه راههای بهتری برای خواستتون ارائه داد…
      توضیح بیشتر لازمه.
      اگر مایلید سوال رو درگروه تلگرامی که لینکش در فوتر صفحه موجوده مطرح کنید

  • سینا1983 ۲۰ بهمن ۱۳۹۶ / ۳:۲۴ ب٫ظ

    سلام خسته نباشید
    من یک جدول دارم که تعدادی داده تو سطرهاش هست
    میخام موقع چاب تا اخرین سطر رو چاپ کنم و بقیه جدول رو چاپ نکنه واسم.
    با چ دستوری می تونم اینکار رو بکنم؟
    با تشکر

    • آواتار
      حسنا خاکزاد ۲۳ بهمن ۱۳۹۶ / ۹:۵۹ ق٫ظ

      سلام
      یعنی این محدوده متغیر هست؟آخرین سطر تغییر میکنه؟؟؟

  • احمد جهانیان ۱ بهمن ۱۳۹۶ / ۱۲:۲۶ ب٫ظ

    با سلام به اساتید محترم
    من یه سوال دارم که خیلی براش وقت گذاشتم راههای مختلفی رو هم امتحان کردم ولی نشد.ممنون میشم اگر راهنماییم کنید.
    من یه برنامه ERP نوشتم برای شرکتی که در آن کار میکنم. در شیت برنامه ریزی تولید برای محاسبه واریانس در انتهای گزارشات عملکرد روزانه تولید، برای هر OP (عملیات) دلیل انحراف را گذاشتم. حالا برای محاسبه اینکه از هر دلیل انحراف مثلا A، چند درصد انحراف داشتیم نمی تونم کاری بکنم. چون محاسبه در یک ردیف میخوام انجام بشه که مجموعه ای از دلیل ها رو دارم و میخوام اگر مثلا A بود خونه های بعدش رو که درصد اون هست رو باهم جمع کنه. حالا نمیدونم برای اینکه یک مجموعه منفصل یا گسسته رو مساوی یک پارامتر قرار بدم و در صورت برقرار بودن اون شرط، مجموعه دیگری که متناظر با اون هست رو با هم جمع کنم، چیکار کنم. از توابع آرایه ای و sumproduct هم استفاده کردم ولی جواب نداد. اگر لازم هست بفرمایید فایل رو هم براتون میفرستم.
    مرررررسی از سایت عالیتون

  • fereshteh ۲۹ دی ۱۳۹۶ / ۱:۲۳ ب٫ظ

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

    • آواتار
      حسنا خاکزاد ۳۰ دی ۱۳۹۶ / ۹:۴۰ ق٫ظ

      سلام
      منظورتون مثلا میانگین جمعیت هست؟
      و اینکه چطور طبقه بندی کنید؟؟؟

      شاید منظورتون نمودار هیستوگرام باشه. پیشنهاد میکنم این پست رو بخونید:
      https://excelpedia.net/histogra