Значение ячейки Excel не меняется
Обновлено: 05.07.2024
При работе с электронными таблицами необходимо знать об относительных и абсолютных ссылках на ячейки.
Вот проблема: когда вы КОПИРУЕТЕ ФОРМУЛУ, содержащую ссылки на ячейки, что происходит со ссылками на ячейки?
Обычно ССЫЛКИ НА КЛЕТКИ ИЗМЕНЯЮТСЯ! Если скопировать формулу на 2 строки вправо, то ссылки на ячейки в формуле сместятся на 2 ячейки вправо. Если скопировать формулу на 3 строки вниз и на 1 строку влево, то ссылки на ячейки в формуле сместятся на 3 строки вниз и на 1 строку влево. Такие ссылки называются «относительными» ячейками, поскольку они изменяются относительно того места, куда вы копируете формулу.
Если вы не хотите, чтобы ссылки на ячейки менялись при копировании формулы, сделайте эти ссылки на ячейки абсолютными ссылками на ячейки. Поместите «$» перед буквой столбца, если вы хотите, чтобы она всегда оставалась неизменной. Поместите «$» перед номером строки, если вы хотите, чтобы он всегда оставался неизменным. Например, «$C$3» относится к ячейке C3, а «$C$3» будет работать точно так же, как «C3», если вы скопируете формулу. Примечание: при вводе формул вы можете использовать клавишу F4 сразу после ввода ссылки на ячейку для переключения между различными относительными/абсолютными версиями этого адреса ячейки.
Хитрость при создании электронных таблиц заключается в том, чтобы перед копированием формулы решить, какие ячейки в формуле должны быть относительными, а какие — абсолютными. Если некоторые ссылки на ячейки относятся к входным ячейкам в электронной таблице, вы обычно хотите, чтобы эти ячейки были абсолютными.
В статье ниже приведены дополнительные инструкции по абсолютным и относительным ссылкам на ячейки.
Карин Стилл
Excel использует два типа ссылок на ячейки для создания формул. У каждого своя цель. Читайте дальше, чтобы определить, какой тип ссылки на ячейку использовать для формулы.
Относительные ссылки на ячейки
Это наиболее широко используемый тип ссылки на ячейку в формулах. Относительные ссылки на ячейки — это базовые ссылки на ячейки, которые корректируются и изменяются при копировании или использовании автозаполнения.
=СУММ(B5:B8), как показано ниже, изменяется на =СУММ(C5:C8) при копировании в следующую ячейку.
Абсолютные ссылки на ячейки
Возникают ситуации, когда ссылка на ячейку должна оставаться неизменной при копировании или при использовании автозаполнения. Знаки доллара используются для сохранения постоянной ссылки на столбец и/или строку.
В приведенном ниже примере при расчете комиссионных для торгового персонала вы не хотите, чтобы ячейка B10 изменялась при копировании формулы вниз. Вы хотите, чтобы и столбец, и строка оставались одинаковыми, чтобы ссылаться на эту точную ячейку. При использовании в формуле $B$10 ни один из них не изменяется при копировании.
Более сложный пример:
Предположим, что вам нужно рассчитать цены товаров на складе с двумя разными ценовыми скидками. Взгляните на рабочий лист ниже.
Проверьте формулу в ячейке E4. Сделав ссылку на первую ячейку $C4, вы предотвратите изменение столбца при копировании, но позволите строке измениться при копировании вниз, чтобы приспособиться к снижению цен на различные товары. Сделав ссылку на последнюю ячейку A$12, вы предотвратите изменение номера строки при копировании вниз, но позволите столбцу измениться и отразить скидку B при копировании. Смущенный? Посмотрите на рисунок ниже и результаты ячеек.
Теперь вы можете подумать, а почему бы просто не использовать 10% и 15% в реальных формулах? Разве это не было бы проще? Да, если вы уверены, что процент скидки никогда не изменится, что крайне маловероятно. Скорее всего, в конечном итоге эти проценты необходимо будет скорректировать. Ссылаясь на ячейки, содержащие 10% и 15%, а не на фактические числа, когда процент изменяется, все, что вам нужно сделать, это изменить процент один раз в ячейке A12 и/или B12 вместо того, чтобы перестраивать все ваши формулы. Excel автоматически обновит цены со скидками, чтобы отразить изменение процента скидки.
Краткий обзор использования абсолютных ссылок на ячейки:
| $A1 | Разрешает изменение ссылки на строку, но не ссылка на столбец. |
| A$1 | Разрешает изменять ссылку на столбец, но не на строку. |
| $A$1 | Не позволяет изменять ни столбец, ни ссылку на строку. |
Существует ярлык для размещения абсолютных ссылок на ячейки в ваших формулах!
При вводе формулы после ввода ссылки на ячейку нажмите клавишу F4. Excel автоматически делает ссылку на ячейку абсолютной! Продолжая нажимать F4, Excel будет циклически перебирать все возможности абсолютной ссылки. Например, в первой формуле абсолютной ссылки на ячейку в этом руководстве, =B4*$B$10, я мог бы ввести =B4*B10, а затем нажать клавишу F4, чтобы изменить B10 на $B$10.Продолжая нажимать F4, вы получите 10 B$, затем B10 и, наконец, B10. Нажатие F4 изменяет только ссылку на ячейку непосредственно слева от точки вставки.
Я надеюсь, что этот учебник сделал эти типы ссылок на ячейки «абсолютно» понятными!
У меня есть формула в ячейке, которая подсчитывает значения в двух других ячейках. Обычно после ввода значений в эти две другие ячейки ячейка с формулой автоматически обновляется при нажатии Enter или при выходе из ячеек.
Однако по какой-то причине значение, рассчитанное по формуле, не обновляется автоматически. Мне нужно щелкнуть строку формул, а затем нажать Enter, чтобы ячейка с формулой сделала свое дело.
Почему это происходит?
8 ответов 8
Вероятная причина заключается в том, что для расчета выбран ручной режим. Чтобы изменить это на автоматический в различных версиях Excel:
2003: Инструменты > Параметры > Расчет > Расчет > Автоматически.
2007: кнопка "Office" > "Параметры Excel" > "Формулы" > "Вычисления в книге" > "Автоматически".
2010 и более поздние версии: "Файл" > "Параметры" > "Формулы" > "Вычисление в книге" > "Автоматически".
- 2008: Настройки Excel > Расчет > Автоматически
В некоторых случаях лучше оставить ручной режим, например, если нужно выполнить много сложных вычислений. В таких случаях вы можете просто нажать F9, когда хотите обновить расчеты.
@3bdalla: Возможно, вы выполнили макрос, содержащий строку Application.Calculation = xlCalculationManual ?
@Sablefoste: Нет, это не так. По крайней мере, не в Excel 2010 и более ранних версиях. 2013 год не проверял, но не вижу причин, по которым это могло бы измениться.
@RFB: Пожалуйста, не редактируйте другие возможные ответы в этот. Просто продолжайте и опубликуйте свой собственный ответ. На этот раз я сделал это для вас.
Подтвердить с помощью Excel 2007: кнопка Office > параметры Excel > формулы > расчет рабочей книги > автоматически.
Быстрая клавиша для обновления
Спасибо! У нас уже были автоматические параметры расчета, и мы не знали, почему значения не обновлялись. F9 и Shift+F9 не помогли и оставили значения прежними. Ctrl+F9 не только обновил значения, но и устранил проблему, чтобы она больше не повторялась.
Ctrl+F9: сворачивает окно книги в значок. Возможно, это изменилось в последних версиях Office, мне помогло следующее: Ctrl+Alt+F9: вычисляет все рабочие листы во всех открытых книгах, независимо от того, изменились ли они с момента последнего вычисления.
У меня только что был такой случай в Excel 2010: конкретная электронная таблица, которая не пересчитывалась автоматически. Я изменил настройку, как указано выше; но автоматический перерасчет по-прежнему не работал, и, перепроверив параметр «Расчет», обнаружил, что он сам сбросил себя обратно на «Вручную». Три попытки спустя, и он по-прежнему был непреклонен в том, что хочет быть «ручным» и никаким другим.
Скопируйте содержимое электронной таблицы в новую и удалите старую (к счастью, это была не особо важная таблица), и все было в порядке. Я могу только предположить, что каким-то образом файл был поврежден.
Веская причина хранить резервные копии.
Если эта рабочая книга была в формате .XLSM или .XLSB, в ней мог быть мошеннический макрос Worksheet_Change или аналогичный, который использовал Application.Calculation = xlCalculationManual и либо выдавал ошибку, либо просто не сбрасывал его на xlCalculationAutomatic .
Репост этого фрагмента, который пользователь RFB (неправомерно) пытался отредактировать в моем ответе:
Возможная причина заключается в том, что файл Office Prefs поврежден. В OSX это можно найти в:
Удалите этот файл и перезапустите ОС. Новый файл plist будет создан при перезапуске Office. Формулы снова идеально пересчитаны.
Похоже, что это "известная ошибка", существующая по крайней мере с 2012 года, судя по результатам Google: как только вы откроете один файл, в котором отключен флаг "автоматический расчет", все последующие рабочие книги, которые вы откроете, будут автоматически установите «автоматический расчет = выкл.». Значит отключение автоматического расчета есть. автоматически.
Также иногда случается так, что файл повреждается, и вы не можете повторно включить автоматический расчет.
Иногда также может быть макрос, "спрятанный" внутри рабочей книги, который программно отключает расчет в определенной ситуации, но вы никак не можете узнать об этом. пока вы не заметите, что результаты формул не меняются!
У меня также была эта проблема с очень большой электронной таблицей, которая просто перестала обновляться на выходных, но, проверив приведенные выше решения, настройка уже была установлена на автоматическую, а таблица слишком велика для перестройки, поэтому я был в затруднительном положении. потеря.
Мое решение состояло в том, чтобы найти старую версию той же таблицы Excel, в которой не было этих проблем до того, как я ее заархивировал, которая имеет такой же размер и сложность, что и текущая версия.
Я открыл эту таблицу, проверил, что она все еще работает и автоматически обновляется с текущей установкой MS Excel и любыми новыми автоматическими обновлениями Office (с которыми она работала), а затем просто снова открыл исходную таблицу. "Привет, вуаля!" снова заработало.
Итог: узнайте о различных режимах расчета в Excel и о том, что делать, если ваши формулы не выполняют расчет при редактировании зависимых ячеек.
Уровень навыков: начинающий
Посмотреть обучающее видео
Загрузить файл Excel
Вы можете использовать ту же книгу, что и я в видео. Я прикрепил его ниже:
График погашения кредита – Пример расчета вручную.xlsx
Почему мои формулы не работают?
Если вы когда-нибудь сталкивались с ситуацией, когда формулы в электронной таблице не выполняют автоматические вычисления должным образом, вы знаете, как это может быть неприятно.
Это случилось с моим другом Бреттом. Он говорил мне, что работает с файлом, и он не пересчитывает формулы, а вводит данные. Он обнаружил, что ему приходилось редактировать каждую ячейку и нажимать Enter, чтобы формула в ячейке обновлялась.
И это происходило только на его домашнем компьютере. Его рабочий компьютер работал нормально. Это сводило его с ума и отнимало много времени.
Наиболее вероятной причиной этой проблемы является режим параметров расчета, и это важный параметр, о котором должен знать каждый пользователь Excel.
Чтобы проверить, в каком режиме расчета находится Excel, перейдите на вкладку "Формулы" и нажмите "Параметры расчета". Это вызовет меню с тремя вариантами. Текущий режим будет отмечен галочкой. На изображении ниже видно, что Excel находится в режиме ручного расчета.
Когда Excel находится в режиме ручного расчета, формулы на вашем листе не будут рассчитываться автоматически. Вы можете быстро и легко устранить проблему, изменив режим на Автоматический. В некоторых случаях вам может понадобиться ручной режим расчета, и я объясню об этом ниже.
Настройки расчета сбивают с толку!
Очень важно знать, как может измениться режим расчета. Технически это настройка уровня приложения. Это означает, что этот параметр будет применяться ко всем книгам, открытым на вашем компьютере.
Как я упоминал в видео выше, это была проблема с моим другом Бреттом. Excel был в ручном режиме расчета на его домашнем компьютере, и его файлы не выполняли расчеты. Когда он открыл те же файлы на своем рабочем компьютере, расчеты выполнялись нормально, поскольку Excel на этом компьютере находился в режиме автоматических расчетов.
Однако здесь есть один существенный нюанс. Рабочая книга (файл Excel) также хранит последнюю сохраненную настройку расчета и может изменить/переопределить настройку на уровне приложения.
Это должно происходить только для первого файла, который вы открываете во время сеанса Excel.
Например, если перед сохранением и закрытием файла вы переключите Excel в ручной режим расчета, этот параметр будет сохранен вместе с книгой. Если вы затем откроете эту книгу как первую книгу в сеансе Excel, режим расчета будет изменен на ручной.
Все последующие рабочие книги, которые вы открываете во время этого сеанса, также будут находиться в ручном режиме расчета. Если вы сохраните и закроете эти файлы, режим ручного расчета также будет сохранен вместе с файлами.
Что сбивает с толку в этом поведении, так это то, что это происходит только с первым файлом, который вы открываете в сеансе. После того как вы полностью закроете приложение Excel, а затем снова откроете его, Excel вернется в режим автоматического расчета, если вы начнете с открытия нового пустого файла или любого файла, находящегося в режиме автоматического расчета.
Поэтому режим расчета первого файла, который вы открываете в сеансе Excel, определяет режим расчета для всех файлов, открытых в этом сеансе. Если вы измените режим расчета в одном файле, он будет изменен для всех открытых файлов.
Примечание. Я неправильно упомянул об этом в видео, когда сказал, что параметры расчета не переносятся вместе с рабочей книгой, и я обновлю видео.
3 варианта расчета
В Excel есть три варианта расчета.
Автоматический расчет означает, что Excel будет пересчитывать все зависимые формулы при изменении значения ячейки или формулы.
Ручное вычисление означает, что Excel будет пересчитывать только тогда, когда вы принуждаете его к этому. Это может быть нажатие кнопки или сочетание клавиш.Вы также можете пересчитать одну ячейку, отредактировав ячейку и нажав Enter.
Автоматически, кроме таблиц данных, означает, что Excel будет автоматически пересчитывать все ячейки, кроме тех, которые используются в таблицах данных. Это не относится к обычным таблицам Excel, с которыми вы можете часто работать. Это относится к инструменту анализа сценариев, которым пользуются немногие. Вы найдете его на вкладке «Данные» под кнопкой «Что, если сценарии». Поэтому, если вы не работаете с этими таблицами данных, маловероятно, что вы когда-либо намеренно измените настройку на этот параметр.
Помимо параметра "Расчет" на вкладке "Данные", его также можно найти в меню "Параметры Excel". Перейдите в «Файл», затем «Параметры», затем «Формулы», чтобы увидеть те же параметры настройки в окне «Параметры Excel».
В разделе "Вручную" вы увидите флажок для пересчета книги перед сохранением, который является настройкой по умолчанию. Это хорошо, потому что вы хотите, чтобы ваши данные были правильно рассчитаны, прежде чем вы сохраните файл и поделитесь им со своими коллегами.
Нажмите, чтобы увеличить
Зачем использовать режим ручного расчета?
Если вам интересно, почему кому-то может понадобиться изменить расчет с автоматического на ручной, есть одна важная причина. При работе с большими файлами, вычисление которых происходит медленно, постоянный пересчет всякий раз, когда вносятся изменения, иногда может замедлить работу вашей системы. Поэтому люди иногда переключаются в ручной режим при работе с изменениями на листах с большим количеством данных, а затем переключаются обратно.
Когда вы находитесь в режиме ручного расчета, вы можете принудительно выполнить расчет в любое время с помощью кнопки "Рассчитать сейчас" на вкладке "Формулы".
Сочетание клавиш для функции "Рассчитать сейчас" — F9, и она рассчитает всю книгу. Если вы хотите рассчитать только текущий рабочий лист, вы можете нажать кнопку под ним: Рассчитать лист. Сочетание клавиш для этого выбора — Shift + F9 .
Вот список всех сочетаний клавиш для пересчета:
| Ярлык | Описание |
| F9 | Пересчитать формулы, которые имеют измененные с момента последнего вычисления, и формулы, зависящие от них, во всех открытых книгах. Если рабочая книга настроена на автоматический пересчет, вам не нужно нажимать F9 для пересчета. |
| Shift+F9 | Пересчитать формулы, которые изменились с момента последнее вычисление и формулы, зависящие от них, на активном листе. |
| Ctrl+Alt+F9 | Пересчитать все формулы во всех открытых рабочих книгах, независимо от изменились ли они с момента последнего пересчета. |
| Ctrl+Shift+Alt+F9 | Проверить зависимые формулы, а затем пересчитать все формулы во всех открытых книгах , независимо от того, изменились ли они с момента последнего пересчета. |
Макрос переходит в режим ручного расчета
Если вы обнаружите, что ваша книга не выполняет автоматические вычисления, но вы не изменили режим намеренно, другой причиной, по которой он мог измениться, является макрос.
Теперь я хочу предварить это, сказав, что проблема вызвана НЕ всеми макросами. Это определенная строка кода, которую разработчик может использовать для ускорения работы макроса.
Следующая строка кода VBA указывает Excel перейти в режим ручного расчета.
Иногда автор макроса добавляет эту строку в начало, чтобы Excel не пытался выполнять вычисления во время выполнения макроса. Затем параметр следует изменить обратно в конце макроса со следующей строкой.
Этот метод может хорошо работать для больших книг, вычисления в которых выполняются медленно.
Однако проблема возникает, когда макрос не завершается — возможно, из-за ошибки, сбоя программы или неожиданной системной проблемы. Макрос изменяет настройку на Вручную, и она не возвращается обратно.
Как я упоминаю в видео, это именно то, что случилось с моим другом Бреттом, и он НЕ знал об этом. Он остался в ручном режиме расчета и не знал, почему и как снова заставить Excel выполнять вычисления.
Поэтому, если вы используете этот метод со своими макросами, я рекомендую вам подумать о том, как смягчить эту проблему. А также предупредите пользователей о том, что Excel может остаться в ручном режиме расчета.
Я также рекомендую НЕ изменять свойство Calculation с помощью кода, если в этом нет крайней необходимости. Это поможет предотвратить разочарование и ошибки пользователей ваших макросов.
Заключение
Я надеюсь, что эта информация окажется для вас полезной, особенно если вы в настоящее время занимаетесь этой конкретной проблемой. Если у вас есть какие-либо вопросы или комментарии по поводу режимов расчета, поделитесь ими в комментариях.
Сводка:
В этой статье содержится полная информация о том, как исправить формулы Excel, которые не обновляются или не вычисляются автоматически, а также о причинах, по которым формулы не обновляются автоматически в Excel.
цитата>Microsoft Excel — одна из самых полезных программ в современных вычислениях, но что, если Excel без формул или изображений с использованием Microsoft Excel без формул.
Ну, я думаю, никто не может, не так ли….
MS Excel — это все о формулах, и миллионы людей используют электронные таблицы Excel для выполнения множества задач, от ведения записей до расширенного анализа данных с использованием сложных формул.
Итак, формулы Excel, безусловно, очень важны в Microsoft Excel. Но бывают случаи, когда формулы Excel внезапно перестают работать и вызывают различные проблемы, связанные с формулами.
И формула Excel не обновляется или формула Excel не вычисляет, является одним из них. Если вы столкнулись с той же проблемой, то это руководство для вас.
Просто внимательно прочитайте статью и следуйте приведенным исправлениям одно за другим, чтобы исправить проблемы с обновлением формул Excel.
Почему формулы Excel не обновляются автоматически?
Здесь ознакомьтесь с возможными причинами того, что формулы Excel не обновляются автоматически:
- Ваш расчет настроен на «Вручную».
- Кнопка "Показать формулы" включена.
- Ячейка отформатирована как текст
- Перед знаком равенства вы ввели пробел.
Итак, это распространенные причины, по которым формулы Excel не обновляются, теперь попробуйте указанные исправления.
Как исправить проблему с обновлением формул Excel?
Решение 1. Для расчета выбран «Вручную»
Это одна из наиболее распространенных причин, по которой формулы Excel не обновляются автоматически.
Итак, проверьте, настроен ли параметр расчета «вручную», а затем установите значение «Автоматически», чтобы устранить проблему.
< /p>Ну, шаги могут отличаться в зависимости от вашей версии Excel, поэтому здесь следуйте инструкциям для разных версий Excel.
- Excel 2003 — нажмите "Сервис" > "Параметры" > "Расчет" > "Расчет" > "Автоматически".
- В Excel 2007: нажмите кнопку Office > Параметры Excel > Формулы > Расчет рабочей книги > Автоматически.
- Excel 2010, 2013 и 2016: перейдите в меню «Файл» > «Параметры» > «Формулы» > раздел «Параметры расчета» > в разделе «Расчет рабочей книги» > выберите «Автоматически».
Теперь проверьте, решена ли проблема с автоматическим обновлением формул.
Решение 2. Кнопка «Показать формулы» включена
Если приведенное выше решение вам не подходит, проверьте, включена ли кнопка "Показать формулы" на вкладке "Формулы".
Тогда также есть вероятность, что формулы не работают или не обновляются. Ну, эта кнопка в основном используется при аудите формул, она отображает формулы вместо конечного результата.
Однако эта вкладка полезна, если формула работает неправильно.
И чтобы изменить его, нажмите кнопку "Показать формулы" и "Отключить".
< /p>Проверьте, разрешены ли формулы, которые не обновляются в Excel.
Решение 3. Ячейка отформатирована как текст
Если вы случайно отформатировали ячейки, включая формулы, как текст, то проблема именно в этом. Так как ячейка имеет текстовый формат, ячейка не будет автоматически вычислять формулы.
< /p>Итак, щелкните ячейку и на вкладке «Главная» проверьте группу «Число»>, если она отображает текст>, затем измените ее на «Общие».
< /p>И теперь вы можете пересчитать формулу в ячейке, дважды щелкнув ячейку > нажмите Enter.
Решение 4. Проверка циклических ссылок
Проверьте циклическую ссылку, так как это может быть еще одной причиной, из-за которой формулы Excel не обновляются.
Циклическая ссылка — это формула, которая включает себя в расчет или ссылается на другую ячейку, которая зависит от самой себя.
В некоторых случаях это не позволяет Excel вычислить формулу. Поэтому проверьте его, исправьте циклическую ссылку и пересчитайте таблицу.
Решение 5. Введен пробел перед знаком равенства
Это последнее решение, из-за которого формулы Excel не обновляются.
Ну, если при вводе формулы вы по ошибке введете пробел перед «Равно (=)», то формула не будет вычисляться.
Эта ошибка довольно коварна, ее трудно заметить, и она остается невыявленной.
< /p>Дважды щелкните ячейку и проверьте, есть ли пробел > удалите ее, и формула обновится.
Вот и все, данные советы по формулам вам помогут, и теперь проблема решена.
Вывод:
Итак, все дело в том, что формулы не обновляются автоматически.
Предполагается, что после выполнения вышеупомянутых решений вы сможете исправить формулу, которая не обновляется в проблеме с Excel.
Это распространенная проблема, с которой однажды сталкивались обычные пользователи Excel, поэтому теперь вы можете легко решить ее.
Если у вас есть какие-либо дополнительные вопросы, запросы или исправления, касающиеся представленных, посетите наш раздел блога «Восстановление MS Excel».
Маргрет
Маргрет Артур – предприниматель и эксперт по контент-маркетингу. Она ведет технические блоги и делится знаниями по MS Office, Excel и другим техническим дисциплинам. Ее самобытное искусство представления технической информации простым для понимания языком очень впечатляет. Когда она не пишет, она любит незапланированные путешествия.
Читайте также:
- Как удалить удаленный доступ к компьютеру
- Как скопировать в фотошопе
- Как удалить номер страницы с последней страницы в Word
- Как убрать флажки в Word
- Как программы внедрялись в компьютеры первого поколения с магнитных дисков
