Svinkovod.ru

Бытовая техника
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Пересчет в Excel

Пересчет в Excel

Пользователь может вызывать пересчет в Microsoft Excel несколькими способами, например:

путем ввода новых данных (если Excel находится в режиме автоматического пересчета, описанном далее в этой статье);

явным указанием Excel пересчитать всю книгу или ее часть;

путем удаления или вставки строки или столбца;

путем сохранения книги при заданном параметре Пересчет перед сохранением;

путем выполнения некоторых действий автофильтра;

двойным щелчком по разделителю строк или столбцов (в режиме автоматического вычисления);

путем добавления, редактирования или удаления заданного имени;

путем переименования листа;

путем изменения позиции листа относительно других листов;

путем скрытия или отображения строк (не столбцов).

В этой статье не делается различий между непосредственным нажатием клавиши или кнопки мыши пользователем и выполнением этих задач командой или макросом. Пользователь запускает команду или делает что-либо, чтобы она запустилась, поэтому это также считается действием пользователя. Таким образом, слово "пользователь" также означает "пользователь либо команда или процесс, запущенные пользователем".

Зависимость, "грязные" ячейки и пересчитанные ячейки

Вычисление листов в Excel можно рассматривать как процесс из трех этапов:

Создание дерева зависимостей

Создание цепочки вычислений

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

При структурном изменении книги, например при вводе новой формулы, Excel заново создает дерево зависимостей и цепочку вычислений. При вводе новых данных или новых формул Excel помечает все ячейки, которые зависят от новых данных, как требующие пересчета. Помеченные таким образом ячейки называются "грязными". Все прямые и косвенные зависимые ячейки помечаются как "грязные", поэтому если ячейка B1 зависит от ячейки A1, а ячейка C1 — от B1, то при изменении ячейки A1 ячейки B1 и C1 помечаются как "грязные".

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

Отметив ячейки как "грязные" при следующем пересчете, Excel повторно оценивает содержимое каждой "грязной" ячейки в порядке, определяемом цепочкой вычислений. В приведенном выше примере это означает, что сначала оценивается ячейка B1, а затем — C1. Пересчет происходит сразу после того, как Excel закончит отмечать ячейки как "грязные", если выбран автоматический режим пересчета. В противном случае это происходит позже.

Начиная с Microsoft Excel 2002, объект Range в Microsoft Visual Basic для приложений (VBA) поддерживает метод Range.Dirty, который отмечает ячейки как требующие подсчета. Когда он используется совместно с методом Range.Calculate (см. следующий раздел), он включает принудительный пересчет ячеек в заданном диапазоне. Это удобно при выполнении ограниченного вычисления в макросе, где установлен ручной режим подсчета (для избежания избытка вычисляемых ячеек, не относящихся к функции макроса). Методы подсчета диапазонов недоступны через API C.

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

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

Асинхронные пользовательские функции

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

Переменные и постоянные функции

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

Читайте так же:
Как в фотошопе высветлить картинку

Переменными являются следующие функции Excel:

NOW

TODAY

RANDBETWEEN

OFFSET

INDIRECT

INFO (в зависимости от аргументов)

CELL (в зависимости от аргументов)

SUMIF (в зависимости от аргументов)

Интерфейсы API VBA и C поддерживают способы сообщить Excel, что пользовательскую функцию следует обрабатывать как переменную. В VBA пользовательская функция объявляется переменной следующим образом.

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

С помощью API C можно зарегистрировать функцию XLL как переменную до ее первого вызова. Он также позволяет включать и отключать переменное состояние функции листа.

По умолчанию Excel обрабатывает пользовательские функции XLL, которые принимают диапазоны в качестве аргументов и объявлены как эквиваленты листа макросов (изменчивые). Вы можете отключить это состояние по умолчанию с помощью функции xlfVolatile при первом вызове пользовательской функции.

Режимы вычисления, команды, выборочный пересчет и таблицы данных

В Excel есть три режима вычисления:

Автоматический, кроме таблиц

В автоматическом режиме вычисления пересчет происходит только после каждого ввода данных и после определенных событий, таких как примеры в предыдущем разделе. В очень больших книгах пересчет может занимать так много времени, что пользователям необходимо ограничивать эти условия, чтобы пересчет происходил только при необходимости. Для этого Excel поддерживает ручной режим. Пользователь может выбрать режим в системе меню Excel или программным способом с помощью API VBA, COM или C.

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

Пересчет таблиц данных обрабатывается немного по-другому:

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

Циклические ссылки допускаются. Если вычисление, используемое для получения результата, зависит от одного или нескольких значений из таблицы данных, то Excel не возвращает ошибку циклической зависимости.

Таблицы данных не используют многопоточные вычисления.

Учитывая, что Excel по-другому обрабатывает пересчет таблиц данных, а вычисление больших таблиц, зависящих от сложных или длинных вычислений, может занимать много времени, Excel позволяет отключить автоматическое вычисление таблиц данных. Для этого выберите режим вычисления "Автоматический, кроме таблиц". В этом режиме пользователь может пересчитывать данные, нажав клавишу F9 или выполнив эквивалентную программную операцию.

Excel предоставляет методы, с помощью которых можно изменять режим пересчета и управлять им. Эти методы улучшались от версии к версии, чтобы обеспечить возможность более точного управления. Возможности API C в этом отношении отражают возможности, доступные в Excel версии 5, поэтому не предоставляют такого управления, как при использовании VBA в более поздних версиях.

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

Вычисление диапазонов

VBA: Range.Calculate (представлен в Excel 2000, изменен в Excel 2007) и Range.CalculateRowMajorOrder (представлен в Excel 2007)

API C: не поддерживается

Ручной режим

Пересчитывает только ячейки в заданном диапазоне независимо от того, "грязные" ли они. Поведение метода Range.Calculate изменилось в Excel 2007. Но предыдущее поведение по-прежнему поддерживается методом Range.CalculateRowMajorOrder.

Режим "Автоматически" или "Автоматически, кроме таблиц"

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

Активное вычисление листов

VBA: ActiveSheet.Calculate

API C: xlcCalculateDocument

Все режимы

Пересчитывает ячейки, отмеченные для вычисления, только на активном листе.

Вычисление указанных листов

VBA: Worksheets( reference ).Calculate

API C: не поддерживается

Все режимы

Пересчитывает "грязные" ячейки и их зависимости только на указанном листе. Ссылка — это имя листа как строка или номер индекса в соответствующей книге.

Excel 2000 и более поздних версий предоставляет свойство листа Boolean (EnableCalculation). Если задать для него значение True вместо False, все ячейки на указанном листе будут помечены как "грязные". В автоматических режимах это вызывает пересчет всей книги.

Читайте так же:
Макрос удаления строк по условию в excel

В ручном режиме следующий код вызывает пересчет только активного листа.

Повторное создание и принудительный пересчет дерева книги

Клавиши: CTRL+ALT+SHIFT+F9 (появились в Excel 2002)

VBA: Workbooks( reference ).ForceFullCalculation (представлен в Excel 2007)

API C: не поддерживается

Все режимы

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

Все открытые книги

VBA: Application.Calculate

API C: xlcCalculateNow

Все режимы

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

Повторное создание и принудительное вычисление дерева всех открытых книг

VBA: Application.CalculateFull

API C: не поддерживается

Все режимы

Пересчитывает все ячейки во всех открытых книгах. Если выбран режим вычисления "Автоматический, кроме таблиц", выполняется принудительный пересчет таблиц.

Как исправить ошибки в Excel

Вопросы про ошибки в Эксель – самые распространенные, я их получаю каждый день, ими наполнены тематические форумы и сервисы ответов. Очень легко допустить ошибку в формуле Excel, особенно когда работаешь быстро и с большим количеством данных. Результат – неправильные расчеты, недовольные руководители, убытки… Как же найти ошибку, которая закралась в Ваших расчетах? Давайте разбираться. Единого инструмента или алгоритма поиска ошибок нет, поэтому будем двигаться от простого к сложному.

Если в ячейке формула вместо результата

Если Вы написали формулу в ячейке, нажали Enter, а вместо результата в ней отображается сама формула – значит выбран текстовый формат значения для данной ячейки. Как исправить? Сначала изучите, какие есть форматы данных, и выберите тот, который нужен Вам, но не текстовый, в этом формате вычисления не производятся.

Теперь на ленте найдите Главная – Число , и в раскрывающемся списке выберите подходящий формат данных. Сделайте это для всех ячеек, в которых формула стала обычным текстом.

Если формулы на листе не пересчитываются

Если Вы изменяете исходные данные, а формулы на листе никак не хотят пересчитываться – у Вас отключен автоматический пересчет формул.

Чтобы это исправить – нажмите на ленте: Формулы – Вычисления – Параметры вычислений – Автоматически . Теперь все будет пересчитываться, как обычно.

Помните, автоматический пересчет мог быть отключен целенаправленно. Если у Вас на листе огромное количество формул – каждое внесение изменений заставляет их пересчитываться. В итоге, работа с документом перерастает в хроническое состояние ожидания после каждого изменения. В таком случае, нужно перевести пересчет в ручной режим: Формулы – Вычисления – Параметры вычислений – Вручную . Теперь вносите все изменения в исходные данные, программа будет ждать. Когда все изменения внесены – жмите F9 , все формулы обновят значения. Или cнова включите автоматический пересчет.

Если ячейка заполнена знаком решетки

Еще одна классическая ситуация, когда в результате расчетов Вы получаете не результат, а ячейку, заполненную знаками решетки:

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

Результат вычисления – не в том формате

Иногда случается, что после выполнение вычислений, результат предстает не в том виде, который Вы ожидаете. Например, сложили два числа, а в результате получили дату. Почему так происходит? Вероятно, формат данных в ячейке ранее был установлен «дата». Просто измените его на нужный, и все будет по-Вашему.

Когда недоступны внешние ссылки

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

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

Если формула Excel возвращает неправильный результат

Если Вы написали формулу, а она возвращает очевидно неправильный результат – разбираемся с логикой формулы. Вы могли банально ошибиться в скобках, порядке следования операторов. Изучите правила написания формул и проверьте все ли верно в Вашей. Если верно – переходим к следующему шагу.

Если Вы используете функции – убедитесь, что Вы знаете правила применения функций. Каждая из них имеет свой синтаксис. Проверьте, правильно ли Вы задали параметры для формулы, для чего прочтите справку по ней. Нажмите F1 и в окне «Справка Excel» в поиске напишите Вашу функцию, например, «ВПР». Программа отобразит список доступных материалов по этой функции. Как правило, их хватает, чтобы получить полное представление о работе Вашей функции. Авторы справки очень доступно излагают материал, приводят примеры использования.

Часто пользователи неверно указывают ссылки на ячейки в формулах, от того и получают ошибочные результаты. Первое, что нужно сделать для проверки внешних формул – включить отображение формул в ячейках. Для этого выполните на ленте Формулы – Зависимости формул – Показать формулы . Теперь в ячейках будут отображаться формулы, а не результаты расчетов. Можно пробежаться глазами по листу и проверить правильные ли ссылки указаны. Чтобы снова показать результаты – еще раз выполните ту же команду.

Читайте так же:
Модульная сетка в фотошопе как включить

Чтобы упростить процесс – можно включить стрелки ссылок. Можно легко определить на какие ячейки ссылается формула, нажав Формулы – Зависимости формул – Влияющие ячейки . На листе синими стрелками будет указано, на какие данные Вы ссылаетесь.

Аналогично, можно увидеть ячейки, формулы в которых ссылаются на заданную клетку. Для этого выполняем: Формулы – Зависимости формул – Зависимые ячейки .

Учтите, что в сложных таблицах отрисовка стрелок займет много времени и машинных ресурсов. Чтобы убрать стрелки – кликните Формулы – Зависимости формул – Убрать стрелки .

Как правило, внимательная проверка формул с перечисленными выше инструментами решает проблемы ошибочного результата. Ищем проблему до победы!

Если возникает циклическая ссылка в Эксель

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

Часто, ячейки ссылаются друг на друга косвенно, т.е. не напрямую, а через промежуточные формулы.

Чтобы найти такие «неправильные» формулы, найдите на ленте: Формулы – Зависимости формул – Проверка наличия ошибок – Циклические ошибки . Это меню открывает список ячеек с «зацикленными» формулами. Кликните по любой, чтобы установить в нее курсор и проверить формулу.

Естественно, циклические ссылки устраняются путем проверки и исправления логики вычислений. Однако, в некоторых случаях, циклическая ссылка не будет ошибкой. То есть, этой системе формул все же нужно дать просчитаться до состояния, близкого к равновесию, когда изменения практически не происходят. Некоторые инженерные задачи требуют этого. К счастью, Excel это допускает. Называется такой подход «итеративные вычисления». Чтобы их включить, нажмите Файл – Параметры – Формулы ,и установите галку «Итеративные вычисления». Там же установите:

  • Предельное число итерации – максимальное количество итераций (циклов), которое будет проведено до полной остановки
  • Относительная погрешность – минимальное изменение целевых значений за одну итерацию, при которых пересчет будет остановлен.

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

Встроенные ошибки Excel

Иногда при вычислениях выпадают ошибки, начинающиеся со знака «#». Например, «#Н/Д», «#ЧИСЛО!», и т.д. эти ошибки я описывал ранее, прочтите этот пост и постарайтесь осмыслить причину появления Вашей ошибки. Когда это произойдет, Вы легко все исправите.

Если же Вам не удается найти ошибку в достаточно сложной формуле – кликните на восклицательный знак возле ячейки и в контекстном меню выберите «Показать этапы вычисления».

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

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

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

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

Решение 1: меняем формат ячеек

Очень часто Excel отказывается выполнять расчеты из-за того, что неправильно выбран формат ячеек.

Например, если задан текстовый формат, то вместо результата мы будем видеть просто саму формулу в виде обычного текста.

Формулы в ячейках Эксель в текстовом формате

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

Формулы в Эксель в формате даты

Очевидно, что формат ячеек нужно изменить, и делается это следующим образом:

  1. Чтобы определить текущий формат ячейки (диапазон ячеек), выделяем ее и, находясь во вкладке “Главная”, обращаем вниманием на группу инструментов “Число”. Здесь есть специальное поле, в котором показывается формат, используемый сейчас.Формулы в ячейках Excel в текстовом формате
  2. Выбрать другой формат можно из списка, который откроется после того, как мы кликнем по стрелку вниз рядом с текущим значением.Выбор формата для выделенных ячеек в Эксель

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

  1. Выбрав ячейку (или выделив диапазон ячеек) щелкаем по ней правой кнопкой мыши и в открывшемся списке жмем по команде “Формат ячеек”. Или вместо этого, после выделения жмем сочетание Ctrl+1.Переход в окно форматирования ячеек через контекстное меню в Excel
  2. В открывшемся окне мы окажемся во вкладке “Число”. Здесь в перечне слева представлены все доступные форматы, которые мы можем выбрать. С левой стороны отображаются настройки выбранного варианта, которые мы можем изменить на свое усмотрение. По готовности жмем OK.Выбор формата в окне форматирования ячеек в Эксель
  3. Чтобы изменения отразились в таблице, по очереди активируем режим редактирования для всех ячеек, в которых формула не работала. Выбрав нужный элемент перейти к редактированию можно нажатием клавиши F2, двойным кликом по нему или щелчком внутри строки формул. После этого, ничего не меняя, жмем Enter.Редактирование формулы в Эксель

Примечание: Если данных слишком много, на ручное выполнение последнего шага потребуется немало времени. В данном случае можно поступить иначе – воспользуемся Маркером заполнения. Но этот работает только в том случае, когда во всех ячейках используются одинаковая формула.

  1. Выполняем последний шаг только для самой верхней ячейки. Затем наводим указатель мыши на ее правый нижний угол, как только появится черный плюсик, зажав левую кнопку мыши тянем его до конца таблицы.Копирование формулы с помощью маркера заполнения в Excel
  2. Получаем столбец с результатами, посчитанными с помощью формул.Выделенный столбец с формулами в Эксель
Читайте так же:
Как вернуть старый дизайн яндекс браузера

Решение 2: отключаем режим “Показать формулы”

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

  1. Переключаемся во вкладку “Формулы”. В группе инструментов “Зависимость формул” щелкаем по кнопке “Показать формулы”, если она активна.Включение и отключение показа формул в таблице Эксель
  2. В результате, в ячейках с формулами теперь будут отображаться результаты вычислений. Правда, из-за этого могут измениться границы столбцов, но это поправимо.Результаты в ячейках с формулами в Эксель

Решение 3: активируем автоматический пересчет формул

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

  1. Заходим в меню “Файл”.Переход в меню Файл в Эксель
  2. В перечне слева выбираем раздел “Параметры”.Переход в Параметры Excel
  3. В появившемся окне переключаемся в подраздел “Формулы”. В правой части окна в группе “Параметры вычислений” ставим отметку напротив опции “автоматически”, если выбран другой вариант. По готовности щелкаем OK.Включение автоматического вычисления формул в Эксель
  4. Все готово, с этого момента все результаты по формулам будут пересчитываться в автоматическом режиме.

Решение 4: исправляем ошибки в формуле

Если в формуле допустить ошибки, программа может воспринимать ее как простое текстовое значение, следовательно, расчеты по ней выполнятся не будут. Например, одной из самых популярных ошибок является пробел, установленный перед знаком “равно”. При этом помним, что знак “=” обязательно должен стоять перед любой формулой.

Ненужный пробел перед формулой в Эксель

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

Вставка функции в ячейку Excel

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

Редактирование формулы в Эксель

Иногда проще удалить формулу и написать ее заново, чем пытаться искать ошибку в уже написанной. То же самое касается функций и их аргументов.

Распространенные ошибки

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

  • #ДЕЛ/0! – результат деления на ноль;
  • #Н/Д – ввод недопустимых значений;
  • #ЧИСЛО! – неверное числовое значение;
  • #ЗНАЧ! – используется неправильный вид аргумента в функции;
  • #ПУСТО! – неверно указан адрес дапазона;
  • #ССЫЛКА! – ячейка, на которую ссылалась формула, удалена;
  • #ИМЯ? – некорректное имя в формуле.

Если мы видим одну из вышеперечисленных ошибок, в первую очередь проверяем, все ли данные в ячейках, участвующих в формуле, заполнены корректно. Затем проверяем саму формулу и наличие в ней ошибок, в том числе тех, которые противоречат законам математики. Например, не допускается деление на ноль (ошибка #ДЕЛ/0!).

Ошибка деления на ноль в Экселе

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

  1. Отмечаем ячейку, содержащую ошибку. Во вкладке “Формулы” в группе инструментов “Зависимости формул” жмем кнопку “Вычислить формулу”.Функция вычисления формулы в Эксель
  2. В открывшемся окне будет отображаться пошаговая информация по расчету. Для этого нажимаем кнопку “Вычислить” (каждое нажатие осуществляет переход к следующему шагу).Окно вычисления формулы в Excel
  3. Таким образом, можно отследить каждый шаг, найти ошибку и устранить ее.

Также можно воспользоваться полезным инструментом “Проверка ошибок”, который расположен в том же блоке.

Функция проверки ошибок в формуле в Эксель

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

Окно проверки ошибок в Excel

Заключение

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

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

Насколько я знаю, Worksheet_Calculate вызывается, когда значение любой ячейки на листе изменяется при пересчете Формулы.

Читайте так же:
Как восстановить закрепленные вкладки в гугл хром

Есть ли способ, чтобы мне нужно было вызывать функцию только тогда, когда конкретная ячейка изменяется при пересчете Формулы

2 ответа

  • Автоматическое обновление ячейки в Excel с текущей датой в любое время, когда любая ячейка в столбце обновляется или изменяется

Не уверен, что это возможно в Excel, но я бы хотел, чтобы ячейка K9 обновлялась с сегодняшней датой каждый раз, когда изменяется ячейка ANY в K11:K119 . Некоторые ячейки в K11:K119 пусты, но в них могут быть введены данные в любое время. Ячейки в K11:K119 , которые уже имеют данные, могут.

Я далек от того, чтобы быть великим в macros в Excel и действительно мог бы использовать некоторые рекомендации. Нужно запустить простой макрос, который выглядит так: Public Sub CopyReplaceValue(rng1 As String) Dim str1 As String Dim str2 As String Dim str3 As String Dim rng2 As Integer On Error.

Чтобы что-то произошло при изменении конкретной ячейки, вам необходимо встроить соответствующее событие изменения выбора в файл Worksheet_Change(byval target as Range) . Мы можем пересчитать рабочий лист, когда ваша ячейка изменится следующим образом:

Теперь то, что вы хотите сделать, это отключить вычисления rest времени. Если вы хотите отключить вычисления только на одном листе (а не на всем файле), вам нужно будет отключить вычисления при его активации и включить при деактивации, например

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

Тогда у вас может быть несколько ячеек, которые могут потребовать каких-то вычислений. Предположительно, причина, по которой вы хотите отключить вычисления, заключается в том, что файл работает слишком медленно. Если это так, и вы идентифицируете все входные ячейки, вы можете ввести выходные данные с помощью кода. Одним из способов было бы ввести формулы, используя это руководство по вводу формул в Excel Visual Basic . Затем вы можете заменить формулу вычисленным значением, например Range(«YourCell») = Range(«YourCell»).Value . таким образом, количество формул на листе постоянно растет

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

Ответ — Да. Чтобы немного подправить код Эда.

Я думаю, что ваше использование «function» сбивает людей с толку. Вот почему ответ Эда так сложен.

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

Похожие вопросы:

В Excel 2011 Mac (это кажется важным, так как другие ссылочные решения в stackoverflow для excel windows или Mac более старых версий, похоже, не работают). Я хочу применить условное форматирование.

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

Я хотел бы создать макрос на Excel, чтобы выделить диапазон ячеек при изменении даты в конкретной ячейке. Ячейка извлекает информацию о дате из Bloomberg (формула BDP), и книга обновляется.

Не уверен, что это возможно в Excel, но я бы хотел, чтобы ячейка K9 обновлялась с сегодняшней датой каждый раз, когда изменяется ячейка ANY в K11:K119 . Некоторые ячейки в K11:K119 пусты, но в них.

Я далек от того, чтобы быть великим в macros в Excel и действительно мог бы использовать некоторые рекомендации. Нужно запустить простой макрос, который выглядит так: Public Sub.

Мне нужен способ timestamp соседней ячейки, значение которой изменяется по формуле. Используя это в качестве примера, мне нужна ячейка, смежная с A1 на листе 1, чтобы timestamp дата и время.

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

Private Sub Worksheet_Activate() If Range(K59).value = 0 Then Columns(K).EntireColumn.Hidden = True Else Columns(K).EntireColumn.Hidden = False End If End Sub — Сэр, у меня есть следующий код, где.

Я следовал этому руководству: https://www.exceltrick.com/how_to/sum-cells-based-on-background-color/ ? Это работает, но когда я меняю цвет ячейки, формулы не вычисляются автоматически. Это.

У меня есть таблица pivot на excel, которая автоматически обновляется значениями каждый день. У меня есть столбцы рядом с таблицей pivot для каждого дня, которые захватывают данные из обновленной.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector