نحوه حذف مقادیر null در اکسل نحوه حذف خطای تقسیم بر صفر در اکسل با استفاده از فرمول

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

صفرها را در Microsoft Excel 2003 حذف یا قرار دهید

برای این کار به منو بروید سرویس،و در قسمت پایین انتخاب کنید مولفه های.

پنجره ای به همین نام باز می شود مولفه های.

در برگه چشم اندازدر پانل گزینه های پنجره، در ستون وسط، یک علامت تیک روی پارامتر تنظیم کنید مقادیر صفر... اکنون تمام مقادیر null شما در سلول ها نمایش داده می شود.

به همان شکلی که می توانید حذف صفر در اکسل... برای این کار کافی است تیک این پارامتر را بردارید و صفرها نمایش داده نمی شوند.

نحوه اضافه یا حذف صفر در Microsoft Excel 2007

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

در منویی که در پایین باز می شود، روی دکمه کلیک کنید مولفه هایبرتری داشتن.

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

در مایکروسافت اکسل 2010

همه چیز مانند اکسل 2007 است، فقط ابتدا منو را باز می کنیم فایل، قابل اعتماد و متخصص مولفه های، و سپس - علاوه بر این – نمایش گزینه ها برای برگه بعدی- حذف یا علامت گذاری روی مورد - صفرها را در سلول هایی که حاوی مقادیر تهی هستند نشان دهید.

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

تقسیم بر صفر خطا در اکسل

در واقع، تقسیم اساساً همان تفریق است. به عنوان مثال، تقسیم 10 بر 2، تفریق چندگانه 2 از 10 است. این تکرار تا زمانی تکرار می شود که نتیجه برابر با 0 شود. بنابراین، باید عدد 2 را دقیقاً 5 بار از ده کم کرد:

1. 10-2=8
2. 8-2=6
3. 6-2=4
4. 4-2=2
5. 2-2=0

اگر بخواهیم عدد 10 را بر 0 تقسیم کنیم، هرگز به نتیجه ای برابر با 0 نخواهیم رسید، زیرا با تفریق 10-0 همیشه 10 خواهد بود. تعداد نامتناهی از تفریق صفر از ده، ما را به نتیجه نمی رساند = 0. همیشه بعد از عمل تفریق یک نتیجه وجود خواهد داشت = 10:

• 10-0=10
• 10-0=10
• 10-0=10
• ∞ بی نهایت.

در حاشیه ریاضیدانان می گویند که نتیجه تقسیم هر عدد بر صفر «نامحدود» است. هر برنامه کامپیوتری وقتی سعی می کند بر 0 تقسیم کند، به سادگی یک خطا را برمی گرداند. در اکسل، این خطا با مقدار موجود در سلول # DIV / 0 نمایش داده می شود.

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

بنابراین، فرمول اکسل به ما این امکان را می دهد که یک عدد را بدون خطا بر 0 "تقسیم" کنیم. وقتی هر عددی را بر 0 تقسیم می کنیم، فرمول مقدار 0 را برمی گرداند. یعنی بعد از تقسیم نتیجه زیر را به دست می آوریم: 10/0 = 0.

نحوه عملکرد فرمول برای حذف تقسیم بر خطای صفر

برای عملکرد صحیح، تابع IF نیاز به پر کردن 3 آرگومان خود دارد:

1. شرایط بولی
2. اعمال یا مقادیری که باید انجام شوند اگر شرط بولی حاصل TRUE برگرداند.
3. اعمال یا مقادیری که باید زمانی که شرط بولی FALSE را برمی گرداند انجام می شود.

در این مورد، آرگومان شرطی حاوی اعتبارسنجی مقادیر است. آیا مقادیر سلول در ستون فروش برابر با 0 است یا خیر. اولین آرگومان یک تابع IF همیشه باید دارای عملگرهای مقایسه بین دو مقدار باشد تا نتیجه شرط به صورت TRUE یا FALSE بدست آید. در بیشتر موارد، علامت مساوی به عنوان عملگر مقایسه استفاده می شود، اما می توان از موارد دیگر مانند بزرگتر از> یا کمتر از> استفاده کرد. یا ترکیبی از آنها - بزرگتر یا مساوی با> =، نه برابر! =.

اگر شرط در آرگومان اول TRUE را برگرداند، فرمول سلول را با مقدار آرگومان دوم به تابع IF پر می کند. در این مثال، آرگومان دوم حاوی عدد 0 به عنوان مقدار است. این به این معنی است که سلول در ستون "پیشرفت" به سادگی با عدد 0 پر می شود اگر در سلول مقابل ستون "فروش" 0 فروش وجود داشته باشد.

اگر شرط در آرگومان اول FALSE را برگرداند، از مقدار آرگومان سوم تابع IF استفاده می شود. در این حالت، این مقدار پس از عمل تقسیم اندیکاتور از ستون "فروش" به اندیکاتور از ستون "طرح" تشکیل می شود.

فرمول تقسیم بر صفر یا صفر بر عدد

بیایید فرمول خود را با تابع = OR () پیچیده کنیم. بیایید یک فروشنده دیگر با فروش صفر اضافه کنیم. اکنون فرمول باید به این صورت تغییر کند:

این فرمول را در تمام سلول های ستون Progress کپی کنید:

حالا، صرف نظر از اینکه صفر در کجا یا در مخرج باشد، فرمول مطابق نیاز کاربر کار خواهد کرد.

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

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

روش 1: تنظیمات اکسل

در سطح جهانی، این مشکل را می توان با تغییر تنظیمات اکسل برای برگه فعلی حل کرد. این به شما امکان می دهد کاملاً تمام سلول های حاوی صفر را خالی کنید.

پس از این مراحل، تمام سلول‌های صفحه فعلی که حاوی مقادیر صفر هستند، خالی نمایش داده می‌شوند.

روش 2: اعمال قالب بندی

شما می توانید مقادیر سلول های خالی را با تغییر قالب آنها پنهان کنید.

اکنون تمام مناطقی که حاوی مقادیر null هستند خالی خواهند بود.

روش 3: قالب بندی شرطی

همچنین می توانید از ابزار قدرتمندی مانند قالب بندی شرطی برای حذف صفرهای اضافی استفاده کنید.

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

روش 4: با استفاده از تابع IF

گزینه دیگری برای پنهان کردن صفرها استفاده از عملگر است اگر.

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

روش 5: با استفاده از تابع ISNUMBER

روش زیر نوعی ترکیب توابع است اگرو ISNUMBER.

چند راه برای حذف رقم "0" در سلولی وجود دارد که مقدار آن صفر باشد. ساده ترین راه این است که نمایش صفرها را در تنظیمات اکسل خاموش کنید. اما پس از آن باید در نظر گرفت که آنها در سراسر ورق ناپدید می شوند. اگر باید غیرفعال کردن را منحصراً در یک منطقه خاص اعمال کنید، قالب بندی محدوده، قالب بندی شرطی و استفاده از توابع به کمک می آیند. انتخاب کدام یک از این روش ها به موقعیت خاص و همچنین به مهارت ها و ترجیحات شخصی کاربر بستگی دارد.

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

مخفی کردن و نمایش تمام مقادیر صفر در یک کاربرگ

لطفا انتخاب کنید فایل > مولفه های > علاوه بر این.

در گروه

• صفرها را در سلول هایی که حاوی مقادیر تهی هستند نشان دهید.

مقادیر صفر را در سلول های انتخاب شده مخفی کنید

این اقدامات با استفاده از فرمت عددی، مقادیر null را در سلول های انتخاب شده پنهان می کند. مقادیر پنهان فقط در نوار فرمول نمایش داده می شوند و چاپ نمی شوند. اگر مقدار یکی از این سلول‌ها به مقدار غیرصفر تغییر کند، در سلول ظاهر می‌شود و فرمت مقدار مشابه قالب اعداد عمومی خواهد بود.

نمایش مقادیر پنهان.

سلول های دارای صفرهای پنهان را انتخاب کنید.

می توانید کلیدها را فشار دهید CTRL + 1یا در برگه خانهکلیک Format> Format Cells.

برای اعمال قالب شماره پیش فرض، را انتخاب کنید شماره> مجموعو دکمه را فشار دهید خوب.

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

در برگه خانه قالب بندی مشروطو Highlight Cell Rules> Equals را انتخاب کنید.

در کادر سمت چپ، وارد کنید 0 .

در کادر سمت راست، را انتخاب کنید فرمت سفارشی.

در زمینه قالب سلولیبرگه را باز کنید فونت.

در لیست رنگسفید را انتخاب کنید و کلیک کنید خوب.

صفرها را به صورت فاصله یا خط تیره نمایش دهید

برای حل این مشکل از تابع IF استفاده کنید.

اگر سلول حاوی مقادیر null است، از فرمولی مانند این برای برگرداندن یک سلول خالی استفاده کنید:

IF (A2-A3 = 0؛ ""؛ A2-A3)

در اینجا نحوه خواندن فرمول آمده است. اگر نتیجه محاسبه (A2-A3) "0" باشد، چیزی نمایش داده نمی شود، از جمله "0" (که با دو نشان داده شده "" نشان داده شده است). در غیر این صورت، نتیجه محاسبه A2-A3 نمایش داده می شود. اگر لازم نیست سلول ها را خالی بگذارید، اما چیزی غیر از "0" را نمایش دهید، یک خط فاصله "-" یا یک کاراکتر دیگر را بین دو گیومه قرار دهید.

گزارش PivotTable را انتخاب کنید.

در برگه تحلیل و بررسیدر گروه جدول محوری مولفه هایو مورد را انتخاب کنید مولفه های.

به برگه بروید طرح و قالب

• نمایش خطاها را تغییر دهیددر زمینه فرمتکادر را علامت بزنید برای نمایش خطاها

  نمایش سلول های خالی را تغییر دهیدکادر را علامت بزنید برای سلول های خالی، نمایش داده شود

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

لطفا انتخاب کنید فایل > مولفه های > علاوه بر این.

در گروه نمایش گزینه ها برای برگه بعدییک برگه را انتخاب کنید و سپس یکی از موارد زیر را انجام دهید:

• برای نمایش مقادیر صفر (0) در سلول ها، کادر را انتخاب کنید صفرها را در سلول هایی که حاوی مقادیر تهی هستند نشان دهید.

  برای نمایش مقادیر null به عنوان سلول های خالی، تیک آن را بردارید صفرها را در سلول هایی که حاوی مقادیر تهی هستند نشان دهید.

می توانید کلیدها را فشار دهید CTRL + 1یا در برگه خانهکلیک Format> Format Cells.

در زمینه یک نوعوارد 0;-0;;@

یادداشت:

مقادیر پنهان فقط در نوار فرمول یا در سلول ظاهر می شوند که محتوای آن را ویرایش می کنید. این مقادیر چاپ نمی شوند.

برای نمایش مجدد مقادیر پنهان، سلول ها را انتخاب کرده و سپس فشار دهید CTRL + 1یا در برگه خانهدر گروه سلول هاماوس را روی عنصر قرار دهید فرمتو انتخاب کنید قالب سلولی... برای اعمال قالب شماره پیش فرض، در لیست دسته بندیانتخاب کنید عمومی... برای نمایش دوباره تاریخ و زمان، فرمت تاریخ و زمان مناسب را در برگه انتخاب کنید عدد.

سلول حاوی مقدار صفر (0) را انتخاب کنید.

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

در کادر سمت چپ، وارد کنید 0 .

در کادر سمت راست، را انتخاب کنید فرمت سفارشی.

در کادر محاوره ای قالب سلولیبرگه را باز کنید فونت.

در زمینه رنگسفید را انتخاب کنید

برای انجام این کار، از تابع استفاده کنید اگر.

مثال

تابع IF.

مخفی کردن مقادیر صفر در گزارش PivotTable

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

به برگه بروید طرح و قالبو سپس این مراحل را دنبال کنید.

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

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

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

نمایش و پنهان کردن تمام مقادیر null در یک کاربرگ

با استفاده از قالب شماره، مقادیر صفر را در سلول های انتخاب شده مخفی کنید

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

سلول های حاوی مقادیر صفر (0) را که می خواهید پنهان کنید انتخاب کنید.

می توانید کلیدها را فشار دهید CTRL + 1یا در برگه خانهدر گروه سلول هاکلیک Format> Format Cells.

در زمینه یک نوعوارد 0;-0;;@

یادداشت:

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

سلول حاوی مقدار صفر (0) را انتخاب کنید.

در برگه خانهدر گروه سبک هاروی فلش کنار دکمه کلیک کنید قالب بندی مشروطو Highlight Cell Rules> Equals را انتخاب کنید.

در کادر سمت چپ، وارد کنید 0 .

در کادر سمت راست، را انتخاب کنید فرمت سفارشی.

در کادر محاوره ای قالب سلولیبرگه را باز کنید فونت.

در زمینه رنگسفید را انتخاب کنید

استفاده از فرمول برای نمایش صفرها به صورت فاصله یا خط تیره

برای انجام این کار از تابع IF استفاده کنید.

مثال

برای سهولت درک این مثال، آن را در یک صفحه خالی کپی کنید.

کپی کردن یک نمونه

مثال ارائه شده در این مقاله را برجسته کنید.

مهم:عناوین سطر یا ستون را برجسته نکنید.

برجسته کردن یک مثال در کمک

CTRL + C را فشار دهید.

در اکسل، یک کتاب کار یا برگه خالی ایجاد کنید.

سلول A1 را در برگه انتخاب کنید و CTRL + V را فشار دهید.

مهم:برای اینکه مثال به درستی کار کند، باید آن را در سلول A1 قرار دهید.

برای جابه‌جایی بین مشاهده نتایج و مشاهده فرمول‌هایی که آن نتایج را برمی‌گردانند، CTRL + `( تاکیدی) یا روی برگه را فشار دهید فرمول هادر گروه Formula Dependencies کلیک کنید نمایش فرمول ها.

هنگامی که مثال را در یک صفحه خالی کپی کردید، می توانید آن را به روشی که می خواهید سفارشی کنید.

برای اطلاعات بیشتر در مورد استفاده از این تابع، به مقاله IF Function مراجعه کنید.

مخفی کردن مقادیر صفر در گزارش PivotTable

روی گزارش PivotTable کلیک کنید.

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

به برگه بروید طرح و قالبو سپس این مراحل را دنبال کنید.

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

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

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

شما نیاز خواهید داشت

• ویرایشگر جدول Microsoft Office Excel 2007 یا 2010.

دستورالعمل ها