Дебет-Кредит
Украинский бухгалтерский еженедельник
#49 '2000: Практичная бухгалтерия - Автоматизация бухучета: Excel

Автоматизация бухучета:
Бухгалтерский учёт с помощью Excel.
2-й урок

Вячеслав ВАРЕНЯ, Владимир ЛАВРЕНОВ

Выделение и объединение ячеек

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

Например, наименование шапки: "Начисления", а под ней находятся столбцы с наименованиями "Сбор в Пенсионный фонд", "Подоходный налог" и т. д.

В создаваемой вами таблице в ячейку D1 был введен текст "Расчетная ведомость заработной платы", который расположился поверх соседних ячеек E1, F1, G1. Рассмотрим процесс объединения ячеек, соединив ячейки D1, E1, F1 и G1. Для этого выделите указанные ячейки, нажав ЛКМ на ячейке D1 (курсор - в виде белого крестика), и, не отпуская ЛКМ, потяните курсор к ячейке G1. При этом ячейки E1, F1 и G1 будут окрашены в черный цвет. Войдите в меню "Формат", выберите опцию "Ячейки" и вкладку "Выравнивание". Во вкладке "Выравнивание" напротив строки "Объединение ячеек" расположено окошко. Активизируйте его, щелкнув на нем ЛКМ (появление "галочки"), и нажмите кнопку "ОК". В результате этих действий выбранные вами ячейки будут объединены в одну (рис. 2).

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

Внимание! Чтобы выделить несколько отдельно расположенных ячеек, щелкайте ЛКМ по нужным ячейкам при нажатой клавише Ctrl. Над выделенными таким образом ячейками можно одновременно производить операции форматирования как с одной ячейкой.

Форматирование таблицы. Контекстное меню

Для того, чтобы ваша ведомость приобрела вид, показанный в первом уроке на рис. 2, вы должны на ячейки А3:Р16 наложить сетку и отформатировать ее.

Выделите массив А3:Р16 и нажмите кнопку в главном меню (рис. 3).

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

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

Если в главном меню вашей программы не установлена эта кнопка, наложить сетку на выбранные ячейки можно, войдя в меню "Формат", где выбрать опцию "Ячейки", а в ней - вкладку "Граница".

Как видно на рис. 4, в этой вкладке вы можете выбрать тип линии сетки, ее цвет, а также расположение рамки. Установив все приемлемые для вас параметры обрамления ячеек, нажмите клавишу "ОК", которая расположена в этой же вкладке.

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

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

Эргономичность вашей таблицы можно повысить, окрасив определенные участки рабочего листа и области таблицы в цвета, которые вам больше всего нравятся. Для примера, окрасим ячейки F5:I15 в желтый цвет. Для этого выделите массив F5:I15, установив курсор в ячейке F5. Щелкните ЛКМ (ячейка получит обрамление в виде двойной рамки) и, удерживая ЛКМ нажатой, потяните ее к ячейке I15. Затем в контекстном меню выберите строку "Формат ячеек", а в появившемся окне - закладку "Вид" (рис. 4). Выберите любой понравившийся вам цвет для заливки ячеек, выделив его на палитре, и подтвердите выбор нажатием кнопки "ОК".

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

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

Формат ячеек

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

Выделите массив D5:P16, войдите в меню "Формат", активизируйте вкладку "Число" в опции "Ячейки". В этой вкладке вы можете установить нужный вам формат ячеек (рис. 6).

В нашем примере нужно выбрать формат "Числовой", установить число десятичных знаков и нажать кнопку "ОК". Если в этих ячейках установлен такой формат, все данные в них будут с двумя десятичными знаками после запятой. Поскольку в ячейках С5:С15 и Е5:Е15 значения должны быть указаны без десятичных знаков, при определении для них формата "Числовой" в окошке строки "Число десятичных знаков" установите 0. Не забудьте предварительно выделить на рабочем листе эти ячейки. Для ячеек В5:В15 установите формат "Текстовый" и нажмите кнопку "ОК". После того как вы установите требуемый формат для всех ячеек таблицы, можно приступать к вводу данных.

Создание платежной ведомости

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


Исходные данные

  • На предприятии работает 11 человек.
  • Работники Кротов Ю. В., Загреба О. Ф., Гетьман Н. А., Лисовский С. А., Рябоконь Д. В., Петренко И. В. работают на предприятии по совместительству.
  • Размер должностного оклада и количество фактически отработанных работником предприятия дней указаны на рис. 2 урока 1.
  • Работникам Гетьман Н. А. и Трохимову И. В. выплачена компенсация за неиспользованный отпуск, соответственно 269,07 и 123,07 гривен.
  • Стриха В. О. был на больничном в течение 3 дней - с 2 по 4 февраля. Оклад Стрихи В. О. в декабре 1999 г. и январе 2000 г. установлен в размере 700 грн.
  • Между предприятием и работником Загребой О. Ф. заключен договор аренды легкового автомобиля. По условиям этого договора предприятие обязано ежемесячно в течение его действия выплачивать Загребе О. Ф. арендную плату 1500 грн.
  • Количество рабочих дней в декабре 1999 года - 20, а в январе 2000 года - 19.

Расчет основных показателей в платежной ведомости. Ввод данных в ячейки

Составление ведомости по заработной плате начнем с листа "ЗП февраль". Не забудьте, что листы "ЗП февраль" и "Аванс февраль" сгруппированы (если это не сделано, сгруппируйте их известным вам способом), поэтому вводимые данные будут копироваться и в лист "Аванс февраль".

Для ввода данных в столбец 2 установите курсор в ячейку В5 (рис. 8), нажмите клавишу F2

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

Подтвердить ввод данных в ячейку можно, щелкнув ЛКМ в строке формул на кнопке . Ошибочный ввод данных можно отменить, щелкнув на кнопке .

Данные в ячейки В6:В15 вводятся аналогично.

На основании данных, приведенных на рис. 2 предыдущего урока, заполните столбец 3 "Общий стаж" и столбец 4 "Оклад" (D5:D15).

Встроенная в программу Excel возможность автоматизировать вычисления уже на этом этапе позволит вам убедиться в простоте этого процесса. Выделите уже известным вам способом ячейки начиная с D5 по D15 включительно и щелкните ЛКМ кнопку суммы в панели инструментов. В результате таких действий в ячейке D16 появится сумма окладов всех работников. Если щелкнуть ЛКМ на этой ячейке, то в строке формул вы увидите "=СУММ(D5:D15)".

Внимание! Определить сумму нескольких ячеек, не вводя результат в соответствующую ячейку, можно и другим способом. Выделите ячейки, которые необходимо просуммировать, например Е5:Е15, и в информационной строке, которая находится в нижней части окна программы, появится: "сумма=212".

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

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

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

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

Математические действия в формуле отображаются с помощью известных вам операторов (+, -, *, /).

В Excel используются три адресных оператора: двоеточие, запятая, пробел.

Двоеточие ":" обозначает диапазон ячеек. Например, выражение "D5:D15" ссылается на ячейки с D5 по D15 включительно. Запятая "," ссылается на отдельную ячейку (выражение "D5,D15" ссылается только на две ячейки - D5 и D15). Пробел указывает на ячейки, расположенные в промежутке между двумя группами ячеек (например, выражение "=СУММ(А1:А6 А4:А10)" просуммирует значения, помещенные в ячейки А4, А5, А6).

Внимание! Формула всегда должна начинаться со знака "=". Если знака равенства "=" в начале формулы нет, программа воспринимает формулу как текст и вычисления не производит.

Знак ";" обычно применяется при вводе условных операторов (функции "Если" и т. п.) и обозначает разделение условия, которое будет выполняться при вычислении формулы.

Например, в ячейку L5 (рис. 9) введена формула "=ЕСЛИ(J5<=150;J5*0,01;ЕСЛИ(J5>150;(J5)*0,02))". Эта формула в нашем примере используется для расчета суммы сбора в Пенсионный фонд и, соответственно, означает:

- если сумма в ячейке J5 меньше или равна 150 грн, то для определения сбора в ПФ используется ставка налога 1% (фрагмент формулы "J5*0,01;");

- если первое условие не соответствует введенным в ячейку J5 данным, то выполняется второе условие, т. е. если сумма в ячейке J5 больше 150 грн, то содержимое ячейки J5 умножается на 0,02.

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

Для того чтобы заполнить в таблице ячейки J5:J15, необходимо суммировать данные в ячейках F, G, H, I.

Установите курсор в ячейку J5, введите знак "=", затем установите курсор в ячейку F5 (формула приобретет вид "=F5"), нажмите кнопку "+", установите курсор в ячейку G5, а затем аналогично последовательно суммируйте ячейки H5 и I5 и нажмите кнопку Enter. Формула в ячейке J5 после указанных действий приобретет такой вид: "=F5+G5+H5+I7".

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

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

Приведенные выше действия проще выполнить, введя в ячейку J5 функцию "=СУММ" и без пробела добавив к ней аргумент, указанный в круглых скобках - (F5:I7). Результат будет тот же.

Формулы, содержащие функции, можно копировать так же, как и простые формулы (типа А5+1). Поэтому для ввода формулы "=СУММ(F5:I7)" в остальные ячейки столбца J используйте ее копирование.

Абсолютная адресация

На рабочих листах "Аванс февраль" и "ЗП февраль" сумма, начисленная по окладу каждому работнику, будет отображена в соответствующей ячейке столбца 6 (F5:F15). Так, например, сумма начисленного аванса Кротову Ю. В. будет отображена в ячейке F5 соответствующего листа и по условиям нашего примера рассчитана по формуле "=D5*E5/$J$2".

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

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

Итак, в ячейку F5 введена формула "=D5*E5/$J$2" (сумма начисленного аванса Кротову Ю. В.). В ячейку F6, соответственно, введена формула "=D6*E6/$J$2" (сумма начисленного аванса Листьеву А. Ф.). Обе указанные формулы имеют общий элемент "$J$2" или ссылаются на ячейку J2.

Если бы в данном случае ячейка J2 не имела абсолютной адресации, то при копировании формулы из ячейки F5 в ячейку F6 элемент формулы J2 изменится, соответственно, на J3, в результате чего вместо ожидаемого числового результата в ячейке F6 появится надпись "#ЗНАЧ!". Это означает, что отсутствуют цифровые данные для формулы, поскольку ячейка J3 имеет текстовый формат и представляет собой название столбца 10 ("Всего начислено").

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

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

Допустим, сумма аванса работника Гетьман Н. А. начисляется исходя из 35% ее должностного оклада, следовательно, формула в ячейке F8 будет иметь следующий вид: "= D8*0,35". В случае если сумма начисленного аванса имеет жесткую привязку к количеству отработанных дней в ячейку F8 будет введена формула "= D8/$J$2*E8". Итог начисленных сумм можно также подсчитать, используя функцию суммирования.

Внимание! Перед тем как в столбец 5 ввести количество фактически отработанных каждым работником предприятия рабочих дней на дату выплаты аванса (E5:E15), разгруппируйте листы рабочей книги. В противном случае в авансовой ведомости и в итоговой ведомости за месяц число отработанных дней будет одинаковым.

В случае если для суммирования выбранных вами элементов невозможно или нецелесообразно использовать указанные ранее способы, то желаемый результат может быть получен при использовании другого метода. Рассмотрим его на простом примере. Пусть необходимо узнать удельный вес суммы, начисленной по больничному листу (ячейка Н12), в общей суме начисленного фонда оплаты труда (J16). Для этого можно использовать формулу "=Н12/ J16*100" (рис. 10).

Активизируйте ячейку, в которой хотите получить результат (например, D2). В строке формул щелкните ЛКМ на знаке равенства, затем ЛКМ щелкните на ячейке Н12 (ее адрес автоматически появится в вводимой формуле), введите знак деления "/", щелкните на ячейке J12 и выполните остальные арифметические действия.

Запомните! Арифметические действия в формулах можно производить, если содержимое ячейки имеет числовое значение. Для установления формата ячейки воспользуйтесь рассмотренным выше методом.


Следующий урок

© 2000
"Дебет-Кредит"
Редакция: debet-kredit@gc.kiev.ua