Перед нами стоит задача рассчитать несколько основных коэффициентов, позволяющих оценить эффективность деятельности компании в динамике.
Для расчета коэффициентов придется использовать данные из разных отчетов (баланс: форма № 1 и форма № 2), которые находятся в разных файлах.
Для начала переместим нужные нам листы в один файл, а затем сделаем сводную таблицу на новом листе.
Предположим, что у нас есть файл с Формой № 1 Баланса, где хранятся балансы за все периоды. Наша задача скопировать нужный нам лист и перенести в новый файл.
Копирование листа из одного файла в новый файл
Активизируем нужный нам лист (внизу страницы нажимаем на него левой клавишей мыши), вызываем меню правой клавишей мыши и выбираем «Переместить/скопировать»:
Отмечаем галочкой квадратик «создать копию» и в окошке «в книгу» выбираем «(новая книга)»:
Нажимаем ENTER и получаем новый файл, содержащий нужный нам лист. В этот лист мы будем помещать все данные о балансах за все периоды, и на основе этих данных рассчитывать коэффициенты в динамике.
Переименуем лист для удобства работы в «форма1». Для этого щелкнем 2 раза по текущему названию листа (фон станет черным):
и переименуем в «форма1»:
Лист готов. Таблицу можно продолжить, добавляя значения следующих периодов.
Учитывая то, что для расчета коэффициентов нам нужны значения выручки за период, следующим шагом мы добавим в файл лист формы № 2 баланса (либо данные по выручке за период из других источников).
Копирование/перемещение листа из одного файла в другой
Для того, чтобы перенести лист из одного файла в другой, необходимо активизировать нужный лист (внизу страницы нажать на него левой клавишей мыши), вызвать меню правой клавишей мыши и выбирать «Переместить/скопировать»:
Отмечаем галочкой квадратик «создать копию», если хотим, чтобы переносимый лист остался в первоначальном файле (если нужно просто перенести лист, галочку не ставим), и в окошке «в книгу» выбираем имя файла, в который переносим лист (в нашем случае koefficienty.xls):
Выбираем лист, перед которым хотим разместить переносимый лист, либо помещаем лист в конце после всех имеющихся в файле листов:
Переименуем лист для удобства работы. Для этого щелкнем 2 раза по текущему названию листа (фон станет черным):
и переименуем в «форма2»:
По аналогии с листом «Форма1» мы будем добавлять в этот лист данные за все следующие периоды.
Ну, а теперь добавим новый лист в наш файл, на котором мы будем производить расчеты коэффициентов эффективности деятельности компании.
Делается это очень просто. Вызываем «Вставку» в главном меню и выбираем «Лист»:
Подготовим таблицу для занесения данных и возьмем для примера расчет Коэффицента оборачиваемости товарных запасов:
Как видим, один из показателей нужно взять из Формы № 1 (Среднегодовое значение товарных запасов), а другой — из Формы № 2 (Себестоимость проданных товаров).
Для того, чтобы сделать формулу, ссылающуюся на разные листы, произведем следующие шаги:
- активизируем нужную ячейки и пишем в ней знак «=»
- выделяем ячейку со значением себестоимости на листе «Форма2»
- пишем знак деления — «/»
- открываем первую скобку «(«, в которой будем рассчитывать среднегодовое значение товарных запасов), и вторую скобку «(«, в которую заключим сумму значений товарных запасов за период
- открываем лист «Форма1» и добавляем после двух открытых скобок сумму товарных запасов на 5 дат
- закрываем первую скобку «)» и делим сумму товарных запасов на 5 дат на «5» (вычисляем среднее значение)
- закрываем вторую скобку «)» и нажимаем ENTER
Получаем следующее выражение:
Для расчета периода, в течение которого запасы находятся на складе используем обратную формулу:
С заведением формулы мы закончили. А теперь предположим, что у нас не два коэффифиента, а гораздо больше. Неужели нам каждый раз придется заводить все эти сложные формулы?
Решение есть!
Как скопировать формулы, ссылающиеся на разные листы
Для начала скопируем готовые формулы, для чего активизируем все ячейки с формулами и правой кнопкой мыши вызовем «Копировать»
и вставим их в соседний столбец, выделив верхнюю ячейку столбца и выбрав «Вставить» в меню, вызванном правой кнопкой мыши:
Как сделать массовую замену формул
Осталась самая малость. Скопированные формулы повторяют имеющиеся, но ссылаются не на те ячейки, которые нам нужны. Это происходит из-за того, что в одном листе (Форма2) для расчета показателя за год используется всего одна ячейка, а в другом листе (Форма1) — 5 ячеек.
Т.е. в части ссылок на лист «Форма2» ссылки при копировании автоматически идут на нужные ячейки, а в листе «Форма1» нам придется передвинуть формулы на 4 ячейки (причем речь идет о буквенных обозначениях, цифровые меняться не будут).
Предлагаю сделать это «оптом» с помощью функции «Поиска и замены значений». Для этого проверим ячейки, на которые ссылается формула и уточним, на какие ячейки она должна ссылаться.
В нашем случае, второй столбик после копирования формулы ссылается в листе «Форма1» на ячейки J, K, L, M, N, в то время как для правильного результата должны ссылаться на M, N, O, P, Q. Как видим, две буквы повторяются. Их мы оставим. Значит, нам останется поменять J, K, L на O, P, Q.
А дальше все проще простого. Выделяем нужный столбец по серому полю (обязательно, иначе мы, не желая того, можем поменять значения других ячеек), вызываем функцию «поиска и замены» и меняем J на O, K на P, L на Q и т.д.
Так же будем действовать и при заполнении данных для следующих периодов. Таким образом, мы избавим себя от сложной работы, требующей пристального внимания.
На всякий случай проверяйте хотя бы одну ячейку из столбца на правильность занесения формул.
