У деяких випадках користувачі Excel хочуть лише відображати позитивні значення та результати у своїх електронних таблицях. Це може бути корисно, наприклад, якщо для розрахунків компанії у зовнішній презентації актуальні лише позитивні результати.
Як обмежити результати додатними числами в Excel?
У наведеній прикладі таблиці різні значення вводяться в діапазон комірок A7:A52, які можуть бути як позитивними, так і негативними. Потім вони додаються разом. Зазначене значення потім віднімається з результату розрахунку.Результат має відображатися в клітинці результату, лише якщо він позитивний. Якщо результат обчислення негативний, у клітинці B3 має з’явитися значення «нуль».
У прикладі значення, яке потрібно відняти, показано в клітинці B4. Щоб у комірці B3 виводився лише позитивний результат або нуль, формула в Microsoft Excel повинна мати таку структуру:
=IF((SUM(A7:A52)-B4)>0;SUM(A7:A52)-B4;0)
Показана формула запитує, чи повертає віднімання значення в B4 від результату обчислення в клітинках A7:A52 значення, більше за нуль. Якщо це так, надається позитивний результат розрахунку. В іншому випадку, якщо обчислена сума є від’ємною, виводиться значення «нуль».
На наступному малюнку показано використання формули в прикладі таблиці:
Фактичний результат додавання клітинок A7:52 дорівнює 152.Оскільки граничне значення 155 призведе до негативного результату, збережена формула повертає результат «нуль» у комірці B3. Фактичний результат у цьому прикладі становить -3. За допомогою формули можна відобразити лише позитивні значення.
Якщо ми змінимо межу нижче значення 152, ми також отримаємо математичний результат у плюсовому діапазоні. Потім формула повертає позитивний результат у комірці B3 і D3.
Як використовувати спеціальний формат чисел?
Альтернативою використанню «формули if» є «спеціальний числовий формат», включений у Microsoft Excel. Користувачі Excel повинні пам’ятати, користуючись «спеціальним числовим форматом», що альтернативний спосіб форматування в Excel працює, лише якщо ви не хочуть проводити подальші обчислення з визначеним результатом.
Це тому, що спеціальне форматування чисел Excel змушує вас думати, що результат дорівнює «нулю» для від’ємних чисел. Однак у фоновому режимі Excel продовжує обчислювати від’ємне значення, тому подальші обчислення будуть некоректними для зовнішніх користувачів.
Щоб призначити настроюваний формат чисел, клацніть правою кнопкою миші клітинку, де потрібно використовувати настроюваний формат, щоб відкрити діалогове меню. У діалоговому меню перейдіть до пункту меню «Формат клітинок»
" Відкриється нове вікно, у якому ви можете вибрати різні формати чисел для своєї електронної таблиці. Ліворуч у результаті перейдіть до категорії Custom. На наступному кроці введіть такі інструкції щодо форматування в поле введення «Тип»:"
" 0;0"
На наступному малюнку показано визначення формату числа в діалоговому вікні:
" Підтвердьте спеціальний формат числа, натиснувши OK. Відтепер у клітинках, відформатованих таким чином, відображатимуться лише позитивні значення або нуль для негативного вмісту."
Excel - потужний інструмент для аналізу даних. У деяких випадках має сенс відображати лише позитивні результати розрахунків. Це може бути корисно, наприклад, якщо ви хочете зосередитися на позитивних результатах формули або якщо ви хочете уникнути негативних результатів у корпоративних комунікаціях. У цьому випадку ви можете або працювати зі спеціальною формулою «якщо», або альтернативно використовувати власний формат числа.