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

Буває, скопіювали таблицю в Excel, а числа в ній не вважаються, формули не працюють вExcel. Однією з причин може бути те, що значення осередків в текстовому форматі. Згадуємо, в Excel є два формати - формат осередків і формат значень. Детальніше про це читайте в статті «Перетворити дату в текст Excel».
Отже, розглянемо, чому вExcel не працює формула.
Потрібно скласти верхні числа в осередках. В осередках значення написані в текстовому форматі з установкою між рядками недрукованих знаків. Нам потрібно перетворити текстовий формат значень в числовий і виділити перше число в комірці. У нас така скопійована таблиця з 1С.
Перший етап.
Перетворимо значення осередків в числовий формат.
Виділяємо комірки. На закладці «Головна» в розділі «Редагування» натискаємо на кнопку «Знайти».
У діалоговому вікні в рядку «Знайти» ставимо кому ». У рядку «Замінити» нічого не ставимо.
Натискаємо кнопку «Замінити все». Вийде так.
Другий етап.
У цьому ж вікні «Знайти і замінити» (ми його не прибирали і осередки виділені залишилися), в рядку «Знайти» ставимо крапку. А в рядку «Замінити» ставимо кому. Натискаємо «Виділити все».

Вийшло так.
Прибираємо вікно «Знайти і замінити».
Третій етап.
Як виділити перше число в комірціExcel.
В осередку В1 пишемо таку формулу. = ЛЕВСИМВ (A1; ШУКАТИ (СИМВОЛ (10); A1) -1)
Цією формулою ми виділили з осередку перше число. Копіюємо формулу по стовпчику. Вийшло так.
В осередку В4 ми написали формулу складання. = B1 + B2 + B3
Автосумма не працює, але, формули, написані вручну працюють.
В 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 не вважає формулу? Якщо так, то напевно варто розділити цю проблему на дві основні причини:

Перший варіант - ви ввели формулу, вона відображається, але не вважається.

Другий варіант - ви ввели формулу, вона вважається, але виводиться помилка.

Детальніше про кожний випадок нижче:

Формулу ви ввели, але розрахунок не відбувається, в чому справа? Швидше за все формат осередки швидше за все Текстовий (правою кнопкою миші - Формат Осередків - вкладка Число - Текстовий)

Поставте формат Загальний або Числовий. Тепер необхідно зайти в осередок і активувати формулу - тобто вибрати її, увійти всередину (F2) і натиснути Enter.

«Хах», - скажете ви - «Так у мене таких рядків тисяча». Резонно. У випадках для першого типу проблеми, в лівому верхньому кутку осередку буде зелений трикутник, якщо Excel порахує, що введений текст в осередку відноситься до числовому формату. Тоді при виборі такого осередку з'явиться віконце «Число збережено як текст»:

Тепер виділіть всі такі осередки і натисніть на пункт меню - перетворити в число. Готово. Цей же інструмент доступний в Формули - Перевірка наявності помилок.

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

Просто помножте кожне число на 1 в сусідньому стовпці / осередку (наприклад, = A1 * 1), вийде число. Тепер у вас є числові значення, які можна скопіювати куди завгодно;)

Excel вважає формулу, але видає помилку

Як бути тут. Спершу пропоную почитати про помилки -. У цій же статті запропоновано вирішення багатьох проблем. Щоб розібратися з випадком, коли помилка в розрахунках, існує спеціальний інструмент - Обчислити формулу (Формули - Розділ Залежності формул - Обчислити формулу)

Якщо ви оберете цю команду, то відкриється спеціальне вікно, за допомогою якого ви зможете виконати будь-яку формулу покроково. наприклад

Натискайте Обчислити і крок за кроком у вас будуть з'являтися результати обчислення. Це дуже зручно, коли у вас 10 і більше умов або вкладених формул!

Формула не рахується. Інші випадки:

Так само я зустрічався з випадками, коли формула не розраховується, бо був випадково включений режим перегляду формул (Лента завдань - Формули - Розділ Залежності формул - Показати формули)

Досить часто видає помилку

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

Функція значить ()

Формула з такою функцією теж використовується для перетворення тексту в число. Детальніше

Діліться цією статтею з друзями, залишайте коментарі і удачі з електронними таблицями!

Якщо Excel не може правильно оцінити формулу або функцію робочого листа; він відобразить значення помилки - наприклад, # ІМ'Я ?, # ЧИСЛО !, # значить !, # Н / Д, # ПУСТО !, # ПОСИЛАННЯ! - в осередку, де знаходиться формула. розберемо типи помилок в Excel, Їх можливі причини, і як їх усунути.

Помилка # ІМ'Я?

Помилка # ІМ'Яз'являється, коли ім'я, яке використовується у формулі, було видалено або не було раніше визначено.

Причини виникнення помилки # ІМ'Я?:

1. Якщо у формулі використовується ім'я, яке було видалено або не визначене.

Помилки в Excel - Використання імені у формулі

усунення помилки: Визначте ім'я. Як це зробити описано в цій.

1. Помилка в написанні імені функції:

Помилки в Excel - Помилка в написанні функції ПОИСКПОЗ

усунення помилки: Перевірте правильність написання функції.

1. На засланні на діапазон комірок пропущений знак двокрапки (:).

Помилки в Excel - Помилка в написанні діапазону комірок

усунення помилкиРівняйте формулу. У наведеному вище прикладі це = СУММ (A1: A3).

1. У формулі використовується текст, що не укладений в подвійні лапки. Excel видає помилку, Так як сприймає такий текст як ім'я.

Помилки в Excel - Помилка в об'єднанні тексту з числом

усунення помилки: Укладіть текст формули в подвійні лапки.

Помилки в Excel - Правильне об'єднання тексту

Помилка # ЧИСЛО!

Помилка # ЧИСЛО! в Excel виводиться, якщо у формулі міститься неправильне число. наприклад:

1. Чи використовуєте негативне число, коли потрібно позитивне значення.

Помилки в Excel - Помилка у формулі, від'ємне значення аргументу у функції Корінь

усунення помилки: Перевірте коректність введених аргументів у функції.

1. Формула повертає число, яке занадто велике або занадто мало, щоб його можна було уявити в Excel.

Помилки в Excel - Помилка у формулі через занадто великого значення

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

Помилка # значить!

Дана помилка Excelвиникає в тому випадку, коли у формулі введений аргумент неприпустимого значення.

Причини помилки # значить !:

1. Формула містить прогалини, символи або текст, але в ній повинно бути число. наприклад:

Помилки в Excel - Підсумовування числових і текстових значень

усунення помилки: Перевірте правильність налаштувань типи аргументів у формулі.

1. В аргументі функції введений діапазон, а функція передбачається введення одного значення.

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

усунення помилки: Вкажіть в функції правильні аргументи.

1. При використанні формули масиву натискається клавіша Enter і Excel виводить помилку, так як сприймає її як звичайну формулу.

усунення помилки: Для завершення введення формули використовуйте комбінацію клавіш Ctrl + Shift + Enter.

Помилки в Excel - Використання формули масиву

Помилка # ПОСИЛАННЯ

Помилки в Excel - Помилка у формулі, через віддаленого стовпчика А

усунення помилки: Змініть формулу.

Помилка # СПРАВ / 0!

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

Помилки в Excel - Помилка # СПРАВ / 0!

усунення помилкиРівняйте формулу.

Помилка # Н / Д

Помилка # Н / Д в Excelозначає, що у формулі використовується недоступне значення.

Причини помилки # Н / Д:

1. При використанні функції ВПР, ГПР, ПЕРЕГЛЯД, ПОИСКПОЗ використовується невірний аргумент шукане_значення:

Помилки в Excel - Шуканого значення немає в просматриваемом масиві

усунення помилки: Задайте правильний аргумент шукане значення.

1. Помилки у використанні функцій ВПР або ГПР.

усунення помилки: Див. Присвячений

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

Помилки в Excel - Помилки в формулі масиву

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

1. У функції не задані один або кілька обов'язкових аргументів.

Помилки в Excel - Помилки у формулі, немає обов'язкового аргументу

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

Помилка # ПУСТО!

Помилка # ПУСТО! в Excelвиникає коли, у формулі використовуються непересічні діапазони.

Помилки в Excel - Використання у формулі СУМ непересічні діапазони

усунення помилки: Перевірте правильність написання формули.

Помилка ####

Причини виникнення помилки

1. Ширини шпальти недостатньо, щоб відобразити вміст комірки.

Помилки в Excel - Збільшення ширини шпальти для відображення значення в осередку

усунення помилки: Збільшення ширини шпальти / стовпців.

1. Осередок містить формулу, яка повертає від'ємне значення при розрахунку дати або часу. Дата і час в Excel повинні бути позитивними значеннями.

Помилки в Excel - Різниця дат і годин не повинна бути негативною

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

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

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

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

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

Є дві найбільш ймовірних причини.

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

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

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