«Розумне» копіювання формул в MS Excel

«Розумне» копіювання формул в MS Excel

Копіювання даних — чи не найпоширеніша операція під час роботі з MS Excel, як, утім, і з будь-яким іншим додатком Windows. Проте Excel у цьому плані має одну особливість. Операція копіювання в цій програмі не просто дублює дані, вона одночасно змінює посилання у формулах, вибудовує правильну систему адрес. І це — дуже важливий момент при розробці електронних таблиць. У переважній більшості випадків проблем щодо цього питання не виникає. Проте нещодавно до мене потрапив документ, коли стандартні інструменти копіювання формул виявилися малоефективними. У результаті, провозившись зі звітом майже півгодини, я дійшов висновку, що мені потрібен інший, більш швидкий спосіб вирішення проблеми. У чому він полягає і де може бути корисним практикуючому бухгалтеру, я розповім у цій статті. Микола КАРПЕНКО, канд. техн. наук, доцент кафедри прикладної математики та інформаційних технологій Харківської національної академії міського господарства

Традиційно розпочнемо із завдання, тобто з’ясуємо: що потрібно отримати і в чому полягає проблема. Отже, потрібно побудувати порівняльну таблицю показників для оцінки господарської діяльності групи підприємств. У документі вони називаються «учасники інвестиційної діяльності». Форму цього документа наведено на рис. 1. У першій колонці таблиці (вона розташована на листі з ім’ям «СрТбл») перелічені найменування показників. Праворуч розміщені їх значення. Ці значення згруповано за підприємствами. Причому для кожного підприємства показники відображено в динаміці за період 2008 — 2012 роки.

Формули для розрахунку показників відомі. Цими формулами ми і збираємося заповнити підсумкову таблицю.

Тепер щодо даних для розрахунку. Вони беруться зі стандартних документів фінансової звітності — «Форма 1», «Форма 2», «Форма 3». Дані в документах показані реальні. Єдине, що я змінив, — це прибрав назви підприємств, замінивши їх на абревіатуру, що нічого не означає. Причина такої дії, думаю, зрозуміла.

Кожен із документів оформлений у вигляді окремого листа MS Excel. Тобто для кожного підприємства є свій лист з документом «Форма 1», окремий лист з документом «Форма 2» і так далі. Ці листи зібрано в одній робочій книзі разом з підсумковим документом. І (що дуже важливо!) усі листи мають однакову структуру. Тобто якщо показник «Залишкова вартість» у документі «Форма 1» для підприємства «СНЭМ» знаходиться на листі «СНЭМф1» у комірці «C4», то для інших підприємств він розташований у такій самій комірці, тільки на інших листах. Фрагменти документів «Форма 2» і «Форма 3» наведено на рис. 2 і 3.

Наше завдання — заповнити лист «СрТбл» розрахунковими формулами для одного підприємства та спробувати скопіювати ці формули на всю таблицю. Приступимо.

Вводимо формули
до підсумкового звіту

Зрозуміло, всі формули до підсумкового документа ми не вводитимемо. Практичного сенсу в цьому немає жодного. Зате є ризик втратити головну мету цієї статті. Щоб зрозуміти суть проблеми, досить обмежитися буквально парою значень, і не більше того. Що ж до детального звіту з усіма показниками, ви можете скачати його на сайті редакції за адресою www.bk.factor.ua. Почнемо з п’ятого рядка таблиці на рис. 1. Щоб заповнити її формулами, робимо так.

1.  Відкриваємо документ, переходимо на лист «СрТбл» з підсумковим звітом.

2.  Стаємо на комірку «B5».

3.  Вводимо до неї формулу «=СНЭМф2!C55». Для цього клацаємо мишею по ярличку листа «СНЭМф2» (документ «Форма 2» підприємства «СНЭМ»). Потім клацаємо по комірці «C55» та натискуємо «Enter».

4.  Повертаємося на лист «СрТбл».

5.  Залишаючись на комірці «B5», копіюємо формулу в буфер обміну (комбінація «Ctrl+C»).

6. Виділяємо блок комірок «C5:F5».

7.  Вставляємо вміст з буфера обміну (комбінація «Ctrl+V»). Поки що все правильно. У комірках «С5:А5» ми отримали правильні посилання на комірки листа « СНЭМф2» (рис. 3).

8.  Переходимо на комірку «B6». Тут має бути формула «=СНЭМф2!C38/СНЭМф1!C63» з посиланнями на два листи з документами «Форма 1» і «Форма 2». Зрозуміло, таку формулу теж зручно вводити, клацаючи мишею по комірках робочих листів.

9.  Далі копіюємо формулу в комірки «C6:F6». І таким же способом заповнюємо розрахункову частину, рухаючись вниз до кінця підсумкового звіту.

До цього моменту все йде нормально. У результаті наших дій ми заповнили таблицю формулами для одного підприємства. Первісно кожна формула розраховувала певний показник на 2008 рік. Але ми скористалися тим фактом, що в документах «Форма 1» і «Форма 2» розташування вихідних значень у динаміці точно збігається з підсумковим документом. Скопіювавши формули на чотири комірки вправо, ми отримали показники для одного підприємства за всіма періодами починаючи з 2009 по 2012 рік. Настав час скопіювати ці результати для інших підприємств підсумкової таблиці. Здавалося б, усе просто. Та де там.

У чому полягає
проблема

При заповненні формулами звіту, наведеного на рис. 1, виникає як мінімум два запитання.

1. Як змінити назви листів?

Зараз у підсумковій таблиці у нас є готові формули для визначення показників за одним конкретним підприємством. Ці формули ми повинні скопіювати вправо так, щоб отримати показники для інших учасників інвестиційної діяльності. Вихідні дані для цих розрахунків знаходяться на інших листах робочої книги. Зрозуміло, що в нових формулах повинні фігурувати саме ці, оновлені назви листів. Наприклад, у комірці «B5» розміщена формула «=СНЭМф2!C55». Вона визначає значення показника «Чистий прибуток (збиток) на одну просту акцію» для підприємства «СНЭМ». У комірці «G5» повинна з’явитися формула для визначення такого ж показника за підприємством «КВСЗ». Виглядає ця формула так: «=КВСЗф2!C55» (відмінності в цих виразах виділено напівжирним зображенням). Заново вводити кожну формулу, указуючи нові посилання на робочі листи довго і непродуктивно. Потрібно придумати інший спосіб вирішення завдання.

До речі, зверніть увагу, що у зазначених формулах відрізняються тільки назви листів. Адреси комірок у них повністю збігаються. І звідси виникає друге запитання.

2. Що робити з адресацією комірок?

У наших формулах усі посилання використовують звичайну адресацію комірок. Коли ми копіювали ці формули в межах одного підприємства, усе було добре. Такий спосіб адресації був нам дуже доречним. Наприклад, формула «=СНЭМф2!C55» з комірки «B5», будучи скопійованою в блок «C5:F5», спричинила результати «=СНЭМф2!D55», «=СНЭМф2!E55», «=СНЭМф2!G55». І це правильно.

Але тепер ми повинні заповнити формулами блок «G5:K5». Ці формули посилатимуться на лист «КВСЗ», але адреси колонок у них повинні залишатися незмінними! Наприклад, у комірці «G5» має вийти «=КВСЗф2!C55», у комірці «H5» — «=КВСЗф2!D55» і так далі. Тобто в нових формулах адреси комірок залишаються, змінюються тільки назви листів. Тому звичайне копіювання формул з блока «C5:F5» у блок «G5:K5» нам не підходить, оскільки в результаті зміняться адреси колонок, після чого коригувати їх доведеться вручну. А це вимагатиме часу та зусиль, причому чималих. Проте не все так погано. Подивимось, що можна зробити в ситуації, що утворилася.

Шляхи її вирішення

Для швидкого копіювання формул в підсумковій таблиці (лист «СрТбл») можна використовувати два підходи: копіювати формули як текст або задіяти допоміжний робочий лист. Кожний спосіб має свої переваги та недоліки. Ми розглянемо обидва варіанти, а там вирішимо, який із них краще і чому.

КОПІЮВАННЯ ФОРМУЛ В РЕЖИМІ ТЕКСТУ

Цей прийом дозволяє певною мірою впоратися з проблемою номер 2, тобто уникнути невиправданого коригування адрес при копіюванні формул. Робимо так.

1.  Відкриваємо документ, переходимо на робочий лист «СрТбл».

2.  Стаємо на комірку «B5» цього листа.

3. Натискуємо клавішу «F2». Стане доступним для редагування вміст комірки.

4.  Виділяємо текст формули та копіюємо його в буфер обміну (комбінація «Ctrl+C»).

5. Натискуємо клавішу «Esc» (повертаємося на робочий лист).

6. Стаємо на комірку «G5» та вставляємо до неї вміст буфера обміну (комбінація «Ctrl+V»). Тепер у комірці «G5» знаходиться точна копія формули з комірки «B5» — «=СНЭМф2!C55». Зверніть увагу, що адреси колонок Excel не змінив, оскільки ми скопіювали не фрагмент робочого листа у вигляді комірки, а її вміст, тобто — текст.

7.  Копіюємо формулу з «G5» в комірки «H5:K5» звичайним способом. У цьому випадку Excel виправить номери колонок, і це саме те, що нам потрібно!

8. Повторюємо аналогічні дії для рядків підсумкової таблиці, що залишилися.

З адресацією колонок ми впоралися. Залишається відкоригувати назви листів. Зрозуміло, що вручну ми цього не робитимемо. Зараз саме час пригадати, що формули — це звичайний текст. А це означає, що працювати з ними можна інструментом пошуку та заміни. Робимо так.

1.  Відкриваємо документ, переходимо на лист «СрТбл».

2.  Стаємо на комірку «G5».

3. Натискуємо клавішу «F2» (входимо в режим редагування комірки).

4. У тексті формули виділяємо назву листа. Це буде частина тексту, яка виділена напівжирним зображенням: «=СНЭМф2!C55».

5.  Копіюємо цей текст в буфер обміну (комбінація «Ctrl+C»).

6. Натискуємо клавішу «Esc», щоб повернутися на робочий лист.

7. Виділяємо блок колонок «G:K».

8.  Викликаємо інструмент «Поиск и замена». Найзручніше це зробити комбінацією «Ctrl+H». Відкриється вікно «Найти и заменить», наведене на рис. 4.

9. У цьому вікні стаємо на полі «Найти:» та вставляємо вміст із буфера (комбінація «Ctrl+V»). У результаті в цьому полі має з’явитися текст «СНЭМф2».

10. Переходимо на поле «Заменить на:» та вводимо нову назву листа. У нашому прикладі — це «КВСЗф2».

11. Натискуємо на кнопку «Заменить все» (рис. 4). Через кілька секунд з’явиться повідомлення про те, що ми відкоригували в документі 245 формул (рис. 5).

12. Таким же способом замінюємо назви інших листів, які зустрічаються у формулах.

13. Повторюємо аналогічні дії для заповнення частини підсумкової таблиці, що залишилася, за всіма підприємствами.

Думаю, ідея зрозуміла. На цей момент ми сформували на листі розрахунок показників для двох підприємств. Щоб розширити таблицю далі та сформувати розрахунки для решти учасників інвестиційної діяльності, ми повинні повторити всю процедуру: спочатку скопіювати формули в текстовому режимі, потім контекстною заміною відкоригувати номери листів. Остаточний варіант таблиці наведено на рис. 6. У принципі такий підхід має право на життя. Але я пропоную розглянути ще один, не менш швидкий спосіб вирішення завдання.

КОПІЮВАННЯ ФОРМУЛ ЧЕРЕЗ ДОПОМІЖНИЙ РОБОЧИЙ ЛИСТ

Якщо уважно придивитись, то в попередньому прикладі найбільше часу ми витратили на копіювання текстів формул із колонки «В» у колонку «G». Для невеликих документів це непомітно. Але в нашому випадку ситуація інша: у таблиці на рис. 1 зібрано 56 показників. Виходячи з цього, доведеться 56 разів скопіювати формулу в текстовому режимі, після чого повторити цю процедуру для кожного підприємства. А таких підприємств в таблиці 15… Неважко порахувати, що у результаті процес копіювання формул доведеться повторити 840 разів. Зрозуміло, що робити це, м’яко кажучи, не хотілося б. Тим більше що є простий спосіб вирішення цієї проблеми. Принцип наших дій буде таким.

За основу візьмемо робочий лист «СрТбл», в якому є готові формули для одного підприємства. Нагадаю, що на рис. 1 — це колонки «B:F». Потім ми створимо точну копію цього листа. При такій операції всі посилання, імена листів та адреси комірок збережуться. Формули у блоці «B:F» на новому листі працюватимуть правильно. Тепер саме час пригадати про операцію «Вырезать» програми MS Excel. Річ у тім, що при переміщенні даних у межах листа (або між листами) Excel не коригує адреси комірок. І в цьому сенсі така операція схожа на копіювання формул у текстовому режимі. Отже, ми можемо перемістити блок з формулами з робочого листа на лист «СрТбл» і тим самим вирішити проблему. Подивимось, як це виглядає практично. Робимо так.

1.  Відкриваємо документ, ставимо покажчик миші на ярличок листа «СрТбл».

2.  Утримуючи клавішу «Ctrl», перетягуємо його вбік у межах області ярликів MS Excel (управо або вліво — неважливо).

3. Відпускаємо клавішу «Ctrl». У книзі з’явиться новий лист з ім’ям «СрТбл (2)». Переходимо на цей лист. Дивимося на формули — вони тут такі самі, як і на листі «СрТбл».

4.  Виділяємо блок колонок «B:F» на листі «СрТбл (2)».

5.  Вирізаємо цей блок у буфер обміну (можна скористатися комбінацією «Ctrl+X»).

6.  Повертаємося на лист «СрТбл».

7. Стаємо на комірку «G1» — із колонки «G» ми маємо намір вставити нові формули.

8. Натискуємо «Ctrl+V» — вставляємо вміст із буфера обміну. У колонках «G:K» з’являться ті самі значення, що й у блоці «B:F».

Тут я пропоную зробити паузу та подивитись на формули в комірках блока «G:K». Завдяки тому, що ми перемістили дані за допомогою функції «Вырезать», Excel не змінив адреси комірок і всі формули у блоці «G:K» точно повторюють відповідні вирази блока «B:F». Схожий результат ми вже отримували, копіюючи тексти формул. Але тепер нам удалося заповнити всю таблицю за один прийом.