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

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

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

В прошлой статье мы подготовили страницы учета рабочего времени, КТУ и выработки.

Теперь, на основании данных на этих страницах, нужно рассчитать необходимую сумму.

Страница расчета ЗП

После того, как все страницы настроены, остается добавить лист для расчета заработной платы. Так его и назовем — «РасчетЗП» Первые 5 колонок будут ссылками на страницу «Ввод» (№; ФИО; Профессия; Разряд; Бригада). Поэтому просто скопируем их с листа «Часы» или с листа «КТУ«.

Расчет оклада

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

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

Теперь в колонке «Оклад» в ячейку F5 занесем формулу: =ВПР(D5;Разряд;2;ЛОЖЬ)

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

Протягиваем формулу до нижней строки таблицы.

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

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

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

Нормативное количество рабочих часов в текущем месяце предлагаю указать с помощью функции ВПР в ячейке справа от названия месяца (F1). Формула в ней будет такой: =ВПР(E1;Списки!A2:B13;2;ЛОЖЬ).

Количество отработанных часов, это сумма строк на странице «Часы«.

Назовем колонку «Оклад начисл.» Вот формула в ячейке G5:

=F5/$F$1*СУММ(Часы!F5:AJ5).

Протянем ее до нижней строки таблицы.

Расчет сделки

Приступаем к разработке формулы расчета сдельной части оплаты труда. Логика расчета такова:

  1. В зависимости от выполнения плана производства, определяется сумма общего котла.
  2. Доля каждого рабочего в этом котле определяется по сумме всех КТУ сотрудника за месяц.
    1. Сумма КТУ всех работников, это 100%.
    2. Доля КТУ работника, это сумма его КТУ за месяц, деленное на общую сумму всех КТУ и умноженное на 100%.
  3. Подобным образом определяем долю отработанного работником времени.
  4. Умножаем долю работника в котле на его же долю в общем количестве отработанных рабочими часов.
  5. Умножаем сумму котла на долю работника, определив, тем самым, сумму его сделки.

Для определения суммы котла руководство определило такое условие:

  • При выполнении плана менее чем на 50% сумма не выделяется;
  • При выполнении плана в диапазоне от 50% до 70% выделяется 1% от месячной суммы выработки;
  • При выполнении плана в диапазоне от 70% до 90% выделяется 2% от месячной суммы выработки;
  • При выполнении плана в диапазоне от 90% до 110% выделяется 3% от месячной суммы выработки;
  • При перевыполнении плана более чем на 110% выделяется 3,5% от месячной суммы выработки.

Заведем следующий список на странице «Списки» (см. рис.)

Назовем этот список «ДоляКотла«. Для определения процента, составляющего долю котла будем использовать функцию ВПР с указанием ИСТИНА (при указании аргумента ИСТИНА будет возвращаться значение, соответствующее ближайшему меньшему значению сравниваемого числа).

Формула расчета будет такой:

ВПР(СУММ(Планирование!$B$9:$AF$9)/Планирование!$B$1;ДоляКотла;2;ИСТИНА).

  • СУММ(Планирование!$B$9:$AF$9) — это сумма выработки за весь месяц;
  • Планирование!$B$1 — это план по выработке;
  • ДоляКотла — это таблица, на которую ссылается функция ВПР.

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

=СУММ(Часы!F5:AJ5)/СУММ(Часы!$F$5:$AJ$32)*СУММ(КТУ!F5:AJ5)/СУММ(КТУ!$F$5:$AJ$32)

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

=Планирование!$B$3*ВПР(СУММ(Планирование!$B$9:$AF$9)/Планирование!$B$1;ДоляКотла;2;ИСТИНА)*H5

Теперь осталось просуммировать все составляющие в колонке «Итого ЗП«, оформить таблицу и все готово.

Случайное распределение с заданной суммой. I часть

Случайное распределение с заданной суммой ексельСлучайное распределение с заданной суммой. Часть I.

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

Не буду уточнять, для чего это необходимо, вариантов может быть масса.

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

Итак — условия задачи:

  1. Есть некая сумма;
  2. Есть календарь, где каждый день разбит на часы (см.рис.)
  3. Эту сумму необходимо распределить по дням и часам с определенными ограничениями:
    1. В ночное время значения должны составлять 70% от дневных;
    2. Вариации должны быть в интервале 5-10% от средних значений;
    3. В праздничные дни значения также должны составлять 70% от дневных значений в обычные дни.

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

Создание вводной страницы

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

  1. Итоговая сумма — это очевидно. Нам необходимо «раскидать» значения по дням и часам месяца именно так, чтобы в сумме получилось эта сумма. Ячейке с суммой дадим имя «Сумм» (это облегчит использование формул в дальнейшем);
  2. Расчетный год — таблицу создаем «на века», поэтому позаботимся над тем, чтобы можно было устанавливать год;
  3. Расчетный месяц — чтобы знать, на сколько дней необходимо «раскидывать» сумму, а также иметь информацию о выходных и праздничных днях. Для выбора месяца из списка, создадим страницу «Списки» и создадим там список месяцев (см.рис.). Подробнее о работе со списками читайте здесь;
  4. Время начала и окончания ночи — по трудовому законодательству ночь начинается в 22.00, а заканчивается в 6.00. Однако некоторые организации началом и окончанием ночи считают, например, время работы ночных смен. Словом, необходимо оставить возможность менять эти часы. Первое значение ночного интервала назовем «Ночь1«, второе — «Ночь2«;
  5. Примерная доля, отличающая значения в праздничные дни и в ночные часы (у нас — 70% в том и в другом случае). Ячейку со значением доли днем назовем «ДоляДень«, ночью — «ДоляНочь«, в праздники — «ДоляПраздник«;
  6. Процент вариации, т.е. в каких пределах примерно должны отличаться значения друг от друга. В нашем случае — 10%. Назовем эту ячейку «Вариация».

Закладываем все это на странице «Ввод» (см.рисунок выше).

Настройка формы основной таблицы

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

Формула первого числа месяца будет преобразовывать введенные на странице «Ввод» текстовые значения в дату: =ДАТАЗНАЧ(1&Ввод!B2&Ввод!B1)

Следующие числа будут образованы простым прибавлением единицы к предыдущей дате. А формула, соответствующая 29-му числу будет следующей: =ЕСЛИ(МЕСЯЦ(N34)=МЕСЯЦ(N34+1);N34+1;»»).

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

Настройка условий распределения

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

Распределение долей

Запрограммировать условие, при котором в ночные часы должно быть одно значение, а в дневные — другое, не проблема. Используем функцию ЕСЛИ. В случае, когда необходимо заложить два условия, используем функцию ИЛИ: =ЕСЛИ(ИЛИ($A4<Ночь1;$A4>=Ночь2);ДоляНочь; ДоляДень)

Перевод функции на русский примерно такой:

  • Если одно из двух условий верно:
    • значение меньше значения в ячейке «Ночь1» или…
    • значение больше или равно значения в ячейке «Ночь2«
  • то используем значение в ячейке «ДоляНочь«, в противном случае…
  • используем значение в ячейке «ДоляДень«.

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

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

Формула будет такой:

=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(B$3;Праздник;0));ДоляДень;ДоляПраздник)

Перевод на русский:

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

Теперь, напрягая остатки интеллекта (у меня шел дым из ушей), объединяем эти две части формулы в одну:

=ЕСЛИ(ИЛИ($A4<Ночь1;$A4>=Ночь2);ДоляНочь;ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(B$3;Праздник;0));ДоляДень;ДоляПраздник))

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

На этом я прерываюсь. Продолжим в следующей статье.

Случайное распределение с заданной суммой. II часть

Случайное распределение с заданной суммой ексельСлучайное распределение с заданной суммой. Часть II.

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

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

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

Распределение средних по долям

Теперь нам необходимо распределить значения в соответствии с этими долями. Здесь все чрезвычайно просто:

  • просуммируем все доли и назовем ячейку с этой суммой «СумДоли«;
  • создадим еще одну таблицу для промежуточных значений (я ее назвал «Распределение средних по долям);
  • в ячейке новой таблицы, соответствующей первому числу месяца запишем формулу: =B4/СумДоли*Сумм
    В4 — это значение, соответствующее первому числу месяца из первой таблицы, «Сумм» — значение в ячейке с суммой, которую необходимо «раскидать»;
  • скопируем формулу в прочие ячейки таблицы.

Случайное распределение

Мы определили своеобразный каркас нашей модели. Осталась самая малость — обеспечить случайное распределение в рамках тех ограничений, которые были заданы. Мы обеспечили соблюдение почти всех правил, кроме одного — значения должны варьироваться в пределах установленного диапазона вариации (мы определили 10%). Для этого создадим еще одну таблицу («Распределение корректив«), которая будет устанавливать корректирующие значения в пределах диапазона, установленного нами ранее (процент вариации, если помните).

Для случайного распределения по нормальному принципу используем две функции:

  • НОРМОБР преобразует число в единичное наблюдение кривой нормального распределения;
  • СЛЧИС обеспечит случайное число.

Для функции НОРМОБР необходимо три переменных:

  1. случайное число в интервале от 0 до 1, которое мы обеспечиваем функцией СЛЧИС;
  2. среднее значение. Поскольку среднее значение у нас зависит от указанных ранее чисел, нам необходимо оперировать долями, которые мы устанавливали в ячейке «Процент вариации» и числами, которые мы рассчитали в таблице распределения средних значений. Формула здесь будет такая — =B63-B63*(1-Вариация/2). Суть ее в том, что мы определяем среднее значение отклонения в большую или меньшую сторону, которое должно равняться половине значения вариации, которую мы установили в ячейке «Процент вариации» (уж не знаю, понятно ли я объяснил суть формулы);
  3. стандартное отклонение. Не буду вдаваться в подробности статистических функций, скажу лишь, что это разброс большинства значений в нашей выборке от среднего значения. Чем меньшее число мы установим, тем больше значений в выборке будет стремиться к среднему. Чтобы приблизить кривую распределения к нормальной, я предлагаю установить стандартное отклонение как пятую часть вариации. Формула такова: =B63-B63*(1-Вариация/5)

Есть одна проблемка — мы не сможем настроить сложение или вычитание по случайному принципу. Поэтому нам необходимо обеспечить вариацию от нуля в сторону положительных или отрицательных чисел. А затем уже можно прибавить к среднему значению положительное или отрицательное число. Поэтому из преобразованного с помощью функции НОРМОБР числа мы вычтем то среднее значение, которое было в этой формуле использовали: =B63-B63*(1-Вариация/2). Таким образом вся формула, соответствующая первому числу месяца, будет выглядеть так:
=НОРМОБР(СЛЧИС();B63-B63*(1-Вариация/2); B63-B63*(1-Вариация/5))-(B63-B63*(1-Вариация/2))

Скопируем ее на все ячейки.

Настройка основной таблицы

Пора переходить к нашей основной таблице. Мы вычислили все промежуточные значения, и осталось из средней, вычисленной в таблице «Распределение средних по долям» вычесть отклонение, вычисленное в таблице «Распределение корректив«. Формула, соответствующая первому числу месяца, будет такой: =Коррективы!B63+Коррективы!B120

Казалось бы — и все. Однако остается проблема. Если мы просуммируем все, вычисленные таким образом, значения, они не дадут идеального совпадения с той суммой, которая нами установлена изначально. Можно было бы установить какую-нибудь одну корректирующую ячейку, значение в которой сформировалось бы путем вычитания из общей суммы, суммы всех ячеек, кроме последней. Но, в этому случае, отклонение может стать слишком большим. Поэтому я сделал 12 корректирующих ячеек, в диапазоне N43:N54.

Коррекция для обеспечения конечной суммы

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

  1. определяем шкалу, которая будет обеспечивать доли разброса. Эта шкала должна в сумме давать 12, чтобы сумма соответствовала двенадцати ячейкам. Ее я настроил рядом с таблицей распределения корректив, в диапазоне R156:R167;
  2. производим расчет значений путем перемножения среднего значения на долю (диапазон S156:S167);
  3. добавим шкалу, которая будет определять порядок вывода значений в таблице (диапазон Т156:Т167);
  4. в основной таблице, в ячейках диапазона N43:N54 используем функцию ВЫБОР, которая вытягивает значение из таблицы на основании номера, указанного нами в шкале, созданной на предыдущем шаге. Формула первого значения в этом диапазоне будет такой: =ВЫБОР(Коррективы!T156;Коррективы!$S$156;Коррективы!$S$157;Коррективы!$S$158;Коррективы!$S$159;Коррективы!$S$160;Коррективы!$S$161;Коррективы!$S$162;Коррективы!$S$163;Коррективы!$S$164;Коррективы!$S$165;Коррективы!$S$166;Коррективы!$S$167)

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

А у меня — все. Во вложении файл со всеми расчетами.

Календарь в Excel

Календарь в ExcelРазличных офисных приложений с календарями немало.

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

Опишу, что он умеет:

Как видно из рисунка, календарь охватывает три месяца – прошлый, текущий и следующий.

Смена месяцев происходит автоматически. Также автоматически происходит и смена года. То есть 1 января 2016 года заголовок сменится на число 2016.

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

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

Все числа, соответствующие субботе и воскресению окрашены красным цветом.

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

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

Оба списка на листе «Памятные» расширяются автоматически при добавлении записей.

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

Обратите внимание, при обновлении года даты памятных дней и праздников не обновятся. То есть, если наступит декабрь 2015 года, то праздничные дни января (который будут относиться уже к 2016-му году) не выделятся.

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

Пользуйтесь на здоровье :-)

Расчет итогов инвентаризации в программе Excel

Расчет итогов инвентаризации ExcelРасчет итогов инвентаризации в Excel.

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

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

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

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

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

В появившемся окне не забываем поставить галочку напротив пункта «Создать копию». Затем выбираем лист, перед которым будет располагаться наша копия (я выбираю лист «Свод») и нажимаем «ОК». Теперь на этот лист можно вносить данные инвентаризации.

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

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

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

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

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

Собственно, это все. Теперь давайте займемся доработкой. Жду предложений в комментариях.

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

Защита от ошибочного ввода данных ExcelЗащита от ошибочного ввода данных в Excel.

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

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

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

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

Для начала, необходимо ограничить ввод числовых значений. Другими словами — сделать так, чтобы в ячейки для чисел невозможно было ввести буквы или слова. Кроме того, не помешает, если мы запретим вносить числа больше 24. Если руководитель напишет число 41, то, очевидно, что это опечатка.

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

В поле «Тип данных» выбираем пункт «Действительное«, если допускается внесение дробных значений, или «Целое число«, если дробные значения не допускаются.В поле «Значение» выбираем «Между«. В поле «Минимум» вносим «0«, в поле «Максимум» вносим «24«.

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

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

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

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

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

Так настраиваем всю таблицу.

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

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

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

После выбора команды «Защитить лист» открывается окошко, позволяющее выбрать параметры защиты:

Назначить пароль я рекомендую, а прочие пункты можно оставить в том виде, в котором они заданы. Нажимаем «ОК«, и проверяем. Попробуйте внести какую-нибудь запись вне того блока, который мы выделяли. Должно появиться окошко, как на рисунке. Если появилось, значит все проделано верно.

Excel для начинающих II

Excel для начинающихExcel для начинающих II часть.

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

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

Использование формул и ссылок

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

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

Если, например, мы внесем в ячейку формулу =(6+2)*12, то после нажатия клавиши Enter, увидим в этой ячейке не формулу, а конечный результат вычислений — 132. При этом, обратите внимание, если вы установите табличный курсор на это значение, то в строке формул отобразится не результат вычислений, а формула, которую мы внесли.

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

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

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

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

Использование функций

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

Они сгруппированы по типам:

  • Математические;
  • Финансовые;
  • Текстовые;
  • Операции с датами;
  • Логические;
  • Операции со ссылками и массивами;
  • Прочие.

Каждая функция имеет свое имя, при вводе которого в строке формул (после знака «=») активируется расчет по алгоритму, который заложен в этой функции. однако, помимо внесения наименования функции, необходимо в круглых скобках указать аргументы, необходимые для вычисления. Аргументы, это данные, необходимые для вычислений. Если функция предусматривает несколько аргументов, то они заносятся через точку с запятой.

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

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

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

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

Excel для начинающих I

Excel для начинающихExcel для начинающих I часть.

Тем, кто только начал изучение этой удивительной программы, можно только позавидовать.

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

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

Но, чтобы начать использовать все эти возможности, необходимо научиться хотя бы самым азам работы в Excel. Этому и посвящена статья.

Файл MS Excel имеет расширение xlsx (в версиях до 2007 года файлы MS Excel имели расширение xls). Эти файлы называют рабочая книга, которая, в свою очередь, состоит из рабочих листов. Каждый рабочий лист, это разбитая на колонки и строки таблица, в каждую ячейку которой можно вносить тексты, числа и формулы. Количество ячеек на каждом листе более 17 триллионов, чего вполне достаточно для самых объемных таблиц.

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

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

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

Листы можно перемещать относительно других листов. Для этого нужно нажать левую кнопку мыши на ярлычке перемещаемого листа, и, не отпуская кнопку, перетащить лист вправо или влево.

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

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

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

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

Нередко в Excel приходится оперировать не отдельными ячейками, а целыми диапазонами — выделенной группой ячеек. Адрес выделенного диапазона указывается путем указания адреса верхней левой его ячейки, и, через двоеточие адрес нижней правой ячейки этого диапазона. Например Е2:D5.

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

Использование макроса для автоматизации повторяющихся операций Excel

ExcelИспользование макроса для автоматизации повторяющихся операций Excel.

Это страшное слово «Макрос», на самом деле, не такая уж и сложная штука.

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

Для этого MS Excel имеет очень интересный инструмент, под названием макрорекордер — надстройка, которая включается через меню параметров Excel.

Нажимаем кнопку office в левом верхнем углу и выбираем пункт «Параметры Excel«. В открывшемся окне уже будет активна вкладка «Основные«. Находим пункт «Показывать вкладку «Разработчик» на ленте«, и ставим галочку в окошке слева от него. На ленте меню появится вкладка «Разработчик«. Ею мы и будем пользоваться.

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

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

Итак, мы имеем прайс-лист, который нужно раскидать по листам в соответствии с группой клиентов (см.рисунок. как вы понимаете — компания вымышленная, и даже цены по всей номенклатуре установлены нереальные). Алгоритм действий несложен:

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

Макрос готов. Давайте проверим — удалим со всех листов, кроме «Свод» все данные, и нажмем сочетание клавиш Ctrl+п. Как видите — все работает. Теперь, чтобы обновить прайс-листы, не нужно будет нудно выполнять одни и те же операции.

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

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

Сводные таблицы в ExcelСводные таблицы Excel.

Чтобы анализировать большое количество данных, простое подведение итогов в нижней строке таблицы недостаточно.

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

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

К примеру:

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

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

Вставка сводной таблицы

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

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

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

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

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

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

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

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

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

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

Настройка фильтра сводной таблицы

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

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

Вставка формул в сводную таблицу

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

  • устанавливаем табличный курсор в любом поле сводной таблицы;
  • жмем вкладку меню «Параметры«;
  • выбираем пункт «формулы«-«вычисляемое поле«.

В открывшемся окне заносим имя нашего поля (я назвал «Общая ЗП»), а в пункте «Формула» заносим операции с интересующими нас полями: =Оклад + Премия. Жмем «ОК». В области значений появляется требуемый результат.

Изменение параметра полей значений и оформление

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

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

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

и вот такое у нее размещение полей по областям.