Работа с листами в Excel

Сортировка значений по заданным параметрам. Автофильтр

Сортировка данных по убыванию и возрастанию

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

Как добавить формулу. Перемножаем числа

Как одну дату вычесть из другой, чтобы получить количество дней

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

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

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

Как устранить причины, по которым Excel отказывается считать

Брать или не брать кредит? Как не попасть в долговую яму

EXCEL ДЛЯ "ЧАЙНИКОВ" И НЕ ТОЛЬКО

ВСЕ УРОКИ

Пользовательский поиск

Назад

Таблица расчета основных коэффициентов, оценивающих эффективность деятельности компании* СКАЧАТЬ * При заполнении форм № 1 и № 2 Баланса, коэффициенты будут считаться автоматически. Для того, чтобы продолжить приложенную таблицу, читайте ниже приведенную статью.

Работа с листами в Excel. Коэффициенты эффективности работы предприятия в Excel

Перед нами стоит задача рассчитать несколько основных коэффициентов, позволяющих оценить эффективность деятельности компании в динамике.

Для расчета коэффициентов придется использовать данные из разных отчетов (баланс: форма № 1 и форма № 2), которые находятся в разных файлах.

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

Предположим, что у нас есть файл с Формой № 1 Баланса, где хранятся балансы за все периоды. Наша задача скопировать нужный нам лист и перенести в новый файл.

Копирование листа из одного файла в новый файл

Активизируем нужный нам лист (внизу страницы нажимаем на него левой клавишей мыши), вызываем меню правой клавишей мыши и выбираем «Переместить/скопировать»:

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

Переименуем лист для удобства работы в «форма1». Для этого щелкнем 2 раза по текущему названию листа (фон станет черным):

и переименуем в «форма1»:

Лист готов. Таблицу можно продолжить, добавляя значения следующих периодов.

Учитывая то, что для расчета коэффициентов нам нужны значения выручки за период, следующим шагом мы добавим в файл лист формы № 2 баланса (либо данные по выручке за период из других источников).

Копирование/перемещение листа из одного файла в другой

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

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

Выбираем лист, перед которым хотим разместить переносимый лист, либо помещаем лист в конце после всех имеющихся в файле листов:

Переименуем лист для удобства работы. Для этого щелкнем 2 раза по текущему названию листа (фон станет черным):

По аналогии с листом "Форма1" мы будем добавлять в этот лист данные за все следующие периоды.

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

Делается это очень просто. Вызываем "Вставку" в главном меню и выбираем "Лист":

Подготовим таблицу для занесения данных и возьмем для примера расчет Коэффицента оборачиваемости товарных запасов:

Как видим, один из показателей нужно взять из Формы № 1 (Среднегодовое значение товарных запасов), а другой - из Формы № 2 (Себестоимость проданных товаров).

Для того, чтобы сделать формулу, ссылающуюся на разные листы, произведем следующие шаги:

1. активизируем нужную ячейки и пишем в ней знак "="
2. выделяем ячейку со значением себестоимости на листе "Форма2"



3. пишем знак деления - "/"
4. открываем первую скобку "(", в которой будем рассчитывать среднегодовое значение товарных запасов), и вторую скобку "(", в которую заключим сумму значений товарных запасов за период
5. открываем лист "Форма1" и добавляем после двух открытых скобок сумму товарных запасов на 5 дат
6. закрываем первую скобку ")" и делим сумму товарных запасов на 5 дат на "5" (вычисляем среднее значение)
7. закрываем вторую скобку ")" и нажимаем 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 и т.д.

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

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

Удачи во всем! Ваш Офисный

Если после прочтения статьи у Вас остались вопросы или вы хотели бы видеть в данном разделе определенные темы напишите мне письмо с пометкой "эксель" по адресу: ngt@inbox.ru

Назад    Вверх страницы

Как пронумеровать строки

Как удалить лишние цифры после запятой

Как визуально разделить число на разряды

Как округлить значение

Как сделать скидку

Как посчитать наценку и маржу

Как сделать наценку на число

Как посчитать пеню

Как подготовить прайс-лист

Как внести изменения в готовый прайс-лист

Как сделать наценку на базовый прайс-лист, чтобы клиент этого не заметил

Как рассчитать точку безубыточности

Как рассчитать цену с учетом заданной маржи

Как создать базу данных

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

Как составить список студентов, не допущенных к экзамену по данным о сдаче зачетов

Как вычленить клиентов, сумма задолженности которых больше судебных издержек

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

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

Сортировка подобных значений. Автофильтр

  2011-2016   Перепечатка материалов возможна лишь при указании прямой индексируемой гиперссылки