Читатели Лайфхакера уже знакомы с Денисом Батьяновым , который делился с нами . Сегодня Денис расскажет о том, как избежать самых распространённых проблем с Excel, которые мы зачастую создаём себе самостоятельно.
Сразу оговорюсь, что материал статьи предназначается для начинающих пользователей Excel. Опытные пользователи уже зажигательно станцевали на этих граблях не раз, поэтому моя задача уберечь от этого молодых и неискушённых «танцоров».
Вы не даёте заголовки столбцам таблиц
Многие инструменты Excel, например: сортировка, фильтрация, умные таблицы, сводные таблицы, - подразумевают, что ваши данные содержат заголовки столбцов. В противном случае вы либо вообще не сможете ими воспользоваться, либо они отработают не совсем корректно. Всегда заботьтесь, чтобы ваши таблицы содержали заголовки столбцов.
Пустые столбцы и строки внутри ваших таблиц
Это сбивает с толку Excel. Встретив пустую строку или столбец внутри вашей таблицы, он начинает думать, что у вас 2 таблицы, а не одна. Вам придётся постоянно его поправлять. Также не стоит скрывать ненужные вам строки/столбцы внутри таблицы, лучше удалите их.
На одном листе располагается несколько таблиц
Если это не крошечные таблицы, содержащие справочники значений, то так делать не стоит.
Вам будет неудобно полноценно работать больше чем с одной таблицей на листе. Например, если одна таблица располагается слева, а вторая справа, то фильтрация одной таблицы будет влиять и на другую. Если таблицы расположены одна под другой, то невозможно воспользоваться закреплением областей, а также одну из таблиц придётся постоянно искать и производить лишние манипуляции, чтобы встать на неё табличным курсором. Оно вам надо?
Данные одного типа искусственно располагаются в разных столбцах
Очень часто пользователи, которые знают Excel достаточно поверхностно, отдают предпочтение такому формату таблицы:
Казалось бы, перед нами безобидный формат для накопления информации по продажам агентов и их штрафах. Подобная компоновка таблицы хорошо воспринимается человеком визуально, так как она компактна. Однако, поверьте, что это сущий кошмар - пытаться извлекать из таких таблиц данные и получать промежуточные итоги (агрегировать информацию).
Дело в том, что данный формат содержит 2 измерения: чтобы , вы должны определиться со строкой, перебирая филиал, группу и агента. Когда вы найдёте нужную стоку, то потом придётся искать уже нужный столбец, так как их тут много. И эта «двухмерность» сильно усложняет работу с такой таблицей и для стандартных инструментов Excel - формул и сводных таблиц.
Если вы построите сводную таблицу, то обнаружите, что нет возможности легко получить данные по году или кварталу, так как показатели разнесены по разным полям. У вас нет одного поля по объёму продаж, которым можно удобно манипулировать, а есть 12 отдельных полей. Придётся создавать руками отдельные вычисляемые поля для кварталов и года, хотя, будь это всё в одном столбце, сводная таблица сделала бы это за вас.
Если вы захотите применить стандартные формулы суммирования типа СУММЕСЛИ (SUMIF), СУММЕСЛИМН (SUMIFS), СУММПРОИЗВ (SUMPRODUCT), то также обнаружите, что они не смогут эффективно работать с такой компоновкой таблицы.
Разнесение информации по разным листам книги «для удобства»
Ещё одна распространенная ошибка — это, имея какой-то стандартный формат таблицы и нуждаясь в аналитике на основе этих данных, разносить её по отдельным листам книги Excel. Например, часто создают отдельные листы на каждый месяц или год. В результате объём работы по анализу данных фактически умножается на число созданных листов. Не надо так делать. Накапливайте информацию на ОДНОМ листе.
Информация в комментариях
Часто пользователи добавляют важную информацию, которая может им понадобиться, в комментарий к ячейке. Имейте в виду, то, что находится в комментариях, вы можете только посмотреть (если найдёте). Вытащить это в ячейку затруднительно. Рекомендую лучше выделить отдельный столбец для комментариев.
Бардак с форматированием
Определённо не добавит вашей таблице ничего хорошего. Это выглядит отталкивающе для людей, которые пользуются вашими таблицами. В лучшем случае этому не придадут значения, в худшем — подумают, что вы не организованы и неряшливы в делах. Стремитесь к следующему:
Объединение ячеек
Используйте объединение ячеек только тогда, когда без него никак. Объединенные ячейки сильно затрудняют манипулирование диапазонами, в которые они входят. Возникают проблемы при перемещении ячеек, при вставке ячеек и т.д.
Объединение текста и чисел в одной ячейке
Тягостное впечатление производит ячейка, содержащая число, дополненное сзади текстовой константой « РУБ.» или » USD», введенной вручную. Особенно, если это не печатная форма, а обычная таблица. Арифметические операции с такими ячейками естественно невозможны.
Числа в виде текста в ячейке
Избегайте хранить числовые данные в ячейке в формате текста. Со временем часть ячеек в таком столбце у вас будут иметь текстовый формат, а часть в обычном. Из-за этого будут проблемы с формулами.
Если ваша таблица будет презентоваться через LCD проектор
Выбирайте максимально контрастные комбинации цвета и фона. Хорошо выглядит на проекторе тёмный фон и светлые буквы. Самое ужасное впечатление производит красный на чёрном и наоборот. Это сочетание крайне неконтрастно выглядит на проекторе — избегайте его.
Страничный режим листа в Excel
Это тот самый режим, при котором Excel показывает, как лист будет разбит на страницы при печати. Границы страниц выделяются голубым цветом. Не рекомендую постоянно работать в этом режиме, что многие делают, так как в процессе вывода данных на экран участвует драйвер принтера, а это в зависимости от многих причин (например, принтер сетевой и в данный момент недоступен) чревато подвисаниями процесса визуализации и пересчёта формул. Работайте в обычном режиме.
Ещё больше полезной информации про Excel можно узнать на
В данной статье мы рассмотрим, что такое Автосумма, и рассмотрим наиболее эффективные способы использования Автосуммы в Excel . Вы узнаете, как пользоваться автосуммой в Excel , увидите, как посчитать автосумму в Excel столбца или строки с помощью кнопки Автосумма , суммировать только видимые ячейки, всего выделенного диапазона по вертикали и горизонтали за один раз, и узнать наиболее распространенную причину, по которой Автосумма в Excel выдает 0 .
Наиболее распространенная функция, которую используют большинство пользователей Microsoft Excel, это функция СУММ. Неудивительно, что Microsoft Excel решили добавить специальную кнопку на ленту Excel, которая автоматически вставляет функцию СУММ.
По сути, Автосумма в Excel автоматически вводит формулу для суммирования чисел на вашем листе. В данной статье мы рассмотрим следующую информацию:
Где находится Автосумма в Excel
Кнопку Автосумма можно найти в двух местах на ленте Excel.
- Вкладка «ГЛАВНАЯ » --> группа «Редактирование » --> «Автосумма »:
Автосумма в Excel - Кнопка Автосуммы в Excel
- Вкладка «ФОРМУЛЫ » --> группа «Библиотека функций » --> «Автосумма »:
Автосумма в Excel - Кнопка Автосумма во вкладке Формулы
Теперь где Автосумма в Excel вы знаете.
Как сделать автосумму в Excel
Всякий раз, когда вам нужно посчитать автосумму одного диапазона ячеек, будь то столбец, строка или нескольких соседних столбцов или строк, вы можете использовать формулу в Excel Автосумма для автоматического создания соответствующей формулы СУММ.
Чтобы использовать Автосумму в Excel , выполните следующие 3 простых действия:
- Выберите ячейку рядом с цифрами, которые вы хотите просуммировать:
- Чтобы суммировать столбец, выберите ячейку сразу под последним значением в столбце.
- Чтобы суммировать строку, выберите ячейку справа от последнего числа в строке.
Автосумма в Excel - Выбор ячейки, после который вы хотите посчитать сумму
- Нажмите кнопку Автосумма на вкладке «ГЛАВНАЯ » или «ФОРМУЛЫ ».
В выбранной ячейке появляется формула суммы, а диапазон суммируемых ячеек выделяется (B2:B6 в этом примере):
Автосумма в Excel - Кликните на кнопке Автосумма на вкладке Главная и Формулы (в данном случае на вкладке Формула)
В большинстве случаев Excel выбирает правильный диапазон для общей суммы. В редких случаях, когда выбран неправильный диапазон, вы можете исправить его вручную, введя нужный диапазон в формуле или перетащив курсор через ячейки, которые вы хотите суммировать.
Чтобы суммировать несколько столбцов или строк за раз, выберите несколько ячеек внизу или справа от вашей таблицы, соответственно, а затем нажмите кнопку Автосумма или используйте . Для получения дополнительной информации см. пункт .
- Нажмите клавишу 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 не может распознать формулу, которую вы пытаетесь создать, может появиться сообщение об ошибке такого вида:
К сожалению, это значит, что Excel не понимает, что вы пытаетесь делать, так что, возможно, вам лучше выйти и начать все заново.
Прежде всего нажмите кнопку ОК или клавишу ESC , чтобы закрыть сообщение об ошибке.
Вы вернетесь к ячейке с неправильной формулой, для которой будет включен режим редактирования и выделено проблемное место. Если вы не знаете, что с этим делать, и хотите начать заново, выйдите из режима редактирования, для чего еще раз нажмите клавишу ESC или кнопку Отмена в строке формул.
Если вы хотите перейти вперед, в следующем контрольном списке содержатся инструкции по устранению неполадок, которые помогут вам узнать, что могло быть не так.
Видите ли вы ошибку решетки (#)?
В Excel выводятся различные ошибки решетки (#), такие как #VALUE!, #REF!, #NUM, #N/A, #DIV/0!, #NAME? и #NULL!, чтобы указать, что в формуле не работает правильно. Например, #VALUE! ошибка вызвана неправильным форматированием или неподдерживаемыми типами данных в аргументах. Кроме того, вы увидите #REF! ошибка, если формула ссылается на ячейки, которые были удалены или заменены другими данными. Рекомендации по устранению неполадок будут отличаться для каждой ошибки.
Примечание: # # # # не является ошибкой, связанной с формулой. Это означает, что столбец недостаточно широк для отображения содержимого ячейки. Просто перетащите столбец, чтобы расширить его, или перейдите на вкладку главная > формат > Автоподбор ширины столбца .
Ознакомьтесь со следующими разделами, в которых рассматриваются ошибки, которые вы видите:
Каждый раз, когда вы открываете электронную таблицу с формулами, которые ссылаются на значения в других электронных таблицах, вам будет предложено обновить ссылки или оставить их как есть.
Excel выводит диалоговое окно выше, чтобы убедиться в том, что формулы в текущем листе всегда указывают на самые обновленные значения в случае, если эталонное значение изменилось. Вы можете обновить ссылки или пропустить этот шаг. Даже если вы решили не обновлять ссылки, вы можете в любой момент сделать это вручную непосредственно в электронной таблице.
Вы также можете отключить показ этого диалогового окна при открытии файла. Для этого перейдите в раздел Файл > Параметры > Дополнительно > Общие и снимите флажок Запрашивать об обновлении автоматических связей .
Формула возвращает синтаксическую конструкцию, а не значение
Если формула не выдает значение, воспользуйтесь приведенными ниже инструкциями.
Формула не вычисляется
Если формула не вычисляется, проверьте, включена ли в Excel функция автоматического вычисления. Формулы не вычисляются, если включено ручное вычисление. Чтобы проверить, включено ли автоматическое вычисление , выполните указанные ниже действия.
Дополнительные сведения о вычислениях можно найти в разделе Изменение пересчета формул, итераций или точности .
Формула содержит одну или несколько циклических ссылок
Циклическая ссылка возникает, когда формула ссылается на ячейку, в которой она находится. Исправление предназначено для того, чтобы переместить формулу в другую ячейку или изменить синтаксис формулы, чтобы избежать циклических ссылок. Однако в некоторых сценариях вам может потребоваться циклическая ссылка, так как они приводят к итерации функций, повторяются до тех пор, пока не будет выполнено определенное числовое условие. В таком случае вам потребуется включить или отключить циклическую ссылку .
Дополнительные сведения о циклических ссылках можно найти в разделе Удаление или разрешение циклической ссылки
Начинается ли функция со знака равенства (=)?
Если запись начинается не со знака равенства, она не считается формулой и не вычисляется (это распространенная ошибка).
Если ввести СУММ(A1:A10) , Excel отобразит текстовую строку СУММ(A1:A10) вместо результата формулы. Кроме того, при вводе 11/2 в Excel отображается дата, например 2-ноя или 11/02/2009, вместо деления 11 на 2.
Чтобы избежать подобных неожиданных результатов, всегда начинайте формулу со знака равенства. Например, введите = СУММ(A1:A10) и =11/2 .
Соблюдается ли соответствие открывающих и закрывающих скобок?
Если в формуле используется функция, для ее правильной работы важно, чтобы у каждой открывающей скобки была закрывающая, поэтому убедитесь, что каждой скобке соответствует парная. Например, формула =ЕСЛИ(B5<0);"Недопустимо";B5*1,05) не будет работать, так как в ней две закрывающие и только одна открывающая скобка. Правильный вариант этой формулы выглядит следующим образом: =ЕСЛИ(B5<0;"Недопустимо";B5*1,05) .
Включает ли синтаксис все обязательные аргументы?
Функции в Excel имеют аргументы - значения, которые необходимо указать, чтобы функция работала. Без аргументов работает лишь небольшое количество функций (например, ПИ или СЕГОДНЯ). Проверьте синтаксис формулы, который отображается, когда вы начинаете вводить функцию, чтобы убедиться в том, что указаны все обязательные аргументы.
Если вам нужно быстро просмотреть синтаксис определенной функции, см. список функций Excel (по категориям) .
Есть ли в формулах неформатированные числа?
Не вводите числа, отформатированные с помощью знаков доллара ($) и десятичных разделителей (,) в формулах, так как знаки доллары означают абсолютные ссылки и запятые - это разделители аргументов. Вместо $1,000 в формуле необходимо ввести 1000.
Если вы используете в аргументах форматированные числа, вы получаете непредвиденные результаты вычислений, но вы также можете увидеть ошибку #NUM! . Например, если ввести формулу = ABS (-2 134) , чтобы найти абсолютное значение-2134, в Excel появится #NUM! ошибка, так как Функция ABS принимает только один аргумент, и она видит числа-2 и 134 в качестве отдельных аргументов.
Примечание: Результат формулы можно отформатировать с использованием десятичных разделителей и обозначений денежных единиц после ввода формулы с неформатированными числами (константами). Как правило, использовать константы непосредственно в формулах не рекомендуется: их бывает сложно найти в случае, если потребуется обновить значения, а кроме того, при их вводе часто допускаются опечатки. Гораздо удобнее помещать константы в отдельные ячейки, в которых они будут доступны и на них легко ссылаться.
Формула может не возвратить ожидаемые результаты, если тип данных ячейки не подходит для вычислений. Например, если ввести простую формулу =2+3 в ячейке, которая имеет текстовый формат, Excel не сможет вычислить введенные данные. В ячейке будет отображаться строка =2+3 . Чтобы исправить эту ошибку, измените тип данных ячейки с Текстовый на Общий , как описано ниже.
Выделите ячейку.
На вкладке Главная щелкните стрелку рядом со списком Числовой формат (или нажмите CTRL+1 ) и выберите пункт Общий .
Нажмите клавишу F2 , чтобы перейти в режим правки, а затем - клавишу ВВОД , чтобы подтвердить формулу.
Если ввести дату в ячейку, которая имеет числовой тип данных, она может быть отображена как числовое значение, а не как дата. Чтобы это число отображалось в виде даты, в коллекции Числовой формат выберите формат Дата .
Пытаетесь ли вы выполнить умножение, не используя символ *?
В качестве оператора умножения в формуле часто используют крестик (x ), однако в этих целях в Excel необходимо использовать звездочку (*). Если в формуле использовать знак "x", появится сообщение об ошибке и будет предложено исправить формулу, заменив x на "*".
Отсутствуют ли кавычки вокруг текста в формулах?
Если в формуле содержится текст, его нужно заключить в кавычки.
Например, формула ="Сегодня " & ТЕКСТ(СЕГОДНЯ();"дддд, дд.ММ") объединяет текстовую строку "Сегодня " с результатами функций ТЕКСТ и СЕГОДНЯ и возвращает результат наподобие следующего: Сегодня понедельник, 30.05 .
В формуле в поле "сегодня" есть пробел перед закрывающей кавычкой, чтобы предоставить пустое пространство между словами "сегодня" и "Понедельник, 30 мая". При отсутствии кавычек вокруг текста в формуле может отображаться #NAME? ошибка .
Включает ли формула больше 64 функций?
Заключены ли имена листов в апострофы?
Например, чтобы возвратить значение из ячейки D3 на листе с квартальными данными в книге, введите: = "квартальные данные"! D3 . Без кавычек вокруг имени листа в формуле отображается #NAME? ошибка .
Вы также можете щелкнуть значения или ячейки на другом листе, чтобы добавить ссылку на них в формулу. Excel автоматически заключит имена листов в кавычки.
Если формула ссылается на внешнюю книгу, правильно ли указан путь к книге?
Например, для ссылки на ячейки a1 – A8 на листе Sales в книге операций Q2, открытой в Excel, введите: = Sales! A1: A8 . При отсутствии квадратных скобок в формуле отображается #REF! .
Если книга не открыта в Excel, введите полный путь к файлу.
Например =ЧСТРОК("C:\Мои документы\[Операции за II квартал.xlsx]Продажи"!A1:A8) .
Примечание: Если полный путь содержит пробелы, необходимо заключить его в апострофы (в начале пути и после имени книги перед восклицательным знаком).
Совет: Чтобы получить путь к другой книге, проще всего открыть ее, ввести в исходной книге знак равенства (=), а затем с помощью клавиш ALT+TAB перейти во вторую книгу и выделить на нужном листе любую ячейку. После этого книгу-источник можно закрыть. Формула автоматически обновится, и в ней отобразится полный путь к имени листа с правильным синтаксисом. При необходимости этот путь можно копировать и вставлять.
Пытаетесь ли вы делить числовые значения на нуль?
Деление ячейки на другую ячейку, которая имеет нулевое значение (0) или отсутствие значения, приводит к #DIVу или 0! Error .
Чтобы устранить эту ошибку, можно просто проверить, существует ли знаменатель.
ЕСЛИ(B1;A1/B1;0)
Смысл это формулы таков: ЕСЛИ B1 существует, вернуть результат деления A1 на B1, в противном случае вернуть 0.
Ссылается ли формула на удаленные данные?
Прежде чем удалять данные в ячейках, диапазонах, определенных именах, листах и книгах, всегда проверяйте, нет есть ли у вас формул, которые ссылаются на них. Вы сможете заменить формулы их результатами перед удалением данных, на которые имеется ссылка.
Если вам не удается заменить формулы их результатами, прочтите сведения об ошибках и возможных решениях:
Если формула ссылается на ячейки, которые были удалены или заменены другими данными, и возвращает ошибку #REF! Выделите ячейку с #REF! Если позиция, которую вы указали, находится перед первым или после последнего элемента в поле, формула возвращает ошибку #ССЫЛКА!. В строке формул нажмите кнопку #REF! и удалите его. Затем снова введите диапазон для формулы.
Если определенное имя отсутствует, а формула, которая ссылается на это имя, возвращает #NAME? ошибка Определите новое имя, которое ссылается на нужный диапазон, или измените формулу таким образом, чтобы она ссылалась непосредственно на диапазон ячеек (например, a2: D8).
Если лист отсутствует, а формула, ссылающаяся на него, возвращает #REF! Ошибка: не удается устранить эту проблему, к сожалению, лист, который был удален, невозможно восстановить.
Если отсутствует книга, это не влияет на формулу, которая ссылается на нее, пока не обновить формулу.
Например, если используется формула =[Книга1.xlsx]Лист1"!A1 , а такой книги больше нет, значения, ссылающиеся на нее, будут доступны. Но если изменить и сохранить формулу, которая ссылается на эту книгу, появится диалоговое окно Обновить значения с предложением ввести имя файла. Нажмите кнопку Отмена и обеспечьте сохранность данных, заменив формулу, которая ссылается на отсутствующую книгу, ее результатами.
Здравствуйте, друзья. Бывало ли у Вас, что, после ввода формулы, в ячейке отображается сама формула вместо результата вычисления? Это немного обескураживает, ведь Вы сделали все правильно, а получили непонятно что. Как заставить программу вычислить формулу в этом случае?
Для начала нужно разобраться с причинами такого поведения, после чего исправлять ситуацию. А причины могут быть такими.
Включено отображение формул
В Экселе есть режим проверки вычислений. Когда он включен, вы видите на листе формулы. Как и зачем применять отображение формул, я рассказывал в . Проверьте, возможно он активирован, тогда отключите. На ленте есть кнопка Формулы – Зависимости формул – Показать формулы . Если она включена – кликните, по ней, чтобы отключить.
Часто показ формул включают случайно, нажав комбинацию клавиш Ctrl+` .
Формула воспринимается программой, как текст
Если Excel считает, что в ячейке текстовая строка, он не будет вычислять значение, а просто выведет на экран содержимое. Причин тому может быть несколько:
- В ячейке установлен текстовый формат
. Для программы это сигнал, что содержимое нужно вывести в том же виде, в котором оно хранится.
Измените формат на Числовой, как сказано . - Нет знака «=» вначале формулы
. Как я рассказывал в , любые вычисления начинаются со знака «равно».
Если его упустить – содержимое будет воспринято, как текст. Добавьте знак равенства и пересчитайте формулу. - Пробел перед «=»
. Если перед знаком равенства случайно указан пробел, это тоже будет воспринято, как текст.
Эту ошибку сложно заметить, поэтому придется проверять вручную. Удалите лишний пробел и пересчитайте. - Формула заключена в кавычки
. Такая ошибка случается, когда работаем с текстовыми данными. Само собой, информация в кавычках трактуется, как текст.
Удалите лишние кавычки, нажмите Enter , чтобы пересчитать.
Я перечислил все распространенные случаи, когда формула отображается вместо результата. Думаю, Ваш случай будет в приведенном списке, устранить проблему не составит труда. Если не получилось – пишите в комментариях.
Обещает быть очень интересной. Мы рассмотрим некоторые простые приемы быстрого написания формул. Если довести их до автоматизма, Ваша работа будет выполняться проще и быстрее. Подпишитесь на обновления, чтобы не пропустить!
Приложение Эксель используют не только для создания таблиц. Его главным предназначением является расчет чисел по формулам. Достаточно вписать в ячейки новые значения и система автоматически пересчитает их. Однако, в некоторых случаях расчет не происходит. Тогда, необходимо выяснить, почему Эксель не считает сумму.
Основные причины неисправности
Эксель может не считать сумму или формулы по многим причинам. Проблема часто заключается, как в неправильной формуле , так и в системных настройках книги. Поэтому, рекомендуется воспользоваться несколькими советами, чтобы выяснить, какой именно подходит в данной конкретной ситуации.
Изменяем формат ячеек
Программа выводит неправильные расчеты, если указанные форматы не соответствуют значению , которое находится в ячейке. Тогда вычисление или вообще не будет применяться, или выдавать совсем другое число. Например, если формат является текстовым, то расчет проводится не будет. Для программы, это только текст, а не числа. Также, может возникнуть ситуация, когда формат не соответствует действительному . В таком случае, у пользователя не получится правильно вставить вычисление, и Эксель не посчитает сумму и не рассчитает результат формулы.
Чтобы проверить, действительно ли дело в формате, следует перейти во вкладку «Главная ». Предварительно, необходимо выбрать непроверенную ячейку. В этой вкладке находится информация о формате .
Если его нужно изменить, достаточно нажать на стрелочку и выбрать требуемый из списка
. После этого, система произведет новый расчет.
Список форматов в данном разделе полный, но без описаний и параметров. Поэтому в некоторых случаях пользователь не может найти нужный. Тогда, лучше воспользоваться другим методом. Так же, как и в первом варианте, следует выбрать ячейку
. После этого кликнуть правой клавишей мыши и открыть команду «».
В открытом окне находится полный список форматов
с описанием и настройками. Достаточно выбрать нужный и нажать на «ОК
».
Отключаем режим «Показать формулы»
Иногда пользователь может заметить, что вместо числа отображено само вычисление и формула в ячейке не считается. Тогда, нужно отключить данный режим. После этого система будет выводить готовый результат расчета, а не выражения.
Для отключения функции «Показать формулы
», следует перейти в соответствующий раздел «Формулы
». Здесь находится окно «Зависимости
». Именно в нем расположена требуемая команда. Чтобы отобразить список всех зависимостей, следует кликнуть на стрелочке. Из перечня необходимо выбрать «Показать
» и отключить данный режим, если он активен.
Ошибки в синтаксисе
Часто, неправильное отображение результата является следствием ошибок синтаксиса
. Такое случается, если пользователь вводил вычисление самостоятельно и не прибегал к помощи встроенного мастера. Тогда, все ячейки с ошибками не будут выдавать расчет.
В таком случае, следует проверить правильное написание каждой ячейки, которая выдает неверный результат. Можно переписать все значения, воспользовавшись встроенным мастером.
Включаем пересчет формулы
Все вычисления могут быть прописаны правильно, но в случае изменения значений ячеек, перерасчет не происходит. Тогда, может быть отключена функция автоматического изменения расчета
. Чтобы это проверить, следует перейти в раздел «Файл
», затем «Параметры
».
В открытом окне необходимо перейти во вкладку «Формулы
». Здесь находятся параметры вычислений. Достаточно установить флажок на пункте «Автоматически
» и сохранить изменения, чтобы система начала проводить перерасчет.
Программа может проводить полный расчет, но вместо готового значения отображается ошибка и столбец или ячейка может не суммировать числа. В зависимости от выводимого сообщения можно судить о том, какая неисправность возникла, например, деление на ноль или неправильный формат .
Для того, чтобы перепроверить синтаксис и исправить ошибку, следует перейти в раздел «Формулы
». В зависимостях находится команда, которая отвечает за вычисления.
Откроется окно, которое отображает саму формулу. Здесь, следует нажать на «Вычислить », чтобы провести проверку ошибки.
Другие ошибки
Также, пользователь может столкнуться с другими ошибками. В зависимости от причины, их можно исправить соответствующим образом.
Формула не растягивается
Растягивание необходимо в том случае, когда несколько ячеек должны проводить одинаковые вычисления с разными значениями. Но бывает, что этого не происходит автоматически. Тогда, следует проверить, что установлена функция автоматического заполнения , которая расположена в параметрах.
Неверно считается сумма ячеек
Сумма также считается неверно, если в книге находятся скрытые ячейки . Их пользователь не видит, но система проводит расчет. В итоге, программа отображает одно значение, а реальная сумма должна быть другой.
Такая же проблема возникает, если отображены значения с цифрами после запятой . В таком случае их требуется округлить, чтобы вычисление производилось правильно.
Формула не считается автоматически
В открытом окне следует перейти к настройке автоматического перерасчета и установить флажок на соответствующей команде. После этого требуется сохранить изменения.
