Как скопировать формулу в excel во все листы книги - Word и Excel

Вставить формулу на всех листах

Kroha-ua

Дата: Вторник, 23.12.2014, 15:13 |
Сообщение № 1

Группа: Пользователи

Ранг: Новичок

Сообщений: 19

Репутация:
0
±

Замечаний:
20% ±

Excel 2013

Собственно, имеем книгу сбольшим количеством листов с названием 1, 2, 3, 4 и т.д… и нужно в каждый лист вставить одну и туже самую формую в определенную ячейку… как это сделать!?

Ответить

SLAVICK

Дата: Вторник, 23.12.2014, 15:26 |
Сообщение № 2

Группа: Модераторы

Ранг: Старожил

Сообщений: 2290

Репутация:
766
±

Замечаний:
0% ±

2019

Правой кнопкой мыши на ярлыке любого листа: «выделить все листы» — потом пишите формулу — она появится на всех листах

Иногда все проще чем кажется с первого взгляда.

Ответить

Kroha-ua

Дата: Вторник, 23.12.2014, 15:39 |
Сообщение № 3

Группа: Пользователи

Ранг: Новичок

Сообщений: 19

Репутация:
0
±

Замечаний:
20% ±

Excel 2013

Все так просто, спасибо!

Ответить

Источник

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

Проблема

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

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

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

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

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

При большом количестве ячеек этот вариант, понятное дело, отпадает — слишком трудоемко.

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

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

Выделяем диапазон с формулами (в нашем примере D2:D8)
Жмем Ctrl+H на клавиатуре или на вкладке Главная — Найти и выделить — Заменить (Home — Find&Select — Replace)
В появившемся диалоговом окне вводим что ищем и на что заменяем и в Параметрах (Options) не забываем уточнить Область поиска — Формулы. Жмем Заменить все (Replace all).
Копируем получившийся диапазон с деактивированными формулами в нужное место:
Заменяем # на = обратно с помощью того же окна, возвращая функциональность формулам.

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

Этот способ существенно быстрее и проще.

Нажмите сочетание клавиш Ctrl+Ё или кнопку Показать формулы на вкладке Формулы (Formulas — Show formulas), чтобы включить режим проверки формул — в ячейках вместо результатов начнут отображаться формулы, по которым они посчитаны:

Скопируйте наш диапазон D2:D8 и вставьте его в стандартный Блокнот:

Теперь выделите все вставленное (Ctrl+A), скопируйте в буфер еще раз (Ctrl+C) и вставьте на лист в нужное вам место:

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

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

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

Если подобное копирование формул без сдвига ссылок вам приходится делать часто, то имеет смысл использовать для этого макрос. Нажмите сочетание клавиш Alt+F11 или кнопку Visual Basic на вкладке Разработчик (Developer), вставьте новый модуль через меню Insert — Module и скопируйте туда текст вот такого макроса:

Sub Copy_Formulas()
    Dim copyRange As Range, pasteRange As Range
    
    On Error Resume Next
    Set copyRange = Application.InputBox("Выделите ячейки с формулами, которые надо скопировать.", _
                                "Точное копирование формул", Default:=Selection.Address, Type:=8)
    If copyRange Is Nothing Then Exit Sub
    Set pasteRange = Application.InputBox("Теперь выделите диапазон вставки." & vbCrLf & vbCrLf & _
                                          "Диапазон должен быть равен по размеру исходному " & vbCrLf & _
                                          "диапазону копируемых ячеек.", "Точное копирование формул", _
                                          Default:=Selection.Address, Type:=8)
    
    If pasteRange.Cells.Count <> copyRange.Cells.Count Then
        MsgBox "Диапазоны копирования и вставки разного размера!", vbExclamation, "Ошибка копирования"
        Exit Sub
    End If
    
    If pasteRange Is Nothing Then
        Exit Sub
    Else
        pasteRange.Formula = copyRange.Formula
    End If
End Sub

Для запуска макроса можно воспользоваться кнопкой Макросы на вкладке Разработчик (Developer — Macros) или сочетанием клавиш Alt+F8. После запуска макрос попросит вас выделить диапазон с исходными формулами и диапазон вставки и произведет точное копирование формул автоматически:

Ссылки по теме

Удобный просмотр формул и результатов одновременно
Зачем нужен стиль ссылок R1C1 в формулах Excel
Как быстро найти все ячейки с формулами
Инструмент для точного копирования формул из надстройки PLEX

Источник

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

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

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

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

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

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

Вы выберите ячейку, содержащую формулу, которую нужно скопировать.
В группе Буфер обмена на вкладке Главная нажмите кнопку Копировать.
Выполните одно из указанных ниже действий.
- Чтобы вировать формулу и любое форматирование, в группе Буфер обмена на вкладке Главная нажмите кнопку В виде вкладки.
- Чтобы вировать только формулу, в группе Буфер обмена на вкладке Главная нажмите кнопку Вировать ,выберите специальная ветвь ,а затем щелкните Формулы.
  
  Примечание: В нее можно вклеить только результаты формулы. В группе Буфер обмена на вкладке Главная нажмите кнопку Вировать, выберите специальная ветвь ,а затем щелкните Значения.
Убедитесь, что ссылки на ячейки в формуле дают нужный результат. При необходимости переключить тип ссылки можно следующим образом:
1. Выделите ячейку с формулой.
2. В строке формул строка формул выделите ссылку, которую нужно изменить.
3. Для переключения между сочетаниями нажмите F4.
  
  В таблице подводится информация об обновлении типа ссылки при копировании формулы, содержащей ссылку, на две ячейки вниз и на две ячейки вправо.

Копируемая формула	Первоначальная ссылка	Новая ссылка
	$A$1 (абсолютный столбец и абсолютная строка)	$A$1
	A$1 (относительный столбец и абсолютная строка)	C$1
	$A1 (абсолютный столбец и относительная строка)	$A3
	A1 (относительный столбец и относительная строка)	C3

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

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

Вы выберите ячейку с формулой, которую вы хотите переместить.
Щелкните Главная > вырезать (или нажмите CTRL+X).
Выйдите из ячейки, в которая должна вться формула, и нажмите кнопку Вировать (или нажмите CTRL+V).
Убедитесь, что ссылки на ячейки остаются нужными.

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

Источник

Download Article

Using Find and Replace

Filling a Column or Row

Pasting a Formula into Multiple Cells

Using Relative and Absolute Cell References

Video

Tips

Warnings

Excel makes it easy to copy your formula across an entire row or column, but you don’t always get the results you want. If you end up with unexpected results, or those awful #REF and /DIV0 errors, it can be extremely frustrating. But don’t worry—you won’t need to edit your 5,000 line spreadsheet cell-by-cell. This wikiHow teaches you easy ways to copy formulas to other cells.

Open your workbook in Excel. Sometimes, you have a large spreadsheet full of formulas, and you want to copy them exactly. Changing everything to absolute cell references would be tedious, especially if you just want to change them back again afterward. Use this method to quickly move formulas with relative cell references elsewhere without changing the references.^[1] In our example spreadsheet, we want to copy the formulas from column C to column D without changing anything.

Example Spreadsheet

	Column A	Column B	Column C
row 1	944	Frogs	=A1/2
row 2	636	Toads	=A2/2
row 3	712	Newts	=A3/2
row 4	690	Snakes	=A4/2

If you’re just trying to copy the formula in a single cell, skip to the last step («Try alternate methods») in this section.

2

Press Ctrl+H to open the Find window. The shortcut is the same on Windows and macOS.

Advertisement

Find and replace «=» with another character. Type «=» into the «Find what» field, and then type a different character into the «Replace with» box. Click Replace All to turn all formulas (which always begin with an equal’s sign) into text strings beginning with some other character. Always use a character that you have not used in your spreadsheet. For example, replace it with # or &, or a longer string of characters, such as ##&.

Example Spreadsheet

	Column A	Column B	Column C
row 1	944	Frogs	##&A1/2
row 2	636	Toads	##&A2/2
row 3	712	Newts	##&A3/2
row 4	690	Snakes	##&A4/2

Do not use the characters * or ?, since these will make later steps more difficult.

Copy and paste the cells. Highlight the cells you want to copy, and then press Ctrl + C (PC) or Cmd + C (Mac) to copy them. Then, select the cells you want to paste into, and press Ctrl + V (PC) or Cmd + V (Mac) to paste. Since they are no longer interpreted as formulas, they will be copied exactly.

Example Spreadsheet

	Column A	Column B	Column C	Column D
row 1	944	Frogs	##&A1/2	##&A1/2
row 2	636	Toads	##&A2/2	##&A2/2
row 3	712	Newts	##&A3/2	##&A3/2
row 4	690	Snakes	##&A4/2	##&A4/2

Use Find & Replace again to reverse the change. Now that you have the formulas where you want them, use «Replace All» again to reverse your change. In our example, we’ll look for the character string «##&» and replace it with «=» again, so those cells become formulas once again. You can now continue editing your spreadsheet as usual:

Example Spreadsheet

	Column A	Column B	Column C	Column D
row 1	944	Frogs	=A1/2	=A1/2
row 2	636	Toads	=A2/2	=A2/2
row 3	712	Newts	=A3/2	=A3/2
row 4	690	Snakes	=A4/2	=A4/2

6
Try alternate methods. If the method described above doesn’t work for some reason, or if you are worried about accidentally changing other cell contents with the «Replace all» option, there are a couple other things you can try:
- To copy a single cell’s formula without changing references, select the cell, then copy the formula shown in the formula bar near the top of the window (not in the cell itself). Press Esc to close the formula bar, then paste the formula wherever you need it.
- Press Ctrl and ` (usually on the same key as ~) to put the spreadsheet in formula view mode. Copy the formulas and paste them into a text editor such as Notepad or TextEdit. Copy them again, then paste them back into the spreadsheet at the desired location. Then, press Ctrl and ` again to switch back to regular viewing mode.

1

Type a formula into a blank cell. Excel makes it easy to propagate a formula down a column or across a row by «filling» the cells. As with any formula, start with an = sign, then use whichever functions or arithmetic you’d like. We’ll use a simple example spreadsheet, and add column A and column B together. Press Enter or Return to calculate the formula.

Example Spreadsheet

Column A Column B Column C

row 1
10

9

19

row 2
20

8

row 3
30

7

row 4
40

6
2

Click the lower right corner of the cell with the formula you want to copy. The cursor will become a bold + sign.
3
Click and drag the cursor across the column or row you’re copying to. The formula you entered will automatically be entered into the cells you’ve highlighted. Relative cell references will automatically update to refer to the cell in the same relative position rather than stay exactly the same. Here’s our example spreadsheet, showing the formulas used and the results displayed:

Example Spreadsheet

Column A Column B Column C

row 1
10

9

=A1+B1

row 2
20

8

=A2+B2

row 3
30

7

=A3+B3

row 4
40

6

=A4+B4

Example Spreadsheet

Column A Column B Column C

row 1
10

9

19

row 2
20

8

28

row 3
30

7

37

row 4
40

6

46
- You can also double-click the plus sign to fill the entire column instead of dragging. Excel will stop filling out the column if it sees an empty cell. If the reference data contains a gap, you will have to repeat this step to fill out the column below the gap.
- Another way to fill the entire column with the same formula is to select the cells directly below the one containing the formula and then press Ctrl + D.^[2]

	Column A	Column B	Column C
row 1	10	9	19
row 2	20	8
row 3	30	7
row 4	40	6

	Column A	Column B	Column C
row 1	10	9	=A1+B1
row 2	20	8	=A2+B2
row 3	30	7	=A3+B3
row 4	40	6	=A4+B4

	Column A	Column B	Column C
row 1	10	9	19
row 2	20	8	28
row 3	30	7	37
row 4	40	6	46

1

Type the formula into one cell. As with any formula, start with an = sign, then use whichever functions or arithmetic you’d like. We’ll use a simple example spreadsheet, and add column A and column B together. When you press Enter or Return, the formula will calculate.

Example Spreadsheet

Column A Column B Column C

row 1
10

9

19

row 2
20

8

row 3
30

7

row 4
40

6
2

Select the cell and press Ctrl+C (PC) or ⌘ Command+C (Mac). This copies the formula to your clipboard.
3

Select the cells you want to copy the formula to. Click on one and drag up or down using your mouse or the arrow keys. Unlike with the column or row fill method, the cells you are copying the formula to do not need to be adjacent to the cell you are copying from. You can hold down the Control key while selecting to copy non-adjacent cells and ranges.
4

Press Ctrl+V (PC) or ⌘ Command+V (Mac) to paste. The formulas now appear in the selected cells.

	Column A	Column B	Column C
row 1	10	9	19
row 2	20	8
row 3	30	7
row 4	40	6

1

Use a relative cell reference in a formula. In an Excel formula, a «cell reference» is the address a cell. You can type these in manually, or click on the cell you wish to use while you are entering a formula. For example, the following spreadsheet has a formula that references cell A2:

Relative References

Column A Column B Column C

row 2
50

7

=A2*2

row 3
100

row 4
200

row 5
400
2
Understand why they’re called relative references. In an Excel formula, a relative reference uses the relative position of a cell address. In our example, C2 has the formula “=A2”, which is a relative reference to the value two cells to the left. If you copy the formula into C4, then it will still refer to two cells to the left, now showing “=A4”.

Relative References

Column A Column B Column C

row 2
50

7

=A2*2

row 3
100

row 4
200

=A4*2

row 5
400
- This works for cells outside of the same row and column as well. If you copied the same formula from cell C1 into cell D6 (not shown), Excel would change the reference «A2» to a cell one column to the right (C→D) and 5 rows below (2→7), or «B7».
3
Use an absolute reference instead. Let’s say you don’t want Excel to automatically change your formula. Instead of using a relative cell reference, you can make it absolute by adding a $ symbol in front of the column or row that you want to keep the same, no matter where you copy the formula too.^[3] Here are a few example spreadsheets, showing the original formula in larger, bold text, and the result when you copy-paste it to other cells:
- Relative Column, Absolute Row (B$3): The formula has an absolute reference to row 3, so it always refers to row 3:
  
  Column A Column B Column C
  
  row 1
  50
  
  7
  
  =B$3
  
  row 2
  100
  
  =A$3
  
  =B$3
  
  row 3
  200
  
  =A$3
  
  =B$3
  
  row 4
  400
  
  =A$3
  
  =B$3
- Absolute Column, Relative Row ($B1): The formula has an absolute reference to column B, so it always refers to column B.
  
  Column A Column B Column C
  
  row 1
  50
  
  7
  
  =$B1
  
  row 2
  100
  
  =$B2
  
  =$B2
  
  row 3
  200
  
  =$B3
  
  =$B3
  
  row 4
  400
  
  =$B4
  
  =$B4
- Absolute Column & Row ($B$1): The formula has an absolute reference to column B of row 1, so it always refers to column B of row 1.
  
  Column A Column B Column C
  
  row 1
  50
  
  7
  
  =$B$1
  
  row 2
  100
  
  =$B$1
  
  =$B$1
  
  row 3
  200
  
  =$B$1
  
  =$B$1
  
  row 4
  400
  
  =$B$1
  
  =$B$1
4

Use the F4 key to switch between absolute and relative. Highlight a cell reference in a formula by clicking it and press F4 to automatically add or remove $ symbols. Keep pressing F4 until the absolute or relative references you’d like are selected, then press Enter or Return.

	Column A	Column B	Column C
row 2	50	7	=A2*2
row 3	100
row 4	200
row 5	400

	Column A	Column B	Column C
row 2	50	7	=A2*2
row 3	100
row 4	200		=A4*2
row 5	400

	Column A	Column B	Column C
row 1	50	7	=B$3
row 2	100	=A$3	=B$3
row 3	200	=A$3	=B$3
row 4	400	=A$3	=B$3

	Column A	Column B	Column C
row 1	50	7	=$B1
row 2	100	=$B2	=$B2
row 3	200	=$B3	=$B3
row 4	400	=$B4	=$B4

	Column A	Column B	Column C
row 1	50	7	=$B$1
row 2	100	=$B$1	=$B$1
row 3	200	=$B$1	=$B$1
row 4	400	=$B$1	=$B$1

Add New Question

Question

When I try to pull down formula, it stays the same and does not change with row, what can I do?

Go to Formulas, Calculation Options, and change them from Manual to Automatic.
Question

When I click and drag, it copies the format also. I don’t want to copy the format, just the formula?

Krisztian Toth

Community Answer

Right after the drag there should be an icon in the lower right corner of the highlighted area. Hover over that and select from the various fill options, among which you can find an option to fill without format.
Question

How do I copy a date formula I have created (that includes the week day as well as date) so that it runs in sequence?

Krisztian Toth

Community Answer

Double click into the cell, copy your formula, double click into the destination cell, then press Ctrl+V or Command+V.

Ask a Question

200 characters left

Include your email address to get a message when this question is answered.

Submit

If you copy a formula to a new cell and see a green triangle, Excel has detected a possible error. Examine the formula carefully to see if anything went wrong.^[4]
If you accidentally did replace the = character with ? or * in the «copying a formula exactly» method, searching for «?» or «*» will not give you the results you expect. Correct this by searching for «~?» or for «~*» instead.^[5]
Select a cell and press Ctrl‘ (apostrophe) to fill it with the formula directly above it.

Thanks for submitting a tip for review!

Different versions of Excel may not show exactly the same screenshots in the same ways as are displayed here.

References

About This Article

Article SummaryX

To copy a formula into multiple adjoining cells in Microsoft Excel, type the formula into a cell, and then press Enter or Return to calculate it. Hover your mouse cursor over the bottom-right corner of the cell so the cursor turns to a crosshair, then drag the crosshair down to copy the formula to other cells in the column. If you’d rather copy the formula to cells in a row, drag the crosshair left or right.
To copy a formula to cells that aren’t touching the formula cell, click the cell once to select it, and then press Control + C (on a PC) or Command + C (on a Mac) to copy the formula. Now, select the cell or cells you want to copy the formula to, then press Control + V (on a PC) or Command + V (on a Mac) to paste it into the selected cells.

Did this summary help you?

Thanks to all authors for creating a page that has been read 513,759 times.

Is this article up to date?

Источник

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

Смотрите также а: Через Ctrl+H заменитьInsert — ModuleВ появившемся диалоговом окне а затем итог «неправильная ссылка на А пока отметим при их копировании в первой ячейке, ячейки с формулами, столбец вычитать, умножать, т. облегчающих работу – другую ячейку наВырезатьПосле копирования формулы вПримечание:относительные ссылки. Обычно «=» на например и скопируйте туда вводим что ищем

переводится в евро ячейку» в E2. что по умолчанию или перемещении. затем перетаскиванием (черным

но.

д. Подробнее, смотрите это
листе. В случаеили нажмите клавиши новом месте, важноМы стараемся как это делается для «xyz», потом поменять
текст вот такого и на что по курсу из Но если бы ссылки относительные, аВ зависимости от того
крестиком) ведем вниз.адреса в формулах ExcelНам нужно скопировать в статье «Суммированиекопирование в Excel простой скопировать выделенный Ctrl + X. проверьте, правильно ли

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

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

Проверка и исправление ссылок на ячейки в новом месте

вы скопируетеP.S. Подправил пост -Sub Copy_Formulas() Dim Параметрах (Options) неПроблема в том, что а просто скопировали присутствует символ «$» формула в Excel, как по столбикам,(не привязывать к

F24. Наводим наЕсли копируем числаМожно копировать всю приведет к возникновению или в другой ячейки. Выбранная ячейка

материалами на вашем	исходную ячейку с
поменять на «@?&» copyRange As Range,	забываем уточнить Область
если скопировать диапазон формулы, то никаких	— значит ссылка
адреса ее ссылок так и по	новым ячейкам), то
квадратик в правом с точками -	таблицу или только

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

не даёт…
pasteRange As Range поиска — Формулы. D2:D8 с формулами
ошибок не возникло. абсолютная. могут существенно отличаться. строкам. это снова в

нижнем углу ячейки. разделителями всместо запятой значение ячеек без ячейка (#REF!) должна в которую нужно

Перенос формулы в другое место

с учетом типа переведена автоматически, поэтому вбок, ссылка наМур On Error Resume Жмем Заменить все куда-нибудь в другоеПримечание. Быстро перемещать формулыТеперь усложним задание. Верните Об этом нужноЕще несколько способов, статье «Относительные и

Указатель мышки приобретает (1.23), то вставив,

формул, или копировать отображаться в целевой вставить формулу.
абсолютные или относительные ее текст может строку или столбец: Щас попробую, вроде Next Set copyRange (Replace all).
место на лист, можно с помощью табличку до изначального помнить всегда! как писать одинаковый
абсолютные ссылки в вид черного крестика. эти числа превратятся формулы с привязкой ячейке, поскольку большеЧтобы вставить формулу с ссылки, которые вы содержать неточности и

изменилась соответствующим образом. идея гениальная. = Application.InputBox(«Выделите ячейкиКопируем получившийся диапазон с то Microsoft Excel перетаскивания ячейки мышкой вида как на
- На готовом примере разберем текст, формулы в формулах Excel». Ссылка Нажимаем два раза
-  в дату. Чтобы к новым ячейкам не будет ссылки сохранением форматирования, выберите использовали.

support.office.com

Вставка значений, а не формул

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

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

на этот крестик. есть несколько способов. к ячейке (со значения, которые используютсяГлавная две ячейки вниз эта статья была и относительные, иHugo «Точное копирование формул»,Заменяем сдвинув их на на рамку курсораСкопируйте «Лист1», например с перед тем как «Как заполнить ячейкиЕсли нужно Формула скопируестя до Смотрите статью «Автозамена сменой адреса ячейки в формуле. >

и вправо от вам полезна. Просим абсолютные, большое спасибо.

Default:=Selection.Address, Type:=8) If

# новое место и выделенной ячейки. А
помощью мышки+CTRL. Наведите скопировать формулу в в Excel одновременно»не копировать, а перенести конца заполненного столбца. в Excel». или оставить адресМожно избежать этой ошибкиВставить
ячейки A1, ячейки, вас уделить пару

ссылки, желая воспроизвестиВсе получилось. copyRange Is Nothingна перестав считать: выполнив это действие указатель на ярлычок Excel, создайте на тут. ячейку с формулой
У нас, вКак скопировать формулу в без изменений). Можно путем вставки толькоили нажмите клавиши которые ссылки будут секунд и сообщить, те же формулыГость Then Exit Sub=Задача: скопировать диапазон с с нажатой клавишей первого листа. Удерживая листе простую табличку
Как cкопировать лист в, то не копируем примере, в ячейки Excel. копировать данные с

support.office.com

Копирование в Excel.

результат формулы в CTRL+V. обновляться согласно следующее: помогла ли она в другом диапазоне: оч. интересно. Set pasteRange =обратно с помощью формулами так, чтобы CTRL, тогда формула левую клавишу мышки
как показано на Excel - её, а через F25:F26.Первый вариант. определенной последовательностью и, целевой ячейке.Чтобы воспользоваться другими параметрамиСсылка вам, с помощью на том жеоказывается, проблема в Application.InputBox(«Теперь выделите диапазон того же окна, формулы не изменились скопируется. и клавишу CTRL
рисунке:правой мышью нажимаем контекстное меню «Вырезать»Получилось.Когда мы таким образом, заполнятьСделайте следующее:
вставки, щелкните стрелкуНовая ссылка кнопок внизу страницы.
рабочем листе, на символе @, если вставки.» & vbCrLf возвращая функциональность формулам. и остались темиДанный урок может показаться
 на клавиатуре, переместитеСкопируйте значения столбца B на ярлык листа.
-> «Вставить». ФормулаВнимание!копируем ячейки с относительными таблицу. Можно копироватьНа листе выделите ячейки, под кнопкой$A$1 (абсолютный столбец и Для удобства также
другом листе в он первый. & vbCrLf &Этот способ существенно быстрее же самыми, сохранив сложным для понимания, ярлычок (копия листа) (например, комбинацией клавиш В диалоговом окне удалится из первойФормула скопируется в формулами лист. И многое, содержащие результатом формулы,Вставить
абсолютная строка) приводим ссылку на той же рабочейпричем Excel (у _ «Диапазон должен и проще. результаты расчета. но на практике в новое место. CTRL+C) и вставьте
выбираем «Переместить/скопировать». ячейки и перенесется таблице, если ячейки(«Копировать» -> «Вставить») многое другое. который нужно скопировать.и выберите один$A$1 оригинал (на английском книге или, возможно,
 меня 2003) при быть равен по
Нажмите сочетание клавиш
Как можно заметить достаточно прост. Нужно Отпустите сначала мышку, их в столбецСтавим галочку у слов в новое место. столбца слева таблицы и затем переносимЧтобы вызвать функциюНа вкладке из указанных нижеA$1 (относительный столбец и языке) . на другом
 простом вводе в
размеру исходному «Ctrl+Ё по предыдущей картинке, только помнить об а потом клавиатуру. D (CTRL+V) . «Создать копию». ВыбираемЕщё варианты копирования будут заполнены. Тогда в другое место, «Копировать», достаточно нажатьГлавная вариантов. абсолютная строка)При копировании формулы влисте другой рабочей ячейку любого текста, & vbCrLf & или кнопку Excel сдвигает только особенностях поведения формул У вас получился А потом переместите куда переместить скопированный
формул, чтобы ссылки Excel поймет до то таблица Excel правой кнопкой мышив группе
ФормулыC$1 другое место, можно книги. Это можно начинающегося с @, _ «диапазону копируемых
Показать формулы
 относительные ссылки. Абсолютная при их копировании. такой же лист, данные из столбца
лист (в эту в них не какой ячейке расположена сама меняет адреса на выделенную ячейку,буфер обменаЧтобы вставить только$A1 (абсолютный столбец и выбрать параметры вставки сделать, не изменяя выдает сообщение «Неверная ячеек.», «Точное копированиена вкладке (со знаками $)она копировалась с измененными
но уже с
 B в E
или другую книгу, менялись, описаны в таблица. ячеек в формулах диапзон и выбратьнажмите кнопку формулу
относительная строка) определенного формулы в ссылки на диапазоны функция» (конечно, это формул», _ Default:=Selection.Address,Формулы (Formulas — Show ссылка на желтую ячейками соотвественно положению? названием «Лист1(2)». (например, комбинацией клавиш перед каким листом) статье «Как скопироватьТакми способом можно относительно новых ячеек. из контекстного меню
Копировать.
$A3 ячейках назначения.внутри формул. не относится к Type:=8) If pasteRange.Cells.Count formulas) ячейку $J$2 не т.е. если формулаНа копии «Лист1(2)» в CTRL+X). или оставляем так
формулу в Excel копировать значение, формулы, Смотрите подробнее в
функцию «Копировать».или нажмите сочетаниеЗначенияA1 (относительный столбец иНиже описано, как скопироватьВыделите диапазон ячеек, ячейкам, имеющим формат <> copyRange.Cells.Count Then, чтобы включить режим сместилась. Поэтому для B6=(B5-C5)+F5, как сделать ячейку D2 скопируйте
Теперь переключитесь в режим и нажимаем «ОК». без изменения ссылок». формат. Если выделим статье «Относительные иИли, можно нажать клавиш CTRL +, чтобы вставить относительная строка) и Вставить формулу. который хотите скопировать. Текстовый. MsgBox «Диапазоны копирования
проверки формул - точного копирования формул чтобы нижняя автоматом значение из B2, отображения формул – Затем, у скопированногоКак заполнить таблицу в ячейки в нескольких абсолютные ссылки в сочетание клавиш «Ctrl»
C. результат формулы толькоC3Выделите ячейку, содержащую формулу, Выберите командуэто баг или и вставки разного
 в ячейках вместо можно временно перевести
была B7=(B6-C6)+F6 ? а в ячейку CTRL+`(Ё). Обратите внимание, листа, можно изменить Excel. стролбцах, то копирование Excel». + «C».Выделите левую верхнюю ячейку (результат вычисления поЕсли ссылки на ячейки которую нужно скопировать.Правка ► Заменить фича? размера!», vbExclamation, «Ошибка результатов начнут отображаться все ссылки воПользователь удален
E2 переместите (как как ведут себя название, переместить егоМожно копировать данные будет сразу во
Второй выриант.Или, на закладке область вставки. формуле). в формуле неВыберите (Edit ► Replace)Serge_007 копирования» Exit Sub формулы, по которым всех формулах в
: они так по на предыдущем задании). ссылки на ячейки на другое место, ячеек в определенной всех выделенных столбцах.Можно «Главная» в разделе
Совет:Примечание: возвращают нужный результат,Главная
и в поле: End If If они посчитаны: абсолютные. Нужно будет умолчанию копируютсяТеперь скопируйте столбцы D:E при перемещении и
 изменить цвет ярлычка. последовательности. Например, надо Например, если выделимкопировать формулы перетаскиванием «Буфер обмена» нажать Чтобы переместить или скопировать
Мы стараемся как попробуйте использовать другой > Что (Find What)Из книги Рейны и pasteRange Is NothingСкопируйте наш диапазон D2:D8 выделить каждую формулуформула должна быть из «Лист1(2)» и копировании.Как скопировать лист в заполнить графу таблицы
 ячейки D24, E24,, чтобы не писать на кнопку функции
выделенный фрагмент на можно оперативнее обеспечивать тип ссылки:Копировать введите = (знак
 Девида Холи «Трюки Then Exit Sub и вставьте его в строке формул =(B5-C5)+F5 вставьте их вПри перемещении (те, что новую книгу Excel
названиями месяцев года. F24, нажмем два одну и ту «Копировать», предварительно выделив другой лист или вас актуальными справочнымиВыделите ячейку, содержащую формулу.или нажмите клавиши равенства).
в Excel»: Else pasteRange.Formula = в стандартный и нажать клавишуесли вы ее столбцы D:E из в столбце E)

excel-office.ru

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

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

материалами на вашемВ формуле в строке CTRL+C.В поле ЗаменитьQuote copyRange.Formula End IfБлокнот

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

F4 скопируете ниже, то «Лист1». ссылки не изменяются.Для этого в ячейке «Январь», в углу ячейки F24, других ячейках таблицы.Как скопировать значение ячейки

щелкните ярлычок другого языке. Эта страница, выделите ссылку,На том же листе на (Replace With)200?’200px’:»+(this.scrollHeight+5)+’px’);»> End Sub:: номера поменяются

Как видите обе ячейки А при копировании диалоговом окне в следующей ячейке ниже то скопируются данные Таким образом, заполняем в Excel

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

Теперь выделите все вставленное

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

При большом количествечтобы не менялись, D2 и E2 (те, что в разделе «Переместить выбранные пишем «Февраль». Теперь по всем этим таблицу формулами. Например,(без формулы). другую книгу и ее текст можетНажмите клавишу F4, чтобы лист щелкните фигуру коммерческого at) илибез изменения ссылок воспользоваться кнопкой (Ctrl+A), скопируйте в ячеек этот вариант, нужно, вроде, B$5$ были одинаково и столбце D), они листы в книгу» выделяем обе эти

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

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

ячейки. Наводим мышьКопирование в Excel через по столбцу. Для нужно скопировать. Нажимаем ячейку области вставки. грамматические ошибки. Для абсолютных и относительных которую нужно вставитьдругой символ который, формуле может бытьна вкладке (Ctrl+C) и вставьте — слишком трудоемко.Andrey gavrilov ссылки в их
Как скопировать формат в на правый нижний ячейку. этого устанавливаем (пишем) «Копировать». Переходим на
На вкладке нас важно, чтобы ссылок и выберите формулу. вы уверены, не

либо относительной, либоРазработчик (Developer — Macros) на лист вЧтобы формулы при копировании: нажимаешь ячекй, и формулах уже ведутПри копировании адреса относительных Excel ячейки или угол выделенного диапазонаМожно копировать не формулу в первую новое место, делаемГлавная эта статья была необходимый вариант.Чтобы вставить формулу с используется ни вабсолютной. Иногда, однако,или сочетанием клавиш нужное вам место: не менялись, надо наводишь на ее себя по-разному. При ссылок приспосабливаются к

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

вам полезна. ПросимДополнительные сведения о абсолютные сохранением форматирования, выберите одной формуле. возникает необходимость воспроизвестиAlt+F8Осталось только отжать кнопку (временно) сделать так, бок, а лаьше

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

Щелкните на кнопке те же. После запуска макросПоказать формулы (Show Formulas) чтобы Excel перестал растягиваешь вниз, ну значение не меняется.

exceltable.com

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

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

нажмите кнопку секунд и сообщить, на ячейки ознакомьтесь
Главная Заменить все (Replace
формулы в другом попросит вас выделить, чтобы вернуть Excel
их рассматривать как или в сторону Все из-за того,

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

Вставить помогла ли она со статьей Обзор > All). месте на рабочем диапазон с исходными

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

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

Проблема

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

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

или нажмите клавиши на вашем рабочем рабочей книге, или вставки и произведет дает сбой на время копирования знак

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

ячейки где есть путем перемещения и но адрес естественноИногда, когда нужно по какой месяц нужно поставить даты там появится черный формулы (цифры) не. Или нажмите Для удобства также формулы при перемещении CTRL+V. листе вместо знака же на точное копирование формул сложных таблицах с «равно» (=) на формула появится чёрный

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

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

в столбце А крестик. Нажимаем на копируются, делаем так:Сочетание клавиш Ctrl + приводим ссылку на формулы в другоеДругими параметры вставки, щелкните равенства будет стоятьдругом листе. автоматически: объединенными ячейками, но любой другой символ, плюсик — и для Excel иной =A2*1,23 стала формулой (копируется) не значение

мышку. Все, таблица через одну пустую него левой мышкой
выделяем ячейки как Alt + V оригинал (на английском место в той стрелку под кнопкойсимвол @.Если формулу нужноМур
в подавляющем большинстве не встречающийся обычно тянуть туда, куда способ присваивания адресов =C2*1,23. Когда мы ячеек, а формулы. заполнена. ячейку. и ведем вниз,
обычно-> «Копировать». Появится, чтобы отобразить языке) .
же или другойВставитьТеперь вы просто сделать абсолютной, введите: Здравствуйте, Все. случаев — работает в формулах, например

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

надо её вставить в формулах данной

ту же самую Чтобы было значениеТак же можноВыделяем первую ячейку с удерживая левую кнопку вокруг ячеек, которые диалоговое окноПри копировании содержимого ячеек, лист, ссылки наи выберите вариант: можете скопировать этот $ (знак доллара)Не могу разобраться

отлично. на «решетку» (#)Геннадий гривин ячейки. Чтобы еще формулу не скопируем,

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

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

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

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

формулы) в ячейках, года, т.д. Таким ячейку ниже.Отпускаем мышку. Формулы скопировались, рамка, еще раз. только значения — не изменится —Чтобы вставить только на нужноестолбца или номером с формулами в без сдвига ссылок амперсандов (&&). Для курсор на правый убедиться, снова приведите адреса ее ссылок читайте статью «Печать

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

нижнем углу выделенного нового места. Таблица и в диалоговоми нажмите в строка формул. помощью ссылки наЗначения > Заменить (Edit $А$1. Когда вы не везде предусмотрели смысл использовать для (в нашем примере удерживая левую кнопку как на первом

planetaexcel.ru

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

 они относительные. При в большой таблице
так и с диапазона ячеек вниз формулами заполнена. окне
кнопку ОКНапример может потребоваться скопировать ячейки.
Чтобы вставить значения ► Replace). Насделаете это, неважно, абсолютные ссылки.
этого макрос. Нажмите D2:D8) мышки тащи вниз

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

Как скопировать формулувыбираем команду «Специальная вставка».

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

 этот раз куда вы скопируетеНадеялся на специальную сочетание клавиш
Жмем

 по столбцу доНа этот раз в
ячейки ведут себя диапазон, можно воспользоваться. Например: 5,10, 15,
скопируются так. в Excel таким, выбираем — «Значение».Здесь рассмотрим, формулы в ячейку чтобы переместить формулу. по формуле)замените символ @ формулу, она все вставку (скопировать формулы),
Alt+F11Ctrl+H

 нужной строки… ячейку E2 скопируйте

 как абсолютные. Об функцией «Поиск». Смотрите т.д. Смотрим пример:

Если выделим три способом, чтобы неЗатем, как обычно -
как копировать в Excel

на другом листе.Щелкните ячейку, содержащую формулу,.
на = (знак так же будет но что-то не
или кнопкуна клавиатуре илиПредположим, что у нас формулу из B2, этом следует всегда
статью «Поиск в
интервал 2. пустые ячейки с копировался формат, смотрите «Вставить». С помощью
формулыячейки, как скопировать Или, может потребоваться которую вы хотитеСовет:
равенства). Скопированные формулы ссылаться на получается.Visual Basic на вкладке
есть вот такая
а в ячейку помнить пользователю Excel. Excel».Если нужно
датой, то даты в статье «Как функции «Специальная вставка» лист в Excel,
удалить значения, которые переместить. Можно также с помощью будут
те же ячейки.
Подскажите пожалуйста способ,на вкладкеГлавная — Найти и несложная таблица, в
D2 переместите тужеПримечание. В разделе, посвященномЦелью этого урока являетсянаписать одинаковый текст в
скопируются через три скопировать в Excel». можно не только таблицу,значениеформат можно использовать в
Выберите маркера заполнения дляссылаться на теИногда, однако, можно
если это возможно
Разработчик (Developer) выделить — Заменить которой подсчитываются суммы
самую формулу. формулам, будет уделено научить пользователя контролировать нескольких ячейках Excel пустые ячейки. ПолучитсяТретий вариант.
копировать данные, но, т.д. Одна из формуле, скопировав значениеГлавная копирования формулы в
же ячейки, что ввести много формул, вообще., вставьте новый модуль
(Home — Find&Select по каждому месяцуПрограмма нас информирует, что
больше внимания относительным адреса ссылок на подряд так.
Как копировать формулы в
и складывать их, многих возможностей таблицы, результат формулы в>
смежные ячейки листа. и исходные. содержащих не абсолютные,Hugo через меню
— Replace) в двух городах, мы имеем ошибку и абсолютным ссылкам.
ячейки в формулах, то пишем текстЕсли нужно перенести

excelworld.ru

Excel на весь

Источник

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Новая ссылка

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

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

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

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

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

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

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

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

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

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

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

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Как сделать формулу в excel с одного листа на другой?

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

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

Ссылка на лист в формуле Excel

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

Возникает вопрос: как сделать ссылку на другой лист в Excel? Для реализации данной задачи делаем следующее:

Заполните Лист1, Лист2 и Лист3 так как показано выше на рисунке.
Перейдите на Лист4, ячейка B2.
Поставьте знак «=» и перейдите на Лист1 чтобы там щелкнуть левой клавишей мышки по ячейке B2.
Поставьте знак «+» и повторите те же действия предыдущего пункта, но только на Лист2, а потом и Лист3.
Когда формула будет иметь следующий вид: =Лист1!B2+Лист2!B2+Лист3!B2, нажмите Enter. Результат должен получиться такой же, как на рисунке.

Как сделать ссылку на лист в Excel?

Ссылка на лист немного отличается от традиционной ссылки. Она состоит из 3-х элементов:

Имя листа.
Знак восклицания (служит как разделитель и помогает визуально определить, к какому листу принадлежит адрес ячейки).
Адрес на ячейку в этом же листе.

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

Ссылка на лист в другой книге Excel

Ссылка на лист в другой книге имеет уже 5 элементов. Выглядит она следующим образом: =’C:DocsЛист1′!B2.

Описание элементов ссылки на другую книгу Excel:

Путь к файлу книги (после знака = открывается апостроф).
Имя файла книги (имя файла взято в квадратные скобки).
Имя листа этой книги (после имени закрывается апостроф).
Знак восклицания.
Ссылка на ячейку или диапазон ячеек.

Данную ссылку следует читать так:

книга расположена на диске C: в папке Docs;
имя файла книги «Отчет» с расширением «.xlsx»;
на «Лист1» в ячейке B2 находится значение на которое ссылается формула или функция.

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

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

Один из читателей нашего сайта SirExcel задал вопрос, который может встречаться очень часто. Вопрос заключался в следующем «Как сделать так, чтобы на одном листе показывалась информация из определенной ячейки к примеру $A$1 но с каждого листа из книги? Листов очень много надо сделать сводный список содержимого определенной ячейки со всех листов.»

Я как раз собирался написать о том, как работает функция ДВССЫЛ, почему бы не рассмотреть ее именно на этом примере.

Итак, мы имеем файл Excel, в котором много листов. Давайте рассмотрим простой пример, где названия листов у нас не менялись. То есть у нас есть Лист1, Лист2, Лист3 и так далее.

Допустим, что на каждом листе (Лист1-Лист5) у нас есть данные по выручке 5 различных магазинов в виде одинаковых таблиц данных.

На листе 6 есть результирующая таблица, где нам необходимо заполнить ее данными, взяв их со всех листов.

Таким образом, чтобы заполнить данные за январь нам необходимо взять данные по выручке магазинов, находящихся в ячейке B2, но на 5 различных листов.

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

Существует различные способы решить данную задачу, например, с помощью простенького макроса, который можно записать макрорекодером. Ну а мы с вами для решения задачи будет использовать функцию ДВССЫЛ.

ДВССЫЛ – функция, которая возвращает ссылку заданную текстовой строкой.

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

Посмотрите на рисунок ниже. В ячейке А1 написан текст D4, а в самой ячейке D4 указано число 9999. Если мы напишем формулу ДВССЫЛ(A1), то в результате получим число 9999. То же самое мы получим если напишем формулу так:
=ДВССЫЛ(«D4»)
То есть мы написали текстом адрес ячейки D4 и функция ДВССЫЛ вернула нам то значение, которое находится по данному адресу (D4).

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

=Лист1!B2 (Выручка за январь магазина 1)

=Лист2! В2 (Выручка за январь магазина 2) и т.д.

Но сложность заключается в том, что если ячейку просто протянуть вниз, то Лист1 не будет меняться на Лист2 и так далее.

Если мы пропишем формулу

ДВCСЫЛ(«Лист1!B2»), ДВCСЫЛ(«Лист2!B2») и так далее, то функция будет возвращать нам то же самое, но это так же не решает нашу проблему, ведь «Лист1!B2» прописан обычным текстом и так же не будет изменяться при протягивании.

Для решения нашей задачи мы разделим текст «Лист1!B2» на две части (отдельно «Лист1» и отдельно «!B2» — обратите внимание на восклицательный знак во второй части текста) и потом их склеим. Текст Лист1, Лист2 и так далее мы пропишем напротив соответствующих магазинов, при этом написав Лист1, мы сможем просто протянуть ячейку вниз и автоматически получить список Лист1, Лист2 и так далее.

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

Итоговая формула выглядит именно так, но я специально расписал с самого начала по шагам, чтобы было понятно из чего состоит данная формула. В принципе, этого уже достаточно, чтобы за короткий срок решить задачу, указанную в начале статьи. Чтобы заполнить данные за февраль, нам необходимо в ячейке D2 написать такую же формулу, но поменять текст «!B2» на «!B3»

=ДВССЫЛ(A2&»!B3″) и протянуть вниз, аналогично за март.

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

Итак, мы решили задачу, как быстро заполнить данные за определенный месяц, но нам необходимо заполнить данные за остальные месяца. Мы знаем, что данные за Январь соответствуют ячейке B2, февраль — B3, Март — B4. То есть буква «B» остается неизменной, меняется лишь цифра. Зная это мы просто разобьем текст внутри ссылки ДВССЫЛ не на 2 части, а на три. Добавим дополнительно строку над таблицей и напишем сверху цифры соответствующие ячейки месяца. Пропишем формулу.

=ДВССЫЛ(A3& «!B»&C1), где A3 — это текст «Лист1», «!B» — это неизменный текст и С1 — это цифра 2. Все это объединено с помощью знака & в общий текст «Лист1!B2». Нам также потребуется закрепить столбец A (с помощью знака $), чтобы он не менялся при протягивании формулы вправо и строку 1, чтобы она не менялась при протягивании формулы вниз. Мы получаем следующую итоговую формулу.

=ДВССЫЛ($A3&»!B»&C$1)
которую можно протянуть вправо и вниз.

Заметки по теме:

Если бы листы назывались у нас Магазин 1, Магазин 2 и так далее, то дополнительный столбец со словами Лист1, Лист2 и так далее нам бы не потребовался.
Функцию ДВССЫЛ используют часто тогда, когда требуется изменить ссылку на ячейку в формуле, не изменяя саму формулу.

Если вам понравилась статья, пожалуйста, нажмите +1 и «Мне нравится». Так же подписывайтесь на нашу рассылку или вступайте в нашу группу ВКонтакте, чтобы не пропустить наши следующие уроки по Excel

SirExcel — безграничные возможности Excel

Формулы в Excel – одно из самых главных достоинств этого редактора. Благодаря им ваши возможности при работе с таблицами увеличиваются в несколько раз и ограничиваются только имеющимися знаниями. Вы сможете сделать всё что угодно. При этом Эксель будет помогать на каждом шагу – практически в любом окне существуют специальные подсказки.

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

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

Сделайте активной любую клетку. Кликните на строку ввода формул. Поставьте знак равенства.

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

так и ссылки на ячейки.

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

Из чего состоит формула

В качестве примера приведём следующее выражение.

символ «=» – с него начинается любая формула;
функция «СУММ»;
аргумента функции «A1:C1» (в данном случае это массив ячеек с «A1» по «C1»);
оператора «+» (сложение);
ссылки на ячейку «C1»;
оператора «^» (возведение в степень);
константы «2».

Использование операторов

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

скобки;
экспоненты;
умножение и деление (в зависимости от последовательности);
сложение и вычитание (также в зависимости от последовательности).

Арифметические

К ним относятся:

сложение – «+» (плюс);

отрицание или вычитание – «-» (минус);

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

умножение – «*»;

деление «/»;

процент «%»;

возведение в степень – «^».

Операторы сравнения

Данные операторы применяются для сравнения значений. В результате операции возвращается ИСТИНА или ЛОЖЬ. К ним относятся:

знак «равенства» – «=»;

знак «больше» – «>»;

знак «меньше» — «=D1
- знак «меньше или равно» — «3″;B3:C3)
  1. Excel может складывать с учетом сразу нескольких условий. Можно посчитать сумму клеток первого столбца, значение которых больше 2 и меньше 6. И ту же самую формулу можно установить для второй колонки.
1. Результат всех формул получится следующим.
Математические функции и графики

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

В качестве примера попробуем построить графики для экспоненты и какого-нибудь уравнения. Инструкция будет следующей:
1. Создадим таблицу. В первой графе у нас будет исходное число «X», во второй – функция «EXP», в третьей – указанное соотношение. Можно было бы сделать квадратичное выражение, но тогда бы результирующее значение на фоне экспоненты на графике практически пропало бы.
1. Для того чтобы преобразовать значение «X», нужно указать следующие формулы.
1. Дублируем эти выражения до самого конца. В итоге получаем следующий результат.
1. Выделяем всю таблицу. Переходим на вкладку «Вставка». Кликаем на инструмент «Рекомендуемые диаграммы».
1. Выбираем тип «Линия». Для продолжения кликаем на «OK».
1. Результат получился довольно-таки красивый и аккуратный.
Как мы и говорили ранее, прирост экспоненты происходит намного быстрее, чем у обычного кубического уравнения.

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

Отличие в версиях MS Excel

Всё описанное выше подходит для современных программ 2007, 2010, 2013 и 2016 года. Старый редактор Эксель значительно уступает в плане возможностей, количества функций и инструментов. Если откроете официальную справку от Microsoft, то увидите, что они дополнительно указывают, в какой именно версии программы появилась данная функция.

Во всём остальном всё выглядит практически точно так же. В качестве примера, посчитаем сумму нескольких ячеек. Для этого необходимо:
1. Указать какие-нибудь данные для вычисления. Кликните на любую клетку. Нажмите на иконку «Fx».
1. Выбираем категорию «Математические». Находим функцию «СУММ» и нажимаем на «OK».
1. Указываем данные в нужном диапазоне. Для того чтобы отобразить результат, нужно нажать на «OK».
1. Можете попробовать пересчитать в любом другом редакторе. Процесс будет происходить точно так же.
Заключение

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

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

Кроме этого, важно помнить, что формулы должны начинаться с символа «=» (равно). Многие начинающие пользователи забывают про это.

Файл примеров

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

Видеоинструкция

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

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

Приведу три способа Автоматического переноса данных с одного листа программы «Эксель» в другой.

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

Рассмотрим, как соединить две таблицы по шагам.

Первый шаг.

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

Второй шаг.

Копировать информацию сочетанием клавиш ctrl+C или вызвав контекстное меню правой кнопкой мыши и кликнув по пункту меню «Копировать»

Третий шаг.

Перейти на лист документа «Excel», в который Вы планируете транслировать информацию из первой таблицы.

Четвертый шаг.

Поставить курсор в первую (левую верхнюю) ячейку таблицы и выбрать в меню «Вставка» пункт «Вставить связь». В некоторых версиях программы «Excel» этот пункт находится в меню «Специальная вставка»

После вставки связи следует отформатировать вид ячеек – привести их к надлежащему виду.

Результат вставки связи

Второй способ переноса данных из одной таблицы в другую — это использование сводных таблиц в программе «Excel».

При использовании данного метода роль второй таблицы («реципиента») играет сама сводная таблица.

Как обновить сводную таблицу

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

О том, как в «Эксель» создавать сводные таблицы подробно написано в статье:

Как делать сводные таблицы в программе «Excel» и для чего они нужны.

Третий способ самый эффективный и наиболее автоматизированный — это использование меню надстройки «Power Query».

Правда нужно отметить, что этот способ подходит только пользователям Excel 2016 и пользователям Excel 2013и выше с установленной надстройкой «Power Query».

Смысл способа в следующем:

Необходимо открыть вкладку «Power Query». В разделе «Данные Excel» нажимаем кнопку (пиктограмму) «Из таблицы».

Из таблицы -Power Query

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

Источник данных для запроса Power Query

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

После настройки вида таблицы нажмите кнопку «Закрыть и загрузить»

Обновление полученной таблицы происходит кликом правой кнопки мыши по названию нужного запроса в правой части листа (список «Запросы книги»). После клика правой кнопкой мыши в выпадающем контекстном меню следует нажать на пункт «Обновить»

Обновление запроса в PowerQuery

Источник

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

Рассмотрим на примерах популярные методы копирования формул, их плюсы и минусы.

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

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

Содержание

Самый популярный способ копировать. Автозаполнение
Как скопировать формулу в Excel? Перетаскиванием или растаскиванием
Копирование формулы простой вставкой и специальной вставкой
Копирование формул из другой книги
Как скопировать формулы, как текст
Похожие статьи

Самый популярный способ копировать. Автозаполнение

Запишем формулу в ячейку C2. Описание функции ЕСЛИ можно прочитать здесь.

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

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

Очень удобно!

Важное замечание. Такой способ работает только если в соседнем столбце, слева или справа, данные не прерываются. Т.е. если в строке 9 не будет данных, то диапазон заполнится только до 8 строки. Если у вас огромная таблица, нужно быть аккуратным и проверять автозаполнение!

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

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

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

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

Конечно, никто не отменял копирование данных стандартными способами:

Выбрали данные — Правой кнопкой мыши — Копировать. Выбрали место для копирования — Правой кнопкой мыши — Вставить
Выбрали данные — Ctrl + C. Выбрали место для копирования — Ctrl + V.

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

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

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

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

В данном случае Копирование формул.xlsx — название файла, квадратные скобки отделяют его от других частей ссылки. Чтобы удалить имя файла из формулы (т.е. сделать формулы только для этой книги), воспользуйтесь возможностью Найти и Заменить. Обращаю внимание, что заменять нужно именно фразу [Копирование формул.xlsx]

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

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

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

Получится вот так:

Теперь данные из столбца C можно скопировать в любую книгу или лист, после чего заменить / обратно на =. Формулы перенесены!

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

Есть вопрос — напишите комментарий!

Источник

Содержание

1 Как скопировать таблицу с формулами в Excel?
- 1.1 Перемещение и копирование ячеек
2 Как перенести таблицу в Excel без изменений?
3 Как продолжить таблицу в Excel?
4 Как скопировать данные из ячейки с формулой?
- 4.1 Вставка значений, а не формул
5 Как скопировать таблицу на другой лист?
- 5.1 Копирование листа в другую книгу
6 Как перенести часть таблицы в Excel?
7 Как перетащить таблицу в Excel?
8 Как скопировать лист в Excel с сохранением формата?
9 Как продолжить таблицу на компьютере?
- 9.1 Добавление ячейки
10 Как добавить несколько строк в Excel?
11 Как добавить строку в Excel?
12 Как скопировать формулу на весь столбец в Excel?
- 12.1 Копирование формулы
13 Как в Excel убрать формулы и оставить только значения?
- 13.1 Замена формул на вычисленные значения
14 Как зафиксировать значение ячейки в Excel?
15 Как скопировать таблицу Эксель на новый лист?
16 Как перенести формулу в Excel на другой лист?
- 16.1 Перенос формулы в другое место
17 Как скопировать таблицу в Excel с фильтрами?

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

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

Как перенести таблицу в Excel без изменений?

0:11

7:56

Рекомендуемый клип · 116 сек.

Excel 2007. Копирование и вставка данных. — YouTube

YouTube

Начало рекомендуемого клипа

Конец рекомендуемого клипа

Как продолжить таблицу в Excel?

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

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

Вставка значений, а не формул

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

Ещё

Как скопировать таблицу на другой лист?

Копирование листа в другую книгу

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

Ещё

Как перенести часть таблицы в Excel?

0:09

1:21

Рекомендуемый клип · 72 сек.

Как перенести таблицу в Экселе? — YouTube

YouTube

Начало рекомендуемого клипа

Конец рекомендуемого клипа

Как перетащить таблицу в Excel?

Рекомендуемый клип · 73 сек.

Как сместить таблицу в Excel — YouTube

YouTube

Начало рекомендуемого клипа

Конец рекомендуемого клипа

Как скопировать лист в Excel с сохранением формата?

Для копирования листа из одной книги в другую вам необходимо для начала открыть обе книги. Затем откройте лист из которого вы хотите скопировать данные. Щелкните правой кнопкой мышки на закладке данного листа и выберите в выпадающем меню «Переместить/скопировать» (Рис.6 авг. 2010 г.

Как продолжить таблицу на компьютере?

Добавление ячейки

Щелкните ячейку справа или сверху от того места, куда вы хотите вставить новую ячейку.
В разделе Работа с таблицами откройте вкладку Макет.
Щелкните стрелку в правом нижнем углу раздела Строки и столбцы.
Выберите один из следующих вариантов: Параметр Задача со сдвигом вправо

Как добавить несколько строк в Excel?

Вставка новых строк в таблицу

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

Как добавить строку в Excel?

Рекомендуемый клип · 26 сек.

Как вставить ячейку или несколько ячеек в Excel? Добавляем

YouTube

Начало рекомендуемого клипа

Конец рекомендуемого клипа

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

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

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

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

Замена формул на вычисленные значения

Выделите ячейку или диапазон ячеек с формулами. Если это формула массива, выделите диапазон ячеек, содержащих ее.
Нажмите кнопку Копировать .
Нажмите кнопку Вставить .
Щелкните стрелку рядом с командой Параметры вставки и выберите команду Только значения .

Как зафиксировать значение ячейки в Excel?

Чтобы не прописывать знак доллара вручную, вы можете установить курсор на формулу в ячейке E2 (выделите текст B7) и нажмите затем клавишу F4 на клавиатуре, Excel автоматически закрепит формулу, приписав доллар перед столбцом и строкой, если вы еще раз нажмете на клавишу F4, то закрепится только столбец, еще раз —

Как скопировать таблицу Эксель на новый лист?

Рекомендуемый клип · 33 сек.

Как скопировать таблицу в excel — YouTube

YouTube

Начало рекомендуемого клипа

Конец рекомендуемого клипа

Как перенести формулу в Excel на другой лист?

Перенос формулы в другое место

Щелкните ячейку с формулой, которую хотите перенести.
Нажмите +X.
Щелкните ячейку, в которую нужно вставить формулу.
Чтобы вставить формулу с сохранением форматирования, на вкладке Главная выберите команду Вставить или нажмите клавишу + V.

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

2:07

4:40

Рекомендуемый клип · 99 сек.

Вставка в отфильтрованные ячейки — YouTube

YouTube

Начало рекомендуемого клипа

Конец рекомендуемого клипа

Источник

Загрузить PDF

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

1

Введите формулу в одной ячейке. Сначала введите знак равенства (=), а затем введите нужную функцию или математическую операцию. Например, вам нужно сложить значения столбцов A и B:

Таблица

Столбец A Столбец B Столбец C

строка 1 10 9 =A1+B1

строка 2 20 8

строка 3 30 7

строка 4 40 6
2

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

Таблица

Столбец A Столбец B Столбец C

строка 1 10 9 19

строка 2 20 8

строка 3 30 7

строка 4 40 6
3

Нажмите на маркер в нижнем правом углу ячейки. Переместите указатель в правый нижний угол ячейки с формулой; указатель превратится в символ «+». ^[1]
4

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

Таблица

Столбец A Столбец B Столбец C

строка 1 10 9 =A1+B1

строка 2 20 8 =A2+B2

строка 3 30 7 =A3+B3

строка 4 40 6 =A4+B4

Таблица

Столбец A Столбец B Столбец C

строка 1 10 9 19

строка 2 20 8 28

строка 3 30 7 37

строка 4 40 6 46
5
Дважды щелкните по «+», чтобы скопировать формулу во все ячейки столбца.
Вместо того, чтобы перетаскивать символ «+», переместите указатель мыши в правый нижний угол ячейки с формулой и дважды щелкните по появившемуся значку «+». Формула скопируется во все ячейки столбца.
^[2]
- Если в столбце есть пустые ячейки, автоматическое копирование формулы прекратится, как только будет достигнута первая пустая ячейка. В этом случае повторите описанные действия, чтобы скопировать формулу в ячейки, которые расположены под пустой ячейкой.
Реклама

	Столбец A	Столбец B	Столбец C
строка 1	10	9	=A1+B1
строка 2	20	8
строка 3	30	7
строка 4	40	6

	Столбец A	Столбец B	Столбец C
строка 1	10	9	19
строка 2	20	8
строка 3	30	7
строка 4	40	6

	Столбец A	Столбец B	Столбец C
строка 1	10	9	=A1+B1
строка 2	20	8	=A2+B2
строка 3	30	7	=A3+B3
строка 4	40	6	=A4+B4

	Столбец A	Столбец B	Столбец C
строка 1	10	9	19
строка 2	20	8	28
строка 3	30	7	37
строка 4	40	6	46

1

Введите формулу в одной ячейке. Сначала введите знак равенства (=), а затем введите нужную функцию или математическую операцию. Например, вам нужно сложить значения столбцов A и B:

Таблица

Столбец A Столбец B Столбец C

строка 1 10 9 =A1+B1

строка 2 20 8

строка 3 30 7

строка 4 40 6
2

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

Таблица

Столбец A Столбец B Столбец C

строка 1 10 9 19

строка 2 20 8

строка 3 30 7

строка 4 40 6
3

Щелкните по ячейке с формулой, а затем нажмите CTRL+C, чтобы скопировать ее.
4

Выделите ячейки, в которые будет скопирована формула. Нажмите на одну из ячеек, удерживайте кнопку мыши и проведите указателем по нужным ячейкам. Также можно удерживать клавишу «Ctrl» и щелкнуть по несмежным нужным ячейкам.
5

Вставьте формулу в выделенные ячейки. Для этого нажмите CTRL+V.

Реклама

	Столбец A	Столбец B	Столбец C
строка 1	10	9	=A1+B1
строка 2	20	8
строка 3	30	7
строка 4	40	6

	Столбец A	Столбец B	Столбец C
строка 1	10	9	19
строка 2	20	8
строка 3	30	7
строка 4	40	6

Воспользуйтесь этим методом, чтобы скопировать формулу без изменения адресов ячеек, которые входят в нее. Иногда таблица полна формул, и их нужно точно скопировать (то есть без изменений). Если менять относительные адреса ячеек на абсолютные адреса во всех формулах, можно потратить уйму времени (это тем более не приемлемо, если в будущем вам придется опять менять адреса). Этот метод подразумевает копирование формулы с относительными адресами ячеек в другие ячейки так, что формула не изменится. ^[3] Например, скопируем все содержимое столбца C в столбец D:

Таблица

	Столбец A	Столбец B	Столбец C
строка 1	944	Лягушки	=A1/2
строка 2	636	Жабы	=A2/2
строка 3	712	Тритоны	=A3/2
строка 4	690	Змеи	=A4/2

Если вы хотите скопировать просто одну формулу, перейдите к шагу «Воспользуйтесь альтернативным методом» этого раздела.

2

Откройте окно «Найти». В большинстве версий Excel щелкните по вкладке Главная в верхней части окна Excel, а затем щелкните по Найти и выбрать в разделе «Редактирование».^[4] Также можно нажать клавиши CTRL+F.

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

Таблица

	Столбец A	Столбец B	Столбец C
строка 1	944	Лягушки	##&A1/2
строка 2	636	Жабы	##&A2/2
строка 3	712	Тритоны	##&A3/2
строка 4	690	Змеи	##&A4/2

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

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

Таблица

	Столбец A	Столбец B	Столбец C	Столбец D
строка 1	944	Лягушки	##&A1/2	##&A1/2
строка 2	636	Жабы	##&A2/2	##&A2/2
строка 3	712	Тритоны	##&A3/2	##&A3/2
строка 4	690	Змеи	##&A4/2	##&A4/2

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

Таблица

	Столбец A	Столбец B	Столбец C	Столбец D
строка 1	944	Лягушки	=A1/2	=A1/2
строка 2	636	Жабы	=A2/2	=A2/2
строка 3	712	Тритоны	=A3/2	=A3/2
строка 4	690	Змеи	=A4/2	=A4/2

6
Воспользуйтесь альтернативным методом. Если описанный здесь метод не привел к успеху или вы не хотите пользоваться им, чтобы случайно не поменять символы в других ячейках, примените другие способы:
- Чтобы точно скопировать одну формулу, выделите ячейку с формулой, а затем скопируйте формулу, которая отображается в строке формул (а не в ячейке) в верхней части окна. Нажмите esc, чтобы закрыть строку формул, а затем вставьте формулу в нужные ячейки.^[5]
- Нажмите Ctrl` (обычно этот символ находится на клавише с символом ~), чтобы перейти в режим просмотра формул. Скопируйте формулы, а затем вставьте их в простейший текстовый редактор, такой как Блокнот или TextEdit. Скопируйте формулы из текстового редактора и вставьте их в нужные ячейки электронной таблицы.^[6] Еще раз нажмите Ctrl`, чтобы вернуться в обычный режим работы с таблицей.
Реклама

1

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

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

Столбец A Столбец B Столбец C

строка 2 50 7 =A2*2

строка 3 100

строка 4 200

строка 5 400
2
Запомните, что такое относительный адрес ячейки. В формуле такой адрес указывает на относительную позицию ячейки. Например, если в ячейке С2 есть формула «=A2», эта формула указывает на значение, которое находится двумя столбцами левее. Если скопировать формулу в ячейку С4, эта формула опять укажет на значение, которое находится двумя столбцами левее — то есть в ячейке С4 отобразится формула «= A4».

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

Столбец A Столбец B Столбец C

строка 2 50 7 =A2*2

строка 3 100

строка 4 200 =A4*2

строка 5 400
- Этот принцип распространяется на ячейки в разных строках и столбцах. Например, если скопировать ту же формулу из ячейки C1 в ячейку D6 (не показана), в формуле появится адрес ячейки, которая расположена одним столбцом правее (C → D) и пятью строками ниже (2 → 7), а именно адрес «B7».
3

Используйте в формуле абсолютный адрес ячейки. Сделайте это, чтобы формула не менялась автоматически. Чтобы относительный адрес превратить в абсолютный, введите символ $ перед буквой столбца или номером строки, которые не должны измениться.^[7] Далее приведены примеры (исходная формулы выделена жирным шрифтом; также показана эта формула, скопированная в другие ячейки):

Относительный столбец, Абсолютная строка (B$3):
В формуле присутствует абсолютная строка «3», то есть формула будет всегда ссылаться на третью строку.

Столбец A Столбец B Столбец C

строка 1 50 7 =B$3

строка 2 100 =A$3 =B$3

строка 3 200 =A$3 =B$3

строка 4 400 =A$3 =B$3

Абсолютный столбец, Относительная строка ($B1):
В формуле присутствует абсолютный столбец «В», то есть формула будет всегда ссылаться на столбец «В».

Столбец A Столбец B Столбец C

строка 1 50 7 =$B1

строка 2 100 =$B2 =$B2

строка 3 200 =$B3 =$B3

строка 4 400 =$B4 =$B4

Абсолютные столбец и строка ($B$1):
В формуле присутствует абсолютный столбец «В» и абсолютная строка «1», то есть формула будет всегда ссылаться на столбец «В» и первую строку.

Столбец A Столбец B Столбец C

строка 1 50 7 =$B$1

строка 2 100 $B$1 $B$1

строка 3 200 $B$1 $B$1

строка 4 400 $B$1 $B$1
4

Используйте клавишу F4, чтобы относительный адрес превратить в абсолютный. В формуле выделите адрес ячейки, щелкнув по ней, а затем нажмите клавишу F4, чтобы ввести или удалить символ(ы) $. Нажимайте F4 до тех пор, пока не создадите нужный абсолютный адрес, а затем нажмите Enter.^[8]

Реклама

	Столбец A	Столбец B	Столбец C
строка 2	50	7	=A2*2
строка 3	100
строка 4	200
строка 5	400

	Столбец A	Столбец B	Столбец C
строка 2	50	7	=A2*2
строка 3	100
строка 4	200		=A4*2
строка 5	400

	Столбец A	Столбец B	Столбец C
строка 1	50	7	=B$3
строка 2	100	=A$3	=B$3
строка 3	200	=A$3	=B$3
строка 4	400	=A$3	=B$3

	Столбец A	Столбец B	Столбец C
строка 1	50	7	=$B1
строка 2	100	=$B2	=$B2
строка 3	200	=$B3	=$B3
строка 4	400	=$B4	=$B4

	Столбец A	Столбец B	Столбец C
строка 1	50	7	=$B$1
строка 2	100	$B$1	$B$1
строка 3	200	$B$1	$B$1
строка 4	400	$B$1	$B$1

Советы

Если при копировании формулы в ячейке отобразился значок в виде зеленого треугольника, Excel обнаружил ошибку. Внимательно посмотрите на формулу, чтобы выяснить, что пошло не так.^[9]
Если при точном копировании формулы вы случайно заменили знак равенства (=) на символ ? или *, поиск этих символов ничего не даст. В этом случае ищите символы ~? или ~*.^[10]
Щелкните по ячейке и нажмите Ctrl‘ (апостроф), чтобы скопировать в нее формулу из ячейки, которая находится над выбранной ячейкой.^[11]

Предупреждения

Возможно, у вашей версии Excel немного другой интерфейс, чем тот, который показан на скриншотах в этой статье.

Об этой статье

Эту страницу просматривали 131 980 раз.

Была ли эта статья полезной?

Источник

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

Проблема

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

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

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

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

Ссылки по теме

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

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

References

About This Article

Is this article up to date?

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

Проверка и исправление ссылок на ячейки в новом месте

Перенос формулы в другое место

Вставка значений, а не формул

Копирование в Excel.

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

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

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

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

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

Проблема

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

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

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

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

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

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

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

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

Как сделать формулу в excel с одного листа на другой?

Ссылка на лист в формуле Excel

Как сделать ссылку на лист в Excel?

Ссылка на лист в другой книге Excel

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

Из чего состоит формула

Использование операторов

Арифметические

Операторы сравнения

Математические функции и графики

Отличие в версиях MS Excel

Заключение

Файл примеров

Видеоинструкция

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

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

Первый шаг.

Второй шаг.

Третий шаг.

Четвертый шаг.

Второй способ переноса данных из одной таблицы в другую — это использование сводных таблиц в программе «Excel».

Как делать сводные таблицы в программе «Excel» и для чего они нужны.

Третий способ самый эффективный и наиболее автоматизированный — это использование меню надстройки «Power Query».

Смысл способа в следующем:

Самый популярный способ копировать. Автозаполнение

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

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

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

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

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

Как перенести таблицу в Excel без изменений?

Как продолжить таблицу в Excel?

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

Вставка значений, а не формул

Как скопировать таблицу на другой лист?

Копирование листа в другую книгу

Как перенести часть таблицы в Excel?

Как перетащить таблицу в Excel?

Как скопировать лист в Excel с сохранением формата?

Как продолжить таблицу на компьютере?

Добавление ячейки

Как добавить несколько строк в Excel?

Как добавить строку в Excel?

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

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

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

Замена формул на вычисленные значения

Как зафиксировать значение ячейки в Excel?

Как скопировать таблицу Эксель на новый лист?