Як копіювати формули в excel

В Excel можна швидко скопіювати формулу в безліч осередків одного рядка чи одного шпальти, але результат не завжди буде таким, який планувався. Якщо ви не домоглися бажаного результату або в осередках з`явилися повідомлення #REF і / DIV0, почитайте, що таке абсолютні та відносні адреси осередків, щоб з`ясувати причину помилки. Пам`ятайте, що вносити зміни в усіх елементах таблиці, яка складається з 5000 рядків, не доведеться - для цього є методи, які дозволяють автоматично оновити формулу в певній клітинці або скопіювати її без зміни значень.

кроки

Метод 1 з 4:

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

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. Натисніть на маркер в нижньому правому куті комірки. Наведіть курсор в правий нижній кут комірки з формулой- покажчик перетвориться в символ «+».

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 з 4:

Копіювання формули за допомогою вставки

*Таблиця*
	стовпець 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. Клацніть по комірці з формулою, а потім натисніть CTRL + C, щоб скопіювати її.

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

5. Вставте формулу в виділені осередки. Для цього натисніть CTRL + V.

Метод 3 з 4:

Точне копіювання формули

1. Скористайтеся цим методом, щоб скопіювати формулу без зміни адрес осередків, які входять в неї. Іноді таблиця повна формул, і їх потрібно точно скопіювати (тобто без змін). Якщо міняти відносні адреси осередків на абсолютні адреси у всіх формулах, можна витратити багато часу (це тим більше не прийнятно, якщо в майбутньому вам доведеться знову міняти адреси). Цей метод має на увазі копіювання формули з відносними адресами осередків в інші осередки так, що формула не зміниться.Наприклад, скопіюємо весь вміст стовпця C в стовпець D:

*Таблиця*
	стовпець A	стовпець B	стовпець C
рядок 1	944	жаби	=A1/ 2
рядок 2	636	жаби	=A2/ 2
рядок 3	712	тритони	=A3/ 2
рядок 4	690	змії	=A4/ 2

Якщо ви хочете скопіювати просто одну формулу, перейдіть до кроку «Скористайтеся альтернативним методом» цього розділу.

2. Відкрийте вікно «Знайти». У більшості версій Excel клацніть по вкладці Основне у верхній частині вікна Excel, а потім клацніть по Знайти і вибрати в розділі «Редагування». Також можна натиснути клавіші CTRL + F.

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

*Таблиця*
	стовпець A	стовпець B	стовпець C
рядок 1	944	жаби	## &A1/ 2
рядок 2	636	жаби	## &A2/ 2
рядок 3	712	тритони	## &A3/ 2
рядок 4	690	змії	## &A4/ 2

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

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

*Таблиця*
	стовпець 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

5. Використовуйте функції «Знайти» і «Замінити» ще раз, щоб повернутися до колишніх формулами. Скопіювавши формули як текст, скористайтеся функціями «Знайти всі» і «Замінити на», щоб повернутися до колишніх формулами. У нашому прикладі знайдемо всі символи ## & і замінимо їх на знак рівності (=), щоб в осередках з`явилися формули.

*Таблиця*
	стовпець 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, щоб закрити рядок формул, а потім вставте формулу в потрібні комірки.

Натисніть Ctrl` (зазвичай цей символ знаходиться на клавіші з символом ~), щоб перейти в режим перегляду формул. Скопіюйте формули, а потім вставте їх в найпростіший текстовий редактор, такий як Блокнот або TextEdit. Скопіюйте формули з текстового редактора і вставте їх в потрібні комірки електронної таблиці. Ще раз натисніть Ctrl`, щоб повернутися в звичайний режим роботи з таблицею.

Метод 4 з 4:

Використання відносних і абсолютних адрес осередків

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. Використовуйте у формулі абсолютний адреса осередку. Зробіть це, щоб формула не змінювалася автоматично. Щоб відносний адреса перетворити в абсолютний, введіть символ $ перед літерою стовпчика або номером рядка, які не повинні змінитися. Далі наведені приклади (вихідна формули виділена жирним шріфтом- також показана ця формула, скопійована в інші осередки):

Відносний стовпець, Абсолютна рядок (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.

Поради

Якщо при копіюванні формули в осередку відобразився значок у вигляді зеленого трикутника, Excel виявив помилку. Уважно подивіться на формулу, щоб з`ясувати, що пішло не так.
Якщо при точному копіюванні формули ви випадково замінили знак рівності (=) на символ ? або *, пошук цих символів нічого не дасть. В цьому випадку шукайте символи ~? або ~ *.
Клацніть по комірці і натисніть Ctrl `(апостроф), щоб скопіювати в неї формулу з комірки, яка знаходиться над обраною осередком.