Как протянуть формулу в excel чтобы менялась одна переменная
Как сделать, чтобы в формуле значение одной ячейки не менялось и осталось тем же самым при копировании?
Чтобы разобраться как добиться, чтобы при копировании формулы в Excel ячейка оставалась прежней (а не менялась) необходимо ее закрепить, еще говорят "заморозить", "зафиксировать".
Лучше всего разобрать на примере.
Допустим в ячейке у Вас формула
если Вы растяните ее вниз (или скопируете и вставите вниз - в следующую ячейку)
то формула преобразуется и станет вот такой:
Однако, если Вам нужно, чтобы ко всем ячейкам из столбца A (последовательно) прибавлялось некое значение из ячейки B2 и при копировании эта ячейка не смещалась, то Вам следует немного изменить первоначальную формулу написав ее так:
Сделайте так и ячейка с "долларами" (то есть $B$2 в нашем случае) не будет меняться.
Значки доллара ($) можно проставить как вручную введя их с клавиатуры, так и через F4.
Использовать F4 надо так: мышкой выделяете (в формуле) нужный адрес (в нашем примере - это B2) и нажимаете 1 раз F4 - сразу и перед буквой и перед цифрой появится значок доллара $ (он, кстати, в Excel означает, т.н. абсолютный адрес - то есть такой адрес, который не будет изменяться при копировании формулы)
"Excel" - это популярный документ, который дает много возможностей. Иногда существуют некоторые нюансы, которые приходится соблюдать, чтобы некоторые моменты не создавали неудобства.
Так, попытавшись скопировать данные в другую область, меняются формулы, закрепленные к этим данным.
Есть разные способы решить задачу, чтобы скопировать, в то же время не изменив формулы.
Первый самый простой.
Пример. Вот данные, которые получены в столбце "D" суммированием данных из столбцов "В" и "С", разделив результат на данные из столбца "J", чтобы преобразовать в евро.
Если попытаться перетащить столбец горячими клавишами "скопировать" и "вставить", то итоговые значения посчитаются в каждой из ячеек столбца, как "0", поскольку изменилась формула при копировании. Это потому происходит, что "Excel" свойственно сдвигать относительные ссылки.
1) И вот какое решение существует - преобразовать относительные ссылки в абсолютные.
Этот способ имеет одно неудобство, что приходится вручную работать с данными, подставляя каждый раз.
2) Тогда можно попробовать другой способ - "дезактивировать" формулы, то есть сделать так, чтобы в документе "Excel" не воспринимались формулы - "формулами", а воспринимались, словно это обычный текст.
Воспользуемся горячими клавишами.
3) Еще способ - воспользоваться копированием с применением блокнота, то есть из документа копировать в блокнот, оттуда переносить данные в нужный диапазон.
На вкладке "Формулы" надо найти "Показать формулы" - режим проверки формул, тогда в ячейках вместо результатов программа показывает формулы, по которым вычислили эти результаты. Можно пользоваться горячими клавишами, алгоритм следующий:
Дальше надо скопировать диапазон из Exel в блокнот -
4) Четвертый способ подходит тем, кто постоянно выполняет копирование, перенося каждый раз. Можно воспользоваться макросом - здесь все проще всего, все задано заранее, надо только создать макрос.
Чтобы работать с макросами, надо перейти на вкладку "Разработчик" или применить сочетание горячих клавишь Alt+F8.
Запустив макрос, необходимо показать программе исходный диапазон, откуда копируют и куда вставляют.
В авторском ролике Николай Павлов, специалист по Exel, расскажет еще более подробно и наглядно.
Документ "Excel" часто бывает необходим, чтобы посчитать значение в различных задачах. Но иногда бывает неудобно работать с этим документом по ряду причин. Так, если попытаться скопировать столбец с формулой в другой диапазон, то формула становится другой и считает неправильно. А чтобы не менялась формула, приходится идти на хитрости.
"Excel" - это один из самых используемых документов, так как помогает быстро делать расчеты. В верхней части видны формулы, а если постараться перебросить столбец в другой диапазон, то формула меняется. В этом случае не отображаются расчеты. Как обойти подобное? Можно конечно вручную пытаться изменить ситуацию, сначала переводя ссылки в формулах не в относительные, а в абсолютные, затем, выделяя каждую строку, с ней работать. Но если таких строк много, то долго, не вариант.
Другой вариант - изменить формулу таким образом, чтобы "Excel" воспринимал формулу не как формулу, а как обычный текст. Для этого надо изменить знак равенства на другой. Горячими клавишами открывается окно из вкладки "Главной" - "Найти и выделить" и там смотрим "Заменить" - вместо "равно" - любой знак, ту же "решетку". Конечно, потом все придется поменять обратно, чтобы формулы были формулами.
Еще один вариант - применение макросов, это самое удобное, надо только сохранить макрос с готовым вариантом действия и подставлять.
Рассмотрим несколько способов протягивания (копирования) формул в строках и столбцах программы Excel.
Первый способ: протянуть ячейку в столбце или строке.
Чтобы протянуть (распространить) формулу из одной ячейки в несколько ячеек столбца или строки следует выполнить ряд действий:
1. Записать функцию (формулу) в ячейку и нажать ENTER.
2. Навести курсор на нижний правый угол ячейки таким образом, чтобы он приобрел форму тонкого черного крестика.
3. Нажать и удерживать левую кнопку мыши.
4. Не отпуская кнопку мыши потянуть крестик в нужном направлении. В ту сторону, в которую следует распространить значение ячейки.
Второй способ: быстрое протягивание формулы в один клик мыши.
Для быстрого заполнения столбца формулой или значением из ячейки достаточно выполнить следующие действия:
1. Записать функцию (формулу) в ячейку и нажать «ENTER».
2. Навести курсор на нижний правый угол ячейки таким образом, чтобы он приобрел форму тонкого черного крестика.
3. Кликнуть двойным кликом по правому нижнему углу ячейки.
Формула протянется автоматически до первой пустой ячейки или до полной в случаях, когда в столбце после ячейки с размножаемой функцией стоят пустые ячейки.
Третий способ: протянуть формулу без изменения (смещения) исходных ячеек.
Для протягивания функции без ее изменения следует выполнить те же операции, что в первом и во втором случает. Только перед протягиванием нужно зафиксировать адреса неизменяемых ячеек.
Зафиксировать адреса ячеек можно добавив перед значением адреса столбца или перед значением адреса строки знак «$»доллара.
Теперь, когда адрес закреплен, он не будет меняться при протягивании.
Четвертый способ: протянуть формулу при помощи копирования.
Подходит для работы под фильтрами.
Выполняется следующим образом:
1. Записать функцию (формулу) в ячейку и нажать ENTER.
2. Копировать значение ячейки при помощи выпадающего контекстного меню правой кнопкой мыши или сочетанием клавиш «ctrl»+»c».
3. Выделить нужный диапазон.
Для быстрого выделения вниз достаточно нажать сочетание клавиш:
«Ctrl»+»shift»+ стрелка вниз
Для быстрого выделения в сторону достаточно нажать сочетание клавиш:
«Ctrl»+»shift»+ стрелка в сторону
Рассмотрим несколько способов протягивания (копирования) формул в строках и столбцах программы Excel.
Первый способ: протянуть ячейку в столбце или строке.
Чтобы протянуть (распространить) формулу из одной ячейки в несколько ячеек столбца или строки следует выполнить ряд действий: 1. Записать функцию (формулу) в ячейку и нажать ENTER. 2. Навести курсор на нижний правый угол ячейки таким образом, чтобы он приобрел форму тонкого черного крестика. 3. Нажать и удерживать левую кнопку мыши.
4. Не отпуская кнопку мыши потянуть крестик в нужном направлении. В ту сторону, в которую следует распространить значение ячейки.
Для быстрого заполнения столбца формулой или значением из ячейки достаточно выполнить следующие действия: 1. Записать функцию (формулу) в ячейку и нажать «ENTER». 2. Навести курсор на нижний правый угол ячейки таким образом, чтобы он приобрел форму тонкого черного крестика.
Формула протянется автоматически до первой пустой ячейки или до полной в случаях, когда в столбце после ячейки с размножаемой функцией стоят пустые ячейки.
Третий способ: протянуть формулу без изменения (смещения) исходных ячеек.
Для протягивания функции без ее изменения следует выполнить те же операции, что в первом и во втором случает. Только перед протягиванием нужно зафиксировать адреса неизменяемых ячеек. Зафиксировать адреса ячеек можно добавив перед значением адреса столбца или перед значением адреса строки знак "$"доллара.
Четвертый способ: протянуть формулу при помощи копирования.
Подходит для работы под фильтрами. Выполняется следующим образом: 1. Записать функцию (формулу) в ячейку и нажать ENTER. 2. Копировать значение ячейки при помощи выпадающего контекстного меню правой кнопкой мыши или сочетанием клавиш "ctrl"+"c".
3. Выделить нужный диапазон. Для быстрого выделения вниз достаточно нажать сочетание клавиш: "Ctrl"+"shift"+ стрелка вниз Для быстрого выделения в сторону достаточно нажать сочетание клавиш: "Ctrl"+"shift"+ стрелка в сторону
Работая с таблицами Excel, пользователям часто приходится использовать для вычислений одни и те же формулы. Причем расположенные не просто рядом, а вдоль всей строки или столбца. Работу можно заметно упростить — для этого надо познакомиться с различными способами, как протянуть формулу в Эксель в нужном направлении.
Наши советы помогут работать с обычными суммами значений в выбранном диапазоне ячеек или сложными вычислениями с десятками аргументов. Главное, что при большом количестве формул их будет легко расположить в нужных местах.
1 Простое протягивание формулы
Это самый простой и привычный для многих пользователей способ распространения формулы сразу на несколько ячеек строки или столбца. Он требует выполнения следующих действий:
- В первую ячейку с одной из сторон (например, сверху) надо записать нужную формулу и нажать Enter.
- После появления рассчитанного по формуле значения навести курсор в нижний правый угол ячейки. Подождать, пока толстый белый крестик не превратиться в тонкий черный.
- Нажать на крестик и, удерживая его, протянуть формулу в нужном направлении. В указанном примере — вниз.
Аргументы в формуле будут изменяться соответственно новому расположению. И если в самой первой ячейке это были F7 и G7, в последней позиции столбца это будет уже F12 и G12. Соответственно, если начинать распространять формулы по строкам, изменяться будут не цифры, а буквы в обозначениях ячеек.
Способ отличается простотой и высокой скоростью. Но не всегда подходит для больших таблиц. Так, если в столбце несколько сотен или даже тысяч значений, формулу проще растягивать другими способами, чтобы сэкономить время. Один из них — автоматическое копирование, требующее всего лишь двойного клика кнопкой мыши.
2 Быстрое автозаполнение
Еще один способ в Excel протянуть формулу до конца столбца с более высокой по сравнению с первой методикой скоростью. Требует от пользователя применить такие действия:
Доброго дня, друзья. Давайте разберем сегодня основное, я бы сказал, кармическое предназначение Excel. А именно, как же начать (или, с чего начать) эффективно работать в Excel и что такое формула в Excel?
Никто не спорит, что работая в Excel, как нигде больше, можно размещать данные в таблицах и выводить их красиво на печать. Но выло бы большим, читай — смертным, грехом использовать его только для этого. Это все равно как купить калькулятор и использовать его как табло для цифр. Так вот, Excel — это, прежде всего, мощный вычислительный комплекс, позволяющий обрабатывать и анализировать внесенные в таблицы данные. Давайте рассмотрим, как, работая в Excel, заставить его выполнять вычисления.
Как вы уже, наверняка, знаете (или догадываетесь ? ), в ячейки Excel можно поместить такие данные: числа, текст, даты и формулы.
Как же Excel отличает формулы от других данных? Очень просто, по знаку равенства. Представьте, что ячейка, это некая переменная, отображающая результат вычисления. Следовательно, если приравнять ее к какому-то выражению (например, записать в ней =3+2), то результат в виде числа 5 отобразится в ячейке. А если знак равенства убрать, то в ячейке будет видно надпись 3+2. Попробуйте.
Адрес ячейки Excel
Как я уже отметил, ячейку удобно представлять как переменную (как X или Y в математике). Стало быть, записывая в ячейку данные, мы присваиваем их значение ячейке-переменной. Каждая ячейка имеет в таблице свой определенный адрес, который определяется как в шахматах по пересечению столбца и строки. Так, ячейка, расположенная на пересечении столбца В и пятой строки имеет адрес (он же ее имя) В5. Фактически это как бы переменная В5.
Это наиболее привычный нам формат адреса ячеек Excel. Но можно встретить и RC-формат, который устанавливается в настройках табличного редактора. RC расшифровывается как Row (строка) и Column (столбец). Ниже на рисунке показано, где в параметрах устанавливается (или отключается) переключение форматов ссылок. Таким образом, для той же ячейки В5 адрес в этом формате будет выглядеть как R5C2 (5-я строка и 2-й столбец). Так, с адресацией, надеюсь, разобрались.
Заставляем Excel считать
Как уже отмечалось, чтобы отобразить в ячейке расчетные данные, нужно записать в нее знак равенства и математическое выражение. Но, не будем же мы каждый раз переписывать наше выражение, изменяя в нем цифры. Гораздо удобней иметь готовую формулу, которая пересчитывает результат, как только в ней изменяются значения. То есть, чтобы записать формулу, суммирующую два числа, в ней можно указать адреса ячеек, в которые эти числа будут подставляться.
Например, поместим в ячейки A1 и В1 числа 5 и 12 соответственно, а в ячейку С1 запишем формулу суммирования =A1+В1. Как только формула будет введена, в ячейке С1 отобразится результат — число 17. Все просто! Если поменять в ячейках A1 и В1 числа на другие, то сумма в ячейке С1 будет автоматически пересчитана.
Нужно отметить, что в формулах Excel могут использоваться различные арифметические операторы: умножения (*), деления (/), сложения (+) и вычитания (-). Если отсутствуют скобки, то их приоритет стандартный, как в математике. Тут они перечислены в порядке убывания приоритета: умножение и деление имеют более высокий приоритет, чем сложение и вычитание. Операторы с одинаковым приоритетом выполняются слева направо. Но, чтобы работая в Excel не ошибиться, можно использовать скобки, тем более, что скобки облегчают чтение и анализ формул.
Относительные ссылки Excel
Выше были рассмотрены относительные ссылки, которые указывают на некоторые ячейки: А1, В1 и др. Их называют относительными, так как при копировании формулы в другую ячейку, содержащиеся в ней формулы изменяются на величину перемещения.
Например, если взять ячейку С1, содержащую формулу =A1+В1, и скопировать в ячейку С2, то формула в последней будет =A2+В2. Если копировать по строкам дальше, то формула будет соответственно изменяться, указывая на ячейки той строки, куда выполняется копирование. Это очень удобно для расчета данных таблицы построчно.
Допустим, есть небольшая табличка с какими-то данными. Достаточно создать формулу в итоговом столбце для 1-й строки и скопировать ее на другие строки протаскиванием. Результаты появятся в итоговом столбце для каждой строки. При этом в адресах ячеек изменятся номера строк на величину перемещения формулы.
Этот прием можно проводить и для копирования в другие столбцы, адреса ячеек в формуле изменят имена столбцов на величину смещения формулы относительно исходной (копируемой).
Абсолютные ссылки Excel
Абсолютные ссылки нужны, если мы хотим указать на ячейку, местоположение которой не должно изменяться при копировании формулы. Абсолютные ссылки ячеек записываются как $A$1, A$1 или $A1. При этом знак доллара ($) указывает на то, какая часть ссылки зафиксирована. Доллар перед именем столбца фиксирует столбец (при копировании по горизонтали имя столбца меняться не будет), а доллар перед номером строки фиксирует строку (при копировании по столбцу номер строки меняться не будет). Если же знак доллара указан и перед столбцом, и перед строкой, то ссылка останется неизменной при любом копировании.
Для быстрого изменения абсолютной ссылки на относительную используется клавиша F4. Для смена типа достаточно установить текстовый курсор на какую-нибудь ссылку в созданной формуле и нажать несколько раз клавишу F4. После каждого нажатия этой клавиши тип ссылки будет циклично меняться. Например, если установить курсор на ссылку A2 в формуле =A2+В2 и нажимать F4, то она поочередно примет вид: $A$2, A$2, $A2 и опять A2. Достаточно удобно и лучше, чем вручную забивать туда знак доллара.
Чтобы лучше разобраться в различиях ссылок, давайте рассмотрим такой пример: допустим, надо получить в столбце С сумму чисел, указанных в столбцах А и В, умноженную на некоторый коэффициент, размещенный в ячейке D1 и равный 0,5.
Для выполнения этого задания следует в ячейке с адресом С1 разместить формулу, которая будет вычислять данные. Это удобно выполнить, вводя знаки действия с клавиатуры, а адреса ячеек удобно вводить щелчком мыши на нужных ячейках (при этом их адреса будут заноситься в формулу).
После этого устанавливаем текстовый курсор на адрес ячейки D1 и нажимая на клавишу F4 делаем ссылку абсолютной — $D$1. После этого формула примет такой вид:
Теперь содержимое ячейки можно копировать на весь столбец таблицы с данными. При этом ссылки A1 и B1 будут изменяться на величину смещения, а ссылка $D$1 останется неизменной, благодаря чему значение коэффициента будет браться только из этой ячейки.
Обратите внимание, что в ячейке и в строке формул ссылки на ячейки подсвечены разными цветами. Этими же цветами отмечены ячейки, на которые они указывают.
Ссылки на другие листы книги Excel
Следует отметить, что можно также ссылаться и на другие листы текущей книги Excel (вашего файла) точно так же, как и на ячейки текущего листа. Честно говоря, можно ссылаться и на листы других книг (при этом ссылка будет называться внешней ссылкой), но об этом в другой статье.
Например, чтобы записать в ячейку А1 (Лист 1) ссылку на ячейку А15 листа 2, надо проделать следующие действия:
- установить курсор на ячейку А1 и ввести знак равенства;
- выполнить щелчок на ярлыке листа «Лист 2» (выполнится переход на этот лист);
- щелкнуть на ячейке А15 и нажать клавишу Enter;
после этого опять будет выполнен переход на Лист 1 в ячейку А15, где появится формула Excel. =Лист2!А15.
При работе в Excel редактирование формулы выполняется так же, как и редактирование текстовой строки, записанной в ячейку. Т.е. надо курсор Excel (белый крестик) установить на ячейку с формулой и выполнить двойной щелчок мышью или нажать клавишу F2. При этом в формуле появится текстовый курсор и выполняется редактирование.
Читайте также: