Почему не протягивается формула в excel на весь столбец

Blog of Khlebalin Dmitriy

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

О чем я:

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

• Если протянуть формулу в столбик, то во все последующие ячейки подставляется сумма первой строки, то есть далее они не пересчитываются.

Что проверяем в этом случае:

• Файл — Параметры — Дополнительно — группа Параметры правки (галка Разрешить маркеры заполнения и перетаскивание ячеек должна стоять)

• Посчитать в ручном режиме: вкладка «формулы»-«Вычисление»-кнопка «Произвести вычисления».

• Или посчитать в автоматическом режиме: вкладка «формулы» -«вычисления»-кнопка «Параметры вычислений». Появится выпадающий список: «Автоматически».

также эту настройку можно выставить или проверить в разделе «Параметры»

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

Теперь все считается и протягивается правильно.

Всем хорошей работы.

Share this:

Понравилось это:

Sorry, the comment form is closed at this time.

О сайте

Записки из мира IT…

В этом блоге, я пишу заметки о своей, как повседневной жизни, так и жизни и работе в сфере IT технологий. Собираю интересные ссылки, выражаю свои мысли и прочее… В основном посты посвящены, Управленческим моментам и решениям, различным продуктам Microsoft и VMWare, которые я эксплуатирую многие годы, Nix, MacOS, сетке, и другим интересным вопросам и задачам, с которыми приходится ежедневно сталкиваться и иметь дело. Здесь приведены не только мои посты, но и посты, которые были найдены мною на безграничных просторах интернета. Все написанное здесь, было проделано мною или моими коллегами при моем непосредственном участии на виртуальных машинах или в продакшин среде, о чем свидетельствуют комментарии в текстах. Всем удачи в работе.

Почему не протягивается формула в excel на весь столбец

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

Неправильный формат ячеек

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

• Войдите в раздел «Главная» и в разделе инструментов «Число» посмотрите значение. Если стоит «Тестовый», не удивляйтесь, почему не протягивается формула в Excel.

• Дважды жмите по этому полю и выберите оптимальное значение.
• При отсутствии нужного варианта выделите целевой диапазон, жмите по нему правой кнопкой мышки (ПКМ) и выберите «Формат ячеек».

• Выделите секции, в которых расчета не производилась и жмите F2 для проведения новых вычислений.

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

Ошибки

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

1. Зайдите в раздел «Формулы».
2. В блоке «Зависимости …» жмите «Вычислить …».
3. Жмите на кнопку «Вычислить» и ищите ошибку, после чего устраните ее.

Активен режим «Показать формулы»

Объяснением, почему не протягивается формула в Excel, является активный режим «Показать …». Для решения задачи сделайте следующее:

1. Зайдите в «Формулы» и в разделе «Зависимости …» жмите на кнопку «Показать …» (если активна).
2. Убедитесь, что в интересующей ячейке отображается результат.

В случае, когда Эксель все равно не протягивает формулу, причина кроется в другом (об этом ниже).

Неправильная настройка вычислений

Бывает, что программа показывает правильное значение, но при внесении изменений, которые с ней связаны, пересчет результата не происходит. Это свидетельствует о неправильной настройке параметров, из-за чего Excel не протягивает формулу. Для восстановления нормальной работоспособности сделайте следующие шаги:

• Зайдите в раздел «Файл» в Эксель.
• Кликните на пункт «Параметры».

• Зайдите в раздел «Формулы».
• Найдите блок «Параметры вычислений», который находится в верхней части.
• Обратите внимание на параметр «Вычисления в книге».
• Если он стоит в позиции «Автоматически», это является причиной неактуальности результата.
• Переставьте выключатель в требуемое место.

• Жмите на «ОК» для сохранения результата.

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

Сбои в синтаксической части

Причиной текстового отображения, или того, что не протягивается формульный расчет, может быть пропуск / изменение буквы в Эксель. Если ее вводили вручную, а не через «Мастер функций», это вполне возможно. Распространенный сбой, связанный с отображением выражения — пробел перед знаком «=». В таких обстоятельствах пересмотрите синтаксис в тех моментах, где в Экселе не протягивает формулу, и она некорректно отображается.

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

В комментариях отпишитесь, какой из способов помог решить проблему, и почему еще не протягивается расчет в Эксель.

Отличного Вам дня!

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

Неправильный формат ячеек

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

• Перейдите в раздел «Главная» и под инструментами «Число» посмотрите значение. Если там написано «Тест», не удивляйтесь, потому что формула в Excel не растянута.

• Дважды щелкните это поле и выберите оптимальное значение.
• Если нужный вариант отсутствует, выберите целевой диапазон, щелкните его правой кнопкой мыши (ПКМ) и выберите «Форматировать ячейки».

• Выделите разделы, в которых расчет не производился, и нажмите F2 для выполнения новых расчетов.

Затем проверьте, растянуто значение или нет. Если не получается получить результат, переходите к следующему варианту.

Ошибки

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

1. Перейдите в раздел «Формулы».
2. В блоке «Зависимости…» нажмите «Рассчитать …».
3. Нажмите кнопку «Рассчитать» и найдите ошибку, а затем исправьте ее.

Активен режим «Показать формулы»

Объяснение, почему формула не расширяется в Excel, — это активный режим «Показать…». Чтобы исправить это, сделайте следующее:

1. Перейдите в «Формулы» и в разделе «Зависимости…» нажмите кнопку «Показать…» (если она активна).
2. Убедитесь, что результат отображается в интересующей ячейке.

Если Excel по-прежнему не сохраняет формулу, причина в другом (подробнее об этом ниже).

Неправильная настройка вычислений

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

• Перейдите в раздел «Файл» в Excel.
• Щелкните по записи «Параметры».

• Перейдите в раздел «Формулы».
• Найдите блок параметров расчета вверху.
• Обратите внимание на параметр «Рабочая книга расчетов».
• Если он находится в положении «Автоматически», это причина неактуальности результата.
• Установите переключатель в желаемое положение.

• Нажмите «ОК», чтобы сохранить результат.

Далее проверяем работу — необходимый элемент в Excel, как правило, расширяется и при настройке параметра происходит автоматический пересчет.

Сбои в синтаксической части

Причиной отображения текста или того факта, что расчет формулы не растягивается, может быть переход / изменение буквы в Excel. Если он был введен вручную, а не через «Мастер», это вполне возможно. Распространенная проблема при отображении выражения — это пробел перед знаком «=». В таких случаях проверяйте синтаксис в тех случаях, когда Excel не расширяет формулу и она отображается неправильно.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Заключение

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

В Excel не работают формулы.

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

1 Вариант

Вполне вероятно у Вас отключен автоматический пересчет листа, для этого просто нажмите клавишу F9

2 Вариант для всех версий Office

Проверьте что бы формат ячейки не было текстовым (из-за этого могут не работать вычисления и соответственно формулы)

PS что бы это исправить, выделите ячейки и выберите формат Общий (см. рисунок Формат Ячеек)

3 Вариант для Office 2010

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

Для этого нажмите на меню ФАЙЛ-> ПАРАМЕТРЫ-> ФОРМУЛЫ и в меню «Параметры вычисления» смотрите где стоит у Вас галочки (см картинку должно быть так)

4 Вариант для Office 2007/2010

Для Office 2007 путь такой Формула->Параметры вычисления->и ставим галку на Автоматически, кроме таблиц данных. (см рисунок ниже)

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

Рассмотрим несколько способов протягивания (копирования) формул в строках и столбцах программы Excel.

Первый способ: протянуть ячейку в столбце или строке.

Чтобы протянуть (распространить) формулу из одной ячейки в несколько ячеек столбца или строки следует выполнить ряд действий:
1. Записать функцию (формулу) в ячейку и нажать ENTER.
2. Навести курсор на нижний правый угол ячейки таким образом, чтобы он приобрел форму тонкого черного крестика.
3. Нажать и удерживать левую кнопку мыши.
4. Не отпуская кнопку мыши потянуть крестик в нужном направлении. В ту сторону, в которую следует распространить значение ячейки.

Второй способ: быстрое протягивание формулы в один клик мыши.

Для быстрого заполнения столбца формулой или значением из ячейки достаточно выполнить следующие действия:
1. Записать функцию (формулу) в ячейку и нажать «ENTER».
2. Навести курсор на нижний правый угол ячейки таким образом, чтобы он приобрел форму тонкого черного крестика.
3. Кликнуть двойным кликом по правому нижнему углу ячейки.

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

Третий способ: протянуть формулу без изменения (смещения) исходных ячеек.

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

Четвертый способ: протянуть формулу при помощи копирования.

Подходит для работы под фильтрами.
Выполняется следующим образом:
1. Записать функцию (формулу) в ячейку и нажать ENTER.
2. Копировать значение ячейки при помощи выпадающего контекстного меню правой кнопкой мыши или сочетанием клавиш «ctrl»+»c».
3. Выделить нужный диапазон.
Для быстрого выделения вниз достаточно нажать сочетание клавиш:
«Ctrl»+»shift»+ стрелка вниз
Для быстрого выделения в сторону достаточно нажать сочетание клавиш:
«Ctrl»+»shift»+ стрелка в сторону

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

О чем я:

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

• Если протянуть формулу в столбик, то во все последующие ячейки подставляется сумма первой строки, то есть далее они не пересчитываются.

Что проверяем в этом случае:

• Файл — Параметры — Дополнительно — группа Параметры правки (галка Разрешить маркеры заполнения и перетаскивание ячеек должна стоять)

• Посчитать в ручном режиме: вкладка «формулы»-«Вычисление»-кнопка «Произвести вычисления».

• Или посчитать в автоматическом режиме: вкладка «формулы» -«вычисления»-кнопка «Параметры вычислений». Появится выпадающий список: «Автоматически».

также эту настройку можно выставить или проверить в разделе «Параметры»

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

Теперь все считается и протягивается правильно.

Всем хорошей работы.

Posted by |
ms office 2003/2007/2010/2013/365

Sorry, the comment form is closed at this time.

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

1 Простое протягивание формулы

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

• В первую ячейку с одной из сторон (например, сверху) надо записать нужную формулу и нажать Enter.
• После появления рассчитанного по формуле значения навести курсор в нижний правый угол ячейки. Подождать, пока толстый белый крестик не превратиться в тонкий черный.
• Нажать на крестик и, удерживая его, протянуть формулу в нужном направлении. В указанном примере — вниз.

Аргументы в формуле будут изменяться соответственно новому расположению. И если в самой первой ячейке это были F7 и G7, в последней позиции столбца это будет уже F12 и G12. Соответственно, если начинать распространять формулы по строкам, изменяться будут не цифры, а буквы в обозначениях ячеек.

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

2 Быстрое автозаполнение

Еще один способ в Excel протянуть формулу до конца столбца с более высокой по сравнению с первой методикой скоростью. Требует от пользователя применить такие действия:

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

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

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

3 Протягивание без изменения ячеек в формуле

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

Для распределения без изменения адреса ячейки выполняются те же действия, что и при обычном протягивании или автоматическом копировании. Но при вводе формулы следует зафиксировать адреса, которые не будут меняться. Для этого используются символы доллара — $. Если в каждом новом пункте столбца при расчетах используется одна и та же ячейка, значки надо будет поставить и перед номером строки, и перед литерой, которая указывает на колонку. Как в примере: $G$6.

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

4 Простое копирование

Еще один способ представляет собой не совсем протягивание, а копирование. Но только более простое и позволяющее выделить конкретный диапазон, а не доверять такое выделение компьютеру. Процесс распределения требует выполнить следующие действия:

• Записать в одну из крайних ячеек строки или столбца нужную формулу и нажать Enter.
• Скопировать значение функции — с помощью контекстного меню, иконки на панели или комбинации клавиш Ctrl + C.
• Установить курсор в противоположную часть столбца или строки.
• Нажать на клавиши Ctrl + Shift + «Стрелка вверх». Результатом становится выделение нужного диапазона, даже если на этом участке столбца будет несколько сотен или тысяч пунктов.
• Вставить формулу. Самый простой способ сделать это — нажать комбинацию Ctrl + V.

Результатом будет такое же распределение функции по столбцу, как и при использовании способа №2. Но в отличие от него здесь можно выделить только часть диапазона. Или, наоборот, продлить такое протягивание дальше даже при наличии пустых строк. Правда, во втором случае лишнее значение придется удалить вручную.

Эта небольшая хитрость подходит и для распределения вдоль строки. В этом случае вместо комбинации Ctrl + Shift + «Стрелка вверх» придется нажать Ctrl + Shift + «Стрелка влево» (или вправо, если копируемая формула находится в крайнем левом столбце).

5 Протягивание формул в таблице Excel

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

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

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

Как в excel растянуть формулу на столбец с объединенными ячейками?

Использовать объединенные ячейки в расчетных таблицах.

А если по существу — если вам нужны красивые таблички с объединенными ячейками на выходе то сделайте отдельно лист с плоской таблицей и записями вида БД (ранее уже писалось как это должно выглядеть) где и производите все расчеты.
А в итоговую красивую табличку с, прости Господи, объединенными ячейками выводите только результат.

Решаем проблемы с формулами в Excel. Что если формула не работает или показывает ошибку

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

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

Изменение формата ячеек Excel

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

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

Конечно, в таком случае следует выставить правильный формат. Для этого нужно следовать таким шагам:

1. Выделить ячейку и перейти на вкладку «Главная» на ленте. После этого перемещаем курсор мыши в группу «число». Здесь также есть поле, в котором демонстрируется формат, в данный момент присвоенные ячейке.
2. Нужно нажать на стрелку вниз возле этого поля. После этого появится список с разными форматами ячеек. Нужно выбрать тот, который соответствует тому, который нужно применять в конкретной формуле. После этого делается клик по выбранному варианту.

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

1. Определить, нам нужно изменить формат одной ячейки или нескольких. После этого выбираем ту, которая нам подходит (или целый диапазон выделяем), после чего делаем правый клик по ней (ним). Далее ищем в появившемся перечне пункт «Формат ячеек». Задача может быть существенно упрощена, если пользователь нажмет сочетание Ctrl+1.

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

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

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

Что следует делать на практике?

1. Выделяем ячейку, которая расположена в самом верху диапазона.
2. Наводим курсор на ее правую нижнюю часть (то есть, на угол), убеждаемся в том, что появился знак плюса черного цвета, нажимаем левую кнопку, зажимаем ее и перемещаем курсор до того места, где должен закончиться этот диапазон.

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

Отключение режима “Показать формулы” в Excel

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

1. Находим в главном меню вкладку «Формулы». Немного ниже и правее нее находится группа инструментов «Зависимость формул», в которой находится кнопка «Показать формулы». Нужно сделать один левый клик мышью при условии, что эта функция активирована.

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

Активизация автоматического пересчета формул в Excel

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

1. Переходим в меню «Файл».
2. После этого ищем раздел «Параметры» и переходим в него.
3. Далее мы увидим окно. В списке справа находится большое количество различных разделов, но нас интересует «Формулы». После клика мы увидим, что правая часть окна изменилась. Переходим в группу «Параметры вычислений», где ставим флажок возле функции «автоматически». После того, как действия будут выполнены, нам нужно нажать кнопку ОК.

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

Исправление ошибок в формулах

Бывают ситуации, когда человек допускает ошибку при вводе формулы. В этом случае программа будет просто ее определять, как простое значение в текстовом формате. То есть, и расчетов также не будет. Так, самой распространенной ошибкой является ситуация, когда перед вводом знака «равно» ставится пробел. Здесь важно помнить, что перед знаком = не должно быть никаких пробелов, и он всегда должен располагаться перед формулой.

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

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

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

Если хотя бы одна из этих ошибок появляется, то нужно проверить, правильно ли заполнена информация в аргументах этой функции. После этого осуществляется проверка самой формулы и то, есть ли в ней ошибки. Особенно нужно акцентировать внимание на тех из них, которые не соответствуют законам математики. Например, очень частая проблема — деление на ноль. Нередко она возникает случайно. Просто в результате вычисления определенного значения функцией, которая входит в состав формулы и служит аргументом для функции более высокого порядка, появляется число ноль. В результате, формула выдает ошибку #ДЕЛ/0.

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

1. Выделяем ту ячейку, в которой есть ошибочная формула. Далее переходим во вкладку «Формулы». Там нужно найти инструмент «Зависимости формул» — «Вычислить формулу».
2. Перед глазами пользователя появится диалоговое окно. В нем детально будет описано, как осуществлять отчет правильно. Чтобы решить проблему, необходимо нажать кнопку «Вычислить». После этого функция будет последовательно проверяться на каждом шагу. Поэтому кнопку нужно нажать несколько раз. Таким образом можно отследить, на каком шагу возникла проблема и оперативно ее решить.

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

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

Чтобы исправить проблему, необходимо нажать на кнопку «Изменить в строке формул». После этого формула будет работать правильно.

Исправление ошибки в синтаксисе

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

1. В целом, если формула небольшая, ее можно проверить и самостоятельно. Это не потребует большого количества времени. Также можно без проблем проверять формулы самостоятельно, если их количество небольшое. Дело в том, что при увеличении количества формул увеличивается и количество ошибок при проверке. Мозг загромождается большим количеством информации, пусть и простой для обработки.
2. Если есть большое количество формул или сами формулы довольно большие, лучше воспользоваться специализированным инструментом. Каким именно — решать только вам. Каждый из них подходит, просто один предусматривает выполнение действий более в ручном режиме, а другой — автоматическом.

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

Выводы

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