خط تقسیم بین مجموعه ردیف ها

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

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

روش 1. ساده

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

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

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

روش 2. با پشتیبانی از فیلتر برای اعداد و تاریخ

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

در این حالت می توانید با استفاده از تابع خارج شوید جمع کل (SUBTOTAL)، که می تواند عملیات ریاضی مختلف (جمع، میانگین، شمارش و غیره) را انجام دهد، اما فقط سلول های فیلتر شده را "دیدن" می کند. به عنوان مثال، بیایید جدول خود را بر اساس آخرین ستون با تاریخ مرتب کنیم و یک خط تقسیم بین روزها بکشیم. در قالب بندی شرطی، باید یک قانون مشابه روش اول ایجاد کنید، اما از پیوندهای مستقیم در مقایسه سلول های D2 و D3 استفاده نکنید، بلکه آنها را به عنوان آرگومان در تابع SUBTOTAL قرار دهید:

اولین آرگومان تابع (شماره 109) اپکد جمع است. در واقع ما اینجا چیزی اضافه نمی کنیم و عمل احمقانه ای مانند SUM (D2) را انجام می دهیم که البته برابر با D2 است. اما این تابع دقیقاً با SUM تفاوت دارد زیرا فقط روی سلول‌های قابل مشاهده اعمال می‌کند، یعنی سلول‌های باقی‌مانده بعد از فیلتر روی صفحه با هم مقایسه می‌شوند، چیزی که ما می‌خواستیم.

روش 3. با پشتیبانی از فیلتر برای هر داده

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

=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103;СМЕЩ($A$1:$A2;СТРОКА($A$1:$A2)-МИН(СТРОКА($A$1:$A2));;1));—($A$1:$A2=$A2))=1

در نسخه انگلیسی این خواهد بود:

=SUMPRODUCT(SUBTOTAL(103;OFFSET($A$1:$A2;ROW($A$1:$A2)-MIN(ROW($A$1:$A2));;1));—($A$1:$A2=$A2))=1

با کلیک بر روی دکمه چارچوب (فرمت) یک حاشیه با یک خط قرمز در بالا قرار دهید و کلیک کنید OK. تقسیم بندی بر اساس کشور حتی پس از فیلتر کردن، به عنوان مثال، بر اساس تاریخ، به درستی کار خواهد کرد:

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