Планы практических работ (стр. 4 )

Автоматизация учета амортизации основных средств

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

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

1. Введите заголовок таблицы и заголовки столбцов.

2. В ячейку С2 введите дату расчета, и выберите формат, при котором выводится только месяц и год, без числа. В ячейку М2 введите формулу =МЕСЯЦ(С2), вычисляющий номер месяца.

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

4. Для расчета амортизации с начала года введите в ячейку Н6 длинную
формулу. Амортизация за прошедшие месяцы будет рассчитываться как
Балансовая стоимость * Процент амортизации * Текущий месяц / 12.

(=D5*G5*$М$2/12)

Вычислять амортизацию есть смысл если средство еще полностью не амортизировано, т. е. если будет выполняться условие: амортизация на начало года + балансовая стоимость * годовой процент / 12 * количество месяцев < балансовой стоимости (Е5+D5*G5*$М$2/12<В5) Таким образом итоговая формула примет вид =ЕСЛИ(Е5+D5*G5*$М$5/12<D5;D5*G5*$М$2/12;D5-Е5)

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

5. В следующую ячейку, в столбце амортизации за период эксплуатации,
вводится простая формула = Е5+Н5 (амортизация на начало года и
амортизация за прошедшие с начала года месяцы). Аналогично, в следующем
столбце рассчитывается остаточная стоимость с помощью формулы =D5-I5.

Также размножьте формулы на расположенные ниже строки с помощью автозаполнения.

6. Чтобы вычислить амортизацию в текущем месяце, в ячейку К5
введите формулу

=ЕСЛИ(Е5+D5*G5*$М$2/12<D5;D5*05*$М$2/12;D5-Е5)-EСЛИ(Е5+D5*G5*($М$2-1)/12<D5; D5*С5*($М$2-1)/12; В5-Е5)

Расчетная часть состоит из двух похожих фрагментов, таких же, как и в ячейке Н6. Только в первом фрагменте стоит $М$2, а во втором — $М$2-1. В результате первый фрагмент считает амортизацию на текущую дату, а второй - амортизацию на дату, отстоящую от текущей на месяц назад. Вычтя из одной суммы другую, мы получим амортизацию за текущий месяц.

Эту формулу нужно тоже размножить с помощью автозаполнения.

7. Все сложные формулы введены, осталось только добавить суммарные
показатели. Они вводятся предельно просто, например, для вычисления
общей балансовой стоимости в ячейку N5 нужно ввести формулу
=СУММ(D5:D7). Аналогичные формулы нужно ввести и в другие ячейки,
указав другие столбцы для суммирования.

Лабораторная работа №10

Работа со списком как с базой данных

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

-  сортировать список,

-  производить фильтрацию записей, удовлетворяющую заданному критерию

-  подводить итоги в списке и, наконец,

-  узнаете, что такое Форма при работе со списком,

-  использовать функции при работе со списком.

Задание

Скопируйте файл «Реки» с диска Y в свою личную папку под тем же именем. Откройте скопированный файл. На экране появляется пример списка, содержащий 6 столбцов. При просмотре строк списка удобно использовать команду Закрепить области меню Окно для фиксации строки с названиями столбцов, чтобы она всё время оставалась на экране при прокрутке списка, а линия ниже этой строки являлась границей области. Для этого выделите первую ячейку третьей строки списка и выберите команду Закрепить области меню в меню Окно.

Как выполнить сортировку в списке

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

-  Установите курсор в любую ячейку списка

-  Выберите команду из меню Данные/Сортировка. На экране
появится диалоговое окно « Сортировка диапазона»:

-  Введите имена полей и направление сортировки, которые отображаются в диалоговом окне

Задание

1. Выполните сортировку списка по алфавиту по столбцу «Название
реки». Для этого:

А) Вызовите на экран список для выполнения сортировки.

Б) Выделите любую ячейку в списке.

В) Вызовите диалоговое окно "Сортировка диапазона", для этого

выберите Данные/Сортировка. Выберите имя столбца («Название реки») и направление сортировки (по возрастанию), которые отображаются в диалоговом окне.

2. Предположим, что мы хотим отсортировать список по 2 столбцам
одновременно: "Куда впадает" (по возрастанию) и "Название реки» (по
алфавиту). Для этого

-  Вызовите на экран список для выполнения сортировки.

-  Выделите одну любую ячейку в списке.

-  Вызовите диалоговое окно "Сортировка диапазона",

-  Выберите имя столбца («Куда впадает») и направление сортировки (по возрастанию), которые отображаются в диалоговом окне.

-  В окне «Затем по» выберите имя столбца «Название реки» и направление сортировки (по возрастанию). Ниже представлено окончательное окно диалога "Сортировка диапазона".

3. Выполните самостоятельно сортировку списка по столбцу «Длина». Определите реки с наименьшей и наибольшей длиной.

Под поиском, который часто называют фильтрацией, будем понимать нахождение в списке таких записей, которые удовлетворяют требованиям, вводимым для полей.. Ехсед предоставляет для фильтрации 2 команды: Автофильтр; Расширенный фильтр.

Как осуществить поиск в списке, используя Автофильтр

Для поиска Автофильтром следует выполнить следующие шаги:

-  Вызвать на экран список.

-  Выбрать из меню команду Данные/ Фильтр/ Автофильтр

-  Установить курсор на стрелку того поля, по которому хотим произвести поиск. Щёлкнуть левой кнопкой мыши. На экране появится перечень значений всех записей в этом поле.

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

-  Если требуется выполнить поиск по такому значению, которого нет ни в одной записи, то необходимо выбрать Условие. На экране появится диалоговое окно: Пользовательский Автофильтр.

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

-  Такой поиск можно последовательно выполнять по нескольким полям.

-   

Задание

1. Предположим, что нам нужно вывести на экран список рек, исследованных в 1980г. Для этого выполните следующие щаги:

А) Вызовите на экран список.

Б) Выделите любую ячейку в списке.

В) Данные/ Фильтр/ Автофильтр. На экране: в каждом имени поля появилась стрелка.

Г) Курсор установите на стрелку того столбца, по которому хотим произвести фильтрацию (в примере: «Год исследования»)

Д) В раскрывающемся списке рядом с заголовком столбца выбираем «1980».

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

Как восстановить исходный список

-  Чтобы удалить Автофильтр для конкретного столбца, раскройте соответствующий список Автофильтр и выберите в нём Все.

-  Для удаления всех Автофильтров и их кнопок, выберите команду Автофильтр ещё раз, удалив, таким образом, галочку рядом с названием этой команды в подменю Фильтр из меню Данные.

-   

Задание

1.  Восстановите исходный список.

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

А) Вызовите на экран список.

Б) Выделите любую ячейку в списке.

В) Данные/ Фильтр/ Автофильтр. На экране в каждом имени поля появилась стрелка.

Г) Курсор установите на стрелку того столбца, по которому хотим

произвести фильтрацию (в примере: «Площадь бассейна»).

Д) В раскрывающемся списке рядом с заголовком столбца выберите "Условие...". На эекране диалоговое окно Пользовательский фильтр

Е) Введите необходимый критерий.

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

Как осуществить поиск в списке, используя Расширенный фильтр

Команда Расширенный фильтр позволяет выполнить следующие действия:

1.  фильтрацию по нескольким столбцам;

2.  фильтрацию по вычисляемому критерию (не рассматривается)

Рассмотрим последовательность действий при фильтрации по нескольким столбцам или полям:

-  Вызвать список, в котором нужно осуществить фильтрацию

-  Сформировать критерий поиска - диапазон условий. При этом учесть следующие замечания: Команда Расширенный фильтр, в отличии от Автофильтр, требует задания условий отбора записей в отдельном диапазоне рабочего листа. Поскольку при фильтрации скрываются целые строки, диапазон условий лучше поместить выше или ниже списка. Если вы предполагаете, что список со временем будет расширяться, то диапазон условий следует разместить выше списка. Диапазон условий должен содержать, по крайней мере, 2 строки. Введите один или несколько заголовков столбцов в верхней строке, а условия отбора - во вторую и последующую строки. За исключением вычисляемых условий заголовки в диапазоне условий должны точно совпадать с заголовками столбцов в списке. Для обеспечения точности эти заголовки лучше всего создавать, выделяя заголовки столбцов в списке и копируя их в верхнюю строку диапазона условий с помощью команд Копировать и Вставить из меню Правка.

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

Диапазон условий должен быть отделён от строк списка хотя бы одной пустой строкой.

В диапазоне условий можно ввести любое количество условий. Ехсеl интерпретирует их в соответствии со следующими правилами:

a.  условия на одной строке считаются соединёнными логическим оператором "И";

b.  условия на разных строках считаются соединёнными логическим оператором "ИЛИ";

c.  пустая ячейка в диапазоне условий означает любое значение для соответствующего поля.

§  Вызвать команду из меню : Данные/ Фильтр/ Расширенный фильтр

§  Ввести необходимые данные в диалоговом окне Расширенный фильтр

Задание

1. Выведите на экран список рек, исследуемых в 1981 и 1994г и с длиной не более 2000 км. Для этого выполните следующие шаги:

А) Вызовите на экран список.

Б) Вставьте несколько строк перед первой строкой списка, сформируйте критерий фильтрации

В) Выделите любую ячейку в списке,

Г) Данные/ Фильтр/ Расширенный фильтр.

Д) Введите необходимые данные.

Е) Результат скопируйте на новый лист.

Ещё одно важное замечание:

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

1) Единственная буква означает: "Найти все значения, которые начинаются с этой буквы". Пример: при вводе К условие выполняется для следующих названий рек: Кама, Колыма, Кубань.

2) Символ ">" или "<" означает: "Найти все значения, которые находятся по алфавиту после или перед введённым текстовым значением".

3) Формула = "=текст" означает: "Найти значения, которые точно совпадают со строкой символов текст”.

В условиях отбора расширенного фильтра разрешается использование символов шаблона, и они работают так же, как и в пользовательском Автофильтре.

Задание

1. (используя расширенный фильтр)

a.  Вывести на экран список рек, имеющих истоки с Кавказа или впадающие в Азовское море.

b.  Вывести на экран список рек, названия которых начинаются с букв "К" и "П" и длина в них не менее 3000 км. Результаты выполненных заданий скопируйте на новый лист.

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

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

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

Ехсеl предлагает 14 функций - СЧЁТЕСЛИ, СУММЕСЛИ и БДФункции (функции для работы с базами данных): ДСРЗНАЧ, БСЧЁТ, БСЧЁТЛ, БИЗВЛЕЧЬ, ДМАКС, ДМИН, БДПРОИЗВЕД, ДСТАНДОТКЛ, ДСТАНДОТКЛП, БДСУММ, БДДИСП и БДДИСПП - которые специально предназначены для работы со списками. Каждая из этих функций возвращает информацию об элементах списка, которые удовлетворяют некоторому условию.

Задание

Подсчитать годовой сток рек, впадающих в Карское море.

А) Вызовите исходный список.

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

В) В ячейку АЗ скопируйте название столбца - Куда впадает, а в ячейку А4 - условие - Карское море.

Г) Курсор установите в ячейку, где должен быть результат вычисления годового стока.

Д) Выберите кнопку - вставка функции, категория - работа с базой данных, функция –БДСУММ

Е) Заполните поля функции по образцу

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

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

Третий аргумент задаёт диапазон условий.

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

1.  Самостоятельно определите среднее значение площади бассейнов рек, впадающих в Азовское море.

2.  Определить максимальную длину реки, исследуемой в 1980 году.

Ехсеl позволяет создавать итоговые строки в списках по запросу пользователя. Рассмотрим на примере.

Задание

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

А) Восстановите исходный список.

Б) Отсортируйте список по году исследования реки.

В) Выбрать из меню Данные/ Итоги и ввести необходимые данные по образцу ниже:

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

Г) У вас должно получиться следующее:

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

Для управления структурой программа создаёт области выше и (или) левее таблицы, размещая в них кнопки управления структурой - символы структуры. Сгруппировать можно только смежные строки и столбцы. Область группировки показана жирной линией, связанной с кнопкой, на которой изображён знак минус. Эта кнопка позволяет скрыть группу данных. Каждый элемент группы отмечен точкой. После того как данные скрыты, появляется кнопка со знаком плюс, которая позволяет их восстановить. Скрытие данных не влияет на итоговые величины в таблице. Кроме того, к символам структуры относятся кнопки, обозначающие уровни структуры. На этих кнопках изображён номер уровня. Простейшая структура имеет два уровня, а максимальное их число — 8

Задание

1.  Используя кнопки со знаком минус, скройте группы данных, оставив лишь итоговые строки для 1980,1990, 1994г., остальные группы оставив без изменения.

2.  Последовательно нажмите кнопки уровней структуры - 1, 2, 3. Следите как меняется информация на экране. Что позволяют сделать со структурой эти кнопки?

3.  Удалите структуру, используя команды меню: Данные/ Группы и
структура/ Удалить структуру

4.  Удалите итоговые строки (Данные/ Итоги/ Удалить всё)

5.  Самостоятельно создайте итоги по запросам;

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

Существует еще один способ отображения данных, когда из списка выбирается только одна строка и каждое поле располагается в определённом месте экрана. Таким образом, пользователь видит только данные одной строки. Это режим работы - Форма. Формы удобны для широких таблиц, строки которых не умещаются на экране.

Создадим форму для списка «Реки» :

А) Данные/ Форма

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

О некоторых возможностях формы.

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

Задание

1.  Используя Форму, осуществите поиск рек по критерию год исследования -1980г и имеющих длину не более 1000 км. Сколько таких рек вы получили?

2.  Очистите старый критерий и осуществите поиск рек с площадью бассейна не более 2000 км и годовым стоком не более 300 км. Закрыть форму.