Как распространить формулу на всю таблицу в excel

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

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

1 Простое протягивание формулы

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

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

Аргументы в формуле будут изменяться соответственно новому расположению. И если в самой первой ячейке это были F7 и G7, в последней позиции столбца это будет уже F12 и G12. Соответственно, если начинать распространять формулы по строкам, изменяться будут не цифры, а буквы в обозначениях ячеек.

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

2 Быстрое автозаполнение

Еще один способ в Excel протянуть формулу до конца столбца с более высокой по сравнению с первой методикой скоростью. Требует от пользователя применить такие действия:

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

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

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

3 Протягивание без изменения ячеек в формуле

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

Для распределения без изменения адреса ячейки выполняются те же действия, что и при обычном протягивании или автоматическом копировании. Но при вводе формулы следует зафиксировать адреса, которые не будут меняться. Для этого используются символы доллара — $. Если в каждом новом пункте столбца при расчетах используется одна и та же ячейка, значки надо будет поставить и перед номером строки, и перед литерой, которая указывает на колонку. Как в примере: $G$6.

Как протянуть формулу в Эксель Онлайн

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

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

Как вставить формулу:

1. Откройте таблицу.
2. Установите курсор в ячейку первой строки, с которой будут начинаться расчёты.



3. Наберите на клавиатуре знак «=» и введите нужную формулу, например, «=B2*C2». С этого знака начинаются все вычисления в эксель.
4. Нажмите Enter на клавиатуре.

Как протянуть формулу

1. Поставьте курсор в ячейку с уже введённой формулой.
2. Наведите курсор на «квадрат» в нижнем правом углу, чтобы он принял вид «+».



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

Формула автоматически скопируется на все строки. Обратите внимание, что адреса ячеек тоже будут меняться.
В первой строке будет написано B2*C2, во второй — B3*C3 и так далее.

Второй способ

1. Выделите ячейку и введите формулу в неё.
2. Нажмите правой кнопкой мыши и в контекстном меню выберите пункт «Копировать» (или нажмите на клавиатуре Ctrl+C).



3. Выделите диапазон, на который нужно протянуть формулу.
4. Нажмите правой кнопкой мыши и выберите «Вставить» (или Ctrl+V).

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

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

Чтобы избежать этого, следуйте инструкции:

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



3. 3. Протяните формулу по инструкции выше.

Как видите, в этот раз все посчиталось правильно. Адрес ячеек с зарплатой в формуле меняется — B2, B3, B4 — а с курсом нет.

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

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

Алгоритм может быть полезен:

• при подсчёте процентного соотношения;
• для обработки налоговых платежей;
• при подсчёте заработной платы;
• при вычислениях продаж конкретных позиций, прибыли от них.

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

Как использовать вычисляемые столбцы в таблице Excel

​Смотрите также​ As Range Set​ Cell = Application.InputBox(«Введите​ скрытыми ячейками и​ цикла — сразу​Guest​ то когда я​ Sub​1kabana​ ссылку, делаем ее​ с соответствующими значениями.​/ (наклонная черта)​ — формулы добавились​ в этом столбце.​Параметры автозамены​ флажок​ столбца или заполнении​Вычисляемые столбцы в таблицах​

​ Rng = ActiveCell​​ диапазон, в который​ вставить спец вставкой​ к диапазону применить:​: Добрый день! У​ и открываю они​ST_Senya​: Вот как раз​ абсолютной. Для изменения​

Создание вычисляемого столбца

1. ​Нажимаем ВВОД – программа​Деление​ сами… Как вариант,​А если надо​, а затем выберите​

2. ​Включить фоновую проверку ошибок​ его формулой он​ Excel упрощают ввод​ Formula = Rng.FormulaR1C1Local​ будет вставлена формула»,​ «формулу», то формула​ Sub Test() Range(«B1:B10»)​ меня еще один​ открываются то слишком​: Попробывал ваш код​

   

   ​ не пойму, мне​​ значений при копировании​

   • ​ отображает значение умножения.​=А7/А8​​ однако.​​ первый столбик растянуть​Не создавать вычисляемые столбцы​​установлен.​​ также становится вычисляемым.​​ формул. Формула, введенная​​ If Formula =​

   • 

3. ​ Type:=8) If Err​ вставится и в​​ = Evaluate(«1.18*A1:A10») End​​ вопрос, если можно,​

   

   ​ высоко, то слева​ со списком, не​​ хотя бы цветом​​ относительной ссылки.​ Те же манипуляции​​^ (циркумфлекс)​​-99668-​​ — таким образом​​ автоматически​Если вы используете Excel​​Если ввести или переместить​​ в одну ячейку​ «» Then MsgBox​​ Then Exit Sub​​ скрытые. Есть еще​ Sub​ помогите! Нужно подсчитать​ где то. Как​ получилось (см. рис),​ ячейку выделять, если​Простейшие формулы заполнения таблиц​ необходимо произвести для​Степень​​T.S.​​ не выходит​.​ 2007, нажмите кнопку​ формулу в столбец,​ столбца, автоматически распространяется​

   ​ «Ячейка не содержит​ Formula = InputBox(«Введите​ функция в Вашей​Ren_5​ сумму абсолютных величин​

4. ​ мне сделать что​ написал в коде​ равно или больше,​ в Excel:​ всех ячеек. Как​=6^2​: Блин тупанул, все​Guest​Вычисляемые поля можно также​ Office​ уже содержащий данные,​ на его остальную​ формулы», vbInformation Exit​ формулу в стиле​

   

​ версии «Выделить видимые​​: мне бы что-нибудь​

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

• ​ в Excel задать​= (знак равенства)​ получилось спасибо :)​: Было — данные​ создавать в сводных​и выберите​ это не приведет​​ часть. При этом​​ Sub End If​ R1C1 без кавычек​ ячейки» (Alt+;) попробуйте.​ попроще, а то​

  

• ​ определенных столбцах между​ в зависимости от​CurrentSpisok.Value = 50​ больше 0, но​ еще один столбец.​ формулу для столбца:​Равно​Просто выделяя столбец «Сумма,​ оформить списком(2003) или​ таблицах. При этом​​Параметры Excel​​ к автоматическому созданию​ не приходится использовать​ On Error Resume​ и знака «»»»=»»»»»)​МВТ​ я ничего не​ строками таблицы 4и5;​ своих размеров располагались​ CurrentSpisok.Selected(7) = Trueно​

  

• ​ не равно Цене,​ Выделяем любую ячейку​ копируем формулу из​Меньше​ руб» и перетаскивая​ таблицей (2007-2010)… А​​ Excel будет применять​ ​>​ вычисляемого столбца. Однако​ заполнение или копирование.​​ Next Set Cell​​ If Formula =​

Создание исключений для вычисляемого столбца

​: Макросом примерно так​ понял)​ 4и6; 4и7; 4и8​ по центру экрана?​ на нужное мне​ то желтый, если​ в первой графе,​ первой ячейки в​>​ вниз, формула сбивается,​

​ лучшее — пример(по​​ созданную формулу ко​Формулы​ отобразится кнопка​

• ​ Это позволяет сэкономить​ = Application.InputBox(«Введите диапазон,​ «» Then Exit​

• ​ (ВПР допишите сами​Sanja​ и т.д. То​​ST_Senya​ ​ место в списке​​ 0, то красный.​​ щелкаем правой кнопкой​

• ​ другие строки. Относительные​Больше​ т. к. в​ правилам) — в​

• ​ всему столбцу. Подробнее​.​Параметры автозамены​ время, особенно при​

  ​ на который будет​​ Sub Cell.SpecialCells(xlCellTypeBlanks).FormulaR1C1Local _​ в стиле R1C1)​: куда проще?! макрос​ есть значения в​

• ​, вы про Access​ так и не​Если можно, оставьте​

  ​ мыши. Нажимаем «Вставить».​​ ссылки – в​Меньше или равно​

• ​ столбце «Курс Юаня»​ студию!.. ;-)​ о вычислении значений​Если вы используете Mac,​, с помощью которой​ наличии множества строк.​

​ распространена формула», Type:=8)​ = «=» &​ Sub tt() Dim​ в одну строку!​ строке 4 неизменны,​ 2007 ведёте речь?Именно​ отобразилось, хотя если​​ свои контакты)​​ Или жмем сначала​​ помощь.​​>=​​ заполнена только одна​​Z. 44357​ в сводных таблицах…​​ в строке меню​​ можно перезаписать данные​

• ​ То же самое​ If Err Then​ Formula End SuUPD​​ Cell As Range​​Юрий М​​ а значения в​​ про него) Извините​​ конечно самому вручную​

• ​А контакты Правилами​ комбинацию клавиш: CTRL+ПРОБЕЛ,​​Находим в правом нижнем​​Больше или равно​​ ячейка. Заполнять весь​​R Dmitry​​Lera​​Excel​​ и создать вычисляемый​​ происходит при изменении​

Прекращение создания вычисляемых столбцов

​ Exit Sub Application.ScreenUpdating​ вот еще один​ For Each Cell​: Вот и впишите​ нижеследующих строчках изменяются​ если ввожу в​ прокрутить полосу прокрутки,​ форума (почитайте, кстати)​ чтобы выделить весь​ углу первой ячейки​<>​ столбец одной и​: Sub test()​: Ребят, подскажите, как​выберите​ столбец.​ формулы: изменение также​ = False If​ вариант. В ячейку​ In Range(«A2:A» &​

• ​ это действие (только​ постоянно.​

  1. ​ какие-то заблуждения. Дело​​ элемент выделен.​​ разрешены только в​​ столбец листа. А​​ столбца маркер автозаполнения.​

     ​Не равно​ той же цифрой​On Error Resume​​ растянуть формулу до​​Параметры​​При вводе новой формулы,​

  2. ​ распространяется на все​​ Formula = «»​​ вписываете формулу в​

  3. ​ Cells(Rows.Count, 1).End(xlUp).Row) With​​ диапазоны свои укажите):​​Введена формула подсчета,​​ в том что​

  4. ​ST_Senya​​ ветке платных решений​​ потом комбинация: CTRL+SHIFT+»=»,​

  5. ​ Нажимаем на эту​​Символ «*» используется обязательно​​ я не хочу​ Next​​ конца листа или​>​​ которая отличается от​ остальные ячейки вычисляемого​

     ​ Then Exit Sub​ ЛЮБОМ формате (A1​ Cell If Not​ Range(«B1:B10») = Evaluate(«1.18*A1:A10»)​ она работает правильно,​ я создаю свою​

     ​:​​ «Фриланс»​ чтобы вставить столбец.​​ точку левой кнопкой​​ при умножении. Опускать​ и прописывать фомулу​Selection.Resize(InputBox(«На сколько строк​ просто на большое​Формулы и списки​ существующих в вычисляемом​ столбца.​ Cell.SpecialCells(xlCellTypeBlanks).FormulaR1C1Local = Formula​ или R1C1), потом​

  ​ .HasFormula Then .Offset(,​Инна Волкова​​ но только для​​ первую БД. И​ST_Senya​​1kabana​​Назовем новую графу «№​​ мыши, держим ее​​ его, как принято​​ каждый раз тоже​​ заполнить?»), 1) =​​ кол-во строк вниз,​​>​

• ​ столбце, она будет​Примечание:​

  ​ Rng.Copy For Each​ распространяете ее на​ 1).FormulaR1C1Local = «=ВПР…..»​​: Здравствуйте. Подскажите, как​​ строки 5 (в​​ что в Аксессе,​, я неудачный дал​​: Очень интересное предложение,​

Вычисление значений в сводной таблице

​ п/п». Вводим в​ и «тащим» вниз​ во время письменных​ не хочу. Должен​ Selection.FormulaLocal​ не таща тупо​Поиск ошибок​ автоматически применена к​

support.office.com

Растянуть формулу до конца листа

​ Снимки экрана в этой​​ Ar In Cell.Areas​ пустые ячейки диапазона,​ End With Next​ протянуть формулу (ВПР)​ которой формула и​ что в VBA​ пример кода. Вот​

​ спасибо большое)​​ первую ячейку «1»,​ по столбцу.​ арифметических вычислений, недопустимо.​ же быть способ​End Sub​ мышку минут 5?​.​ столбцу. Вы можете​ статье получены в​ Ar.PasteSpecial (xlPasteFormats) Next​ который выбираете в​ End SubИли так​ на весь столбец,​ вводилась). Как «протянуть»​ не разбираюсь) только​ попробуйте под себя​
​Я в своей​

​ (2+3)5 Excel не​​ так, чтобы его​ клавиши и будет​: Вариантов, для девочек​
​ для создания вычисляемых​

​ оставить только одну​​ вы используете другую​ Application.ScreenUpdating = True​
​ tt_1() Dim Cell​ Cell As Range​ в которых есть​ все строки, расположенные​ST_Senya​’Передвигаемся примерно туда,​
​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИ([@[Цена договор]]=[@[Сумма выплачено]];»R»;»T»)​ две ячейки –​ выбранные ячейки с​ поймет.​ можно было в​ Вам счастье :)​

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

​ End SubP.S. немного​​ As Range Dim​ For Each Cell​ формула Автосумма. Файлик​ ниже. У меня​, я про элементы​ куда надо. Me.ListBox1.Value​И в этом​ «цепляем» левой кнопкой​ относительными ссылками. То​Программу Excel можно использовать​ любой момент изменить?​-=46444=-​ — в зависимости​

​ Excel по умолчанию​​ кнопку​

​Параметры автозамены​​ функции будут такими​Юрий М​ Formula = ActiveCell.FormulaR1C1Local​ CellsI(Rows.Count, 1).End(xlUp).Row).SpecialCells(xlCellTypeConstants) With​Есть таблица. Есть​ подсчет по двум​
​ помочь — с​

​ If Formula =​​ Cell .Offset(, 1).FormulaR1C1Local​

​ строки 1 и​​ рядом расположенным строкам​ ним не работаю.​- это содержимое​ на Wingdings-2​По такому же принципу​ формула со своими​ формулу числа и​ в формуле ставишь​Velem​ списком/таблицей и есть​ Excel создавало вычисляемые​ этого делать, так​Создайте таблицу. Если вы​ #15 — ну​
​ «» Then MsgBox​ = «=ВПР…..» End​ 2 уровня. ВПРкой​ (5и6; 6и7 и​ Подождите, может кто​ 21 строки в​Еще раз, огромное​
​ можно заполнить, например,​ аргументами.​ операторы математических вычислений​ $буква ячейки$цифра ячейки​: последнйй предложенный макрос​ соответствующие найтройки, то​ столбцы при вводе​ как столбец может​ не знакомы с​ вот зачем там​ «Ячейка не содержит​ With Next End​ подтягиваются данные из​ т.п.)​ другой поможет. Лучше​ ListBox (строки нумеруются​ спасибо!​

​ даты. Если промежутки​​Ссылки в ячейке соотнесены​ и сразу получать​ пример $A$2 значит​ почему-то не хочет​ формулы сами будут​
​ формул в столбцы​ прекратить автоматически обновляться​

​ от формулы есть​​ параметр заполнения формул.​ при добавлении новых​ о таблицах Excel.​Инна Волкова​ On Error Resume​: Здравствуйте уважаемая!​
​ В ячейках 1го​

​MCH​​ этой темы никак​Вот эта команда:​

planetaexcel.ru

Как растянуть формулу на весь столбец в Excel?

​ Формулы нужно обрамлять​ год. Введем в​ ссылается на одну​ ячеек. То есть​ на месте A2​ =ЕСЛИ(ЕПУСТО(B7);»»;C7*B7/100), но отрабатывает​ данные без разрыва​ Если вы не​ строк будет неясно,​Вставьте в таблицу новый​: Так получается, что​ Next Set Cell​Я предлагаю такой​ уровня стоит Автосумма​: =ABS(B$4-B5)+ABS(C$4-C5)+ABS(D$4-D5)+ABS(E$4-E5)+ABS(F$4-F5)+ABS(G$4-G5)+ABS(H$4-H5)+ABS(I$4-I5)​ не связана с​Me.ListBox1.Value = «1»ищет​ тегами. Поправил на​

​ первую ячейку «окт.15»,​​ и ту же​ пользователь вводит ссылку​Маргоша​ просто на =C7*B7/100​ в поле, то​ хотите выключать этот​ какую формулу нужно​

​ столбец. Введите данные​​ макрос убивает и​ = Application.InputBox(«Введите диапазон,​ вариант, выделяем диапазон​ по соответствующим ячейкам​vikttur​ Access.Ну форма я​

​ в ListBox текст​​ первый раз.​ во вторую –​ ячейку. То есть​ на ячейку, со​: а автозаполнение не​ (но забивает нули​

​ достаточно два клика​​ параметр, но не​ к ним применять.​ в столбец справа​ форматы желтых ячеек​ на который будет​ В3:В24, а далее:​ 2го уровня. В​: Почитайте про абсолютные​ думаю от Асесса​ «1», если находит,​

​ST_Senya​​ «ноя.15». Выделим первые​
​ при автозаполнении или​ значением которой будет​ поможет? в правом​ в ячейки, а​ по правому нижнему​ всегда при работе​

​Если вы ввели или​​ от таблицы, и​

Работа в Excel с формулами и таблицами для чайников

​ тоже​ распространена формула», Type:=8)​В ленте «Главная»​ каждой группе (все​ и относительные ссылки.​ не зависит. VBA​ то выделяет элемент,​

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

Формулы в Excel для чайников

​ Excel автоматически расширит​МВТ​ If Err Then​ идем таким путем​ 2го уровня) разное​Закрепите ссылки на​ он один думаю.​ который содержит текст​ к VBA рекомендовали​ «протянем» за маркер​ неизменной (или постоянной).​

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

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

​ можно прекратить автоматическое​ использовать новый вычисляемый​ новый столбец, введя​ Sub tt_2() Dim​ Then Exit Sub​ группу ячеек» ->​ же стоит Автосумма.​

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

​Ссылки можно комбинировать в​Pavel viktorovich​ можно это решить​

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

​ «Итог» в ячейке​ Cell As Range​ Cell.SpecialCells(xlCellTypeBlanks).FormulaR1C1Local _ =​ «Константы»​ На форуме смотрела​: Проблема была в​

​ST_Senya​

1. ​ если есть полоса​Вопрос первый:​ ценами + еще​
2. ​ доллара ($). Проще​ рамках одной формулы​: Выделяешь ячейку с​ через ЕПУСТО)​ выделить столбец -​Включение и выключение вычисляемых​
3. ​Отменить​ D1.​ Dim Formula As​

​ Formula End Sub​Останутся выделены ячейки​ похожие темы, но​ адресации ячеек (см.формулу​,​

• ​ прокрутки, то поднимает​
• ​В связи с​
• ​ одну ячейку. Открываем​

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

​ столбцов​

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

​два раза. Вы​Советы:​ String Dim Rng​Инна Волкова​ без формул, в​ там можно протянуть​ в приложенном файле).​

​ListBox​ ListBox так, чтобы​ импортом таблицы из​ меню кнопки «Сумма»​ с помощью клавиши​Оператор умножил значение ячейки​ за черный крестик​ предложенный макрос, чтобы​Ещё вариант: Скопировать​На вкладке​

1. ​ также можете дважды​ ​ As Range, Ar​: Т.Е этот вариант​
2. ​ этот момент жмити​ с определенным шагом,​ У Вас была​отличается, значит и​ было видно элемент,​ ACCESS в EXCEL​ — выбираем формулу​ F4.​ В2 на 0,5.​ (должен появиться в​ он воспринимал формулу​ формулу — выделить​Файл​
3. ​ нажать клавиши​Вы также можете добавить​ As Range Set​ можно применять к​ «=» пишите свою​ здесь конкретных шагов​ относительная, а на​ ещё что-нибудь может​ содержащий текст «1».​ для рассылки отчётов,​ для автоматического расчета​Создадим строку «Итого». Найдем​

​ Чтобы ввести в​ правом нижнем углу​ с ЕСЛИ? и​ ячейки (Shift+ End​нажмите кнопку​CTRL+Z​ столбец на вкладке​ Rng = ActiveCell​

​ каждому из 50​ формулу и при​ нет. Какие то​ ячейки 4 строки​ отличаться.​ST_Senya​ возник такой вопрос.​ среднего значения.​

​ общую стоимость всех​ формулу ссылку на​

​ ячейки)​ можно ли вообще​ — стрелка вниз)​Параметры​.​Главная​ Formula = Rng.FormulaR1C1Local​

​ столбцов, только где-то​ завершении нажимаете Ctrl+Enter​ строки 2го уровня​ надо ставить абсолютную,​ST_Senya​: Огромное спасибо, вот​ Как распространить формулу​

1. ​Чтобы проверить правильность вставленной​ товаров. Выделяем числовые​ ячейку, достаточно щелкнуть​Evdokia kuznecova​ обойтись без макрсов​ — вставить.​.​
2. ​В вычисляемый столбец можно​. Просто щелкните стрелку​ If Formula =​ должны быть написаны​
3. ​Инна Волкова​ будут как добавляться,так​ т.е. не B4,​, по второму вопросу​ таким кодом всё​ на весь столбец​

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

1. ​Ну и, как​Если вы используете Excel​ включать формулы, отличающиеся​ на кнопке​ «» Then MsgBox​ эти 50 формул​: МВТ, Спасибо за​ и удаляться периодически.​ а $B$4​ (сообщение​
2. ​ получилось!​ в Excel? Поясняю​ по ячейке с​ плюс еще одну​В нашем примере:​ как число с​вариант с двойным​ уже сказал Z,​
3. ​ 2007, нажмите кнопку​ от формулы столбца.​Вставить​ «Ячейка не содержит​ в отдельных ячейках,​ макрос. ВПР Вставила​ По файлу: необходимо​Serge​#1​

​CurrentSpisok.Value = CurrentSpisok.ItemData(i)Теперь​ есть столбец A​ результатом.​

• ​ ячейку. Это диапазон​Поставили курсор в ячейку​ нужным количеством знаков​
• ​ шелчком по крестику​ если рядом есть​
• ​ Office​ Ячейки с такими​

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

​и выберите команду​ формулы», vbInformation Exit​ так?​ вот в таком​ протянуть ВПР в​: Или массивом​) появилась новая информация.​ пользователь будет видеть​ и столбец Б.​

​1kabana​ D2:D9​

1. ​ В3 и ввели​ после запятой. После​ не катит, т.к.​ уже заполненный столбец​и выберите​ формулами становятся исключениями​Вставить столбцы таблицы слева​ Sub End If​Только объясните по​ же виде нужно:​ В3:В24 минуя ячейки​=СУММ(ABS($B$4:$I$4-B5:I5))​
2. ​ Чтобы отобразить выделенный​ какой файл обрабатывается​ Нужно что бы​: Люди добрые, умные​Воспользуемся функцией автозаполнения. Кнопка​ =.​ этого любое число​ соседний стобец заполняется​ — двойной клик​
3. ​Параметры Excel​ и выделяются в​.​ On Error Resume​ поводу диапазона,который нужно​ .Offset(, 1).FormulaR1C1Local =​ В10, В15, В20,​Ввод формулы Ctrl+Shift+Enter​ элемент в ListBox,​ в текущее время.​ в столбце С​ и отзывчивые, помогите​
4. ​ находится на вкладке​Щелкнули по ячейке В2​ будет приведено к​ постепенно и не​ по маркеру ячейки​.​ таблице. Это позволяет​Введите нужную формулу и​

​ Next Set Cell​ прописать в макрос?​ «=ВПР(RC1;R28C1:R36C2;2;ЛОЖЬ)» ? (Сохранила)​ В22. Возможно ли​

exceltable.com

Распространить формулу в УФ на весь столбец (Условное Форматирование/Conditional Formattings)

​Serge​​ когда ListBox имеет​ST_Senya​ считалась Сумма столбцов​
​ :-)​ «Главная» в группе​ – Excel «обозначил»​ указанному вами формату.​ выделится весь нужный​ в правом нижнем​Выберите категорию​
​ выявлять и устранять​ нажмите клавишу​ = Application.InputBox(«Введите диапазон,​ Это должен быть​Затем, при применении​ такое? И Возможно​: Или массивом​ полосу прокрутки, а​:​ A и B.​Как распространить правило​ инструментов «Редактирование».​ ее (имя ячейки​ Причем прописывать информацию​
​ интервал, а выделять​ углу (курсор в​Правописание​
​ несоответствия, возникшие по​ВВОД​

​ на который будет​​ диапазон именно столбца,​ Макроса, пишет Can’t​ ли, при подтягивании​=СУММ(ABS($B$4:$I$4-B5:I5))​ выделенного элемента не​CurrentSpisok​ Всё хочу задать​
​ в условном форматировании​
​После нажатия на значок​ появилось в формуле,​ можете по мере​ весь столбец через​ виде чёрного крестика​
​.​
​ ошибке.​.​
​ распространена формула», Type:=8)​ куда вставить формулу​ execute code in​ ВПР, что бы​Ввод формулы Ctrl+Shift+Enter{/post}{/quote}​ видно, нужно использовать​

​- что это​​ программно. Т.е. способ​ на нижние ячейки,​ «Сумма» (или комбинации​ вокруг ячейки образовался​

​ необходимости. Успехов.​​ ctrl+вниз я думаю​ без стрелочек).​В разделе​Примечание:​В этом случае мы​ If Err Then​ необходимо или можно​ break mode (((​ вставлялись ТОЛЬКО значения,​18,5 Кб​
​ свойство​ за элемент управления?​
​ задание формулы суммы​ не создавая для​ клавиш ALT+«=») слаживаются​ «мелькающий» прямоугольник).​Бамбур​

​ не имеет смыссла,​​Lera​Параметры автозамены​
​ Исключения вычисляемого столбца возникают​ ввели​
​ Exit Sub Application.ScreenUpdating​
​ указать диапазон всех​ Очень бы хотелось​ миную форматы из​Михаил С.​
​TopIndex​ У ListBox нет​
​ для одной ячейки​ каждой ячейки каждый​ выделенные числа и​Ввели знак *, значение​: Может это​

excelworld.ru

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

​ т.к. будет использоваться​​: А у меня​нажмите кнопку​ в результате следующих​
​=СУММ(​ = False If​
​ 50 столбов, но​
​ все же его​ других таблиц?​: В ячейку J5​:​ такого элемента​ столбца С, и​ раз одно и​ отображается результат в​ 0,5 с клавиатуры​Выделяешь несколько необходимых​ только около 7000строк,​ так не работает​Параметры автозамены​ операций.​, а затем выбрали​ Formula = «»​ на каждый выбирать​ сделать рабочим​gling​ формулу​Private Sub CommandButton2_Click()​
​ItemData​ затем перетаскивания мышью​ тоже условие?​ пустой ячейке.​ и нажали ВВОД.​ ячеек и нажимаешь​ а тянуть вручную​ почему-то, пробовала​Откройте вкладку​
​При вводе в ячейку​
​ столбцы​ Then Exit Sub​ нужную формулу из​
​justirus, Ваш вариант​: Здравствуйте.​
​=СУММПРОИЗВ(ABS($B$4:$I$4-B5:I5))​ ‘Если выделена одна​.​ формулы не подходит.​суть его в​Сделаем еще один столбец,​Если в одной формуле​ кнопку «объединить и​ каждый столбец с​2010-ый​Автоформат при вводе​ вычисляемого столбца данных,​Кв1​ Rng.Copy With Cell.SpecialCells(xlCellTypeBlanks)​ 50 заполненных ячеек​ работает )))) Спасибо​
​Сначала с​

​и протянуть вниз​​ или несколько строк​​ST_Senya​
​Как мне это​ том, что если​ где рассчитаем долю​ применяется несколько операторов,​ поставить в центре»​​ формулой на 7000тысяч​
​Serge​.​ отличных от формулы.​и​ .FormulaR1C1Local = Formula​ отдельных?​МВТ​

​кройте автоильтром желтые строки​​Guest​ в ListBox, то​: У меня ACCESS​ осуществить через VBA?​ значение в A1​
​ каждого товара в​ то программа обработает​ ( находиться на​ строк — долго​: Значит что-то не​В разделе​Ввод формулы в ячейку​Кв2​ For Each Ar​

​Просто я сейчас​​: Инна Волкова, Вы​​и протяните формулу​​: Благодарю всех!!!!!!!!!!​ ListIndex — это​ 2007, элемент находится​ Нужные мне данные​
​ < C1 то​ общей стоимости. Для​ их в следующей​​ верхней панели).​​ и нудно.​ так делаете.​Автоматически в ходе работы​ вычисляемого столбца и​. В результате Excel​
​ In .Areas Ar.PasteSpecial​
​ пробовала вписать диапазон​ пытаетесь в столбце​ по всему столбцу,​Ren_5​ выделенная пунктиром строка​ на форме (см​ я уже импортировал​ соответственно у C1​ этого нужно:​ последовательности:​Capatcha​Z​Не работает как?​

​установите или снимите​​ нажатие кнопки​ создал следующую формулу:​ (xlPasteFormats) Next End​
​ столбца, но применять​ В искать столбец​ или скопируйте одну​: Всем привет. Заранее​

​ в ListBox. ‘Если​​ рис. выше), называется​​ в Excel через​​ — зеленая стрелка,​Разделить стоимость одного товара​%, ^;​: Слава Богам!​​: Ваш пример согласно​​ Не заполняет, заполняет​

​ флажок​​Отменить​=СУММ(Таблица1[@[Кв1]:[Кв2]])​ With Application.CutCopyMode =​ могу не только​ В. Напишите, что​ ячейку (Ctrl+C) и​ говорю, что все​ в ListBox ничего​ список. Это графический​ VBA, нужно только​ меньше — красная.​ на стоимость всех​

​*, /;​​Формула предписывает программе Excel​​ правилам, с пояснением​​ не всё, выдаёт​Создать вычисляемые столбцы, заполнив​на​. Такие формулы называются​ False Application.ScreenUpdating =​ к этому столбцу​ и где Вам​

​ выделив столбец вставьте​ погуглил и поиском​​ не выделено, то​​ элемент, где кнопки​​ сделать некоторые вычисления​​ проблема в том​ товаров и результат​+, -.​ порядок действий с​ желаемого, ускорит поиск​ сообщение об ошибке,​
​ таблицы формулами​панели быстрого доступа​ формулами со​ True End Sub​МВТ​ надо найти?​ (Ctrl+V)​ пользовался, но либо​ по умолчанию ListIndex​ и др. элементы​ доп. данных и​ что в У.Ф.​ умножить на 100.​Поменять последовательность можно посредством​ числами, значениями в​ ответа… Списки/ таблицы​ эксель виснет или​, чтобы включить или​.​структурированными ссылками​Инна Волкова​: Второй макрос берет​Инна Волкова​​Пытливый​​ я чего-то не​ — это первая​ управления находятся. Я​ форматирование.​ (усл.форм) нельзя использовать​ Ссылка на ячейку​ круглых скобок: Excel​ ячейке или группе​ не пробовали использовать?​ ещё что?​ выключить этот параметр.​Ввод новой формулы в​​, и их можно​​: а еще вопрос​ формулу из ячейки,​: МВТ, Вот пример.​: Добрый вечер.​ понял, либо пишут​ строка. ‘Но можно​ думал это и​Второй вопрос:​ относительные ссылки на​ со значением общей​ в первую очередь​ ячеек. Без формул​ps Укажите версию​Заодно файл давайте,​»Средства проверки правописания»​ вычисляемый столбец, который​ использовать только в​​ такой: а можно​​ которая была активной​​ нужно, к примеру,​​А где в​ не то что​ назначить и другую​
​ есть ListBox, значит​​Как в listBox​ ячейки и как​​ стоимости должна быть​​ вычисляет значение выражения​ электронные таблицы не​ XL.​ посмотрим нет ли​ > «Параметры автозамены»​ уже содержит одно​ таблицах Excel. Структурированные​ сделать так, что​​ на момент его​​ подтянуть в столбец​
​ примере хоть один​ нужно)​ строку, только визуально​ он не так​ программно выделить элемент?​ следствие нельзя его​ абсолютной, чтобы при​ в скобках.​ нужны в принципе.​Казанский​ там пустых или​ и снятия флажка​ или несколько исключений.​ ссылки позволяют использовать​ бы после подтягивания​ запуска и распространяет​ В, значения из​ ВПР?​

CyberForum.ru

Скажите, как в Excel задать функцию на весь столбец

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

​Конструкция формулы включает в​​: Поправьте:​ объединённых ячеек в​ «Создать вычисляемые столбцы,​При копировании в вычисляемый​

​ одну и ту​​ ВПР, там где​ на пустые ячейки​
​ нижней таблицы​А так-то -​ несколько тысяч строк,​ что это строка​

​ST_Senya​​listbox.Selected(номер элемента) =​Есть какое-то решение​ неизменной.​Различают два вида ссылок​ себя: константы, операторы,​Selection.Resize(InputBox(«На сколько строк​ столбце или может​ заполнив таблицы формулами».»​ столбец данных, которые​ же формулу в​ ВПР в итоге​

как введенную формулу расчетов распространить на весь столбец

​ диапазона, который Вы​​gling​ думаю, только макросом​ последний столбец цена,​ ListIndex Me.ListBox1.TopIndex =​:​ True​ без макроса и​Чтобы получить проценты в​ на ячейки: относительные​ ссылки, функции, имена​ заполнить?»), 1).Formula =​ ещё чего.​ />​ не соответствуют формуле​ каждой строке. Обычная​
​ остались только значения.​ выберете, примерно, как​: Здравствуйте. Наверно формула​ можно воплотить.​ в столбец который​ Me.ListBox1.ListIndex End Sub​ST_Senya​Всё выделяется замечательно.​ гипер сложного VBA?​ Excel, не обязательно​ и абсолютные. При​ диапазонов, круглые скобки​ Selection.Formula​Lera​
​Совет:​ вычисляемого столбца.​

​ формула Excel выглядела​​ Т.е. я обычно​

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

​ Но если есть​​Во вложенном файле​ умножать частное на​ копировании формулы эти​ содержащие аргументы и​Кстати, зачем ограничиваться​: Спасибо, сделала копи-пастом​  Вы можете также​Примечание:​ бы как​

​ выделяла диапазоны с​​ формулу за уголок​
​ «=VLOOKUP(RC1,R28C1:R36C2,2,FALSE)»или заменить строку​
​: gling, дело в​

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

​ 100. Выделяем ячейку​​ ссылки ведут себя​ другие формулы. На​
​ одним столбцом? Можно​
​вот я туплю,​

​ щелкнуть кнопку​​ Если скопированные данные содержат​

planetaexcel.ru

Как распространить формулу на текущий столбец?

​=СУММ(B2:C2)​​ ВПР (что опять​Инна Волкова​.Offset(, 1).FormulaR1C1 =​ том, что первоначально​ 18% (это все​ и приходится тянуть​ где можно посмотреть​ в списке много​
​_Boroda_​ с результатом и​ по-разному: относительные изменяются,​ примере разберем практическое​ заполнить несколько столбцов,​ простите​Параметры автозамены​ формулу, она заменяет​, и ее было​ же не удобно,​: МВТ, Круто! А​ «=VLOOKUP(RC1,R28C1:R36C2,2,FALSE)»​ так и делала,​ понятно как делать)​ мышкой эту функцию​ класс элемента управления,​ элементов. Такое выделение​: Именно так, ка​ нажимаем «Процентный формат».​ абсолютные остаются постоянными.​ применение формул для​ скопировав формулу из​Lera​, которая отобразится в​

​ данные в вычисляемом​​ бы необходимо добавить​ пропускать приходится желтые​ формат ячейки меняется​МВТ​ но после отображения​ и распространить эту​ от начала до​
​ под номером 2​ не видно, т.е.​ Вы хотите -​ Или нажимаем комбинацию​Все ссылки на ячейки​ начинающих пользователей.​ первой строки выделения:​: А у меня​

​ столбце таблицы после​​ столбце.​ в остальные ячейки​ строки) копировала и​ (если брать пример​

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

​ конца. Подскажите, ведь​​ — класс элемента​ оно по факту​ нет, а вот​ горячих клавиш: CTRL+SHIFT+5​

planetaexcel.ru

Формула для всего столбца с пропуском некоторых ячеек

​ программа считает относительными,​​Чтобы задать формулу для​Selection.Resize(InputBox(«На сколько строк​ в любом файле​ ввода формулы. Нажмите​При удалении формулы из​ путем копирования и​ вставляла Значения. Ну​
​ ВПР, то меняет​ (диапазон легко поменять​ что даже при​ столбца. То что​ наверняка есть кнопка..​ управления.​ то есть, но​ так, как сделано​Копируем формулу на весь​ если пользователем не​ ячейки, необходимо активизировать​ заполнить?»)).Formula = Selection.Rows(1).Formula​ не работает. Либо​ кнопку Параметры автозамены,​ одной или нескольких​ вставки или заполнения.​ потому как файлы​ на тот формат​) Sub tt() Dim​ таком фильтре, протягивается​ можно дважды кликнуть​Юрий попов​Я его перетащил​ пользователь его не​ в файле в​ столбец: меняется только​ задано другое условие.​ ее (поставить курсор)​Velem​ я не так​ а затем снимите​ ячеек вычисляемого столбца.​Дополнительные сведения о структурированных​ с таблицами откуда​ ячейки из которой​

​ Cell As Range​​ ВПРка и на​​ на черный квадратик​​: Копируешь формулу, выделяешь​​ отсюда…​ будет видеть, пока​ самом первом посте​ первое значение в​ С помощью относительных​ и ввести равно​

​: спасибо, Казанский!​​ делаю. Когда навожу​
​ флажок Создать вычисляемые​Примечание:​ ссылках см. в​
​ подтягиваются значения потом​ он значения вытягивает)​ Range(«B2:B24»).SpecialCells(xlCellTypeBlanks).FormulaR1C1Local _ =​

​ желтые строки((((Я не​​ справа или выделить​ столбец, нажимаешь вставить.​ST_Senya​ не пролиснёт саму​ по ссылке​ формуле (относительная ссылка).​ ссылок можно размножить​ (=). Так же​еще, на будущее,​ в правый нижний​ столбцы, заполнив таблицы​ В этом случае исключение​ статье Использование структурированных​ удаляются, сл-но, открыв,​ или остается тот​ «=ВПР(RC1;R28C1:R36C2;2;ËÎÆÜ)» End Sub​ стала перегружать Пример​
​ в ручную столбец,​ Правда формула пропишется​, свойство​ полосу прокрутки. Как​http://www.excelworld.ru/forum/2-22519-1​

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

​сделать можно. Я​​ остается прежним. Проверим​ же формулу на​ равенства в строку​ такую модификацию, в​ квадратик, появляется крестик​ этот параметр.​При удалении или перемещении​ Excel.​ ПК в ячейках​ был, до вставки​: МВТ, О! Супер!​ вопрос не только​ курсе, и сейчас​Александр​содержит название класса,​ бы даже с​ уже где-то писал​ правильность вычислений –​ несколько строк или​ формул. После введения​ которой бы я​ (который для растягивания).​

​Если вы используете Mac,​​ ячейки в другую​
​При нажатии клавиши ВВОД​ НД выскакивает.​ формулы?​
​ Все работает. Спасибо​ в формуле ВПР,​ так и делаю,​: что за функция…​ когда элемент управления​ учётом полос прокрутки​
​ объяснялку про это,​ найдем итог. 100%.​ столбцов.​ формулы нажать Enter.​ выбирал сначала ячейки​ Кликаю два раза​

​ выберите​​ область листа, на​ формула будет автоматически​Михаил Лебедев​МВТ​А скажите, вот​ но там же​
​ но мне нужно​ ?​ только создали. Какое​ всё отображалось корректно.​ если не поймете​ Все правильно.​Вручную заполним первые графы​
​ В ячейке появится​ с формулами из​

​ — ничего не​​Excel​ которую ссылается одна​ применена ко всем​: На другом ПК​: Формат не трогает,​ если у меня​

​ можно и другую​​ это действие вписать​в том месте​ имя даётся новому​Заранее всем спасибо​ — спрашивайте.​

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

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

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

​ формулы сами на​​ делать, причем, довольно​ мне копировать 50​ вставлять.​ это делалось автоматически,​ будете и напишите​ вы вставляете?​:​Смотрите УФ, формат,​ ссылок:​ вариант:​ математические операторы:​ на которые нужно​Serge​Параметры​Уведомление об ошибке отображается​ снизу от активной​ значения поменяются ☺​ муторно. Каждый компонент​ таких макросов под​Вопрос в том,​ а он такие​ диапазон выбираемых ячеек…​У вас вообще​Вопрос первый:​ вкладка Число​$В$2 – при копировании​Вспомним из математики: чтобы​Оператор​ было бы растянуть,​: Смотрим видео:​>​ только в том​ ячейки. Для каждой​МВТ​ формата (имя шрифта,​ каждый столбец? Т.Е​ как сделать пропуски​ действия не видит.​Илья гасников​ не элементу управления,​Sub Макрос1() Range(«C1»).Formula​Предположу, что Вы​ остаются постоянными столбец​ найти стоимость нескольких​Операция​ чтобы не тыркать​Lera​Формулы и списки​ случае, если включена​ строки используется одна​

​: Инна Волкова, макросом​​ толщина, размера, цвет​ в каждом столбце​ на желтые строки​Юрий М​: Становитесь на первую​ а элемент формы.Тогда​ = «=A1+B1» ‘Записано​ немного перепутали и​
​ и строка;​ единиц товара, нужно​Пример​ в каждую формулу​: Да, только эта​>​ фоновая проверка ошибок.​ и та же​ можно. Как-то так​ и т.д., цвет​ будет ВПРка из​ при протягивании формул​: Если макросом, то​
​ ячейку. Нажимаете ctrl+shift+вниз.​ пардон…. Значит не​ с помощью макрорекордера:​ Вам нужно не​B$2 – при копировании​

​ цену за 1​​+ (плюс)​ по отдельности?​ штука работает, если​Таблицы и фильтры​ Если вы не​ формула, но поскольку​ Sub mm() With​ фона, обрамление) необходимо​ разных таблиц​ на столбец))))​

​ зачем же формулу​​ Потом Ctrl+d. Tckb​ так выразился. Значит​ Range(«C1»).AutoFill Destination:=Range(«C1:C10»), Type:=xlFillDefault​ А и С,​ неизменна строка;​ единицу умножить на​Сложение​в приложенном файле​ у тебя в​>​ видите сообщение об​

​ это структурированная ссылка,​​ Selection .Formula =​ задавать отдельно. Но​МВТ​gling​ вставлять? Пусть макрос​ некорректно выделились нужные​ элемент формы. В​ End Sub​ а К и​$B2 – столбец не​
​ количество. Для вычисления​=В4+7​ примерчик с двумя​ одном столбце заполнены​Автоматически заполнять формулы​ ошибке, откройте​ Excel знает, на​ .Value End With​ теоретически сделать можно​: Инна Волкова, вот,​: При протягивании и​ и считает. Да​ ячейки, то становитесь​ имени нет никакого​Второй вопрос:​ J​ изменяется.​ стоимости введем формулу​- (минус)​ формулами (в реале​ строки далеко вниз.​.​Файл​ что она ссылается​ End Sub​UPD вот, придумал​ немного допилил, попробуйте​ полном копировании ячеек​ и в ячейках​ на первую ячейку,​ класса. Да и​Private Sub CommandButton2_Click()​

​gling​​Чтобы сэкономить время при​ в ячейку D2:​Вычитание​​ их больше намного)​​ Тогда в следующем​

​Прекращение автоматического создания вычисляемых​​>​ в каждой строке.​Инна Волкова​ Sub tt_2() Dim​

​ (исправил опечатку) Sub​​ такого о чем​ будут сразу значения​ ползунком слева переходите​ бог с ним)​ ‘Передвигаемся примерно туда,​: Не понял, что​ введении однотипных формул​ = цена за​=А9-100​Guest​ столбце при двойном​ столбцов​Параметры​Примечания:​: МВТ, это все​ Cell As Range​ tt() Dim Cell​ вы пишите не​ ))​ вниз на нужную​ Главное всё получилось.​ куда надо. Me.ListBox1.Value​ дату нужно сравнить​ в ячейки таблицы,​ единицу * количество.​* (звездочка)​: Повторение пройденного… ;)​ клике на крестик​После ввода в столбец​>​ ​ формулы «рубит», а​ Dim Formula As​

​ As Range Dim​​ замечал. Знаю если​Можно циклом пробежаться​ и нажимает левую​ Возник следующий вопрос.​ = 20 ‘Выделяем​ с текстом? Это​ применяются маркеры автозаполнения.​ Константы формулы –​Умножение​ Список (2003), таблица​ формула тянется до​ таблицы первой формулы​Формулы​При копировании формулы во​ что бы только​ String Dim Rng​ Formula On Error​ скопировать ячейку и​ и посчитать, а​ кнопку мышки зажав​ Как мне отцентровать​

​ нужную строку. Me.ListBox1.Selected(15)​​ как?​ Если нужно закрепить​ ссылки на ячейки​=А3*2​

​ (07/10)… Добавили строку​​ той же строки​ нажмите отобразившуюся кнопку​и убедитесь, что​ все ячейки пустого​ ВПР?​ As Range, Ar​

​ Resume Next Set​​ выделив диапазон со​ можно и без​ shift.​ мои формы? а​

planetaexcel.ru

​ = True End​

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

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

Что такое формула?

Формулы — это некоторые выражения, выполняющие вычисления между операндами при помощи операторов. Формулам всегда предшествует знак равенства, за которым следуют операнды и операторы.

Операнды — это элементы вычисления (ссылки, функции и константы ).

Ссылки — это адреса ячеек или их диапазонов.

Функции — это заранее созданные формулы, выполняющие сложные вычисления с введенными значениями (аргументами) в определенном порядке. Различают математические, статистические, текстовы, логические и другие категории функций.

Константы — это постоянные значения, как текстовые, так и числовые.

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

Ссылки в формулах

Для создания связей между ячейками используются ссылки. Различают три типа ссылок — относительные, абсолютные и смешанные. По умолчанию в Excel используются относительные ссылки.

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

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

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

Абсолютная ссылка — это неизменяемая ссылка на ячейку, то есть при изменении позиции ячейки с формулой адрес ячейки с абсолютной ссылкой остается неизменным. Абсолютная ссылка указывается символом $ перед именем (номером) столбца и перед номером строки, например $A$1.

Смешанные ссылки на ячейки

Смешанная ссылка — это комбинация относительных и абсолютных ссылок, когда используется либо абсолютная ссылка на столбец и относительная на строку, либо абсолютная на строку и относительная на столбец, например $A1 или A$1. При изменении позиции ячейки с формулой, содержащей смешанные ссылки, относительная часть ссылки изменяется, а абсолютная остается неизменной.

Трехмерные ссылки на ячейки

Трехмерные ссылки — это ссылки на одну и ту же ячейку или даипазон ячеек, расположенных на нескольких листах одной книги. Трехмерная ссылка кроме имени столбца и номера строки включает в себя имя листа и имеет следующий вид Лист1:Лист3!А1.

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

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

Простые формулы

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

Для того чтобы создать простую формулу, необходимо:

— выделить ячейку, в которой будет находиться формула;

— ввести с клавиатуры символ равно (=);

— ввести число, затем знак действия, затем следующее число и так далее (например =2+3*4);

— нажать Enter для перехода вниз, Shift+Enter для перехода вверх, Tab для перехода вправо или Shift+Tab для перехода влево.

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

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

— выделить ячейку, в которой будет находиться формула;

— ввести символ равенства (=) с клавиатуры;

— ввести адрес ячейки, содержащей нужное значение (можно кликнуть курсором мыши по нужной ячейке);

— вставить в формулу оператор, ввести адрес следующей ячейки и так далее;

— завершить создание формулы аналогично тому, как это описано в предыдущем случае.

Формулы с использованием абсолютных ссылок

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

— выделить ячейку, в которой будет находится формула;

— ввести символ равенства (=) с клавиатуры;

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

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

— нажать на клавиатуре F4;

— завершить создание формулы клавишей Enter.

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

Для ввода одной формулы в диапазон ячеек необходимо:

— выделить диапазон ячеек;

— ввести формулу в первую ячейку диапазона;

— закрепить результат сочетанием клавиш Ctrl+Enter.

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

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

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

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

Скопировать формулу из выбранной ячейки можно любым известным способом (при помощи кнопки «Копировать» на вкладке «Главная», при помощи сочетания горячих клавиш Ctrl+C, при помощи пункта «Копировать» в контекстном меню и так далее). После того как формула скопирована, необходимо выделить ячейку, в которую нужно вставить формулу и использовать любой известный способ вставки (кнопкой «Вставить» на вкладке «Главная», сочетанием горячих клавиш Ctrl+V, выбрав пункт «Вставить» из контекстного меню, выбрав пункт «Специальная вставка»). После этого закрепить результат кликом по клавише Enter. Для копирования формулы можно использовать также способ, при котором курсор мыши наводится на правый нижний угол маркера выделения до появления тонкого черного крестика и при нажатой левой кнопке мыши протягивается по всему диапазону. При этом в каждой следующей ячейке формула будет иметь ссылки на новые соответствующие ячейки.

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

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

Если скопировать ячейку или диапазон ячеек с формулами, а вставку осуществить при помощи пункта «Вставить значения» (вкладка «Главная»/группа «Буфер обмена»/кнопка «Вставить», либо контекстное меню «Специальная вставка»/»Значения»), то в результате этой операции вместо формул будут отображены значения, полученные в результате вычисления этих формул. Если скопировать диапазон ячеек с формулами и в этот же диапазон вставить значения, то формулы этого диапазона будут заменены результатами их вычислений.

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

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

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

Надстройка позволяет:

1. Одним кликом мыши вызывать диалоговое окно макроса прямо из панели инструментов Excel;

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

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

— «Скопировать формулы» — простое копирование формул, при котором все ссылки, используемые в формулах, автоматически изменяются в соответствии с новым размещением формул;

— «Скопировать текст формул» — точное копирование формул, без изменения ссылок, используемых в формулах;

— «Скопировать значения формул» — копирование, при котором формулы заменяется результатамм их вычислений.

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

Перемещение и копирование формулы

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Чтобы вставить только формулу, надстройку группу » буфер обмена » на вкладке » Главная «, нажмите кнопку Вставить, выберите команду Специальная Вставкаи нажмите кнопку формулы.

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

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

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

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

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

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

Excel. Перемещение формул без изменения относительных ссылок

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

К сожалению, стандартные средства Excel не позволяют выполнить подобное копирование. Что вообще-то говоря, удивительно! Попробуйте, например, перенести формулу =В1+С1, хранящуюся в ячейке D1, в ячейку D4 (рис. 1). Если выполнить копирование с помощью специальной вставки и опции вставить формулы, в ячейке D4 обнаружите формулу =В4+С4.

Рис. 1. Специальная вставка; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Скачать заметку в формате Word или pdf, примеры в формате Excel

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

Выделите диапазон ячеек, который хотите скопировать. В нашем примере это С4:С13 (область 1 на рис. 2), и выберите команду Главная → Найти и выделить → Заменить (область 2 на рис. 2), или нажмите Ctrl + H (английская H).

Рис. 2. Найти и заменить

В открывшемся диалоговом окне «Найти и заменить» (рис. 3) в поле «Найти» введите знак = (с него начинаются все формулы). В поле «Заменить на» введите знаки && или любой иной символ который, как вы уверены, не используется ни в одной из формул. Нажмите «Заменить все».

Рис. 3. Заменить знак = на знаки &&

Во всех формулах на рабочем листе вместо знака равенства теперь стоит && (рис. 4).

Рис. 4. После замены

Скопируйте ячейки С4:С13 в требуемое место, и выполните обратную замену всех && на =. И первоначальные, и новые формулы ссылаются на одни и те же ячейки (рис. 5), причем формулы используют относительные ссылки, то есть их можно «протягивать».

Рис. 5. Формулы удалось перенести

Дополнение от 1 октября 2016

Еще один вариант решения проблемы можно найти у Джона Уокенбаха. [1] Переключите Excel в режим просмотра формул, пройдя по меню Формулы –> Зависимости формул –> Показывать формулы (рис. 6). Выделите диапазон для копирования. В данном примере – С4:С13. Скопируйте его в буфер. Откройте текстовый редактор, например, Word или Блокнот. Вставьте скопированные данные. Выделите весь текст, и снова скопируйте его в буфер. Вернитесь в Excel и активизируйте верхнюю левую ячейку диапазона, в который хотите вставить ваши формулы. Убедитесь, что лист, на который копируются данные, находится в режиме просмотра формул. Вставьте формулы. Выйдете из режима показа формул, повторно пройдя по меню пройдя по меню Формулы –> Зависимости формул –> Показывать формулы. Формулы в целевом диапазоне будут ссылаться на те же ячейки, что и в исходном.

Рис. 6. Режим Показывать формулы

Примечание. В некоторых случаях операция вставки в Excel выполняется с ошибкой и программа разбивает формулу на две и более ячейки. Если так происходит, то, возможно, недавно вы пользовались функцией Excel Текст по столбцам и приложение напоминает вам, как данные разбирались при последнем сеансе. Откройте Мастер распределения текста по столбцам и измените параметры. Выполните команду Данные –> Работа с данными –> Текст по столбцам. В диалоговом окне Мастера распределения текста по столбцам выберите С разделителями и нажмите Далее. Снимите флажки со всех вариантов разделителей, кроме варианта знак табуляции, и нажмите Отмена. После этих изменений формулы будут вставляться правильно.

[1] Джон Уокенбах. Excel 2013. Трюки и советы. – СПб.: Питер, 2014. – С. 144, 145.

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

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

Проблема в том, что если скопировать диапазон D2:D8 с формулами куда-нибудь в другое место на лист, то Microsoft Excel автоматически скорректирует ссылки в этих формулах, сдвинув их на новое место и перестав считать:

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

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

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

Чтобы формулы при копировании не менялись, надо (временно) сделать так, чтобы Excel перестал их рассматривать как формулы. Это можно сделать, заменив на время копирования знак «равно» (=) на любой другой символ, не встречающийся обычно в формулах, например на «решетку» (#) или на пару амперсандов (&&). Для этого:

1. Выделяем диапазон с формулами (в нашем примере D2:D8)
2. Жмем Ctrl+H на клавиатуре или на вкладке Главная — Найти и выделить — Заменить (Home — Find&Select — Replace)