Скопіюйте формули в Excel - Чайові

Відеоролик: КАК КОПИРОВАТЬ ФОРМУЛУ БЕЗ ПОТЕРИ ССЫЛОК НА ЯЧЕЙКИ MS EXCEL

Зміст

Кроки
Порада
Увага

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

Кроки

Спосіб 1 з 4: Перетягніть, щоб скопіювати формулу в кілька комірок

Введіть формулу в одну клітинку. Будь-яка формула починається зі знака = потім функція або алгоритм, який ви хочете використовувати. Тут ми використаємо просту електронну таблицю як приклад і перейдемо до додавання стовпців A і B разом:
Натисніть Enter, щоб обчислити формулу. Після натискання клавіші введення формула буде введена і розрахована. Хоча відображається лише кінцевий результат (19), насправді таблиця все ще містить вашу формулу.
Клацніть нижній правий кут вихідної комірки. Перемістіть вказівник миші в правий нижній кут комірки, яку ви щойно відредагували. Курсор перетвориться на знак + сміливий.
Утримуйте і перетягніть мишу вздовж стовпця або рядка, куди потрібно скопіювати формулу з вихідної комірки. Утримуйте комп'ютерну мишу та перетягуйте курсор вниз, стовпець вниз або горизонтально, за рядок, у якому потрібно редагувати формулу (виділена область). Формула, яку ви ввели раніше, буде автоматично введена в клітинки, які ви щойно виділили. Відносні посилання на комірки оновлюються автоматично на основі комірок, які мають однакове відносне положення. Використана формула та результати, відображені в нашій прикладі електронної таблиці, виглядатимуть так:
Двічі клацніть на знак плюс, щоб заповнити формулу зі стовпця. Замість того, щоб утримувати мишу та перетягувати, ви можете перемістити мишу в нижній правий кут і двічі клацнути, коли вказівник перетвориться на знак +. На цьому етапі формула буде автоматично скопійована до всіх комірок стовпця.
- Excel перестане заповнювати стовпець, коли у нього з’явиться порожня комірка. Отже, коли в опорних даних є пробіл, вам доведеться повторити цей крок, щоб продовжити заповнення розділу стовпця під пробілом.
реклама

Спосіб 2 з 4: Вставте, щоб скопіювати формулу в кілька комірок

Введіть формулу в одну клітинку. Для будь-якої формули потрібно починати зі знака = потім використовуйте потрібну функцію або алгоритм. Тут ми візьмемо простий приклад електронної таблиці і продовжимо додавати два стовпці A і B разом:
Натисніть Enter, щоб обчислити формулу. Після натискання клавіші введення формула буде введена і розрахована. Незважаючи на те, що відображаються лише результати (19), таблиця все одно зберігає ваші формули.
Клацніть та скопіюйте комірку, що містить оригінальну формулу (CTRL + C).
Виділіть клітинки, куди потрібно скопіювати наведену вище формулу. Клацніть на клітинку та перетягуйте вгору / вниз за допомогою миші або клавіш зі стрілками. На відміну від методу перетягування в кут, комірки, куди потрібно скопіювати формулу, не повинні знаходитися поблизу вихідної комірки.
Вставити (CTRL + V). реклама

Метод 3 з 4: Скопіюйте формулу правильно

За допомогою цього методу можна швидко скопіювати формулу, не змінюючи посилання на комірки. Іноді ваша велика електронна таблиця заповнена формулами, і ви просто хочете скопіювати їх точно. Перетворити все на абсолютні посилання на клітинки (описано в розділі про посилання на комірки) не так весело, особливо коли вам доведеться повернутися до нього після того, як закінчите. Використовуйте цей метод для швидкого переключення формул, які використовують відносні посилання на комірки в іншому місці, не змінюючи посилання. У нашому прикладі електронної таблиці стовпець C потрібно скопіювати в стовпець D:
- Якщо ви хочете скопіювати формулу лише в одній комірці, перейдіть до останнього кроку ("Спробуйте різні методи") цього розділу.
Відкриває вікно пошуку. У більшості версій Excel це вікно можна знайти, натиснувши вкладку Домашня сторінка у верхній частині вікна Excel, а потім клацнувши пункт Знайти та вибрати у розділі "Редагування" вікна Excel. цю картку. Ви також можете використовувати комбінацію клавіш CTRL F.
Знайдіть і замініть знак "=" іншим символом. Введіть "=", натисніть "Знайти все", а потім введіть інший символ у полі "Замінити на". Будь-яка формула (завжди починається зі знака =) буде автоматично перетворена в рядок символів, що починається з певного символу. Давайте завжди використовуйте символи, яких ще немає у вашій електронній таблиці. Наприклад, замініть його на # або &, або довший рядок, наприклад ## &.
- Не використовувати * або? оскільки вони ускладнять наступні кроки.
Скопіюйте та вставте комірки. На цьому етапі ви можете вибрати будь-які комірки, які потрібно скопіювати, та вставити їх в інше місце. Оскільки Excel більше не розуміється як формули, тепер ви можете точно скопіювати вищевказані комірки.
Знову скористайтеся функцією Знайти та замінити, щоб повернути її назад. Тепер, коли у вас є рецепт, де ви його хочете, тепер ви можете скористатися "Знайти все" та "Замінити на", щоб змінити його. У нашому прикладі електронної таблиці ми знайдемо рядок "## &", замінимо її знаком "=", щоб клітинки знову були формулами і могли продовжувати редагування електронної таблиці, як зазвичай:
Спробуйте інші методи. Якщо з якихось причин вищевказаний метод не працює або якщо ви стурбовані тим, що інший вміст у комірці може випадково змінитися параметром «Замінити все», для вас існує кілька інших способів:
- Щоб скопіювати формулу комірки, не змінюючи її посилання, виділіть комірку та скопіюйте формулу, що відображається у рядку формул у верхній частині вікна (не самої комірки). Натисніть клавішу esc, щоб закрити рядок формул, а потім вставте формулу туди, куди ви хочете.
- Натисніть Ctrl` (зазвичай на тій самій клавіші, що і ~), щоб перевести таблицю у вигляд формул. Скопіюйте формули та вставте їх у текстовий редактор, наприклад Блокнот або TextEdit. Скопіюйте його ще раз і вставте назад у потрібне місце на аркуші. Натисніть Ctrl`, щоб повернутися до звичайного перегляду.
реклама

Метод 4 з 4: Використовуйте абсолютні та відносні посилання на клітинки

Використовуйте відносні посилання на клітинки у формулі. У формулі Excel "посилання на комірку" - це адреса комірки. Ви можете ввести або натиснути клітинку, яку хочете використовувати під час введення формули. У таблиці нижче формула стосується комірки А2:
Зрозумійте, чому їх називають відносними посиланнями. У формулі Excel відносне посилання використовує відносне розташування адреси комірки. Наприклад, клітинка C2 має формулу "= A2", яка є відносним посиланням на значення клітинки зліва, за одну клітинку. Якщо скопіювати цю формулу до C4, вона все одно буде посилатися на ліву комірку, що знаходиться за однією коміркою: тепер "= A4".
- Відносні посилання також працюють для комірок, які не знаходяться в одному рядку та стовпці. Коли ви копіюєте ту саму формулу в комірці C1, у клітинку D6 (не на малюнку), Excel змінює посилання на один стовпець праворуч (C → D) і на п'ять рядків вниз (2 → 7), а "A2" на "B7. ".
Ви також можете використовувати абсолютні посилання. Припустимо, ви не є хоче, щоб Excel автоматично змінив свою формулу. Потім, замість використання відносного посилання на клітинку, ви можете перетворити його на посилання абсолютний додавши символ $ перед кількістю стовпців або рядків, які потрібно зберегти при копіюванні в будь-яке місце. У наведених нижче прикладах електронних таблиць оригінальна формула представлена великим жирним шрифтом, і одночасно відображається результат копіювання-вставки в інші комірки:
Використовуйте ключ F4 для перетворення між абсолютним та відносним. Клацніть, щоб виділити посилання на клітинки у формулі. Потім натисніть клавішу F4, і символ $ автоматично буде доданий або видалений у вашому посиланні. Продовжуйте натискати клавішу F4, поки не з’явиться потрібне абсолютне / відносне посилання, і натисніть enter. реклама

Порада

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