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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

На картинке видно, что при копировании формулы во вторую ячейку меняется ячейка, откуда берется новое число ответов с данным числом лайков (вместо A2 поставляется A3 ), но также меняется и ячейка, откуда берется общее число ответов (вместо counts!B1 подставляется counts!B2 ). Как бы сделать, чтобы это второе число всегда бралось бы из одной ячейки при копировании формулы? (Чтобы всегда оставалось бы counts!B1 ?)

• Вопрос задан более трёх лет назад
• 86293 просмотра

Комментировать
Решения вопроса 1

Наверное как в Excel. Попробуйте поставить знак доллара перед каждой осью ячейки, то есть в вашем случае нужно В1 в формуле заменить на $B$1. Доллар фиксирует ось, перед которой написан.

покупка

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

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

• Метод 1: с помощью функции замены
• Метод 2: преобразование формулы в текст
• Метод 3: путем преобразования в абсолютные ссылки
• Метод 4: с помощью функции точного копирования

Копировать формулу, не меняя ссылки на ячейки по функции «Заменить»

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

1. Выберите ячейки формулы, которые вы скопируете, и нажмите Главная > Найти и выбрать > Замените, или нажмите ярлыки CTRL + H для открытия Найти и выбрать диалоговое окно.

2. Нажмите Замените кнопку в Найти то, что поле ввода « = ”, И в Заменить поле ввода « # »Или любые другие знаки, которые отличаются от ваших формул, и нажмите Заменить все кнопку.
По сути, это предотвратит превращение ссылок в ссылки. Например, « = A1 * B1 «Становится» # A1 * B1 », И вы можете перемещать его, не меняя автоматически Excel ссылки на ячейки в текущем листе.

3. А теперь все » = »В выбранных формулах заменяются на« # ». Появляется диалоговое окно, в котором показано, сколько замен было произведено. Пожалуйста, закройте его. Смотрите скриншот выше:
И формулы в диапазоне будут изменены на текстовые строки. Смотрите скриншоты:

4. Скопируйте и вставьте формулы в нужное место на текущем листе.

5. Выберите оба измененных диапазона, а затем выполните шаг 2 в обратном порядке. Щелкните Главная> Найти и выбрать >Заменить… или нажмите ярлыки CTRL + H, но на этот раз введите « # «В поле» Найти «и» = »В поле« Заменить на »и нажмите Заменить все. Затем формулы были скопированы и вставлены в другое место без изменения ссылок на ячейки. Смотрите скриншот:

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

Вышеупомянутый метод состоит в том, чтобы изменить формулу на текст с заменой = на #. На самом деле, Kutools for Excel предоставлять такие утилиты Преобразовать формулу в текст и Преобразовать текст в формулу. И вы можете преобразовывать формулы в текст и копировать их в другие места, а затем легко восстанавливать этот текст в формулу.

Kutools for Excel — Содержит более 300 основных инструментов для Excel. Наслаждайтесь полнофункциональным 30 -дневная БЕСПЛАТНАЯ пробная версия без необходимости использования кредитной карты! Скачать сейчас!

Kutools for Excel — Combines more than 300 Advanced Functions and Tools for Microsoft Excel

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

2. Теперь выбранные формулы преобразованы в текст. Скопируйте их и вставьте в целевой диапазон.

3. И затем вы можете восстановить текстовые строки в формулу, выбрав текстовые строки и нажав Кутулс > Содержание > Преобразовать текст в формулу. Смотрите скриншот:

Kutools for Excel — Дополните Excel более чем 300 основными инструментами. Наслаждайтесь полнофункциональным 30 -дневная БЕСПЛАТНАЯ пробная версия без необходимости использования кредитной карты! Get It Now

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

Формулы изменяются после копирования в результате относительных ссылок. Следовательно, мы можем применить Kutools for ExcelАвтора Преобразовать ссылки утилита для изменения ссылок на ячейки на абсолютные, чтобы предотвратить их изменение после копирования в Excel.

Kutools for Excel — Содержит более 300 основных инструментов для Excel. Наслаждайтесь полнофункциональным 30 -дневная БЕСПЛАТНАЯ пробная версия без необходимости использования кредитной карты! Скачать сейчас!

Kutools for Excel — Combines more than 300 Advanced Functions and Tools for Microsoft Excel

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

2. В открывшемся диалоговом окне «Преобразование ссылок на формулы» установите флажок К абсолютному и нажмите Ok кнопка. Смотрите скриншот:

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

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

Kutools for Excel — Дополните Excel более чем 300 основными инструментами. Наслаждайтесь полнофункциональным 30 -дневная БЕСПЛАТНАЯ пробная версия без необходимости использования кредитной карты! Get It Now

Скопируйте формулу, не меняя ссылки на ее ячейки, Kutools for Excel

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

Kutools for Excel — Содержит более 300 основных инструментов для Excel. Наслаждайтесь полнофункциональным 30 -дневная БЕСПЛАТНАЯ пробная версия без необходимости использования кредитной карты! Скачать сейчас!

Kutools for Excel — Combines more than 300 Advanced Functions and Tools for Microsoft Excel

1. Выберите ячейки формулы, которые вы скопируете, и нажмите Кутулс > Точная копия.

2, Во-первых Точная копия формулы диалоговое окно, щелкните OK. Во втором диалоговом окне «Копирование точной формулы» укажите первую ячейку целевого диапазона и щелкните значок OK кнопка. Смотрите скриншот:

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

Kutools for Excel — Дополните Excel более чем 300 основными инструментами. Наслаждайтесь полнофункциональным 30 -дневная БЕСПЛАТНАЯ пробная версия без необходимости использования кредитной карты! Get It Now

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

Kutools for Excel: Более 300 удобных инструментов у вас под рукой! Начните свой 30 Бесплатная пробная версия на день без каких-либо ограничений сегодня. Скачать

5 основ Excel (обучение): как написать формулу, как посчитать сумму, сложение с условием, счет строк и пр.

Подумать только : складывать в автоматическом режиме значения из одних формул в другие, искать нужные строки в тексте, создавать собственные условия и т.д. — в общем-то, по сути мини-язык программирования для решения «узких» задач (признаться честно, я сам долгое время Excel не рассматривал за программу, и почти его не использовал) .

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

То есть эта статья будет что-то мини гайда по обучению самому нужному для работы (точнее, чтобы начать пользоваться Excel и почувствовать всю мощь этого продукта!) .

Возможно, что прочти подобную статью лет 17-20 назад, я бы сам намного быстрее начал пользоваться Excel (и сэкономил бы кучу своего времени для решения «простых» задач. ��

Обучение основам Excel: ячейки и числа

Примечание : все скриншоты ниже представлены из программы Excel 2016 (одна из самых новых на сегодняшний день. Если у вас версия 2019 — всё будет аналогично).

*

Многие начинающие пользователи, после запуска Excel — задают один странный вопрос: «ну и где тут таблица?». Между тем, все клеточки, что вы видите после запуска программы — это и есть одна большая таблица!

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

• слева : в ячейке (A1) написано простое число «6». Обратите внимание, когда вы выбираете эту ячейку, то в строке формулы (Fx) показывается просто число «6».
• справа : в ячейке (C1) с виду тоже простое число «6», но если выбрать эту ячейку, то вы увидите формулу «=3+3» — это и есть важная фишка в Excel!

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

�� Суть в том, что Excel может считать как калькулятор, если выбрать какую нибудь ячейку, а потом написать формулу, например «=3+5+8» (без кавычек). Результат вам писать не нужно — Excel посчитает его сам и отобразит в ячейке (как в ячейке C1 в примере выше)!

Но писать в формулы и складывать можно не просто числа, но и числа, уже посчитанные в других ячейках. На скриншоте ниже в ячейке A1 и B1 числа 5 и 6 соответственно. В ячейке D1 я хочу получить их сумму — можно написать формулу двумя способами:

• первый: «=5+6» (не совсем удобно, представьте, что в ячейке A1 — у нас число тоже считается по какой-нибудь другой формуле и оно меняется. Не будете же вы подставлять вместо 5 каждый раз заново число?!);
• второй: «=A1+B1» — а вот это идеальный вариант, просто складываем значение ячеек A1 и B1 (несмотря даже какие числа в них!).

Сложение ячеек, в которых уже есть числа

�� Распространение формулы на другие ячейки

В примере выше мы сложили два числа в столбце A и B в первой строке. Но строк то у нас 6, и чаще всего в реальных задачах сложить числа нужно в каждой строке! Чтобы это сделать, можно:

1. в строке 2 написать формулу «=A2+B2» , в строке 3 — «=A3+B3» и т.д. (это долго и утомительно, этот вариант никогда не используют) ;
2. выбрать ячейку D1 (в которой уже есть формула) , затем подвести указатель мышки к правому уголку ячейки, чтобы появился черный крестик (см. скрин ниже) . Затем зажать левую кнопку и растянуть формулу на весь столбец. Удобно и быстро! ( Примечание : так же можно использовать для формул комбинации Ctrl+C и Ctrl+V (скопировать и вставить соответственно)) .

Кстати, обратите внимание на то, что Excel сам подставил формулы в каждую строку. То есть, если сейчас вы выберите ячейку, скажем, D2 — то увидите формулу «=A2+B2» (т.е. Excel автоматически подставляет формулы и сразу же выдает результат) .

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

Довольно часто требуется в формулах (когда вы их копируете), чтобы какой-нибудь значение не менялось. Скажем простая задача: перевести цены в долларах в рубли. Стоимость рубля задается в одной ячейке, в моем примере ниже — это G2.

Далее в ячейке E2 пишется формула «=D2*G2» и получаем результат. Только вот если растянуть формулу, как мы это делали до этого, в других строках результата мы не увидим, т.к. Excel в строку 3 поставит формулу «D3*G3», в 4-ю строку: «D4*G4» и т.д. Надо же, чтобы G2 везде оставалась G2.

Чтобы это сделать — просто измените ячейку E2 — формула будет иметь вид «=D2*$G$2». Т.е. значок доллара $ — позволяет задавать ячейку, которая не будет меняться, когда вы будете копировать формулу (т.е. получаем константу, пример ниже) .

Константа / в формуле ячейка не изменяется

Как посчитать сумму (формулы СУММ и СУММЕСЛИМН)

Можно, конечно, составлять формулы в ручном режиме, печатая «=A1+B1+C1» и т.п. Но в Excel есть более быстрые и удобные инструменты.

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

�� Что нужно сделать, чтобы посчитать сумму определенных ячеек:

1. сначала выделяем ячейки (см. скрин ниже ��) ;
2. далее открываем раздел «Формулы» ;
3. следующий шаг жмем кнопку «Автосумма» . Под выделенными вами ячейками появиться результат из сложения;
4. если выделить ячейку с результатом (в моем случае — это ячейка E8) — то вы увидите формулу «=СУММ(E2:E7)» .
5. таким образом, написав формулу «=СУММ(xx)» , где вместо xx поставить (или выделить) любые ячейки, можно считать самые разнообразные диапазоны ячеек, столбцов, строк.

Автосумма выделенных ячеек

�� Как посчитать сумму с каким-нибудь условием

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

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

1. » =СУММЕСЛИМН( F2:F7 ; A2:A7 ;»Саша») » — ( прим .: обратите внимание на кавычки для условия — они должны быть как на скрине ниже, а не как у меня сейчас написано на блоге) . Так же обратите внимание, что Excel при вбивании начала формулы (к примеру «СУММ. «), сам подсказывает и подставляет возможные варианты — а формул в Excel’e сотни!;
2. F2:F7 — это диапазон, по которому будут складываться (суммироваться) числа из ячеек;
3. A2:A7 — это столбик, по которому будет проверяться наше условие;
4. «Саша» — это условие, те строки, в которых в столбце A будет «Саша» будут сложены (обратите внимание на показательный скриншот ниже) .

Сумма с условием

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

Как посчитать количество строк (с одним, двумя и более условием)

Довольно типичная задача: посчитать не сумму в ячейках, а количество строк, удовлетворяющих какомe-либо условию.

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

«=СЧЁТЕСЛИ( A2:A7 ; A2 )» — где:

• A2:A7 — диапазон, в котором будут проверяться и считаться строки;
• A2 — задается условие (обратите внимание, что можно было написать условие вида «Саша», а можно просто указать ячейку).

Результат показан в правой части на скрине ниже.

Количество строк с одним условием

Теперь представьте более расширенную задачу: нужно посчитать строки, где встречается имя «Саша», и где в столбце «B» будет стоять цифра «6». Забегая вперед, скажу, что такая строка всего лишь одна (скрин с примером ниже) .

Формула будет иметь вид:

=СЧЁТЕСЛИМН( A2:A7 ; A2 ; B2:B7 ;»6″) — (прим.: обратите внимание на кавычки — они должны быть как на скрине ниже, а не как у меня) , где:

A2:A7 ; A2 — первый диапазон и условие для поиска (аналогично примеру выше);

B2:B7 ;»6″ — второй диапазон и условие для поиска (обратите внимание, что условие можно задавать по разному: либо указывать ячейку, либо просто написано в кавычках текст/число).

Счет строк с двумя и более условиями

Как посчитать процент от суммы

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

�� В помощь!

Как посчитать проценты: от числа, от суммы чисел и др. [в уме, на калькуляторе и с помощью Excel] — заметка для начинающих

Самый простой способ, в котором просто невозможно запутаться — это использовать правило «квадрата», или пропорции.

Вся суть приведена на скрине ниже: если у вас есть общая сумма, допустим в моем примере это число 3060 — ячейка F8 (т.е. это 100% прибыль, и какую то ее часть сделал «Саша», нужно найти какую. ).

По пропорции формула будет выглядеть так: =F10*G8/F8 (т.е. крест на крест: сначала перемножаем два известных числа по диагонали, а затем делим на оставшееся третье число).

В принципе, используя это правило, запутаться в процентах практически невозможно ��.

Пример решения задач с процентами

PS

Собственно, на этом я завершаю данную статью. Не побоюсь сказать, что освоив все, что написано выше (а приведено здесь всего лишь «пяток» формул) — Вы дальше сможете самостоятельно обучаться Excel, листать справку, смотреть, экспериментировать, и анализировать. ��

Скажу даже больше, все что я описал выше, покроет многие задачи, и позволит решать всё самое распространенное, над которым часто ломаешь голову (если не знаешь возможности Excel) , и даже не догадывается как быстро это можно сделать. ✔

Первая публикация: 21.01.2017