Журнал, справочная система и сервисы
№23
Декабрь

В свежем «Главбухе»

Не сообщаем в службу занятости об увольнении сотрудников

Подписка
Срочно заберите все!
№23

Excel упрощает работу бухгалтеру

13 июля 2010 553 просмотра

Найти сумму затрат по каждой статье расходов очень просто

В Excel есть функция, которая очень может пригодиться бухгалтеру. Это математическая функция «СУММЕСЛИ». Она позволит быстро и безошибочно посчитать сумму необходимых данных в таблице. Рассмотрим рис. 1.

Рис. 1

Например, учет текущих расходов компании ведется по датам. Притом учитывается, сколько расходов приходится на каждого сотрудника. А в конце квартала или даже года нужно посчитать, сколько расходов понесла компания, например, только на бумагу и картриджи или сколько потратил Сидоров за год на командировки. Вариантов выборки может быть много.

Бухгалтеры на практике часто считают нужные суммы с помощью калькулятора и маркера, распечатав таблицу. Кто-то пытается вручную сложить необходимые суммы, складывая данные нужных ячеек. Иногда используют функцию фильтра. Все это занимает много времени и зачастую приводит к ошибкам.

Рассмотрим на примерах, как работает функция «СУММЕСЛИ». Допустим, надо посчитать, сколько компания потратила денег отдельно на картриджи, на бумагу, командировки и премии (см. рис. 2).

Рис. 2

Для этого с помощью функции «СУММЕСЛИ» сначала нужно посчитать, сколько компания потратила на картриджи. Потом эту формулу «протянуть» по таблице. То есть копировать ее в ячейки, в которых должны быть суммы для других статей расходов. При копировании формулы программа автоматически посчитает нужную сумму для бумаги, командировок и премий.

Сразу сделаю небольшое отступление. При копировании любой формулы в другую ячейку автоматически меняются адреса используемых в формуле ячеек. Иногда нужно адрес определенной ячейки в формуле зафиксировать. Это нужно для того, чтобы, например, использовать в расчетах одно конкретное значение. Это могут быть курсы валют, процентные ставки, ставки налогов, суммы налоговых вычетов в месяц и т. д. Фиксирование адреса ячейки в формуле делается с помощью знака доллара – «$». Чтобы зафиксировать ячейку в формуле, нужно либо выделить ее и нажать клавишу F4, либо в формуле вручную поставить знак доллара слева от каждого символа адреса ячейки. Например, нужно зафиксировать в какой-нибудь формуле ячейку D5. Для этого курсор ставится рядом с адресом этой ячейки в формуле и нажимается клавиша F4. Обозначение этой ячейки в формуле станет таким: «$D$5». Теперь при копировании формулы в другие ячейки значение ячейки D5 меняться не будет.

Такая фиксация необходима и для копировании функции «СУММЕСЛИ».

Итак, чтобы посчитать сумму расходов на картриджи за период, выделяется пустая ячейка В3 (см. рис. 2). Строка формул, в которой будут отображаться расчеты, расположена над строкой буквенных обозначений столбцов. В левой части этой строки есть значок «fx». Если на него нажать, то появится диалоговое окно под названием «Мастер функций».

В разделе «Категория» выбираем тип функций «Математические», так как «СУММЕСЛИ» относится к этому типу. В разделе «Выберите функцию» можно легко найти функцию «СУММЕСЛИ». Ведь все названия функций располагаются по алфавиту. После нажатия «ОК» на экране появится окно «Аргументы функций». Его можно свободно двигать по экрану, нажав на него левой кнопкой мыши. Сместите это окно в программе Excel таким образом, чтобы вам были доступны данные сводной таблицы учета расходов (см. рис. 1).

В окне «Аргументы функций» есть три строки: «Диапазон», «Критерий» и «Диапазон_суммирования».

Поле «Диапазон» – это первоначальные данные, из которых функция делает выборку. В нашем примере это будет столбец со всеми статьями расходов – С4:С12. Потому что нужно посчитать сумму именно картриджей. Этот диапазон С4:С12 будет одинаков и для бумаги, и для командировок, и для премий. Поэтому при копировании формулы в другие ячейки он должен оставаться неизменным. Значит, его нужно зафиксировать. Выделяем в строке «Диапазон» значение столбца С4:С12 и нажимаем клавишу F4. Получим «$С$4:$С$12». Теперь столбец зафиксирован.

Затем ставим курсор в поле «Критерий». Критерием для работы функции будет слово «картридж». Но ячейку с этим словом нужно выделить уже в таблице итоговых расходов по статьям (см. рис. 2). В поле «Критерий» появится адрес ячейки B3 из другого листа Книги Excel. Это в случае, если сводная таблица (рис. 1) и таблица итогов (рис. 2) располагаются на разных листах одной Книги Excel или даже в разных документах Excel.

Далее в поле «Диапазон_суммирования» ставим курсор и выделяем столбец со стоимостью всех расходов. Это столбец D4:D12. Аналогично столбцу С4:С12 его нужно выделить и зафиксировать, нажав клавишу F4. Ведь для всех остальных статей расходов диапазон сумм столбца D не меняется.

После нажатия клавиши «ОК» в итоговой таблице расходов появится сумма расходов компании на картриджи.

Чтобы быстро скопировать формулу в другие ячейки итоговой таблицы и найти сумму расходов на бумагу, премии и командировки, необходимо сделать следующее. Курсор мыши подводится к правому нижнему углу ячейки B3. После того как появится черный крестик «+», нужно нажать левую кнопку мыши и провести курсор по столбцу вниз. Программа автоматически скопирует формулу. И в столбце «Общая сумма» появятся искомые значения (см. рис. 3).

Рис. 3

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

Можно быстро посчитать в  Excel сумму только нужных данных

Теперь я расскажу про самый простой вариант использования функции «СУММЕСЛИ». Допустим, из всех расходов в сводной таблице, представленной на рис. 1, нужно найти сумму только тех чисел, которые не меньше 4000.

Выделяем пустую ячейку, в которой после расчетов будет значение суммы нужных чисел. Опять вызываем функцию «СУММЕСЛИ», выбрав ее в разделе математических функций. В поле «Диапазон» снова нужно поставить курсор и выделить столбец со всеми расходами – D4:D12. Ничего фиксировать не нужно. В поле «Критерий» с клавиатуры вручную вводится знак больше или равно «>=».

Делается это в английской раскладке, поэтому необходимо поменять язык. Знаков больше (Shift + Ю) или меньше (Shift + Б) в русской раскладке клавиатуры нет. И только после этого знака вводится пороговое значение критерия: 4000 (см. рис. 4).

Рис. 4

Заполнять поле «Диапазон_суммирования» не нужно.

Excel без проблем сложит все необходимые числа, которые не меньше 4000. В нашем случае получится результат 8620 (см. рис. 5).

Рис. 5

Если бухгалтер передумает и захочет найти сумму чисел, например, больше 1000, то заново формулу вносить не надо. Можно просто поменять в самой формуле значение 4000 на 1000. Для этого сначала нужно выделить ячейку с итоговой суммой – D13. В строке формул появится сама формула, где вручную можно легко изменить заданный ранее критерий. Потом нужно нажать клавишу «Enter». Программа автоматически все пересчитает.

Можно за минуты определить стаж сотрудников, их возраст и расходы на премии

Есть такая категория функций, как «Дата и время». Чтобы ее найти, нужно снова нажать на значок «fx» в левой части строки формул. Появится диалоговое окно «Мастер функций». В разделе «Категория» выбираем тип функций «Дата и время».

Самая простая функция – это «СЕГОДНЯ». Выбираете любую пустую ячейку, вызываете функцию «СЕГОДНЯ» в категории функций «Дата и время». Эта функция аргументов вообще не имеет, поэтому можно смело нажать «ОК». В выбранной ячейке появится текущая дата. Именно та, которая установлена в компьютере.

В категории «Дата и время» есть еще одна полезная и простая функция «ТДАТА». Она тоже не имеет аргументов. Выделяете пустую ячейку, выбираете функцию «ТДАТА» и нажимаете «ОК». В ячейке появятся текущая дата и время. Вручную вбивать эти данные в ячейку придется дольше.

Теперь самое интересное. Можно быстро посчитать в  Excel возраст и стаж сотрудника. Это очень полезно при расчете, например, больничных листов.

На рис. 6 представлена таблица, в левом верхнем углу которой стоит текущая дата. Ее можно быстро менять при помощи функции «СЕГОДНЯ» либо вручную задавать любую нужную дату.

Рис. 6

Хочу сразу отметить, что в Excel не всегда применяется одна функция. Часто одна функция «вложена» в другую. На будущее: для удобства набирайте внешнюю. А внешняя функция при расчете возраста сотрудника в нашем случае будет математическая функция «ЦЕЛОЕ». Она округляет число до наименьшего ближайшего целого числа. Дело в том, что возраст сотрудника не может быть 51, 61 год. Но при этом, если ему не исполнилось пока 52 лет, то ему 51 год. Даже если 52 года ему исполнится через пару дней. То же самое и со стажем при расчете больничных листов. Аргументом для функции «ЦЕЛОЕ» будет другая функция «ДОЛЯГОДА», которая находится в категории функций «Дата и время». Именно она считает разницу между начальной и конечной датами.

Чтобы одну функцию вставить в другую, нужно сделать следующее. Выделяем ячейку, в которой должен быть результат, – возраст сотрудника Антипова (см. рис. 6). Это ячейка F4. Вызываем функцию «ЦЕЛОЕ». Ставим курсор в поле «Число» в окне «Аргументы функции». Далее слева от строки функций нажимаем на стрелочку вниз, как показано на рис. 7. Excel выдает список недавно использовавшихся функций. Если там нет функции «ДОЛЯГОДА», то нажимаем на «Другие функции» в этом списке. Опять появится окно «Мастер функций». И вот теперь можно выбрать нужную функцию – «ДОЛЯГОДА». Нажимаем «ОК». Теперь появится окно «Аргументы функций», но уже функции «ДОЛЯГОДА». Ставим курсор в поле «Начальная дата» и в таблице выделяем ячейку с датой рождения Антипова – D4. Теперь ставим курсор в поле «Конечная дата» и выделяем текущую дату – ячейка В2. При этом значение В2 надо зафиксировать. Ведь при копировании формулы для расчета возраста других сотрудников ячейка с текущей датой в формуле меняться не должна. В поле «Базис» можно ничего не указывать. Нажимаем «ОК». Теперь копируем формулу для всех сотрудников, «протягивая» ее до конца столбца F. Программа автоматически посчитает возраст всех сотрудников.

Рис. 7

Точно так же считается стаж. Только в формуле вместо даты рождения сотрудников будет дата приема на работу.

Бухгалтеру так же зачастую нужно знать, будет в этом году у сотрудника юбилей или нет. Ведь в компаниях часто предусмотрены премии сотрудникам в связи с юбилеем. Что такое юбилей на языке Excel. Это значит, что возраст сотрудника должен быть кратным 5. То есть либо в этом году будет юбилей у сотрудника, либо нет. Поэтому в столбце «Юбилей» необходимо получить либо минус «–», либо плюс «+».

Для нахождения юбилея используются уже посчитанный возраст сотрудника и логическая функция «ЕСЛИ». Логика при составлении функции будет следующая. Если остаток от деления разности дат на 5 равен нулю, то у работника в этом году юбилей. Если не равен нулю – значит, юбилея нет.

Как это сделать. Выделяем ячейку, в которой хотим получить результат, – G4 (см. рис. 6). Нажимаем на значок «fx» строки формул. Далее в списке категорий выбираем «Логические». Находим функцию «ЕСЛИ» и нажимаем на нее.

Появилось окно «Аргументы функций». Во втором поле «Значение_если_истина» ставим знак «+». То есть логическое выражение будет правильное: остаток от деления возраста на 5 равен нулю. А в третьем поле «Значение_если_ложь» ставим знак «–».

Теперь наша задача– заполнить первое поле «Логическое выражение». Для этого необходимо поставить в него курсор. Так как с нулем сравнивается остаток от деления возраста на 5, то вызываем функцию «ОСТАТ». Она находится в категории «Математические». Чтобы ее вставить в поле, необходимо выбрать эту функцию из списка «Других функций». Они находятся слева от строки формул (см. рис. 7). Чтобы заполнить поле «Число», нажимаем на ячейку возраста нужного сотрудника. В поле «Делитель» ставим число 5.

В ячейке юбилея сотрудника Антипова программа поставит знак «–». Значит, у него в этом году юбилея нет и премия ему не положена.

Затем копируем формулу, «протягивая» ее по всей строке. Программа сама расставит все плюсы и минусы. Теперь все наглядно: у кого есть юбилей в текущем году, а у кого нет.

Лектор:

Савченко Ирина Валерьевна,

ведущий преподаватель ЗАО «Что Делать Информ».

Будьте в курсе!



Бухгалтерских новостей слишком много, а времени на их поиск слишком мало. Рекомендуем подписаться на новостную рассылку журнала «Главбух», чтобы следить за всеми изменениями в работе бухгалтеров.





Для того, чтобы оставить свой комментарий необходимо зайти под своим паролем или зарегистрироваться






  • Налоговый кодекс
  • Гражданский кодекс
  • Трудовой кодекс

Новые документы

Все изменения в законодательстве для бухгалтера


Совет недели

Если акт от поставщика датирован 2015 годом, но получили вы его только сейчас, расходы можно учесть в текущем периоде. Ведь из-за ошибки налог на прибыль в 2015 году переплатили (п. 1 ст. 54 НК РФ).

Директор заваливает вас дополнительной работой?

  Результаты

Система Главбух

Профессиональная справочная система для бухгалтеров

Получить демодоступ

Программа Главбух: Зарплата и кадры

Сервис по расчету и оформлению выплат работникам

Попробовать бесплатно

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


Пока вы были в отпуске

Самые важные события, материалы и изменения в законе


Подписка на рассылки



Наши партнеры

  • Семинар для бухгалтера
  • Практическое налоговое планирование
  • Зарплата
  • Учет в строительстве
  • Юрист компании
  • Кадровое дело
  • Учет.Налоги.Право
  • Документы и комментарии
  • Учет в сельском хозяйстве
  • Коммерческий директор
  • Упрощенка