Чому не рахує формула в excel. Рекомендації, що дають змогу уникнути появи непрацюючих формул. Помилки в Excel - Правильне поєднання тексту

Ви можете самі настроїти відображення нульових значень у комірці або використовувати в таблиці набір стандартів форматування, які вимагають приховувати нульові значення. Відображати та приховувати нульові значення можна різними способами.

Приховування та відображення всіх нульових значень на аркуші

Виберіть Файл > Параметри > Додатково.

В групі

• Показувати нулі в осередках, які містять нульові значення.

Приховування нульових значень у виділених осередках

Ці дії приховують нульові значення у виділених осередках за допомогою числового формату. Приховані значення відображаються лише у рядку формул і не виводяться на друк. Якщо значення в одному з цих комірок зміниться на ненульове, воно буде відображатися в комірці, а формат значення буде схожий на загальний цифровий формат.

Відображення прихованих значень.

Виділіть комірки із прихованими нульовими значеннями.

Ви можете натиснути клавіші CTRL+1або на вкладці Головнаклацнути Формат > Формат осередків.

Для використання числового формату, визначеного за умовчанням, виберіть Число > Загальнийта натисніть кнопку ОК.

Приховування нульових значень, повернутих формулою

На вкладці Головна Умовне форматуваннята виберіть "Правила виділення осередків" > "Рівне".

У лівому полі введіть 0 .

У правому полі виберіть Користувальницький формат.

В полі Формат осередкувідкрийте вкладку Шрифт.

В списку Колірвиберіть білий колір та натисніть кнопку ОК.

Відображення нулів у вигляді пробілів або тире

Для вирішення цього завдання скористайтеся функцією ЯКЩО.

Якщо осередок містить нульові значення, для повернення порожнього осередку використовуйте формулу, наприклад:

ЯКЩО(A2-A3=0;"";A2-A3)

Ось як читати формулу. Якщо результат обчислення (A2-A3) дорівнює "0", нічого не відображається, у тому числі і "0" (це вказується подвійними лапками""). Інакше відображається результат обчислення A2-A3. Якщо вам потрібно не залишати комірки порожніми, але відображати не "0", а щось інше, між подвійними лапками вставте дефіс "-" або інший символ.

Виберіть звіт зведеної таблиці.

На вкладці Аналізв групі Зведена таблиця Параметрита виберіть пункт Параметри.

Перейдіть на вкладку Розмітка та формат

• Зміна відображення помилокВ полі Форматвстановіть прапорець Для помилок відображати

  Зміна відображення порожніх осередківВстановіть прапорець Для порожніх осередків відображати

Потреба відображати нульові значення (0) на аркушах не завжди. Чи вимагають стандарти форматування чи власні переваги відображати чи приховувати нульові значення, є кілька способів реалізації всіх цих вимог.

Виберіть Файл > Параметри > Додатково.

В групі Показати параметри для наступного листавиберіть аркуш, після чого виконайте одну з наведених нижче дій.

• Щоб відобразити в осередках нульові значення (0), встановіть прапорець Показувати нулі в осередках, які містять нульові значення.

  Щоб відобразити нульові значення у вигляді порожніх осередків, зніміть прапорець Показувати нулі в осередках, які містять нульові значення.

Ви можете натиснути клавіші CTRL+1або на вкладці Головнаклацнути Формат > Формат осередків.

В полі Типвведіть 0;-0;;@

Примітки:

Приховані значення відображаються лише у рядку формул або комірці, якщо ви редагуєте її вміст. Ці значення не друкуються.

Щоб знову відобразити приховані значення, виділіть комірки, а потім натисніть клавіші CTRL+1або на вкладці Головнав групі Осередкинаведіть вказівник миші на елемент Форматта виберіть Формат осередків. Щоб застосувати цифровий формат за замовчуванням, у списку КатегоріяВиберіть Загальний. Щоб знову відобразити дату та час, виберіть відповідний формат дати та часу на вкладці Число.

Виділіть комірку, що містить нульове (0) значення.

На вкладці Головнав групі Стиліклацніть стрілку поруч із елементом Умовне форматування, наведіть вказівник на елемент Правила виділення осередківта виберіть варіант Рівно.

У лівому полі введіть 0 .

У правому полі виберіть Користувальницький формат.

У діалоговому вікні Формат осередківвідкрийте вкладку Шрифт.

В полі КолірВиберіть білий колір.

Для виконання цього завдання використовуйте функцію ЯКЩО.

приклад

Функція ЯКЩО.

Приховування нульових значень у звіті зведеної таблиці

На вкладці Параметрив групі Параметри зведеної таблиціклацніть стрілку поруч із командою Параметрита виберіть пункт Параметри.

Перейдіть на вкладку Розмітка та формат, а потім виконайте наведені нижче дії.

В полі Форматвстановіть прапорець Для помилок відображати. Введіть у поле значення, яке потрібно виводити замість помилок. Щоб відобразити помилки у вигляді порожніх осередків, видаліть з поля весь текст.

Встановіть прапорець Для порожніх осередків відображати. Введіть у поле значення, яке потрібно виводити у порожніх комірках. Щоб залишитися порожнім, видаліть із поля весь текст. Щоб відобразити нульові значення, зніміть цей прапорець.

Потреба відображати нульові значення (0) на аркушах не завжди. Чи вимагають стандарти форматування чи власні переваги відображати чи приховувати нульові значення, є кілька способів реалізації всіх цих вимог.

Відображення та приховування всіх нульових значень на аркуші

Приховування нульових значень у виділених осередках за допомогою числового формату

Ці дії дозволяють приховати нульові значення у виділених осередках. Якщо значення в одній із осередків стане ненульовим, його формат буде аналогічним загальному числовому формату.

Виділіть комірки, що містять нульові значення (0), які потрібно приховати.

Ви можете натиснути клавіші CTRL+1або на вкладці Головнав групі Осередкиклацнути Формат > Формат осередків.

В полі Типвведіть 0;-0;;@

Примітки:

Приховування нульових значень, повернутих формулою, за допомогою умовного форматування

Виділіть комірку, що містить нульове (0) значення.

На вкладці Головнав групі Стиліклацніть стрілку поруч із кнопкою Умовне форматуваннята виберіть "Правила виділення осередків" > "Рівне".

У лівому полі введіть 0 .

У правому полі виберіть Користувальницький формат.

У діалоговому вікні Формат осередківвідкрийте вкладку Шрифт.

В полі КолірВиберіть білий колір.

Використання формули для відображення нулів у вигляді пробілів або тире

Для виконання цього завдання використовуйте функцію ЯКЩО.

приклад

Щоб цей приклад було простіше зрозуміти, скопіюйте його на порожній лист.

Копіювання прикладу

Виділіть приклад, наведений у цій статті.

Важливо:Не виділяйте заголовки рядків чи стовпців.

Виділення прикладу у довідці

Натисніть клавіші CTRL+C.

В Excel створіть порожню книгу чи аркуш.

Виділіть на аркуші комірку A1 і натисніть клавіші CTRL+V.

Важливо:Щоб приклад правильно працював, його потрібно вставити в комірку A1.

Щоб переключитись між переглядом результатів та переглядом формул, що повертають ці результати, натисніть клавіші CTRL+` (знак наголосу) або на вкладці Формулиу групі "Залежності формул" натисніть кнопку Показати формули.

Скопіювавши приклад на порожній аркуш, ви можете налаштувати його так, як вам потрібно.

Щоб отримати додаткові відомості про використання цієї функції, див. функцію ЯКЩО.

Приховування нульових значень у звіті зведеної таблиці

Натисніть на звіт зведеної таблиці.

На вкладці Параметрив групі Параметри зведеної таблиціклацніть стрілку поруч із командою Параметрита виберіть пункт Параметри.

Перейдіть на вкладку Розмітка та формат, а потім виконайте наведені нижче дії.

Зміна способу відображення помилок.В полі Форматвстановіть прапорець Для помилок відображати. Введіть у поле значення, яке потрібно виводити замість помилок. Щоб відобразити помилки у вигляді порожніх осередків, видаліть з поля весь текст.

Зміна способу відображення порожніх осередків.Встановіть прапорець Для порожніх осередків відображати. Введіть у поле значення, яке потрібно виводити у порожніх комірках. Щоб залишитися порожнім, видаліть із поля весь текст. Щоб відобразити нульові значення, зніміть цей прапорець.

У цій статті ми розглянемо, що таке Автосума, і розглянемо найбільше ефективні способивикористання Автосуми в Excel. Ви дізнаєтесь, як користуватися автосумою в Excel, побачите, як порахувати автосуму в Excelстовпця або рядки за допомогою кнопки Автосума, сумувати тільки видимі осередки, всього виділеного діапазону по вертикалі та горизонталі за один раз, і дізнатися найпоширенішу причину, через яку Автосума в Excel видає 0.

Найбільш поширена функція, яку використовують більшість користувачів Microsoft Excel, це функція СУМ. Не дивно, що Microsoft Excel вирішили додати спеціальну кнопку на стрічку Excel, яка автоматично вставляє функцію СУМ.

По суті, Автосума в Excelавтоматично вводить формулу для підсумовування чисел на листі. У цій статті ми розглянемо таку інформацію:

Де знаходиться Автосума в Excel

Кнопку Автосумаможна знайти у двох місцях на стрічці Excel.

1. Вкладка «ГОЛОВНА» --> група «Редагування» --> « Автосума»:

Автосума в Excel - Кнопка Автосуми в Excel

1. Вкладка «ФОРМУЛИ» --> група « Бібліотека функцій» --> « Автосума»:

Автосума в Excel - Кнопка Автосума у ​​вкладці Формули

Тепер де Автосума в Excelви знаєте.

Як зробити автосуму в Excel

Щоразу, коли вам потрібно порахувати автосуму одного діапазону осередків, чи то стовпець, рядок чи кількох сусідніх стовпців чи рядків, ви можете використовувати формулу в Excel Автосумадля автоматичного створення відповідної формули СУМ.

Щоб використати Автосуму в Excel, виконайте наступні три простих дії:

1. Виберіть комірку поряд із цифрами, які ви хочете підсумувати:

• Щоб підсумувати стовпець, оберіть осередок відразу під останнім значенням у стовпці.
• Щоб підсумувати рядок, виберіть осередок праворуч від останнього числа у рядку.

Автосума в Excel - вибір комірки, після якого ви хочете порахувати суму

1. Натисніть кнопку Автосумана вкладці «ГОЛОВНА» або «ФОРМУЛИ».

У вибраному осередку з'являється формула суми, а діапазон осередків, що підсумовуються, виділяється (B2:B6 в цьому прикладі):

Автосума в Excel - Клікніть на кнопці Автосума на вкладці Головна та Формули (в даному випадку на вкладці Формула)

У більшості випадків Excel вибирає правильний діапазон загальної суми. В окремих випадках, коли вибрано неправильний діапазон, ви можете виправити його вручну, ввівши потрібний діапазон у формулі або перетягнувши курсор через комірки, які ви хочете підсумовувати.

Щоб підсумувати кілька стовпців або рядків за раз, виберіть кілька осередків внизу або праворуч від вашої таблиці, відповідно, а потім натисніть кнопку Автосумаабо використовуйте . Для отримання додаткової інформаціїдив. пункт.

1. Натисніть клавішу Enter, щоб завершити формулу.

Тепер ви можете побачити обчислену суму в комірці та формулу СУМ у рядку формул:

Автосума в Excel - Натисніть клавішу Enter, щоб завершити формулу СУМ

Розберемо причини, з яких автосума в Excel видає 0.

Автосума в Excel не рахує

Бувають випадки, коли Автосума в Excel не працює, тобто автосума в Excel видає 0. Найпоширеніша причина чому не працює Автосума в Excel- Це числа, відформатовані як текст. На перший погляд ці значення можуть виглядати як звичайні числа, але Excel розглядає їх як текстові рядки і не враховує при розрахунках.

Найбільш очевидними показниками чисел, відформатованих як текст, є їхнє вирівнювання по лівому краю і маленькі зелені трикутники у верхньому лівому кутку осередків. Щоб виправити такі текстові числа, виберіть усі проблемні осередки, клацніть попереджувальний знак і натисніть «Перетворити на число».

Автосума в Excel - Перетворення числа в текстовому форматі, в числовій

Числа можуть мати текстовий формат з різних причин, наприклад, вони можуть бути імпортовані з зовнішнього джерела. Тепер ви знаєте причину, чому Автосума в Excel не рахуєваші дані та вирішення цього питання.

Автосума в Excel гарячі клавіші

Якщо ви один із тих користувачів Excel, які воліють працювати з клавіатурою, а не з мишею, ви можете використати наступну комбінацію гарячих клавіш Автосуми в Excelдля підсумовування осередків:

Автосума в Excel - Автосума в Excel гарячі клавіші

При утримуванні клавіші Alt , і натисканні знака рівності (=), у вибраній комірці (комірках) вставляється формула СУМ точно так само, як при натисканні кнопки Автосумана стрічці, а потім ви натискаєте клавішу Enter для введення формули.

Як використовувати Автосуму з іншими функціями

Крім підсумовування осередків, можна використовувати кнопку Автосума для вставки інших функцій, таких як:

СЕРЕДНЕ - повертає середнє (середнє арифметичне) чисел.

ЛІЧИЛЬНИК - підраховує кількість осередків з числами.

МАКСИМУМ – повертає найбільше значення.

МІНІМУМ – повертає найменше значення.

Все, що вам потрібно зробити, це вибрати осередок, в який ви хочете вставити формулу, натиснути стрілку списку Автосума, що розкривається, і вибрати потрібну функціюз списку.

Наприклад, саме так ви можете отримати найбільше число у стовпці B:

Автосума в Excel - Використання кнопки Автосума для вставлення інших формул (в даному випадку Максимум)

Якщо ви виберете «Інші функції» зі списку «Автосума», Microsoft Excel відкриє діалогове вікно «Вставити функцію», де ви можете знайти необхідну формулу у списку або скористатися пошуком.

Як використовувати Автосуму в Excel тільки для видимих ​​осередків

Ви вже знаєте, як використовувати Автосуму в Excelдля загального стовпця чи рядка. Але чи знаєте ви, що ви можете використовувати цю функцію для підсумовування тільки видимих ​​осередківнаприклад, у відфільтрованому списку?

Якщо ваші дані організовані в таблиці Excel (що можна легко зробити, натиснувши клавіші Ctrl+T), і ви відфільтрували свої дані, застосувавши один із параметрів фільтрації, натискання кнопки Автосумивставляє формулу ПРОМІЖНІ.ПІДСУМКИ, а не СУМ, як показано на наступному зображенні:

Автосума в Excel - Використання Автосуми для видимих ​​осередків

У вихідній таблиці були приховані 3, 6, 9 та 12 рядок. В даному випадку, використання Автосуми та функції ПРОМІЖНІ.ПІДСУМКИ, були підсумовані тільки видимі осередки.

Тепер ви знаєте, як використовувати Автосуму в Excelдля автоматичного підсумовування осередків, причини чому автосума в Excel видає 0, . Але вам, можливо, захочеться вивчити кілька трюків, які заощаджують час, які могли б зробити вашу роботу ще ефективнішою.

Як використовувати Автосуму для більш ніж одного осередку за раз

Якщо ви бажаєте підсумувати значення в кількох стовпцях або рядках, виберіть усі комірки, в які ви хочете вставити формулу СУММ, а потім натисніть кнопку Автосума на стрічці або .

Наприклад, ви можете вибрати діапазон осередків B7:D7, клацнути Автосума, і як показано на наступному зображенні, значення в кожному з трьох стовпців сумуються окремо:

Автосума в Excel - Автосума кількох стовпців одночасно

Як підсумовувати виділені осередки по вертикалі та по горизонталі

Щоб підсумувати тільки певні осередки у стовпці, виділіть ці комірки та натисніть кнопку Автосума. Якщо ви хочете порахувати суму обраних осередків по стовпцю, то виділіть комірки та натисніть Автосума.

Автосума в Excel - Сума вибраних осередків вертикально по стовпцях

Якщо ви хочете підсумовувати осередки рядків, виберіть осередки, які ви хочете підсумовувати, і один порожній стовпець праворуч. Excel підсумовуватиме виділені осередки по горизонталі і вставить формулу СУММ у порожній стовпець, включений у виділення:

Автосума в Excel - Сума вибраних осередків горизонтально по рядках

Щоб підсумувати комірки по стовпцях і рядкам, виберіть комірки, які ви хочете підсумовувати, плюс один порожній рядок нижче і один порожній стовпець праворуч, а Excel автоматично порахуємо суму вибраних осередків по вертикалі та по горизонталі:

Автосума в Excel - Підсумовування обраних осередків стовпцям та рядкам

Тепер ви знаєте, як зробити автосуму в Excelчому автосума в Excel видає 0і як це виправити, а також як використовувати гарячі клавіші автосуми в Excelі багато іншого. До доповнення на нашому ресурсі є ще одна стаття, де розглядаються різні способи, .

Сьогодні зіткнувся з ситуацією коли в excel при протягуванні не працювали формули, тобто. було два стовпці і треба було у третьому стовпці вирахувати різницю перших двох стовпців. У результаті робиться формула і простягається вниз до кінця, але результат з першої комірки копіюється в інші комірки.

1 варіант

Цілком ймовірно у Вас вимкнено автоматичний перерахунок аркуша, для цього просто натисніть клавішу F9

2 Варіант для всіх версій Office

Перевірте, щоб формат осередку не був текстовим (через це можуть не працювати обчислення і відповідно формули)

PS щоб це виправити, виділіть комірки і виберіть формат Загальний (Див. малюнок Формат Осередків)

3 Варіант для Office 2010

Перевірте параметри обчислення формул.

Для цього натисніть на меню ФАЙЛ->ПАРАМЕТРИ->ФОРМУЛИ і в меню «Параметри обчислення» дивіться де стоїть у Вас галочки (див. картинку має бути так)

4 Варіант для Office 2007/2010

Для Office 2007 шлях такий Формула->Параметри обчислення->і ставимо галку на Автоматично, окрім таблиць даних.(Див малюнок нижче)

Як порахувати суму значень в Excel

Припустимо, у вас є стовпець зі значеннями і вам необхідно дізнатися про їх суму. Найпростіший спосіб - ви можете виділити потрібні осередки і в правому нижньому кутку Excel, у рядку стану, ви побачите суму значень, їх кількість і навіть середнє арифметичне значення.

Якщо отриману суму вам необхідно десь використовувати або просто вивести в комірку, скористаємось функцією СУМ. Вона може працювати як у ручному режимі, так і автоматичному. Виділіть потрібні комірки та натисніть кнопку «Автосума», вона знаходиться на вкладці «Головна». Формула з розрахунком суми з'явиться в наступному осередку нижче виділених вами. Щоб вставити із сумою в довільному місці, виділіть лубу вільну комірку і наберіть у ній «=СУМ(» без лапок, далі мишкою виділіть потрібний діапазон і натисніть Enter на клавіатурі.

Чому може не вважатися сума в Excel

Є дві найімовірніші причини.

1. Неправильний роздільник дробової частини. Наприклад у Windows стоїть кома, а у таблиці вас точка. Спробуйте замінити розділовий знак для пари значень і складіть їх. Щоб побачити який знак стоїть у системі, зайдіть у «Панель управління», виберіть «Мова та регіональні стандарти» -> «Додаткові параметри»:

2. Ваші комірки в текстовому форматі і в них, крім цифр, є зайві знаки, наприклад, прогалини. Виділіть їх та натисніть праву клавішу миші. Виберіть «Формат осередків» у меню та виставте «Числовий» формат, якщо він не такий. Перевірте, що крім цифр у осередках нічого немає.

Якщо у вас залишилися питання або вам потрібні уточнення – питання, наші експерти оперативно дадуть відповідь на ваш запит.

Ексель використовують не тільки для створення таблиць. Його головним призначенням є розрахунок чисел за формулами. Достатньо вписати в комірки нові значення та система автоматично перерахує їх. Однак у деяких випадках розрахунок не відбувається. Тоді необхідно з'ясувати, чому Ексель не рахує суму.

Основні причини несправності

Ексель може не брати до уваги суму або формули з багатьох причин. Проблема часто полягає, як у неправильною формулою, так і в системних налаштуваннях книги. Тому рекомендується скористатися кількома порадами, щоб з'ясувати, який саме підходить у цій конкретній ситуації.

Змінюємо формат осередків

Програма виводить неправильні розрахунки, якщо зазначені формати не відповідають значенню, що знаходиться в осередку. Тоді обчислення або взагалі не застосовуватиметься, або видаватиме зовсім інше число. Наприклад, якщо формат є текстовим, то розрахунок не проводиться. Для програми це лише текст, а не числа. Також може виникнути ситуація, коли формат не відповідає дійсному. У такому разі, користувач не зможе правильно вставити обчислення, і Ексель не порахує суму і не розрахує результат формули.

Щоб перевірити, чи справді справа у форматі, слід перейти у вкладку « Головна». Попередньо, необхідно вибрати неперевірену комірку. У цій вкладці знаходиться інформація про формат.

Якщо його потрібно змінити, достатньо натиснути на стрілку і вибрати потрібний зі списку. Після цього система зробить новий розрахунок.

Список форматів у цьому розділі повний, але без описів та параметрів. Тому в деяких випадках користувач може знайти потрібний. Тоді краще скористатися іншим методом. Так само, як і в першому варіанті, слідує вибрати осередок. Після цього клацнути правою клавішею миші та відкрити команду «».

У відкритому вікнізнаходиться повний списокформатівз описом та налаштуваннями. Достатньо вибрати потрібний і натиснути на « ОК».

Відключаємо режим «Показати формули»

Іноді користувач може помітити, що замість числа відображено саме обчислення і формула в осередку не рахується. Тоді потрібно вимкнути цей режим. Після цього система буде виводити готовий результатрозрахунку, а чи не висловлювання.

Для вимкнення функції « Показати формули», слід перейти у відповідний розділ « Формули». Тут знаходиться вікно « Залежно». Саме в ньому розташована потрібна команда. Щоб відобразити список усіх залежностей, натисніть на стрілочці. З переліку необхідно вибрати Показати» та вимкнути цей режим, якщо він активний.

Помилки у синтаксисі

Часто неправильне відображення результату є наслідком помилок синтаксису. Таке трапляється, якщо користувач вводив обчислення самостійно і не вдавався до допомоги вбудованого майстра. Тоді всі осередки з помилками не будуть видавати розрахунок.

У такому разі слід перевірити правильне написаннякожного осередку, який видає невірний результат. Можна переписати всі значення, скориставшись вбудованим майстром.

Включаємо перерахунок формули

Усі обчислення можуть бути прописані правильно, але у разі зміни значень осередків перерахунок не відбувається. Тоді, може бути вимкнена функція автоматичної зміни розрахунку. Щоб це перевірити, перейдіть до розділу « Файл», потім « Параметри».

У відкритому вікні потрібно перейти у вкладку « Формули». Тут є параметри обчислень. Достатньо встановити прапорець на пункті « Автоматично» та зберегти зміни, щоб система почала проводити перерахунок.

Програма може проводити повний розрахунок, але замість готового значення відображається помилка і стовпець або осередок може не підсумовувати числа. Залежно від повідомлення, що виводиться, можна судити про те, яка несправність виникла, наприклад, ділення на нульабо неправильний формат.

Для того, щоб перевірити ще раз синтаксис і виправити помилку, слід перейти в розділ « Формули». У залежності знаходиться команда, яка відповідає за обчислення.

Відкриється вікно, яке відображає саму формулу. Тут слід натиснути на « Обчислити», щоб здійснити перевірку помилки.

Інші помилки

Також користувач може зіткнутися з іншими помилками. Залежно від причини їх можна виправити відповідним чином.

Формула не розтягується

Розтягування необхідне в тому випадку, коли кілька осередків мають проводити однакові обчисленняз різними значеннями. Але буває, що це не відбувається автоматично. Тоді слід перевірити, чи встановлено функцію автоматичного заповнення, яка розташована у параметрах.

Невірно вважається сума осередків

Сума також вважається невірно, якщо у книзі знаходяться приховані осередки. Їхній користувач не бачить, але система проводить розрахунок. У результаті програма відображає одне значення, а реальна сума має бути іншою.

Така ж проблема виникає, якщо відображено значення з цифрами після коми. У такому разі їх потрібно округлити, щоб обчислення проводилося правильно.

Формула не вважається автоматично

У відкритому вікні слід перейти до налаштування автоматичного перерахунку та встановити прапорецьна відповідній команді. Після цього потрібно зберегти зміни.