Использование программы ms excel для решения бухгалтерских задач

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

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

Скачать:

ВложениеРазмер

Методическое пособие по Excel	97.02 КБ

Предварительный просмотр:

ДЕПАРТАМЕНТ ОБРАЗОВАНИЯ ГОРОДА МОСКВЫ

Государственное бюджетное образовательное учреждение

среднего профессионального образования города Москвы

КОММЕРЧЕСКО-БАНКОВСКИЙ КОЛЛЕДЖ №6

(ГБОУ СПО КБК № 6)

Решение финансово-экономических задач средствами Ms Excel для практических работ студентов

по дисциплине «ИТ в ПД», «АБС»

(3 и 4 курсов обучения, специальность 080110

уровень спо базовый/повышенный)

Преподаватель спецдисциплин Руднева А. В.

Зам.директора по УМР

_______________ Н. Е. Василенкова

____ ______________ 2014 г.

Рецензент: преподаватель спец.дисциплин Зайцева Е. Г.

Протокол заседания кафедры

от ____ ____________200___ №____

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

В данном практикуме закрепляются возможности табличного процессора Microsoft Excel (Office 2007).

Табличный процессор Excel, имеет широчайшие возможности по обработке данных, в том числе финансово-экономического характера. В данном пособии многие из этих возможностей заложены в решение различных задач финансового характера.

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

Во время выполнения заданий студенты учатся самостоятельно анализировать полученные результаты – представлять их в виде диаграмм, а также работать в поисковых системах с сайтами и справочно — правовой системой ГАРАНТ.

Преподавателю рекомендуется данное методическое пособие для изучения тем, связанных с функциями Excel, а задачи сами по себе могут быть использованы как итоговые занятия по этим темам (см. раздел 2 УМК ИТ в ПД).

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

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

Практическая работа №1. Расчет распределения прибыли по итогам

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

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

Требуется создать таблицу, состоящую из граф: «№ п/п» (1), «ФИО сотрудника» (2), «Дата приема на работу» (3), «Стаж работы» (4), «Оклад сотрудника» (5), «Модифицированный оклад» (6), «Премия сотрудника» (7).

Данные граф 1, 2, 3 и 5 задайте самостоятельно.

Значение графы 4 рассчитайте различными способами – с помощью функций даты и времени ( ДНЕЙ360, ДОЛЯГОДА, РАЗНДАТ, ГОД и СЕГОДНЯ ).

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

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

Премия каждого сотрудника определяется путем умножения величины модифицированного оклада на коэффициент распределения. Полученную премию следует округлить до целого. При расчете графы 7 используйте функцию ОКРУГЛ и абсолютную ссылку .

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

Дата приема на работу

Установить в ячейках D5, D6 формат ГГ

Практическая работа № 2. Остатки денежных средств на валютных счетах

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

Филиал банка работает с частными вкладчиками, имеющими валютные счета в четырех валютах. Необходимо получить на конец месяца остатки по всем валютным счетам в рублевом эквиваленте.

Требуется создать таблицу, состоящую из граф: «№ п/п» (1), «ФИО вкладчика» (2), «Расчетный счет» (3), «Сумма в валюте» (4), «Код валюты» (5), «Остаток в рублях» (6) . Данные граф 1, 2,3 и 4 задайте самостоятельно.

Вкладчики банка могут иметь от одного до четырех валютных счетов. Состояние счета оценивается и в валюте, и в рублях. Код валюты определяется исходя из расчетного счета клиента, состоящего из 20 символов. При этом код валюты занимает место с 6 по 8 символ в счете клиента.

Для извлечения кода валюты из счета клиента используйте несколько способов .

Первый способ – с помощью функции ПСТР .

Второй – с применением функции ПРАВСИМВ .

Третий – с помощью функции ЛЕВСИМВ .

Четвертый – с использованием функции ЗНАЧЕН .

Для расчета графы 6 (расчет рублевого эквивалента по каждой валюте) используются функции ЕСЛИ() и ПРОСМОТР в форме массива и «Информационный справочник Банка России». Выдержка из данного документа приведена в Таблице 1 . [1]

Что означают цифры счета:

ААА-ББ-ВВВ-Г-ДДДД-ЕЕЕЕЕЕЕ (для удобства разделён дефисами):

ААА (1—3 цифры) — номер балансового счёта первого порядка;

ББ (4—5 цифры) — номер балансового счёта второго порядка;

Г (9 цифра) — контрольная цифра;

ДДДД (10—13 цифры) — четырехзначный код подразделения банка;

ЕЕЕЕЕЕЕ (14—20 цифры) — семизначный внутренний номер (лицевого) счёта в банке.

Код валюты ПСТР

Код валюты ПРАВСИМВ

Код валюты ЛЕВСИМВ

Перевод текстового значения в числовое ЗНАЧЕН()

Остаток на счете в рублях

• Выдержка из «Информационного справочника Банка России»

Центральный банк Российской Федерации установил с 03.04.2012 следующие курсы иностранных валют к рублю Российской Федерации без обязательств Банка России покупать или продавать указанные валюты по данному курсу

Вон Республики Корея

Практическая работа № 3. Обменный пункт валюты

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

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

Создайте таблицу, состоящую из граф: «Код валюты» (1), «Наименование валюты» (2), «Количество валюты купленной» (3), «Количество валюты проданной» (4), «Курс валюты ЦБ» (5), «Курс покупки» (6), «Курс продажи» (7), «Прибыль от покупки» (8), «Прибыль от продажи» (9), «Общая прибыль» (10), «Налог на прибыль» (11), «Прибыль обменного пункта» (12) .

Данные для граф 1, 3 и 4 задайте самостоятельно.

Значение граф 2 и 5 определите с использованием дополнительной таблицы «Информационный справочник Банка России» ( см. Таблицу 1 ) и функции ВПР (графа 2) и функции ВПР (графа 5) .

Графы 6 и 7 рассчитайте в соответствии с инструкцией ( маржа составляет не более 10% ).

Подсчитайте общую сумму по графам 10, 11 и 12 .

Кол-во валюты купленной

Кол-во валюты проданной

Прибыль от покупки

Прибыль от продажи

Налог на прибыль

Прибыль обменного пункта

Дано, что маржа не более 10%, но маржа покупки чуть меньше, чем маржа продажи (чтобы получить прибыль с продажи), например, 5% и 8%)

Налог на прибыль – 20% от общей прибыли – см. статья 284, Налоговые ставки (Поисковая система Гарант Налоговый кодекс)

Практическая работа № 4. Налоговые отчисления предприятия по Единому социальному налогу

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

Рассчитать Единый социальный налог за 1-й квартал текущего года для каждого сотрудника и по предприятию в целом.

Создайте таблицу, состоящую из граф: «№ п/п» (1), «ФИО сотрудника» (2), «Оклад» (3); «Иждивенцы» (4); «Налогооблагаемая база за квартал» (5); «Налог на доходы физических лиц (НДФЛ)» (6); Отчисления в: «Федеральный бюджет» (7); «Фонд социального страхования (ФСС РФ)» (8); «Федеральный фонд обязательного медицинского страхования» (9); «Территориальные фонды обязательного медицинского страхования» (10); «Итого Единый социальный1» (11); «Итого Единый социальный2» (12) .

Данные граф 1, 2, 3 и 4 задаются самостоятельно.

Значение графы 5 вычисляется как утроенное значение графы 3 .

Значение графы 6 вычисляется по ставке 13% .

Графы 7, 8, 9, 10 и 11 вычисляются с помощью функции ЕСЛИ(И))) , ставки налогов содержатся в Таблице 2 [2]

Значение графы 12 рассчитывается как сумма граф 7-10 .

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

Стандартные налоговые вычеты можно разделить на две группы:

1. Необлагаемый минимум дохода;

Необлагаемый минимум дохода состоит из трех видов налоговых вычетов:

1. Налоговый вычет в размере 3000 руб. — предоставляется ежемесячно в течение налогового периода (календарного года) — на вычет в размере 3000 руб. имеют право категории налогоплательщиков, перечисленные в подпункте 1 пункта 1 статьи 218 Налогового кодекса РФ;

2. Налоговый вычет в размере 500 руб. — предоставляется ежемесячно в течение налогового периода (календарного года) — на вычет в размере 500 руб. могут претендовать налогоплательщики, указанные в подпункте 2 пункта 1 статьи 218 Налогового кодекса РФ;

3. Налоговый вычет в размере 400 руб.

Этот стандартный вычет применяется до того месяца, в котором доход налогоплательщика, облагаемый по ставке 13 процентов и исчисленный нарастающим итогом с начала налогового периода работодателем, предоставляющим налоговые вычеты, не превысит 40 000 руб. (Федеральный закон от 22 июля 2008 г. № 121-ФЗ «О внесении изменений в статью 218 части второй Налогового кодекса Российской Федерации»).

В соответствии с подпунктом 4 пункта 1 статьи 218 Налогового кодекса РФ налогоплательщики имеют право на стандартный налоговый вычет на каждого ребенка в возрасте до 18 лет, а также на учащегося очной формы обучения, студента, аспиранта, курсанта и ординатора в возрасте до 24 лет. Вычет предоставляется за весь период обучения детей в образовательном учреждении и (или) учебном заведении (включая академический отпуск, оформленный в установленном порядке в период обучения).

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

1. На каждого ребенка у налогоплательщиков, на обеспечении которых находится ребенок и которые являются его родителями (при этом в соответствующих случаях вычет предоставляется и супругу родителя ребенка);

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

Если же индивидуальный предприниматель является вдовой (вдовцом), одиноким родителем, опекуном или попечителем, то налоговый вычет производится в двойном размере. То же происходит в случае, если ребенок в возрасте до 18 лет — инвалид, а также в случае, если учащийся очной формы обучения, аспирант, ординатор, студент в возрасте до 24 лет является инвалидом I или II группы (подп. 4 п. 1 ст. 218 Налогового кодекса РФ).

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

Налоговая база на каждое физическое лицо нарастающим итогом с начала года

Фонд социального страхования Российской Федерации

Фонды обязательного медицинского страхования

Источник: nsportal.ru

ЛАБОРАТОРНАЯ РАБОТА 9 по теме Решение бухгалтерских задач с помощью пакета Excel

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

9.2 Приборы и материалы

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

9.3 Решение задачи учета доходов и расходов в семье

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

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

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

Решение поставленной в данной лабораторной работе задаче студенты начинают с создание плана счетов, который записывают на первый лист книги для решения задачи, так как показано на рисунке 9.1. В диапазон ячеек F 10 – G 16 показан один из вариантов плана счетов для ведения домашней бухгалтерии. При решении задачи студенты могут сами создать удобный для их семьи план счетов. Кроме плана счетов на первом листе книги Excel , который назовем «Текущие доходы и расходы» создадим аналог журнала операций в бухгалтерии. Только записывать в него будем не бухгалтерские операции, а наши текущие доходы и расходы.

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

Приступая к выполнению задачи, создайте в пакете Excel файл под именем Книга1_лаб9_(ваша фамилия). В этом файле назовите Лист1 – «Текущие доходы и расходы», Листы 2-5 назовем именами месяцев (например, июнь, июль, август, сентябрь). Переименовать листы можно, например, воспользовавшись контекстным меню названия листа. Записи в главном журнале (лист – «Текущие доходы и расходы») в хронологическом порядке фиксируют все операции по доходам и расходам. Записи в главной книге (остальные листы книги, названные именами месяцев) накапливают на счетах данные о каждой хозяйственной операции из главного журнала по месяцам.

Для облегчения переноса записей из главного журнала в главную книгу в рабочем листе «Текущие доходы и расходы» введите четыре имени диапазонов: ДатаВвода (например, от $А$4 до $А$500), НомерСчета (например, от $С$4 до $С$500), Доход (например, от $ D $4 до $ D $500) и Расход (например, от $Е$4 до $Е$500). Все эти имена и связанные с ними диапазоны ячеек должны быть на листе «Текущие доходы и расходы». Обратите внимание, что массивы ячеек должныбыть равноразмерны.

Заполните лист «Текущие доходы и расходы» как показано на рисунке 9.1. Заполняя лист «Текущие доходы и расходы», не забудьте правильно задать форматы данных для заполняемых ячеек.

В главной книге (листы с именами месяцев) введите имя ДатаКнига_ноябрь, относящееся к ячейке $ A $4, поскольку это – абсолютная ссылка (на это указывают два символа доллара). А бсолютные ссылки всегда указывают на конкретные ячейки. Если перед буквой или номером стоит знак доллара, например, $A$4, то ссылка на столбец или строку является абсолютной. Если необходимо, чтобы ссылки не изменялись при копировании формулы в другую ячейку, пользуются абсолютными ссылками. По умолчанию имена являются абсолютными ссылками.

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

Или смешанной относительный столбец и абсолютная строка (C$1), абсолютный столбец и относительная строка ($C1). Введем также имя ГКСчет относящееся к ячейке $ D 4. Это смешанная ссылка. Зафиксирован только столбец, а строка может изменяться в зависимости от того, куда вводится ссылка на ГКСчет.

В рабочих листах главной книги (листы с названиями месяцев) заполните строку под номером три заголовками, как показано на рисунке 9.2. В столбцы С и D введите соответственно наименования счетов и их номера (см. рисунок 9.2). В ячейке $А$4 установите формат ячейки Дата с сокращенным выводом даты на экран. Введите в эту ячейку даты: 01.11.10 для ноября, 01.12.10 для декабря и 01.01.11 для января месяца соответственно.

Рисунок 9.1 – Лист Главный журнал

В столбцах Доход для сбора соответствующих записей из журнала регистрации в ноябре месяце используется следующая формула:

Для выбора соответствующих расходов в ноябре месяце служит формула:

Обе эти формулы следует вводить как формулы массива. Набрав формулу, не спешите нажимать < Enter >. Сначала нажмите комбинацию клавиш < Ctrl + Shift >. Вы увидите, что теперь формула заключена в фигурные скобки. Это значит, что Excel принял вашу формулу как формулу массива. Не надо вводить фигурные скобки вручную (с клавиатуры), поскольку в этом случае Excel воспримет формулу как текст.

Формула в Excel выполняет следующую процедуру.

1. Сравнивает каждую запись в диапазоне ДатаВвода главного журнала (лист «Текущие доходы и расходы») с месяцем в дате главной книги (листы с названиями месяцев). При соответствии этих значений возвращает значение 1; в противном случае значение – 0.

2. Оценивает каждую запись в диапазоне НомерСчета главного журнала. Если номер счета там совпадает с номером текущего счета главной книги, то возвращает значение 1; в противном случае значение – 0.

3. Умножает результат, полученный в пункте 1 на результата пункта 2. Только в том случае если выполнены оба предыдущих условия, результат будет равен 1; в противном случае – 0.

Рисунок 9.2 – Рабочий лист главной книги

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

5. Возвращает значение при выполнении пункта 4.

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

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

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

= C :Финансы[Главная_книга. xls ]”Журнал”!$ A $4:$ A $215

9.4 Порядок выполнения работы

1. Разработайте план счетов для ведения бухгалтерии вашей семьи.

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

3. Заполните 1 и 2 строки листа «Текущие доходы и расходы» так, как показано на рисунке 9.1.

4. На листе «Текущие доходы и расходы» определите имена для массивов размерностью 500 ячеек для даты операции, номера счета, дохода и расхода.

5. Определите формат ячеек для массивов, созданных в предыдущем пункте. Для массива ДатаВвода формат ячеек – формат ячеек Дата. Для массива НомерСчета – формат ячеек целое число. Для массивов Доход и Расход – формат ячеек Денежный.

6. Заполните несколько строк листа «Текущие доходы и расходы» данными.

7. Подготовьте листы главной книги (листы с названиями месяцев).

8. Заполните строку 3 так, как показано на рисунке 9.2.

9. Введите дату в ячейку А4 .

10. Скопируйте с листа «Текущие доходы и расходы» план счетов.

11. Введите формулу в Е4 закончите ввод формулы нажатием сочетания клавиш Ctrl + Shift + Enter .

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

13. Воспользовавшись курсором автозаполнение, скопируйте формулу в ячейку F 4.

14. Замените в формуле ссылку на массив Доход на ссылку на массив Расход.

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

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

17. Добавьте поле остаток.

18. Оформить результаты лабораторной работы в виде отчета.

9.5 Оформление отчета

Отчет должен содержать следующее:

1. Вашу фамилию имя отчество и номер группы.

3. Название файла созданного в Microsoft Excel .

4. Письменные ответы на два (по заданию преподавателя) контрольных вопроса.

9.6 Контрольные вопросы

1. Что такое относительная ссылка на ячейку?

2. Что такое абсолютная ссылка на ячейку?

3. Что такое массив ячеек?

4. Как задать имя массива?

5. Как использовать имя массива при вводе формулы?

6. Как правильно вводить формулы?

7. Как правильно вводить формулу, содержащую несколько функций?

8. Скачано с www.znanio.ru

Лабораторная работа №9. Решение бухгалтерских задач с помощью пакета

Приступая к выполнению задачи, создайте в пакете

Рисунок 9.1 – Лист Главный журнал

Умножает результат, полученный в пункте 1 на результата пункта 2

Переименуйте листы книги Excel , в которой будете решать задачу

Как использовать имя массива при вводе формулы? 2

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

Источник: znanio.ru

Excel для бухгалтера: 12 полезных инструментов, приемов и функций

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

1.Автозаполнение формул в таблице Excel

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

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

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

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

При протягивании этой ячейки получится копия таблицы с данным, которые будут изменяться соответственно со сменой информации в исходной таблице. Это пример протягивания ячеек без фиксирования диапазонов. Можно закрепить ссылку, чтобы оставить ее неизменной при протягивании полностью, по строке или по столбцу. Фиксирование выполняется в строке формул с помощью знака $. Этот знак ставят перед той частью координат в ссылке, которую необходимо зафиксировать: $ перед буквой – фиксирование по столбцу — $С1 $ перед цифрой – фиксирование по строке — С$1 $ перед буквой и цифрой – полное фиксирование ячейки — $С$1.

2.Подсчет календарных дней

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

Если это небольшой отрезок времени, то его просто посчитать, воспользовавшись календарем, но для выполнения постоянных расчетов такой формат достаточно неудобен. В таких ситуациях приходит Excel в помощь бухгалтеру. Чтобы выполнить расчеты, необходимо выделить три свободных ячейки в таблице. В одну нужно записать начальную дату, во вторую конечную, а третью оставить пустой для получения результатов.
Рекомендация: набирайте дату на цифровой части клавиатуры так: 12/10/2016. Программа сама превратит введенные данные в формат даты и получится 12.10.2016. Далее выбираем третью ячейку и жмем “Вставить функцию”, вы можете найти ее по значку ¶x. После нажатия всплывет окно “Мастер функций”.

Из списка “Категория” выбираем “Дата и время”, а из списка “Функция”— “ДНЕЙ360” и нажимаем кнопку Ок. В появившемся окне нужно вставить значения начальной и конечной даты. Для этого нужно просто щелкнуть по ячейкам таблицы с этими датами, а в строке “Метод” поставить единицу и нажать Ок.

Если итоговое значение отражено не в числовом формате, нужно проверить формат ячейки: щелкнуть правой кнопкой мыши и выбрать из меню “Формат ячейки”, установить “Числовой формат” и нажать Ок. Еще можно выполнить подсчет дней таким способом: в третьей ячейке набрать = ДНЕЙ 360 (В1; В2; 1). В скобках необходимо указать координаты двух первых ячеек с датами, а для метода поставить значение единицы. При расчете процентов за недели можно полученное количество дней разделить на 7. Также к дате можно прибавлять и отнимать любое количество дней. Чтобы это выполнить, нужно в одной ячейке написать дату, во второй разместить знак равенства, затем щелкнуть по ячейке с датой и набрать “+” или “-” и требуемое количество дней.

3.Сортировка данных

Очень удобная функция, которая позволяет разместить данные по возрастанию/убыванию. Также сортировать данные можно и для упорядочивания записей по дате. Для выполнения этого действия необходимо выбрать область, которая требует сортировки. Затем можно нажать кнопку “Сортировка по возрастанию” в верхнем ряду меню “Данные”, ее вы найдете по знаку “АЯ”.

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

Если данные нужно сортировать по среднему столбцу, то можно использовать меню “Данные” — пункт “Сортировка” — “Сортировка диапазона”. В разделе “Сортировать по” необходимо выбрать столбец и тип сортировки.

4.Работа с длинными таблицами

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

Для этого нужно выбрать в меню “Файл”— “Параметры страницы” и закладку “Лист”. Размещаем курсор на “Сквозные строки” или “Сквозные столбцы” и в таблице кликаем на строки, которые нужно разместить на каждом листе. Также для работы с такими документами можно использовать колонтитулы. В них отмечают необходимые данные, такие как дата, номера листов, имя составителя и прочее.

Настройка колонтитулов доступна в “Параметрах страницы” — “Колонтитулы”. Там доступны готовые варианты разметки или возможность добавления собственного. Кроме полезных приемов по работе в Эксель, бухгалтеру необходимо освоить его горячие клавиши.

5.Сопоставление показателей за разные периоды

Часто данные за разные годы, кварталы, месяцы приходится сравнивать. Чтобы их сопоставить, нужно выбрать критерий — уникальный показатель для каждой единицы учета, например, артикул. Для этого используем функцию “ВПР” (“VLOOKUP”), которая к значениям по заданному критерию из первой таблицы подберет соответствующие из второй таблицы. Чтобы сравнить, например, цены на товары, следуйте алгоритму:
На панели инструментов заходим в функции(“fx”) и выбираем “ВПР”. В появившемся окне указываем для нее аргументы:

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

Копируем формулу до конца столбца. Сообщение ”#Н/Д” значит, что для указанного артикула во второй таблице значений не обнаружено.

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

6.Поиск показателей, соответствующих условиям

Возможности Excel облегчают поиск нужных данных. Разберем на примере поиска задолженности контрагента. Для этого:

Рядом с таблицей, в которой будем искать, добавляем таблицу с условиями поиска. Для этого копируем шапку и в соответствующие столбцы вводим критерии поиска, используя знаки сравнения: «>», « 200;”Ок”;”Удалить”)

Функция проверяет ячейку В2 и сравнивает ее с логическим выражением – “>200”. Если истинное значение содержит цифру больше 200, то в столбце С появляется значение “Ок”, если меньше – “Удалить”.

Выпадающий список в Excel можно создать за пару кликов. Для этого необходимо: 1.Открыть таблицу с данными, которые нужно добавить в выпадающий список. Для версии Excel 2003 и более ранних делайте это через меню Вставка — Имя — Присвоить (Insert — Name — Define).
2.Для версий Excel 2007 и моложе нужно кликнуть на вкладку “Формулы”- “Диспетчер имен” и “Создать”. Введите название позиций (например Счета на оплату услуг), которые хотите сгруппировать и подтвердите его через “Ок”. Важно: имя должно быть без пробелов и начинаться с буквы.
3.Выделить ячейки, которые должны стать выпадающим списком. Выберите меню из вкладки “Данные” — “Проверка” (Data — Validation). Из предложенного списка выберите “Тип данных”, введите строку “Источник”= название вашего диапазона Счета на оплату услуг. Готово!

12.Набор горячих клавиш Excel, без которых вам не обойтись

Применение этих сочетаний клавиш в Excel ускорит работу и поможет в выполнении анализа данных, построении графиков и форматировании таблиц.
F4 — при вводе формулы, регулирует тип ссылок (относительные, фиксированные). Можно использовать для повтора последнего действия.
Shift+F2 — редактирование примечаний
Ctrl+; — ввод текущей даты (для некоторых компьютеров Ctrl+Shift+4)
Ctrl+’ — копирование значений ячейки, находящейся над текущей (для некоторых компьютеров работает комбинация Ctrl+Shift+2)
Alt+F8 — открытие редактора макросов
Alt+= — суммирование диапазона ячеек, находящихся сверху или слева от текущей ячейки
Ctrl+Shift+4 — определяет денежный формат ячейки
Ctrl+Shift+7 — установка внешней границы выделенного диапазона ячеек
Ctrl+Shift+0 — определение общего формата ячейки
Ctrl+Shift+F — комбинация открывает диалоговое окно форматирования ячеек
Ctrl+Shift+L — включение/ отключение фильтра
Ctrl+S — сохранение файла (сохраняйтесь как можно чаще, чтобы не потерять ценные данные).

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

Не останавливайтесь на Excel!
Научитесь автоматизировать подготовку отчетов в программе Power BI на курсе «ACPM: Бизнес-анализ данных в финансах»!

Источник: finacademy.net