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

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

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

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

Копирование формул Excel без изменений ссылок

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

Скопируйте значения столбца B (например, комбинацией клавиш CTRL+C) и вставьте их в столбец D (CTRL+V) . А потом переместите данные из столбца B в E (например, комбинацией клавиш CTRL+X).

Теперь переключитесь в режим отображения формул – CTRL+`(Ё). Обратите внимание, как ведут себя ссылки на ячейки при перемещении и копировании.

При перемещении (те, что в столбце E) ссылки не изменяются. А при копировании (те, что в столбце D), они смещаются автоматически.



Копирование формул в Excel со смещением

При копировании адреса относительных ссылок приспосабливаются к новому положению. Если ссылка была на одну ячейку влево, то она так и продолжает ссылаться, но адрес естественно меняется. Поэтому формула: =A2*1,23 стала формулой =C2*1,23. Когда мы ту же самую формулу не скопируем, а переместим, то адреса ее ссылок не изменятся, несмотря на то, что они относительные. При перемещении ссылки на ячейки ведут себя как абсолютные. Об этом следует всегда помнить пользователю Excel.

Примечание. В разделе, посвященном формулам, будет уделено больше внимания относительным и абсолютным ссылкам. А пока отметим что по умолчанию ссылки относительные, а если в адресе присутствует символ «$» — значит ссылка абсолютная.

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

  1. Скопируйте «Лист1», например с помощью мышки+CTRL. Наведите указатель на ярлычок первого листа. Удерживая левую клавишу мышки и клавишу CTRL на клавиатуре, переместите ярлычок (копия листа) в новое место. Отпустите сначала мышку, а потом клавиатуру. У вас получился такой же лист, но уже с названием «Лист1(2)».
  2. На копии «Лист1(2)» в ячейку D2 скопируйте значение из B2, а в ячейку E2 переместите (как на предыдущем задании).
  3. Теперь скопируйте столбцы D:E из «Лист1(2)» и вставьте их в столбцы D:E из «Лист1».

Как видите обе ячейки D2 и E2 были одинаково и одновременно скопированы, но ссылки в их формулах уже ведут себя по-разному. При копировании формул E2 значение не меняется. Все из-за того, что значения E2 из «Лист1(2)» получены путем перемещения и это уже считается для Excel иной способ присваивания адресов в формулах данной ячейки. Чтобы еще раз в этом убедиться, снова приведите табличку на «Лист1» в изначальный вид как на первом рисунке.

На этот раз в ячейку E2 скопируйте формулу из B2, а в ячейку D2 переместите туже самую формулу.

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

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

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

все уроки

Автозаполнение ячеек в Numbers на iPad

Numbers

Поиск по этому руководству

Руководство пользователя Numbers для iPad

  • Добро пожаловать!

    • Основные сведения о приложении Numbers

    • Основные сведения об изображениях, диаграммах и других объектах

    • Создание таблицы

    • Открытие таблиц

    • Как избежать случайных правок

    • Персонализация шаблонов

    • Использование листов

    • Отмена и повтор изменений

    • Сохранение таблицы

    • Поиск таблиц

    • Удаление таблицы

    • Печать таблицы

    • Изменение фона листа

    • Копирование текста и объектов из одних приложений в другие

    • Настройка панели инструментов

    • Основы работы с сенсорным экраном

    • Использование Apple Pencil с приложением Numbers

      • Создание таблицы с помощью VoiceOver

      • Создание формул и автозаполнение ячеек с помощью VoiceOver

    • Добавление и удаление таблицы

    • Выбор ячеек, строк и столбцов

      • Добавление и удаление строк и столбцов

      • Перемещение строк и столбцов

      • Изменение размера строк и столбцов

    • Объединение и разделение ячеек

      • Изменение внешнего вида текста в таблице

      • Отображение, редактирование и скрытие заголовка таблицы

      • Изменение цветов сетки

      • Использование стилей таблицы

    • Изменение размера, перемещение и открепление

      • Добавление текста в таблицы

      • Ввод данных с помощью форм

      • Добавление объектов в ячейки

      • Добавление биржевой информации

      • Добавление даты, времени и длительности

      • Автозаполнение ячеек

      • Копирование, перемещение и удаление содержимого ячеек

      • Создание снимка таблицы

      • Форматирование дат, значений валют и других типов данных

      • Добавление флажков и других элементов управления в ячейки

      • Форматирование таблиц с двунаправленным текстом

    • Перенос текста по словам для размещения в ячейке

    • Выделение ячеек

    • Сортировка по алфавиту и сортировка данных в таблице

    • Фильтрация данных

      • Основные сведения о категориях

      • Добавление, редактирование или удаление категорий

      • Изменение групп категорий

      • Добавление вычислений для обобщения данных группы

      • Основные сведения о сводных таблицах

      • Создание сводной таблицы

      • Добавление и расстановка данных сводной таблицы

      • Изменение параметров сортировки и группировки данных сводной таблицы и многое другое

      • Обновление сводной таблицы

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

    • Быстрое вычисление суммы, среднего значения и других значений

    • Использование клавиатуры формул

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

    • Копирование и вставка формул

    • Использование справки по формулам и функциям

      • Выбор данных для создания диаграммы

      • Добавление столбчатых, полосчатых, линейных, площадных, круговых, кольцевых или лепестковых диаграмм

      • Добавление пузырьковых диаграмм и диаграмм рассеивания

      • Интерактивные диаграммы

      • Удаление диаграммы

      • Изменение типа диаграммы

    • Изменение данных диаграммы

    • Перемещение и изменение размеров диаграмм

      • Изменение внешнего вида серий данных

      • Добавление легенды, линий сетки и других меток

      • Изменение внешнего вида текста и меток на диаграмме

      • Добавление границы и фона к диаграмме

      • Использование стилей диаграммы

    • Выбор текста и размещение точки вставки

      • Добавление текста

      • Копирование и вставка текста

        • Форматирование таблицы для другого языка

        • Использование фонетической транскрипции

        • Использование двунаправленного текста

        • Использование вертикального текста

    • Изменение внешнего вида текста

    • Использование стилей текста

      • Изменение регистра текста

      • Добавление буквиц

      • Лигатуры

      • Надстрочные и подстрочные символы

      • Автоматическое форматирование дробей

      • Формат тире и кавычек

      • Форматирование текста (китайский, японский, корейский)

      • Установка табуляторов

      • Выравнивание текста

      • Разбиение текста на колонки

      • Настройка межстрочного интервала

    • Форматирование списков

    • Добавление и редактирование уравнений

    • Добавление ссылок

      • Добавление изображения

      • Добавление галереи изображений

      • Редактирование изображения

      • Добавление и редактирование фигуры

      • Объединение или разъединение фигур

      • Сохранение фигуры в медиатеке фигур

      • Добавление и выравнивание текста внутри фигуры

    • Добавление линий и стрелок

    • Добавление и редактирование рисунков

      • Добавление аудио и видео

      • Запись видео и звука

      • Редактирование аудио- и видеофайлов

    • Выбор формата фильма

      • Размещение и выравнивание объектов

      • Использование направляющих линий

      • Размещение объектов внутри текстового блока или фигуры

      • Наложение, группировка и закрепление объектов

    • Изменение прозрачности объектов

    • Заливка объектов цветом или изображением

    • Добавление границы объекта или листа

    • Добавление подписи или заголовка

    • Добавление отражения или тени

    • Использование стилей объектов

    • Изменение размеров, поворот и зеркальное отражение объектов

    • Поиск определения слова

    • Поиск и замена текста

    • Автоматическая замена текста

    • Проверка правописания

    • Задание имени автора для комментариев

    • Выделение текста цветом

    • Добавление и печать комментариев

    • Отправка таблицы

    • Основные сведения о совместной работе

    • Приглашение к совместной работе

    • Совместная работа над общей таблицей

    • Просмотр последних действий пользователей в общей таблице

    • Изменение настроек общей таблицы

    • Закрытие общего доступа к таблице

    • Общие папки и совместная работа

    • Использование Box для совместной работы

    • Использование iCloud с приложением Numbers

    • Импорт таблицы Excel или текстового файла

    • Экспорт в Excel или другой формат

    • Восстановление более ранней версии таблицы

    • Перемещение таблицы

    • Защита таблицы паролем

    • Создание собственных шаблонов и управление ими

      • Перенос таблиц с помощью AirDrop

      • Передача таблиц с помощь Handoff

      • Перенос таблиц через Finder

    • Сочетания клавиш

    • Символы сочетаний клавиш

  • Авторские права

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

Автозаполнение ячеек

Выполните одно из описанных ниже действий.

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

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

    Совет. Для быстрого автозаполнения ячеек содержимым из соседних ячеек с помощью клавиатуры нажмите Command-обратная косая черта (\), чтобы включить автозаполнение, затем нажмите клавиши со стрелками, чтобы расширить границы желтой рамки. По завершении нажмите клавишу Return.

  • Автозаполнение ячеек последовательным содержимым или шаблонами из соседних ячеек. Введите два первых элемента серии в первых двух ячейках строки или столбца, которые нужно заполнить: например, введите «A» и «B». Выберите эти ячейки, коснитесь , коснитесь «Автозаполнение ячеек», затем перетяните желтую рамку над теми ячейками, которые нужно заполнить.

    Вы также можете автоматически заполнить ячейки, используя шаблоны значений. Например, если две выбранные ячейки содержат 1 и 4, то при перетягивании их через две соседние ячейки в соседние ячейки добавятся значения 7 и 10 (то есть значение каждый раз увеличивается на 3).

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

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

Максимальное количество символов:
250

Не указывайте в комментарии личную информацию.

Максимальное количество символов: 250.

Благодарим вас за отзыв.

Скопируйте формулу, перетащив маркер заполнения в Excel для Mac

Excel для Microsoft 365 для Mac Excel 2021 для Mac Excel 2019 для Mac Excel 2016 для Mac Дополнительно…Меньше

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

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

Скопируйте формулу, перетащив маркер заполнения

Выполните следующие действия:

  1. Выберите ячейку с формулой, которую вы хотите ввести в соседние ячейки.

  2. org/ListItem»>

    Поместите курсор в правый нижний угол так, чтобы он превратился в знак плюса (+), например:

  3. Перетащите маркер заполнения вниз, вверх или через ячейки, которые вы хотите заполнить. В этом примере на рисунке показано перетаскивание маркера заполнения вниз:

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

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

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

Советы: 

  • Вы также можете нажать Ctrl+D, чтобы заполнить формулу вниз в столбце. Сначала выберите ячейку с формулой, которую вы хотите заполнить, затем выберите ячейки под ней и нажмите Ctrl+D.

  • Вы также можете нажать Ctrl+R, чтобы заполнить формулу справа в строке. Сначала выберите ячейку с формулой, которую вы хотите заполнить, затем выберите ячейки справа от нее, а затем нажмите Ctrl+R.

При заполнении не работает

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

Ссылки на ячейки — это просто указатели на другие ячейки. Например, предположим, что первая ячейка имеет следующую формулу:

.


=СУММ(А1,В1)


A1 и B1 являются относительными ссылками . Это просто означает, что когда вы заполняете формулу, ссылки будут постепенно изменяться с A1, B1 на A2, B2 и т. д., например:


=СУММ(А1,В1)


=СУММ(А2,В2)


=СУММ(A3,B3)

В других случаях вы можете не захотеть изменять ссылки на другие ячейки. Например, предположим, что вы хотите, чтобы первая ссылка, A1, оставалась фиксированной, а B1 изменялась при перетаскивании маркера заполнения. В этом случае вы должны ввести знак доллара ($) в первой ссылке: =СУММ($A$1,B1) . Знак доллара заставляет Excel продолжать указывать на A1 при заполнении других ячеек. Вот как это будет выглядеть:


=СУММ($A$1;B1)


=СУММ($A$1;B2)


=СУММ($A$3;B3)

Ссылки со знаком доллара ($) называются абсолютными ссылками . При заполнении ссылка на A1 остается фиксированной, но Excel меняет ссылку B1 на B2 и B3.

Не удается увидеть дескриптор файла?

Если вы не видите дескриптор заполнения, возможно, он скрыт. Для отображения:

  1. В меню Excel выберите Настройки .

  2. org/ListItem»>

    Щелкните Редактировать .

  3. В разделе Параметры редактирования установите флажок Разрешить дескриптор заполнения и перетаскивание ячеек .

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

Вот как это включить:

  1. В меню Excel выберите Настройки .

  2. Щелкните Расчет .

  3. org/ListItem»>

    Убедитесь, что в параметрах расчета выбрано значение Автоматически .

Как расширить уравнения столбцов в Excel | Малый бизнес

К. Тейлор

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

Абсолютные и относительные ссылки

  1. Форматирование ячейки с использованием абсолютных или относительных ссылок действует только при копировании формулы ячейки. Excel автоматически корректирует относительные ссылки на ячейки, чтобы отразить новую позицию скопированной формулы. Например, если ячейка C1 умножает ячейки A1 и B1, формула впоследствии умножит A2 на B2 при копировании в ячейку C2. Чтобы предотвратить это изменение, отформатируйте формулу с абсолютными ссылками, которые обозначены знаком доллара. В предыдущем примере формула «=A1 B1» будет иметь формат «=$A$1 $B$1.»

Копирование формул

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

Специальная вставка

  1. Щелчок правой кнопкой мыши по выделенному диапазону дает возможность выбрать Специальная вставка. Эта функция дает вам расширенный контроль над вставкой выбранных ячеек, например выбор вставки только результатов без каких-либо формул. Когда вы выбираете вставку формул, Excel по-прежнему изменяет относительные ссылки на ячейки, чтобы отразить их новые позиции, но абсолютные ссылки остаются статичными. В качестве альтернативы, выбрав «Формулы и числовые форматы», формулы будут вставлены, а результаты будут использовать числовой формат исходной ячейки.

Массивы

  1. Массивы позволяют одновременно вводить формулы в диапазон ячеек, например, выделяя ячейки с C1 по C20, а затем вводя формулу в верхней строке формул. Чтобы отформатировать формулу как массив, нажмите «Ctrl», «Shift» и «Enter» одновременно после завершения формулы. По умолчанию формулы массива не изменяются, чтобы отразить скопированную позицию, если только вы не укажете это, введя диапазон для одной переменной. Например, выделив от C1 до C20 и введя формулу «=A1 B1» (без кавычек) в качестве массива вводит одну и ту же формулу во все выделенные ячейки, тогда как ввод «=A1:A20 B1:B20» умножает столбцы A и B в каждой соответствующей строке.

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