Как на excel сделать числовой

Как на excel сделать числовой

Опубликовано 20.03.2018

Ежедневник в ExcelЕжедневник в Excel часть I.

Многие из нас ведут ежедневники.

Вещь очень полезная, но не всегда удобная.

У меня, например, ежедневник неизбежно превращался в записную книжку.

А планы, даже если я их там вел, регулярно перечеркивались. Более удобным для планирования я считаю различные электронные варианты. Один из них — Excel.

Конечно, если уж говорить об электронном планировщике, то почему бы не использовать предназначенный исключительно для этого MS Outlook?

Однако: во-первых, он удобен далеко не всем; во вторых, его требуется специально осваивать; в-третьих, его затруднительно перемещать с одного рабочего места на другой (если, к примеру, постоянное рабочее место отсутствует).

А таблица Excel настолько проста и понятна с точки зрения интерфейса, что можно сесть за компьютер, и тут же накидать план хоть на день, хоть на год.

Идея использовать Excel для ведения ежедневника принадлежит не мне. Впервые я с этим столкнулся на сайте известного тайм-менеджера Глеба Архангельского www.improvement.ru.

Подчеркну — это только идея. Прелесть Excel в том, что он гибок, и каждый может настроить ежедневник под себя.

Я же расскажу о своем варианте, и объясню, почему он именно такой. Первоначально планировщик выглядел точно так, каким его предложил Глеб архангельский. Однако, со временем, что-то было изменено под меня лично, а от чего-то я попросту отказался.

Логика ежедневника-планировщика

Логика планировщика в ExcelЛогика планировщика в Excel включает следующие принципы:

Основной принцип предложенного подхода — ежедневник должен входить в экран и иметь возможность быть распечатанным на одной странице (А4 или А5 — это на ваш выбор). Должны быть верно учтены зоны распределения внимания — первоначально мы смотрим в левую верхнюю зону, проходим слева на право, переключаемся в центр. Держим в поле зрения левую сторону и в самую последнюю очередь обращаем внимание на правую нижнюю зону. Человек одномоментно может держать в зоне своего внимания не более трех единиц информации. Планирование должно учитывать стадии — долгосрочное планирование, среднесрочное планирование, краткосрочное планирование. За максимальный период планирования принимаем год. На мой взгляд, это наиболее оправдано для регулярного тайм-менеджмента. Впрочем, если пожелаете, можете сверху посередине написать задачи из разряда жизненных целей. В этом случае планировщик будет иметь более целостный вид. Любой план может меняться. Это не должно нарушать структуру планировщика. Структура ежедневника в Excel

Таким образом получаем следующий вариант структуры ежедневника (нетерпеливые в конце второй части этой статьи могут скачать его образец):

Структура ежедневника Excel

Что мы здесь имеем:

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

В следующей статье я расскажу как дополнительно настроить ежедневник в Excel и как им пользоваться.

Пожелания пишите в комментариях. Если статья понравилась — прошу вас нажать на одну из кнопок ниже.

Опубликовано 20.03.201820.03.2018

Ежедневник в ExcelЕжедневник в Excel часть II.

Продолжим разбираться с ежедневником.

Первая часть статьи здесь.

Грех было бы не использовать возможности форматирования в Excel  для большей наглядности.

Вот что здесь можно сделать: Автоматическое отображение текущей даты. В Excel есть функция СЕГОДНЯ(), которая сама выводит текущую дату. Для наглядности, эту функцию можно улучшить, чтобы отображалась не просто числа, а целый текст и указывался день недели. Для этого в ячейку просто нужно внести формулу
=СЦЕПИТЬ(«Сегодня «;ТЕКСТ(СЕГОДНЯ();»дддд, «);ТЕКСТ(СЕГОДНЯ();»[$-FC19]Д ММММ ГГГГ \го\да\.»)). На основании сегодняшней даты, необходимо добиться, чтобы таблица автоматически сигнализировала о целях и задачах, срок которых уже приближается к критическим. Для долгосрочных целей, если указан срок, сигнал должен появиться за месяц. Для среднесрочных — за неделю. Также, если какая-либо запланированная встреча должна произойти сегодня, то она должна быть выделена аналогичным образом. Я долго не думал, а просто определил правило окрашивания выделяемых ячеек в красный цвет. Для этого в Excel версии 2007 года и выше имеется инструмент условного форматирования ячеек. Для начала нам необходимо в какой-нибудь свободной ячейке внести функцию СЕГОДНЯ. Это необходимо для того, чтобы сравнивать даты. Формула, которую мы внесли для отображения текущей даты в удобном для нас виде не годится. Внесем функцию в ячейку Е11, а затем установим белый цвет шрифта, чтобы запись нас не отвлекала. Теперь определяем правило форматирования для блока долгосрочных задач: Выделяем все ячейки блока Нажимаем на кнопку «Условное форматирование» и выбираем из выпадающего списка «Создать правило». В открывшемся окне выбираем «Использовать формулу для определения форматируемых ячеек». В поле для ввода формулы вносим: =И(($B2-$E)<=30;($B2-$E)>0). Как видим, мы установили два условия для окрашивания — срок должен быть меньше или равен 30 дням (месяц) и быть больше нуля (чтобы исключить окрашивание незаполненных ячеек). Нажимаем на кнопку «Формат», открываем закладку «Заливка» и выбираем красный цвет. Для блока среднесрочных задач все проделываем аналогично, но формула здесь будет чуть другая: =И(($E2-$E)<=7;($E2-$E)>0). Для блока встреч формула должна сравнивать текущую дату с числом, указанным для встречи. но, предварительно, мы должны округлить значение даты встречи, чтобы исключить из сравнения время. Ведь время, если перевести его в числовой формат, эквивалентно дробным значениям. В противном случае даже если дата сегодняшняя, сравнение укажет разные значения. Формула будет следующей: =ОКРУГЛВНИЗ($H2;0)=$E

Ежедневник готов!

Как им пользоваться

Каждое утро открывайте его, и определяйте план на сегодня. Вечером корректируйте текущие задачи в зависимости от их выполнения, планируйте встречи. Выполненные задачи складывайте в корзину и отмечайте дату ее выполнения и ценные идеи, возникшие в ходе ее выполнения.

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

В последний день месяца анализируйте, насколько вы приблизились к выполнению долгосрочных целей. Принимайте решение о коррекции, при необходимости. Редактируйте среднесрочные цели.

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

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

Мой вариант ежедневника можно скачать здесь.

Желаю вам успехов!

Пожелания пишите в комментариях. Если статья понравилась — прошу вас нажать на одну из кнопок ниже.

Опубликовано 20.03.201820.03.2018

Создание плана-графика ExcelСоздание плана-графика в Excel.

При планировании мероприятий, особенно, если работа происходит в группе, важно иметь наглядный план-график.

Конечно, существуют программы, типа MS Project или, бесплатный,  OpenProj. Но гибкость, которая присуща Excel при планировании проекта, не сравнится ни с какой специализированной программой.

Да и требуется ли приобретать и учиться работе в новой программе, ради обычного составления графика Ганта? На мой взгляд, если ты не являешься профессиональным менеджером проекта, то и погружаться в дебри специализированных программ тебе совершенно ни к чему. Лучше потратить время на развитие в более близких твоему профилю областях.

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

Создание структуры таблицы

Мы имеем перечень мероприятий, ответственных по каждому мероприятию и сроки.

Для начала продумаем структуру таблицы. Помимо временной шкалы, она будет состоять из следующих колонок:

Порядковый номер мероприятия; Наименование мероприятия; Ответственный исполнитель; Дата начала мероприятия; Здесь можно сделать дату окончания, но мне кажется компактнее и нагляднее использовать значение продолжительности мероприятия в днях. (берем календарные дни. Для более продвинутых, можно запрограммировать условие расчета по рабочим дням, но это отдельная тема.) Примечания — как бы полно мы ни называли задачу, все равно будут возникать нюансы, которые целесообразно писать в примечаниях.

Над таблицей предусмотрим две строки:

Наименование проекта; Строка для отображения текущей даты. Построение шкалы времени

Далее выстраиваем шкалу времени. У меня нет строгих требований к ее длине. Я, скорее, ориентируюсь на удобство восприятия. Обычно выделяю 30 колонок, и сужаю их размер, чтобы ячейки стали квадратными.

Над таблицей выделяем место для определения шага временной шкалы. Это понадобиться нам для того, чтобы гибко подходить к размерам страницы. Допустим, если проект будет общей продолжительностью три месяца, то сделав шаг в один день, мы слишком растянем временную шкалу, и нам потребуется не 30, а более 90 ячеек по горизонтали. Это сделает таблицу трудночитаемой. В подобных случаях можно сделать шаг, допустим, в три дня, или в одну неделю, и шкала окажется вполне компактной.

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

Изменяемой будет только самое левое значение. Сюда мы вносим дату начала проекта. А в ячейку справа от нее вносим формулу =G3+$O, где второе слагаемое, это адрес ячейки с шагом временной шкалы. Далее просто протягиваем эту формулу — то есть выделяем ячейку с формулой, наводим курсор мыши на ее нижний правый угол (появиться черный крестик), зажимаем левую кнопку мыши, и ведем мышкой вправо до конца строки. Таким образом, формула будет скопирована автоматически.

Проверяем — внесем в ячейку с шагом временной шкалы число 2. Смотрим на шкалу — все даты должны отображаться через день — если в одной ячейке 1 января, то в следующей ячейке должно быть 3 января.

Настройка условного форматирования

Теперь самое интересное и полезное для плана-графика проекта — настройка условного форматирования. Нам нужно, чтобы на временной шкале по каждому мероприятию цветом отображался интервал с даты начала этого мероприятия до его окончания. Кроме того, на мой взгляд, полезным будет настроить что-то вроде вертикальной полоски, которая будет отображаться в колонке с текущей (сегодняшней) датой.

Самое сложное в этом — продумать формулу, которая будет являться условием окрашивания ячеек. Логически, все — просто. Должно быть окрашено то, что позднее или равно дате начала, но раньше или равно дате окончания. В Excel для этого используется функция «И», которая проверяет соответствие нескольким условиям.

условное форматированиеВыделяем все ячейки временной шкалы. Находим в меню Excel «Условное форматирование» и выбираем пункт «Создать правило«. Выбираем в открывшемся окне пункт «Использовать формулу для определения форматируемых ячеек«. В строку для формул вносим: =И(G>=$D4;G<=($D4+$E4)). Цвет заливки выберем, например, зеленый.формула шкалы

Создадим второе условие, для полоски, отмечающей сегодняшнюю дату. Если бы во временной шкале у нас был шаг в один день, то мы бы просто внесли равенство сегодняшней даты с датой по временной шкале. Но текущий день во временной шкале может отсутствовать, если шаг установлен в несколько дней. Поэтому нам снова нужно внести двойное условие — текущая дата должна быть больше или равно соответствующей даты в шкале времени, и быть меньше (без равно) следующей по шкале времени даты.

Формула выглядит так: =И($C>=G;$C<H). На этот раз цвет выбираем красный.

Итак, проверяем работоспособность таблицы. Заносим все данные, и смотрим, что у нас получилось. Как видим — все работает, а полученный шаблон можно использовать для любых других проектов.

Делитесь материалом со своими друзьями, нажав на кнопки ниже. Наверняка кому-то он будет полезен.

Усовершенствование плана-графика Связанные задачи

Вопрос от читателя по имени Олег (см. комментарии ниже) натолкнул меня на усовершенствование файла. Действительно, почему бы не предусмотреть возможность связывания задач, когда дата начала последующей задачи будет начинаться на следующий день после предыдущей? Кроме того, было бы неплохо предусмотреть сигнализирование в случаях, когда происходит задержка выполнения той или иной задачи.

Что было сделано:

В прежней версии плана-графика дата старта проекта указывалась вначале колонки с датами. Удобнее было бы, если бы дата старта проекта указывалась в заголовке. Так и сделаем — добавим строку в заголовке, создадим там поле «Дата старта проекта«, а от той ячейки, куда мы раньше заносили дату, сделаем ссылку на это поле.

Как видно из рисунка, после колонки «Ответственный» добавлена колонка «№ предшествующей задачи«. Суть ее понятна — в нее будет вносится номер той задачи, к которой должна быть привязана текущая задача (после которой должно начинаться ее выполнение). А в ячейку первой строки колонки с датой начала занесем следующую формулу:

=ЕСЛИ(ЕПУСТО(D5);$C;ВПР(D5;$A:$F;5;ЛОЖЬ)+ВПР(D5;$A:$F;6;ЛОЖЬ)+1)

Протянем ее до нижней ячейки колонки. Суть формулы в том, что при отсутствии ссылки на предшествующую задачу будет отображена дата старта проекта. В случае, если номер предшествующей задачи будет указан, то будет указана дата этой задачи плюс значение ее продолжительности плюс 1 (на следующий день). Все, привязка выполнена. Теперь, если начнется аналогичный проект, достаточно будет сменить дату старта проекта, а все прочие даты выполнения задач сдвинутся автоматически.

Следующая задача — настроить индикацию задержек. Видов задержек может быть два:

задача выполнена, но с запозданием; задача до сих пор не выполнена.

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

Предусмотрим для разных случаев различные сообщения:

Задержек нет — цвет заливки зеленый; Задержка на … дней — надпись жирным красным шрифтом; Срок выполнения задачи вышел — цвет заливки красный.

Для отображения соответствующих записей, внесем в верхнюю ячейку колонки «Сообщение о ходе выполнения» следующую формулу:

=ЕСЛИ(ЕПУСТО(G5);ЕСЛИ($C>(E5+F5);»срок выполнения задачи вышел»;»»);ЕСЛИ(F5<РАЗНДАТ(E5;G5;»d»);»задержка на «&РАЗНДАТ(E5;G5;»d»)-F5&» дней»;»задержек нет»))

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

таблица ексель

Скачать файл можно по ссылке.

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

Уточнение срока

И еще одно дополнение. В графике отсчет всех сроков начинается со следующего дня. Например, если задача стартовала 11-го января, а закончилась 12-го, то таблица укажет, что продолжительность задачи 1 день, и все расчеты будет осуществлять исходя из этого принципа. Однако, по логике, 11-е число тоже должно считаться, и, в этом случае, следовало бы считать два дня. Возможно, это не принципиально, но для полноты картины выкладываю файл, который первый день срока выполнения задачи тоже учитывает при расчете ее продолжительности.

График с почасовым учетом

Благодаря моим уважаемым читателям, план-график обрастает все новыми возможностями. Так, по просьбе Александра, решил доработать файл, чтобы он вел учет не по дням, а по часам. Стало чуть менее удобно вносить данные о дате и времени, но, зато, теперь все считается в почасовом формате. Принимайте новый файл.

Учет выходных дней

И еще одно улучшение — читательница Вера предложила считать продолжительность задач в рабочих днях. Что и было сделано в этом файле.

Опубликовано 20.03.2018

Оформление командировки ExcelОформление командировки в Excel.

В один, не очень прекрасный день, в бытность мою HR-ом, после очередного конфликта между представителями трех разных подразделений по поводу отсутствия необходимых документов по командировке, я задумался о том, как облегчить процесс оформления командировки.

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

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

Порядок действий по процессу «Оформление командировки»

Я начал с изучения порядка выполнения действий по оформлению командировки. Вот что, примерно, у меня получилось:

Руководитель должен обозначить цель командировки, продолжительность, определить командируемого. Все это перечисляется в служебной записке, которая, затем, согласовывается с директором. Согласованная с директором служебная записка передается в отдел кадров. Кадровик проверяет, уточняет и переписывает все данные в служебное задание (форма Т-10А). Затем, те же данные заносит в командировочное удостоверение (форма Т-10).
Актуальная поправка — с 2015 года правительство отменило командировочные удостоверения. Теперь, для отчетности, достаточно будет предъявить проездные документы. Если сотрудник использует личный автотранспорт, то для отчетности принимается служебная записка, подкрепленная документами о расходах (квитанция, или кассовый чек). Далее, кадровик оформляет приказ на командировку (форма Т-9). Большинство данных в приказе снова дублируются. Все документы распечатываются (если заполнялись на компьютере) и подписываются директором. На основании документов и внутренних положений бухгалтер производит расчеты и выплачивает денежные средства на командировку. Секретарь в командировочном удостоверении делает отметки об убытии и прибытии сотрудника, и регистрирует приказ на командировку. После приезда, сотрудник отчитывается по командировке и заполняет авансовый отчет. Документы согласуются с руководителем, и передаются в бухгалтерию. Бухгалтер производит окончательные расчеты.

Сразу же бросается в глаза совершенно тупое переписывание кадровиком трех идентичных документов — служебного задания, командировочного удостоверения и приказа на командировку. Хорошо, что в моей фирме это делал один человек, а если это делают три разных подразделения без ведома, как это обычно бывает, друг друга?

И нужна ли служебная записка, которую руководители нередко затруднялись составлять, если можно сразу же составить служебное задание? Ведь вся информация, которая должна быть в служебном задании, начальнику должна быть уже известна (ну, если этот начальник хоть как-то соответствует своей должности).

Создание новых форм

Я предложил, чтобы руководитель сразу оформлял служебное задание в Excel. Ну, а теперь нужно сделать так, чтобы во всех других документах занесенная ранее информация отобразилась автоматически. Это сделать проще простого, создав необходимые формы (T-9, Т-10 и Т-10а) в том же файле на разных страницах, а в необходимых местах создав ссылки на ячейки с требуемой информацией.

К слову говоря, все необходимые формы документов в формате Excel есть в интернете, и их можно скачать. Ячейка с примечанием

Ячейка с примечанием

Примечание, появляющееся при наведении курсора

Примечание, появляющееся при наведении курсора

Чтобы заполняющему служебное задание было легче сориентироваться в форме, там, где необходимо внести какие-либо данные, вставлено примечание, отображающееся при наведении курсора (в правом верхнем углу ячейки с примечанием виден красный маленький треугольник). Вставить примечание можно с помощью закладки меню «Рецензирование«. Там есть кнопка «Создать примечание».

Для того, чтобы сделать ссылку, необходимо внести знак «=«, а потом открыть нужную страницу, и кликнуть мышкой по той ячейке, ссылка на которую нужна. Можно, конечно же, вручную внести адрес ячейки, например «=А23».

Правда, если в ячейке, на которую ведет ссылка, отсутствуют значения, то отобразится «0». Нам это не нужно, поэтому с помощью функций «ЕСЛИ» и «ЕПУСТО» зададим условие, которое, если отсутствует запись (ячейка пустая), тоже будет оставлять ячейку пустой. Формула выглядит так: =ЕСЛИ(ЕПУСТО(А23);»»;А23)

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

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

После того, как все необходимые поля освобождены от защиты, там же, в меню «Рецензирование» находим кнопку «Защитить лист«, нажимаем ее и, при необходимости, вносим пароль для отмены защиты листа.

Все, форма готова. Теперь процесс будет намного проще, а именно:

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

Время на оформление документов сокращается минимум раза в два!

Форма, о которой здесь говориться, находится по ссылке. Все листы в этом файле защищены от изменений, кроме ячеек, где эти изменения должны осуществляться. Пароля нет, поэтому вы можете легко снять защиту через меню «Рецензирование«-«Снять защиту листа«.

Опубликовано 20.03.2018

Расчет кредитных выплат в ExcelРасчет кредитных выплат с помощью Excel.

Многие из нас брали кредит в банке.

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

Впрочем, поэтому я за них и не берусь.

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

Excel позволяет быстро произвести правильные расчеты не ломая голову над формулами. Поскольку здесь уже имеются встроенные функции, позволяющие рассчитать:

сумму ежемесячных выплат (аннуитетный платеж); часть из этой суммы, которая идет на погашение основного долга; оставшуюся часть, которая идет на погашение процентов.

Кроме того, с помощью Excel можно поиграть значениями, к примеру, — какая будет сумма ежемесячных выплат при разном годовом проценте или при разных сроках кредита.

Построение таблицы

Построить такую таблицу совсем несложно. использовать мы будем три функции:

ПЛТ — рассчитывает сумму регулярного платежа при той или иной процентной ставке; ОСПЛТ — рассчитывает часть суммы регулярного платежа, которая уходит на погашение основного долга; ПРПЛТ — рассчитывает часть суммы регулярного платежа, которая уходит на погашение процентов.

Для начала, внесем основные данные, а именно — сумму кредита, срок кредита в годах, и процентную ставку. Например, это будет 200 тыс. руб, на 2 года под 18%.

Составим колонки таблицы расчета: Месяц — порядковый номер месяца очередной выплаты; Аннуитетный платеж — общая сумма ежемесячных выплат; В т.ч. основная сумма — часть общей суммы, уходящей на погашение основного долга; В т.ч. проценты — оставшаяся часть суммы, уходящей на погашение процентов; Остаток долга на конец месяца.

За два года пройдет 24 месяца, поэтому в колонке месяц проставляем порядковые номера от 1 до 24. Для облегчения процедуры можно воспользоваться автонумерацией. В первую ячейку проставляем 1. Подводим курсор мыши к правому нижнему углу выделенной ячейки (там должен быть виден маленький черный квадратик), зажимаем левую клавишу мыши и, одновременно, клавишу Ctrl на клавиатуре, и протягиваем курсор вниз до тех пор, пока справа от курсора мыши не появиться значение «24».

Внесение формул

В верхнюю ячейку колонки «Аннуитетный платеж» вносим такую формулу: =ПЛТ($B/12;$B2;-$B). Знак $ перед адресом колонки и номером строки обозначает, что при протягивании этой формулы на другие ячейки, этот адрес не будет изменяться.

Первый аргумент функции ПЛТ обозначает процентную ставку. Мы делаем ссылку на B4. Но, поскольку ставка указывается годовая, а нам необходимо вычислить ставку за месяц, мы это значение делим на 12. Второй аргумент должен указывать период, на который взят кредит. В нашем случае это 5 лет, однако, поскольку мы уже заложили ежемесячный расчет, то и период необходимо указать в количестве месяцев. Поэтому ссылку на B3 мы умножаем на 12. Третий аргумент — сумма кредита. Ссылаемся на B2. Однако, если мы просто сошлемся на сумму кредита, то будет выпадать отрицательное значение. И это логично, ведь каждая выплата, это — убыток. Поэтому, если мы не хотим видеть отрицательные значения, то перед ссылкой поставим минус.

В верхнюю строку колонки «в т.ч. основная сумма» вносим формулу: =ОСПЛТ($B/12;A7;$B2;-$B). Здесь все аналогично, кроме второго аргумента, который указывает на порядковый номер периода (в нашем случае, это порядковый номер месяца).

В верхнюю строку колонки «в т.ч. проценты» вносим формулу: =ПРПЛТ($B/12;A7;$B2;-$B). Здесь все аргументы соответствуют предыдущей формуле.

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

Обратите внимание, если бы мы не проставили знак $ перед адресами колонок и строк, то этот адрес тоже бы смещался со строки на строку. Этим свойством мы воспользуемся при расчете колонки «Остаток долга на конец месяца».

При расчете долга на конец месяца, мы должны учитывать и выплату на погашение процента. Поэтому это не просто остаток долга минус текущий аннуитетный платеж, а остаток долга плюс сумма на погашение процента минус текущий аннуитетный платеж (уж простите меня за сложность). Поэтому формула, которую мы внесем, будет такой: =B2+D7-B7.

B2 — это ссылка на сумму кредита (остаток на прошлый месяц); D7 — ссылка на сумму погашения процента, набежавшего к концу первого месяца; B7 — ссылка на сумму первой выплаты по кредиту.

Протянуть на все ячейки ниже для автозаполнения эту формулу мы не можем, т.к. адрес с остатком долга поменялся. Теперь это будет не В2, а значение в ячейке выше. Поэтому формула теперь будет следующей: =E7+D8-B8.

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

Все, таблица заполнена, и мы видим, сколько нам требуется выплачивать ежемесячно, и сколько потребуется для досрочного погашения долга.

Анализ «что-если»

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

Для начала, подготовим таблицу для анализа. Нам нужно будет сравнивать суммы ежемесячных выплат по двум критериям — процентная ставка, и срок кредита. Внесем в наименования строк нашей таблицы сравниваемые процентные ставки, а в наименования колонок — различные периоды в годах.

На пересечении наименований строк и колонок необходимо сделать ссылку на зависимую ячейку. В нашем случае это любая ячейка из колонки «Аннуитетный платеж». Поставим ссылку на В7 (появиться значение с этой ячейки — 9986). Теперь выделяем эту нашу таблицу, и нажимаем на кнопку меню «Анализ «что-если«. В выпавшем меню выбираем строку «Таблица данных«.

Появилось окошечко, для определения критериев. Значения по столбцам у нас указывают на срок кредита в годах, поэтому в верхней строке окошка делаем ссылку на ячейку В3. Значения по строкам — это процентная ставка. Значит ссылаемся на В4. Нажимаем ОК.

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

Опубликовано 20.03.201820.03.2018

Учет рабочего времени в ExcelУчет рабочего времени в Excel. часть I.

Кадровики организаций обязаны вести табели учета рабочего времени.

При этом, сам учет рабочего времени своих сотрудников обязаны вести руководители.

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

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

Подготовка структуры таблицы

Табель учета рабочего времени Т-12

Для начала, скачаем с интернета форму табеля рабочего времени в Excel. Кому-то может понравится форма табеля Т-13, но я использую форму Т-12. Можно использовать любую — жестких требований здесь нет.  Найти ее несложно, например, вот здесь. Вообще, кадровикам я рекомендую использовать справочные системы типа «Гарант» или «Консультант» в своей работе. Все, что необходимо там имеется.

В файле, который мы скачали, несколько листов. На первом указаны коды рабочего времени. Назовем этот лист «Коды«. На втором — собственно, табель. Так и назовем его — «Табель«. Третий лист посвящен оплате труда. Назовем его «Расчет» (он нам пригодится на следующих уроках). Четвертый лист назовем «Итоги«. Здесь будут подсчитываться итоговые значения по отработанному времени.

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

Создание таблицы для ввода данных

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

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

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

При желании, можно предусмотреть различные варианты ввода данных. Например, руководитель может выбирать ФИО из выпадающего списка, и заносить время начала и окончания работы этого сотрудника. А при выборе другого сотрудника, будут отображаться только поля для ввода данных о времени только этого сотрудника. Но такую таблицу будет сложнее создать, а задача этой статьи прописать алгоритм, который будет понятен большинству.

Набросок таблицы ввода данных об отработанном времени

Поэтому ограничимся относительно простым вариантом ввода данных в таблицу. Предусмотрим поле для ввода наименования подразделения. Пусть, в нашем случае, это будет «Магазин №1«.  Ниже предусмотрим место для названия месяца. Далее — начинаем создавать таблицу. В ней будут следующие поля:

Порядковый номер; ФИО сотрудника; Наименование должности сотрудника; Табельный номер сотрудника; Далее будут располагаться данные о времени. Предусмотрим время начала рабочего дня, время окончания, продолжительность обеденного перерыва и колонку для внесения кода рабочего времени.

Таблица ввода данных об отработанном времени

Последние четыре колонки относятся к первому числу месяца. Нам необходимо будет повторить их 31 раз (по максимальному количеству дней в месяце). Однако, вначале, выше наименования колонок нашей таблицы поставим цифру 1. Теперь выделим четыре колонки, которые необходимо продублировать 31 раз, подведем курсор мыши к черному квадратику справа вверху и протянем диапазон до того момента, пока блоков из четырех колонок не будет 31. Должно получится как на картинке внизу.

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

Таблица для ввода данных создана. В следующей статье я опишу, как  обеспечить ее защиту от ошибочного ввода данных.

Опубликовано 20.03.2018

Учет рабочего времени в ExcelУчет рабочего времени в Excel. часть II.

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

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

Иначе постоянно придется отвечать на вопросы и принимать нарекания о том, что таблица не работает (хотя не работает исключительно из-за неправильного ее заполнения).

О возможностях Excel в этом вопросе я писал в соответствующей статье.

Здесь же мы поговорим, как эти возможности применить к табелю учета рабочего времени.

Вот что нам необходимо обеспечить: в поле «Месяц» должен допускаться только ввод одного из 12 наименований месяца; во всех колонках «Начало» и «Окончание» должен до пускаться только ввод в формате времени от 0 часов до 24 часов; Время окончания не должно быть раньше времени начала; во всех колонках «Обед» должен допускаться только ввод в формате времени от 0 часов до 1 часа; во всех колонках «Код» допускается ввод только одного из кодов рабочего времени. Защита от ошибочного введения месяца

Для безошибочного ввода названия месяца необходимо создать список. Нам придется создавать еще списки, поэтому предлагаю для них создать отдельный лист, который мы так и назовем — «Списки«. В дальнейшем мы его попросту скроем.

На этом листе перечислим все месяцы. Выделяем список и задаем ему имя — «месяц«. О том, как присваивать имя диапазону и работать со списками, я писал здесь.

Возвращаемся на лист «Учет«, и подводим курсор мыши к ячейке, в которой хотим использовать список. Через меню «Проверка данных» задаем тип данных «Список» и источник данных — диапазон с именем «Месяц«.

Ограничение на ввод данных о времени

Выделим ячейки колонок «Начало» и «Окончание«, относящихся к 1-му числу месяца. Также, через «Проверку данных» задаем тип данных «Время«, и выбираем значения между 0:00 и 23:59. В закладке «Сообщение об ошибке» оставляем вид «Останов«, а в заголовке пишем «Неверный формат данных«. В тексте сообщения пишем примерно следующее — «Введите правильное время от 0:00 до 23:59«.

Похожим образом защитим ячейки в колонке «Обед«, только диапазон значений выбираем между 0:00 и 1:00. В тексте сообщения об ошибке я написал — «Введите правильную продолжительность от 0:00 до 1:00«.

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

Для этого выделяем ячейку в колонке «Начало«, и в меню «Условное форматирование» выбираем пункт «Создать правило«. Затем выбираем пункт «Использовать формулу для определения форматированных ячеек«. Устанавливаем курсор в строке формулы и вписываем условие =E6>F6.

Обратите внимание — если вы не вписываете адреса ячеек с клавиатуры, а указывает на эти ячейки мышкой, то Excel автоматически сделает эти адреса абсолютными (со знаками «$»). Необходимо сделать эти адреса относительными (удалить все знаки «$»). В противном случае при копировании этого условия на другие ячейки, ссылки не будут перемещаться.

Затем устанавливаем курсор в ячейку «Окончание«, и создаем второе правило с аналогичной формулой. Выделим обе ячейки, и протянем условие на всю колонку. Проверяем — работает ли условие.

Защита от ошибочного ввода кода

Для защиты полей в колонке «Код» нужно создать список этих кодов. Хоть он уже имеется на странице «Коды«, но располагается в двух колонках и не в каждой строчке. Поэтому копируем обе эти колонки на страницу список в одну колонку и в виде значений. Но буквы будут расположены не в каждой строчке. Для удаления пустых ячеек выделяем всю колонку, и в меню «Найти и выделить» выбираем пункт «Выделение группы ячеек«.

В появившемся окошке ставим галочку в пункте «Пустые ячейки«, и нажимаем «ОК«. Теперь подводим курсор мыши к любой выделенной ячейке, и нажимаем правую кнопку мыши. В открывшемся контекстном меню выбираем пункт «Удалить«, а затем ставим галочку на пункте «Ячейки со сдвигом вверх«. После того, как мы нажмем «ОК«, список приобретет требуемый вид. Выделяем его, и задаем имя «Код«.

Возвращаемся к листу «Учет«. Выделяем колонку «Код» и через меню «Проверка данных» задаем тип данных «Список» и источник данных — диапазон с именем «Код«. Галочку с пункта «Список допустимых значений» лучше всего отжать. Кнопка раскрытия списка только будет мешать, а значения со слишком большого списка лучше вносить вручную, а не выбирать. В сообщении об ошибке я написал «Вы ввели неверный код. Ознакомьтесь со списком и их значениями на страницы «Коды».

Завершение защиты листа

Теперь все ячейки, относящиеся к первому числу месяца, защищены. Для защиты остальных ячеек воспользуемся протягиванием блока. Выделяем этот блок (все ячейки, относящиеся к первому числу), подводим курсор мыли к маленькому черному квадратику в правом нижнем углу выделенного диапазона, и протягиваем до конца.

Для пущей надежности, желательно защитить лист от изменений. Выделим все ячейки, изменения в которых допускаются. Они будут следующими:

Поле с наименованием месяца; Колонка «ФИО сотрудника»; Колонка «Должность»; Колонка «Табельный номер»; Все колонки с данными о времени.

Подведя курсор мыши к любой из выделенных ячеек, нажимаем правую кнопку и выбираем в контекстном меню пункт «Формат ячеек«. Заходим на вкладку «Защита» и отжимаем галочку в пункте «Защищаемая ячейка«.

Теперь, на вкладке меню «Рецензирование» лист можно защитить. Подробнее об этом в статье «Защита от ошибочного ввода данных«. Пароль на снятие защите в своем файле я не задаю, но при использовании таблицы другими сотрудниками настоятельно рекомендую это сделать.

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

Итак, таблица ввода данных готова, и мы можем перейти к настройке табеля, чтобы он автоматически считал все данные об отработанном времени. Об этом в следующей статье.

Опубликовано 20.03.2018

Учет рабочего времени в ExcelУчет рабочего времени в Excel. часть III.

В прошлых материалах (часть I и часть II) мы с вами скачали официальную форму табеля учета рабочего времени, и создали таблицу для ввода данных об отработанном времени сотрудников.

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

Автозаполнение данных о сотрудниках

Для начала, обеспечим дублирование данных по сотрудникам. Зачем нам вводить по нескольку раз имена и должности. Попросту создадим прямые ссылки на те ячейки, где данные уже внесены. Для этого устанавливаем табличный курсор на листе «Табель» в ячейку, которая должна ссылаться на имя сотрудника в листе «Учет«, пишем в ней знак «=«, открываем лист, на который ссылаемся и щелкаем мышкой там, где находятся данные.

Однако, обратим внимание, нам необходимо указать не только имя сотрудника, но и его должность. В этом случае можно просто объединит данные с двух ячеек через оператор «&«. После того, как мы указали ссылку на данные с ФИО, щелкаем мышкой в строке формулы, чтобы здесь появился мигающий курсор, и вносим необходимые операторы. Затем ссылаемся на вторую ячейку. В конечном итоге должна получиться вот такая формула: =Учет!B6&» «&Учет!C6

Как видите, мы не просто объединили данные, а еще добавили кавычки с запятой и пробелом между ними. Если бы мы этого не сделали, то данные бы объединились без пробела и запятой.

К сожалению. нам не удастся заполнить остальные ячейки табеля с помощью автопротягивания. Поскольку в исходной таблице данные внесены в каждой строке, а в табеле через строку (ячейки двух строк объединены), то Excel формирует новые ссылки через строку. Поэтому придется каждую формулу вносить заново.

Должно получиться следующее (см. скриншот)

Расчет продолжительности работ и автозаполнение кодов

Теперь необходимо вычислить ежедневную продолжительность работы. Формула здесь проста — из времени окончания вычитаем время начала и из полученной разницы вычитаем продолжительность обеденного перерыва. Однако такая формула не сработает при наличии ночных смен. В этом случае необходимо задавать условия, но здесь мы этого делать не будем, чтобы не усложнять.

Внесем формулу в ячейку, соответствующую времени, отработанного Ивановым первого числа месяца: =Учет!F6-Учет!E6-Учет!G6

Теперь мы можем протянуть эту формулу до ячейки, соответствующей пятнадцатому числу. Все ссылки переместятся правильно, поскольку количество колонок, объединяющих одну ячейку в табеле совпадает с количеством колонок, соответствующих одному дню на листе «Учет«. Приятное совпадение.

Аналогичным образом вносим формулу в ячейку, соответствующую шестнадцатому числу (=Учет!BN6-Учет!BM6-Учет!BO6), и протягиваем ее до 31-го числа. Во всех этих ячейках необходимо установить формат времени с указанием часов и минут.

Теперь, если мы начнем проверять расчеты, то обнаружим неприятный момент — значения не вмещаются в границы ячейки, поэтому отображаются одни решетки. Выйти из положения можно несколькими путями:

уменьшить размер шрифта до 5 пунктов; увеличив ширину ячеек; задать формат с указанием только часов (при выборе формата ячейки, в поле «Тип» вносим одну букву «ч» см. картинку).

Я решил уменьшить размер шрифта.

Заполняем все ячейки с указанием отработанных часов табеля.

С указанием кода рабочего времени проблем быть не должно — просто ссылаемся на нужную ячейку. Однако, нам не нужны нули в случаях, когда ячейка с кодам на листе «Учет» пуста. Поэтому закладываем следующее условие: =ЕСЛИ(ЕПУСТО(Учет!H6);»»;Учет!H6).

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

Опубликовано 20.03.201820.03.2018

Учет рабочего времени в ExcelУчет рабочего времени в Excel. часть IV.

В прошлой статье мы настроили автозаполнение данных о сотрудниках, и данных об отработанном времени в табеле.

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

Все формулы, которые мы будем использовать, рассматривались в статье «Суммирование по условию«.

Подсчет отработанных дней и часов

В полях «Итого отработано…» просто заносим формулу суммирования значений отработанного времени. Обратите внимание, что формат времени не всегда считает исходя из общего количества отработанных часов. Часто, по умолчанию, устанавливается формат, исходящий из 24 часов. Для исправления ошибки выделите все ячейки с суммой часов, и замените формат на 37:30:55. Он считает не теряя часы за пределами 24.

А вот для подсчета количества отработанных дней нужно будет использовать функцию подсчета количества ячеек по условию (подробнее о ней здесь). Пишем: =СЧЕТЕСЛИ(AG9:CN9;»>0″)+СЧЕТЕСЛИ(CV9:FG9;»>0″). Как вы понимаете, поскольку диапазоны у нас разорваны, то мы складываем два диапазона по отдельности.

Копируем формулу во все остальные ячейки. При копировании ссылки будут смещаться правильно. Если отобразится временной формат, то, попросту, замените его на числовой (с помощью контекстного меню «Формат ячеек«), без указания десятичных дробей.

Подсчет отработанных часов за месяц обеспечиваем простейшей формулой складывания промежуточных формул, которые мы писали ранее в полях «Итого отработано…«.

Подсчет прочих данных об отработанном времени

Подсчет количества сверхурочных часов давайте оставим для ручного заполнения, поскольку каждая переработка должна подтверждаться не просто отметкой в табеле, но и другими документами. Можно, конечно, и здесь прописать сложный алгоритм, но это потребует иного подхода к формированию всей таблицы.

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

С подсчетом количества часов, отработанных в праздничные дни, все — просто. Используем функцию «СУММЕСЛИ«: =СУММЕСЛИ(AG10:CN10;»=РВ»;AG9:CN9)+СУММЕСЛИ(CV10:FG10;»=РВ»;CV9:FG9)

Смотрим — что получилось. Укажем в один из дней код «РВ«. Получилась десятичная дробь. Проблема в том, что так настроился формат ячеек. Просто выделяем все ячейки, формат которых должен быть временной, и устанавливаем, собственно, тот, что указывает часы и минуты. Копируем формулу на остальные ячейки.

Количество неявок предлагаю указывать в днях, поскольку логика этого табеля предполагает именно такой подход. Неявка на работу отмечается кодами «ПР» и «НН«. Используем функцию «СЧЕТЕСЛИ«.  Поскольку у нас два условия, то мы их складываем. А, поскольку, два диапазона, до складываем еще два условия и по второму диапазону. Таким образом получается формула: =СЧЁТЕСЛИ(AG10:CN10;»=НН»)+СЧЁТЕСЛИ(AG10:CN10;»=ПР»)+СЧЁТЕСЛИ(CV10:FG10;»=НН»)+СЧЁТЕСЛИ(CV10:FG10;»=ПР»)

Копируем эту формулу на другие ячейки.

В колонке «Из них по причинам…» предусмотрено по две ячейки. Заносим в каждую из них коды «ПР» и «НН«, соответственно. А в следующих ячейках, также, с помощью функции «СЧЕТЕСЛИ» подсчитываем количество ячеек, отмеченных этими кодами. Копируем формулы на все прочие ячейки.

Аналогично заносим формулу подсчета количества ячеек с кодами «В» в колонке «Количество выходных и праздничных дней»: =СЧЁТЕСЛИ(AG10:CN10;»=В»)+СЧЁТЕСЛИ(CV10:FG10;»=В»). Должно получиться как на картинке.

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

Опубликовано 20.03.201820.03.2018

Заполнение формы Т-12 ExcelЗаполнение формы Т-12 в Excel. Часть I.

В серии статей «Учет рабочего времени в Excel» мы настраивали автозаполнение табеля учета рабочего времени в установленной отчетной форме Т-12.

Правда 3-я и 4-я страницы этой формы остались без внимания. А ведь это страницы расчета с персоналом по оплате труда и статистических итогов по отработанному времени сотрудников. Когда таблица создавалась, мы дали этим страницам имена «Расчеты» и «Итоги«.

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

Внесение первичных данных

Начнем с самого простого — сделаем ссылки на ячейки, которые уже должны быть заполнены на предыдущих страницах. Это колонка «Табельный номер» и графа 22 — «Часы (дни)«. Табельный номер мы заносили на странице «Учет«. Ставим знак «=» и кликаем мышкой на соответствующей ячейке. И так по всему списку.

Расчет отработанных в течение месяца часов осуществляется на странице «Табель«. Это ячейки в колонке «Всего«, являющейся подразделом колонки «Итого отработано за месяц«. Также, как и табельный номер, вносим ссылки на эту колонку.

Далее, в графе 19, пишем установленный для каждого сотрудника оклад (о более сложных схемах оплаты труда мы будем говорить в другой статье). Графа 20 для бухгалтеров. В нее заносится счет БУ, на который относятся расходы по оплате труда.

Собственно, расчеты, начинаются с графы 21. Это окладная сумма, которая рассчитывается пропорционально отработанному времени (как правило, в часах). И вот здесь начинается очень интересная тема. Для ежемесячного учета мы отталкиваемся от нормативов рабочего времени, установленных трудовым законодательством и указанных в ежегодно утверждаемом правительством производственном календаре.

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

Подготовка списка для месячного норматива рабочего времени

Для начала дополним список с месяцами. Откроем страницу «Список«, и добавим колонку справа от наименований месяцев. Назовем эту колонку «Норматив«.

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

Поэтому выделяем колонку, в которую будем заносить данные, и через правую кнопку мыши вызываем диалоговое окно «Формат ячеек«. Чтобы лишние поля, типа «Минуты» и «Секунды» нам не мешались, я выбираю пункт «Все форматы» и ставлю в поле «Тип» букву «ч» в квадратных скобках (вот так — [ч]), обозначающей отображение только часов.

Найдем в интернете производственный календарь (я использую эту  ссылку) и перепишем с него нормативы по каждому месяцу для 40-часовой рабочей недели. Только записывать нужно будет с минутами через двоеточие, например — 120:00. Иначе программа будет число воспринимать неадекватно.

Во второй части статьи «Учет рабочего времени в Excel» мы давали имя списку месяцев. Сейчас нам необходимо создать еще один поименованный диапазон. Пусть он будет называться «НормативМесяц» (без пробела). Для этого выделяем диапазон с ячейки A2 до ячейки B13 (в него должен попасть список месяцев и нормативы часов в эти месяцы). В адресную ячейку вносим имя «НормативМесяц» (см. картинку выше).

Настройка автоматического расчета окладной части заработной платы

Теперь мы можем рассчитать окладную сумму, рассчитанную пропорционально отработанному времени сотрудника. Формула арифметически проста — отработанное время делим на нормативное время и умножаем на оклад. Но как это реализовать в Excel?

Нормативное время мы можем взять на основании выбранного на странице «Учет» месяца. Для этого используем функцию ВПР, которая будет выводить то количество часов, которое соответствует выбранному месяцу (для этой функции мы и создавали диапазон «НормативМесяц«). Функцию можно сразу ввести в строку формул: ВПР(Учет!C3;НормативМесяц;2;ЛОЖЬ).

Устанавливаем курсор вначале формулы (после знака равно) и вводим частное — адрес ячейки с суммой отработанного времени. Для первой записи это будет ячейка BI11 (я, обычно, не заношу адрес ячейки вручную, а просто кликаю по ней мышкой).

Затем устанавливаем курсор в конце формулы и умножаем все на ячейку с суммой оклада (М11). Конечная формула для первой строки будет выглядеть так: =BI11/ВПР(Учет!$C;НормативМесяц;2;ЛОЖЬ)M11

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

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

Копируем формулу на все прочие ячейки

Графы с 23 по 32 оставляем пустыми. для нашей формы оплаты труда они не используются. С граф 33 и 34 делаем простые ссылки на графы 21 и 22.

Страница «Расчет» заполнена. Страницу итогов начнем настраивать в следующей статье.

Навигация по записям

Страница 1 Страница 2 … Страница 6 Следующая страница


Как на excel сделать числовой

Еще статьи:


Хороший подарок ко дню учителя своими руками

Как сделать гта 4 с модами

Прическа шишка на голове своими руками