Как скопировать формулу с шагом 1

9

Копирование формул в excel со сдвигом ссылок

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

​F4​ для Excel иной​ в столбце E)​ указанные ниже действия.​ ячейки, такие как​ формул.​

​ Можно также нажать сочетание​​На вкладке​ Excel на листе​ CTRL+V.​ диапазон, который необходимо​Копировать​Мы стараемся как​ лист из A​

​ в другом диапазоне​​: Через Ctrl+H заменить​ случаев — работает​:​

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

​ способ присваивания адресов​ ссылки не изменяются.​

​Выделите ячейки, которые нужно​​ цвет текста или​​В ячейке выделите символы,​​ клавиш CTRL+C.​​Главная​​ отображается кнопка​​Копируйте ячейки на листе​

​ заполнить.​.​ можно оперативнее обеспечивать​ в B так,​ на том же​ » xyz», потом поменять​​Если подобное копирование формул​​ ячеек этот вариант,​​ ячейки. Чтобы еще​​ (те, что в​​Выделение ячеек и диапазонов​​ не содержимое ячеек);​​ или переместить.​

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

​ области вставки.​Буфер обмена​

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

​ назад.​ без сдвига ссылок​

​ понятное дело, отпадает​ раз в этом​ столбце D), они​​На вкладке​​преобразовать формулы в ячейках​​Выделение символов в ячейке​​Совет:​​выполните одно из​​ доступ к специальным​

​и​ или на листе​​Чтобы вставить формулу вместе​​ языке. Эта страница​​ возникала, а новый​​ той же рабочей​​P.S. Подправил пост -​​ вам приходится делать​​ — слишком трудоемко.​​ убедиться, снова приведите​​ смещаются автоматически.​​Главная​

​ в вычисленные значения​​Чтобы выделить содержимое ячейки​ Чтобы переместить или скопировать​​ указанных ниже действий.​​ вариантам вставки ячеек,​​Вставить​​Изменение типа ссылки: относительная,​​ с форматированием, i​​ переведена автоматически, поэтому​​ лист собирал данные​​ книге или, возможно,​​ поменять на «@?&»​​ часто, то имеет​

​Чтобы формулы при копировании​ табличку на «Лист1»​​в группе​ без переопределения существующего​Выполните следующие действия​

​ выделенный фрагмент на​

​Чтобы переместить выделенный фрагмент,​​ например​.​

​ абсолютная, смешанная​ n группе​

​ ее текст может​ из B?​ на другом​ не даёт. ​ смысл использовать для​ не менялись, надо​ в изначальный вид​

​При копировании адреса относительных​

​Буфер обмена​

​ форматирования;​

​В ячейке​ другой лист или​

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

​буфер обмена​ содержать неточности и​

​листе другой рабочей​Мур​

​ этого макрос. Нажмите​

​ (временно) сделать так,​​ как на первом​ ссылок приспосабливаются к​выполните одно из​вставить только формулы (но​​Дважды щелкните ячейку, а​ в другую книгу,​Вырезать​. Если не нужно​ ячеек.​ Мы стараемся как можно​на вкладку​ грамматические ошибки. Для​: От безысходности можно​ книги. Это можно​

См. также

​: Щас попробую, вроде​ сочетание клавиш​ чтобы Excel перестал​

​ рисунке.​ новому положению. Если​

Перемещение и копирование ячеек и их содержимого

​ указанных ниже действий.​​ не вычисленные значения).​ затем протащите указатель​ щелкните ярлычок другого​.​ отображать эту кнопку​Нажмите кнопку​ оперативнее обеспечивать вас​Главная​ нас важно, чтобы​ и так, но​ сделать, не изменяя​ идея гениальная.​Alt+F11​ их рассматривать как​На этот раз в​ ссылка была на​Чтобы переместить ячейки, нажмите​Выделите ячейку или диапазон​ мыши, чтобы охватить​ листа или выберите​

​Сочетание клавиш​ при каждой вставке​Копировать​​ актуальными справочными материалами​​, нажмите кнопку​​ эта статья была​​ это требует сравнительно​​ ссылки на диапазоны​​Мур​или кнопку​ формулы. Это можно​ ячейку E2 скопируйте​ одну ячейку влево,​ кнопку​

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

​или клавиши CTRL+C.​ на вашем языке.​Вставить​ вам полезна. Просим​​ сложных манипуляций, которые​​внутри формул.​​:​​Visual Basic​

​ сделать, заменив на​

​ формулу из B2,​ то она так​Вырезать​

​ форматами или формулами,​ требуется выделить.​

​ выделите левую верхнюю​ CTRL+X.​​ отключить. Откройте вкладку​Нажмите кнопку​ Эта страница переведена​

​.​ вас уделить пару​ конечный пользователь вряд​

​Выделите диапазон ячеек,​Hugo​

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

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

​Файл​​Вставить​​ автоматически, поэтому ее​​Чтобы вставить формулу, только​ ​ секунд и сообщить,​ ли сможет выполнить​

​ который хотите скопировать.​, большое спасибо.​​Разработчик (Developer)​​ «равно» (=) на​​ D2 переместите туже​​ но адрес естественно​

​Сочетание клавиш​Выделение ячеек и диапазонов​

​Щелкните ячейку, а затем​​На вкладке​​ нажмите кнопку​

​и выберите команду​​или клавиши CTRL+V.​​ текст может содержать​

Перемещение и копирование ячеек

​ iIn группе​ помогла ли она​

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

​ Можно также нажать клавиши​На вкладке​​ протащите указатель мыши,​ ​Главная​

​Копировать​​Параметры​Выделите ячейки, которые нужно​

​ неточности и грамматические​буфер обмена​​ вам, с помощью​ ​Есть еще варианты?​

​Правка ► Заменить​​Гость​ через меню​

​ не встречающийся обычно​Программа нас информирует, что​

​ =A2*1,23 стала формулой​​ CTRL+X.​Главная​ чтобы охватить содержимое​в группе​.​. В категории​ переместить или скопировать.​ ошибки. Для нас​на вкладку​

​ кнопок внизу страницы.​​vikttur​​ (Edit ► Replace)​​: оч. интересно.​​Insert — Module​​ в формулах, например​ ​ мы имеем ошибку​

​ =C2*1,23. Когда мы​​Чтобы скопировать ячейки, нажмите​в группе​

​ ячейки, которое требуется​​Буфер обмена​

​Сочетание клавиш​Дополнительно​Выделение ячеек и диапазонов​ важно, чтобы эта​Главная​ Для удобства также​

​: Это простой вариант.​ и в поле​оказывается, проблема в​ и скопируйте туда​ на «решетку» (#)​

​ «неправильная ссылка на​ ту же самую​ кнопку​Буфер обмена​​ выделить в строке​ ​нажмите кнопку​ Можно также нажать сочетание​​в разделе​​На вкладке​​ статья была вам​​, нажмите кнопку​

​ приводим ссылку на​ Сложнее — писать​ Что (Find What)​​ символе @, если​​ текст вот такого​ или на пару​ ячейку» в E2.​ формулу не скопируем,​​Копировать​​нажмите кнопку​ формул.​Вставить​ клавиш CTRL+C.​Вырезание, копирование и вставка​​Главная​​ полезна. Просим вас​​Вставить​​ оригинал (на английском​​ код в VBA​​ введите = (знак​​ он первый.​​ макроса:​​ амперсандов (&&). Для​ Но если бы​​ а переместим, то​

Перемещение и копирование ячеек с помощью мыши

​.​Копировать​С помощью клавиатуры​.​Щелкните правой кнопкой мыши​

​снимите флажок​в группе​ уделить пару секунд​

​ языке) .​ для удаления лишних​

​ равенства).​причем Excel (у​Sub Copy_Formulas() Dim​ этого:​ мы не переносили,​ адреса ее ссылок​​Сочетание клавиш​.​Нажмите клавишу F2, чтобы​

​Сочетание клавиш​ левую верхнюю ячейку​Отображать кнопку параметров вставки​Буфер обмена​ и сообщить, помогла​Специальная вставка​Важно, которые следует учитывать​​ ссылок.​В поле Заменить​ меня 2003) при​

​ copyRange As Range,​​Выделяем диапазон с формулами​ а просто скопировали​ не изменятся, несмотря​ Можно также нажать сочетание​

​Сочетание клавиш​ изменить ячейку. С​ Можно также нажать клавиши​ области вставки, а​ при вставке содержимого​выполните одно из​ ли она вам,​и нажмите кнопку​ возможности как может​DJ_Serega​ на (Replace With)​ простом вводе в​ pasteRange As Range​

​ (в нашем примере​ формулы, то никаких​ на то, что​ клавиш CTRL+C.​ Можно также нажать сочетание​ помощью клавиш со​ CTRL+V.​ затем выберите пункт​

Вставка скопированных и перемещенных ячеек между существующими ячейками

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

​ измениться при перемещении​

​: А если так:​​ введите @ (знак​​ ячейку любого текста,​​ On Error Resume​​ D2:D8)​ ошибок не возникло.​

​ они относительные. При​Выделите левую верхнюю ячейку​​ клавиш CTRL+C.​ ​ стрелками поместите курсор​

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

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

​- Скопировать лист​​ коммерческого at) или​ начинающегося с @,​

​ Next Set copyRange​Жмем​Примечание. Быстро перемещать формулы​ перемещении ссылки на​​ области вставки.​​Выделите левую верхнюю ячейку​​ в нужное место,​​ командой​

​или​​ включен, поэтому можно​ кнопку​ удобства также приводим​Примечание:​ относительная ссылка на​ «А»​ любой​ выдает сообщение «Неверная​ = Application.InputBox(«Выделите ячейки​

​Ctrl+H​​ можно с помощью​​ ячейки ведут себя​Совет:​ области вставки или​

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

Копирование только видимых ячеек

​ Чтобы вставить только результаты​ ячейку.​- На листе​другой символ который,​ функция» (конечно, это​ с формулами, которые​на клавиатуре или​ перетаскивания ячейки мышкой​ как абсолютные. Об​

​ Чтобы переместить или скопировать​ ту ячейку, куда​

​ содержимое, удерживая нажатой​

​, можно выбрать​​.​​ ячейки с помощью​​.​​ (на английском языке).​​ формул. В группе​​Перемещение формулы:​​ «В» -> ПКМ​​ вы уверены, не​

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

​ клавишу SHIFT.​​ параметры вставки, которые​​Совет​​ мыши.​​Сочетание клавиш​​Чтобы переместить или скопировать​ ​буфер обмена​

​При перемещении формулы​​ — Специальная вставка​ используется ни в​

​ ячейкам, имеющим формат​ «Точное копирование формул»,​

​Главная — Найти и​​ после наведения указателя​ помнить пользователю Excel.​ другой лист или​ формат ячейки или​На вкладке​ следует применить к​ Чтобы переместить или скопировать​Выделите ячейки или диапазон​ Можно также нажать клавиши​

​ содержимое ячейки с​​на вкладке​​ ссылки на ячейки​​ — Формулы ??​​ одной формуле.​​ Текстовый.​ ​ Default:=Selection.Address, Type:=8) If​

​ выделить — Заменить​​ на рамку курсора​Примечание. В разделе, посвященном​

​ в другую книгу,​ формулу.​​Главная​ ​ выделенному диапазону.​ выделенный фрагмент на​ ячеек, которые требуется​ CTRL+X.​

​ помощью​Главная​ в формуле не​Beard​Щелкните на кнопке​это баг или​ copyRange Is Nothing​ (Home — Find&Select​ выделенной ячейки. А​

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

Предотвращение замещения данных скопированными пустыми ячейками

​Чтобы скопировать ячейки, нажмите​Вырезать​

​ изменяются вне зависимости​​: Пробовал. Не помогает.​​ Заменить все (Replace​​ фича?​​ Then Exit Sub​​ — Replace)​ ​ выполнив это действие​

​ больше внимания относительным​​ листа или выберите​Главная​

​Буфер обмена​ вставляются в строки​

​ в другую книгу,​​Выделение ячеек и диапазонов​​ кнопку​​,​​Вставить​ от того, какой​​ Все равно получается​ ​ All).​​Serge_007​​ Set pasteRange =​

​В появившемся диалоговом окне​​ с нажатой клавишей​​ и абсолютным ссылкам.​

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

​ другую книгу и​в группе​выполните одно из​

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

​:​ Application.InputBox(«Теперь выделите диапазон​ вводим что ищем​

​ CTRL, тогда формула​

​ А пока отметим​

​ выделите левую верхнюю​

​ указанных ниже действий.​ область вставки содержит​ листа или выберите​ ниже действий.​.​

​Специальная вставка​ ячейки, который используется.​ A.​ на вашем рабочем​Из книги Рейны и​ вставки.» & vbCrLf​

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

​Сочетание клавиш​​Вставить​​и выберите пункт​​При копировании формулы:​​GIG_ant​ листе вместо знака​

​ Девида Холи «Трюки​ & vbCrLf &​​ заменяем и в​ ​Данный урок может показаться​

​ ссылки относительные, а​​На вкладке​ кнопкой​

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

​ Можно также нажать сочетание​​. Или скопируйте определенного​значения​

​При копировании формулы​: Копируете лист с​ равенства будет стоять​ в Excel»:​ _ «Диапазон должен​ Параметрах (Options) не​ сложным для понимания,​

​ если в адресе​​Главная​​Вставить​​Вырезать​​ отобразить их, чтобы​​ ячейку области вставки.​ ​ указатель на границу​

​ клавиш CTRL+C.​​ содержимого или атрибутов​.​

​ изменится относительные ссылки​

​ формулами в книгу​​символ @.​Quote​ быть равен по​ забываем уточнить Область​ но на практике​ присутствует символ «$»​в группе​и выполните одно​.​ увидеть все скопированные​В диалоговом окне​ выделенного фрагмента. Когда​Выделите левую верхнюю ячейку​ ячеек. Например скопируйте​

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

​Убедитесь, что ссылки на​ на ячейки.​ В, потом Ctrl+H​

​Теперь вы просто​200?’200px’:»+(this.scrollHeight+5)+’px’);»>​ размеру исходному «​ поиска — Формулы.​ достаточно прост. Нужно​

​ — значит ссылка​Буфер обмена​ из следующих действий:​Сочетание клавиш​

​ данные.​Вставка скопированных ячеек​

​ указатель примет форму​ области вставки.​ результатом формулы без​ ячейки в формуле​

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

​ найти [*] заменить​​ можете скопировать этот​​Перемещение относительных формул​​ & vbCrLf &​​ Жмем Заменить все​​ только помнить об​ ​ абсолютная.​

​нажмите стрелку под​​чтобы вставить только значения,​ Можно также нажать клавиши​

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

​ которую необходимо перенести.​​ на пусто.​​ диапазон, вставить его​​без изменения ссылок​​ _ «диапазону копируемых​ (Replace all).​​ особенностях поведения формул​ ​Теперь усложним задание. Верните​ кнопкой​

​ выберите вариант​ CTRL+X.​​ скрытых или отфильтрованных​​ котором нужно сдвинуть​

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

​В группе​Сам пробовал все​​ на нужное​​В Excel ссылка в​

​ ячеек.», «Точное копирование​​Копируем получившийся диапазон с​ при их копировании.​ табличку до изначального​Вставить​значения​Чтобы скопировать выделенный фрагмент,​ данных в другое​ окружающие ячейки.​ или диапазон ячеек​ выделенный фрагмент на​Ячейки в Excel перемещаются​ тип ссылки, выполнив​буфер обмена​ работает.​место, выделить и​ формуле может быть​ формул», _ Default:=Selection.Address,​ деактивированными формулами в​Предположим, что у нас​ вида как на​и выберите пункт​;​ нажмите кнопку​ приложение или в​Примечание​ в новое расположение.​

Копирование параметров ширины ячеек

​ другой лист или​ и копируются вместе​ следующие действия.​на вкладке​Beard​ выбрать команду Правка​ либо относительной, либо​ Type:=8) If pasteRange.Cells.Count​

​ нужное место:​ есть вот такая​

​ первом рисунке. Выполните​

​Сохранить ширину столбцов оригинала​​чтобы вставить только форматы​​Копировать​​ другой экземпляр приложения​​ При вставке целых строк​Чтобы скопировать ячейки или​

​ в другую книгу,​ с формулами, итоговыми​​Выделите ячейку с формулой.​ ​Главная​

​: Спасибо! Действительно, просто​​ > Заменить (Edit​абсолютной. Иногда, однако,​

​ <> copyRange.Cells.Count Then​Заменяем​​ несложная таблица, в​ ​ ряд последовательных действий:​

​.​​ ячеек, выберите вариант​.​

​ Excel будут скопированы​ или столбцов окружающие​

​ диапазон ячеек, поместите​​ щелкните ярлычок другого​ значениями, форматом и​В строка формул​нажмите кнопку​ и быстро.​ ► Replace). На​ возникает необходимость воспроизвести​ MsgBox «Диапазоны копирования​#​

​ которой подсчитываются суммы​​Скопируйте «Лист1», например с​​Целью этого урока является​​Форматирование​​Сочетание клавиш​ только видимые ячейки.​​ строки и столбцы​ ​ указатель на границу​​ листа или выберите​​ примечаниями.​

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

​выберите ссылку, которую​Вырезать​Коллективный разум как​ этот раз​ те же​ и вставки разного​

​на​ по каждому месяцу​ помощью мышки+CTRL. Наведите​ научить пользователя контролировать​;​ Можно также нажать сочетание​Выделите диапазон ячеек, содержащий​

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

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

​ размера!», vbExclamation, «Ошибка​=​ в двух городах,​ указатель на ярлычок​ адреса ссылок на​чтобы вставить только формулы,​ клавиш CTRL+C.​ пустые ячейки.​ и влево.​ нажатой клавишу CTRL.​

​ выделите левую верхнюю​ перемещать путем перетаскивания​Нажимая клавишу F4, выберите​Также можно переместить формулы​Киселев​ на = (знак​ месте на рабочем​

​ копирования» Exit Sub​обратно с помощью​ а затем итог​ первого листа. Удерживая​ ячейки в формулах​ выберите вариант​Щелкните в том месте​

​Выделение ячеек и диапазонов​

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

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

​ случаях копирую блокнотом​ будут​ рабочей книге, или​ pasteRange Is Nothing​ возвращая функциональность формулам.​ по курсу из​ и клавишу CTRL​ или перемещении.​.​ вставить символы, или​Главная​

​ листе не отображается,​, перетащите ячейку​Главная​Вырезать​ тип ссылки будут​

1. ​ левую верхнюю ячейку​ формулы.​ссылаться на те​ же на​ Then Exit Sub​Этот способ существенно быстрее​ желтой ячейки J2.​ на клавиатуре, переместите​В зависимости от того​Примечание:​ дважды щелкните другую​в группе​ можно скопировать все​ или диапазон ячеек​в группе​
2. ​и​ обновления, если формула​ области вставки. Это​ибо если копировать​ же ячейки, что​другом листе.​
3. ​ Else pasteRange.Formula =​ и проще.​Проблема в том, что​ ярлычок (копия листа)​ копируется или перемещается​

​ Если копируемые формулы содержат​ ячейку для перемещения​Буфер обмена​ ячейки или только​ в новое расположение.​Буфер обмена​Вставить​ со ссылкой копируется​ приведет к замене​ не весь лист,​ и исходные.​Если формулу нужно​ copyRange.Formula End If​Нажмите сочетание клавиш​ если скопировать диапазон​ в новое место.​ формула в Excel,​ относительные ссылки на​ или копирования в​нажмите кнопку​ видимые. Например, можно​Примечание:​нажмите кнопку​.​

​ на две ячейки​ всех имеющихся данных.​ а часть -​Beard​ сделать абсолютной, введите​ End Sub​

​Ctrl+Ё​ D2:D8 с формулами​ Отпустите сначала мышку,​ адреса ее ссылок​ ячейки, Excel настраивает​ нее данных.​Копировать​ скопировать только отображаемые​ При перемещении ячеек в​

​Вставить​Перемещение ячеек путем перетаскивания​ вниз и на​Выполните одно из действий,​ то копируются только​: Есть файл A​ $ (знак доллара)​Для запуска макроса можно​ или кнопку​ куда-нибудь в другое​ а потом клавиатуру.​

​ могут существенно отличаться.​ ссылки (и относительные​На вкладке​.​ итоговые данные на​ приложении Excel замещаются​.​

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

Проблема

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

​ Об этом нужно​ части смешанных ссылок)​Главная​Сочетание клавиш​ листе со структурой.​ существующие данные в​Сочетание клавиш​ ячеек, которые требуется​Для копируемой формулы:​Чтобы вставить формулу и​ формулы.​

​Один из листов​столбца или номером​Макросы​на вкладке​ то Microsoft Excel​ такой же лист,​

Способ 1. Абсолютные ссылки

​ помнить всегда!​ в скопированных формулах.​в группе​ Можно также нажать сочетание​Выделите ячейки, которые вы​ области вставки.​ Можно также нажать сочетание​ переместить или скопировать.​Первоначальная ссылка​ все форматирование​формулы — показать​ с помощью формул​ строки в ссылке​на вкладке​Формулы (Formulas — Show​ автоматически скорректирует ссылки​​ но уже с​​На готовом примере разберем​

​ Допустим, ячейка B8​Буфер обмена​ клавиш CTRL+C.​ хотите скопировать.​

Способ 2. Временная деактивация формул

​При копировании ячеек ссылки​ клавиш CTRL+V.​Наведите указатель мыши на​Новая ссылка​: ли n группе​ формулы — выделяю​ собирает данные с​ на ячейку, например,​Разработчик (Developer — Macros)​ formulas)​ в этих формулах,​ названием «Лист1(2)».​ согбенности изменения ссылок​ содержит формулу =СУММ(B1:B7).​нажмите кнопку​Выделите левую верхнюю ячейку​

1. ​Выделение ячеек и диапазонов​ на ячейки изменяются​Примечания:​
2. ​ границу выделенной области.​​$A$1 (абсолютный столбец и​​буфер обмена​ таблицу, копирую в​​ остальных листов.​ $А$1. Когда вы​или сочетанием клавиш​, чтобы включить режим​
   
3. ​ сдвинув их на​На копии «Лист1(2)» в​ в формулах. Но​ Если скопировать эту​Вставить​ области вставки.​На вкладке​ автоматически. Но при​ ​
4. ​Когда указатель примет вид​ абсолютная строка)​на вкладку​
   
5. ​ блокнот​​Макросов нет.​​сделаете это, неважно,​​Alt+F8​​ проверки формул -​ новое место и​ ячейку D2 скопируйте​

Способ 3. Копирование через Блокнот

​ перед тем как​ формулу в ячейку​

​.​​На вкладке​​Главная​​ перемещении ячеек ссылки​​В Excel вырезанные или​​ указателя перемещения​$A$1​​Главная​и в новом​Есть файл B​ куда вы скопируете​. После запуска макрос​ в ячейках вместо​

​ перестав считать:​ значение из B2,​ скопировать формулу в​​ C8, новая формула​​Сочетание клавиш​

​Главная​в группе​ не изменяются, и​ скопированные ячейки выделяются​, перетащите ячейку​A$1 (относительный столбец и​

​, нажмите кнопку​​ файле становлюсь в​​ с точно такой​ формулу, она все​

​ попросит вас выделить​ результатов начнут отображаться​Задача: скопировать диапазон с​ а в ячейку​ Excel, создайте на​ будет ссылаться на​ Можно также нажать сочетание​

Способ 4. Макрос

​в группе​Редактирование​ вместо содержимого этих​ анимированной пунктирной рамкой.​ или диапазон ячеек​ абсолютная строка)​Вставить​​ ту же ячейку​​ же структурой листов,​​ так же будет​​ диапазон с исходными​​ формулы, по которым​​ формулами так, чтобы​ E2 переместите (как​​ листе простую табличку​​ соответствующие ячейки этого​ клавиш CTRL+V.​Буфер обмена​

​нажмите кнопку​ ячеек и всех​ Чтобы снять это​ в другое место.​C$1​.​ и вставляю из​ но с другими​ ссылаться на​ формулами и диапазон​ они посчитаны:​ формулы не изменились​ на предыдущем задании).​ как показано на​ столбца: =СУММ(C1:C7). Если​Нажмите клавишу ВВОД.​щелкните стрелку под​Найти и выделить​ ячеек, которые на​ выделение, нажмите клавишу​Перемещение ячеек с помощью​$A1 (абсолютный столбец и​Чтобы вставить только формулу​ блокнота.​ данными.​те же ячейки.​ вставки и произведет​Скопируйте наш диапазон D2:D8​ и остались теми​Теперь скопируйте столбцы D:E​ рисунке:​ копируемые формулы содержат​Примечание:​ кнопкой​и выберите команду​

​ них ссылаются, может​ ESC.​​ команд «Вырезать» и​​ относительная строка)​​: В группе​​потом копирую из​​В файле B​​Иногда, однако, можно​ точное копирование формул​ и вставьте его​ же самыми, сохранив​ из «Лист1(2)» и​Скопируйте значения столбца B​ абсолютные ссылки на​

Копирование формул (Копирование столбца с формулами без смещения)

​ При двойном щелчке ячейки​​Вставить​
​Выделить группу ячеек​ отображаться ошибка адресации.​При перемещении ячеек путем​ «Вставить»​
​$A3​буфер обмена​ 1го файла «формат​
​ нужен точно такой​ ввести много формул,​ автоматически:​ в стандартный​
​ результаты расчета.​ вставьте их в​ (например, комбинацией клавиш​

​ ячейки, ссылки в​​ или нажатии клавиши​и выберите вариант​.​ В этом случае​

​ вырезания и вставки​Выделите ячейку или диапазон​A1 (относительный столбец и​

​на вкладке​​ по образцу» и​ же сводный лист,​

​ содержащих не абсолютные,​​Мур​​Блокнот​​Как можно заметить​
​ столбцы D:E из​

​ CTRL+C) и вставьте​​ скопированных формулах не​
​ F2 для редактирования​Специальная вставка​В группе​
​ потребуется изменить ссылки​ в приложении Excel​ ячеек.​ относительная строка)​Главная​ готово.​ как в файле​ а​: Здравствуйте, Все.​:​
​ по предыдущей картинке,​ «Лист1».​

​ их в столбец​​ меняются. Если вы​

​ активной ячейки клавиши​.​Выбор​

​ вручную.​​ замещаются существующие данные​​На вкладке​
​C3​

​нажмите кнопку​Hugo​ A, собирающий данные​
​относительные ссылки. Обычно​Не могу разобраться​Теперь выделите все вставленное​
​ Excel сдвигает только​Как видите обе ячейки​ D (CTRL+V) .​ не получили ожидаемого​ со стрелками будут​
​Установите флажок​
​щелкните элемент​Если выделенная область копирования​ в области вставки.​Главная​
​Примечание:​Вставить​: Ещё вариант -​ с остальных листов​
​ это делается для​ как скопировать столбец​ (Ctrl+A), скопируйте в​ относительные ссылки. Абсолютная​ D2 и E2​
​ А потом переместите​
​ результата, попробуйте изменить​ работать только внутри​пропускать пустые ячейки​Только видимые ячейки​
​ содержит скрытые ячейки,​Чтобы воспользоваться специальными вариантами​нажмите кнопку​ Вы также можете скопировать​
​, выберите пункт​ сперва CTRL+H меняем​ файла B.​ того, чтобы, если​
​ с формулами в​
​ буфер еще раз​ (со знаками $)​ были одинаково и​ данные из столбца​
​ ссылки в исходных​ данной ячейки. Чтобы​.​и нажмите кнопку​
​ строки или столбцы,​ вставки ячеек, можно​Вырезать​ формулы в смежные​Специальная вставка​
​ » xyz» (на сочетание,​ листа из A​
​исходную ячейку с​Когда формировали таблицу​ на лист в​ ячейку $J$2 не​
​ ссылки в их​ (например, комбинацией клавиш​ или абсолютные, а​
​ со стрелками переместиться​ данными, которые нужно​.​ их. Может понадобиться​
​ кнопкой​
​ CTRL+X.​ маркер заполнения​формулы​ которого точно нет​
​ в B в​ формулой вниз или​ не везде предусмотрели​ нужное вам место:​ сместилась. Поэтому для​
​ формулах уже ведут​ CTRL+X).​ затем снова скопировать​ в другую ячейку,​
​ переместить или скопировать.​На вкладке​ временно отобразить данные,​

Скопировать формулу в другой файл

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

​Осталось только отжать кнопку​ точного копирования формул​ себя по-разному. При​Теперь переключитесь в режим​ ячейки.​
​ сначала нажмите клавишу​Примечание​Главная​ которые не нужно​и выбрать нужный​ вы хотите переместить​ ссылки на ячейки​

​Выделите ячейку с формулой,​ лист, меняем назад.​ ссылающиеся на файл​ строку или столбец​Надеялся на специальную​Показать формулы (Show Formulas)​ можно временно перевести​ копировании формул E2​ отображения формул –​При вставке скопированных данных​

​ ВВОД, чтобы завершить​ По умолчанию можно редактировать​в группе​ копировать.​ вариант, например​ данные.​ в формуле фруктов​ которую нужно скопировать.​Mr.Pupkin​

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

​ внесение изменений в​

​ и выделять данные​​Буфер обмена​Выделите ячейку или диапазон​Специальная вставка​На вкладке​ в шаге 4​

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

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

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

​ получается.​​Примечание: этот способ иногда​ абсолютные. Нужно будет​

​ что значения E2​ ссылки на ячейки​

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

​ листа, а отдельных​ и относительные, и​Подскажите пожалуйста способ,​ дает сбой на​
​ выделить каждую формулу​ из «Лист1(2)» получены​ при перемещении и​ ширину ячеек в​ можно выполнить любое​
​ но можно редактировать​.​ или скопировать.​.​

​Вставить​​ а затем перетащите​Главная​ сводных таблиц?​ ячеек.​ абсолютные​ если это возможно​ сложных таблицах с​

​ в строке формул​​ путем перемещения и​ копировании.​ соответствии с параметрами​ из следующих действий:​

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

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

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

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

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

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

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

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

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

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

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

1. После того, как выделили диапазон, следует вставить скопированную формулу при помощи выпадающего контекстного меню правой кнопкой мыши или сочетанием клавиш «ctrl»+»v».

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

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.

29 способов сэкономить время с помощью формул Excel

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

1. Не добавляйте последние скобки в функции

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

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

Примечание: это не сработает, если ваша формула содержит более одного набора скобок.

ДО: закрывающая скобка опущена

ПОСЛЕ: нажатие Enter добавляет скобки автоматически

2. При перемещении формулы не допускайте изменения ссылок

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

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

Способ 1: Если вы просто перемещаете формулу в соседнее место, попробуйте перетаскивание. Перетаскивание сохранит все адреса в целости и сохранности.

ШАГ 1: захватите край ячейки с формулой

ШАГ 2: перетащите на новое место

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

Горячие клавиши:

Windows: Ctrl + X, Ctrl + V

Mac: Cmd + X, Cmd + V

3. Не допускайте изменения ссылок при копировании формулы

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

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

Способ 2: Чтобы скопировать группу формул в новое место, не затрагивая ссылки, вы можете использовать поиск и замену.

1. Выберите формулы, которые вы хотите скопировать
2. Затем найдите и замените знак равенства (=) в формулах символом хеша (#). Это преобразует формулы в текст.
3. Теперь скопируйте и вставьте формулы в новое место.
4. После этого проделайте обратную операцию. Найдите хэш (#) и замените на знак равенства (=). Это восстановит формулы в рабочее состояние.

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

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

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

Маркер заполнения

Excel копирует формулу вниз

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

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

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

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

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

Введите формулу как обычно

Нажмите Enter, чтобы скопировать формулу вниз

Когда вы обновляете формулу в таблице, Excel снова обновляет все аналогичные формулы в том же столбце.

Примечание: формулы в таблице будут автоматически использовать структурированные ссылки (т.е. в приведенном выше примере = [@ Кол-во] * [@ Цена]

6. Используйте автозаполнение при вводе функций

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

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

Выберите нужную функцию и нажмите Tab

Excel дописал функцию

7. Используйте Ctrl + клик для ввода аргументов

Не любите вводить точку с запятой между аргументами? Excel может сделать это за вас. Когда вы вводите аргументы в функцию, просто удерживайте нажатой клавишу «Ctrl» (Mac: Command) при нажатии на каждую ссылку, и Excel автоматически введет для вас разделители.

Например, вы можете ввести формулу: = СУММ(A1; B10; C5:C10), введя «= СУММ(», затем щелкнув по каждой ссылке, удерживая нажатой клавишу «Ctrl». Это работает с любой функцией, в которой можно использовать ссылки в качестве аргументов.

Зажми Ctrl и выбери следующую ячейку

Все еще зажимаем Ctrl и выбираем следующую ячейку

Все разделители (;) были введены Excel

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

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

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

Наведите курсор на аргумент в подсказке в строке ввода формул

Нажмите на аргумент, чтобы «подсветить» его внутри формулы

9. Вставьте заполнители аргументов функции с помощью горячих клавиш

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

Когда вы вводите функцию, после того, как Excel распознал имя функции, введите Ctrl + Shift + A (обе платформы).

Например, если вы наберете «=ДАТА (» и затем используете Ctrl + Shift + A, Excel выдаст вам «= DATE (год, месяц, день)». Затем вы можете дважды щелкнуть каждый аргумент (или воспользоваться подсказкой функции окно для выбора каждого аргумента) и измените его на значение, которое вы хотите.

Убедитесь, что функция распознана

Ctrl + Shift + A вставляет именованные аргументы

10. Уберите мешающую подсказку с обзора

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

Возьмитесь за край подсказки

Перетащите подсказку в удобное место

11. Включить отображение всех формул одновременно

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

(это тильда). С помощью этого трюка вы можете быстро включать и выключать отображение всех формул на листе. Это хороший способ проверить формулы на согласованность, увидев все формулы одновременно.

Ctrl + `раскрывает все формулы

Все формулы видны

12. Выберите все формулы на рабочем листе одновременно

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

Чтобы выбрать все ячейки, содержащие формулы на листе, просто нажмите Ctrl + G, чтобы открыть диалоговое окно «Переход», затем нажмите кнопку «Выделить» и выберите «Формулы». Когда вы нажмете ОК, будут выбраны все ячейки, которые содержат формулы.

Ctrl + G, чтобы открыть окно Переход

Нажмите кнопку Выделить

Выберите формулы

Все формулы выбраны

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

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

Распространенной проблемой в Excel является необходимость остановить обновление рассчитанных значений. Например, вы хотите упростить рабочую таблицу, удалив вспомогательные столбцы, которые вы использовали для генерации определенных значений. Но если вы удалите эти столбцы с формулами, все еще ссылающимися на них, вы получите массу ошибок #ЗНАЧ. Решение состоит в том, чтобы сначала преобразовать формулы в значения, а затем удалить дополнительные столбцы. Самый простой способ сделать это — использовать Специальную вставку. Сначала выберите формулы, которые вы хотите преобразовать, и скопируйте в буфер обмена. Затем, когда формулы еще выбраны, откройте диалоговое окно «Специальная вставка» (Win: Ctrl + Alt + V, Mac: Ctrl + Cmd + V) и используйте параметр «Значения». Это заменит все формулы, которые вы выбрали, на значения, которые они рассчитали.

Формулы выбраны и скопированы

Используйте Специальную вставку

Нет больше формул!

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

Другой распространенной проблемой в Excel является необходимость изменения большого количества значений одновременно. Например, возможно, у вас есть список из 500 цен на товары, и вам нужно увеличить все цены на 5%. Или, может быть, у вас есть список из 100 дат, которые нужно перенести на одну неделю? В таких случаях вы можете добавить столбец «Помощник» в таблицу, выполнить необходимые вычисления, преобразовать результаты в значения, а затем скопировать их в исходный столбец. Но если вам нужен только простой расчет, Специальная вставка поможет намного проще и быстрее, потому что вы можете изменить значение напрямую без каких-либо дополнительных формул.

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

• добавьте число 7 к любой ячейке на рабочем листе
• скопируйте его в буфер обмена
• выберите все даты, которые вы хотите изменить
• используйте Специальная вставка> Операция> Сложить
• нажмите ОК

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

Скопируйте временное значение и выберите даты

Специальная вставка: значения + сложить

Все даты перенесены на неделю

Чтобы увеличить цены на 5%, используйте тот же подход. Введите «1.05» в ячейку и скопируйте в буфер обмена. Затем выберите цены, которые вы хотите изменить, и используйте Специальная вставка> Операция> Умножить, чтобы преобразовать все цены сразу. Как только вы освоите этот совет, вы найдете много способов его применения.

Скопируйте временное значение и выберите цены

Специальная вставка: значения + умножить

Все цены выросли на 5%

Примечание: этот способ работает только со значениями. Не пытайтесь делать это с формулами!

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

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

Но если вы назовете ячейку A1 «почасовая_ставка», ваши формулы будут выглядеть так:

= B3 * почасовая_ставка
= B4 * почасовая_ставка
= B5 * почасовая_ставка

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

Создать именованный диапазон легко. Просто выберите диапазон/ячейку(и), которые хотите назвать, затем введите имя в поле для имени и нажмите ввод. Теперь, когда вы назвали диапазон, Excel будет использовать его всякий раз, когда вы указываете и нажимаете на диапазон при написании формулы. Нажав на именованный диапазон, вы увидите, что его имя автоматически вставляется в формулу.

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

Первоначальная формула

Ячейку C2 переименовали в «почасовая_ставка»

Формула использует именованный диапазон

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

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

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

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

Применить имена на вкладке «Формулы» на ленте

Выберите именованный диапазон для применения

Именованный диапазон автоматически применен

17. Сохраните формулу, которая еще не дописана

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

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

18. Будьте в курсе функций, которые предлагает Excel

Функции существуют для решения конкретных проблем. Вы можете думать о функции как о готовой формуле с определенным названием, целью и возвращаемым значением. Например, функция ПРОПНАЧ (PROPER) имеет только одну цель: она преобразует первую букву каждого слова в прописную. Дайте ей текст типа «ПОЕЗД чита — ЧЕЛябинск», и она вернет вам «Поезд Чита — Челябинск». Функции невероятно удобны, когда они решают вашу проблему, поэтому имеет смысл ознакомиться с функциями, доступными в Excel.

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

19. Используйте клавишу F4 для переключения относительных и абсолютных ссылок

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

Однако преобразование между относительными и абсолютными ссылками может создавать неудобства — ввод всех этих знаков доллара ($) утомителен и подвержен ошибкам. К счастью, есть отличная горячая клавиша, которая позволяет быстро переключаться между 4 вариантами, доступными для каждой ссылки: (Windows: F4, Mac: Command + T).

Просто поместите курсор в ссылку, используя клавишу. Каждый раз, когда вы нажимаете ее, Excel будет «переходить» к следующему варианту в следующем порядке: полностью относительный (A1)> полностью абсолютный ($A$1)> абсолютный ряд (A$1)> абсолютный столбец ($A1).

20. Помните, что формулы и функции возвращают значение. Всегда.

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

Примечание: Слово «возвращение» происходит из мира программирования, и иногда оно вводит в заблуждение людей, плохо знакомых с этой концепцией. В контексте формул Excel он используется следующим образом: «Функция ДЛСТР возвращает число» или «Формула в ячейке A1 возвращает ошибку». Всякий раз, когда вы слышите слово «возвращает» с формулой, просто думайте «результат».

21. Используйте F9 для оценки частей формулы

Клавиша F9 (Fn + F9 на Mac) может решать части формулы в режиме реального времени. Это фантастический инструмент для отладки больших формул, когда вам необходимо убедиться, что вы получите ожидаемый результат определенной части формулы.

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

Примечание: В Windows вы можете отменить F9, но на Mac — нет. Чтобы выйти из формулы без внесения изменений, просто используйте Esc.

22. Используйте функцию «Вычислить формулу»

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

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

Примечание: Эта функция доступна только в версии для Windows. Версия для Mac использует другой подход, называемый Formula Builder, который отображает результаты при создании формулы. Функциональность отличается, но этот подход также очень полезен.

23. Построить сложные формулы в небольших шагах

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

Например, вы хотите написать формулу, которая извлекает имя из полного имени. Вы знаете, что могли бы использовать функцию ЛЕВСИМВ (LEFT), чтобы вытянуть текст слева, но вы не знаете, как рассчитать количество символов для извлечения. Начните с ЛЕВСИМВ (полное-имя; 5), чтобы формула заработала. Затем подумайте, как заменить число 5 на вычисленное значение. В этом случае вы можете определить количество извлекаемых символов, используя функцию НАЙТИ, чтобы определить положение первого пробела.

24. Используйте именованные диапазоны как переменные

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

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

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

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

Например, обычно у вас может быть заголовок «Стоимость кофе», за которым следует рассчитанная стоимость. С конкатенацией вы можете сделать так, чтобы было написано: «Стоимость кофе (150 рублей за чашку)».

26. Добавьте разрывы строк во вложенные ЕСЛИ (IF), чтобы их было легче читать

Когда вы создаете вложенную формулу ЕСЛИ (IF), отслеживание истинных и ложных аргументов в скобках может привести к путанице. Когда вы будете закрывать скобки, легко ошибиться в логике. Однако существует простой способ сделать формулу с несколькими операторами ЕСЛИ (IF) более читабельной: просто добавьте разрывы строк в формулу после каждого ИСТИННОГО аргумента. Это сделает формулу более похожей на таблицу.

27. Вводите функции с автозаполнением

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

28. Используйте Автосумму для ввода формул СУММ (SUM)

Этот способ подойдет не для всех случаев, но при использовании точно доставит удовольствие. Автосумма работает как для строк, так и для столбцов. Просто выберите пустую ячейку справа или под ячейками, которые вы хотите суммировать, и введите Alt + = (Mac: Command + Shift + T). Excel определит диапазон, который вы пытаетесь суммировать, и вставит функцию СУММ (SUM) за один шаг. Если вы хотите быть более конкретным, чтобы Excel не догадывался, сначала выберите диапазон, который вы хотите суммировать, включая ячейку, в которой вы хотите использовать функцию СУММ (SUM).

Автосумма даже вставит несколько функций СУММ (SUM) одновременно. Чтобы сложить несколько столбцов, выберите диапазон пустых ячеек под столбцами. Чтобы сложить несколько строк, выберите диапазон пустых ячеек в столбце справа от строк.

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

29. Введите одну и ту же формулу одновременно в несколько ячеек.

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

Вы также можете использовать эту же технику для редактирования нескольких формул одновременно. Просто выберите все формулы , внесите необходимые изменения и нажмите Ctrl + Enter.

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

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

Команда контент-менеджеров wikiHow тщательно следит за работой редакторов, чтобы гарантировать соответствие каждой статьи нашим высоким стандартам качества.

Количество источников, использованных в этой статье: 8. Вы найдете их список внизу страницы.

Количество просмотров этой статьи: 130 864.

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