Меню сайта

Категории каталога
Самоучитель фотошоп [26]
3D Studio Max 5 [5]
Итак, вы являетесь счастливым обладателем одного из лучших пакетов трехмерной графики - 3D Studio Max или просто Мах.
Adobe Illustrator 10 [6]
Эта программа, которую используют миллионы графических дизайнеров во всем мире, в том числе и в России, по-прежнему является лидером и обеспечивает все увеличивающиеся требования к программам этого класса, а во многих направлениях остается "законодателем мод".
EXCEL [5]
Очень часто предпринимателям приходится производить расчет эффективности будущих операций. Для автоматизации подобных расчетов можно составить электронную таблицу.

Начало » Статьи » EXCEL

Цветовое оформление
Цветовое оформление

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

Рис. 2.19. Кнопка Цвет заливки с открытой цветовой палитрой

Цветовое оформление ячеек также задается путем активизации команды Формат/Ячейки и выбора нужного цвета на вкладке Вид диалогового окна Формат ячеек.

Рис. 2.20. Диалоговое окно Формат ячеек, вкладка Вид

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

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

Рис. 2.21. Кнопка Границы с открытым списком рамок

Дополнительные типы рамок можно выбрать на вкладке Граница диалогового окна Формат ячеек (рис. 2.22). Это окно вызывается при помощи команды Ячейки, находящейся в меню Формат, или команды Формат ячеек контекстного меню.

Рис. 2.22. Диалоговое окно Формат ячеек, вкладка Граница
Представление результатов

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

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

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

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

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

ПРИМЕР

В компании N при сдаче декларации по НДС три года назад в графе налоговых обязательств было указано число 20,11, в графе налогового кредита - число 20,8. Естественно, согласно декларации, дебетовое сальдо составило 20,8 - 20,11 = 0,69. Однако дело происходило в конце рабочего дня и налоговый инспектор, только взглянув на декларацию, сказал, что она заполнена неправильно. На вопрос "Почему?" он снисходительно объяснил: "Ведь 11 больше, чем 8". Можно было бы обвинить инспектора в элементарной безграмотности, ведь по его расчетам получалось 20,11 - 20,8 = 0,3. Однако инспектор не заметил, что речь идет о цифрах, находящихся после запятой, и не сообразил, что восемь десятых это никак не меньше, чем одиннадцать сотых. В данном случае виноват человек, предоставивший такую декларацию, поскольку он не учел возможности подобной интерпретации чисел.
Числовое форматирование

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

Pис. 2.24. Рабочий лист с таблицей расчета шести формул; для ячеек с результатами задан формат Числовой

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

Рис. 2.25. Диалоговое окно Формат ячеек, вкладка Число, выбран элемент Числовой

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

ПРИМЕР

Во время августовского кризиса 1998 года многие предприятия при отгрузке товара перешли на цены, привязанные к у.е. Экономисту одного из таких предприятий потребовалось подготовить спецификацию к контракту на довольно приличную сумму. Умея работать на компьютере, он, естественно, решил воспользоваться программой Excel. В ячейках справа от спецификации экономист поместил курс доллара и цены на товары в долларах. В "официальной" же части рабочего листа - все остальные атрибуты спецификации. При изменении курса доллара он планировал получить новый результат, не производя расчетов вручную, а лишь изменив в ячейке F1 курс доллара. Цены следовало указать в рублях без копеек (то есть в виде целых чисел). При курсе доллара 28,06 руб/$ цена на товар стоимостью $2 составила 56,16 руб (2 х 28,06 = 56,16). Однако на экране отобразилось число 56, так как в качестве параметра форматирования ячеек для рабочего листа было задано отображение только целых чисел (о чем пользователь не вспомнил). Во втором случае товар стоил $3, а его стоимость в национальной валюте составила 3 х 28,06 = 84,24 руб. Однако на экране появилось только число 84. Если опираться на представленные на экране значения, то при умножении цены первого товара на количество должно получиться 56 х 4 = 224. Но компьютер произвел операцию с другими числами (56,16 х 4 = 224,64), и на экране отобразилось число 225.

Рис. 2.26. Рабочий лист с примером неправильного форматирования при создании спецификации

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

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

Рис. 2.27. Диалоговое окно Параметры, вкладка Вычисления

ВНИМАНИЕ

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

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

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

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

Рис. 2.28. Панель функции ОКРУГЛ

Данная функция округляет число до указанного количества десятичных разрядов. Синтаксис ее следующий:

ОКРУГЛ(число; количество_цифр)

здесь число - это округляемое число, количество_цифр - это количество десятичных разрядов, до которого нужно округлить число.

Например, для операции извлечения корня в нашем примере формула округления будет иметь такой вид:

=ОКРУГЛ(В10/\(1/С10);2)

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

ПРИМЕР

На таможню приходит товар стоимостью 3 цента за единицу в количестве 1000 штук. Курс обмена - 29,31 руб./$, а все таможенные платежи (без учета НДС) составляют 29,45%. База начисления для таможенных платежей будет равна цене в долларах, умноженной на курс доллара и количество единиц товара:
$0,03 * 29,31 * 1000 = 879,30 руб.

При умножении базы начисления на ставку таможенного сбора получается сумма, которой быть не может (так как не существует пока в мире десятых и сотых долей копеек при перечислении их через банк):
879,30 руб. * 29,45% = 258,9539 руб.

Если и далее производить расчеты без округления размера таможенного платежа, то можно получить стоимость партии товара, которая равна стоимости товара плюс таможенные платежи:
879,30 руб. + 258,9539 руб. = 1138,25385 руб.

Таким образом, цена единицы товара будет следующей:
1138,25385 руб. : 1000 шт. = 1,138254 руб.

Полученные неправильные результаты представлены в табл. 2.1.

Цена за единицу ($) Количество (штук) Курс (руб./$) Таможенный платеж (%) Таможенный платеж (руб.) Итого стоимость партии (руб.) Цена за единицу (руб.)
0,03 1000 29,31 29,45% 258,9539 1138,25385 1,138254

Таблица 2.1. Пример неправильного расчета таможенных платежей

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

=ОКРУГЛ(цена в долларах х на курс доллара х количество;2)

Цифра 2 означает, что выполняется округление с точностью до второго знака (то есть до копеек).

Таким же образом можно округлить и стоимость партии товара, в результате чего получится сумма, равная 1138,25 руб. Однако операция округления цены за единицу товара может привести к нежелательным последствиям. Это зависит от того, как происходит расчет цены. Если округлить цену товара до копеек, исходя из стоимости партии товара:

=ОКРУГЛ(1138,25/1000;2)

то результат будет равен 1,14 руб. Но получается парадокс: произведя обратный расчет, мы получим, что партия стоит 1,14 * 1000 =1140 руб. Откуда-то взялись лишние 2 рубля. Эти 2 рубля могут значительно усложнить ведение бухгалтерского учета, если в бухгалтерской программе не предусмотрена возможность задания разрядности денежной единицы при некоторых операциях. До какого же знака целесообразно задать точность в данном примере?

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

Цена ($) Количество Курс (руб./$) Таможенный платеж (%) Таможенный платеж (руб.) Итого стоимость партии (руб.) Цена за единицу (руб.)
0,03 1000 29,31 29,45% 258,95 1138,25 1,13825

Таблица 2.2. Пример правильного расчета таможенных платежей и стоимости товара

В Excel существует более 10 функций округления, каждая из которых выполняет эту операцию по-своему. Часть из них представлена на рис. 2.29. Исчерпывающую информацию относительно всех этих функций вы можете получить в справке Excel, вызываемой путем нажатия функциональной клавиши [F1].

Рис. 2.29. Функции округления в Excel
Тестирование созданной таблицы

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

Тестирование - это проверка правильности работы таблицы. В процессе тестирования необходимо, изменяя значения в ячейках области ввода, проверить корректность вычислений. В качестве экперимента заменим значение в ячейке В4 нулем. Полученные результаты приведены на рис. 2.30.

Рис. 2.30. Результаты вычисления при значении второго операнда, равном 0

Присутствие в ячейке значения, которое начинается символом "#", говорит о том, что формула возвратила ошибку. Результат при делении на нуль в ячейках D11 и D13 (#ДЕЛ/0!) является правильным, но недопустимым. Таблица, в которую попадет это значение, работать не будет. Поэтому необходимо предпринять соответствующие меры (информацию по данной теме вы можете получить в следующих главах).
Практическое применение полученных знаний

Расчет НДС

Приобретая товары, нередко приходится определять заложенную в стоимости товара сумму НДС. Для этого достаточно создать небольшую таблицу, которая при изменении данных о стоимости товара мгновенно рассчитает сумму НДС. Таблица, представленная на рис. 2.31, условно разделена на две части: область ввода А1:В4, в которую занесены действующая ставка НДС в процентах (ячейка В1) и стоимость товара с НДС (ячейка ВЗ), а также область вывода А5:В8.

В области вывода определяется удельная сумма НДС в стоимости товара и стоимость товара без НДС.

Расчет суммы НДС производится по формуле:

=ОКРУГЛ(Стоимость приобретения х (Ставка НДС : (100% + Ставка НДС));2)

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

=ОКРУГЛ(ВЗ*(Bl/(1+B1));2)

Расчет стоимости товара без НДС можно осуществить двумя способами. В первом случае используется почти та же формула, что и для определения суммы НДС:

=ОКРУГЛ(Стоимость приобретения х (100% : (100% + Ставка НДС));2)

или в табличном виде:

=ОКРУГЛ(ВЗ*(1/(1+В1));2)

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

Второй способ состоит в вычитании из стоимости товара предварительно вычисленной суммы НДС:

=Стоимость товара - Сумма НДС

или в табличном виде:

=ВЗ-В5

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

Таблицы расчета суммы НДС (с числовыми значениями и формулами) приведены на рис. 2.31 и 2.32.

Рис. 2.31. Таблица расчета суммы НДС (с числовыми данными)

Рис. 2.32. таблица расчета суммы НДС (с формулами)
Элементарный расчет налогов и прибыли

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

В данной таблице имеются четыре области:
- Ставки налогов (область А1:ВЗ).
- Суммы реализованных товаров, издержек и дебетового НДС (область А5:В7).
- Расчет НДС, подлежащего уплате в бюджет (область А9:В10).
- Расчет прибыли и налогов на прибыль и с оборота (область А12:В17).

Рис. 2.33. Таблица расчета налогов и прибыли (с числовыми данными)

Заметим, что первые две области предназначены для ввода информации, а последние - для ее дальнейшей обработки. Рассмотрим формулы, введенные в область расчета (рис. 2.34).

Формула для расчета налогового обязательства по НДС описывалась в предыдущем примере (она показана также на рис. 2.32). Табличный вид этой формулы следующий:

=ОКРУГЛ(В5*(B1/(1+B1));2)

Формула расчета НДС, подлежащего уплате в бюджет, определяется вычитанием суммы налогового кредита из суммы налоговых обязательств (находится в ячейке В10):

=В9-В7

Валовая прибыль без НДС (формула в ячейке В12) равна разности суммы реализации и суммы налоговых обязательств:

=В5-В9

Рис. 2.34. Таблица расчета налогов и прибыли (с формулами)

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

=В12-В6

Налог с оборота рассчитывается умножением полученной валовой прибыли на ставку налога с оборота:

=ОКРУГЛ(В12*ВЗ;2)

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

=В13-В14

Налог на прибыль равен округленному до второго разряда (до копеек) произведению налогооблагаемой прибыли на действующую ставку налога на прибыль:

=ОКРУГЛ(В15*В2;2)

Чистая прибыль в результате реализации равняется разности налогооблагаемой прибыли и суммы налога на прибыль:

=В15-В16

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

Резюме

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

Категория: EXCEL | Добавил: freeinstruct (2006-12-26) | Автор: Valentin
Просмотров: 2801 | Рейтинг: 0.0 |

Всего комментариев: 0
Имя *:
Email *:
Код *:
Форма входа

Поиск по каталогу

Друзья сайта

Каталог Ресурсов Интернет
Статистика


Copyright MyCorp © 2006
Создать бесплатный сайт с uCoz