Перейти к содержимому

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

  • автор:

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

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

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

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

Перемещение формулы

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

В группе Буфер обмена на вкладке Главная нажмите кнопку Вырезать.

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

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

Чтобы вировать формулу иформатирование: в группе Буфер обмена на вкладке Главная нажмите кнопку В виде вкладки.

Чтобы вировать только формулу:в группе Буфер обмена на вкладке Главная нажмите кнопку В paste(Главная), выберите специальная ветвь ищелкните Формулы.

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

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

В группе Буфер обмена на вкладке Главная нажмите кнопку Копировать.

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

Чтобы вировать формулу и любое форматирование, в группе Буфер обмена на вкладке Главная нажмите кнопку В виде вкладки.

Чтобы вировать только формулу, в группе Буфер обмена на вкладке Главная нажмите кнопку Вировать ,выберите специальная ветвь ,а затем щелкните Формулы.

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

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

Выделите ячейку с формулой.

В строке формул строка формул выделите ссылку, которую нужно изменить.

Для переключения между сочетаниями нажмите F4.

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

Копируемая формула

Первоначальная ссылка

Новая ссылка

$A$1 (абсолютный столбец и абсолютная строка)

A$1 (относительный столбец и абсолютная строка)

$A1 (абсолютный столбец и относительная строка)

A1 (относительный столбец и относительная строка)

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

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

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

Щелкните Главная > вырезать (или нажмите CTRL+X).

Команда "Вырезать" в группе "Буфер обмена"

Выйдите из ячейки, в которая должна вться формула, и нажмите кнопку Вировать (или нажмите CTRL+V).

Команда "Вставить" в группе "Буфер обмена"

Убедитесь, что ссылки на ячейки остаются нужными.

Совет: Вы также можете щелкнуть ячейки правой кнопкой мыши, чтобы вырезать и врезать формулу.
Щелкните правой кнопкой мыши, чтобы увидеть команды "Копировать", "Вырезать" и "Вставить"

Совет: Формулу также можно скопировать и вкопировать в другую ячейку или на другой таблицу.

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Абсолютная и относительная адресация

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

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

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

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

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

Наименование

Нажатие F4

Полный абсолютный адрес

Неполный абсолютный адрес (по строке)

Неполный абсолютный адрес (по столбцу)

Копирование содержимого ячейки

Метод буксировки.

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

Для выбора способа выполнения этой операции используют специальную буксировку с помощью правой кнопки мыши. При отпускании кнопки появляется специальное меню, в котором можно выбрать конкретную операцию.

Копирование и перемещение через буфер обмена.

Применяется при необходимости создания копий и перемещения содержимого ячеек за пределами видимости.

Выбрать команду «Главная – Буфер обмена –Копировать (Ctrl + C)» или «Главная – Буфер обмена – Вырезать (Ctrl + X)».

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

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

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

Команда «Вставить» (Ctrl +V). Для управления способом вставки используется команда «Специальная вставка». Правила вставки данных из буфера задаются в диалоговом окне.

Автоматизация ввода.

Т.к. таблицы часто содержат однотипные или повторяющиеся данные, то в MS Excel существуют средства автоматизации ввода. Это автозавершение, автозаполнение числами и формулами.

Автозавершение

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

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

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

Автозаполнение

Одной из самых важных функций MS Excel является функция заполнения.

Заполнение – это многократное копирование данных ячеек электронной таблицы.

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

Автозаполнение числами

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

«Копировать ячейки» ячейки А2:А4 будут содержать 1.

«Заполнить» в ячейках А2:А4 будут числа 2, 3, 4.

«Прогрессия» заполняется тип прогрессии, шаг, предельное значение.

Автозаполнение прогрессией

Для формулировки условий заполнения ячеек используется команда «Главная – Редактирование – Заполнить – Прогрессия».

В открывшемся диалоговом окне выбирается тип прогрессии, величина шага и предельное значение. После щелчка на ОК MS Excel автоматически заполнит ячейки в соответствии с заданными правилами.

Автозаполнение формулами

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

относительные адреса изменяются в соответствии с относительным расположением копии и оригинала;

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

А также типичные сценарии, когда вам все это требуется.

Копирование ячейки с формулой в Excel — это на самом деле не проблема. И тут бы можно было закончить заметку, но нет! Вы выделяете то, что нужно скопировать. И Excel послушно скопирует текст, формулу, формат, шрифт. Другими словам — все, что было в исходной ячейке. Но проблемы начинаются, когда вы захотите скопировать ячейку только с формулой или, например, содержимое ячейки без формулы. А нередко нужно скопировать и просто саму формулу в отрыве от ячейки. Часто это необходимо, например, для того, чтобы составлять таблицы для анализа различных данных и показателей (например, посещаемость страниц) в рамках SEO-продвижения сайта
Как поступить в таких случаях? – Читайте наше руководство!

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

Копировать / вставить

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

Копирование происходит через одноименную команду в меню, либо нажав сочетание клавиш Ctrl+C.

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

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

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

Пример: в ячейке A1 нашей таблицы есть формула, которая ссылается на ячейку B1. Мы хотим вставить какую-либо формулу в ячейку C1. В таком случае, по умолчанию, формула в ячейке C1 будет ссылаться на ячейку D1.

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

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

Напишите формулу с любыми данными и скопируйте ее. Нужно знать несколько правил:

  • Если нужно оставить ячейку неизменной — используйте символ $. Пример: $C$2.
  • Если нужно оставить неизменным только столбик — используйте тот же символ. Пример: $C2
  • Если нужно оставить неизменным только строчку — используйте тот же символ. Пример: C$2.

Не используйте символ $ в изменяемых ячейках! Просто перетащите или скопируйте такую вставку.

В Google и «Яндексе», соцсетях, рассылках, на видеоплатформах, у блогеров

Что происходит с ячейкой с формулой при копировании

Проделаем небольшой эксперимент:

  1. Откроем таблицу.
  2. Копируем формулу любым удобным способом.
  3. Вставляем ее через инструмент «Вставить функцию».

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

Формула после вставки перешла в соседнюю ячейку

Мы рекомендуем два решения этой проблемы:

  • Поменять эту формулу на ту, что была до этого. Другими словами, заменить ее на исходный вариант.
  • Задействовать автоматическую фильтрацию.

Итак, если новая формула переходит в соседнюю ячейку, то допустимо будет применить инструмент «Автофильтр»:

  1. Наводим стрелку в угол ячейки (обязательно в нижний, справа).
  2. Сразу же курсор примет вид символа +.
  3. Кликаем по этому плюсу и зажимаем правую кнопку.
  4. Не отпуская кнопки двигаем курсор вниз, вверх, либо — в любую сторону. Формула будет автоматически подтягиваться в новую ячейку.

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

Шаблон для неизменной формулы

Важно: независимо от того, куда вы переместите эту формулу, она постоянно будет ссылаться на A1 и A2.

Удаление $ перед 1 и 2 позволяет изменять строки, но не столбцы. Изменение $ перед A позволит менять столбцы, но не даст изменять строки.

Результат копирования: относительные и абсолютные формулы

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

Все виды формул в Excel можно разделить на два: относительные и абсолютные. Давайте смотреть в чем между ними разница.

Относительные формулы

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

Запомнить: при копировании относительной формулы все такие ссылки (напомним, что они ведут на ячейки) будут автоматом исправлены — чтобы соответствовать относительному положению формулы в ячейке назначения.

Пример: у нас имеется одна формула «=A1+B1» в ячейке C1. Мы копируем указанную формулу в ячейку C2, при этом – формула в ячейке C2 автоматически изменится на «=A2+B2».

Абсолютные формулы

Если вы используете такую формулу, то она относится к ссылкам на ячейки, которые статичны (фиксированы) и не изменяются, независимо от положения формулы в целевой ячейке.

Для создания абсолютной формулы, как вы уже поняли, применяется символ ($), который фиксирует ссылку на ячейку.

Пример: в нашей таблице имеется формула вида «=$A$1+$B$1» в ячейке C1. Мы хотим скопировать эту формулу в ячейку C2. Делаем это и видим: формула в ячейке C2 останется такой же, как и исходная формула «=$A$1+$B$1».

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

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

Решений у этой задачи может быть несколько. Рассмотрим каждый вариант.

Предварительные условия: допустим, у нас есть формула вида:

И нам нужно скопировать её вниз, чтобы получить:

Пример формулы

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

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

Вот еще несколько важных моментов, которые нужно знать:

  • Если поставить символ $ перед столбцом (например, $D62), то столбец будет заблокирован, но строка может изменяться,
  • Если поставить символ $ перед строкой (например, D$62), то строка будет заблокирована, но столбец может изменяться.
  • Если поставить символ $ перед обеими строками, то они будут заблокированы целиком. Например: $D$73.

Эксперимент: попробуйте выделить C62 и сразу нажмите на клавишу F4 до тех пор, пока не увидите необходимое вам значение.

Альтернативный вариант 1

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

  1. Перейдите к нужной ячейке.
  2. Нажмите F2.
  3. Нажмите CTRL+A (так вы выделите все содержимое ячейки).
  4. Нажмите CTRL+C (так вы скопируете все содержимое).
  5. Теперь кликните по какой-либо другой ячейке, куда нужно вставить формулу, и нажмите F2.
  6. Нажмите CTRL + V. Будет вставлена абсолютно идентичная формула.
  7. Теперь осталось просто перетащить формулу по всему диапазону.

Альтернативный вариант 2

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

Пример формулы

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

Вариант изменения формулы

Попробуйте потянуть эту формулу вниз или вправо. Вы увидите, что ссылка осталась такой, какой была изначально. Опять же — вы можете просто удалить символ доллара перед «C». В таком случае значение 62 будет сохранено. Если же вы сотрете символ доллара перед 62, то сохранится «C».

Альтернативный вариант 3

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

Формула для диапазона ячеек

И вот еще один вариант решения этой задачи:

  1. Измените первую формулу (т.е. исходную формулу), закрепив C62.
  2. Направьте указатель мыши в ячейку, где находится первое вхождение формулы.
  3. Нажмите F2 (редактировать)
  4. Переместите курсор на C62.
  5. Нажмите F4 (закрепить).

Теперь первоначальная формула у нас будет выглядеть следующим образом:

Измененная формула

Задача практически решена. Осталось лишь только копировать значения в соответствующие ячейки.

Важно:

  • Все ссылки, которые ведут на С62, — абсолютные
  • Все ссылки, которые ведут на ячейки К, — относительные.

Сложные вопросы в работе с Excel

Я хочу скопировать / вставить идентичную формулу. Как это сделать?

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

Для одной формулы

Просто скопируйте ее из панели формул и вставьте в нужное место.

Для нескольких формул

  1. Создайте пустой лист в рабочей книге.
  2. Вернитесь к первому рабочему листу и скопируйте нужный вам диапазон ячеек.
  3. На новом рабочем листе добавьте идентичный диапазон ячеек и вставьте его туда. Обратите внимание: все адреса ссылок в формулах остались прежними.
  4. Перетащите вставленный диапазон в тот же диапазон на новом рабочем листе. Именно на этот лист, в конечном итоге, и нужно будет вставить диапазон (внимание: речь про первый рабочий лист). Обратите внимание: все наши адреса ссылок остались прежними (внутри формул).
  5. Скопируйте этот диапазон формул на новый рабочий лист.
  6. Перейдите в нужный диапазон назначения на первом рабочем листе (это должен быть тот же диапазон адресов, из которого скопировали на новый рабочий лист) и примените команду вставки.

Обратите внимание: все ранее используемые адреса ссылок остались прежними (внутри формул).

Почему функция суммирования не работает?

Не видя вашего рабочего листа, сложно сделать на 100% корректный вывод и дать совет. Но наиболее частой причиной некорректной работы СУММ можно назвать наличие в ячейке текста (а не цифр).

Дело в том, что функция SUM (СУММ) всегда игнорирует любые текстовые ячейки.

Обычно текстовые числа легко обнаружить: они выровнены по левому краю и имеют зеленый маркер в углу. Проблема в том, что некоторые пользователи привыкли выравнивать текст по правому краю. Но Excel – умная программа: даже когда номер выровнен по правому краю, зеленый маркер также отображается. Некоторые пользователи идут еще дальше: они выбирают опцию «Игнорировать эту ошибку». В итоге — текст показывается как обычные числа (поэтому всегда обещайте внимание на зеленый уголок в ячейке):

Источник ошибки

Не нужно использовать конструкции типа =B7+B6+B5+B4+B3+B2. Вы получите ошибки. Но эту формулу можно задействовать для обнаружения текста, который маскируется под цифры.

Вот как решить проблему чисел, сохраненных как текст:

  1. Выберите диапазон чисел (не включая функцию SUM или СУММ).
  2. Используйте сочетание горячих клавиш Alt+D+E+F. Это приведет к запуску инструмента Text to Columns. В итоге текстовые числа автоматически переведутся в настоящие. Посмотрите на пример ниже:

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

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

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

Параметры вычислений

Два чекбокса Manual (либо, в противоположном случае — Automatic) будут выводиться на панели инструментов. Соответственно, вы сможете очень быстро заметить, когда редактор находится в ручном режиме и наоборот.

Можно ли сделать фильтрацию дубликатов значений из уже настроенного диапазона?

Да, можно. В Excel есть специальная формула для этого:

Специальная формула для фильтрации дубликатов

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

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

  • Если вернулся «duplicate» — значение в этой ячейке уже было в диапазоне.
  • Если вернулся «unique» — значение в этой ячейке еще не было в диапазоне.

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

Заключение: краткая инструкция

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

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

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

Выделите ячейку и воспользуйтесь сочетанием горячих клавиш Control+С, или используйте правую кнопку мыши для выделения. Вставьте ячейку с формулой туда, куда вам необходимо (при помощи команды Control+V или через меню «Вставить»).

Как копировать текст формулы

Вы можете воспользоваться, как минимум, двумя способами:

  1. Нажать F2 и скопировать текст формулы вручную и вставить его в нужное место.
  2. Скопировать ячейку, перейти в ячейку (куда вы хотите вставить формулу) и просто вставить её. Формула будет вставлена с соответствующими корректировками ссылок. Либо — можно скопировать ячейку с формулой и перейти в новую ячейку, воспользоваться сочетанием клавиш Alt+A+E+S и выделить формулу (это специальная вставка).

Если же нужно копировать формулу из «Эксель» в другое приложение, — нажмите клавишы Alt+. Вы увидите длинный список всех формул. Из него вы уже сможете скопировать любой необходимый вариант.

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

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

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

Теперь вы можете вставить формулу как-текст в любую другую программу.

Помните важное: при вставке формулы внутри «Эксель» автоматически обновятся ссылки.

Как вставить одну и ту же формулу несколько раз

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

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

  • Перетащить или заполнить – ссылки на формулы будут меняться в каждой ячейке.
  • Введите «статическую» формулу (например, $A$1), затем – перетащите её или заполните.

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

Способов скопировать такую ячейку несколько:

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

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

Как использовать специальную вставку при фильтрации листов

Здесь все не так сложно:

  1. Примените нужные вам фильтры.
  2. Маркируйте цветным выделением столбик, содержащие данные.
  3. Удалите все фильтры. Для этого используйте сочетание горячих клавиш Alt+D, затем нажмите клавишу F, и после – клавишу S.
  4. Теперь нужно сделать сортировку столбика по цвету (его мы задействовали в шаге до этого).
  5. Спокойно применяем специальную вставку. Данные, которые расположены между фильтрами, никак не пострадают.
  6. Уберите маркирование цветом, которое мы сделали в предыдущих шагах. Удалите раскраску из первого шага.
  7. Снова примените фильтр.

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

Самый простой способ — кликните по нужной ячейке и тут же нажмите клавишу F2, затем — просто вставьте текст. Либо — кликните по строке для ввода формулы (Fx), вставьте ее туда и нажмите Enter.

Вы также можете скопировать и вставить специальные значения.

Если вы копируете «из Excel в Excel», то можно вставлять специальные значения или сами формулы.

Относительные и абсолютные ссылки – как создать и изменить

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

Что такое ссылка на ячейку?

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

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

Например, A1 относится к адресу на пересечении столбца A и строки 1; B2 относится ко второй ячейке в столбце B и так далее.

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

Например, если вы введете простейшее выражение =A1 в клетку C1, Эксель продублирует данные из A1 в C1:

В Microsoft Excel диапазон – это блок из двух или более ячеек. Ссылка на диапазонпредставлена адресами верхней левой и нижней правой его ячеек, разделенных двоеточием.

Например, диапазон A1:C2 включает 6 ячеек от A1 до C2.

Как создать ссылку?

Чтобы записать ссылку на ячейку на том же листе, вам нужно сделать следующее:

  1. Выберите, где вы хотите ввести формулу.
  2. Введите знак равенства (=).
  3. Выполните одно из следующих действий:
    • Запишите координаты прямо в ячейку или в строку формул, или же
    • Кликните ячейку, к которой хотите обратиться.
  4. Введите оставшуюся часть формулы и нажмите Enter для завершения.

Например, чтобы сложить значения в A1 и A2, введите знак равенства, щелкните A1, введите знак плюса, щелкните A2 и нажмите Enter:

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

Например, чтобы сложить значения в A1, A2 и A3, введите знак равенства, затем имя функции СУММ и открывающую скобку, выберите ячейки от A1 до A3, введите закрывающую скобку и нажмите Enter:

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

Например, чтобы сложить все ячейки в строке 1, начните вводить функцию СУММ, а затем кликните заголовок первой строки, чтобы включить ссылку на строку в ваш расчёт:

Как изменить ссылку?

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

Как сделать перекрестную ссылку?

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

Чтобы сослаться на данные, находящиеся на другом листе, введите имя этого целевого листа с восклицательным знаком (!) перед адресом ячейки или диапазона.

Например, вот как вы можете создать ссылку на адрес A1 на листе Лист2 в той же книге Excel:

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

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

  1. Начните ввод в ячейку. Запишите знак «=».
  2. Щелкните вкладку листа, на которую хотите сослаться, затем выберите ячейку или диапазон ячеек на этом листе.
  3. Завершите ввод и нажмите Enter.

Как сослаться на другую книгу?

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

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

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

Итак, мы научились создавать простейшие ссылки. Теперь рассмотрим, какими они бывают.

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

Относительная ссылка на ячейку.

Относительная ссылка является самой простой и включает координаты строки и столбца, например А1 или А1:D10. По умолчанию все адреса ячеек в Экселе являются относительными.

Это простейшее выражение сообщает программе, что нужно показать значение, которое записано в первой колонке (A) и второй строке (2). Используя скриншот чуть ниже, если бы эта формула была помещена в ячейку D1, она отобразила бы число «8», поскольку это значение находится по адресу A2.

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

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

Например, чтобы сложить числа в A2 и B2, вы вводите это в C2: =A2+B2. При копировании из строки 2 в строку 3 выражение изменится на = A3+B3.

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

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

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

Вместо того, чтобы вводить формулу для всех ячеек одну за другой, вы можете просто скопировать ячейку D2 и вставить ее во все остальные ячейки (D3: D8). Когда вы это сделаете, вы заметите, что адрес автоматически настраивается, чтобы ссылаться на соответствующую строку. Например, формула в ячейке D3 становится B3*C3, а в D4 теперь записано: B4*C4.

Абсолютная ссылка на ячейку.

Абсолютная ссылка в Excel имеет знак доллара ($) в координатах строк или столбцов, например $A$1 или $A$1:$B$20.

Символ доллара, добавленный перед любой из координат, делает адрес абсолютным (т. е. предотвращает изменение номера строки и столбца).

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

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

Например, чтобы умножить числа в столбце B на величину скидки из F2, вы вводите следующую формулу в строке 2, а затем копируете её вниз, перетаскивая маркер заполнения:

Относительная ссылка (B2) будет изменяться в зависимости от относительного положения строки, в которую она копируется, в то время как абсолютная ($F$2) всегда будет зафиксирована на одном и том же адресе:

Конечно, можно в ваше выражение жёстко вбить 10% скидки, и этим решить проблему при копировании. Но если впоследствии вам понадобится изменить процент скидки, то придется искать и корректировать все формулы. И обязательно какую-то случайно пропустите. Поэтому принято подобные константы записывать отдельно и использовать абсолютные ссылки на них.

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

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

Смешанная ссылка.

Смешанные ссылки немного сложнее, чем абсолютные и относительные.

Может быть два типа смешанных ссылок:

  • Строка блокируется, а столбец изменяется при копировании.
  • Столбец фиксируется, а строка изменяется.

Смешанная ссылкасодержит одну относительную и одну абсолютную координату, например $A1 или A$1.

Как вы помните, абсолютная ссылка содержит 2 знака доллара ($), которые фиксируют как столбец, так и строку. В смешанной только одна координата является фиксированной (абсолютной), а другая (относительная) будет изменяться в зависимости от нового расположения:

  • Абсолютный столбец и относительная строка, например $A1. Когда выражение с этим типом ссылки копируется в другие ячейки, знак $ перед буквой столбца фиксирует обращение строго на указанный столбец, чтобы оно никогда не изменялось. Относительная ссылка на строку без знака доллара будет меняться в зависимости от строки, в которую копируется формула.
  • Относительный столбец и абсолютная строка, например A$1. Здесь адресация на строку будет зафиксирована, а на столбец — поменяется.

Может быть много ситуаций, когда нужно фиксировать только одну координату: либо столбец, либо строку.

Например, чтобы умножить колонку с ценами (столбец В) на 3 разных значения наценки (C2, D2 и E2), вы поместите следующую формулу в C3, а затем скопируете ее вправо и затем вниз:

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

В первом множителе мы зафиксировали столбец. Поэтому при копировании вправо по строке адрес $B3 не изменится: ведь строка по-прежнему третья.

А вот во втором множителе знак доллара мы поставили перед номером строки. Поэтому при копировании формулы в D3 координаты столбца изменятся и вместо C$2 мы получим D$2. В результате в D3 получим:

А когда будем копировать вниз, всё будет наоборот: $B3 изменится на $B4, $B5 и т.д. А вот D$2 не изменится, так как «заморожена» строка. В результате в С4 получим:

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

И если ваши наценки вдруг изменятся, просто поменяйте числа в C2:E2, и проблема будет решена почти мгновенно.

Как изменить ссылку с относительной на абсолютную (или смешанную)?

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

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

Неоднократно нажимая F4 , вы будете переключать их в следующем порядке:

Если вы выбрали относительную ссылку без знака $, например A1, последовательно нажимая F4, вы будете переключаться между абсолютной ссылкой с двумя знаками доллара $A$1, абсолютной строкой A$1, абсолютным столбцом $A1, а затем вновь вернёмся к A1.

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

Имя как разновидность абсолютной ссылки.

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

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

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

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

Формула же при этом становится более понятной и читаемой.

Ссылка на столбец.

Как и на отдельные ячейки, ссылка на весь столбец может быть абсолютной и относительной, например:

  • Абсолютная ссылка на столбец – $A:$A
  • Относительная – A:A

Когда вы используете знак доллара ($) в абсолютной ссылке на столбец, его адрес не изменится при копировании в другое расположение.

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

А теперь давайте посмотрим это на примере.

Предположим, у вас есть некоторые числа в колонке B, и вы хотите узнать их общее и среднее значение. Проблема в том, что новые данные добавляются в таблицу каждую неделю, поэтому писать обычную формулу СУММ() или СРЗНАЧ() для фиксированного диапазона ячеек – не лучший вариант. Вместо этого вы можете ссылаться на весь столбец B:

=СУММ($D:$D)— используйте знак доллара ($), чтобы создать абсолютную ссылку на весь столбец, которая привязывает формулу к столбцу B.

=СУММ(D:D)— напишите формулу без $, чтобы сделать относительную ссылку на весь столбец, которая будет изменяться при копировании.

Совет. При написании формулы щелкните мышкой на букве заголовка (D, например), чтобы добавить ссылку сразу на весь столбец. Как и в случае ячейками, программа по умолчанию вставляет относительную ссылку (без знака $):

Примечание. При использовании ссылки на весь столбец никогда не вводите формулу в том же столбце, на который ссылаетесь. Например, может показаться хорошей идеей ввести =СУММ(D:D) в одну из самых нижних пустых ячеек в этом же столбце D, чтобы получить итоговый результат в конце таблицы. Не делайте этого! Это создаст так называемую циклическую ссылку, и вы получите результат 0.

Ссылка на строку.

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

  • Абсолютная ссылка на строку – $1:$1
  • Относительная – 1:1

Пример 2. Ссылка на всю строку (абсолютная и относительная)

Если данные в вашем листе расположены горизонтально, а не по вертикали, вы можете ссылаться на всю строку. Например, вот как мы можем рассчитать среднюю цену в строке 2:

=СРЗНАЧ($3:$3) – абсолютная ссылка на всю строку зафиксирована с помощью знака доллара ($).

=СРЗНАЧ(3:3) – относительная ссылка на строку изменится при копировании вниз.

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

Для бананов (B13) расчет уже будет такой: СРЗНАЧ(4:4). Как видите, номер строки автоматически изменился.

Ссылка на столбец, исключая первые несколько строк.

Это очень актуальная проблема, потому что довольно часто первые несколько строк на листе содержат некоторые вводные предложения, шапку даблицы или пояснительную информацию, и вы не хотите включать их в свои вычисления. К сожалению, Excel не допускает ссылок типа D3:D, которые включали бы все данные в столбце D, только начиная со строки 3. Если вы попытаетесь добавить такую ​​конструкцию, ваша формула, скорее всего, вернет ошибку #ИМЯ?.

Вместо этого вы можете указать максимальную строку, чтобы ваша ссылка включала все возможные адреса в данном столбце. В Excel с 2019 по 2007 максимум составляет 1 048 576 строк и 16 384 столбца. Более ранние версии программы имеют максимум 65 536 строк и 256 столбцов.

Итак, чтобы найти сумму продаж в приведенной ниже таблице (колонка «Стоимость»), можно использовать выражение:

Как вариант, можно вычесть из общей суммы те данные, которые хотите исключить:

Но первый вариант предпочтительнее, так как СУММ(D:D) выполняется дольше и требует больше вычислительных ресурсов, чем СУММ(D3:D1048576).

Смешанная ссылка на весь столбец.

Как я упоминал ранее, вы также можете создать смешанную ссылку на весь столбец или целую строку:

  • Смешанная на столбец, например $A:A
  • Смешанная на строку, например $1:1

Теперь посмотрим, что произойдет, если вы скопируете формулу с такими адресами в другие ячейки. Предположим, вы вводите формулу =СУММ($B:B) в какую-то клетку, в этом примере F3. Когда вы копируете формулу вправо (в G3), она меняется на, =СУММ($B:C), потому что первая B имеет знак $ и остается неподвижной, а вторая B – обычная и поэтому меняется.

В результате Эксель сложит все числа в столбцах B и C. Ну и, двигаясь далее вправо, далее можно найти сумму уже трёх колонок.

Предупреждение! Не используйте на листе слишком много ссылок на целые столбцы или строки, поскольку так вы можете существенно замедлить работу Excel.

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

Благодарю вас за чтение и надеюсь увидеть вас в нашем блоге!

Как удалить сразу несколько гиперссылок — В этой короткой статье я покажу вам, как можно быстро удалить сразу все нежелательные гиперссылки с рабочего листа Excel и предотвратить их появление в будущем. Решение работает во всех версиях Excel,…
Как использовать функцию ГИПЕРССЫЛКА — В статье объясняются основы функции ГИПЕРССЫЛКА в Excel и приводятся несколько советов и примеров формул для ее наиболее эффективного использования. Существует множество способов создать гиперссылку в Excel. Чтобы сделать ссылку на…
Гиперссылка в Excel: как сделать, изменить, удалить — В статье разъясняется, как сделать гиперссылку в Excel, используя 3 разных метода. Вы узнаете, как вставлять, изменять и удалять гиперссылки на рабочих листах, а также исправлять неработающие ссылки. Гиперссылки широко используются…
Как использовать функцию ДВССЫЛ – примеры формул — В этой статье объясняется синтаксис функции ДВССЫЛ, основные способы ее использования и приводится ряд примеров формул, демонстрирующих использование ДВССЫЛ в Excel. В Microsoft Excel существует множество функций, некоторые из которых…
Как сделать диаграмму Ганта — Думаю, каждый пользователь Excel знает, что такое диаграмма и как ее создать. Однако один вид графиков остается достаточно сложным для многих — это диаграмма Ганта. В этом кратком руководстве я постараюсь показать…
Как сделать автозаполнение в Excel — В этой статье рассматривается функция автозаполнения Excel. Вы узнаете, как заполнять ряды чисел, дат и других данных, создавать и использовать настраиваемые списки в Excel. Эта статья также позволяет вам убедиться, что вы…
Проверка данных в Excel: как сделать, использовать и убрать — Мы рассмотрим, как выполнять проверку данных в Excel: создавать правила проверки для чисел, дат или текстовых значений, создавать списки проверки данных, копировать проверку данных в другие ячейки, находить недопустимые записи,…
Быстрое удаление пустых столбцов в Excel — В этом руководстве вы узнаете, как можно легко удалить пустые столбцы в Excel с помощью макроса, формулы и даже простым нажатием кнопки. Как бы банально это ни звучало, удаление пустых…
Как полностью или частично зафиксировать ячейку в формуле — При написании формулы Excel знак $ в ссылке на ячейку сбивает с толку многих пользователей. Но объяснение очень простое: это всего лишь способ ее зафиксировать. Знак доллара в данном случае служит только…
Чем отличается абсолютная, относительная и смешанная адресация — Важность ссылки на ячейки Excel трудно переоценить. Ссылка включает в себя адрес, из которого вы хотите получить информацию. При этом используются два основных вида адресации – абсолютная и относительная. Они…

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *