Формула эксель не работает: Excel не считает формулы в ячейках. Не производит вычислений

Содержание

Пример как работать с формулой массива в таблице Excel

Как узнать, является ли данная формула формулой массива? Что вообще она означает?

На этапе создания формула (или также функция) сама по себе не является ни формулой массива, ни обычной формулой. Это вы определяете, как Excel должен истолковать формулу, которую вы вводите. То, что формула является формулой массива – это не столько особенность самой формулы, а скорее способом, которым программа Excel введенную формулу «обрабатывает». Подтверждение формулы с помощью сочетания клавиш «Ctrl + Shitf + Enter» – это является для Excelя командой на выполнение (обработку данных) как массив вычислений. Тогда он используется в качестве аргумента функции и возвращает в качестве результата вычислений таблицу (массив данных).

Примеры формул массива и отличие от обычных формул в Excel

Некоторые функции Excelя по умолчанию в качестве аргумента принимают диапазон ячеек (массив) и в результате возвращают одно значение. Отличными примерами являются функции СУММ, СЧЕТЕСЛИ, СРЗНАЧ и т. д. Для этих функций не имеет никакого значения, вводите ли вы их как функции массива или нет. Они и так обрабатывают таблицы, и найдут выход (сработают правильно) из любой ситуации. Вот такие маленькие Excel-евские приспособленцы.

К счастью, существуют другие функции, которые работают совершенно иначе, т.е. в зависимости от вашего решения относительно их принадлежности к «функциям массива» (иногда они вообще не хотят работать). Прекрасным примером является функция ЕСЛИ.



Когда формула является формулой массива, а когда обычной?

Для начала определимся как выглядит обычный массив значений в Excel. Это значения, которые находятся внутри фигурных скобок и разделены между собой точкой с запятой. Например:

{23;-32;15;7} – это синтаксис массива значений в Excel. Он может быть использован в аргументах функций.

Диапазон ячеек A1:A4 – так же является массивом значений в Excel. Естественно так же используется в аргументах функций. Например сравним результаты вычислений двух формул: =СУММ(A1:A4) и =СУММ({23;-32;15;7}) – они идентичны:

Визуально формула массива находится так же внутри фигурных скобок, но они не должны быть введены вручную, а только лишь при помощи комбинации клавиш CTRL+SHIFT+Enter. Если ввести вручную фигурные скобки, то формула не будет выполнятся в массиве – это будет синтаксическая ошибка в Excel.

Формула массива (введенная с помощью сочитания CTRL+SHIFT+Enter) будет использоваться везде, где вы хотите, чтобы функция, которая обычно работает с отдельными значениями (ячейками), внезапно повела себя иначе и приняла в качестве аргумента и вернула в качестве результата массив значений (таблицу). Вернемся к уже упомянутой функции ЕСЛИ. В качестве аргумента она принимает логическое значение ИСТИНА или ЛОЖЬ. В классической форме:

=ЕСЛИ($A$1>0;»больше»;»меньше»)

Если значение в ячейке A1 больше нуля, в качестве аргумента функция получит значение ИСТИНА и в качестве результата вернет текстовую строку «больше». Однако, если бы вы хотели проверить несколько ячеек сразу и передать результат такой проверки другой функции, вы должны были бы использовать вышеуказанную формулу как формулу массива. Для этого при вводе нажмем сочитание клавиш CTRL+SHIFT+Enter, а не как обычно (просто Enter):

{=ЕСЛИ($A$1:$A$4>0;»больше»;»меньше»)}

В качестве аргумента функция принимает целый диапазон $A$1:$A$4. В результате проверки каждой ячейки диапазона в памяти компьютера создается таблица значений в массиве. Схематически таблицу можно отобразить так:

А так выглядят эти значения в массиве:

{ИСТИНА;ЛОЖЬ;ИСТИНА;ИСТИНА}

Например, чтобы прочитать этот массив и получить второе значение (сделать выборку значений) воспользуемся функцией:

=ИНДЕКС({ИСТИНА;ЛОЖЬ;ИСТИНА;ИСТИНА};2)

Тоже самое что и:

Затем создается другая таблица, значения которой зависят непосредственно от значений в первой таблице. Если элемент в первом массиве имеет значение ИСТИНА, во втором массиве он примет значение «больше». Если он имеет значение ЛОЖЬ, элемент во второй таблице примет значение «меньше». После этой операции первая таблица удаляется из памяти компьютера, и в конечном счете, функция возвращает массив {«больше», «меньше», «больше», «больше»}. Схематически вторую таблицу можно отобразить так:

Так же ее можно прочитать функцией:

=ИНДЕКС({«больше»;»меньше»;»больше»;»больше»};2)

В примере с функцией ЕСЛИ была введена формула массива только в одну ячейку, поэтому в результате получили только одно значение, соответствующее первому значению в таблице. Однако достаточно ввести формулу массива в диапазон ячеек, чтобы увидеть все значения массива результатов. Для этого выделяем диапазон из нескольких ячеек, нажимаем клавишу F2 (или заново вводим формулу вручную) и жмем CTRL+SHIFT+Enter.

В примере (рисунок ниже) видно, что таблица результатов содержит ровно четыре элемента, о которых я упоминал выше.

Примеры как использовать формулу массива в Excel

Это все хорошо, но возникают некоторые вопросы: «Зачем же нужна формула массива?» или «Как или где использовать формулу в массиве?», «Чем она лучше обычной формулы?».

Разумеется, массив, возвращаемый функцией ЕСЛИ, может передаваться далее на «обработку» в качестве аргумента для другой функции.

Пример. Представим, что вы хотели бы найти сумму ячеек B7:B10, но только тех, которые имеют значение больше нуля. Конечно же, вы можете использовать функцию СУММЕСЛИ, однако в нашем примере мы хотим сделать это только с помощью формулы массива. Суммируя значения ячеек нашего диапазона, необходимо будет как-то избавиться от значения «-32». Функции СУММ необходимо передать массив, в котором содержатся только значения больше нуля. Везде там, где значение меньше нуля, мы заменяем его на ноль, что, конечно же, не повлияет на результат. Как вы уже знаете, временную таблицу с соответствующими значениями вы можете получить, используя функцию ЕСЛИ. В конечном итоге соответствующая формула будет выглядеть так:

Вводим формулу и не забываем для подтверждения ввода нажать комбинацию клавиш CTRL+SHIFT+Enter. В результате проверки каждой ячейки диапазона $A$1:$A$4 (является ли значение больше нуля) в памяти компьютера создается массив {ИСТИНА; ЛОЖЬ; ИСТИНА; ИСТИНА}. Затем создается очередная таблица. Если элемент в первом массиве имеет значение ИСТИНА, то во второй таблице будет отображаться значение из соответствующей ячейки. Если он имеет значение ЛОЖЬ, то элемент во второй таблице примет значение 0. После этой операции первая таблица удаляется из памяти компьютера, и в конечном итоге функция ЕСЛИ возвращает массив {23; 0; 15; 7}. Затем эта таблица передается в качестве аргумента функции =СУММ({23; 0; 15; 7}), которая, согласно своему предназначению, возвращает сумму всех элементов в таблице. В нашем примере сумма равна 45. В завершении, посмотрите, что произойдет, если вы скажете Excelю обработать приведенную выше формулу не как формулу массива.

Ни одна из описанных выше таблиц, в этом случае, не будет создана. Только одна ячейка диапазона будет проверена (ячейка в той же строке, в которой находится функция). В нашем случае 15>0 значит, как первый аргумент функция ЕСЛИ получит логическое значение ИСТИНА. Затем ВЕСЬ диапазон A1:A4 будет передан функции СУММ, и в результате функция возвращает значение равное 13 (23-32+15+7). Если бы в ячейке вместо значения 15 было число меньше нуля, функция ЕСЛИ в качестве аргумента получила бы значение ЛОЖЬ, и, следовательно, только значение нуля было бы передано функции СУММ. Наша функция СУММ в результате также вернет значение равное нулю.

Как отличать формулу массива от обычной формулы

При нажатии клавиш CTRL+SHIFT+Enter для подтверждения ввода в строке формул будут отображены фигурные скобки по краям. Значит данная формула выполняется в массиве. Но что если еще на этапе создания неизвестно какой тип формул следует применять?

Правильное «распознавание», когда следует нажимать CTRL+SHIFT+Enter, а когда просто Enter полностью зависит от понимания того, как работают массивы в формулах. Когда вы это поймете, сможете сказать, что конкретную формулу следует вводить (подтверждать) сочетанием клавиш – CTRL+SHIFT+Enter.

Конечно же, не подтвержденная, а просто как формула также может возвращать КАКОЙ-ТО результат (в чем вы могли только что убедиться сами). Однако, если вы сможете прочитать формулу и понять механизм, то вы заметите, что такой результат является ОШИБОЧНЫМ. И поэтому для правильной работы формулы вам необходимо ее подтвердить «Ctr+Shift+Enter». Как и все, понимание и использование формул массива требует практики. Тем не менее, стоит некоторое время посвятить тому, чтобы во всем разобраться. Потому что формулы массива позволяют решить многие проблемы, которые на первый взгляд могут казаться неразрешимыми.

Примеры вычислений и анализа формул массива

Каким образом можно просматривать и проверять значения промежуточных результатов расчета, например, содержание массивов, созданных в памяти компьютера и используемых для выполнения последующих действий? Ничего сложного! Пример 1:

Перейдите на ячейку с формулой, а затем в строке формул выделите в первом аргументе функции ссылку на диапазон ячеек:

Нажмите клавишу F9 (или «Пересчет» в правом верхнем углу меню «Формулы»), и вы получите (в строке формулы) значения аргументов, которые используются для вычислений, как показано ниже:

— запись с использованием двоеточий означает, что мы имеем дело с элементами вертикального (столбикового) массива, элементы горизонтального (строкового) разделены стандартным символом — «;» (точкой с запятой).

Пример 2: Снова перейдите на ячейку с формулой массива, но на этот раз выделите первый аргумент функции целиком вместе со знаком сравнения «>» и значением критерия – «0»).

Нажмите кнопку F9, и вы получите массив результатов вычислений, как показано ниже:

То есть, созданный в памяти компьютера массив:

{ИСТИНА:ЛОЖЬ:ИСТИНА:ИСТИНА}

Пример 3: Выделите ячейку формулой массива где в функцию СУММ вложена функция ЕСЛИ. Затем в строке формул выберите весь аргумент функции СУММ (вместе с функцией ЕСЛИ):

Нажмите клавишу F9 и вы получите массив итоговых результатов вычисления, которые используются для суммирования, как показано ниже:

То есть, созданный в памяти компьютера массив:

{23\0\15\7}

Пример 4: Просто перейдите на ячейку с формулой B1 и выберите инструмент «ФОРМУЛЫ»-«Зависимости формул»-«Вычислить формулу»

После чего нажмите на кнопку «Вычислить»:

В результате ссылка на диапазон ячеек в аргументе вложенной функции ЕСЛИ разложилась на массив значений. Снова нажмите на кнопку «Вычислить»:

Мы получили массив значений теперь уже для функции СУММ. Такой же, как и в примере 3.

Читайте также: Работа с массивами функций в Excel.

Часто неопытные пользователи Excel возмущаются, что формула не работает. В конце концов оказалось, как легко догадаться, формулу в массиве вводили как обычную (просто Enter). Речь идет не о недоразумении, которое произошло, а о том факте, что у этих пользователей возникает вопрос: как избегать таких ошибок? Поэтому важно сразу во всем разобраться, чтобы в дальнейшем больше не задавать таких вопросов.

Не работают формулы в Microsoft Excel? Читаем тему и решаем проблему :-): it_vlg — LiveJournal

Не работают формулы в Microsoft Excel? Читаем тему и решаем проблему :-): it_vlg — LiveJournal

?

chinchipos (chinchipos) wrote in it_vlg,

Category:

  • catIsShown({ humanName: ‘it’ })» data-human-name=»it»> IT
  • Cancel
  • Location: Russian Federation,
  • Mood: crazy

Сегодня столкнулся с ситуацией — у сотрудника (Сметчица) работает с ГРАНД-Сметой и excel. При протягивании не работали формулы в Microsoft Excel, т.е. было два столбца и надо было в третьем столбце вычислить разницу первых двух столбцов. В итоге делается формула и протягивается вниз до конца необходимой ячейки в таблице, но результата ни какого. Либо из первой ячейки копируется результат формулы и дублируется во все остальные, либо просто ни чего не происходит.

Что же делать в этом случае?

Вариант самый простой — вероятно у Вас отключен автоматический пересчет листа, для этого просто нажмите клавишу F9. Если данный вариант не Ваша тема переходим к следующему:

Вариант №2 чуть посложнее — убедитесь, что формат ячейки не выбран как текстовый (это может быть причиной что не работают вычисления и формулы), если Вы не можете определиться с форматом ячейки в Вашей таблице, спокойно выбирайте формат Общий для наглядного отображения смотрим картинку:

Вариант №3 если предыдущие варианты не спасли — убедитесь, что в настройках параметры вычисления формул выбран автоматически режим. Для этого нажмите на меню в программе Microsoft Excel: ФАЙЛ —> ПАРАМЕТРЫ —> ФОРМУЛЫ и в меню Параметры вычисления для наглядного отображения смотрим картинку:

Вот и все проблемы с вычислением формул в программе Microsoft Excel! Есть ещё один вариант если Вы пользуетесь 2007 Excel, но она решается на панели задачи (вверху основного экрана Excel), выбираем Формулы —> Параметры вычисления —> и ставим галку на автоматически всё.

Tags: 2007, 2010, 2013, 2016, excel 2003, microsoft, office, windows, ошибки, работа, таблицы, формулы

Subscribe

  • Удаление несуществующего экземпляра M$ Exchange.

    Приветствую всех читателей. В своё время, ради эксперимента, поднимал второй сервер Microsoft Exchange Server, но различные манипуляции с…

Photo

Hint http://pics.livejournal.com/igrick/pic/000r1edq

  • 0 comments
    • 0 comments

    Формула Excel не вычисляет — 5 причин, почему

    Главная/ Формулы/ 5 причин, почему ваша формула Excel не вычисляет

    Когда ваша формула Excel не вычисляет или не обновляет, это может быть очень неприятно. Ваши формулы являются движущей силой для вашей электронной таблицы.

    Есть 5 причин, по которым ваша формула Excel не вычисляет. В этом уроке мы объясним эти 5 сценариев.

    Смотреть видео — Формула Excel не вычисляет

    1. Для параметров расчета установлено значение «Вручную»

    Первое, что вы должны проверить, это то, что параметры расчета не установлены вручную. Это наиболее вероятная проблема.

    Щелкните вкладку Формулы , а затем кнопку Параметры расчета .

    сообщить об этом объявлении

    Если установлено ручное, формулы не будут обновляться, пока вы не нажмете кнопки Рассчитать сейчас или Рассчитать лист .
    Измените его на Автоматически , и формулы начнут работать.

    Этот параметр можно изменить с помощью макросов или других рабочих книг, которые вы могли открыть первыми. Поэтому, если вы не знаете об этом параметре, это все равно может быть причиной того, что формула не рассчитывается.

    2. Формула не вычисляется, так как ячейка отформатирована как текст

    Другой распространенной причиной является случайное форматирование ячеек, содержащих формулы, как текста. Они не будут рассчитываться в этом формате.

    Чтобы проверить это; щелкните ячейку и проверьте числовую группу из Главная вкладка.
    Если отображается Текст . Измените формат на Общий , используя предоставленный список.

    Затем пересчитайте формулу в ячейке, дважды щелкнув ячейку и нажав Enter.

    3. Перед равенством вводится пробел

    При вводе формулы убедитесь, что перед равенством не введен пробел. Это трудно заметить, поэтому он может остаться незамеченным, однако это помешает расчету формулы.

    Дважды щелкните ячейку или отредактируйте ее в строке формул. Проверьте, есть ли пробел, и если есть, удалите его. Формула обновится.

    4. Вводится апостроф Сохранение формулы в виде текста

    Когда перед вводом в Excel вводится апостроф (‘), это указывает Excel сохранить содержимое в виде текста. Это распространенный подход к хранению номеров, таких как номера телефонов, в виде текста, чтобы сохранить начальные нули.

    Однако это может быть причиной того, что ваша формула не работает.

    Апостроф не будет виден в ячейке электронной таблицы, но вы сможете увидеть его в строке формул.
    Дважды щелкните ячейку или отредактируйте ее в строке формул и удалите апостроф.

    5. Кнопка «Показать формулы» включена

    Последней причиной может быть то, что кнопка «Показать формулы» на вкладке «Формулы» включена. Это может быть легко сделано случайно или, возможно, кем-то другим, ранее использовавшим эту книгу.

    Эта кнопка используется при проверке формул. Он показывает формулу вместо результата формулы, мешая им вычислять. Это может быть полезно при устранении неполадок с формулой.

    Просто нажмите Показать кнопку Формулы еще раз, чтобы отключить ее, и формула будет работать.

    Другие потрясающие руководства по Excel

    • Использование подстановочных знаков в формулах Excel
    • Предотвращение отображения формул в строке формул
    • Устранение неполадок с формулами в Excel
    • Формула для извлечения почтового индекса из адреса в Великобритании
    • учебная платформа для вас?

    Взаимодействие с читателем

    6 Основные причины, по которым формулы Excel не работают (с решениями)

    1. Причина № 1 — Ячейки отформатированы как текст
    2. Причина № 2 — Случайно набраны клавиши CTRL + `
    3. Причина № 3 — Различаются значения и результат
    4. Причина № 4 — Не заключайте числа в двойной Цитаты
    5. Причина № 5. Проверка того, заключены ли формулы в двойные кавычки
    6. Причина № 6. Пробел перед формулой Excel
    Содержание
    • 6 Основные причины, по которым формула Excel не работает (с решением)
      • #1 ячейки, отформатированные в виде текста
        • Решение
      • #2 Случайно напечатали клавиши Ctrl + `
        • Решение
      • #3 ИЗОБРАЖЕНИЕ И РЕЗУЛЬТАТ. Не заключайте числа в двойные кавычки
      • #5 Проверяйте, заключены ли формулы в двойные кавычки
      • #6 Пробел перед формулой Excel
      • Рекомендуемые статьи