Почему формула не копируется в Excel при перетаскивании

Обновлено: 21.11.2024

Мои формулы работали нормально до сегодняшнего дня. Я думаю,
какая-то настройка где-то в Excel вышла из строя, но я
не знаю, что и как.

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

Реклама

Норман Харкер

Что такое формула?

Похоже на проблему абсолютной/относительной ссылки. См.:

Арви Лаанемец


Или установлен ручной расчет (Инструменты.Параметры.Расчет). Вы можете легко
проверить это, нажав F9

Гость

Нет, проблема вовсе не в этом.

Вот пример!
Дебет Кредит Баланс
307,28$ 307,28$
514,81$822,09$
13,38$835,47
16,14$ 851,61$
114,22$ 965,83$
4,41$ 970,24$
40,18$ 1,
44,18$ 1,
16,14$ 851,61$ br />4,93 доллара 1 019,35 доллара
228,80 доллара 1 248,15 доллара
17,04 доллара 1 265,19 доллара

Теперь это результат, которого я хочу! Таким образом, это
нарастающая сумма столбца дебетов за вычетом столбца
кредитов. Если мы назовем столбец "Дебет" A, столбец "Кредит" B и
столбец баланса C, и я попытаюсь получить результат второй строки
со значением $822,09, я подставлю в формулу
=C1+ А2-В2. Затем я перетащу формулу вниз, чтобы она
обновила остальную часть столбца C.

У меня это всегда работало до сегодняшнего дня. Если вы
посмотрите на формулы, которые он помещает в оставшиеся поля в
столбце C, они кажутся правильными (т. е. следующая строка
будет =C2+A3-B3, а затем следующий =C3+A4-B4 и т. д.,
однако он продолжает выдавать результат $822,09 в каждой
строке независимо от формулы. Это очень необычно!

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

В этом руководстве мы объясним, как скопировать формулу в Excel тремя способами. Мы также объясним, как переключаться между относительными и абсолютными ссылками на формулы, и ответим на распространенные вопросы, связанные с этой темой.

Как скопировать формулу вниз в Excel путем перетаскивания

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

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

Как скопировать формулу вниз в Excel без перетаскивания

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

Если вам нужно быстро скопировать формулу во весь столбец, вы можете использовать сочетание клавиш «Ctrl + D». Следуйте инструкциям ниже:

Ярлыки также позволяют копировать формулу во всю строку. Вот как это сделать:

  1. Выберите ячейку с формулой.
  2. Выберите номер строки слева и выделите всю строку.
  3. Нажмите одновременно клавиши «Ctrl» и «R».

    4. Как скопировать формулу вниз в Excel без изменения формулы

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

    В качестве примера возьмем простую формулу =СУММ(B1,C1). Если вы скопируете его вниз, следующие ячейки будут содержать формулы =СУММ(B2,C2) , =СУММ(B3,C3) и =СУММ(B4,C4) .

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

    Предположим, вы хотите просуммировать ячейку B1 с каждым значением в столбце C. В этом случае вы должны добавить знак доллара ($) перед индексом столбца и строки. Формула должна выглядеть так: =СУММ($B$1,C1) , а следующие ячейки будут содержать =СУММ($B$1,C2) , =СУММ($B$1,C3) и =СУММ($B$1 ,С4) формулы. С помощью этого метода вы можете исправить столько ссылок, сколько захотите.

    Дополнительные часто задаваемые вопросы

    Можно ли исправить только ссылку на столбец или только ссылку на строку в формуле?

    Знак доллара ($) позволяет зафиксировать выбранные значения в формуле. Например, если вы хотите, чтобы значение в ячейке B3 оставалось неизменным при копировании формулы в другие ячейки, добавьте знак доллара как перед столбцом, так и перед ссылкой на строку ($B$3).

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

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

    • B$3 — ссылка на столбец может меняться, но ссылка на строку остается фиксированной.

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

    По умолчанию Excel копирует формулу ячейки при использовании сочетаний клавиш «Ctrl + C» и «Ctrl + V». Если вместо этого вам нужно скопировать значение ячейки, выполните следующие действия:

    <р>1. Выберите ячейку, содержащую значение, которое вы хотите воспроизвести.

    <р>2. Используйте сочетание клавиш «Ctrl + C», чтобы скопировать данные ячейки.

    <р>3. Выберите ячейки, в которые нужно вставить значение ячейки.

    <р>4. Нажмите на стрелку рядом с кнопкой "Вставить" в верхней части экрана.

    <р>5. Выберите «Значения».

    Улучшите свой рабочий процесс

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

    Знаете ли вы какие-нибудь полезные советы по использованию Excel? Пожалуйста, поделитесь ими в разделе комментариев ниже.

    У меня есть таблица с формулой, которую я пытался перетащить вниз, и хотя формула копируется правильно, результаты остаются одинаковыми во всем столбце. Я попробовал F9/проверить, что настройки формулы были установлены на автоматический режим, но это тоже не решило проблему. Кто-нибудь может помочь?

    Факты об Excel

    Ctrl+S используется для сохранения. Ctrl+5 используется для зачеркивания. Почему Ctrl+5? Когда вы используете хэш-метки для подсчета |||| равно 4, зачеркните, чтобы получить 5.

    любовь

    Известный участник

    Добо_Бобо

    Новый участник

    Перейдите в Excel > Файл > Параметры > Параметры расчета

    Там вы увидите три варианта:

    Автоматически
    Автоматически, кроме таблиц данных
    Вручную

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

    Девушка с электронными таблицами

    Новый участник

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

    Я не могу ничего прикрепить здесь? Я думаю, что проще всего было бы сделать снимок экрана - в основном у меня есть столбец A, который является идентификатором клиента, а столбец B - это дата заказа. Столбец C представляет собой дату первого заказа с формулой =MAX(IF($A$2:$A$5000=A2,$B$2:$B$5000)) и дату первого заказа в столбце D с формулой =MIN(IF($A$2) :$A$5000 = A2, $B$2:$B$5000)) . Обе формулы имеют одинаковую проблему. Я пытаюсь определить дату первого и последнего заказа для некоторых заказов клиентов, чтобы определить срок службы. Очевидно, что не каждый клиент делал заказы более одного раза, таких заказов тысячи.

    Я нашел в Интернете формулу даты первого и последнего заказа, но не знаю, почему она не работает. Вы можете помочь?

    Эрик В

    MrExcel MVP

    Добро пожаловать на форум.

    Рискуя констатировать очевидное, знаете ли вы, что это формулы массива? Чтобы заставить формулу массива работать, вы должны ввести ее в строку формул, и вместо того, чтобы нажимать Enter, когда вы закончите, вы нажимаете Control + Shift + Enter. Затем вы можете скопировать его и вставить в столбец. Без этого формула не будет работать правильно, и это может быть причиной плохих результатов.

    В качестве альтернативы, если у вас есть подписка на Office 365, вы можете использовать эту формулу без массива:

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

    Девушка с электронными таблицами

    Новый участник

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

    Большое спасибо за помощь!

    Девушка с электронными таблицами

    Новый участник

    Я попробовал формулу без массива, но она не работает. Я заметил, что некоторые из моих свиданий имеют забавный формат. Таким образом, те, которые отображаются правильно, меняются на число, когда я меняю их с даты на текстовый формат. Однако те, у которых, кажется, есть проблема, не превращаются в число, когда я переключаю форматирование с даты на текст, который по-прежнему отображается как дата. например правильно рабочая дата 31/12/2017 и превращается в 43100 при переключении с даты на текстовый формат. Дата, которая, кажется, имеет ошибку, - 01.01.2014 и остается 01.01.2014, когда я переключаю ячейку с даты на текстовый формат.можешь помочь?

    Добо_Бобо

    Новый участник

    Я попробовал формулу без массива, но она не работает. Я заметил, что некоторые из моих свиданий имеют забавный формат. Таким образом, те, которые отображаются правильно, меняются на число, когда я меняю их с даты на текстовый формат. Однако те, у которых, кажется, есть проблема, не превращаются в число, когда я переключаю форматирование с даты на текст, который по-прежнему отображается как дата. например правильно рабочая дата 31/12/2017 и превращается в 43100 при переключении с даты на текстовый формат. Дата, которая, кажется, имеет ошибку, - 01.01.2014 и остается 01.01.2014, когда я переключаю ячейку с даты на текстовый формат. можешь помочь?

    Думаю, Эрик решил вашу проблему, так что поясним ее подробнее.

    Если вы используете формулу без < >, она не будет работать, потому что это должна быть формула массива.

    Нужно щелкнуть ячейки, содержащие формулы, и нажать клавиши: Ctrl Shift Enter

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

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

    Я раскрасил некоторые ячейки, чтобы их было понятнее. Я выбрал G2, чтобы вы могли видеть формулу массива. И я нахожусь в Великобритании, поэтому формат даты установлен в формат даты Великобритании для всех ячеек, содержащих даты. Как говорит Эрик, формула массива также должна быть применена к =MIN(IF

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

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

    Перетащите или скопируйте формулу и заблокируйте значение ячейки с помощью клавиши F4

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

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

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

    Перетащите или скопируйте формулу и заблокируйте значение ячейки с помощью Kutools for Excel

    Утилита Convert Refers Kutools for Excel может не только сразу преобразовать все ссылки на ячейки одной ячейки в абсолютные, но и сразу преобразовать все ссылки на ячейки диапазона ячеек формулы в абсолютные. Пожалуйста, сделайте следующее.

    Перед применением Kutools for Excel сначала загрузите и установите его.

    <р>1. Выберите ячейку формулы (или диапазон ячеек формулы), которая вам нужна, чтобы заблокировать значение, затем нажмите Kutools > Еще > Преобразовать ссылки. Смотрите скриншот:

    <р>2. В диалоговом окне "Преобразование ссылок на формулы" выберите параметр "В абсолютные" и нажмите кнопку "ОК".

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

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

    Читайте также: