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

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

В прошлой статье мы настроили страницу «Расчеты» для расчета окладной части заработной платы.

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

Особенности 4-й страницы формы Т-12

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

Автозаполнение даты

Хотелось бы, чтобы не приходилось вносить вручную. Чтобы дата формировалась автоматически. Ведь месяц мы уже вносили на странице «Учет«, число там уже стоит а год… ну, давайте внесем год на странице «Списки«, поскольку  итак ежегодно корректируем там ежемесячные нормативы по часам.

Заходим на страницу, и чуть ниже наименований месяцев пишем слово «Год«. Правее от него вносим 2015. Однако, что мы видим — отображается странное числе 48360. Все потому, что на эту ячейку распространился формат времени в часах, который мы настраивали ранее. Выделяем ячейку, и выбираем формат числовой без десятичных знаков.

Теперь нам предстоит непростая задача. Нужно сделать так, чтобы число месяца, наименование месяца и год были объединены в одну ячейку и преобразованы в формат даты. Я для этого использовал функцию ДАТАЗНАЧ, которая преобразует текст в виде 1-янв-2015 в формат даты. Но для этого необходимо сформировать этот текст.

Число внесем в формулу простой цифрой. С помощью оператора «&», который объединяет записи различных ячеек в одной, соединим это число с наименованием месяца через дефис. Чтобы дефис воспринимался не как оператор, а как символ, заключим его в кавычки. Далее, снова через дефис, соединяем с информацией из ячейки с текущим годом.

Формула приобретает следующий вид: =ДАТАЗНАЧ(1&»-«&Учет!C3&»-«&Списки!B15). Не забудем по всей колонке через формат ячеек настроить краткий формат даты.

Внесение дат следующих чисел в таблице упростим простым прибавлением к вышестоящей ячейке единицы. Таким образом каждая следующая ячейка будет отображать следующее число установленного месяца и года. Например в ячейке А7 будет формула =А6+1.

Далее, протянем формулу до нижней ячейки таблицы. Обратите внимание, что в месяцы с количеством дней менее 31, в нижних ячейках будут отображаться числа следующего месяца. Можно, конечно, оставить как есть, но я решил исправить несоответствие формулой в ячейках, соответствующих 29-31 числам: =ЕСЛИ(A33=»»;»»;ЕСЛИ(МЕСЯЦ(A33+1)=МЕСЯЦ(A33);A33+1;»»))

Суть этой логической формулы в двойной проверке.

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

Если вторую проверку не сделать, то в следующей, после пустой, ячейке будет отображаться ошибочное значение (#ЗНАЧ).

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

Расчет человекодней и человекочасов

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

Можно использовать запись макроса, но это отдельная тема для другой статьи. О написании макросов мы обязательно поговорим позднее. А формула для подсчета человекодней, отработанных первого числа месяца будет такой: =СЧЁТЕСЛИ(Табель!AG9:AJ36;»>0″).

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

Как вы уже, наверное, догадываетесь, человекочасы будем считать с использованием функции СУММ. Просто суммируем весь диапазон, соответствующего числа месяца. И так по всем строкам. Буквенные обозначения считаться не будут, а числовые войдут в расчет, как и положено. Не забудьте предварительно преобразовать формат ячеек во временной без указания секунд ([ч]:мм;@).

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

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

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

Продолжаем наполнение страницы «Итоги» формы Т-12.

У нас остались следующие поля:

  • Количество человеко-дней простоев. Здесь очевидно можно использовать коды, обозначающие простой по разным причинам — РП, НП, ВП, ЗБ.
  • Не отработано человеко-часов в связи с работой в режиме неполного рабочего дня. Используем код НС.
  • Дни неявок на работу:
    • ежегодный основной оплачиваемый отпуск — код ОТ;
    • ежегодный дополнительный оплачиваемый отпуск — код ОД;
    • отпуск в связи с обучением с сохранением заработной платы, повышением квалификации с отрывом от производства — коды У, ПК и ПМ;
    • отпуск по беременности и родам — код Р;
    • по болезни — код Б;
    • прочие неявки, разрешенные законодательством (выполнение государственных обязанностей и т.п.) — боюсь ошибиться, но здесь, вероятно, коды ОЖ, ДО, ОЗ, ДБ, Т, Г.
  • По причинам:
    • с разрешения работодателя — все вышеперечисленные коды;
    • прогулы — код ПР;
    • массовые неявки — забастовки в порядке, предусмотренном законом — код ЗБ;
    • количество человеко-дней выходных и праздничных — используем код В;
    • Всего — складываем пункты этого блока.
  • Всего человеко-дней явок и неявок на работу — тоже используем сложение данных в этой таблице.
  • Количество дополнительных выходных человеко-дней при пятидневной рабочей неделе — коды ОВ и НВ.
  • Списочная численность работников — складываем подсчеты по всем кодам.
  • Численность работников:
    • не учитываемых в списочном составе, но включенных в среднесписочную численность — это для ручного подсчета, поскольку данных по таким сотрудникам в табеле нет.
    • списочного состава, которые не включаются в среднесписочную численность — аналогично предыдущему пункту.

В основном будем использовать функции СЧЁТЕСЛИ и СУММЕСЛИ.  Описывать подробно каждую формулу я не буду, поскольку все подобные функции мы уже использовали не один раз. Просто приведу список этих формул, соответствующих первому числу месяца:

  • Количество человеко-дней простоев — поскольку кодов несколько, то мы складываем несколько значений. Формула в ячейке первого числа месяца будет следующей =СЧЁТЕСЛИ(Табель!AG9:AG36;»РП»)+СЧЁТЕСЛИ(Табель!AG9:AG36;»НП»)+СЧЁТЕСЛИ(Табель!AG9:AG36;»ВП»)+СЧЁТЕСЛИ(Табель!AG$9:AG36;»ЗБ»). Здесь мы снова сталкиваемся с проблемой, что в одной таблице перемещение идет по горизонтали, а в другой — по вертикали. Использовать автозаполнение не удастся.
  • Не отработано человеко-часов в связи с работой в режиме неполного рабочего дня — =СУММЕСЛИ(Табель!$AG$9:$AG$36;»НС»)
  • Дни неявок на работу:
    • ежегодный основной оплачиваемый отпуск — =СЧЁТЕСЛИ(Табель!$AG$9:$AG$36;»ОТ»);
    • ежегодный дополнительный оплачиваемый отпуск — =СЧЁТЕСЛИ(Табель!$AG$9:$AG$36;»Од»);
    • отпуск в связи с обучением с сохранением заработной платы, повышением квалификации с отрывом от производства — =СЧЁТЕСЛИ(Табель!$AG$9:$AG$36;»У»)+СЧЁТЕСЛИ(Табель!$AG$9:$AG$36;»ПК»)+СЧЁТЕСЛИ(Табель!$AG$9:$AG$36;»ПМ»);
    • отпуск по беременности и родам — =СЧЁТЕСЛИ(Табель!$AG$9:$AG$36;»Р»);
    • по болезни — =СЧЁТЕСЛИ(Табель!$AG$9:$AG$36;»Б»);
    • прочие неявки, разрешенные законодательством (выполнение государственных обязанностей и т.п.) — =СЧЁТЕСЛИ(Табель!$AG$9:$AG$36;»ОЖ»)+СЧЁТЕСЛИ(Табель!$AG$9:$AG$36;»ДО»)+СЧЁТЕСЛИ(Табель!$AG$9:$AG$36;»ОЗ»)+СЧЁТЕСЛИ(Табель!$AG$9:$AG$36;»ДБ»)+СЧЁТЕСЛИ(Табель!$AG$9:$AG$36;»Т»)+СЧЁТЕСЛИ(Табель!$AG$9:$AG$36;»Г»).
  • По причинам:
    • с разрешения работодателя. Здесь достаточно сложить предыдущие расчеты — =СУММ(AU6:DF6);
    • прогулы — =СЧЁТЕСЛИ(Табель!$AG$9:$AG$36;»ПР»);
    • массовые неявки — забастовки в порядке, предусмотренном законом — =СЧЁТЕСЛИ(Табель!$AG$9:$AG$36;»ЗБ»);
    • количество человеко-дней выходных и праздничных — =СЧЁТЕСЛИ(Табель!$AG$9:$AG$36;»В»);
    • Всего — =СУММ(DG6:EX6).
  • Всего человеко-дней явок и неявок на работу — =СУММ(I6;EY6).
  • Количество дополнительных выходных человеко-дней при пятидневной рабочей неделе — =СЧЁТЕСЛИ(Табель!$AG$9:$AG$36;»ОВ»)+СЧЁТЕСЛИ(Табель!$AG$9:$AG$36;»НВ»).
  • Списочная численность работников — =СУММ(I6;Y6;EY6;FU6).

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

Учет личных финансов в Excel. I

Учет личных финансов ExcelУчет личных финансов в Excel. Часть I.

Старая пословица гласит — «Деньги любят счет».

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

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

Продумывание структуры файла

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

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

Итак, у нас получились следующие колонки (См. рисунок):

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

Вот что у нас должно получиться:

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

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

Мои имена диапазонов следующие:

  • «ДоходРасход»;
  • «Доход» — статьи доходов;
  • «Расход» — статьи расходов.

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

 

Структура таблицы готова. Если что-то понадобиться в ходе работы, мы всегда можем внести дополнения или исправления.

Настройка ввода даты

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

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

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

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

Формулу предлагаю внести не в верхней пустой ячейке, а во второй строке таблицы (по адресу C3). Выглядеть она будет так:

=ЕСЛИ(ЕПУСТО(Таблица2[[#Эта строка];[Дата]]);C2;Таблица2[[#Эта строка];[Дата]])

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

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

Чтобы избежать ошибки, в колонке «Дата» можно установить защиту от ошибочного ввода данных с помощью меню Данные-проверка данных (об этом подробнее здесь). Установим значение «Дата» больше, например, вчерашнего дня (если, конечно, вы не предполагаете вносить вчерашние данные.

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

Учет личных финансов в Excel. II

Учет личных финансов ExcelУчет личных финансов в Excel. Часть II.

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

Продолжим работу.

Настройка выбора из списка

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

Начнем с колонки «Доход/Расход«. Выделяем все ее ячейки, и в меню «Данные» выбираем пункт «Проверка данных«. В открывшемся окне выбираем тип данных «Список«, а в поле «Источник» вписываем наименование диапазона, на который ссылаемся. У меня он называется «ДоходРасход«. Теперь любое значение в колонке будет задаваться выбором из заданного списка.

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

«Тип данных«, как и прежде, выбираем «Список«. А вот в поле «Источник» нужно будет внести функцию ДВССЫЛ. Она будет ссылаться на ячейку колонки «Доход/Расход» и вызывать тот список, который в ней отображается. Поставим галочку в пункте «Распространить изменения на другие ячейки с тем же условием«. Так формула будет автоматически распространяться на всю колонку таблицы.

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

  • «Тип данных» — «Действительное»;
  • «Значение» — «Больше»;
  • «Минимум» — «0».

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

Колонку «ДатаГлав» предлагаю скрыть, чтобы она нас не отвлекала.

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

Формирование отчета

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

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

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

=ЕСЛИ(Ввод[[#Эта строка];[Доход/ Расход]]=»Расход»;-Ввод[[#Эта строка];[Сумма]];Ввод[[#Эта строка];[Сумма]])

Суть ее проста:

  • если в колонке «Доход/Расход» выбрано «Расход«;
  • то к значению из колонки «Сумма» добавляется знак минус;
  • В противном случае значение из колонки «Сумма» копируется без изменений.

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

Теперь переходим на лист отчеты. Открываем закладку меню «Вставка» и жмем на пункт «Сводные таблицы«. В поле «Таблица или диапазон» заносим имя только что поименованной таблицы — «Ввод«.  Нажимаем «ОК«. Видим слева появилось поле сводной таблицы (оно пока пустое), а справа — список полей для настройки и четыре зоны:

  • Фильтр отчета;
  • Названия столбцов;
  • Названия строк;
  • Значения.

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

Затем находим пункт «Группировка по полю«. Выбираем пункт «Месяцы» и нажимаем «ОК«. Колонки должны сгруппироваться по месяцам.

Теперь необходимо задать имена строк. Они у нас будут сгруппированы по двум параметрам — Доход/Расход и статьи. Поэтому вначале переносим в зону «Названия столбцов» пункт «Доход/Расход«, а затем, ниже, пункт «Статья«.

В зону «Значения» переносим пункт «СумПреобр«. Вот что у нас должно получиться:

Месяц можно задать и в качестве фильтра. Достаточно просто перенести пункт «ГлавДата» в зону «Фильтр отчета«. В сводной таблице появится поле для выбора месяца.

Однако, на мой взгляд, лучше оставить значения поля «ГлавДата» в названиях колонок. Это даст нам больше возможностей, в том числе и фильтрации. Кликните правой кнопкой мыши на поле с датой, и выберите в контекстном меню Фильтр Фильтры по дате.

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

Отчетная таблица готова!

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

Учет личных финансов в Excel. III часть

Учет личных финансов ExcelУчет личных финансов в Excel. Часть III.

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

Осталась самая малость — построение графиков и окончательное оформление.

В конце текста выложен файл, который вы можете скачать.

Построение графиков

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

Итак, идем на страницу «Графики«, открываем вкладку меню «Вставка» и жмем не на саму иконку сводной таблицы, а на надпись. Раскроется небольшой список, в котором мы выбираем «Сводная диаграмма«. Далее — все как и со сводной таблицей:

  • В поле «Таблица или диапазон» вносим имя таблицы — «Ввод«;
  • Нажимаем «ОК«;
  • В зону «Фильтр отчета» заводим вначале пункт «ГлавДата«, а ниже «Доход/Расход«;
  • В зону «Поля осей» заводим пункт «Статья«;
  • В зону «Значения» заводим пункт «Сумма«.

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

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

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

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

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

Надпись «ГлавДата» лучше заменить на «Месяц». Так будет понятнее. Вот что у меня получилось.

Окончательное оформление и защита

Необходимости в дополнительных оформлениях страниц «Отчеты» и «Графики» я не вижу. На мой взгляд — все итак нормально. А вот страницу «Ввод» нужно еще немного доработать.

Во-первых, скроем не скрытую раньше колонку «СумПреобр«. Она будет заполняться автоматически, как и скрытая нами ранее колонка «ГлавДата«.

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

Также настраиваем условие со словом «Расход«. Заливку, на этот раз, делаем красную.

Ну, пожалуй, и все. Прочие интересности оформляйте уже самостоятельно. А по ссылке можете скачать файл, который у меня получился.

Создание каталогов в Excel (электронная библиотека) I

Создание каталогов Excel (электронная библиотека)Создание каталогов в Excel (электронная библиотека) I часть.

С помощью Excel можно создавать различные списки и каталоги, позволяющие систематизировать данные вашего компьютера.

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

Что нам даст создание списка и его систематизация? Облегчение поиска необходимого в дальнейшем. Допустим, нам захотелось послушать спокойную музыку. Если у нас есть список, то мы просто отфильтровываем его по признаку «Спокойная музыка«, и получаем то, что требуется.

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

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

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

Итак, приступим.

Продумывание формы каталога

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

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

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

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

Все эти признаки будут у нас колонками таблицы-каталога:

  1. ФИО автора — сортировка по автору используется чаще всего;
  2. Название книги — для поиска конкретной книги;
  3. Год написания — иногда бывает необходимо;
  4. Год издания;
  5. Издатель;
  6. Жанр — по нему будем сортировать, когда есть желание почитать из определенного жанра;
  7. Формат (аудио/текст) — иногда хочется не читать, а слушать;

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

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

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

Чем меньше данных мы будем вводить вручную с клавиатуры, тем лучше. Поэтому создадим отдельный лист, на котором будем вводить списки, из которых настроим выбор значений. Пока это колонки «Жанр» и «Формат (аудио/текст)». Однако по жанру сделаем две ступени группировки, иначе список получится слишком большим и им неудобно будет пользоваться.

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

Создание каталогов в Excel (электронная библиотека) II

Создание каталогов Excel (электронная библиотека)Создание каталогов в Excel (электронная библиотека) II часть.

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

Осталось совсем немного — настроить выбор из списка в колонках «Жанр» и «Формат«, и наполнить таблицу содержанием.

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

Начнем со списков.

Создание списков

Создавать сразу идеальную группировку я не советую. Все равно, со временем, вы сами пожелаете что-то дополнить. Поэтому создавайте его, ориентируясь на те книги, которые у вас уже имеются. Посмотрите, что получилось у меня:

Обратите внимание, записи из списка «Группа1» являются заголовками трех остальных списков (по количеству записей в первой группе). Каждый из списков вставьте в таблицу и дайте им в точности те же имена, что написаны в заголовках.

Впоследствии, если список «Группа1» у вас будет дополняться, то каждой новой записи должна соответствовать отдельная таблица с точно таким же именем.

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

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

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

Заполнение каталога и настройка ссылок

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

В открывшемся окне должен быть активирован в колонке «Связать с:» пункт «файлом, веб-страницей«. Ближе к правому краю находим иконку открытой желтой папки (Поиск файла) и жмем ее. Во втором открывшемся окне, во-первых,  находим кнопку выбора отображаемых форматов файлов, и выбираем пункт «Все файлы«. Находим файл электронной книги и жмем ее. Нажимаем «ОК«.

Можно проверить — работает ли гиперссылка. Проделываем аналогичную операцию со всеми остальными книгами.

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

С помощью поиска (кнопку «Найти и выделить» в правой части закладки меню «Главная«) можно отыскать необходимую запись по названию или автору.

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

Ежедневный мониторинг выполнения плана в Excel. I часть

Ежедневный мониторинг выполнения плана ExcelЕжедневный мониторинг выполнения плана в Excel. Часть I.

Сегодня я хочу поделиться с вами своим опытом участия в планировании производства.

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

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

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

Постановка задачи

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

  • Разбивать месячный план на каждый день, но с учетом того, сколько уже было сделано. Другими словами, если сегодня мы не справились с дневной нормой, то она перераспределяет нормативы следующих дней.
  • Рассчитывать, сколько человек необходимо привлечь к производству, чтобы справиться с планом.
  • Указывать фактическую выработку на человека , чтобы сигнализировать об отклонениях.
  • Указывать, сколько уже сделано на текущий день.
  • Использовать данные, которые в настоящее время уже ведутся, чтобы не перегружать сотрудников дополнительной работой.

Продумывание логики реализации

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

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

  • сколько сотрудников отработало в смене;
  • сколько было выработано за каждую смену.

Этого достаточно, чтобы рассчитать:

  • сумму выработки на текущий день;
  • сумму выработки на человекосмену.

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

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

Таблица ввода отработанных часов

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

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

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

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

Для даты у нас в разных ячейках есть наименование месяца и год (в ячейке С1 мы это предусмотрели). Используем функцию ДАТАЗНАЧ (она преобразует текстовое обозначение даты в числовой, который и понимает Excel)  и ссылаемся на эти ячейки. А в том месте, где должно быть число месяца, просто ставим 1. Все эти данные в формуле объединяем с помощью оператора &: =ДАТАЗНАЧ(1&E1&C1)

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

  • используем функцию МЕСЯЦ, которая преобразует дату в число, обозначающее порядковый номер месяца;
  • с помощью функции ЕСЛИ настроим сравнение по алгоритму
    • если номер месяца даты в ячейке слева равен номеру месяца той же даты плюс 1 то …
    • оставляем ту же дату плюс 1, в противном случае …
    • оставляем пустое значение (оно указывается просто двумя кавычками).
  • Эту формулу запишем во всех ячейках, соответствующих числам месяца 29-31.

=ЕСЛИ(МЕСЯЦ(AG3)=МЕСЯЦ(AG3+1);AG3+1;»»)

День недели определяется с помощью функции ДЕНЬНЕД. Правда возвращает он число от 1 до 7, а нам нужно текстовое обозначение. Делается это с помощью следующей формулы: =ТЕКСТ(ДЕНЬНЕД(G3;2);»ДДД») Как вы понимаете, три буквы Д в кавычках определяет формат отображения дня недели.

Чтобы не было надписей, соответствующих пустым ячейкам с датой, добавим условие ЕСЛИ ячейке, соответствующей 29-му числу месяца: =ЕСЛИ(AH3=»»;»»;ТЕКСТ(ДЕНЬНЕД(AH3;2);»ДДД»)) Затем протянем эту формулу на ячейки, соответствующие 30-му и 31-му числам месяца.

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

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

О защите ячеек от ошибочного ввода я писал здесь. Настроим условие на ввод целых чисел от 1 до 12 (ну, или до 24). Пароль в своем файле я не устанавливаю, а вам предлагаю это сделать.

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

Ежедневный мониторинг выполнения плана в Excel. II часть

Ежедневный мониторинг выполнения плана ExcelЕжедневный мониторинг выполнения плана в Excel. Часть II.

Продолжим формировать таблицу.

Ранее мы настроили страницу ввода данных об отработанном времени рабочих и о дневной выработке.

Пора переходить к обработке этих данных.

Страница отчета

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

  • План на месяц в рублях — сумма будет вноситься руководителем вручную, поскольку это значение рассчитывается заранее;
  • Текущая дата — это значение будет устанавливаться автоматически с помощью функции СЕГОДНЯ;
  • Фактическое выполнение плана на текущую дату — значение рассчитывается автоматически на основании данных по ежедневной выработке, которую вносят мастера на странице «Бригады«;
  • Процент выполнения плана — просто делим факт на план;
  • Выработка на человекосмену в рублях — очень важный показатель, который мы рассчитали ранее на основании прежних статистик по выработке. На основании этого значения будет рассчитываться потребность в рабочих для выполнения ежедневного плана по выработке.

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

Теперь приступим к созданию строк нашей таблицы:

  • Дата — дата, соответствующая дню месяца. Мы можем сделать простые ссылки на ячейки с датой страницы «Бригады«;
  • План — здесь должен быть хитрый расчет, вначале месяца мы просто делим сумму плана на количество рабочих дней в месяце, и получаем искомое значение. Количество дней в месяце это функция СЧЕТ, которая подсчитывает количество чисел в диапазоне. Эту функцию я поместил справа от нашей таблицы, чтобы не загромождать формулу. А теперь давайте подумаем — после того, как первый день будет отработан, значение выработки в этот день должно скорректировать план. Если план на день перевыполнен, то сумма перевыполнения распределяется по оставшимся дням, снижая, тем самым, плановую выработку на эти дни. Если план не выполнен, то выработка на следующие дни должна быть больше. Это непростая задача, и о том, как она решена, будет написано ниже.
  • Факт — простые ссылки на значения со страницы «Бригады«.
  • План по количеству рабочих на день. Здесь достаточно разделить дневной план по выработке на значение выработки на человекосмену. Правда будут получаться дробные значения, а полтора землекопа нам ну никак не нужны. Поэтому используем функцию ОКРВВЕРХ. Вот формула для ячейки, соответствующей первому числу месяца: =ОКРВВЕРХ(B8/$B$5;1)
  • Количество рабочих. Снова используем функцию СЧЕТ. На этот раз на странице «Бригады» посчитаем количество чисел в колонках с соответствующими днями месяца. Ведь каждое число, это человек, который отработал в этот день определенное количество часов.
  • Не хватает для плана — это разница между требуемым количеством рабочих, и фактическим.
  • Фактическая выработка на человекосмену — фактическую выработку делим на количество рабочих.

Как же нам настроить автоматический пересчет плана на каждый день? Используем следующие предпосылки:

  • Каждый день план уменьшается на сумму фактической выработки прошлых дней. Значит расчет будем вести от разницы между месячным планом и суммой выработки прошлых дней.
  • Вот только когда мы будем протягивать формулу на следующие ячейки, она будет увеличивать план, поскольку в этой логике все нулевые значения будут означать, что план не выполнен. Нам нужно создать отдельную строку, в которой будет условие — если рабочие в день не работали, будут браться плановые значения. А если рабочие работали — фактические. Это условие я поместил в строку №15, которую назвал «Проверка». Вот какая там формула: =ЕСЛИ(B9=0;B8;B9). Можно привязать расчет и к формуле СЕГОДНЯ:  =ЕСЛИ(В7>=СЕГОДНЯ();В8;В9). Правда в этом случае необходимо обеспечить своевременное заполнение таблицы и с данными в ней задним числом уже не поиграешься.
  • Каждый раз, считая ежедневный план, нам необходимо делить оставшуюся плановую сумму на оставшееся количество дней. Откуда брать оставшееся количество дней? Тут я долго не думал, а просто добавил над таблицей ряд от 1 до 30, начинающийся с колонки, соответствующей второму числу месяца. Теперь если из количества дней в месяце (эту формулу мы добавили чуть ранее, как вы помните) вычесть цифру, соответствующую текущей дате, то получится как раз количество оставшихся дней до конца месяца.

Таким образом, формула плана по выработке на первое число месяца будет такой: =$B$1/$AG$7

А со второго числа — такой: =($B$1-СУММ($B$15:B15))/($AG$7-C6)

Протянем ее до конца месяца и, начиная с 29 числа, добавим условие на пустое значение (если ячейка даты пустая, то и эта ячейка должна быть пустой):

=ЕСЛИ(AE7=»»;»»;($B$1-СУММ($B$15:AD15))/($AG$7-AE6))

Все! Вот формулы в ячейках, соответствующих вторым числам месяца (см. рис.)

И вот такая получилась таблица:

Остается составить диаграмму для наглядности. И тут нас ждет очередная засада — график выстраивает линии даже там, где их быть не должно — на пустых значениях линии плана, и на нулевых значениях линии факта. Я долго искал решение, и нашел один вариант — использовать функцию НД. Ее диаграмма воспринимает как пустое значение, и линии не строит.

Для этого нам нужно выстроить отдельные ряды с формулой:

  • Для плановых значений делаем простые ссылки, а в колонке соответствующей 29-му числу месяца пишем: =ЕСЛИ(AD8=»»;НД();AD8). Затем протягиваем формулу до колонки, соответствующей 31-му числу месяца.
  • Для фактических значений начиная с колонки, соответствующей первому числу, пишем: =ЕСЛИ(B9=0;НД();B9). Заем протягиваем формулу до 28-го числа. Далее пишем: =ЕСЛИ(AE9=0;НД();ЕСЛИ(AE9=»»;НД();AE9)) и протягиваем формулу до 31-го числа.

Эти два ряда и используем для построения диаграммы.

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

Расчет заработной платы в Excel. I часть

Расчет заработной платы ExcelРасчет заработной платы в Excel. Часть I.

Схем расчета заработной платы в разных организациях разработано великое множество.

И, казалось бы, какой смысл приводить пример всего лишь одной расчетной таблицы зарплаты?

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

Тем более, что рассматриваем мы отнюдь не простой способ расчета сдельной оплаты труда с фондом, зависящим от суммы выработки предприятия. Этот фонд часто называют «котел«.

В чем суть этого котла?

  1. Ежемесячно подсчитывается сумма, на которую было произведено (или продано, если это торговля) продукции;
  2. Эта сумма соотносится с планом, меняясь в зависимости от процента выполнения плана;
  3. По каждому рабочему (сотруднику) рассчитывается его доля, на которую он может рассчитывать в этом «котле». Эта доля зависит:
    1. От времени, которое сотрудник отработал за месяц;
    2. От его эффективности, которая может определяться по различным параметрам. Например: количество брака, которое допустил работник, количества нареканий к нему со стороны руководства или коллег (покупателей), количества произведенной (проданной) продукции и т.п.
    3. От количества работников, поскольку, чем больше людей в бригаде, тем меньшая сумма приходится на каждого.
  4. Сумма котла умножается на эту долю, определяя, таким образом, сумму, на которую может рассчитывать работник.

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

Структура файла

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

выработка

На производстве оценка доли, которую имеет рабочий, рассчитывается на основании оценки, называемой «Коэффициент Трудового Участия» (КТУ). Ее устанавливает мастер или бригадир ежедневно, на основании того, как сработал этот день рабочий — сколько сделал брака, исполнял ли распоряжения и т.п. Обычно правила выставления КТУ строго регламентируются, чтобы свести к минимуму произвол, возможный со стороны мастеров или бригадиров. Эти правила тоже можно заложить в Excel, но мы не будем усложнять материал. Главное — понять принцип.

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

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

Лист «Бригады» предлагаю переименовать на «Часы«. Лист с КТУ так и назовем — «КТУ«. А лист для ввода назовем «Ввод«.

Страницы для учета часов и КТУ

Поскольку первоначально мы настраивали лист «Бригады» (теперь «Часы«) для ввода, то можно попросту скопировать его содержание на лист «Ввод» со всеми формулами. Следующий шаг — на все заполненные ячейки страницы «Ввод» настраиваем ссылки со всех ячеек страницы «Часы«. Для этого устанавливаем табличный курсор на ячейку А5 листа «Часы«, вносим знак «=«, переходим на лист «Ввод» и кликаем мышкой на аналогичной ячейке. Жмем «Enter«. Затем протягиваем ячейку А1 листа «Часы» на все ячейки в диапазоне А5:AJ28.

Аналогичным образом сделаем ссылки со всех прочих заполненных ячеек листа «Часы«.

Поскольку на странице «Часы» данные вносится не будут, нужно сделать все ячейки здесь защищаемыми. Ну, и отмените заливку, чтобы не сбивать пользователей с толку.

Страницу «КТУ» я предлагаю оформить также, как и страницу «Часы«. То есть копируем все ячейки страницы «Часы» и вставляем их в страницу «КТУ«. Ссылки на количество часов удаляем.

Теперь добавим колонки для ввода значений КТУ на странице ввод и строку для наименования колонок. После всех манипуляций с оформлением, получится вот такая таблица (см. рис.).

таблица ввода

Если вы помните, мы делали ограничение на ввод значений кроме от 1 до 12. На странице «Часы» это ограничение можно оставить, а со страницы КТУ ограничение нужно снять.

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

Аналогичным образом выделите все ячейки для ввода КТУ на странице «Ввод» (выделять придется с помощью клавиши Ctrl) и смените ограничение.

В организации, где я работал, максимальный КТУ мог равняться двум. Поэтому давайте настроим диапазон возможных значений КТУ от 0 до 2.

Сделаем ссылки на ячейки с КТУ со страницы «КТУ«. На этот раз автозаполнением нам удастся протянуть ссылки только по вертикали. Протягивание по горизонтали не учтет то, что нам нужно пропускать ссылки через колонку. Поэтому придется настраивать ссылки вручную.

В следующей статье мы приступим к настройке страницы расчета заработной платы.